ALTER PROCEDURE "dba"."pa_tsd2015"(in a_t_akuu numeric(2),in a_t_aasta numeric(4),in a_skoop integer, in a_skoopkood char(16) default '')
begin
  declare ldc_sots_maar numeric(10,3);
  declare ldc_sots_protsent numeric(10,3);
  declare ldc_tulu_protsent numeric(10,3);
  declare ls_vmkood char(3);
  declare ls_mvaba_vmkood char(3);
  declare ls_mtm_vmkood char(3);
  declare ll_lepnr integer;
  declare ll_asutnr integer;
  declare ld_peralg date;
  declare ld_perlop date;
  declare ld_kuup1 date;
  declare ld_kuup2 date;
  declare ldc_koht numeric(6,3);
  declare ldc_mvaba decimal(15,2);
  declare ldc_mtm decimal(15,2);
  declare ldc_msm decimal(15,2);
  declare ldc_kok_mvaba decimal(15,2);
  declare ldc_kok_mtm decimal(15,2);
  declare ldc_kok_msm decimal(15,2);
  declare ldc_mtmak decimal(15,2);
  declare ldc_msmak decimal(15,2);
  declare err_notfound exception for sqlstate value '02000';
  declare c_pa_leping dynamic scroll cursor for
    select distinct pa_leping.lepnr
      from pa_leping left outer join pa_isik on pa_leping.lepnr=pa_isik.lepnr
                     left outer join pa_yksus on pa_isik.yksusnr=pa_yksus.yksusnr
                     left outer join skoobid on pa_yksus.skoop=skoobid.skoop
                     left outer join pa_kaader on pa_leping.asutnr=pa_kaader.asutnr
      where (pa_yksus.skoop=a_skoop or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and
             pa_leping.kuup1<=ld_perlop and (pa_leping.kuup2>=ld_peralg or pa_leping.kuup2 is null) and
            (pa_leping.id_koht=1 or pa_leping.id_koht=4) and
            (pa_kaader.mres is null or pa_kaader.mres=0);
  declare c_stakk dynamic scroll cursor for
    select gl_stakk."id",gl_stakk.kogus,gl_stakk.summa
      from gl_stakk;
  declare c_tsd dynamic scroll cursor for
    select gl_tsd.vmkood,gl_tsd.mtmak-gl_tsd.mpens-gl_tsd.mtvtk-gl_tsd.mvaba_630
      from gl_tsd
      where gl_tsd.asutnr=ll_asutnr
      order by vmkood;
  declare c_tsd_sots dynamic scroll cursor for
    select gl_tsd.vmkood,gl_tsd.msmak
      from gl_tsd
      where gl_tsd.asutnr=ll_asutnr
      order by vmkood;
  set ld_peralg=ymd(a_t_aasta,a_t_akuu,1);
  set ld_perlop=get_perlop(ld_peralg);
  if Length(a_skoopkood)=0 and a_skoop>0 then
    select if skoobid.kood is null then '' else skoobid.kood endif into a_skoopkood from skoobid where skoobid.skoop=a_skoop
  end if;
  delete from gl_tsd;
  insert into gl_tsd(mres,osa,asutnr,vmkood,summa,msmak,msm,mpens,mtkmak,mtvtk,mtatk,mtmak,mvaba,mvaba_630,mtm,koht,tmkood)
    select if pa_kaader.mres=0 or pa_kaader.mres is null then 0 else 1 endif as pa_kaader_mres,
           'a',pa_leping.asutnr,pa_taslist.vmkood,
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.summa),
           sum(if pa_taslist.smrk=1 then ((-2)*pa_taslist.sisse+1)*pa_tasu.summa else 0 endif),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.sotsmaks),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.pensmaks),
           sum(if pa_taslist.tk=1 then ((-2)*pa_taslist.sisse+1)*pa_tasu.summa else 0 endif),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.tvtkmaks),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.tatkmaks),
           sum(if pa_taslist.tulu=1 then ((-2)*pa_taslist.sisse+1)*pa_tasu.summa else 0 endif),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.maksuvaba),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.maksuvaba_630),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.tulumaks),
           0,
           if get_penskar(pa_leping.asutnr,a_t_aasta)=1 then '650' else '610' endif
      from pa_tasu left outer join pa_yksus on pa_tasu.yksusnr=pa_yksus.yksusnr left outer join skoobid on pa_yksus.skoop=skoobid.skoop left outer join pa_taslist on pa_tasu.tas_s=pa_taslist.tas_s left outer join pa_leping on pa_tasu.lepnr=pa_leping.lepnr left outer join pa_kaader on pa_leping.asutnr=pa_kaader.asutnr
      where pa_tasu.palgaleht is not null and(pa_tasu.t_aasta=a_t_aasta and pa_tasu.t_akuu=a_t_akuu) and(pa_yksus.skoop=a_skoop or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and pa_taslist.vmkood is not null and pa_tasu.vtasunr is null
      group by pa_kaader_mres,pa_leping.asutnr,pa_taslist.vmkood;
-- maksuvaba ringijagamine
  delete from gl_stakk;
  -- summeeri maksuvaba ja tulumaks residendist isikute kaupa
  insert into gl_stakk(id,kogus,summa)
    select gl_tsd.asutnr,sum(mvaba),sum(mtm)
      from gl_tsd
      where gl_tsd.mres=0
      group by gl_tsd.asutnr,gl_tsd.mres;
  update gl_tsd set mvaba=0 where gl_tsd.mres=0;
  set ldc_tulu_protsent=get_maar(2,ld_perlop);
  -- tsykkel üle isiku kokku maksuvaba ja tulumaksu
  open c_stakk;
  stakk_loop: loop
    fetch next c_stakk into ll_asutnr,ldc_kok_mvaba,ldc_kok_mtm;
    if sqlstate = err_notfound then -- kui eof
      leave stakk_loop
    end if;
    set ls_mvaba_vmkood='xxx';
    set ls_mtm_vmkood='xxx';
    -- tsykkel yle isiku tsd ridade (tulumaksuga maksustatav summa)
    open c_tsd;
    tsd_loop: loop
      fetch next c_tsd into ls_vmkood,ldc_mtmak;
      if sqlstate = err_notfound then -- kui eof
        leave tsd_loop
      end if;
      -- teatud koodidega maksuvaba ja tulumaks null
      if ls_vmkood not in ('11','14','18','19','22','26','30','34','36') then
        if ldc_kok_mvaba>ldc_mtmak then
          set ldc_mvaba=ldc_mtmak;
          set ldc_kok_mvaba=ldc_kok_mvaba-ldc_mvaba
        else
          set ldc_mvaba=ldc_kok_mvaba;
          set ldc_kok_mvaba=0
        end if;
        set ldc_mtm=Round((ldc_mtmak-ldc_mvaba)*ldc_tulu_protsent/100,2);
        set ldc_kok_mtm=ldc_kok_mtm-ldc_mtm;
        update first gl_tsd set mvaba=ldc_mvaba, mtm=ldc_mtm where gl_tsd.osa='a' and gl_tsd.asutnr=ll_asutnr and gl_tsd.vmkood=ls_vmkood;
        if ldc_mvaba>0 then
          set ls_mvaba_vmkood=ls_vmkood
        end if;
        if ldc_mtm>0 then
          set ls_mtm_vmkood=ls_vmkood
        end if;
      else
        update first gl_tsd set mvaba=0, mtm=0 where gl_tsd.osa='a' and gl_tsd.asutnr=ll_asutnr and gl_tsd.vmkood=ls_vmkood
      end if;
    end loop tsd_loop;
    if ldc_kok_mvaba<>0 then
      if ls_mvaba_vmkood = 'xxx' then
        update first gl_tsd set mvaba=mvaba+ldc_kok_mvaba where gl_tsd.osa='a' and gl_tsd.asutnr=ll_asutnr and gl_tsd.vmkood=ls_vmkood
      else
        update first gl_tsd set mvaba=mvaba+ldc_kok_mvaba where gl_tsd.osa='a' and gl_tsd.asutnr=ll_asutnr and gl_tsd.vmkood=ls_mvaba_vmkood
      end if
    end if;
    if ldc_kok_mtm<>0 then
      if ls_mtm_vmkood = 'xxx' then
        update first gl_tsd set mtm=mtm+ldc_kok_mtm where gl_tsd.osa='a' and gl_tsd.asutnr=ll_asutnr and gl_tsd.vmkood=ls_vmkood
      else
        update first gl_tsd set mtm=mtm+ldc_kok_mtm where gl_tsd.osa='a' and gl_tsd.asutnr=ll_asutnr and gl_tsd.vmkood=ls_mtm_vmkood
      end if
    end if;
    close c_tsd;
  end loop stakk_loop;
  close c_stakk;
-- sotsiaalmaksu ringijagamine
  delete from gl_stakk;
  insert into gl_stakk(id, kogus, summa)
    select gl_tsd.asutnr,0,sum(msm)
      from gl_tsd
      where gl_tsd.mres=0
      group by gl_tsd.asutnr,gl_tsd.mres;
  update gl_tsd set msm=0 where gl_tsd.mres=0;
  set ldc_sots_maar=get_maar(6,ymd(a_t_aasta,a_t_akuu,1));
  set ldc_sots_protsent=get_maar(7,ymd(a_t_aasta,a_t_akuu,1));
  open c_stakk;
  stakk_sots_loop: loop
    fetch next c_stakk into ll_asutnr,ldc_kok_mvaba,ldc_kok_msm;
    if sqlstate = err_notfound then -- kui eof
      leave stakk_sots_loop
    end if;
    set ls_mvaba_vmkood='xxx';
    open c_tsd_sots;
    tsd_sots_loop: loop
      fetch next c_tsd_sots into ls_vmkood,ldc_msmak;
      if sqlstate = err_notfound then -- kui eof
        leave tsd_sots_loop
      end if;
      if ls_mvaba_vmkood='xxx' and ls_vmkood in ('10','11','13','25','26','28','33','34') then
        set ls_mvaba_vmkood=ls_vmkood
      end if;
      set ldc_msm=Round((ldc_msmak)*ldc_sots_protsent/100,2);
      set ldc_kok_msm=ldc_kok_msm-ldc_msm;
      update first gl_tsd set msm=ldc_msm where gl_tsd.osa='a' and gl_tsd.asutnr=ll_asutnr and gl_tsd.vmkood=ls_vmkood;
    end loop tsd_sots_loop;
    if ldc_kok_msm<>0 then
      if ls_mvaba_vmkood = 'xxx' then
        update first gl_tsd set msm=msm+ldc_kok_msm where gl_tsd.osa='a' and gl_tsd.asutnr=ll_asutnr and gl_tsd.vmkood=ls_vmkood
      else
        update first gl_tsd set msm=msm+ldc_kok_msm where gl_tsd.osa='a' and gl_tsd.asutnr=ll_asutnr and gl_tsd.vmkood=ls_mvaba_vmkood
      end if
    end if;
    close c_tsd_sots;
  end loop stakk_sots_loop;
  close c_stakk;
-- b osa
  insert into gl_tsd(osa,asutnr,vmkood,v_t_aasta,v_t_akuu,summa,msmak,msm,mpens,mtkmak,mtvtk,mtatk,mtmak,mvaba,mvaba_630,mtm,koht,tmkood)
    select 'b',pa_leping.asutnr,pa_taslist.vmkood,pa_tasu_b.t_aasta,pa_tasu_b.t_akuu,
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.summa),
           sum(if pa_taslist.smrk=1 then ((-2)*pa_taslist.sisse+1)*pa_tasu.summa else 0 endif),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.sotsmaks),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.pensmaks),
           sum(if pa_taslist.tk=1 then ((-2)*pa_taslist.sisse+1)*pa_tasu.summa else 0 endif),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.tvtkmaks),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.tatkmaks),
           sum(if pa_taslist.tulu=1 then ((-2)*pa_taslist.sisse+1)*pa_tasu.summa else 0 endif),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.maksuvaba),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.maksuvaba_630),
           sum(((-2)*pa_taslist.sisse+1)*pa_tasu.tulumaks),
           0,
           if get_penskar(pa_leping.asutnr,a_t_aasta)=1 then '650' else '610' endif
      from pa_tasu left outer join pa_yksus on pa_tasu.yksusnr=pa_yksus.yksusnr left outer join skoobid on pa_yksus.skoop=skoobid.skoop left outer join pa_taslist on pa_tasu.tas_s=pa_taslist.tas_s left outer join pa_leping on pa_tasu.lepnr=pa_leping.lepnr left outer join pa_tasu as pa_tasu_b on pa_tasu.vtasunr=pa_tasu_b.tasunr and pa_tasu.va_aasta=pa_tasu_b.a_aasta
      where pa_tasu.palgaleht is not null and(pa_tasu.t_aasta=a_t_aasta and pa_tasu.t_akuu=a_t_akuu) and(pa_yksus.skoop=a_skoop or a_skoop=0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood and pa_taslist.vmkood is not null and pa_tasu.vtasunr is not null
      group by pa_leping.asutnr,pa_taslist.vmkood,pa_tasu_b.t_aasta,pa_tasu_b.t_akuu;
  update gl_tsd set smkor1=if get_pa_kaader_tun_sots(gl_tsd.asutnr,ld_peralg,a_skoopkood)=2 then Round(msmak-msm/ldc_sots_protsent*100,2) else 0 endif,smkor2=if get_pa_kaader_tun_sots(gl_tsd.asutnr,ld_peralg,a_skoopkood)=1 and Round(msmak-msm/ldc_sots_protsent*100,2)>0 then Abs(Round(msmak-msm/ldc_sots_protsent*100,2)) else 0 endif,smkor3=if get_pa_kaader_tun_sots(gl_tsd.asutnr,ld_peralg,a_skoopkood)=1 and Round(msmak-msm/ldc_sots_protsent*100,2)<0 then Abs(Round(msmak-msm/ldc_sots_protsent*100,2)) else 0 endif;
  update gl_tsd set smkor1=ldc_sots_maar where Abs(smkor1-ldc_sots_maar)<0.05;
  update gl_tsd set smkor2=ldc_sots_maar where Abs(smkor2-ldc_sots_maar)<0.05;
  update gl_tsd set smkor3=ldc_sots_maar where Abs(smkor3-ldc_sots_maar)<0.05;
  update gl_tsd set smkor1=0 where Abs(smkor1)<0.05;
  update gl_tsd set smkor2=0 where Abs(smkor2)<0.05;
  update gl_tsd set smkor3=0 where Abs(smkor3)<0.05;
  delete from gl_tsd where summa=0 and msm=0 and msmak=0 and mtm=0 and mtmak=0 and mvaba=0 and mtvtk=0 and mtkmak=0 and mtatk=0 and mpens=0;
  if a_t_aasta > 2015 then
    open c_pa_leping;
    pa_leping_loop: loop
      fetch next c_pa_leping into ll_lepnr;
      if sqlstate = err_notfound then -- kui eof
        leave pa_leping_loop
      end if;
      select pa_leping.asutnr,
             if pa_leping.kuup1>ld_peralg then pa_leping.kuup1 else ld_peralg endif,
             if pa_leping.kuup2 is null or pa_leping.kuup2>ld_perlop then ld_perlop else pa_leping.kuup2 endif
        into ll_asutnr,ld_kuup1,ld_kuup2
        from pa_leping
        where pa_leping.lepnr=ll_lepnr;
      set ldc_koht=get_pa_leping_tun_koht(ll_lepnr,ld_kuup2);
      if exists(select 1 from pa_tgraaf left outer join peatlist on pa_tgraaf.peatnr=peatlist.peatnr where pa_tgraaf.lepnr=ll_lepnr and peatlist.emta=1 and pa_tgraaf.akuup<=ld_peralg and pa_tgraaf.lkuup>=ld_perlop) then
        set ldc_koht=0
      end if;
      if ldc_koht>0 then
        if exists(select 1 from gl_tsd where asutnr=ll_asutnr and osa='a' and vmkood in ('10','11','12','13')) then
          update gl_tsd set koht=koht+ldc_koht where gl_tsd.asutnr=ll_asutnr and osa='a' and vmkood in ('10','11','12','13')
        else
          insert into gl_tsd(osa,asutnr,vmkood,summa,msmak,msm,mpens,mtkmak,mtvtk,mtatk,mtmak,mvaba,mvaba_630,mtm,koht) values ('a',ll_asutnr,'10',0,0,0,0,0,0,0,0,0,0,0,ldc_koht)
        end if
      end if;
    end loop pa_leping_loop;
    close c_pa_leping;
    update gl_tsd set koht=1 where koht>1
  else
    update gl_tsd set koht=get_pa_leping_koht(gl_tsd.asutnr,ymd(a_t_aasta,a_t_akuu,28),a_skoop);
  end if;
end