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.
.credentials.inifile required by
Look at the
try block in
The code first constructs an
OrOperator, seeking customer IDs that start with
OrderBuilder sets a a descending sort order based on the value of
SelectBuilder chooses two fields to return,
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
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);
query.php file in a terminal:
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.
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.
Open the generated
logs/intacct.html file in an HTML browser and review the entries.
You should see information such as the following:
Note that the log file was created by the logger set up in the
bootstrap.php in the project root
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.
Replace the beggining of the
try block with the following code, pasting up to and over the line starting with
$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');
Run the file:
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.
(Optional) Update the the echo statements in the
else blocks so that they are for APBILL instead of ARINVOICE objects.