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 ft.mtime > NOW() - '50 days'::interval
77 AND COALESCE(ft.mtime > ftv.time,TRUE)
78 AND ft.ftid IN (SELECT ftid FROM forum_posts WHERE ftid = ft.ftid)
80 fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1))))
81 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1))
82 ORDER BY fcid,fbid,sticky DESC,last_post DESC
85 $threads->execute($c->stash->{UID});
87 my $category = {fcid => 0};
88 my $board = {fbid => 0};
89 while (my $thread = $threads->fetchrow_hashref){
90 if ($category->{fcid} != $thread->{fcid}){
91 $category = {fcid => $thread->{fcid}, category => $thread->{category}};
92 push @categories,$category;
94 if ($board->{fbid} != $thread->{fbid}){
95 $board = {fbid => $thread->{fbid}, board => $thread->{board}};
96 push @{$category->{boards}},$board;
98 delete $thread->{fcid};
99 delete $thread->{fbid};
100 delete $thread->{category};
101 delete $thread->{board};
102 push @{$board->{threads}},$thread;
104 $c->stash(categories => \@categories);
105 $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
110 my ( $self, $c ) = @_;
115 if ($c->req->param('search')){
116 push @queries,'('.$c->req->param('search').')';
118 my %cat = (body => 'D', topic => 'A', author => 'B');
119 for ('body','topic','author'){
120 if ($c->req->param($_)){
121 my @words = split /\W+/,$c->req->param($_);
122 my $op = $c->req->param('all'.$_) ? '&' : '|';
124 my $query = join " $op ", map {"$_:$cat"} @words;
125 push @queries,"($query)";
128 my $search = join ' & ', @queries;
131 my $posts = $dbh->prepare(q{SELECT fp.ftid,u.username,ft.subject
132 ,ts_headline(fp.message,to_tsquery($2)) AS headline
133 ,ts_rank_cd(fp.textsearch, to_tsquery($2),32) AS rank
135 JOIN forum_threads ft USING (fbid)
136 JOIN forum_posts fp USING (ftid)
137 JOIN users u ON fp.uid = u.uid
138 WHERE (fb.fbid IN (SELECT fbid FROM forum_access
139 WHERE gid IN (SELECT groups($1)))
140 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1)
141 ) AND fp.textsearch @@@ to_tsquery($2)
145 $posts->execute($c->stash->{UID},$search);
147 while (my $post = $posts->fetchrow_hashref){
150 $c->stash(searchresults => \@posts);
153 $c->stash( searcherror => $dbh->errstr);
161 my ( $self, $c, $board ) = @_;
164 $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
166 $c->forward('findBoard');
167 $board = $c->stash->{board};
168 if ( !defined $board->{fbid}){
169 $c->detach('/default');
172 my $threads = $dbh->prepare(q{
173 SELECT ft.ftid,u.username,ft.subject,ft.posts, ft.sticky
174 ,(SELECT count(*) FROM forum_posts WHERE ftid = ft.ftid
175 AND COALESCE(time > ftv.time,TRUE)) AS unread
176 ,ft.ctime::DATE as posting_date
177 ,date_trunc('seconds',ft.mtime::timestamp) as last_post
178 FROM forum_threads ft
179 JOIN users u USING(uid)
180 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2)
181 ftv ON ftv.ftid = ft.ftid
182 WHERE ft.posts > 0 AND ft.fbid = $1 AND (
183 ft.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($2)))
184 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2)
186 GROUP BY ft.ftid, ft.subject,ft.sticky,u.username,ft.ctime,ft.mtime,ft.posts,ftv.time
187 ORDER BY sticky DESC,last_post DESC
189 $threads->execute($board->{fbid},$c->stash->{UID});
191 while (my $thread = $threads->fetchrow_hashref){
192 push @threads,$thread;
195 if ( !(defined $board->{post}) && @threads == 0){
196 $c->acl_access_denied('test',$c->action,'No access to board')
198 $c->stash(threads => \@threads);
200 $c->stash(title => "$board->{board} ($board->{category})");
202 $c->forward('listModeratorBoards', [$board->{fbid}]) if $board->{moderate};
207 my ( $self, $c, $thread ) = @_;
210 $c->forward('findThread');
211 $thread = $c->stash->{thread};
213 $c->stash(template => 'default.tt2');
214 $c->res->status(404);
217 my $query = $dbh->prepare(q{SELECT uid,username FROM users u
218 JOIN forum_priv_access fta USING (uid) WHERE fta.ftid = $1});
219 $query->execute($thread->{ftid});
220 $c->stash(access => $query->fetchall_arrayref({}) );
221 $c->stash(title => $thread->{subject}
222 . " ($thread->{category} - $thread->{board})");
223 $c->forward('findPosts');
224 $c->forward('markThreadAsRead') if $c->user_exists;
225 if ($c->stash->{thread}->{moderate}) {
226 $c->forward('findUsers');
227 $c->forward('listModeratorBoards', [$c->stash->{thread}->{fbid}]);
231 sub findPosts :Private {
232 my ( $self, $c, $thread ) = @_;
235 my $posts = $dbh->prepare(q{
236 SELECT fpid,u.uid,u.username,date_trunc('seconds',fp.time::timestamp) AS time
237 ,fp.message,COALESCE(fp.time > ftv.time,TRUE) AS unread
238 FROM forum_threads ft
239 JOIN forum_posts fp USING (ftid)
240 JOIN users u ON u.uid = fp.uid
242 (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
246 $posts->execute($thread,$c->stash->{UID});
249 while (my $post = $posts->fetchrow_hashref){
250 $post->{message} = parseMarkup($post->{message});
254 $c->stash(posts => \@posts);
258 sub markBoardAsRead : Local {
259 my ( $self, $c, $board, $time ) = @_;
262 $c->forward('findBoard');
263 $board = $c->stash->{board};
265 my $threads = $dbh->prepare(q{SELECT ft.ftid,ft.subject
266 ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread
267 ,count(fp.fpid) AS posts, max(fp.time)::timestamp as last_post
268 FROM forum_threads ft
269 JOIN forum_posts fp USING (ftid)
270 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
271 WHERE ft.fbid = $1 AND fp.time <= $3
272 GROUP BY ft.ftid, ft.subject
273 HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
275 $threads->execute($board->{fbid},$c->user->id,$time);
277 while (my $thread = $threads->fetchrow_hashref){
278 $c->forward('markThreadAsRead',[$thread->{ftid}]);
281 $c->forward('/redirect');
284 sub markThreadAsRead : Private {
285 my ( $self, $c, $thread ) = @_;
288 my $rows = $dbh->do(q{UPDATE forum_thread_visits SET time = now()
289 WHERE uid = $1 AND ftid = $2
290 },undef,$c->user->id,$thread);
292 $dbh->do(q{INSERT INTO forum_thread_visits (uid,ftid)
294 ,undef,$c->user->id,$thread);
298 sub markThreadAsUnread : Local {
299 my ( $self, $c, $thread ) = @_;
302 my ($fbid) = $dbh->selectrow_array(q{
303 SELECT fbid FROM forum_threads WHERE ftid = $1
307 DELETE FROM forum_thread_visits WHERE uid = $1 AND ftid = $2
308 }, undef, $c->user->id, $thread);
309 $c->res->redirect($c->uri_for('board',$fbid));
312 sub markPostAsUnread : Local {
313 my ( $self, $c, $post ) = @_;
316 my ($fbid) = $dbh->selectrow_array(q{
317 SELECT fbid FROM forum_threads JOIN forum_posts USING (ftid) WHERE fpid = $1
321 UPDATE forum_thread_visits ftv SET time = (fp.time - interval '1 second')
323 WHERE ftv.uid = $1 AND fp.fpid = $2 AND fp.ftid = ftv.ftid
324 }, undef, $c->user->id, $post);
325 $c->res->redirect($c->uri_for('board',$fbid));
328 sub moveThreads : Local {
329 my ( $self, $c, $board ) = @_;
332 my $b = $c->req->param('board');
333 $c->forward('findBoard',[$b]);
334 my $toboard = $c->stash->{board};
335 unless ($toboard->{moderate}){
336 $c->acl_access_denied('test',$c->action,'No moderator access for target board.')
339 $c->forward('findBoard');
340 $board = $c->stash->{board};
341 unless ($board->{moderate}){
342 $c->acl_access_denied('test',$c->action,'No moderator access for source board.')
345 my $log = "Moved these threads:\n\n";
347 my $moveThread = $dbh->prepare(q{UPDATE forum_threads SET fbid = $1 WHERE ftid = $2 AND fbid = $3});
348 for my $param ($c->req->param){
349 if ($param =~ /t:(\d+)/){
350 $moveThread->execute($toboard->{fbid},$1,$board->{fbid});
351 if ($moveThread->rows > 0){
357 $log .= "\nFrom board: $board->{board} ($board->{fbid})";
358 $log .= "\nTo board: $toboard->{board} ($toboard->{fbid})";
359 $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
360 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
361 }, undef, $c->user->id, $log);
364 $c->res->redirect($c->uri_for('board',$board->{fbid}));
367 sub newThread : Local {
368 my ( $self, $c, $board ) = @_;
370 $c->forward('findBoard');
371 $board = $c->stash->{board};
373 unless ($c->stash->{board}->{post}){
374 $c->acl_access_denied('test',$c->action,'No post access to board.')
377 $c->forward('insertThread');
378 $c->forward('addPost',[$c->stash->{thread}]);
381 sub insertThread : Private {
382 my ( $self, $c, $board ) = @_;
385 my $subject = html_escape($c->req->param('subject'));
386 my $insert = $dbh->prepare(q{INSERT INTO forum_threads (ftid,fbid,subject,uid)
387 VALUES(DEFAULT,$1,$2,$3) RETURNING (ftid);
389 $insert->execute($board,$subject,$c->stash->{UID});
390 $c->stash(thread => $insert->fetchrow);
394 sub addPost : Local {
395 my ( $self, $c, $thread ) = @_;
398 if ($c->req->param('cmd') eq 'Submit'){
399 $c->forward('findThread');
400 unless ($c->stash->{thread}->{post}){
401 $c->acl_access_denied('test',$c->action,'No post access to board.')
403 $c->forward('insertPost');
404 $c->res->redirect($c->uri_for('thread',$thread));
405 }elsif ($c->req->param('cmd') eq 'Preview'){
406 $c->forward('thread');
407 $c->forward('previewPost');
408 $c->stash(template => 'forum/thread.tt2');
412 sub setSticky : Local {
413 my ( $self, $c, $thread, $sticky ) = @_;
416 $c->forward('findThread');
417 unless ($c->stash->{thread}->{moderate}){
418 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
421 $dbh->do(q{UPDATE forum_threads SET sticky = $2 WHERE ftid = $1}
422 , undef,$thread, $sticky);
423 $c->res->redirect($c->uri_for('thread',$thread));
426 sub postthreadaccess : Local {
427 my ( $self, $c, $thread) = @_;
430 $c->forward('findThread');
432 unless ($c->stash->{thread}->{moderate}){
433 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
435 if ($c->req->param('access')){
436 $c->req->parameters->{access} = [$c->req->parameters->{access}]
437 unless ref $c->req->parameters->{access} eq 'ARRAY';
438 my $query = $dbh->prepare(q{DELETE From forum_priv_access
439 WHERE ftid = $1 AND uid = ANY ($2)});
440 $query->execute($thread,$c->req->parameters->{access});
441 $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
442 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
443 }, undef, $c->user->id
444 ,"Removed access on thread $thread for : @{$c->req->parameters->{access}}");
446 if ($c->req->param('uid')){
447 $c->forward('addaccess');
450 $c->res->redirect($c->uri_for('thread',$thread));
453 sub removeownthreadaccess : Local {
454 my ( $self, $c, $thread) = @_;
456 $dbh->do(q{DELETE FROM forum_priv_access WHERE uid = $1 AND ftid = $2}
457 ,undef,$c->user->id,$thread);
458 $c->res->redirect($c->uri_for('allUnread'));
461 sub privmsg : Local {
462 my ( $self, $c, $uid ) = @_;
465 $c->stash(uid => $uid);
467 $c->forward('findUsers');
470 sub postprivmsg : Local {
471 my ( $self, $c ) = @_;
475 $c->forward('insertThread',[-1999]);
477 $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
478 unless ref $c->req->parameters->{uid} eq 'ARRAY';
479 push @{$c->req->parameters->{uid}}, $c->user->id;
480 $c->forward('addaccess',[$c->stash->{thread}]);
482 $c->forward('addPost',[$c->stash->{thread}]);
486 sub addaccess : Private {
487 my ( $self, $c, $thread) = @_;
490 $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
491 unless ref $c->req->parameters->{uid} eq 'ARRAY';
492 my $query = $dbh->prepare(q{INSERT INTO forum_priv_access (ftid,uid)
493 (SELECT $1,uid FROM users u WHERE uid = ANY ($2) AND NOT uid
494 IN (SELECT uid FROM forum_priv_access WHERE ftid = $1))});
495 $query->execute($thread,$c->req->parameters->{uid});
496 $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
497 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
498 }, undef, $c->user->id
499 ,"Gave access on thread $thread to : @{$c->req->parameters->{uid}}");
502 sub findUsers : Private {
503 my ( $self, $c ) = @_;
506 my $query = $dbh->prepare(q{SELECT uid,username FROM users
507 WHERE uid > 0 AND uid IN (SELECT uid FROM groupmembers)
511 $c->stash(users => $query->fetchall_arrayref({}) );
514 sub findThread : Private {
515 my ( $self, $c, $thread ) = @_;
517 my $findThread = $dbh->prepare(q{SELECT ft.ftid,ft.subject
518 ,COALESCE(bool_or(fa.post),true) AS post, bool_or(fa.moderate) AS moderate
519 ,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
521 NATURAL JOIN forum_threads ft
522 NATURAL JOIN forum_categories fc
523 LEFT OUTER JOIN (SELECT fa.* FROM forum_access fa
524 JOIN (SELECT groups($2) AS gid) g USING (gid)
526 WHERE ft.ftid = $1 AND (fa.post IS NOT NULL
527 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2))
528 GROUP BY ft.ftid,ft.subject,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
530 $thread = $dbh->selectrow_hashref($findThread,undef,$thread,$c->stash->{UID});
531 $c->stash(thread => $thread);
534 sub findBoard : Private {
535 my ( $self, $c, $board ) = @_;
538 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
540 NATURAL JOIN forum_categories fc
541 LEFT OUTER JOIN (SELECT * FROM forum_access
542 WHERE fbid = $1 AND gid IN (SELECT groups($2))
545 GROUP BY fb.fbid,fb.board,fb.fcid,fc.category
547 $board = $dbh->selectrow_hashref($boards,undef,$board,$c->stash->{UID});
549 $c->stash(board => $board);
552 sub previewPost : Private {
553 my ( $self, $c) = @_;
554 my $message = html_escape $c->req->param('message');
555 push @{$c->stash->{posts}}, {
557 username => 'PREVIEW',
558 message => parseMarkup($message),
560 $c->stash(previewMessage => $message);
563 sub insertPost : Private {
564 my ( $self, $c, $thread ) = @_;
567 my $message = html_escape($c->req->param('message'));
568 my $insert = $dbh->prepare(q{INSERT INTO forum_posts (ftid,message,uid)
570 $insert->execute($thread,$message,$c->stash->{UID});
573 sub listModeratorBoards : Private {
574 my ( $self, $c, $fbid ) = @_;
577 my $categories = $dbh->prepare(q{SELECT fcid,category FROM forum_categories ORDER BY fcid});
578 my $boards = $dbh->prepare(q{SELECT fb.fbid,fb.board, bool_or(fa.post) AS post
579 FROM forum_boards fb NATURAL JOIN forum_access fa
581 AND gid IN (SELECT groups($2))
583 GROUP BY fb.fbid,fb.board
586 $categories->execute;
588 while (my $category = $categories->fetchrow_hashref){
589 $boards->execute($category->{fcid},$c->stash->{UID});
592 while (my $b = $boards->fetchrow_hashref){
593 next if ($b->{fbid} == $fbid);
596 $category->{boards} = \@boards;
597 push @categories,$category if @boards;
599 $c->stash(categories => \@categories);
604 Michael Andreen (harv@ruin.nu)