<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="utils.*, java.sql.*, java.util.Date" errorPage="" %> <%@page import="java.util.*"%> CASP Portal <%@ include file="../inc/header.jsp" %> <%@ include file="menu.jsp" %>
<% int inst_ID = 6001; Date date = new Date(); DatabaseConnection db_conn = DatabaseConnection.getConnection(); String sql_inst = "SELECT * FROM Institution WHERE Inst_ID = ?"; PreparedStatement ps_inst = db_conn.getPreparedStatement(sql_inst); ps_inst.setInt(1,inst_ID); ps_inst.executeUpdate(); String inst_name = ""; String inst_contact = ""; String inst_email = ""; ResultSet rs_inst = ps_inst.executeQuery(); if (rs_inst.next()) { inst_name = ff.getValue(rs_inst,"Inst_Name"); inst_contact = ff.getValue(rs_inst,"Contact_Person"); inst_email = ff.getValue(rs_inst, "Contact_Email"); } String sql_apps = "SELECT First_Name, Middle_Name, Last_Name, Email, " + "fn.Program_Name AS Fn_Program, " + "uq.Program_Name AS UQ_Program " + "FROM USERS, ROLES, APPLICANT, PROGRAM fn, PROGRAM uq " + "WHERE Role_Name = 'APPLICANT' " + "AND Role_FK = Role_ID " + "AND User_FK = User_ID " + "AND Prev_Program_FK = fn.Program_ID " + "AND UQ_Program_FK = uq.Program_ID " + "AND Prev_Inst_FK = ? " + "ORDER BY Last_Name ASC"; PreparedStatement ps_apps = db_conn.getPreparedStatement(sql_apps); ps_apps.setInt(1,inst_ID); ps_apps.executeUpdate(); ResultSet rs_apps = ps_apps.executeQuery(); int count_apps = 0; String fn_apps = ""; String mn_apps = ""; String ln_apps = ""; String email_apps = ""; String fprog_apps = ""; String uqprog_apps = ""; %> <%! public String name(String first, String middle, String last) { String name = last + ", " + first; if (!(middle.equals(""))) {name += " " + middle;} return name; } %>

Institutional Summary Report

Report Generated at: <%=date.toString()%>

Institution: <%=inst_name%>

Contact Person: <%=inst_contact%>
Contact Email: <%=inst_email%>

<% while(rs_apps.next()) { count_apps++; fn_apps = rs_apps.getString("First_Name"); mn_apps = rs_apps.getString("Middle_Name"); ln_apps = rs_apps.getString("Last_Name"); email_apps = rs_apps.getString("Email"); fprog_apps = rs_apps.getString("Fn_Program"); uqprog_apps = rs_apps.getString("UQ_Program"); %> <% } %>

Details of Applicants from <%=inst_name%>
Applicant Name Previous Program Interested Programs Email Address
<%= name(fn_apps,mn_apps,ln_apps)%> <%= fprog_apps%> <%= uqprog_apps%> <%= email_apps%>
Total number of applicants from <%=inst_name%> <%=count_apps%>
<% rs_inst.close(); ps_inst.close(); rs_apps.close(); ps_apps.close(); db_conn.closeConnection(); %>
<%@ include file="../inc/footer.jsp" %>