机房收费系统—结账

原创 2016年08月29日 17:07:27

结账也是机房收费系统中难点之一,首先要理清楚怎样结账。

售卡张数:student_Info 表中 未结账的卡的总数
退卡张数:cancelCard_Info 表中 未结账 退卡的总数
总收卡张数:售卡张数-退卡张数
充值金额:recharge_Info 表中 未结账 的充值金额(包括注册时候充值的金额和充值金额)
退卡金额:cancelCard_Info 表中 未结账 的退卡金额
应收金额:充值金额—退卡金额
临时收费金额:临时用户应收的金额

Private Sub cmdaccounts_Click(Index As Integer)   '结账时,如果管理员开卡了了,也应该给管理员结账,不能只给操作员结账。
Dim txtSQL As String
Dim msgText As String
Dim mrc As ADODB.Recordset
Dim mrcc As ADODB.Recordset
Dim mrb As ADODB.Recordset
Dim mrbb As ADODB.Recordset
Dim mrd As ADODB.Recordset
Dim mrdd As ADODB.Recordset
Dim lastmoney As String
Dim nowmoney As String

'更改数据库中的结账状态
txtSQL = "select Ischeck from student_Info where UserID='" & Trim(ComboUserID.Text) & "' and IsCheck='未结账'"
Set mrc = ExecuteSQL(txtSQL, msgText)
    Do While Not mrc.EOF
        mrc.Fields(0) = Trim("结账")
        mrc.Update
        mrc.MoveNext
    Loop
        mrc.Close
        
txtSQL = "select * from cancelcard_info where UserID = '" & Trim(ComboUserID.Text) & "' and status = '" & "未结账" & "'"
Set mrcc = ExecuteSQL(txtSQL, msgText)
   Do While Not mrcc.EOF
      mrcc.Fields(6) = Trim("结账")
      mrcc.Update
      mrcc.MoveNext
   Loop
      mrcc.Close

txtSQL = "select * from recharge_info where UserID = '" & Trim(ComboUserID.Text) & "' and status = '未结账'"
Set mrbb = ExecuteSQL(txtSQL, msgText)
   Do While Not mrbb.EOF
      mrbb.Fields(7) = Trim("结账")
      mrbb.Update
      mrbb.MoveNext
   Loop
      mrbb.Close
      
 '修改成功,将刚才的操作写入日报表
 Call ReportDay
 Call ReportSum
 
 MsgBox "恭喜您,结账成功", vbOKOnly + vbExclamation '"警告"
 Unload Me
 
   Call viewdate
End Sub
Private Sub ReportDay()
    Dim txtSQL As String
    Dim msgText As String
    
    Dim mrCD As ADODB.Recordset
    Dim mrcSD As ADODB.Recordset
    Dim mrcRC As ADODB.Recordset
    Dim mrcL As ADODB.Recordset
    Dim mrcCD 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) 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.Fields(0).Value) 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
 '向日结账单中添加记录CheckDay 表
        mrcCD.AddNew
        mrcCD.Fields(0) = Val(CardCash) - Val(rechargeCash) + Val(lineCash) + Val(cancelcash) '上期充值卡金额
        mrcCD.Fields(1) = Val(rechargeCash)  '当日充值卡金额
        mrcCD.Fields(2) = Val(lineCash)      '当日消费金额
        mrcCD.Fields(3) = Val(cancelcash)    '当日退卡金额
        mrcCD.Fields(4) = Val(CardCash)      '本期充值卡金额
        mrcCD.Fields(5) = Date
        mrcCD.Update
        mrcCD.Close
End Sub
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)  
    <span style="white-space:pre">	</span>mrcCS.Update        '更新
    <span style="white-space:pre">	</span>mrcCS.Close         '释放内存
        mrcCD.Close     
End Sub
Private Sub ComboUserName_click()
Dim msgText As String
Dim mrc As ADODB.Recordset
Dim txtSQL As String

'从数据库中调出操作员的卡号
txtSQL = "select * from User_Info where Level='" & "操作员" & "'"
Set mrc = ExecuteSQL(txtSQL, msgText)
Do While mrc.EOF = False

    ComboUserID.AddItem mrc.Fields(0)
    mrc.MoveNext
    
Loop

Call viewdate
End Sub
Private Sub ComboUserID_click()
'从数据库中调出所选操作员的姓名
Dim msgText As String
Dim mrc As ADODB.Recordset
Dim txtSQL As String

txtSQL = "select * from User_Info where userID='" & ComboUserID.Text & "'"
Set mrc = ExecuteSQL(txtSQL, msgText)
    ComboUserName.Text = mrc.Fields(3)
mrc.Close
  
Call viewdate
End Sub
Private Sub Form_Load()
Dim msgText As String
Dim mrc As ADODB.Recordset
Dim txtSQL As String

