CREATE PROCEDURE "dba"."dk_insert_invoices"( /* [IN | OUT | INOUT] parameter_name parameter_type [DEFAULT default_value], ... */ )
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_add integer;
  declare ll_count_rem integer;
  declare ll_count_mis integer;
  declare ll_count_unc integer;
  declare ll_count_err integer;
  declare ls_kood long varchar;
  declare ls_state long varchar;
  declare ls_invoices long varchar;
  declare ll_asutnr integer;
  declare li_eakchannel smallint;
  delete from gl_logi;
  -- loe paringu sisu
  set ls_body=HTTP_BODY();
  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
    set ll_pos1=locate(ls_body,'<Invoices>',1);
    if ll_pos1>0 then
      body_loop:
      loop
        set ll_pos1=locate(ls_body,'<Row>',1);
        if ll_pos1>0 then
          set ll_pos2=locate(ls_body,'</Row>',ll_pos1);
          if ll_pos2>ll_pos1 then
            set ll_count=ll_count+1;
            -- loe xml rida
            set ls_row=substring(ls_body,ll_pos1+5, ll_pos2-ll_pos1-5);
            -- loika ylejaanud sisu
            set ls_body=substring(ls_body,ll_pos2+6);
            -- loe rea elemendid
            set ls_kood=Left(Trim(get_xml_value('InvoiceNumber',ls_row)),35);
            set ls_state=Left(Trim(get_xml_value('InvoiceDate',ls_row)),1);
            if Length(ls_kood)>0 AND Length(ls_state)>0 then
              set ls_invoices=ls_invoices+ls_kood+','
            else
              set ll_count_err=ll_count_err+1;
            end if;
          else
            leave body_loop;
          end if
        else
          leave body_loop;
        end if;
      end loop body_loop;
      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 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