Na}{

File Content: `OrdersTableQuery.php`

<?php
// phpcs:disable Generic.Commenting.Todo.TaskFound
/**
 * OrdersTableQuery class file.
 */

namespace Automattic\WooCommerce\Internal\DataStores\Orders;

use Automattic\WooCommerce\Internal\Utilities\DatabaseUtil;

defined( 'ABSPATH' ) || exit;

/**
 * This class provides a `WP_Query`-like interface to custom order tables.
 *
 * @property-read int   $found_orders  Number of found orders.
 * @property-read int   $found_posts   Alias of the `$found_orders` property.
 * @property-read int   $max_num_pages Max number of pages matching the current query.
 * @property-read array $orders        Order objects, or order IDs.
 * @property-read array $posts         Alias of the $orders property.
 */
class OrdersTableQuery {

	/**
	 * Values to ignore when parsing query arguments.
	 */
	public const SKIPPED_VALUES = array( '', array(), null );

	/**
	 * Regex used to catch "shorthand" comparisons in date-related query args.
	 */
	public const REGEX_SHORTHAND_DATES = '/([^.<>]*)(>=|<=|>|<|\.\.\.)([^.<>]+)/';

	/**
	 * Highest possible unsigned bigint value (unsigned bigints being the type of the `id` column).
	 *
	 * This is deliberately held as a string, rather than a numeric type, for inclusion within queries.
	 */
	private const MYSQL_MAX_UNSIGNED_BIGINT = '18446744073709551615';

	/**
	 * Names of all COT tables (orders, addresses, operational_data, meta) in the form 'table_id' => 'table name'.
	 *
	 * @var array
	 */
	private $tables = array();

	/**
	 * Column mappings for all COT tables.
	 *
	 * @var array
	 */
	private $mappings = array();

	/**
	 * Query vars after processing and sanitization.
	 *
	 * @var array
	 */
	private $args = array();

	/**
	 * Columns to be selected in the SELECT clause.
	 *
	 * @var array
	 */
	private $fields = array();

	/**
	 * Array of table aliases and conditions used to compute the JOIN clause of the query.
	 *
	 * @var array
	 */
	private $join = array();

	/**
	 * Array of fields and conditions used to compute the WHERE clause of the query.
	 *
	 * @var array
	 */
	private $where = array();

	/**
	 * Field to be used in the GROUP BY clause of the query.
	 *
	 * @var array
	 */
	private $groupby = array();

	/**
	 * Array of fields used to compute the ORDER BY clause of the query.
	 *
	 * @var array
	 */
	private $orderby = array();

	/**
	 * Limits used to compute the LIMIT clause of the query.
	 *
	 * @var array
	 */
	private $limits = array();

	/**
	 * Results (order IDs) for the current query.
	 *
	 * @var array
	 */
	private $orders = array();

	/**
	 * Final SQL query to run after processing of args.
	 *
	 * @var string
	 */
	private $sql = '';

	/**
	 * Final SQL query to count results after processing of args.
	 *
	 * @var string
	 */
	private $count_sql = '';

	/**
	 * The number of pages (when pagination is enabled).
	 *
	 * @var int
	 */
	private $max_num_pages = 0;

	/**
	 * The number of orders found.
	 *
	 * @var int
	 */
	private $found_orders = 0;

	/**
	 * Field query parser.
	 *
	 * @var OrdersTableFieldQuery
	 */
	private $field_query = null;

	/**
	 * Meta query parser.
	 *
	 * @var OrdersTableMetaQuery
	 */
	private $meta_query = null;

	/**
	 * Search query parser.
	 *
	 * @var OrdersTableSearchQuery?
	 */
	private $search_query = null;

	/**
	 * Date query parser.
	 *
	 * @var WP_Date_Query
	 */
	private $date_query = null;

	/**
	 * Instance of the OrdersTableDataStore class.
	 *
	 * @var OrdersTableDataStore
	 */
	private $order_datastore = null;

	/**
	 * Whether to run filters to modify the query or not.
	 *
	 * @var boolean
	 */
	private $suppress_filters = false;

	/**
	 * Sets up and runs the query after processing arguments.
	 *
	 * @param array $args Array of query vars.
	 */
	public function __construct( $args = array() ) {
		// 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.
		$this->order_datastore = wc_get_container()->get( OrdersTableDataStore::class );

		$this->tables   = $this->order_datastore::get_all_table_names_with_id();
		$this->mappings = $this->order_datastore->get_all_order_column_mappings();

		$this->suppress_filters = array_key_exists( 'suppress_filters', $args ) ? (bool) $args['suppress_filters'] : false;
		unset( $args['suppress_filters'] );

		$this->args = $args;

		// TODO: args to be implemented.
		unset( $this->args['customer_note'], $this->args['name'] );

		$this->build_query();
		if ( ! $this->maybe_override_query() ) {
			$this->run_query();
		}
	}

	/**
	 * Lets the `woocommerce_hpos_pre_query` filter override the query.
	 *
	 * @return boolean Whether the query was overridden or not.
	 */
	private function maybe_override_query(): bool {
		/**
		 * Filters the orders array before the query takes place.
		 *
		 * Return a non-null value to bypass the HPOS default order queries.
		 *
		 * If the query includes limits via the `limit`, `page`, or `offset` arguments, we
		 * encourage the `found_orders` and `max_num_pages` properties to also be set.
		 *
		 * @since 8.2.0
		 *
		 * @param array|null $order_data {
		 *     An array of order data.
		 *     @type int[] $orders        Return an array of order IDs data to short-circuit the HPOS query,
		 *                                or null to allow HPOS to run its normal query.
		 *     @type int   $found_orders  The number of orders found.
		 *     @type int   $max_num_pages The number of pages.
		 * }
		 * @param OrdersTableQuery   $query The OrdersTableQuery instance.
		 * @param string             $sql The OrdersTableQuery instance.
		 */
		$pre_query = apply_filters( 'woocommerce_hpos_pre_query', null, $this, $this->sql );
		if ( ! $pre_query || ! isset( $pre_query[0] ) || ! is_array( $pre_query[0] ) ) {
			return false;
		}

		// If the filter set the orders, make sure the others values are set as well and skip running the query.
		list( $this->orders, $this->found_orders, $this->max_num_pages ) = $pre_query;

		if ( ! is_int( $this->found_orders ) || $this->found_orders < 1 ) {
			$this->found_orders = count( $this->orders );
		}

		if ( ! is_int( $this->max_num_pages ) || $this->max_num_pages < 1 ) {
			if ( ! $this->arg_isset( 'limit' ) || ! is_int( $this->args['limit'] ) || $this->args['limit'] < 1 ) {
				$this->args['limit'] = 10;
			}
			$this->max_num_pages = (int) ceil( $this->found_orders / $this->args['limit'] );
		}

		return true;
	}

	/**
	 * Remaps some legacy and `WP_Query` specific query vars to vars available in the customer order table scheme.
	 *
	 * @return void
	 */
	private function maybe_remap_args(): void {
		$mapping = array(
			// WP_Query legacy.
			'post_date'           => 'date_created',
			'post_date_gmt'       => 'date_created_gmt',
			'post_modified'       => 'date_updated',
			'post_modified_gmt'   => 'date_updated_gmt',
			'post_status'         => 'status',
			'_date_completed'     => 'date_completed',
			'_date_paid'          => 'date_paid',
			'paged'               => 'page',
			'post_parent'         => 'parent_order_id',
			'post_parent__in'     => 'parent_order_id',
			'post_parent__not_in' => 'parent_exclude',
			'post__not_in'        => 'exclude',
			'posts_per_page'      => 'limit',
			'p'                   => 'id',
			'post__in'            => 'id',
			'post_type'           => 'type',
			'fields'              => 'return',

			'customer_user'       => 'customer_id',
			'order_currency'      => 'currency',
			'order_version'       => 'woocommerce_version',
			'cart_discount'       => 'discount_total_amount',
			'cart_discount_tax'   => 'discount_tax_amount',
			'order_shipping'      => 'shipping_total_amount',
			'order_shipping_tax'  => 'shipping_tax_amount',
			'order_tax'           => 'tax_amount',

			// Translate from WC_Order_Query to table structure.
			'version'             => 'woocommerce_version',
			'date_modified'       => 'date_updated',
			'date_modified_gmt'   => 'date_updated_gmt',
			'discount_total'      => 'discount_total_amount',
			'discount_tax'        => 'discount_tax_amount',
			'shipping_total'      => 'shipping_total_amount',
			'shipping_tax'        => 'shipping_tax_amount',
			'cart_tax'            => 'tax_amount',
			'total'               => 'total_amount',
			'customer_ip_address' => 'ip_address',
			'customer_user_agent' => 'user_agent',
			'parent'              => 'parent_order_id',
		);

		foreach ( $mapping as $query_key => $table_field ) {
			if ( isset( $this->args[ $query_key ] ) && '' !== $this->args[ $query_key ] ) {
				$this->args[ $table_field ] = $this->args[ $query_key ];
				unset( $this->args[ $query_key ] );
			}
		}

		// meta_query.
		$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

		$shortcut_meta_query = array();
		foreach ( array( 'key', 'value', 'compare', 'type', 'compare_key', 'type_key' ) as $key ) {
			if ( $this->arg_isset( "meta_{$key}" ) ) {
				$shortcut_meta_query[ $key ] = $this->args[ "meta_{$key}" ];
			}
		}

		if ( ! empty( $shortcut_meta_query ) ) {
			if ( ! empty( $this->args['meta_query'] ) ) {
				$this->args['meta_query'] = array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
					'relation' => 'AND',
					$shortcut_meta_query,
					$this->args['meta_query'],
				);
			} else {
				$this->args['meta_query'] = array( $shortcut_meta_query ); // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
			}
		}
	}

	/**
	 * Generates a `WP_Date_Query` compatible query from a given date.
	 * YYYY-MM-DD queries have 'day' precision for backwards compatibility.
	 *
	 * @param mixed  $date The date. Can be a {@see \WC_DateTime}, a timestamp or a string.
	 * @return array An array with keys 'year', 'month', 'day' and possibly 'hour', 'minute' and 'second'.
	 */
	private function date_to_date_query_arg( $date ): array {
		$result    = array(
			'year'  => '',
			'month' => '',
			'day'   => '',
		);

		if ( is_numeric( $date ) ) {
			$date      = new \WC_DateTime( "@{$date}", new \DateTimeZone( 'UTC' ) );
			$precision = 'second';
		} elseif ( ! is_a( $date, 'WC_DateTime' ) ) {
			// For backwards compat (see https://github.com/woocommerce/woocommerce/wiki/wc_get_orders-and-WC_Order_Query#date)
			// only YYYY-MM-DD is considered for date values. Timestamps do support second precision.
			$date      = wc_string_to_datetime( date( 'Y-m-d', strtotime( $date ) ) );
			$precision = 'day';
		}

		$result['year']  = $date->date( 'Y' );
		$result['month'] = $date->date( 'm' );
		$result['day']   = $date->date( 'd' );

		if ( 'second' === $precision ) {
			$result['hour']   = $date->date( 'H' );
			$result['minute'] = $date->date( 'i' );
			$result['second'] = $date->date( 's' );
		}

		return $result;
	}

	/**
	 * Returns UTC-based date query arguments for a combination of local time dates and a date shorthand operator.
	 *
	 * @param  array $dates_raw Array of dates (in local time) to use in combination with the operator.
	 * @param  string $operator One of the operators supported by date queries (<, <=, =, ..., >, >=).
	 * @return array Partial date query arg with relevant dates now UTC-based.
	 *
	 * @since 8.2.0
	 */
	private function local_time_to_gmt_date_query( $dates_raw, $operator ) {
		$result = array();

		// Convert YYYY-MM-DD to UTC timestamp. Per https://github.com/woocommerce/woocommerce/wiki/wc_get_orders-and-WC_Order_Query#date only date is relevant (time is ignored).
		foreach ( $dates_raw as &$raw_date ) {
			$raw_date = is_numeric( $raw_date ) ? $raw_date : strtotime( get_gmt_from_date( date( 'Y-m-d', strtotime( $raw_date ) ) ) );
		}

		$date1  = end( $dates_raw );

		switch ( $operator ) {
			case '>':
				$result = array(
					'after'     => $this->date_to_date_query_arg( $date1 + DAY_IN_SECONDS ),
					'inclusive' => true,
				);
				break;
			case '>=':
				$result = array(
					'after'     => $this->date_to_date_query_arg( $date1 ),
					'inclusive' => true,
				);
				break;
			case '=':
				$result = array(
					'relation' => 'AND',
					array(
						'after'     => $this->date_to_date_query_arg( $date1 ),
						'inclusive' => true,
					),
					array(
						'before'     => $this->date_to_date_query_arg( $date1 + DAY_IN_SECONDS ),
						'inclusive'  => false,
					)
				);
				break;
			case '<=':
				$result = array(
					'before'    => $this->date_to_date_query_arg( $date1 + DAY_IN_SECONDS ),
					'inclusive' => false,
				);
				break;
			case '<':
				$result = array(
					'before'    => $this->date_to_date_query_arg( $date1 ),
					'inclusive' => false,
				);
				break;
			case '...':
				$result = array(
					'relation' => 'AND',
					$this->local_time_to_gmt_date_query( array( $dates_raw[1] ), '<=' ),
					$this->local_time_to_gmt_date_query( array( $dates_raw[0] ), '>=' ),
				);

				break;
		}

		if ( ! $result ) {
			throw new \Exception( 'Please specify a valid date shorthand operator.' );
		}

		return $result;
	}

	/**
	 * Processes date-related query args and merges the result into 'date_query'.
	 *
	 * @return void
	 * @throws \Exception When date args are invalid.
	 */
	private function process_date_args(): void {
		if ( $this->arg_isset( 'date_query' ) ) {
			// Process already passed date queries args.
			$this->args['date_query'] = $this->map_gmt_and_post_keys_to_hpos_keys( $this->args['date_query'] );
		}

		$valid_operators        = array( '>', '>=', '=', '<=', '<', '...' );
		$date_queries           = array();
		$local_to_gmt_date_keys = array(
			'date_created'   => 'date_created_gmt',
			'date_updated'   => 'date_updated_gmt',
			'date_paid'      => 'date_paid_gmt',
			'date_completed' => 'date_completed_gmt',
		);

		$gmt_date_keys   = array_values( $local_to_gmt_date_keys );
		$local_date_keys = array_keys( $local_to_gmt_date_keys );

		$valid_date_keys = array_merge( $gmt_date_keys, $local_date_keys );
		$date_keys       = array_filter( $valid_date_keys, array( $this, 'arg_isset' ) );

		foreach ( $date_keys as $date_key ) {
			$is_local   = in_array( $date_key, $local_date_keys, true );
			$date_value = $this->args[ $date_key ];

			$operator   = '=';
			$dates_raw  = array();
			$dates      = array();

			if ( is_string( $date_value ) && preg_match( self::REGEX_SHORTHAND_DATES, $date_value, $matches ) ) {
				$operator = in_array( $matches[2], $valid_operators, true ) ? $matches[2] : '';

				if ( ! empty( $matches[1] ) ) {
					$dates_raw[] = $matches[1];
				}

				$dates_raw[] = $matches[3];
			} else {
				$dates_raw[] = $date_value;
			}

			if ( empty( $dates_raw ) || ! $operator || ( '...' === $operator && count( $dates_raw ) < 2 ) ) {
				throw new \Exception( 'Invalid date_query' );
			}

			if ( $is_local ) {
				$date_key = $local_to_gmt_date_keys[ $date_key ];

				if ( ! is_numeric( $dates_raw[0] ) && ( ! isset( $dates_raw[1] ) || ! is_numeric( $dates_raw[1] ) ) ) {
					// Only non-numeric args can be considered local time. Timestamps are assumed to be UTC per https://github.com/woocommerce/woocommerce/wiki/wc_get_orders-and-WC_Order_Query#date.
					$date_queries[] = array_merge(
						array(
							'column' => $date_key,
						),
						$this->local_time_to_gmt_date_query( $dates_raw, $operator )
					);

					continue;
				}
			}

			$operator_to_keys = array();

			if ( in_array( $operator, array( '>', '>=', '...' ), true ) ) {
				$operator_to_keys[] = 'after';
			}

			if ( in_array( $operator, array( '<', '<=', '...' ), true ) ) {
				$operator_to_keys[] = 'before';
			}

			$dates          = array_map( array( $this, 'date_to_date_query_arg' ), $dates_raw );
			$date_queries[] = array_merge(
				array(
					'column'    => $date_key,
					'inclusive' => ! in_array( $operator, array( '<', '>' ), true ),
				),
				'=' === $operator
					? end( $dates )
					: array_combine( $operator_to_keys, $dates )
			);
		}

		// Add top-level date parameters to the date_query.
		$tl_query = array();
		foreach ( array( 'hour', 'minute', 'second', 'year', 'monthnum', 'week', 'day', 'year' ) as $tl_key ) {
			if ( $this->arg_isset( $tl_key ) ) {
				$tl_query[ $tl_key ] = $this->args[ $tl_key ];
				unset( $this->args[ $tl_key ] );
			}
		}

		if ( $tl_query ) {
			$tl_query['column'] = 'date_created_gmt';
			$date_queries[]     = $tl_query;
		}

		if ( $date_queries ) {
			if ( ! $this->arg_isset( 'date_query' ) ) {
				$this->args['date_query'] = array();
			}

			$this->args['date_query'] = array_merge(
				array( 'relation' => 'AND' ),
				$date_queries,
				$this->args['date_query']
			);
		}

		$this->process_date_query_columns();
	}

	/**
	 * Helper function to map posts and gmt based keys to HPOS keys.
	 *
	 * @param array $query Date query argument.
	 *
	 * @return array|mixed Date query argument with modified keys.
	 */
	private function map_gmt_and_post_keys_to_hpos_keys( $query ) {
		if ( ! is_array( $query ) ) {
			return $query;
		}

		$post_to_hpos_mappings = array(
			'post_date'         => 'date_created',
			'post_date_gmt'     => 'date_created_gmt',
			'post_modified'     => 'date_updated',
			'post_modified_gmt' => 'date_updated_gmt',
			'_date_completed'   => 'date_completed',
			'_date_paid'        => 'date_paid',
			'date_modified'     => 'date_updated',
			'date_modified_gmt' => 'date_updated_gmt',
		);

		$local_to_gmt_date_keys = array(
			'date_created'   => 'date_created_gmt',
			'date_updated'   => 'date_updated_gmt',
			'date_paid'      => 'date_paid_gmt',
			'date_completed' => 'date_completed_gmt',
		);

		array_walk(
			$query,
			function ( &$sub_query ) {
				$sub_query = $this->map_gmt_and_post_keys_to_hpos_keys( $sub_query );
			}
		);

		if ( ! isset( $query['column'] ) ) {
			return $query;
		}

		if ( isset( $post_to_hpos_mappings[ $query['column'] ] ) ) {
			$query['column'] = $post_to_hpos_mappings[ $query['column'] ];
		}

		// Convert any local dates to GMT.
		if ( isset( $local_to_gmt_date_keys[ $query['column'] ] ) ) {
			$query['column']  = $local_to_gmt_date_keys[ $query['column'] ];
			$op               = isset( $query['after'] ) ? 'after' : 'before';
			$date_value_local = $query[ $op ];
			$date_value_gmt   = wc_string_to_timestamp( get_gmt_from_date( wc_string_to_datetime( $date_value_local ) ) );
			$query[ $op ]     = $this->date_to_date_query_arg( $date_value_gmt );
		}

		return $query;
	}

	/**
	 * Makes sure all 'date_query' columns are correctly prefixed and their respective tables are being JOIN'ed.
	 *
	 * @return void
	 */
	private function process_date_query_columns() {
		global $wpdb;

		$legacy_columns = array(
			'post_date'         => 'date_created_gmt',
			'post_date_gmt'     => 'date_created_gmt',
			'post_modified'     => 'date_modified_gmt',
			'post_modified_gmt' => 'date_updated_gmt',
		);
		$table_mapping  = array(
			'date_created_gmt'   => $this->tables['orders'],
			'date_updated_gmt'   => $this->tables['orders'],
			'date_paid_gmt'      => $this->tables['operational_data'],
			'date_completed_gmt' => $this->tables['operational_data'],
		);

		if ( empty( $this->args['date_query'] ) ) {
			return;
		}

		array_walk_recursive(
			$this->args['date_query'],
			function( &$value, $key ) use ( $legacy_columns, $table_mapping, $wpdb ) {
				if ( 'column' !== $key ) {
					return;
				}

				// Translate legacy columns from wp_posts if necessary.
				$value =
					( isset( $legacy_columns[ $value ] ) || isset( $legacy_columns[ "{$wpdb->posts}.{$value}" ] ) )
					? $legacy_columns[ $value ]
					: $value;

				$table = $table_mapping[ $value ] ?? null;

				if ( ! $table ) {
					return;
				}

				$value = "{$table}.{$value}";

				if ( $table !== $this->tables['orders'] ) {
					$this->join( $table, '', '', 'inner', true );
				}
			}
		);
	}

	/**
	 * Sanitizes the 'status' query var.
	 *
	 * @return void
	 */
	private function sanitize_status(): void {
		// Sanitize status.
		$valid_statuses = array_keys( wc_get_order_statuses() );

		if ( empty( $this->args['status'] ) || 'any' === $this->args['status'] ) {
			$this->args['status'] = $valid_statuses;
		} elseif ( 'all' === $this->args['status'] ) {
			$this->args['status'] = array();
		} else {
			$this->args['status'] = is_array( $this->args['status'] ) ? $this->args['status'] : array( $this->args['status'] );

			foreach ( $this->args['status'] as &$status ) {
				$status = in_array( 'wc-' . $status, $valid_statuses, true ) ? 'wc-' . $status : $status;
			}

			$this->args['status'] = array_unique( array_filter( $this->args['status'] ) );
		}
	}

	/**
	 * Parses and sanitizes the 'orderby' query var.
	 *
	 * @return void
	 */
	private function sanitize_order_orderby(): void {
		// Allowed keys.
		// TODO: rand, meta keys, etc.
		$allowed_keys = array( 'ID', 'id', 'type', 'date', 'modified', 'parent' );

		// Translate $orderby to a valid field.
		$mapping = array(
			'ID'            => "{$this->tables['orders']}.id",
			'id'            => "{$this->tables['orders']}.id",
			'type'          => "{$this->tables['orders']}.type",
			'date'          => "{$this->tables['orders']}.date_created_gmt",
			'date_created'  => "{$this->tables['orders']}.date_created_gmt",
			'modified'      => "{$this->tables['orders']}.date_updated_gmt",
			'date_modified' => "{$this->tables['orders']}.date_updated_gmt",
			'parent'        => "{$this->tables['orders']}.parent_order_id",
			'total'         => "{$this->tables['orders']}.total_amount",
			'order_total'   => "{$this->tables['orders']}.total_amount",
		);

		$order   = $this->args['order'] ?? '';
		$orderby = $this->args['orderby'] ?? '';

		if ( 'none' === $orderby ) {
			return;
		}

		// No need to sanitize, will be processed in calling function.
		if ( 'include' === $orderby || 'post__in' === $orderby ) {
			return;
		}

		if ( is_string( $orderby ) ) {
			$orderby_fields = array_map( 'trim', explode( ' ', $orderby ) );
			$orderby        = array();
			foreach ( $orderby_fields as $field ) {
				$orderby[ $field ] = $order;
			}
		}

		$allowed_orderby = array_merge(
			array_keys( $mapping ),
			array_values( $mapping ),
			$this->meta_query ? $this->meta_query->get_orderby_keys() : array()
		);

		$this->args['orderby'] = array();
		foreach ( $orderby as $order_key => $order ) {
			if ( ! in_array( $order_key, $allowed_orderby, true ) ) {
				continue;
			}

			if ( isset( $mapping[ $order_key ] ) ) {
				$order_key = $mapping[ $order_key ];
			}

			$this->args['orderby'][ $order_key ] = $this->sanitize_order( $order );
		}
	}

	/**
	 * Makes sure the order in an ORDER BY statement is either 'ASC' o 'DESC'.
	 *
	 * @param string $order The unsanitized order.
	 * @return string The sanitized order.
	 */
	private function sanitize_order( string $order ): string {
		$order = strtoupper( $order );

		return in_array( $order, array( 'ASC', 'DESC' ), true ) ? $order : 'DESC';
	}

	/**
	 * Builds the final SQL query to be run.
	 *
	 * @return void
	 */
	private function build_query(): void {
		$this->maybe_remap_args();

		// Field queries.
		if ( ! empty( $this->args['field_query'] ) ) {
			$this->field_query = new OrdersTableFieldQuery( $this );
			$sql               = $this->field_query->get_sql_clauses();
			$this->join        = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
			$this->where       = $sql['where'] ? array_merge( $this->where, $sql['where'] ) : $this->where;
		}

		// Build query.
		$this->process_date_args();
		$this->process_orders_table_query_args();
		$this->process_operational_data_table_query_args();
		$this->process_addresses_table_query_args();

		// Search queries.
		if ( ! empty( $this->args['s'] ) ) {
			$this->search_query = new OrdersTableSearchQuery( $this );
			$sql                = $this->search_query->get_sql_clauses();
			$this->join         = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
			$this->where        = $sql['where'] ? array_merge( $this->where, $sql['where'] ) : $this->where;
		}

		// Meta queries.
		if ( ! empty( $this->args['meta_query'] ) ) {
			$this->meta_query = new OrdersTableMetaQuery( $this );

			$sql = $this->meta_query->get_sql_clauses();

			$this->join  = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
			$this->where = $sql['where'] ? array_merge( $this->where, array( $sql['where'] ) ) : $this->where;

		}

		// Date queries.
		if ( ! empty( $this->args['date_query'] ) ) {
			$this->date_query = new \WP_Date_Query( $this->args['date_query'], "{$this->tables['orders']}.date_created_gmt" );
			$this->where[]    = substr( trim( $this->date_query->get_sql() ), 3 ); // WP_Date_Query includes "AND".
		}

		$this->process_orderby();
		$this->process_limit();

		$orders_table = $this->tables['orders'];

		// 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.
		$this->groupby[] = "{$this->tables['orders']}.id";
		$this->fields    = "{$orders_table}.id";
		$fields          = $this->fields;

		// JOIN.
		$join = implode( ' ', array_unique( array_filter( array_map( 'trim', $this->join ) ) ) );

		// WHERE.
		$where = '1=1';
		foreach ( $this->where as $_where ) {
			$where .= " AND ({$_where})";
		}

		// ORDER BY.
		$orderby = $this->orderby ? implode( ', ', $this->orderby ) : '';

		// LIMITS.
		$limits = '';

		if ( ! empty( $this->limits ) && count( $this->limits ) === 2 ) {
			list( $offset, $row_count ) = $this->limits;
			$row_count                  = -1 === $row_count ? self::MYSQL_MAX_UNSIGNED_BIGINT : (int) $row_count;
			$limits                     = 'LIMIT ' . (int) $offset . ', ' . $row_count;
		}

		// GROUP BY.
		$groupby = $this->groupby ? implode( ', ', (array) $this->groupby ) : '';

		$pieces = compact( 'fields', 'join', 'where', 'groupby', 'orderby', 'limits' );

		if ( ! $this->suppress_filters ) {
			/**
			 * Filters all query clauses at once.
			 * Covers the fields (SELECT), JOIN, WHERE, GROUP BY, ORDER BY, and LIMIT clauses.
			 *
			 * @since 7.9.0
			 *
			 * @param string[]         $clauses {
			 *     Associative array of the clauses for the query.
			 *
			 *     @type string $fields  The SELECT clause of the query.
			 *     @type string $join    The JOIN clause of the query.
			 *     @type string $where   The WHERE clause of the query.
			 *     @type string $groupby The GROUP BY clause of the query.
			 *     @type string $orderby The ORDER BY clause of the query.
			 *     @type string $limits  The LIMIT clause of the query.
			 * }
			 * @param OrdersTableQuery $query   The OrdersTableQuery instance (passed by reference).
			 * @param array            $args    Query args.
			 */
			$clauses = (array) apply_filters_ref_array( 'woocommerce_orders_table_query_clauses', array( $pieces, &$this, $this->args ) );

			$fields  = $clauses['fields'] ?? '';
			$join    = $clauses['join'] ?? '';
			$where   = $clauses['where'] ?? '';
			$groupby = $clauses['groupby'] ?? '';
			$orderby = $clauses['orderby'] ?? '';
			$limits  = $clauses['limits'] ?? '';
		}

		$groupby = $groupby ? ( 'GROUP BY ' . $groupby ) : '';
		$orderby = $orderby ? ( 'ORDER BY ' . $orderby ) : '';

		$this->sql = "SELECT $fields FROM $orders_table $join WHERE $where $groupby $orderby $limits";

		if ( ! $this->suppress_filters ) {
			/**
			 * Filters the completed SQL query.
			 *
			 * @since 7.9.0
			 *
			 * @param string           $sql   The complete SQL query.
			 * @param OrdersTableQuery $query The OrdersTableQuery instance (passed by reference).
			 * @param array            $args  Query args.
			 */
			$this->sql = apply_filters_ref_array( 'woocommerce_orders_table_query_sql', array( $this->sql, &$this, $this->args ) );
		}

		$this->build_count_query( $fields, $join, $where, $groupby );
	}

	/**
	 * Build SQL query for counting total number of results.
	 *
	 * @param string $fields Prepared fields for SELECT clause.
	 * @param string $join Prepared JOIN clause.
	 * @param string $where Prepared WHERE clause.
	 * @param string $groupby Prepared GROUP BY clause.
	 */
	private function build_count_query( $fields, $join, $where, $groupby ) {
		if ( ! isset( $this->sql ) || '' === $this->sql ) {
			wc_doing_it_wrong( __FUNCTION__, 'Count query can only be build after main query is built.', '7.3.0' );
		}
		$orders_table    = $this->tables['orders'];
		$this->count_sql = "SELECT COUNT(DISTINCT $fields) FROM  $orders_table $join WHERE $where";
	}

	/**
	 * Returns the table alias for a given table mapping.
	 *
	 * @param string $mapping_id The mapping name (e.g. 'orders' or 'operational_data').
	 * @return string Table alias.
	 *
	 * @since 7.0.0
	 */
	public function get_core_mapping_alias( string $mapping_id ): string {
		return in_array( $mapping_id, array( 'billing_address', 'shipping_address' ), true )
			? $mapping_id
			: $this->tables[ $mapping_id ];
	}

	/**
	 * Returns an SQL JOIN clause that can be used to join the main orders table with another order table.
	 *
	 * @param string $mapping_id The mapping name (e.g. 'orders' or 'operational_data').
	 * @return string The JOIN clause.
	 *
	 * @since 7.0.0
	 */
	public function get_core_mapping_join( string $mapping_id ): string {
		global $wpdb;

		if ( 'orders' === $mapping_id ) {
			return '';
		}

		$is_address_mapping = in_array( $mapping_id, array( 'billing_address', 'shipping_address' ), true );

		$alias   = $this->get_core_mapping_alias( $mapping_id );
		$table   = $is_address_mapping ? $this->tables['addresses'] : $this->tables[ $mapping_id ];
		$join    = '';
		$join_on = '';

		$join .= "INNER JOIN `{$table}`" . ( $alias !== $table ? " AS `{$alias}`" : '' );

		if ( isset( $this->mappings[ $mapping_id ]['order_id'] ) ) {
			$join_on .= "`{$this->tables['orders']}`.id = `{$alias}`.order_id";
		}

		if ( $is_address_mapping ) {
			$join_on .= $wpdb->prepare( " AND `{$alias}`.address_type = %s", substr( $mapping_id, 0, -8 ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}

		return $join . ( $join_on ? " ON ( {$join_on} )" : '' );
	}

	/**
	 * JOINs the main orders table with another table.
	 *
	 * @param string  $table      Table name (including prefix).
	 * @param string  $alias      Table alias to use. Defaults to $table.
	 * @param string  $on         ON clause. Defaults to "wc_orders.id = {$alias}.order_id".
	 * @param string  $join_type  JOIN type: LEFT, RIGHT or INNER.
	 * @param boolean $alias_once If TRUE, table won't be JOIN'ed again if already JOIN'ed.
	 * @return void
	 * @throws \Exception When an error occurs, such as trying to re-use an alias with $alias_once = FALSE.
	 */
	private function join( string $table, string $alias = '', string $on = '', string $join_type = 'inner', bool $alias_once = false ) {
		$alias     = empty( $alias ) ? $table : $alias;
		$join_type = strtoupper( trim( $join_type ) );

		if ( $this->tables['orders'] === $alias ) {
			// translators: %s is a table name.
			throw new \Exception( sprintf( __( '%s can not be used as a table alias in OrdersTableQuery', 'woocommerce' ), $alias ) );
		}

		if ( empty( $on ) ) {
			if ( $this->tables['orders'] === $table ) {
				$on = "`{$this->tables['orders']}`.id = `{$alias}`.id";
			} else {
				$on = "`{$this->tables['orders']}`.id = `{$alias}`.order_id";
			}
		}

		if ( isset( $this->join[ $alias ] ) ) {
			if ( ! $alias_once ) {
				// translators: %s is a table name.
				throw new \Exception( sprintf( __( 'Can not re-use table alias "%s" in OrdersTableQuery.', 'woocommerce' ), $alias ) );
			}

			return;
		}

		if ( '' === $join_type || ! in_array( $join_type, array( 'LEFT', 'RIGHT', 'INNER' ), true ) ) {
			$join_type = 'INNER';
		}

		$sql_join  = '';
		$sql_join .= "{$join_type} JOIN `{$table}` ";
		$sql_join .= ( $alias !== $table ) ? "AS `{$alias}` " : '';
		$sql_join .= "ON ( {$on} )";

		$this->join[ $alias ] = $sql_join;
	}

	/**
	 * Generates a properly escaped and sanitized WHERE condition for a given field.
	 *
	 * @param string $table    The table the field belongs to.
	 * @param string $field    The field or column name.
	 * @param string $operator The operator to use in the condition. Defaults to '=' or 'IN' depending on $value.
	 * @param mixed  $value    The value.
	 * @param string $type     The column type as specified in {@see OrdersTableDataStore} column mappings.
	 * @return string The resulting WHERE condition.
	 */
	public function where( string $table, string $field, string $operator, $value, string $type ): string {
		global $wpdb;

		$db_util  = wc_get_container()->get( DatabaseUtil::class );
		$operator = strtoupper( '' !== $operator ? $operator : '=' );

		try {
			$format = $db_util->get_wpdb_format_for_type( $type );
		} catch ( \Exception $e ) {
			$format = '%s';
		}

		// = and != can be shorthands for IN and NOT in for array values.
		if ( is_array( $value ) && '=' === $operator ) {
			$operator = 'IN';
		} elseif ( is_array( $value ) && '!=' === $operator ) {
			$operator = 'NOT IN';
		}

		if ( ! in_array( $operator, array( '=', '!=', 'IN', 'NOT IN' ), true ) ) {
			return false;
		}

		if ( is_array( $value ) ) {
			$value = array_map( array( $db_util, 'format_object_value_for_db' ), $value, array_fill( 0, count( $value ), $type ) );
		} else {
			$value = $db_util->format_object_value_for_db( $value, $type );
		}

		if ( is_array( $value ) ) {
			$placeholder = array_fill( 0, count( $value ), $format );
			$placeholder = '(' . implode( ',', $placeholder ) . ')';
		} else {
			$placeholder = $format;
		}

		$sql = $wpdb->prepare( "{$table}.{$field} {$operator} {$placeholder}", $value ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare

		return $sql;
	}

	/**
	 * Processes fields related to the orders table.
	 *
	 * @return void
	 */
	private function process_orders_table_query_args(): void {
		$this->sanitize_status();

		$fields = array_filter(
			array(
				'id',
				'status',
				'type',
				'currency',
				'tax_amount',
				'customer_id',
				'billing_email',
				'total_amount',
				'parent_order_id',
				'payment_method',
				'payment_method_title',
				'transaction_id',
				'ip_address',
				'user_agent',
			),
			array( $this, 'arg_isset' )
		);

		foreach ( $fields as $arg_key ) {
			$this->where[] = $this->where( $this->tables['orders'], $arg_key, '=', $this->args[ $arg_key ], $this->mappings['orders'][ $arg_key ]['type'] );
		}

		if ( $this->arg_isset( 'parent_exclude' ) ) {
			$this->where[] = $this->where( $this->tables['orders'], 'parent_order_id', '!=', $this->args['parent_exclude'], 'int' );
		}

		if ( $this->arg_isset( 'exclude' ) ) {
			$this->where[] = $this->where( $this->tables['orders'], 'id', '!=', $this->args['exclude'], 'int' );
		}

		// 'customer' is a very special field.
		if ( $this->arg_isset( 'customer' ) ) {
			$customer_query = $this->generate_customer_query( $this->args['customer'] );

			if ( $customer_query ) {
				$this->where[] = $customer_query;
			}
		}
	}

	/**
	 * Generate SQL conditions for the 'customer' query.
	 *
	 * @param array  $values   List of customer ids or emails.
	 * @param string $relation 'OR' or 'AND' relation used to build the customer query.
	 * @return string SQL to be used in a WHERE clause.
	 */
	private function generate_customer_query( $values, string $relation = 'OR' ): string {
		$values = is_array( $values ) ? $values : array( $values );
		$ids    = array();
		$emails = array();

		foreach ( $values as $value ) {
			if ( is_array( $value ) ) {
				$sql      = $this->generate_customer_query( $value, 'AND' );
				$pieces[] = $sql ? '(' . $sql . ')' : '';
			} elseif ( is_numeric( $value ) ) {
				$ids[] = absint( $value );
			} elseif ( is_string( $value ) && is_email( $value ) ) {
				$emails[] = sanitize_email( $value );
			} else {
				// Invalid query.
				$pieces[] = '1=0';
			}
		}

		if ( $ids ) {
			$pieces[] = $this->where( $this->tables['orders'], 'customer_id', '=', $ids, 'int' );
		}

		if ( $emails ) {
			$pieces[] = $this->where( $this->tables['orders'], 'billing_email', '=', $emails, 'string' );
		}

		return $pieces ? implode( " $relation ", $pieces ) : '';
	}

	/**
	 * Processes fields related to the operational data table.
	 *
	 * @return void
	 */
	private function process_operational_data_table_query_args(): void {
		$fields = array_filter(
			array(
				'created_via',
				'woocommerce_version',
				'prices_include_tax',
				'order_key',
				'discount_total_amount',
				'discount_tax_amount',
				'shipping_total_amount',
				'shipping_tax_amount',
			),
			array( $this, 'arg_isset' )
		);

		if ( ! $fields ) {
			return;
		}

		$this->join(
			$this->tables['operational_data'],
			'',
			'',
			'inner',
			true
		);

		foreach ( $fields as $arg_key ) {
			$this->where[] = $this->where( $this->tables['operational_data'], $arg_key, '=', $this->args[ $arg_key ], $this->mappings['operational_data'][ $arg_key ]['type'] );
		}
	}

	/**
	 * Processes fields related to the addresses table.
	 *
	 * @return void
	 */
	private function process_addresses_table_query_args(): void {
		global $wpdb;

		foreach ( array( 'billing', 'shipping' ) as $address_type ) {
			$fields = array_filter(
				array(
					$address_type . '_first_name',
					$address_type . '_last_name',
					$address_type . '_company',
					$address_type . '_address_1',
					$address_type . '_address_2',
					$address_type . '_city',
					$address_type . '_state',
					$address_type . '_postcode',
					$address_type . '_country',
					$address_type . '_phone',
				),
				array( $this, 'arg_isset' )
			);

			if ( ! $fields ) {
				continue;
			}

			$this->join(
				$this->tables['addresses'],
				$address_type,
				$wpdb->prepare( "{$this->tables['orders']}.id = {$address_type}.order_id AND {$address_type}.address_type = %s", $address_type ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				'inner',
				false
			);

			foreach ( $fields as $arg_key ) {
				$column_name = str_replace( "{$address_type}_", '', $arg_key );

				$this->where[] = $this->where(
					$address_type,
					$column_name,
					'=',
					$this->args[ $arg_key ],
					$this->mappings[ "{$address_type}_address" ][ $column_name ]['type']
				);
			}
		}
	}

	/**
	 * Generates the ORDER BY clause.
	 *
	 * @return void
	 */
	private function process_orderby(): void {
		// 'order' and 'orderby' vars.
		$this->args['order'] = $this->sanitize_order( $this->args['order'] ?? '' );
		$this->sanitize_order_orderby();

		$orderby = $this->args['orderby'];

		if ( 'none' === $orderby ) {
			$this->orderby = '';
			return;
		}

		if ( 'include' === $orderby || 'post__in' === $orderby ) {
			$ids = $this->args['id'] ?? $this->args['includes'];
			if ( empty( $ids ) ) {
				return;
			}
			$ids           = array_map( 'absint', $ids );
			$this->orderby = array( "FIELD( {$this->tables['orders']}.id, " . implode( ',', $ids ) . ' )' );
			return;
		}

		$meta_orderby_keys = $this->meta_query ? $this->meta_query->get_orderby_keys() : array();

		$orderby_array = array();
		foreach ( $this->args['orderby'] as $_orderby => $order ) {
			if ( in_array( $_orderby, $meta_orderby_keys, true ) ) {
				$_orderby = $this->meta_query->get_orderby_clause_for_key( $_orderby );
			}

			$orderby_array[] = "{$_orderby} {$order}";
		}

		$this->orderby = $orderby_array;
	}

	/**
	 * Generates the limits to be used in the LIMIT clause.
	 *
	 * @return void
	 */
	private function process_limit(): void {
		$row_count = ( $this->arg_isset( 'limit' ) ? (int) $this->args['limit'] : false );
		$page      = ( $this->arg_isset( 'page' ) ? absint( $this->args['page'] ) : 1 );
		$offset    = ( $this->arg_isset( 'offset' ) ? absint( $this->args['offset'] ) : false );

		// Bool false indicates no limit was specified; less than -1 means an invalid value was passed (such as -3).
		if ( false === $row_count || $row_count < -1 ) {
			return;
		}

		if ( false === $offset && $row_count > -1 ) {
			$offset = (int) ( ( $page - 1 ) * $row_count );
		}

		$this->limits = array( $offset, $row_count );
	}

	/**
	 * Checks if a query var is set (i.e. not one of the "skipped values").
	 *
	 * @param string $arg_key Query var.
	 * @return bool TRUE if query var is set.
	 */
	public function arg_isset( string $arg_key ): bool {
		return ( isset( $this->args[ $arg_key ] ) && ! in_array( $this->args[ $arg_key ], self::SKIPPED_VALUES, true ) );
	}

	/**
	 * Runs the SQL query.
	 *
	 * @return void
	 */
	private function run_query(): void {
		global $wpdb;

		// Run query.
		$this->orders = array_map( 'absint', $wpdb->get_col( $this->sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		// Set max_num_pages and found_orders if necessary.
		if ( ( $this->arg_isset( 'no_found_rows' ) && ! $this->args['no_found_rows'] ) || empty( $this->orders ) ) {
			return;
		}

		if ( $this->limits ) {
			$this->found_orders  = absint( $wpdb->get_var( $this->count_sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$this->max_num_pages = (int) ceil( $this->found_orders / $this->args['limit'] );
		} else {
			$this->found_orders = count( $this->orders );
		}
	}

	/**
	 * Make some private available for backwards compatibility.
	 *
	 * @param string $name Property to get.
	 * @return mixed
	 */
	public function __get( string $name ) {
		switch ( $name ) {
			case 'found_orders':
			case 'found_posts':
				return $this->found_orders;
			case 'max_num_pages':
				return $this->max_num_pages;
			case 'posts':
			case 'orders':
				return $this->orders;
			case 'request':
				return $this->sql;
			default:
				break;
		}
	}

	/**
	 * Returns the value of one of the query arguments.
	 *
	 * @param string $arg_name Query var.
	 * @return mixed
	 */
	public function get( string $arg_name ) {
		return $this->args[ $arg_name ] ?? null;
	}

	/**
	 * Returns the name of one of the OrdersTableDatastore tables.
	 *
	 * @param string $table_id Table identifier. One of 'orders', 'operational_data', 'addresses', 'meta'.
	 * @return string The prefixed table name.
	 * @throws \Exception When table ID is not found.
	 */
	public function get_table_name( string $table_id = '' ): string {
		if ( ! isset( $this->tables[ $table_id ] ) ) {
			// Translators: %s is a table identifier.
			throw new \Exception( sprintf( __( 'Invalid table id: %s.', 'woocommerce' ), $table_id ) );
		}

		return $this->tables[ $table_id ];
	}

	/**
	 * Finds table and mapping information about a field or column.
	 *
	 * @param string $field Field to look for in `<mapping|field_name>.<column|field_name>` format or just `<field_name>`.
	 * @return false|array {
	 *     @type string $table      Full table name where the field is located.
	 *     @type string $mapping_id Unprefixed table or mapping name.
	 *     @type string $field_name Name of the corresponding order field.
	 *     @type string $column     Column in $table that corresponds to the field.
	 *     @type string $type       Field type.
	 * }
	 */
	public function get_field_mapping_info( $field ) {
		global $wpdb;

		$result = array(
			'table'       => '',
			'mapping_id'  => '',
			'field_name'  => '',
			'column'      => '',
			'column_type' => '',
		);

		$mappings_to_search = array();

		if ( false !== strstr( $field, '.' ) ) {
			list( $mapping_or_table, $field_name_or_col ) = explode( '.', $field );

			$mapping_or_table = substr( $mapping_or_table, 0, strlen( $wpdb->prefix ) ) === $wpdb->prefix ? substr( $mapping_or_table, strlen( $wpdb->prefix ) ) : $mapping_or_table;
			$mapping_or_table = 'wc_' === substr( $mapping_or_table, 0, 3 ) ? substr( $mapping_or_table, 3 ) : $mapping_or_table;

			if ( isset( $this->mappings[ $mapping_or_table ] ) ) {
				if ( isset( $this->mappings[ $mapping_or_table ][ $field_name_or_col ] ) ) {
					$result['mapping_id'] = $mapping_or_table;
					$result['column']     = $field_name_or_col;
				} else {
					$mappings_to_search = array( $mapping_or_table );
				}
			}
		} else {
			$field_name_or_col  = $field;
			$mappings_to_search = array_keys( $this->mappings );
		}

		foreach ( $mappings_to_search as $mapping_id ) {
			foreach ( $this->mappings[ $mapping_id ] as $column_name => $column_data ) {
				if ( isset( $column_data['name'] ) && $column_data['name'] === $field_name_or_col ) {
					$result['mapping_id'] = $mapping_id;
					$result['column']     = $column_name;
					break 2;
				}
			}
		}

		if ( ! $result['mapping_id'] || ! $result['column'] ) {
			return false;
		}

		$field_info = $this->mappings[ $result['mapping_id'] ][ $result['column'] ];

		$result['field_name']  = $field_info['name'];
		$result['column_type'] = $field_info['type'];
		$result['table']       = ( in_array( $result['mapping_id'], array( 'billing_address', 'shipping_address' ), true ) )
								? $this->tables['addresses']
								: $this->tables[ $result['mapping_id'] ];

		return $result;
	}

}
Name	Size	Permission
CustomOrdersTableController.php	18840 bytes	0644
DataSynchronizer.php	34744 bytes	0644
OrdersTableDataStore.php	96516 bytes	0644
OrdersTableDataStoreMeta.php	734 bytes	0644
OrdersTableFieldQuery.php	8672 bytes	0644
OrdersTableMetaQuery.php	18974 bytes	0644
OrdersTableQuery.php	44925 bytes	0644
OrdersTableRefundDataStore.php	6135 bytes	0644
OrdersTableSearchQuery.php	4626 bytes	0644
File Content: OrdersTableQuery.php

File Content: `OrdersTableQuery.php`
