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
});
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)))
+ 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
JOIN forum_threads ft USING (fbid)
JOIN forum_posts fp USING (ftid)
JOIN users u ON fp.uid = u.uid
- WHERE fb.fbid IN (SELECT fbid FROM forum_access
- WHERE gid IN (SELECT groups($1)))
- AND fp.textsearch @@@ to_tsquery($2)
+ WHERE (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)
+ ) AND fp.textsearch @@@ to_tsquery($2)
ORDER BY rank DESC
});
eval {
,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 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
+ 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 $dbh = $c->model;
$c->forward('findThread');
- $thread = $c->stash->{thread};
+ unless ($c->stash->{thread}){
+ $c->stash(template => 'access_denied.tt2');
+ return;
+ }
+ my $query = $dbh->prepare(q{SELECT uid,username FROM users u
+ JOIN forum_priv_access fta USING (uid) WHERE fta.ftid = $1});
+ $query->execute($thread);
+ $c->stash(access => $query->fetchall_arrayref({}) );
+ $c->forward('findUsers') if $c->stash->{thread}->{moderate};
+ $c->forward('findPosts');
+ $c->forward('markThreadAsRead') if $c->user_exists;
+}
+
+sub findPosts :Private {
+ my ( $self, $c, $thread ) = @_;
+ my $dbh = $c->model;
my $posts = $dbh->prepare(q{
- SELECT u.username,date_trunc('seconds',fp.time::timestamp) AS time
+ SELECT 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)
WHERE ft.ftid = $1
ORDER BY fp.time ASC
});
- $posts->execute($c->stash->{thread}->{ftid},$c->stash->{UID});
+ $posts->execute($thread,$c->stash->{UID});
my @posts;
while (my $post = $posts->fetchrow_hashref){
}
$c->stash(posts => \@posts);
- $c->forward('markThreadAsRead') if $c->user_exists;
}
$c->res->redirect($c->uri_for('thread',$thread));
}
+sub postthreadaccess : Local {
+ my ( $self, $c, $thread) = @_;
+ my $dbh = $c->model;
+
+ $c->forward('findThread');
+ $dbh->begin_work;
+ unless ($c->stash->{thread}->{moderate}){
+ $c->acl_access_denied('test',$c->action,'No moderator access to board.')
+ }
+ if ($c->req->param('access')){
+ $c->req->parameters->{access} = [$c->req->parameters->{access}]
+ unless ref $c->req->parameters->{access} eq 'ARRAY';
+ my $query = $dbh->prepare(q{DELETE From forum_priv_access
+ WHERE ftid = $1 AND uid = ANY ($2)});
+ $query->execute($thread,$c->req->parameters->{access});
+ $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
+ VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
+ }, undef, $c->user->id
+ ,"Removed access on thread $thread for : @{$c->req->parameters->{access}}");
+ }
+ if ($c->req->param('uid')){
+ $c->forward('addaccess');
+ }
+ $dbh->commit;
+ $c->res->redirect($c->uri_for('thread',$thread));
+}
+
+sub removeownthreadaccess : Local {
+ my ( $self, $c, $thread) = @_;
+ my $dbh = $c->model;
+ $dbh->do(q{DELETE FROM forum_priv_access WHERE uid = $1 AND ftid = $2}
+ ,undef,$c->user->id,$thread);
+ $c->res->redirect($c->uri_for('allUnread'));
+}
+
+sub addaccess : Private {
+ my ( $self, $c, $thread) = @_;
+ my $dbh = $c->model;
+
+ $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
+ unless ref $c->req->parameters->{uid} eq 'ARRAY';
+ my $query = $dbh->prepare(q{INSERT INTO forum_priv_access (ftid,uid)
+ (SELECT $1,uid FROM users u WHERE uid = ANY ($2) AND NOT uid
+ IN (SELECT uid FROM forum_priv_access WHERE ftid = $1))});
+ $query->execute($thread,$c->req->parameters->{uid});
+ $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message)
+ VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2)
+ }, undef, $c->user->id
+ ,"Gave access on thread $thread to : @{$c->req->parameters->{uid}}");
+}
+
+sub findUsers : Private {
+ my ( $self, $c ) = @_;
+ my $dbh = $c->model;
+
+ my $query = $dbh->prepare(q{SELECT uid,username FROM users
+ WHERE uid > 0 AND uid IN (SELECT uid FROM groupmembers)
+ ORDER BY LOWER(username)});
+ $query->execute;
+
+ $c->stash(users => $query->fetchall_arrayref({}) );
+}
+
sub findThread : Private {
my ( $self, $c, $thread ) = @_;
my $dbh = $c->model;
- my $findThread = $dbh->prepare(q{SELECT ft.ftid,ft.subject, bool_or(fa.post) AS post
- , bool_or(fa.moderate) AS moderate,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
+ my $findThread = $dbh->prepare(q{SELECT ft.ftid,ft.subject
+ ,COALESCE(bool_or(fa.post),true) AS post, bool_or(fa.moderate) AS moderate
+ ,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
FROM forum_boards fb
- NATURAL JOIN forum_access fa
NATURAL JOIN forum_threads ft
NATURAL JOIN forum_categories fc
- WHERE ft.ftid = $1 AND gid IN (SELECT groups($2))
+ LEFT OUTER JOIN (SELECT * FROM forum_access
+ WHERE gid IN (SELECT groups($2))
+ ) 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))
GROUP BY ft.ftid,ft.subject,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category
});
$thread = $dbh->selectrow_hashref($findThread,undef,$thread,$c->stash->{UID});
my $dbh = $c->model;
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
- FROM forum_boards fb
- NATURAL JOIN forum_access fa
+ FROM forum_boards fb
NATURAL JOIN forum_categories fc
- WHERE fb.fbid = $1 AND
- gid IN (SELECT groups($2))
+ LEFT OUTER JOIN (SELECT * FROM forum_access
+ WHERE fbid = $1 AND gid IN (SELECT groups($2))
+ ) fa USING (fbid)
+ WHERE fb.fbid = $1
GROUP BY fb.fbid,fb.board,fb.fcid,fc.category
});
$board = $dbh->selectrow_hashref($boards,undef,$board,$c->stash->{UID});
projects / ndwebbie.git / blobdiff