CustomOrdersTableController.php
3 years ago
DataSynchronizer.php
3 years ago
OrdersTableDataStore.php
3 years ago
OrdersTableDataStoreMeta.php
3 years ago
OrdersTableFieldQuery.php
3 years ago
OrdersTableMetaQuery.php
3 years ago
OrdersTableQuery.php
3 years ago
OrdersTableRefundDataStore.php
3 years ago
OrdersTableSearchQuery.php
3 years ago
OrdersTableQuery.php
1263 lines
| 1 | <?php |
| 2 | // phpcs:disable Generic.Commenting.Todo.TaskFound |
| 3 | /** |
| 4 | * OrdersTableQuery class file. |
| 5 | */ |
| 6 | |
| 7 | namespace Automattic\WooCommerce\Internal\DataStores\Orders; |
| 8 | |
| 9 | use Automattic\WooCommerce\Internal\Utilities\DatabaseUtil; |
| 10 | |
| 11 | defined( 'ABSPATH' ) || exit; |
| 12 | |
| 13 | /** |
| 14 | * This class provides a `WP_Query`-like interface to custom order tables. |
| 15 | * |
| 16 | * @property-read int $found_orders Number of found orders. |
| 17 | * @property-read int $found_posts Alias of the `$found_orders` property. |
| 18 | * @property-read int $max_num_pages Max number of pages matching the current query. |
| 19 | * @property-read array $orders Order objects, or order IDs. |
| 20 | * @property-read array $posts Alias of the $orders property. |
| 21 | */ |
| 22 | class OrdersTableQuery { |
| 23 | |
| 24 | /** |
| 25 | * Values to ignore when parsing query arguments. |
| 26 | */ |
| 27 | public const SKIPPED_VALUES = array( '', array(), null ); |
| 28 | |
| 29 | /** |
| 30 | * Regex used to catch "shorthand" comparisons in date-related query args. |
| 31 | */ |
| 32 | public const REGEX_SHORTHAND_DATES = '/([^.<>]*)(>=|<=|>|<|\.\.\.)([^.<>]+)/'; |
| 33 | |
| 34 | /** |
| 35 | * Highest possible unsigned bigint value (unsigned bigints being the type of the `id` column). |
| 36 | * |
| 37 | * This is deliberately held as a string, rather than a numeric type, for inclusion within queries. |
| 38 | */ |
| 39 | private const MYSQL_MAX_UNSIGNED_BIGINT = '18446744073709551615'; |
| 40 | |
| 41 | /** |
| 42 | * Names of all COT tables (orders, addresses, operational_data, meta) in the form 'table_id' => 'table name'. |
| 43 | * |
| 44 | * @var array |
| 45 | */ |
| 46 | private $tables = array(); |
| 47 | |
| 48 | /** |
| 49 | * Column mappings for all COT tables. |
| 50 | * |
| 51 | * @var array |
| 52 | */ |
| 53 | private $mappings = array(); |
| 54 | |
| 55 | /** |
| 56 | * Query vars after processing and sanitization. |
| 57 | * |
| 58 | * @var array |
| 59 | */ |
| 60 | private $args = array(); |
| 61 | |
| 62 | /** |
| 63 | * Columns to be selected in the SELECT clause. |
| 64 | * |
| 65 | * @var array |
| 66 | */ |
| 67 | private $fields = array(); |
| 68 | |
| 69 | /** |
| 70 | * Array of table aliases and conditions used to compute the JOIN clause of the query. |
| 71 | * |
| 72 | * @var array |
| 73 | */ |
| 74 | private $join = array(); |
| 75 | |
| 76 | /** |
| 77 | * Array of fields and conditions used to compute the WHERE clause of the query. |
| 78 | * |
| 79 | * @var array |
| 80 | */ |
| 81 | private $where = array(); |
| 82 | |
| 83 | /** |
| 84 | * Field to be used in the GROUP BY clause of the query. |
| 85 | * |
| 86 | * @var array |
| 87 | */ |
| 88 | private $groupby = array(); |
| 89 | |
| 90 | /** |
| 91 | * Array of fields used to compute the ORDER BY clause of the query. |
| 92 | * |
| 93 | * @var array |
| 94 | */ |
| 95 | private $orderby = array(); |
| 96 | |
| 97 | /** |
| 98 | * Limits used to compute the LIMIT clause of the query. |
| 99 | * |
| 100 | * @var array |
| 101 | */ |
| 102 | private $limits = array(); |
| 103 | |
| 104 | /** |
| 105 | * Results (order IDs) for the current query. |
| 106 | * |
| 107 | * @var array |
| 108 | */ |
| 109 | private $results = array(); |
| 110 | |
| 111 | /** |
| 112 | * Final SQL query to run after processing of args. |
| 113 | * |
| 114 | * @var string |
| 115 | */ |
| 116 | private $sql = ''; |
| 117 | |
| 118 | /** |
| 119 | * Final SQL query to count results after processing of args. |
| 120 | * |
| 121 | * @var string |
| 122 | */ |
| 123 | private $count_sql = ''; |
| 124 | |
| 125 | /** |
| 126 | * The number of pages (when pagination is enabled). |
| 127 | * |
| 128 | * @var int |
| 129 | */ |
| 130 | private $max_num_pages = 0; |
| 131 | |
| 132 | /** |
| 133 | * The number of orders found. |
| 134 | * |
| 135 | * @var int |
| 136 | */ |
| 137 | private $found_orders = 0; |
| 138 | |
| 139 | /** |
| 140 | * Field query parser. |
| 141 | * |
| 142 | * @var OrdersTableFieldQuery |
| 143 | */ |
| 144 | private $field_query = null; |
| 145 | |
| 146 | /** |
| 147 | * Meta query parser. |
| 148 | * |
| 149 | * @var OrdersTableMetaQuery |
| 150 | */ |
| 151 | private $meta_query = null; |
| 152 | |
| 153 | /** |
| 154 | * Search query parser. |
| 155 | * |
| 156 | * @var OrdersTableSearchQuery? |
| 157 | */ |
| 158 | private $search_query = null; |
| 159 | |
| 160 | /** |
| 161 | * Date query parser. |
| 162 | * |
| 163 | * @var WP_Date_Query |
| 164 | */ |
| 165 | private $date_query = null; |
| 166 | |
| 167 | /** |
| 168 | * Instance of the OrdersTableDataStore class. |
| 169 | * |
| 170 | * @var OrdersTableDataStore |
| 171 | */ |
| 172 | private $order_datastore = null; |
| 173 | |
| 174 | /** |
| 175 | * Sets up and runs the query after processing arguments. |
| 176 | * |
| 177 | * @param array $args Array of query vars. |
| 178 | */ |
| 179 | public function __construct( $args = array() ) { |
| 180 | // Note that ideally we would inject this dependency via constructor, but that's not possible since this class needs to be backward compatible with WC_Order_Query class. |
| 181 | $this->order_datastore = wc_get_container()->get( OrdersTableDataStore::class ); |
| 182 | |
| 183 | $this->tables = $this->order_datastore::get_all_table_names_with_id(); |
| 184 | $this->mappings = $this->order_datastore->get_all_order_column_mappings(); |
| 185 | |
| 186 | $this->args = $args; |
| 187 | |
| 188 | // TODO: args to be implemented. |
| 189 | unset( $this->args['customer_note'], $this->args['name'] ); |
| 190 | |
| 191 | $this->build_query(); |
| 192 | $this->run_query(); |
| 193 | } |
| 194 | |
| 195 | /** |
| 196 | * Remaps some legacy and `WP_Query` specific query vars to vars available in the customer order table scheme. |
| 197 | * |
| 198 | * @return void |
| 199 | */ |
| 200 | private function maybe_remap_args(): void { |
| 201 | $mapping = array( |
| 202 | // WP_Query legacy. |
| 203 | 'post_date' => 'date_created', |
| 204 | 'post_date_gmt' => 'date_created_gmt', |
| 205 | 'post_modified' => 'date_updated', |
| 206 | 'post_modified_gmt' => 'date_updated_gmt', |
| 207 | 'post_status' => 'status', |
| 208 | '_date_completed' => 'date_completed', |
| 209 | '_date_paid' => 'date_paid', |
| 210 | 'paged' => 'page', |
| 211 | 'post_parent' => 'parent_order_id', |
| 212 | 'post_parent__in' => 'parent_order_id', |
| 213 | 'post_parent__not_in' => 'parent_exclude', |
| 214 | 'post__not_in' => 'exclude', |
| 215 | 'posts_per_page' => 'limit', |
| 216 | 'p' => 'id', |
| 217 | 'post__in' => 'id', |
| 218 | 'post_type' => 'type', |
| 219 | 'fields' => 'return', |
| 220 | |
| 221 | 'customer_user' => 'customer_id', |
| 222 | 'order_currency' => 'currency', |
| 223 | 'order_version' => 'woocommerce_version', |
| 224 | 'cart_discount' => 'discount_total_amount', |
| 225 | 'cart_discount_tax' => 'discount_tax_amount', |
| 226 | 'order_shipping' => 'shipping_total_amount', |
| 227 | 'order_shipping_tax' => 'shipping_tax_amount', |
| 228 | 'order_tax' => 'tax_amount', |
| 229 | |
| 230 | // Translate from WC_Order_Query to table structure. |
| 231 | 'version' => 'woocommerce_version', |
| 232 | 'date_modified' => 'date_updated', |
| 233 | 'date_modified_gmt' => 'date_updated_gmt', |
| 234 | 'discount_total' => 'discount_total_amount', |
| 235 | 'discount_tax' => 'discount_tax_amount', |
| 236 | 'shipping_total' => 'shipping_total_amount', |
| 237 | 'shipping_tax' => 'shipping_tax_amount', |
| 238 | 'cart_tax' => 'tax_amount', |
| 239 | 'total' => 'total_amount', |
| 240 | 'customer_ip_address' => 'ip_address', |
| 241 | 'customer_user_agent' => 'user_agent', |
| 242 | 'parent' => 'parent_order_id', |
| 243 | ); |
| 244 | |
| 245 | foreach ( $mapping as $query_key => $table_field ) { |
| 246 | if ( isset( $this->args[ $query_key ] ) && '' !== $this->args[ $query_key ] ) { |
| 247 | $this->args[ $table_field ] = $this->args[ $query_key ]; |
| 248 | unset( $this->args[ $query_key ] ); |
| 249 | } |
| 250 | } |
| 251 | |
| 252 | // meta_query. |
| 253 | $this->args['meta_query'] = ( $this->arg_isset( 'meta_query' ) && is_array( $this->args['meta_query'] ) ) ? $this->args['meta_query'] : array(); // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query |
| 254 | |
| 255 | $shortcut_meta_query = array(); |
| 256 | foreach ( array( 'key', 'value', 'compare', 'type', 'compare_key', 'type_key' ) as $key ) { |
| 257 | if ( $this->arg_isset( "meta_{$key}" ) ) { |
| 258 | $shortcut_meta_query[ $key ] = $this->args[ "meta_{$key}" ]; |
| 259 | } |
| 260 | } |
| 261 | |
| 262 | if ( ! empty( $shortcut_meta_query ) ) { |
| 263 | if ( ! empty( $this->args['meta_query'] ) ) { |
| 264 | $this->args['meta_query'] = array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query |
| 265 | 'relation' => 'AND', |
| 266 | $shortcut_meta_query, |
| 267 | $this->args['meta_query'], |
| 268 | ); |
| 269 | } else { |
| 270 | $this->args['meta_query'] = array( $shortcut_meta_query ); // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query |
| 271 | } |
| 272 | } |
| 273 | } |
| 274 | |
| 275 | /** |
| 276 | * Generates a `WP_Date_Query` compatible query from a given date. |
| 277 | * YYYY-MM-DD queries have 'day' precision for backwards compatibility. |
| 278 | * |
| 279 | * @param mixed $date The date. Can be a {@see \WC_DateTime}, a timestamp or a string. |
| 280 | * @param string $timezone The timezone to use for the date. |
| 281 | * @return array An array with keys 'year', 'month', 'day' and possibly 'hour', 'minute' and 'second'. |
| 282 | */ |
| 283 | private function date_to_date_query_arg( $date, $timezone ): array { |
| 284 | $result = array( |
| 285 | 'year' => '', |
| 286 | 'month' => '', |
| 287 | 'day' => '', |
| 288 | ); |
| 289 | $precision = 'second'; |
| 290 | |
| 291 | if ( is_numeric( $date ) ) { |
| 292 | $date = new \WC_DateTime( "@{$date}", new \DateTimeZone( $timezone ) ); |
| 293 | } elseif ( ! is_a( $date, 'WC_DateTime' ) ) { |
| 294 | // YYYY-MM-DD queries have 'day' precision for backwards compat. |
| 295 | $date = wc_string_to_datetime( $date ); |
| 296 | $precision = 'day'; |
| 297 | } |
| 298 | |
| 299 | $result['year'] = $date->date( 'Y' ); |
| 300 | $result['month'] = $date->date( 'm' ); |
| 301 | $result['day'] = $date->date( 'd' ); |
| 302 | |
| 303 | if ( 'second' === $precision ) { |
| 304 | $result['hour'] = $date->date( 'H' ); |
| 305 | $result['minute'] = $date->date( 'i' ); |
| 306 | $result['second'] = $date->date( 's' ); |
| 307 | } |
| 308 | |
| 309 | return $result; |
| 310 | } |
| 311 | |
| 312 | /** |
| 313 | * Processes date-related query args and merges the result into 'date_query'. |
| 314 | * |
| 315 | * @return void |
| 316 | * @throws \Exception When date args are invalid. |
| 317 | */ |
| 318 | private function process_date_args(): void { |
| 319 | if ( $this->arg_isset( 'date_query' ) ) { |
| 320 | // Process already passed date queries args. |
| 321 | $this->args['date_query'] = $this->map_gmt_and_post_keys_to_hpos_keys( $this->args['date_query'] ); |
| 322 | } |
| 323 | |
| 324 | $valid_operators = array( '>', '>=', '=', '<=', '<', '...' ); |
| 325 | $date_queries = array(); |
| 326 | $local_to_gmt_date_keys = array( |
| 327 | 'date_created' => 'date_created_gmt', |
| 328 | 'date_updated' => 'date_updated_gmt', |
| 329 | 'date_paid' => 'date_paid_gmt', |
| 330 | 'date_completed' => 'date_completed_gmt', |
| 331 | ); |
| 332 | |
| 333 | $gmt_date_keys = array_values( $local_to_gmt_date_keys ); |
| 334 | $local_date_keys = array_keys( $local_to_gmt_date_keys ); |
| 335 | |
| 336 | $valid_date_keys = array_merge( $gmt_date_keys, $local_date_keys ); |
| 337 | $date_keys = array_filter( $valid_date_keys, array( $this, 'arg_isset' ) ); |
| 338 | |
| 339 | foreach ( $date_keys as $date_key ) { |
| 340 | $date_value = $this->args[ $date_key ]; |
| 341 | $operator = '='; |
| 342 | $dates = array(); |
| 343 | $timezone = in_array( $date_key, $gmt_date_keys, true ) ? '+0000' : wc_timezone_string(); |
| 344 | |
| 345 | if ( is_string( $date_value ) && preg_match( self::REGEX_SHORTHAND_DATES, $date_value, $matches ) ) { |
| 346 | $operator = in_array( $matches[2], $valid_operators, true ) ? $matches[2] : ''; |
| 347 | |
| 348 | if ( ! empty( $matches[1] ) ) { |
| 349 | $dates[] = $this->date_to_date_query_arg( $matches[1], $timezone ); |
| 350 | } |
| 351 | |
| 352 | $dates[] = $this->date_to_date_query_arg( $matches[3], $timezone ); |
| 353 | } else { |
| 354 | $dates[] = $this->date_to_date_query_arg( $date_value, $timezone ); |
| 355 | } |
| 356 | |
| 357 | if ( empty( $dates ) || ! $operator || ( '...' === $operator && count( $dates ) < 2 ) ) { |
| 358 | throw new \Exception( 'Invalid date_query' ); |
| 359 | } |
| 360 | |
| 361 | $operator_to_keys = array(); |
| 362 | |
| 363 | if ( in_array( $operator, array( '>', '>=', '...' ), true ) ) { |
| 364 | $operator_to_keys[] = 'after'; |
| 365 | } |
| 366 | |
| 367 | if ( in_array( $operator, array( '<', '<=', '...' ), true ) ) { |
| 368 | $operator_to_keys[] = 'before'; |
| 369 | } |
| 370 | |
| 371 | $date_key = in_array( $date_key, $local_date_keys, true ) ? $local_to_gmt_date_keys[ $date_key ] : $date_key; |
| 372 | $date_queries[] = array_merge( |
| 373 | array( |
| 374 | 'column' => $date_key, |
| 375 | 'inclusive' => ! in_array( $operator, array( '<', '>' ), true ), |
| 376 | ), |
| 377 | '=' === $operator |
| 378 | ? end( $dates ) |
| 379 | : array_combine( $operator_to_keys, $dates ) |
| 380 | ); |
| 381 | } |
| 382 | |
| 383 | // Add top-level date parameters to the date_query. |
| 384 | $tl_query = array(); |
| 385 | foreach ( array( 'hour', 'minute', 'second', 'year', 'monthnum', 'week', 'day', 'year' ) as $tl_key ) { |
| 386 | if ( $this->arg_isset( $tl_key ) ) { |
| 387 | $tl_query[ $tl_key ] = $this->args[ $tl_key ]; |
| 388 | unset( $this->args[ $tl_key ] ); |
| 389 | } |
| 390 | } |
| 391 | |
| 392 | if ( $tl_query ) { |
| 393 | $tl_query['column'] = 'date_created_gmt'; |
| 394 | $date_queries[] = $tl_query; |
| 395 | } |
| 396 | |
| 397 | if ( $date_queries ) { |
| 398 | if ( ! $this->arg_isset( 'date_query' ) ) { |
| 399 | $this->args['date_query'] = array(); |
| 400 | } |
| 401 | |
| 402 | $this->args['date_query'] = array_merge( |
| 403 | array( 'relation' => 'AND' ), |
| 404 | $date_queries, |
| 405 | $this->args['date_query'] |
| 406 | ); |
| 407 | } |
| 408 | |
| 409 | $this->process_date_query_columns(); |
| 410 | } |
| 411 | |
| 412 | /** |
| 413 | * Helper function to map posts and gmt based keys to HPOS keys. |
| 414 | * |
| 415 | * @param array $query Date query argument. |
| 416 | * |
| 417 | * @return array|mixed Date query argument with modified keys. |
| 418 | */ |
| 419 | private function map_gmt_and_post_keys_to_hpos_keys( $query ) { |
| 420 | if ( ! is_array( $query ) ) { |
| 421 | return $query; |
| 422 | } |
| 423 | |
| 424 | $post_to_hpos_mappings = array( |
| 425 | 'post_date' => 'date_created', |
| 426 | 'post_date_gmt' => 'date_created_gmt', |
| 427 | 'post_modified' => 'date_updated', |
| 428 | 'post_modified_gmt' => 'date_updated_gmt', |
| 429 | '_date_completed' => 'date_completed', |
| 430 | '_date_paid' => 'date_paid', |
| 431 | 'date_modified' => 'date_updated', |
| 432 | 'date_modified_gmt' => 'date_updated_gmt', |
| 433 | ); |
| 434 | |
| 435 | $local_to_gmt_date_keys = array( |
| 436 | 'date_created' => 'date_created_gmt', |
| 437 | 'date_updated' => 'date_updated_gmt', |
| 438 | 'date_paid' => 'date_paid_gmt', |
| 439 | 'date_completed' => 'date_completed_gmt', |
| 440 | ); |
| 441 | |
| 442 | array_walk( |
| 443 | $query, |
| 444 | function ( &$sub_query ) { |
| 445 | $sub_query = $this->map_gmt_and_post_keys_to_hpos_keys( $sub_query ); |
| 446 | } |
| 447 | ); |
| 448 | |
| 449 | if ( ! isset( $query['column'] ) ) { |
| 450 | return $query; |
| 451 | } |
| 452 | |
| 453 | if ( isset( $post_to_hpos_mappings[ $query['column'] ] ) ) { |
| 454 | $query['column'] = $post_to_hpos_mappings[ $query['column'] ]; |
| 455 | } |
| 456 | |
| 457 | // Convert any local dates to GMT. |
| 458 | if ( isset( $local_to_gmt_date_keys[ $query['column'] ] ) ) { |
| 459 | $query['column'] = $local_to_gmt_date_keys[ $query['column'] ]; |
| 460 | $op = isset( $query['after'] ) ? 'after' : 'before'; |
| 461 | $date_value_local = $query[ $op ]; |
| 462 | $date_value_gmt = wc_string_to_timestamp( get_gmt_from_date( wc_string_to_datetime( $date_value_local ) ) ); |
| 463 | $query[ $op ] = $this->date_to_date_query_arg( $date_value_gmt, 'UTC' ); |
| 464 | } |
| 465 | |
| 466 | return $query; |
| 467 | } |
| 468 | |
| 469 | /** |
| 470 | * Makes sure all 'date_query' columns are correctly prefixed and their respective tables are being JOIN'ed. |
| 471 | * |
| 472 | * @return void |
| 473 | */ |
| 474 | private function process_date_query_columns() { |
| 475 | global $wpdb; |
| 476 | |
| 477 | $legacy_columns = array( |
| 478 | 'post_date' => 'date_created_gmt', |
| 479 | 'post_date_gmt' => 'date_created_gmt', |
| 480 | 'post_modified' => 'date_modified_gmt', |
| 481 | 'post_modified_gmt' => 'date_updated_gmt', |
| 482 | ); |
| 483 | $table_mapping = array( |
| 484 | 'date_created_gmt' => $this->tables['orders'], |
| 485 | 'date_updated_gmt' => $this->tables['orders'], |
| 486 | 'date_paid_gmt' => $this->tables['operational_data'], |
| 487 | 'date_completed_gmt' => $this->tables['operational_data'], |
| 488 | ); |
| 489 | |
| 490 | if ( empty( $this->args['date_query'] ) ) { |
| 491 | return; |
| 492 | } |
| 493 | |
| 494 | array_walk_recursive( |
| 495 | $this->args['date_query'], |
| 496 | function( &$value, $key ) use ( $legacy_columns, $table_mapping, $wpdb ) { |
| 497 | if ( 'column' !== $key ) { |
| 498 | return; |
| 499 | } |
| 500 | |
| 501 | // Translate legacy columns from wp_posts if necessary. |
| 502 | $value = |
| 503 | ( isset( $legacy_columns[ $value ] ) || isset( $legacy_columns[ "{$wpdb->posts}.{$value}" ] ) ) |
| 504 | ? $legacy_columns[ $value ] |
| 505 | : $value; |
| 506 | |
| 507 | $table = $table_mapping[ $value ] ?? null; |
| 508 | |
| 509 | if ( ! $table ) { |
| 510 | return; |
| 511 | } |
| 512 | |
| 513 | $value = "{$table}.{$value}"; |
| 514 | |
| 515 | if ( $table !== $this->tables['orders'] ) { |
| 516 | $this->join( $table, '', '', 'inner', true ); |
| 517 | } |
| 518 | } |
| 519 | ); |
| 520 | } |
| 521 | |
| 522 | /** |
| 523 | * Sanitizes the 'status' query var. |
| 524 | * |
| 525 | * @return void |
| 526 | */ |
| 527 | private function sanitize_status(): void { |
| 528 | // Sanitize status. |
| 529 | $valid_statuses = array_keys( wc_get_order_statuses() ); |
| 530 | |
| 531 | if ( empty( $this->args['status'] ) || 'any' === $this->args['status'] ) { |
| 532 | $this->args['status'] = $valid_statuses; |
| 533 | } elseif ( 'all' === $this->args['status'] ) { |
| 534 | $this->args['status'] = array(); |
| 535 | } else { |
| 536 | $this->args['status'] = is_array( $this->args['status'] ) ? $this->args['status'] : array( $this->args['status'] ); |
| 537 | |
| 538 | foreach ( $this->args['status'] as &$status ) { |
| 539 | $status = in_array( 'wc-' . $status, $valid_statuses, true ) ? 'wc-' . $status : $status; |
| 540 | } |
| 541 | |
| 542 | $this->args['status'] = array_unique( array_filter( $this->args['status'] ) ); |
| 543 | } |
| 544 | } |
| 545 | |
| 546 | /** |
| 547 | * Parses and sanitizes the 'orderby' query var. |
| 548 | * |
| 549 | * @return void |
| 550 | */ |
| 551 | private function sanitize_order_orderby(): void { |
| 552 | // Allowed keys. |
| 553 | // TODO: rand, meta keys, etc. |
| 554 | $allowed_keys = array( 'ID', 'id', 'type', 'date', 'modified', 'parent' ); |
| 555 | |
| 556 | // Translate $orderby to a valid field. |
| 557 | $mapping = array( |
| 558 | 'ID' => "{$this->tables['orders']}.id", |
| 559 | 'id' => "{$this->tables['orders']}.id", |
| 560 | 'type' => "{$this->tables['orders']}.type", |
| 561 | 'date' => "{$this->tables['orders']}.date_created_gmt", |
| 562 | 'date_created' => "{$this->tables['orders']}.date_created_gmt", |
| 563 | 'modified' => "{$this->tables['orders']}.date_updated_gmt", |
| 564 | 'date_modified' => "{$this->tables['orders']}.date_updated_gmt", |
| 565 | 'parent' => "{$this->tables['orders']}.parent_order_id", |
| 566 | 'total' => "{$this->tables['orders']}.total_amount", |
| 567 | 'order_total' => "{$this->tables['orders']}.total_amount", |
| 568 | ); |
| 569 | |
| 570 | $order = $this->args['order'] ?? ''; |
| 571 | $orderby = $this->args['orderby'] ?? ''; |
| 572 | |
| 573 | if ( 'none' === $orderby ) { |
| 574 | return; |
| 575 | } |
| 576 | |
| 577 | // No need to sanitize, will be processed in calling function. |
| 578 | if ( 'include' === $orderby || 'post__in' === $orderby ) { |
| 579 | return; |
| 580 | } |
| 581 | |
| 582 | if ( is_string( $orderby ) ) { |
| 583 | $orderby_fields = array_map( 'trim', explode( ' ', $orderby ) ); |
| 584 | $orderby = array(); |
| 585 | foreach ( $orderby_fields as $field ) { |
| 586 | $orderby[ $field ] = $order; |
| 587 | } |
| 588 | } |
| 589 | |
| 590 | $allowed_orderby = array_merge( |
| 591 | array_keys( $mapping ), |
| 592 | array_values( $mapping ), |
| 593 | $this->meta_query ? $this->meta_query->get_orderby_keys() : array() |
| 594 | ); |
| 595 | |
| 596 | $this->args['orderby'] = array(); |
| 597 | foreach ( $orderby as $order_key => $order ) { |
| 598 | if ( ! in_array( $order_key, $allowed_orderby, true ) ) { |
| 599 | continue; |
| 600 | } |
| 601 | |
| 602 | if ( isset( $mapping[ $order_key ] ) ) { |
| 603 | $order_key = $mapping[ $order_key ]; |
| 604 | } |
| 605 | |
| 606 | $this->args['orderby'][ $order_key ] = $this->sanitize_order( $order ); |
| 607 | } |
| 608 | } |
| 609 | |
| 610 | /** |
| 611 | * Makes sure the order in an ORDER BY statement is either 'ASC' o 'DESC'. |
| 612 | * |
| 613 | * @param string $order The unsanitized order. |
| 614 | * @return string The sanitized order. |
| 615 | */ |
| 616 | private function sanitize_order( string $order ): string { |
| 617 | $order = strtoupper( $order ); |
| 618 | |
| 619 | return in_array( $order, array( 'ASC', 'DESC' ), true ) ? $order : 'DESC'; |
| 620 | } |
| 621 | |
| 622 | /** |
| 623 | * Builds the final SQL query to be run. |
| 624 | * |
| 625 | * @return void |
| 626 | */ |
| 627 | private function build_query(): void { |
| 628 | $this->maybe_remap_args(); |
| 629 | |
| 630 | // Field queries. |
| 631 | if ( ! empty( $this->args['field_query'] ) ) { |
| 632 | $this->field_query = new OrdersTableFieldQuery( $this ); |
| 633 | $sql = $this->field_query->get_sql_clauses(); |
| 634 | $this->join = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join; |
| 635 | $this->where = $sql['where'] ? array_merge( $this->where, $sql['where'] ) : $this->where; |
| 636 | } |
| 637 | |
| 638 | // Build query. |
| 639 | $this->process_date_args(); |
| 640 | $this->process_orders_table_query_args(); |
| 641 | $this->process_operational_data_table_query_args(); |
| 642 | $this->process_addresses_table_query_args(); |
| 643 | |
| 644 | // Search queries. |
| 645 | if ( ! empty( $this->args['s'] ) ) { |
| 646 | $this->search_query = new OrdersTableSearchQuery( $this ); |
| 647 | $sql = $this->search_query->get_sql_clauses(); |
| 648 | $this->join = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join; |
| 649 | $this->where = $sql['where'] ? array_merge( $this->where, $sql['where'] ) : $this->where; |
| 650 | } |
| 651 | |
| 652 | // Meta queries. |
| 653 | if ( ! empty( $this->args['meta_query'] ) ) { |
| 654 | $this->meta_query = new OrdersTableMetaQuery( $this ); |
| 655 | |
| 656 | $sql = $this->meta_query->get_sql_clauses(); |
| 657 | |
| 658 | $this->join = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join; |
| 659 | $this->where = $sql['where'] ? array_merge( $this->where, array( $sql['where'] ) ) : $this->where; |
| 660 | |
| 661 | } |
| 662 | |
| 663 | // Date queries. |
| 664 | if ( ! empty( $this->args['date_query'] ) ) { |
| 665 | $this->date_query = new \WP_Date_Query( $this->args['date_query'], "{$this->tables['orders']}.date_created_gmt" ); |
| 666 | $this->where[] = substr( trim( $this->date_query->get_sql() ), 3 ); // WP_Date_Query includes "AND". |
| 667 | } |
| 668 | |
| 669 | $this->process_orderby(); |
| 670 | $this->process_limit(); |
| 671 | |
| 672 | $orders_table = $this->tables['orders']; |
| 673 | |
| 674 | // Group by is a faster substitute for DISTINCT, as long as we are only selecting IDs. MySQL don't like it when we join tables and use DISTINCT. |
| 675 | $this->groupby[] = "{$this->tables['orders']}.id"; |
| 676 | $this->fields = "{$orders_table}.id"; |
| 677 | $fields = $this->fields; |
| 678 | |
| 679 | // JOIN. |
| 680 | $join = implode( ' ', array_unique( array_filter( array_map( 'trim', $this->join ) ) ) ); |
| 681 | |
| 682 | // WHERE. |
| 683 | $where = '1=1'; |
| 684 | foreach ( $this->where as $_where ) { |
| 685 | $where .= " AND ({$_where})"; |
| 686 | } |
| 687 | |
| 688 | // ORDER BY. |
| 689 | $orderby = $this->orderby ? ( 'ORDER BY ' . implode( ', ', $this->orderby ) ) : ''; |
| 690 | |
| 691 | // LIMITS. |
| 692 | $limits = ''; |
| 693 | |
| 694 | if ( ! empty( $this->limits ) && count( $this->limits ) === 2 ) { |
| 695 | list( $offset, $row_count ) = $this->limits; |
| 696 | $row_count = -1 === $row_count ? self::MYSQL_MAX_UNSIGNED_BIGINT : (int) $row_count; |
| 697 | $limits = 'LIMIT ' . (int) $offset . ', ' . $row_count; |
| 698 | } |
| 699 | |
| 700 | // GROUP BY. |
| 701 | $groupby = $this->groupby ? 'GROUP BY ' . implode( ', ', (array) $this->groupby ) : ''; |
| 702 | |
| 703 | $this->sql = "SELECT $fields FROM $orders_table $join WHERE $where $groupby $orderby $limits"; |
| 704 | $this->build_count_query( $fields, $join, $where, $groupby ); |
| 705 | } |
| 706 | |
| 707 | /** |
| 708 | * Build SQL query for counting total number of results. |
| 709 | * |
| 710 | * @param string $fields Prepared fields for SELECT clause. |
| 711 | * @param string $join Prepared JOIN clause. |
| 712 | * @param string $where Prepared WHERE clause. |
| 713 | * @param string $groupby Prepared GROUP BY clause. |
| 714 | */ |
| 715 | private function build_count_query( $fields, $join, $where, $groupby ) { |
| 716 | if ( ! isset( $this->sql ) || '' === $this->sql ) { |
| 717 | wc_doing_it_wrong( __FUNCTION__, 'Count query can only be build after main query is built.', '7.3.0' ); |
| 718 | } |
| 719 | $orders_table = $this->tables['orders']; |
| 720 | $this->count_sql = "SELECT COUNT(DISTINCT $fields) FROM $orders_table $join WHERE $where"; |
| 721 | } |
| 722 | |
| 723 | /** |
| 724 | * Returns the table alias for a given table mapping. |
| 725 | * |
| 726 | * @param string $mapping_id The mapping name (e.g. 'orders' or 'operational_data'). |
| 727 | * @return string Table alias. |
| 728 | * |
| 729 | * @since 7.0.0 |
| 730 | */ |
| 731 | public function get_core_mapping_alias( string $mapping_id ): string { |
| 732 | return in_array( $mapping_id, array( 'billing_address', 'shipping_address' ), true ) |
| 733 | ? $mapping_id |
| 734 | : $this->tables[ $mapping_id ]; |
| 735 | } |
| 736 | |
| 737 | /** |
| 738 | * Returns an SQL JOIN clause that can be used to join the main orders table with another order table. |
| 739 | * |
| 740 | * @param string $mapping_id The mapping name (e.g. 'orders' or 'operational_data'). |
| 741 | * @return string The JOIN clause. |
| 742 | * |
| 743 | * @since 7.0.0 |
| 744 | */ |
| 745 | public function get_core_mapping_join( string $mapping_id ): string { |
| 746 | global $wpdb; |
| 747 | |
| 748 | if ( 'orders' === $mapping_id ) { |
| 749 | return ''; |
| 750 | } |
| 751 | |
| 752 | $is_address_mapping = in_array( $mapping_id, array( 'billing_address', 'shipping_address' ), true ); |
| 753 | |
| 754 | $alias = $this->get_core_mapping_alias( $mapping_id ); |
| 755 | $table = $is_address_mapping ? $this->tables['addresses'] : $this->tables[ $mapping_id ]; |
| 756 | $join = ''; |
| 757 | $join_on = ''; |
| 758 | |
| 759 | $join .= "INNER JOIN `{$table}`" . ( $alias !== $table ? " AS `{$alias}`" : '' ); |
| 760 | |
| 761 | if ( isset( $this->mappings[ $mapping_id ]['order_id'] ) ) { |
| 762 | $join_on .= "`{$this->tables['orders']}`.id = `{$alias}`.order_id"; |
| 763 | } |
| 764 | |
| 765 | if ( $is_address_mapping ) { |
| 766 | $join_on .= $wpdb->prepare( " AND `{$alias}`.address_type = %s", substr( $mapping_id, 0, -8 ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 767 | } |
| 768 | |
| 769 | return $join . ( $join_on ? " ON ( {$join_on} )" : '' ); |
| 770 | } |
| 771 | |
| 772 | /** |
| 773 | * JOINs the main orders table with another table. |
| 774 | * |
| 775 | * @param string $table Table name (including prefix). |
| 776 | * @param string $alias Table alias to use. Defaults to $table. |
| 777 | * @param string $on ON clause. Defaults to "wc_orders.id = {$alias}.order_id". |
| 778 | * @param string $join_type JOIN type: LEFT, RIGHT or INNER. |
| 779 | * @param boolean $alias_once If TRUE, table won't be JOIN'ed again if already JOIN'ed. |
| 780 | * @return void |
| 781 | * @throws \Exception When an error occurs, such as trying to re-use an alias with $alias_once = FALSE. |
| 782 | */ |
| 783 | private function join( string $table, string $alias = '', string $on = '', string $join_type = 'inner', bool $alias_once = false ) { |
| 784 | $alias = empty( $alias ) ? $table : $alias; |
| 785 | $join_type = strtoupper( trim( $join_type ) ); |
| 786 | |
| 787 | if ( $this->tables['orders'] === $alias ) { |
| 788 | // translators: %s is a table name. |
| 789 | throw new \Exception( sprintf( __( '%s can not be used as a table alias in OrdersTableQuery', 'woocommerce' ), $alias ) ); |
| 790 | } |
| 791 | |
| 792 | if ( empty( $on ) ) { |
| 793 | if ( $this->tables['orders'] === $table ) { |
| 794 | $on = "`{$this->tables['orders']}`.id = `{$alias}`.id"; |
| 795 | } else { |
| 796 | $on = "`{$this->tables['orders']}`.id = `{$alias}`.order_id"; |
| 797 | } |
| 798 | } |
| 799 | |
| 800 | if ( isset( $this->join[ $alias ] ) ) { |
| 801 | if ( ! $alias_once ) { |
| 802 | // translators: %s is a table name. |
| 803 | throw new \Exception( sprintf( __( 'Can not re-use table alias "%s" in OrdersTableQuery.', 'woocommerce' ), $alias ) ); |
| 804 | } |
| 805 | |
| 806 | return; |
| 807 | } |
| 808 | |
| 809 | if ( '' === $join_type || ! in_array( $join_type, array( 'LEFT', 'RIGHT', 'INNER' ), true ) ) { |
| 810 | $join_type = 'INNER'; |
| 811 | } |
| 812 | |
| 813 | $sql_join = ''; |
| 814 | $sql_join .= "{$join_type} JOIN `{$table}` "; |
| 815 | $sql_join .= ( $alias !== $table ) ? "AS `{$alias}` " : ''; |
| 816 | $sql_join .= "ON ( {$on} )"; |
| 817 | |
| 818 | $this->join[ $alias ] = $sql_join; |
| 819 | } |
| 820 | |
| 821 | /** |
| 822 | * Generates a properly escaped and sanitized WHERE condition for a given field. |
| 823 | * |
| 824 | * @param string $table The table the field belongs to. |
| 825 | * @param string $field The field or column name. |
| 826 | * @param string $operator The operator to use in the condition. Defaults to '=' or 'IN' depending on $value. |
| 827 | * @param mixed $value The value. |
| 828 | * @param string $type The column type as specified in {@see OrdersTableDataStore} column mappings. |
| 829 | * @return string The resulting WHERE condition. |
| 830 | */ |
| 831 | public function where( string $table, string $field, string $operator, $value, string $type ): string { |
| 832 | global $wpdb; |
| 833 | |
| 834 | $db_util = wc_get_container()->get( DatabaseUtil::class ); |
| 835 | $operator = strtoupper( '' !== $operator ? $operator : '=' ); |
| 836 | |
| 837 | try { |
| 838 | $format = $db_util->get_wpdb_format_for_type( $type ); |
| 839 | } catch ( \Exception $e ) { |
| 840 | $format = '%s'; |
| 841 | } |
| 842 | |
| 843 | // = and != can be shorthands for IN and NOT in for array values. |
| 844 | if ( is_array( $value ) && '=' === $operator ) { |
| 845 | $operator = 'IN'; |
| 846 | } elseif ( is_array( $value ) && '!=' === $operator ) { |
| 847 | $operator = 'NOT IN'; |
| 848 | } |
| 849 | |
| 850 | if ( ! in_array( $operator, array( '=', '!=', 'IN', 'NOT IN' ), true ) ) { |
| 851 | return false; |
| 852 | } |
| 853 | |
| 854 | if ( is_array( $value ) ) { |
| 855 | $value = array_map( array( $db_util, 'format_object_value_for_db' ), $value, array_fill( 0, count( $value ), $type ) ); |
| 856 | } else { |
| 857 | $value = $db_util->format_object_value_for_db( $value, $type ); |
| 858 | } |
| 859 | |
| 860 | if ( is_array( $value ) ) { |
| 861 | $placeholder = array_fill( 0, count( $value ), $format ); |
| 862 | $placeholder = '(' . implode( ',', $placeholder ) . ')'; |
| 863 | } else { |
| 864 | $placeholder = $format; |
| 865 | } |
| 866 | |
| 867 | $sql = $wpdb->prepare( "{$table}.{$field} {$operator} {$placeholder}", $value ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare |
| 868 | |
| 869 | return $sql; |
| 870 | } |
| 871 | |
| 872 | /** |
| 873 | * Processes fields related to the orders table. |
| 874 | * |
| 875 | * @return void |
| 876 | */ |
| 877 | private function process_orders_table_query_args(): void { |
| 878 | $this->sanitize_status(); |
| 879 | |
| 880 | $fields = array_filter( |
| 881 | array( |
| 882 | 'id', |
| 883 | 'status', |
| 884 | 'type', |
| 885 | 'currency', |
| 886 | 'tax_amount', |
| 887 | 'customer_id', |
| 888 | 'billing_email', |
| 889 | 'total_amount', |
| 890 | 'parent_order_id', |
| 891 | 'payment_method', |
| 892 | 'payment_method_title', |
| 893 | 'transaction_id', |
| 894 | 'ip_address', |
| 895 | 'user_agent', |
| 896 | ), |
| 897 | array( $this, 'arg_isset' ) |
| 898 | ); |
| 899 | |
| 900 | foreach ( $fields as $arg_key ) { |
| 901 | $this->where[] = $this->where( $this->tables['orders'], $arg_key, '=', $this->args[ $arg_key ], $this->mappings['orders'][ $arg_key ]['type'] ); |
| 902 | } |
| 903 | |
| 904 | if ( $this->arg_isset( 'parent_exclude' ) ) { |
| 905 | $this->where[] = $this->where( $this->tables['orders'], 'parent_order_id', '!=', $this->args['parent_exclude'], 'int' ); |
| 906 | } |
| 907 | |
| 908 | if ( $this->arg_isset( 'exclude' ) ) { |
| 909 | $this->where[] = $this->where( $this->tables['orders'], 'id', '!=', $this->args['exclude'], 'int' ); |
| 910 | } |
| 911 | |
| 912 | // 'customer' is a very special field. |
| 913 | if ( $this->arg_isset( 'customer' ) ) { |
| 914 | $customer_query = $this->generate_customer_query( $this->args['customer'] ); |
| 915 | |
| 916 | if ( $customer_query ) { |
| 917 | $this->where[] = $customer_query; |
| 918 | } |
| 919 | } |
| 920 | } |
| 921 | |
| 922 | /** |
| 923 | * Generate SQL conditions for the 'customer' query. |
| 924 | * |
| 925 | * @param array $values List of customer ids or emails. |
| 926 | * @param string $relation 'OR' or 'AND' relation used to build the customer query. |
| 927 | * @return string SQL to be used in a WHERE clause. |
| 928 | */ |
| 929 | private function generate_customer_query( $values, string $relation = 'OR' ): string { |
| 930 | $values = is_array( $values ) ? $values : array( $values ); |
| 931 | $ids = array(); |
| 932 | $emails = array(); |
| 933 | |
| 934 | foreach ( $values as $value ) { |
| 935 | if ( is_array( $value ) ) { |
| 936 | $sql = $this->generate_customer_query( $value, 'AND' ); |
| 937 | $pieces[] = $sql ? '(' . $sql . ')' : ''; |
| 938 | } elseif ( is_numeric( $value ) ) { |
| 939 | $ids[] = absint( $value ); |
| 940 | } elseif ( is_string( $value ) && is_email( $value ) ) { |
| 941 | $emails[] = sanitize_email( $value ); |
| 942 | } else { |
| 943 | // Invalid query. |
| 944 | $pieces[] = '1=0'; |
| 945 | } |
| 946 | } |
| 947 | |
| 948 | if ( $ids ) { |
| 949 | $pieces[] = $this->where( $this->tables['orders'], 'customer_id', '=', $ids, 'int' ); |
| 950 | } |
| 951 | |
| 952 | if ( $emails ) { |
| 953 | $pieces[] = $this->where( $this->tables['orders'], 'billing_email', '=', $emails, 'string' ); |
| 954 | } |
| 955 | |
| 956 | return $pieces ? implode( " $relation ", $pieces ) : ''; |
| 957 | } |
| 958 | |
| 959 | /** |
| 960 | * Processes fields related to the operational data table. |
| 961 | * |
| 962 | * @return void |
| 963 | */ |
| 964 | private function process_operational_data_table_query_args(): void { |
| 965 | $fields = array_filter( |
| 966 | array( |
| 967 | 'created_via', |
| 968 | 'woocommerce_version', |
| 969 | 'prices_include_tax', |
| 970 | 'order_key', |
| 971 | 'discount_total_amount', |
| 972 | 'discount_tax_amount', |
| 973 | 'shipping_total_amount', |
| 974 | 'shipping_tax_amount', |
| 975 | ), |
| 976 | array( $this, 'arg_isset' ) |
| 977 | ); |
| 978 | |
| 979 | if ( ! $fields ) { |
| 980 | return; |
| 981 | } |
| 982 | |
| 983 | $this->join( |
| 984 | $this->tables['operational_data'], |
| 985 | '', |
| 986 | '', |
| 987 | 'inner', |
| 988 | true |
| 989 | ); |
| 990 | |
| 991 | foreach ( $fields as $arg_key ) { |
| 992 | $this->where[] = $this->where( $this->tables['operational_data'], $arg_key, '=', $this->args[ $arg_key ], $this->mappings['operational_data'][ $arg_key ]['type'] ); |
| 993 | } |
| 994 | } |
| 995 | |
| 996 | /** |
| 997 | * Processes fields related to the addresses table. |
| 998 | * |
| 999 | * @return void |
| 1000 | */ |
| 1001 | private function process_addresses_table_query_args(): void { |
| 1002 | global $wpdb; |
| 1003 | |
| 1004 | foreach ( array( 'billing', 'shipping' ) as $address_type ) { |
| 1005 | $fields = array_filter( |
| 1006 | array( |
| 1007 | $address_type . '_first_name', |
| 1008 | $address_type . '_last_name', |
| 1009 | $address_type . '_company', |
| 1010 | $address_type . '_address_1', |
| 1011 | $address_type . '_address_2', |
| 1012 | $address_type . '_city', |
| 1013 | $address_type . '_state', |
| 1014 | $address_type . '_postcode', |
| 1015 | $address_type . '_country', |
| 1016 | $address_type . '_phone', |
| 1017 | ), |
| 1018 | array( $this, 'arg_isset' ) |
| 1019 | ); |
| 1020 | |
| 1021 | if ( ! $fields ) { |
| 1022 | continue; |
| 1023 | } |
| 1024 | |
| 1025 | $this->join( |
| 1026 | $this->tables['addresses'], |
| 1027 | $address_type, |
| 1028 | $wpdb->prepare( "{$this->tables['orders']}.id = {$address_type}.order_id AND {$address_type}.address_type = %s", $address_type ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 1029 | 'inner', |
| 1030 | false |
| 1031 | ); |
| 1032 | |
| 1033 | foreach ( $fields as $arg_key ) { |
| 1034 | $column_name = str_replace( "{$address_type}_", '', $arg_key ); |
| 1035 | |
| 1036 | $this->where[] = $this->where( |
| 1037 | $address_type, |
| 1038 | $column_name, |
| 1039 | '=', |
| 1040 | $this->args[ $arg_key ], |
| 1041 | $this->mappings[ "{$address_type}_address" ][ $column_name ]['type'] |
| 1042 | ); |
| 1043 | } |
| 1044 | } |
| 1045 | } |
| 1046 | |
| 1047 | /** |
| 1048 | * Generates the ORDER BY clause. |
| 1049 | * |
| 1050 | * @return void |
| 1051 | */ |
| 1052 | private function process_orderby(): void { |
| 1053 | // 'order' and 'orderby' vars. |
| 1054 | $this->args['order'] = $this->sanitize_order( $this->args['order'] ?? '' ); |
| 1055 | $this->sanitize_order_orderby(); |
| 1056 | |
| 1057 | $orderby = $this->args['orderby']; |
| 1058 | |
| 1059 | if ( 'none' === $orderby ) { |
| 1060 | $this->orderby = ''; |
| 1061 | return; |
| 1062 | } |
| 1063 | |
| 1064 | if ( 'include' === $orderby || 'post__in' === $orderby ) { |
| 1065 | $ids = $this->args['id'] ?? $this->args['includes']; |
| 1066 | if ( empty( $ids ) ) { |
| 1067 | return; |
| 1068 | } |
| 1069 | $ids = array_map( 'absint', $ids ); |
| 1070 | $this->orderby = array( "FIELD( {$this->tables['orders']}.id, " . implode( ',', $ids ) . ' )' ); |
| 1071 | return; |
| 1072 | } |
| 1073 | |
| 1074 | $meta_orderby_keys = $this->meta_query ? $this->meta_query->get_orderby_keys() : array(); |
| 1075 | |
| 1076 | $orderby_array = array(); |
| 1077 | foreach ( $this->args['orderby'] as $_orderby => $order ) { |
| 1078 | if ( in_array( $_orderby, $meta_orderby_keys, true ) ) { |
| 1079 | $_orderby = $this->meta_query->get_orderby_clause_for_key( $_orderby ); |
| 1080 | } |
| 1081 | |
| 1082 | $orderby_array[] = "{$_orderby} {$order}"; |
| 1083 | } |
| 1084 | |
| 1085 | $this->orderby = $orderby_array; |
| 1086 | } |
| 1087 | |
| 1088 | /** |
| 1089 | * Generates the limits to be used in the LIMIT clause. |
| 1090 | * |
| 1091 | * @return void |
| 1092 | */ |
| 1093 | private function process_limit(): void { |
| 1094 | $row_count = ( $this->arg_isset( 'limit' ) ? (int) $this->args['limit'] : false ); |
| 1095 | $page = ( $this->arg_isset( 'page' ) ? absint( $this->args['page'] ) : 1 ); |
| 1096 | $offset = ( $this->arg_isset( 'offset' ) ? absint( $this->args['offset'] ) : false ); |
| 1097 | |
| 1098 | // Bool false indicates no limit was specified; less than -1 means an invalid value was passed (such as -3). |
| 1099 | if ( false === $row_count || $row_count < -1 ) { |
| 1100 | return; |
| 1101 | } |
| 1102 | |
| 1103 | if ( false === $offset && $row_count > -1 ) { |
| 1104 | $offset = (int) ( ( $page - 1 ) * $row_count ); |
| 1105 | } |
| 1106 | |
| 1107 | $this->limits = array( $offset, $row_count ); |
| 1108 | } |
| 1109 | |
| 1110 | /** |
| 1111 | * Checks if a query var is set (i.e. not one of the "skipped values"). |
| 1112 | * |
| 1113 | * @param string $arg_key Query var. |
| 1114 | * @return bool TRUE if query var is set. |
| 1115 | */ |
| 1116 | public function arg_isset( string $arg_key ): bool { |
| 1117 | return ( isset( $this->args[ $arg_key ] ) && ! in_array( $this->args[ $arg_key ], self::SKIPPED_VALUES, true ) ); |
| 1118 | } |
| 1119 | |
| 1120 | /** |
| 1121 | * Runs the SQL query. |
| 1122 | * |
| 1123 | * @return void |
| 1124 | */ |
| 1125 | private function run_query(): void { |
| 1126 | global $wpdb; |
| 1127 | |
| 1128 | // Run query. |
| 1129 | $this->orders = array_map( 'absint', $wpdb->get_col( $this->sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 1130 | |
| 1131 | // Set max_num_pages and found_orders if necessary. |
| 1132 | if ( ( $this->arg_isset( 'no_found_rows' ) && ! $this->args['no_found_rows'] ) || empty( $this->orders ) ) { |
| 1133 | return; |
| 1134 | } |
| 1135 | |
| 1136 | if ( $this->limits ) { |
| 1137 | $this->found_orders = absint( $wpdb->get_var( $this->count_sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 1138 | $this->max_num_pages = (int) ceil( $this->found_orders / $this->args['limit'] ); |
| 1139 | } else { |
| 1140 | $this->found_orders = count( $this->orders ); |
| 1141 | } |
| 1142 | } |
| 1143 | |
| 1144 | /** |
| 1145 | * Make some private available for backwards compatibility. |
| 1146 | * |
| 1147 | * @param string $name Property to get. |
| 1148 | * @return mixed |
| 1149 | */ |
| 1150 | public function __get( string $name ) { |
| 1151 | switch ( $name ) { |
| 1152 | case 'found_orders': |
| 1153 | case 'found_posts': |
| 1154 | return $this->found_orders; |
| 1155 | case 'max_num_pages': |
| 1156 | return $this->max_num_pages; |
| 1157 | case 'posts': |
| 1158 | case 'orders': |
| 1159 | return $this->results; |
| 1160 | case 'request': |
| 1161 | return $this->sql; |
| 1162 | default: |
| 1163 | break; |
| 1164 | } |
| 1165 | } |
| 1166 | |
| 1167 | /** |
| 1168 | * Returns the value of one of the query arguments. |
| 1169 | * |
| 1170 | * @param string $arg_name Query var. |
| 1171 | * @return mixed |
| 1172 | */ |
| 1173 | public function get( string $arg_name ) { |
| 1174 | return $this->args[ $arg_name ] ?? null; |
| 1175 | } |
| 1176 | |
| 1177 | /** |
| 1178 | * Returns the name of one of the OrdersTableDatastore tables. |
| 1179 | * |
| 1180 | * @param string $table_id Table identifier. One of 'orders', 'operational_data', 'addresses', 'meta'. |
| 1181 | * @return string The prefixed table name. |
| 1182 | * @throws \Exception When table ID is not found. |
| 1183 | */ |
| 1184 | public function get_table_name( string $table_id = '' ): string { |
| 1185 | if ( ! isset( $this->tables[ $table_id ] ) ) { |
| 1186 | // Translators: %s is a table identifier. |
| 1187 | throw new \Exception( sprintf( __( 'Invalid table id: %s.', 'woocommerce' ), $table_id ) ); |
| 1188 | } |
| 1189 | |
| 1190 | return $this->tables[ $table_id ]; |
| 1191 | } |
| 1192 | |
| 1193 | /** |
| 1194 | * Finds table and mapping information about a field or column. |
| 1195 | * |
| 1196 | * @param string $field Field to look for in `<mapping|field_name>.<column|field_name>` format or just `<field_name>`. |
| 1197 | * @return false|array { |
| 1198 | * @type string $table Full table name where the field is located. |
| 1199 | * @type string $mapping_id Unprefixed table or mapping name. |
| 1200 | * @type string $field_name Name of the corresponding order field. |
| 1201 | * @type string $column Column in $table that corresponds to the field. |
| 1202 | * @type string $type Field type. |
| 1203 | * } |
| 1204 | */ |
| 1205 | public function get_field_mapping_info( $field ) { |
| 1206 | global $wpdb; |
| 1207 | |
| 1208 | $result = array( |
| 1209 | 'table' => '', |
| 1210 | 'mapping_id' => '', |
| 1211 | 'field_name' => '', |
| 1212 | 'column' => '', |
| 1213 | 'column_type' => '', |
| 1214 | ); |
| 1215 | |
| 1216 | $mappings_to_search = array(); |
| 1217 | |
| 1218 | if ( false !== strstr( $field, '.' ) ) { |
| 1219 | list( $mapping_or_table, $field_name_or_col ) = explode( '.', $field ); |
| 1220 | |
| 1221 | $mapping_or_table = substr( $mapping_or_table, 0, strlen( $wpdb->prefix ) ) === $wpdb->prefix ? substr( $mapping_or_table, strlen( $wpdb->prefix ) ) : $mapping_or_table; |
| 1222 | $mapping_or_table = 'wc_' === substr( $mapping_or_table, 0, 3 ) ? substr( $mapping_or_table, 3 ) : $mapping_or_table; |
| 1223 | |
| 1224 | if ( isset( $this->mappings[ $mapping_or_table ] ) ) { |
| 1225 | if ( isset( $this->mappings[ $mapping_or_table ][ $field_name_or_col ] ) ) { |
| 1226 | $result['mapping_id'] = $mapping_or_table; |
| 1227 | $result['column'] = $field_name_or_col; |
| 1228 | } else { |
| 1229 | $mappings_to_search = array( $mapping_or_table ); |
| 1230 | } |
| 1231 | } |
| 1232 | } else { |
| 1233 | $field_name_or_col = $field; |
| 1234 | $mappings_to_search = array_keys( $this->mappings ); |
| 1235 | } |
| 1236 | |
| 1237 | foreach ( $mappings_to_search as $mapping_id ) { |
| 1238 | foreach ( $this->mappings[ $mapping_id ] as $column_name => $column_data ) { |
| 1239 | if ( isset( $column_data['name'] ) && $column_data['name'] === $field_name_or_col ) { |
| 1240 | $result['mapping_id'] = $mapping_id; |
| 1241 | $result['column'] = $column_name; |
| 1242 | break 2; |
| 1243 | } |
| 1244 | } |
| 1245 | } |
| 1246 | |
| 1247 | if ( ! $result['mapping_id'] || ! $result['column'] ) { |
| 1248 | return false; |
| 1249 | } |
| 1250 | |
| 1251 | $field_info = $this->mappings[ $result['mapping_id'] ][ $result['column'] ]; |
| 1252 | |
| 1253 | $result['field_name'] = $field_info['name']; |
| 1254 | $result['column_type'] = $field_info['type']; |
| 1255 | $result['table'] = ( in_array( $result['mapping_id'], array( 'billing_address', 'shipping_address' ), true ) ) |
| 1256 | ? $this->tables['addresses'] |
| 1257 | : $this->tables[ $result['mapping_id'] ]; |
| 1258 | |
| 1259 | return $result; |
| 1260 | } |
| 1261 | |
| 1262 | } |
| 1263 |