机房收费系统之周结账单

         在做系统的过程中，应该说报表这块儿，尤其是周结账和日结账这块是最繁琐的了，我用了3、4天吧，拿下了这块儿。

         首先看一下这个周结账单

        

           这个窗体的控件我也找了很长时间才找到，显示时间的是DTpicker，日历是monthview。

          首先实例化报表       

     Dim WithEvents report As grproLibCtl.GridppReport '实例化报表

         在报表中初始化日期、用户名

      

Private Sub report_initialize() '初始化数据
    report.ParameterByName("username").AsString = username
    report.ParameterByName("datestart").AsString = Format(startDate.Value, "yyyy-mm-dd")
    report.ParameterByName("dateend").AsString = Format(endDate.Value, "yyyy-mm-dd")

End Sub

 

          下面看一下我的周结账单是如何计算的

Private Sub cmdUpdate_Click()
    Dim strMsg As String
    Dim strSqlRe As String
    Dim mrcRe As ADODB.Recordset
    Dim strSqlCan As String
    Dim mrcCan As ADODB.Recordset
    Dim strSqlWeek As String
    Dim mrcWeek  As ADODB.Recordset
    Dim strSQLL As String
    Dim mrcL As ADODB.Recordset
    Dim Toconsume As Long
    Dim consume As Long
    Dim dateIndex As Date
    Dim datestart As Date
    Dim dateend As Date
    Dim Toremain As Integer
    Dim Torecharge As Integer
    Dim Tocancel As Integer
    Dim remain As Integer
    Dim charge As Integer
    Dim cancel As Integer
    Dim all As Long
    'GRDisplayViewer1.Refresh
    
    datestart = Format(startDate.Value, "yyyy-mm-dd")
    dateend = Format(endDate.Value, "yyyy-mm-dd")
    
                 '删除周表中的数据
    strSqlWeek = "delete from checkweek_info "
    Set mrcWeek = executeSQL(strSqlWeek, strMsg)

    For dateIndex = datestart To dateend
        remain = 0
        Recharge = 0
        consume = 0
        cancel = 0
        all = 0
        '上期充值卡余额计算
        If dateIndex = datestart Then
            remain = 0
            Call Form_Load
        Else
        
            '先算总的recharge
            strSqlRe = "select sum(addmoney) as rechargecash from recharge_info where date<='" & Format(dateIndex - 1, "yyyy-mm-dd") & "'"
            Set mrcRe = executeSQL(strSqlRe, strMsg)
            If Not IsNull(mrcRe!rechargecash) Then
                Recharge = mrcRe!rechargecash
                mrcRe.Close
            End If
        
            '总的cancelcard
            strSqlCan = "select sum(cancelcash) as cancel from cancelcard_info where date<='" & Format(dateIndex - 1, "yyyy-mm-dd") & "'"
            Set mrcCan = executeSQL(strSqlCan, strMsg)
            If Not IsNull(mrcCan!cancel) Then
               cancel = mrcCan!cancel
               mrcCan.Close
            End If
            '总的consume
            strSQLL = "select sum(consume) as consumecash from line_info where offdate<= '" & Format(dateIndex - 1, "yyyy-mm-dd") & "'"
            Set mrcL = executeSQL(strSQLL, strMsg)
            If Not IsNull(mrcL!consumeCash) Then
                consume = mrcL!consumeCash
                mrcL.Close
            End If
            '上期充值卡余额
            remain = Recharge - consume - cancel
            '本期充值金额
            strSqlRe = "select sum(addmoney) as rechargecash from recharge_info where date='" & Format(dateIndex, "yyyy-mm-dd") & "'"
            Set mrcRe = executeSQL(strSqlRe, strMsg)
            If Not IsNull(mrcRe!rechargecash) Then
                Torecharge = mrcRe!rechargecash
                mrcRe.Close
            End If
            '本期退卡
            strSqlCan = "select sum(cancelcash) as cancel from cancelcard_info where date='" & Format(dateIndex, "yyyy-mm-dd") & "'"
            Set mrcCan = executeSQL(strSqlCan, strMsg)
            If Not IsNull(mrcCan!cancel) Then
                Tocancel = mrcCan!cancel
                mrcCan.Close
            End If
            '本期消费
            strSQLL = "select sum(consume) as consumecash from line_info where offdate= '" & Format(dateIndex, "yyyy-mm-dd") & "'"
            Set mrcL = executeSQL(strSQLL, strMsg)
            If Not IsNull(mrcL!consumeCash) Then
                Toconsume = mrcL!consumeCash
                mrcL.Close
            End If
            '本期充值卡余额
            all = remain + Torecharge - Toconsume - Tocancel
            strSqlWeek = "select * from checkweek_info "
            Set mrcWeek = executeSQL(strSqlWeek, strMsg)
            mrcWeek.AddNew
            mrcWeek.Fields("remaincash") = remain
            mrcWeek.Fields("rechargecash") = Torecharge
            mrcWeek.Fields("consumecash") = Toconsume
            mrcWeek.Fields("cancelcash") = Tocancel
            mrcWeek.Fields("allcash") = all
            mrcWeek.Fields("date") = Format(dateIndex, "yyyy-mm-dd")
            mrcWeek.Update
            mrcWeek.Close
        End If
    Next
    Call Form_Load
    End Sub

