DataStore.php
679 lines
| 1 | <?php |
| 2 | /** |
| 3 | * API\Reports\Orders\DataStore class file. |
| 4 | */ |
| 5 | |
| 6 | namespace Automattic\WooCommerce\Admin\API\Reports\Orders; |
| 7 | |
| 8 | defined( 'ABSPATH' ) || exit; |
| 9 | |
| 10 | use Automattic\WooCommerce\Admin\API\Reports\DataStore as ReportsDataStore; |
| 11 | use Automattic\WooCommerce\Admin\API\Reports\DataStoreInterface; |
| 12 | use Automattic\WooCommerce\Admin\API\Reports\SqlQuery; |
| 13 | use Automattic\WooCommerce\Internal\DataStores\Orders\OrdersTableDataStore; |
| 14 | use Automattic\WooCommerce\Internal\Traits\OrderAttributionMeta; |
| 15 | use Automattic\WooCommerce\Utilities\OrderUtil; |
| 16 | |
| 17 | |
| 18 | /** |
| 19 | * API\Reports\Orders\DataStore. |
| 20 | */ |
| 21 | class DataStore extends ReportsDataStore implements DataStoreInterface { |
| 22 | use OrderAttributionMeta; |
| 23 | |
| 24 | /** |
| 25 | * The cache key for order statuses. |
| 26 | */ |
| 27 | const ORDERS_STATUSES_ALL_CACHE_KEY = 'woocommerce_analytics_orders_statuses_all'; |
| 28 | |
| 29 | /** |
| 30 | * Dynamically sets the date column name based on configuration |
| 31 | * |
| 32 | * @override ReportsDataStore::__construct() |
| 33 | */ |
| 34 | public function __construct() { |
| 35 | $this->date_column_name = get_option( 'woocommerce_date_type', 'date_paid' ); |
| 36 | parent::__construct(); |
| 37 | } |
| 38 | |
| 39 | /** |
| 40 | * Set up all the hooks for maintaining data consistency (transients and co). |
| 41 | * |
| 42 | * @internal |
| 43 | */ |
| 44 | final public static function init() { |
| 45 | add_action( 'woocommerce_analytics_update_order_stats', array( __CLASS__, 'maybe_update_order_statuses_cache' ) ); |
| 46 | } |
| 47 | |
| 48 | /** |
| 49 | * Table used to get the data. |
| 50 | * |
| 51 | * @override ReportsDataStore::$table_name |
| 52 | * |
| 53 | * @var string |
| 54 | */ |
| 55 | protected static $table_name = 'wc_order_stats'; |
| 56 | |
| 57 | /** |
| 58 | * Cache identifier. |
| 59 | * |
| 60 | * @override ReportsDataStore::$cache_key |
| 61 | * |
| 62 | * @var string |
| 63 | */ |
| 64 | protected $cache_key = 'orders'; |
| 65 | |
| 66 | /** |
| 67 | * Mapping columns to data type to return correct response types. |
| 68 | * |
| 69 | * @override ReportsDataStore::$column_types |
| 70 | * |
| 71 | * @var array |
| 72 | */ |
| 73 | protected $column_types = array( |
| 74 | 'order_id' => 'intval', |
| 75 | 'parent_id' => 'intval', |
| 76 | 'date_created' => 'strval', |
| 77 | 'date_created_gmt' => 'strval', |
| 78 | 'status' => 'strval', |
| 79 | 'customer_id' => 'intval', |
| 80 | 'net_total' => 'floatval', |
| 81 | 'total_sales' => 'floatval', |
| 82 | 'num_items_sold' => 'intval', |
| 83 | 'customer_type' => 'strval', |
| 84 | ); |
| 85 | |
| 86 | /** |
| 87 | * Data store context used to pass to filters. |
| 88 | * |
| 89 | * @override ReportsDataStore::$context |
| 90 | * |
| 91 | * @var string |
| 92 | */ |
| 93 | protected $context = 'orders'; |
| 94 | |
| 95 | /** |
| 96 | * Assign report columns once full table name has been assigned. |
| 97 | * |
| 98 | * @override ReportsDataStore::assign_report_columns() |
| 99 | */ |
| 100 | protected function assign_report_columns() { |
| 101 | $table_name = self::get_db_table_name(); |
| 102 | // Avoid ambiguous columns in SQL query. |
| 103 | $this->report_columns = array( |
| 104 | 'order_id' => "DISTINCT {$table_name}.order_id", |
| 105 | 'parent_id' => "{$table_name}.parent_id", |
| 106 | // Add 'date' field based on date type setting. |
| 107 | 'date' => "{$table_name}.{$this->date_column_name} AS date", |
| 108 | 'date_created' => "{$table_name}.date_created", |
| 109 | 'date_created_gmt' => "{$table_name}.date_created_gmt", |
| 110 | 'status' => "REPLACE({$table_name}.status, 'wc-', '') as status", |
| 111 | 'customer_id' => "{$table_name}.customer_id", |
| 112 | 'net_total' => "{$table_name}.net_total", |
| 113 | 'total_sales' => "{$table_name}.total_sales", |
| 114 | 'num_items_sold' => "{$table_name}.num_items_sold", |
| 115 | 'customer_type' => "(CASE WHEN {$table_name}.returning_customer = 0 THEN 'new' ELSE 'returning' END) as customer_type", |
| 116 | ); |
| 117 | } |
| 118 | |
| 119 | /** |
| 120 | * Updates the database query with parameters used for orders report: coupons and products filters. |
| 121 | * |
| 122 | * @param array $query_args Query arguments supplied by the user. |
| 123 | */ |
| 124 | protected function add_sql_query_params( $query_args ) { |
| 125 | global $wpdb; |
| 126 | $order_stats_lookup_table = self::get_db_table_name(); |
| 127 | $order_coupon_lookup_table = $wpdb->prefix . 'wc_order_coupon_lookup'; |
| 128 | $order_product_lookup_table = $wpdb->prefix . 'wc_order_product_lookup'; |
| 129 | $order_tax_lookup_table = $wpdb->prefix . 'wc_order_tax_lookup'; |
| 130 | $operator = $this->get_match_operator( $query_args ); |
| 131 | $where_subquery = array(); |
| 132 | $have_joined_products_table = false; |
| 133 | |
| 134 | $this->add_time_period_sql_params( $query_args, $order_stats_lookup_table ); |
| 135 | $this->get_limit_sql_params( $query_args ); |
| 136 | $this->add_order_by_sql_params( $query_args ); |
| 137 | |
| 138 | $status_subquery = $this->get_status_subquery( $query_args ); |
| 139 | if ( $status_subquery ) { |
| 140 | if ( empty( $query_args['status_is'] ) && empty( $query_args['status_is_not'] ) ) { |
| 141 | $this->subquery->add_sql_clause( 'where', "AND {$status_subquery}" ); |
| 142 | } else { |
| 143 | $where_subquery[] = $status_subquery; |
| 144 | } |
| 145 | } |
| 146 | |
| 147 | $included_orders = $this->get_included_orders( $query_args ); |
| 148 | if ( $included_orders ) { |
| 149 | $where_subquery[] = "{$order_stats_lookup_table}.order_id IN ({$included_orders})"; |
| 150 | } |
| 151 | |
| 152 | $excluded_orders = $this->get_excluded_orders( $query_args ); |
| 153 | if ( $excluded_orders ) { |
| 154 | $where_subquery[] = "{$order_stats_lookup_table}.order_id NOT IN ({$excluded_orders})"; |
| 155 | } |
| 156 | |
| 157 | if ( $query_args['customer_type'] ) { |
| 158 | $returning_customer = 'returning' === $query_args['customer_type'] ? 1 : 0; |
| 159 | $where_subquery[] = "{$order_stats_lookup_table}.returning_customer = {$returning_customer}"; |
| 160 | } |
| 161 | |
| 162 | $refund_subquery = $this->get_refund_subquery( $query_args ); |
| 163 | $this->subquery->add_sql_clause( 'from', $refund_subquery['from_clause'] ); |
| 164 | if ( $refund_subquery['where_clause'] ) { |
| 165 | $where_subquery[] = $refund_subquery['where_clause']; |
| 166 | } |
| 167 | |
| 168 | $included_coupons = $this->get_included_coupons( $query_args ); |
| 169 | $excluded_coupons = $this->get_excluded_coupons( $query_args ); |
| 170 | if ( $included_coupons || $excluded_coupons ) { |
| 171 | $this->subquery->add_sql_clause( 'join', "LEFT JOIN {$order_coupon_lookup_table} ON {$order_stats_lookup_table}.order_id = {$order_coupon_lookup_table}.order_id" ); |
| 172 | } |
| 173 | if ( $included_coupons ) { |
| 174 | $where_subquery[] = "{$order_coupon_lookup_table}.coupon_id IN ({$included_coupons})"; |
| 175 | } |
| 176 | if ( $excluded_coupons ) { |
| 177 | $where_subquery[] = "({$order_coupon_lookup_table}.coupon_id IS NULL OR {$order_coupon_lookup_table}.coupon_id NOT IN ({$excluded_coupons}))"; |
| 178 | } |
| 179 | |
| 180 | $included_products = $this->get_included_products( $query_args ); |
| 181 | $excluded_products = $this->get_excluded_products( $query_args ); |
| 182 | if ( $included_products || $excluded_products ) { |
| 183 | $this->subquery->add_sql_clause( 'join', "LEFT JOIN {$order_product_lookup_table} product_lookup" ); |
| 184 | $this->subquery->add_sql_clause( 'join', "ON {$order_stats_lookup_table}.order_id = product_lookup.order_id" ); |
| 185 | } |
| 186 | if ( $included_products ) { |
| 187 | $this->subquery->add_sql_clause( 'join', "AND product_lookup.product_id IN ({$included_products})" ); |
| 188 | $where_subquery[] = 'product_lookup.order_id IS NOT NULL'; |
| 189 | } |
| 190 | if ( $excluded_products ) { |
| 191 | $this->subquery->add_sql_clause( 'join', "AND product_lookup.product_id IN ({$excluded_products})" ); |
| 192 | $where_subquery[] = 'product_lookup.order_id IS NULL'; |
| 193 | } |
| 194 | |
| 195 | $included_variations = $this->get_included_variations( $query_args ); |
| 196 | $excluded_variations = $this->get_excluded_variations( $query_args ); |
| 197 | if ( $included_variations || $excluded_variations ) { |
| 198 | $this->subquery->add_sql_clause( 'join', "LEFT JOIN {$order_product_lookup_table} variation_lookup" ); |
| 199 | $this->subquery->add_sql_clause( 'join', "ON {$order_stats_lookup_table}.order_id = variation_lookup.order_id" ); |
| 200 | } |
| 201 | if ( $included_variations ) { |
| 202 | $this->subquery->add_sql_clause( 'join', "AND variation_lookup.variation_id IN ({$included_variations})" ); |
| 203 | $where_subquery[] = 'variation_lookup.order_id IS NOT NULL'; |
| 204 | } |
| 205 | if ( $excluded_variations ) { |
| 206 | $this->subquery->add_sql_clause( 'join', "AND variation_lookup.variation_id IN ({$excluded_variations})" ); |
| 207 | $where_subquery[] = 'variation_lookup.order_id IS NULL'; |
| 208 | } |
| 209 | |
| 210 | $included_tax_rates = ! empty( $query_args['tax_rate_includes'] ) ? implode( ',', array_map( 'esc_sql', $query_args['tax_rate_includes'] ) ) : false; |
| 211 | $excluded_tax_rates = ! empty( $query_args['tax_rate_excludes'] ) ? implode( ',', array_map( 'esc_sql', $query_args['tax_rate_excludes'] ) ) : false; |
| 212 | if ( $included_tax_rates || $excluded_tax_rates ) { |
| 213 | $this->subquery->add_sql_clause( 'join', "LEFT JOIN {$order_tax_lookup_table} ON {$order_stats_lookup_table}.order_id = {$order_tax_lookup_table}.order_id" ); |
| 214 | } |
| 215 | if ( $included_tax_rates ) { |
| 216 | $where_subquery[] = "{$order_tax_lookup_table}.tax_rate_id IN ({$included_tax_rates})"; |
| 217 | } |
| 218 | if ( $excluded_tax_rates ) { |
| 219 | $where_subquery[] = "{$order_tax_lookup_table}.tax_rate_id NOT IN ({$excluded_tax_rates}) OR {$order_tax_lookup_table}.tax_rate_id IS NULL"; |
| 220 | } |
| 221 | |
| 222 | $attribute_subqueries = $this->get_attribute_subqueries( $query_args ); |
| 223 | if ( $attribute_subqueries['join'] && $attribute_subqueries['where'] ) { |
| 224 | $this->subquery->add_sql_clause( 'join', "JOIN {$order_product_lookup_table} ON {$order_stats_lookup_table}.order_id = {$order_product_lookup_table}.order_id" ); |
| 225 | |
| 226 | // Add JOINs for matching attributes. |
| 227 | foreach ( $attribute_subqueries['join'] as $attribute_join ) { |
| 228 | $this->subquery->add_sql_clause( 'join', $attribute_join ); |
| 229 | } |
| 230 | // Add WHEREs for matching attributes. |
| 231 | $where_subquery = array_merge( $where_subquery, $attribute_subqueries['where'] ); |
| 232 | } |
| 233 | |
| 234 | if ( 0 < count( $where_subquery ) ) { |
| 235 | $this->subquery->add_sql_clause( 'where', 'AND (' . implode( " {$operator} ", $where_subquery ) . ')' ); |
| 236 | } |
| 237 | } |
| 238 | |
| 239 | /** |
| 240 | * Get the default query arguments to be used by get_data(). |
| 241 | * These defaults are only partially applied when used via REST API, as that has its own defaults. |
| 242 | * |
| 243 | * @override ReportsDataStore::get_default_query_vars() |
| 244 | * |
| 245 | * @return array Query parameters. |
| 246 | */ |
| 247 | public function get_default_query_vars() { |
| 248 | $defaults = array_merge( |
| 249 | parent::get_default_query_vars(), |
| 250 | array( |
| 251 | 'orderby' => $this->date_column_name, |
| 252 | 'product_includes' => array(), |
| 253 | 'product_excludes' => array(), |
| 254 | 'coupon_includes' => array(), |
| 255 | 'coupon_excludes' => array(), |
| 256 | 'tax_rate_includes' => array(), |
| 257 | 'tax_rate_excludes' => array(), |
| 258 | 'customer_type' => null, |
| 259 | 'status_is' => array(), |
| 260 | 'extended_info' => false, |
| 261 | 'refunds' => null, |
| 262 | 'order_includes' => array(), |
| 263 | 'order_excludes' => array(), |
| 264 | ) |
| 265 | ); |
| 266 | |
| 267 | return $defaults; |
| 268 | } |
| 269 | |
| 270 | /** |
| 271 | * Returns the report data based on normalized parameters. |
| 272 | * Will be called by `get_data` if there is no data in cache. |
| 273 | * |
| 274 | * @override ReportsDataStore::get_noncached_data() |
| 275 | * |
| 276 | * @see get_data |
| 277 | * @param array $query_args Query parameters. |
| 278 | * @return stdClass|WP_Error Data object `{ totals: *, intervals: array, total: int, pages: int, page_no: int }`, or error. |
| 279 | */ |
| 280 | public function get_noncached_data( $query_args ) { |
| 281 | global $wpdb; |
| 282 | |
| 283 | $this->initialize_queries(); |
| 284 | |
| 285 | $data = (object) array( |
| 286 | 'data' => array(), |
| 287 | 'total' => 0, |
| 288 | 'pages' => 0, |
| 289 | 'page_no' => 0, |
| 290 | ); |
| 291 | |
| 292 | $selections = $this->selected_columns( $query_args ); |
| 293 | $params = $this->get_limit_params( $query_args ); |
| 294 | $this->add_sql_query_params( $query_args ); |
| 295 | /* phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared */ |
| 296 | $db_records_count = (int) $wpdb->get_var( |
| 297 | "SELECT COUNT( DISTINCT tt.order_id ) FROM ( |
| 298 | {$this->subquery->get_query_statement()} |
| 299 | ) AS tt" |
| 300 | ); |
| 301 | /* phpcs:enable */ |
| 302 | |
| 303 | if ( 0 === $params['per_page'] ) { |
| 304 | $total_pages = 0; |
| 305 | } else { |
| 306 | $total_pages = (int) ceil( $db_records_count / $params['per_page'] ); |
| 307 | } |
| 308 | if ( $query_args['page'] < 1 || $query_args['page'] > $total_pages ) { |
| 309 | $data = (object) array( |
| 310 | 'data' => array(), |
| 311 | 'total' => $db_records_count, |
| 312 | 'pages' => 0, |
| 313 | 'page_no' => 0, |
| 314 | ); |
| 315 | return $data; |
| 316 | } |
| 317 | |
| 318 | $this->subquery->clear_sql_clause( 'select' ); |
| 319 | $this->subquery->add_sql_clause( 'select', $selections ); |
| 320 | $this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) ); |
| 321 | $this->subquery->add_sql_clause( 'limit', $this->get_sql_clause( 'limit' ) ); |
| 322 | /* phpcs:disable WordPress.DB.PreparedSQL.NotPrepared */ |
| 323 | $orders_data = $wpdb->get_results( |
| 324 | $this->subquery->get_query_statement(), |
| 325 | ARRAY_A |
| 326 | ); |
| 327 | /* phpcs:enable */ |
| 328 | |
| 329 | if ( null === $orders_data ) { |
| 330 | return $data; |
| 331 | } |
| 332 | |
| 333 | if ( $query_args['extended_info'] ) { |
| 334 | $this->include_extended_info( $orders_data, $query_args ); |
| 335 | } |
| 336 | |
| 337 | $orders_data = array_map( array( $this, 'cast_numbers' ), $orders_data ); |
| 338 | $data = (object) array( |
| 339 | 'data' => $orders_data, |
| 340 | 'total' => $db_records_count, |
| 341 | 'pages' => $total_pages, |
| 342 | 'page_no' => (int) $query_args['page'], |
| 343 | ); |
| 344 | return $data; |
| 345 | } |
| 346 | |
| 347 | /** |
| 348 | * Normalizes order_by clause to match to SQL query. |
| 349 | * |
| 350 | * @override ReportsDataStore::normalize_order_by() |
| 351 | * |
| 352 | * @param string $order_by Order by option requeste by user. |
| 353 | * @return string |
| 354 | */ |
| 355 | protected function normalize_order_by( $order_by ) { |
| 356 | if ( 'date' === $order_by ) { |
| 357 | return $this->date_column_name; |
| 358 | } |
| 359 | |
| 360 | return $order_by; |
| 361 | } |
| 362 | |
| 363 | /** |
| 364 | * Enriches the order data. |
| 365 | * |
| 366 | * @param array $orders_data Orders data. |
| 367 | * @param array $query_args Query parameters. |
| 368 | */ |
| 369 | protected function include_extended_info( &$orders_data, $query_args ) { |
| 370 | $mapped_orders = $this->map_array_by_key( $orders_data, 'order_id' ); |
| 371 | $related_orders = $this->get_orders_with_parent_id( $mapped_orders ); |
| 372 | $order_ids = array_merge( array_keys( $mapped_orders ), array_keys( $related_orders ) ); |
| 373 | $products = $this->get_products_by_order_ids( $order_ids ); |
| 374 | $coupons = $this->get_coupons_by_order_ids( array_keys( $mapped_orders ) ); |
| 375 | $order_attributions = $this->get_order_attributions_by_order_ids( array_keys( $mapped_orders ) ); |
| 376 | $customers = $this->get_customers_by_orders( $orders_data ); |
| 377 | $mapped_customers = $this->map_array_by_key( $customers, 'customer_id' ); |
| 378 | |
| 379 | $mapped_data = array(); |
| 380 | foreach ( $products as $product ) { |
| 381 | if ( ! isset( $mapped_data[ $product['order_id'] ] ) ) { |
| 382 | $mapped_data[ $product['order_id'] ]['products'] = array(); |
| 383 | } |
| 384 | |
| 385 | $is_variation = '0' !== $product['variation_id']; |
| 386 | $product_data = array( |
| 387 | 'id' => $is_variation ? $product['variation_id'] : $product['product_id'], |
| 388 | 'name' => $product['product_name'], |
| 389 | 'quantity' => $product['product_quantity'], |
| 390 | ); |
| 391 | |
| 392 | if ( $is_variation ) { |
| 393 | $variation = wc_get_product( $product_data['id'] ); |
| 394 | /** |
| 395 | * Used to determine the separator for products and their variations titles. |
| 396 | * |
| 397 | * @since 4.0.0 |
| 398 | */ |
| 399 | $separator = apply_filters( 'woocommerce_product_variation_title_attributes_separator', ' - ', $variation ); |
| 400 | |
| 401 | if ( false === strpos( $product_data['name'], $separator ) ) { |
| 402 | $attributes = wc_get_formatted_variation( $variation, true, false ); |
| 403 | $product_data['name'] .= $separator . $attributes; |
| 404 | } |
| 405 | } |
| 406 | |
| 407 | $mapped_data[ $product['order_id'] ]['products'][] = $product_data; |
| 408 | |
| 409 | // If this product's order has another related order, it will be added to our mapped_data. |
| 410 | if ( isset( $related_orders [ $product['order_id'] ] ) ) { |
| 411 | $mapped_data[ $related_orders[ $product['order_id'] ]['order_id'] ] ['products'] [] = $product_data; |
| 412 | } |
| 413 | } |
| 414 | |
| 415 | foreach ( $coupons as $coupon ) { |
| 416 | if ( ! isset( $mapped_data[ $coupon['order_id'] ] ) ) { |
| 417 | $mapped_data[ $coupon['order_id'] ]['coupons'] = array(); |
| 418 | } |
| 419 | |
| 420 | $mapped_data[ $coupon['order_id'] ]['coupons'][] = array( |
| 421 | 'id' => $coupon['coupon_id'], |
| 422 | 'code' => wc_format_coupon_code( $coupon['coupon_code'] ), |
| 423 | ); |
| 424 | } |
| 425 | |
| 426 | foreach ( $orders_data as $key => $order_data ) { |
| 427 | $defaults = array( |
| 428 | 'products' => array(), |
| 429 | 'coupons' => array(), |
| 430 | 'customer' => array(), |
| 431 | 'attribution' => array(), |
| 432 | ); |
| 433 | $order_id = $order_data['order_id']; |
| 434 | |
| 435 | $orders_data[ $key ]['extended_info'] = isset( $mapped_data[ $order_id ] ) ? array_merge( $defaults, $mapped_data[ $order_id ] ) : $defaults; |
| 436 | if ( $order_data['customer_id'] && isset( $mapped_customers[ $order_data['customer_id'] ] ) ) { |
| 437 | $orders_data[ $key ]['extended_info']['customer'] = $mapped_customers[ $order_data['customer_id'] ]; |
| 438 | } |
| 439 | |
| 440 | $source_type = $order_attributions[ $order_id ]['_wc_order_attribution_source_type'] ?? ''; |
| 441 | $utm_source = $order_attributions[ $order_id ]['_wc_order_attribution_utm_source'] ?? ''; |
| 442 | $orders_data[ $key ]['extended_info']['attribution']['origin'] = $this->get_origin_label( $source_type, $utm_source ); |
| 443 | } |
| 444 | } |
| 445 | |
| 446 | /** |
| 447 | * Returns oreders that have a parent id |
| 448 | * |
| 449 | * @param array $orders Orders array. |
| 450 | * @return array |
| 451 | */ |
| 452 | protected function get_orders_with_parent_id( $orders ) { |
| 453 | $related_orders = array(); |
| 454 | foreach ( $orders as $order ) { |
| 455 | if ( '0' !== $order['parent_id'] ) { |
| 456 | $related_orders[ $order['parent_id'] ] = $order; |
| 457 | } |
| 458 | } |
| 459 | return $related_orders; |
| 460 | } |
| 461 | |
| 462 | /** |
| 463 | * Returns the same array index by a given key |
| 464 | * |
| 465 | * @param array $array Array to be looped over. |
| 466 | * @param string $key Key of values used for new array. |
| 467 | * @return array |
| 468 | */ |
| 469 | protected function map_array_by_key( $array, $key ) { |
| 470 | $mapped = array(); |
| 471 | foreach ( $array as $item ) { |
| 472 | $mapped[ $item[ $key ] ] = $item; |
| 473 | } |
| 474 | return $mapped; |
| 475 | } |
| 476 | |
| 477 | /** |
| 478 | * Get product IDs, names, and quantity from order IDs. |
| 479 | * |
| 480 | * @param array $order_ids Array of order IDs. |
| 481 | * @return array |
| 482 | */ |
| 483 | protected function get_products_by_order_ids( $order_ids ) { |
| 484 | global $wpdb; |
| 485 | $order_product_lookup_table = $wpdb->prefix . 'wc_order_product_lookup'; |
| 486 | $included_order_ids = implode( ',', $order_ids ); |
| 487 | |
| 488 | /* phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared */ |
| 489 | $products = $wpdb->get_results( |
| 490 | "SELECT |
| 491 | order_id, |
| 492 | product_id, |
| 493 | variation_id, |
| 494 | post_title as product_name, |
| 495 | product_qty as product_quantity |
| 496 | FROM {$wpdb->posts} |
| 497 | JOIN |
| 498 | {$order_product_lookup_table} |
| 499 | ON {$wpdb->posts}.ID = ( |
| 500 | CASE WHEN variation_id > 0 |
| 501 | THEN variation_id |
| 502 | ELSE product_id |
| 503 | END |
| 504 | ) |
| 505 | WHERE order_id IN ({$included_order_ids}) |
| 506 | AND product_qty > 0 |
| 507 | ", |
| 508 | ARRAY_A |
| 509 | ); |
| 510 | /* phpcs:enable */ |
| 511 | |
| 512 | return $products; |
| 513 | } |
| 514 | |
| 515 | /** |
| 516 | * Get customer data from Order data. |
| 517 | * |
| 518 | * @param array $orders Array of orders data. |
| 519 | * @return array |
| 520 | */ |
| 521 | protected function get_customers_by_orders( $orders ) { |
| 522 | global $wpdb; |
| 523 | |
| 524 | $customer_lookup_table = $wpdb->prefix . 'wc_customer_lookup'; |
| 525 | $customer_ids = array(); |
| 526 | |
| 527 | foreach ( $orders as $order ) { |
| 528 | if ( $order['customer_id'] ) { |
| 529 | $customer_ids[] = intval( $order['customer_id'] ); |
| 530 | } |
| 531 | } |
| 532 | |
| 533 | if ( empty( $customer_ids ) ) { |
| 534 | return array(); |
| 535 | } |
| 536 | |
| 537 | /* phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared */ |
| 538 | $customer_ids = implode( ',', $customer_ids ); |
| 539 | $customers = $wpdb->get_results( |
| 540 | "SELECT * FROM {$customer_lookup_table} WHERE customer_id IN ({$customer_ids})", |
| 541 | ARRAY_A |
| 542 | ); |
| 543 | /* phpcs:enable */ |
| 544 | |
| 545 | return $customers; |
| 546 | } |
| 547 | |
| 548 | /** |
| 549 | * Get coupon information from order IDs. |
| 550 | * |
| 551 | * @param array $order_ids Array of order IDs. |
| 552 | * @return array |
| 553 | */ |
| 554 | protected function get_coupons_by_order_ids( $order_ids ) { |
| 555 | global $wpdb; |
| 556 | $order_coupon_lookup_table = $wpdb->prefix . 'wc_order_coupon_lookup'; |
| 557 | $included_order_ids = implode( ',', $order_ids ); |
| 558 | |
| 559 | /* phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared */ |
| 560 | $coupons = $wpdb->get_results( |
| 561 | "SELECT order_id, coupon_id, post_title as coupon_code |
| 562 | FROM {$wpdb->posts} |
| 563 | JOIN {$order_coupon_lookup_table} ON {$order_coupon_lookup_table}.coupon_id = {$wpdb->posts}.ID |
| 564 | WHERE |
| 565 | order_id IN ({$included_order_ids}) |
| 566 | ", |
| 567 | ARRAY_A |
| 568 | ); |
| 569 | /* phpcs:enable */ |
| 570 | |
| 571 | return $coupons; |
| 572 | } |
| 573 | |
| 574 | /** |
| 575 | * Get order attributions data from order IDs. |
| 576 | * |
| 577 | * @param array $order_ids Array of order IDs. |
| 578 | * @return array |
| 579 | */ |
| 580 | protected function get_order_attributions_by_order_ids( $order_ids ) { |
| 581 | global $wpdb; |
| 582 | $order_meta_table = OrdersTableDataStore::get_meta_table_name(); |
| 583 | $included_order_ids = implode( ',', array_map( 'absint', $order_ids ) ); |
| 584 | |
| 585 | if ( OrderUtil::custom_orders_table_usage_is_enabled() ) { |
| 586 | /* phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared */ |
| 587 | $order_attributions_meta = $wpdb->get_results( |
| 588 | "SELECT order_id, meta_key, meta_value |
| 589 | FROM $order_meta_table |
| 590 | WHERE order_id IN ({$included_order_ids}) |
| 591 | AND meta_key IN ( '_wc_order_attribution_source_type', '_wc_order_attribution_utm_source' ) |
| 592 | ", |
| 593 | ARRAY_A |
| 594 | ); |
| 595 | /* phpcs:enable */ |
| 596 | } else { |
| 597 | /* phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared */ |
| 598 | $order_attributions_meta = $wpdb->get_results( |
| 599 | "SELECT post_id as order_id, meta_key, meta_value |
| 600 | FROM $wpdb->postmeta |
| 601 | WHERE post_id IN ({$included_order_ids}) |
| 602 | AND meta_key IN ( '_wc_order_attribution_source_type', '_wc_order_attribution_utm_source' ) |
| 603 | ", |
| 604 | ARRAY_A |
| 605 | ); |
| 606 | /* phpcs:enable */ |
| 607 | } |
| 608 | |
| 609 | $order_attributions = array(); |
| 610 | foreach ( $order_attributions_meta as $meta ) { |
| 611 | if ( ! isset( $order_attributions[ $meta['order_id'] ] ) ) { |
| 612 | $order_attributions[ $meta['order_id'] ] = array(); |
| 613 | } |
| 614 | $order_attributions[ $meta['order_id'] ][ $meta['meta_key'] ] = $meta['meta_value']; |
| 615 | } |
| 616 | |
| 617 | return $order_attributions; |
| 618 | } |
| 619 | |
| 620 | /** |
| 621 | * Get all statuses that have been synced. |
| 622 | * |
| 623 | * @return string[] Unique order statuses. |
| 624 | */ |
| 625 | public static function get_all_statuses() { |
| 626 | global $wpdb; |
| 627 | |
| 628 | $statuses = wp_cache_get( self::ORDERS_STATUSES_ALL_CACHE_KEY, 'woocommerce_analytics' ); |
| 629 | if ( false === $statuses ) { |
| 630 | $table_name = self::get_db_table_name(); |
| 631 | $statuses = $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT status FROM %i', $table_name ) ); |
| 632 | wp_cache_set( self::ORDERS_STATUSES_ALL_CACHE_KEY, $statuses, 'woocommerce_analytics', YEAR_IN_SECONDS ); |
| 633 | } |
| 634 | |
| 635 | return $statuses; |
| 636 | } |
| 637 | |
| 638 | /** |
| 639 | * Ensure the order status will present in `get_all_statuses` call result. |
| 640 | * |
| 641 | * @internal |
| 642 | * @param int $order_id Order ID. |
| 643 | * @return void |
| 644 | */ |
| 645 | public static function maybe_update_order_statuses_cache( $order_id ) { |
| 646 | $order = wc_get_order( $order_id ); |
| 647 | if ( $order ) { |
| 648 | $status = self::normalize_order_status( $order->get_status() ); |
| 649 | $statuses = self::get_all_statuses(); |
| 650 | if ( ! in_array( $status, $statuses, true ) ) { |
| 651 | $statuses[] = $status; |
| 652 | wp_cache_set( self::ORDERS_STATUSES_ALL_CACHE_KEY, $statuses, 'woocommerce_analytics', YEAR_IN_SECONDS ); |
| 653 | } |
| 654 | } |
| 655 | } |
| 656 | |
| 657 | /** |
| 658 | * Ensure the order status will present in `get_all_statuses` call result. |
| 659 | * |
| 660 | * @deprecated 10.3.0 Use maybe_update_order_statuses_cache(). |
| 661 | * @param int $order_id Order ID. |
| 662 | * @return void |
| 663 | */ |
| 664 | public static function maybe_update_order_statuses_transient( $order_id ) { |
| 665 | wc_deprecated_function( __METHOD__, '10.3.0', __CLASS__ . '::maybe_update_order_statuses_cache()' ); |
| 666 | self::maybe_update_order_statuses_cache( $order_id ); |
| 667 | } |
| 668 | |
| 669 | /** |
| 670 | * Initialize query objects. |
| 671 | */ |
| 672 | protected function initialize_queries() { |
| 673 | $this->clear_all_clauses(); |
| 674 | $this->subquery = new SqlQuery( $this->context . '_subquery' ); |
| 675 | $this->subquery->add_sql_clause( 'select', self::get_db_table_name() . '.order_id' ); |
| 676 | $this->subquery->add_sql_clause( 'from', self::get_db_table_name() ); |
| 677 | } |
| 678 | } |
| 679 |