ALTER PROCEDURE "dba"."pr_koondtulem"(in a_skoopkood char(16),in a_skoop integer,in id_paring integer,in peralg date,in perlop date,in ii_mkt smallint default 3)
begin
  declare li_tulemnr integer;
  declare li_tulemnr1 integer;
  declare li_tulemnr2 integer;
  declare ldc_summa decimal(20,6);
  declare ldc_deeb decimal(20,6);
  declare ldc_kree decimal(20,6);
  declare ls_eriline varchar(50);
  declare ls_tk1 char(2);
  declare ls_tk2 char(2);
  declare ls_tk3 char(2);
  declare ls_tk4 char(2);
  declare li_level integer;
  declare err_notfound exception for sqlstate value '02000';
  declare c_gl_pr_koond dynamic scroll cursor for select kontlist.tulemnr,kontlist.tulemnr2,gl_pr_koond.deeb,gl_pr_koond.kree from
      gl_pr_koond left outer join kontlist on gl_pr_koond.konto = kontlist.konto;
  declare c_pr_tulem dynamic scroll cursor for select pr_tulem.tulemnr,pr_tulem.eriline from pr_tulem;
  declare c_pr_koondtulem dynamic scroll cursor for select gl_pr_koondtulem.tulemnr,gl_pr_koondtulem.tk1,gl_pr_koondtulem.tk2,gl_pr_koondtulem.tk3,gl_pr_koondtulem.tk4 from gl_pr_koondtulem order by gl_pr_koondtulem.tk1 asc,gl_pr_koondtulem.tk2 asc,gl_pr_koondtulem.tk3 asc,gl_pr_koondtulem.tk4 asc;
  delete from gl_pr_koondtulem;
  insert into gl_pr_koondtulem( tulemnr,tulkul,tk1,tk2,tk3,tk4,nimi,eriline,summa1,summa2) select pr_tulem.tulemnr,pr_tulem.tulkul,pr_tulem.tk1,pr_tulem.tk2,pr_tulem.tk3,pr_tulem.tk4,pr_tulem.nimi,pr_tulem.eriline,0,0 from pr_tulem;

  call pr_koond1_simple(a_skoopkood,a_skoop,id_paring,peralg,perlop);

  open c_gl_pr_koond;
  pr_koond_loop: loop
    --  loe pearaamatu koondi rida
    fetch next c_gl_pr_koond into li_tulemnr1,li_tulemnr2,ldc_deeb,ldc_kree;
    if sqlstate = err_notfound then -- kui eof
      leave pr_koond_loop
    end if;
    if li_tulemnr1 is null then
      set li_tulemnr1=0
    end if;
    if li_tulemnr2 is null then
      set li_tulemnr2=0
    end if;
    if li_tulemnr1 > 0 or li_tulemnr2 > 0 then
      if ldc_deeb <> 0 then
        set li_tulemnr=li_tulemnr1;
        if li_tulemnr = 0 then
          set li_tulemnr=li_tulemnr2
        end if;
        update gl_pr_koondtulem set summa2 = summa2-ldc_deeb where gl_pr_koondtulem.tulemnr = li_tulemnr
      end if;
      if ldc_kree <> 0 then
        set li_tulemnr=li_tulemnr2;
        if li_tulemnr = 0 then
          set li_tulemnr=li_tulemnr1
        end if;
        update gl_pr_koondtulem set summa2 = summa2+ldc_kree where gl_pr_koondtulem.tulemnr = li_tulemnr
      end if
    end if
  end loop pr_koond_loop;
  close c_gl_pr_koond;

  if month(peralg)=month(perlop) then
    if month(perlop)=month(dateadd(day,1,perlop)) then
      set peralg=dateadd(month,-1,peralg);
      set perlop=dateadd(month,-1,perlop);
    else
      set peralg=dateadd(month,-1,peralg);
      set perlop=get_perlop(dateadd(month,-1,perlop));
    end if;
  else
    set peralg=dateadd(year,-1,peralg);
    set perlop=dateadd(year,-1,perlop);
    if ii_mkt=5 then
      set perlop=ymd(years(perlop),12,31);
    else
      if month(perlop)=2 and day(perlop)=28 then
        set perlop=get_perlop(perlop);
      end if;
    end if;
  end if;

  call pr_koond1_simple(a_skoopkood,a_skoop,id_paring,peralg,perlop);

  open c_gl_pr_koond;
  pr_koond_loop: loop
    --  loe pearaamatu koondi rida
    fetch next c_gl_pr_koond into li_tulemnr1,li_tulemnr2,ldc_deeb,ldc_kree;
    if sqlstate = err_notfound then -- kui eof
      leave pr_koond_loop
    end if;
    if li_tulemnr1 is null then
      set li_tulemnr1=0
    end if;
    if li_tulemnr2 is null then
      set li_tulemnr2=0
    end if;
    if li_tulemnr1 > 0 or li_tulemnr2 > 0 then
      if ldc_deeb <> 0 then
        set li_tulemnr=li_tulemnr1;
        if li_tulemnr = 0 then
          set li_tulemnr=li_tulemnr2
        end if;
        update gl_pr_koondtulem set summa1 = summa1-ldc_deeb where gl_pr_koondtulem.tulemnr = li_tulemnr
      end if;
      if ldc_kree <> 0 then
        set li_tulemnr=li_tulemnr2;
        if li_tulemnr = 0 then
          set li_tulemnr=li_tulemnr1
        end if;
        update gl_pr_koondtulem set summa1 = summa1+ldc_kree where gl_pr_koondtulem.tulemnr = li_tulemnr
      end if
    end if
  end loop pr_koond_loop;
  close c_gl_pr_koond;


  -- erilised vahekokkuvotted
  open c_pr_tulem;
  pr_tulem_loop: loop
    --  loe tulemi rida
    fetch next c_pr_tulem into li_tulemnr,ls_eriline;
    if sqlstate = err_notfound then -- kui eof
      leave pr_tulem_loop
    end if;
    if length(trim(ls_eriline)) > 0 then
      set ls_eriline=','+trim(ls_eriline)+',';
      select sum(summa1),sum(summa2) into ldc_deeb,ldc_kree from gl_pr_koondtulem where locate(ls_eriline,','+trim(gl_pr_koondtulem.tk1)+',') > 0;
      if ldc_deeb is null then
        set ldc_deeb=0
      end if;
      if ldc_kree is null then
        set ldc_kree=0
      end if;
      update gl_pr_koondtulem set summa1kok = ldc_deeb-summa1,summa2kok = ldc_kree-summa2 where gl_pr_koondtulem.tulemnr = li_tulemnr
    end if
  end loop pr_tulem_loop;
  close c_pr_tulem;
  --  vahekokkuvotted
  open c_pr_koondtulem;
  pr_koondtulem_loop: loop
    --  loe koond tulemansi rida
    fetch next c_pr_koondtulem into li_tulemnr,ls_tk1,ls_tk2,ls_tk3,ls_tk4;
    if sqlstate = err_notfound then -- kui eof
      leave pr_koondtulem_loop
    end if;
    set li_level=0;
    if length(trim(ls_tk1)) > 0 then
      set li_level=1;
      if length(trim(ls_tk2)) > 0 then
        set li_level=2;
        if length(trim(ls_tk3)) > 0 then
          set li_level=3;
          if length(trim(ls_tk4)) > 0 then
            set li_level=4
          end if
        end if
      end if
    end if;
    if li_level > 0 then
      case li_level
      when 1 then
        select sum(summa1),sum(summa2) into ldc_deeb,ldc_kree from gl_pr_koondtulem where gl_pr_koondtulem.tk1 = ls_tk1
      when 2 then
        select sum(summa1),sum(summa2) into ldc_deeb,ldc_kree from gl_pr_koondtulem where gl_pr_koondtulem.tk1 = ls_tk1 and gl_pr_koondtulem.tk2 = ls_tk2
      when 3 then
        select sum(summa1),sum(summa2) into ldc_deeb,ldc_kree from gl_pr_koondtulem where gl_pr_koondtulem.tk1 = ls_tk1 and gl_pr_koondtulem.tk2 = ls_tk2 and gl_pr_koondtulem.tk3 = ls_tk3
      when 4 then
        select sum(summa1),sum(summa2) into ldc_deeb,ldc_kree from gl_pr_koondtulem where gl_pr_koondtulem.tk1 = ls_tk1 and gl_pr_koondtulem.tk2 = ls_tk2 and gl_pr_koondtulem.tk3 = ls_tk3 and gl_pr_koondtulem.tk4 = ls_tk4
      end case
      ;
      if ldc_deeb is null then
        set ldc_deeb=0
      end if;
      if ldc_kree is null then
        set ldc_kree=0
      end if;
      update gl_pr_koondtulem set summa1kok = summa1kok+ldc_deeb-summa1,summa2kok = summa2kok+ldc_kree-summa2 where gl_pr_koondtulem.tulemnr = li_tulemnr
    end if
  end loop pr_koondtulem_loop;
  close c_pr_koondtulem;
  delete from gl_pr_koondtulem where gl_pr_koondtulem.summa1 = 0 and gl_pr_koondtulem.summa2 = 0 and gl_pr_koondtulem.summa1kok = 0 and gl_pr_koondtulem.summa2kok = 0;
  update gl_pr_koondtulem set tk2 = '' where tk2 is null;
  update gl_pr_koondtulem set tk3 = '' where tk3 is null;
  update gl_pr_koondtulem set tk4 = '' where tk4 is null;
  update gl_pr_koondtulem set eriline = '' where eriline is null
end