That is unfortunate. 🙁
I took the liberty to try to hack my own report. Can you please look at the mySQL statement and see if you see anything wrong with the way I’m doing this to generate the balances due on a certain date? I’m essentially searching the invoices and payments tables for anything before the date I need the report on, excluding any repeating setups and deleted items. I spot checked several of our clients and it seems to be working correctly, but I want to double check as I’m not 100% comfortable with your database structure.
Thanks in advance!
SET @useDate = '2019-09-31';
SELECT client_id, cli.name, SUM(amount) AS invoiced, SUM(amt) AS paid, SUM(due) AS due, SUM(CASE WHEN due > 0 THEN 1 ELSE 0 END) AS openInvoices, MAX(invoice_date) AS lastInvoiceDate, MIN(aged) AS oldestInvoice
FROM (
SELECT
IF(ISNULL(inv.amount), 0, inv.amount) - IF(ISNULL(pay.amt), 0, pay.amt) AS due,
IF( IF(ISNULL(inv.amount), 0, inv.amount) - IF(ISNULL(pay.amt), 0, pay.amt) > 0, inv.invoice_date, NULL ) AS aged,
pay.amt, inv.*
FROM ( SELECT * FROM ninja.invoices AS inv WHERE invoice_date <= @useDate AND is_deleted = 0 AND is_recurring = 0) AS inv
LEFT JOIN ( SELECT invoice_id, SUM(amount) AS amt FROM ninja.payments WHERE payment_date <= @useDate AND is_deleted = 0 GROUP BY invoice_id ) AS pay ON pay.invoice_id = inv.id
) AS lst
LEFT JOIN clients AS cli ON cli.id = lst.client_id
GROUP BY client_id
ORDER BY ( IF(SUM(due) > 0, 1, 2) ), name