机房收费系统——结账

结账这部分算是机房收费系统的一个难点,之所以它难,是因为这个窗体不光涉及到代码,而且还涉及到算数的问题,还和报表有关,所以它是个难点,但是只要把思路弄清楚了,再难的东西也不怕啦!

思维导图:

在这里插入图片描述
思路有没有清楚一点呢?

代码片段

先让操作员的名字和label框实现同步:

 txtsql = "select * from user_info where userid= '" & Trim(cboOpUserID.Text) & "'"
    Set mrc_u = ExecuteSQL(txtsql, msgtext)
    
    If mrc_u.EOF Then
        mrc_u.Close
        Exit Sub
    Else
        lblusername.Caption = mrc_u.fields(3)
    mrc_u.Close
    
    End If
    call viewdata '调用过程

以一个为例:

    Private Sub viewdata()
Dim txtsql As String
Dim msgtext As String
Dim mrc As ADODB.Recordset
'从student_info中查找未结账和用户名相一致的学生信息显示出来
        txtsql = "select * from student_info where userid= '" & Trim(cboOpUserID.Text) & "'and ischeck='" & "未结账" & "'"

        Set mrc = ExecuteSQL(txtsql, msgtext)
       With MSHFlexGrid1
'       .Rows = 1
       Do While Not mrc.EOF
       
       .CellAlignment = 4
        
        .Rows = .Rows + 1
        .CellAlignment = 4
        .TextMatrix(.Rows - 1, 0) = Trim(mrc.fields(0))
         .TextMatrix(.Rows - 1, 1) = Trim(mrc.fields(1))
        .TextMatrix(.Rows - 1, 2) = Trim(mrc.fields(12))
        .TextMatrix(.Rows - 1, 3) = Trim(mrc.fields(13))
        mrc.MoveNext
      Loop
        
         
          End With
        Call AdjustColWidth(Account, MSHFlexGrid1)
         mrc.Close

重头戏来了,让我们看一下结账部分:
首先我们要先弄清楚以下几个关系:
1、售卡张数=student_info表中的总数
2、退卡张数=cancelcard_info表中的总数
3、总售卡张数=售卡张数-退卡张数
4、临时用户金额=当天未结账的临时用户金额
5、应收金额=充值金额-退卡金额(充值金额=充值金额+注册金额)

下面我们来看一下具体的代码应该怎么写:

'充值部分
Dim txtsql1 As String
Dim msgtext1 As String
Dim mrc1 As ADODB.Recordset  '连接recharge_info表

'退卡部分
Dim txtsql2 As String
Dim msgtext2 As String
Dim mrc2 As ADODB.Recordset  '连接cancel_info表

'临时用户部分
Dim txtsql3 As String
Dim msgtext3 As String
Dim mrc3 As ADODB.Recordset   '连接student_info表

Dim mrc4 As ADODB.Recordset    '连接为结账的学生表

Dim mrc_line As ADODB.Recordset '连接line表

Dim mrc_c As ADODB.Recordset  '连接checkday表
Dim txtsql As String
Dim msgtext As String
Dim mrcstudent As ADODB.Recordset
Dim mrccancel As ADODB.Recordset



txtsql = "select * from student_info "

Set mrcstudent = ExecuteSQL(txtsql, msgtext)


txtsql = "select * from cancelcard_info "

Set mrccancel = ExecuteSQL(txtsql, msgtext)

'把操作员的所有统计信息汇总到汇总列表
txtSellCardSum.Text = mrcstudent.RecordCount '售卡张数

txtBackCardSum.Text = mrccancel.RecordCount '   退卡张数
'售卡总张数
txtSellCardActual.Text = mrcstudent.RecordCount - mrccancel.RecordCount

'退卡金额
txtsql2 = "select sum(cancelcash) from cancelcard_info where userid= '" & Trim(cboOpUserID) & " 'and status='" & "未结账" & "'"
Set mrc2 = ExecuteSQL(txtsql2, msgtext2)
    If IsNull(mrc2.fields(0)) Then
        txtBackCardMoney.Text = "0"
    Else
    
    txtBackCardMoney.Text = mrc2.fields(0)
    End If


