proc.sql

一个财务程序和相应的数据库脚本

drop procedure p_mxz_lz@
create  procedure p_mxz_lz  --兰州
(
  v_drq11  varchar(6),
  v_drq2   varchar(6),   
  v_ckmbm  varchar(50),     
  v_lmxz   smallint,          
  v_lrjz   smallint,  --是否包含当日小计          
  v_ldfkm  smallint,          
  v_lzph   smallint,          
  v_lpz    smallint,           
  v_ljz    smallint,
  v_del0   smallint   --是否删除借贷方均为0的记录
)
result sets 1
language sql
begin
   declare sqlcode integer default 0;
   declare v_snjz varchar(20) default '上年结转';
   declare v_sqjz varchar(20) default '上期结转';   
   declare v_brxj varchar(20) default '当日小计';     
   declare v_byhj varchar(20) default '本月合计';     
   declare v_bnlj varchar(20) default '本年累计';     
   declare v_jzxn varchar(20) default '结转下年';      
   
   declare global temporary table session.tmp_mxz_c
  (
    num      int default 0 not null,
    pxbm     varchar(8) default ' ',
    lbbm     varchar(4) default ' ',
    pzrq     date,
    gjdbm    varchar(2) default ' ',
    pzbm     varchar(17) default ' ' not null,
    pzzy     varchar(255) default ' ' not null,
    pzjf     decimal(18,2) default 0 not null,
    pzdf     decimal(18,2) default 0 not null,
    jd       varchar(4) default ' ',
    ye       decimal(18,2) default 0 not null,
    kmbm     varchar(50) default ' ',
    kmmc     varchar(255) default ' ',
    zph      varchar(15) default ' ',
    dfkm     varchar(2000) default ' ',
    pzywyd   varchar(6) default ' ',
    pzid     varchar(36) default ' ',
    pzlb     smallint default 0 not null,
    pzlbview varchar(10) default ' ' not null,
    hslx     smallint default 1 not null,
    hsxm     varchar(100) default ' ' not null,
    pzlbpx   smallint default 0 not null,
    ywlsh    varchar(3949) default ' ' not null
  )
  on commit preserve rows with replace;  
begin
  declare v_drq1         varchar(6);
  declare v_drq          varchar(6);
  declare v_qyny         varchar(6);
  declare v_printort     smallint;
  declare v_printzyhs_zx smallint;
  declare v_printzyhs_hx smallint;
  declare v_nyefx        smallint;
  declare v_nrets        smallint;
  declare v_nqce         decimal(16,2);
  declare v_nqcjf        decimal(16,2);
  declare v_nqcdf        decimal(16,2);
  declare v_cmxkm        varchar(50);
  declare v_ckmmc        varchar(255);
  declare v_cmc          varchar(255);
  declare v_cm0          varchar(4);
  declare v_cl0          varchar(4);
  declare v_cjd          varchar(4);
  declare v_cpzid        varchar(36);
  declare v_i            integer;
  declare v_j            smallint;
  declare v_k            integer;
  declare v_npzjf        decimal(16,2);
  declare v_npzdf        decimal(16,2);
  declare v_nkmfx        smallint;
  declare v_cpxbm        varchar(8) ;
  declare v_clbbm        varchar(4) ;
  declare v_else         decimal(16,2);
  declare v_cpzbm        varchar(10);
  declare v_cdfkm        varchar(2000);
  declare v_ywlsh        varchar(3949);
  declare v_kmjb         smallint default 0;
  declare v_pzlbbm       smallint default 0;
  declare v_clsh         varchar(20);
  declare v_gjd          varchar(2);
  declare v_np1 smallint default 1;
  declare v_np2 smallint default 1;
  declare v_date date;
  declare  v_dpzrq date;
  declare  v_cjd1  varchar(4);
  declare  v_nqce1 decimal(16,2);
  declare v_cnt integer default 0;
  declare v_pzzy         varchar(255);
  declare v_pxbm        varchar(8) ;
  declare v_pxbm1       varchar(8) ;
  declare v_pzjf        decimal(16,2);
  declare v_pzdf        decimal(16,2);
  declare v_lsb varchar(30) default 'session.tmp_mxz_c';
    declare v_errorcode integer;
  declare v_errormsg  varchar(255);

  declare pz_cur cursor for select gjdbm,pzbm,pzid,pzjf,pzdf,pzlb from session.tmp_mxz_c where pzywyd=v_drq order by pzrq,pzbm;
  declare bm_cur cursor for select pxbm,trim(lbbm),jd,ye,pzid,pzrq from session.tmp_mxz_c order by pxbm,lbbm;


  declare mx_cur cursor for  select a.kmbm,a.kmmc from jtpjek a,jtpkmk b
        where a.kmid=b.kmid and a.kmbm like trim(v_ckmbm)||'%'
        and b.kmjb=v_kmjb+(case when v_cnt>0 then 1 else 0 end) order by a.kmbm;
  declare pz_cur1 cursor for select gjdbm,pzbm,pzid,pzjf,pzdf,pzlb from session.tmp_mxz_c where pzywyd=v_drq and trim(kmbm)=trim(v_cmxkm) order by pzrq,pzbm;
  declare bm_cur1  cursor for select pxbm,trim(lbbm),jd,ye,pzid,pzrq from session.tmp_mxz_c where trim(kmbm)=trim(v_cmxkm) order by pxbm,lbbm;
  
  declare bm_cur2  cursor for select pxbm,pzzy from session.tmp_mxz_c order by pxbm,lbbm;

  declare exit handler for sqlexception   
  begin
    get diagnostics exception 1 v_errormsg=message_text;
    set v_errorcode=sqlcode;
    rollback;
    insert into log_err(err_date,name_proc,err_code,err_msg)
      values(current timestamp,'p_mxz_lz',v_errorcode,v_errormsg);
    set v_nrets = -5;
    commit;
      end; 

  set v_drq1=v_drq11;
