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