--- /dev/null
+#include <stdio.h>
+#include <math.h>
+#include <assert.h>
+
+#include <vector>
+#include <algorithm>
+
+// integration step size
+static const double step_size = 10.0;
+
+using namespace std;
+
+double prob_score(double a, double rd);
+double prob_score_real(double a, double prodai, double rd_norm);
+double prodai(double a);
+
+// probability of match ending 10-a when winnerR - loserR = RD
+//
+// +inf
+// /
+// |
+// | Poisson[lambda1, t](a) * Erlang[lambda2, 10](t) dt
+// |
+// /
+// -inf
+//
+// where lambda1 = 1.0, lambda2 = 2^(rd/455)
+//
+// The constant of 455 is chosen carefully so to match with the
+// Glicko/Bradley-Terry assumption that a player rated 400 points over
+// his/her opponent will win with a probability of 10/11 =~ 0.90909.
+//
+double prob_score(double a, double rd)
+{
+ return prob_score_real(a, prodai(a), rd/455.0);
+}
+
+// Same, but takes in Product(a+i, i=1..9) as an argument in addition to a. Faster
+// if you already have that precomputed, and assumes rd is already divided by 455.
+double prob_score_real(double a, double prodai, double rd_norm)
+{
+ double nom =
+ pow(2.0, -a*rd_norm) * pow(2.0, 10.0*rd_norm) * pow(pow(2.0, -rd_norm) + 1.0, -a)
+ * prodai;
+ double denom = 362880 * pow(1.0 + pow(2.0, rd_norm), 10.0);
+ return nom/denom;
+}
+
+// Calculates Product(a+i, i=1..9) (see above).
+double prodai(double a)
+{
+ return (a+1)*(a+2)*(a+3)*(a+4)*(a+5)*(a+6)*(a+7)*(a+8)*(a+9);
+}
+
+//
+// Computes the integral
+//
+// +inf
+// /
+// |
+// | ProbScore[a] (r2-r1) Gaussian[mu2, sigma2] (dr2) dr2
+// |
+// /
+// -inf
+//
+// For practical reasons, -inf and +inf are replaced by 0 and 3000, which
+// is reasonable in the this context.
+//
+// The Gaussian is not normalized.
+//
+// Set the last parameter to 1.0 if player 1 won, or -1.0 if player 2 won.
+// In the latter case, ProbScore will be given (r1-r2) instead of (r2-r1).
+//
+double opponent_rating_pdf(double a, double r1, double mu2, double sigma2, double winfac)
+{
+ double sum = 0.0;
+ double prodai_precompute = prodai(a);
+ winfac /= 455.0;
+ for (double r2 = 0.0; r2 < 3000.0; r2 += step_size) {
+ double x = r2 + step_size*0.5;
+ double probscore = prob_score_real(a, prodai_precompute, (r1 - x)*winfac);
+ double z = (x - mu2)/sigma2;
+ double gaussian = exp(-(z*z/2.0));
+ sum += step_size * probscore * gaussian;
+ }
+ return sum;
+}
+
+// normalize the curve so we know that A ~= 1
+void normalize(vector<pair<double, double> > &curve)
+{
+ double peak = 0.0;
+ for (vector<pair<double, double> >::const_iterator i = curve.begin(); i != curve.end(); ++i) {
+ peak = max(peak, i->second);
+ }
+
+ double invpeak = 1.0 / peak;
+ for (vector<pair<double, double> >::iterator i = curve.begin(); i != curve.end(); ++i) {
+ i->second *= invpeak;
+ }
+}
+
+// computes matA * matB
+void mat_mul(double *matA, unsigned ah, unsigned aw,
+ double *matB, unsigned bh, unsigned bw,
+ double *result)
+{
+ assert(aw == bh);
+ for (unsigned y = 0; y < bw; ++y) {
+ for (unsigned x = 0; x < ah; ++x) {
+ double sum = 0.0;
+ for (unsigned c = 0; c < aw; ++c) {
+ sum += matA[c*ah + x] * matB[y*bh + c];
+ }
+ result[y*bw + x] = sum;
+ }
+ }
+}
+
+// computes matA^T * matB
+void mat_mul_trans(double *matA, unsigned ah, unsigned aw,
+ double *matB, unsigned bh, unsigned bw,
+ double *result)
+{
+ assert(ah == bh);
+ for (unsigned y = 0; y < bw; ++y) {
+ for (unsigned x = 0; x < aw; ++x) {
+ double sum = 0.0;
+ for (unsigned c = 0; c < ah; ++c) {
+ sum += matA[x*ah + c] * matB[y*bh + c];
+ }
+ result[y*bw + x] = sum;
+ }
+ }
+}
+
+void print3x3(double *M)
+{
+ printf("%f %f %f\n", M[0], M[3], M[6]);
+ printf("%f %f %f\n", M[1], M[4], M[7]);
+ printf("%f %f %f\n", M[2], M[5], M[8]);
+}
+
+void print3x1(double *M)
+{
+ printf("%f\n", M[0]);
+ printf("%f\n", M[1]);
+ printf("%f\n", M[2]);
+}
+
+// solves Ax = B by Gauss-Jordan elimination, where A is a 3x3 matrix,
+// x is a column vector of length 3 and B is a row vector of length 3.
+// Destroys its input in the process.
+void solve3x3(double *A, double *x, double *B)
+{
+ // row 1 -= row 0 * (a1/a0)
+ {
+ double f = A[1] / A[0];
+ A[1] = 0.0;
+ A[4] -= A[3] * f;
+ A[7] -= A[6] * f;
+
+ B[1] -= B[0] * f;
+ }
+
+ // row 2 -= row 0 * (a2/a0)
+ {
+ double f = A[2] / A[0];
+ A[2] = 0.0;
+ A[5] -= A[3] * f;
+ A[8] -= A[6] * f;
+
+ B[2] -= B[0] * f;
+ }
+
+ // row 2 -= row 1 * (a5/a4)
+ {
+ double f = A[5] / A[4];
+ A[5] = 0.0;
+ A[8] -= A[7] * f;
+
+ B[2] -= B[1] * f;
+ }
+
+ // back substitute:
+
+ // row 1 -= row 2 * (a7/a8)
+ {
+ double f = A[7] / A[8];
+ A[7] = 0.0;
+
+ B[1] -= B[2] * f;
+ }
+
+ // row 0 -= row 2 * (a6/a8)
+ {
+ double f = A[6] / A[8];
+ A[6] = 0.0;
+
+ B[0] -= B[2] * f;
+ }
+
+ // row 0 -= row 1 * (a3/a4)
+ {
+ double f = A[3] / A[4];
+ A[3] = 0.0;
+
+ B[0] -= B[1] * f;
+ }
+
+ // normalize
+ x[0] = B[0] / A[0];
+ x[1] = B[1] / A[4];
+ x[2] = B[2] / A[8];
+}
+
+// Give an OK starting estimate for the least squares, by numerical integration
+// of x*f(x) and x^2 * f(x). Somehow seems to underestimate sigma, though.
+void estimate_musigma(vector<pair<double, double> > &curve, double &mu_result, double &sigma_result)
+{
+ double mu = 0.0;
+ double sigma = 0.0;
+ double sum_area = 0.0;
+
+ for (unsigned i = 1; i < curve.size(); ++i) {
+ double x1 = curve[i].first;
+ double x0 = curve[i-1].first;
+ double y1 = curve[i].second;
+ double y0 = curve[i-1].second;
+ double xm = 0.5 * (x0 + x1);
+ double ym = 0.5 * (y0 + y1);
+ sum_area += (x1-x0) * ym;
+ mu += (x1-x0) * xm * ym;
+ sigma += (x1-x0) * xm * xm * ym;
+ }
+
+ mu_result = mu / sum_area;
+ sigma_result = sqrt(sigma) / sum_area;
+}
+
+// Find best fit of the data in curves to a Gaussian pdf, based on the
+// given initial estimates. Works by nonlinear least squares, iterating
+// until we're below a certain threshold.
+void least_squares(vector<pair<double, double> > &curve, double mu1, double sigma1, double &mu_result, double &sigma_result)
+{
+ double A = 1.0;
+ double mu = mu1;
+ double sigma = sigma1;
+
+ // column-major
+ double matA[curve.size() * 3]; // N x 3
+ double dbeta[curve.size()]; // N x 1
+
+ // A^T * A: 3xN * Nx3 = 3x3
+ double matATA[3*3];
+
+ // A^T * dβ: 3xN * Nx1 = 3x1
+ double matATdb[3];
+
+ double dlambda[3];
+
+ for ( ;; ) {
+ //printf("A=%f mu=%f sigma=%f\n", A, mu, sigma);
+
+ // fill in A (depends only on x_i, A, mu, sigma -- not y_i)
+ for (unsigned i = 0; i < curve.size(); ++i) {
+ double x = curve[i].first;
+
+ // df/dA(x_i)
+ matA[i + 0 * curve.size()] =
+ exp(-(x-mu)*(x-mu)/(2.0*sigma*sigma));
+
+ // df/dµ(x_i)
+ matA[i + 1 * curve.size()] =
+ A * (x-mu)/(sigma*sigma) * matA[i + 0 * curve.size()];
+
+ // df/dσ(x_i)
+ matA[i + 2 * curve.size()] =
+ matA[i + 1 * curve.size()] * (x-mu)/sigma;
+ }
+
+ // find dβ
+ for (unsigned i = 0; i < curve.size(); ++i) {
+ double x = curve[i].first;
+ double y = curve[i].second;
+
+ dbeta[i] = y - A * exp(- (x-mu)*(x-mu)/(2.0*sigma*sigma));
+ }
+
+ // compute a and b
+ mat_mul_trans(matA, curve.size(), 3, matA, curve.size(), 3, matATA);
+ mat_mul_trans(matA, curve.size(), 3, dbeta, curve.size(), 1, matATdb);
+
+ // solve
+ solve3x3(matATA, dlambda, matATdb);
+
+ A += dlambda[0];
+ mu += dlambda[1];
+ sigma += dlambda[2];
+
+ // terminate when we're down to three digits
+ if (fabs(dlambda[0]) <= 1e-3 && fabs(dlambda[1]) <= 1e-3 && fabs(dlambda[2]) <= 1e-3)
+ break;
+ }
+
+ mu_result = mu;
+ sigma_result = sigma;
+}
+
+int main(int argc, char **argv)
+{
+ double mu1 = atof(argv[1]);
+ double sigma1 = atof(argv[2]);
+ double mu2 = atof(argv[3]);
+ double sigma2 = atof(argv[4]);
+ int score1 = atoi(argv[5]);
+ int score2 = atoi(argv[6]);
+ vector<pair<double, double> > curve;
+
+ if (score1 == 10) {
+ for (double r1 = 0.0; r1 < 3000.0; r1 += step_size) {
+ double z = (r1 - mu1) / sigma1;
+ double gaussian = exp(-(z*z/2.0));
+ curve.push_back(make_pair(r1, gaussian * opponent_rating_pdf(score2, r1, mu2, sigma2, 1.0)));
+ }
+ } else {
+ for (double r1 = 0.0; r1 < 3000.0; r1 += step_size) {
+ double z = (r1 - mu1) / sigma1;
+ double gaussian = exp(-(z*z/2.0));
+ curve.push_back(make_pair(r1, gaussian * opponent_rating_pdf(score1, r1, mu2, sigma2, -1.0)));
+ }
+ }
+
+ double mu_est, sigma_est, mu, sigma;
+ normalize(curve);
+ estimate_musigma(curve, mu_est, sigma_est);
+ least_squares(curve, mu_est, sigma_est, mu, sigma);
+ printf("%f %f\n", mu, sigma);
+}
--- /dev/null
+use strict;
+use warnings;
+use DBI;
+
+package foosball;
+
+sub db_connect {
+ my $dbh = DBI->connect("dbi:Pg:dbname=foosball;host=127.0.0.1", "foosball", "cleanrun", {AutoCommit => 0});
+ $dbh->{RaiseError} = 1;
+ return $dbh;
+}
+
+sub find_single_rating {
+ my ($dbh, $username, $limit) = @_;
+ my ($age, $rating, $rd) = $dbh->selectrow_array('SELECT EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP-ratetime)), rating, rd FROM single_rating WHERE username=? '.$limit.' ORDER BY ratetime DESC LIMIT 1',
+ undef, $username);
+ $rd = apply_aging($rd, $age / 86400.0);
+ return ($rating, $rd);
+}
+
+sub find_double_rating {
+ my ($dbh, $username, $limit) = @_;
+ my ($age, $rating, $rd) = $dbh->selectrow_array('SELECT EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP-ratetime)), rating, rd FROM double_rating WHERE username=? '.$limit.'ORDER BY ratetime DESC LIMIT 1',
+ undef, $username);
+ $rd = apply_aging($rd, $age / 86400.0);
+ return ($rating, $rd);
+}
+
+sub create_user_if_not_exists {
+ my ($dbh, $username) = @_;
+ my $count = $dbh->selectrow_array('SELECT count(*) FROM users WHERE username=?',
+ undef, $username);
+ return if ($count > 0);
+ $dbh->do('INSERT INTO users (username) VALUES (?)',
+ undef, $username);
+ $dbh->do('INSERT INTO single_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,1500.0,350.0)',
+ undef, $username);
+ $dbh->do('INSERT INTO double_rating (username,ratetime,rating,rd) VALUES (?,CURRENT_TIMESTAMP,1500.0,350.0)',
+ undef, $username);
+ return $dbh;
+}
+
+# 10-9 is 0.60
+# 10-0 is 1.00
+sub find_score {
+ my ($score1, $score2) = @_;
+ if ($score1 == 10) {
+ # yay, a win
+ return 0.60 + 0.40 * (9.0-$score2)/9.0;
+ }
+ if ($score2 == 10) {
+ # a loss
+ return 0.40 - 0.40 * (9.0-$score1)/9.0;
+ }
+ die "Nobody won?";
+}
+
+# c=8 => RD=50 moves to RD=350 over approx. five years
+our $c = 8;
+
+sub apply_aging {
+ my ($rd, $age) = @_;
+ $rd = sqrt($rd*$rd + $c * $c * ($age / 86400.0));
+ $rd = 350.0 if ($rd > 350.0);
+ return $rd;
+}
+
+our $q = log(10)/400.0;
+our $pi = 3.1415926535897932384626433832795;
+
+# computes a^b
+sub pow {
+ my ($a, $b) = @_;
+
+ # a^b = exp(log(a^b)) = exp(b log a)
+ return exp($b * log($a));
+}
+
+sub g {
+ my $rd = shift;
+ return 1.0 / sqrt(1.0 + 3.0 * $q * $q * $rd * $rd / ($pi*$pi));
+}
+
+sub E {
+ my ($r, $rating, $rd) = @_;
+ return 1.0 / (1.0 + pow(10.0, -g($rd) * ($r - $rating) / 400.0));
+}
+
+sub dsq {
+ my ($r, $rating, $rd) = @_;
+ return 1.0 / ($q*$q * g($rd) * g($rd) * E($r, $rating, $rd) * (1 - E($r, $rating, $rd)));
+}
+
+sub calc_rating {
+ my ($rating1, $rd1, $rating2, $rd2, $score1, $score2) = @_;
+ my $s1 = find_score($score1, $score2);
+ my $d1sq = dsq($rating1, $rating2, $rd2);
+ my $newr1 = $rating1 + ($q / (1.0/($rd1*$rd1) + 1.0 / $d1sq)) * g($rd2) * ($s1 - E($rating1, $rating2, $rd2));
+ my $newrd1 = sqrt(1.0 / (1.0 / ($rd1*$rd1) + 1.0 / $d1sq));
+
+ $newrd1 = 30.0 if ($newrd1 < 30.0);
+
+ return ($newr1, $newrd1);
+}
+
+1;
--- /dev/null
+#! /usr/bin/perl
+use strict;
+use warnings;
+use DBI;
+use CGI;
+use CGI::Carp qw(fatalsToBrowser);
+require '../foosball.pm';
+
+my $cgi = CGI->new;
+
+my $team1_username1 = $cgi->param('team1_username1');
+$team1_username1 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 1/1";
+$team1_username1 = $1;
+
+my $team1_username2 = $cgi->param('team1_username2');
+$team1_username2 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 1/2";
+$team1_username2 = $1;
+
+my $team2_username1 = $cgi->param('team2_username1');
+$team2_username1 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 2/1";
+$team2_username1 = $1;
+
+my $team2_username2 = $cgi->param('team2_username2');
+$team2_username2 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 2/2";
+$team2_username2 = $1;
+
+my $score1 = $cgi->param('score1');
+$score1 =~ /^([0-9]|10)$/ or die "Invalid score 1";
+$score1 = $1;
+
+my $score2 = $cgi->param('score2');
+$score2 =~ /^([0-9]|10)$/ or die "Invalid score 2";
+$score2 = $1;
+
+my $dbh = foosball::db_connect();
+$dbh->{AutoCommit} = 0;
+
+foosball::create_user_if_not_exists($dbh, $team1_username1);
+foosball::create_user_if_not_exists($dbh, $team1_username2);
+foosball::create_user_if_not_exists($dbh, $team2_username1);
+foosball::create_user_if_not_exists($dbh, $team2_username2);
+$dbh->commit;
+
+# fetch the previous double ratings
+my ($rating1_1, $rd1_1) = foosball::find_double_rating($dbh, $team1_username1);
+my ($rating1_2, $rd1_2) = foosball::find_double_rating($dbh, $team1_username2);
+my ($rating2_1, $rd2_1) = foosball::find_double_rating($dbh, $team2_username1);
+my ($rating2_2, $rd2_2) = foosball::find_double_rating($dbh, $team2_username2);
+
+# make virtual "team players"
+my $rating_team1 = 0.5 * ($rating1_1 + $rating1_2);
+my $rd_team1 = sqrt($rd1_1 * $rd1_1 + $rd1_2 * $rd1_2) / sqrt(2.0);
+my $rating_team2 = 0.5 * ($rating1_2 + $rating2_2);
+my $rd_team2 = sqrt($rd2_1 * $rd2_1 + $rd2_2 * $rd2_2) / sqrt(2.0);
+
+my $q = $foosball::q;
+
+my ($new_t1r, undef) = foosball::calc_rating($rating_team1, $rd_team1, $rating_team2, $rd_team2, $score1, $score2);
+my ($new_t2r, undef) = foosball::calc_rating($rating_team2, $rd_team2, $rating_team1, $rd_team1, $score2, $score1);
+my $newr1_1 = $rating1_1 + ($new_t1r - $rating_team1);
+my $newr1_2 = $rating1_2 + ($new_t1r - $rating_team1);
+my $newr2_1 = $rating2_1 + ($new_t2r - $rating_team2);
+my $newr2_2 = $rating2_2 + ($new_t2r - $rating_team2);
+
+my (undef, $newrd1_1) = foosball::calc_rating($rating1_1, $rd1_1, $rating_team2, $rd_team2, $score1, $score2);
+my (undef, $newrd1_2) = foosball::calc_rating($rating1_2, $rd1_2, $rating_team2, $rd_team2, $score1, $score2);
+my (undef, $newrd2_1) = foosball::calc_rating($rating2_1, $rd2_1, $rating_team1, $rd_team1, $score2, $score1);
+my (undef, $newrd2_2) = foosball::calc_rating($rating2_2, $rd2_2, $rating_team1, $rd_team1, $score2, $score1);
+
+$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->commit;
+
+print $cgi->header(-status=>'303 See Other',
+ -location=>'http://foosball.sesse.net/');
+
--- /dev/null
+#! /usr/bin/perl
+use strict;
+use warnings;
+use DBI;
+use CGI;
+use CGI::Carp qw(fatalsToBrowser);
+require '../foosball.pm';
+
+my $cgi = CGI->new;
+
+my $username1 = $cgi->param('username1');
+$username1 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 1";
+$username1 = $1;
+
+my $username2 = $cgi->param('username2');
+$username2 =~ /^([a-z][a-z0-9]*)$/ or die "Invalid user name 2";
+$username2 = $1;
+
+my $score1 = $cgi->param('score1');
+$score1 =~ /^([0-9]|10)$/ or die "Invalid score 1";
+$score1 = $1;
+
+my $score2 = $cgi->param('score2');
+$score2 =~ /^([0-9]|10)$/ or die "Invalid score 2";
+$score2 = $1;
+
+my $dbh = foosball::db_connect();
+$dbh->{AutoCommit} = 0;
+
+foosball::create_user_if_not_exists($dbh, $username1);
+foosball::create_user_if_not_exists($dbh, $username2);
+$dbh->commit;
+
+# fetch the previous single ratings
+my ($rating1, $rd1) = foosball::find_single_rating($dbh, $username1);
+my ($rating2, $rd2) = foosball::find_single_rating($dbh, $username2);
+
+my $q = $foosball::q;
+
+my ($newr1, $newrd1) = foosball::calc_rating($rating1, $rd1, $rating2, $rd2, $score1, $score2);
+my ($newr2, $newrd2) = foosball::calc_rating($rating2, $rd2, $rating1, $rd1, $score2, $score1);
+
+my $d2sq = foosball::dsq($rating2, $rating1, $rd1);
+my $newr2 = $rating2 + ($q / (1.0/($rd2*$rd2) + 1.0 / $d2sq)) * foosball::g($rd1) * ($s2 - foosball::E($rating2, $rating1, $rd1));
+my $newrd2 = sqrt(1.0 / (1.0 / ($rd2*$rd2) + 1.0 / $d2sq));
+
+$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->commit;
+
+print $cgi->header(-status=>'303 See Other',
+ -location=>'http://foosball.sesse.net/');
+
--- /dev/null
+#! /usr/bin/perl
+use strict;
+use warnings;
+use lib qw(/srv/bzr.sesse.net/www/xml-template/perl/);
+use XML::Template;
+use CGI;
+require '../foosball.pm',
+
+my $dbh = foosball::db_connect();
+
+# Single score board (whoa, inefficient)
+my @single_top = ();
+my $q = $dbh->prepare('select username from users');
+$q->execute();
+while (my $ref = $q->fetchrow_hashref) {
+ my $username = $ref->{'username'};
+ my ($rating, $rd) = foosball::find_single_rating($dbh, $username);
+ my ($oldrating) = foosball::find_single_rating($dbh, $username, 'AND ratetime::date < current_date ');
+
+ my $trend = "";
+ if (defined($oldrating)) {
+ $trend = (sprintf "%+d", int($rating-$oldrating+0.5));
+ }
+
+ push @single_top, {
+ 'username' => $username,
+ 'rating' => int($rating+0.5),
+ 'rd' => int($rd+0.5),
+ 'lowerbound' => int($rating - 3.0*$rd + 0.5),
+ 'trend' => $trend,
+ };
+}
+@single_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @single_top;
+
+# Double score board
+my @double_top = ();
+$q = $dbh->prepare('select username from users');
+$q->execute();
+while (my $ref = $q->fetchrow_hashref) {
+ my $username = $ref->{'username'};
+ my ($rating, $rd) = foosball::find_double_rating($dbh, $username);
+ my ($oldrating) = foosball::find_double_rating($dbh, $username, 'AND ratetime::date < current_date ');
+
+ my $trend = "";
+ if (defined($oldrating)) {
+ $trend = (sprintf "%+d", int($rating-$oldrating+0.5));
+ }
+
+ push @double_top, {
+ 'username' => $username,
+ 'rating' => int($rating+0.5),
+ 'rd' => int($rd+0.5),
+ 'lowerbound' => int($rating - 3.0*$rd + 0.5),
+ 'trend' => $trend,
+ };
+}
+@double_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @double_top;
+
+# 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->execute();
+while (my $ref = $q->fetchrow_hashref) {
+ push @last_games, $ref;
+}
+
+$dbh->disconnect;
+
+print CGI->header(-type=>'application/xhtml+xml');
+
+my $doc = XML::Template::process_file('index.xml', {
+ '#singletop' => \@single_top,
+ '#doubletop' => \@double_top,
+ '#lastgames' => \@last_games,
+});
+print $doc->toString;
--- /dev/null
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE
+ html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
+ "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
+<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" xmlns:t="http://template.sesse.net/">
+ <head>
+ <title>Foosball!</title>
+ <link rev="made" href="mailto:sgunderson@bigfoot.com" />
+ <meta name="MSSmartTagsPreventParsing" content="TRUE" />
+ </head>
+ <body>
+ <h1>Foosball!</h1>
+
+ <h2>Add a singles result</h2>
+
+ <form method="post" action="add-single-result.pl">
+ <table>
+ <tr>
+ <th>User name 1</th>
+ <td><input name="username1" value="" size="10" /></td>
+ </tr>
+ <tr>
+ <th>User name 2</th>
+ <td><input name="username2" value="" size="10" /></td>
+ </tr>
+ <tr>
+ <th>Score</th>
+ <td>
+ <input name="score1" value="" size="2" /> -
+ <input name="score2" value="" size="2" />
+ </td>
+ </tr>
+ <tr>
+ <td colspan="2"><input type="submit" /></td>
+ </tr>
+ </table>
+ </form>
+
+ <h2>Add a doubles result</h2>
+
+ <form method="post" action="add-double-result.pl">
+ <table>
+ <tr>
+ <th>Team 1 (usernames)</th>
+ <td>
+ <input name="team1_username1" value="" size="10" /> and
+ <input name="team1_username2" value="" size="10" />
+ </td>
+ </tr>
+ <tr>
+ <th>Team 2 (usernames)</th>
+ <td>
+ <input name="team2_username1" value="" size="10" /> and
+ <input name="team2_username2" value="" size="10" />
+ </td>
+ </tr>
+ <tr>
+ <th>Score</th>
+ <td>
+ <input name="score1" value="" size="2" /> -
+ <input name="score2" value="" size="2" />
+ </td>
+ </tr>
+ <tr>
+ <td colspan="2"><input type="submit" /></td>
+ </tr>
+ </table>
+ </form>
+
+ <h2>Singles score board</h2>
+
+ <table>
+ <thead>
+ <tr>
+ <th>Username</th>
+ <th>Rating mean</th>
+ <th>Rating deviation</th>
+ <th>Conservative estimate</th>
+ <th>Change since yesterday</th>
+ </tr>
+ </thead>
+ <tbody t:id="singletop">
+ <tr>
+ <td><t:username /></td>
+ <td><t:rating /></td>
+ <td><t:rd /></td>
+ <td><t:lowerbound /></td>
+ <td><t:trend /></td>
+ </tr>
+ </tbody>
+ </table>
+
+ <h2>Doubles score board</h2>
+
+ <table>
+ <thead>
+ <tr>
+ <th>Username</th>
+ <th>Rating mean</th>
+ <th>Rating deviation</th>
+ <th>Conservative estimate</th>
+ <th>Change since yesterday</th>
+ </tr>
+ </thead>
+ <tbody t:id="doubletop">
+ <tr>
+ <td><t:username /></td>
+ <td><t:rating /></td>
+ <td><t:rd /></td>
+ <td><t:lowerbound /></td>
+ <td><t:trend /></td>
+ </tr>
+ </tbody>
+ </table>
+
+ <h2>Last ten games</h2>
+
+ <table>
+ <thead>
+ <tr>
+ <th>Registered</th>
+ <th>Type</th>
+ <th colspan="2">Opponents</th>
+ <th>Score</th>
+ </tr>
+ </thead>
+ <tbody t:id="lastgames">
+ <tr>
+ <td><t:gametime /></td>
+ <td><t:type /></td>
+ <td><t:username1 /></td>
+ <td><t:username2 /></td>
+ <td><t:score1 /> – <t:score2 /></td>
+ </tr>
+ </tbody>
+ </table>
+
+ <h2>About the ratings</h2>
+
+ <p>The rating system is a modified Glicko 1 variant, adjusted for teams
+ (with some ideas from Microsoft's TrueSkill system) and non-binary results.
+ For those not familiar with these ratings, the most important parts are:</p>
+
+ <ul>
+ <li>Your rating is a <em>statistical estimation</em> of your true skill.
+ It has a mean (the point estimate of your skill) and a deviation
+ (measuring the uncertainity of the estimate), called the RD. It is approximately
+ Gaussian (actually logistic).</li>
+ <li>When you win or lose a game, your rating will change accordingly,
+ based on your score and your opponent. <em>You do not get 'points'
+ for winning or losing, the estimate is merely getting more accurate.</em>
+ In the process, the RD gets lower as you play. However, the RD increases
+ with time, opening up for the fact that your true skill can change.
+ (Glicko 2 also supports a volatility measure, which better models change
+ in true skill, but it has not been implemented here.)</li>
+ <li>The score board is sorted by a conservative estimate of your rating
+ (mean - 3 * RD). This makes it non-attractive for people with artifically
+ high ratings (especially newcomers) to avoid playing to stay high up in
+ the score board.</li>
+ <li>The single and double rankings are separate. Even though you play as a
+ team and all four players' rankings and RDs influence the rating adjustment,
+ you are ranked as an individual, as we do not usually play with fixed
+ teams.</li>
+ </ul>
+ </body>
+</html>
+
projects / foosball / commitdiff