← All changes | back/Classes/UrlClickClass.php +0 -292 11.0.4 → 11.1.1 View file →

		@@ -1,292 +1,0 @@
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		-}