4 use lib qw(/srv/bzr.sesse.net/www/xml-template/perl/);
7 require '../foosball.pm',
9 my $dbh = foosball::db_connect();
11 # Single score board (whoa, inefficient)
13 my $q = $dbh->prepare('select username,count(*) as num_games from single_rating group by username');
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);
20 my ($oldrating) = foosball::find_single_rating($dbh, $username, 'AND ratetime::date < current_date ');
23 if (defined($oldrating)) {
24 $trend = (sprintf "%+d", foosball::round($rating-$oldrating));
28 'username' => $username,
29 'rating' => foosball::round($rating),
30 'rd' => foosball::round($rd),
31 'lowerbound' => foosball::round($rating - 3.0*$rd),
33 'numgames' => $ref->{'num_games'},
36 @single_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @single_top;
40 $q = $dbh->prepare('select username,count(*) as num_games from double_rating group by username');
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);
47 my ($oldrating) = foosball::find_double_rating($dbh, $username, 'AND ratetime::date < current_date ');
50 if (defined($oldrating)) {
51 $trend = (sprintf "%+d", foosball::round($rating-$oldrating));
55 'username' => $username,
56 'rating' => foosball::round($rating),
57 'rd' => foosball::round($rd),
58 'lowerbound' => foosball::round($rating - 3.0*$rd),
60 'numgames' => $ref->{'num_games'},
63 @double_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @double_top;
70 gametime as sort_gametime,
71 to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,
73 team1_username1 || \' / \' || team1_username2 as username1,
74 team2_username1 || \' / \' || team2_username2 as username2,
77 ra1.rating_diff as diff1,
78 ra2.rating_diff as diff2,
79 ra3.rating_diff as diff3,
80 ra4.rating_diff as diff4
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
97 gametime as sort_gametime,
98 to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,
104 ra1.rating_diff as diff1,
106 ra2.rating_diff as diff3,
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
117 order by sort_gametime desc limit 10');
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'});
128 $ref->{'diff1'} = sprintf "%+d", foosball::round($ref->{'diff1'});
129 $ref->{'diff2'} = sprintf "%+d", foosball::round($ref->{'diff3'});
131 push @last_games, $ref;
136 print CGI->header(-type=>'application/xhtml+xml');
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'),
143 print $doc->toString;