| @@ -1,357 +1,0 @@ | ||
| 1 | -<?php | |
| 2 | - | |
| 3 | -namespace AcyMailing\Classes; | |
| 4 | - | |
| 5 | -use AcyMailing\Helpers\ScenarioHelper; | |
| 6 | -use AcyMailing\Core\AcymClass; | |
| 7 | - | |
| 8 | -class UserStatClass extends AcymClass | |
| 9 | -{ | |
| 10 | - public function __construct() | |
| 11 | - { | |
| 12 | - parent::__construct(); | |
| 13 | - | |
| 14 | - $this->table = 'user_stat'; | |
| 15 | - } | |
| 16 | - | |
| 17 | - public function save(object $element, bool $overrideSendDate = false): ?int | |
| 18 | - { | |
| 19 | - $userStat = get_object_vars($element); | |
| 20 | - | |
| 21 | - $column = []; | |
| 22 | - $valueColumn = []; | |
| 23 | - $columnName = acym_getColumns('user_stat'); | |
| 24 | - | |
| 25 | - foreach ($userStat as $key => $value) { | |
| 26 | - if (in_array($key, $columnName)) { | |
| 27 | - $column[] = '`'.acym_secureDBColumn($key).'`'; | |
| 28 | - | |
| 29 | - if ($key === 'tracking_sale') { | |
| 30 | - $valueColumn[] = strlen($value) === 0 ? 'NULL' : floatval($value); | |
| 31 | - } else { | |
| 32 | - $valueColumn[] = acym_escapeDB($value); | |
| 33 | - } | |
| 34 | - } | |
| 35 | - } | |
| 36 | - | |
| 37 | - $query = 'INSERT INTO #__acym_user_stat ('.implode(',', $column).') VALUE ('.implode(', ', $valueColumn).')'; | |
| 38 | - $onDuplicate = []; | |
| 39 | - | |
| 40 | - if (isset($userStat['statusSending'])) { | |
| 41 | - $onDuplicate[] = empty($userStat['statusSending']) ? 'fail = fail + 1' : 'sent = sent + 1'; | |
| 42 | - } | |
| 43 | - | |
| 44 | - if ($overrideSendDate && !empty($userStat['send_date'])) { | |
| 45 | - $onDuplicate[] = 'send_date = '.acym_escapeDB($userStat['send_date']); | |
| 46 | - } | |
| 47 | - | |
| 48 | - if (!empty($userStat['open'])) { | |
| 49 | - $onDuplicate[] = 'open = open + 1'; | |
| 50 | - $automationClass = new AutomationClass(); | |
| 51 | - $automationClass->trigger('user_open', ['userId' => $userStat['user_id'], 'mailId' => $userStat['mail_id']]); | |
| 52 | - | |
| 53 | - $scenarioHelper = new ScenarioHelper(); | |
| 54 | - $scenarioHelper->trigger('user_open', ['userId' => $userStat['user_id'], 'mailId' => $userStat['mail_id']]); | |
| 55 | - } | |
| 56 | - | |
| 57 | - if (!empty($userStat['open_date'])) { | |
| 58 | - $onDuplicate[] = 'open_date = '.acym_escapeDB($userStat['open_date']); | |
| 59 | - } | |
| 60 | - | |
| 61 | - if (!empty($userStat['tracking_sale'])) { | |
| 62 | - $onDuplicate[] = 'tracking_sale = '.floatval($userStat['tracking_sale']); | |
| 63 | - } | |
| 64 | - | |
| 65 | - if (!empty($userStat['currency'])) { | |
| 66 | - $onDuplicate[] = 'currency = '.acym_escapeDB($userStat['currency']); | |
| 67 | - } | |
| 68 | - | |
| 69 | - if (!empty($userStat['unsubscribe'])) { | |
| 70 | - $onDuplicate[] = 'unsubscribe = '.intval($userStat['unsubscribe']); | |
| 71 | - } | |
| 72 | - | |
| 73 | - if (!empty($userStat['device'])) { | |
| 74 | - $onDuplicate[] = 'device = '.acym_escapeDB($userStat['device']); | |
| 75 | - } | |
| 76 | - | |
| 77 | - if (!empty($userStat['opened_with'])) { | |
| 78 | - $onDuplicate[] = 'opened_with = '.acym_escapeDB($userStat['opened_with']); | |
| 79 | - } | |
| 80 | - | |
| 81 | - if (!empty($onDuplicate)) { | |
| 82 | - $query .= ' ON DUPLICATE KEY UPDATE '; | |
| 83 | - $query .= implode(',', $onDuplicate); | |
| 84 | - } | |
| 85 | - | |
| 86 | - return (int)acym_query($query); | |
| 87 | - } | |
| 88 | - | |
| 89 | - public function getOneByMailAndUserId(int $mailId, int $userId): ?object | |
| 90 | - { | |
| 91 | - $userStat = acym_loadObject('SELECT * FROM #__acym_user_stat WHERE `mail_id` = '.intval($mailId).' AND `user_id` = '.intval($userId)); | |
| 92 | - | |
| 93 | - return empty($userStat) ? null : $userStat; | |
| 94 | - } | |
| 95 | - | |
| 96 | - public function getDetailedStatistics(array $options): array | |
| 97 | - { | |
| 98 | - $limit = $options['limit'] ?? 10; | |
| 99 | - $offset = $options['offset'] ?? 0; | |
| 100 | - $mailId = $options['mail_id'] ?? 0; | |
| 101 | - | |
| 102 | - return acym_loadObjectList( | |
| 103 | - 'SELECT user_stat.*, `user`.email | |
| 104 | - FROM #__acym_user_stat AS user_stat | |
| 105 | - JOIN #__acym_user AS `user` ON user_stat.user_id = `user`.id | |
| 106 | - WHERE `mail_id` = '.intval($mailId), | |
| 107 | - '', | |
| 108 | - $offset, | |
| 109 | - $limit | |
| 110 | - ); | |
| 111 | - } | |
| 112 | - | |
| 113 | - public function getAllUserStatByUserId(int $userId): array | |
| 114 | - { | |
| 115 | - return acym_loadObjectList( | |
| 116 | - 'SELECT userStat.*, mail.subject | |
| 117 | - FROM #__acym_user_stat AS userStat | |
| 118 | - JOIN #__acym_mail AS mail ON mail.id = userStat.mail_id | |
| 119 | - WHERE userStat.user_id = '.intval($userId) | |
| 120 | - ); | |
| 121 | - } | |
| 122 | - | |
| 123 | - public function getDetailedStats(array $settings): array | |
| 124 | - { | |
| 125 | - $mailClass = new MailClass(); | |
| 126 | - | |
| 127 | - $query = 'SELECT us.*, m.name, m.subject, m.parent_id, u.email, u.name AS username | |
| 128 | - FROM #__acym_user_stat AS us | |
| 129 | - LEFT JOIN #__acym_user AS u ON us.user_id = u.id | |
| 130 | - INNER JOIN #__acym_mail AS m ON us.mail_id = m.id'; | |
| 131 | - $queryCount = 'SELECT COUNT(*) AS total FROM #__acym_user_stat as us | |
| 132 | - LEFT JOIN #__acym_user AS u ON us.user_id = u.id | |
| 133 | - INNER JOIN #__acym_mail AS m ON us.mail_id = m.id'; | |
| 134 | - $where = []; | |
| 135 | - | |
| 136 | - if (!empty($settings['mail_ids'])) { | |
| 137 | - if (!is_array($settings['mail_ids'])) $settings['mail_ids'] = [$settings['mail_ids']]; | |
| 138 | - acym_arrayToInteger($settings['mail_ids']); | |
| 139 | - $where[] = 'us.mail_id IN ('.implode(',', $settings['mail_ids']).')'; | |
| 140 | - } | |
| 141 | - | |
| 142 | - if (!empty($settings['search'])) { | |
| 143 | - $searchTerms = acym_escapeDB('%'.$settings['search'].'%'); | |
| 144 | - $where[] = 'm.name LIKE '.$searchTerms.' OR u.email LIKE '.$searchTerms.' OR u.name LIKE '.$searchTerms; | |
| 145 | - } | |
| 146 | - | |
| 147 | - if (!empty($where)) { | |
| 148 | - $query .= ' WHERE ('.implode(') AND (', $where).')'; | |
| 149 | - $queryCount .= ' WHERE ('.implode(') AND (', $where).')'; | |
| 150 | - } | |
| 151 | - | |
| 152 | - if (!empty($settings['ordering']) && !empty($settings['ordering_sort_order'])) { | |
| 153 | - if (in_array($settings['ordering'], ['email', 'name'])) { | |
| 154 | - $table = 'u'; | |
| 155 | - } elseif ($settings['ordering'] === 'subject') { | |
| 156 | - $table = 'm'; | |
| 157 | - } else { | |
| 158 | - $table = 'us'; | |
| 159 | - } | |
| 160 | - $query .= ' ORDER BY '.$table.'.'.acym_secureDBColumn($settings['ordering']).' '.acym_secureDBColumn(strtoupper($settings['ordering_sort_order'])).', us.user_id ASC'; | |
| 161 | - } | |
| 162 | - | |
| 163 | - $mails = acym_loadObjectList($query, '', $settings['offset'], $settings['detailedStatsPerPage']); | |
| 164 | - | |
| 165 | - if (!empty($mails)) { | |
| 166 | - if (acym_isTrackingSalesActive()) { | |
| 167 | - $mailIds = []; | |
| 168 | - foreach ($mails as $mail) { | |
| 169 | - if (!in_array($mail->mail_id, $mailIds)) $mailIds[] = $mail->mail_id; | |
| 170 | - } | |
| 171 | - | |
| 172 | - $trackingSales = []; | |
| 173 | - $trackingSalesDB = acym_loadObjectList( | |
| 174 | - 'SELECT SUM(tracking_sale) as sale, currency, mail_id, user_id FROM #__acym_user_stat WHERE mail_id IN ('.implode( | |
| 175 | - ',', | |
| 176 | - $mailIds | |
| 177 | - ).') AND currency IS NOT NULL GROUP BY user_id, mail_id' | |
| 178 | - ); | |
| 179 | - foreach ($trackingSalesDB as $oneMailTrackSales) { | |
| 180 | - $trackingSales[$oneMailTrackSales->mail_id.'-'.$oneMailTrackSales->user_id] = $oneMailTrackSales; | |
| 181 | - } | |
| 182 | - } | |
| 183 | - | |
| 184 | - $totalClicksForMails = []; | |
| 185 | - $campaignsForMails = []; | |
| 186 | - foreach ($mails as $key => $mail) { | |
| 187 | - // Add tracked income amount | |
| 188 | - if (!empty($trackingSales[$mail->mail_id.'-'.$mail->user_id])) { | |
| 189 | - acym_trigger('getCurrency', [&$mails[$key]->currency]); | |
| 190 | - $mails[$key]->sales = $trackingSales[$mail->mail_id.'-'.$mail->user_id]->sale; | |
| 191 | - } | |
| 192 | - | |
| 193 | - // Add total clicks | |
| 194 | - if (!isset($totalClicksForMails[$mail->mail_id])) { | |
| 195 | - $totalClicksForMails[$mail->mail_id] = acym_loadObjectList( | |
| 196 | - 'SELECT SUM(click) AS nbClicks, user_id | |
| 197 | - FROM #__acym_url_click | |
| 198 | - WHERE mail_id = '.intval($mail->mail_id).' | |
| 199 | - GROUP BY user_id', | |
| 200 | - 'user_id' | |
| 201 | - ); | |
| 202 | - } | |
| 203 | - $mails[$key]->total_click = empty($totalClicksForMails[$mail->mail_id][$mail->user_id]) ? 0 : $totalClicksForMails[$mail->mail_id][$mail->user_id]->nbClicks; | |
| 204 | - | |
| 205 | - // Handle multilingual | |
| 206 | - $mainMailId = empty($mail->parent_id) ? $mail->mail_id : $mail->parent_id; | |
| 207 | - if (!isset($campaignsForMails[$mainMailId])) { | |
| 208 | - $campaignsForMails[$mainMailId] = acym_loadObject('SELECT id AS campaign_id, parent_id FROM #__acym_campaign WHERE mail_id = '.intval($mainMailId)); | |
| 209 | - } | |
| 210 | - | |
| 211 | - $mails[$key]->campaign_id = empty($campaignsForMails[$mainMailId]) ? 0 : $campaignsForMails[$mainMailId]->campaign_id; | |
| 212 | - $mails[$key]->parent_id = empty($campaignsForMails[$mainMailId]) ? 0 : $campaignsForMails[$mainMailId]->parent_id; | |
| 213 | - } | |
| 214 | - } | |
| 215 | - | |
| 216 | - return [ | |
| 217 | - 'detailed_stats' => $mailClass->decode($mails), | |
| 218 | - 'total' => acym_loadObject($queryCount), | |
| 219 | - ]; | |
| 220 | - } | |
| 221 | - | |
| 222 | - public function getOpenTimeStats(array $mailIds): array | |
| 223 | - { | |
| 224 | - acym_arrayToInteger($mailIds); | |
| 225 | - $specificMailsCondition = empty($mailIds) ? '' : ' AND mail_id in ('.implode(',', $mailIds).')'; | |
| 226 | - $offsetSeconds = acym_getTimeOffsetCMS(); | |
| 227 | - | |
| 228 | - return [ | |
| 229 | - 'total_open' => acym_loadResult( | |
| 230 | - 'SELECT SUM(`open`) | |
| 231 | - FROM #__acym_user_stat | |
| 232 | - WHERE open_date IS NOT NULL '.$specificMailsCondition | |
| 233 | - ), | |
| 234 | - 'stats' => acym_loadObjectList( | |
| 235 | - 'SELECT | |
| 236 | - SUM(`open`) AS `open_total`, | |
| 237 | - DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%w") AS `day`, | |
| 238 | - FORMAT(CONVERT(DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%H"), SIGNED INTEGER) / 3, 0) AS `hour`, | |
| 239 | - CONCAT( | |
| 240 | - DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%w"), | |
| 241 | - "_", | |
| 242 | - FORMAT(CONVERT(DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%H"), SIGNED INTEGER) / 3, 0) | |
| 243 | - ) AS `date_id` | |
| 244 | - FROM `#__acym_user_stat` | |
| 245 | - WHERE open_date IS NOT NULL '.$specificMailsCondition.' | |
| 246 | - GROUP BY date_id', | |
| 247 | - 'date_id' | |
| 248 | - ), | |
| 249 | - ]; | |
| 250 | - } | |
| 251 | - | |
| 252 | - public function getDefaultStat(): array | |
| 253 | - { | |
| 254 | - $percentageRemaining = 100; | |
| 255 | - $stats = []; | |
| 256 | - | |
| 257 | - for ($day = 0; $day < 7; $day++) { | |
| 258 | - $stats[$day] = []; | |
| 259 | - for ($hour = 0; $hour < 8; $hour++) { | |
| 260 | - $stats[$day][$hour] = $this->getRandomStatOpenTime($percentageRemaining, $hour); | |
| 261 | - } | |
| 262 | - } | |
| 263 | - | |
| 264 | - return $stats; | |
| 265 | - } | |
| 266 | - | |
| 267 | - public function getOpenSourcesStats(array $mailIds = []): array | |
| 268 | - { | |
| 269 | - acym_arrayToInteger($mailIds); | |
| 270 | - | |
| 271 | - $query = 'SELECT opened_with, COUNT(*) AS number FROM #__acym_user_stat WHERE `open` > 0'; | |
| 272 | - if (!empty($mailIds)) { | |
| 273 | - $query .= ' AND mail_id IN ('.implode(',', $mailIds).')'; | |
| 274 | - } | |
| 275 | - $query .= ' GROUP BY opened_with'; | |
| 276 | - | |
| 277 | - return acym_loadObjectList($query); | |
| 278 | - } | |
| 279 | - | |
| 280 | - public function deleteDetailedStatsPeriod(): array | |
| 281 | - { | |
| 282 | - $shouldClearExpiredStats = $this->config->get('delete_stats_enabled', 0); | |
| 283 | - if (empty($shouldClearExpiredStats)) { | |
| 284 | - return []; | |
| 285 | - } | |
| 286 | - | |
| 287 | - $deleteAfterXSeconds = $this->config->get('delete_stats', 86400 * 360); | |
| 288 | - if (empty($deleteAfterXSeconds)) { | |
| 289 | - return []; | |
| 290 | - } | |
| 291 | - | |
| 292 | - $date = acym_date(time() - $deleteAfterXSeconds, 'Y-m-d H:i', false); | |
| 293 | - | |
| 294 | - $queries = [ | |
| 295 | - '#__acym_user_stat' => 'DELETE FROM #__acym_user_stat WHERE send_date < '.acym_escapeDB($date), | |
| 296 | - '#__acym_url_click' => 'DELETE FROM #__acym_url_click WHERE date_click < '.acym_escapeDB($date), | |
| 297 | - ]; | |
| 298 | - | |
| 299 | - $messages = []; | |
| 300 | - foreach ($queries as $table => $query) { | |
| 301 | - try { | |
| 302 | - $status = acym_query($query); | |
| 303 | - if (!empty($status)) { | |
| 304 | - $messages[] = acym_translationSprintf('ACYM_DELETE_X_ROWS_TABLE_X', $status, acym_translation('ACYM_USER_DETAILED_STATS').' ('.$table.')'); | |
| 305 | - } | |
| 306 | - } catch (\Exception $e) { | |
| 307 | - $messages[] = $e->getMessage(); | |
| 308 | - } | |
| 309 | - } | |
| 310 | - | |
| 311 | - if (empty($messages)) { | |
| 312 | - return []; | |
| 313 | - } | |
| 314 | - | |
| 315 | - return ['message' => implode("\r\n", $messages)]; | |
| 316 | - } | |
| 317 | - | |
| 318 | - private function getRandomStatOpenTime(int &$percentageRemaining, int $hour): int | |
| 319 | - { | |
| 320 | - if (empty($percentageRemaining)) { | |
| 321 | - return 0; | |
| 322 | - } | |
| 323 | - | |
| 324 | - $randoms = [ | |
| 325 | - 0 => [0, 1], | |
| 326 | - 1 => [0, 2], | |
| 327 | - 2 => [1, 2], | |
| 328 | - 3 => [1, 3], | |
| 329 | - 4 => [3, 5], | |
| 330 | - 5 => [1, 3], | |
| 331 | - 6 => [1, 2], | |
| 332 | - 7 => [0, 2], | |
| 333 | - ]; | |
| 334 | - | |
| 335 | - $percentage = acym_rand($randoms[$hour][0], $randoms[$hour][1]); | |
| 336 | - | |
| 337 | - if ($percentageRemaining - $percentage < 0) { | |
| 338 | - return 0; | |
| 339 | - } | |
| 340 | - | |
| 341 | - $percentageRemaining -= $percentage; | |
| 342 | - | |
| 343 | - return $percentage; | |
| 344 | - } | |
| 345 | - | |
| 346 | - public function hasUserReceivedMail(int $mailId, int $userId): bool | |
| 347 | - { | |
| 348 | - $sentToRecipient = acym_loadResult( | |
| 349 | - 'SELECT COUNT(*) | |
| 350 | - FROM #__acym_user_stat AS userStat | |
| 351 | - WHERE userStat.mail_id = '.intval($mailId).' | |
| 352 | - AND userStat.user_id = '.intval($userId) | |
| 353 | - ); | |
| 354 | - | |
| 355 | - return !empty($sentToRecipient); | |
| 356 | - } | |
| 357 | -} | |
