机房收费系统之上机、下机

    上机流程图：

 

对应的代码：

'#################################################################
'上机，将上机记录写入到上机记录表里
'#################################################################
Private Sub cmdOn_Click()
    Dim strSQL As String, strSQL2 As String, strSQL3 As String
    Dim strMsgText As String, strMsgText2 As String, strMsgText3 As String
    Dim objRst As ADODB.Recordset, objRst2 As ADODB.Recordset, objRst3 As ADODB.Recordset
    
    '让下机日期和时间，消费时间和金额为空
    txtOutDate.Text = ""
    txtOutTime.Text = ""
    txtPayTime.Text = ""
    txtPayMoney.Text = ""
    
    If txtCardNo.Text = "" Then
        '判断卡号是否为空
        MsgBox "请输入卡号！", vbOKOnly, "警告!"
        txtCardNo.SetFocus
        Exit Sub
    Else
        If Len(txtCardNo.Text) > 10 Then
            '判断输入卡号是否超过设定的长度，防止出错
            MsgBox "卡号过长，请输入长度<10的卡号", vbOKOnly, "警告!"
            txtCardNo.SetFocus
            Exit Sub
        End If
        '查询数据库里学生基本信息表
        Set objRst = New ADODB.Recordset
        strSQL = "select * from student_Info where cardNo='" & Trim(txtCardNo.Text) & "'"
        Set objRst = ExecuteSQL(strSQL, strMsgText)
        If objRst.BOF And objRst.EOF Then
            '判读该卡号是否存在
            MsgBox "该卡号未注册!", vbOKOnly, "警告!"
            txtCardNo.Text = ""
            txtCardNo.SetFocus
            Exit Sub
        Else
            '判断余额是否充足
            If objRst.Fields(3) < GetLeastMoney() Then
                MsgBox "余额只有" & objRst.Fields(3) & ",少于最少金额，请先充值！", vbOKOnly, "警告!"
                Exit Sub
            Else
            
                '判断该卡号是否正在上机
                Set objRst3 = New ADODB.Recordset
                strSQL3 = "select * from online_Info where cardNo='" & Trim(txtCardNo.Text) & "' and outDate is null"
                Set objRst3 = ExecuteSQL(strSQL3, strMsgText3)
                If Not (objRst3.BOF And objRst3.EOF) Then
                    Label1.Caption = "该卡正在上机!"
                    txtCardNo.SetFocus
                    Exit Sub
                Else
                
                    '显示该卡号的一些基本信息
                    txtStudentNo.Text = checkField(objRst.Fields(0))
                    txtDepartment.Text = checkField(objRst.Fields(4))
                    txtType.Text = checkField(objRst.Fields(14))
                    txtStudentName.Text = checkField(objRst.Fields(1))
                    txtSex.Text = checkField(objRst.Fields(7))
                    txtOnDate.Text = Date
                    'onTime = Time
                    txtOnTime.Text = Time
                    txtAllCash.Text = checkField(objRst.Fields(3))
            
                    '将上机前的余额提出来，用于下机时计算余额
                    'curAllCash = checkField(objRst.Fields(3))
                    Label1.Caption = "欢迎光临！"
            
                    '将该卡上机的信息填入到online_Info表里
                    Set objRst2 = New ADODB.Recordset
                    strSQL2 = "select * from online_Info "
                    Set objRst2 = ExecuteSQL(strSQL2, strMsgText2)
                    'objRst2.MoveLast
                    objRst2.AddNew
                    objRst2.Fields(0) = txtCardNo.Text
                    objRst2.Fields(1) = txtStudentName.Text
                    objRst2.Fields(2) = Date
                    objRst2.Fields(3) = Time
                    objRst2.Fields(4) = Null
                    objRst2.Fields(5) = Null
                    objRst2.Update
                    '查询此时正在上机的人数。（可以直接加1）
                    lblPeopleCount.Caption = GetPeopleCount()
                    PeopleCount = GetPeopleCount()   '保存正在上机的人数
                    objRst2.Close
            
                    objRst.Close
                End If
            End If
        End If
    End If
    
            
        
End Sub

 

下机的流程图：

 

 

对应的代码：

 

