| 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, revenue by order_type (one-time payment vs new subscription vs renewal), or revenue by country. product_id or variation_id limits to orders containing that product. This is the tool for revenue BY GEOGRAPHY: group by country/state, which read each order\'s own billing address, falling back to the buyer\'s primary billing address. 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. country/state come from each order\'s own billing address, falling back to the buyer\'s primary billing address when the order has no address row of its own (common for digital goods) — this is the right basis for revenue by geography. Orders neither source can place land in an explicit "unknown" bucket so the rows still sum to total revenue; see meta.geo_source.', |
| 254 |
'items' => ['type' => 'string', 'enum' => ['day', 'week', 'month', 'status', 'payment_status', 'order_type', 'country', 'state']], |
| 255 |
], |
| 256 |
'country' => ['type' => 'string', 'description' => 'ISO-2 code. Limit to orders billed to this country, resolved the same way as the country dimension (order address, then the buyer\'s primary billing address).'], |
| 257 |
'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.'], |
| 258 |
'variation_id' => ['type' => 'integer', 'description' => 'Limit to orders containing this variation.'], |
| 259 |
'range' => $rangeProp, |
| 260 |
'start_date' => ['type' => 'string'], |
| 261 |
'end_date' => ['type' => 'string'], |
| 262 |
'date_from' => $dateFrom, |
| 263 |
'date_to' => $dateTo, |
| 264 |
'since' => $since, |
| 265 |
'currency' => ['type' => 'string'], |
| 266 |
'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.'], |
| 267 |
], |
| 268 |
], |
| 269 |
'execute_callback' => [self::class, 'queryOrders'], |
| 270 |
'permission_callback' => function () { |
| 271 |
return PermissionGate::can('reports/view'); |
| 272 |
}, |
| 273 |
'annotations' => ['readonly' => true], |
| 274 |
], |
| 275 |
|
| 276 |
'fluent-cart/query-products' => [ |
| 277 |
'label' => __('Query Products (flexible aggregate)', 'fluent-cart'), |
| 278 |
'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'), |
| 279 |
'input_schema' => [ |
| 280 |
'type' => 'object', |
| 281 |
'properties' => [ |
| 282 |
'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']]], |
| 283 |
'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']]], |
| 284 |
'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.'], |
| 285 |
'variation_id' => ['type' => 'integer', 'description' => 'Limit to one variation of the product.'], |
| 286 |
'range' => $rangeProp, |
| 287 |
'start_date' => ['type' => 'string'], |
| 288 |
'end_date' => ['type' => 'string'], |
| 289 |
'date_from' => $dateFrom, |
| 290 |
'date_to' => $dateTo, |
| 291 |
'since' => $since, |
| 292 |
'currency' => ['type' => 'string'], |
| 293 |
], |
| 294 |
], |
| 295 |
'execute_callback' => [self::class, 'queryProducts'], |
| 296 |
'permission_callback' => function () { |
| 297 |
return PermissionGate::can('reports/view'); |
| 298 |
}, |
| 299 |
'annotations' => ['readonly' => true], |
| 300 |
], |
| 301 |
|
| 302 |
'fluent-cart/query-customers' => [ |
| 303 |
'label' => __('Query Customers (flexible aggregate)', 'fluent-cart'), |
| 304 |
'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. country/state resolve from the customer\'s primary billing address, falling back to their profile location, then "unknown" — this is where a customer is REGISTERED, captured at their first purchase and not refreshed since. To attribute revenue to where each order was actually billed, group query-orders by country instead.', 'fluent-cart'), |
| 305 |
'input_schema' => [ |
| 306 |
'type' => 'object', |
| 307 |
'properties' => [ |
| 308 |
'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']]], |
| 309 |
'dimensions' => ['type' => 'array', 'items' => ['type' => 'string', 'enum' => ['country', 'state', 'status', 'first_purchase_month', 'last_purchase_month']]], |
| 310 |
'country' => ['type' => 'string', 'description' => 'ISO-2 code, matched against the customer profile country (same caveat as the country dimension).'], |
| 311 |
'status' => ['type' => 'string', 'enum' => ['active', 'archived'], 'description' => 'Not every customer has one set — customers with no status appear under the status dimension as "unknown" and are excluded by this filter. Omit it to count all customers.'], |
| 312 |
'min_ltv' => ['type' => 'number', 'description' => 'Minimum LTV in store currency.'], |
| 313 |
'min_purchase_count' => ['type' => 'integer'], |
| 314 |
], |
| 315 |
], |
| 316 |
'execute_callback' => [self::class, 'queryCustomers'], |
| 317 |
'permission_callback' => function () { |
| 318 |
return PermissionGate::can('reports/view'); |
| 319 |
}, |
| 320 |
'annotations' => ['readonly' => true], |
| 321 |
], |
| 322 |
|
| 323 |
'fluent-cart/query-subscriptions' => [ |
| 324 |
'label' => __('Query Subscriptions (flexible aggregate)', 'fluent-cart'), |
| 325 |
'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'), |
| 326 |
'input_schema' => [ |
| 327 |
'type' => 'object', |
| 328 |
'properties' => [ |
| 329 |
'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']]], |
| 330 |
'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']]], |
| 331 |
'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.'], |
| 332 |
'plan_type' => ['type' => 'string', 'enum' => ['installment', 'recurring', 'all'], 'default' => 'all', 'description' => 'Filter to installment (bill_times > 0) or recurring (bill_times = 0) plans.'], |
| 333 |
'status' => ['type' => 'string', 'enum' => $subStatuses], |
| 334 |
'product_id' => ['type' => 'integer', 'description' => 'Limit to subscriptions for one product.'], |
| 335 |
'range' => $rangeProp, |
| 336 |
'start_date' => ['type' => 'string'], |
| 337 |
'end_date' => ['type' => 'string'], |
| 338 |
'date_from' => $dateFrom, |
| 339 |
'date_to' => $dateTo, |
| 340 |
'since' => $since, |
| 341 |
], |
| 342 |
], |
| 343 |
'execute_callback' => [self::class, 'querySubscriptions'], |
| 344 |
'permission_callback' => function () { |
| 345 |
return PermissionGate::can('reports/view'); |
| 346 |
}, |
| 347 |
'annotations' => ['readonly' => true], |
| 348 |
], |
| 349 |
]; |
| 350 |
|
| 351 |
// The live/test mode filter applies only to order-based reports — |
| 352 |
// fct_orders has a mode column, but subscription/customer analytics do |
| 353 |
// not. Injected here so the shared $modeProp stays a single definition. |
| 354 |
foreach ([ |
| 355 |
'fluent-cart/get-sales-report', |
| 356 |
'fluent-cart/get-sales-trend', |
| 357 |
'fluent-cart/get-top-products', |
| 358 |
'fluent-cart/get-refund-report', |
| 359 |
'fluent-cart/query-sources', |
| 360 |
'fluent-cart/query-orders', |
| 361 |
'fluent-cart/query-products', |
| 362 |
] as $modeTool) { |
| 363 |
$defs[$modeTool]['input_schema']['properties']['mode'] = $modeProp; |
| 364 |
} |
| 365 |
|
| 366 |
// page/per_page belong on the flexible aggregates, whose grouped output can |
| 367 |
// exceed the 200-row cap. The fixed reports (sales/trend/top/refund) return |
| 368 |
// a bounded shape and don't paginate. query-sources is excluded on purpose: |
| 369 |
// it already exposes its own `limit` + peek + truncated, and adding a |
| 370 |
// second page-size param would be ambiguous. |
| 371 |
foreach ([ |
| 372 |
'fluent-cart/query-orders', |
| 373 |
'fluent-cart/query-products', |
| 374 |
'fluent-cart/query-customers', |
| 375 |
'fluent-cart/query-subscriptions', |
| 376 |
] as $pagedTool) { |
| 377 |
$defs[$pagedTool]['input_schema']['properties']['page'] = $pageProp; |
| 378 |
$defs[$pagedTool]['input_schema']['properties']['per_page'] = $perPageProp; |
| 379 |
} |
| 380 |
|
| 381 |
// All five query-* aggregates share the same response envelope. |
| 382 |
foreach ([ |
| 383 |
'fluent-cart/query-orders', |
| 384 |
'fluent-cart/query-products', |
| 385 |
'fluent-cart/query-customers', |
| 386 |
'fluent-cart/query-subscriptions', |
| 387 |
'fluent-cart/query-sources', |
| 388 |
] as $queryTool) { |
| 389 |
$defs[$queryTool]['output_schema'] = $queryOutputSchema; |
| 390 |
} |
| 391 |
|
| 392 |
return $defs; |
| 393 |
} |
| 394 |
|
| 395 |
// ----------------------------------------------------------------- |
| 396 |
// get-sales-report |
| 397 |
// ----------------------------------------------------------------- |
| 398 |
|
| 399 |
public static function getSalesReport($params = []) |
| 400 |
{ |
| 401 |
$currency = self::currency($params); |
| 402 |
$range = self::resolveRange($params); |
| 403 |
$mode = self::orderMode($params); |
| 404 |
|
| 405 |
$current = self::salesMetrics($range['start'], $range['end'], $currency, $mode); |
| 406 |
|
| 407 |
$data = [ |
| 408 |
'range' => self::rangeBlock($range, $currency), |
| 409 |
'metrics' => self::salesMetricsOut($current, $currency), |
| 410 |
'definitions' => self::metricDefs(), |
| 411 |
]; |
| 412 |
|
| 413 |
$compare = !isset($params['compare']) || !empty($params['compare']); |
| 414 |
if ($compare && $range['prev_start']) { |
| 415 |
$prior = self::salesMetrics($range['prev_start'], $range['prev_end'], $currency, $mode); |
| 416 |
$data['comparison'] = [ |
| 417 |
'prior_metrics' => self::salesMetricsOut($prior, $currency), |
| 418 |
'change_percent' => [ |
| 419 |
'gross_revenue' => self::pct($current['gross'], $prior['gross']), |
| 420 |
'net_revenue' => self::pct($current['net'], $prior['net']), |
| 421 |
'order_count' => self::pct($current['orders'], $prior['orders']), |
| 422 |
], |
| 423 |
]; |
| 424 |
} |
| 425 |
|
| 426 |
$summary = sprintf( |
| 427 |
/* translators: 1: gross revenue, 2: order count, 3: average order value */ |
| 428 |
__('Revenue %1$s across %2$d paid orders, AOV %3$s.', 'fluent-cart'), |
| 429 |
MCPHelper::displayAmount($current['gross'], $currency), |
| 430 |
$current['orders'], |
| 431 |
MCPHelper::displayAmount($current['aov'], $currency) |
| 432 |
); |
| 433 |
|
| 434 |
return MCPHelper::envelope($summary, $data, ['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode]); |
| 435 |
} |
| 436 |
|
| 437 |
private static function salesMetrics($start, $end, $currency, $mode = 'all') |
| 438 |
{ |
| 439 |
// One aggregate scan instead of eight (this is the headline report, and |
| 440 |
// it runs twice when compare=true). Same filtered set, same numbers. |
| 441 |
$q = Order::query() |
| 442 |
->whereIn('payment_status', self::PAID) |
| 443 |
->where('currency', $currency) |
| 444 |
->where('created_at', '>=', $start) |
| 445 |
->where('created_at', '<=', $end); |
| 446 |
self::applyMode($q, $mode); |
| 447 |
$row = $q->selectRaw( |
| 448 |
'COUNT(*) as orders, ' |
| 449 |
. 'COALESCE(SUM(total_amount), 0) as gross, ' |
| 450 |
. 'COALESCE(SUM(total_paid), 0) as paid, ' |
| 451 |
. 'COALESCE(SUM(total_refund), 0) as refund, ' |
| 452 |
. 'COALESCE(SUM(tax_total), 0) as tax, ' |
| 453 |
. 'COALESCE(SUM(shipping_total), 0) as ship, ' |
| 454 |
. 'COALESCE(SUM(fee_total), 0) as fees, ' |
| 455 |
. 'COUNT(DISTINCT customer_id) as uniq' |
| 456 |
) |
| 457 |
->first(); |
| 458 |
|
| 459 |
$orders = $row ? (int) $row->orders : 0; |
| 460 |
$gross = $row ? (int) $row->gross : 0; |
| 461 |
$paid = $row ? (int) $row->paid : 0; |
| 462 |
$refund = $row ? (int) $row->refund : 0; |
| 463 |
$tax = $row ? (int) $row->tax : 0; |
| 464 |
$ship = $row ? (int) $row->ship : 0; |
| 465 |
$fees = $row ? (int) $row->fees : 0; |
| 466 |
$uniq = $row ? (int) $row->uniq : 0; |
| 467 |
|
| 468 |
return [ |
| 469 |
'orders' => $orders, |
| 470 |
'gross' => $gross, |
| 471 |
'paid' => $paid, |
| 472 |
'refund' => $refund, |
| 473 |
'net' => $paid - $refund, |
| 474 |
'tax' => $tax, |
| 475 |
'shipping' => $ship, |
| 476 |
'fees' => $fees, |
| 477 |
'unique' => $uniq, |
| 478 |
'aov' => $orders > 0 ? (int) round($gross / $orders) : 0, |
| 479 |
]; |
| 480 |
} |
| 481 |
|
| 482 |
private static function salesMetricsOut($m, $currency) |
| 483 |
{ |
| 484 |
return [ |
| 485 |
'order_count' => $m['orders'], |
| 486 |
'unique_customers' => $m['unique'], |
| 487 |
'gross_revenue' => MCPHelper::money($m['gross'], $currency), |
| 488 |
'net_revenue' => MCPHelper::money($m['net'], $currency), |
| 489 |
'paid' => MCPHelper::money($m['paid'], $currency), |
| 490 |
'refunded' => MCPHelper::money($m['refund'], $currency), |
| 491 |
'tax' => MCPHelper::money($m['tax'], $currency), |
| 492 |
'shipping' => MCPHelper::money($m['shipping'], $currency), |
| 493 |
'fees' => MCPHelper::money($m['fees'], $currency), |
| 494 |
'aov' => MCPHelper::money($m['aov'], $currency), |
| 495 |
]; |
| 496 |
} |
| 497 |
|
| 498 |
private static function metricDefs() |
| 499 |
{ |
| 500 |
return [ |
| 501 |
'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.', |
| 502 |
'gross_revenue' => 'Sum of order total_amount for paid orders (gross sales, before refunds).', |
| 503 |
'net_revenue' => 'Sum of total_paid minus total_refund; a fully refunded order nets to zero.', |
| 504 |
'refunded' => 'Sum of total_refund over paid orders — includes fully refunded orders, independent of current order status.', |
| 505 |
'aov' => 'gross_revenue divided by paid order count.', |
| 506 |
'date_basis' => 'created_at, within the given range.', |
| 507 |
]; |
| 508 |
} |
| 509 |
|
| 510 |
// ----------------------------------------------------------------- |
| 511 |
// get-sales-trend |
| 512 |
// ----------------------------------------------------------------- |
| 513 |
|
| 514 |
public static function getSalesTrend($params = []) |
| 515 |
{ |
| 516 |
$currency = self::currency($params); |
| 517 |
$range = self::resolveRange($params); |
| 518 |
$mode = self::orderMode($params); |
| 519 |
// `granularity` is an alias for `interval`; hour is for intraday launch |
| 520 |
// monitoring (MAX_BUCKETS caps it at 180 hours ~ 7.5 days per call). |
| 521 |
$intervalIn = isset($params['granularity']) ? $params['granularity'] : (isset($params['interval']) ? $params['interval'] : 'day'); |
| 522 |
$interval = in_array($intervalIn, ['hour', 'day', 'week', 'month'], true) ? $intervalIn : 'day'; |
| 523 |
|
| 524 |
$format = $interval === 'month' |
| 525 |
? '%Y-%m' |
| 526 |
: ($interval === 'week' ? '%x-W%v' : ($interval === 'hour' ? '%Y-%m-%d %H:00' : '%Y-%m-%d')); |
| 527 |
|
| 528 |
$q = Order::query() |
| 529 |
->whereIn('payment_status', self::PAID) |
| 530 |
->where('currency', $currency) |
| 531 |
->where('created_at', '>=', $range['start']) |
| 532 |
->where('created_at', '<=', $range['end']); |
| 533 |
self::applyMode($q, $mode); |
| 534 |
$rows = $q->selectRaw('DATE_FORMAT(created_at, ?) as bucket, COUNT(*) as order_count, SUM(total_amount) as gross', [$format]) |
| 535 |
->groupBy('bucket') |
| 536 |
->orderBy('bucket', 'ASC') |
| 537 |
->limit(self::MAX_BUCKETS) |
| 538 |
->get(); |
| 539 |
|
| 540 |
$trend = []; |
| 541 |
$sum = 0; |
| 542 |
foreach ($rows as $row) { |
| 543 |
$gross = (int) $row->gross; |
| 544 |
$sum += $gross; |
| 545 |
$trend[] = [ |
| 546 |
'bucket' => $row->bucket, |
| 547 |
'order_count' => (int) $row->order_count, |
| 548 |
'gross' => MCPHelper::moneyCompact($gross), |
| 549 |
]; |
| 550 |
} |
| 551 |
|
| 552 |
$summary = sprintf( |
| 553 |
/* translators: 1: number of buckets, 2: interval, 3: total revenue */ |
| 554 |
__('%1$d %2$s buckets, total revenue %3$s.', 'fluent-cart'), |
| 555 |
count($trend), |
| 556 |
$interval, |
| 557 |
MCPHelper::displayAmount($sum, $currency) |
| 558 |
); |
| 559 |
|
| 560 |
return MCPHelper::envelope( |
| 561 |
$summary, |
| 562 |
['interval' => $interval, 'range' => self::rangeBlock($range, $currency), 'trend' => $trend], |
| 563 |
['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode, 'truncated' => count($rows) >= self::MAX_BUCKETS] |
| 564 |
); |
| 565 |
} |
| 566 |
|
| 567 |
// ----------------------------------------------------------------- |
| 568 |
// get-top-products |
| 569 |
// ----------------------------------------------------------------- |
| 570 |
|
| 571 |
public static function getTopProducts($params = []) |
| 572 |
{ |
| 573 |
$currency = self::currency($params); |
| 574 |
$range = self::resolveRange($params); |
| 575 |
$mode = self::orderMode($params); |
| 576 |
$metric = isset($params['metric']) && $params['metric'] === 'units' ? 'units' : 'revenue'; |
| 577 |
$limit = isset($params['limit']) ? min(max((int) $params['limit'], 1), 50) : 10; |
| 578 |
$orderCol = $metric === 'units' ? 'units' : 'revenue'; |
| 579 |
|
| 580 |
$rows = OrderItem::query() |
| 581 |
->whereHas('order', function ($q) use ($range, $currency, $mode) { |
| 582 |
$q->whereIn('payment_status', self::PAID) |
| 583 |
->where('currency', $currency) |
| 584 |
->where('created_at', '>=', $range['start']) |
| 585 |
->where('created_at', '<=', $range['end']); |
| 586 |
self::applyMode($q, $mode); |
| 587 |
}) |
| 588 |
->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') |
| 589 |
->groupBy('post_id') |
| 590 |
->orderBy($orderCol, 'DESC') |
| 591 |
->limit($limit) |
| 592 |
->get(); |
| 593 |
|
| 594 |
$products = []; |
| 595 |
foreach ($rows as $row) { |
| 596 |
$products[] = [ |
| 597 |
'product_id' => (int) $row->post_id, |
| 598 |
'title' => $row->title, |
| 599 |
'units_sold' => (int) $row->units, |
| 600 |
'revenue' => MCPHelper::moneyCompact((int) $row->revenue), |
| 601 |
'order_count' => (int) $row->order_count, |
| 602 |
]; |
| 603 |
} |
| 604 |
|
| 605 |
$summary = sprintf( |
| 606 |
/* translators: 1: number of products, 2: ranking metric */ |
| 607 |
__('Top %1$d products by %2$s.', 'fluent-cart'), |
| 608 |
count($products), |
| 609 |
$metric |
| 610 |
); |
| 611 |
|
| 612 |
return MCPHelper::envelope($summary, ['metric' => $metric, 'range' => self::rangeBlock($range, $currency), 'products' => $products], ['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode]); |
| 613 |
} |
| 614 |
|
| 615 |
// ----------------------------------------------------------------- |
| 616 |
// get-refund-report |
| 617 |
// ----------------------------------------------------------------- |
| 618 |
|
| 619 |
public static function getRefundReport($params = []) |
| 620 |
{ |
| 621 |
$currency = self::currency($params); |
| 622 |
$range = self::resolveRange($params); |
| 623 |
$mode = self::orderMode($params); |
| 624 |
|
| 625 |
$paidBase = Order::query() |
| 626 |
->whereIn('payment_status', self::PAID) |
| 627 |
->where('currency', $currency) |
| 628 |
->where('created_at', '>=', $range['start']) |
| 629 |
->where('created_at', '<=', $range['end']); |
| 630 |
self::applyMode($paidBase, $mode); |
| 631 |
|
| 632 |
$paidCount = (clone $paidBase)->count(); |
| 633 |
|
| 634 |
$refundedBase = (clone $paidBase)->where('total_refund', '>', 0); |
| 635 |
$refundedCount = (clone $refundedBase)->count(); |
| 636 |
$refundedAmount = (int) (clone $refundedBase)->sum('total_refund'); |
| 637 |
|
| 638 |
$rate = $paidCount > 0 ? round(($refundedCount / $paidCount) * 100, 2) : 0; |
| 639 |
$avg = $refundedCount > 0 ? (int) round($refundedAmount / $refundedCount) : 0; |
| 640 |
|
| 641 |
$summary = sprintf( |
| 642 |
/* translators: 1: refunded order count, 2: refund rate percent, 3: total refunded */ |
| 643 |
__('%1$d orders refunded, %2$s%% of paid, totaling %3$s.', 'fluent-cart'), |
| 644 |
$refundedCount, |
| 645 |
$rate, |
| 646 |
MCPHelper::displayAmount($refundedAmount, $currency) |
| 647 |
); |
| 648 |
|
| 649 |
return MCPHelper::envelope( |
| 650 |
$summary, |
| 651 |
[ |
| 652 |
'range' => self::rangeBlock($range, $currency), |
| 653 |
'paid_order_count' => $paidCount, |
| 654 |
'refunded_order_count' => $refundedCount, |
| 655 |
'refund_rate_percent' => $rate, |
| 656 |
'total_refunded' => MCPHelper::money($refundedAmount, $currency), |
| 657 |
'average_refund' => MCPHelper::money($avg, $currency), |
| 658 |
'definitions' => [ |
| 659 |
'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.', |
| 660 |
'paid_order_count' => 'Orders that captured payment in the window, including those later fully refunded. This is the refund_rate denominator.', |
| 661 |
'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.', |
| 662 |
], |
| 663 |
], |
| 664 |
['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode] |
| 665 |
); |
| 666 |
} |
| 667 |
|
| 668 |
// ----------------------------------------------------------------- |
| 669 |
// query-sources (flexible UTM attribution) |
| 670 |
// ----------------------------------------------------------------- |
| 671 |
|
| 672 |
const UTM_DIMENSIONS = ['utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content', 'utm_id']; |
| 673 |
|
| 674 |
public static function querySources($params = []) |
| 675 |
{ |
| 676 |
$currency = self::currency($params); |
| 677 |
$range = self::resolveRange($params); |
| 678 |
$mode = self::orderMode($params); |
| 679 |
$limit = isset($params['limit']) ? min(max((int) $params['limit'], 1), self::MAX_ROWS) : 50; |
| 680 |
$metrics = self::pickList($params, 'metrics', ['orders', 'gross_revenue', 'net_revenue', 'aov', 'unique_customers', 'refunded_amount'], ['orders', 'gross_revenue']); |
| 681 |
$dimensions = self::pickList($params, 'dimensions', self::UTM_DIMENSIONS, ['utm_source', 'utm_medium', 'utm_campaign']); |
| 682 |
|
| 683 |
// Build the aggregate directly (rather than via SourceReportService, |
| 684 |
// which hard-codes its grouping) so the agent picks the UTM dimensions. |
| 685 |
// Uses the raw query builder with the same aliases as the admin Source |
| 686 |
// report to avoid Order model global scopes. Paid + one currency to stay |
| 687 |
// consistent with the other reports. |
| 688 |
// Dedupe operations to one row per order before joining: fct_order_operations |
| 689 |
// has only an INDEX on order_id (not UNIQUE), so a raw leftJoin would fan out |
| 690 |
// and make every SUM(o.<money>) double-count any order with >1 ops row. |
| 691 |
// MAX() per UTM column is ONLY_FULL_GROUP_BY-safe and returns the single |
| 692 |
// row's value in the normal one-row-per-order case. |
| 693 |
$opSub = App::db()->table('fct_order_operations') |
| 694 |
->select('order_id') |
| 695 |
->selectRaw( |
| 696 |
'MAX(utm_source) as utm_source, MAX(utm_medium) as utm_medium, ' |
| 697 |
. 'MAX(utm_campaign) as utm_campaign, MAX(utm_term) as utm_term, ' |
| 698 |
. 'MAX(utm_content) as utm_content, MAX(utm_id) as utm_id' |
| 699 |
) |
| 700 |
->groupBy('order_id'); |
| 701 |
|
| 702 |
$query = App::db()->table('fct_orders as o') |
| 703 |
->leftJoinSub($opSub, 'oo', 'o.id', '=', 'oo.order_id') |
| 704 |
->whereIn('o.payment_status', self::PAID) |
| 705 |
->where('o.currency', $currency) |
| 706 |
->where('o.created_at', '>=', $range['start']) |
| 707 |
->where('o.created_at', '<=', $range['end']); |
| 708 |
// Orders table is aliased 'o' here; qualify the mode column to match. |
| 709 |
self::applyMode($query, $mode, 'o.mode'); |
| 710 |
|
| 711 |
// Optional drill-down filters on exact UTM values. |
| 712 |
foreach (['utm_source', 'utm_medium', 'utm_campaign'] as $f) { |
| 713 |
if (!empty($params[$f])) { |
| 714 |
$query->where('oo.' . $f, sanitize_text_field($params[$f])); |
| 715 |
} |
| 716 |
} |
| 717 |
|
| 718 |
// Optional entity filters: restrict attribution to orders containing a |
| 719 |
// product/variation. whereExists on fct_order_items (never a join, so the |
| 720 |
// per-order SUM()s don't fan out) — mirrors the admin SourceReport filter. |
| 721 |
foreach (['product_id' => 'post_id', 'variation_id' => 'object_id'] as $param => $col) { |
| 722 |
if (!empty($params[$param])) { |
| 723 |
$val = (int) $params[$param]; |
| 724 |
$query->whereExists(function ($q) use ($col, $val) { |
| 725 |
$q->selectRaw('1') |
| 726 |
->from('fct_order_items as oi') |
| 727 |
->whereRaw('oi.order_id = o.id') |
| 728 |
->where('oi.' . $col, $val); |
| 729 |
}); |
| 730 |
} |
| 731 |
} |
| 732 |
|
| 733 |
$selects = []; |
| 734 |
$groupExpr = []; |
| 735 |
foreach ($dimensions as $dim) { |
| 736 |
// Coalesce NULL/'' into a single 'none' bucket; group by the |
| 737 |
// expression so the split values collapse together. |
| 738 |
$expr = "COALESCE(NULLIF(oo." . $dim . ", ''), 'none')"; |
| 739 |
$selects[] = $expr . ' as ' . $dim; |
| 740 |
$groupExpr[] = $expr; |
| 741 |
} |
| 742 |
|
| 743 |
// Definitions must match metricDefs() and the sales report so an agent's |
| 744 |
// "revenue by source" ties out with "revenue this month": |
| 745 |
// gross_revenue = SUM(total_amount), net_revenue = SUM(total_paid - total_refund). |
| 746 |
$metricSql = [ |
| 747 |
'orders' => 'COUNT(DISTINCT o.id) as orders', |
| 748 |
'gross_revenue' => 'SUM(o.total_amount) as gross_revenue', |
| 749 |
'net_revenue' => 'SUM(o.total_paid - o.total_refund) as net_revenue', |
| 750 |
'unique_customers' => 'COUNT(DISTINCT o.customer_id) as unique_customers', |
| 751 |
'refunded_amount' => 'SUM(o.total_refund) as refunded_amount', |
| 752 |
]; |
| 753 |
foreach ($metrics as $m) { |
| 754 |
if (isset($metricSql[$m])) { |
| 755 |
$selects[] = $metricSql[$m]; |
| 756 |
} |
| 757 |
} |
| 758 |
if (in_array('aov', $metrics, true)) { |
| 759 |
if (!in_array('gross_revenue', $metrics, true)) { |
| 760 |
$selects[] = $metricSql['gross_revenue']; |
| 761 |
} |
| 762 |
if (!in_array('orders', $metrics, true)) { |
| 763 |
$selects[] = $metricSql['orders']; |
| 764 |
} |
| 765 |
} |
| 766 |
|
| 767 |
$query->selectRaw(implode(', ', $selects)); |
| 768 |
if ($groupExpr) { |
| 769 |
$query->groupByRaw(implode(', ', $groupExpr)); |
| 770 |
} |
| 771 |
|
| 772 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'orders'; |
| 773 |
if ($firstMetric === 'aov') { |
| 774 |
$firstMetric = 'gross_revenue'; |
| 775 |
} |
| 776 |
if ($groupExpr && isset($metricSql[$firstMetric])) { |
| 777 |
$query->orderBy($firstMetric, 'DESC'); |
| 778 |
} |
| 779 |
// Fetch one extra row so we can tell "more exist beyond your limit" from |
| 780 |
// "you hit the 200 hard cap". $limit is already clamped to <= MAX_ROWS. |
| 781 |
$query->limit($limit + 1); |
| 782 |
|
| 783 |
$rows = $query->get(); |
| 784 |
$moneyMetrics = ['gross_revenue', 'net_revenue', 'refunded_amount']; |
| 785 |
$truncated = count($rows) > $limit; |
| 786 |
|
| 787 |
$out = []; |
| 788 |
foreach ($rows as $row) { |
| 789 |
if (count($out) >= $limit) { |
| 790 |
break; |
| 791 |
} |
| 792 |
$r = []; |
| 793 |
foreach ($dimensions as $dim) { |
| 794 |
$r[$dim] = $row->{$dim}; |
| 795 |
} |
| 796 |
foreach ($metrics as $m) { |
| 797 |
if ($m === 'aov') { |
| 798 |
$g = (int) $row->gross_revenue; |
| 799 |
$c = (int) $row->orders; |
| 800 |
$r['aov'] = MCPHelper::moneyCompact($c > 0 ? (int) round($g / $c) : 0); |
| 801 |
} elseif (in_array($m, $moneyMetrics, true)) { |
| 802 |
$r[$m] = MCPHelper::moneyCompact((int) $row->{$m}); |
| 803 |
} else { |
| 804 |
$r[$m] = (int) $row->{$m}; |
| 805 |
} |
| 806 |
} |
| 807 |
$out[] = $r; |
| 808 |
} |
| 809 |
|
| 810 |
$summary = sprintf( |
| 811 |
/* translators: 1: row count, 2: metric list, 3: dimension list */ |
| 812 |
__('%1$d rows — metrics [%2$s] grouped by [%3$s].', 'fluent-cart'), |
| 813 |
count($out), |
| 814 |
implode(', ', $metrics), |
| 815 |
$dimensions ? implode(', ', $dimensions) : __('total', 'fluent-cart') |
| 816 |
); |
| 817 |
|
| 818 |
return MCPHelper::envelope( |
| 819 |
$summary, |
| 820 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'range' => self::rangeBlock($range, $currency), 'rows' => $out], |
| 821 |
[ |
| 822 |
'currency' => $currency, |
| 823 |
'date_basis' => 'created_at', |
| 824 |
'mode' => $mode, |
| 825 |
'returned' => count($out), |
| 826 |
'limit' => $limit, |
| 827 |
'max_rows' => self::MAX_ROWS, |
| 828 |
// true = more rows exist; raise `limit` (up to max_rows) to see them. |
| 829 |
'truncated' => $truncated, |
| 830 |
] |
| 831 |
); |
| 832 |
} |
| 833 |
|
| 834 |
// ----------------------------------------------------------------- |
| 835 |
// query-orders (flexible aggregate) |
| 836 |
// ----------------------------------------------------------------- |
| 837 |
|
| 838 |
public static function queryOrders($params = []) |
| 839 |
{ |
| 840 |
$currency = self::currency($params); |
| 841 |
$range = self::resolveRange($params); |
| 842 |
$mode = self::orderMode($params); |
| 843 |
$metrics = self::pickList($params, 'metrics', ['order_count', 'gross_revenue', 'paid_revenue', 'refunded_amount', 'aov', 'unique_customers'], ['order_count', 'gross_revenue']); |
| 844 |
$dimensions = self::pickList($params, 'dimensions', ['day', 'week', 'month', 'status', 'payment_status', 'order_type', 'country', 'state'], []); |
| 845 |
|
| 846 |
$query = Order::query() |
| 847 |
->whereIn('payment_status', self::PAID) |
| 848 |
->where('currency', $currency) |
| 849 |
->where('created_at', '>=', $range['start']) |
| 850 |
->where('created_at', '<=', $range['end']); |
| 851 |
self::applyMode($query, $mode); |
| 852 |
|
| 853 |
// Optional entity filters: restrict to orders CONTAINING a product/variation. |
| 854 |
// whereHas keeps the aggregate order-level (metrics still reflect the whole |
| 855 |
// order); it never fans out rows the way a raw join would. |
| 856 |
self::applyOrderItemFilter($query, $params); |
| 857 |
|
| 858 |
// Geography comes from the order's OWN billing address first, so each |
| 859 |
// order's revenue is attributed to where that order was actually billed — |
| 860 |
// not to wherever the customer is registered now. Joined only when asked |
| 861 |
// for, so every other dimension keeps its current cost. |
| 862 |
// |
| 863 |
// Grouped subqueries, not raw joins: neither fct_order_addresses nor |
| 864 |
// fct_customer_addresses has a UNIQUE constraint on its (parent, type) |
| 865 |
// pair — fct_order_addresses actually holds 208 duplicate billing groups on |
| 866 |
// the reference store — and a duplicate would fan out and double-count |
| 867 |
// that order's revenue in every SUM. |
| 868 |
// |
| 869 |
// Within a duplicate group the LATEST row wins (highest id), picked as one |
| 870 |
// whole row. Taking MAX(country) and MAX(state) independently would let the |
| 871 |
// two columns come from DIFFERENT rows and synthesize a place that does not |
| 872 |
// exist: order 259 on the reference store has billing rows (BD, BD-60) and |
| 873 |
// (AT, BD-60), so independent MAX()es resolve it to Bangladesh even though |
| 874 |
// its latest address is Austrian. (That order is payment_status=pending and |
| 875 |
// so outside self::PAID — it demonstrates the mechanism, not a revenue error |
| 876 |
// this report was making.) Five duplicate groups there disagree on country or |
| 877 |
// state. latestRowPick() does the one-row pick inside the GROUP BY. |
| 878 |
// |
| 879 |
// The customer's primary billing address is the SECOND source, and the |
| 880 |
// choice between the two is made PER ORDER, not per column — see |
| 881 |
// dimensionExpr(). Most orders here carry no address row of their own |
| 882 |
// (digital goods skip the billing step), so order-address-only attribution |
| 883 |
// left 976 of 4,905 paid orders — 19.9% of orders and 9.0% of revenue — in |
| 884 |
// the 'unknown' bucket, which makes "revenue by country" unusable for the |
| 885 |
// question it exists to answer. Falling back to the buyer's own primary |
| 886 |
// billing address recovers 878 of those 976 and cuts 'unknown' to 2.0%; |
| 887 |
// 98 orders are then genuinely unattributable. (Figures over all four |
| 888 |
// self::PAID statuses, which is what this method actually queries.) |
| 889 |
// geo_source in meta names the precedence so an agent never has to guess. |
| 890 |
$geoDims = array_values(array_intersect(['country', 'state'], $dimensions)); |
| 891 |
$wantsGeo = $geoDims || !empty($params['country']); |
| 892 |
if ($wantsGeo) { |
| 893 |
// Columns aliased oaddr_*/caddr_* so raw select expressions stay |
| 894 |
// unambiguous without table prefixes (same note as queryCustomers). |
| 895 |
$addrSub = App::db()->table('fct_order_addresses') |
| 896 |
->select('order_id') |
| 897 |
->selectRaw( |
| 898 |
self::latestRowPick('country') . ' as oaddr_country, ' |
| 899 |
. self::latestRowPick('state') . ' as oaddr_state' |
| 900 |
) |
| 901 |
->where('type', 'billing') |
| 902 |
->groupBy('order_id'); |
| 903 |
|
| 904 |
$query->leftJoinSub($addrSub, 'fc_oaddr', 'fct_orders.id', '=', 'fc_oaddr.order_id'); |
| 905 |
|
| 906 |
// LEFT JOIN on customer_id, so a guest order simply has no fallback and |
| 907 |
// still lands in 'unknown' rather than dropping: fct_orders.customer_id |
| 908 |
// is nullable and NULL never matches a join predicate. The customer_id > 0 |
| 909 |
// guard covers the other shape of missing buyer — an order carrying 0 |
| 910 |
// instead of NULL would otherwise join to an orphaned address row filed |
| 911 |
// under customer 0 and inherit a stranger's country. |
| 912 |
// |
| 913 |
// customer_id is aliased caddr_customer_id for the same reason the value |
| 914 |
// columns are aliased: fct_orders has a customer_id too, and exposing a |
| 915 |
// second one made the existing COUNT(DISTINCT customer_id) behind the |
| 916 |
// unique_customers metric ambiguous — a hard SQL error, not a wrong |
| 917 |
// number. Nothing joined here may share a name with an orders column. |
| 918 |
$custAddrSub = App::db()->table('fct_customer_addresses') |
| 919 |
->selectRaw('customer_id as caddr_customer_id') |
| 920 |
->selectRaw( |
| 921 |
self::latestRowPick('country') . ' as caddr_country, ' |
| 922 |
. self::latestRowPick('state') . ' as caddr_state' |
| 923 |
) |
| 924 |
->where('type', 'billing') |
| 925 |
->where('is_primary', 1) |
| 926 |
->where('customer_id', '>', 0) |
| 927 |
->groupBy('customer_id'); |
| 928 |
|
| 929 |
$query->leftJoinSub($custAddrSub, 'fc_caddr', 'fct_orders.customer_id', '=', 'fc_caddr.caddr_customer_id'); |
| 930 |
|
| 931 |
if (!empty($params['country'])) { |
| 932 |
// Built from dimensionExpr() itself, so the filter and the country |
| 933 |
// dimension resolve a country identically BY CONSTRUCTION — including |
| 934 |
// the 'unknown' bucket, which a hand-written COALESCE here omitted, |
| 935 |
// making country=unknown return zero rows while the dimension |
| 936 |
// reported 98 such orders. |
| 937 |
$query->whereRaw( |
| 938 |
self::dimensionExpr('country') . ' = ?', |
| 939 |
[sanitize_text_field($params['country'])] |
| 940 |
); |
| 941 |
} |
| 942 |
} |
| 943 |
|
| 944 |
$selects = []; |
| 945 |
$groupCols = []; |
| 946 |
$groupRaws = []; |
| 947 |
foreach ($dimensions as $dim) { |
| 948 |
$expr = self::dimensionExpr($dim); |
| 949 |
$selects[] = $expr . ' as ' . $dim; |
| 950 |
if (in_array($dim, ['country', 'state'], true)) { |
| 951 |
// Group by the EXPRESSION, not the alias: the alias collides with |
| 952 |
// the real fc_oaddr.country column, and MySQL resolves a GROUP BY |
| 953 |
// name to the column first — which would split NULL from '' and |
| 954 |
// scatter the unknowns across two rows instead of one bucket. |
| 955 |
$groupRaws[] = $expr; |
| 956 |
} else { |
| 957 |
$groupCols[] = $dim; |
| 958 |
} |
| 959 |
} |
| 960 |
|
| 961 |
$metricSql = [ |
| 962 |
'order_count' => 'COUNT(*) as order_count', |
| 963 |
'gross_revenue' => 'SUM(total_amount) as gross_revenue', |
| 964 |
'paid_revenue' => 'SUM(total_paid) as paid_revenue', |
| 965 |
'refunded_amount' => 'SUM(total_refund) as refunded_amount', |
| 966 |
'unique_customers' => 'COUNT(DISTINCT customer_id) as unique_customers', |
| 967 |
]; |
| 968 |
foreach ($metrics as $m) { |
| 969 |
if (isset($metricSql[$m])) { |
| 970 |
$selects[] = $metricSql[$m]; |
| 971 |
} |
| 972 |
} |
| 973 |
if (in_array('aov', $metrics, true)) { |
| 974 |
if (!in_array('gross_revenue', $metrics, true)) { |
| 975 |
$selects[] = $metricSql['gross_revenue']; |
| 976 |
} |
| 977 |
if (!in_array('order_count', $metrics, true)) { |
| 978 |
$selects[] = $metricSql['order_count']; |
| 979 |
} |
| 980 |
} |
| 981 |
|
| 982 |
$query->selectRaw(implode(', ', $selects)); |
| 983 |
foreach ($groupCols as $g) { |
| 984 |
$query->groupBy($g); |
| 985 |
} |
| 986 |
foreach ($groupRaws as $g) { |
| 987 |
$query->groupByRaw($g); |
| 988 |
} |
| 989 |
|
| 990 |
$sortDesc = !isset($params['sort_desc']) || !empty($params['sort_desc']); |
| 991 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'order_count'; |
| 992 |
if ($firstMetric === 'aov') { |
| 993 |
$firstMetric = 'gross_revenue'; |
| 994 |
} |
| 995 |
|
| 996 |
// Find the first time dimension, if any. |
| 997 |
$timeDim = null; |
| 998 |
foreach ($dimensions as $dim) { |
| 999 |
if (in_array($dim, ['day', 'week', 'month'], true)) { |
| 1000 |
$timeDim = $dim; |
| 1001 |
break; |
| 1002 |
} |
| 1003 |
} |
| 1004 |
|
| 1005 |
$paging = self::queryPaging($params); |
| 1006 |
if ($groupCols || $groupRaws) { |
| 1007 |
if ($timeDim !== null && !isset($params['sort_desc'])) { |
| 1008 |
// A time series reads chronologically by default; ranking a |
| 1009 |
// calendar by metric is rarely what's wanted. An explicit |
| 1010 |
// sort_desc still overrides this. |
| 1011 |
$query->orderBy($timeDim, 'ASC'); |
| 1012 |
} else { |
| 1013 |
$query->orderBy($firstMetric, $sortDesc ? 'DESC' : 'ASC'); |
| 1014 |
} |
| 1015 |
// Deterministic tie-break on the group key so offset paging never |
| 1016 |
// reshuffles equal-metric rows across pages. |
| 1017 |
foreach ($groupCols as $g) { |
| 1018 |
$query->orderBy($g, 'ASC'); |
| 1019 |
} |
| 1020 |
foreach ($groupRaws as $g) { |
| 1021 |
// Raw, for the same alias/column collision reason as the GROUP BY. |
| 1022 |
$query->orderByRaw($g . ' ASC'); |
| 1023 |
} |
| 1024 |
} |
| 1025 |
// One extra row peeks past the page boundary → meta.page.has_more. |
| 1026 |
$query->limit($paging['per_page'] + 1)->offset($paging['offset']); |
| 1027 |
|
| 1028 |
$rows = $query->get(); |
| 1029 |
$fetched = count($rows); |
| 1030 |
$moneyMetrics = ['gross_revenue', 'paid_revenue', 'refunded_amount']; |
| 1031 |
|
| 1032 |
$out = []; |
| 1033 |
foreach ($rows as $row) { |
| 1034 |
if (count($out) >= $paging['per_page']) { |
| 1035 |
break; |
| 1036 |
} |
| 1037 |
$r = []; |
| 1038 |
foreach ($dimensions as $dim) { |
| 1039 |
$r[$dim] = $row->{$dim}; |
| 1040 |
} |
| 1041 |
foreach ($metrics as $m) { |
| 1042 |
if ($m === 'aov') { |
| 1043 |
$g = (int) $row->gross_revenue; |
| 1044 |
$c = (int) $row->order_count; |
| 1045 |
$r['aov'] = MCPHelper::moneyCompact($c > 0 ? (int) round($g / $c) : 0); |
| 1046 |
} elseif (in_array($m, $moneyMetrics, true)) { |
| 1047 |
$r[$m] = MCPHelper::moneyCompact((int) $row->{$m}); |
| 1048 |
} else { |
| 1049 |
$r[$m] = (int) $row->{$m}; |
| 1050 |
} |
| 1051 |
} |
| 1052 |
$out[] = $r; |
| 1053 |
} |
| 1054 |
|
| 1055 |
$summary = sprintf( |
| 1056 |
/* translators: 1: row count, 2: metric list, 3: dimension list */ |
| 1057 |
__('%1$d rows — metrics [%2$s] grouped by [%3$s].', 'fluent-cart'), |
| 1058 |
count($out), |
| 1059 |
implode(', ', $metrics), |
| 1060 |
$dimensions ? implode(', ', $dimensions) : __('total', 'fluent-cart') |
| 1061 |
); |
| 1062 |
|
| 1063 |
$meta = ['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode]; |
| 1064 |
// Only when geography was actually asked for — an agent grouping by month |
| 1065 |
// should not have to read a note about addresses. |
| 1066 |
if ($wantsGeo) { |
| 1067 |
$meta['geo_source'] = 'the order\'s own billing address, falling back to the buyer\'s primary billing address, then "unknown" for orders neither source can place. Rows always sum to the period total, so the "unknown" bucket shows exactly how much revenue is unattributed.'; |
| 1068 |
} |
| 1069 |
|
| 1070 |
return MCPHelper::envelope( |
| 1071 |
$summary, |
| 1072 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'range' => self::rangeBlock($range, $currency), 'rows' => $out], |
| 1073 |
array_merge($meta, self::pageMeta($paging, $fetched)) |
| 1074 |
); |
| 1075 |
} |
| 1076 |
|
| 1077 |
// ----------------------------------------------------------------- |
| 1078 |
// query-products / query-customers (flexible aggregates) |
| 1079 |
// ----------------------------------------------------------------- |
| 1080 |
|
| 1081 |
public static function queryProducts($params = []) |
| 1082 |
{ |
| 1083 |
$currency = self::currency($params); |
| 1084 |
$range = self::resolveRange($params); |
| 1085 |
$mode = self::orderMode($params); |
| 1086 |
$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']); |
| 1087 |
$dimensions = self::pickList($params, 'dimensions', ['product', 'variation', 'order_type'], ['product']); |
| 1088 |
|
| 1089 |
$query = OrderItem::query()->whereHas('order', function ($q) use ($range, $currency, $mode) { |
| 1090 |
$q->whereIn('payment_status', self::PAID) |
| 1091 |
->where('currency', $currency) |
| 1092 |
->where('created_at', '>=', $range['start']) |
| 1093 |
->where('created_at', '<=', $range['end']); |
| 1094 |
self::applyMode($q, $mode); |
| 1095 |
}); |
| 1096 |
|
| 1097 |
// Entity filters on the line item itself: post_id is the product, object_id |
| 1098 |
// the variation. These live on fct_order_items (the base model here), so a |
| 1099 |
// plain where — no join — scopes the whole aggregate to one product/variation. |
| 1100 |
// Without this, dimensions=[variation] grouped across the ENTIRE catalog even |
| 1101 |
// when a product_id was passed (the param was silently dropped). |
| 1102 |
if (!empty($params['product_id'])) { |
| 1103 |
$query->where('post_id', (int) $params['product_id']); |
| 1104 |
} |
| 1105 |
if (!empty($params['variation_id'])) { |
| 1106 |
$query->where('object_id', (int) $params['variation_id']); |
| 1107 |
} |
| 1108 |
|
| 1109 |
// order_type lives on the parent order (fct_orders.type), not the line |
| 1110 |
// item. Join orders only when it's requested so existing calls are |
| 1111 |
// unchanged. order_id -> orders.id is many-to-one, so the join never fans |
| 1112 |
// out line rows and the SUM()s stay identical to the ungrouped query. |
| 1113 |
$groupByOrderType = in_array('order_type', $dimensions, true); |
| 1114 |
if ($groupByOrderType) { |
| 1115 |
$query->join('fct_orders as fctord', 'fct_order_items.order_id', '=', 'fctord.id'); |
| 1116 |
} |
| 1117 |
|
| 1118 |
$hasProduct = in_array('product', $dimensions, true); |
| 1119 |
$hasVariation = in_array('variation', $dimensions, true); |
| 1120 |
|
| 1121 |
$selects = []; |
| 1122 |
$groupCols = []; |
| 1123 |
if ($hasProduct) { |
| 1124 |
$selects[] = 'post_id'; |
| 1125 |
$selects[] = 'MAX(post_title) as product_title'; |
| 1126 |
$groupCols[] = 'post_id'; |
| 1127 |
} |
| 1128 |
if ($hasVariation) { |
| 1129 |
$selects[] = 'object_id'; |
| 1130 |
$groupCols[] = 'object_id'; |
| 1131 |
// Make variation rows self-describing so the agent needs no follow-up |
| 1132 |
// lookup: the variation's own stored title, plus the parent product's |
| 1133 |
// id + name when we aren't already grouping by product. A variation |
| 1134 |
// belongs to exactly one product, so MAX(post_id)/MAX(post_title) is |
| 1135 |
// that single product's value per group — the join never fans out. |
| 1136 |
$selects[] = 'MAX(title) as variation_label'; |
| 1137 |
if (!$hasProduct) { |
| 1138 |
$selects[] = 'MAX(post_id) as vproduct_id'; |
| 1139 |
$selects[] = 'MAX(post_title) as product_title'; |
| 1140 |
} |
| 1141 |
} |
| 1142 |
if ($groupByOrderType) { |
| 1143 |
// Reuse the order_type -> column mapping from query-orders rather than |
| 1144 |
// hard-coding it again; qualify it with the join alias. |
| 1145 |
$selects[] = 'fctord.' . self::dimensionExpr('order_type') . ' as order_type'; |
| 1146 |
$groupCols[] = 'order_type'; |
| 1147 |
} |
| 1148 |
|
| 1149 |
// line_total = subtotal - discount_total on every item (see DiscountService/ |
| 1150 |
// CheckoutProcessor), so list_price_sum - line_revenue == discount_amount by |
| 1151 |
// construction: margin leakage is the gap between what was listed and what |
| 1152 |
// was charged, before refunds. |
| 1153 |
// |
| 1154 |
// Every item column below is qualified with the real (prefixed) items table. |
| 1155 |
// When order_type is grouped, fct_orders is joined and columns that exist on |
| 1156 |
// BOTH tables — subtotal is one — make a bare SUM(subtotal) throw SQL 1052 |
| 1157 |
// ("column is ambiguous"). selectRaw bypasses the grammar's table-prefixing, |
| 1158 |
// so the literal prefixed name (not the bare `fct_order_items`) is required. |
| 1159 |
// Qualifying all of them, not just subtotal, keeps a future column collision |
| 1160 |
// (or a new metric) from silently reintroducing the crash. |
| 1161 |
$itemsTable = App::db()->getTableName('fct_order_items'); |
| 1162 |
$metricSql = [ |
| 1163 |
'units_sold' => 'SUM(' . $itemsTable . '.quantity) as units_sold', |
| 1164 |
'line_revenue' => 'SUM(' . $itemsTable . '.line_total) as line_revenue', |
| 1165 |
'list_price_sum' => 'SUM(' . $itemsTable . '.subtotal) as list_price_sum', |
| 1166 |
'discount_amount' => 'SUM(' . $itemsTable . '.discount_total) as discount_amount', |
| 1167 |
'net_revenue' => 'SUM(' . $itemsTable . '.line_total - ' . $itemsTable . '.refund_total) as net_revenue', |
| 1168 |
'order_count' => 'COUNT(DISTINCT ' . $itemsTable . '.order_id) as order_count', |
| 1169 |
'refund_amount' => 'SUM(' . $itemsTable . '.refund_total) as refund_amount', |
| 1170 |
]; |
| 1171 |
foreach ($metrics as $m) { |
| 1172 |
if (isset($metricSql[$m])) { |
| 1173 |
$selects[] = $metricSql[$m]; |
| 1174 |
} |
| 1175 |
} |
| 1176 |
if (in_array('avg_unit_price', $metrics, true)) { |
| 1177 |
if (!in_array('line_revenue', $metrics, true)) { |
| 1178 |
$selects[] = $metricSql['line_revenue']; |
| 1179 |
} |
| 1180 |
if (!in_array('units_sold', $metrics, true)) { |
| 1181 |
$selects[] = $metricSql['units_sold']; |
| 1182 |
} |
| 1183 |
} |
| 1184 |
|
| 1185 |
$query->selectRaw(implode(', ', $selects)); |
| 1186 |
foreach ($groupCols as $g) { |
| 1187 |
$query->groupBy($g); |
| 1188 |
} |
| 1189 |
|
| 1190 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'line_revenue'; |
| 1191 |
if ($firstMetric === 'avg_unit_price') { |
| 1192 |
$firstMetric = 'line_revenue'; |
| 1193 |
} |
| 1194 |
$paging = self::queryPaging($params); |
| 1195 |
if ($groupCols && isset($metricSql[$firstMetric])) { |
| 1196 |
$query->orderBy($firstMetric, 'DESC'); |
| 1197 |
// Deterministic tie-break on the group key for stable offset paging. |
| 1198 |
foreach ($groupCols as $g) { |
| 1199 |
$query->orderBy($g, 'ASC'); |
| 1200 |
} |
| 1201 |
} |
| 1202 |
$query->limit($paging['per_page'] + 1)->offset($paging['offset']); |
| 1203 |
|
| 1204 |
$rows = $query->get(); |
| 1205 |
$fetched = count($rows); |
| 1206 |
$moneyMetrics = ['line_revenue', 'list_price_sum', 'discount_amount', 'net_revenue', 'refund_amount', 'avg_unit_price']; |
| 1207 |
|
| 1208 |
$out = []; |
| 1209 |
foreach ($rows as $row) { |
| 1210 |
if (count($out) >= $paging['per_page']) { |
| 1211 |
break; |
| 1212 |
} |
| 1213 |
$r = []; |
| 1214 |
if ($hasProduct) { |
| 1215 |
$r['product_id'] = (int) $row->post_id; |
| 1216 |
$r['product_name'] = $row->product_title; |
| 1217 |
// product_title kept as a backward-compatible alias of product_name. |
| 1218 |
$r['product_title'] = $row->product_title; |
| 1219 |
} elseif ($hasVariation) { |
| 1220 |
$r['product_id'] = (int) $row->vproduct_id; |
| 1221 |
$r['product_name'] = $row->product_title; |
| 1222 |
} |
| 1223 |
if ($hasVariation) { |
| 1224 |
$r['variation_id'] = (int) $row->object_id; |
| 1225 |
$r['variation_label'] = $row->variation_label; |
| 1226 |
} |
| 1227 |
if ($groupByOrderType) { |
| 1228 |
$r['order_type'] = $row->order_type; |
| 1229 |
} |
| 1230 |
foreach ($metrics as $m) { |
| 1231 |
if ($m === 'avg_unit_price') { |
| 1232 |
$u = (int) $row->units_sold; |
| 1233 |
$rev = (int) $row->line_revenue; |
| 1234 |
$r['avg_unit_price'] = MCPHelper::moneyCompact($u > 0 ? (int) round($rev / $u) : 0); |
| 1235 |
} elseif (in_array($m, $moneyMetrics, true)) { |
| 1236 |
$r[$m] = MCPHelper::moneyCompact((int) $row->{$m}); |
| 1237 |
} else { |
| 1238 |
$r[$m] = (int) $row->{$m}; |
| 1239 |
} |
| 1240 |
} |
| 1241 |
$out[] = $r; |
| 1242 |
} |
| 1243 |
|
| 1244 |
return MCPHelper::envelope( |
| 1245 |
sprintf( |
| 1246 |
/* translators: 1: row count, 2: metric list */ |
| 1247 |
__('%1$d rows — product metrics [%2$s].', 'fluent-cart'), |
| 1248 |
count($out), |
| 1249 |
implode(', ', $metrics) |
| 1250 |
), |
| 1251 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'range' => self::rangeBlock($range, $currency), 'rows' => $out], |
| 1252 |
array_merge(['currency' => $currency, 'date_basis' => 'created_at', 'mode' => $mode], self::pageMeta($paging, $fetched)) |
| 1253 |
); |
| 1254 |
} |
| 1255 |
|
| 1256 |
public static function queryCustomers($params = []) |
| 1257 |
{ |
| 1258 |
$metrics = self::pickList($params, 'metrics', ['customer_count', 'total_ltv', 'avg_ltv', 'avg_purchase_count', 'repeat_customers'], ['customer_count', 'total_ltv']); |
| 1259 |
$dimensions = self::pickList($params, 'dimensions', ['country', 'state', 'status', 'first_purchase_month', 'last_purchase_month'], []); |
| 1260 |
|
| 1261 |
$query = Customer::query(); |
| 1262 |
|
| 1263 |
// Geography resolves through the customer's primary BILLING ADDRESS, |
| 1264 |
// falling back to the fct_customers profile columns. |
| 1265 |
// |
| 1266 |
// Neither source is complete on its own, which is the whole reason for the |
| 1267 |
// coalesce. Measured on the 2,320-customer reference store: 2,176 customers |
| 1268 |
// have both and the two NEVER disagree (zero rows where both are set and |
| 1269 |
// differ), 96 have only the profile column, 7 have only an address row, and |
| 1270 |
// 41 have neither. So reading either source alone silently drops customers |
| 1271 |
// the other could place — profile-only would lose 7, address-only would lose |
| 1272 |
// 96 — while the coalesce leaves just the 41 genuinely unplaceable. |
| 1273 |
// |
| 1274 |
// Billing address is ordered first because it is the value the buyer actually |
| 1275 |
// typed, whereas the profile column is written once when the customer row is |
| 1276 |
// created and never refreshed (and at checkout the seeded value can be a |
| 1277 |
// country the frontend guessed from the browser timezone). On this store that |
| 1278 |
// ordering changes no result; it is the correct precedence for the case where |
| 1279 |
// a profile snapshot has gone stale. |
| 1280 |
// |
| 1281 |
// LEFT JOIN, so a customer with no address row is still counted (on the |
| 1282 |
// profile value, or 'unknown'); an INNER JOIN would silently drop them |
| 1283 |
// and quietly shrink every total. The join rides the existing |
| 1284 |
// (customer_id, is_primary) index — measured at no material cost. |
| 1285 |
// |
| 1286 |
// Grouped subquery rather than a raw leftJoin: fct_customer_addresses has |
| 1287 |
// no UNIQUE constraint on (customer_id, type, is_primary), so a second |
| 1288 |
// primary billing row would fan the join out and double-count that |
| 1289 |
// customer in COUNT(*) and SUM(ltv). Same guard the source report applies |
| 1290 |
// to fct_order_operations above. MAX() is ONLY_FULL_GROUP_BY-safe and |
| 1291 |
// returns the row's own value in the normal one-row case. |
| 1292 |
// The subquery's columns are aliased addr_* on purpose: raw SQL fragments |
| 1293 |
// are not table-prefixed by the builder, so a bare `country` in a |
| 1294 |
// selectRaw would be ambiguous across the two tables and a qualified |
| 1295 |
// `fct_customers.country` would miss the wp_ prefix. Distinct names keep |
| 1296 |
// every raw expression unambiguous with no prefix handling at all. |
| 1297 |
// latestRowPick, not MAX() per column: with more than one primary billing row |
| 1298 |
// independent MAX()es can take country from one row and state from another and |
| 1299 |
// report a place that does not exist. See the note in queryOrders. |
| 1300 |
$addrSub = App::db()->table('fct_customer_addresses') |
| 1301 |
->select('customer_id') |
| 1302 |
->selectRaw( |
| 1303 |
self::latestRowPick('country') . ' as addr_country, ' |
| 1304 |
. self::latestRowPick('state') . ' as addr_state' |
| 1305 |
) |
| 1306 |
->where('type', 'billing') |
| 1307 |
->where('is_primary', 1) |
| 1308 |
->groupBy('customer_id'); |
| 1309 |
|
| 1310 |
$query->leftJoinSub($addrSub, 'fc_addr', 'fct_customers.id', '=', 'fc_addr.customer_id'); |
| 1311 |
|
| 1312 |
if (!empty($params['country'])) { |
| 1313 |
$country = sanitize_text_field($params['country']); |
| 1314 |
// Match on the resolved value, not the raw column, so the filter and |
| 1315 |
// the grouping can never disagree about which country a customer is in. |
| 1316 |
$query->whereRaw( |
| 1317 |
"COALESCE(NULLIF(fc_addr.addr_country, ''), NULLIF(country, '')) = ?", |
| 1318 |
[$country] |
| 1319 |
); |
| 1320 |
} |
| 1321 |
// Unambiguous without qualification: the joined subquery exposes only |
| 1322 |
// customer_id / addr_country / addr_state. |
| 1323 |
if (!empty($params['status'])) { |
| 1324 |
$query->where('status', sanitize_text_field($params['status'])); |
| 1325 |
} |
| 1326 |
if (isset($params['min_ltv'])) { |
| 1327 |
$query->where('ltv', '>=', Helper::toCent($params['min_ltv'])); |
| 1328 |
} |
| 1329 |
if (isset($params['min_purchase_count'])) { |
| 1330 |
$query->where('purchase_count', '>=', (int) $params['min_purchase_count']); |
| 1331 |
} |
| 1332 |
|
| 1333 |
$selects = []; |
| 1334 |
$groupCols = []; |
| 1335 |
foreach ($dimensions as $dim) { |
| 1336 |
if ($dim === 'country' || $dim === 'state') { |
| 1337 |
// Billing address first, profile column second, 'unknown' last. |
| 1338 |
// Coalesce NULL and '' into the single 'unknown' bucket. Group by |
| 1339 |
// the expression (not the alias, which would resolve to the raw |
| 1340 |
// column and keep null/'' split). |
| 1341 |
$expr = "COALESCE(NULLIF(fc_addr.addr_$dim, ''), NULLIF($dim, ''), 'unknown')"; |
| 1342 |
$selects[] = "$expr as $dim"; |
| 1343 |
$groupCols[] = $expr; |
| 1344 |
} elseif ($dim === 'status') { |
| 1345 |
// Same 'unknown' coalescing as country/state, for the same reason. |
| 1346 |
// fct_customers.status is nullable with no default and 77 of the |
| 1347 |
// 2,320 customers on the reference store have NULL or '' — so a bare |
| 1348 |
// GROUP BY status answered "how many customers per status" with two |
| 1349 |
// buckets the agent was never told about (null AND '', split apart), |
| 1350 |
// neither of them in the status enum. One labelled bucket keeps the |
| 1351 |
// rows summing to the customer total and makes the gap legible. |
| 1352 |
$expr = "COALESCE(NULLIF(status, ''), 'unknown')"; |
| 1353 |
$selects[] = "$expr as status"; |
| 1354 |
$groupCols[] = $expr; |
| 1355 |
} elseif ($dim === 'first_purchase_month') { |
| 1356 |
$selects[] = "DATE_FORMAT(first_purchase_date, '%Y-%m') as first_purchase_month"; |
| 1357 |
$groupCols[] = "DATE_FORMAT(first_purchase_date, '%Y-%m')"; |
| 1358 |
} elseif ($dim === 'last_purchase_month') { |
| 1359 |
$selects[] = "DATE_FORMAT(last_purchase_date, '%Y-%m') as last_purchase_month"; |
| 1360 |
$groupCols[] = "DATE_FORMAT(last_purchase_date, '%Y-%m')"; |
| 1361 |
} |
| 1362 |
} |
| 1363 |
|
| 1364 |
$metricSql = [ |
| 1365 |
'customer_count' => 'COUNT(*) as customer_count', |
| 1366 |
'total_ltv' => 'SUM(ltv) as total_ltv', |
| 1367 |
'avg_ltv' => 'AVG(ltv) as avg_ltv', |
| 1368 |
'avg_purchase_count' => 'AVG(purchase_count) as avg_purchase_count', |
| 1369 |
'repeat_customers' => 'SUM(CASE WHEN purchase_count > 1 THEN 1 ELSE 0 END) as repeat_customers', |
| 1370 |
]; |
| 1371 |
foreach ($metrics as $m) { |
| 1372 |
if (isset($metricSql[$m])) { |
| 1373 |
$selects[] = $metricSql[$m]; |
| 1374 |
} |
| 1375 |
} |
| 1376 |
|
| 1377 |
$query->selectRaw(implode(', ', $selects)); |
| 1378 |
if ($groupCols) { |
| 1379 |
$query->groupByRaw(implode(', ', $groupCols)); |
| 1380 |
} |
| 1381 |
|
| 1382 |
$paging = self::queryPaging($params); |
| 1383 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'customer_count'; |
| 1384 |
if ($groupCols && isset($metricSql[$firstMetric])) { |
| 1385 |
$query->orderBy($firstMetric, 'DESC'); |
| 1386 |
// Deterministic tie-break on the grouped dimensions (their aliases) |
| 1387 |
// so offset paging is stable across pages. |
| 1388 |
foreach ($dimensions as $d) { |
| 1389 |
$query->orderBy($d, 'ASC'); |
| 1390 |
} |
| 1391 |
} |
| 1392 |
$query->limit($paging['per_page'] + 1)->offset($paging['offset']); |
| 1393 |
|
| 1394 |
$rows = $query->get(); |
| 1395 |
$fetched = count($rows); |
| 1396 |
$moneyMetrics = ['total_ltv', 'avg_ltv']; |
| 1397 |
|
| 1398 |
$out = []; |
| 1399 |
foreach ($rows as $row) { |
| 1400 |
if (count($out) >= $paging['per_page']) { |
| 1401 |
break; |
| 1402 |
} |
| 1403 |
$r = []; |
| 1404 |
foreach ($dimensions as $dim) { |
| 1405 |
$r[$dim] = $row->{$dim}; |
| 1406 |
} |
| 1407 |
foreach ($metrics as $m) { |
| 1408 |
if (in_array($m, $moneyMetrics, true)) { |
| 1409 |
$r[$m] = MCPHelper::moneyCompact((int) round((float) $row->{$m})); |
| 1410 |
} elseif ($m === 'avg_purchase_count') { |
| 1411 |
$r[$m] = round((float) $row->{$m}, 2); |
| 1412 |
} else { |
| 1413 |
$r[$m] = (int) $row->{$m}; |
| 1414 |
} |
| 1415 |
} |
| 1416 |
$out[] = $r; |
| 1417 |
} |
| 1418 |
|
| 1419 |
return MCPHelper::envelope( |
| 1420 |
sprintf( |
| 1421 |
/* translators: 1: row count, 2: metric list */ |
| 1422 |
__('%1$d rows — customer metrics [%2$s].', 'fluent-cart'), |
| 1423 |
count($out), |
| 1424 |
implode(', ', $metrics) |
| 1425 |
), |
| 1426 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'rows' => $out], |
| 1427 |
array_merge( |
| 1428 |
[ |
| 1429 |
'currency' => MCPHelper::currencyCode(), |
| 1430 |
'note' => 'LTV is in store currency; customers are not currency-scoped.', |
| 1431 |
'country_source' => 'primary billing address, falling back to the customer profile location, then "unknown". Registration geography, captured at first purchase — for per-order billing geography use query-orders grouped by country.', |
| 1432 |
], |
| 1433 |
self::pageMeta($paging, $fetched) |
| 1434 |
) |
| 1435 |
); |
| 1436 |
} |
| 1437 |
|
| 1438 |
// ----------------------------------------------------------------- |
| 1439 |
// query-subscriptions (flexible aggregate) |
| 1440 |
// ----------------------------------------------------------------- |
| 1441 |
|
| 1442 |
public static function querySubscriptions($params = []) |
| 1443 |
{ |
| 1444 |
$range = self::resolveRange($params); |
| 1445 |
$dateBasis = self::subDateBasis($params); |
| 1446 |
$metrics = self::pickList($params, 'metrics', ['subscription_count', 'contract_value', 'recurring_value'], ['subscription_count', 'contract_value']); |
| 1447 |
$dimensions = self::pickList($params, 'dimensions', ['month', 'plan_type', 'status', 'billing_interval'], []); |
| 1448 |
|
| 1449 |
// Same UTC window resolution as every other report; the chosen date_basis |
| 1450 |
// is the only thing that varies (signup cohort vs churn vs upcoming). |
| 1451 |
$query = Subscription::query() |
| 1452 |
->where($dateBasis, '>=', $range['start']) |
| 1453 |
->where($dateBasis, '<=', $range['end']); |
| 1454 |
|
| 1455 |
if (!empty($params['product_id'])) { |
| 1456 |
$query->where('product_id', (int) $params['product_id']); |
| 1457 |
} |
| 1458 |
if (!empty($params['status'])) { |
| 1459 |
$query->where('status', sanitize_text_field($params['status'])); |
| 1460 |
} |
| 1461 |
$planType = isset($params['plan_type']) && in_array($params['plan_type'], ['installment', 'recurring'], true) ? $params['plan_type'] : 'all'; |
| 1462 |
if ($planType !== 'all') { |
| 1463 |
$query->ofPlanType($planType); |
| 1464 |
} |
| 1465 |
|
| 1466 |
// plan_type is derived from bill_times with the SAME threshold as |
| 1467 |
// Subscription::isInstallment(), so the SQL and PHP definitions agree. |
| 1468 |
$planExpr = "CASE WHEN bill_times > 0 THEN 'installment' ELSE 'recurring' END"; |
| 1469 |
|
| 1470 |
$selects = []; |
| 1471 |
$groupExpr = []; |
| 1472 |
foreach ($dimensions as $dim) { |
| 1473 |
if ($dim === 'month') { |
| 1474 |
$expr = "DATE_FORMAT($dateBasis, '%Y-%m')"; |
| 1475 |
} elseif ($dim === 'plan_type') { |
| 1476 |
$expr = $planExpr; |
| 1477 |
} else { |
| 1478 |
// status / billing_interval — plain columns. |
| 1479 |
$expr = $dim; |
| 1480 |
} |
| 1481 |
$selects[] = $expr . ' as ' . $dim; |
| 1482 |
$groupExpr[] = $expr; |
| 1483 |
} |
| 1484 |
|
| 1485 |
// contract_value is the SUM form of Subscription::totalContractValue() |
| 1486 |
// (recurring_total * bill_times) — installments booked at full committed |
| 1487 |
// value, 0 for open-ended plans. No parallel money math. |
| 1488 |
$metricSql = [ |
| 1489 |
'subscription_count' => 'COUNT(*) as subscription_count', |
| 1490 |
'contract_value' => 'SUM(recurring_total * bill_times) as contract_value', |
| 1491 |
'recurring_value' => 'SUM(recurring_total) as recurring_value', |
| 1492 |
]; |
| 1493 |
foreach ($metrics as $m) { |
| 1494 |
if (isset($metricSql[$m])) { |
| 1495 |
$selects[] = $metricSql[$m]; |
| 1496 |
} |
| 1497 |
} |
| 1498 |
|
| 1499 |
$query->selectRaw(implode(', ', $selects)); |
| 1500 |
if ($groupExpr) { |
| 1501 |
$query->groupByRaw(implode(', ', $groupExpr)); |
| 1502 |
} |
| 1503 |
|
| 1504 |
$paging = self::queryPaging($params); |
| 1505 |
$firstMetric = isset($metrics[0]) ? $metrics[0] : 'subscription_count'; |
| 1506 |
if ($groupExpr && isset($metricSql[$firstMetric])) { |
| 1507 |
$query->orderBy($firstMetric, 'DESC'); |
| 1508 |
// Deterministic tie-break on the grouped dimensions (their aliases). |
| 1509 |
foreach ($dimensions as $d) { |
| 1510 |
$query->orderBy($d, 'ASC'); |
| 1511 |
} |
| 1512 |
} |
| 1513 |
$query->limit($paging['per_page'] + 1)->offset($paging['offset']); |
| 1514 |
|
| 1515 |
$rows = $query->get(); |
| 1516 |
$fetched = count($rows); |
| 1517 |
$moneyMetrics = ['contract_value', 'recurring_value']; |
| 1518 |
|
| 1519 |
$out = []; |
| 1520 |
foreach ($rows as $row) { |
| 1521 |
if (count($out) >= $paging['per_page']) { |
| 1522 |
break; |
| 1523 |
} |
| 1524 |
$r = []; |
| 1525 |
foreach ($dimensions as $dim) { |
| 1526 |
$r[$dim] = $row->{$dim}; |
| 1527 |
} |
| 1528 |
foreach ($metrics as $m) { |
| 1529 |
if (in_array($m, $moneyMetrics, true)) { |
| 1530 |
$r[$m] = MCPHelper::moneyCompact((int) $row->{$m}); |
| 1531 |
} else { |
| 1532 |
$r[$m] = (int) $row->{$m}; |
| 1533 |
} |
| 1534 |
} |
| 1535 |
$out[] = $r; |
| 1536 |
} |
| 1537 |
|
| 1538 |
return MCPHelper::envelope( |
| 1539 |
sprintf( |
| 1540 |
/* translators: 1: row count, 2: metric list, 3: dimension list */ |
| 1541 |
__('%1$d rows — subscription metrics [%2$s] grouped by [%3$s].', 'fluent-cart'), |
| 1542 |
count($out), |
| 1543 |
implode(', ', $metrics), |
| 1544 |
$dimensions ? implode(', ', $dimensions) : __('total', 'fluent-cart') |
| 1545 |
), |
| 1546 |
['metrics' => $metrics, 'dimensions' => $dimensions, 'date_basis' => $dateBasis, 'range' => self::rangeBlock($range, MCPHelper::currencyCode()), 'rows' => $out], |
| 1547 |
array_merge([ |
| 1548 |
'currency' => MCPHelper::currencyCode(), |
| 1549 |
'date_basis' => $dateBasis, |
| 1550 |
'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.', |
| 1551 |
], self::pageMeta($paging, $fetched)) |
| 1552 |
); |
| 1553 |
} |
| 1554 |
|
| 1555 |
private static function subDateBasis($params) |
| 1556 |
{ |
| 1557 |
$allowed = ['created_at', 'canceled_at', 'next_billing_date']; |
| 1558 |
|
| 1559 |
return isset($params['date_basis']) && in_array($params['date_basis'], $allowed, true) ? $params['date_basis'] : 'created_at'; |
| 1560 |
} |
| 1561 |
|
| 1562 |
/** |
| 1563 |
* Restrict an Order-model aggregate to orders CONTAINING a given product / |
| 1564 |
* variation. Uses whereHas on order_items (post_id = product, object_id = |
| 1565 |
* variation) so the filter is a subquery, not a join — order-level metrics stay |
| 1566 |
* per-order and never fan out. Both filters combine (AND) when both are given. |
| 1567 |
*/ |
| 1568 |
private static function applyOrderItemFilter($query, $params) |
| 1569 |
{ |
| 1570 |
foreach (['product_id' => 'post_id', 'variation_id' => 'object_id'] as $param => $col) { |
| 1571 |
if (!empty($params[$param])) { |
| 1572 |
$val = (int) $params[$param]; |
| 1573 |
$query->whereHas('order_items', function ($q) use ($col, $val) { |
| 1574 |
$q->where($col, $val); |
| 1575 |
}); |
| 1576 |
} |
| 1577 |
} |
| 1578 |
} |
| 1579 |
|
| 1580 |
/** |
| 1581 |
* Aggregate expression returning $column from the HIGHEST-id row in the group. |
| 1582 |
* |
| 1583 |
* Address tables have no UNIQUE constraint on (parent, type), so a group can hold |
| 1584 |
* several rows and the newest is the one the buyer last entered. Two columns each |
| 1585 |
* aggregated with a bare MAX() can come from two different rows and describe a |
| 1586 |
* place that never existed (a real case: country from one row, state from another). |
| 1587 |
* |
| 1588 |
* Prefixing each value with its zero-padded id makes lexicographic MAX() agree |
| 1589 |
* with numeric id order, so every column built this way resolves to the SAME row; |
| 1590 |
* SUBSTRING then drops the prefix. The width is 20 because that is exactly the |
| 1591 |
* digit count of the largest BIGINT UNSIGNED (18446744073709551615) — no id can |
| 1592 |
* overflow the padding, so all prefixes are equal-length and compare numerically. |
| 1593 |
* Ids are unique within a group (id is the PK), so the prefix alone always decides |
| 1594 |
* the winner and the collation of the value suffix can never influence it. |
| 1595 |
* |
| 1596 |
* COALESCE(...,'') matters — CONCAT with NULL is NULL and MAX() skips NULLs, which |
| 1597 |
* would let a NULL column fall back to a different row and reintroduce the mixing |
| 1598 |
* this exists to prevent. |
| 1599 |
* |
| 1600 |
* Portable to MySQL 5.6+ (no window functions) and needs no nested join, unlike |
| 1601 |
* ORDER BY ... LIMIT 1 or ROW_NUMBER(). |
| 1602 |
* |
| 1603 |
* @param string $column trusted column name — never interpolate caller input here |
| 1604 |
* @return string |
| 1605 |
*/ |
| 1606 |
private static function latestRowPick($column) |
| 1607 |
{ |
| 1608 |
return "SUBSTRING(MAX(CONCAT(LPAD(id, 20, '0'), COALESCE($column, ''))), 21)"; |
| 1609 |
} |
| 1610 |
|
| 1611 |
private static function dimensionExpr($dim) |
| 1612 |
{ |
| 1613 |
if ($dim === 'day') { |
| 1614 |
return "DATE_FORMAT(created_at, '%Y-%m-%d')"; |
| 1615 |
} |
| 1616 |
if ($dim === 'week') { |
| 1617 |
return "DATE_FORMAT(created_at, '%x-W%v')"; |
| 1618 |
} |
| 1619 |
if ($dim === 'month') { |
| 1620 |
return "DATE_FORMAT(created_at, '%Y-%m')"; |
| 1621 |
} |
| 1622 |
if ($dim === 'order_type') { |
| 1623 |
// The order-type values (payment | renewal | subscription) live on the |
| 1624 |
// fct_orders.type column; expose it under the order_type alias so the |
| 1625 |
// dimension name and response key read naturally and don't collide |
| 1626 |
// with the unrelated payment_type on line items. |
| 1627 |
return 'type'; |
| 1628 |
} |
| 1629 |
if ($dim === 'country' || $dim === 'state') { |
| 1630 |
// ONE source per order, decided by whether the order has a billing |
| 1631 |
// address row at all (fc_oaddr.order_id IS NULL) — never per column. |
| 1632 |
// |
| 1633 |
// Coalescing each column independently mixes provenance inside a single |
| 1634 |
// order and invents places: order 16 on the reference store has its own |
| 1635 |
// billing row (BG, '') while its buyer's address says (BG, BG-22), so a |
| 1636 |
// per-column COALESCE reported that order's state as BG-22 — a value |
| 1637 |
// from a mutable customer record, for an order that carries its own |
| 1638 |
// address. 38 paid orders were affected. It is the same row-mixing |
| 1639 |
// latestRowPick() prevents inside a table, reappearing across tables. |
| 1640 |
// |
| 1641 |
// Consequence, deliberately: an order whose own address has a country |
| 1642 |
// but a blank state reports state 'unknown' rather than borrowing one. |
| 1643 |
// That is the honest answer — that order's address genuinely has no |
| 1644 |
// state — and it keeps historical attribution stable when a customer |
| 1645 |
// later edits their address. |
| 1646 |
// |
| 1647 |
// Orders neither source can place get the 'unknown' bucket rather than |
| 1648 |
// being dropped, so rows still sum to the period's total revenue and the |
| 1649 |
// size of the gap stays visible. |
| 1650 |
$pick = "CASE WHEN fc_oaddr.order_id IS NULL THEN fc_caddr.caddr_$dim ELSE fc_oaddr.oaddr_$dim END"; |
| 1651 |
|
| 1652 |
return "COALESCE(NULLIF($pick, ''), 'unknown')"; |
| 1653 |
} |
| 1654 |
return $dim; |
| 1655 |
} |
| 1656 |
|
| 1657 |
// ----------------------------------------------------------------- |
| 1658 |
// shared helpers |
| 1659 |
// ----------------------------------------------------------------- |
| 1660 |
|
| 1661 |
private static function currency($params) |
| 1662 |
{ |
| 1663 |
if (!empty($params['currency'])) { |
| 1664 |
return strtoupper(sanitize_text_field($params['currency'])); |
| 1665 |
} |
| 1666 |
return MCPHelper::currencyCode(); |
| 1667 |
} |
| 1668 |
|
| 1669 |
/** |
| 1670 |
* Effective order-mode filter: 'live', 'test', or 'all'. Reports have always |
| 1671 |
* counted BOTH live and test orders, so 'all' (the default) keeps existing |
| 1672 |
* numbers unchanged; an agent opts into 'live' for clean revenue. Applied to |
| 1673 |
* the fct_orders.mode column. |
| 1674 |
*/ |
| 1675 |
private static function orderMode($params) |
| 1676 |
{ |
| 1677 |
$m = isset($params['mode']) ? strtolower(sanitize_text_field((string) $params['mode'])) : 'all'; |
| 1678 |
return in_array($m, ['live', 'test'], true) ? $m : 'all'; |
| 1679 |
} |
| 1680 |
|
| 1681 |
/** |
| 1682 |
* Apply the mode filter to an Order query (or an order-relation subquery / |
| 1683 |
* whereHas closure). 'all' is a no-op so existing numbers are unchanged. The |
| 1684 |
* column is fct_orders.mode; pass a qualified name via $column when the orders |
| 1685 |
* table is aliased (e.g. query-sources uses 'o.mode'). |
| 1686 |
*/ |
| 1687 |
private static function applyMode($query, $mode, $column = 'mode') |
| 1688 |
{ |
| 1689 |
if ($mode !== 'all') { |
| 1690 |
$query->where($column, $mode); |
| 1691 |
} |
| 1692 |
return $query; |
| 1693 |
} |
| 1694 |
|
| 1695 |
/** |
| 1696 |
* Resolve range/start/end into a UTC window plus the prior equal-length |
| 1697 |
* window. Relative ranges are computed in store timezone, expressed in UTC. |
| 1698 |
* |
| 1699 |
* Public so the single source of truth for MCP date-window resolution is |
| 1700 |
* shared (e.g. list-transactions) instead of duplicated — every tool then |
| 1701 |
* accepts the identical range vocabulary and UTC semantics. |
| 1702 |
*/ |
| 1703 |
public static function resolveRange($params) |
| 1704 |
{ |
| 1705 |
// Resolve windows in UTC to match FluentCart's own admin reports, which |
| 1706 |
// bucket on the GMT-stored created_at (DATE_FORMAT(created_at, ...)) with |
| 1707 |
// no timezone conversion. Using store-local boundaries here would make a |
| 1708 |
// local day straddle two UTC dates and emit an extra trailing bucket. |
| 1709 |
$tz = new \DateTimeZone('UTC'); |
| 1710 |
|
| 1711 |
// Delta mode: everything strictly after an instant, up to now. Time-precise |
| 1712 |
// (not snapped to a day) so "what changed since 14:05" works during a launch. |
| 1713 |
if (!empty($params['since'])) { |
| 1714 |
$start = self::instant($params['since'], $tz, false); |
| 1715 |
if ($start !== null) { |
| 1716 |
return self::withPrior($start, gmdate('Y-m-d H:i:s'), 'since'); |
| 1717 |
} |
| 1718 |
} |
| 1719 |
|
| 1720 |
// Time-precise custom window (ISO 8601). A time of day is kept; a date-only |
| 1721 |
// value snaps to the day edge. Overrides range and the legacy start/end_date. |
| 1722 |
if (!empty($params['date_from']) || !empty($params['date_to'])) { |
| 1723 |
$start = self::instant(!empty($params['date_from']) ? $params['date_from'] : '-30 days', $tz, false); |
| 1724 |
$end = self::instant(!empty($params['date_to']) ? $params['date_to'] : 'now', $tz, true); |
| 1725 |
if ($start !== null && $end !== null) { |
| 1726 |
return self::withPrior($start, $end, 'custom'); |
| 1727 |
} |
| 1728 |
} |
| 1729 |
|
| 1730 |
if (!empty($params['start_date']) || !empty($params['end_date'])) { |
| 1731 |
$start = self::dayStart(!empty($params['start_date']) ? $params['start_date'] : '-30 days', $tz); |
| 1732 |
$end = self::dayEnd(!empty($params['end_date']) ? $params['end_date'] : 'now', $tz); |
| 1733 |
return self::withPrior($start, $end, !empty($params['start_date']) ? 'custom' : 'last_30_days'); |
| 1734 |
} |
| 1735 |
|
| 1736 |
$range = isset($params['range']) && in_array($params['range'], self::RANGES, true) ? $params['range'] : 'last_30_days'; |
| 1737 |
|
| 1738 |
// since_launch (alias: all_time): the store's first paid order to now. |
| 1739 |
// Needs a DB read, so it sits here rather than in the pure calendar math |
| 1740 |
// below. Falls back to the last 30 days if the store has no paid orders |
| 1741 |
// yet. Both names resolve identically — for a paid-order-scoped report the |
| 1742 |
// first paid order IS the start of all data — so an agent that learned |
| 1743 |
// all_time from get-product-financials succeeds here too. The label echoes |
| 1744 |
// whichever name was requested. |
| 1745 |
if ($range === 'since_launch' || $range === 'all_time') { |
| 1746 |
$launch = self::storeLaunchDate(); |
| 1747 |
$start = $launch ? $launch : self::dayStart('-30 days', $tz); |
| 1748 |
return self::withPrior($start, gmdate('Y-m-d H:i:s'), $range); |
| 1749 |
} |
| 1750 |
|
| 1751 |
$now = new \DateTime('now', $tz); |
| 1752 |
$startDt = clone $now; |
| 1753 |
$endDt = clone $now; |
| 1754 |
// Set for calendar-bounded ranges to force a calendar-aligned prior period. |
| 1755 |
$prevStartDt = null; |
| 1756 |
$prevEndDt = null; |
| 1757 |
|
| 1758 |
if ($range === 'yesterday') { |
| 1759 |
$startDt->modify('-1 day'); |
| 1760 |
$endDt->modify('-1 day'); |
| 1761 |
} elseif ($range === 'last_7_days') { |
| 1762 |
$startDt->modify('-6 days'); |
| 1763 |
} elseif ($range === 'last_30_days') { |
| 1764 |
$startDt->modify('-29 days'); |
| 1765 |
} elseif ($range === 'this_month' || $range === 'mtd') { |
| 1766 |
$startDt = new \DateTime($now->format('Y-m-01'), $tz); |
| 1767 |
} elseif ($range === 'last_month') { |
| 1768 |
$startDt = new \DateTime($now->format('Y-m-01'), $tz); |
| 1769 |
$startDt->modify('-1 month'); |
| 1770 |
$endDt = (clone $startDt)->modify('last day of this month'); |
| 1771 |
// Prior = the full calendar month before last month. |
| 1772 |
$prevStartDt = (clone $startDt)->modify('-1 month'); |
| 1773 |
$prevEndDt = (clone $prevStartDt)->modify('last day of this month'); |
| 1774 |
} elseif ($range === 'qtd') { |
| 1775 |
$startDt = self::quarterStart($now, $tz); |
| 1776 |
} elseif ($range === 'last_quarter') { |
| 1777 |
$qs = self::quarterStart($now, $tz); |
| 1778 |
$startDt = (clone $qs)->modify('-3 months'); |
| 1779 |
$endDt = (clone $qs)->modify('-1 day'); |
| 1780 |
// Prior = the full calendar quarter before last quarter. |
| 1781 |
$prevStartDt = (clone $startDt)->modify('-3 months'); |
| 1782 |
$prevEndDt = (clone $startDt)->modify('-1 day'); |
| 1783 |
} elseif ($range === 'ytd') { |
| 1784 |
$startDt = new \DateTime($now->format('Y-01-01'), $tz); |
| 1785 |
} elseif ($range === 'last_year') { |
| 1786 |
$year = (int) $now->format('Y') - 1; |
| 1787 |
$startDt = new \DateTime($year . '-01-01', $tz); |
| 1788 |
$endDt = new \DateTime($year . '-12-31', $tz); |
| 1789 |
// Prior = the full calendar year before last year. |
| 1790 |
$prevStartDt = new \DateTime(($year - 1) . '-01-01', $tz); |
| 1791 |
$prevEndDt = new \DateTime(($year - 1) . '-12-31', $tz); |
| 1792 |
} |
| 1793 |
|
| 1794 |
$start = self::dayStart($startDt->format('Y-m-d'), $tz); |
| 1795 |
$end = self::dayEnd($endDt->format('Y-m-d'), $tz); |
| 1796 |
|
| 1797 |
if ($prevStartDt !== null && $prevEndDt !== null) { |
| 1798 |
return self::withPrior( |
| 1799 |
$start, |
| 1800 |
$end, |
| 1801 |
$range, |
| 1802 |
self::dayStart($prevStartDt->format('Y-m-d'), $tz), |
| 1803 |
self::dayEnd($prevEndDt->format('Y-m-d'), $tz) |
| 1804 |
); |
| 1805 |
} |
| 1806 |
|
| 1807 |
return self::withPrior($start, $end, $range); |
| 1808 |
} |
| 1809 |
|
| 1810 |
private static function quarterStart($now, $tz) |
| 1811 |
{ |
| 1812 |
$month = (int) $now->format('n'); |
| 1813 |
$qStartMonth = (int) (floor(($month - 1) / 3) * 3 + 1); |
| 1814 |
return new \DateTime($now->format('Y') . '-' . str_pad($qStartMonth, 2, '0', STR_PAD_LEFT) . '-01', $tz); |
| 1815 |
} |
| 1816 |
|
| 1817 |
private static function withPrior($startUtc, $endUtc, $label, $prevStartUtc = null, $prevEndUtc = null) |
| 1818 |
{ |
| 1819 |
// Calendar-bounded ranges (last_month/last_quarter/last_year) pass an |
| 1820 |
// explicit prior *calendar* period so a 31-day month isn't compared to a |
| 1821 |
// 28-day second-count window. Other ranges fall back to an equal-length |
| 1822 |
// block ending 1s before start (exact for fixed-length, rolling, custom). |
| 1823 |
if ($prevStartUtc !== null && $prevEndUtc !== null) { |
| 1824 |
return [ |
| 1825 |
'start' => $startUtc, |
| 1826 |
'end' => $endUtc, |
| 1827 |
'prev_start' => $prevStartUtc, |
| 1828 |
'prev_end' => $prevEndUtc, |
| 1829 |
'label' => $label, |
| 1830 |
]; |
| 1831 |
} |
| 1832 |
|
| 1833 |
$s = new \DateTime($startUtc, new \DateTimeZone('UTC')); |
| 1834 |
$e = new \DateTime($endUtc, new \DateTimeZone('UTC')); |
| 1835 |
$lengthSec = $e->getTimestamp() - $s->getTimestamp(); |
| 1836 |
|
| 1837 |
$prevEnd = (clone $s)->modify('-1 second'); |
| 1838 |
$prevStart = (clone $prevEnd)->modify('-' . ($lengthSec + 1) . ' seconds'); |
| 1839 |
|
| 1840 |
return [ |
| 1841 |
'start' => $startUtc, |
| 1842 |
'end' => $endUtc, |
| 1843 |
'prev_start' => $prevStart->format('Y-m-d H:i:s'), |
| 1844 |
'prev_end' => $prevEnd->format('Y-m-d H:i:s'), |
| 1845 |
'label' => $label, |
| 1846 |
]; |
| 1847 |
} |
| 1848 |
|
| 1849 |
/** |
| 1850 |
* Parse an ISO-8601 / relative value to a UTC 'Y-m-d H:i:s'. A date-only input |
| 1851 |
* (YYYY-MM-DD) is snapped to the day start (or end when $isEnd); an explicit |
| 1852 |
* time is preserved. Returns null on an unparseable value so the caller can |
| 1853 |
* fall back to the next window source rather than silently matching all rows. |
| 1854 |
*/ |
| 1855 |
private static function instant($value, $tz, $isEnd = false) |
| 1856 |
{ |
| 1857 |
try { |
| 1858 |
$dt = new \DateTime((string) $value, $tz); |
| 1859 |
} catch (\Exception $e) { |
| 1860 |
return null; |
| 1861 |
} |
| 1862 |
if (preg_match('/^\d{4}-\d{2}-\d{2}$/', trim((string) $value))) { |
| 1863 |
$dt->setTime($isEnd ? 23 : 0, $isEnd ? 59 : 0, $isEnd ? 59 : 0); |
| 1864 |
} |
| 1865 |
$dt->setTimezone(new \DateTimeZone('UTC')); |
| 1866 |
return $dt->format('Y-m-d H:i:s'); |
| 1867 |
} |
| 1868 |
|
| 1869 |
/** |
| 1870 |
* The store's first paid order timestamp (UTC); null if the store has no paid |
| 1871 |
* orders yet. Only hit on the since_launch path (one indexed min per call), so |
| 1872 |
* it is not memoized — a static cache would leak across calls in a long-lived |
| 1873 |
* process (e.g. the test runner) for no real per-request gain. |
| 1874 |
*/ |
| 1875 |
private static function storeLaunchDate() |
| 1876 |
{ |
| 1877 |
$min = Order::query()->whereIn('payment_status', self::PAID)->min('created_at'); |
| 1878 |
return ($min && strpos((string) $min, '0000-00-00') !== 0) ? (string) $min : null; |
| 1879 |
} |
| 1880 |
|
| 1881 |
private static function dayStart($value, $tz) |
| 1882 |
{ |
| 1883 |
try { |
| 1884 |
$dt = new \DateTime((string) $value, $tz); |
| 1885 |
} catch (\Exception $e) { |
| 1886 |
$dt = new \DateTime('now', $tz); |
| 1887 |
} |
| 1888 |
$dt->setTime(0, 0, 0); |
| 1889 |
$dt->setTimezone(new \DateTimeZone('UTC')); |
| 1890 |
return $dt->format('Y-m-d H:i:s'); |
| 1891 |
} |
| 1892 |
|
| 1893 |
private static function dayEnd($value, $tz) |
| 1894 |
{ |
| 1895 |
try { |
| 1896 |
$dt = new \DateTime((string) $value, $tz); |
| 1897 |
} catch (\Exception $e) { |
| 1898 |
$dt = new \DateTime('now', $tz); |
| 1899 |
} |
| 1900 |
$dt->setTime(23, 59, 59); |
| 1901 |
$dt->setTimezone(new \DateTimeZone('UTC')); |
| 1902 |
return $dt->format('Y-m-d H:i:s'); |
| 1903 |
} |
| 1904 |
|
| 1905 |
private static function rangeBlock($range, $currency) |
| 1906 |
{ |
| 1907 |
return [ |
| 1908 |
'start' => MCPHelper::toIso8601($range['start']), |
| 1909 |
'end' => MCPHelper::toIso8601($range['end']), |
| 1910 |
'label' => $range['label'], |
| 1911 |
'currency' => $currency, |
| 1912 |
]; |
| 1913 |
} |
| 1914 |
|
| 1915 |
private static function pickList($params, $key, array $allowed, array $default) |
| 1916 |
{ |
| 1917 |
if (empty($params[$key]) || !is_array($params[$key])) { |
| 1918 |
return $default; |
| 1919 |
} |
| 1920 |
$out = []; |
| 1921 |
foreach ($params[$key] as $v) { |
| 1922 |
if (in_array($v, $allowed, true) && !in_array($v, $out, true)) { |
| 1923 |
$out[] = $v; |
| 1924 |
} |
| 1925 |
} |
| 1926 |
return $out ? $out : $default; |
| 1927 |
} |
| 1928 |
|
| 1929 |
/** |
| 1930 |
* Page/offset for the query-* aggregates. per_page defaults to and is clamped |
| 1931 |
* at MAX_ROWS — grouped rows are compact but a single page still can't exceed |
| 1932 |
* the context guardrail. Returns page, per_page and the row offset. |
| 1933 |
*/ |
| 1934 |
private static function queryPaging($params) |
| 1935 |
{ |
| 1936 |
$page = isset($params['page']) ? max(1, (int) $params['page']) : 1; |
| 1937 |
$perPage = isset($params['per_page']) ? (int) $params['per_page'] : self::MAX_ROWS; |
| 1938 |
if ($perPage < 1 || $perPage > self::MAX_ROWS) { |
| 1939 |
$perPage = self::MAX_ROWS; |
| 1940 |
} |
| 1941 |
return ['page' => $page, 'per_page' => $perPage, 'offset' => ($page - 1) * $perPage]; |
| 1942 |
} |
| 1943 |
|
| 1944 |
/** |
| 1945 |
* meta.page block for a query-* aggregate. The query fetches per_page + 1 rows |
| 1946 |
* to peek past the page boundary; $fetchedCount is that raw count. `truncated` |
| 1947 |
* is kept (never removed — additive API rule) and now means "more rows exist |
| 1948 |
* beyond this page"; raise `page` to fetch them. |
| 1949 |
*/ |
| 1950 |
private static function pageMeta($paging, $fetchedCount) |
| 1951 |
{ |
| 1952 |
$hasMore = $fetchedCount > $paging['per_page']; |
| 1953 |
return [ |
| 1954 |
'page' => [ |
| 1955 |
'current' => $paging['page'], |
| 1956 |
'per_page' => $paging['per_page'], |
| 1957 |
'has_more' => $hasMore, |
| 1958 |
], |
| 1959 |
'truncated' => $hasMore, |
| 1960 |
]; |
| 1961 |
} |
| 1962 |
|
| 1963 |
private static function pct($current, $prior) |
| 1964 |
{ |
| 1965 |
if ($prior == 0) { |
| 1966 |
return $current == 0 ? 0 : null; |
| 1967 |
} |
| 1968 |
return round((($current - $prior) / abs($prior)) * 100, 2); |
| 1969 |
} |
| 1970 |
} |
| 1971 |
|
