| 1 |
<?php |
| 2 |
/** |
| 3 |
* Dashboard Controller Class |
| 4 |
* |
| 5 |
* @package Easy_Invoice |
| 6 |
* @subpackage Controllers |
| 7 |
*/ |
| 8 |
|
| 9 |
namespace EasyInvoice\Controllers; |
| 10 |
|
| 11 |
use EasyInvoice\Constants\PagesSlugs; |
| 12 |
use EasyInvoice\Providers\InvoiceServiceProvider; |
| 13 |
use EasyInvoice\Providers\ClientServiceProvider; |
| 14 |
|
| 15 |
/** |
| 16 |
* DashboardController handles dashboard functionality |
| 17 |
*/ |
| 18 |
class DashboardController extends BaseController { |
| 19 |
|
| 20 |
/** |
| 21 |
* Initialize the controller |
| 22 |
*/ |
| 23 |
public function init() { |
| 24 |
// Add necessary initialization here |
| 25 |
} |
| 26 |
|
| 27 |
/** |
| 28 |
* Display method implementation |
| 29 |
* |
| 30 |
* @param array $args Display arguments |
| 31 |
*/ |
| 32 |
public function display(array $args = []) { |
| 33 |
$page = isset($args['page']) ? $args['page'] : ''; |
| 34 |
|
| 35 |
switch ($page) { |
| 36 |
case PagesSlugs::DASHBOARD: |
| 37 |
$this->displayDashboardPage(); |
| 38 |
break; |
| 39 |
|
| 40 |
default: |
| 41 |
$this->displayDashboardPage(); |
| 42 |
break; |
| 43 |
} |
| 44 |
} |
| 45 |
|
| 46 |
/** |
| 47 |
* Display dashboard page |
| 48 |
*/ |
| 49 |
protected function displayDashboardPage() { |
| 50 |
// Check user capability |
| 51 |
$error = $this->checkCapability('ei_view_dashboard'); |
| 52 |
if (is_wp_error($error)) { |
| 53 |
wp_die(esc_html($error->get_error_message())); |
| 54 |
} |
| 55 |
|
| 56 |
// Get data for dashboard |
| 57 |
$invoice_repository = InvoiceServiceProvider::getInvoiceRepository(); |
| 58 |
$client_repository = ClientServiceProvider::getClientRepository(); |
| 59 |
|
| 60 |
// Get counts |
| 61 |
// Counts are SQL counts; only the invoices that can still carry a |
| 62 |
// balance are loaded as models. Loading every invoice (three times) |
| 63 |
// put a 3,000-invoice site at ~40 seconds per dashboard view. |
| 64 |
$total_invoices = (int) $invoice_repository->count(); |
| 65 |
$paid_invoices = (int) $invoice_repository->count(['meta_key' => '_easy_invoice_status', 'meta_value' => 'paid']); // phpcs:ignore WordPress.DB.SlowDBQuery |
| 66 |
// "Unpaid" is every issued invoice still carrying a balance — a sent |
| 67 |
// invoice is 'available' (or 'partial') until paid, so counting the |
| 68 |
// literal 'unpaid' / 'overdue' statuses showed 0 on nearly every site. |
| 69 |
// Outstanding balances come from SQL over the persisted totals; loading |
| 70 |
// every open invoice as a model does not scale past a few thousand. |
| 71 |
$outstanding = \EasyInvoice\Services\InvoiceTotalsCache::outstanding(); |
| 72 |
$unpaid_invoices = $outstanding['count']; |
| 73 |
$overdue_invoices = $outstanding['overdue_count']; |
| 74 |
|
| 75 |
$total_clients = (int) (new \WP_User_Query(['role__not_in' => ['Administrator'], 'fields' => 'ID', 'number' => 1, 'count_total' => true]))->get_total(); |
| 76 |
$active_clients = $this->getActiveClientCount($client_repository, $invoice_repository); |
| 77 |
|
| 78 |
// Get recent invoices |
| 79 |
$recent_invoices = $this->getRecentInvoices($invoice_repository); |
| 80 |
|
| 81 |
// Get revenue data |
| 82 |
$total_revenue = $this->getTotalRevenue($invoice_repository); |
| 83 |
$monthly_revenue = $this->getMonthlyRevenue($invoice_repository); |
| 84 |
|
| 85 |
// Display the template |
| 86 |
$this->displayTemplate( |
| 87 |
EASY_INVOICE_PLUGIN_DIR . 'templates/dashboard-page.php', |
| 88 |
[ |
| 89 |
'total_invoices' => $total_invoices, |
| 90 |
'paid_invoices' => $paid_invoices, |
| 91 |
'unpaid_invoices' => $unpaid_invoices, |
| 92 |
'overdue_invoices' => $overdue_invoices, |
| 93 |
'unpaid_amount' => $outstanding['amount'], |
| 94 |
'overdue_amount' => $outstanding['overdue_amount'], |
| 95 |
'total_clients' => $total_clients, |
| 96 |
'active_clients' => $active_clients, |
| 97 |
'recent_invoices' => $recent_invoices, |
| 98 |
'total_revenue' => $total_revenue, |
| 99 |
'monthly_revenue' => $monthly_revenue |
| 100 |
] |
| 101 |
); |
| 102 |
} |
| 103 |
|
| 104 |
/** |
| 105 |
* Get active client count |
| 106 |
* |
| 107 |
* @param object $client_repository |
| 108 |
* @param object $invoice_repository |
| 109 |
* @return int Count of active clients |
| 110 |
*/ |
| 111 |
private function getActiveClientCount($client_repository, $invoice_repository) { |
| 112 |
// Distinct clients billed in the last 90 days — one query instead of |
| 113 |
// one full invoice load per client. |
| 114 |
global $wpdb; |
| 115 |
$since = wp_date('Y-m-d', strtotime('-90 days')); |
| 116 |
$count = $wpdb->get_var($wpdb->prepare( |
| 117 |
"SELECT COUNT(DISTINCT c.meta_value) |
| 118 |
FROM {$wpdb->posts} p |
| 119 |
INNER JOIN {$wpdb->postmeta} c ON c.post_id = p.ID AND c.meta_key = '_easy_invoice_client_id' |
| 120 |
INNER JOIN {$wpdb->postmeta} d ON d.post_id = p.ID AND d.meta_key = '_easy_invoice_issue_date' |
| 121 |
WHERE p.post_type = %s AND p.post_status = 'publish' |
| 122 |
AND c.meta_value <> '' AND c.meta_value <> '0' AND d.meta_value >= %s", |
| 123 |
\EasyInvoice\Constants\PostTypes::EASY_INVOICE_POST_TYPE, |
| 124 |
$since |
| 125 |
)); |
| 126 |
return (int) $count; |
| 127 |
} |
| 128 |
|
| 129 |
/** |
| 130 |
* Get recent invoices |
| 131 |
* |
| 132 |
* @param object $invoice_repository |
| 133 |
* @return array Recent invoices |
| 134 |
*/ |
| 135 |
private function getRecentInvoices($invoice_repository) { |
| 136 |
try { |
| 137 |
// Five most recently issued: let the database sort and limit. |
| 138 |
return $invoice_repository->all([ |
| 139 |
'posts_per_page' => 5, |
| 140 |
'meta_key' => '_easy_invoice_issue_date', // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key |
| 141 |
'orderby' => ['meta_value' => 'DESC', 'ID' => 'DESC'], |
| 142 |
]); |
| 143 |
} catch (\Exception $e) { |
| 144 |
// Log the error and return an empty array |
| 145 |
return []; |
| 146 |
} |
| 147 |
} |
| 148 |
|
| 149 |
/** |
| 150 |
* Get total revenue from paid invoices |
| 151 |
* |
| 152 |
* @param object $invoice_repository |
| 153 |
* @return array Total revenue by currency |
| 154 |
*/ |
| 155 |
private function getTotalRevenue($invoice_repository) { |
| 156 |
// SQL-aggregate path. Replaces the previous "load every payment post + |
| 157 |
// call get_post_meta() 2× per row in PHP" approach with a single |
| 158 |
// GROUP BY query. On a site with 50K payments this cuts ~150K |
| 159 |
// postmeta queries to 1 SQL aggregate. |
| 160 |
// |
| 161 |
// Returns the SAME data structure as the legacy path: |
| 162 |
// [ 'USD' => ['amount' => 12345.67, 'symbol' => '$'], ... ] |
| 163 |
// |
| 164 |
// Filter `easy_invoice_dashboard_use_sql_aggregates` lets admins |
| 165 |
// revert to the legacy PHP path if any production-data edge case |
| 166 |
// surfaces unexpected numbers. |
| 167 |
$use_sql = (bool) apply_filters('easy_invoice_dashboard_use_sql_aggregates', true); |
| 168 |
if ($use_sql) { |
| 169 |
$sql_result = $this->getTotalRevenueViaSql(); |
| 170 |
if ($sql_result !== null) { |
| 171 |
return $sql_result; |
| 172 |
} |
| 173 |
} |
| 174 |
return $this->getTotalRevenueViaPhpFallback(); |
| 175 |
} |
| 176 |
|
| 177 |
/** |
| 178 |
* Single-query revenue aggregation. Returns null on hard DB failure so |
| 179 |
* the caller can fall back to the PHP path; returns an empty array |
| 180 |
* legitimately when there are zero matching payments. |
| 181 |
* |
| 182 |
* @return array<string,array{amount:float,symbol:string}>|null |
| 183 |
*/ |
| 184 |
private function getTotalRevenueViaSql(): ?array { |
| 185 |
global $wpdb; |
| 186 |
$global_currency = strtoupper((string) get_option('easy_invoice_currency_code', 'USD')); |
| 187 |
|
| 188 |
// SQL inputs: |
| 189 |
// m_stat → payment status (must be completed / approved / paid) |
| 190 |
// m_amt → payment amount (CAST to DECIMAL so the SUM ignores junk) |
| 191 |
// m_cur → payment currency (LEFT JOIN — older payments may not have it) |
| 192 |
// |
| 193 |
// The IFNULL/NULLIF chain collapses empty-string and the literal |
| 194 |
// 'global' sentinel to the site default, matching the PHP path's |
| 195 |
// `empty($currency_code) || $currency_code === 'global'` check. |
| 196 |
$sql = $wpdb->prepare( |
| 197 |
"SELECT |
| 198 |
UPPER(IFNULL(NULLIF(NULLIF(m_cur.meta_value, ''), 'global'), %s)) AS currency_code, |
| 199 |
SUM(CAST(m_amt.meta_value AS DECIMAL(20,4))) AS total_amount |
| 200 |
FROM {$wpdb->posts} p |
| 201 |
INNER JOIN {$wpdb->postmeta} m_stat ON m_stat.post_id = p.ID AND m_stat.meta_key = '_status' |
| 202 |
INNER JOIN {$wpdb->postmeta} m_amt ON m_amt.post_id = p.ID AND m_amt.meta_key = '_amount' |
| 203 |
LEFT JOIN {$wpdb->postmeta} m_cur ON m_cur.post_id = p.ID AND m_cur.meta_key = '_currency' |
| 204 |
WHERE p.post_type = 'easy_invoice_payment' |
| 205 |
AND p.post_status = 'publish' |
| 206 |
AND m_stat.meta_value IN ('completed','approved','paid') |
| 207 |
AND CAST(m_amt.meta_value AS DECIMAL(20,4)) > 0 |
| 208 |
GROUP BY currency_code", |
| 209 |
$global_currency |
| 210 |
); |
| 211 |
$rows = $wpdb->get_results($sql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is built with $wpdb->prepare() above. |
| 212 |
if ($rows === null) { |
| 213 |
return null; // hard DB error → caller falls back to PHP path |
| 214 |
} |
| 215 |
|
| 216 |
$out = []; |
| 217 |
foreach ($rows as $row) { |
| 218 |
$code = (string) $row->currency_code; |
| 219 |
$out[$code] = [ |
| 220 |
'amount' => (float) $row->total_amount, |
| 221 |
'symbol' => \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol($code), |
| 222 |
]; |
| 223 |
} |
| 224 |
return $out; |
| 225 |
} |
| 226 |
|
| 227 |
/** |
| 228 |
* Legacy PHP aggregation path. Kept verbatim from the original |
| 229 |
* implementation so the filter-off fallback returns identical numbers |
| 230 |
* to what the dashboard always rendered before the SQL refactor. |
| 231 |
* |
| 232 |
* @return array<string,array{amount:float,symbol:string}> |
| 233 |
*/ |
| 234 |
private function getTotalRevenueViaPhpFallback() { |
| 235 |
try { |
| 236 |
$payments = get_posts([ |
| 237 |
'post_type' => 'easy_invoice_payment', |
| 238 |
'post_status' => 'publish', |
| 239 |
'meta_query' => [ |
| 240 |
[ |
| 241 |
'key' => '_status', |
| 242 |
'value' => ['completed', 'approved', 'paid'], |
| 243 |
'compare' => 'IN' |
| 244 |
] |
| 245 |
], |
| 246 |
'numberposts' => -1 |
| 247 |
]); |
| 248 |
|
| 249 |
$revenue_by_currency = []; |
| 250 |
$global_currency = get_option('easy_invoice_currency_code', 'USD'); |
| 251 |
|
| 252 |
foreach ($payments as $payment) { |
| 253 |
try { |
| 254 |
$payment_amount = get_post_meta($payment->ID, '_amount', true); |
| 255 |
if (!is_numeric($payment_amount) || $payment_amount <= 0) { |
| 256 |
continue; |
| 257 |
} |
| 258 |
|
| 259 |
$currency_code = get_post_meta($payment->ID, '_currency', true); |
| 260 |
if (empty($currency_code) || $currency_code === 'global') { |
| 261 |
$currency_code = $global_currency; |
| 262 |
} |
| 263 |
$currency_code = strtoupper($currency_code); |
| 264 |
|
| 265 |
$currency_symbol = \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol($currency_code); |
| 266 |
|
| 267 |
if (!isset($revenue_by_currency[$currency_code])) { |
| 268 |
$revenue_by_currency[$currency_code] = [ |
| 269 |
'amount' => 0, |
| 270 |
'symbol' => $currency_symbol |
| 271 |
]; |
| 272 |
} |
| 273 |
|
| 274 |
$revenue_by_currency[$currency_code]['amount'] += $payment_amount; |
| 275 |
} catch (\Exception $e) { |
| 276 |
continue; |
| 277 |
} |
| 278 |
} |
| 279 |
|
| 280 |
return $revenue_by_currency; |
| 281 |
} catch (\Exception $e) { |
| 282 |
return []; |
| 283 |
} |
| 284 |
} |
| 285 |
|
| 286 |
/** |
| 287 |
* Get monthly revenue data for charts |
| 288 |
* |
| 289 |
* @param object $invoice_repository |
| 290 |
* @return array Monthly revenue data |
| 291 |
*/ |
| 292 |
private function getMonthlyRevenue($invoice_repository) { |
| 293 |
// Initialize months for the last 12 months (rolling period) |
| 294 |
$monthly_revenue = array(); |
| 295 |
|
| 296 |
// Get the current date and go back 11 months to create a 12-month period |
| 297 |
$current_date = new \DateTime(); |
| 298 |
$start_date = clone $current_date; |
| 299 |
$start_date->modify('-11 months'); |
| 300 |
|
| 301 |
// Initialize all 12 months |
| 302 |
for ($i = 0; $i < 12; $i++) { |
| 303 |
$month_date = clone $start_date; |
| 304 |
$month_date->modify("+{$i} months"); |
| 305 |
$month_name = $month_date->format('M Y'); |
| 306 |
$monthly_revenue[$month_name] = []; |
| 307 |
} |
| 308 |
|
| 309 |
// SQL-aggregate path. Same logic as the chart's PHP loop but executed |
| 310 |
// as a single GROUP BY in MySQL. Filterable via |
| 311 |
// `easy_invoice_dashboard_use_sql_aggregates` (shared with |
| 312 |
// getTotalRevenue — flip both at once). |
| 313 |
if (apply_filters('easy_invoice_dashboard_use_sql_aggregates', true)) { |
| 314 |
$sql_buckets = $this->getMonthlyRevenueViaSql($start_date, $current_date); |
| 315 |
if ($sql_buckets !== null) { |
| 316 |
// Merge SQL aggregates into the pre-initialized 12-month skeleton |
| 317 |
// so empty months stay empty (instead of disappearing from the chart). |
| 318 |
foreach ($sql_buckets as $month_label => $by_currency) { |
| 319 |
if (isset($monthly_revenue[$month_label])) { |
| 320 |
$monthly_revenue[$month_label] = $by_currency; |
| 321 |
} |
| 322 |
} |
| 323 |
return $monthly_revenue; |
| 324 |
} |
| 325 |
} |
| 326 |
|
| 327 |
try { |
| 328 |
// Get all completed payments (including different statuses that might be considered completed) |
| 329 |
$payments = get_posts([ |
| 330 |
'post_type' => 'easy_invoice_payment', |
| 331 |
'post_status' => 'publish', |
| 332 |
'meta_query' => [ |
| 333 |
[ |
| 334 |
'key' => '_status', |
| 335 |
'value' => ['completed', 'approved', 'paid'], |
| 336 |
'compare' => 'IN' |
| 337 |
] |
| 338 |
], |
| 339 |
'numberposts' => -1 |
| 340 |
]); |
| 341 |
|
| 342 |
// If no completed payments found, try to get any payments with amounts |
| 343 |
if (empty($payments)) { |
| 344 |
$payments = get_posts([ |
| 345 |
'post_type' => 'easy_invoice_payment', |
| 346 |
'post_status' => 'publish', |
| 347 |
'meta_query' => [ |
| 348 |
[ |
| 349 |
'key' => '_amount', |
| 350 |
'value' => '0', |
| 351 |
'compare' => '>' |
| 352 |
] |
| 353 |
], |
| 354 |
'numberposts' => -1 |
| 355 |
]); |
| 356 |
} |
| 357 |
|
| 358 |
$global_currency = get_option('easy_invoice_currency_code', 'USD'); |
| 359 |
|
| 360 |
// Calculate revenue for each month by currency |
| 361 |
foreach ($payments as $payment) { |
| 362 |
try { |
| 363 |
$payment_date = get_post_meta($payment->ID, '_payment_date', true); |
| 364 |
if (!$payment_date) { |
| 365 |
continue; |
| 366 |
} |
| 367 |
|
| 368 |
$payment_date_obj = new \DateTime($payment_date); |
| 369 |
if ($payment_date_obj >= $start_date && $payment_date_obj <= $current_date) { |
| 370 |
$month = $payment_date_obj->format('M Y'); |
| 371 |
$payment_amount = get_post_meta($payment->ID, '_amount', true); |
| 372 |
|
| 373 |
if (!is_numeric($payment_amount)) { |
| 374 |
continue; |
| 375 |
} |
| 376 |
|
| 377 |
// Get currency information from payment |
| 378 |
$currency_code = get_post_meta($payment->ID, '_currency', true); |
| 379 |
if (empty($currency_code) || $currency_code === 'global') { |
| 380 |
$currency_code = $global_currency; |
| 381 |
} |
| 382 |
$currency_code = strtoupper($currency_code); |
| 383 |
|
| 384 |
$currency_symbol = \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol($currency_code); |
| 385 |
|
| 386 |
// Initialize currency for this month if not exists |
| 387 |
if (!isset($monthly_revenue[$month][$currency_code])) { |
| 388 |
$monthly_revenue[$month][$currency_code] = [ |
| 389 |
'amount' => 0, |
| 390 |
'symbol' => $currency_symbol |
| 391 |
]; |
| 392 |
} |
| 393 |
|
| 394 |
$monthly_revenue[$month][$currency_code]['amount'] += $payment_amount; |
| 395 |
} |
| 396 |
} catch (\Exception $e) { |
| 397 |
// Log the error and continue with the next payment |
| 398 |
continue; |
| 399 |
} |
| 400 |
} |
| 401 |
return $monthly_revenue; |
| 402 |
} catch (\Exception $e) { |
| 403 |
// Log the error and return empty monthly revenue |
| 404 |
return $monthly_revenue; |
| 405 |
} |
| 406 |
} |
| 407 |
|
| 408 |
/** |
| 409 |
* Single-query monthly revenue aggregation. Returns null on hard DB |
| 410 |
* failure so the caller falls back to the PHP path; returns an array |
| 411 |
* keyed by 'Mon YYYY' month label (matching the PHP path's format). |
| 412 |
* |
| 413 |
* The query mirrors getTotalRevenueViaSql but adds DATE_FORMAT |
| 414 |
* grouping on the _payment_date meta. Date range is enforced in SQL |
| 415 |
* via lexicographic comparison on the ISO date string, which works |
| 416 |
* because _payment_date is stored as 'YYYY-MM-DD' (sortable). |
| 417 |
* |
| 418 |
* @param \DateTime $start_date |
| 419 |
* @param \DateTime $current_date |
| 420 |
* @return array<string,array<string,array{amount:float,symbol:string}>>|null |
| 421 |
*/ |
| 422 |
private function getMonthlyRevenueViaSql(\DateTime $start_date, \DateTime $current_date): ?array { |
| 423 |
global $wpdb; |
| 424 |
$global_currency = strtoupper((string) get_option('easy_invoice_currency_code', 'USD')); |
| 425 |
$start_iso = $start_date->format('Y-m-01'); |
| 426 |
$end_iso = $current_date->format('Y-m-d'); |
| 427 |
|
| 428 |
$sql = $wpdb->prepare( |
| 429 |
"SELECT |
| 430 |
DATE_FORMAT(m_date.meta_value, '%%b %%Y') AS month_label, |
| 431 |
MIN(m_date.meta_value) AS month_sort, |
| 432 |
UPPER(IFNULL(NULLIF(NULLIF(m_cur.meta_value, ''), 'global'), %s)) AS currency_code, |
| 433 |
SUM(CAST(m_amt.meta_value AS DECIMAL(20,4))) AS total_amount |
| 434 |
FROM {$wpdb->posts} p |
| 435 |
INNER JOIN {$wpdb->postmeta} m_stat ON m_stat.post_id = p.ID AND m_stat.meta_key = '_status' |
| 436 |
INNER JOIN {$wpdb->postmeta} m_amt ON m_amt.post_id = p.ID AND m_amt.meta_key = '_amount' |
| 437 |
INNER JOIN {$wpdb->postmeta} m_date ON m_date.post_id = p.ID AND m_date.meta_key = '_payment_date' |
| 438 |
LEFT JOIN {$wpdb->postmeta} m_cur ON m_cur.post_id = p.ID AND m_cur.meta_key = '_currency' |
| 439 |
WHERE p.post_type = 'easy_invoice_payment' |
| 440 |
AND p.post_status = 'publish' |
| 441 |
AND m_stat.meta_value IN ('completed','approved','paid') |
| 442 |
AND CAST(m_amt.meta_value AS DECIMAL(20,4)) > 0 |
| 443 |
AND m_date.meta_value <> '' |
| 444 |
AND m_date.meta_value >= %s |
| 445 |
AND m_date.meta_value <= %s |
| 446 |
GROUP BY month_label, currency_code |
| 447 |
ORDER BY month_sort ASC", |
| 448 |
$global_currency, |
| 449 |
$start_iso, |
| 450 |
$end_iso |
| 451 |
); |
| 452 |
$rows = $wpdb->get_results($sql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is built with $wpdb->prepare() above. |
| 453 |
if ($rows === null) { |
| 454 |
return null; // hard DB error → caller falls back to PHP path |
| 455 |
} |
| 456 |
|
| 457 |
$buckets = []; |
| 458 |
foreach ($rows as $row) { |
| 459 |
$month = (string) $row->month_label; |
| 460 |
$code = (string) $row->currency_code; |
| 461 |
if (!isset($buckets[$month])) { |
| 462 |
$buckets[$month] = []; |
| 463 |
} |
| 464 |
$buckets[$month][$code] = [ |
| 465 |
'amount' => (float) $row->total_amount, |
| 466 |
'symbol' => \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol($code), |
| 467 |
]; |
| 468 |
} |
| 469 |
return $buckets; |
| 470 |
} |
| 471 |
} |
