my ( $self, $c ) = @_;
my $dbh = $c->model;
- my $boards = $dbh->prepare(q{SELECT fcid,category,fb.fbid,fb.board
- ,count(NULLIF(COALESCE(fp.fpid::BOOLEAN,FALSE)
- AND COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread
- ,date_trunc('seconds',max(fp.time)::timestamp) as last_post
- FROM forum_categories
- JOIN forum_boards fb USING (fcid)
- LEFT OUTER JOIN forum_threads ft USING (fbid)
- LEFT OUTER JOIN forum_posts fp USING (ftid)
- LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv USING (ftid)
- WHERE EXISTS (SELECT fbid FROM forum_access WHERE fbid = fb.fbid AND gid IN (SELECT groups($1)))
- OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1)
- GROUP BY fcid,category,fb.fbid, fb.board
- ORDER BY fcid,fb.fbid
+ my $boards = $dbh->prepare(q{
+SELECT fcid,category,fb.fbid,fb.board
+ ,SUM((SELECT count(*) FROM forum_posts WHERE ftid = ft.ftid
+ AND COALESCE(time > ftv.time,TRUE))) AS unread
+ ,date_trunc('seconds',max(ft.mtime)::timestamp ) AS last_post
+FROM forum_categories fc
+ JOIN forum_boards fb USING (fcid)
+ LEFT OUTER JOIN forum_threads ft USING (fbid)
+ LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1)
+ ftv USING (ftid)
+WHERE fbid IN (SELECT fbid FROM forum_access
+ WHERE gid IN (SELECT groups($1)))
+ OR ftid IN (SELECT ftid FROM forum_priv_access
+ WHERE uid = $1)
+GROUP BY fcid,category,fb.fbid, fb.board
+ORDER BY fcid,fb.fbid
});
$boards->execute($c->stash->{UID});
my ( $self, $c ) = @_;
my $dbh = $c->model;
- my $threads = $dbh->prepare(q{SELECT fcid,category,fbid,board,ft.ftid,u.username,ft.subject,
- count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts,
- date_trunc('seconds',max(fp.time)::timestamp) as last_post,
- min(fp.time)::date as posting_date, ft.sticky
- FROM forum_categories fc
- JOIN forum_boards fb USING (fcid)
- JOIN forum_threads ft USING (fbid)
- JOIN forum_posts fp USING (ftid)
- JOIN users u ON u.uid = ft.uid
- LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid
- WHERE (fbid > 0 AND
- fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1)))
- ) OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1)
- GROUP BY fcid,category,fbid,board,ft.ftid, ft.subject,ft.sticky,u.username
- HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
- ORDER BY fcid,fbid,sticky DESC,last_post DESC
+ my $threads = $dbh->prepare(q{
+SELECT fcid,category,fbid,board,ft.ftid,u.username,ft.subject,ft.sticky
+ ,(SELECT count(*) FROM forum_posts WHERE ftid = ft.ftid
+ AND COALESCE(time > ftv.time,TRUE)) AS unread
+ ,ft.posts,date_trunc('seconds',ft.mtime::timestamp) as last_post
+ ,ft.ctime::DATE as posting_date
+FROM forum_categories fc
+ JOIN forum_boards fb USING (fcid)
+ JOIN forum_threads ft USING (fbid)
+ JOIN users u ON u.uid = ft.uid
+ LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1)
+ ftv ON ftv.ftid = ft.ftid
+WHERE ft.mtime > NOW() - '50 days'::interval
+ AND COALESCE(ft.mtime > ftv.time,TRUE)
+ AND ft.ftid IN (SELECT ftid FROM forum_posts WHERE ftid = ft.ftid)
+ AND ((fbid > 0 AND
+ fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1))))
+ OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $1))
+ORDER BY fcid,fbid,sticky DESC,last_post DESC
});
$threads->execute($c->stash->{UID});
$c->detach('/default');
}
- my $threads = $dbh->prepare(q{SELECT ft.ftid,u.username,ft.subject
- ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts
- ,date_trunc('seconds',max(fp.time)::timestamp) as last_post
- ,min(fp.time)::date as posting_date, ft.sticky
- FROM forum_threads ft
- JOIN forum_posts fp USING (ftid)
- JOIN users u ON u.uid = ft.uid
- LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
- WHERE ft.fbid = $1 AND (
- ft.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($2)))
- OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2)
- )
- GROUP BY ft.ftid, ft.subject,ft.sticky,u.username
- ORDER BY sticky DESC,last_post DESC
+ my $threads = $dbh->prepare(q{
+SELECT ft.ftid,u.username,ft.subject,ft.posts, ft.sticky
+ ,(SELECT count(*) FROM forum_posts WHERE ftid = ft.ftid
+ AND COALESCE(time > ftv.time,TRUE)) AS unread
+ ,ft.ctime::DATE as posting_date
+ ,date_trunc('seconds',ft.mtime::timestamp) as last_post
+FROM forum_threads ft
+ JOIN users u USING(uid)
+ LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2)
+ ftv ON ftv.ftid = ft.ftid
+WHERE ft.posts > 0 AND ft.fbid = $1 AND (
+ ft.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($2)))
+ OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2)
+ )
+GROUP BY ft.ftid, ft.subject,ft.sticky,u.username,ft.ctime,ft.mtime,ft.posts,ftv.time
+ORDER BY sticky DESC,last_post DESC
});
$threads->execute($board->{fbid},$c->stash->{UID});
my @threads;
my $dbh = $c->model;
my $posts = $dbh->prepare(q{
- SELECT u.uid,u.username,date_trunc('seconds',fp.time::timestamp) AS time
+ SELECT fpid,u.uid,u.username,date_trunc('seconds',fp.time::timestamp) AS time
,fp.message,COALESCE(fp.time > ftv.time,TRUE) AS unread
FROM forum_threads ft
JOIN forum_posts fp USING (ftid)
}
}
+sub markThreadAsUnread : Local {
+ my ( $self, $c, $thread ) = @_;
+ my $dbh = $c->model;
+
+ my ($fbid) = $dbh->selectrow_array(q{
+SELECT fbid FROM forum_threads WHERE ftid = $1
+ },undef, $thread);
+
+ $dbh->do(q{
+DELETE FROM forum_thread_visits WHERE uid = $1 AND ftid = $2
+ }, undef, $c->user->id, $thread);
+ $c->res->redirect($c->uri_for('board',$fbid));
+}
+
+sub markPostAsUnread : Local {
+ my ( $self, $c, $post ) = @_;
+ my $dbh = $c->model;
+
+ my ($fbid) = $dbh->selectrow_array(q{
+SELECT fbid FROM forum_threads JOIN forum_posts USING (ftid) WHERE fpid = $1
+ },undef, $post);
+
+ $dbh->do(q{
+UPDATE forum_thread_visits ftv SET time = (fp.time - interval '1 second')
+FROM forum_posts fp
+WHERE ftv.uid = $1 AND fp.fpid = $2 AND fp.ftid = ftv.ftid
+ }, undef, $c->user->id, $post);
+ $c->res->redirect($c->uri_for('board',$fbid));
+}
+
sub moveThreads : Local {
my ( $self, $c, $board ) = @_;
my $dbh = $c->model;
- $c->forward('findBoard',[$c->req->param('board')]);
+ my $b = $c->req->param('board');
+ $c->forward('findBoard',[$b]);
my $toboard = $c->stash->{board};
unless ($toboard->{moderate}){
$c->acl_access_denied('test',$c->action,'No moderator access for target board.')
my ( $self, $c, $board ) = @_;
my $dbh = $c->model;
+ my $subject = html_escape($c->req->param('subject'));
my $insert = $dbh->prepare(q{INSERT INTO forum_threads (ftid,fbid,subject,uid)
VALUES(DEFAULT,$1,$2,$3) RETURNING (ftid);
});
- $insert->execute($board,html_escape($c->req->param('subject')),$c->stash->{UID});
+ $insert->execute($board,$subject,$c->stash->{UID});
$c->stash(thread => $insert->fetchrow);
$insert->finish;
}
my $query = $dbh->prepare(q{SELECT uid,username FROM users
WHERE uid > 0 AND uid IN (SELECT uid FROM groupmembers)
- ORDER BY LOWER(username)});
+ ORDER BY username});
$query->execute;
$c->stash(users => $query->fetchall_arrayref({}) );
FROM forum_boards fb
NATURAL JOIN forum_threads ft
NATURAL JOIN forum_categories fc
- LEFT OUTER JOIN (SELECT * FROM forum_access
- WHERE gid IN (SELECT groups($2))
+ LEFT OUTER JOIN (SELECT fa.* FROM forum_access fa
+ JOIN (SELECT groups($2) AS gid) g USING (gid)
) fa USING (fbid)
WHERE ft.ftid = $1 AND (fa.post IS NOT NULL
OR ft.ftid IN (SELECT ftid FROM forum_priv_access WHERE uid = $2))
sub previewPost : Private {
my ( $self, $c) = @_;
+ my $message = html_escape $c->req->param('message');
push @{$c->stash->{posts}}, {
unread => 1,
username => 'PREVIEW',
- message => parseMarkup(html_escape $c->req->param('message')),
+ message => parseMarkup($message),
};
- $c->stash(previewMessage => html_escape $c->req->param('message'));
+ $c->stash(previewMessage => $message);
}
sub insertPost : Private {
my ( $self, $c, $thread ) = @_;
my $dbh = $c->model;
+ my $message = html_escape($c->req->param('message'));
my $insert = $dbh->prepare(q{INSERT INTO forum_posts (ftid,message,uid)
VALUES($1,$2,$3)});
- $insert->execute($thread,html_escape($c->req->param('message')),$c->stash->{UID});
+ $insert->execute($thread,$message,$c->stash->{UID});
}
sub listModeratorBoards : Private {