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.


Examine the code

Look at the try block in query.php. Here we build up a query function that will list AR invoices based on attributes of associated customers. Specifically, we’ll look for AR invoices where the customer ID starts with c or 1.

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

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

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

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

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.

$fields = ( new SelectBuilder() )->fields([ 'CUSTOMERID', 'CUSTOMERNAME' ])

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:

$res = ( new Query() )->select($fields)

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 across all their invoices.

     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. Comment out the sum aggregate operator and run the query again.

    // ->sum('TOTALDUE')

    The number of results will increase because the AR invoices are not being categorized into groups according to the customer ID. Instead, each AR invoice is returned.

  5. Uncomment the sum line and run the query again.

  6. 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. In the query.php file, change all occurrences of CUSTOMERID and CUSTOMERNAME to VENDORID and VENDORNAME.

  2. Similarly, change all occurrences of ARINVOICE to APBILL.

  3. Run the file:

    php query.php
  4. 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.
  5. (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