ALTER PROCEDURE "dba"."pa_stat_keskarv"( in a_skoopkood char(16), in a_skoop integer, in a_yksuskood char(16), in a_yksusnr integer, in a_a_akuu numeric(2), in a_a_aasta char(4), in a_statnr integer default 0)
BEGIN
  declare ld_peralg date;
  declare ld_perlop date;
  declare ll_lepnr integer;
  declare ldc_koht decimal(15,6);
  declare ldc_koht5 decimal(15,6);
  declare ldc_summa decimal(15,2);
  declare ls_tasudeeb char(7);
  declare ls_konto char(7);
  declare ls_pa_leping_tun_konto char(7);
  declare ls_konto5 char(7);
  declare ld_kuup1 date;
  declare ld_kuup2 date;
  declare ld_kuup5 date;
  declare li_talnr integer;
  declare li_leping_tun smallint;
  declare ldc_kpa decimal(15,2);
  declare err_notfound exception for sqlstate value '02000';
  declare c_leping dynamic scroll cursor for
    select pa_isik.lepnr,
           min(if pa_isik.kuup1 is null then pa_leping.kuup1 else if pa_isik.kuup1>pa_leping.kuup1 then pa_isik.kuup1 else pa_leping.kuup1 endif endif) as pa_kuup1,
           max(if pa_isik.kuup2 is null then if pa_leping.kuup2 is null then ymd(2500,12,31) else pa_leping.kuup2 endif else if pa_leping.kuup2 is null then pa_isik.kuup2 else if pa_isik.kuup2<pa_leping.kuup2 then pa_isik.kuup2 else pa_leping.kuup2 endif endif endif) as pa_kuup2
      from pa_isik left outer join pa_leping on pa_isik.lepnr=pa_leping.lepnr
                   left outer join pa_yksus on pa_isik.yksusnr = pa_yksus.yksusnr
                   left outer join skoobid on pa_yksus.skoop=skoobid.skoop
      where ((pa_yksus.skoop = a_skoop or a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood) and
            ((pa_isik.yksusnr = a_yksusnr or a_yksusnr = 0) and Left(pa_yksus.kood,Length(a_yksuskood))=a_yksuskood) and
--            (pa_leping.id_koht=1 or pa_leping.id_koht=4) and
            (pa_yksus.statnr = a_statnr or a_statnr = 0)
       group by pa_isik.lepnr;
  declare c_leping_tun dynamic scroll cursor for
    select pa_leping_tun.kuup1,pa_leping_tun.koht,Left(pa_leping_tun.konto,5)
      from pa_leping_tun
      where pa_leping_tun.lepnr=ll_lepnr and pa_leping_tun.kuup1>ld_kuup1 and pa_leping_tun.kuup1<=ld_kuup2;
  declare c_tgraaf dynamic scroll cursor for
    select lepnr,if akuup>ld_peralg then akuup else ld_peralg endif as a1_kuup,date(dateadd(month,6,a1_kuup))-1 as al_kuup,if lkuup<ld_perlop then lkuup else ld_perlop endif as l1_kuup FROM "pa_tgraaf" where al_kuup<=l1_kuup;
  declare c_pa_tasu dynamic scroll cursor for
    select pa_tasu.lepnr,
           sum(pa_tasu.summa),
          if pa_tasu.talnr is null then (if pa_paryld_b.talnr is null then (if pa_taslist_a.talnr is null then (if pa_taslist_b.talnr is null or pa_taslist_b.samkul=0 then pa_yksus.talnr else pa_taslist_b.talnr endif) else pa_taslist_a.talnr endif) else pa_paryld_b.talnr endif) else pa_tasu.talnr endif as pa_talnr,
          if pa_taslist_a.deeb is null then pa_taslist_b.deeb else pa_taslist_a.deeb endif as pa_deeb,
          if (pa_tasu.v_aasta>pa_tasu.a_aasta or pa_tasu.v_aasta=pa_tasu.a_aasta and pa_tasu.v_akuu>=pa_tasu.a_akuu) then null else (if pa_taslist_a.konto is null then pa_taslist_b.konto else pa_taslist_a.konto endif) endif as pa_konto,
          if li_leping_tun=1 then get_pa_leping_tun_konto(pa_tasu.lepnr,ymd(pa_tasu.a_aasta,pa_tasu.a_akuu, pa_tasu.a_lpaev)) else null endif as pa_tun_konto
      from pa_tasu left outer join pa_taslist as pa_taslist_a on pa_tasu.tas_s=pa_taslist_a.tas_s
                   left outer join pa_taslist as pa_taslist_b on pa_tasu.vtas_s=pa_taslist_b.tas_s
                   left outer join pa_paryld as pa_paryld_b on pa_tasu.vparyldnr = pa_paryld_b.paryldnr
                   left outer join pa_yksus on pa_tasu.yksusnr = pa_yksus.yksusnr 
                   left outer join skoobid on pa_yksus.skoop=skoobid.skoop
                   left outer join gl_4 on pa_tasu.lepnr=gl_4."id"
      where (pa_yksus.skoop = a_skoop or a_skoop = 0) and "Left"(skoobid.kood,Length(a_skoopkood))=a_skoopkood and
            "Left"(pa_yksus.kood,Length(a_yksuskood))=a_yksuskood and (pa_tasu.yksusnr = a_yksusnr or a_yksusnr = 0) and
            (pa_yksus.statnr = a_statnr or a_statnr = 0) and
            pa_deeb is not null and
            gl_4."id" is not null and
            pa_tasu.v_aasta=a_a_aasta and (pa_tasu.v_akuu=a_a_akuu or a_a_akuu=0) and pa_taslist_a.sisse=0
      group by pa_tasu.lepnr,pa_talnr,pa_deeb,pa_konto,pa_tun_konto;
  declare c_pa_tasu_arvest dynamic scroll cursor for
    select pa_tasu.lepnr,
           sum(pa_tasu.summa),
          if pa_tasu.talnr is null then (if pa_paryld_b.talnr is null then (if pa_taslist_a.talnr is null then (if pa_taslist_b.talnr is null or pa_taslist_b.samkul=0 then pa_yksus.talnr else pa_taslist_b.talnr endif) else pa_taslist_a.talnr endif) else pa_paryld_b.talnr endif) else pa_tasu.talnr endif as pa_talnr,
          if pa_taslist_a.deeb is null then pa_taslist_b.deeb else pa_taslist_a.deeb endif as pa_deeb,
          (if pa_taslist_a.konto is null then pa_taslist_b.konto else pa_taslist_a.konto endif) as pa_konto,
          if li_leping_tun=1 then get_pa_leping_tun_konto(pa_tasu.lepnr,ymd(pa_tasu.a_aasta,pa_tasu.a_akuu, pa_tasu.a_lpaev)) else null endif as pa_tun_konto
      from pa_tasu left outer join pa_taslist as pa_taslist_a on pa_tasu.tas_s=pa_taslist_a.tas_s
                   left outer join pa_taslist as pa_taslist_b on pa_tasu.vtas_s=pa_taslist_b.tas_s
                   left outer join pa_paryld as pa_paryld_b on pa_tasu.vparyldnr = pa_paryld_b.paryldnr
                   left outer join pa_yksus on pa_tasu.yksusnr = pa_yksus.yksusnr 
                   left outer join skoobid on pa_yksus.skoop=skoobid.skoop
                   left outer join gl_4 on pa_tasu.lepnr=gl_4."id"
      where (pa_yksus.skoop = a_skoop or a_skoop = 0) and "Left"(skoobid.kood,Length(a_skoopkood))=a_skoopkood and
            "Left"(pa_yksus.kood,Length(a_yksuskood))=a_yksuskood and (pa_tasu.yksusnr = a_yksusnr or a_yksusnr = 0) and
            (pa_yksus.statnr = a_statnr or a_statnr = 0) and
            pa_deeb is not null and pa_konto is not null and
            gl_4."id" is not null and
            ((pa_tasu.a_aasta > pa_tasu.v_aasta) or(pa_tasu.a_aasta = pa_tasu.v_aasta and pa_tasu.a_akuu > pa_tasu.v_akuu)) and
            pa_tasu.a_aasta=a_a_aasta and (pa_tasu.a_akuu=a_a_akuu or a_a_akuu=0) and pa_taslist_a.sisse=0
      group by pa_tasu.lepnr,pa_talnr,pa_deeb,pa_konto,pa_tun_konto;
  declare c_order dynamic scroll cursor for
    select sisse,deeb1,summa from "gl_tr_order";


  if a_a_akuu=0 then
    set ld_peralg=ymd(a_a_aasta,1,1);
    set ld_perlop=ymd(a_a_aasta,12,31);
  else
    set ld_peralg=ymd(a_a_aasta,a_a_akuu,1);
    set ld_perlop=get_perlop(ymd(a_a_aasta,a_a_akuu,28));
  end if;
  set ldc_kpa=get_kp_arv_per_pta(ld_peralg,ld_perlop);
  if ldc_kpa=0 then
    set ldc_kpa=365
  end if;
  delete from gl_tr_universal;
  delete from gl_tr_universal2;
  delete from gl_list;
  delete from gl_tr_order;
  delete from gl_4;

  -- tsykkel yle lepingute
  open c_leping;
  leping_loop: loop
    fetch next c_leping into ll_lepnr,ld_kuup1,ld_kuup2;
    if sqlstate = err_notfound then
      leave leping_loop
    end if;
    -- kui lepingu alguse kuupaev on vaiksem kui muutuv kuupaev
    if ld_kuup1<ld_peralg then
      set ld_kuup1=ld_peralg;
    end if;
    -- kui lepingu lopu kuupaev on suurem kui perioodi lopp
    if ld_kuup2>ld_perlop then
      set ld_kuup2=ld_perlop;
    end if;
    if ld_kuup2>=ld_kuup1 then
      -- tsykkel yle lepingu tunnuste
      set ldc_koht=get_pa_leping_tun_koht(ll_lepnr,ld_kuup1);
      set ls_konto=left(get_pa_leping_tun_konto(ll_lepnr,ld_kuup1),5);
      open c_leping_tun;
      leping_tun_loop: loop
        fetch next c_leping_tun into ld_kuup5,ldc_koht5,ls_konto5;
        if sqlstate = err_notfound then
          leave leping_tun_loop
        end if;
        insert into gl_list("id1",sum1,str1,kuup1,kuup2) values (ll_lepnr,ldc_koht,ls_konto,ld_kuup1,dateadd(day,-1,ld_kuup5));
        set ldc_koht=ldc_koht5;
        set ls_konto=ls_konto5;
        set ld_kuup1=ld_kuup5;
      end loop leping_tun_loop;
      close c_leping_tun;
      insert into gl_list("id1",sum1,str1,kuup1,kuup2) values (ll_lepnr,ldc_koht,ls_konto,ld_kuup1,ld_kuup2);
    end if;
  end loop leping_loop;
  close c_leping;

  -- maha perioodid, kui peatatud kauem, kui kuus kuud
  open c_tgraaf;
  tgraaf_loop: loop
    fetch next c_tgraaf into ll_lepnr,ld_kuup1,ld_kuup2,ld_kuup5;
    if sqlstate = err_notfound then
      leave tgraaf_loop
    end if;
    delete from gl_list where id1=ll_lepnr
  end loop tgraaf_loop;
  close c_tgraaf;
  -- arvuta paevade arv
  update gl_list set sum2=datediff(day,kuup1,kuup2)+1;
  -- tool, keskmine
  update gl_list set sum3=Round(sum1*sum2,6),sum4=Round(sum1*sum2/ldc_kpa,6);
  
  -- yhe lepingu tootajate arv kokku kontode kaupa
  insert into gl_tr_order(sisse,deeb1,summa)
    select gl_list.id1,gl_list.str1,sum(gl_list.sum4)
      from gl_list
      group by gl_list.id1,gl_list.str1;
  if exists(select 1 from gl_tr_order where deeb1 is not null) then
    set li_leping_tun=1
  else
    set li_leping_tun=0
  end if;
  -- yhe lepingu tootajad
  insert into gl_4("id")
    select gl_list.id1
      from gl_list
      group by gl_list.id1;

  -- tootasud kontode ja tegevusalade lőikes
  open c_pa_tasu;
  pa_tasu_loop: loop
    fetch next c_pa_tasu into ll_lepnr,ldc_summa,li_talnr,ls_tasudeeb,ls_konto,ls_pa_leping_tun_konto;
    if sqlstate = err_notfound then -- kui eof
      leave pa_tasu_loop
    end if;
    if ls_pa_leping_tun_konto is not null and Length(ls_pa_leping_tun_konto)>4 and Length(ls_tasudeeb)>5 and left(ls_tasudeeb,2)=left(ls_pa_leping_tun_konto,2) then
      set ls_tasudeeb=ls_pa_leping_tun_konto
    end if;
    if ls_konto is not null then
      set ls_tasudeeb=ls_konto
    end if;
    if Left(ls_tasudeeb,3)='500' then
      insert into gl_tr_universal2("id1",sum1,id2,konto) values (ll_lepnr,ldc_summa,li_talnr,ls_tasudeeb);
    end if;
  end loop pa_tasu_loop;
  close c_pa_tasu;
  -- ette arvestatud
  open c_pa_tasu_arvest;
  pa_tasu_arvest_loop: loop
    fetch next c_pa_tasu_arvest into ll_lepnr,ldc_summa,li_talnr,ls_tasudeeb,ls_konto,ls_pa_leping_tun_konto;
    if sqlstate = err_notfound then -- kui eof
      leave pa_tasu_arvest_loop
    end if;
    if ls_pa_leping_tun_konto is not null and Length(ls_pa_leping_tun_konto)>4 and Length(ls_tasudeeb)>5 and left(ls_tasudeeb,2)=left(ls_pa_leping_tun_konto,2) then
      set ls_tasudeeb=ls_pa_leping_tun_konto
    end if;
    if Left(ls_tasudeeb,3)='500' then
      insert into gl_tr_universal2("id1",sum1,id2,konto) values (ll_lepnr,ldc_summa,li_talnr,ls_tasudeeb);
    end if;
  end loop pa_tasu_arvest_loop;
  close c_pa_tasu_arvest;
  update gl_tr_universal2 left outer join tallist on gl_tr_universal2.id2=tallist.talnr set tal_kood=Left(tallist.osa,5),konto=Left(konto,5);
  -- yhe lepingu summad kokku
  insert into gl_tr_universal("id1",sum1,sum2,tal_kood,konto)
    select gl_tr_universal2.id1,
           sum(gl_tr_universal2.sum1),
           0,
           gl_tr_universal2.tal_kood,
           gl_tr_universal2.konto
      from gl_tr_universal2
      group by id1,tal_kood,konto;
  -- paevad yhekordselt
  open c_order;
  order_loop: loop
    fetch next c_order into ll_lepnr,ls_konto,ldc_koht;
    if sqlstate = err_notfound then
      leave order_loop
    end if;
    if ls_konto is null then
      update first gl_tr_universal set sum2=sum2+ldc_koht where id1=ll_lepnr order by sum1 desc;
    else
      update first gl_tr_universal set sum2=sum2+ldc_koht where id1=ll_lepnr and konto=ls_konto order by sum1 desc;
    end if;
  end loop order_loop;
  close c_order;


END