prepare($sql);
if (!$stmtGroup) {
die("Error preparing group query: " . $mysqli->error);
}
// Bind parameters in the same order as the placeholders (Teacher_ID first, then Program_ID).
$stmtGroup->bind_param("ii", $Teacher_ID, $Program_ID);
$stmtGroup->execute();
$resultGroup = $stmtGroup->get_result();
$stmtGroup->close();
// Check if any rows were returned.
if ($resultGroup->num_rows === 0) {
echo "
";
$foundAssignment = true;
// If multiple assignments apply, additional rows could be output.
}
}
if (!$foundAssignment) {
// For Free days, output a row in a different color (e.g., light blue).
echo "
" . htmlspecialchars($currentDate) . "
No Class
-
-
Free
";
}
}
}
*/
/*
if (isset($_POST["Program_ID"]) && isset($_POST["Group_ID"])&& isset($_POST["Teacher_ID"])) {
// Get parameters via GET (or fixed for testing)
//$Program_ID = isset($_GET['Program_ID']) ? intval($_GET['Program_ID']) : 1;
//$Group_ID = isset($_GET['Group_ID']) ? intval($_GET['Group_ID']) : 4;
//$Teacher_ID = isset($_GET['Teacher_ID']) ? intval($_GET['Teacher_ID']) : 250598;
$Program_ID = $_POST['Program_ID'];
$Group_ID = $_POST['Group_ID'];
$Teacher_ID = $_POST['Teacher_ID'];
if ($Program_ID <= 0 || $Group_ID <= 0 || $Teacher_ID <= 0) {
die("Invalid parameters.");
}
// ---------------------------------------------------
// 1. Retrieve group details from Manager_Group_Name
// ---------------------------------------------------
$groupQuery = "SELECT * FROM Manager_Group_Name WHERE Group_ID = ? AND Program_ID = ?";
$stmtGroup = $mysqli->prepare($groupQuery);
if (!$stmtGroup) {
die("Error preparing group query: " . $mysqli->error);
}
$stmtGroup->bind_param("ii", $Group_ID, $Program_ID);
$stmtGroup->execute();
$resultGroup = $stmtGroup->get_result();
$groupData = $resultGroup->fetch_assoc();
$stmtGroup->close();
if (!$groupData) {
die("Group not found.");
}
// Expected columns: Time_Slot, Time_From, Time_To, Weekend_Class
$groupTimeSlot = trim($groupData['Time_Slot']); // e.g., "Morning,Afternoon" or "Morning"
$groupStartTime = $groupData['Time_From']; // e.g., "08:30:00"
$groupEndTime = $groupData['Time_To']; // e.g., "15:30:00"
$weekendClass = isset($groupData['Weekend_Class']) ? intval($groupData['Weekend_Class']) : 0; // 0 = no Saturday class
// Split the group's time slot field into periods.
$groupPeriods = array_map('trim', explode(',', $groupTimeSlot));
if (count($groupPeriods) > 1) {
$firstPeriodStr = $groupPeriods[0]; // e.g., "Morning"
$secondPeriodStr = $groupPeriods[1]; // e.g., "Afternoon"
$groupStartDT = new DateTime($groupStartTime);
$groupEndDT = new DateTime($groupEndTime);
// For the first period, assume duration of 3 hours from group start.
$firstPeriodStart = clone $groupStartDT;
$firstPeriodEnd = clone $groupStartDT;
$firstPeriodEnd->modify('+3 hours'); // e.g., 08:30 -> 11:30
// For the second period, assume duration of 3 hours before group end.
$secondPeriodEnd = clone $groupEndDT;
$secondPeriodStart = clone $groupEndDT;
$secondPeriodStart->modify('-3 hours'); // e.g., 15:30 -> 12:30
} else {
$singlePeriod = $groupPeriods[0]; // e.g., "Morning"
$singlePeriodStart = new DateTime($groupStartTime);
$singlePeriodEnd = new DateTime($groupEndTime);
}
// ---------------------------------------------------
// 2. Retrieve assignments for this teacher
// ---------------------------------------------------
$query = "SELECT tca.*, c.Course_Name, c.Course_Time
FROM Teacher_Course_Assignments tca
JOIN Courses c ON tca.Course_ID = c.Course_ID
WHERE tca.Program_ID = ?
AND tca.Group_ID = ?
AND tca.Teacher_ID = ?
ORDER BY tca.Assigned_At DESC";
$stmt = $mysqli->prepare($query);
if (!$stmt) {
die("Error preparing assignment query: " . $mysqli->error);
}
$stmt->bind_param("iii", $Program_ID, $Group_ID, $Teacher_ID);
if (!$stmt->execute()) {
die("Error executing assignment query: " . $stmt->error);
}
$result = $stmt->get_result();
$assignments = [];
while ($row = $result->fetch_assoc()) {
$assignments[] = $row;
}
$stmt->close();
// ---------------------------------------------------
// 3. Retrieve holiday events from the Events table
// ---------------------------------------------------
$eventQuery = "SELECT Event_Title, Event_Color, Event_Start, Event_End
FROM Events";
$stmtEvent = $mysqli->prepare($eventQuery);
if ($stmtEvent) {
//$stmtEvent->bind_param("i", $Program_ID);
$stmtEvent->execute();
$eventResult = $stmtEvent->get_result();
$holidayMapping = []; // key: date (Y-m-d) => array of event details
while ($event = $eventResult->fetch_assoc()) {
// Generate dates from Event_Start to Event_End (inclusive)
$startEvent = new DateTime($event['Event_Start']);
$endEvent = new DateTime($event['Event_End']);
$endEvent->modify('+1 day'); // include the last day
$interval = new DateInterval('P1D');
$period = new DatePeriod($startEvent, $interval, $endEvent);
foreach ($period as $dt) {
$d = $dt->format('Y-m-d');
$holidayMapping[$d][] = [
'title' => $event['Event_Title'],
'color' => $event['Event_Color']
];
}
}
$stmtEvent->close();
} else {
$holidayMapping = [];
}
// ---------------------------------------------------
// 4. Retrieve retake dates from Retake_Records table.
// ---------------------------------------------------
$retakeQuery = "SELECT Retake_Date FROM Retake_Records WHERE Program_ID = ? AND Group_ID = ?";
$stmtRetake = $mysqli->prepare($retakeQuery);
if ($stmtRetake) {
$stmtRetake->bind_param("ii", $Program_ID, $Group_ID);
$stmtRetake->execute();
$retakeResult = $stmtRetake->get_result();
$retakes = [];
while ($r = $retakeResult->fetch_assoc()) {
$retakes[] = $r['Retake_Date'];
}
$stmtRetake->close();
} else {
$retakes = [];
}
// ---------------------------------------------------
// 5. Determine a global date range for the report (based on assignments).
// ---------------------------------------------------
if (!empty($assignments)) {
$globalStart = new DateTime($assignments[0]['Start_Date']);
$globalEnd = new DateTime($assignments[0]['End_Date']);
foreach ($assignments as $assignment) {
$d1 = new DateTime($assignment['Start_Date']);
$d2 = new DateTime($assignment['End_Date']);
if ($d1 < $globalStart) {
$globalStart = $d1;
}
if ($d2 > $globalEnd) {
$globalEnd = $d2;
}
}
$globalEnd->modify('+1 day'); // Make inclusive
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
} else {
$globalStart = new DateTime();
$globalEnd = clone $globalStart;
$globalEnd->modify('+30 days');
$globalPeriod = new DatePeriod($globalStart, new DateInterval('P1D'), $globalEnd);
}
// ---------------------------------------------------
// 6. Generate the day-by-day report.
// ---------------------------------------------------
//echo "
Daily Course Schedule Report for Teacher $Teacher_ID, Group $Group_ID, Program $Program_ID
";
//echo "
";
//echo "
//
Date
//
Course Name / Status
//
Time Slot
//
Time
//
Type
//
";
foreach ($globalPeriod as $date) {
$currentDate = $date->format('Y-m-d');
$dayOfWeek = $date->format('N'); // ISO-8601: 6 = Saturday, 7 = Sunday
// --- Weekend Check ---
if ($dayOfWeek == 7) { // Always skip Sunday.
continue;
}
if ($dayOfWeek == 6 && $weekendClass == 0) {
continue;
}
// --- Exclusion Check: If current date is a holiday or retake, output those rows and skip class row.
if (isset($holidayMapping[$currentDate])) {
foreach ($holidayMapping[$currentDate] as $event) {
echo "
" . htmlspecialchars($currentDate) . "
" . htmlspecialchars($event['title']) . "
-
-
Holiday
";
}
// Skip regular class row for this date.
continue;
}
if (in_array($currentDate, $retakes)) {
echo "
" . htmlspecialchars($currentDate) . "
Retake Day
-
-
Retake
";
continue;
}
// --- Regular Class Days ---
$foundAssignment = false;
foreach ($assignments as $assignment) {
$assignStart = new DateTime($assignment['Start_Date']);
$assignEnd = new DateTime($assignment['End_Date']);
$assignEnd->modify('+1 day'); // Make inclusive
if ($date >= $assignStart && $date < $assignEnd) {
// Determine time range based on assignment's Time_Slot.
$timeRange = "";
if (count($groupPeriods) > 1) {
if ($assignment['Time_Slot'] === $firstPeriodStr) {
$timeRange = $firstPeriodStart->format("h:i A") . " - " . $firstPeriodEnd->format("h:i A");
} elseif ($assignment['Time_Slot'] === $secondPeriodStr) {
$timeRange = $secondPeriodStart->format("h:i A") . " - " . $secondPeriodEnd->format("h:i A");
} else {
$timeRange = "N/A";
}
} else {
$timeRange = $singlePeriodStart->format("h:i A") . " - " . $singlePeriodEnd->format("h:i A");
}
echo "