机房-结账

￡神魔★判官ぃ 2022-06-03 05:25 245阅读 0赞

# 开篇： #

结账这块感觉也是一个难点，但其实正是这种不好弄的窗体我们才能学到很多，把这块弄完回头看的时候，发现还不是太难，毕竟人总是向前发展的哈！

# 逻辑过程： #

##     一、整体要求： ##

选择操作员用户名之后，操作员姓名以及下面的SSTab控件里的所有内容应该都显示出来。

###       1.窗体加载时 ###

把所有的操作员显示出来，每个MSHFlexGrid上加载相应的内容（举一例）

Dim mrc As ADODB.Recordset
        Dim txtSQL As String
        Dim Msgtext As String
        Dim N As Integer
        Dim mrc As ADODB.Recordset
        Dim txtSQL As String
        Dim Msgtext As String
        Dim N As Integer
    
        txtSQL = "select * from user_Info where Level = '操作员'"
        Set mrc = ExecuteSQL(txtSQL, Msgtext)
        
        Do While Not mrc.EOF
            cboUserID.AddItem Trim(mrc.Fields(0))
            mrc.MoveNext
            N = N + 1
        Loop
        mrc.Close

SSTab1.Tab = 0       '设置刚刚载入窗体的时候的选项卡位置
        
        '初始化表格的标题
        With MFGbuy
            .TextMatrix(0, 0) = "学号"
            .TextMatrix(0, 1) = "卡号"
            .TextMatrix(0, 2) = "日期"
            .TextMatrix(0, 3) = "时间"
            .TextMatrix(0, 4) = "金额"
        End With

### `  2.单击操作员用户名` ###

Dim mrc As ADODB.Recordset
        Dim txtSQL As String
        Dim Msgtext As String
        
        If cboUserID = "" Then
            labUserName = ""
        Else
            txtSQL = "select * from user_info where userID = '" & cboUserID.Text & "'"
            Set mrc = ExecuteSQL(txtSQL, Msgtext)
            labUserName.Caption = mrc.Fields(3)
            mrc.Close
        End If
        
        Call viewdate

###       3.call viewdate，调用一个过程 ###

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 RegisterCash As Variant         '用于存储，注册的所有金额
        Dim RechargeCash As Variant         '用于存储，充值的所有金额
        Dim cancelCash As Variant           '用于存储，退钱的所有金额
          
        '把他的所有信息，未结账的显示出来
      
        txtSQL = "select studentNo,cardno,date,time,cash from student_Info where Ischeck= '未结账' and UserID= '" & cboUserID.Text & "'"
        Set mrcSD = ExecuteSQL(txtSQL, Msgtext)
          
        MFGbuy.Rows = mrcSD.RecordCount + 1
          
        With MFGbuy
            .Row = 0
            While mrcSD.EOF = False
                .Row = .Row + 1
                .TextMatrix(.Row, 0) = mrcSD.Fields(0)
                .TextMatrix(.Row, 1) = mrcSD.Fields(1)
                .TextMatrix(.Row, 2) = mrcSD.Fields(2)
                .TextMatrix(.Row, 3) = mrcSD.Fields(3)
                .TextMatrix(.Row, 4) = mrcSD.Fields(4)
                RegisterCash = Val(RegisterCash) + mrcSD.Fields(4)
                mrcSD.MoveNext
            Wend
        End With
          
        '把该操作员的所有未结账的充值信息汇总到表格，一个注册信息对应一个充值信息
        txtSQL = "select studentNo,cardno,addmoney,date,time from ReCharge_Info where status= '未结账' and UserID= '" & cboUserID.Text & "'"
        Set mrcRC = ExecuteSQL(txtSQL, Msgtext)
          
        MFGcharge.Rows = mrcRC.RecordCount + 1
          
        With MFGcharge
            .Row = 0
            While Not mrcRC.EOF
                .Row = .Row + 1
                .TextMatrix(.Row, 0) = mrcRC.Fields(0)
                .TextMatrix(.Row, 1) = mrcRC.Fields(1)
                .TextMatrix(.Row, 2) = mrcRC.Fields(2)
                .TextMatrix(.Row, 3) = mrcRC.Fields(3)
                .TextMatrix(.Row, 4) = mrcRC.Fields(4)
                RechargeCash = Val(RechargeCash) + mrcRC.Fields(2)
                mrcRC.MoveNext
            Wend
        End With
          
        '把所有退卡信息汇总到表格
        txtSQL = "select studentNo,cardNo,date,time,cancelcash from CancelCard_Info where status= '未结账' and UserID= '" & cboUserID.Text & "'"
        Set mrcCC = ExecuteSQL(txtSQL, Msgtext)
          
        MFGbackcard.Rows = mrcCC.RecordCount + 1
          
          
        With MFGbackcard
            .Row = 0
              
            While mrcCC.EOF = False
                .Row = .Row + 1
                .TextMatrix(.Row, 0) = mrcCC.Fields(0)
                .TextMatrix(.Row, 1) = mrcCC.Fields(1)
                .TextMatrix(.Row, 2) = mrcCC.Fields(2)
                .TextMatrix(.Row, 3) = mrcCC.Fields(3)
                .TextMatrix(.Row, 4) = mrcCC.Fields(4)
                cancelCash = Val(cancelCash) + mrcCC.Fields(4)
                mrcCC.MoveNext
            Wend
        End With
              
        '然后把操作员的所有统计信息汇总到汇总列表
        txtbuy = mrcSD.RecordCount
        txtbackcard = mrcCC.RecordCount
        txtchargemoney = Val(RegisterCash)
        txtbackmoney = Val(cancelCash)
        txtallbuy = Val(txtbuy.Text) - Val(txtbackcard.Text)
        txtrealmoney = Val(txtchargemoney.Text) - Val(txtbackmoney.Text)
          
        mrcSD.Close         '关闭释放空间
        mrcRC.Close
        mrcCC.Close

