ALTER PROCEDURE "dba"."pv_web_update"( )
RESULT( vastus char(100))
BEGIN
  declare ls_body long varchar;
  declare ls_row long varchar;
  declare ll_pos1 integer;
  declare ll_pos2 integer;
  declare ll_count integer;
  declare ll_count_kan integer;
  declare ll_count_toot integer;
  declare ll_count_m_kan integer;
  declare ll_count_m_toot integer;
  declare ll_id_pv_kanne integer;
  declare ll_skoop integer;
  declare ll_yksusnr integer;
  declare ls_toot_nr char(20);
  declare ls_info char(100);
  declare ls_rfid char(100);
  declare ls_charset char(20);
  declare ls_text char(50);
  declare ld_kuup date;
  declare ld_kuup2 date;
  declare ll_objnr2 integer;
  declare ls_doknr2 char(20);
  declare li_mahakuukulum smallint;
  declare ll_saajanr integer;
  declare ls_regnr char(11);
  declare ls_deeb char(7);
  declare ll_ravonr2 integer;
  
  delete from gl_pv_kanne;
  -- loe paringu sisu
  set ls_body=HTTP_BODY();
  set ll_count=0;
  set ll_count_kan=0;
  set ll_count_toot=0;
  set ll_count_m_kan=0;
  set ll_count_m_toot=0;
  if Length(ls_body)>0 then
    set ls_body=TO_CHAR(ls_body,'UTF-8');
    set ll_pos1=locate(ls_body,'<InventoryResults',1);
    if ll_pos1>0 then
      body_loop:
      loop
        set ll_pos1=locate(ls_body,'<asset>',1);
        if ll_pos1>0 then
          set ll_pos2=locate(ls_body,'</asset>',ll_pos1);
          if ll_pos2>ll_pos1 then
            -- loe xml rida
            set ls_row=substring(ls_body,ll_pos1+8, ll_pos2-ll_pos1-8);
            -- loika ylejaanud sisu
            set ls_body=substring(ls_body,ll_pos2+9);
            -- nulli
            set ll_id_pv_kanne=0;
            set ll_skoop=0;
            set ll_yksusnr=0;
            set ls_toot_nr = '';
            -- loe rea elemendid
            set ls_info=Left(Trim(get_xml_value('valdkond',ls_row)),50);
            set ll_skoop=get_skoop_kood(ls_info);
            set ls_info=Left(Trim(get_xml_value('allyksus',ls_row)),50);
            set ll_yksusnr=get_pv_yksusnr(ll_skoop,'',ls_info,null,null);
            set ll_id_pv_kanne=convert(integer,get_xml_value('inv_id',ls_row));
            if ll_id_pv_kanne is null then
              set ll_id_pv_kanne=0
            end if;
            set ls_toot_nr=Left(Trim(get_xml_value('inv_number',ls_row)),20);
            set ls_info=Left(Trim(get_xml_value('invent',ls_row)),10);
            if isdate(ls_info)=1 then
              set ld_kuup=convert(date,ls_info)
            else
              set ld_kuup=ymd(2000,1,1)
            end if;
            set ls_rfid=Left(Trim(get_xml_value('rfid',ls_row)),100);
            set ls_info=Left(Trim(get_xml_value('info3',ls_row)),100);
            set ls_text=Left(Trim(get_xml_value('kuup2',ls_row)),10);
            if isdate(ls_text)=1 then
              set ld_kuup2=convert(date,ls_text)
            else
              set ld_kuup2=ymd(2000,1,1)
            end if;
            set ls_text=Left(Trim(get_xml_value('objnr2',ls_row)),50);
            if Length(ls_text)>0 then
              set ll_objnr2=existid_pv_obj(ls_text,0)
            else
              set ll_objnr2=0
            end if;
            set ls_doknr2=Left(Trim(get_xml_value('doknr2',ls_row)),20);
            set ls_text=Left(Trim(get_xml_value('mahakuukulum',ls_row)),1);
            if ls_text='1' then
              set li_mahakuukulum=1
            else
              set li_mahakuukulum=0
            end if;
            set ls_regnr=Left(Trim(get_xml_value('saaja_regnr',ls_row)),11);
            set ls_text=Left(Trim(get_xml_value('saaja_nimi',ls_row)),100);
            if Length(ls_regnr)>0 and Length(ls_text)>0 then
              set ll_saajanr=existid_asulist(ls_regnr,ls_text,'','')
            else
              set ll_saajanr=null
            end if;
            set ls_text=Left(Trim(get_xml_value('deeb',ls_row)),7);
            if Length(ls_text)>0 then
              set ls_deeb=existid_kontlist(ls_text)
            else
              set ls_deeb=null
            end if;
            set ls_text=Left(Trim(get_xml_value('ravonr2',ls_row)),16);
            if Length(ls_text)>0 then
              set ll_ravonr2=existid_ravolist(ls_text)
            else
              set ll_ravonr2=null
            end if;

            if ll_id_pv_kanne>0 or (ll_yksusnr>0 and Length(ls_toot_nr)>0) then
              select count() into ll_count from pv_kanne where pv_kanne."id"=ll_id_pv_kanne or (pv_kanne."id"=ll_id_pv_kanne or ll_id_pv_kanne=0) and pv_kanne.toot_nr=ls_toot_nr and pv_kanne.yksusnr=ll_yksusnr;
              if ll_count>0 then
                -- mahakandmine
                if ld_kuup2>ymd(2021,1,1) and ll_objnr2>0 then
                  update pv_kanne set pv_kanne.kuup2=ld_kuup2, pv_kanne.objnr2=ll_objnr2, pv_kanne.doknr2=ls_doknr2, pv_kanne.mahakuukulum=li_mahakuukulum, pv_kanne.saajanr=ll_saajanr, pv_kanne.deeb=ls_deeb, pv_kanne.ravonr2=ll_ravonr2 where pv_kanne."id"=ll_id_pv_kanne or (pv_kanne."id"=ll_id_pv_kanne or ll_id_pv_kanne=0) and pv_kanne.toot_nr=ls_toot_nr and pv_kanne.yksusnr=ll_yksusnr;
                  set ll_count_m_kan=ll_count_m_kan+ll_count;
                  set ll_count_m_toot=ll_count_m_toot+1;
                end if;
                -- inventeerimine
                if ld_kuup>ymd(2021,1,1) then
                  update pv_kanne set pv_kanne.invent=ld_kuup, pv_kanne.rfid=ls_rfid where pv_kanne."id"=ll_id_pv_kanne or (pv_kanne."id"=ll_id_pv_kanne or ll_id_pv_kanne=0) and pv_kanne.toot_nr=ls_toot_nr and pv_kanne.yksusnr=ll_yksusnr;
                  set ll_count_kan=ll_count_kan+ll_count;
                  set ll_count_toot=ll_count_toot+1;
                  -- inventuuri info
                  if Length(ls_info)>0 then
                    if ll_id_pv_kanne>0 and (ll_yksusnr=0 or Length(ls_toot_nr)=0) then
                      select "pv_kanne"."toot_nr","pv_kanne"."yksusnr" INTO ls_toot_nr,ll_yksusnr FROM "pv_kanne" WHERE "pv_kanne"."id"=ll_id_pv_kanne;
                    end if;
                    if ll_yksusnr>0 and Length(ls_toot_nr)>0 then
                      set ll_id_pv_kanne=0;
                      SELECT FIRST "pv_kanne"."id" INTO ll_id_pv_kanne FROM "pv_kanne" WHERE "pv_kanne"."toot_nr"=ls_toot_nr AND "pv_kanne"."yksusnr"=ll_yksusnr 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, ld_kuup, ls_info, 32767);
                      end if;
                    end if;
                  end if;
                end if;
              end if;
            end if;

          else
            leave body_loop;
          end if
        else
          leave body_loop;
        end if;
      end loop body_loop;
      insert into gl_pv_kanne ("id",toot_nr,"nimi") values (1,'','Inventeeritud '+convert(char(10),ll_count_toot)+' vara '+convert(char(10),ll_count_kan)+' kannet'+'   Välja kantud '+convert(char(10),ll_count_m_toot)+' vara '+convert(char(10),ll_count_m_kan)+' kannet');
    else
      insert into gl_pv_kanne ("id",toot_nr,"nimi") values (1,'','Ei leia elementi: InventoryResults');
    end if;
  else
    insert into gl_pv_kanne ("id",toot_nr,"nimi") values (1,'','Sisu puudub');
  end if;
  select nimi from gl_pv_kanne;
END