/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/test.php (14278B)
prepare($scheduleQuery);
if (!$stmt) {
die("Error preparing schedule query: " . $mysqli->error);
}
$stmt->bind_param("ii", $Program_ID, $Reserve_Course);
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 3: Loop through each unassigned schedule and try to assign a teacher
// -----------------------------
foreach ($schedules as $schedule) {
// Loop through teachers in the order we just retrieved
foreach ($teachers as $teacher) {
// STEP 3.1: Check if the teacher is available in the required time slot.
// Convert teacher's comma-separated Availability into an array (trim spaces)
$availableSlots = array_map('trim', explode(',', $teacher['Availability']));
if (!in_array($schedule['Time_Slot'], $availableSlots)) {
continue; // Skip teacher if not available in this time slot
}
// STEP 3.2: Check for conflicts in existing assignments.
// A conflict exists if the new schedule's Start_Date is <= an existing assignment’s End_Date
// and the new schedule's End_Date is >= an existing assignment’s Start_Date.
$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;
continue;
}
// Bind: Teacher_ID, Time_Slot, Start_Date, End_Date (order as needed to check for overlap)
$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();
continue;
}
$conflictResult = $conflictStmt->get_result();
$conflictRow = $conflictResult->fetch_assoc();
$conflictCount = $conflictRow['cnt'];
$conflictStmt->close();
if ($conflictCount > 0) {
continue; // Conflict exists, skip teacher
}
// STEP 3.3: Check teacher's load capacity.
// 3.3a: Calculate teacher's current total 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;
continue;
}
$currentLoadStmt->bind_param("i", $teacher['Teacher_ID']);
$currentLoadStmt->execute();
$currentLoadResult = $currentLoadStmt->get_result();
$currentLoadRow = $currentLoadResult->fetch_assoc();
$currentLoad = $currentLoadRow['total_hours'];
$currentLoadStmt->close();
// 3.3b: Get new course's hours from the Courses table.
$courseQuery = "SELECT Course_Time FROM Courses WHERE Course_ID = ?";
$courseStmt = $mysqli->prepare($courseQuery);
if (!$courseStmt) {
echo "Error preparing course query: " . $mysqli->error;
continue;
}
$courseStmt->bind_param("i", $schedule['Course_ID']);
$courseStmt->execute();
$courseResult = $courseStmt->get_result();
$courseData = $courseResult->fetch_assoc();
$newCourseTime = $courseData['Course_Time'];
$courseStmt->close();
// Check if adding this course would exceed the teacher's Load_Hours.
if (($currentLoad + $newCourseTime) > $teacher['Load_Hours']) {
continue; // Teacher is overloaded; skip
}
// STEP 3.4: Check teacher's course preferences for the schedule's Course_ID (priority 1 or 2)
$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;
continue;
}
$prefStmt->bind_param("ii", $teacher['Teacher_ID'], $schedule['Course_ID']);
if (!$prefStmt->execute()) {
echo "Error executing preference query: " . $prefStmt->error;
$prefStmt->close();
continue;
}
$prefResult = $prefStmt->get_result();
$preferences = [];
while ($prefRow = $prefResult->fetch_assoc()) {
$preferences[] = $prefRow;
}
$prefStmt->close();
// STEP 3.5: If teacher has valid preferences, assign the course
if (!empty($preferences)) {
// Insert assignment into teacher_course_assignments table
$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();
// STEP 3.6: 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();
// Once assigned, break out of teacher loop for this schedule.
break;
}
}
}
// After processing, redirect or show a success message
//header("Location: assignment_results.php?status=success");
//exit();
// 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 "
";
?>