0 AND " . dashboardRevenueSql() . " AND $range_revenue ) AS income JOIN clients USING (client_id) WHERE 1 = 1 " . clientScopeSql('client_id') . " GROUP BY client_id, client_name, client_abbreviation"; }; $clients = []; $sql = mysqli_query($mysqli, $income_sql(reportRangeSql('payment_date', $period), reportRangeSql('revenue_date', $period))); while ($row = mysqli_fetch_assoc($sql)) { $clients[intval($row['client_id'])] = ['client_id' => intval($row['client_id']), 'client' => $row['client_name'], 'abbr' => $row['client_abbreviation'], 'paid' => floatval($row['v']), 'payments' => intval($row['n']), 'last_paid' => $row['last_paid'], 'prev' => 0.0, 'mrr' => 0.0]; } $prev_total = 0.0; $sql = mysqli_query($mysqli, $income_sql(reportRangeSql('payment_date', $period, true), reportRangeSql('revenue_date', $period, true))); while ($row = mysqli_fetch_assoc($sql)) { $prev_total += floatval($row['v']); if (isset($clients[intval($row['client_id'])])) { $clients[intval($row['client_id'])]['prev'] = floatval($row['v']); } } $sql = mysqli_query($mysqli, "SELECT recurring_invoice_client_id AS client_id, SUM(" . recurringInvoiceMonthlySql() . ") AS v FROM recurring_invoices WHERE recurring_invoice_status = 1 AND recurring_invoice_archived_at IS NULL GROUP BY recurring_invoice_client_id"); while ($row = mysqli_fetch_assoc($sql)) { if (isset($clients[intval($row['client_id'])])) { $clients[intval($row['client_id'])]['mrr'] = floatval($row['v']); } } $total = array_sum(array_column($clients, 'paid')); if ($only_600) { $clients = array_filter($clients, function ($c) { return $c['paid'] >= 600; }); } uasort($clients, function ($a, $b) { return $b['paid'] <=> $a['paid']; }); foreach ($clients as &$row) { $row['share'] = reportShare($row['paid'], $total, 1); $row['change'] = reportChange($row['paid'], $row['prev']); } unset($row); $clients = array_values($clients); $columns = [ 'client' => ['label' => 'Client', 'html' => function ($row) { return reportClientCell($row['client_id'], $row['client'], $row['abbr']); }], 'paid' => ['label' => 'Paid', 'type' => 'money', 'class' => 'fw-semibold', 'html' => function ($row) use ($period) { return '' . reportMoney($row['paid']) . ''; }], 'share' => ['label' => 'Share', 'type' => 'percent', 'html' => function ($row) { return '
' . '' . reportPercent($row['share']) . '
'; }], 'prev' => ['label' => 'Period before', 'type' => 'money', 'class' => 'text-body-secondary'], 'change' => ['label' => 'Change', 'type' => 'percent', 'csv' => false, 'html' => function ($row) { return $row['change'] === null ? 'New' : '' . reportChangeHtml($row['paid'], $row['prev'], 'up') . ''; }], 'payments' => ['label' => 'Payments', 'type' => 'count'], 'mrr' => ['label' => 'Recurring / month', 'type' => 'money', 'html' => function ($row) { return $row['mrr'] > 0 ? reportMoney($row['mrr']) : '–'; }], 'last_paid' => ['label' => 'Last paid', 'type' => 'date', 'class' => 'text-nowrap'], ]; $totals = ['client' => 'Total', 'paid' => array_sum(array_column($clients, 'paid')), 'prev' => array_sum(array_column($clients, 'prev')), 'payments' => array_sum(array_column($clients, 'payments')), 'mrr' => array_sum(array_column($clients, 'mrr'))]; if (reportExporting()) { reportCsv('Income by Client ' . $period['start'] . ' to ' . $period['end'], $columns, $clients, $totals); } require_once "includes/inc_report_layout.php"; $top = array_slice($clients, 0, 12); $top_five_share = reportShare(array_sum(array_column(array_slice($clients, 0, 5), 'paid')), $total); ?>
$period, 'years' => reportPastYears("SELECT DISTINCT YEAR(payment_date) AS y FROM payments"), 'controls' => reportSwitchControl('over600', $only_600, 'Only clients who paid 600 or more'), ]); ?> 'Paid by clients', 'value' => reportMoney($total), 'sub' => reportChangeHtml($total, $prev_total, 'up', $period['compare'])], ['label' => 'Clients who paid', 'value' => reportCount(count($clients)), 'sub' => '' . ($only_600 ? 'Paid 600 or more' : 'In the period') . ''], ['label' => 'Average per client', 'value' => count($clients) ? reportMoney(array_sum(array_column($clients, 'paid')) / count($clients)) : '-', 'sub' => 'Over the period'], ['label' => 'Top five', 'value' => reportPercent($top_five_share), 'sub' => 'of income - how much rides on a few clients', 'tone' => $top_five_share !== null && $top_five_share >= 60 ? 'warning' : ''], ]) ?>
'bar', 'horizontal' => true, 'labels' => array_map(function ($c) { return $c['client']; }, $top), 'series' => [ ['label' => 'This period', 'data' => array_map(function ($c) { return round($c['paid'], 2); }, $top), 'tone' => 'accent'], ['label' => 'Period before', 'data' => array_map(function ($c) { return round($c['prev'], 2); }, $top), 'tone' => 'faint'], ], 'format' => 'money', ], '', 'What each of the biggest clients paid, this period and the period before') ?>
'No client paid anything in this period.', 'totals' => $totals]) ?>
Invoice payments by the date they arrived, plus revenue entered against the client. "Period before" is .