Add release filters to VN browser

9 files changed, 71 insertions, 33 deletions

diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index eafb84c0..ecec63aa 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -7,26 +7,15 @@ use POSIX 'strftime';
 use Exporter 'import';
 use VNDB::Func 'gtintype';
 
-our @EXPORT = qw|dbReleaseGet dbReleaseGetRev dbReleaseRevisionInsert|;
+our @EXPORT = qw|dbReleaseFilters dbReleaseGet dbReleaseGetRev dbReleaseRevisionInsert|;
 
 
-# Options: id vid pid released page results what med sort reverse date_before date_after
-#   plat lang olang type minage search resolution freeware doujin voiced ani_story ani_ero
-# What: extended vn producers platforms media affiliates
-# Sort: title released minage
-sub dbReleaseGet {
+# Release filters shared by dbReleaseGet and dbVNGet
+sub dbReleaseFilters {
   my($self, %o) = @_;
-  $o{results} ||= 50;
-  $o{page} ||= 1;
-  $o{what} ||= '';
   $o{plat} = [ $o{plat} ] if $o{plat} && !ref $o{plat};
   $o{med}  = [ $o{med}  ] if $o{med}  && !ref $o{med};
-
-  my @where = (
-    !$o{id}                 ? ( 'r.hidden = FALSE' => 0       ) : (),
-    $o{id}                  ? ( 'r.id = ?'         => $o{id}  ) : (),
-    $o{vid}                 ? ( 'rv.vid IN(!l)'    => [ ref $o{vid} ? $o{vid} : [$o{vid}] ] ) : (),
-    $o{pid}                 ? ( 'rp.pid = ?'       => $o{pid} ) : (),
+  return (
     defined $o{patch}       ? ( 'r.patch = ?'      => $o{patch}    == 1 ? 1 : 0) : (),
     defined $o{freeware}    ? ( 'r.freeware = ?'   => $o{freeware} == 1 ? 1 : 0) : (),
     defined $o{doujin}      ? ( 'r.doujin = ?'     => $o{doujin}   == 1 ? 1 : 0) : (),
@@ -52,6 +41,26 @@ sub dbReleaseGet {
       grep(!/^unk$/, @{$o{med}}) ? 'r.id IN(SELECT irm.id FROM releases_media irm WHERE irm.medium IN(!l))' : ()
       ).')', [ [ grep(!/^unk$/, @{$o{med}}) ] ]) : (),
   );
+}
+
+
+# Options: id vid pid released page results what med sort reverse date_before date_after
+#   plat lang olang type minage search resolution freeware doujin voiced ani_story ani_ero
+# What: extended vn producers platforms media affiliates
+# Sort: title released minage
+sub dbReleaseGet {
+  my($self, %o) = @_;
+  $o{results} ||= 50;
+  $o{page} ||= 1;
+  $o{what} ||= '';
+
+  my @where = (
+    !$o{id}                 ? ( 'r.hidden = FALSE' => 0       ) : (),
+    $o{id}                  ? ( 'r.id = ?'         => $o{id}  ) : (),
+    $o{vid}                 ? ( 'rv.vid IN(!l)'    => [ ref $o{vid} ? $o{vid} : [$o{vid}] ] ) : (),
+    $o{pid}                 ? ( 'rp.pid = ?'       => $o{pid} ) : (),
+    $self->dbReleaseFilters(%o),
+  );
 
   if($o{search}) {
     for (split /[ -,._]/, $o{search}) {
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 203fdcad..91ad7ccb 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -3,6 +3,7 @@ package VNDB::DB::VN;
 
 use strict;
 use warnings;
+use TUWF 'sqlprint';
 use Exporter 'import';
 use VNDB::Func 'gtintype', 'normalize_query';
 use Encode 'decode_utf8';
@@ -11,7 +12,7 @@ our @EXPORT = qw|dbVNGet dbVNGetRev dbVNRevisionInsert dbVNImageId dbScreenshotA
 
 
 # Options: id, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil,
-#   hasani, hasshot, ul_notblack, ul_onwish, results, page, what, sort, reverse, inc_hidden
+#   hasani, hasshot, ul_notblack, ul_onwish, results, page, what, sort, reverse, inc_hidden, release
 # What: extended anime staff seiyuu relations screenshots relgraph rating ranking wishlist vnlist
 #  Note: wishlist and vnlist are ignored (no db search) unless a user is logged in
 # Sort: id rel pop rating title tagscore rand
@@ -71,6 +72,13 @@ sub dbVNGet {
       'v.id IN(SELECT floor(random() * last_value)::integer FROM generate_series(1,20), (SELECT MAX(id) AS last_value FROM vn) s1 LIMIT 20)' ) : (),
   );
 
+  if($o{release}) {
+    my($q, @p) = sqlprint
+      'v.id IN(SELECT rv.vid FROM releases r JOIN releases_vn rv ON rv.id = r.id !W)',
+      [ 'NOT r.hidden' => 1, $self->dbReleaseFilters(%{$o{release}}), ];
+    push @where, $q, \@p;
+  }
+
   my @join = (
     $o{what} =~ /relgraph/ ?
       'JOIN relgraphs vg ON vg.id = v.rgraph' : (),
diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm
index 98847b4c..74029e5c 100644
--- a/lib/VNDB/Handler/Chars.pm
+++ b/lib/VNDB/Handler/Chars.pm
@@ -508,8 +508,10 @@ sub list {
     }
    end;
 
-   a id => 'filselect', href => '#c';
-    lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
+   p class => 'filselect';
+    a id => 'filselect', href => '#c';
+     lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
+    end;
    end;
    input type => 'hidden', class => 'hidden', name => 'fil', id => 'fil', value => $f->{fil};
   end;
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index d2497954..890058af 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -498,8 +498,10 @@ sub browse {
   div class => 'mainbox';
    h1 mt '_rbrowse_title';
    $self->htmlSearchBox('r', $f->{q});
-   a id => 'filselect', href => '#r';
-    lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
+   p class => 'filselect';
+    a id => 'filselect', href => '#r';
+     lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
+    end;
    end;
    input type => 'hidden', class => 'hidden', name => 'fil', id => 'fil', value => $f->{fil};
   end;
diff --git a/lib/VNDB/Handler/Staff.pm b/lib/VNDB/Handler/Staff.pm
index 0f878ca6..839d118a 100644
--- a/lib/VNDB/Handler/Staff.pm
+++ b/lib/VNDB/Handler/Staff.pm
@@ -326,8 +326,10 @@ sub list {
     }
     end;
 
-    a id => 'filselect', href => '#s';
-     lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
+    p class => 'filselect';
+     a id => 'filselect', href => '#s';
+      lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
+     end;
     end;
     input type => 'hidden', class => 'hidden', name => 'fil', id => 'fil', value => $f->{fil};
    end;
diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm
index cc38cdf8..f97fe238 100644
--- a/lib/VNDB/Handler/Tags.pm
+++ b/lib/VNDB/Handler/Tags.pm
@@ -111,8 +111,10 @@ sub tagpage {
       a href => "/g$t->{id}?fil=$f->{fil};m=2", $f->{m} == 2 ? (class => 'optselected') : (), mt '_spoilset_2';
      end;
 
-     a id => 'filselect', href => '#v';
-      lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
+     p class => 'filselect';
+      a id => 'filselect', href => '#v';
+       lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
+      end;
      end;
      input type => 'hidden', class => 'hidden', name => 'fil', id => 'fil', value => $f->{fil};
 
diff --git a/lib/VNDB/Handler/Traits.pm b/lib/VNDB/Handler/Traits.pm
index 98f0b757..8d689581 100644
--- a/lib/VNDB/Handler/Traits.pm
+++ b/lib/VNDB/Handler/Traits.pm
@@ -100,8 +100,10 @@ sub traitpage {
       a href => "/i$trait?m=2", $f->{m} == 2 ? (class => 'optselected') : (), mt '_spoilset_2';
      end;
 
-     a id => 'filselect', href => '#c';
-      lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
+     p class => 'filselect';
+      a id => 'filselect', href => '#c';
+       lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
+      end;
      end;
      input type => 'hidden', class => 'hidden', name => 'fil', id => 'fil', value => $f->{fil};
 
diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm
index bc8b39b2..01f5bc61 100644
--- a/lib/VNDB/Handler/VNBrowse.pm
+++ b/lib/VNDB/Handler/VNBrowse.pm
@@ -22,6 +22,7 @@ sub list {
     { get => 'q', required => 0, default => '' },
     { get => 'sq', required => 0, default => '' },
     { get => 'fil',required => 0 },
+    { get => 'rfil', required => 0, default => '' },
     { get => 'vnlist', required => 0, default => 2, enum => [ '0', '1' ] }, # 2: use pref
     { get => 'wish',   required => 0, default => 2, enum => [ '0', '1' ] }, # 2: use pref
   );
@@ -50,6 +51,9 @@ sub list {
   $f->{s} = 'title' if $f->{fil} !~ /tag_inc-/ && $f->{s} eq 'tagscore';
   $f->{o} = $f->{s} eq 'tagscore' ? 'd' : 'a' if !$f->{o};
 
+  my $rfil = fil_parse $f->{rfil}, @{$VNDB::Util::Misc::filfields{release}};
+  $f->{rfil} = fil_serialize $rfil, @{$VNDB::Util::Misc::filfields{release}};
+
   my($list, $np) = $self->filFetchDB(vn => $f->{fil}, {
     %compat,
     tagspoil => $self->authPref('spoilers')||0,
@@ -57,8 +61,9 @@ sub list {
     what =>               ' rating' .
          ($f->{vnlist}  ? ' vnlist'   : '').
          ($f->{wish}    ? ' wishlist' : ''),
-    $char ne 'all' ? ( char => $char ) : (),
-    $f->{q} ? ( search => $f->{q} ) : (),
+    $char ne 'all' ? ( char    => $char   ) : (),
+    $f->{q}        ? ( search  => $f->{q} ) : (),
+    keys %$rfil    ? ( release => $rfil   ) : (),
     results => 50,
     page => $f->{p},
     sort => $f->{s}, reverse => $f->{o} eq 'd',
@@ -76,7 +81,7 @@ sub list {
   my $url = sub {
     my($char, $toggle) = @_;
 
-    return "/v/$char?q=$quri;fil=$f->{fil};s=$f->{s};o=$f->{o}" .
+    return "/v/$char?q=$quri;fil=$f->{fil};rfil=$f->{rfil};s=$f->{s};o=$f->{o}" .
            ($toggle ? ";$toggle=".($f->{$toggle}?0:1) : '');
   };
 
@@ -95,14 +100,20 @@ sub list {
      end 'p';
    }
 
-   a id => 'filselect', href => '#v';
-    lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
+   p class => 'filselect';
+    a id => 'filselect', href => '#v';
+     lit '<i>&#9656;</i> '.mt('_vnbrowse_fil_title').'<i></i>';
+    end;
+    a id => 'rfilselect', href => '#r';
+     lit '<i>&#9656;</i> '.mt('_rbrowse_fil_title').'<i></i>';
+    end;
    end;
    input type => 'hidden', class => 'hidden', name => 'fil', id => 'fil', value => $f->{fil};
+   input type => 'hidden', class => 'hidden', name => 'rfil', id => 'rfil', value => $f->{rfil};
   end;
   end 'form';
 
-  $self->htmlBrowseVN($list, $f, $np, "/v/$char?q=$quri;fil=$f->{fil}", $f->{fil} =~ /tag_inc-/);
+  $self->htmlBrowseVN($list, $f, $np, "/v/$char?q=$quri;fil=$f->{fil};rfil=$f->{rfil}", $f->{fil} =~ /tag_inc-/);
   $self->htmlFooter(pref_code => 1);
 }
 
diff --git a/lib/VNDB/Util/Misc.pm b/lib/VNDB/Util/Misc.pm
index f05bdf72..c84b41d5 100644
--- a/lib/VNDB/Util/Misc.pm
+++ b/lib/VNDB/Util/Misc.pm
@@ -10,7 +10,7 @@ use VNDB::Func;
 our @EXPORT = qw|filFetchDB bbSubstLinks|;
 
 
-my %filfields = (
+our %filfields = (
   vn      => [qw|length hasani hasshot tag_inc tag_exc taginc tagexc tagspoil lang olang plat ul_notblack ul_onwish ul_voted ul_onlist|],
   release => [qw|type patch freeware doujin date_before date_after released minage lang olang resolution plat med voiced ani_story ani_ero|],
   char    => [qw|gender bloodt bust_min bust_max waist_min waist_max hip_min hip_max height_min height_max weight_min weight_max trait_inc trait_exc tagspoil role|],