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

       日结和周结其实逻辑是一样的，不过就是日结只结了今天的账，而周结结的是选中日期之间的账。

 一、日结账单

       我日结账单采取的方法是：先判断今天的账结了吗，如果已经结了，就从日结账单表中删除记录，再结一次，如果没有结，就向日结账单表中写入记录。

<span style="font-size:24px;">'在窗体加载时，写日结账单表
'在写日结账单表之前，先判断今天是否已经写过日结账单表了
'如果写过日结账单表了，那么，就删除今天写过的那条，重写今天的日结账记录
'如果，没有，就不重写记录
  
'日结账单中的计算
    '昨日充值卡中余额=读取昨天的日结账单的金额总和
    '充值金额=今天充值表中所有的钱
    '消费金额=今天学生上机表中，所有的消费金额相加
    '退还金额=今天教师退卡表里面，已经（结过账）的退卡总金额
    '金额总和=昨日卡中金额+充值金额-消费金额-退还金额
    '日期时间是今天的日期时间
Dim WithEvents Report As grproLibCtl.GridppReport  '实例化报表</SPAN>


Private Sub cmbCheckDay_Click()
       Dim txtSQL As String
       Dim mrc As ADODB.Recordset
       Dim Msgtext As String
       Report.DetailGrid.Recordset.ConnectionString = ConnectString '数据源连接数据库直接调用写好的模块内容
       txtSQL = "select * from checkday_Info where date= '" & Date & "'"
       Set mrc = ExecuteSQL(txtSQL, Msgtext)
        '显示报表中的内容
       Report.DetailGrid.Recordset.QuerySQL = txtSQL
       grddayliyBill.Refresh
       '显示报表中的日期和时间
       Report.ParameterByName("date1").Value = Date
       Report.ParameterByName("time1").Value = Time
End Sub

Private Sub Form_Activate()
For Each a In Forms
   If a.Name <> frmdaycheck.Name And a.Name <> frmMain.Name And a.Name <> Me.Name And a.Name <> frmzhu.Name Then
       a.WindowState = 1
       frmdaycheck.ZOrder 0
       End If
Next
End Sub
'预览
Private Sub cmdpreview_Click()
    Report.PrintPreview (True)
End Sub
  
'打印
Private Sub cmdput_Click()
    Report.[Print] (True)
End Sub
'载入窗体
Private Sub Form_Load()
   Dim txtSQL As String
'''   Dim txtSQL1 As String
   Dim mrc As ADODB.Recordset
'''   Dim mrc1 As ADODB.Recordset
   Dim Msgtext As String
   Dim Remaincash As Variant '存储昨天的结账总和
   Dim YesDayRMB As Currency  '用于存储上期余额
   Dim RechargeRMB As Currency '用于存储充值金额
   Dim CostRMB As Currency '用于存储消费金额
   Dim CancelRMB As Currency '用于存储退卡金额
   Dim AllRmb As Currency '用于存储金额总和