'从数据库中调出操作员的卡号
txtSQL = "select * from User_Info where Level='" & "操作员" & "'"
Set mrc = ExecuteSQL(txtSQL, msgText)
Do While mrc.EOF = False

    ComboUserID.AddItem mrc.Fields(0)
    mrc.MoveNext
    
Loop

SSTab1.Tab = 0 '设置刚刚载入窗体时候的选项卡的位置
End Sub
Private Sub viewdate()
Dim txtSQL As String
Dim mrc As ADODB.Recordset
Dim mrc1 As ADODB.Recordset
Dim mrc2 As ADODB.Recordset
Dim mrc3 As ADODB.Recordset
Dim mrc4 As ADODB.Recordset
Dim mrc5 As ADODB.Recordset
Dim msgText As String
Dim cancelcash As Variant
Dim linshicash As Variant
Dim rechargeCash As Variant

'注册时
    With myflexgrid1
        .CellAlignment = 4
        .Rows = 1
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "日期"
        .TextMatrix(0, 3) = "时间"
        
        '选择学生表中用户名为combo、未结账数据
        txtSQL = "select * from student_Info where UserID ='" & Trim(ComboUserID.Text) & "' and Ischeck='" & "未结账" & "'"

        
            Do While Not mrc1.EOF
                .Rows = .Rows + 1
                .CellAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc1.Fields(1) & "")
                .TextMatrix(.Rows - 1, 1) = Trim(mrc1.Fields(0) & "")
                .TextMatrix(.Rows - 1, 2) = Trim(mrc1.Fields(12) & "")
                .TextMatrix(.Rows - 1, 3) = Trim(mrc1.Fields(13) & "")
                mrc1.MoveNext
            Loop
    End With


 '当点击充值时
    With myflexgrid2
        .CellAlignment = 4
        .Rows = 1
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "充值金额"
        .TextMatrix(0, 3) = "日期"
        .TextMatrix(0, 4) = "时间"
        
        '选择学生表中用户名为combo、未结账数据
        txtSQL = "select* from ReCharge_Info where UserID ='" & Trim(ComboUserID.Text) & "' and status='" & "未结账" & "' "

            Do While Not mrc2.EOF
                .Rows = .Rows + 1
                .CellAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc2.Fields(1) & "")
                .TextMatrix(.Rows - 1, 1) = Trim(mrc2.Fields(2) & "")
                .TextMatrix(.Rows - 1, 2) = Trim(mrc2.Fields(3) & "")
                .TextMatrix(.Rows - 1, 3) = Trim(mrc2.Fields(4) & "")
                .TextMatrix(.Rows - 1, 4) = Trim(mrc2.Fields(5) & "")
                rechargeCash = rechargeCash + mrc2.Fields(3)          '汇总充值金额
                mrc2.MoveNext
            Loop
    End With


 '当点击退卡时
    With myflexgrid3
        .CellAlignment = 4
        .Rows = 1
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "退卡金额"
        .TextMatrix(0, 3) = "日期"
        .TextMatrix(0, 4) = "时间"
        
        '选择学生表中用户名为combo、未结账数据
        txtSQL = "select* from CancelCard_Info where UserID ='" & Trim(ComboUserID.Text) & "' and status='" & "未结账" & "'"
        Set mrc3 = ExecuteSQL(txtSQL, msgText)

            Do While Not mrc3.EOF
                .Rows = .Rows + 1
                .CellAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc3.Fields(0) & "")
                .TextMatrix(.Rows - 1, 1) = Trim(mrc3.Fields(1) & "")
                .TextMatrix(.Rows - 1, 2) = Trim(mrc3.Fields(2) & "")
                .TextMatrix(.Rows - 1, 3) = Trim(mrc3.Fields(3) & "")
                .TextMatrix(.Rows - 1, 4) = Trim(mrc3.Fields(4) & "")
                cancelcash = cancelcash + mrc3.Fields(2)              '汇总退卡金额
                mrc3.MoveNext
            Loop
    End With


  '当点击临时用户时
    With myflexgrid4
        .CellAlignment = 4
        .Rows = 1
        .TextMatrix(0, 0) = "学号"
        .TextMatrix(0, 1) = "卡号"
        .TextMatrix(0, 2) = "日期"
        .TextMatrix(0, 3) = "时间"
   End With
        '选择学生表中用户名为combo、临时用户、未结账数据
        txtSQL = "select* from student_Info where UserID ='" & Trim(ComboUserID.Text) & "' and Ischeck='未结账' and type='临时用户'"
        Set mrc = ExecuteSQL(txtSQL, msgText)
        If mrc.EOF = True Then
