机房收费系统—结账

原创 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



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

机房收费系统—结账

第一遍机房收费系统很顺利就完成了,但是并没有完善,借着建一个样板的机会,写一个相对完善的机房收费系统。首先,就是完善结账功能,很多同学都没有做这个,因为前模板就没有完善这个功能,所以我就写这篇文章,给...

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

结账,结的就是哪些账,给谁结账,结账需要涉及到哪些表?这些都是开始设计代码之前首先要想的。        看到这个界面,你会怎么想?对,首先要看需要哪些表来查询相关信息。               ...

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

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

【机房收费系统】日、周结账单

这个功能其实当时做的时候很马虎,不明所以的就查看别人的博客,稀里糊涂的就完成了,但是在后来再看的时候,怎么都觉得不对劲。现在我要从新理一下思路。。 一、【作用功能】         报表的制作方法...

机房收费系统学生下机结账小结

这几天一直在考虑机房收费系统学生下机操作。学生下机,一则需要添加学生下机记录信息;还需要计算学生在整个上机过程中所花费的金额,并且更新学生余额。那么如何做在性能上或者扩展上更好一些呢? ...

机房收费系统之周结账单

机房收费的周结账单是日结账单的衍生,因为在每一天中都可以结好几次帐,最后在日结账单表中一天的记录有好几个,而周结账单就是为了解决这个问题。把每一天的记录汇总成一条来显示出来,方便管理员清楚的了解到每天...

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

对于第一次机房收费系统来说,结账也算一门比较头疼的事情,如果账这个东西理解不清楚的话,真的没法赚钱了,人员的效绩也无法算的门清。可能每个人的机房收费系统都不一样,下面介绍一下我的机房收费系统是如何结账...
  • fjxcsdn
  • fjxcsdn
  • 2017年12月07日 17:00
  • 72

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

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

VB查询数据库之结账——机房收费系统总结(五)

对于机房收费的结账,我感觉是所有窗体中,最难的一个。这个窗体我真的做了好多天。它的难度系数我感觉是最高的。     首先,你要理清上机时间和收费标准的关系,在预备时间中,是不收费的。     其次,在...

机房收费系统(五)——结账

【前言】 机房收费系统终于大收官了,感觉结账可以说是机房收费系统中相对需要好好理清思路的一部分了,其他的现在感觉起来还简单,在结账的过程中每个人都有自己的理解,但是选择自己认为正确的理解去实现功能就好...
  • lnazj
  • lnazj
  • 2016年08月19日 11:00
  • 1181
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:机房收费系统—结账
举报原因:
原因补充:

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