DataStore.php — WooCommerce 11.0.0-beta.1

woocommerce / src / Admin / API / Reports / DataStore.php

woocommerce / src / Admin / API / Reports Last commit date

Categories 1 month ago Coupons 1 month ago Customers 5 days ago Downloads 1 year ago Export 3 years ago Import 3 years ago Orders 1 month ago PerformanceIndicators 9 months ago Products 5 days ago Revenue 3 months ago Stock 4 months ago Taxes 1 month ago Variations 5 days ago Cache.php 4 years ago Controller.php 11 months ago DataStore.php 5 days ago DataStoreInterface.php 4 years ago ExportableInterface.php 4 years ago ExportableTraits.php 4 years ago FilteredGetDataTrait.php 1 year ago GenericController.php 1 year ago GenericQuery.php 1 year 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

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