| 1 |
<?php |
| 2 |
|
| 3 |
/** |
| 4 |
* CampaignQuery — focused queries for campaign data. |
| 5 |
* |
| 6 |
* @package Disco |
| 7 |
* @subpackage Disco\App\Analytics\Queries |
| 8 |
* @since 1.3.37 |
| 9 |
*/ |
| 10 |
|
| 11 |
namespace Disco\App\Analytics\Queries; |
| 12 |
|
| 13 |
/** |
| 14 |
* Handles all single-purpose SQL queries related to campaigns. |
| 15 |
* |
| 16 |
* Each method does exactly ONE job. |
| 17 |
* LEFT JOINs to the campaigns table preserve data for deleted campaigns. |
| 18 |
*/ |
| 19 |
class CampaignQuery extends BaseQuery { |
| 20 |
|
| 21 |
/** |
| 22 |
* Only completed orders count toward campaign metrics. |
| 23 |
* |
| 24 |
* Keeps orders_count / revenue / customers_count from including on-hold, |
| 25 |
* cancelled, refunded, or other non-final order statuses. |
| 26 |
*/ |
| 27 |
private const STATUS_COMPLETED = 'wc-completed'; |
| 28 |
|
| 29 |
// ========================================================================= |
| 30 |
// List / paginated queries (used by CampaignService for REST list endpoints) |
| 31 |
// ========================================================================= |
| 32 |
|
| 33 |
/** |
| 34 |
* Returns a paginated campaign list with aggregate order metrics. |
| 35 |
* |
| 36 |
* Runs three separate queries: |
| 37 |
* 1. COUNT — how many distinct campaigns match the filters. |
| 38 |
* 2. AGG — paginated campaign_id + metrics (no JOIN with campaigns table). |
| 39 |
* 3. META — campaign name/intent/status/data for the IDs found in step 2. |
| 40 |
* PHP merges the results. No subqueries, no multi-level prepare issues. |
| 41 |
* |
| 42 |
* @param array $args search, date_from, date_to, sort_by, page, per_page. |
| 43 |
* @return array { total: int, pages: int, rows: array } |
| 44 |
*/ |
| 45 |
public function get_campaign_list( array $args ): array { |
| 46 |
$context = $this->build_list_context( $args ); |
| 47 |
$pagination = $this->resolve_pagination( $args ); |
| 48 |
$sort = $this->resolve_campaign_sort( $args ); |
| 49 |
|
| 50 |
// Name search matched no campaigns — nothing can match downstream. |
| 51 |
if ( $context['no_match'] ) { |
| 52 |
return $this->format_list_result( 0, $pagination['per_page'], array() ); |
| 53 |
} |
| 54 |
|
| 55 |
// QUERY 1: COUNT distinct campaigns. |
| 56 |
$total = $this->run_count( $this->build_count_sql( $context ), $context['params'] ); |
| 57 |
|
| 58 |
if ( 0 === $total ) { |
| 59 |
return $this->format_list_result( 0, $pagination['per_page'], array() ); |
| 60 |
} |
| 61 |
|
| 62 |
// QUERY 2: Aggregated metrics per campaign (paginated). |
| 63 |
$agg_rows = $this->fetch_campaign_aggregates( $context, $sort, $pagination ); |
| 64 |
|
| 65 |
if ( empty( $agg_rows ) ) { |
| 66 |
return $this->format_list_result( $total, $pagination['per_page'], array() ); |
| 67 |
} |
| 68 |
|
| 69 |
// QUERY 3 + merge: campaign meta mapped onto aggregated rows. |
| 70 |
$campaign_map = $this->fetch_campaign_meta_map( $agg_rows, $context['tables'] ); |
| 71 |
$rows = $this->merge_campaign_rows( $agg_rows, $campaign_map ); |
| 72 |
|
| 73 |
return $this->format_list_result( $total, $pagination['per_page'], $rows ); |
| 74 |
} |
| 75 |
|
| 76 |
/** |
| 77 |
* Returns aggregate metrics for a single campaign in a period. |
| 78 |
* |
| 79 |
* @param int $campaign_id Campaign ID. |
| 80 |
* @param array $period { from: string, to: string } — empty strings skip date filter. |
| 81 |
* @return object|null |
| 82 |
*/ |
| 83 |
public function get_campaign( int $campaign_id, array $period ) { |
| 84 |
$tables = $this->get_tables(); |
| 85 |
|
| 86 |
/** @var object{campaign_id: int|string, orders_count: int|string, customers_count: int|string, revenue: string|null}|null $aggregates */ |
| 87 |
$aggregates = $this->fetch_campaign_aggregate_row( $campaign_id, $period, $tables ); |
| 88 |
|
| 89 |
if ( ! $aggregates ) { |
| 90 |
return null; |
| 91 |
} |
| 92 |
|
| 93 |
// QUERY 2 + merge: campaign meta resolved separately, like the list query. |
| 94 |
$campaign_map = $this->fetch_campaign_meta_map( array( $aggregates ), $tables ); |
| 95 |
$campaign = $campaign_map[ $campaign_id ] ?? null; |
| 96 |
|
| 97 |
return (object) array( |
| 98 |
'campaign_id' => (int) $aggregates->campaign_id, |
| 99 |
'intent' => $campaign->intent ?? 'Unknown', |
| 100 |
'status' => $this->resolve_campaign_status( $campaign ), |
| 101 |
'is_deleted' => null === $campaign ? 1 : 0, |
| 102 |
'campaign_data' => $campaign->data ?? null, |
| 103 |
'orders_count' => $aggregates->orders_count, |
| 104 |
'customers_count' => $aggregates->customers_count, |
| 105 |
'revenue' => $aggregates->revenue, |
| 106 |
); |
| 107 |
} |
| 108 |
|
| 109 |
/** |
| 110 |
* Fetches the aggregate metrics row for a single campaign (no campaigns JOIN). |
| 111 |
* |
| 112 |
* @param int $campaign_id Campaign ID. |
| 113 |
* @param array $period { from: string, to: string } — empty strings skip date filter. |
| 114 |
* @param array $tables From get_tables(). |
| 115 |
* @return object|null Aggregate row, or null when the campaign has no matching orders. |
| 116 |
*/ |
| 117 |
private function fetch_campaign_aggregate_row( int $campaign_id, array $period, array $tables ) { |
| 118 |
global $wpdb; |
| 119 |
|
| 120 |
$clauses = $this->get_order_clauses( $tables ); |
| 121 |
$order_id_column = $tables['order_id_col']; |
| 122 |
$where_conditions = array( 'order_meta.meta_key = %s', 'CAST(order_meta.meta_value AS UNSIGNED) = %d', 'o.status = %s', $this->get_campaign_dedup_condition( $tables ) ); |
| 123 |
$query_params = array( 'disco_campaign', $campaign_id, self::STATUS_COMPLETED ); |
| 124 |
|
| 125 |
if ( ! empty( $period['from'] ) ) { |
| 126 |
$where_conditions[] = "{$clauses['date_col']} >= %s"; |
| 127 |
$query_params[] = $period['from'] . ' 00:00:00'; |
| 128 |
} |
| 129 |
|
| 130 |
if ( ! empty( $period['to'] ) ) { |
| 131 |
$where_conditions[] = "{$clauses['date_col']} <= %s"; |
| 132 |
$query_params[] = $period['to'] . ' 23:59:59'; |
| 133 |
} |
| 134 |
|
| 135 |
$where_clause = 'WHERE ' . implode( ' AND ', $where_conditions ); |
| 136 |
|
| 137 |
$sql = "SELECT |
| 138 |
CAST(order_meta.meta_value AS UNSIGNED) AS campaign_id, |
| 139 |
COUNT(DISTINCT order_meta.{$order_id_column}) AS orders_count, |
| 140 |
COUNT(DISTINCT CASE WHEN {$clauses['customer_expr']} != 0 THEN CAST({$clauses['customer_expr']} AS CHAR) ELSE o.billing_email END) AS customers_count, |
| 141 |
SUM({$clauses['total_expr']} - COALESCE(order_stats.shipping_total, 0)) AS revenue |
| 142 |
FROM {$tables['order_meta']} order_meta |
| 143 |
JOIN {$tables['orders']} o ON o.ID = order_meta.{$order_id_column} AND {$clauses['status_where']} |
| 144 |
LEFT JOIN {$wpdb->prefix}wc_order_stats order_stats ON order_stats.order_id = order_meta.{$order_id_column} |
| 145 |
{$clauses['total_join']} |
| 146 |
{$clauses['customer_join']} |
| 147 |
{$where_clause} |
| 148 |
GROUP BY CAST(order_meta.meta_value AS UNSIGNED)"; |
| 149 |
|
| 150 |
return $this->run_row( $sql, $query_params ); |
| 151 |
} |
| 152 |
|
| 153 |
/** |
| 154 |
* Builds WHERE conditions and params for the campaign list filters. |
| 155 |
* |
| 156 |
* Numeric search matches the campaign ID. Text search pre-resolves matching |
| 157 |
* campaign IDs in a separate small query, so the COUNT and aggregate queries |
| 158 |
* need no campaigns JOIN and no per-row JSON_EXTRACT; no_match short-circuits |
| 159 |
* the list to an empty result. |
| 160 |
* |
| 161 |
* @param array $args search, date_from, date_to, etc. |
| 162 |
* @return array { tables, clauses, id_col, where, params, no_match } |
| 163 |
*/ |
| 164 |
private function build_list_context( array $args ): array { |
| 165 |
$tables = $this->get_tables(); |
| 166 |
$clauses = $this->get_order_clauses( $tables ); |
| 167 |
$common = $this->build_common_conditions( $args, $clauses, $tables ); |
| 168 |
$conditions = array_merge( |
| 169 |
array( 'order_meta.meta_key = %s', 'o.status = %s', $this->get_campaign_dedup_condition( $tables ) ), |
| 170 |
$common['conditions'] |
| 171 |
); |
| 172 |
$params = array_merge( array( 'disco_campaign', self::STATUS_COMPLETED ), $common['params'] ); |
| 173 |
|
| 174 |
$search = $args['search'] ?? ''; |
| 175 |
$no_match = false; |
| 176 |
|
| 177 |
if ( is_numeric( $search ) && '' !== $search ) { |
| 178 |
// Sargable string compare so the (meta_key, meta_value) index is used. |
| 179 |
$conditions[] = 'order_meta.meta_value = %s'; |
| 180 |
$params[] = (string) (int) $search; |
| 181 |
} elseif ( ! empty( $search ) ) { |
| 182 |
$matching_ids = $this->find_campaign_ids_by_name( $search, $tables ); |
| 183 |
|
| 184 |
if ( empty( $matching_ids ) ) { |
| 185 |
$no_match = true; |
| 186 |
} else { |
| 187 |
$id_placeholders = implode( ',', array_fill( 0, count( $matching_ids ), '%s' ) ); |
| 188 |
// Sargable string compare so the (meta_key, meta_value) index is used. |
| 189 |
$conditions[] = "order_meta.meta_value IN ({$id_placeholders})"; |
| 190 |
$params = array_merge( $params, $matching_ids ); |
| 191 |
} |
| 192 |
} |
| 193 |
|
| 194 |
return array( |
| 195 |
'tables' => $tables, |
| 196 |
'clauses' => $clauses, |
| 197 |
'id_col' => $tables['order_id_col'], |
| 198 |
'where' => 'WHERE ' . implode( ' AND ', $conditions ), |
| 199 |
'params' => $params, |
| 200 |
'no_match' => $no_match, |
| 201 |
); |
| 202 |
} |
| 203 |
|
| 204 |
/** |
| 205 |
* Returns the IDs of campaigns whose JSON name matches the search term. |
| 206 |
* |
| 207 |
* The campaigns table only holds user-created campaigns, so the result |
| 208 |
* (and the IN list built from it) stays small and bounded. |
| 209 |
* |
| 210 |
* @param string $search Raw search term. |
| 211 |
* @param array $tables From get_tables(). |
| 212 |
* @return array<string> Campaign IDs as bare digit strings (matching meta_value storage). |
| 213 |
*/ |
| 214 |
private function find_campaign_ids_by_name( string $search, array $tables ): array { |
| 215 |
global $wpdb; |
| 216 |
|
| 217 |
$rows = $this->run_rows( |
| 218 |
"SELECT id FROM {$tables['campaigns']} WHERE LOWER(JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) LIKE LOWER(%s)", |
| 219 |
array( '%' . $wpdb->esc_like( $search ) . '%' ) |
| 220 |
); |
| 221 |
|
| 222 |
return array_map( |
| 223 |
function ( $row ) { |
| 224 |
return (string) (int) $row->id; |
| 225 |
}, |
| 226 |
$rows |
| 227 |
); |
| 228 |
} |
| 229 |
|
| 230 |
/** |
| 231 |
* Resolves the aggregate column and direction to sort the campaign list by. |
| 232 |
* |
| 233 |
* Accepts sort_by (REST param) or orderby; whitelist: orders, customers, revenue. |
| 234 |
* |
| 235 |
* @param array $args sort_by / orderby, order. |
| 236 |
* @return array { column: string, direction: 'ASC'|'DESC' } |
| 237 |
*/ |
| 238 |
private function resolve_campaign_sort( array $args ): array { |
| 239 |
$args['orderby'] = $args['sort_by'] ?? $args['orderby'] ?? ''; |
| 240 |
|
| 241 |
$sort = $this->resolve_sort( $args, array( 'orders', 'customers', 'revenue' ), 'revenue' ); |
| 242 |
|
| 243 |
$sort_col_map = array( |
| 244 |
'orders' => 'total_orders', |
| 245 |
'customers' => 'total_customers', |
| 246 |
'revenue' => 'total_revenue', |
| 247 |
); |
| 248 |
|
| 249 |
return array( |
| 250 |
'column' => $sort_col_map[ $sort['orderby'] ], |
| 251 |
'direction' => $sort['direction'], |
| 252 |
); |
| 253 |
} |
| 254 |
|
| 255 |
/** |
| 256 |
* Builds the COUNT(DISTINCT campaign) SQL for the current filters. |
| 257 |
* |
| 258 |
* @param array $context From build_list_context(). |
| 259 |
* @return string COUNT SQL with placeholders matching context params. |
| 260 |
*/ |
| 261 |
private function build_count_sql( array $context ): string { |
| 262 |
$tables = $context['tables']; |
| 263 |
$clauses = $context['clauses']; |
| 264 |
|
| 265 |
return "SELECT COUNT(DISTINCT CAST(order_meta.meta_value AS UNSIGNED)) |
| 266 |
FROM {$tables['order_meta']} order_meta |
| 267 |
JOIN {$tables['orders']} o ON o.ID = order_meta.{$context['id_col']} AND {$clauses['status_where']} |
| 268 |
{$context['where']}"; |
| 269 |
} |
| 270 |
|
| 271 |
/** |
| 272 |
* Fetches the paginated per-campaign aggregate rows (orders, customers, revenue). |
| 273 |
* |
| 274 |
* No JOIN with the campaigns table — meta is resolved separately so deleted |
| 275 |
* campaigns keep their metrics. |
| 276 |
* |
| 277 |
* @param array $context From build_list_context(). |
| 278 |
* @param array $sort From resolve_campaign_sort(). |
| 279 |
* @param array $pagination From resolve_pagination(). |
| 280 |
* @return array Aggregate row objects. |
| 281 |
*/ |
| 282 |
private function fetch_campaign_aggregates( array $context, array $sort, array $pagination ): array { |
| 283 |
global $wpdb; |
| 284 |
|
| 285 |
$tables = $context['tables']; |
| 286 |
$clauses = $context['clauses']; |
| 287 |
$order_id_column = $context['id_col']; |
| 288 |
|
| 289 |
$agg_sql = "SELECT |
| 290 |
CAST(order_meta.meta_value AS UNSIGNED) AS campaign_id, |
| 291 |
COUNT(DISTINCT order_meta.{$order_id_column}) AS total_orders, |
| 292 |
COUNT(DISTINCT CASE WHEN {$clauses['customer_expr']} != 0 THEN CAST({$clauses['customer_expr']} AS CHAR) ELSE o.billing_email END) AS total_customers, |
| 293 |
SUM({$clauses['total_expr']} - COALESCE(order_stats.shipping_total, 0)) AS total_revenue |
| 294 |
FROM {$tables['order_meta']} order_meta |
| 295 |
JOIN {$tables['orders']} o ON o.ID = order_meta.{$order_id_column} AND {$clauses['status_where']} |
| 296 |
LEFT JOIN {$wpdb->prefix}wc_order_stats order_stats ON order_stats.order_id = order_meta.{$order_id_column} |
| 297 |
{$context['where']} |
| 298 |
GROUP BY CAST(order_meta.meta_value AS UNSIGNED) |
| 299 |
ORDER BY {$sort['column']} {$sort['direction']} |
| 300 |
LIMIT %d OFFSET %d"; |
| 301 |
|
| 302 |
$params = array_merge( $context['params'], array( $pagination['per_page'], $pagination['offset'] ) ); |
| 303 |
|
| 304 |
return $this->run_rows( $agg_sql, $params ); |
| 305 |
} |
| 306 |
|
| 307 |
/** |
| 308 |
* Fetches id/intent/status/data for the campaigns present in the aggregate rows. |
| 309 |
* |
| 310 |
* @param array $agg_rows Aggregate rows from fetch_campaign_aggregates(). |
| 311 |
* @param array $tables From get_tables(). |
| 312 |
* @return array Map of campaign_id => campaign row object. |
| 313 |
*/ |
| 314 |
private function fetch_campaign_meta_map( array $agg_rows, array $tables ): array { |
| 315 |
$campaign_ids = array_map( |
| 316 |
function ( $row ) { |
| 317 |
return (int) $row->campaign_id; |
| 318 |
}, |
| 319 |
$agg_rows |
| 320 |
); |
| 321 |
|
| 322 |
$placeholders = implode( ',', array_fill( 0, count( $campaign_ids ), '%d' ) ); |
| 323 |
|
| 324 |
$campaign_rows = $this->run_rows( |
| 325 |
"SELECT id, intent, status, data FROM {$tables['campaigns']} WHERE id IN ({$placeholders})", |
| 326 |
$campaign_ids |
| 327 |
); |
| 328 |
|
| 329 |
$campaign_map = array(); |
| 330 |
|
| 331 |
foreach ( $campaign_rows as $campaign_row ) { |
| 332 |
$campaign_map[ (int) $campaign_row->id ] = $campaign_row; |
| 333 |
} |
| 334 |
|
| 335 |
return $campaign_map; |
| 336 |
} |
| 337 |
|
| 338 |
/** |
| 339 |
* Merges campaign meta (name/intent/status) onto the aggregated metric rows. |
| 340 |
* |
| 341 |
* Campaigns missing from the map are flagged 'deleted' with name 'Unknown'. |
| 342 |
* |
| 343 |
* @param array $agg_rows Aggregate rows from fetch_campaign_aggregates(). |
| 344 |
* @param array $campaign_map Map from fetch_campaign_meta_map(). |
| 345 |
* @return array Final merged row objects. |
| 346 |
*/ |
| 347 |
private function merge_campaign_rows( array $agg_rows, array $campaign_map ): array { |
| 348 |
$rows = array(); |
| 349 |
|
| 350 |
foreach ( $agg_rows as $row ) { |
| 351 |
$cid = (int) $row->campaign_id; |
| 352 |
$campaign = $campaign_map[ $cid ] ?? null; |
| 353 |
|
| 354 |
$rows[] = (object) array( |
| 355 |
'campaign_id' => $cid, |
| 356 |
'campaign_name' => $this->resolve_campaign_name( $campaign ), |
| 357 |
'intent' => $campaign->intent ?? 'Unknown', |
| 358 |
'status' => $this->resolve_campaign_status( $campaign ), |
| 359 |
'campaign_data' => $campaign->data ?? null, |
| 360 |
'total_orders' => $row->total_orders, |
| 361 |
'total_customers' => $row->total_customers, |
| 362 |
'total_revenue' => $row->total_revenue, |
| 363 |
); |
| 364 |
} |
| 365 |
|
| 366 |
return $rows; |
| 367 |
} |
| 368 |
|
| 369 |
/** |
| 370 |
* Resolves the display name from a campaign row's JSON data column. |
| 371 |
* |
| 372 |
* @param object|null $campaign Campaign row, or null when deleted. |
| 373 |
* @return string Campaign name, 'Unknown' when unresolvable. |
| 374 |
* @phpstan-param object{data: string|null}|null $campaign |
| 375 |
*/ |
| 376 |
private function resolve_campaign_name( ?object $campaign ): string { |
| 377 |
if ( null === $campaign ) { |
| 378 |
return 'Unknown'; |
| 379 |
} |
| 380 |
|
| 381 |
$campaign_data = json_decode( (string) $campaign->data, true ); |
| 382 |
$name = ''; |
| 383 |
|
| 384 |
if ( is_array( $campaign_data ) ) { |
| 385 |
$name = $campaign_data['name'] ?? ''; |
| 386 |
} |
| 387 |
|
| 388 |
if ( '' !== $name ) { |
| 389 |
return $name; |
| 390 |
} |
| 391 |
|
| 392 |
return 'Unknown'; |
| 393 |
} |
| 394 |
|
| 395 |
/** |
| 396 |
* Resolves the status label for a campaign row. |
| 397 |
* |
| 398 |
* @param object|null $campaign Campaign row, or null when deleted. |
| 399 |
* @return string One of 'deleted' | 'active' | 'inactive'. |
| 400 |
* @phpstan-param object{status: string|null}|null $campaign |
| 401 |
*/ |
| 402 |
private function resolve_campaign_status( ?object $campaign ): string { |
| 403 |
if ( null === $campaign ) { |
| 404 |
return 'deleted'; |
| 405 |
} |
| 406 |
|
| 407 |
if ( '1' === $campaign->status ) { |
| 408 |
return 'active'; |
| 409 |
} |
| 410 |
|
| 411 |
return 'inactive'; |
| 412 |
} |
| 413 |
|
| 414 |
} |
| 415 |
|
