Report - Invoice items by line to CSV

[dom_itechnique]

Trying to generate a report / CSV to get the attached format. Idealy would have date pickers to filter results.
I thought that starting with the sales_tax_liability.php would be a good place to start but it would only generate 1 invoice record per line.
Would anyone have a template I can work from / customise? 

export_20190411 (3).csv

[brian!]

On 11/04/2019 at 12:16, dom_itechnique said:

Trying to generate a report / CSV to get the attached format. Ideally would have date pickers to filter results.

if it were me, i'd be doing this outside of WHMCS, e.g using phpmyadmin or similar - not least because the information you want (if that sample output is to be believed) is contained within multiple database tables, not just one.

On 11/04/2019 at 12:16, dom_itechnique said:

I thought that starting with the sales_tax_liability.php would be a good place to start but it would only generate 1 invoice record per line.

i'd have started with the invoices report, duplicated it, replaced all occurrences of tblinvoices with tblinvoiceitems in the code and then modified $filterfields to remove the fields not available to tblinvoiceitems (Creation & Date Paid being two examples as they're fields in tblinvoices).

also, there isn't a qty field in either table - you'd have to do a count of those rows to get the quantity.

On 11/04/2019 at 12:16, dom_itechnique said:

Would anyone have a template I can work from / customise? 

i've quickly modified the invoices report to search tblinvoiceitems instead - i've added the description field and tweaked the code to allow 'datepaid' back into the output (you can add other fields from tblinvoices in the same way)... hope it helps. 🙂

invoiceitems.php

[dom_itechnique]

On 4/13/2019 at 1:00 AM, brian! said:

if it were me, i'd be doing this outside of WHMCS, e.g using phpmyadmin or similar - not least because the information you want (if that sample output is to be believed) is contained within multiple database tables, not just one.

i'd have started with the invoices report, duplicated it, replaced all occurrences of tblinvoices with tblinvoiceitems in the code and then modified $filterfields to remove the fields not available to tblinvoiceitems (Creation & Date Paid being two examples as they're fields in tblinvoices).

also, there isn't a qty field in either table - you'd have to do a count of those rows to get the quantity.

i've quickly modified the invoices report to search tblinvoiceitems instead - i've added the description field and tweaked the code to allow 'datepaid' back into the output (you can add other fields from tblinvoices in the same way)... hope it helps. 🙂

invoiceitems.php

Wow, thanks so much Brian.
Will have a play with it.

Cheers.