机房收费系统之上下机

前言

 上下机部分是机房收费系统的一个重要部分，需要捋清思路，逻辑要清楚。

---

上下机逻辑

先来捋一下上下机的思路。

上机：
(其中上机不用更新Line表，只下机更新Line表)

下机

---

问题及重点代码：
上机部分：

1.文本框如何锁定：locked 属性决定控件是否可以编辑；BackStyle属性指出label或shape背景样式是否透明。
2.label如何显示当前时间：通过如下代码实现
Private Sub Timer1_Timer()
Label20.Caption = Hour(Time) & “:” & Minute(Time) & “:” & Second(Time)
End Sub
3.上机需要注意的问题：上机之后把line表中的status栏改为正在上机

下机部分：

1.如何计算时间：（用datediff函数计算）
Time1 = DateDiff(“n”, txtOnDate, txtOffDate)
Time2 = DateDiff(“n”, txtOnTime, txtOffTime)
Time3 = Val(Time1 + Time2)
txtCostTime = Time3

datediff函数用法可参考下面这个链接：
https://zhidao.baidu.com/question/2053624245418181347.html

2.计算收费：（收费这块是个麻烦的地方）
分为两块：上机时间小于准备时间（准备时间是2分钟）；
上机时间大于准备时间；
其中有个Mod，是用来求余数的，特别适合用来计算时间。

'计算收费
    '上机时间小于准备时间
    If Time3 <= prepareTime Then
        txtCostTime = 0
        txtCostCash = 0
    Else
        useTime = txtCostTime - prepareTime
        a = useTime Mod Val(unitTime)   'Mod是用来求余数的，很适合用来计算收费
            If a = 0 Then
                a = Int(useTime / unitTime)
            Else
                a = Int(useTime / unitTime) + 1
            End If

            '判断用户类型
            If txtUserType = "固定用户" Then
                b = Rate
            Else
                b = tmpRate
            End If
            costMoney = Format(a * b, "0.0")
            txtCostCash.Text = costMoney
            '计算余额,更新余额
            txtRecharge.Text = txtRecharge.Text - costMoney
    End If

3.下机注意事项：
删除online表中信息，将status列的正在上机更新为正常下机；

---

上下机的代码：

上机：

    '判断卡号是否为空
    If Not Testtxt(txtCard.Text) Then
        MsgBox "请输入卡号后进行上机操作!", vbOKOnly, "提示"
        Exit Sub
    End If

    If Not IsNumeric(Trim(txtCard.Text)) Then '判断是否为数字
        MsgBox "请输入数字!", vbOKOnly, "提示"
        Exit Sub
    End If


    '判断卡号是否注册
    txtSQL2 = "select * from student_Info where student_Card = '" & Trim(txtCard.Text) & "'"
    Set mrc2 = ExecuteSQL(txtSQL2, MsgText)
    If mrc2.EOF = True Then
        MsgBox "该卡号未注册,请先注册信息!", vbOKOnly, "提示"
        Exit Sub
    End If

    studentNo.Text = mrc2.Fields(1)
    department.Text = mrc2.Fields(4)
    txtUserType.Text = mrc2.Fields(13)
    txtName.Text = mrc2.Fields(2)
    txtSex.Text = mrc2.Fields(3)
    txtOnDate.Text = Date
    txtOnTime.Text = Time
    txtRecharge.Text = mrc2.Fields(15)
    '执行查询操作
    txtSQL3 = "select * from online_Info where online_Card = '" & Trim(txtCard.Text) & "'"
    Set mrc3 = ExecuteSQL(txtSQL3, MsgText)

    If mrc3.EOF = False Then
        MsgBox "此卡号正在上机!", vbOKOnly, "提示"

        txtCard = ""
        studentNo = ""
        department = ""
        comboGender = ""
        comboUserType = ""
        txtName = ""
        txtOnDate = ""
        txtOnTime = ""
        txtOffDate = ""
        txtOffTime = ""
        txtRecharge = ""
        txtCostTime = ""
        txtCostCash = ""
        Exit Sub
    Else

    If Val(mrc2.Fields(7)) < 1 Then
         MsgBox "余额不足,请充值!", vbOKOnly, "提示"
         Exit Sub
    Else
    txtSQL1 = "select * from money_Info "
    Set mrc1 = ExecuteSQL(txtSQL1, MsgText)

    txtSQL = "select * from line_Info "
    Set mrc = ExecuteSQL(txtSQL, MsgText)


    txtSQL3 = "select * from online_Info where online_Card = '" & Trim(txtCard.Text) & "'"
    Set mrc3 = ExecuteSQL(txtSQL3, MsgText)

    mrc3.AddNew
    mrc3.Fields(0) = Trim(txtCard.Text)
    mrc3.Fields(1) = Trim(txtSex.Text)
    mrc3.Fields(2) = mrc3.RecordCount
    mrc3.Fields(4) = Date
    mrc3.Fields(5) = Time
    mrc3.Fields(6) = Trim(txtUserType.Text)
    mrc3.Fields(7) = "4JRTBT0"
    mrc3.Fields(8) = Trim(txtCard.Text)
    mrc3.Fields(10) = Trim(studentNo.Text)
    mrc3.Fields(11) = Trim(department.Text)
    mrc3.Fields(12) = "正常下机"
    mrc3.Update

    Label21.Caption = mrc3.RecordCount

    MsgBox "开始上机!", vbOKOnly, "提示"


