机房-结账

开篇：

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

 

逻辑过程：

 

    一、整体要求：

 

        选择操作员用户名之后，操作员姓名以及下面的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

 

 

 

后记：

 

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