woo-mailerlite / includes / models / WooMailerLiteCustomer.php

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

150 lines 5.8 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	];
28
29	protected $format = [
30	'accepts_marketing' => 'boolean',
31	'create_subscriber' => 'boolean',
32	'resource_id' => 'string',
33	];
34
35	/**
36	* Get all customers
37	* @return mixed\|WooMailerLiteCollection\|null
38	*/
39	public static function getAll($limit = 100)
40	{
41	$prefix = db()->prefix;
42
43	$statesQuery = static::builder()->select("
44	{$prefix}wc_order_stats.customer_id,
45	{$prefix}wc_order_stats.customer_id as resource_id,
46	MAX({$prefix}wc_order_stats.order_id) AS last_order_id,
47	MAX({$prefix}wc_order_stats.date_created) AS last_order,
48	COUNT({$prefix}wc_order_stats.order_id) AS orders_count,
49	SUM({$prefix}wc_order_stats.total_sales) AS total_spent
50	")
51	->from("wc_order_stats")
52	->whereIn("status", WooMailerLiteOptions::COMPLETE_ORDER_STATUSES)
53	->groupBy("wc_order_stats.customer_id");
54
55	return static::builder()->select("
56	{$prefix}wc_customer_lookup.customer_id,
57	{$prefix}wc_customer_lookup.customer_id AS resource_id,
58	{$prefix}wc_customer_lookup.email,
59	CASE WHEN {$prefix}postmeta.meta_value IS NOT NULL THEN TRUE ELSE FALSE END AS create_subscriber,
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	{$prefix}order_agg.last_order_id,
71	{$prefix}order_agg.last_order,
72	CASE WHEN {$prefix}postmeta.meta_value IS NOT NULL THEN TRUE ELSE FALSE END AS create_subscriber
73	")
74	->join($statesQuery, 'order_agg.customer_id', 'wc_customer_lookup.customer_id', 'order_agg')
75	->leftJoin("postmeta", [
76	'postmeta.post_id' => 'order_agg.last_order_id',
77	'postmeta.meta_key' => '_woo_ml_subscribe'
78	])
79	->leftJoin("usermeta", [
80	'usermeta.user_id' => 'wc_customer_lookup.user_id',
81	'usermeta.meta_key' => 'billing_company'
82	])
83	->where('order_agg.customer_id', '>', WooMailerLiteOptions::get('lastSyncedCustomer', 0))
84	->orderBy('order_agg.customer_id')->get($limit);
85	}
86
87	public static function getUntrackedCustomersCount()
88	{
89	$prefix = db()->prefix;
90	return static::builder()->select("{$prefix}wc_customer_lookup.customer_id")
91	->from("wc_customer_lookup")
92	->join("wc_order_stats", [
93	'wc_order_stats.customer_id' => 'wc_customer_lookup.customer_id',
94	'wc_order_stats.status' => [
95	'in' => [
96	WooMailerLiteOptions::COMPLETE_ORDER_STATUSES
97	]
98	]
99	])
100	->where("wc_order_stats.customer_id", ">", WooMailerLiteOptions::get('lastSyncedCustomer', 0))
101	->groupBy("wc_customer_lookup.customer_id")
102	->orderBy('wc_customer_lookup.customer_id')
103	->count();
104	}
105
106	public static function selectAll($sync = true)
107	{
108	$prefix = db()->prefix;
109	$query = static::builder()->select("*,
110	CASE WHEN (
111	SELECT
112	wpm.meta_value
113	FROM
114	{$prefix}postmeta wpm
115	WHERE
116	wpm.meta_key = '_woo_ml_subscribe'
117	AND wpm.post_id = max({$prefix}wc_order_stats.order_id)
118	LIMIT 1) THEN
119	TRUE
120	ELSE
121	FALSE
122	END AS create_subscriber,
123	max({$prefix}wc_order_stats.order_id) AS last_order_id,
124	max({$prefix}wc_order_stats.date_created) AS last_order,
125	count(DISTINCT ({$prefix}wc_order_stats.order_id)) AS orders_count,
126	sum(({$prefix}wc_order_stats.total_sales)) AS total_spent")
127	->join('wc_order_stats', 'wc_order_stats.customer_id', 'wc_customer_lookup.customer_id')
128	->whereIn('wc_order_stats.status', WooMailerLiteOptions::COMPLETE_ORDER_STATUSES);
129	if (self::builder()->customTableEnabled() && $sync) {
130	$query->where('wc_order_stats.customer_id', '>', WooMailerLiteOptions::get('lastSyncedCustomer', 0));
131	$query->groupBy('wc_order_stats.customer_id, wp_wc_order_stats.order_id')
132	->orderBy('wc_order_stats.customer_id');
133	}
134
135	return $query;
136	}
137
138	public function markTracked()
139	{
140	WooMailerLiteOptions::update('lastSyncedOrder', $this->last_order_id);
141	WooMailerLiteOptions::update('lastSyncedCustomer', $this->customer_id);
142	}
143
144	public static function martUntracked()
145	{
146	WooMailerLiteOptions::update('lastSyncedOrder', 0);
147	WooMailerLiteOptions::update('lastSyncedCustomer', 0);
148	}
149	}
150