Private Sub Form_Load()
    Dim strSQL As String
    Dim strMsg As String
    Dim mrc As ADODB.Recordset
    Dim strSqlRe As String
    Dim mrcRe As ADODB.Recordset
    Dim strSqlCan As String
    Dim mrcCan As ADODB.Recordset
    Dim strSqlWeek As String
    Dim mrcWeek  As ADODB.Recordset
    Dim strSQLL As String
    Dim mrcL As ADODB.Recordset
    Dim Toconsume As Long
    Dim consume As Long
    Dim remain As Integer
    Dim Recharge As Integer
    Dim cancel As Integer
    Dim all As Integer
    Dim Toremain As Integer
    Dim Torecharge As Integer
    Dim Tocancel As Integer
    
    MonthViewStart.Visible = False
    MonthViewEnd.Visible = False

    strSQL = "select * from checkweek_info where date between '" & Format(startDate.Value, "yyyy-mm-dd") & "' and '" & Format(endDate.Value, "yyyy-mm-dd") & "'"
    Set mrc = executeSQL(strSQL, strMsg)
    strSQL = strSQL & "order by date"
    Set report = New grproLibCtl.GridppReport
    GRDisplayViewer1.Stop
    report.LoadFromFile (App.Path & "\周结账单.grf")
    

    report.DetailGrid.Recordset.ConnectionString = connectstring()
    report.DetailGrid.Recordset.QuerySQL = strSQL
    GRDisplayViewer1.report = report
    GRDisplayViewer1.Start
    
    strSqlRe = "select sum(addmoney) as rechargecash from recharge_info where date<='" & Format(startDate.Value - 1, "yyyy-mm-dd") & "'"
    Set mrcRe = executeSQL(strSqlRe, strMsg)
    If Not IsNull(mrcRe!rechargecash) Then
        Recharge = mrcRe!rechargecash
        mrcRe.Close
    End If

    '总的cancelcard
    strSqlCan = "select sum(cancelcash) as cancel from cancelcard_info where date<='" & Format(startDate.Value - 1, "yyyy-mm-dd") & "'"
    Set mrcCan = executeSQL(strSqlCan, strMsg)
    If Not IsNull(mrcCan!cancel) Then
       cancel = mrcCan!cancel
       mrcCan.Close
    End If
    '总的consume
    strSQLL = "select sum(consume) as consumecash from line_info where offdate<= '" & Format(startDate.Value - 1, "yyyy-mm-dd") & "'"
    Set mrcL = executeSQL(strSQLL, strMsg)
    If Not IsNull(mrcL!consumeCash) Then
        consume = mrcL!consumeCash
        mrcL.Close
    End If
    '上期充值卡余额
    remain = Recharge - consume - cancel
    '本期充值金额
    strSqlRe = "select sum(addmoney) as rechargecash from recharge_info where date='" & Format(startDate.Value, "yyyy-mm-dd") & "'"
    Set mrcRe = executeSQL(strSqlRe, strMsg)
    If Not IsNull(mrcRe!rechargecash) Then
        Torecharge = mrcRe!rechargecash
        mrcRe.Close
    End If
    '本期退卡
    strSqlCan = "select sum(cancelcash) as cancel from cancelcard_info where date='" & Format(startDate.Value, "yyyy-mm-dd") & "'"
    Set mrcCan = executeSQL(strSqlCan, strMsg)
    If Not IsNull(mrcCan!cancel) Then
        Tocancel = mrcCan!cancel
        mrcCan.Close
    End If
    '本期消费
    strSQLL = "select sum(consume) as consumecash from line_info where offdate= '" & Format(startDate.Value, "yyyy-mm-dd") & "'"
    Set mrcL = executeSQL(strSQLL, strMsg)
    If Not IsNull(mrcL!consumeCash) Then
        Toconsume = mrcL!consumeCash
        mrcL.Close
    End If
    '本期充值卡余额
    all = remain + Torecharge - Toconsume - Tocancel
    strSqlWeek = "select * from checkweek_info "
    Set mrcWeek = executeSQL(strSqlWeek, strMsg)
    mrcWeek.AddNew
    mrcWeek.Fields("remaincash") = remain
    mrcWeek.Fields("rechargecash") = Torecharge
    mrcWeek.Fields("consumecash") = Toconsume
    mrcWeek.Fields("cancelcash") = Tocancel
    mrcWeek.Fields("allcash") = all
    mrcWeek.Fields("date") = Format(startDate.Value, "yyyy-mm-dd")
    mrcWeek.Update


End Sub

         我的方法很麻烦，这是我使劲想出来的，大家有好的方法可以给我一个连接哦，让我借鉴一下。