第一次机房收费系统—上/下机_下户无db9fdf-优快云博客

本文详细介绍了上机和下机流程的实现方法，包括输入卡号验证、余额检查、在线状态更新等步骤，并提供了具体的代码示例。针对下机过程中的计费逻辑进行了深入解析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言：

今天进行上/下机部分的学习，主要是宏观逻辑的把控和对查询语句的使用，首先我画了个逻辑图梳理思路，要做什么？先后顺序？以及用到的知识点！

上机

一、上机—输入卡号

二、判断卡号输入是否规范

1.是否输入？2.是否为数字？(用代码限制输入)

三、判断是否注册

查询Student_info中的studentId

四、判断卡内是否有钱

查询Student_info中的cash（提示后要及时清空Text）

五、上机成功—更新数据表Online_info

查询正在上机的人数

六、上机代码展示：

    '判断卡号是否为空  
    If Trim(txtCardID.Text) = "" Then  
        MsgBox "请输入卡号！", vbOKOnly + vbExclamation, "提示"  
        txtCardID.SetFocus  
        Exit Sub  
    Else  
        If IsNumeric(txtCardID.Text) = False Then  
            MsgBox "卡号必须输入数字！", vbOKOnly + vbExclamation, "提示"  
            txtCardID.Text = ""  
            txtCardID.SetFocus    '，清空输入框，焦点返回到输入框
            Exit Sub  
        End If  
          
        '查询数据库中基本信息表  
        txtSQL = "select * from student_Info where cardno= '" & Trim(txtCardID.Text) & "'"  
        Set mrc = ExecuteSQL(txtSQL, MsgText)  
          
        '判断该卡号是否注册  
        If mrc.BOF And mrc.EOF Then  
            MsgBox "该卡号未注册，请先注册！", vbOKOnly + vbExclamation, "提示"  
            txtCardID.Text = ""  
            txtCardID.SetFocus  
            Exit Sub  
        Else  
              '判断卡号是否已经退卡，退卡后不能上机  
             If Trim(mrc.Fields(10)) = "未激活" Then  
                MsgBox "该卡已经退卡", vbOKCancel + vbInformation, "提示"  
                txtCardID.Text = ""  
                txtCardID.SetFocus  
                Exit Sub  
            Else  
             '查询basicdata_info中的limitcash  
                txtSQL4 = "select * from basicdata_info"  
                Set mrc4 = ExecuteSQL(txtSQL4, MsgText4)  
                      
                If Val(mrc.Fields(7)) < Val(mrc4.Fields(5)) Then  
                    MsgBox "余额不足，请充值后上机！", vbOKOnly + vbExclamation, "提示"  
                    txtCardID.Text = ""  
                    txtCardID.SetFocus  
                    Exit Sub  
  
                Else  
                  
                    '判断卡号是否正在上机  
                    txtSQL2 = "select * from online_info where cardno='" & Trim(txtCardID.Text) & "'"  
                    Set mrc2 = ExecuteSQL(txtSQL2, MsgText2)  
                       
                     '查询student_info中的cash  
                    txtSQL = "select * from student_info where cardno='" & Trim(txtCardID.Text) & "'"  
                    Set mrc = ExecuteSQL(txtSQL, MsgText)  
                      
                    If mrc2.EOF = False Then  
                        MsgBox "该卡正在上机！"  
                        txtSID.Text = mrc2.Fields(2)  
                        txtName.Text = mrc2.Fields(3)  
                        txtSex.Text = mrc2.Fields(5)  
                        txtDepartment = mrc.Fields(4)  
                        txtType.Text = mrc2.Fields(1)  
                        txtUpdate.Text = mrc2.Fields(6)  
                        txtUptime.Text = mrc2.Fields(7)  
                        Exit Sub  
                      
                    Else  
                  
                        '显示该卡号的一些基本信息  
                        txtSID.Text = mrc.Fields(1)  
                        txtName.Text = mrc.Fields(2)  
                        txtSex.Text = mrc.Fields(3)  
                        txtDepartment = mrc.Fields(4)  
                        txtType.Text = mrc.Fields(14)  
                        txtUpdate.Text = Date  
                        txtUptime.Text = Time  
                    End If  
                '将上机前的余额提出来，用于下机时计算余额  
                    txtRemain.Text = mrc.Fields(7)  
                      
                    '将该卡上机的信息填入到online_info表中  
                      
                    txtSQL5 = "select * from online_info"  
                    Set mrc5 = ExecuteSQL(txtSQL5, MsgText5)  
                      
                    mrc5.AddNew  
                    mrc5.Fields(0) = txtCardID.Text  
                    mrc5.Fields(1) = txtType.Text  
                    mrc5.Fields(2) = txtSID.Text  
                    mrc5.Fields(3) = txtName.Text  
                    mrc5.Fields(4) = txtDepartment.Text  
                    mrc5.Fields(5) = txtSex.Text  
                    mrc5.Fields(6) = Date  
                    mrc5.Fields(7) = Time  
                    mrc5.Fields(8) = Trim(Environ("computername"))  
                      
                    mrc5.Update  
                      
                    '查询正在上机的人数  
                    txtSQL6 = "select * from online_info"  
                    Set mrc6 = ExecuteSQL(txtSQL6, MsgText6)  
                      
                    If mrc6.EOF = True Then  
                        lblnumber.Caption = 0  
                    Else  
                        lblnumber.Caption = mrc6.RecordCount  
                    End If  
                  
                End If  
                  
            End If  
        End If  
    End If

