【机房收费系统】——（全部下机，选择下机）

  一直在全部下机和选择下机卡着，这几天突然来了灵感就写了写，感觉主要问题出在自己没有沉下心来思考，光想不实践感觉这样实现不了，其实写出来就已经成功一半了，人的思想和程序的思想还是有些区别的。

Private Sub EverthingOffline_Click()
    
    Dim onlinemrc As ADODB.Recordset
    Dim onlinetext As String
    Dim onlinesql As String
    
    Dim linemrc As ADODB.Recordset
    Dim linetext As String
    Dim linesql As String

    Dim studentmrc As ADODB.Recordset
    Dim studenttext As String
    Dim studentsql As String
    
    Dim BasicDatamrc As ADODB.Recordset
    Dim BasicDatatext As String
    Dim BasicDatasql As String
    
    Dim balance As Integer  '余额
    Dim Money As Integer    '消费金额
    Dim consumetime As Integer  '消费时间
    Dim n As Timer
    
    '查看表格控件
    If MsShow.Rows <= 1 Then
          MsgBox "没有卡号在上机，请重试!", vbOKOnly + vbExclamation, "温馨提示"
          Exit Sub
    End If
    
    '查询上机表
    onlinesql = "select * from OnLine_info where cardno = '" & MsShow.TextMatrix(1, 0) & "'"
    Set onlinemrc = ExecuteSQL(onlinesql, onlinetext)
    
    '计算消费时间
    consumetime = Val(DateDiff("n", onlinemrc.Fields(7), Time))
    
    '查询学生表
    studentsql = "select * from student_info where cardno = '" & MsShow.TextMatrix(1, 0) & "'"
    Set studentmrc = ExecuteSQL(studentsql, studenttext)
    
    '查询基础数据表
    BasicDatasql = "select * from BasicData_info "
    Set BasicDatamrc = ExecuteSQL(BasicDatasql, BasicDatatext)

    '查询下机表
    linesql = "select * from Line_Info"
    Set linemrc = ExecuteSQL(linesql, linetext)
    
    '消费时间最少上机时间，用户类型为临时用户
    If (Val(consumetime) < Val(BasicDatamrc.Fields(2)) And MsShow.TextMatrix(1, 5) = "临时用户") Then

        Money = Val(BasicDatamrc.Fields(1))   '消费金额=临时用户的基础消费
        balance = Val(onlinemrc.Fields(8)) - Val(Money)  '余额=初始金额-消费金额
    
        linemrc.AddNew  '在下机表写入上机表的数据
            linemrc.Fields(0) = onlinemrc.Fields(1)
            linemrc.Fields(1) = onlinemrc.Fields(0)
            linemrc.Fields(2) = onlinemrc.Fields(2)
            linemrc.Fields(3) = onlinemrc.Fields(3)
            linemrc.Fields(4) = onlinemrc.Fields(4)
            linemrc.Fields(5) = onlinemrc.Fields(5)
            linemrc.Fields(6) = onlinemrc.Fields(6)
            linemrc.Fields(7) = onlinemrc.Fields(7)
            linemrc.Fields(8) = Date
            linemrc.Fields(9) = Time
            linemrc.Fields(10) = Val(consumetime)
            linemrc.Fields(11) = Val(Money)
            linemrc.Fields(12) = Val(balance)
            linemrc.Fields(13) = "正常下机"
            
            studentmrc.Fields(7) = Val(balance)    '更新学生表的余额
            
            studentmrc.Update
            onlinemrc.Update
            linemrc.Update
         
         '下机删除上机表相应数据
        onlinesql = "delete from OnLine_Info where cardno = '" & MsShow.TextMatrix(1, 0) & "'"
        Set onlinemrc = ExecuteSQL(onlinesql, onlinetext)
        
        MsShow.RemoveItem 1
    
    End If
                 
     '消费时间最少上机时间，用户类型为固定用户
    If (Val(consumetime) < Val(BasicDatamrc.Fields(2)) And MsShow.TextMatrix(1, 5) = "固定用户") Then
        
            Money = Val(BasicDatamrc.Fields(0))   '消费金额=固定用户的基础消费
            balance = Val(onlinemrc.Fields(8)) - Val(Money)  '余额=初始金额-消费金额
        
            linemrc.AddNew  '在下机表写入上机表的数据
                linemrc.Fields(0) = onlinemrc.Fields(1)
                linemrc.Fields(1) = onlinemrc.Fields(0)
                linemrc.Fields(2) = onlinemrc.Fields(2)
                linemrc.Fields(3) = onlinemrc.Fields(3)
                linemrc.Fields(4) = onlinemrc.Fields(4)
                linemrc.Fields(5) = onlinemrc.Fields(5)
                linemrc.Fields(6) = onlinemrc.Fields(6)
                linemrc.Fields(7) = onlinemrc.Fields(7)
                linemrc.Fields(8) = Date
                linemrc.Fields(9) = Time
                linemrc.Fields(10) = Val(consumetime)
                linemrc.Fields(11) = Val(Money)
                linemrc.Fields(12) = Val(balance)
                linemrc.Fields(13) = "正常下机"
                
                studentmrc.Fields(7) = Val(balance)    '更新学生表的余额
                
                studentmrc.Update
                onlinemrc.Update
                linemrc.Update
             
             '下机删除上机表相应数据
            onlinesql = "delete from OnLine_Info where cardno = '" & MsShow.TextMatrix(1, 0) & "'"
            Set onlinemrc = ExecuteSQL(onlinesql, onlinetext)
            
            MsShow.RemoveItem 1
            
    End If
    
     '消费时间>上机时间，用户类型为临时用户
    If (Val(consumetime) > Val(BasicDatamrc.Fields(2)) And MsShow.TextMatrix(1, 5) = "临时用户") Then
            '消费金额=消费时间/递增时间*临时用户基础消费
            Money = Val(consumetime) / BasicDatamrc.Fields(2) * BasicDatamrc.Fields(1)
            '余额=初始金额-消费金额
            balance = Val(onlinemrc.Fields(8)) - Val(Money)
        
            linemrc.AddNew  '在下机表写入上机表的数据
                linemrc.Fields(0) = onlinemrc.Fields(1)
                linemrc.Fields(1) = onlinemrc.Fields(0)
                linemrc.Fields(2) = onlinemrc.Fields(2)
                linemrc.Fields(3) = onlinemrc.Fields(3)
                linemrc.Fields(4) = onlinemrc.Fields(4)
                linemrc.Fields(5) = onlinemrc.Fields(5)
                linemrc.Fields(6) = onlinemrc.Fields(6)
                linemrc.Fields(7) = onlinemrc.Fields(7)
                linemrc.Fields(8) = Date
                linemrc.Fields(9) = Time
                linemrc.Fields(10) = Val(consumetime)
                linemrc.Fields(11) = Val(Money)
                linemrc.Fields(12) = Val(balance)
                linemrc.Fields(13) = "正常下机"
                
                studentmrc.Fields(7) = Val(balance)    '更新学生表的余额
                
                studentmrc.Update
                onlinemrc.Update
                linemrc.Update
             
             '下机删除上机表相应数据
            onlinesql = "delete from OnLine_Info where cardno = '" & MsShow.TextMatrix(1, 0) & "'"
            Set onlinemrc = ExecuteSQL(onlinesql, onlinetext)
            
            MsShow.RemoveItem 1
            
    End If
     '消费时间>上机时间，用户类型为固定用户
    If (Val(consumetime) > Val(BasicDatamrc.Fields(2)) And MsShow.TextMatrix(1, 5) = "固定用户") Then
            '消费金额=消费时间/递增时间*固定用户基础消费
            Money = Val(consumetime) / BasicDatamrc.Fields(2) * BasicDatamrc.Fields(0)
            '余额=初始金额-消费金额
            balance = Val(onlinemrc.Fields(8)) - Val(Money)
        
            linemrc.AddNew  '在下机表写入上机表的数据
                linemrc.Fields(0) = onlinemrc.Fields(1)
                linemrc.Fields(1) = onlinemrc.Fields(0)
                linemrc.Fields(2) = onlinemrc.Fields(2)
                linemrc.Fields(3) = onlinemrc.Fields(3)
                linemrc.Fields(4) = onlinemrc.Fields(4)
                linemrc.Fields(5) = onlinemrc.Fields(5)
                linemrc.Fields(6) = onlinemrc.Fields(6)
                linemrc.Fields(7) = onlinemrc.Fields(7)
                linemrc.Fields(8) = Date
                linemrc.Fields(9) = Time
                linemrc.Fields(10) = Val(consumetime)
                linemrc.Fields(11) = Val(Money)
                linemrc.Fields(12) = Val(balance)
                linemrc.Fields(13) = "正常下机"
                
                studentmrc.Fields(7) = Val(balance)    '更新学生表的余额
                
                studentmrc.Update
                onlinemrc.Update
                linemrc.Update
             
             '下机删除上机表相应数据
            onlinesql = "delete from OnLine_Info where cardno = '" & MsShow.TextMatrix(1, 0) & "'"
            Set onlinemrc = ExecuteSQL(onlinesql, onlinetext)
            
            MsShow.RemoveItem 1
            
    End If
