/home/techb158/workloadmatch.com/api/routes
Edit: /home/techb158/workloadmatch.com/api/routes/groups.php (11099B)
prepare($sql);
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$stmt->bind_result($total);
$stmt->fetch();
$stmt->close();
$sql = "SELECT g.*, p.Program_Name FROM manager_group_name g LEFT JOIN Programs p ON p.Program_ID = g.Program_ID $whereClause ORDER BY $sort $dir LIMIT ? OFFSET ?";
$stmt = $mysqli->prepare($sql);
$bindParams = array_merge($params, [$perPage, $offset]);
$bindTypes = $types . 'ii';
if ($bindParams) $stmt->bind_param($bindTypes, ...$bindParams);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
Response::paginated($items, $total, $page, $perPage);
}
function getGroup(string $id, mysqli $mysqli): void
{
Auth::requireLogin();
$stmt = $mysqli->prepare("SELECT g.*, p.Program_Name FROM manager_group_name g LEFT JOIN Programs p ON p.Program_ID = g.Program_ID WHERE g.Group_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
if (!$item) Response::notFound('Group not found');
// Get time slots for this group
$stmt = $mysqli->prepare("SELECT * FROM group_slot_mapping WHERE Group_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item['time_slots'] = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// Get teacher count for this group
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_profile WHERE Group_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($teacherCount);
$stmt->fetch();
$stmt->close();
$item['teacher_count'] = $teacherCount;
Response::success($item);
}
function createGroup(?array $body, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile', 'manager_profile']);
$groupName = trim($body['Group_Name'] ?? '');
$programId = $body['Program_ID'] ?? null;
$groupYear = $body['Group_Year'] ?? '';
$weekendClass = (int)($body['Weekend_Class'] ?? 0);
$timeSlotIds = $body['Time_Slot_IDs'] ?? [];
$classDays = $body['class_days'] ?? 'Monday,Tuesday,Wednesday,Thursday,Friday';
if (!$groupName || !$programId) {
Response::validationError(['Group_Name' => 'Required', 'Program_ID' => 'Required']);
}
// Get IDs from session
$userType = Auth::getUserType();
$userId = Auth::getUserId();
if ($userType === 'master_profile') {
$q = $mysqli->prepare("SELECT Admin_ID, Master_ID FROM master_profile WHERE Master_ID = ?");
$q->bind_param('s', $userId);
$q->execute();
$r = $q->get_result()->fetch_assoc();
$q->close();
$adminId = $r['Admin_ID'];
$masterId = $r['Master_ID'];
$managerId = 0;
} else {
$q = $mysqli->prepare("SELECT Admin_ID, Master_ID, Manager_ID FROM manager_profile WHERE Manager_ID = ?");
$q->bind_param('s', $userId);
$q->execute();
$r = $q->get_result()->fetch_assoc();
$q->close();
$adminId = $r['Admin_ID'];
$masterId = $r['Master_ID'];
$managerId = $r['Manager_ID'];
}
// Check duplicate
$stmt = $mysqli->prepare("SELECT Group_ID FROM manager_group_name WHERE Group_Name = ? AND Program_ID = ?");
$stmt->bind_param('si', $groupName, $programId);
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows > 0) {
$stmt->close();
Response::error('Group name already exists in this program', 409);
}
$stmt->close();
// Get time slot details
$timeSlotCombined = '';
$earliestTime = null;
$latestTime = null;
$timeSlotDetails = [];
if (!empty($timeSlotIds)) {
$ids = implode(',', array_map('intval', $timeSlotIds));
$result = $mysqli->query("SELECT * FROM time_slot_programs WHERE time_slot_programs_ID IN ($ids)");
$slots = [];
while ($row = $result->fetch_assoc()) {
$slots[] = $row['Time_Slot'];
$timeSlotDetails[] = $row;
if ($earliestTime === null || strtotime($row['Time_From']) < strtotime($earliestTime)) {
$earliestTime = $row['Time_From'];
}
if ($latestTime === null || strtotime($row['Time_To']) > strtotime($latestTime)) {
$latestTime = $row['Time_To'];
}
}
$timeSlotCombined = implode(',', $slots);
}
$dateTime = date('Y-m-d H:i:s');
$stmt = $mysqli->prepare("INSERT INTO manager_group_name (Admin_ID, Master_ID, Manager_ID, Program_ID, Weekend_Class, Group_Name, Group_Year, Time_Slot, Time_From, Time_To, Date_Time, class_days) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param('iiiissssssss', $adminId, $masterId, $managerId, $programId, $weekendClass, $groupName, $groupYear, $timeSlotCombined, $earliestTime, $latestTime, $dateTime, $classDays);
$stmt->execute();
$newId = $stmt->insert_id;
$stmt->close();
// Insert slot mappings
foreach ($timeSlotDetails as $slot) {
$stmt = $mysqli->prepare("INSERT INTO group_slot_mapping (Group_ID, Time_Slot_ID, time_slot_programs_ID, Time_Slot, Time_From, Time_To) VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param('iiisss', $newId, $slot['Time_Slot_ID'], $slot['time_slot_programs_ID'], $slot['Time_Slot'], $slot['Time_From'], $slot['Time_To']);
$stmt->execute();
$stmt->close();
}
log_activity($mysqli, 'create', 'group', $newId, $groupName, 'Group created via API');
$stmt = $mysqli->prepare("SELECT g.*, p.Program_Name FROM manager_group_name g LEFT JOIN Programs p ON p.Program_ID = g.Program_ID WHERE g.Group_ID = ?");
$stmt->bind_param('i', $newId);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
Response::created($item);
}
function updateGroup(?string $id, ?array $body, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile', 'manager_profile']);
if (!$id) Response::error('Group ID required');
$stmt = $mysqli->prepare("SELECT * FROM manager_group_name WHERE Group_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$existing = $result->fetch_assoc();
$stmt->close();
if (!$existing) Response::notFound('Group not found');
$groupName = trim($body['Group_Name'] ?? $existing['Group_Name']);
$programId = $body['Program_ID'] ?? $existing['Program_ID'];
$groupYear = $body['Group_Year'] ?? $existing['Group_Year'];
$weekendClass = (int)($body['Weekend_Class'] ?? $existing['Weekend_Class']);
$classDays = $body['class_days'] ?? $existing['class_days'];
$stmt = $mysqli->prepare("UPDATE manager_group_name SET Group_Name = ?, Program_ID = ?, Group_Year = ?, Weekend_Class = ?, class_days = ? WHERE Group_ID = ?");
$stmt->bind_param('sisisi', $groupName, $programId, $groupYear, $weekendClass, $classDays, $id);
$stmt->execute();
$stmt->close();
log_activity($mysqli, 'update', 'group', $id, $groupName, 'Group updated via API');
$stmt = $mysqli->prepare("SELECT g.*, p.Program_Name FROM manager_group_name g LEFT JOIN Programs p ON p.Program_ID = g.Program_ID WHERE g.Group_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
Response::success($item, 'Group updated');
}
function deleteGroup(?string $id, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile']);
if (!$id) Response::error('Group ID required');
$stmt = $mysqli->prepare("SELECT Group_Name FROM manager_group_name WHERE Group_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($name);
$stmt->fetch();
$stmt->close();
if (!$name) Response::notFound('Group not found');
// Check for related teachers
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_profile WHERE Group_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($tCount);
$stmt->fetch();
$stmt->close();
if ($tCount > 0) {
Response::error("Cannot delete group: $tCount teacher(s) are assigned to it", 409);
}
// Check for related schedules
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM schedule_course_for_group WHERE Group_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($sCount);
$stmt->fetch();
$stmt->close();
if ($sCount > 0) {
Response::error("Cannot delete group: $sCount schedule(s) reference it", 409);
}
// Delete slot mappings
$stmt = $mysqli->prepare("DELETE FROM group_slot_mapping WHERE Group_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();
// Delete group
$stmt = $mysqli->prepare("DELETE FROM manager_group_name WHERE Group_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();
log_activity($mysqli, 'delete', 'group', $id, $name, 'Group deleted via API');
Response::success(null, 'Group deleted');
}