机房收费系统之结账

        

        机房收费系统中的结账，首先要理清思路，谁给谁结账，如何结账。理解之后就容易多了。我将机房看作是一个网吧。里边的人员都是有着不同权限，管理员，相当于网吧里负责人或者老板，这间网吧是属于他的，他不需要干活，只要查和管理就好。而操作员是给管理员打工的人，是管理人们上机下机的人，相当于网吧里的网管，一般用户就是比操作员权限更低的人，只能做一些“查”的工作。在网吧中，操作员坐在柜台的地方管理着上网的人上机、下机、充值、退卡一系列的工作。而管理员则需要查明某个操作员在工作时收了多少钱退了多少钱，之后手里还有多少钱。这样我们就知道了，结账就应该是管理员结账，结的是操作员工作时的账。

       再来区分一下临时用户和固定用户。所谓的固定用户就是，比如说，一个人经常来上网，所以注册的时候注册为固定用户，他就相当于会员，他向卡中充值了200，以后他来上网，就不用带钱，直接用卡就好。而临时用户则是平常不会常来网吧，来一次就会注册一个卡号，每次都会充值，充值的钱不多，可能一次性就用完了，如果没有用完将卡退回到柜台前，操作员会退给剩余的钱。如果他下次还来，那一定还要再次注册一个卡号。同时固定用户和临时用户的基本费用不能，会员和非会员的区别。

一、结账的窗体：

二、各个选项卡：

                          1.购卡。显示某操作员名下Student_Info表里的未结账的记录。

                          2.充值。显示某操作员名下Recharge_Info表里未结账的记录。

                          3.退卡。显示某操作员名下CancelCard_Info表里未结账的记录。

                          4.临时用户。显示的是Student_Info表里未结账，并且type为临时用户的记录。

                          5.汇总。显示各个卡数和各种金额的信息

 

                           充值金额：包括注册时的充值金额和向卡中充值的金额。

                          退卡金额：退卡时卡里的余额。

                          应收金额=充值金额-退卡金额。(固定用户)

                          应收金额=充值金额+临时金额-退卡金额(临时用户)

                         临时金额：是指收临时用户的费用。

                        售卡张数:一共注册了多少张卡（包括已经退卡的）

                        退卡张数:退了多少张卡。

                        总售卡数=售卡张数-退卡张数。

                      三：代码展示

                               

