From da3fa6c4de949158716dd130551cf6494f3766da Mon Sep 17 00:00:00 2001 From: "Steinar H. Gunderson" Date: Fri, 5 Oct 2007 23:27:57 +0200 Subject: [PATCH] Show the rating difference for each game. --- recalc-double-result.pl | 16 +++++++-------- recalc-single-result.pl | 8 ++++---- www/add-double-result.pl | 16 +++++++-------- www/add-single-result.pl | 8 ++++---- www/index.pl | 42 +++++++++++++++++++++++++++++++++++++++- www/index.xml | 3 +++ 6 files changed, 68 insertions(+), 25 deletions(-) diff --git a/recalc-double-result.pl b/recalc-double-result.pl index 388a177..2ccb6a7 100755 --- 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 02f421b..c67be84 100755 --- 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 07cefa8..0f269ab 100755 --- 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 0037020..76edbd0 100755 --- 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 a6b8cf0..6f77c33 100755 --- 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 dbd5359..df12da8 100644 --- a/www/index.xml +++ b/www/index.xml @@ -122,6 +122,7 @@ Type Opponents Score + Rating diff. @@ -131,6 +132,8 @@ – + + -- 2.39.2