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

/*
 * Contact otto@igotafrica.com for details
 */

include_once('BaseTableClass.php');

/**
 * Control the  SchoolEvents table
 *
 * @author Otto Saayman
 */
class SchoolEvents extends BaseTableClass
{

/**
     * Find an event by its ID
     *
     * @param int $eventId
     * @return array
     */
    public function getAll()
    {
        return $this->sqlf->getAll("SELECT * FROM SchoolEvents");
    }

/**
     * Find an event by its ID
     *
     * @param int $eventId
     * @return array
     */
    public function getAllWithEmptyWebCategory()
    {
        return $this->sqlf->getAll("SELECT * FROM SchoolEvents WHERE (WebCategory = '' OR WebCategory IS NULL) 
            AND StartDate >= '" . date('Y-m-01') . "' 
            ORDER BY StartDate ASC");
    }

/**
     * Find an event by its ID
     *
     * @param int $eventId
     * @return array
     */
    public function findById($eventId)
    {
        return $this->sqlf->getRow("SELECT * FROM SchoolEvents WHERE SchoolEvents.EventId = $eventId");
    }

/**
     * Find an event by EventName, StartDate, and EndDate
     *
     * @param string $eventName
     * @param string $startDate (Y-m-d format)
     * @param string $endDate (Y-m-d format)
     * @return array|false Event data if found, false otherwise
     */
    public function findByEventNameAndDates($eventName, $startDate, $endDate)
    {
        global $conni;
        
        // Escape the values
        if (isset($conni) && ($conni instanceof mysqli)) {
            $eventName = mysqli_real_escape_string($conni, $eventName);
            $startDate = mysqli_real_escape_string($conni, $startDate);
            $endDate = mysqli_real_escape_string($conni, $endDate);
        } else {
            $eventName = addslashes($eventName);
            $startDate = addslashes($startDate);
            $endDate = addslashes($endDate);
        }
        
        $sql = "SELECT * FROM SchoolEvents 
                WHERE EventName = '$eventName' 
                AND StartDate = '$startDate' 
                AND EndDate = '$endDate' 
                LIMIT 1";
        
        return $this->sqlf->getRow($sql);
    }

/**
     * Get the event cost options for the given event row
     */
    public function getEventCostOptions($eventRow)
    {
        $sql = "SELECT DISTINCT CostPerPerson, MemberDiscount, OptionName, OptionDesc, AddCost, DisplayOrder, StartDate "
            . "FROM SchoolEvents "
            . "LEFT JOIN (SchoolCostPackages JOIN SchoolCostPackageOptions ON (SchoolCostPackages.`CostPackageID`=SchoolCostPackageOptions.`PackageID`)) "
            . "ON (SchoolEvents.`CostPackage`=SchoolCostPackages.`CostPackageID`) "
            . "WHERE ";

if (isset($eventRow["EventID"])) {
            $sql .= "SchoolEvents.EventID='" . $eventRow["EventID"] . "' ";
        } else {
            $sql .= "SchoolEvents.SchoolCourseID='" . $eventRow["SchoolCourseID"] . "' AND SchoolEvents.StartDate>='" . date("Y-m-d") . "' ";
        }

$sql .= "ORDER BY SchoolEvents.StartDate ASC, SchoolCostPackageOptions.DisplayOrder";

echo '';

$eventOptions = $this->sqlf->getAll($sql);
        $eventOptionsOut = [];

foreach ($eventOptions as $eventOption) {
            // Round down to the nearest R 10
            // $eventOption['CostPerPerson'] = floor($eventOption['CostPerPerson'] / 10) * 10;
            
            if (!isset($eventOptionsOut[$eventOption['CostPerPerson']])) {
                $eventOptionsOut[$eventOption['CostPerPerson']] = $eventOption;
            }
        }

return $eventOptionsOut;
    }

/**
     * Run SELECT and return associative rows via mysqli (avoids ADOdb edge cases).
     *
     * @param string $sql
     * @return array
     */
    private function fetchAssocRows($sql)
    {
        $rows = array();
        $mysqli = null;
        $shouldClose = false;
        global $conni;

if (isset($conni) && ($conni instanceof mysqli)) {
            $mysqli = $conni;
        } elseif (defined('EW_CONN_HOST') && defined('EW_CONN_USER') && defined('EW_CONN_PASS') && defined('EW_CONN_DB')) {
            $mysqli = @mysqli_connect(EW_CONN_HOST, EW_CONN_USER, EW_CONN_PASS, EW_CONN_DB);
            $shouldClose = ($mysqli instanceof mysqli);
        }

if (!($mysqli instanceof mysqli)) {
            $fallback = $this->sqlf->getAll($sql);
            return is_array($fallback) ? $fallback : array();
        }

$result = mysqli_query($mysqli, $sql);
        if ($result instanceof mysqli_result) {
            while ($row = mysqli_fetch_assoc($result)) {
                $rows[] = $row;
            }
            mysqli_free_result($result);
        }

if ($shouldClose) {
            mysqli_close($mysqli);
        }

return $rows;
    }

/**
     * Public listings hide only Cancelled = 1. Active (0) and Postponed (2) remain visible.
     *
     * @param mixed $cancelled
     * @return bool
     */
    public static function isCancelledForPublicHide($cancelled)
    {
        return intval($cancelled) === 1;
    }

/**
     * SQL fragment: exclude cancelled events from public listings (keep postponed).
     *
     * @param string $alias Table alias
     * @return string
     */
    public static function sqlExcludeCancelledForPublic($alias = 'SchoolEvents')
    {
        $alias = preg_replace('/[^A-Za-z0-9_]/', '', (string) $alias);
        if ($alias === '') {
            $alias = 'SchoolEvents';
        }

return '(' . $alias . '.Cancelled IS NULL OR ' . $alias . '.Cancelled <> 1)';
 }

/**
 * Upcoming events for the public course page (event_allvenues.php).
 *
 * @param int|null $courseId
 * @param string $eventName
 * @param string|null $accredNum
 * @param string|null $cpd
 * @param array $options
 * @return array{events: array, debug: array}
 */
 public function getUpcomingEventsForCoursePage($courseId = null, $eventName = '', $accredNum = null, $cpd = null, array $options = array())
 {
 $today = date('Y-m-d');
 $windowMonths = isset($options['window_months']) ? intval($options['window_months']) : 24;
 if ($windowMonths <= 0) {
 $windowMonths = 24;
 }
 $windowEnd = date('Y-m-01', strtotime('+' . $windowMonths . ' months', strtotime(date('Y-m') . '-01')));

$debug = array(
            'course_id' => $courseId !== null && $courseId !== '' ? intval($courseId) : null,
            'event_name' => (string) $eventName,
            'accred_num' => $accredNum,
            'cpd' => $cpd,
            'today' => $today,
            'window_end' => $windowEnd,
            'sql' => '',
            'linked_events_sql' => '',
            'candidate_count' => 0,
            'included_event_ids' => array(),
            'skipped' => array(),
            'past_event_summary' => null,
            'cancelled_future_events' => array(),
        );

$courseId = ($courseId !== null && $courseId !== '') ? intval($courseId) : 0;

$selectParts = array(
            'SchoolEvents.EventID',
            'SchoolEvents.Venue',
            'SchoolEvents.City',
            'SchoolEvents.Cancelled',
            'SchoolEvents.AccredNum',
            'CourseVenues.CourseVenueCity',
            'CourseVenues.CourseVenueArea',
            'SchoolEvents.StartDate',
            'SchoolEvents.EndDate',
            'SchoolEvents.CPD',
            'SchoolEvents.AccredNum2',
            'SchoolEvents.Level',
            'SchoolEvents.SACPCMPNum',
            'SchoolEvents.WebCategory',
            'SchoolEvents.SchoolCourseID',
            'SchoolEvents.EventName',
        );

static $schoolEventsColumns = null;
        if ($schoolEventsColumns === null) {
            $schoolEventsColumns = array();
            foreach ($this->fetchAssocRows('SHOW COLUMNS FROM SchoolEvents') as $colRow) {
                if (isset($colRow['Field'])) {
                    $schoolEventsColumns[strtolower((string) $colRow['Field'])] = true;
                }
            }
        }

foreach (array('SACPCMPCategory', 'SACPCMP_CPDPoints', 'SACNASP_ValidationNumber', 'SACNASP_CPD_Points') as $optionalColumn) {
            if (isset($schoolEventsColumns[strtolower($optionalColumn)])) {
                $selectParts[] = 'SchoolEvents.' . $optionalColumn;
            }
        }

$selectParts[] = "(SELECT COUNT(*) FROM EventSchedules es0 WHERE es0.EventID = SchoolEvents.EventID) AS ScheduleRowCount";
        $selectParts[] = "(SELECT MIN(es1.ScheduleDate) FROM EventSchedules es1 WHERE es1.EventID = SchoolEvents.EventID AND es1.ScheduleDate >= '" . $today . "') AS NextScheduleDate";
        $selectParts[] = "(SELECT MAX(es2.ScheduleDate) FROM EventSchedules es2 WHERE es2.EventID = SchoolEvents.EventID) AS LastScheduleDate";

$sql = "SELECT " . implode(', ', $selectParts) . " "
 . "FROM SchoolEvents "
 . "LEFT JOIN CourseVenues ON SchoolEvents.Venue = CourseVenues.CourseVenueName "
 . "WHERE " . self::sqlExcludeCancelledForPublic('SchoolEvents') . " "
 . "AND (SchoolEvents.WebCategory IS NULL OR SchoolEvents.WebCategory <> 11) AND ";

if ($courseId > 0) {
            $sql .= "SchoolEvents.SchoolCourseID = " . $courseId;
        } else {
            $sql .= " SchoolEvents.EventName = '" . addslashes((string) $eventName) . "' AND (SchoolEvents.AccredNum = '" . addslashes((string) $accredNum) . "'";
            if ($accredNum === null || $accredNum === '') {
                $sql .= " OR SchoolEvents.AccredNum IS NULL";
            }
            $sql .= ") AND (SchoolEvents.CPD = '" . addslashes((string) $cpd) . "'";
            if ($cpd === null || $cpd === '') {
                $sql .= " OR SchoolEvents.CPD IS NULL";
            }
            $sql .= ")";
        }

$sql .= " ORDER BY SchoolEvents.StartDate, SchoolEvents.EventID";
        $debug['sql'] = $sql;

// Unfiltered diagnostic query: all SchoolEvents linked to this course/name (incl. cancelled / past).
        if ($courseId > 0) {
            $debug['linked_events_sql'] = "SELECT EventID, EventName, StartDate, EndDate, Cancelled, WebCategory, Venue, AccredNum "
                . "FROM SchoolEvents WHERE SchoolCourseID = " . $courseId . " ORDER BY StartDate DESC, EventID DESC";
        } else {
            $debug['linked_events_sql'] = "SELECT EventID, EventName, StartDate, EndDate, Cancelled, WebCategory, Venue, AccredNum "
                . "FROM SchoolEvents WHERE EventName = '" . addslashes((string) $eventName) . "' "
                . "ORDER BY StartDate DESC, EventID DESC";
        }

$linkedRows = $this->fetchAssocRows($debug['linked_events_sql']);
        foreach ($linkedRows as $linkedRow) {
            $linkedCancelled = self::isCancelledForPublicHide(isset($linkedRow['Cancelled']) ? $linkedRow['Cancelled'] : 0);
            $linkedEnd = !empty($linkedRow['EndDate']) ? (string) $linkedRow['EndDate'] : '';
            $linkedStart = !empty($linkedRow['StartDate']) ? (string) $linkedRow['StartDate'] : '';
            if ($linkedCancelled && (($linkedEnd !== '' && $linkedEnd >= $today) || ($linkedStart !== '' && $linkedStart >= $today))) {
                $debug['cancelled_future_events'][] = array(
                    'EventID' => intval($linkedRow['EventID']),
                    'StartDate' => $linkedStart,
                    'EndDate' => $linkedEnd,
                    'Venue' => isset($linkedRow['Venue']) ? $linkedRow['Venue'] : '',
                );
            }
        }

$rows = $this->fetchAssocRows($sql);
        $debug['candidate_count'] = count($rows);

foreach ($rows as &$rowNorm) {
            foreach (array('SACPCMPCategory', 'SACPCMP_CPDPoints', 'SACNASP_ValidationNumber', 'SACNASP_CPD_Points') as $optionalField) {
                if (!array_key_exists($optionalField, $rowNorm)) {
                    $rowNorm[$optionalField] = null;
                }
            }
        }
        unset($rowNorm);

$pastCount = 0;
        $latestPastEnd = null;
        $latestPastStart = null;
        $included = array();

foreach ($rows as $row) {
            $eventId = intval($row['EventID']);
            $endDate = !empty($row['EndDate']) ? (string) $row['EndDate'] : '';
            $startDate = !empty($row['StartDate']) ? (string) $row['StartDate'] : '';
            $nextSchedule = !empty($row['NextScheduleDate']) ? (string) $row['NextScheduleDate'] : '';
            $scheduleRowCount = intval($row['ScheduleRowCount']);

$stillRunning = ($endDate !== '' && $endDate >= $today);
 $hasFutureSchedule = ($nextSchedule !== '' && $nextSchedule < $windowEnd);

if (!$stillRunning && !$hasFutureSchedule) {
 $reason = 'no_upcoming_end_or_schedule';
 if ($scheduleRowCount <= 0) {
 $reason = 'no_event_schedules_rows';
 } elseif ($endDate !== '' && $endDate < $today) {
 $reason = 'event_ended';
 $pastCount++;
 if ($latestPastEnd === null || $endDate > $latestPastEnd) {
 $latestPastEnd = $endDate;
 $latestPastStart = $startDate;
 }
 }
 $debug['skipped'][] = array(
 'EventID' => $eventId,
 'StartDate' => $startDate,
 'EndDate' => $endDate,
 'ScheduleRowCount' => $scheduleRowCount,
 'NextScheduleDate' => $nextSchedule,
 'reason' => $reason,
 );
 continue;
 }

// Load remaining schedule days in the public window
 $days = $this->getFutureScheduleDaysForEvent($eventId, $today, $windowEnd);
 if (count($days) <= 0) {
 $debug['skipped'][] = array(
 'EventID' => $eventId,
 'StartDate' => $startDate,
 'EndDate' => $endDate,
 'ScheduleRowCount' => $scheduleRowCount,
 'NextScheduleDate' => $nextSchedule,
 'reason' => 'no_future_schedule_days_in_window',
 );
 continue;
 }

$row['_schedule_days'] = $days;
            $included[] = $row;
            $debug['included_event_ids'][] = $eventId;
        }

if ($pastCount > 0) {
            $debug['past_event_summary'] = array(
                'count' => $pastCount,
                'latest_start' => $latestPastStart,
                'latest_end' => $latestPastEnd,
            );
        }

return array(
            'events' => $included,
            'debug' => $debug,
        );
    }

/**
 * Future EventSchedules day rows for an event within [today, windowEnd).
 *
 * @param int $eventId
 * @param string $today Y-m-d
 * @param string $windowEnd Y-m-d exclusive upper bound
 * @return array
 */
 public function getFutureScheduleDaysForEvent($eventId, $today = null, $windowEnd = null)
 {
 $eventId = intval($eventId);
 if ($eventId <= 0) {
 return array();
 }

if ($today === null || $today === '') {
            $today = date('Y-m-d');
        }
        if ($windowEnd === null || $windowEnd === '') {
            $windowEnd = date('Y-m-01', strtotime('+24 months', strtotime(date('Y-m') . '-01')));
        }

$sql = "SELECT * FROM EventSchedules WHERE EventID = " . $eventId
 . " AND ScheduleDate >= '" . addslashes($today) . "'"
 . " AND ScheduleDate < '" . addslashes($windowEnd) . "'"
 . " ORDER BY ScheduleDate";

return $this->fetchAssocRows($sql);
    }

/**
 * Build calendar rows grouped by course and month.
 * Course-level accred_num is an active SchoolEvents.AccredNum (MM/YYYY expiry), not SchoolCourses.
 *
 * @param int $numMonths
 * @param int|null $webCategoryId
 * @return array
 */
 public function getCalendarCoursesByMonth($numMonths, $webCategoryId = null)
 {
 $numMonths = intval($numMonths);
 if ($numMonths <= 0) {
 $numMonths = 12;
 }

$windowStart = date('Y-m-01');
        $windowEnd = date('Y-m-01', strtotime('+' . $numMonths . ' months', strtotime($windowStart)));

$sql = "SELECT 
            SchoolEvents.EventID,
            SchoolEvents.SchoolCourseID,
            IF(ISNULL(SchoolCourses.CourseName), SchoolEvents.EventName, SchoolCourses.CourseName) AS EventName,
            SchoolEvents.City,
            SchoolEvents.CostPerPerson,
            SchoolEvents.AccredNum AS EventAccredNum,
            IF(ISNULL(SchoolCourses.CPD), SchoolEvents.CPD, SchoolCourses.CPD) AS CPD,
            SchoolEvents.SACPCMPNum,
            SchoolEvents.SACPCMPCategory,
            SchoolEvents.SACPCMP_CPDPoints,
            SchoolEvents.Cancelled,
            SchoolEvents.Venue,
            CourseVenues.CourseVenueCity,
            SchoolWebCategories.WebCategoryName,
            MIN(EventSchedules.ScheduleDate) AS StartDate,
            MAX(EventSchedules.ScheduleDate) AS EndDate,
            SchoolEvents.SACNASP_ValidationNumber,
            SchoolEvents.SACNASP_CPD_Points
        FROM ((SchoolEvents LEFT JOIN SchoolCourses USING (SchoolCourseID)
            LEFT OUTER JOIN SchoolWebCategories ON (SchoolEvents.WebCategory = SchoolWebCategories.WebCategoryID))
            LEFT JOIN CourseVenues ON SchoolEvents.Venue = CourseVenues.CourseVenueName)
            LEFT JOIN EventSchedules ON SchoolEvents.EventID = EventSchedules.EventID
        WHERE " . self::sqlExcludeCancelledForPublic('SchoolEvents') . "
            AND (SchoolEvents.WebCategory != 11 OR SchoolEvents.WebCategory IS NULL)";

if (!empty($webCategoryId)) {
            $sql .= " AND SchoolWebCategories.WebCategoryID = " . intval($webCategoryId);
        }

$sql .= "
 GROUP BY
 SchoolEvents.EventID,
 SchoolEvents.SchoolCourseID,
 EventName,
 SchoolEvents.City,
 SchoolEvents.CostPerPerson,
 SchoolEvents.AccredNum,
 CPD,
 SchoolEvents.SACPCMPNum,
 SchoolEvents.SACPCMPCategory,
 SchoolEvents.SACPCMP_CPDPoints,
 SchoolEvents.Cancelled,
 SchoolEvents.Venue,
 CourseVenues.CourseVenueCity,
 SchoolWebCategories.WebCategoryName,
 SchoolEvents.SACNASP_ValidationNumber,
 SchoolEvents.SACNASP_CPD_Points
 HAVING StartDate >= '" . $windowStart . "'
 AND StartDate < '" . $windowEnd . "'
 ORDER BY EventName, StartDate, EventID";

$eventRows = $this->sqlf->getAll($sql);
        if (!is_array($eventRows) || count($eventRows) === 0) {
            return [];
        }

$monthTemplate = [];
 for ($i = 0; $i < $numMonths; $i++) {
 $monthDate = strtotime('+' . $i . ' months', strtotime($windowStart));
 $monthKey = date('Y-m', $monthDate);
 $monthTemplate[$monthKey] = [
 'display_name' => date('M Y', $monthDate),
 'events' => []
 ];
 }

$courses = [];

foreach ($eventRows as $row) {
            if (empty($row['StartDate'])) {
                continue;
            }

$courseKey = '';
            if (!empty($row['SchoolCourseID'])) {
                $courseKey = 'course-' . $row['SchoolCourseID'];
            } else {
                $courseKey = 'name-' . md5($row['EventName']);
            }

if (!isset($courses[$courseKey])) {
                $courses[$courseKey] = [
                    'name' => $row['EventName'],
                    'accred_num' => '',
                    'accred_candidates' => [],
                    'cpd' => $row['CPD'],
                    'sacpcmp_num' => $row['SACPCMPNum'],
                    'sacpcmp_cpd_points' => $row['SACPCMP_CPDPoints'],
                    'sacnasp_validation_number' => $row['SACNASP_ValidationNumber'],
                    'sacnasp_cpd_points' => $row['SACNASP_CPD_Points'],
                    'web_category_name' => $row['WebCategoryName'],
                    'course_link_event_id' => $row['EventID'],
                    'months' => $monthTemplate
                ];
            }

if (!empty($row['EventAccredNum'])) {
                $courses[$courseKey]['accred_candidates'][] = $row['EventAccredNum'];
            }

$monthKey = date('Y-m', strtotime($row['StartDate']));
            if (!isset($courses[$courseKey]['months'][$monthKey])) {
                continue;
            }

$city = $row['City'];
            if (empty($city)) {
                $city = $row['CourseVenueCity'];
            }
            if (empty($city)) {
                $city = $row['Venue'];
            }

$eventCostOptions = $this->getEventCostOptions($row);

$courses[$courseKey]['months'][$monthKey]['events'][] = [
                'event_id' => $row['EventID'],
                'name' => $row['EventName'],
                'start_date' => $row['StartDate'],
                'end_date' => $row['EndDate'],
                'city' => $city,
                'cancelled' => self::isCancelledForPublicHide($row['Cancelled']),
                'cost_options' => $eventCostOptions
            ];
        }

foreach ($courses as &$course) {
            $course['accred_num'] = $this->selectActiveAccredNum($course['accred_candidates']);
            unset($course['accred_candidates']);
        }
        unset($course);

return array_values($courses);
    }

/**
     * Parse MM/YYYY expiry embedded at the end of a validation number.
     *
     * @param string $accredNum
     * @return array|null Keys: month, year
     */
    public function parseAccredNumExpiry($accredNum)
    {
        $accredNum = trim((string) $accredNum);
        if ($accredNum === '' || strcasecmp($accredNum, 'TBA') === 0) {
            return null;
        }

if (!preg_match('/(\d{2})\/(\d{4})\s*$/', $accredNum, $matches)) {
            return null;
        }

$month = intval($matches[1]);
 $year = intval($matches[2]);
 if ($month < 1 || $month > 12 || $year < 1990) {
 return null;
 }

return [
            'month' => $month,
            'year' => $year,
        ];
    }

/**
     * Whether a validation number is currently active (expiry month/year >= as-of).
     *
     * @param string $accredNum
     * @param int|string|null $asOf Unix timestamp or date string; defaults to now
     * @return bool
     */
    public function isAccredNumCurrentlyActive($accredNum, $asOf = null)
    {
        $expiry = $this->parseAccredNumExpiry($accredNum);
        if ($expiry === null) {
            return false;
        }

if ($asOf === null) {
            $asOfTs = time();
        } elseif (is_int($asOf)) {
            $asOfTs = $asOf;
        } else {
            $asOfTs = strtotime((string) $asOf);
            if ($asOfTs === false) {
                $asOfTs = time();
            }
        }

$currentYear = intval(date('Y', $asOfTs));
        $currentMonth = intval(date('m', $asOfTs));

return ($expiry['year'] > $currentYear)
            || ($expiry['year'] === $currentYear && $expiry['month'] >= $currentMonth);
    }

/**
     * Pick one currently active AccredNum from candidates, preferring latest expiry.
     *
     * @param array $candidates
     * @param int|string|null $asOf
     * @return string
     */
    public function selectActiveAccredNum(array $candidates, $asOf = null)
    {
        $best = '';
        $bestExpiry = null;

foreach ($candidates as $candidate) {
            $candidate = trim((string) $candidate);
            if ($candidate === '' || !$this->isAccredNumCurrentlyActive($candidate, $asOf)) {
                continue;
            }

$expiry = $this->parseAccredNumExpiry($candidate);
            if ($expiry === null) {
                continue;
            }

if ($bestExpiry === null
                || $expiry['year'] > $bestExpiry['year']
                || ($expiry['year'] === $bestExpiry['year'] && $expiry['month'] > $bestExpiry['month'])
            ) {
                $best = $candidate;
                $bestExpiry = $expiry;
            }
        }

return $best;
    }

/**
     * Get the dates of an event
     *
     * @param array $event
     * @return string
     */
    public static function getEventDatesString($event, $formatted = false)
    {

$startDate = new DateTime($event['StartDate']);
        $endDate = new DateTime($event['EndDate']);

if ($formatted) {
            $startDate = $startDate->format('d F Y');
            $endDate = $endDate->format('d F Y');
        } else {
            $startDate = $event['StartDate'];
            $endDate = $event['EndDate'];
        }

$return = $startDate;

if ($event['StartDate'] == $event['EndDate']) {
            $return .= ' ' . $event['StartTime'] . ' - ' . $event['EndTime'];
        } else {
            //            $return .= ' ' . $event['StartTime'] . ' - ' . $event['EndDate'] . ' ' . $event['EndTime'];
            $return .= ' - ' . $endDate;
        }

return $return;
    }

public function eventWebCategoryReport()
    {
        $events = $this->getAllWithEmptyWebCategory();

if (!is_array($events) || count($events) <= 0) {
 return 'No events found with empty web category';
 }

$msgHtml = 'The following events have no web category: \n'
 . '<ul>';
 foreach ($events as $event) {
 $msgHtml .= '<li>' . $event['EventID'] . ' - ' . $event['EventName'] . ' - ' . $event['WebCategory'] . ' - '
 . $this->getEventDatesString($event) . "</li>\n";
 }

$msgHtml .= "</ul>\n";

$msgHtml .= " \n";

$msgHtml .= "Total events: " . count($events) . "\n";

maill(
            'cesa-sce@igotafrica.com,blessings@cesa.co.za',
            'School Events Empty Web Category Report',
            $msgHtml,
            null,
            [
                'isHTML' => true,
                'from-address' => 'sce@cesa.co.za',
                'from-name' => 'CESA SCE',
            ]
        );

return 'Report sent';
    }

/**
 * Create a new event
 * @param array $eventData
 * @return int|false EventID on success, false on failure
 */
 public function create($eventData) {
 global $conni;
 
 // Set default WebCategory to 14 if not provided
 if (!isset($eventData['WebCategory']) || $eventData['WebCategory'] === null || $eventData['WebCategory'] === '') {
 $eventData['WebCategory'] = '14';
 }
 
 // Set default ECSACategory to 1A if not provided
 if (!isset($eventData['ECSACategory']) || $eventData['ECSACategory'] === null || $eventData['ECSACategory'] === '') {
 $eventData['ECSACategory'] = '1A';
 }
 
 // Set default AvailableSpaces to 30 if not provided
 if (!isset($eventData['AvailableSpaces']) || $eventData['AvailableSpaces'] === null || $eventData['AvailableSpaces'] === '' || $eventData['AvailableSpaces'] === '0') {
 $eventData['AvailableSpaces'] = 30;
 }
 
 // Handle ProviderID -> TrainerID conversion
 // If ProviderID is set, look up TrainingProvider and set TrainerID to that ProviderID, then set ProviderID to NULL
 $providerIDShouldBeNull = false;
 if (isset($eventData['ProviderID']) && $eventData['ProviderID'] !== null && $eventData['ProviderID'] !== '') {
 // Verify TrainingProvider exists
 $providerID = intval($eventData['ProviderID']);
 // intval() makes this safe from SQL injection
 $providerCheck = $this->sqlf->getRow("SELECT ProviderID FROM TrainingProviders WHERE ProviderID = $providerID AND (Archived = 0 OR Archived IS NULL) LIMIT 1");
 
 if ($providerCheck) {
 // Set TrainerID to the ProviderID from TrainingProvider table
 $eventData['TrainerID'] = $providerID;
 }
 // Always set ProviderID to NULL
 $eventData['ProviderID'] = null;
 $providerIDShouldBeNull = true;
 }
 
 // If TrainerID is 45, set CourseType to SAICE-PDP
 if (isset($eventData['TrainerID']) && intval($eventData['TrainerID']) === 45) {
 $eventData['CourseType'] = 'SAICE-PDP';
 }
 
 $fields = [];
 $values = [];
 
 $allowedFields = [
 'SchoolCourseID', 'Venue', 'ProviderID', 'PresenterID', 'TrainerID',
 'StartDate', 'EndDate', 'StartTime', 'EndTime',
 'CostPerPerson', 'MemberDiscount', 'EventName', 'EventDescription',
 'CPD', 'AccredNum', 'SACPCMPNum', 'SACPCMP_CPDPoints',
 'Level', 'CourseType', 'AvailableSpaces', 'WebCategory', 'ECSACategory'
 ];
 
 foreach ($allowedFields as $field) {
 // Special handling for ProviderID when it should be explicitly set to NULL
 if ($field === 'ProviderID' && $providerIDShouldBeNull) {
 $fields[] = "`$field`";
 $values[] = 'NULL';
 } elseif (isset($eventData[$field]) && $eventData[$field] !== null && $eventData[$field] !== '') {
 $fields[] = "`$field`";
 // Handle null values properly
 if ($eventData[$field] === null) {
 $values[] = 'NULL';
 } else {
 // Check if $conni is available and is a mysqli connection
 if (isset($conni) && ($conni instanceof mysqli)) {
 $values[] = "'" . mysqli_real_escape_string($conni, $eventData[$field]) . "'";
 } else {
 // Fallback to addslashes if mysqli connection not available
 $values[] = "'" . addslashes($eventData[$field]) . "'";
 }
 }
 }
 }
 
 if (empty($fields)) {
 $this->lastError = 'No valid fields to insert';
 return false;
 }
 
 $sql = "INSERT INTO SchoolEvents (" . implode(', ', $fields) . ") VALUES (" . implode(', ', $values) . ")";
 
 // Store SQL for debugging
 $this->lastSQL = $sql;
 
 if ($this->sqlf->execute($sql)) {
 $eventID = null;
 if (isset($conni) && ($conni instanceof mysqli)) {
 $eventID = mysqli_insert_id($conni);
 } else {
 // Try to get insert ID from sqlf if available
 if (method_exists($this->sqlf, 'Insert_ID')) {
 $eventID = $this->sqlf->Insert_ID();
 }
 }
 
 // Create EventSchedules entries if event was created successfully and we have dates
 if ($eventID && $eventID > 0) {
 // Get dates from eventData or fetch from database
 $startDate = null;
 $endDate = null;
 
 if (isset($eventData['StartDate']) && $eventData['StartDate'] !== null && $eventData['StartDate'] !== '') {
 $startDate = $eventData['StartDate'];
 } else {
 // Try to fetch from database
 $eventRow = $this->findById($eventID);
 if ($eventRow) {
 // Check both possible column name cases
 if (isset($eventRow['StartDate']) && $eventRow['StartDate'] !== null && $eventRow['StartDate'] !== '') {
 $startDate = $eventRow['StartDate'];
 } elseif (isset($eventRow['startdate']) && $eventRow['startdate'] !== null && $eventRow['startdate'] !== '') {
 $startDate = $eventRow['startdate'];
 }
 }
 }
 
 if (isset($eventData['EndDate']) && $eventData['EndDate'] !== null && $eventData['EndDate'] !== '') {
 $endDate = $eventData['EndDate'];
 } else {
 // Try to fetch from database
 if (!isset($eventRow)) {
 $eventRow = $this->findById($eventID);
 }
 if ($eventRow) {
 // Check both possible column name cases
 if (isset($eventRow['EndDate']) && $eventRow['EndDate'] !== null && $eventRow['EndDate'] !== '') {
 $endDate = $eventRow['EndDate'];
 } elseif (isset($eventRow['enddate']) && $eventRow['enddate'] !== null && $eventRow['enddate'] !== '') {
 $endDate = $eventRow['enddate'];
 }
 }
 }
 
 // Only create schedules if we have both dates
 if ($startDate && $endDate) {
 // Include EventSchedules class
 if (!class_exists('EventSchedules')) {
 include_once(__DIR__ . '/EventSchedules.php');
 }
 
 $eventSchedules = new EventSchedules($this->sqlf);
 
 // Check if schedules already exist
 if (!$eventSchedules->schedulesExistForEvent($eventID)) {
 // Get StartTime and EndTime from event data, or use defaults
 $startTime = isset($eventData['StartTime']) && $eventData['StartTime'] !== null && $eventData['StartTime'] !== '' 
 ? $eventData['StartTime'] 
 : '08:30:00';
 $endTime = isset($eventData['EndTime']) && $eventData['EndTime'] !== null && $eventData['EndTime'] !== '' 
 ? $eventData['EndTime'] 
 : '16:30:00';
 
 // echo 'Creating event schedules: ' . $eventID . ' - ' . $startDate . ' ';

// Create schedules for each day of the event
 $eventSchedules->createSchedulesForDateRange(
 $eventID, 
 $startDate, 
 $endDate, 
 $startTime, 
 $endTime, 
 false // Not weekly, create for each day
 );
 } else {
 // echo 'Event schedules exist: ' . $eventID . ' - ' . $startDate . ' ';
 }
 } else {
 // echo 'Start or end date missing: ' . $eventID . ' - ' . $startDate . ' ';
 }
 }
 
 return $eventID ? $eventID : true; // Return eventID or true if we can't get ID but query succeeded
 }
 
 // Store error message
 $this->lastError = 'SQL execution failed';
 if (isset($conni) && ($conni instanceof mysqli)) {
 $this->lastError .= ': ' . mysqli_error($conni);
 } elseif (method_exists($this->sqlf, 'ErrorMsg')) {
 $this->lastError .= ': ' . $this->sqlf->ErrorMsg();
 }
 
 return false;
 }
 
 public function getLastError() {
 return isset($this->lastError) ? $this->lastError : '';
 }
 
 public function getLastSQL() {
 return isset($this->lastSQL) ? $this->lastSQL : '';
 }

/**
 * Update an existing event
 * @param int $eventID
 * @param array $eventData
 * @return bool True on success, false on failure
 */
 public function update($eventID, $eventData) {
 global $conni;
 
 $sets = [];
 
 $allowedFields = [
 'SchoolCourseID', 'Venue', 'ProviderID', 'PresenterID', 'TrainerID',
 'StartDate', 'EndDate', 'StartTime', 'EndTime',
 'CostPerPerson', 'MemberDiscount', 'EventName', 'EventDescription',
 'CPD', 'AccredNum', 'SACPCMPNum', 'SACPCMP_CPDPoints',
 'Level', 'CourseType', 'AvailableSpaces', 'WebCategory', 'ECSACategory'
 ];
 
 foreach ($allowedFields as $field) {
 if (isset($eventData[$field])) {
 if ($eventData[$field] === null || $eventData[$field] === '') {
 $sets[] = "`$field` = NULL";
 } else {
 // Check if $conni is available and is a mysqli connection
 if (isset($conni) && ($conni instanceof mysqli)) {
 $sets[] = "`$field` = '" . mysqli_real_escape_string($conni, $eventData[$field]) . "'";
 } else {
 // Fallback to addslashes if mysqli connection not available
 $sets[] = "`$field` = '" . addslashes($eventData[$field]) . "'";
 }
 }
 }
 }
 
 if (empty($sets)) {
 $this->lastError = 'No valid fields to update';
 return false;
 }
 
 // Escape eventID
 if (isset($conni) && ($conni instanceof mysqli)) {
 $eventID = mysqli_real_escape_string($conni, $eventID);
 } else {
 $eventID = addslashes($eventID);
 }
 
 $sql = "UPDATE SchoolEvents SET " . implode(', ', $sets) . " WHERE EventID = '$eventID'";
 
 // Store SQL for debugging
 $this->lastSQL = $sql;
 
 if ($this->sqlf->execute($sql)) {
 // echo 'Event updated: ' . $eventID . ' ';
 
 // Check and create EventSchedules entries if dates are being updated
 if ($eventID && $eventID > 0) {
 // Get dates from eventData or fetch from database
 $startDate = null;
 $endDate = null;
 
 if (isset($eventData['StartDate']) && $eventData['StartDate'] !== null && $eventData['StartDate'] !== '') {
 $startDate = $eventData['StartDate'];
 // echo 'StartDate from eventData: ' . $startDate . ' ';
 } else {
 // Try to fetch from database
 $eventRow = $this->findById($eventID);
 if ($eventRow) {
 // Check both possible column name cases
 if (isset($eventRow['StartDate']) && $eventRow['StartDate'] !== null && $eventRow['StartDate'] !== '') {
 $startDate = $eventRow['StartDate'];
 // echo 'StartDate from database: ' . $startDate . ' ';
 } elseif (isset($eventRow['startdate']) && $eventRow['startdate'] !== null && $eventRow['startdate'] !== '') {
 $startDate = $eventRow['startdate'];
 // echo 'StartDate from database (lowercase): ' . $startDate . ' ';
 }
 }
 }
 
 if (isset($eventData['EndDate']) && $eventData['EndDate'] !== null && $eventData['EndDate'] !== '') {
 $endDate = $eventData['EndDate'];
 // echo 'EndDate from eventData: ' . $endDate . ' ';
 } else {
 // Try to fetch from database
 if (!isset($eventRow)) {
 $eventRow = $this->findById($eventID);
 }
 if ($eventRow) {
 // Check both possible column name cases
 if (isset($eventRow['EndDate']) && $eventRow['EndDate'] !== null && $eventRow['EndDate'] !== '') {
 $endDate = $eventRow['EndDate'];
 // echo 'EndDate from database: ' . $endDate . ' ';
 } elseif (isset($eventRow['enddate']) && $eventRow['enddate'] !== null && $eventRow['enddate'] !== '') {
 $endDate = $eventRow['enddate'];
 // echo 'EndDate from database (lowercase): ' . $endDate . ' ';
 }
 }
 }
 
 // Only create schedules if we have both dates
 if ($startDate && $endDate) {
 // Include EventSchedules class
 if (!class_exists('EventSchedules')) {
 include_once(__DIR__ . '/EventSchedules.php');
 }
 
 $eventSchedules = new EventSchedules($this->sqlf);
 
 // Check if schedules already exist
 if (!$eventSchedules->schedulesExistForEvent($eventID)) {
 // Get StartTime and EndTime from event data, or use defaults
 $startTime = isset($eventData['StartTime']) && $eventData['StartTime'] !== null && $eventData['StartTime'] !== '' 
 ? $eventData['StartTime'] 
 : '08:30:00';
 $endTime = isset($eventData['EndTime']) && $eventData['EndTime'] !== null && $eventData['EndTime'] !== '' 
 ? $eventData['EndTime'] 
 : '16:30:00';
 
 // echo 'Creating event schedules (update): ' . $eventID . ' - ' . $startDate . ' to ' . $endDate . ' ';
 
 // Create schedules for each day of the event
 $eventSchedules->createSchedulesForDateRange(
 $eventID, 
 $startDate, 
 $endDate, 
 $startTime, 
 $endTime, 
 false // Not weekly, create for each day
 );
 } else {
 // echo 'Event schedules exist (update): ' . $eventID . ' - ' . $startDate . ' ';
 }
 } else {
 // echo 'Start or end date missing (update): ' . $eventID . ' - StartDate: ' . ($startDate ? $startDate : 'null') . ', EndDate: ' . ($endDate ? $endDate : 'null') . ' ';
 }
 }
 
 return true;
 }
 
 // Store error message
 $this->lastError = 'SQL execution failed';
 if (isset($conni) && ($conni instanceof mysqli)) {
 $this->lastError .= ': ' . mysqli_error($conni);
 } elseif (method_exists($this->sqlf, 'ErrorMsg')) {
 $this->lastError .= ': ' . $this->sqlf->ErrorMsg();
 }
 
 return false;
 }
}

function SchoolEventsPageLoad($objThis)
{

if (!isset($_REQUEST['z_Archived'])) {
        $_REQUEST['z_Archived'] = '=';
    }
    if (!isset($_REQUEST['x_Archived'])) {
        $_REQUEST['x_Archived'] = '0';
    }
    if (!isset($_GET['z_Archived'])) {
        $_GET['z_Archived'] = '=';
    }
    if (!isset($_GET['x_Archived'])) {
        $_GET['x_Archived'] = '0';
    }
    
    // Add Import Events button at top of list (protected from PHPMaker regeneration)
    schoolEventsListMenu();
}