机房收费系统之上下机

Private Sub cmdOnLine_Click()
    Dim txtsql As String
    Dim Msgtext As String
    Dim mrcstu As ADODB.Recordset        '连接student表
    Dim mrcdata As ADODB.Recordset        '连接basicData表
    Dim mrcOnline As ADODB.Recordset        '连接Online表
    '判断卡号是否为空
    If Trim(txtCardNo.Text) = "" Then
        MsgBox "卡号为空，请先输入", 64, "温馨提示"
        txtCardNo.SetFocus
        Exit Sub
    End If
    '连接student表查看卡号是否存在
    txtsql = "select * from student_info where cardno='" & Trim(txtCardNo.Text) & "'"
    Set mrcstu = ExecuteSQL(txtsql, Msgtext)
    If mrcstu.EOF = True Then
        MsgBox "该卡号不存在，请先去注册！", 64, "温馨提示"
        txtCardNo.Text = ""
        txtCardNo.SetFocus
        Exit Sub
    End If
    '连接Basicdata表判断卡内余额是否大于最小金额
    txtsql = "select * from BasicData_info"
    Set mrcdata = ExecuteSQL(txtsql, Msgtext)
    If Val(mrcstu.Fields(7)) < Val(mrcdata.Fields(5)) Then
        MsgBox "卡内余额不足，请先去充值！", 64, "温馨提示"
        txtCardNo.Text = ""
        txtCardNo.SetFocus
        Exit Sub
    End If
    '连接Online表判断该号是否已上线
    txtsql = "select * from online_info where cardno='" & Trim(txtCardNo.Text) & "'"
    Set mrcOnline = ExecuteSQL(txtsql, Msgtext)
    If mrcOnline.EOF = False Then
        MsgBox "该卡已经上机！", 64, "温馨提示"
        txtCardNo.Text = ""
        txtCardNo.SetFocus
        Exit Sub
    Else
        '将数据写入Online表
        mrcOnline.AddNew
        mrcOnline.Fields(0) = mrcstu.Fields(0)    '卡号
        mrcOnline.Fields(1) = mrcstu.Fields(14)    '用户类型
        mrcOnline.Fields(2) = mrcstu.Fields(1)    '学号
        mrcOnline.Fields(3) = mrcstu.Fields(2)    '姓名
        mrcOnline.Fields(4) = mrcstu.Fields(4)    '系别
        mrcOnline.Fields(5) = mrcstu.Fields(3)    '性别
        mrcOnline.Fields(6) = Date   '上线日期
        mrcOnline.Fields(7) = Time   '上线时间
        mrcOnline.Fields(8) = Trim(VBA.Environ("computername"))   '电脑名称
        mrcOnline.Fields(9) = Date + Time '日期和时间
        rem:我暂时没有将上机信息写入line表，等下机的时候再写入
        '将student表内的数据展示出来
        txtType.Text = Trim(mrcstu.Fields(14))   '类型
        txtSID.Text = Trim(mrcstu.Fields(1))    '学号
        txtName.Text = Trim(mrcstu.Fields(2))       '姓名
        txtDept.Text = Trim(mrcstu.Fields(4))       '系别
        txtSex.Text = Trim(mrcstu.Fields(3))    '性别
        lblOnDate.Text = Trim(mrcline.Fields(6))     '上机日期
        lblontime.Text = Trim(mrcline.Fields(7))     '上机时间
        lblBaLance.Text = Trim(mrcstu.Fields(7))    '余额
        MsgBox "上机成功", 64, "温馨提示"
        mrcdata.Close
        mrcstu.Close
        mrcOnline.Update
        mrcOnline.Close
    End If
End Sub

Private Sub cmdOffLine_Click()
    '消费时间
    Dim consumetime As String
    Dim txtsql As String
    Dim Msgtext As String
    Dim Day, Minute As String           '计算日期差与时间差
    Dim mrcstu As ADODB.Recordset       '连接student表
    Dim mrcdata As ADODB.Recordset      '连接BasicData表
    Dim mrcOnline As ADODB.Recordset    '连接Online表
    Dim mrcline As ADODB.Recordset      '连接line表
     '判断卡号是否为空
    If Trim(txtCardNo.Text) = "" Then
        MsgBox "卡号为空", 64, "温馨提示"
        txtCardNo.SetFocus
        Exit Sub
    End If
    '判断该号是否上机
    txtsql = "select * from OnLine_info where cardno='" & Trim(txtCardNo.Text) & "'"
    Set mrcOnline = ExecuteSQL(txtsql, Msgtext)
    '连接student表
    txtsql = "select * from student_info where cardno='" & Trim(txtCardNo.Text) & "'"
    Set mrcstu = ExecuteSQL(txtsql, Msgtext)
    If mrcOnline.EOF = True Then
        MsgBox "该号没有上机", 64, "温馨提示"
        txtCardNo.Text = ""
        txtCardNo.SetFocus
    Else
            '计算消费时间
