/home/techb158/workloadmatch.com/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/workloadmatch.com/Manager/complete_schedule_report_by_teacher.php (19312B)
prepare("
SELECT tca.*, c.Course_Name, cg.Group_ID, cg.Start_Time, cg.End_Time, g.Group_Name
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
JOIN Manager_Group_Name g ON g.Group_ID = cg.Group_ID
WHERE tca.Teacher_ID = ?
");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$assignments = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 2. Get replacement assignments where this teacher is replacing someone else
$stmt = $mysqli->prepare("
SELECT tra.*, c.Course_Name, cg.Time_Slot, cg.Group_ID, cg.Start_Time, cg.End_Time, g.Group_Name
FROM Teacher_Replacement_Assignments tra
JOIN Course_Group_Schedule cg ON tra.Schedule_ID = cg.Schedule_ID
JOIN Courses c ON cg.Course_ID = c.Course_ID
JOIN Manager_Group_Name g ON g.Group_ID = cg.Group_ID
WHERE tra.Replacing_Teacher_ID = ?
");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$replacements = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 3. Get teacher 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. Get retake dates (only for the groups teacher teaches)
$groupIDs = array_unique(array_column($assignments, 'Group_ID'));
$retakeDates = [];
if (!empty($groupIDs)) {
$in = implode(',', array_fill(0, count($groupIDs), '?'));
$types = str_repeat('i', count($groupIDs));
$stmt = $mysqli->prepare("SELECT Retake_Date FROM Retake_Records WHERE Group_ID IN ($in)");
$stmt->bind_param($types, ...$groupIDs);
$stmt->execute();
$res = $stmt->get_result();
while ($r = $res->fetch_assoc()) {
$retakeDates[] = $r['Retake_Date'];
}
$stmt->close();
}
// 5. Get 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');
$interval = new DateInterval('P1D');
foreach (new DatePeriod($start, $interval, $end) as $dt) {
$holidayMap[$dt->format('Y-m-d')] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
// 6. Build the date range
$dates = array_merge(
array_column($assignments, 'Start_Date'),
array_column($assignments, 'End_Date'),
array_column($replacements, 'Replacement_From'),
array_column($replacements, 'Replacement_To')
);
$minDate = !empty($dates) ? min($dates) : date('Y-m-d');
$maxDate = !empty($dates) ? max($dates) : date('Y-m-d', strtotime('+30 days'));
$start = new DateTime($minDate);
$end = (new DateTime($maxDate))->modify('+1 day');
$period = new DatePeriod($start, new DateInterval('P1D'), $end);
echo "
| Date | Course | Group | Time Slot | Time | Type |
|---|
";
foreach ($period as $date) {
$current = $date->format('Y-m-d');
$dayShown = false;
// Holiday
if (isset($holidayMap[$current])) {
echo "
| {$current} | {$holidayMap[$current]['title']} | Holiday |
";
continue;
}
// Retake
if (in_array($current, $retakeDates)) {
echo "
| {$current} | Retake Day | Retake |
";
continue;
}
// Absence
foreach ($absences as $abs) {
if ($current >= $abs['Absence_From'] && $current <= $abs['Absence_To']) {
echo "
| {$current} | Teacher Absent | Absent |
";
$dayShown = true;
break;
}
}
if ($dayShown) continue;
// Replacement
foreach ($replacements as $r) {
if ($current >= $r['Replacement_From'] && $current <= $r['Replacement_To']) {
$time = date('h:i A', strtotime($r['Start_Time'])) . ' - ' . date('h:i A', strtotime($r['End_Time']));
echo "
| {$current} | {$r['Course_Name']} | {$r['Group_Name']} |
{$r['Time_Slot']} | {$time} | Replacement |
";
$dayShown = true;
}
}
if ($dayShown) continue;
// Regular assignments
foreach ($assignments as $a) {
if ($current >= $a['Start_Date'] && $current <= $a['End_Date']) {
$type = ($current === $a['End_Date']) ? 'Exam Day' : 'Regular';
$bg = ($type === 'Exam Day') ? "style='background-color:#ffeeba'" : "";
$time = date('h:i A', strtotime($a['Start_Time'])) . ' - ' . date('h:i A', strtotime($a['End_Time']));
echo "
| {$current} | {$a['Course_Name']} | {$a['Group_Name']} |
{$a['Time_Slot']} | {$time} | {$type} |
";
$dayShown = true;
}
}
if (!$dayShown) {
echo "
| {$current} | No Class | Free |
";
}
}
echo "
";
//} else {
// echo "
Please select a teacher to view their schedule.
";
//}
*/
/*
//if (isset($_POST["Teacher_ID"])) {
// $Teacher_ID = $_POST['Teacher_ID'];
// 1. Get regular assignments for the teacher
$assignments = [];
$stmt = $mysqli->prepare("SELECT tca.*, c.Course_Name, cg.Group_ID, cg.Start_Time, cg.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.Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$assignments = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 2. Get replacement assignments
$stmt = $mysqli->prepare("SELECT tra.*, c.Course_Name, cg.Time_Slot, cg.Group_ID, cg.Start_Time, cg.End_Time
FROM Teacher_Replacement_Assignments tra
JOIN Course_Group_Schedule cg ON tra.Schedule_ID = cg.Schedule_ID
JOIN Courses c ON cg.Course_ID = c.Course_ID
WHERE tra.Replacing_Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$replacements = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 3. Get teacher 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. Get filtered retake dates based on groups assigned to the teacher
$groupPairs = [];
$stmt = $mysqli->prepare("SELECT DISTINCT Program_ID, Group_ID FROM Teacher_Course_Assignments WHERE Teacher_ID = ?");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$res = $stmt->get_result();
while ($row = $res->fetch_assoc()) {
$groupPairs[] = $row;
}
$stmt->close();
$retakeDates = [];
foreach ($groupPairs as $pair) {
$stmt = $mysqli->prepare("SELECT Retake_Date FROM Retake_Records WHERE Program_ID = ? AND Group_ID = ?");
$stmt->bind_param("ii", $pair['Program_ID'], $pair['Group_ID']);
$stmt->execute();
$result = $stmt->get_result();
while ($r = $result->fetch_assoc()) {
$retakeDates[] = $r['Retake_Date'];
}
$stmt->close();
}
// 5. Get 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. Determine schedule date range
$dates = [];
foreach ($assignments as $a) {
$dates[] = $a['Start_Date'];
$dates[] = $a['End_Date'];
}
foreach ($replacements as $r) {
$dates[] = $r['Replacement_From'];
$dates[] = $r['Replacement_To'];
}
$minDate = !empty($dates) ? min($dates) : date('Y-m-d');
$maxDate = !empty($dates) ? max($dates) : date('Y-m-d', strtotime('+30 days'));
$start = new DateTime($minDate);
$end = (new DateTime($maxDate))->modify('+1 day');
$period = new DatePeriod($start, new DateInterval('P1D'), $end);
echo "
| Date | Course | Group | Time Slot | Time | Type |
|---|
";
foreach ($period as $date) {
$current = $date->format('Y-m-d');
$dayShown = false;
// Holidays
if (isset($holidayMap[$current])) {
echo "
| {$current} | {$holidayMap[$current]['title']} | Holiday |
";
continue;
}
// Retake
if (in_array($current, $retakeDates)) {
echo "
| {$current} | Retake Day | Retake |
";
continue;
}
// Absence
foreach ($absences as $abs) {
if ($current >= $abs['Absence_From'] && $current <= $abs['Absence_To']) {
echo "
| {$current} | Teacher Absent | Absent |
";
$dayShown = true;
break;
}
}
if ($dayShown) continue;
// Replacements
foreach ($replacements as $r) {
if ($current >= $r['Replacement_From'] && $current <= $r['Replacement_To']) {
$time = date('h:i A', strtotime($r['Start_Time'])) . ' - ' . date('h:i A', strtotime($r['End_Time']));
echo "
| {$current} | {$r['Course_Name']} | Group {$r['Group_ID']} |
{$r['Time_Slot']} | {$time} | Replacement |
";
$dayShown = true;
}
}
if ($dayShown) continue;
// Regular assignments
foreach ($assignments as $a) {
if ($current >= $a['Start_Date'] && $current <= $a['End_Date']) {
$type = ($current === $a['End_Date']) ? 'Exam Day' : 'Regular';
$rowColor = ($type === 'Exam Day') ? "style='background-color:#ffeeba'" : "";
$time = date('h:i A', strtotime($a['Start_Time'])) . ' - ' . date('h:i A', strtotime($a['End_Time']));
echo "
| {$current} | {$a['Course_Name']} | Group {$a['Group_ID']} |
{$a['Time_Slot']} | {$time} | {$type} |
";
$dayShown = true;
}
}
// Free
if (!$dayShown) {
echo "
| {$current} | No Class | Free |
";
}
}
echo "
";
//} else {
// echo "
Please select a teacher to view their schedule.
";
//}
*/
/*
//if (isset($_POST["Teacher_ID"])) {
$Teacher_ID = $_POST['Teacher_ID'];
// 1. Get regular assignments for the teacher
$assignments = [];
$stmt = $mysqli->prepare("
SELECT tca.*, c.Course_Name, cg.Group_ID, cg.Start_Time, cg.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.Teacher_ID = ?
");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$assignments = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 2. Get replacement assignments where this teacher is replacing someone else
$stmt = $mysqli->prepare("
SELECT tra.*, c.Course_Name, cg.Time_Slot, cg.Group_ID, cg.Start_Time, cg.End_Time
FROM Teacher_Replacement_Assignments tra
JOIN Course_Group_Schedule cg ON tra.Schedule_ID = cg.Schedule_ID
JOIN Courses c ON cg.Course_ID = c.Course_ID
WHERE tra.Replacing_Teacher_ID = ?
");
$stmt->bind_param("i", $Teacher_ID);
$stmt->execute();
$replacements = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 3. Get teacher 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. Get retake dates
$retakeDates = [];
$res = $mysqli->query("SELECT Retake_Date FROM Retake_Records");
while ($r = $res->fetch_assoc()) {
$retakeDates[] = $r['Retake_Date'];
}
// 5. Get 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');
$interval = new DateInterval('P1D');
foreach (new DatePeriod($start, $interval, $end) as $dt) {
$holidayMap[$dt->format('Y-m-d')] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
// 6. Build the full date range
$dates = [];
foreach ($assignments as $a) {
$dates[] = $a['Start_Date'];
$dates[] = $a['End_Date'];
}
foreach ($replacements as $r) {
$dates[] = $r['Replacement_From'];
$dates[] = $r['Replacement_To'];
}
$minDate = !empty($dates) ? min($dates) : date('Y-m-d');
$maxDate = !empty($dates) ? max($dates) : date('Y-m-d', strtotime('+30 days'));
$start = new DateTime($minDate);
$end = (new DateTime($maxDate))->modify('+1 day');
$period = new DatePeriod($start, new DateInterval('P1D'), $end);
echo "
| Date | Course | Group | Time Slot | Time | Type |
|---|
";
foreach ($period as $date) {
$current = $date->format('Y-m-d');
$dayShown = false;
// Holidays
if (isset($holidayMap[$current])) {
echo "
| {$current} | {$holidayMap[$current]['title']} | Holiday |
";
continue;
}
// Retake
if (in_array($current, $retakeDates)) {
echo "
| {$current} | Retake Day | Retake |
";
continue;
}
// Absence (marked red)
foreach ($absences as $abs) {
if ($current >= $abs['Absence_From'] && $current <= $abs['Absence_To']) {
echo "
| {$current} | Teacher Absent | Absent |
";
$dayShown = true;
break;
}
}
if ($dayShown) continue;
// Replacements
foreach ($replacements as $r) {
if ($current >= $r['Replacement_From'] && $current <= $r['Replacement_To']) {
$time = date('h:i A', strtotime($r['Start_Time'])) . ' - ' . date('h:i A', strtotime($r['End_Time']));
echo "
| {$current} | {$r['Course_Name']} | Group {$r['Group_ID']} |
{$r['Time_Slot']} | {$time} | Replacement |
";
$dayShown = true;
}
}
if ($dayShown) continue;
// Regular assignments
foreach ($assignments as $a) {
if ($current >= $a['Start_Date'] && $current <= $a['End_Date']) {
$type = ($current === $a['End_Date']) ? 'Exam Day' : 'Regular';
$time = date('h:i A', strtotime($a['Start_Time'])) . ' - ' . date('h:i A', strtotime($a['End_Time']));
echo "
| {$current} | {$a['Course_Name']} | Group {$a['Group_ID']} |
{$a['Time_Slot']} | {$time} | {$type} |
";
$dayShown = true;
}
}
// Free day
if (!$dayShown) {
echo "
| {$current} | No Class | Free |
";
}
}
echo "
";
//} else {
// echo "
Please select a teacher to view their schedule.
";
//}
*/
?>