temp-存储过程 以前的

-----------------------------------------------------------------------------------------------------------------

　　　　eb_rp_pertrancereportday(个人网银业务状况明细日报表).sql

if exists(select 1 from sysobjects where name = 'eb_rp_pertrancereportday' and type = 'P')
    drop procedure eb_rp_pertrancereportday
go

create procedure eb_rp_pertrancereportday(@rprtdt varchar(8))     --个人网银业务状况明细日报表
as
begin
    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
    
end
go

-----------------------------------------------------------------------------------------------------------------

　　　　eb_rp_pertrancereportmonth(个人网银业务状况明细月报表).sql

if exists(select 1 from sysobjects where name = 'eb_rp_pertrancereportmonth' and type = 'P')
    drop procedure eb_rp_pertrancereportmonth
go

create procedure eb_rp_pertrancereportmonth(@rprtdt varchar(8))     --个人网银业务状况明细月报表
as
begin
    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
    
end
go

-----------------------------------------------------------------------------------------------------------------

　　　　报表.sql---我写的

---------------网银新统计报表总表
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') ;
begin

p_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;