机房收费--学生上机状态

 前言：本节中主要是下机的运用，查询只是调用其中的组合查询，显示全部只是将数据库中的内容显示出来。

生成模式

全部下线

'让所有学生下线
    Dim OnlineSQL As String, LineSQL As String, BasicDataSQL As String, studentSQL As String
    Dim mrcBasicData As ADODB.Recordset
    Dim mrcLine As ADODB.Recordset
    Dim mrcStudent As ADODB.Recordset
    Dim mrcOnline As ADODB.Recordset

    Dim OMsgtext As String, LMsgtext As String, BMsgtext As String, Smsgtext As String
    Dim intLineTime As Integer
    Dim intConsumeTime As Integer
    Dim curConsume As Currency
    Dim curBalance As Currency

    Dim CostDate As Integer
    Dim CostTime As Integer



    '删除online表中的信息，让所有的学生下线
    OnlineSQL = "select * from online_Info "
    Set mrcOnline = ExecuteSQL(OnlineSQL, OMsgtext)
    '如果没有人上线，提示
    If mrcOnline.RecordCount = 0 Then
        MsgBox "没有学生上线！", vbOKOnly + vbInformation, "提示"
        Exit Sub
    End If
    a = MSHFlexGrid1.Rows - 1
    If MSHFlexGrid1.Rows = 0 Then
        MsgBox "没有学生上线！", vbOKOnly + vbInformation, "提示"
        Exit Sub
    End If

    MSHFlexGrid1.Rows = 1
    For b = a To 1 Step -1
        '从basicdata表中读取数据
        BasicDataSQL = "select * from basicdata_Info"
        Set mrcBasicData = ExecuteSQL(BasicDataSQL, BMsgtext)

        '从student表中读取数据
        studentSQL = "select * from student_Info"
        Set mrcStudent = ExecuteSQL(studentSQL, Smsgtext)

        '从line表中读取数据
        LineSQL = "select * from line_Info"
        Set mrcLine = ExecuteSQL(LineSQL, LMsgtext)

        '从online表中读取数据
        OnlineSQL = "select * from online_Info"
        Set mrcOnline = ExecuteSQL(OnlineSQL, OMsgtext)

        CostTime = DateDiff("n", Trim(Hour(Time)), Trim(mrcOnline!OnTime))
        CostDate = DateDiff("n", Trim(Date), Trim(mrcOnline!OnDate))
        intLineTime = Format(Str(Val(CostTime) + Val(CostDate)))



        '判断实际在线时间是否小于准备时间，若小于则消费时间为0
        If intLineTime < mrcBasicData!prepareTime Then
            intConsumeTime = 0
         '判断实际在线时间是否小于最低消费时间，若小于，则消费时间为最低消费时间
         ElseIf intLineTime < mrcBasicData!leastTime Then
            intConsumeTime = mrcBasicData!leastTime
         Else
            intConsumeTime = Round(intLineTime / mrcBasicData!unitTime) * mrcBasicData!unitTime 'round()函数为四舍五入函数
         End If

         '计算消费金额（消费金额=消费时间/30*用户类型的半小时费用)
         '固定用户(为什么要除以30）
         If Trim(mrcStudent.Fields(14)) = "固定用户" Then
            curConsume = intConsumeTime / 30 * mrcBasicData!Rate
        Else

            curConsume = intConsumeTime / 30 * mrcBasicData!tmpRate
         End If

        '计算余额（账户余额=原账户余额-消费金额）
        curBalance = mrcStudent!cash - curConsume

         '更新学生信息表的余额
         mrcStudent.Fields(7) = curBalance
         mrcStudent.Update
         mrcStudent.Close

         '更新上机记录表（line表）
         LineSQL = "select * from line_Info"
         Set mrcLine = ExecuteSQL(LineSQL, LMsgtext)
         mrcLine.AddNew
         mrcLine.Fields(1) = Trim(mrcOnline!cardno)
         mrcLine.Fields(2) = Trim(mrcOnline!studentNo)
         mrcLine.Fields(3) = Trim(mrcOnline!studentName)
         mrcLine.Fields(4) = Trim(mrcOnline!Department)
         mrcLine.Fields(5) = Trim(mrcOnline!Sex)
         mrcLine.Fields(6) = Trim(mrcOnline!OnDate)

         mrcLine.Fields(7) = Trim(mrcOnline!OnTime)
         mrcLine.Fields(8) = Date
         mrcLine.Fields(9) = Trim(Time & "")
         mrcLine.Fields(10) = intConsumeTime

         mrcLine.Fields(11) = curConsume
         mrcLine.Fields(12) = curBalance
         mrcLine.Fields(13) = "强制下机"
         mrcLine.Fields(14) = Trim(Environ("computername"))
         mrcLine.Update

         '删除在线表中的信息
         OnlineSQL = "select * from online_Info"
         Set mrcOnline = ExecuteSQL(OnlineSQL, OMsgtext)
         mrcOnline.Delete
         mrcOnline.Update

         Next b
         MsgBox "所有学生已下线！", vbOKOnly + vbInformation, "提示"

