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
/**
 * Parse weekly sessions Excel: DATE, START TIME, LECTURE REFERENCE, DESCRIPTION,
 * PRESENTER, SESSION BACKUP, ASSIGNMENT, SUBMISSION DATE.
 * Date column spans rows (carry over until new date). Submission date is relative (e.g. "date + 1 week").
 */

use PhpOffice\PhpSpreadsheet\IOFactory;

class WeeklySessionsParser {

const COL_DATE = 0;
    const COL_START_TIME = 1;
    const COL_LECTURE_REF = 2;
    const COL_DESCRIPTION = 3;
    const COL_PRESENTER = 4;
    const COL_SESSION_BACKUP = 5;
    const COL_ASSIGNMENT = 6;
    const COL_SUBMISSION_DATE = 7;

/** @var int Year used when parsing DD-Mon dates (e.g. 2025) */
    public $year;

public function __construct($year = null) {
        $this->year = $year ? (int) $year : (int) date('Y');
    }

/**
     * @param string $path Path to .xlsx or .xls file (e.g. uploaded tmp_name)
     * @param string|null $originalName Original filename for extension check (tmp files often have no extension)
     * @return array{rows: array, errors: string[]}
     */
    public function parseFile($path, $originalName = null) {
        $errors = [];
        if (!is_readable($path)) {
            return ['rows' => [], 'errors' => ['File not readable.']];
        }
        $ext = strtolower(pathinfo($originalName !== null ? $originalName : $path, PATHINFO_EXTENSION));
        if ($ext !== '' && !in_array($ext, ['xlsx', 'xls'], true)) {
            return ['rows' => [], 'errors' => ['Only .xlsx or .xls files are allowed.']];
        }

try {
            $spreadsheet = IOFactory::load($path);
            $sheet = $spreadsheet->getActiveSheet();
        } catch (Exception $e) {
            return ['rows' => [], 'errors' => ['Could not read Excel: ' . $e->getMessage()]];
        }

$rows = [];
        $lastDate = null;
        $headerSkipped = false;
        $highestRow = $sheet->getHighestRow();

for ($row = 1; $row <= $highestRow; $row++) {
            $dateVal = $this->getCellValue($sheet, $row, self::COL_DATE);
            $startTimeVal = $this->getCellValue($sheet, $row, self::COL_START_TIME);
            $lectureRef = $this->getCellValue($sheet, $row, self::COL_LECTURE_REF);
            $description = $this->getCellValue($sheet, $row, self::COL_DESCRIPTION);
            $presenter = $this->getCellValue($sheet, $row, self::COL_PRESENTER);
            $sessionBackup = $this->getCellValue($sheet, $row, self::COL_SESSION_BACKUP);
            $assignment = $this->getCellValue($sheet, $row, self::COL_ASSIGNMENT);
            $submissionDateDesc = $this->getCellValue($sheet, $row, self::COL_SUBMISSION_DATE);

// Skip header row (optional: detect "DATE" in first column)
            if (!$headerSkipped && (
                stripos((string) $dateVal, 'date') !== false ||
                stripos((string) $startTimeVal, 'time') !== false
            )) {
                $headerSkipped = true;
                continue;
            }

// Carry over date when empty
            if (trim((string) $dateVal) !== '') {
                $parsed = $this->parseDate($dateVal);
                if ($parsed) {
                    $lastDate = $parsed;
                }
            }

// Skip rows with no date and no meaningful content
            if (!$lastDate && trim((string) $startTimeVal) === '' && trim((string) $description) === '') {
                continue;
            }
            if (!$lastDate) {
                continue;
            }

$sessionDate = $lastDate;
            $startTime = $this->normalizeTime($startTimeVal);
            $submissionDate = $this->parseSubmissionDate($submissionDateDesc, $sessionDate);

$descTrim = trim((string) $description);
            if (strcasecmp($descTrim, 'Break') === 0 || strcasecmp($descTrim, 'End') === 0) {
                continue; // do not import Break or End sessions
            }

$rows[] = [
                'SessionDate'       => $sessionDate,
                'StartTime'         => $startTime,
                'EndTime'           => null, // set below from next session's StartTime
                'LectureReference'  => trim((string) $lectureRef) ?: null,
                'Description'       => trim((string) $description) ?: null,
                'Presenter'        => trim((string) $presenter) ?: null,
                'SessionBackup'     => trim((string) $sessionBackup) ?: null,
                'Assignment'        => trim((string) $assignment) ?: null,
                'SubmissionDateDesc' => trim((string) $submissionDateDesc) ?: null,
                'SubmissionDate'    => $submissionDate,
            ];
        }

// End time = start time of the next session (same date)
        for ($i = 0, $n = count($rows); $i < $n; $i++) {
            if ($i + 1 < $n && $rows[$i]['SessionDate'] === $rows[$i + 1]['SessionDate']) {
                $rows[$i]['EndTime'] = $rows[$i + 1]['StartTime'];
            }
        }

return ['rows' => $rows, 'errors' => $errors];
    }

private function getCellValue($sheet, $row, $colIndex) {
        $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($colIndex + 1);
        $cell = $sheet->getCell($colLetter . $row);
        $v = $cell->getValue();
        if ($v instanceof \PhpOffice\PhpSpreadsheet\RichText\RichText) {
            $v = $v->getPlainText();
        }
        return $v === null ? '' : trim((string) $v);
    }

/**
     * Parse DD-Mon or similar to Y-m-d.
     */
    private function parseDate($value) {
        $value = trim((string) $value);
        if ($value === '') return null;
        // Excel may have serial date number
        if (is_numeric($value)) {
            $d = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);
            return $d ? $d->format('Y-m-d') : null;
        }
        // DD-Mon or D-Mon
        if (preg_match('/^(\d{1,2})[- ]([A-Za-z]{3})$/i', $value, $m)) {
            $day = (int) $m[1];
            $mon = $m[2];
            $ts = strtotime($day . ' ' . $mon . ' ' . $this->year);
            if ($ts !== false) {
                return date('Y-m-d', $ts);
            }
        }
        $ts = strtotime($value . ' ' . $this->year);
        if ($ts !== false) {
            return date('Y-m-d', $ts);
        }
        return null;
    }

