Show the rating difference for each game.

diff --git a/recalc-double-result.pl b/recalc-double-result.pl
index 388a177c61f553aa275cd23580fbcb450e3c5a04..2ccb6a7428430db97079ca1f25d96f7ba487292f 100755 (executable)
--- a/recalc-double-result.pl
+++ b/recalc-double-result.pl
@@ -69,14 +69,14 @@ while (my $ref = $q->fetchrow_hashref) {
                $ref->{'score1'}, $ref->{'score2'},
                $newr1_1, $newrd1_1, $newr1_2, $newrd1_2,
                $newr2_1, $newrd2_1, $newr2_2, $newrd2_2);
-       $dbh->do('insert into double_rating values (?,?,?,?)', undef,
-               $ref->{'team1_username1'}, $ref->{'gametime'}, $newr1_1, $newrd1_1);
-       $dbh->do('insert into double_rating values (?,?,?,?)', undef,
-               $ref->{'team1_username2'}, $ref->{'gametime'}, $newr1_2, $newrd1_2);
-       $dbh->do('insert into double_rating values (?,?,?,?)', undef,
-               $ref->{'team2_username1'}, $ref->{'gametime'}, $newr2_1, $newrd2_1);
-       $dbh->do('insert into double_rating values (?,?,?,?)', undef,
-               $ref->{'team2_username2'}, $ref->{'gametime'}, $newr2_2, $newrd2_2);
+       $dbh->do('insert into double_rating values (?,?,?,?,?)', undef,
+               $ref->{'team1_username1'}, $ref->{'gametime'}, $newr1_1, $newrd1_1, $newr1_1-$rating1_1);
+       $dbh->do('insert into double_rating values (?,?,?,?,?)', undef,
+               $ref->{'team1_username2'}, $ref->{'gametime'}, $newr1_2, $newrd1_2, $newr1_2-$rating1_2);
+       $dbh->do('insert into double_rating values (?,?,?,?,?)', undef,
+               $ref->{'team2_username1'}, $ref->{'gametime'}, $newr2_1, $newrd2_1, $newr2_1-$rating2_1);
+       $dbh->do('insert into double_rating values (?,?,?,?,?)', undef,
+               $ref->{'team2_username2'}, $ref->{'gametime'}, $newr2_2, $newrd2_2, $newr2_2-$rating2_2);
 
        $ratings{$ref->{'team1_username1'}} = [ $newr1_1, $newrd1_1, $ref->{'eptime'} ];
        $ratings{$ref->{'team1_username2'}} = [ $newr1_2, $newrd1_2, $ref->{'eptime'} ];

diff --git a/recalc-single-result.pl b/recalc-single-result.pl
index 02f421b899b5bef903db0074abe8ba3a2d13d5e2..c67be84b36e84a68e3fba5bd596e57b2e4b460a1 100755 (executable)
--- a/recalc-single-result.pl
+++ b/recalc-single-result.pl
@@ -42,10 +42,10 @@ while (my $ref = $q->fetchrow_hashref) {
        printf("%-10s - %-10s: %u - %u, new ratings %u/%u %u/%u\n",
                $ref->{'username1'}, $ref->{'username2'}, $ref->{'score1'},
                $ref->{'score2'}, $newr1, $newrd1, $newr2, $newrd2);
-       $dbh->do('insert into single_rating values (?,?,?,?)', undef,
-               $ref->{'username1'}, $ref->{'gametime'}, $newr1, $newrd1);
-       $dbh->do('insert into single_rating values (?,?,?,?)', undef,
-               $ref->{'username2'}, $ref->{'gametime'}, $newr2, $newrd2);
+       $dbh->do('insert into single_rating values (?,?,?,?,?)', undef,
+               $ref->{'username1'}, $ref->{'gametime'}, $newr1, $newrd1, $newr1-$rating1);
+       $dbh->do('insert into single_rating values (?,?,?,?,?)', undef,
+               $ref->{'username2'}, $ref->{'gametime'}, $newr2, $newrd2, $newr2-$rating2);
 
        $ratings{$ref->{'username1'}} = [ $newr1, $newrd1, $ref->{'eptime'} ];
        $ratings{$ref->{'username2'}} = [ $newr2, $newrd2, $ref->{'eptime'} ];

diff --git a/www/add-double-result.pl b/www/add-double-result.pl
index 07cefa8bd2b1f6084639330158ae1b18816ae5b4..0f269ab42e0b162a0fe050e909ecf567a8a8adf6 100755 (executable)
--- a/www/add-double-result.pl
+++ b/www/add-double-result.pl
@@ -67,14 +67,14 @@ my (undef, $newrd2_2) = foosball::calc_rating($rating2_2, $rd2_2, $rating_team1,
 
 $dbh->do('INSERT INTO double_results (gametime,team1_username1,team1_username2,team2_username1,team2_username2,score1,score2) VALUES (CURRENT_TIMESTAMP,?,?,?,?,?,?)',
        undef, $team1_username1, $team1_username2, $team2_username1, $team2_username2, $score1, $score2);
-$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
-       undef, $team1_username1, $newr1_1, $newrd1_1);
-$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
-       undef, $team1_username2, $newr1_2, $newrd1_2);
-$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
-       undef, $team2_username1, $newr2_1, $newrd2_1);
-$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
-       undef, $team2_username2, $newr2_2, $newrd2_2);
+$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?,?)',
+       undef, $team1_username1, $newr1_1, $newrd1_1, $newr1_1-$rating1_1);
+$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?,?)',
+       undef, $team1_username2, $newr1_2, $newrd1_2, $newr1_2-$rating1_2);
+$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?,?)',
+       undef, $team2_username1, $newr2_1, $newrd2_1, $newr2_1-$rating2_1);
+$dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?,?)',
+       undef, $team2_username2, $newr2_2, $newrd2_2, $newr2_2-$rating2_2);
 
 $dbh->commit;
 

