← All changes | back/Classes/UrlClickClass.php +65 -66 10.0.1 → 11.1.0 View file →

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