woo-mailerlite / includes / classes / data / TrackingData.php

TrackingData.php in MailerLite – WooCommerce integration 2.0.1, at includes/classes/data/TrackingData.php

730 lines 24.7 KB

Raw Download Zip

1	<?php
2
3	namespace MailerLite\Includes\Classes\Data;
4
5	use MailerLite\Includes\Classes\Settings\MailerLiteSettings;
6	use MailerLite\Includes\Classes\Settings\ShopSettings;
7	use MailerLite\Includes\Classes\Singleton;
8	use Automattic\WooCommerce\Utilities\OrderUtil;
9
10	class TrackingData extends Singleton
11	{
12	private $wpdb;
13
14	private $hposEnabled = false;
15	public function __construct()
16	{
17	global $wpdb;
18
19	$this->wpdb = $wpdb;
20	if ( OrderUtil::custom_orders_table_usage_is_enabled() ) {
21	$this->hposEnabled = true;
22	}
23
24	}
25
26
27	/**
28	* Get untracked product categories
29	* woo_ml_get_untracked_categories
30	* @return array
31	*/
32	public function getUntrackedCategories()
33	{
34
35	$term_args = array(
36	'taxonomy' => 'product_cat',
37	'hide_empty' => false,
38	'orderby' => 'none',
39	'meta_key' => '_woo_ml_category_tracked',
40	'meta_compare' => 'NOT EXISTS'
41	);
42
43	return get_terms($term_args);
44	}
45
46	/**
47	* Get untracked products
48	* woo_ml_get_untracked_products
49	*
50	* @param array $args
51	*
52	* @return array
53	*/
54	public function getUntrackedProducts($args = array())
55	{
56
57	$defaults = array(
58	'post_type' => 'product',
59	'posts_per_page' => 100,
60	'meta_key' => '_woo_ml_product_tracked',
61	'meta_compare' => 'NOT EXISTS'
62	);
63
64	$args = wp_parse_args($args, $defaults);
65	$product_posts_query = new \WP_Query($args);
66
67	$products = [];
68
69	if ($product_posts_query->have_posts()) {
70	$products = $product_posts_query->posts;
71	}
72
73	return $products;
74	}
75
76	/**
77	* Get tracked product categories count
78	* woo_ml_get_tracked_categories_count
79	* @return int
80	*/
81	public function getTrackedCategoriesCount()
82	{
83
84	$term_args = array(
85	'taxonomy' => 'product_cat',
86	'hide_empty' => false,
87	'orderby' => 'none',
88	'meta_key' => '_woo_ml_category_tracked',
89	'meta_compare' => 'EXISTS'
90	);
91
92	$categories = get_terms($term_args);
93
94	return count($categories);
95	}
96
97	/**
98	*
99	* woo_ml_count_untracked_products_count
100	* @return mixed
101	*/
102	public function getUntrackedProductsCount()
103	{
104	$defaults = array(
105	'post_type' => 'product',
106	'posts_per_page' => 1,
107	'meta_key' => '_woo_ml_product_tracked',
108	'meta_compare' => 'NOT EXISTS'
109	);
110
111	$args = wp_parse_args($defaults);
112	$products_query = new \WP_Query($args);
113
114	return $products_query->found_posts;
115	}
116
117	/**
118	*
119	* woo_ml_get_untracked_customers_count
120	* @return mixed
121	*/
122	public function getUntrackedCustomersCount()
123	{
124	$customer_query = new \WP_User_Query(
125	[
126	'fields' => 'ID',
127	'role__in' => ['customer', 'administrator'],
128	'meta_query' => [
129	'relation' => 'AND',
130	[
131	'relation' => 'OR',
132	[
133	'key' => 'last_order_date',
134	'compare' => 'EXISTS',
135	],
136	[
137	'key' => 'billing_last_name',
138	'compare' => 'EXISTS',
139	],
140	],
141	[
142	'key' => '_woo_ml_customer_tracked',
143	'compare' => 'NOT EXISTS'
144	]
145	],
146	'number' => 1,
147	]
148	);
149
150	return $customer_query->get_total();
151	}
152
153	/**
154	*
155	* woo_ml_get_tracked_products_count
156	* @return int
157	*/
158	public function getTrackedProductCount()
159	{
160	$defaults = array(
161	'post_type' => 'product',
162	'posts_per_page' => 100,
163	'meta_key' => '_woo_ml_product_tracked',
164	'meta_compare' => 'EXISTS'
165	);
166
167	$args = wp_parse_args($defaults);
168	$product_posts_query = new \WP_Query($args);
169
170	$product_posts = [];
171
172	if ($product_posts_query->have_posts()) {
173	$product_posts = $product_posts_query->get_posts();
174	}
175
176	return count($product_posts);
177	}
178
179	/**
180	*
181	* woo_ml_count_untracked_categories_count
182	* @return int
183	*/
184	public function getUntrackedCategoriesCount()
185	{
186	return count($this->getUntrackedCategories());
187	}
188
189	/**
190	*
191	* woo_ml_get_tracked_customers_count
192	* @return mixed
193	*/
194	public function getTrackedCustomersCount()
195	{
196	$query = "SELECT
197	count(DISTINCT wcl.customer_id)
198	FROM
199	{$this->wpdb->prefix}wc_customer_lookup wcl
200	INNER JOIN {$this->wpdb->prefix}usermeta wum ON wum.user_id = wcl.user_id
201	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos on wcos.customer_id = wcl.customer_id
202	WHERE wcl.user_id IS NOT NULL
203	AND wum.meta_key = '_woo_ml_customer_tracked'
204	AND wcos.status IN ('wc-processing', 'wc-completed');";
205
206	return $this->wpdb->get_var($query);
207	}
208
209	/**
210	*
211	* woo_ml_get_untracked_customers
212	* @return array\|array[]
213	*/
214	public function getUntrackedCustomers()
215	{
216
217	$customer_query = new \WP_User_Query(
218	[
219	'fields' => 'ID',
220	'role__in' => ['customer', 'administrator'],
221	'meta_query' => [
222	'relation' => 'AND',
223	[
224	'relation' => 'OR',
225	[
226	'key' => 'last_order_date',
227	'compare' => 'EXISTS',
228	],
229	[
230	'key' => 'billing_last_name',
231	'compare' => 'EXISTS',
232	],
233	],
234	[
235	'key' => '_woo_ml_customer_tracked',
236	'compare' => 'NOT EXISTS'
237	]
238	],
239	'number' => 100,
240	]
241	);
242
243	return array_map(function ($customer) {
244	return [
245	'id' => $customer
246	];
247	}, $customer_query->get_results());
248	}
249
250	/**
251	* Get settings page url
252	* woo_ml_get_settings_page_url
253	* @return string
254	*/
255	public function getSettingsPageUrl()
256	{
257	return admin_url('admin.php?page=wc-settings&tab=integration&section=mailerlite');
258	}
259
260	/**
261	* Get complete integration setup url
262	* woo_ml_get_complete_integration_setup_url
263	* @return string
264	*/
265	public function getCompleteIntegrationSetupUrl()
266	{
267	return add_query_arg('woo_ml_action', 'setup_integration', $this->getSettingsPageUrl());
268	}
269
270	/**
271	* Update ignore product list in ml_data table
272	* woo_ml_update_data
273	* @return mixed
274	*/
275	public function updateData($products)
276	{
277
278	$table = $this->wpdb->prefix . 'ml_data';
279
280	$tableCreated = get_option('ml_data_table');
281
282	if ($tableCreated != 1) {
283
284	MailerLiteSettings::getInstance()->createMailerDataTable();
285	}
286
287	$updateQuery = $this->wpdb->prepare("
288	INSERT INTO $table (data_name, data_value) VALUES ('products', %s) ON DUPLICATE KEY UPDATE data_value = %s
289	", json_encode($products), json_encode($products));
290
291	return $this->wpdb->query($updateQuery);
292	}
293
294	/**
295	* Save ignored products to WooCommerce Integration and ml_data table
296	* woo_ml_save_local_ignore_products
297	*/
298	public function saveLocalIgnoreProducts($products)
299	{
300
301	$ignore_map = MailerLiteSettings::getInstance()->remapList($products);
302
303	if (ShopSettings::getInstance()->updateIgnoreProductList($ignore_map) === true) {
304
305	// save updated ignore product list to WooCommerce Integration
306	$settings = get_option('woocommerce_mailerlite_settings');
307
308	if (!isset($settings['ignore_product_list'])) {
309	$settings['ignore_product_list'] = array();
310	}
311
312	$settings['ignore_product_list'] = $ignore_map;
313
314	update_option('woocommerce_mailerlite_settings', $settings);
315
316	//save product ignore list to ml_data
317	$this->updateData($products);
318	}
319	}
320
321	/**
322	* Remove product from product ignore list for ml_data
323	* woo_ml_remove_product_from_list
324	* @return array
325	*/
326	public function removeProductFromList($products, $remove_list)
327	{
328
329	return array_filter($products, function ($k) use ($remove_list) {
330	return !in_array($k, $remove_list);
331	}, ARRAY_FILTER_USE_KEY);
332	}
333
334	/**
335	*
336	* woo_ml_get_customers_count
337	* @return mixed
338	*/
339	public function getCustomersCount()
340	{
341
342	$query = "SELECT
343	sum(
344	CASE WHEN customer_id = 1 THEN 1 ELSE 1 END
345	) as count
346	FROM (
347	SELECT
348	count(DISTINCT wcl.customer_id) AS customer_id
349	FROM
350	{$this->wpdb->prefix}wc_customer_lookup wcl
351	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.customer_id = wcl.customer_id
352	WHERE
353	wcos.status IN('wc-processing', 'wc-completed')
354	GROUP BY
355	email) AS allCusotmers";
356
357	return $this->wpdb->get_var($query);
358	}
359
360	/**
361	*
362	* woo_ml_get_all_customers_count
363	* @return int
364	* @throws \Exception
365	*/
366	public function getAllCustomersCount()
367	{
368
369	$query = "SELECT
370	count(DISTINCT wcl.email)
371	FROM
372	{$this->wpdb->prefix}wc_customer_lookup wcl
373	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos on wcos.customer_id = wcl.customer_id
374	WHERE wcos.status IN ('wc-processing', 'wc-completed');";
375	return $this->wpdb->get_var($query);
376	}
377
378	/**
379	*
380	* woo_ml_get_all_customers
381	*
382	* @param $page
383	*
384	* @return array
385	* @throws \Exception
386	*/
387	public function getAllCustomers($page = 1)
388	{
389	$data_store = \WC_Data_Store::load('report-customers');
390
391	$customers = $data_store->get_data([
392	'per_page' => 100,
393	'page' => $page,
394	'order_before' => null,
395	'order_after' => null,
396	]);
397
398	if (isset($customers->pages)) {
399
400	if ($customers->pages >= $page) {
401
402	WC()->session->set('untracked_customer_page', $page + 1);
403	} else {
404
405	return [];
406	}
407	}
408
409	return $customers->data ?? [];
410	}
411
412	public function getRegisteredCustomersToSync($limit = 100)
413	{
414
415	if($this->hposEnabled) {
416	return $this->getRegisteredCustomersToSyncForHpos($limit);
417	}
418	$query = "SELECT
419	max(user_id) AS user_id,
420	min(resource_id) AS resource_id,
421	email,
422	CASE
423	WHEN (SELECT wpm.meta_value
424	FROM {$this->wpdb->prefix}postmeta wpm
425	WHERE wpm.meta_key = '_woo_ml_subscribe'
426	AND wpm.post_id = max(allData.last_order_id)
427	LIMIT 1) THEN TRUE ELSE FALSE END
428	AS create_subscriber,
429	sum(orders_count) AS orders_count,
430	sum(total_spent) AS total_spent,
431	max(name) AS name,
432	max(last_name) AS last_name,
433	max(company) AS company,
434	max(city) AS city,
435	max(state) AS state,
436	max(country) AS country,
437	max(phone) AS phone,
438	max(postcode) AS postcode,
439	max(last_order_id) AS last_order_id,
440	max(last_order) AS last_order
441	FROM (
442	SELECT
443	wcl.user_id AS user_id,
444	wcos.customer_id AS resource_id,
445	wcl.email,
446	count(DISTINCT (wcos.order_id)) AS orders_count,
447	sum(DISTINCT (wcos.net_total)) AS total_spent,
448	wcl.first_name AS name,
449	wcl.last_name AS last_name,
450	min(
451	CASE WHEN wpm.meta_key = '_billing_company' THEN
452	wpm.meta_value
453	END) AS company,
454	wcl.city AS city,
455	wcl.state AS state,
456	wcl.country AS country,
457	min(
458	CASE WHEN wpm.meta_key = '_billing_phone' THEN
459	wpm.meta_value
460	END) AS phone,
461	wcl.postcode AS postcode,
462	max(wcos.order_id) AS last_order_id,
463	max(wcos.date_created) AS last_order
464	FROM
465	{$this->wpdb->prefix}postmeta AS wpm
466	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.order_id = wpm.post_id
467	INNER JOIN {$this->wpdb->prefix}wc_customer_lookup wcl ON wcl.customer_id = wcos.customer_id
468	LEFT JOIN {$this->wpdb->prefix}usermeta wum ON wum.user_id = wcl.user_id
469	AND wum.meta_key = '_woo_ml_customer_tracked'
470	WHERE
471	wcos.status IN('wc-processing', 'wc-completed')
472	AND wcl.user_id IS NOT NULL
473	AND wum.user_id IS NULL
474	GROUP BY
475	wcos.customer_id
476	ORDER BY max(wpm.post_id)
477	LIMIT {$limit}) allData
478	GROUP BY
479	allData.email
480	ORDER BY
481	resource_id;";
482
483	return $this->wpdb->get_results($query, 'ARRAY_A');
484	}
485
486	public function getGuestCustomersToSync($lastTrackedGuest = 0, $limit = 100)
487	{
488	if($this->hposEnabled) {
489	return $this->getGuestCustomersToSyncForHpos($lastTrackedGuest, $limit);
490	}
491	$query = "SELECT
492	max(user_id) as user_id,
493	min(resource_id) AS resource_id,
494	email,
495	CASE
496	WHEN (SELECT wpm.meta_value
497	FROM {$this->wpdb->prefix}postmeta wpm
498	WHERE wpm.meta_key = '_woo_ml_subscribe'
499	AND wpm.post_id = max(allData.last_order_id)
500	LIMIT 1) THEN TRUE ELSE FALSE END
501	AS create_subscriber,
502	sum(orders_count) AS orders_count,
503	sum(total_spent) AS total_spent,
504	max(name) AS name,
505	max(last_name) AS last_name,
506	max(company) AS company,
507	max(city) AS city,
508	max(state) AS state,
509	max(country) AS country,
510	max(phone) AS phone,
511	max(postcode) AS postcode,
512	max(last_order_id) AS last_order_id,
513	max(last_order) AS last_order
514	FROM (
515	SELECT
516	wcl.user_id as user_id,
517	wcos.customer_id AS resource_id,
518	wcl.email,
519	count(DISTINCT (wcos.order_id)) AS orders_count,
520	sum(DISTINCT (wcos.net_total)) AS total_spent,
521	wcl.first_name AS name,
522	wcl.last_name AS last_name,
523	min(
524	CASE WHEN wpm.meta_key = '_billing_company' THEN
525	wpm.meta_value
526	END) AS company,
527	wcl.city AS city,
528	wcl.state AS state,
529	wcl.country AS country,
530	min(
531	CASE WHEN wpm.meta_key = '_billing_phone' THEN
532	wpm.meta_value
533	END) AS phone,
534	wcl.postcode AS postcode,
535	max(wcos.order_id) AS last_order_id,
536	max(wcos.date_created) AS last_order
537	FROM
538	{$this->wpdb->prefix}postmeta AS wpm
539	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.order_id = wpm.post_id
540	INNER JOIN {$this->wpdb->prefix}wc_customer_lookup wcl ON wcl.customer_id = wcos.customer_id
541	WHERE
542	wcos.status IN('wc-processing', 'wc-completed')
543	AND wcl.customer_id > {$lastTrackedGuest}
544	GROUP BY
545	wcos.customer_id,
546	email
547	ORDER BY max(wpm.post_id)) allData
548	GROUP BY
549	allData.email
550	HAVING user_id IS NULL
551	ORDER BY
552	resource_id
553	LIMIT {$limit};";
554
555	return $this->wpdb->get_results($query, 'ARRAY_A');
556	}
557
558	public function getTrackedGuestCustomersCount()
559	{
560	$lastTrackedGuest = get_option('woo_ml_last_synced_guest_id', 0);
561	$query = "SELECT
562	count(email)
563	FROM (
564	SELECT
565	email,
566	max(user_id) AS user_id
567	FROM (
568	SELECT
569	wcl.user_id AS user_id,
570	wcl.email
571	FROM
572	{$this->wpdb->prefix}wc_customer_lookup wcl
573	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.customer_id = wcl.customer_id
574	WHERE
575	wcos.status IN('wc-processing', 'wc-completed')
576	AND wcl.customer_id <= {$lastTrackedGuest}
577	GROUP BY
578	wcos.customer_id,
579	email) allData
580	GROUP BY
581	allData.email
582	HAVING
583	user_id IS NULL) a;";
584
585	return $this->wpdb->get_var($query);
586	}
587
588	public function getRegisteredCustomersToSyncForHpos($limit = 100)
589	{
590	$query = "SELECT
591	max(user_id) AS user_id,
592	min(resource_id) AS resource_id,
593	email,
594	CASE WHEN (
595	SELECT
596	wca.meta_value
597	FROM
598	{$this->wpdb->prefix}wc_orders_meta wca
599	WHERE
600	wca.meta_key = '_woo_ml_subscribe'
601	AND wca.order_id = max(allData.last_order_id)
602	LIMIT 1) THEN
603	TRUE
604	ELSE
605	FALSE
606	END AS create_subscriber,
607	sum(orders_count) AS orders_count,
608	sum(total_spent) AS total_spent,
609	max(name) AS name,
610	max(last_name) AS last_name,
611	max(company) AS company,
612	max(city) AS city,
613	max(state) AS state,
614	max(country) AS country,
615	max(phone) AS phone,
616	max(postcode) AS postcode,
617	max(last_order_id) AS last_order_id,
618	max(last_order) AS last_order
619	FROM (
620	SELECT
621	wcl.user_id AS user_id,
622	wcos.customer_id AS resource_id,
623	wcl.email,
624	count(DISTINCT (wcos.order_id)) AS orders_count,
625	sum(DISTINCT (wcos.net_total)) AS total_spent,
626	wcl.first_name AS name,
627	wcl.last_name AS last_name,
628	min(wca.company) AS company,
629	wcl.city AS city,
630	wcl.state AS state,
631	wcl.country AS country,
632	min(wca.phone) AS phone,
633	wcl.postcode AS postcode,
634	max(wcos.order_id) AS last_order_id,
635	max(wcos.date_created) AS last_order
636	FROM
637	{$this->wpdb->prefix}wc_orders_meta AS wpm
638	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.order_id = wpm.order_id
639	INNER JOIN {$this->wpdb->prefix}wc_customer_lookup wcl ON wcl.customer_id = wcos.customer_id
640	INNER JOIN {$this->wpdb->prefix}wc_order_addresses wca ON wca.order_id = wpm.order_id
641	LEFT JOIN {$this->wpdb->prefix}usermeta wum ON wum.user_id = wcl.user_id
642	AND wum.meta_key = '_woo_ml_customer_tracked'
643	WHERE
644	wcos.status IN('wc-processing', 'wc-completed')
645	AND wcl.user_id IS NOT NULL
646	AND wum.user_id IS NULL
647	AND wca.address_type = 'billing'
648	GROUP BY
649	wcos.customer_id
650	ORDER BY
651	max(wpm.order_id)
652	LIMIT {$limit}) allData
653	GROUP BY
654	allData.email
655	ORDER BY
656	resource_id;";
657	return $this->wpdb->get_results($query, 'ARRAY_A');
658	}
659
660	public function getGuestCustomersToSyncForHpos($lastTrackedGuest = 0, $limit = 100)
661	{
662	$query = "SELECT
663	max(user_id) as user_id,
664	min(resource_id) AS resource_id,
665	email,
666	CASE WHEN (
667	SELECT
668	wca.meta_value
669	FROM
670	{$this->wpdb->prefix}wc_orders_meta wca
671	WHERE
672	wca.meta_key = '_woo_ml_subscribe'
673	AND wca.order_id = max(allData.last_order_id)
674	LIMIT 1) THEN
675	TRUE
676	ELSE
677	FALSE
678	END AS create_subscriber,
679	sum(orders_count) AS orders_count,
680	sum(total_spent) AS total_spent,
681	max(name) AS name,
682	max(last_name) AS last_name,
683	max(company) AS company,
684	max(city) AS city,
685	max(state) AS state,
686	max(country) AS country,
687	max(phone) AS phone,
688	max(postcode) AS postcode,
689	max(last_order_id) AS last_order_id,
690	max(last_order) AS last_order
691	FROM (
692	SELECT
693	wcl.user_id AS user_id,
694	wcos.customer_id AS resource_id,
695	wcl.email,
696	count(DISTINCT (wcos.order_id)) AS orders_count,
697	sum(DISTINCT (wcos.net_total)) AS total_spent,
698	wcl.first_name AS name,
699	wcl.last_name AS last_name,
700	min(wca.company) AS company,
701	wcl.city AS city,
702	wcl.state AS state,
703	wcl.country AS country,
704	min(wca.phone) AS phone,
705	wcl.postcode AS postcode,
706	max(wcos.order_id) AS last_order_id,
707	max(wcos.date_created) AS last_order
708	FROM
709	{$this->wpdb->prefix}wc_orders_meta AS wpm
710	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.order_id = wpm.order_id
711	INNER JOIN {$this->wpdb->prefix}wc_customer_lookup wcl ON wcl.customer_id = wcos.customer_id
712	INNER JOIN {$this->wpdb->prefix}wc_order_addresses wca ON wca.order_id = wpm.order_id
713	WHERE
714	wcos.status IN('wc-processing', 'wc-completed')
715	AND wcl.customer_id > {$lastTrackedGuest}
716	AND wca.address_type = 'billing'
717	GROUP BY
718	wcos.customer_id,
719	email
720	ORDER BY max(wpm.order_id)
721	LIMIT {$limit}) allData
722	GROUP BY
723	allData.email
724	HAVING user_id IS NULL
725	ORDER BY
726	resource_id;";
727
728	return $this->wpdb->get_results($query, 'ARRAY_A');
729	}
730	}