Categories
2 months ago
Coupons
2 months ago
Customers
1 month ago
Downloads
1 year ago
Export
3 weeks ago
Import
3 years ago
Orders
2 weeks ago
PerformanceIndicators
10 months ago
Products
1 month ago
Revenue
5 months ago
Stock
6 months ago
Taxes
2 months ago
Variations
1 month ago
Cache.php
4 years ago
Controller.php
1 year ago
DataStore.php
3 weeks ago
DataStoreInterface.php
4 years ago
ExportableInterface.php
4 years ago
ExportableTraits.php
4 years ago
FilteredGetDataTrait.php
2 years ago
GenericController.php
1 year ago
GenericQuery.php
2 years ago
GenericStatsController.php
1 year ago
OrderAwareControllerTrait.php
1 year ago
ParameterException.php
4 years ago
Query.php
1 year ago
Segmenter.php
1 year ago
SqlQuery.php
3 years ago
StatsDataStoreTrait.php
1 year ago
TimeInterval.php
2 years ago
DataStore.php
1657 lines
| 1 | <?php |
| 2 | /** |
| 3 | * Admin\API\Reports\DataStore class file. |
| 4 | */ |
| 5 | |
| 6 | namespace Automattic\WooCommerce\Admin\API\Reports; |
| 7 | |
| 8 | if ( ! defined( 'ABSPATH' ) ) { |
| 9 | exit; |
| 10 | } |
| 11 | |
| 12 | use Automattic\WooCommerce\Admin\API\Reports\DataStoreInterface; |
| 13 | use Automattic\WooCommerce\Admin\API\Reports\TimeInterval; |
| 14 | |
| 15 | /** |
| 16 | * Common parent for custom report data stores. |
| 17 | * |
| 18 | * We use Report DataStores to separate DB data retrieval logic from the REST API controllers. |
| 19 | * |
| 20 | * Handles caching, data normalization, intervals-related methods, and other common functionality. |
| 21 | * So, in your custom report DataStore class that extends this class |
| 22 | * you can focus on specifics by overriding the `get_noncached_data` method. |
| 23 | * |
| 24 | * Minimalistic example: |
| 25 | * <pre><code class="language-php">class MyDataStore extends DataStore implements DataStoreInterface { |
| 26 | * /** Cache identifier, used by the `DataStore` class to handle caching for you. */ |
| 27 | * protected $cache_key = 'my_thing'; |
| 28 | * /** Data store context used to pass to filters. */ |
| 29 | * protected $context = 'my_thing'; |
| 30 | * /** Table used to get the data. */ |
| 31 | * protected static $table_name = 'my_table'; |
| 32 | * /** |
| 33 | * * Method that overrides the `DataStore::get_noncached_data()` to return the report data. |
| 34 | * * Will be called by `get_data` if there is no data in cache. |
| 35 | * */ |
| 36 | * public function get_noncached_data( $query ) { |
| 37 | * // Do your magic. |
| 38 | * |
| 39 | * // Then return your data in conforming object structure. |
| 40 | * return (object) array( |
| 41 | * 'data' => $product_data, |
| 42 | * 'total' => 1, |
| 43 | * 'page_no' => 1, |
| 44 | * 'pages' => 1, |
| 45 | * ); |
| 46 | * } |
| 47 | * } |
| 48 | * </code></pre> |
| 49 | * |
| 50 | * Please use the `woocommerce_data_stores` filter to add your custom data store to the list of available ones. |
| 51 | * Then, your store could be accessed by Controller classes ({@see GenericController::get_datastore_data() GenericController::get_datastore_data()}) |
| 52 | * or using {@link \WC_Data_Store::load() \WC_Data_Store::load()}. |
| 53 | * |
| 54 | * We recommend registering using the REST base name of your Controller as the key, e.g.: |
| 55 | * <pre><code class="language-php">add_filter( 'woocommerce_data_stores', function( $stores ) { |
| 56 | * $stores['reports/my-thing'] = 'MyExtension\Admin\Analytics\Rest_API\MyDataStore'; |
| 57 | * } ); |
| 58 | * </code></pre> |
| 59 | * This way, `GenericController` will pick it up automatically. |
| 60 | * |
| 61 | * Note that this class is NOT {@link https://developer.woocommerce.com/docs/how-to-manage-woocommerce-data-stores/ a CRUD data store}. |
| 62 | * It does not implement the {@see WC_Object_Data_Store_Interface WC_Object_Data_Store_Interface} nor extend WC_Data & WC_Data_Store_WP classes. |
| 63 | */ |
| 64 | class DataStore extends SqlQuery implements DataStoreInterface { |
| 65 | |
| 66 | /** |
| 67 | * Cache group for the reports. |
| 68 | * |
| 69 | * @var string |
| 70 | */ |
| 71 | protected $cache_group = 'reports'; |
| 72 | |
| 73 | /** |
| 74 | * Time out for the cache. |
| 75 | * |
| 76 | * @var int |
| 77 | */ |
| 78 | protected $cache_timeout = 3600; |
| 79 | |
| 80 | /** |
| 81 | * Cache identifier. |
| 82 | * |
| 83 | * @var string |
| 84 | */ |
| 85 | protected $cache_key = ''; |
| 86 | |
| 87 | /** |
| 88 | * Table used as a data store for this report. |
| 89 | * |
| 90 | * @var string |
| 91 | */ |
| 92 | protected static $table_name = ''; |
| 93 | |
| 94 | /** |
| 95 | * Date field name. |
| 96 | * |
| 97 | * @var string |
| 98 | */ |
| 99 | protected $date_column_name = 'date_created'; |
| 100 | |
| 101 | /** |
| 102 | * Allow-list a date column name before it is interpolated into SQL. |
| 103 | * |
| 104 | * @param string $column Requested date column name. |
| 105 | * @param string $fallback Column to use when the requested one is not allowed. |
| 106 | * @return string |
| 107 | */ |
| 108 | protected function sanitize_date_column_name( $column, $fallback = 'date_created' ) { |
| 109 | $allowed = array( 'date_created', 'date_created_gmt', 'date_paid', 'date_completed' ); |
| 110 | return in_array( $column, $allowed, true ) ? $column : $fallback; |
| 111 | } |
| 112 | |
| 113 | /** |
| 114 | * Mapping columns to data type to return correct response types. |
| 115 | * |
| 116 | * @var array |
| 117 | */ |
| 118 | protected $column_types = array(); |
| 119 | |
| 120 | /** |
| 121 | * SQL columns to select in the db query. |
| 122 | * |
| 123 | * @var array |
| 124 | */ |
| 125 | protected $report_columns = array(); |
| 126 | |
| 127 | // @todo This does not really belong here, maybe factor out the comparison as separate class? |
| 128 | /** |
| 129 | * Order by property, used in the cmp function. |
| 130 | * |
| 131 | * @var string |
| 132 | */ |
| 133 | private $order_by = ''; |
| 134 | |
| 135 | /** |
| 136 | * Order property, used in the cmp function. |
| 137 | * |
| 138 | * @var string |
| 139 | */ |
| 140 | private $order = ''; |
| 141 | |
| 142 | /** |
| 143 | * Query limit parameters. |
| 144 | * |
| 145 | * @var array |
| 146 | */ |
| 147 | private $limit_parameters = array(); |
| 148 | |
| 149 | /** |
| 150 | * Data store context used to pass to filters. |
| 151 | * |
| 152 | * @override SqlQuery |
| 153 | * |
| 154 | * @var string |
| 155 | */ |
| 156 | protected $context = 'reports'; |
| 157 | |
| 158 | /** |
| 159 | * Subquery object for query nesting. |
| 160 | * |
| 161 | * @var SqlQuery |
| 162 | */ |
| 163 | protected $subquery; |
| 164 | |
| 165 | /** |
| 166 | * Totals query object. |
| 167 | * |
| 168 | * @var SqlQuery |
| 169 | */ |
| 170 | protected $total_query; |
| 171 | |
| 172 | /** |
| 173 | * Intervals query object. |
| 174 | * |
| 175 | * @var SqlQuery |
| 176 | */ |
| 177 | protected $interval_query; |
| 178 | |
| 179 | /** |
| 180 | * Refresh the cache for the current query when true. |
| 181 | * |
| 182 | * @var bool |
| 183 | */ |
| 184 | protected $force_cache_refresh = false; |
| 185 | |
| 186 | /** |
| 187 | * Include debugging information in the returned data when true. |
| 188 | * |
| 189 | * @var bool |
| 190 | */ |
| 191 | protected $debug_cache = true; |
| 192 | |
| 193 | /** |
| 194 | * Debugging information to include in the returned data. |
| 195 | * |
| 196 | * @var array |
| 197 | */ |
| 198 | protected $debug_cache_data = array(); |
| 199 | |
| 200 | /** |
| 201 | * Class constructor. |
| 202 | * |
| 203 | * @override SqlQuery::__construct() |
| 204 | */ |
| 205 | public function __construct() { |
| 206 | self::set_db_table_name(); |
| 207 | $this->assign_report_columns(); |
| 208 | |
| 209 | if ( $this->report_columns ) { |
| 210 | $this->report_columns = apply_filters( |
| 211 | 'woocommerce_admin_report_columns', |
| 212 | $this->report_columns, |
| 213 | $this->context, |
| 214 | self::get_db_table_name() |
| 215 | ); |
| 216 | } |
| 217 | |
| 218 | // Utilize enveloped responses to include debugging info. |
| 219 | // See https://querymonitor.com/blog/2021/05/debugging-wordpress-rest-api-requests/ |
| 220 | if ( isset( $_GET['_envelope'] ) ) { |
| 221 | $this->debug_cache = true; |
| 222 | add_filter( 'rest_envelope_response', array( $this, 'add_debug_cache_to_envelope' ), 999, 2 ); |
| 223 | } |
| 224 | } |
| 225 | |
| 226 | |
| 227 | /** |
| 228 | * Get the data based on args. |
| 229 | * |
| 230 | * Returns the report data based on parameters supplied by the user. |
| 231 | * Fetches it from cache or returns `get_noncached_data` result. |
| 232 | * |
| 233 | * @param array $query_args Query parameters. |
| 234 | * @return stdClass|WP_Error |
| 235 | */ |
| 236 | public function get_data( $query_args ) { |
| 237 | $defaults = $this->get_default_query_vars(); |
| 238 | $query_args = wp_parse_args( $query_args, $defaults ); |
| 239 | $this->normalize_timezones( $query_args, $defaults ); |
| 240 | |
| 241 | /* |
| 242 | * We need to get the cache key here because |
| 243 | * parent::update_intervals_sql_params() modifies $query_args. |
| 244 | */ |
| 245 | $cache_key = $this->get_cache_key( $query_args ); |
| 246 | $data = $this->get_cached_data( $cache_key ); |
| 247 | |
| 248 | if ( false === $data ) { |
| 249 | $data = $this->get_noncached_data( $query_args ); |
| 250 | $this->set_cached_data( $cache_key, $data ); |
| 251 | } |
| 252 | |
| 253 | return $data; |
| 254 | } |
| 255 | |
| 256 | /** |
| 257 | * Get the default query arguments to be used by get_data(). |
| 258 | * These defaults are only partially applied when used via REST API, as that has its own defaults. |
| 259 | * |
| 260 | * @return array Query parameters. |
| 261 | */ |
| 262 | public function get_default_query_vars() { |
| 263 | return array( |
| 264 | 'per_page' => get_option( 'posts_per_page' ), |
| 265 | 'page' => 1, |
| 266 | 'order' => 'DESC', |
| 267 | 'orderby' => 'date', |
| 268 | 'before' => TimeInterval::default_before(), |
| 269 | 'after' => TimeInterval::default_after(), |
| 270 | 'fields' => '*', |
| 271 | ); |
| 272 | } |
| 273 | |
| 274 | /** |
| 275 | * Get table name from database class. |
| 276 | */ |
| 277 | public static function get_db_table_name() { |
| 278 | global $wpdb; |
| 279 | return isset( $wpdb->{static::$table_name} ) ? $wpdb->{static::$table_name} : $wpdb->prefix . static::$table_name; |
| 280 | } |
| 281 | |
| 282 | /** |
| 283 | * Returns the report data based on normalized parameters. |
| 284 | * Will be called by `get_data` if there is no data in cache. |
| 285 | * |
| 286 | * @see get_data |
| 287 | * @param array $query_args Query parameters. |
| 288 | * @return stdClass|WP_Error Data object `{ totals: *, intervals: array, total: int, pages: int, page_no: int }`, or error. |
| 289 | */ |
| 290 | public function get_noncached_data( $query_args ) { |
| 291 | /* translators: %s: Method name */ |
| 292 | return new \WP_Error( 'invalid-method', sprintf( __( "Method '%s' not implemented. Must be overridden in subclass.", 'woocommerce' ), __METHOD__ ), array( 'status' => 405 ) ); |
| 293 | } |
| 294 | |
| 295 | /** |
| 296 | * Set table name from database class. |
| 297 | */ |
| 298 | protected static function set_db_table_name() { |
| 299 | global $wpdb; |
| 300 | if ( static::$table_name && ! isset( $wpdb->{static::$table_name} ) ) { |
| 301 | $wpdb->{static::$table_name} = $wpdb->prefix . static::$table_name; |
| 302 | } |
| 303 | } |
| 304 | |
| 305 | /** |
| 306 | * Batch-prime post + meta caches for a list of IDs, plus their `_thumbnail_id` attachments. |
| 307 | * |
| 308 | * @param array $ids Post IDs to prime. |
| 309 | * @return void |
| 310 | */ |
| 311 | protected static function prime_object_caches( array $ids ): void { |
| 312 | $ids = array_unique( array_filter( array_map( 'intval', $ids ) ) ); |
| 313 | if ( empty( $ids ) ) { |
| 314 | return; |
| 315 | } |
| 316 | _prime_post_caches( $ids ); |
| 317 | |
| 318 | $image_ids = array_filter( |
| 319 | array_map( static fn( $id ) => (int) get_post_meta( $id, '_thumbnail_id', true ), $ids ) |
| 320 | ); |
| 321 | if ( ! empty( $image_ids ) ) { |
| 322 | _prime_post_caches( array_unique( $image_ids ) ); |
| 323 | } |
| 324 | } |
| 325 | |
| 326 | /** |
| 327 | * Whether or not the report should use the caching layer. |
| 328 | * |
| 329 | * Provides an opportunity for plugins to prevent reports from using cache. |
| 330 | * |
| 331 | * @return boolean Whether or not to utilize caching. |
| 332 | */ |
| 333 | protected function should_use_cache() { |
| 334 | /** |
| 335 | * Determines if a report will utilize caching. |
| 336 | * |
| 337 | * @param bool $use_cache Whether or not to use cache. |
| 338 | * @param string $cache_key The report's cache key. Used to identify the report. |
| 339 | */ |
| 340 | return (bool) apply_filters( 'woocommerce_analytics_report_should_use_cache', true, $this->cache_key ); |
| 341 | } |
| 342 | |
| 343 | /** |
| 344 | * Returns string to be used as cache key for the data. |
| 345 | * |
| 346 | * @param array $params Query parameters. |
| 347 | * @return string |
| 348 | */ |
| 349 | protected function get_cache_key( $params ) { |
| 350 | if ( isset( $params['force_cache_refresh'] ) ) { |
| 351 | if ( true === $params['force_cache_refresh'] ) { |
| 352 | $this->force_cache_refresh = true; |
| 353 | } |
| 354 | |
| 355 | // We don't want this param in the key. |
| 356 | unset( $params['force_cache_refresh'] ); |
| 357 | } |
| 358 | |
| 359 | if ( true === $this->debug_cache ) { |
| 360 | $this->debug_cache_data['query_args'] = $params; |
| 361 | } |
| 362 | |
| 363 | // Normalize the $params to reduce cache misses. |
| 364 | $params = array_filter( |
| 365 | $params, |
| 366 | function ( $param ) { |
| 367 | return ! empty( $param ); |
| 368 | } |
| 369 | ); |
| 370 | |
| 371 | // Normalize DateTime objects to ISO 8601 strings to avoid cache key |
| 372 | // instability caused by microsecond-level differences in serialization. |
| 373 | array_walk( |
| 374 | $params, |
| 375 | function ( &$value ) { |
| 376 | if ( $value instanceof \DateTimeInterface ) { |
| 377 | $value = $value->format( DATE_ATOM ); |
| 378 | } |
| 379 | } |
| 380 | ); |
| 381 | |
| 382 | ksort( $params ); |
| 383 | return implode( |
| 384 | '_', |
| 385 | array( |
| 386 | 'wc_report', |
| 387 | $this->cache_key, |
| 388 | md5( wp_json_encode( $params ) ), |
| 389 | ) |
| 390 | ); |
| 391 | } |
| 392 | |
| 393 | /** |
| 394 | * Wrapper around Cache::get(). |
| 395 | * |
| 396 | * @param string $cache_key Cache key. |
| 397 | * @return mixed |
| 398 | */ |
| 399 | protected function get_cached_data( $cache_key ) { |
| 400 | if ( true === $this->debug_cache ) { |
| 401 | $this->debug_cache_data['should_use_cache'] = $this->should_use_cache(); |
| 402 | $this->debug_cache_data['force_cache_refresh'] = $this->force_cache_refresh; |
| 403 | $this->debug_cache_data['cache_hit'] = false; |
| 404 | } |
| 405 | |
| 406 | if ( $this->should_use_cache() && false === $this->force_cache_refresh ) { |
| 407 | $cached_data = Cache::get( $cache_key ); |
| 408 | |
| 409 | $cache_hit = false !== $cached_data; |
| 410 | if ( true === $this->debug_cache ) { |
| 411 | $this->debug_cache_data['cache_hit'] = $cache_hit; |
| 412 | } |
| 413 | |
| 414 | return $cached_data; |
| 415 | } |
| 416 | |
| 417 | // Cached item has now functionally been refreshed. Reset the option. |
| 418 | $this->force_cache_refresh = false; |
| 419 | |
| 420 | return false; |
| 421 | } |
| 422 | |
| 423 | /** |
| 424 | * Wrapper around Cache::set(). |
| 425 | * |
| 426 | * @param string $cache_key Cache key. |
| 427 | * @param mixed $value New value. |
| 428 | * @return bool |
| 429 | */ |
| 430 | protected function set_cached_data( $cache_key, $value ) { |
| 431 | if ( $this->should_use_cache() ) { |
| 432 | return Cache::set( $cache_key, $value ); |
| 433 | } |
| 434 | |
| 435 | return true; |
| 436 | } |
| 437 | |
| 438 | /** |
| 439 | * Add cache debugging information to an enveloped API response. |
| 440 | * |
| 441 | * @param array $envelope |
| 442 | * @param \WP_REST_Response $response |
| 443 | * |
| 444 | * @return array |
| 445 | */ |
| 446 | public function add_debug_cache_to_envelope( $envelope, $response ) { |
| 447 | if ( 0 !== strncmp( '/wc-analytics', $response->get_matched_route(), 13 ) ) { |
| 448 | return $envelope; |
| 449 | } |
| 450 | |
| 451 | if ( ! empty( $this->debug_cache_data ) ) { |
| 452 | $envelope['debug_cache'] = $this->debug_cache_data; |
| 453 | } |
| 454 | |
| 455 | return $envelope; |
| 456 | } |
| 457 | |
| 458 | /** |
| 459 | * Compares two report data objects by pre-defined object property and ASC/DESC ordering. |
| 460 | * |
| 461 | * @param stdClass $a Object a. |
| 462 | * @param stdClass $b Object b. |
| 463 | * @return string |
| 464 | */ |
| 465 | private function interval_cmp( $a, $b ) { |
| 466 | if ( '' === $this->order_by || '' === $this->order ) { |
| 467 | return 0; |
| 468 | // @todo Should return WP_Error here perhaps? |
| 469 | } |
| 470 | if ( $a[ $this->order_by ] === $b[ $this->order_by ] ) { |
| 471 | // As relative order is undefined in case of equality in usort, second-level sorting by date needs to be enforced |
| 472 | // so that paging is stable. |
| 473 | if ( $a['time_interval'] === $b['time_interval'] ) { |
| 474 | return 0; // This should never happen. |
| 475 | } elseif ( $a['time_interval'] > $b['time_interval'] ) { |
| 476 | return 1; |
| 477 | } elseif ( $a['time_interval'] < $b['time_interval'] ) { |
| 478 | return -1; |
| 479 | } |
| 480 | } elseif ( $a[ $this->order_by ] > $b[ $this->order_by ] ) { |
| 481 | return strtolower( $this->order ) === 'desc' ? -1 : 1; |
| 482 | } elseif ( $a[ $this->order_by ] < $b[ $this->order_by ] ) { |
| 483 | return strtolower( $this->order ) === 'desc' ? 1 : -1; |
| 484 | } |
| 485 | } |
| 486 | |
| 487 | /** |
| 488 | * Sorts intervals according to user's request. |
| 489 | * |
| 490 | * They are pre-sorted in SQL, but after adding gaps, they need to be sorted including the added ones. |
| 491 | * |
| 492 | * @param stdClass $data Data object, must contain an array under $data->intervals. |
| 493 | * @param string $sort_by Ordering property. |
| 494 | * @param string $direction DESC/ASC. |
| 495 | */ |
| 496 | protected function sort_intervals( &$data, $sort_by, $direction ) { |
| 497 | $this->sort_array( $data->intervals, $sort_by, $direction ); |
| 498 | } |
| 499 | |
| 500 | /** |
| 501 | * Sorts array of arrays based on subarray key $sort_by. |
| 502 | * |
| 503 | * @param array $arr Array to sort. |
| 504 | * @param string $sort_by Ordering property. |
| 505 | * @param string $direction DESC/ASC. |
| 506 | */ |
| 507 | protected function sort_array( &$arr, $sort_by, $direction ) { |
| 508 | $this->order_by = $this->normalize_order_by( $sort_by ); |
| 509 | $this->order = $direction; |
| 510 | usort( $arr, array( $this, 'interval_cmp' ) ); |
| 511 | } |
| 512 | |
| 513 | /** |
| 514 | * Fills in interval gaps from DB with 0-filled objects. |
| 515 | * |
| 516 | * @param array $db_intervals Array of all intervals present in the db. |
| 517 | * @param DateTime $start_datetime Start date. |
| 518 | * @param DateTime $end_datetime End date. |
| 519 | * @param string $time_interval Time interval, e.g. day, week, month. |
| 520 | * @param stdClass $data Data with SQL extracted intervals. |
| 521 | * @return stdClass |
| 522 | */ |
| 523 | protected function fill_in_missing_intervals( $db_intervals, $start_datetime, $end_datetime, $time_interval, &$data ) { |
| 524 | // @todo This is ugly and messy. |
| 525 | $local_tz = new \DateTimeZone( wc_timezone_string() ); |
| 526 | // At this point, we don't know when we can stop iterating, as the ordering can be based on any value. |
| 527 | $time_ids = array_flip( wp_list_pluck( $data->intervals, 'time_interval' ) ); |
| 528 | $db_intervals = array_flip( $db_intervals ); |
| 529 | // Totals object used to get all needed properties. |
| 530 | $totals_arr = get_object_vars( $data->totals ); |
| 531 | foreach ( $totals_arr as $key => $val ) { |
| 532 | $totals_arr[ $key ] = 0; |
| 533 | } |
| 534 | // @todo Should 'products' be in intervals? |
| 535 | unset( $totals_arr['products'] ); |
| 536 | while ( $start_datetime <= $end_datetime ) { |
| 537 | $next_start = TimeInterval::iterate( $start_datetime, $time_interval ); |
| 538 | $time_id = TimeInterval::time_interval_id( $time_interval, $start_datetime ); |
| 539 | // Either create fill-zero interval or use data from db. |
| 540 | if ( $next_start > $end_datetime ) { |
| 541 | $interval_end = $end_datetime->format( 'Y-m-d H:i:s' ); |
| 542 | } else { |
| 543 | $prev_end_timestamp = (int) $next_start->format( 'U' ) - 1; |
| 544 | $prev_end = new \DateTime(); |
| 545 | $prev_end->setTimestamp( $prev_end_timestamp ); |
| 546 | $prev_end->setTimezone( $local_tz ); |
| 547 | $interval_end = $prev_end->format( 'Y-m-d H:i:s' ); |
| 548 | } |
| 549 | if ( array_key_exists( $time_id, $time_ids ) ) { |
| 550 | // For interval present in the db for this time frame, just fill in dates. |
| 551 | $record = &$data->intervals[ $time_ids[ $time_id ] ]; |
| 552 | $record['date_start'] = $start_datetime->format( 'Y-m-d H:i:s' ); |
| 553 | $record['date_end'] = $interval_end; |
| 554 | } elseif ( ! array_key_exists( $time_id, $db_intervals ) ) { |
| 555 | // For intervals present in the db outside of this time frame, do nothing. |
| 556 | // For intervals not present in the db, fabricate it. |
| 557 | $record_arr = array(); |
| 558 | $record_arr['time_interval'] = $time_id; |
| 559 | $record_arr['date_start'] = $start_datetime->format( 'Y-m-d H:i:s' ); |
| 560 | $record_arr['date_end'] = $interval_end; |
| 561 | $data->intervals[] = array_merge( $record_arr, $totals_arr ); |
| 562 | } |
| 563 | $start_datetime = $next_start; |
| 564 | } |
| 565 | return $data; |
| 566 | } |
| 567 | |
| 568 | /** |
| 569 | * Converts input datetime parameters to local timezone. If there are no inputs from the user in query_args, |
| 570 | * uses default from $defaults. |
| 571 | * |
| 572 | * @param array $query_args Array of query arguments. |
| 573 | * @param array $defaults Array of default values. |
| 574 | */ |
| 575 | protected function normalize_timezones( &$query_args, $defaults ) { |
| 576 | $local_tz = new \DateTimeZone( wc_timezone_string() ); |
| 577 | foreach ( array( 'before', 'after' ) as $query_arg_key ) { |
| 578 | if ( isset( $query_args[ $query_arg_key ] ) && is_string( $query_args[ $query_arg_key ] ) ) { |
| 579 | // Assume that unspecified timezone is a local timezone. |
| 580 | $datetime = new \DateTime( $query_args[ $query_arg_key ], $local_tz ); |
| 581 | // In case timezone was forced by using +HH:MM, convert to local timezone. |
| 582 | $datetime->setTimezone( $local_tz ); |
| 583 | $query_args[ $query_arg_key ] = $datetime; |
| 584 | } elseif ( isset( $query_args[ $query_arg_key ] ) && is_a( $query_args[ $query_arg_key ], 'DateTime' ) ) { |
| 585 | // In case timezone is in other timezone, convert to local timezone. |
| 586 | $query_args[ $query_arg_key ]->setTimezone( $local_tz ); |
| 587 | } else { |
| 588 | $query_args[ $query_arg_key ] = isset( $defaults[ $query_arg_key ] ) ? $defaults[ $query_arg_key ] : null; |
| 589 | } |
| 590 | } |
| 591 | } |
| 592 | |
| 593 | /** |
| 594 | * Removes extra records from intervals so that only requested number of records get returned. |
| 595 | * |
| 596 | * @param stdClass $data Data from whose intervals the records get removed. |
| 597 | * @param int $page_no Offset requested by the user. |
| 598 | * @param int $items_per_page Number of records requested by the user. |
| 599 | * @param int $db_interval_count Database interval count. |
| 600 | * @param int $expected_interval_count Expected interval count on the output. |
| 601 | * @param string $order_by Order by field. |
| 602 | * @param string $order ASC or DESC. |
| 603 | */ |
| 604 | protected function remove_extra_records( &$data, $page_no, $items_per_page, $db_interval_count, $expected_interval_count, $order_by, $order ) { |
| 605 | if ( 'date' === strtolower( $order_by ) ) { |
| 606 | $offset = 0; |
| 607 | } else { |
| 608 | if ( 'asc' === strtolower( $order ) ) { |
| 609 | $offset = ( $page_no - 1 ) * $items_per_page; |
| 610 | } else { |
| 611 | $offset = ( $page_no - 1 ) * $items_per_page - $db_interval_count; |
| 612 | } |
| 613 | $offset = $offset < 0 ? 0 : $offset; |
| 614 | } |
| 615 | $count = $expected_interval_count - ( $page_no - 1 ) * $items_per_page; |
| 616 | if ( $count < 0 ) { |
| 617 | $count = 0; |
| 618 | } elseif ( $count > $items_per_page ) { |
| 619 | $count = $items_per_page; |
| 620 | } |
| 621 | $data->intervals = array_slice( $data->intervals, $offset, $count ); |
| 622 | } |
| 623 | |
| 624 | /** |
| 625 | * Returns expected number of items on the page in case of date ordering. |
| 626 | * |
| 627 | * @param int $expected_interval_count Expected number of intervals in total. |
| 628 | * @param int $items_per_page Number of items per page. |
| 629 | * @param int $page_no Page number. |
| 630 | * |
| 631 | * @return float|int |
| 632 | */ |
| 633 | protected function expected_intervals_on_page( $expected_interval_count, $items_per_page, $page_no ) { |
| 634 | $total_pages = (int) ceil( $expected_interval_count / $items_per_page ); |
| 635 | if ( $page_no < $total_pages ) { |
| 636 | return $items_per_page; |
| 637 | } elseif ( $page_no === $total_pages ) { |
| 638 | return $expected_interval_count - ( $page_no - 1 ) * $items_per_page; |
| 639 | } else { |
| 640 | return 0; |
| 641 | } |
| 642 | } |
| 643 | |
| 644 | /** |
| 645 | * Returns true if there are any intervals that need to be filled in the response. |
| 646 | * |
| 647 | * @param int $expected_interval_count Expected number of intervals in total. |
| 648 | * @param int $db_records Total number of records for given period in the database. |
| 649 | * @param int $items_per_page Number of items per page. |
| 650 | * @param int $page_no Page number. |
| 651 | * @param string $order asc or desc. |
| 652 | * @param string $order_by Column by which the result will be sorted. |
| 653 | * @param int $intervals_count Number of records for given (possibly shortened) time interval. |
| 654 | * |
| 655 | * @return bool |
| 656 | */ |
| 657 | protected function intervals_missing( $expected_interval_count, $db_records, $items_per_page, $page_no, $order, $order_by, $intervals_count ) { |
| 658 | if ( $expected_interval_count <= $db_records ) { |
| 659 | return false; |
| 660 | } |
| 661 | if ( 'date' === $order_by ) { |
| 662 | $expected_intervals_on_page = $this->expected_intervals_on_page( $expected_interval_count, $items_per_page, $page_no ); |
| 663 | return $intervals_count < $expected_intervals_on_page; |
| 664 | } |
| 665 | if ( 'desc' === $order ) { |
| 666 | return $page_no > floor( $db_records / $items_per_page ); |
| 667 | } |
| 668 | if ( 'asc' === $order ) { |
| 669 | return $page_no <= ceil( ( $expected_interval_count - $db_records ) / $items_per_page ); |
| 670 | } |
| 671 | // Invalid ordering. |
| 672 | return false; |
| 673 | } |
| 674 | |
| 675 | /** |
| 676 | * Updates the LIMIT query part for Intervals query of the report. |
| 677 | * |
| 678 | * If there are less records in the database than time intervals, then we need to remap offset in SQL query |
| 679 | * to fetch correct records. |
| 680 | * |
| 681 | * @param array $query_args Query arguments. |
| 682 | * @param int $db_interval_count Database interval count. |
| 683 | * @param int $expected_interval_count Expected interval count on the output. |
| 684 | * @param string $table_name Name of the db table relevant for the date constraint. |
| 685 | */ |
| 686 | protected function update_intervals_sql_params( &$query_args, $db_interval_count, $expected_interval_count, $table_name ) { |
| 687 | if ( $db_interval_count === $expected_interval_count ) { |
| 688 | return; |
| 689 | } |
| 690 | |
| 691 | $params = $this->get_limit_params( $query_args ); |
| 692 | $local_tz = new \DateTimeZone( wc_timezone_string() ); |
| 693 | if ( 'date' === strtolower( $query_args['orderby'] ) ) { |
| 694 | // page X in request translates to slightly different dates in the db, in case some |
| 695 | // records are missing from the db. |
| 696 | $start_iteration = 0; |
| 697 | $end_iteration = 0; |
| 698 | if ( 'asc' === strtolower( $query_args['order'] ) ) { |
| 699 | // ORDER BY date ASC. |
| 700 | $new_start_date = $query_args['after']; |
| 701 | $intervals_to_skip = ( $query_args['page'] - 1 ) * $params['per_page']; |
| 702 | $latest_end_date = $query_args['before']; |
| 703 | for ( $i = 0; $i < $intervals_to_skip; $i++ ) { |
| 704 | if ( $new_start_date > $latest_end_date ) { |
| 705 | $new_start_date = $latest_end_date; |
| 706 | $start_iteration = 0; |
| 707 | break; |
| 708 | } |
| 709 | $new_start_date = TimeInterval::iterate( $new_start_date, $query_args['interval'] ); |
| 710 | $start_iteration ++; |
| 711 | } |
| 712 | |
| 713 | $new_end_date = clone $new_start_date; |
| 714 | for ( $i = 0; $i < $params['per_page']; $i++ ) { |
| 715 | if ( $new_end_date > $latest_end_date ) { |
| 716 | break; |
| 717 | } |
| 718 | $new_end_date = TimeInterval::iterate( $new_end_date, $query_args['interval'] ); |
| 719 | $end_iteration ++; |
| 720 | } |
| 721 | if ( $new_end_date > $latest_end_date ) { |
| 722 | $new_end_date = $latest_end_date; |
| 723 | $end_iteration = 0; |
| 724 | } |
| 725 | if ( $end_iteration ) { |
| 726 | $new_end_date_timestamp = (int) $new_end_date->format( 'U' ) - 1; |
| 727 | $new_end_date->setTimestamp( $new_end_date_timestamp ); |
| 728 | } |
| 729 | } else { |
| 730 | // ORDER BY date DESC. |
| 731 | $new_end_date = $query_args['before']; |
| 732 | $intervals_to_skip = ( $query_args['page'] - 1 ) * $params['per_page']; |
| 733 | $earliest_start_date = $query_args['after']; |
| 734 | for ( $i = 0; $i < $intervals_to_skip; $i++ ) { |
| 735 | if ( $new_end_date < $earliest_start_date ) { |
| 736 | $new_end_date = $earliest_start_date; |
| 737 | $end_iteration = 0; |
| 738 | break; |
| 739 | } |
| 740 | $new_end_date = TimeInterval::iterate( $new_end_date, $query_args['interval'], true ); |
| 741 | $end_iteration ++; |
| 742 | } |
| 743 | |
| 744 | $new_start_date = clone $new_end_date; |
| 745 | for ( $i = 0; $i < $params['per_page']; $i++ ) { |
| 746 | if ( $new_start_date < $earliest_start_date ) { |
| 747 | break; |
| 748 | } |
| 749 | $new_start_date = TimeInterval::iterate( $new_start_date, $query_args['interval'], true ); |
| 750 | $start_iteration ++; |
| 751 | } |
| 752 | if ( $new_start_date < $earliest_start_date ) { |
| 753 | $new_start_date = $earliest_start_date; |
| 754 | $start_iteration = 0; |
| 755 | } |
| 756 | if ( $start_iteration ) { |
| 757 | // @todo Is this correct? should it only be added if iterate runs? other two iterate instances, too? |
| 758 | $new_start_date_timestamp = (int) $new_start_date->format( 'U' ) + 1; |
| 759 | $new_start_date->setTimestamp( $new_start_date_timestamp ); |
| 760 | } |
| 761 | } |
| 762 | // @todo - Do this without modifying $query_args? |
| 763 | $query_args['adj_after'] = $new_start_date; |
| 764 | $query_args['adj_before'] = $new_end_date; |
| 765 | $adj_after = $new_start_date->format( TimeInterval::$sql_datetime_format ); |
| 766 | $adj_before = $new_end_date->format( TimeInterval::$sql_datetime_format ); |
| 767 | $this->interval_query->clear_sql_clause( array( 'where_time', 'limit' ) ); |
| 768 | $this->interval_query->add_sql_clause( 'where_time', "AND {$table_name}.`{$this->date_column_name}` <= '$adj_before'" ); |
| 769 | $this->interval_query->add_sql_clause( 'where_time', "AND {$table_name}.`{$this->date_column_name}` >= '$adj_after'" ); |
| 770 | $this->clear_sql_clause( 'limit' ); |
| 771 | $this->add_sql_clause( 'limit', 'LIMIT 0,' . $params['per_page'] ); |
| 772 | } else { |
| 773 | if ( 'asc' === $query_args['order'] ) { |
| 774 | $offset = ( ( $query_args['page'] - 1 ) * $params['per_page'] ) - ( $expected_interval_count - $db_interval_count ); |
| 775 | $offset = $offset < 0 ? 0 : $offset; |
| 776 | $count = $query_args['page'] * $params['per_page'] - ( $expected_interval_count - $db_interval_count ); |
| 777 | if ( $count < 0 ) { |
| 778 | $count = 0; |
| 779 | } elseif ( $count > $params['per_page'] ) { |
| 780 | $count = $params['per_page']; |
| 781 | } |
| 782 | |
| 783 | $this->clear_sql_clause( 'limit' ); |
| 784 | $this->add_sql_clause( 'limit', 'LIMIT ' . $offset . ',' . $count ); |
| 785 | } |
| 786 | // Otherwise no change in limit clause. |
| 787 | // @todo - Do this without modifying $query_args? |
| 788 | $query_args['adj_after'] = $query_args['after']; |
| 789 | $query_args['adj_before'] = $query_args['before']; |
| 790 | } |
| 791 | } |
| 792 | |
| 793 | /** |
| 794 | * Casts strings returned from the database to appropriate data types for output. |
| 795 | * |
| 796 | * @param array $array Associative array of values extracted from the database. |
| 797 | * @return array|WP_Error |
| 798 | */ |
| 799 | protected function cast_numbers( $array ) { |
| 800 | $retyped_array = array(); |
| 801 | $column_types = apply_filters( 'woocommerce_rest_reports_column_types', $this->column_types, $array ); |
| 802 | foreach ( $array as $column_name => $value ) { |
| 803 | if ( is_array( $value ) ) { |
| 804 | $value = $this->cast_numbers( $value ); |
| 805 | } |
| 806 | |
| 807 | if ( isset( $column_types[ $column_name ] ) ) { |
| 808 | $retyped_array[ $column_name ] = $column_types[ $column_name ]( $value ); |
| 809 | } else { |
| 810 | $retyped_array[ $column_name ] = $value; |
| 811 | } |
| 812 | } |
| 813 | return $retyped_array; |
| 814 | } |
| 815 | |
| 816 | /** |
| 817 | * Returns a list of columns selected by the query_args formatted as a comma separated string. |
| 818 | * |
| 819 | * @param array $query_args User-supplied options. |
| 820 | * @return string |
| 821 | */ |
| 822 | protected function selected_columns( $query_args ) { |
| 823 | $selections = $this->report_columns; |
| 824 | |
| 825 | if ( isset( $query_args['fields'] ) && is_array( $query_args['fields'] ) ) { |
| 826 | $keep = array(); |
| 827 | foreach ( $query_args['fields'] as $field ) { |
| 828 | if ( isset( $selections[ $field ] ) ) { |
| 829 | $keep[ $field ] = $selections[ $field ]; |
| 830 | } |
| 831 | } |
| 832 | $selections = implode( ', ', $keep ); |
| 833 | } else { |
| 834 | $selections = implode( ', ', $selections ); |
| 835 | } |
| 836 | return $selections; |
| 837 | } |
| 838 | |
| 839 | /** |
| 840 | * Get the excluded order statuses used when calculating reports. |
| 841 | * |
| 842 | * @return array |
| 843 | */ |
| 844 | protected static function get_excluded_report_order_statuses() { |
| 845 | $excluded_statuses = \WC_Admin_Settings::get_option( 'woocommerce_excluded_report_order_statuses', array( 'pending', 'failed', 'cancelled' ) ); |
| 846 | $excluded_statuses = array_merge( array( 'auto-draft', 'trash' ), array_map( 'esc_sql', $excluded_statuses ) ); |
| 847 | return apply_filters( 'woocommerce_analytics_excluded_order_statuses', $excluded_statuses ); |
| 848 | } |
| 849 | |
| 850 | /** |
| 851 | * Maps order status provided by the user to the one used in the database. |
| 852 | * |
| 853 | * @param string $status Order status. |
| 854 | * @return string |
| 855 | */ |
| 856 | protected static function normalize_order_status( $status ) { |
| 857 | $status = trim( $status ); |
| 858 | return 'wc-' . $status; |
| 859 | } |
| 860 | |
| 861 | /** |
| 862 | * Normalizes order_by clause to match to SQL query. |
| 863 | * |
| 864 | * @param string $order_by Order by option requested by user. |
| 865 | * @return string |
| 866 | */ |
| 867 | protected function normalize_order_by( $order_by ) { |
| 868 | if ( 'date' === $order_by ) { |
| 869 | return 'time_interval'; |
| 870 | } |
| 871 | |
| 872 | return $order_by; |
| 873 | } |
| 874 | |
| 875 | /** |
| 876 | * Updates start and end dates for intervals so that they represent intervals' borders, not times when data in db were recorded. |
| 877 | * |
| 878 | * E.g. if there are db records for only Tuesday and Thursday this week, the actual week interval is [Mon, Sun], not [Tue, Thu]. |
| 879 | * |
| 880 | * @param DateTime $start_datetime Start date. |
| 881 | * @param DateTime $end_datetime End date. |
| 882 | * @param string $time_interval Time interval, e.g. day, week, month. |
| 883 | * @param array $intervals Array of intervals extracted from SQL db. |
| 884 | */ |
| 885 | protected function update_interval_boundary_dates( $start_datetime, $end_datetime, $time_interval, &$intervals ) { |
| 886 | $local_tz = new \DateTimeZone( wc_timezone_string() ); |
| 887 | foreach ( $intervals as $key => $interval ) { |
| 888 | $datetime = new \DateTime( $interval['datetime_anchor'], $local_tz ); |
| 889 | |
| 890 | $prev_start = TimeInterval::iterate( $datetime, $time_interval, true ); |
| 891 | // @todo Not sure if the +1/-1 here are correct, especially as they are applied before the ?: below. |
| 892 | $prev_start_timestamp = (int) $prev_start->format( 'U' ) + 1; |
| 893 | $prev_start->setTimestamp( $prev_start_timestamp ); |
| 894 | if ( $start_datetime ) { |
| 895 | $date_start = $prev_start < $start_datetime ? $start_datetime : $prev_start; |
| 896 | $intervals[ $key ]['date_start'] = $date_start->format( 'Y-m-d H:i:s' ); |
| 897 | } else { |
| 898 | $intervals[ $key ]['date_start'] = $prev_start->format( 'Y-m-d H:i:s' ); |
| 899 | } |
| 900 | |
| 901 | $next_end = TimeInterval::iterate( $datetime, $time_interval ); |
| 902 | $next_end_timestamp = (int) $next_end->format( 'U' ) - 1; |
| 903 | $next_end->setTimestamp( $next_end_timestamp ); |
| 904 | if ( $end_datetime ) { |
| 905 | $date_end = $next_end > $end_datetime ? $end_datetime : $next_end; |
| 906 | $intervals[ $key ]['date_end'] = $date_end->format( 'Y-m-d H:i:s' ); |
| 907 | } else { |
| 908 | $intervals[ $key ]['date_end'] = $next_end->format( 'Y-m-d H:i:s' ); |
| 909 | } |
| 910 | |
| 911 | $intervals[ $key ]['interval'] = $time_interval; |
| 912 | } |
| 913 | } |
| 914 | |
| 915 | /** |
| 916 | * Change structure of intervals to form a correct response. |
| 917 | * |
| 918 | * Also converts local datetimes to GMT and adds them to the intervals. |
| 919 | * |
| 920 | * @param array $intervals Time interval, e.g. day, week, month. |
| 921 | */ |
| 922 | protected function create_interval_subtotals( &$intervals ) { |
| 923 | foreach ( $intervals as $key => $interval ) { |
| 924 | $start_gmt = TimeInterval::convert_local_datetime_to_gmt( $interval['date_start'] ); |
| 925 | $end_gmt = TimeInterval::convert_local_datetime_to_gmt( $interval['date_end'] ); |
| 926 | // Move intervals result to subtotals object. |
| 927 | $intervals[ $key ] = array( |
| 928 | 'interval' => $interval['time_interval'], |
| 929 | 'date_start' => $interval['date_start'], |
| 930 | 'date_start_gmt' => $start_gmt->format( TimeInterval::$sql_datetime_format ), |
| 931 | 'date_end' => $interval['date_end'], |
| 932 | 'date_end_gmt' => $end_gmt->format( TimeInterval::$sql_datetime_format ), |
| 933 | ); |
| 934 | |
| 935 | unset( $interval['interval'] ); |
| 936 | unset( $interval['date_start'] ); |
| 937 | unset( $interval['date_end'] ); |
| 938 | unset( $interval['datetime_anchor'] ); |
| 939 | unset( $interval['time_interval'] ); |
| 940 | $intervals[ $key ]['subtotals'] = (object) $this->cast_numbers( $interval ); |
| 941 | } |
| 942 | } |
| 943 | |
| 944 | /** |
| 945 | * Fills WHERE clause of SQL request with date-related constraints. |
| 946 | * |
| 947 | * @param array $query_args Parameters supplied by the user. |
| 948 | * @param string $table_name Name of the db table relevant for the date constraint. |
| 949 | */ |
| 950 | protected function add_time_period_sql_params( $query_args, $table_name ) { |
| 951 | $this->clear_sql_clause( array( 'from', 'where_time', 'where' ) ); |
| 952 | if ( isset( $this->subquery ) ) { |
| 953 | $this->subquery->clear_sql_clause( 'where_time' ); |
| 954 | } |
| 955 | |
| 956 | if ( isset( $query_args['before'] ) && '' !== $query_args['before'] ) { |
| 957 | if ( is_a( $query_args['before'], 'WC_DateTime' ) ) { |
| 958 | $datetime_str = $query_args['before']->date( TimeInterval::$sql_datetime_format ); |
| 959 | } else { |
| 960 | $datetime_str = $query_args['before']->format( TimeInterval::$sql_datetime_format ); |
| 961 | } |
| 962 | if ( isset( $this->subquery ) ) { |
| 963 | $this->subquery->add_sql_clause( 'where_time', "AND {$table_name}.`{$this->date_column_name}` <= '$datetime_str'" ); |
| 964 | } else { |
| 965 | $this->add_sql_clause( 'where_time', "AND {$table_name}.`{$this->date_column_name}` <= '$datetime_str'" ); |
| 966 | } |
| 967 | } |
| 968 | |
| 969 | if ( isset( $query_args['after'] ) && '' !== $query_args['after'] ) { |
| 970 | if ( is_a( $query_args['after'], 'WC_DateTime' ) ) { |
| 971 | $datetime_str = $query_args['after']->date( TimeInterval::$sql_datetime_format ); |
| 972 | } else { |
| 973 | $datetime_str = $query_args['after']->format( TimeInterval::$sql_datetime_format ); |
| 974 | } |
| 975 | if ( isset( $this->subquery ) ) { |
| 976 | $this->subquery->add_sql_clause( 'where_time', "AND {$table_name}.`{$this->date_column_name}` >= '$datetime_str'" ); |
| 977 | } else { |
| 978 | $this->add_sql_clause( 'where_time', "AND {$table_name}.`{$this->date_column_name}` >= '$datetime_str'" ); |
| 979 | } |
| 980 | } |
| 981 | } |
| 982 | |
| 983 | /** |
| 984 | * Fills LIMIT clause of SQL request based on user supplied parameters. |
| 985 | * |
| 986 | * @param array $query_args Parameters supplied by the user. |
| 987 | * @return array |
| 988 | */ |
| 989 | protected function get_limit_sql_params( $query_args ) { |
| 990 | global $wpdb; |
| 991 | $params = $this->get_limit_params( $query_args ); |
| 992 | |
| 993 | $this->clear_sql_clause( 'limit' ); |
| 994 | $this->add_sql_clause( 'limit', $wpdb->prepare( 'LIMIT %d, %d', $params['offset'], $params['per_page'] ) ); |
| 995 | return $params; |
| 996 | } |
| 997 | |
| 998 | /** |
| 999 | * Fills LIMIT parameters of SQL request based on user supplied parameters. |
| 1000 | * |
| 1001 | * @param array $query_args Parameters supplied by the user. |
| 1002 | * @return array |
| 1003 | */ |
| 1004 | protected function get_limit_params( $query_args = array() ) { |
| 1005 | if ( isset( $query_args['per_page'] ) && is_numeric( $query_args['per_page'] ) ) { |
| 1006 | $this->limit_parameters['per_page'] = (int) $query_args['per_page']; |
| 1007 | } else { |
| 1008 | $this->limit_parameters['per_page'] = get_option( 'posts_per_page' ); |
| 1009 | } |
| 1010 | |
| 1011 | $this->limit_parameters['offset'] = 0; |
| 1012 | if ( isset( $query_args['page'] ) ) { |
| 1013 | $this->limit_parameters['offset'] = ( (int) $query_args['page'] - 1 ) * $this->limit_parameters['per_page']; |
| 1014 | } |
| 1015 | |
| 1016 | return $this->limit_parameters; |
| 1017 | } |
| 1018 | |
| 1019 | /** |
| 1020 | * Generates a virtual table given a list of IDs. |
| 1021 | * |
| 1022 | * @param array $ids Array of IDs. |
| 1023 | * @param array $id_field Name of the ID field. |
| 1024 | * @param array $other_values Other values that must be contained in the virtual table. |
| 1025 | * @return array |
| 1026 | */ |
| 1027 | protected function get_ids_table( $ids, $id_field, $other_values = array() ) { |
| 1028 | global $wpdb; |
| 1029 | $selects = array(); |
| 1030 | foreach ( $ids as $id ) { |
| 1031 | // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 1032 | $new_select = $wpdb->prepare( "SELECT %s AS {$id_field}", $id ); |
| 1033 | foreach ( $other_values as $key => $value ) { |
| 1034 | $new_select .= $wpdb->prepare( ", %s AS {$key}", $value ); |
| 1035 | } |
| 1036 | // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 1037 | array_push( $selects, $new_select ); |
| 1038 | } |
| 1039 | return join( ' UNION ', $selects ); |
| 1040 | } |
| 1041 | |
| 1042 | /** |
| 1043 | * Returns a comma separated list of the fields in the `query_args`, if there aren't, returns `report_columns` keys. |
| 1044 | * |
| 1045 | * @param array $query_args Parameters supplied by the user. |
| 1046 | * @return array |
| 1047 | */ |
| 1048 | protected function get_fields( $query_args ) { |
| 1049 | if ( isset( $query_args['fields'] ) && is_array( $query_args['fields'] ) ) { |
| 1050 | return $query_args['fields']; |
| 1051 | } |
| 1052 | return array_keys( $this->report_columns ); |
| 1053 | } |
| 1054 | |
| 1055 | /** |
| 1056 | * Returns a comma separated list of the field names prepared to be used for a selection after a join with `default_results`. |
| 1057 | * |
| 1058 | * @param array $fields Array of fields name. |
| 1059 | * @param array $default_results_fields Fields to load from `default_results` table. |
| 1060 | * @param array $outer_selections Array of fields that are not selected in the inner query. |
| 1061 | * @return string |
| 1062 | */ |
| 1063 | protected function format_join_selections( $fields, $default_results_fields, $outer_selections = array() ) { |
| 1064 | foreach ( $fields as $i => $field ) { |
| 1065 | foreach ( $default_results_fields as $default_results_field ) { |
| 1066 | if ( $field === $default_results_field ) { |
| 1067 | $field = esc_sql( $field ); |
| 1068 | $fields[ $i ] = "default_results.{$field} AS {$field}"; |
| 1069 | } |
| 1070 | } |
| 1071 | if ( in_array( $field, $outer_selections, true ) && array_key_exists( $field, $this->report_columns ) ) { |
| 1072 | $fields[ $i ] = $this->report_columns[ $field ]; |
| 1073 | } |
| 1074 | } |
| 1075 | return implode( ', ', $fields ); |
| 1076 | } |
| 1077 | |
| 1078 | /** |
| 1079 | * Fills ORDER BY clause of SQL request based on user supplied parameters. |
| 1080 | * |
| 1081 | * @param array $query_args Parameters supplied by the user. |
| 1082 | */ |
| 1083 | protected function add_order_by_sql_params( $query_args ) { |
| 1084 | if ( isset( $query_args['orderby'] ) ) { |
| 1085 | $order_by_clause = $this->normalize_order_by( esc_sql( $query_args['orderby'] ) ); |
| 1086 | } else { |
| 1087 | $order_by_clause = ''; |
| 1088 | } |
| 1089 | |
| 1090 | $this->clear_sql_clause( 'order_by' ); |
| 1091 | $this->add_sql_clause( 'order_by', $order_by_clause ); |
| 1092 | $this->add_orderby_order_clause( $query_args, $this ); |
| 1093 | } |
| 1094 | |
| 1095 | /** |
| 1096 | * Fills FROM and WHERE clauses of SQL request for 'Intervals' section of data response based on user supplied parameters. |
| 1097 | * |
| 1098 | * @param array $query_args Parameters supplied by the user. |
| 1099 | * @param string $table_name Name of the db table relevant for the date constraint. |
| 1100 | */ |
| 1101 | protected function add_intervals_sql_params( $query_args, $table_name ) { |
| 1102 | $this->clear_sql_clause( array( 'from', 'where_time', 'where' ) ); |
| 1103 | |
| 1104 | $this->add_time_period_sql_params( $query_args, $table_name ); |
| 1105 | |
| 1106 | if ( isset( $query_args['interval'] ) && '' !== $query_args['interval'] ) { |
| 1107 | $interval = $query_args['interval']; |
| 1108 | $this->clear_sql_clause( 'select' ); |
| 1109 | $this->add_sql_clause( 'select', TimeInterval::db_datetime_format( $interval, $table_name, $this->date_column_name ) ); |
| 1110 | } |
| 1111 | } |
| 1112 | |
| 1113 | /** |
| 1114 | * Get join and where clauses for refunds based on user supplied parameters. |
| 1115 | * |
| 1116 | * @param array $query_args Parameters supplied by the user. |
| 1117 | * @return array |
| 1118 | */ |
| 1119 | protected function get_refund_subquery( $query_args ) { |
| 1120 | global $wpdb; |
| 1121 | $table_name = $wpdb->prefix . 'wc_order_stats'; |
| 1122 | $sql_query = array( |
| 1123 | 'where_clause' => '', |
| 1124 | 'from_clause' => '', |
| 1125 | ); |
| 1126 | |
| 1127 | if ( ! isset( $query_args['refunds'] ) ) { |
| 1128 | return $sql_query; |
| 1129 | } |
| 1130 | |
| 1131 | if ( 'all' === $query_args['refunds'] ) { |
| 1132 | $sql_query['where_clause'] .= 'parent_id != 0'; |
| 1133 | } |
| 1134 | |
| 1135 | if ( 'none' === $query_args['refunds'] ) { |
| 1136 | $sql_query['where_clause'] .= 'parent_id = 0'; |
| 1137 | } |
| 1138 | |
| 1139 | if ( 'full' === $query_args['refunds'] || 'partial' === $query_args['refunds'] ) { |
| 1140 | $operator = 'full' === $query_args['refunds'] ? '=' : '!='; |
| 1141 | $sql_query['from_clause'] .= " JOIN {$table_name} parent_order_stats ON {$table_name}.parent_id = parent_order_stats.order_id"; |
| 1142 | $sql_query['where_clause'] .= "parent_order_stats.status {$operator} '{$this->normalize_order_status( 'refunded' )}'"; |
| 1143 | } |
| 1144 | |
| 1145 | return $sql_query; |
| 1146 | } |
| 1147 | |
| 1148 | /** |
| 1149 | * Returns an array of products belonging to given categories. |
| 1150 | * |
| 1151 | * @param array $categories List of categories IDs. |
| 1152 | * @return array|stdClass |
| 1153 | */ |
| 1154 | protected function get_products_by_cat_ids( $categories ) { |
| 1155 | $terms = get_terms( |
| 1156 | array( |
| 1157 | 'taxonomy' => 'product_cat', |
| 1158 | 'include' => $categories, |
| 1159 | ) |
| 1160 | ); |
| 1161 | |
| 1162 | if ( is_wp_error( $terms ) || empty( $terms ) ) { |
| 1163 | return array(); |
| 1164 | } |
| 1165 | |
| 1166 | $args = array( |
| 1167 | 'category' => wc_list_pluck( $terms, 'slug' ), |
| 1168 | 'limit' => -1, |
| 1169 | 'return' => 'ids', |
| 1170 | ); |
| 1171 | return wc_get_products( $args ); |
| 1172 | } |
| 1173 | |
| 1174 | /** |
| 1175 | * Get WHERE filter by object ids subquery. |
| 1176 | * |
| 1177 | * @param string $select_table Select table name. |
| 1178 | * @param string $select_field Select table object ID field name. |
| 1179 | * @param string $filter_table Lookup table name. |
| 1180 | * @param string $filter_field Lookup table object ID field name. |
| 1181 | * @param string $compare Comparison string (IN|NOT IN). |
| 1182 | * @param string $id_list Comma separated ID list. |
| 1183 | * |
| 1184 | * @return string |
| 1185 | */ |
| 1186 | protected function get_object_where_filter( $select_table, $select_field, $filter_table, $filter_field, $compare, $id_list ) { |
| 1187 | global $wpdb; |
| 1188 | if ( empty( $id_list ) ) { |
| 1189 | return ''; |
| 1190 | } |
| 1191 | |
| 1192 | $lookup_name = isset( $wpdb->$filter_table ) ? $wpdb->$filter_table : $wpdb->prefix . $filter_table; |
| 1193 | // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 1194 | return " {$select_table}.{$select_field} {$compare} ( |
| 1195 | SELECT |
| 1196 | DISTINCT {$filter_table}.{$select_field} |
| 1197 | FROM |
| 1198 | {$filter_table} |
| 1199 | WHERE |
| 1200 | {$filter_table}.{$filter_field} IN ({$id_list}) |
| 1201 | )"; |
| 1202 | // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 1203 | } |
| 1204 | |
| 1205 | /** |
| 1206 | * Returns an array of ids of allowed products, based on query arguments from the user. |
| 1207 | * |
| 1208 | * @param array $query_args Parameters supplied by the user. |
| 1209 | * @return array |
| 1210 | */ |
| 1211 | protected function get_included_products_array( $query_args ) { |
| 1212 | $included_products = array(); |
| 1213 | $operator = $this->get_match_operator( $query_args ); |
| 1214 | |
| 1215 | if ( isset( $query_args['category_includes'] ) && is_array( $query_args['category_includes'] ) && count( $query_args['category_includes'] ) > 0 ) { |
| 1216 | $included_products = $this->get_products_by_cat_ids( $query_args['category_includes'] ); |
| 1217 | |
| 1218 | // If no products were found in the specified categories, we will force an empty set |
| 1219 | // by matching a product ID of -1, unless the filters are OR/any and products are specified. |
| 1220 | if ( empty( $included_products ) ) { |
| 1221 | $included_products = array( '-1' ); |
| 1222 | } |
| 1223 | } |
| 1224 | |
| 1225 | if ( isset( $query_args['product_includes'] ) && is_array( $query_args['product_includes'] ) && count( $query_args['product_includes'] ) > 0 ) { |
| 1226 | if ( count( $included_products ) > 0 ) { |
| 1227 | if ( 'AND' === $operator ) { |
| 1228 | // AND results in an intersection between products from selected categories and manually included products. |
| 1229 | $included_products = array_intersect( $included_products, $query_args['product_includes'] ); |
| 1230 | } elseif ( 'OR' === $operator ) { |
| 1231 | // OR results in a union of products from selected categories and manually included products. |
| 1232 | $included_products = array_merge( $included_products, $query_args['product_includes'] ); |
| 1233 | } |
| 1234 | } else { |
| 1235 | $included_products = $query_args['product_includes']; |
| 1236 | } |
| 1237 | } |
| 1238 | |
| 1239 | return $included_products; |
| 1240 | } |
| 1241 | |
| 1242 | /** |
| 1243 | * Returns comma separated ids of allowed products, based on query arguments from the user. |
| 1244 | * |
| 1245 | * @param array $query_args Parameters supplied by the user. |
| 1246 | * @return string |
| 1247 | */ |
| 1248 | protected function get_included_products( $query_args ) { |
| 1249 | $included_products = $this->get_included_products_array( $query_args ); |
| 1250 | return implode( ',', $included_products ); |
| 1251 | } |
| 1252 | |
| 1253 | /** |
| 1254 | * Returns comma separated ids of allowed variations, based on query arguments from the user. |
| 1255 | * |
| 1256 | * @param array $query_args Parameters supplied by the user. |
| 1257 | * @return string |
| 1258 | */ |
| 1259 | protected function get_included_variations( $query_args ) { |
| 1260 | return $this->get_filtered_ids( $query_args, 'variation_includes' ); |
| 1261 | } |
| 1262 | |
| 1263 | /** |
| 1264 | * Returns comma separated ids of excluded variations, based on query arguments from the user. |
| 1265 | * |
| 1266 | * @param array $query_args Parameters supplied by the user. |
| 1267 | * @return string |
| 1268 | */ |
| 1269 | protected function get_excluded_variations( $query_args ) { |
| 1270 | return $this->get_filtered_ids( $query_args, 'variation_excludes' ); |
| 1271 | } |
| 1272 | |
| 1273 | /** |
| 1274 | * Returns an array of ids of disallowed products, based on query arguments from the user. |
| 1275 | * |
| 1276 | * @param array $query_args Parameters supplied by the user. |
| 1277 | * @return array |
| 1278 | */ |
| 1279 | protected function get_excluded_products_array( $query_args ) { |
| 1280 | $excluded_products = array(); |
| 1281 | $operator = $this->get_match_operator( $query_args ); |
| 1282 | |
| 1283 | if ( isset( $query_args['category_excludes'] ) && is_array( $query_args['category_excludes'] ) && count( $query_args['category_excludes'] ) > 0 ) { |
| 1284 | $excluded_products = $this->get_products_by_cat_ids( $query_args['category_excludes'] ); |
| 1285 | } |
| 1286 | |
| 1287 | if ( isset( $query_args['product_excludes'] ) && is_array( $query_args['product_excludes'] ) && count( $query_args['product_excludes'] ) > 0 ) { |
| 1288 | $excluded_products = array_merge( $excluded_products, $query_args['product_excludes'] ); |
| 1289 | } |
| 1290 | |
| 1291 | return $excluded_products; |
| 1292 | } |
| 1293 | |
| 1294 | /** |
| 1295 | * Returns comma separated ids of excluded products, based on query arguments from the user. |
| 1296 | * |
| 1297 | * @param array $query_args Parameters supplied by the user. |
| 1298 | * @return string |
| 1299 | */ |
| 1300 | protected function get_excluded_products( $query_args ) { |
| 1301 | $excluded_products = $this->get_excluded_products_array( $query_args ); |
| 1302 | return implode( ',', $excluded_products ); |
| 1303 | } |
| 1304 | |
| 1305 | /** |
| 1306 | * Returns comma separated ids of included categories, based on query arguments from the user. |
| 1307 | * |
| 1308 | * @param array $query_args Parameters supplied by the user. |
| 1309 | * @return string |
| 1310 | */ |
| 1311 | protected function get_included_categories( $query_args ) { |
| 1312 | return $this->get_filtered_ids( $query_args, 'category_includes' ); |
| 1313 | } |
| 1314 | |
| 1315 | /** |
| 1316 | * Returns comma separated ids of included coupons, based on query arguments from the user. |
| 1317 | * |
| 1318 | * @param array $query_args Parameters supplied by the user. |
| 1319 | * @param string $field Field name in the parameter list. |
| 1320 | * @return string |
| 1321 | */ |
| 1322 | protected function get_included_coupons( $query_args, $field = 'coupon_includes' ) { |
| 1323 | return $this->get_filtered_ids( $query_args, $field ); |
| 1324 | } |
| 1325 | |
| 1326 | /** |
| 1327 | * Returns comma separated ids of excluded coupons, based on query arguments from the user. |
| 1328 | * |
| 1329 | * @param array $query_args Parameters supplied by the user. |
| 1330 | * @return string |
| 1331 | */ |
| 1332 | protected function get_excluded_coupons( $query_args ) { |
| 1333 | return $this->get_filtered_ids( $query_args, 'coupon_excludes' ); |
| 1334 | } |
| 1335 | |
| 1336 | /** |
| 1337 | * Returns comma separated ids of included orders, based on query arguments from the user. |
| 1338 | * |
| 1339 | * @param array $query_args Parameters supplied by the user. |
| 1340 | * @return string |
| 1341 | */ |
| 1342 | protected function get_included_orders( $query_args ) { |
| 1343 | return $this->get_filtered_ids( $query_args, 'order_includes' ); |
| 1344 | } |
| 1345 | |
| 1346 | /** |
| 1347 | * Returns comma separated ids of excluded orders, based on query arguments from the user. |
| 1348 | * |
| 1349 | * @param array $query_args Parameters supplied by the user. |
| 1350 | * @return string |
| 1351 | */ |
| 1352 | protected function get_excluded_orders( $query_args ) { |
| 1353 | return $this->get_filtered_ids( $query_args, 'order_excludes' ); |
| 1354 | } |
| 1355 | |
| 1356 | /** |
| 1357 | * Returns comma separated ids of included users, based on query arguments from the user. |
| 1358 | * |
| 1359 | * @param array $query_args Parameters supplied by the user. |
| 1360 | * @return string |
| 1361 | */ |
| 1362 | protected function get_included_users( $query_args ) { |
| 1363 | return $this->get_filtered_ids( $query_args, 'user_includes' ); |
| 1364 | } |
| 1365 | |
| 1366 | /** |
| 1367 | * Returns comma separated ids of excluded users, based on query arguments from the user. |
| 1368 | * |
| 1369 | * @param array $query_args Parameters supplied by the user. |
| 1370 | * @return string |
| 1371 | */ |
| 1372 | protected function get_excluded_users( $query_args ) { |
| 1373 | return $this->get_filtered_ids( $query_args, 'user_excludes' ); |
| 1374 | } |
| 1375 | |
| 1376 | /** |
| 1377 | * Returns order status subquery to be used in WHERE SQL query, based on query arguments from the user. |
| 1378 | * |
| 1379 | * @param array $query_args Parameters supplied by the user. |
| 1380 | * @param string $operator AND or OR, based on match query argument. |
| 1381 | * @return string |
| 1382 | */ |
| 1383 | protected function get_status_subquery( $query_args, $operator = 'AND' ) { |
| 1384 | global $wpdb; |
| 1385 | |
| 1386 | $subqueries = array(); |
| 1387 | $excluded_statuses = array(); |
| 1388 | if ( isset( $query_args['status_is'] ) && is_array( $query_args['status_is'] ) && count( $query_args['status_is'] ) > 0 ) { |
| 1389 | $allowed_statuses = array_map( array( $this, 'normalize_order_status' ), esc_sql( $query_args['status_is'] ) ); |
| 1390 | if ( $allowed_statuses ) { |
| 1391 | $subqueries[] = "{$wpdb->prefix}wc_order_stats.status IN ( '" . implode( "','", $allowed_statuses ) . "' )"; |
| 1392 | } |
| 1393 | } |
| 1394 | |
| 1395 | if ( isset( $query_args['status_is_not'] ) && is_array( $query_args['status_is_not'] ) && count( $query_args['status_is_not'] ) > 0 ) { |
| 1396 | $excluded_statuses = array_map( array( $this, 'normalize_order_status' ), $query_args['status_is_not'] ); |
| 1397 | } |
| 1398 | |
| 1399 | if ( ( ! isset( $query_args['status_is'] ) || empty( $query_args['status_is'] ) ) |
| 1400 | && ( ! isset( $query_args['status_is_not'] ) || empty( $query_args['status_is_not'] ) ) |
| 1401 | ) { |
| 1402 | $excluded_statuses = array_map( array( $this, 'normalize_order_status' ), $this->get_excluded_report_order_statuses() ); |
| 1403 | } |
| 1404 | |
| 1405 | if ( $excluded_statuses ) { |
| 1406 | $subqueries[] = "{$wpdb->prefix}wc_order_stats.status NOT IN ( '" . implode( "','", $excluded_statuses ) . "' )"; |
| 1407 | } |
| 1408 | |
| 1409 | return implode( " $operator ", $subqueries ); |
| 1410 | } |
| 1411 | |
| 1412 | /** |
| 1413 | * Add order status SQL clauses if included in query. |
| 1414 | * |
| 1415 | * @param array $query_args Parameters supplied by the user. |
| 1416 | * @param string $table_name Database table name. |
| 1417 | * @param SqlQuery $sql_query Query object. |
| 1418 | */ |
| 1419 | protected function add_order_status_clause( $query_args, $table_name, &$sql_query ) { |
| 1420 | global $wpdb; |
| 1421 | $order_status_filter = $this->get_status_subquery( $query_args ); |
| 1422 | if ( $order_status_filter ) { |
| 1423 | $sql_query->add_sql_clause( 'join', "JOIN {$wpdb->prefix}wc_order_stats ON {$table_name}.order_id = {$wpdb->prefix}wc_order_stats.order_id" ); |
| 1424 | $sql_query->add_sql_clause( 'where', "AND ( {$order_status_filter} )" ); |
| 1425 | } |
| 1426 | } |
| 1427 | |
| 1428 | /** |
| 1429 | * Add order by SQL clause if included in query. |
| 1430 | * |
| 1431 | * @param array $query_args Parameters supplied by the user. |
| 1432 | * @param SqlQuery $sql_query Query object. |
| 1433 | * @return string Order by clause. |
| 1434 | */ |
| 1435 | protected function add_order_by_clause( $query_args, &$sql_query ) { |
| 1436 | $order_by_clause = ''; |
| 1437 | |
| 1438 | $sql_query->clear_sql_clause( array( 'order_by' ) ); |
| 1439 | if ( isset( $query_args['orderby'] ) ) { |
| 1440 | $order_by_clause = $this->normalize_order_by( esc_sql( $query_args['orderby'] ) ); |
| 1441 | $sql_query->add_sql_clause( 'order_by', $order_by_clause ); |
| 1442 | } |
| 1443 | |
| 1444 | // Return ORDER BY clause to allow adding the sort field(s) to query via a JOIN. |
| 1445 | return $order_by_clause; |
| 1446 | } |
| 1447 | |
| 1448 | /** |
| 1449 | * Add order by order SQL clause. |
| 1450 | * |
| 1451 | * @param array $query_args Parameters supplied by the user. |
| 1452 | * @param SqlQuery $sql_query Query object. |
| 1453 | */ |
| 1454 | protected function add_orderby_order_clause( $query_args, &$sql_query ) { |
| 1455 | if ( isset( $query_args['order'] ) ) { |
| 1456 | $sql_query->add_sql_clause( 'order_by', esc_sql( $query_args['order'] ) ); |
| 1457 | } else { |
| 1458 | $sql_query->add_sql_clause( 'order_by', 'DESC' ); |
| 1459 | } |
| 1460 | } |
| 1461 | |
| 1462 | /** |
| 1463 | * Returns customer subquery to be used in WHERE SQL query, based on query arguments from the user. |
| 1464 | * |
| 1465 | * @param array $query_args Parameters supplied by the user. |
| 1466 | * @return string |
| 1467 | */ |
| 1468 | protected function get_customer_subquery( $query_args ) { |
| 1469 | global $wpdb; |
| 1470 | |
| 1471 | $customer_filter = ''; |
| 1472 | if ( isset( $query_args['customer_type'] ) ) { |
| 1473 | if ( 'new' === strtolower( $query_args['customer_type'] ) ) { |
| 1474 | $customer_filter = " {$wpdb->prefix}wc_order_stats.returning_customer = 0"; |
| 1475 | } elseif ( 'returning' === strtolower( $query_args['customer_type'] ) ) { |
| 1476 | $customer_filter = " {$wpdb->prefix}wc_order_stats.returning_customer = 1"; |
| 1477 | } |
| 1478 | } |
| 1479 | |
| 1480 | return $customer_filter; |
| 1481 | } |
| 1482 | |
| 1483 | /** |
| 1484 | * Returns product attribute subquery elements used in JOIN and WHERE clauses, |
| 1485 | * based on query arguments from the user. |
| 1486 | * |
| 1487 | * @param array $query_args Parameters supplied by the user. |
| 1488 | * @return array |
| 1489 | */ |
| 1490 | protected function get_attribute_subqueries( $query_args ) { |
| 1491 | global $wpdb; |
| 1492 | |
| 1493 | $sql_clauses = array( |
| 1494 | 'join' => array(), |
| 1495 | 'where' => array(), |
| 1496 | ); |
| 1497 | $match_operator = $this->get_match_operator( $query_args ); |
| 1498 | $post_meta_comparators = array( |
| 1499 | '=' => 'attribute_is', |
| 1500 | '!=' => 'attribute_is_not', |
| 1501 | ); |
| 1502 | |
| 1503 | foreach ( $post_meta_comparators as $comparator => $arg ) { |
| 1504 | if ( ! isset( $query_args[ $arg ] ) || ! is_array( $query_args[ $arg ] ) ) { |
| 1505 | continue; |
| 1506 | } |
| 1507 | foreach ( $query_args[ $arg ] as $attribute_term ) { |
| 1508 | // We expect tuples. |
| 1509 | if ( ! is_array( $attribute_term ) || 2 !== count( $attribute_term ) ) { |
| 1510 | continue; |
| 1511 | } |
| 1512 | |
| 1513 | $term_id = ''; |
| 1514 | // If the tuple is numeric, assume these are IDs. |
| 1515 | if ( is_numeric( $attribute_term[0] ) && is_numeric( $attribute_term[1] ) ) { |
| 1516 | $attribute_id = intval( $attribute_term[0] ); |
| 1517 | $term_id = intval( $attribute_term[1] ); |
| 1518 | |
| 1519 | // Invalid IDs. |
| 1520 | if ( 0 === $attribute_id || 0 === $term_id ) { |
| 1521 | continue; |
| 1522 | } |
| 1523 | |
| 1524 | // @todo: Use wc_get_attribute () instead ? |
| 1525 | $attr_taxonomy = wc_attribute_taxonomy_name_by_id( $attribute_id ); |
| 1526 | // Invalid attribute ID. |
| 1527 | if ( empty( $attr_taxonomy ) ) { |
| 1528 | continue; |
| 1529 | } |
| 1530 | |
| 1531 | $attr_term = get_term_by( 'id', $term_id, $attr_taxonomy ); |
| 1532 | // Invalid term ID. |
| 1533 | if ( false === $attr_term ) { |
| 1534 | continue; |
| 1535 | } |
| 1536 | |
| 1537 | $meta_key = sanitize_title( $attr_taxonomy ); |
| 1538 | $meta_value = $attr_term->slug; |
| 1539 | } else { |
| 1540 | // Assume these are a custom attribute slug/value pair. |
| 1541 | $meta_key = esc_sql( $attribute_term[0] ); |
| 1542 | $meta_value = esc_sql( $attribute_term[1] ); |
| 1543 | $attr_term = get_term_by( 'slug', $meta_value, $meta_key ); |
| 1544 | if ( false !== $attr_term ) { |
| 1545 | $term_id = $attr_term->term_id; |
| 1546 | } |
| 1547 | } |
| 1548 | |
| 1549 | $join_alias = 'orderitemmeta1'; |
| 1550 | $table_to_join_on = "{$wpdb->prefix}wc_order_product_lookup"; |
| 1551 | |
| 1552 | if ( empty( $sql_clauses['join'] ) ) { |
| 1553 | $sql_clauses['join'][] = "JOIN {$wpdb->prefix}woocommerce_order_items orderitems ON orderitems.order_id = {$table_to_join_on}.order_id"; |
| 1554 | } |
| 1555 | |
| 1556 | // If we're matching all filters (AND), we'll need multiple JOINs on postmeta. |
| 1557 | // If not, just one. |
| 1558 | if ( 'AND' === $match_operator || 1 === count( $sql_clauses['join'] ) ) { |
| 1559 | $join_idx = count( $sql_clauses['join'] ); |
| 1560 | $join_alias = 'orderitemmeta' . $join_idx; |
| 1561 | $sql_clauses['join'][] = "JOIN {$wpdb->prefix}woocommerce_order_itemmeta as {$join_alias} ON {$join_alias}.order_item_id = {$table_to_join_on}.order_item_id"; |
| 1562 | } |
| 1563 | |
| 1564 | $in_comparator = '=' === $comparator ? 'in' : 'not in'; |
| 1565 | |
| 1566 | // Add subquery for products ordered using attributes not used in variations. |
| 1567 | $term_attribute_subquery = "select product_id from {$wpdb->prefix}wc_product_attributes_lookup where is_variation_attribute=0 and term_id = %s"; |
| 1568 | // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 1569 | // phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber |
| 1570 | $sql_clauses['where'][] = $wpdb->prepare( |
| 1571 | " |
| 1572 | ( ( {$join_alias}.meta_key = %s AND {$join_alias}.meta_value {$comparator} %s ) or ( |
| 1573 | {$wpdb->prefix}wc_order_product_lookup.variation_id = 0 and {$wpdb->prefix}wc_order_product_lookup.product_id {$in_comparator} ({$term_attribute_subquery}) |
| 1574 | ) )", |
| 1575 | $meta_key, |
| 1576 | $meta_value, |
| 1577 | $term_id, |
| 1578 | ); |
| 1579 | // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared |
| 1580 | // phpcs:enable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber |
| 1581 | } |
| 1582 | } |
| 1583 | |
| 1584 | // If we're matching multiple attributes and all filters (AND), make sure |
| 1585 | // we're matching attributes on the same product. |
| 1586 | $num_attribute_filters = count( $sql_clauses['join'] ); |
| 1587 | |
| 1588 | for ( $i = 2; $i < $num_attribute_filters; $i++ ) { |
| 1589 | $join_alias = 'orderitemmeta' . $i; |
| 1590 | $sql_clauses['join'][] = "AND orderitemmeta1.order_item_id = {$join_alias}.order_item_id"; |
| 1591 | } |
| 1592 | |
| 1593 | return $sql_clauses; |
| 1594 | } |
| 1595 | |
| 1596 | /** |
| 1597 | * Returns logic operator for WHERE subclause based on 'match' query argument. |
| 1598 | * |
| 1599 | * @param array $query_args Parameters supplied by the user. |
| 1600 | * @return string |
| 1601 | */ |
| 1602 | protected function get_match_operator( $query_args ) { |
| 1603 | $operator = 'AND'; |
| 1604 | |
| 1605 | if ( ! isset( $query_args['match'] ) ) { |
| 1606 | return $operator; |
| 1607 | } |
| 1608 | |
| 1609 | if ( 'all' === strtolower( $query_args['match'] ) ) { |
| 1610 | $operator = 'AND'; |
| 1611 | } elseif ( 'any' === strtolower( $query_args['match'] ) ) { |
| 1612 | $operator = 'OR'; |
| 1613 | } |
| 1614 | return $operator; |
| 1615 | } |
| 1616 | |
| 1617 | /** |
| 1618 | * Returns filtered comma separated ids, based on query arguments from the user. |
| 1619 | * |
| 1620 | * @param array $query_args Parameters supplied by the user. |
| 1621 | * @param string $field Query field to filter. |
| 1622 | * @param string $separator Field separator. |
| 1623 | * @return string |
| 1624 | */ |
| 1625 | protected function get_filtered_ids( $query_args, $field, $separator = ',' ) { |
| 1626 | global $wpdb; |
| 1627 | |
| 1628 | $ids_str = ''; |
| 1629 | $ids = isset( $query_args[ $field ] ) && is_array( $query_args[ $field ] ) ? $query_args[ $field ] : array(); |
| 1630 | |
| 1631 | /** |
| 1632 | * Filter the IDs before retrieving report data. |
| 1633 | * |
| 1634 | * Allows filtering of the objects included or excluded from reports. |
| 1635 | * |
| 1636 | * @param array $ids List of object Ids. |
| 1637 | * @param array $query_args The original arguments for the request. |
| 1638 | * @param string $field The object type. |
| 1639 | * @param string $context The data store context. |
| 1640 | */ |
| 1641 | $ids = apply_filters( 'woocommerce_analytics_' . $field, $ids, $query_args, $field, $this->context ); |
| 1642 | |
| 1643 | if ( ! empty( $ids ) ) { |
| 1644 | $placeholders = implode( $separator, array_fill( 0, count( $ids ), '%d' ) ); |
| 1645 | /* phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared */ |
| 1646 | $ids_str = $wpdb->prepare( "{$placeholders}", $ids ); |
| 1647 | /* phpcs:enable */ |
| 1648 | } |
| 1649 | return $ids_str; |
| 1650 | } |
| 1651 | |
| 1652 | /** |
| 1653 | * Assign report columns once full table name has been assigned. |
| 1654 | */ |
| 1655 | protected function assign_report_columns() {} |
| 1656 | } |
| 1657 |