自学生管理系统以后,进入了机房收费系统,开始自己真的很抵触这个东西,因为没有源码,只能靠自己了。对代码没有亲和力的我,只能硬着头皮硬闯。下面看看就讲讲我的上下机的辛酸路吧!
【分析】
界面:很简单,只有两个简单的Command命令按钮掌控着上下机。
功能:上机时,窗体显示该学生的基本信息;下机时,更新学生卡里的余额。计算消费情况。上下机时及时更新上机人数。
需求:用到的数据库为:student表;online表;line表;basicdata表;
【逻辑】
【实现】
上机部分:
1、定义变量:
Dim mrs As ADODB.Recordset
Dim strSQL As String
Dim MsgString As String
Dim mrs1 As ADODB.Recordset
Dim mrs2 As ADODB.Recordset
2、链接数据库:
strSQL = "select * from student_info where cardno='" & Trim(txtCardno.Text) & "'and status='使用'" '链接学生表
Set mrs = ExecuteSQL(strSQL, MsgString)
strSQL = "select * from online_Info where cardno='" & Trim(txtCardno.Text) & "'" '链接online表
Set mrs1 = ExecuteSQL(strSQL, MsgString)
strSQL = "select * from basicdata_info " '链接basicdata表
Set mrs2 = ExecuteSQL(strSQL, MsgString)
3、判断部分:
'判断卡号是否为空
If txtCardno.Text = "" Then
MsgBox "卡号不能为空,请输入卡号!", vbOKOnly + vbExclamation, "警告!"
txtCardno.Text = ""
txtCardno.SetFocus
Exit Sub
End If
'判断卡号是否存在
If mrs.EOF Then
MsgBox "此卡尚未注册,请先注册!", vbOKOnly + vbExclamation, "警告!"
txtCardno.Text = ""
txtCardno.SetFocus
Exit Sub
Else
'判断此卡是否已经上机
If mrs1.EOF = False Then
MsgBox "此卡正在上机!", vbOKOnly + vbExclamation, "警告!"
txtType.Text = mrs1.Fields(1)
txtStudentno.Text = mrs1.Fields(2)
txtName.Text = mrs1.Fields(3)
txtDepartment.Text = mrs1.Fields(4)
txtSex.Text = mrs1.Fields(5)
txtOndate.Text = mrs1.Fields(6)
txtOntime.Text = mrs1.Fields(7)
txtCardno.Text = ""
txtCardno.SetFocus
Exit Sub
Else
'判断余额
If Val(mrs.Fields(7)) < Trim(mrs2.Fields(3)) Then
MsgBox "余额不足,请充值后再登录!"
txtCardno.Text = ""
txtCardno.SetFocus
Exit Sub
Else
'调用学生信息到输入框。上机成功!
txtType.Text = mrs.Fields(14)
txtStudentno.Text = mrs.Fields(1)
txtName.Text = mrs.Fields(2)
txtDepartment.Text = mrs.Fields(4)
txtSex.Text = mrs.Fields(3)
txtChargemoney.Text = mrs.Fields(7)
txtOndate.Text = Date
txtOntime.Text = Time
txtOfftime.Text = ""
txtOffdate.Text = ""
txtCtime.Text = ""
txtCmoney.Text = ""
MsgBox "成功上机!", vbOKOnly + vbExclamation, "警告!"
Label18.Caption = "欢迎使用!"
End If
End If
End If
4、更新部分:
With mrs1 '添加online表
.AddNew
.Fields(0) = Trim(txtCardno.Text)
.Fields(1) = Trim(txtType.Text)
.Fields(2) = Trim(txtStudentno.Text)
.Fields(3) = Trim(txtName.Text)
.Fields(4) = Trim(txtDepartment.Text)
.Fields(5) = Trim(txtSex.Text)
.Fields(6) = Date
.Fields(7) = Time
.Fields(8) = VBA.Environ("computername")
.Fields(9) = Date + Time
.Update
txtCount.Text = mrs1.RecordCount
.Clone
.Close
End With
With mrs '更新学生表
.Fields(11) = "未结账"
.Update
.Clone
.Close
End With
strSQL = "select * from online_info " '更新上机人数
Set mrs2 = ExecuteSQL(strSQL, MsgString)
txtCount.Text = mrs2.RecordCount
理清思路,代码敲起来才更容易。
对于下机部分:
基本数据设定表中存在:准备时间和至少上机时间;这里规定若上机时间不超过准备时间不收费,小于至少上机时间也不收费。难点部分也就在计算消费金额部分,所以这里就重点显示计算消费金额部分:
'数据库连接:
strSQL = "select * from student_info where cardno='" & Trim(txtCardno.Text) & "'and status='使用'"'连接学生数据库
Set mrs = ExecuteSQL(strSQL, MsgString)
strSQL = "select * from online_info where cardno='" & Trim(txtCardno.Text) & "'"'连接正在上机的卡数据库
Set mrs1 = ExecuteSQL(strSQL, MsgString)
strSQL = "select * from online_info"
Set mrs2 = ExecuteSQL(strSQL, MsgString)
strSQL = "select * from basicdata_info"'连接基本数据设定数据库
Set mrs3 = ExecuteSQL(strSQL, MsgString)
strSQL = "select * from line_info"'连接上机记录数据库
Set mrs4 = ExecuteSQL(strSQL, MsgString)
'页面更新信息
txtStudentno.Text = mrs1.Fields(2)
txtType.Text = mrs1.Fields(1)
txtName.Text = mrs1.Fields(3)
txtSex.Text = mrs1.Fields(5)
txtDepartment.Text = mrs1.Fields(4)
txtOntime.Text = mrs1.Fields(7)
txtOndate.Text = mrs1.Fields(6)
txtOfftime.Text = Time
txtOffdate.Text = Date
txtChargemoney.Text = mrs.Fields(7)
txtCtime.Text = Abs(Val(DateDiff("n", Time, Trim(txtOntime.Text))))'计算消费时间
'判断消费时间是否小于准备时间
If Trim(txtCtime.Text) < OfterTime Then
txtCtime.Text = 0
txtCmoney.Text = 0
txtChargemoney.Text = mrs.Fields(7)
End If
'判断消费时间是否小于至少上机时间
If Trim(txtCtime.Text) < inTtime1 Then
txtCtime.Text = 0
txtCmoney.Text = 0.5
txtChargemoney.Text = mrs.Fields(7)
Else
If Trim(mrs.Fields(14)) = "临时用户" Then'若为临时用户,消费金额的计算
txtCmoney.Text = (Int(Trim(txtCtime.Text) / 30) + 1) * (Trim(mrs3.Fields(1)) / 2)
txtChargemoney.Text = Trim(mrs.Fields(7)) - Trim(txtCmoney.Text)
Else'若为固定用户,消费金额的计算
txtCmoney.Text = (Int(Trim(txtCtime.Text) / 30) + 1) * (Trim(mrs3.Fields(0)) / 2)
txtChargemoney.Text = Trim(mrs.Fields(7)) - Trim(txtCmoney.Text)
End If
MsgBox "下机成功!", vbOKOnly + vbExclamation, "提示!"
End If
这样一来,其实上下机只有理清逻辑关系,按照自己的思路敲下来,也很简单。所以不要给自己随便定位,没有什么不可能的,米老师说过:每天多给自己一些正能量的词汇。不要凭空的给自己压力,把自己的思维固定住了。相信自己可以做到的!我写的这个只是按着自己的思路敲了,其实有些过程可以复用的,期待自己更新~~~~~~,人生就是不断的更新,不断的超越,加油!