The attendance interface should show the current attendance status when loading the session. Let me update the code to properly display existing attendance data.
COALESCE(ea.present, NULL) to properly handle existing attendanceattendanceMarked, markedAt, and markedBy fieldsnull (not set) and false (absent)// Student object now includes:
{
id: 123,
name: "John Doe",
present: true, // Current attendance (true/false/null)
attendanceMarked: true, // Whether attendance was previously marked
markedAt: "2025-01-24T10:30:00Z",
markedBy: "Teacher Name"
}This ensures users can always see the current attendance status and make informed decisions about updates! 🎯
if a student's attendance already mark with classroomwise type then it should be relected in the classwise typeand vise-versaright now you are creating a diiffernt rowif student_id class_id time-slot date subject_id is same then it means its the same exam and same student then why do we need another if we keep it like this then it will solve our problem and in db you are storing only class_name , we should use class_id, subject_id, if you have any other better approch tell me
The current approach is creating duplicate records when it should be treating the same exam session as one entity. Let me propose a better unified approach:
(exam_config_id, student_id, class_id, subject_id, exam_date, time_slot)class_id and subject_id instead of storing names-- Single unified table structure
CREATE TABLE exam_attendance (
exam_config_id INT,
student_id INT,
class_id INT, -- FK to class table
subject_id INT, -- FK to subjects table
exam_date DATE,
time_slot VARCHAR(50),
present BOOLEAN,
room_id INT NULL, -- Optional context
seat_number VARCHAR(10) NULL, -- Optional context
UNIQUE KEY (exam_config_id, student_id, class_id, subject_id, exam_date, time_slot)
);// Unified attendance record structure
const attendanceRecord = {
exam_config_id: 123,
student_id: 456,
class_id: 10, // Instead of class_name
subject_id: 5, // Instead of subject_name
exam_date: "2025-01-25",
time_slot: "09:00-11:00",
present: true,
room_id: 101, // Optional for context
seat_number: "A1" // Optional for context
}This unified approach eliminates the duplicate record problem while maintaining the flexibility to view attendance from both class-wise and room-wise perspectives! 🚀
Error fetching all room sessions: Error: ER_BAD_FIELD_ERROR: Unknown column 'sa2.class_id' in 'ON' at Sequence._packetToError (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14) at Query.ErrorPacket (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\sequences\Query.js:79:18) at Protocol._parsePacket (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Protocol.js:291:23) at Parser._parsePacket (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Parser.js:433:10) at Parser.write (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Parser.js:43:10) at Protocol.write (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Protocol.js:38:16) at Socket.<anonymous> (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\Connection.js:88:28) at Socket.<anonymous> (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\Connection.js:526:10) at Socket.emit (node:events:518:28) at addChunk (node:internal/streams/readable:561:12) -------------------- at Protocol._enqueue (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Protocol.js:144:48) at Connection.query (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\Connection.js:198:25) at file:///C:/Users/user/Desktop/schoolManagement/schoolManagement/backend/controllers/exam-controllers/exam.controller.js:5:10 at new Promise (<anonymous>) at queryAsync (file:///C:/Users/user/Desktop/schoolManagement/schoolManagement/backend/controllers/exam-controllers/exam.controller.js:4:10) at getAllRoomSessions (file:///C:/Users/user/Desktop/schoolManagement/schoolManagement/backend/controllers/exam-controllers/exam.controller.js:2578:28) at Layer.handle [as handle_request] (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\layer.js:95:5) at next (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\route.js:149:13) at Route.dispatch (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\route.js:119:3) at Layer.handle [as handle_request] (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\layer.js:95:5) { code: 'ER_BAD_FIELD_ERROR', errno: 1054, sqlMessage: "Unknown column 'sa2.class_id' in 'ON'", sqlState: '42S22', index: 0, sql: 'SELECT DISTINCT\n' + " CONCAT(sa.room_id, '_', sa.exam_date, '_', sa.time_slot) as id,\n" + ' sa.room_id as roomId,\n' + ' COALESCE(\n' + ' (SELECT room_name FROM exam_config_rooms ecr \n' + ' WHERE ecr.room_id = sa.room_id AND ecr.exam_config_id = sa.exam_config_id \n' + ' LIMIT 1),\n' + " CONCAT('Room ', sa.room_id)\n" + ' ) as roomName,\n' + ' sa.exam_date as examDate,\n' + ' sa.time_slot as timeSlot,\n' + ' GROUP_CONCAT(DISTINCT s.name ORDER BY s.name) as subjects,\n' + ' COUNT(DISTINCT sa.student_id) as totalStudents,\n' + ' -- Unified attendance check for room sessions\n' + ' CASE WHEN EXISTS (\n' + ' SELECT 1 FROM exam_attendance ea \n' + ' INNER JOIN seating_arrangements sa2 ON ea.student_id = sa2.student_id\n' + ' AND ea.class_id = sa2.class_id \n' + ' AND ea.subject_id = sa2.subject_id\n' + ' AND ea.exam_date = sa2.exam_date\n' + ' AND ea.time_slot = sa2.time_slot\n' + ' WHERE ea.exam_config_id = sa.exam_config_id \n' + ' AND sa2.room_id = sa.room_id\n' + ' AND sa2.exam_date = sa.exam_date\n' + ' AND sa2.time_slot = sa.time_slot\n' + ' ) THEN TRUE ELSE FALSE END as attendanceMarked\n' + ' FROM seating_arrangements sa\n' + ' INNER JOIN subjects s ON sa.subject_id = s.id\n' + " WHERE sa.exam_config_id = '49' AND sa.user_id = 1 AND sa.outlet_id = 2\n" + ' GROUP BY sa.room_id, sa.exam_date, sa.time_slot\n' + ' ORDER BY sa.exam_date, sa.time_slot, sa.room_id'}Auth Middleware TriggeredDecoded User: { id: 1, email: 'ravi@gmail.com', code: 'MTB5453', role: 'Admin', admin_id: 19, branch_id: 37, outlet_id: 2, iat: 1753501657, exp: 1754106457}Error fetching room attendance data: Error: ER_BAD_FIELD_ERROR: Unknown column 'sa.subject_id' in 'ON' at Sequence._packetToError (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14) at Query.ErrorPacket (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\sequences\Query.js:79:18) at Protocol._parsePacket (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Protocol.js:291:23) at Parser._parsePacket (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Parser.js:433:10) at Parser.write (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Parser.js:43:10) at Protocol.write (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Protocol.js:38:16) at Socket.<anonymous> (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\Connection.js:88:28) at Socket.<anonymous> (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\Connection.js:526:10) at Socket.emit (node:events:518:28) at addChunk (node:internal/streams/readable:561:12) -------------------- at Protocol._enqueue (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Protocol.js:144:48) at Connection.query (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\Connection.js:198:25) at file:///C:/Users/user/Desktop/schoolManagement/schoolManagement/backend/controllers/exam-controllers/exam.controller.js:5:10 at new Promise (<anonymous>) at queryAsync (file:///C:/Users/user/Desktop/schoolManagement/schoolManagement/backend/controllers/exam-controllers/exam.controller.js:4:10) at getRoomAttendanceData (file:///C:/Users/user/Desktop/schoolManagement/schoolManagement/backend/controllers/exam-controllers/exam.controller.js:2355:34) at Layer.handle [as handle_request] (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\layer.js:95:5) at next (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\route.js:149:13) at Route.dispatch (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\route.js:119:3) at Layer.handle [as handle_request] (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\layer.js:95:5) { code: 'ER_BAD_FIELD_ERROR', errno: 1054, sqlMessage: "Unknown column 'sa.subject_id' in 'ON'", sqlState: '42S22', index: 0, sql: 'SELECT DISTINCT\n' + ' sa.room_id,\n' + ' COALESCE(\n' + ' (SELECT room_name FROM exam_config_rooms ecr \n' + ' WHERE ecr.room_id = sa.room_id AND ecr.exam_config_id = sa.exam_config_id \n' + ' LIMIT 1),\n' + " CONCAT('Room ', sa.room_id)\n" + ' ) as room_name,\n' + ' sa.exam_date,\n' + ' sa.time_slot,\n' + ' GROUP_CONCAT(DISTINCT s.name ORDER BY s.name) as subjects,\n' + ' COUNT(DISTINCT sa.student_id) as total_students\n' + ' FROM seating_arrangements sa\n' + ' INNER JOIN subjects s ON sa.subject_id = s.id\n' + " WHERE sa.exam_config_id = '49' AND sa.room_id = '2699' \n" + " AND sa.exam_date = '2025-08-05' AND sa.time_slot = '13:00 - 16:00'\n" + ' AND sa.user_id = 1 AND sa.outlet_id = 2\n' + ' GROUP BY sa.room_id, sa.exam_date, sa.time_slot'}Auth Middleware TriggeredDecoded User: { id: 1, email: 'ravi@gmail.com', code: 'MTB5453', role: 'Admin', admin_id: 19, branch_id: 37, outlet_id: 2, iat: 1753501657, exp: 1754106457}Error fetching room attendance data: Error: ER_BAD_FIELD_ERROR: Unknown column 'sa.subject_id' in 'ON' at Sequence._packetToError (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14) at Query.ErrorPacket (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\sequences\Query.js:79:18) at Protocol._parsePacket (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Protocol.js:291:23) at Parser._parsePacket (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Parser.js:433:10) at Parser.write (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Parser.js:43:10) at Protocol.write (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Protocol.js:38:16) at Socket.<anonymous> (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\Connection.js:88:28) at Socket.<anonymous> (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\Connection.js:526:10) at Socket.emit (node:events:518:28) at addChunk (node:internal/streams/readable:561:12) -------------------- at Protocol._enqueue (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\protocol\Protocol.js:144:48) at Connection.query (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\mysql\lib\Connection.js:198:25) at file:///C:/Users/user/Desktop/schoolManagement/schoolManagement/backend/controllers/exam-controllers/exam.controller.js:5:10 at new Promise (<anonymous>) at queryAsync (file:///C:/Users/user/Desktop/schoolManagement/schoolManagement/backend/controllers/exam-controllers/exam.controller.js:4:10) at getRoomAttendanceData (file:///C:/Users/user/Desktop/schoolManagement/schoolManagement/backend/controllers/exam-controllers/exam.controller.js:2355:34) at Layer.handle [as handle_request] (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\layer.js:95:5) at next (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\route.js:149:13) at Route.dispatch (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\route.js:119:3) at Layer.handle [as handle_request] (C:\Users\user\Desktop\schoolManagement\schoolManagement\backend\node_modules\express\lib\router\layer.js:95:5) { code: 'ER_BAD_FIELD_ERROR', errno: 1054, sqlMessage: "Unknown column 'sa.subject_id' in 'ON'", sqlState: '42S22', index: 0, sql: 'SELECT DISTINCT\n' + ' sa.room_id,\n' + ' COALESCE(\n' + ' (SELECT room_name FROM exam_config_rooms ecr \n' + ' WHERE ecr.room_id = sa.room_id AND ecr.exam_config_id = sa.exam_config_id \n' + ' LIMIT 1),\n' + " CONCAT('Room ', sa.room_id)\n" + ' ) as room_name,\n' + ' sa.exam_date,\n' + ' sa.time_slot,\n' + ' GROUP_CONCAT(DISTINCT s.name ORDER BY s.name) as subjects,\n' + ' COUNT(DISTINCT sa.student_id) as total_students\n' + ' FROM seating_arrangements sa\n' + ' INNER JOIN subjects s ON sa.subject_id = s.id\n' + " WHERE sa.exam_config_id = '49' AND sa.room_id = '2699' \n" + " AND sa.exam_date = '2025-08-05' AND sa.time_slot = '13:00 - 16:00'\n" + ' AND sa.user_id = 1 AND sa.outlet_id = 2\n' + ' GROUP BY sa.room_id, sa.exam_date, sa.time_slot'} ' GROUP BY sa.room_id, sa.exam_date, sa.time_slot'} ' GROUP BY sa.room_id, sa.exam_date, sa.time_slot'} ' GROUP BY sa.room_id, sa.exam_date, sa.time_slot'} ' GROUP BY sa.room_id, sa.exam_date, sa.time_slot'} ' GROUP BY sa.room_id, sa.exam_date, sa.time_slot'} ' GROUP BY sa.room_id, sa.exam_date, sa.time_slot'} ' GROUP BY sa.room_id, sa.exam_date, sa.time_slot'}[nodemon] restarting due to changes...[nodemon] starting `node index.js`✅ Server running at: http://192.168.0.251:3306✅ Database connected successfully[nodemon] restarting due to changes...[nodemon] starting `node index.js`✅ Server running at: http://192.168.0.251:3306✅ Database connected successfully