这样整体要求就完成了，如果可以正常显示的话。

##     二、单击结账时 ##

此时要求把与操作员有关的未结账的信息改为结账，并写报表。

###       1.把与操作员有关的未结账的信息改为结账 ###

Dim txtSQL As String
        Dim Msgtext As String
        Dim mrcSD As ADODB.Recordset
        Dim mrcRC As ADODB.Recordset
        Dim mrcCC As ADODB.Recordset
        Dim mrcCO As ADODB.Recordset
          
        txtSQL = "select Ischeck from student_Info where Ischeck= '未结账' and UserID= '" & cboUserID.Text & "'"
        Set mrcSD = ExecuteSQL(txtSQL, Msgtext)
          
        txtSQL = "select status from Recharge_Info where status= '未结账' and UserID= '" & cboUserID.Text & "'"
        Set mrcRC = ExecuteSQL(txtSQL, Msgtext)
          
        txtSQL = "select status from CancelCard_Info where status= '未结账' and UserID= '" & cboUserID.Text & "'"
        Set mrcCC = ExecuteSQL(txtSQL, Msgtext)
          
        While mrcSD.EOF = False            '修改数据库   并且 更新
            mrcSD.Fields(0) = "已结账"
            mrcSD.Update        '更新数据库
            mrcSD.MoveNext
        Wend
          
        While mrcRC.EOF = False
            mrcRC.Fields(0) = "已结帐"
            mrcRC.Update
            mrcRC.MoveNext
              
        Wend
          
        While mrcCC.EOF = False
            mrcCC.Fields(0) = "已结帐"
              
            mrcCC.Update
            mrcCC.MoveNext
        Wend
          
        mrcSD.Close         '关闭释放空间
        mrcRC.Close
        mrcCC.Close
          
        '记录修改成功   将刚才的操作写入 日报表
        Call ReportDay
        Call ReportSum
          
        MsgBox "结账成功", vbOKOnly + vbInformation, "提示"

###       2.更新日结账单 ###

