| @@ -6,33 +6,8 @@ | ||
| 6 | 6 | use AcyMailing\Core\AcymClass; |
| 7 | 7 | |
| 8 | 8 | class UserStatClass extends AcymClass |
| 9 | 9 | { |
| 10 | - const DESKTOP_DEVICES = [ | |
| 11 | - 'windows' => 'Windows', | |
| 12 | - 'macintosh' => 'Mac', | |
| 13 | - 'linux' => 'Linux', | |
| 14 | - ]; | |
| 15 | - | |
| 16 | - const MOBILE_DEVICES = [ | |
| 17 | - 'bada' => 'Bada', | |
| 18 | - 'ubuntu; mobile' => 'Ubuntu Mobile', | |
| 19 | - 'ubuntu; tablet' => 'Ubuntu Tablet', | |
| 20 | - 'tizen' => 'Tizen', | |
| 21 | - 'palm os' => 'Palm', | |
| 22 | - 'meego' => 'meeGo', | |
| 23 | - 'symbian' => 'Symbian', | |
| 24 | - 'symbos' => 'Symbian', | |
| 25 | - 'blackberry' => 'BlackBerry', | |
| 26 | - 'windows ce' => 'Windows Phone', | |
| 27 | - 'windows mobile' => 'Windows Phone', | |
| 28 | - 'windows phone' => 'Windows Phone', | |
| 29 | - 'iphone' => 'iPhone', | |
| 30 | - 'ipad' => 'iPad', | |
| 31 | - 'ipod' => 'iPod', | |
| 32 | - 'android' => 'Android', | |
| 33 | - ]; | |
| 34 | - | |
| 35 | 10 | public function __construct() |
| 36 | 11 | { |
| 37 | 12 | parent::__construct(); |
| 38 | 13 | |
| @@ -38,16 +13,15 @@ | ||
| 38 | 13 | |
| 39 | 14 | $this->table = 'user_stat'; |
| 40 | 15 | } |
| 41 | 16 | |
| 42 | - public function save($userStat, $overrideSendDate = false) | |
| 17 | + public function save(object $element, bool $overrideSendDate = false): ?int | |
| 43 | 18 | { |
| 19 | + $userStat = get_object_vars($element); | |
| 20 | + | |
| 44 | 21 | $column = []; |
| 45 | 22 | $valueColumn = []; |
| 46 | 23 | $columnName = acym_getColumns('user_stat'); |
| 47 | - if (![$userStat] || !is_array($userStat)) { | |
| 48 | - $userStat = (array)$userStat; | |
| 49 | - } | |
| 50 | 24 | |
| 51 | 25 | foreach ($userStat as $key => $value) { |
| 52 | 26 | if (in_array($key, $columnName)) { |
| 53 | 27 | $column[] = '`'.acym_secureDBColumn($key).'`'; |
| @@ -62,10 +36,10 @@ | ||
| 62 | 36 | |
| 63 | 37 | $query = 'INSERT INTO #__acym_user_stat ('.implode(',', $column).') VALUE ('.implode(', ', $valueColumn).')'; |
| 64 | 38 | $onDuplicate = []; |
| 65 | 39 | |
| 66 | - if (!empty($userStat['statusSending'])) { | |
| 67 | - $onDuplicate[] = $userStat['statusSending'] == 0 ? 'fail = fail + 1' : 'sent = sent + 1'; | |
| 40 | + if (isset($userStat['statusSending'])) { | |
| 41 | + $onDuplicate[] = empty($userStat['statusSending']) ? 'fail = fail + 1' : 'sent = sent + 1'; | |
| 68 | 42 | } |
| 69 | 43 | |
| 70 | 44 | if ($overrideSendDate && !empty($userStat['send_date'])) { |
| 71 | 45 | $onDuplicate[] = 'send_date = '.acym_escapeDB($userStat['send_date']); |
| @@ -72,13 +46,8 @@ | ||
| 72 | 46 | } |
| 73 | 47 | |
| 74 | 48 | if (!empty($userStat['open'])) { |
| 75 | 49 | $onDuplicate[] = 'open = open + 1'; |
| 76 | - $automationClass = new AutomationClass(); | |
| 77 | - $automationClass->trigger('user_open', ['userId' => $userStat['user_id'], 'mailId' => $userStat['mail_id']]); | |
| 78 | - | |
| 79 | - $scenarioHelper = new ScenarioHelper(); | |
| 80 | - $scenarioHelper->trigger('user_open', ['userId' => $userStat['user_id'], 'mailId' => $userStat['mail_id']]); | |
| 81 | 50 | } |
| 82 | 51 | |
| 83 | 52 | if (!empty($userStat['open_date'])) { |
| 84 | 53 | $onDuplicate[] = 'open_date = '.acym_escapeDB($userStat['open_date']); |
| @@ -108,20 +77,28 @@ | ||
| 108 | 77 | $query .= ' ON DUPLICATE KEY UPDATE '; |
| 109 | 78 | $query .= implode(',', $onDuplicate); |
| 110 | 79 | } |
| 111 | 80 | |
| 112 | - acym_query($query); | |
| 81 | + return (int)acym_query($query); | |
| 113 | 82 | } |
| 114 | 83 | |
| 115 | - public function getOneByMailAndUserId($mail_id, $user_id) | |
| 84 | + public static function triggerActivity(string $event, int $userId, int $mailId): void | |
| 116 | 85 | { |
| 117 | - $query = 'SELECT * FROM #__acym_user_stat WHERE `mail_id` = '.intval($mail_id).' AND `user_id` = '.intval($user_id); | |
| 86 | + $payload = ['userId' => $userId, 'mailId' => $mailId]; | |
| 118 | 87 | |
| 119 | - return acym_loadObject($query); | |
| 88 | + (new AutomationClass())->trigger($event, $payload); | |
| 89 | + (new ScenarioHelper())->trigger($event, $payload); | |
| 120 | 90 | } |
| 121 | 91 | |
| 122 | - public function getDetailedStatistics(array $options) | |
| 92 | + public function getOneByMailAndUserId(int $mailId, int $userId): ?object | |
| 123 | 93 | { |
| 94 | + $userStat = acym_loadObject('SELECT * FROM #__acym_user_stat WHERE `mail_id` = '.intval($mailId).' AND `user_id` = '.intval($userId)); | |
| 95 | + | |
| 96 | + return empty($userStat) ? null : $userStat; | |
| 97 | + } | |
| 98 | + | |
| 99 | + public function getDetailedStatistics(array $options): array | |
| 100 | + { | |
| 124 | 101 | $limit = $options['limit'] ?? 10; |
| 125 | 102 | $offset = $options['offset'] ?? 0; |
| 126 | 103 | $mailId = $options['mail_id'] ?? 0; |
| 127 | 104 | |
| @@ -135,16 +112,19 @@ | ||
| 135 | 112 | $limit |
| 136 | 113 | ); |
| 137 | 114 | } |
| 138 | 115 | |
| 139 | - public function getAllUserStatByUserId($idUser) | |
| 116 | + public function getAllUserStatByUserId(int $userId): array | |
| 140 | 117 | { |
| 141 | - $query = 'SELECT * FROM #__acym_user_stat WHERE user_id = '.intval($idUser); | |
| 142 | - | |
| 143 | - return acym_loadObjectList($query); | |
| 118 | + return acym_loadObjectList( | |
| 119 | + 'SELECT userStat.*, mail.subject | |
| 120 | + FROM #__acym_user_stat AS userStat | |
| 121 | + JOIN #__acym_mail AS mail ON mail.id = userStat.mail_id | |
| 122 | + WHERE userStat.user_id = '.intval($userId) | |
| 123 | + ); | |
| 144 | 124 | } |
| 145 | 125 | |
| 146 | - public function getDetailedStats($settings) | |
| 126 | + public function getDetailedStats(array $settings): array | |
| 147 | 127 | { |
| 148 | 128 | $mailClass = new MailClass(); |
| 149 | 129 | |
| 150 | 130 | $query = 'SELECT us.*, m.name, m.subject, m.parent_id, u.email, u.name AS username |
| @@ -150,9 +130,9 @@ | ||
| 150 | 130 | $query = 'SELECT us.*, m.name, m.subject, m.parent_id, u.email, u.name AS username |
| 151 | 131 | FROM #__acym_user_stat AS us |
| 152 | 132 | LEFT JOIN #__acym_user AS u ON us.user_id = u.id |
| 153 | 133 | INNER JOIN #__acym_mail AS m ON us.mail_id = m.id'; |
| 154 | - $queryCount = 'SELECT COUNT(*) FROM #__acym_user_stat as us | |
| 134 | + $queryCount = 'SELECT COUNT(*) AS total FROM #__acym_user_stat as us | |
| 155 | 135 | LEFT JOIN #__acym_user AS u ON us.user_id = u.id |
| 156 | 136 | INNER JOIN #__acym_mail AS m ON us.mail_id = m.id'; |
| 157 | 137 | $where = []; |
| 158 | 138 | |
| @@ -206,13 +186,15 @@ | ||
| 206 | 186 | |
| 207 | 187 | $totalClicksForMails = []; |
| 208 | 188 | $campaignsForMails = []; |
| 209 | 189 | foreach ($mails as $key => $mail) { |
| 190 | + // Add tracked income amount | |
| 210 | 191 | if (!empty($trackingSales[$mail->mail_id.'-'.$mail->user_id])) { |
| 211 | 192 | acym_trigger('getCurrency', [&$mails[$key]->currency]); |
| 212 | 193 | $mails[$key]->sales = $trackingSales[$mail->mail_id.'-'.$mail->user_id]->sale; |
| 213 | 194 | } |
| 214 | 195 | |
| 196 | + // Add total clicks | |
| 215 | 197 | if (!isset($totalClicksForMails[$mail->mail_id])) { |
| 216 | 198 | $totalClicksForMails[$mail->mail_id] = acym_loadObjectList( |
| 217 | 199 | 'SELECT SUM(click) AS nbClicks, user_id |
| 218 | 200 | FROM #__acym_url_click |
| @@ -222,8 +204,9 @@ | ||
| 222 | 204 | ); |
| 223 | 205 | } |
| 224 | 206 | $mails[$key]->total_click = empty($totalClicksForMails[$mail->mail_id][$mail->user_id]) ? 0 : $totalClicksForMails[$mail->mail_id][$mail->user_id]->nbClicks; |
| 225 | 207 | |
| 208 | + // Handle multilingual | |
| 226 | 209 | $mainMailId = empty($mail->parent_id) ? $mail->mail_id : $mail->parent_id; |
| 227 | 210 | if (!isset($campaignsForMails[$mainMailId])) { |
| 228 | 211 | $campaignsForMails[$mainMailId] = acym_loadObject('SELECT id AS campaign_id, parent_id FROM #__acym_campaign WHERE mail_id = '.intval($mainMailId)); |
| 229 | 212 | } |
| @@ -232,57 +215,53 @@ | ||
| 232 | 215 | $mails[$key]->parent_id = empty($campaignsForMails[$mainMailId]) ? 0 : $campaignsForMails[$mainMailId]->parent_id; |
| 233 | 216 | } |
| 234 | 217 | } |
| 235 | 218 | |
| 236 | - $results['detailed_stats'] = $mailClass->decode($mails); | |
| 237 | - $results['total'] = acym_loadResult($queryCount); | |
| 238 | - | |
| 239 | - return $results; | |
| 219 | + return [ | |
| 220 | + 'detailed_stats' => $mailClass->decode($mails), | |
| 221 | + 'total' => acym_loadObject($queryCount), | |
| 222 | + ]; | |
| 240 | 223 | } |
| 241 | 224 | |
| 242 | - public function getTotalFailClickOpenByMailIds($mailIds) | |
| 225 | + public function getOpenTimeStats(array $mailIds): array | |
| 243 | 226 | { |
| 244 | 227 | acym_arrayToInteger($mailIds); |
| 245 | - if (empty($mailIds)) return []; | |
| 228 | + $specificMailsCondition = empty($mailIds) ? '' : ' AND mail_id in ('.implode(',', $mailIds).')'; | |
| 229 | + $offsetSeconds = acym_getTimeOffsetCMS(); | |
| 246 | 230 | |
| 247 | - $query = 'SELECT mail_id, SUM(fail) AS fail, SUM(sent) AS sent, SUM(open) AS open FROM #__acym_user_stat WHERE mail_id IN ('.implode(',', $mailIds).') GROUP BY mail_id'; | |
| 248 | - | |
| 249 | - return acym_loadObjectList($query, 'mail_id'); | |
| 231 | + return [ | |
| 232 | + 'total_open' => acym_loadResult( | |
| 233 | + 'SELECT SUM(`open`) | |
| 234 | + FROM #__acym_user_stat | |
| 235 | + WHERE open_date IS NOT NULL '.$specificMailsCondition | |
| 236 | + ), | |
| 237 | + 'stats' => acym_loadObjectList( | |
| 238 | + 'SELECT | |
| 239 | + SUM(`open`) AS `open_total`, | |
| 240 | + DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%w") AS `day`, | |
| 241 | + FORMAT(CONVERT(DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%H"), SIGNED INTEGER) / 3, 0) AS `hour`, | |
| 242 | + CONCAT( | |
| 243 | + DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%w"), | |
| 244 | + "_", | |
| 245 | + FORMAT(CONVERT(DATE_FORMAT(DATE_ADD(open_date, INTERVAL '.$offsetSeconds.' SECOND), "%H"), SIGNED INTEGER) / 3, 0) | |
| 246 | + ) AS `date_id` | |
| 247 | + FROM `#__acym_user_stat` | |
| 248 | + WHERE open_date IS NOT NULL '.$specificMailsCondition.' | |
| 249 | + GROUP BY date_id', | |
| 250 | + 'date_id' | |
| 251 | + ), | |
| 252 | + ]; | |
| 250 | 253 | } |
| 251 | 254 | |
| 252 | - public function getUserWithNoMailOpen() | |
| 255 | + public function getDefaultStat(): array | |
| 253 | 256 | { |
| 254 | - $query = 'SELECT user_id FROM #__acym_user_stat GROUP BY user_id HAVING MAX(open) = 0'; | |
| 255 | - | |
| 256 | - return acym_loadResultArray($query); | |
| 257 | - } | |
| 258 | - | |
| 259 | - public function getOpenTimeStats($mailIds) | |
| 260 | - { | |
| 261 | - | |
| 262 | - if (!is_array($mailIds)) $mailIds = [$mailIds]; | |
| 263 | - acym_arrayToInteger($mailIds); | |
| 264 | - $where = empty($mailIds) ? '' : ' AND mail_id in ('.implode(',', $mailIds).')'; | |
| 265 | - | |
| 266 | - $query = 'SELECT SUM(`open`) AS open_total, DATE_FORMAT(open_date, "%w") AS day, FORMAT(CONVERT(DATE_FORMAT(open_date, "%H"), SIGNED INTEGER) / 3, 0) AS hour, CONCAT(DATE_FORMAT(open_date, "%w"), "_", FORMAT(CONVERT(DATE_FORMAT(open_date, "%H"), SIGNED INTEGER) / 3, 0)) AS date_id FROM `#__acym_user_stat` WHERE open_date IS NOT NULL '.$where.' GROUP BY date_id'; | |
| 267 | - | |
| 268 | - $return['total_open'] = acym_loadResult('SELECT SUM(`open`) FROM #__acym_user_stat WHERE open_date IS NOT NULL '.$where); | |
| 269 | - $return['stats'] = acym_loadObjectList($query, 'date_id'); | |
| 270 | - | |
| 271 | - return $return; | |
| 272 | - } | |
| 273 | - | |
| 274 | - public function getDefaultStat() | |
| 275 | - { | |
| 276 | 257 | $percentageRemaining = 100; |
| 277 | 258 | $stats = []; |
| 278 | 259 | |
| 279 | - | |
| 280 | - for ($day = 0 ; $day < 7 ; $day++) { | |
| 260 | + for ($day = 0; $day < 7; $day++) { | |
| 281 | 261 | $stats[$day] = []; |
| 282 | - for ($hour = 0 ; $hour < 8 ; $hour++) { | |
| 283 | - $hourPercentage = $this->getRandomStatOpenTime($percentageRemaining, $hour); | |
| 284 | - $stats[$day][$hour] = $hourPercentage; | |
| 262 | + for ($hour = 0; $hour < 8; $hour++) { | |
| 263 | + $stats[$day][$hour] = $this->getRandomStatOpenTime($percentageRemaining, $hour); | |
| 285 | 264 | } |
| 286 | 265 | } |
| 287 | 266 | |
| 288 | 267 | return $stats; |
| @@ -287,37 +266,10 @@ | ||
| 287 | 266 | |
| 288 | 267 | return $stats; |
| 289 | 268 | } |
| 290 | 269 | |
| 291 | - private function getRandomStatOpenTime(&$percentageRemaining, $hour) | |
| 270 | + public function getOpenSourcesStats(array $mailIds = []): array | |
| 292 | 271 | { |
| 293 | - if (empty($percentageRemaining)) return 0; | |
| 294 | - $randoms = [ | |
| 295 | - 0 => [0, 1], | |
| 296 | - 1 => [0, 2], | |
| 297 | - 2 => [1, 2], | |
| 298 | - 3 => [1, 3], | |
| 299 | - 4 => [3, 5], | |
| 300 | - 5 => [1, 3], | |
| 301 | - 6 => [1, 2], | |
| 302 | - 7 => [0, 2], | |
| 303 | - ]; | |
| 304 | - | |
| 305 | - $percentage = rand($randoms[$hour][0], $randoms[$hour][1]); | |
| 306 | - | |
| 307 | - if ($percentageRemaining - $percentage < 0) return 0; | |
| 308 | - | |
| 309 | - | |
| 310 | - $percentageRemaining -= $percentage; | |
| 311 | - | |
| 312 | - return $percentage; | |
| 313 | - } | |
| 314 | - | |
| 315 | - public function getOpenSourcesStats($mailIds = []) | |
| 316 | - { | |
| 317 | - if (!is_array($mailIds)) { | |
| 318 | - $mailIds = [$mailIds]; | |
| 319 | - } | |
| 320 | 272 | acym_arrayToInteger($mailIds); |
| 321 | 273 | |
| 322 | 274 | $query = 'SELECT opened_with, COUNT(*) AS number FROM #__acym_user_stat WHERE `open` > 0'; |
| 323 | 275 | if (!empty($mailIds)) { |
| @@ -339,9 +291,9 @@ | ||
| 339 | 291 | if (empty($deleteAfterXSeconds)) { |
| 340 | 292 | return []; |
| 341 | 293 | } |
| 342 | 294 | |
| 343 | - $date = acym_date(time() - $deleteAfterXSeconds, 'Y-m-d H:i'); | |
| 295 | + $date = acym_date(time() - $deleteAfterXSeconds, 'Y-m-d H:i', false); | |
| 344 | 296 | |
| 345 | 297 | $queries = [ |
| 346 | 298 | '#__acym_user_stat' => 'DELETE FROM #__acym_user_stat WHERE send_date < '.acym_escapeDB($date), |
| 347 | 299 | '#__acym_url_click' => 'DELETE FROM #__acym_url_click WHERE date_click < '.acym_escapeDB($date), |
| @@ -363,6 +315,46 @@ | ||
| 363 | 315 | return []; |
| 364 | 316 | } |
| 365 | 317 | |
| 366 | 318 | return ['message' => implode("\r\n", $messages)]; |
| 319 | + } | |
| 320 | + | |
| 321 | + private function getRandomStatOpenTime(int &$percentageRemaining, int $hour): int | |
| 322 | + { | |
| 323 | + if (empty($percentageRemaining)) { | |
| 324 | + return 0; | |
| 325 | + } | |
| 326 | + | |
| 327 | + $randoms = [ | |
| 328 | + 0 => [0, 1], | |
| 329 | + 1 => [0, 2], | |
| 330 | + 2 => [1, 2], | |
| 331 | + 3 => [1, 3], | |
| 332 | + 4 => [3, 5], | |
| 333 | + 5 => [1, 3], | |
| 334 | + 6 => [1, 2], | |
| 335 | + 7 => [0, 2], | |
| 336 | + ]; | |
| 337 | + | |
| 338 | + $percentage = acym_rand($randoms[$hour][0], $randoms[$hour][1]); | |
| 339 | + | |
| 340 | + if ($percentageRemaining - $percentage < 0) { | |
| 341 | + return 0; | |
| 342 | + } | |
| 343 | + | |
| 344 | + $percentageRemaining -= $percentage; | |
| 345 | + | |
| 346 | + return $percentage; | |
| 347 | + } | |
| 348 | + | |
| 349 | + public function hasUserReceivedMail(int $mailId, int $userId): bool | |
| 350 | + { | |
| 351 | + $sentToRecipient = acym_loadResult( | |
| 352 | + 'SELECT COUNT(*) | |
| 353 | + FROM #__acym_user_stat AS userStat | |
| 354 | + WHERE userStat.mail_id = '.intval($mailId).' | |
| 355 | + AND userStat.user_id = '.intval($userId) | |
| 356 | + ); | |
| 357 | + | |
| 358 | + return !empty($sentToRecipient); | |
| 367 | 359 | } |
| 368 | 360 | } |
