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,count(*) as num_games from single_rating group by username');
  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", foosball::round($rating-$oldrating));
  25         }
  26
  27         push @single_top, {
  28                 'username' => $username,
  29                 'rating' => foosball::round($rating),
  30                 'rd' => foosball::round($rd),
  31                 'lowerbound' => foosball::round($rating - 3.0*$rd),
  32                 'trend' => $trend,
  33                 'numgames' => $ref->{'num_games'},
  34         };
  35 }
  36 @single_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @single_top;
  37
  38 # Double score board
  39 my @double_top = ();
  40 $q = $dbh->prepare('select username,count(*) as num_games from double_rating group by username');
  41 $q->execute();
  42 while (my $ref = $q->fetchrow_hashref) {
  43         my $username = $ref->{'username'};
  44         my ($rating, $rd) = foosball::find_double_rating($dbh, $username);
  45         next if ($rating == 0 || $rating == 1500);
  46
  47         my ($oldrating) = foosball::find_double_rating($dbh, $username, 'AND ratetime::date < current_date ');
  48
  49         my $trend = "";
  50         if (defined($oldrating)) {
  51                 $trend = (sprintf "%+d", foosball::round($rating-$oldrating));
  52         }
  53
  54         push @double_top, {
  55                 'username' => $username,
  56                 'rating' => foosball::round($rating),
  57                 'rd' => foosball::round($rd),
  58                 'lowerbound' => foosball::round($rating - 3.0*$rd),
  59                 'trend' => $trend,
  60                 'numgames' => $ref->{'num_games'},
  61         };
  62 }
  63 @double_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @double_top;
  64
  65 # Last games
  66 my @last_games = ();
  67 $q = $dbh->prepare('
  68 select * from (
  69     select
  70         gametime as sort_gametime,
  71         to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,
  72         \'Double\' as type,
  73         team1_username1 || \' / \' || team1_username2 as username1,
  74         team2_username1 || \' / \' || team2_username2 as username2,
  75         score1,
  76         score2,
  77         ra1.rating_diff as diff1,
  78         ra2.rating_diff as diff2,
  79         ra3.rating_diff as diff3,
  80         ra4.rating_diff as diff4
  81     from
  82         double_results re
  83         join double_rating ra1
  84             on re.gametime=ra1.ratetime
  85             and re.team1_username1=ra1.username
  86         join double_rating ra2
  87             on re.gametime=ra2.ratetime
  88             and re.team1_username2=ra2.username
  89         join double_rating ra3
  90             on re.gametime=ra3.ratetime
  91             and re.team2_username1=ra3.username
  92         join double_rating ra4
  93             on re.gametime=ra4.ratetime
  94             and re.team2_username2=ra4.username
  95     union all
  96     select
  97         gametime as sort_gametime,
  98         to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,
  99         \'Single\' as type,
 100         username1,
 101         username2,
 102         score1,
 103         score2,
 104         ra1.rating_diff as diff1,
 105         null as diff2,
 106         ra2.rating_diff as diff3,
 107         null as diff4
 108     from
 109         single_results re
 110         join single_rating ra1
 111             on re.gametime=ra1.ratetime
 112             and re.username1=ra1.username
 113         join single_rating ra2
 114             on re.gametime=ra2.ratetime
 115             and re.username2=ra2.username
 116 ) t1
 117 order by sort_gametime desc limit 10');
 118 $q->execute();
 119 while (my $ref = $q->fetchrow_hashref) {
 120         if (defined($ref->{'diff2'})) {
 121                 $ref->{'diff1'} = sprintf "%+d / %+d",
 122                         foosball::round($ref->{'diff1'}),
 123                         foosball::round($ref->{'diff2'});
 124                 $ref->{'diff2'} = sprintf "%+d / %+d",
 125                         foosball::round($ref->{'diff3'}),
 126                         foosball::round($ref->{'diff4'});
 127         } else {
 128                 $ref->{'diff1'} = sprintf "%+d", foosball::round($ref->{'diff1'});
 129                 $ref->{'diff2'} = sprintf "%+d", foosball::round($ref->{'diff3'});
 130         }
 131         push @last_games, $ref;
 132 }
 133
 134 $dbh->disconnect;
 135
 136 print CGI->header(-type=>'application/xhtml+xml');
 137
 138 my $doc = XML::Template::process_file('index.xml', {
 139         '#singletop' => XML::Template::alternate('tr/class', \@single_top, 'odd', 'even'),
 140         '#doubletop' => XML::Template::alternate('tr/class', \@double_top, 'odd', 'even'),
 141         '#lastgames' => XML::Template::alternate('tr/class', \@last_games, 'odd', 'even'),
 142 });
 143 print $doc->toString;