summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2021-08-03 18:57:35 +0200
committerYorhel <git@yorhel.nl>2021-08-03 18:57:35 +0200
commitb9af3a060bd8dd2a886a542ccd13ae22e6e40c9c (patch)
tree29b465d3e3c55de5fc2007dc157e3c8345d08ed7
parent82267a30fd0e9d46e7be76204f34c232b438b874 (diff)
VNLengthVote: Add vote listing + aggregated stats + make public
-rw-r--r--css/v2.css11
-rw-r--r--elm/VNEdit.elm4
-rw-r--r--lib/VNWeb/VN/Length.pm89
-rw-r--r--lib/VNWeb/VN/Page.pm33
-rw-r--r--sql/schema.sql14
-rwxr-xr-xutil/dbdump.pl5
6 files changed, 140 insertions, 16 deletions
diff --git a/css/v2.css b/css/v2.css
index 3e2e3730..7b17ec57 100644
--- a/css/v2.css
+++ b/css/v2.css
@@ -866,6 +866,17 @@ div.votelist td.tc1 { width: 100px; padding-top: 0; padding-bottom: 0 }
div.votelist td.tc2 { width: 50px; text-align: right; padding-right: 10px }
+
+/* vn/user length vote list */
+
+div.lengthlist {
+ .tc1 { width: 100px }
+ .tc3 { text-align: right }
+ .tc3a, .tc3b { width: 35px; padding-left: 0; padding-right: 0; text-align: right; white-space: nowrap }
+ .tc4 { width: 100px; padding-left: 10px }
+}
+
+
/***** Wishlist browser ******/
.wishlist .tc1 { padding-top: 0; padding-bottom: 0; }
diff --git a/elm/VNEdit.elm b/elm/VNEdit.elm
index 7f1fed98..269a2e14 100644
--- a/elm/VNEdit.elm
+++ b/elm/VNEdit.elm
@@ -367,7 +367,9 @@ view model =
, text "Short description of the main story. Please do not include spoilers, and don't forget to list the source in case you didn't write the description yourself."
]
, formField "olang::Original language" [ inputSelect "olang" model.olang OLang [] GT.languages ]
- , formField "length::Length" [ inputSelect "length" model.length Length [] GT.vnLengths ]
+ , formField "length::Length"
+ [ inputSelect "length" model.length Length [] GT.vnLengths
+ , text " (only displayed if there are no length votes)" ]
, formField "l_wikidata::Wikidata ID" [ inputWikidata "l_wikidata" model.lWikidata LWikidata [onInvalid (Invalid General)] ]
, formField "l_renai::Renai.us link" [ text "http://renai.us/game/", inputText "l_renai" model.lRenai LRenai (onInvalid (Invalid General) :: GVE.valL_Renai), text ".shtml" ]
diff --git a/lib/VNWeb/VN/Length.pm b/lib/VNWeb/VN/Length.pm
new file mode 100644
index 00000000..ea0eaee9
--- /dev/null
+++ b/lib/VNWeb/VN/Length.pm
@@ -0,0 +1,89 @@
+package VNWeb::VN::Length;
+
+use VNWeb::Prelude;
+
+sub opts {
+ my($vn) = @_;
+ tableopts
+ date => { name => 'Date', sort_id => 0, sort_sql => 'l.date', sort_default => 'desc' },
+ length => { name => 'Time', sort_id => 1, sort_sql => 'l.length' },
+ speed => { name => 'Speed', sort_id => 2, sort_sql => 'l.speed ?o, l.length' },
+ $vn ? (
+ username => { name => 'User', sort_id => 3, sort_sql => 'u.username' },
+ ) : (
+ title => { name => 'Title', sort_id => 4, sort_sql => 'v.title' },
+ );
+}
+my $TABLEOPTS_U = opts 0;
+my $TABLEOPTS_V = opts 1;
+
+
+sub listing_ {
+ my($opt, $count, $list, $vn) = @_;
+
+ my sub url { '?'.query_encode %$opt, @_ }
+
+ paginate_ \&url, $opt->{p}, [$count, $opt->{s}->results], 't';
+ div_ class => 'mainbox browse lengthlist', sub {
+ table_ class => 'stripe', sub {
+ thead_ sub { tr_ sub {
+ td_ class => 'tc1', sub { txt_ 'Date'; sortable_ 'date', $opt, \&url };
+ td_ class => 'tc2', sub { txt_ 'User'; sortable_ 'username', $opt, \&url } if $vn;
+ td_ class => 'tc2', sub { txt_ 'Title'; sortable_ 'title', $opt, \&url } if !$vn;
+ td_ class => 'tc3', colspan => 2, sub { txt_ 'Time'; sortable_ 'length', $opt, \&url };
+ td_ class => 'tc4', sub { txt_ 'Speed'; sortable_ 'speed', $opt, \&url };
+ td_ class => 'tc5', 'Notes';
+ } };
+ tr_ sub {
+ td_ class => 'tc1', fmtdate $_->{date};
+ td_ class => 'tc2', sub { user_ $_ } if $vn;
+ td_ class => 'tc2', sub {
+ a_ href => "/$_->{vid}", title => $_->{original}||$_->{title}, $_->{title};
+ } if !$vn;
+ td_ class => 'tc3a', $_->{length} >= 60 ? floor($_->{length}/60).'h' : '';
+ td_ class => 'tc3b', $_->{length} % 60 > 0 ? ($_->{length}%60).'m' : '';
+ td_ class => 'tc4', ['Slow','Normal','Fast']->[$_->{speed}];
+ td_ class => 'tc5', sub { lit_ bb_format $_->{notes}, inline => 1 };
+ } for @$list;
+ };
+ };
+ paginate_ \&url, $opt->{p}, [$count, $opt->{s}->results], 'b';
+}
+
+
+TUWF::get qr{/(?<thing>$RE{vid}|$RE{uid})/lengthvotes}, sub {
+ my $o = dbobj tuwf->capture('thing');
+ return tuwf->resNotFound if !$o->{id} || $o->{entry_hidden};
+ my $vn = $o->{id} =~ /^v/;
+
+ my $opt = tuwf->validate(get =>
+ p => { page => 1 },
+ s => { tableopts => $vn ? $TABLEOPTS_V : $TABLEOPTS_U },
+ )->data;
+
+ my $where = sql_and
+ $vn ? 'NOT EXISTS(SELECT 1 FROM users WHERE users.id = l.uid AND NOT perm_lengthvote)' : (),
+ sql($vn ? 'l.vid =' : 'l.uid =', \$o->{id});
+ my $count = tuwf->dbVali('SELECT COUNT(*) FROM vn_length_votes l WHERE', $where);
+
+ my $lst = tuwf->dbPagei({results => $opt->{s}->results, page => $opt->{p}},
+ 'SELECT l.uid, l.vid, l.length, l.speed, l.notes, ', sql_totime('l.date'), 'AS date, ',
+ $vn ? sql_user() : 'v.title, v.original', '
+ FROM vn_length_votes l',
+ $vn ? 'LEFT JOIN users u ON u.id = l.uid'
+ : 'JOIN vn v ON v.id = l.vid',
+ 'WHERE', $where,
+ 'ORDER BY', $opt->{s}->sql_order(),
+ );
+
+ my $title = 'Length votes '.($vn ? 'for ' : 'by ').$o->{title};
+ framework_ title => $title, dbobj => $o, sub {
+ div_ class => 'mainbox', sub {
+ h1_ $title;
+ p_ 'Nothing to list. :(' if !@$lst;
+ };
+ listing_ $opt, $count, $lst, $vn if @$lst;
+ };
+};
+
+1;
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index cfb3a649..6e538595 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -157,21 +157,40 @@ sub infobox_length_ {
my($v) = @_;
my $today = strftime('%Y%m%d', gmtime);
- my $canvote = auth->permLengthvote && grep $_->{type} ne 'trial' && $_->{released} <= $today, $v->{releases}->@*;
- return if !$v->{length} && !$canvote;
+ return if !grep $_->{type} ne 'trial' && $_->{released} <= $today, $v->{releases}->@*;
- my $vote = $canvote && tuwf->dbRowi('SELECT rid, length, speed, notes FROM vn_length_votes WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid);
+ my $stats = tuwf->dbRowi('
+ SELECT count(*) as count, avg(l.length)::int as avg, stddev_pop(l.length::real)::int as stddev
+ FROM vn_length_votes l
+ LEFT JOIN users u ON u.id = l.uid
+ WHERE u.perm_lengthvote IS DISTINCT FROM false AND l.vid =', \$v->{id});
+ return if !$v->{length} && !$stats->{count} && !auth->permLengthvote;
- # TODO: Display aggregated vote stats
+ my $my = auth->permLengthvote && tuwf->dbRowi('SELECT rid, length, speed, notes FROM vn_length_votes WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid);
+
+ my sub fmtlength {
+ my($l) = @_;
+ +($l>60?floor($l/60).'h':'').($l%60?sprintf '%dm', $l%60:'');
+ }
tr_ sub {
td_ 'Length';
td_ sub {
- txt_ $v->{length} ? "$VN_LENGTH{$v->{length}}{txt} ($VN_LENGTH{$v->{length}}{time})" : 'Unknown';
- if ($canvote) {
+ if($stats->{count}) {
+ txt_ fmtlength $stats->{avg};
+ txt_ ' σ '.fmtlength $stats->{stddev} if $stats->{stddev};
+ txt_ ' (';
+ a_ href => "/$v->{id}/lengthvotes", sprintf '%d vote%s', $stats->{count}, $stats->{count}==1?'':'s';
+ txt_ ').';
+ } elsif($v->{length}) {
+ txt_ "$VN_LENGTH{$v->{length}}{txt} ($VN_LENGTH{$v->{length}}{time})";
+ } else {
+ txt_ 'Unknown';
+ }
+ if (auth->permLengthvote) {
elm_ VNLengthVote => $VNWeb::VN::Elm::LENGTHVOTE, {
uid => auth->uid, vid => $v->{id},
- vote => $vote->{rid}?$vote:undef,
+ vote => $my->{rid}?$my:undef,
}, sub { span_ @_, ''};
}
};
diff --git a/sql/schema.sql b/sql/schema.sql
index 71d32ad0..c33f0b58 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -1153,13 +1153,13 @@ CREATE TABLE vn_staff_hist (
-- vn_length_votes
CREATE TABLE vn_length_votes (
- vid vndbid NOT NULL,
- rid vndbid NOT NULL,
- date timestamptz NOT NULL DEFAULT NOW(),
- uid vndbid,
- length smallint NOT NULL, -- minutes
- speed smallint NOT NULL, -- 0=slow, 1=normal, 2=fast
- notes text NOT NULL DEFAULT ''
+ vid vndbid NOT NULL, -- [pub]
+ rid vndbid NOT NULL, -- [pub]
+ date timestamptz NOT NULL DEFAULT NOW(), -- [pub]
+ uid vndbid, -- [pub]
+ length smallint NOT NULL, -- [pub] minutes
+ speed smallint NOT NULL, -- [pub] 0=slow, 1=normal, 2=fast
+ notes text NOT NULL DEFAULT '' -- [pub]
);
-- wikidata
diff --git a/util/dbdump.pl b/util/dbdump.pl
index 3d10ec4d..734abaf2 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -97,7 +97,8 @@ my %tables = (
.' AND EXISTS(SELECT 1 FROM ulist_labels ul WHERE ul.uid = ulist_vns_labels.uid AND id = lbl AND NOT ul.private)' },
users => { where => 'id IN(SELECT DISTINCT uvl.uid FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE NOT ul.private)'
.' OR id IN(SELECT DISTINCT uid FROM tags_vn)'
- .' OR id IN(SELECT DISTINCT uid FROM image_votes)' },
+ .' OR id IN(SELECT DISTINCT uid FROM image_votes)'
+ .' OR id IN(SELECT DISTINCT uid FROM vn_length_votes)' },
vn => { where => 'NOT hidden' },
vn_anime => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
vn_relations => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
@@ -106,6 +107,8 @@ my %tables = (
.' AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)'
.' AND cid IN(SELECT id FROM chars WHERE NOT hidden)' },
vn_staff => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden) AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' },
+ vn_length_votes => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden) AND rid IN(SELECT id FROM releases WHERE NOT hidden)'
+ , order => 'vid, uid' },
wikidata => { where => q{id IN(SELECT l_wikidata FROM producers WHERE NOT hidden
UNION SELECT l_wikidata FROM staff WHERE NOT hidden
UNION SELECT l_wikidata FROM vn WHERE NOT hidden)} },