git.sesse.net Git - foosball/blob - www/index.pl

   1 #! /usr/bin/perl
   2 use strict;
   3 use warnings;
   4 use lib qw(/srv/bzr.sesse.net/www/xml-template/perl/);
   5 use XML::Template;
   6 use CGI;
   7 require '../foosball.pm',
   8
   9 my $dbh = foosball::db_connect();
  10
  11 # Single score board (whoa, inefficient)
  12 my @single_top = ();
  13 my $q = $dbh->prepare('select username from users');
  14 $q->execute();
  15 while (my $ref = $q->fetchrow_hashref) {
  16         my $username = $ref->{'username'};
  17         my ($rating, $rd) = foosball::find_single_rating($dbh, $username);
  18         next if ($rating == 0 || $rating == 1500);
  19
  20         my ($oldrating) = foosball::find_single_rating($dbh, $username, 'AND ratetime::date < current_date ');
  21
  22         my $trend = "";
  23         if (defined($oldrating)) {
  24                 $trend = (sprintf "%+d", int($rating-$oldrating+0.5));
  25         }
  26
  27         push @single_top, {
  28                 'username' => $username,
  29                 'rating' => int($rating+0.5),
  30                 'rd' => int($rd+0.5),
  31                 'lowerbound' => int($rating - 3.0*$rd + 0.5),
  32                 'trend' => $trend,
  33         };
  34 }
  35 @single_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @single_top;
  36
  37 # Double score board
  38 my @double_top = ();
  39 $q = $dbh->prepare('select username from users');
  40 $q->execute();
  41 while (my $ref = $q->fetchrow_hashref) {
  42         my $username = $ref->{'username'};
  43         my ($rating, $rd) = foosball::find_double_rating($dbh, $username);
  44         next if ($rating == 0 || $rating == 1500);
  45
  46         my ($oldrating) = foosball::find_double_rating($dbh, $username, 'AND ratetime::date < current_date ');
  47
  48         my $trend = "";
  49         if (defined($oldrating)) {
  50                 $trend = (sprintf "%+d", int($rating-$oldrating+0.5));
  51         }
  52
  53         push @double_top, {
  54                 'username' => $username,
  55                 'rating' => int($rating+0.5),
  56                 'rd' => int($rd+0.5),
  57                 'lowerbound' => int($rating - 3.0*$rd + 0.5),
  58                 'trend' => $trend,
  59         };
  60 }
  61 @double_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @double_top;
  62
  63 # Last games
  64 my @last_games = ();
  65 $q = $dbh->prepare('
  66 select * from (
  67     select
  68         to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,
  69         \'Double\' as type,
  70         team1_username1 || \' / \' || team1_username2 as username1,
  71         team2_username1 || \' / \' || team2_username2 as username2,
  72         score1,
  73         score2,
  74         ra1.rating_diff as diff1,
  75         ra2.rating_diff as diff2
  76     from
  77         double_results re
  78         join double_rating ra1
  79             on re.gametime=ra1.ratetime
  80             and re.team1_username1=ra1.username
  81         join double_rating ra2
  82             on re.gametime=ra2.ratetime
  83             and re.team2_username1=ra2.username
  84     union all
  85     select
  86         to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,
  87         \'Single\' as type,
  88         username1,
  89         username2,
  90         score1,
  91         score2,
  92         ra1.rating_diff as diff1,
  93         ra2.rating_diff as diff2
  94     from
  95         single_results re
  96         join single_rating ra1
  97             on re.gametime=ra1.ratetime
  98             and re.username1=ra1.username
  99         join single_rating ra2
 100             on re.gametime=ra2.ratetime
 101             and re.username2=ra2.username
 102 ) t1
 103 order by gametime desc limit 10');
 104 $q->execute();
 105 while (my $ref = $q->fetchrow_hashref) {
 106         $ref->{'diff1'} = sprintf "%+d", int($ref->{'diff1'} + 0.5);
 107         $ref->{'diff2'} = sprintf "%+d", int($ref->{'diff2'} + 0.5);
 108         push @last_games, $ref;
 109 }
 110
 111 $dbh->disconnect;
 112
 113 print CGI->header(-type=>'application/xhtml+xml');
 114
 115 my $doc = XML::Template::process_file('index.xml', {
 116         '#singletop' => \@single_top,
 117         '#doubletop' => \@double_top,
 118         '#lastgames' => \@last_games,
 119 });
 120 print $doc->toString;