日账单

【窗体设置】

我的日账单窗体设置是，在加载的时候插入今天的账单，在窗体中显示所有日账单表里的数据。然后点击日结账单刷新只显示今天的。

【Grid++Report报表设计器与VB的交互】

在窗体加载事件中，加载报表

    Set Report = New gregn6LibCtl.GridppReport                      \'实例化模版
    Report.LoadFromFile (App.Path & \"\\checkday.grf\")               \'加载模版
    Report.DetailGrid.Recordset.ConnectionString = ConnectString() \'连接数据源
    Report.DetailGrid.Recordset.QuerySQL = \"select  * from checkday_Info \"    \'通过SELECT查询创建记录集
    Report.ParameterByName(\"Operator\").Value = UserName \'向gridreport内传参数
    Report.ParameterByName(\"Administrator\").Value = UserName
    GRDisplayViewer1.Report = Report
    GRDisplayViewer1.Start                                         \'开始打印

日账单的刷新

Private Sub cmdRefresh_Click()
    Report.DetailGrid.Recordset.QuerySQL = \"select  * from checkday_Info  where date=\'\" & Format(Now(), \"yyyy-MM-dd\") & \"\'\" \'通过SELECT查询创建记录集
    GRDisplayViewer1.Refresh \'刷新
End Sub

打印预览

Report.PrintPreview (True)           \'打印预览

打印

Report.[Print] True                 \'打印

【计算今日账单】

① 计算过程

上期余额：等于前一天（如果前一天没有记录再往前查）的本期余额

当日充值余额：从recharge_info表里查出来的当前充值的总钱数

当日消费余额：从Line_info表里查的消费余额

当日退还金额：从cancelcard_info表里查出来的退卡金额

本期金额：上期余额+当日充值金额-当日消费金额-当日退还金额

② 代码

    Dim Remaincash  As Single           \'定义上期余额
    Dim RechargeCash As Single          \'定义充值金额
    Dim ConsumeCash As Single           \'定义消费金额
    Dim CancelCash As Single            \'定义退还金额
    Dim AllCash As Integer              \'定义本期余额
    
    Dim TadayDate                       \'定义日期
    Dim n As Integer
    
    \'计算上期余额
    TadayDate = Format(Now() - 1, \"yyyy-mm-dd\")            \'让日期等于前一天
    DtxtSQL = \"select * from checkday_info where date =\'\" & TadayDate & \"\'\"        \'查询前一天的记录
    Set Dmrc = ExecuteSQL(DtxtSQL, Dmsgtext)
    n = 1
    Do While Dmrc.RecordCount = 0                          \'当前一天没有记录的时候，利用循环语句，一直往前一天查
        n = n + 1
        TadayDate = Format(Now() - n, \"yyyy-mm-dd\")
        DtxtSQL = \"select * from checkday_info where date =\'\" & TadayDate & \"\'\"     
        Set Dmrc = ExecuteSQL(DtxtSQL, Dmsgtext)
    Loop
    If IsNull(Dmrc.Fields(4)) = True Then
        Remaincash = 0
    Else
        Remaincash = Trim(Dmrc.Fields(4)) & \"\"
    End If
    
    \'计算当日充值金额
    RtxtSQL = \"select sum(addmoney) from Recharge_info where date =\'\" & Format(Now(), \"yyyy-mm-dd\") & \"\'\"
    Set Rmrc = ExecuteSQL(RtxtSQL, Rmsgtext)
    If IsNull(Rmrc.Fields(0)) = True Then
        RechargeCash = 0
    Else
        RechargeCash = Trim(Rmrc.Fields(0))
    End If
    Rmrc.Close
    
    \'计算消费金额
    LtxtSQL = \"select sum(consume) from line_info where offdate=\'\" & Format(Now(), \"yyyy-mm-dd\") & \"\'\"
    Set Lmrc = ExecuteSQL(LtxtSQL, Lmsgtext)
    If IsNull(Lmrc.Fields(0)) = True Then
        ConsumeCash = 0
    Else
        ConsumeCash = Trim(Lmrc.Fields(0))
    End If
    Lmrc.Close
    
    \'计算退还金额
    CtxtSQL = \"select sum(cancelcash) from cancelcard_info where date=\'\" & Format(Now(), \"yyyy-mm-dd\") & \"\'\"
    Set Cmrc = ExecuteSQL(CtxtSQL, Cmsgtext)
    If IsNull(Cmrc.Fields(0)) = True Then
        CancelCash = 0
    Else
        CancelCash = Trim(Cmrc.Fields(0))
    End If
    Cmrc.Close
    
    \'计算本期金额
    AllCash = Remaincash + RechargeCash - ConsumeCash - CancelCash

【今天账单插入】

在插入今日账单之前，应该先查询是否今天已经结算，如果结算，就把之前的删除，在插入。然后在显示报表

插入数据

 \'在表checkday_info表中插入数据
    DtxtSQL = \"insert into checkday_info values(\'\" & Remaincash & \"\',\'\" & RechargeCash & \"\',\'\" & ConsumeCash & \"\',\'\" & CancelCash & \"\',\'\" & AllCash & \"\',\'\" & Format(Now(), \"yyyy-mm-dd\") & \"\')\"
    Call ExecuteSQL(DtxtSQL, Dmsgtext)