From 55ed28a8dcc7fe5f22fe5259c5a518233c2b8bdc Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 29 Jul 2020 12:02:00 +0200 Subject: Reports: Keep track of unseen, new and last updated reports --- data/style.css | 2 +- lib/VNWeb/HTML.pm | 10 +++++++++- lib/VNWeb/Misc/Reports.pm | 6 ++++++ sql/perms.sql | 4 ++-- sql/schema.sql | 3 ++- sql/tableattrs.sql | 3 ++- util/updates/2020-07-29-reports-last-seen.sql | 5 +++++ 7 files changed, 27 insertions(+), 6 deletions(-) create mode 100644 util/updates/2020-07-29-reports-last-seen.sql diff --git a/data/style.css b/data/style.css index c17243d5..44284234 100644 --- a/data/style.css +++ b/data/style.css @@ -111,7 +111,7 @@ b.done { font-weight: normal; color: $statok$ } b.todo { font-weight: normal; color: $statnok$ } b.neutral { font-weight: normal } p.center { text-align: center; } -.standout { color: $standout$ } +.standout { color: $standout$!important } b.future, b.standout { font-weight: normal; color: $standout$; } .clearfloat { clear: both; height: 0; } diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm index 91c13526..159b1d4b 100644 --- a/lib/VNWeb/HTML.pm +++ b/lib/VNWeb/HTML.pm @@ -247,7 +247,15 @@ sub _menu_ { a_ href => '/s/new', 'Add Staff'; br_; } if(auth->isMod) { - a_ href => '/report/list?status=new', sprintf 'Reports (%d)', tuwf->dbVali('SELECT count(*) FROM reports WHERE status = \'new\''); br_; + my $stats = tuwf->dbRowi("SELECT + (SELECT count(*) FROM reports WHERE status = 'new') as new, + (SELECT count(*) FROM reports WHERE status = 'new' AND date > (SELECT last_reports FROM users WHERE id =", \auth->uid, ")) AS unseen, + (SELECT count(*) FROM reports WHERE lastmod > (SELECT last_reports FROM users WHERE id =", \auth->uid, ")) AS upd + "); + a_ $stats->{unseen} ? (class => 'standout') : (), href => '/report/list?status=new', sprintf 'Reports %d/%d', $stats->{unseen}, $stats->{new}; + b_ class => 'grayedout', ' | '; + a_ href => '/report/list?s=lastmod', sprintf '%d upd', $stats->{upd}; + br_; } br_; form_ action => "$uid/logout", method => 'post', sub { diff --git a/lib/VNWeb/Misc/Reports.pm b/lib/VNWeb/Misc/Reports.pm index 3514c4d3..de677c3d 100644 --- a/lib/VNWeb/Misc/Reports.pm +++ b/lib/VNWeb/Misc/Reports.pm @@ -151,6 +151,12 @@ TUWF::get qr{/report/list}, sub { ); enrich_object @$lst; + tuwf->dbExeci( + 'UPDATE users SET last_reports = NOW() + WHERE (last_reports IS NULL OR EXISTS(SELECT 1 FROM reports WHERE lastmod > last_reports OR date > last_reports)) + AND id =', \auth->uid + ); + my sub url { '?'.query_encode %$opt, @_ } framework_ title => 'Reports', sub { diff --git a/sql/perms.sql b/sql/perms.sql index 452141fc..a98690a6 100644 --- a/sql/perms.sql +++ b/sql/perms.sql @@ -70,7 +70,7 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_site; -- users table is special; The 'perm_usermod', 'passwd' and 'mail' columns are -- protected and can only be accessed through the user_* functions. -GRANT SELECT ( id, username, registered, ip, ign_votes, email_confirmed +GRANT SELECT ( id, username, registered, ip, ign_votes, email_confirmed, last_reports , perm_board, perm_boardmod, perm_dbmod, perm_edit, perm_imgvote, perm_tag, perm_tagmod, perm_usermod, perm_imgmod , skin, customcss, show_nsfw, notify_dbedit, notify_announce , tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, max_sexual, max_violence @@ -79,7 +79,7 @@ GRANT SELECT ( id, username, registered, ip, ign_votes, email_confirmed , ulist_votes, ulist_vnlist, ulist_wish , c_vns, c_wish, c_votes, c_changes, c_imgvotes, c_tags), INSERT ( username, mail, ip), - UPDATE ( username, ign_votes, email_confirmed + UPDATE ( username, ign_votes, email_confirmed, last_reports , perm_board, perm_boardmod, perm_dbmod, perm_edit, perm_imgvote, perm_tag, perm_tagmod, perm_imgmod , skin, customcss, show_nsfw, notify_dbedit, notify_announce , tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, max_sexual, max_violence diff --git a/sql/schema.sql b/sql/schema.sql index c7effd9c..0ed88425 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -862,7 +862,8 @@ CREATE TABLE users ( perm_usermod boolean NOT NULL DEFAULT false, perm_imgmod boolean NOT NULL DEFAULT false, max_sexual smallint NOT NULL DEFAULT 0, - max_violence smallint NOT NULL DEFAULT 0 + max_violence smallint NOT NULL DEFAULT 0, + last_reports timestamptz -- For mods: Most recent activity seen on the reports listing ); -- vn diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql index 919d05d7..3ee1299f 100644 --- a/sql/tableattrs.sql +++ b/sql/tableattrs.sql @@ -117,7 +117,8 @@ CREATE INDEX image_votes_id ON image_votes (id); CREATE INDEX notifications_uid ON notifications (uid); CREATE INDEX releases_producers_pid ON releases_producers (pid); CREATE INDEX releases_vn_vid ON releases_vn (vid); -CREATE INDEX reports_status ON reports (status,id); +CREATE INDEX reports_new ON reports (date) WHERE status = 'new'; +CREATE INDEX reports_lastmod ON reports (lastmod); CREATE INDEX staff_alias_id ON staff_alias (id); CREATE UNIQUE INDEX tags_vn_pkey ON tags_vn (tag,vid,uid); CREATE INDEX tags_vn_date ON tags_vn (date); diff --git a/util/updates/2020-07-29-reports-last-seen.sql b/util/updates/2020-07-29-reports-last-seen.sql new file mode 100644 index 00000000..e38c5f54 --- /dev/null +++ b/util/updates/2020-07-29-reports-last-seen.sql @@ -0,0 +1,5 @@ +ALTER TABLE users ADD COLUMN last_reports timestamptz; +DROP INDEX reports_status; +CREATE INDEX reports_new ON reports (date) WHERE status = 'new'; +CREATE INDEX reports_lastmod ON reports (lastmod); +\i sql/perms.sql -- cgit v1.2.3