/home/techb158/workloadmatch.com/api/routes
Edit: /home/techb158/workloadmatch.com/api/routes/teachers.php (16013B)
prepare($sql);
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$stmt->bind_result($total);
$stmt->fetch();
$stmt->close();
$sql = "SELECT t.Teacher_ID, t.Admin_ID, t.Master_ID, t.Manager_ID, t.Group_ID, t.Seniority_ID,
t.First_Name, t.Last_Name, t.F_Name, t.L_Name, t.User_Name, t.Email, t.Phone,
t.User_Access, t.Profile_Status, t.prof_lang, t.Note, t.Load_Hours,
t.Reg_Date, t.Login_Date, t.Logout_Date,
g.Group_Name
FROM teacher_profile t
LEFT JOIN manager_group_name g ON g.Group_ID = t.Group_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();
// Remove sensitive fields
foreach ($items as &$item) {
unset($item['Password'], $item['salt']);
}
Response::paginated($items, $total, $page, $perPage);
}
function getTeacher(string $id, mysqli $mysqli): void
{
Auth::requireLogin();
$stmt = $mysqli->prepare("
SELECT t.*, g.Group_Name, p.Program_Name
FROM teacher_profile t
LEFT JOIN manager_group_name g ON g.Group_ID = t.Group_ID
LEFT JOIN Programs p ON p.Program_ID = t.Program_ID
WHERE t.Teacher_ID = ?
");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
if (!$item) Response::notFound('Teacher not found');
unset($item['Password'], $item['salt']);
// Get restricted groups
$stmt = $mysqli->prepare("SELECT Group_ID FROM teacher_restricted_groups WHERE Teacher_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item['restricted_groups'] = array_map(function($r) { return (int)$r['Group_ID']; }, $result->fetch_all(MYSQLI_ASSOC));
$stmt->close();
Response::success($item);
}
function createTeacher(?array $body, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile', 'manager_profile']);
$firstName = trim($body['First_Name'] ?? '');
$lastName = trim($body['Last_Name'] ?? '');
$email = trim($body['Email'] ?? '');
$username = trim($body['username'] ?? $body['User_Name'] ?? '');
$password = $body['password'] ?? $body['p'] ?? '';
$phone = $body['Phone'] ?? '';
$seniority = $body['Seniority_ID'] ?? $body['Seniority_level'] ?? null;
$groupId = $body['Group_ID'] ?? null;
$profLang = $body['prof_lang'] ?? 'en';
$loadHours = $body['Load_Hours'] ?? null;
$note = $body['Note'] ?? '';
$userAccess = (int)($body['User_Access'] ?? 1);
if (!$firstName || !$lastName || !$email || !$password) {
Response::validationError(['First_Name', 'Last_Name', 'Email', 'password' => 'Required']);
}
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
Response::validationError(['Email' => 'Invalid email format']);
}
$passwordHash = password_hash($password, PASSWORD_BCRYPT);
// Check for existing username/email
foreach (['teacher_profile' => 'User_Name', 'manager_profile' => 'User_Name', 'master_profile' => 'User_Name', 'admin_profile' => 'User_Name'] as $table => $col) {
$stmt = $mysqli->prepare("SELECT 1 FROM $table WHERE $col = ? LIMIT 1");
$stmt->bind_param('s', $username);
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows > 0) {
$stmt->close();
Response::error("Username '$username' already exists", 409);
}
$stmt->close();
}
// Generate unique ID
$numberId = mt_rand(10000, 999999);
// Get hierarchy IDs
$userType = Auth::getUserType();
$userId = Auth::getUserId();
$adminId = $masterId = $managerId = 0;
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'];
} 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'];
}
$dateTime = date('Y/m/d H:i:s');
$stmt = $mysqli->prepare("INSERT INTO teacher_profile (Teacher_ID, Admin_ID, Master_ID, Manager_ID, User_Type_ID, Seniority_ID, First_Name, Last_Name, User_Name, Email, Phone, Password, User_Access, Reg_Date, Login_Date, Logout_Date, prof_lang, Note, Load_Hours, Group_ID) VALUES (?, ?, ?, ?, 3, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param('sssssssssssssssssss', $numberId, $adminId, $masterId, $managerId, $seniority, $firstName, $lastName, $username, $email, $phone, $passwordHash, $userAccess, $dateTime, $dateTime, $dateTime, $profLang, $note, $loadHours, $groupId);
$stmt->execute();
$newId = $numberId;
$stmt->close();
// Handle restricted groups
if (!empty($body['Restricted_Groups']) && is_array($body['Restricted_Groups'])) {
foreach ($body['Restricted_Groups'] as $groupID) {
$stmt = $mysqli->prepare("INSERT INTO teacher_restricted_groups (Teacher_ID, Group_ID) VALUES (?, ?)");
$stmt->bind_param('si', $newId, $groupID);
$stmt->execute();
$stmt->close();
}
}
log_activity($mysqli, 'create', 'teacher', $newId, "$firstName $lastName", 'Teacher created via API');
$stmt = $mysqli->prepare("SELECT * FROM teacher_profile WHERE Teacher_ID = ?");
$stmt->bind_param('s', $newId);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
unset($item['Password'], $item['salt']);
Response::created($item);
}
function updateTeacher(?string $id, ?array $body, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile', 'manager_profile']);
if (!$id) Response::error('Teacher ID required');
$stmt = $mysqli->prepare("SELECT * FROM teacher_profile WHERE Teacher_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$existing = $result->fetch_assoc();
$stmt->close();
if (!$existing) Response::notFound('Teacher not found');
$firstName = trim($body['First_Name'] ?? $existing['First_Name']);
$lastName = trim($body['Last_Name'] ?? $existing['Last_Name']);
$email = trim($body['Email'] ?? $existing['Email']);
$phone = $body['Phone'] ?? $existing['Phone'];
$seniority = $body['Seniority_ID'] ?? $existing['Seniority_ID'];
$groupId = $body['Group_ID'] ?? $existing['Group_ID'];
$profLang = $body['prof_lang'] ?? $existing['prof_lang'];
$loadHours = $body['Load_Hours'] ?? $existing['Load_Hours'];
$note = $body['Note'] ?? $existing['Note'];
$userAccess = (int)($body['User_Access'] ?? $existing['User_Access']);
$profileStatus = $body['Profile_Status'] ?? $existing['Profile_Status'] ?? '';
$stmt = $mysqli->prepare("UPDATE teacher_profile SET First_Name = ?, Last_Name = ?, Email = ?, Phone = ?, Seniority_ID = ?, Group_ID = ?, prof_lang = ?, Load_Hours = ?, Note = ?, User_Access = ?, Profile_Status = ? WHERE Teacher_ID = ?");
$stmt->bind_param('ssssiisssssi', $firstName, $lastName, $email, $phone, $seniority, $groupId, $profLang, $loadHours, $note, $userAccess, $profileStatus, $id);
$stmt->execute();
$stmt->close();
// Update restricted groups
if (isset($body['Restricted_Groups']) && is_array($body['Restricted_Groups'])) {
$stmt = $mysqli->prepare("DELETE FROM teacher_restricted_groups WHERE Teacher_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();
foreach ($body['Restricted_Groups'] as $groupID) {
$stmt = $mysqli->prepare("INSERT INTO teacher_restricted_groups (Teacher_ID, Group_ID) VALUES (?, ?)");
$stmt->bind_param('ii', $id, $groupID);
$stmt->execute();
$stmt->close();
}
}
log_activity($mysqli, 'update', 'teacher', $id, "$firstName $lastName", 'Teacher updated via API');
$stmt = $mysqli->prepare("SELECT * FROM teacher_profile WHERE Teacher_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
unset($item['Password'], $item['salt']);
Response::success($item, 'Teacher updated');
}
function deleteTeacher(?string $id, mysqli $mysqli): void
{
Auth::requireAnyRole(['admin_profile', 'master_profile']);
if (!$id) Response::error('Teacher ID required');
$stmt = $mysqli->prepare("SELECT First_Name, Last_Name FROM teacher_profile WHERE Teacher_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($fn, $ln);
$stmt->fetch();
$stmt->close();
if (!$fn) Response::notFound('Teacher not found');
// Check for assignments
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM teacher_course_assignments WHERE Teacher_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($aCount);
$stmt->fetch();
$stmt->close();
if ($aCount > 0) {
Response::error("Cannot delete teacher: $aCount course assignment(s) exist", 409);
}
// Delete restricted groups
$stmt = $mysqli->prepare("DELETE FROM teacher_restricted_groups WHERE Teacher_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();
$stmt = $mysqli->prepare("DELETE FROM teacher_profile WHERE Teacher_ID = ?");
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->close();
log_activity($mysqli, 'delete', 'teacher', $id, "$fn $ln", 'Teacher deleted via API');
Response::success(null, 'Teacher deleted');
}
function getTeacherAvailability(string $id, mysqli $mysqli): void
{
Auth::requireLogin();
$stmt = $mysqli->prepare("SELECT Start_Date, End_Date FROM teacher_course_assignments WHERE Teacher_ID = ? ORDER BY Start_Date ASC");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$assignments = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
if (empty($assignments)) {
Response::success([
'overall_period' => null,
'available_days' => [],
'busy_days' => [],
], 'No assignments found. Teacher is fully available.');
}
$busyDays = [];
foreach ($assignments as $a) {
$start = new DateTime($a['Start_Date']);
$end = new DateTime($a['End_Date']);
$end->modify('+1 day');
$period = new DatePeriod($start, new DateInterval('P1D'), $end);
foreach ($period as $date) {
$busyDays[] = $date->format('Y-m-d');
}
}
// Compute overall range
$allStart = [];
$allEnd = [];
foreach ($assignments as $a) {
$allStart[] = $a['Start_Date'];
$allEnd[] = $a['End_Date'];
}
$overallStart = min($allStart);
$overallEnd = max($allEnd);
// Get available days (weekdays not busy)
$availableDays = [];
$period = new DatePeriod(
new DateTime($overallStart),
new DateInterval('P1D'),
(new DateTime($overallEnd))->modify('+1 day')
);
$busySet = array_flip($busyDays);
foreach ($period as $date) {
$d = $date->format('Y-m-d');
if (in_array($date->format('N'), ['6', '7'])) continue; // skip weekends
if (!isset($busySet[$d])) {
$availableDays[] = $d;
}
}
Response::success([
'overall_period' => [
'start_date' => $overallStart,
'end_date' => $overallEnd,
],
'available_days' => $availableDays,
'busy_days' => array_values(array_unique($busyDays)),
]);
}
function listTeacherAssignments(string $id, mysqli $mysqli): void
{
Auth::requireLogin();
$stmt = $mysqli->prepare("
SELECT tca.*, c.Course_Name, c.Course_Code, g.Group_Name, scg.Start_Date AS Sched_Start, scg.End_Date AS Sched_End
FROM teacher_course_assignments tca
LEFT JOIN Courses c ON c.Course_ID = tca.Course_ID
LEFT JOIN manager_group_name g ON g.Group_ID = tca.Group_ID
LEFT JOIN schedule_course_for_group scg ON scg.Schedule_ID = tca.Schedule_ID
WHERE tca.Teacher_ID = ?
ORDER BY tca.Start_Date DESC
");
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
Response::success($items);
}