| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentCart\App\Modules\MCP\Tools; |
| 4 |
|
| 5 |
use FluentCart\App\Helpers\Helper; |
| 6 |
use FluentCart\App\App; |
| 7 |
use FluentCart\App\Models\Order; |
| 8 |
use FluentCart\App\Models\OrderItem; |
| 9 |
use FluentCart\App\Models\Customer; |
| 10 |
use FluentCart\App\Modules\MCP\Support\MCPHelper; |
| 11 |
use FluentCart\App\Modules\MCP\Support\PermissionGate; |
| 12 |
|
| 13 |
/** |
| 14 |
* Reports & analytics — the headline research surface. |
| 15 |
* |
| 16 |
* Design rules baked in: |
| 17 |
* - Every report is CURRENCY-SCOPED: it filters to one currency (the store |
| 18 |
* default unless `currency` is passed) so totals are never silently summed |
| 19 |
* across currencies. The chosen currency is echoed in meta.currency. |
| 20 |
* - "Revenue" definitions are explicit and consistent across tools (see |
| 21 |
* metricDefs): gross = sum(total_amount) of paid orders; net = paid − |
| 22 |
* refunded; paid orders = payment_status in the paid set. |
| 23 |
* - Date basis is created_at (echoed as meta.date_basis) for determinism. |
| 24 |
* - Server-side aggregation only — no raw row dumps. query-orders caps at 200 |
| 25 |
* grouped rows; trend caps its bucket count. |
| 26 |
* - Each report returns an NL summary the agent can quote verbatim. |
| 27 |
* |
| 28 |
* Parameter design: a shared `range` enum (today … last_year) resolves to a |
| 29 |
* UTC window server-side, with explicit start_date/end_date as an override — |
| 30 |
* the agent never has to compute "last month" itself. |
| 31 |
*/ |
| 32 |
class ReportTools |
| 33 |
{ |
| 34 |
const PAID = ['paid', 'partially_paid', 'partially_refunded']; |
| 35 |
|
| 36 |
const RANGES = ['today', 'yesterday', 'last_7_days', 'last_30_days', 'this_month', 'last_month', 'mtd', 'qtd', 'ytd', 'last_quarter', 'last_year']; |
| 37 |
|
| 38 |
const MAX_BUCKETS = 180; |
| 39 |
|
| 40 |
const MAX_ROWS = 200; |
| 41 |
|
| 42 |
public static function definitions() |
| 43 |
{ |
| 44 |
$rangeProp = ['type' => 'string', 'enum' => self::RANGES, 'description' => 'Relative window, resolved in UTC to match the store reports. Or pass start_date + end_date.']; |
| 45 |
|
| 46 |
return [ |
| 47 |
'fluent-cart/get-sales-report' => [ |
| 48 |
'label' => __('Get Sales Report', 'fluent-cart'), |
| 49 |
'description' => __('Revenue overview for a period with comparison to the prior equal period: gross, net, paid, refunded, tax, shipping, fees, order count, AOV, unique customers, and percent change. Scoped to one currency, the store default unless a currency is given.', 'fluent-cart'), |
| 50 |
'input_schema' => [ |
| 51 |
'type' => 'object', |
| 52 |
'properties' => [ |
| 53 |
'range' => $rangeProp, |
| 54 |
'start_date' => ['type' => 'string', 'description' => 'YYYY-MM-DD, UTC. Overrides range.'], |
| 55 |
'end_date' => ['type' => 'string', 'description' => 'YYYY-MM-DD, UTC. Overrides range.'], |
| 56 |
'currency' => ['type' => 'string', 'description' => 'ISO currency. Defaults to the store currency.'], |
| 57 |
'compare' => ['type' => 'boolean', 'default' => true, 'description' => 'Include prior-period comparison.'], |
| 58 |
], |
| 59 |
], |
| 60 |
'execute_callback' => [self::class, 'getSalesReport'], |
| 61 |
'permission_callback' => function () { |
| 62 |
return PermissionGate::can('reports/view'); |
| 63 |
}, |
| 64 |
'annotations' => ['readonly' => true], |
| 65 |
], |
| 66 |
|
| 67 |
'fluent-cart/get-sales-trend' => [ |
| 68 |
'label' => __('Get Sales Trend', 'fluent-cart'), |
| 69 |
'description' => __('Time series of revenue and order count bucketed by day, week, or month over a period. Scoped to one currency. Use to see growth and seasonality.', 'fluent-cart'), |
| 70 |
'input_schema' => [ |
| 71 |
'type' => 'object', |
| 72 |
'properties' => [ |
| 73 |
'range' => $rangeProp, |
| 74 |
'start_date' => ['type' => 'string', 'description' => 'YYYY-MM-DD, UTC.'], |
| 75 |
'end_date' => ['type' => 'string', 'description' => 'YYYY-MM-DD, UTC.'], |
| 76 |
'interval' => ['type' => 'string', 'enum' => ['day', 'week', 'month'], 'default' => 'day'], |
| 77 |
'currency' => ['type' => 'string'], |
| 78 |
], |
| 79 |
], |
| 80 |
'execute_callback' => [self::class, 'getSalesTrend'], |
| 81 |
'permission_callback' => function () { |
| 82 |
return PermissionGate::can('reports/view'); |
| 83 |
}, |
| 84 |
'annotations' => ['readonly' => true], |
| 85 |
], |
| 86 |
|
| 87 |
'fluent-cart/get-top-products' => [ |
| 88 |
'label' => __('Get Top Products', 'fluent-cart'), |
| 89 |
'description' => __('Best-selling products over a period, ranked by revenue or units sold. Scoped to one currency.', 'fluent-cart'), |
| 90 |
'input_schema' => [ |
| 91 |
'type' => 'object', |
| 92 |
'properties' => [ |
| 93 |
'range' => $rangeProp, |
| 94 |
'start_date' => ['type' => 'string'], |
| 95 |
'end_date' => ['type' => 'string'], |
| 96 |
'metric' => ['type' => 'string', 'enum' => ['revenue', 'units'], 'default' => 'revenue'], |
| 97 |
'currency' => ['type' => 'string'], |
| 98 |
'limit' => ['type' => 'integer', 'default' => 10, 'description' => 'Max 50.'], |
| 99 |
], |
| 100 |
], |
| 101 |
'execute_callback' => [self::class, 'getTopProducts'], |
| 102 |
'permission_callback' => function () { |
| 103 |
return PermissionGate::can('reports/view'); |
| 104 |
}, |
| 105 |
'annotations' => ['readonly' => true], |
| 106 |
], |
| 107 |
|
| 108 |
'fluent-cart/get-refund-report' => [ |
| 109 |
'label' => __('Get Refund Report', 'fluent-cart'), |
| 110 |
'description' => __('Refund metrics for a period: refunded order count, refund rate as a share of paid orders, total and average refunded amount. Scoped to one currency.', 'fluent-cart'), |
| 111 |
'input_schema' => [ |
| 112 |
'type' => 'object', |
| 113 |
'properties' => [ |
| 114 |
'range' => $rangeProp, |
| 115 |
'start_date' => ['type' => 'string'], |
| 116 |
'end_date' => ['type' => 'string'], |
| 117 |
'currency' => ['type' => 'string'], |
| 118 |
], |
| 119 |
], |
| 120 |
'execute_callback' => [self::class, 'getRefundReport'], |
| 121 |
'permission_callback' => function () { |
| 122 |
return PermissionGate::can('reports/view'); |
| 123 |
}, |
| 124 |
'annotations' => ['readonly' => true], |
| 125 |
], |
| 126 |
|
| 127 |
'fluent-cart/query-sources' => [ |
| 128 |
'label' => __('Query Sources (UTM attribution)', 'fluent-cart'), |
| 129 |
'description' => __('Flexible UTM attribution: pick metrics and group by any UTM fields — source, medium, campaign, term, content, id — over a period, with optional source/medium/campaign filters to drill down. Scoped to one currency, paid orders. Orders with no UTM fall under a none bucket. Returns up to 200 rows ranked by the first metric.', 'fluent-cart'), |
| 130 |
'input_schema' => [ |
| 131 |
'type' => 'object', |
| 132 |
'properties' => [ |
| 133 |
'metrics' => ['type' => 'array', 'description' => 'Defaults to orders and gross_revenue.', 'items' => ['type' => 'string', 'enum' => ['orders', 'gross_revenue', 'net_revenue', 'aov', 'unique_customers', 'refunded_amount']]], |
| 134 |
'dimensions' => ['type' => 'array', 'description' => 'Group by these UTM fields. Defaults to utm_source, utm_medium, utm_campaign.', 'items' => ['type' => 'string', 'enum' => ['utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content', 'utm_id']]], |
| 135 |
'utm_source' => ['type' => 'string', 'description' => 'Filter to one source, exact match.'], |
| 136 |
'utm_medium' => ['type' => 'string', 'description' => 'Filter to one medium, exact match.'], |
| 137 |
'utm_campaign' => ['type' => 'string', 'description' => 'Filter to one campaign, exact match.'], |
| 138 |
'range' => $rangeProp, |
| 139 |
'start_date' => ['type' => 'string'], |
| 140 |
'end_date' => ['type' => 'string'], |
| 141 |
'currency' => ['type' => 'string'], |
| 142 |
'limit' => ['type' => 'integer', 'default' => 50, 'description' => 'Max 200.'], |
| 143 |
], |
| 144 |
], |
| 145 |
'execute_callback' => [self::class, 'querySources'], |
| 146 |
'permission_callback' => function () { |
| 147 |
return PermissionGate::can('reports/view'); |
| 148 |
}, |
| 149 |
'annotations' => ['readonly' => true], |
| 150 |
], |
| 151 |
|
| 152 |
'fluent-cart/query-orders' => [ |
| 153 |
'label' => __('Query Orders (flexible aggregate)', 'fluent-cart'), |
| 154 |
'description' => __('Flexible order analytics: pick metrics and group by dimensions with filters. Use when a fixed report does not fit, for example revenue by payment_status this month, or orders by month. Scoped to one currency. Returns up to 200 grouped rows.', 'fluent-cart'), |
| 155 |
'input_schema' => [ |
| 156 |
'type' => 'object', |
| 157 |
'properties' => [ |
| 158 |
'metrics' => [ |
| 159 |
'type' => 'array', |
| 160 |
'description' => 'One or more. Defaults to order_count and gross_revenue.', |
| 161 |
'items' => ['type' => 'string', 'enum' => ['order_count', 'gross_revenue', 'paid_revenue', 'refunded_amount', 'aov', 'unique_customers']], |
| 162 |
], |
| 163 |
'dimensions' => [ |
| 164 |
'type' => 'array', |
| 165 |
'description' => 'Group by these. Empty means a single total row.', |
| 166 |
'items' => ['type' => 'string', 'enum' => ['day', 'week', 'month', 'status', 'payment_status']], |
| 167 |
], |
| 168 |
'range' => $rangeProp, |
| 169 |
'start_date' => ['type' => 'string'], |
| 170 |
'end_date' => ['type' => 'string'], |
| 171 |
'currency' => ['type' => 'string'], |
| 172 |
'sort_desc' => ['type' => 'boolean', 'default' => true, 'description' => 'Sort by the first metric descending. When grouping by a time dimension (day/week/month), rows default to chronological order unless you set this explicitly.'], |
| 173 |
], |
| 174 |
], |
| 175 |
'execute_callback' => [self::class, 'queryOrders'], |
| 176 |
'permission_callback' => function () { |
| 177 |
return PermissionGate::can('reports/view'); |
| 178 |
}, |
| 179 |
'annotations' => ['readonly' => true], |
| 180 |
], |
| 181 |
|
| 182 |
'fluent-cart/query-products' => [ |
| 183 |
'label' => __('Query Products (flexible aggregate)', 'fluent-cart'), |
| 184 |
'description' => __('Flexible product-sales analytics over sold items: pick metrics and group by product or variation, within a period and one currency. For a time series use get-sales-trend. Returns up to 200 rows.', 'fluent-cart'), |
| 185 |
'input_schema' => [ |
| 186 |
'type' => 'object', |
| 187 |
'properties' => [ |
| 188 |
'metrics' => ['type' => 'array', 'description' => 'Defaults to units_sold and line_revenue. net_revenue = line_revenue minus refunds.', 'items' => ['type' => 'string', 'enum' => ['units_sold', 'line_revenue', 'net_revenue', 'order_count', 'avg_unit_price', 'refund_amount']]], |
| 189 |
'dimensions' => ['type' => 'array', 'items' => ['type' => 'string', 'enum' => ['product', 'variation']]], |
| 190 |
'range' => $rangeProp, |
| 191 |
'start_date' => ['type' => 'string'], |
| 192 |
'end_date' => ['type' => 'string'], |
| 193 |
'currency' => ['type' => 'string'], |
| 194 |
], |
| 195 |
], |
| 196 |
'execute_callback' => [self::class, 'queryProducts'], |
| 197 |
'permission_callback' => function () { |
| 198 |
return PermissionGate::can('reports/view'); |
| 199 |
}, |
| 200 |
'annotations' => ['readonly' => true], |
| 201 |
], |
| 202 |
|
| 203 |
'fluent-cart/query-customers' => [ |
| 204 |
'label' => __('Query Customers (flexible aggregate)', 'fluent-cart'), |
| 205 |
'description' => __('Flexible customer analytics: pick metrics and group by country, state, status, or first/last purchase month, with optional filters. LTV is in store currency. Returns up to 200 rows.', 'fluent-cart'), |
| 206 |
'input_schema' => [ |
| 207 |
'type' => 'object', |
| 208 |
'properties' => [ |
| 209 |
'metrics' => ['type' => 'array', 'description' => 'Defaults to customer_count and total_ltv.', 'items' => ['type' => 'string', 'enum' => ['customer_count', 'total_ltv', 'avg_ltv', 'avg_purchase_count', 'repeat_customers']]], |
| 210 |
'dimensions' => ['type' => 'array', 'items' => ['type' => 'string', 'enum' => ['country', 'state', 'status', 'first_purchase_month', 'last_purchase_month']]], |
| 211 |
'country' => ['type' => 'string'], |
| 212 |
'status' => ['type' => 'string', 'enum' => ['active', 'archived']], |
| 213 |
'min_ltv' => ['type' => 'number', 'description' => 'Minimum LTV in store currency.'], |
| 214 |
'min_purchase_count' => ['type' => 'integer'], |
| 215 |
], |
| 216 |
], |
| 217 |
'execute_callback' => [self::class, 'queryCustomers'], |
| 218 |
'permission_callback' => function () { |
| 219 |
return PermissionGate::can('reports/view'); |
| 220 |
}, |
| 221 |
'annotations' => ['readonly' => true], |
| 222 |
], |
| 223 |
]; |
| 224 |
} |
| 225 |
|
| 226 |
// ----------------------------------------------------------------- |
| 227 |
// get-sales-report |
| 228 |
// ----------------------------------------------------------------- |
| 229 |
|
| 230 |
public static function getSalesReport($params = []) |
| 231 |
{ |
| 232 |
$currency = self::currency($params); |
| 233 |
$range = self::resolveRange($params); |
| 234 |
|
| 235 |
$current = self::salesMetrics($range['start'], $range['end'], $currency); |
| 236 |
|
| 237 |
$data = [ |
| 238 |
'range' => self::rangeBlock($range, $currency), |
| 239 |
'metrics' => self::salesMetricsOut($current, $currency), |
| 240 |
'definitions' => self::metricDefs(), |
| 241 |
]; |
| 242 |
|
| 243 |
$compare = !isset($params['compare']) || !empty($params['compare']); |
| 244 |
if ($compare && $range['prev_start']) { |
| 245 |
$prior = self::salesMetrics($range['prev_start'], $range['prev_end'], $currency); |
| 246 |
$data['comparison'] = [ |
| 247 |
'prior_metrics' => self::salesMetricsOut($prior, $currency), |
| 248 |
'change_percent' => [ |
| 249 |
'gross_revenue' => self::pct($current['gross'], $prior['gross']), |
| 250 |
'net_revenue' => self::pct($current['net'], $prior['net']), |
| 251 |
'order_count' => self::pct($current['orders'], $prior['orders']), |
| 252 |
], |
| 253 |
]; |
| 254 |
} |
| 255 |
|
| 256 |
$summary = sprintf( |
| 257 |
/* translators: 1: gross revenue, 2: order count, 3: average order value */ |
| 258 |
__('Revenue %1$s across %2$d paid orders, AOV %3$s.', 'fluent-cart'), |
| 259 |
MCPHelper::displayAmount($current['gross'], $currency), |
| 260 |
$current['orders'], |
| 261 |
MCPHelper::displayAmount($current['aov'], $currency) |
| 262 |
); |
| 263 |
|
| 264 |
return MCPHelper::envelope($summary, $data, ['currency' => $currency, 'date_basis' => 'created_at']); |
| 265 |
} |
| 266 |
|
| 267 |
private static function salesMetrics($start, $end, $currency) |
| 268 |
{ |
| 269 |
// One aggregate scan instead of eight (this is the headline report, and |
| 270 |
// it runs twice when compare=true). Same filtered set, same numbers. |
| 271 |
$row = Order::query() |
| 272 |
->whereIn('payment_status', self::PAID) |
| 273 |
->where('currency', $currency) |
| 274 |
->where('created_at', '>=', $start) |
| 275 |
->where('created_at', '<=', $end) |
| 276 |
->selectRaw( |
| 277 |
'COUNT(*) as orders, ' |
| 278 |
. 'COALESCE(SUM(total_amount), 0) as gross, ' |
| 279 |
. 'COALESCE(SUM(total_paid), 0) as paid, ' |
| 280 |
. 'COALESCE(SUM(total_refund), 0) as refund, ' |
| 281 |
. 'COALESCE(SUM(tax_total), 0) as tax, ' |
| 282 |
. 'COALESCE(SUM(shipping_total), 0) as ship, ' |
| 283 |
. 'COALESCE(SUM(fee_total), 0) as fees, ' |
| 284 |
. 'COUNT(DISTINCT customer_id) as uniq' |
| 285 |
) |
| 286 |
->first(); |
| 287 |
|
| 288 |
$orders = $row ? (int) $row->orders : 0; |
| 289 |
$gross = $row ? (int) $row->gross : 0; |
| 290 |
$paid = $row ? (int) $row->paid : 0; |
| 291 |
$refund = $row ? (int) $row->refund : 0; |
| 292 |
$tax = $row ? (int) $row->tax : 0; |
| 293 |
$ship = $row ? (int) $row->ship : 0; |
| 294 |
$fees = $row ? (int) $row->fees : 0; |
| 295 |
$uniq = $row ? (int) $row->uniq : 0; |
| 296 |
|
| 297 |
return [ |
| 298 |
'orders' => $orders, |
| 299 |
'gross' => $gross, |
| 300 |
'paid' => $paid, |
| 301 |
'refund' => $refund, |
| 302 |
'net' => $paid - $refund, |
| 303 |
'tax' => $tax, |
| 304 |
'shipping' => $ship, |
| 305 |
'fees' => $fees, |
| 306 |
'unique' => $uniq, |
| 307 |
'aov' => $orders > 0 ? (int) round($gross / $orders) : 0, |
| 308 |
]; |
| 309 |
} |
| 310 |
|
| 311 |
private static function salesMetricsOut($m, $currency) |
| 312 |
{ |
| 313 |
return [ |
| 314 |
'order_count' => $m['orders'], |
| 315 |
'unique_customers' => $m['unique'], |
| 316 |
'gross_revenue' => MCPHelper::money($m['gross'], $currency), |
| 317 |
'net_revenue' => MCPHelper::money($m['net'], $currency), |
| 318 |
'paid' => MCPHelper::money($m['paid'], $currency), |
| 319 |
'refunded' => MCPHelper::money($m['refund'], $currency), |
| 320 |
'tax' => MCPHelper::money($m['tax'], $currency), |
| 321 |
'shipping' => MCPHelper::money($m['shipping'], $currency), |
| 322 |
'fees' => MCPHelper::money($m['fees'], $currency), |
| 323 |
'aov' => MCPHelper::money($m['aov'], $currency), |
| 324 |
]; |
| 325 |
} |
| 326 |
|
| 327 |
private static function metricDefs() |
| 328 |
{ |
| 329 |
return [ |
| 330 |
'paid_orders' => 'Orders with payment_status in: ' . implode(', ', self::PAID), |
| 331 |
'gross_revenue' => 'Sum of order total_amount for paid orders.', |
| 332 |
'net_revenue' => 'Sum of total_paid minus total_refund.', |
| 333 |
'aov' => 'gross_revenue divided by paid order count.', |
| 334 |
'date_basis' => 'created_at, within the given range.', |
| 335 |
]; |
| 336 |
} |
| 337 |
|
| 338 |
// ----------------------------------------------------------------- |
| 339 |
// get-sales-trend |
| 340 |
// ----------------------------------------------------------------- |
| 341 |
|
| 342 |
public static function getSalesTrend($params = []) |
| 343 |
{ |
| 344 |
$currency = self::currency($params); |
| 345 |
$range = self::resolveRange($params); |
| 346 |
$interval = isset($params['interval']) && in_array($params['interval'], ['day', 'week', 'month'], true) ? $params['interval'] : 'day'; |
| 347 |
|
| 348 |
$format = $interval === 'month' ? '%Y-%m' : ($interval === 'week' ? '%x-W%v' : '%Y-%m-%d'); |
| 349 |
|
| 350 |
$rows = Order::query() |
| 351 |
->whereIn('payment_status', self::PAID) |
| 352 |
->where('currency', $currency) |
| 353 |
->where('created_at', '>=', $range['start']) |
| 354 |
->where('created_at', '<=', $range['end']) |
| 355 |
->selectRaw('DATE_FORMAT(created_at, ?) as bucket, COUNT(*) as order_count, SUM(total_amount) as gross', [$format]) |
| 356 |
->groupBy('bucket') |
| 357 |
->orderBy('bucket', 'ASC') |
| 358 |
->limit(self::MAX_BUCKETS) |
| 359 |
->get(); |
| 360 |
|
| 361 |
$trend = []; |
| 362 |
$sum = 0; |
| 363 |
foreach ($rows as $row) { |
| 364 |
$gross = (int) $row->gross; |
| 365 |
$sum += $gross; |
| 366 |
$trend[] = [ |
| 367 |
'bucket' => $row->bucket, |
| 368 |
'order_count' => (int) $row->order_count, |
| 369 |
'gross' => MCPHelper::moneyCompact($gross), |
| 370 |
]; |
| 371 |
} |
| 372 |
|
| 373 |
$summary = sprintf( |
| 374 |
/* translators: 1: number of buckets, 2: interval, 3: total revenue */ |
| 375 |
__('%1$d %2$s buckets, total revenue %3$s.', 'fluent-cart'), |
| 376 |
count($trend), |
| 377 |
$interval, |
| 378 |
MCPHelper::displayAmount($sum, $currency) |
| 379 |
); |
| 380 |
|
| 381 |
return MCPHelper::envelope( |
| 382 |
$summary, |
| 383 |
['interval' => $interval, 'range' => self::rangeBlock($range, $currency), 'trend' => $trend], |
| 384 |
['currency' => $currency, 'truncated' => count($rows) >= self::MAX_BUCKETS] |
| 385 |
); |
| 386 |
} |
| 387 |
|
| 388 |
// ----------------------------------------------------------------- |
| 389 |
// get-top-products |
| 390 |
// ----------------------------------------------------------------- |
| 391 |
|
| 392 |
public static function getTopProducts($params = []) |
| 393 |
{ |
| 394 |
$currency = self::currency($params); |
| 395 |
$range = self::resolveRange($params); |
| 396 |
$metric = isset($params['metric']) && $params['metric'] === 'units' ? 'units' : 'revenue'; |
| 397 |
$limit = isset($params['limit']) ? min(max((int) $params['limit'], 1), 50) : 10; |
| 398 |
$orderCol = $metric === 'units' ? 'units' : 'revenue'; |
| 399 |
|
| 400 |
$rows = OrderItem::query() |
| 401 |
->whereHas('order', function ($q) use ($range, $currency) { |
| 402 |
$q->whereIn('payment_status', self::PAID) |
| 403 |
->where('currency', $currency) |
| 404 |
->where('created_at', '>=', $range['start']) |
| 405 |
->where('created_at', '<=', $range['end']); |
| 406 |
}) |
| 407 |
->selectRaw('post_id, MAX(post_title) as title, SUM(quantity) as units, SUM(line_total - refund_total) as revenue, COUNT(DISTINCT order_id) as order_count') |
| 408 |
->groupBy('post_id') |
| 409 |
->orderBy($orderCol, 'DESC') |
| 410 |
->limit($limit) |
| 411 |
->get(); |
| 412 |
|
| 413 |
$products = []; |
| 414 |
foreach ($rows as $row) { |
| 415 |
$products[] = [ |
| 416 |
'product_id' => (int) $row->post_id, |
| 417 |
'title' => $row->title, |
| 418 |
'units_sold' => (int) $row->units, |
| 419 |
'revenue' => MCPHelper::moneyCompact((int) $row->revenue), |
| 420 |
'order_count' => (int) $row->order_count, |
| 421 |
]; |
| 422 |
} |
| 423 |
|
| 424 |
$summary = sprintf( |
| 425 |
/* translators: 1: number of products, 2: ranking metric */ |
| 426 |
__('Top %1$d products by %2$s.', 'fluent-cart'), |
| 427 |
count($products), |
| 428 |
$metric |
| 429 |
); |
| 430 |
|
| 431 |
return MCPHelper::envelope($summary, ['metric' => $metric, 'range' => self::rangeBlock($range, $currency), 'products' => $products], ['currency' => $currency]); |
| 432 |
} |
| 433 |
|
| 434 |
// ----------------------------------------------------------------- |
| 435 |
// get-refund-report |
| 436 |
// ----------------------------------------------------------------- |
| 437 |
|
| 438 |
public static function getRefundReport($params = []) |
| 439 |
{ |
| 440 |
$currency = self::currency($params); |
| 441 |
$range = self::resolveRange($params); |
| 442 |
|
| 443 |
$paidBase = Order::query() |
| 444 |
->whereIn('payment_status', self::PAID) |
| 445 |
->where('currency', $currency) |
| 446 |
->where('created_at', '>=', $range['start']) |
| 447 |
->where('created_at', '<=', $range['end']); |
| 448 |
|
| 449 |
$paidCount = (clone $paidBase)->count(); |
| 450 |
|
| 451 |
$refundedBase = (clone $paidBase)->where('total_refund', '>', 0); |
| 452 |
$refundedCount = (clone $refundedBase)->count(); |
| 453 |
$refundedAmount = (int) (clone $refundedBase)->sum('total_refund'); |
| 454 |
|
| 455 |
$rate = $paidCount > 0 ? round(($refundedCount / $paidCount) * 100, 2) : 0; |
| 456 |
$avg = $refundedCount > 0 ? (int) round($refundedAmount / $refundedCount) : 0; |
| 457 |
|
| 458 |
$summary = sprintf( |
| 459 |
/* translators: 1: refunded order count, 2: refund rate percent, 3: total refunded */ |
| 460 |
__('%1$d orders refunded, %2$s%% of paid, totaling %3$s.', 'fluent-cart'), |
| 461 |
$refundedCount, |
| 462 |
$rate, |
| 463 |
MCPHelper::displayAmount($refundedAmount, $currency) |
| 464 |
); |
| 465 |
|
| 466 |
return MCPHelper::envelope( |
| 467 |
$summary, |
| 468 |
[ |
| 469 |
'range' => self::rangeBlock($range, $currency), |
| 470 |
'paid_order_count' => $paidCount, |
| 471 |
'refunded_order_count' => $refundedCount, |
| 472 |
'refund_rate_percent' => $rate, |
| 473 |
'total_refunded' => MCPHelper::money($refundedAmount, $currency), |
| 474 |
'average_refund' => MCPHelper::money($avg, $currency), |
| 475 |
], |
| 476 |
['currency' => $currency] |
| 477 |
); |
| 478 |
} |
| 479 |
|
| 480 |
// ----------------------------------------------------------------- |
| 481 |
// query-sources (flexible UTM attribution) |
| 482 |
// ----------------------------------------------------------------- |
| 483 |
|
| 484 |
const UTM_DIMENSIONS = ['utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content', 'utm_id']; |
| 485 |
|
| 486 |
public static function querySources($params = []) |
| 487 |
{ |
| 488 |
$currency = self::currency($params); |
| 489 |
$range = self::resolveRange($params); |
| 490 |
$limit = isset($params['limit']) ? min(max((int) $params['limit'], 1), self::MAX_ROWS) : 50; |
| 491 |
$metrics = self::pickList($params, 'metrics', ['orders', 'gross_revenue', 'net_revenue', 'aov', 'unique_customers', 'refunded_amount'], ['orders', 'gross_revenue']); |
| 492 |
$dimensions = self::pickList($params, 'dimensions', self::UTM_DIMENSIONS, ['utm_source', 'utm_medium', 'utm_campaign']); |
| 493 |
|
| 494 |
// Build the aggregate directly (rather than via SourceReportService, |
| 495 |
// which hard-codes its grouping) so the agent picks the UTM dimensions. |
| 496 |
// Uses the raw query builder with the same aliases as the admin Source |
| 497 |
// report to avoid Order model global scopes. Paid + one currency to stay |
| 498 |
// consistent with the other reports. |
| 499 |
// Dedupe operations to one row per order before joining: fct_order_operations |
| 500 |
// has only an INDEX on order_id (not UNIQUE), so a raw leftJoin would fan out |
| 501 |
// and make every SUM(o.<money>) double-count any order with >1 ops row. |
| 502 |
// MAX() per UTM column is ONLY_FULL_GROUP_BY-safe and returns the single |
| 503 |
// row's value in the normal one-row-per-order case. |
| 504 |
$opSub = App::db()->table('fct_order_operations') |
| 505 |
->select('order_id') |
| 506 |
->selectRaw( |
| 507 |
'MAX(utm_source) as utm_source, MAX(utm_medium) as utm_medium, ' |
| 508 |
. 'MAX(utm_campaign) as utm_campaign, MAX(utm_term) as utm_term, ' |
| 509 |
. 'MAX(utm_content) as utm_content, MAX(utm_id) as utm_id' |
| 510 |
) |
| 511 |
->groupBy('order_id'); |
| 512 |
|
| 513 |
$query = App::db()->table('fct_orders as o') |
| 514 |
->leftJoinSub($opSub, 'oo', 'o.id', '=', 'oo.order_id') |
| 515 |
->whereIn('o.payment_status', self::PAID) |
| 516 |
->where('o.currency', $currency) |
| 517 |
->where('o.created_at', '>=', $range['start']) |
| 518 |
->where('o.created_at', '<=', $range['end']); |
| 519 |
|
| 520 |
// Optional drill-down filters on exact UTM values. |
| 521 |
foreach (['utm_source', 'utm_medium', 'utm_campaign'] as $f) { |
| 522 |
if (!empty($params[$f])) { |
| 523 |
$query->where('oo.' . $f, sanitize_text_field($params[$f])); |
| 524 |
} |
| 525 |
} |
| 526 |
|
| 527 |
$selects = []; |
| 528 |
$groupExpr = []; |
| 529 |
foreach ($dimensions as $dim) { |
| 530 |
// Coalesce NULL/'' into a single 'none' bucket; group by the |
| 531 |
// expression so the split values collapse together. |
| 532 |
$expr = "COALESCE(NULLIF(oo." . $dim . ", ''), 'none')"; |
| 533 |
$selects[] = $expr . ' as ' . $dim; |
| 534 |
$groupExpr[] = $expr; |
| 535 |
} |
| 536 |
|
| 537 |
// Definitions must match metricDefs() and the sales report so an agent's |
| 538 |
// "revenue by source" ties out with "revenue this month": |
| 539 |
// gross_revenue = SUM(total_amount), net_revenue = SUM(total_paid - total_refund). |
| 540 |
$metricSql = [ |
| 541 |
'orders' => 'COUNT(DISTINCT o.id) as orders', |
| 542 |
'gross_revenue' => 'SUM(o.total_amount) as gross_revenue', |
| 543 |
'net_revenue' => 'SUM(o.total_paid - o.total_refund) as net_revenue', |
| 544 |
'unique_customers' => 'COUNT(DISTINCT o.customer_id) as unique_customers', |
| 545 |
'refunded_amount' => 'SUM(o.total_refund) as refunded_amount', |
| 546 |
]; |
| 547 |
foreach ($metrics as $m) { |
| 548 |
if (isset($metricSql[$m])) { |
| 549 |
$selects[] = $metricSql[$m]; |
| 550 |
} |
| 551 |
} |
| 552 |
if (in_array('aov', $metrics, true)) { |
| 553 |
if (!in_array('gross_revenue', $metrics, true)) { |
| 554 |
$selects[] = $metricSql['gross_revenue']; |
| 555 |
} |
| 556 |
if (!in_array('orders', $metrics, true)) { |
| 557 |
$selects[] = $metricSql['orders']; |
| 558 |
} |
| 559 |
} |
| 560 |
|
| 561 |
$query->selectRaw(implode(', ', $selects)); |
| 562 |
if ($groupExpr) { |
| 563 |
$query->groupByRaw(implode(', ', $groupExpr)); |
| 564 |
} |
| 565 |
|
| 566 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'orders'; |
| 567 |
if ($firstMetric === 'aov') { |
| 568 |
$firstMetric = 'gross_revenue'; |
| 569 |
} |
| 570 |
if ($groupExpr && isset($metricSql[$firstMetric])) { |
| 571 |
$query->orderBy($firstMetric, 'DESC'); |
| 572 |
} |
| 573 |
// Fetch one extra row so we can tell "more exist beyond your limit" from |
| 574 |
// "you hit the 200 hard cap". $limit is already clamped to <= MAX_ROWS. |
| 575 |
$query->limit($limit + 1); |
| 576 |
|
| 577 |
$rows = $query->get(); |
| 578 |
$moneyMetrics = ['gross_revenue', 'net_revenue', 'refunded_amount']; |
| 579 |
$truncated = count($rows) > $limit; |
| 580 |
|
| 581 |
$out = []; |
| 582 |
foreach ($rows as $row) { |
| 583 |
if (count($out) >= $limit) { |
| 584 |
break; |
| 585 |
} |
| 586 |
$r = []; |
| 587 |
foreach ($dimensions as $dim) { |
| 588 |
$r[$dim] = $row->{$dim}; |
| 589 |
} |
| 590 |
foreach ($metrics as $m) { |
| 591 |
if ($m === 'aov') { |
| 592 |
$g = (int) $row->gross_revenue; |
| 593 |
$c = (int) $row->orders; |
| 594 |
$r['aov'] = MCPHelper::moneyCompact($c > 0 ? (int) round($g / $c) : 0); |
| 595 |
} elseif (in_array($m, $moneyMetrics, true)) { |
| 596 |
$r[$m] = MCPHelper::moneyCompact((int) $row->{$m}); |
| 597 |
} else { |
| 598 |
$r[$m] = (int) $row->{$m}; |
| 599 |
} |
| 600 |
} |
| 601 |
$out[] = $r; |
| 602 |
} |
| 603 |
|
| 604 |
$summary = sprintf( |
| 605 |
/* translators: 1: row count, 2: metric list, 3: dimension list */ |
| 606 |
__('%1$d rows — metrics [%2$s] grouped by [%3$s].', 'fluent-cart'), |
| 607 |
count($out), |
| 608 |
implode(', ', $metrics), |
| 609 |
$dimensions ? implode(', ', $dimensions) : __('total', 'fluent-cart') |
| 610 |
); |
| 611 |
|
| 612 |
return MCPHelper::envelope( |
| 613 |
$summary, |
| 614 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'range' => self::rangeBlock($range, $currency), 'rows' => $out], |
| 615 |
[ |
| 616 |
'currency' => $currency, |
| 617 |
'date_basis' => 'created_at', |
| 618 |
'returned' => count($out), |
| 619 |
'limit' => $limit, |
| 620 |
'max_rows' => self::MAX_ROWS, |
| 621 |
// true = more rows exist; raise `limit` (up to max_rows) to see them. |
| 622 |
'truncated' => $truncated, |
| 623 |
] |
| 624 |
); |
| 625 |
} |
| 626 |
|
| 627 |
// ----------------------------------------------------------------- |
| 628 |
// query-orders (flexible aggregate) |
| 629 |
// ----------------------------------------------------------------- |
| 630 |
|
| 631 |
public static function queryOrders($params = []) |
| 632 |
{ |
| 633 |
$currency = self::currency($params); |
| 634 |
$range = self::resolveRange($params); |
| 635 |
$metrics = self::pickList($params, 'metrics', ['order_count', 'gross_revenue', 'paid_revenue', 'refunded_amount', 'aov', 'unique_customers'], ['order_count', 'gross_revenue']); |
| 636 |
$dimensions = self::pickList($params, 'dimensions', ['day', 'week', 'month', 'status', 'payment_status'], []); |
| 637 |
|
| 638 |
$query = Order::query() |
| 639 |
->whereIn('payment_status', self::PAID) |
| 640 |
->where('currency', $currency) |
| 641 |
->where('created_at', '>=', $range['start']) |
| 642 |
->where('created_at', '<=', $range['end']); |
| 643 |
|
| 644 |
$selects = []; |
| 645 |
$groupCols = []; |
| 646 |
foreach ($dimensions as $dim) { |
| 647 |
$selects[] = self::dimensionExpr($dim) . ' as ' . $dim; |
| 648 |
$groupCols[] = $dim; |
| 649 |
} |
| 650 |
|
| 651 |
$metricSql = [ |
| 652 |
'order_count' => 'COUNT(*) as order_count', |
| 653 |
'gross_revenue' => 'SUM(total_amount) as gross_revenue', |
| 654 |
'paid_revenue' => 'SUM(total_paid) as paid_revenue', |
| 655 |
'refunded_amount' => 'SUM(total_refund) as refunded_amount', |
| 656 |
'unique_customers' => 'COUNT(DISTINCT customer_id) as unique_customers', |
| 657 |
]; |
| 658 |
foreach ($metrics as $m) { |
| 659 |
if (isset($metricSql[$m])) { |
| 660 |
$selects[] = $metricSql[$m]; |
| 661 |
} |
| 662 |
} |
| 663 |
if (in_array('aov', $metrics, true)) { |
| 664 |
if (!in_array('gross_revenue', $metrics, true)) { |
| 665 |
$selects[] = $metricSql['gross_revenue']; |
| 666 |
} |
| 667 |
if (!in_array('order_count', $metrics, true)) { |
| 668 |
$selects[] = $metricSql['order_count']; |
| 669 |
} |
| 670 |
} |
| 671 |
|
| 672 |
$query->selectRaw(implode(', ', $selects)); |
| 673 |
foreach ($groupCols as $g) { |
| 674 |
$query->groupBy($g); |
| 675 |
} |
| 676 |
|
| 677 |
$sortDesc = !isset($params['sort_desc']) || !empty($params['sort_desc']); |
| 678 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'order_count'; |
| 679 |
if ($firstMetric === 'aov') { |
| 680 |
$firstMetric = 'gross_revenue'; |
| 681 |
} |
| 682 |
|
| 683 |
// Find the first time dimension, if any. |
| 684 |
$timeDim = null; |
| 685 |
foreach ($dimensions as $dim) { |
| 686 |
if (in_array($dim, ['day', 'week', 'month'], true)) { |
| 687 |
$timeDim = $dim; |
| 688 |
break; |
| 689 |
} |
| 690 |
} |
| 691 |
|
| 692 |
if ($groupCols) { |
| 693 |
if ($timeDim !== null && !isset($params['sort_desc'])) { |
| 694 |
// A time series reads chronologically by default; ranking a |
| 695 |
// calendar by metric is rarely what's wanted. An explicit |
| 696 |
// sort_desc still overrides this. |
| 697 |
$query->orderBy($timeDim, 'ASC'); |
| 698 |
} else { |
| 699 |
$query->orderBy($firstMetric, $sortDesc ? 'DESC' : 'ASC'); |
| 700 |
} |
| 701 |
} |
| 702 |
$query->limit(self::MAX_ROWS); |
| 703 |
|
| 704 |
$rows = $query->get(); |
| 705 |
$moneyMetrics = ['gross_revenue', 'paid_revenue', 'refunded_amount']; |
| 706 |
|
| 707 |
$out = []; |
| 708 |
foreach ($rows as $row) { |
| 709 |
$r = []; |
| 710 |
foreach ($dimensions as $dim) { |
| 711 |
$r[$dim] = $row->{$dim}; |
| 712 |
} |
| 713 |
foreach ($metrics as $m) { |
| 714 |
if ($m === 'aov') { |
| 715 |
$g = (int) $row->gross_revenue; |
| 716 |
$c = (int) $row->order_count; |
| 717 |
$r['aov'] = MCPHelper::moneyCompact($c > 0 ? (int) round($g / $c) : 0); |
| 718 |
} elseif (in_array($m, $moneyMetrics, true)) { |
| 719 |
$r[$m] = MCPHelper::moneyCompact((int) $row->{$m}); |
| 720 |
} else { |
| 721 |
$r[$m] = (int) $row->{$m}; |
| 722 |
} |
| 723 |
} |
| 724 |
$out[] = $r; |
| 725 |
} |
| 726 |
|
| 727 |
$summary = sprintf( |
| 728 |
/* translators: 1: row count, 2: metric list, 3: dimension list */ |
| 729 |
__('%1$d rows — metrics [%2$s] grouped by [%3$s].', 'fluent-cart'), |
| 730 |
count($out), |
| 731 |
implode(', ', $metrics), |
| 732 |
$dimensions ? implode(', ', $dimensions) : __('total', 'fluent-cart') |
| 733 |
); |
| 734 |
|
| 735 |
return MCPHelper::envelope( |
| 736 |
$summary, |
| 737 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'range' => self::rangeBlock($range, $currency), 'rows' => $out], |
| 738 |
['currency' => $currency, 'truncated' => count($rows) >= self::MAX_ROWS] |
| 739 |
); |
| 740 |
} |
| 741 |
|
| 742 |
// ----------------------------------------------------------------- |
| 743 |
// query-products / query-customers (flexible aggregates) |
| 744 |
// ----------------------------------------------------------------- |
| 745 |
|
| 746 |
public static function queryProducts($params = []) |
| 747 |
{ |
| 748 |
$currency = self::currency($params); |
| 749 |
$range = self::resolveRange($params); |
| 750 |
$metrics = self::pickList($params, 'metrics', ['units_sold', 'line_revenue', 'net_revenue', 'order_count', 'avg_unit_price', 'refund_amount'], ['units_sold', 'line_revenue']); |
| 751 |
$dimensions = self::pickList($params, 'dimensions', ['product', 'variation'], ['product']); |
| 752 |
|
| 753 |
$query = OrderItem::query()->whereHas('order', function ($q) use ($range, $currency) { |
| 754 |
$q->whereIn('payment_status', self::PAID) |
| 755 |
->where('currency', $currency) |
| 756 |
->where('created_at', '>=', $range['start']) |
| 757 |
->where('created_at', '<=', $range['end']); |
| 758 |
}); |
| 759 |
|
| 760 |
$selects = []; |
| 761 |
$groupCols = []; |
| 762 |
if (in_array('product', $dimensions, true)) { |
| 763 |
$selects[] = 'post_id'; |
| 764 |
$selects[] = 'MAX(post_title) as product_title'; |
| 765 |
$groupCols[] = 'post_id'; |
| 766 |
} |
| 767 |
if (in_array('variation', $dimensions, true)) { |
| 768 |
$selects[] = 'object_id'; |
| 769 |
$groupCols[] = 'object_id'; |
| 770 |
} |
| 771 |
|
| 772 |
$metricSql = [ |
| 773 |
'units_sold' => 'SUM(quantity) as units_sold', |
| 774 |
'line_revenue' => 'SUM(line_total) as line_revenue', |
| 775 |
'net_revenue' => 'SUM(line_total - refund_total) as net_revenue', |
| 776 |
'order_count' => 'COUNT(DISTINCT order_id) as order_count', |
| 777 |
'refund_amount' => 'SUM(refund_total) as refund_amount', |
| 778 |
]; |
| 779 |
foreach ($metrics as $m) { |
| 780 |
if (isset($metricSql[$m])) { |
| 781 |
$selects[] = $metricSql[$m]; |
| 782 |
} |
| 783 |
} |
| 784 |
if (in_array('avg_unit_price', $metrics, true)) { |
| 785 |
if (!in_array('line_revenue', $metrics, true)) { |
| 786 |
$selects[] = $metricSql['line_revenue']; |
| 787 |
} |
| 788 |
if (!in_array('units_sold', $metrics, true)) { |
| 789 |
$selects[] = $metricSql['units_sold']; |
| 790 |
} |
| 791 |
} |
| 792 |
|
| 793 |
$query->selectRaw(implode(', ', $selects)); |
| 794 |
foreach ($groupCols as $g) { |
| 795 |
$query->groupBy($g); |
| 796 |
} |
| 797 |
|
| 798 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'line_revenue'; |
| 799 |
if ($firstMetric === 'avg_unit_price') { |
| 800 |
$firstMetric = 'line_revenue'; |
| 801 |
} |
| 802 |
if ($groupCols && isset($metricSql[$firstMetric])) { |
| 803 |
$query->orderBy($firstMetric, 'DESC'); |
| 804 |
} |
| 805 |
$query->limit(self::MAX_ROWS); |
| 806 |
|
| 807 |
$rows = $query->get(); |
| 808 |
$moneyMetrics = ['line_revenue', 'net_revenue', 'refund_amount', 'avg_unit_price']; |
| 809 |
|
| 810 |
$out = []; |
| 811 |
foreach ($rows as $row) { |
| 812 |
$r = []; |
| 813 |
if (in_array('product', $dimensions, true)) { |
| 814 |
$r['product_id'] = (int) $row->post_id; |
| 815 |
$r['product_title'] = $row->product_title; |
| 816 |
} |
| 817 |
if (in_array('variation', $dimensions, true)) { |
| 818 |
$r['variation_id'] = (int) $row->object_id; |
| 819 |
} |
| 820 |
foreach ($metrics as $m) { |
| 821 |
if ($m === 'avg_unit_price') { |
| 822 |
$u = (int) $row->units_sold; |
| 823 |
$rev = (int) $row->line_revenue; |
| 824 |
$r['avg_unit_price'] = MCPHelper::moneyCompact($u > 0 ? (int) round($rev / $u) : 0); |
| 825 |
} elseif (in_array($m, $moneyMetrics, true)) { |
| 826 |
$r[$m] = MCPHelper::moneyCompact((int) $row->{$m}); |
| 827 |
} else { |
| 828 |
$r[$m] = (int) $row->{$m}; |
| 829 |
} |
| 830 |
} |
| 831 |
$out[] = $r; |
| 832 |
} |
| 833 |
|
| 834 |
return MCPHelper::envelope( |
| 835 |
sprintf( |
| 836 |
/* translators: 1: row count, 2: metric list */ |
| 837 |
__('%1$d rows — product metrics [%2$s].', 'fluent-cart'), |
| 838 |
count($out), |
| 839 |
implode(', ', $metrics) |
| 840 |
), |
| 841 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'range' => self::rangeBlock($range, $currency), 'rows' => $out], |
| 842 |
['currency' => $currency, 'truncated' => count($rows) >= self::MAX_ROWS] |
| 843 |
); |
| 844 |
} |
| 845 |
|
| 846 |
public static function queryCustomers($params = []) |
| 847 |
{ |
| 848 |
$metrics = self::pickList($params, 'metrics', ['customer_count', 'total_ltv', 'avg_ltv', 'avg_purchase_count', 'repeat_customers'], ['customer_count', 'total_ltv']); |
| 849 |
$dimensions = self::pickList($params, 'dimensions', ['country', 'state', 'status', 'first_purchase_month', 'last_purchase_month'], []); |
| 850 |
|
| 851 |
$query = Customer::query(); |
| 852 |
if (!empty($params['country'])) { |
| 853 |
$query->where('country', sanitize_text_field($params['country'])); |
| 854 |
} |
| 855 |
if (!empty($params['status'])) { |
| 856 |
$query->where('status', sanitize_text_field($params['status'])); |
| 857 |
} |
| 858 |
if (isset($params['min_ltv'])) { |
| 859 |
$query->where('ltv', '>=', Helper::toCent($params['min_ltv'])); |
| 860 |
} |
| 861 |
if (isset($params['min_purchase_count'])) { |
| 862 |
$query->where('purchase_count', '>=', (int) $params['min_purchase_count']); |
| 863 |
} |
| 864 |
|
| 865 |
$selects = []; |
| 866 |
$groupCols = []; |
| 867 |
foreach ($dimensions as $dim) { |
| 868 |
if ($dim === 'country' || $dim === 'state') { |
| 869 |
// Coalesce NULL and '' into a single 'unknown' bucket. Group by the |
| 870 |
// expression (not the alias, which would resolve to the raw column |
| 871 |
// and keep null/'' split). |
| 872 |
$expr = "COALESCE(NULLIF($dim, ''), 'unknown')"; |
| 873 |
$selects[] = "$expr as $dim"; |
| 874 |
$groupCols[] = $expr; |
| 875 |
} elseif ($dim === 'status') { |
| 876 |
$selects[] = $dim; |
| 877 |
$groupCols[] = $dim; |
| 878 |
} elseif ($dim === 'first_purchase_month') { |
| 879 |
$selects[] = "DATE_FORMAT(first_purchase_date, '%Y-%m') as first_purchase_month"; |
| 880 |
$groupCols[] = "DATE_FORMAT(first_purchase_date, '%Y-%m')"; |
| 881 |
} elseif ($dim === 'last_purchase_month') { |
| 882 |
$selects[] = "DATE_FORMAT(last_purchase_date, '%Y-%m') as last_purchase_month"; |
| 883 |
$groupCols[] = "DATE_FORMAT(last_purchase_date, '%Y-%m')"; |
| 884 |
} |
| 885 |
} |
| 886 |
|
| 887 |
$metricSql = [ |
| 888 |
'customer_count' => 'COUNT(*) as customer_count', |
| 889 |
'total_ltv' => 'SUM(ltv) as total_ltv', |
| 890 |
'avg_ltv' => 'AVG(ltv) as avg_ltv', |
| 891 |
'avg_purchase_count' => 'AVG(purchase_count) as avg_purchase_count', |
| 892 |
'repeat_customers' => 'SUM(CASE WHEN purchase_count > 1 THEN 1 ELSE 0 END) as repeat_customers', |
| 893 |
]; |
| 894 |
foreach ($metrics as $m) { |
| 895 |
if (isset($metricSql[$m])) { |
| 896 |
$selects[] = $metricSql[$m]; |
| 897 |
} |
| 898 |
} |
| 899 |
|
| 900 |
$query->selectRaw(implode(', ', $selects)); |
| 901 |
if ($groupCols) { |
| 902 |
$query->groupByRaw(implode(', ', $groupCols)); |
| 903 |
} |
| 904 |
|
| 905 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'customer_count'; |
| 906 |
if ($groupCols && isset($metricSql[$firstMetric])) { |
| 907 |
$query->orderBy($firstMetric, 'DESC'); |
| 908 |
} |
| 909 |
$query->limit(self::MAX_ROWS); |
| 910 |
|
| 911 |
$rows = $query->get(); |
| 912 |
$moneyMetrics = ['total_ltv', 'avg_ltv']; |
| 913 |
|
| 914 |
$out = []; |
| 915 |
foreach ($rows as $row) { |
| 916 |
$r = []; |
| 917 |
foreach ($dimensions as $dim) { |
| 918 |
$r[$dim] = $row->{$dim}; |
| 919 |
} |
| 920 |
foreach ($metrics as $m) { |
| 921 |
if (in_array($m, $moneyMetrics, true)) { |
| 922 |
$r[$m] = MCPHelper::moneyCompact((int) round((float) $row->{$m})); |
| 923 |
} elseif ($m === 'avg_purchase_count') { |
| 924 |
$r[$m] = round((float) $row->{$m}, 2); |
| 925 |
} else { |
| 926 |
$r[$m] = (int) $row->{$m}; |
| 927 |
} |
| 928 |
} |
| 929 |
$out[] = $r; |
| 930 |
} |
| 931 |
|
| 932 |
return MCPHelper::envelope( |
| 933 |
sprintf( |
| 934 |
/* translators: 1: row count, 2: metric list */ |
| 935 |
__('%1$d rows — customer metrics [%2$s].', 'fluent-cart'), |
| 936 |
count($out), |
| 937 |
implode(', ', $metrics) |
| 938 |
), |
| 939 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'rows' => $out], |
| 940 |
['currency' => MCPHelper::currencyCode(), 'note' => 'LTV is in store currency; customers are not currency-scoped.'] |
| 941 |
); |
| 942 |
} |
| 943 |
|
| 944 |
private static function dimensionExpr($dim) |
| 945 |
{ |
| 946 |
if ($dim === 'day') { |
| 947 |
return "DATE_FORMAT(created_at, '%Y-%m-%d')"; |
| 948 |
} |
| 949 |
if ($dim === 'week') { |
| 950 |
return "DATE_FORMAT(created_at, '%x-W%v')"; |
| 951 |
} |
| 952 |
if ($dim === 'month') { |
| 953 |
return "DATE_FORMAT(created_at, '%Y-%m')"; |
| 954 |
} |
| 955 |
return $dim; |
| 956 |
} |
| 957 |
|
| 958 |
// ----------------------------------------------------------------- |
| 959 |
// shared helpers |
| 960 |
// ----------------------------------------------------------------- |
| 961 |
|
| 962 |
private static function currency($params) |
| 963 |
{ |
| 964 |
if (!empty($params['currency'])) { |
| 965 |
return strtoupper(sanitize_text_field($params['currency'])); |
| 966 |
} |
| 967 |
return MCPHelper::currencyCode(); |
| 968 |
} |
| 969 |
|
| 970 |
/** |
| 971 |
* Resolve range/start/end into a UTC window plus the prior equal-length |
| 972 |
* window. Relative ranges are computed in store timezone, expressed in UTC. |
| 973 |
*/ |
| 974 |
private static function resolveRange($params) |
| 975 |
{ |
| 976 |
// Resolve windows in UTC to match FluentCart's own admin reports, which |
| 977 |
// bucket on the GMT-stored created_at (DATE_FORMAT(created_at, ...)) with |
| 978 |
// no timezone conversion. Using store-local boundaries here would make a |
| 979 |
// local day straddle two UTC dates and emit an extra trailing bucket. |
| 980 |
$tz = new \DateTimeZone('UTC'); |
| 981 |
|
| 982 |
if (!empty($params['start_date']) || !empty($params['end_date'])) { |
| 983 |
$start = self::dayStart(!empty($params['start_date']) ? $params['start_date'] : '-30 days', $tz); |
| 984 |
$end = self::dayEnd(!empty($params['end_date']) ? $params['end_date'] : 'now', $tz); |
| 985 |
return self::withPrior($start, $end, !empty($params['start_date']) ? 'custom' : 'last_30_days'); |
| 986 |
} |
| 987 |
|
| 988 |
$range = isset($params['range']) && in_array($params['range'], self::RANGES, true) ? $params['range'] : 'last_30_days'; |
| 989 |
|
| 990 |
$now = new \DateTime('now', $tz); |
| 991 |
$startDt = clone $now; |
| 992 |
$endDt = clone $now; |
| 993 |
// Set for calendar-bounded ranges to force a calendar-aligned prior period. |
| 994 |
$prevStartDt = null; |
| 995 |
$prevEndDt = null; |
| 996 |
|
| 997 |
if ($range === 'yesterday') { |
| 998 |
$startDt->modify('-1 day'); |
| 999 |
$endDt->modify('-1 day'); |
| 1000 |
} elseif ($range === 'last_7_days') { |
| 1001 |
$startDt->modify('-6 days'); |
| 1002 |
} elseif ($range === 'last_30_days') { |
| 1003 |
$startDt->modify('-29 days'); |
| 1004 |
} elseif ($range === 'this_month' || $range === 'mtd') { |
| 1005 |
$startDt = new \DateTime($now->format('Y-m-01'), $tz); |
| 1006 |
} elseif ($range === 'last_month') { |
| 1007 |
$startDt = new \DateTime($now->format('Y-m-01'), $tz); |
| 1008 |
$startDt->modify('-1 month'); |
| 1009 |
$endDt = (clone $startDt)->modify('last day of this month'); |
| 1010 |
// Prior = the full calendar month before last month. |
| 1011 |
$prevStartDt = (clone $startDt)->modify('-1 month'); |
| 1012 |
$prevEndDt = (clone $prevStartDt)->modify('last day of this month'); |
| 1013 |
} elseif ($range === 'qtd') { |
| 1014 |
$startDt = self::quarterStart($now, $tz); |
| 1015 |
} elseif ($range === 'last_quarter') { |
| 1016 |
$qs = self::quarterStart($now, $tz); |
| 1017 |
$startDt = (clone $qs)->modify('-3 months'); |
| 1018 |
$endDt = (clone $qs)->modify('-1 day'); |
| 1019 |
// Prior = the full calendar quarter before last quarter. |
| 1020 |
$prevStartDt = (clone $startDt)->modify('-3 months'); |
| 1021 |
$prevEndDt = (clone $startDt)->modify('-1 day'); |
| 1022 |
} elseif ($range === 'ytd') { |
| 1023 |
$startDt = new \DateTime($now->format('Y-01-01'), $tz); |
| 1024 |
} elseif ($range === 'last_year') { |
| 1025 |
$year = (int) $now->format('Y') - 1; |
| 1026 |
$startDt = new \DateTime($year . '-01-01', $tz); |
| 1027 |
$endDt = new \DateTime($year . '-12-31', $tz); |
| 1028 |
// Prior = the full calendar year before last year. |
| 1029 |
$prevStartDt = new \DateTime(($year - 1) . '-01-01', $tz); |
| 1030 |
$prevEndDt = new \DateTime(($year - 1) . '-12-31', $tz); |
| 1031 |
} |
| 1032 |
|
| 1033 |
$start = self::dayStart($startDt->format('Y-m-d'), $tz); |
| 1034 |
$end = self::dayEnd($endDt->format('Y-m-d'), $tz); |
| 1035 |
|
| 1036 |
if ($prevStartDt !== null && $prevEndDt !== null) { |
| 1037 |
return self::withPrior( |
| 1038 |
$start, |
| 1039 |
$end, |
| 1040 |
$range, |
| 1041 |
self::dayStart($prevStartDt->format('Y-m-d'), $tz), |
| 1042 |
self::dayEnd($prevEndDt->format('Y-m-d'), $tz) |
| 1043 |
); |
| 1044 |
} |
| 1045 |
|
| 1046 |
return self::withPrior($start, $end, $range); |
| 1047 |
} |
| 1048 |
|
| 1049 |
private static function quarterStart($now, $tz) |
| 1050 |
{ |
| 1051 |
$month = (int) $now->format('n'); |
| 1052 |
$qStartMonth = (int) (floor(($month - 1) / 3) * 3 + 1); |
| 1053 |
return new \DateTime($now->format('Y') . '-' . str_pad($qStartMonth, 2, '0', STR_PAD_LEFT) . '-01', $tz); |
| 1054 |
} |
| 1055 |
|
| 1056 |
private static function withPrior($startUtc, $endUtc, $label, $prevStartUtc = null, $prevEndUtc = null) |
| 1057 |
{ |
| 1058 |
// Calendar-bounded ranges (last_month/last_quarter/last_year) pass an |
| 1059 |
// explicit prior *calendar* period so a 31-day month isn't compared to a |
| 1060 |
// 28-day second-count window. Other ranges fall back to an equal-length |
| 1061 |
// block ending 1s before start (exact for fixed-length, rolling, custom). |
| 1062 |
if ($prevStartUtc !== null && $prevEndUtc !== null) { |
| 1063 |
return [ |
| 1064 |
'start' => $startUtc, |
| 1065 |
'end' => $endUtc, |
| 1066 |
'prev_start' => $prevStartUtc, |
| 1067 |
'prev_end' => $prevEndUtc, |
| 1068 |
'label' => $label, |
| 1069 |
]; |
| 1070 |
} |
| 1071 |
|
| 1072 |
$s = new \DateTime($startUtc, new \DateTimeZone('UTC')); |
| 1073 |
$e = new \DateTime($endUtc, new \DateTimeZone('UTC')); |
| 1074 |
$lengthSec = $e->getTimestamp() - $s->getTimestamp(); |
| 1075 |
|
| 1076 |
$prevEnd = (clone $s)->modify('-1 second'); |
| 1077 |
$prevStart = (clone $prevEnd)->modify('-' . ($lengthSec + 1) . ' seconds'); |
| 1078 |
|
| 1079 |
return [ |
| 1080 |
'start' => $startUtc, |
| 1081 |
'end' => $endUtc, |
| 1082 |
'prev_start' => $prevStart->format('Y-m-d H:i:s'), |
| 1083 |
'prev_end' => $prevEnd->format('Y-m-d H:i:s'), |
| 1084 |
'label' => $label, |
| 1085 |
]; |
| 1086 |
} |
| 1087 |
|
| 1088 |
private static function dayStart($value, $tz) |
| 1089 |
{ |
| 1090 |
try { |
| 1091 |
$dt = new \DateTime((string) $value, $tz); |
| 1092 |
} catch (\Exception $e) { |
| 1093 |
$dt = new \DateTime('now', $tz); |
| 1094 |
} |
| 1095 |
$dt->setTime(0, 0, 0); |
| 1096 |
$dt->setTimezone(new \DateTimeZone('UTC')); |
| 1097 |
return $dt->format('Y-m-d H:i:s'); |
| 1098 |
} |
| 1099 |
|
| 1100 |
private static function dayEnd($value, $tz) |
| 1101 |
{ |
| 1102 |
try { |
| 1103 |
$dt = new \DateTime((string) $value, $tz); |
| 1104 |
} catch (\Exception $e) { |
| 1105 |
$dt = new \DateTime('now', $tz); |
| 1106 |
} |
| 1107 |
$dt->setTime(23, 59, 59); |
| 1108 |
$dt->setTimezone(new \DateTimeZone('UTC')); |
| 1109 |
return $dt->format('Y-m-d H:i:s'); |
| 1110 |
} |
| 1111 |
|
| 1112 |
private static function rangeBlock($range, $currency) |
| 1113 |
{ |
| 1114 |
return [ |
| 1115 |
'start' => MCPHelper::toIso8601($range['start']), |
| 1116 |
'end' => MCPHelper::toIso8601($range['end']), |
| 1117 |
'label' => $range['label'], |
| 1118 |
'currency' => $currency, |
| 1119 |
]; |
| 1120 |
} |
| 1121 |
|
| 1122 |
private static function pickList($params, $key, array $allowed, array $default) |
| 1123 |
{ |
| 1124 |
if (empty($params[$key]) || !is_array($params[$key])) { |
| 1125 |
return $default; |
| 1126 |
} |
| 1127 |
$out = []; |
| 1128 |
foreach ($params[$key] as $v) { |
| 1129 |
if (in_array($v, $allowed, true) && !in_array($v, $out, true)) { |
| 1130 |
$out[] = $v; |
| 1131 |
} |
| 1132 |
} |
| 1133 |
return $out ? $out : $default; |
| 1134 |
} |
| 1135 |
|
| 1136 |
private static function pct($current, $prior) |
| 1137 |
{ |
| 1138 |
if ($prior == 0) { |
| 1139 |
return $current == 0 ? 0 : null; |
| 1140 |
} |
| 1141 |
return round((($current - $prior) / abs($prior)) * 100, 2); |
| 1142 |
} |
| 1143 |
} |
| 1144 |
|
