机房收费系统之周结账单

冷不防 2022-09-17 07:26 117阅读 0赞

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

首先看一下这个周结账单

![Center][]

这个窗体的控件我也找了很长时间才找到，显示时间的是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

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

[Center]: /images/20220828/800caf9b88d647a49f72eb7940794c6b.png