HposLegacyOrderReportQueryBuilder.php
937 lines
| 1 | <?php |
| 2 | declare( strict_types=1 ); |
| 3 | |
| 4 | namespace Automattic\WooCommerce\Internal\Admin\Reports; |
| 5 | |
| 6 | use Automattic\WooCommerce\Internal\DataStores\Orders\OrdersTableDataStore; |
| 7 | use Automattic\WooCommerce\Utilities\OrderUtil; |
| 8 | |
| 9 | defined( 'ABSPATH' ) || exit; |
| 10 | |
| 11 | /** |
| 12 | * Builds HPOS-backed SQL clauses for the legacy admin order reports. |
| 13 | * |
| 14 | * Used by {@see WC_Admin_Report::get_order_report_data()} when HPOS is the |
| 15 | * authoritative order store, so the same `data` / `where` / `where_meta` |
| 16 | * descriptors that legacy reports already pass continue to work against |
| 17 | * `wc_orders`, `wc_orders_meta` and `wc_order_operational_data` instead of |
| 18 | * `{$wpdb->posts}` / `{$wpdb->postmeta}`. |
| 19 | * |
| 20 | * @internal |
| 21 | * |
| 22 | * @since 11.1.0 |
| 23 | */ |
| 24 | class HposLegacyOrderReportQueryBuilder { |
| 25 | |
| 26 | /** |
| 27 | * Lazy cache for schema details (column mappings, gmt offset) used during one query build. |
| 28 | * |
| 29 | * @var array<string,mixed>|null |
| 30 | */ |
| 31 | private $report_schema = null; |
| 32 | |
| 33 | /** |
| 34 | * Lazy cache for the legacy `posts` column => HPOS column map used during one query build. |
| 35 | * |
| 36 | * @var array<string,string>|null |
| 37 | */ |
| 38 | private $column_map = null; |
| 39 | |
| 40 | /** |
| 41 | * Report date window for the current build as `[start, end)` Unix timestamps, |
| 42 | * or null when the query is not bounded by a caller-supplied range. |
| 43 | * |
| 44 | * @var array{0:int,1:int}|null |
| 45 | */ |
| 46 | private $report_range = null; |
| 47 | |
| 48 | /** |
| 49 | * Build the SQL clauses for an HPOS-backed legacy order report query. |
| 50 | * |
| 51 | * @since 11.1.0 |
| 52 | * |
| 53 | * @param array $args Parsed report arguments. |
| 54 | * @param int $start_date Start date as a Unix timestamp. |
| 55 | * @param int $end_date End date as a Unix timestamp. |
| 56 | * |
| 57 | * @return array<string,string> SQL clauses keyed by select/from/join/where/group_by/order_by/limit. |
| 58 | */ |
| 59 | public function build_query( array $args, int $start_date, int $end_date ): array { |
| 60 | // The class is resolved from the DI container as a shared instance: reset the |
| 61 | // per-build caches so option-derived state can't leak between queries. |
| 62 | $this->report_schema = null; |
| 63 | $this->column_map = null; |
| 64 | // Only trust the range when the query is actually bounded by it: queries with |
| 65 | // filter_range off (e.g. the sparklines) may still carry the report page's |
| 66 | // selected dates, which can describe a different DST period than the rows |
| 67 | // they fetch. |
| 68 | $this->report_range = ( ! empty( $args['filter_range'] ) && $start_date > 0 && $end_date > 0 ) |
| 69 | ? array( $start_date, (int) strtotime( '+1 DAY', $end_date ) ) |
| 70 | : null; |
| 71 | |
| 72 | $data = $args['data'] ?? array(); |
| 73 | $where = $args['where'] ?? array(); |
| 74 | $where_meta = $args['where_meta'] ?? array(); |
| 75 | $group_by = $args['group_by'] ?? ''; |
| 76 | $order_by = $args['order_by'] ?? ''; |
| 77 | $limit = $args['limit'] ?? ''; |
| 78 | $filter_range = $args['filter_range'] ?? false; |
| 79 | $order_types = $args['order_types'] ?? wc_get_order_types( 'reports' ); |
| 80 | $order_status = $args['order_status'] ?? array(); |
| 81 | $parent_order_status = $args['parent_order_status'] ?? false; |
| 82 | |
| 83 | $select = array(); |
| 84 | $joins = array(); |
| 85 | |
| 86 | foreach ( $data as $raw_key => $value ) { |
| 87 | $part = $this->build_data_select_and_joins( $raw_key, $value ); |
| 88 | if ( null === $part ) { |
| 89 | continue; |
| 90 | } |
| 91 | $select[] = $part['select']; |
| 92 | $joins = array_merge( $joins, $part['joins'] ); |
| 93 | } |
| 94 | |
| 95 | // The CPT path builds joins from `( $data + $where )`, so a `where` row carrying a |
| 96 | // `type` gets its join even without a matching `data` entry (rows shadowed by a |
| 97 | // `data` key are skipped, matching the array-union semantics). Such rows only ever |
| 98 | // contribute joins, never SELECT columns. |
| 99 | foreach ( $where as $raw_key => $value ) { |
| 100 | if ( is_array( $value ) && ! array_key_exists( $raw_key, $data ) ) { |
| 101 | $joins = array_merge( $joins, $this->resolve_type_joins( $raw_key, $value ) ); |
| 102 | } |
| 103 | } |
| 104 | |
| 105 | if ( ! empty( $where_meta ) ) { |
| 106 | foreach ( $where_meta as $value ) { |
| 107 | if ( is_array( $value ) ) { |
| 108 | $joins = array_merge( $joins, $this->build_where_meta_joins( $value ) ); |
| 109 | } |
| 110 | } |
| 111 | } |
| 112 | |
| 113 | if ( ! empty( $parent_order_status ) ) { |
| 114 | $joins = array_merge( $joins, $this->build_parent_orders_join() ); |
| 115 | } |
| 116 | |
| 117 | $query = array(); |
| 118 | $query['select'] = 'SELECT ' . implode( ',', $select ); |
| 119 | $query['from'] = 'FROM ' . OrderUtil::get_table_for_orders() . ' AS orders'; |
| 120 | $query['join'] = implode( ' ', $joins ); |
| 121 | $query['where'] = $this->build_where_clause( $order_types, $order_status, $parent_order_status, (bool) $filter_range, $start_date, $end_date ); |
| 122 | |
| 123 | if ( ! empty( $where_meta ) ) { |
| 124 | $query['where'] .= $this->build_where_meta_predicates( $where_meta ); |
| 125 | } |
| 126 | |
| 127 | if ( ! empty( $where ) ) { |
| 128 | $query['where'] .= $this->build_where_predicates( $where ); |
| 129 | } |
| 130 | |
| 131 | if ( $group_by ) { |
| 132 | $query['group_by'] = 'GROUP BY ' . $this->translate_legacy_sql_fragment( $group_by ); |
| 133 | } |
| 134 | |
| 135 | if ( $order_by ) { |
| 136 | $query['order_by'] = 'ORDER BY ' . $this->translate_legacy_sql_fragment( $order_by ); |
| 137 | } |
| 138 | |
| 139 | if ( $limit ) { |
| 140 | $query['limit'] = "LIMIT {$limit}"; |
| 141 | } |
| 142 | |
| 143 | return $query; |
| 144 | } |
| 145 | |
| 146 | /** |
| 147 | * Resolve a single `$data` entry into a SELECT fragment plus the JOINs it requires. |
| 148 | * |
| 149 | * @param string $raw_key Original key from the `$data` array. |
| 150 | * @param array $value The `$data` row. |
| 151 | * |
| 152 | * @return array{select: string, joins: array<string,string>}|null Null when the row has no resolvable type. |
| 153 | */ |
| 154 | private function build_data_select_and_joins( $raw_key, $value ) { |
| 155 | $key = sanitize_key( $raw_key ); |
| 156 | $distinct = isset( $value['distinct'] ) ? 'DISTINCT' : ''; |
| 157 | $join_type = $value['join_type'] ?? 'INNER'; |
| 158 | $type = $value['type'] ?? ''; |
| 159 | $get_key = ''; |
| 160 | $joins = array(); |
| 161 | |
| 162 | switch ( $type ) { |
| 163 | case 'meta': |
| 164 | list( $get_key, $joins ) = $this->resolve_meta_select( $raw_key, $key, $join_type ); |
| 165 | break; |
| 166 | case 'parent_meta': |
| 167 | list( $get_key, $joins ) = $this->resolve_parent_meta_select( $raw_key, $key, $join_type ); |
| 168 | break; |
| 169 | case 'post_data': |
| 170 | $get_key = $this->translate_post_column( $key ); |
| 171 | break; |
| 172 | case 'order_item_meta': |
| 173 | $get_key = "order_item_meta_{$key}.meta_value"; |
| 174 | $joins = $this->build_order_item_meta_joins( $key, $raw_key, $value['order_item_type'] ?? '', $join_type ); |
| 175 | break; |
| 176 | case 'order_item': |
| 177 | $get_key = "order_items.{$key}"; |
| 178 | $joins = $this->build_order_items_join( $join_type ); |
| 179 | break; |
| 180 | } |
| 181 | |
| 182 | if ( '' === $get_key ) { |
| 183 | return null; |
| 184 | } |
| 185 | |
| 186 | // Only bare selects get the legacy money format; inside an aggregate the raw |
| 187 | // column must be used, as per-row rounding would accumulate error where the |
| 188 | // CPT path sums the unrounded meta values. |
| 189 | if ( ! $value['function'] && $this->is_money_meta_key( $type, $raw_key ) ) { |
| 190 | $get_key = $this->format_money_column( $get_key ); |
| 191 | } |
| 192 | |
| 193 | $expr = $value['function'] |
| 194 | ? "{$value['function']}({$distinct} {$get_key})" |
| 195 | : "{$distinct} {$get_key}"; |
| 196 | |
| 197 | return array( |
| 198 | 'select' => "{$expr} as {$value['name']}", |
| 199 | 'joins' => $joins, |
| 200 | ); |
| 201 | } |
| 202 | |
| 203 | /** |
| 204 | * Resolve the JOINs required by a `data` or `where` row's `type`, without any SELECT part. |
| 205 | * |
| 206 | * @param string|int $raw_key Key of the row in its original array (a numeric index for `where` rows). |
| 207 | * @param array $value The row. |
| 208 | * |
| 209 | * @return array<string,string> JOINs keyed by alias. |
| 210 | */ |
| 211 | private function resolve_type_joins( $raw_key, array $value ): array { |
| 212 | $raw_key = (string) $raw_key; |
| 213 | $key = sanitize_key( $raw_key ); |
| 214 | $join_type = $value['join_type'] ?? 'INNER'; |
| 215 | |
| 216 | switch ( $value['type'] ?? '' ) { |
| 217 | case 'meta': |
| 218 | return $this->resolve_meta_select( $raw_key, $key, $join_type )[1]; |
| 219 | case 'parent_meta': |
| 220 | return $this->resolve_parent_meta_select( $raw_key, $key, $join_type )[1]; |
| 221 | case 'order_item_meta': |
| 222 | return $this->build_order_item_meta_joins( $key, $raw_key, $value['order_item_type'] ?? '', $join_type ); |
| 223 | case 'order_item': |
| 224 | return $this->build_order_items_join( $join_type ); |
| 225 | } |
| 226 | |
| 227 | return array(); |
| 228 | } |
| 229 | |
| 230 | /** |
| 231 | * Resolve a `type=meta` SELECT. |
| 232 | * |
| 233 | * @param string $raw_key Meta key. |
| 234 | * @param string $key Sanitized version of the meta key. |
| 235 | * @param string $join_type Join type. |
| 236 | * |
| 237 | * @return array{0: string, 1: array<string,string>} SELECT fragment and JOINs. |
| 238 | */ |
| 239 | private function resolve_meta_select( $raw_key, $key, $join_type ) { |
| 240 | $schema = $this->get_report_schema(); |
| 241 | if ( isset( $schema['order_column'][ $raw_key ] ) ) { |
| 242 | return array( $schema['order_column'][ $raw_key ], array() ); |
| 243 | } |
| 244 | if ( isset( $schema['op_data_column'][ $raw_key ] ) ) { |
| 245 | return array( $schema['op_data_column'][ $raw_key ], $this->build_op_data_join() ); |
| 246 | } |
| 247 | if ( isset( $schema['address_column'][ $raw_key ] ) ) { |
| 248 | list( $address_type, $column ) = $schema['address_column'][ $raw_key ]; |
| 249 | return array( "address_{$address_type}.{$column}", $this->build_address_join( $address_type ) ); |
| 250 | } |
| 251 | |
| 252 | return array( |
| 253 | "meta_{$key}.meta_value", |
| 254 | $this->build_meta_join( $key, $raw_key, $join_type ), |
| 255 | ); |
| 256 | } |
| 257 | |
| 258 | /** |
| 259 | * Resolve a `type=parent_meta` SELECT. |
| 260 | * |
| 261 | * @param string $raw_key Meta key on the parent order. |
| 262 | * @param string $key Sanitized version of the meta key. |
| 263 | * @param string $join_type Join type. |
| 264 | * |
| 265 | * @return array{0: string, 1: array<string,string>} SELECT fragment and JOINs. |
| 266 | */ |
| 267 | private function resolve_parent_meta_select( $raw_key, $key, $join_type ) { |
| 268 | $schema = $this->get_report_schema(); |
| 269 | if ( isset( $schema['order_column'][ $raw_key ] ) ) { |
| 270 | $column = substr( $schema['order_column'][ $raw_key ], strlen( 'orders.' ) ); |
| 271 | return array( "parent_orders.{$column}", $this->build_parent_orders_join() ); |
| 272 | } |
| 273 | if ( isset( $schema['op_data_column'][ $raw_key ] ) ) { |
| 274 | $column = substr( $schema['op_data_column'][ $raw_key ], strlen( 'op_data.' ) ); |
| 275 | $joins = array_merge( $this->build_parent_orders_join(), $this->build_parent_op_data_join() ); |
| 276 | return array( "parent_op_data.{$column}", $joins ); |
| 277 | } |
| 278 | if ( isset( $schema['address_column'][ $raw_key ] ) ) { |
| 279 | list( $address_type, $column ) = $schema['address_column'][ $raw_key ]; |
| 280 | $joins = array_merge( |
| 281 | $this->build_parent_orders_join(), |
| 282 | $this->build_address_join( $address_type, 'parent_orders', 'parent_address' ) |
| 283 | ); |
| 284 | return array( "parent_address_{$address_type}.{$column}", $joins ); |
| 285 | } |
| 286 | |
| 287 | return array( |
| 288 | "parent_meta_{$key}.meta_value", |
| 289 | $this->build_parent_meta_join( $key, $raw_key, $join_type ), |
| 290 | ); |
| 291 | } |
| 292 | |
| 293 | /** |
| 294 | * Whether a data row resolves to a mapped HPOS money column. |
| 295 | * |
| 296 | * @param string $type Row type. |
| 297 | * @param string $raw_key Original meta key. |
| 298 | * |
| 299 | * @return bool |
| 300 | */ |
| 301 | private function is_money_meta_key( $type, $raw_key ): bool { |
| 302 | if ( 'meta' !== $type && 'parent_meta' !== $type ) { |
| 303 | return false; |
| 304 | } |
| 305 | |
| 306 | $schema = $this->get_report_schema(); |
| 307 | return isset( $schema['order_column'][ $raw_key ] ) || isset( $schema['op_data_column'][ $raw_key ] ); |
| 308 | } |
| 309 | |
| 310 | /** |
| 311 | * Wrap a mapped money column so bare SELECTs match the legacy meta format. |
| 312 | * |
| 313 | * HPOS money columns are DECIMAL(26,8), so selecting one raw yields e.g. |
| 314 | * '50.00000000' where the CPT meta stores '50.00'. Rounding to the store's |
| 315 | * price decimals restores the legacy format. Never applied inside aggregate |
| 316 | * functions: several money metas are stored at full precision, so per-row |
| 317 | * rounding would drift from the CPT sums. |
| 318 | * |
| 319 | * @param string $column Qualified column reference. |
| 320 | * |
| 321 | * @return string SQL expression. |
| 322 | */ |
| 323 | private function format_money_column( string $column ): string { |
| 324 | return "ROUND({$column}, " . wc_get_price_decimals() . ')'; |
| 325 | } |
| 326 | |
| 327 | /** |
| 328 | * Build the JOIN onto `wc_order_addresses` for one address type. |
| 329 | * |
| 330 | * @param string $address_type 'billing' or 'shipping'. |
| 331 | * @param string $order_alias Orders-table alias to join from. |
| 332 | * @param string $alias_prefix Prefix for the address-table alias. |
| 333 | * |
| 334 | * @return array<string,string> JOIN keyed by alias. |
| 335 | */ |
| 336 | private function build_address_join( string $address_type, string $order_alias = 'orders', string $alias_prefix = 'address' ): array { |
| 337 | $alias = "{$alias_prefix}_{$address_type}"; |
| 338 | return array( |
| 339 | $alias => 'LEFT JOIN ' . OrdersTableDataStore::get_addresses_table_name() . " AS {$alias} ON ( {$order_alias}.id = {$alias}.order_id AND {$alias}.address_type = '{$address_type}' )", |
| 340 | ); |
| 341 | } |
| 342 | |
| 343 | /** |
| 344 | * Build the JOINs required to satisfy a `where_meta` predicate. |
| 345 | * |
| 346 | * @param array $value A `where_meta` row. |
| 347 | * |
| 348 | * @return array<string,string> JOINs keyed by alias. |
| 349 | */ |
| 350 | private function build_where_meta_joins( $value ) { |
| 351 | $type = $value['type'] ?? ''; |
| 352 | $join_type = $value['join_type'] ?? 'INNER'; |
| 353 | $meta_key = $value['meta_key']; |
| 354 | $key = sanitize_key( is_array( $meta_key ) ? $meta_key[0] . '_array' : $meta_key ); |
| 355 | |
| 356 | if ( 'order_item_meta' === $type ) { |
| 357 | global $wpdb; |
| 358 | $alias = "order_item_meta_{$key}"; |
| 359 | return array( |
| 360 | 'order_items' => "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON orders.id = order_items.order_id", |
| 361 | $alias => "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS {$alias} ON order_items.order_item_id = {$alias}.order_item_id", |
| 362 | ); |
| 363 | } |
| 364 | |
| 365 | $schema = $this->get_report_schema(); |
| 366 | if ( ! is_array( $meta_key ) && isset( $schema['where_meta_column'][ $meta_key ] ) ) { |
| 367 | return array(); |
| 368 | } |
| 369 | if ( ! is_array( $meta_key ) && isset( $schema['address_column'][ $meta_key ] ) ) { |
| 370 | return $this->build_address_join( $schema['address_column'][ $meta_key ][0] ); |
| 371 | } |
| 372 | |
| 373 | $alias = "meta_{$key}"; |
| 374 | return array( $alias => "{$join_type} JOIN " . OrderUtil::get_table_for_order_meta() . " AS {$alias} ON orders.id = {$alias}.order_id" ); |
| 375 | } |
| 376 | |
| 377 | /** |
| 378 | * Build the JOIN required to look up parent order data. |
| 379 | * |
| 380 | * @return array<string,string> JOIN keyed by alias. |
| 381 | */ |
| 382 | private function build_parent_orders_join() { |
| 383 | return array( |
| 384 | 'parent_orders' => 'LEFT JOIN ' . OrderUtil::get_table_for_orders() . ' AS parent_orders ON orders.parent_order_id = parent_orders.id', |
| 385 | ); |
| 386 | } |
| 387 | |
| 388 | /** |
| 389 | * Build the JOIN required to look up the parent order's operational-data row. |
| 390 | * |
| 391 | * @return array<string,string> JOIN keyed by alias. |
| 392 | */ |
| 393 | private function build_parent_op_data_join() { |
| 394 | return array( |
| 395 | 'parent_op_data' => 'LEFT JOIN ' . OrdersTableDataStore::get_operational_data_table_name() . ' AS parent_op_data ON parent_orders.id = parent_op_data.order_id', |
| 396 | ); |
| 397 | } |
| 398 | |
| 399 | /** |
| 400 | * Build the JOIN onto `wc_order_operational_data`. |
| 401 | * |
| 402 | * @return array<string,string> JOIN keyed by alias. |
| 403 | */ |
| 404 | private function build_op_data_join() { |
| 405 | return array( |
| 406 | 'op_data' => 'LEFT JOIN ' . OrdersTableDataStore::get_operational_data_table_name() . ' AS op_data ON orders.id = op_data.order_id', |
| 407 | ); |
| 408 | } |
| 409 | |
| 410 | /** |
| 411 | * Build the JOIN onto the orders-meta table for an order meta lookup. |
| 412 | * |
| 413 | * @param string $key Sanitized meta key for the alias. |
| 414 | * @param string $raw_key Original meta key. |
| 415 | * @param string $join_type Join type. |
| 416 | * |
| 417 | * @return array<string,string> JOIN keyed by alias. |
| 418 | */ |
| 419 | private function build_meta_join( $key, $raw_key, $join_type ) { |
| 420 | $alias = "meta_{$key}"; |
| 421 | return array( |
| 422 | $alias => "{$join_type} JOIN " . OrderUtil::get_table_for_order_meta() . " AS {$alias} ON ( orders.id = {$alias}.order_id AND {$alias}.meta_key = '{$raw_key}' )", |
| 423 | ); |
| 424 | } |
| 425 | |
| 426 | /** |
| 427 | * Build the JOIN onto the orders-meta table keyed on the parent order id. |
| 428 | * |
| 429 | * @param string $key Sanitized meta key for the alias. |
| 430 | * @param string $raw_key Original meta key. |
| 431 | * @param string $join_type Join type. |
| 432 | * |
| 433 | * @return array<string,string> JOIN keyed by alias. |
| 434 | */ |
| 435 | private function build_parent_meta_join( $key, $raw_key, $join_type ) { |
| 436 | $alias = "parent_meta_{$key}"; |
| 437 | return array( |
| 438 | $alias => "{$join_type} JOIN " . OrderUtil::get_table_for_order_meta() . " AS {$alias} ON (orders.parent_order_id = {$alias}.order_id) AND ({$alias}.meta_key = '{$raw_key}')", |
| 439 | ); |
| 440 | } |
| 441 | |
| 442 | /** |
| 443 | * Build the JOIN onto `wp_woocommerce_order_items`. |
| 444 | * |
| 445 | * @param string $join_type Join type. |
| 446 | * |
| 447 | * @return array<string,string> JOIN keyed by alias. |
| 448 | */ |
| 449 | private function build_order_items_join( $join_type ) { |
| 450 | global $wpdb; |
| 451 | return array( |
| 452 | 'order_items' => "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON orders.id = order_items.order_id", |
| 453 | ); |
| 454 | } |
| 455 | |
| 456 | /** |
| 457 | * Build the JOIN pair used by `type=order_item_meta` data entries. |
| 458 | * |
| 459 | * @param string $key Sanitized meta key for the itemmeta alias. |
| 460 | * @param string $raw_key Original meta key. |
| 461 | * @param string $order_item_type Optional order-item type filter. |
| 462 | * @param string $join_type Join type. |
| 463 | * |
| 464 | * @return array<string,string> JOINs keyed by alias. |
| 465 | */ |
| 466 | private function build_order_item_meta_joins( $key, $raw_key, $order_item_type, $join_type ) { |
| 467 | global $wpdb; |
| 468 | $items_join = "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_items AS order_items ON (orders.id = order_items.order_id)"; |
| 469 | |
| 470 | if ( '' !== $order_item_type ) { |
| 471 | $items_join .= " AND (order_items.order_item_type = '{$order_item_type}')"; |
| 472 | } |
| 473 | |
| 474 | $itemmeta_alias = "order_item_meta_{$key}"; |
| 475 | return array( |
| 476 | 'order_items' => $items_join, |
| 477 | $itemmeta_alias => "{$join_type} JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS {$itemmeta_alias} ON " . |
| 478 | "(order_items.order_item_id = {$itemmeta_alias}.order_item_id) " . |
| 479 | " AND ({$itemmeta_alias}.meta_key = '{$raw_key}')", |
| 480 | ); |
| 481 | } |
| 482 | |
| 483 | /** |
| 484 | * Build the top-level WHERE clause. |
| 485 | * |
| 486 | * @param array $order_types Order types to restrict to. |
| 487 | * @param array|false $order_status Order statuses without the `wc-` prefix. |
| 488 | * @param array|false $parent_order_status Parent order statuses without the `wc-` prefix. |
| 489 | * @param bool $filter_range Whether to bound the query by date. |
| 490 | * @param int $start_date Start date as a Unix timestamp. |
| 491 | * @param int $end_date End date as a Unix timestamp. |
| 492 | * |
| 493 | * @return string WHERE clause. |
| 494 | */ |
| 495 | private function build_where_clause( $order_types, $order_status, $parent_order_status, $filter_range, $start_date, $end_date ) { |
| 496 | $clause = " |
| 497 | WHERE orders.type IN ( '" . implode( "','", $order_types ) . "' ) |
| 498 | "; |
| 499 | |
| 500 | if ( ! empty( $order_status ) ) { |
| 501 | $clause .= " |
| 502 | AND orders.status IN ( 'wc-" . implode( "','wc-", $order_status ) . "') |
| 503 | "; |
| 504 | } |
| 505 | |
| 506 | if ( ! empty( $parent_order_status ) ) { |
| 507 | $clause .= $this->build_parent_order_status_where_clause( $parent_order_status, $order_status ); |
| 508 | } |
| 509 | |
| 510 | if ( $filter_range ) { |
| 511 | $clause .= $this->build_filter_range_where_clause( $start_date, $end_date ); |
| 512 | } |
| 513 | |
| 514 | return $clause; |
| 515 | } |
| 516 | |
| 517 | /** |
| 518 | * Build the WHERE fragment that filters by the parent order's status. |
| 519 | * |
| 520 | * @param array $parent_order_status Status slugs without `wc-` prefix. |
| 521 | * @param array|false $order_status If non-empty, parent-status NULL is allowed. |
| 522 | * |
| 523 | * @return string WHERE fragment. |
| 524 | */ |
| 525 | private function build_parent_order_status_where_clause( $parent_order_status, $order_status ) { |
| 526 | $statuses_in = "'wc-" . implode( "','wc-", $parent_order_status ) . "'"; |
| 527 | |
| 528 | if ( ! empty( $order_status ) ) { |
| 529 | return " AND ( parent_orders.status IN ( {$statuses_in} ) OR parent_orders.id IS NULL ) "; |
| 530 | } |
| 531 | |
| 532 | return " AND parent_orders.status IN ( {$statuses_in} ) "; |
| 533 | } |
| 534 | |
| 535 | /** |
| 536 | * Build the WHERE fragment that bounds the query by report date range. |
| 537 | * |
| 538 | * @param int $start_date Start date as a Unix timestamp. |
| 539 | * @param int $end_date End date as a Unix timestamp. |
| 540 | * |
| 541 | * @return string WHERE fragment. |
| 542 | */ |
| 543 | private function build_filter_range_where_clause( $start_date, $end_date ) { |
| 544 | $start_gmt = $this->local_timestamp_to_gmt( (int) $start_date ); |
| 545 | $end_gmt = $this->local_timestamp_to_gmt( (int) strtotime( '+1 DAY', $end_date ) ); |
| 546 | |
| 547 | return " |
| 548 | AND orders.date_created_gmt >= '{$start_gmt}' |
| 549 | AND orders.date_created_gmt < '{$end_gmt}' |
| 550 | "; |
| 551 | } |
| 552 | |
| 553 | /** |
| 554 | * Convert a WordPress "local" timestamp (one that reads as site-local wall-clock time |
| 555 | * when formatted with {@see gmdate()}) into the equivalent GMT datetime string. |
| 556 | * |
| 557 | * Uses the site timezone via {@see wp_timezone()} so the offset is resolved for that |
| 558 | * specific date. On timezones that observe DST this yields the correct UTC instant for |
| 559 | * the boundary even when the report range spans a different offset period than "now", |
| 560 | * unlike a single cached `gmt_offset`. |
| 561 | * |
| 562 | * @param int $local_ts Local timestamp to convert. |
| 563 | * |
| 564 | * @return string GMT datetime in `Y-m-d H:i:s` format. |
| 565 | */ |
| 566 | private function local_timestamp_to_gmt( int $local_ts ): string { |
| 567 | return (string) $this->local_datetime_to_gmt( gmdate( 'Y-m-d H:i:s', $local_ts ) ); |
| 568 | } |
| 569 | |
| 570 | /** |
| 571 | * Convert a site-local date or datetime string into the equivalent GMT datetime string. |
| 572 | * |
| 573 | * Only plain `Y-m-d`, `Y-m-d H:i` and `Y-m-d H:i:s` values are converted; anything else |
| 574 | * (including relative formats) returns null so callers can fall back to per-row SQL |
| 575 | * conversion instead of guessing at the caller's intent. |
| 576 | * |
| 577 | * @param string $local_datetime Local date or datetime string. |
| 578 | * |
| 579 | * @return string|null GMT datetime in `Y-m-d H:i:s` format, or null when not convertible. |
| 580 | */ |
| 581 | private function local_datetime_to_gmt( string $local_datetime ): ?string { |
| 582 | if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}( \d{2}:\d{2}(:\d{2})?)?$/', $local_datetime ) ) { |
| 583 | return null; |
| 584 | } |
| 585 | |
| 586 | try { |
| 587 | $local = new \DateTimeImmutable( $local_datetime, wp_timezone() ); |
| 588 | } catch ( \Exception $e ) { |
| 589 | return null; |
| 590 | } |
| 591 | |
| 592 | return $local->setTimezone( new \DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' ); |
| 593 | } |
| 594 | |
| 595 | /** |
| 596 | * Build the WHERE fragment for caller-supplied `where_meta` predicates. |
| 597 | * |
| 598 | * @param array $where_meta The original `where_meta` argument. |
| 599 | * |
| 600 | * @return string WHERE fragment. |
| 601 | */ |
| 602 | private function build_where_meta_predicates( $where_meta ) { |
| 603 | $schema = $this->get_report_schema(); |
| 604 | $relation = $where_meta['relation'] ?? 'AND'; |
| 605 | |
| 606 | $clause = ' AND ('; |
| 607 | $first = true; |
| 608 | |
| 609 | foreach ( $where_meta as $value ) { |
| 610 | if ( ! is_array( $value ) ) { |
| 611 | continue; |
| 612 | } |
| 613 | |
| 614 | $where_value = $this->prepare_predicate_value( $value, 'meta_value' ); |
| 615 | if ( '' === $where_value ) { |
| 616 | continue; |
| 617 | } |
| 618 | |
| 619 | if ( ! $first ) { |
| 620 | $clause .= ' ' . $relation; |
| 621 | } |
| 622 | $first = false; |
| 623 | |
| 624 | $meta_key = $value['meta_key']; |
| 625 | $key = sanitize_key( is_array( $meta_key ) ? $meta_key[0] . '_array' : $meta_key ); |
| 626 | |
| 627 | if ( isset( $value['type'] ) && 'order_item_meta' === $value['type'] ) { |
| 628 | if ( is_array( $meta_key ) ) { |
| 629 | $clause .= " ( order_item_meta_{$key}.meta_key IN ('" . implode( "','", $meta_key ) . "')"; |
| 630 | } else { |
| 631 | $clause .= " ( order_item_meta_{$key}.meta_key = '{$meta_key}'"; |
| 632 | } |
| 633 | $clause .= " AND order_item_meta_{$key}.meta_value {$where_value} )"; |
| 634 | } elseif ( ! is_array( $meta_key ) && isset( $schema['where_meta_column'][ $meta_key ] ) ) { |
| 635 | $clause .= ' ( ' . $schema['where_meta_column'][ $meta_key ] . " {$where_value} )"; |
| 636 | } elseif ( ! is_array( $meta_key ) && isset( $schema['address_column'][ $meta_key ] ) ) { |
| 637 | list( $address_type, $column ) = $schema['address_column'][ $meta_key ]; |
| 638 | $clause .= " ( address_{$address_type}.{$column} {$where_value} )"; |
| 639 | } else { |
| 640 | if ( is_array( $meta_key ) ) { |
| 641 | $clause .= " ( meta_{$key}.meta_key IN ('" . implode( "','", $meta_key ) . "')"; |
| 642 | } else { |
| 643 | $clause .= " ( meta_{$key}.meta_key = '{$meta_key}'"; |
| 644 | } |
| 645 | $clause .= " AND meta_{$key}.meta_value {$where_value} )"; |
| 646 | } |
| 647 | } |
| 648 | |
| 649 | return $clause . ')'; |
| 650 | } |
| 651 | |
| 652 | /** |
| 653 | * Build the WHERE fragment for caller-supplied `where` predicates. |
| 654 | * |
| 655 | * @param array $where Caller-supplied predicates. |
| 656 | * |
| 657 | * @return string WHERE fragment. |
| 658 | */ |
| 659 | private function build_where_predicates( $where ) { |
| 660 | $clause = ''; |
| 661 | |
| 662 | foreach ( $where as $value ) { |
| 663 | $where_value = $this->prepare_predicate_value( $value, 'value' ); |
| 664 | if ( '' === $where_value ) { |
| 665 | continue; |
| 666 | } |
| 667 | |
| 668 | $predicate = $this->build_gmt_date_predicate( $value ); |
| 669 | if ( null === $predicate ) { |
| 670 | $predicate = $this->translate_legacy_sql_fragment( $value['key'] ) . " {$where_value}"; |
| 671 | } |
| 672 | |
| 673 | $clause .= ' AND ' . $predicate; |
| 674 | } |
| 675 | |
| 676 | return $clause; |
| 677 | } |
| 678 | |
| 679 | /** |
| 680 | * Build a sargable predicate on `orders.date_created_gmt` for a plain `post_date` where row. |
| 681 | * |
| 682 | * Translating `post_date` wraps the column in the per-row local-time expression, which |
| 683 | * stops MySQL from using the date index. For the common shape — a bare `post_date` key, |
| 684 | * a scalar comparison operator and a date/datetime value (e.g. the sales sparkline's |
| 685 | * only date bound, `post_date > 'Y-m-d'`) — the boundary is converted to UTC in PHP |
| 686 | * instead and compared against the raw GMT column, matching what |
| 687 | * {@see self::build_filter_range_where_clause()} does for `filter_range` bounds. |
| 688 | * |
| 689 | * @param array $value A `where` row. |
| 690 | * |
| 691 | * @return string|null Predicate SQL, or null when the row is not a plain post_date comparison. |
| 692 | */ |
| 693 | private function build_gmt_date_predicate( array $value ): ?string { |
| 694 | $key = trim( (string) ( $value['key'] ?? '' ) ); |
| 695 | if ( 'post_date' !== $key && 'posts.post_date' !== $key ) { |
| 696 | return null; |
| 697 | } |
| 698 | |
| 699 | $rhs = $value['value'] ?? ''; |
| 700 | if ( ! in_array( $value['operator'], array( '=', '!=', '<', '<=', '>', '>=' ), true ) || ! is_string( $rhs ) ) { |
| 701 | return null; |
| 702 | } |
| 703 | |
| 704 | $gmt = $this->local_datetime_to_gmt( $rhs ); |
| 705 | if ( null === $gmt ) { |
| 706 | return null; |
| 707 | } |
| 708 | |
| 709 | global $wpdb; |
| 710 | return "orders.date_created_gmt {$value['operator']} " . $wpdb->prepare( '%s', $gmt ); |
| 711 | } |
| 712 | |
| 713 | /** |
| 714 | * Prepare the right-hand side of a predicate (e.g. `= '5'` or `IN ('a','b')`). |
| 715 | * |
| 716 | * Used by both the `where` (`value` / `operator`) and `where_meta` |
| 717 | * (`meta_value` / `operator`) builders. |
| 718 | * |
| 719 | * @param array $value A `where` or `where_meta` row. |
| 720 | * @param string $value_key The array key holding the RHS value (`value` or `meta_value`). |
| 721 | * |
| 722 | * @return string SQL fragment, or empty string when no predicate is emitted. |
| 723 | */ |
| 724 | private function prepare_predicate_value( array $value, string $value_key ): string { |
| 725 | global $wpdb; |
| 726 | $op_lc = strtolower( $value['operator'] ); |
| 727 | $rhs = $value[ $value_key ] ?? ''; |
| 728 | |
| 729 | if ( 'in' === $op_lc || 'not in' === $op_lc ) { |
| 730 | if ( ! empty( $rhs ) && ! is_array( $rhs ) ) { |
| 731 | $rhs = (array) $rhs; |
| 732 | } |
| 733 | if ( empty( $rhs ) ) { |
| 734 | return ''; |
| 735 | } |
| 736 | $formats = implode( ', ', array_fill( 0, count( $rhs ), '%s' ) ); |
| 737 | return $value['operator'] . ' (' . $wpdb->prepare( $formats, $rhs ) . ')'; // @phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 738 | } |
| 739 | |
| 740 | return $value['operator'] . ' ' . $wpdb->prepare( '%s', $rhs ); |
| 741 | } |
| 742 | |
| 743 | /** |
| 744 | * Get HPOS schema details used by the report SQL builder (column mappings and gmt offset). |
| 745 | * |
| 746 | * @return array<string,mixed> |
| 747 | */ |
| 748 | private function get_report_schema(): array { |
| 749 | if ( null !== $this->report_schema ) { |
| 750 | return $this->report_schema; |
| 751 | } |
| 752 | |
| 753 | $this->report_schema = array( |
| 754 | 'gmt_offset' => (int) ( (float) get_option( 'gmt_offset' ) * HOUR_IN_SECONDS ), |
| 755 | 'order_column' => array( |
| 756 | '_order_total' => 'orders.total_amount', |
| 757 | '_order_tax' => 'orders.tax_amount', |
| 758 | // On a refund row, wc_orders.total_amount stores the negative refund total. |
| 759 | // Legacy callers expect the positive `_refund_amount` meta value, so it |
| 760 | // is intentionally not mapped here. |
| 761 | ), |
| 762 | 'op_data_column' => array( |
| 763 | '_order_shipping' => 'op_data.shipping_total_amount', |
| 764 | '_order_shipping_tax' => 'op_data.shipping_tax_amount', |
| 765 | ), |
| 766 | 'where_meta_column' => array( |
| 767 | '_customer_user' => 'orders.customer_id', |
| 768 | ), |
| 769 | 'address_column' => $this->build_address_column_map(), |
| 770 | ); |
| 771 | |
| 772 | return $this->report_schema; |
| 773 | } |
| 774 | |
| 775 | /** |
| 776 | * Map legacy `_billing_*` / `_shipping_*` meta keys to `wc_order_addresses` columns. |
| 777 | * |
| 778 | * Under HPOS these fields live in the addresses table, not in order meta, so a |
| 779 | * meta join would return no rows for them. |
| 780 | * |
| 781 | * @return array<string,array{0:string,1:string}> Meta key => [address_type, column]. |
| 782 | */ |
| 783 | private function build_address_column_map(): array { |
| 784 | $fields = array( 'first_name', 'last_name', 'company', 'address_1', 'address_2', 'city', 'state', 'postcode', 'country', 'email', 'phone' ); |
| 785 | $map = array(); |
| 786 | |
| 787 | foreach ( array( 'billing', 'shipping' ) as $address_type ) { |
| 788 | foreach ( $fields as $field ) { |
| 789 | if ( 'shipping' === $address_type && 'email' === $field ) { |
| 790 | continue; |
| 791 | } |
| 792 | $map[ "_{$address_type}_{$field}" ] = array( $address_type, $field ); |
| 793 | } |
| 794 | } |
| 795 | |
| 796 | return $map; |
| 797 | } |
| 798 | |
| 799 | /** |
| 800 | * Map of bare legacy `posts` column names to their HPOS equivalents. |
| 801 | * |
| 802 | * Source of truth shared by {@see self::translate_post_column()} and |
| 803 | * {@see self::translate_legacy_sql_fragment()}. |
| 804 | * |
| 805 | * @return array<string,string> |
| 806 | */ |
| 807 | private function legacy_to_hpos_column_map(): array { |
| 808 | if ( null !== $this->column_map ) { |
| 809 | return $this->column_map; |
| 810 | } |
| 811 | |
| 812 | $this->column_map = array( |
| 813 | 'ID' => 'orders.id', |
| 814 | 'post_date' => $this->hpos_local_date_expr(), |
| 815 | 'post_parent' => 'orders.parent_order_id', |
| 816 | 'post_status' => 'orders.status', |
| 817 | 'post_type' => 'orders.type', |
| 818 | ); |
| 819 | |
| 820 | return $this->column_map; |
| 821 | } |
| 822 | |
| 823 | /** |
| 824 | * Translate a `post_data` column key to an HPOS column reference. |
| 825 | * |
| 826 | * @param string $key Sanitized `post_data` key from the caller. |
| 827 | * |
| 828 | * @return string SQL fragment referencing the equivalent HPOS column. |
| 829 | */ |
| 830 | private function translate_post_column( string $key ): string { |
| 831 | $map = $this->legacy_to_hpos_column_map(); |
| 832 | |
| 833 | // `sanitize_key()` lowercases `ID` to `id`; treat both forms as the orders.id column. |
| 834 | if ( 'id' === $key ) { |
| 835 | return $map['ID']; |
| 836 | } |
| 837 | return $map[ $key ] ?? "orders.{$key}"; |
| 838 | } |
| 839 | |
| 840 | /** |
| 841 | * Translate legacy `posts.<col>` (and bare `ID` / `post_date`) references in an arbitrary SQL fragment. |
| 842 | * |
| 843 | * Matches are bounded by `\b` so tokens embedded in longer identifiers |
| 844 | * (e.g. `product_ID`, `posts.post_date_gmt`) are left untouched. |
| 845 | * |
| 846 | * @param string $fragment Caller-supplied SQL fragment. |
| 847 | * |
| 848 | * @return string Translated fragment safe to drop into an HPOS query. |
| 849 | */ |
| 850 | private function translate_legacy_sql_fragment( string $fragment ): string { |
| 851 | $map = $this->legacy_to_hpos_column_map(); |
| 852 | |
| 853 | // Qualified `posts.<col>` references first, then the bare tokens legacy |
| 854 | // callers use unqualified. Callbacks avoid `$`/`\` replacement-string pitfalls. |
| 855 | $fragment = (string) preg_replace_callback( |
| 856 | '/\bposts\.(ID|post_date|post_parent|post_status|post_type)\b/', |
| 857 | static function ( $matches ) use ( $map ) { |
| 858 | return $map[ $matches[1] ]; |
| 859 | }, |
| 860 | $fragment |
| 861 | ); |
| 862 | |
| 863 | return (string) preg_replace_callback( |
| 864 | '/\b(ID|post_date)\b/', |
| 865 | static function ( $matches ) use ( $map ) { |
| 866 | return $map[ $matches[1] ]; |
| 867 | }, |
| 868 | $fragment |
| 869 | ); |
| 870 | } |
| 871 | |
| 872 | /** |
| 873 | * Build a MySQL expression that converts `orders.date_created_gmt` into site-local time. |
| 874 | * |
| 875 | * For sites configured with a named timezone (e.g. `Europe/Berlin`) the conversion is done |
| 876 | * per row with `CONVERT_TZ()` so DST is honoured and rows near midnight bucket into the |
| 877 | * correct day/month. `CONVERT_TZ()` returns NULL when the server's timezone tables are not |
| 878 | * loaded, so it falls back to {@see self::build_transition_fallback_expr()}. Sites using a |
| 879 | * manual UTC offset have no DST, so a fixed-offset shift is exact and is used directly. |
| 880 | * |
| 881 | * @return string SQL fragment that produces a local DATETIME. |
| 882 | */ |
| 883 | private function hpos_local_date_expr(): string { |
| 884 | $timezone_string = get_option( 'timezone_string' ); |
| 885 | if ( '' === $timezone_string ) { |
| 886 | $schema = $this->get_report_schema(); |
| 887 | return "DATE_ADD(orders.date_created_gmt, INTERVAL {$schema['gmt_offset']} SECOND)"; |
| 888 | } |
| 889 | |
| 890 | global $wpdb; |
| 891 | $convert = $wpdb->prepare( 'CONVERT_TZ(orders.date_created_gmt, %s, %s)', '+00:00', $timezone_string ); |
| 892 | |
| 893 | return "IFNULL({$convert}, {$this->build_transition_fallback_expr()})"; |
| 894 | } |
| 895 | |
| 896 | /** |
| 897 | * Build the fallback used when `CONVERT_TZ()` can't resolve the named timezone |
| 898 | * (MySQL timezone tables not loaded). |
| 899 | * |
| 900 | * A single shift by the current `gmt_offset` would be wrong for rows created in a |
| 901 | * different DST period, so the site timezone's DST transitions inside the report |
| 902 | * window are baked into a CASE expression and each row is shifted by the offset in |
| 903 | * effect when it was created. Without a caller-supplied range (e.g. the sparklines) |
| 904 | * the window covers the last year, which spans any window such callers use. |
| 905 | * |
| 906 | * @return string SQL fragment that produces a local DATETIME. |
| 907 | */ |
| 908 | private function build_transition_fallback_expr(): string { |
| 909 | list( $window_start, $window_end ) = $this->report_range ?? array( time() - YEAR_IN_SECONDS, time() + DAY_IN_SECONDS ); |
| 910 | |
| 911 | $transitions = wp_timezone()->getTransitions( $window_start, $window_end ); |
| 912 | if ( ! is_array( $transitions ) || array() === $transitions ) { |
| 913 | $schema = $this->get_report_schema(); |
| 914 | return "DATE_ADD(orders.date_created_gmt, INTERVAL {$schema['gmt_offset']} SECOND)"; |
| 915 | } |
| 916 | |
| 917 | // The first entry describes the offset already in effect at the window start; |
| 918 | // each later entry is a transition inside the window. |
| 919 | $case = ''; |
| 920 | $count = count( $transitions ); |
| 921 | for ( $i = 0; $i < $count - 1; $i++ ) { |
| 922 | $boundary = gmdate( 'Y-m-d H:i:s', $transitions[ $i + 1 ]['ts'] ); |
| 923 | $offset = (int) $transitions[ $i ]['offset']; |
| 924 | $case .= "WHEN orders.date_created_gmt < '{$boundary}' THEN DATE_ADD(orders.date_created_gmt, INTERVAL {$offset} SECOND) "; |
| 925 | } |
| 926 | |
| 927 | $last_offset = (int) $transitions[ $count - 1 ]['offset']; |
| 928 | $last_shift = "DATE_ADD(orders.date_created_gmt, INTERVAL {$last_offset} SECOND)"; |
| 929 | |
| 930 | if ( '' === $case ) { |
| 931 | return $last_shift; |
| 932 | } |
| 933 | |
| 934 | return "CASE {$case}ELSE {$last_shift} END"; |
| 935 | } |
| 936 | } |
| 937 |