Gecko [ v3.cesa.co.za ]

Name	Size	Permission	Date
error_handling	[ DIR ]	drwxr-sr-x	2025-07-07 15:54
mail	[ DIR ]	drwxr-sr-x	2025-06-14 11:17
ADQAnswers.php	3.16 KB	-rw-r--r--	2026-06-03 05:55
ADQAudit.php	6.51 KB	-rw-r--r--	2026-06-03 05:55
ADQAuditAnswers.php	1.21 KB	-rw-r--r--	2026-06-03 05:55
ADQFirms.php	18.41 KB	-rw-r--r--	2025-12-31 10:56
ADQuestionnairesAnswers.php	1.05 KB	-rw-r--r--	2026-06-03 05:55
ADQuestionnairesAudit.php	2.01 KB	-rw-r--r--	2026-06-03 05:55
ADecQuestionnaires.php	3.31 KB	-rw-r--r--	2026-06-03 05:55
ADecQuestions.php	541 B	-rw-r--r--	2026-06-03 05:55
ADec_Contacts.php	1.61 KB	-rw-r--r--	2026-06-03 05:55
ADec_OfficeContacts.php	7.43 KB	-rw-r--r--	2026-06-03 05:55
ADec_PIUnderwriters.php	742 B	-rw-r--r--	2026-06-03 05:55
ADec_Transformation.php	817 B	-rw-r--r--	2026-06-03 05:55
ADec_tblMemberFirmOffices.php	1.49 KB	-rw-r--r--	2026-06-03 05:55
ADec_tblMemberFirmSkills.php	2.38 KB	-rw-r--r--	2026-06-03 05:55
ADec_tblMemberFirms.php	19.37 KB	-rw-r--r--	2026-06-03 05:55
AnonRespondees.php	4.25 KB	-rw-r--r--	2026-06-05 11:41
AwardsEntries.php	2.4 KB	-rw-r--r--	2026-06-06 10:48
BaseTableClass.php	950 B	-rw-r--r--	2026-04-26 08:24
BatchJob.php	4.46 KB	-rw-r--r--	2026-06-03 05:55
BceAssignmentFeedbackFormsReportService.php	6.77 KB	-rw-r--r--	2026-05-25 13:06
CCEAssignment.php	12.44 KB	-rw-r--r--	2026-05-26 14:26
CCEAssignmentMarking.php	26.26 KB	-rw-r--r--	2026-08-14 12:06
CCEBooks.php	524 B	-rw-r--r--	2026-06-03 05:55
CCEConvenorAuth.php	3.06 KB	-rw-r--r--	2026-08-19 11:18
CCECourseSchedules.php	579 B	-rw-r--r--	2026-06-03 05:55
CCEDocCategories.php	529 B	-rw-r--r--	2026-06-03 05:55
CCEDocuments.php	8.15 KB	-rw-r--r--	2026-08-20 12:16
CCEExam.php	15.87 KB	-rw-r--r--	2026-06-03 06:35
CCEExamQuestion.php	1.76 KB	-rw-r--r--	2026-06-03 05:55
CCEFacilitatorUser.php	931 B	-rw-r--r--	2026-06-03 05:55
CCEMessageLog.php	951 B	-rw-r--r--	2026-06-03 05:55
CCEMessages.php	8.88 KB	-rw-r--r--	2026-07-01 13:20
CCEModuleMarksService.php	7.16 KB	-rw-r--r--	2026-05-26 10:30
CCEModules.php	1.16 KB	-rw-r--r--	2026-06-03 05:55
CCEResourceGroups.php	591 B	-rw-r--r--	2026-06-03 05:55
CCEResources.php	896 B	-rw-r--r--	2026-06-03 05:55
CCESetup.php	256 B	-rw-r--r--	2026-02-09 16:20
CCEStAssigExtension.php	2.07 KB	-rw-r--r--	2026-06-03 05:55
CCEStAssigExtensionRequest.php	6.65 KB	-rw-r--r--	2026-06-03 05:55
CCEStudentAssignment.php	16.61 KB	-rw-r--r--	2026-07-15 14:44
CCEStudentCourses.php	611 B	-rw-r--r--	2026-06-03 05:55
CCEStudentDisplayOrder.php	4.96 KB	-rw-r--r--	2026-05-26 09:32
CCEStudents.php	8.4 KB	-rw-r--r--	2026-08-19 11:18
CCETemplates.php	896 B	-rw-r--r--	2026-06-03 05:55
CCEWeeklySessions.php	2.27 KB	-rw-r--r--	2026-02-09 16:25
CPDSPReview.php	624 B	-rw-r--r--	2026-06-03 05:55
CPDServiceProvider.php	4.57 KB	-rw-r--r--	2026-06-12 11:48
CPDTrainingMaterial.php	3.96 KB	-rw-r--r--	2026-06-12 11:48
CceUploadedFileService.php	34.9 KB	-rw-r--r--	2026-06-24 12:03
Certificates.php	8.93 KB	-rw-r--r--	2026-08-18 15:42
Contact.php	690 B	-rw-r--r--	2026-06-03 05:55
ErrorLog.php	1.22 KB	-rw-r--r--	2026-08-24 10:12
EventSchedules.php	5.39 KB	-rw-r--r--	2025-12-20 10:03
FileUploads.php	2.1 KB	-rw-r--r--	2026-06-03 05:55
HTMLHelper.php	17.2 KB	-rw-r--r--	2026-06-03 05:55
KeyValue.php	4.9 KB	-rw-r--r--	2026-04-25 09:34
Mailer.php	12.78 KB	-rw-r--r--	2026-07-15 09:50
MarketingAttendees.php	36.87 KB	-rw-r--r--	2026-08-14 12:06
MarketingPayments.php	2.52 KB	-rw-r--r--	2026-06-03 05:55
MemberContact.php	1.04 KB	-rw-r--r--	2026-06-03 05:55
MemberFirm.php	17.71 KB	-rw-r--r--	2026-06-03 05:55
MemberFirmOffices.php	752 B	-rw-r--r--	2026-06-03 05:55
MemberFirmSkill.php	18.04 KB	-rw-r--r--	2026-06-03 05:55
MemberPortalTransformationProxy.php	4.44 KB	-rw-r--r--	2026-05-20 09:08
MemberPortalUrls.php	1.82 KB	-rw-r--r--	2026-05-20 09:53
MsgAttachFileService.php	4.92 KB	-rw-r--r--	2026-06-22 08:37
OfficeContact.php	1.01 KB	-rw-r--r--	2026-06-03 05:55
Picklists.php	539 B	-rw-r--r--	2026-06-03 05:55
Questionnaires.php	849 B	-rw-r--r--	2026-01-23 12:39
SchoolAttendeeBulkMailerService.php	34.26 KB	-rw-r--r--	2026-08-11 10:45
SchoolAttendees.php	73.21 KB	-rw-r--r--	2026-08-11 10:45
SchoolCourses.php	7.98 KB	-rw-r--r--	2026-08-19 11:59
SchoolEvents.php	45.54 KB	-rw-r--r--	2026-08-18 15:42
SchoolPayments.php	4.48 KB	-rw-r--r--	2026-04-13 09:33
SchoolPresenters.php	1.86 KB	-rw-r--r--	2026-01-23 12:43
SchoolWeeksCoursesDashboardProxy.php	10.69 KB	-rw-r--r--	2026-08-14 12:32
TrainingProvider.php	8.06 KB	-rw-r--r--	2026-01-23 12:43
TrainingProviderDoc.php	554 B	-rw-r--r--	2026-06-03 05:55
Transformation.php	10.87 KB	-rw-r--r--	2026-05-13 09:36
UnempPractitioners.php	1.43 KB	-rw-r--r--	2026-06-03 05:55
Utils.php	13.72 KB	-rw-r--r--	2026-07-23 07:28
WeeklySessionsParser.php	7.87 KB	-rw-r--r--	2026-02-09 16:20
cceMessageAttachmentUpload.inc.php	5.12 KB	-rw-r--r--	2026-06-03 05:55
cceMessageEditor.inc.php	9.26 KB	-rw-r--r--	2026-07-01 13:20
cce_file_download_endpoint.php	1.75 KB	-rw-r--r--	2026-06-24 08:38
cstUnreadMessages.php	2.1 KB	-rw-r--r--	2026-07-01 13:20
cstWeeksCourses.php	1.84 KB	-rw-r--r--	2026-08-14 12:06
load_phpmailer.php	742 B	-rw-r--r--	2026-07-17 14:57
maill.php	19.45 KB	-rw-r--r--	2026-08-19 12:23
require_ewmysql.php	1.2 KB	-rw-r--r--	2026-08-24 10:12
sqlFunctions.php	10.48 KB	-rw-r--r--	2026-08-24 10:12

