机房收费系统——结账

前言：

机房收费系统的结账花了我四天的时间，终于将其整明白了，从中有非常多的心得体会，下来分享给大家！

1、流程图

流程图是特别关键的一步，它代表了你整体的思路！（期中checkweek与checkday整体都差不多，所以整篇文章以日结账单为说明）

2、汇总思路

售卡张数=购卡选项卡的记录总数
退卡张数=退卡选项卡的记录总数
总售卡数=售卡张数-退卡张数
退卡金额=要结账的操作员在CancelCard_Info表中的金额累加
充值金额=要结账的操作员在ReCharge_Info表中金额累加
临时收费金额=临时用户在此网吧的花费（）
应收金额=充值金额—退卡金额（这个要好好的解释下，我是把在注册卡的时候的充值的钱同时在ReCharge_Info表中更新，这样只要是往自己的卡里充钱，不管是注册，还是充钱，最后的结果都是充钱，这样做的目的是因为，在自己的studnet_info表中的学生的余额是一直随着上机时间的变化而变化的；临时用户注册卡充值的金额也是这样，但是临时用户退的卡，也是在退卡金额中，所以，此算法和临时收费金额，没有任何的关系！）

3、代码的思想

这里我想分享一个n+1的实践！大家都学了sql sever了吧，可是在需要同时调用多个表的时候，有多少人是利用好了以前学过的知识select语句的知识呢，下来小编给大家展示一下！

在我的临时收费金额里，需要调用两个表，一个是表student_info，一个是表recharge_info 如下列代码！

txtsql = "select sum(addmoney)as sum from student_info ,ReCharge_Info where student_info.cardno=recharge_info.cardno and student_info.cardno = '" & Trim(comUserId.Text) & "'  and ischeck = '未结账' and type = '临时用户'"Set mrc3 = ExecuteSQL(txtsql, MsgText)   If IsNull(mrc3.Fields(0)) Then       '无记录        txtTemReceiveMoney.Text = "0"   Else        txtTemReceiveMoney.Text = mrc3.Fields(0)   End If

其中的 txtTemReceiveMoney.Text = mrc3.Fields(0)为什么是fields（0），后边会有解释！
在这里大家可能看着一脸懵逼，因为这是在vb环境中，翻译的SQL语句，vb用& 和“”将SQL语句写到了vb的环境中，vb只认识双引号，而SQL只认识单引号！大家来看看SQL环境中的语句吧！

select sum(addmoney)as sum from student_info ,ReCharge_Info where student_info.userid=recharge_info.userid and student_info.userid = '13' and ischeck = '未结账' and type = '临时用户'

下面是SQL中的查询结果！，根据查询的结果就可以写查询的代码了，是不是太简单了！ 现在就回明白txtTemReceiveMoney.Text = mrc3.Fields(0)为什么是fields（0）了吧！

看到上边的代码是不是很熟悉，sum为求和函数，在SQL中被称作为聚合函数，还有count（计算个数）、avg（求平均值）、max（最大值）、min（最小值），此时完全就可以不用再vb中添加其他的复杂的循环体了！

4、比较难的代码展示

（1）更新表中数据的部分

