X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=lib%2FNDWeb%2FController%2FForum.pm;h=4cca534c3f7e2137cda1f3640dffed7d1edf2d7d;hb=23470c3481cbd9e7dfa3bfa5cf05bbb41d098cc6;hp=1a796246688d090a717e7046e02af5bb52c12e16;hpb=c66b1b9074d880fb13a825bc75c1bc842ec65f75;p=ndwebbie.git

diff --git a/lib/NDWeb/Controller/Forum.pm b/lib/NDWeb/Controller/Forum.pm
index 1a79624..4cca534 100644
--- a/lib/NDWeb/Controller/Forum.pm
+++ b/lib/NDWeb/Controller/Forum.pm
@@ -26,19 +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)))
-				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});
 
@@ -58,22 +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)))
-			) 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 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});
@@ -159,29 +164,40 @@ 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 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;
 	while (my $thread = $threads->fetchrow_hashref){
 		push @threads,$thread;
 	}
+
+	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};
 	
 }
@@ -191,14 +207,18 @@ sub thread : Local {
 	my $dbh = $c->model;
 
 	$c->forward('findThread');
-	unless ($c->stash->{thread}){
-		$c->stash(template => 'access_denied.tt2');
+	$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);
+	$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}) {
@@ -452,7 +472,7 @@ sub findUsers : Private {
 
 	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({}) );
@@ -467,8 +487,8 @@ sub findThread : Private {
 		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))