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

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

773 lines 26.4 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)
548	LIMIT {$limit}) allData
549	GROUP BY
550	allData.email
551	HAVING user_id IS NULL
552	ORDER BY
553	resource_id;";
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	if($this->hposEnabled) {
562	return $this->getTrackedGuestCustomersCountForHpos($lastTrackedGuest);
563	}
564	$query = "SELECT
565	count(email)
566	FROM (
567	SELECT
568	email,
569	max(user_id) AS user_id
570	FROM (
571	SELECT
572	wcl.user_id AS user_id,
573	max(
574	CASE WHEN wpm.meta_key = '_billing_email'
575	AND wpm.meta_value != '' THEN
576	wpm.meta_value
577	ELSE
578	wcl.email
579	END) AS email
580	FROM
581	{$this->wpdb->prefix}postmeta AS wpm
582	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.order_id = wpm.post_id
583	INNER JOIN {$this->wpdb->prefix}wc_customer_lookup wcl ON wcl.customer_id = wcos.customer_id
584	WHERE
585	wcos.status IN('wc-processing', 'wc-completed')
586	AND wcl.customer_id <= {$lastTrackedGuest}
587	GROUP BY
588	wcos.customer_id
589	ORDER BY
590	NULL) allData
591	GROUP BY
592	email) a
593	WHERE
594	user_id IS NULL";
595
596	return $this->wpdb->get_var($query);
597	}
598
599	public function getRegisteredCustomersToSyncForHpos($limit = 100)
600	{
601	$query = "SELECT
602	max(user_id) AS user_id,
603	min(resource_id) AS resource_id,
604	email,
605	CASE WHEN (
606	SELECT
607	wca.meta_value
608	FROM
609	{$this->wpdb->prefix}wc_orders_meta wca
610	WHERE
611	wca.meta_key = '_woo_ml_subscribe'
612	AND wca.order_id = max(allData.last_order_id)
613	LIMIT 1) THEN
614	TRUE
615	ELSE
616	FALSE
617	END AS create_subscriber,
618	sum(orders_count) AS orders_count,
619	sum(total_spent) AS total_spent,
620	max(name) AS name,
621	max(last_name) AS last_name,
622	max(company) AS company,
623	max(city) AS city,
624	max(state) AS state,
625	max(country) AS country,
626	max(phone) AS phone,
627	max(postcode) AS postcode,
628	max(last_order_id) AS last_order_id,
629	max(last_order) AS last_order
630	FROM (
631	SELECT
632	wcl.user_id AS user_id,
633	wcos.customer_id AS resource_id,
634	wcl.email,
635	count(DISTINCT (wcos.order_id)) AS orders_count,
636	sum(DISTINCT (wcos.net_total)) AS total_spent,
637	wcl.first_name AS name,
638	wcl.last_name AS last_name,
639	min(wca.company) AS company,
640	wcl.city AS city,
641	wcl.state AS state,
642	wcl.country AS country,
643	min(wca.phone) AS phone,
644	wcl.postcode AS postcode,
645	max(wcos.order_id) AS last_order_id,
646	max(wcos.date_created) AS last_order
647	FROM
648	{$this->wpdb->prefix}wc_orders_meta AS wpm
649	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.order_id = wpm.order_id
650	INNER JOIN {$this->wpdb->prefix}wc_customer_lookup wcl ON wcl.customer_id = wcos.customer_id
651	INNER JOIN {$this->wpdb->prefix}wc_order_addresses wca ON wca.order_id = wpm.order_id
652	LEFT JOIN {$this->wpdb->prefix}usermeta wum ON wum.user_id = wcl.user_id
653	AND wum.meta_key = '_woo_ml_customer_tracked'
654	WHERE
655	wcos.status IN('wc-processing', 'wc-completed')
656	AND wcl.user_id IS NOT NULL
657	AND wum.user_id IS NULL
658	AND wca.address_type = 'billing'
659	GROUP BY
660	wcos.customer_id
661	ORDER BY
662	max(wpm.order_id)
663	LIMIT {$limit}) allData
664	GROUP BY
665	allData.email
666	ORDER BY
667	resource_id;";
668	return $this->wpdb->get_results($query, 'ARRAY_A');
669	}
670
671	public function getGuestCustomersToSyncForHpos($lastTrackedGuest = 0, $limit = 100)
672	{
673	$query = "SELECT
674	max(user_id) as user_id,
675	min(resource_id) AS resource_id,
676	email,
677	CASE WHEN (
678	SELECT
679	wca.meta_value
680	FROM
681	{$this->wpdb->prefix}wc_orders_meta wca
682	WHERE
683	wca.meta_key = '_woo_ml_subscribe'
684	AND wca.order_id = max(allData.last_order_id)
685	LIMIT 1) THEN
686	TRUE
687	ELSE
688	FALSE
689	END AS create_subscriber,
690	sum(orders_count) AS orders_count,
691	sum(total_spent) AS total_spent,
692	max(name) AS name,
693	max(last_name) AS last_name,
694	max(company) AS company,
695	max(city) AS city,
696	max(state) AS state,
697	max(country) AS country,
698	max(phone) AS phone,
699	max(postcode) AS postcode,
700	max(last_order_id) AS last_order_id,
701	max(last_order) AS last_order
702	FROM (
703	SELECT
704	wcl.user_id AS user_id,
705	wcos.customer_id AS resource_id,
706	wcl.email,
707	count(DISTINCT (wcos.order_id)) AS orders_count,
708	sum(DISTINCT (wcos.net_total)) AS total_spent,
709	wcl.first_name AS name,
710	wcl.last_name AS last_name,
711	min(wca.company) AS company,
712	wcl.city AS city,
713	wcl.state AS state,
714	wcl.country AS country,
715	min(wca.phone) AS phone,
716	wcl.postcode AS postcode,
717	max(wcos.order_id) AS last_order_id,
718	max(wcos.date_created) AS last_order
719	FROM
720	{$this->wpdb->prefix}wc_orders_meta AS wpm
721	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.order_id = wpm.order_id
722	INNER JOIN {$this->wpdb->prefix}wc_customer_lookup wcl ON wcl.customer_id = wcos.customer_id
723	INNER JOIN {$this->wpdb->prefix}wc_order_addresses wca ON wca.order_id = wpm.order_id
724	WHERE
725	wcos.status IN('wc-processing', 'wc-completed')
726	AND wcl.customer_id > {$lastTrackedGuest}
727	AND wca.address_type = 'billing'
728	GROUP BY
729	wcos.customer_id,
730	email
731	ORDER BY max(wpm.order_id)
732	LIMIT {$limit}) allData
733	GROUP BY
734	allData.email
735	HAVING user_id IS NULL
736	ORDER BY
737	resource_id;";
738
739	return $this->wpdb->get_results($query, 'ARRAY_A');
740	}
741
742	public function getTrackedGuestCustomersCountForHpos($lastTrackedGuest)
743	{
744	$query = "SELECT
745	count(email)
746	FROM (
747	SELECT
748	email,
749	max(user_id) AS user_id
750	FROM (
751	SELECT
752	wcl.user_id AS user_id,
753	max(wca.email) AS email
754	FROM
755	{$this->wpdb->prefix}wc_orders_meta AS wpm
756	INNER JOIN {$this->wpdb->prefix}wc_order_addresses wca ON wca.order_id = wpm.order_id
757	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.order_id = wpm.order_id
758	INNER JOIN {$this->wpdb->prefix}wc_customer_lookup wcl ON wcl.customer_id = wcos.customer_id
759	WHERE
760	wcos.status IN('wc-processing', 'wc-completed')
761	AND wcl.customer_id <= {$lastTrackedGuest}
762	AND wca.address_type = 'billing'
763	GROUP BY
764	wcos.customer_id
765	ORDER BY
766	NULL) allData
767	GROUP BY
768	email) a
769	WHERE
770	user_id IS NULL;";
771	return $this->wpdb->get_var($query);
772	}
773	}