CREATE PROCEDURE "dba"."perfi_paymentnotifications"()
RESULT( vastus char(100))
BEGIN
  declare ls_arveid char(30);
  declare ls_arvenr char(30);
  declare ls_u_masut char(100);
  declare ls_u_regnr char(30);
  declare ls_u_aadr char(130);
  declare ls_u_kmkr char(30);
  declare ls_asutnr1 char(130);
  declare ls_aadr char(130);
  declare ls_viitnr char(35);
  declare ls_regnr char(11);
  declare ls_meil char(50);
  declare ls_yhik char(50);
  declare ls_sisu char(250);
  declare ld_peralg date;
  declare ld_perlop date;
  declare ld_arvekuup date;
  declare ld_taskuup date;
  declare ll_id integer;
  declare ll_objnr integer;
  declare ll_asutnr integer;
  declare ll_count integer;
  declare ll_count_ridu integer;
  declare ldc_kogus decimal(18,6);
  declare ldc_hind decimal(18,6);
  declare ldc_kb_protsent decimal(15,3);
  declare ldc_summa decimal(15,2);
  declare ldc_summa2 decimal(15,2);
  declare ldc_koksumma decimal(15,2);
  declare ldc_koksumma2 decimal(15,2);
  declare err_notfound exception for sqlstate value '02000';
  declare c_asulist dynamic scroll cursor for select asutnr from asulist where eakchannel=9;
  declare c_arve dynamic scroll cursor for select distinct objnr,arvenr,arvekuup,asutnr from gl_tr_arve;
  declare c_ridu dynamic scroll cursor for
    SELECT "gl_tr_arve"."id",
           "gl_tr_arve"."sisu",
           "gl_tr_arve"."kogus",
           "gl_tr_arve"."hind",
           "gl_tr_arve"."kb_protsent",
           "gl_tr_arve"."yhik",
           "gl_tr_arve"."summa",
           "gl_tr_arve"."summa2"
      FROM "gl_tr_arve" LEFT OUTER JOIN "asulist" ON "gl_tr_arve"."asutnr"="asulist"."asutnr" LEFT OUTER JOIN "dk_arve_komb" ON "gl_tr_arve"."kombid"="dk_arve_komb"."id"
      WHERE "gl_tr_arve"."objnr"=ll_objnr and "gl_tr_arve"."arvenr"=ls_arvenr and "gl_tr_arve"."arvekuup"=ld_arvekuup and "gl_tr_arve"."asutnr"=ll_asutnr;
  set ld_peralg=get_peralg(ToDay());
  set ld_perlop=get_perlop(ld_peralg);
  delete from gl_2;
  open c_asulist;
  asulist_loop: loop
    fetch next c_asulist into ll_asutnr;
    if sqlstate = err_notfound then -- kui eof
      leave asulist_loop
    end if;
    insert into gl_2("id","aasta")
      select first "dk_kanne"."id","dk_kanne"."aasta" from dk_kanne key left outer join dk_obj where dk_kanne.asutnr=ll_asutnr and dk_obj.spank=0 and dk_kanne.arvekuup>=ld_peralg and dk_kanne.arvekuup<=ld_perlop;
  end loop asulist_loop;
  close c_asulist;
  call dk_arve_koond();
  delete from gl_logi;
  insert into gl_logi("text") values ('<?xml version="1.0" encoding="UTF-8"?>');
  insert into gl_logi("text") values ('<Documents xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">');
  set ll_count=0;
  open c_arve;
  arve_loop: loop
    fetch next c_arve into ll_objnr,ls_arvenr,ld_arvekuup,ll_asutnr;
    if sqlstate = err_notfound then -- kui eof
      leave arve_loop
    end if;
    set ll_count=ll_count+1;
    set ll_count_ridu=0;
    set ldc_koksumma=0;
    set ldc_koksumma2=0;
    SELECT FIRST
         if "dk_arve_komb"."myyja_nimi" is null then get_u_masut("gl_tr_arve"."skoop") else "dk_arve_komb"."myyja_nimi" endif as u_masut,
         if "dk_arve_komb"."myyja_nimi" is null or "dk_arve_komb"."myyja_regnr" is null then get_u_regnr("gl_tr_arve"."skoop") else "dk_arve_komb"."myyja_regnr" endif as u_regnr,
         if "dk_arve_komb"."myyja_nimi" is null or "dk_arve_komb"."myyja_aadr1" is null or "dk_arve_komb"."myyja_aadr2" is null then get_u_aadr("gl_tr_arve"."skoop") else "dk_arve_komb"."myyja_aadr1" + ' ' + "dk_arve_komb"."myyja_aadr2" + ' ' + "dk_arve_komb"."myyja_aadr3" endif as u_aadr,
         get_u_kmkr("gl_tr_arve"."skoop") as u_kmkr,
         get_asulist_nimi("gl_tr_arve"."asutnr") as asutnr1,
         LTrim("asulist"."aadr1" + ' ') + LTrim("asulist"."aadr2" + ' ') + LTrim("asulist"."aadr3" + ' ') + Trim("asulist"."pi") as aadr,
         get_arvesaaja_meil("gl_tr_arve"."asutnr","gl_tr_arve"."saajanr") as asulist_meil,
         "gl_tr_arve"."viitnr",
         "gl_tr_arve"."taskuup"

      INTO ls_u_masut,ls_u_regnr,ls_u_aadr,ls_u_kmkr,ls_asutnr1,ls_aadr,ls_meil,ls_viitnr,ld_taskuup
      FROM "gl_tr_arve" LEFT OUTER JOIN "asulist" ON "gl_tr_arve"."asutnr"="asulist"."asutnr" LEFT OUTER JOIN "dk_arve_komb" ON "gl_tr_arve"."kombid"="dk_arve_komb"."id"
      WHERE "gl_tr_arve"."objnr"=ll_objnr and "gl_tr_arve"."arvenr"=ls_arvenr and "gl_tr_arve"."arvekuup"=ld_arvekuup and "gl_tr_arve"."asutnr"=ll_asutnr;
    insert into gl_logi("text") values ('<Document>');
    set ls_arveid=ls_arvenr+'_'+dateformat(ld_arvekuup,'ddmmyyyy')+'_'+cast(ll_asutnr as char(10))+'_'+cast(ll_objnr as char(10));
    insert into gl_logi("text") values ('<DocumentID>'+ls_arveid+'</DocumentID>');
    insert into gl_logi("text") values ('<SenderName>'+ls_u_masut+'</SenderName>');
    insert into gl_logi("text") values ('<SenderAddress>'+ls_u_aadr+'</SenderAddress>');
    insert into gl_logi("text") values ('<SenderCode>'+ls_u_regnr+'</SenderCode>');
    insert into gl_logi("text") values ('<SenderVatCode>'+ls_u_kmkr+'</SenderVatCode>');
    --insert into gl_logi("text") values ('<SenderIBAN/>');
    insert into gl_logi("text") values ('<PUN>'+ls_viitnr+'</PUN>');
    insert into gl_logi("text") values ('<ReceiverName>'+ls_asutnr1+'</ReceiverName>');
    if length(ls_aadr)>0 then
      insert into gl_logi("text") values ('<ReceiverAddress>'+ls_aadr+'</ReceiverAddress>');
    end if;
    if length(ls_regnr)>0 then
      insert into gl_logi("text") values ('<ReceiverCode>'+ls_regnr+'</ReceiverCode>');
    end if;
    if length(ls_meil)>0 then
      insert into gl_logi("text") values ('<ReceiverEmail>'+ls_meil+'</ReceiverEmail>');
    end if;
    insert into gl_logi("text") values ('<PaymentRef>'+ls_viitnr+'</PaymentRef>');
    insert into gl_logi("text") values ('<DocNumber>'+ls_arvenr+'</DocNumber>');
    insert into gl_logi("text") values ('<DocDate>'+dateformat(ld_arvekuup,'yyyy-mm-dd')+'</DocDate>');
    insert into gl_logi("text") values ('<DocDueDate>'+dateformat(ld_taskuup,'yyyy-mm-dd')+'</DocDueDate>');

    insert into gl_logi("text") values ('<Items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">');

    open c_ridu;
    ridu_loop: loop
      fetch next c_ridu into ll_id,ls_sisu,ldc_kogus,ldc_hind,ldc_kb_protsent,ls_yhik,ldc_summa,ldc_summa2;
      if sqlstate = err_notfound then -- kui eof
        leave ridu_loop
      end if;
      set ll_count_ridu=ll_count_ridu+1;
      set ldc_koksumma=ldc_koksumma+ldc_summa;
      set ldc_koksumma2=ldc_koksumma2+ldc_summa2;

