trunc to seconds for thread listing

[ndwebbie.git] / ND / Web / Pages / Forum.pm

diff --git a/ND/Web/Pages/Forum.pm b/ND/Web/Pages/Forum.pm
index 3e8f30e01a3f7ccd2046764c2663e2946216484b..4aa72fec7ece06206f5e95516bbc1045c8f6c790 100644 (file)
--- a/ND/Web/Pages/Forum.pm
+++ b/ND/Web/Pages/Forum.pm
@@ -19,8 +19,7 @@
 
 package ND::Web::Pages::Forum;
 use strict;
-use warnings FATAL => 'all';
-no warnings 'uninitialized';
+use warnings;
 use ND::Web::Forum;
 use CGI qw/:standard/;
 use ND::Web::Include;
@@ -29,7 +28,7 @@ $ND::PAGES{forum} = {parse => \&parse, process => \&process, render=> \&render};
 
 sub parse {
        my ($uri) = @_;
-       if ($uri =~ m{^/.*/allUnread$}){
+       if ($uri =~ m{^/.*/allUnread}){
                param('allUnread',1);
        }
 }
@@ -43,6 +42,8 @@ sub render {
 
        $ND::TEMPLATE->param(TITLE => 'Forum');
 
+       $DBH->do(q{UPDATE users SET last_forum_visit = NOW() WHERE uid = $1},undef,$ND::UID) or $ND::ERROR .= p($DBH->errstr);
+
        my $board;
        if(param('b')){
                my $boards = $DBH->prepare(q{SELECT fb.fbid AS id,fb.board, bool_or(fa.post) AS post
@@ -52,6 +53,22 @@ sub render {
                        GROUP BY fb.fbid,fb.board});
                $board = $DBH->selectrow_hashref($boards,undef,param('b'),$ND::UID) or $ND::ERROR .= p($DBH->errstr);
        }
+       if (param('markAsRead')){
+               my $threads = $DBH->prepare(q{SELECT ft.ftid AS id,ft.subject,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts, max(fp.time)::timestamp as last_post
+               FROM forum_threads ft JOIN forum_posts fp USING (ftid) LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid
+               WHERE ((ft.fbid IS NULL AND $1 IS NULL) OR ft.fbid = $1) AND fp.time <= $3
+               GROUP BY ft.ftid, ft.subject
+               HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1
+               });
+
+               $threads->bind_param('$1',$board->{id},{TYPE => DBI::SQL_INTEGER }) or $ND::ERROR .= p($DBH->errstr);
+               $threads->bind_param('$2',$ND::UID,{TYPE => DBI::SQL_INTEGER }) or $ND::ERROR .= p($DBH->errstr);
+               $threads->bind_param('$3',param('markAsRead')) or $ND::ERROR .= p($DBH->errstr);
+               $threads->execute or $ND::ERROR .= p($DBH->errstr);
+               while (my $thread = $threads->fetchrow_hashref){
+                       markThreadAsRead $thread->{id};
+               }
+       }
 
        my $thread;
        my $findThread = $DBH->prepare(q{SELECT ft.ftid AS id,ft.subject, bool_or(fa.post) AS post
@@ -63,7 +80,7 @@ sub render {
                $thread = $DBH->selectrow_hashref($findThread,undef,param('t'),$ND::UID) or $ND::ERROR .= p($DBH->errstr);
        }
 
-       if (defined param('cmd') && param('cmd') eq 'forumpost'){
+       if (defined param('cmd') && param('cmd') eq 'Submit'){
                $DBH->begin_work;
                if ($board && $board->{post}){
                        $thread = addForumThread $DBH,$board,$ND::UID,param('subject');
@@ -75,7 +92,7 @@ sub render {
        }
 
        my $categories = $DBH->prepare(q{SELECT fcid AS id,category FROM forum_categories ORDER BY fcid});
-       my $threads = $DBH->prepare(q{SELECT ft.ftid AS id,ft.subject,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts, max(fp.time)::timestamp as last_post
+       my $threads = $DBH->prepare(q{SELECT ft.ftid AS id,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
                FROM forum_threads ft JOIN forum_posts fp USING (ftid) 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
@@ -85,23 +102,11 @@ sub render {
        if ($thread){ #Display the thread
                $BODY->param(Thread => viewForumThread $thread);
 
-       }elsif($board){ #List threads in this board
-               $BODY->param(Board => $board->{board});
-               $BODY->param(Post => $board->{post});
-               $BODY->param(Id => $board->{id});
-               $threads->execute($board->{id},$ND::UID,0) or $ND::ERROR .= p($DBH->errstr);
-               my $i = 0;
-               my @threads;
-               while (my $thread = $threads->fetchrow_hashref){
-                       $i++;
-                       $thread->{Odd} = $i % 2;
-                       push @threads,$thread;
-               }
-               $BODY->param(Threads => \@threads);
-
        }elsif(defined param('allUnread')){ #List threads in this board
                $BODY->param(AllUnread => 1);
                $BODY->param(Id => $board->{id});
+               my ($time) = $DBH->selectrow_array('SELECT now()::timestamp',undef);
+               $BODY->param(Date => $time);
                $categories->execute or $ND::ERROR .= p($DBH->errstr);
                my @categories;
                my $boards = $DBH->prepare(q{SELECT fb.fbid AS id,fb.board, bool_or(fa.post) AS post
@@ -133,6 +138,22 @@ sub render {
                }
                $BODY->param(Categories => \@categories);
 
+       }elsif($board){ #List threads in this board
+               $BODY->param(Board => $board->{board});
+               $BODY->param(Post => $board->{post});
+               $BODY->param(Id => $board->{id});
+               my ($time) = $DBH->selectrow_array('SELECT now()::timestamp',undef);
+               $BODY->param(Date => $time);
+               $threads->execute($board->{id},$ND::UID,0) or $ND::ERROR .= p($DBH->errstr);
+               my $i = 0;
+               my @threads;
+               while (my $thread = $threads->fetchrow_hashref){
+                       $i++;
+                       $thread->{Odd} = $i % 2;
+                       push @threads,$thread;
+               }
+               $BODY->param(Threads => \@threads);
+
        }else{ #List boards
                $BODY->param(Overview => 1);
                $categories->execute or $ND::ERROR .= p($DBH->errstr);