| @@ -1,266 +1,0 @@ | ||
| 1 | -<?php | |
| 2 | - | |
| 3 | -namespace AcyMailing\Classes; | |
| 4 | - | |
| 5 | -use AcyMailing\Core\AcymClass; | |
| 6 | - | |
| 7 | -class MailStatClass extends AcymClass | |
| 8 | -{ | |
| 9 | - public function __construct() | |
| 10 | - { | |
| 11 | - parent::__construct(); | |
| 12 | - | |
| 13 | - $this->table = 'mail_stat'; | |
| 14 | - $this->pkey = 'mail_id'; | |
| 15 | - } | |
| 16 | - | |
| 17 | - public function save($element): ?int | |
| 18 | - { | |
| 19 | - $mailStat = is_object($element) ? get_object_vars($element) : $element; | |
| 20 | - | |
| 21 | - $column = []; | |
| 22 | - $valueColumn = []; | |
| 23 | - $columnName = acym_getColumns('mail_stat'); | |
| 24 | - | |
| 25 | - foreach ($mailStat as $key => $value) { | |
| 26 | - if (in_array($key, $columnName)) { | |
| 27 | - $column[] = '`'.acym_secureDBColumn($key).'`'; | |
| 28 | - | |
| 29 | - if ($key === 'tracking_sale') { | |
| 30 | - $valueColumn[] = is_null($value) || strlen($value) === 0 ? 'NULL' : floatval($value); | |
| 31 | - } else { | |
| 32 | - $valueColumn[] = acym_escapeDB($value); | |
| 33 | - } | |
| 34 | - } | |
| 35 | - } | |
| 36 | - | |
| 37 | - $query = '#__acym_mail_stat ('.implode(',', $column).') VALUES ('.implode(', ', $valueColumn).')'; | |
| 38 | - | |
| 39 | - $onDuplicate = []; | |
| 40 | - | |
| 41 | - if (isset($mailStat['sent'])) { | |
| 42 | - $onDuplicate[] = ' sent = sent + '.intval($mailStat['sent']); | |
| 43 | - } | |
| 44 | - | |
| 45 | - if (isset($mailStat['fail'])) { | |
| 46 | - $onDuplicate[] = ' fail = fail + '.intval($mailStat['fail']); | |
| 47 | - } | |
| 48 | - | |
| 49 | - if (isset($mailStat['open_unique'])) { | |
| 50 | - $onDuplicate[] = 'open_unique = open_unique + '.intval($mailStat['open_unique']); | |
| 51 | - } | |
| 52 | - | |
| 53 | - if (isset($mailStat['open_total'])) { | |
| 54 | - $onDuplicate[] = 'open_total = open_total + '.intval($mailStat['open_total']); | |
| 55 | - } | |
| 56 | - | |
| 57 | - if (isset($mailStat['total_subscribers'])) { | |
| 58 | - $onDuplicate[] = 'total_subscribers = total_subscribers + '.intval($mailStat['total_subscribers']); | |
| 59 | - } | |
| 60 | - | |
| 61 | - if (isset($mailStat['unsubscribe_total'])) { | |
| 62 | - $onDuplicate[] = ' unsubscribe_total = unsubscribe_total + '.intval($mailStat['unsubscribe_total']); | |
| 63 | - } | |
| 64 | - | |
| 65 | - if (isset($mailStat['tracking_sale'])) { | |
| 66 | - // Cannot be changed as it is not guaranteed that the plugin is updated at the same time | |
| 67 | - $onDuplicate[] = 'tracking_sale = '.floatval($mailStat['tracking_sale']); | |
| 68 | - } | |
| 69 | - | |
| 70 | - if (isset($mailStat['currency'])) { | |
| 71 | - $onDuplicate[] = 'currency = '.acym_escapeDB($mailStat['currency']); | |
| 72 | - } | |
| 73 | - | |
| 74 | - if (!empty($onDuplicate)) { | |
| 75 | - $query .= ' ON DUPLICATE KEY UPDATE '; | |
| 76 | - $query .= implode(',', $onDuplicate); | |
| 77 | - $query = 'INSERT INTO '.$query; | |
| 78 | - } else { | |
| 79 | - $query = 'INSERT IGNORE INTO '.$query; | |
| 80 | - } | |
| 81 | - | |
| 82 | - return (int)acym_query($query); | |
| 83 | - } | |
| 84 | - | |
| 85 | - public function getTotalSubscribersByMailId(int $mailId): int | |
| 86 | - { | |
| 87 | - $result = acym_loadResult('SELECT total_subscribers FROM #__acym_mail_stat WHERE mail_id = '.intval($mailId)); | |
| 88 | - | |
| 89 | - return empty($result) ? 0 : $result; | |
| 90 | - } | |
| 91 | - | |
| 92 | - public function getTotalSubscribersByMailIdWithChild(int $mailId): int | |
| 93 | - { | |
| 94 | - $result = acym_loadResult( | |
| 95 | - 'SELECT SUM(total_subscribers) FROM #__acym_mail_stat WHERE mail_id IN (SELECT id FROM #__acym_mail WHERE id = '.intval($mailId).' OR parent_id = '.intval($mailId).')' | |
| 96 | - ); | |
| 97 | - | |
| 98 | - return empty($result) ? 0 : $result; | |
| 99 | - } | |
| 100 | - | |
| 101 | - public function getOneByMailId(int $id): ?object | |
| 102 | - { | |
| 103 | - $query = 'SELECT SUM(sent) AS sent, SUM(fail) AS fail FROM #__acym_mail_stat'; | |
| 104 | - $query .= empty($id) ? '' : ' WHERE `mail_id` = '.intval($id); | |
| 105 | - | |
| 106 | - $stats = acym_loadObject($query); | |
| 107 | - | |
| 108 | - return empty($stats) ? null : $stats; | |
| 109 | - } | |
| 110 | - | |
| 111 | - public function getSentFailByMailIds(array $mailIds = []): object | |
| 112 | - { | |
| 113 | - acym_arrayToInteger($mailIds); | |
| 114 | - | |
| 115 | - $query = 'SELECT SUM(sent) AS sent, SUM(fail) AS fail FROM #__acym_mail_stat'; | |
| 116 | - $query .= empty($mailIds) ? '' : ' WHERE `mail_id` IN ('.implode(',', $mailIds).')'; | |
| 117 | - | |
| 118 | - return acym_loadObject($query); | |
| 119 | - } | |
| 120 | - | |
| 121 | - public function getOneRowByMailId(int $mailId): ?object | |
| 122 | - { | |
| 123 | - $stats = acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE mail_id = '.intval($mailId)); | |
| 124 | - | |
| 125 | - return empty($stats) ? null : $stats; | |
| 126 | - } | |
| 127 | - | |
| 128 | - public function getAllMailsForStats(string $search = ''): array | |
| 129 | - { | |
| 130 | - $mailClass = new MailClass(); | |
| 131 | - | |
| 132 | - $query = 'SELECT mail.* | |
| 133 | - FROM #__acym_mail AS mail | |
| 134 | - JOIN #__acym_mail_stat AS mail_stat ON mail.id = mail_stat.mail_id'; | |
| 135 | - | |
| 136 | - $queryAutoCampaign = 'SELECT mail.* FROM #__acym_mail AS mail | |
| 137 | - JOIN #__acym_campaign as campaign ON campaign.mail_id = mail.id AND campaign.sending_type = '.acym_escapeDB(CampaignClass::SENDING_TYPE_AUTO); | |
| 138 | - | |
| 139 | - $querySearch = ''; | |
| 140 | - | |
| 141 | - if (!empty($search)) { | |
| 142 | - $querySearch .= ' mail.name LIKE '.acym_escapeDB('%'.acym_utf8Encode($search).'%').' '; | |
| 143 | - $queryAutoCampaign .= ' WHERE '.$querySearch; | |
| 144 | - | |
| 145 | - $querySearch = ' AND '.$querySearch; | |
| 146 | - } | |
| 147 | - | |
| 148 | - $query .= ' WHERE mail.parent_id IS NULL '.$querySearch; | |
| 149 | - $query .= ' ORDER BY mail_stat.send_date DESC LIMIT 20'; | |
| 150 | - | |
| 151 | - $mails = acym_loadObjectList($query); | |
| 152 | - $mailsAuto = acym_loadObjectList($queryAutoCampaign); | |
| 153 | - | |
| 154 | - return $mailClass->decode(array_merge($mails, $mailsAuto)); | |
| 155 | - } | |
| 156 | - | |
| 157 | - public function getCumulatedStatsByMailIds(array $mailsIds = []): object | |
| 158 | - { | |
| 159 | - acym_arrayToInteger($mailsIds); | |
| 160 | - $condMailIds = ''; | |
| 161 | - if (!empty($mailsIds)) { | |
| 162 | - $condMailIds = 'WHERE mail_id IN ('.implode(',', $mailsIds).')'; | |
| 163 | - } | |
| 164 | - | |
| 165 | - $query = 'SELECT SUM(sent) AS sent, SUM(open_unique) AS open, SUM(fail) AS fails, SUM(bounce_unique) AS bounces FROM #__acym_mail_stat '.$condMailIds; | |
| 166 | - | |
| 167 | - return acym_loadObject($query); | |
| 168 | - } | |
| 169 | - | |
| 170 | - public function getByMailIds(array $mailIds): ?object | |
| 171 | - { | |
| 172 | - acym_arrayToInteger($mailIds); | |
| 173 | - | |
| 174 | - if (empty($mailIds)) { | |
| 175 | - return null; | |
| 176 | - } | |
| 177 | - | |
| 178 | - return acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE mail_id IN ('.implode(',', $mailIds).')'); | |
| 179 | - } | |
| 180 | - | |
| 181 | - public function migrateTrackingSale(): void | |
| 182 | - { | |
| 183 | - $query = 'SELECT tracking_sale, currency, mail_id FROM #__acym_user_stat WHERE currency IS NOT NULL'; | |
| 184 | - | |
| 185 | - $trackingSales = acym_loadObjectList($query); | |
| 186 | - | |
| 187 | - if (empty($trackingSales)) return; | |
| 188 | - | |
| 189 | - $mailStats = []; | |
| 190 | - | |
| 191 | - foreach ($trackingSales as $sale) { | |
| 192 | - if (empty($mailStats[$sale->mail_id])) { | |
| 193 | - $mailStats[$sale->mail_id] = new \stdClass(); | |
| 194 | - $mailStats[$sale->mail_id]->mail_id = $sale->mail_id; | |
| 195 | - $mailStats[$sale->mail_id]->tracking_sale = $sale->tracking_sale; | |
| 196 | - $mailStats[$sale->mail_id]->currency = $sale->currency; | |
| 197 | - } else { | |
| 198 | - $mailStats[$sale->mail_id]->tracking_sale += $sale->tracking_sale; | |
| 199 | - } | |
| 200 | - } | |
| 201 | - | |
| 202 | - foreach ($mailStats as $mailStat) { | |
| 203 | - $this->save($mailStat); | |
| 204 | - } | |
| 205 | - } | |
| 206 | - | |
| 207 | - public function getBestEmailByStats(array $mailIds, string $statsType): array | |
| 208 | - { | |
| 209 | - acym_arrayToInteger($mailIds); | |
| 210 | - | |
| 211 | - $mailStats = acym_loadObjectList( | |
| 212 | - 'SELECT ms.total_subscribers, ms.mail_id, ms.open_total, ms.click_total | |
| 213 | - FROM #__acym_mail_stat AS ms | |
| 214 | - WHERE ms.mail_id IN ('.implode(',', $mailIds).')', | |
| 215 | - 'mail_id' | |
| 216 | - ); | |
| 217 | - | |
| 218 | - foreach ($mailStats as $mailStat) { | |
| 219 | - $mailStat->click_rate = $mailStat->open_total > 0 ? $mailStat->click_total / $mailStat->open_total : 0; | |
| 220 | - $mailStat->open_rate = $mailStat->total_subscribers > 0 ? $mailStat->open_total / $mailStat->total_subscribers : 0; | |
| 221 | - } | |
| 222 | - | |
| 223 | - if ($statsType === 'click_rate') { | |
| 224 | - return ['click_rate' => $this->getBestMailByRate($mailStats, 'click_rate')]; | |
| 225 | - } elseif ($statsType === 'open_rate') { | |
| 226 | - return ['open_rate' => $this->getBestMailByRate($mailStats, 'open_rate')]; | |
| 227 | - } else { | |
| 228 | - return [ | |
| 229 | - 'click_rate' => $this->getBestMailByRate($mailStats, 'click_rate'), | |
| 230 | - 'open_rate' => $this->getBestMailByRate($mailStats, 'open_rate'), | |
| 231 | - ]; | |
| 232 | - } | |
| 233 | - } | |
| 234 | - | |
| 235 | - private function getBestMailByRate(array $mailStats, string $type): int | |
| 236 | - { | |
| 237 | - uasort( | |
| 238 | - $mailStats, | |
| 239 | - function ($a, $b) use ($type) { | |
| 240 | - if ($a->$type == $b->$type) { | |
| 241 | - return 0; | |
| 242 | - } | |
| 243 | - | |
| 244 | - return ($a->$type < $b->$type) ? 1 : -1; | |
| 245 | - } | |
| 246 | - ); | |
| 247 | - | |
| 248 | - return array_keys($mailStats)[0]; | |
| 249 | - } | |
| 250 | - | |
| 251 | - public function incrementClicks(int $mailId, bool $isFirst): void | |
| 252 | - { | |
| 253 | - acym_query( | |
| 254 | - 'UPDATE #__acym_mail_stat | |
| 255 | - SET click_total = click_total + 1'.($isFirst ? ', click_unique = click_unique + 1' : '').' | |
| 256 | - WHERE mail_id = '.intval($mailId) | |
| 257 | - ); | |
| 258 | - } | |
| 259 | - | |
| 260 | - public function getOneSentEmail(): bool | |
| 261 | - { | |
| 262 | - $firstSentEmail = acym_loadObject('SELECT * FROM #__acym_mail_stat WHERE sent > 0'); | |
| 263 | - | |
| 264 | - return !empty($firstSentEmail); | |
| 265 | - } | |
| 266 | -} | |
