| 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\Models\Subscription; |
| 11 |
use FluentCart\App\Modules\MCP\Support\MCPHelper; |
| 12 |
use FluentCart\App\Modules\MCP\Support\PermissionGate; |
| 13 |
|
| 14 |
/** |
| 15 |
* Reports & analytics — the headline research surface. |
| 16 |
* |
| 17 |
* Design rules baked in: |
| 18 |
* - Every report is CURRENCY-SCOPED: it filters to one currency (the store |
| 19 |
* default unless `currency` is passed) so totals are never silently summed |
| 20 |
* across currencies. The chosen currency is echoed in meta.currency. |
| 21 |
* - "Revenue" definitions are explicit and consistent across tools (see |
| 22 |
* metricDefs): gross = sum(total_amount) of paid orders; net = paid − |
| 23 |
* refunded; paid orders = payment_status in the paid set. |
| 24 |
* - Date basis is created_at (echoed as meta.date_basis) for determinism. |
| 25 |
* - Server-side aggregation only — no raw row dumps. query-orders caps at 200 |
| 26 |
* grouped rows; trend caps its bucket count. |
| 27 |
* - Each report returns an NL summary the agent can quote verbatim. |
| 28 |
* |
| 29 |
* Parameter design: a shared `range` enum (today … last_year) resolves to a |
| 30 |
* UTC window server-side, with explicit start_date/end_date as an override — |
| 31 |
* the agent never has to compute "last month" itself. |
| 32 |
*/ |
| 33 |
class ReportTools |
| 34 |
{ |
| 35 |
// Payment statuses for orders that CAPTURED PAYMENT at some point — including |
| 36 |
// one later fully refunded (payment_status 'refunded', order status 'canceled'). |
| 37 |
// A fully refunded order captured payment then returned it, so it belongs in the |
| 38 |
// refund metrics (it is the whole point of a refund report) and in the paid |
| 39 |
// denominator of the refund rate, and it nets to zero in net_revenue |
| 40 |
// (total_paid - total_refund). Gating refund aggregation on the narrower paid set |
| 41 |
// silently undercounted every fully-refunded order. Including 'refunded' here |
| 42 |
// matches FluentCart's own admin reports, which compute refunds from |
| 43 |
// total_refund > 0 with no current-status gate (see RevenueReportService / |
| 44 |
// RefundReportService::applyFilters — the default has no payment_status filter). |
| 45 |
const PAID = ['paid', 'partially_paid', 'partially_refunded', 'refunded']; |
| 46 |
|
| 47 |
// all_time is a documented alias of since_launch (see resolveRange) so the |
| 48 |
// range vocabulary matches get-product-financials, which uses all_time. |
| 49 |
const RANGES = ['today', 'yesterday', 'last_7_days', 'last_30_days', 'this_month', 'last_month', 'mtd', 'qtd', 'ytd', 'last_quarter', 'last_year', 'since_launch', 'all_time']; |
| 50 |
|
| 51 |
const MAX_BUCKETS = 180; |
| 52 |
|
| 53 |
const MAX_ROWS = 200; |
| 54 |
|
| 55 |
public static function definitions() |
| 56 |
{ |
| 57 |
$rangeProp = ['type' => 'string', 'enum' => self::RANGES, 'description' => 'Relative window, resolved in UTC to match the store reports. today = midnight UTC to now; since_launch (alias: all_time) = the store\'s first paid order to now. Or pass start_date + end_date (dates), date_from + date_to (ISO 8601 datetimes), or since (delta).']; |
| 58 |
|
| 59 |
// Shared custom-window params. All UTC — reports stay reconcilable with the |
| 60 |
// admin dashboard, which buckets on the GMT-stored created_at. |
| 61 |
$dateFrom = ['type' => 'string', 'description' => 'ISO 8601 datetime or YYYY-MM-DD, UTC. Time-precise custom window start; overrides range. A time of day is honored (e.g. launch hour).']; |
| 62 |
$dateTo = ['type' => 'string', 'description' => 'ISO 8601 datetime or YYYY-MM-DD, UTC. Time-precise custom window end; overrides range.']; |
| 63 |
$since = ['type' => 'string', 'description' => 'ISO 8601 datetime, UTC. Delta mode: only records after this instant, up to now — answers "what changed since my last check". Overrides range/date_from/date_to.']; |
| 64 |
|
| 65 |
// Live/test order scoping. Reports historically counted BOTH, so 'all' is |
| 66 |
// the non-breaking default; pass 'live' to exclude test-mode orders from |
| 67 |
// revenue. The effective mode is always echoed as meta.mode so a number |
| 68 |
// is never silently polluted by test orders. |
| 69 |
$modeProp = ['type' => 'string', 'enum' => ['live', 'test', 'all'], 'default' => 'all', 'description' => 'Order mode. all (default) counts both live and test orders; pass live to exclude test-mode orders. Echoed as meta.mode.']; |
| 70 |
|
| 71 |
// Pagination for the flexible query-* aggregates: when a grouping produces |
| 72 |
// more than per_page groups the response sets meta.page.has_more; raise |
| 73 |
// page to walk the rest instead of only being able to narrow the window. |
| 74 |
$pageProp = ['type' => 'integer', 'default' => 1, 'description' => '1-based page over the grouped rows. Use with meta.page.has_more to page past the per_page cap.']; |
| 75 |
$perPageProp = ['type' => 'integer', 'default' => 200, 'description' => 'Grouped rows per page. Max 200.']; |
| 76 |
|
| 77 |
// The query-* aggregates share one response shape: metrics/dimensions echo |
| 78 |
// + a rows array whose keys are dynamic (one per requested dimension and |
| 79 |
// metric). Declared for the model up front so it needn't probe a call to |
| 80 |
// learn the envelope. Money in rows is a compact decimal in meta.currency. |
| 81 |
$queryOutputSchema = MCPHelper::envelopeSchema([ |
| 82 |
'type' => 'object', |
| 83 |
'properties' => [ |
| 84 |
'metrics' => ['type' => 'array', 'items' => ['type' => 'string'], 'description' => 'The metrics that were computed.'], |
| 85 |
'dimensions' => ['type' => 'array', 'items' => ['type' => 'string'], 'description' => 'The group-by dimensions.'], |
| 86 |
'range' => ['type' => 'object', 'description' => 'Resolved UTC window (absent on query-customers, which is not date-scoped).'], |
| 87 |
'rows' => [ |
| 88 |
'type' => 'array', |
| 89 |
'description' => 'One object per group. Keys are the requested dimensions plus one key per metric; money metrics are compact decimals in meta.currency, counts are integers.', |
| 90 |
'items' => ['type' => 'object'], |
| 91 |
], |
| 92 |
], |
| 93 |
], ['date_basis' => ['type' => 'string'], 'mode' => ['type' => 'string'], 'page' => ['type' => 'object'], 'truncated' => ['type' => 'boolean']]); |
| 94 |
|
| 95 |
$subStatuses = ContextTools::ENUMS['subscription_statuses']; |
| 96 |
|
| 97 |
$defs = [ |
| 98 |
'fluent-cart/get-sales-report' => [ |
| 99 |
'label' => __('Get Sales Report', 'fluent-cart'), |
| 100 |
'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. The refunded metric covers all refunds in the window including fully refunded orders (which net to zero in net_revenue). Scoped to one currency, the store default unless a currency is given.', 'fluent-cart'), |
| 101 |
'input_schema' => [ |
| 102 |
'type' => 'object', |
| 103 |
'properties' => [ |
| 104 |
'range' => $rangeProp, |
| 105 |
'start_date' => ['type' => 'string', 'description' => 'YYYY-MM-DD, UTC. Overrides range.'], |
| 106 |
'end_date' => ['type' => 'string', 'description' => 'YYYY-MM-DD, UTC. Overrides range.'], |
| 107 |
'date_from' => $dateFrom, |
| 108 |
'date_to' => $dateTo, |
| 109 |
'since' => $since, |
| 110 |
'currency' => ['type' => 'string', 'description' => 'ISO currency. Defaults to the store currency.'], |
| 111 |
'compare' => ['type' => 'boolean', 'default' => true, 'description' => 'Include prior-period comparison.'], |
| 112 |
], |
| 113 |
], |
| 114 |
'output_schema' => MCPHelper::envelopeSchema([ |
| 115 |
'type' => 'object', |
| 116 |
'properties' => [ |
| 117 |
'range' => ['type' => 'object', 'description' => 'Resolved UTC window: start, end, label, currency.'], |
| 118 |
'metrics' => [ |
| 119 |
'type' => 'object', |
| 120 |
'description' => 'order_count and unique_customers are integers; every other key (gross_revenue, net_revenue, paid, refunded, tax, shipping, fees, aov) is a money object.', |
| 121 |
'properties' => [ |
| 122 |
'order_count' => ['type' => 'integer'], |
| 123 |
'unique_customers' => ['type' => 'integer'], |
| 124 |
], |
| 125 |
// Declare the money shape ONCE for all the money metrics |
| 126 |
// rather than inlining it per key (10x is real tokens). |
| 127 |
'additionalProperties' => MCPHelper::moneyDef(), |
| 128 |
], |
| 129 |
'definitions' => ['type' => 'object', 'description' => 'Human-readable metric definitions.'], |
| 130 |
'comparison' => ['type' => 'object', 'description' => 'Prior-period metrics and percent change (present when compare=true).'], |
| 131 |
], |
| 132 |
], ['date_basis' => ['type' => 'string'], 'mode' => ['type' => 'string']]), |
| 133 |
'execute_callback' => [self::class, 'getSalesReport'], |
| 134 |
'permission_callback' => function () { |
| 135 |
return PermissionGate::can('reports/view'); |
| 136 |
}, |
| 137 |
'annotations' => ['readonly' => true], |
| 138 |
], |
| 139 |
|
| 140 |
'fluent-cart/get-sales-trend' => [ |
| 141 |
'label' => __('Get Sales Trend', 'fluent-cart'), |
| 142 |
'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'), |
| 143 |
'input_schema' => [ |
| 144 |
'type' => 'object', |
| 145 |
'properties' => [ |
| 146 |
'range' => $rangeProp, |
| 147 |
'start_date' => ['type' => 'string', 'description' => 'YYYY-MM-DD, UTC.'], |
| 148 |
'end_date' => ['type' => 'string', 'description' => 'YYYY-MM-DD, UTC.'], |
| 149 |
'date_from' => $dateFrom, |
| 150 |
'date_to' => $dateTo, |
| 151 |
'since' => $since, |
| 152 |
'interval' => ['type' => 'string', 'enum' => ['hour', 'day', 'week', 'month'], 'default' => 'day', 'description' => 'Bucket size. hour is for intraday launch monitoring (capped at 180 buckets per call). Alias: granularity.'], |
| 153 |
'granularity' => ['type' => 'string', 'enum' => ['hour', 'day', 'week', 'month'], 'description' => 'Alias for interval.'], |
| 154 |
'currency' => ['type' => 'string'], |
| 155 |
], |
| 156 |
], |
| 157 |
'execute_callback' => [self::class, 'getSalesTrend'], |
| 158 |
'permission_callback' => function () { |
| 159 |
return PermissionGate::can('reports/view'); |
| 160 |
}, |
| 161 |
'annotations' => ['readonly' => true], |
| 162 |
], |
| 163 |
|
| 164 |
'fluent-cart/get-top-products' => [ |
| 165 |
'label' => __('Get Top Products', 'fluent-cart'), |
| 166 |
'description' => __('Best-selling products over a period, ranked by revenue or units sold. Scoped to one currency.', 'fluent-cart'), |
| 167 |
'input_schema' => [ |
| 168 |
'type' => 'object', |
| 169 |
'properties' => [ |
| 170 |
'range' => $rangeProp, |
| 171 |
'start_date' => ['type' => 'string'], |
| 172 |
'end_date' => ['type' => 'string'], |
| 173 |
'date_from' => $dateFrom, |
| 174 |
'date_to' => $dateTo, |
| 175 |
'since' => $since, |
| 176 |
'metric' => ['type' => 'string', 'enum' => ['revenue', 'units'], 'default' => 'revenue'], |
| 177 |
'currency' => ['type' => 'string'], |
| 178 |
'limit' => ['type' => 'integer', 'default' => 10, 'description' => 'Max 50.'], |
| 179 |
], |
| 180 |
], |
| 181 |
'execute_callback' => [self::class, 'getTopProducts'], |
| 182 |
'permission_callback' => function () { |
| 183 |
return PermissionGate::can('reports/view'); |
| 184 |
}, |
| 185 |
'annotations' => ['readonly' => true], |
| 186 |
], |
| 187 |
|
| 188 |
'fluent-cart/get-refund-report' => [ |
| 189 |
'label' => __('Get Refund Report', 'fluent-cart'), |
| 190 |
'description' => __('Refund metrics for a period: refunded order count, refund rate as a share of paid orders, total and average refunded amount. Counts every order refunded in the window from its total_refund, including orders that were fully refunded and then canceled (not just partial refunds on still-paid orders). Scoped to one currency.', 'fluent-cart'), |
| 191 |
'input_schema' => [ |
| 192 |
'type' => 'object', |
| 193 |
'properties' => [ |
| 194 |
'range' => $rangeProp, |
| 195 |
'start_date' => ['type' => 'string'], |
| 196 |
'end_date' => ['type' => 'string'], |
| 197 |
'date_from' => $dateFrom, |
| 198 |
'date_to' => $dateTo, |
| 199 |
'since' => $since, |
| 200 |
'currency' => ['type' => 'string'], |
| 201 |
], |
| 202 |
], |
| 203 |
'execute_callback' => [self::class, 'getRefundReport'], |
| 204 |
'permission_callback' => function () { |
| 205 |
return PermissionGate::can('reports/view'); |
| 206 |
}, |
| 207 |
'annotations' => ['readonly' => true], |
| 208 |
], |
| 209 |
|
| 210 |
'fluent-cart/query-sources' => [ |
| 211 |
'label' => __('Query Sources (UTM attribution)', 'fluent-cart'), |
| 212 |
'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. Pass product_id (or variation_id) to attribute only orders containing that product. 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'), |
| 213 |
'input_schema' => [ |
| 214 |
'type' => 'object', |
| 215 |
'properties' => [ |
| 216 |
'metrics' => ['type' => 'array', 'description' => 'Defaults to orders and gross_revenue.', 'items' => ['type' => 'string', 'enum' => ['orders', 'gross_revenue', 'net_revenue', 'aov', 'unique_customers', 'refunded_amount']]], |
| 217 |
'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']]], |
| 218 |
'utm_source' => ['type' => 'string', 'description' => 'Filter to one source, exact match.'], |
| 219 |
'utm_medium' => ['type' => 'string', 'description' => 'Filter to one medium, exact match.'], |
| 220 |
'utm_campaign' => ['type' => 'string', 'description' => 'Filter to one campaign, exact match.'], |
| 221 |
'product_id' => ['type' => 'integer', 'description' => 'Limit attribution to orders containing this product (e.g. one product on a multi-product store).'], |
| 222 |
'variation_id' => ['type' => 'integer', 'description' => 'Limit attribution to orders containing this variation.'], |
| 223 |
'range' => $rangeProp, |
| 224 |
'start_date' => ['type' => 'string'], |
| 225 |
'end_date' => ['type' => 'string'], |
| 226 |
'date_from' => $dateFrom, |
| 227 |
'date_to' => $dateTo, |
| 228 |
'since' => $since, |
| 229 |
'currency' => ['type' => 'string'], |
| 230 |
'limit' => ['type' => 'integer', 'default' => 50, 'description' => 'Max 200.'], |
| 231 |
], |
| 232 |
], |
| 233 |
'execute_callback' => [self::class, 'querySources'], |
| 234 |
'permission_callback' => function () { |
| 235 |
return PermissionGate::can('reports/view'); |
| 236 |
}, |
| 237 |
'annotations' => ['readonly' => true], |
| 238 |
], |
| 239 |
|
| 240 |
'fluent-cart/query-orders' => [ |
| 241 |
'label' => __('Query Orders (flexible aggregate)', 'fluent-cart'), |
| 242 |
'description' => __('Flexible order analytics: pick metrics, group by dimensions with filters, when a fixed report does not fit — e.g. revenue by payment_status, orders by month, or revenue by order_type (one-time payment vs new subscription vs renewal). product_id or variation_id limits to orders containing that product. One currency; window filters on created_at, echoed as meta.date_basis.', 'fluent-cart'), |
| 243 |
'input_schema' => [ |
| 244 |
'type' => 'object', |
| 245 |
'properties' => [ |
| 246 |
'metrics' => [ |
| 247 |
'type' => 'array', |
| 248 |
'description' => 'One or more. Defaults to order_count and gross_revenue.', |
| 249 |
'items' => ['type' => 'string', 'enum' => ['order_count', 'gross_revenue', 'paid_revenue', 'refunded_amount', 'aov', 'unique_customers']], |
| 250 |
], |
| 251 |
'dimensions' => [ |
| 252 |
'type' => 'array', |
| 253 |
'description' => 'Group by these. Empty means a single total row. order_type splits sales by payment (one-time purchase), subscription (first subscription order) and renewal (recurring charge); combine with a time dimension for e.g. order_type x month.', |
| 254 |
'items' => ['type' => 'string', 'enum' => ['day', 'week', 'month', 'status', 'payment_status', 'order_type']], |
| 255 |
], |
| 256 |
'product_id' => ['type' => 'integer', 'description' => 'Limit to orders CONTAINING this product. Order-level metrics (revenue, count) reflect the whole order, not just this product\'s lines — for per-product line revenue use query-products.'], |
| 257 |
'variation_id' => ['type' => 'integer', 'description' => 'Limit to orders containing this variation.'], |
| 258 |
'range' => $rangeProp, |
| 259 |
'start_date' => ['type' => 'string'], |
| 260 |
'end_date' => ['type' => 'string'], |
| 261 |
'date_from' => $dateFrom, |
| 262 |
'date_to' => $dateTo, |
| 263 |
'since' => $since, |
| 264 |
'currency' => ['type' => 'string'], |
| 265 |
'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.'], |
| 266 |
], |
| 267 |
], |
| 268 |
'execute_callback' => [self::class, 'queryOrders'], |
| 269 |
'permission_callback' => function () { |
| 270 |
return PermissionGate::can('reports/view'); |
| 271 |
}, |
| 272 |
'annotations' => ['readonly' => true], |
| 273 |
], |
| 274 |
|
| 275 |
'fluent-cart/query-products' => [ |
| 276 |
'label' => __('Query Products (flexible aggregate)', 'fluent-cart'), |
| 277 |
'description' => __('Flexible product-line analytics over sold items: pick metrics, group by product or variation, optionally split by order_type (one-time payment vs new subscription vs renewal), within a period and one currency. Rows self-describe: product_name always, plus variation_label when grouped by variation, so no follow-up lookup. For a time series use get-sales-trend. Window filters on the parent order created_at, echoed as meta.date_basis.', 'fluent-cart'), |
| 278 |
'input_schema' => [ |
| 279 |
'type' => 'object', |
| 280 |
'properties' => [ |
| 281 |
'metrics' => ['type' => 'array', 'description' => 'Defaults to units_sold and line_revenue. list_price_sum = units x list price before any discount; discount_amount = list_price_sum minus line_revenue (coupon + manual discount) — pick both with line_revenue to see margin leakage directly; net_revenue = line_revenue minus refunds.', 'items' => ['type' => 'string', 'enum' => ['units_sold', 'line_revenue', 'list_price_sum', 'discount_amount', 'net_revenue', 'order_count', 'avg_unit_price', 'refund_amount']]], |
| 282 |
'dimensions' => ['type' => 'array', 'description' => 'Group by these. order_type splits a product\'s sales by the parent order type: payment (one-time purchase), subscription (first subscription order) and renewal (recurring charge). Combine with product/variation, e.g. product x order_type.', 'items' => ['type' => 'string', 'enum' => ['product', 'variation', 'order_type']]], |
| 283 |
'product_id' => ['type' => 'integer', 'description' => 'Limit to one product (by product/post id). Combine with dimensions=[variation] to break that single product down by variation.'], |
| 284 |
'variation_id' => ['type' => 'integer', 'description' => 'Limit to one variation of the product.'], |
| 285 |
'range' => $rangeProp, |
| 286 |
'start_date' => ['type' => 'string'], |
| 287 |
'end_date' => ['type' => 'string'], |
| 288 |
'date_from' => $dateFrom, |
| 289 |
'date_to' => $dateTo, |
| 290 |
'since' => $since, |
| 291 |
'currency' => ['type' => 'string'], |
| 292 |
], |
| 293 |
], |
| 294 |
'execute_callback' => [self::class, 'queryProducts'], |
| 295 |
'permission_callback' => function () { |
| 296 |
return PermissionGate::can('reports/view'); |
| 297 |
}, |
| 298 |
'annotations' => ['readonly' => true], |
| 299 |
], |
| 300 |
|
| 301 |
'fluent-cart/query-customers' => [ |
| 302 |
'label' => __('Query Customers (flexible aggregate)', 'fluent-cart'), |
| 303 |
'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'), |
| 304 |
'input_schema' => [ |
| 305 |
'type' => 'object', |
| 306 |
'properties' => [ |
| 307 |
'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']]], |
| 308 |
'dimensions' => ['type' => 'array', 'items' => ['type' => 'string', 'enum' => ['country', 'state', 'status', 'first_purchase_month', 'last_purchase_month']]], |
| 309 |
'country' => ['type' => 'string'], |
| 310 |
'status' => ['type' => 'string', 'enum' => ['active', 'archived']], |
| 311 |
'min_ltv' => ['type' => 'number', 'description' => 'Minimum LTV in store currency.'], |
| 312 |
'min_purchase_count' => ['type' => 'integer'], |
| 313 |
], |
| 314 |
], |
| 315 |
'execute_callback' => [self::class, 'queryCustomers'], |
| 316 |
'permission_callback' => function () { |
| 317 |
return PermissionGate::can('reports/view'); |
| 318 |
}, |
| 319 |
'annotations' => ['readonly' => true], |
| 320 |
], |
| 321 |
|
| 322 |
'fluent-cart/query-subscriptions' => [ |
| 323 |
'label' => __('Query Subscriptions (flexible aggregate)', 'fluent-cart'), |
| 324 |
'description' => __('Flexible subscription analytics: pick metrics, group by month/plan_type/status/billing_interval over a date window. plan_type splits fixed-term installment/split-pay from open-ended recurring plans. contract_value books installments at their full committed price, recurring_total x bill_times — a split-pay deal counts once at signup, not per charge. date_basis=created_at for booking cohorts, canceled_at for churn — a completed installment is paid-in-full, never churn. Not currency-scoped; money is store currency.', 'fluent-cart'), |
| 325 |
'input_schema' => [ |
| 326 |
'type' => 'object', |
| 327 |
'properties' => [ |
| 328 |
'metrics' => ['type' => 'array', 'description' => 'Defaults to subscription_count and contract_value. contract_value = full committed price of installments, recurring_total x bill_times, and 0 for open-ended plans; recurring_value = one-cycle total.', 'items' => ['type' => 'string', 'enum' => ['subscription_count', 'contract_value', 'recurring_value']]], |
| 329 |
'dimensions' => ['type' => 'array', 'description' => 'Group by these. Empty means a single total row. plan_type splits installment vs recurring; status keeps completed installments separate from canceled/expired churn.', 'items' => ['type' => 'string', 'enum' => ['month', 'plan_type', 'status', 'billing_interval']]], |
| 330 |
'date_basis' => ['type' => 'string', 'enum' => ['created_at', 'canceled_at', 'next_billing_date'], 'default' => 'created_at', 'description' => 'Which date the range and the month dimension use: created_at for booking cohorts, canceled_at for churn, next_billing_date for upcoming renewals.'], |
| 331 |
'plan_type' => ['type' => 'string', 'enum' => ['installment', 'recurring', 'all'], 'default' => 'all', 'description' => 'Filter to installment (bill_times > 0) or recurring (bill_times = 0) plans.'], |
| 332 |
'status' => ['type' => 'string', 'enum' => $subStatuses], |
| 333 |
'product_id' => ['type' => 'integer', 'description' => 'Limit to subscriptions for one product.'], |
| 334 |
'range' => $rangeProp, |
| 335 |
'start_date' => ['type' => 'string'], |
| 336 |
'end_date' => ['type' => 'string'], |
| 337 |
'date_from' => $dateFrom, |
| 338 |
'date_to' => $dateTo, |
| 339 |
'since' => $since, |
| 340 |
], |
| 341 |
], |
| 342 |
'execute_callback' => [self::class, 'querySubscriptions'], |
| 343 |
'permission_callback' => function () { |
| 344 |
return PermissionGate::can('reports/view'); |
| 345 |
}, |
| 346 |
'annotations' => ['readonly' => true], |
| 347 |
], |
| 348 |
]; |
| 349 |
|
| 350 |
// The live/test mode filter applies only to order-based reports — |
| 351 |
// fct_orders has a mode column, but subscription/customer analytics do |
| 352 |
// not. Injected here so the shared $modeProp stays a single definition. |
| 353 |
foreach ([ |
| 354 |
'fluent-cart/get-sales-report', |
| 355 |
'fluent-cart/get-sales-trend', |
| 356 |
'fluent-cart/get-top-products', |
| 357 |
'fluent-cart/get-refund-report', |
| 358 |
'fluent-cart/query-sources', |
| 359 |
'fluent-cart/query-orders', |
| 360 |
'fluent-cart/query-products', |
| 361 |
] as $modeTool) { |
| 362 |
$defs[$modeTool]['input_schema']['properties']['mode'] = $modeProp; |
| 363 |
} |
| 364 |
|
| 365 |
// page/per_page belong on the flexible aggregates, whose grouped output can |
| 366 |
// exceed the 200-row cap. The fixed reports (sales/trend/top/refund) return |
| 367 |
// a bounded shape and don't paginate. query-sources is excluded on purpose: |
| 368 |
// it already exposes its own `limit` + peek + truncated, and adding a |
| 369 |
// second page-size param would be ambiguous. |
| 370 |
foreach ([ |
| 371 |
'fluent-cart/query-orders', |
| 372 |
'fluent-cart/query-products', |
| 373 |
'fluent-cart/query-customers', |
| 374 |
'fluent-cart/query-subscriptions', |
| 375 |
] as $pagedTool) { |
| 376 |
$defs[$pagedTool]['input_schema']['properties']['page'] = $pageProp; |
| 377 |
$defs[$pagedTool]['input_schema']['properties']['per_page'] = $perPageProp; |
| 378 |
} |
| 379 |
|
| 380 |
// All five query-* aggregates share the same response envelope. |
| 381 |
foreach ([ |
| 382 |
'fluent-cart/query-orders', |
| 383 |
'fluent-cart/query-products', |
| 384 |
'fluent-cart/query-customers', |
| 385 |
'fluent-cart/query-subscriptions', |
| 386 |
'fluent-cart/query-sources', |
| 387 |
] as $queryTool) { |
| 388 |
$defs[$queryTool]['output_schema'] = $queryOutputSchema; |
| 389 |
} |
| 390 |
|
| 391 |
return $defs; |
| 392 |
} |
| 393 |
|
| 394 |
// ----------------------------------------------------------------- |
| 395 |
// get-sales-report |
| 396 |
// ----------------------------------------------------------------- |
| 397 |
|
| 398 |
public static function getSalesReport($params = []) |
| 399 |
{ |
| 400 |
$currency = self::currency($params); |
| 401 |
$range = self::resolveRange($params); |
| 402 |
$mode = self::orderMode($params); |
| 403 |
|
| 404 |
$current = self::salesMetrics($range['start'], $range['end'], $currency, $mode); |
| 405 |
|
| 406 |
$data = [ |
| 407 |
'range' => self::rangeBlock($range, $currency), |
| 408 |
'metrics' => self::salesMetricsOut($current, $currency), |
| 409 |
'definitions' => self::metricDefs(), |
| 410 |
]; |
| 411 |
|
| 412 |
$compare = !isset($params['compare']) || !empty($params['compare']); |
| 413 |
if ($compare && $range['prev_start']) { |
| 414 |
$prior = self::salesMetrics($range['prev_start'], $range['prev_end'], $currency, $mode); |
| 415 |
$data['comparison'] = [ |
| 416 |
'prior_metrics' => self::salesMetricsOut($prior, $currency), |
| 417 |
'change_percent' => [ |
| 418 |
'gross_revenue' => self::pct($current['gross'], $prior['gross']), |
| 419 |
'net_revenue' => self::pct($current['net'], $prior['net']), |
| 420 |
'order_count' => self::pct($current['orders'], $prior['orders']), |
| 421 |
], |
| 422 |
]; |
| 423 |
} |
| 424 |
|
| 425 |
$summary = sprintf( |
| 426 |
/* translators: 1: gross revenue, 2: order count, 3: average order value */ |
| 427 |
__('Revenue %1$s across %2$d paid orders, AOV %3$s.', 'fluent-cart'), |
| 428 |
MCPHelper::displayAmount($current['gross'], $currency), |
| 429 |
$current['orders'], |
| 430 |
MCPHelper::displayAmount($current['aov'], $currency) |
| 431 |
); |
| 432 |
|
| 433 |
return MCPHelper::envelope($summary, $data, ['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode]); |
| 434 |
} |
| 435 |
|
| 436 |
private static function salesMetrics($start, $end, $currency, $mode = 'all') |
| 437 |
{ |
| 438 |
// One aggregate scan instead of eight (this is the headline report, and |
| 439 |
// it runs twice when compare=true). Same filtered set, same numbers. |
| 440 |
$q = Order::query() |
| 441 |
->whereIn('payment_status', self::PAID) |
| 442 |
->where('currency', $currency) |
| 443 |
->where('created_at', '>=', $start) |
| 444 |
->where('created_at', '<=', $end); |
| 445 |
self::applyMode($q, $mode); |
| 446 |
$row = $q->selectRaw( |
| 447 |
'COUNT(*) as orders, ' |
| 448 |
. 'COALESCE(SUM(total_amount), 0) as gross, ' |
| 449 |
. 'COALESCE(SUM(total_paid), 0) as paid, ' |
| 450 |
. 'COALESCE(SUM(total_refund), 0) as refund, ' |
| 451 |
. 'COALESCE(SUM(tax_total), 0) as tax, ' |
| 452 |
. 'COALESCE(SUM(shipping_total), 0) as ship, ' |
| 453 |
. 'COALESCE(SUM(fee_total), 0) as fees, ' |
| 454 |
. 'COUNT(DISTINCT customer_id) as uniq' |
| 455 |
) |
| 456 |
->first(); |
| 457 |
|
| 458 |
$orders = $row ? (int) $row->orders : 0; |
| 459 |
$gross = $row ? (int) $row->gross : 0; |
| 460 |
$paid = $row ? (int) $row->paid : 0; |
| 461 |
$refund = $row ? (int) $row->refund : 0; |
| 462 |
$tax = $row ? (int) $row->tax : 0; |
| 463 |
$ship = $row ? (int) $row->ship : 0; |
| 464 |
$fees = $row ? (int) $row->fees : 0; |
| 465 |
$uniq = $row ? (int) $row->uniq : 0; |
| 466 |
|
| 467 |
return [ |
| 468 |
'orders' => $orders, |
| 469 |
'gross' => $gross, |
| 470 |
'paid' => $paid, |
| 471 |
'refund' => $refund, |
| 472 |
'net' => $paid - $refund, |
| 473 |
'tax' => $tax, |
| 474 |
'shipping' => $ship, |
| 475 |
'fees' => $fees, |
| 476 |
'unique' => $uniq, |
| 477 |
'aov' => $orders > 0 ? (int) round($gross / $orders) : 0, |
| 478 |
]; |
| 479 |
} |
| 480 |
|
| 481 |
private static function salesMetricsOut($m, $currency) |
| 482 |
{ |
| 483 |
return [ |
| 484 |
'order_count' => $m['orders'], |
| 485 |
'unique_customers' => $m['unique'], |
| 486 |
'gross_revenue' => MCPHelper::money($m['gross'], $currency), |
| 487 |
'net_revenue' => MCPHelper::money($m['net'], $currency), |
| 488 |
'paid' => MCPHelper::money($m['paid'], $currency), |
| 489 |
'refunded' => MCPHelper::money($m['refund'], $currency), |
| 490 |
'tax' => MCPHelper::money($m['tax'], $currency), |
| 491 |
'shipping' => MCPHelper::money($m['shipping'], $currency), |
| 492 |
'fees' => MCPHelper::money($m['fees'], $currency), |
| 493 |
'aov' => MCPHelper::money($m['aov'], $currency), |
| 494 |
]; |
| 495 |
} |
| 496 |
|
| 497 |
private static function metricDefs() |
| 498 |
{ |
| 499 |
return [ |
| 500 |
'paid_orders' => 'Orders that captured payment at some point (payment_status in: ' . implode(', ', self::PAID) . '). A fully refunded order (payment_status "refunded") is included — it captured payment, so it counts toward gross/paid and the refunded total, and nets to zero in net_revenue.', |
| 501 |
'gross_revenue' => 'Sum of order total_amount for paid orders (gross sales, before refunds).', |
| 502 |
'net_revenue' => 'Sum of total_paid minus total_refund; a fully refunded order nets to zero.', |
| 503 |
'refunded' => 'Sum of total_refund over paid orders — includes fully refunded orders, independent of current order status.', |
| 504 |
'aov' => 'gross_revenue divided by paid order count.', |
| 505 |
'date_basis' => 'created_at, within the given range.', |
| 506 |
]; |
| 507 |
} |
| 508 |
|
| 509 |
// ----------------------------------------------------------------- |
| 510 |
// get-sales-trend |
| 511 |
// ----------------------------------------------------------------- |
| 512 |
|
| 513 |
public static function getSalesTrend($params = []) |
| 514 |
{ |
| 515 |
$currency = self::currency($params); |
| 516 |
$range = self::resolveRange($params); |
| 517 |
$mode = self::orderMode($params); |
| 518 |
// `granularity` is an alias for `interval`; hour is for intraday launch |
| 519 |
// monitoring (MAX_BUCKETS caps it at 180 hours ~ 7.5 days per call). |
| 520 |
$intervalIn = isset($params['granularity']) ? $params['granularity'] : (isset($params['interval']) ? $params['interval'] : 'day'); |
| 521 |
$interval = in_array($intervalIn, ['hour', 'day', 'week', 'month'], true) ? $intervalIn : 'day'; |
| 522 |
|
| 523 |
$format = $interval === 'month' |
| 524 |
? '%Y-%m' |
| 525 |
: ($interval === 'week' ? '%x-W%v' : ($interval === 'hour' ? '%Y-%m-%d %H:00' : '%Y-%m-%d')); |
| 526 |
|
| 527 |
$q = Order::query() |
| 528 |
->whereIn('payment_status', self::PAID) |
| 529 |
->where('currency', $currency) |
| 530 |
->where('created_at', '>=', $range['start']) |
| 531 |
->where('created_at', '<=', $range['end']); |
| 532 |
self::applyMode($q, $mode); |
| 533 |
$rows = $q->selectRaw('DATE_FORMAT(created_at, ?) as bucket, COUNT(*) as order_count, SUM(total_amount) as gross', [$format]) |
| 534 |
->groupBy('bucket') |
| 535 |
->orderBy('bucket', 'ASC') |
| 536 |
->limit(self::MAX_BUCKETS) |
| 537 |
->get(); |
| 538 |
|
| 539 |
$trend = []; |
| 540 |
$sum = 0; |
| 541 |
foreach ($rows as $row) { |
| 542 |
$gross = (int) $row->gross; |
| 543 |
$sum += $gross; |
| 544 |
$trend[] = [ |
| 545 |
'bucket' => $row->bucket, |
| 546 |
'order_count' => (int) $row->order_count, |
| 547 |
'gross' => MCPHelper::moneyCompact($gross), |
| 548 |
]; |
| 549 |
} |
| 550 |
|
| 551 |
$summary = sprintf( |
| 552 |
/* translators: 1: number of buckets, 2: interval, 3: total revenue */ |
| 553 |
__('%1$d %2$s buckets, total revenue %3$s.', 'fluent-cart'), |
| 554 |
count($trend), |
| 555 |
$interval, |
| 556 |
MCPHelper::displayAmount($sum, $currency) |
| 557 |
); |
| 558 |
|
| 559 |
return MCPHelper::envelope( |
| 560 |
$summary, |
| 561 |
['interval' => $interval, 'range' => self::rangeBlock($range, $currency), 'trend' => $trend], |
| 562 |
['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode, 'truncated' => count($rows) >= self::MAX_BUCKETS] |
| 563 |
); |
| 564 |
} |
| 565 |
|
| 566 |
// ----------------------------------------------------------------- |
| 567 |
// get-top-products |
| 568 |
// ----------------------------------------------------------------- |
| 569 |
|
| 570 |
public static function getTopProducts($params = []) |
| 571 |
{ |
| 572 |
$currency = self::currency($params); |
| 573 |
$range = self::resolveRange($params); |
| 574 |
$mode = self::orderMode($params); |
| 575 |
$metric = isset($params['metric']) && $params['metric'] === 'units' ? 'units' : 'revenue'; |
| 576 |
$limit = isset($params['limit']) ? min(max((int) $params['limit'], 1), 50) : 10; |
| 577 |
$orderCol = $metric === 'units' ? 'units' : 'revenue'; |
| 578 |
|
| 579 |
$rows = OrderItem::query() |
| 580 |
->whereHas('order', function ($q) use ($range, $currency, $mode) { |
| 581 |
$q->whereIn('payment_status', self::PAID) |
| 582 |
->where('currency', $currency) |
| 583 |
->where('created_at', '>=', $range['start']) |
| 584 |
->where('created_at', '<=', $range['end']); |
| 585 |
self::applyMode($q, $mode); |
| 586 |
}) |
| 587 |
->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') |
| 588 |
->groupBy('post_id') |
| 589 |
->orderBy($orderCol, 'DESC') |
| 590 |
->limit($limit) |
| 591 |
->get(); |
| 592 |
|
| 593 |
$products = []; |
| 594 |
foreach ($rows as $row) { |
| 595 |
$products[] = [ |
| 596 |
'product_id' => (int) $row->post_id, |
| 597 |
'title' => $row->title, |
| 598 |
'units_sold' => (int) $row->units, |
| 599 |
'revenue' => MCPHelper::moneyCompact((int) $row->revenue), |
| 600 |
'order_count' => (int) $row->order_count, |
| 601 |
]; |
| 602 |
} |
| 603 |
|
| 604 |
$summary = sprintf( |
| 605 |
/* translators: 1: number of products, 2: ranking metric */ |
| 606 |
__('Top %1$d products by %2$s.', 'fluent-cart'), |
| 607 |
count($products), |
| 608 |
$metric |
| 609 |
); |
| 610 |
|
| 611 |
return MCPHelper::envelope($summary, ['metric' => $metric, 'range' => self::rangeBlock($range, $currency), 'products' => $products], ['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode]); |
| 612 |
} |
| 613 |
|
| 614 |
// ----------------------------------------------------------------- |
| 615 |
// get-refund-report |
| 616 |
// ----------------------------------------------------------------- |
| 617 |
|
| 618 |
public static function getRefundReport($params = []) |
| 619 |
{ |
| 620 |
$currency = self::currency($params); |
| 621 |
$range = self::resolveRange($params); |
| 622 |
$mode = self::orderMode($params); |
| 623 |
|
| 624 |
$paidBase = Order::query() |
| 625 |
->whereIn('payment_status', self::PAID) |
| 626 |
->where('currency', $currency) |
| 627 |
->where('created_at', '>=', $range['start']) |
| 628 |
->where('created_at', '<=', $range['end']); |
| 629 |
self::applyMode($paidBase, $mode); |
| 630 |
|
| 631 |
$paidCount = (clone $paidBase)->count(); |
| 632 |
|
| 633 |
$refundedBase = (clone $paidBase)->where('total_refund', '>', 0); |
| 634 |
$refundedCount = (clone $refundedBase)->count(); |
| 635 |
$refundedAmount = (int) (clone $refundedBase)->sum('total_refund'); |
| 636 |
|
| 637 |
$rate = $paidCount > 0 ? round(($refundedCount / $paidCount) * 100, 2) : 0; |
| 638 |
$avg = $refundedCount > 0 ? (int) round($refundedAmount / $refundedCount) : 0; |
| 639 |
|
| 640 |
$summary = sprintf( |
| 641 |
/* translators: 1: refunded order count, 2: refund rate percent, 3: total refunded */ |
| 642 |
__('%1$d orders refunded, %2$s%% of paid, totaling %3$s.', 'fluent-cart'), |
| 643 |
$refundedCount, |
| 644 |
$rate, |
| 645 |
MCPHelper::displayAmount($refundedAmount, $currency) |
| 646 |
); |
| 647 |
|
| 648 |
return MCPHelper::envelope( |
| 649 |
$summary, |
| 650 |
[ |
| 651 |
'range' => self::rangeBlock($range, $currency), |
| 652 |
'paid_order_count' => $paidCount, |
| 653 |
'refunded_order_count' => $refundedCount, |
| 654 |
'refund_rate_percent' => $rate, |
| 655 |
'total_refunded' => MCPHelper::money($refundedAmount, $currency), |
| 656 |
'average_refund' => MCPHelper::money($avg, $currency), |
| 657 |
'definitions' => [ |
| 658 |
'refunded_order_count' => 'Orders with total_refund > 0 in the window, regardless of current status — a fully refunded (canceled) order still counts. Matches the admin refund report.', |
| 659 |
'paid_order_count' => 'Orders that captured payment in the window, including those later fully refunded. This is the refund_rate denominator.', |
| 660 |
'refund_rate_percent' => 'refunded_order_count / paid_order_count * 100. A fully refunded order was paid before being refunded, so it is counted in BOTH the numerator and the denominator.', |
| 661 |
], |
| 662 |
], |
| 663 |
['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode] |
| 664 |
); |
| 665 |
} |
| 666 |
|
| 667 |
// ----------------------------------------------------------------- |
| 668 |
// query-sources (flexible UTM attribution) |
| 669 |
// ----------------------------------------------------------------- |
| 670 |
|
| 671 |
const UTM_DIMENSIONS = ['utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content', 'utm_id']; |
| 672 |
|
| 673 |
public static function querySources($params = []) |
| 674 |
{ |
| 675 |
$currency = self::currency($params); |
| 676 |
$range = self::resolveRange($params); |
| 677 |
$mode = self::orderMode($params); |
| 678 |
$limit = isset($params['limit']) ? min(max((int) $params['limit'], 1), self::MAX_ROWS) : 50; |
| 679 |
$metrics = self::pickList($params, 'metrics', ['orders', 'gross_revenue', 'net_revenue', 'aov', 'unique_customers', 'refunded_amount'], ['orders', 'gross_revenue']); |
| 680 |
$dimensions = self::pickList($params, 'dimensions', self::UTM_DIMENSIONS, ['utm_source', 'utm_medium', 'utm_campaign']); |
| 681 |
|
| 682 |
// Build the aggregate directly (rather than via SourceReportService, |
| 683 |
// which hard-codes its grouping) so the agent picks the UTM dimensions. |
| 684 |
// Uses the raw query builder with the same aliases as the admin Source |
| 685 |
// report to avoid Order model global scopes. Paid + one currency to stay |
| 686 |
// consistent with the other reports. |
| 687 |
// Dedupe operations to one row per order before joining: fct_order_operations |
| 688 |
// has only an INDEX on order_id (not UNIQUE), so a raw leftJoin would fan out |
| 689 |
// and make every SUM(o.<money>) double-count any order with >1 ops row. |
| 690 |
// MAX() per UTM column is ONLY_FULL_GROUP_BY-safe and returns the single |
| 691 |
// row's value in the normal one-row-per-order case. |
| 692 |
$opSub = App::db()->table('fct_order_operations') |
| 693 |
->select('order_id') |
| 694 |
->selectRaw( |
| 695 |
'MAX(utm_source) as utm_source, MAX(utm_medium) as utm_medium, ' |
| 696 |
. 'MAX(utm_campaign) as utm_campaign, MAX(utm_term) as utm_term, ' |
| 697 |
. 'MAX(utm_content) as utm_content, MAX(utm_id) as utm_id' |
| 698 |
) |
| 699 |
->groupBy('order_id'); |
| 700 |
|
| 701 |
$query = App::db()->table('fct_orders as o') |
| 702 |
->leftJoinSub($opSub, 'oo', 'o.id', '=', 'oo.order_id') |
| 703 |
->whereIn('o.payment_status', self::PAID) |
| 704 |
->where('o.currency', $currency) |
| 705 |
->where('o.created_at', '>=', $range['start']) |
| 706 |
->where('o.created_at', '<=', $range['end']); |
| 707 |
// Orders table is aliased 'o' here; qualify the mode column to match. |
| 708 |
self::applyMode($query, $mode, 'o.mode'); |
| 709 |
|
| 710 |
// Optional drill-down filters on exact UTM values. |
| 711 |
foreach (['utm_source', 'utm_medium', 'utm_campaign'] as $f) { |
| 712 |
if (!empty($params[$f])) { |
| 713 |
$query->where('oo.' . $f, sanitize_text_field($params[$f])); |
| 714 |
} |
| 715 |
} |
| 716 |
|
| 717 |
// Optional entity filters: restrict attribution to orders containing a |
| 718 |
// product/variation. whereExists on fct_order_items (never a join, so the |
| 719 |
// per-order SUM()s don't fan out) — mirrors the admin SourceReport filter. |
| 720 |
foreach (['product_id' => 'post_id', 'variation_id' => 'object_id'] as $param => $col) { |
| 721 |
if (!empty($params[$param])) { |
| 722 |
$val = (int) $params[$param]; |
| 723 |
$query->whereExists(function ($q) use ($col, $val) { |
| 724 |
$q->selectRaw('1') |
| 725 |
->from('fct_order_items as oi') |
| 726 |
->whereRaw('oi.order_id = o.id') |
| 727 |
->where('oi.' . $col, $val); |
| 728 |
}); |
| 729 |
} |
| 730 |
} |
| 731 |
|
| 732 |
$selects = []; |
| 733 |
$groupExpr = []; |
| 734 |
foreach ($dimensions as $dim) { |
| 735 |
// Coalesce NULL/'' into a single 'none' bucket; group by the |
| 736 |
// expression so the split values collapse together. |
| 737 |
$expr = "COALESCE(NULLIF(oo." . $dim . ", ''), 'none')"; |
| 738 |
$selects[] = $expr . ' as ' . $dim; |
| 739 |
$groupExpr[] = $expr; |
| 740 |
} |
| 741 |
|
| 742 |
// Definitions must match metricDefs() and the sales report so an agent's |
| 743 |
// "revenue by source" ties out with "revenue this month": |
| 744 |
// gross_revenue = SUM(total_amount), net_revenue = SUM(total_paid - total_refund). |
| 745 |
$metricSql = [ |
| 746 |
'orders' => 'COUNT(DISTINCT o.id) as orders', |
| 747 |
'gross_revenue' => 'SUM(o.total_amount) as gross_revenue', |
| 748 |
'net_revenue' => 'SUM(o.total_paid - o.total_refund) as net_revenue', |
| 749 |
'unique_customers' => 'COUNT(DISTINCT o.customer_id) as unique_customers', |
| 750 |
'refunded_amount' => 'SUM(o.total_refund) as refunded_amount', |
| 751 |
]; |
| 752 |
foreach ($metrics as $m) { |
| 753 |
if (isset($metricSql[$m])) { |
| 754 |
$selects[] = $metricSql[$m]; |
| 755 |
} |
| 756 |
} |
| 757 |
if (in_array('aov', $metrics, true)) { |
| 758 |
if (!in_array('gross_revenue', $metrics, true)) { |
| 759 |
$selects[] = $metricSql['gross_revenue']; |
| 760 |
} |
| 761 |
if (!in_array('orders', $metrics, true)) { |
| 762 |
$selects[] = $metricSql['orders']; |
| 763 |
} |
| 764 |
} |
| 765 |
|
| 766 |
$query->selectRaw(implode(', ', $selects)); |
| 767 |
if ($groupExpr) { |
| 768 |
$query->groupByRaw(implode(', ', $groupExpr)); |
| 769 |
} |
| 770 |
|
| 771 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'orders'; |
| 772 |
if ($firstMetric === 'aov') { |
| 773 |
$firstMetric = 'gross_revenue'; |
| 774 |
} |
| 775 |
if ($groupExpr && isset($metricSql[$firstMetric])) { |
| 776 |
$query->orderBy($firstMetric, 'DESC'); |
| 777 |
} |
| 778 |
// Fetch one extra row so we can tell "more exist beyond your limit" from |
| 779 |
// "you hit the 200 hard cap". $limit is already clamped to <= MAX_ROWS. |
| 780 |
$query->limit($limit + 1); |
| 781 |
|
| 782 |
$rows = $query->get(); |
| 783 |
$moneyMetrics = ['gross_revenue', 'net_revenue', 'refunded_amount']; |
| 784 |
$truncated = count($rows) > $limit; |
| 785 |
|
| 786 |
$out = []; |
| 787 |
foreach ($rows as $row) { |
| 788 |
if (count($out) >= $limit) { |
| 789 |
break; |
| 790 |
} |
| 791 |
$r = []; |
| 792 |
foreach ($dimensions as $dim) { |
| 793 |
$r[$dim] = $row->{$dim}; |
| 794 |
} |
| 795 |
foreach ($metrics as $m) { |
| 796 |
if ($m === 'aov') { |
| 797 |
$g = (int) $row->gross_revenue; |
| 798 |
$c = (int) $row->orders; |
| 799 |
$r['aov'] = MCPHelper::moneyCompact($c > 0 ? (int) round($g / $c) : 0); |
| 800 |
} elseif (in_array($m, $moneyMetrics, true)) { |
| 801 |
$r[$m] = MCPHelper::moneyCompact((int) $row->{$m}); |
| 802 |
} else { |
| 803 |
$r[$m] = (int) $row->{$m}; |
| 804 |
} |
| 805 |
} |
| 806 |
$out[] = $r; |
| 807 |
} |
| 808 |
|
| 809 |
$summary = sprintf( |
| 810 |
/* translators: 1: row count, 2: metric list, 3: dimension list */ |
| 811 |
__('%1$d rows — metrics [%2$s] grouped by [%3$s].', 'fluent-cart'), |
| 812 |
count($out), |
| 813 |
implode(', ', $metrics), |
| 814 |
$dimensions ? implode(', ', $dimensions) : __('total', 'fluent-cart') |
| 815 |
); |
| 816 |
|
| 817 |
return MCPHelper::envelope( |
| 818 |
$summary, |
| 819 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'range' => self::rangeBlock($range, $currency), 'rows' => $out], |
| 820 |
[ |
| 821 |
'currency' => $currency, |
| 822 |
'date_basis' => 'created_at', |
| 823 |
'mode' => $mode, |
| 824 |
'returned' => count($out), |
| 825 |
'limit' => $limit, |
| 826 |
'max_rows' => self::MAX_ROWS, |
| 827 |
// true = more rows exist; raise `limit` (up to max_rows) to see them. |
| 828 |
'truncated' => $truncated, |
| 829 |
] |
| 830 |
); |
| 831 |
} |
| 832 |
|
| 833 |
// ----------------------------------------------------------------- |
| 834 |
// query-orders (flexible aggregate) |
| 835 |
// ----------------------------------------------------------------- |
| 836 |
|
| 837 |
public static function queryOrders($params = []) |
| 838 |
{ |
| 839 |
$currency = self::currency($params); |
| 840 |
$range = self::resolveRange($params); |
| 841 |
$mode = self::orderMode($params); |
| 842 |
$metrics = self::pickList($params, 'metrics', ['order_count', 'gross_revenue', 'paid_revenue', 'refunded_amount', 'aov', 'unique_customers'], ['order_count', 'gross_revenue']); |
| 843 |
$dimensions = self::pickList($params, 'dimensions', ['day', 'week', 'month', 'status', 'payment_status', 'order_type'], []); |
| 844 |
|
| 845 |
$query = Order::query() |
| 846 |
->whereIn('payment_status', self::PAID) |
| 847 |
->where('currency', $currency) |
| 848 |
->where('created_at', '>=', $range['start']) |
| 849 |
->where('created_at', '<=', $range['end']); |
| 850 |
self::applyMode($query, $mode); |
| 851 |
|
| 852 |
// Optional entity filters: restrict to orders CONTAINING a product/variation. |
| 853 |
// whereHas keeps the aggregate order-level (metrics still reflect the whole |
| 854 |
// order); it never fans out rows the way a raw join would. |
| 855 |
self::applyOrderItemFilter($query, $params); |
| 856 |
|
| 857 |
$selects = []; |
| 858 |
$groupCols = []; |
| 859 |
foreach ($dimensions as $dim) { |
| 860 |
$selects[] = self::dimensionExpr($dim) . ' as ' . $dim; |
| 861 |
$groupCols[] = $dim; |
| 862 |
} |
| 863 |
|
| 864 |
$metricSql = [ |
| 865 |
'order_count' => 'COUNT(*) as order_count', |
| 866 |
'gross_revenue' => 'SUM(total_amount) as gross_revenue', |
| 867 |
'paid_revenue' => 'SUM(total_paid) as paid_revenue', |
| 868 |
'refunded_amount' => 'SUM(total_refund) as refunded_amount', |
| 869 |
'unique_customers' => 'COUNT(DISTINCT customer_id) as unique_customers', |
| 870 |
]; |
| 871 |
foreach ($metrics as $m) { |
| 872 |
if (isset($metricSql[$m])) { |
| 873 |
$selects[] = $metricSql[$m]; |
| 874 |
} |
| 875 |
} |
| 876 |
if (in_array('aov', $metrics, true)) { |
| 877 |
if (!in_array('gross_revenue', $metrics, true)) { |
| 878 |
$selects[] = $metricSql['gross_revenue']; |
| 879 |
} |
| 880 |
if (!in_array('order_count', $metrics, true)) { |
| 881 |
$selects[] = $metricSql['order_count']; |
| 882 |
} |
| 883 |
} |
| 884 |
|
| 885 |
$query->selectRaw(implode(', ', $selects)); |
| 886 |
foreach ($groupCols as $g) { |
| 887 |
$query->groupBy($g); |
| 888 |
} |
| 889 |
|
| 890 |
$sortDesc = !isset($params['sort_desc']) || !empty($params['sort_desc']); |
| 891 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'order_count'; |
| 892 |
if ($firstMetric === 'aov') { |
| 893 |
$firstMetric = 'gross_revenue'; |
| 894 |
} |
| 895 |
|
| 896 |
// Find the first time dimension, if any. |
| 897 |
$timeDim = null; |
| 898 |
foreach ($dimensions as $dim) { |
| 899 |
if (in_array($dim, ['day', 'week', 'month'], true)) { |
| 900 |
$timeDim = $dim; |
| 901 |
break; |
| 902 |
} |
| 903 |
} |
| 904 |
|
| 905 |
$paging = self::queryPaging($params); |
| 906 |
if ($groupCols) { |
| 907 |
if ($timeDim !== null && !isset($params['sort_desc'])) { |
| 908 |
// A time series reads chronologically by default; ranking a |
| 909 |
// calendar by metric is rarely what's wanted. An explicit |
| 910 |
// sort_desc still overrides this. |
| 911 |
$query->orderBy($timeDim, 'ASC'); |
| 912 |
} else { |
| 913 |
$query->orderBy($firstMetric, $sortDesc ? 'DESC' : 'ASC'); |
| 914 |
} |
| 915 |
// Deterministic tie-break on the group key so offset paging never |
| 916 |
// reshuffles equal-metric rows across pages. |
| 917 |
foreach ($groupCols as $g) { |
| 918 |
$query->orderBy($g, 'ASC'); |
| 919 |
} |
| 920 |
} |
| 921 |
// One extra row peeks past the page boundary → meta.page.has_more. |
| 922 |
$query->limit($paging['per_page'] + 1)->offset($paging['offset']); |
| 923 |
|
| 924 |
$rows = $query->get(); |
| 925 |
$fetched = count($rows); |
| 926 |
$moneyMetrics = ['gross_revenue', 'paid_revenue', 'refunded_amount']; |
| 927 |
|
| 928 |
$out = []; |
| 929 |
foreach ($rows as $row) { |
| 930 |
if (count($out) >= $paging['per_page']) { |
| 931 |
break; |
| 932 |
} |
| 933 |
$r = []; |
| 934 |
foreach ($dimensions as $dim) { |
| 935 |
$r[$dim] = $row->{$dim}; |
| 936 |
} |
| 937 |
foreach ($metrics as $m) { |
| 938 |
if ($m === 'aov') { |
| 939 |
$g = (int) $row->gross_revenue; |
| 940 |
$c = (int) $row->order_count; |
| 941 |
$r['aov'] = MCPHelper::moneyCompact($c > 0 ? (int) round($g / $c) : 0); |
| 942 |
} elseif (in_array($m, $moneyMetrics, true)) { |
| 943 |
$r[$m] = MCPHelper::moneyCompact((int) $row->{$m}); |
| 944 |
} else { |
| 945 |
$r[$m] = (int) $row->{$m}; |
| 946 |
} |
| 947 |
} |
| 948 |
$out[] = $r; |
| 949 |
} |
| 950 |
|
| 951 |
$summary = sprintf( |
| 952 |
/* translators: 1: row count, 2: metric list, 3: dimension list */ |
| 953 |
__('%1$d rows — metrics [%2$s] grouped by [%3$s].', 'fluent-cart'), |
| 954 |
count($out), |
| 955 |
implode(', ', $metrics), |
| 956 |
$dimensions ? implode(', ', $dimensions) : __('total', 'fluent-cart') |
| 957 |
); |
| 958 |
|
| 959 |
return MCPHelper::envelope( |
| 960 |
$summary, |
| 961 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'range' => self::rangeBlock($range, $currency), 'rows' => $out], |
| 962 |
array_merge(['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode], self::pageMeta($paging, $fetched)) |
| 963 |
); |
| 964 |
} |
| 965 |
|
| 966 |
// ----------------------------------------------------------------- |
| 967 |
// query-products / query-customers (flexible aggregates) |
| 968 |
// ----------------------------------------------------------------- |
| 969 |
|
| 970 |
public static function queryProducts($params = []) |
| 971 |
{ |
| 972 |
$currency = self::currency($params); |
| 973 |
$range = self::resolveRange($params); |
| 974 |
$mode = self::orderMode($params); |
| 975 |
$metrics = self::pickList($params, 'metrics', ['units_sold', 'line_revenue', 'list_price_sum', 'discount_amount', 'net_revenue', 'order_count', 'avg_unit_price', 'refund_amount'], ['units_sold', 'line_revenue']); |
| 976 |
$dimensions = self::pickList($params, 'dimensions', ['product', 'variation', 'order_type'], ['product']); |
| 977 |
|
| 978 |
$query = OrderItem::query()->whereHas('order', function ($q) use ($range, $currency, $mode) { |
| 979 |
$q->whereIn('payment_status', self::PAID) |
| 980 |
->where('currency', $currency) |
| 981 |
->where('created_at', '>=', $range['start']) |
| 982 |
->where('created_at', '<=', $range['end']); |
| 983 |
self::applyMode($q, $mode); |
| 984 |
}); |
| 985 |
|
| 986 |
// Entity filters on the line item itself: post_id is the product, object_id |
| 987 |
// the variation. These live on fct_order_items (the base model here), so a |
| 988 |
// plain where — no join — scopes the whole aggregate to one product/variation. |
| 989 |
// Without this, dimensions=[variation] grouped across the ENTIRE catalog even |
| 990 |
// when a product_id was passed (the param was silently dropped). |
| 991 |
if (!empty($params['product_id'])) { |
| 992 |
$query->where('post_id', (int) $params['product_id']); |
| 993 |
} |
| 994 |
if (!empty($params['variation_id'])) { |
| 995 |
$query->where('object_id', (int) $params['variation_id']); |
| 996 |
} |
| 997 |
|
| 998 |
// order_type lives on the parent order (fct_orders.type), not the line |
| 999 |
// item. Join orders only when it's requested so existing calls are |
| 1000 |
// unchanged. order_id -> orders.id is many-to-one, so the join never fans |
| 1001 |
// out line rows and the SUM()s stay identical to the ungrouped query. |
| 1002 |
$groupByOrderType = in_array('order_type', $dimensions, true); |
| 1003 |
if ($groupByOrderType) { |
| 1004 |
$query->join('fct_orders as fctord', 'fct_order_items.order_id', '=', 'fctord.id'); |
| 1005 |
} |
| 1006 |
|
| 1007 |
$hasProduct = in_array('product', $dimensions, true); |
| 1008 |
$hasVariation = in_array('variation', $dimensions, true); |
| 1009 |
|
| 1010 |
$selects = []; |
| 1011 |
$groupCols = []; |
| 1012 |
if ($hasProduct) { |
| 1013 |
$selects[] = 'post_id'; |
| 1014 |
$selects[] = 'MAX(post_title) as product_title'; |
| 1015 |
$groupCols[] = 'post_id'; |
| 1016 |
} |
| 1017 |
if ($hasVariation) { |
| 1018 |
$selects[] = 'object_id'; |
| 1019 |
$groupCols[] = 'object_id'; |
| 1020 |
// Make variation rows self-describing so the agent needs no follow-up |
| 1021 |
// lookup: the variation's own stored title, plus the parent product's |
| 1022 |
// id + name when we aren't already grouping by product. A variation |
| 1023 |
// belongs to exactly one product, so MAX(post_id)/MAX(post_title) is |
| 1024 |
// that single product's value per group — the join never fans out. |
| 1025 |
$selects[] = 'MAX(title) as variation_label'; |
| 1026 |
if (!$hasProduct) { |
| 1027 |
$selects[] = 'MAX(post_id) as vproduct_id'; |
| 1028 |
$selects[] = 'MAX(post_title) as product_title'; |
| 1029 |
} |
| 1030 |
} |
| 1031 |
if ($groupByOrderType) { |
| 1032 |
// Reuse the order_type -> column mapping from query-orders rather than |
| 1033 |
// hard-coding it again; qualify it with the join alias. |
| 1034 |
$selects[] = 'fctord.' . self::dimensionExpr('order_type') . ' as order_type'; |
| 1035 |
$groupCols[] = 'order_type'; |
| 1036 |
} |
| 1037 |
|
| 1038 |
// line_total = subtotal - discount_total on every item (see DiscountService/ |
| 1039 |
// CheckoutProcessor), so list_price_sum - line_revenue == discount_amount by |
| 1040 |
// construction: margin leakage is the gap between what was listed and what |
| 1041 |
// was charged, before refunds. |
| 1042 |
// |
| 1043 |
// Every item column below is qualified with the real (prefixed) items table. |
| 1044 |
// When order_type is grouped, fct_orders is joined and columns that exist on |
| 1045 |
// BOTH tables — subtotal is one — make a bare SUM(subtotal) throw SQL 1052 |
| 1046 |
// ("column is ambiguous"). selectRaw bypasses the grammar's table-prefixing, |
| 1047 |
// so the literal prefixed name (not the bare `fct_order_items`) is required. |
| 1048 |
// Qualifying all of them, not just subtotal, keeps a future column collision |
| 1049 |
// (or a new metric) from silently reintroducing the crash. |
| 1050 |
$itemsTable = App::db()->getTableName('fct_order_items'); |
| 1051 |
$metricSql = [ |
| 1052 |
'units_sold' => 'SUM(' . $itemsTable . '.quantity) as units_sold', |
| 1053 |
'line_revenue' => 'SUM(' . $itemsTable . '.line_total) as line_revenue', |
| 1054 |
'list_price_sum' => 'SUM(' . $itemsTable . '.subtotal) as list_price_sum', |
| 1055 |
'discount_amount' => 'SUM(' . $itemsTable . '.discount_total) as discount_amount', |
| 1056 |
'net_revenue' => 'SUM(' . $itemsTable . '.line_total - ' . $itemsTable . '.refund_total) as net_revenue', |
| 1057 |
'order_count' => 'COUNT(DISTINCT ' . $itemsTable . '.order_id) as order_count', |
| 1058 |
'refund_amount' => 'SUM(' . $itemsTable . '.refund_total) as refund_amount', |
| 1059 |
]; |
| 1060 |
foreach ($metrics as $m) { |
| 1061 |
if (isset($metricSql[$m])) { |
| 1062 |
$selects[] = $metricSql[$m]; |
| 1063 |
} |
| 1064 |
} |
| 1065 |
if (in_array('avg_unit_price', $metrics, true)) { |
| 1066 |
if (!in_array('line_revenue', $metrics, true)) { |
| 1067 |
$selects[] = $metricSql['line_revenue']; |
| 1068 |
} |
| 1069 |
if (!in_array('units_sold', $metrics, true)) { |
| 1070 |
$selects[] = $metricSql['units_sold']; |
| 1071 |
} |
| 1072 |
} |
| 1073 |
|
| 1074 |
$query->selectRaw(implode(', ', $selects)); |
| 1075 |
foreach ($groupCols as $g) { |
| 1076 |
$query->groupBy($g); |
| 1077 |
} |
| 1078 |
|
| 1079 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'line_revenue'; |
| 1080 |
if ($firstMetric === 'avg_unit_price') { |
| 1081 |
$firstMetric = 'line_revenue'; |
| 1082 |
} |
| 1083 |
$paging = self::queryPaging($params); |
| 1084 |
if ($groupCols && isset($metricSql[$firstMetric])) { |
| 1085 |
$query->orderBy($firstMetric, 'DESC'); |
| 1086 |
// Deterministic tie-break on the group key for stable offset paging. |
| 1087 |
foreach ($groupCols as $g) { |
| 1088 |
$query->orderBy($g, 'ASC'); |
| 1089 |
} |
| 1090 |
} |
| 1091 |
$query->limit($paging['per_page'] + 1)->offset($paging['offset']); |
| 1092 |
|
| 1093 |
$rows = $query->get(); |
| 1094 |
$fetched = count($rows); |
| 1095 |
$moneyMetrics = ['line_revenue', 'list_price_sum', 'discount_amount', 'net_revenue', 'refund_amount', 'avg_unit_price']; |
| 1096 |
|
| 1097 |
$out = []; |
| 1098 |
foreach ($rows as $row) { |
| 1099 |
if (count($out) >= $paging['per_page']) { |
| 1100 |
break; |
| 1101 |
} |
| 1102 |
$r = []; |
| 1103 |
if ($hasProduct) { |
| 1104 |
$r['product_id'] = (int) $row->post_id; |
| 1105 |
$r['product_name'] = $row->product_title; |
| 1106 |
// product_title kept as a backward-compatible alias of product_name. |
| 1107 |
$r['product_title'] = $row->product_title; |
| 1108 |
} elseif ($hasVariation) { |
| 1109 |
$r['product_id'] = (int) $row->vproduct_id; |
| 1110 |
$r['product_name'] = $row->product_title; |
| 1111 |
} |
| 1112 |
if ($hasVariation) { |
| 1113 |
$r['variation_id'] = (int) $row->object_id; |
| 1114 |
$r['variation_label'] = $row->variation_label; |
| 1115 |
} |
| 1116 |
if ($groupByOrderType) { |
| 1117 |
$r['order_type'] = $row->order_type; |
| 1118 |
} |
| 1119 |
foreach ($metrics as $m) { |
| 1120 |
if ($m === 'avg_unit_price') { |
| 1121 |
$u = (int) $row->units_sold; |
| 1122 |
$rev = (int) $row->line_revenue; |
| 1123 |
$r['avg_unit_price'] = MCPHelper::moneyCompact($u > 0 ? (int) round($rev / $u) : 0); |
| 1124 |
} elseif (in_array($m, $moneyMetrics, true)) { |
| 1125 |
$r[$m] = MCPHelper::moneyCompact((int) $row->{$m}); |
| 1126 |
} else { |
| 1127 |
$r[$m] = (int) $row->{$m}; |
| 1128 |
} |
| 1129 |
} |
| 1130 |
$out[] = $r; |
| 1131 |
} |
| 1132 |
|
| 1133 |
return MCPHelper::envelope( |
| 1134 |
sprintf( |
| 1135 |
/* translators: 1: row count, 2: metric list */ |
| 1136 |
__('%1$d rows — product metrics [%2$s].', 'fluent-cart'), |
| 1137 |
count($out), |
| 1138 |
implode(', ', $metrics) |
| 1139 |
), |
| 1140 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'range' => self::rangeBlock($range, $currency), 'rows' => $out], |
| 1141 |
array_merge(['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode], self::pageMeta($paging, $fetched)) |
| 1142 |
); |
| 1143 |
} |
| 1144 |
|
| 1145 |
public static function queryCustomers($params = []) |
| 1146 |
{ |
| 1147 |
$metrics = self::pickList($params, 'metrics', ['customer_count', 'total_ltv', 'avg_ltv', 'avg_purchase_count', 'repeat_customers'], ['customer_count', 'total_ltv']); |
| 1148 |
$dimensions = self::pickList($params, 'dimensions', ['country', 'state', 'status', 'first_purchase_month', 'last_purchase_month'], []); |
| 1149 |
|
| 1150 |
$query = Customer::query(); |
| 1151 |
if (!empty($params['country'])) { |
| 1152 |
$query->where('country', sanitize_text_field($params['country'])); |
| 1153 |
} |
| 1154 |
if (!empty($params['status'])) { |
| 1155 |
$query->where('status', sanitize_text_field($params['status'])); |
| 1156 |
} |
| 1157 |
if (isset($params['min_ltv'])) { |
| 1158 |
$query->where('ltv', '>=', Helper::toCent($params['min_ltv'])); |
| 1159 |
} |
| 1160 |
if (isset($params['min_purchase_count'])) { |
| 1161 |
$query->where('purchase_count', '>=', (int) $params['min_purchase_count']); |
| 1162 |
} |
| 1163 |
|
| 1164 |
$selects = []; |
| 1165 |
$groupCols = []; |
| 1166 |
foreach ($dimensions as $dim) { |
| 1167 |
if ($dim === 'country' || $dim === 'state') { |
| 1168 |
// Coalesce NULL and '' into a single 'unknown' bucket. Group by the |
| 1169 |
// expression (not the alias, which would resolve to the raw column |
| 1170 |
// and keep null/'' split). |
| 1171 |
$expr = "COALESCE(NULLIF($dim, ''), 'unknown')"; |
| 1172 |
$selects[] = "$expr as $dim"; |
| 1173 |
$groupCols[] = $expr; |
| 1174 |
} elseif ($dim === 'status') { |
| 1175 |
$selects[] = $dim; |
| 1176 |
$groupCols[] = $dim; |
| 1177 |
} elseif ($dim === 'first_purchase_month') { |
| 1178 |
$selects[] = "DATE_FORMAT(first_purchase_date, '%Y-%m') as first_purchase_month"; |
| 1179 |
$groupCols[] = "DATE_FORMAT(first_purchase_date, '%Y-%m')"; |
| 1180 |
} elseif ($dim === 'last_purchase_month') { |
| 1181 |
$selects[] = "DATE_FORMAT(last_purchase_date, '%Y-%m') as last_purchase_month"; |
| 1182 |
$groupCols[] = "DATE_FORMAT(last_purchase_date, '%Y-%m')"; |
| 1183 |
} |
| 1184 |
} |
| 1185 |
|
| 1186 |
$metricSql = [ |
| 1187 |
'customer_count' => 'COUNT(*) as customer_count', |
| 1188 |
'total_ltv' => 'SUM(ltv) as total_ltv', |
| 1189 |
'avg_ltv' => 'AVG(ltv) as avg_ltv', |
| 1190 |
'avg_purchase_count' => 'AVG(purchase_count) as avg_purchase_count', |
| 1191 |
'repeat_customers' => 'SUM(CASE WHEN purchase_count > 1 THEN 1 ELSE 0 END) as repeat_customers', |
| 1192 |
]; |
| 1193 |
foreach ($metrics as $m) { |
| 1194 |
if (isset($metricSql[$m])) { |
| 1195 |
$selects[] = $metricSql[$m]; |
| 1196 |
} |
| 1197 |
} |
| 1198 |
|
| 1199 |
$query->selectRaw(implode(', ', $selects)); |
| 1200 |
if ($groupCols) { |
| 1201 |
$query->groupByRaw(implode(', ', $groupCols)); |
| 1202 |
} |
| 1203 |
|
| 1204 |
$paging = self::queryPaging($params); |
| 1205 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'customer_count'; |
| 1206 |
if ($groupCols && isset($metricSql[$firstMetric])) { |
| 1207 |
$query->orderBy($firstMetric, 'DESC'); |
| 1208 |
// Deterministic tie-break on the grouped dimensions (their aliases) |
| 1209 |
// so offset paging is stable across pages. |
| 1210 |
foreach ($dimensions as $d) { |
| 1211 |
$query->orderBy($d, 'ASC'); |
| 1212 |
} |
| 1213 |
} |
| 1214 |
$query->limit($paging['per_page'] + 1)->offset($paging['offset']); |
| 1215 |
|
| 1216 |
$rows = $query->get(); |
| 1217 |
$fetched = count($rows); |
| 1218 |
$moneyMetrics = ['total_ltv', 'avg_ltv']; |
| 1219 |
|
| 1220 |
$out = []; |
| 1221 |
foreach ($rows as $row) { |
| 1222 |
if (count($out) >= $paging['per_page']) { |
| 1223 |
break; |
| 1224 |
} |
| 1225 |
$r = []; |
| 1226 |
foreach ($dimensions as $dim) { |
| 1227 |
$r[$dim] = $row->{$dim}; |
| 1228 |
} |
| 1229 |
foreach ($metrics as $m) { |
| 1230 |
if (in_array($m, $moneyMetrics, true)) { |
| 1231 |
$r[$m] = MCPHelper::moneyCompact((int) round((float) $row->{$m})); |
| 1232 |
} elseif ($m === 'avg_purchase_count') { |
| 1233 |
$r[$m] = round((float) $row->{$m}, 2); |
| 1234 |
} else { |
| 1235 |
$r[$m] = (int) $row->{$m}; |
| 1236 |
} |
| 1237 |
} |
| 1238 |
$out[] = $r; |
| 1239 |
} |
| 1240 |
|
| 1241 |
return MCPHelper::envelope( |
| 1242 |
sprintf( |
| 1243 |
/* translators: 1: row count, 2: metric list */ |
| 1244 |
__('%1$d rows — customer metrics [%2$s].', 'fluent-cart'), |
| 1245 |
count($out), |
| 1246 |
implode(', ', $metrics) |
| 1247 |
), |
| 1248 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'rows' => $out], |
| 1249 |
array_merge(['currency' => MCPHelper::currencyCode(), 'note' => 'LTV is in store currency; customers are not currency-scoped.'], self::pageMeta($paging, $fetched)) |
| 1250 |
); |
| 1251 |
} |
| 1252 |
|
| 1253 |
// ----------------------------------------------------------------- |
| 1254 |
// query-subscriptions (flexible aggregate) |
| 1255 |
// ----------------------------------------------------------------- |
| 1256 |
|
| 1257 |
public static function querySubscriptions($params = []) |
| 1258 |
{ |
| 1259 |
$range = self::resolveRange($params); |
| 1260 |
$dateBasis = self::subDateBasis($params); |
| 1261 |
$metrics = self::pickList($params, 'metrics', ['subscription_count', 'contract_value', 'recurring_value'], ['subscription_count', 'contract_value']); |
| 1262 |
$dimensions = self::pickList($params, 'dimensions', ['month', 'plan_type', 'status', 'billing_interval'], []); |
| 1263 |
|
| 1264 |
// Same UTC window resolution as every other report; the chosen date_basis |
| 1265 |
// is the only thing that varies (signup cohort vs churn vs upcoming). |
| 1266 |
$query = Subscription::query() |
| 1267 |
->where($dateBasis, '>=', $range['start']) |
| 1268 |
->where($dateBasis, '<=', $range['end']); |
| 1269 |
|
| 1270 |
if (!empty($params['product_id'])) { |
| 1271 |
$query->where('product_id', (int) $params['product_id']); |
| 1272 |
} |
| 1273 |
if (!empty($params['status'])) { |
| 1274 |
$query->where('status', sanitize_text_field($params['status'])); |
| 1275 |
} |
| 1276 |
$planType = isset($params['plan_type']) && in_array($params['plan_type'], ['installment', 'recurring'], true) ? $params['plan_type'] : 'all'; |
| 1277 |
if ($planType !== 'all') { |
| 1278 |
$query->ofPlanType($planType); |
| 1279 |
} |
| 1280 |
|
| 1281 |
// plan_type is derived from bill_times with the SAME threshold as |
| 1282 |
// Subscription::isInstallment(), so the SQL and PHP definitions agree. |
| 1283 |
$planExpr = "CASE WHEN bill_times > 0 THEN 'installment' ELSE 'recurring' END"; |
| 1284 |
|
| 1285 |
$selects = []; |
| 1286 |
$groupExpr = []; |
| 1287 |
foreach ($dimensions as $dim) { |
| 1288 |
if ($dim === 'month') { |
| 1289 |
$expr = "DATE_FORMAT($dateBasis, '%Y-%m')"; |
| 1290 |
} elseif ($dim === 'plan_type') { |
| 1291 |
$expr = $planExpr; |
| 1292 |
} else { |
| 1293 |
// status / billing_interval — plain columns. |
| 1294 |
$expr = $dim; |
| 1295 |
} |
| 1296 |
$selects[] = $expr . ' as ' . $dim; |
| 1297 |
$groupExpr[] = $expr; |
| 1298 |
} |
| 1299 |
|
| 1300 |
// contract_value is the SUM form of Subscription::totalContractValue() |
| 1301 |
// (recurring_total * bill_times) — installments booked at full committed |
| 1302 |
// value, 0 for open-ended plans. No parallel money math. |
| 1303 |
$metricSql = [ |
| 1304 |
'subscription_count' => 'COUNT(*) as subscription_count', |
| 1305 |
'contract_value' => 'SUM(recurring_total * bill_times) as contract_value', |
| 1306 |
'recurring_value' => 'SUM(recurring_total) as recurring_value', |
| 1307 |
]; |
| 1308 |
foreach ($metrics as $m) { |
| 1309 |
if (isset($metricSql[$m])) { |
| 1310 |
$selects[] = $metricSql[$m]; |
| 1311 |
} |
| 1312 |
} |
| 1313 |
|
| 1314 |
$query->selectRaw(implode(', ', $selects)); |
| 1315 |
if ($groupExpr) { |
| 1316 |
$query->groupByRaw(implode(', ', $groupExpr)); |
| 1317 |
} |
| 1318 |
|
| 1319 |
$paging = self::queryPaging($params); |
| 1320 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'subscription_count'; |
| 1321 |
if ($groupExpr && isset($metricSql[$firstMetric])) { |
| 1322 |
$query->orderBy($firstMetric, 'DESC'); |
| 1323 |
// Deterministic tie-break on the grouped dimensions (their aliases). |
| 1324 |
foreach ($dimensions as $d) { |
| 1325 |
$query->orderBy($d, 'ASC'); |
| 1326 |
} |
| 1327 |
} |
| 1328 |
$query->limit($paging['per_page'] + 1)->offset($paging['offset']); |
| 1329 |
|
| 1330 |
$rows = $query->get(); |
| 1331 |
$fetched = count($rows); |
| 1332 |
$moneyMetrics = ['contract_value', 'recurring_value']; |
| 1333 |
|
| 1334 |
$out = []; |
| 1335 |
foreach ($rows as $row) { |
| 1336 |
if (count($out) >= $paging['per_page']) { |
| 1337 |
break; |
| 1338 |
} |
| 1339 |
$r = []; |
| 1340 |
foreach ($dimensions as $dim) { |
| 1341 |
$r[$dim] = $row->{$dim}; |
| 1342 |
} |
| 1343 |
foreach ($metrics as $m) { |
| 1344 |
if (in_array($m, $moneyMetrics, true)) { |
| 1345 |
$r[$m] = MCPHelper::moneyCompact((int) $row->{$m}); |
| 1346 |
} else { |
| 1347 |
$r[$m] = (int) $row->{$m}; |
| 1348 |
} |
| 1349 |
} |
| 1350 |
$out[] = $r; |
| 1351 |
} |
| 1352 |
|
| 1353 |
return MCPHelper::envelope( |
| 1354 |
sprintf( |
| 1355 |
/* translators: 1: row count, 2: metric list, 3: dimension list */ |
| 1356 |
__('%1$d rows — subscription metrics [%2$s] grouped by [%3$s].', 'fluent-cart'), |
| 1357 |
count($out), |
| 1358 |
implode(', ', $metrics), |
| 1359 |
$dimensions ? implode(', ', $dimensions) : __('total', 'fluent-cart') |
| 1360 |
), |
| 1361 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'date_basis' => $dateBasis, 'range' => self::rangeBlock($range, MCPHelper::currencyCode()), 'rows' => $out], |
| 1362 |
array_merge([ |
| 1363 |
'currency' => MCPHelper::currencyCode(), |
| 1364 |
'date_basis' => $dateBasis, |
| 1365 |
'note' => 'Money is in the store currency; subscriptions are not currency-scoped. contract_value books installments at recurring_total x bill_times; completed installments are paid-in-full, not churn.', |
| 1366 |
], self::pageMeta($paging, $fetched)) |
| 1367 |
); |
| 1368 |
} |
| 1369 |
|
| 1370 |
private static function subDateBasis($params) |
| 1371 |
{ |
| 1372 |
$allowed = ['created_at', 'canceled_at', 'next_billing_date']; |
| 1373 |
|
| 1374 |
return isset($params['date_basis']) && in_array($params['date_basis'], $allowed, true) ? $params['date_basis'] : 'created_at'; |
| 1375 |
} |
| 1376 |
|
| 1377 |
/** |
| 1378 |
* Restrict an Order-model aggregate to orders CONTAINING a given product / |
| 1379 |
* variation. Uses whereHas on order_items (post_id = product, object_id = |
| 1380 |
* variation) so the filter is a subquery, not a join — order-level metrics stay |
| 1381 |
* per-order and never fan out. Both filters combine (AND) when both are given. |
| 1382 |
*/ |
| 1383 |
private static function applyOrderItemFilter($query, $params) |
| 1384 |
{ |
| 1385 |
foreach (['product_id' => 'post_id', 'variation_id' => 'object_id'] as $param => $col) { |
| 1386 |
if (!empty($params[$param])) { |
| 1387 |
$val = (int) $params[$param]; |
| 1388 |
$query->whereHas('order_items', function ($q) use ($col, $val) { |
| 1389 |
$q->where($col, $val); |
| 1390 |
}); |
| 1391 |
} |
| 1392 |
} |
| 1393 |
} |
| 1394 |
|
| 1395 |
private static function dimensionExpr($dim) |
| 1396 |
{ |
| 1397 |
if ($dim === 'day') { |
| 1398 |
return "DATE_FORMAT(created_at, '%Y-%m-%d')"; |
| 1399 |
} |
| 1400 |
if ($dim === 'week') { |
| 1401 |
return "DATE_FORMAT(created_at, '%x-W%v')"; |
| 1402 |
} |
| 1403 |
if ($dim === 'month') { |
| 1404 |
return "DATE_FORMAT(created_at, '%Y-%m')"; |
| 1405 |
} |
| 1406 |
if ($dim === 'order_type') { |
| 1407 |
// The order-type values (payment | renewal | subscription) live on the |
| 1408 |
// fct_orders.type column; expose it under the order_type alias so the |
| 1409 |
// dimension name and response key read naturally and don't collide |
| 1410 |
// with the unrelated payment_type on line items. |
| 1411 |
return 'type'; |
| 1412 |
} |
| 1413 |
return $dim; |
| 1414 |
} |
| 1415 |
|
| 1416 |
// ----------------------------------------------------------------- |
| 1417 |
// shared helpers |
| 1418 |
// ----------------------------------------------------------------- |
| 1419 |
|
| 1420 |
private static function currency($params) |
| 1421 |
{ |
| 1422 |
if (!empty($params['currency'])) { |
| 1423 |
return strtoupper(sanitize_text_field($params['currency'])); |
| 1424 |
} |
| 1425 |
return MCPHelper::currencyCode(); |
| 1426 |
} |
| 1427 |
|
| 1428 |
/** |
| 1429 |
* Effective order-mode filter: 'live', 'test', or 'all'. Reports have always |
| 1430 |
* counted BOTH live and test orders, so 'all' (the default) keeps existing |
| 1431 |
* numbers unchanged; an agent opts into 'live' for clean revenue. Applied to |
| 1432 |
* the fct_orders.mode column. |
| 1433 |
*/ |
| 1434 |
private static function orderMode($params) |
| 1435 |
{ |
| 1436 |
$m = isset($params['mode']) ? strtolower(sanitize_text_field((string) $params['mode'])) : 'all'; |
| 1437 |
return in_array($m, ['live', 'test'], true) ? $m : 'all'; |
| 1438 |
} |
| 1439 |
|
| 1440 |
/** |
| 1441 |
* Apply the mode filter to an Order query (or an order-relation subquery / |
| 1442 |
* whereHas closure). 'all' is a no-op so existing numbers are unchanged. The |
| 1443 |
* column is fct_orders.mode; pass a qualified name via $column when the orders |
| 1444 |
* table is aliased (e.g. query-sources uses 'o.mode'). |
| 1445 |
*/ |
| 1446 |
private static function applyMode($query, $mode, $column = 'mode') |
| 1447 |
{ |
| 1448 |
if ($mode !== 'all') { |
| 1449 |
$query->where($column, $mode); |
| 1450 |
} |
| 1451 |
return $query; |
| 1452 |
} |
| 1453 |
|
| 1454 |
/** |
| 1455 |
* Resolve range/start/end into a UTC window plus the prior equal-length |
| 1456 |
* window. Relative ranges are computed in store timezone, expressed in UTC. |
| 1457 |
* |
| 1458 |
* Public so the single source of truth for MCP date-window resolution is |
| 1459 |
* shared (e.g. list-transactions) instead of duplicated — every tool then |
| 1460 |
* accepts the identical range vocabulary and UTC semantics. |
| 1461 |
*/ |
| 1462 |
public static function resolveRange($params) |
| 1463 |
{ |
| 1464 |
// Resolve windows in UTC to match FluentCart's own admin reports, which |
| 1465 |
// bucket on the GMT-stored created_at (DATE_FORMAT(created_at, ...)) with |
| 1466 |
// no timezone conversion. Using store-local boundaries here would make a |
| 1467 |
// local day straddle two UTC dates and emit an extra trailing bucket. |
| 1468 |
$tz = new \DateTimeZone('UTC'); |
| 1469 |
|
| 1470 |
// Delta mode: everything strictly after an instant, up to now. Time-precise |
| 1471 |
// (not snapped to a day) so "what changed since 14:05" works during a launch. |
| 1472 |
if (!empty($params['since'])) { |
| 1473 |
$start = self::instant($params['since'], $tz, false); |
| 1474 |
if ($start !== null) { |
| 1475 |
return self::withPrior($start, gmdate('Y-m-d H:i:s'), 'since'); |
| 1476 |
} |
| 1477 |
} |
| 1478 |
|
| 1479 |
// Time-precise custom window (ISO 8601). A time of day is kept; a date-only |
| 1480 |
// value snaps to the day edge. Overrides range and the legacy start/end_date. |
| 1481 |
if (!empty($params['date_from']) || !empty($params['date_to'])) { |
| 1482 |
$start = self::instant(!empty($params['date_from']) ? $params['date_from'] : '-30 days', $tz, false); |
| 1483 |
$end = self::instant(!empty($params['date_to']) ? $params['date_to'] : 'now', $tz, true); |
| 1484 |
if ($start !== null && $end !== null) { |
| 1485 |
return self::withPrior($start, $end, 'custom'); |
| 1486 |
} |
| 1487 |
} |
| 1488 |
|
| 1489 |
if (!empty($params['start_date']) || !empty($params['end_date'])) { |
| 1490 |
$start = self::dayStart(!empty($params['start_date']) ? $params['start_date'] : '-30 days', $tz); |
| 1491 |
$end = self::dayEnd(!empty($params['end_date']) ? $params['end_date'] : 'now', $tz); |
| 1492 |
return self::withPrior($start, $end, !empty($params['start_date']) ? 'custom' : 'last_30_days'); |
| 1493 |
} |
| 1494 |
|
| 1495 |
$range = isset($params['range']) && in_array($params['range'], self::RANGES, true) ? $params['range'] : 'last_30_days'; |
| 1496 |
|
| 1497 |
// since_launch (alias: all_time): the store's first paid order to now. |
| 1498 |
// Needs a DB read, so it sits here rather than in the pure calendar math |
| 1499 |
// below. Falls back to the last 30 days if the store has no paid orders |
| 1500 |
// yet. Both names resolve identically — for a paid-order-scoped report the |
| 1501 |
// first paid order IS the start of all data — so an agent that learned |
| 1502 |
// all_time from get-product-financials succeeds here too. The label echoes |
| 1503 |
// whichever name was requested. |
| 1504 |
if ($range === 'since_launch' || $range === 'all_time') { |
| 1505 |
$launch = self::storeLaunchDate(); |
| 1506 |
$start = $launch ? $launch : self::dayStart('-30 days', $tz); |
| 1507 |
return self::withPrior($start, gmdate('Y-m-d H:i:s'), $range); |
| 1508 |
} |
| 1509 |
|
| 1510 |
$now = new \DateTime('now', $tz); |
| 1511 |
$startDt = clone $now; |
| 1512 |
$endDt = clone $now; |
| 1513 |
// Set for calendar-bounded ranges to force a calendar-aligned prior period. |
| 1514 |
$prevStartDt = null; |
| 1515 |
$prevEndDt = null; |
| 1516 |
|
| 1517 |
if ($range === 'yesterday') { |
| 1518 |
$startDt->modify('-1 day'); |
| 1519 |
$endDt->modify('-1 day'); |
| 1520 |
} elseif ($range === 'last_7_days') { |
| 1521 |
$startDt->modify('-6 days'); |
| 1522 |
} elseif ($range === 'last_30_days') { |
| 1523 |
$startDt->modify('-29 days'); |
| 1524 |
} elseif ($range === 'this_month' || $range === 'mtd') { |
| 1525 |
$startDt = new \DateTime($now->format('Y-m-01'), $tz); |
| 1526 |
} elseif ($range === 'last_month') { |
| 1527 |
$startDt = new \DateTime($now->format('Y-m-01'), $tz); |
| 1528 |
$startDt->modify('-1 month'); |
| 1529 |
$endDt = (clone $startDt)->modify('last day of this month'); |
| 1530 |
// Prior = the full calendar month before last month. |
| 1531 |
$prevStartDt = (clone $startDt)->modify('-1 month'); |
| 1532 |
$prevEndDt = (clone $prevStartDt)->modify('last day of this month'); |
| 1533 |
} elseif ($range === 'qtd') { |
| 1534 |
$startDt = self::quarterStart($now, $tz); |
| 1535 |
} elseif ($range === 'last_quarter') { |
| 1536 |
$qs = self::quarterStart($now, $tz); |
| 1537 |
$startDt = (clone $qs)->modify('-3 months'); |
| 1538 |
$endDt = (clone $qs)->modify('-1 day'); |
| 1539 |
// Prior = the full calendar quarter before last quarter. |
| 1540 |
$prevStartDt = (clone $startDt)->modify('-3 months'); |
| 1541 |
$prevEndDt = (clone $startDt)->modify('-1 day'); |
| 1542 |
} elseif ($range === 'ytd') { |
| 1543 |
$startDt = new \DateTime($now->format('Y-01-01'), $tz); |
| 1544 |
} elseif ($range === 'last_year') { |
| 1545 |
$year = (int) $now->format('Y') - 1; |
| 1546 |
$startDt = new \DateTime($year . '-01-01', $tz); |
| 1547 |
$endDt = new \DateTime($year . '-12-31', $tz); |
| 1548 |
// Prior = the full calendar year before last year. |
| 1549 |
$prevStartDt = new \DateTime(($year - 1) . '-01-01', $tz); |
| 1550 |
$prevEndDt = new \DateTime(($year - 1) . '-12-31', $tz); |
| 1551 |
} |
| 1552 |
|
| 1553 |
$start = self::dayStart($startDt->format('Y-m-d'), $tz); |
| 1554 |
$end = self::dayEnd($endDt->format('Y-m-d'), $tz); |
| 1555 |
|
| 1556 |
if ($prevStartDt !== null && $prevEndDt !== null) { |
| 1557 |
return self::withPrior( |
| 1558 |
$start, |
| 1559 |
$end, |
| 1560 |
$range, |
| 1561 |
self::dayStart($prevStartDt->format('Y-m-d'), $tz), |
| 1562 |
self::dayEnd($prevEndDt->format('Y-m-d'), $tz) |
| 1563 |
); |
| 1564 |
} |
| 1565 |
|
| 1566 |
return self::withPrior($start, $end, $range); |
| 1567 |
} |
| 1568 |
|
| 1569 |
private static function quarterStart($now, $tz) |
| 1570 |
{ |
| 1571 |
$month = (int) $now->format('n'); |
| 1572 |
$qStartMonth = (int) (floor(($month - 1) / 3) * 3 + 1); |
| 1573 |
return new \DateTime($now->format('Y') . '-' . str_pad($qStartMonth, 2, '0', STR_PAD_LEFT) . '-01', $tz); |
| 1574 |
} |
| 1575 |
|
| 1576 |
private static function withPrior($startUtc, $endUtc, $label, $prevStartUtc = null, $prevEndUtc = null) |
| 1577 |
{ |
| 1578 |
// Calendar-bounded ranges (last_month/last_quarter/last_year) pass an |
| 1579 |
// explicit prior *calendar* period so a 31-day month isn't compared to a |
| 1580 |
// 28-day second-count window. Other ranges fall back to an equal-length |
| 1581 |
// block ending 1s before start (exact for fixed-length, rolling, custom). |
| 1582 |
if ($prevStartUtc !== null && $prevEndUtc !== null) { |
| 1583 |
return [ |
| 1584 |
'start' => $startUtc, |
| 1585 |
'end' => $endUtc, |
| 1586 |
'prev_start' => $prevStartUtc, |
| 1587 |
'prev_end' => $prevEndUtc, |
| 1588 |
'label' => $label, |
| 1589 |
]; |
| 1590 |
} |
| 1591 |
|
| 1592 |
$s = new \DateTime($startUtc, new \DateTimeZone('UTC')); |
| 1593 |
$e = new \DateTime($endUtc, new \DateTimeZone('UTC')); |
| 1594 |
$lengthSec = $e->getTimestamp() - $s->getTimestamp(); |
| 1595 |
|
| 1596 |
$prevEnd = (clone $s)->modify('-1 second'); |
| 1597 |
$prevStart = (clone $prevEnd)->modify('-' . ($lengthSec + 1) . ' seconds'); |
| 1598 |
|
| 1599 |
return [ |
| 1600 |
'start' => $startUtc, |
| 1601 |
'end' => $endUtc, |
| 1602 |
'prev_start' => $prevStart->format('Y-m-d H:i:s'), |
| 1603 |
'prev_end' => $prevEnd->format('Y-m-d H:i:s'), |
| 1604 |
'label' => $label, |
| 1605 |
]; |
| 1606 |
} |
| 1607 |
|
| 1608 |
/** |
| 1609 |
* Parse an ISO-8601 / relative value to a UTC 'Y-m-d H:i:s'. A date-only input |
| 1610 |
* (YYYY-MM-DD) is snapped to the day start (or end when $isEnd); an explicit |
| 1611 |
* time is preserved. Returns null on an unparseable value so the caller can |
| 1612 |
* fall back to the next window source rather than silently matching all rows. |
| 1613 |
*/ |
| 1614 |
private static function instant($value, $tz, $isEnd = false) |
| 1615 |
{ |
| 1616 |
try { |
| 1617 |
$dt = new \DateTime((string) $value, $tz); |
| 1618 |
} catch (\Exception $e) { |
| 1619 |
return null; |
| 1620 |
} |
| 1621 |
if (preg_match('/^\d{4}-\d{2}-\d{2}$/', trim((string) $value))) { |
| 1622 |
$dt->setTime($isEnd ? 23 : 0, $isEnd ? 59 : 0, $isEnd ? 59 : 0); |
| 1623 |
} |
| 1624 |
$dt->setTimezone(new \DateTimeZone('UTC')); |
| 1625 |
return $dt->format('Y-m-d H:i:s'); |
| 1626 |
} |
| 1627 |
|
| 1628 |
/** |
| 1629 |
* The store's first paid order timestamp (UTC); null if the store has no paid |
| 1630 |
* orders yet. Only hit on the since_launch path (one indexed min per call), so |
| 1631 |
* it is not memoized — a static cache would leak across calls in a long-lived |
| 1632 |
* process (e.g. the test runner) for no real per-request gain. |
| 1633 |
*/ |
| 1634 |
private static function storeLaunchDate() |
| 1635 |
{ |
| 1636 |
$min = Order::query()->whereIn('payment_status', self::PAID)->min('created_at'); |
| 1637 |
return ($min && strpos((string) $min, '0000-00-00') !== 0) ? (string) $min : null; |
| 1638 |
} |
| 1639 |
|
| 1640 |
private static function dayStart($value, $tz) |
| 1641 |
{ |
| 1642 |
try { |
| 1643 |
$dt = new \DateTime((string) $value, $tz); |
| 1644 |
} catch (\Exception $e) { |
| 1645 |
$dt = new \DateTime('now', $tz); |
| 1646 |
} |
| 1647 |
$dt->setTime(0, 0, 0); |
| 1648 |
$dt->setTimezone(new \DateTimeZone('UTC')); |
| 1649 |
return $dt->format('Y-m-d H:i:s'); |
| 1650 |
} |
| 1651 |
|
| 1652 |
private static function dayEnd($value, $tz) |
| 1653 |
{ |
| 1654 |
try { |
| 1655 |
$dt = new \DateTime((string) $value, $tz); |
| 1656 |
} catch (\Exception $e) { |
| 1657 |
$dt = new \DateTime('now', $tz); |
| 1658 |
} |
| 1659 |
$dt->setTime(23, 59, 59); |
| 1660 |
$dt->setTimezone(new \DateTimeZone('UTC')); |
| 1661 |
return $dt->format('Y-m-d H:i:s'); |
| 1662 |
} |
| 1663 |
|
| 1664 |
private static function rangeBlock($range, $currency) |
| 1665 |
{ |
| 1666 |
return [ |
| 1667 |
'start' => MCPHelper::toIso8601($range['start']), |
| 1668 |
'end' => MCPHelper::toIso8601($range['end']), |
| 1669 |
'label' => $range['label'], |
| 1670 |
'currency' => $currency, |
| 1671 |
]; |
| 1672 |
} |
| 1673 |
|
| 1674 |
private static function pickList($params, $key, array $allowed, array $default) |
| 1675 |
{ |
| 1676 |
if (empty($params[$key]) || !is_array($params[$key])) { |
| 1677 |
return $default; |
| 1678 |
} |
| 1679 |
$out = []; |
| 1680 |
foreach ($params[$key] as $v) { |
| 1681 |
if (in_array($v, $allowed, true) && !in_array($v, $out, true)) { |
| 1682 |
$out[] = $v; |
| 1683 |
} |
| 1684 |
} |
| 1685 |
return $out ? $out : $default; |
| 1686 |
} |
| 1687 |
|
| 1688 |
/** |
| 1689 |
* Page/offset for the query-* aggregates. per_page defaults to and is clamped |
| 1690 |
* at MAX_ROWS — grouped rows are compact but a single page still can't exceed |
| 1691 |
* the context guardrail. Returns page, per_page and the row offset. |
| 1692 |
*/ |
| 1693 |
private static function queryPaging($params) |
| 1694 |
{ |
| 1695 |
$page = isset($params['page']) ? max(1, (int) $params['page']) : 1; |
| 1696 |
$perPage = isset($params['per_page']) ? (int) $params['per_page'] : self::MAX_ROWS; |
| 1697 |
if ($perPage < 1 || $perPage > self::MAX_ROWS) { |
| 1698 |
$perPage = self::MAX_ROWS; |
| 1699 |
} |
| 1700 |
return ['page' => $page, 'per_page' => $perPage, 'offset' => ($page - 1) * $perPage]; |
| 1701 |
} |
| 1702 |
|
| 1703 |
/** |
| 1704 |
* meta.page block for a query-* aggregate. The query fetches per_page + 1 rows |
| 1705 |
* to peek past the page boundary; $fetchedCount is that raw count. `truncated` |
| 1706 |
* is kept (never removed — additive API rule) and now means "more rows exist |
| 1707 |
* beyond this page"; raise `page` to fetch them. |
| 1708 |
*/ |
| 1709 |
private static function pageMeta($paging, $fetchedCount) |
| 1710 |
{ |
| 1711 |
$hasMore = $fetchedCount > $paging['per_page']; |
| 1712 |
return [ |
| 1713 |
'page' => [ |
| 1714 |
'current' => $paging['page'], |
| 1715 |
'per_page' => $paging['per_page'], |
| 1716 |
'has_more' => $hasMore, |
| 1717 |
], |
| 1718 |
'truncated' => $hasMore, |
| 1719 |
]; |
| 1720 |
} |
| 1721 |
|
| 1722 |
private static function pct($current, $prior) |
| 1723 |
{ |
| 1724 |
if ($prior == 0) { |
| 1725 |
return $current == 0 ? 0 : null; |
| 1726 |
} |
| 1727 |
return round((($current - $prior) / abs($prior)) * 100, 2); |
| 1728 |
} |
| 1729 |
} |
| 1730 |
|
