所有学生下机循环MHSFlexGrade,不遍历OnLine表的方法——机房遇到问题(三)

前端之家收集整理的这篇文章主要介绍了所有学生下机循环MHSFlexGrade,不遍历OnLine表的方法——机房遇到问题(三)前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

机房正式看似简单,实则变化无穷啊!

一个下午一直想着怎么让所有学生下机!想了一个下午起初想的是要遍历两张表Online和Line,但是想想都这么困难。其实也难怪,我非要先做左右学生下机而不是所选学生下机,所以很是费劲。这就告诉我们一个问题:做事情要由易到难,不能好高骛远!切记!切记!切记!

之后,振华一语点醒梦中人,先做所选学生下机,然后将单个下机的重复只遍历Online表就好了!也是这句话打开了我的思路,突然想到那么我要是循环一下MHFlexGrade这个控件所选出的记录是不是也可以呢?只要知道选出多少记录就行啦啊!于是就有以下代码

'用来存放系统当前时间
Dim OffTime As Variant
Dim OffDate As Variant
'存放转化后的下机时间
Dim OffDate2 As Single
Dim OffTime2 As Single
'下机时间之和
Dim OffTimeSUM As Single
'存放上机时间
Dim OnTime As Single
Dim OnDate As Single
'上机时间之和
Dim OnTimeSUM As Single
'存放消费、每小时的费用和余额
Dim Consume As Currency
Dim ConsumeAnHour As Currency
Dim Cash As Currency
'存放消费时间和转化为分钟的消费时间
Dim ConsumeTime As Variant
Dim ConsumeTimem As Single
'每个数据库中的选择语句
Dim mrcl As ADODB.Recordset
Dim txtsqll As String
Dim mrco As ADODB.Recordset
Dim txtsqlo As String
Dim mrcb As ADODB.Recordset
Dim txtsqlb As String
Dim mrcs As ADODB.Recordset
Dim txtsqls As String
Dim MsgText As String
Private Sub allsoffMenu_Click()
    '定义cardno用来记录每条记录的卡号
    Dim cardNo As Single
    '用i来记录行数
    Dim i As Integer
    '总行数减一为记录的总数
    For i = 1 To myFlexGrid.Rows - 1 
        
            cardNo = Trim(myFlexGrid.TextMatrix(i,0))
            txtsqls = "select * from student_Info where cardno='" & cardNo & "'" & " and status='" & "使用" & "'"
            Set mrcs = Executesql(txtsqls,MsgText)
            txtsqll = "select * from Line_Info where cardno='" & cardNo & "'" & " and status='" & "正常上机" & "'"
            Set mrcl = Executesql(txtsqll,MsgText)
            txtsqlo = "select * from OnLine_Info where cardno='" & cardNo & "'"
            Set mrco = Executesql(txtsqlo,MsgText)
            txtsqlb = "select * from BasicData_Info"
            Set mrcb = Executesql(txtsqlb,MsgText)
            '取上机时间
            OnTime = CDate(Trim(mrco!OnTime))
            OnDate = CDate(Trim(mrco!OnDate))
            '取下机时间
            OffTime = Format(Now(),"hh:mm:ss")
            OffDate = Format(Now(),"yyyy-mm-dd")
            mrcl!OffTime = OffTime
            mrcl!OffDate = OffDate
            mrcl.Update
		    '计算下机总时间
            OffDate2 = CDate(Trim(mrcl!OffDate))
            OffTime2 = CDate(Trim(mrcl!OffTime))
            ConsumeTime = CDate((OffDate2 - OnDate) + (OffTime2 - OnTime))
            '计算下机时的总时间并转化为分钟
            OnTimeSUM = CDate(Trim(mrco!OnDate) + " " + Trim(mrco!OnTime))
            OffTimeSUM = CDate(Trim(mrcl!OffDate) + " " + Trim(mrcl!OffTime))
            ConsumeTimem = DateDiff("n",Trim(OnTimeSUM),Trim(OffTimeSUM))
	’时间的处理上一定有简化的方法,不过我试了好多次总是出错,所以就用这个比较笨的办法了
            '判断上机时间时候大于准备时间,若小于则花费为0
            If ConsumeTimem < mrcb!PrepareTime Then
                Consume = 0
                Cash = mrcs!Cash
                mrcl!OffTime = mrco!OnTime
                mrcl!OffDate = mrco!OnDate
                mrcl!ConsumeTime = 0
                mrcl!Status = "正常下机"
                mrcl.Update
            Else
                '判断用户类型
                If Trim(mrcs!Type) = "固定用户" Then
                    ConsumeAnHour = mrcb!Rate
                Else
                    ConsumeAnHour = mrcb!tmpRate
                End If
                '计算消费金额
                Consume = (ConsumeTimem / 60) * ConsumeAnHour
                '计算余额
                Cash = Trim(mrcl!Cash) - Consume
            End If
            '计算消费金额
            Consume = (ConsumeTimem / 60) * ConsumeAnHour
	     '计算余额
            Cash = Trim(mrcl!Cash) - Consume
            '向表中更新数据
            mrcl!ConsumeTime = ConsumeTime
            mrcl!Consume = Consume
            mrcl!Cash = Cash
            mrcl!Status = "正常下机"
            mrcl.Update
            mrcs!Cash = mrcl!Cash
            mrcs.Update
            mrco.Delete
            mrco.Update
            MsgBox "所有学生已下机",vbOKOnly + vbExclamation,"提示"
            '触发加载事件更新显示的数据
            Dim mrc As ADODB.Recordset
            Dim txtsql As String
            
            txtsql = "select * from OnLine_info "
            Set mrc = Executesql(txtsql,MsgText)
            
            With myFlexGrid
                .Rows = 1
                .CellAlignment = 4
                .TextMatrix(0,0) = "卡号"
                .TextMatrix(0,1) = "姓名"
                .TextMatrix(0,2) = "上机日期"
                .TextMatrix(0,3) = "上机时间"
                .TextMatrix(0,4) = "机房号"                
            End With
    Next
End Sub
望大家多多指正,小弟一定虚心接受,做改进!

猜你在找的VB相关文章