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

素颜马尾好姑娘i 2024-02-17 17:51 156阅读 0赞

说起机房，我个人感觉最具有挑战性的就是上下机，组合查询，以及结账。下面我就来说一说结账。

结账，概括的来说，就让管理员知道每天机房的金钱收支。详细的说是把每个操作员一天收支明细列出来让管理员一目了然。

下面我们先来看看结账的界面，如下图

Center

经过分析，我们可知道，这就是管理员看操作员一天的工作明细，即售卡，充值，退卡的情况。

1、 购卡：就是在student_info表总查询没有结账的那些新注册的学生卡号等信息。

2、 充值：在recharge_info表中查询未结账的充值信息。

3、 退卡：在cancelCard_info中查询未结账的退卡信息。

4、 临时用户：在student_info表中查询未结账的，没有退卡的临时用户。

5、 汇总：将前面所有的信息汇总起来。用到了student_Info 、Recharge_Info 、cancelcard_Info 表。

6、 售卡张数=购卡选项卡的记录总数

7、 退卡张数=退卡选项卡的记录总数

8、 退卡金额=cancelcard_Info表中的金额进行累加

9、 总售卡数=售卡数-退卡数

10、 注册和充值金额=student_Info 表中注册时候的充值金额+Recharge_Info 表中的充值金额（注册时的充值的钱和充值时充进去的钱其实都一样）

11、 临时收费=临时收费选项卡记录中的卡号上机所消费的钱的总和(Line_Info)（只是用来显示）

12、应收金额=注册和充值金额-退卡金额

但是，这样使用三个表相互查询，略觉复杂。我们可以在recharge表中添个字段，让问题变得简单点。值使用两个表即可将此窗体功能实现。如下图：

Center 1

只需添加图中的两个字段，就可以在实现本窗体功能时，丢弃student表了。

功能分析如下：

1、 购卡：就是在recharge_info表总查询没有结账的那些新注册的学生卡号等信息。

2、 充值：在recharge_info表中查询未结账的充值信息。

3、 退卡：在cancelCard_info中查询未结账的退卡信息。

4、 临时用户：在recharge_info表中查询未结账的，没有退卡的临时用户。

5、 汇总：因为我们数据表的改动，所以我们只是用到了Recharge_Info 、cancelcard_Info 表。

