summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-07-29 12:02:00 +0200
committerYorhel <git@yorhel.nl>2020-07-29 12:03:28 +0200
commit55ed28a8dcc7fe5f22fe5259c5a518233c2b8bdc (patch)
tree4c6ec1c15dbdbb88a11de2af1240b55a33553250
parent8bc45a4b082438595f63dcb876f1e2215a341caf (diff)
Reports: Keep track of unseen, new and last updated reports
-rw-r--r--data/style.css2
-rw-r--r--lib/VNWeb/HTML.pm10
-rw-r--r--lib/VNWeb/Misc/Reports.pm6
-rw-r--r--sql/perms.sql4
-rw-r--r--sql/schema.sql3
-rw-r--r--sql/tableattrs.sql3
-rw-r--r--util/updates/2020-07-29-reports-last-seen.sql5
7 files changed, 27 insertions, 6 deletions
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