<%@ page import="java.sql.*" %> <%@ page import="java.util.ArrayList"%> <% int id = Integer.parseInt(request.getParameter("courseID")); 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 notStmt = con.createStatement(); Statement parStmt = con.createStatement(); ResultSet rs, attendedRS, notAttendedRS, participantRS; rs = stmt.executeQuery("SELECT * FROM courses WHERE id =" + id); String department= ""; String title = ""; String instructor = ""; int number = 0; int section = 0; if (rs.next()) { department = rs.getString("department"); title = rs.getString("title"); instructor = rs.getString("instructor"); number = rs.getInt("coursenum"); section = rs.getInt("section"); } int i = 0; int PID = 0; String firstName = "test"; String lastName = "test"; String email = "test@test.com"; attendedRS = stmt.executeQuery("SELECT DISTINCT PID FROM attendance WHERE PID IN (SELECT DISTINCT PID FROM courses_participants_link WHERE courseID = " + id + ")"); notAttendedRS = notStmt.executeQuery("SELECT DISTINCT PID FROM courses_participants_link WHERE courseID = " + id + " AND PID NOT IN (SELECT PID FROM attendance WHERE PID IN " + "(SELECT PID FROM courses_participants_link WHERE courseID = " + id + "))"); %> StudyManager: Attendance report

Course research study attendance report

<%= department %> <%= number%>-<%= section%>: <%= title %>

<% while (attendedRS.next()) { PID = attendedRS.getInt("PID"); participantRS = parStmt.executeQuery("SELECT DISTINCT * FROM participants WHERE PID = " + PID); participantRS.next(); firstName = participantRS.getString("firstName"); lastName = participantRS.getString("lastName"); email = participantRS.getString("email"); i++; %> <% } %>
Students who have attended a study
# PID First name Last name Email
<%= i %> <%= PID %> <%= firstName %> <%= lastName %> <%= email %>
<% while (notAttendedRS.next()) { PID = notAttendedRS.getInt("PID"); participantRS = parStmt.executeQuery("SELECT * FROM participants WHERE PID = " + PID); participantRS.next(); firstName = participantRS.getString("firstName"); lastName = participantRS.getString("lastName"); email = participantRS.getString("email"); i++; %> <% } %>
Students who have not attended a study
# PID First name Last name Email
<%= i %> <%= PID %> <%= firstName %> <%= lastName %> <%= email %>
<% con.close(); %>