acymailing / back / Classes / UrlClickClass.php

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

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