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

前言:谈到结账,大家都觉得挺难的,当然我也是这么觉得的,但是通过一段时间的磨练,最终还是解决了,那么就来分享一下把!

页面

在这里插入图片描述

首先谈到页面,我将临时收费金额改成了上网收费金额。

汇总的计算

售卡张数

就是你今天所出售的卡数
注意: 是今天售的卡数。

退卡张数

即你今天所退的的卡数

充值金额

我认为注册基础余额也是一种充值,所以我的充值是充值的钱+注册时候的钱。

上网收费金额

即是上机产生的消费。

退卡金额

即你今天退卡所返还给用户的钱。

总售卡数

即售卡张数-退卡张数

应收金额

即充值金额(充值金额包括充值金额+注册时所充值的金额)-退卡金额

代码

添加操作员用户名

Private Sub Form_Load()
    '让窗体居中
    Me.Left = Screen.Width / 2 - Me.Width / 2
    Me.Top = Screen.Height / 2 - Me.Height / 2
    BorderStyle = 1
    WindowState = 0
    '添加操作员用户名
    txtSQL = "select *from user_info where level='" & "操作员" & "' or level='" & "管理员" & "'"
    Set mrc_User = ExecuteSQL(txtSQL, MsgText)
    If mrc_User.EOF Then
        Exit Sub
    End If
    
    mrc_User.MoveFirst
    While Not mrc_User.EOF
        ComboUserID.AddItem mrc_User.Fields(0)
        mrc_User.MoveNext
    Wend
    mrc_User.Close
    '调用过程,添加表名
    Call AddName

End Sub

添加操作员真实姓名和四个控件中的内容

Private Sub ComboUserID_click()
    '添加操作员姓名
    txtSQL = "select *from user_info where userid='" & Trim(ComboUserID.Text) & "'"
    Set mrc_User = ExecuteSQL(txtSQL, MsgText)
    If mrc_User.EOF Then
        mrc_User.Close
        Exit Sub
    End If
        lblUsrName.Caption = mrc_User.Fields(3)
    mrc_User.Close
    
    '调用过程,添加表名
    Call AddName
    
    '购卡
    SellNum = 0 '售卡张数为0
    SellMoney = 0
    txtSQL = "select*from student_info where userid='" & ComboUserID.Text & "'" & "and " & _
                "ischeck='" & "未结账" & "'and date='" & Date & "'"
    Set mrc_Stu_Sum = ExecuteSQL(txtSQL, MsgText)
    If mrc_Stu_Sum.EOF = False Then
        mrc_Stu_Sum.MoveFirst
    End If
    
        
        While Not mrc_Stu_Sum.EOF
            With FlexGridSell
                .Rows = .Rows + 1
                .CellAlignment = 4
                .ColAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc_Stu_Sum.Fields(1)) '学号
                .TextMatrix(.Rows - 1, 1) = Trim(mrc_Stu_Sum.Fields(0)) ' "卡号"
                .TextMatrix(.Rows - 1, 2) = Trim(mrc_Stu_Sum.Fields(13)) '"日期"
                .TextMatrix(.Rows - 1, 3) = Trim(mrc_Stu_Sum.Fields(14)) '"时间"
                SellMoney = SellMoney + Val(mrc_Stu_Sum.Fields(7))
                SellNum = SellNum + 1
                mrc_Stu_Sum.MoveNext
            End With
        Wend

    '关闭数据库
    mrc_Stu_Sum.Close
    
    '充值
     AddMoney = 0
    txtSQL = "select*from recharge_info where userid='" & ComboUserID.Text & "'" & "and " & _
                "status='" & "未结账" & "' and date='" & Date & "'"
    Set mrc_RE = ExecuteSQL(txtSQL, MsgText)
    If mrc_RE.EOF = False Then
        mrc_RE.MoveFirst
    End If
    
    
        While Not mrc_RE.EOF
            With FlexGridRe
                .Rows = .Rows + 1
                .CellAlignment = 4
                .ColAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc_RE.Fields(1)) '学号
                .TextMatrix(.Rows - 1, 1) = Trim(mrc_RE.Fields(2)) ' "卡号"
                .TextMatrix(.Rows - 1, 2) = Trim(mrc_RE.Fields(3)) '"充值金额"
                .TextMatrix(.Rows - 1, 3) = Trim(mrc_RE.Fields(4)) '"日期"
                .TextMatrix(.Rows - 1, 4) = Trim(mrc_RE.Fields(5)) '"时间"
                AddMoney = AddMoney + Val(mrc_RE.Fields(3))
                mrc_RE.MoveNext
            End With
        Wend
    mrc_RE.Close
    
    '退卡
    BackCard = 0
    txtSQL = "select*from cancelcard_info where userid='" & ComboUserID.Text & "'" & "and " & _
                "status='" & "未结账" & "' and date='" & Date & "'"


    Set mrc_Stu_Off = ExecuteSQL(txtSQL, MsgText)
    If mrc_Stu_Off.EOF = False Then
        mrc_Stu_Off.MoveFirst
    End If
    
        
        While Not mrc_Stu_Off.EOF
            With FlexGridOff
                .Rows = .Rows + 1
                .CellAlignment = 4
                .ColAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc_Stu_Off.Fields(1))  '学号
                .TextMatrix(.Rows - 1, 1) = Trim(mrc_Stu_Off.Fields(0)) ' "卡号"
                .TextMatrix(.Rows - 1, 2) = Trim(mrc_Stu_Off.Fields(3)) '"日期"
                .TextMatrix(.Rows - 1, 3) = Trim(mrc_Stu_Off.Fields(4)) '"时间"
                .TextMatrix(.Rows - 1, 4) = Trim(mrc_Stu_Off.Fields(2)) '"退卡金额"
                BackNum = BackNum + 1
                BackMoney = BackMoney + Val(mrc_Stu_Off.Fields(2))
                mrc_Stu_Off.MoveNext
            End With
        Wend
    mrc_Stu_Off.Close
    
    '临时用户
    txtSQL = "select*from student_info where userid='" & ComboUserID.Text & "'" & "and " & _
                "ischeck='" & "未结账" & "'" & "and " & _
                "type='" & "临时用户" & "' and date='" & Date & "'"
    Set mrc_Stu_Tem = ExecuteSQL(txtSQL, MsgText)
    If mrc_Stu_Tem.EOF = False Then
        mrc_Stu_Tem.MoveFirst
    End If
    
        
        While Not mrc_Stu_Tem.EOF
            With FlexGridTem
                .Rows = .Rows + 1
                .CellAlignment = 4
                .ColAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc_Stu_Tem.Fields(1)) '学号
                .TextMatrix(.Rows - 1, 1) = Trim(mrc_Stu_Tem.Fields(0)) ' "卡号"
                .TextMatrix(.Rows - 1, 2) = Trim(mrc_Stu_Tem.Fields(13)) '"日期"
                .TextMatrix(.Rows - 1, 3) = Trim(mrc_Stu_Tem.Fields(14)) '"时间"
                .TextMatrix(.Rows - 1, 4) = Trim(mrc_Stu_Tem.Fields(7)) '金额
                mrc_Stu_Tem.MoveNext
            End With
        Wend
    mrc_Stu_Tem.Close
        
        
        
