acymailing / back / Classes / UrlClickClass.php

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

289 lines 11.8 KB

Raw Download Zip

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