1 package org.paneris.messageboard.controller;
2
3 import java.sql.Connection;
4 import java.sql.ResultSet;
5 import java.sql.Statement;
6 import java.util.Enumeration;
7 import java.util.Vector;
8
9 import org.paneris.jal.model.DBConnectionManager;
10 import org.paneris.jal.model.HTMLUtil;
11 import org.paneris.jal.model.RecordSet;
12 import org.paneris.messageboard.model.Board;
13 import org.paneris.messageboard.model.Message;
14 import org.paneris.user.model.User;
15 import org.webmacro.Template;
16 import org.webmacro.servlet.HandlerException;
17 import org.webmacro.servlet.PanerisPage;
18 import org.webmacro.servlet.WebContext;
19
20 public class SearchMessages extends PanerisPage {
21
22 private static final long serialVersionUID = 1L;
23 private DBConnectionManager connMgr = DBConnectionManager.getInstance();;
24
25 public Template handle(WebContext context) throws HandlerException {
26 String db = (String) context.getForm("db");
27 context.put("db",db);
28 Connection conn = connMgr.getConnection("SearchMessages",db);
29 String submit = (String) context.getForm("submit");
30 String next = (String) context.getForm("next");
31 String previous = (String) context.getForm("previous");
32 String templateName = null;
33 try {
34 templateName = User.checkLoggedIn("controller", this.getClass().getName(), context, " search the Messageboards.");
35 } catch (Exception e) {
36 throw new HandlerException("Could not get user:" + e.toString());
37 }
38 if (templateName == null) {
39 templateName = (String) context.getForm("wmtemplate");
40 if (templateName == null) {
41 templateName = "messageboard/view/SearchBoards.wm";
42 }
43 try {
44 context.put("types", new RecordSet(db,"messageboards_types", "SELECT id, type FROM messageboards_types ORDER BY display_order",new Integer(0)));
45 String board = (String) context.getForm("board");
46 if (board != null && (!board.equals("0"))) {
47 context.put("board", new Board(db,new Integer(board)));
48 }
49 String messagestatus = (String) context.getForm("messagestatus");
50 String username = (String) context.getForm("username");
51 String sqlString = "SELECT id, description FROM messageboards ORDER BY description";
52 Statement s = conn.createStatement();
53 ResultSet rs = s.executeQuery(sqlString);
54 context.put("dropdown",HTMLUtil.getDropDown(rs, "board", true, null, board));
55 sqlString = "SELECT id, status FROM messagestatus ORDER BY status";
56 rs = s.executeQuery(sqlString);
57 context.put("statusdropdown",HTMLUtil.getDropDown(rs, "messagestatus", true, "All", messagestatus));
58 sqlString = "SELECT id, username FROM users ORDER BY username";
59 rs = s.executeQuery(sqlString);
60 context.put("usersdropdown",HTMLUtil.getDropDown(rs, "username", true, "All", username));
61 String searchString = (String) context.getForm("searchstring");
62 if (submit != null || next != null || previous != null) {
63 templateName = (String) context.getForm("wmtemplate");
64 if (templateName == null) {
65 templateName = "messageboard/view/SearchResults.wm";
66 }
67 RecordSet set = RecordSet.getInstance(context, "messages");
68 if (set == null) {
69 sqlString = "SELECT messages.* FROM messages, messageboards"
70 + " WHERE messages.board = messageboards.id";
71 if (!searchString.equals("")) {
72 if (connMgr.getDatabaseEngineType(db) == DBConnectionManager.POSTGRES) {
73 sqlString += " AND (messages.subject ~* '" + searchString + "'"
74 + " OR messages.message ~* '" + searchString + "')";
75 } else {
76 sqlString += " AND (messages.subject LIKE '%" + searchString + "%'"
77 + " OR messages.message LIKE '%" + searchString + "%')";
78 }
79 }
80 if (board != null && (!board.equals("0"))) {
81 sqlString += " AND messageboards.id = " + board;
82 }
83 if (messagestatus != null && (!messagestatus.equals("0"))) {
84 sqlString += " AND messages.status = " + messagestatus;
85 }
86 if (username != null && (!username.equals("0"))) {
87 sqlString += " AND messages.author = " + username;
88 }
89 sqlString += " ORDER BY messages.messagedate DESC";
90 set = RecordSet.getInstance(context, db, "messages", sqlString, null);
91 }
92 context.put("navigation", set);
93 Vector results = new Vector();
94 for (Enumeration en = set.getNext(); en.hasMoreElements();) {
95 Integer id = (Integer) en.nextElement();
96 results.addElement(new Message(db,id));
97 }
98 context.put("results", results);
99 context.put("wmtemplate", templateName);
100 }
101 } catch (Exception e) {
102 throw new HandlerException(e.toString());
103 }
104 }
105 connMgr.freeConnection(db, conn);
106
107
108 try {
109 return (Template) context.getBroker().get("template",templateName);
110 } catch (Exception e) {
111 throw new HandlerException("Could not locate template: " + templateName);
112 }
113 }
114 }