]> git.sesse.net Git - wloh/blobdiff - www/rating.pl
Show average ratings for each subdivision.
[wloh] / www / rating.pl
index 604ca289595c00ae294e92adf919d78192881dc6..26ced551e0a0c03ce50dade5ed14ae3fa2165ee8 100755 (executable)
@@ -84,8 +84,23 @@ printf <<"EOF", $params{-3}, $match_stddev;
       </tr>
 EOF
 
-$q = $dbh->prepare('SELECT divisjon,AVG(rating) AS avg_rating,STDDEV(rating) AS stddev_rating FROM ratings NATURAL JOIN siste_divisjon GROUP BY divisjon ORDER BY divisjon');
-$q->execute;
+my $season = wloh_common::get_max_season($dbh);
+
+# Pick up all the subdivisions' ratings.
+my %subdivision_ratings = ();
+$q = $dbh->prepare('SELECT divisjon, avdeling, serie_id, AVG(rating) AS avg_rating FROM ratings NATURAL JOIN siste_divisjon WHERE sesong=? GROUP BY divisjon, avdeling, serie_id ORDER BY divisjon, avdeling');
+$q->execute($season);
+
+while (my $ref = $q->fetchrow_hashref) {
+       my $division = $ref->{'divisjon'};
+       my $rating = $ref->{'avg_rating'};
+       my $id = $ref->{'serie_id'};
+
+       push @{$subdivision_ratings{$division}}, [ $id, $rating ];
+}
+
+$q = $dbh->prepare('SELECT divisjon,AVG(rating) AS avg_rating,STDDEV(rating) AS stddev_rating FROM ratings NATURAL JOIN siste_divisjon WHERE sesong=? GROUP BY divisjon ORDER BY divisjon');
+$q->execute($season);
 
 my $i = 0;
 while (my $ref = $q->fetchrow_hashref) {
@@ -97,6 +112,11 @@ while (my $ref = $q->fetchrow_hashref) {
        printf "        <th>%d.</th>\n", $ref->{'divisjon'};
        printf "        <td class=\"num\">%.1f</td>\n", $ref->{'avg_rating'};
        printf "        <td class=\"num\">%.1f</td>\n", $ref->{'stddev_rating'};
+
+       for my $arr (@{$subdivision_ratings{$ref->{'divisjon'}}}) {
+               my ($id, $rating) = @$arr;
+               printf "        <td class=\"num\"><a href=\"http://wordfeud.aasmul.net/serie-%d\">%.1f</a></td>\n", $id, $rating;
+       }
        print "      </tr>\n";
 }