CREATE PROCEDURE "DBA"."dk_payments"( )
RESULT( seller_regnr char(11), unique_code integer, invoice_date date, invoice_number char(20), "message" char(200), amount_attached decimal(15,2) )
BEGIN
  declare ls_u_regnr char(11);
  declare ll_asutnr integer;
  declare ls_arvenr char(20);
  declare ld_arvekuup date;
  declare ldc_summa decimal(15,2);
  declare ls_body long varchar;
  declare ls_row long varchar;
  declare ll_pos1 integer;
  declare ll_pos2 integer;
  declare ll_exists smallint;
  delete gl_dk_arvetas;
  -- loe paringu sisu
  set ls_body=HTTP_BODY();
  if Length(ls_body)>0 then
    body_loop:
    loop
      -- tsykkel kuni leidub json rida
      set ll_pos1=locate(ls_body,'{',1);
      if ll_pos1>0 then
        set ll_pos2=locate(ls_body,'}',ll_pos1);
        if ll_pos2>ll_pos1 then
          -- loe json rida
          set ls_row=substring(ls_body,ll_pos1+1, ll_pos2-ll_pos1);
          -- lõika ülejaanud sisu
          set ls_body=substring(ls_body,ll_pos2+1);
          -- loe rea elemendid
          set ls_u_regnr=get_json_value('"seller_regnumber"',ls_row);
          set ll_asutnr=convert(integer,get_json_value('"unique_code"',ls_row));
          set ls_arvenr=get_json_value('"invoice_number"',ls_row);
          set ld_arvekuup=convert(date,get_json_value('"invoice_date"',ls_row));
          -- kui vaartused olemas
          if Length(ls_u_regnr)>0 and ll_asutnr>0 and Length(ls_arvenr)>0 and ld_arvekuup>ymd(2010,12,31) then
            -- kas asutus/isik billme klient
            set ll_exists=0;
            select 1 into ll_exists from asulist where asutnr=ll_asutnr and eakchannel=7;
            if ll_exists=1 then
              select sum(kp_kanne.summa) into ldc_summa from kp_kanne key left outer join kp_obj where get_u_regnr(kp_obj.skoop)=ls_u_regnr and kp_kanne.asutnr=ll_asutnr and kp_kanne.arvekuup=ld_arvekuup and kp_kanne.arvenr=ls_arvenr;
              if ldc_summa is null then
                set ldc_summa=0
              end if;
              insert into gl_dk_arvetas(doknr,asutnr,arvekuup,arvenr,"info",makssumma) values (ls_u_regnr,ll_asutnr,ld_arvekuup,ls_arvenr,'',ldc_summa);
            else
              insert into gl_dk_arvetas(doknr,asutnr,arvekuup,arvenr,"info",makssumma) values (ls_u_regnr,ll_asutnr,ld_arvekuup,ls_arvenr,'Not client',0);
            end if;
          end if;
        else
          leave body_loop;
        end if
      else
        leave body_loop;
      end if;
    end loop body_loop;
  end if;
  select doknr,asutnr,arvekuup,arvenr,"info",makssumma
    from gl_dk_arvetas;
END