lib/NDWeb/Controller/Forum.pm

   1 package NDWeb::Controller::Forum;
   2
   3 use strict;
   4 use warnings;
   5 use parent 'Catalyst::Controller';
   6
   7 use NDWeb::Include;
   8
   9 =head1 NAME
  10
  11 NDWeb::Controller::Forum - Catalyst Controller
  12
  13 =head1 DESCRIPTION
  14
  15 Catalyst Controller.
  16
  17 =head1 METHODS
  18
  19 =cut
  20
  21 =head2 index
  22
  23 =cut
  24
  25 sub index :Path :Args(0) {
  26         my ( $self, $c ) = @_;
  27         my $dbh = $c->model;
  28
  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
  33                         FROM forum_categories
  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
  40                         ORDER BY fcid,fb.fbid
  41                 });
  42                 $boards->execute($c->stash->{UID});
  43
  44         my @categories;
  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;
  50                 }
  51                 push @{$category->{boards}},$board;
  52         }
  53         $c->stash(categories => \@categories);
  54 }
  55
  56 sub allUnread : Local {
  57         my ( $self, $c ) = @_;
  58         my $dbh = $c->model;
  59
  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
  70                 WHERE fbid > 0 AND
  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
  75                 });
  76
  77         $threads->execute($c->stash->{UID});
  78         my @categories;
  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;
  85                 }
  86                 if ($board->{fbid} != $thread->{fbid}){
  87                         $board = {fbid => $thread->{fbid}, board => $thread->{board}};
  88                         push @{$category->{boards}},$board;
  89                 }
  90                 delete $thread->{fcid};
  91                 delete $thread->{fbid};
  92                 delete $thread->{category};
  93                 delete $thread->{board};
  94                 push @{$board->{threads}},$thread;
  95         }
  96         $c->stash(categories => \@categories);
  97         $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
  98 }
  99
 100
 101 sub search : Local {
 102         my ( $self, $c ) = @_;
 103
 104         my $dbh = $c->model;
 105
 106         my @queries;
 107         if ($c->req->param('search')){
 108                 push @queries,'('.$c->req->param('search').')';
 109         }
 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'.$_) ? '&' : '|';
 115                         my $cat = $cat{$_};
 116                         my $query = join " $op ", map {"$_:$cat"} @words;
 117                         push @queries,"($query)";
 118                 }
 119         }
 120         my $search = join ' & ', @queries;
 121
 122         if ($search){
 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
 126                         FROM forum_boards fb
 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)
 133                         ORDER BY rank DESC
 134                 });
 135                 eval {
 136                         $posts->execute($c->stash->{UID},$search);
 137                         my @posts;
 138                         while (my $post = $posts->fetchrow_hashref){
 139                                 push @posts,$post;
 140                         }
 141                         $c->stash(searchresults => \@posts);
 142                 };
 143                 if ($@){
 144                         $c->stash( searcherror => $dbh->errstr);
 145                 }
 146         }
 147
 148 }
 149
 150
 151 sub board : Local {
 152         my ( $self, $c, $board ) = @_;
 153         my $dbh = $c->model;
 154
 155         $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
 156
 157         $c->forward('findBoard');
 158         $board = $c->stash->{board};
 159
 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
 168                 WHERE ft.fbid = $1
 169                 GROUP BY ft.ftid, ft.subject,ft.sticky,u.username
 170                 ORDER BY sticky DESC,last_post DESC
 171         });
 172         $threads->execute($board->{fbid},$c->stash->{UID});
 173         my @threads;
 174         while (my $thread = $threads->fetchrow_hashref){
 175                 push @threads,$thread;
 176         }
 177         $c->stash(threads => \@threads);
 178
 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
 186                         ORDER BY fb.fbid
 187                 });
 188                 $categories->execute;
 189                 my @categories;
 190                 while (my $category = $categories->fetchrow_hashref){
 191                         $boards->execute($category->{fcid},$c->stash->{UID});
 192
 193                         my @boards;
 194                         while (my $b = $boards->fetchrow_hashref){
 195                                 next if (not $b->{post} or $b->{fbid} == $board->{fbid});
 196                                 push @boards,$b;
 197                         }
 198                         $category->{boards} = \@boards;
 199                         push @categories,$category if @boards;
 200                 }
 201                 $c->stash(categories => \@categories);
 202         }
 203 }
 204
 205
 206 sub thread : Local {
 207         my ( $self, $c, $thread ) = @_;
 208         my $dbh = $c->model;
 209
 210         $c->forward('findThread');
 211         $c->forward('findPosts') if $c->stash->{thread};
 212         $c->forward('markThreadAsRead') if $c->user_exists;
 213 }
 214
 215 sub findPosts :Private {
 216         my ( $self, $c, $thread ) = @_;
 217         my $dbh = $c->model;
 218
 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
 225                         LEFT OUTER JOIN
 226                                 (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
 227                 WHERE ft.ftid = $1
 228                 ORDER BY fp.time ASC
 229                 });
 230         $posts->execute($thread,$c->stash->{UID});
 231
 232         my @posts;
 233         while (my $post = $posts->fetchrow_hashref){
 234                 $post->{message} = parseMarkup($post->{message});
 235                 push @posts,$post;
 236         }
 237
 238         $c->stash(posts => \@posts);
 239 }
 240
 241
 242 sub markBoardAsRead : Local {
 243         my ( $self, $c, $board, $time ) = @_;
 244         my $dbh = $c->model;
 245
 246         $c->forward('findBoard');
 247         $board = $c->stash->{board};
 248
 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
 258                 });
 259         $threads->execute($board->{fbid},$c->user->id,$time);
 260         $dbh->begin_work;
 261         while (my $thread = $threads->fetchrow_hashref){
 262                 $c->forward('markThreadAsRead',[$thread->{ftid}]);
 263         }
 264         $dbh->commit;
 265         $c->res->redirect($c->req->referer);
 266 }
 267
 268 sub markThreadAsRead : Private {
 269         my ( $self, $c, $thread ) = @_;
 270         my $dbh = $c->model;
 271
 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);
 275         if ($rows == 0){
 276                 $dbh->do(q{INSERT INTO forum_thread_visits (uid,ftid)
 277                         VALUES ($1,$2)}
 278                         ,undef,$c->user->id,$thread);
 279         }
 280 }
 281
 282 sub moveThreads : Local {
 283         my ( $self, $c, $board ) = @_;
 284         my $dbh = $c->model;
 285
 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.')
 290         }
 291
 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.')
 296         }
 297
 298         my $log = "Moved these threads:\n\n";
 299         $dbh->begin_work;
 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){
 305                                 $log .= "$1\n";
 306                         }
 307                 }
 308         }
 309
 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);
 315         $dbh->commit;
 316
 317         $c->res->redirect($c->uri_for('board',$board->{fbid}));
 318 }
 319
 320 sub newThread : Local {
 321         my ( $self, $c, $board ) = @_;
 322
 323         $c->forward('findBoard');
 324         $board = $c->stash->{board};
 325
 326         unless ($c->stash->{board}->{post}){
 327                 $c->acl_access_denied('test',$c->action,'No post access to board.')
 328         }
 329
 330         $c->forward('insertThread');
 331         $c->forward('addPost',[$c->stash->{thread}]);
 332 }
 333
 334 sub insertThread : Private {
 335         my ( $self, $c, $board ) = @_;
 336         my $dbh = $c->model;
 337
 338         my $insert = $dbh->prepare(q{INSERT INTO forum_threads (ftid,fbid,subject,uid)
 339                 VALUES(DEFAULT,$1,$2,$3) RETURNING (ftid);
 340                 });
 341         $insert->execute($board,html_escape($c->req->param('subject')),$c->stash->{UID});
 342         $c->stash(thread => $insert->fetchrow);
 343         $insert->finish;
 344 }
 345
 346 sub addPost : Local {
 347         my ( $self, $c, $thread ) = @_;
 348         my $dbh = $c->model;
 349
 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.')
 354                 }
 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');
 361         }
 362 }
 363
 364 sub setSticky : Local {
 365         my ( $self, $c, $thread, $sticky ) = @_;
 366         my $dbh = $c->model;
 367
 368         $c->forward('findThread');
 369         unless ($c->stash->{thread}->{moderate}){
 370                 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
 371         }
 372
 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));
 376 }
 377
 378 sub findThread : Private {
 379         my ( $self, $c, $thread ) = @_;
 380         my $dbh = $c->model;
 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
 383                 FROM forum_boards fb
 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
 389         });
 390         $thread = $dbh->selectrow_hashref($findThread,undef,$thread,$c->stash->{UID});
 391         $c->stash(thread => $thread);
 392 }
 393
 394 sub findBoard : Private {
 395         my ( $self, $c, $board ) = @_;
 396         my $dbh = $c->model;
 397
 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
 399                         FROM forum_boards fb
 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
 405                 });
 406         $board = $dbh->selectrow_hashref($boards,undef,$board,$c->stash->{UID});
 407
 408         $c->stash(board => $board);
 409 }
 410
 411 sub previewPost : Private {
 412         my ( $self, $c) = @_;
 413         push @{$c->stash->{posts}}, {
 414                 unread => 1,
 415                 username => 'PREVIEW',
 416                 message => parseMarkup(html_escape $c->req->param('message')),
 417         };
 418         $c->stash(previewMessage => html_escape $c->req->param('message'));
 419 }
 420
 421 sub insertPost : Private {
 422         my ( $self, $c, $thread ) = @_;
 423         my $dbh = $c->model;
 424
 425         my $insert = $dbh->prepare(q{INSERT INTO forum_posts (ftid,message,uid)
 426                 VALUES($1,$2,$3)});
 427         $insert->execute($thread,html_escape($c->req->param('message')),$c->stash->{UID});
 428 }
 429
 430 =head1 AUTHOR
 431
 432 Michael Andreen (harv@ruin.nu)
 433
 434 =head1 LICENSE
 435
 436 GPL 2.0, or later.
 437
 438 =cut
 439
 440 1;