'写报表信息
    Private Sub ReportDay()
        Dim txtSQL As String
        Dim Msgtext As String
        Dim mrcCD As ADODB.Recordset    '用于写  今日的 账单
          
        Dim mrcSD As ADODB.Recordset    '用于找出  上期 和 本期的充值卡余额
        Dim mrcRC As ADODB.Recordset    '用于找出 当日的 充值金额
        Dim mrcL As ADODB.Recordset     '用于找出 当日的 消费金额
        Dim mrcCC As ADODB.Recordset    '用于找出 当日的 退卡金额
          
        Dim CardCash As Double      '对应 本期消费金额
        Dim RechargeCash As Double  '对应 本日充值余额
        Dim lineCash As Double      '对应 本日消费金额
        Dim cancelCash As Double    '对应 本日退卡金额
          
        txtSQL = "select * from CheckDay_Info"      '等待写入
        Set mrcCD = ExecuteSQL(txtSQL, Msgtext)
          
        txtSQL = "select sum(cash) from student_Info where status= '使用'"
        Set mrcSD = ExecuteSQL(txtSQL, Msgtext)     '存放 本期充值卡余额
          
        txtSQL = "select sum(addmoney)as a1 from ReCharge_Info where date= '" & Date & "'"
        Set mrcRC = ExecuteSQL(txtSQL, Msgtext)     '存放 本日充值金额
          
        txtSQL = "select sum(consume) from Line_Info where offdate= '" & Date & "'"
        Set mrcL = ExecuteSQL(txtSQL, Msgtext)      '存放 本日消费金额
          
        txtSQL = "select sum(CancelCash) from CancelCard_Info where date= '" & Date & "'"
        Set mrcCC = ExecuteSQL(txtSQL, Msgtext)     '存放 本日退卡金额
          
        '逐一判断记录集合为空的情况。如果查询结果为空则赋值为0
        If IsNull(mrcSD.Fields(0).Value) Then
            CardCash = 0
        Else
            CardCash = mrcSD.Fields(0)
        End If
          
        If IsNull(mrcRC!a1) Then
            RechargeCash = 0
        Else
            RechargeCash = mrcRC.Fields(0)
        End If
          
        If IsNull(mrcL.Fields(0).Value) Then
            lineCash = 0
        Else
            lineCash = mrcL.Fields(0)
        End If
          
        If IsNull(mrcCC.Fields(0).Value) Then
            cancelCash = 0
        Else
            cancelCash = mrcCC.Fields(0)
        End If
          
        mrcCD.AddNew
        mrcCD.Fields(4) = Val(CardCash) + Val(lineCash) + Val(cancelCash) - Val(RechargeCash)   '该过程需要认真思考一下
        mrcCD.Fields(1) = Val(RechargeCash)
        mrcCD.Fields(2) = Val(lineCash)
        mrcCD.Fields(3) = Val(cancelCash)
        mrcCD.Fields(0) = Val(CardCash)
        mrcCD.Fields(5) = Date
          
          
        c0 = mrcCD.Fields(0)
        c1 = mrcCD.Fields(1)
        c2 = mrcCD.Fields(2)
        c3 = mrcCD.Fields(3)
        c4 = mrcCD.Fields(4)
        c5 = mrcCD.Fields(5)
          
          
        mrcCD.Update
        mrcCD.Close
          
    End Sub

###         3.更新周结账单 ###

Private Sub ReportSum()
        Dim txtSQL As String
        Dim Msgtext As String
        Dim mrcCD As ADODB.Recordset
        Dim mrcCS As ADODB.Recordset
          
        txtSQL = "select * from CheckDay_Info where date= '" & Date & "'"
        Set mrcCD = ExecuteSQL(txtSQL, Msgtext)
          
        txtSQL = "select * from checkWeek_Info where date= '" & Date & "'"
        Set mrcCS = ExecuteSQL(txtSQL, Msgtext)
          
            '如果 总报表中 没有数据，那么把日汇总表 的内容写入
            mrcCS.AddNew
            mrcCS.Fields(0) = mrcCD.Fields(0)
            mrcCS.Fields(1) = mrcCD.Fields(1)
            mrcCS.Fields(2) = mrcCD.Fields(2)
            mrcCS.Fields(3) = mrcCD.Fields(3)
            mrcCS.Fields(4) = mrcCD.Fields(4)
            mrcCS.Fields(5) = mrcCD.Fields(5)
          
          
        mrcCS.Update        '更新
        mrcCS.Close         '释放内存
          
        mrcCD.Close
              
              
    End Sub

# 后记： #

结账当时困扰了很长的时间，这只是其中的一种办法，还有其他的方法，日结账单和周结账单的代码可以写到自己的窗体之内，这都是自己考虑的啦。中间的逻辑过程有不同的理解的话，欢迎骚扰哦！