← All changes | back/Classes/UrlClickClass.php +45 -48 10.1.4 → 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,9 +134,11 @@
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	143	public function getUrlsFromMailsWithDetails(array $params): array
159	144	{
		@@ -248,9 +233,9 @@
248	233	'query' => $query,
249	234	];
250	235	}
251	236
252		- public function getTotalClicksPerMail(array $mailIds = [])
	237	+ public function getTotalClicksPerMail(array $mailIds = []): array
253	238	{
254	239	acym_arrayToInteger($mailIds);
255	240
256	241	$query = 'SELECT SUM(click) AS total_clicks, COUNT(click) AS unique_clicks, mail_id FROM #__acym_url_click ';
		@@ -259,11 +244,13 @@
259	244
260	245	return acym_loadObjectList($query, 'mail_id');
261	246	}
262	247
263		- public function getOneByMailIdAndUserId(int $mailId, int $userId)
	248	+ public function getOneByMailIdAndUserId(int $mailId, int $userId): ?object
264	249	{
265		- 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;
266	253	}
267	254
268	255	public function getUserMailsClicked(int $userId): array
269	256	{
		@@ -285,7 +272,17 @@
285	272	FROM #__acym_url_click AS url_click
286	273	LEFT JOIN #__acym_user AS `user` ON url_click.user_id = `user`.id
287	274	JOIN #__acym_url AS url ON url_click.url_id = url.id
288	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)
289	286	);
290	287	}
291	288	}