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