End If
End If

下机：

    Dim Time1, Time2, Time3 As String
    Dim Rate, tmpRate, unitTime, leastTime, preTime, useTime, b As String
    Dim a As Integer
    Dim costMoney As Currency'（Currency货币）


    '判断是否输入卡号
    If Not Testtxt(txtCard.Text) Then
         MsgBox "请输入卡号!", vbOKOnly, "提示"
         Exit Sub
    End If
    If Not IsNumeric(Trim(txtCard.Text)) Then
         MsgBox "卡号必须输入数字！", vbOKOnly, "提示"
         Exit Sub
    End If

         txtSQL2 = "select * from student_Info where student_Card = '" & Trim(txtCard.Text) & " '"
         Set mrc2 = ExecuteSQL(txtSQL2, MsgText)

    If mrc2.EOF Then
             MsgBox "该卡号未注册,请先注册信息!", vbOKOnly, "提示"
             Exit Sub
        Else
        If Trim(mrc2.Fields(10)) = "未使用" Then
             MsgBox "该卡已经退卡", vbOKOnly, "提示"
             Exit Sub
        End If
    End If

        txtSQL3 = "select * from online_Info where online_Card = '" & Trim(txtCard.Text) & "'"
        Set mrc3 = ExecuteSQL(txtSQL3, MsgText)

    If mrc3.EOF Then
        MsgBox "该卡没有上机,不能下机 ", vbOKOnly, "提示"
        Exit Sub
    End If


    studentNo.Text = mrc2.Fields(1)
    department.Text = mrc2.Fields(4)
    txtUserType.Text = mrc2.Fields(13)
    txtName.Text = mrc2.Fields(2)
    txtSex.Text = mrc2.Fields(3)
    txtOnDate.Text = mrc3.Fields(4)
    txtOnTime.Text = mrc3.Fields(5)
    txtOffDate.Text = Date
    txtOffTime.Text = Time

    '执行查询操作
        txtSQL = "select * from line_Info"
        Set mrc = ExecuteSQL(txtSQL, MsgText)
    '计算时间

        Time1 = DateDiff("n", txtOnDate, txtOffDate)
        Time2 = DateDiff("n", txtOnTime, txtOffTime)
        Time3 = Val(Time1 + Time2)
        txtCostTime = Time3

    '计算上机金额
        txtSQL4 = "select * from basic_Info "
        Set mrc4 = ExecuteSQL(txtSQL4, MsgText)

        Rate = mrc4.Fields(0)
        tmpRate = mrc4.Fields(1)
        unitTime = mrc4.Fields(2)
        leastTime = mrc4.Fields(3)
        prepareTime = mrc4.Fields(7)

    '计算收费
    '上机时间小于准备时间
    If Time3 <= prepareTime Then
        txtCostTime = 0
        txtCostCash = 0
    Else
        useTime = txtCostTime - prepareTime
        a = useTime Mod Val(unitTime)   'Mod是用来求余数的，很适合用来计算收费
            If a = 0 Then
                a = Int(useTime / unitTime)
            Else
                a = Int(useTime / unitTime) + 1
            End If

            '判断用户类型
            If txtUserType = "固定用户" Then
                b = Rate
            Else
                b = tmpRate
            End If
            costMoney = Format(a * b, "0.0")
            txtCostCash.Text = costMoney
            '计算余额,更新余额
            txtRecharge.Text = txtRecharge.Text - costMoney
    End If

    mrc.AddNew
    mrc.Fields(0) = Trim(txtCard.Text)
    mrc.Fields(1) = Trim(studentNo.Text)
    mrc.Fields(2) = Trim(txtName.Text)
    mrc.Fields(3) = Trim(department.Text)
    mrc.Fields(4) = Trim(txtSex.Text)
    mrc.Fields(5) = Trim(txtOnDate.Text)
    mrc.Fields(6) = Trim(txtOnTime.Text)
    mrc.Fields(7) = Date
    mrc.Fields(8) = Time
    mrc.Fields(9) = Trim(txtCostTime.Text)
    mrc.Fields(10) = Trim(txtCostCash.Text)
    mrc.Fields(11) = Trim(txtRecharge.Text)
    mrc.Fields(12) = "4JRTBT0"
    mrc.Fields(13) = "正常下机"
    mrc.Update

    mrc2.Fields(15) = Trim(txtRecharge.Text)
    mrc2.Update
    txtSQL3 = "delete from online_Info where online_Card = '" & Trim(txtCard.Text) & "'"
    Set mrc3 = ExecuteSQL(txtSQL3, MsgText)

    MsgBox "用户下机成功!", vbOKOnly, "提示"
    mrc.Close