File 28: admin/reports.jsp
Purpose
Generates attendance summary and detailed reports for a selected course offering. The response is returned in JSON format for reports.html.
Complete Source Code
<%@ page import="java.sql.*" %>
<%@ page import="org.json.*" %>
<%
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");
Integer userId = (Integer) session.getAttribute("userId");
String role = (String) session.getAttribute("role");
if (userId == null || !"ADMIN".equals(role)) {
response.setStatus(401);
out.print("{\"success\":false,\"message\":\"Unauthorized access\"}");
return;
}
int offeringId;
try {
offeringId = Integer.parseInt(
request.getParameter("offeringId")
);
if (offeringId <= 0) {
throw new NumberFormatException();
}
} catch (Exception e) {
response.setStatus(400);
out.print("{\"success\":false,\"message\":\"Invalid offering ID\"}");
return;
}
String dbURL = "jdbc:oracle:thin:@localhost:1521/XEPDB1";
String dbUser = "YOUR_DB_USERNAME";
String dbPassword = "YOUR_DB_PASSWORD";
try {
Class.forName("oracle.jdbc.OracleDriver");
try (Connection con = DriverManager.getConnection(
dbURL, dbUser, dbPassword)) {
// Check whether the offering exists
String checkSQL =
"SELECT COUNT(*) FROM OFFERINGS WHERE OFFERING_ID=?";
try (PreparedStatement checkPS =
con.prepareStatement(checkSQL)) {
checkPS.setInt(1, offeringId);
try (ResultSet checkRS = checkPS.executeQuery()) {
checkRS.next();
if (checkRS.getInt(1) == 0) {
response.setStatus(404);
out.print(
"{\"success\":false,\"message\":\"Offering not found\"}"
);
return;
}
}
}
JSONArray summary = new JSONArray();
// Attendance summary query
String summarySQL =
"SELECT S.STUDENT_ID, S.STUDENT_NAME, S.BATCH, " +
"COUNT(A.ATTENDANCE_ID) AS TOTAL_CLASSES, " +
"NVL(SUM(CASE WHEN A.STATUS='PRESENT' THEN 1 ELSE 0 END),0) AS PRESENT_COUNT, " +
"NVL(SUM(CASE WHEN A.STATUS='ABSENT' THEN 1 ELSE 0 END),0) AS ABSENT_COUNT, " +
"NVL(SUM(CASE WHEN A.STATUS='LATE' THEN 1 ELSE 0 END),0) AS LATE_COUNT, " +
"NVL(SUM(CASE WHEN A.STATUS='EXCUSED' THEN 1 ELSE 0 END),0) AS EXCUSED_COUNT " +
"FROM ENROLLMENTS E " +
"JOIN STUDENTS S ON E.STUDENT_ID=S.STUDENT_ID " +
"LEFT JOIN SESSIONS CS ON CS.OFFERING_ID=E.OFFERING_ID " +
"LEFT JOIN ATTENDANCE A ON A.SESSION_ID=CS.SESSION_ID " +
"AND A.STUDENT_ID=S.STUDENT_ID " +
"WHERE E.OFFERING_ID=? " +
"GROUP BY S.STUDENT_ID, S.STUDENT_NAME, S.BATCH " +
"ORDER BY S.STUDENT_ID";
try (PreparedStatement ps =
con.prepareStatement(summarySQL)) {
ps.setInt(1, offeringId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
int studentId = rs.getInt("STUDENT_ID");
String studentName =
rs.getString("STUDENT_NAME");
String batch = rs.getString("BATCH");
int totalClasses =
rs.getInt("TOTAL_CLASSES");
int present =
rs.getInt("PRESENT_COUNT");
int absent =
rs.getInt("ABSENT_COUNT");
int late =
rs.getInt("LATE_COUNT");
int excused =
rs.getInt("EXCUSED_COUNT");
// Excused classes are excluded
// from the attendance percentage denominator.
int eligibleClasses = present + absent + late;
double percentage = 0.0;
if (eligibleClasses > 0) {
percentage =
((present + late) * 100.0)
/ eligibleClasses;
}
percentage =
Math.round(percentage * 100.0) / 100.0;
JSONObject obj = new JSONObject();
obj.put("studentId", studentId);
obj.put("studentName", studentName);
obj.put("batch", batch);
obj.put("totalClasses", totalClasses);
obj.put("present", present);
obj.put("absent", absent);
obj.put("late", late);
obj.put("excused", excused);
obj.put("percentage", percentage);
summary.put(obj);
}
}
}
// Detailed attendance records
JSONArray details = new JSONArray();
String detailsSQL =
"SELECT S.STUDENT_ID, S.STUDENT_NAME, " +
"CS.SESSION_DATE, CS.TOPIC, A.STATUS, A.REMARKS " +
"FROM ATTENDANCE A " +
"JOIN STUDENTS S ON A.STUDENT_ID=S.STUDENT_ID " +
"JOIN SESSIONS CS ON A.SESSION_ID=CS.SESSION_ID " +
"WHERE CS.OFFERING_ID=? " +
"ORDER BY S.STUDENT_ID, CS.SESSION_DATE";
try (PreparedStatement ps =
con.prepareStatement(detailsSQL)) {
ps.setInt(1, offeringId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
JSONObject obj = new JSONObject();
obj.put(
"studentId",
rs.getInt("STUDENT_ID")
);
obj.put(
"studentName",
rs.getString("STUDENT_NAME")
);
Date sessionDate =
rs.getDate("SESSION_DATE");
obj.put(
"sessionDate",
sessionDate == null
? JSONObject.NULL
: sessionDate.toString()
);
obj.put(
"topic",
rs.getString("TOPIC")
);
obj.put(
"status",
rs.getString("STATUS")
);
String remarks = rs.getString("REMARKS");
obj.put(
"remarks",
remarks == null ? "" : remarks
);
details.put(obj);
}
}
}
// Final JSON response
JSONObject result = new JSONObject();
result.put("success", true);
result.put("summary", summary);
result.put("details", details);
out.print(result.toString());
}
} catch (Exception e) {
application.log("Error in reports.jsp", e);
response.setStatus(500);
out.print(
"{\"success\":false,\"message\":\"Unable to generate attendance report\"}"
);
}
%>How It Works
- Admin verification: Checks whether the logged-in user has the ADMIN role.
- Offering validation: Checks whether the selected course offering exists.
- Summary generation: Counts each student's recorded attendance statuses.
- Percentage calculation: Calculates attendance using the following formula:
Attendance Percentage = (Present + Late) / (Present + Absent + Late) × 100
Excused classes are excluded from the percentage denominator. Total Classes represents all recorded attendance entries, including excused entries.
- Detailed report: Retrieves individual attendance records for the selected offering.
- JSON response: Sends the summary and details to reports.html.
How to Test
- Log in as an administrator.
- Open
admin/reports.html. - Select a course offering.
- Click the Generate Report button.
- Check the attendance summary and detailed records.
- Test the Print button.
Required Configuration
- Update Oracle database username and password.
- Ensure the Oracle JDBC driver is available in Tomcat's library folder.
- Ensure the JSON library is available to the application.
- Confirm that the database tables and column names match the project schema.
Important: This is a classroom demonstration. For a production system, use secure password hashing, externalize database credentials, and apply appropriate access controls.
Project Completion
All 28 main HTML and JSP files in the project structure have now been covered.
Next optional file: js/common.js — shared JavaScript functions for the project.
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন