woo-mailerlite / includes / models / WooMailerLiteCustomer.php

WooMailerLiteCustomer.php in MailerLite – WooCommerce integration 3.1.27, at includes/models/WooMailerLiteCustomer.php

163 lines 6.4 KB

Raw Download Zip

1	<?php
2
3	class WooMailerLiteCustomer extends WooMailerLiteModel
4	{
5	/**
6	* @var string\|null
7	*/
8	protected $table = 'wc_customer_lookup';
9
10	protected $casts = [
11	'customer_id',
12	'resource_id',
13	'email',
14	'create_subscriber',
15	'accepts_marketing',
16	'orders_count',
17	'total_spent',
18	'name',
19	'last_name',
20	'company',
21	'city',
22	'state',
23	'country',
24	'zip',
25	'last_order_id',
26	'last_order',
27	'phone'
28	];
29
30	protected $format = [
31	'accepts_marketing' => 'boolean',
32	'create_subscriber' => 'boolean',
33	'resource_id' => 'string',
34	];
35
36	/**
37	* Get all customers
38	* @return mixed\|WooMailerLiteCollection\|null
39	*/
40	public static function getAll($limit = 100)
41	{
42	$prefix = db()->prefix;
43
44	$statesQuery = static::builder()->select("
45	{$prefix}wc_order_stats.customer_id,
46	{$prefix}wc_order_stats.customer_id as resource_id,
47	MAX({$prefix}wc_order_stats.order_id) AS last_order_id,
48	MAX({$prefix}wc_order_stats.date_created) AS last_order,
49	COUNT({$prefix}wc_order_stats.order_id) AS orders_count,
50	SUM({$prefix}wc_order_stats.total_sales) AS total_spent
51	")
52	->from("wc_order_stats")
53	->whereIn("status", WooMailerLiteOptions::COMPLETE_ORDER_STATUSES)
54	->groupBy("wc_order_stats.customer_id");
55
56	return static::builder()->select("
57	{$prefix}wc_customer_lookup.customer_id,
58	{$prefix}wc_customer_lookup.customer_id AS resource_id,
59	{$prefix}wc_customer_lookup.email,
60	CASE WHEN {$prefix}postmeta.meta_value IS NOT NULL THEN TRUE ELSE FALSE END AS accepts_marketing,
61	{$prefix}order_agg.orders_count,
62	{$prefix}order_agg.total_spent,
63	{$prefix}wc_customer_lookup.first_name AS name,
64	{$prefix}wc_customer_lookup.last_name,
65	{$prefix}wc_customer_lookup.city,
66	{$prefix}wc_customer_lookup.state,
67	{$prefix}wc_customer_lookup.country,
68	{$prefix}wc_customer_lookup.postcode AS zip,
69	COALESCE({$prefix}usermeta.meta_value, '') AS company,
70	COALESCE({$prefix}usermeta_phone.meta_value, '') AS phone,
71	{$prefix}order_agg.last_order_id,
72	{$prefix}order_agg.last_order,
73	CASE WHEN {$prefix}postmeta.meta_value IS NOT NULL THEN TRUE ELSE FALSE END AS create_subscriber
74	")
75	->join($statesQuery, 'order_agg.customer_id', 'wc_customer_lookup.customer_id', 'order_agg')
76	->leftJoin("postmeta", [
77	'postmeta.post_id' => 'order_agg.last_order_id',
78	'postmeta.meta_key' => '_woo_ml_subscribe'
79	])
80	->leftJoin("usermeta", [
81	'usermeta.user_id' => 'wc_customer_lookup.user_id',
82	'usermeta.meta_key' => 'billing_company'
83	])
84	->leftJoinAs("usermeta", "usermeta_phone", [
85	'usermeta_phone.user_id' => 'wc_customer_lookup.user_id',
86	'usermeta_phone.meta_key' => 'billing_phone'
87	])
88	->where('order_agg.customer_id', '>', WooMailerLiteOptions::get('lastSyncedCustomer', 0))
89	->orderBy('order_agg.customer_id')->get($limit);
90	}
91
92	public static function getUntrackedCustomersCount()
93	{
94	$prefix = db()->prefix;
95	return static::builder()->select("{$prefix}wc_customer_lookup.customer_id")
96	->from("wc_customer_lookup")
97	->join("wc_order_stats", [
98	'wc_order_stats.customer_id' => 'wc_customer_lookup.customer_id',
99	'wc_order_stats.status' => [
100	'in' => WooMailerLiteOptions::COMPLETE_ORDER_STATUSES
101	]
102	])
103	->where("wc_order_stats.customer_id", ">", WooMailerLiteOptions::get('lastSyncedCustomer', 0))
104	->groupBy("wc_customer_lookup.customer_id")
105	->orderBy('wc_customer_lookup.customer_id')
106	->count();
107	}
108
109	public static function selectAll($sync = true)
110	{
111	$prefix = db()->prefix;
112	$query = static::builder()->select("*,
113	CASE WHEN (
114	SELECT
115	wpm.meta_value
116	FROM
117	{$prefix}postmeta wpm
118	WHERE
119	wpm.meta_key = '_woo_ml_subscribe'
120	AND wpm.post_id = max({$prefix}wc_order_stats.order_id)
121	LIMIT 1) THEN
122	TRUE
123	ELSE
124	FALSE
125	END AS create_subscriber,
126	max({$prefix}wc_order_stats.order_id) AS last_order_id,
127	max({$prefix}wc_order_stats.date_created) AS last_order,
128	count(DISTINCT ({$prefix}wc_order_stats.order_id)) AS orders_count,
129	sum(({$prefix}wc_order_stats.total_sales)) AS total_spent,
130	MAX(COALESCE({$prefix}usermeta.meta_value, '')) AS company,
131	MAX(COALESCE({$prefix}usermeta_phone.meta_value, '')) AS phone")
132	->join('wc_order_stats', 'wc_order_stats.customer_id', 'wc_customer_lookup.customer_id')
133	->leftJoin("usermeta", [
134	'usermeta.user_id' => 'wc_customer_lookup.user_id',
135	'usermeta.meta_key' => 'billing_company'
136	])
137	->leftJoinAs("usermeta", "usermeta_phone", [
138	'usermeta_phone.user_id' => 'wc_customer_lookup.user_id',
139	'usermeta_phone.meta_key' => 'billing_phone'
140	])
141	->whereIn('wc_order_stats.status', WooMailerLiteOptions::COMPLETE_ORDER_STATUSES);
142	if (self::builder()->customTableEnabled() && $sync) {
143	$query->where('wc_order_stats.customer_id', '>', WooMailerLiteOptions::get('lastSyncedCustomer', 0));
144	$query->groupBy('wc_order_stats.customer_id, wp_wc_order_stats.order_id')
145	->orderBy('wc_order_stats.customer_id');
146	}
147
148	return $query;
149	}
150
151	public function markTracked()
152	{
153	WooMailerLiteOptions::update('lastSyncedOrder', $this->last_order_id);
154	WooMailerLiteOptions::update('lastSyncedCustomer', $this->customer_id);
155	}
156
157	public static function martUntracked()
158	{
159	WooMailerLiteOptions::update('lastSyncedOrder', 0);
160	WooMailerLiteOptions::update('lastSyncedCustomer', 0);
161	}
162	}
163