Exporting line_items - API coding

Self-Hosted | v5
1

v4 stored line items in a separate table, v5 stores line items in “An array of objects which define the line items of the invoice”

I need the array broken out into line items and would rather not spend the two hours it’s going to take to get this done.

I know it’s a long shot, but does anyone have code they would be willing to share that accomplishes this goal?

2

Hi,

Sorry, I don’t have the code to share.

3

When I export Invoice_items, I get a csv with the information I need. I have read through the API documentation, however I don’t see this export listed.

Is there a way to directly call app\Export\CSV\InvoiceItemExport.php to obtain this file?

Or - is there a better solution?

4

Hi,

I suggest using the browser console to inspect the request in the network tab. If it’s possible in the app it’s possible calling the API directly.

5

@katycomputer

If i am following correctly, you should just be able to use the Invoice Item API endpoint here:

https://api-docs.invoicing.co/#post-/api/v1/reports/invoice_items

6

My brain struggles with understanding and implementing APIs, I was able to solve this using chatGPT and MariaDB 10.6, 10.6 introduces the JSON_TABLE function:

– SalesReceipt
SELECT
i.number invno,
LEFT(c.number, 6) Custno,
LEFT(c.NAME, 35) Customer,
p.date TransDte,
IF (CAST(p.transaction_reference AS UNSIGNED) = 0, ‘Visa’, ‘Check’) Paymethod,
IF (CAST(p.transaction_reference AS UNSIGNED) = 0, ‘Visa’, LEFT(p.transaction_reference, 10)) CheckNum,
i.date ShipDte,
LEFT(CONCAT_WS(’ ‘, ct.first_name, ct.last_name), 35) BillAddr1,
LEFT(ct.phone, 12) altphone,
LEFT(ct.email, 25) email,
LEFT(CONCAT_WS(’ ', ct.first_name, ct.last_name), 35) ContactName,
items.quantity,
items.cost,
items.product_key,
items.notes,
items.tax_rate1,
IF (CAST(p.transaction_reference AS UNSIGNED) = 0, ‘Undeposited Funds’, ‘Banks:US Bank’) DepositTo
FROM
payments p
INNER JOIN paymentables pt ON p.id = pt.payment_id
INNER JOIN invoices i ON pt.paymentable_id = i.id
INNER JOIN clients c ON p.client_id = c.id
INNER JOIN client_contacts ct ON c.id = ct.client_id AND ct.is_primary = 1
CROSS JOIN JSON_TABLE(i.line_items, ‘$[*]’ COLUMNS (
quantity INT PATH ‘$.quantity’,
cost DECIMAL(10, 2) PATH ‘$.cost’,
product_key VARCHAR(255) PATH ‘$.product_key’,
notes VARCHAR(255) PATH ‘$.notes’,
tax_rate1 DECIMAL(5, 2) PATH ‘$.tax_rate1’
)) AS items
ORDER BY
p.id DESC