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