ALTER PROCEDURE "dba"."pv_koond_id_balti_back"(  )
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_id_pv_kanne integer;
  declare ll_yksusnr integer;
  declare ls_toot_nr char(20);
  declare ls_info char(100);
  declare ld_kuup date;
  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;
  if Length(ls_body)>0 then
    set ls_body=CSCONVERT(ls_body,'char_charset','UTF-8');
    set ll_pos1=locate(ls_body,'<InventoryResults',1);
    if ll_pos1>0 then
      body_loop:
      loop
        set ll_pos1=locate(ls_body,'<result>',1);
        if ll_pos1>0 then
          set ll_pos2=locate(ls_body,'</result>',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);
            -- loe rea elemendid
            set ll_yksusnr=convert(integer,get_xml_value('responsibleOrgUnitId',ls_row));
            set ls_toot_nr=Left(Trim(get_xml_value('inventoryNumber',ls_row)),20);
            set ls_info=Left(Trim(get_xml_value('inventoryDate',ls_row)),100);
            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_info=Left(Trim(get_xml_value('otherComments',ls_row)),100);
            if ll_yksusnr>0 and ld_kuup>ymd(2021,1,1) and Length(ls_toot_nr)>0 then
              select count() into ll_count from pv_kanne where pv_kanne.toot_nr=ls_toot_nr and pv_kanne.yksusnr=ll_yksusnr;
              if ll_count>0 then
                update pv_kanne set pv_kanne.invent=ld_kuup where 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
                  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;
          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');
    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