/home/techb158/workloadmatch.com/Manager
Edit: /home/techb158/workloadmatch.com/Manager/save_schedule_group_course.php (26301B)
'Invalid data format.']);
exit;
}
// 3. PREPARE STATEMENTS
// 3A. Fetch course name and duration
$nameStmt = $mysqli->prepare("
SELECT Course_Name, Course_Time
FROM Courses
WHERE Course_ID = ?
");
if (!$nameStmt) {
error_log("nameStmt error: " . $mysqli->error);
http_response_code(500);
exit(json_encode(['error' => 'Server error.']));
}
// 3B. Check for existing master row
// (10 placeholders: Program_ID, Course_ID, Group_ID, Time_Slot_ID, Course_Hours,
// Time_Slot, Start_Date, End_Date, Start_Time, End_Time)
$getMainStmt = $mysqli->prepare("
SELECT Schedule_ID
FROM course_group_schedule_main
WHERE Program_ID = ?
AND Course_ID = ?
AND Group_ID = ?
AND Time_Slot_ID = ?
AND Course_Hours = ?
AND Time_Slot = ?
AND Start_Date = ?
AND End_Date = ?
AND Start_Time = ?
AND End_Time = ?
");
if (!$getMainStmt) {
error_log("getMainStmt error: " . $mysqli->error);
http_response_code(500);
exit(json_encode(['error' => 'Server error.']));
}
// 3C. Insert new master row when needed (11 placeholders)
// (Program_ID, Course_ID, Group_ID, Time_Slot_ID, Course_Hours,
// Reserve_Course, Time_Slot, Start_Date, End_Date, Start_Time, End_Time)
$insertMainStmt = $mysqli->prepare("
INSERT INTO course_group_schedule_main
(Program_ID, Course_ID, Group_ID, Time_Slot_ID,
Course_Hours, Reserve_Course, Time_Slot,
Start_Date, End_Date, Start_Time, End_Time,
Assigned, Last_Updated)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NOW())
");
if (!$insertMainStmt) {
error_log("insertMainStmt error: " . $mysqli->error);
http_response_code(500);
exit(json_encode(['error' => 'Server error.']));
}
// 3D. Insert individual session rows (13 placeholders)
// (Schedule_ID, Program_ID, Course_ID, Group_ID, Group_Slot_ID,
// Course_Hours, Course_Name, Reserve_Course, Time_Slot,
// Start_Date, End_Date, Start_Time, End_Time)
$sessionStmt = $mysqli->prepare("
INSERT INTO schedule_course_for_group
(Schedule_ID, Program_ID, Course_ID, Group_ID, Group_Slot_ID,
Course_Hours, Course_Name, Reserve_Course, Time_Slot,
Start_Date, End_Date, Start_Time, End_Time,
Assigned, Last_Updated)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NOW())
");
if (!$sessionStmt) {
error_log("sessionStmt error: " . $mysqli->error);
http_response_code(500);
exit(json_encode(['error' => 'Server error.']));
}
// 4. GROUP ROWS BY UNIQUE ASSIGNMENT
$assignments = [];
foreach ($data as $row) {
// Create a unique key for each assignment
$key = implode('-', [
$row['program_id'],
$row['course_id'],
$row['group_id'],
$row['group_slot_id'],
$row['reserve_course'],
$row['time_slot'],
$row['start_time'],
$row['end_time']
]);
if (!isset($assignments[$key])) {
$assignments[$key] = [
'info' => $row,
'sessions' => []
];
}
$assignments[$key]['sessions'][] = $row;
if ($row['time_slot'] === 0 || $row['time_slot'] === '0') {
error_log("Zero time_slot detected: " . json_encode($row));
}
}
// 5. PROCESS EACH ASSIGNMENT
$insertedCount = 0;
foreach ($assignments as $assignment) {
$info = $assignment['info'];
// 5A. Fetch course name + duration
$nameStmt->bind_param('i', $info['course_id']);
$nameStmt->execute();
$nameStmt->bind_result($courseName, $courseHours);
$nameStmt->fetch();
$nameStmt->free_result();
// 5B. Look for existing master row
// Type string: 5×i (Program_ID, Course_ID, Group_ID, Time_Slot_ID, Course_Hours)
// 5×s (Time_Slot, Start_Date, End_Date, Start_Time, End_Time)
$getMainStmt->bind_param(
'iiiii' . 'sssss',
$info['program_id'], // i
$info['course_id'], // i
$info['group_id'], // i
$info['group_slot_id'], // i
$courseHours, // i
$info['time_slot'], // s
$info['start_date'], // s
$info['end_date'], // s
$info['start_time'], // s
$info['end_time'] // s
);
$getMainStmt->execute();
$getMainStmt->bind_result($mainScheduleID);
$foundMain = $getMainStmt->fetch();
$getMainStmt->free_result();
// 5C. If no master row, insert it
if (!$foundMain || !$mainScheduleID) {
$reserveCourse = $info['reserve_course']; // integer column
// Type string: 6×i (Program_ID, Course_ID, Group_ID, Time_Slot_ID, Course_Hours, Reserve_Course)
// 5×s (Time_Slot, Start_Date, End_Date, Start_Time, End_Time)
$insertMainStmt->bind_param(
'iiiiiisssss',
$info['program_id'], // i
$info['course_id'], // i
$info['group_id'], // i
$info['group_slot_id'], // i
$courseHours, // i
$reserveCourse, // i
$info['time_slot'], // s
$info['start_date'], // s
$info['end_date'], // s
$info['start_time'], // s
$info['end_time'] // s
);
if ($insertMainStmt->execute()) {
$mainScheduleID = $insertMainStmt->insert_id;
} else {
error_log("Failed to insert master: " . $insertMainStmt->error);
continue; // skip sessions for this assignment
}
}
// 5D. Insert each session row tied to $mainScheduleID
foreach ($assignment['sessions'] as $session) {
$reserveCourse = $session['reserve_course'];
// Correct column order:
// 1. Schedule_ID (i)
// 2. Program_ID (i)
// 3. Course_ID (i)
// 4. Group_ID (i)
// 5. Group_Slot_ID (i)
// 6. Course_Hours (i)
// 7. Course_Name (s)
// 8. Reserve_Course (i)
// 9. Time_Slot (s)
// 10. Start_Date (s)
// 11. End_Date (s)
// 12. Start_Time (s)
// 13. End_Time (s)
$sessionStmt->bind_param(
'iiiiiiissssss',
$mainScheduleID, // i
$session['program_id'], // i
$session['course_id'], // i
$session['group_id'], // i
$session['group_slot_id'], // i
$courseHours, // i
$courseName, // s
$reserveCourse, // i
$session['time_slot'], // s
$session['start_date'], // s
$session['end_date'], // s
$session['start_time'], // s
$session['end_time'] // s
);
if ($sessionStmt->execute()) {
$insertedCount++;
} else {
error_log("Session insert error: " . $sessionStmt->error
. " - " . json_encode($session));
}
}
}
// 6. CLEAN UP & RETURN RESPONSE
$sessionStmt->close();
$insertMainStmt->close();
$getMainStmt->close();
$nameStmt->close();
echo "✅ Inserted $insertedCount session rows into schedule_course_for_group, all linked to their correct Schedule_ID from course_group_schedule_main.";
// Return a success message with the number of inserted sessions
//echo json_encode([
// 'message' => "Inserted $insertedCount session rows successfully."
//]); */
// 1. INITIAL SETUP
include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
header('Content-Type: application/json');
ini_set('error_log', __DIR__ . '/schedule_error_log.txt');
// 2. READ & VALIDATE INPUT
$payload = file_get_contents('php://input');
$data = json_decode($payload, true);
if (!is_array($data)) {
error_log("Invalid payload: $payload");
http_response_code(400);
echo "Invalid data format.";
exit;
}
// 3. PREPARE STATEMENTS
// 3A. Fetch course name + duration
$nameStmt = $mysqli->prepare("
SELECT Course_Name, Course_Time
FROM Courses
WHERE Course_ID = ?
");
if (!$nameStmt) {
error_log("nameStmt prepare failed: " . $mysqli->error);
http_response_code(500);
exit("Server error.");
}
// 3B. Check for existing master row
// Now includes Start_Date AND End_Date (10 placeholders total)
$getMainStmt = $mysqli->prepare("
SELECT Schedule_ID
FROM course_group_schedule_main
WHERE Program_ID = ?
AND Course_ID = ?
AND Group_ID = ?
AND Time_Slot_ID = ?
AND Course_Hours = ?
AND Time_Slot = ?
AND Start_Date = ?
AND End_Date = ?
AND Start_Time = ?
AND End_Time = ?
");
if (!$getMainStmt) {
error_log("getMainStmt prepare failed: " . $mysqli->error);
http_response_code(500);
exit("Server error.");
}
// 3C. Insert a new master row when needed
// 11 placeholders (reserve_course plus the 10 other columns)
$insertMainStmt = $mysqli->prepare("
INSERT INTO course_group_schedule_main
(Program_ID, Course_ID, Group_ID, Time_Slot_ID,
Course_Hours, Reserve_Course, Time_Slot,
Start_Date, End_Date, Start_Time, End_Time,
Assigned, Last_Updated)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NOW())
");
if (!$insertMainStmt) {
error_log("insertMainStmt prepare failed: " . $mysqli->error);
http_response_code(500);
exit("Server error.");
}
// 3D. Insert individual session rows (13 placeholders total)
$sessionStmt = $mysqli->prepare("
INSERT INTO schedule_course_for_group
(Schedule_ID, Program_ID, Course_ID, Group_ID, Group_Slot_ID,
Course_Hours, Course_Name, Reserve_Course, Time_Slot,
Start_Date, End_Date, Start_Time, End_Time,
Assigned, Last_Updated)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NOW())
");
if (!$sessionStmt) {
error_log("sessionStmt prepare failed: " . $mysqli->error);
http_response_code(500);
exit("Server error.");
}
// 4. GROUP ROWS BY UNIQUE ASSIGNMENT
$assignments = [];
foreach ($data as $row) {
$key = implode('-', [
$row['program_id'],
$row['course_id'],
$row['group_id'],
$row['group_slot_id'],
$row['reserve_course'],
$row['time_slot'],
$row['start_time'],
$row['end_time']
]);
if (!isset($assignments[$key])) {
$assignments[$key] = [
'info' => $row,
'sessions' => []
];
}
$assignments[$key]['sessions'][] = $row;
if ($row['time_slot'] === 0 || $row['time_slot'] === '0') {
error_log("Zero time_slot detected: " . json_encode($row));
}
}
// 5. PROCESS EACH ASSIGNMENT
$insertedCount = 0;
foreach ($assignments as $assignment) {
$info = $assignment['info'];
// 5A. Fetch course name and course hours
$nameStmt->bind_param('i', $info['course_id']);
$nameStmt->execute();
$nameStmt->bind_result($course_name, $course_hours);
$fetched = $nameStmt->fetch();
$nameStmt->free_result();
if (! $fetched) {
error_log("No course found for Course_ID=" . $info['course_id']);
continue; // skip this assignment entirely
}
// 5B. Look for an existing master row
// type string = 5×i + 5×s = 'iiiii' . 'sssss'
$getMainStmt->bind_param(
'iiiii' . 'sssss',
$info['program_id'], // i
$info['course_id'], // i
$info['group_id'], // i
$info['group_slot_id'], // i
$course_hours, // i
$info['time_slot'], // s
$info['start_date'], // s
$info['end_date'], // s
$info['start_time'], // s
$info['end_time'] // s
);
$getMainStmt->execute();
$getMainStmt->bind_result($mainScheduleID);
$foundMain = $getMainStmt->fetch();
$getMainStmt->free_result();
// 5C. If no master row, insert it
if (! $foundMain || ! $mainScheduleID) {
$reserve_course = (int) $info['reserve_course'];
// type string = 6×i + 5×s = 'iiiiiisssss'
$insertMainStmt->bind_param(
'iiiiiisssss',
$info['program_id'], // i
$info['course_id'], // i
$info['group_id'], // i
$info['group_slot_id'], // i
$course_hours, // i
$reserve_course, // i
$info['time_slot'], // s
$info['start_date'], // s
$info['end_date'], // s
$info['start_time'], // s
$info['end_time'] // s
);
if ($insertMainStmt->execute()) {
$mainScheduleID = $insertMainStmt->insert_id;
} else {
error_log("Failed to insert master row: " . $insertMainStmt->error);
continue; // skip sessions for this assignment
}
}
// 5D. Insert each session row tied to $mainScheduleID
foreach ($assignment['sessions'] as $row) {
$reserve_course = (int) $row['reserve_course'];
// type string = 7×i + 6×s = 'iiiiiiissssss'
$sessionStmt->bind_param(
'iiiiiisisssss',
$mainScheduleID, // i
$row['program_id'], // i
$row['course_id'], // i
$row['group_id'], // i
$row['group_slot_id'], // i
$course_hours, // i
$course_name, // s
$reserve_course, // i
$row['time_slot'], // s
$row['start_date'], // s
$row['end_date'], // s
$row['start_time'], // s
$row['end_time'] // s
);
if ($sessionStmt->execute()) {
$insertedCount++;
} else {
error_log(
"Session insert failed: " .
$sessionStmt->error .
" - row: " . json_encode($row)
);
}
}
}
// 6. CLEAN UP & OUTPUT
$sessionStmt->close();
$insertMainStmt->close();
$getMainStmt->close();
$nameStmt->close();
// Assuming you already have $mysqli as your mysqli connection
$sql = "
UPDATE course_group_schedule_main AS main
JOIN (
SELECT
Schedule_ID,
Program_ID,
Course_ID,
Group_ID,
MAX(End_Date) AS MaxEndDate
FROM schedule_course_for_group
GROUP BY Schedule_ID, Program_ID, Course_ID, Group_ID
) AS scfg
ON main.Schedule_ID = scfg.Schedule_ID
AND main.Program_ID = scfg.Program_ID
AND main.Course_ID = scfg.Course_ID
AND main.Group_ID = scfg.Group_ID
SET main.End_Date = scfg.MaxEndDate
";
// Run the query
if ($mysqli->query($sql)) {
// echo "course_group_schedule_main updated successfully.";
} else {
echo "Error updating records: " . $mysqli->error;
}
echo "✅ Inserted $insertedCount session rows into schedule_course_for_group.";
/* include_once '../includes/db_connect.php';
include_once '../includes/functions.php';
sec_session_start();
ini_set('error_log', __DIR__ . '/schedule_error_log.txt');
$payload = file_get_contents('php://input');
$data = json_decode($payload, true);
if (!is_array($data)) {
error_log("⌠Invalid input payload: $payload");
http_response_code(400);
echo "Invalid data format.";
exit;
}
//error_log("✅ Decoded data structure: " . print_r($data, true));
//
//exit();
//exit("Server error.");
$nameStmt = $mysqli->prepare("SELECT Course_Name FROM Courses WHERE Course_ID = ?");
if (! $nameStmt) {
error_log("⌠Course name lookup failed: " . $mysqli->error);
http_response_code(500);
exit("Server error.");
}
$stmt = $mysqli->prepare("INSERT INTO schedule_course_for_group
(Schedule_ID, Program_ID, Course_ID, Group_ID, Group_Slot_ID, Course_Name,
Reserve_Course, Time_Slot, Start_Date, End_Date, Start_Time, End_Time,
Assigned, Last_Updated)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NOW())");
if (! $stmt) {
error_log("⌠Insert prepare failed: " . $mysqli->error);
http_response_code(500);
exit("Server error.");
}
$inserted = 0;
$grouped = [];
// Fetch the last Schedule_ID
$result = $mysqli->query("SELECT MAX(Schedule_ID) AS LastID FROM schedule_course_for_group");
$row = $result->fetch_assoc();
$currentScheduleID = (int) $row['LastID'];
$checkDuplicateStmt = $mysqli->prepare("
SELECT COUNT(*) FROM schedule_course_for_group
WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ?
AND Time_Slot = ? AND Start_Date = ? AND End_Date = ?
AND Start_Time = ? AND End_Time = ?
");
if (! $checkDuplicateStmt) {
error_log("⌠Failed to prepare checkDuplicateStmt: " . $mysqli->error);
exit("Server error.");
}
foreach ($data as $row) {
$program_id = (int) $row['program_id'];
$course_id = (int) $row['course_id'];
$group_id = (int) $row['group_id'];
$group_slot_id = (int) $row['group_slot_id'];
$reserve_course = (int) $row['reserve_course'];
$time_slot = trim($row['time_slot'] ?? '');
$start_date = trim($row['start_date'] ?? '');
$end_date = trim($row['end_date'] ?? '') ?: $start_date;
$start_time = trim($row['start_time'] ?? '');
$end_time = trim($row['end_time'] ?? '');
if (!$program_id || !$course_id || !$group_id || !$group_slot_id || $time_slot === '' || $start_date === '') {
error_log("âš ï¸ Skipping invalid row: " . json_encode($row));
continue;
}
// Lookup course name
$nameStmt->bind_param('i', $course_id);
$nameStmt->execute();
$nameStmt->bind_result($course_name);
$nameStmt->fetch();
$nameStmt->free_result();
// Always add to grouped list (even if duplicate in session table)
$key = "$program_id-$course_id-$group_id-$time_slot-$start_time-$end_time";
if (!isset($grouped[$key])) {
$grouped[$key] = [
'program_id' => $program_id,
'course_id' => $course_id,
'group_id' => $group_id,
'group_slot_id' => $group_slot_id,
'reserve_course' => $reserve_course,
'time_slot' => $time_slot,
'start_time' => $start_time,
'end_time' => $end_time,
'start_date' => $start_date,
'end_date' => $end_date,
];
} else {
$grouped[$key]['start_date'] = min($grouped[$key]['start_date'], $start_date);
$grouped[$key]['end_date'] = max($grouped[$key]['end_date'], $end_date);
}
// Check for duplicate
$checkDuplicateStmt->bind_param(
'iiisssss',
$program_id,
$course_id,
$group_id,
$time_slot,
$start_date,
$end_date,
$start_time,
$end_time
);
$checkDuplicateStmt->execute();
$checkDuplicateStmt->bind_result($exists);
$checkDuplicateStmt->fetch();
$checkDuplicateStmt->free_result();
if ($exists > 0) {
error_log("âš ï¸ Duplicate row skipped (already exists): " . json_encode($row));
continue; // Skip inserting into session table
}
// Insert new session
$currentScheduleID++;
$stmt->bind_param(
'iiiiisisssss',
$currentScheduleID,
$program_id,
$course_id,
$group_id,
$group_slot_id,
$course_name,
$reserve_course,
$time_slot,
$start_date,
$end_date,
$start_time,
$end_time
);
if ($stmt->execute()) {
$inserted++;
} else {
error_log("⌠Insert failed: " . $stmt->error . " - row: " . json_encode($row));
}
}
// Final: Insert into course_group_schedule_main
$checkStmt = $mysqli->prepare("
SELECT COUNT(*) FROM course_group_schedule_main
WHERE Program_ID = ? AND Course_ID = ? AND Group_ID = ?
AND Time_Slot = ? AND Start_Time = ? AND End_Time = ?
");
$insertStmt2 = $mysqli->prepare("
INSERT INTO course_group_schedule_main
(Program_ID, Course_ID, Group_ID, Time_Slot_ID, Reserve_Course, Time_Slot,
Start_Date, End_Date, Start_Time, End_Time, Assigned, Last_Updated)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NOW())");
$mainInserted = 0;
foreach ($grouped as $g) {
$checkStmt->bind_param(
'iiisss',
$g['program_id'],
$g['course_id'],
$g['group_id'],
$g['time_slot'],
$g['start_time'],
$g['end_time']
);
$checkStmt->execute();
$checkStmt->bind_result($count);
$checkStmt->fetch();
$checkStmt->free_result();
if ($count == 0) {
$insertStmt2->bind_param(
'iiiissssss',
$g['program_id'],
$g['course_id'],
$g['group_id'],
$g['group_slot_id'],
$g['reserve_course'],
$g['time_slot'],
$g['start_date'],
$g['end_date'],
$g['start_time'],
$g['end_time']
);
if ($insertStmt2->execute()) {
$mainInserted++;
} else {
error_log("⌠Failed to insert into Main: " . $insertStmt2->error);
}
}
}
$stmt->close();
$insertStmt2->close();
$checkStmt->close();
$nameStmt->close();
$checkDuplicateStmt->close();
echo "✅ Inserted $inserted rows into schedule_course_for_group. Inserted $mainInserted grouped rows into course_group_schedule_main.";
*/
//echo "✅ Saved all session rows. Inserted $mainInserted new grouped rows into course_group_schedule_main.";
//echo "✅ Inserted $inserted session rows into schedule_course_for_group. Inserted $mainInserted grouped rows into course_group_schedule_main.";
/*****
// Enable error logging
//ini_set('log_errors', 1);
ini_set('error_log', __DIR__ . '/schedule_error_log.txt');
// Read & decode incoming JSON
$payload = file_get_contents('php://input');
$data = json_decode($payload, true);
if (!is_array($data)) {
error_log("⌠Invalid input payload: $payload");
http_response_code(400);
echo "Invalid data format.";
exit;
}
// Prepare a statement to look up Course_Name
$nameStmt = $mysqli->prepare(
"SELECT Course_Name
FROM Courses
WHERE Course_ID = ?"
);
if (! $nameStmt) {
error_log("⌠Name-lookup prepare failed: " . $mysqli->error);
http_response_code(500);
echo "Server error.";
exit;
}
// Prepare the insert (now including Course_Name)
$sql = "INSERT INTO schedule_course_for_group
(Program_ID, Course_ID, Group_ID, Group_Slot_ID, Course_Name,
Reserve_Course, Time_Slot, Start_Date, End_Date, Start_Time, End_Time,
Assigned, Last_Updated)
VALUES
(?, ?, ?, ?, ?,
?, ?, ?, ?, ?,
?, 0, NOW())";
$stmt = $mysqli->prepare($sql);
if (! $stmt) {
error_log("⌠Insert-prepare failed: " . $mysqli->error);
http_response_code(500);
echo "Server error.";
exit;
}
$inserted = 0;
foreach ($data as $row) {
// pull out & sanitize
$program_id = (int) $row['program_id'];
$course_id = (int) $row['course_id'];
$group_id = (int) $row['group_id'];
$group_slot_id = (int) $row['group_slot_id'];
$reserve_course = (int) $row['reserve_course'];
$time_slot = trim($row['time_slot'] ?? '');
$start_date = trim($row['start_date'] ?? '');
$end_date = trim($row['end_date'] ?? '');
$start_time = trim($row['start_time'] ?? '');
$end_time = trim($row['end_time'] ?? '');
// default end_date to start_date if empty
if ($end_date === '') {
$end_date = $start_date;
}
// basic sanity check
if (! $program_id
|| ! $course_id
|| ! $group_id
|| ! $group_slot_id
|| $time_slot === ''
|| $start_date === ''
|| $end_date === ''
) {
error_log("âš ï¸ Skipping invalid row: " . json_encode($row));
continue;
}
// 1) look up Course_Name
$nameStmt->bind_param('i', $course_id);
$nameStmt->execute();
$nameStmt->bind_result($course_name);
if (! $nameStmt->fetch()) {
$course_name = ''; // or some default
}
$nameStmt->free_result();
// 2) insert
$stmt->bind_param(
'iiiisisssss',
$program_id, // i
$course_id, // i
$group_id, // i
$group_slot_id, // i
$course_name, // s
$reserve_course,// i
$time_slot, // s
$start_date, // s
$end_date, // s
$start_time, // s
$end_time // s
);
if ($stmt->execute()) {
$inserted++;
} else {
error_log("⌠Insert failed: " . $stmt->error . " - row: " . json_encode($row));
}
}
$stmt->close();
$nameStmt->close();
echo "✅ Inserted $inserted row(s) into schedule_course_for_group.";
**/