'充值金额
txtsql1 = "select sum(addmoney) from recharge_info where userid= '" & Trim(cboOpUserID) & " 'and status='" & "未结账" & "'"


Set mrc1 = ExecuteSQL(txtsql1, msgtext1)
    If IsNull(mrc1.fields(0)) Then
    
        txtRecharge.Text = "0"
    Else
        txtRecharge.Text = mrc1.fields(0)
    End If
    

'临时金额
txtsql3 = "select sum(cash) from student_info where userid= '" & Trim(cboOpUserID) & "'and type='" & "临时用户" & "'and ischeck='" & "未结账" & "'"

Set mrc3 = ExecuteSQL(txtsql3, msgtext3)
    If IsNull(mrc3.fields(0)) Then
        txtTmpRecharge.Text = "0"
    Else
        txtTmpRecharge.Text = mrc3.fields(0)
    End If

'应收金额
'应收金额=充值金额-退卡金额,也就是挣得的钱,充值金额包括充值金额+注册金额
txtCollectMoney.Text = Val(txtRecharge.Text) + Val(txtTmpRecharge.Text) - Val(txtBackCardMoney.Text)
'充值部分
Dim txtsql1 As String
Dim msgtext1 As String
Dim mrc1 As ADODB.Recordset  '连接recharge_info表

'退卡部分
Dim txtsql2 As String
Dim msgtext2 As String
Dim mrc2 As ADODB.Recordset  '连接cancel_info表

'临时用户部分
Dim txtsql3 As String
Dim msgtext3 As String
Dim mrc3 As ADODB.Recordset   '连接student_info表

Dim mrc4 As ADODB.Recordset    '连接为结账的学生表

Dim mrc_line As ADODB.Recordset '连接line表

Dim mrc_c As ADODB.Recordset  '连接checkday表
Dim txtsql As String
Dim msgtext As String
Dim mrcstudent As ADODB.Recordset
Dim mrccancel As ADODB.Recordset



txtsql = "select * from student_info "

Set mrcstudent = ExecuteSQL(txtsql, msgtext)


txtsql = "select * from cancelcard_info "

Set mrccancel = ExecuteSQL(txtsql, msgtext)

'把操作员的所有统计信息汇总到汇总列表
txtSellCardSum.Text = mrcstudent.RecordCount '售卡张数

txtBackCardSum.Text = mrccancel.RecordCount '   退卡张数
'售卡总张数
txtSellCardActual.Text = mrcstudent.RecordCount - mrccancel.RecordCount

'退卡金额
txtsql2 = "select sum(cancelcash) from cancelcard_info where userid= '" & Trim(cboOpUserID) & " 'and status='" & "未结账" & "'"
Set mrc2 = ExecuteSQL(txtsql2, msgtext2)
    If IsNull(mrc2.fields(0)) Then
        txtBackCardMoney.Text = "0"
    Else
    
    txtBackCardMoney.Text = mrc2.fields(0)
    End If


'充值金额
txtsql1 = "select sum(addmoney) from recharge_info where userid= '" & Trim(cboOpUserID) & " 'and status='" & "未结账" & "'"


Set mrc1 = ExecuteSQL(txtsql1, msgtext1)
    If IsNull(mrc1.fields(0)) Then
    
        txtRecharge.Text = "0"
    Else
        txtRecharge.Text = mrc1.fields(0)
    End If
    

'临时金额
txtsql3 = "select sum(cash) from student_info where userid= '" & Trim(cboOpUserID) & "'and type='" & "临时用户" & "'and ischeck='" & "未结账" & "'"

Set mrc3 = ExecuteSQL(txtsql3, msgtext3)
    If IsNull(mrc3.fields(0)) Then
        txtTmpRecharge.Text = "0"
    Else
        txtTmpRecharge.Text = mrc3.fields(0)
    End If

'应收金额
'应收金额=充值金额-退卡金额,也就是挣得的钱,充值金额包括充值金额+注册金额
txtCollectMoney.Text = Val(txtRecharge.Text) + Val(txtTmpRecharge.Text) - Val(txtBackCardMoney.Text)

将结账的钱数同步到日结账里

Dim mrcline As ADODB.Recordset
Dim mrccheckday As ADODB.Recordset
Dim mrcrecharge As ADODB.Recordset



