VB.NET版機房收費系統之報表

報表，即報告情況的表格，簡單的說：報表就是用表格、圖表等格式來動態顯示數據，可以用公式表示為：“報表 = 多樣的格式 + 動態的數據”。 在沒有計算機以前，人們利用紙和筆來記錄數據。

比如：民間常常說的豆腐帳，就是賣豆腐的每天將自己的賣出的豆腐記在一個本子上，然後每月都要匯總算算，這種情況下，報表數據和報表格式是緊密結合在一起的，都在同一個本子上。數據也只能有一種幾乎只有記帳的人才能理解的表現形式，且這種形式難於修改。

VB版機房收費系統的報表采用的是第三方的Grid++Report，.NET版機房收費系統，采用了自帶的報表設計器，實現過程如下：

第一部分：添加數據集

a、右擊添加---新建項目；

b、選擇窗體應用程序and重命名；

c、界面布局如下，其中ReportViewer自帶具有刷新、打印、打印布局等功能，所以就不用再獨自拿出來當一個小功能實現了。ps，這裡添加一個DataGridView控件，設置為不可見，她有什麼神奇的作用nie，`(*∩_∩*)′ ，我們可以把當天收取金額，退還金額，消費金額顯示在這個控件上，比方拿消費金額來說，她的計算結果就是該控件所有的行，加上line表中第六列即consumeMoney相加，是不是很方便。

d、右擊添加---新建項目；

e、Reoprting---報表；

第二部分：連接數據庫

a、選擇數據源

b、選擇數據庫類型

c、選擇數據庫模型

d、選擇數據庫連接；

e、將連接字符串保存到應用程序配置文件中；

f、添加連接

g、選擇數據庫對象

第三部分，設計報表；

a、添加表頭

b、選擇報表

第四部分：代碼實現部分

首先，實體層；

<span style="font-size:18px;">Public Class CheckDayinfo
    Public rechargeCash As Integer                     '字段
    Public Property _rechargeCash As Integer            '屬性  
        Get
            Return rechargeCash
        End Get
        Set(value As Integer)
            rechargeCash = value
        End Set
    End Property
    Public consumeCash As Integer
    Public Property _consumeCash As Integer
        Get
            Return consumeCash
        End Get
        Set(value As Integer)
            rechargeCash = value
        End Set
    End Property
    Public cancelCash As Integer
    Public Property _cancelCash As Integer
        Get
            Return cancelCash
 
        End Get
        Set(value As Integer)
            cancelCash = value
        End Set
    End Property
 
    Public allCash As Integer
    Public Property _allCash As Integer
        Get
            Return allCash
        End Get
        Set(value As Integer)
            allCash = value
        End Set
    End Property
    Public Ddate As String
 
    Public Property _date As String
        Get
            Return Ddate
        End Get
        Set(value As String)
            Ddate = value
        End Set
    End Property
End Class</span>

D層

<span style="font-size:18px;">Imports System.Data.SqlClient
Public Class DayBillDAO
    Public Function queryRechargeCash(ByVal date1 As String) As DataTable  '在recharge這張表中查詢收取金額
        Dim db As New Entity.Dbutil  '實例化一個新的數據庫連接
        Dim dt As New DataTable      '實例化D層DataTable這個類的一個對象
 
        Using conn As New SqlConnection(db.connstring)
            conn.Open()
 
            Dim sql As String
            Dim cmd As New SqlCommand
            Dim dataAdapter As New SqlDataAdapter
            Dim dst As New DataSet
 
            sql = "select * from Recharge_info where date=@date"   '從rechargeinfo這張表中查找充值金額，根據日期進行相關選擇
            cmd = New SqlCommand(sql, conn)
            cmd.Parameters.Add(New SqlParameter("@date", date1))
            dataAdapter.SelectCommand = cmd
            dataAdapter.Fill(dst, "Recharge_info")
            dt = dst.Tables("Recharge_info")
            Return dt
        End Using
    End Function
 
 
    Public Function queryCancelCash(ByVal date2 As String) As DataTable  '在卡表中查詢退還金額也就是余額
        Dim db As New Entity.Dbutil
        Dim dt As New DataTable
 
        Using conn As New SqlConnection(db.connstring)
            conn.Open()
 
            Dim sql As String
            Dim cmd As New SqlCommand
            Dim dataAdapter As New SqlDataAdapter
            Dim dst As New DataSet
 
            sql = "select * from card_info where returnDate=@date "     '從卡表中選擇退還金額，根據日期進行相關判斷
            cmd = New SqlCommand(sql, conn)
            cmd.Parameters.Add(New SqlParameter("@date", date2))
            dataAdapter.SelectCommand = cmd
            dataAdapter.Fill(dst, "card_info")
            dt = dst.Tables("card_info")
            Return dt
        End Using
    End Function
 
    Public Function queryConsumeCash(ByVal date3 As String) As DataTable  '從line表中查詢消費金額
        Dim db As New Entity.Dbutil
        Dim dt As New DataTable
 
        Using conn As New SqlConnection(db.connstring)
            conn.Open()
 
            Dim sql As String
            Dim cmd As New SqlCommand
            Dim dataAdapter As New SqlDataAdapter
            Dim dst As New DataSet
 
            sql = "select * from line_info where offdate=@date " '從line表中查找消費金額
            cmd = New SqlCommand(sql, conn)
            cmd.Parameters.Add(New SqlParameter("@date", date3))
            dataAdapter.SelectCommand = cmd
            dataAdapter.Fill(dst, "line_info")
            dt = dst.Tables("line_info")
            Return dt
        End Using
    End Function
 
    Public Sub insertDayBill(ByVal checkDayinfo As Entity.CheckDayinfo) '如果，我說的是如果啊，如果日結賬單裡面沒有記錄，我們需要在日結賬單裡面插入一條新的記錄，如果有，我們更新即可
        Dim db As New Entity.Dbutil
 
 
        Using conn As New SqlConnection(db.connstring)
            conn.Open()
 
            Dim sql As String
            Dim cmd As New SqlCommand
 
            sql = "insert into CheckDay_info values (@rechargeCash,@consumeCash,@cancelCash,@allCash,@date)"
            cmd = New SqlCommand(sql, conn)
            cmd.Parameters.Add(New SqlParameter("@rechargeCash", checkDayinfo.rechargeCash))
            cmd.Parameters.Add(New SqlParameter("@consumeCash", checkDayinfo.consumeCash))
            cmd.Parameters.Add(New SqlParameter("@cancelCash", checkDayinfo.cancelCash))
            cmd.Parameters.Add(New SqlParameter("@allCash", checkDayinfo.allCash))
            cmd.Parameters.Add(New SqlParameter("@date", checkDayinfo.Ddate))
 
            cmd.ExecuteNonQuery()
 
        End Using
    End Sub
 
    Public Sub updateDayBill(ByVal checkDayinfo As Entity.CheckDayinfo) '更新日結賬單裡面的內容
        Dim db As New Entity.Dbutil
        Dim dt As New DataTable
 
 
        Using conn As New SqlConnection(db.connstring)
            conn.Open()
 
            Dim sql As String
            Dim cmd As New SqlCommand
            
 
            sql = "update CheckDay_info set rechargeCash=@rechargeCash,consumeCash=@consumeCash,cancelCash=@cancelCash,allCash=@allCash where date=@date "
            cmd = New SqlCommand(sql, conn)
            cmd.Parameters.Add(New SqlParameter("@rechargeCash", checkDayinfo.rechargeCash))
            cmd.Parameters.Add(New SqlParameter("@consumeCash", checkDayinfo.consumeCash))
            cmd.Parameters.Add(New SqlParameter("@cancelCash", checkDayinfo.cancelCash))
            cmd.Parameters.Add(New SqlParameter("@allCash", checkDayinfo.allCash))
            cmd.Parameters.Add(New SqlParameter("@date", checkDayinfo.Ddate))
 
            cmd.ExecuteNonQuery()
        End Using
    End Sub
 
    Public Function queryCheckDay(ByVal date1 As String) As Entity.CheckDayinfo  '查詢日結賬單中的相關信息，決定我們在U層的時候到底是插入一條記錄呢，還是更新一條記錄
        Dim db As New Entity.Dbutil
        Dim CheckDayinfo As New Entity.CheckDayinfo
 
        Using conn As New SqlConnection(db.connstring)
            conn.Open()
 
            Dim sql As String
            Dim cmd As SqlCommand
            Dim reader As SqlDataReader
 
            sql = "select * from CheckDay_info  where date=@date"
            cmd = New SqlCommand(sql, conn)
            cmd.Parameters.Add(New SqlParameter("date", date1))
            reader = cmd.ExecuteReader
 
            If (reader.Read()) Then
                CheckDayinfo.rechargeCash = reader.GetDecimal(reader.GetOrdinal("rechargeCash"))
                CheckDayinfo.consumeCash = reader.GetDecimal(reader.GetOrdinal("consumeCash"))
                CheckDayinfo.cancelCash = reader.GetDecimal(reader.GetOrdinal("cancelCash"))
                CheckDayinfo.allCash = reader.GetDecimal(reader.GetOrdinal("allCash"))
                CheckDayinfo.Ddate = reader.GetString(reader.GetOrdinal("date"))
            Else
                CheckDayinfo = Nothing
            End If
        End Using
        Return CheckDayinfo
    End Function
End Class
</span>

B層

<span style="font-size:18px;">Public Class DayBillManager
    Public Function queryRechargeCash(ByVal date1 As String) As DataTable '在recharge這張表中查詢收取金額的相關信息
        Dim DayBillDAO As New DAL.DayBillDAO
        Return DayBillDAO.queryRechargeCash(date1)
    End Function
 
    Public Function queryCancelCash(ByVal date2 As String) As DataTable  '從卡表中查詢退還金額也就是余額
        Dim DayBillDAO As New DAL.DayBillDAO
        Return DayBillDAO.queryCancelCash(date2)
    End Function
 
    Public Function queryConsumeCash(ByVal date3 As String) As DataTable '從line表查詢消費金額
        Dim DayBillDAO As New DAL.DayBillDAO
        Return DayBillDAO.queryConsumeCash(date3)
    End Function
 
    Public Sub inserDayBill(ByVal checkDayinfo As Entity.CheckDayinfo) '如果日結賬單裡面沒有信息，我們需要插入一條
        Dim DayBillDAO As New DAL.DayBillDAO
        DayBillDAO.insertDayBill(checkDayinfo)
    End Sub
 
    Public Sub updateDayBill(ByVal checkDayinfo As Entity.CheckDayinfo)  '如果日結賬單裡面有信息，我們只需要更新即可
        Dim DayBillDAO As New DAL.DayBillDAO
        DayBillDAO.updateDayBill(checkDayinfo)
    End Sub
 
    Public Function queryCheckDay(ByVal date1 As String) As Entity.CheckDayinfo  '查找日結賬單中的相關信息，如果沒有信息，我們需要插入一條信息，如果有，我們需要更新一條信息
        Dim DayBillDAO As New DAL.DayBillDAO
        Return DayBillDAO.queryCheckDay(date1)
    End Function
End Class
</span>

U層

<span style="font-size:18px;">Public Class frmDayBill
 
    Private Sub frmDayBill_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Call write()
 
        'TODO:  這行代碼將數據加載到表“DataSet1.CheckDay_info”中。您可以根據需要移動或刪除它。
        Me.CheckDay_infoTableAdapter.Fill(Me.DataSet1.CheckDay_info, CStr(Format(dtpDate.Value, "yyyy-MM-dd")))
        Me.ReportViewer1.RefreshReport()
    End Sub
 
    Public Sub write()     '在這裡，添加了一個DataGridView把相關信息顯示在DataGridView中，在報表中顯示的時候，我們只需要用一個循環進行相加即可
        Dim daybill As New BLL.DayBillManager
        Dim dt1 As New DataTable
 
 
        dt1 = daybill.queryRechargeCash(Format(dtpDate.Value, "yyyy-MM-dd"))
        dgv.AutoGenerateColumns = True
        dgv.DataSource = dt1
 
        Dim i As Integer     '定義變量
        Dim rechargeCash As Decimal
 
        For i = 0 To dgv.RowCount - 1        '充值的金額等於DataGirdView所有的行中的列相加，這裡的列即recharge_info中的第二列(rechargeAmount)（當天的哦）
            rechargeCash = rechargeCash + dgv.Rows(i).Cells(2).Value
        Next i
 
        Dim cancelCash As Decimal
        dt1 = daybill.queryCancelCash(Format(dtpDate.Value, "yyyy-MM-dd"))
        dgv.DataSource = dt1
 
        For i = 0 To dgv.RowCount - 1   '退還的金額等於DataGirdView所有的行中的列相加，這裡的列即card_info中的第二列balance，也就是退還金額
            cancelCash = cancelCash + dgv.Rows(i).Cells(2).Value
        Next i
 
        Dim consumeCash As Decimal
        dt1 = daybill.queryConsumeCash(Format(dtpDate.Value, "yyyy-MM-dd"))
        dgv.DataSource = dt1
 
        For i = 0 To dgv.RowCount - 1     '消費的金額等於DataGridView中所有的行中的列相加，這裡的列即line_info中的第六列consumeMoney，也就是消費金額
            consumeCash = consumeCash + dgv.Rows(i).Cells(6).Value
        Next i
        Dim allCash As Decimal
        allCash = rechargeCash - cancelCash    '總金額，等於充值金額減去退還金額
 
        Dim enCheck As New Entity.CheckDayinfo      '封裝實體
        Dim enCheck1 As New Entity.CheckDayinfo
        enCheck1.rechargeCash = rechargeCash
        enCheck1.cancelCash = cancelCash
        enCheck1.consumeCash = consumeCash
        enCheck1.allCash = allCash
        enCheck1.Ddate = CStr(Format(dtpDate.Value, "yyyy-MM-dd"))
 
        enCheck = daybill.queryCheckDay(Format(dtpDate.Value, "yyyy-MM-dd "))  '如果enCheck中沒有記錄，我們就插入一條，否則更新
        If (enCheck Is Nothing) Then
            daybill.inserDayBill(enCheck1)
        Else
            daybill.updateDayBill(enCheck1)
        End If
    End Sub
 
    Private Sub dtpDate_ValueChanged(sender As Object, e As EventArgs) Handles dtpDate.ValueChanged    '調用窗體加載事件
        Call frmDayBill_Load(sender, e)
    End Sub
 
End Class</span>

最後運行結果；

計算機出現之後，我們利用計算機處理數據和界面設計的功能來生成、展示報表。計算機上的報表的主要特點是數據動態化，格式多樣化，並且實現報表數據和報表格式的完全分離，用戶可以只修改數據，或者只修改格式。報表分類EXCEL、WORD等編輯軟件：它們可以做出很復雜的報表格式，但是由於它們沒有定義專門的報表結構來動態的加載報表數據，所有這類軟件中的數據都是已經定義好的，靜態的，不能動態變化的。它們沒有辦法實現報表軟件的“數據動態化”特性。自此，日結的功能告一段落，機房收費系統未完，待續......

作者：csdn博客 丁國華