上下机---机房收费系统

上机：

步骤：

第一步：首先查询学生表（student)看看有没有这个卡号，查询online表看看这个学生有没有在正在上机。

第二步：限定条件，退卡之后不能再上机，余额不足不能上机，正在上机就不能重复上机。

第三步：显示卡号的一些信息，然后将上机信息添加到正在上机表(online)中，然后将这个记录添加到上机记录表(line)中,查询上机人数。

值得注意的一点是得提前将余额更新，用于下机结账。

代码如下：

Private Sub cmdon_Click()    Dim txtSQL As String   '查询student_info,判断卡号是否注册    Dim txtSQL2 As String  '查询online_info,判断卡号是否正在上机    Dim txtSQL3 As String   '查询basicdata_info中的limitcash    Dim txtSQL4 As String   '将该卡上机的信息填入到online_info表中    Dim txtSQL5 As String   '查询正在上机的人数    Dim txtsql6 As String        Dim msgText As String    Dim MsgText2 As String    Dim MsgText3 As String    Dim MsgText4 As String    Dim MsgText5 As String    Dim MsgText1 As String        Dim mrc As ADODB.Recordset    Dim mrc2 As ADODB.Recordset    Dim mrc3 As ADODB.Recordset    Dim mrc4 As ADODB.Recordset    Dim mrc5As ADODB.Recordset    Dim mrcc As ADODB.Recordset    '上机之前应该清空下机内容    txtofftime.Text = ""    txtoffdate.Text = ""    txtconsumeTime.Text = ""    txtConsumeCash.Text = ""    txtCancelCash.Text = ""        '判断卡号是否为空    If Trim(txtcardno.Text) = "" Then        MsgBox "卡号不能为空，请输入卡号！", vbOKOnly + vbExclamation, "提示"                Exit Sub    Else        If IsNumeric(txtcardno.Text) = False Then            MsgBox "卡号必须输入数字！", vbOKOnly + vbExclamation, "提示"           txtcardno.Text = ""            txtcardno.SetFocus            Exit Sub        End If                  '查询数据库中基本信息表        txtSQL = "select * from student_Info where cardno= '" & Trim(txtcardno.Text) & "'"        Set mrc = executesql(txtSQL, msgText)                  '判断该卡号是否注册        If mrc.BOF And mrc.EOF Then            MsgBox "该卡号未注册，请先注册信息！", vbOKOnly + vbExclamation, "提示"            txtcardno.Text = ""            txtcardno.SetFocus            Exit Sub        Else              '判断卡号是否已经退卡，退卡后不能上机             If Trim(mrc.Fields(10)) = "不使用" Then                MsgBox "该卡已经退卡", vbOKCancel + vbInformation, "提示"                txtcardno.Text = ""                txtcardno.SetFocus                Exit Sub            Else             '查询basicdata_info中的limitcash                txtSQL3= "select * from BasicData_info"                Set mrc3 = executesql(txtSQL4, MsgText4)                                      If Val(mrc.Fields(7)) < Val(mrc4.Fields(5)) Then      'val转换为数值型                    MsgBox "余额不足，请充值后上机！", vbOKOnly + vbExclamation, "提示"                    txtcardno.Text = ""                    txtcardno.SetFocus                    Exit Sub                  Else                                      '判断卡号是否正在上机                    txtSQL2 = "select * from Online_Info where cardno='" & Trim(txtcardno.Text) & "'"                    Set mrc2 = executesql(txtSQL2, MsgText2)''                     '查询student_info中的cash'                    txtsql = "select * from student_Info where cardno='" & Trim(txtcardno.Text) & "'"'                    Set mrc = executesql(txtsql, msgtext)                                          If mrc2.EOF = False Then                        MsgBox "该卡正在上机，不能重复上机！"                        txtcardno.Text = mrc2.Fields(0)                        txtstudentName.Text = mrc2.Fields(3)                        txtsex.Text = mrc2.Fields(5)                        txtdepartment = mrc2.Fields(4)                        txttype.Text = mrc.Fields(14)                        txtondate.Text = mrc2.Fields(6)                        txtontime.Text = mrc2.Fields(7)                        Exit Sub                                          Else                                          '显示该卡号的一些基本信息                        txtcardno.Text = mrc.Fields(0)                        txtstudentNo.Text = mrc.Fields(1)                        txtstudentName.Text = mrc.Fields(2)                        txtsex.Text = mrc.Fields(3)                        txtdepartment = mrc.Fields(4)                        txttype.Text = mrc.Fields(14)                        txtondate.Text = Date       '自动获取当前时间和日期                        txtontime.Text = Time                        txtCancelCash.Text = mrc.Fields(7)                    End If                '将上机前的余额提出来，用于下机时计算余额                    txtCancelCash.Text = mrc.Fields(7)                                          '将该卡上机的信息填入到online_info表中                                          txtSQL4 = "select * from OnLine_Info"                    Set mrc4 = executesql(txtSQL5, MsgText5)                                          mrc4.AddNew                    mrc4.Fields(0) = Trim(txtcardno.Text)                    mrc4.Fields(1) = txttype.Text                    mrc4.Fields(2) = txtstudentNo.Text                    mrc4.Fields(3) = txtstudentName.Text                    mrc4.Fields(4) = txtdepartment.Text                    mrc4.Fields(5) = txtsex.Text                    mrc4.Fields(6) = txtondate.Text                    mrc4.Fields(7) = txtontime.Text                    mrc4.Fields(8) = Trim(Environ("computername"))                                          mrc4.Update                                        txtsql6 = "select * from Line_Info"                    Set mrcc = executesql(txtsql7, MsgText1)                    mrcc.AddNew                    mrcc.Fields(1) = Trim(txtcardno.Text)                    mrcc.Fields(2) = txtstudentNo.Text                    mrcc.Fields(3) = txtstudentName.Text                    mrcc.Fields(4) = txtdepartment.Text                    mrcc.Fields(5) = txtsex.Text                    mrcc.Fields(6) = txtondate.Text                    mrcc.Fields(7) = txtontime.Text                    mrcc.Fields(13) = "正常上机"                                                                    mrcc.Update                                        '查询正在上机的人数                    txtSQL5 = "select * from OnLine_Info"                    Set mrc5 = executesql(txtSQL6, MsgText6)                    If mrc5.EOF = True Then      '没有查询到上机人数                        Label19.Caption = 0                    Else                        Label19.Caption = mrc6.RecordCount    '查询到上机人数                    End If                                  End If                              End If        End If    End IfEnd Sub