'            MsgBox "没有记录", vbOKOnly + vbExclamation, "警告"
            linshicash = "0"  '若没有临时用户,临时用户的金额未经0
        Else
            linshicash = mrc.Fields(7)
            txtSQL = "select * from line_Info where cardno='" & Trim(mrc.Fields(0)) & "'"
            Set mrc4 = ExecuteSQL(txtSQL, msgText)
            
            With myflexgrid4
            Do While Not mrc.EOF
                .Rows = .Rows + 1
                .CellAlignment = 4
                .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1) & "")
                .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(0) & "")
                .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(12) & "")
                .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(13) & "")
                linshicash = Val(linshicash) + Val(Trim(mrc4.Fields(11)))    '临时收费金额=卡内剩余金额+消费金额
                mrc4.MoveNext
                mrc.MoveNext
            Loop
            End With
        End If
         
   '当点击结账时
    salecard.Text = mrc1.RecordCount
    cancelcard.Text = mrc3.RecordCount
    rechargemoney.Text = Val(rechargeCash)
    linshimoney.Text = Val(linshicash)
    cancelmoney.Text = Val(cancelcash)
    card.Text = Val(salecard.Text) - Val(cancelcard.Text)
    cash.Text = Val(rechargemoney.Text) - Val(cancelmoney.Text)

End Sub
Private Sub SSTab1_Click(previousTab As Integer)

If SSTab1.Tab = 5 Then   '当点击退出时
    Unload Me
End If

End Sub



版权声明:本文为博主原创文章,未经博主允许不得转载。

【机房收费系统】结账

我在进行结账窗体的时候,真的是被困了很长时间,好多逻辑性的东西以及一些常识性的东西都没有考虑到,花了整整1天时间才算是初步整理清楚,下面就将一些我遇到的问题以及应该考虑到的问题写下来,希望可以帮助到有...
  • G165945348
  • G165945348
  • 2016年09月09日 20:57
  • 343

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

说起机房,我个人感觉最具有挑战性的就是上下机,组合查询,以及结账。下面我就来说一说结账。 结账,概括的来说,就让管理员知道每天机房的金钱收支。详细的说是把每个操作员一天收支明细列出来让管理员一目了然...
  • aiming66
  • aiming66
  • 2016年08月10日 20:58
  • 728

机房收费系统—结账

机房收费系统快要接近尾声了,到了最后有关钱的部分,在没有理清计算关系之前真的是一头雾水,就像是中间遇到过的一些困难,只要理清关系代码就很容易写了。...
  • xdd19910505
  • xdd19910505
  • 2013年10月31日 18:01
  • 1026

机房收费系统之结账窗体的思考

机房收费系统的结账窗体算是一个难点,也提供了一个提高我们自己考虑问题和解决问题的能力的机会。下面说说结账的问题:        结账流程图是这样的:(图片借鉴来自谷浩樟同学博客) 结账窗体的界面:...
  • ght886
  • ght886
  • 2017年08月23日 20:58
  • 847

【机房收费系统——报表与vb交互之日结账和周结账】

在上一篇中已经介绍了报表的模板
  • u013045437
  • u013045437
  • 2014年08月14日 18:36
  • 1262

机房收费系统之八(组合查询)

1.组合查询:涉及的窗体有学生基本信息维护,学生上机记录查询,学生上机统计查询 初次见到组合查询界面,有种似曾相识的感觉。没错,我们在学生信息管理系统里面见过类似的。也可以说我们已经接触过组合查询了。...
  • ght886
  • ght886
  • 2017年06月11日 16:39
  • 479

【机房收费系统】日结账单报表实现

【背景】    经过看同学们的博客和自己的亲身实践,终于把报表这一块弄明白了,是应该该写个博客总结一下了。这一篇博客介绍简单的报表制作过程,并以日结账单为例,因为日结算是比较简单,它没有时间限制,也没...
  • u013044029
  • u013044029
  • 2015年05月06日 21:45
  • 1074

机房收费系统之日结账单和周结账单

日结和周结其实逻辑是一样的,不过就是日结只结了今天的账,而周结结的是选中日期之间的账。 一、日结账单 我日结账单采取的方法是:先判断今天的账结了吗,如果已经结了,就从日结账单表中删除记录,再结一次...
  • u013031565
  • u013031565
  • 2014年10月09日 09:41
  • 2376

【机房收费系统】——结账

众所周知,结账可以汇总一天的销售额。对自己的经营管理提出良好建议的基础。根据对于近期销售的梳理进行改革和创新。结账在机房收费系统中是重头戏,设计几张表的嵌套。结合实际生活,可以更好更深的理解窗体中每个...
  • fjj15732621696
  • fjj15732621696
  • 2015年08月16日 15:34
  • 478

机房收费系统-结账

前言:      敲着敲着,就到了最后的结账。一段时间与代码的碰撞,发现早已褪去了起初的畏惧和怀疑,反而越来越自信和自如了。这就是所谓的增加了代码的亲切感吧!突然发现生活开始越来越离不开代码,有时候就...
  • weienjun
  • weienjun
  • 2016年07月30日 19:10
  • 386
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:机房收费系统—结账
举报原因:
原因补充:

(最多只允许输入30个字)