/home/techb158/workloadmatch.com/workloadmatch.com/BackUp/Manager
Edit: /home/techb158/workloadmatch.com/workloadmatch.com/BackUp/Manager/fetch_all_scheduled_courses.php (6318B)
query("
SELECT Event_Title, Event_Start, Event_End, Event_Color
FROM Events
WHERE Calendar_Year IN (
SELECT DISTINCT YEAR(Start_Date)
FROM Schedule_Course_for_Group
WHERE Program_ID = $programID
AND Group_ID = $groupID
)
");
while ($row = $res->fetch_assoc()) {
$start = new DateTime($row['Event_Start']);
$end = new DateTime($row['Event_End']);
while ($start <= $end) {
$d = $start->format('Y-m-d');
$holidays[$d] = [
'title' => $row['Event_Title'],
'color' => $row['Event_Color'] ?: '#FFD700'
];
$start->modify('+1 day');
}
}
// 2) Load all Retakes (slot aware, new structure)
$retakeSessions = [];
$stmt = $mysqli->prepare("
SELECT rr.Retake_Date, rr.Type, gsm.Time_Slot, gsm.Time_From, gsm.Time_To, gsm.Group_Slot_ID
FROM Retake_Records rr
JOIN Group_Slot_Mapping gsm ON
rr.Time_Slot_Programs_ID = gsm.Time_Slot_Programs_ID
AND rr.Group_ID = gsm.Group_ID
WHERE rr.Program_ID = ?
AND rr.Group_ID = ?
");
$stmt->bind_param('ii', $programID, $groupID);
$stmt->execute();
$stmt->bind_result($retakeDate, $retakeType, $slotLabel, $slotFrom, $slotTo, $groupSlotId);
while ($stmt->fetch()) {
$retakeSessions[$retakeDate][$groupSlotId] = [
'label' => $slotLabel,
'from' => $slotFrom,
'to' => $slotTo,
'type' => $retakeType
];
}
$stmt->close();
// 3) Determine the “Exam Day” for each course (last End_Date)
$examDates = [];
$stmt = $mysqli->prepare("
SELECT Course_ID, MAX(End_Date)
FROM Schedule_Course_for_Group
WHERE Program_ID = ?
AND Group_ID = ?
GROUP BY Course_ID
");
$stmt->bind_param('ii', $programID, $groupID);
$stmt->execute();
$stmt->bind_result($eCourseID, $eDate);
while ($stmt->fetch()) {
$examDates[$eCourseID] = $eDate;
}
$stmt->close();
// 4) Fetch all scheduled sessions
$scheduleRows = [];
$stmt = $mysqli->prepare("
SELECT
scf.Schedule_ID,
scf.Course_ID,
scf.Course_Name,
scf.Reserve_Course,
scf.Start_Date,
scf.End_Date,
gsm.Time_Slot,
gsm.Time_From,
gsm.Time_To,
gsm.Group_Slot_ID
FROM Schedule_Course_for_Group AS scf
JOIN Group_Slot_Mapping AS gsm
ON scf.Group_ID = gsm.Group_ID
WHERE scf.Program_ID = ?
AND scf.Group_ID = ?
ORDER BY scf.Start_Date, scf.Start_Time
");
$stmt->bind_param("ii", $programID, $groupID);
$stmt->execute();
$stmt->bind_result(
$scheduleID,
$courseID,
$courseName,
$reserveCourse,
$startDate,
$endDate,
$slotLabel,
$slotFrom,
$slotTo,
$groupSlotId
);
while ($stmt->fetch()) {
$scheduleRows[$startDate][$groupSlotId] = [
'schedule_id' => $scheduleID,
'course_id' => $courseID,
'course_name' => $courseName,
'reserve' => $reserveCourse,
'start_date' => $startDate,
'end_date' => $endDate,
'slot' => $slotLabel,
'from' => $slotFrom,
'to' => $slotTo,
'group_slot_id' => $groupSlotId
];
}
$stmt->close();
// 5) Build a sorted list of _all_ relevant dates
$allDates = array_unique(
array_merge(
array_keys($scheduleRows),
array_keys($holidays),
array_keys($retakeSessions)
)
);
sort($allDates, SORT_STRING);
// 6) Display
foreach ($allDates as $date) {
$dayName = date('l', strtotime($date));
$isHoliday = isset($holidays[$date]);
$isRetake = isset($retakeSessions[$date]);
$hasSchedule = !empty($scheduleRows[$date]);
// --- 1. Holidays (if no class/retake) ---
if ($isHoliday && !$hasSchedule && !$isRetake) {
$c = $holidays[$date]['color'];
echo "
";
echo "| ({$dayName}) {$date} | - | - | {$holidays[$date]['title']} | ";
echo "
";
continue;
}
// --- 2. Retake Sessions (slot aware) ---
if ($isRetake) {
foreach ($retakeSessions[$date] as $groupSlotId => $retake) {
$slotLabel = $retake['label'] ?? 'Unknown';
$from = $retake['from'] ?? '';
$to = $retake['to'] ?? '';
$retakeType = $retake['type'] ?? '';
$timeRange = ($from && $to)
? date("h:i A", strtotime($from)) . " – " . date("h:i A", strtotime($to))
: '-';
echo "
";
echo "| ({$dayName}) {$date} | ";
echo "{$slotLabel} | ";
echo "{$timeRange} | ";
echo "Retake: " . htmlspecialchars($retakeType) . " | ";
echo "
";
}
// Continue to print regular sessions below if any (on same date)
}
// --- 3. Scheduled Sessions ---
if ($hasSchedule) {
foreach ($scheduleRows[$date] as $groupSlotId => $r) {
$isExam = isset($examDates[$r['course_id']]) && $examDates[$r['course_id']] === $date;
$isRetakeSlot = isset($retakeSessions[$date][$groupSlotId]);
// If this slot already displayed as retake, skip displaying as a regular session.
if ($isRetakeSlot) continue;
$style = "";
if ($isExam) {
$style = "style='background-color: #FFCCCC; font-weight: bold;'";
} elseif ($isHoliday) {
$style = "style='background-color: #FFF5D1;'";
}
$timeRange = date("h:i A", strtotime($r['from'])) . " – " . date("h:i A", strtotime($r['to']));
echo "
";
echo "| ({$dayName}) {$date} | ";
echo "{$r['slot']} | ";
echo "{$timeRange} | ";
echo "" . htmlspecialchars($r['course_name']) . ($isExam ? " (Exam Day)" : "") . " | ";
echo "
";
}
}
}
?>