#【前言】
下机部分涉及的表比较多,逻辑性格比较强,要事先理清楚逻辑关系再去敲代码会事半功倍,从刚开始接触机房的一头雾水到后来的逐渐清晰经历了一个不断探索和摸索的过程。
#【下机逻辑】
下机其实并没有什么难的,只要把所有验证的条件想全面就好了,稍微复杂一点就是计算消费金额,把这点再解决了下机就很简单了,下面是我的下机流程图
#【下机代码展示】
Private Sub Command2_Click()
Dim StudentSQL As String
Dim CancelCardSQL As String
Dim OnLineSQL As String
Dim LineSQL As String
Dim MsgText As String
Dim mrcStudent As ADODB.Recordset
Dim mrcCancelCard As ADODB.Recordset
Dim mrcOnLine As ADODB.Recordset
Dim mrcLine As ADODB.Recordset
'定义变量储存数据
Dim IntLineTime As Integer '存储实际在线实际
Dim IntConSume As Single
Dim CurConSume As Single '存储真正花费的时间
Dim CurBalance As Single '存储用户的余额
Dim FixEdunit As Single '存储单位余额
Dim TeMunit As Single '存储单位时间
Dim a As Integer
Dim RemainCash As Single
'判断是否输入卡号
If Not Testtxt(txtCardNo.Text) Then
MsgBox "请输入卡号!", vbOKOnly + vbExclamation, "系统提示"
txtCardNo.SetFocus
Exit Sub
End If
'判断是否注册和退卡
StudentSQL = "select * from student_info where cardno='" & Trim(txtCardNo.Text) & "'"
Set mrcStudent = ExecuteSQL(StudentSQL, MsgText)
If mrcStudent.EOF Then
MsgBox "此卡未注册!", vbOKOnly + vbExclamation, "系统提示"
txtCardNo.SetFocus
txtCardNo.Text = ""
mrcStudent.Close
Exit Sub
Else
'判断是否退卡
CancelCardSQL = "select * from cancelcard_info where cardno='" & Trim(txtCardNo.Text) & "'"
Set mrcCancelCard = ExecuteSQL(CancelCardSQL, MsgText)
If mrcCancelCard.EOF = True And mrcStudent.Fields(10) = "未使用" Then
MsgBox "此卡号已经退卡!", vbOKOnly + vbExclamation, "系统提示"
txtCardNo.SetFocus
txtCardNo.Text = ""
Exit Sub
Else
'判断是否上机
OnLineSQL = "select * from online_info where cardno= '" & Trim(txtCardNo.Text) & "'"
Set mrcOnLine = ExecuteSQL(OnLineSQL, MsgText)
If mrcOnLine.EOF = True Then
MsgBox "此卡未上机,请选择其他卡号!", vbOKOnly + vbExclamation, "系统提示"
txtCardNo.SetFocus
txtCardNo.Text = ""
Exit Sub
End If
End If
End If
'在线时长计算
IntLineTime = (Date - DateValue(mrcOnLine!onDate)) * 1440 + (Hour(Time) - Hour(TimeValue(mrcOnLine!ontime))) * 60 + (Minute(Time) - Minute(TimeValue(mrcOnLine!ontime)))
'时间单位为分钟
'获得基本表的数据
BasicDataSQL = "select * from Basicdata_Info"
Set mrcBasicData = ExecuteSQL(BasicDataSQL, MsgText)
mrcBasicData.MoveLast
'单位时间的费用 (把固定用户,临时用户单位时间的费用分别赋值给费用)
FixEdunit = Val(mrcBasicData.Fields(0)) '把固定用户的金额赋值给变量
TeMunit = Val(mrcBasicData.Fields(1)) '把临时用户的金额赋值给变量
'判断在线时间是否小于准备时间,若小于则 消费金额=0
If IntLineTime <= Val(Trim(mrcBasicData.Fields(4))) Then
txtOffTime.Text = "0"
Else
'判断在线时间是否小于最低消费时间,若小于则为0
If IntLineTime < Val(Trim(mrcBasicData.Fields(3))) Then
txtctime.Text = "0"
End If
End If
'在线时间大于单位时间,就按有几个单位时间算,分为固定用户和临时用户
If IntLineTime >= Val(Trim(mrcBasicData!leastTime)) And IntLineTime And Trim(mrcStudent!Type) = "固定用户" Then
a = Int(IntLineTime / Val(Trim(mrcBasicData!unitTime)))
If a = IntLineTime / Trim(mrcBasicData!unitTime) Then
CurConSume = a
Else
CurConSume = a + 1
End If
txtConSume.Text = Val(CurConSume) * Val(FixEdunit)
If txtConSume.Text = "" Then txtCash.Text = "0"
Else
If IntLineTime >= Val(Trim(mrcBasicData!leastTime)) And IntLineTime And Trim(mrcStudent.Fields(14)) = "临时用户" Then
a = Int(IntLineTime / Val(Trim(mrcBasicData!unitTime)))
If a = IntLineTime / Trim(mrcBasicData!unitTime) Then
CurConSume = a
Else
CurConSume = a + 1
End If
txtConSume.Text = Val(CurConSume) * Val(TeMunit)
End If
End If
'更新student表
StudentSQL = "select * from student_Info where cardno='" & Trim(txtCardNo.Text) & "'"
Set mrcStudent = ExecuteSQL(StudentSQL, MsgText)
RemainCash = mrcStudent!Cash - Val(txtConSume.Text)
mrcStudent.Fields(7) = RemainCash
mrcStudent.Update
'更新Line表
LineSQL = "select * from line_info"
Set mrcLine = ExecuteSQL(LineSQL, MsgText)
mrcLine.AddNew
mrcLine.Fields(1) = Trim(txtCardNo.Text)
mrcLine.Fields(2) = Trim(mrcOnLine.Fields(2))
mrcLine.Fields(3) = Trim(mrcOnLine.Fields(3))
mrcLine.Fields(4) = Trim(mrcOnLine.Fields(4))
mrcLine.Fields(5) = Trim(mrcOnLine.Fields(5))
mrcLine.Fields(6) = Trim(mrcOnLine.Fields(6))
mrcLine.Fields(7) = Trim(mrcOnLine.Fields(7))
mrcLine.Fields(8) = Trim(Date)
mrcLine.Fields(9) = Trim(Time)
mrcLine.Fields(10) = Trim(IntLineTime)
mrcLine.Fields(11) = Trim(Val(CurConSume) * Val(TeMunit))
mrcLine.Fields(12) = Trim(mrcStudent.Fields(7))
mrcLine.Fields(13) = "正常下机"
mrcLine.Fields(14) = Trim(VBA.Environ("computername"))
mrcLine.Update
'更新下机界面
txtStudentNo.Text = mrcStudent.Fields(1)
txtDepaMent.Text = mrcStudent.Fields(4)
txtType.Text = mrcStudent.Fields(14)
txtStudentName.Text = mrcStudent.Fields(2)
txtSex.Text = mrcStudent.Fields(3)
txtOndate.Text = mrcOnLine.Fields(6)
txtOntime.Text = mrcOnLine.Fields(7)
txtConsumeMoney.Text = mrcStudent.Fields(7)
txtOffDate.Text = Date
txtOffTime = Time
txtConSumeTime.Text = mrcLine.Fields(10)
txtConSume.Text = mrcLine.Fields(11)
mrcOnLine.Delete
mrcOnLine.Update
If mrcOnLine.RecordCount - 1 Then
MsgBox "下机成功", vbOKOnly + vbExclamation, "系统提示"
End If
mrcStudent.Close
mrcOnLine.Close
mrcLine.Close
mrcBasicData.Close
'清空文本框
txtCardNo.Text = ""
txtStudentNo.Text = ""
txtStudentName.Text = ""
txtDepaMent.Text = ""
txtSex.Text = ""
txtOndate.Text = ""
txtOntime.Text = ""
txtType.Text = ""
txtOffDate.Text = ""
txtConsumeMoney.Text = ""
txtOffTime.Text = ""
txtConSumeTime.Text = ""
txtConSume.Text = ""
End Sub
#【总结】
刚开始认为很难的东西,刚接触一定要打败自己的畏难心理,逆流而上静心分析不急躁,一步一个脚印就会做出我们想要的效果。