--- /dev/null
+#! /usr/bin/perl
+use strict;
+use warnings;
+use Date::Manip;
+
+# Parses tournament results from our own HTML (!).
+
+my ($season, $country, $location, $date, $mix, $system);
+my ($name, $round, $group, $player, $position, $chosensongs, @rsongs);
+
+print "begin;\n";
+
+while (<>) {
+ if (/\s* (.*?) \s* <\/h1>/x) {
+ $name = $1;
+ next;
+ }
+ if (/<li> Season: \s* (.*?) <\/li>/x) {
+ $season = $1;
+ $season =~ s/Standalone/Frittstående/;
+ $season =~ s/Norwegian national championship/Norgesmesterskap/;
+ next;
+ }
+ if (/<li> Country: \s* (.*?) <\/li>/x) {
+ $country = $1;
+ next;
+ }
+ if (/<li> Place: \s* (.*?) <\/li>/x) {
+ $location = $1;
+ next;
+ }
+ if (/<li> Date: \s* (.*?) <\/li>/x) {
+ $date = $1;
+ next;
+ }
+ if (/<li> Machine: \s* (.*?) <\/li>/x) {
+ $mix = $1;
+ next;
+ }
+ if (/<li> Scoring \s* system: \s* (.*?) <\/li>/x) {
+ $system = $1;
+
+ print "INSERT INTO tournaments \n";
+ print " (season, tournamentname, country, location, \"date\", machine, scoringsystem) VALUES (\n";
+ print " (SELECT season FROM seasons WHERE seasonname='$season' ),\n";
+ print " '$name',\n";
+ print " (SELECT country FROM countries WHERE countryname='$country'),\n";
+ print " '$location',\n";
+ printf " '%s',\n", Date::Manip::UnixDate($date, '%Y-%m-%d');
+ print " (SELECT machine FROM machines WHERE machinename='$mix'),\n";
+ print " (SELECT scoringsystem FROM scoringsystems WHERE scoringsystemname='$system')\n";
+ print ");\n";
+
+ next;
+ }
+
+ # Player rankings
+ if (/^ \s* <li> ( \d+) \. \s* place: \s* <a.*?> ( \S+ ) (?: \s* \( ( [A-Z]+ ) \) )? <\/a> , \s* with \s* ( \d+ ) \s* points/x) {
+ my ($ranking, $nick, $code, $points) = ($1, $2, $3, $4);
+
+ # Woot, evil
+ print "INSERT INTO players (player, nick, country, club) \n";
+ print "WITH code_lookup AS ( \n";
+ print " SELECT ( SELECT country FROM countries WHERE countryname='Norway' ) AS country, club FROM clubs WHERE clubcode='$code'\n";
+ print " UNION ALL SELECT country, NULL FROM countries WHERE countrycode='$code' \n";
+ print " UNION ALL SELECT country, NULL FROM countries WHERE countrycode='NOR' \n";
+ print ") \n";
+ print " SELECT nextval('players_player_seq'), \n";
+ print " '$nick', \n";
+ print " ( SELECT country FROM code_lookup LIMIT 1 ), \n";
+ print " ( SELECT club FROM code_lookup LIMIT 1 ) \n";
+ print "WHERE '$nick' NOT IN ( SELECT nick FROM players );\n";
+
+ print "INSERT INTO tournamentrankings (tournament, ranking, player, points) VALUES (\n";
+ print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
+ print " $ranking,\n";
+ print " (SELECT player FROM players WHERE nick='$nick'),\n";
+ print " $points);\n";
+ }
+
+ # New round
+ if (/<h2><a \s* name="round\d+">Runde \s* (\d+) \s* <\/a><\/h2\>/x) {
+ $round = $1;
+
+ print "INSERT INTO rounds (tournament, round, randomsongs, chosensongs) \n";
+ print " VALUES (\n";
+ print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
+ print " $round,\n";
+ print " 0, 0);\n"; # Don't worry, we'll fix it later :-P
+ }
+
+ # New group
+ if (/<input \s* type="hidden" \s* name="group" \s* value=" ( \d+ )" \s* \/>/x) {
+ $group = $1;
+ $position = 1;
+ @rsongs = ();
+ $chosensongs = 0;
+
+ print "INSERT INTO groups (tournament, round, parallel) VALUES (\n";
+ print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
+ print " $round, $group);\n";
+ }
+
+ # New random song
+ if (/<th \s* colspan="3"><a.*?> ( .*?) <\/a><\/th>/x) {
+ my $song = $1;
+ $song =~ s/'/''/g;
+ push @rsongs, $song;
+
+ print "INSERT INTO roundrandomsongs (tournament, round, parallel, song) VALUES (\n";
+ print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
+ print " $round, $group,\n";
+ print " (SELECT song FROM songs WHERE title='$song')\n";
+ print ");\n";
+
+ # Correct the random songs in the table
+ printf "UPDATE rounds SET randomsongs=%u WHERE \n", scalar @rsongs;
+ print " tournament=(SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )) AND \n";
+ print " round=$round;\n";
+ }
+
+ # New self-selected song
+ if (/<th \s* colspan="4">Self-selected<\/th>/x) {
+ $chosensongs++;
+ printf "UPDATE rounds SET chosensongs=$chosensongs WHERE \n";
+ print " tournament=(SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )) AND \n";
+ print " round=$round;\n";
+ }
+
+ # Player's results (header)
+ if (/^\s* <a \s* href="\S+"> (\S+) (?: \s* \( ( [A-Z]+ ) \) )? <\/a> \s*$/x) {
+ $player = $1;
+
+ printf "INSERT INTO roundparticipation (tournament, round, parallel, player, position) VALUES (\n";
+ print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
+ print " $round, $group,\n";
+ print " (SELECT player FROM players WHERE nick='$player'),\n";
+ print " $position\n";
+ print ");\n";
+ ++$position;
+
+ chomp ($_ = <>);
+ die unless $_ =~ /<\/th>/;
+
+ # Self-selected songs
+ for my $i (1..scalar @rsongs) {
+ chomp ($_ = <>);
+ die unless $_ =~ /<td \s* class="score"> (\d+) <\/td>/x;
+ my $score = $1;
+
+ chomp ($_ = <>);
+ die unless $_ =~ /<td \s* class="playmode"> (\S+) <\/td>/x;
+ my $playmode = $1;
+
+ chomp ($_ = <>);
+ die unless $_ =~ /<td \s* class="difficulty"> (\S+) <\/td>/x;
+ my $difficulty = $1;
+
+ printf "INSERT INTO scores (tournament, round, parallel, player, songnumber, song, playmode, difficulty, chosen, score) VALUES (\n";
+ print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
+ print " $round, $group,\n";
+ print " (SELECT player FROM players WHERE nick='$player'),\n";
+ print " $i,\n";
+ print " (SELECT song FROM songs WHERE title='$rsongs[$i-1]'),\n";
+ print " '$playmode',\n";
+ print " '$difficulty',\n";
+ print " 'f',\n";
+ print " $score);\n";
+ }
+
+ # Chosen songs
+ for my $i (1..$chosensongs) {
+ my $songnumber = $i + scalar @rsongs;
+
+ chomp ($_ = <>);
+ die unless $_ =~ /<td \s* class="score"> (\d+) <\/td>/x;
+ my $score = $1;
+
+ chomp ($_ = <>);
+ die unless $_ =~ /<td \s* class="playmode"> (\S+) <\/td>/x;
+ my $playmode = $1;
+
+ chomp ($_ = <>);
+ die unless $_ =~ /<td \s* class="difficulty"> (\S+) <\/td>/x;
+ my $difficulty = $1;
+
+ chomp ($_ = <>);
+ die unless $_ =~ /<td><a.*?> (.*?) <\/a><\/td>/x;
+ my $song = $1;
+ $song =~ s/'/''/g;
+
+ printf "INSERT INTO scores (tournament, round, parallel, player, songnumber, song, playmode, difficulty, chosen, score) VALUES (\n";
+ print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
+ print " $round, $group,\n";
+ print " (SELECT player FROM players WHERE nick='$player'),\n";
+ print " $songnumber,\n";
+ print " (SELECT song FROM songs WHERE title='$song'),\n";
+ print " '$playmode',\n";
+ print " '$difficulty',\n";
+ print " 't',\n";
+ print " $score);\n";
+ }
+ }
+}
+
+printf "commit;\n";
projects / ccbs / blobdiff