/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/AI_Generate_Schedule.php (11031B)
prepare("SELECT Time_Slot, Time_From, Time_To, class_days, Weekend_Class, Group_Name FROM manager_group_name WHERE Group_ID = ?");
$stmt->bind_param("i", $Group_ID);
$stmt->execute();
return $stmt->get_result()->fetch_assoc();
}
// Retrieve time slots for the group
function fetch_slot_times($mysqli, $slotLabels, $managerStart, $managerEnd) {
$timeFrom = [];
$timeTo = [];
foreach ($slotLabels as $label) {
$label = trim($label);
$stmt = $mysqli->prepare("SELECT Time_From, Time_To FROM time_slot_programs WHERE Time_Slot = ?");
$stmt->bind_param("s", $label);
$stmt->execute();
$result = $stmt->get_result();
$validSlotFound = false;
while ($row = $result->fetch_assoc()) {
$slotStart = new DateTime($row['Time_From']);
$slotEnd = new DateTime($row['Time_To']);
if ($slotStart >= $managerStart && $slotEnd <= $managerEnd) {
$timeFrom[] = $slotStart->format("H:i:s");
$timeTo[] = $slotEnd->format("H:i:s");
$validSlotFound = true;
break;
}
}
if (!$validSlotFound && count($slotLabels) === 1) {
$timeFrom[] = $managerStart->format("H:i:s");
$timeTo[] = $managerEnd->format("H:i:s");
}
}
return [$timeFrom, $timeTo];
}
// Collect all holiday dates in the current year
function get_holidays($mysqli, $Start_Date) {
$holidays = [];
$res = $mysqli->query("SELECT Event_Start, Event_End FROM Events WHERE Calendar_Year = YEAR('$Start_Date')");
while ($row = $res->fetch_assoc()) {
$start = new DateTime($row['Event_Start']);
$end = new DateTime($row['Event_End']);
while ($start <= $end) {
$holidays[] = $start->format('Y-m-d');
$start->modify('+1 day');
}
}
return $holidays;
}
// Collect all retake dates for the group and program
function get_retake_dates($mysqli, $Program_ID, $Group_ID) {
$retakeDates = [];
$stmt = $mysqli->prepare("SELECT Retake_Date FROM retake_records WHERE Program_ID = ? AND Group_ID = ?");
$stmt->bind_param("ii", $Program_ID, $Group_ID);
$stmt->execute();
$res = $stmt->get_result();
while ($row = $res->fetch_assoc()) {
$retakeDates[] = $row['Retake_Date'];
}
return $retakeDates;
}
// Generate all valid class dates, excluding holidays, retakes, and July
function generate_valid_dates($Start_Date, $End_Date, $classDays, $holidayDates, $retakeDates) {
$validDates = [];
$cur = new DateTime($Start_Date);
$end = $End_Date ? new DateTime($End_Date) : null;
while (!$end || $cur <= $end) {
$day = $cur->format('l');
$dateStr = $cur->format('Y-m-d');
$month = (int) $cur->format('m');
if ($month === 7 || !in_array($day, $classDays) || in_array($dateStr, $holidayDates) || in_array($dateStr, $retakeDates)) {
$cur->modify('+1 day');
continue;
}
$validDates[] = $dateStr;
$cur->modify('+1 day');
if ($end === null && count($validDates) > 365) break; // Prevent infinite loop in fallback
}
return $validDates;
}
// Calculate the average duration of one session
function calculate_session_length($timeFrom, $timeTo) {
$sessionLength = 0;
foreach ($timeFrom as $i => $from) {
$fromTime = new DateTime($from);
$toTime = new DateTime($timeTo[$i]);
$sessionLength += ($toTime->getTimestamp() - $fromTime->getTimestamp()) / 3600;
}
$slotCount = count($timeFrom);
return $slotCount > 0 ? $sessionLength / $slotCount : 3;
}
// Calculate the number of sessions required for each course
function calculate_course_sessions($mysqli, $Program_ID, $sessionLength) {
$courseSessions = [];
$res = $mysqli->query("SELECT Course_ID, Course_Time FROM Courses WHERE Program_ID = $Program_ID");
while ($row = $res->fetch_assoc()) {
$courseID = $row['Course_ID'];
$courseHours = $row['Course_Time'];
$courseSessions[$courseID] = ceil($courseHours / $sessionLength);
}
return $courseSessions;
}
// Main POST Logic
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// Retrieve and sanitize inputs
$Program_ID = intval($_POST['Program_ID']);
$Group_ID = intval($_POST['Group_ID']);
$Start_Date = $_POST['Start_Date'];
$End_Date = $_POST['End_Date'] ?? '';
$selectedCourses = $_POST['Selected_Courses'] ?? [];
$courseSessions = $_POST['Course_Sessions'] ?? [];
$priorityCourses = $_POST['Priority_Course'] ?? [];
// Fetch group information
$group = get_group_info($mysqli, $Group_ID);
if (!$group) {
echo "Group info not found.";
exit;
}
// Prepare class schedule details
$classDays = explode(',', $group['class_days']);
if (!$group['Weekend_Class'] && in_array('Saturday', $classDays)) {
$classDays = array_filter($classDays, fn($day) => $day !== 'Saturday');
}
// Fetch slot times
$slotLabels = explode(',', $group['Time_Slot']);
$managerStart = new DateTime(trim($group['Time_From']));
$managerEnd = new DateTime(trim($group['Time_To']));
list($timeFrom, $timeTo) = fetch_slot_times($mysqli, $slotLabels, $managerStart, $managerEnd);
$sessionLength = calculate_session_length($timeFrom, $timeTo);
$courseSessions = calculate_course_sessions($mysqli, $Program_ID, $sessionLength);
// Retrieve holidays and retake dates
$holidayDates = get_holidays($mysqli, $Start_Date);
$retakeDates = get_retake_dates($mysqli, $Program_ID, $Group_ID);
// Generate valid class dates
$validDates = generate_valid_dates($Start_Date, $End_Date, $classDays, $holidayDates, $retakeDates);
// Debug output for valid dates
echo "
📅 Valid Dates for Scheduling (Excluding July)
"; print_r($validDates); echo "
";
// Total number of sessions needed and available days
$totalSessionsNeeded = array_sum($courseSessions);
$availableDays = count($validDates);
// Estimate sessions per day (based on number of slots)
$sessionsPerDay = count($slotLabels);
// Calculate how many days are needed to finish all sessions
$daysRequired = ceil($totalSessionsNeeded / $sessionsPerDay);
// Get expected end date
$expectedEndDate = $validDates[$daysRequired - 1] ?? end($validDates);
echo "
📅 Expected End Date Based on Andragogical Days
";
echo "
$expectedEndDate (" . (new DateTime($expectedEndDate))->format('l') . ")
";
// Sort courses by priority
usort($selectedCourses, function ($a, $b) use ($priorityCourses) {
$priorityA = $priorityCourses[$a] ?? PHP_INT_MAX;
$priorityB = $priorityCourses[$b] ?? PHP_INT_MAX;
return $priorityA <=> $priorityB;
});
echo "
🔄 Sorted Courses (Priority First)
"; print_r($selectedCourses); echo "
";
// Prepare for fair scheduling (one course per slot)
$schedule = [];
$remainingSessions = [];
foreach ($selectedCourses as $courseID) {
$remainingSessions[$courseID] = $courseSessions[$courseID] ?? 0;
}
// Initialize scheduling logic
$courseQueue = $selectedCourses;
$currentCourses = [0 => null, 1 => null];
foreach ([0, 1] as $slotIndex) {
foreach ($courseQueue as $courseID) {
if ($remainingSessions[$courseID] > 0) {
$currentCourses[$slotIndex] = $courseID;
break;
}
}
}
// Fetch course names
$courseNames = [];
$courseRes = $mysqli->query("SELECT Course_ID, Course_Name FROM Courses");
while ($row = $courseRes->fetch_assoc()) {
$courseNames[$row['Course_ID']] = $row['Course_Name'];
}
foreach ($validDates as $date) {
foreach ([0, 1] as $slotIndex) {
if (!isset($currentCourses[$slotIndex])) continue;
$courseID = $currentCourses[$slotIndex];
$courseName = $courseNames[$courseID] ?? "Course #$courseID";
// Schedule the session
$schedule[] = [
'Date' => $date,
'Slot' => "Slot " . ($slotIndex + 1),
'Time' => format_time_slot_range($slotLabels[$slotIndex], $timeFrom[$slotIndex], $timeTo[$slotIndex], (new DateTime($date))->format('l')),
'Course_ID' => $courseID
];
// Decrease remaining sessions
$remainingSessions[$courseID]--;
}
}
// Initialize the generated HTML content
$generatedScheduleHTML = "
📘 Final Generated Schedule
" . print_r($schedule, true) . "
";
$generatedScheduleHTML .= "
";
$generatedScheduleHTML .= "| Date | Slot | Time | Course | Action |
|---|
";
// Loop through the schedule and generate table rows
foreach ($schedule as $row) {
$courseName = $courseNames[$row['Course_ID']] ?? "Course #" . $row['Course_ID'];
$dayName = (new DateTime($row['Date']))->format('l');
$slotIndex = (int) filter_var($row['Slot'], FILTER_SANITIZE_NUMBER_INT) - 1;
$slotName = format_time_slot_label($slotLabels[$slotIndex], $dayName);
// Add each row to the table
$generatedScheduleHTML .= "| {$row['Date']} | {$slotName} | {$row['Time']} | {$courseName} | - |
";
}
$generatedScheduleHTML .= "
";
// Return the generated schedule HTML as part of a JSON response
echo json_encode([
'status' => 'success', // Or 'error' if there's an issue
'html' => $generatedScheduleHTML // The HTML content to be rendered on the frontend
]);
exit; // Ensure no further output is sent after the JSON response
}
?>