机房收费系统之结账总结

【前言】

说到机房结账，说难不难，说简单也简单，只要捋清楚思理接着再写代码就可以了，本次是第一次机房，只是初次接触结账的，只要是账能对的上就可以，这次机房的验收光结账我就验收了三次，还是有个严格的师傅好啊，跟我讲了很多，正好另一个师傅在做机房重构，一些内容和第一的机房的区别都告诉了。

【机房结账理解】

要想把结账做好首先要知道理解它的功能，结账是给谁结的对吧！我把机房项目当作成一个网吧。

一般用户：来上网的人

操作员： 网管

管理员： 网吧老板

管理员那一列就是给网吧老板用的，之前我们涉及到了级别登陆在数据库中User_Info表中不难看出，登陆管理员的账号操作那一下拉菜单，之后老板来看操作员网管一天都办理了几张卡，充值了多少，临时用户又办了几张，最后又退卡还给人家多少钱，最后这就是他一天获利的钱。

结账：三进一出， 有这个理解不会让用你软件的成为一个糊涂老板。

应收金额=充值金额+临时收费金额 -退卡金额

在就是知道SSTab控件中购卡、充值、退卡、临时用户所对应的表，充值金额是固定用户平时充值的记录，而临时用户不具备充值这个功能，所以在管理员算今天柜台上的收入的时候，就应该算上临时用户消费的金额和固定用户充值的金额，再减掉退卡金额。

结账部分代码

    '计算上期充值卡余额
    txtSQL = "select sum(cash) from student_Info where UserID='" & Trim(cmbOpUserID.Text) & "' and Ischeck ='" & "未结账" & "'"
        Set mrcStudent = ExecuteSQL(txtSQL, MsgText)
        If IsNull(mrcStudent.Fields(0)) Then
            sumcash = 0
        Else
            sumcash = mrcStudent.Fields(0)
        End If

    '计算line表当日消费余额
    txtSQL = "select sum(cash) from Line_Info where offdate='" & Date & "'"
        Set mrcline = ExecuteSQL(txtSQL, MsgText)
        If IsNull(mrcline.Fields(0)) Then
            ConsumeCash = 0
        Else
            ConsumeCash = mrcline.Fields(0)
        End If

    '将相应的记录存入到日结表
    txtSQL = "select * from CheckDay_Info"
    Set mrcCheckDay = ExecuteSQL(txtSQL, MsgText)
            
    '当天结多次账的情况，就在原有基础上更新
    mrcCheckDay.AddNew
        mrcCheckDay!RemainCash = sumcash + mrcCheckDay!RemainCash
        mrcCheckDay!RechargeCash = Val(txtRechargeMoney.Text) + mrcCheckDay!RechargeCash
        mrcCheckDay!ConsumeCash = ConsumeCash + mrcCheckDay!ConsumeCash
        mrcCheckDay!CancelCash = Val(txtBackCardMoney.Text) + mrcCheckDay!CancelCash
        mrcCheckDay!AllCash = (sumcash + Val(txtRechargeMoney.Text) - Val(txtBackCardMoney.Text) - ConsumeCash) + mrcCheckDay!AllCash
        mrcCheckDay!Date = Date
        mrcCheckDay.Update
        mrcCheckDay.Close
        mrcStudent.Close
        mrcline.Close


    txtSQL = "select * from student_Info where UserID='" & cmbOpUserID.Text & "'"
        Set mrcStudent = ExecuteSQL(txtSQL, MsgText)

            Do While Not mrcStudent.EOF
                mrcStudent.Fields(11) = "结账"
                mrcStudent.MoveNext
            Loop

    txtSQL = "select * from ReCharge_Info where UserID='" & cmbOpUserID.Text & "'"
        Set mrcRecharge = ExecuteSQL(txtSQL, MsgText)

        Do While Not mrcRecharge.EOF
            mrcRecharge.Fields(7) = "结账"
            mrcRecharge.MoveNext
        Loop

    txtSQL = "select * from CancelCard_Info where UserID='" & cmbOpUserID.Text & "'"
        Set mrcCancelCard = ExecuteSQL(txtSQL, MsgText)

        Do While Not mrcCancelCard.EOF
            mrcCancelCard.Fields(6) = "结账"
            mrcCancelCard.MoveNext
        Loop

        MsgBox "结账成功!", vbOKOnly + vbExclamation, "提示"
            txtSalecard.Text = "0"
            txtBackCardMoney.Text = "0"
            txtBackCard.Text = "0"
            txtSalecardSum.Text = "0"
            txtRechargeMoney.Text = "0"
            txtCollectMoney.Text = "0"
            txtTemRecharge.Text = "0"

【结账查询】

结账的时候应该修改相应的数据库信息（将结账的所有账户信息改为“已结账”），并且在checkWeek_Info和checkDay_Info中添加数据，方便在报表中调用，完成之后将窗体的所有信息清0。

注意：

既然这里将所有信息都改为了“已结账”，那么这些用户下次登录的记录将不再收集，但是确实消费了，所以在下次登录的时候应该将余额大于0的用户统一改回“未结账”

仔细研究下这三个表中的status列和Ischeck列对应下面的代码思考下为什么这么写，代码的重点在查询表语句中。

【如下图和代码】

