X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=lib%2FNDWeb%2FController%2FForum.pm;h=bf7dc3ca8f11b76788ef52aa4cc8bbc2b4e72582;hb=0fd1ff08781f7be2e32d33a4130de91151f5d461;hp=0a27acc7547ae88e11d29a4d8685e939e70f447e;hpb=11149303e8c0c031f5e08c5792930b490ba4a30b;p=ndwebbie.git

diff --git a/lib/NDWeb/Controller/Forum.pm b/lib/NDWeb/Controller/Forum.pm
index 0a27acc..bf7dc3c 100644
--- a/lib/NDWeb/Controller/Forum.pm
+++ b/lib/NDWeb/Controller/Forum.pm
@@ -26,18 +26,22 @@ sub index :Path :Args(0) {
 	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)))
-			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});
 
@@ -57,21 +61,24 @@ sub allUnread : Local {
 	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)))
-		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 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});
@@ -127,9 +134,10 @@ sub search : Local {
 				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 {
@@ -156,60 +164,67 @@ sub board : Local {
 
 	$c->forward('findBoard');
 	$board = $c->stash->{board};
+	if ( !defined $board->{fbid}){
+		$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
-		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;
 	while (my $thread = $threads->fetchrow_hashref){
 		push @threads,$thread;
 	}
-	$c->stash(threads => \@threads);
 
-	if ($board->{moderate}){
-		my $categories = $dbh->prepare(q{SELECT fcid,category FROM forum_categories ORDER BY fcid});
-		my $boards = $dbh->prepare(q{SELECT fb.fbid,fb.board, bool_or(fa.post) AS post
-			FROM forum_boards fb NATURAL JOIN forum_access fa
-			WHERE fb.fcid = $1 AND
-				gid IN (SELECT groups($2))
-			GROUP BY fb.fbid,fb.board
-			ORDER BY fb.fbid
-		});
-		$categories->execute;
-		my @categories;
-		while (my $category = $categories->fetchrow_hashref){
-			$boards->execute($category->{fcid},$c->stash->{UID});
-
-			my @boards;
-			while (my $b = $boards->fetchrow_hashref){
-				next if (not $b->{post} or $b->{fbid} == $board->{fbid});
-				push @boards,$b;
-			}
-			$category->{boards} = \@boards;
-			push @categories,$category if @boards;
-		}
-		$c->stash(categories => \@categories);
+	if ( !(defined $board->{post}) && @threads == 0){
+		$c->acl_access_denied('test',$c->action,'No access to board')
 	}
-}
+	$c->stash(threads => \@threads);
+
+	$c->stash(title => "$board->{board} ($board->{category})");
 
+	$c->forward('listModeratorBoards', [$board->{fbid}]) if $board->{moderate};
+	
+}
 
 sub thread : Local {
 	my ( $self, $c, $thread ) = @_;
 	my $dbh = $c->model;
 
 	$c->forward('findThread');
-	$c->forward('findPosts') if $c->stash->{thread};
+	$thread = $c->stash->{thread};
+	unless ($thread){
+		$c->stash(template => 'default.tt2');
+		$c->res->status(404);
+		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->{ftid});
+	$c->stash(access => $query->fetchall_arrayref({}) );
+	$c->stash(title => $thread->{subject}
+		. " ($thread->{category} - $thread->{board})");
+	$c->forward('findPosts');
 	$c->forward('markThreadAsRead') if $c->user_exists;
+	if ($c->stash->{thread}->{moderate}) {
+		$c->forward('findUsers');
+		$c->forward('listModeratorBoards', [$c->stash->{thread}->{fbid}]);
+	}
 }
 
 sub findPosts :Private {
@@ -217,7 +232,7 @@ sub findPosts :Private {
 	my $dbh = $c->model;
 
 	my $posts = $dbh->prepare(q{
-		SELECT 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)
@@ -262,7 +277,7 @@ sub markBoardAsRead : Local {
 		$c->forward('markThreadAsRead',[$thread->{ftid}]);
 	}
 	$dbh->commit;
-	$c->res->redirect($c->req->referer);
+	$c->forward('/redirect');
 }
 
 sub markThreadAsRead : Private {
@@ -279,6 +294,36 @@ sub markThreadAsRead : Private {
 	}
 }
 
+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;
@@ -375,16 +420,108 @@ sub setSticky : Local {
 	$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 privmsg : Local {
+	my ( $self, $c, $uid ) = @_;
+
+	$uid ||= 0;
+	$c->stash(uid => $uid);
+
+	$c->forward('findUsers');
+}
+
+sub postprivmsg : Local {
+	my ( $self, $c ) = @_;
+	my $dbh = $c->model;
+
+	$dbh->begin_work;
+	$c->forward('insertThread',[-1999]);
+
+	$c->req->parameters->{uid} = [$c->req->parameters->{uid}]
+		unless ref $c->req->parameters->{uid} eq 'ARRAY';
+	push @{$c->req->parameters->{uid}}, $c->user->id;
+	$c->forward('addaccess',[$c->stash->{thread}]);
+
+	$c->forward('addPost',[$c->stash->{thread}]);
+	$dbh->commit;
+}
+
+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 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 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))
 		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});
@@ -396,11 +533,12 @@ sub findBoard : Private {
 	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});
@@ -427,6 +565,35 @@ sub insertPost : Private {
 	$insert->execute($thread,html_escape($c->req->param('message')),$c->stash->{UID});
 }
 
+sub listModeratorBoards : Private {
+	my ( $self, $c, $fbid ) = @_;
+	my $dbh = $c->model;
+
+	my $categories = $dbh->prepare(q{SELECT fcid,category FROM forum_categories ORDER BY fcid});
+	my $boards = $dbh->prepare(q{SELECT fb.fbid,fb.board, bool_or(fa.post) AS post
+		FROM forum_boards fb NATURAL JOIN forum_access fa
+		WHERE fb.fcid = $1
+			AND gid IN (SELECT groups($2))
+			AND moderate
+		GROUP BY fb.fbid,fb.board
+		ORDER BY fb.fbid
+		});
+	$categories->execute;
+	my @categories;
+	while (my $category = $categories->fetchrow_hashref){
+		$boards->execute($category->{fcid},$c->stash->{UID});
+
+		my @boards;
+		while (my $b = $boards->fetchrow_hashref){
+			next if ($b->{fbid} == $fbid);
+			push @boards,$b;
+		}
+		$category->{boards} = \@boards;
+		push @categories,$category if @boards;
+	}
+	$c->stash(categories => \@categories);
+}
+
 =head1 AUTHOR
 
 Michael Andreen (harv@ruin.nu)