| @@ -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 | -} | |