下机：

步骤：

第一步：查询学生表（student)表判断卡号是否注册，查询online表判断卡号是否正在上机。

第二步：限定条件，退卡的不能进行下机。

第三步：查询基本数据表计算实际在线时间和余额。

第四步：显示下机信息，更新余额，更新上机记录表，然后删除正在上机表中的信息。

其中的难点就是计算余额和实际在线时间了。

代码如下：

Private Sub cmddoff_Click()    Dim txtSQL As String  '查询student_info，判断卡号是否注册,判断卡号是否可以使用    Dim txtSQL1 As String  '查询online_info，判断卡号是否正在上机    Dim txtSQL2 As String  '查询基本数据表，获得设定的基本数据    Dim txtSQL3 As String  '更新上机表line_info的信息               Dim msgText As String    Dim MsgText1 As String    Dim MsgText2 As String    Dim MsgText3 As String              Dim mrc As ADODB.Recordset    Dim mrc1 As ADODB.Recordset    Dim mrc2 As ADODB.Recordset    Dim mrc3 As ADODB.Recordset                  Dim intLineTime As Integer    '用于存储实际在线时间    Dim intconsume As Single    Dim curConsume As Single      '用户存储真正花费钱的时间    Dim curBalance As Single      '用于存储用户的余额    Dim fixedunit As Single       '用于存储固定用户的单位金额    Dim temunit As Single         '用于存储临时用户的单位金额                '判断卡号是否为空    If Trim(txtcardno.Text) = "" Then        MsgBox "请输入卡号！", vbOKOnly + vbExclamation, "提示"        txtcardno.SetFocus        Exit Sub    Else        If IsNumeric(txtcardno.Text) = False Then            MsgBox "卡号必须输入数字！", vbOKOnly + vbExclamation, "提示"            txtcardno.Text = ""            txtcardno.SetFocus            Exit Sub        Else            '查询数据库中学生的基本信息表            txtSQL = "select * from student_Info where cardno= '" & Trim(txtcardno.Text) & "'"            Set mrc = executesql(txtSQL, msgText)                              '判断该卡号是否注册            If mrc.BOF And mrc.EOF Then                MsgBox "该卡号未注册，请先注册信息！", vbOKOnly + vbExclamation, "提示"                txtcardno.Text = ""                txtcardno.SetFocus                Exit Sub            Else            '判断该卡号是否可以使用（是否已被退卡）                If mrc.Fields(10) = "未使用" Then                    MsgBox "该卡已经退卡，不能进行下机处理！", vbOKOnly + vbExclamation, "提示"                    txtcardno.Text = ""                    txtcardno.SetFocus                    Exit Sub                Else                    '判断卡号是否正在上机                    txtSQL1 = "select * from Online_Info where cardno= '" & Trim(txtcardno.Text) & "'"                    Set mrc1 = executesql(txtSQL1, MsgText1)                                          If mrc1.EOF And mrc1.BOF = True Then                        MsgBox "该卡没有上机，不能进行下机处理！", vbOKOnly + vbExclamation, "提示"                        txtcardno.Text = ""                        txtcardno.SetFocus                        Exit Sub                    End If                End If            End If        End If    End If              '查询基本数据表，获得设定的基本数据    txtSQL2 = "select * from BasicData_Info"    Set mrc2 = executesql(txtSQL2, MsgText2)          '计算消费时间    '实际在线时间    intLineTime = (Date - DateValue(mrc1!ondate)) * 1440 + (Hour(Time) - Hour(TimeValue(mrc1!OnTime))) * 60 + (Minute(Time) - Minute(TimeValue(mrc1!OnTime))) '时间单位转换成分钟    '把固定用户、临时用户单位时间的费用分别赋给费用    fixedunit = Val(mrc2.Fields(0))     '把固定用户的金额赋给变量    temunit = Val(mrc2.Fields(1))       '把临时用户的金额赋给变量          '判断实际在线时间是否小于准备时间，若小于则消费为0    If intLineTime <= Val(Trim(mrc2.Fields(4))) Then        txtConsumeCash.Text = 0    Else        '判断实际在线时间是否小于最低消费时间，如小于消费为0        If intLineTime <= Val(Trim(mrc2.Fields(3))) Then            txtConsumeCash.Text = 0        Else            '实际在线时间大于最低消费时间则按单位时间算，分固定用户和临时用户            If intLineTime >= Val(Trim(mrc2!leastTime)) And intLineTime < Val(Trim(mrc2!unitTime)) And Trim(mrc.Fields(14)) = "固定用户" Then                txtConsumeCash.Text = fixedunit            Else                 If intLineTime >= Val(Trim(mrc2!leastTime)) And intLineTime < Val(Trim(mrc2!unitTime)) And Trim(mrc.Fields(14)) = "临时用户" Then                    txtConsumeCash.Text = temunit                 Else                    '当实际在线时间大于单位时间，就按有几个单位时间算，分固定用户和临时用户                    If intLineTime > Val(Trim(mrc2!unitTime)) And Trim(mrc.Fields(14)) = "固定用户" Then                        curConsume = intLineTime / Val(Trim(mrc2!unitTime))                        txtConsumeCash.Text = Val(curConsume) * Val(fixedunit)                    Else                        If intLineTime > Val(Trim(mrc2!unitTime)) And Trim(mrc.Fields(14)) = "临时用户" Then                            curConsume = intLineTime / Val(Trim(mrc2!unitTime))                            txtConsumeCash.Text = Val(curConsume) * Val(temunit)                        End If                    End If                End If            End If        End If    End If          '计算余额（账户余额=原账户余额-消费金额）    curBalance = mrc!cash - Val(txtConsumeCash.Text)    '下机信息显示          txtoffdate.Text = Date    txtofftime.Text = Time    txttype.Text = Trim(mrc1.Fields(1))    txtstudentNo.Text = Trim(mrc1.Fields(2))    txtstudentName.Text = Trim(mrc1.Fields(3))    txtdepartment.Text = Trim(mrc1.Fields(4))    txtsex.Text = Trim(mrc1.Fields(5))    txtondate.Text = Trim(mrc1.Fields(6))    txtontime.Text = Trim(mrc1.Fields(7))    txtconsumeTime.Text = intLineTime  '实际在线时间    txtCancelCash.Text = curBalance    MsgBox "下机成功！欢迎下次再来！", vbOKOnly + vbInformation, "提示"          '更新学生信息表的余额    mrc.Fields(7) = curBalance    mrc.Update    mrc.Close          '更新上机记录表（line_info）    txtSQL3 = "select * from Line_Info"    Set mrc3 = executesql(txtSQL3, MsgText3)    mrc3.AddNew    mrc3.Fields(1) = Trim(txtcardno.Text)    mrc3.Fields(2) = Trim(txtstudentNo.Text)    mrc3.Fields(3) = Trim(txtstudentName.Text)    mrc3.Fields(4) = Trim(txtdepartment.Text)    mrc3.Fields(5) = Trim(txtsex.Text)    mrc3.Fields(6) = Trim(txtondate.Text)    mrc3.Fields(7) = Trim(txtontime.Text)    mrc3.Fields(8) = Trim(txtoffdate.Text)    mrc3.Fields(9) = Trim(txtofftime.Text)    mrc3.Fields(10) = Trim(Val(txtconsumeTime.Text))    mrc3.Fields(11) = Trim(Val(txtConsumeCash.Text))    mrc3.Fields(12) = Trim(Val(txtCancelCash.Text))    mrc3.Fields(13) = "正常下机"    mrc3.Fields(14) = Trim(Environ("computername"))    mrc3.Update          '删除在线表的信息          txtSQL1 = "select * from Online_Info where cardno = '" & Trim(txtcardno.Text) & "'"    Set mrc1 = executesql(txtSQL1, MsgText1)    mrc1.Delete    mrc1.Update   Label19.Caption = mrc1.RecordCount

这样，我们就能上下机了。