= 1; $invoices = []; $sql = mysqli_query($mysqli, "SELECT recurring_invoice_id, recurring_invoice_prefix, recurring_invoice_number, recurring_invoice_scope, recurring_invoice_frequency, recurring_invoice_next_date, recurring_invoice_amount, recurring_invoice_auto_send, recurring_invoice_created_at, recurring_invoice_client_id, client_name, client_abbreviation, category_name, category_id FROM recurring_invoices JOIN clients ON recurring_invoice_client_id = client_id LEFT JOIN categories ON recurring_invoice_category_id = category_id WHERE recurring_invoice_status = 1 AND recurring_invoice_archived_at IS NULL " . reportClientSql('recurring_invoice_client_id', $client_id) . " ORDER BY client_name ASC, recurring_invoice_next_date ASC"); while ($row = mysqli_fetch_assoc($sql)) { $amount = floatval($row['recurring_invoice_amount']); $row['monthly'] = recurringInvoiceMonthly($amount, $row['recurring_invoice_frequency']); $invoices[] = $row; } $clients = []; $categories = []; foreach ($invoices as $invoice) { $id = intval($invoice['recurring_invoice_client_id']); $clients[$id] = $clients[$id] ?? ['client_id' => $id, 'client' => $invoice['client_name'], 'abbr' => $invoice['client_abbreviation'], 'count' => 0, 'mrr' => 0.0, 'next' => null, 'since' => null]; $clients[$id]['count']++; $clients[$id]['mrr'] += $invoice['monthly']; $clients[$id]['next'] = $clients[$id]['next'] === null ? $invoice['recurring_invoice_next_date'] : min($clients[$id]['next'], $invoice['recurring_invoice_next_date']); $since = substr($invoice['recurring_invoice_created_at'], 0, 10); $clients[$id]['since'] = $clients[$id]['since'] === null ? $since : min($clients[$id]['since'], $since); $cat = $invoice['category_name'] ?? 'No category'; $categories[$cat] = ($categories[$cat] ?? 0) + $invoice['monthly']; } $mrr = array_sum(array_column($clients, 'mrr')); foreach ($clients as &$row) { $row['arr'] = $row['mrr'] * 12; $row['share'] = reportShare($row['mrr'], $mrr, 1); } unset($row); uasort($clients, function ($a, $b) { return $b['mrr'] <=> $a['mrr']; }); $clients = array_values($clients); $client_columns = [ 'client' => ['label' => 'Client', 'html' => function ($row) { return reportClientCell($row['client_id'], $row['client'], $row['abbr'], 'recurring_invoices.php'); }], 'count' => ['label' => 'Recurring invoices', 'type' => 'count'], 'mrr' => ['label' => 'Per month', 'type' => 'money', 'class' => 'fw-semibold'], 'arr' => ['label' => 'Per year', 'type' => 'money', 'class' => 'text-body-secondary'], 'share' => ['label' => 'Share', 'type' => 'percent', 'html' => function ($row) { return '
' . '' . reportPercent($row['share']) . '
'; }], 'next' => ['label' => 'Next bills', 'type' => 'date', 'class' => 'text-nowrap'], 'since' => ['label' => 'Recurring since', 'type' => 'date', 'class' => 'text-nowrap text-body-secondary'], ]; $invoice_columns = [ 'number' => ['label' => 'Recurring invoice', 'csv' => function ($row) { return $row['recurring_invoice_prefix'] . $row['recurring_invoice_number']; }, 'html' => function ($row) { return '' . escapeHtml($row['recurring_invoice_prefix'] . $row['recurring_invoice_number']) . ''; }], 'client_name' => ['label' => 'Client', 'html' => function ($row) { return reportClientCell($row['recurring_invoice_client_id'], $row['client_name'], $row['client_abbreviation'], 'recurring_invoices.php'); }], 'recurring_invoice_scope' => ['label' => 'Scope', 'class' => 'small itflow-report-text'], 'category_name' => ['label' => 'Category', 'class' => 'small text-body-secondary'], 'recurring_invoice_frequency' => ['label' => 'Every', 'csv' => function ($row) { return ucfirst(recurringInvoicePeriodWord($row['recurring_invoice_frequency'])); }, 'html' => function ($row) { return ucfirst(recurringInvoicePeriodWord($row['recurring_invoice_frequency'])); }], 'recurring_invoice_amount' => ['label' => 'Amount', 'type' => 'money'], 'monthly' => ['label' => 'Per month', 'type' => 'money', 'class' => 'fw-semibold'], 'recurring_invoice_next_date' => ['label' => 'Next bills', 'type' => 'date', 'class' => 'text-nowrap'], 'auto' => ['label' => 'Sends', 'csv' => function ($row) { return intval($row['recurring_invoice_auto_send']) ? 'Automatically' : 'Draft for review'; }, 'html' => function ($row) { return intval($row['recurring_invoice_auto_send']) ? 'Automatically' : 'Draft first'; }], ]; if (reportExporting('invoices')) { reportCsv('Recurring Invoices', $invoice_columns, $invoices, ['number' => 'Total', 'monthly' => $mrr]); } if (reportExporting()) { reportCsv('Recurring Revenue by Client', $client_columns, $clients, ['client' => 'Total', 'count' => count($invoices), 'mrr' => $mrr, 'arr' => $mrr * 12]); } // What comes off it every month $costs = 0.0; if ($can_see_costs && !$client_id) { $costs = recurringExpenseMonthlyTotal(); } // What bills in the next 30 days $next_30 = 0.0; $limit = date('Y-m-d', strtotime('+30 days')); foreach ($invoices as $invoice) { if ($invoice['recurring_invoice_next_date'] <= $limit) { $next_30 += floatval($invoice['recurring_invoice_amount']); } } require_once "includes/inc_report_layout.php"; arsort($categories); $cat_rows = reportTopN(array_map(function ($label, $value) { return ['label' => $label, 'value' => $value]; }, array_keys($categories), array_values($categories)), 6); $cat_colors = array_map(function ($i) { return reportPalette($i); }, array_keys($cat_rows)); foreach ($cat_rows as $i => $row) { if ($row['label'] === 'Other') { $cat_colors[$i] = 'faint'; } } $stats = [ ['label' => 'Recurring revenue', 'value' => reportMoney($mrr), 'sub' => 'Per month', 'url' => '/agent/recurring_invoices.php'], ['label' => 'A year of it', 'value' => reportMoney($mrr * 12), 'sub' => 'At today\'s recurring invoices'], ['label' => 'Recurring invoices', 'value' => reportCount(count($invoices)), 'sub' => reportCount(count($clients)) . ' client' . (count($clients) === 1 ? '' : 's') . ', ' . (count($clients) ? escapeHtml(reportMoney($mrr / count($clients))) : '-') . ' each a month'], ]; if ($can_see_costs && !$client_id) { $stats[] = ['label' => 'After recurring expenses', 'value' => reportMoney($mrr - $costs), 'tone' => $mrr - $costs < 0 ? 'danger' : '', 'sub' => escapeHtml(reportMoney($costs)) . ' a month goes out', 'url' => '/agent/recurring_expenses.php']; } else { $stats[] = ['label' => 'Bills in the next 30 days', 'value' => reportMoney($next_30), 'sub' => 'From these recurring invoices']; } ?>
$client_id, 'subtitle' => 'As of ' . date('M j, Y'), 'export' => ['clients' => 'By client', 'invoices' => 'Every recurring invoice'], ]); ?>
'doughnut', 'labels' => array_column($cat_rows, 'label'), 'series' => [['label' => 'Per month', 'data' => array_map(function ($r) { return round($r['value'], 2); }, $cat_rows)]], 'colors' => $cat_colors, 'format' => 'money', ], 'donut', 'Recurring revenue by category') ?>
'bar', 'horizontal' => true, 'labels' => array_map(function ($c) { return $c['client']; }, $top), 'series' => [['label' => 'Per month', 'data' => array_map(function ($c) { return round($c['mrr'], 2); }, $top), 'tone' => 'accent']], 'format' => 'money', 'legend' => false, ], '', 'Recurring revenue per month for the biggest clients') ?>
'No active recurring invoices.', 'totals' => ['client' => 'Total', 'count' => count($invoices), 'mrr' => $mrr, 'arr' => $mrr * 12], ]) ?> 'No active recurring invoices.', 'limit' => 300, 'export_table' => 'invoices', 'totals' => ['number' => 'Total', 'monthly' => $mrr]]) ?>
Active recurring invoices only. A yearly invoice counts a twelfth of its amount each month.