下机

（判断步骤同上）

一、获得用户实际在线的时间，获取固定用户、临时用户单位时间的费用分别赋给费用

二、查询基本数据表，获得设定的基本数据，判断实际在线时间是否小于准备时间，若小于则消费金额为0

三、查询基本数据表，获得设定的基本数据，判断实际在线时间是否小于最低消费时间，如小于消费金额为0

四、若实际在线时间大于最低消费时间则按单位时间算，分固定用户和临时用户

五、实际在线时间大于最低消费时间小于单位递增时间，则费用就是用户每小时费用

六、当实际在线时间大于单位时间，就按有几个单位时间算，分固定用户和临时用户；

计算余额：账户余额=原账户余额-消费金额

七、代码展示

'查询基本数据表，获得设定的基本数据
    BasicDataSQL = "select * from BasicData_Info "
    Set mrcBasicData = ExecuteSQL(BasicDataSQL, BMsgtext)
  
'计算消费时间
 
    '实际在线时间
     intLineTime = (Date - DateValue(mrcOnLine!onDate)) * 1440 + (Hour(Time) - Hour(TimeValue(mrcOnLine!OnTime))) * 60 + (Minute(Time) - Minute(TimeValue(mrcOnLine!OnTime)))
     
    '把固定用户，临时用户单位时间的费用分别赋给费用
     fixedunit = Val(mrcBasicData.Fields(0))  '把固定金额的费用赋给固定用户
     temunit = Val(mrcBasicData.Fields(1))    '把临时用户的费用赋给临时用户
     
 '判断时间3种情况
     
     '判断实际上机时间是否小于准备时间，若小于则消费时间为0
     If intLineTime <= Val(Trim(mrcBasicData.Fields(4))) Then
        txtConsumCash.Text = 0

     Else
     
        '判断实际上机时间是否小于最低消费时间，若小于则消费时间为0
        If intLineTime <= Val(Trim(mrcBasicData.Fields(3))) Then
           txtConsumCash.Text = 0
        Else
         
         '实际上机时间大于最低消费时间则按单位时间算，分为固定用户和临时用户
         If intLineTime >= Val(Trim(mrcBasicData!LeastTime)) And intLineTime < Val(Trim(mrcBasicData!unitTime)) And Trim(mrcStudent.Fields(14)) = "固定用户" Then
           txtConsumTime.Text = fixedunit
         Else
           If intLineTime >= Val(Trim(mrcBasicData!LeastTime)) And intLineTime < Val(Trim(mrcBasicData!unitTime)) And Trim(mrcStudent.Fields(14)) = "临时用户" Then
             txtConsumTime.Text = temunit
           Else
            
            '当实际上机时间大于单位时间，就按有几个单位时间算，分为固定用户和临时用户
            If intLineTime >= Val(Trim(mrcBasicData!unitTime)) And Trim(mrcStudent.Fields(14)) = "固定用户" Then
              curConsume = intLineTime / Val(Trim(mrcBasicData!unitTime))
              txtConsumCash.Text = Val(curConsume) * Val(fixedunit)
            Else
              If intLineTime >= Val(Trim(mrcBasicData!unitTime)) And Trim(mrcStudent.Fields(14)) = "临时用户" Then
                 curConsume = intLineTime / Val(Trim(mrcBasicData!unitTime))
                 txtConsumCash.Text = Val(curConsume) * Val(temunit)
                 
              End If
            End If
          End If
        End If
      End If
    End If

 
 
    '计算余额（账户余额=原账户余额-消费余额）
     curBalance = Val(mrcStudent!cash) - Val(txtConsumCash.Text)
     
    '下机信息显示
     txtdowndate.Text = Date
     txtdowntime.Text = Time
     txtStuNo.Text = Trim(mrcOnLine.Fields(2))
     txtName.Text = Trim(mrcOnLine.Fields(3))
     txtDepartment.Text = Trim(mrcOnLine.Fields(4))
     txtStyle.Text = Trim(mrcOnLine.Fields(1))
     txtSex.Text = Trim(mrcOnLine.Fields(5))
     txtOndate.Text = Trim(mrcOnLine.Fields(6))
     txtOnTime.Text = Trim(mrcOnLine.Fields(7))
     txtConsumTime.Text = intLineTime
     txtBalance.Text = Val(curBalance)
     txtConsumCash.Text = Val(curConsume) * Val(temunit)
     '判断余额是否有钱
     
     MsgBox "下机成功,欢迎下次再来！", 0 + 48, "提示"
     
       '定义a为控件变量的集合
                    Dim a As Control
                     '对每个正在执行的控件做循环
                     For Each a In Me.Controls
                      '是否为文本框类型
                      If TypeOf a Is TextBox Then
                         a.Text = ""
                      Else
                      txtSex.Text = ""
                      txtStyle.Text = ""
                      End If
                      
                    Next
     '更新学生信息表的余额
     
     mrcStudent.Fields(7) = Val(curBalance)
     mrcStudent.Fields(11) = "已结账"
     mrcStudent.Update
     mrcStudent.Close
     
     '更新上机记录表line表
     
      LineSQL = "select * from Line_Info"
      Set mrcLine = ExecuteSQL(LineSQL, LMsgtext)

     mrcLine.AddNew

     mrcLine.Fields(1) = Trim(txtCard.Text)
     mrcLine.Fields(2) = Trim(txtStuNo.Text)
     mrcLine.Fields(3) = Trim(txtName.Text)
     mrcLine.Fields(4) = Trim(txtDepartment.Text)
     mrcLine.Fields(5) = Trim(txtSex.Text)
     mrcLine.Fields(6) = Trim(txtOndate.Text)
     mrcLine.Fields(7) = Trim(txtOnTime.Text)
     mrcLine.Fields(8) = Trim(txtdowndate.Text)
     mrcLine.Fields(9) = Trim(txtdowntime.Text)
     mrcLine.Fields(10) = Trim(Val(txtConsumTime.Text))
     mrcLine.Fields(11) = Trim(Val(txtConsumCash.Text))
     mrcLine.Fields(12) = Trim(Val(txtBalance.Text))
     mrcLine.Fields(13) = "正常下机"
     mrcLine.Fields(14) = Trim(VBA.Environ("computername"))
     mrcLine.Update
     mrcLine.Close
     
     '删除在线表中的信息
       OnLineSQL = "select * from OnLine_Info"
      Set mrcOnLine = ExecuteSQL(OnLineSQL, OnMsgtext)
      mrcOnLine.Delete
      mrcOnLine.Update
      

'显示正在上机的人数
    OnLineSQL = "select * from OnLine_Info"
    Set mrcOnLine = ExecuteSQL(OnLineSQL, OnMsgtext)

    If mrcOnLine.EOF = True Then
       txtPeople.Text = 0
    Else
       txtPeople.Text = mrcOnLine.RecordCount
    End If

小结：上机这个点并不难，只是下机尤为复杂，因为要计算时间还要考虑日期，而且困难的是如何将各个表联系起来，在敲之前一定先做一个宏观的把控，先做什么后做什么，这样做起来也会很顺畅！