Dim txtSQL As String '定义字符串变量，表示查询语句        Dim mrc As ADODB.Recordset '定义数据集对象        Dim mrc1 As ADODB.Recordset '定义数据集对象        Dim mrc2 As ADODB.Recordset '定义数据集对象        Dim mrc3 As ADODB.Recordset '定义数据集对象        Dim msgtext As String '定义字符串变量，返回查询语句        Dim Sellcard As String '售卡张数        Dim Backcard As String '退卡张数        Dim Charge As String '充值金额        Dim Back As String '退卡金额        Dim Temporary As String '临时金额'购卡按钮，显示该操作员的购卡情况，student_Info表中查询信息，其中的购卡包含了已经退了的卡。            txtSQL = "select * from student_Info where userID = '" & Operator.Text & "'" & " " & "and Level = '" & "操作员" & "'"            Set mrc = ExecuteSQL(txtSQL, msgtext)            If mrc.BOF And mrc.EOF Then                MsgBox "没有购卡记录，请重新选择！", vbOKOnly + vbExclamation, "友好提示"                Exit Sub            End If            With MSHFlexGrid1                .Rows = 1                .ColAlignment = 4                .CellAlignment = 4                .TextMatrix(0, 0) = "学号"                .TextMatrix(0, 1) = "卡号"                .TextMatrix(0, 2) = "日期"                .TextMatrix(0, 3) = "时间"                Do While Not mrc.EOF                .Rows = .Rows + 1                .ColAlignment = 4                .CellAlignment = 4                .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1))                .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(0))                .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(12))                .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(13))                mrc.MoveNext                Loop            End With            mrc.Close'充值从ReCharge_Info中查询信息，包含了某个卡号的初始充值记录和每一次的充值记录                    txtSQL = "select * from ReCharge_Info where UserID='" & Operator.Text & "'" & " " & "and Level = '" & "操作员" & "'"                    Set mrc1 = ExecuteSQL(txtSQL, msgtext)                    If mrc1.BOF And mrc1.EOF Then                         MsgBox "没有充值记录，请重新选择！", vbOKOnly + vbExclamation, "友好提示"                         Exit Sub                    End If                    With MSHFlexGrid2                    .Rows = 1                                        .TextMatrix(0, 0) = "卡号"                    .TextMatrix(0, 1) = "学号"                    .TextMatrix(0, 2) = "充值金额"                    .TextMatrix(0, 3) = "日期"                    .TextMatrix(0, 4) = "时间"                    Do While mrc1.EOF = False                    .Rows = .Rows + 1                   .TextMatrix(.Rows - 1, 0) = mrc1.Fields(2)                   .TextMatrix(.Rows - 1, 1) = mrc1.Fields(1)                   .TextMatrix(.Rows - 1, 2) = mrc1.Fields(3)                    .TextMatrix(.Rows - 1, 3) = mrc1.Fields(4)                  .TextMatrix(.Rows - 1, 4) = mrc1.Fields(5)                  mrc1.MoveNext                  Loop                 End With                 mrc1.Close'退卡信息，从CancelCard_Info表中查询信息                           txtSQL = "select * from CancelCard_Info where UserID='" & Operator.Text & "'" & " " & "and Level = '" & "操作员" & "'"                           Set mrc2 = ExecuteSQL(txtSQL, msgtext)                If mrc2.BOF And mrc2.EOF Then                         MsgBox "没有退卡记录，请重新选择！", vbOKOnly + vbExclamation, "友好提示"                         Exit Sub                End If                        With MSHFlexGrid3                        .Rows = 1                       .TextMatrix(0, 0) = "卡号"                       .TextMatrix(0, 1) = "学号"                       .TextMatrix(0, 2) = "日期"                       .TextMatrix(0, 3) = "时间"                       .TextMatrix(0, 4) = "退卡金额"                      Do While mrc2.EOF = False                     .Rows = .Rows + 1                    .TextMatrix(.Rows - 1, 0) = mrc2.Fields(1)                    .TextMatrix(.Rows - 1, 1) = mrc2.Fields(0)                   .TextMatrix(.Rows - 1, 2) = mrc2.Fields(3)                   .TextMatrix(.Rows - 1, 3) = mrc2.Fields(4)                   .TextMatrix(.Rows - 1, 4) = mrc2.Fields(2)                   mrc2.MoveNext                   Loop                  End With                  mrc2.Close                  '临时用户信息，从student_Info中查询信息            txtSQL = "select * from student_Info where type='临时用户' and UserID = '" & Operator.Text & "'" & " " & "and Level = '" & "操作员" & "'"            Set mrc3 = ExecuteSQL(txtSQL, msgtext)            If mrc3.BOF And mrc3.EOF Then                MsgBox "没有临时用户记录，请重新选择！", vbOKOnly + vbExclamation, "友好提示"                Exit Sub            End If            With MSHFlexGrid4                .Rows = 1                .ColAlignment = 4                .CellAlignment = 4                .TextMatrix(0, 0) = "学号"                .TextMatrix(0, 1) = "卡号"                .TextMatrix(0, 2) = "日期"                .TextMatrix(0, 3) = "时间"                Do While Not mrc3.EOF                .Rows = .Rows + 1                .ColAlignment = 4                .CellAlignment = 4                .TextMatrix(.Rows - 1, 0) = Trim(mrc3.Fields(1))                .TextMatrix(.Rows - 1, 1) = Trim(mrc3.Fields(0))                .TextMatrix(.Rows - 1, 2) = Trim(mrc3.Fields(12))                .TextMatrix(.Rows - 1, 3) = Trim(mrc3.Fields(13))                mrc3.MoveNext                Loop            End With            mrc3.Close            '售卡张数            txtSQL = "select count(cardno) from student_Info where userID = '" & Operator.Text & "'" & " " & "and Ischeck = '" & "未结账" & "'"            Set mrc = ExecuteSQL(txtSQL, msgtext)            If mrc.BOF And mrc.EOF Then               Sellcard = 0            Else                If IsNull(Trim(mrc.Fields(0))) Then                    Sellcard = 0                Else                    Sellcard = mrc.Fields(0)                End If            End If            txtshouka.Text = Sellcard                        '退卡张数             txtSQL = "select count(cardNo) from CancelCard_Info where userID = '" & Operator.Text & "'" & " " & "and status = '" & "未结账" & "'"            Set mrc = ExecuteSQL(txtSQL, msgtext)            If mrc.BOF And mrc.EOF Then                Backcard = 0            Else                If IsNull(Trim(mrc.Fields(0))) Then                    Backcard = 0                Else                    Backcard = mrc.Fields(0)                End If                            End If            txttuika.Text = Backcard           '充值金额            txtSQL = "select sum(addmoney) from ReCharge_Info where userID = '" & Operator.Text & "'" & " " & "and status = '" & "未结账" & "'"            Set mrc = ExecuteSQL(txtSQL, msgtext)            If mrc.BOF And mrc.EOF Then                Charge = 0            Else                If IsNull(Trim(mrc.Fields(0))) Then                    Charge = 0                Else                    Charge = mrc.Fields(0)                End If                            End If            txtchongka.Text = Charge                        '退卡金额            txtSQL = "select sum(CancelCash)from CancelCard_Info where userID = '" & Operator.Text & "'" & " " & "and status = '" & "未结账" & "'"            Set mrc = ExecuteSQL(txtSQL, msgtext)            If mrc.BOF And mrc.EOF Then                Back = 0            Else                If IsNull(Trim(mrc.Fields(0))) Then                    Back = 0                Else                    Back = mrc.Fields(0)                End If            End If            txttuiyue.Text = Back        '临时金额           txtSQL = "select sum(cash) from student_Info where userID = '" & Operator.Text & "'" & " " & "and Ischeck = '" & "未结账" & "'" & " " & "and type = '" & "临时用户" & "'"            Set mrc = ExecuteSQL(txtSQL, msgtext)            If mrc.BOF And mrc.EOF Then                Temporary = 0            Else                If IsNull(Trim(mrc.Fields(0))) Then                    Temporary = 0                Else                    Temporary = mrc.Fields(0)                End If            End If            txtlinshi.Text = Temporary