VB.NET版机房收费系统---报表

报表，即报告情况的表格，简单的说：报表就是用表格、图表等格式来动态显示数据，可以用公式表示为：“报表 = 多样的格式 + 动态的数据”。 在没有计算机以前，人们利用纸和笔来记录数据。

比如：民间常常说的豆腐帐，就是卖豆腐的每天将自己的卖出的豆腐记在一个本子上，然后每月都要汇总算算，这种情况下，报表数据和报表格式是紧密结合在一起的，都在同一个本子上。数据也只能有一种几乎只有记帐的人才能理解的表现形式，且这种形式难于修改。

vb版机房收费的报表采用的是第三方的grid++report，.net版机房收费系统，采用了自带的报表设计器，实现过程如下：

第一部分：添加数据集

a、右击添加---新建项目；

b、选择窗体应用程序and重命名；

vctdz/u30b3wtu7atmu1o6zl/bxevmbl473hufu+zcrhumo/2lz+y/nt0lxe0ncjrlztyc9saw5lse3w0lxawfnb0ly0y29uc3vtzu1vbmv5z+c806osyseyu8rhuty3vbhjoam8l3a+cjxwpiagicagpgltzybzcmm9"/uploadfile/collfiles/20140707/20140707084231154.png" alt="\">

d、右击添加---新建项目；

e、reoprting---报表；

第二部分：连接数据库

a、选择数据源

b、选择数据库类型

c、选择数据库模型

d、选择数据库连接；

e、将连接字符串保存到应用程序配置文件中；

f、添加连接

g、选择数据库对象

第三部分，设计报表；

a、添加表头

b、选择报表

第四部分：代码实现部分

首先，实体层；

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

d层

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

b层

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

u层

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

最后运行结果；

计算机出现之后，我们利用计算机处理数据和界面设计的功能来生成、展示报表。计算机上的报表的主要特点是数据动态化，格式多样化，并且实现报表数据和报表格式的完全分离，用户可以只修改数据，或者只修改格式。报表分类excel、word等编辑软件：它们可以做出很复杂的报表格式，但是由于它们没有定义专门的报表结构来动态的加载报表数据，所有这类软件中的数据都是已经定义好的，静态的，不能动态变化的。它们没有办法实现报表软件的“数据动态化”特性。自此，日结的功能告一段落，机房收费系统未完，待续......