--      insert into gl_logi("text") values ('<Item typeCode="'+cast(ll_count_ridu as char(10))+'">');
      insert into gl_logi("text") values ('<Item>');
      insert into gl_logi("text") values ('<ID>'+cast(ll_id as char(10))+'</ID>');
      insert into gl_logi("text") values ('<DocumentID>'+ls_arveid+'</DocumentID>');
      insert into gl_logi("text") values ('<ItemLineNo>'+cast(ll_count_ridu as char(10))+'</ItemLineNo>');
      insert into gl_logi("text") values ('<ItemDescription>'+ls_sisu+'</ItemDescription>');
      insert into gl_logi("text") values ('<ItemUnit>'+ls_yhik+'</ItemUnit>');
      insert into gl_logi("text") values ('<ItemQty>'+cast(ldc_kogus as char(18))+'</ItemQty>');
      insert into gl_logi("text") values ('<ItemNetPrice>'+cast(ldc_hind as char(18))+'</ItemNetPrice>');
      insert into gl_logi("text") values ('<NetAmount>'+cast(ldc_summa as char(15))+'</NetAmount>');
      insert into gl_logi("text") values ('<VatRate>'+cast(ldc_kb_protsent as char(18))+'</VatRate>');
      insert into gl_logi("text") values ('<VatAmount>'+cast(ldc_summa2 as char(15))+'</VatAmount>');
      insert into gl_logi("text") values ('<GrossAmount>'+cast(ldc_summa+ldc_summa2 as char(15))+'</GrossAmount>');
      insert into gl_logi("text") values ('<ItemGroupId>1</ItemGroupId>');
      insert into gl_logi("text") values ('</Item>');
    end loop ridu_loop;
    close c_ridu;

    insert into gl_logi("text") values ('</Items>');
    insert into gl_logi("text") values ('<ItemsCalculatedTotals>');
    insert into gl_logi("text") values ('<Total itemsCount="'+cast(ll_count_ridu as char(10))+'">');
    insert into gl_logi("text") values ('<NetAmount>'+cast(ldc_koksumma as char(15))+'</NetAmount>');
    insert into gl_logi("text") values ('</Total>');
    insert into gl_logi("text") values ('</ItemsCalculatedTotals>');


    insert into gl_logi("text") values ('<CurrencyCode>EUR</CurrencyCode>');
    insert into gl_logi("text") values ('<DocAmount>'+cast(ldc_koksumma as char(15))+'</DocAmount>');
    insert into gl_logi("text") values ('<DocVatAmount>'+cast(ldc_koksumma2 as char(15))+'</DocVatAmount>');
    insert into gl_logi("text") values ('<DocTotalAmount>'+cast(ldc_koksumma+ldc_koksumma2 as char(15))+'</DocTotalAmount>');
    insert into gl_logi("text") values ('<DocBalanceAmount>0</DocBalanceAmount>');
    insert into gl_logi("text") values ('<DocPayAmount>'+cast(ldc_koksumma+ldc_koksumma2 as char(15))+'</DocPayAmount>');
    --insert into gl_logi("text") values ('<DocMetadata>*</DocMetadata>');
    insert into gl_logi("text") values ('<GeneratedDate>'+dateformat(Now(),'yyyy-mm-ddThh:mm:ss')+'</GeneratedDate>');
    insert into gl_logi("text") values ('<DocTypeCode>Invoice</DocTypeCode>');
    insert into gl_logi("text") values ('<SenderPrefix>PMEN</SenderPrefix>');
    --insert into gl_logi("text") values ('<DocMetadataExt/>');





    insert into gl_logi("text") values ('</Document>');

  end loop arve_loop;
  close c_arve;

  insert into gl_logi("text") values ('</Documents>');
  select CSCONVERT("text",'UTF-8') from gl_logi order by "id";

END