/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/scheduleteacherreport.php (28788B)
Schedule Teacher Report
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 ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['Teacher_ID'])) {
$teacherID = (int) $_POST['Teacher_ID'];
$programID = (!empty($_POST['Program_ID']) && $_POST['Program_ID'] !== "all") ? (int) $_POST['Program_ID'] : null;
// 1. Load all holidays relevant to this teacher’s scheduled sessions
$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');
}
}
// 2. Retake Records — pulled from all groups this teacher has
$retakes = [];
$stmt = $mysqli->prepare("
SELECT rr.Type, rr.Retake_Date
FROM retake_records rr
JOIN manager_group_name g ON rr.Group_ID = g.Group_ID
JOIN schedule_course_for_group scg ON rr.Group_ID = scg.Group_ID
JOIN teacher_course_assignments tca ON tca.Schedule_ID = scg.Schedule_ID
WHERE tca.Teacher_ID = ?
");
$stmt->bind_param("i", $teacherID);
$stmt->execute();
$stmt->bind_result($type, $retakeDate);
while ($stmt->fetch()) {
$retakes[$retakeDate] = $type;
}
$stmt->close();
// 3. Exam Dates
$examDates = [];
$query = "
SELECT scg.Course_ID, MAX(scg.End_Date)
FROM schedule_course_for_group scg
JOIN teacher_course_assignments tca ON scg.Schedule_ID = tca.Schedule_ID
WHERE tca.Teacher_ID = ?
";
if ($programID) {
$query .= " AND scg.Program_ID = ?";
}
$query .= " GROUP BY scg.Course_ID";
$stmt = $mysqli->prepare($query);
if ($programID) {
$stmt->bind_param("ii", $teacherID, $programID);
} else {
$stmt->bind_param("i", $teacherID);
}
$stmt->execute();
$stmt->bind_result($courseID, $examDate);
while ($stmt->fetch()) {
$examDates[$courseID] = $examDate;
}
$stmt->close();
// 4. Sessions Assigned to Teacher
$scheduleRows = [];
$query = "
SELECT
scg.Schedule_ID,
scg.Course_ID,
scg.Course_Name,
scg.Reserve_Course,
scg.Start_Date,
scg.End_Date,
gsm.Time_Slot,
gsm.Time_From,
gsm.Time_To,
g.Group_Name
FROM schedule_course_for_group AS scg
JOIN group_slot_mapping AS gsm ON scg.Group_Slot_ID = gsm.Group_Slot_ID
JOIN teacher_course_assignments AS tca ON scg.Schedule_ID = tca.Schedule_ID
JOIN manager_group_name AS g ON scg.Group_ID = g.Group_ID
WHERE tca.Teacher_ID = ?
";
if ($programID) {
$query .= " AND scg.Program_ID = ?";
}
$query .= " ORDER BY scg.Start_Date, scg.Start_Time";
$stmt = $mysqli->prepare($query);
if ($programID) {
$stmt->bind_param("ii", $teacherID, $programID);
} else {
$stmt->bind_param("i", $teacherID);
}
$stmt->execute();
$stmt->bind_result(
$scheduleID,
$courseID,
$courseName,
$reserveCourse,
$startDate,
$endDate,
$slotLabel,
$slotFrom,
$slotTo,
$groupName
);
while ($stmt->fetch()) {
$scheduleRows[] = [
'schedule_id' => $scheduleID,
'course_id' => $courseID,
'course_name' => $courseName,
'reserve' => $reserveCourse,
'start_date' => $startDate,
'end_date' => $endDate,
'slot' => $slotLabel,
'from' => $slotFrom,
'to' => $slotTo,
'teacher' => '-', // you can add name if needed
'group' => $groupName
];
}
$stmt->close();
// 5. Group by Date
$byDate = [];
foreach ($scheduleRows as $r) {
$period = new DatePeriod(
new DateTime($r['start_date']),
new DateInterval('P1D'),
(new DateTime($r['end_date']))->modify('+1 day')
);
foreach ($period as $dt) {
$current = $dt->format('Y-m-d');
$byDate[$current][] = $r;
}
}
// 6. Merge All Dates
$allDates = array_unique(array_merge(array_keys($byDate), array_keys($holidays), array_keys($retakes)));
sort($allDates);
// 7. Render Table
echo "";
echo "
| Date |
Time Slot |
Course |
Group |
Time |
Type |
";
foreach ($allDates as $date) {
$dayName = date('l', strtotime($date));
$isHoliday = isset($holidays[$date]);
$isRetake = isset($retakes[$date]);
if ($isHoliday && !isset($byDate[$date])) {
$h = $holidays[$date];
echo "
| ($dayName) $date |
" . htmlspecialchars($h['title']) . " |
Holiday |
";
continue;
}
if ($isRetake && !isset($byDate[$date])) {
echo "
| ($dayName) $date |
Retake Session |
" . htmlspecialchars($retakes[$date]) . " |
";
continue;
}
foreach ($byDate[$date] as $r) {
$isExam = isset($examDates[$r['course_id']]) && $examDates[$r['course_id']] === $date;
$type = $isExam ? 'Exam Day' : 'Regular';
$style = $isExam ? "style='background-color:#FFCCCC; font-weight:bold;'" : "";
$timeRange = date("h:i A", strtotime($r['from'])) . " – " . date("h:i A", strtotime($r['to']));
echo "
| ($dayName) $date |
{$r['slot']} |
" . htmlspecialchars($r['course_name']) . " |
" . htmlspecialchars($r['group']) . " |
{$timeRange} |
{$type} |
";
}
}
echo "
";
}
/**
if (isset($_POST["Teacher_ID"])) {
$Teacher_ID = intval($_POST['Teacher_ID']);
$Program_ID_Filter = (!empty($_POST['Program_ID']) && $_POST['Program_ID'] !== "all") ? intval($_POST['Program_ID']) : null;
// 1. Assignments
$whereAssignments = "WHERE tca.Teacher_ID = ?";
$params = ["i", $Teacher_ID];
if ($Program_ID_Filter) {
$whereAssignments .= " AND tca.Program_ID = ?";
$params[0] .= "i";
$params[] = $Program_ID_Filter;
}
$query = "SELECT tca.*, c.Course_Name, scg.Group_ID, scg.Start_Date, scg.End_Date, scg.Time_Slot, scg.Start_Time, scg.End_Time, g.Group_Name, g.Weekend_Class, g.class_days
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
JOIN schedule_course_for_group scg ON tca.Schedule_ID = scg.Schedule_ID
JOIN manager_group_name g ON scg.Group_ID = g.Group_ID
$whereAssignments";
$stmt = $mysqli->prepare($query);
if (!$stmt) {
die("Prepare failed (Assignments): " . $mysqli->error . "\nQuery: $query");
}
$stmt->bind_param(...$params);
$stmt->execute();
$assignments = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 2. Replacements
$whereReplacements = "WHERE tra.Replacing_Teacher_ID = ?";
$paramsRep = ["i", $Teacher_ID];
if ($Program_ID_Filter) {
$whereReplacements .= " AND c.Program_ID = ?";
$paramsRep[0] .= "i";
$paramsRep[] = $Program_ID_Filter;
}
$query = "SELECT tra.*, c.Course_Name, scg.Group_ID, scg.Start_Date AS Replacement_From, scg.End_Date AS Replacement_To, scg.Time_Slot, scg.Start_Time, scg.End_Time, g.Group_Name, g.Weekend_Class, g.class_days
FROM teacher_replacement_assignments tra
JOIN schedule_course_for_group scg ON tra.Schedule_ID = scg.Schedule_ID
JOIN Courses c ON scg.Course_ID = c.Course_ID
JOIN manager_group_name g ON scg.Group_ID = g.Group_ID
$whereReplacements";
$stmt = $mysqli->prepare($query);
if (!$stmt) {
die("Prepare failed (Replacements): " . $mysqli->error . "\nQuery: $query");
}
$stmt->bind_param(...$paramsRep);
$stmt->execute();
$replacements = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 3. Absences
$stmt = $mysqli->prepare("SELECT Absence_From, Absence_To FROM teacher_absences WHERE Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$absences = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 4. Retake Dates
$groupIDs = array_unique(array_column($assignments, 'Group_ID'));
$retakeMap = [];
foreach ($groupIDs as $groupID) {
$stmt = $mysqli->prepare("
SELECT rr.Retake_Date, tsp.Time_Slot, g.Group_Name
FROM retake_records rr
JOIN time_slot_programs tsp ON rr.time_slot_programs_ID = tsp.time_slot_programs_ID
JOIN manager_group_name g ON rr.Group_ID = g.Group_ID
WHERE rr.Group_ID = ?
");
$stmt->bind_param("i", $groupID);
$stmt->execute();
$res = $stmt->get_result();
while ($r = $res->fetch_assoc()) {
$retakeMap[$r['Retake_Date']][] = [
'Time_Slot' => $r['Time_Slot'],
'Group_Name' => $r['Group_Name']
];
}
$stmt->close();
}
// 5. Holidays
$holidayMap = [];
$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 $dt) {
$holidayMap[$dt->format('Y-m-d')] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
// 6. Build date range from real dates
$dates = array_merge(
array_column($assignments, 'Start_Date'),
array_column($replacements, 'Replacement_From'),
array_column($replacements, 'Replacement_To'),
array_keys($retakeMap)
);
if (empty($dates)) {
echo "No schedule found for this teacher.
";
exit;
}
$start = new DateTime(min($dates));
$end = (new DateTime(max($dates)))->modify('+1 day');
$range = new DatePeriod($start, new DateInterval('P1D'), $end);
// 7. Display report
echo "";
echo "
| Date | Course | Group | Time Slot | Time | Type |
|---|
";
foreach ($range as $date) {
$current = $date->format('Y-m-d');
$day = $date->format('l');
$shown = false;
// Holiday
if (isset($holidayMap[$current])) {
echo "| $current | {$holidayMap[$current]['title']} | - | - | - | Holiday |
";
continue;
}
// Retake
if (isset($retakeMap[$current])) {
foreach ($retakeMap[$current] as $retake) {
$slotLabel = format_time_slot_label($retake['Time_Slot'], $day);
$time = ($day === 'Saturday') ? '09:00 AM - 01:00 PM' : get_group_slot_time($groupIDs[0], $retake['Time_Slot'], $mysqli);
$groupName = $retake['Group_Name'];
echo "
| $current |
Retake |
$groupName |
$slotLabel |
$time |
Retake |
";
}
continue;
}
// Absences
foreach ($absences as $abs) {
if ($current >= $abs['Absence_From'] && $current <= $abs['Absence_To']) {
echo "| $current | Teacher Absent | - | - | - | Absent |
";
$shown = true;
break;
}
}
if ($shown) continue;
// Replacements
foreach ($replacements as $r) {
if ($current >= $r['Replacement_From'] && $current <= $r['Replacement_To']) {
$time = format_time_slot($r['Time_Slot'], $r['Start_Time'], $r['End_Time'], $day);
$slotLabel = format_time_slot_label($r['Time_Slot'], $day);
echo "| $current | {$r['Course_Name']} | {$r['Group_Name']} | {$slotLabel} | {$time} | Replacement |
";
$shown = true;
}
}
if ($shown) continue;
// Regular Assignments
foreach ($assignments as $a) {
if ($current == $a['Start_Date']) {
$type = ($current == $a['End_Date']) ? 'Exam Day' : 'Regular';
$bg = ($type === 'Exam Day') ? "style='background-color:#ffeeba'" : "";
$time = format_time_slot($a['Time_Slot'], $a['Start_Time'], $a['End_Time'], $day);
$slotLabel = format_time_slot_label($a['Time_Slot'], $day);
echo "| $current | {$a['Course_Name']} | {$a['Group_Name']} | {$slotLabel} | {$time} | {$type} |
";
$shown = true;
}
}
// Free day
if (!$shown) {
echo "| $current | No Class | - | - | - | Free |
";
}
}
echo "