if exists(select 1 from sysobjects where name = ‘eb_rp_pertrancereportday‘ and type = ‘P‘)    drop procedure eb_rp_pertrancereportdaygocreate procedure eb_rp_pertrancereportday(@rprtdt varchar(8))     --个人网银业务状况明细日报表asbegin    declare @brchno        varchar(10)    declare @brchna        varchar(255)        declare @iitrnm int                            --期初行内转账笔数    declare @iitram decimal(15,2)                --期初行内转账金额    declare @intrnm int                            --新增行内转账笔数    declare @intram decimal(15,2)                --新增行内转账金额    declare @ietrnm int                            --期末行内转账笔数    declare @ietram decimal(15,2)                --期末行内转账金额    declare @oitrnm int                            --期初跨行转账笔数    declare @oitram decimal(15,2)                --期初跨行转账金额    declare @ontrnm int                            --新增跨行转账笔数    declare @ontram decimal(15,2)                --新增跨行转账金额    declare @oetrnm int                            --期末跨行转账笔数    declare @oetram decimal(15,2)                --期末跨行转账金额    declare @aitrnm int                            --期初总计转账笔数    declare @aitram decimal(15,2)                --期初总计转账金额    declare @antrnm int                            --新增总计转账笔数    declare @antram decimal(15,2)                --新增总计转账金额    declare @aetrnm int                            --期末总计转账笔数    declare @aetram decimal(15,2)                --期末总计转账金额        declare @f_rprtdt   varchar(10)                --今天first 格式yyyy-mm-dd    declare @e_rprtdt    varchar(10)                --明天end 格式yyyy-mm-dd    declare @o_rprtdt    varchar(10)             --昨天last 格式yyyymmdd    declare @n_rprtdt    varchar(10)                --明天  格式yyyymmdd    select @f_rprtdt = convert(varchar,datepart(yy,@rprtdt))+"-"+convert(varchar,datepart(mm,@rprtdt))+"-"+convert(varchar,datepart(dd,@rprtdt))    select @n_rprtdt = convert(varchar,dateadd(dd,1,@rprtdt),112)    select @e_rprtdt = convert(varchar,datepart(yy,@n_rprtdt))+"-"+convert(varchar,datepart(mm,@n_rprtdt))+"-"+convert(varchar,datepart(dd,@n_rprtdt))    select @o_rprtdt = convert(varchar,dateadd(dd,-1,@rprtdt),112)        --取网点号    declare cur_rprt cursor for select brchno,brchna from emb_brch where statis = ‘1‘ order by brchno for read only     open cur_rprt    fetch cur_rprt into @brchno,@brchna        while @@sqlstatus = 0    begin            if not exists (select 1 from emp_trpt where rprtdt = @o_rprtdt and rptpfg = ‘0‘)        begin            select @iitrnm = 0            select @iitram = 0.00            select @intrnm = 0            select @intram = 0.00            select @ietrnm = 0            select @ietram = 0.00            select @oitrnm = 0            select @oitram = 0.00            select @ontrnm = 0            select @ontram = 0.00            select @oetrnm = 0            select @oetram = 0.00            select @aitrnm = 0            select @aitram = 0.00            select @antrnm = 0            select @antram = 0.00            select @aetrnm = 0            select @aetram = 0.00        end        else begin            select @iitrnm = ietrnm ,@iitram = ietram from emp_trpt where brchno = @brchno and rprtdt = @o_rprtdt and rptpfg=‘0‘                select @oitrnm = oetrnm ,@oitram = oetram from emp_trpt where brchno = @brchno and rprtdt = @o_rprtdt and rptpfg=‘0‘                select @aitrnm = aetrnm ,@aitram = aetram from emp_trpt where brchno = @brchno and rprtdt = @o_rprtdt and rptpfg=‘0‘            end                --新增(当天)行内转账笔数和金额        select @intrnm = count(1), @intram = sum(tranam) from epc_flog a, epc_cifx b where a.mastid = b.mastid and senddt >= @f_rprtdt and senddt < @e_rprtdt and b.brchid = @brchno and a.status = ‘success‘ and tranna = ‘/transfer/transferInBank‘        --新增(当天)跨行转账笔数和金额        select @ontrnm = count(1), @ontram = sum(tranam) from epc_flog a, epc_cifx b where a.mastid = b.mastid and senddt >= @f_rprtdt and senddt < @e_rprtdt and b.brchid = @brchno and a.status = ‘success‘ and tranna = ‘/transfer/transferOutBank‘                                if @iitram is null         begin            select @iitram = 0.00        end                if @intram is null        begin            select @intram = 0.00        end        if @ietram is null        begin            select @ietram = 0.00        end                            if @oitram is null         begin            select @oitram = 0.00        end                if @ontram is null        begin            select @ontram = 0.00        end        if @oetram is null        begin            select @oetram = 0.00        end                if @aitram is null         begin            select @aitram = 0.00        end                if @antram is null        begin            select @antram = 0.00        end        if @aetram is null        begin            select @aetram = 0.00        end                select @antrnm = @intrnm + @ontrnm  --当天新增转账笔数合计                select @antram = @intram + @ontram  --当天新增转账金额合计        select @ietrnm = @iitrnm + @intrnm  --本期行内转账笔数        select @oetrnm = @oitrnm + @ontrnm    --本期跨行转账笔数        select @aetrnm = @aitrnm + @antrnm    --本期合计转账笔数                    select @ietram = @iitram + @intram   --本期行内转账金额        select @oetram = @oitram + @ontram     --本期跨行转账金额        select @aetram = @aitram + @antram     --本期行内转账金额                insert into emp_trpt (rprtdt,brchno,brchna,iitrnm,iitram,intrnm,intram,ietrnm,ietram,oitrnm,oitram,ontrnm,ontram,oetrnm,oetram,aitrnm,aitram,antrnm,antram,aetrnm,aetram,rptpfg) values(@rprtdt,@brchno,@brchna,@iitrnm,@iitram,@intrnm,@intram,@ietrnm,@ietram,@oitrnm,@oitram,@ontrnm,@ontram,@oetrnm,@oetram,@aitrnm,@aitram,@antrnm,@antram,@aetrnm,@aetram,‘0‘)        fetch cur_rprt into @brchno,@brchna    end    close cur_rprt    deallocate cursor cur_rprt    endgo

