/home/techb158/workloadmatch.com/workloadmatch.com/Manager/Inc
Edit: /home/techb158/workloadmatch.com/workloadmatch.com/Manager/Inc/Assign_Teacher_Manual.php (20508B)
$courseID) {
$courseID = intval($courseID);
// Get the corresponding time slot id, start and end dates.
$timeSlotInput = !empty($_POST['Time_Slot'][$index]) ? mysqli_real_escape_string($mysqli, $_POST['Time_Slot'][$index]) : null;
$startDate = !empty($_POST['Start_Date'][$index]) ? mysqli_real_escape_string($mysqli, $_POST['Start_Date'][$index]) : null;
$endDate = !empty($_POST['End_Date'][$index]) ? mysqli_real_escape_string($mysqli, $_POST['End_Date'][$index]) : null;
// Fetch time slot details from Time_Slot_Programs using the provided time slot string.
$queryTime = "SELECT Time_Slot_ID, Time_Slot, Time_From, Time_To
FROM Time_Slot_Programs
WHERE Time_Slot = ?";
$stmtTime = $mysqli->prepare($queryTime);
if (!$stmtTime) {
//echo "Error preparing time slot query for Course ID $courseID: " . $mysqli->error . "
";
continue;
}
$stmtTime->bind_param("s", $timeSlotInput);
if (!$stmtTime->execute()) {
//echo "Error executing time slot query for Course ID $courseID: " . $stmtTime->error . "
";
$stmtTime->close();
continue;
}
$resultTime = $stmtTime->get_result();
$timeRow = $resultTime->fetch_assoc();
$stmtTime->close();
if ($timeRow) {
$timeSlot = $timeRow["Time_Slot"];
$timeSlotID = $timeRow["Time_Slot_ID"];
$startTime = !empty($timeRow['Time_From']) ? $timeRow['Time_From'] : null;
$endTime = !empty($timeRow['Time_To']) ? $timeRow['Time_To'] : null;
} else {
//echo "No time slot details found for Time_Slot: $timeSlotInput for Course ID $courseID.
";
continue;
}
// Retrieve Reserve_Course value (checkbox handling)
$reserveCourse = isset($_POST['Reserve_Course'][$courseID]) ? 1 : 0;
// Proceed only if all necessary details are present.
if ($timeSlot && $startDate && $endDate && $startTime && $endTime) {
// STEP A: Check if this course has already been assigned for this teacher.
$checkQuery = "SELECT COUNT(*) FROM Teacher_Course_Assignments
WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ? AND Teacher_ID = ?";
$stmtCheck = $mysqli->prepare($checkQuery);
if (!$stmtCheck) {
//echo "Error preparing check query for Course ID $courseID: " . $mysqli->error . "
";
continue;
}
$stmtCheck->bind_param("iiii", $Program_ID, $courseID, $Group_ID, $Teacher_ID);
if (!$stmtCheck->execute()) {
//echo "Error executing check query for Course ID $courseID: " . $stmtCheck->error . "
";
$stmtCheck->close();
continue;
}
$stmtCheck->bind_result($count);
$stmtCheck->fetch();
$stmtCheck->close();
if ($count > 0) {
//echo "Course ID $courseID already assigned for teacher $Teacher_ID. Skipping.
";
continue;
}
// STEP B: Check for conflict with existing assignments (conflict schedule verification)
// A conflict exists if there is an 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)";
$stmtConflict = $mysqli->prepare($conflictQuery);
if (!$stmtConflict) {
//echo "Error preparing conflict query for Course ID $courseID: " . $mysqli->error . "
";
continue;
}
$stmtConflict->bind_param("isss", $Teacher_ID, $timeSlot, $startDate, $endDate);
if (!$stmtConflict->execute()) {
//echo "Error executing conflict query for Course ID $courseID: " . $stmtConflict->error . "
";
$stmtConflict->close();
continue;
}
$resultConflict = $stmtConflict->get_result();
$conflictRow = $resultConflict->fetch_assoc();
$conflictCount = $conflictRow['cnt'];
$stmtConflict->close();
$stmtConflict=0;
if ($conflictCount > 0) {
//echo "Conflict detected for Course ID $courseID for teacher $Teacher_ID. Skipping.
";
continue;
}
// STEP C: Fetch schedule details from Course_Group_Schedule for an unassigned schedule.
$fetchQuery = "SELECT Schedule_ID, Reserve_Course, Time_Slot, Start_Date, End_Date, Start_Time, End_Time
FROM Course_Group_Schedule_Main
WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ? AND Assigned = 0";
$stmtFetch = $mysqli->prepare($fetchQuery);
if (!$stmtFetch) {
//echo "Error preparing schedule fetch query for Course ID $courseID: " . $mysqli->error . "
";
continue;
}
$stmtFetch->bind_param("iii", $Program_ID, $courseID, $Group_ID);
if (!$stmtFetch->execute()) {
//echo "Error executing schedule fetch query for Course ID $courseID: " . $stmtFetch->error . "
";
$stmtFetch->close();
continue;
}
///Fix: Update STEP C and D to loop through both time slots
/* $stmtFetch->bind_result($Schedule_ID, $dbReserveCourse, $dbTime_Slot, $dbStart_Date, $dbEnd_Date, $dbStart_Time, $dbEnd_Time);
if (!$stmtFetch->fetch()) {
//echo "No unassigned schedule found for Course ID $courseID.
";
$stmtFetch->close();
continue;
}
$stmtFetch->close(); */
/* // Use the fetched schedule details.
$timeSlotFinal = $dbTime_Slot;
$startDateFinal = $dbStart_Date;
$endDateFinal = $dbEnd_Date;
$startTimeFinal = $dbStart_Time;
$endTimeFinal = $dbEnd_Time;
// STEP D: Insert the new assignment into Teacher_Course_Assignments.
$assignedAt = date('Y-m-d H:i:s');
$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 (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmtInsert = $mysqli->prepare($insertQuery);
if (!$stmtInsert) {
//echo "Error preparing insert query for Course ID $courseID: " . $mysqli->error . "
";
continue;
}
$stmtInsert->bind_param("iiiiissss",
$Teacher_ID,
$courseID,
$Group_ID,
$Program_ID,
$Schedule_ID,
$timeSlotFinal,
$startDateFinal,
$endDateFinal,
$assignedAt
);
if (!$stmtInsert->execute()) {
//echo "Error executing insert query for Course ID $courseID: " . $stmtInsert->error . "
";
$stmtInsert->close();
continue;
}
$stmtInsert->close();
//echo "Assignment inserted for Course ID $courseID.
";
// STEP E: After successful insert, update the schedule to mark it as assigned.
$updateQuery = "UPDATE Course_Group_Schedule_Main SET Assigned = 1 WHERE Schedule_ID = ?";
$stmtUpdate = $mysqli->prepare($updateQuery);
if (!$stmtUpdate) {
//echo "Error preparing update query for Schedule ID $Schedule_ID: " . $mysqli->error . "
";
continue;
}
$stmtUpdate->bind_param("i", $Schedule_ID);
if (!$stmtUpdate->execute()) {
//echo "Error executing update query for Schedule ID $Schedule_ID: " . $stmtUpdate->error . "
";
$stmtUpdate->close();
continue;
}
$stmtUpdate->close();
// STEP E: After successful insert, update the schedule to mark it as assigned.
$updateQuery = "UPDATE Schedule_Course_for_Group SET Assigned = 1 WHERE Schedule_ID = ? AND Program_ID = ? AND Group_ID = ?";
$stmtUpdate = $mysqli->prepare($updateQuery);
if (!$stmtUpdate) {
//echo "Error preparing update query for Schedule ID $Schedule_ID: " . $mysqli->error . "
";
continue;
}
$stmtUpdate->bind_param("iii", $Schedule_ID,$Program_ID, $Group_ID);
if (!$stmtUpdate->execute()) {
//echo "Error executing update query for Schedule ID $Schedule_ID: " . $stmtUpdate->error . "
";
$stmtUpdate->close();
continue;
}
$stmtUpdate->close(); */
$stmtFetch->bind_result($Schedule_ID, $dbReserveCourse, $dbTime_Slot, $dbStart_Date, $dbEnd_Date, $dbStart_Time, $dbEnd_Time);
$schedules = [];
while ($stmtFetch->fetch()) {
$schedules[] = [
'Schedule_ID' => $Schedule_ID,
'Reserve_Course' => $dbReserveCourse,
'Time_Slot' => $dbTime_Slot,
'Start_Date' => $dbStart_Date,
'End_Date' => $dbEnd_Date,
'Start_Time' => $dbStart_Time,
'End_Time' => $dbEnd_Time,
];
}
$stmtFetch->close();
if (empty($schedules)) {
continue;
}
// Loop through both slots (e.g., Morning and Afternoon)
foreach ($schedules as $s) {
$assignedAt = date('Y-m-d H:i:s');
$stmtInsert = $mysqli->prepare("INSERT INTO Teacher_Course_Assignments
(Teacher_ID, Course_ID, Group_ID, Program_ID, Schedule_ID, Time_Slot, Start_Date, End_Date, Assigned_At)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
if (!$stmtInsert) {
continue;
}
$stmtInsert->bind_param("iiiiissss",
$Teacher_ID,
$courseID,
$Group_ID,
$Program_ID,
$s['Schedule_ID'],
$s['Time_Slot'],
$s['Start_Date'],
$s['End_Date'],
$assignedAt
);
if ($stmtInsert->execute()) {
$stmtInsert->close();
// Update Assigned = 1 in both tables
$stmtUpdate1 = $mysqli->prepare("UPDATE Course_Group_Schedule_Main SET Assigned = 1 WHERE Schedule_ID = ?");
$stmtUpdate1->bind_param("i", $s['Schedule_ID']);
$stmtUpdate1->execute();
$stmtUpdate1->close();
$stmtUpdate2 = $mysqli->prepare("UPDATE Schedule_Course_for_Group SET Assigned = 1 WHERE Schedule_ID = ? AND Program_ID = ? AND Group_ID = ?");
$stmtUpdate2->bind_param("iii", $s['Schedule_ID'], $Program_ID, $Group_ID);
$stmtUpdate2->execute();
$stmtUpdate2->close();
} else {
$stmtInsert->close();
}
}
//echo "Schedule ID $Schedule_ID updated as assigned.
";
} else {
//echo "Missing required details for Course ID $courseID.
";
}
}
// After processing all records, redirect if desired or output final message.
header("Location: assign_teacher_manual.php?error=Course Assigned successfully!");
exit();
}
//////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////
///////////////////Working Code/////////////////////
//////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////
//include_once '../includes/db_connect.php';
//include_once '../includes/functions.php';
//sec_session_start();
//$error_msg = "";
//
//if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// // Get additional fields (Program_ID, Group_ID, Teacher_ID)
// $Program_ID = intval($_POST['Program_ID']);
// $Group_ID = intval($_POST['Group_ID']);
// $Teacher_ID = intval($_POST['Teacher_ID']);
//
// // Loop through each active course from the posted array.
// foreach ($_POST['Active_Course'] as $index => $courseID) {
// $courseID = intval($courseID);
// // Get the corresponding time slot id (string), start and end dates.
// $timeSlotid = !empty($_POST['Time_Slot'][$index]) ? mysqli_real_escape_string($mysqli, $_POST['Time_Slot'][$index]) : null;
// $startDate = !empty($_POST['Start_Date'][$index]) ? mysqli_real_escape_string($mysqli, $_POST['Start_Date'][$index]) : null;
// $endDate = !empty($_POST['End_Date'][$index]) ? mysqli_real_escape_string($mysqli, $_POST['End_Date'][$index]) : null;
//
// // Fetch time slot details from Time_Slot_Programs using the provided time slot string.
// $querys = "SELECT Time_Slot_ID, Time_Slot, Time_From, Time_To
// FROM Time_Slot_Programs
// WHERE Time_Slot = ?";
// $stmts = $mysqli->prepare($querys);
// if (!$stmts) {
// ////echo "Error preparing time slot query for Course ID $courseID: " . $mysqli->error . "
";
// continue;
// }
// $stmts->bind_param("s", $timeSlotid);
// if (!$stmts->execute()) {
// ////echo "Error executing time slot query for Course ID $courseID: " . $stmts->error . "
";
// $stmts->close();
// continue;
// }
// $results = $stmts->get_result();
// $rows = $results->fetch_assoc();
// $stmts->close();
//
// // If the query returns data, extract the values.
// if ($rows) {
// $timeSlot = $rows["Time_Slot"];
// $startTime = !empty($rows['Time_From']) ? $rows['Time_From'] : null;
// $endTime = !empty($rows['Time_To']) ? $rows['Time_To'] : null;
// } else {
// ////echo "No time slot details found for Time_Slot: $timeSlotid for Course ID $courseID.
";
// continue;
// }
//
// // Retrieve Reserve_Course value (checkbox handling)
// $reserveCourse = isset($_POST['Reserve_Course'][$courseID]) ? 1 : 0;
//
// // Proceed only if all necessary details are present.
// if ($timeSlot && $startDate && $endDate && $startTime && $endTime) {
// // Check if this course has already been assigned for this teacher.
// $checkStmt = $mysqli->prepare("SELECT COUNT(*) FROM Teacher_Course_Assignments WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ? AND Teacher_ID = ?");
// if (!$checkStmt) {
// ////echo "Error preparing check query for record $index: " . $mysqli->error . "
";
// continue;
// }
// $checkStmt->bind_param("iiii", $Program_ID, $courseID, $Group_ID, $Teacher_ID);
// if (!$checkStmt->execute()) {
// ////echo "Error executing check query for record $index: " . $checkStmt->error . "
";
// $checkStmt->close();
// continue;
// }
// $checkStmt->bind_result($count);
// $checkStmt->fetch();
// $checkStmt->close();
//
// if ($count > 0) {
// ////echo "Record $index: Course ID $courseID already assigned for teacher $Teacher_ID. Skipping.
";
// continue;
// }
//
// // Fetch schedule details from Course_Group_Schedule for an unassigned schedule.
// $fetchStmt = $mysqli->prepare("SELECT Schedule_ID, Reserve_Course, Time_Slot, Start_Date, End_Date, Start_Time, End_Time
// FROM Course_Group_Schedule
// WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ? AND Assigned = 0");
// if (!$fetchStmt) {
// ////echo "Error preparing schedule fetch query for record $index: " . $mysqli->error . "
";
// continue;
// }
// $fetchStmt->bind_param("iii", $Program_ID, $courseID, $Group_ID);
// if (!$fetchStmt->execute()) {
// ////echo "Error executing schedule fetch query for record $index: " . $fetchStmt->error . "
";
// $fetchStmt->close();
// continue;
// }
// $fetchStmt->bind_result($Schedule_ID, $dbReserveCourse, $dbTime_Slot, $dbStart_Date, $dbEnd_Date, $dbStart_Time, $dbEnd_Time);
// if (!$fetchStmt->fetch()) {
// ////echo "Record $index: No unassigned schedule found for Course ID $courseID.
";
// $fetchStmt->close();
// continue;
// }
// $fetchStmt->close();
//
// // Use the fetched schedule details.
// $timeSlotFinal = $dbTime_Slot;
// $startDateFinal = $dbStart_Date;
// $endDateFinal = $dbEnd_Date;
// $startTimeFinal = $dbStart_Time;
// $endTimeFinal = $dbEnd_Time;
//
// // Insert the new assignment into Teacher_Course_Assignments.
// $assignedAt = date('Y-m-d H:i:s');
// $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 (?, ?, ?, ?, ?, ?, ?, ?, ?)";
// $stmtInsert = $mysqli->prepare($insertQuery);
// if (!$stmtInsert) {
// ////echo "Error preparing insert query for record $index: " . $mysqli->error . "
";
// continue;
// }
// $stmtInsert->bind_param("iiiiissss",
// $Teacher_ID,
// $courseID,
// $Group_ID,
// $Program_ID,
// $Schedule_ID,
// $timeSlotFinal,
// $startDateFinal,
// $endDateFinal,
// $assignedAt
// );
// if (!$stmtInsert->execute()) {
// ////echo "Error executing insert query for record $index: " . $stmtInsert->error . "
";
// $stmtInsert->close();
// continue;
// }
// $stmtInsert->close();
// ////echo "Record $index: Assignment inserted for Course ID $courseID.
";
//
// // After successful insert, update the schedule to mark it as assigned.
// $updateQuery = "UPDATE Course_Group_Schedule SET Assigned = 1 WHERE Schedule_ID = ?";
// $updateStmt = $mysqli->prepare($updateQuery);
// if (!$updateStmt) {
// ////echo "Error preparing update query for record $index: " . $mysqli->error . "
";
// continue;
// }
// $updateStmt->bind_param("i", $Schedule_ID);
// if (!$updateStmt->execute()) {
// ////echo "Error executing update query for record $index: " . $updateStmt->error . "
";
// $updateStmt->close();
// continue;
// }
// $updateStmt->close();
// ////echo "Record $index: Schedule ID $Schedule_ID updated as assigned.
";
// } else {
// ////echo "Record $index: Missing required schedule details for Course ID $courseID.
";
// }
// }
//
// if (empty($errors)) {
// header("Location: assign_teacher_manual.php?error=Course Assigned successfully!");
// exit();
// ////echo "Records saved successfully!";
// } else {
// ////echo "There were errors: " . implode("; ", $errors);
// }
//}
?>