①全部下机(通过do while ...loop循环对所有上机人员进行下机操作,并逐个更改对应数据表中的消费金额、余额等数据)
Private Sub alldown_Click()
Dim omrc As ADODB.Recordset
Dim otxtSQL As String
Dim oMsgText As String
Dim smrc As ADODB.Recordset
Dim stxtSQL As String
Dim sMsgText As String
Dim consume As Integer
Dim consumetime As String
Dim deleotxtSQL As String
Dim deleoMsgText As String
Dim deleomrc As ADODB.Recordset
Dim updatestxtSQL As String
Dim updatesMsgText As String
Dim updatesmrc As ADODB.Recordset
Dim lmrc As ADODB.Recordset
Dim ltxtSQL As String
Dim lMsgText As String
Dim StrCPN As String * 10
Dim i As String
Dim n As Integer
GetComputerName StrCPN, 10
'myflexgrid内数据循环删除
Do While Not myflexgrid.Rows = 1
otxtSQL = "select * from Online_Info where cardno='" & myflexgrid.TextMatrix(1, 0) & "'"
Set omrc = ExecuteSQL(otxtSQL, oMsgText)
stxtSQL = "select * from student_Info where cardno='" & myflexgrid.TextMatrix(1, 0) & "'"
Set smrc = ExecuteSQL(stxtSQL, sMsgText)
consumetime = DateDiff("n", omrc.Fields(7), Time) '计算消费时长
If Trim(smrc.Fields(1)) = "固定用户" Then '根据用户类型计算出消费金额
consume = consumetime / 2 '固定用户除以2元每分钟
Else
consume = consumetime / 3 '临时用户除以3元每分钟
End If
'更新student_Info 数据,用户余额更新
i = Trim(smrc.Fields(7)) - consume
updatestxtSQL = "update student_Info set cash=" & i & " where cardno='" & myflexgrid.TextMatrix(1, 0) & "'"
Set updatesmrc = ExecuteSQL(updatestxtSQL, updatesMsgText)
'更新line_Info数据,添加下机记录
ltxtSQL = "select * from Line_Info "
Set lmrc = ExecuteSQL(ltxtSQL, lMsgText)
lmrc.AddNew
lmrc.Fields(1) = Trim(myflexgrid.TextMatrix(1, 0))
lmrc.Fields(2) = Trim(smrc.Fields(1))
lmrc.Fields(3) = Trim(smrc.Fields(2))
lmrc.Fields(4) = Trim(smrc.Fields(4))
lmrc.Fields(5) = Trim(smrc.Fields(3))
lmrc.Fields(6) = Trim(omrc.Fields(6))
lmrc.Fields(7) = Trim(omrc.Fields(7))
lmrc.Fields(8) = Format(Now(), "yyyy-MM-dd")
lmrc.Fields(9) = Format(Now(), "HH:mm:ss")
lmrc.Fields(10) = consumetime
lmrc.Fields(11) = consume
lmrc.Fields(12) = i
lmrc.Fields(13) = "正常下机"
lmrc.Fields(14) = Trim(StrCPN)
lmrc.Update
'更新online_Info数据,删除上机数据
deleotxtSQL = "delete from Online_Info where cardno='" & myflexgrid.TextMatrix(1, 0) & "'"
Set deleomrc = ExecuteSQL(deleotxtSQL, deleoMsgText)
myflexgrid.RemoveItem 1 '删除myflexgrid本行数据
Loop
omrc.Close '释放omrc记录集
smrc.Close '释放smrc记录集
lmrc.Close '释放lmrc记录集
End Sub
m = myflexgrid.Rows - 1 '定义m变量为检索次数
For a = 1 To m '循环次数
If myflexgrid.TextMatrix(m, 5) = "√" Then '检索有√标识的行
......(与全部下机相同,只是查询、删除、更新语句中的myflexgrid.text(1,0)中的“1”改为变量"m")
m = m - 1
Else
m = m - 1
End If
Next