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{
  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)
  38                 ftv USING (ftid)
  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
  42                 WHERE uid = $1)
  43 GROUP BY fcid,category,fb.fbid, fb.board
  44 ORDER BY fcid,fb.fbid
  45                 });
  46                 $boards->execute($c->stash->{UID});
  47
  48         my @categories;
  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;
  54                 }
  55                 push @{$category->{boards}},$board;
  56         }
  57         $c->stash(categories => \@categories);
  58 }
  59
  60 sub allUnread : Local {
  61         my ( $self, $c ) = @_;
  62         my $dbh = $c->model;
  63
  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)
  79         AND ((fbid > 0 AND
  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
  83                 });
  84
  85         $threads->execute($c->stash->{UID});
  86         my @categories;
  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;
  93                 }
  94                 if ($board->{fbid} != $thread->{fbid}){
  95                         $board = {fbid => $thread->{fbid}, board => $thread->{board}};
  96                         push @{$category->{boards}},$board;
  97                 }
  98                 delete $thread->{fcid};
  99                 delete $thread->{fbid};
 100                 delete $thread->{category};
 101                 delete $thread->{board};
 102                 push @{$board->{threads}},$thread;
 103         }
 104         $c->stash(categories => \@categories);
 105         $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
 106 }
 107
 108
 109 sub search : Local {
 110         my ( $self, $c ) = @_;
 111
 112         my $dbh = $c->model;
 113
 114         my @queries;
 115         if ($c->req->param('search')){
 116                 push @queries,'('.$c->req->param('search').')';
 117         }
 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'.$_) ? '&' : '|';
 123                         my $cat = $cat{$_};
 124                         my $query = join " $op ", map {"$_:$cat"} @words;
 125                         push @queries,"($query)";
 126                 }
 127         }
 128         my $search = join ' & ', @queries;
 129
 130         if ($search){
 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
 134                         FROM forum_boards fb
 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)
 142                         ORDER BY rank DESC
 143                 });
 144                 eval {
 145                         $posts->execute($c->stash->{UID},$search);
 146                         my @posts;
 147                         while (my $post = $posts->fetchrow_hashref){
 148                                 push @posts,$post;
 149                         }
 150                         $c->stash(searchresults => \@posts);
 151                 };
 152                 if ($@){
 153                         $c->stash( searcherror => $dbh->errstr);
 154                 }
 155         }
 156
 157 }
 158
 159
 160 sub board : Local {
 161         my ( $self, $c, $board ) = @_;
 162         my $dbh = $c->model;
 163
 164         $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
 165
 166         $c->forward('findBoard');
 167         $board = $c->stash->{board};
 168         if ( !defined $board->{fbid}){
 169                 $c->detach('/default');
 170         }
 171
 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)
 185         )
 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
 188         });
 189         $threads->execute($board->{fbid},$c->stash->{UID});
 190         my @threads;
 191         while (my $thread = $threads->fetchrow_hashref){
 192                 push @threads,$thread;
 193         }
 194
 195         if ( !(defined $board->{post}) && @threads == 0){
 196                 $c->acl_access_denied('test',$c->action,'No access to board')
 197         }
 198         $c->stash(threads => \@threads);
 199
 200         $c->stash(title => "$board->{board} ($board->{category})");
 201
 202         $c->forward('listModeratorBoards', [$board->{fbid}]) if $board->{moderate};
 203
 204 }
 205
 206 sub thread : Local {
 207         my ( $self, $c, $thread ) = @_;
 208         my $dbh = $c->model;
 209
 210         $c->forward('findThread');
 211         $thread = $c->stash->{thread};
 212         unless ($thread){
 213                 $c->stash(template => 'default.tt2');
 214                 $c->res->status(404);
 215                 return;
 216         }
 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}]);
 228         }
 229 }
 230
 231 sub findPosts :Private {
 232         my ( $self, $c, $thread ) = @_;
 233         my $dbh = $c->model;
 234
 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
 241                         LEFT OUTER JOIN
 242                                 (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
 243                 WHERE ft.ftid = $1
 244                 ORDER BY fp.time ASC
 245                 });
 246         $posts->execute($thread,$c->stash->{UID});
 247
 248         my @posts;
 249         while (my $post = $posts->fetchrow_hashref){
 250                 $post->{message} = parseMarkup($post->{message});
 251                 push @posts,$post;
 252         }
 253
 254         $c->stash(posts => \@posts);
 255 }
 256
 257
 258 sub markBoardAsRead : Local {
 259         my ( $self, $c, $board, $time ) = @_;
 260         my $dbh = $c->model;
 261
 262         $c->forward('findBoard');
 263         $board = $c->stash->{board};
 264
 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
 274                 });
 275         $threads->execute($board->{fbid},$c->user->id,$time);
 276         $dbh->begin_work;
 277         while (my $thread = $threads->fetchrow_hashref){
 278                 $c->forward('markThreadAsRead',[$thread->{ftid}]);
 279         }
 280         $dbh->commit;
 281         $c->forward('/redirect');
 282 }
 283
 284 sub markThreadAsRead : Private {
 285         my ( $self, $c, $thread ) = @_;
 286         my $dbh = $c->model;
 287
 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);
 291         if ($rows == 0){
 292                 $dbh->do(q{INSERT INTO forum_thread_visits (uid,ftid)
 293                         VALUES ($1,$2)}
 294                         ,undef,$c->user->id,$thread);
 295         }
 296 }
 297
 298 sub markThreadAsUnread : Local {
 299         my ( $self, $c, $thread ) = @_;
 300         my $dbh = $c->model;
 301
 302         my ($fbid) = $dbh->selectrow_array(q{
 303 SELECT fbid FROM forum_threads WHERE ftid = $1
 304                 },undef, $thread);
 305
 306         $dbh->do(q{
 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));
 310 }
 311
 312 sub markPostAsUnread : Local {
 313         my ( $self, $c, $post ) = @_;
 314         my $dbh = $c->model;
 315
 316         my ($fbid) = $dbh->selectrow_array(q{
 317 SELECT fbid FROM forum_threads JOIN forum_posts USING (ftid) WHERE fpid = $1
 318                 },undef, $post);
 319
 320         $dbh->do(q{
 321 UPDATE forum_thread_visits ftv SET time = (fp.time - interval '1 second')
 322 FROM forum_posts fp
 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));
 326 }
 327
 328 sub moveThreads : Local {
 329         my ( $self, $c, $board ) = @_;
 330         my $dbh = $c->model;
 331
 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.')
 337         }
 338
 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.')
 343         }
 344
 345         my $log = "Moved these threads:\n\n";
 346         $dbh->begin_work;
 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){
 352                                 $log .= "$1\n";
 353                         }
 354                 }
 355         }
 356
 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);
 362         $dbh->commit;
 363
 364         $c->res->redirect($c->uri_for('board',$board->{fbid}));
 365 }
 366
 367 sub newThread : Local {
 368         my ( $self, $c, $board ) = @_;
 369
 370         $c->forward('findBoard');
 371         $board = $c->stash->{board};
 372
 373         unless ($c->stash->{board}->{post}){
 374                 $c->acl_access_denied('test',$c->action,'No post access to board.')
 375         }
 376
 377         $c->forward('insertThread');
 378         $c->forward('addPost',[$c->stash->{thread}]);
 379 }
 380
 381 sub insertThread : Private {
 382         my ( $self, $c, $board ) = @_;
 383         my $dbh = $c->model;
 384
 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);
 388                 });
 389         $insert->execute($board,$subject,$c->stash->{UID});
 390         $c->stash(thread => $insert->fetchrow);
 391         $insert->finish;
 392 }
 393
 394 sub addPost : Local {
 395         my ( $self, $c, $thread ) = @_;
 396         my $dbh = $c->model;
 397
 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.')
 402                 }
 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');
 409         }
 410 }
 411
 412 sub setSticky : Local {
 413         my ( $self, $c, $thread, $sticky ) = @_;
 414         my $dbh = $c->model;
 415
 416         $c->forward('findThread');
 417         unless ($c->stash->{thread}->{moderate}){
 418                 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
 419         }
 420
 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));
 424 }
 425
 426 sub postthreadaccess : Local {
 427         my ( $self, $c, $thread) = @_;
 428         my $dbh = $c->model;
 429
 430         $c->forward('findThread');
 431         $dbh->begin_work;
 432         unless ($c->stash->{thread}->{moderate}){
 433                 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
 434         }
 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}}");
 445         }
 446         if ($c->req->param('uid')){
 447                 $c->forward('addaccess');
 448         }
 449         $dbh->commit;
 450         $c->res->redirect($c->uri_for('thread',$thread));
 451 }
 452
 453 sub removeownthreadaccess : Local {
 454         my ( $self, $c, $thread) = @_;
 455         my $dbh = $c->model;
 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'));
 459 }
 460
 461 sub privmsg : Local {
 462         my ( $self, $c, $uid ) = @_;
 463
 464         $uid ||= 0;
 465         $c->stash(uid => $uid);
 466
 467         $c->forward('findUsers');
 468 }
 469
 470 sub postprivmsg : Local {
 471         my ( $self, $c ) = @_;
 472         my $dbh = $c->model;
 473
 474         $dbh->begin_work;
 475         $c->forward('insertThread',[-1999]);
 476
 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}]);
 481
 482         $c->forward('addPost',[$c->stash->{thread}]);
 483         $dbh->commit;
 484 }
 485
 486 sub addaccess : Private {
 487         my ( $self, $c, $thread) = @_;
 488         my $dbh = $c->model;
 489
 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}}");
 500 }
 501
 502 sub findUsers : Private {
 503         my ( $self, $c ) = @_;
 504         my $dbh = $c->model;
 505
 506         my $query = $dbh->prepare(q{SELECT uid,username FROM users
 507                 WHERE uid > 0 AND uid IN (SELECT uid FROM groupmembers)
 508                 ORDER BY username});
 509         $query->execute;
 510
 511         $c->stash(users => $query->fetchall_arrayref({}) );
 512 }
 513
 514 sub findThread : Private {
 515         my ( $self, $c, $thread ) = @_;
 516         my $dbh = $c->model;
 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
 520                 FROM forum_boards fb
 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)
 525                         ) fa USING (fbid)
 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
 529         });
 530         $thread = $dbh->selectrow_hashref($findThread,undef,$thread,$c->stash->{UID});
 531         $c->stash(thread => $thread);
 532 }
 533
 534 sub findBoard : Private {
 535         my ( $self, $c, $board ) = @_;
 536         my $dbh = $c->model;
 537
 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
 539                         FROM forum_boards fb
 540                                 NATURAL JOIN forum_categories fc
 541                                 LEFT OUTER JOIN (SELECT * FROM forum_access
 542                                         WHERE fbid = $1 AND gid IN (SELECT groups($2))
 543                                 ) fa USING (fbid)
 544                         WHERE fb.fbid = $1
 545                         GROUP BY fb.fbid,fb.board,fb.fcid,fc.category
 546                 });
 547         $board = $dbh->selectrow_hashref($boards,undef,$board,$c->stash->{UID});
 548
 549         $c->stash(board => $board);
 550 }
 551
 552 sub previewPost : Private {
 553         my ( $self, $c) = @_;
 554         my $message = html_escape $c->req->param('message');
 555         push @{$c->stash->{posts}}, {
 556                 unread => 1,
 557                 username => 'PREVIEW',
 558                 message => parseMarkup($message),
 559         };
 560         $c->stash(previewMessage => $message);
 561 }
 562
 563 sub insertPost : Private {
 564         my ( $self, $c, $thread ) = @_;
 565         my $dbh = $c->model;
 566
 567         my $message = html_escape($c->req->param('message'));
 568         my $insert = $dbh->prepare(q{INSERT INTO forum_posts (ftid,message,uid)
 569                 VALUES($1,$2,$3)});
 570         $insert->execute($thread,$message,$c->stash->{UID});
 571 }
 572
 573 sub listModeratorBoards : Private {
 574         my ( $self, $c, $fbid ) = @_;
 575         my $dbh = $c->model;
 576
 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
 580                 WHERE fb.fcid = $1
 581                         AND gid IN (SELECT groups($2))
 582                         AND moderate
 583                 GROUP BY fb.fbid,fb.board
 584                 ORDER BY fb.fbid
 585                 });
 586         $categories->execute;
 587         my @categories;
 588         while (my $category = $categories->fetchrow_hashref){
 589                 $boards->execute($category->{fcid},$c->stash->{UID});
 590
 591                 my @boards;
 592                 while (my $b = $boards->fetchrow_hashref){
 593                         next if ($b->{fbid} == $fbid);
 594                         push @boards,$b;
 595                 }
 596                 $category->{boards} = \@boards;
 597                 push @categories,$category if @boards;
 598         }
 599         $c->stash(categories => \@categories);
 600 }
 601
 602 =head1 AUTHOR
 603
 604 Michael Andreen (harv@ruin.nu)
 605
 606 =head1 LICENSE
 607
 608 GPL 2.0, or later.
 609
 610 =cut
 611
 612 1;