send-users-email / includes / class-external-list-model.php

class-external-list-model.php in Send Users Email – Email Subscribers, Email Marketing Newsletter trunk, at includes/class-external-list-model.php

347 lines 11.0 KB

Raw Download Zip

1	<?php
2	class External_List_Model
3	{
4	public static function insert_data($data = [])
5	{
6	global $wpdb;
7
8	$table_name = External_Lists_Schema::get_external_list_table_name();
9
10	return $wpdb->insert(
11	$table_name,
12	[
13	'list_id' => $data['list_id'],
14	'user_id' => $data['user_id'],
15	'email' => $data['email'],
16	'first_name' => $data['first_name'] ?? '',
17	'last_name' => $data['last_name'] ?? '',
18	'title' => $data['title'] ?? '',
19	'salutation' => $data['salutation'] ?? '',
20	'field_01' => $data['field_01'] ?? '',
21	'field_02' => $data['field_02'] ?? '',
22	'field_03' => $data['field_03'] ?? '',
23	'field_04' => $data['field_04'] ?? '',
24	'field_05' => $data['field_05'] ?? '',
25	'subscribed' => $data['subscribed'] ?? 1,
26	'created_at' => current_time('mysql'),
27	]
28	);
29	}
30
31	public static function insert_metadata($data = [])
32	{
33	global $wpdb;
34
35	$table_name = External_Lists_Schema::get_external_list_meta_table_name();
36
37	return $wpdb->insert(
38	$table_name,
39	[
40	'user_id' => $data['user_id'],
41	'list_id' => $data['list_id'] ?? '',
42	'list_name' => $data['list_name'],
43	'count' => $data['count'],
44	'created_at' => current_time('mysql'),
45	]
46	);
47	}
48
49	public static function email_exists($email)
50	{
51	global $wpdb;
52
53	$table_name = External_Lists_Schema::get_external_list_table_name();
54
55	$query = $wpdb->prepare("SELECT COUNT(*) FROM $table_name WHERE email = %s", $email);
56	$count = $wpdb->get_var($query);
57
58	return $count > 0;
59	}
60
61	public static function get_list_count($list_id)
62	{
63	global $wpdb;
64
65	$table_name = External_Lists_Schema::get_external_list_meta_table_name();
66	$sql = "SELECT count FROM $table_name WHERE list_id = %s ORDER BY created_at DESC LIMIT 1";
67
68	$query = $wpdb->prepare($sql, $list_id);
69	$count = $wpdb->get_var($query);
70
71	return $count;
72	}
73
74	public static function get_all_lists_meta()
75	{
76	global $wpdb;
77
78	$table_name = External_Lists_Schema::get_external_list_meta_table_name();
79
80	$query = "SELECT * FROM $table_name ORDER BY created_at DESC";
81	$results = $wpdb->get_results($query);
82
83	return $results;
84	}
85
86	public static function get_all_lists()
87	{
88	global $wpdb;
89
90	$table_name = External_Lists_Schema::get_external_list_table_name();
91
92	$query = "SELECT * FROM $table_name ORDER BY created_at DESC";
93	$results = $wpdb->get_results($query);
94
95	return $results;
96	}
97
98	/**
99	* Summary of get_all_external_lists_by_listid
100	* Get all data in external list table
101	* Supports pagination using offset and limit.
102	* @param mixed $list_id
103	* @return void
104	*/
105	public static function get_all_external_lists_by_listid($list_id, $limit = 10, $offset = 0)
106	{
107	global $wpdb;
108
109	$list_table = External_Lists_Schema::get_external_list_table_name();
110	// Prepare the SQL query with placeholders for list_id, offset, and limit
111	$sql = "SELECT * FROM $list_table WHERE list_id = %s ORDER BY created_at DESC LIMIT %d OFFSET %d";
112	// Set the offset and limit values for pagination
113	$query = $wpdb->prepare($sql, $list_id, $limit, $offset);
114	// Execute the query and retrieve the results
115	$results = $wpdb->get_results($query);
116	return $results;
117	}
118
119	/**
120	* Summary of count_total_external_lists_by_listid
121	* Count total records in external list table by list_id
122	* Supports pagination using offset and limit.
123	* @param mixed $list_id
124	* @return int
125	*/
126	public static function count_total_external_lists_by_listid($list_id)
127	{
128	global $wpdb;
129	$list_table = External_Lists_Schema::get_external_list_table_name();
130
131	$sql = "SELECT COUNT(*) FROM $list_table WHERE list_id = %s";
132	$query = $wpdb->prepare($sql, $list_id);
133	$count = $wpdb->get_var($query);
134	return (int)$count;
135	}
136
137	/**
138	* Join the two table to get list details along with count
139	**/
140	public static function get_lists_with_counts()
141	{
142	global $wpdb;
143
144	$list_table = External_Lists_Schema::get_external_list_table_name();
145	$meta_table = External_Lists_Schema::get_external_list_meta_table_name();
146
147	$query = "
148	SELECT
149	$meta_table.id,
150	$meta_table.list_id,
151	$meta_table.list_name,
152	COUNT($list_table.id) AS total_users,
153	SUM(CASE WHEN $list_table.subscribed = 1 THEN 1 ELSE 0 END) AS subscribed_users,
154	SUM(CASE WHEN $list_table.subscribed = 0 THEN 1 ELSE 0 END) AS unsubscribed_users
155	FROM $meta_table
156	LEFT JOIN $list_table ON $list_table.list_id = $meta_table.list_id
157	GROUP BY $meta_table.id, $meta_table.list_id, $meta_table.list_name, $meta_table.created_at
158	ORDER BY $meta_table.created_at DESC
159	";
160
161	$results = $wpdb->get_results($query);
162
163	return $results;
164	}
165
166	public static function get_lists_by_listid($list_id)
167	{
168	global $wpdb;
169
170	$table_name = External_Lists_Schema::get_external_list_meta_table_name();
171
172	$query = $wpdb->prepare("SELECT * FROM $table_name WHERE list_id = %s ORDER BY created_at DESC", $list_id);
173	$results = $wpdb->get_results($query);
174
175	return $results;
176	}
177
178	public static function get_lists_by_id($id)
179	{
180	global $wpdb;
181
182	$table_name = External_Lists_Schema::get_external_list_meta_table_name();
183
184	$query = $wpdb->prepare("SELECT * FROM $table_name WHERE id = %d", $id);
185	$result = $wpdb->get_row($query);
186
187	return $result;
188	}
189
190	public static function get_list_meta_query_by_id($id = [])
191	{
192	global $wpdb;
193
194	$list_table = External_Lists_Schema::get_external_list_table_name();
195	$meta_table = External_Lists_Schema::get_external_list_meta_table_name();
196
197	$str_where = '';
198	if (!empty($id)) {
199	$str_where = 'WHERE ' . $meta_table . '.id IN (' . implode(',', array_map('intval', $id)) . ')';
200	}
201
202	$str_query = "
203	SELECT
204	$list_table.id,
205	$list_table.list_id,
206	$list_table.email,
207	$list_table.first_name,
208	$list_table.last_name,
209	$list_table.title,
210	$list_table.salutation,
211	$list_table.field_01,
212	$list_table.field_02,
213	$list_table.field_03,
214	$list_table.field_04,
215	$list_table.field_05,
216	$list_table.subscribed,
217	$meta_table.id as meta_id,
218	$meta_table.list_id as meta_list_id
219	FROM $meta_table
220	LEFT JOIN $list_table ON $list_table.list_id = $meta_table.list_id
221	$str_where
222	";
223
224	$results = $wpdb->get_results($str_query);
225
226	return $results;
227	}
228
229	public static function get_list_meta_query_by_listid($list_id)
230	{
231	global $wpdb;
232
233	$list_table = External_Lists_Schema::get_external_list_table_name();
234	$meta_table = External_Lists_Schema::get_external_list_meta_table_name();
235
236	$query = sue_db_prepare("
237	SELECT
238	$list_table.id,
239	$list_table.list_id,
240	$list_table.email,
241	$list_table.first_name,
242	$list_table.last_name,
243	$list_table.title,
244	$list_table.salutation,
245	$list_table.field_01,
246	$list_table.field_02,
247	$list_table.field_03,
248	$list_table.field_04,
249	$list_table.field_05,
250	$list_table.subscribed,
251	$meta_table.id as meta_id,
252	$meta_table.list_id as meta_list_id
253	FROM $meta_table
254	LEFT JOIN $list_table ON $list_table.list_id = $meta_table.list_id
255	WHERE $meta_table.list_id = %s
256	", $list_id);
257
258	$results = $wpdb->get_results($query);
259
260	return $results;
261	}
262
263	public static function get_list_meta_query_by_email($email)
264	{
265	global $wpdb;
266
267	$list_table = External_Lists_Schema::get_external_list_table_name();
268	$meta_table = External_Lists_Schema::get_external_list_meta_table_name();
269
270	$query = sue_db_prepare("
271	SELECT
272	$list_table.id,
273	$list_table.list_id,
274	$list_table.email,
275	$list_table.first_name,
276	$list_table.last_name,
277	$list_table.title,
278	$list_table.salutation,
279	$list_table.field_01,
280	$list_table.field_02,
281	$list_table.field_03,
282	$list_table.field_04,
283	$list_table.field_05,
284	$list_table.subscribed,
285	$meta_table.id as meta_id,
286	$meta_table.list_id as meta_list_id,
287	$meta_table.list_name as meta_list_name
288	FROM $meta_table
289	LEFT JOIN $list_table ON $list_table.list_id = $meta_table.list_id
290	WHERE $list_table.email = %s
291	", $email);
292
293	$results = $wpdb->get_results($query);
294
295	return $results;
296	}
297
298	public static function update_list($id, $data = [])
299	{
300	global $wpdb;
301
302	$table_name = External_Lists_Schema::get_external_list_table_name();
303
304	$update_data = [];
305	$update_format = [];
306
307	foreach ($data as $key => $value) {
308	$update_data[$key] = $value;
309	$update_format[] = '%s';
310	}
311
312	return $wpdb->update(
313	$table_name,
314	$update_data,
315	['id' => $id],
316	$update_format,
317	['%d']
318	);
319	}
320
321	public static function delete_list($id)
322	{
323	global $wpdb;
324
325	$table_name = External_Lists_Schema::get_external_list_table_name();
326
327	return $wpdb->delete(
328	$table_name,
329	['id' => $id],
330	['%d']
331	);
332	}
333
334	public static function delete_list_by_list_id($list_id)
335	{
336	global $wpdb;
337
338	$table_name = External_Lists_Schema::get_external_list_table_name();
339
340	return $wpdb->delete(
341	$table_name,
342	['list_id' => $list_id],
343	['%d']
344	);
345
346	}
347	}