<%@ page buffer="32kb" contentType="text/html; charset=iso-8859-1" language="java" errorPage="" import="java.io.*,java.sql.*,javax.sql.DataSource,java.util.Date,java.util.Calendar,java.util.Locale,java.text.SimpleDateFormat,java.util.TimeZone" %><% String strDefaultCSS = "blue"; // Database initialisation Connection connection = (Connection) request.getAttribute("connection"); String id = request.getParameter("id"); int menuID = -1; try { menuID = Integer.parseInt(id); } catch (Exception e){} PreparedStatement ps = connection.prepareStatement("select Menu.Name, MenuCSS.CSS from Menu left join MenuCSS on Menu.ID = MenuCSS.MenuID where Menu.ID=?"); if (menuID != -1) { ps.setInt(1, menuID); } else { ps.setNull(1, Types.NULL); } ResultSet rs = ps.executeQuery(); String menu = null; String menuCSS = null; if (rs.next()) { menu = rs.getString(1); menuCSS = rs.getString(2); } rs.close(); ps.close(); ps = connection.prepareStatement("SELECT MenuCSS.CSS from ReverseMenu left join MenuCSS on ReverseMenu.ParentID = MenuCSS.MenuID where ReverseMenu.ID=? and ReverseMenu.ParentID is not null order by ReverseMenu.Order ASC, MenuCSS.Order ASC"); if (menuID != -1) { ps.setInt(1, menuID); } else { ps.setNull(1, Types.NULL); } rs = ps.executeQuery(); StringBuffer sb1 = new StringBuffer(); sb1.append(strDefaultCSS); while (rs.next()) { String strCSS = rs.getString(1); if (strCSS != null) { sb1.append(","); sb1.append(strCSS); } } rs.close(); ps.close(); if (menuCSS != null) { if (sb1.length() > 0) { sb1.append(","); } sb1.append(menuCSS); } ps = connection.prepareStatement("SELECT Menu.ID, Menu.Name from ReverseMenu left join Menu on ReverseMenu.ParentID = Menu.ID where ReverseMenu.ID=? and ReverseMenu.ParentID is not null order by ReverseMenu.Order ASC"); if (menuID != -1) { ps.setInt(1, menuID); } else { ps.setNull(1, Types.NULL); } rs = ps.executeQuery(); StringBuffer sb2 = new StringBuffer(); boolean bNotEmpty = false; while (rs.next()) { if (bNotEmpty) { sb2.append(" > "); } bNotEmpty = true; sb2.append(""); sb2.append(rs.getString(2)); sb2.append(""); } rs.close(); ps.close(); if (menu != null) { if (bNotEmpty) { sb2.append(" > "); } sb2.append(""); sb2.append(menu); sb2.append(""); } %> <% PreparedStatement pstatementDate = null; PreparedStatement pstatementPop = null; pstatementDate = connection.prepareStatement("select Article.ID, Article.Name, AverageScore from Article inner join (select ID, ID as ParentID from Menu UNION select ID,ParentID from ReverseMenu) A on Article.MenuID = A.ID left join ArticleVotes on Article.ID = ArticleVotes.ArticleID where A.ParentID=? order by Article.DateCreated DESC limit 0,5;"); pstatementPop = connection.prepareStatement("select Article.ID, Article.Name, AverageScore from Article inner join (select ID, ID as ParentID from Menu UNION select ID,ParentID from ReverseMenu) A on Article.MenuID = A.ID left join ArticleVotes on Article.ID = ArticleVotes.ArticleID where A.ParentID=? order by ArticleVotes.AverageScore DESC limit 0,5;"); if (menuID != -1) { ps = connection.prepareStatement("select ID, Name from Menu where ParentID=?"); ps.setInt(1, menuID); } else { ps = connection.prepareStatement("select ID, Name from Menu where ParentID is null"); } rs = ps.executeQuery(); boolean bHadData = false; while (rs.next()) { bHadData = true; int subID = rs.getInt(1); String subName = rs.getString(2); pstatementPop.setInt(1, subID); ResultSet rs2 = pstatementPop.executeQuery(); boolean bNotFirst = false; while (rs2.next()) { if (bNotFirst) { out.print("
"); } else { %>
Popular Articles under <%=subName%>
<% bNotFirst = true; } float fRating = rs2.getFloat(3); if (rs2.wasNull()) { fRating = -1; } %><%=rs2.getString(2)%><% } rs2.close(); if (bNotFirst) { %>
<% } pstatementDate.setInt(1, subID); rs2 = pstatementDate.executeQuery(); bNotFirst = false; while (rs2.next()) {if (bNotFirst) { out.print("
"); } else { %>
Recent Articles under <%=subName%>
<% bNotFirst = true; } float fRating = rs2.getFloat(3); if (rs2.wasNull()) { fRating = -1; } %><%=rs2.getString(2)%><% } rs2.close(); if (bNotFirst) { %>
<% } } rs.close(); ps.close(); pstatementDate.close(); pstatementPop.close(); if (menu != null) { pstatementDate = connection.prepareStatement("select Article.ID, Article.Name, AverageScore from Article inner join Menu on Article.MenuID = Menu.ID left join ArticleVotes on Article.ID = ArticleVotes.ArticleID where Menu.ID=? order by Article.DateCreated DESC limit 0,5;"); pstatementPop = connection.prepareStatement("select Article.ID, Article.Name, AverageScore from Article inner join Menu on Article.MenuID = Menu.ID left join ArticleVotes on Article.ID = ArticleVotes.ArticleID where Menu.ID=? order by ArticleVotes.AverageScore DESC limit 0,5;"); if (menuID != -1) { pstatementPop.setInt(1, menuID); } else { pstatementPop.setNull(1, Types.NULL); } ResultSet rs2 = pstatementPop.executeQuery(); boolean bNotFirst = false; while (rs2.next()) { if (bNotFirst) { out.print("
"); } else { %>
Popular Articles in <%=menu%>
<% bNotFirst = true; } float fRating = rs2.getFloat(3); if (rs2.wasNull()) { fRating = -1; } %><%=rs2.getString(2)%><% } rs2.close(); if (bNotFirst) { %>
<% } if (menuID != -1) { pstatementDate.setInt(1, menuID); } else { pstatementDate.setNull(1, Types.NULL); } rs2 = pstatementDate.executeQuery(); bNotFirst = false; while (rs2.next()) { if (bNotFirst) { out.print("
"); } else { %>
Recent Articles in <%=menu%>
<% bNotFirst = true; } float fRating = rs2.getFloat(3); if (rs2.wasNull()) { fRating = -1; } %><%=rs2.getString(2)%><% } rs2.close(); if (bNotFirst) { %>
<% } pstatementDate.close(); pstatementPop.close(); } connection.close(); %> <% %>