X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=lib%2FNDWeb%2FController%2FForum.pm;h=f06848de982c5e8da0391b5c27ec3bf5ae9733ab;hb=48df392b0817ea813443309eecc5949550095155;hp=a9a1f0d5dc98143bb3d37f8eea870ce947a10307;hpb=acd22833d74158d4b5320578987f372464bef7b7;p=ndwebbie.git

diff --git a/lib/NDWeb/Controller/Forum.pm b/lib/NDWeb/Controller/Forum.pm
index a9a1f0d..f06848d 100644
--- a/lib/NDWeb/Controller/Forum.pm
+++ b/lib/NDWeb/Controller/Forum.pm
@@ -33,7 +33,7 @@ SELECT fcid,category,fb.fbid,fb.board
 	,date_trunc('seconds',max(ft.mtime)::timestamp ) AS last_post
 FROM forum_categories fc
 	JOIN forum_boards fb USING (fcid)
-	JOIN forum_threads ft USING (fbid)
+	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
@@ -73,7 +73,8 @@ FROM forum_categories fc
 	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)
+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))))
@@ -232,7 +233,7 @@ sub findPosts :Private {
 	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)
@@ -294,6 +295,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;
@@ -472,7 +503,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({}) );
@@ -487,8 +518,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))