1 package org.paneris.messageboard.model;
2
3 import java.sql.Connection;
4 import java.sql.ResultSet;
5 import java.sql.Statement;
6
7 import org.paneris.jal.model.DBConnectionManager;
8 import org.paneris.jal.model.DDField;
9 import org.paneris.jal.model.DDRecord;
10 import org.paneris.jal.model.Email;
11 import org.paneris.jal.model.SystemProperties;
12 import org.paneris.user.model.User;
13 import org.webmacro.servlet.HandlerException;
14
15
16
17
18
19
20 public class Board extends DDRecord {
21
22 private static final long serialVersionUID = 1L;
23 private Integer messages;
24 private Integer subscribers;
25 private DBConnectionManager connMgr;
26
27
28
29
30
31
32
33 public Board(String db, ResultSet rs) throws Exception {
34 super(db,"messageboards",rs);
35 setMessages();
36 }
37
38
39
40
41
42
43 public Board(String db) throws Exception {
44 super(db,"messageboards");
45 setMessages();
46 }
47
48
49
50
51
52
53 public Board(String db, Integer record) throws Exception {
54 super(db,"messageboards",record);
55 setMessages();
56 }
57
58
59
60
61
62 private void setMessages() throws Exception {
63 connMgr = DBConnectionManager.getInstance();
64 Connection conn = connMgr.getConnection("Board",database);
65 Statement s = conn.createStatement();
66 DDField idField = (DDField) get("id");
67 String sqlString = new String("SELECT COUNT(*) FROM messages WHERE board = " + (Integer)idField.getValue());
68 ResultSet rs = s.executeQuery(sqlString);
69 rs.next();
70 messages = new Integer(rs.getInt(1));
71 sqlString = new String("SELECT COUNT(*) FROM usersboards WHERE board = " + (Integer)idField.getValue());
72 rs = s.executeQuery(sqlString);
73 rs.next();
74 subscribers = new Integer(rs.getInt(1));
75 connMgr.freeConnection(database, conn);
76 }
77
78 public Integer getMessages() {
79 return messages;
80 }
81
82 public Integer getSubscribers() {
83 return subscribers;
84 }
85
86 public String getServletURL() {
87 String surl = "";
88 try {
89 SystemProperties sp = new SystemProperties(database);
90 surl = sp.getProperty("servleturl");
91 } catch (Exception e) {
92 ;
93 }
94 return surl;
95 }
96
97 public String getMessageboardURL() {
98 return getServletURL() + "MessageBoard?db=" + database + "&messageboard=" + getFieldValue("id");
99 }
100 public String getUnsubscribeURL() {
101 return getServletURL() + "Subscribe?subscribe=false&db=" + database + "&board=" + getFieldValue("id") + "&message=unsubscribe";
102 }
103
104 public String getMessageURL(String id) {
105 return getServletURL() + "MessagePage?db=" + database + "&messageboard=" + getFieldValue("id") + "&id=" + id;
106 }
107
108 public String getMessageboardEmail() {
109 String murl = "";
110 try {
111 SystemProperties sp = new SystemProperties(database);
112 murl = sp.getProperty("messageboardurl");
113 } catch (Exception e) {
114 murl = "messageboards.paneris.org";
115 }
116 return getFieldValue("name").trim() + "@" + murl;
117 }
118
119 public static Integer getLurkerBoard(String db) {
120 Integer lurk = new Integer(0);
121 try {
122 SystemProperties sp = new SystemProperties(db);
123 lurk = new Integer(sp.getProperty("lurkerboard"));
124 } catch (Exception e) {
125 ;
126 }
127 return lurk;
128 }
129
130
131
132
133
134
135
136
137 public void subscribe(User user) throws Exception {
138 if (!isSubscribed(user)) {
139 connMgr = DBConnectionManager.getInstance();
140 Connection conn = connMgr.getConnection("board",database);
141 Statement s = conn.createStatement();
142 DDField boardId = (DDField) get("id");
143 DDField userId = (DDField) user.get("id");
144 String sqlString = new String("INSERT INTO usersboards (username, board) VALUES (" + (Integer)userId.getValue() + "," + (Integer)boardId.getValue() + ")");
145 try {
146 s.executeUpdate(sqlString);
147 } catch (Exception e) {
148 throw new HandlerException("Failed to subscribe user " + (Integer)userId.getValue() + " to board " + (Integer)boardId.getValue() + " because:" + e.toString() + " sqlString: " + sqlString);
149 }
150
151 User systemuser = User.getSystemUser(database);
152 String message = "Thank you for subscribing to the " + getFieldValue("description").trim() + " messageboard.";
153 message += "\n\nTo post to this board, simply email " + getMessageboardEmail();
154 message += "\n\nYou can access the " + getFieldValue("description").trim() + " messageboard online at";
155 message += "\n" + getMessageboardURL();
156 message += "\nWhere you can read the archive, post messages and unsubscribe.";
157 if (!getFieldValue("charter").trim().equals("")) {
158 message += "\n\nBoard Charter:\n " + getFieldValue("charter").trim();
159 }
160 Email.send(database, systemuser.getFieldValue("email"), user.getFieldValue("email"), "", "Welcome to the " + getFieldValue("description").trim() + " messageboard", message);
161
162
163 Message m = new Message(database);
164 message = "A new user has joined the " + getFieldValue("description").trim() + " messageboard.";
165 message += "\n" + getMessageboardURL();
166 message += "\n\nTheir details are:";
167 message += "\nName: " + user.getFieldValue("username");
168 message += "\nEmail: " + user.getFieldValue("email");
169 m.setFieldValue("message", message);
170 m.setFieldValue("parent", "0");
171 m.setFieldValue("subject", "New Subscriber");
172 m.setFieldValue("board", getLurkerBoard(database));
173 if (systemuser != null) {
174 m.setFieldValue("author", systemuser.getFieldValue("id"));
175 }
176 m.write();
177 m.distribute();
178 connMgr.freeConnection(database, conn);
179 }
180 }
181
182
183
184
185
186
187 public void unSubscribe(User user) throws Exception {
188 if (isSubscribed(user)) {
189 connMgr = DBConnectionManager.getInstance();
190 Connection conn = connMgr.getConnection("Board",database);
191 Statement s = conn.createStatement();
192 DDField boardId = (DDField) get("id");
193 DDField userId = (DDField) user.get("id");
194 String sqlString = new String("DELETE FROM usersboards WHERE username = " + (Integer)userId.getValue() + " AND board = " + (Integer)boardId.getValue());
195 try {
196 s.executeUpdate(sqlString);
197 } catch (Exception e) {
198 throw new HandlerException("Failed to unSubscribe user " + (Integer)userId.getValue() + " to board " + (Integer)boardId.getValue() + " because:" + e.toString() + " sqlString: " + sqlString);
199 }
200
201
202 Message m = new Message(database);
203 User systemuser = User.getSystemUser(database);
204 String message = "A user has unsubscribed from the " + getFieldValue("description").trim() + " messageboard.";
205 message += "\n\nTheir details are:";
206 message += "\nName: " + user.getFieldValue("username");
207 message += "\nEmail: " + user.getFieldValue("email");
208 m.setFieldValue("message", message);
209 m.setFieldValue("parent", "0");
210 m.setFieldValue("subject", "Unsubscriber");
211 m.setFieldValue("board", getLurkerBoard(database));
212 if (systemuser != null) {
213 m.setFieldValue("author", systemuser.getFieldValue("id"));
214 }
215 m.write();
216 m.distribute();
217 connMgr.freeConnection(database, conn);
218 }
219 }
220
221
222
223
224
225
226
227 public boolean isSubscribed(User user) throws Exception {
228 connMgr = DBConnectionManager.getInstance();
229 Connection conn = connMgr.getConnection("Board",database);
230 Statement s = conn.createStatement();
231 DDField boardId = (DDField) get("id");
232 DDField userId = (DDField) user.get("id");
233 String sqlString = new String("SELECT id FROM usersboards WHERE username = " + (Integer)userId.getValue() + " AND board = " + (Integer)boardId.getValue());
234 try {
235 ResultSet rs = s.executeQuery(sqlString);
236 if (rs.next()) {
237 connMgr.freeConnection(database, conn);
238 return true;
239 }
240 } catch (Exception e) {
241 throw new HandlerException("Failed to determin if user " + (Integer)userId.getValue() + " is subscribed to board " + (Integer)boardId.getValue() + " because:" + e.toString() + " sqlString: " + sqlString);
242 }
243 connMgr.freeConnection(database, conn);
244 return false;
245 }
246 }
247