CREATE PROCEDURE "dba"."pa_insert_data"()
RESULT( vastus char(100))
BEGIN
  declare ls_body long varchar;
  declare ls_row long varchar;
  declare ls_kaader long varchar;
  declare ll_pos1 integer;
  declare ll_pos2 integer;
  declare ll_count integer;
  declare ll_count_add integer;
  declare ll_count_rem integer;
  declare ll_count_mis integer;
  declare ll_count_unc integer;
  declare ll_count_err integer;
  declare ls_date char(10);
  declare ls_regnr char(11);
  declare ls_nimi char(100);
  declare ls_enimi char(100);
  declare ls_invoices long varchar;
  declare ll_asutnr integer;
  declare ld_kuup date;
  delete from gl_logi;
  -- loe paringu sisu
  set ls_body=HTTP_BODY();
  delete from gl_dk_kanne;
  set ls_invoices='';
  set ll_count=0;
  set ll_count_add=0;
  set ll_count_rem=0;
  set ll_count_mis=0;
  set ll_count_unc=0;
  set ll_count_err=0;
  insert into gl_logi("text") values ('<?xml version="1.0" encoding="UTF-8"?>');
  insert into gl_logi("text") values ('<root>');
  if Length(ls_body)>0 then
--    select TO_CHAR(CSCONVERT(ls_body,'char_charset','UTF-8')) into ls_body;
    select CSCONVERT(ls_body,'char_charset','UTF-8') into ls_body;
    set ll_pos1=locate(ls_body,'<Personal>',1);
    if ll_pos1>0 then
      body_loop:
      loop
        set ll_pos1=locate(ls_body,'<Person>',1);
        if ll_pos1>0 then
          set ll_pos2=locate(ls_body,'</Person>',ll_pos1);
          if ll_pos2>ll_pos1 then
            set ll_count=ll_count+1;
            set ll_asutnr=null;
            -- 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 ls_kaader=Trim(get_xml_value('Kaader',ls_row));
            if Length(ls_kaader)>0 then
              set ls_regnr=Left(Trim(get_xml_value('Kood',ls_row)),11);
              set ls_nimi=Left(Trim(get_xml_value('Nimi',ls_row)),100);
              set ls_enimi=Left(Trim(get_xml_value('Enimi',ls_row)),50);
              if Length(ls_regnr)=11 AND Length(ls_nimi)>0 then
                set ll_asutnr=existid_asulist(ls_regnr,ls_nimi,'',ls_enimi);
                    if ll_asutnr>0 then
                    else
                      set ll_count_err=ll_count_err+1;
                    end if;
              else
                set ll_count_err=ll_count_err+1;
              end if;
            else
              -- message 'Ei ole isik'
            end if;
          else
            leave body_loop;
          end if
        else
          leave body_loop;
        end if;
      end loop body_loop;
      -- lisa kannetesse
      --call dk_set_doknr2;
      insert into gl_logi("text") values ('<Processed>'+convert(char(10),ll_count)+'</Processed>');
      insert into gl_logi("text") values ('<Invoices>'+ls_invoices+'</Invoices>');
      insert into gl_logi("text") values ('<Added>'+convert(char(10),ll_count_add)+'</Added>');
      --insert into gl_logi("text") values ('<Removed>'+convert(char(10),ll_count_rem)+'</Removed>');
      insert into gl_logi("text") values ('<Unchanged>'+convert(char(10),ll_count_unc)+'</Unchanged>');
      --insert into gl_logi("text") values ('<Missing>'+convert(char(10),ll_count_mis)+'</Missing>');
      insert into gl_logi("text") values ('<Error>'+convert(char(10),ll_count_err)+'</Error>');
    else
      insert into gl_logi("text") values ('<Failure>Element Kaader is missing</Failure>');
    end if;
  else
    insert into gl_logi("text") values ('<Failure>Body is missing</Failure>');
  end if;
  insert into gl_logi("text") values ('</root>');
  select CSCONVERT("text",'UTF-8') from gl_logi order by "id";
END