代码如下：

  1. Dim txtSQL As String
  2. Dim msgtext As String
  3. Dim checkDaymrc As ADODB.Recordset
  4. Dim LineMrc As ADODB.Recordset
  5. Dim CancelMrc As ADODB.Recordset
  6. Dim REChargeMrc As ADODB.Recordset
  7. Dim Yescheckmrc As ADODB.Recordset
  9. 'Dim cancelCash1 As String '用于记录退卡总金额
  10. Dim Allcash1 As String '用户记录注册和充值总金额
  14. Private Sub cmdcheckout_Click()
  15.     '打开日结账单 的表，查询上次结账时是否有记录，如果没有则
  16.     'remainCash清零。否则将上次的记录填上。
  17.     '每次都是增加一条记录，将汇总里面的信息填上即可。并且情况，然后对其数据库标记
  19.     Dim Remaincash As String '上期余额
  20.     Dim RechargeCash As String '上期充值
  21.     Dim ConsumCash As String '学生消费
  22.     ConsumCash = 0
  24.     '判断下拉菜单中是否有东西
  25.     If ComLevel.Text = "" Then
  26.         MsgBox "先选择员工", vbOKOnly + vbExclamation, "提示"
  27.         Exit Sub
  28.     End If
  30.     txtSQL = "select * from checkday_info"
  31.     Set checkDaymrc = ExecuteSQL(txtSQL, msgtext)
  32.     Call SQL_Timer
  33.     txtSQL = "select * from checkday_info where date='" & Trim(Left(Sqlser_time, 9)) & "'"
  34.     Set Yescheckmrc = ExecuteSQL(txtSQL, msgtext)
  38.     '看看checkday数据库是否有记录，有记录的话上次的余额等内容填写上次的，否则为零
  39.     If checkDaymrc.EOF = False Then '如果有记录
  40.         checkDaymrc.MoveLast '跳到最后一条记录
  41.         Remaincash = checkDaymrc.Fields(4)          'remaincash
  42.         '计算今日消费
  43.         txtSQL = "select * from line_info where ischeck='未结账'and status='正常下机'"
  44.         Set LineMrc = ExecuteSQL(txtSQL, msgtext)
  46.         If LineMrc.EOF = False Then
  47.             Do While LineMrc.EOF = False
  48.                 ConsumCash = Val(ConsumCash) + Val(LineMrc.Fields(11))
  49.                 LineMrc.Fields(13) = "已结账"
  50.                 LineMrc.MoveNext
  51.             Loop
  53.         End If
  54.         If Yescheckmrc.EOF = True Then  '如果今天没有结账
  55.             checkDaymrc.AddNew
  56.             checkDaymrc.Fields(0) = Val(Remaincash)                 'remaincash
  57.             checkDaymrc.Fields(1) = Val(labRechargeCash.Caption)    'rechargecash
  58.             checkDaymrc.Fields(2) = Val(ConsumCash)
  59.             checkDaymrc.Fields(3) = Val(labCancelCash.Caption)      'cancelcash
  60.             checkDaymrc.Fields(4) = Val(labAllCash.Caption)
  61.             Call SQL_Timer
  62.             checkDaymrc.Fields(5) = Trim(Left(Sqlser_time, 9))      'date
  63.             checkDaymrc.Update
  64.             checkDaymrc.Close
  65.         Else
  67.             Yescheckmrc.Fields(0) = Val(Remaincash)                 'remaincash
  68.             Yescheckmrc.Fields(1) = Val(labRechargeCash.Caption) + Val(Yescheckmrc.Fields(1))   'rechargecash
  69.             Yescheckmrc.Fields(2) = Val(ConsumCash) + Val(Yescheckmrc.Fields(2))
  70.             Yescheckmrc.Fields(3) = Val(labCancelCash.Caption) + Val(Yescheckmrc.Fields(3))     'cancelcash
  71.             Yescheckmrc.Fields(4) = Val(labAllCash.Caption) + Val(Yescheckmrc.Fields(4))
  72.             Call SQL_Timer
  73.             Yescheckmrc.Fields(5) = Trim(Left(Sqlser_time, 9))      'date
  74.             Yescheckmrc.Update
  75.             Yescheckmrc.Close
  76.         End If
  77.     Else '如果没有记录
  78.         txtSQL = "select * from line_info where ischeck='未结账'and status='正常下机'"
  79.         Set LineMrc = ExecuteSQL(txtSQL, msgtext)
  81.         If LineMrc.EOF = False Then
  82.             Do While LineMrc.EOF = False
  83.                 ConsumCash = Val(ConsumCash) + Val(LineMrc.Fields(11))
  84.                 LineMrc.Fields(13) = "已结账"
  85.                 LineMrc.MoveNext
  86.             Loop
  88.         End If
  90.         checkDaymrc.AddNew
  91.         checkDaymrc.Fields(0) = 0                               'remaincash
  92.         checkDaymrc.Fields(1) = Val(labRechargeCash.Caption)    'rechargecash
  93.         checkDaymrc.Fields(2) = Val(ConsumCash)
  94.         checkDaymrc.Fields(3) = Val(labCancelCash.Caption)      'cancelcash
  95.         checkDaymrc.Fields(4) = Val(labAllCash.Caption)
  96.         Call SQL_Timer
  97.         checkDaymrc.Fields(5) = Trim(Left(Sqlser_time, 9))      'date
  98.         checkDaymrc.Update
  99.         checkDaymrc.Close
  100.     End If
  102.     '对数据库进行结账标记
  103.     '对cancelcard标记
  104.     txtSQL = "select * from cancelcard_info where status='未结账'and userid='" & ComLevel.Text & "'"
  105.     Set CancelMrc = ExecuteSQL(txtSQL, msgtext)
  107.     Do While CancelMrc.EOF = False
  108.         CancelMrc.Fields(5) = "结账"
  109.         CancelMrc.MoveNext
  110.     Loop
  112.     '对recharge数据库标记
  113.     txtSQL = "select * from recharge_info where status='未结账'and userid='" & ComLevel.Text & "'"
  114.     Set REChargeMrc = ExecuteSQL(txtSQL, msgtext)
  116.     Do While REChargeMrc.EOF = False
  117.         REChargeMrc.Fields(8) = "结账"
  118.         REChargeMrc.MoveNext
  119.     Loop
  121.     labCardNo.Caption = 0
  122.     labCancelNo.Caption = 0
  123.     labRechargeCash.Caption = 0
  124.     lablinCash.Caption = 0
  125.     labCancelCash.Caption = 0
  126.     laballCardNo.Caption = 0
  127.     labAllCash.Caption = 0
  129. End Sub
  131. Private Sub comLevel_Click()     '当在点击下拉菜单中的选项，则显示真是姓名
  132.     txtSQL = "select * from user_info where userid='" & ComLevel.Text & "'"
  133.     Set mrc = ExecuteSQL(txtSQL, msgtext)
  135.     labName.Caption = Trim(mrc.Fields(3))   '显示姓名
  136.     lablevel.Caption = Trim(mrc.Fields(2))  '级别
  137.     mrc.Close
  139.     Call viewdate
  140. End Sub
  142. Private Sub ComLevel_KeyPress(KeyAscii As Integer)  '下拉菜单不可输入。
  144.             KeyAscii = 0
  145. End Sub
  147. Private Sub Form_Deactivate()       '失去焦点时，窗体卸载
  148.     Unload Me
  149. End Sub
  151. Private Sub Form_Load()
  152.     '窗体初始加载时，在下拉菜单中加载userid（级别）
  153.     txtSQL = "select * from user_Info"      '查询
  154.     Set mrc = ExecuteSQL(txtSQL, msgtext)
  156.     '在下来菜单中显示所有userid
  157.     Do While mrc.EOF = False
  158.         ComLevel.AddItem mrc.Fields(0)    '操作员用户名
  159.         mrc.MoveNext
  160.     Loop
  162.     mrc.Close
  164.     '窗体加载时，加载购卡卡片的字段名
  165.     With MfgBugCard
  166.         .Rows = 1
  167.         .CellAlignment = 4      '居中
  168.         .TextMatrix(0, 0) = "学号"  'studentid
  169.         .TextMatrix(0, 1) = "卡号"  'cardid
  170.         .TextMatrix(0, 2) = "日期"  'date
  171.         .TextMatrix(0, 3) = "时间"  'time
  172.         .TextMatrix(0, 4) = "金额"  'cash
  173.     End With
  176.     With MfgRecharge
  177.         .Rows = 1
  178.         .CellAlignment = 4      '居中
  179.         .TextMatrix(0, 0) = "学号"  'studentid
  180.         .TextMatrix(0, 1) = "卡号"  'cardid
  181.         .TextMatrix(0, 2) = "充值金额"  'cash
  182.         .TextMatrix(0, 3) = "日期"  'date
  183.         .TextMatrix(0, 4) = "时间"
  184.         .TextMatrix(0, 5) = "方式"  'source
  185.     End With
  187.     With MfgCancelCard
  188.         .Rows = 1
  189.         .CellAlignment = 4      '居中
  190.         .TextMatrix(0, 0) = "学号"  'studentid
  191.         .TextMatrix(0, 1) = "卡号"  'cardid
  192.         .TextMatrix(0, 2) = "日期"  'cash
  193.         .TextMatrix(0, 3) = "时间"  'date
  194.         .TextMatrix(0, 4) = "退卡金额"
  195.     End With
  197.     With MSHFlexGrid4
  198.          .Rows = 1
  199.         .CellAlignment = 4      '居中
  200.         .TextMatrix(0, 0) = "学号"  'studentid
  201.         .TextMatrix(0, 1) = "卡号"  'cardid
  202.         .TextMatrix(0, 2) = "日期"  'date
  203.         .TextMatrix(0, 3) = "时间"  'time
  204.     End With
  205. End Sub
  207. Private Sub viewdate()      '根据已经选择好人员信息来修改SSTab里面的汇总信息
  208.     Dim txtSQL As String
  209.     Dim msgtext As String
  210.     Dim mrcSD As ADODB.Recordset
  211.     Dim mrcRC As ADODB.Recordset
  212.     Dim mrcCC As ADODB.Recordset
  213.     Dim mrcTmp As ADODB.Recordset
  214.     Dim RechargeCash As Variant         '用于存储，充值的 所有金额
  215.     Dim cancelCash As Variant           '用于存储，退钱的 所有金额
  216.     Dim TmpCash As Variant              '临时用户金额
  218.     '把他的所有信息，未结账的显示出来
  220.      '购卡
  221.     txtSQL = "select * from recharge_Info where status='未结账' and UserID='" & ComLevel.Text & "'and source='注册'"
  222.     Set mrcSD = ExecuteSQL(txtSQL, msgtext)
  224.     MfgBugCard.Rows = mrcSD.RecordCount + 1
  226.     With MfgBugCard
  228.          .Rows = 1
  229.         .CellAlignment = 4      '居中
  230.         .TextMatrix(0, 0) = "学号"  'studentid
  231.         .TextMatrix(0, 1) = "卡号"  'cardid
  232.         .TextMatrix(0, 2) = "日期"  'date
  233.         .TextMatrix(0, 3) = "时间"  'time
  235.         While mrcSD.EOF = False
  236.             .Rows = .Rows + 1
  237.             .CellAlignment = 4
  238.             .TextMatrix(.Rows - 1, 0) = Trim(mrcSD.Fields(1)) 'studentno
  239.             .TextMatrix(.Rows - 1, 1) = Trim(mrcSD.Fields(0)) 'cardno
  240.             .TextMatrix(.Rows - 1, 2) = Trim(mrcSD.Fields(5)) 'date
  241.             .TextMatrix(.Rows - 1, 3) = Trim(mrcSD.Fields(6)) 'time
  242.             .TextMatrix(.Rows - 1, 4) = Trim(mrcSD.Fields(3)) 'cash
  243.             mrcSD.MoveNext
  244.         Wend
  245.     End With
  246.     AutoColWidth Me, MfgBugCard                    '自动调整表格大小
  248.     '把该操作员的所有未结账的充值信息汇总到表格，一个注册信息对应一个充值信息
  249.     '充值结账
  250.     txtSQL = "select * from ReCharge_Info where status='未结账' and UserID='" & ComLevel.Text & "'and source='充值'"
  251.     Set mrcRC = ExecuteSQL(txtSQL, msgtext)
  253.      RechargeCash = 0
  255.     With MfgRecharge
  256.          .Rows = 1
  257.         .CellAlignment = 4      '居中
  258.         .TextMatrix(0, 0) = "学号"  'studentid
  259.         .TextMatrix(0, 1) = "卡号"  'cardid
  260.         .TextMatrix(0, 2) = "充值金额"  'cash
  261.         .TextMatrix(0, 3) = "日期"  'date
  262.         .TextMatrix(0, 4) = "时间"
  263.         While Not mrcRC.EOF
  264.             .Rows = .Rows + 1
  265.             .TextMatrix(.Rows - 1, 0) = Trim(mrcRC.Fields(1)) 'studentno
  266.             .TextMatrix(.Rows - 1, 1) = Trim(mrcRC.Fields(2)) 'cardno
  267.             .TextMatrix(.Rows - 1, 2) = Trim(mrcRC.Fields(3)) 'rechargecash
  268.             .TextMatrix(.Rows - 1, 3) = Trim(mrcRC.Fields(4)) 'date
  269.             .TextMatrix(.Rows - 1, 4) = Trim(mrcRC.Fields(5)) 'time
  271.             mrcRC.MoveNext
  272.         Wend
  273.     End With
  274.     AutoColWidth Me, MfgRecharge                    '自动调整表格大小
  275.     '退卡
  276.     '把所有信息汇总到表格
  277.     txtSQL = "select * from CancelCard_Info where status='未结账' and UserID='" & ComLevel.Text & "'"
  278.     Set mrcCC = ExecuteSQL(txtSQL, msgtext)
  280.     cancelCash = 0
  283.     With MfgCancelCard
  284.         .Rows = 1
  285.         .CellAlignment = 4      '居中
  286.         .TextMatrix(0, 0) = "学号"  'studentid
  287.         .TextMatrix(0, 1) = "卡号"  'cardid
  288.         .TextMatrix(0, 2) = "日期"  'cash
  289.         .TextMatrix(0, 3) = "时间"  'date
  290.         .TextMatrix(0, 4) = "退卡金额"
  292.         While mrcCC.EOF = False
  293.             .Rows = .Rows + 1
  294.             .CellAlignment = 4
  295.             .TextMatrix(.Rows - 1, 0) = Trim(mrcCC.Fields(0))  'studentno
  296.             .TextMatrix(.Rows - 1, 1) = Trim(mrcCC.Fields(1))   'cardno
  297.             .TextMatrix(.Rows - 1, 2) = Trim(mrcCC.Fields(2))   'date
  298.             .TextMatrix(.Rows - 1, 3) = Trim(mrcCC.Fields(3))   'time
  299.             .TextMatrix(.Rows - 1, 4) = Trim(mrcCC.Fields(6))   'cancelcash
  300.             cancelCash = cancelCash + mrcCC.Fields(6)
  301.             mrcCC.MoveNext
  302.         Wend
  303.     End With
  304.     AutoColWidth Me, MfgCancelCard                    '自动调整表格大小
  306.     '临时用户
  307.     txtSQL = "select * from Recharge_Info where status='未结账' and UserID='" & ComLevel.Text & "'and source='注册'and type='临时用户'"
  308.     Set mrcTmp = ExecuteSQL(txtSQL, msgtext)
  310.     TmpCash = 0
  311.     With MSHFlexGrid4
  312.         .Rows = 1
  313.         .CellAlignment = 4      '居中
  314.         .TextMatrix(0, 0) = "学号"  'studentid
  315.         .TextMatrix(0, 1) = "卡号"  'cardid
  316.         .TextMatrix(0, 2) = "日期"  'date
  317.         .TextMatrix(0, 3) = "时间"  'time
  319.         While mrcTmp.EOF = False
  320.             .Rows = .Rows + 1
  321.             .CellAlignment = 4
  322.             .TextMatrix(.Rows - 1, 0) = Trim(mrcTmp.Fields(1))  'studentno
  323.             .TextMatrix(.Rows - 1, 1) = Trim(mrcTmp.Fields(0))   'cardno
  324.             .TextMatrix(.Rows - 1, 2) = Trim(mrcTmp.Fields(5))    'date
  325.             .TextMatrix(.Rows - 1, 3) = Trim(mrcTmp.Fields(6))   'time
  327.             TmpCash = cancelCash + mrcTmp.Fields(7)
  328.             mrcTmp.MoveNext
  329.         Wend
  330.     End With
  331.     AutoColWidth Me, MSHFlexGrid4                    '自动调整表格大小
  333.     txtSQL = "select * from Recharge_Info where status='未结账' and UserID='" & ComLevel.Text & "'"
  334.     Set mrcTmp = ExecuteSQL(txtSQL, msgtext)
  335.     Do While mrcTmp.EOF = False
  337.             RechargeCash = RechargeCash + mrcTmp.Fields(3)
  338.             mrcTmp.MoveNext
  339.     Loop
  342.     '然后，把操作员 的所有统计 信息  汇总 到 汇总列表
  343.     labCardNo.Caption = Trim(mrcSD.RecordCount)
  344.     labCancelNo.Caption = Trim(mrcCC.RecordCount)
  345.     labRechargeCash.Caption = RechargeCash
  346.     lablinCash.Caption = TmpCash
  347.     labCancelCash.Caption = cancelCash
  348.     laballCardNo.Caption = Val(labCardNo.Caption) - Val(labCancelNo.Caption)
  349.     labAllCash = Val(labRechargeCash.Caption) - Val(labCancelCash.Caption)
  351.     mrcSD.Close         '关闭释放空间
  352.     mrcRC.Close
  353.     mrcCC.Close
  354.     mrcTmp.Close
  355. End Sub
  360. Private Sub SSTab1_Click(PreviousTab As Integer)
  361.     If SSTab1.Caption = "退出" Then
  363.             Unload Me
  364.         Else
  365.             Call viewdate
  367.     End If
  368. End Sub

