ALTER PROCEDURE "DBA"."pr_koondtulem_pikk"()
BEGIN
  declare li_id integer;
  declare ls_konto char(7);
  declare li_pikkus integer;
  declare ls_nimi char(100);
  declare ldc_summa numeric(15,2);
  declare ldc_summa2 numeric(15,2);
  declare ldc_tulud numeric(15,2);
  declare ldc_kulud numeric(15,2);
  declare ldc_fin numeric(15,2);
  declare ldc_tulud2 numeric(15,2);
  declare ldc_kulud2 numeric(15,2);
  declare ldc_fin2 numeric(15,2);
  declare err_notfound exception for sqlstate value '02000';
  declare c_kontlistkir dynamic scroll cursor for select "kontlist"."konto","kontlist"."nimi" from kontlist where left(konto,1) in ('3','4','5','6','7') order by konto asc;
  delete from gl_pr_saldoandmik;
  delete from gl_tr_tabel;
  set ldc_tulud=0;
  set ldc_kulud=0;
  set ldc_fin=0;
  set ldc_tulud2=0;
  set ldc_kulud2=0;
  set ldc_fin2=0;
  -- kahest tabelist kokku
  insert into gl_pr_saldoandmik( konto,part,osa,allik,ravo,deebet,kreedit,deebet2,kreedit2)
    select Left(gl_dk_koond.konto,6) as ab_konto,
           '1','','','',
           sum(gl_dk_koond.adeeb-gl_dk_koond.akree+gl_dk_koond.deeb-gl_dk_koond.kree),
           sum(gl_dk_koond.akree-gl_dk_koond.adeeb+gl_dk_koond.kree-gl_dk_koond.deeb),
           0,
           0
    from gl_dk_koond group by ab_konto;
  insert into gl_pr_saldoandmik( konto,part,osa,allik,ravo,deebet,kreedit,deebet2,kreedit2)
    select Left(gl_pr_koond.konto,6) as ab_konto,
           '2','','','',
           0,0,
           sum(gl_pr_koond.deeb-gl_pr_koond.kree),
           sum(gl_pr_koond.kree-gl_pr_koond.deeb)
    from gl_pr_koond group by ab_konto;

  update gl_pr_saldoandmik set deebet = 0 where gl_pr_saldoandmik.deebet < 0;
  update gl_pr_saldoandmik set kreedit = 0 where gl_pr_saldoandmik.kreedit < 0;
  update gl_pr_saldoandmik set deebet2 = 0 where gl_pr_saldoandmik.deebet2 < 0;
  update gl_pr_saldoandmik set kreedit2 = 0 where gl_pr_saldoandmik.kreedit2 < 0;
  delete from gl_pr_saldoandmik where deebet=0 and kreedit=0 and deebet2=0 and kreedit2=0;
  -- tsykkel yle kontode
  open c_kontlistkir;
  kontlistkir_loop: loop
    fetch next c_kontlistkir into ls_konto,ls_nimi;
    if sqlstate = err_notfound then -- kui eof
      leave kontlistkir_loop
    end if;
    set li_pikkus=Length(ls_konto);
    if li_pikkus>0 then
      set ldc_summa=0;
      set li_id=1;
      select sum(kreedit-deebet),sum(kreedit2-deebet2) into ldc_summa,ldc_summa2 from gl_pr_saldoandmik where left(gl_pr_saldoandmik.konto,li_pikkus)=ls_konto;
      if ldc_summa is null then
        set ldc_summa=0
      end if;
      if ldc_summa2 is null then
        set ldc_summa2=0
      end if;
      if ldc_summa<>0 or ldc_summa2<>0 then
        if left(ls_konto,1)='3' then
          set li_id=30;
          if li_pikkus=6 then
            set ldc_tulud=ldc_tulud+ldc_summa;
            set ldc_tulud2=ldc_tulud2+ldc_summa2;
          end if;
        else
          if Left(ls_konto,1)='7' then
            set li_id=70;
          else
            if Left(ls_konto,2) in ('65','68','69') then
              set li_id=65;
              if li_pikkus=6 then
                set ldc_fin=ldc_fin+ldc_summa;
                set ldc_fin2=ldc_fin2+ldc_summa2;
              end if;
            else
              set li_id=41;
              if li_pikkus=6 then
                set ldc_kulud=ldc_kulud+ldc_summa;
                set ldc_kulud2=ldc_kulud2+ldc_summa2;
              end if;
            end if;
          end if;
        end if;
        insert into gl_tr_tabel("id",kood,nimi,adeeb,ldeeb) values(li_id,ls_konto,ls_nimi,ldc_summa,ldc_summa2)
      end if
    end if;
  end loop kontlistkir_loop;
  close c_kontlistkir;
  if ldc_kulud is not null then
    insert into gl_tr_tabel("id",kood,nimi,adeeb,ldeeb) values(40,'*','TEGEVUSKULUD',ldc_kulud,ldc_kulud2)
  end if;
  if ldc_kulud is not null and ldc_tulud is not null then
    insert into gl_tr_tabel("id",kood,nimi,adeeb,ldeeb) values(50,'*','TEGEVUSTULEM',ldc_tulud+ldc_kulud,ldc_tulud2+ldc_kulud2)
  end if;
  if ldc_kulud is not null and ldc_tulud is not null and ldc_fin is not null then
    insert into gl_tr_tabel("id",kood,nimi,adeeb,ldeeb) values(69,'*','TULEM',ldc_tulud+ldc_kulud+ldc_fin,ldc_tulud2+ldc_kulud2+ldc_fin2)
  end if;
 END