summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSpaceRanger <space.ranger.vn@gmail.com>2012-07-24 10:07:04 +0200
committerYorhel <git@yorhel.nl>2012-07-24 10:07:04 +0200
commit51d88d29e640f577d64549e8bd8fc7a94a05911f (patch)
tree0a99f8cd3f3d3aaedc5ed69293d4a83290ad61f6
parent9a83733079e17f46c66d761a098952d0f50d93fa (diff)
Add wishlist / VN list status options to VN list browser
-rw-r--r--data/lang.txt24
-rw-r--r--data/style.css2
-rw-r--r--lib/VNDB/DB/VN.pm25
-rw-r--r--lib/VNDB/Handler/VNBrowse.pm38
-rw-r--r--lib/VNDB/Util/BrowseHTML.pm8
-rw-r--r--util/sql/all.sql2
-rw-r--r--util/updates/update_2.23.sql6
7 files changed, 100 insertions, 5 deletions
diff --git a/data/lang.txt b/data/lang.txt
index 8c04a916..cc06fe9d 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -12057,6 +12057,30 @@ tr : Görsel romanlara gözat
uk : Огляд візуальних новел
it : Sfoglia visual novel
+:_vnbrowse_vnlist
+en : User VN list
+ru*:
+cs*:
+hu*:
+nl*:
+de*:
+es*:
+tr*:
+uk*:
+it*:
+
+:_vnbrowse_wishlist
+en : Wishlist
+ru*:
+cs*:
+hu*:
+nl*:
+de*:
+es*:
+tr*:
+uk*:
+it*:
+
:_vnbrowse_col_score
en : Score
ru : Рейтинг
diff --git a/data/style.css b/data/style.css
index 4e08a220..128695b3 100644
--- a/data/style.css
+++ b/data/style.css
@@ -434,6 +434,8 @@ div.scr_uploader { visibility: hidden; overflow: hidden; width: 1px; height:
.vnbrowse .tc3 { padding: 0; }
.vnbrowse .tc5 { text-align: right; padding-right: 10px }
.vnbrowse .tc6 { width: 80px }
+.vnbrowse .tc7 { text-align: right; width: 8px }
+.vnbrowse .tc8 { width: 8px }
#filselect {
text-align: center;
display: block;
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});
diff --git a/util/sql/all.sql b/util/sql/all.sql
index c0664c51..68739776 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -13,7 +13,7 @@ CREATE TYPE language AS ENUM ('cs', 'da', 'de', 'en', 'es', 'fi', 'fr',
CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce');
CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 'c', 't');
-CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce');
+CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce', 'vn_list_own', 'vn_list_wish');
CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
CREATE TYPE tag_category AS ENUM('cont', 'ero', 'tech');
diff --git a/util/updates/update_2.23.sql b/util/updates/update_2.23.sql
index 69ef05c9..4464053c 100644
--- a/util/updates/update_2.23.sql
+++ b/util/updates/update_2.23.sql
@@ -46,3 +46,9 @@ UPDATE vn SET c_rating = (SELECT (
((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
);
+
+
+-- New enum types for user list display in VN list
+
+ALTER TYPE prefs_key ADD VALUE 'vn_list_own' AFTER 'notify_announce';
+ALTER TYPE prefs_key ADD VALUE 'vn_list_wish' AFTER 'vn_list_own';