HORNEKS ANS

ACF Library

ContaInvoicePayments

Back to List

Description: Import Payments from Conta and link it to Orders in FM system

FileMaker Prototype: 

Set Variable [$res; ACF_Run("ContaInvoicePayments"; date_fromDate)]

Category: CONTA API

Dependencies

NOTE: This function serves as a reference template. To use it effectively, you may need to adjust certain parts of the code, such as field names, database structures, and specific data lists to fit the requirements of your system. This customization allows the function to work seamlessly within your environment.

Function source:

function ContaInvoicePayments ( date fromDate )

   date toDate = date(now()); 
   string apiParams = "?page=%d&fromDate=%s&toDate=%s"; 
   array int pContaID, pInvoiceID, pInvoiceNo;
   array date pPaymentDate;
   int pageNo = 0; 
   
   // Get the payments we allready have, so we can skip them when getting data from the API
   string sql = "SELECT ContaID, InvoiceID, InvoiceNo, PaymentDate FROM Payments
   WHERE PaymentDate >= :fromDate AND PaymentDate <= :toDate
   
   INTO :pContaID, :pInvoiceID, :pInvoiceNo, :pPaymentDate"; 
   
   string res = ExecuteSQL ( sql ) ; 
   if ( res != "OK") then
      throw "SQL Select Error: in " + sql + "\n"+res; 
   end if
   int origCount = sizeof (pInvoiceID); 
   // Prepare doing the request
   JSON apidata = getContaApiKeyandNumber(); 
   if ( apidata["APIkey"] == "") then
      alert ("No API key found"); 
      return "ERROR: Missing APIkey"; 
   end if
   
   string APIkey = apidata["APIkey"]; 
   bool production = (apidata["Production"]=="1");
   int compOrg = int(apidata["CompNo"]); 
   
   string hdr = 'apiKey:'+APIkey;
   string dateFormat = "%Y-%m-%d";  
   // /invoice/organizations/{opContextOrgId}/invoice-payments
   string url,pre; 
   int i,x, apiHits,apiPages, hits, hitID, hitCuID, hitInvID, hitInvNo;
   float hitAm; 
   date hitPayDateD;
   string hitPayDate;  
   
   JSON apiRes;
   // Start of the SQL insert statement, values part is added later in the function. 
   string insertSQL = "INSERT INTO Payments (ContaID,InvoiceID,  InvoiceNo, PaidAmount, PaymentDate, CustomerID) ";
   array string insValues; 
   float sumImported = 0.0; 
   apiPages = 0; 
   // The API returns a certain number of 
   repeat // All pages in the query-result. 
      url = getContaBaseURL(production)+"/invoice/organizations/"+compOrg+"/invoice-payments"
      + format (apiParams, pageNo, string(fromDate, dateFormat), string (toDate, dateFormat)); 
   
      print "\n"+url+"\n"; 
   
      res = HTTP_GET ( url, hdr);
      print "\n"+res;
   
      // Verify the result- 
      clear(apiRes);
      apiRes = res; 
      if (ContaVerifyAPIresponce(apiRes)) then
         apiHits = int(apiRes["hitCount"]);
         apiPages = int(apiRes["pageCount"])-1; 
         if ( apiHits == 0) then
            break; 
         end if
         print format("\nPages %d, hits %d\n", apiHits, apiPages); 
         
         hits = sizeof (apiRes["hits"]);
         for (i=1, hits)
            pre = format ("hits[%d].", i); 
            hitID = int ( apiRes[pre+"id"]);
            hitCuID = int ( apiRes[pre+"customerId"]);
            hitInvID = int ( apiRes[pre+"invoiceId"]);
            hitInvNo = int ( apiRes[pre+"invoiceNo"]);
            hitPayDate = apiRes[pre+"date"];
            hitPayDateD = date(hitPayDate,"%Y-%m-%d");
            hitAm = float (apiRes[pre+"amount"]);
            x = find_in_arrayInt (pContaID, hitID ); 
            if ( x < 0) then
               sumImported += hitAm; 
               insValues[] = format("(%d, %d, %d, %f, DATE '%s', %d)", hitID,hitInvID, hitInvNo,hitAm,hitPayDate, hitCuID);
               pContaID[] = hitID;
            else
               // Verify the stored occurence with the found. 
               if (x<=origCount) then
                  if (hitInvID != pInvoiceID[x] || hitInvNo != pInvoiceNo[x] || hitPayDateD != pPaymentDate[x]) then
                     alert ( format("Stored payment does not match retrieved, invoiceNo: %d, amount=%.2f", hitInvNo,hitAm));
                  end if
               end if
            end if
         
         end for
         pageNo++;
      end if
   until (pageNo>apiPages);
   
   // Should we add any ? 
   if ( sizeof (insValues)>0) then
      sql = insertSQL+"VALUES " + implode (",", insValues);
      print "\n" + sql; 
      res = ExecuteSQL (sql); 
      if ( res != "") then
         throw "Error Insert SQL: " + res; 
      end if
   end if
   
   // Do the Update of the OrderFK field. 
   // We use a subquery in this construct as FileMaker SQL does not allow JOIN in UPDATE. 
   sql = "UPDATE Payments 
SET OrderFK = (SELECT PrimaryKey FROM Orders WHERE Orders.InvoiceNo = Payments.InvoiceNo)
WHERE Payments.OrderFK IS NULL OR Payments.OrderFK = ''";
   res = executeSQL (sql ); 
   if ( res != "") then
      print sql + "\n"; 
      throw "Error Update relations SQL: " + res; 
   end if
   return format ( "OK: %d payments imported, Sum %.2f", sizeof (insValues), sumImported); 
end

The ContaInvoicePayments function retrieves payments from the Conta Accounting system and adds records to the Payments table in the FileMaker solution. The API returns payments registered within a specified date range, set here to go back 35 days.

Before collecting new payments, the function queries the Payments table to compile a list of previously imported payments within the same date range. As it processes the payments, it checks against this list to skip any already-imported entries, importing only new ones into the Payments table. Finally, the function links all retrieved payments to the Orders table to ensure accurate calculations. This setup enables a portal showing registered payments within the system.

While managing and updating payment data in Conta remains crucial, the ability to view orders and track which ones are paid is essential when accepting new orders.

Example

Set Variable [$res; ACF_Run("ContaInvoicePayments"; Get(CurrentDate)-35)]

This retrieves and processes all payments from the past 35 days, skipping any that have already been imported.

A little special remark regarding the last linking of the payments to the orders. Suppose we have a OrderFK field in the payments table, but we also have InvoiceNo in both tables. We could have the relation on InvoiceNo, and that would work great. As we are accustomed to use the PrimaryKey in relations, there is a good idea to use that instead. For this to work, we do a final SQL Update to ensure the relational linking work between the Orders table and the Payments table. 

UPDATE Payments 
SET OrderFK = (SELECT PrimaryKey FROM Orders WHERE Orders.InvoiceNo = Payments.InvoiceNo)
WHERE Payments.OrderFK IS NULL OR Payments.OrderFK = ''

The SQL processes all records in the Payments table where the OrderFK is null or has an empty value. For each, we use a subquery to select the PrimaryKey in the Orders table and set it to OrderFK.

Some SQL dialects allow us to use relations when updating instead, but not FileMaker SQL. This method works OK.

Back to List