/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/get_schedule_ai.php (37712B)
prepare("SELECT Program_ID, Program_Name FROM Programs");
$stmtGroup->execute();
$programData = $stmtGroup->get_result()->fetch_all(MYSQLI_ASSOC);
$stmtGroup->close();
// =========================================
// 3. Define Utility Functions (Important Modularization)
// =========================================
// Build calendar excluding holidays, retakes, and July
function build_schedule_calendar($Start_Date, $End_Date, $classDays, $timeSlots, $mysqli, $Program_ID, $Group_ID) {
$start = new DateTime($Start_Date);
$end = $End_Date ? new DateTime($End_Date) : new DateTime('+1 year');
$calendar = [];
$holidayDates = get_holidays($mysqli, $Start_Date);
$retakeDates = get_retake_dates($mysqli, $Program_ID, $Group_ID);
$cur = clone $start;
while ($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)) {
foreach ($timeSlots as $slot) {
$calendar[$dateStr][trim($slot)] = 'free';
}
}
$cur->modify('+1 day');
}
return $calendar;
}
// =========================================
// 4. Handle POST Request to Generate Schedule
// =========================================
// Format the label of the time slot, especially for Saturday sessions
function format_time_slot_label($slot, $day) {
if ($day === 'Saturday') {
if (stripos($slot, 'evening') !== false) return 'Morning (Sat)';
if (stripos($slot, 'morning') !== false) return 'Morning';
}
return $slot;
}
// Format the time range of the session based on the slot and the day
function format_time_slot_range($slot, $start, $end, $day) {
if ($day === 'Saturday') {
if (stripos($slot, 'morning') !== false) return "08:30 AM - 12:30 PM";
if (stripos($slot, 'evening') !== false) return "09:00 AM - 01:00 PM";
}
return date('h:i A', strtotime($start)) . ' - ' . date('h:i A', strtotime($end));
}
// Get group configuration from database
function get_group_info($mysqli, $Group_ID) {
$stmt = $mysqli->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;
}
// ✅ You can now call these functions in your main POST logic to keep code readable and modular.
$scheduleOutput = []; // Default
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
//if (isset($_POST['reset']) || isset($_POST['Start_Date'])) {
// unset($_SESSION['deletedSaturdays']);
//}
// Step 1: Retrieve Form Data
$Program_ID = intval($_POST['Program_ID']);
$Group_ID = intval($_POST['Group_ID']);
$Start_Date = $_POST['Start_Date'];
$End_Date = $_POST['End_Date'] ?? date('Y-m-d', strtotime('+1 year', strtotime($Start_Date)));
$selectedCourses = $_POST['Selected_Courses'] ?? [];
$courseSessions = $_POST['Course_Sessions'] ?? [];
$priorityCourses = $_POST['Priority_Course'] ?? [];
$group = get_group_info($mysqli, $Group_ID);
if (!$group) {
echo "Group info not found.";
exit;
}
$classDays = explode(',', $group['class_days']);
if (!$group['Weekend_Class'] && in_array('Saturday', $classDays)) {
$classDays = array_filter($classDays, fn($day) => $day !== 'Saturday');
}
$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);
$holidayDates = get_holidays($mysqli, $Start_Date);
$retakeDates = get_retake_dates($mysqli, $Program_ID, $Group_ID);
$validDates = generate_valid_dates($Start_Date, $End_Date, $classDays, $holidayDates, $retakeDates);
//echo "
📅 Valid Dates for Scheduling (Excluding July) "; print_r($validDates); echo " ";
// 🔄 Additional scheduling logic ....
// Output valid dates
//echo "
📅 Valid Dates for Scheduling (Excluding July) "; print_r($validDates); echo " ";
$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); // fallback to last available if out of bounds
//echo "
📅 Expected End Date Based on Andragogical Days ";
//echo "
$expectedEndDate (" . (new DateTime($expectedEndDate))->format('l') . ")
";
// Sort by course 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)
// Initialize
$schedule = [];
$remainingSessions = [];
foreach ($selectedCourses as $courseID) {
$remainingSessions[$courseID] = $courseSessions[$courseID] ?? 0;
}
// Queue of all courses
$courseQueue = $selectedCourses;
$currentCourses = [
0 => null, // Slot 1 (e.g. Morning)
1 => null // Slot 2 (e.g. Afternoon)
];
// Assign initial two courses
// Assign initial courses to slots without repeating
$courseQueueCopy = $selectedCourses;
$assignedCourses = [];
foreach ([0, 1] as $slotIndex) {
foreach ($courseQueueCopy as $courseID) {
if (!in_array($courseID, $assignedCourses) && $remainingSessions[$courseID] > 0) {
$currentCourses[$slotIndex] = $courseID;
$assignedCourses[] = $courseID;
break;
}
}
}
// Fetch all course names into array
$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) {
$totalSessions = $courseSessions; // Copy of original sessions
foreach ([0, 1] as $slotIndex) {
$from = $timeFrom[$slotIndex] ?? null;
$to = $timeTo[$slotIndex] ?? null;
if (!$from || !$to || !$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], $from, $to, (new DateTime($date))->format('l')),
'Course_ID' => $courseID
];
// Session info
$scheduledSoFar = $totalSessions[$courseID] - $remainingSessions[$courseID] + 1;
$total = $totalSessions[$courseID];
//echo "
✅ Scheduled $courseName (Course ID: $courseID) on $date (Slot " . ($slotIndex + 1) . ") — Session $scheduledSoFar of $total
";
// Decrease remaining
$remainingSessions[$courseID]--;
// If finished, pull a new course
if ($remainingSessions[$courseID] <= 0) {
$currentCourses[$slotIndex] = null;
while (!empty($courseQueue)) {
$nextCourse = array_shift($courseQueue);
if ($remainingSessions[$nextCourse] > 0) {
$currentCourses[$slotIndex] = $nextCourse;
break;
}
}
}
}
// Stop if both slots are out of courses
if (!$currentCourses[0] && !$currentCourses[1]) break;
}
// 🔍 Identify last scheduled session for each course
$lastSessions = [];
foreach ($schedule as $index => $entry) {
$courseID = $entry['Course_ID'];
$lastSessions[$courseID] = $index; // keeps overwriting until the last index
}
//echo "
📘 Final Generated Schedule "; print_r($schedule); echo " ";
//echo "
📋 Generated Schedule Table ";
//echo "
Group: " . htmlspecialchars($groupName) . "
";
if (!empty($schedule)) {
// Collect all displayed dates: scheduled, holidays, and retakes
$displayDates = [];
// 1. From scheduled sessions
foreach ($schedule as $row) {
$displayDates[$row['Date']][] = $row; // grouped by date
}
// 2. Add holidays (if no course scheduled on them)
foreach ($holidayDates as $hDate) {
if (!isset($displayDates[$hDate])) {
$displayDates[$hDate] = []; // will be handled below
}
}
// 3. Add retakes
foreach ($retakeDates as $rDate) {
if (!isset($displayDates[$rDate])) {
$displayDates[$rDate] = []; // will be handled below
}
}
// Sort the dates chronologically
ksort($displayDates);
} else {
echo "
🚫 No schedule generated — check if valid time slots, sessions, or dates are missing.
";
}
}
?>
Generate Group Schedule
"; // ✅ Start container
echo "
";
echo "Date Slot Time Course Action
$date $label ";
continue;
}
// Otherwise, loop through actual scheduled courses
foreach ($rows as $row) {
$courseName = $courseNames[$row['Course_ID']] ?? "Course ID {$row['Course_ID']}";
$index = array_search($row, $schedule);
$isExam = isset($lastSessions[$row['Course_ID']]) && $index === $lastSessions[$row['Course_ID']];
$style = "";
if ($isHoliday) {
$style = "style='background-color: #FFD700;'";
} elseif ($isRetake) {
$style = "style='background-color: #CCE5FF;'";
} elseif ($isExam) {
$style = "style='background-color: #FFCCCC; font-weight: bold;'";
}
//$slotName = $slotLabels[(int) filter_var($row['Slot'], FILTER_SANITIZE_NUMBER_INT) - 1];
$dayName = (new DateTime($row['Date']))->format('l');
$slotName = format_time_slot_label($slotLabels[(int) filter_var($row['Slot'], FILTER_SANITIZE_NUMBER_INT) - 1], $dayName);
$dateObj = new DateTime($row['Date']);
//$isSaturday = $dateObj->format('l') === 'Saturday';
$slotIndex = (int) filter_var($row['Slot'], FILTER_SANITIZE_NUMBER_INT) - 1;
//$dayName = (new DateTime($row['Date']))->format('l');
$isSaturday = ($dayName === 'Saturday');
echo "
($dayName) {$row['Date']}
{$slotName}
{$row['Time']}
{$courseName}" . ($isExam ? " (Exam Day)" : "") . "
";
if ($isSaturday) {
echo "🧹 Replace
";
}
}
echo "
";
echo "
"; // ✅ End container
}
?>