Ignore existing tag votes by users without perm_tag

Same idea as 00fbcdf96432efad85abdbc7392d493409a4c253. Not sure why it
hadn't occurred to me before, seems like such an obvious thing to do.

3 files changed, 7 insertions, 5 deletions

diff --git a/lib/VNWeb/Tags/Links.pm b/lib/VNWeb/Tags/Links.pm
index 6e7dc06b..e3294520 100644
--- a/lib/VNWeb/Tags/Links.pm
+++ b/lib/VNWeb/Tags/Links.pm
@@ -67,7 +67,7 @@ TUWF::get qr{/g/links}, sub {
 
     my $count = $filt && tuwf->dbVali('SELECT COUNT(*) FROM tags_vn tv WHERE', $where);
     my($lst, $np) = tuwf->dbPagei({ page => $opt->{p}, results => 50 }, '
-        SELECT tv.vid, tv.uid, tv.tag, tv.vote, tv.spoiler,', sql_totime('tv.date'), 'as date, tv.ignore, tv.notes, v.title,', sql_user(), ', t.name
+        SELECT tv.vid, tv.uid, tv.tag, tv.vote, tv.spoiler,', sql_totime('tv.date'), 'as date, tv.ignore OR (u.id IS NOT NULL AND NOT u.perm_tag) AS ignore, tv.notes, v.title,', sql_user(), ', t.name
           FROM tags_vn tv
           JOIN vn v ON v.id = tv.vid
           LEFT JOIN users u ON u.id = tv.uid
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index a8ffa88a..1b012cd4 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -256,12 +256,13 @@ sub infobox_anime_ {
 
 sub infobox_tags_ {
     my($v) = @_;
-    my $rating = 'avg(CASE WHEN tv.ignore THEN NULL ELSE tv.vote END)';
+    my $rating = 'avg(CASE WHEN tv.ignore OR (u.id IS NOT NULL AND NOT u.perm_tag) THEN NULL ELSE tv.vote END)';
     my $tags = tuwf->dbAlli("
         SELECT t.id, t.name, t.cat, count(*) as cnt, $rating as rating
-             , coalesce(avg(CASE WHEN tv.ignore THEN NULL ELSE tv.spoiler END), t.defaultspoil) as spoiler
+             , coalesce(avg(CASE WHEN tv.ignore OR (u.id IS NOT NULL AND NOT u.perm_tag) THEN NULL ELSE tv.spoiler END), t.defaultspoil) as spoiler
           FROM tags t
           JOIN tags_vn tv ON tv.tag = t.id
+          LEFT JOIN users u ON u.id = tv.uid
          WHERE t.state = 1+1 AND tv.vid =", \$v->{id}, "
          GROUP BY t.id, t.name, t.cat
         HAVING $rating > 0
diff --git a/lib/VNWeb/VN/Tagmod.pm b/lib/VNWeb/VN/Tagmod.pm
index 790d23a4..4630c2f6 100644
--- a/lib/VNWeb/VN/Tagmod.pm
+++ b/lib/VNWeb/VN/Tagmod.pm
@@ -73,11 +73,12 @@ TUWF::get qr{/$RE{vid}/tagmod}, sub {
 
     my $tags = tuwf->dbAlli('
         SELECT t.id, t.name, t.cat, count(*) as count, t.applicable
-             , avg(CASE WHEN tv.ignore THEN NULL ELSE tv.vote END) as rating
-             , coalesce(avg(CASE WHEN tv.ignore THEN NULL ELSE tv.spoiler END), t.defaultspoil) as spoiler
+             , avg(CASE WHEN tv.ignore OR (u.id IS NOT NULL AND NOT u.perm_tag) THEN NULL ELSE tv.vote END) as rating
+             , coalesce(avg(CASE WHEN tv.ignore OR (u.id IS NOT NULL AND NOT u.perm_tag) THEN NULL ELSE tv.spoiler END), t.defaultspoil) as spoiler
              , bool_or(tv.ignore) as overruled
           FROM tags t
           JOIN tags_vn tv ON tv.tag = t.id
+          LEFT JOIN users u ON u.id = tv.uid
          WHERE tv.vid =', \$v->{id}, '
          GROUP BY t.id, t.name, t.cat
          ORDER BY t.name'