End Sub

Private Sub Query_Click()
    frmComputerstatistics.Show
End Sub

Private Sub show_Click()
    Dim mrc As ADODB.Recordset
    Dim msgtext As String
    Dim txtsql As String
    
    txtsql = "select * from onLine_Info "
    Set mrc = ExecuteSQL(txtsql, msgtext)

    
    MsShow.Clear
    With MsShow
        .Rows = 1
        .TextMatrix(0, 0) = "卡号"
        .TextMatrix(0, 1) = "姓名"
        .TextMatrix(0, 2) = "上机日期"
        .TextMatrix(0, 3) = "上机时间"
        .TextMatrix(0, 4) = "金额"
        .TextMatrix(0, 5) = "用户类型"
    End With
        
        '判断是否存在收取金额记录
        If mrc.EOF = True Then
            MsgBox "没有上机记录！", vbOKOnly + vbInformation, "提示"
            Exit Sub
        Else
            With MsShow
                '判断是否移动到数据集对象的最后一条记录
                Do While mrc.EOF = False
                    .Rows = .Rows + 1
                    .CellAlignment = 4
                    .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))
                    .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(3))
                    .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(6))
                    .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))
                    .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(8))
                    .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(1))
                    '移动到下一条记录
                    mrc.MoveNext
                Loop
            mrc.Close
            End With
       
         End If