'           lblCTime.Text = Abs(Val(DateDiff("n", Trim(mrcOnline!Date), Now))) '把日期差转换成分钟
            Day = DateDiff("d", mrcOnline.Fields(6), Date)       '计算上机下机的日期差
            Minute = DateDiff("n", mrcOnline.Fields(7), Time)        '计算上机下机的同日时间差
            lblCTime.Text = 3600 * Val(Day) + Minute              '计算总时间差，分钟为单位
            '判断是否小于准备时间
            consumetime = lblCTime.Text
'            mrcOnline.Update
            txtsql = "select * from BasicData_info"
            Set mrcdata = ExecuteSQL(txtsql, Msgtext)
             '判断是否小于准备时间
            If consumetime < mrcdata.Fields(4) Then
                lblCMoney.Text = "0"
                lblCTime.Text = "0"
                lbloffDate.Text = Date
                lblOffTime.Text = Time
                lblBaLance.Text = Trim(mrcstu.Fields(7))
            Else
            '判断是否小于最小上机时间
                '判断消费时间小于最小上机时间时消费金额为0
                If consumetime < mrcdata!leasttime Then
                    lblCMoney.Text = "0" '消费金额为0元
                    lblCTime.Text = "0"
                    lbloffDate.Text = Date
                    lblOffTime.Text = Time
                    lblBaLance.Text = Trim(mrcstu.Fields(7))
                Else
                    If Trim(mrcOnline.Fields(1)) = "固定用户" Then
                        'cdbl将时间转换成double类型，round是将数据四舍五入，保留两位小数
                        lblCMoney.Text = Round((mrcdata.Fields(0) / 60 * consumetime))
                        lblBaLance.Text = Val(mrcstu.Fields(7)) - Val(lblCMoney.Text)
                    Else
                        lblCMoney.Text = Round((mrcdata.Fields(1) / 60 * consumetime)) '计算临时用户金额
                        lblBaLance.Text = Val(mrcstu.Fields(7)) - Val(lblCMoney.Text)
                        mrcstu.Fields(7) = Trim(lblBaLance.Text)
                        mrcstu.Fields(11) = "未结账"
                    End If
                    txtsql = "select * from line_info where cardno='" & Trim(txtCardNo.Text) & "'"
                    Set mrcline = ExecuteSQL(txtsql, Msgtext)
                    mrcline.AddNew
                    mrcline.Fields(1) = mrcOnline.Fields(0)         '卡号
                    mrcline.Fields(2) = mrcOnline.Fields(2)      '学号
                    mrcline.Fields(3) = mrcOnline.Fields(3)      '姓名
                    mrcline.Fields(4) = mrcOnline.Fields(1)        '系别
                    mrcline.Fields(5) = mrcOnline.Fields(5)        '性别
                    mrcline.Fields(6) = mrcOnline.Fields(6)       '上机日期
                    mrcline.Fields(7) = mrcOnline.Fields(7)        '上机时间
                    mrcline.Fields(8) = Date        '下机日期
                    mrcline.Fields(9) = Time        '下机时间
                    mrcline.Fields(10) = consumetime       '消费时间
                    mrcline.Fields(11) = Trim(lblCMoney.Text)        '消费金额
                    mrcline.Fields(12) = Trim(lblBaLance.Text)       '余额
                    mrcline.Fields(13) = "正常下机"
                    mrcline.Fields(14) = Trim(VBA.Environ("computername"))
                    mrcline.Update
                    mrcstu.Fields(7) = Trim(lblBaLance.Text)
                    mrcstu.Fields(11) = "未结账"
                    mrcstu.Update
                    '显示数据
                    '将student表内的数据展示出来
                    txtType.Text = Trim(mrcstu.Fields(14))   '类型
                    txtSID.Text = Trim(mrcstu.Fields(1))    '学号
                    txtName.Text = Trim(mrcstu.Fields(2))       '姓名
                    txtDept.Text = Trim(mrcstu.Fields(4))       '系别
                    txtSex.Text = Trim(mrcstu.Fields(3))    '性别
                    lblOnDate.Text = Trim(mrcline.Fields(6))     '上机日期
                    lblontime.Text = Trim(mrcline.Fields(7))     '上机时间
                    '计算消费金额
                    lbloffDate.Text = Date '下机日期
                    lblOffTime.Text = Time '下机时间
                    MsgBox "下机成功", 64, "温馨提示"
                    mrcOnline.Delete
            End If
        End If
    End If
End Sub