前言
在机房系统中,强制下机是非常必要的,当用户余额用完时自动下机。做这个功能还是比较坎坷的,用时2天才完成。最开始的思路是在timer中使用循环,当上机时间每大于5分钟时进行扣费,更新表里余额,写好代码运行,发现上机时间大于5分钟后,每次执行timer都要扣一次费,也就是只要上机时间到了5分钟,用不了1s卡里的钱都被扣光。这时,需要定义一个时间变量,上机时长大于5分钟后,上机时长要+5分钟,以防止扣之前5分钟的费。就是这个时间变量花费了很长时间,代码也比较乱,果断换个思路。下面看看实现功能的思路是什么样的!
流程图
知识点
Dim 和 Redim的区别
Dim :Dimension(维度的缩写)定义函数时使用,例如:Dim a As Integer,定义整型变量。
Redim :在有些时候不知道需要多大的数组,就可以使用一个能改变大小的数组,就是动态数组,redim是很方便,灵活的可以随时改变大小的数组。有效管理内存,可以将内存空间释放给系统,大大节省内存,提高运行速度。
Redim使用:只能在过程中使用。redim有两种用法,一个带preserve,一个不带,区别是是否保留原来的数据。
例如:有一个Byte数组A,如果知道它的上限最大只能是4,就用Dim
Dim A As Byte()
但如果不能事先确定大小,或在运行时改变它的大小就要用动态数组。
Dim A As Byte()
Redim A (4) '这样改变大小时会清空原来的数据,进行重新定义
Redim Preserve A (4) 这样改变大小时会保留原来的数据
代码片段
timer.interval设置为60000,也就是1min.
Private Sub Timer2_Timer()
Dim txtSQL As String
Dim MsgText As String
Dim mrc As ADODB.Recordset
Dim mrc1 As ADODB.Recordset
Dim mrc2 As ADODB.Recordset
Dim mrc3 As ADODB.Recordset
Dim mrc4 As ADODB.Recordset
Dim mrc5 As ADODB.Recordset
Dim m, i, a, valueDate, valueTime As Integer
Dim pay As Single
txtSQL = "select * from online_Info"
Set mrc = ExecuteSQL(txtSQL, MsgText)
'判断是否有卡号上机
If mrc.EOF Then
Timer2.Enabled = False
Exit Sub
Else
mrc.MoveFirst
End If
'将上机卡号定义为一个数组
ReDim a(mrc.RecordCount) As String
For i = 0 To mrc.RecordCount - 1
a(i) = Trim(mrc!cardno)
cardno = a(i)
txtSQL = "select * from online_Info where cardno='" & Trim(cardno) & "'"
Set mrc1 = ExecuteSQL(txtSQL, MsgText)
txtSQL = "select * from student_Info where cardno='" & Trim(cardno) & "'"
Set mrc2 = ExecuteSQL(txtSQL, MsgText)
txtSQL = "select * from line_Info where status='" & "正常上机" & "' and cardno='" & Trim(cardno) & "'"
Set mrc3 = ExecuteSQL(txtSQL, MsgText)
'记录上机日期时间
ondate = Trim(mrc1.Fields(6))
onTime = Trim(mrc1.Fields(7))
'函数计算上机时长
valueDate = DateDiff("n", ondate, Date)
valueTime = DateDiff("n", onTime, Time)
valueTime = valueDate + valueTime
txtSQL = "select * from basicdata_Info"
Set mrc4 = ExecuteSQL(txtSQL, MsgText)
'计算单位金额
If mrc2.Fields(14) = "固定用户" Then
pay = Format(mrc4.Fields(0) / 60, "0.0")
Else
pay = Format(mrc4.Fields(1) / 60, "0.0")
End If
'判断上机时间是否大于2min,计算消费金额
If valueTime >= 2 Then
valueTime1 = valueTime - (valueTime - 1)
pay = Format(pay * valueTime1, "0.0")
Else
pay = Format(pay * valueTime, "0.0")
End If
'更新student的cash
mrc2.Fields(7) = mrc2.Fields(7) - pay
mrc2.Update
If Val(mrc2.Fields(7)) > 0 And Val(mrc2.Fields(7)) = Val(mrc4.Fields(5)) Then
MsgBox a(i) & ",您卡内金额已不足" & Val(mrc4.Fields(5)) & "元,请尽快充值!", 48, "温馨提示"
End If
'判断是否余额为0,更新student,online表
If Val(mrc2.Fields(7)) <= 0 Then
mrc2.Fields(7) = 0
mrc2.Update
mrc.Delete
mrc.Update
MsgBox "卡号:" & a(i) & ",余额不足,即将下机!", 48, "警告"
End If
mrc.MoveNext
'更新line表
mrc3.Fields(8) = Date
mrc3.Fields(9) = Time
mrc3.Fields(12) = mrc2.Fields(7)
mrc3.Fields(10) = valueTime
mrc3.Fields(11) = pay
mrc3.Update
Next i
End Sub