diff options
author | SpaceRanger <space.ranger.vn@gmail.com> | 2012-07-24 10:07:04 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2012-07-24 10:07:04 +0200 |
commit | 51d88d29e640f577d64549e8bd8fc7a94a05911f (patch) | |
tree | 0a99f8cd3f3d3aaedc5ed69293d4a83290ad61f6 | |
parent | 9a83733079e17f46c66d761a098952d0f50d93fa (diff) |
Add wishlist / VN list status options to VN list browser
-rw-r--r-- | data/lang.txt | 24 | ||||
-rw-r--r-- | data/style.css | 2 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 25 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNBrowse.pm | 38 | ||||
-rw-r--r-- | lib/VNDB/Util/BrowseHTML.pm | 8 | ||||
-rw-r--r-- | util/sql/all.sql | 2 | ||||
-rw-r--r-- | util/updates/update_2.23.sql | 6 |
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>▸</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'; |