/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/Schedule_Report_by_Teacher.php (94634B)
prepare("SELECT Time_From, Time_To FROM group_slot_mapping WHERE Group_ID = ? AND LOWER(TRIM(Time_Slot)) = LOWER(TRIM(?)) LIMIT 1");
$stmt->bind_param("is", $groupId, $slotFormatted);
$stmt->execute();
$res = $stmt->get_result()->fetch_assoc();
$stmt->close();
if ($res) {
return date('h:i A', strtotime($res['Time_From'])) . ' - ' . date('h:i A', strtotime($res['Time_To']));
}
return '-';
}
if (isset($_POST['Program_ID'], $_POST['Group_ID'], $_POST['Teacher_ID'])) {
$Program_ID = intval($_POST['Program_ID']);
$Group_ID = intval($_POST['Group_ID']);
$Teacher_ID = intval($_POST['Teacher_ID']);
$stmt = $mysqli->prepare("SELECT class_days FROM manager_group_name WHERE Group_ID = ? AND Program_ID = ?");
$stmt->bind_param("ii", $Group_ID, $Program_ID);
$stmt->execute();
$groupData = $stmt->get_result()->fetch_assoc();
$stmt->close();
if (!$groupData) die("Group not found.");
$groupClassDays = array_map('trim', explode(',', $groupData['class_days']));
$stmt = $mysqli->prepare("SELECT scg.*, c.Course_Name FROM schedule_course_for_group scg JOIN Courses c ON scg.Course_ID = c.Course_ID WHERE scg.Program_ID = ? AND scg.Group_ID = ? AND scg.Assigned = 1 AND EXISTS (SELECT 1 FROM teacher_course_assignments WHERE Teacher_ID = ? AND Schedule_ID = scg.Schedule_ID)");
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Teacher_ID);
$stmt->execute();
$scheduledRows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
$stmt = $mysqli->prepare("SELECT tra.Schedule_ID, tra.Replacement_From, tra.Replacement_To, tca.Course_ID, c.Course_Name, scg.Time_Slot, scg.Start_Date FROM teacher_replacement_assignments tra JOIN teacher_course_assignments tca ON tra.Schedule_ID = tca.Schedule_ID JOIN Courses c ON tca.Course_ID = c.Course_ID JOIN schedule_course_for_group scg ON scg.Schedule_ID = tca.Schedule_ID WHERE tra.Replacing_Teacher_ID = ? AND scg.Group_ID = ? AND scg.Program_ID = ?");
$stmt->bind_param("iii", $Teacher_ID, $Group_ID, $Program_ID);
$stmt->execute();
$replacementsRaw = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
$seenReplacements = [];
foreach ($replacementsRaw as $r) {
$stmt = $mysqli->prepare("SELECT DISTINCT Start_Date FROM schedule_course_for_group WHERE Schedule_ID = ? AND Start_Date BETWEEN ? AND ?");
$stmt->bind_param("iss", $r['Schedule_ID'], $r['Replacement_From'], $r['Replacement_To']);
$stmt->execute();
$replacementDays = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
foreach ($replacementDays as $rd) {
$dateKey = $rd['Start_Date'] . '-' . $r['Schedule_ID'];
if (!isset($seenReplacements[$dateKey])) {
$seenReplacements[$dateKey] = true;
$scheduledRows[] = [
'Start_Date' => $rd['Start_Date'],
'Course_Name' => $r['Course_Name'],
'Time_Slot' => $r['Time_Slot'],
'Course_ID' => $r['Course_ID'],
'Replacement_Mode' => true
];
}
}
}
$absentDates = [];
$stmt = $mysqli->prepare("SELECT Replacement_From, Replacement_To FROM teacher_replacement_assignments WHERE Absent_Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$absents = $stmt->get_result();
while ($rep = $absents->fetch_assoc()) {
$start = new DateTime($rep['Replacement_From']);
$end = (new DateTime($rep['Replacement_To']))->modify('+1 day');
foreach (new DatePeriod($start, new DateInterval('P1D'), $end) as $d) {
$absentDates[$d->format('Y-m-d')] = true;
}
}
$stmt->close();
$examDates = [];
$stmt = $mysqli->prepare("SELECT Course_ID, MAX(Start_Date) AS ExamDate FROM schedule_course_for_group WHERE Program_ID = ? AND Group_ID = ? GROUP BY Course_ID");
$stmt->bind_param("ii", $Program_ID, $Group_ID);
$stmt->execute();
$stmt->bind_result($cid, $examDate);
while ($stmt->fetch()) {
$examDates[$cid] = $examDate;
}
$stmt->close();
$holidays = [];
$res = $mysqli->query("SELECT Event_Title, Event_Start, Event_End, Event_Color FROM Events");
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');
}
}
$retakes = [];
$stmt = $mysqli->prepare("SELECT Retake_Date FROM retake_records WHERE Program_ID = ? AND Group_ID = ?");
$stmt->bind_param("ii", $Program_ID, $Group_ID);
$stmt->execute();
$stmt->bind_result($rDate);
while ($stmt->fetch()) {
$retakes[$rDate] = 'Retake';
}
$stmt->close();
echo "
";
echo "
| Date | Course | Time Slot | Time | Type |
|---|
";
$allDates = [];
foreach ($scheduledRows as $row) {
$allDates[$row['Start_Date']][] = $row;
}
$extraDates = array_merge(array_keys($holidays), array_keys($retakes));
foreach ($extraDates as $d) {
if (!isset($allDates[$d])) {
$allDates[$d] = [];
}
}
ksort($allDates);
foreach ($allDates as $currentDate => $sessions) {
$day = (new DateTime($currentDate))->format('l');
if (isset($holidays[$currentDate]) && empty($sessions)) {
$h = $holidays[$currentDate];
echo "| $currentDate | {$h['title']} | - | - | Holiday |
";
continue;
}
if (isset($retakes[$currentDate]) && empty($sessions)) {
echo "| $currentDate | Retake | - | - | Retake |
";
continue;
}
foreach ($sessions as $row) {
$isAbsent = isset($absentDates[$currentDate]) && empty($row['Replacement_Mode']);
$type = ($examDates[$row['Course_ID']] === $currentDate) ? 'Exam Day' : ($isAbsent ? 'Absent' : (isset($row['Replacement_Mode']) ? 'Replacement' : 'Regular'));
$style = '';
if ($type === 'Exam Day') $style = "style='background-color:#FFCCCC;'";
elseif ($type === 'Replacement') $style = "style='background-color:#ccffcc;'";
elseif ($type === 'Absent') $style = "style='background-color:#ffcccc;'";
$slotLabel = format_time_slot_label($row['Time_Slot'], $day);
$time = format_saturday_time_override($day) ?: get_group_slot_time($Group_ID, $row['Time_Slot'], $mysqli);
echo "| $currentDate | {$row['Course_Name']} | $slotLabel | $time | $type |
";
}
}
echo "
";
}
*/
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
function format_time_slot_label($slot, $day) {
if (strtolower($day) === 'saturday') {
return 'Morning (Sat)';
}
return ucwords(strtolower(trim($slot)));
}
function format_saturday_time_override($day) {
return (strtolower($day) === 'saturday') ? '09:00 AM - 01:00 PM' : null;
}
function get_group_slot_time($groupId, $slot, $mysqli) {
$stmt = $mysqli->prepare("SELECT Time_From, Time_To FROM group_slot_mapping WHERE Group_ID = ? AND LOWER(TRIM(Time_Slot)) = LOWER(TRIM(?)) LIMIT 1");
$stmt->bind_param("is", $groupId, $slot);
$stmt->execute();
$res = $stmt->get_result()->fetch_assoc();
$stmt->close();
if ($res) {
return date('h:i A', strtotime($res['Time_From'])) . ' - ' . date('h:i A', strtotime($res['Time_To']));
}
return '-';
}
if (isset($_POST['Program_ID'], $_POST['Group_ID'], $_POST['Teacher_ID'])) {
$Program_ID = intval($_POST['Program_ID']);
$Group_ID = intval($_POST['Group_ID']);
$Teacher_ID = intval($_POST['Teacher_ID']);
// Get class days
$stmt = $mysqli->prepare("SELECT class_days FROM manager_group_name WHERE Group_ID = ? AND Program_ID = ?");
$stmt->bind_param("ii", $Group_ID, $Program_ID);
$stmt->execute();
$groupData = $stmt->get_result()->fetch_assoc();
$stmt->close();
if (!$groupData) die("Group not found.");
$groupClassDays = array_map('trim', explode(',', $groupData['class_days']));
// Main teacher assignments
$stmt = $mysqli->prepare("
SELECT scg.*, c.Course_Name
FROM schedule_course_for_group scg
JOIN Courses c ON scg.Course_ID = c.Course_ID
JOIN teacher_course_assignments tca ON scg.Schedule_ID = tca.Schedule_ID
WHERE scg.Program_ID = ? AND scg.Group_ID = ? AND scg.Assigned = 1 AND tca.Teacher_ID = ?
");
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Teacher_ID);
$stmt->execute();
$scheduledRows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// Teacher replacements
$stmt = $mysqli->prepare("
SELECT tra.Schedule_ID, tra.Replacement_From, tra.Replacement_To, tca.Course_ID, c.Course_Name, scg.Time_Slot, scg.Start_Date
FROM teacher_replacement_assignments tra
JOIN teacher_course_assignments tca ON tra.Schedule_ID = tca.Schedule_ID
JOIN Courses c ON tca.Course_ID = c.Course_ID
JOIN schedule_course_for_group scg ON scg.Schedule_ID = tca.Schedule_ID
WHERE tra.Replacing_Teacher_ID = ? AND scg.Group_ID = ? AND scg.Program_ID = ?
");
$stmt->bind_param("iii", $Teacher_ID, $Group_ID, $Program_ID);
$stmt->execute();
$replacementsRaw = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
$seenReplacements = [];
foreach ($replacementsRaw as $r) {
$stmt = $mysqli->prepare("SELECT DISTINCT Start_Date FROM schedule_course_for_group WHERE Schedule_ID = ? AND Start_Date BETWEEN ? AND ?");
$stmt->bind_param("iss", $r['Schedule_ID'], $r['Replacement_From'], $r['Replacement_To']);
$stmt->execute();
$replacementDays = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
foreach ($replacementDays as $rd) {
$dateKey = $rd['Start_Date'] . '-' . $r['Schedule_ID'];
if (!isset($seenReplacements[$dateKey])) {
$seenReplacements[$dateKey] = true;
$scheduledRows[] = [
'Start_Date' => $rd['Start_Date'],
'Course_Name' => $r['Course_Name'],
'Time_Slot' => $r['Time_Slot'],
'Course_ID' => $r['Course_ID'],
'Replacement_Mode' => true
];
}
}
}
// Teacher absences
$absentDates = [];
$stmt = $mysqli->prepare("SELECT Replacement_From, Replacement_To FROM teacher_replacement_assignments WHERE Absent_Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$absents = $stmt->get_result();
while ($rep = $absents->fetch_assoc()) {
$start = new DateTime($rep['Replacement_From']);
$end = (new DateTime($rep['Replacement_To']))->modify('+1 day');
foreach (new DatePeriod($start, new DateInterval('P1D'), $end) as $d) {
$absentDates[$d->format('Y-m-d')] = true;
}
}
$stmt->close();
// Exam days (last day per course)
$examDates = [];
$stmt = $mysqli->prepare("SELECT Course_ID, MAX(Start_Date) AS ExamDate FROM schedule_course_for_group WHERE Program_ID = ? AND Group_ID = ? GROUP BY Course_ID");
$stmt->bind_param("ii", $Program_ID, $Group_ID);
$stmt->execute();
$stmt->bind_result($cid, $examDate);
while ($stmt->fetch()) {
$examDates[$cid] = $examDate;
}
$stmt->close();
// Holidays
$holidays = [];
$res = $mysqli->query("SELECT Event_Title, Event_Start, Event_End, Event_Color FROM Events");
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');
}
}
// Retakes
$retakes = [];
$stmt = $mysqli->prepare("SELECT Retake_Date FROM retake_records WHERE Program_ID = ? AND Group_ID = ?");
$stmt->bind_param("ii", $Program_ID, $Group_ID);
$stmt->execute();
$stmt->bind_result($rDate);
while ($stmt->fetch()) {
$retakes[$rDate] = 'Retake';
}
$stmt->close();
// Output HTML table
echo "
";
echo "
| Date | Course | Time Slot | Time | Type |
|---|
";
$allDates = [];
foreach ($scheduledRows as $row) {
$allDates[$row['Start_Date']][] = $row;
}
$extraDates = array_merge(array_keys($holidays), array_keys($retakes));
foreach ($extraDates as $d) {
if (!isset($allDates[$d])) {
$allDates[$d] = [];
}
}
ksort($allDates);
foreach ($allDates as $currentDate => $sessions) {
$day = (new DateTime($currentDate))->format('l');
if (isset($holidays[$currentDate]) && empty($sessions)) {
$h = $holidays[$currentDate];
echo "| $currentDate | {$h['title']} | - | - | Holiday |
";
continue;
}
if (isset($retakes[$currentDate]) && empty($sessions)) {
echo "| $currentDate | Retake | - | - | Retake |
";
continue;
}
foreach ($sessions as $row) {
$isAbsent = isset($absentDates[$currentDate]) && empty($row['Replacement_Mode']);
$type = ($examDates[$row['Course_ID']] === $currentDate) ? 'Exam Day' : ($isAbsent ? 'Absent' : (isset($row['Replacement_Mode']) ? 'Replacement' : 'Regular'));
$style = '';
if ($type === 'Exam Day') $style = "style='background-color:#FFCCCC;'";
elseif ($type === 'Replacement') $style = "style='background-color:#ccffcc;'";
elseif ($type === 'Absent') $style = "style='background-color:#ffcccc;'";
$slotLabel = format_time_slot_label($row['Time_Slot'], $day);
$time = format_saturday_time_override($day) ?: get_group_slot_time($Group_ID, $row['Time_Slot'], $mysqli);
echo "| $currentDate | {$row['Course_Name']} | $slotLabel | $time | $type |
";
}
}
echo "
";
}
/***********************Orgenal Code****************************
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
function format_time_slot_label($slot, $day) {
if ($day === 'Saturday') {
if (stripos($slot, 'evening') !== false) return 'Morning (Sat)';
if (stripos($slot, 'morning') !== false) return 'Morning';
}
return $slot;
}
function format_time_slot_range($slot, $start, $end, $day) {
if ($day === 'Saturday') {
if (stripos($slot, 'morning') !== false) return "08:30 AM - 12:30 PM";
if (stripos($slot, 'evening') !== false) return "09:00 AM - 01:00 PM";
}
return date('h:i A', strtotime($start)) . ' - ' . date('h:i A', strtotime($end));
}
if (isset($_POST['Program_ID'], $_POST['Group_ID'], $_POST['Teacher_ID'])) {
$Program_ID = intval($_POST['Program_ID']);
$Group_ID = intval($_POST['Group_ID']);
$Teacher_ID = intval($_POST['Teacher_ID']);
// 1. Get group details
$stmt = $mysqli->prepare("SELECT class_days FROM manager_group_name WHERE Group_ID = ? AND Program_ID = ?");
$stmt->bind_param("ii", $Group_ID, $Program_ID);
$stmt->execute();
$groupData = $stmt->get_result()->fetch_assoc();
$stmt->close();
if (!$groupData) die("Group not found.");
$groupClassDays = array_map('trim', explode(',', $groupData['class_days']));
// 2. Get teacher assignments
$stmt = $mysqli->prepare("SELECT tca.*, c.Course_Name, cg.Time_Slot, cg.Start_Time AS Schedule_Start_Time, cg.End_Time AS Schedule_End_Time
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
JOIN course_group_schedule cg ON tca.Schedule_ID = cg.Schedule_ID
WHERE tca.Program_ID = ? AND tca.Group_ID = ? AND tca.Teacher_ID = ?");
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Teacher_ID);
$stmt->execute();
$assignments = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 3. Replacements
$stmt = $mysqli->prepare("SELECT tra.Replacement_From, tra.Replacement_To, tca.*, c.Course_Name, cg.Time_Slot, cg.Start_Time AS Schedule_Start_Time, cg.End_Time AS Schedule_End_Time
FROM teacher_replacement_assignments tra
JOIN teacher_course_assignments tca ON tra.Schedule_ID = tca.Schedule_ID
JOIN Courses c ON tca.Course_ID = c.Course_ID
JOIN course_group_schedule cg ON cg.Schedule_ID = tca.Schedule_ID
WHERE tra.Replacing_Teacher_ID = ? AND cg.Group_ID = ? AND cg.Program_ID = ?");
$stmt->bind_param("iii", $Teacher_ID, $Group_ID, $Program_ID);
$stmt->execute();
$replacements = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
foreach ($replacements as &$r) {
$r['Replacement_Mode'] = true;
}
// 4. Absences
$absentDates = [];
$stmt = $mysqli->prepare("SELECT Replacement_From, Replacement_To FROM teacher_replacement_assignments WHERE Absent_Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$absents = $stmt->get_result();
while ($rep = $absents->fetch_assoc()) {
$start = new DateTime($rep['Replacement_From']);
$end = (new DateTime($rep['Replacement_To']))->modify('+1 day');
foreach (new DatePeriod($start, new DateInterval('P1D'), $end) as $d) {
$absentDates[$d->format('Y-m-d')] = true;
}
}
$stmt->close();
// 5. Holidays
$holidayMapping = [];
$res = $mysqli->query("SELECT Event_Title, Event_Start, Event_End, Event_Color FROM Events");
while ($event = $res->fetch_assoc()) {
$start = new DateTime($event['Event_Start']);
$end = (new DateTime($event['Event_End']))->modify('+1 day');
foreach (new DatePeriod($start, new DateInterval('P1D'), $end) as $d) {
$holidayMapping[$d->format('Y-m-d')][] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
// 6. Retakes
$retakes = [];
$stmt = $mysqli->prepare("SELECT rr.Retake_Date, tsp.Time_Slot
FROM retake_records rr
JOIN time_slot_programs tsp ON rr.time_slot_programs_ID = tsp.time_slot_programs_ID
WHERE rr.Program_ID = ? AND rr.Group_ID = ?");
$stmt->bind_param("ii", $Program_ID, $Group_ID);
$stmt->execute();
$retakes = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 7. Build date range
$combined = array_merge($assignments, $replacements);
$dates = [];
foreach ($combined as $a) {
$s = new DateTime($a['Replacement_Mode'] ? $a['Replacement_From'] : $a['Start_Date']);
$e = new DateTime($a['Replacement_Mode'] ? $a['Replacement_To'] : $a['End_Date']);
$dates[] = $s->format('Y-m-d');
$dates[] = $e->format('Y-m-d');
}
$start = new DateTime(min($dates));
$end = (new DateTime(max($dates)))->modify('+1 day');
$range = new DatePeriod($start, new DateInterval('P1D'), $end);
// 8. Output report
echo "
";
echo "
| Date | Course | Time Slot | Time | Type |
|---|
";
foreach ($range as $date) {
$day = $date->format('l');
$currentDate = $date->format('Y-m-d');
// Holiday
if (isset($holidayMapping[$currentDate])) {
foreach ($holidayMapping[$currentDate] as $event) {
echo "| $currentDate | {$event['title']} | - | - | Holiday |
";
}
continue;
}
// Retakes
foreach ($retakes as $r) {
if ($r['Retake_Date'] === $currentDate) {
$label = format_time_slot_label($r['Time_Slot'], $day);
echo "| $currentDate | Retake | $label | - | Retake |
";
}
}
// Classes
foreach ($combined as $a) {
$s = new DateTime($a['Replacement_Mode'] ? $a['Replacement_From'] : $a['Start_Date']);
$e = new DateTime($a['Replacement_Mode'] ? $a['Replacement_To'] : $a['End_Date']);
$e->modify('+1 day');
if ($date >= $s && $date < $e) {
if (!in_array($day, $groupClassDays) && $currentDate != $a['End_Date']) continue;
$skip = false;
foreach ($retakes as $r) {
if ($r['Retake_Date'] === $currentDate && strtolower(trim($r['Time_Slot'])) === strtolower(trim($a['Time_Slot']))) {
$skip = true;
break;
}
}
if ($skip) continue;
//$type = $a['Replacement_Mode'] ? 'Replacement' : (($currentDate == $a['End_Date']) ? 'Exam Day' : 'Regular');
//$bg = $a['Replacement_Mode'] ? "style='background-color:#ccffcc'" : (isset($absentDates[$currentDate]) ? "style='background-color:#ffcccc'" : "");
$type = $a['Replacement_Mode'] ? 'Replacement' : (($currentDate == $a['End_Date']) ? 'Exam Day' : 'Regular');
// Set background color based on type
if (isset($absentDates[$currentDate]) && !$a['Replacement_Mode']) {
$type = 'Absent';
$bg = "style='background-color:#ffcccc'"; // Light red
} elseif ($type === 'Exam Day') {
$bg = "style='background-color:#fff3cd'"; // Light yellow
} elseif ($a['Replacement_Mode']) {
$bg = "style='background-color:#ccffcc'"; // Light green
} else {
$bg = ""; // No background
}
$slotLabel = format_time_slot_label($a['Time_Slot'], $day);
$time = format_time_slot_range($a['Time_Slot'], $a['Schedule_Start_Time'], $a['Schedule_End_Time'], $day);
echo "| $currentDate | {$a['Course_Name']} | $slotLabel | $time | $type |
";
// echo "| $currentDate | {$a['Course_Name']} | $slotLabel | $time | $type |
";
}
}
}
echo "
";
}
**/
/*
if (isset($_POST['Program_ID']) && isset($_POST['Group_ID']) && isset($_POST['Teacher_ID'])) {
$Program_ID = intval($_POST['Program_ID']);
$Group_ID = intval($_POST['Group_ID']);
$Teacher_ID = intval($_POST['Teacher_ID']);
// 1. Group Details
$stmtGroup = $mysqli->prepare("SELECT * FROM manager_group_name WHERE Group_ID = ? AND Program_ID = ?");
$stmtGroup->bind_param("ii", $Group_ID, $Program_ID);
$stmtGroup->execute();
$groupData = $stmtGroup->get_result()->fetch_assoc();
$stmtGroup->close();
if (!$groupData) die("Group not found.");
$groupClassDays = explode(',', trim($groupData['class_days']));
// 2. Teacher Assignments
$stmt = $mysqli->prepare("SELECT tca.*, c.Course_Name, cg.Start_Time AS Schedule_Start_Time, cg.End_Time AS Schedule_End_Time
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
JOIN course_group_schedule cg ON tca.Schedule_ID = cg.Schedule_ID
WHERE tca.Program_ID = ? AND tca.Group_ID = ? AND tca.Teacher_ID = ?");
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Teacher_ID);
$stmt->execute();
$assignments = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 3. Replacements WHERE teacher is replacing someone
$replacementAssignments = [];
$stmt = $mysqli->prepare("SELECT tra.Replacement_From, tra.Replacement_To, tca.*, c.Course_Name, cg.Start_Time AS Schedule_Start_Time, cg.End_Time AS Schedule_End_Time
FROM teacher_replacement_assignments tra
JOIN teacher_course_assignments tca ON tra.Schedule_ID = tca.Schedule_ID
JOIN Courses c ON tca.Course_ID = c.Course_ID
JOIN course_group_schedule cg ON cg.Schedule_ID = tca.Schedule_ID
WHERE tra.Replacing_Teacher_ID = ? AND cg.Group_ID = ? AND cg.Program_ID = ?");
$stmt->bind_param("iii", $Teacher_ID, $Group_ID, $Program_ID);
$stmt->execute();
$reps = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
foreach ($reps as $r) {
$r['Replacement_Mode'] = true;
$replacementAssignments[] = $r;
}
// 4. Absence dates WHERE teacher is absent
$absentDates = [];
$stmt = $mysqli->prepare("SELECT Replacement_From, Replacement_To FROM teacher_replacement_assignments WHERE Absent_Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$absents = $stmt->get_result();
while ($rep = $absents->fetch_assoc()) {
$start = new DateTime($rep['Replacement_From']);
$end = (new DateTime($rep['Replacement_To']))->modify('+1 day');
foreach (new DatePeriod($start, new DateInterval('P1D'), $end) as $d) {
$absentDates[$d->format('Y-m-d')] = true;
}
}
$stmt->close();
// 5. Holidays
$holidayMapping = [];
$stmt = $mysqli->prepare("SELECT Event_Title, Event_Color, Event_Start, Event_End FROM Events");
$stmt->execute();
$events = $stmt->get_result();
while ($event = $events->fetch_assoc()) {
$start = new DateTime($event['Event_Start']);
$end = (new DateTime($event['Event_End']))->modify('+1 day');
foreach (new DatePeriod($start, new DateInterval('P1D'), $end) as $d) {
$holidayMapping[$d->format('Y-m-d')][] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
$stmt->close();
// 6. Retakes
$retakes = [];
$stmt = $mysqli->prepare("SELECT Retake_Date FROM retake_records WHERE Program_ID = ? AND Group_ID = ?");
$stmt->bind_param("ii", $Program_ID, $Group_ID);
$stmt->execute();
$retakes = array_column($stmt->get_result()->fetch_all(MYSQLI_ASSOC), 'Retake_Date');
$stmt->close();
// 7. Build total schedule list
$assignments = array_merge($assignments, $replacementAssignments);
$dates = [];
foreach ($assignments as $a) {
$s = new DateTime($a['Replacement_Mode'] ? $a['Replacement_From'] : $a['Start_Date']);
$e = new DateTime($a['Replacement_Mode'] ? $a['Replacement_To'] : $a['End_Date']);
$dates[] = $s->format('Y-m-d');
$dates[] = $e->format('Y-m-d');
}
$start = !empty($dates) ? new DateTime(min($dates)) : new DateTime();
$end = !empty($dates) ? (new DateTime(max($dates)))->modify('+1 day') : (clone $start)->modify('+30 days');
$range = new DatePeriod($start, new DateInterval('P1D'), $end);
foreach ($range as $date) {
$day = $date->format('l');
$currentDate = $date->format('Y-m-d');
if (!in_array($day, array_map('trim', $groupClassDays))) continue;
if (isset($holidayMapping[$currentDate])) {
foreach ($holidayMapping[$currentDate] as $event) {
echo "
| $currentDate | {$event['title']} | - | - | Holiday |
";
}
continue;
}
if (in_array($currentDate, $retakes)) {
echo "
| $currentDate | Retake Day | - | - | Retake |
";
continue;
}
$shown = false;
foreach ($assignments as $a) {
$s = new DateTime($a['Replacement_Mode'] ? $a['Replacement_From'] : $a['Start_Date']);
$e = new DateTime($a['Replacement_Mode'] ? $a['Replacement_To'] : $a['End_Date']);
$e->modify('+1 day');
if ($date >= $s && $date < $e) {
$type = $a['Replacement_Mode'] ? 'Replacement' : (($currentDate == $a['End_Date']) ? 'Exam Day' : 'Regular');
$bg = $a['Replacement_Mode'] ? "background-color:#ccffcc" : (isset($absentDates[$currentDate]) ? "background-color:#ffcccc" : "");
if (isset($absentDates[$currentDate]) && !$a['Replacement_Mode']) {
$type = 'Absent';
}
$rangeTime = date('h:i A', strtotime($a['Schedule_Start_Time'])) . ' - ' . date('h:i A', strtotime($a['Schedule_End_Time']));
echo "
| $currentDate | {$a['Course_Name']} | {$a['Time_Slot']} | $rangeTime | $type |
";
$shown = true;
}
}
if (!$shown) {
echo "
| $currentDate | No Class | - | - | Free |
";
}
}
}
*/
/*
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if (isset($_POST["Program_ID"]) && isset($_POST["Group_ID"]) && isset($_POST["Teacher_ID"])) {
$Program_ID = $_POST['Program_ID'];
$Group_ID = $_POST['Group_ID'];
$Teacher_ID = $_POST['Teacher_ID'];
if ($Program_ID <= 0 || $Group_ID <= 0 || $Teacher_ID <= 0) {
die("Invalid parameters.");
}
// 1. Retrieve group details
$stmtGroup = $mysqli->prepare("SELECT * FROM manager_group_name WHERE Group_ID = ? AND Program_ID = ?");
if (!$stmtGroup) die("Error preparing group query: " . $mysqli->error);
$stmtGroup->bind_param("ii", $Group_ID, $Program_ID);
$stmtGroup->execute();
$groupData = $stmtGroup->get_result()->fetch_assoc();
$stmtGroup->close();
if (!$groupData) die("Group not found.");
$groupTimeSlot = trim($groupData['Time_Slot']);
$groupStartTime = $groupData['Time_From'];
$groupEndTime = $groupData['Time_To'];
$weekendClass = intval($groupData['Weekend_Class'] ?? 0);
$groupClassDays = trim($groupData['class_days']);
// 2. Fetch main assignments
$stmt = $mysqli->prepare("SELECT tca.*, c.Course_Name, cg.Start_Time AS Schedule_Start_Time, cg.End_Time AS Schedule_End_Time
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
JOIN course_group_schedule cg ON tca.Schedule_ID = cg.Schedule_ID
WHERE tca.Program_ID = ? AND tca.Group_ID = ? AND tca.Teacher_ID = ?");
if (!$stmt) die("Error preparing assignment query: " . $mysqli->error);
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Teacher_ID);
$stmt->execute();
$assignments = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 3. Retrieve holiday events
$holidayMapping = [];
$stmtEvent = $mysqli->prepare("SELECT Event_Title, Event_Color, Event_Start, Event_End FROM Events");
if ($stmtEvent) {
$stmtEvent->execute();
$events = $stmtEvent->get_result();
while ($event = $events->fetch_assoc()) {
$start = new DateTime($event['Event_Start']);
$end = (new DateTime($event['Event_End']))->modify('+1 day');
foreach (new DatePeriod($start, new DateInterval('P1D'), $end) as $d) {
$holidayMapping[$d->format('Y-m-d')][] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
$stmtEvent->close();
}
// 4. Fetch retake days
$retakes = [];
$stmtRetake = $mysqli->prepare("SELECT Retake_Date FROM retake_records WHERE Program_ID = ? AND Group_ID = ?");
if ($stmtRetake) {
$stmtRetake->bind_param("ii", $Program_ID, $Group_ID);
$stmtRetake->execute();
$retakes = array_column($stmtRetake->get_result()->fetch_all(MYSQLI_ASSOC), 'Retake_Date');
$stmtRetake->close();
}
// 5. Fetch replacement dates for this teacher
$replacementDates = [];
$stmtRepl = $mysqli->prepare("SELECT Replacement_From, Replacement_To FROM teacher_replacement_assignments WHERE Absent_Teacher_ID = ?");
if ($stmtRepl) {
$stmtRepl->bind_param("i", $Teacher_ID);
$stmtRepl->execute();
$replacements = $stmtRepl->get_result();
while ($rep = $replacements->fetch_assoc()) {
$start = new DateTime($rep['Replacement_From']);
$end = (new DateTime($rep['Replacement_To']))->modify('+1 day');
foreach (new DatePeriod($start, new DateInterval('P1D'), $end) as $d) {
$replacementDates[$d->format('Y-m-d')] = true;
}
}
$stmtRepl->close();
}
// 6. Determine the report date range
if (!empty($assignments)) {
$startDates = array_column($assignments, 'Start_Date');
$endDates = array_column($assignments, 'End_Date');
$globalStart = new DateTime(min($startDates));
$globalEnd = (new DateTime(max($endDates)))->modify('+1 day');
} else {
$globalStart = new DateTime();
$globalEnd = (clone $globalStart)->modify('+30 days');
}
$period = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
// 7. Generate schedule rows
foreach ($period as $date) {
$currentDate = $date->format('Y-m-d');
$dayName = $date->format('l');
$dayOfWeek = $date->format('N');
// Check class days
if (!in_array($dayName, array_map('trim', explode(',', $groupClassDays)))) continue;
// Display holiday
if (isset($holidayMapping[$currentDate])) {
foreach ($holidayMapping[$currentDate] as $event) {
echo "
| $currentDate | {$event['title']} | - | - | Holiday |
";
}
continue;
}
// Display retake day
if (in_array($currentDate, $retakes)) {
echo "
| $currentDate | Retake Day | - | - | Retake |
";
continue;
}
// Check if it's an assigned or replaced day
$found = false;
foreach ($assignments as $a) {
$start = new DateTime($a['Start_Date']);
$end = (new DateTime($a['End_Date']))->modify('+1 day');
if ($date >= $start && $date < $end) {
$type = ($currentDate === $a['End_Date']) ? 'Exam Day' : (isset($replacementDates[$currentDate]) ? 'Replaced' : 'Regular');
$bgColor = $type === 'Replaced' ? "style='background-color:#ffcccc;'" : "";
$timeRange = date('h:i A', strtotime($a['Schedule_Start_Time'])) . ' - ' . date('h:i A', strtotime($a['Schedule_End_Time']));
echo "
| $currentDate | {$a['Course_Name']} | {$a['Time_Slot']} | $timeRange | $type |
";
$found = true;
}
}
if (!$found) {
echo "
| $currentDate | No Class | - | - | Free |
";
}
}
}
*/
/*
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if (isset($_POST["Program_ID"]) && isset($_POST["Group_ID"]) && isset($_POST["Teacher_ID"])) {
// Get and validate parameters.
$Program_ID = $_POST['Program_ID'];
$Group_ID = $_POST['Group_ID'];
$Teacher_ID = $_POST['Teacher_ID'];
if ($Program_ID <= 0 || $Group_ID <= 0 || $Teacher_ID <= 0) {
die("Invalid parameters.");
}
// ---------------------------------------------------
// 1. Retrieve group details from manager_group_name.
// ---------------------------------------------------
$groupQuery = "SELECT * FROM manager_group_name WHERE Group_ID = ? AND Program_ID = ?";
$stmtGroup = $mysqli->prepare($groupQuery);
if (!$stmtGroup) {
die("Error preparing group query: " . $mysqli->error);
}
$stmtGroup->bind_param("ii", $Group_ID, $Program_ID);
$stmtGroup->execute();
$resultGroup = $stmtGroup->get_result();
$groupData = $resultGroup->fetch_assoc();
$stmtGroup->close();
if (!$groupData) {
die("Group not found.");
}
// Retrieve group details.
$groupTimeSlot = trim($groupData['Time_Slot']); // e.g., "Morning,Afternoon"
$groupStartTime = $groupData['Time_From']; // e.g., "08:30:00"
$groupEndTime = $groupData['Time_To']; // e.g., "15:30:00"
$weekendClass = isset($groupData['Weekend_Class']) ? intval($groupData['Weekend_Class']) : 0; // 0 = no Saturday class
$groupClassDays = trim($groupData['class_days']); // e.g., "Monday,Tuesday,Wednesday,Thursday,Friday"
// ---------------------------------------------------
// 2. Retrieve assignments for this teacher.
// ---------------------------------------------------
// Now join with course_group_schedule (alias: cg). Note that the table contains Start_Time and End_Time.
$assignmentQuery = "SELECT tca.*, c.Course_Name, c.Course_Time,
cg.Start_Time AS Schedule_Start_Time,
cg.End_Time AS Schedule_End_Time
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
JOIN course_group_schedule cg ON tca.Schedule_ID = cg.Schedule_ID
WHERE tca.Program_ID = ?
AND tca.Group_ID = ?
AND tca.Teacher_ID = ?
ORDER BY tca.Assigned_At DESC";
$stmt = $mysqli->prepare($assignmentQuery);
if (!$stmt) {
die("Error preparing assignment query: " . $mysqli->error);
}
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Teacher_ID);
if (!$stmt->execute()) {
die("Error executing assignment query: " . $stmt->error);
}
$result = $stmt->get_result();
$assignments = [];
while ($row = $result->fetch_assoc()) {
$assignments[] = $row;
}
$stmt->close();
// ---------------------------------------------------
// 3. Retrieve holiday events from the Events table.
// ---------------------------------------------------
$eventQuery = "SELECT Event_Title, Event_Color, Event_Start, Event_End FROM Events";
$stmtEvent = $mysqli->prepare($eventQuery);
if ($stmtEvent) {
$stmtEvent->execute();
$eventResult = $stmtEvent->get_result();
$holidayMapping = []; // key: date (Y-m-d) => array of event details.
while ($event = $eventResult->fetch_assoc()) {
$startEvent = new DateTime($event['Event_Start']);
$endEvent = new DateTime($event['Event_End']);
$endEvent->modify('+1 day'); // Include the last day.
$interval = new DateInterval('P1D');
$period = new DatePeriod($startEvent, $interval, $endEvent);
foreach ($period as $dt) {
$d = $dt->format('Y-m-d');
$holidayMapping[$d][] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
$stmtEvent->close();
} else {
$holidayMapping = [];
}
// ---------------------------------------------------
// 4. Retrieve retake dates from the retake_records table.
// ---------------------------------------------------
$retakeQuery = "SELECT Retake_Date FROM retake_records WHERE Program_ID = ? AND Group_ID = ?";
$stmtRetake = $mysqli->prepare($retakeQuery);
if ($stmtRetake) {
$stmtRetake->bind_param("ii", $Program_ID, $Group_ID);
$stmtRetake->execute();
$retakeResult = $stmtRetake->get_result();
$retakes = [];
while ($r = $retakeResult->fetch_assoc()) {
$retakes[] = $r['Retake_Date'];
}
$stmtRetake->close();
} else {
$retakes = [];
}
// ---------------------------------------------------
// 5. Determine a global date range for the report (based on assignments).
// ---------------------------------------------------
if (!empty($assignments)) {
$globalStart = new DateTime($assignments[0]['Start_Date']);
$globalEnd = new DateTime($assignments[0]['End_Date']);
foreach ($assignments as $assignment) {
$d1 = new DateTime($assignment['Start_Date']);
$d2 = new DateTime($assignment['End_Date']);
if ($d1 < $globalStart) {
$globalStart = $d1;
}
if ($d2 > $globalEnd) {
$globalEnd = $d2;
}
}
$globalEnd->modify('+1 day'); // Make the end date inclusive.
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
} else {
$globalStart = new DateTime();
$globalEnd = clone $globalStart;
$globalEnd->modify('+30 days');
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
}
// ---------------------------------------------------
// 6. Generate the day-by-day report.
// ---------------------------------------------------
// Break group time slot into periods (if defined).
$groupPeriods = array_map('trim', explode(',', $groupTimeSlot));
// Always define $singlePeriodStart and $singlePeriodEnd using the overall group times.
if (!empty($groupStartTime) && !empty($groupEndTime)) {
try {
$singlePeriodStart = new DateTime($groupStartTime);
$singlePeriodEnd = new DateTime($groupEndTime);
} catch (Exception $e) {
$singlePeriodStart = new DateTime("08:00:00");
$singlePeriodEnd = new DateTime("15:00:00");
}
} else {
$singlePeriodStart = new DateTime("08:00:00");
$singlePeriodEnd = new DateTime("15:00:00");
}
// Loop through each day in the global period.
foreach ($globalPeriod as $date) {
if (!($date instanceof DateTime)) {
continue;
}
$currentDate = $date->format('Y-m-d');
$dayOfWeek = $date->format('N'); // 1 (Monday) to 7 (Sunday)
$currentDay = $date->format('l'); // e.g., "Monday"
// --- Custom Class Days Check ---
if (!empty($groupClassDays)) {
$customDays = array_map('trim', explode(',', $groupClassDays));
if (!in_array($currentDay, $customDays)) {
continue; // Skip this date if it is not one of the custom class days.
}
} else {
if ($dayOfWeek == 7) {
continue;
}
if ($dayOfWeek == 6 && $weekendClass == 0) {
continue;
}
}
// --- Exclusion Check: Holidays and Retake Days ---
if (isset($holidayMapping[$currentDate])) {
foreach ($holidayMapping[$currentDate] as $event) {
echo "
| " . htmlspecialchars($currentDate) . " |
" . htmlspecialchars($event['title']) . " |
- |
- |
Holiday |
";
}
continue;
}
if (in_array($currentDate, $retakes)) {
echo "
| " . htmlspecialchars($currentDate) . " |
Retake Day |
- |
- |
Retake |
";
continue;
}
// --- Regular Class Days ---
$foundAssignment = false;
foreach ($assignments as $assignment) {
$assignStart = new DateTime($assignment['Start_Date']);
$assignEnd = new DateTime($assignment['End_Date']);
$assignEnd->modify('+1 day'); // Make the assignment period inclusive.
if ($date >= $assignStart && $date < $assignEnd) {
// Try to use the Schedule times from course_group_schedule first.
$slotStart = null;
$slotEnd = null;
if (!empty($assignment['Schedule_Start_Time']) && !empty($assignment['Schedule_End_Time'])) {
try {
$slotStart = new DateTime($assignment['Schedule_Start_Time']);
$slotEnd = new DateTime($assignment['Schedule_End_Time']);
} catch (Exception $e) {
// If parsing fails, leave them as null to trigger fallback.
$slotStart = null;
$slotEnd = null;
}
}
// If schedule times are not available, or if they were invalid, fall back on group-defined times
if (empty($slotStart) || empty($slotEnd)) {
$slot = strtolower(trim($assignment['Time_Slot']));
switch ($slot) {
case 'morning':
if (!empty($groupData['Morning_From']) && !empty($groupData['Morning_To'])) {
try {
$slotStart = new DateTime($groupData['Morning_From']);
$slotEnd = new DateTime($groupData['Morning_To']);
} catch (Exception $e) {
$slotStart = new DateTime("08:30:00");
$slotEnd = new DateTime("12:00:00");
}
} else {
$slotStart = new DateTime("08:30:00");
$slotEnd = new DateTime("12:00:00");
}
break;
case 'afternoon':
if (!empty($groupData['Afternoon_From']) && !empty($groupData['Afternoon_To'])) {
try {
$slotStart = new DateTime($groupData['Afternoon_From']);
$slotEnd = new DateTime($groupData['Afternoon_To']);
} catch (Exception $e) {
$slotStart = new DateTime("01:00:00");
$slotEnd = new DateTime("05:00:00");
}
} else {
$slotStart = new DateTime("01:00:00");
$slotEnd = new DateTime("05:00:00");
}
break;
case 'evening':
if (!empty($groupData['Evening_From']) && !empty($groupData['Evening_To'])) {
try {
$slotStart = new DateTime($groupData['Evening_From']);
$slotEnd = new DateTime($groupData['Evening_To']);
} catch (Exception $e) {
$slotStart = new DateTime("05:30:00");
$slotEnd = new DateTime("09:00:00");
}
} else {
$slotStart = new DateTime("05:30:00");
$slotEnd = new DateTime("09:00:00");
}
break;
default:
// For unknown or missing time slot values, fallback to overall group times.
$slotStart = $singlePeriodStart;
$slotEnd = $singlePeriodEnd;
break;
}
}
// Format the time range.
$timeRange = $slotStart->format("h:i A") . " - " . $slotEnd->format("h:i A");
// Determine the type: if current date equals the actual End_Date, mark as Exam Day.
$actualEnd = new DateTime($assignment['End_Date']);
$type = "Regular";
if ($currentDate === $actualEnd->format("Y-m-d")) {
$type = "Exam Day";
}
echo "
| " . htmlspecialchars($currentDate) . " |
" . htmlspecialchars($assignment['Course_Name']) . " |
" . htmlspecialchars($assignment['Time_Slot']) . " |
" . htmlspecialchars($timeRange) . " |
" . htmlspecialchars($type) . " |
";
$foundAssignment = true;
}
}
if (!$foundAssignment) {
// Output a row for free days.
echo "
| " . htmlspecialchars($currentDate) . " |
No Class |
- |
- |
Free |
";
}
}
}
*/
/*
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if (isset($_POST["Program_ID"]) && isset($_POST["Group_ID"]) && isset($_POST["Teacher_ID"])) {
// Get and validate parameters.
$Program_ID = $_POST['Program_ID'];
$Group_ID = $_POST['Group_ID'];
$Teacher_ID = $_POST['Teacher_ID'];
if ($Program_ID <= 0 || $Group_ID <= 0 || $Teacher_ID <= 0) {
die("Invalid parameters.");
}
// ---------------------------------------------------
// 1. Retrieve group details from manager_group_name.
// ---------------------------------------------------
$groupQuery = "SELECT * FROM manager_group_name WHERE Group_ID = ? AND Program_ID = ?";
$stmtGroup = $mysqli->prepare($groupQuery);
if (!$stmtGroup) {
die("Error preparing group query: " . $mysqli->error);
}
$stmtGroup->bind_param("ii", $Group_ID, $Program_ID);
$stmtGroup->execute();
$resultGroup = $stmtGroup->get_result();
$groupData = $resultGroup->fetch_assoc();
$stmtGroup->close();
if (!$groupData) {
die("Group not found.");
}
// Retrieve group details.
$groupTimeSlot = trim($groupData['Time_Slot']); // e.g., "Morning,Afternoon"
$groupStartTime = $groupData['Time_From']; // e.g., "08:30:00"
$groupEndTime = $groupData['Time_To']; // e.g., "15:30:00"
$weekendClass = isset($groupData['Weekend_Class']) ? intval($groupData['Weekend_Class']) : 0; // 0 = no Saturday class
$groupClassDays = trim($groupData['class_days']); // e.g., "Monday,Tuesday,Wednesday,Thursday,Friday"
// ---------------------------------------------------
// 2. Retrieve assignments for this teacher.
// ---------------------------------------------------
$query = "SELECT tca.*, c.Course_Name, c.Course_Time
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Program_ID = ?
AND tca.Group_ID = ?
AND tca.Teacher_ID = ?
ORDER BY tca.Assigned_At DESC";
$stmt = $mysqli->prepare($query);
if (!$stmt) {
die("Error preparing assignment query: " . $mysqli->error);
}
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Teacher_ID);
if (!$stmt->execute()) {
die("Error executing assignment query: " . $stmt->error);
}
$result = $stmt->get_result();
$assignments = [];
while ($row = $result->fetch_assoc()) {
$assignments[] = $row;
}
$stmt->close();
// ---------------------------------------------------
// 3. Retrieve holiday events from the Events table.
// ---------------------------------------------------
$eventQuery = "SELECT Event_Title, Event_Color, Event_Start, Event_End FROM Events";
$stmtEvent = $mysqli->prepare($eventQuery);
if ($stmtEvent) {
$stmtEvent->execute();
$eventResult = $stmtEvent->get_result();
$holidayMapping = []; // key: date (Y-m-d) => array of event details.
while ($event = $eventResult->fetch_assoc()) {
$startEvent = new DateTime($event['Event_Start']);
$endEvent = new DateTime($event['Event_End']);
$endEvent->modify('+1 day'); // Include the last day.
$interval = new DateInterval('P1D');
$period = new DatePeriod($startEvent, $interval, $endEvent);
foreach ($period as $dt) {
$d = $dt->format('Y-m-d');
$holidayMapping[$d][] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
$stmtEvent->close();
} else {
$holidayMapping = [];
}
// ---------------------------------------------------
// 4. Retrieve retake dates from the retake_records table.
// ---------------------------------------------------
$retakeQuery = "SELECT Retake_Date FROM retake_records WHERE Program_ID = ? AND Group_ID = ?";
$stmtRetake = $mysqli->prepare($retakeQuery);
if ($stmtRetake) {
$stmtRetake->bind_param("ii", $Program_ID, $Group_ID);
$stmtRetake->execute();
$retakeResult = $stmtRetake->get_result();
$retakes = [];
while ($r = $retakeResult->fetch_assoc()) {
$retakes[] = $r['Retake_Date'];
}
$stmtRetake->close();
} else {
$retakes = [];
}
// ---------------------------------------------------
// 5. Determine a global date range for the report (based on assignments).
// ---------------------------------------------------
if (!empty($assignments)) {
$globalStart = new DateTime($assignments[0]['Start_Date']);
$globalEnd = new DateTime($assignments[0]['End_Date']);
foreach ($assignments as $assignment) {
$d1 = new DateTime($assignment['Start_Date']);
$d2 = new DateTime($assignment['End_Date']);
if ($d1 < $globalStart) {
$globalStart = $d1;
}
if ($d2 > $globalEnd) {
$globalEnd = $d2;
}
}
$globalEnd->modify('+1 day'); // Make the end date inclusive.
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
} else {
$globalStart = new DateTime();
$globalEnd = clone $globalStart;
$globalEnd->modify('+30 days');
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
}
// ---------------------------------------------------
// 6. Generate the day-by-day report.
// ---------------------------------------------------
// Break group time slot into periods (if defined).
$groupPeriods = array_map('trim', explode(',', $groupTimeSlot));
// Always define $singlePeriodStart and $singlePeriodEnd using the group's overall times.
if (!empty($groupStartTime) && !empty($groupEndTime)) {
try {
$singlePeriodStart = new DateTime($groupStartTime);
$singlePeriodEnd = new DateTime($groupEndTime);
} catch (Exception $e) {
$singlePeriodStart = new DateTime("08:00:00");
$singlePeriodEnd = new DateTime("15:00:00");
}
} else {
$singlePeriodStart = new DateTime("08:00:00");
$singlePeriodEnd = new DateTime("15:00:00");
}
// Loop through each day in the global period.
foreach ($globalPeriod as $date) {
if (!($date instanceof DateTime)) {
continue;
}
$currentDate = $date->format('Y-m-d');
$dayOfWeek = $date->format('N'); // 1 (Monday) to 7 (Sunday)
$currentDay = $date->format('l'); // e.g., "Monday"
// --- Custom Class Days Check ---
if (!empty($groupClassDays)) {
$customDays = array_map('trim', explode(',', $groupClassDays));
if (!in_array($currentDay, $customDays)) {
continue;
}
} else {
if ($dayOfWeek == 7) {
continue;
}
if ($dayOfWeek == 6 && $weekendClass == 0) {
continue;
}
}
// --- Exclusion Check: Holidays and Retake Days ---
if (isset($holidayMapping[$currentDate])) {
foreach ($holidayMapping[$currentDate] as $event) {
echo "
| " . htmlspecialchars($currentDate) . " |
" . htmlspecialchars($event['title']) . " |
- |
- |
Holiday |
";
}
continue;
}
if (in_array($currentDate, $retakes)) {
echo "
| " . htmlspecialchars($currentDate) . " |
Retake Day |
- |
- |
Retake |
";
continue;
}
//// --- Regular Class Days ---
//$foundAssignment = false;
//foreach ($assignments as $assignment) {
// $assignStart = new DateTime($assignment['Start_Date']);
// $assignEnd = new DateTime($assignment['End_Date']);
// $assignEnd->modify('+1 day'); // Make the assignment period inclusive.
// if ($date >= $assignStart && $date < $assignEnd) {
// if ($dayOfWeek == 6) {
// $timeRange = "09:00 AM - 01:00 PM";
// } else {
// $timeRange = $singlePeriodStart->format("h:i A") . " - " . $singlePeriodEnd->format("h:i A");
// }
// echo "
// | " . htmlspecialchars($currentDate) . " |
// " . htmlspecialchars($assignment['Course_Name']) . " |
// " . htmlspecialchars($assignment['Time_Slot']) . " |
// " . htmlspecialchars($timeRange) . " |
// Regular |
//
";
// $foundAssignment = true;
// }
//}
// --- Regular Class Days ---
$foundAssignment = false;
foreach ($assignments as $assignment) {
$assignStart = new DateTime($assignment['Start_Date']);
$assignEnd = new DateTime($assignment['End_Date']);
$assignEnd->modify('+1 day'); // Make the assignment period inclusive.
if ($date >= $assignStart && $date < $assignEnd) {
// Determine the time range.
if ($dayOfWeek == 6) {
$timeRange = "09:00 AM - 01:00 PM";
} else {
$timeRange = $singlePeriodStart->format("h:i A") . " - " . $singlePeriodEnd->format("h:i A");
}
// Determine the course type: if current date is the final day of the course, mark it as Exam Day.
$actualEnd = new DateTime($assignment['End_Date']); // Actual last day of the course.
$type = "Regular";
if ($currentDate === $actualEnd->format("Y-m-d")) {
$type = "Exam Day";
}
echo "
| " . htmlspecialchars($currentDate) . " |
" . htmlspecialchars($assignment['Course_Name']) . " |
" . htmlspecialchars($assignment['Time_Slot']) . " |
" . htmlspecialchars($timeRange) . " |
" . htmlspecialchars($type) . " |
";
$foundAssignment = true;
}
}
if (!$foundAssignment) {
echo "
| " . htmlspecialchars($currentDate) . " |
No Class |
- |
- |
Free |
";
}
}
}
*/
/*
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if (isset($_POST["Program_ID"]) && isset($_POST["Group_ID"]) && isset($_POST["Teacher_ID"])) {
// Get and validate parameters
$Program_ID = $_POST['Program_ID'];
$Group_ID = $_POST['Group_ID'];
$Teacher_ID = $_POST['Teacher_ID'];
if ($Program_ID <= 0 || $Group_ID <= 0 || $Teacher_ID <= 0) {
die("Invalid parameters.");
}
// ---------------------------------------------------
// 1. Retrieve group details from manager_group_name
// ---------------------------------------------------
$groupQuery = "SELECT * FROM manager_group_name WHERE Group_ID = ? AND Program_ID = ?";
$stmtGroup = $mysqli->prepare($groupQuery);
if (!$stmtGroup) {
die("Error preparing group query: " . $mysqli->error);
}
$stmtGroup->bind_param("ii", $Group_ID, $Program_ID);
$stmtGroup->execute();
$resultGroup = $stmtGroup->get_result();
$groupData = $resultGroup->fetch_assoc();
$stmtGroup->close();
if (!$groupData) {
die("Group not found.");
}
// Retrieve group details.
$groupTimeSlot = trim($groupData['Time_Slot']); // e.g., "Morning,Afternoon"
$groupStartTime = $groupData['Time_From']; // e.g., "08:30:00"
$groupEndTime = $groupData['Time_To']; // e.g., "15:30:00"
$weekendClass = isset($groupData['Weekend_Class']) ? intval($groupData['Weekend_Class']) : 0; // 0 = no Saturday class
$groupClassDays = trim($groupData['class_days']); // e.g., "Monday,Tuesday,Wednesday,Thursday,Friday"
// ---------------------------------------------------
// 2. Retrieve assignments for this teacher
// ---------------------------------------------------
$query = "SELECT tca.*, c.Course_Name, c.Course_Time
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Program_ID = ?
AND tca.Group_ID = ?
AND tca.Teacher_ID = ?
ORDER BY tca.Assigned_At DESC";
$stmt = $mysqli->prepare($query);
if (!$stmt) {
die("Error preparing assignment query: " . $mysqli->error);
}
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Teacher_ID);
if (!$stmt->execute()) {
die("Error executing assignment query: " . $stmt->error);
}
$result = $stmt->get_result();
$assignments = [];
while ($row = $result->fetch_assoc()) {
$assignments[] = $row;
}
$stmt->close();
// ---------------------------------------------------
// 3. Retrieve holiday events from the Events table
// ---------------------------------------------------
$eventQuery = "SELECT Event_Title, Event_Color, Event_Start, Event_End FROM Events";
$stmtEvent = $mysqli->prepare($eventQuery);
if ($stmtEvent) {
$stmtEvent->execute();
$eventResult = $stmtEvent->get_result();
$holidayMapping = []; // key: date (Y-m-d) => array of event details
while ($event = $eventResult->fetch_assoc()) {
$startEvent = new DateTime($event['Event_Start']);
$endEvent = new DateTime($event['Event_End']);
$endEvent->modify('+1 day'); // include the last day
$interval = new DateInterval('P1D');
$period = new DatePeriod($startEvent, $interval, $endEvent);
foreach ($period as $dt) {
$d = $dt->format('Y-m-d');
$holidayMapping[$d][] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
$stmtEvent->close();
} else {
$holidayMapping = [];
}
// ---------------------------------------------------
// 4. Retrieve retake dates from retake_records table.
// ---------------------------------------------------
$retakeQuery = "SELECT Retake_Date FROM retake_records WHERE Program_ID = ? AND Group_ID = ?";
$stmtRetake = $mysqli->prepare($retakeQuery);
if ($stmtRetake) {
$stmtRetake->bind_param("ii", $Program_ID, $Group_ID);
$stmtRetake->execute();
$retakeResult = $stmtRetake->get_result();
$retakes = [];
while ($r = $retakeResult->fetch_assoc()) {
$retakes[] = $r['Retake_Date'];
}
$stmtRetake->close();
} else {
$retakes = [];
}
// ---------------------------------------------------
// 5. Determine a global date range for the report (based on assignments).
// ---------------------------------------------------
if (!empty($assignments)) {
$globalStart = new DateTime($assignments[0]['Start_Date']);
$globalEnd = new DateTime($assignments[0]['End_Date']);
foreach ($assignments as $assignment) {
$d1 = new DateTime($assignment['Start_Date']);
$d2 = new DateTime($assignment['End_Date']);
if ($d1 < $globalStart) {
$globalStart = $d1;
}
if ($d2 > $globalEnd) {
$globalEnd = $d2;
}
}
$globalEnd->modify('+1 day'); // Make inclusive
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
} else {
$globalStart = new DateTime();
$globalEnd = clone $globalStart;
$globalEnd->modify('+30 days');
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
}
// ---------------------------------------------------
// 6. Generate the day-by-day report.
// ---------------------------------------------------
// Before looping the days, define fallback period variables if necessary.
if (!isset($groupPeriods)) {
$groupPeriods = array_map('trim', explode(',', $groupTimeSlot));
}
// When there's only one period defined in the group, we use the group's overall times:
if (count($groupPeriods) <= 1) {
$singlePeriodStart = new DateTime($groupStartTime);
$singlePeriodEnd = new DateTime($groupEndTime);
}
// If there are at least 2 periods and they are defined elsewhere, they should be defined in your code.
// (In your current snippet, variables like $firstPeriodStr, $firstPeriodStart etc. are not defined if not set.)
foreach ($globalPeriod as $date) {
// Ensure $date is a valid DateTime object.
if (!($date instanceof DateTime)) {
continue;
}
$currentDate = $date->format('Y-m-d');
$dayOfWeek = $date->format('N'); // 1 (Monday) to 7 (Sunday)
$currentDay = $date->format('l'); // Full day name
// --- Custom Days Check ---
if (!empty($groupClassDays)) {
$customDays = array_map('trim', explode(',', $groupClassDays));
if (!in_array($currentDay, $customDays)) {
continue; // Skip this date if not in custom days.
}
} else {
// Default: Skip Sunday and Saturday if weekendClass is not enabled.
if ($dayOfWeek == 7) {
continue;
}
if ($dayOfWeek == 6 && $weekendClass == 0) {
continue;
}
}
// --- Exclusion Check: Holidays and Retakes ---
if (isset($holidayMapping[$currentDate])) {
foreach ($holidayMapping[$currentDate] as $event) {
echo "
| " . htmlspecialchars($currentDate) . " |
" . htmlspecialchars($event['title']) . " |
- |
- |
Holiday |
";
}
continue;
}
if (in_array($currentDate, $retakes)) {
echo "
| " . htmlspecialchars($currentDate) . " |
Retake Day |
- |
- |
Retake |
";
continue;
}
// --- Regular Class Days ---
$foundAssignment = false;
foreach ($assignments as $assignment) {
$assignStart = new DateTime($assignment['Start_Date']);
$assignEnd = new DateTime($assignment['End_Date']);
$assignEnd->modify('+1 day'); // inclusive
if ($date >= $assignStart && $date < $assignEnd) {
// Determine time range.
// If today is Saturday, force the time range to be Morning from 09:00 AM to 01:00 PM.
if ($dayOfWeek == 6) {
$timeRange = "09:00 AM - 01:00 PM";
} else {
// If more than one period is defined, you may have custom logic.
// In this snippet, if only one period exists, use the single period defined.
$timeRange = $singlePeriodStart->format("h:i A") . " - " . $singlePeriodEnd->format("h:i A");
// (If you have custom period logic for multi-period days, implement accordingly.)
}
echo "
| " . htmlspecialchars($currentDate) . " |
" . htmlspecialchars($assignment['Course_Name']) . " |
" . htmlspecialchars($assignment['Time_Slot']) . " |
" . htmlspecialchars($timeRange) . " |
Regular |
";
$foundAssignment = true;
}
}
if (!$foundAssignment) {
// For Free days, output a row in a different color (e.g., light blue).
echo "
| " . htmlspecialchars($currentDate) . " |
No Class |
- |
- |
Free |
";
}
}
}
*/
/*
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
if (isset($_POST["Program_ID"]) && isset($_POST["Group_ID"]) && isset($_POST["Teacher_ID"])) {
// Retrieve POST parameters.
$Program_ID = $_POST['Program_ID'];
$Group_ID = $_POST['Group_ID'];
$Teacher_ID = $_POST['Teacher_ID'];
if ($Program_ID <= 0 || $Group_ID <= 0 || $Teacher_ID <= 0) {
die("Invalid parameters.");
}
// ---------------------------------------------------
// 1. Retrieve group details from manager_group_name
// ---------------------------------------------------
$groupQuery = "SELECT * FROM manager_group_name WHERE Group_ID = ? AND Program_ID = ?";
$stmtGroup = $mysqli->prepare($groupQuery);
if (!$stmtGroup) {
die("Error preparing group query: " . $mysqli->error);
}
$stmtGroup->bind_param("ii", $Group_ID, $Program_ID);
$stmtGroup->execute();
$resultGroup = $stmtGroup->get_result();
$groupData = $resultGroup->fetch_assoc();
$stmtGroup->close();
if (!$groupData) {
die("Group not found.");
}
// Retrieve group details.
$groupTimeSlot = trim($groupData['Time_Slot']); // e.g., "Morning,Afternoon"
$groupStartTime = $groupData['Time_From']; // e.g., "08:30:00"
$groupEndTime = $groupData['Time_To']; // e.g., "15:30:00"
$weekendClass = isset($groupData['Weekend_Class']) ? intval($groupData['Weekend_Class']) : 0; // 0 = no Saturday
// New custom days field (comma‑separated string)
$groupClassDays = trim($groupData['class_days']); // e.g., "Monday,Wednesday,Friday" or default ("Monday,Tuesday,Wednesday,Thursday,Friday")
// ---------------------------------------------------
// 2. Retrieve assignments for this teacher
// ---------------------------------------------------
$query = "SELECT tca.*, c.Course_Name, c.Course_Time
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Program_ID = ?
AND tca.Group_ID = ?
AND tca.Teacher_ID = ?
ORDER BY tca.Assigned_At DESC";
$stmt = $mysqli->prepare($query);
if (!$stmt) {
die("Error preparing assignment query: " . $mysqli->error);
}
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Teacher_ID);
if (!$stmt->execute()) {
die("Error executing assignment query: " . $stmt->error);
}
$result = $stmt->get_result();
$assignments = [];
while ($row = $result->fetch_assoc()) {
$assignments[] = $row;
}
$stmt->close();
// ---------------------------------------------------
// 3. Retrieve holiday events from the Events table
// ---------------------------------------------------
$eventQuery = "SELECT Event_Title, Event_Color, Event_Start, Event_End
FROM Events";
$stmtEvent = $mysqli->prepare($eventQuery);
if ($stmtEvent) {
$stmtEvent->execute();
$eventResult = $stmtEvent->get_result();
$holidayMapping = []; // key: date (Y-m-d) => array of event details
while ($event = $eventResult->fetch_assoc()) {
$startEvent = new DateTime($event['Event_Start']);
$endEvent = new DateTime($event['Event_End']);
$endEvent->modify('+1 day'); // include last day
$interval = new DateInterval('P1D');
$period = new DatePeriod($startEvent, $interval, $endEvent);
foreach ($period as $dt) {
$d = $dt->format('Y-m-d');
$holidayMapping[$d][] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
$stmtEvent->close();
} else {
$holidayMapping = [];
}
// ---------------------------------------------------
// 4. Retrieve retake dates from retake_records table.
// ---------------------------------------------------
$retakeQuery = "SELECT Retake_Date FROM retake_records WHERE Program_ID = ? AND Group_ID = ?";
$stmtRetake = $mysqli->prepare($retakeQuery);
if ($stmtRetake) {
$stmtRetake->bind_param("ii", $Program_ID, $Group_ID);
$stmtRetake->execute();
$retakeResult = $stmtRetake->get_result();
$retakes = [];
while ($r = $retakeResult->fetch_assoc()) {
$retakes[] = $r['Retake_Date'];
}
$stmtRetake->close();
} else {
$retakes = [];
}
// ---------------------------------------------------
// 5. Determine a global date range for the report (based on assignments).
// ---------------------------------------------------
if (!empty($assignments)) {
$globalStart = new DateTime($assignments[0]['Start_Date']);
$globalEnd = new DateTime($assignments[0]['End_Date']);
foreach ($assignments as $assignment) {
$d1 = new DateTime($assignment['Start_Date']);
$d2 = new DateTime($assignment['End_Date']);
if ($d1 < $globalStart) {
$globalStart = $d1;
}
if ($d2 > $globalEnd) {
$globalEnd = $d2;
}
}
$globalEnd->modify('+1 day'); // inclusive
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
} else {
$globalStart = new DateTime();
$globalEnd = clone $globalStart;
$globalEnd->modify('+30 days');
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
}
// ---------------------------------------------------
// 6. Generate the day-by-day report.
// ---------------------------------------------------
// Loop through each day in the global date period.
foreach ($globalPeriod as $date) {
// Ensure $date is a valid DateTime object.
if (!($date instanceof DateTime)) {
continue;
}
$currentDate = $date->format('Y-m-d');
$dayOfWeek = $date->format('N'); // 1 (Monday) to 7 (Sunday)
$currentDay = $date->format('l'); // Full day name, e.g., "Monday"
// --- Custom Days Check ---
// If groupClassDays is defined (non-empty) then we only process this day if it is in that list.
if (!empty($groupClassDays)) {
$customDays = array_map('trim', explode(',', $groupClassDays));
if (!in_array($currentDay, $customDays)) {
continue; // Skip this date
}
} else {
// No custom days defined; use default:
// Always skip Sunday.
if ($dayOfWeek == 7) {
continue;
}
// Skip Saturday if weekendClass is not enabled.
if ($dayOfWeek == 6 && $weekendClass == 0) {
continue;
}
}
// --- Exclusion Check for Holidays and Retakes ---
if (isset($holidayMapping[$currentDate])) {
foreach ($holidayMapping[$currentDate] as $event) {
echo "
| " . htmlspecialchars($currentDate) . " |
" . htmlspecialchars($event['title']) . " |
- |
- |
Holiday |
";
}
continue;
}
if (in_array($currentDate, $retakes)) {
echo "
| " . htmlspecialchars($currentDate) . " |
Retake Day |
- |
- |
Retake |
";
continue;
}
// --- Regular Class Days ---
$foundAssignment = false;
foreach ($assignments as $assignment) {
$assignStart = new DateTime($assignment['Start_Date']);
$assignEnd = new DateTime($assignment['End_Date']);
$assignEnd->modify('+1 day'); // inclusive
if ($date >= $assignStart && $date < $assignEnd) {
// Determine time range based on assignment’s Time_Slot.
$timeRange = "";
if (isset($groupPeriods) && count($groupPeriods) > 1) {
if ($assignment['Time_Slot'] === $firstPeriodStr) {
$timeRange = $firstPeriodStart->format("h:i A") . " - " . $firstPeriodEnd->format("h:i A");
} elseif ($assignment['Time_Slot'] === $secondPeriodStr) {
$timeRange = $secondPeriodStart->format("h:i A") . " - " . $secondPeriodEnd->format("h:i A");
} else {
$timeRange = "N/A";
}
} else {
// Define singlePeriodStart and singlePeriodEnd if not already defined.
// For example, use the group's overall start and end times:
$singlePeriodStart = new DateTime($groupStartTime);
$singlePeriodEnd = new DateTime($groupEndTime);
$timeRange = $singlePeriodStart->format("h:i A") . " - " . $singlePeriodEnd->format("h:i A");
}
echo "
| " . htmlspecialchars($currentDate) . " |
" . htmlspecialchars($assignment['Course_Name']) . " |
" . htmlspecialchars($assignment['Time_Slot']) . " |
" . htmlspecialchars($timeRange) . " |
Regular |
";
$foundAssignment = true;
// If multiple assignments apply, additional rows could be output.
}
}
if (!$foundAssignment) {
// For Free days, output a row in a different color (e.g., light blue).
echo "
| " . htmlspecialchars($currentDate) . " |
No Class |
- |
- |
Free |
";
}
}
}
*/
/*
if (isset($_POST["Program_ID"]) && isset($_POST["Group_ID"])&& isset($_POST["Teacher_ID"])) {
// Get parameters via GET (or fixed for testing)
//$Program_ID = isset($_GET['Program_ID']) ? intval($_GET['Program_ID']) : 1;
//$Group_ID = isset($_GET['Group_ID']) ? intval($_GET['Group_ID']) : 4;
//$Teacher_ID = isset($_GET['Teacher_ID']) ? intval($_GET['Teacher_ID']) : 250598;
$Program_ID = $_POST['Program_ID'];
$Group_ID = $_POST['Group_ID'];
$Teacher_ID = $_POST['Teacher_ID'];
if ($Program_ID <= 0 || $Group_ID <= 0 || $Teacher_ID <= 0) {
die("Invalid parameters.");
}
// ---------------------------------------------------
// 1. Retrieve group details from manager_group_name
// ---------------------------------------------------
$groupQuery = "SELECT * FROM manager_group_name WHERE Group_ID = ? AND Program_ID = ?";
$stmtGroup = $mysqli->prepare($groupQuery);
if (!$stmtGroup) {
die("Error preparing group query: " . $mysqli->error);
}
$stmtGroup->bind_param("ii", $Group_ID, $Program_ID);
$stmtGroup->execute();
$resultGroup = $stmtGroup->get_result();
$groupData = $resultGroup->fetch_assoc();
$stmtGroup->close();
if (!$groupData) {
die("Group not found.");
}
// Expected columns: Time_Slot, Time_From, Time_To, Weekend_Class
$groupTimeSlot = trim($groupData['Time_Slot']); // e.g., "Morning,Afternoon" or "Morning"
$groupStartTime = $groupData['Time_From']; // e.g., "08:30:00"
$groupEndTime = $groupData['Time_To']; // e.g., "15:30:00"
$weekendClass = isset($groupData['Weekend_Class']) ? intval($groupData['Weekend_Class']) : 0; // 0 = no Saturday class
// Split the group's time slot field into periods.
$groupPeriods = array_map('trim', explode(',', $groupTimeSlot));
if (count($groupPeriods) > 1) {
$firstPeriodStr = $groupPeriods[0]; // e.g., "Morning"
$secondPeriodStr = $groupPeriods[1]; // e.g., "Afternoon"
$groupStartDT = new DateTime($groupStartTime);
$groupEndDT = new DateTime($groupEndTime);
// For the first period, assume duration of 3 hours from group start.
$firstPeriodStart = clone $groupStartDT;
$firstPeriodEnd = clone $groupStartDT;
$firstPeriodEnd->modify('+3 hours'); // e.g., 08:30 -> 11:30
// For the second period, assume duration of 3 hours before group end.
$secondPeriodEnd = clone $groupEndDT;
$secondPeriodStart = clone $groupEndDT;
$secondPeriodStart->modify('-3 hours'); // e.g., 15:30 -> 12:30
} else {
$singlePeriod = $groupPeriods[0]; // e.g., "Morning"
$singlePeriodStart = new DateTime($groupStartTime);
$singlePeriodEnd = new DateTime($groupEndTime);
}
// ---------------------------------------------------
// 2. Retrieve assignments for this teacher
// ---------------------------------------------------
$query = "SELECT tca.*, c.Course_Name, c.Course_Time
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Program_ID = ?
AND tca.Group_ID = ?
AND tca.Teacher_ID = ?
ORDER BY tca.Assigned_At DESC";
$stmt = $mysqli->prepare($query);
if (!$stmt) {
die("Error preparing assignment query: " . $mysqli->error);
}
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Teacher_ID);
if (!$stmt->execute()) {
die("Error executing assignment query: " . $stmt->error);
}
$result = $stmt->get_result();
$assignments = [];
while ($row = $result->fetch_assoc()) {
$assignments[] = $row;
}
$stmt->close();
// ---------------------------------------------------
// 3. Retrieve holiday events from the Events table
// ---------------------------------------------------
$eventQuery = "SELECT Event_Title, Event_Color, Event_Start, Event_End
FROM Events";
$stmtEvent = $mysqli->prepare($eventQuery);
if ($stmtEvent) {
//$stmtEvent->bind_param("i", $Program_ID);
$stmtEvent->execute();
$eventResult = $stmtEvent->get_result();
$holidayMapping = []; // key: date (Y-m-d) => array of event details
while ($event = $eventResult->fetch_assoc()) {
// Generate dates from Event_Start to Event_End (inclusive)
$startEvent = new DateTime($event['Event_Start']);
$endEvent = new DateTime($event['Event_End']);
$endEvent->modify('+1 day'); // include the last day
$interval = new DateInterval('P1D');
$period = new DatePeriod($startEvent, $interval, $endEvent);
foreach ($period as $dt) {
$d = $dt->format('Y-m-d');
$holidayMapping[$d][] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
$stmtEvent->close();
} else {
$holidayMapping = [];
}
// ---------------------------------------------------
// 4. Retrieve retake dates from retake_records table.
// ---------------------------------------------------
$retakeQuery = "SELECT Retake_Date FROM retake_records WHERE Program_ID = ? AND Group_ID = ?";
$stmtRetake = $mysqli->prepare($retakeQuery);
if ($stmtRetake) {
$stmtRetake->bind_param("ii", $Program_ID, $Group_ID);
$stmtRetake->execute();
$retakeResult = $stmtRetake->get_result();
$retakes = [];
while ($r = $retakeResult->fetch_assoc()) {
$retakes[] = $r['Retake_Date'];
}
$stmtRetake->close();
} else {
$retakes = [];
}
// ---------------------------------------------------
// 5. Determine a global date range for the report (based on assignments).
// ---------------------------------------------------
if (!empty($assignments)) {
$globalStart = new DateTime($assignments[0]['Start_Date']);
$globalEnd = new DateTime($assignments[0]['End_Date']);
foreach ($assignments as $assignment) {
$d1 = new DateTime($assignment['Start_Date']);
$d2 = new DateTime($assignment['End_Date']);
if ($d1 < $globalStart) {
$globalStart = $d1;
}
if ($d2 > $globalEnd) {
$globalEnd = $d2;
}
}
$globalEnd->modify('+1 day'); // Make inclusive
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
} else {
$globalStart = new DateTime();
$globalEnd = clone $globalStart;
$globalEnd->modify('+30 days');
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
}
// ---------------------------------------------------
// 6. Generate the day-by-day report.
// ---------------------------------------------------
//echo "
Daily Course Schedule Report for Teacher $Teacher_ID, Group $Group_ID, Program $Program_ID
";
//echo "
";
//echo "
// | Date |
// Course Name / Status |
// Time Slot |
// Time |
// Type |
//
";
foreach ($globalPeriod as $date) {
$currentDate = $date->format('Y-m-d');
$dayOfWeek = $date->format('N'); // ISO-8601: 6 = Saturday, 7 = Sunday
// --- Weekend Check ---
if ($dayOfWeek == 7) { // Always skip Sunday.
continue;
}
if ($dayOfWeek == 6 && $weekendClass == 0) {
continue;
}
// --- Exclusion Check: If current date is a holiday or retake, output those rows and skip class row.
if (isset($holidayMapping[$currentDate])) {
foreach ($holidayMapping[$currentDate] as $event) {
echo "
| " . htmlspecialchars($currentDate) . " |
" . htmlspecialchars($event['title']) . " |
- |
- |
Holiday |
";
}
// Skip regular class row for this date.
continue;
}
if (in_array($currentDate, $retakes)) {
echo "
| " . htmlspecialchars($currentDate) . " |
Retake Day |
- |
- |
Retake |
";
continue;
}
// --- Regular Class Days ---
$foundAssignment = false;
foreach ($assignments as $assignment) {
$assignStart = new DateTime($assignment['Start_Date']);
$assignEnd = new DateTime($assignment['End_Date']);
$assignEnd->modify('+1 day'); // Make inclusive
if ($date >= $assignStart && $date < $assignEnd) {
// Determine time range based on assignment's Time_Slot.
$timeRange = "";
if (count($groupPeriods) > 1) {
if ($assignment['Time_Slot'] === $firstPeriodStr) {
$timeRange = $firstPeriodStart->format("h:i A") . " - " . $firstPeriodEnd->format("h:i A");
} elseif ($assignment['Time_Slot'] === $secondPeriodStr) {
$timeRange = $secondPeriodStart->format("h:i A") . " - " . $secondPeriodEnd->format("h:i A");
} else {
$timeRange = "N/A";
}
} else {
$timeRange = $singlePeriodStart->format("h:i A") . " - " . $singlePeriodEnd->format("h:i A");
}
echo "
| " . htmlspecialchars($currentDate) . " |
" . htmlspecialchars($assignment['Course_Name']) . " |
" . htmlspecialchars($assignment['Time_Slot']) . " |
" . htmlspecialchars($timeRange) . " |
Regular |
";
$foundAssignment = true;
// If multiple assignments apply, additional rows could be output.
}
}
if (!$foundAssignment) {
echo "
| " . htmlspecialchars($currentDate) . " |
No Class |
- |
- |
Free |
";
}
}
//echo "
";
}
*/
?>