/home/techb158/workloadmatch.com/api/routes
Edit: /home/techb158/workloadmatch.com/api/routes/notifications.php (5975B)
prepare("
SELECT tp.Teacher_ID, CONCAT(tp.First_Name, ' ', tp.Last_Name) AS name,
COUNT(*) AS cnt, MAX(cm.created_at) AS last_time
FROM chat_messages cm
JOIN teacher_profile tp ON tp.Teacher_ID = cm.sender_id
WHERE cm.receiver_id = ? AND cm.receiver_type = 'manager' AND cm.is_read = 0
GROUP BY tp.Teacher_ID, tp.First_Name, tp.Last_Name
ORDER BY last_time DESC LIMIT 10
");
$stmt->bind_param("i", $effectiveId);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$totalCount += (int)$row['cnt'];
$items[] = [
'type' => 'chat',
'icon' => 'fa fa-comment text-aqua',
'text' => $row['name'] . ' (' . $row['cnt'] . ' messages)',
'time' => timeAgo($row['last_time']),
'link' => 'chat.php',
];
}
$stmt->close();
// Unassigned courses notification
$stmt = $mysqli->prepare("
SELECT COUNT(DISTINCT CONCAT(scg.Course_ID, '-', scg.Group_ID)) AS task_count
FROM schedule_course_for_group scg
JOIN manager_group_name mgn ON mgn.Group_ID = scg.Group_ID
WHERE scg.Assigned = 0 AND mgn.Manager_ID = ?
");
$stmt->bind_param("i", $effectiveId);
$stmt->execute();
$r = $stmt->get_result()->fetch_assoc();
$stmt->close();
if ($r && (int)$r['task_count'] > 0) {
$c = (int)$r['task_count'];
$totalCount += $c;
$items[] = [
'type' => 'task',
'icon' => 'fa fa-user-plus text-yellow',
'text' => $c . ' unassigned course' . ($c > 1 ? 's' : ''),
'time' => '',
'link' => 'assign_teacher_manual.php',
];
}
} elseif ($userType === 'teacher_profile') {
// Unread messages from manager
$stmt = $mysqli->prepare("
SELECT cm.message, cm.created_at,
CONCAT(mp.First_Name, ' ', mp.Last_Name) AS name
FROM chat_messages cm
JOIN manager_profile mp ON mp.Manager_ID = cm.sender_id
WHERE cm.receiver_id = ? AND cm.receiver_type = 'teacher' AND cm.is_read = 0
ORDER BY cm.created_at DESC LIMIT 10
");
$stmt->bind_param("i", $userId);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$totalCount++;
$preview = $row['message'] ? (mb_strlen($row['message']) > 50 ? mb_substr($row['message'], 0, 50) . '...' : $row['message']) : '';
$items[] = [
'type' => 'chat',
'icon' => 'fa fa-comment text-aqua',
'text' => 'Message from ' . $row['name'],
'preview' => $preview,
'time' => timeAgo($row['created_at']),
'link' => 'chat.php',
];
}
$stmt->close();
// Today's courses notification
$today = date('Y-m-d');
$stmt = $mysqli->prepare("
SELECT COUNT(*) AS cnt
FROM schedule_course_for_group scg
JOIN teacher_course_assignments tca ON tca.Schedule_ID = scg.Schedule_ID
WHERE tca.Teacher_ID = ? AND scg.Start_Date = ?
");
$stmt->bind_param("is", $userId, $today);
$stmt->execute();
$r = $stmt->get_result()->fetch_assoc();
$stmt->close();
if ($r && (int)$r['cnt'] > 0) {
$c = (int)$r['cnt'];
$totalCount++;
$items[] = [
'type' => 'schedule',
'icon' => 'fa fa-calendar-check-o text-green',
'text' => $c . ' course' . ($c > 1 ? 's' : '') . ' today',
'time' => '',
'link' => 'dashboard.php',
];
}
}
Response::success([
'count' => $totalCount,
'items' => $items,
]);
}
function markNotificationsRead(?array $body, mysqli $mysqli): void
{
Auth::requireLogin();
$userId = Auth::getUserId();
$userType = Auth::getUserType();
$typeMap = [
'manager_profile' => 'manager',
'teacher_profile' => 'teacher',
];
$typeShort = $typeMap[$userType] ?? '';
$stmt = $mysqli->prepare("UPDATE chat_messages SET is_read = 1 WHERE receiver_id = ? AND receiver_type = ? AND is_read = 0");
$stmt->bind_param('is', $userId, $typeShort);
$stmt->execute();
$affected = $mysqli->affected_rows;
$stmt->close();
Response::success(null, "Marked $affected notification(s) as read");
}
function timeAgo($dt): string
{
if (!$dt) return '';
$ts = is_numeric($dt) ? (int)$dt : strtotime($dt);
if (!$ts) return '';
$diff = time() - $ts;
if ($diff < 60) return 'now';
if ($diff < 3600) return floor($diff / 60) . 'm';
if ($diff < 86400) return floor($diff / 3600) . 'h';
if ($diff < 172800) return 'yesterday';
return date('M d', $ts);
}