private function normalizeTime($value) {
        $value = trim((string) $value);
        if ($value === '') return null;
        if (is_numeric($value)) {
            $dt = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);
            return $dt ? $dt->format('H:i:s') : null;
        }
        if (preg_match('/^(\d{1,2}):(\d{2})(?::(\d{2}))?\s*(am|pm)?$/i', $value, $m)) {
            $h = (int) $m[1];
            $i = (int) $m[2];
            $s = isset($m[3]) ? (int) $m[3] : 0;
            if (isset($m[4]) && strtolower($m[4]) === 'pm' && $h < 12) $h += 12;
            if (isset($m[4]) && strtolower($m[4]) === 'am' && $h === 12) $h = 0;
            return sprintf('%02d:%02d:%02d', $h, $i, $s);
        }
        if (preg_match('/^(\d{1,2}):(\d{2})/', $value, $m)) {
            return sprintf('%02d:%02d:00', (int) $m[1], (int) $m[2]);
        }
        return null;
    }

/**
     * Parse relative submission date (e.g. "date + 1 week", "lecture date + 1 week") to Y-m-d.
     */
    private function parseSubmissionDate($desc, $sessionDate) {
        $desc = trim((string) $desc);
        if ($desc === '') return null;
        $base = $sessionDate; // Y-m-d
        if (preg_match('/\+?\s*(\d+)\s*week/i', $desc, $m)) {
            $weeks = (int) $m[1];
            $d = new DateTime($base);
            $d->modify('+' . $weeks . ' weeks');
            return $d->format('Y-m-d');
        }
        if (preg_match('/\+?\s*(\d+)\s*day/i', $desc, $m)) {
            $days = (int) $m[1];
            $d = new DateTime($base);
            $d->modify('+' . $days . ' days');
            return $d->format('Y-m-d');
        }
        return null;
    }
}