proc.sql

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

        set v_i=v_i+1;
    end while;
    delete from session.tmp_mxz_c where pzjf+pzdf = 0   and trim(pzzy) <>'上年结转' and trim(pzzy) <>'上期结转';
    --分页计算
    set v_cjd1='  ';
    set v_nqce1=0;
    set v_i=0;
    open bm_cur;
    loop_bm_cur:
    loop
        fetch bm_cur into v_cpxbm,v_clbbm,v_cjd,v_nqce,v_cpzid,v_dpzrq;
        if sqlcode=100 then
           leave loop_bm_cur;
        end if;
        set v_i = v_i + 1;
        set v_j = (case when v_printort=1 and v_ldfkm=0 and v_lzph=0 then v_printzyhs_zx else v_printzyhs_hx end);
        if mod(v_i,v_j)=v_j-1 then
            insert into session.tmp_mxz_c (pxbm,lbbm,pzzy,jd,ye) values(v_cpxbm,v_clbbm||'01','过次页',v_cjd,v_nqce);
            insert into session.tmp_mxz_c (pxbm,lbbm,pzzy,jd,ye) values(v_cpxbm,v_clbbm||'02','承前页',v_cjd,v_nqce);
            set v_i = 1;
        end if;
        if (ifnull(trim(v_cjd),'')='') and (v_dpzrq is not null) then
            update session.tmp_mxz_c set jd=v_cjd1, ye=v_nqce1 where pzid=v_cpzid ;
        end if;
        set v_cjd1 =v_cjd;
        set v_nqce1=v_nqce;
    end loop;
    close bm_cur;

  else
    select kmjb into v_kmjb from jtpjek a,jtpkmk b where a.kmid=b.kmid and trim(a.kmbm)=trim(v_ckmbm);
    select count(*) into v_cnt from jtpjek a,jtpkmk b
        where a.kmid=b.kmid and a.kmbm like trim(v_ckmbm)||'%'
        and b.kmjb=v_kmjb+1;

    set v_i=1;
    open mx_cur;
    loop_mx_cur:
    loop
        fetch mx_cur into v_cmxkm,v_ckmmc;
        if sqlcode=100 then
           leave loop_mx_cur;
        end if;
        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)=trim(v_cmxkm);
        set v_cmc=v_ckmmc;
        while posstr(v_cmc,'/')<>0 do
            set v_cmc=substr(v_cmc,posstr(v_cmc,'/'));
        end while;
        set v_cm0=(case when v_nyefx=1 then '借' else '贷' end);
        set v_cl0=(case when v_nyefx=1 then '贷' else '借' end);
        set v_ckmmc=v_cmc;
        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_cmxkm,v_lpz,v_ljz,v_nqce  ,v_nrets );	--期初余额
        set v_np2=9;
        call p_calc(1,9, v_drq ,v_drq ,v_cmxkm,v_lpz,v_ljz,v_nqcjf ,v_nrets );	--期初累计借方发生额
        set v_np2=10;
        call p_calc(1,10,v_drq ,v_drq ,v_cmxkm,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,v_cmxkm,v_ckmmc,zph,pzid,pzywyd,pzlb from jtppzk
                    where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_cmxkm)||'%'  and ifnull(trim(pzjzid),'')<>''
                    order by kmbm,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,v_cmxkm,v_ckmmc,zph,pzid,pzywyd,pzlb from jtppzk
                    where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_cmxkm)||'%'  and ifnull(trim(pzfhid),'')<>''
                    order by kmbm,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,v_cmxkm,v_ckmmc,zph,pzid,pzywyd,pzlb from jtppzk
                    where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_cmxkm)||'%'
                    order by kmbm,pzrq,pzbm ;
        end if;
        set v_drq=v_drq1;
        set v_i=v_i+1;
        while v_drq<=v_drq2 do
            if v_drq=v_drq1 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,'01',date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01'),'    '||case when substr(v_drq,5,2)='01' then '上年结转' else '上期结转' end,
                      v_cjd,v_nqce,v_cmxkm,v_ckmmc);
                set v_i=v_i+1;
            end if;
            open pz_cur1;
            loop_pz_cur1:
            loop
                fetch pz_cur1 into v_gjd,v_cpzbm,v_cpzid,v_npzjf,v_npzdf;
                if sqlcode=100 then
                   leave loop_pz_cur1;
                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='02',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(v_drq, v_cpzbm, v_nkmfx, v_cdfkm);
                    update session.tmp_mxz_c set dfkm = v_cdfkm where pzid = v_cpzid;
                end if;
                set v_i=v_i+1;
             end loop;
             close pz_cur1;
             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),'03',pzrq,'    本日合计',sum(pzjf),sum(pzdf),
                    '  ',v_else,