End Sub

选择下机和全部下机的代码逻辑是大概是相同的。

Private Sub SelecOffline_Click()
    Dim onlinetext As String
    Dim onlineSQL As String
    Dim onlinemrc As ADODB.Recordset
    
    Dim BasicDatatext As String
    Dim BasicDataSQL As String
    Dim BasicDatamrc As ADODB.Recordset
    
    Dim linetext As String
    Dim lineSQL As String
    Dim linemrc As ADODB.Recordset
    
    Dim studenttext As String
    Dim studentSQL As String
    Dim studentmrc As ADODB.Recordset
    
    Dim balance As Integer  '余额
    Dim Money As Integer    '消费金额
    Dim consumetime As Integer  '消费时间
    Dim n As Timer
    
    If MSshow.TextMatrix(MSshow.Row, 6) = "" Then
        MsgBox "没有选中记录！", vbOKOnly + vbInformation, "提示"
    Else
        '连接上机表
        onlineSQL = "select * from online_info  where cardno = '" & MSshow.TextMatrix(1, 0) & "'"
        Set onlinemrc = ExecuteSQL(onlineSQL, onlinetext)
        
        '连接学生表
        studentSQL = "select * from student_info  where cardno = '" & MSshow.TextMatrix(1, 0) & "'"
        Set studentmrc = ExecuteSQL(studentSQL, studenttext)
        
        '连接基础数据表
        BasicDataSQL = "select * from BasicData_info"
        Set BasicDatamrc = ExecuteSQL(BasicDataSQL, BasicDatatext)
        
        '连接下机表
        lineSQL = "select * from line_info  "
        Set linemrc = ExecuteSQL(lineSQL, linetext)
        
        '计算消费时间
        consumetime = Val(DateDiff("n", onlinemrc.Fields(7), Time))
        
        '消费时间最少上机时间，用户类型为临时用户
        If (Val(consumetime) < Val(BasicDatamrc.Fields(2)) And MSshow.TextMatrix(1, 5) = "临时用户") Then

            Money = Val(BasicDatamrc.Fields(1))   '消费金额=临时用户的基础消费
            balance = Val(onlinemrc.Fields(8)) - Val(Money)  '余额=初始金额-消费金额
        
            linemrc.AddNew  '在下机表写入上机表的数据
                linemrc.Fields(0) = onlinemrc.Fields(1)
                linemrc.Fields(1) = onlinemrc.Fields(0)
                linemrc.Fields(2) = onlinemrc.Fields(2)
                linemrc.Fields(3) = onlinemrc.Fields(3)
                linemrc.Fields(4) = onlinemrc.Fields(4)
                linemrc.Fields(5) = onlinemrc.Fields(5)
                linemrc.Fields(6) = onlinemrc.Fields(6)
                linemrc.Fields(7) = onlinemrc.Fields(7)
                linemrc.Fields(8) = Date
                linemrc.Fields(9) = Time
                linemrc.Fields(10) = Val(consumetime)
                linemrc.Fields(11) = Val(Money)
                linemrc.Fields(12) = Val(balance)
                linemrc.Fields(13) = "正常下机"
                
                studentmrc.Fields(7) = Val(balance)    '更新学生表的余额
                
                studentmrc.Update
                onlinemrc.Update
                linemrc.Update
             
             '下机删除上机表相应数据
            onlineSQL = "delete from OnLine_Info where cardno = '" & MSshow.TextMatrix(1, 0) & "'"
            Set onlinemrc = ExecuteSQL(onlineSQL, onlinetext)
            
            MSshow.RemoveItem 1
    
        End If
    
    End If
    
End Sub