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

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

737 lines 25.3 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 (class_exists('OrderUtil') && 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	INNER JOIN {$this->wpdb->prefix}users u on u.ID = wcl.user_id
469	LEFT JOIN {$this->wpdb->prefix}usermeta wum ON wum.user_id = wcl.user_id
470	AND wum.meta_key = '_woo_ml_customer_tracked'
471	WHERE
472	wcos.status IN('wc-processing', 'wc-completed')
473	AND wcl.user_id IS NOT NULL
474	AND wum.user_id IS NULL
475	GROUP BY
476	wcos.customer_id
477	ORDER BY max(wpm.post_id)
478	LIMIT {$limit}) allData
479	GROUP BY
480	allData.email
481	ORDER BY
482	resource_id;";
483
484	return $this->wpdb->get_results($query, 'ARRAY_A');
485	}
486
487	public function getGuestCustomersToSync($lastTrackedGuest = 0, $limit = 100)
488	{
489	if($this->hposEnabled) {
490	return $this->getGuestCustomersToSyncForHpos($lastTrackedGuest, $limit);
491	}
492	$query = "SELECT
493	max(user_id) as user_id,
494	min(resource_id) AS resource_id,
495	email,
496	CASE
497	WHEN (SELECT wpm.meta_value
498	FROM {$this->wpdb->prefix}postmeta wpm
499	WHERE wpm.meta_key = '_woo_ml_subscribe'
500	AND wpm.post_id = max(allData.last_order_id)
501	LIMIT 1) THEN TRUE ELSE FALSE END
502	AS create_subscriber,
503	sum(orders_count) AS orders_count,
504	sum(total_spent) AS total_spent,
505	max(name) AS name,
506	max(last_name) AS last_name,
507	max(company) AS company,
508	max(city) AS city,
509	max(state) AS state,
510	max(country) AS country,
511	max(phone) AS phone,
512	max(postcode) AS postcode,
513	max(last_order_id) AS last_order_id,
514	max(last_order) AS last_order,
515	max(users_table_id) as users_table_id
516	FROM (
517	SELECT
518	wcl.user_id as user_id,
519	wcos.customer_id AS resource_id,
520	wcl.email,
521	count(DISTINCT (wcos.order_id)) AS orders_count,
522	sum(DISTINCT (wcos.net_total)) AS total_spent,
523	wcl.first_name AS name,
524	wcl.last_name AS last_name,
525	min(
526	CASE WHEN wpm.meta_key = '_billing_company' THEN
527	wpm.meta_value
528	END) AS company,
529	wcl.city AS city,
530	wcl.state AS state,
531	wcl.country AS country,
532	min(
533	CASE WHEN wpm.meta_key = '_billing_phone' THEN
534	wpm.meta_value
535	END) AS phone,
536	wcl.postcode AS postcode,
537	max(wcos.order_id) AS last_order_id,
538	max(wcos.date_created) AS last_order,
539	max(u.ID) as users_table_id
540	FROM
541	{$this->wpdb->prefix}postmeta AS wpm
542	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.order_id = wpm.post_id
543	INNER JOIN {$this->wpdb->prefix}wc_customer_lookup wcl ON wcl.customer_id = wcos.customer_id
544	LEFT JOIN {$this->wpdb->prefix}users u ON u.ID = wcl.user_id
545	WHERE
546	wcos.status IN('wc-processing', 'wc-completed')
547	AND wcl.customer_id > {$lastTrackedGuest}
548	GROUP BY
549	wcos.customer_id,
550	email
551	ORDER BY max(wpm.post_id)) allData
552	WHERE allData.users_table_id IS NULL
553	GROUP BY
554	allData.email
555	ORDER BY
556	resource_id
557	LIMIT {$limit};";
558
559	return $this->wpdb->get_results($query, 'ARRAY_A');
560	}
561
562	public function getTrackedGuestCustomersCount()
563	{
564	$lastTrackedGuest = get_option('woo_ml_last_synced_guest_id', 0);
565	$query = "SELECT
566	count(email)
567	FROM (
568	SELECT
569	email,
570	max(users_table_id)
571	FROM (
572	SELECT
573	wcl.user_id AS user_id,
574	wcl.email,
575	u.ID as users_table_id
576	FROM
577	{$this->wpdb->prefix}wc_customer_lookup wcl
578	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.customer_id = wcl.customer_id
579	LEFT JOIN {$this->wpdb->prefix}users as u on u.id = wcl.user_id
580	WHERE
581	wcos.status IN('wc-processing', 'wc-completed')
582	AND wcl.customer_id <= {$lastTrackedGuest}
583	GROUP BY
584	wcos.customer_id,
585	email) allData
586	WHERE allData.users_table_id IS NULL
587	GROUP BY
588	allData.email) a;";
589
590	return $this->wpdb->get_var($query);
591	}
592
593	public function getRegisteredCustomersToSyncForHpos($limit = 100)
594	{
595	$query = "SELECT
596	max(user_id) AS user_id,
597	min(resource_id) AS resource_id,
598	email,
599	CASE WHEN (
600	SELECT
601	wca.meta_value
602	FROM
603	{$this->wpdb->prefix}wc_orders_meta wca
604	WHERE
605	wca.meta_key = '_woo_ml_subscribe'
606	AND wca.order_id = max(allData.last_order_id)
607	LIMIT 1) THEN
608	TRUE
609	ELSE
610	FALSE
611	END AS create_subscriber,
612	sum(orders_count) AS orders_count,
613	sum(total_spent) AS total_spent,
614	max(name) AS name,
615	max(last_name) AS last_name,
616	max(company) AS company,
617	max(city) AS city,
618	max(state) AS state,
619	max(country) AS country,
620	max(phone) AS phone,
621	max(postcode) AS postcode,
622	max(last_order_id) AS last_order_id,
623	max(last_order) AS last_order
624	FROM (
625	SELECT
626	wcl.user_id AS user_id,
627	wcos.customer_id AS resource_id,
628	wcl.email,
629	count(DISTINCT (wcos.order_id)) AS orders_count,
630	sum(DISTINCT (wcos.net_total)) AS total_spent,
631	wcl.first_name AS name,
632	wcl.last_name AS last_name,
633	min(wca.company) AS company,
634	wcl.city AS city,
635	wcl.state AS state,
636	wcl.country AS country,
637	min(wca.phone) AS phone,
638	wcl.postcode AS postcode,
639	max(wcos.order_id) AS last_order_id,
640	max(wcos.date_created) AS last_order
641	FROM
642	{$this->wpdb->prefix}wc_orders_meta AS wpm
643	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.order_id = wpm.order_id
644	INNER JOIN {$this->wpdb->prefix}wc_customer_lookup wcl ON wcl.customer_id = wcos.customer_id
645	INNER JOIN {$this->wpdb->prefix}wc_order_addresses wca ON wca.order_id = wpm.order_id
646	INNER JOIN {$this->wpdb->prefix}users u on u.ID = wcl.user_id
647	LEFT JOIN {$this->wpdb->prefix}usermeta wum ON wum.user_id = wcl.user_id
648	AND wum.meta_key = '_woo_ml_customer_tracked'
649	WHERE
650	wcos.status IN('wc-processing', 'wc-completed')
651	AND wcl.user_id IS NOT NULL
652	AND wum.user_id IS NULL
653	AND wca.address_type = 'billing'
654	GROUP BY
655	wcos.customer_id
656	ORDER BY
657	max(wpm.order_id)
658	LIMIT {$limit}) allData
659	GROUP BY
660	allData.email
661	ORDER BY
662	resource_id;";
663	return $this->wpdb->get_results($query, 'ARRAY_A');
664	}
665
666	public function getGuestCustomersToSyncForHpos($lastTrackedGuest = 0, $limit = 100)
667	{
668	$query = "SELECT
669	max(user_id) as user_id,
670	min(resource_id) AS resource_id,
671	email,
672	CASE WHEN (
673	SELECT
674	wca.meta_value
675	FROM
676	{$this->wpdb->prefix}wc_orders_meta wca
677	WHERE
678	wca.meta_key = '_woo_ml_subscribe'
679	AND wca.order_id = max(allData.last_order_id)
680	LIMIT 1) THEN
681	TRUE
682	ELSE
683	FALSE
684	END AS create_subscriber,
685	sum(orders_count) AS orders_count,
686	sum(total_spent) AS total_spent,
687	max(name) AS name,
688	max(last_name) AS last_name,
689	max(company) AS company,
690	max(city) AS city,
691	max(state) AS state,
692	max(country) AS country,
693	max(phone) AS phone,
694	max(postcode) AS postcode,
695	max(last_order_id) AS last_order_id,
696	max(last_order) AS last_order
697	FROM (
698	SELECT
699	wcl.user_id AS user_id,
700	wcos.customer_id AS resource_id,
701	wcl.email,
702	count(DISTINCT (wcos.order_id)) AS orders_count,
703	sum(DISTINCT (wcos.net_total)) AS total_spent,
704	wcl.first_name AS name,
705	wcl.last_name AS last_name,
706	min(wca.company) AS company,
707	wcl.city AS city,
708	wcl.state AS state,
709	wcl.country AS country,
710	min(wca.phone) AS phone,
711	wcl.postcode AS postcode,
712	max(wcos.order_id) AS last_order_id,
713	max(wcos.date_created) AS last_order
714	FROM
715	{$this->wpdb->prefix}wc_orders_meta AS wpm
716	INNER JOIN {$this->wpdb->prefix}wc_order_stats wcos ON wcos.order_id = wpm.order_id
717	INNER JOIN {$this->wpdb->prefix}wc_customer_lookup wcl ON wcl.customer_id = wcos.customer_id
718	INNER JOIN {$this->wpdb->prefix}wc_order_addresses wca ON wca.order_id = wpm.order_id
719	LEFT JOIN {$this->wpdb->prefix}users on u.ID = wcl.user_id
720	WHERE
721	wcos.status IN('wc-processing', 'wc-completed')
722	AND wcl.customer_id > {$lastTrackedGuest}
723	AND wca.address_type = 'billing'
724	AND u.ID IS NULL
725	GROUP BY
726	wcos.customer_id,
727	email
728	ORDER BY max(wpm.order_id)) allData
729	GROUP BY
730	allData.email
731	ORDER BY
732	resource_id
733	LIMIT {$limit};";
734
735	return $this->wpdb->get_results($query, 'ARRAY_A');
736	}
737	}