<%@ page import="java.io.File" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page import="java.text.ParsePosition" %>
<%@ include file="Database.jsp" %>
<%
String categories[] = request.getParameterValues("class");
String projects[] = request.getParameterValues("project");
int perCat = 100; try { perCat = Integer.parseInt(request.getParameter("percat")); } catch (Exception e) {}
int maxRes = 100; try { maxRes = Integer.parseInt(request.getParameter("maxRes")); } catch (Exception e) {}
Connection c = null;
PreparedStatement stmt = null;
ResultSet rs = null;
SimpleDateFormat dateout = new SimpleDateFormat("h:mmaa 'on' EEEE, MMMM d, yyyy ");
try {
c = getDBConnection(out);
if (c == null) {
out.println("
couldn't get DB connection");
return;
}
StringBuffer sql = new StringBuffer();
sql.append("SELECT NEWSID, CATNAME, IMAGEURL, NEWSDATE, AUTHOR, SUBJECT, DETAILS FROM NEWSITEM, CATEGORY WHERE CATEGORY.CATID = NEWSITEM.CATID AND NEWSID IN (");
sql.append("SELECT T2.NEWSID from CATEGORY T1 left join NEWSITEM T2 on T1.CATID=T2.CATID and T2.NEWSID IN (");
sql.append("SELECT FIRST ");
sql.append(perCat);
sql.append(" NEWSID FROM NEWSITEM T3 WHERE T3.CATID = T1.CATID ");
sql.append(" ORDER BY NEWSDATE DESC)");
if ((categories != null) && (categories.length > 0)) {
if ((projects != null) && (projects.length > 0)) {
sql.append(" AND (T1.CLASSIFICATION IN (");
}
else { sql.append(" AND T1.CLASSIFICATION IN (");
}
int count = 0;
for (String cat: categories) {
sql.append((count++ != 0) ? "," : "");
sql.append(Integer.toString(Integer.parseInt(cat)));
}
sql.append(")");
}
if ((projects != null) && (projects.length > 0)) {
if ((categories != null) && (categories.length > 0)) {
sql.append(" OR ");
}
else { sql.append("AND ");
}
sql.append(" T1.CATID IN (");
int count = 0;
for (String pro: projects) {
sql.append((count++ != 0) ? "," : "");
sql.append(Integer.toString(Integer.parseInt(pro)));
}
if ((categories != null) && (categories.length > 0)) {
sql.append("))");
}
else { sql.append(")");
}
}
sql.append(") ORDER BY NEWSDATE DESC");
stmt = c.prepareStatement(sql.toString());
rs = stmt.executeQuery();
for (int i = 0; ((rs.next()) && (i < maxRes)); i++) {
boolean hasImage = false;
String imgURL = rs.getString("IMAGEURL");
if (imgURL != null) {
imgURL = imgURL.trim();
if (imgURL.length() > 2)
hasImage = true;
}
%>
"><%= rs.getString("SUBJECT") %>
<%= (hasImage) ? ("![](\"")
") : "" %>
Posted <%= dateout.format(rs.getTimestamp("NEWSDATE")) %> by <%= rs.getString("AUTHOR") %>
<%= rs.getString("DETAILS") %>
<%
}
}
catch (Exception e) {
out.print(e);
e.printStackTrace();
} // at least show rest of page
finally {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (c != null) {
c.close();
}
}
%>