if exists(select 1 from sysobjects where name = ‘eb_rp_pertrancereportmonth‘ and type = ‘P‘)    drop procedure eb_rp_pertrancereportmonthgocreate procedure eb_rp_pertrancereportmonth(@rprtdt varchar(8))     --个人网银业务状况明细月报表asbegin    declare @brchno        varchar(10)    declare @brchna        varchar(255)        declare @iitrnm int                            --期初行内转账笔数    declare @iitram decimal(15,2)                --期初行内转账金额    declare @intrnm int                            --新增行内转账笔数    declare @intram decimal(15,2)                --新增行内转账金额    declare @ietrnm int                            --期末行内转账笔数    declare @ietram decimal(15,2)                --期末行内转账金额    declare @oitrnm int                            --期初跨行转账笔数    declare @oitram decimal(15,2)                --期初跨行转账金额    declare @ontrnm int                            --新增跨行转账笔数    declare @ontram decimal(15,2)                --新增跨行转账金额    declare @oetrnm int                            --期末跨行转账笔数    declare @oetram decimal(15,2)                --期末跨行转账金额    declare @aitrnm int                            --期初总计转账笔数    declare @aitram decimal(15,2)                --期初总计转账金额    declare @antrnm int                            --新增总计转账笔数    declare @antram decimal(15,2)                --新增总计转账金额    declare @aetrnm int                            --期末总计转账笔数    declare @aetram decimal(15,2)                --期末总计转账金额            declare @f_rprtdt varchar(10)                --当月第一天        declare @e_rprtdt    varchar(10)                --当月最后一天    declare @o_rprtdt varchar(10)                --上月最后一天            select @f_rprtdt = convert(varchar,convert(char(8),(datepart(yy,@rprtdt)*10000+datepart(mm,@rprtdt)*100+01)),112)    select @e_rprtdt = convert(varchar,dateadd(dd,-1,dateadd(mm,1,convert(char(8),(datepart(yy,@rprtdt)*10000+datepart(mm,@rprtdt)*100+01)))),112)    --上月最后一天    select @o_rprtdt = convert(varchar,dateadd(dd,-1,convert(char(8),(datepart(yy,@rprtdt)*10000+datepart(mm,@rprtdt)*100+01))),112)            --取网点号    declare cur_rprt cursor for select brchno,brchna from emb_brch where statis = ‘1‘ order by brchno for read only     open cur_rprt    fetch cur_rprt into @brchno,@brchna        while @@sqlstatus = 0    begin            if not exists (select 1 from emp_trpt where rprtdt = @o_rprtdt and rptpfg = ‘1‘)        begin            select @iitrnm = 0            select @iitram = 0.00            select @intrnm = 0            select @intram = 0.00            select @ietrnm = 0            select @ietram = 0.00            select @oitrnm = 0            select @oitram = 0.00            select @ontrnm = 0            select @ontram = 0.00            select @oetrnm = 0            select @oetram = 0.00            select @aitrnm = 0            select @aitram = 0.00            select @antrnm = 0            select @antram = 0.00            select @aetrnm = 0            select @aetram = 0.00        end        else begin            --            select @iitrnm = ietrnm ,@iitram = ietram from emp_trpt where brchno = @brchno and rprtdt = @o_rprtdt and rptpfg=‘1‘                select @oitrnm = oetrnm ,@oitram = oetram from emp_trpt where brchno = @brchno and rprtdt = @o_rprtdt and rptpfg=‘1‘                select @aitrnm = aetrnm ,@aitram = aetram from emp_trpt where brchno = @brchno and rprtdt = @o_rprtdt and rptpfg=‘1‘            end            select @intrnm = sum(intrnm) from emp_trpt where brchno = @brchno and rprtdt between @f_rprtdt and @e_rprtdt and rptpfg = ‘0‘            select @intram = sum(intram) from emp_trpt where brchno = @brchno and rprtdt between @f_rprtdt and @e_rprtdt and rptpfg = ‘0‘            select @ontrnm = sum(ontrnm) from emp_trpt where brchno = @brchno and rprtdt between @f_rprtdt and @e_rprtdt and rptpfg = ‘0‘            select @ontram = sum(ontram) from emp_trpt where brchno = @brchno and rprtdt between @f_rprtdt and @e_rprtdt and rptpfg = ‘0‘                        if @iitram is null         begin            select @iitram = 0.00        end                if @intram is null        begin            select @intram = 0.00        end        if @ietram is null        begin            select @ietram = 0.00        end                            if @oitram is null         begin            select @oitram = 0.00        end                if @ontram is null        begin            select @ontram = 0.00        end        if @oetram is null        begin            select @oetram = 0.00        end                if @aitram is null         begin            select @aitram = 0.00        end                if @antram is null        begin            select @antram = 0.00        end        if @aetram is null        begin            select @aetram = 0.00        end                select @antrnm = @intrnm + @ontrnm  --当月新增转账笔数合计                select @antram = @intram + @ontram  --当月新增转账金额合计         select @ietrnm = @iitrnm + @intrnm  --本期行内转账笔数        select @oetrnm = @oitrnm + @ontrnm    --本期跨行转账笔数        select @aetrnm = @aitrnm + @antrnm    --本期合计转账笔数                    select @ietram = @iitram + @intram   --本期行内转账金额        select @oetram = @oitram + @ontram     --本期行内转账金额        select @aetram = @aitram + @antram     --本期行内转账金额                insert into emp_trpt values(@rprtdt,@brchno,@brchna,@iitrnm,@iitram,@intrnm,@intram,@ietrnm,@ietram,@oitrnm,@oitram,@ontrnm,@ontram,@oetrnm,@oetram,@aitrnm,@aitram,@antrnm,@antram,@aetrnm,@aetram,‘1‘)        fetch cur_rprt into @brchno,@brchna    end    close cur_rprt    deallocate cursor cur_rprt    endgo

