ALTER PROCEDURE "DBA"."mt_arvuta"(in a_tootnr integer,in a_yksusnr integer,in a_kuup date)
begin
  declare li_id integer;
  declare li_aasta integer;
  declare li_sisse smallint;
  declare ls_const char(1);
  declare ldc_kogus numeric(15,3);
  declare ldc_summa numeric(15,2);
  declare li_id_stakk integer;
  declare li_gl_id integer;
  declare ldc_gl_kogus numeric(15,3);
  declare ldc_gl_summa numeric(15,2);
  declare ldc_mlsumma numeric(15,2);
  declare ldc_myhind numeric(30,6);
  declare ldc_protsent numeric(15,3);
  declare ld_peralg date;
  declare ld_perlop date;
  declare li_euroforeve smallint;
  declare err_notfound exception for sqlstate value '02000';
  declare c_mt_kanne dynamic scroll cursor for select mt_kanne.id,mt_kanne.aasta,mt_kanne.sisse,mt_kanne.const,mt_kanne.kogus,mt_kanne.summa+mt_kanne.summa2,if kblist.protsent is null then 0 else kblist.protsent endif from mt_kanne left outer join kblist on mt_kanne.kbnr = kblist.kbnr where mt_kanne.yksusnr = a_yksusnr and mt_kanne.tootnr = a_tootnr order by kuup asc,kell asc,sisse desc;
  declare c_gl_stakk dynamic scroll cursor for select gl_stakk.id,gl_stakk.kogus,gl_stakk.summa from gl_stakk order by gl_stakk.id asc;
  declare c_mt_kanne_kesk dynamic scroll cursor for select mt_kanne.id,mt_kanne.aasta,mt_kanne.kogus,if kblist.protsent is null then 0 else kblist.protsent endif from mt_kanne left outer join kblist on mt_kanne.kbnr = kblist.kbnr where mt_kanne.yksusnr = a_yksusnr and mt_kanne.tootnr = a_tootnr and mt_kanne.sisse = 0 and mt_kanne.const = 'K' and mt_kanne.kuup >= ld_peralg and mt_kanne.kuup <= ld_perlop;
  delete from gl_stakk;
  set li_id_stakk=0;
  select euroforeve_mt into li_euroforeve from tm_rmpt where "id"=1;
  if li_euroforeve=0 or a_kuup>ymd(2010,12,31) then
    set ld_peralg=get_peralg(a_kuup);
    set ld_perlop=get_perlop(a_kuup);
    if exists(select 1 from mt_kanne where mt_kanne.yksusnr = a_yksusnr and mt_kanne.tootnr = a_tootnr and mt_kanne.const = 'E') then
      open c_mt_kanne;
      mt_kanne_loop: loop
        --  loe kanne
        fetch next c_mt_kanne into li_id,li_aasta,li_sisse,ls_const,ldc_kogus,ldc_summa,ldc_protsent;
        if sqlstate = err_notfound then -- kui eof
          leave mt_kanne_loop
        end if;
        if li_sisse = 1 then
          -- sissetulek lisatakse stakki
          set li_id_stakk=li_id_stakk+1;
          insert into gl_stakk( id,kogus,summa) values( li_id_stakk,ldc_kogus,ldc_summa) 
        else
          if ls_const = 'E' then
            -- valjalainud kogus ja summa lahutatakse stakist maha lahutamine
            set ldc_mlsumma=0;
          open c_gl_stakk;
          stakk1_loop: loop
            fetch next c_gl_stakk into li_gl_id,ldc_gl_kogus,ldc_gl_summa;
            if sqlstate = err_notfound then -- kui eof
              leave stakk1_loop
            end if;
            if ldc_gl_kogus > 0 or ldc_gl_summa > 0 then
              if ldc_kogus < ldc_gl_kogus then
                if ldc_gl_kogus = 0 then
                  set ldc_summa=0
                else
                  set ldc_summa=Round(ldc_kogus*ldc_gl_summa/ldc_gl_kogus,2)
                end if;
                update gl_stakk set gl_stakk.kogus = gl_stakk.kogus-ldc_kogus,gl_stakk.summa = gl_stakk.summa-ldc_summa where gl_stakk.id = li_gl_id;
                set ldc_mlsumma=ldc_mlsumma+ldc_summa;
                leave stakk1_loop
              else
                set ldc_kogus=ldc_kogus-ldc_gl_kogus;
                set ldc_mlsumma=ldc_mlsumma+ldc_gl_summa;
                update gl_stakk set gl_stakk.kogus = 0,gl_stakk.summa = 0 where gl_stakk.id = li_gl_id;
                if ldc_kogus = 0 then
                  leave stakk1_loop
                end if
              end if
            end if
          end loop stakk1_loop;
          close c_gl_stakk;
          if ldc_protsent = 0 then
            update mt_kanne set mt_kanne.summa = ldc_mlsumma,mt_kanne.summa2 =0 where mt_kanne.id = li_id and mt_kanne.aasta = li_aasta
          else
            update mt_kanne set mt_kanne.summa = ldc_mlsumma-Round(ldc_gl_summa*ldc_protsent/(100+ldc_protsent),2),mt_kanne.summa2 = Round(ldc_gl_summa*ldc_protsent/(100+ldc_protsent),2) where mt_kanne.id = li_id and mt_kanne.aasta = li_aasta
          end if
        else
          if ls_const = 'S' then
            -- summana valjalainud koguse ja summa stakist maha lahutamine
            open c_gl_stakk;
            stakk2_loop: loop
              fetch next c_gl_stakk into li_gl_id,ldc_gl_kogus,ldc_gl_summa;
              if sqlstate = err_notfound then -- kui eof
                leave stakk2_loop
              end if;
              if ldc_gl_kogus > 0 then
                if ldc_kogus < ldc_gl_kogus then
                  update gl_stakk set gl_stakk.kogus = gl_stakk.kogus-ldc_kogus where gl_stakk.id = li_gl_id;
                  set ldc_kogus=0;
                  leave stakk2_loop
                else
                  set ldc_kogus=ldc_kogus-ldc_gl_kogus;
                  update gl_stakk set gl_stakk.kogus = 0 where gl_stakk.id = li_gl_id;
                  if ldc_kogus = 0 then
                    leave stakk2_loop
                  end if
                end if
              end if
            end loop stakk2_loop;
            close c_gl_stakk;
            open c_gl_stakk;
            stakk3_loop: loop
              fetch next c_gl_stakk into li_gl_id,ldc_gl_kogus,ldc_gl_summa;
              if sqlstate = err_notfound then -- kui eof
                leave stakk3_loop
              end if;
              if ldc_gl_summa > 0 then
                if ldc_summa < ldc_gl_summa then
                  update gl_stakk set gl_stakk.summa = gl_stakk.summa-ldc_summa where gl_stakk.id = li_gl_id;
                  set ldc_summa=0;
                  leave stakk3_loop
                else
                  set ldc_summa=ldc_summa-ldc_gl_summa;
                  update gl_stakk set gl_stakk.summa = 0 where gl_stakk.id = li_gl_id;
                  if ldc_summa = 0 then
                    leave stakk3_loop
                  end if
                end if
              end if
            end loop stakk3_loop;
            close c_gl_stakk
          end if
        end if
      end if
    end loop mt_kanne_loop;
    close c_mt_kanne
  end if;
  set ldc_kogus=0;
  set ldc_summa=0;
  set ldc_gl_kogus=0;
  set ldc_gl_summa=0;
  select sum(if mt_kanne.sisse = 1 then mt_kanne.kogus else-1*mt_kanne.kogus endif),sum(if mt_kanne.sisse = 1 then mt_kanne.summa+mt_kanne.summa2 else-1*(mt_kanne.summa+mt_kanne.summa2) endif) into ldc_gl_kogus,ldc_gl_summa from mt_kanne where mt_kanne.yksusnr = a_yksusnr and mt_kanne.tootnr = a_tootnr and mt_kanne.kuup < ld_peralg;
  if ldc_gl_kogus is not null then
    set ldc_kogus=ldc_gl_kogus
  end if;
  if ldc_gl_summa is not null then
    set ldc_summa=ldc_gl_summa
  end if;
  kuud_loop: loop
    if not exists(select 1 from mt_kanne where mt_kanne.yksusnr = a_yksusnr and mt_kanne.tootnr = a_tootnr and mt_kanne.kuup >= ld_peralg) then
      leave kuud_loop
    end if;
    select sum(if mt_kanne.sisse = 1 then mt_kanne.kogus else if mt_kanne.const = 'S' or mt_kanne.const = 'E' then-1*mt_kanne.kogus endif endif),sum(if mt_kanne.sisse = 1 then mt_kanne.summa+mt_kanne.summa2 else if mt_kanne.const = 'S' or mt_kanne.const = 'E' then-1*(mt_kanne.summa+mt_kanne.summa2) endif endif) into ldc_gl_kogus,ldc_gl_summa from mt_kanne where mt_kanne.yksusnr = a_yksusnr and mt_kanne.tootnr = a_tootnr and mt_kanne.kuup >= ld_peralg and mt_kanne.kuup <= ld_perlop;
    if ldc_gl_kogus is not null then
      set ldc_kogus=ldc_kogus+ldc_gl_kogus
    end if;
    if ldc_gl_summa is not null then
      set ldc_summa=ldc_summa+ldc_gl_summa
    end if;
    if ldc_kogus = 0 then
      set ldc_myhind=0
    else
      set ldc_myhind=ldc_summa/ldc_kogus
    end if;
    open c_mt_kanne_kesk;
    mt_kanne_kesk_loop: loop
      --  loe kanne
        fetch next c_mt_kanne_kesk into li_id,li_aasta,ldc_gl_kogus,ldc_protsent;
        if sqlstate = err_notfound then -- kui eof
          leave mt_kanne_kesk_loop
        end if;
        set ldc_kogus=ldc_kogus-ldc_gl_kogus;
        if ldc_kogus = 0 then
          set ldc_gl_summa=ldc_summa
        else
          set ldc_gl_summa=Round(ldc_gl_kogus*ldc_myhind,2)
        end if;
        if ldc_protsent=0 then
          update mt_kanne set mt_kanne.summa = ldc_gl_summa,mt_kanne.hind = ldc_myhind,mt_kanne.summa2 = 0 where mt_kanne.id = li_id and mt_kanne.aasta = li_aasta
        else
          update mt_kanne set mt_kanne.summa = ldc_gl_summa-Round(ldc_gl_summa*ldc_protsent/(100+ldc_protsent),2),mt_kanne.hind = ldc_myhind,mt_kanne.summa2 = Round(ldc_gl_summa*ldc_protsent/(100+ldc_protsent),2) where mt_kanne.id = li_id and mt_kanne.aasta = li_aasta
        end if;
        set ldc_summa=ldc_summa-ldc_gl_summa
      end loop mt_kanne_kesk_loop;
      close c_mt_kanne_kesk;
      set ld_peralg=DAYS(ld_perlop,1);
      set ld_perlop=get_perlop(ld_peralg);
    end loop kuud_loop
  end if
end