--                    max(case when pxbm=max(pxbm) then jd else '  '     end),
--                    max(case when pxbm=max(pxbm) then ye else v_else    end),
                    v_cmxkm,v_ckmmc,max(pxbm) from session.tmp_mxz_c
                    where pzywyd=v_drq and trim(kmbm) = trim(v_cmxkm) 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,'04',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    本月合计',
                    value(sum(pzjf),0),value(sum(pzdf),0),v_cjd,v_nqce,v_cmxkm,v_ckmmc from session.tmp_mxz_c
                    where pzywyd=v_drq and trim(kmbm) = trim(v_cmxkm);
            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,'05',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    本年累计',
                        v_nqcjf+value(sum(pzjf),0),v_nqcdf+value(sum(pzdf),0),v_cjd,v_nqce,v_cmxkm,v_ckmmc from jtppzk
                where pzywyd>=v_drq1 and pzywyd<=v_drq and kmbm like trim(v_cmxkm)||'%' 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,'05',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    本年累计',
                        v_nqcjf+value(sum(pzjf),0),v_nqcdf+value(sum(pzdf),0),v_cjd,v_nqce,v_cmxkm,v_ckmmc from jtppzk
                where pzywyd>=v_drq1 and pzywyd<=v_drq and kmbm like trim(v_cmxkm)||'%' 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,'05',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    本年累计',
                        v_nqcjf+value(sum(pzjf),0),v_nqcdf+value(sum(pzdf),0),v_cjd,v_nqce,v_cmxkm,v_ckmmc from jtppzk
                where pzywyd>=v_drq1 and pzywyd<=v_drq and kmbm like trim(v_cmxkm)||'%' ;
            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,'06',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    结转下年',
                        0,0,v_cjd,v_nqce,v_cmxkm,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;
    end loop;
    close mx_cur;
     delete from session.tmp_mxz_c where pzjf+pzdf = 0   and trim(pzzy) <>'上年结转' and trim(pzzy) <>'上期结转';
    open mx_cur;
    loop_mx_cur1:
    loop
        fetch mx_cur into v_cmxkm,v_ckmmc;
        if sqlcode=100 then
           leave loop_mx_cur1;
        end if;
        set v_cmc=v_ckmmc;
        while posstr(v_cmc,'/')<>0 do
            set v_cmc=substr(v_cmc,posstr(v_cmc,'/'));
        end while;
        set v_ckmmc=v_cmc;
        set v_cjd1='  ';
        set v_nqce1=0;
        set v_i=0;
        open bm_cur1;
        loop_bm_cur1:
        loop
            fetch bm_cur1 into v_cpxbm,v_clbbm,v_cjd,v_nqce,v_cpzid,v_dpzrq;
            if sqlcode=100 then
              leave loop_bm_cur1;
            end if;
            set v_i = v_i + 1;
            set v_j = (case when v_printort=1 and v_ldfkm=0 and v_lzph=0 then v_printzyhs_zx else v_printzyhs_hx end);
            if mod(v_i,v_j)=v_j-1 then
                insert into session.tmp_mxz_c (pxbm,lbbm,pzzy,jd,ye,kmbm,kmmc) values(v_cpxbm,v_clbbm||'01','过次页',v_cjd,v_nqce,v_cmxkm,v_ckmmc);
                insert into session.tmp_mxz_c (pxbm,lbbm,pzzy,jd,ye,kmbm,kmmc) values(v_cpxbm,v_clbbm||'02','承前页',v_cjd,v_nqce,v_cmxkm,v_ckmmc);
                set v_i = 1;
            end if;
            if (ifnull(trim(v_cjd),'')='') and (v_dpzrq is not null) then
                update session.tmp_mxz_c set jd=v_cjd1, ye=v_nqce1 where pzid=v_cpzid ;
            end if;
            set v_cjd1 =v_cjd;
            set v_nqce1=v_nqce;
        end loop;
        close bm_cur1;
    end loop;
    close mx_cur;
  end if;
  set v_pxbm1='00000001';
  open bm_cur2;
  loop_bm_cur2:
  loop
        fetch bm_cur2 into v_pxbm,v_pzzy;
        if sqlcode=100 then
           leave loop_bm_cur2;
        end if;
        if v_lrjz = 1 then
           if trim(v_pzzy)='当日小计' then
               set v_pxbm1=v_pxbm;
           end if;
           if trim(v_pzzy)='过次页' then
              select value(sum(pzjf),0),value(sum(pzdf),0) into v_pzjf,v_pzdf from session.tmp_mxz_c where pxbm>v_pxbm1 and pxbm<=v_pxbm;
              set v_pxbm1=v_pxbm;
              update session.tmp_mxz_c set pzjf=v_pzjf,pzdf=v_pzdf where pxbm=v_pxbm and trim(pzzy)='过次页';
           end if;
        else
           if trim(v_pzzy)='过次页' then
              select value(sum(pzjf),0),value(sum(pzdf),0) into v_pzjf,v_pzdf from session.tmp_mxz_c where pxbm>v_pxbm1 and pxbm<=v_pxbm ;
              set v_pxbm1=v_pxbm;
              update session.tmp_mxz_c set pzjf=v_pzjf,pzdf=v_pzdf where pxbm=v_pxbm and trim(pzzy)='过次页';
           end if;
        end if;
  end loop;
  close bm_cur2;
  
  call p_pz_update_pzlbview(v_lsb);

  commit;
  begin
    declare sel_cur cursor with return to client for select * from session.tmp_mxz_c order by pxbm,lbbm;
    open sel_cur;
  end;
end;
end@