<%@ page import="java.sql.*" %> <% if ( null == session.getAttribute("username") ) response.sendRedirect("admin.jsp"); Class.forName ("oracle.jdbc.driver.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@dbprod03.isis.unc.edu:1521:ord1p", "acrystal", "&GJ8$mRQ"); Statement stmt = con.createStatement(); Statement parStmt = con.createStatement(); Statement earnedStmt = con.createStatement(); ResultSet parRS, creditsRS, earnedRS; parRS = parStmt.executeQuery("SELECT * FROM participants ORDER BY lastName, firstName"); int PID = 0, creditsRequired = 0, creditsEarned = 0, i = 0; String firstName = "test"; String lastName = "test"; String email = "test@test.com"; %> StudyManager: Attendance report

Full attendance report

<% while (parRS.next()) { i++; PID = parRS.getInt("PID"); firstName = parRS.getString("firstName"); lastName = parRS.getString("lastName"); email = parRS.getString("email"); creditsRS = stmt.executeQuery("SELECT SUM(creditsRequired) FROM courses WHERE id IN " + "(SELECT courseID FROM courses_participants_link WHERE PID = " + PID + ")"); if (creditsRS.next()) creditsRequired = creditsRS.getInt(1); earnedRS = earnedStmt.executeQuery("SELECT SUM(creditHours) FROM studies WHERE id IN (" + " SELECT studyID FROM sessions WHERE id IN ( " + "SELECT sessionID FROM attendance WHERE PID = " + PID + "))"); if (earnedRS.next()) creditsEarned = earnedRS.getInt(1); System.out.println("PID: " + PID + " " + creditsEarned + "/" + creditsRequired); %> <% } %>

#	PID	First name	Last name	Email	Credits earned	Credits required	Credits needed
Attendance report
<%= i %>	<%= PID %>	<%= firstName %>	<%= lastName %>	<%= email %>	<%= creditsEarned %>	<%= creditsRequired %>	<%= creditsRequired - creditsEarned %>

<% con.close(); %>