机房收费系统上下机也是一个很重要的部分,期中理清思路是很重要的,下面就是上下机部分的流程图
一:上机:
上机过程涉及到的数据表是:从student_info到online_ info,代码如下:
'判断卡号是否存在,包括有卡号和在使用
txtsql = "select * from student_info where cardno= '" & Trim(txtcid.Text) & "' And Status = '使用'"
Set mrc = executesql(txtsql, msgtext)
If mrc.EOF Then
MsgBox "此卡尚未注册", vbOKOnly + vbExclamation, "警告"
txtcid.SetFocus
txtcid.Text = ""
mrc.Close
Else
mrc.Close
'判断是否在上机
txtsql = "select * from online_info where cardno= '" & Trim(txtcid.Text) & "'"
Set mrc = executesql(txtsql, msgtext)
If mrc.EOF = False Then '若在上机
MsgBox "该用户已上机", vbOKOnly + vbExclamation, "警告"
mrc.Close
'自动添加第一次上机信息
txtsql = "select * from student_info where cardno= '" & Trim(txtcid.Text) & "'"
Set mrc = executesql(txtsql, msgtext)
txtsid.Text = mrc.Fields(1) '学号
txtname.Text = mrc.Fields(2) '姓名
txtsex.Text = mrc.Fields(3) '性别
txtdepartment.Text = mrc.Fields(4) '系别
txtbalance.Text = mrc.Fields(7) '余额
txttype.Text = mrc.Fields(14) '类型
mrc.Close
'上机日期时间显示
txtsql = "select * from online_info where cardno= '" & Trim(txtcid.Text) & "'"
Set mrc = executesql(txtsql, msgtext)
txtondate.Text = mrc.Fields(6)
txtontime.Text = mrc.Fields(7)
Timer1.Enabled = True
Else
mrc.Close
'判断是否有余额,不足提示余额不足请充值后上机,只要有钱就可以
'查看余额
txtsql = "select cash from student_info where cardno= '" & Trim(txtcid.Text) & "'"
Set mrc = executesql(txtsql, msgtext)
If mrc.Fields(0) <= 0 Then
MsgBox "余额不足请充值后上机", vbOKOnly + vbExclamation, "警告"
mrc.Close
Else
mrc.Close
'然后就可以上机了,自动添加信息
txtsql = "select * from student_info where cardno= '" & Trim(txtcid.Text) & "'"
Set mrc = executesql(txtsql, msgtext)
txtsid.Text = mrc.Fields(1) '学号
txtname.Text = mrc.Fields(2) '姓名
txtsex.Text = mrc.Fields(3) '性别
txtdepartment.Text = mrc.Fields(4) '系别
txtbalance.Text = mrc.Fields(7) '余额
txttype.Text = mrc.Fields(14) '类型
txtoffdate.Text = ""
txtofftime.Text = ""
txtspend.Text = ""
txtcost.Text = ""
mrc.Close
'上机日期时间显示
txtondate.Text = Date
startdate = txtondate.Text
txtontime.Text = Time
starttime = txtontime.Text
Timer1.Enabled = True
'更新数据库online_info里的信息
txtsql = "select * from online_info "
Set mrc = executesql(txtsql, msgtext)
mrc.AddNew
mrc.Fields(0) = txtcid.Text
mrc.Fields(1) = txttype.Text
mrc.Fields(2) = txtsid.Text
mrc.Fields(3) = txtname.Text
mrc.Fields(4) = txtdepartment.Text
mrc.Fields(5) = txtsex.Text
mrc.Fields(6) = txtondate.Text
mrc.Fields(7) = txtontime.Text
mrc.Fields(8) = "JTB-PC"
mrc.Update
mrc.Close
End If
End If
End If
End If
'显示当前上机人数
txtsql = "select * from online_info"
Set mrc = executesql(txtsql, msgtext)
If mrc.EOF Then
lbloncount.Caption = 0
Else
lbloncount.Caption = mrc.RecordCount
End If
二:下机:
下机过程比较不好搞的是:余额的计算,计算公式:余额=上机前的金额-上机时间*单位时间价格
计算过程中涉及到的数据:
数据来源:student_info(金额)
basicdata_info(基本数据设定)
online_info(上机日期,时间)
数据录入到:student_info(上完机之后的金额)
line_info(下机日期,时间)
下面主要是金额计算及把最后余额更新到student_info表中的代码展示:
1.消费时间的计算,用到了datediff函数,返回两个日期之间的时间差,有三个参数,第一个是时间差的形式(年,月 天,分钟),第二个是开始时间,第二个是结束时间。
'计算消费时间,用到了函数
txtspend.Text = DateDiff("n", Trim(starttime), Trim(endtime))
costtime = txtspend.Text
2.不同用户单位时间的金额不一样,所以当使用单位时间金额时,数据应从basicdate_info 表中根据不同的用户条件 获取。
'查看每小时的的价格,分为"固定用户"和"一般用户"
stutxtsql = "select * from student_info where cardno= '" & Trim(txtcid.Text) & "'"
Set stumrc = executesql(stutxtsql, msgtext)
bastxtsql = "select top 1 * from basicdata_info "
Set basmrc = executesql(bastxtsql, msgtext)
If stumrc.Fields(14) = "固定用户" Then
strrate = Val(Trim(basmrc.Fields(0)))
Else
strrate = Val(Trim(basmrc.Fields(1))) '固定用户的单位价格
End If
stumrc.Close
basmrc.Close
3.消费金额计算的时候,一定要看上机时间是否小于设置的最小时间,并且一般用户,和固定用户的单位时间消费不一样。
'计算消费金额,分情况
txtsql = "select top 1 * from basicdata_info "
Set basmrc = executesql(txtsql, msgtext)
If costtime < Val(basmrc.Fields(3)) Then '小于leastime,不收钱
txtcost.Text = 0
txtspend.Text = 0
basmrc.Close
Else
txtcost.Text = costtime / 60 * Val(strrate)
basmrc.Close
4,获取上机前的金额,计算上机消费,并且将余额更行到student_info 表中
'获取上机前的金额,同时将剩余金额更新student 表中cash
txtsql = "select * from student_info where cardno= '" & Trim(txtcid.Text) & "'"
Set stumrc = executesql(txtsql, msgtext)
'余额
txtbalance.Text = Val(stumrc.Fields(7) - Val(txtcost.Text))
'把余额更新到表中
stumrc.Fields(7) = Val(Trim(txtbalance.Text))
stumrc.Update
stumrc.Close
总结:
写代码之前一定要理清思路,并且要知道都是用到了那个表里的数据,需要更新哪个表里的数据,先从全局出发,再细化代码,这样就可以做到不断完善。