CREATE PROCEDURE "DBA"."dk_koond_seadtas"( in a_skoopkood char(16), in id_skoop integer,in id_obj integer,in id_paring integer,in peralg date,in perlop date, in id_dk_per smallint default 0,in gl_id_user integer default 32767 )
BEGIN
  declare ls_dk char(5000);
  declare ls_kp char(5000);
  declare ls_insert char(1000);
  declare ls_group char(1000);
  declare ls_dk_select char(1000);
  declare ls_dk_from char(1000);
  declare ls_dk_where char(1000);
  declare ls_kp_select char(1000);
  declare ls_kp_from char(1000);
  declare ls_kp_where char(1000);
  declare li_paring integer;
  declare li_saldod smallint;
  declare li_i smallint;
  declare li_level smallint;
  declare li_level1 smallint;
  declare li_level2 smallint;
  declare li_level3 smallint;
  declare ls_dk_kuup char(20);
  declare ls_dk_kontod char(5000);
  delete from gl_dk_koond;
  delete from gl_pr_koond;
  if id_obj > 0 then
    select dk_obj.skoop into id_skoop from dk_obj where dk_obj.objnr = id_obj
  end if;
  select tr_dk_koond1_saldod into li_saldod from t_user where id_user=gl_id_user;
  select tr_dk_koond1_saldod,tr_dk_koond_level1,tr_dk_koond_level2,tr_dk_koond_level3 into li_saldod,li_level1,li_level2,li_level3 from t_user where t_user.id_user=gl_id_user;
  set ls_dk_kontod=get_dk_kontod_koond(id_skoop,id_obj);
  case id_dk_per
  when 1 then
    set ls_dk_kuup='dk_kanne.arvekuup';
  when 2 then
    set ls_dk_kuup='dk_kanne.taskuup';
  else
    set ls_dk_kuup='dk_kanne.kuup';
  end case;
  set ls_insert= 'insert into gl_pr_koond( konto,asutnr,adeeb,akree,deeb,kree';
  set ls_dk_select=' select 1,dk_kanne.asutnr as kanne_asutnr,sum(if dk_kanne.sisse = 1 and ' + ls_dk_kuup + ' < peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif),sum(if dk_kanne.sisse = 0 and ' + ls_dk_kuup + ' < peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif),sum(if dk_kanne.sisse = 1 and ' + ls_dk_kuup + ' >= peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif),sum(if dk_kanne.sisse = 0 and ' + ls_dk_kuup + ' >= peralg then dk_kanne.summa+dk_kanne.summa2 else 0 endif)';
  set ls_dk_from=' from dk_kanne left outer join gl_1 on(dk_kanne.id = gl_1.id) and(dk_kanne.kuup = gl_1.kuup) key left outer join dk_obj left outer join skoobid on dk_obj.skoop=skoobid.skoop';
  set ls_dk_where=' where ((dk_kanne.id = gl_1.id) and(dk_kanne.kuup = gl_1.kuup) or id_paring = 0) and(dk_kanne.objnr = id_obj or id_obj = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and(dk_obj.skoop = id_skoop or id_skoop = 0) and dk_kanne.asutnr > 0 and ' + ls_dk_kuup + ' <= perlop';
  set ls_kp_select=' select 2,kp_kanne.asutnr as kanne_asutnr,sum(if kp_kanne.sisse = 0 and kp_kanne.kuup < peralg then kp_kanne.summa else 0 endif),sum(if kp_kanne.sisse = 1 and kp_kanne.kuup < peralg then kp_kanne.summa else 0 endif),sum(if kp_kanne.sisse = 0 and kp_kanne.kuup >= peralg then kp_kanne.summa else 0 endif),sum(if kp_kanne.sisse = 1 and kp_kanne.kuup >= peralg then kp_kanne.summa else 0 endif)';
  set ls_kp_from=' from kp_kanne left outer join gl_1 on(kp_kanne.id = gl_1.id) and(kp_kanne.kuup = gl_1.kuup) left outer join kp_obj on kp_kanne.objnr = kp_obj.objnr left outer join skoobid on kp_obj.skoop=skoobid.skoop';
  set ls_kp_where=' where ((kp_kanne.id = gl_1.id) and(kp_kanne.kuup = gl_1.kuup) or id_paring = 0) and locate(ls_dk_kontod,'';''+Left(kp_kanne.konto+Space(7),7)+'';'') > 0 and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and(kp_obj.skoop = id_skoop or id_skoop = 0) and kp_kanne.asutnr > 0 and kp_kanne.kuup <= perlop';
  set ls_group=' group by kanne_asutnr';
  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=ls_insert+',kulnr';
        set ls_dk_select=ls_dk_select+',if dk_kanne.kulnr is null then 0 else dk_kanne.kulnr endif as kanne_kulnr';
        set ls_kp_select=ls_kp_select+',if kp_kanne.kulnr is null then 0 else kp_kanne.kulnr endif as kanne_kulnr';
        set ls_group=ls_group+',kanne_kulnr'
      when 2 then
        set ls_insert=ls_insert+',talnr';
        set ls_dk_select=ls_dk_select+',if dk_kanne.talnr is null then 0 else dk_kanne.talnr endif as kanne_talnr';
        set ls_kp_select=ls_kp_select+',if kp_kanne.talnr is null then 0 else kp_kanne.talnr endif as kanne_talnr';
        set ls_group=ls_group+',kanne_talnr'
      when 3 then
        set ls_insert=ls_insert+',artnr';
        set ls_dk_select=ls_dk_select+',if dk_kanne.artnr is null then 0 else dk_kanne.artnr endif as kanne_artnr';
        set ls_kp_select=ls_kp_select+',if kp_kanne.artnr is null then 0 else kp_kanne.artnr endif as kanne_artnr';
        set ls_group=ls_group+',kanne_artnr'
      when 4 then
        set ls_insert=ls_insert+',tulnr';
        set ls_dk_select=ls_dk_select+',if dk_kanne.tulnr is null then 0 else dk_kanne.tulnr endif as kanne_tulnr';
        set ls_kp_select=ls_kp_select+',if kp_kanne.tulnr is null then 0 else kp_kanne.tulnr endif as kanne_tulnr';
        set ls_group=ls_group+',kanne_tulnr'
      when 5 then
        set ls_insert=ls_insert+',objectnr';
        set ls_dk_select=ls_dk_select+',if dk_kanne.objectnr is null then 0 else dk_kanne.objectnr endif as kanne_objectnr';
        set ls_kp_select=ls_kp_select+',if kp_kanne.objectnr is null then 0 else kp_kanne.objectnr endif as kanne_objectnr';
        set ls_group=ls_group+',kanne_objectnr'
      when 6 then
        set ls_insert=ls_insert+',osaknr';
        set ls_dk_select=ls_dk_select+',if dk_kanne.osaknr is null then 0 else dk_kanne.osaknr endif as kanne_osaknr';
        set ls_kp_select=ls_kp_select+',if kp_kanne.osaknr is null then 0 else kp_kanne.osaknr endif as kanne_osaknr';
        set ls_group=ls_group+',kanne_osaknr'
      when 7 then
        set ls_insert=ls_insert+',subjectnr';
        set ls_dk_select=ls_dk_select+',if dk_kanne.subjectnr is null then 0 else dk_kanne.subjectnr endif as kanne_subjectnr';
        set ls_kp_select=ls_kp_select+',if kp_kanne.subjectnr is null then 0 else kp_kanne.subjectnr endif as kanne_subjectnr';
        set ls_group=ls_group+',kanne_subjectnr'
      when 8 then
        set ls_insert=ls_insert+',suundnr';
        set ls_dk_select=ls_dk_select+',if dk_kanne.suundnr is null then 0 else dk_kanne.suundnr endif as kanne_suundnr';
        set ls_kp_select=ls_kp_select+',if kp_kanne.suundnr is null then 0 else kp_kanne.suundnr endif as kanne_suundnr';
        set ls_group=ls_group+',kanne_suundnr'
      when 9 then
        set ls_insert=ls_insert+',meetnr';
        set ls_dk_select=ls_dk_select+',if dk_kanne.meetnr is null then 0 else dk_kanne.meetnr endif as kanne_meetnr';
        set ls_kp_select=ls_kp_select+',if kp_kanne.meetnr is null then 0 else kp_kanne.meetnr endif as kanne_meetnr';
        set ls_group=ls_group+',kanne_meetnr'
      when 10 then
        set ls_insert=ls_insert+',taotnr';
        set ls_dk_select=ls_dk_select+',if dk_kanne.taotnr is null then 0 else dk_kanne.taotnr endif as kanne_taotnr';
        set ls_kp_select=ls_kp_select+',if kp_kanne.taotnr is null then 0 else kp_kanne.taotnr endif as kanne_taotnr';
        set ls_group=ls_group+',kanne_taotnr'
      end case;
    else
      leave tase_loop;
    end if;
    set li_i=li_i+1
  end loop;
  set ls_insert=ls_insert+')';
  -- arveldused
  set li_paring=set_gl_1(id_paring);
  set ls_dk=ls_insert + ls_dk_select + ls_dk_from + ls_dk_where + ls_group;
  execute immediate ls_dk;
  -- kassa/pank
  set li_paring=set_gl_1_conv_dk_kp(id_paring);
  set ls_kp=ls_insert + ls_kp_select + ls_kp_from + ls_kp_where + ls_group;
  execute immediate ls_kp;
  -- koond
  insert into gl_dk_koond( asutnr,adeeb,akree,deeb,kree,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,meetnr,taotnr) 
    select gl_pr_koond.asutnr,sum(gl_pr_koond.adeeb),sum(gl_pr_koond.akree),sum(gl_pr_koond.deeb),sum(gl_pr_koond.kree),kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,meetnr,taotnr from
      gl_pr_koond group by gl_pr_koond.asutnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,meetnr,taotnr;
  -- loppjaagid
  update gl_dk_koond set adeeb = adeeb-akree,akree = akree-adeeb;
  update gl_dk_koond set adeeb = 0 where gl_dk_koond.adeeb < 0;
  update gl_dk_koond set akree = 0 where gl_dk_koond.akree < 0;
  update gl_dk_koond set ldeeb = adeeb-akree+deeb-kree,lkree = akree-adeeb+kree-deeb;
  update gl_dk_koond set ldeeb = 0 where gl_dk_koond.ldeeb < 0;
  update gl_dk_koond set lkree = 0 where gl_dk_koond.lkree < 0;
  delete from gl_dk_koond where adeeb = 0 and akree = 0 and deeb = 0 and kree = 0;
  -- kustuta jäägid, kus asutus/isik on li_saldod
  if li_saldod=1 then
    delete from gl_pr_koond;
    insert into gl_pr_koond( asutnr,deeb)
        select gl_dk_koond.asutnr,sum(gl_dk_koond.ldeeb-gl_dk_koond.lkree)
         from gl_dk_koond
         group by gl_dk_koond.asutnr;
    delete gl_dk_koond from gl_dk_koond left outer join gl_pr_koond on gl_dk_koond.asutnr=gl_pr_koond.asutnr where gl_pr_koond.deeb=0;
  end if;
END