'Not due yet', '1-30' => '1–30 days', '31-60' => '31–60 days', '61-90' => '61–90 days', '90+' => 'Over 90 days']; $invoices = []; $sql = mysqli_query($mysqli, " SELECT open_invoices.invoice_id, open_invoices.invoice_client_id, open_invoices.invoice_due, balance, invoice_prefix, invoice_number, invoice_date, invoice_amount, invoice_scope, client_name, client_abbreviation, DATEDIFF(CURDATE(), open_invoices.invoice_due) AS days_late FROM ($open) AS open_invoices JOIN invoices ON invoices.invoice_id = open_invoices.invoice_id JOIN clients ON client_id = open_invoices.invoice_client_id WHERE balance > 0.004 ORDER BY open_invoices.invoice_due ASC"); while ($row = mysqli_fetch_assoc($sql)) { $late = intval($row['days_late']); $row['bucket'] = $late <= 0 ? 'current' : ($late <= 30 ? '1-30' : ($late <= 60 ? '31-60' : ($late <= 90 ? '61-90' : '90+'))); $row['balance'] = floatval($row['balance']); $row['paid'] = floatval($row['invoice_amount']) - $row['balance']; $invoices[] = $row; } // Per client, a column per bucket $clients = []; $totals = array_fill_keys(array_keys($buckets), 0.0); foreach ($invoices as $invoice) { $id = intval($invoice['invoice_client_id']); if (!isset($clients[$id])) { $clients[$id] = ['client_id' => $id, 'client' => $invoice['client_name'], 'abbr' => $invoice['client_abbreviation'], 'count' => 0, 'total' => 0.0, 'oldest_late' => null, 'last_payment' => null] + array_fill_keys(array_keys($buckets), 0.0); } $clients[$id][$invoice['bucket']] += $invoice['balance']; $clients[$id]['total'] += $invoice['balance']; $clients[$id]['count']++; $clients[$id]['oldest_late'] = max($clients[$id]['oldest_late'] ?? -100000, intval($invoice['days_late'])); $totals[$invoice['bucket']] += $invoice['balance']; } if ($clients) { $sql = mysqli_query($mysqli, "SELECT invoice_client_id AS client_id, MAX(payment_date) AS last_paid FROM payments JOIN invoices ON payment_invoice_id = invoice_id WHERE invoice_client_id IN (" . implode(',', array_keys($clients)) . ") GROUP BY invoice_client_id"); while ($row = mysqli_fetch_assoc($sql)) { $clients[intval($row['client_id'])]['last_payment'] = $row['last_paid']; } } uasort($clients, function ($a, $b) { return $b['total'] <=> $a['total']; }); $total = array_sum($totals); $overdue_total = $total - $totals['current']; $bucket_cell = function ($key) { return function ($row) use ($key) { if ($row[$key] < 0.005) { return '–'; } $class = in_array($key, ['61-90', '90+'], true) ? 'text-danger-emphasis fw-semibold' : ''; return '' . reportMoney($row[$key]) . ''; }; }; $client_columns = ['client' => ['label' => 'Client', 'html' => function ($row) { return reportClientCell($row['client_id'], $row['client'], $row['abbr'], 'invoices.php') ; }]]; foreach ($buckets as $key => $label) { $client_columns[$key] = ['label' => $label, 'type' => 'money', 'html' => $bucket_cell($key)]; } $client_columns += [ 'total' => ['label' => 'Owed', 'type' => 'money', 'html' => function ($row) { return '' . reportMoney($row['total']) . ''; }], 'count' => ['label' => 'Invoices', 'type' => 'count'], 'oldest_late' => ['label' => 'Oldest', 'type' => 'count', 'csv_label' => 'Oldest days late', 'title' => 'Days the oldest unpaid invoice is past due', 'html' => function ($row) { return $row['oldest_late'] > 0 ? '' . $row['oldest_late'] . ' day' . ($row['oldest_late'] === 1 ? '' : 's') . ' late' : 'Not due'; }], 'last_payment' => ['label' => 'Last paid', 'type' => 'date', 'class' => 'text-nowrap', 'html' => function ($row) { return $row['last_payment'] ? '' . escapeHtml(timeAgo($row['last_payment'])) . '' : 'Never'; }], ]; $invoice_columns = [ 'invoice' => ['label' => 'Invoice', 'csv' => function ($row) { return $row['invoice_prefix'] . $row['invoice_number']; }, 'html' => function ($row) { return '' . escapeHtml($row['invoice_prefix'] . $row['invoice_number']) . '' . ($row['invoice_scope'] !== '' && $row['invoice_scope'] !== null ? '
' . escapeHtml($row['invoice_scope']) . '
' : ''); }], 'client_name' => ['label' => 'Client', 'html' => function ($row) { return reportClientCell($row['invoice_client_id'], $row['client_name'], $row['client_abbreviation'], 'invoices.php'); }], 'invoice_date' => ['label' => 'Date', 'type' => 'date', 'class' => 'text-nowrap'], 'invoice_due' => ['label' => 'Due', 'type' => 'date', 'class' => 'text-nowrap'], 'days_late' => ['label' => 'Late', 'type' => 'count', 'csv_label' => 'Days late', 'html' => function ($row) { $late = intval($row['days_late']); if ($late <= 0) { return '' . ($late === 0 ? 'Due today' : 'In ' . (-$late) . ' day' . ($late === -1 ? '' : 's')) . ''; } $tone = $late > 60 ? 'danger' : ($late > 30 ? 'warning' : 'secondary'); return '' . $late . ' day' . ($late === 1 ? '' : 's') . ''; }], 'invoice_amount' => ['label' => 'Amount', 'type' => 'money', 'class' => 'text-body-secondary'], 'paid' => ['label' => 'Paid', 'type' => 'money', 'class' => 'text-body-secondary', 'html' => function ($row) { return $row['paid'] > 0.004 ? reportMoney($row['paid']) : '–'; }], 'balance' => ['label' => 'Owed', 'type' => 'money', 'class' => 'fw-semibold'], ]; if (reportExporting('invoices')) { reportCsv('Unpaid Invoices', $invoice_columns, $invoices, ['invoice' => 'Total', 'balance' => $total]); } if (reportExporting()) { reportCsv('Receivables Aging', $client_columns, array_values($clients), ['client' => 'Total', 'total' => $total, 'count' => count($invoices)] + $totals); } require_once "includes/inc_report_layout.php"; $late_90 = $totals['90+']; $late_60 = $totals['61-90'] + $totals['90+']; $overdue = array_values(array_filter($invoices, function ($i) { return intval($i['days_late']) > 0; })); usort($overdue, function ($a, $b) { return intval($b['days_late']) <=> intval($a['days_late']); }); ?>
$client_id, 'subtitle' => 'As of ' . date('M j, Y'), 'export' => ['clients' => 'By client', 'invoices' => 'Every unpaid invoice'], ]); ?> 'Owed to you', 'value' => reportMoney($total), 'url' => '/agent/invoices.php?status=Unpaid', 'sub' => reportCount(count($invoices)) . ' unpaid invoice' . (count($invoices) === 1 ? '' : 's') . ''], ['label' => 'Overdue', 'value' => reportMoney($overdue_total), 'tone' => $overdue_total > 0 ? 'danger' : '', 'url' => '/agent/invoices.php?status=Overdue', 'sub' => $total > 0 ? escapeHtml(reportPercent(reportShare($overdue_total, $total))) . ' of what is owed' : ''], ['label' => 'Over 60 days late', 'value' => reportMoney($late_60), 'tone' => $late_60 > 0 ? 'danger' : '', 'sub' => $late_90 > 0 ? escapeHtml(reportMoney($late_90)) . ' over 90' : 'Nothing over 90 days'], ['label' => 'Clients owing', 'value' => reportCount(count($clients)), 'sub' => count(array_filter($clients, function ($c) { return $c['oldest_late'] > 0; })) . ' with something overdue'], ]) ?> 0) { ?>
$label) { ?>
reportEmpty('Nothing is owed on unpaid invoices.'), 'totals' => ['client' => 'Total', 'total' => $total, 'count' => count($invoices)] + $totals, ]) ?> reportEmpty('No invoice is past its due date.'), 'limit' => 300, 'export_table' => 'invoices', 'totals' => ['invoice' => 'Total', 'balance' => array_sum(array_column($overdue, 'balance'))], ]) ?>
Unpaid invoices are the ones sent, viewed or part-paid; drafts and cancelled invoices are not owed. Days late count from the due date.