/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/scheduleteacherbygroupreport.php (37418B)
Schedule Teacher Report
prepare("SELECT * FROM manager_group_name WHERE Group_ID = ? AND Program_ID = ?");
$stmtGroup->bind_param("ii", $groupID, $programID);
$stmtGroup->execute();
$groupData = $stmtGroup->get_result()->fetch_assoc();
$stmtGroup->close();
if (!$groupData) die("Group not found.");
$groupName = htmlspecialchars($groupData['Group_Name']);
// 1. Load all Holidays
$holidays = [];
$res = $mysqli->query("
SELECT Event_Title, Event_Start, Event_End, Event_Color
FROM Events
WHERE Calendar_Year IN (
SELECT DISTINCT YEAR(Start_Date)
FROM schedule_course_for_group
WHERE Program_ID = $programID
AND Group_ID = $groupID
)
");
while ($row = $res->fetch_assoc()) {
$start = new DateTime($row['Event_Start']);
$end = new DateTime($row['Event_End']);
while ($start <= $end) {
$d = $start->format('Y-m-d');
$holidays[$d] = [
'title' => $row['Event_Title'],
'color' => $row['Event_Color'] ?: '#FFD700'
];
$start->modify('+1 day');
}
}
// 2. Load Retake Records
$retakes = [];
$stmt = $mysqli->prepare("
SELECT Type, Retake_Date
FROM retake_records
WHERE Program_ID = ? AND Group_ID = ?
");
$stmt->bind_param('ii', $programID, $groupID);
$stmt->execute();
$stmt->bind_result($rType, $rDate);
while ($stmt->fetch()) {
$retakes[$rDate] = $rType;
}
$stmt->close();
// 3. Get Exam Dates per Course
$examDates = [];
$stmt = $mysqli->prepare("
SELECT Course_ID, MAX(End_Date)
FROM schedule_course_for_group
WHERE Program_ID = ? AND Group_ID = ?
GROUP BY Course_ID
");
$stmt->bind_param('ii', $programID, $groupID);
$stmt->execute();
$stmt->bind_result($eCourseID, $eDate);
while ($stmt->fetch()) {
$examDates[$eCourseID] = $eDate;
}
$stmt->close();
// 4. Load Scheduled Sessions WITH Teacher Info
$scheduleRows = [];
$stmt = $mysqli->prepare("
SELECT
scf.Schedule_ID,
scf.Course_ID,
scf.Course_Name,
scf.Reserve_Course,
scf.Start_Date,
scf.End_Date,
gsm.Time_Slot,
gsm.Time_From,
gsm.Time_To,
tp.First_Name,
tp.Last_Name
FROM schedule_course_for_group AS scf
JOIN group_slot_mapping AS gsm
ON scf.Group_Slot_ID = gsm.Group_Slot_ID
LEFT JOIN teacher_course_assignments AS tca
ON scf.Schedule_ID = tca.Schedule_ID
LEFT JOIN teacher_profile AS tp
ON tca.Teacher_ID = tp.Teacher_ID
WHERE scf.Program_ID = ?
AND scf.Group_ID = ?
ORDER BY scf.Start_Date, scf.Start_Time
");
$stmt->bind_param("ii", $programID, $groupID);
$stmt->execute();
$stmt->bind_result(
$scheduleID,
$courseID,
$courseName,
$reserveCourse,
$startDate,
$endDate,
$slotLabel,
$slotFrom,
$slotTo,
$firstName,
$lastName
);
while ($stmt->fetch()) {
//echo $firstName .'"-"'. $lastName;
$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' => trim("$firstName $lastName")
];
}
$stmt->close();
// 5. Group sessions 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. Combine all possible dates
$allDates = array_unique(
array_merge(
array_keys($byDate),
array_keys($holidays),
array_keys($retakes)
)
);
sort($allDates, SORT_STRING);
// 7. Render table
echo "
";
echo "";
echo "Schedule for Group: $groupName
Date
Time Slot
Course
Teacher
Time
Type
";
foreach ($allDates as $date) {
$dayName = date('l', strtotime($date));
$isHoliday = isset($holidays[$date]);
$isRetake = isset($retakes[$date]);
// Holiday row
if ($isHoliday && !isset($byDate[$date])) {
$h = $holidays[$date];
echo "
($dayName) $date
{$h['title']}
Holiday
";
continue;
}
// Retake row
if ($isRetake && !isset($byDate[$date])) {
echo "
($dayName) $date
Retake Session
{$retakes[$date]}
";
continue;
}
// Scheduled sessions
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']));
$teacher = $r['teacher'] ?: "-";
echo "
($dayName) $date
{$r['slot']}
{$r['course_name']}
" . htmlspecialchars($teacher) . "
{$timeRange}
{$type}
";
}
}
echo "
";
}
/*************
if (isset($_POST['Program_ID'], $_POST['Group_ID'])) {
$Program_ID = intval($_POST['Program_ID']);
$Group_ID = intval($_POST['Group_ID']);
// 1. Group Info
$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.");
$groupName = htmlspecialchars($groupData['Group_Name']);
$classDays = array_map('trim', explode(',', $groupData['class_days']));
// 2. Assignments
$stmt = $mysqli->prepare("SELECT tca.*, tp.First_Name, tp.Last_Name, c.Course_Name, cg.Time_Slot, cg.Start_Time, cg.End_Time
FROM teacher_course_assignments tca
JOIN teacher_profile tp ON tca.Teacher_ID = tp.Teacher_ID
JOIN Courses c ON tca.Course_ID = c.Course_ID
JOIN course_group_schedule_main cg ON tca.Schedule_ID = cg.Schedule_ID
WHERE tca.Program_ID = ? AND tca.Group_ID = ?");
$stmt->bind_param("ii", $Program_ID, $Group_ID);
$stmt->execute();
$assignments = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 3. Retakes
$stmt = $mysqli->prepare("
SELECT rr.Retake_Date, gsm.Time_Slot
FROM retake_records rr
JOIN group_slot_mapping gsm
ON rr.Group_ID = gsm.Group_ID
WHERE rr.Program_ID = ? AND rr.Group_ID = ?
");
//$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();
// 4. 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']
];
}
}
// 5. Date Range
$dates = array_merge(array_column($assignments, 'Start_Date'), array_column($assignments, 'End_Date'));
$start = new DateTime(min($dates));
$end = (new DateTime(max($dates)))->modify('+1 day');
$range = new DatePeriod($start, new DateInterval('P1D'), $end);
echo "
Schedule for Group: {$groupName} ";
echo "
";
echo "
";
echo "Date Course Teacher Time Slot Time Type ";
foreach ($range as $date) {
$current = $date->format('Y-m-d');
$day = $date->format('l');
// Always display holiday regardless of class day
//if (isset($holidayMap[$current])) {
// $h = $holidayMap[$current];
// echo "$current {$h['title']} - - - Holiday $current {$h['title']} - - - Holiday $current Retake - {$r['Time_Slot']} - Retake $current {$a['Course_Name']} {$teacher} {$slot} {$time} {$type}
";
}
************/
/*
if (isset($_POST['Program_ID'], $_POST['Group_ID'])) {
$Program_ID = intval($_POST['Program_ID']);
$Group_ID = intval($_POST['Group_ID']);
// Get Group Name
$stmt = $mysqli->prepare("SELECT Group_Name, class_days FROM manager_group_name WHERE Program_ID = ? AND Group_ID = ?");
$stmt->bind_param("ii", $Program_ID, $Group_ID);
$stmt->execute();
$groupData = $stmt->get_result()->fetch_assoc();
$stmt->close();
$groupName = $groupData['Group_Name'] ?? 'Unknown Group';
$groupClassDays = explode(',', trim($groupData['class_days']));
// Assignments
$stmt = $mysqli->prepare("SELECT tca.*, tp.First_Name, tp.Last_Name, c.Course_Name, cg.Time_Slot, cg.Start_Time, cg.End_Time
FROM teacher_course_assignments tca
JOIN teacher_profile tp ON tp.Teacher_ID = tca.Teacher_ID
JOIN Courses c ON c.Course_ID = tca.Course_ID
JOIN course_group_schedule cg ON cg.Schedule_ID = tca.Schedule_ID
WHERE tca.Program_ID = ? AND tca.Group_ID = ?");
$stmt->bind_param("ii", $Program_ID, $Group_ID);
$stmt->execute();
$assignments = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 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();
// 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']
];
}
}
// Date range
$dates = array_merge(array_column($assignments, 'Start_Date'), array_column($assignments, 'End_Date'));
$start = new DateTime(min($dates));
$end = (new DateTime(max($dates)))->modify('+1 day');
$range = new DatePeriod($start, new DateInterval('P1D'), $end);
echo "
";
echo "
";
echo "Schedule for Group: $groupName Date Course Teacher Time Slot Time Type ";
foreach ($range as $date) {
$current = $date->format('Y-m-d');
$day = $date->format('l');
$shownSlots = [];
if (isset($holidayMap[$current])) {
$h = $holidayMap[$current];
echo "$current {$h['title']} - - - Holiday $current Retake - $slot - Retake $current {$a['Course_Name']} $teacher $slotLabel $time $type
";
}
*/
/*
if (isset($_POST['Program_ID'], $_POST['Group_ID'])) {
include_once '../includes/db_connect.php';
$Program_ID = intval($_POST['Program_ID']);
$Group_ID = intval($_POST['Group_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.");
$groupName = $groupData['Group_Name'];
$classDays = array_map('trim', explode(',', $groupData['class_days']));
// 2. Assignments
$stmt = $mysqli->prepare("SELECT tca.*, tp.First_Name, tp.Last_Name, c.Course_Name, cg.Time_Slot, cg.Start_Time, cg.End_Time
FROM teacher_course_assignments tca
JOIN teacher_profile tp ON tca.Teacher_ID = tp.Teacher_ID
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 = ?");
$stmt->bind_param("ii", $Program_ID, $Group_ID);
$stmt->execute();
$assignments = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 3. Replacements
$stmt = $mysqli->prepare("SELECT tra.*, tp.First_Name, tp.Last_Name, c.Course_Name, cg.Time_Slot, cg.Start_Time, cg.End_Time
FROM teacher_replacement_assignments tra
JOIN teacher_profile tp ON tra.Replacing_Teacher_ID = tp.Teacher_ID
JOIN course_group_schedule cg ON tra.Schedule_ID = cg.Schedule_ID
JOIN Courses c ON cg.Course_ID = c.Course_ID
WHERE cg.Group_ID = ? AND cg.Program_ID = ?");
$stmt->bind_param("ii", $Group_ID, $Program_ID);
$stmt->execute();
$replacements = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// 4. 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();
// 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. Dates range
$dates = array_merge(
array_column($assignments, 'Start_Date'),
array_column($assignments, 'End_Date'),
array_column($replacements, 'Replacement_From'),
array_column($replacements, 'Replacement_To')
);
$start = new DateTime(min($dates));
$end = (new DateTime(max($dates)))->modify('+1 day');
$range = new DatePeriod($start, new DateInterval('P1D'), $end);
echo "
Schedule for Group: " . htmlspecialchars($groupName) . " ";
echo "
";
echo "
";
echo "Date Course Teacher Time Slot Time Type ";
foreach ($range as $date) {
$current = $date->format('Y-m-d');
$day = $date->format('l');
if (isset($holidayMap[$current])) {
echo "$current {$holidayMap[$current]['title']} - - - Holiday $current Retake - $slot - Retake $current {$r['Course_Name']} $teacher $slot $time Replacement $current {$a['Course_Name']} $teacher $slot $time $type
";
}
*/
?>