End Sub

结账按钮

Private Sub lblOK_Click()
    '购卡
    txtSQL = "select*from student_info where userid='" & ComboUserID.Text & "'" & "and " & _
                "ischeck='" & "未结账" & "' and date='" & Date & "'"
    Set mrc_Stu_Sum = ExecuteSQL(txtSQL, MsgText)
    If mrc_Stu_Sum.EOF = False Then
        mrc_Stu_Sum.MoveFirst
    End If
        While Not mrc_Stu_Sum.EOF
                mrc_Stu_Sum.Fields(11) = "结账"
                mrc_Stu_Sum.Update
                mrc_Stu_Sum.MoveNext
        Wend

    '关闭数据库
    mrc_Stu_Sum.Close
    
    '充值
     AddMoney = 0
    txtSQL = "select*from recharge_info where userid='" & ComboUserID.Text & "'" & "and " & _
                "status='" & "未结账" & "' and date='" & Date & "'"
    Set mrc_RE = ExecuteSQL(txtSQL, MsgText)
    If mrc_RE.EOF = False Then
        mrc_RE.MoveFirst
    End If
    
    
        While Not mrc_RE.EOF
                AddMoney = AddMoney + Trim(mrc_RE.Fields(3))
                mrc_RE.Fields(7) = "结账"
                mrc_RE.Update
                mrc_RE.MoveNext
        Wend
    mrc_RE.Close
    
    '退卡
    BackCard = 0
    txtSQL = "select*from cancelcard_info where userid='" & ComboUserID.Text & "'" & "and " & _
                "status='" & "未结账" & "' and date='" & Date & "'"


    Set mrc_Stu_Off = ExecuteSQL(txtSQL, MsgText)
    If mrc_Stu_Off.EOF = False Then
        mrc_Stu_Off.MoveFirst
    End If
    
        
        While Not mrc_Stu_Off.EOF
                mrc_Stu_Off.Fields(6) = "结账"
                mrc_Stu_Off.Update
                mrc_Stu_Off.MoveNext
        Wend
    mrc_Stu_Off.Close
    
    '临时用户