Rename

<?php
/*
 * Service: queue School attendee bulk emails into mail system tables.
 */

class SchoolAttendeeBulkMailerService
{
    const SESSION_REDIRECT_SEND_EVENT_LINK = 'SCE_REDIRECT_SEND_EVENT_LINK';

private $sqlf;
    private $sqlfMail;

public function __construct($sqlf, $sqlfMail)
    {
        $this->sqlf = $sqlf;
        $this->sqlfMail = $sqlfMail;
    }

/**
     * URL for the paid event-link send screen (custom, not PHPMaker-generated).
     *
     * @param int $eventId
     * @param int $attendeeId
     * @param string $focus
     * @return string
     */
    public static function buildSendEventLinkUrl($eventId, $attendeeId, $focus = 'paid_link')
    {
        $eventId = intval($eventId);
        $attendeeId = intval($attendeeId);
        if ($eventId <= 0 || $attendeeId <= 0) {
            return '';
        }
        $focus = preg_replace('/[^a-z0-9_]/', '', strtolower((string) $focus));
        if ($focus === '') {
            $focus = 'paid_link';
        }
        return '/echasl23/SchoolAttendeeBulkEmail.php?EventID=' . $eventId
            . '&AttendeeID=' . $attendeeId
            . '&focus=' . rawurlencode($focus);
    }

/**
     * @param array|null $row SchoolPayments row (AmountPaid / AmountDue)
     * @return bool
     */
    public static function isPaymentRowPaid($row)
    {
        if (!is_array($row)) {
            return false;
        }
        $amountPaid = isset($row['AmountPaid']) ? (float) $row['AmountPaid'] : 0.0;
        $amountDue = isset($row['AmountDue']) ? (float) $row['AmountDue'] : 0.0;
        return $amountPaid >= $amountDue;
    }

/**
     * True when save should redirect to Send Event Link (insert paid, or transition to paid).
     *
     * @param array|null $rsold
     * @param array|null $rsnew
     * @return bool
     */
    public static function shouldRedirectAfterPaymentSave($rsold, $rsnew)
    {
        if (!is_array($rsnew) || !self::isPaymentRowPaid($rsnew)) {
            return false;
        }
        if (!is_array($rsold) || !array_key_exists('AmountPaid', $rsold)) {
            return true;
        }
        return !self::isPaymentRowPaid($rsold);
    }

/**
     * Store redirect URL for Page_Redirecting after SchoolPayments save.
     *
     * @param int $eventId
     * @param int $attendeeId
     * @return bool
     */
    public static function rememberSendEventLinkRedirect($eventId, $attendeeId)
    {
        $url = self::buildSendEventLinkUrl($eventId, $attendeeId, 'paid_link');
        if ($url === '') {
            return false;
        }
        $_SESSION[self::SESSION_REDIRECT_SEND_EVENT_LINK] = $url;
        return true;
    }

/**
     * Apply pending redirect URL (PHPMaker Page_Redirecting).
     *
     * @param string $url
     * @return bool
     */
    public static function consumeSendEventLinkRedirect(&$url)
    {
        if (empty($_SESSION[self::SESSION_REDIRECT_SEND_EVENT_LINK])) {
            return false;
        }
        $url = (string) $_SESSION[self::SESSION_REDIRECT_SEND_EVENT_LINK];
        unset($_SESSION[self::SESSION_REDIRECT_SEND_EVENT_LINK]);
        return true;
    }

/**
     * @param array|null $rsold
     * @param array $rsnew
     * @return bool
     */
    public static function maybeRememberRedirectAfterPaymentSave($rsold, $rsnew)
    {
        if (!self::shouldRedirectAfterPaymentSave($rsold, $rsnew)) {
            return false;
        }
        $eventId = isset($rsnew['EventID']) ? intval($rsnew['EventID']) : 0;
        $attendeeId = isset($rsnew['AttendeeID']) ? intval($rsnew['AttendeeID']) : 0;
        return self::rememberSendEventLinkRedirect($eventId, $attendeeId);
    }

public static function getPaidLinkFocusSubject()
    {
        return 'Course Payment Confirmed – Access Link Provided';
    }

public static function getPaidLinkFocusTemplate()
    {
        return "Dear *KnownAs* *ContactLastName*\n\n"
            . "Thank you for your payment in response to our course confirmation email regarding the *CourseName*, "
            . "scheduled to start on *StartDate* and conclude on *EndDate*, in which we advised that "
            . "CESA School of Consulting Engineering (SCE) had not yet received your payment.\n\n"
            . "Herewith, please find the link to access the course:\n\n"
            . "*OnlineCourseLink*\n\n"
            . "We look forward to welcoming you and trust you will find the experience valuable.\n\n"
            . "With kind regards,\n\n"
            . "Melissa Jardine";
    }

public function getEventWithAttendees($eventId)
    {
        $eventId = intval($eventId);
        if ($eventId <= 0) {
            return array('event' => null, 'attendees' => array());
        }

$event = $this->sqlf->getRow("SELECT * FROM SchoolEvents WHERE EventID = " . $eventId . " LIMIT 1");
        $attendees = $this->sqlf->getAll(
            "SELECT AttendeeID, ContactFirstName, ContactLastName, EmailAddress, Cancelled,
                    CompanyName, CompanyType, PhoneNumber,
                    CASE
                        WHEN EXISTS (
                            SELECT 1
                            FROM SchoolPayments
                            WHERE SchoolPayments.AttendeeID = SchoolAttendees.AttendeeID
                              AND IFNULL(SchoolPayments.AmountPaid, 0) >= IFNULL(SchoolPayments.AmountDue, 0)
                        ) THEN 1
                        ELSE 0
                    END AS IsPaid
             FROM SchoolAttendees
             WHERE EventID = " . $eventId . "
             ORDER BY ContactFirstName ASC, ContactLastName ASC, AttendeeID ASC"
        );

if (!is_array($attendees)) {
            $attendees = array();
        }

return array(
            'event' => $event,
            'attendees' => $attendees,
        );
    }

public function queueEventAttendeeMail(array $payload)
    {
        $eventId = isset($payload['event_id']) ? intval($payload['event_id']) : 0;
        if ($eventId <= 0) {
            throw new Exception('Invalid event selected.');
        }

$event = $this->sqlf->getRow("SELECT * FROM SchoolEvents WHERE EventID = " . $eventId . " LIMIT 1");
        if (!is_array($event) || count($event) <= 0) {
            throw new Exception('Event not found.');
        }

$onlineCourseLink = isset($payload['online_course_link']) ? trim((string) $payload['online_course_link']) : '';
        $subject = isset($payload['subject']) ? trim((string) $payload['subject']) : '';
        $template = isset($payload['template']) ? trim((string) $payload['template']) : '';
        $unpaidTemplate = isset($payload['unpaid_template']) ? trim((string) $payload['unpaid_template']) : '';
        $sendMode = isset($payload['send_mode']) ? trim((string) $payload['send_mode']) : 'all';
        $selectedIds = isset($payload['selected_attendee_ids']) && is_array($payload['selected_attendee_ids'])
            ? $payload['selected_attendee_ids']
            : array();
        $updateEventLink = !empty($payload['update_event_link']);
        $testOnly = !empty($payload['test_only']);
        $testEmail = isset($payload['test_email']) ? trim((string) $payload['test_email']) : '';
        $bccAddresses = isset($payload['bcc_addresses']) ? (string) $payload['bcc_addresses'] : '';
        $sendPaidLinkAttendeeMap = $this->buildSendPaidLinkAttendeeMap(
            isset($payload['send_paid_link_attendee_ids']) ? $payload['send_paid_link_attendee_ids'] : array()
        );

if ($subject === '') {
            throw new Exception('Subject is required.');
        }
        if ($template === '') {
            throw new Exception('Message template is required.');
        }
        if ($unpaidTemplate === '') {
            throw new Exception('Unpaid attendee message template is required.');
        }

if ($testOnly) {
            if ($testEmail === '' || !filter_var($testEmail, FILTER_VALIDATE_EMAIL)) {
                throw new Exception('A valid test e-mail address is required when using Test-only mode.');
            }
        }

if ($sendMode === 'selected' && count($selectedIds) <= 0) {
            throw new Exception('Select at least one attendee or choose "all attendees".');
        }

$attendees = $this->sqlf->getAll(
            "SELECT AttendeeID, ContactFirstName, ContactLastName, EmailAddress, Cancelled,
                    CASE
                        WHEN EXISTS (
                            SELECT 1
                            FROM SchoolPayments
                            WHERE SchoolPayments.AttendeeID = SchoolAttendees.AttendeeID
                              AND IFNULL(SchoolPayments.AmountPaid, 0) >= IFNULL(SchoolPayments.AmountDue, 0)
                        ) THEN 1
                        ELSE 0
                    END AS IsPaid
             FROM SchoolAttendees
             WHERE EventID = " . $eventId
        );
        if (!is_array($attendees) || count($attendees) <= 0) {
            throw new Exception('No attendees found for this event.');
        }

$selectedMap = array();
        foreach ($selectedIds as $oneId) {
            $selectedMap[intval($oneId)] = true;
        }

$targets = array();
        foreach ($attendees as $attendee) {
            $attendeeId = intval($attendee['AttendeeID']);
            $email = trim((string) $attendee['EmailAddress']);
            $cancelled = intval($attendee['Cancelled']) > 0;

if ($cancelled || $email === '' || stripos($email, 'no e-mail') === 0) {
                continue;
            }

if ($sendMode === 'selected' && !isset($selectedMap[$attendeeId])) {
                continue;
            }

$targets[] = $attendee;
        }

if (!$testOnly && count($targets) <= 0) {
            throw new Exception('No valid attendee e-mail addresses matched your selection.');
        }

$messageFrom = isset($_SESSION[EW_SESSION_USER_NAME]) ? $_SESSION[EW_SESSION_USER_NAME] : 'SCE Admin';
        if (!is_string($messageFrom) || trim($messageFrom) === '') {
            $messageFrom = 'SCE Admin';
        }

$headers = 'From: website@cesa.co.za';
        $bccList = $testOnly ? array() : self::parseEmailAddressList($bccAddresses);
        if (count($bccList) > 0) {
            $headers .= "\r\n" . 'Bcc: ' . implode(',', $bccList);
        }

$eventLabel = isset($event['EventName']) ? $event['EventName'] : ('Event ' . $eventId);
        $sentToGroupBase = ($testOnly ? 'TEST ONLY - ' : '') . 'SCE Event Attendees: ' . $eventLabel;

$queuedCount = 0;
        $messageIds = array();

if ($testOnly) {
            // ToSend has a unique key on (SendTo, MessageID): use two Messages rows so paid/unpaid tests are distinct.
            $exampleAttendee = $this->resolveExampleAttendeeForTest($targets, $attendees);

$paidBody = $this->buildMessageForAttendee($template, $exampleAttendee, $event, $onlineCourseLink);
            $unpaidBody = $this->buildMessageForAttendee($unpaidTemplate, $exampleAttendee, $event, $onlineCourseLink);

$paidMessageId = $this->createMessagesCampaign(
                $sentToGroupBase . ' (Paid template)',
                $messageFrom,
                $headers,
                $subject . ' [TEST - Paid]',
                $template,
                1
            );
            $this->queueToSendRow($testEmail, $paidMessageId, $paidBody);
            $messageIds[] = $paidMessageId;
            $queuedCount++;

$unpaidMessageId = $this->createMessagesCampaign(
                $sentToGroupBase . ' (Unpaid template)',
                $messageFrom,
                $headers,
                $subject . ' [TEST - Unpaid]',
                $unpaidTemplate,
                1
            );
            $this->queueToSendRow($testEmail, $unpaidMessageId, $unpaidBody);
            $messageIds[] = $unpaidMessageId;
            $queuedCount++;

$messageId = $paidMessageId;
        } else {
            $messageId = $this->createMessagesCampaign(
                $sentToGroupBase,
                $messageFrom,
                $headers,
                $subject,
                $template,
                count($targets)
            );
            $messageIds[] = $messageId;

foreach ($targets as $attendee) {
                $attendeeTemplate = $this->resolveAttendeeTemplate($attendee, $template, $unpaidTemplate, $sendPaidLinkAttendeeMap);
                $attendeeMessage = $this->buildMessageForAttendee($attendeeTemplate, $attendee, $event, $onlineCourseLink);
                $this->queueToSendRow(trim((string) $attendee['EmailAddress']), $messageId, $attendeeMessage);
                $queuedCount++;
            }
        }

if ($updateEventLink) {
            $this->sqlf->execute(
                "UPDATE SchoolEvents SET OnlineCourseLink = " . $this->sqlf->qstr($onlineCourseLink)
                    . " WHERE EventID = " . $eventId
            );
        }

return array(
            'message_id' => $messageId,
            'message_ids' => $messageIds,
            'queued_count' => $queuedCount,
            'event_id' => $eventId,
            'test_only' => $testOnly,
        );
    }

/**
     * Queue a single editable template to all non-cancelled attendees with valid email.
     * Used by the manual "insufficient delegates / course may proceed" email page.
     *
     * @param array<string, mixed> $payload
     * @return array<string, mixed>
     */
    public function queueSingleTemplateToAllActiveAttendees(array $payload)
    {
        $eventId = isset($payload['event_id']) ? intval($payload['event_id']) : 0;
        if ($eventId <= 0) {
            throw new Exception('Invalid event selected.');
        }

if (empty($payload['confirm_send'])) {
            throw new Exception('Please confirm that these emails should be sent before submitting.');
        }

$subject = isset($payload['subject']) ? trim((string) $payload['subject']) : '';
        $template = isset($payload['template']) ? trim((string) $payload['template']) : '';
        $onlineCourseLink = isset($payload['online_course_link'])
            ? trim((string) $payload['online_course_link'])
            : (isset($event['OnlineCourseLink']) ? trim((string) $event['OnlineCourseLink']) : '');
        $campaignLabel = isset($payload['campaign_label'])
            ? trim((string) $payload['campaign_label'])
            : 'SCE Event Attendees';

if ($subject === '') {
            throw new Exception('Subject is required.');
        }
        if ($template === '') {
            throw new Exception('Message template is required.');
        }

$attendees = $this->sqlf->getAll(
            "SELECT AttendeeID, ContactFirstName, ContactLastName, EmailAddress, Cancelled
             FROM SchoolAttendees
             WHERE EventID = " . $eventId . "
             ORDER BY ContactFirstName ASC, ContactLastName ASC, AttendeeID ASC"
        );
        if (!is_array($attendees) || count($attendees) <= 0) {
            throw new Exception('No attendees found for this event.');
        }

$targets = array();
        foreach ($attendees as $attendee) {
            $email = trim((string) $attendee['EmailAddress']);
            $cancelled = intval($attendee['Cancelled']) > 0;

if ($cancelled || $email === '' || stripos($email, 'no e-mail') === 0) {
                continue;
            }

$targets[] = $attendee;
        }

if (count($targets) <= 0) {
            throw new Exception('No valid non-cancelled attendee e-mail addresses found for this event.');
        }

$headers = 'From: website@cesa.co.za';
        $eventLabel = isset($event['EventName']) ? $event['EventName'] : ('Event ' . $eventId);
        $sentToGroup = $campaignLabel . ': ' . $eventLabel;

$messageId = $this->createMessagesCampaign(
            $sentToGroup,
            $messageFrom,
            $headers,
            $subject,
            $template,
            count($targets)
        );

$queuedCount = 0;
        foreach ($targets as $attendee) {
            $attendeeMessage = $this->buildMessageForAttendee($template, $attendee, $event, $onlineCourseLink);
            $this->queueToSendRow(trim((string) $attendee['EmailAddress']), $messageId, $attendeeMessage);
            $queuedCount++;
        }

return array(
            'message_id' => $messageId,
            'queued_count' => $queuedCount,
            'event_id' => $eventId,
        );
    }

/**
     * Default refund/credit paragraph for paid delegates on course-cancelled emails.
     */
    public static function getCourseCancelledPaidRefundParagraph()
    {
        return 'If you have already made payment, our accounts team will promptly process a full refund, '
            . 'or alternatively, the credit can be applied to a future course, either for yourself or someone else in your company.';
    }

/**
     * Queue the Course Cancelled email to all non-cancelled attendees with valid email.
     * Requires the School Event to be marked Cancelled and an explicit confirm_send flag.
     *
     * @param array<string, mixed> $payload
     * @return array<string, mixed>
     */
    public function queueCourseCancelledEmail(array $payload)
    {
        $eventId = isset($payload['event_id']) ? intval($payload['event_id']) : 0;
        if ($eventId <= 0) {
            throw new Exception('Invalid event selected.');
        }

if (empty($payload['confirm_send'])) {
            throw new Exception('Please confirm that these emails should be sent before submitting.');
        }

if (intval(isset($event['Cancelled']) ? $event['Cancelled'] : 0) <= 0) {
            throw new Exception('This School Event is not marked as cancelled. Mark it cancelled before sending this email.');
        }

if ($subject === '') {
            throw new Exception('Subject is required.');
        }
        if ($template === '') {
            throw new Exception('Message template is required.');
        }

$attendees = $this->sqlf->getAll(
            "SELECT AttendeeID, ContactFirstName, ContactLastName, EmailAddress, Cancelled,
                    CASE
                        WHEN EXISTS (
                            SELECT 1
                            FROM SchoolPayments
                            WHERE SchoolPayments.AttendeeID = SchoolAttendees.AttendeeID
                              AND IFNULL(SchoolPayments.AmountPaid, 0) >= IFNULL(SchoolPayments.AmountDue, 0)
                        ) THEN 1
                        ELSE 0
                    END AS IsPaid
             FROM SchoolAttendees
             WHERE EventID = " . $eventId . "
             ORDER BY ContactFirstName ASC, ContactLastName ASC, AttendeeID ASC"
        );
        if (!is_array($attendees) || count($attendees) <= 0) {
            throw new Exception('No attendees found for this event.');
        }

$targets = array();
        foreach ($attendees as $attendee) {
            $email = trim((string) $attendee['EmailAddress']);
            $cancelled = intval($attendee['Cancelled']) > 0;

if ($cancelled || $email === '' || stripos($email, 'no e-mail') === 0) {
                continue;
            }

$targets[] = $attendee;
        }

if (count($targets) <= 0) {
            throw new Exception('No valid non-cancelled attendee e-mail addresses found for this event.');
        }

$headers = 'From: website@cesa.co.za';
        $eventLabel = isset($event['EventName']) ? $event['EventName'] : ('Event ' . $eventId);
        $sentToGroup = $campaignLabel . ': ' . $eventLabel;

return array(
            'message_id' => $messageId,
            'queued_count' => $queuedCount,
            'event_id' => $eventId,
        );
    }

/**
     * Remember original event dates for the "edit schedule, then send postponement" flow.
     */
    public static function rememberOriginalEventDates($eventId, $startDate, $endDate)
    {
        $eventId = intval($eventId);
        if ($eventId <= 0) {
            return;
        }

if (!isset($_SESSION['SCE_POSTPONE_ORIGINAL_DATES']) || !is_array($_SESSION['SCE_POSTPONE_ORIGINAL_DATES'])) {
            $_SESSION['SCE_POSTPONE_ORIGINAL_DATES'] = array();
        }

if (!isset($_SESSION['SCE_POSTPONE_ORIGINAL_DATES'][$eventId])) {
            $_SESSION['SCE_POSTPONE_ORIGINAL_DATES'][$eventId] = array(
                'StartDate' => (string) $startDate,
                'EndDate' => (string) $endDate,
            );
        }
    }

/**
     * @return array{StartDate:string,EndDate:string}|null
     */
    public static function getRememberedOriginalEventDates($eventId)
    {
        $eventId = intval($eventId);
        if ($eventId <= 0 || empty($_SESSION['SCE_POSTPONE_ORIGINAL_DATES'][$eventId]) || !is_array($_SESSION['SCE_POSTPONE_ORIGINAL_DATES'][$eventId])) {
            return null;
        }

return $_SESSION['SCE_POSTPONE_ORIGINAL_DATES'][$eventId];
    }

/**
     * Queue a Course Postponed email (dates TBA, or new dates after schedule edit).
     *
     * @param array<string, mixed> $payload
     * @return array<string, mixed>
     */
    public function queueCoursePostponedEmail(array $payload)
    {
        $eventId = isset($payload['event_id']) ? intval($payload['event_id']) : 0;
        if ($eventId <= 0) {
            throw new Exception('Invalid event selected.');
        }

if (empty($payload['confirm_send'])) {
            throw new Exception('Please confirm that these emails should be sent before submitting.');
        }

$mode = isset($payload['mode']) ? trim((string) $payload['mode']) : 'tba';
        if ($mode !== 'tba' && $mode !== 'dates') {
            throw new Exception('Invalid postponement email mode.');
        }

if ($subject === '') {
            throw new Exception('Subject is required.');
        }
        if ($template === '') {
            throw new Exception('Message template is required.');
        }

$extraReplacements = array();
        if ($mode === 'dates') {
            $originalStart = isset($payload['original_start_date']) ? trim((string) $payload['original_start_date']) : '';
            $originalEnd = isset($payload['original_end_date']) ? trim((string) $payload['original_end_date']) : '';
            $newStart = isset($payload['new_start_date']) ? trim((string) $payload['new_start_date']) : '';
            $newEnd = isset($payload['new_end_date']) ? trim((string) $payload['new_end_date']) : '';

if ($originalStart === '' || $originalEnd === '' || $newStart === '' || $newEnd === '') {
                throw new Exception('Original and new start/end dates are required for the new-dates postponement email.');
            }

$extraReplacements['*OriginalStartDate*'] = $originalStart;
            $extraReplacements['*OriginalEndDate*'] = $originalEnd;
            $extraReplacements['*NewStartDate*'] = $newStart;
            $extraReplacements['*NewEndDate*'] = $newEnd;
        }

$targets = array();
        foreach ($attendees as $attendee) {
            $email = trim((string) $attendee['EmailAddress']);
            $cancelled = intval($attendee['Cancelled']) > 0;

if ($cancelled || $email === '' || stripos($email, 'no e-mail') === 0) {
                continue;
            }

$targets[] = $attendee;
        }

if (count($targets) <= 0) {
            throw new Exception('No valid non-cancelled attendee e-mail addresses found for this event.');
        }

$headers = 'From: website@cesa.co.za';
        $eventLabel = isset($event['EventName']) ? $event['EventName'] : ('Event ' . $eventId);
        $sentToGroup = $campaignLabel . ': ' . $eventLabel;

$queuedCount = 0;
        foreach ($targets as $attendee) {
            $attendeeMessage = $this->buildMessageForAttendee($template, $attendee, $event, $onlineCourseLink, $extraReplacements);
            $this->queueToSendRow(trim((string) $attendee['EmailAddress']), $messageId, $attendeeMessage);
            $queuedCount++;
        }

return array(
            'message_id' => $messageId,
            'queued_count' => $queuedCount,
            'event_id' => $eventId,
            'mode' => $mode,
        );
    }

/**
     * @param array<int, array<string, mixed>> $targets
     * @param array<int, array<string, mixed>> $attendees
     * @return array<string, string>
     */
    private function resolveExampleAttendeeForTest(array $targets, array $attendees)
    {
        $exampleAttendee = array(
            'ContactFirstName' => 'Test',
            'ContactLastName' => 'Attendee',
        );

$source = count($targets) > 0 ? $targets[0] : (count($attendees) > 0 ? $attendees[0] : null);
        if (is_array($source)) {
            $exampleAttendee['ContactFirstName'] = (string) ($source['ContactFirstName'] ?? $exampleAttendee['ContactFirstName']);
            $exampleAttendee['ContactLastName'] = (string) ($source['ContactLastName'] ?? $exampleAttendee['ContactLastName']);
        }

return $exampleAttendee;
    }

/**
     * Insert a Messages row and return MessageID.
     */
    private function createMessagesCampaign($sentToGroup, $messageFrom, $headers, $subject, $bodyTemplate, $numRecipients)
    {
        $messageId = intval($this->sqlfMail->getNewId('Messages', 'MessageID'));
        if ($messageId <= 0) {
            throw new Exception('Could not allocate MessageID for mail campaign.');
        }

$messagesRow = array(
            'MessageID' => $messageId,
            'SentToGroup' => $sentToGroup,
            'DateSent' => date('Y-m-d H:i:s'),
            'MessageFrom' => $messageFrom,
            'MessageSize' => strlen((string) $bodyTemplate),
            'Subject' => $subject,
            'Message' => $bodyTemplate,
            'Headers' => $headers,
            'NumRecipients' => intval($numRecipients),
            'SendComplete' => 0,
        );

$insertResult = $this->sqlfMail->autoExecute('Messages', $messagesRow, 'INSERT', null, 'MessageID');
        if ($insertResult === false) {
            throw new Exception('Could not create Messages record.');
        }

$insertedMessage = $this->sqlfMail->getRow("SELECT MessageID FROM Messages WHERE MessageID = " . $messageId . " LIMIT 1");
        if (!is_array($insertedMessage) || !isset($insertedMessage['MessageID'])) {
            throw new Exception('Could not create Messages record.');
        }

return $messageId;
    }

/**
     * Insert one ToSend row (unique per SendTo + MessageID).
     */
    private function queueToSendRow($sendTo, $messageId, $personalizedBody)
    {
        $insertResult = $this->sqlfMail->autoExecute(
            'ToSend',
            array(
                'SendTo' => $sendTo,
                'MessageID' => intval($messageId),
                'Message' => $personalizedBody,
            ),
            'INSERT',
            null,
            'ToSendID'
        );

if ($insertResult === false) {
            throw new Exception('Could not queue message for ' . $sendTo . '.');
        }
    }

/**
     * Parse comma-, semicolon-, or whitespace-separated email addresses.
     *
     * @param string $input
     * @return array
     */
    private static function parseEmailAddressList($input)
    {
        if (!is_string($input) || trim($input) === '') {
            return array();
        }

$parts = preg_split('/[\s,;]+/', trim($input), -1, PREG_SPLIT_NO_EMPTY);
        $valid = array();

foreach ($parts as $email) {
            $email = trim($email);
            if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $valid[] = $email;
            }
        }

return array_values(array_unique($valid));
    }

