第一次机房收费系统之结账

最新推荐文章于 2019-12-23 19:29:47 发布

孙正嗯大正

最新推荐文章于 2019-12-23 19:29:47 发布

阅读量296

点赞数

分类专栏：第一次机房收费系统

本文链接：https://blog.csdn.net/sunzheng176/article/details/102825446

版权

第一次机房收费系统专栏收录该内容

9 篇文章 0 订阅

订阅专栏

前言：
机房收费系统的结账窗体主要是先想清楚功能的逻辑思路。
1.结账是结谁的帐？
结账是管理员的权限，是管理员给操作员结账，操作员工作期间售卡以及充值的金额还有退卡时应退给学生的金额。所以结账是操作员工作期间机房的收入。
2.怎么结账？
在SSTab控件中有购卡，充值，退卡，临时用户几个选项卡，临时用户和购卡的收费金额已经结算到充值中，所以现在清楚结账就是：应收金额=充值金额-退卡金额
部分代码：
单击选项卡：

Private Sub SSTab_Click(PreviousTab As Integer)
    Dim Smrc As ADODB.Recordset
    Dim Rmrc As ADODB.Recordset
    Dim Cmrc As ADODB.Recordset
    Dim SLmrc As ADODB.Recordset
    
    
    Dim StxtSQL As String
    Dim RtxtSQL As String
    Dim CtxtSQL As String
    Dim SLtxtSQL As String
    
    Dim Smsgtext As String
    Dim Rmsgtext As String
    Dim Cmsgtext As String
    Dim SLmsgtext As String
    
    Dim i As Integer
    Dim ReChargeSum As Single                   '定义充值金额
    Dim j As Integer
    Dim BackCardMoneySum As Single              '定义退卡金额
    
    '连接student_info表
    StxtSQL = "select * from student_info where userid ='" & Trim(cboOpUserID.Text) & "' and ischeck='未结账'"
    Set Smrc = ExecuteSQL(StxtSQL, Smsgtext)
        
    '连接recharge_info表
    RtxtSQL = "select * from recharge_info where userid='" & Trim(cboOpUserID.Text) & "' and status='未结账'"
    Set Rmrc = ExecuteSQL(RtxtSQL, Rmsgtext)
    
    '连接cancelcard_info表
    CtxtSQL = "select * from cancelcard_info where userid='" & Trim(cboOpUserID.Text) & "' and status='未结账' "
    Set Cmrc = ExecuteSQL(CtxtSQL, Cmsgtext)
    
    '连接student_info表
    SLtxtSQL = "select * from student_info where userid ='" & Trim(cboOpUserID.Text) & "' and ischeck='未结账' and type='临时用户'"
    Set SLmrc = ExecuteSQL(SLtxtSQL, SLmsgtext)
    
    Select Case SSTab.Tab
    '选择购卡
    Case 0
        '显示数据
        With MSHFlexGrid1
            .rows = 1
            .CellAlignment = 4
            .ColAlignment = 4
            .TextMatrix(0, 0) = "学号"
            .TextMatrix(0, 1) = "卡号"
            .TextMatrix(0, 2) = "日期"
            .TextMatrix(0, 3) = "时间"
            Do While Not Smrc.EOF
                .rows = .rows + 1
                .CellAlignment = 4
                .ColAlignment = 4
                .TextMatrix(.rows - 1, 0) = Smrc.Fields(1)
                .TextMatrix(.rows - 1, 1) = Smrc.Fields(0)
                .TextMatrix(.rows - 1, 2) = Smrc.Fields(12)
                .TextMatrix(.rows - 1, 3) = Smrc.Fields(13)
                Smrc.MoveNext
            Loop
            AdjustColWidth Me, MSHFlexGrid1
        End With
    '选择充值
    Case 1
        '显示数据
        With MSHFlexGrid2
            .rows = 1
            .CellAlignment = 4
            .ColAlignment = 4
            .TextMatrix(0, 0) = "学号"
            .TextMatrix(0, 1) = "卡号"
            .TextMatrix(0, 2) = "充值金额"
            .TextMatrix(0, 3) = "日期"
            .TextMatrix(0, 4) = "时间"
            Do While Not Rmrc.EOF
                .rows = .rows + 1
                .CellAlignment = 4
                .ColAlignment = 4
                .TextMatrix(.rows - 1, 0) = Rmrc.Fields(1)
                .TextMatrix(.rows - 1, 1) = Rmrc.Fields(2)
                .TextMatrix(.rows - 1, 2) = Rmrc.Fields(3)
                .TextMatrix(.rows - 1, 3) = Rmrc.Fields(4)
                .TextMatrix(.rows - 1, 4) = Rmrc.Fields(5)
                Rmrc.MoveNext
            Loop
            AdjustColWidth Me, MSHFlexGrid1
        End With
    '选择退卡
    Case 2
        '显示数据
        With MSHFlexGrid3
            .rows = 1
            .CellAlignment = 4
            .ColAlignment = 4
            .TextMatrix(0, 0) = "学号"
            .TextMatrix(0, 1) = "卡号"
            .TextMatrix(0, 2) = "日期"
            .TextMatrix(0, 3) = "时间"
            .TextMatrix(0, 4) = "退卡金额"
            Do While Not Cmrc.EOF
                .rows = .rows + 1
                .CellAlignment = 4
                .ColAlignment = 4
                .TextMatrix(.rows - 1, 0) = Cmrc.Fields(0)
                .TextMatrix(.rows - 1, 1) = Cmrc.Fields(1)
                .TextMatrix(.rows - 1, 2) = Cmrc.Fields(3)
                .TextMatrix(.rows - 1, 3) = Cmrc.Fields(4)
                .TextMatrix(.rows - 1, 4) = Cmrc.Fields(2)
                Cmrc.MoveNext
            Loop
            AdjustColWidth Me, MSHFlexGrid1
        End With
    '选择临时用户
    Case 3
        '显示数据
        With MSHFlexGrid4
            .rows = 1
            .CellAlignment = 4
            .ColAlignment = 4
            .TextMatrix(0, 0) = "学号"
            .TextMatrix(0, 1) = "卡号"
            .TextMatrix(0, 2) = "日期"
            .TextMatrix(0, 3) = "时间"
            Do While Not SLmrc.EOF
                .rows = .rows + 1
                .CellAlignment = 4
                .ColAlignment = 4
                .TextMatrix(.rows - 1, 0) = SLmrc.Fields(1)
                .TextMatrix(.rows - 1, 1) = SLmrc.Fields(0)
                .TextMatrix(.rows - 1, 2) = SLmrc.Fields(12)
                .TextMatrix(.rows - 1, 3) = Trim(SLmrc.Fields(13))
                SLmrc.MoveNext
            Loop
            AdjustColWidth Me, MSHFlexGrid1
        End With
    '选择汇总
    Case 4
        '计算售卡张数
        txtSellCardSum.Text = Smrc.RecordCount
        '计算退卡张数
        txtBackCardSum.Text = Cmrc.RecordCount
        '计算总售卡张数
        txtSellCardActual.Text = Smrc.RecordCount - Cmrc.RecordCount
        
        '计算充值金额
        For i = 0 To Rmrc.RecordCount - 1
            ReChargeSum = ReChargeSum + Val(Rmrc.Fields(3))
            Rmrc.MoveNext
        Next i
        txtRecharge.Text = ReChargeSum
        
        '计算退卡金额
        For j = 0 To Cmrc.RecordCount - 1
            BackCardMoneySum = BackCardMoneySum + Val(Cmrc.Fields(2))
            Cmrc.MoveNext
        Next j
        txtBackCardMoney.Text = BackCardMoneySum
        
        '计算应收金额
        txtCollectMoney.Text = txtRecharge.Text - txtBackCardMoney.Text
    Case 5
        Unload Me
    End Select
    Smrc.Close
    Rmrc.Close
    Cmrc.Close
    SLmrc.Close
End Sub

结账:

Private Sub cmdAccount_Click()
    Dim Smrc, Rmrc, Cmrc As ADODB.Recordset
    Dim StxtSQL, RtxtSQL, CtxtSQL As String
    Dim Smsgtext, Cmsgtext, Rmsgtext As String

    If Not Testtxt(cboOpUserID.Text) Then
       MsgBox "请先选择操作员后结账！", vbOKOnly + vbExclamation, "提示"
       Exit Sub
    End If
    
    '更新student_Info表的更新状态
    StxtSQL = "update student_Info set Ischeck ='已结账' where userid= '" & cboOpUserID & "'"
    Call ExecuteSQL(StxtSQL, Smsgtext)
    
    '更新recharge_Info表的更新状态
    RtxtSQL = "update recharge_Info set status ='已结账' where userid= '" & cboOpUserID & "'"
    Call ExecuteSQL(RtxtSQL, Rmsgtext)
    
    '更新cancelcard_Info表的更新状态
    CtxtSQL = "update cancelcard_Info set status ='已结账' where userid= '" & cboOpUserID & "'"
    Call ExecuteSQL(CtxtSQL, Cmsgtext)
    
    MsgBox "结账成功！", vbOKOnly + vbExclamation, "提示"
    
End Sub