第一次机房收费系统【总结】——结账

素颜马尾好姑娘i 2024-02-17 17:51 74阅读 0赞

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

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

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

![Center][]

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

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表中添个字段，让问题变得简单点。值使用两个表即可将此窗体功能实现。如下图：

![Center 1][]

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

功能分析如下：

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

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

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

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

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

代码如下：

Dim txtSQL As String
    Dim msgtext As String
    Dim checkDaymrc As ADODB.Recordset
    Dim LineMrc As ADODB.Recordset
    Dim CancelMrc As ADODB.Recordset
    Dim REChargeMrc As ADODB.Recordset
    Dim 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 Sub
    
    Private 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 viewdate
    End Sub
    
    Private Sub ComLevel_KeyPress(KeyAscii As Integer)  '下拉菜单不可输入。
       
                KeyAscii = 0
    End Sub
    
    Private Sub Form_Deactivate()       '失去焦点时，窗体卸载
        Unload Me
    End Sub
    
    Private 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 With
    End Sub
    
    Private 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.Close
    End Sub
    
    
    
    
    Private Sub SSTab1_Click(PreviousTab As Integer)
        If SSTab1.Caption = "退出" Then
            
                Unload Me
            Else
                Call viewdate
            
        End If
    End Sub

[Center]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d25e7058237b4cb8b1059d82833517b6.png
[Center 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/4bcbc85a4bb14c6aac7f55637fc73296.png