---------------网银新统计报表总表create table dbo.emc_cprt (mainid     int              identity not null ,rprtdt     smalldatetime         null ,status     char(1)               null ,rptype     char(1)               null ,rprtcs     char(1)               null ,--2:个人网银交易统计constraint pk_emc_cprt primary key nonclustered (mainid))-----------------个人网银交易统计create table dbo.emp_trpt (mainid     int              identity not null ,rprtdt     smalldatetime         null ,iitrnm int NULL ,                            --期初行内转账笔数iitram decimal(15,2) null,        --期初行内转账金额intrnm int NULL ,                            --新增行内转账笔数intram decimal(15,2) null,        --新增行内转账金额ietrnm int NULL ,                            --期末行内转账笔数ietram decimal(15,2) null,        --期末行内转账金额oitrnm int NULL ,                            --期初跨行转账笔数oitram decimal(15,2) null,        --期初跨行转账金额ontrnm int NULL ,                            --新增跨行转账笔数ontram decimal(15,2) null,        --新增跨行转账金额oetrnm int NULL ,                            --期末跨行转账笔数oetram decimal(15,2) null,        --期末跨行转账金额aitrnm int NULL ,                            --期初总计转账笔数aitram decimal(15,2) null,        --期初总计转账金额antrnm int NULL ,                            --新增总计转账笔数antram decimal(15,2) null,        --新增总计转账金额aetrnm int NULL ,                            --期末总计转账笔数aetram decimal(15,2) null,        --期末总计转账金额rptpfg     char(1)               null ,    -- 0:日报表 1:月报表 2:季报表 3:年报表constraint pk_emp_trpt primary key nonclustered (mainid))-----------------个人交易流水表create table dbo.epc_flog (mainid     int              identity not null ,senddt     smalldatetime         null ,        --交易时间brchno     varchar(20)           null ,        --机构号custid     varchar(20)           null ,        --客户号trancd     varchar(20)           null ,        --交易码   (其实是一种类型，，暂时用1表示行内，2表示跨行)tranam     decimal(15,2)          null ,    --交易金额tranfe         decimal(15,2)          null,        --交易手续费constraint pk_epc_flog primary key nonclustered (mainid))--功能：--1、可按日、月维度统计转账交易的统计--2、eb_rp_pertrancereportday查询epc_flog表，统计出当天的交易量，并写入到emp_trpt表--3、eb_rp_pertrancereportmonth从emp_trpt表查询该月每天的交易量并进行统计，统计结果也写入到emp_trpt表--4、统计的图表可使用饼状图、柱状图或曲线图--5、批处理类报表：----a.用曲线图展示每天交易量（金额）同时显示其交易笔数，查询条件：日期范围、指定转账类型----b.用饼状图展示指定月行内转账和跨行转账的笔数和金额，查询条件：指定月份----c.用柱状图展示最近6个月中每月的交易笔数和交易金额--6、实时类报表：----用柱状图展示当天的交易量（笔数和交易金额）-----------------------------------------------------------------------------------------------------------------------------------个人交易流水表create table epc_flog (mainid     int              identity not null ,senddt     smalldatetime         null ,        --交易时间brchno     varchar(20)           null ,        --机构号custid     varchar(20)           null ,        --客户号trancd     varchar(20)           null ,        --交易码   (其实是一种类型，，暂时用1表示行内，2表示跨行)tranam     decimal(15,2)          null ,    --交易金额tranfe         decimal(15,2)          null,        --交易手续费constraint pk_epc_flog primary key nonclustered (mainid))-----------------个人网银交易统计create table dbo.emp_trpt (mainid     int              identity not null ,rprtdt     smalldatetime         null ,intrnm int NULL ,            --新增行内转账笔数intram decimal(15,2) null,        --新增行内转账金额ontrnm int NULL ,            --新增跨行转账笔数ontram decimal(15,2) null,        --新增跨行转账金额rptpfg     char(1)               null ,    -- 0:日报表 1:月报表 2:季报表 3:年报表constraint pk_emp_trpt primary key nonclustered (mainid))---创建存储过程.create or replace procedure  p_pertrancereportday(pdate in date)    as            Cursor my_cursor         is        select TRANCD ,count(1) pcount,sum(TRANAM) pamount        from EPC_FLOG            where SENDDT between (pdate-interval ‘24‘ hour) and pdate             group by TRANCD ;        c_row my_cursor%rowtype ;        begin                for c_row in my_cursor loop                    if c_row.TRANCD=‘1‘ then                   insert into EMP_TRPT(RPRTDT,INTRNM,INTRAM,ONTRNM,ONTRAM,RPTPFG)           values(pdate,c_row.pcount,c_row.pamount,0,0.00,‘0‘);        else           insert into  EMP_TRPT(RPRTDT,INTRNM,INTRAM,ONTRNM,ONTRAM,RPTPFG)           values(pdate,0,0.00,c_row.pcount,c_row.pamount,‘0‘);        end if;                end loop;    end;    ----存储过程的调用declare pdate date:= to_date(‘2013-07-08 17:00:00‘,‘yyyy-MM-dd hh24:mi:ss‘) ;beginp_pertrancereportday(pdate);end;/----创建存储过程 （月统计，不可用）create or replace procedure p_pertrancereportmonth(pdate in date)    as        --定义变量        pintrnm EMP_TRPT.INTRNM%TYPE;        pintram EMP_TRPT.INTRAM%TYPE;        pontrnm EMP_TRPT.ONTRNM%TYPE;        pontram EMP_TRPT.ONTRAM%TYPE;    begin        select  sum(INTRNM) into pintrnm, sum(INTRAM) into pintram, sum(ONTRNM) into pontrnm, sum(ONTRAM) into pontram ，--这里的聚合函数只能有一个        from EMP_TRPT             where RPTPFG=‘0‘ and  RPRTDT between trunc(pdate,‘MON‘) and pdate ; --用到了oracle的时间函数                           if SQL%FOUND then             insert into EMP_TRPT(RPRTDT,INTRNM,INTRAM,ONTRNM,ONTRAM,RPTPFG)             values(pdate,pintrnm,pintram,pontrnm,pontram,‘1‘);        end if;        commit;    end; -----调用与前一个一样--创建序列      drop sequence emp_trpt_tb_seq;      create sequence emp_trpt_tb_seq               increment by 1               start with 1;   --创建序列 create or replace trigger emp_trpt_tb_tri          before insert on emp_trpt               for each row                                begin                                                 select emp_trpt_tb_seq.nextval into :new.MAINID from dual;           end;----创建存储过程(月统计，可用)create or replace procedure p_pertrancereportmonth(pdate in date)    as        Cursor my_cursor         is        select  sum(INTRNM) pintrnm, sum(INTRAM) pintram, sum(ONTRNM) pontrnm, sum(ONTRAM) pontram        from EMP_TRPT             where RPTPFG=‘0‘ and  RPRTDT between trunc(pdate,‘MON‘) and pdate ; --用到了oracle的时间函数        c_row my_cursor%rowtype ;    begin        for c_row in my_cursor loop          insert into EMP_TRPT(RPRTDT,INTRNM,INTRAM,ONTRNM,ONTRAM,RPTPFG)             values(pdate,c_row.pintrnm,c_row.pintram,c_row.pontrnm,c_row.pontram,‘1‘);                end loop;        end;