diff --git a/www/add-single-result.pl b/www/add-single-result.pl
index 0037020197c48c3fdbde95f65e430ebfc747d2f3..76edbd043286c37011975375fbe977a7737e99cb 100755 (executable)
--- a/www/add-single-result.pl
+++ b/www/add-single-result.pl
@@ -42,10 +42,10 @@ my ($newr2, $newrd2) = foosball::calc_rating($rating2, $rd2, $rating1, $rd1, $sc
 
 $dbh->do('INSERT INTO single_results (gametime,username1,username2,score1,score2) VALUES (CURRENT_TIMESTAMP,?,?,?,?)',
        undef, $username1, $username2, $score1, $score2);
-$dbh->do('INSERT INTO single_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
-       undef, $username1, $newr1, $newrd1);
-$dbh->do('INSERT INTO single_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?)',
-       undef, $username2, $newr2, $newrd2);
+$dbh->do('INSERT INTO single_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?,?)',
+       undef, $username1, $newr1, $newrd1, $newr1-$rating1);
+$dbh->do('INSERT INTO single_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,?,?,?)',
+       undef, $username2, $newr2, $newrd2, $newr2-$rating2);
 
 $dbh->commit;
 

diff --git a/www/index.pl b/www/index.pl
index a6b8cf043fdb92fb6cb945c627f9d7179875d814..6f77c3323de3117a4bdae5d32a0c8ff734472029 100755 (executable)
--- a/www/index.pl
+++ b/www/index.pl
@@ -62,9 +62,49 @@ while (my $ref = $q->fetchrow_hashref) {
 
 # Last games
 my @last_games = ();
-$q = $dbh->prepare('select * from ( select to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,\'Double\' as type,team1_username1 || \' / \' || team1_username2 as username1, team2_username1 || \' / \' || team2_username2 as username2,score1,score2 from double_results union all select to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,\'Single\' as type,username1,username2,score1,score2 from single_results ) t1 order by gametime desc limit 10');
+$q = $dbh->prepare('
+select * from (
+    select
+        to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,
+        \'Double\' as type,
+       team1_username1 || \' / \' || team1_username2 as username1,
+        team2_username1 || \' / \' || team2_username2 as username2,
+       score1,
+       score2,
+       ra1.rating_diff as diff1, 
+       ra2.rating_diff as diff2
+    from
+        double_results re
+        join double_rating ra1
+            on re.gametime=ra1.ratetime
+           and re.team1_username1=ra1.username
+       join double_rating ra2
+           on re.gametime=ra2.ratetime
+           and re.team2_username1=ra2.username
+    union all
+    select
+        to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,
+       \'Single\' as type,
+       username1,
+       username2,
+       score1,
+       score2,
+       ra1.rating_diff as diff1, 
+       ra2.rating_diff as diff2
+    from
+        single_results re
+        join single_rating ra1
+            on re.gametime=ra1.ratetime
+           and re.username1=ra1.username
+       join single_rating ra2
+           on re.gametime=ra2.ratetime
+           and re.username2=ra2.username
+) t1
+order by gametime desc limit 10');
 $q->execute();
 while (my $ref = $q->fetchrow_hashref) {
+       $ref->{'diff1'} = sprintf "%+d", int($ref->{'diff1'} + 0.5);
+       $ref->{'diff2'} = sprintf "%+d", int($ref->{'diff2'} + 0.5);
        push @last_games, $ref;
 }
 

diff --git a/www/index.xml b/www/index.xml
index dbd5359983acd332a1df0b1b3bd7a1a3fb90e867..df12da814e206a36603197a8bd6bc43d03f6995b 100644 (file)
--- a/www/index.xml
+++ b/www/index.xml
@@ -122,6 +122,7 @@
           <th>Type</th>
           <th colspan="2">Opponents</th>
          <th>Score</th>
+         <th colspan="2">Rating diff.</th>
        </tr>
       </thead>
       <tbody t:id="lastgames">
@@ -131,6 +132,8 @@
          <td><t:username1 /></td>
          <td><t:username2 /></td>
          <td><t:score1 /> &#8211; <t:score2 /></td>
+         <td><t:diff1 /></td>
+         <td><t:diff2 /></td>
        </tr>
       </tbody>
     </table>