【VB】机房收费系统（报表）

制作报表推荐：

手把手教你制作报表

 

我的理解：

日报表就是今天结账的记录，所以就是读取的CheckDay表中今天的数据，其实挺简单的，然后周结账我理解的并不是一周的账单，而是你选择看一个时间段的账单，不局限，所以选择完日期的区间以后，会把CheckDay表中的部分数据加在一起，然后添加到CheckWeek表中，显示在报表中。

 

代码展示：

 

Dim WithEvents Report As grproLibCtl.GridppReport
Private Sub cmdCheckDay_Click()
    Dim txtSQL As String
    Dim MsgText As String
    Dim RemainCash As Integer
    Dim RechargeCash As Integer
    Dim ConsumeCash As Integer
    Dim CancelCash As Integer
    Dim AllCash As Integer
    Dim mrc1 As ADODB.Recordset
    Dim mrcweek As ADODB.Recordset
    Dim mrc2 As ADODB.Recordset
    Dim mrc3 As ADODB.Recordset
    Dim mrc4 As ADODB.Recordset
    Dim mrc5 As ADODB.Recordset
    Dim mrc6 As ADODB.Recordset
    
    If StartDateView.Value > EndDateView.Value Then
        MsgBox "终止日期不能小于起始日期！", vbOKOnly + vbExclamation, "友情提示"
        Exit Sub
    End If
    
    txtSQL = "select * from checkDay_Info where date between '" & StartDateView.Value & " ' and '" & EndDateView.Value & "'"
    Set mrc1 = ExecuteSQL(txtSQL, MsgText)
    
    If mrc1.EOF Then
        MsgBox "该时间段没有记录！", vbOKOnly + vbInformation, "提示"
        Exit Sub
    End If
    '先删除之前checkweek表中记录
    txtSQL = "delete from checkweek_Info"
    Set mrcweek = ExecuteSQL(txtSQL, MsgText)
    
    '计算上次充值卡余额
    mrc1.MoveLast
    RemainCash = mrc1.Fields(0)
    
    '计算总充值金额
    txtSQL = "select sum(RechargeCash) from checkday_Info where date between '" & StartDateView.Value & " ' and '" & EndDateView.Value & "'"
    Set mrc2 = ExecuteSQL(txtSQL, MsgText)
    
    If IsNull(mrc2.Fields(0)) = True Then
        RechargeCash = 0
    Else
        RechargeCash = Trim(mrc2.Fields(0))
    End If
    
    '计算总消费金额
    txtSQL = "select sum(ConsumeCash) from checkday_Info where date between '" & StartDateView.Value & " ' and '" & EndDateView.Value & "'"
    Set mrc3 = ExecuteSQL(txtSQL, MsgText)
    
    If IsNull(mrc3.Fields(0)) = True Then
        ConsumeCash = 0
    Else
        ConsumeCash = Trim(mrc3.Fields(0))
    End If
    
    '计算总退卡金额
    txtSQL = "select sum(CancelCash) from checkday_Info where date between '" & StartDateView.Value & " ' and '" & EndDateView.Value & "'"
    Set mrc4 = ExecuteSQL(txtSQL, MsgText)
    
    If IsNull(mrc4.Fields(0)) = True Then
        CancelCash = 0
    Else
        CancelCash = Trim(mrc4.Fields(0))
    End If
    
    '计算本期充值卡余额
    mrc1.MoveLast
    AllCash = mrc1!AllCash
    
    '添加到checkweek表中
    txtSQL = "select * from Checkweek_Info  "
    Set mrcweek = ExecuteSQL(txtSQL, MsgText)
    mrcweek.AddNew
    mrcweek.Fields(0) = RemainCash
    mrcweek.Fields(1) = RechargeCash
    mrcweek.Fields(2) = ConsumeCash
    mrcweek.Fields(3) = CancelCash
    mrcweek.Fields(4) = AllCash
    mrcweek.Update
    
    '显示报表中的内容
    Report.DetailGrid.Recordset.QuerySQL = txtSQL
    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("time").Value = Time
End Sub

Private Sub cmdpreview_Click()
    Report.PrintPreview (True)
End Sub

Private Sub cmdput_Click()
    Report.[Print] (True)
End Sub

Private Sub Form_Load()
        txtSQL = "select * from checkWeek_Info "
         '创建报表对象
        Set Report = New grproLibCtl.GridppReport
        '载入报表模版文件
        Report.LoadFromFile (App.Path & "\checkweek.grf")
        '设置数据连接串
        Report.DetailGrid.Recordset.ConnectionString = "FileDSN=charge.dsn;uid=sa;pwd=123456"
        Report.DetailGrid.Recordset.QuerySQL = txtSQL '执行
        '显示报表中的内容
        grdWeekliyBill.Report = Report
        grdWeekliyBill.Start
    '报表模板上的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("time").Value = Time
End Sub