| 1 |
<?php |
| 2 |
/** |
| 3 |
* Persisted invoice totals, so list headers, the dashboard and the reports can be |
| 4 |
* answered with SQL instead of loading every invoice as a model. |
| 5 |
* |
| 6 |
* The invoice total is computed by the model (items, discount, tax, per-invoice |
| 7 |
* overrides, legacy fallbacks to the global tax setting). Until 2.4.0 it was never |
| 8 |
* stored, so anything that needed "the sum of all open invoices" instantiated |
| 9 |
* thousands of models per page view. This service writes the computed total to |
| 10 |
* post meta whenever a document is saved, backfills documents that pre-date it, |
| 11 |
* and drops the cached value whenever something it depends on changes (the |
| 12 |
* document's own items/discount/tax meta, or the global tax settings). |
| 13 |
* |
| 14 |
* @package EasyInvoice |
| 15 |
* @since 2.4.0 |
| 16 |
*/ |
| 17 |
|
| 18 |
namespace EasyInvoice\Services; |
| 19 |
|
| 20 |
if ( ! defined( 'ABSPATH' ) ) { |
| 21 |
exit; |
| 22 |
} |
| 23 |
|
| 24 |
class InvoiceTotalsCache { |
| 25 |
|
| 26 |
const META_TOTAL = '_easy_invoice_cached_total'; |
| 27 |
const META_AT = '_easy_invoice_cached_at'; |
| 28 |
const OPTION_COMPLETE = 'easy_invoice_totals_cache_complete'; |
| 29 |
|
| 30 |
/** |
| 31 |
* Bump whenever the total arithmetic changes (calculateTotals, rounding, discount |
| 32 |
* caps…): every stored total is then dropped and rebuilt in the background. |
| 33 |
*/ |
| 34 |
const CACHE_VERSION = '2'; |
| 35 |
|
| 36 |
/** Statuses that still carry a balance. Mirrors InvoiceBalance::outstanding(). */ |
| 37 |
const OPEN_STATUSES = [ 'available', 'unpaid', 'partial', 'overdue', 'sent', 'pending' ]; |
| 38 |
|
| 39 |
/** Meta keys whose change makes a cached total stale. */ |
| 40 |
const DEPENDENT_META = [ |
| 41 |
'_easy_invoice_items', '_easy_invoice_discount_type', '_easy_invoice_discount_value', |
| 42 |
'_easy_invoice_discount_calculation_method', '_easy_invoice_tax_enabled', '_easy_invoice_tax_rate', |
| 43 |
'_easy_invoice_prices_include_tax', '_easy_invoice_customer_country', '_easy_invoice_customer_vat_number', |
| 44 |
]; |
| 45 |
|
| 46 |
/** Options whose change makes every cached total stale (legacy invoices follow them). */ |
| 47 |
const DEPENDENT_OPTIONS = [ |
| 48 |
'easy_invoice_tax_enabled', 'easy_invoice_tax_rate', 'easy_invoice_prices_include_tax', |
| 49 |
'easy_invoice_reverse_charge_enabled', 'easy_invoice_company_country', 'easy_invoice_company_vat_number', |
| 50 |
]; |
| 51 |
|
| 52 |
/** |
| 53 |
* Store the model's current total. |
| 54 |
* |
| 55 |
* @param \EasyInvoice\Models\Invoice $invoice Saved invoice. |
| 56 |
*/ |
| 57 |
public static function store( $invoice ): void { |
| 58 |
if ( ! is_callable( [ $invoice, 'getId' ] ) || ! is_callable( [ $invoice, 'getTotal' ] ) ) { |
| 59 |
return; |
| 60 |
} |
| 61 |
$id = (int) $invoice->getId(); |
| 62 |
if ( $id <= 0 ) { |
| 63 |
return; |
| 64 |
} |
| 65 |
update_post_meta( $id, self::META_TOTAL, (string) round( (float) $invoice->getTotal(), 2 ) ); |
| 66 |
update_post_meta( $id, self::META_AT, (string) time() ); |
| 67 |
self::bumpVersion(); |
| 68 |
} |
| 69 |
|
| 70 |
/** |
| 71 |
* Forget one invoice's cached total (recomputed by the next backfill or save). |
| 72 |
*/ |
| 73 |
public static function invalidate( int $invoice_id ): void { |
| 74 |
delete_post_meta( $invoice_id, self::META_TOTAL ); |
| 75 |
delete_post_meta( $invoice_id, self::META_AT ); |
| 76 |
delete_option( self::OPTION_COMPLETE ); |
| 77 |
self::bumpVersion(); |
| 78 |
} |
| 79 |
|
| 80 |
/** |
| 81 |
* Forget every cached total (a global tax setting changed). |
| 82 |
*/ |
| 83 |
public static function invalidateAll(): void { |
| 84 |
global $wpdb; |
| 85 |
$wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->postmeta} WHERE meta_key IN (%s, %s)", self::META_TOTAL, self::META_AT ) ); |
| 86 |
wp_cache_flush(); |
| 87 |
delete_option( self::OPTION_COMPLETE ); |
| 88 |
self::bumpVersion(); |
| 89 |
self::scheduleBackfill(); |
| 90 |
} |
| 91 |
|
| 92 |
/** |
| 93 |
* Site-wide aggregates are identical for every admin who opens a list, so they are |
| 94 |
* memoised in a transient keyed by a version that every invoice, payment or credit |
| 95 |
* write bumps (see bumpVersion()). |
| 96 |
*/ |
| 97 |
private static function memo( string $key, callable $compute ) { |
| 98 |
// Fixed key per aggregate; the version travels inside the value. Keying by |
| 99 |
// version instead left a new transient row behind after every write. |
| 100 |
$version = (int) get_option( 'easy_invoice_stats_version', 1 ); |
| 101 |
$tkey = 'ei_stats_' . $key; |
| 102 |
$hit = get_transient( $tkey ); |
| 103 |
if ( is_array( $hit ) && isset( $hit['v'], $hit['d'] ) && (int) $hit['v'] === $version ) { |
| 104 |
return $hit['d']; |
| 105 |
} |
| 106 |
$value = $compute(); |
| 107 |
set_transient( $tkey, [ 'v' => $version, 'd' => $value ], 10 * MINUTE_IN_SECONDS ); |
| 108 |
self::$bumped = false; // a later write in this request must bump again |
| 109 |
return $value; |
| 110 |
} |
| 111 |
|
| 112 |
/** @var bool Whether the version was already bumped since the last memoised read. */ |
| 113 |
private static $bumped = false; |
| 114 |
|
| 115 |
/** |
| 116 |
* Something that feeds the aggregates changed: forget every memoised value. |
| 117 |
*/ |
| 118 |
public static function bumpVersion(): void { |
| 119 |
if ( self::$bumped ) { |
| 120 |
return; // already bumped since the last read; one UPDATE per burst of writes |
| 121 |
} |
| 122 |
self::$bumped = true; |
| 123 |
update_option( 'easy_invoice_stats_version', (int) get_option( 'easy_invoice_stats_version', 1 ) + 1, false ); |
| 124 |
} |
| 125 |
|
| 126 |
/** |
| 127 |
* Published invoices that have no cached total yet. |
| 128 |
*/ |
| 129 |
public static function missingCount(): int { |
| 130 |
global $wpdb; |
| 131 |
return (int) $wpdb->get_var( $wpdb->prepare( |
| 132 |
"SELECT COUNT(*) FROM {$wpdb->posts} p |
| 133 |
LEFT JOIN {$wpdb->postmeta} c ON c.post_id = p.ID AND c.meta_key = %s |
| 134 |
WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' AND c.meta_id IS NULL", |
| 135 |
self::META_TOTAL |
| 136 |
) ); |
| 137 |
} |
| 138 |
|
| 139 |
/** |
| 140 |
* Compute and store totals for up to $limit invoices that lack one. |
| 141 |
* |
| 142 |
* @return int How many are still missing afterwards. |
| 143 |
*/ |
| 144 |
public static function backfill( int $limit = 200 ): int { |
| 145 |
global $wpdb; |
| 146 |
$ids = $wpdb->get_col( $wpdb->prepare( |
| 147 |
"SELECT p.ID FROM {$wpdb->posts} p |
| 148 |
LEFT JOIN {$wpdb->postmeta} c ON c.post_id = p.ID AND c.meta_key = %s |
| 149 |
WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' AND c.meta_id IS NULL |
| 150 |
ORDER BY p.ID DESC LIMIT %d", |
| 151 |
self::META_TOTAL, |
| 152 |
max( 1, $limit ) |
| 153 |
) ); |
| 154 |
if ( ! $ids ) { |
| 155 |
return 0; |
| 156 |
} |
| 157 |
$ids = array_map( 'intval', $ids ); |
| 158 |
update_meta_cache( 'post', $ids ); |
| 159 |
$repository = \EasyInvoice\Providers\InvoiceServiceProvider::getInvoiceRepository(); |
| 160 |
foreach ( $ids as $id ) { |
| 161 |
$invoice = $repository->find( $id ); |
| 162 |
if ( $invoice ) { |
| 163 |
self::store( $invoice ); |
| 164 |
} else { |
| 165 |
// Not loadable as an invoice: mark it so the query does not pick it up again. |
| 166 |
update_post_meta( $id, self::META_TOTAL, '0' ); |
| 167 |
update_post_meta( $id, self::META_AT, (string) time() ); |
| 168 |
} |
| 169 |
} |
| 170 |
return self::missingCount(); |
| 171 |
} |
| 172 |
|
| 173 |
/** |
| 174 |
* Queue a background backfill (one-off cron event, re-queued until nothing is missing). |
| 175 |
*/ |
| 176 |
public static function scheduleBackfill(): void { |
| 177 |
if ( ! wp_next_scheduled( 'easy_invoice_totals_backfill' ) ) { |
| 178 |
wp_schedule_single_event( time() + 5, 'easy_invoice_totals_backfill' ); |
| 179 |
} |
| 180 |
} |
| 181 |
|
| 182 |
/** |
| 183 |
* Cron callback: backfill in chunks until done. |
| 184 |
*/ |
| 185 |
public static function cronBackfill(): void { |
| 186 |
$remaining = self::backfill( 500 ); |
| 187 |
if ( $remaining > 0 ) { |
| 188 |
wp_schedule_single_event( time() + 10, 'easy_invoice_totals_backfill' ); |
| 189 |
} else { |
| 190 |
update_option( self::OPTION_COMPLETE, '1', false ); |
| 191 |
} |
| 192 |
} |
| 193 |
|
| 194 |
/** |
| 195 |
* Make sure the cache is usable for a statistics query: backfill a bounded number |
| 196 |
* inline and leave the rest to cron. |
| 197 |
*/ |
| 198 |
public static function ensure( int $inline_limit = 200 ): void { |
| 199 |
static $done = false; |
| 200 |
if ( $done ) { |
| 201 |
return; |
| 202 |
} |
| 203 |
$done = true; |
| 204 |
// Once every invoice has a stored total the flag is set; invalidate() and |
| 205 |
// invalidateAll() clear it, and save() stores a total for every new invoice, |
| 206 |
// so there is nothing to re-count on each view. |
| 207 |
if ( '1' === get_option( self::OPTION_COMPLETE, '' ) ) { |
| 208 |
return; |
| 209 |
} |
| 210 |
if ( self::missingCount() > 0 ) { |
| 211 |
$remaining = self::backfill( $inline_limit ); |
| 212 |
if ( $remaining > 0 ) { |
| 213 |
self::scheduleBackfill(); |
| 214 |
return; |
| 215 |
} |
| 216 |
} |
| 217 |
update_option( self::OPTION_COMPLETE, '1', false ); |
| 218 |
} |
| 219 |
|
| 220 |
/** |
| 221 |
* SQL fragment resolving an invoice's currency ('global' / '' → site currency). |
| 222 |
*/ |
| 223 |
private static function currencyExpr( string $alias ): string { |
| 224 |
global $wpdb; |
| 225 |
$site = strtoupper( (string) get_option( 'easy_invoice_currency_code', 'USD' ) ); |
| 226 |
return $wpdb->prepare( "UPPER(COALESCE(NULLIF(NULLIF({$alias}.meta_value, ''), 'global'), %s))", $site ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 227 |
} |
| 228 |
|
| 229 |
/** |
| 230 |
* Subquery: completed payment sums per invoice. |
| 231 |
*/ |
| 232 |
private static function paidSubquery(): string { |
| 233 |
global $wpdb; |
| 234 |
return "SELECT inv.meta_value AS invoice_id, SUM(CAST(amt.meta_value AS DECIMAL(18,4))) AS paid |
| 235 |
FROM {$wpdb->posts} pp |
| 236 |
INNER JOIN {$wpdb->postmeta} inv ON inv.post_id = pp.ID AND inv.meta_key = '_invoice_id' |
| 237 |
INNER JOIN {$wpdb->postmeta} amt ON amt.post_id = pp.ID AND amt.meta_key = '_amount' |
| 238 |
INNER JOIN {$wpdb->postmeta} st ON st.post_id = pp.ID AND st.meta_key = '_status' AND st.meta_value = 'completed' |
| 239 |
WHERE pp.post_type = 'easy_invoice_payment' AND pp.post_status = 'publish' |
| 240 |
GROUP BY inv.meta_value"; |
| 241 |
} |
| 242 |
|
| 243 |
/** |
| 244 |
* Subquery: credit note sums per invoice. |
| 245 |
*/ |
| 246 |
private static function creditSubquery(): string { |
| 247 |
global $wpdb; |
| 248 |
return "SELECT inv.meta_value AS invoice_id, SUM(CAST(tot.meta_value AS DECIMAL(18,4))) AS credited |
| 249 |
FROM {$wpdb->posts} cp |
| 250 |
INNER JOIN {$wpdb->postmeta} inv ON inv.post_id = cp.ID AND inv.meta_key = '_easy_invoice_credited_invoice_id' |
| 251 |
LEFT JOIN {$wpdb->postmeta} tot ON tot.post_id = cp.ID AND tot.meta_key = '_easy_invoice_total' |
| 252 |
WHERE cp.post_type = 'easy_invoice_credit' AND cp.post_status = 'publish' |
| 253 |
GROUP BY inv.meta_value"; |
| 254 |
} |
| 255 |
|
| 256 |
/** |
| 257 |
* Outstanding balances across all open invoices, by currency, with the overdue split. |
| 258 |
* Same shape as InvoiceBalance::outstanding(). |
| 259 |
* |
| 260 |
* @return array{count:int,overdue_count:int,amount:array<string,float>,overdue_amount:array<string,float>} |
| 261 |
*/ |
| 262 |
public static function outstanding(): array { |
| 263 |
return self::snapshot()['outstanding']; |
| 264 |
} |
| 265 |
|
| 266 |
/** |
| 267 |
* Site-wide figures the list headers, dashboard and clients page all need, |
| 268 |
* from one pass over the stored totals: outstanding balances (by currency, with |
| 269 |
* the overdue split) and paid revenue net of credits. Memoised together so a |
| 270 |
* page that shows both pays for one scan, not three. |
| 271 |
*/ |
| 272 |
public static function snapshot(): array { |
| 273 |
return self::memo( 'snapshot', [ self::class, 'computeSnapshot' ] ); |
| 274 |
} |
| 275 |
|
| 276 |
/** @internal */ |
| 277 |
public static function computeSnapshot(): array { |
| 278 |
global $wpdb; |
| 279 |
self::ensure(); |
| 280 |
$today = gmdate( 'Y-m-d', current_time( 'timestamp' ) ); |
| 281 |
$open = "'" . implode( "','", array_map( 'esc_sql', self::OPEN_STATUSES ) ) . "'"; |
| 282 |
$cur = self::currencyExpr( 'cur' ); |
| 283 |
$paid = self::paidSubquery(); |
| 284 |
$credit = self::creditSubquery(); |
| 285 |
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- fragments above are prepared / constant. |
| 286 |
$rows = $wpdb->get_results( $wpdb->prepare( |
| 287 |
"SELECT {$cur} AS currency, |
| 288 |
(s.meta_value = 'paid') AS is_paid, |
| 289 |
(s.meta_value IN ({$open})) AS is_open, |
| 290 |
(CASE WHEN dd.meta_value IS NOT NULL AND dd.meta_value <> '' AND dd.meta_value < %s THEN 1 ELSE 0 END) AS overdue, |
| 291 |
COUNT(*) AS n, |
| 292 |
SUM(CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(cr.credited, 0)) AS net_total, |
| 293 |
SUM(CASE WHEN CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(pd.paid, 0) - COALESCE(cr.credited, 0) > 0.004 THEN 1 ELSE 0 END) AS n_due, |
| 294 |
SUM(GREATEST(0, CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(pd.paid, 0) - COALESCE(cr.credited, 0))) AS due |
| 295 |
FROM {$wpdb->posts} p |
| 296 |
INNER JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_easy_invoice_status' |
| 297 |
INNER JOIN {$wpdb->postmeta} c ON c.post_id = p.ID AND c.meta_key = %s |
| 298 |
LEFT JOIN {$wpdb->postmeta} cur ON cur.post_id = p.ID AND cur.meta_key = '_easy_invoice_currency_code' |
| 299 |
LEFT JOIN {$wpdb->postmeta} dd ON dd.post_id = p.ID AND dd.meta_key = '_easy_invoice_due_date' |
| 300 |
LEFT JOIN ({$paid}) pd ON pd.invoice_id = p.ID |
| 301 |
LEFT JOIN ({$credit}) cr ON cr.invoice_id = p.ID |
| 302 |
WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' |
| 303 |
AND (s.meta_value = 'paid' OR s.meta_value IN ({$open})) |
| 304 |
GROUP BY currency, is_paid, is_open, overdue", |
| 305 |
$today, |
| 306 |
self::META_TOTAL |
| 307 |
), ARRAY_A ); |
| 308 |
$counts = $wpdb->get_row( |
| 309 |
"SELECT COUNT(*) AS n, COUNT(DISTINCT NULLIF(cl.meta_value, '0')) AS clients |
| 310 |
FROM {$wpdb->posts} p |
| 311 |
LEFT JOIN {$wpdb->postmeta} cl ON cl.post_id = p.ID AND cl.meta_key = '_easy_invoice_client_id' |
| 312 |
WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish'", |
| 313 |
ARRAY_A |
| 314 |
); |
| 315 |
// phpcs:enable |
| 316 |
$out = [ 'count' => 0, 'overdue_count' => 0, 'amount' => [], 'overdue_amount' => [], 'count_by_currency' => [] ]; |
| 317 |
$revenue = []; $paid_n = 0; |
| 318 |
foreach ( (array) $rows as $r ) { |
| 319 |
$c = (string) $r['currency']; |
| 320 |
if ( (int) $r['is_paid'] ) { |
| 321 |
$revenue[ $c ] = [ 'amount' => round( ( $revenue[ $c ]['amount'] ?? 0 ) + (float) $r['net_total'], 2 ), 'symbol' => \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol( $c ) ]; |
| 322 |
$paid_n += (int) $r['n']; |
| 323 |
} |
| 324 |
if ( (int) $r['is_open'] && (int) $r['n_due'] > 0 ) { |
| 325 |
$out['count'] += (int) $r['n_due']; |
| 326 |
$out['count_by_currency'][ $c ] = ( $out['count_by_currency'][ $c ] ?? 0 ) + (int) $r['n_due']; |
| 327 |
$out['amount'][ $c ] = round( ( $out['amount'][ $c ] ?? 0 ) + (float) $r['due'], 2 ); |
| 328 |
if ( (int) $r['overdue'] ) { |
| 329 |
$out['overdue_count'] += (int) $r['n_due']; |
| 330 |
$out['overdue_amount'][ $c ] = round( ( $out['overdue_amount'][ $c ] ?? 0 ) + (float) $r['due'], 2 ); |
| 331 |
} |
| 332 |
} |
| 333 |
} |
| 334 |
return [ |
| 335 |
'outstanding' => $out, |
| 336 |
'paid' => [ |
| 337 |
'revenue' => $revenue, |
| 338 |
'paid_count' => $paid_n, |
| 339 |
'invoice_count' => (int) ( $counts['n'] ?? 0 ), |
| 340 |
'client_count' => (int) ( $counts['clients'] ?? 0 ), |
| 341 |
], |
| 342 |
]; |
| 343 |
} |
| 344 |
|
| 345 |
/** @internal */ |
| 346 |
public static function computeOutstanding(): array { |
| 347 |
global $wpdb; |
| 348 |
self::ensure(); |
| 349 |
$today = gmdate( 'Y-m-d', current_time( 'timestamp' ) ); |
| 350 |
$in = "'" . implode( "','", array_map( 'esc_sql', self::OPEN_STATUSES ) ) . "'"; |
| 351 |
$cur = self::currencyExpr( 'cur' ); |
| 352 |
$paid = self::paidSubquery(); |
| 353 |
$credit = self::creditSubquery(); |
| 354 |
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- fragments above are prepared / constant. |
| 355 |
$rows = $wpdb->get_results( $wpdb->prepare( |
| 356 |
"SELECT {$cur} AS currency, |
| 357 |
(CASE WHEN dd.meta_value IS NOT NULL AND dd.meta_value <> '' AND dd.meta_value < %s THEN 1 ELSE 0 END) AS overdue, |
| 358 |
COUNT(*) AS n, |
| 359 |
SUM(GREATEST(0, CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(pd.paid, 0) - COALESCE(cr.credited, 0))) AS due |
| 360 |
FROM {$wpdb->posts} p |
| 361 |
INNER JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_easy_invoice_status' AND s.meta_value IN ({$in}) |
| 362 |
INNER JOIN {$wpdb->postmeta} c ON c.post_id = p.ID AND c.meta_key = %s |
| 363 |
LEFT JOIN {$wpdb->postmeta} cur ON cur.post_id = p.ID AND cur.meta_key = '_easy_invoice_currency_code' |
| 364 |
LEFT JOIN {$wpdb->postmeta} dd ON dd.post_id = p.ID AND dd.meta_key = '_easy_invoice_due_date' |
| 365 |
LEFT JOIN ({$paid}) pd ON pd.invoice_id = p.ID |
| 366 |
LEFT JOIN ({$credit}) cr ON cr.invoice_id = p.ID |
| 367 |
WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' |
| 368 |
AND CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(pd.paid, 0) - COALESCE(cr.credited, 0) > 0.004 |
| 369 |
GROUP BY currency, overdue", |
| 370 |
$today, |
| 371 |
self::META_TOTAL |
| 372 |
), ARRAY_A ); |
| 373 |
// phpcs:enable |
| 374 |
$out = [ 'count' => 0, 'overdue_count' => 0, 'amount' => [], 'overdue_amount' => [], 'count_by_currency' => [] ]; |
| 375 |
foreach ( (array) $rows as $r ) { |
| 376 |
$c = (string) $r['currency']; |
| 377 |
$out['count'] += (int) $r['n']; |
| 378 |
$out['count_by_currency'][ $c ] = ( $out['count_by_currency'][ $c ] ?? 0 ) + (int) $r['n']; |
| 379 |
$out['amount'][ $c ] = round( ( $out['amount'][ $c ] ?? 0 ) + (float) $r['due'], 2 ); |
| 380 |
if ( (int) $r['overdue'] ) { |
| 381 |
$out['overdue_count'] += (int) $r['n']; |
| 382 |
$out['overdue_amount'][ $c ] = round( ( $out['overdue_amount'][ $c ] ?? 0 ) + (float) $r['due'], 2 ); |
| 383 |
} |
| 384 |
} |
| 385 |
return $out; |
| 386 |
} |
| 387 |
|
| 388 |
/** |
| 389 |
* Revenue from paid invoices (issued in the range when given), net of credit notes, |
| 390 |
* grouped by currency; plus the number of invoices issued and distinct clients billed. |
| 391 |
* |
| 392 |
* @return array{revenue:array<string,array{amount:float,symbol:string}>,paid_count:int,invoice_count:int,client_count:int} |
| 393 |
*/ |
| 394 |
public static function paidRevenue( string $start_date = '', string $end_date = '' ): array { |
| 395 |
if ( '' === $start_date && '' === $end_date ) { |
| 396 |
return self::snapshot()['paid']; |
| 397 |
} |
| 398 |
return self::memo( 'paid_' . md5( $start_date . '|' . $end_date ), static function () use ( $start_date, $end_date ) { return self::computePaidRevenue( $start_date, $end_date ); } ); |
| 399 |
} |
| 400 |
|
| 401 |
/** @internal */ |
| 402 |
public static function computePaidRevenue( string $start_date = '', string $end_date = '' ): array { |
| 403 |
global $wpdb; |
| 404 |
self::ensure(); |
| 405 |
$cur = self::currencyExpr( 'cur' ); |
| 406 |
$credit = self::creditSubquery(); |
| 407 |
$where = ''; |
| 408 |
$args = [ self::META_TOTAL ]; |
| 409 |
if ( '' !== $start_date ) { $where .= ' AND d.meta_value >= %s'; $args[] = $start_date; } |
| 410 |
if ( '' !== $end_date ) { $where .= ' AND d.meta_value <= %s'; $args[] = $end_date; } |
| 411 |
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 412 |
$rows = $wpdb->get_results( $wpdb->prepare( |
| 413 |
"SELECT {$cur} AS currency, COUNT(*) AS n, |
| 414 |
SUM(CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(cr.credited, 0)) AS revenue |
| 415 |
FROM {$wpdb->posts} p |
| 416 |
INNER JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_easy_invoice_status' AND s.meta_value = 'paid' |
| 417 |
INNER JOIN {$wpdb->postmeta} c ON c.post_id = p.ID AND c.meta_key = %s |
| 418 |
LEFT JOIN {$wpdb->postmeta} d ON d.post_id = p.ID AND d.meta_key = '_easy_invoice_issue_date' |
| 419 |
LEFT JOIN {$wpdb->postmeta} cur ON cur.post_id = p.ID AND cur.meta_key = '_easy_invoice_currency_code' |
| 420 |
LEFT JOIN ({$credit}) cr ON cr.invoice_id = p.ID |
| 421 |
WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' {$where} |
| 422 |
GROUP BY currency", |
| 423 |
...$args |
| 424 |
), ARRAY_A ); |
| 425 |
$count_sql = "SELECT COUNT(*) AS n, COUNT(DISTINCT NULLIF(cl.meta_value, '0')) AS clients |
| 426 |
FROM {$wpdb->posts} p |
| 427 |
LEFT JOIN {$wpdb->postmeta} d ON d.post_id = p.ID AND d.meta_key = '_easy_invoice_issue_date' |
| 428 |
LEFT JOIN {$wpdb->postmeta} cl ON cl.post_id = p.ID AND cl.meta_key = '_easy_invoice_client_id' |
| 429 |
WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' " . $where; |
| 430 |
$count_args = array_slice( $args, 1 ); |
| 431 |
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnnecessaryPrepare -- $count_sql is built above from constants plus %s placeholders; prepared when it has any. |
| 432 |
$counts = $wpdb->get_row( $count_args ? $wpdb->prepare( $count_sql, ...$count_args ) : $count_sql, ARRAY_A ); |
| 433 |
// phpcs:enable |
| 434 |
$revenue = []; $paid = 0; |
| 435 |
foreach ( (array) $rows as $r ) { |
| 436 |
$c = (string) $r['currency']; |
| 437 |
$revenue[ $c ] = [ 'amount' => round( (float) $r['revenue'], 2 ), 'symbol' => \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol( $c ) ]; |
| 438 |
$paid += (int) $r['n']; |
| 439 |
} |
| 440 |
return [ |
| 441 |
'revenue' => $revenue, |
| 442 |
'paid_count' => $paid, |
| 443 |
'invoice_count' => (int) ( $counts['n'] ?? 0 ), |
| 444 |
'client_count' => (int) ( $counts['clients'] ?? 0 ), |
| 445 |
]; |
| 446 |
} |
| 447 |
|
| 448 |
/** |
| 449 |
* Invoice counts by display status (paid / partial / unpaid / overdue / draft / canceled) |
| 450 |
* for invoices issued in the range. |
| 451 |
* |
| 452 |
* @return array<string,int> |
| 453 |
*/ |
| 454 |
public static function statusCounts( string $start_date = '', string $end_date = '' ): array { |
| 455 |
return self::memo( 'status_' . md5( $start_date . '|' . $end_date ), static function () use ( $start_date, $end_date ) { return self::computeStatusCounts( $start_date, $end_date ); } ); |
| 456 |
} |
| 457 |
|
| 458 |
/** @internal */ |
| 459 |
public static function computeStatusCounts( string $start_date = '', string $end_date = '' ): array { |
| 460 |
global $wpdb; |
| 461 |
$today = gmdate( 'Y-m-d', current_time( 'timestamp' ) ); |
| 462 |
$where = ''; $args = [ $today ]; |
| 463 |
if ( '' !== $start_date ) { $where .= ' AND d.meta_value >= %s'; $args[] = $start_date; } |
| 464 |
if ( '' !== $end_date ) { $where .= ' AND d.meta_value <= %s'; $args[] = $end_date; } |
| 465 |
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 466 |
$rows = $wpdb->get_results( $wpdb->prepare( |
| 467 |
"SELECT CASE |
| 468 |
WHEN s.meta_value IN ('paid','completed') THEN 'paid' |
| 469 |
WHEN s.meta_value IN ('partial','partially_paid') THEN 'partial' |
| 470 |
WHEN s.meta_value = 'draft' THEN 'draft' |
| 471 |
WHEN s.meta_value IN ('cancelled','canceled') THEN 'canceled' |
| 472 |
WHEN s.meta_value = 'overdue' THEN 'overdue' |
| 473 |
WHEN s.meta_value IN ('available','unpaid','sent','pending') AND dd.meta_value IS NOT NULL AND dd.meta_value <> '' AND dd.meta_value < %s THEN 'overdue' |
| 474 |
WHEN s.meta_value IN ('available','unpaid','sent','pending') THEN 'unpaid' |
| 475 |
ELSE 'other' END AS k, COUNT(*) AS n |
| 476 |
FROM {$wpdb->posts} p |
| 477 |
INNER JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_easy_invoice_status' |
| 478 |
LEFT JOIN {$wpdb->postmeta} d ON d.post_id = p.ID AND d.meta_key = '_easy_invoice_issue_date' |
| 479 |
LEFT JOIN {$wpdb->postmeta} dd ON dd.post_id = p.ID AND dd.meta_key = '_easy_invoice_due_date' |
| 480 |
WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' {$where} |
| 481 |
GROUP BY k", |
| 482 |
...$args |
| 483 |
), ARRAY_A ); |
| 484 |
// phpcs:enable |
| 485 |
$out = []; |
| 486 |
foreach ( (array) $rows as $r ) { |
| 487 |
$out[ (string) $r['k'] ] = (int) $r['n']; |
| 488 |
} |
| 489 |
return $out; |
| 490 |
} |
| 491 |
|
| 492 |
/** |
| 493 |
* Clients ranked by paid revenue (net of credits) in the range. |
| 494 |
* |
| 495 |
* @return array<int,array{id:int,total_amount:array<string,array{amount:float,symbol:string}>,total_invoices:int,last_invoice:string}> |
| 496 |
*/ |
| 497 |
public static function topClients( string $start_date = '', string $end_date = '', int $limit = 10 ): array { |
| 498 |
return self::memo( 'top_' . md5( $start_date . '|' . $end_date . '|' . $limit ), static function () use ( $start_date, $end_date, $limit ) { return self::computeTopClients( $start_date, $end_date, $limit ); } ); |
| 499 |
} |
| 500 |
|
| 501 |
/** @internal */ |
| 502 |
public static function computeTopClients( string $start_date = '', string $end_date = '', int $limit = 10 ): array { |
| 503 |
global $wpdb; |
| 504 |
self::ensure(); |
| 505 |
$cur = self::currencyExpr( 'cur' ); |
| 506 |
$credit = self::creditSubquery(); |
| 507 |
$where = ''; $args = [ self::META_TOTAL ]; |
| 508 |
if ( '' !== $start_date ) { $where .= ' AND d.meta_value >= %s'; $args[] = $start_date; } |
| 509 |
if ( '' !== $end_date ) { $where .= ' AND d.meta_value <= %s'; $args[] = $end_date; } |
| 510 |
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 511 |
$rows = $wpdb->get_results( $wpdb->prepare( |
| 512 |
"SELECT CAST(cl.meta_value AS UNSIGNED) AS client_id, {$cur} AS currency, |
| 513 |
COUNT(*) AS n, MAX(d.meta_value) AS last_issue, |
| 514 |
SUM(CASE WHEN s.meta_value = 'paid' THEN CAST(COALESCE(c.meta_value, '0') AS DECIMAL(18,4)) - COALESCE(cr.credited, 0) ELSE 0 END) AS revenue |
| 515 |
FROM {$wpdb->posts} p |
| 516 |
INNER JOIN {$wpdb->postmeta} cl ON cl.post_id = p.ID AND cl.meta_key = '_easy_invoice_client_id' AND cl.meta_value <> '' AND cl.meta_value <> '0' |
| 517 |
INNER JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_easy_invoice_status' |
| 518 |
LEFT JOIN {$wpdb->postmeta} c ON c.post_id = p.ID AND c.meta_key = %s |
| 519 |
LEFT JOIN {$wpdb->postmeta} d ON d.post_id = p.ID AND d.meta_key = '_easy_invoice_issue_date' |
| 520 |
LEFT JOIN {$wpdb->postmeta} cur ON cur.post_id = p.ID AND cur.meta_key = '_easy_invoice_currency_code' |
| 521 |
LEFT JOIN ({$credit}) cr ON cr.invoice_id = p.ID |
| 522 |
WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' {$where} |
| 523 |
GROUP BY client_id, currency", |
| 524 |
...$args |
| 525 |
), ARRAY_A ); |
| 526 |
// phpcs:enable |
| 527 |
$site = strtoupper( (string) get_option( 'easy_invoice_currency_code', 'USD' ) ); |
| 528 |
$clients = []; |
| 529 |
foreach ( (array) $rows as $r ) { |
| 530 |
$cid = (int) $r['client_id']; |
| 531 |
if ( ! isset( $clients[ $cid ] ) ) { |
| 532 |
$clients[ $cid ] = [ 'id' => $cid, 'total_amount' => [], 'total_invoices' => 0, 'last_invoice' => '' ]; |
| 533 |
} |
| 534 |
$clients[ $cid ]['total_invoices'] += (int) $r['n']; |
| 535 |
if ( (string) $r['last_issue'] > $clients[ $cid ]['last_invoice'] ) { |
| 536 |
$clients[ $cid ]['last_invoice'] = (string) $r['last_issue']; |
| 537 |
} |
| 538 |
$rev = round( (float) $r['revenue'], 2 ); |
| 539 |
if ( $rev > 0 ) { |
| 540 |
$c = (string) $r['currency']; |
| 541 |
$clients[ $cid ]['total_amount'][ $c ] = [ 'amount' => round( ( $clients[ $cid ]['total_amount'][ $c ]['amount'] ?? 0 ) + $rev, 2 ), 'symbol' => \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol( $c ) ]; |
| 542 |
} |
| 543 |
} |
| 544 |
$score = static function ( $row ) use ( $site ) { |
| 545 |
$sum = 0.0; |
| 546 |
foreach ( $row['total_amount'] as $data ) { |
| 547 |
$sum += (float) $data['amount']; |
| 548 |
} |
| 549 |
return [ $sum, (float) ( $row['total_amount'][ $site ]['amount'] ?? 0 ), $row['total_invoices'] ]; |
| 550 |
}; |
| 551 |
uasort( $clients, static function ( $a, $b ) use ( $score ) { |
| 552 |
return $score( $b ) <=> $score( $a ); |
| 553 |
} ); |
| 554 |
return array_slice( $clients, 0, $limit, true ); |
| 555 |
} |
| 556 |
} |
| 557 |
|
