Java > Open Source Codes > net > killingar > forum > internal > managers > PollManager


1 /* Copyright 2000-2005 Anders Hovmöller
2  *
3  * The person or persons who have associated their work with
4  * this document (the "Dedicator") hereby dedicate the entire
5  * copyright in the work of authorship identified below (the
6  * "Work") to the public domain.
7  *
8  * Dedicator makes this dedication for the benefit of the
9  * public at large and to the detriment of Dedicator's heirs
10  * and successors. Dedicator intends this dedication to be an
11  * overt act of relinquishment in perpetuity of all present
12  * and future rights under copyright law, whether vested or
13  * contingent, in the Work. Dedicator understands that such
14  * relinquishment of all rights includes the relinquishment of
15  * all rights to enforce (by lawsuit or otherwise) those
16  * copyrights in the Work.
17  *
18  * Dedicator recognizes that, once placed in the public
19  * domain, the Work may be freely reproduced, distributed,
20  * transmitted, used, modified, built upon, or otherwise
21  * exploited by anyone for any purpose, commercial or non-
22  * commercial, and in any way, including by methods that have
23  * not yet been invented or conceived.
24  */
25
26 /**
27  * Manager for polls.
28  */
29 package net.killingar.forum.internal.managers;
30
31 import net.killingar.forum.internal.*;
32
33 import java.sql.Connection ;
34 import java.sql.ResultSet ;
35 import java.sql.SQLException ;
36 import java.sql.Statement ;
37 import java.sql.PreparedStatement ;
38 import java.util.ArrayList ;
39
40 public class PollManager extends AbstractManager implements java.io.Serializable
41 {
42     //protected static transient PollsCache pollsCache = new PollsCache();
43
44     /**
45      * Add a poll.
46      */
47     public long addPoll(Poll poll) throws SQLException , AccessDeniedException
48     {
49         manager.checkMyAccess(AccessLevel.addPoll);
50         if (poll.groupID == -1)
51             manager.checkMyAccess(AccessLevel.addGlobalPoll);
52         if (poll.groupID != -1 && !manager.isUserInGroup(manager.getUserID(), poll.groupID))
53             throw new AccessDeniedException("attempt to add a poll to a restricted group");
54
55         Connection c = null;
56         Statement statement = null;
57         ResultSet result = null;
58
59         try
60         {
61             c = getNewConnection();
62             statement = c.createStatement();
63
64             statement.executeUpdate(
65                 "insert into Polls(User, UserGroup, Question, Time, LastChanged, LastChangedUser) values ("+
66                     manager.getUserID()+", "+
67                     ((poll.groupID == -1)?"null":Long.toString(poll.groupID))+", '"+
68                     Utils.disableSQL(poll.question)+"', NOW(), NOW(), "+manager.getUserID()+")");
69
70             result = statement.getGeneratedKeys();
71             result.next();
72             return result.getLong(1);
73         }
74         finally { closeAll(c, statement, result); }
75     }
76
77     /**
78      * Add a poll alternative to a poll.
79      */
80     public long addPollAlternative(PollAlternative pollAlternative) throws SQLException , AccessDeniedException
81     {
82         Poll poll = getPoll(pollAlternative.pollID);
83         if (poll.ownerID != manager.getUserID())
84             manager.checkMyAccess(AccessLevel.changePoll);
85
86         Connection c = null;
87         Statement statement = null;
88         ResultSet result = null;
89
90         try
91         {
92             c = getNewConnection();
93             statement = c.createStatement();
94
95         statement.executeUpdate("insert into PollAlternatives(Poll, Alternative, Color, LastChanged, LastChangedUser) values ("+pollAlternative.pollID+", '"+Utils.disableSQL(pollAlternative.alternative)+"', '"+Utils.disableSQL(pollAlternative.color)+"', NOW(), "+manager.getUserID()+")");
96             statement.executeUpdate("update Polls set LastChanged = NOW(), LastChangedUser = "+manager.getUserID()+" where ID = "+poll.getId());
97
98             result = statement.getGeneratedKeys();
99             result.next();
100             return result.getLong(1);
101         }
102         finally { closeAll(c, statement, result); }
103     }
104
105     /**
106      * Remove a poll.
107      */
108     public void removePoll(long pollID) throws SQLException , AccessDeniedException
109     {
110         Poll poll = getPoll(pollID);
111         if (poll.ownerID != manager.getUserID())
112             manager.checkMyAccess(AccessLevel.removePoll);
113
114         Connection c = null;
115         Statement statement = null;
116         ResultSet result = null;
117
118         try
119         {
120             c = getNewConnection();
121             statement = c.createStatement();
122
123             statement.executeUpdate("update Polls set LastChanged = NOW(), LastChangedUser = "+manager.getUserID()+", Visible = 0 where ID = "+pollID);
124         }
125         finally { closeAll(c, statement, result); }
126     }
127
128     /**
129      * Remova a poll alternative.
130      */
131     public void removePollAlternative(long pollAlternativeID) throws SQLException , AccessDeniedException
132     {
133         Poll poll = getPoll(getPollAlternative(pollAlternativeID).pollID);
134         if (poll.ownerID != manager.getUserID())
135             manager.checkMyAccess(AccessLevel.changePoll);
136
137         Connection c = null;
138         Statement statement = null;
139         ResultSet result = null;
140
141         try
142         {
143             c = getNewConnection();
144             statement = c.createStatement();
145
146             statement.executeUpdate("delete from PollAlternatives where ID = "+pollAlternativeID);
147             statement.executeUpdate("delete from PollAnswers where Reply = "+pollAlternativeID);
148             statement.executeUpdate("update Polls set LastChanged = NOW(), LastChangedUser = "+manager.getUserID()+" where ID = "+poll.getId());
149         }
150         finally { closeAll(c, statement, result); }
151     }
152
153     /**
154      * Change a poll.
155      */
156     public void changePoll(Poll poll) throws SQLException , AccessDeniedException
157     {
158         Poll _poll = getPoll(poll.ID);
159         if (_poll.ownerID != manager.getUserID())
160             manager.checkMyAccess(AccessLevel.changePoll);
161
162         Connection c = null;
163         Statement statement = null;
164         ResultSet result = null;
165
166         try
167         {
168             c = getNewConnection();
169             statement = c.createStatement();
170
171             statement.executeUpdate("update Polls set LastChanged = NOW(), LastChangedUser = "+manager.getUserID()+", UserGroup = "+poll.groupID+", Question = '"+Utils.disableSQL(poll.question)+"', Time = now() where ID = "+poll.ID);
172         }
173         finally { closeAll(c, statement, result); }
174     }
175
176     /**
177      * Change a poll alternative.
178      */
179     public void changePollAlternative(PollAlternative pollAlternative) throws SQLException , AccessDeniedException
180     {
181         Poll poll = getPoll(pollAlternative.pollID);
182         if (poll.ownerID != manager.getUserID())
183             manager.checkMyAccess(AccessLevel.changePoll);
184
185         // validate
186 Integer.parseInt(pollAlternative.color.substring(0, 2), 16);
187         Integer.parseInt(pollAlternative.color.substring(2, 4), 16);
188         Integer.parseInt(pollAlternative.color.substring(4, 6), 16);
189
190         pollAlternative.color = pollAlternative.color.substring(0, 6);
191
192         Connection c = null;
193         Statement statement = null;
194         ResultSet result = null;
195
196         try
197         {
198             c = getNewConnection();
199             statement = c.createStatement();
200
201             statement.executeUpdate("update PollAlternatives set Alternative = '"+Utils.disableSQL(pollAlternative.alternative)+"', color = '"+Utils.disableSQL(pollAlternative.color)+"' where ID = "+pollAlternative.ID);
202             statement.executeUpdate("update Polls set LastChanged = NOW(), LastChangedUser = "+manager.getUserID()+" where ID = "+poll.getId());
203         }
204         finally { closeAll(c, statement, result); }
205     }
206
207     /**
208      * Get all polls the current user have access to.
209      */
210     public Poll[] getPolls() throws SQLException
211     {
212         Connection c = null;
213         Statement statement = null;
214         ResultSet result = null;
215
216         try
217         {
218             c = getNewConnection();
219             statement = c.createStatement();
220
221             String whereClause = manager.hasAccess(manager.getUserID(), AccessLevel.viewPoll)? " where Visible = 1": " where Visible = 1 AND User = "+manager.getUserID()+manager.getGroupsString("UserGroup", " OR ")+" OR UserGroup is null";
222
223             result = statement.executeQuery("select ID, User, UserGroup, Question, Time, LastChanged from Polls"+whereClause+" order by ID desc");
224             ArrayList list = new ArrayList ();
225             while (result.next())
226             {
227                 Poll poll = new Poll(
228                     result.getLong(1),
229                     result.getLong(2),
230                     result.getString(3) == null? -1: result.getLong(3),
231                     result.getString(4),
232                     result.getTimestamp(5),
233                     result.getTimestamp(6));
234
235                 list.add(poll);
236             }
237
238             Poll r[] = new Poll[list.size()];
239             list.toArray(r);
240
241             return r;
242         }
243         finally { closeAll(c, statement, result); }
244     }
245
246     /**
247      * Get a poll.
248      */
249     public Poll getPoll(long pollID) throws SQLException , AccessDeniedException
250     {
251         Connection c = null;
252         Statement statement = null;
253         ResultSet result = null;
254
255         try
256         {
257             c = getNewConnection();
258             statement = c.createStatement();
259
260             result = statement.executeQuery("select ID, User, UserGroup, Question, Time, LastChanged from Polls where ID = "+pollID);
261             result.next();
262             Poll poll = new Poll(
263                 result.getLong(1),
264                 result.getLong(2),
265                 result.getString(3) == null? -1: result.getLong(3),
266                 result.getString(4),
267                 result.getTimestamp(5),
268                 result.getTimestamp(6));
269             if (poll.groupID != -1 && !manager.isUserInGroup(manager.getUserID(), poll.groupID))
270                 manager.checkMyAccess(AccessLevel.viewPoll);
271             return poll;
272         }
273         finally { closeAll(c, statement, result); }
274     }
275
276     /**
277      * Get a random poll the current user has access to.
278      */
279     public Poll getRandomPoll() throws SQLException , AccessDeniedException
280     {
281         Poll[] polls = getPolls();
282
283         if (polls.length == 0)
284             return null;
285
286         return polls[(int)(Math.random()*(double)polls.length)];
287     }
288
289     /**
290      * Get a random poll the user has access to.
291      */
292     public Poll[] getUnAnsweredPolls() throws SQLException , AccessDeniedException
293     {
294         /*String groupsString = "";
295
296         if (!manager.hasAccess(AccessLevel.viewPoll))
297         {
298             Group groups[] = manager.getGroupsOfUser(manager.getUserID());
299             StringBuffer s = new StringBuffer("AND (Polls.UserGroup = -1 or Polls.UserGroup is null");
300             for (int i = 0; i < groups.length; i++)
301             {
302                 s.append(" or Polls.UserGroup = ");
303                 s.append(groups[i].getId());
304             }
305
306             s.append(")");
307             groupsString = s.toString();
308         }
309
310         Connection c = null;
311         Statement statement = null;
312         ResultSet result = null;
313
314         try
315         {
316             c = getNewConnection();
317             statement = c.createStatement();
318
319             Poll poll = null;
320             result = statement.executeQuery("select * from Users, Polls left join PollAnswers on Polls.ID = PollAnswers.Poll where Visible = 1 AND PollAnswers.User = Users.ID AND Users.ID = "+manager.getUserID()+" AND PollAnswers.Reply is null "+groupsString);
321
322             ArrayList list = new ArrayList();
323             while (result.next())
324             {
325                 poll = new Poll(
326                     result.getLong("Polls.ID"),
327                     result.getLong("Polls.User"),
328                     result.getString(3) == null? -1: result.getLong("Polls.UserGroup"),
329                     result.getString("Polls.Question"),
330                     result.getTimestamp("Polls.Time"),
331                     result.getTimestamp("Polls.LastChanged"));
332
333                 // double check access
334                 if (poll.groupID != -1 && !manager.isUserInGroup(manager.getUserID(), poll.groupID))
335                     manager.checkMyAccess(AccessLevel.viewPoll);
336
337                 list.add(poll);
338             }
339       Poll r[] = new Poll[list.size()];
340             list.toArray(r);
341
342             return r;
343         }
344         finally { closeAll(c, statement, result); }*/
345
346         Connection c = null;
347         Statement statement = null;
348         ResultSet result = null;
349
350         try
351         {
352             Poll[] polls = getPolls();
353             StringBuffer whereClause = new StringBuffer ("User = ");
354             whereClause.append(manager.getUserID());
355
356             if (polls.length != 0)
357             {
358                 whereClause.append(" and (");
359
360                 for (int i = 0; i != polls.length; i++)
361                 {
362                     if (i != 0)
363                         whereClause.append(" or ");
364
365                     whereClause.append("Poll = ");
366                     whereClause.append(polls[i].getId());
367                 }
368
369                 whereClause.append(")");
370             }
371
372             c = getNewConnection();
373             statement = c.createStatement();
374
375             result = statement.executeQuery("select Poll from PollAnswers where "+whereClause);
376
377             int count = polls.length;
378             while (result.next())
379             {
380                 long id = result.getLong(1);
381                 for (int i = 0; i != polls.length; i++)
382                 {
383                     if (polls[i] == null)
384                         continue;
385
386                     if (polls[i].getId() == id)
387                     {
388                         // this poll has been answered, remove it from the list
389 polls[i] = null;
390                         count--;
391                         continue;
392                     }
393                 }
394             }
395
396             Poll[] r = new Poll[count];
397             int c2 = 0;
398             for (int i = 0; i != polls.length; i++)
399             {
400                 if (polls[i] == null)
401                     continue;
402
403                 r[c2++] = polls[i];
404             }
405
406             // count should be equal to c2 here
407
408             return r;
409         }
410         finally { closeAll(c, statement, result); }
411     }
412
413     /**
414      * Get a random poll the user has access to and hasn't answered.
415      */
416     public Poll getRandomUnAnsweredPoll() throws SQLException , AccessDeniedException
417     {
418         Poll[] polls = getUnAnsweredPolls();
419
420         //System.err.println("INFO: PollManager.getUnAnsweredPolls() returned "+polls.length+" polls");
421
422         if (polls.length == 0)
423             return null;
424
425         return polls[(int)(Math.random()*(double)polls.length)];
426     }
427
428     /**
429      * Get poll alternatives of a specific poll.
430      */
431     public PollAlternative[] getPollAlternatives(long pollID) throws SQLException , AccessDeniedException
432     {
433         Poll poll = getPoll(pollID);
434         if (poll.groupID != -1 && !manager.isUserInGroup(manager.getUserID(), poll.groupID))
435             manager.checkMyAccess(AccessLevel.viewPoll);
436
437         Connection c = null;
438         Statement statement = null;
439         ResultSet result = null;
440
441         try
442         {
443             c = getNewConnection();
444             statement = c.createStatement();
445
446             result = statement.executeQuery(
447                 "select "+
448                     "PollAlternatives.ID, "+
449                     "PollAlternatives.Alternative, "+
450                     "PollAlternatives.Color, "+
451                     "count(PollAnswers.Reply) "+
452                 "from "+
453                     "PollAlternatives "+
454                 "left join PollAnswers on PollAlternatives.ID = PollAnswers.Reply "+
455                 "where "+
456                     "PollAlternatives.Poll = "+pollID+" "+
457                 "group by "+
458                     "PollAlternatives.ID");
459             ArrayList list = new ArrayList ();
460             while (result.next())
461                 list.add(new PollAlternative(result.getLong(1), pollID, result.getString(2), result.getString(3), result.getLong(4)));
462             PollAlternative r[] = new PollAlternative[list.size()];
463             list.toArray(r);
464
465             return r;
466         }
467         finally { closeAll(c, statement, result); }
468     }
469
470     /**
471      * Get a poll alternative.
472      */
473     public PollAlternative getPollAlternative(long pollAlternativeID) throws SQLException , AccessDeniedException
474     {
475         Connection c = null;
476         Statement statement = null;
477         ResultSet result = null;
478
479         try
480         {
481             c = getNewConnection();
482             statement = c.createStatement();
483
484             result = statement.executeQuery(
485                 "select "+
486                     "PollAlternatives.Poll, "+
487                     "PollAlternatives.Alternative, "+
488                     "PollAlternatives.Color, "+
489                     "count(PollAnswers.Reply) "+
490                 "from "+
491                     "PollAlternatives "+
492                 "left join PollAnswers on PollAlternatives.ID = PollAnswers.Poll "+
493                 "where "+
494                     "PollAlternatives.ID = "+pollAlternativeID+" "+
495                 "group by "+
496                     "PollAlternatives.Poll");
497
498             PollAlternative pollAlternative = null;
499             if (result.next())
500             {
501                 pollAlternative = new PollAlternative(
502                     pollAlternativeID,
503                     result.getLong(1),
504                     result.getString(2),
505                     result.getString(3),
506                     result.getLong(4));
507                 Poll poll = getPoll(pollAlternative.pollID);
508                 if (poll.groupID != -1 && !manager.isUserInGroup(manager.getUserID(), poll.groupID))
509                     manager.checkMyAccess(AccessLevel.viewPoll);
510             }
511
512             return pollAlternative;
513         }
514         finally { closeAll(c, statement, result); }
515     }
516
517     /**
518      * Get the current users answer on a certain poll. Returns null if the user has no answer set on this poll.
519      */
520     public PollAnswer getMyAnswer(long pollID) throws SQLException , AccessDeniedException
521     {
522         Connection c = null;
523         Statement statement = null;
524         ResultSet result = null;
525
526         try
527         {
528             c = getNewConnection();
529             statement = c.createStatement();
530
531             result = statement.executeQuery("select Reply, LastChanged from PollAnswers where User = "+manager.getUserID()+" AND Poll = "+pollID);
532             PollAnswer r = null;
533             if (result.next())
534                 r = new PollAnswer(pollID, result.getLong(1), result.getTimestamp(2));
535             return r;
536         }
537         finally { closeAll(c, statement, result); }
538     }
539
540     /**
541      * Sets the current users answer on a given poll.
542      * The poll specified is implicit since a given poll alternative ID maps to
543      */
544     public void setMyAnswer(long pollAlternativeID) throws SQLException , AccessDeniedException
545     {
546         Connection c = null;
547         PreparedStatement statement = null;
548         ResultSet result = null;
549
550         try
551         {
552             PollAlternative pollAlternative = getPollAlternative(pollAlternativeID);
553
554             c = getNewConnection();
555             statement = c.prepareStatement("delete from PollAnswers where Poll = ? and User = ?");
556             statement.setLong(1, pollAlternative.pollID);
557             statement.setLong(2, manager.getUserID());
558             statement.executeUpdate();
559             statement.close();
560
561             statement = c.prepareStatement("insert into PollAnswers (Poll, User, Reply, Time) values (?, ?, ?, NOW())");
562             statement.setLong(1, pollAlternative.pollID);
563             statement.setLong(2, manager.getUserID());
564             statement.setLong(3, pollAlternative.ID);
565             statement.executeUpdate();
566         }
567         finally { closeAll(c, statement, result); }
568     }
569
570     public boolean isUnread() throws Exception
571     {
572         TimeManager times = ((TimeManager)manager.getManager(TimeManager.class.getName()));
573
574         Connection c = null;
575         Statement statement = null;
576         ResultSet result = null;
577
578         try
579         {
580             c = getNewConnection();
581             statement = c.createStatement();
582
583             result = statement.executeQuery(
584                 "select "+
585                     "MAX(Polls.LastChanged > PollAnswers.LastChanged) "+
586                 "from "+
587                     "Users, "+
588                     "Polls "+
589                 "left join "+
590                     "PollAnswers "+
591                 "on "+
592                     "Polls.ID = PollAnswers.Poll "+
593                 "where "+
594                     "Polls.Visible = 1 AND "+
595                     "Users.ID = "+manager.getUserID()+" AND "+
596                     "Users.ID = PollAnswers.User ");
597
598             if (result.next() && result.getInt(1) == 0)
599             {
600                 // no newly changed polls, check for unanswered polls
601 result.close();
602                 result = statement.executeQuery("select count(*) from Polls where Visible = 1");
603                 result.next();
604                 int pollCount = result.getInt(1);
605
606                 result.close();
607                 result = statement.executeQuery("select count(*) from PollAnswers inner join Polls on PollAnswers.Poll = Polls.ID where Visible = 1 and PollAnswers.User = "+manager.getUserID());
608                 result.next();
609                 int answeredPollCount = result.getInt(1);
610
611                 return pollCount != answeredPollCount;
612             }
613             else
614                 return false;
615         }
616         finally { closeAll(c, statement, result); }
617     }
618 }

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags