1 #**************************************************************************
2 # Copyright (C) 2006 by Michael Andreen <harvATruinDOTnu> *
4 # This program is free software; you can redistribute it and/or modify *
5 # it under the terms of the GNU General Public License as published by *
6 # the Free Software Foundation; either version 2 of the License, or *
7 # (at your option) any later version. *
9 # This program is distributed in the hope that it will be useful, *
10 # but WITHOUT ANY WARRANTY; without even the implied warranty of *
11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
12 # GNU General Public License for more details. *
14 # You should have received a copy of the GNU General Public License *
15 # along with this program; if not, write to the *
16 # Free Software Foundation, Inc., *
17 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. *
18 #**************************************************************************/
20 package NDWeb::Pages::Forum;
24 use CGI qw/:standard/;
28 use base qw/NDWeb::XMLPage/;
30 $NDWeb::Page::PAGES{forum} = __PACKAGE__;
34 if ($self->{URI} =~ m{^/.*/allUnread}){
35 $self->{allUnread} = 1;
36 }elsif ($self->{URI} =~ m{^/.*/search(?:/(.*))?}){
37 bless $self, 'NDWeb::Pages::Forum::Search';
38 $self->{PAGE} = 'forum/search';
45 $self->{TITLE} = 'Forum';
46 my $DBH = $self->{DBH};
48 $DBH->do(q{UPDATE users SET last_forum_visit = NOW() WHERE uid = $1},undef,$ND::UID) or warn $DBH->errstr;
52 my $boards = $DBH->prepare(q{SELECT fb.fbid AS id,fb.board, bool_or(fa.post) AS post, bool_or(fa.moderate) AS moderate,fb.fcid
53 FROM forum_boards fb NATURAL JOIN forum_access fa
54 WHERE fb.fbid = $1 AND
55 gid IN (SELECT groups($2))
56 GROUP BY fb.fbid,fb.board,fb.fcid
58 $board = $DBH->selectrow_hashref($boards,undef,param('b'),$ND::UID) or warn $DBH->errstr;
60 if (param('markAsRead')){
61 my $threads = $DBH->prepare(q{SELECT ft.ftid AS id,ft.subject
62 ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread
63 ,count(fp.fpid) AS posts, max(fp.time)::timestamp as last_post
65 JOIN forum_posts fp USING (ftid)
66 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
67 WHERE ((ft.fbid IS NULL AND $1 IS NULL) OR ft.fbid = $1) AND fp.time <= $3
68 GROUP BY ft.ftid, ft.subject
69 HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
72 $threads->bind_param('$1',$board->{id},{TYPE => DBI::SQL_INTEGER }) or warn $DBH->errstr;
73 $threads->bind_param('$2',$ND::UID,{TYPE => DBI::SQL_INTEGER }) or warn $DBH->errstr;
74 $threads->bind_param('$3',param('markAsRead')) or warn $DBH->errstr;
75 $threads->execute or warn $DBH->errstr;
76 while (my $thread = $threads->fetchrow_hashref){
77 markThreadAsRead $thread->{id};
83 my $findThread = $DBH->prepare(q{SELECT ft.ftid AS id,ft.subject, bool_or(fa.post) AS post
84 , bool_or(fa.moderate) AS moderate,ft.fbid,fb.board,fb.fcid,ft.sticky
85 FROM forum_boards fb NATURAL JOIN forum_access fa NATURAL JOIN forum_threads ft
86 WHERE ft.ftid = $1 AND
87 gid IN (SELECT groups($2))
88 GROUP BY ft.ftid,ft.subject,ft.fbid,fb.board,fb.fcid,ft.sticky
90 $thread = $DBH->selectrow_hashref($findThread,undef,param('t'),$ND::UID) or warn $DBH->errstr;
93 if (defined param('cmd')){
94 if(param('cmd') eq 'Submit' or param('cmd') eq 'Preview'){
96 if ($board && $board->{post}){
97 $thread = addForumThread $DBH,$board,$ND::UID,param('subject');
99 if (param('cmd') eq 'Submit' and $thread && $thread->{post}){
100 addForumPost($DBH,$thread,$ND::UID,param('message'));
101 $self->{RETURN} = 'REDIRECT';
102 $self->{REDIR_LOCATION} = "/forum?t=$thread->{id}#NewPosts";
104 $DBH->commit or warn $DBH->errstr;
105 return if $self->{RETURN};
107 if(param('cmd') eq 'Move' && $board->{moderate}){
109 my $moveThread = $DBH->prepare(q{UPDATE forum_threads SET fbid = $1 WHERE ftid = $2 AND fbid = $3});
110 for my $param (param()){
111 if ($param =~ /t:(\d+)/){
112 $moveThread->execute(param('board'),$1,$board->{id}) or warn $DBH->errstr;
113 if ($moveThread->rows > 0){
114 log_message $ND::UID, qq{Moved thread: $1 to board: }.param('board');
118 $DBH->commit or warn $DBH->errstr;
120 if($thread && param('cmd') eq 'Sticky' && $thread->{moderate}){
121 if ($DBH->do(q{UPDATE forum_threads SET sticky = TRUE WHERE ftid = ?}, undef,$thread->{id})){
122 $thread->{sticky} = 1;
127 if($thread && param('cmd') eq 'Unsticky' && $thread->{moderate}){
128 if ($DBH->do(q{UPDATE forum_threads SET sticky = FALSE WHERE ftid = ?}, undef,$thread->{id})){
129 $thread->{sticky} = 0;
137 if ($thread){ #Display the thread
138 $BODY->param(Title => $thread->{subject});
139 $BODY->param(FBID => $thread->{fbid});
140 $BODY->param(Board => $thread->{board});
141 $BODY->param(FTID => $thread->{id});
142 $BODY->param(Moderate => $thread->{moderate});
143 $BODY->param(Sticky => $thread->{sticky} ? 'Unsticky' : 'Sticky');
144 $BODY->param(Thread => viewForumThread $thread);
145 my ($category) = $DBH->selectrow_array(q{SELECT category FROM forum_categories WHERE fcid = $1}
146 ,undef,$thread->{fcid}) or warn $DBH->errstr;
147 $BODY->param(Category => $category);
149 }elsif(defined $self->{allUnread}){ #List threads in this board
150 $BODY->param(AllUnread => 1);
152 my $threads = $DBH->prepare(q{SELECT fcid,category,fbid,board,ft.ftid AS id,u.username,ft.subject,
153 count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts,
154 date_trunc('seconds',max(fp.time)::timestamp) as last_post,
155 min(fp.time)::date as posting_date, ft.sticky
156 FROM forum_categories fc
157 JOIN forum_boards fb USING (fcid)
158 JOIN forum_threads ft USING (fbid)
159 JOIN forum_posts fp USING (ftid)
160 JOIN users u ON u.uid = ft.uid
161 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid
163 fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1)))
164 GROUP BY fcid,category,fbid,board,ft.ftid, ft.subject,ft.sticky,u.username
165 HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
166 ORDER BY fcid,fbid,sticky DESC,last_post DESC});
168 my ($time) = $DBH->selectrow_array('SELECT now()::timestamp',undef);
169 $BODY->param(Date => $time);
170 $threads->execute($ND::UID) or warn $DBH->errstr;
172 my $category = {fcid => 0};
173 my $board = {id => 0};
174 while (my $thread = $threads->fetchrow_hashref){
175 if ($category->{fcid} != $thread->{fcid}){
176 delete $category->{fcid};
177 $category = {fcid => $thread->{fcid}, category => $thread->{category}};
178 push @categories,$category;
180 if ($board->{id} != $thread->{fbid}){
181 $board = {id => $thread->{fbid}, board => $thread->{board}};
182 push @{$category->{Boards}},$board;
184 delete $thread->{fcid};
185 delete $thread->{fbid};
186 delete $thread->{category};
187 delete $thread->{board};
188 push @{$board->{Threads}},$thread;
190 delete $category->{fcid};
191 $BODY->param(Categories => \@categories);
193 }elsif($board){ #List threads in this board
194 $BODY->param(ViewBoard => 1);
195 $BODY->param(Title => $board->{board});
196 $BODY->param(Post => $board->{post});
197 $BODY->param(Moderate => $board->{moderate});
198 $BODY->param(Id => $board->{id});
199 $BODY->param(FBID => $board->{id});
200 $BODY->param(Board => $board->{board});
201 my ($time) = $DBH->selectrow_array('SELECT now()::timestamp',undef);
202 $BODY->param(Date => $time);
204 my $threads = $DBH->prepare(q{SELECT ft.ftid AS id,u.username,ft.subject
205 ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts
206 ,date_trunc('seconds',max(fp.time)::timestamp) as last_post
207 ,min(fp.time)::date as posting_date, ft.sticky
208 FROM forum_threads ft
209 JOIN forum_posts fp USING (ftid)
210 JOIN users u ON u.uid = ft.uid
211 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
213 GROUP BY ft.ftid, ft.subject,ft.sticky,u.username
214 ORDER BY sticky DESC,last_post DESC
215 }) or warn $DBH->errstr;
216 $threads->execute($board->{id},$ND::UID) or warn $DBH->errstr;
218 while (my $thread = $threads->fetchrow_hashref){
219 push @threads,$thread;
221 $BODY->param(Threads => \@threads);
223 if ($board->{moderate}){
224 my $categories = $DBH->prepare(q{SELECT fcid AS id,category FROM forum_categories ORDER BY fcid});
225 my $boards = $DBH->prepare(q{SELECT fb.fbid AS id,fb.board, bool_or(fa.post) AS post
226 FROM forum_boards fb NATURAL JOIN forum_access fa
227 WHERE fb.fcid = $1 AND
228 gid IN (SELECT groups($2))
229 GROUP BY fb.fbid,fb.board
232 $categories->execute or warn $DBH->errstr;
234 while (my $category = $categories->fetchrow_hashref){
235 $boards->execute($category->{id},$ND::UID) or warn $DBH->errstr;
238 while (my $b = $boards->fetchrow_hashref){
239 next if (not $b->{post} or $b->{id} == $board->{id});
243 $category->{Boards} = \@boards;
244 delete $category->{id};
245 push @categories,$category if @boards;
247 $BODY->param(Categories => \@categories);
249 my ($category) = $DBH->selectrow_array(q{SELECT category FROM forum_categories WHERE fcid = $1}
250 ,undef,$board->{fcid}) or warn $DBH->errstr;
251 $BODY->param(Category => $category);
253 $BODY->param(Overview => 1);
254 my $boards = $DBH->prepare(q{SELECT fcid,category,fb.fbid AS id,fb.board
255 ,count(NULLIF(COALESCE(fp.fpid::BOOLEAN,FALSE)
256 AND COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread
257 ,date_trunc('seconds',max(fp.time)::timestamp) as last_post
258 FROM forum_categories
259 JOIN forum_boards fb USING (fcid)
260 LEFT OUTER JOIN forum_threads ft USING (fbid)
261 LEFT OUTER JOIN forum_posts fp USING (ftid)
262 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv USING (ftid)
263 WHERE EXISTS (SELECT fbid FROM forum_access WHERE fbid = fb.fbid AND gid IN (SELECT groups($1)))
264 GROUP BY fcid,category,fb.fbid, fb.board
265 ORDER BY fcid,fb.fbid
267 $boards->execute($ND::UID) or warn $DBH->errstr;
269 my $category = {fcid => 0};
270 while (my $board = $boards->fetchrow_hashref){
271 if ($category->{fcid} != $board->{fcid}){
272 delete $category->{fcid};
273 $category = {fcid => $board->{fcid}, category => $board->{category}};
274 push @categories,$category;
276 delete $board->{fcid};
277 delete $board->{category};
278 push @{$category->{Boards}},$board;
280 delete $category->{fcid};
281 $BODY->param(Categories => \@categories);