本文共 12898 字，大约阅读时间需要 42 分钟。

       说起机房，我个人感觉最具有挑战性的就是上下机，组合查询，以及结账。下面我就来说一说结账。

结账，概括的来说，就让管理员知道每天机房的金钱收支。详细的说是把每个操作员一天收支明细列出来让管理员一目了然。

下面我们先来看看结账的界面，如下图

经过分析，我们可知道，这就是管理员看操作员一天的工作明细，即售卡，充值，退卡的情况。

1、  购卡：就是在student_info表总查询没有结账的那些新注册的学生卡号等信息。

2、  充值：在recharge_info表中查询未结账的充值信息。

3、  退卡：在cancelCard_info中查询未结账的退卡信息。

4、  临时用户：在student_info表中查询未结账的，没有退卡的临时用户。

5、  汇总：将前面所有的信息汇总起来。用到了student_Info 、Recharge_Info 、cancelcard_Info 表。

6、  售卡张数=购卡选项卡的记录总数

7、  退卡张数=退卡选项卡的记录总数

8、  退卡金额=cancelcard_Info表中的金额进行累加

9、  总售卡数=售卡数-退卡数

10、 注册和充值金额=student_Info 表中注册时候的充值金额+Recharge_Info 表中的充值金额（注册时的充值的钱和充值时充进去的钱其实都一样）

11、 临时收费=临时收费选项卡记录中的卡号上机所消费的钱的总和(Line_Info)（只是用来显示）

12、应收金额=注册和充值金额-退卡金额

  但是，这样使用三个表相互查询，略觉复杂。我们可以在recharge表中添个字段，让问题变得简单点。值使用两个表即可将此窗体功能实现。如下图：

只需添加图中的两个字段，就可以在实现本窗体功能时，丢弃student表了。

功能分析如下：

1、  购卡：就是在recharge_info表总查询没有结账的那些新注册的学生卡号等信息。

2、  充值：在recharge_info表中查询未结账的充值信息。

3、  退卡：在cancelCard_info中查询未结账的退卡信息。

4、  临时用户：在recharge_info表中查询未结账的，没有退卡的临时用户。

5、  汇总：因为我们数据表的改动，所以我们只是用到了Recharge_Info 、cancelcard_Info 表。

代码如下：

