ALTER PROCEDURE "DBA"."pv_inventuur"( in a_toot_nr char(200) default '', in a_skoop char(100) default '', in a_org char(100) default '', in a_yksus char(100) default '', in a_asut char(101) default '',in a_new_toot_nr char(200) default '',in a_info3 char(100) default '' )
RESULT( vastus char(100))
BEGIN
  declare ld_perlop date;
  declare ll_id integer;
  declare ll_id_pv_kanne integer;
  declare ll_count integer;
  declare ll_skoop integer;
  declare ls_skoopkood char(16);
  declare ll_orgnr integer;
  declare ls_orgkood char(16);
  declare ll_yksusnr integer;
  declare ls_yksuskood char(16);
  declare ll_asutnr integer;
  set ld_perlop=ToDay();
  -- Valdkonna tuvastamine
  if a_skoop is null then
    set a_skoop=''
  end if;
  set ll_count=0;
  set ll_skoop=null;
  set ls_skoopkood='';
  if Length(a_skoop)>0 then
    select first skoop into ll_skoop from skoobid where nimi=a_skoop;
    if ll_skoop is null then
      select count() into ll_count from skoobid where Left(kood,Length(a_skoop))=a_skoop;
      if ll_count=0 then
        set ll_skoop=0
      else
        if ll_count=1 then
          select first skoop into ll_skoop from skoobid where Left(kood,Length(a_skoop))=a_skoop
        else
          set ls_skoopkood=a_skoop;
          set ll_skoop=0
        end if
      end if
    end if
  else
    set ll_skoop=0
  end if;
  -- Organisatsiooni tuvastamine
  if a_org is null then
    set a_org=''
  end if;
  set ll_count=0;
  set ll_orgnr=null;
  set ls_orgkood='';
  if Length(a_org)>0 then
    select first orgnr into ll_orgnr from orglist where nimi=a_org;
    if ll_orgnr is null then
      select count() into ll_count from orglist where Left(kood,Length(a_org))=a_org;
      if ll_count=0 then
        set ll_orgnr=0
      else
        if ll_count=1 then
          select first orgnr into ll_orgnr from orglist where Left(kood,Length(a_org))=a_org
        else
          set ls_orgkood=a_org;
          set ll_orgnr=0
        end if
      end if
    end if
  else
    set ll_orgnr=0
  end if;
  -- Allyksuse tuvastamine
  if a_yksus is null then
    set a_yksus=''
  end if;
  set ll_count=0;
  set ll_yksusnr=null;
  set ls_yksuskood='';
  if Length(a_yksus)>0 then
    select first pv_yksus.yksusnr into ll_yksusnr
      from pv_yksus left outer join skoobid on pv_yksus.skoop=skoobid.skoop
                    left outer join orglist on pv_yksus.orgnr=orglist.orgnr
      where pv_yksus.nimi=a_yksus and
           (pv_yksus.skoop=ll_skoop or ll_skoop=0) and Left(skoobid.kood,Length(ls_skoopkood))=ls_skoopkood and
           (ll_orgnr=0 and pv_yksus.orgnr is null or ((pv_yksus.orgnr=ll_orgnr or ll_orgnr=0) and Left(orglist.kood,Length(ls_orgkood))=ls_orgkood));
    if ll_yksusnr is null then
      select count() into ll_count
        from pv_yksus left outer join skoobid on pv_yksus.skoop=skoobid.skoop
                      left outer join orglist on pv_yksus.orgnr=orglist.orgnr
        where Left(pv_yksus.kood,Length(a_yksus))=a_yksus and
             (pv_yksus.skoop=ll_skoop or ll_skoop=0) and Left(skoobid.kood,Length(ls_skoopkood))=ls_skoopkood and
             (ll_orgnr=0 and pv_yksus.orgnr is null or ((pv_yksus.orgnr=ll_orgnr or ll_orgnr=0) and Left(orglist.kood,Length(ls_orgkood))=ls_orgkood));
      if ll_count=0 then
        set ll_yksusnr=0
      else
        if ll_count=1 then
          select first pv_yksus.yksusnr into ll_yksusnr
            from pv_yksus left outer join skoobid on pv_yksus.skoop=skoobid.skoop
                          left outer join orglist on pv_yksus.orgnr=orglist.orgnr
            where Left(pv_yksus.kood,Length(a_yksus))=a_yksus and
                 (pv_yksus.skoop=ll_skoop or ll_skoop=0) and Left(skoobid.kood,Length(ls_skoopkood))=ls_skoopkood and
                 (ll_orgnr=0 and pv_yksus.orgnr is null or ((pv_yksus.orgnr=ll_orgnr or ll_orgnr=0) and Left(orglist.kood,Length(ls_orgkood))=ls_orgkood));
        else
          set ls_yksuskood=a_yksus;
          set ll_yksusnr=0
        end if
      end if
    end if
  else
    set ll_yksusnr=0
  end if;
  -- Mat.vastutaja tuvastamine
  if a_asut is null then
    set a_asut=''
  end if;
  set ll_asutnr=null;
  if Length(a_asut)>0 then
    select first asutnr into ll_asutnr from asulist where get_asulist_nimi_quick(asutnr)=a_asut;
    if ll_asutnr is null then
      select first asutnr into ll_asutnr from asulist where reg_nr=a_asut;
    end if
  end if;
  if ll_asutnr is null then
    set ll_asutnr=0
  end if;
  delete from gl_pv_kanne;
  if a_toot_nr is null then
    set a_toot_nr=''
  end if;
  if a_new_toot_nr is null then
    set a_new_toot_nr=''
  end if;
  set a_new_toot_nr=Left(a_new_toot_nr,20);
  if ll_skoop>0 or ll_skoop=0 and a_skoop='Kõik valdkonnad' then
    if Length(a_toot_nr)>0 then
      if Length(a_toot_nr)<21 then
        select count() into ll_count from pv_kanne key left outer join pv_yksus left outer join orglist on pv_yksus.orgnr=orglist.orgnr
              where pv_kanne.toot_nr=a_toot_nr and pv_yksus.skoop=ll_skoop and
                   ((pv_yksus.orgnr=ll_orgnr or a_org='') and Left(orglist.kood,Length(ls_orgkood))=ls_orgkood) and
                   (pv_yksus.asutnr=ll_asutnr or a_asut='') and
                   (pv_kanne.yksusnr=ll_yksusnr or a_yksus='') and Left(pv_yksus.kood,Length(ls_yksuskood))=ls_yksuskood and
                   (pv_kanne.objnr2 is null or(pv_kanne.objnr2 is not null and pv_kanne.kuup2>ld_perlop));
        if ll_count>0 then
          if Length(a_new_toot_nr)>0 and a_new_toot_nr<>a_toot_nr then
            update pv_kanne key left outer join pv_yksus left outer join orglist on pv_yksus.orgnr=orglist.orgnr
              set pv_kanne.invent=Now(),pv_kanne.vana_toot_nr=a_toot_nr,pv_kanne.toot_nr=a_new_toot_nr
              where pv_kanne.toot_nr=a_toot_nr and pv_yksus.skoop=ll_skoop and
                   ((pv_yksus.orgnr=ll_orgnr or a_org='') and Left(orglist.kood,Length(ls_orgkood))=ls_orgkood) and
                   (pv_yksus.asutnr=ll_asutnr or a_asut='') and
                   (pv_kanne.yksusnr=ll_yksusnr or a_yksus='') and Left(pv_yksus.kood,Length(ls_yksuskood))=ls_yksuskood and
                   (pv_kanne.objnr2 is null or(pv_kanne.objnr2 is not null and pv_kanne.kuup2>ld_perlop));
            insert into gl_pv_kanne ("id",toot_nr,"nimi") values (1,'','Inventeeritud kandeid: '+convert(char(10),ll_count)+' inventari numbriga: '+a_toot_nr+' uus inventari number: '+a_new_toot_nr);
          else
            update pv_kanne key left outer join pv_yksus left outer join orglist on pv_yksus.orgnr=orglist.orgnr
              set pv_kanne.invent=Now()
              where pv_kanne.toot_nr=a_toot_nr and pv_yksus.skoop=ll_skoop and
                   ((pv_yksus.orgnr=ll_orgnr or a_org='') and Left(orglist.kood,Length(ls_orgkood))=ls_orgkood) and
                   (pv_yksus.asutnr=ll_asutnr or a_asut='') and
                   (pv_kanne.yksusnr=ll_yksusnr or a_yksus='') and Left(pv_yksus.kood,Length(ls_yksuskood))=ls_yksuskood and
                   (pv_kanne.objnr2 is null or(pv_kanne.objnr2 is not null and pv_kanne.kuup2>ld_perlop));
            insert into gl_pv_kanne ("id",toot_nr,"nimi") values (1,'','Inventeeritud kandeid: '+convert(char(10),ll_count)+' inventari numbriga: '+a_toot_nr);
          end if;
          -- inventuuri info
          if Length(a_info3)>0 then
            set ll_id_pv_kanne=0;
            SELECT FIRST "pv_kanne"."id" INTO ll_id_pv_kanne FROM "pv_kanne" LEFT OUTER JOIN "pv_yksus" ON "pv_kanne"."yksusnr"="pv_yksus"."yksusnr" WHERE "pv_kanne"."toot_nr"=a_toot_nr AND "pv_yksus"."skoop"=ll_skoop ORDER BY "pv_kanne"."id" ASC;
            if ll_id_pv_kanne>0 then
              INSERT INTO "pv_kanne_invinfo" ("id_pv_kanne", "kuup", "info3", "user_code") VALUES (ll_id_pv_kanne, date(Now()), a_info3, 32767);
            end if;
          end if
        else
          insert into gl_pv_kanne ("id",toot_nr,"nimi") values (1,'','Ei leia inventari numbrit: '+a_toot_nr);
        end if;
      else
        insert into gl_pv_kanne ("id",toot_nr,"nimi") values (1,'','Inventari number on liiga pikk (max.20): '+a_toot_nr);
      end if
    else
      insert into gl_pv_kanne ("id",toot_nr,"nimi") values (1,'','Inventari number puudub');
    end if;
  else
    insert into gl_pv_kanne ("id",toot_nr,"nimi") values (1,'','Valdkond üheselt määramata');
  end if;
  
  select nimi from gl_pv_kanne;

END