acymailing / back / Classes / UrlClickClass.php

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

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