Betaalstatus Exact Online synchroniseren naar WooCommerce

Wiki (nl)
,
1

De volgende applicatiemodule voor gebruik met Invantive Cloud synchroniseert de betaalstatus uit Exact Online naar WooCommerce van WooCommerce-orders.

Bij gebrek aan een WooCommerce-SQL driver voor Invantive SQL wordt de WooCommerce-API rechtstreeks geadresseerd.

declare
  l_cnt             pls_integer;
  l_consumer_key    varchar2 := 'my-id';
  l_consumer_secret varchar2 := 'secret';
  l_woo_eol_prefix  varchar2 := 'B2B order';
  l_site            varchar2 := 'https://acme.com';
begin
  cloud_http.set_use_template(true);
  cloud_http.set_template_step_name('Synchroniseren betaalstatus Exact Online naar WooCommerce');
  --
  -- Invoices open according to Exact Online.
  --
  create or replace table OpenVolgensExact@InMemoryStorage
  as
  select sie.invoicenumber InvoiceNumber
  ,      to_number(replace(sie.yourref, l_woo_eol_prefix, '')) WooCommerceOrderNummer
  from   aroutstandingitems@eol aom
  join   salesinvoices@eol sie
  on     sie.invoicenumber = aom.number_attr
  and    sie.yourref like l_woo_eol_prefix || '%'
  ; 
  --
  -- Invoices open according to WooCommerce.
  --
  create or replace table OpenVolgensWooCommerce@InMemoryStorage
  as
  select WooCommerceOrderNummer
  from   ( select jte.id WooCommerceOrderNummer
           ,      l_woo_eol_prefix || to_char(jte.id) ExactYourRef
           from   httpdownload@datadictionary
                  ( url => l_site || '/wp-json/wc/v3/orders/?status=pending&per_page=100'
                  , contentType => 'application/json'
                  , basicAuthenticationUser => l_consumer_key
                  , basicAuthenticationPassword => l_consumer_secret
                  ) htp
           join   jsontable
                  ( '[*]'
                    passing htp.contents_char
                    columns id integer path 'id'
                  ) jte
         ) woo
  --
  -- The invoice must also exist already in Exact Online;
  -- it might not yet have synced to Exact Online.
  --
  join   salesinvoices@eol sie
  on     sie.yourref = woo.ExactYourRef
  ;
  --
  -- Mark orders as paid in WooCommerce.
  --
  for r 
  in
  ( select WooCommerceOrderNummer
    from   OpenVolgensWooCommerce@InMemoryStorage
    minus
    select WooCommerceOrderNummer
    from   OpenVolgensExact@InMemoryStorage
    order
    by     WooCommerceOrderNummer
  )
  loop
    --
    -- Update
    --
    select count(*)
    into   l_cnt
    from   httpdownload@datadictionary
           ( url => l_site || '/wp-json/wc/v3/orders/' || to_char(r.WooCommerceOrderNummer)
           , method => 'POST'
           , textPayload => '{"status": "completed"}'
           , contentType => 'application/json'
           , basicAuthenticationUser => l_consumer_key
           , basicAuthenticationPassword => l_consumer_secret
           ) htp
    ;      
    cloud_http.append_line_to_response_body_text
    ( '<p>WooCommerce order #' || to_char(r.WooCommerceOrderNummer) || ' gemerkt als betaald.');    
  end loop;
end;