Dim lastrecharge As Integer
Dim LastExitcash As Integer
Dim RemainCash As Integer
Dim RechargeCash As Integer
Dim ConsumeCash As Integer
Dim cancelcash As Integer

'计算上期充值卡余额(上期就是昨天的钱=充值卡的钱-退卡的钱)
'计算昨天充值卡里面的余额
    txtsql = "select sum(addmoney) from recharge_info where date= '" & Date - 1 & "'"
    Set mrcrecharge = ExecuteSQL(txtsql, msgtext)
'判断充值卡里面的钱是否为空
    If IsNull(mrcrecharge.fields(0)) = True Then
        lastrecharge = "0"
    Else
'引用sum函数,默认的addmoney列为field(0)
        lastrecharge = mrcrecharge.fields(0)
'计算昨天退卡表里面的钱
        
    txtsql = "select  sum(cancelcash) from cancelcard_info where date= '" & Date - 1 & "'"
    Set mrccancel = ExecuteSQL(txtsql, msgtext)
        If IsNull(mrccancel.fields(0)) = True Then
            LastExitcash = "0"
        Else
            RemainCash = lastrecharge - LastExitcash
        End If
    End If
 '计算当日充值金额
    txtsql = "select sum(addmoney) from recharge_info where date='" & Date & "'"
    Set mrcrecharge = ExecuteSQL(txtsql, msgtext)
        If IsNull(mrcrecharge.fields(0)) Then
            RechargeCash = "0"
        Else
            RechargeCash = mrcrecharge.fields(0)
        End If
'计算当日消费金额
    txtsql = "select sum(consume) 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 sum(cancelcash) from cancelcard_info where date= '" & Date & "'"
    Set mrccancel = ExecuteSQL(txtsql, msgtext)
    If IsNull(mrccancel.fields(0)) Then
        cancelcash = "0"
    Else
        cancelcash = mrccancel.fields(0)
    End If
'清楚当天记录
    txtsql = "delete checkday_info where date= '" & Date & "'"
    Set mrccheckday = ExecuteSQL(txtsql, msgtext)
'将数据同步到checkday_info 表中
    txtsql = "select * from checkday_info "
    Set mrccheckday = ExecuteSQL(txtsql, msgtext)
    
    mrccheckday.AddNew
    
    mrccheckday!RemainCash = Val(RemainCash)
    mrccheckday!RechargeCash = Val(RechargeCash)
    mrccheckday!ConsumeCash = Val(ConsumeCash)
    mrccheckday!cancelcash = Val(cancelcash)
    mrccheckday!allcash = Val(RechargeCash) - Val(cancelcash)
    mrccheckday!Date = Date
    mrccheckday.Update
    mrccheckday.Close
    
    MsgBox "结账成功!", 0 + 46, "提示"
    
    txtsql = "select * from student_info where date = '" & Date & "'and ischeck= '" & "未结账" & "'"
    Set mrcstudent = ExecuteSQL(txtsql, msgtext)
    
    Do While Not mrcstudent.EOF
        mrcstudent!ischeck = "结账"
        mrcstudent.Update
        mrcstudent.MoveNext
        
    Loop
        mrcstudent.Close
        
    txtsql = "select * from recharge_info where  date= '" & Date & " 'and status= '" & "未结账" & "'"
    Set mrcrecharge = ExecuteSQL(txtsql, msgtext)
    
    Do While Not mrcrecharge.EOF
        mrcrecharge!status = "结账"
        mrcrecharge.Update
        mrcrecharge.MoveNext
        
    Loop
        mrcrecharge.Close
        
    txtsql = "select * from cancelcard_info where date = '" & Date & "'and status='" & "未结账" & "'"
    Set mrccancel = ExecuteSQL(txtsql, msgtext)
    
    Do While Not mrccancel.EOF
        mrccancel!status = "结账"
        mrccancel.Update
        mrccancel.MoveNext
        
    Loop
        mrccancel.Close

最后在窗体加载事件中写以下代码:

