| @@ -1,709 +1,0 @@ | ||
| 1 | -<?php | |
| 2 | - | |
| 3 | -namespace AcyMailing\Classes; | |
| 4 | - | |
| 5 | -use AcyMailing\Controllers\SegmentsController; | |
| 6 | -use AcyMailing\Helpers\AutomationHelper; | |
| 7 | -use AcyMailing\Core\AcymClass; | |
| 8 | - | |
| 9 | -class QueueClass extends AcymClass | |
| 10 | -{ | |
| 11 | - public array $emailtypes = []; | |
| 12 | - | |
| 13 | - public function getMatchingCampaigns(array $settings): array | |
| 14 | - { | |
| 15 | - $queuedMails = acym_loadResultArray('SELECT DISTINCT mail_id FROM #__acym_queue'); | |
| 16 | - if (empty($queuedMails)) { | |
| 17 | - return [ | |
| 18 | - 'elements' => [], | |
| 19 | - 'total' => (object)['total' => 0], | |
| 20 | - 'status' => [ | |
| 21 | - 'all' => 0, | |
| 22 | - 'sending' => 0, | |
| 23 | - 'paused' => 0, | |
| 24 | - 'automation' => 0, | |
| 25 | - 'followup' => 0, | |
| 26 | - ], | |
| 27 | - ]; | |
| 28 | - } | |
| 29 | - | |
| 30 | - $campaignClass = new CampaignClass(); | |
| 31 | - $mailStatClass = new MailStatClass(); | |
| 32 | - $mailClass = new MailClass(); | |
| 33 | - | |
| 34 | - $queryCount = 'SELECT COUNT(DISTINCT mail.id) AS total | |
| 35 | - FROM #__acym_mail AS mail | |
| 36 | - LEFT JOIN #__acym_campaign AS campaign ON campaign.mail_id = mail.id OR campaign.mail_id = mail.parent_id'; | |
| 37 | - | |
| 38 | - $query = 'SELECT | |
| 39 | - mail.name, | |
| 40 | - mail.subject, | |
| 41 | - mail.type, | |
| 42 | - mail.id, | |
| 43 | - campaign.id AS campaign, | |
| 44 | - COALESCE(campaign.sending_date, queue.min_sending_date) AS sending_date, | |
| 45 | - campaign.sending_type, | |
| 46 | - campaign.active, | |
| 47 | - campaign.sending_params AS sending_params, | |
| 48 | - queue.nbqueued, | |
| 49 | - mail.language, | |
| 50 | - mail.parent_id | |
| 51 | - FROM #__acym_mail AS mail | |
| 52 | - JOIN ( | |
| 53 | - SELECT mail_id, COUNT(*) AS nbqueued, MIN(sending_date) AS min_sending_date | |
| 54 | - FROM #__acym_queue | |
| 55 | - GROUP BY mail_id | |
| 56 | - ) AS queue ON mail.id = queue.mail_id | |
| 57 | - LEFT JOIN #__acym_campaign AS campaign | |
| 58 | - ON mail.id = campaign.mail_id | |
| 59 | - OR mail.parent_id = campaign.mail_id'; | |
| 60 | - | |
| 61 | - $queryStatus = 'SELECT COUNT(DISTINCT mail.id) AS number, campaign.active | |
| 62 | - FROM #__acym_mail AS mail | |
| 63 | - LEFT JOIN #__acym_campaign AS campaign ON mail.id = campaign.mail_id'; | |
| 64 | - | |
| 65 | - if (!empty($settings['tag'])) { | |
| 66 | - $tagJoin = ' JOIN #__acym_tag AS tag | |
| 67 | - ON (mail.id = tag.id_element OR mail.parent_id = tag.id_element) | |
| 68 | - AND tag.type = "mail" | |
| 69 | - AND tag.name = '.acym_escapeDB($settings['tag']); | |
| 70 | - $query .= $tagJoin; | |
| 71 | - $queryCount .= $tagJoin; | |
| 72 | - $queryStatus .= ' JOIN #__acym_tag AS tag ON mail.id = tag.id_element AND tag.type = "mail" AND tag.name = '.acym_escapeDB($settings['tag']); | |
| 73 | - } | |
| 74 | - | |
| 75 | - $filters = []; | |
| 76 | - $filters[] = 'campaign.id IS NULL OR campaign.draft = 0'; | |
| 77 | - | |
| 78 | - if (!empty($settings['search'])) { | |
| 79 | - $filters[] = 'mail.subject LIKE '.acym_escapeDB('%'.$settings['search'].'%').' OR mail.name LIKE '.acym_escapeDB('%'.$settings['search'].'%'); | |
| 80 | - } | |
| 81 | - | |
| 82 | - $queryStatus .= ' WHERE ('.implode(') AND (', $filters).') AND mail.id IN ('.implode(',', $queuedMails).')'; | |
| 83 | - | |
| 84 | - if (!empty($settings['status'])) { | |
| 85 | - $allowedStatus = [ | |
| 86 | - 'sending' => 'campaign.active = 1', | |
| 87 | - 'paused' => 'campaign.active = 0', | |
| 88 | - 'automation' => 'mail.type = '.acym_escapeDB(MailClass::TYPE_AUTOMATION), | |
| 89 | - 'followup' => 'mail.type = '.acym_escapeDB(MailClass::TYPE_FOLLOWUP), | |
| 90 | - ]; | |
| 91 | - | |
| 92 | - if (empty($allowedStatus[$settings['status']])) { | |
| 93 | - die('Unauthorized filter: '.acym_escape($settings['status'])); | |
| 94 | - } | |
| 95 | - | |
| 96 | - $filters[] = $allowedStatus[$settings['status']]; | |
| 97 | - } | |
| 98 | - | |
| 99 | - $queryCount .= ' WHERE ('.implode(') AND (', $filters).') AND mail.id IN ('.implode(',', $queuedMails).')'; | |
| 100 | - $query .= ' WHERE ('.implode(') AND (', $filters).')'; | |
| 101 | - $query .= ' ORDER BY queue.min_sending_date ASC'; | |
| 102 | - | |
| 103 | - acym_query('SET SQL_BIG_SELECTS=1;'); | |
| 104 | - $results = [ | |
| 105 | - 'elements' => $mailClass->decode(acym_loadObjectList($query, '', $settings['offset'], $settings['campaignsPerPage'])), | |
| 106 | - 'total' => acym_loadObject($queryCount), | |
| 107 | - ]; | |
| 108 | - | |
| 109 | - $isMultilingual = acym_isMultilingual(); | |
| 110 | - $campaignRecipientsMultilingual = []; | |
| 111 | - $automationHelper = new AutomationHelper(); | |
| 112 | - | |
| 113 | - $specialTypes = []; | |
| 114 | - acym_trigger('getCampaignTypes', [&$specialTypes]); | |
| 115 | - | |
| 116 | - foreach ($results['elements'] as $i => $oneMail) { | |
| 117 | - $results['elements'][$i]->sending_params = empty($oneMail->sending_params) ? [] : json_decode($oneMail->sending_params, true); | |
| 118 | - if (in_array($oneMail->sending_type, $specialTypes)) { | |
| 119 | - $results['elements'][$i]->iscampaign = false; | |
| 120 | - $results['elements'][$i]->lists = acym_translation('ACYM_SPECIAL_MAIL_SENT_TO'); | |
| 121 | - $results['elements'][$i]->recipients = acym_loadResult('SELECT COUNT(*) FROM #__acym_queue WHERE mail_id = '.intval($oneMail->id)); | |
| 122 | - } elseif (empty($oneMail->campaign)) { | |
| 123 | - $results['elements'][$i]->iscampaign = false; | |
| 124 | - if ($oneMail->type === MailClass::TYPE_FOLLOWUP) { | |
| 125 | - $results['elements'][$i]->lists = acym_translation('ACYM_MAIL_FROM_FOLLOWUP_SENT_TO'); | |
| 126 | - } else { | |
| 127 | - $results['elements'][$i]->lists = acym_translation('ACYM_MAIL_FROM_AUTOMATION_SENT_TO'); | |
| 128 | - } | |
| 129 | - $results['elements'][$i]->recipients = acym_loadResult('SELECT COUNT(*) FROM #__acym_queue WHERE mail_id = '.intval($oneMail->id)); | |
| 130 | - } else { | |
| 131 | - $mailId = empty($oneMail->parent_id) ? $oneMail->id : $oneMail->parent_id; | |
| 132 | - $results['elements'][$i]->iscampaign = true; | |
| 133 | - $results['elements'][$i]->lists = acym_loadObjectList( | |
| 134 | - 'SELECT l.color, l.name , l.id | |
| 135 | - FROM #__acym_list AS l | |
| 136 | - JOIN #__acym_mail_has_list AS ml ON ml.list_id = l.id | |
| 137 | - WHERE ml.mail_id = '.intval($mailId), | |
| 138 | - 'id' | |
| 139 | - ); | |
| 140 | - | |
| 141 | - if (isset($results['elements'][$i]->sending_params['abtest'])) { | |
| 142 | - $isVersionB = $results['elements'][$i]->sending_params['abtest']['B'] == $results['elements'][$i]->id; | |
| 143 | - | |
| 144 | - if (empty($results['elements'][$i]->parent_id) || $isVersionB) { | |
| 145 | - $results['elements'][$i]->recipients = intval($mailStatClass->getTotalSubscribersByMailId($results['elements'][$i]->id)); | |
| 146 | - } else { | |
| 147 | - $results['elements'][$i]->recipients = intval($mailStatClass->getTotalSubscribersByMailIdWithChild($mailId)); | |
| 148 | - } | |
| 149 | - } elseif ($isMultilingual) { | |
| 150 | - if (empty($campaignRecipientsMultilingual[$oneMail->campaign])) { | |
| 151 | - $listIds = array_keys($results['elements'][$i]->lists); | |
| 152 | - acym_arrayToInteger($listIds); | |
| 153 | - | |
| 154 | - $automationHelper->join['user_list'] = ' #__acym_user_has_list AS user_list ON user_list.user_id = user.id AND user_list.list_id IN ('.implode( | |
| 155 | - ',', | |
| 156 | - $listIds | |
| 157 | - ).') and user_list.status = 1 '; | |
| 158 | - $automationHelper->leftjoin['mail'] = '`#__acym_mail` AS mail ON `mail`.`language` = `user`.language AND `mail`.`parent_id` = '.intval($mailId); | |
| 159 | - $automationHelper->where[] = '`user_list`.`list_id` IN ('.implode(',', $listIds).') AND `user_list`.`status` = 1'; | |
| 160 | - | |
| 161 | - $filters = $campaignClass->getFilterCampaign($oneMail->sending_params); | |
| 162 | - if (!empty($filters)) { | |
| 163 | - foreach ($filters as $orValues) { | |
| 164 | - if (empty($orValues)) continue; | |
| 165 | - | |
| 166 | - $automationHelperSegment = new AutomationHelper(); | |
| 167 | - foreach ($orValues as $and => $andValues) { | |
| 168 | - $and = intval($and); | |
| 169 | - foreach ($andValues as $filterName => $options) { | |
| 170 | - acym_trigger('onAcymProcessFilter_'.$filterName, [&$automationHelperSegment, &$options, &$and]); | |
| 171 | - } | |
| 172 | - } | |
| 173 | - $automationHelperSegment->addFlag(SegmentsController::FLAG_COUNT); | |
| 174 | - } | |
| 175 | - | |
| 176 | - $segmentMatchingCondition = 'LIKE'; | |
| 177 | - if (!empty($oneMail->sending_params['segment']['invert']) && $oneMail->sending_params['segment']['invert'] === 'exclude') { | |
| 178 | - $segmentMatchingCondition = 'NOT LIKE'; | |
| 179 | - } | |
| 180 | - $automationHelper->where[] = 'user.automation '.$segmentMatchingCondition.' "%a'.intval(SegmentsController::FLAG_COUNT).'a%"'; | |
| 181 | - } | |
| 182 | - | |
| 183 | - $automationHelper->groupBy = 'mail_id'; | |
| 184 | - $campaignRecipientsMultilingual[$oneMail->campaign] = acym_loadObjectList( | |
| 185 | - $automationHelper->getQuery(['COUNT(DISTINCT user_list.`user_id`) AS elements', 'IF(mail.id IS NULL, '.intval($mailId).', `mail`.`id`) AS mail_id']), | |
| 186 | - 'mail_id' | |
| 187 | - ); | |
| 188 | - $automationHelper->removeFlag(SegmentsController::FLAG_COUNT); | |
| 189 | - } | |
| 190 | - $results['elements'][$i]->recipients = intval($campaignRecipientsMultilingual[$oneMail->campaign][$oneMail->id]->elements); | |
| 191 | - } else { | |
| 192 | - $results['elements'][$i]->recipients = intval($mailStatClass->getTotalSubscribersByMailId($mailId)); | |
| 193 | - } | |
| 194 | - } | |
| 195 | - } | |
| 196 | - | |
| 197 | - $automationNumber = acym_loadResult( | |
| 198 | - 'SELECT COUNT(DISTINCT mail.id) FROM #__acym_mail AS mail | |
| 199 | - JOIN #__acym_queue AS queue | |
| 200 | - ON mail.id = queue.mail_id | |
| 201 | - WHERE mail.type = '.acym_escapeDB(MailClass::TYPE_AUTOMATION) | |
| 202 | - ); | |
| 203 | - $followupNumber = acym_loadResult( | |
| 204 | - 'SELECT COUNT(DISTINCT mail.id) | |
| 205 | - FROM #__acym_mail AS mail | |
| 206 | - JOIN #__acym_queue AS queue | |
| 207 | - ON mail.id = queue.mail_id | |
| 208 | - WHERE mail.type = '.acym_escapeDB(MailClass::TYPE_FOLLOWUP) | |
| 209 | - ); | |
| 210 | - | |
| 211 | - $elementsPerStatus = acym_loadObjectList($queryStatus.' GROUP BY active'); | |
| 212 | - $queuedActiveCampaigns = 0; | |
| 213 | - $queuedPausedCampaigns = 0; | |
| 214 | - foreach ($elementsPerStatus as $element) { | |
| 215 | - if (is_null($element->active)) { | |
| 216 | - continue; | |
| 217 | - } | |
| 218 | - | |
| 219 | - if (!empty($element->active)) { | |
| 220 | - $queuedActiveCampaigns = intval($element->number); | |
| 221 | - } else { | |
| 222 | - $queuedPausedCampaigns = intval($element->number); | |
| 223 | - } | |
| 224 | - } | |
| 225 | - | |
| 226 | - $results['status'] = [ | |
| 227 | - 'all' => $queuedActiveCampaigns + $queuedPausedCampaigns + $automationNumber + $followupNumber, | |
| 228 | - 'sending' => $queuedActiveCampaigns, | |
| 229 | - 'paused' => $queuedPausedCampaigns, | |
| 230 | - 'automation' => $automationNumber, | |
| 231 | - 'followup' => $followupNumber, | |
| 232 | - ]; | |
| 233 | - | |
| 234 | - return $results; | |
| 235 | - } | |
| 236 | - | |
| 237 | - public function getMatchingScheduledCampaigns(array $settings): array | |
| 238 | - { | |
| 239 | - $mailClass = new MailClass(); | |
| 240 | - $query = 'FROM #__acym_mail AS mail | |
| 241 | - JOIN #__acym_campaign AS campaign ON mail.id = campaign.mail_id OR mail.parent_id = campaign.mail_id '; | |
| 242 | - | |
| 243 | - if (!empty($settings['tag'])) { | |
| 244 | - $query .= ' JOIN #__acym_tag AS tag ON mail.id = tag.id_element AND tag.type = "mail" AND tag.name = '.acym_escapeDB($settings['tag']); | |
| 245 | - } | |
| 246 | - | |
| 247 | - $filters = [ | |
| 248 | - 'campaign.draft = 0', | |
| 249 | - 'campaign.sent = 0', | |
| 250 | - 'campaign.sending_type = '.acym_escapeDB(CampaignClass::SENDING_TYPE_SCHEDULED), | |
| 251 | - ]; | |
| 252 | - | |
| 253 | - if (!empty($settings['search'])) { | |
| 254 | - $filters[] = 'mail.subject LIKE '.acym_escapeDB('%'.$settings['search'].'%').' OR mail.name LIKE '.acym_escapeDB('%'.$settings['search'].'%'); | |
| 255 | - } | |
| 256 | - | |
| 257 | - $query .= ' WHERE ('.implode(') AND (', $filters).')'; | |
| 258 | - | |
| 259 | - $queryCount = 'SELECT COUNT(DISTINCT mail.id) AS total '.$query; | |
| 260 | - $query = 'SELECT mail.name, mail.subject, mail.id, campaign.sending_date, campaign.sending_params, mail.language, mail.parent_id '.$query.' GROUP BY mail.id ORDER BY campaign.sending_date ASC'; | |
| 261 | - | |
| 262 | - acym_query('SET SQL_BIG_SELECTS=1;'); | |
| 263 | - $results['elements'] = $mailClass->decode(acym_loadObjectList($query, '', $settings['offset'], $settings['campaignsPerPage'])); | |
| 264 | - $results['total'] = acym_loadObject($queryCount); | |
| 265 | - | |
| 266 | - foreach ($results['elements'] as $i => $oneMail) { | |
| 267 | - $results['elements'][$i]->sending_params = empty($oneMail->sending_params) ? [] : json_decode($oneMail->sending_params, true); | |
| 268 | - $mailId = empty($oneMail->parent_id) ? $oneMail->id : $oneMail->parent_id; | |
| 269 | - $results['elements'][$i]->lists = acym_loadObjectList( | |
| 270 | - 'SELECT l.color, l.name, l.id | |
| 271 | - FROM #__acym_list AS l | |
| 272 | - JOIN #__acym_mail_has_list AS ml ON ml.list_id = l.id | |
| 273 | - WHERE ml.mail_id = '.intval($mailId), | |
| 274 | - 'id' | |
| 275 | - ); | |
| 276 | - } | |
| 277 | - | |
| 278 | - return $results; | |
| 279 | - } | |
| 280 | - | |
| 281 | - public function getMatchingResults(array $settings): array | |
| 282 | - { | |
| 283 | - $query = 'FROM #__acym_queue AS queue | |
| 284 | - JOIN #__acym_mail AS mail ON mail.id = queue.mail_id | |
| 285 | - JOIN #__acym_user AS user ON queue.user_id = user.id '; | |
| 286 | - | |
| 287 | - $filters = []; | |
| 288 | - | |
| 289 | - if (!empty($settings['tag'])) { | |
| 290 | - $query .= ' JOIN #__acym_tag AS tag ON queue.mail_id = tag.id_element AND tag.type = "mail" AND tag.name = '.acym_escapeDB($settings['tag']); | |
| 291 | - } | |
| 292 | - | |
| 293 | - if (!empty($settings['search'])) { | |
| 294 | - $searchColumns = [ | |
| 295 | - 'user.email', | |
| 296 | - 'user.name', | |
| 297 | - 'mail.subject', | |
| 298 | - 'mail.name', | |
| 299 | - ]; | |
| 300 | - | |
| 301 | - $filters[] = implode(' LIKE '.acym_escapeDB('%'.$settings['search'].'%').' OR ', $searchColumns).' LIKE '.acym_escapeDB('%'.$settings['search'].'%'); | |
| 302 | - } | |
| 303 | - | |
| 304 | - if (!empty($filters)) { | |
| 305 | - $query .= ' WHERE ('.implode(') AND (', $filters).')'; | |
| 306 | - } | |
| 307 | - | |
| 308 | - if (!empty($settings['tag'])) { | |
| 309 | - $query .= ' GROUP BY queue.mail_id, queue.user_id'; | |
| 310 | - } | |
| 311 | - | |
| 312 | - $queryCount = 'SELECT COUNT(queue.mail_id) AS total '.$query; | |
| 313 | - $query = 'SELECT mail.id, queue.sending_date, mail.name, mail.subject, user.email, user.name AS user_name, queue.user_id, queue.try '.$query.' ORDER BY queue.sending_date ASC'; | |
| 314 | - | |
| 315 | - $mailClass = new MailClass(); | |
| 316 | - | |
| 317 | - return [ | |
| 318 | - 'elements' => $mailClass->decode(acym_loadObjectList($query, '', $settings['offset'], $settings['elementsPerPage'])), | |
| 319 | - 'total' => acym_loadObject($queryCount), | |
| 320 | - ]; | |
| 321 | - } | |
| 322 | - | |
| 323 | - public function scheduleReady(): ?int | |
| 324 | - { | |
| 325 | - $this->messages = []; | |
| 326 | - | |
| 327 | - $mailClass = new MailClass(); | |
| 328 | - | |
| 329 | - $multilingualQuery = acym_isMultilingual() ? ' OR mail.parent_id = campaign.mail_id ' : ''; | |
| 330 | - | |
| 331 | - $mailReady = $mailClass->decode( | |
| 332 | - acym_loadObjectList( | |
| 333 | - 'SELECT mail.id, campaign.sending_date, mail.name, campaign.mail_id AS parent_id, mail.language, campaign.sending_params | |
| 334 | - FROM #__acym_campaign AS campaign | |
| 335 | - JOIN #__acym_mail AS mail | |
| 336 | - ON campaign.mail_id = mail.id '.$multilingualQuery.' | |
| 337 | - WHERE campaign.sending_type = '.acym_escapeDB(CampaignClass::SENDING_TYPE_SCHEDULED).' | |
| 338 | - AND campaign.draft = 0 | |
| 339 | - AND campaign.sending_date <= '.acym_escapeDB(acym_date('now', 'Y-m-d H:i:s', false)).' | |
| 340 | - AND campaign.sent = 0', | |
| 341 | - 'id' | |
| 342 | - ) | |
| 343 | - ); | |
| 344 | - | |
| 345 | - if (empty($mailReady)) { | |
| 346 | - return null; | |
| 347 | - } | |
| 348 | - | |
| 349 | - $nbQueue = []; | |
| 350 | - | |
| 351 | - foreach ($mailReady as $mailId => $mail) { | |
| 352 | - $sendingParams = $mail->sending_params ?? []; | |
| 353 | - if (is_string($sendingParams)) { | |
| 354 | - $sendingParams = json_decode($sendingParams, true) ?? []; | |
| 355 | - } | |
| 356 | - | |
| 357 | - if (!empty($sendingParams['abtest']['repartition'])) { | |
| 358 | - $nbQueue[$mailId] = 0; | |
| 359 | - } else { | |
| 360 | - $nbQueue[$mailId] = $this->queue($mail); | |
| 361 | - $this->messages[] = acym_translationSprintf('ACYM_ADDED_QUEUE_SCHEDULE', $nbQueue[$mailId], '<b>'.$mail->name.'</b>'); | |
| 362 | - } | |
| 363 | - } | |
| 364 | - | |
| 365 | - $mailIds = array_keys($mailReady); | |
| 366 | - acym_arrayToInteger($mailIds); | |
| 367 | - $campaigns = acym_loadObjectList('SELECT id, mail_id FROM #__acym_campaign WHERE mail_id IN ('.implode(',', $mailIds).')'); | |
| 368 | - $campaignClass = new CampaignClass(); | |
| 369 | - foreach ($campaigns as $campaign) { | |
| 370 | - $result = $campaignClass->send($campaign->id, $nbQueue[$campaign->mail_id]); | |
| 371 | - if (!empty($result) || !acym_isMultilingual()) { | |
| 372 | - continue; | |
| 373 | - } | |
| 374 | - | |
| 375 | - $translatedMails = acym_loadResultArray('SELECT id FROM #__acym_mail WHERE id != '.intval($campaign->mail_id).' AND parent_id = '.intval($campaign->mail_id)); | |
| 376 | - if (empty($translatedMails)) { | |
| 377 | - continue; | |
| 378 | - } | |
| 379 | - | |
| 380 | - foreach ($translatedMails as $translatedMailId) { | |
| 381 | - if (!empty($nbQueue[$translatedMailId])) { | |
| 382 | - $campaignClass->send($campaign->id, $nbQueue[$translatedMailId]); | |
| 383 | - } | |
| 384 | - } | |
| 385 | - } | |
| 386 | - | |
| 387 | - return count($mailReady); | |
| 388 | - } | |
| 389 | - | |
| 390 | - public function delete(array $elements): int | |
| 391 | - { | |
| 392 | - if (empty($elements)) return 0; | |
| 393 | - acym_arrayToInteger($elements); | |
| 394 | - | |
| 395 | - $query = 'DELETE FROM #__acym_queue WHERE mail_id IN ('.implode(',', $elements).')'; | |
| 396 | - $result = acym_query($query); | |
| 397 | - | |
| 398 | - acym_query('UPDATE #__acym_campaign SET draft = 1, active = 1 WHERE mail_id IN ('.implode(',', $elements).')'); | |
| 399 | - | |
| 400 | - if (!$result) { | |
| 401 | - return 0; | |
| 402 | - } | |
| 403 | - | |
| 404 | - return $result; | |
| 405 | - } | |
| 406 | - | |
| 407 | - public function deleteQueuedByUserIds(array $userIds, ?int $mailId = null): int | |
| 408 | - { | |
| 409 | - acym_arrayToInteger($userIds); | |
| 410 | - if (empty($userIds)) { | |
| 411 | - return 0; | |
| 412 | - } | |
| 413 | - | |
| 414 | - $query = 'DELETE FROM #__acym_queue WHERE user_id IN ('.implode(',', $userIds).')'; | |
| 415 | - if (!empty($mailId)) { | |
| 416 | - $query .= ' AND mail_id = '.intval($mailId); | |
| 417 | - } | |
| 418 | - | |
| 419 | - try { | |
| 420 | - $nbDeleted = acym_query($query); | |
| 421 | - | |
| 422 | - if ($nbDeleted === false) { | |
| 423 | - $this->errors[] = acym_getDBError(); | |
| 424 | - | |
| 425 | - return 0; | |
| 426 | - } | |
| 427 | - | |
| 428 | - return (int)$nbDeleted; | |
| 429 | - } catch (\Exception $e) { | |
| 430 | - $this->errors[] = $e->getMessage(); | |
| 431 | - | |
| 432 | - return 0; | |
| 433 | - } | |
| 434 | - } | |
| 435 | - | |
| 436 | - public function getReady(int $startFrom, int $limit, int $mailId = 0): array | |
| 437 | - { | |
| 438 | - if (empty($limit)) { | |
| 439 | - return []; | |
| 440 | - } | |
| 441 | - | |
| 442 | - $query = 'SELECT queue.*, campaign.sending_params AS sending_params FROM #__acym_queue AS queue'; | |
| 443 | - $query .= ' JOIN #__acym_user AS user ON queue.`user_id` = user.`id` '; | |
| 444 | - $query .= ' JOIN #__acym_mail AS mail ON queue.`mail_id` = mail.`id` '; | |
| 445 | - $query .= ' LEFT JOIN #__acym_campaign AS campaign ON campaign.`mail_id` = mail.`id` '; | |
| 446 | - $query .= ' WHERE user.active = 1 | |
| 447 | - AND queue.`sending_date` <= '.acym_escapeDB(acym_date('now', 'Y-m-d H:i:s', false)).' | |
| 448 | - AND (campaign.mail_id IS NULL | |
| 449 | - OR (campaign.`active` = 1 | |
| 450 | - AND campaign.`draft` = 0 | |
| 451 | - ) | |
| 452 | - )'; | |
| 453 | - | |
| 454 | - if ($this->config->get('require_confirmation', 1) == 1) { | |
| 455 | - $query .= ' AND (user.confirmed = 1 OR mail.type = '.acym_escapeDB(MailClass::TYPE_NOTIFICATION).' OR mail.name LIKE "%confirm%")'; | |
| 456 | - } | |
| 457 | - | |
| 458 | - if (!empty($this->emailtypes)) { | |
| 459 | - foreach ($this->emailtypes as &$oneType) { | |
| 460 | - $oneType = acym_escapeDB($oneType); | |
| 461 | - } | |
| 462 | - $query .= ' AND mail.type IN ('.implode(', ', $this->emailtypes).')'; | |
| 463 | - } | |
| 464 | - | |
| 465 | - if (!empty($mailId)) { | |
| 466 | - $query .= ' AND queue.`mail_id` = '.intval($mailId); | |
| 467 | - } | |
| 468 | - | |
| 469 | - $sendOrder = $this->config->get('sendorder'); | |
| 470 | - if (empty($sendOrder)) { | |
| 471 | - $order = 'queue.`user_id` ASC'; | |
| 472 | - } elseif ($sendOrder === 'rand') { | |
| 473 | - $order = 'RAND()'; | |
| 474 | - } else { | |
| 475 | - $sendOrder = str_replace('subid', 'user_id', $sendOrder); | |
| 476 | - $ordering = explode(',', $sendOrder); | |
| 477 | - $order = 'queue.`'.acym_secureDBColumn(trim($ordering[0])).'` '.acym_secureDBColumn(trim($ordering[1])); | |
| 478 | - } | |
| 479 | - | |
| 480 | - $query .= ' ORDER BY queue.`priority` ASC, queue.`sending_date` ASC, '.$order; | |
| 481 | - $query .= ' LIMIT '.intval($startFrom).','.intval($limit); | |
| 482 | - | |
| 483 | - try { | |
| 484 | - $results = acym_loadObjectList($query); | |
| 485 | - } catch (\Exception $e) { | |
| 486 | - $results = null; | |
| 487 | - } | |
| 488 | - | |
| 489 | - if ($results === null) { | |
| 490 | - acym_query('REPAIR TABLE #__acym_queue, #__acym_user, #__acym_mail, #__acym_campaign'); | |
| 491 | - } | |
| 492 | - | |
| 493 | - if (empty($results)) { | |
| 494 | - return []; | |
| 495 | - } | |
| 496 | - | |
| 497 | - $firstElementQueued = reset($results); | |
| 498 | - acym_query( | |
| 499 | - 'UPDATE #__acym_queue | |
| 500 | - SET sending_date = DATE_ADD(sending_date, INTERVAL 1 SECOND) | |
| 501 | - WHERE mail_id = '.intval($firstElementQueued->mail_id).' AND user_id = '.intval($firstElementQueued->user_id).' | |
| 502 | - LIMIT 1' | |
| 503 | - ); | |
| 504 | - | |
| 505 | - return $results; | |
| 506 | - } | |
| 507 | - | |
| 508 | - public function delayFailed(int $mailId, array $userIds): void | |
| 509 | - { | |
| 510 | - acym_arrayToInteger($userIds); | |
| 511 | - if (empty($mailId) || empty($userIds)) { | |
| 512 | - return; | |
| 513 | - } | |
| 514 | - | |
| 515 | - acym_query( | |
| 516 | - 'UPDATE #__acym_queue | |
| 517 | - SET sending_date = DATE_ADD(sending_date, INTERVAL 1 HOUR), try = try +1 | |
| 518 | - WHERE mail_id = '.intval($mailId).' | |
| 519 | - AND user_id IN ('.implode(',', $userIds).')' | |
| 520 | - ); | |
| 521 | - } | |
| 522 | - | |
| 523 | - public function delayAll(int $hours): void | |
| 524 | - { | |
| 525 | - if ($hours < 1) { | |
| 526 | - return; | |
| 527 | - } | |
| 528 | - | |
| 529 | - acym_query( | |
| 530 | - 'UPDATE #__acym_queue | |
| 531 | - SET sending_date = DATE_ADD(sending_date, INTERVAL '.$hours.' HOUR) | |
| 532 | - WHERE sending_date < DATE_ADD(NOW(), INTERVAL '.intval($hours).' HOUR)' | |
| 533 | - ); | |
| 534 | - } | |
| 535 | - | |
| 536 | - public function getMailReceivers(object $mail, bool $onlyNew = false): AutomationHelper | |
| 537 | - { | |
| 538 | - if (empty($mail->sending_params)) { | |
| 539 | - $sendingParams = []; | |
| 540 | - $mail->filters = []; | |
| 541 | - } else { | |
| 542 | - $sendingParams = is_array($mail->sending_params) ? $mail->sending_params : json_decode($mail->sending_params, true); | |
| 543 | - | |
| 544 | - $campaignClass = new CampaignClass(); | |
| 545 | - $mail->filters = $campaignClass->getFilterCampaign($sendingParams); | |
| 546 | - | |
| 547 | - if (!empty($sendingParams['resendTarget']) && 'new' === $sendingParams['resendTarget']) { | |
| 548 | - $onlyNew = true; | |
| 549 | - } | |
| 550 | - } | |
| 551 | - | |
| 552 | - $automationHelper = new AutomationHelper(); | |
| 553 | - $automationHelper->join['userlist'] = ' #__acym_user_has_list AS userlist ON user.id = userlist.user_id'; | |
| 554 | - $automationHelper->join['maillist'] = ' #__acym_mail_has_list AS maillist ON userlist.list_id = maillist.list_id'; | |
| 555 | - $automationHelper->where = [ | |
| 556 | - 'userlist.status = 1', | |
| 557 | - 'maillist.mail_id = '.intval(empty($mail->parent_id) ? $mail->id : $mail->parent_id), | |
| 558 | - ]; | |
| 559 | - | |
| 560 | - if (!$onlyNew && acym_isMultilingual()) { | |
| 561 | - $where = 'user.language = '.acym_escapeDB($mail->language); | |
| 562 | - if ($mail->id == $mail->parent_id) { | |
| 563 | - $where .= ' OR user.language = "" OR user.language NOT IN (SELECT language FROM #__acym_mail WHERE parent_id = '.intval($mail->id).')'; | |
| 564 | - } | |
| 565 | - $automationHelper->where[] = $where; | |
| 566 | - } | |
| 567 | - | |
| 568 | - if ($this->config->get('require_confirmation', 1) == 1) { | |
| 569 | - $automationHelper->where[] = '`user`.`confirmed` = 1'; | |
| 570 | - } | |
| 571 | - | |
| 572 | - if ($onlyNew) { | |
| 573 | - $automationHelper->leftjoin['us'] = '`#__acym_user_stat` AS `us` ON `us`.`user_id` = `user`.`id` AND `us`.`mail_id` IN (SELECT id FROM #__acym_mail WHERE parent_id = '.intval( | |
| 574 | - $mail->id | |
| 575 | - ).' OR id = '.intval($mail->id).')'; | |
| 576 | - $automationHelper->where[] = '`us`.`user_id` IS NULL'; | |
| 577 | - | |
| 578 | - $automationHelper->where[] = '`user`.`active` = 1'; | |
| 579 | - } | |
| 580 | - | |
| 581 | - $automationHelper->removeFlag(SegmentsController::FLAG_USERS); | |
| 582 | - $automationHelper->removeFlag(SegmentsController::FLAG_COUNT); | |
| 583 | - | |
| 584 | - if (empty($mail->filters)) { | |
| 585 | - return $automationHelper; | |
| 586 | - } | |
| 587 | - | |
| 588 | - foreach ($mail->filters as $orValues) { | |
| 589 | - if (empty($orValues)) continue; | |
| 590 | - | |
| 591 | - $automationHelperSegment = new AutomationHelper(); | |
| 592 | - foreach ($orValues as $and => $andValues) { | |
| 593 | - $and = intval($and); | |
| 594 | - foreach ($andValues as $filterName => $options) { | |
| 595 | - acym_trigger('onAcymProcessFilter_'.$filterName, [&$automationHelperSegment, &$options, &$and]); | |
| 596 | - } | |
| 597 | - } | |
| 598 | - $automationHelperSegment->addFlag(SegmentsController::FLAG_COUNT); | |
| 599 | - } | |
| 600 | - | |
| 601 | - $segmentMatchingCondition = 'LIKE'; | |
| 602 | - if (!empty($sendingParams['segment']['invert']) && $sendingParams['segment']['invert'] === 'exclude') { | |
| 603 | - $segmentMatchingCondition = 'NOT LIKE'; | |
| 604 | - } | |
| 605 | - $automationHelper->where[] = 'user.automation '.$segmentMatchingCondition.' "%a'.intval(SegmentsController::FLAG_COUNT).'a%"'; | |
| 606 | - | |
| 607 | - return $automationHelper; | |
| 608 | - } | |
| 609 | - | |
| 610 | - public function queue(object $mail): int | |
| 611 | - { | |
| 612 | - $automationHelper = $this->getMailReceivers($mail); | |
| 613 | - $automationHelper->where[] = '`user`.`active` = 1'; | |
| 614 | - | |
| 615 | - $priority = $this->config->get('priority_newsletter', 3); | |
| 616 | - $select = [intval($mail->id), 'userlist.user_id', acym_escapeDB($mail->sending_date), intval($priority), '0']; | |
| 617 | - $inserted = acym_query('INSERT IGNORE INTO #__acym_queue (`mail_id`, `user_id`, `sending_date`, `priority`, `try`) ('.$automationHelper->getQuery($select).')'); | |
| 618 | - | |
| 619 | - $automationHelper->removeFlag(SegmentsController::FLAG_COUNT); | |
| 620 | - | |
| 621 | - return (int)$inserted; | |
| 622 | - } | |
| 623 | - | |
| 624 | - public function addQueue(int $userId, int $mailId, string $sendingDate): int | |
| 625 | - { | |
| 626 | - $priority = $this->config->get('priority_newsletter', 3); | |
| 627 | - | |
| 628 | - return (int)acym_query( | |
| 629 | - 'INSERT IGNORE INTO #__acym_queue (`mail_id`, `user_id`, `sending_date`, `priority`, `try`) VALUES ('.intval($mailId).', '.intval($userId).', '.acym_escapeDB( | |
| 630 | - $sendingDate | |
| 631 | - ).', '.intval($priority).', 0)' | |
| 632 | - ); | |
| 633 | - } | |
| 634 | - | |
| 635 | - public function unpauseCampaign(int $campaignId, int $active): void | |
| 636 | - { | |
| 637 | - if (acym_query('UPDATE #__acym_campaign SET active = '.intval($active).' WHERE id = '.intval($campaignId))) { | |
| 638 | - acym_enqueueMessage(acym_translation($active ? 'ACYM_UNPAUSE_CAMPAIGN_SUCCESSFUL' : 'ACYM_PAUSE_CAMPAIGN_SUCCESSFUL'), "success"); | |
| 639 | - } else { | |
| 640 | - acym_enqueueMessage(acym_translation($active ? 'ACYM_UNPAUSE_CAMPAIGN_FAIL' : 'ACYM_PAUSE_CAMPAIGN_FAIL'), "error"); | |
| 641 | - } | |
| 642 | - } | |
| 643 | - | |
| 644 | - public function emptyQueue(): int | |
| 645 | - { | |
| 646 | - return (int)acym_query('DELETE FROM `#__acym_queue`'); | |
| 647 | - } | |
| 648 | - | |
| 649 | - public function cleanQueue(): int | |
| 650 | - { | |
| 651 | - $twoDaysEarlier = acym_date(time() - 172800, 'Y-m-d H:i:s', false); | |
| 652 | - | |
| 653 | - $conditionUser = '`user`.`active` = 0'; | |
| 654 | - if ($this->config->get('require_confirmation', 1) == 1) { | |
| 655 | - $conditionUser .= ' OR `user`.`confirmed` = 0'; | |
| 656 | - } | |
| 657 | - | |
| 658 | - $numberOfDaysToWait = $this->config->get('queue_delete_days', 0); | |
| 659 | - $conditionDateDelete = ''; | |
| 660 | - if (!empty($numberOfDaysToWait)) { | |
| 661 | - $dateTimeConditionDelete = acym_date(time() - ($numberOfDaysToWait * 86400), 'Y-m-d H:i:s', false); | |
| 662 | - $conditionDateDelete = ' OR (`queue`.`sending_date` < '.acym_escapeDB($dateTimeConditionDelete).')'; | |
| 663 | - } | |
| 664 | - | |
| 665 | - return (int)acym_query( | |
| 666 | - 'DELETE `queue`.* | |
| 667 | - FROM `#__acym_queue` AS `queue` | |
| 668 | - JOIN `#__acym_user` AS `user` ON `queue`.`user_id` = `user`.`id` | |
| 669 | - WHERE (('.$conditionUser.') AND `queue`.`sending_date` < '.acym_escapeDB($twoDaysEarlier).') '.$conditionDateDelete | |
| 670 | - ); | |
| 671 | - } | |
| 672 | - | |
| 673 | - public function isSendingFinished(int $mailId): bool | |
| 674 | - { | |
| 675 | - $mailClass = new MailClass(); | |
| 676 | - $mail = $mailClass->getOneById($mailId); | |
| 677 | - | |
| 678 | - if (empty($mail) || $mailClass->isTransactionalMail($mail)) return false; | |
| 679 | - | |
| 680 | - $filters = [ | |
| 681 | - '`queue`.`mail_id` = '.intval($mailId), | |
| 682 | - '`user`.`active` = 1', | |
| 683 | - ]; | |
| 684 | - if ($this->config->get('require_confirmation')) { | |
| 685 | - $filters[] = '`user`.`confirmed` = 1'; | |
| 686 | - } | |
| 687 | - | |
| 688 | - $res = intval( | |
| 689 | - acym_loadResult( | |
| 690 | - 'SELECT COUNT(`queue`.`mail_id`) | |
| 691 | - FROM #__acym_queue AS `queue` | |
| 692 | - JOIN #__acym_user AS `user` | |
| 693 | - ON `queue`.`user_id` = `user`.`id` | |
| 694 | - WHERE '.implode(' AND ', $filters) | |
| 695 | - ) | |
| 696 | - ); | |
| 697 | - | |
| 698 | - return empty($res); | |
| 699 | - } | |
| 700 | - | |
| 701 | - public function getQueueParams(int $mailId, int $userId): array | |
| 702 | - { | |
| 703 | - $query = acym_loadObject( | |
| 704 | - 'SELECT `params` FROM #__acym_queue WHERE `user_id` = '.intval($userId).' AND `mail_id` = '.$mailId | |
| 705 | - ); | |
| 706 | - | |
| 707 | - return empty($query) ? [] : json_decode((string)$query->params, true); | |
| 708 | - } | |
| 709 | -} | |
