/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/test1.php (22425B)
prepare($scheduleQuery);
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Reserve_Course);
}else{
$scheduleQuery = "SELECT * FROM course_group_schedule
WHERE Assigned = 0
AND Program_ID = ?
AND Reserve_Course = ?
ORDER BY Time_Slot";
$stmt = $mysqli->prepare($scheduleQuery);
$stmt->bind_param("ii", $Program_ID, $Reserve_Course);
}
if (!$stmt) {
die("Error preparing schedule query: " . $mysqli->error);
}
if (!$stmt->execute()) {
die("Error executing schedule query: " . $stmt->error);
}
$schedulesResult = $stmt->get_result();
$schedules = [];
while ($row = $schedulesResult->fetch_assoc()) {
$schedules[] = $row;
}
$stmt->close();
// -----------------------------
// STEP 2: Get teachers for this Program, combining teacher_profile and top_teacher_list.
// Ordering: Premium (top) teachers first, then teachers with Seniority_ID = 1 (qualified),
// then teachers with Seniority_ID = 2 (unqualified).
// -----------------------------
//$teacherQuery = "
// SELECT
// tp.Teacher_ID,
// tp.First_Name,
// tp.Last_Name,
// tp.Seniority_ID,
// tp.Time_Slot AS Availability,
// tp.Load_Hours,
// ttl.Top_Teacher_ID,
// ttl.Teacher_Level
// FROM teacher_profile tp
// LEFT JOIN top_teacher_list ttl
// ON tp.Teacher_ID = ttl.Teacher_ID
// AND ttl.Program_ID = ?
// WHERE 1
// ORDER BY
// CASE
// WHEN ttl.Top_Teacher_ID IS NOT NULL THEN 1
// WHEN tp.Seniority_ID = 1 THEN 2
// WHEN tp.Seniority_ID = 2 THEN 3
// ELSE 4
// END,
// tp.First_Name ASC,
// tp.Last_Name ASC
//";
//$stmt = $mysqli->prepare($teacherQuery);
//if (!$stmt) {
// die("Error preparing teacher query: " . $mysqli->error);
//}
//$stmt->bind_param("i", $Program_ID);
//if (!$stmt->execute()) {
// die("Error executing teacher query: " . $stmt->error);
//}
//$result = $stmt->get_result();
//$teachers = [];
//while ($row = $result->fetch_assoc()) {
// $teachers[] = $row;
//}
//$stmt->close();
//// Debug: Optional print teacher list
//foreach ($teachers as $teacher) {
// $isPremium = !empty($teacher['Top_Teacher_ID']) ? "Premium (Level: " . $teacher['Teacher_Level'] . ")" : "";
// echo $teacher['First_Name'] . " " . $teacher['Last_Name'] . " - Seniority: " . $teacher['Seniority_ID'] . " " . $isPremium . " - Load Hours: " . $teacher['Load_Hours'] . "
";
//}
// STEP 2: Get teachers for this Program by combining teacher_profile and top_teacher_list.
// (No filtering on teacher_profile by Program_ID because it's not in that table.)
$teacherQuery = "
SELECT
tp.Teacher_ID,
tp.First_Name,
tp.Last_Name,
tp.Seniority_ID,
tp.Time_Slot AS Availability,
tp.Load_Hours,
ttl.Top_Teacher_ID,
ttl.Teacher_Level
FROM teacher_profile tp
LEFT JOIN top_teacher_list ttl
ON tp.Teacher_ID = ttl.Teacher_ID
AND ttl.Program_ID = ?
WHERE 1
ORDER BY tp.First_Name ASC, tp.Last_Name ASC
";
$stmt = $mysqli->prepare($teacherQuery);
if (!$stmt) {
die("Error preparing teacher query: " . $mysqli->error);
}
$stmt->bind_param("i", $Program_ID);
$stmt->execute();
$result = $stmt->get_result();
$allTeachers = [];
while ($row = $result->fetch_assoc()) {
$allTeachers[] = $row;
}
$stmt->close();
// Initialize the round-robin pointer
$teacherCount = count($teachers);
$teacherIndex = 0;
// -----------------------------
// STEP 3: Loop through each unassigned schedule and try to assign a teacher
// -----------------------------
// Separate teachers into groups
$premiumTeachers = [];
$qualifiedTeachers = [];
$unqualifiedTeachers = [];
foreach ($allTeachers as $teacher) {
if (!empty($teacher['Top_Teacher_ID'])) {
$premiumTeachers[] = $teacher;
} elseif ($teacher['Seniority_ID'] == 1) {
$qualifiedTeachers[] = $teacher;
} elseif ($teacher['Seniority_ID'] == 2) {
$unqualifiedTeachers[] = $teacher;
}
}
// Initialize round-robin pointers for each group
$ptrPremium = 0;
$ptrQualified = 0;
$ptrUnqualified = 0;
$cntPremium = count($premiumTeachers);
$cntQualified = count($qualifiedTeachers);
$cntUnqualified = count($unqualifiedTeachers);
// (Optional) Debug output: print ordered teacher groups
echo "
Premium Teachers:
";
foreach ($premiumTeachers as $t) {
echo $t['First_Name']." ".$t['Last_Name']." (Level: ".$t['Teacher_Level'].")
";
}
echo "
Qualified Teachers:
";
foreach ($qualifiedTeachers as $t) {
echo $t['First_Name']." ".$t['Last_Name']." (Seniority: ".$t['Seniority_ID'].")
";
}
echo "
Unqualified Teachers:
";
foreach ($unqualifiedTeachers as $t) {
echo $t['First_Name']." ".$t['Last_Name']." (Seniority: ".$t['Seniority_ID'].")
";
}
echo "
";
// Helper inline: For each teacher, perform assignment checks.
// (Availability, conflict, load hours, preferences)
foreach ($schedules as $schedule) {
$assigned = false;
// Function to check if teacher can be assigned
// (We inline the checks here.)
$canAssign = function($teacher, $schedule) use ($mysqli, $Program_ID) {
// Check availability: Teacher's Availability field is a comma-separated list.
$availableSlots = array_map('trim', explode(',', $teacher['Availability']));
if (!in_array($schedule['Time_Slot'], $availableSlots)) {
return false;
}
// Check conflicts: any overlapping assignment in the same time slot.
$conflictQuery = "SELECT COUNT(*) AS cnt FROM teacher_course_assignments
WHERE Teacher_ID = ?
AND Time_Slot = ?
AND (? <= End_Date AND ? >= Start_Date)";
$conflictStmt = $mysqli->prepare($conflictQuery);
if (!$conflictStmt) {
echo "Error preparing conflict query: " . $mysqli->error . "
";
return false;
}
$conflictStmt->bind_param("isss", $teacher['Teacher_ID'], $schedule['Time_Slot'], $schedule['Start_Date'], $schedule['End_Date']);
if (!$conflictStmt->execute()) {
echo "Error executing conflict query: " . $conflictStmt->error . "
";
$conflictStmt->close();
return false;
}
$conflictResult = $conflictStmt->get_result();
$conflictRow = $conflictResult->fetch_assoc();
$conflictCount = $conflictRow['cnt'];
$conflictStmt->close();
if ($conflictCount > 0) {
return false;
}
// Check load hours capacity:
// 1. Get teacher's current assigned hours.
$currentLoadQuery = "SELECT IFNULL(SUM(c.Course_Time), 0) AS total_hours
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Teacher_ID = ?";
$currentLoadStmt = $mysqli->prepare($currentLoadQuery);
if (!$currentLoadStmt) {
echo "Error preparing current load query: " . $mysqli->error . "
";
return false;
}
$currentLoadStmt->bind_param("i", $teacher['Teacher_ID']);
$currentLoadStmt->execute();
$currentLoadResult = $currentLoadStmt->get_result();
$currentLoadRow = $currentLoadResult->fetch_assoc();
$currentLoad = $currentLoadRow['total_hours'];
$currentLoadStmt->close();
// 2. Get new course's hours.
$courseQuery = "SELECT Course_Time FROM Courses WHERE Course_ID = ?";
$courseStmt = $mysqli->prepare($courseQuery);
if (!$courseStmt) {
echo "Error preparing course query: " . $mysqli->error . "
";
return false;
}
$courseStmt->bind_param("i", $schedule['Course_ID']);
$courseStmt->execute();
$courseResult = $courseStmt->get_result();
$courseData = $courseResult->fetch_assoc();
$newCourseTime = $courseData['Course_Time'];
$courseStmt->close();
if (($currentLoad + $newCourseTime) > $teacher['Load_Hours']) {
return false;
}
// Check teacher's course preferences.
$prefQuery = "SELECT * FROM teacher_course_preferences
WHERE Teacher_ID = ?
AND Course_ID = ?
AND Priority IN (1,2)
ORDER BY Priority ASC";
$prefStmt = $mysqli->prepare($prefQuery);
if (!$prefStmt) {
echo "Error preparing preference query: " . $mysqli->error . "
";
return false;
}
$prefStmt->bind_param("ii", $teacher['Teacher_ID'], $schedule['Course_ID']);
$prefStmt->execute();
$prefResult = $prefStmt->get_result();
$preferences = [];
while ($prefRow = $prefResult->fetch_assoc()) {
$preferences[] = $prefRow;
}
$prefStmt->close();
if (empty($preferences)) {
return false;
}
return true;
}; // end of anonymous function
// Try to assign from premium teachers first
if ($cntPremium > 0) {
for ($i = 0; $i < $cntPremium; $i++) {
$currentIndex = ($ptrPremium + $i) % $cntPremium;
$teacher = $premiumTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
// Assign this teacher
// Insert assignment
$insertQuery = "INSERT INTO teacher_course_assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
// Mark schedule as assigned.
$updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
// Update pointer for premium group.
$ptrPremium = ($currentIndex + 1) % $cntPremium;
$assigned = true;
break;
}
}
}
// If not assigned by premium, try qualified teachers.
if (!$assigned && $cntQualified > 0) {
for ($i = 0; $i < $cntQualified; $i++) {
$currentIndex = ($ptrQualified + $i) % $cntQualified;
$teacher = $qualifiedTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
$insertQuery = "INSERT INTO teacher_course_assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
$updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
$ptrQualified = ($currentIndex + 1) % $cntQualified;
$assigned = true;
break;
}
}
}
// If still not assigned, try unqualified teachers.
if (!$assigned && $cntUnqualified > 0) {
for ($i = 0; $i < $cntUnqualified; $i++) {
$currentIndex = ($ptrUnqualified + $i) % $cntUnqualified;
$teacher = $unqualifiedTeachers[$currentIndex];
if ($canAssign($teacher, $schedule)) {
$insertQuery = "INSERT INTO teacher_course_assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$insertStmt = $mysqli->prepare($insertQuery);
if (!$insertStmt) {
echo "Error preparing insert query: " . $mysqli->error . "
";
continue;
}
$assignedAt = date('Y-m-d H:i:s');
$insertStmt->bind_param("iiiiissss",
$teacher['Teacher_ID'],
$schedule['Course_ID'],
$schedule['Group_ID'],
$schedule['Program_ID'],
$schedule['Schedule_ID'],
$schedule['Time_Slot'],
$schedule['Start_Date'],
$schedule['End_Date'],
$assignedAt
);
if (!$insertStmt->execute()) {
echo "Error executing insert query: " . $insertStmt->error . "
";
$insertStmt->close();
continue;
}
$insertStmt->close();
$updateQuery = "UPDATE course_group_schedule SET Assigned = 1 WHERE Schedule_ID = ?";
$updateStmt = $mysqli->prepare($updateQuery);
if (!$updateStmt) {
echo "Error preparing update query: " . $mysqli->error . "
";
continue;
}
$updateStmt->bind_param("i", $schedule['Schedule_ID']);
if (!$updateStmt->execute()) {
echo "Error executing update query: " . $updateStmt->error . "
";
$updateStmt->close();
continue;
}
$updateStmt->close();
$ptrUnqualified = ($currentIndex + 1) % $cntUnqualified;
$assigned = true;
break;
}
}
}
}
// Build the query to retrieve teacher assignments along with related details
$query = "
SELECT
tca.Assignment_ID,
tca.Teacher_ID,
tca.Course_ID,
tca.Group_ID,
tca.Program_ID,
tca.Schedule_ID,
tca.Time_Slot,
tca.Start_Date,
tca.End_Date,
tca.Assigned_At,
tp.First_Name,
tp.Last_Name,
p.Program_Name,
c.Course_Name,
g.Group_Name
FROM teacher_course_assignments tca
JOIN teacher_profile tp ON tca.Teacher_ID = tp.Teacher_ID
JOIN Programs p ON tca.Program_ID = p.Program_ID
JOIN Courses c ON tca.Course_ID = c.Course_ID
JOIN manager_group_name g ON tca.Group_ID = g.Group_ID
ORDER BY tca.Assigned_At DESC
";
// Execute the query
$result = $mysqli->query($query);
if (!$result) {
die("Error executing query: " . $mysqli->error);
}
?>
| Assignment ID |
Teacher |
Course |
Group |
Program |
Time Slot |
Start Date |
End Date |
Assigned At |
fetch_assoc()) { ?>
|
|
|
|
|
|
|
|
|
| Assignment ID |
Teacher |
Course |
Group |
Program |
Time Slot |
Start Date |
End Date |
Assigned At |
query($query);
if (!$result) {
die("Error fetching teachers: " . $mysqli->error);
}
echo "
Teacher Assigned Hours
";
echo "
";
echo "
| Teacher |
Load Hours |
Total Assigned Hours |
";
while ($teacher = $result->fetch_assoc()) {
$teacher_id = $teacher['Teacher_ID'];
$teacherName = $teacher['First_Name'] . " " . $teacher['Last_Name'];
$load_hours = $teacher['Load_Hours'];
// Query to sum Course_Time for all assignments of this teacher.
// Assumes Course_Time is stored in a numeric format (e.g., integer or decimal) representing hours.
$stmt = $mysqli->prepare("
SELECT IFNULL(SUM(c.Course_Time), 0) AS total_hours
FROM teacher_course_assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Teacher_ID = ?
");
if (!$stmt) {
die("Error preparing sum query: " . $mysqli->error);
}
$stmt->bind_param("i", $teacher_id);
if (!$stmt->execute()) {
die("Error executing sum query: " . $stmt->error);
}
$result2 = $stmt->get_result();
$sum_row = $result2->fetch_assoc();
$total_hours = $sum_row['total_hours'];
$stmt->close();
echo "";
echo "| " . htmlspecialchars($teacherName) . " | ";
echo "" . htmlspecialchars($load_hours) . " | ";
echo "" . htmlspecialchars($total_hours) . " | ";
echo "
";
}
echo "
";
//} else {
//echo "Program ID and Group ID are required.";
//}
?>