NEW!

This example uses the new Query function to list AR invoices based on attributes of associated customers.

In addition, the query uses a sum aggregate operator to provide information about how much a given customer owes across all invoices.


Prerequisites


Examine the code

Look at the try block in query.php.

The code first constructs an OrOperator, seeking customer IDs that start with c or 1.

Next, the OrderBuilder sets a a descending sort order based on the value of CUSTOMERID.

The SelectBuilder chooses two fields to return, CUSTOMERID and CUSTOMERNAME, and includes the sum aggregate operator for TOTALDUE. When used in a Query of AR invoices, this adds the values of the TOTALDUE parameters across all invoices. When using aggregate functions, the fields in the select element are used to group the results. So, the results will be grouped by CUSTOMERID first.

The Query constructor pulls the pieces together and specifies the target of the query, ARINVOICE. The constructor also specifies a case insensitive filter and a pagesize of 100:

$filter = new OrOperator([ ( new Filter('CUSTOMERID') )->like('c%'),
                           ( new Filter('CUSTOMERID') )->like('1%') ]);

$order = ( new OrderBuilder())->descending('CUSTOMERID')->getOrders();

$fields = ( new SelectBuilder() )->fields([ 'CUSTOMERID', 'CUSTOMERNAME' ])
                                 ->sum('TOTALDUE')
                                 ->getFields();

$res = ( new Query() )->select($fields)
                      ->from('ARINVOICE')
                      ->filter($filter)
                      ->caseInsensitive(true)
                      ->pageSize('100')
                      ->orderBy($order);

Run the example

  1. Run the query.php file in a terminal:

    php query.php
    
  2. Examine the output, which gives the total number of results and prints the first one.

    The following sample result shows that YellowHammer (CUSTOMERID 10003) owes 15016.44.

     Success! Total number of results:2
        
     First ARINVOICE result found:
         'CUSTOMERID' => '10003'
         'CUSTOMERNAME' => 'YellowHammer'
         'SUM.TOTALDUE' => '15016.44'
     See the log file (logs/intacct.html) for the complete list of results.
    
  3. If you don’t get any results, comment out the filter in the Query constructor so that all ARINVOICE objects will be returned and rerun the the example.

    // ->filter($filter)
    
  4. Open the generated logs/intacct.html file in an HTML browser and review the entries.

    You should see information such as the following:

    Log file contents with sample results

    Note that the log file was created by the logger set up in the bootstrap.php in the project root


Extra credit

Try a different object

Modify query.php to perform a different query. For example, you might want to calculate the total amount due to a given vendor across all the bills.

  1. Replace the beggining of the try block with the following code, pasting up to and over the line starting with $logger.

     $filter =  (new Filter('VENDORID') )->equalTo('20000');
    
     $fields = ( new SelectBuilder() )->fields([ 'VENDORID', 'VENDORNAME' ])
         ->sum('TOTALDUE')
         ->count('RECORDNO')
         ->getFields();
    
     $res = ( new Query() )->select($fields)
         ->from('APBILL')
         ->filter($filter)
         ->pageSize('100');
    
     $logger->info('Executing query to Intacct API');
    
  2. Run the file:

    php query.php
    
  3. Examine the output, which will look something like this:

     Success! Total number of results: 12
        
     First ARINVOICE result found:
         'VENDORID' => '20000'
         'VENDORNAME' => 'Acme Co.
         'SUM.TOTALDUE' => '418138.31'
         'COUNT.RECORDNO' => '18'
     See the log file (logs/intacct.html) for the complete list of results.
    
  4. (Optional) Update the the echo statements in the if/else blocks so that they are for APBILL instead of ARINVOICE objects.


What’s next?

Provide feedback