'#####################################################################
'下机,计算余额，将余额写入学生信息表里，和上机信息表里
'#####################################################################
Private Sub cmdOff_Click()

    Dim strSQL As String, strSQL2 As String, strSQL3 As String
    Dim strMsgText As String, strMsgText2 As String, strMsgText3 As String
    Dim objRst As ADODB.Recordset, objRst2 As ADODB.Recordset, objRst3 As ADODB.Recordset
    Dim intTime As Single
    Dim fixedRate As Single
    Dim AllMoney As Currency
    Dim pay As Currency


    If txtCardNo.Text = "" Then
        '判断卡号是否为空
        MsgBox "请输入卡号！", vbOKOnly, "警告!"
        txtCardNo.SetFocus
        Exit Sub
    Else
        If Len(txtCardNo.Text) > 10 Then
            '判断输入卡号是否超过设定的长度，防止出错
            MsgBox "卡号过长，请输入长度<10的卡号", vbOKOnly, "警告!"
            txtCardNo.SetFocus
            Exit Sub
        End If
    End If
    Set objRst = New ADODB.Recordset
    strSQL = "select * from student_Info where cardNo='" & Trim(txtCardNo.Text) & "'"
    Set objRst = ExecuteSQL(strSQL, strMsgText)
    If objRst.BOF And objRst.EOF Then
        '判读该卡号是否存在
        MsgBox "该卡号未注册!", vbOKOnly, "警告!"
        txtCardNo.Text = ""
        txtCardNo.SetFocus
        Exit Sub
        
    End If
    objRst.Close
    strSQL = ""
    strMsgText = ""
    
    
    
    Set objRst = New ADODB.Recordset
    strSQL = "select * from online_Info where cardno='" & Trim(txtCardNo.Text) & "' and outDate is null "
    Set objRst = ExecuteSQL(strSQL, strMsgText)
    If objRst.BOF And objRst.EOF Then
        '判断该卡是否正在上机
        Label1.Caption = "该卡没有上机！"
        txtCardNo.SetFocus
        Exit Sub
    Else
        '显示下机的一些信息
        txtOutDate.Text = Date
        outTime = Time
        txtOutTime.Text = outTime
        
        onTime = CDate(Format(objRst.Fields("onTime"), "hh:mm:ss"))
    
        txtCardNo.Text = checkField(objRst.Fields("cardNo"))
        txtStudentName.Text = checkField(objRst.Fields("studentName"))
        txtOnDate.Text = checkField(objRst.Fields("onDate"))
        txtOnTime.Text = checkField(objRst.Fields("onTime"))
        
        Set objRst3 = New ADODB.Recordset
        strSQL3 = "select * from student_Info where cardNo='" & txtCardNo.Text & "'"
        Set objRst3 = ExecuteSQL(strSQL3, strMsgText3)
        txtStudentNo.Text = objRst3.Fields("studentNo")
        txtDepartment.Text = objRst3.Fields("department")
        txtSex.Text = objRst3.Fields("sex")
        txtType.Text = "固定用户"
        
        '删除该记录，将此时信息填入online_Info表里
        objRst.Delete
        objRst.AddNew
        objRst.Fields(0) = txtCardNo.Text
        objRst.Fields(1) = txtStudentName.Text
        objRst.Fields(2) = txtOnDate.Text
        objRst.Fields("onTime") = txtOnTime.Text
        objRst.Fields(4) = txtOutDate.Text
        objRst.Fields("outTime") = txtOutTime.Text
        objRst.Fields(9) = "正常下机"
        'intTime = Val(txtOutTime.Text - txtOnTime.Text)
        
        '计算上机的时间
        intTime = (outTime - onTime) * 24 * 2
                
         Set objRst2 = New ADODB.Recordset
         strSQL2 = "select * from basicDate_Info "
         Set objRst2 = ExecuteSQL(strSQL2, strMsgText2)
         '查询固定用户30分钟的费用
         fixedRate = Val(objRst2.Fields(0))
         If intTime < (GetPreparTime() / 30) Then
             '判断上机时间是否超过了准备时间，没超过则花费为0
             objRst.Fields("pay") = 0
             objRst.Fields("allCash") = GetAllMoney(txtCardNo.Text)
         Else
             If intTime <= (GetLeastTime() / 30) Then
                 '判断上机时间是否超过半个小时，没则当成已经上了30分钟
                 objRst.Fields("pay") = fixedRate
                 AllMoney = GetAllMoney(txtCardNo.Text) - fixedRate
                 objRst.Fields("allCash") = AllMoney     '将余额写入上机记录表里
                 WriteAllMoney txtCardNo.Text, AllMoney  '将余额写入学生基本信息表里
             Else
                 If intTime > Int(intTime) Then
                     intTime = Int(intTime) + 1
                 Else
                     intTime = Int(intTime)
                 End If
                 pay = fixedRate * intTime
                 objRst.Fields("pay") = pay
                 AllMoney = GetAllMoney(txtCardNo.Text) - pay
                 objRst.Fields("allCash") = AllMoney    '将余额写入上机记录表里
                 WriteAllMoney txtCardNo.Text, AllMoney  '将余额写入学生基本信息表里
             End If
         End If
         
         '显示消费的时间金额，和余额
         txtPayTime.Text = Format(outTime - onTime, "hh-mm-ss")
         txtPayMoney.Text = objRst.Fields(6)
         txtAllCash.Text = GetAllMoney(txtCardNo.Text)     '- objRst.Fields(6)
         Label1.Caption = "欢迎下次再来!"
         
         objRst.Fields(7) = txtAllCash.Text    '将余额写到online_Info表里
         
         lblPeopleCount.Caption = GetPeopleCount()  '显示正在上机人数
         PeopleCount = GetPeopleCount()
         objRst.Update
        
         objRst.Close
         objRst2.Close
         objRst3.Close
         
    End If
    
        
End Sub

 

其中，有一些是自己写的函数。GetPeopleCount()是取得正在上机的函数，GetAllMoney(txtCardNo.Text)是取得余额的函数，GetLeastTime()是取得至少上机时间的函数，GetPreparTime()是取得准备上机时间，GetFixedRate()是取得固定用户30分钟的费用。