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

【前言】

机房收费系统快要完成了，结账部分是这机房收费系统中相对需要好好理清思路的一部分内容，在结账过程中有每个人都会有不同的见解去计算各种金额，理清楚几个金额之间的关系就非常好做了。

【计算分析】

售卡张数：student_info表中该用户未结账的记录条数

退卡张数：cancelcard_info表中该用户未结账的记录条数

充值金额：recharge_info表中该用户未结账的金额总数

临时收费金额：student_info表中该用户对临时用户的收费金额

退卡金额：cancelcard_info表中该用户当天未结账的金额总数

总售卡数=售卡数-退卡数

应收金额：充值金额+购卡金额-退卡金额

【代码】

Dim CheckDaySQL As String
    Dim mrcCheckDay As ADODB.Recordset
    Dim LineSQL As String
    Dim mrcLine As ADODB.Recordset

    Dim RemainCash As String            '上期余额

    ConsumeCash = 
    If ComboUserID.Text = "" Then
       MsgBox "请选择用户！",  + , "系统提示"
       Exit Sub
    End If
    '更新表
    CheckDaySQL = "select * from checkday_info where date='" & Format(Date) & "'"
    Set mrcCheckDay = ExecuteSQL(CheckDaySQL, MsgText)

    If mrcCheckDay.EOF = True Then

       RemainCash = mrcCheckDay.Fields()

       LineSQL = "select * from line_info where status='正常下机'"
       Set mrcLine = ExecuteSQL(LineSQL, MsgText)

       If mrcLine.EOF = False Then
          Do While mrcLine.EOF = False
             ConsumeCash = Val(ConsumeCash) + Val(mrcLine.Fields())
             mrcLine.MoveNext
          Loop

          studentSQL = "select * from student_info where userid='" & Trim(ComboUserID.Text) & "'and ischeck='" & "未结账" & "'and date='" & Format(Date, "yyyy-mm-dd") & "'"
          Set mrcstudent = ExecuteSQL(studentSQL, MsgText)

          Do While Not mrcstudent.EOF
             mrcstudent.Fields() = "结账"
             mrcstudent.Update
             mrcstudent.MoveNext
          Loop
          mrcstudent.Close

          ReChargeSQL = "select * from recharge_info where userid='" & Trim(ComboUserID.Text) & "'and status='" & "未结账" & "'and date='" & Format(Date, "yyyy-mm-dd") & "' "
          Set mrcReCharge = ExecuteSQL(ReChargeSQL, MsgText)

          Do While Not mrcReCharge.EOF
             mrcReCharge.Fields() = "结账"
             mrcReCharge.Update
             mrcReCharge.MoveNext
          Loop
          mrcReCharge.Close

          CancelCardSQL = "select * from cancelcard_info where userid='" & Trim(ComboUserID.Text) & "'and status='" & "未结账" & "'and date='" & Format(Date, "yyyy-mm-dd") & "'"
          Set mrcCancelCard = ExecuteSQL(CancelCardSQL, MsgText)

          Do While Not mrcCancelCard.EOF
             mrcCancelCard.Fields() = "结账"
             mrcCancelCard.Update
             mrcCancelCard.MoveNext
         Loop
         mrcCancelCard.Close
       End If
       '更新日结表
       mrcCheckDay.AddNew
       mrcCheckDay.Fields() = Trim(RemainCash)
       mrcCheckDay.Fields() = Val(txtRecharge.Text)
       mrcCheckDay.Fields() = Val(txtMoney.Text)
       mrcCheckDay.Fields() = Val(txtBackCash.Text)
       mrcCheckDay.Fields() = Val(txtTemRecharge.Text) + Val(txtRecharge.Text) - Val(txtBackCash.Text)
       mrcCheckDay.Fields() = Date
       mrcCheckDay.Update
       mrcCheckDay.Close
       Else
           RemainCash = mrcCheckDay.Fields()

       LineSQL = "select * from line_info where status='正常下机'"
       Set mrcLine = ExecuteSQL(LineSQL, MsgText)

       If mrcLine.EOF = False Then
          Do While mrcLine.EOF = False
             ConsumeCash = Val(ConsumeCash) + Val(mrcLine.Fields())
             mrcLine.MoveNext
          Loop

          studentSQL = "select * from student_info where userid='" & Trim(ComboUserID.Text) & "'and ischeck='" & "未结账" & "'and date='" & Format(Date, "yyyy-mm-dd") & "'"
          Set mrcstudent = ExecuteSQL(studentSQL, MsgText)

          Do While Not mrcstudent.EOF
             mrcstudent.Fields() = "结账"
             mrcstudent.Update
             mrcstudent.MoveNext
          Loop
          mrcstudent.Close

          ReChargeSQL = "select * from recharge_info where userid='" & Trim(ComboUserID.Text) & "'and status='" & "未结账" & "'and date='" & Format(Date, "yyyy-mm-dd") & "' "
          Set mrcReCharge = ExecuteSQL(ReChargeSQL, MsgText)

          Do While Not mrcReCharge.EOF
             mrcReCharge.Fields() = "结账"
             mrcReCharge.Update
             mrcReCharge.MoveNext
          Loop
          mrcReCharge.Close

          CancelCardSQL = "select * from cancelcard_info where userid='" & Trim(ComboUserID.Text) & "'and status='" & "未结账" & "'and date='" & Format(Date, "yyyy-mm-dd") & "'"
          Set mrcCancelCard = ExecuteSQL(CancelCardSQL, MsgText)

          Do While Not mrcCancelCard.EOF
             mrcCancelCard.Fields() = "结账"
             mrcCancelCard.Update
             mrcCancelCard.MoveNext
         Loop
         mrcCancelCard.Close
         End If
         '更新表

         mrcCheckDay.Fields() = Trim(RemainCash) + mrcCheckDay.Fields()
         mrcCheckDay.Fields() = Val(txtRecharge.Text) + mrcCheckDay.Fields()
         mrcCheckDay.Fields() = Val(txtMoney.Text) + mrcCheckDay.Fields()
         mrcCheckDay.Fields() = Val(txtBackCash.Text) + mrcCheckDay.Fields()
         mrcCheckDay.Fields() = Val(txtTemRecharge.Text) + Val(txtRecharge.Text) - Val(txtBackCash.Text) + mrcCheckDay.Fields()

         mrcCheckDay.Update
         mrcCheckDay.Close
       End If