发表评论

表情：
评论列表 （有 0 条评论，156人围观）

还没有评论，来说两句吧...

相关阅读

    相关 【机房收费】结账

    前言       感觉机房中最难的一部分就是结账这一部分了，越敲越乱，敲的曲曲折折的，真正弄懂结账这一块是第一次验收之后。结账是整个机房收费系统中除了上下机最核心的地方了

    ゝ一世哀愁。ゝ一世哀愁。/ 0 赞/ 293 阅读

    相关 机房收费系统——结账

        机房收费系统的逻辑性很强。师傅也说了，第一次做机房，首要的是理清思路，明白它的逻辑。     做到结账这里，跟大家分享一下我对结账的一点理解： ![SouthEas

    小鱼儿小鱼儿/ 0 赞/ 347 阅读

    相关 机房收费系统之结账

    事实上，我觉得机房收费系统中结账的部分是耗我精力最多的。首先我就不明白结账是干嘛的，所以一上来就晕乎乎。后来看了一篇博客说结账方便老板管理的才明白了为什么是“操作员”。这里面要

    ╰半橙微兮°╰半橙微兮°/ 0 赞/ 286 阅读

    相关 机房收费系统之结账

           机房收费系统中一个重要的窗体之一就是结账窗体，因为涉及到数据库中的好多表所以刚开始会觉得很难，其实理清思路后按照自己的思路写完后便觉得没有想象中的那么难！好先来看

    偏执的太偏执、偏执的太偏执、/ 0 赞/ 322 阅读