Option ExplicitDim txtsql As StringDim txtsql1 As StringDim MsgText As StringDim mrc As ADODB.Recordset    '代表学生表（student_info）Dim mrc1 As ADODB.Recordset   '代表充值表（recharge_info）Dim mrc2 As ADODB.Recordset   '代表退卡表（cancelcard_info）Dim mrc3 As ADODB.Recordset   '代表日结账单（checkday_info）Dim mrc4 As ADODB.Recordset   '代表日结账单中有条件的查询，这里没有也可以，主要是为了区分Dim mrc5 As ADODB.Recordset   '代表line表Dim mrc6 As ADODB.Recordset   '代表日结账单表中有条件的查询（也是为了区分）Private Sub CmdCheck_Click()Dim remaincash As StringDim rechargecash As StringDim consumecash As StringDim cancelcash As StringDim allcash As String    If comUserId.Text = "" Then        MsgBox "请选择操作员后再结账！", vbOKOnly + vbExclamation, "提示"        Exit Sub    End If'更新学生表    txtsql = "select * from student_info where UserID = '" & Trim(comUserId.Text) & "'and ischeck = '" & "未结账" & "'"    Set mrc = ExecuteSQL(txtsql, MsgText)    Do While Not mrc.EOF        mrc!ischeck = "结账"        mrc.Update        mrc.MoveNext    Loop    mrc.Close'更新充值表    txtsql = "select * from ReCharge_Info where UserID='" & Trim(comUserId.Text) & "' and status = '" & "未结账" & "'"        Set mrc1 = ExecuteSQL(txtsql, MsgText)         Do While Not mrc1.EOF        mrc1!Status = "结账"        mrc1.Update        mrc1.MoveNext    Loop    mrc1.Close'更新退卡表    txtsql = "select * from cancelcard_Info where UserID='" & Trim(comUserId.Text) & "' and status = '" & "未结账" & "'"        Set mrc2 = ExecuteSQL(txtsql, MsgText)         Do While Not mrc2.EOF        mrc2!Status = "结账"        mrc2.Update        mrc2.MoveNext    Loop    mrc2.Close'更新日结账单表    '计算上期充值卡余额（remaincash）        txtsql = "select allcash from checkday_Info where date ='" & Date - 1 & "'"   '当前日期减少一天        Set mrc4 = ExecuteSQL(txtsql, MsgText)    If IsNull(mrc4.Fields(0)) Then       remaincash = "0"    Else       remaincash = mrc4.Fields(0)    End If    '更新当天充值金额，充值金额是今天的值班的任意个操作员充值的金额，并且是已经结了账的，如果今天充值了某些金额，但是没有结账，则在计算日结账单时，不计入）    txtsql = "select sum(addmoney) from recharge_info where status = '结账' and date  = '" & Date & "'" '代表今天    Set mrc1 = ExecuteSQL(txtsql, MsgText)    If IsNull(mrc1.Fields(0)) = True Then        rechargecash = "0"    Else        rechargecash = mrc1.Fields(0)    End If    '从line表计算当日消费金额    txtsql = "select sum(consume) from Line_Info where offdate='" & Date & "'"    Set mrc5 = ExecuteSQL(txtsql, MsgText)    If IsNull(mrc5.Fields(0)) Then       consumecash = "0"    Else       consumecash = mrc5.Fields(0)    End If   '更新计算当天的退卡金额（cancelcash）这样没结账的操作员就不会算到这里面了   txtsql = "select sum(cancelcash) from cancelcard_Info where date='" & Date & "'and status = '结账'"   Set mrc2 = ExecuteSQL(txtsql, MsgText)   If IsNull(mrc2.Fields(0)) Then       cancelcash = "0"   Else       cancelcash = mrc2.Fields(0)   End If    '往表里更新    txtsql = "select * from CheckDay_info "    Set mrc3 = ExecuteSQL(txtsql, MsgText)    '判断是不是当天已经结过帐了    txtsql = "select * from checkday_info where date='" & Date & "'"    Set mrc6 = ExecuteSQL(txtsql, MsgText)    If mrc6.EOF = False Then        mrc6!remaincash = Val(remaincash)        mrc6!rechargecash = Val(rechargecash)        mrc6!consumecash = Val(consumecash)        mrc6!cancelcash = Val(cancelcash)        mrc6!allcash = Val(remaincash) + Val(rechargecash) - Val(cancelcash) - Val(consumecash)        mrc6!Date = Date        mrc6.Update        mrc6.Close     Else        mrc6.AddNew        mrc6!remaincash = Val(remaincash)        mrc6!rechargecash = Val(rechargecash)        mrc6!consumecash = Val(consumecash)        mrc6!cancelcash = Val(cancelcash)        mrc6!allcash = Val(remaincash) + Val(rechargecash) - Val(cancelcash)        mrc6!Date = Date        mrc6.Update        mrc6.Close      End If'更新周结账单(只需要删除周结账单表里的东西，然后将日结账单里所有的内容都跟新进去)    txtsql = "delete checkweek_info"    Set mrc = ExecuteSQL(txtsql, MsgText)    txtsql1 = "insert into checkweek_info select * from checkday_info"    Set mrc1 = ExecuteSQL(txtsql1, MsgText)End Sub

（2）汇总部分

（这部分一些代码的技巧已经在上边讲过了，现在不给予解释）

'计算售卡张数        txtsql = "select * from student_info where UserID = '" & Trim(comUserId.Text) & "'and ischeck = '" & "未结账" & "'"        Set mrc = ExecuteSQL(txtsql, MsgText)        txtSellCardCount.Text = mrc.RecordCount   '计算退卡张数        txtsql = "select * from cancelcard_info where userid='" & Trim(comUserId.Text) & "' and status = '" & "未结账" & "'"        Set mrc2 = ExecuteSQL(txtsql, MsgText)        txtQuitCardcount.Text = mrc2.RecordCount   '计算充值金额(不区分固定还是临时用户)        txtsql = "select sum(addmoney) from ReCharge_Info where UserID='" & Trim(comUserId.Text) & "' and status = '" & "未结账" & "'"        Set mrc1 = ExecuteSQL(txtsql, MsgText)        If IsNull(mrc1.Fields(0)) Then       '无记录            txtRechargeMoney.Text = "0"        Else            txtRechargeMoney.Text = mrc1.Fields(0)        End If   '计算退卡金额        txtsql = "select sum(CancelCash) from CancelCard_Info where userID = '" & Trim(comUserId.Text) & "'and status = '" & "未结账" & "'"        Set mrc2 = ExecuteSQL(txtsql, MsgText)        If IsNull(mrc2.Fields(0)) Then       '无记录            txtQuitCardMoney.Text = "0"        Else            txtQuitCardMoney.Text = mrc2.Fields(0)        End If   '计算临时收费金额        txtsql = "select sum(addmoney)as sum from student_info ,ReCharge_Info where student_info.cardno=recharge_info.cardno and student_info.cardno = '" & Trim(comUserId.Text) & "'  and ischeck = '未结账' and type = '临时用户'"        Set mrc3 = ExecuteSQL(txtsql, MsgText)        If IsNull(mrc3.Fields(0)) Then       '无记录            txtTemReceiveMoney.Text = "0"        Else            txtTemReceiveMoney.Text = mrc3.Fields(0)        End If    '计算应收金额        txtShouldGainMoney.Text = Val(txtRechargeMoney.Text) - Val(txtQuitCardMoney.Text)

心得：

在结账的时候一直抱怨给的系统有太多的bug，心里充满了负面的情绪，可是既然感觉到了有bug，那么我们就应该设计出没有bug的结账系统，知道做到，这才是我们应该做的。抱怨，没办法的说辞，全都是在逃避，遇到困难，解决它！