前言: 首先,学生上机需要在主界面上显示学生的信息,然后在online表和line表中添加记录,下机需要计算好上机时间和消费金额,更新line表和student表,删除online表中对应的记录。
流程图如下:
上机:
Dim txtsql As String
Dim msgtext As String
Dim mrc As ADODB.Recordset
Dim mrc1 As ADODB.Recordset
txtdowntime.Text = ""
txtdowndate.Text = ""
txtpaytime.Text = ""
txtpay.Text = ""
'判断是否输入卡号
If Not Testtxt(txtcardid.Text) Then
Label1.Visible = True
Label1.Caption = "请输入卡号!"
Exit Sub
End If
If Not IsNumeric(txtcardid.Text) Then
Label1.Visible = True
txtcardid.Text = ""
txtcardid.SetFocus
Label1.Caption = "卡号请输入数字!"
Exit Sub
End If
'查询卡号
txtsql = "select * from student_info where cardno='" & Trim(txtcardid.Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.RecordCount = 0 Then
Label1.Visible = True
txtcardid.Text = ""
txtcardid.SetFocus
Label1.Caption = "卡号不存在!"
Exit Sub
Else
If Trim(mrc!Status) = "不使用" Then '查询是否注册
Label1.Visible = True
Label1.Caption = "此卡尚未注册!"
Exit Sub
End If
End If
mrc.Close
'查询是否上机
txtsql = "select * from online_info where cardno='" & Trim(txtcardid.Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.RecordCount = 1 Then
Label1.Visible = True
Label1.Caption = "此卡正在上机!"
txttype.Text = Trim(mrc.Fields(1))
txtsid.Text = Trim(mrc.Fields(2))
txtname.Text = Trim(mrc.Fields(3))
txtdepartment.Text = Trim(mrc.Fields(4))
txtsex.Text = Trim(mrc.Fields(5))
txtupdate.Text = Trim(mrc.Fields(6))
txtuptime.Text = Trim(mrc.Fields(7))
Exit Sub
End If
mrc.Close
'查询余额
txtsql = "select * from student_info where cardno='" & Trim(txtcardid.Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
txtsql = "select * from basicdata_info"
Set mrc1 = ExecuteSQL(txtsql, msgtext)
If Val(Trim(mrc!cash)) < Val(Trim(mrc1!limitcash)) Then
Label1.Visible = True
Label1.Caption = "余额不足,请充值!"
Exit Sub
End If
mrc.Close
mrc1.Close
'显示学生信息
txtsql = "select * from student_info where cardno='" & Trim(txtcardid.Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
txtname.Text = Trim(mrc.Fields(2))
txtsex.Text = Trim(mrc.Fields(3))
txtsid.Text = Trim(mrc.Fields(1))
txtdepartment.Text = Trim(mrc.Fields(4))
txttype.Text = Trim(mrc.Fields(14))
txtcash.Text = Trim(mrc.Fields(7))
txtuptime.Text = time
txtupdate.Text = Date
mrc.Close
'向online表中添加数据
txtsql = "select * from online_info "
Set mrc = ExecuteSQL(txtsql, msgtext)
mrc.AddNew
mrc.Fields(0) = txtcardid.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) = txtupdate.Text
mrc.Fields(7) = txtuptime.Text
mrc.Fields(8) = GetThisComputerName
mrc.Fields(9) = Now
mrc.Update
lblshangji.Caption = mrc.RecordCount
mrc.Close
'向line表中添加数据
txtsql = "select * from line_info"
Set mrc = ExecuteSQL(txtsql, msgtext)
mrc.AddNew
mrc.Fields(1) = txtcardid.Text
mrc.Fields(2) = txtsid.Text
mrc.Fields(3) = txtname.Text
mrc.Fields(4) = txtdepartment.Text
mrc.Fields(5) = txtsex.Text
mrc.Fields(6) = txtupdate.Text
mrc.Fields(7) = txtuptime.Text
mrc.Fields(14) = GetThisComputerName
mrc.Fields(13) = "正常上机"
mrc.Fields(12) = txtcash.Text
mrc.Update
mrc.Close
MsgBox "成功上机", vbOKOnly + vbExclamation, "提示"
Label1.Visible = False
下机:
Dim txtsql As String
Dim msgtext As String
Dim mrc As ADODB.Recordset
Dim mrc1 As ADODB.Recordset
Dim umrc As ADODB.Recordset
'判断是否输入卡号
If Not Testtxt(txtcardid.Text) Then
Label1.Visible = True
Label1.Caption = "请输入卡号!"
Exit Sub
End If
If Not IsNumeric(txtcardid.Text) Then
Label1.Visible = True
Label1.Caption = "卡号请输入数字!"
txtcardid.Text = ""
txtcardid.SetFocus
Exit Sub
End If
'查询卡号
txtsql = "select * from student_info where cardno='" & Trim(txtcardid.Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.RecordCount = 0 Then
Label1.Visible = True
Label1.Caption = "卡号不存在!"
txtcardid.Text = ""
txtcardid.SetFocus
Exit Sub
Else
If Trim(mrc!Status) = "不使用" Then '查询是否注册
Label1.Visible = True
Label1.Caption = "此卡尚未注册!"
Exit Sub
End If
End If
mrc.Close
'查询是否上机
txtsql = "select * from online_info where cardno='" & Trim(txtcardid.Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
If mrc.RecordCount = 0 Then
Label1.Visible = True
Label1.Caption = "此卡没有上机!"
Exit Sub
End If
mrc.Close
'显示数据
txtdowntime.Text = time
txtdowndate.Text = Date
txtsql = "select * from online_info where cardno='" & txtcardid.Text & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
txtcardid.Text = Trim(mrc.Fields(0))
txttype.Text = Trim(mrc.Fields(1))
txtsid.Text = Trim(mrc.Fields(2))
txtname.Text = Trim(mrc.Fields(3))
txtdepartment.Text = Trim(mrc.Fields(4))
txtsex.Text = Trim(mrc.Fields(5))
txtupdate.Text = Trim(mrc.Fields(6))
txtuptime.Text = Trim(mrc.Fields(7))
'计算上机时间
txtpaytime.Text = DateDiff("n", txtupdate, txtdowndate) + DateDiff("n", txtuptime, txtdowntime)
mrc.Close
'计算消费金额
txtsql = "select * from student_info where cardno='" & Trim(txtcardid.Text) & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
txtsql = "select * from basicdata_info "
Set mrc1 = ExecuteSQL(txtsql, msgtext)
If Val(txtpaytime.Text) < Trim(mrc1!LeastTime) Then
txtpay.Text = 0
txtcash.Text = Trim(mrc.Fields(7))
Else
If Trim(mrc!Type) = "固定用户" Then
txtpay.Text = Round(Val(txtpaytime.Text) / Trim(mrc1!unittime) * Trim(mrc1!Rate))
txtcash.Text = Trim(mrc!cash - txtpay.Text)
Else
txtpay.Text = Round(Val(txtpaytime.Text) / Trim(mrc1!unittime) * Trim(mrc1!TmpRate))
txtcash.Text = Trim(mrc!cash - txtpay.Text)
End If
End If
mrc.Close
mrc1.Close
'更新两个表
txtsql = "select * from online_info where cardno='" & txtcardid.Text & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
txtsql = "select * from line_info where cardno='" & txtcardid.Text & "'and ondate='" & Trim(mrc!ondate) & "'and ontime='" & Trim(mrc!OnTime) & "'"
Set mrc1 = ExecuteSQL(txtsql, msgtext)
mrc1.Fields(8) = Date
mrc1.Fields(9) = time
mrc1.Fields(10) = Trim(txtpaytime.Text)
mrc1.Fields(11) = Trim(txtpay.Text)
mrc1.Fields(12) = Trim(txtcash.Text)
mrc1.Fields(13) = "正常下机"
mrc1.Update
mrc.Delete
mrc.Close
mrc1.Close
txtsql = "select * from online_info "
Set mrc = ExecuteSQL(txtsql, msgtext)
lblshangji.Caption = mrc.RecordCount
mrc.Close
txtsql = "select * from student_info where cardno='" & txtcardid.Text & "'"
Set mrc = ExecuteSQL(txtsql, msgtext)
mrc.Fields(7) = Trim(txtcash.Text)
mrc.Update
mrc.Close