← All changes | back/Classes/FollowupClass.php +731 -0 10.2.0 → 11.1.0 View file →

		@@ -1,0 +1,731 @@
	1	+<?php
	2	+
	3	+namespace AcyMailing\Classes;
	4	+
	5	+use AcyMailing\Helpers\PaginationHelper;
	6	+use AcyMailing\Core\AcymClass;
	7	+
	8	+class FollowupClass extends AcymClass
	9	+{
	10	+ const DEFAULT_DELAY_UNIT = 86400;
	11	+ const DELAY_UNIT = [
	12	+ 60 => 'ACYM_MINUTES',
	13	+ 3600 => 'ACYM_HOURS',
	14	+ 86400 => 'ACYM_DAYS',
	15	+ 604800 => 'ACYM_WEEKS',
	16	+ 2628000 => 'ACYM_MONTHS',
	17	+ ];
	18	+
	19	+ public function __construct()
	20	+ {
	21	+ parent::__construct();
	22	+
	23	+ $this->table = 'followup';
	24	+ $this->pkey = 'id';
	25	+ }
	26	+
	27	+ public function getDelayUnits(): array
	28	+ {
	29	+ $return = self::DELAY_UNIT;
	30	+ foreach ($return as $key => $value) {
	31	+ $return[$key] = acym_translation($value);
	32	+ }
	33	+
	34	+ return $return;
	35	+ }
	36	+
	37	+ public function getMatchingElements(array $settings = []): array
	38	+ {
	39	+ $query = 'SELECT `followup`.*, COUNT(`mails`.`mail_id`) AS nbEmails
	40	+ FROM #__acym_followup AS followup
	41	+ LEFT JOIN #__acym_followup_has_mail AS mails
	42	+ ON followup.id = mails.followup_id';
	43	+
	44	+ $queryCount = 'SELECT COUNT(*) AS total FROM #__acym_followup AS followup ';
	45	+
	46	+ $filters = [];
	47	+
	48	+ if (!empty($settings['search'])) {
	49	+ $filters[] = 'followup.name LIKE '.acym_escapeDB('%'.$settings['search'].'%');
	50	+ }
	51	+
	52	+ if (!empty($filters)) {
	53	+ $query .= ' WHERE ('.implode(') AND (', $filters).')';
	54	+ $queryCount .= ' WHERE ('.implode(') AND (', $filters).')';
	55	+ }
	56	+
	57	+ $query .= ' GROUP BY `followup`.`id` ';
	58	+
	59	+ if (empty($settings['ordering'])) $settings['ordering'] = $this->pkey;
	60	+ $query .= ' ORDER BY `'.acym_secureDBColumn($settings['ordering']).'`';
	61	+ if (!empty($settings['ordering_sort_order'])) $query .= ' '.acym_secureDBColumn(strtoupper($settings['ordering_sort_order']));
	62	+
	63	+ if (empty($settings['offset']) || $settings['offset'] < 0) {
	64	+ $settings['offset'] = 0;
	65	+ }
	66	+
	67	+ if (empty($settings['elementsPerPage']) || $settings['elementsPerPage'] < 1) {
	68	+ $pagination = new PaginationHelper();
	69	+ $settings['elementsPerPage'] = $pagination->getListLimit();
	70	+ }
	71	+
	72	+ $results = [];
	73	+ $results['elements'] = acym_loadObjectList($query, '', $settings['offset'], $settings['elementsPerPage']);
	74	+ $urlClickClass = new UrlClickClass();
	75	+ foreach ($results['elements'] as $key => $oneFollowup) {
	76	+ $results['elements'][$key]->subscribers = $this->getNumberSubscribersByListId($oneFollowup->list_id);
	77	+ $this->getGlobalStats($results['elements'][$key], $urlClickClass);
	78	+ }
	79	+
	80	+ $results['total'] = acym_loadObject($queryCount);
	81	+
	82	+ return $results;
	83	+ }
	84	+
	85	+ public function getNumberSubscribersByListId(int $listId = 0, bool $onlySubscribed = false): int
	86	+ {
	87	+ if (empty($listId)) return 0;
	88	+
	89	+ $query = 'SELECT COUNT(*)
	90	+ FROM #__acym_user_has_list
	91	+ WHERE `list_id` = '.intval($listId);
	92	+
	93	+ if ($onlySubscribed) {
	94	+ $query .= ' AND `status` = 1';
	95	+ }
	96	+
	97	+ return (int)acym_loadResult($query);
	98	+ }
	99	+
	100	+ private function getGlobalStats(object $element, UrlClickClass $urlClickClass): void
	101	+ {
	102	+ $mailsStats = acym_loadObjectList(
	103	+ 'SELECT mail_stat.*
	104	+ FROM #__acym_mail_stat AS mail_stat
	105	+ JOIN #__acym_followup_has_mail AS fhm ON fhm.mail_id = mail_stat.mail_id
	106	+ WHERE fhm.followup_id = '.intval($element->id)
	107	+ );
	108	+
	109	+ $element->open = 0;
	110	+ $element->click = 0;
	111	+ $element->income = 0;
	112	+ $numberMailSent = 0;
	113	+ if (empty($mailsStats)) return;
	114	+
	115	+ foreach ($mailsStats as $key => $mailsStat) {
	116	+ $element->open += $mailsStat->open_unique;
	117	+ $element->click += $urlClickClass->getNumberUsersClicked([$mailsStat->mail_id]);
	118	+ $numberMailSent += $mailsStat->sent;
	119	+ }
	120	+
	121	+ if (!empty($numberMailSent)) {
	122	+ $element->open = number_format($element->open / $numberMailSent * 100, 2);
	123	+ $element->click = number_format($element->click / $numberMailSent * 100, 2);
	124	+ }
	125	+
	126	+ //Tracking sales
	127	+ if (!acym_isTrackingSalesActive()) return;
	128	+
	129	+ $trackingSales = acym_loadObject(
	130	+ 'SELECT SUM(user_stat.tracking_sale) AS sale, user_stat.currency
	131	+ FROM #__acym_user_stat AS user_stat
	132	+ JOIN #__acym_followup_has_mail AS fhm ON fhm.mail_id = user_stat.mail_id
	133	+ WHERE fhm.followup_id = '.intval($element->id)
	134	+ );
	135	+
	136	+ $element->sale = $trackingSales->sale;
	137	+ if (empty($element->currency)) {
	138	+ $element->currency = '';
	139	+ }
	140	+
	141	+ acym_trigger('getCurrency', [&$element->currency]);
	142	+ }
	143	+
	144	+ public function delete(array $elements): int
	145	+ {
	146	+ acym_arrayToInteger($elements);
	147	+
	148	+ if (empty($elements)) return 0;
	149	+
	150	+ $emailIds = $this->getEmailsByIds($elements);
	151	+ if (!empty($emailIds)) {
	152	+ $mailClass = new MailClass();
	153	+ $mailClass->delete($emailIds);
	154	+ }
	155	+
	156	+ $listIds = $this->getListsByIds($elements);
	157	+
	158	+ $result = parent::delete($elements);
	159	+
	160	+ $listClass = new ListClass();
	161	+ $listClass->delete($listIds);
	162	+
	163	+ return $result;
	164	+ }
	165	+
	166	+ public function getEmailsByIds(array $followupIds): array
	167	+ {
	168	+ acym_arrayToInteger($followupIds);
	169	+
	170	+ if (empty($followupIds)) {
	171	+ return [];
	172	+ }
	173	+
	174	+ return acym_loadResultArray(
	175	+ 'SELECT `mail_id`
	176	+ FROM #__acym_followup_has_mail
	177	+ WHERE `followup_id` IN ('.implode(', ', $followupIds).')
	178	+ ORDER BY `delay`*`delay_unit` ASC'
	179	+ );
	180	+ }
	181	+
	182	+ public function getListsByIds(array $followupIds): array
	183	+ {
	184	+ acym_arrayToInteger($followupIds);
	185	+
	186	+ if (empty($followupIds)) {
	187	+ return [];
	188	+ }
	189	+
	190	+ return acym_loadResultArray(
	191	+ 'SELECT `list_id`
	192	+ FROM #__acym_followup
	193	+ WHERE `id` IN ('.implode(', ', $followupIds).')'
	194	+ );
	195	+ }
	196	+
	197	+ public function getOneById(int $id): ?object
	198	+ {
	199	+ $followup = parent::getOneById($id);
	200	+
	201	+ if (empty($followup)) return null;
	202	+
	203	+ if (!empty($followup->condition)) {
	204	+ $followup->condition = json_decode($followup->condition, true);
	205	+ }
	206	+
	207	+ if (!empty($followup->loop_mail_skip)) {
	208	+ $followup->loop_mail_skip = json_decode($followup->loop_mail_skip, true);
	209	+ }
	210	+
	211	+ return $followup;
	212	+ }
	213	+
	214	+ public function getOneByListId(int $listId): ?object
	215	+ {
	216	+ $followup = acym_loadObject('SELECT * FROM `#__acym_followup` WHERE `list_id` = '.intval($listId));
	217	+
	218	+ if (empty($followup)) {
	219	+ return null;
	220	+ }
	221	+
	222	+ if (!empty($followup->condition)) {
	223	+ $followup->condition = json_decode($followup->condition, true);
	224	+ }
	225	+
	226	+ return $followup;
	227	+ }
	228	+
	229	+ public function save(object $element): ?int
	230	+ {
	231	+ if (!empty($element->condition) && is_array($element->condition)) {
	232	+ $element->condition = json_encode($element->condition);
	233	+ }
	234	+
	235	+ if (!empty($element->loop_mail_skip) && is_array($element->loop_mail_skip)) {
	236	+ $element->loop_mail_skip = json_encode($element->loop_mail_skip);
	237	+ }
	238	+
	239	+ $this->updateListFollowup($element);
	240	+
	241	+ return parent::save($element);
	242	+ }
	243	+
	244	+ private function updateListFollowup(object $element): void
	245	+ {
	246	+ $listClass = new ListClass();
	247	+ if (empty($element->list_id)) {
	248	+ $list = new \stdClass();
	249	+ $list->name = $element->display_name;
	250	+ $list->description = '';
	251	+ $list->color = '#'.implode('', [
	252	+ ListClass::COLOR_PARTS[acym_rand(0, 15)],
	253	+ ListClass::COLOR_PARTS[acym_rand(0, 15)],
	254	+ ListClass::COLOR_PARTS[acym_rand(0, 15)],
	255	+ ListClass::COLOR_PARTS[acym_rand(0, 15)],
	256	+ ListClass::COLOR_PARTS[acym_rand(0, 15)],
	257	+ ListClass::COLOR_PARTS[acym_rand(0, 15)],
	258	+ ]);
	259	+ $list->access = '';
	260	+ $list->type = ListClass::LIST_TYPE_FOLLOWUP;
	261	+ } else {
	262	+ $list = $listClass->getOneById($element->list_id);
	263	+ $list->name = $element->display_name;
	264	+ }
	265	+
	266	+ $element->list_id = $listClass->save($list);
	267	+ }
	268	+
	269	+ public function getConditionSummary(object $followup): array
	270	+ {
	271	+ $condition = $followup->condition;
	272	+ $statusArray = [
	273	+ 'is' => acym_strtolower(acym_translation('ACYM_IS')),
	274	+ 'is_not' => acym_strtolower(acym_translation('ACYM_IS_NOT')),
	275	+ ];
	276	+ $listClass = new ListClass();
	277	+ $segmentClass = new SegmentClass();
	278	+
	279	+ $return = [];
	280	+ if (empty($condition['lists_status']) || empty($condition['lists'])) {
	281	+ $return[] = acym_translation('ACYM_NO_CONDITION_USER_SUBSCRIPTION');
	282	+ } else {
	283	+ $lists = $listClass->getListsByIds($condition['lists']);
	284	+ $listsToDisplay = [];
	285	+ foreach ($lists as $list) {
	286	+ $listsToDisplay[] = $list->name;
	287	+ }
	288	+
	289	+ $translationKey = $followup->trigger === 'user_subscribe' ? 'ACYM_X_SUBSCRIBING_X_LIST' : 'ACYM_X_SUBSCRIBED_X_LIST';
	290	+ $return[] = acym_translationSprintf($translationKey, acym_strtolower($statusArray[$condition['lists_status']]), implode(',', $listsToDisplay));
	291	+ }
	292	+
	293	+
	294	+ if (empty($condition['segments_status']) || empty($condition['segments'])) {
	295	+ $return[] = acym_translation('ACYM_NO_CONDITION_SEGMENT');
	296	+ } else {
	297	+ $segments = $segmentClass->getByIds($condition['segments']);
	298	+ $segmentsToDisplay = [];
	299	+ foreach ($segments as $segment) {
	300	+ $segmentsToDisplay[] = $segment->name;
	301	+ }
	302	+ $return[] = acym_translationSprintf('ACYM_X_PART_X_SEGMENT', acym_strtolower($statusArray[$condition['segments_status']]), implode(',', $segmentsToDisplay));
	303	+ }
	304	+
	305	+ acym_trigger('getFollowupConditionSummary', [&$return, $condition, $followup->trigger, $statusArray]);
	306	+
	307	+ return $return;
	308	+ }
	309	+
	310	+ private function getKeyMailArray(int $key, array $mailsKey): int
	311	+ {
	312	+ if (!empty($mailsKey) && in_array($key, $mailsKey)) {
	313	+ return $this->getKeyMailArray(++$key, $mailsKey);
	314	+ }
	315	+
	316	+ return $key;
	317	+ }
	318	+
	319	+ public function getOneByIdWithMails(int $id): ?object
	320	+ {
	321	+ $followup = $this->getOneById($id);
	322	+
	323	+ if (empty($followup)) {
	324	+ return null;
	325	+ }
	326	+
	327	+ $mailClass = new MailClass();
	328	+ $mails = $mailClass->decode(
	329	+ acym_loadObjectList(
	330	+ 'SELECT mail.subject, mail.id, followup_mail.delay, followup_mail.delay_unit
	331	+ FROM #__acym_mail AS mail
	332	+ JOIN #__acym_followup_has_mail AS followup_mail ON mail.id = followup_mail.mail_id AND followup_mail.followup_id = '.intval($id),
	333	+ 'id'
	334	+ )
	335	+ );
	336	+
	337	+ $return = [];
	338	+ foreach ($mails as $key => $mail) {
	339	+ $mail->delay_display = $this->getDelayDisplay($mail->delay, $mail->delay_unit);
	340	+ $mail->edit_link = acym_completeLink('mails&task=edit&step=editEmail&type=followup&id='.$key.'&followup_id='.$id.'&return='.urlencode(acym_currentURL()), false, true);
	341	+ $finalKey = $this->getKeyMailArray(intval($mail->delay) * intval($mail->delay_unit), array_keys($return));
	342	+ $return[$finalKey] = $mail;
	343	+ }
	344	+
	345	+ ksort($return);
	346	+
	347	+ $followup->mails = empty($return) ? [] : $return;
	348	+
	349	+ return $followup;
	350	+ }
	351	+
	352	+ private function getDelayDisplay(int $delay, int $delayUnit): string
	353	+ {
	354	+ $delayUnits = $this->getDelayUnits();
	355	+
	356	+ return acym_translationSprintf('ACYM_X_PLUS_X_FOLLOW_UP', $delayUnits[$delayUnit], $delay);
	357	+ }
	358	+
	359	+ public function getDelaySettingToMail(object $mail, int $followupId): bool
	360	+ {
	361	+ $settings = acym_loadObject('SELECT delay, delay_unit FROM #__acym_followup_has_mail WHERE followup_id = '.intval($followupId).' AND mail_id = '.intval($mail->id));
	362	+
	363	+ if (empty($settings)) {
	364	+ return false;
	365	+ }
	366	+
	367	+ $mail->delay = $settings->delay;
	368	+ $mail->delay_unit = $settings->delay_unit;
	369	+
	370	+ return true;
	371	+ }
	372	+
	373	+ public function saveDelaySettings(array $followupData, int $mailId): bool
	374	+ {
	375	+ if (empty($followupData['id']) || empty($followupData['delay_unit']) || empty($mailId)) {
	376	+ return false;
	377	+ }
	378	+
	379	+ acym_arrayToInteger($followupData);
	380	+
	381	+ $affectedRow = acym_query(
	382	+ 'INSERT INTO #__acym_followup_has_mail (`mail_id`, `followup_id`, `delay`, `delay_unit`) VALUE ('.intval(
	383	+ $mailId
	384	+ ).', '.$followupData['id'].', '.$followupData['delay'].', '.$followupData['delay_unit'].') ON DUPLICATE KEY UPDATE delay = '.$followupData['delay'].', delay_unit = '.$followupData['delay_unit'].''
	385	+ );
	386	+
	387	+ return $affectedRow !== false;
	388	+ }
	389	+
	390	+ public function duplicateMail(int $mailId, int $id): bool
	391	+ {
	392	+ if (empty($mailId) || empty($id)) {
	393	+ return false;
	394	+ }
	395	+
	396	+ $mailClass = new MailClass();
	397	+ $mail = $mailClass->getOneById($mailId);
	398	+
	399	+ if (empty($mail)) {
	400	+ return false;
	401	+ }
	402	+
	403	+ $delaySettings = new \stdClass();
	404	+ $delaySettings->id = $mail->id;
	405	+ $this->getDelaySettingToMail($delaySettings, $id);
	406	+
	407	+ unset($mail->id);
	408	+ unset($mail->creator_id);
	409	+ $mail->name .= '_copy';
	410	+
	411	+ $mail->id = $mailClass->save($mail);
	412	+ if (empty($mail->id)) {
	413	+ return false;
	414	+ }
	415	+
	416	+ $affectedRow = acym_query(
	417	+ 'INSERT INTO #__acym_followup_has_mail (`mail_id`, `followup_id`, `delay`, `delay_unit`) VALUE ('.intval($mail->id).', '.$id.', '.intval(
	418	+ $delaySettings->delay
	419	+ ).', '.intval($delaySettings->delay_unit).')'
	420	+ );
	421	+
	422	+ return !empty($affectedRow);
	423	+ }
	424	+
	425	+ public function deleteMail(int $mailId): bool
	426	+ {
	427	+ if (empty($mailId)) {
	428	+ return false;
	429	+ }
	430	+
	431	+ $mailClass = new MailClass();
	432	+
	433	+ return (bool)$mailClass->delete([$mailId]);
	434	+ }
	435	+
	436	+ public function getFollowupsWithMailsInfoByIds(array $followupIds): array
	437	+ {
	438	+ acym_arrayToInteger($followupIds);
	439	+
	440	+ $mailsInfo = acym_loadObjectList(
	441	+ 'SELECT followup_mail.*, followup.send_once
	442	+ FROM #__acym_followup_has_mail AS followup_mail
	443	+ JOIN #__acym_followup AS followup ON followup.id = followup_mail.followup_id AND followup_mail.followup_id IN ('.implode(',', $followupIds).')'
	444	+ );
	445	+
	446	+ if (empty($mailsInfo)) {
	447	+ return [];
	448	+ }
	449	+
	450	+ $return = [];
	451	+
	452	+ foreach ($mailsInfo as $mailInfo) {
	453	+ if (!isset($return[$mailInfo->followup_id])) $return[$mailInfo->followup_id] = [];
	454	+ $return[$mailInfo->followup_id][] = $mailInfo;
	455	+ }
	456	+
	457	+ return $return;
	458	+ }
	459	+
	460	+ public function subscribeUserToFollowupList(array $followupIds, $userId): array
	461	+ {
	462	+ acym_arrayToInteger($followupIds);
	463	+
	464	+ $followupLists = acym_loadObjectList(
	465	+ 'SELECT followup.list_id, IF(user_list.status IS NULL, "", status) AS status, followup.id, followup.send_once FROM #__acym_followup AS followup
	466	+ LEFT JOIN #__acym_user_has_list AS user_list ON followup.list_id = user_list.list_id AND (user_list.user_id = '.intval($userId).' OR user_list.user_id IS NULL)
	467	+ WHERE followup.id IN ('.implode(',', $followupIds).')',
	468	+ 'list_id'
	469	+ );
	470	+
	471	+ if (empty($followupLists)) {
	472	+ return [];
	473	+ }
	474	+
	475	+ $followupListIds = [];
	476	+ foreach ($followupLists as $listId => $followupList) {
	477	+ // The user unsubscribed, don't re-subscribe them
	478	+ if (in_array($followupList->status, [0, '0'], true)) {
	479	+ continue;
	480	+ }
	481	+
	482	+ if ($followupList->status === '' || $followupList->send_once == 0) {
	483	+ $followupListIds[$listId] = $followupList->id;
	484	+ }
	485	+ }
	486	+
	487	+ if (empty($followupListIds)) {
	488	+ return [];
	489	+ }
	490	+
	491	+ $userClass = new UserClass();
	492	+ $userClass->subscribe([$userId], array_keys($followupListIds), false);
	493	+
	494	+ return $followupListIds;
	495	+ }
	496	+
	497	+ public function addFollowupEmailsQueue(string $followupTrigger, int $userId, array $params = []): bool
	498	+ {
	499	+ if (empty($followupTrigger) || empty($userId)) {
	500	+ return false;
	501	+ }
	502	+
	503	+ $followupToTrigger = acym_loadObjectList('SELECT * FROM #__acym_followup WHERE active = 1 AND `trigger` = '.acym_escapeDB($followupTrigger), 'id');
	504	+
	505	+ if (empty($followupToTrigger)) {
	506	+ return false;
	507	+ }
	508	+
	509	+ foreach ($followupToTrigger as $key => $followup) {
	510	+ if (!empty($followup->condition)) {
	511	+ $followupToTrigger[$key]->condition = json_decode($followup->condition, true);
	512	+ }
	513	+ }
	514	+
	515	+ acym_trigger('matchFollowupsConditions', [&$followupToTrigger, $userId, $params]);
	516	+
	517	+ if (empty($followupToTrigger)) {
	518	+ return false;
	519	+ }
	520	+
	521	+ $followupIds = $this->subscribeUserToFollowupList(array_keys($followupToTrigger), $userId);
	522	+
	523	+ if (empty($followupIds)) {
	524	+ return false;
	525	+ }
	526	+
	527	+ $followups = $this->getFollowupsWithMailsInfoByIds($followupIds);
	528	+
	529	+ if (empty($followups)) {
	530	+ return false;
	531	+ }
	532	+
	533	+ $priority = $this->config->get('followup_max_priority', 0) == 1 ? 1 : 2;
	534	+
	535	+ $valuesToInsert = [];
	536	+
	537	+ foreach ($followups as $followupId => $mailsInfo) {
	538	+ foreach ($mailsInfo as $mailInfo) {
	539	+ $sendDate = time() + (intval($mailInfo->delay) * intval($mailInfo->delay_unit));
	540	+ $sendDate = acym_date($sendDate, 'Y-m-d H:i:s', false);
	541	+ $valuesToInsert[] = ' ('.intval($mailInfo->mail_id).', '.intval($userId).', '.acym_escapeDB($sendDate).', '.intval($priority).', '.acym_escapeDB(
	542	+ json_encode($params)
	543	+ ).') ';
	544	+ $this->addMailStat($mailInfo->mail_id);
	545	+ }
	546	+ $followupToTrigger[$followupId]->last_trigger = time();
	547	+ $this->save($followupToTrigger[$followupId]);
	548	+ }
	549	+
	550	+ $query = 'INSERT IGNORE INTO #__acym_queue (`mail_id`, `user_id`, `sending_date`, `priority`, `params`) VALUES '.implode(',', $valuesToInsert);
	551	+
	552	+ $affectedRows = acym_query($query);
	553	+
	554	+ return !empty($affectedRows);
	555	+ }
	556	+
	557	+ private function addMailStat(int $mailId): void
	558	+ {
	559	+ $mailStatClass = new MailStatClass();
	560	+ $mailStat = $mailStatClass->getOneRowByMailId($mailId);
	561	+ $newMailStat = new \stdClass();
	562	+ $newMailStat->mail_id = intval($mailId);
	563	+ $newMailStat->total_subscribers = 1;
	564	+
	565	+ if (empty($mailStat)) {
	566	+ $newMailStat->send_date = acym_date('now', 'Y-m-d H:i:s', false);
	567	+ }
	568	+
	569	+ $mailStatClass->save($newMailStat);
	570	+ }
	571	+
	572	+ public function getFollowupDailyBases(): array
	573	+ {
	574	+ $triggers = [];
	575	+ acym_trigger('onAcymGetFollowupDailyBases', [&$triggers]);
	576	+
	577	+ if (empty($triggers)) {
	578	+ return [];
	579	+ }
	580	+
	581	+ $safeTriggers = array_map('acym_escapeDB', $triggers);
	582	+
	583	+ return acym_loadObjectList('SELECT * FROM #__acym_followup WHERE `trigger` IN ('.implode(',', $safeTriggers).') AND active = 1');
	584	+ }
	585	+
	586	+ public function queueForSubscribers(int $emailId): ?int
	587	+ {
	588	+ if (empty($emailId)) {
	589	+ return null;
	590	+ }
	591	+
	592	+ $mailInfo = acym_loadObject(
	593	+ 'SELECT map.*, followup.list_id
	594	+ FROM #__acym_followup_has_mail AS map
	595	+ JOIN #__acym_followup AS followup
	596	+ ON followup.id = map.followup_id
	597	+ WHERE map.mail_id = '.intval($emailId)
	598	+ );
	599	+ if (empty($mailInfo)) return null;
	600	+
	601	+ $this->addMailStat($mailInfo->mail_id);
	602	+
	603	+ $delay = intval($mailInfo->delay) * intval($mailInfo->delay_unit);
	604	+ $query = 'INSERT IGNORE INTO #__acym_queue (`mail_id`, `user_id`, `sending_date`, `priority`)
	605	+ SELECT '.intval($mailInfo->mail_id).', user_id, TIMESTAMPADD(SECOND, '.$delay.', subscription_date), 2
	606	+ FROM #__acym_user_has_list
	607	+ WHERE status = 1
	608	+ AND list_id = '.intval($mailInfo->list_id);
	609	+
	610	+ $addedEmails = acym_query($query);
	611	+
	612	+ return $addedEmails === false || $addedEmails === null ? null : intval($addedEmails);
	613	+ }
	614	+
	615	+ public function getXFollowups(array $options): array
	616	+ {
	617	+ $limit = $options['limit'] ?? 10;
	618	+ $offset = $options['offset'] ?? 0;
	619	+ $filters = $options['filters'] ?? [];
	620	+
	621	+ $conditions = [];
	622	+ foreach ($filters as $column => $filter) {
	623	+ switch ($column) {
	624	+ case 'id':
	625	+ case 'active':
	626	+ case 'send_one':
	627	+ $conditions[] = acym_secureDBColumn($column).' = '.intval($filter);
	628	+ break;
	629	+ case 'name':
	630	+ case 'trigger':
	631	+ case 'display_name':
	632	+ $conditions[] = '`'.acym_secureDBColumn($column).'` LIKE '.acym_escapeDB('%'.$filter.'%');
	633	+ break;
	634	+ default:
	635	+ $conditions[] = acym_secureDBColumn($column).' = '.acym_escapeDB('%'.$filter.'%');
	636	+ }
	637	+ }
	638	+
	639	+ $query = 'SELECT * FROM #__acym_followup ';
	640	+
	641	+ if (!empty($conditions)) {
	642	+ $query .= ' WHERE '.implode(' AND ', $conditions);
	643	+ }
	644	+
	645	+ $followUps = acym_loadObjectList($query, $this->pkey, $offset, $limit);
	646	+
	647	+ if (empty($followUps)) {
	648	+ return [];
	649	+ }
	650	+
	651	+ foreach ($followUps as &$followUp) {
	652	+ if (!empty($followUp->condition)) {
	653	+ $followUp->condition = json_decode($followUp->condition, true);
	654	+ }
	655	+ }
	656	+
	657	+ return $followUps;
	658	+ }
	659	+
	660	+ /**
	661	+ * @param string $mailId
	662	+ *
	663	+ * @return object|null
	664	+ */
	665	+ public function getOneByMailId(int $mailId): ?object
	666	+ {
	667	+ return acym_loadObject(
	668	+ 'SELECT followup.*
	669	+ FROM #__acym_followup AS followup
	670	+ JOIN #__acym_followup_has_mail AS map
	671	+ ON followup.id = map.followup_id
	672	+ WHERE mail_id = '.intval($mailId)
	673	+ );
	674	+ }
	675	+
	676	+ /**
	677	+ * @param $followupId
	678	+ *
	679	+ * @return object
	680	+ */
	681	+ public function getLastEmail(int $followupId): object
	682	+ {
	683	+ return acym_loadObject(
	684	+ 'SELECT mail_id as id, delay * delay_unit as totalDelay FROM #__acym_followup_has_mail WHERE followup_id = '.intval($followupId).' ORDER BY totalDelay DESC'
	685	+ );
	686	+ }
	687	+
	688	+ /**
	689	+ * @param int $followUpId
	690	+ * @param int $userId
	691	+ * @param int $additionalDelay
	692	+ * @param array $mailIdToSkip
	693	+ *
	694	+ * @return void
	695	+ */
	696	+ public function triggerFollowUp(int $followUpId, int $userId, int $additionalDelay = 0, array $mailIdToSkip = []): void
	697	+ {
	698	+ $followUp = $this->getOneById($followUpId);
	699	+
	700	+ if (empty($followUp)) {
	701	+ return;
	702	+ }
	703	+
	704	+ $values = intval($userId).', '.intval($followUp->list_id).', 1, '.acym_escapeDB(acym_date(time(), 'Y-m-d H:i:s'));
	705	+
	706	+ acym_query('INSERT IGNORE INTO #__acym_user_has_list (`user_id`, `list_id`, `status`, `subscription_date`) VALUES ('.$values.') ON DUPLICATE KEY UPDATE status = 1');
	707	+
	708	+ $followups = $this->getFollowupsWithMailsInfoByIds([$followUpId]);
	709	+ $allValues = [];
	710	+ foreach ($followups as $mails) {
	711	+ foreach ($mails as $mail) {
	712	+ if (in_array($mail->mail_id, $mailIdToSkip)) {
	713	+ continue;
	714	+ }
	715	+ $sendDate = time() + (intval($mail->delay) * intval($mail->delay_unit)) + $additionalDelay;
	716	+ $sendDate = acym_escapeDB(acym_date($sendDate, 'Y-m-d H:i:s', false));
	717	+ $allValues[] = '('.intval($mail->mail_id).', '.intval($userId).', '.$sendDate.', '.$this->config->get('priority_newsletter', 3).', 0'.')';
	718	+ }
	719	+ }
	720	+
	721	+ if (empty($allValues)) {
	722	+ return;
	723	+ }
	724	+
	725	+ $queryToProcess = 'INSERT IGNORE INTO #__acym_queue (`mail_id`, `user_id`, `sending_date`, `priority`, `try`) VALUES '.implode(', ', $allValues);
	726	+
	727	+ if (empty(acym_query($queryToProcess))) {
	728	+ acym_logError("Error adding follow up email to queue \n Values: ".implode(', ', $allValues)." \n Error: ".acym_getDBError());
	729	+ }
	730	+ }
	731	+}