DataStore.php — WooCommerce 10.9.1

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

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

Stats 3 months ago Controller.php 3 months ago DataStore.php 2 months ago Query.php 1 year ago

DataStore.php

1189 lines

1	<?php
2	/**
3	* Admin\API\Reports\Customers\DataStore class file.
4	*/
5
6	namespace Automattic\WooCommerce\Admin\API\Reports\Customers;
7
8	defined( 'ABSPATH' ) \|\| exit;
9
10	use Automattic\WooCommerce\Admin\API\Reports\DataStore as ReportsDataStore;
11	use Automattic\WooCommerce\Admin\API\Reports\DataStoreInterface;
12	use Automattic\WooCommerce\Admin\API\Reports\TimeInterval;
13	use Automattic\WooCommerce\Admin\API\Reports\SqlQuery;
14	use Automattic\WooCommerce\Admin\API\Reports\Cache as ReportsCache;
15	use Automattic\WooCommerce\Utilities\OrderUtil;
16
17	/**
18	* Admin\API\Reports\Customers\DataStore.
19	*/
20	class DataStore extends ReportsDataStore implements DataStoreInterface {
21
22	/**
23	* Table used to get the data.
24	*
25	* @override ReportsDataStore::$table_name
26	*
27	* @var string
28	*/
29	protected static $table_name = 'wc_customer_lookup';
30
31	/**
32	* Cache identifier.
33	*
34	* @override ReportsDataStore::$cache_key
35	*
36	* @var string
37	*/
38	protected $cache_key = 'customers';
39
40	/**
41	* Mapping columns to data type to return correct response types.
42	*
43	* @override ReportsDataStore::$column_types
44	*
45	* @var array
46	*/
47	protected $column_types = array(
48	'id' => 'intval',
49	'user_id' => 'intval',
50	'orders_count' => 'intval',
51	'total_spend' => 'floatval',
52	'avg_order_value' => 'floatval',
53	);
54
55	/**
56	* Data store context used to pass to filters.
57	*
58	* @override ReportsDataStore::$context
59	*
60	* @var string
61	*/
62	protected $context = 'customers';
63
64	/**
65	* Assign report columns once full table name has been assigned.
66	*
67	* @override ReportsDataStore::assign_report_columns()
68	*/
69	protected function assign_report_columns() {
70	global $wpdb;
71	$table_name = self::get_db_table_name();
72	$orders_count = 'SUM( CASE WHEN parent_id = 0 THEN 1 ELSE 0 END )';
73	$total_spend = 'SUM( total_sales )';
74	$this->report_columns = array(
75	'id' => "{$table_name}.customer_id as id",
76	'user_id' => 'user_id',
77	'username' => 'username',
78	'name' => "CONCAT_WS( ' ', first_name, last_name ) as name", // @xxx: What does this mean for RTL?
79	'first_name' => 'first_name',
80	'last_name' => 'last_name',
81	'email' => 'email',
82	'country' => 'country',
83	'city' => 'city',
84	'state' => 'state',
85	'postcode' => 'postcode',
86	'date_registered' => 'date_registered',
87	// Use single quotes for string literals to ensure compatibility with sql_mode=ANSI_QUOTES.
88	'date_last_active' => "IF( date_last_active <= '0000-00-00 00:00:00', NULL, date_last_active ) AS date_last_active",
89	'date_last_order' => "MAX( {$wpdb->prefix}wc_order_stats.date_created ) as date_last_order",
90	'orders_count' => "{$orders_count} as orders_count",
91	'total_spend' => "{$total_spend} as total_spend",
92	'avg_order_value' => "CASE WHEN {$orders_count} = 0 THEN NULL ELSE {$total_spend} / {$orders_count} END AS avg_order_value",
93	);
94	}
95
96	/**
97	* Set up all the hooks for maintaining and populating table data.
98	*/
99	public static function init() {
100	add_action( 'woocommerce_new_customer', array( __CLASS__, 'update_registered_customer' ) );
101
102	add_action( 'woocommerce_update_customer', array( __CLASS__, 'update_registered_customer' ) );
103	add_action( 'profile_update', array( __CLASS__, 'update_registered_customer' ) );
104
105	add_action( 'added_user_meta', array( __CLASS__, 'update_registered_customer_via_last_active' ), 10, 3 );
106	add_action( 'updated_user_meta', array( __CLASS__, 'update_registered_customer_via_last_active' ), 10, 3 );
107
108	add_action( 'delete_user', array( __CLASS__, 'delete_customer_by_user_id' ) );
109	add_action( 'remove_user_from_blog', array( __CLASS__, 'delete_customer_by_user_id' ) );
110
111	add_action( 'woocommerce_privacy_remove_order_personal_data', array( __CLASS__, 'anonymize_customer' ) );
112
113	add_action( 'woocommerce_analytics_delete_order_stats', array( __CLASS__, 'sync_on_order_delete' ), 15, 2 );
114
115	add_action( 'woocommerce_created_customer', array( __CLASS__, 'merge_guest_customer_on_delayed_account_creation' ), 5, 2 );
116	}
117
118	/**
119	* When a customer registers via delayed account creation (order confirmation page),
120	* merge the existing guest lookup row instead of creating a duplicate.
121	*
122	* This runs on woocommerce_created_customer at priority 5, before the analytics
123	* hooks (woocommerce_new_customer) that call update_registered_customer(). It updates
124	* the guest row's user_id so that update_registered_customer() finds it via
125	* get_customer_id_by_user_id() and updates in place rather than inserting a new row.
126	*
127	* @param int $customer_id New WP user ID.
128	* @param array $new_customer_data Customer data including 'source'.
129	*/
130	public static function merge_guest_customer_on_delayed_account_creation( $customer_id, $new_customer_data ): void {
131	if ( empty( $new_customer_data['source'] ) \|\| 'delayed-account-creation' !== $new_customer_data['source'] ) {
132	return;
133	}
134
135	$email = $new_customer_data['user_email'] ?? '';
136	if ( empty( $email ) ) {
137	return;
138	}
139
140	$guest_customer_id = self::get_guest_id_by_email( $email );
141	if ( ! $guest_customer_id ) {
142	return;
143	}
144
145	global $wpdb;
146	$wpdb->update(
147	self::get_db_table_name(),
148	array( 'user_id' => $customer_id ),
149	array( 'customer_id' => $guest_customer_id ),
150	array( '%d' ),
151	array( '%d' )
152	);
153	}
154
155	/**
156	* Sync customers data after an order was deleted.
157	*
158	* When an order is deleted, the customer record is deleted from the
159	* table if the customer has no other orders.
160	*
161	* @param int $order_id Order ID.
162	* @param int $customer_id Customer ID.
163	*/
164	public static function sync_on_order_delete( $order_id, $customer_id ) {
165	$customer_id = absint( $customer_id );
166
167	if ( 0 === $customer_id ) {
168	return;
169	}
170
171	// Calculate the amount of orders remaining for this customer.
172	$order_count = self::get_order_count( $customer_id );
173
174	if ( 0 === $order_count ) {
175	self::delete_customer( $customer_id );
176	}
177	}
178
179	/**
180	* Sync customers data after an order was updated.
181	*
182	* Only updates customer if it is the customers last order.
183	*
184	* @param int $post_id of order.
185	* @return true\|-1
186	*/
187	public static function sync_order_customer( $post_id ) {
188	global $wpdb;
189
190	if ( ! OrderUtil::is_order( $post_id, array( 'shop_order', 'shop_order_refund' ) ) ) {
191	return -1;
192	}
193
194	$order = wc_get_order( $post_id );
195	$customer_id = self::get_existing_customer_id_from_order( $order );
196	if ( false === $customer_id ) {
197	return -1;
198	}
199	$last_order = self::get_last_order( $customer_id );
200
201	if ( ! $last_order \|\| $order->get_id() !== $last_order->get_id() ) {
202	return -1;
203	}
204
205	list($data, $format) = self::get_customer_order_data_and_format( $order );
206
207	$result = $wpdb->update( self::get_db_table_name(), $data, array( 'customer_id' => $customer_id ), $format );
208
209	/**
210	* Fires when a customer is updated.
211	*
212	* @param int $customer_id Customer ID.
213	* @since 4.0.0
214	*/
215	do_action( 'woocommerce_analytics_update_customer', $customer_id );
216
217	return 1 === $result;
218	}
219
220	/**
221	* Fills ORDER BY clause of SQL request based on user supplied parameters. Overridden here to allow multiple direction
222	* clauses.
223	*
224	* @since 10.5.0
225	* @param array $query_args Parameters supplied by the user.
226	* @return void
227	*/
228	protected function add_order_by_sql_params( $query_args ) {
229	$order_by_clause = $this->normalize_order_by_clause(
230	$query_args['orderby'] ?? 'date_registered',
231	$query_args['order'] ?? 'desc'
232	);
233	$this->clear_sql_clause( 'order_by' );
234	$this->add_sql_clause( 'order_by', $order_by_clause );
235	}
236
237	/**
238	* Maps ordering specified by the user to columns in the database/fields in the data.
239	*
240	* Handles both order_by and direction.
241	*
242	* @since 10.5.0
243	* @param string $order_by Sorting criterion.
244	* @param string $order Order direction.
245	* @return string
246	*/
247	protected function normalize_order_by_clause( $order_by, $order = 'desc' ) {
248	$order_by = esc_sql( $order_by );
249	$order = strtolower( $order ) === 'asc' ? 'ASC' : 'DESC';
250	$order_by_clause = '';
251
252	if ( 'location' === $order_by ) {
253	$order_by_clause = "state {$order}, country {$order}";
254	} else {
255	$order_by_clause = "{$order_by} {$order}";
256	}
257
258	return $order_by_clause;
259	}
260
261	/**
262	* Fills WHERE clause of SQL request with date-related constraints.
263	*
264	* @override ReportsDataStore::add_time_period_sql_params()
265	*
266	* @param array $query_args Parameters supplied by the user.
267	* @param string $table_name Name of the db table relevant for the date constraint.
268	*/
269	protected function add_time_period_sql_params( $query_args, $table_name ) {
270	global $wpdb;
271
272	$this->clear_sql_clause( array( 'where', 'where_time', 'having' ) );
273	$date_param_mapping = array(
274	'registered' => array(
275	'clause' => 'where',
276	'column' => $table_name . '.date_registered',
277	),
278	'order' => array(
279	'clause' => 'where',
280	'column' => $wpdb->prefix . 'wc_order_stats.date_created',
281	),
282	'last_active' => array(
283	'clause' => 'where',
284	'column' => $table_name . '.date_last_active',
285	),
286	'last_order' => array(
287	'clause' => 'having',
288	'column' => "MAX( {$wpdb->prefix}wc_order_stats.date_created )",
289	),
290	);
291	$match_operator = $this->get_match_operator( $query_args );
292	$where_time_clauses = array();
293	$having_time_clauses = array();
294
295	foreach ( $date_param_mapping as $query_param => $param_info ) {
296	$subclauses = array();
297	$before_arg = $query_param . '_before';
298	$after_arg = $query_param . '_after';
299	$column_name = $param_info['column'];
300
301	if ( ! empty( $query_args[ $before_arg ] ) ) {
302	$datetime = new \DateTime( $query_args[ $before_arg ] );
303	$datetime_str = $datetime->format( TimeInterval::$sql_datetime_format );
304	$subclauses[] = "{$column_name} <= '$datetime_str'";
305	}
306
307	if ( ! empty( $query_args[ $after_arg ] ) ) {
308	$datetime = new \DateTime( $query_args[ $after_arg ] );
309	$datetime_str = $datetime->format( TimeInterval::$sql_datetime_format );
310	$subclauses[] = "{$column_name} >= '$datetime_str'";
311	}
312
313	if ( $subclauses && ( 'where' === $param_info['clause'] ) ) {
314	$where_time_clauses[] = '(' . implode( ' AND ', $subclauses ) . ')';
315	}
316
317	if ( $subclauses && ( 'having' === $param_info['clause'] ) ) {
318	$having_time_clauses[] = '(' . implode( ' AND ', $subclauses ) . ')';
319	}
320	}
321
322	if ( $where_time_clauses ) {
323	$this->subquery->add_sql_clause( 'where_time', 'AND ' . implode( " {$match_operator} ", $where_time_clauses ) );
324	}
325
326	if ( $having_time_clauses ) {
327	$this->subquery->add_sql_clause( 'having', 'AND ' . implode( " {$match_operator} ", $having_time_clauses ) );
328	}
329	}
330
331	/**
332	* Updates the database query with parameters used for Customers report: categories and order status.
333	*
334	* @param array $query_args Query arguments supplied by the user.
335	*/
336	protected function add_sql_query_params( $query_args ) {
337	global $wpdb;
338	$customer_lookup_table = self::get_db_table_name();
339	$order_stats_table_name = $wpdb->prefix . 'wc_order_stats';
340
341	$this->add_time_period_sql_params( $query_args, $customer_lookup_table );
342	$this->get_limit_sql_params( $query_args );
343	$this->add_order_by_sql_params( $query_args );
344	$this->subquery->add_sql_clause( 'left_join', "LEFT JOIN {$order_stats_table_name} ON {$customer_lookup_table}.customer_id = {$order_stats_table_name}.customer_id" );
345
346	$match_operator = $this->get_match_operator( $query_args );
347	$where_clauses = array();
348	$having_clauses = array();
349
350	$exact_match_params = array(
351	'name' => "CONCAT_WS( ' ', {$customer_lookup_table}.first_name, {$customer_lookup_table}.last_name )",
352	'username' => "{$customer_lookup_table}.username",
353	'email' => "{$customer_lookup_table}.email",
354	'country' => "{$customer_lookup_table}.country",
355	);
356
357	foreach ( $exact_match_params as $exact_match_param => $column_expression ) {
358	if ( ! empty( $query_args[ $exact_match_param . '_includes' ] ) ) {
359	$exact_match_arguments = $query_args[ $exact_match_param . '_includes' ];
360	$exact_match_arguments_escaped = array_map( 'esc_sql', explode( ',', $exact_match_arguments ) );
361	$included = implode( "','", $exact_match_arguments_escaped );
362	$where_clauses[] = "{$column_expression} IN ('{$included}')";
363	}
364
365	if ( ! empty( $query_args[ $exact_match_param . '_excludes' ] ) ) {
366	$exact_match_arguments = $query_args[ $exact_match_param . '_excludes' ];
367	$exact_match_arguments_escaped = array_map( 'esc_sql', explode( ',', $exact_match_arguments ) );
368	$excluded = implode( "','", $exact_match_arguments_escaped );
369	$where_clauses[] = "{$column_expression} NOT IN ('{$excluded}')";
370	}
371	}
372
373	$search_params = array(
374	'name',
375	'username',
376	'email',
377	'all',
378	);
379
380	if ( ! empty( $query_args['search'] ) ) {
381	$name_like = '%' . $wpdb->esc_like( $query_args['search'] ) . '%';
382
383	if ( empty( $query_args['searchby'] ) \|\| 'name' === $query_args['searchby'] \|\| ! in_array( $query_args['searchby'], $search_params, true ) ) {
384	$searchby = "CONCAT_WS( ' ', first_name, last_name )";
385	} elseif ( 'all' === $query_args['searchby'] ) {
386	$searchby = "CONCAT_WS( ' ', first_name, last_name, username, email )";
387	} else {
388	$searchby = $query_args['searchby'];
389	}
390
391	$where_clauses[] = $wpdb->prepare( "{$searchby} LIKE %s", $name_like ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
392	}
393
394	$filter_empty_params = array(
395	'email',
396	'name',
397	'country',
398	'city',
399	'state',
400	'postcode',
401	);
402
403	if ( ! empty( $query_args['filter_empty'] ) ) {
404	$fields_to_filter_by = array_intersect( $query_args['filter_empty'], $filter_empty_params );
405	if ( in_array( 'name', $fields_to_filter_by, true ) ) {
406	$fields_to_filter_by = array_diff( $fields_to_filter_by, array( 'name' ) );
407	$fields_to_filter_by[] = "CONCAT_WS( ' ', first_name, last_name )";
408	}
409	$fields_with_not_condition = array_map(
410	function ( $field ) {
411	return $field . ' <> \'\'';
412	},
413	$fields_to_filter_by
414	);
415	$where_clauses[] = '(' . implode( ' AND ', $fields_with_not_condition ) . ')';
416	}
417
418	// Allow a list of customer IDs to be specified.
419	if ( ! empty( $query_args['customers'] ) ) {
420	$included_customers = $this->get_filtered_ids( $query_args, 'customers' );
421	$where_clauses[] = "{$customer_lookup_table}.customer_id IN ({$included_customers})";
422	}
423
424	// Allow a list of customer IDs to be excluded.
425	if ( ! empty( $query_args['customers_exclude'] ) ) {
426	$excluded_customers = $this->get_filtered_ids( $query_args, 'customers_exclude' );
427	$where_clauses[] = "{$customer_lookup_table}.customer_id NOT IN ({$excluded_customers})";
428	}
429
430	// Allow a list of user IDs to be specified.
431	if ( ! empty( $query_args['users'] ) ) {
432	$included_users = $this->get_filtered_ids( $query_args, 'users' );
433	$where_clauses[] = "{$customer_lookup_table}.user_id IN ({$included_users})";
434	}
435
436	// Allow a list of locations to be specified (includes).
437	if ( ! empty( $query_args['location_includes'] ) ) {
438	$location_clause = $this->build_location_filter_clause( $query_args['location_includes'], true );
439	if ( '' !== $location_clause ) {
440	$where_clauses[] = $location_clause;
441	}
442	}
443
444	// Allow a list of locations to be excluded.
445	if ( ! empty( $query_args['location_excludes'] ) ) {
446	$location_clause = $this->build_location_filter_clause( $query_args['location_excludes'], false );
447	if ( '' !== $location_clause ) {
448	$where_clauses[] = $location_clause;
449	}
450	}
451
452	// Filter by user type.
453	if ( ! empty( $query_args['user_type'] ) && 'all' !== $query_args['user_type'] ) {
454	$user_type = $query_args['user_type'];
455	$where_clauses[] = "{$customer_lookup_table}.user_id IS " . ( 'registered' === $user_type ? 'NOT NULL' : 'NULL' );
456	}
457
458	$numeric_params = array(
459	'orders_count' => array(
460	'column' => 'COUNT( order_id )',
461	'format' => '%d',
462	),
463	'total_spend' => array(
464	'column' => 'SUM( total_sales )',
465	'format' => '%f',
466	),
467	'avg_order_value' => array(
468	'column' => '( SUM( total_sales ) / COUNT( order_id ) )',
469	'format' => '%f',
470	),
471	);
472
473	foreach ( $numeric_params as $numeric_param => $param_info ) {
474	$subclauses = array();
475	$min_param = $numeric_param . '_min';
476	$max_param = $numeric_param . '_max';
477	$or_equal = isset( $query_args[ $min_param ] ) && isset( $query_args[ $max_param ] ) ? '=' : '';
478
479	if ( isset( $query_args[ $min_param ] ) ) {
480	$subclauses[] = $wpdb->prepare(
481	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
482	"{$param_info['column']} >{$or_equal} {$param_info['format']}",
483	$query_args[ $min_param ]
484	);
485	}
486
487	if ( isset( $query_args[ $max_param ] ) ) {
488	$subclauses[] = $wpdb->prepare(
489	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
490	"{$param_info['column']} <{$or_equal} {$param_info['format']}",
491	$query_args[ $max_param ]
492	);
493	}
494
495	if ( $subclauses ) {
496	$having_clauses[] = '(' . implode( ' AND ', $subclauses ) . ')';
497	}
498	}
499
500	if ( $where_clauses ) {
501	$preceding_match = empty( $this->get_sql_clause( 'where_time' ) ) ? ' AND ' : " {$match_operator} ";
502	$this->subquery->add_sql_clause( 'where', $preceding_match . implode( " {$match_operator} ", $where_clauses ) );
503	}
504
505	$order_status_filter = $this->get_status_subquery( $query_args );
506	if ( $order_status_filter ) {
507	$this->subquery->add_sql_clause( 'left_join', "AND ( {$order_status_filter} )" );
508	}
509
510	if ( $having_clauses ) {
511	$preceding_match = empty( $this->get_sql_clause( 'having' ) ) ? ' AND ' : " {$match_operator} ";
512	$this->subquery->add_sql_clause( 'having', $preceding_match . implode( " {$match_operator} ", $having_clauses ) );
513	}
514	}
515
516	/**
517	* Get the default query arguments to be used by get_data().
518	* These defaults are only partially applied when used via REST API, as that has its own defaults.
519	*
520	* @override ReportsDataStore::get_default_query_vars()
521	*
522	* @return array Query parameters.
523	*/
524	public function get_default_query_vars() {
525	$defaults = parent::get_default_query_vars();
526	$defaults['orderby'] = 'date_registered';
527	$defaults['order_before'] = TimeInterval::default_before();
528	$defaults['order_after'] = TimeInterval::default_after();
529
530	return $defaults;
531	}
532
533	/**
534	* Returns an existing customer ID for an order if one exists.
535	*
536	* @param object $order WC Order.
537	* @return int\|bool
538	*/
539	public static function get_existing_customer_id_from_order( $order ) {
540	global $wpdb;
541
542	if ( ! is_a( $order, 'WC_Order' ) ) {
543	return false;
544	}
545
546	$user_id = $order->get_customer_id();
547
548	if ( 0 === $user_id ) {
549	$customer_id = $wpdb->get_var(
550	$wpdb->prepare(
551	"SELECT customer_id FROM {$wpdb->prefix}wc_order_stats WHERE order_id = %d",
552	$order->get_id()
553	)
554	);
555
556	if ( $customer_id ) {
557	return $customer_id;
558	}
559
560	$email = $order->get_billing_email( 'edit' );
561
562	if ( $email ) {
563	return self::get_customer_id_by_email( $email );
564	} else {
565	return false;
566	}
567	} else {
568	return self::get_customer_id_by_user_id( $user_id );
569	}
570	}
571
572	/**
573	* Returns the report data based on normalized parameters.
574	* Will be called by `get_data` if there is no data in cache.
575	*
576	* @override ReportsDataStore::get_noncached_data()
577	*
578	* @see get_data
579	* @param array $query_args Query parameters.
580	* @return stdClass\|WP_Error Data object `{ totals: *, intervals: array, total: int, pages: int, page_no: int }`, or error.
581	*/
582	public function get_noncached_data( $query_args ) {
583	global $wpdb;
584
585	$this->initialize_queries();
586
587	$data = (object) array(
588	'data' => array(),
589	'total' => 0,
590	'pages' => 0,
591	'page_no' => 0,
592	);
593
594	$selections = $this->selected_columns( $query_args );
595	$sql_query_params = $this->add_sql_query_params( $query_args );
596	$count_query = "SELECT COUNT(*) FROM (
597	{$this->subquery->get_query_statement()}
598	) as tt
599	";
600	$db_records_count = (int) $wpdb->get_var(
601	$count_query // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
602	);
603
604	$params = $this->get_limit_params( $query_args );
605	$total_pages = (int) ceil( $db_records_count / $params['per_page'] );
606	if ( $query_args['page'] < 1 \|\| $query_args['page'] > $total_pages ) {
607	return $data;
608	}
609
610	$this->subquery->clear_sql_clause( 'select' );
611	$this->subquery->add_sql_clause( 'select', $selections );
612	// For aggregated fields, ensure deterministic ordering by including GROUP BY field.
613	$order_by = $this->get_sql_clause( 'order_by' );
614	$aggregated_fields = array( 'orders_count', 'total_spend', 'avg_order_value' );
615	$has_aggregated_field = false;
616
617	foreach ( $aggregated_fields as $field ) {
618	if ( false !== strpos( $order_by, $field ) ) {
619	$has_aggregated_field = true;
620	break;
621	}
622	}
623
624	if ( $has_aggregated_field ) {
625	$customer_lookup_table = self::get_db_table_name();
626	$this->subquery->add_sql_clause( 'order_by', $order_by . ", {$customer_lookup_table}.customer_id" );
627	} else {
628	$this->subquery->add_sql_clause( 'order_by', $order_by );
629	}
630	$this->subquery->add_sql_clause( 'limit', $this->get_sql_clause( 'limit' ) );
631
632	$customer_data = $wpdb->get_results(
633	$this->subquery->get_query_statement(), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
634	ARRAY_A
635	);
636
637	if ( null === $customer_data ) {
638	return $data;
639	}
640
641	$customer_data = array_map( array( $this, 'cast_numbers' ), $customer_data );
642	$data = (object) array(
643	'data' => $customer_data,
644	'total' => $db_records_count,
645	'pages' => $total_pages,
646	'page_no' => (int) $query_args['page'],
647	);
648
649	return $data;
650	}
651
652	/**
653	* Get or create a customer from a given order.
654	*
655	* @param object $order WC Order.
656	* @return int\|bool
657	*/
658	public static function get_or_create_customer_from_order( $order ) {
659	if ( ! $order ) {
660	return false;
661	}
662
663	global $wpdb;
664
665	if ( ! is_a( $order, 'WC_Order' ) ) {
666	return false;
667	}
668
669	$returning_customer_id = self::get_existing_customer_id_from_order( $order );
670
671	if ( $returning_customer_id ) {
672	return $returning_customer_id;
673	}
674
675	list($data, $format) = self::get_customer_order_data_and_format( $order );
676
677	$result = $wpdb->insert( self::get_db_table_name(), $data, $format );
678	$customer_id = $wpdb->insert_id;
679
680	/**
681	* Fires when a new report customer is created.
682	*
683	* @param int $customer_id Customer ID.
684	* @since 4.0.0
685	*/
686	do_action( 'woocommerce_analytics_new_customer', $customer_id );
687
688	return $result ? $customer_id : false;
689	}
690
691	/**
692	* Returns a data object and format object of the customers data coming from the order.
693	*
694	* @param object $order WC_Order where we get customer info from.
695	* @param object\|null $customer_user WC_Customer registered customer WP user.
696	* @return array ($data, $format)
697	*/
698	public static function get_customer_order_data_and_format( $order, $customer_user = null ) {
699	$data = array(
700	'first_name' => $order->get_customer_first_name(),
701	'last_name' => $order->get_customer_last_name(),
702	'email' => $order->get_billing_email( 'edit' ),
703	'city' => $order->get_billing_city( 'edit' ),
704	'state' => $order->get_billing_state( 'edit' ),
705	'postcode' => $order->get_billing_postcode( 'edit' ),
706	'country' => $order->get_billing_country( 'edit' ),
707	'date_last_active' => gmdate( 'Y-m-d H:i:s', $order->get_date_created( 'edit' )->getTimestamp() ),
708	);
709	$format = array(
710	'%s',
711	'%s',
712	'%s',
713	'%s',
714	'%s',
715	'%s',
716	'%s',
717	'%s',
718	);
719
720	// Add registered customer data.
721	if ( 0 !== $order->get_user_id() ) {
722	$user_id = $order->get_user_id();
723	if ( is_null( $customer_user ) ) {
724	$customer_user = new \WC_Customer( $user_id );
725	}
726
727	// Set email as customer email instead of Order Billing Email if we have a customer.
728	$data['email'] = $customer_user->get_email( 'edit' );
729
730	// Adding other relevant customer data.
731	$data['user_id'] = $user_id;
732	$data['username'] = $customer_user->get_username( 'edit' );
733	$data['date_registered'] = $customer_user->get_date_created( 'edit' ) ? $customer_user->get_date_created( 'edit' )->date( TimeInterval::$sql_datetime_format ) : null;
734	$format[] = '%d';
735	$format[] = '%s';
736	$format[] = '%s';
737	}
738	return array( $data, $format );
739	}
740
741	/**
742	* Retrieve a guest ID (when user_id is null) by email.
743	*
744	* @param string $email Email address.
745	* @return false\|array Customer array if found, boolean false if not.
746	*/
747	public static function get_guest_id_by_email( $email ) {
748	global $wpdb;
749
750	$table_name = self::get_db_table_name();
751	$customer_id = $wpdb->get_var(
752	$wpdb->prepare(
753	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
754	"SELECT customer_id FROM {$table_name} WHERE email = %s AND user_id IS NULL LIMIT 1",
755	$email
756	)
757	);
758
759	return $customer_id ? (int) $customer_id : false;
760	}
761
762	/**
763	* Retrieve a customer ID by email address, regardless of user registration status.
764	* Prioritizes registered customers over guest customers when both exist.
765	*
766	* @param string $email Email address.
767	* @return false\|int Customer ID if found, boolean false if not.
768	*/
769	public static function get_customer_id_by_email( $email ) {
770	global $wpdb;
771
772	if ( empty( $email ) \|\| ! is_email( $email ) ) {
773	return false;
774	}
775
776	$table_name = self::get_db_table_name();
777	$customer_id = $wpdb->get_var(
778	$wpdb->prepare(
779	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
780	"SELECT customer_id FROM {$table_name} WHERE email = %s ORDER BY user_id IS NOT NULL DESC LIMIT 1",
781	$email
782	)
783	);
784
785	return $customer_id ? (int) $customer_id : false;
786	}
787
788	/**
789	* Retrieve a registered customer row id by user_id.
790	*
791	* @param string\|int $user_id User ID.
792	* @return false\|int Customer ID if found, boolean false if not.
793	*/
794	public static function get_customer_id_by_user_id( $user_id ) {
795	global $wpdb;
796
797	$table_name = self::get_db_table_name();
798	$customer_id = $wpdb->get_var(
799	$wpdb->prepare(
800	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
801	"SELECT customer_id FROM {$table_name} WHERE user_id = %d LIMIT 1",
802	$user_id
803	)
804	);
805
806	return $customer_id ? (int) $customer_id : false;
807	}
808
809	/**
810	* Retrieve the last order made by a customer.
811	*
812	* @param int $customer_id Customer ID.
813	* @return object WC_Order\|false.
814	*/
815	public static function get_last_order( $customer_id ) {
816	global $wpdb;
817	$orders_table = $wpdb->prefix . 'wc_order_stats';
818
819	$last_order = $wpdb->get_var(
820	$wpdb->prepare(
821	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
822	"SELECT order_id, date_created_gmt FROM {$orders_table}
823	WHERE customer_id = %d
824	ORDER BY date_created_gmt DESC, order_id DESC LIMIT 1",
825	// phpcs:enable
826	$customer_id
827	)
828	);
829	if ( ! $last_order ) {
830	return false;
831	}
832	return wc_get_order( absint( $last_order ) );
833	}
834
835	/**
836	* Retrieve the oldest orders made by a customer.
837	*
838	* @param int $customer_id Customer ID.
839	* @return array Orders.
840	*/
841	public static function get_oldest_orders( $customer_id ) {
842	global $wpdb;
843	$orders_table = $wpdb->prefix . 'wc_order_stats';
844	$excluded_statuses = array_map( array( __CLASS__, 'normalize_order_status' ), self::get_excluded_report_order_statuses() );
845	$excluded_statuses_condition = '';
846	if ( ! empty( $excluded_statuses ) ) {
847	$excluded_statuses_str = implode( "','", $excluded_statuses );
848	$excluded_statuses_condition = "AND status NOT IN ('{$excluded_statuses_str}')";
849	}
850
851	return $wpdb->get_results(
852	$wpdb->prepare(
853	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
854	"SELECT order_id, date_created FROM {$orders_table} WHERE customer_id = %d {$excluded_statuses_condition} ORDER BY date_created, order_id ASC LIMIT 2",
855	$customer_id
856	)
857	);
858	}
859
860	/**
861	* Retrieve the amount of orders made by a customer.
862	*
863	* @param int $customer_id Customer ID.
864	* @return int\|null Amount of orders for customer or null on failure.
865	*/
866	public static function get_order_count( $customer_id ) {
867	global $wpdb;
868	$customer_id = absint( $customer_id );
869
870	if ( 0 === $customer_id ) {
871	return null;
872	}
873
874	$result = $wpdb->get_var(
875	$wpdb->prepare(
876	"SELECT COUNT( order_id ) FROM {$wpdb->prefix}wc_order_stats WHERE customer_id = %d",
877	$customer_id
878	)
879	);
880
881	if ( is_null( $result ) ) {
882	return null;
883	}
884
885	return (int) $result;
886	}
887
888	/**
889	* Update the database with customer data.
890	*
891	* @param int $user_id WP User ID to update customer data for.
892	* @return int\|bool\|null Number or rows modified or false on failure.
893	*/
894	public static function update_registered_customer( $user_id ) {
895	global $wpdb;
896
897	$customer = new \WC_Customer( $user_id );
898
899	if ( ! self::is_valid_customer( $user_id ) ) {
900	return false;
901	}
902
903	$first_name = $customer->get_first_name();
904	$last_name = $customer->get_last_name();
905
906	if ( empty( $first_name ) ) {
907	$first_name = $customer->get_billing_first_name();
908	}
909	if ( empty( $last_name ) ) {
910	$last_name = $customer->get_billing_last_name();
911	}
912
913	$last_active = $customer->get_meta( 'wc_last_active', true, 'edit' );
914	$data = array(
915	'user_id' => $user_id,
916	'username' => $customer->get_username( 'edit' ),
917	'first_name' => $first_name,
918	'last_name' => $last_name,
919	'email' => $customer->get_email( 'edit' ),
920	'city' => $customer->get_billing_city( 'edit' ),
921	'state' => $customer->get_billing_state( 'edit' ),
922	'postcode' => $customer->get_billing_postcode( 'edit' ),
923	'country' => $customer->get_billing_country( 'edit' ),
924	'date_registered' => $customer->get_date_created( 'edit' ) ? $customer->get_date_created( 'edit' )->date( TimeInterval::$sql_datetime_format ) : null,
925	'date_last_active' => $last_active ? gmdate( 'Y-m-d H:i:s', $last_active ) : null,
926	);
927	$format = array(
928	'%d',
929	'%s',
930	'%s',
931	'%s',
932	'%s',
933	'%s',
934	'%s',
935	'%s',
936	'%s',
937	'%s',
938	'%s',
939	'%s',
940	);
941
942	$customer_id = self::get_customer_id_by_user_id( $user_id );
943
944	if ( $customer_id ) {
945	// Preserve customer_id for existing user_id.
946	$data['customer_id'] = $customer_id;
947	$format[] = '%d';
948	}
949
950	$results = $wpdb->replace( self::get_db_table_name(), $data, $format );
951
952	/**
953	* Fires when customser's reports are updated.
954	*
955	* @param int $customer_id Customer ID.
956	* @since 4.0.0
957	*/
958	do_action( 'woocommerce_analytics_update_customer', $customer_id );
959
960	ReportsCache::invalidate();
961
962	return $results;
963	}
964
965	/**
966	* Update the database if the "last active" meta value was changed.
967	* Function expects to be hooked into the `added_user_meta` and `updated_user_meta` actions.
968	*
969	* @param int $meta_id ID of updated metadata entry.
970	* @param int $user_id ID of the user being updated.
971	* @param string $meta_key Meta key being updated.
972	*/
973	public static function update_registered_customer_via_last_active( $meta_id, $user_id, $meta_key ) {
974	if ( 'wc_last_active' === $meta_key ) {
975	// Optimization note related to guarded updates in `wc_update_user_last_active`: the meta update will trigger
976	// this method execution. We evaluated adding `! doing_action( 'wp' )` here, but the performance gain lays
977	// in the micro-optimization area while exposing the Analytics to certain edge-cases.
978	self::update_registered_customer( $user_id );
979	}
980	}
981
982	/**
983	* Check if a user ID is a valid customer or other user role with past orders.
984	*
985	* @param int $user_id User ID.
986	* @return bool
987	*/
988	protected static function is_valid_customer( $user_id ) {
989	$user = new \WP_User( $user_id );
990
991	if ( (int) $user_id !== $user->ID ) {
992	return false;
993	}
994
995	/**
996	* Filter the customer roles, used to check if the user is a customer.
997	*
998	* @param array List of customer roles.
999	* @since 4.0.0
1000	*/
1001	$customer_roles = (array) apply_filters( 'woocommerce_analytics_customer_roles', array( 'customer' ) );
1002
1003	if ( empty( $user->roles ) \|\| empty( array_intersect( $user->roles, $customer_roles ) ) ) {
1004	return false;
1005	}
1006
1007	return true;
1008	}
1009
1010	/**
1011	* Delete a customer lookup row.
1012	*
1013	* @param int $customer_id Customer ID.
1014	*/
1015	public static function delete_customer( $customer_id ) {
1016	global $wpdb;
1017
1018	$customer_id = (int) $customer_id;
1019	$num_deleted = $wpdb->delete( self::get_db_table_name(), array( 'customer_id' => $customer_id ) );
1020
1021	if ( $num_deleted ) {
1022	/**
1023	* Fires when a customer is deleted.
1024	*
1025	* @param int $order_id Order ID.
1026	* @since 4.0.0
1027	*/
1028	do_action( 'woocommerce_analytics_delete_customer', $customer_id );
1029
1030	ReportsCache::invalidate();
1031	}
1032	}
1033
1034	/**
1035	* Delete a customer lookup row by WordPress User ID.
1036	*
1037	* @param int $user_id WordPress User ID.
1038	*/
1039	public static function delete_customer_by_user_id( $user_id ) {
1040	global $wpdb;
1041
1042	if ( (int) $user_id < 1 \|\| doing_action( 'wp_uninitialize_site' ) ) {
1043	// Skip the deletion.
1044	return;
1045	}
1046
1047	$user_id = (int) $user_id;
1048	$num_deleted = $wpdb->delete( self::get_db_table_name(), array( 'user_id' => $user_id ) );
1049
1050	if ( $num_deleted ) {
1051	ReportsCache::invalidate();
1052	}
1053	}
1054
1055	/**
1056	* Anonymize the customer data for a single order.
1057	*
1058	* @internal
1059	* @param int\|WC_Order $order Order instance or ID.
1060	* @return void
1061	*/
1062	public static function anonymize_customer( $order ) {
1063	global $wpdb;
1064
1065	if ( ! is_object( $order ) ) {
1066	$order = wc_get_order( absint( $order ) );
1067	}
1068
1069	$customer_id = $wpdb->get_var(
1070	$wpdb->prepare( "SELECT customer_id FROM {$wpdb->prefix}wc_order_stats WHERE order_id = %d", $order->get_id() )
1071	);
1072
1073	if ( ! $customer_id ) {
1074	return;
1075	}
1076
1077	// Long form query because $wpdb->update rejects [deleted].
1078	$deleted_text = __( '[deleted]', 'woocommerce' );
1079	$updated = $wpdb->query(
1080	$wpdb->prepare(
1081	"UPDATE {$wpdb->prefix}wc_customer_lookup
1082	SET
1083	user_id = NULL,
1084	username = %s,
1085	first_name = %s,
1086	last_name = %s,
1087	email = %s,
1088	country = '',
1089	postcode = %s,
1090	city = %s,
1091	state = %s
1092	WHERE
1093	customer_id = %d",
1094	array(
1095	$deleted_text,
1096	$deleted_text,
1097	$deleted_text,
1098	'deleted@site.invalid',
1099	$deleted_text,
1100	$deleted_text,
1101	$deleted_text,
1102	$customer_id,
1103	)
1104	)
1105	);
1106	// If the customer row was anonymized, flush the cache.
1107	if ( $updated ) {
1108	ReportsCache::invalidate();
1109	}
1110	}
1111
1112	/**
1113	* Build location filter SQL clause for includes or excludes.
1114	*
1115	* @since 10.5.0
1116	* @param string $locations_string Comma-separated list of locations (e.g., "US:CA,US:NY,GB").
1117	* @param bool $is_include True for IN clause, false for NOT IN clause.
1118	* @return string SQL WHERE clause condition.
1119	*/
1120	protected function build_location_filter_clause( $locations_string, $is_include = true ) {
1121	$customer_lookup_table = self::get_db_table_name();
1122	$locations_array = explode( ',', $locations_string );
1123	$country_state_pairs = array();
1124	$countries = array();
1125
1126	foreach ( $locations_array as $location ) {
1127	$location = trim( $location );
1128	if ( empty( $location ) ) {
1129	continue;
1130	}
1131
1132	if ( false !== strpos( $location, ':' ) ) {
1133	$parts = explode( ':', $location );
1134	if ( 2 === count( $parts ) ) {
1135	$country_state_pairs[] = array(
1136	'country' => esc_sql( $parts[0] ),
1137	'state' => esc_sql( $parts[1] ),
1138	);
1139	}
1140	} else {
1141	$countries[] = esc_sql( $location );
1142	}
1143	}
1144
1145	$conditions = array();
1146
1147	// Build country:state pair conditions.
1148	if ( ! empty( $country_state_pairs ) ) {
1149	$pair_conditions = array();
1150	foreach ( $country_state_pairs as $pair ) {
1151	if ( $is_include ) {
1152	$pair_conditions[] = "({$customer_lookup_table}.country = '{$pair['country']}' AND {$customer_lookup_table}.state = '{$pair['state']}')";
1153	} else {
1154	$pair_conditions[] = "({$customer_lookup_table}.country != '{$pair['country']}' OR {$customer_lookup_table}.state != '{$pair['state']}')";
1155	}
1156	}
1157	$pair_connector = $is_include ? ' OR ' : ' AND ';
1158	$conditions[] = '(' . implode( $pair_connector, $pair_conditions ) . ')';
1159	}
1160
1161	// Build country-only conditions.
1162	if ( ! empty( $countries ) ) {
1163	$operator = $is_include ? 'IN' : 'NOT IN';
1164	$conditions[] = "{$customer_lookup_table}.country {$operator} ('" . implode( "','", $countries ) . "')";
1165	}
1166
1167	if ( empty( $conditions ) ) {
1168	return '';
1169	}
1170
1171	// Combine conditions with OR for includes, AND for excludes.
1172	$connector = $is_include ? ' OR ' : ' AND ';
1173
1174	return '(' . implode( $connector, $conditions ) . ')';
1175	}
1176
1177	/**
1178	* Initialize query objects.
1179	*/
1180	protected function initialize_queries() {
1181	$this->clear_all_clauses();
1182	$table_name = self::get_db_table_name();
1183	$this->subquery = new SqlQuery( $this->context . '_subquery' );
1184	$this->subquery->add_sql_clause( 'from', $table_name );
1185	$this->subquery->add_sql_clause( 'select', "{$table_name}.customer_id" );
1186	$this->subquery->add_sql_clause( 'group_by', "{$table_name}.customer_id" );
1187	}
1188	}
1189