rendered paste body<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="utils.*, java.sql.*, java.util.Date" errorPage="" %><%@page import="java.util.*"%><jsp:useBean id="ff" class="utils.FormFunctions" scope="request"></jsp:useBean><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><title>CASP Portal</title><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><meta name="title" content="WiseTech Solutions - CSSE3004GF" /><meta name="description" content="Credit Assessment and Study Plan System - CASP System" /><link rel="icon" href="http://www.uq.edu.au/images/home/favicon.ico" type="image/x-icon" /><link rel="shortcut icon" href="http://www.uq.edu.au/images/home/favicon.ico" type="image/x-icon" /><link rel='stylesheet' href='/casp/css/template.css' type='text/css' /><link rel="stylesheet" type="text/css" href="../css/casp.css"> <style type="text/css"> table.app { border-style: none; border-collapse: collapse; background-color: white; } table.app th { border-style: none none solid none; border-width: thick; border-color: black; background-color: white; padding: 0cm 1cm ! important } table.app td { border-style: none none dashed none !important; border-width: 1px !important; border-color: gray; background-color: white; padding: 0cm 1cm ! important } tr.empty td { border-style: none !important } tr.summary { border-top-style: double !important } tr.summary td { border-style: none !important } </style></head><body><!-- HEADER --> <%@ include file="../inc/header.jsp" %><!-- START: Main Content --><table border="0" cellspacing="0" cellpadding="0" summary="Content Table" class="mainblockpart"><tr> <!-- Menu Section --> <%@ include file="menu.jsp" %> <!-- Body Section --> <td valign="top" width="95%"> <% 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; } %> <center><h1>Institutional Summary Report</h1></center> <div style="text-align: right">Report Generated at: <%=date.toString()%></div> <h2>Institution: <%=inst_name%></h2> <p> Contact Person: <%=inst_contact%> <br> Contact Email: <%=inst_email%> </p> <hr> <table class = app style="margin-left: auto; margin-right: auto"> <tr class="empty"> <TD COLSPAN = "4" style="text-align: center"><H3>Details of Applicants from <%=inst_name%></H3></TD> </tr> <tr> <th>Applicant Name</th> <th>Previous Program</th> <th>Interested Programs</th> <th>Email Address</th> </tr> <% 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"); %> <tr> <td style="border-right: 1px solid black> <%= name(fn_apps,mn_apps,ln_apps)%></td> <td style="text-align: center" > <%= fprog_apps%></td> <td style="text-align: center"> <%= uqprog_apps%></td> <td style="text-align: right"><%= email_apps%></td> </tr> <% } %> <tr class="summary"> <td COLSPAN = "3"><b>Total number of applicants from <%=inst_name%></b></td> <td style="text-align: right"><b><%=count_apps%></b></td> </tr> </table> <% rs_inst.close(); ps_inst.close(); rs_apps.close(); ps_apps.close(); db_conn.closeConnection(); %> </td> <!-- End of Body Section --></tr></table><!-- FOOTER --><%@ include file="../inc/footer.jsp" %></body></html>