Add wishlist / VN list status options to VN list browser

3 files changed, 67 insertions, 4 deletions

diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 473fa92c..3a5544b2 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -12,7 +12,8 @@ our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreen
 
 # Options: id, rev, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil,
 #   hasani, hasshot, ul_notblack, ul_onwish, results, page, what, sort, reverse, inc_hidden
-# What: extended anime relations screenshots relgraph rating ranking changes
+# What: extended anime relations screenshots relgraph rating ranking changes 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
 sub dbVNGet {
   my($self, %o) = @_;
@@ -85,6 +86,14 @@ sub dbVNGet {
       'JOIN users u ON u.id = c.requester' : (),
     $o{what} =~ /relgraph/ ?
       'JOIN relgraphs vg ON vg.id = v.rgraph' : (),
+    $uid && $o{what} =~ /wishlist/ ?
+      'LEFT JOIN wlists wl ON wl.vid = v.id AND wl.uid = ' . $uid : (),
+    $uid && $o{what} =~ /vnlist/ ? (
+     'LEFT JOIN rlists rl
+           JOIN releases r     ON rl.rid = r.id
+           JOIN releases_vn rv ON rv.rid = r.latest
+         ON rv.vid = v.id AND rl.uid = ' . $uid,
+       ) : (),
   );
 
   my $tag_ids = $o{tag_inc} && join ',', ref $o{tag_inc} ? @{$o{tag_inc}} : $o{tag_inc};
@@ -100,6 +109,11 @@ sub dbVNGet {
       '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > COALESCE(v.c_popularity, 0.0)) AS p_ranking',
       '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > COALESCE(v.c_rating, 0.0)) AS r_ranking',
     ) : (),
+    $uid && $o{what} =~ /wishlist/ ? 'wl.wstat' : (),
+    $uid && $o{what} =~ /vnlist/ ? (
+      'COUNT(rv.vid) AS userlist_all',
+      'SUM(CASE WHEN rl.status = 2 THEN 1 ELSE 0 END) AS userlist_obtained',
+    ) : (),
     # TODO: optimize this, as it will be very slow when the selected tags match a lot of VNs (>1000)
     $tag_ids ?
       qq|(SELECT AVG(tvh.rating) FROM tags_vn_inherit tvh WHERE tvh.tag IN($tag_ids) AND tvh.vid = v.id AND spoiler <= $o{tagspoil} GROUP BY tvh.vid) AS tagscore| : (),
@@ -115,13 +129,20 @@ sub dbVNGet {
     rand     => 'RANDOM()',
   }->{$o{sort}}, $o{reverse} ? 'DESC' : 'ASC';
 
+  my @group = (
+    qw|v.id vr.title vr.original vr.id|,
+    $uid && $o{what} =~ /wishlist/ ? 'wl.wstat'  : (),
+    $uid && $o{what} =~ /vnlist/   ? 'rl.status' : (),
+  );
+
   my($r, $np) = $self->dbPage(\%o, q|
     SELECT !s
       FROM vn_rev vr
       !s
       !W
+      GROUP BY !s
       ORDER BY !s|,
-    join(', ', @select), join(' ', @join), \@where, $order,
+    join(', ', @select), join(' ', @join), \@where, join(', ', @group), $order,
   );
 
   if($o{what} =~ /relgraph/) {
diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm
index 869eeac8..86061bd1 100644
--- a/lib/VNDB/Handler/VNBrowse.pm
+++ b/lib/VNDB/Handler/VNBrowse.pm
@@ -22,11 +22,27 @@ sub list {
     { get => 'q', required => 0, default => '' },
     { get => 'sq', required => 0, default => '' },
     { get => 'fil',required => 0 },
+    { get => 'vnlist', required => 0, default => 2, enum => [ '0', '1' ] }, # 2: use pref
+    { get => 'wish',   required => 0, default => 2, enum => [ '0', '1' ] }, # 2: use pref
   );
   return $self->resNotFound if $f->{_err};
   $f->{q} ||= $f->{sq};
   $f->{fil} //= $self->authPref('filter_vn');
   my %compat = _fil_compat($self);
+  my $uid = $self->authInfo->{id};
+
+  my $read_write_pref = sub {
+    my($type, $pref_name) = @_;
+
+    return 0 if !$uid; # no data to display anyway
+    return $self->authPref($pref_name)?1:0 if $f->{$type} == 2;
+
+    $self->authPref($pref_name => $f->{$type}?1:0) if ($self->authPref($pref_name)?1:0) != $f->{$type};
+    return $f->{$type};
+  };
+
+  $f->{vnlist} = $read_write_pref->('vnlist', 'vn_list_own');
+  $f->{wish}   = $read_write_pref->('wish',   'vn_list_wish');
 
   return $self->resRedirect('/'.$1.$2.(!$3 ? '' : $1 eq 'd' ? '#'.$3 : '.'.$3), 'temp')
     if $f->{q} && $f->{q} =~ /^([gvrptudci])([0-9]+)(?:\.([0-9]+))?$/;
@@ -35,7 +51,9 @@ sub list {
   $f->{o} = $f->{s} eq 'tagscore' ? 'd' : 'a' if !$f->{o};
 
   my($list, $np) = $self->filFetchDB(vn => $f->{fil}, \%compat, {
-    what => 'rating',
+    what =>               ' rating' .
+         ($f->{vnlist}  ? ' vnlist'   : '').
+         ($f->{wish}    ? ' wishlist' : ''),
     $char ne 'all' ? ( char => $char ) : (),
     $f->{q} ? ( search => $f->{q} ) : (),
     results => 50,
@@ -50,14 +68,30 @@ sub list {
 
   my $quri = uri_escape($f->{q});
   form action => '/v/all', 'accept-charset' => 'UTF-8', method => 'get';
+
+  # url generator
+  my $url = sub {
+    my($char, $toggle) = @_;
+
+    return '/v/' . $char . "?q=$quri;fil=$f->{fil}" .
+           ($toggle ? ';' . $toggle . '=' . ($f->{$toggle}?0:1)
+                    : '');
+  };
+
   div class => 'mainbox';
    h1 mt '_vnbrowse_title';
    $self->htmlSearchBox('v', $f->{q});
    p class => 'browseopts';
     for ('all', 'a'..'z', 0) {
-      a href => "/v/$_?q=$quri;fil=$f->{fil}", $_ eq $char ? (class => 'optselected') : (), $_ eq 'all' ? mt('_char_all') : $_ ? uc $_ : '#';
+      a href => $url->($_), $_ eq $char ? (class => 'optselected') : (), $_ eq 'all' ? mt('_char_all') : $_ ? uc $_ : '#';
     }
    end;
+   if($uid) {
+     p class => 'browseopts';
+      a href => $url->($char, 'vnlist'), $f->{vnlist} ? (class => 'optselected') : (), mt('_vnbrowse_vnlist');
+      a href => $url->($char, 'wish'  ), $f->{wish}   ? (class => 'optselected') : (), mt('_vnbrowse_wishlist');
+     end 'p';
+   }
 
    a id => 'filselect', href => '#v';
     lit '<i>&#9656;</i> '.mt('_js_fil_filters').'<i></i>';
diff --git a/lib/VNDB/Util/BrowseHTML.pm b/lib/VNDB/Util/BrowseHTML.pm
index 88396cf5..94049cdc 100644
--- a/lib/VNDB/Util/BrowseHTML.pm
+++ b/lib/VNDB/Util/BrowseHTML.pm
@@ -172,6 +172,8 @@ sub htmlBrowseVN {
     header   => [
       $tagscore ? [ mt('_vnbrowse_col_score'), 'tagscore', undef, 'tc_s' ] : (),
       [ mt('_vnbrowse_col_title'),       'title', undef, $tagscore ? 'tc_t' : 'tc1' ],
+      $f->{vnlist} ? [ '',               0,       undef, 'tc7' ] : (),
+      $f->{wish}   ? [ '',               0,       undef, 'tc8' ] : (),
       [ '',                              0,       undef, 'tc2' ],
       [ '',                              0,       undef, 'tc3' ],
       [ mt('_vnbrowse_col_released'),    'rel',   undef, 'tc4' ],
@@ -189,6 +191,12 @@ sub htmlBrowseVN {
        td class => $tagscore ? 'tc_t' : 'tc1';
         a href => '/v'.$l->{id}, title => $l->{original}||$l->{title}, shorten $l->{title}, 100;
        end;
+       if($f->{vnlist}) {
+         td class => 'tc7';
+          lit sprintf '<b class="%s">%d/%d</b>', $l->{userlist_obtained} == $l->{userlist_all} ? 'done' : 'todo', $l->{userlist_obtained}, $l->{userlist_all} if $l->{userlist_all};
+         end 'td';
+       }
+       td class => 'tc8', defined($l->{wstat}) ? mt "_wish_$l->{wstat}" : '' if $f->{wish};
        td class => 'tc2';
         $_ ne 'oth' && cssicon $_, mt "_plat_$_"
           for (sort split /\//, $l->{c_platforms});