选中下机

  Dim OnlineSQL As String, LineSQL As String, BasicDataSQL As String, studentSQL As String
    Dim mrcBasicData As ADODB.Recordset
    Dim mrcLine As ADODB.Recordset
    Dim mrcStudent As ADODB.Recordset
    Dim mrcOnline As ADODB.Recordset

    Dim OMsgtext As String, LMsgtext As String, BMsgtext As String, Smsgtext As String
    Dim intLineTime As Integer
    Dim intConsumeTime As Integer
    Dim curConsume As Currency
    Dim curBalance As Currency

         '连接学生表
        studentSQL = "select * from student_Info"
        Set mrcStudent = ExecuteSQL(studentSQL, Smsgtext)

         '连接在线表
        OnlineSQL = "select * from online_Info"
        Set mrcOnline = ExecuteSQL(OnlineSQL, OMsgtext)

         '连接line表
        LineSQL = "select * from line_Info"
        Set mrcLine = ExecuteSQL(LineSQL, LMsgtext)

         '连接基础设置表
        BasicDataSQL = "select * from basicdata_Info"
        Set mrcBasicData = ExecuteSQL(BasicDataSQL, BMsgtext)


        If MSHFlexGrid1.RowSel = 0 Then   '为一定范围的单元格返回或设置的起始行和或终止行
            MsgBox "请选择学生！", vbOKOnly + vbInformation, "提示"
            Exit Sub
        End If

        If MSHFlexGrid1.Rows = 0 Then
            MsgBox "请选择学生下机！", vbOKOnly + vbInformation, "提示"
            Exit Sub
        End If



        MSHFlexGrid1.RemoveItem MSHFlexGrid1.RowSel
         '选中学生
         '判断实际在线时间是否小于准备时间，若小于则消费时间为0
          If intLineTime < mrcBasicData!prepareTime Then
            intConsumeTime = 0
         '判断实际在线时间是否小于最低消费时间，若小于，则消费时间为最低消费时间
         ElseIf intLineTime < mrcBasicData!leastTime Then
            intConsumeTime = mrcBasicData!leastTime
         Else
            intConsumeTime = Round(intLineTime / mrcBasicData!unitTime) * mrcBasicData!unitTime 'round()函数为四舍五入函数
         End If



         '计算消费金额（消费金额=消费时间/30*用户类型的半小时费用)
         '固定用户收费单位半小时
         If Trim(mrcStudent.Fields(14)) = "固定用户" Then
            curConsume = intConsumeTime / 30 * mrcBasicData!Rate
        Else
            curConsume = intConsumeTime / 30 * mrcBasicData!tmpRate
         End If


        '计算余额（账户余额=原账户余额-消费金额）
        curBalance = mrcStudent!cash - curConsume

        '更新学生信息表的余额
         mrcStudent.Fields(7) = curBalance
         mrcStudent.Update
         mrcStudent.Close

          '更新上机记录表（line表）
         LineSQL = "select * from line_Info"
         Set mrcLine = ExecuteSQL(LineSQL, LMsgtext)

         '从online表中读取数据
         OnlineSQL = "select * from online_Info"
         Set mrcOnline = ExecuteSQL(OnlineSQL, LMsgtext)
         '更新line表
         mrcLine.AddNew
         mrcLine.Fields(1) = Trim(mrcOnline!cardno)
         mrcLine.Fields(2) = Trim(mrcOnline!studentNo)
         mrcLine.Fields(3) = Trim(mrcOnline!studentName)
         mrcLine.Fields(4) = Trim(mrcOnline!Department)
         mrcLine.Fields(5) = Trim(mrcOnline!Sex)
         mrcLine.Fields(6) = Trim(mrcOnline!OnDate)

         mrcLine.Fields(7) = Trim(mrcOnline!OnTime)
         mrcLine.Fields(8) = Date
         mrcLine.Fields(9) = Trim(Time)
         mrcLine.Fields(10) = intConsumeTime

         mrcLine.Fields(11) = curConsume
         mrcLine.Fields(12) = curBalance
         mrcLine.Fields(13) = "正常下机"
         mrcLine.Fields(14) = Trim(Environ("computername"))
         mrcLine.Update

         '删除在线表中的信息
         OnlineSQL = "select * from online_Info"
         Set mrcOnline = ExecuteSQL(OnlineSQL, OMsgtext)
         mrcOnline.Delete
         mrcOnline.Update
         MsgBox "选中学生已下机！", vbOKOnly + vbInformation, "提示"

总结

  全部下机相对简单一些，因为只是将余额整理出来，将在线表去掉，更新学生表余额，还要上机记录表即可；但是选中下机时候会出现和控件之间的联系，不能出现将上面删除掉，并且选中的部分要去除掉，这个有一些代码上的问题，其余的都一样，但是师父验收的时候也没有细看这些，还是需要在总结一番。