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

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