AccPac report

AccPac report can be downloaded from BE Invoice. Select a few invoices; then choose Export dropdown, choose AccPac.

Terms: PBI: Pay By Invoice. For AccPac, PBI orders are exported differently than non-PBI orders.

Code: ConvertToAccpac.php

Steps to build AccPac report:

First; grab all the headers (orders)
Query: 

$orderHeaders = $connection->fetchAll($queryGetHeaders);

Order headers are grouped by OrdUniq. OrdUniq are either Accounting Label ID (or sales_payment's po_number; in case the order is PBI). Note that AccPac report is supposed to be exported only once per day; therefore the OrdUniq should always be unique. Look into the raw SQL query GROUP BY to know more.

Second; grab all the details from `sales_invoice_item` :

$orderSkuRows = $connection->fetchAll($queryGetSkuRows);

$orderSkuRows are grouped by OrdUniq (see above); and then grouped by AccPac SKU. 

With the $orderHeaders and $orderSkuRows, we are ready to build the report. 

Example built report look like this:

For a PBI order:

RECTYPE,ORDUNIQ,ORDNUMBER,CUSTOMER,SHPNAME,SHPADDR1,SHPADDR2,SHPADDR3,SHPADDR4,SHPCITY,SHPSTATE,SHPZIP,SHPCOUNTRY,SHPPHONE,SHPFAX,SHPCONTACT,PONUMBER,TYPE,ORDDATE,EXPDATE,LOCATION,TAMOUNT1,AUTOTAXCAL
RECTYPE,ORDUNIQ,LINENUM,LINETYPE,ITEM,MISCCHARGE,CATEGORY,LOCATION,QTYORDERED,PRIUNTPRC,EXTINVMISC,INVDISC,TAMOUNT1,COMMINST
RECTYPE,ORDUNIQ,PAYMENT
RECTYPE,ORDUNIQ,UNIQUIFIER,DETAILNUM,COINTYPE,COIN
RECTYPE,ORDUNIQ,UNIQUIFIER
RECTYPE,ORDUNIQ,OPTFIELD
RECTYPE,ORDUNIQ,LINENUM,OPTFIELD
1,239143,239143-081224,239143,"","","","","","","","","",,,"","this is where po number should be",1,20240812,20240812,03MAIL,0.0000,0.0000
2,239143,220300,1,220525,,STDCAT,03MAIL,1,48.000000000000,48.0000,7.2000,0.0000,0
2,239143,220301,1,220520,,STDCAT,03MAIL,1,48.000000000000,48.0000,7.2000,0.0000,0
2,239143,50,2,,MOFRT,,03MAIL,0,0,16.7500,0,,0
2,239143,51,2,,SLSTAX,,03MAIL,0,0,0.0000,0,,0

For a non-PBI order:

RECTYPE,ORDUNIQ,ORDNUMBER,CUSTOMER,SHPNAME,SHPADDR1,SHPADDR2,SHPADDR3,SHPADDR4,SHPCITY,SHPSTATE,SHPZIP,SHPCOUNTRY,SHPPHONE,SHPFAX,SHPCONTACT,PONUMBER,TYPE,ORDDATE,EXPDATE,LOCATION,TAMOUNT1,AUTOTAXCAL
RECTYPE,ORDUNIQ,LINENUM,LINETYPE,ITEM,MISCCHARGE,CATEGORY,LOCATION,QTYORDERED,PRIUNTPRC,EXTINVMISC,INVDISC,TAMOUNT1,COMMINST
RECTYPE,ORDUNIQ,PAYMENT
RECTYPE,ORDUNIQ,UNIQUIFIER,DETAILNUM,COINTYPE,COIN
RECTYPE,ORDUNIQ,UNIQUIFIER
RECTYPE,ORDUNIQ,OPTFIELD
RECTYPE,ORDUNIQ,LINENUM,OPTFIELD
1,5004,MAIL070224,00003,"","","","","","","","","",,,"","MAIL",1,20240702,20240702,03MAIL,16.5700,16.5700
2,5004,220300,1,220520,,STDCAT,03MAIL,14,44.000000000000,631.4000,0.0000,15.4000,0
2,5004,220306,1,232822,,STDCAT,03MAIL,2,44.000000000000,88.0000,0.0000,0.0000,0
2,5004,50,2,,MOFRT,,03MAIL,0,0,43.2500,0,,0
2,5004,51,2,,SLSTAX,,03MAIL,0,0,16.5700,0,,0