ALTER PROCEDURE "DBA"."dk_payments_balance"( )
RESULT( seller_regnr char(11), unique_code integer, on_date date, invoice_number char(20), "message" char(200), debit decimal(15,2), credit decimal(15,2) )
BEGIN
  declare ls_u_regnr char(11);
  declare ll_skoop integer;
  declare ll_objnr integer;
  declare ls_djkonto char(7);
  declare ll_asutnr integer;
  declare ls_arvenr char(20);
  declare ld_arvekuup date;
  declare ld_kuup date;
  declare ls_kuup char(30);
  declare ldc_summa decimal(15,2);
  declare ldc_saldo 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=Left(get_json_value('"seller_regnumber"',ls_row),11);
          set ll_asutnr=convert(integer,get_json_value('"unique_code"',ls_row));
          set ls_arvenr=Left(get_json_value('"invoice_number"',ls_row),20);
          set ls_kuup=Left(get_json_value('"invoice_date"',ls_row),30);
          if isdate(ls_kuup)=1 then
            set ld_arvekuup=convert(date,ls_kuup)
          else
            set ld_arvekuup=ymd(2000,1,1)
          end if;
          set ls_kuup=Left(get_json_value('"on_date"',ls_row),30);
          if isdate(ls_kuup)=1 then
            set ld_kuup=convert(date,ls_kuup)
          else
            set ld_kuup=ymd(2000,1,1)
          end if;
          if ld_kuup<ymd(2010,1,1) then
            set ld_kuup=Now()
          end if;
          -- kui vaartused olemas
          if ll_asutnr>0 then
            -- kas asutus/isik billme klient
            set ll_exists=1;
            --select 1 into ll_exists from asulist where asutnr=ll_asutnr and eakchannel=7;
            if ll_exists=1 then
              --otsi arvelduste objekt
              select first dk_kanne.objnr,dk_obj.djkonto,dk_obj.skoop into ll_objnr,ls_djkonto,ll_skoop from dk_kanne key left outer join dk_obj where get_u_regnr(dk_obj.skoop)=ls_u_regnr and dk_kanne.asutnr=ll_asutnr and (dk_kanne.arvekuup=ld_arvekuup or ld_arvekuup=ymd(2000,1,1)) and dk_kanne.arvenr=ls_arvenr;
              -- summeeri laekumised
              select sum(kp_kanne.summa) into ldc_summa from kp_kanne key left outer join kp_obj where kp_obj.skoop=ll_skoop and kp_kanne.konto=ls_djkonto and kp_kanne.asutnr=ll_asutnr and kp_kanne.kuup<=ld_kuup;
              if ldc_summa is null then
                set ldc_summa=0
              end if;
              -- summeeri arved
              select sum(dk_kanne.summa+dk_kanne.summa2) into ldc_saldo from dk_kanne key left outer join dk_obj where dk_kanne.objnr=ll_objnr and dk_kanne.asutnr=ll_asutnr and dk_kanne.arvekuup<=ld_kuup;
              if ldc_saldo is null then
                set ldc_saldo=0
              end if;
              set ldc_saldo=ldc_saldo-ldc_summa;
              if ldc_saldo>0 then
                insert into gl_dk_arvetas(doknr,asutnr,arvekuup,arvenr,"info",arvesumma,makssumma) values (ls_u_regnr,ll_asutnr,ld_kuup,ls_arvenr,'',ldc_saldo,0)
              else
                insert into gl_dk_arvetas(doknr,asutnr,arvekuup,arvenr,"info",arvesumma,makssumma) values (ls_u_regnr,ll_asutnr,ld_kuup,ls_arvenr,'',0,Abs(ldc_saldo))
              end if;
            else
              insert into gl_dk_arvetas(doknr,asutnr,arvekuup,arvenr,"info",arvesumma,makssumma) values (ls_u_regnr,ll_asutnr,ld_kuup,ls_arvenr,'Not client',0,0);
            end if;
          else
            insert into gl_dk_arvetas(doknr,asutnr,arvekuup,arvenr,"info",arvesumma,makssumma) values (ls_u_regnr,ll_asutnr,ld_kuup,ls_arvenr,'Client is missing',0,0);
          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",arvesumma,makssumma
    from gl_dk_arvetas;
END