ALTER 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 ls_dim 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_arvenr char(35);
  declare ls_date char(10);
  declare ls_regnr char(11);
  declare ls_nimi char(100);
  declare ls_viitnr char(35);
  declare ls_konto char(7);
  declare ls_info char(200);
  declare ls_invoices long varchar;
  declare ll_objnr integer;
  declare ll_asutnr integer;
  declare ll_saajanr integer;
  declare ll_yhiknr integer;
  declare ll_talnr integer;
  declare ll_tulnr integer;
  declare ll_osaknr integer;
  declare ldc_summa decimal(15,2);
  declare ld_fikskuup date;
  declare ld_kuup date;
  declare ld_arvekuup date;
  declare ld_taskuup date;
  declare li_eakchannel smallint;
  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;
  select yld_fikskuup into ld_fikskuup from "dk_konfm" where "id"=1;
  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,'<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;
            set ll_objnr=0;
            set ll_asutnr=null;
            set ll_saajanr=null;
            set ll_talnr=null;
            set ll_tulnr=null;
            set ll_osaknr=null;
            -- 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_arvenr=Left(Trim(get_xml_value('documentType',ls_row)),35);
            if ls_arvenr='Invoice' then
              set ls_arvenr=Left(Trim(get_xml_value('documentNo',ls_row)),35);
              set ls_date=Left(Trim(get_xml_value('documentDate',ls_row)),10);
              if Length(ls_arvenr)>0 AND IsDate(ls_date)=1 then
                set ld_arvekuup=cast(ls_date as date);
                if ld_arvekuup>ld_fikskuup and ld_arvekuup<=ToDay() then
                  set ls_invoices=ls_invoices+ls_arvenr+',';
                  -- kande kuupaev
                  set ls_date=Left(Trim(get_xml_value('postingDate',ls_row)),10);
                  if IsDate(ls_date)=1 then
                    set ld_kuup=cast(ls_date as date);
                  else
                    set ld_kuup=ld_arvekuup;
                  end if;
                  if ld_kuup<=ld_fikskuup and ld_kuup>ToDay() then
                    set ld_kuup=ld_arvekuup;
                  end if;
                  -- tasumise kuupaev
                  set ls_date=Left(Trim(get_xml_value('dueDate',ls_row)),10);
                  if IsDate(ls_date)=1 then
                    set ld_taskuup=cast(ls_date as date);
                  else
                    set ld_taskuup=ld_arvekuup;
                  end if;
                  if ld_taskuup<=ld_arvekuup then
                    set ld_taskuup=ld_arvekuup;
                  end if;
                  -- asutus/isik
                  set ls_regnr=Left(Trim(get_xml_value('sellToCustomerNo',ls_row)),11);
                  set ls_nimi=Left(Trim(get_xml_value('sellToCustomerName',ls_row)),100);
                  if Length(ls_regnr)>0 AND Length(ls_nimi)>0 then
                    set ll_asutnr=existid_asulist(ls_regnr,ls_nimi,'','');
                    if ll_asutnr>0 then
                      -- kontrolli topelt arve
                      if not exists(select 1 from dk_kanne where arvekuup=ld_arvekuup and arvenr=ls_arvenr and asutnr=ll_asutnr) then
                        -- saaja
                        set ls_regnr=Left(Trim(get_xml_value('billToCustomerNo',ls_row)),11);
                        set ls_nimi=Left(Trim(get_xml_value('billToCustomerName',ls_row)),100);
                        if Length(ls_regnr)>0 AND Length(ls_nimi)>0 then
                          set ll_saajanr=existid_asulist(ls_regnr,ls_nimi,'','');
                        end if;
                        if ll_saajanr=ll_asutnr or ll_saajanr=0 then
                          set ll_saajanr=null
                        end if;
                        -- viitenumber
                        set ls_viitnr=Left(Trim(get_xml_value('referenceNo',ls_row)),35);
                        -- arvelduste objekti konto
                        set ls_konto=Left(Trim(get_xml_value('objectAccountNo',ls_row)),7);
                        if Length(ls_konto)>0 then
                          select min(objnr) into ll_objnr from "dk_obj" where "djkonto"=ls_konto;
                        else
                          set ll_objnr=0;
                        end if;
                        if ll_objnr=null then
                          set ll_objnr=0;
                        end if;
                        if ll_objnr>0 then
                          -- kande konto
                          set ls_konto=Left(Trim(get_xml_value('accountNo',ls_row)),7);
                          if Length(ls_konto)>0 then
                            set ls_konto=existid_kontlist(ls_konto);
                          else
                            set ls_konto=null;
                          end if;
                          -- yhik
                          set ls_nimi=Left(Trim(get_xml_value('unitOfMeasureCode',ls_row)),12);
                          if Length(ls_nimi)>0 then
                            set ll_yhiknr=existid_yhiklist(ls_nimi);
                          else
                            set ll_yhiknr=null;
                          end if;
                          -- info
                          set ls_info=Left(Trim(get_xml_value('description',ls_row)),200);
                          -- summa
                          set ls_nimi=Left(Trim(get_xml_value('amount',ls_row)),20);
                          set ldc_summa=convert(decimal, ls_nimi);
                          if ldc_summa<>0 then
                            -- klassifikaatorid
                            set ls_dim=Trim(get_xml_value('dimensions',ls_row));
                            if Length(ls_dim)>0 then
                              set ls_nimi=Trim(get_xml_value('TT',ls_dim));
                              if Length(ls_nimi)>0 then
                                set ll_talnr=existid_tallist(ls_nimi)
                              end if;
                              set ls_nimi=Trim(get_xml_value('TKL',ls_dim));
                              if Length(ls_nimi)>0 then
                                set ll_tulnr=existid_tullist(ls_nimi)
                              end if;
                              set ls_nimi=Trim(get_xml_value('ÜKSUS',ls_dim));
                              if Length(ls_nimi)>0 then
                                set ll_osaknr=existid_osaklist(ls_nimi)
                              end if;
                            end if;
                            -- lisa ajutine kanne
                            insert into gl_dk_kanne( objnr,tulnud,sisse,kuup,arvekuup,taskuup,arvenr,summa,summa2,kogus,yhiknr,hind,kbnr,alusnr,"info",konto,asutnr,saajanr,viitnr,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,partnr,user_code) 
                                values (ll_objnr,'web teenus',1,ld_kuup,ld_arvekuup,ld_taskuup,ls_arvenr,ldc_summa,0,0,ll_yhiknr,0,null,null,ls_info,ls_konto,ll_asutnr,ll_saajanr,ls_viitnr,null,null,ll_talnr,null,ll_tulnr,null,ll_osaknr,null,null,null,null,null,null,32767);
                            if sqlstate='00000' then
                              set ll_count_add=ll_count_add+1;
                            else
                              set ll_count_err=ll_count_err+1;
                            end if;
                          else
                            set ll_count_err=ll_count_err+1;
                          end if;
                        else
                          set ll_count_err=ll_count_err+1;
                        end if;
                      else
                        set ll_count_unc=ll_count_unc+1;
                      end if;
                    else
                      set ll_count_err=ll_count_err+1;
                    end if;
                  else
                    set ll_count_err=ll_count_err+1;
                  end if;
                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 arve'
            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 Invoices 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