CREATE PROCEDURE "DBA"."pr_koond_seadtas"( in a_skoopkood char(16),in a_skoop integer,in id_paring integer,in peralg date,in perlop date,in gl_id_user integer default 32767)
BEGIN
  declare ls_sql char(2000);
  declare ls_insert char(1000);
  declare ls_select char(1000);
  declare ls_from char(1000);
  declare ls_where char(1000);
  declare ls_group char(1000);
  declare ls_insert_level char(1000);
  declare ls_select_level char(1000);
  declare ls_group_level char(1000);
  declare ls_select_level_d char(1000);
  declare ls_select_level_k char(1000);
  declare ls_deebkree char(4);
  declare li_paring integer;
  declare li_i smallint;
  declare li_j smallint;
  declare li_level smallint;
  declare li_level1 smallint;
  declare li_level2 smallint;
  declare li_level3 smallint;
  declare ls_saldod_konto char(7);
  declare li_saldod smallint;
  declare li_saldod_9 smallint;
  delete from gl_dk_koond;
  delete from gl_pr_koond;
  select pr_konfm.yld_saldod,pr_konfm.yld_saldod_9,pr_konfm.yld_saldod_konto into li_saldod,li_saldod_9,ls_saldod_konto from pr_konfm where pr_konfm.id = 1;
  select tr_pr_koond_level1,tr_pr_koond_level2,tr_pr_koond_level3 into li_level1,li_level2,li_level3 from t_user where t_user.id_user=gl_id_user;
  set li_paring=set_gl_1(id_paring);
  set ls_group=' group by kanne_konto';
  set ls_insert_level = '';
  set ls_select_level_d = '';
  set ls_select_level_k = '';
  set li_i=1;
  tase_loop: while li_i < 4 loop
    case li_i
    when 1 then
      set li_level=li_level1
    when 2 then
      set li_level=li_level2
    when 3 then
      set li_level=li_level3
    end case;
    if li_level > 0 then
      case li_level
      when 1 then
        set ls_insert_level=ls_insert_level+',kulnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.kulnr is null or kontlist.djuhkul is null or kontlist.djuhkul = 0 then 0 else pr_kanne.kulnr endif as kanne_kulnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.kulnr is null or kontlist.kjuhkul is null or kontlist.kjuhkul = 0 then 0 else pr_kanne.kulnr endif as kanne_kulnr';
        set ls_group=ls_group+',kanne_kulnr'
      when 2 then
        set ls_insert_level=ls_insert_level+',talnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.kulnr is null or kontlist.djuhtal is null or kontlist.djuhtal = 0 then 0 else pr_kanne.talnr endif as kanne_talnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.kulnr is null or kontlist.kjuhtal is null or kontlist.kjuhtal = 0 then 0 else pr_kanne.talnr endif as kanne_talnr';
        set ls_group=ls_group+',kanne_talnr'
      when 3 then
        set ls_insert_level=ls_insert_level+',artnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.kulnr is null or kontlist.djuhart is null or kontlist.djuhart = 0 then 0 else pr_kanne.artnr endif as kanne_artnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.kulnr is null or kontlist.kjuhart is null or kontlist.kjuhart = 0 then 0 else pr_kanne.artnr endif as kanne_artnr';
        set ls_group=ls_group+',kanne_artnr'
      when 4 then
        set ls_insert_level=ls_insert_level+',tulnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.kulnr is null or kontlist.djuhtul is null or kontlist.djuhtul = 0 then 0 else pr_kanne.tulnr endif as kanne_tulnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.kulnr is null or kontlist.kjuhtul is null or kontlist.kjuhtul = 0 then 0 else pr_kanne.tulnr endif as kanne_tulnr';
        set ls_group=ls_group+',kanne_tulnr'
      when 5 then
        set ls_insert_level=ls_insert_level+',objectnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.kulnr is null or kontlist.djuhobject is null or kontlist.djuhobject = 0 then 0 else pr_kanne.objectnr endif as kanne_objectnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.kulnr is null or kontlist.kjuhobject is null or kontlist.kjuhobject = 0 then 0 else pr_kanne.objectnr endif as kanne_objectnr';
        set ls_group=ls_group+',kanne_objectnr'
      when 6 then
        set ls_insert_level=ls_insert_level+',osaknr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.kulnr is null or kontlist.djuhosak is null or kontlist.djuhosak = 0 then 0 else pr_kanne.osaknr endif as kanne_osaknr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.kulnr is null or kontlist.kjuhosak is null or kontlist.kjuhosak = 0 then 0 else pr_kanne.osaknr endif as kanne_osaknr';
        set ls_group=ls_group+',kanne_osaknr'
      when 7 then
        set ls_insert_level=ls_insert_level+',subjectnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.kulnr is null or kontlist.djuhsubject is null or kontlist.djuhsubject = 0 then 0 else pr_kanne.subjectnr endif as kanne_subjectnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.kulnr is null or kontlist.kjuhsubject is null or kontlist.kjuhsubject = 0 then 0 else pr_kanne.subjectnr endif as kanne_subjectnr';
        set ls_group=ls_group+',kanne_subjectnr'
      when 8 then
        set ls_insert_level=ls_insert_level+',suundnr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.kulnr is null or kontlist.djuhsuund is null or kontlist.djuhsuund = 0 then 0 else pr_kanne.suundnr endif as kanne_suundnr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.kulnr is null or kontlist.kjuhsuund is null or kontlist.kjuhsuund = 0 then 0 else pr_kanne.suundnr endif as kanne_suundnr';
        set ls_group=ls_group+',kanne_suundnr'
      when 9 then
        set ls_insert_level=ls_insert_level+',alliknr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.kulnr is null or kontlist.djuhallik is null or kontlist.djuhallik = 0 then 0 else pr_kanne.alliknr endif as kanne_alliknr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.kulnr is null or kontlist.kjuhallik is null or kontlist.kjuhallik = 0 then 0 else pr_kanne.alliknr endif as kanne_alliknr';
        set ls_group=ls_group+',kanne_alliknr'
      when 10 then
        set ls_insert_level=ls_insert_level+',ravonr';
        set ls_select_level_d=ls_select_level_d+',if pr_kanne.kulnr is null or kontlist.djuhravo is null or kontlist.djuhravo = 0 then 0 else pr_kanne.ravonr endif as kanne_ravonr';
        set ls_select_level_k=ls_select_level_k+',if pr_kanne.kulnr is null or kontlist.kjuhravo is null or kontlist.kjuhravo = 0 then 0 else pr_kanne.ravonr endif as kanne_ravonr';
        set ls_group=ls_group+',kanne_ravonr'
      when 11 then
        set ls_insert_level=ls_insert_level+',partnr';
        set ls_select_level_d=ls_select_level_d+',if kontlist.djuhpart is null or kontlist.djuhpart = 0 then 0 else (if kontlist.partnr is null then(if pr_kanne.partnr is null then 0 else pr_kanne.partnr endif) else kontlist.partnr endif) endif as kanne_partnr';
        set ls_select_level_k=ls_select_level_k+',if kontlist.kjuhpart is null or kontlist.kjuhpart = 0 then 0 else (if kontlist.partnr is null then(if pr_kanne.partnr2 is null then 0 else pr_kanne.partnr2 endif) else kontlist.partnr endif) endif as kanne_partnr';
        set ls_group=ls_group+',kanne_partnr'
      end case;
    else
      leave tase_loop;
    end if;
    set li_i=li_i+1
  end loop;
  set ls_insert_level=ls_insert_level+')';
  set li_i=1;
  deebkree_loop: while li_i < 3 loop
    case li_i
    when 1 then
      set ls_deebkree='deeb';
      set ls_select_level=ls_select_level_d
    when 2 then
      set ls_deebkree='kree';
      set ls_select_level=ls_select_level_k
    end case;
      set li_j=1;
      per_loop: while li_j < 4 loop
        case li_j
        when 1 then -- aasta alguseni
          set ls_insert='insert into gl_dk_koond( konto,a' + ls_deebkree + ls_insert_level;
          set ls_select=' select if li_saldod = 1 and Length(ls_saldod_konto)>0 and locate(''345678'',"left"(pr_kanne.'+ls_deebkree+',1)) > 0 then ls_saldod_konto else trim(pr_kanne.'+ls_deebkree+') endif as kanne_konto,sum(pr_kanne.summa)' + ls_select_level;
          set ls_from=' from pr_kanne left outer join gl_1 on(pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) left outer join skoobid on pr_kanne.skoop=skoobid.skoop left outer join kontlist on pr_kanne.deeb = kontlist.konto';
          set ls_where=' where ((pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) or id_paring = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and(pr_kanne.skoop=a_skoop or a_skoop=0) and pr_kanne.'+ls_deebkree+' is not null and years(pr_kanne.kuup) < years(peralg) and not(li_saldod_9 = 1 and "left"(pr_kanne.'+ls_deebkree+',1) = ''9'')';
        when 2 then -- perioodi alguseni
          set ls_insert='insert into gl_dk_koond( konto,a' + ls_deebkree + ls_insert_level;
          set ls_select=' select pr_kanne.' + ls_deebkree + ' as kanne_konto,sum(pr_kanne.summa)' + ls_select_level;
          set ls_from=' from pr_kanne left outer join gl_1 on(pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) left outer join skoobid on pr_kanne.skoop=skoobid.skoop left outer join kontlist on pr_kanne.deeb = kontlist.konto';
          set ls_where=' where ((pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) or id_paring = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and(pr_kanne.skoop=a_skoop or a_skoop=0) and pr_kanne.'+ls_deebkree+' is not null and pr_kanne.kuup >= peralg and pr_kanne.kuup < peralg and years(pr_kanne.kuup) = years(peralg)';
        when 3 then -- perioodi lõpuni
          set ls_insert='insert into gl_dk_koond( konto,' + ls_deebkree + ls_insert_level;
          set ls_select=' select pr_kanne.' + ls_deebkree + ' as kanne_konto,sum(pr_kanne.summa)' + ls_select_level;
          set ls_from=' from pr_kanne left outer join gl_1 on(pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) left outer join skoobid on pr_kanne.skoop=skoobid.skoop left outer join kontlist on pr_kanne.deeb = kontlist.konto';
          set ls_where=' where ((pr_kanne.id = gl_1.id) and(pr_kanne.kuup = gl_1.kuup) or id_paring = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and(pr_kanne.skoop=a_skoop or a_skoop=0) and pr_kanne.'+ls_deebkree+' is not null and pr_kanne.kuup >= peralg and pr_kanne.kuup <= perlop';
        end case;
        set ls_sql=ls_insert + ls_select + ls_from + ls_where + ls_group;
        execute immediate ls_sql;
        set li_j=li_j+1
      end loop;
    set li_i=li_i+1
  end loop;
  if length(ls_saldod_konto) > 0 then
    update gl_dk_koond set kulnr=0,talnr=0,artnr=0,tulnr=0,objectnr=0,osaknr=0,subjectnr=0,suundnr=0,taotnr=0,meetnr=0,alliknr=0,ravonr=0,partnr=0 where konto=ls_saldod_konto
  end if;
  -- koond
  insert into gl_pr_koond( konto,adeeb,akree,deeb,kree,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,meetnr,taotnr) 
    select gl_dk_koond.konto,sum(gl_dk_koond.adeeb),sum(gl_dk_koond.akree),sum(gl_dk_koond.deeb),sum(gl_dk_koond.kree),kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,meetnr,taotnr from
      gl_dk_koond group by gl_dk_koond.konto,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,meetnr,taotnr;
  -- loppjaagid
  update gl_pr_koond set ldeeb = adeeb-akree+deeb-kree,lkree = akree-adeeb+kree-deeb,adeeb = adeeb-akree,akree = akree-adeeb,sdeeb = deeb-kree,skree = kree-deeb;
  update gl_pr_koond set adeeb = 0 where gl_pr_koond.adeeb < 0;
  update gl_pr_koond set akree = 0 where gl_pr_koond.akree < 0;
  update gl_pr_koond set ldeeb = 0 where gl_pr_koond.ldeeb < 0;
  update gl_pr_koond set lkree = 0 where gl_pr_koond.lkree < 0;
  update gl_pr_koond set sdeeb = 0 where gl_pr_koond.sdeeb < 0;
  update gl_pr_koond set skree = 0 where gl_pr_koond.skree < 0;
  delete from gl_pr_koond where adeeb = 0 and akree = 0 and deeb = 0 and kree = 0
END