--- /dev/null
+#! /usr/bin/perl
+
+use ccbs;
+use strict;
+use warnings;
+
+my $cgi = new CGI;
+my $id = $cgi->param('id');
+
+my $dbh = ccbs::db_connect();
+
+my $songs = ccbs::db_fetch_all($dbh, 'SELECT machinename,song,title,artist,avg_score,stddev_score,high_score,high_score_player,high_score_nick FROM machinesongs NATURAL JOIN songs NATURAL JOIN machines NATURAL LEFT JOIN ( SELECT song,AVG(score)::integer AS avg_score,STDDEV(score)::integer AS stddev_score,MAX(score) AS high_score FROM scores GROUP BY song ) t1 NATURAL LEFT JOIN ( SELECT DISTINCT ON (song) song,player AS high_score_player,nick AS high_score_nick,score AS high_score FROM scores NATURAL JOIN players ORDER BY song,score DESC ) t2 ORDER BY machine,LOWER(title)');
+
+ccbs::print_header();
+ccbs::process_template('songs.tmpl', 'Songs', {
+ songs => $songs,
+});
+$dbh->disconnect;
<ul>
<li><a href="tournaments.pl">Turneringer</a></li>
<li><a href="players.pl">Spillere</a></li>
+ <li><a href="songs.pl">Sanger</a></li>
[% IF !public %]
<li><a href="add-tournament.pl">Legg til turnering</a></li>
<li><a href="randomsongs.pl">Sangvelger</a></li>
--- /dev/null
+[%# vim:set filetype=html: %]
+
+[% SET last_machine = '' %]
+
+[% FOR s = songs %]
+[% IF s.machinename != last_machine %]
+[% IF last_machine != '' %]
+ </table>
+ </div>
+[% END %]
+
+[% SET last_machine = s.machinename %]
+ <h2>[% s.machinename %]</h2>
+
+ <div>
+ <table class="scores">
+ <tr>
+ <th>Song name</th>
+ <th>Artist</th>
+ <th>Average</th>
+ <th>Std. dev</th>
+ <th>High score</th>
+ </tr>
+[% END %]
+
+ <tr>
+ <td><a href="song.pl?id=[% s.song %]">[% s.title %]</a></td>
+ <td>[% s.artist %]</td>
+ <td>[% s.avg_score %]</td>
+ <td>[% s.stddev_score %]</td>
+[% IF s.high_score == '' %]
+ <td></td>
+[% ELSE %]
+ <td>[% s.high_score %] by <a href="song.pl?id=[% s.high_score_player %]">[% s.high_score_nick %]</a></td>
+[% END %]
+ </tr>
+[% END %]
+ </table>
+ </div>