机房收费系统——下机

关于下机，明确几个步骤，就可以轻易的实现这个功能，1.连接数据库的哪几个表。2.考虑各个表在下机中扮演什么角色。3.细心的去算钱，就OK了。

• 连接student_info，online_info，line_info，basicdata_info

• 流程

1. student_info——判断是否上机

2. online_info——判断是否在线

3. line_info——添加下机记录

4. basicdata_info——结账

• 结账

公式：

消耗时间=    下机日期-上机日期（以分钟为单位）

消费金额=  消耗时间 * 单位计费

得到时间差，利用了datediff函数，格式如下：
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
其中 interval表示，计算差值的类型
yyyy-年；q-季 ；m-月 ；y-一年的日数 ；
d-日 ；w-一周的日数 ；ww-周 ；
h-时 ；n-分钟 ；s-秒

耗费时间代码展示如下：

lblCTime.Caption = DateDiff("n", Omrc!ondate, Date) + DateDiff("n", Omrc!OnTime, Time)

耗费金额代码展示如下：

注：区分临时用户和固定用户，因为两者的单位计费不同

'在学生表中查询记录是否为固定用户
StudentSQL = "select * from student_info where type='固定用户'"
Set Smrc = ExecuteSQL(StudentSQL, SMsgText)
'在基础数据表中查询不同类型用户的单位计费金额
BasicSQL = "select * from basicdata_info"
Set Bmrc = ExecuteSQL(BasicSQL, BMsgText)

    If Smrc.EOF = True Then
        lblCMoney.Caption = Bmrc!Rate * Trim(lblCTime.Caption)
    Else
        lblCMoney.Caption = Bmrc!tmprate * Trim(lblCTime.Caption)
    End If

下机的重点部分如上，具体的详细代码如下（仅供参考）

Private Sub cmdOffLine_Click()
    StudentSQL = "select * from student_info where status='使用' and cardno='" & Trim(txtCardNo.Text) & "'"
    Set Smrc = ExecuteSQL(StudentSQL, SMsgText)
    
    If Smrc.EOF = True Then
        MsgBox "该卡号尚未注册或已停用，请重新输入卡号！", 0 + 48, "警告"
        txtCardNo.Text = ""
        txtCardNo.SetFocus
    Else
        lblSID.Caption = Smrc!studentno
        lblDept.Caption = Smrc!department
        lblType.Caption = Smrc!Type
        lblName.Caption = Smrc!studentname
        lblSex.Caption = Smrc!sex
        
        OnlineSQL = "select * from online_info where cardno='" & Trim(txtCardNo.Text) & "'"
        Set Omrc = ExecuteSQL(OnlineSQL, OMsgText)
        
        If Omrc.EOF = True Then
            MsgBox "该用户未下机或已下机,请重新输入卡号！", 0 + 48, "警告"
            txtCardNo.Text = ""
            txtCardNo.SetFocus
            lblSID.Caption = ""
            lblDept.Caption = ""
            lblType.Caption = ""
            lblName.Caption = ""
            lblSex.Caption = ""
            lblOnTime.Caption = ""
            lblOnDate.Caption = ""
            lblOffTime.Caption = ""
            lblOffDate.Caption = ""
            lblBalance.Caption = ""
            lblCMoney.Caption = ""
            lblCTime.Caption = ""
        Else
            lblOnDate.Caption = Omrc!ondate
            lblOnTime.Caption = Omrc!OnTime
            lblOffTime.Caption = Time
            lblOffDate.Caption = Date
            lblCTime.Caption = DateDiff("n", Omrc!ondate, Date) + DateDiff("n", Omrc!OnTime, Time)
            
            StudentSQL = "select * from student_info where type='固定用户'"
            Set Smrc = ExecuteSQL(StudentSQL, SMsgText)
            
            BasicSQL = "select * from basicdata_info"
            Set Bmrc = ExecuteSQL(BasicSQL, BMsgText)
            
                If Smrc.EOF = True Then
                    lblCMoney.Caption = Bmrc!Rate * Trim(lblCTime.Caption)
                Else
                    lblCMoney.Caption = Bmrc!tmprate * Trim(lblCTime.Caption)
                End If
            
            lblBalance.Caption = Smrc!cash - Trim(lblCMoney.Caption)
            
            LineSQL = "select * from line_info where cardno='" & Trim(txtCardNo.Text) & "'"
            Set Lmrc = ExecuteSQL(LineSQL, LMsgText)
                Lmrc!consumetime = Trim(lblCTime.Caption)
                Lmrc!consume = Trim(lblCMoney.Caption)
                Lmrc!cash = Trim(lblBalance.Caption)
                Lmrc!Status = "正常下机"
                Lmrc.Update
            
            OnlineSQL = "delete from online_info where cardno='" & Trim(txtCardNo.Text) & "'"
            Set Omrc = ExecuteSQL(OnlineSQL, OMsgText)

            StudentSQL = "select * from student_info where cardno='" & Trim(txtCardNo.Text) & "'"
            Set Smrc = ExecuteSQL(StudentSQL, SMsgText)
                Smrc!cash = lblBalance.Caption
                Smrc.Update
                
            MsgBox "下机成功！", 0 + 48, "提示"
            txtCardNo.Text = ""
            txtCardNo.SetFocus
            lblSID.Caption = ""
            lblDept.Caption = ""
            lblType.Caption = ""
            lblName.Caption = ""
            lblSex.Caption = ""
            lblOnTime.Caption = ""
            lblOnDate.Caption = ""
            lblOffTime.Caption = ""
            lblOffDate.Caption = ""
            lblBalance.Caption = ""
            lblCMoney.Caption = ""
            lblCTime.Caption = ""
        End If
    End If
        
End Sub