机房收费系统—结账

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



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

相关文章推荐

机房收费系统—结账

结账无非就是算钱

机房收费系统—结账

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

机房收费系统之周结账单

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

机房收费系统——结账

结账,上下机,还有组合窗体,是机房的三个难点。之所以难更多的是因为内容多,相对的就变得复杂了,思路就会混乱,然后脑袋就大了。静下来细细分析后将每一块分开,单独实现某一功能,然后汇总,那基本上就完成了。...
  • R_s_x
  • R_s_x
  • 2016-10-06 10:41
  • 300

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

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

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

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

机房收费系统之结账

1· 往表里添加一列 alter table checkday_info add serial numeric(18, 0) 添加标识列:不允许有空值,且在属性中“是否标示”选择是,就ok了 格...

机房收费系统关于结账和账单生成的计算问题

在做这一块的时候,会涉及很多的计算问题,当然都是些加减乘除的简单计算,虽然简单,但是要想用代码实现它,就有些晕了,因为我们计算的数据都来自于数据库.          其实这些计算的程序代码是很简单...

机房收费系统之结账与报表(二)周报表制作《一学就会傻瓜版》

我们要用一个软件做报表的模板、然后在VB里面添加部件、代码调用模板,详细步骤如下。 一、下载安装 首先做报表要下载安装Grid++Report 4.5 报表设计器 点击下载(内含破解补丁) 二、制作模...

机房收费系统之结账与报表(三)VB与报表代码《一学就会傻瓜版》

Dim WithEvents Report As grproLibCtl.GridppReport '实例化报表 '报表刷新 Private Sub cmdCheckDay_Click() Dim ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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