1 package NDWeb::Controller::Forum;
5 use parent 'Catalyst::Controller';
11 NDWeb::Controller::Forum - Catalyst Controller
25 sub index :Path :Args(0) {
26 my ( $self, $c ) = @_;
29 my $boards = $dbh->prepare(q{SELECT fcid,category,fb.fbid,fb.board
30 ,count(NULLIF(COALESCE(fp.fpid::BOOLEAN,FALSE)
31 AND COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread
32 ,date_trunc('seconds',max(fp.time)::timestamp) as last_post
34 JOIN forum_boards fb USING (fcid)
35 LEFT OUTER JOIN forum_threads ft USING (fbid)
36 LEFT OUTER JOIN forum_posts fp USING (ftid)
37 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv USING (ftid)
38 WHERE EXISTS (SELECT fbid FROM forum_access WHERE fbid = fb.fbid AND gid IN (SELECT groups($1)))
39 GROUP BY fcid,category,fb.fbid, fb.board
42 $boards->execute($c->stash->{UID});
45 my $category = {fcid => 0};
46 while (my $board = $boards->fetchrow_hashref){
47 if ($category->{fcid} != $board->{fcid}){
48 $category = {fcid => $board->{fcid}, category => $board->{category}};
49 push @categories,$category;
51 push @{$category->{boards}},$board;
53 $c->stash(categories => \@categories);
56 sub allUnread : Local {
57 my ( $self, $c ) = @_;
60 my $threads = $dbh->prepare(q{SELECT fcid,category,fbid,board,ft.ftid,u.username,ft.subject,
61 count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts,
62 date_trunc('seconds',max(fp.time)::timestamp) as last_post,
63 min(fp.time)::date as posting_date, ft.sticky
64 FROM forum_categories fc
65 JOIN forum_boards fb USING (fcid)
66 JOIN forum_threads ft USING (fbid)
67 JOIN forum_posts fp USING (ftid)
68 JOIN users u ON u.uid = ft.uid
69 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid
71 fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1)))
72 GROUP BY fcid,category,fbid,board,ft.ftid, ft.subject,ft.sticky,u.username
73 HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
74 ORDER BY fcid,fbid,sticky DESC,last_post DESC
77 $threads->execute($c->stash->{UID});
79 my $category = {fcid => 0};
80 my $board = {fbid => 0};
81 while (my $thread = $threads->fetchrow_hashref){
82 if ($category->{fcid} != $thread->{fcid}){
83 $category = {fcid => $thread->{fcid}, category => $thread->{category}};
84 push @categories,$category;
86 if ($board->{fbid} != $thread->{fbid}){
87 $board = {fbid => $thread->{fbid}, board => $thread->{board}};
88 push @{$category->{boards}},$board;
90 delete $thread->{fcid};
91 delete $thread->{fbid};
92 delete $thread->{category};
93 delete $thread->{board};
94 push @{$board->{threads}},$thread;
96 $c->stash(categories => \@categories);
97 $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
102 my ( $self, $c ) = @_;
107 if ($c->req->param('search')){
108 push @queries,'('.$c->req->param('search').')';
110 my %cat = (body => 'D', topic => 'A', author => 'B');
111 for ('body','topic','author'){
112 if ($c->req->param($_)){
113 my @words = split /\W+/,$c->req->param($_);
114 my $op = $c->req->param('all'.$_) ? '&' : '|';
116 my $query = join " $op ", map {"$_:$cat"} @words;
117 push @queries,"($query)";
120 my $search = join ' & ', @queries;
123 my $posts = $dbh->prepare(q{SELECT fp.ftid,u.username,ft.subject
124 ,ts_headline(fp.message,to_tsquery($2)) AS headline
125 ,ts_rank_cd(fp.textsearch, to_tsquery($2),32) AS rank
127 JOIN forum_threads ft USING (fbid)
128 JOIN forum_posts fp USING (ftid)
129 JOIN users u ON fp.uid = u.uid
130 WHERE fb.fbid IN (SELECT fbid FROM forum_access
131 WHERE gid IN (SELECT groups($1)))
132 AND fp.textsearch @@@ to_tsquery($2)
136 $posts->execute($c->stash->{UID},$search);
138 while (my $post = $posts->fetchrow_hashref){
141 $c->stash(searchresults => \@posts);
144 $c->stash( searcherror => $dbh->errstr);
152 my ( $self, $c, $board ) = @_;
155 $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
157 $c->forward('findBoard');
158 $board = $c->stash->{board};
160 my $threads = $dbh->prepare(q{SELECT ft.ftid,u.username,ft.subject
161 ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts
162 ,date_trunc('seconds',max(fp.time)::timestamp) as last_post
163 ,min(fp.time)::date as posting_date, ft.sticky
164 FROM forum_threads ft
165 JOIN forum_posts fp USING (ftid)
166 JOIN users u ON u.uid = ft.uid
167 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
169 GROUP BY ft.ftid, ft.subject,ft.sticky,u.username
170 ORDER BY sticky DESC,last_post DESC
172 $threads->execute($board->{fbid},$c->stash->{UID});
174 while (my $thread = $threads->fetchrow_hashref){
175 push @threads,$thread;
177 $c->stash(threads => \@threads);
179 if ($board->{moderate}){
180 my $categories = $dbh->prepare(q{SELECT fcid,category FROM forum_categories ORDER BY fcid});
181 my $boards = $dbh->prepare(q{SELECT fb.fbid,fb.board, bool_or(fa.post) AS post
182 FROM forum_boards fb NATURAL JOIN forum_access fa
183 WHERE fb.fcid = $1 AND
184 gid IN (SELECT groups($2))
185 GROUP BY fb.fbid,fb.board
188 $categories->execute;
190 while (my $category = $categories->fetchrow_hashref){
191 $boards->execute($category->{fcid},$c->stash->{UID});
194 while (my $b = $boards->fetchrow_hashref){
195 next if (not $b->{post} or $b->{fbid} == $board->{fbid});
198 $category->{boards} = \@boards;
199 push @categories,$category if @boards;
201 $c->stash(categories => \@categories);
207 my ( $self, $c, $thread ) = @_;
210 $c->forward('findThread');
211 $c->forward('findPosts') if $c->stash->{thread};
212 $c->forward('markThreadAsRead') if $c->user_exists;
215 sub findPosts :Private {
216 my ( $self, $c, $thread ) = @_;
219 my $posts = $dbh->prepare(q{
220 SELECT u.username,date_trunc('seconds',fp.time::timestamp) AS time
221 ,fp.message,COALESCE(fp.time > ftv.time,TRUE) AS unread
222 FROM forum_threads ft
223 JOIN forum_posts fp USING (ftid)
224 JOIN users u ON u.uid = fp.uid
226 (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
230 $posts->execute($thread,$c->stash->{UID});
233 while (my $post = $posts->fetchrow_hashref){
234 $post->{message} = parseMarkup($post->{message});
238 $c->stash(posts => \@posts);
242 sub markBoardAsRead : Local {
243 my ( $self, $c, $board, $time ) = @_;
246 $c->forward('findBoard');
247 $board = $c->stash->{board};
249 my $threads = $dbh->prepare(q{SELECT ft.ftid,ft.subject
250 ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread
251 ,count(fp.fpid) AS posts, max(fp.time)::timestamp as last_post
252 FROM forum_threads ft
253 JOIN forum_posts fp USING (ftid)
254 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
255 WHERE ft.fbid = $1 AND fp.time <= $3
256 GROUP BY ft.ftid, ft.subject
257 HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
259 $threads->execute($board->{fbid},$c->user->id,$time);
261 while (my $thread = $threads->fetchrow_hashref){
262 $c->forward('markThreadAsRead',[$thread->{ftid}]);
265 $c->res->redirect($c->req->referer);
268 sub markThreadAsRead : Private {
269 my ( $self, $c, $thread ) = @_;
272 my $rows = $dbh->do(q{UPDATE forum_thread_visits SET time = now()
273 WHERE uid = $1 AND ftid = $2
274 },undef,$c->user->id,$thread);
276 $dbh->do(q{INSERT INTO forum_thread_visits (uid,ftid)
278 ,undef,$c->user->id,$thread);
282 sub moveThreads : Local {
283 my ( $self, $c, $board ) = @_;
286 $c->forward('findBoard',[$c->req->param('board')]);
287 my $toboard = $c->stash->{board};
288 unless ($toboard->{moderate}){
289 $c->acl_access_denied('test',$c->action,'No moderator access for target board.')
292 $c->forward('findBoard');
293 $board = $c->stash->{board};
294 unless ($board->{moderate}){
295 $c->acl_access_denied('test',$c->action,'No moderator access for source board.')
298 my $log = "Moved these threads:\n\n";
300 my $moveThread = $dbh->prepare(q{UPDATE forum_threads SET fbid = $1 WHERE ftid = $2 AND fbid = $3});
301 for my $param ($c->req->param){
302 if ($param =~ /t:(\d+)/){
303 $moveThread->execute($toboard->{fbid},$1,$board->{fbid});
304 if ($moveThread->rows > 0){
310 $log .= "\nFrom board: $board->{board} ($board->{fbid})";
311 $log .= "\nTo board: $toboard->{board} ($toboard->{fbid})";
312 $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
313 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
314 }, undef, $c->user->id, $log);
317 $c->res->redirect($c->uri_for('board',$board->{fbid}));
320 sub newThread : Local {
321 my ( $self, $c, $board ) = @_;
323 $c->forward('findBoard');
324 $board = $c->stash->{board};
326 unless ($c->stash->{board}->{post}){
327 $c->acl_access_denied('test',$c->action,'No post access to board.')
330 $c->forward('insertThread');
331 $c->forward('addPost',[$c->stash->{thread}]);
334 sub insertThread : Private {
335 my ( $self, $c, $board ) = @_;
338 my $insert = $dbh->prepare(q{INSERT INTO forum_threads (ftid,fbid,subject,uid)
339 VALUES(DEFAULT,$1,$2,$3) RETURNING (ftid);
341 $insert->execute($board,html_escape($c->req->param('subject')),$c->stash->{UID});
342 $c->stash(thread => $insert->fetchrow);
346 sub addPost : Local {
347 my ( $self, $c, $thread ) = @_;
350 if ($c->req->param('cmd') eq 'Submit'){
351 $c->forward('findThread');
352 unless ($c->stash->{thread}->{post}){
353 $c->acl_access_denied('test',$c->action,'No post access to board.')
355 $c->forward('insertPost');
356 $c->res->redirect($c->uri_for('thread',$thread));
357 }elsif ($c->req->param('cmd') eq 'Preview'){
358 $c->forward('thread');
359 $c->forward('previewPost');
360 $c->stash(template => 'forum/thread.tt2');
364 sub setSticky : Local {
365 my ( $self, $c, $thread, $sticky ) = @_;
368 $c->forward('findThread');
369 unless ($c->stash->{thread}->{moderate}){
370 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
373 $dbh->do(q{UPDATE forum_threads SET sticky = $2 WHERE ftid = $1}
374 , undef,$thread, $sticky);
375 $c->res->redirect($c->uri_for('thread',$thread));
378 sub findThread : Private {
379 my ( $self, $c, $thread ) = @_;
381 my $findThread = $dbh->prepare(q{SELECT ft.ftid,ft.subject, bool_or(fa.post) AS post
382 , bool_or(fa.moderate) AS moderate,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
384 NATURAL JOIN forum_access fa
385 NATURAL JOIN forum_threads ft
386 NATURAL JOIN forum_categories fc
387 WHERE ft.ftid = $1 AND gid IN (SELECT groups($2))
388 GROUP BY ft.ftid,ft.subject,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
390 $thread = $dbh->selectrow_hashref($findThread,undef,$thread,$c->stash->{UID});
391 $c->stash(thread => $thread);
394 sub findBoard : Private {
395 my ( $self, $c, $board ) = @_;
398 my $boards = $dbh->prepare(q{SELECT fb.fbid,fb.board, bool_or(fa.post) AS post, bool_or(fa.moderate) AS moderate,fb.fcid, fc.category
400 NATURAL JOIN forum_access fa
401 NATURAL JOIN forum_categories fc
402 WHERE fb.fbid = $1 AND
403 gid IN (SELECT groups($2))
404 GROUP BY fb.fbid,fb.board,fb.fcid,fc.category
406 $board = $dbh->selectrow_hashref($boards,undef,$board,$c->stash->{UID});
408 $c->stash(board => $board);
411 sub previewPost : Private {
412 my ( $self, $c) = @_;
413 push @{$c->stash->{posts}}, {
415 username => 'PREVIEW',
416 message => parseMarkup(html_escape $c->req->param('message')),
418 $c->stash(previewMessage => html_escape $c->req->param('message'));
421 sub insertPost : Private {
422 my ( $self, $c, $thread ) = @_;
425 my $insert = $dbh->prepare(q{INSERT INTO forum_posts (ftid,message,uid)
427 $insert->execute($thread,html_escape($c->req->param('message')),$c->stash->{UID});
432 Michael Andreen (harv@ruin.nu)