'  判断昨天是不是已经结账了
''   txtSQL = "select * from CheckDay_Info where date ='" & Trim(Date) & "'"
''   Set mrc = ExecuteSQL(txtSQL, Msgtext)
''   ' 如果今天已经结账了，就不是第一次结账了
'''   If Not (mrc.BOF And mrc.EOF) Then
''    If mrc.EOF = False Then
      '删除今天结过得账单
      txtSQL = "delete  from CheckDay_Info" 'where date ='" & Date & "'" '删除今天结过的账单
      Set mrc = ExecuteSQL(txtSQL, Msgtext)
      '然后根据昨天的结账记录重新结账
      '此处因为今天已经结过账了，也可以不删去今天的结账记录，在今天的记录上重新计算，然后用update更新一下就行
'    End If


   '计算上期余额
   '上期余额=上一次日结账单表中的本期余额
   txtSQL = "select sum(allcash) from checkday_Info where date ='" & Date - 1 & "'"
   Set mrc = ExecuteSQL(txtSQL, Msgtext)
   If IsNull(mrc.Fields(0)) = True Then
            YesDayRMB = 0
        Else
            YesDayRMB = Trim(mrc.Fields(0))
        End If
    '计算充值金额（充值金额是充值表里面，教师今天充值的金额，并且是已经结账了的，如果今天充值了某些金额，但是没有结账，则在计算日结账单时，不计入）
      txtSQL = "select sum(addmoney) from ReCharge_Info where date='" & Date & "' and status='结账'"
'      txtSQL = "select sum(addmoney) from Recharge_Info where date = '" & Date & "' and status = '结账'"
      Set mrc = ExecuteSQL(txtSQL, Msgtext)

      If IsNull(mrc.Fields(0)) = True Then
          RechargeRMB = 0
      Else
          RechargeRMB = Trim(mrc.Fields(0))
      End If


      '计算消费金额
      txtSQL = "select sum(consume) from line_Info where ondate='" & Date & "'"
      Set mrc = ExecuteSQL(txtSQL, Msgtext)

      If IsNull(mrc.Fields(0)) = True Then
         CostRMB = 0
      Else
         CostRMB = Trim(mrc.Fields(0))
      End If


      '计算退还金额
      txtSQL = "select sum(cancelcash) from cancelcard_Info where date='" & Date & "'"
      Set mrc = ExecuteSQL(txtSQL, Msgtext)

      If IsNull(mrc.Fields(0)) = True Then
         CancelRMB = 0
      Else
         CancelRMB = Trim(mrc.Fields(0))
      End If


      '计算金额总和=昨日卡中金额+充值金额-退卡金额
      AllRmb = YesDayRMB + RechargeRMB - CancelRMB

      '插入日结账单表中
'    txtSQL = "inset into CheckDay_Info values('" & YesDayRMB & "','" & RechargeRMB & "','" & CostRMB & "','" & CancelRMB & "','" & AllRmb & "','" & Date & "','" & Now & "')"
    txtSQL = "select * from CheckDay_info "
    Set mrc = ExecuteSQL(txtSQL, Msgtext)
    mrc.AddNew
    mrc.Fields(0) = Trim(YesDayRMB)
    mrc.Fields(1) = Trim(RechargeRMB)
    mrc.Fields(2) = Trim(CostRMB)
    mrc.Fields(3) = Trim(CancelRMB)
    mrc.Fields(4) = Trim(AllRmb)
    mrc.Fields(5) = Trim(Date)
    mrc.Update
    '    创建报表对象
    Set Report = New grproLibCtl.GridppReport
'    载入报表模版文件
    Report.LoadFromFile (App.Path & "\日报表.grf")
'    设置数据连接串
    Report.DetailGrid.Recordset.ConnectionString = ConnectString

    '查询语句
    txtSQL = "select * from CheckDay_Info where date= '" & Date & "'"
    Report.DetailGrid.Recordset.QuerySQL = txtSQL '执行
'    显示报表中的内容
    grddayliyBill.Report = Report
    grddayliyBill.Start
  
    '报表模板上的4个日期或时间赋值
    Report.ParameterByName("date1").Value = Date
    Report.ParameterByName("time1").Value = Time
    
      
      
End Sub
</span>

二、周结账单

      周结账单只是一个临时的表，将两个日期之间的结账内容拿出来。

<span style="font-size:24px;">‘周结账单只是一个临时的表
   
 '上期充值卡余额=在begintime之前的充值卡的余额
 '本期充值金额=从日结表中，得到的这段时间的充值金额
 '本期消费金额=从日结表中，得出的这段时间的消费金额
 '本期退款金额=从日结表中，得出的这时间的退款金额
 '本期充值卡余额=上期充值卡余额+本期充值金额-本期消费金额-本期退款金额
 '日期=今天



Dim WithEvents Report As grproLibCtl.GridppReport  '实例化报表</SPAN>

Private Sub cmbCheckDay_Click()
    Dim txtSQL As String
    Dim mrc As ADODB.Recordset
    Dim Msgtext As String
    Dim BalanceRMB As Currency '用于存储上期充值卡余额
    Dim Benqirechargermb As Currency '用存储本期充值金额
    Dim Benqicostrmb As Currency '用于存储本期消费金额
    Dim Benqireturnrmb As Currency '用于存储本期退款金额
    Dim Benqicardrmb As Currency '用于存储本期充值卡余额
    Dim mrcWeek As ADODB.Recordset
    '判定终止日期不能小于起始日期
    If startDateView.Value > EndDateView.Value Then '条件限制
        MsgBox "终止日期不能小于起始日期！", , "温馨提示"
'        Exit Sub
End If
If txtBalance.Text = "" Then '判断上期text里是否为空
        '查询日期1到日期2的记录
        txtSQL = "select * from checkweek_Info where date between '" & _
                 startDateView.Value & "' and '" & EndDateView.Value & "'"
    Else
        '如果上期余额的text里不为空则查询日期1到日期2中、上期余额是文本中的记录
        txtSQL = "select * from checkweek_Info where date between '" & _
                 startDateView.Value & "' and '" _
                 & EndDateView.Value & "' And remaincash = '" & txtBalance.Text & "'"
        Set mrc = ExecuteSQL(txtSQL, Msgtext)
        If mrc.EOF And mrc.BOF Then
           MsgBox "没有记录！"
        End If
    
    
    End If
'每次都先删除周结账单里的数据，然后重新写入
   txtSQL = "delete from checkWeek_Info"
   Set mrcWeek = ExecuteSQL(txtSQL, Msgtext)
  '上期充值卡余额=在begintime之前的充值卡的余额
   '本期充值金额=从日结表中，得到这段时间的充值金额
   '本期消费金额=从日结表中，得到这段时间的消费金额
   '本期退款金额=从日结表中，得到这段时间的退款金额
   '本期充值卡金额=上期充值卡金额+本期充值金额-本期消费金额-本期退款金额
   '日期=今天
   
    '计算上期充值卡余额
    txtSQL = "select sum(allcash) from CheckDay_Info where date < '" & CDate(startDateView.Value) & "'"
    Set mrc = ExecuteSQL(txtSQL, Msgtext)
    Debug.Print txtSQL

    If IsNull(mrc.Fields(0)) = True Then
       BalanceRMB = 0
    Else
       BalanceRMB = Trim(mrc.Fields(0))
    End If
    '显示在文本框中
    txtBalance.Text = Format(BalanceRMB, "fixed")


    '计算本期充值金额
    txtSQL = "select sum(rechargecash) from CheckDay_Info where date between '" & Format(CDate(startDateView.Value)) & "' and '" & Format(CDate(EndDateView.Value)) & "'"
    Set mrc = ExecuteSQL(txtSQL, Msgtext)
    Debug.Print txtSQL

    If IsNull(mrc.Fields(0)) = True Then
       Benqirechargermb = 0
    Else
       Benqirechargermb = Trim(mrc.Fields(0))
    End If

    '计算本期消费金额
    txtSQL = "select sum(consumecash) from CheckDay_Info where date between '" & Format(CDate(startDateView.Value)) & "'and '" & Format(CDate(EndDateView.Value)) & "'"
    Set mrc = ExecuteSQL(txtSQL, Msgtext)

    Debug.Print txtSQL

    If IsNull(mrc.Fields(0)) = True Then
       Benqicostrmb = 0
    Else
       Benqicostrmb = Trim(mrc.Fields(0))
    End If

    '计算本期退款金额
    txtSQL = "select sum(cancelcash) from CheckDay_Info where date between '" & Format(CDate(startDateView.Value)) & "'and '" & Format(CDate(EndDateView.Value)) & "'"
    Set mrc = ExecuteSQL(txtSQL, Msgtext)

    Debug.Print txtSQL

    If IsNull(mrc.Fields(0)) = True Then
       Benqireturnrmb = 0
    Else
       benqiturnrmb = Trim(mrc.Fields(0))
    End If


    '计算本期充值卡余额
    'BenQiCardRmb = BalanceRmb + BenQiRechargeRmb - BenQiCostRmb - ReturnRmb（在所有数据完整时没问题，但是数据如果不完整，就不行了，所以采取别的办法）
    txtSQL = "select max(allcash) from CheckDay_Info where date <= '" & Format(CDate(EndDateView.Value)) & "'"
    Set mrc = ExecuteSQL(txtSQL, Msgtext)

    If IsNull(mrc.Fields(0)) = True Then
       Benqicardrmb = 0
    Else
        Benqicardrmb = Trim(mrc.Fields(0))
    End If

    '插到周结账表里面
    txtSQL = "select * from Checkweek_Info  "
    Set mrc = ExecuteSQL(txtSQL, Msgtext)
    mrc.AddNew
    mrc.Fields(0) = Trim(BalanceRMB)
    mrc.Fields(1) = Trim(Benqirechargermb)
    mrc.Fields(2) = Trim(Benqicostrmb)
    mrc.Fields(3) = Trim(Benqireturnrmb)
    mrc.Fields(4) = Trim(Benqicardrmb)
    mrc.Fields(5) = Trim(Date)
    mrc.Update
'查询语句
    txtSQL = "select * from checkWeek_Info where date between '" & Format(CDate(startDateView.Value)) & "' and '" & Format(CDate(EndDateView.Value)) & "'"
'    Set mrc = ExecuteSQL(txtSQL, Msgtext)
'''    Report.DetailGrid.Recordset.QuerySQL = txtSQL '执行
'''    '显示报表中的内容
'''    grdWeekliyBill.Report = Report
'''    grdWeekliyBill.Start
'创建报表对象
    Set Report = New grproLibCtl.GridppReport
    '载入报表模版文件
    Report.LoadFromFile (App.Path & "\周报表.grf")
    '设置数据连接串
    Report.DetailGrid.Recordset.ConnectionString = ConnectString()
Report.DetailGrid.Recordset.QuerySQL = txtSQL '执行
    '显示报表中的内容
    grdWeekliyBill.Report = Report
    grdWeekliyBill.Refresh
'报表模板上的4个日期或时间赋值
    Report.ParameterByName("date1").Value = Format$(startDateView.Value, "yyyy-mm-dd")
    Report.ParameterByName("date2").Value = Format$(EndDateView.Value, "yyyy-mm-dd")
    Report.ParameterByName("date3").Value = Date
    Report.ParameterByName("time3").Value = Time

End Sub

Private Sub Form_Activate()
For Each a In Forms
   If a.Name <> frmweekcheck.Name And a.Name <> frmMain.Name And a.Name <> Me.Name And a.Name <> frmzhu.Name Then
       a.WindowState = 1
       frmweekcheck.ZOrder 0
       End If
Next
End Sub
</span>

      周结和日结完事了，我们的机房也就差不多了……