Allow one fractional digit for VN votes

The interface to set a non-integer vote isn't very nice, but at least it
works. Or so I hope.

10 files changed, 84 insertions, 29 deletions

diff --git a/data/lang.txt b/data/lang.txt
index e238e352..0a1a14db 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -9496,6 +9496,30 @@ hu : visszavonás
 nl : intrekken
 de : wiederrufen
 
+:_vnpage_uopt_othvote
+en : Other
+ru*:
+cs*:
+hu*:
+nl : Anders
+de*:
+
+:_vnpage_uopt_othervote
+en : Please input your vote as a number between 1 and 10. One digit after the decimal is allowed, for example: 8.6 or 7.3.
+ru*:
+cs*:
+hu*:
+nl : Geef je stem op als een number tussen 1 en 10. Één getal achter de comma is toegestaan, bijvoorbeeld: 8.6 of 7.3.
+de*:
+
+:_vnpage_uopt_invvote
+en : Invalid number.
+ru*:
+cs*:
+hu*:
+nl : Ongeldig nummer.
+de*:
+
 :_vnpage_uopt_1vote
 en : You are about to give this visual novel a 1 out of 10. This is a rather extreme rating, meaning this game has absolutely nothing to offer, and that it's the worst game you have ever played.
      Are you really sure this visual novel matches that description?
