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 from users');
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", int($rating-$oldrating+0.5));
28 'username' => $username,
29 'rating' => int($rating+0.5),
31 'lowerbound' => int($rating - 3.0*$rd + 0.5),
35 @single_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @single_top;
39 $q = $dbh->prepare('select username from users');
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);
46 my ($oldrating) = foosball::find_double_rating($dbh, $username, 'AND ratetime::date < current_date ');
49 if (defined($oldrating)) {
50 $trend = (sprintf "%+d", int($rating-$oldrating+0.5));
54 'username' => $username,
55 'rating' => int($rating+0.5),
57 'lowerbound' => int($rating - 3.0*$rd + 0.5),
61 @double_top = sort { $b->{'lowerbound'} <=> $a->{'lowerbound'} } @double_top;
68 gametime as sort_gametime,
69 to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,
71 team1_username1 || \' / \' || team1_username2 as username1,
72 team2_username1 || \' / \' || team2_username2 as username2,
75 ra1.rating_diff as diff1,
76 ra2.rating_diff as diff2,
77 ra3.rating_diff as diff3,
78 ra4.rating_diff as diff4
81 join double_rating ra1
82 on re.gametime=ra1.ratetime
83 and re.team1_username1=ra1.username
84 join double_rating ra2
85 on re.gametime=ra2.ratetime
86 and re.team1_username2=ra2.username
87 join double_rating ra3
88 on re.gametime=ra3.ratetime
89 and re.team2_username1=ra3.username
90 join double_rating ra4
91 on re.gametime=ra4.ratetime
92 and re.team2_username2=ra4.username
95 gametime as sort_gametime,
96 to_char(gametime, \'IYYY-MM-DD HH24:MI\') as gametime,
102 ra1.rating_diff as diff1,
104 ra2.rating_diff as diff3,
108 join single_rating ra1
109 on re.gametime=ra1.ratetime
110 and re.username1=ra1.username
111 join single_rating ra2
112 on re.gametime=ra2.ratetime
113 and re.username2=ra2.username
115 order by sort_gametime desc limit 10');
117 while (my $ref = $q->fetchrow_hashref) {
118 if (defined($ref->{'diff2'})) {
119 $ref->{'diff1'} = sprintf "%+d / %+d",
120 int($ref->{'diff1'} + 0.5),
121 int($ref->{'diff2'} + 0.5);
122 $ref->{'diff2'} = sprintf "%+d / %+d",
123 int($ref->{'diff3'} + 0.5),
124 int($ref->{'diff4'} + 0.5);
126 $ref->{'diff1'} = sprintf "%+d", int($ref->{'diff1'} + 0.5);
127 $ref->{'diff2'} = sprintf "%+d", int($ref->{'diff3'} + 0.5);
129 push @last_games, $ref;
134 print CGI->header(-type=>'application/xhtml+xml');
136 my $doc = XML::Template::process_file('index.xml', {
137 '#singletop' => XML::Template::alternate('tr/class', \@single_top, 'odd', 'even'),
138 '#doubletop' => XML::Template::alternate('tr/class', \@double_top, 'odd', 'even'),
139 '#lastgames' => XML::Template::alternate('tr/class', \@last_games, 'odd', 'even'),
141 print $doc->toString;