【代码展示】

   '购卡
    If SSTab1.Tab = 0 Then
        With MSHFlexGrid1
            .CellAlignment = 4
            .Rows = 1
            .TextMatrix(0, 0) = "学号"
            .TextMatrix(0, 1) = "卡号"
            .TextMatrix(0, 2) = "日期"
            .TextMatrix(0, 3) = "时间"
        End With
    End If
    
        '选择student表中用户名为combo且未结账的数据
        txtSQL = "select * from student_Info where UserID='" & cmbOpUserID.Text & "' and Ischeck='未结账 ' and type='固定用户 '"
        Set mrcStudent = ExecuteSQL(txtSQL, MsgText)
        SaleCard = mrcStudent.RecordCount
        
        With MSHFlexGrid1
        Do While Not mrcStudent.EOF
            .Rows = .Rows + 1
            .CellAlignment = 4
            .TextMatrix(.Rows - 1, 0) = mrcStudent.Fields(1)
            .TextMatrix(.Rows - 1, 1) = mrcStudent.Fields(0)
            .TextMatrix(.Rows - 1, 2) = mrcStudent.Fields(12)
            .TextMatrix(.Rows - 1, 3) = mrcStudent.Fields(13)
            mrcStudent.MoveNext
        Loop
        End With
    
    
    
    '充值
    If SSTab1.Tab = 1 Then
        With MSHFlexGrid2
            .CellAlignment = 4
            .Rows = 1
            .TextMatrix(0, 0) = "学号"
            .TextMatrix(0, 1) = "卡号"
            .TextMatrix(0, 2) = "充值金额"
            .TextMatrix(0, 3) = "日期"
            .TextMatrix(0, 4) = "时间"
        End With
    End If
    
        '选择recharge表中用户名为combo、未结账的数据
        txtSQL = "select * from Recharge_Info where UserID='" & cmbOpUserID.Text & "' and status='未结账 '"
        Set mrcRecharge = ExecuteSQL(txtSQL, MsgText)
        RechargeMoney = 0
        
        With MSHFlexGrid2
        Do While Not mrcRecharge.EOF
            .Rows = .Rows + 1
            .CellAlignment = 4
            .TextMatrix(.Rows - 1, 0) = mrcRecharge.Fields(1)
            .TextMatrix(.Rows - 1, 1) = mrcRecharge.Fields(2)
            .TextMatrix(.Rows - 1, 2) = mrcRecharge.Fields(3)
            .TextMatrix(.Rows - 1, 3) = mrcRecharge.Fields(4)
            .TextMatrix(.Rows - 1, 4) = mrcRecharge.Fields(5)
        RechargeMoney = RechargeMoney + mrcRecharge.Fields(3)
        mrcRecharge.MoveNext
        Loop
        End With
    
        
    '退卡
    If SSTab1.Tab = 2 Then
  
        With MSHFlexGrid3
            .CellAlignment = 4
            .Rows = 1
            .TextMatrix(0, 0) = "学号"
            .TextMatrix(0, 1) = "卡号"
            .TextMatrix(0, 2) = "退卡金额"
            .TextMatrix(0, 3) = "日期"
            .TextMatrix(0, 4) = "时间"
        End With
    End If
    
    '选择cancelcard表中用户名为combo、未结账的数据
    txtSQL = "select * from CancelCard_Info where UserId='" & cmbOpUserID.Text & "'and status='未结账 '"
    
    Set mrcCancelCard = ExecuteSQL(txtSQL, MsgText)
        BackCardMoney = 0
        BackCard = mrcCancelCard.RecordCount
    
    With MSHFlexGrid3
    Do While Not mrcCancelCard.EOF
        .Rows = .Rows + 1
        .CellAlignment = 4
        .TextMatrix(.Rows - 1, 0) = mrcCancelCard.Fields(0)
        .TextMatrix(.Rows - 1, 1) = mrcCancelCard.Fields(1)
        .TextMatrix(.Rows - 1, 2) = mrcCancelCard.Fields(2)
        .TextMatrix(.Rows - 1, 3) = mrcCancelCard.Fields(3)
        .TextMatrix(.Rows - 1, 4) = mrcCancelCard.Fields(4)
        BackCardMoney = BackCardMoney + mrcCancelCard.Fields(2)
        mrcCancelCard.MoveNext
    Loop
    End With
    
  
    '临时用户
    If SSTab1.Tab = 3 Then
        With MSHFlexGrid4
            .CellAlignment = 4
            .Rows = 1
            .TextMatrix(0, 0) = "学号"
            .TextMatrix(0, 1) = "卡号"
            .TextMatrix(0, 2) = "日期"
            .TextMatrix(0, 3) = "时间"
        End With
    End If
    '选择学生表中用户名为combo,类型为临时用户，且未结账的用户
    txtSQL = "select * from student_Info where UserID='" & cmbOpUserID.Text & "' and type='临时用户 ' and Ischeck='未结账 '"
    Set mrcStudent = ExecuteSQL(txtSQL, MsgText)
    
    With MSHFlexGrid4
    Do While Not mrcStudent.EOF
        .Rows = .Rows + 1
        .CellAlignment = 4
        .TextMatrix(.Rows - 1, 0) = mrcStudent.Fields(1)
        .TextMatrix(.Rows - 1, 1) = mrcStudent.Fields(0)
        .TextMatrix(.Rows - 1, 2) = mrcStudent.Fields(12)
        .TextMatrix(.Rows - 1, 3) = mrcStudent.Fields(13)
    
        mrcStudent.MoveNext
    Loop
    End With