Private Sub Form_Load()
Dim msgtext As String
Dim txtsql As String
Dim mrc_u As ADODB.Recordset


    txtsql = "select * from user_info where level='" & "操作员" & "'or level= '" & "管理员" & "'"
    
    Set mrc_u = ExecuteSQL(txtsql, msgtext)
    If mrc_u.RecordCount = 0 Then
    
        MsgBox "无操作员记录", 0 + 48, "提示"
        Exit Sub
    
    Else
    
    Do While mrc_u.EOF = False
         cboOpUserID.AddItem mrc_u.fields(0)
         mrc_u.MoveNext
    Loop
    End If
    
    
    
'为三张表添加表头
With MSHFlexGrid1
        .Rows = 1
        .CellAlignment = 4
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "日期"
        .TextMatrix(0, 3) = "时间"
End With

With MSHFlexGrid2
        .Rows = 1
        .CellAlignment = 4
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "充值金额"
        .TextMatrix(0, 3) = "日期"
        .TextMatrix(0, 4) = "时间"
End With

With MSHFlexGrid3
        .Rows = 1
        .CellAlignment = 4
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "日期"
        .TextMatrix(0, 3) = "时间"
        .TextMatrix(0, 4) = "退卡金额"
End With

With MSHFlexGrid4
        .Rows = 1
        .CellAlignment = 4
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "日期"
        .TextMatrix(0, 3) = "时间"
        
End With

End Sub
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 4
    评论
【优质项目推荐】 1、项目代码均经过严格本地测试,运行OK,确保功能稳定后才上传平台。可放心下载并立即投入使用,若遇到任何使用问题,随时欢迎私信反馈与沟通,博主会第一时间回复。 2、项目适用于计算机相关专业(如计科、信息安全、数据科学、人工智能、通信、物联网、自动化、电子信息等)的在校学生、专业教师,或企业员工,小白入门等都适用。 3、该项目不仅具有很高的学习借鉴价值,对于初学者来说,也是入门进阶的绝佳选择;当然也可以直接用于 毕设、课设、期末大作业或项目初期立项演示等。 3、开放创新:如果您有一定基础,且热爱探索钻研,可以在此代码基础上二次开发,进行修改、扩展,创造出属于自己的独特应用。 欢迎下载使用优质资源!欢迎借鉴使用,并欢迎学习交流,共同探索编程的无穷魅力! 基于业务逻辑生成特征变量python实现源码+数据集+超详细注释.zip基于业务逻辑生成特征变量python实现源码+数据集+超详细注释.zip基于业务逻辑生成特征变量python实现源码+数据集+超详细注释.zip基于业务逻辑生成特征变量python实现源码+数据集+超详细注释.zip基于业务逻辑生成特征变量python实现源码+数据集+超详细注释.zip基于业务逻辑生成特征变量python实现源码+数据集+超详细注释.zip基于业务逻辑生成特征变量python实现源码+数据集+超详细注释.zip 基于业务逻辑生成特征变量python实现源码+数据集+超详细注释.zip 基于业务逻辑生成特征变量python实现源码+数据集+超详细注释.zip
提供的源码资源涵盖了安卓应用、小程序、Python应用和Java应用等多个领域,每个领域都包含了丰富的实例和项目。这些源码都是基于各自平台的最新技术和标准编写,确保了在对应环境下能够无缝运行。同时,源码中配备了详细的注释和文档,帮助用户快速理解代码结构和实现逻辑。 适用人群: 这些源码资源特别适合大学生群体。无论你是计算机相关专业的学生,还是对其他领域编程感兴趣的学生,这些资源都能为你提供宝贵的学习和实践机会。通过学习和运行这些源码,你可以掌握各平台开发的基础知识,提升编程能力和项目实战经验。 使用场景及目标: 在学习阶段,你可以利用这些源码资源进行课程实践、课外项目或毕业设计。通过分析和运行源码,你将深入了解各平台开发的技术细节和最佳实践,逐步培养起自己的项目开发和问题解决能力。此外,在求职或创业过程中,具备跨平台开发能力的大学生将更具竞争力。 其他说明: 为了确保源码资源的可运行性和易用性,特别注意了以下几点:首先,每份源码都提供了详细的运行环境和依赖说明,确保用户能够轻松搭建起开发环境;其次,源码中的注释和文档都非常完善,方便用户快速上手和理解代码;最后,我会定期更新这些源码资源,以适应各平台技术的最新发展和市场需求。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值