diff --git a/data/script.js b/data/script.js
index 4065def4..5c8b7ead 100644
--- a/data/script.js
+++ b/data/script.js
@@ -2695,11 +2695,20 @@ if(byId('filselect'))
 if(byId('votesel')) {
   byId('votesel').onchange = function() {
     var s = this.options[this.selectedIndex].value;
+    if(s == -2)
+      s = prompt(mt('_vnpage_uopt_othervote'), '');
+    if(!s || s == -3)
+      return;
+    if(s != -1 && (!s.match(/^([1-9]|10)(\.[0-9])?$/) || s > 10 || s < 1)) {
+      alert(mt('_vnpage_uopt_invvote'));
+      this.selectedIndex = 0;
+      return;
+    }
     if(s == 1 && !confirm(mt('_vnpage_uopt_1vote')))
       return;
     if(s == 10 && !confirm(mt('_vnpage_uopt_10vote')))
       return;
-    if(s)
+    if(s > 0 || s == -1)
       location.href = location.href.replace(/#.*/, '').replace(/\/chars/, '').replace(/\.[0-9]+/, '')+'/vote?formcode='+this.name+';v='+s;
   };
 }
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm
index e1c22594..a0cf4f18 100644
--- a/lib/VNDB/DB/ULists.pm
+++ b/lib/VNDB/DB/ULists.pm
@@ -235,17 +235,18 @@ sub dbVoteGet {
 
 
 # Arguments: (uid|vid), id, use_ignore_list
-# Returns an arrayref with 10 elements containing the number of votes for index+1
+# Returns an arrayref with 10 elements containing the [ count(vote), sum(vote) ]
+#   for votes in the range of ($index+0.5) .. ($index+1.4)
 sub dbVoteStats {
   my($self, $col, $id, $ign) = @_;
   my $u = $self->authInfo->{id};
-  my $r = [ qw| 0 0 0 0 0 0 0 0 0 0 | ];
-  $r->[$_->{vote}-1] = $_->{votes} for (@{$self->dbAll(q|
-    SELECT vote, COUNT(vote) as votes
+  my $r = [ map [0,0], 0..9 ];
+  $r->[$_->{idx}] = [ $_->{votes}, $_->{total} ] for (@{$self->dbAll(q|
+    SELECT (vote::numeric/10)::int-1 AS idx, COUNT(vote) as votes, SUM(vote) AS total
       FROM votes
       !s
       !W
-      GROUP BY vote|,
+      GROUP BY (vote::numeric/10)::int|,
     $ign ? 'JOIN users ON id = uid AND (NOT ign_votes'.($u?sprintf(' OR id = %d',$u):'').')' : '',
     $col ? { '!s = ?' => [ $col, $id ] } : {},
   )});
diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm
index 91659f13..4f564a18 100644
--- a/lib/VNDB/Func.pm
+++ b/lib/VNDB/Func.pm
@@ -9,7 +9,7 @@ use POSIX 'strftime', 'ceil', 'floor';
 use VNDBUtil;
 our @EXPORT = (@VNDBUtil::EXPORT, qw|
   clearfloat cssicon tagscore mt minage fil_parse fil_serialize parenttags
-  childtags charspoil imgpath imgurl
+  childtags charspoil imgpath imgurl fmtvote
 |);
 
 
@@ -195,5 +195,10 @@ sub imgurl {
 }
 
 
+# Formats a vote number.
+sub fmtvote {
+  return !$_[0] ? '-' : $_[0] % 10 == 0 ? $_[0]/10 : sprintf '%.1f', $_[0]/10;
+}
+
 1;
 
diff --git a/lib/VNDB/Handler/ULists.pm b/lib/VNDB/Handler/ULists.pm
index 1c82d982..6e28a61a 100644
--- a/lib/VNDB/Handler/ULists.pm
+++ b/lib/VNDB/Handler/ULists.pm
@@ -27,12 +27,12 @@ sub vnvote {
 
   return if !$self->authCheckCode;
   my $f = $self->formValidate(
-    { get => 'v', enum => [ -1, 1..10 ] }
+    { get => 'v', regex => qr/^(-1|([1-9]|10)(\.[0-9])?)$/ }
   );
-  return $self->resNotFound if $f->{_err};
+  return $self->resNotFound if $f->{_err} || ($f->{v} != -1 && ($f->{v} > 10 || $f->{v} < 1));
 
   $self->dbVoteDel($uid, $id) if $f->{v} == -1;
-  $self->dbVoteAdd($id, $uid, $f->{v}) if $f->{v} > 0;
+  $self->dbVoteAdd($id, $uid, $f->{v}*10) if $f->{v} > 0;
 
   $self->resRedirect('/v'.$id, 'temp');
 }
@@ -138,7 +138,7 @@ sub votelist {
     my @vid = grep $_ && $_ > 0, @{$frm->{vid}};
     if(!$frm->{_err} && @vid && $frm->{batchedit} > -2) {
       $self->dbVoteDel($id, \@vid) if $frm->{batchedit} == -1;
-      $self->dbVoteAdd(\@vid, $id, $frm->{batchedit}) if $frm->{batchedit} >= 0;
+      $self->dbVoteAdd(\@vid, $id, $frm->{batchedit}*10) if $frm->{batchedit} > 0;
     }
   }
 
@@ -191,7 +191,7 @@ sub votelist {
         input type => 'checkbox', name => 'vid', value => $l->{vid} if $own;
         txt ' '.$self->{l10n}->date($l->{date});
        end;
-       td class => 'tc2', $l->{vote};
+       td class => 'tc2', fmtvote $l->{vote};
        td class => 'tc3';
         a href => $type eq 'v' ? ("/u$l->{uid}", $l->{username}) : ("/v$l->{vid}", shorten $l->{title}, 100);
        end;
@@ -460,7 +460,7 @@ sub _vnlist_browse {
         $txt = qq|<b class="todo">$txt</b>| if $obtained < $total;
         lit $txt;
        end;
-       td class => 'tc8', $i->{vote} || '-';
+       td class => 'tc8', fmtvote $i->{vote};
       end 'tr';
 
       for (@{$i->{rels}}) {
diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm
index cc5ce28c..cc42f4d0 100644
--- a/lib/VNDB/Handler/Users.pm
+++ b/lib/VNDB/Handler/Users.pm
@@ -76,10 +76,10 @@ sub userpage {
       } elsif($votes) {
         my($total, $count) = (0, 0);
         for (1..@$votes) {
-          $total += $_*$votes->[$_-1];
-          $count += $votes->[$_-1];
+          $count += $votes->[$_-1][0];
+          $total += $votes->[$_-1][1];
         }
-        lit mt '_userpage_votes_item', "/u$uid/votes", $count, sprintf '%.2f', $total/$count;
+        lit mt '_userpage_votes_item', "/u$uid/votes", $count, sprintf '%.2f', $total/$count/10;
       } else {
         txt '-';
       }
diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm
index 1f73c3be..f71baf30 100644
--- a/lib/VNDB/Handler/VNPage.pm
+++ b/lib/VNDB/Handler/VNPage.pm
@@ -381,9 +381,10 @@ sub _useroptions {
    td;
     if($vote || !$wish) {
       Select id => 'votesel', name => $self->authGetCode("/v$v->{id}/vote");
-       option $vote ? mt '_vnpage_uopt_voted', $vote->{vote} : mt '_vnpage_uopt_novote';
+       option value => -3, $vote ? mt '_vnpage_uopt_voted', fmtvote($vote->{vote}) : mt '_vnpage_uopt_novote';
        optgroup label => $vote ? mt '_vnpage_uopt_changevote' : mt '_vnpage_uopt_dovote';
         option value => $_, "$_ (".mt("_vote_$_").')' for (reverse 1..10);
+        option value => -2, mt '_vnpage_uopt_othvote';
        end;
        option value => -1, mt '_vnpage_uopt_delvote' if $vote;
       end;
@@ -566,7 +567,7 @@ sub _stats {
   my $stats = $self->dbVoteStats(vid => $v->{id}, 1);
   div class => 'mainbox';
    h1 mt '_vnpage_stats';
-   if(!grep $_ > 0, @$stats) {
+   if(!grep $_->[0] > 0, @$stats) {
      p mt '_vnpage_stats_none';
    } else {
      $self->htmlVoteStats(v => $v, $stats);
diff --git a/lib/VNDB/Util/BrowseHTML.pm b/lib/VNDB/Util/BrowseHTML.pm
index 6d6cdeda..993f8746 100644
--- a/lib/VNDB/Util/BrowseHTML.pm
+++ b/lib/VNDB/Util/BrowseHTML.pm
@@ -202,7 +202,7 @@ sub htmlBrowseVN {
        end;
        td class => 'tc5', sprintf '%.2f', ($l->{c_popularity}||0)*100;
        td class => 'tc6';
-        txt sprintf '%.2f', $l->{c_rating}||0;
+        txt sprintf '%.2f', ($l->{c_rating}||0)/10;
         b class => 'grayedout', sprintf ' (%d)', $l->{c_votecount};
        end;
       end 'tr';
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index 73e2c5ba..8f908b19 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -338,11 +338,11 @@ sub htmlItemMessage {
 sub htmlVoteStats {
   my($self, $type, $obj, $stats) = @_;
 
-  my($max, $count, $total) = (0, 0);
+  my($max, $count, $total) = (0, 0, 0);
   for (0..$#$stats) {
-    $max = $stats->[$_] if $stats->[$_] > $max;
-    $count += $stats->[$_];
-    $total += $stats->[$_]*($_+1);
+    $max = $stats->[$_][0] if $stats->[$_][0] > $max;
+    $count += $stats->[$_][0];
+    $total += $stats->[$_][1];
   }
   div class => 'votestats';
    table class => 'votegraph';
@@ -350,15 +350,15 @@ sub htmlVoteStats {
      td colspan => 2, mt '_votestats_title';
     end; end;
     tfoot; Tr;
-     td colspan => 2, mt('_votestats_sum', $count, sprintf('%.2f', $total/$count))
-       .($type eq 'v' ? ' ('.mt('_vote_'.(ceil($total/$count-1)||1)).')' : '');
+     td colspan => 2, mt('_votestats_sum', $count, sprintf('%.2f', $total/$count/10))
+       .($type eq 'v' ? ' ('.mt('_vote_'.(ceil($total/$count/10-1)||1)).')' : '');
     end; end;
     for (reverse 0..$#$stats) {
       Tr;
       td class => 'number', $_+1;
        td class => 'graph';
-        div style => 'width: '.($stats->[$_] ? $stats->[$_]/$max*250 : 0).'px', ' ';
-        txt $stats->[$_];
+        div style => 'width: '.($stats->[$_][0]/$max*250).'px', ' ';
+        txt $stats->[$_][0];
        end;
       end;
     }
@@ -392,7 +392,7 @@ sub htmlVoteStats {
             a href => "/u$recent->[$_]{uid}", $recent->[$_]{username};
           }
          end;
-         td $recent->[$_]{vote};
+         td fmtvote $recent->[$_]{vote};
          td $self->{l10n}->date($recent->[$_]{date});
         end;
       }
@@ -404,7 +404,7 @@ sub htmlVoteStats {
      div;
       h3 mt '_votestats_rank_title';
       p mt '_votestats_rank_pop', $obj->{p_ranking}, sprintf '%.2f', ($obj->{c_popularity}||0)*100;
-      p mt '_votestats_rank_rat', $obj->{r_ranking}, sprintf '%.2f', $obj->{c_rating};
+      p mt '_votestats_rank_rat', $obj->{r_ranking}, sprintf '%.2f', $obj->{c_rating}/10;
      end;
    }
   end 'div';
diff --git a/util/updates/update_2.23.sql b/util/updates/update_2.23.sql
index ed419d16..0d866d3c 100644
--- a/util/updates/update_2.23.sql
+++ b/util/updates/update_2.23.sql
@@ -31,3 +31,18 @@ ALTER TABLE vn ALTER COLUMN c_olang TYPE language[] USING c_olang::text[]::langu
 ALTER TABLE vn ALTER COLUMN c_olang SET DEFAULT '{}';
 
 DROP TYPE language_old;
+
+
+
+-- VN votes * 10
+-- (The WHERE prevents another *10 if this query has already been executed)
+
+UPDATE votes SET vote = vote * 10 WHERE NOT EXISTS(SELECT 1 FROM votes WHERE vote > 10);
+
+-- recalculate c_rating
+UPDATE vn SET c_rating = (SELECT (
+    ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)
+      *(SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) AS v(a)) + SUM(vote)::real) /
+    ((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)
+);