CREATE PROCEDURE "dba"."pr_koond_sisu2"( in a_skoopkood char(16),in a_skoop integer,in id_paring integer,in peralg date,in perlop date,in a_konto char(7))
BEGIN

  declare li_saldod smallint;
  declare ldc_daa numeric(15,2);
  declare ldc_da numeric(15,2);
  declare ldc_kaa numeric(15,2);
  declare ldc_ka numeric(15,2);
  declare ll_id integer;
  declare li_aasta smallint;
  declare ls_deeb char(7);
  declare ls_kree char(7);
  declare err_notfound exception for sqlstate value '02000';
  declare c_kanne dynamic scroll cursor for
    select "id",aasta,summa-summa2
      from gl_pr_kanne
      order by kuup,kell;


  delete from gl_pr_kanne;

  SELECT "pr_konfm"."yld_saldod" INTO li_saldod FROM "pr_konfm" WHERE "pr_konfm"."id"=1;

  set id_paring=set_gl_1(id_paring);

  
    insert into gl_pr_kanne("id",aasta,kuup,doknr,summa,summa2,"order",deeb,kree,"info",asutnr,kell,tulnud,skoop)
     select "pr_kanne"."id",
            "pr_kanne"."aasta",   
            "pr_kanne"."kuup",   
            "pr_kanne"."doknr",   
            if "pr_kanne"."deeb" is not null and left("pr_kanne"."deeb",length(a_konto))=a_konto then "pr_kanne"."summa" else 0 endif,
            if "pr_kanne"."kree" is not null and left("pr_kanne"."kree",length(a_konto))=a_konto then "pr_kanne"."summa" else 0 endif,
            "pr_kanne"."order",   
            "pr_kanne"."deeb",   
            "pr_kanne"."kree",
            Left(' ' + Trim("pr_alus"."nimi" + ' ' + "pr_kanne"."info"),300),
            "pr_kanne"."asutnr",
            if "pr_kanne"."kell2" is null then "pr_kanne"."kell" else "pr_kanne"."kell2" endif,
            "pr_kanne"."tulnud",
            "pr_kanne"."skoop"
      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 "pr_alus" on "pr_kanne"."alusnr"="pr_alus"."alusnr" 
                      left outer join "skoobid" on "pr_kanne"."skoop" = "skoobid"."skoop"
      where (("pr_kanne"."id" = "gl_1"."id") and ("pr_kanne"."kuup" = "gl_1"."kuup") or id_paring = 0) AND
           (Left("pr_kanne"."deeb",length(a_konto)) = a_konto OR Left("pr_kanne"."kree",length(a_konto)) = a_konto) AND  
           ("pr_kanne"."skoop" = a_skoop OR a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood AND  
           "pr_kanne"."kuup">=peralg AND "pr_kanne"."kuup"<=perlop;

  if li_saldod=1 then
      -- deebet
      select Sum("pr_kanne"."summa") INTO ldc_daa
        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"
   		WHERE (("pr_kanne"."id" = "gl_1"."id") and ("pr_kanne"."kuup" = "gl_1"."kuup") or id_paring = 0) AND
      		  Left("pr_kanne"."deeb",length(a_konto)) = a_konto AND  
              ("pr_kanne"."skoop" = a_skoop OR a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood AND  
      	   	Years("pr_kanne"."kuup") < Years(peralg) AND
            locate('345678',left("pr_kanne"."deeb",1)) = 0;
      select Sum("pr_kanne"."summa") INTO ldc_da
        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"
   		WHERE (("pr_kanne"."id" = "gl_1"."id") and ("pr_kanne"."kuup" = "gl_1"."kuup") or id_paring = 0) AND
      		  Left("pr_kanne"."deeb",length(a_konto)) = a_konto AND  
              ("pr_kanne"."skoop" = a_skoop OR a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood AND  
	      	   "pr_kanne"."kuup"<peralg AND Years("pr_kanne"."kuup") >= Years(peralg);
      -- kreedit
      select Sum("pr_kanne"."summa") INTO ldc_kaa
        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"
   		WHERE (("pr_kanne"."id" = "gl_1"."id") and ("pr_kanne"."kuup" = "gl_1"."kuup") or id_paring = 0) AND
      		  Left("pr_kanne"."kree",length(a_konto)) = a_konto AND  
              ("pr_kanne"."skoop" = a_skoop OR a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood AND  
      	   	Years("pr_kanne"."kuup") < Years(peralg) AND
            locate('345678',left("pr_kanne"."kree",1)) = 0;
      select Sum("pr_kanne"."summa") INTO ldc_ka
        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"
   		WHERE (("pr_kanne"."id" = "gl_1"."id") and ("pr_kanne"."kuup" = "gl_1"."kuup") or id_paring = 0) AND
      		  Left("pr_kanne"."kree",length(a_konto)) = a_konto AND  
              ("pr_kanne"."skoop" = a_skoop OR a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood AND  
	      	   "pr_kanne"."kuup"<peralg AND Years("pr_kanne"."kuup") >= Years(peralg)
  else
      -- deebet
      select Sum("pr_kanne"."summa") INTO ldc_da
        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"
   		WHERE (("pr_kanne"."id" = "gl_1"."id") and ("pr_kanne"."kuup" = "gl_1"."kuup") or id_paring = 0) AND
      		  Left("pr_kanne"."deeb",length(a_konto)) = a_konto AND  
              ("pr_kanne"."skoop" = a_skoop OR a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood AND  
	      	   "pr_kanne"."kuup"<peralg;
      -- kreedit
      select Sum("pr_kanne"."summa") INTO ldc_ka
        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"
   		WHERE (("pr_kanne"."id" = "gl_1"."id") and ("pr_kanne"."kuup" = "gl_1"."kuup") or id_paring = 0) AND
      		  Left("pr_kanne"."kree",length(a_konto)) = a_konto AND  
              ("pr_kanne"."skoop" = a_skoop OR a_skoop = 0) and Left(skoobid.kood,Length(a_skoopkood))=a_skoopkood AND  
	      	   "pr_kanne"."kuup"<peralg;
  end if;
  if ldc_daa is null then
    set ldc_daa=0
  end if;
  if ldc_da is null then
    set ldc_da=0
  end if;
  if ldc_kaa is null then
    set ldc_kaa=0
  end if;
  if ldc_ka is null then
    set ldc_ka=0
  end if;
  set ldc_daa=ldc_daa+ldc_da-ldc_kaa-ldc_ka;
  -- algjaagi kanne
  if ldc_daa>0 then
    insert into gl_pr_kanne("id",aasta,kuup,doknr,summa,summa2,"order",deeb,kree,"info",skoop) values (0,2000,peralg,'',ldc_daa,0,'',a_konto,'','Algjääk',a_skoop);
  else
    insert into gl_pr_kanne("id",aasta,kuup,doknr,summa,summa2,"order",deeb,kree,"info",skoop) values (0,2000,peralg,'',0,abs(ldc_daa),'','',a_konto,'Algjääk',a_skoop);
  end if;
  -- saldo arvutamine
  set ldc_kaa=0;
  open c_kanne;
  kanne_loop: loop
    fetch next c_kanne into ll_id,li_aasta,ldc_daa;
    if sqlstate = err_notfound then -- kui eof
      leave kanne_loop
    end if;
    set ldc_kaa=ldc_kaa+ldc_daa;
    update gl_pr_kanne set saldo=ldc_kaa where "id"=ll_id and aasta=li_aasta;
  end loop kanne_loop;
  close c_kanne;

END