''    TemMoney = 0 '临时收费金额
    txtSQL = "select*from student_info where userid='" & ComboUserID.Text & "'" & "and " & _
                "ischeck='" & "未结账" & "'" & "and " & _
                "type='" & "临时用户" & "' and date='" & Date & "'"
    Set mrc_Stu_Tem = ExecuteSQL(txtSQL, MsgText)
    If mrc_Stu_Tem.EOF = False Then
        mrc_Stu_Tem.MoveFirst
    End If
    
        
        While Not mrc_Stu_Tem.EOF
            With FlexGridTem
                .Rows = .Rows + 1
                .CellAlignment = 4
                .ColAlignment = 4
                mrc_Stu_Tem.Fields(11) = "结账"
                mrc_Stu_Tem.MoveNext
            End With
        Wend
    mrc_Stu_Tem.Close
    
    
    '下机
''    TemMoney = 0 '临时收费金额
    txtSQL = "select*from line_info  where userid='" & ComboUserID.Text & "'" & "and " & _
                "statu='" & "未结账" & "' and offdate='" & Date & "'"
    Set mrc_Line = ExecuteSQL(txtSQL, MsgText)
    If mrc_Line.EOF = False Then
        mrc_Line.MoveFirst
    End If
    
        
        While Not mrc_Line.EOF
                mrc_Line.Fields(16) = "结账"
                mrc_Line.MoveNext
        Wend
    mrc_Line.Close
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '调用过程,添加表名
    Call AddName
    
    '临时收费金额
    txtSQL = "select * from line_info where offdate='" & Format(Date, "yyyy-MM-dd") & "'" & _
                    "and userid='" & ComboUserID.Text & "' and offdate='" & Date & "'"
    Set mrc_Line = ExecuteSQL(txtSQL, MsgText)
    If mrc_Line.EOF Then
        TemMoney = 0
    Else
        While Not mrc_Line.EOF
            TemMoney = TemMoney + Val(Trim(mrc_Line.Fields(11)))
            mrc_Line.MoveNext
        Wend
    End If
    
    
'    TemMoney = mrc_Line.Fields(10)
    '售卡张数
    lblSellCardSum.Caption = SellNum
    '退卡张数
    lblBackCardSum.Caption = BackNum
    '充值金额
    lblRecharge.Caption = AddMoney
    '临时收费金额
    lblTemRecharge.Caption = TemMoney
    '退卡金额
    lblBackCardMoney.Caption = BackMoney
    '总售卡数
    SumCard = SellNum - BackNum
    lblSellCardActual.Caption = SumCard
    '所售金额
    SunMoney = AddMoney - BackMoney
    lblCollectMoney.Caption = SunMoney

    '传值去日结账单
    txtSQL = "select *from checkday_info "
    Set mrc_CheckDay = ExecuteSQL(txtSQL, MsgText)
    If mrc_CheckDay.EOF Then
        LastMoney = 0
       
    Else
        mrc_CheckDay.MoveLast
        LastMoney = Trim(mrc_CheckDay.Fields(0))
        
    End If
    
    mrc_CheckDay.AddNew
    mrc_CheckDay.Fields(0) = Trim(LastMoney) '上次余额
    mrc_CheckDay.Fields(1) = AddMoney '充值金额
    mrc_CheckDay.Fields(2) = TemMoney '消费金额
    mrc_CheckDay.Fields(3) = BackMoney '退卡金额
    mrc_CheckDay.Fields(4) = SunMoney '所售金额
    mrc_CheckDay.Fields(5) = Format(Date, "yyyy-MM-dd") '日期
    mrc_CheckDay.Update
    mrc_CheckDay.Close

End Sub

为了避免代码重复过大,所以写的添加导航属性的过程!

Public Sub AddName()
    '购卡添加表头
    With FlexGridSell
        .Rows = 1
        .CellAlignment = 4
        .ColAlignment = 4
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "日期"
        .TextMatrix(0, 3) = "时间"
    End With
    
    '充值添加表头
    With FlexGridRe
        .Rows = 1
        .CellAlignment = 4
        .ColAlignment = 4
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "充值金额"
        .TextMatrix(0, 3) = "日期"
        .TextMatrix(0, 4) = "时间"
    End With
    
    '退卡添加表头
    With FlexGridOff
        .Rows = 1
        .CellAlignment = 4
        .ColAlignment = 4
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "日期"
        .TextMatrix(0, 3) = "时间"
        .TextMatrix(0, 4) = "退卡金额"
    End With
        
    '临时用户添加表头
    With FlexGridTem
        .Rows = 1
        .CellAlignment = 4
        .ColAlignment = 4
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "日期"
        .TextMatrix(0, 3) = "时间"
        .TextMatrix(0, 4) = "消费金额"
    End With
End Sub

希望我的分享能你有所帮助!
感谢您的阅读,如果您有更好的方法,欢迎分享给我,谢谢您!

评论 13
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值