机房收费系统——日、周结账单

最后一个难点就是报表——日、周结账单，这两部分几乎是相同的，只要把日结账单做出来了，周结账单也是so easy的，关键看你怎么做了，接下来看看我做的，仅供参考：

日结：

Option Explicit

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

Private Sub cmdPrint_Click()
    report.[Print] (True)  '打印,因为报表对象的print方法名与vb内部定义有冲突所以要用中括号
End Sub

Private Sub Command1_Click()
    GRDisplayViewer1.Refresh
    
End Sub

Private Sub Command3_Click()
    report.PrintPreview (True)
End Sub

Private Sub Form_Load()
    Dim noda As String
    noda = Now
    
    Dim grprolibct1 As String
    
    txtsql = "select * from checkday_info where date= '" & Format(Date, "yyyy-mm-dd") & "'"
    Set report = New grproLibCtl.GridppReport   '实例化模板
    
    
    report.LoadFromFile (App.Path & "\daycheck.grf") '加载模板
    
    report.DetailGrid.Recordset.ConnectionString = connectstring()  '连接数据源
    
    report.DetailGrid.Recordset.QuerySQL = txtsql '通过select查询创建记录集，只查询

    report.ParameterByName("Parameter3").AsString = noda '显示当前的日期

   report.ParameterByName("Parameter2").AsString = UserName '显示编号
    
    GRDisplayViewer1.report = report '将数据赋给grd的report
    
    GRDisplayViewer1.Start '在报表中显示

End Sub

周结：

Private Sub Command3_Click()

Dim lastrecharge As Integer '计算上期充值金额
Dim mrcconsume As ADODB.Recordset
Dim consume As Integer '计算本期消费金额
Dim mrccancel As ADODB.Recordset
Dim cancelcash As Integer '计算本期退卡金额
Dim allcash As Integer '计算本期充值卡余额
Dim mrcall As ADODB.Recordset
Dim mrcrecharge As ADODB.Recordset '计算本期充值卡金额
Dim Recharge As Integer
Dim mrc As ADODB.Recordset

Dim mrcweek As ADODB.Recordset


    If DTPicker1.Value > DTPicker2.Value Then
        MsgBox "终止时间不能小于起始时间", 0 + 46, "警告"
    Exit Sub
    
    End If
    
    '判断上期余额的文本框是否为空
    If Text1.Text = "" Then
        txtsql = "select * from checkday_info where date between '" & Format$(DTPicker1.Value, "yyyy-mm-dd") & "'" & "and '" & Format$(DTPicker2.Value, "yyyy-mm-dd") & "'"
    Else
        txtsql = "select * from checkday_info where date between '" & Format$(DTPicker1.Value, "yyyy-mm-dd") & "'" & "and '" & Format$(DTPicker2.Value, "yyyy-mm-dd") & "'and remaincash= '" & Trim(Text1.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)
    
    '计算上期充值卡余额（充值卡的钱-退卡的钱）
    txtsql = "select  sum(remaincash) from checkday_info where date <'" & Format$(DTPicker1.Value, "yyyy-mm-dd") & "'"
    
    Set mrcrecharge = ExecuteSQL(txtsql, msgtext)
    
    If IsNull(mrcrecharge.fields(0)) = True Then
        lastrecharge = "0"
    Else
        lastrecharge = Trim(mrcrecharge.fields(0))
    End If
    
    '计算本期消费金额
     txtsql = "select sum(consumecash) from checkday_info where date between'" & Format$(DTPicker1.Value, "yyyy-mm-dd") & "'" & "and'" & Format$(DTPicker2.Value, "yyyy-mm-dd") & "'"
    Set mrcconsume = ExecuteSQL(txtsql, msgtext)
    
    If IsNull(mrcconsume.fields(0)) = True Then
        consume = "0"
    Else
        consume = Trim(mrcconsume.fields(0))
    End If
    
    '计算本期退卡金额
    txtsql = "select sum(cancelcash) from checkday_info where date between'" & Format$(DTPicker1.Value, "yyyy-mm-dd") & "'" & "and'" & Format$(DTPicker2.Value, "yyyy-mm-dd") & "'"
    Set mrccancel = ExecuteSQL(txtsql, msgtext)
    
    If IsNull(mrccancel.fields(0)) = True Then
        cancelcash = "0"
    Else
        cancelcash = Trim(mrccancel.fields(0))
    End If
    
    '计算本期充值卡余额
    txtsql = "select sum(allcash) from checkday_info where  date between'" & Format$(DTPicker1.Value, "yyyy-mm-dd") & "'" & "and'" & Format$(DTPicker2.Value, "yyyy-mm-dd") & "'"
    Set mrcall = ExecuteSQL(txtsql, msgtext)
    If IsNull(mrcall.fields(0)) = True Then
        allcash = "0"
    Else
        allcash = Trim(mrcall.fields(0))
    End If
    
    '计算本期充值金额
    txtsql = "select sum(rechargecash) from checkday_info where date between'" & Format$(DTPicker1.Value, "yyyy-mm-dd") & "'" & "and'" & Format$(DTPicker2.Value, "yyyy-mm-dd") & "'"
    Set mrcrecharge = ExecuteSQL(txtsql, msgtext)
    
    If IsNull(mrcrecharge.fields(0)) = True Then
        Recharge = "0"
    Else
        Recharge = Trim(mrcrecharge.fields(0))
    End If
    
    '将数据更新到checkweek表中
    txtsql = "select * from checkweek_info"
    Set mrc = ExecuteSQL(txtsql, msgtext)
    
    mrc.AddNew
    mrc.fields(0) = Val(lastrecharge)
    mrc.fields(1) = Val(Recharge)
    mrc.fields(2) = Val(consume)
    mrc.fields(3) = Val(cancelcash)
    mrc.fields(4) = Val(allcash)
    mrc.fields(5) = Date
    mrc.Update
    
    
    
    GRDisplayViewer1.Refresh
    
    report.DetailGrid.Recordset.QuerySQL = "select * from checkweek_info where date between '" & Format$(DTPicker1.Value, "yyyy-mm-dd") & "'" & "and'" & Format$(DTPicker2.Value, "yyyy-mm-dd") & "'"

    '显示管理员编号
    report.ParameterByName("Parameter3").Value = UserName
    
        '获取开始和结束时间
    report.ParameterByName("Parameter1").Value = Format$(DTPicker1.Value, "yyyy-mm-dd")
    report.ParameterByName("Parameter2").Value = Format$(DTPicker2.Value, "yyyy-mm-dd")

    
    
    
End Sub

Private Sub Form_Load()
    '连接数据库
    txtsql = "select * from checkday_info where date= '" & Format(Date, "yyyy-mm-dd") & "'"
    Set report = New grproLibCtl.GridppReport  '实例化模板
    
    report.LoadFromFile (App.Path & "\weekcheck.grf") '加载模板
    report.DetailGrid.Recordset.ConnectionString = connectstring()  '连接数据源
    report.DetailGrid.Recordset.QuerySQL = txtsql   '通过select查询创建记录集
    
    report.ParameterByName("Parameter3").AsString = UserName
    report.ParameterByName("Parameter3").Value = UserName '显示管理员编号
    
    GRDisplayViewer1.report = report '将数据赋给GRD的report
    GRDisplayViewer1.Start  '在报表中显示

End Sub