]> ruin.nu Git - ndwebbie.git/blob - lib/NDWeb/Controller/Forum.pm
Added more status codes
[ndwebbie.git] / 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                                 OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1)
40                         GROUP BY fcid,category,fb.fbid, fb.board
41                         ORDER BY fcid,fb.fbid
42                 });
43                 $boards->execute($c->stash->{UID});
44
45         my @categories;
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;
51                 }
52                 push @{$category->{boards}},$board;
53         }
54         $c->stash(categories => \@categories);
55 }
56
57 sub allUnread : Local {
58         my ( $self, $c ) = @_;
59         my $dbh = $c->model;
60
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
71                 WHERE (fbid > 0 AND
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
77                 });
78
79         $threads->execute($c->stash->{UID});
80         my @categories;
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;
87                 }
88                 if ($board->{fbid} != $thread->{fbid}){
89                         $board = {fbid => $thread->{fbid}, board => $thread->{board}};
90                         push @{$category->{boards}},$board;
91                 }
92                 delete $thread->{fcid};
93                 delete $thread->{fbid};
94                 delete $thread->{category};
95                 delete $thread->{board};
96                 push @{$board->{threads}},$thread;
97         }
98         $c->stash(categories => \@categories);
99         $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
100 }
101
102
103 sub search : Local {
104         my ( $self, $c ) = @_;
105
106         my $dbh = $c->model;
107
108         my @queries;
109         if ($c->req->param('search')){
110                 push @queries,'('.$c->req->param('search').')';
111         }
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'.$_) ? '&' : '|';
117                         my $cat = $cat{$_};
118                         my $query = join " $op ", map {"$_:$cat"} @words;
119                         push @queries,"($query)";
120                 }
121         }
122         my $search = join ' & ', @queries;
123
124         if ($search){
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
128                         FROM forum_boards fb 
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)
136                         ORDER BY rank DESC
137                 });
138                 eval {
139                         $posts->execute($c->stash->{UID},$search);
140                         my @posts;
141                         while (my $post = $posts->fetchrow_hashref){
142                                 push @posts,$post;
143                         }
144                         $c->stash(searchresults => \@posts);
145                 };
146                 if ($@){
147                         $c->stash( searcherror => $dbh->errstr);
148                 }
149         }
150
151 }
152
153
154 sub board : Local {
155         my ( $self, $c, $board ) = @_;
156         my $dbh = $c->model;
157
158         $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef));
159
160         $c->forward('findBoard');
161         $board = $c->stash->{board};
162         if ( !defined $board->{fbid}){
163                 $c->detach('/default');
164         }
165
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)
177                         )
178                 GROUP BY ft.ftid, ft.subject,ft.sticky,u.username
179                 ORDER BY sticky DESC,last_post DESC
180         });
181         $threads->execute($board->{fbid},$c->stash->{UID});
182         my @threads;
183         while (my $thread = $threads->fetchrow_hashref){
184                 push @threads,$thread;
185         }
186
187         if ( !(defined $board->{post}) && @threads == 0){
188                 $c->acl_access_denied('test',$c->action,'No access to board')
189         }
190         $c->stash(threads => \@threads);
191
192         $c->forward('listModeratorBoards', [$board->{fbid}]) if $board->{moderate};
193         
194 }
195
196 sub thread : Local {
197         my ( $self, $c, $thread ) = @_;
198         my $dbh = $c->model;
199
200         $c->forward('findThread');
201         unless ($c->stash->{thread}){
202                 $c->stash(template => 'default.tt2');
203                 $c->res->status(404);
204                 return;
205         }
206         my $query = $dbh->prepare(q{SELECT uid,username FROM users u
207                 JOIN forum_priv_access fta USING (uid) WHERE fta.ftid = $1});
208         $query->execute($thread);
209         $c->stash(access => $query->fetchall_arrayref({}) );
210         $c->forward('findPosts');
211         $c->forward('markThreadAsRead') if $c->user_exists;
212         if ($c->stash->{thread}->{moderate}) {
213                 $c->forward('findUsers');
214                 $c->forward('listModeratorBoards', [$c->stash->{thread}->{fbid}]);
215         }
216 }
217
218 sub findPosts :Private {
219         my ( $self, $c, $thread ) = @_;
220         my $dbh = $c->model;
221
222         my $posts = $dbh->prepare(q{
223                 SELECT u.uid,u.username,date_trunc('seconds',fp.time::timestamp) AS time
224                         ,fp.message,COALESCE(fp.time > ftv.time,TRUE) AS unread
225                 FROM forum_threads ft
226                         JOIN forum_posts fp USING (ftid)
227                         JOIN users u ON u.uid = fp.uid
228                         LEFT OUTER JOIN 
229                                 (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
230                 WHERE ft.ftid = $1
231                 ORDER BY fp.time ASC
232                 });
233         $posts->execute($thread,$c->stash->{UID});
234
235         my @posts;
236         while (my $post = $posts->fetchrow_hashref){
237                 $post->{message} = parseMarkup($post->{message});
238                 push @posts,$post;
239         }
240
241         $c->stash(posts => \@posts);
242 }
243
244
245 sub markBoardAsRead : Local {
246         my ( $self, $c, $board, $time ) = @_;
247         my $dbh = $c->model;
248
249         $c->forward('findBoard');
250         $board = $c->stash->{board};
251
252         my $threads = $dbh->prepare(q{SELECT ft.ftid,ft.subject
253                         ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread
254                         ,count(fp.fpid) AS posts, max(fp.time)::timestamp as last_post
255                         FROM forum_threads ft 
256                                 JOIN forum_posts fp USING (ftid) 
257                                 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
258                         WHERE ft.fbid = $1 AND fp.time <= $3
259                         GROUP BY ft.ftid, ft.subject
260                         HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
261                 });
262         $threads->execute($board->{fbid},$c->user->id,$time);
263         $dbh->begin_work;
264         while (my $thread = $threads->fetchrow_hashref){
265                 $c->forward('markThreadAsRead',[$thread->{ftid}]);
266         }
267         $dbh->commit;
268         $c->forward('/redirect');
269 }
270
271 sub markThreadAsRead : Private {
272         my ( $self, $c, $thread ) = @_;
273         my $dbh = $c->model;
274
275         my $rows = $dbh->do(q{UPDATE forum_thread_visits SET time = now() 
276                 WHERE uid =     $1 AND ftid = $2
277                 },undef,$c->user->id,$thread);
278         if ($rows == 0){
279                 $dbh->do(q{INSERT INTO forum_thread_visits (uid,ftid)
280                         VALUES ($1,$2)}
281                         ,undef,$c->user->id,$thread);
282         }
283 }
284
285 sub moveThreads : Local {
286         my ( $self, $c, $board ) = @_;
287         my $dbh = $c->model;
288
289         $c->forward('findBoard',[$c->req->param('board')]);
290         my $toboard = $c->stash->{board};
291         unless ($toboard->{moderate}){
292                 $c->acl_access_denied('test',$c->action,'No moderator access for target board.')
293         }
294
295         $c->forward('findBoard');
296         $board = $c->stash->{board};
297         unless ($board->{moderate}){
298                 $c->acl_access_denied('test',$c->action,'No moderator access for source board.')
299         }
300
301         my $log = "Moved these threads:\n\n";
302         $dbh->begin_work;
303         my $moveThread = $dbh->prepare(q{UPDATE forum_threads SET fbid = $1 WHERE ftid = $2 AND fbid = $3});
304         for my $param ($c->req->param){
305                 if ($param =~ /t:(\d+)/){
306                         $moveThread->execute($toboard->{fbid},$1,$board->{fbid});
307                         if ($moveThread->rows > 0){
308                                 $log .= "$1\n";
309                         }
310                 }
311         }
312
313         $log .= "\nFrom board: $board->{board} ($board->{fbid})";
314         $log .= "\nTo board: $toboard->{board} ($toboard->{fbid})";
315         $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
316                 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
317                 }, undef, $c->user->id, $log);
318         $dbh->commit;
319         
320         $c->res->redirect($c->uri_for('board',$board->{fbid}));
321 }
322
323 sub newThread : Local {
324         my ( $self, $c, $board ) = @_;
325
326         $c->forward('findBoard');
327         $board = $c->stash->{board};
328
329         unless ($c->stash->{board}->{post}){
330                 $c->acl_access_denied('test',$c->action,'No post access to board.')
331         }
332
333         $c->forward('insertThread');
334         $c->forward('addPost',[$c->stash->{thread}]);
335 }
336
337 sub insertThread : Private {
338         my ( $self, $c, $board ) = @_;
339         my $dbh = $c->model;
340
341         my $insert = $dbh->prepare(q{INSERT INTO forum_threads (ftid,fbid,subject,uid)
342                 VALUES(DEFAULT,$1,$2,$3) RETURNING (ftid);
343                 });
344         $insert->execute($board,html_escape($c->req->param('subject')),$c->stash->{UID});
345         $c->stash(thread => $insert->fetchrow);
346         $insert->finish;
347 }
348
349 sub addPost : Local {
350         my ( $self, $c, $thread ) = @_;
351         my $dbh = $c->model;
352
353         if ($c->req->param('cmd') eq 'Submit'){
354                 $c->forward('findThread');
355                 unless ($c->stash->{thread}->{post}){
356                         $c->acl_access_denied('test',$c->action,'No post access to board.')
357                 }
358                 $c->forward('insertPost');
359                 $c->res->redirect($c->uri_for('thread',$thread));
360         }elsif ($c->req->param('cmd') eq 'Preview'){
361                 $c->forward('thread');
362                 $c->forward('previewPost');
363                 $c->stash(template => 'forum/thread.tt2');
364         }
365 }
366
367 sub setSticky : Local {
368         my ( $self, $c, $thread, $sticky ) = @_;
369         my $dbh = $c->model;
370
371         $c->forward('findThread');
372         unless ($c->stash->{thread}->{moderate}){
373                 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
374         }
375
376         $dbh->do(q{UPDATE forum_threads SET sticky = $2 WHERE ftid = $1}
377                 , undef,$thread, $sticky);
378         $c->res->redirect($c->uri_for('thread',$thread));
379 }
380
381 sub postthreadaccess : Local {
382         my ( $self, $c, $thread) = @_;
383         my $dbh = $c->model;
384
385         $c->forward('findThread');
386         $dbh->begin_work;
387         unless ($c->stash->{thread}->{moderate}){
388                 $c->acl_access_denied('test',$c->action,'No moderator access to board.')
389         }
390         if ($c->req->param('access')){
391                 $c->req->parameters->{access} = [$c->req->parameters->{access}]
392                         unless ref $c->req->parameters->{access} eq 'ARRAY';
393                 my $query = $dbh->prepare(q{DELETE From forum_priv_access
394                         WHERE ftid = $1 AND uid = ANY ($2)});
395                 $query->execute($thread,$c->req->parameters->{access});
396                 $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
397                         VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
398                         }, undef, $c->user->id
399                         ,"Removed access on thread $thread for : @{$c->req->parameters->{access}}");
400         }
401         if ($c->req->param('uid')){
402                 $c->forward('addaccess');
403         }
404         $dbh->commit;
405         $c->res->redirect($c->uri_for('thread',$thread));
406 }
407
408 sub removeownthreadaccess : Local {
409         my ( $self, $c, $thread) = @_;
410         my $dbh = $c->model;
411         $dbh->do(q{DELETE FROM forum_priv_access WHERE uid = $1 AND ftid = $2}
412                 ,undef,$c->user->id,$thread);
413         $c->res->redirect($c->uri_for('allUnread'));
414 }
415
416 sub privmsg : Local {
417         my ( $self, $c, $uid ) = @_;
418
419         $uid ||= 0;
420         $c->stash(uid => $uid);
421
422         $c->forward('findUsers');
423 }
424
425 sub postprivmsg : Local {
426         my ( $self, $c ) = @_;
427         my $dbh = $c->model;
428
429         $dbh->begin_work;
430         $c->forward('insertThread',[-1999]);
431
432         $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
433                 unless ref $c->req->parameters->{uid} eq 'ARRAY';
434         push @{$c->req->parameters->{uid}}, $c->user->id;
435         $c->forward('addaccess',[$c->stash->{thread}]);
436
437         $c->forward('addPost',[$c->stash->{thread}]);
438         $dbh->commit;
439 }
440
441 sub addaccess : Private {
442         my ( $self, $c, $thread) = @_;
443         my $dbh = $c->model;
444
445         $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
446                 unless ref $c->req->parameters->{uid} eq 'ARRAY';
447         my $query = $dbh->prepare(q{INSERT INTO forum_priv_access (ftid,uid)
448                 (SELECT $1,uid FROM users u WHERE uid = ANY ($2) AND NOT uid
449                         IN (SELECT uid FROM forum_priv_access WHERE ftid = $1))});
450         $query->execute($thread,$c->req->parameters->{uid});
451         $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
452                 VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
453                 }, undef, $c->user->id
454                 ,"Gave access on thread $thread to : @{$c->req->parameters->{uid}}");
455 }
456
457 sub findUsers : Private {
458         my ( $self, $c ) = @_;
459         my $dbh = $c->model;
460
461         my $query = $dbh->prepare(q{SELECT uid,username FROM users
462                 WHERE uid > 0 AND uid IN (SELECT uid FROM groupmembers)
463                 ORDER BY LOWER(username)});
464         $query->execute;
465
466         $c->stash(users => $query->fetchall_arrayref({}) );
467 }
468
469 sub findThread : Private {
470         my ( $self, $c, $thread ) = @_;
471         my $dbh = $c->model;
472         my $findThread = $dbh->prepare(q{SELECT ft.ftid,ft.subject
473                 ,COALESCE(bool_or(fa.post),true) AS post, bool_or(fa.moderate) AS moderate
474                 ,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
475                 FROM forum_boards fb
476                         NATURAL JOIN forum_threads ft
477                         NATURAL JOIN forum_categories fc
478                         LEFT OUTER JOIN (SELECT * FROM forum_access
479                                 WHERE gid IN (SELECT groups($2))
480                         ) fa USING (fbid)
481                 WHERE ft.ftid = $1 AND (fa.post IS NOT NULL
482                         OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2))
483                 GROUP BY ft.ftid,ft.subject,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
484         });
485         $thread = $dbh->selectrow_hashref($findThread,undef,$thread,$c->stash->{UID});
486         $c->stash(thread => $thread);
487 }
488
489 sub findBoard : Private {
490         my ( $self, $c, $board ) = @_;
491         my $dbh = $c->model;
492
493         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
494                         FROM forum_boards fb
495                                 NATURAL JOIN forum_categories fc
496                                 LEFT OUTER JOIN (SELECT * FROM forum_access
497                                         WHERE fbid = $1 AND gid IN (SELECT groups($2))
498                                 ) fa USING (fbid)
499                         WHERE fb.fbid = $1
500                         GROUP BY fb.fbid,fb.board,fb.fcid,fc.category
501                 });
502         $board = $dbh->selectrow_hashref($boards,undef,$board,$c->stash->{UID});
503
504         $c->stash(board => $board);
505 }
506
507 sub previewPost : Private {
508         my ( $self, $c) = @_;
509         push @{$c->stash->{posts}}, {
510                 unread => 1,
511                 username => 'PREVIEW',
512                 message => parseMarkup(html_escape $c->req->param('message')),
513         };
514         $c->stash(previewMessage => html_escape $c->req->param('message'));
515 }
516
517 sub insertPost : Private {
518         my ( $self, $c, $thread ) = @_;
519         my $dbh = $c->model;
520
521         my $insert = $dbh->prepare(q{INSERT INTO forum_posts (ftid,message,uid)
522                 VALUES($1,$2,$3)});
523         $insert->execute($thread,html_escape($c->req->param('message')),$c->stash->{UID});
524 }
525
526 sub listModeratorBoards : Private {
527         my ( $self, $c, $fbid ) = @_;
528         my $dbh = $c->model;
529
530         my $categories = $dbh->prepare(q{SELECT fcid,category FROM forum_categories ORDER BY fcid});
531         my $boards = $dbh->prepare(q{SELECT fb.fbid,fb.board, bool_or(fa.post) AS post
532                 FROM forum_boards fb NATURAL JOIN forum_access fa
533                 WHERE fb.fcid = $1
534                         AND gid IN (SELECT groups($2))
535                         AND moderate
536                 GROUP BY fb.fbid,fb.board
537                 ORDER BY fb.fbid
538                 });
539         $categories->execute;
540         my @categories;
541         while (my $category = $categories->fetchrow_hashref){
542                 $boards->execute($category->{fcid},$c->stash->{UID});
543
544                 my @boards;
545                 while (my $b = $boards->fetchrow_hashref){
546                         next if ($b->{fbid} == $fbid);
547                         push @boards,$b;
548                 }
549                 $category->{boards} = \@boards;
550                 push @categories,$category if @boards;
551         }
552         $c->stash(categories => \@categories);
553 }
554
555 =head1 AUTHOR
556
557 Michael Andreen (harv@ruin.nu)
558
559 =head1 LICENSE
560
561 GPL 2.0, or later.
562
563 =cut
564
565 1;