acymailing / back / Classes / UrlClickClass.php

UrlClickClass.php in AcyMailing – An Ultimate Newsletter Plugin and Marketing Automation Solution for WordPress 10.0.1, at back/Classes/UrlClickClass.php

290 lines 12.0 KB

Raw Download Zip

1	<?php
2
3	namespace AcyMailing\Classes;
4
5	use AcyMailing\Core\AcymClass;
6	use AcyMailing\Helpers\ScenarioHelper;
7
8
9	class UrlClickClass extends AcymClass
10	{
11	public function __construct()
12	{
13	parent::__construct();
14
15	$this->table = 'url_click';
16	}
17
18	public function save($urlClick)
19	{
20	$column = [];
21	$valueColumn = [];
22	$columnName = acym_getColumns("url_click");
23
24	if (!is_array($urlClick)) {
25	$urlClick = (array)$urlClick;
26	}
27
28	foreach ($urlClick as $key => $value) {
29	if (in_array($key, $columnName)) {
30	$column[] = '`'.acym_secureDBColumn($key).'`';
31	$valueColumn[] = acym_escapeDB($value);
32	}
33	}
34
35	$query = "#__acym_url_click (".implode(',', $column).") VALUES (".implode(',', $valueColumn).")";
36
37	$onDuplicate = [];
38
39	if (!empty($urlClick['click'])) {
40	$onDuplicate[] = "click = click + 1";
41	$automationClass = new AutomationClass();
42	$automationClass->trigger('user_click', ['userId' => $urlClick['user_id'], 'mailId' => $urlClick['mail_id']]);
43
44	$scenarioHelper = new ScenarioHelper();
45	$scenarioHelper->trigger('user_click', ['userId' => $urlClick['user_id'], 'mailId' => $urlClick['mail_id']]);
46	}
47
48	if (!empty($onDuplicate)) {
49	$query .= " ON DUPLICATE KEY UPDATE ";
50	$query .= implode(',', $onDuplicate);
51	$query = "INSERT INTO ".$query;
52	} else {
53	$query = "INSERT IGNORE INTO ".$query;
54	}
55
56	acym_query($query);
57	}
58
59	public function getNumberUsersClicked($mailIds = [], $isAbTest = false)
60	{
61	if (!is_array($mailIds)) $mailIds = [$mailIds];
62
63	acym_arrayToInteger($mailIds);
64
65	$query = 'SELECT COUNT(DISTINCT user_id) FROM #__acym_url_click AS url_click';
66	$isMultilingual = acym_isMultilingual();
67	if (($isMultilingual \|\| $isAbTest) && !empty($mailIds)) {
68	$query .= ' LEFT JOIN #__acym_mail AS mail ON `mail`.`id` = `url_click`.`mail_id` WHERE `mail`.`id` IN ('.implode(
69	',',
70	$mailIds
71	).') OR `mail`.`parent_id` IN ('.implode(',', $mailIds).')';
72	}
73	if (!$isMultilingual && !$isAbTest && !empty($mailIds)) $query .= ' WHERE `url_click`.`mail_id` IN ('.implode(',', $mailIds).')';
74	$clickNb = acym_loadResult($query);
75
76	return empty($clickNb) ? 0 : $clickNb;
77	}
78
79	public function getAllClickByMailMonth($mailIds = [], $start = '', $end = '')
80	{
81	if (!is_array($mailIds)) $mailIds = [$mailIds];
82	acym_arrayToInteger($mailIds);
83
84	$query = 'SELECT COUNT(*) as click, DATE_FORMAT(date_click, \'%Y-%m\') as date_click FROM #__acym_url_click WHERE click > 0';
85	$query .= empty($mailIds) ? '' : ' AND `mail_id` IN ('.implode(',', $mailIds).')';
86	$query .= empty($start) ? '' : ' AND date_click >= '.acym_escapeDB($start);
87	$query .= empty($end) ? '' : ' AND date_click <= '.acym_escapeDB($end);
88	$query .= ' GROUP BY MONTH(date_click), YEAR(date_click) ORDER BY date_click';
89
90	return acym_loadObjectList($query);
91	}
92
93	public function getAllClickByMailWeek($mailid = '', $start = '', $end = '')
94	{
95	$query = 'SELECT COUNT(*) as click, DATE_FORMAT(date_click, \'%Y-%m-%d\') as date_click FROM #__acym_url_click WHERE click > 0';
96	$query .= empty($mailid) ? '' : ' AND `mail_id` = '.intval($mailid);
97	$query .= empty($start) ? '' : ' AND date_click >= '.acym_escapeDB($start);
98	$query .= empty($end) ? '' : ' AND date_click <= '.acym_escapeDB($end);
99	$query .= ' GROUP BY WEEK(date_click), YEAR(date_click) ORDER BY date_click';
100
101	return acym_loadObjectList($query);
102	}
103
104	public function getAllClickByMailDay($mailIds = [], $start = '', $end = '')
105	{
106	if (!is_array($mailIds)) $mailIds = [$mailIds];
107	acym_arrayToInteger($mailIds);
108
109	$query = 'SELECT COUNT(*) as click, DATE_FORMAT(date_click, \'%Y-%m-%d\') as date_click FROM #__acym_url_click WHERE click > 0';
110	$query .= empty($mailIds) ? '' : ' AND `mail_id` IN ('.implode(',', $mailIds).')';
111	$query .= empty($start) ? '' : ' AND date_click >= '.acym_escapeDB($start);
112	$query .= empty($end) ? '' : ' AND date_click <= '.acym_escapeDB($end);
113	$query .= ' GROUP BY DAYOFYEAR(date_click), YEAR(date_click) ORDER BY date_click';
114
115	return acym_loadObjectList($query);
116	}
117
118	public function getAllClickByMailHour($mailIds = [], $start = '', $end = '')
119	{
120	if (!is_array($mailIds)) $mailIds = [$mailIds];
121	acym_arrayToInteger($mailIds);
122
123	$query = 'SELECT COUNT(*) as click, DATE_FORMAT(date_click, \'%Y-%m-%d %H:00:00\') as date_click FROM #__acym_url_click WHERE click > 0';
124	$query .= empty($mailIds) ? '' : ' AND `mail_id` IN ('.implode(',', $mailIds).')';
125	$query .= empty($start) ? '' : ' AND date_click >= '.acym_escapeDB($start);
126	$query .= empty($end) ? '' : ' AND date_click <= '.acym_escapeDB($end);
127	$query .= ' GROUP BY HOUR(date_click), DAYOFYEAR(date_click), YEAR(date_click) ORDER BY date_click';
128
129	return acym_loadObjectList($query);
130	}
131
132	public function getAllLinkFromEmail($id)
133	{
134	$queryClickUrl = 'SELECT url.name, SUM(urlclick.click) as click FROM #__acym_url_click AS urlclick
135	LEFT JOIN #__acym_url AS url ON urlclick.url_id = url.id
136	WHERE `mail_id` = '.intval($id).' GROUP BY `url_id`';
137
138	$queryCountAllClicks = 'SELECT SUM(click) FROM #__acym_url_click WHERE `mail_id` = '.intval($id);
139
140	return [
141	'urls_click' => acym_loadObjectList($queryClickUrl),
142	'allClick' => acym_loadResult($queryCountAllClicks),
143	];
144	}
145
146	public function getClickRateByMailIds($mailsIds = [])
147	{
148	$conditionMailId = '';
149	if (!empty($mailsIds)) {
150	acym_arrayToInteger($mailsIds);
151	$conditionMailId = 'WHERE mail_id IN ('.implode(',', $mailsIds).')';
152	}
153	$query = 'SELECT COUNT(groupStat.user_id) AS nbClick FROM (SELECT user_id FROM #__acym_url_click '.$conditionMailId.' GROUP BY mail_id, user_id) AS groupStat';
154
155	return acym_loadResult($query);
156	}
157
158	public function getUrlsFromMailsWithDetails($params)
159	{
160	if (empty($params['mail_ids'])) return [];
161
162	if (!is_array($params['mail_ids'])) $params['mail_ids'] = [$params['mail_ids']];
163	acym_arrayToInteger($params['mail_ids']);
164
165	$query = 'SELECT url.id, url.name, SUM(click) AS total_click, COUNT(DISTINCT user_id) AS unique_click, mail.subject AS mail_subject, mail.name AS mail_name
166	FROM #__acym_url AS url
167	JOIN #__acym_url_click AS url_click ON url.id = url_click.url_id AND url_click.mail_id IN ('.implode(',', $params['mail_ids']).')
168	JOIN #__acym_mail AS mail ON mail.id = url_click.mail_id';
169
170	$queryCount = 'SELECT COUNT(DISTINCT url.id) FROM #__acym_url AS url
171	JOIN #__acym_url_click AS url_click ON url.id = url_click.url_id AND url_click.mail_id IN ('.implode(',', $params['mail_ids']).')
172	JOIN #__acym_mail AS mail ON mail.id = url_click.mail_id';
173
174	if (!empty($params['search'])) {
175	$searchTerms = acym_escapeDB('%'.$params['search'].'%');
176	$where[] = 'url.name LIKE '.$searchTerms;
177	}
178
179	if (!empty($where)) {
180	$query .= ' WHERE ('.implode(') AND (', $where).')';
181	$queryCount .= ' WHERE ('.implode(') AND (', $where).')';
182	}
183
184	$query .= ' GROUP BY name';
185
186	if (!empty($params['ordering']) && !empty($params['ordering_sort_order'])) {
187	$query .= ' ORDER BY '.acym_secureDBColumn($params['ordering']).' '.acym_secureDBColumn(strtoupper($params['ordering_sort_order']));
188	} else {
189	$query .= ' ORDER BY id DESC';
190	}
191
192	$return = [];
193	$return['links_details'] = acym_loadObjectList($query, '', $params['offset'], $params['detailedStatsPerPage']);
194	$return['total'] = acym_loadResult($queryCount);
195	$return['query'] = $query;
196
197	return $return;
198	}
199
200	public function getUserUrlClicksStats($params)
201	{
202	if (empty($params['mail_ids'])) return [];
203
204	if (!is_array($params['mail_ids'])) $params['mail_ids'] = [$params['mail_ids']];
205	acym_arrayToInteger($params['mail_ids']);
206
207	$query = 'SELECT url.id AS url_id,
208	user.id AS user_id,
209	user.email,
210	user.name AS user_name,
211	url.name AS url_name,
212	url_click.date_click,
213	url_click.click,
214	mail.subject AS mail_subject,
215	mail.name AS mail_name
216	FROM #__acym_user AS user
217	JOIN #__acym_url_click AS url_click ON url_click.user_id = user.id AND url_click.mail_id IN ('.implode(',', $params['mail_ids']).')
218	JOIN #__acym_url AS url ON url.id = url_click.url_id
219	JOIN #__acym_mail AS mail ON mail.id = url_click.mail_id';
220
221	$queryCount = 'SELECT COUNT(DISTINCT user.id) FROM #__acym_user AS user
222	JOIN #__acym_url_click AS url_click ON url_click.user_id = user.id AND url_click.mail_id IN ('.implode(',', $params['mail_ids']).')
223	JOIN #__acym_url AS url ON url.id = url_click.url_id
224	JOIN #__acym_mail AS mail ON mail.id = url_click.mail_id';
225
226	if (!empty($params['search'])) {
227	$searchTerms = acym_escapeDB('%'.$params['search'].'%');
228	$where[] = 'url.name LIKE '.$searchTerms.' or user.name LIKE '.$searchTerms.' or user.email LIKE '.$searchTerms;
229	}
230
231	if (!empty($where)) {
232	$query .= ' WHERE('.implode(') and (', $where).')';
233	$queryCount .= ' WHERE('.implode(') and (', $where).')';
234	}
235
236	if (!empty($params['ordering']) && !empty($params['ordering_sort_order'])) {
237	$query .= ' ORDER BY '.acym_secureDBColumn($params['ordering']).' '.acym_secureDBColumn(strtoupper($params['ordering_sort_order']));
238	} else {
239	$query .= ' ORDER BY user_id DESC';
240	}
241
242	$return = [];
243	$return['user_links_details'] = acym_loadObjectList($query, '', $params['offset'], $params['detailedStatsPerPage']);
244	$return['total'] = acym_loadResult($queryCount);
245	$return['query'] = $query;
246
247	return $return;
248	}
249
250	public function getTotalClicksPerMail(array $mailIds = [])
251	{
252	acym_arrayToInteger($mailIds);
253
254	$query = 'SELECT SUM(click) AS total_clicks, COUNT(click) AS unique_clicks, mail_id FROM #__acym_url_click ';
255	$query .= empty($mailIds) ? '' : 'WHERE `mail_id` IN ('.implode(',', $mailIds).') ';
256	$query .= 'GROUP BY mail_id';
257
258	return acym_loadObjectList($query, 'mail_id');
259	}
260
261	public function getOneByMailIdAndUserId(int $mailId, int $userId)
262	{
263	return acym_loadObject('SELECT * FROM #__acym_url_click WHERE mail_id = '.intval($mailId).' AND user_id = '.intval($userId));
264	}
265
266	public function getUserMailsClicked(int $userId): array
267	{
268	return acym_loadObjectList(
269	'SELECT user_stat.mail_id, url_click.click
270	FROM #__acym_user_stat AS user_stat
271	LEFT JOIN #__acym_url_click AS url_click
272	ON url_click.mail_id = user_stat.mail_id
273	AND url_click.user_id = user_stat.user_id
274	WHERE user_stat.user_id = '.intval($userId).'
275	GROUP BY user_stat.mail_id'
276	);
277	}
278
279	public function getClicksByMailId(int $mailId): array
280	{
281	return acym_loadObjectList(
282	'SELECT url_click.url_id, url_click.user_id, url_click.click, url_click.date_click, `user`.email, url.url
283	FROM #__acym_url_click AS url_click
284	LEFT JOIN #__acym_user AS `user` ON url_click.user_id = `user`.id
285	JOIN #__acym_url AS url ON url_click.url_id = url.id
286	WHERE url_click.mail_id = '.intval($mailId)
287	);
288	}
289	}
290