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{
30 SELECT fcid,category,fb.fbid,fb.board
31 ,SUM((SELECT count(*) FROM forum_posts WHERE ftid = ft.ftid
32 AND COALESCE(time > ftv.time,TRUE))) AS unread
33 ,date_trunc('seconds',max(ft.mtime)::timestamp ) AS last_post
34 FROM forum_categories fc
35 JOIN forum_boards fb USING (fcid)
36 LEFT OUTER JOIN forum_threads ft USING (fbid)
37 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1)
39 WHERE fbid IN (SELECT fbid FROM forum_access
40 WHERE gid IN (SELECT groups($1)))
41 OR ftid IN (SELECT ftid FROM forum_priv_access
43 GROUP BY fcid,category,fb.fbid, fb.board
46 $boards->execute($c->stash->{UID});
49 my $category = {fcid => 0};
50 while (my $board = $boards->fetchrow_hashref){
51 if ($category->{fcid} != $board->{fcid}){
52 $category = {fcid => $board->{fcid}, category => $board->{category}};
53 push @categories,$category;
55 push @{$category->{boards}},$board;
57 $c->stash(categories => \@categories);
60 sub allUnread : Local {
61 my ( $self, $c ) = @_;
64 my $threads = $dbh->prepare(q{
65 SELECT fcid,category,fbid,board,ft.ftid,u.username,ft.subject,ft.sticky
66 ,(SELECT count(*) FROM forum_posts WHERE ftid = ft.ftid
67 AND COALESCE(time > ftv.time,TRUE)) AS unread
68 ,ft.posts,date_trunc('seconds',ft.mtime::timestamp) as last_post
69 ,ft.ctime::DATE as posting_date
70 FROM forum_categories fc
71 JOIN forum_boards fb USING (fcid)
72 JOIN forum_threads ft USING (fbid)
73 JOIN users u ON u.uid = ft.uid
74 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1)
75 ftv ON ftv.ftid = ft.ftid
76 WHERE COALESCE(ft.mtime > ftv.time,TRUE)
77 AND ft.ftid IN (SELECT ftid FROM forum_posts WHERE ftid = ft.ftid)
79 fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1))))
80 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1))
81 ORDER BY fcid,fbid,sticky DESC,last_post DESC
84 $threads->execute($c->stash->{UID});
86 my $category = {fcid => 0};
87 my $board = {fbid => 0};
88 while (my $thread = $threads->fetchrow_hashref){
89 if ($category->{fcid} != $thread->{fcid}){
90 $category = {fcid => $thread->{fcid}, category => $thread->{category}};
91 push @categories,$category;
93 if ($board->{fbid} != $thread->{fbid}){
94 $board = {fbid => $thread->{fbid}, board => $thread->{board}};
95 push @{$category->{boards}},$board;
97 delete $thread->{fcid};
98 delete $thread->{fbid};
99 delete $thread->{category};
100 delete $thread->{board};
101 push @{$board->{threads}},$thread;
103 $c->stash(categories => \@categories);
104 $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
109 my ( $self, $c ) = @_;
114 if ($c->req->param('search')){
115 push @queries,'('.$c->req->param('search').')';
117 my %cat = (body => 'D', topic => 'A', author => 'B');
118 for ('body','topic','author'){
119 if ($c->req->param($_)){
120 my @words = split /\W+/,$c->req->param($_);
121 my $op = $c->req->param('all'.$_) ? '&' : '|';
123 my $query = join " $op ", map {"$_:$cat"} @words;
124 push @queries,"($query)";
127 my $search = join ' & ', @queries;
130 my $posts = $dbh->prepare(q{SELECT fp.ftid,u.username,ft.subject
131 ,ts_headline(fp.message,to_tsquery($2)) AS headline
132 ,ts_rank_cd(fp.textsearch, to_tsquery($2),32) AS rank
134 JOIN forum_threads ft USING (fbid)
135 JOIN forum_posts fp USING (ftid)
136 JOIN users u ON fp.uid = u.uid
137 WHERE (fb.fbid IN (SELECT fbid FROM forum_access
138 WHERE gid IN (SELECT groups($1)))
139 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1)
140 ) AND fp.textsearch @@@ to_tsquery($2)
144 $posts->execute($c->stash->{UID},$search);
146 while (my $post = $posts->fetchrow_hashref){
149 $c->stash(searchresults => \@posts);
152 $c->stash( searcherror => $dbh->errstr);
160 my ( $self, $c, $board ) = @_;
163 $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
165 $c->forward('findBoard');
166 $board = $c->stash->{board};
167 if ( !defined $board->{fbid}){
168 $c->detach('/default');
171 my $threads = $dbh->prepare(q{
172 SELECT ft.ftid,u.username,ft.subject,ft.posts, ft.sticky
173 ,(SELECT count(*) FROM forum_posts WHERE ftid = ft.ftid
174 AND COALESCE(time > ftv.time,TRUE)) AS unread
175 ,ft.ctime::DATE as posting_date
176 ,date_trunc('seconds',ft.mtime::timestamp) as last_post
177 FROM forum_threads ft
178 JOIN users u USING(uid)
179 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2)
180 ftv ON ftv.ftid = ft.ftid
181 WHERE ft.posts > 0 AND ft.fbid = $1 AND (
182 ft.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($2)))
183 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2)
185 GROUP BY ft.ftid, ft.subject,ft.sticky,u.username,ft.ctime,ft.mtime,ft.posts,ftv.time
186 ORDER BY sticky DESC,last_post DESC
188 $threads->execute($board->{fbid},$c->stash->{UID});
190 while (my $thread = $threads->fetchrow_hashref){
191 push @threads,$thread;
194 if ( !(defined $board->{post}) && @threads == 0){
195 $c->acl_access_denied('test',$c->action,'No access to board')
197 $c->stash(threads => \@threads);
199 $c->stash(title => "$board->{board} ($board->{category})");
201 $c->forward('listModeratorBoards', [$board->{fbid}]) if $board->{moderate};
206 my ( $self, $c, $thread ) = @_;
209 $c->forward('findThread');
210 $thread = $c->stash->{thread};
212 $c->stash(template => 'default.tt2');
213 $c->res->status(404);
216 my $query = $dbh->prepare(q{SELECT uid,username FROM users u
217 JOIN forum_priv_access fta USING (uid) WHERE fta.ftid = $1});
218 $query->execute($thread->{ftid});
219 $c->stash(access => $query->fetchall_arrayref({}) );
220 $c->stash(title => $thread->{subject}
221 . " ($thread->{category} - $thread->{board})");
222 $c->forward('findPosts');
223 $c->forward('markThreadAsRead') if $c->user_exists;
224 if ($c->stash->{thread}->{moderate}) {
225 $c->forward('findUsers');
226 $c->forward('listModeratorBoards', [$c->stash->{thread}->{fbid}]);
230 sub findPosts :Private {
231 my ( $self, $c, $thread ) = @_;
234 my $posts = $dbh->prepare(q{
235 SELECT fpid,u.uid,u.username,date_trunc('seconds',fp.time::timestamp) AS time
236 ,fp.message,COALESCE(fp.time > ftv.time,TRUE) AS unread
237 FROM forum_threads ft
238 JOIN forum_posts fp USING (ftid)
239 JOIN users u ON u.uid = fp.uid
241 (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
245 $posts->execute($thread,$c->stash->{UID});
248 while (my $post = $posts->fetchrow_hashref){
249 $post->{message} = parseMarkup($post->{message});
253 $c->stash(posts => \@posts);
257 sub markBoardAsRead : Local {
258 my ( $self, $c, $board, $time ) = @_;
261 $c->forward('findBoard');
262 $board = $c->stash->{board};
264 my $threads = $dbh->prepare(q{SELECT ft.ftid,ft.subject
265 ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread
266 ,count(fp.fpid) AS posts, max(fp.time)::timestamp as last_post
267 FROM forum_threads ft
268 JOIN forum_posts fp USING (ftid)
269 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
270 WHERE ft.fbid = $1 AND fp.time <= $3
271 GROUP BY ft.ftid, ft.subject
272 HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
274 $threads->execute($board->{fbid},$c->user->id,$time);
276 while (my $thread = $threads->fetchrow_hashref){
277 $c->forward('markThreadAsRead',[$thread->{ftid}]);
280 $c->forward('/redirect');
283 sub markThreadAsRead : Private {
284 my ( $self, $c, $thread ) = @_;
287 my $rows = $dbh->do(q{UPDATE forum_thread_visits SET time = now()
288 WHERE uid = $1 AND ftid = $2
289 },undef,$c->user->id,$thread);
291 $dbh->do(q{INSERT INTO forum_thread_visits (uid,ftid)
293 ,undef,$c->user->id,$thread);
297 sub markThreadAsUnread : Local {
298 my ( $self, $c, $thread ) = @_;
301 my ($fbid) = $dbh->selectrow_array(q{
302 SELECT fbid FROM forum_threads WHERE ftid = $1
306 DELETE FROM forum_thread_visits WHERE uid = $1 AND ftid = $2
307 }, undef, $c->user->id, $thread);
308 $c->res->redirect($c->uri_for('board',$fbid));
311 sub markPostAsUnread : Local {
312 my ( $self, $c, $post ) = @_;
315 my ($fbid) = $dbh->selectrow_array(q{
316 SELECT fbid FROM forum_threads JOIN forum_posts USING (ftid) WHERE fpid = $1
320 UPDATE forum_thread_visits ftv SET time = (fp.time - interval '1 second')
322 WHERE ftv.uid = $1 AND fp.fpid = $2 AND fp.ftid = ftv.ftid
323 }, undef, $c->user->id, $post);
324 $c->res->redirect($c->uri_for('board',$fbid));
327 sub moveThreads : Local {
328 my ( $self, $c, $board ) = @_;
331 $c->forward('findBoard',[$c->req->param('board')]);
332 my $toboard = $c->stash->{board};
333 unless ($toboard->{moderate}){
334 $c->acl_access_denied('test',$c->action,'No moderator access for target board.')
337 $c->forward('findBoard');
338 $board = $c->stash->{board};
339 unless ($board->{moderate}){
340 $c->acl_access_denied('test',$c->action,'No moderator access for source board.')
343 my $log = "Moved these threads:\n\n";
345 my $moveThread = $dbh->prepare(q{UPDATE forum_threads SET fbid = $1 WHERE ftid = $2 AND fbid = $3});
346 for my $param ($c->req->param){
347 if ($param =~ /t:(\d+)/){
348 $moveThread->execute($toboard->{fbid},$1,$board->{fbid});
349 if ($moveThread->rows > 0){
355 $log .= "\nFrom board: $board->{board} ($board->{fbid})";
356 $log .= "\nTo board: $toboard->{board} ($toboard->{fbid})";
357 $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
358 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
359 }, undef, $c->user->id, $log);
362 $c->res->redirect($c->uri_for('board',$board->{fbid}));
365 sub newThread : Local {
366 my ( $self, $c, $board ) = @_;
368 $c->forward('findBoard');
369 $board = $c->stash->{board};
371 unless ($c->stash->{board}->{post}){
372 $c->acl_access_denied('test',$c->action,'No post access to board.')
375 $c->forward('insertThread');
376 $c->forward('addPost',[$c->stash->{thread}]);
379 sub insertThread : Private {
380 my ( $self, $c, $board ) = @_;
383 my $insert = $dbh->prepare(q{INSERT INTO forum_threads (ftid,fbid,subject,uid)
384 VALUES(DEFAULT,$1,$2,$3) RETURNING (ftid);
386 $insert->execute($board,html_escape($c->req->param('subject')),$c->stash->{UID});
387 $c->stash(thread => $insert->fetchrow);
391 sub addPost : Local {
392 my ( $self, $c, $thread ) = @_;
395 if ($c->req->param('cmd') eq 'Submit'){
396 $c->forward('findThread');
397 unless ($c->stash->{thread}->{post}){
398 $c->acl_access_denied('test',$c->action,'No post access to board.')
400 $c->forward('insertPost');
401 $c->res->redirect($c->uri_for('thread',$thread));
402 }elsif ($c->req->param('cmd') eq 'Preview'){
403 $c->forward('thread');
404 $c->forward('previewPost');
405 $c->stash(template => 'forum/thread.tt2');
409 sub setSticky : Local {
410 my ( $self, $c, $thread, $sticky ) = @_;
413 $c->forward('findThread');
414 unless ($c->stash->{thread}->{moderate}){
415 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
418 $dbh->do(q{UPDATE forum_threads SET sticky = $2 WHERE ftid = $1}
419 , undef,$thread, $sticky);
420 $c->res->redirect($c->uri_for('thread',$thread));
423 sub postthreadaccess : Local {
424 my ( $self, $c, $thread) = @_;
427 $c->forward('findThread');
429 unless ($c->stash->{thread}->{moderate}){
430 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
432 if ($c->req->param('access')){
433 $c->req->parameters->{access} = [$c->req->parameters->{access}]
434 unless ref $c->req->parameters->{access} eq 'ARRAY';
435 my $query = $dbh->prepare(q{DELETE From forum_priv_access
436 WHERE ftid = $1 AND uid = ANY ($2)});
437 $query->execute($thread,$c->req->parameters->{access});
438 $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
439 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
440 }, undef, $c->user->id
441 ,"Removed access on thread $thread for : @{$c->req->parameters->{access}}");
443 if ($c->req->param('uid')){
444 $c->forward('addaccess');
447 $c->res->redirect($c->uri_for('thread',$thread));
450 sub removeownthreadaccess : Local {
451 my ( $self, $c, $thread) = @_;
453 $dbh->do(q{DELETE FROM forum_priv_access WHERE uid = $1 AND ftid = $2}
454 ,undef,$c->user->id,$thread);
455 $c->res->redirect($c->uri_for('allUnread'));
458 sub privmsg : Local {
459 my ( $self, $c, $uid ) = @_;
462 $c->stash(uid => $uid);
464 $c->forward('findUsers');
467 sub postprivmsg : Local {
468 my ( $self, $c ) = @_;
472 $c->forward('insertThread',[-1999]);
474 $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
475 unless ref $c->req->parameters->{uid} eq 'ARRAY';
476 push @{$c->req->parameters->{uid}}, $c->user->id;
477 $c->forward('addaccess',[$c->stash->{thread}]);
479 $c->forward('addPost',[$c->stash->{thread}]);
483 sub addaccess : Private {
484 my ( $self, $c, $thread) = @_;
487 $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
488 unless ref $c->req->parameters->{uid} eq 'ARRAY';
489 my $query = $dbh->prepare(q{INSERT INTO forum_priv_access (ftid,uid)
490 (SELECT $1,uid FROM users u WHERE uid = ANY ($2) AND NOT uid
491 IN (SELECT uid FROM forum_priv_access WHERE ftid = $1))});
492 $query->execute($thread,$c->req->parameters->{uid});
493 $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
494 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
495 }, undef, $c->user->id
496 ,"Gave access on thread $thread to : @{$c->req->parameters->{uid}}");
499 sub findUsers : Private {
500 my ( $self, $c ) = @_;
503 my $query = $dbh->prepare(q{SELECT uid,username FROM users
504 WHERE uid > 0 AND uid IN (SELECT uid FROM groupmembers)
508 $c->stash(users => $query->fetchall_arrayref({}) );
511 sub findThread : Private {
512 my ( $self, $c, $thread ) = @_;
514 my $findThread = $dbh->prepare(q{SELECT ft.ftid,ft.subject
515 ,COALESCE(bool_or(fa.post),true) AS post, bool_or(fa.moderate) AS moderate
516 ,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
518 NATURAL JOIN forum_threads ft
519 NATURAL JOIN forum_categories fc
520 LEFT OUTER JOIN (SELECT fa.* FROM forum_access fa
521 JOIN (SELECT groups($2) AS gid) g USING (gid)
523 WHERE ft.ftid = $1 AND (fa.post IS NOT NULL
524 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2))
525 GROUP BY ft.ftid,ft.subject,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
527 $thread = $dbh->selectrow_hashref($findThread,undef,$thread,$c->stash->{UID});
528 $c->stash(thread => $thread);
531 sub findBoard : Private {
532 my ( $self, $c, $board ) = @_;
535 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
537 NATURAL JOIN forum_categories fc
538 LEFT OUTER JOIN (SELECT * FROM forum_access
539 WHERE fbid = $1 AND gid IN (SELECT groups($2))
542 GROUP BY fb.fbid,fb.board,fb.fcid,fc.category
544 $board = $dbh->selectrow_hashref($boards,undef,$board,$c->stash->{UID});
546 $c->stash(board => $board);
549 sub previewPost : Private {
550 my ( $self, $c) = @_;
551 push @{$c->stash->{posts}}, {
553 username => 'PREVIEW',
554 message => parseMarkup(html_escape $c->req->param('message')),
556 $c->stash(previewMessage => html_escape $c->req->param('message'));
559 sub insertPost : Private {
560 my ( $self, $c, $thread ) = @_;
563 my $insert = $dbh->prepare(q{INSERT INTO forum_posts (ftid,message,uid)
565 $insert->execute($thread,html_escape($c->req->param('message')),$c->stash->{UID});
568 sub listModeratorBoards : Private {
569 my ( $self, $c, $fbid ) = @_;
572 my $categories = $dbh->prepare(q{SELECT fcid,category FROM forum_categories ORDER BY fcid});
573 my $boards = $dbh->prepare(q{SELECT fb.fbid,fb.board, bool_or(fa.post) AS post
574 FROM forum_boards fb NATURAL JOIN forum_access fa
576 AND gid IN (SELECT groups($2))
578 GROUP BY fb.fbid,fb.board
581 $categories->execute;
583 while (my $category = $categories->fetchrow_hashref){
584 $boards->execute($category->{fcid},$c->stash->{UID});
587 while (my $b = $boards->fetchrow_hashref){
588 next if ($b->{fbid} == $fbid);
591 $category->{boards} = \@boards;
592 push @categories,$category if @boards;
594 $c->stash(categories => \@categories);
599 Michael Andreen (harv@ruin.nu)