woo-mailerlite / includes / models / WooMailerLiteCustomer.php

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

148 lines 5.7 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' => WooMailerLiteOptions::COMPLETE_ORDER_STATUSES
96	]
97	])
98	->where("wc_order_stats.customer_id", ">", WooMailerLiteOptions::get('lastSyncedCustomer', 0))
99	->groupBy("wc_customer_lookup.customer_id")
100	->orderBy('wc_customer_lookup.customer_id')
101	->count();
102	}
103
104	public static function selectAll($sync = true)
105	{
106	$prefix = db()->prefix;
107	$query = static::builder()->select("*,
108	CASE WHEN (
109	SELECT
110	wpm.meta_value
111	FROM
112	{$prefix}postmeta wpm
113	WHERE
114	wpm.meta_key = '_woo_ml_subscribe'
115	AND wpm.post_id = max({$prefix}wc_order_stats.order_id)
116	LIMIT 1) THEN
117	TRUE
118	ELSE
119	FALSE
120	END AS create_subscriber,
121	max({$prefix}wc_order_stats.order_id) AS last_order_id,
122	max({$prefix}wc_order_stats.date_created) AS last_order,
123	count(DISTINCT ({$prefix}wc_order_stats.order_id)) AS orders_count,
124	sum(({$prefix}wc_order_stats.total_sales)) AS total_spent")
125	->join('wc_order_stats', 'wc_order_stats.customer_id', 'wc_customer_lookup.customer_id')
126	->whereIn('wc_order_stats.status', WooMailerLiteOptions::COMPLETE_ORDER_STATUSES);
127	if (self::builder()->customTableEnabled() && $sync) {
128	$query->where('wc_order_stats.customer_id', '>', WooMailerLiteOptions::get('lastSyncedCustomer', 0));
129	$query->groupBy('wc_order_stats.customer_id, wp_wc_order_stats.order_id')
130	->orderBy('wc_order_stats.customer_id');
131	}
132
133	return $query;
134	}
135
136	public function markTracked()
137	{
138	WooMailerLiteOptions::update('lastSyncedOrder', $this->last_order_id);
139	WooMailerLiteOptions::update('lastSyncedCustomer', $this->customer_id);
140	}
141
142	public static function martUntracked()
143	{
144	WooMailerLiteOptions::update('lastSyncedOrder', 0);
145	WooMailerLiteOptions::update('lastSyncedCustomer', 0);
146	}
147	}
148