A report of clients that are past due. Currently do this via PHPMyAdmin. Thinking the following SQL will get us what we want…
SELECT
clients.client_id,
clients.client_name,
IFNULL(SUM(invoices.invoice_amount), 0) - IFNULL(SUM(payments.payment_amount), 0) AS balance,
contacts.contact_phone AS billing_contact_phone,
IFNULL(recurring_totals.recurring_monthly_total, 0) AS recurring_monthly_total,
(IFNULL(SUM(invoices.invoice_amount), 0) - IFNULL(SUM(payments.payment_amount), 0) - IFNULL(recurring_totals.recurring_monthly_total, 0)) AS behind_amount,
CASE
WHEN IFNULL(recurring_totals.recurring_monthly_total, 0) > 0 THEN
(IFNULL(SUM(invoices.invoice_amount), 0) - IFNULL(SUM(payments.payment_amount), 0) - IFNULL(recurring_totals.recurring_monthly_total, 0)) / recurring_totals.recurring_monthly_total
ELSE
0
END AS months_behind
FROM
clients
LEFT JOIN
invoices
ON
clients.client_id = invoices.invoice_client_id
AND invoices.invoice_status NOT LIKE 'Draft'
AND invoices.invoice_status NOT LIKE 'Cancelled'
LEFT JOIN
(SELECT
payment_invoice_id,
SUM(payment_amount) as payment_amount
FROM payments
GROUP BY payment_invoice_id) as payments
ON
invoices.invoice_id = payments.payment_invoice_id
LEFT JOIN
contacts
ON
clients.client_id = contacts.contact_client_id AND contacts.contact_billing = 1
LEFT JOIN
(SELECT
recurring_client_id,
SUM(recurring_amount) AS recurring_monthly_total
FROM recurring
WHERE recurring_status = 1 AND recurring_frequency = 'month'
GROUP BY recurring_client_id) as recurring_totals
ON
clients.client_id = recurring_totals.recurring_client_id
GROUP BY
clients.client_id,
clients.client_name,
contacts.contact_phone,
recurring_totals.recurring_monthly_total
HAVING
balance > 0 AND months_behind >= 2
ORDER BY
months_behind DESC;