/**
     * @param array<int|string> $attendeeIds
     * @return array<int, bool>
     */
    private function buildSendPaidLinkAttendeeMap($attendeeIds)
    {
        $map = array();
        if (!is_array($attendeeIds)) {
            return $map;
        }

foreach ($attendeeIds as $attendeeId) {
            $id = intval($attendeeId);
            if ($id > 0) {
                $map[$id] = true;
            }
        }

return $map;
    }

/**
     * Choose paid or unpaid template for an attendee.
     *
     * @param array<string, mixed> $attendee
     * @param array<int, bool> $sendPaidLinkAttendeeMap
     */
    private function resolveAttendeeTemplate(array $attendee, $paidTemplate, $unpaidTemplate, array $sendPaidLinkAttendeeMap)
    {
        $isPaid = isset($attendee['IsPaid']) && intval($attendee['IsPaid']) === 1;
        $attendeeId = intval($attendee['AttendeeID']);
        if ($isPaid || isset($sendPaidLinkAttendeeMap[$attendeeId])) {
            return $paidTemplate;
        }

return $unpaidTemplate;
    }

private function buildMessageForAttendee($template, array $attendee, array $event, $onlineCourseLink, array $extraReplacements = array())
    {
        $fullName = trim((string) $attendee['ContactFirstName'] . ' ' . (string) $attendee['ContactLastName']);
        $eventName = isset($event['EventName']) ? (string) $event['EventName'] : '';
        $startDate = isset($event['StartDate']) ? (string) $event['StartDate'] : '';
        $endDate = isset($event['EndDate']) ? (string) $event['EndDate'] : '';
        $startTime = isset($event['StartTime']) ? (string) $event['StartTime'] : '';
        $endTime = isset($event['EndTime']) ? (string) $event['EndTime'] : '';
        $isPaid = isset($attendee['IsPaid']) && intval($attendee['IsPaid']) === 1;
        $paidRefundParagraph = $isPaid
            ? "\n" . self::getCourseCancelledPaidRefundParagraph() . "\n"
            : '';
        $replacements = array(
            '*FirstName*' => (string) $attendee['ContactFirstName'],
            '*LastName*' => (string) $attendee['ContactLastName'],
            '*FullName*' => $fullName,
            '*KnownAs*' => (string) $attendee['ContactFirstName'],
            '*ContactLastName*' => (string) $attendee['ContactLastName'],
            '*CourseName*' => $eventName,
            '*EventName*' => $eventName,
            '*StartDate*' => $startDate,
            '*EndDate*' => $endDate,
            '*StartTime*' => $startTime,
            '*EndTime*' => $endTime,
            '*OnlineCourseLink*' => (string) $onlineCourseLink,
            '*PaidRefundParagraph*' => $paidRefundParagraph,
            '*OriginalStartDate*' => isset($event['StartDate']) ? (string) $event['StartDate'] : '',
            '*OriginalEndDate*' => isset($event['EndDate']) ? (string) $event['EndDate'] : '',
            '*NewStartDate*' => isset($event['StartDate']) ? (string) $event['StartDate'] : '',
            '*NewEndDate*' => isset($event['EndDate']) ? (string) $event['EndDate'] : '',
        );

if (count($extraReplacements) > 0) {
            $replacements = array_merge($replacements, $extraReplacements);
        }

return strtr($template, $replacements);
    }
}