Dim txtSQL As StringDim msgtext As StringDim checkDaymrc As ADODB.RecordsetDim LineMrc As ADODB.RecordsetDim CancelMrc As ADODB.RecordsetDim REChargeMrc As ADODB.RecordsetDim Yescheckmrc As ADODB.Recordset'Dim cancelCash1 As String '用于记录退卡总金额Dim Allcash1 As String '用户记录注册和充值总金额Private Sub cmdcheckout_Click()    '打开日结账单 的表，查询上次结账时是否有记录，如果没有则    'remainCash清零。否则将上次的记录填上。    '每次都是增加一条记录，将汇总里面的信息填上即可。并且情况，然后对其数据库标记        Dim Remaincash As String '上期余额    Dim RechargeCash As String '上期充值    Dim ConsumCash As String '学生消费    ConsumCash = 0        '判断下拉菜单中是否有东西    If ComLevel.Text = "" Then        MsgBox "先选择员工", vbOKOnly + vbExclamation, "提示"        Exit Sub    End If        txtSQL = "select * from checkday_info"    Set checkDaymrc = ExecuteSQL(txtSQL, msgtext)    Call SQL_Timer    txtSQL = "select * from checkday_info where date='" & Trim(Left(Sqlser_time, 9)) & "'"    Set Yescheckmrc = ExecuteSQL(txtSQL, msgtext)                   '看看checkday数据库是否有记录，有记录的话上次的余额等内容填写上次的，否则为零    If checkDaymrc.EOF = False Then '如果有记录        checkDaymrc.MoveLast '跳到最后一条记录        Remaincash = checkDaymrc.Fields(4)          'remaincash        '计算今日消费        txtSQL = "select * from line_info where ischeck='未结账'and status='正常下机'"        Set LineMrc = ExecuteSQL(txtSQL, msgtext)                If LineMrc.EOF = False Then            Do While LineMrc.EOF = False                ConsumCash = Val(ConsumCash) + Val(LineMrc.Fields(11))                LineMrc.Fields(13) = "已结账"                LineMrc.MoveNext            Loop                End If        If Yescheckmrc.EOF = True Then  '如果今天没有结账            checkDaymrc.AddNew            checkDaymrc.Fields(0) = Val(Remaincash)                 'remaincash            checkDaymrc.Fields(1) = Val(labRechargeCash.Caption)    'rechargecash            checkDaymrc.Fields(2) = Val(ConsumCash)            checkDaymrc.Fields(3) = Val(labCancelCash.Caption)      'cancelcash            checkDaymrc.Fields(4) = Val(labAllCash.Caption)            Call SQL_Timer            checkDaymrc.Fields(5) = Trim(Left(Sqlser_time, 9))      'date            checkDaymrc.Update            checkDaymrc.Close        Else                      Yescheckmrc.Fields(0) = Val(Remaincash)                 'remaincash            Yescheckmrc.Fields(1) = Val(labRechargeCash.Caption) + Val(Yescheckmrc.Fields(1))   'rechargecash            Yescheckmrc.Fields(2) = Val(ConsumCash) + Val(Yescheckmrc.Fields(2))            Yescheckmrc.Fields(3) = Val(labCancelCash.Caption) + Val(Yescheckmrc.Fields(3))     'cancelcash            Yescheckmrc.Fields(4) = Val(labAllCash.Caption) + Val(Yescheckmrc.Fields(4))            Call SQL_Timer            Yescheckmrc.Fields(5) = Trim(Left(Sqlser_time, 9))      'date            Yescheckmrc.Update            Yescheckmrc.Close        End If    Else '如果没有记录        txtSQL = "select * from line_info where ischeck='未结账'and status='正常下机'"        Set LineMrc = ExecuteSQL(txtSQL, msgtext)                If LineMrc.EOF = False Then            Do While LineMrc.EOF = False                ConsumCash = Val(ConsumCash) + Val(LineMrc.Fields(11))                LineMrc.Fields(13) = "已结账"                LineMrc.MoveNext            Loop                End If                checkDaymrc.AddNew        checkDaymrc.Fields(0) = 0                               'remaincash        checkDaymrc.Fields(1) = Val(labRechargeCash.Caption)    'rechargecash        checkDaymrc.Fields(2) = Val(ConsumCash)        checkDaymrc.Fields(3) = Val(labCancelCash.Caption)      'cancelcash        checkDaymrc.Fields(4) = Val(labAllCash.Caption)        Call SQL_Timer        checkDaymrc.Fields(5) = Trim(Left(Sqlser_time, 9))      'date        checkDaymrc.Update        checkDaymrc.Close    End If        '对数据库进行结账标记    '对cancelcard标记    txtSQL = "select * from cancelcard_info where status='未结账'and userid='" & ComLevel.Text & "'"    Set CancelMrc = ExecuteSQL(txtSQL, msgtext)        Do While CancelMrc.EOF = False        CancelMrc.Fields(5) = "结账"        CancelMrc.MoveNext    Loop        '对recharge数据库标记    txtSQL = "select * from recharge_info where status='未结账'and userid='" & ComLevel.Text & "'"    Set REChargeMrc = ExecuteSQL(txtSQL, msgtext)        Do While REChargeMrc.EOF = False        REChargeMrc.Fields(8) = "结账"        REChargeMrc.MoveNext    Loop        labCardNo.Caption = 0    labCancelNo.Caption = 0    labRechargeCash.Caption = 0    lablinCash.Caption = 0    labCancelCash.Caption = 0    laballCardNo.Caption = 0    labAllCash.Caption = 0    End SubPrivate Sub comLevel_Click()     '当在点击下拉菜单中的选项，则显示真是姓名    txtSQL = "select * from user_info where userid='" & ComLevel.Text & "'"    Set mrc = ExecuteSQL(txtSQL, msgtext)        labName.Caption = Trim(mrc.Fields(3))   '显示姓名    lablevel.Caption = Trim(mrc.Fields(2))  '级别    mrc.Close        Call viewdateEnd SubPrivate Sub ComLevel_KeyPress(KeyAscii As Integer)  '下拉菜单不可输入。               KeyAscii = 0End SubPrivate Sub Form_Deactivate()       '失去焦点时，窗体卸载    Unload MeEnd SubPrivate Sub Form_Load()    '窗体初始加载时，在下拉菜单中加载userid（级别）    txtSQL = "select * from user_Info"      '查询    Set mrc = ExecuteSQL(txtSQL, msgtext)        '在下来菜单中显示所有userid    Do While mrc.EOF = False        ComLevel.AddItem mrc.Fields(0)    '操作员用户名        mrc.MoveNext    Loop        mrc.Close        '窗体加载时，加载购卡卡片的字段名    With MfgBugCard        .Rows = 1        .CellAlignment = 4      '居中        .TextMatrix(0, 0) = "学号"  'studentid        .TextMatrix(0, 1) = "卡号"  'cardid        .TextMatrix(0, 2) = "日期"  'date        .TextMatrix(0, 3) = "时间"  'time        .TextMatrix(0, 4) = "金额"  'cash    End With            With MfgRecharge        .Rows = 1        .CellAlignment = 4      '居中        .TextMatrix(0, 0) = "学号"  'studentid        .TextMatrix(0, 1) = "卡号"  'cardid        .TextMatrix(0, 2) = "充值金额"  'cash        .TextMatrix(0, 3) = "日期"  'date        .TextMatrix(0, 4) = "时间"        .TextMatrix(0, 5) = "方式"  'source    End With        With MfgCancelCard        .Rows = 1        .CellAlignment = 4      '居中        .TextMatrix(0, 0) = "学号"  'studentid        .TextMatrix(0, 1) = "卡号"  'cardid        .TextMatrix(0, 2) = "日期"  'cash        .TextMatrix(0, 3) = "时间"  'date        .TextMatrix(0, 4) = "退卡金额"    End With        With MSHFlexGrid4         .Rows = 1        .CellAlignment = 4      '居中        .TextMatrix(0, 0) = "学号"  'studentid        .TextMatrix(0, 1) = "卡号"  'cardid        .TextMatrix(0, 2) = "日期"  'date        .TextMatrix(0, 3) = "时间"  'time    End WithEnd SubPrivate Sub viewdate()      '根据已经选择好人员信息来修改SSTab里面的汇总信息    Dim txtSQL As String    Dim msgtext As String    Dim mrcSD As ADODB.Recordset    Dim mrcRC As ADODB.Recordset    Dim mrcCC As ADODB.Recordset    Dim mrcTmp As ADODB.Recordset    Dim RechargeCash As Variant         '用于存储，充值的 所有金额    Dim cancelCash As Variant           '用于存储，退钱的 所有金额    Dim TmpCash As Variant              '临时用户金额          '把他的所有信息，未结账的显示出来       '购卡    txtSQL = "select * from recharge_Info where status='未结账' and UserID='" & ComLevel.Text & "'and source='注册'"    Set mrcSD = ExecuteSQL(txtSQL, msgtext)          MfgBugCard.Rows = mrcSD.RecordCount + 1          With MfgBugCard                .Rows = 1        .CellAlignment = 4      '居中        .TextMatrix(0, 0) = "学号"  'studentid        .TextMatrix(0, 1) = "卡号"  'cardid        .TextMatrix(0, 2) = "日期"  'date        .TextMatrix(0, 3) = "时间"  'time               While mrcSD.EOF = False            .Rows = .Rows + 1            .CellAlignment = 4            .TextMatrix(.Rows - 1, 0) = Trim(mrcSD.Fields(1)) 'studentno            .TextMatrix(.Rows - 1, 1) = Trim(mrcSD.Fields(0)) 'cardno            .TextMatrix(.Rows - 1, 2) = Trim(mrcSD.Fields(5)) 'date            .TextMatrix(.Rows - 1, 3) = Trim(mrcSD.Fields(6)) 'time            .TextMatrix(.Rows - 1, 4) = Trim(mrcSD.Fields(3)) 'cash            mrcSD.MoveNext        Wend    End With    AutoColWidth Me, MfgBugCard                    '自动调整表格大小          '把该操作员的所有未结账的充值信息汇总到表格，一个注册信息对应一个充值信息    '充值结账    txtSQL = "select * from ReCharge_Info where status='未结账' and UserID='" & ComLevel.Text & "'and source='充值'"    Set mrcRC = ExecuteSQL(txtSQL, msgtext)           RechargeCash = 0          With MfgRecharge         .Rows = 1        .CellAlignment = 4      '居中        .TextMatrix(0, 0) = "学号"  'studentid        .TextMatrix(0, 1) = "卡号"  'cardid        .TextMatrix(0, 2) = "充值金额"  'cash        .TextMatrix(0, 3) = "日期"  'date        .TextMatrix(0, 4) = "时间"        While Not mrcRC.EOF            .Rows = .Rows + 1            .TextMatrix(.Rows - 1, 0) = Trim(mrcRC.Fields(1)) 'studentno            .TextMatrix(.Rows - 1, 1) = Trim(mrcRC.Fields(2)) 'cardno            .TextMatrix(.Rows - 1, 2) = Trim(mrcRC.Fields(3)) 'rechargecash            .TextMatrix(.Rows - 1, 3) = Trim(mrcRC.Fields(4)) 'date            .TextMatrix(.Rows - 1, 4) = Trim(mrcRC.Fields(5)) 'time                        mrcRC.MoveNext        Wend    End With    AutoColWidth Me, MfgRecharge                    '自动调整表格大小    '退卡    '把所有信息汇总到表格    txtSQL = "select * from CancelCard_Info where status='未结账' and UserID='" & ComLevel.Text & "'"    Set mrcCC = ExecuteSQL(txtSQL, msgtext)          cancelCash = 0                With MfgCancelCard        .Rows = 1        .CellAlignment = 4      '居中        .TextMatrix(0, 0) = "学号"  'studentid        .TextMatrix(0, 1) = "卡号"  'cardid        .TextMatrix(0, 2) = "日期"  'cash        .TextMatrix(0, 3) = "时间"  'date        .TextMatrix(0, 4) = "退卡金额"                  While mrcCC.EOF = False            .Rows = .Rows + 1            .CellAlignment = 4            .TextMatrix(.Rows - 1, 0) = Trim(mrcCC.Fields(0))  'studentno            .TextMatrix(.Rows - 1, 1) = Trim(mrcCC.Fields(1))   'cardno            .TextMatrix(.Rows - 1, 2) = Trim(mrcCC.Fields(2))   'date            .TextMatrix(.Rows - 1, 3) = Trim(mrcCC.Fields(3))   'time            .TextMatrix(.Rows - 1, 4) = Trim(mrcCC.Fields(6))   'cancelcash            cancelCash = cancelCash + mrcCC.Fields(6)            mrcCC.MoveNext        Wend    End With    AutoColWidth Me, MfgCancelCard                    '自动调整表格大小        '临时用户    txtSQL = "select * from Recharge_Info where status='未结账' and UserID='" & ComLevel.Text & "'and source='注册'and type='临时用户'"    Set mrcTmp = ExecuteSQL(txtSQL, msgtext)        TmpCash = 0    With MSHFlexGrid4        .Rows = 1        .CellAlignment = 4      '居中        .TextMatrix(0, 0) = "学号"  'studentid        .TextMatrix(0, 1) = "卡号"  'cardid        .TextMatrix(0, 2) = "日期"  'date        .TextMatrix(0, 3) = "时间"  'time                  While mrcTmp.EOF = False            .Rows = .Rows + 1            .CellAlignment = 4            .TextMatrix(.Rows - 1, 0) = Trim(mrcTmp.Fields(1))  'studentno            .TextMatrix(.Rows - 1, 1) = Trim(mrcTmp.Fields(0))   'cardno            .TextMatrix(.Rows - 1, 2) = Trim(mrcTmp.Fields(5))    'date            .TextMatrix(.Rows - 1, 3) = Trim(mrcTmp.Fields(6))   'time                        TmpCash = cancelCash + mrcTmp.Fields(7)            mrcTmp.MoveNext        Wend    End With    AutoColWidth Me, MSHFlexGrid4                    '自动调整表格大小        txtSQL = "select * from Recharge_Info where status='未结账' and UserID='" & ComLevel.Text & "'"    Set mrcTmp = ExecuteSQL(txtSQL, msgtext)    Do While mrcTmp.EOF = False                        RechargeCash = RechargeCash + mrcTmp.Fields(3)            mrcTmp.MoveNext    Loop            '然后，把操作员 的所有统计 信息  汇总 到 汇总列表    labCardNo.Caption = Trim(mrcSD.RecordCount)    labCancelNo.Caption = Trim(mrcCC.RecordCount)    labRechargeCash.Caption = RechargeCash    lablinCash.Caption = TmpCash    labCancelCash.Caption = cancelCash    laballCardNo.Caption = Val(labCardNo.Caption) - Val(labCancelNo.Caption)    labAllCash = Val(labRechargeCash.Caption) - Val(labCancelCash.Caption)          mrcSD.Close         '关闭释放空间    mrcRC.Close    mrcCC.Close    mrcTmp.CloseEnd SubPrivate Sub SSTab1_Click(PreviousTab As Integer)    If SSTab1.Caption = "退出" Then                    Unload Me        Else            Call viewdate            End IfEnd Sub