if v_lmxz = 0 then
    set v_nrets=0;
    set v_nqce=0;
    set v_else=0;
    select qyny,printort,printzyhs_zx,printzyhs_hx into v_qyny,v_printort,v_printzyhs_zx,v_printzyhs_hx from jtpndk;
    select b.yefx,a.kmmc into v_nyefx, v_ckmmc from jtpjek a,jtpkmk b
            where a.kmid=b.kmid and trim(a.kmbm)=v_ckmbm;
    set v_cm0=(case when v_nyefx=1 then '借' else '贷' end);
    set v_cl0=(case when v_nyefx=1 then '贷' else '借' end);

    if v_drq1<v_qyny then
        set v_drq1=v_qyny;
    end if;
    set v_date = date(substr(v_drq1,1,4)||'-'||substr(v_drq1,5,2)||'-01')-1 months;
    set v_drq=substr(trim(char(v_date)),1,4)||substr(trim(char(v_date)),6,2);

    set v_np2=2;
    call p_calc(1,2, v_qyny,v_drq1,v_ckmbm,v_lpz,v_ljz,v_nqce  ,v_nrets );	    set v_np2=9;
    call p_calc(1,9, v_drq ,v_drq ,v_ckmbm,v_lpz,v_ljz,v_nqcjf ,v_nrets );	    set v_np2=10;
    call p_calc(1,10,v_drq ,v_drq ,v_ckmbm,v_lpz,v_ljz,v_nqcdf ,v_nrets );	
    if v_ljz=0 then
        insert into session.tmp_mxz_c (pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,kmmc,zph,pzid,pzywyd,pzlb)
        select pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,v_ckmmc,zph,pzid,pzywyd,pzlb from jtppzk
                where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_ckmbm)||'%' and ifnull(trim(pzjzid),'')<>''
                order by pzrq,pzbm;
    elseif v_lpz=0   then
        insert into session.tmp_mxz_c (pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,kmmc,zph,pzid,pzywyd,pzlb)
        select pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,v_ckmmc,zph,pzid,pzywyd,pzlb from jtppzk
                where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_ckmbm)||'%' and ifnull(trim(pzfhid),'')<>''
                order by pzrq,pzbm;
    else
        insert into session.tmp_mxz_c (pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,kmmc,zph,pzid,pzywyd,pzlb)
        select pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,v_ckmmc,zph,pzid,pzywyd,pzlb from jtppzk
                where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_ckmbm)||'%'
                order by pzrq,pzbm ;
    end if;


    set v_drq=v_drq1;
    set v_i=1;
    while v_drq<=v_drq2 do
        if v_i=1 then
            set v_cpxbm=right('00000000'||trim(char(v_i)),8);
            set v_cjd=(case when v_nqce=0 then '平' else v_cm0 end);
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,jd,ye,kmbm,kmmc)
            values(v_i,v_cpxbm,'00',date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01'),'    '||case when substr(v_drq,5,2)='01' then v_snjz else v_sqjz end,
                    v_cjd,v_nqce,v_ckmbm,v_ckmmc) ;
        end if;
        set v_i=v_i+1;
        open pz_cur;
        loop_pz_cur:
        loop
            fetch pz_cur into v_gjd,v_cpzbm,v_cpzid,v_npzjf,v_npzdf,v_pzlbbm;
            if sqlcode=100 then
              leave loop_pz_cur;
            end if;
            set v_cpzbm=v_gjd||v_cpzbm;
            set v_nqce=v_nqce + (case when v_nyefx=1 then v_npzjf-v_npzdf else v_npzdf-v_npzjf end);
            set v_cjd=(case when v_nqce=0 then '平' else v_cm0 end);
            set v_cpxbm=right('00000000'||trim(char(v_i)),8);
            update session.tmp_mxz_c set num = v_i, jd = v_cjd ,pxbm = v_cpxbm,lbbm='01',ye = v_nqce where pzid = v_cpzid;
            if v_ldfkm=1 then
                set v_nkmfx= case when v_npzjf>0 then 1 else 2 end;
                set v_cdfkm=repeat(' ',255);
                call p_get_dfkm_lz(v_drq, v_cpzbm, v_nkmfx, v_cdfkm);
                update session.tmp_mxz_c set dfkm = v_cdfkm where pzid = v_cpzid;
                call p_get_yslsh(v_drq,v_cpzbm,v_pzlbbm,v_ywlsh);
                update session.tmp_mxz_c set ywlsh = v_ywlsh where pzid = v_cpzid;
            end if;
            set v_i=v_i+1;
        end  loop;
        close pz_cur;
        if v_lrjz = 1 then
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc,pzid)
                select 0,max(pxbm),'02',pzrq,'    '||v_brxj,sum(pzjf),sum(pzdf),
                ' ',v_else,
                v_ckmbm,v_ckmmc,max(pxbm) from session.tmp_mxz_c
                where pzywyd=v_drq group by pzrq;
            set v_i=v_i+1;
        end if;
        if v_nqce=0 then
            set v_cjd='平';
        end if;
        set v_cpxbm=right('00000000'||trim(char(v_i)),8);
        insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc)
        select v_i,v_cpxbm,'03',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    '||v_byhj,
                value(sum(pzjf),0),value(sum(pzdf),0),v_cjd,v_nqce,v_ckmbm,v_ckmmc from session.tmp_mxz_c where pzywyd=v_drq;
        set v_i=v_i+1;
        set v_cpxbm=right('00000000'||trim(char(v_i)),8);
        if v_ljz=0 then
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc)
            select v_i,v_cpxbm,'04',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    '||v_bnlj,
                    v_nqcjf+value(sum(pzjf),0),v_nqcdf+value(sum(pzdf),0),v_cjd,v_nqce,v_ckmbm,v_ckmmc from jtppzk
            where pzywyd>=v_drq1 and pzywyd<=v_drq and kmbm like trim(v_ckmbm)||'%' and ifnull(trim(pzjzid),'')<>'';
        elseif v_lpz = 0 then
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc)
            select v_i,v_cpxbm,'04',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    '||v_bnlj,
                    v_nqcjf+value(sum(pzjf),0),v_nqcdf+value(sum(pzdf),0),v_cjd,v_nqce,v_ckmbm,v_ckmmc from jtppzk
            where pzywyd>=v_drq1 and pzywyd<=v_drq and kmbm like trim(v_ckmbm)||'%' and ifnull(trim(pzfhid),'')<>'';
        else
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc)
            select v_i,v_cpxbm,'04',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    '||v_bnlj,
                    v_nqcjf+value(sum(pzjf),0),v_nqcdf+value(sum(pzdf),0),v_cjd,v_nqce,v_ckmbm,v_ckmmc from jtppzk
            where pzywyd>=v_drq1 and pzywyd<=v_drq and kmbm like trim(v_ckmbm)||'%';
        end if;
        if substr(v_drq,5,2)='12' and v_nqce <> 0 then
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc)
            values( v_i,v_cpxbm,'05',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    '||v_jzxn,
                    0,0,v_cjd,v_nqce,v_ckmbm,v_ckmmc );
        end if;
        set v_date = date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')+1 months;
        set v_drq=substr(trim(char(v_date)),1,4)||substr(trim(char(v_date)),6,2);
        set v_i=v_i+1;
    end while;
    if v_del0 = 1 then
      delete from session.tmp_mxz_c where pzjf=0 and pzdf = 0   and trim(pzzy) <>v_snjz and trim(pzzy) <>v_sqjz;
    end if;
        set v_cjd1='  ';
    set v_nqce1=0;