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 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1)
40 GROUP BY fcid,category,fb.fbid, fb.board
43 $boards->execute($c->stash->{UID});
46 my $category = {fcid => 0};
47 while (my $board = $boards->fetchrow_hashref){
48 if ($category->{fcid} != $board->{fcid}){
49 $category = {fcid => $board->{fcid}, category => $board->{category}};
50 push @categories,$category;
52 push @{$category->{boards}},$board;
54 $c->stash(categories => \@categories);
57 sub allUnread : Local {
58 my ( $self, $c ) = @_;
61 my $threads = $dbh->prepare(q{SELECT fcid,category,fbid,board,ft.ftid,u.username,ft.subject,
62 count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts,
63 date_trunc('seconds',max(fp.time)::timestamp) as last_post,
64 min(fp.time)::date as posting_date, ft.sticky
65 FROM forum_categories fc
66 JOIN forum_boards fb USING (fcid)
67 JOIN forum_threads ft USING (fbid)
68 JOIN forum_posts fp USING (ftid)
69 JOIN users u ON u.uid = ft.uid
70 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid
72 fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1)))
73 ) OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1)
74 GROUP BY fcid,category,fbid,board,ft.ftid, ft.subject,ft.sticky,u.username
75 HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
76 ORDER BY fcid,fbid,sticky DESC,last_post DESC
79 $threads->execute($c->stash->{UID});
81 my $category = {fcid => 0};
82 my $board = {fbid => 0};
83 while (my $thread = $threads->fetchrow_hashref){
84 if ($category->{fcid} != $thread->{fcid}){
85 $category = {fcid => $thread->{fcid}, category => $thread->{category}};
86 push @categories,$category;
88 if ($board->{fbid} != $thread->{fbid}){
89 $board = {fbid => $thread->{fbid}, board => $thread->{board}};
90 push @{$category->{boards}},$board;
92 delete $thread->{fcid};
93 delete $thread->{fbid};
94 delete $thread->{category};
95 delete $thread->{board};
96 push @{$board->{threads}},$thread;
98 $c->stash(categories => \@categories);
99 $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
104 my ( $self, $c ) = @_;
109 if ($c->req->param('search')){
110 push @queries,'('.$c->req->param('search').')';
112 my %cat = (body => 'D', topic => 'A', author => 'B');
113 for ('body','topic','author'){
114 if ($c->req->param($_)){
115 my @words = split /\W+/,$c->req->param($_);
116 my $op = $c->req->param('all'.$_) ? '&' : '|';
118 my $query = join " $op ", map {"$_:$cat"} @words;
119 push @queries,"($query)";
122 my $search = join ' & ', @queries;
125 my $posts = $dbh->prepare(q{SELECT fp.ftid,u.username,ft.subject
126 ,ts_headline(fp.message,to_tsquery($2)) AS headline
127 ,ts_rank_cd(fp.textsearch, to_tsquery($2),32) AS rank
129 JOIN forum_threads ft USING (fbid)
130 JOIN forum_posts fp USING (ftid)
131 JOIN users u ON fp.uid = u.uid
132 WHERE (fb.fbid IN (SELECT fbid FROM forum_access
133 WHERE gid IN (SELECT groups($1)))
134 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1)
135 ) AND fp.textsearch @@@ to_tsquery($2)
139 $posts->execute($c->stash->{UID},$search);
141 while (my $post = $posts->fetchrow_hashref){
144 $c->stash(searchresults => \@posts);
147 $c->stash( searcherror => $dbh->errstr);
155 my ( $self, $c, $board ) = @_;
158 $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
160 $c->forward('findBoard');
161 $board = $c->stash->{board};
162 if ( !defined $board->{fbid}){
163 $c->detach('/default');
166 my $threads = $dbh->prepare(q{SELECT ft.ftid,u.username,ft.subject
167 ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts
168 ,date_trunc('seconds',max(fp.time)::timestamp) as last_post
169 ,min(fp.time)::date as posting_date, ft.sticky
170 FROM forum_threads ft
171 JOIN forum_posts fp USING (ftid)
172 JOIN users u ON u.uid = ft.uid
173 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
174 WHERE ft.fbid = $1 AND (
175 ft.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($2)))
176 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2)
178 GROUP BY ft.ftid, ft.subject,ft.sticky,u.username
179 ORDER BY sticky DESC,last_post DESC
181 $threads->execute($board->{fbid},$c->stash->{UID});
183 while (my $thread = $threads->fetchrow_hashref){
184 push @threads,$thread;
187 if ( !(defined $board->{post}) && @threads == 0){
188 $c->acl_access_denied('test',$c->action,'No access to board')
190 $c->stash(threads => \@threads);
192 $c->stash(title => "$board->{board} ($board->{category})");
194 $c->forward('listModeratorBoards', [$board->{fbid}]) if $board->{moderate};
199 my ( $self, $c, $thread ) = @_;
202 $c->forward('findThread');
203 $thread = $c->stash->{thread};
205 $c->stash(template => 'default.tt2');
206 $c->res->status(404);
209 my $query = $dbh->prepare(q{SELECT uid,username FROM users u
210 JOIN forum_priv_access fta USING (uid) WHERE fta.ftid = $1});
211 $query->execute($thread->{ftid});
212 $c->stash(access => $query->fetchall_arrayref({}) );
213 $c->stash(title => $thread->{subject}
214 . " ($thread->{category} - $thread->{board})");
215 $c->forward('findPosts');
216 $c->forward('markThreadAsRead') if $c->user_exists;
217 if ($c->stash->{thread}->{moderate}) {
218 $c->forward('findUsers');
219 $c->forward('listModeratorBoards', [$c->stash->{thread}->{fbid}]);
223 sub findPosts :Private {
224 my ( $self, $c, $thread ) = @_;
227 my $posts = $dbh->prepare(q{
228 SELECT u.uid,u.username,date_trunc('seconds',fp.time::timestamp) AS time
229 ,fp.message,COALESCE(fp.time > ftv.time,TRUE) AS unread
230 FROM forum_threads ft
231 JOIN forum_posts fp USING (ftid)
232 JOIN users u ON u.uid = fp.uid
234 (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
238 $posts->execute($thread,$c->stash->{UID});
241 while (my $post = $posts->fetchrow_hashref){
242 $post->{message} = parseMarkup($post->{message});
246 $c->stash(posts => \@posts);
250 sub markBoardAsRead : Local {
251 my ( $self, $c, $board, $time ) = @_;
254 $c->forward('findBoard');
255 $board = $c->stash->{board};
257 my $threads = $dbh->prepare(q{SELECT ft.ftid,ft.subject
258 ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread
259 ,count(fp.fpid) AS posts, max(fp.time)::timestamp as last_post
260 FROM forum_threads ft
261 JOIN forum_posts fp USING (ftid)
262 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
263 WHERE ft.fbid = $1 AND fp.time <= $3
264 GROUP BY ft.ftid, ft.subject
265 HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
267 $threads->execute($board->{fbid},$c->user->id,$time);
269 while (my $thread = $threads->fetchrow_hashref){
270 $c->forward('markThreadAsRead',[$thread->{ftid}]);
273 $c->forward('/redirect');
276 sub markThreadAsRead : Private {
277 my ( $self, $c, $thread ) = @_;
280 my $rows = $dbh->do(q{UPDATE forum_thread_visits SET time = now()
281 WHERE uid = $1 AND ftid = $2
282 },undef,$c->user->id,$thread);
284 $dbh->do(q{INSERT INTO forum_thread_visits (uid,ftid)
286 ,undef,$c->user->id,$thread);
290 sub moveThreads : Local {
291 my ( $self, $c, $board ) = @_;
294 $c->forward('findBoard',[$c->req->param('board')]);
295 my $toboard = $c->stash->{board};
296 unless ($toboard->{moderate}){
297 $c->acl_access_denied('test',$c->action,'No moderator access for target board.')
300 $c->forward('findBoard');
301 $board = $c->stash->{board};
302 unless ($board->{moderate}){
303 $c->acl_access_denied('test',$c->action,'No moderator access for source board.')
306 my $log = "Moved these threads:\n\n";
308 my $moveThread = $dbh->prepare(q{UPDATE forum_threads SET fbid = $1 WHERE ftid = $2 AND fbid = $3});
309 for my $param ($c->req->param){
310 if ($param =~ /t:(\d+)/){
311 $moveThread->execute($toboard->{fbid},$1,$board->{fbid});
312 if ($moveThread->rows > 0){
318 $log .= "\nFrom board: $board->{board} ($board->{fbid})";
319 $log .= "\nTo board: $toboard->{board} ($toboard->{fbid})";
320 $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
321 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
322 }, undef, $c->user->id, $log);
325 $c->res->redirect($c->uri_for('board',$board->{fbid}));
328 sub newThread : Local {
329 my ( $self, $c, $board ) = @_;
331 $c->forward('findBoard');
332 $board = $c->stash->{board};
334 unless ($c->stash->{board}->{post}){
335 $c->acl_access_denied('test',$c->action,'No post access to board.')
338 $c->forward('insertThread');
339 $c->forward('addPost',[$c->stash->{thread}]);
342 sub insertThread : Private {
343 my ( $self, $c, $board ) = @_;
346 my $insert = $dbh->prepare(q{INSERT INTO forum_threads (ftid,fbid,subject,uid)
347 VALUES(DEFAULT,$1,$2,$3) RETURNING (ftid);
349 $insert->execute($board,html_escape($c->req->param('subject')),$c->stash->{UID});
350 $c->stash(thread => $insert->fetchrow);
354 sub addPost : Local {
355 my ( $self, $c, $thread ) = @_;
358 if ($c->req->param('cmd') eq 'Submit'){
359 $c->forward('findThread');
360 unless ($c->stash->{thread}->{post}){
361 $c->acl_access_denied('test',$c->action,'No post access to board.')
363 $c->forward('insertPost');
364 $c->res->redirect($c->uri_for('thread',$thread));
365 }elsif ($c->req->param('cmd') eq 'Preview'){
366 $c->forward('thread');
367 $c->forward('previewPost');
368 $c->stash(template => 'forum/thread.tt2');
372 sub setSticky : Local {
373 my ( $self, $c, $thread, $sticky ) = @_;
376 $c->forward('findThread');
377 unless ($c->stash->{thread}->{moderate}){
378 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
381 $dbh->do(q{UPDATE forum_threads SET sticky = $2 WHERE ftid = $1}
382 , undef,$thread, $sticky);
383 $c->res->redirect($c->uri_for('thread',$thread));
386 sub postthreadaccess : Local {
387 my ( $self, $c, $thread) = @_;
390 $c->forward('findThread');
392 unless ($c->stash->{thread}->{moderate}){
393 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
395 if ($c->req->param('access')){
396 $c->req->parameters->{access} = [$c->req->parameters->{access}]
397 unless ref $c->req->parameters->{access} eq 'ARRAY';
398 my $query = $dbh->prepare(q{DELETE From forum_priv_access
399 WHERE ftid = $1 AND uid = ANY ($2)});
400 $query->execute($thread,$c->req->parameters->{access});
401 $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
402 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
403 }, undef, $c->user->id
404 ,"Removed access on thread $thread for : @{$c->req->parameters->{access}}");
406 if ($c->req->param('uid')){
407 $c->forward('addaccess');
410 $c->res->redirect($c->uri_for('thread',$thread));
413 sub removeownthreadaccess : Local {
414 my ( $self, $c, $thread) = @_;
416 $dbh->do(q{DELETE FROM forum_priv_access WHERE uid = $1 AND ftid = $2}
417 ,undef,$c->user->id,$thread);
418 $c->res->redirect($c->uri_for('allUnread'));
421 sub privmsg : Local {
422 my ( $self, $c, $uid ) = @_;
425 $c->stash(uid => $uid);
427 $c->forward('findUsers');
430 sub postprivmsg : Local {
431 my ( $self, $c ) = @_;
435 $c->forward('insertThread',[-1999]);
437 $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
438 unless ref $c->req->parameters->{uid} eq 'ARRAY';
439 push @{$c->req->parameters->{uid}}, $c->user->id;
440 $c->forward('addaccess',[$c->stash->{thread}]);
442 $c->forward('addPost',[$c->stash->{thread}]);
446 sub addaccess : Private {
447 my ( $self, $c, $thread) = @_;
450 $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
451 unless ref $c->req->parameters->{uid} eq 'ARRAY';
452 my $query = $dbh->prepare(q{INSERT INTO forum_priv_access (ftid,uid)
453 (SELECT $1,uid FROM users u WHERE uid = ANY ($2) AND NOT uid
454 IN (SELECT uid FROM forum_priv_access WHERE ftid = $1))});
455 $query->execute($thread,$c->req->parameters->{uid});
456 $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
457 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
458 }, undef, $c->user->id
459 ,"Gave access on thread $thread to : @{$c->req->parameters->{uid}}");
462 sub findUsers : Private {
463 my ( $self, $c ) = @_;
466 my $query = $dbh->prepare(q{SELECT uid,username FROM users
467 WHERE uid > 0 AND uid IN (SELECT uid FROM groupmembers)
468 ORDER BY LOWER(username)});
471 $c->stash(users => $query->fetchall_arrayref({}) );
474 sub findThread : Private {
475 my ( $self, $c, $thread ) = @_;
477 my $findThread = $dbh->prepare(q{SELECT ft.ftid,ft.subject
478 ,COALESCE(bool_or(fa.post),true) AS post, bool_or(fa.moderate) AS moderate
479 ,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
481 NATURAL JOIN forum_threads ft
482 NATURAL JOIN forum_categories fc
483 LEFT OUTER JOIN (SELECT * FROM forum_access
484 WHERE gid IN (SELECT groups($2))
486 WHERE ft.ftid = $1 AND (fa.post IS NOT NULL
487 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2))
488 GROUP BY ft.ftid,ft.subject,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
490 $thread = $dbh->selectrow_hashref($findThread,undef,$thread,$c->stash->{UID});
491 $c->stash(thread => $thread);
494 sub findBoard : Private {
495 my ( $self, $c, $board ) = @_;
498 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
500 NATURAL JOIN forum_categories fc
501 LEFT OUTER JOIN (SELECT * FROM forum_access
502 WHERE fbid = $1 AND gid IN (SELECT groups($2))
505 GROUP BY fb.fbid,fb.board,fb.fcid,fc.category
507 $board = $dbh->selectrow_hashref($boards,undef,$board,$c->stash->{UID});
509 $c->stash(board => $board);
512 sub previewPost : Private {
513 my ( $self, $c) = @_;
514 push @{$c->stash->{posts}}, {
516 username => 'PREVIEW',
517 message => parseMarkup(html_escape $c->req->param('message')),
519 $c->stash(previewMessage => html_escape $c->req->param('message'));
522 sub insertPost : Private {
523 my ( $self, $c, $thread ) = @_;
526 my $insert = $dbh->prepare(q{INSERT INTO forum_posts (ftid,message,uid)
528 $insert->execute($thread,html_escape($c->req->param('message')),$c->stash->{UID});
531 sub listModeratorBoards : Private {
532 my ( $self, $c, $fbid ) = @_;
535 my $categories = $dbh->prepare(q{SELECT fcid,category FROM forum_categories ORDER BY fcid});
536 my $boards = $dbh->prepare(q{SELECT fb.fbid,fb.board, bool_or(fa.post) AS post
537 FROM forum_boards fb NATURAL JOIN forum_access fa
539 AND gid IN (SELECT groups($2))
541 GROUP BY fb.fbid,fb.board
544 $categories->execute;
546 while (my $category = $categories->fetchrow_hashref){
547 $boards->execute($category->{fcid},$c->stash->{UID});
550 while (my $b = $boards->fetchrow_hashref){
551 next if ($b->{fbid} == $fbid);
554 $category->{boards} = \@boards;
555 push @categories,$category if @boards;
557 $c->stash(categories => \@categories);
562 Michael Andreen (harv@ruin.nu)