|
1
43 package net.jforum.dao.generic;
44
45 import java.sql.Connection  ;
46 import java.sql.PreparedStatement ;
47 import java.sql.ResultSet ;
48 import java.sql.Statement ;
49 import java.sql.Timestamp ;
50 import java.text.SimpleDateFormat ;
51 import java.util.ArrayList ;
52 import java.util.Date ;
53 import java.util.List ;
54
55 import net.jforum.JForumExecutionContext;
56 import net.jforum.SessionFacade;
57 import net.jforum.dao.DataAccessDriver;
58 import net.jforum.dao.TopicDAO;
59 import net.jforum.entities.Forum;
60 import net.jforum.entities.ForumStats;
61 import net.jforum.entities.LastPostInfo;
62 import net.jforum.entities.ModeratorInfo;
63 import net.jforum.entities.Topic;
64 import net.jforum.entities.User;
65 import net.jforum.util.preferences.ConfigKeys;
66 import net.jforum.util.preferences.SystemGlobals;
67
68
75 public class GenericForumDAO extends AutoKeys implements net.jforum.dao.ForumDAO
76 {
77
80 public Forum selectById(int forumId) throws Exception {
81 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.selectById"));
82 p.setInt(1, forumId);
83
84 ResultSet rs = p.executeQuery();
85
86 Forum f = new Forum();
87
88 if (rs.next()) {
89 f = this.fillForum(rs);
90 }
91
92 rs.close();
93 p.close();
94
95 return f;
96 }
97
98 protected Forum fillForum(ResultSet rs) throws Exception {
99 Forum f = new Forum();
100
101 f.setId(rs.getInt("forum_id"));
102 f.setIdCategories(rs.getInt("categories_id"));
103 f.setName(rs.getString("forum_name"));
104 f.setDescription(rs.getString("forum_desc"));
105 f.setOrder(rs.getInt("forum_order"));
106 f.setTotalTopics(rs.getInt("forum_topics"));
107 f.setLastPostId(rs.getInt("forum_last_post_id"));
108 f.setModerated(rs.getInt("moderated") > 0);
109 f.setTotalPosts(this.countForumPosts(f.getId()));
110
111 return f;
112 }
113
114 protected int countForumPosts(int forumId) throws Exception
115 {
116 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(
117 SystemGlobals.getSql("ForumModel.countForumPosts"));
118 p.setInt(1, forumId);
119 ResultSet rs = p.executeQuery();
120 rs.next();
121
122 int total = rs.getInt(1);
123
124 rs.close();
125 p.close();
126
127 return total;
128 }
129
130
133 public List selectAll() throws Exception {
134 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.selectAll"));
135 List l = new ArrayList ();
136
137 ResultSet rs = p.executeQuery();
138
139 while (rs.next()) {
140 l.add(this.fillForum(rs));
141 }
142
143 rs.close();
144 p.close();
145
146 return l;
147 }
148
149
152 public Forum setOrderUp(Forum forum, Forum related) throws Exception
153 {
154 return this.changeForumOrder(forum, related, true);
155 }
156
157
160 public Forum setOrderDown(Forum forum, Forum related) throws Exception {
161 return this.changeForumOrder(forum, related, false);
162 }
163
164 private Forum changeForumOrder(Forum forum, Forum related, boolean up) throws Exception
165 {
166 int tmpOrder = related.getOrder();
167 related.setOrder(forum.getOrder());
168 forum.setOrder(tmpOrder);
169
170 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.setOrderById"));
172 p.setInt(1, forum.getOrder());
173 p.setInt(2, forum.getId());
174 p.executeUpdate();
175 p.close();
176
177 p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.setOrderById"));
179 p.setInt(1, related.getOrder());
180 p.setInt(2, related.getId());
181 p.executeUpdate();
182 p.close();
183
184 return this.selectById(forum.getId());
185 }
186
187
190 public void delete(int forumId) throws Exception {
191 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.delete"));
192 p.setInt(1, forumId);
193
194 p.executeUpdate();
195
196 p.close();
197 }
198
199
202 public void update(Forum forum) throws Exception {
203 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.update"));
204
205 p.setInt(1, forum.getCategoryId());
206 p.setString(2, forum.getName());
207 p.setString(3, forum.getDescription());
208 p.setInt(4, forum.isModerated() ? 1 : 0);
209 p.setInt(5, forum.getId());
210
211 p.executeUpdate();
214 p.close();
215 }
216
217
220 public int addNew(Forum forum) throws Exception {
221 PreparedStatement pOrder = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.getMaxOrder"));
223 ResultSet rs = pOrder.executeQuery();
224
225 if (rs.next()) {
226 forum.setOrder(rs.getInt(1) + 1);
227 }
228
229 rs.close();
230 pOrder.close();
231
232 PreparedStatement p = this.getStatementForAutoKeys("ForumModel.addNew");
234
235 p.setInt(1, forum.getCategoryId());
236 p.setString(2, forum.getName());
237 p.setString(3, forum.getDescription());
238 p.setInt(4, forum.getOrder());
239 p.setInt(5, forum.isModerated() ? 1 : 0);
240
241 this.setAutoGeneratedKeysQuery(SystemGlobals.getSql("ForumModel.lastGeneratedForumId"));
242 int forumId = this.executeAutoKeysQuery(p);
243
244 p.close();
245
246 forum.setId(forumId);
247 return forumId;
248 }
249
250
253 public void setLastPost(int forumId, int postId) throws Exception {
254 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.updateLastPost"));
255
256 p.setInt(1, postId);
257 p.setInt(2, forumId);
258
259 p.executeUpdate();
260
261 p.close();
262 }
263
264
267 public void incrementTotalTopics(int forumId, int count) throws Exception {
268 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.incrementTotalTopics"));
269 p.setInt(1, count);
270 p.setInt(2, forumId);
271 p.executeUpdate();
272 p.close();
273 }
274
275
278 public void decrementTotalTopics(int forumId, int count) throws Exception {
279 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.decrementTotalTopics"));
280 p.setInt(1, count);
281 p.setInt(2, forumId);
282 p.executeUpdate();
283 p.close();
284
285 int totalTopics = this.getTotalTopics(forumId);
288 if (totalTopics < 1) {
289 this.setLastPost(forumId, 0);
290 }
291 }
292
293 private LastPostInfo getLastPostInfo(int forumId, boolean tryFix) throws Exception
294 {
295 LastPostInfo lpi = new LastPostInfo();
296
297 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.lastPostInfo"));
298 p.setInt(1, forumId);
299
300 ResultSet rs = p.executeQuery();
301
302 if (rs.next()) {
303 lpi.setUsername(rs.getString("username"));
304 lpi.setUserId(rs.getInt("user_id"));
305
306 SimpleDateFormat df = new SimpleDateFormat (SystemGlobals.getValue(ConfigKeys.DATE_TIME_FORMAT));
307 lpi.setPostDate(df.format(rs.getTimestamp("post_time")));
308 lpi.setPostId(rs.getInt("post_id"));
309 lpi.setTopicId(rs.getInt("topic_id"));
310 lpi.setPostTimeMillis(rs.getTimestamp("post_time").getTime());
311 lpi.setTopicReplies(rs.getInt("topic_replies"));
312
313 lpi.setHasInfo(true);
314
315 TopicDAO tm = DataAccessDriver.getInstance().newTopicDAO();
317 Topic t = tm.selectById(lpi.getTopicId());
318
319 if (t.getId() == 0) {
320 tm.fixFirstLastPostId(lpi.getTopicId());
322 }
323
324 tryFix = false;
325 }
326 else if (tryFix) {
327 rs.close();
328 p.close();
329
330 int postId = this.getMaxPostId(forumId);
331
332 p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.latestTopicIdForfix"));
333 p.setInt(1, forumId);
334 rs = p.executeQuery();
335
336 int topicId = -1;
337
338 if (rs.next()) {
339 topicId = rs.getInt("topic_id");
340
341 rs.close();
342 p.close();
343
344 p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.fixLatestPostData"));
346 p.setInt(1, postId);
347 p.setInt(2, topicId);
348 p.executeUpdate();
349 p.close();
350
351 p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.fixForumLatestPostData"));
353 p.setInt(1, postId);
354 p.setInt(2, forumId);
355 p.executeUpdate();
356 }
357 }
358
359 rs.close();
360 p.close();
361
362 return (tryFix ? this.getLastPostInfo(forumId, false) : lpi);
363 }
364
365
368 public LastPostInfo getLastPostInfo(int forumId) throws Exception
369 {
370 return this.getLastPostInfo(forumId, true);
371 }
372
373
376 public List getModeratorList(int forumId) throws Exception
377 {
378 List l = new ArrayList ();
379
380 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(
381 SystemGlobals.getSql("ForumModel.getModeratorList"));
382 p.setInt(1, forumId);
383
384 ResultSet rs = p.executeQuery();
385
386 while (rs.next()) {
387 ModeratorInfo mi = new ModeratorInfo();
388
389 mi.setId(rs.getInt("id"));
390 mi.setName(rs.getString("name"));
391
392 l.add(mi);
393 }
394
395 rs.close();
396 p.close();
397
398 return l;
399 }
400
401
404 public int getTotalMessages() throws Exception {
405 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(
406 SystemGlobals.getSql("ForumModel.totalMessages"));
407 ResultSet rs = p.executeQuery();
408 rs.next();
409
410 int total = rs.getInt("total_messages");
411
412 rs.close();
413 p.close();
414
415 return total;
416 }
417
418
421 public int getTotalTopics(int forumId) throws Exception {
422 int total = 0;
423 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.getTotalTopics"));
424 p.setInt(1, forumId);
425 ResultSet rs = p.executeQuery();
426
427 if (rs.next()) {
428 total = rs.getInt(1);
429 }
430
431 rs.close();
432 p.close();
433
434 return total;
435 }
436
437
440 public int getMaxPostId(int forumId) throws Exception {
441 int id = -1;
442
443 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.getMaxPostId"));
444 p.setInt(1, forumId);
445
446 ResultSet rs = p.executeQuery();
447 if (rs.next()) {
448 id = rs.getInt("post_id");
449 }
450
451 rs.close();
452 p.close();
453
454 return id;
455 }
456
457
460 public void moveTopics(String [] topics, int fromForumId, int toForumId) throws Exception {
461 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.moveTopics"));
462 PreparedStatement t = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PostModel.setForumByTopic"));
463
464 p.setInt(1, toForumId);
465 t.setInt(1, toForumId);
466
467 TopicDAO tdao = DataAccessDriver.getInstance().newTopicDAO();
468
469 Forum f = this.selectById(toForumId);
470
471 for (int i = 0; i < topics.length; i++) {
472 int topicId = Integer.parseInt(topics[i]);
473 p.setInt(2, topicId);
474 t.setInt(2, topicId);
475
476 p.executeUpdate();
477 t.executeUpdate();
478
479 tdao.setModerationStatusByTopic(topicId, f.isModerated());
480 }
481
482 this.decrementTotalTopics(fromForumId, topics.length);
483 this.incrementTotalTopics(toForumId, topics.length);
484
485 this.setLastPost(fromForumId, this.getMaxPostId(fromForumId));
486 this.setLastPost(toForumId, this.getMaxPostId(toForumId));
487
488 p.close();
489 }
490
491
494 public List checkUnreadTopics(int forumId, long lastVisit) throws Exception
495 {
496 List l = new ArrayList ();
497
498 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.checkUnreadTopics"));
499 p.setInt(1, forumId);
500 p.setTimestamp(2, new Timestamp (lastVisit));
501
502 ResultSet rs = p.executeQuery();
503 while (rs.next()) {
504 Topic t = new Topic();
505 t.setId(rs.getInt("topic_id"));
506 t.setTime(new Date (rs.getTimestamp(1).getTime()));
507
508 l.add(t);
509 }
510
511 rs.close();
512 p.close();
513
514 return l;
515 }
516
517
520 public void setModerated(int categoryId, boolean status) throws Exception
521 {
522 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.setModerated"));
523 p.setInt(1, status ? 1 : 0);
524 p.setInt(2, categoryId);
525 p.executeUpdate();
526 p.close();
527 }
528
529
532 public ForumStats getBoardStatus() throws Exception
533 {
534 ForumStats fs = new ForumStats();
535 fs.setPosts(this.getTotalMessages());
536
537 Connection c = JForumExecutionContext.getConnection();
538
539 Statement s = c.createStatement();
541 ResultSet rs = s.executeQuery(SystemGlobals.getSql("UserModel.totalUsers"));
542 rs.next();
543 fs.setUsers(rs.getInt(1));
544 rs.close();
545 s.close();
546
547 s = c.createStatement();
549 rs = s.executeQuery(SystemGlobals.getSql("TopicModel.totalTopics"));
550 rs.next();
551 fs.setTopics(rs.getInt(1));
552 rs.close();
553 s.close();
554
555 s = c.createStatement();
557 rs = s.executeQuery(SystemGlobals.getSql("ForumModel.statsFirstPostTime"));
558 rs.next();
559 Date firstTime = rs.getTimestamp(1);
560 rs.close();
561 s.close();
562
563 Date today = new Date ();
564
565 double perDay = firstTime != null ? fs.getPosts() / this.daysUntilToday(today, firstTime) : 0;
566
567 if (fs.getPosts() > 0 && perDay < 1) {
568 perDay = 1;
569 }
570
571 fs.setPostsPerDay(perDay);
572
573 perDay = firstTime != null ? fs.getTopics() / this.daysUntilToday(today, firstTime) : 0;
575
576 fs.setTopicsPerDay(perDay);
577
578 s = c.createStatement();
580 rs = s.executeQuery(SystemGlobals.getSql("ForumModel.statsFirstRegisteredUserTime"));
581 rs.next();
582 firstTime = rs.getTimestamp(1);
583 rs.close();
584 s.close();
585
586 perDay = fs.getUsers() / this.daysUntilToday(today, firstTime);
587
588 fs.setUsersPerDay(perDay);
589
590 return fs;
591 }
592
593 private int daysUntilToday(Date today, Date from)
594 {
595 int days = (int)((today.getTime() - from.getTime()) / (24 * 60 * 60 * 1000));
596 return days == 0 ? 1 : days;
597 }
598
599
602
606 public List notifyUsers(Forum forum) throws Exception {
607 int posterId = SessionFacade.getUserSession().getUserId();
608 int anonUser = SystemGlobals.getIntValue(ConfigKeys.ANONYMOUS_USER_ID);
609
610 PreparedStatement stmt = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.notifyUsers"));
611 ResultSet rs = null;
612
613 stmt.setInt(1, forum.getId());
614 stmt.setInt(2, posterId); stmt.setInt(3, anonUser);
617 rs = stmt.executeQuery();
618
619 List users = new ArrayList ();
620 while(rs.next()) {
621 User user = new User();
622
623 user.setId(rs.getInt("user_id"));
624 user.setEmail(rs.getString("user_email"));
625 user.setUsername(rs.getString("username"));
626 user.setLang(rs.getString("user_lang"));
627
628 users.add(user);
629 }
630
631 rs.close();
641 stmt.close();
642
643 return users;
644
645 }
646
647 public void subscribeUser(int forumId, int userId) throws Exception {
648 PreparedStatement p = JForumExecutionContext.getConnection(). prepareStatement(SystemGlobals.getSql("ForumModel.subscribeUser"));
649
650 p.setInt(1, forumId);
651 p.setInt(2, userId);
652
653 p.executeUpdate();
654 p.close();
655
656 }
657
658 public boolean isUserSubscribed(int forumId, int userId) throws Exception {
659 PreparedStatement stmt = JForumExecutionContext.getConnection(). prepareStatement( SystemGlobals.getSql("ForumModel.isUserSubscribed"));
660 ResultSet rs = null;
661
662 stmt.setInt(1, forumId);
663 stmt.setInt(2, userId);
664
665 rs = stmt.executeQuery();
666 boolean status = rs.next();
667
668 rs.close();
669 stmt.close();
670
671 return status;
672 }
673
674 public void removeSubscription(int forumId, int userId) throws Exception {
675 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.removeSubscription"));
676 p.setInt(1, forumId);
677 p.setInt(2, userId);
678
679 p.executeUpdate();
680 p.close();
681
682 }
683
684
691 public void removeSubscriptionByForum(int forumId) throws Exception {
692 PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("ForumModel.removeSubscriptionByForum"));
693 p.setInt(1, forumId);
694
695 p.executeUpdate();
696 p.close();
697
698 }
699 }
700 |
Popular Tags |
Java API By Example, From Geeks To Geeks.