git.sesse.net Git - ccbs/blob - parse/parse-ccbs-tournament.pl

   1 #! /usr/bin/perl
   2 use strict;
   3 use warnings;
   4 use Date::Manip;
   5
   6 # Parses tournament results from our own HTML (!).
   7
   8 my ($season, $country, $location, $date, $mix, $system);
   9 my ($name, $round, $group, $player, $position, $chosensongs, @rsongs);
  10
  11 print "begin;\n";
  12
  13 while (<>) {
  14         if (/\s* (.*?) \s* <\/h1>/x) {
  15                 $name = $1;
  16                 next;
  17         }
  18         if (/<li> Season: \s* (.*?) <\/li>/x) {
  19                 $season = $1;
  20                 $season =~ s/Standalone/Frittstående/;
  21                 $season =~ s/Norwegian national championship/Norgesmesterskap/;
  22                 next;
  23         }
  24         if (/<li> Country: \s* (.*?) <\/li>/x) {
  25                 $country = $1;
  26                 next;
  27         }
  28         if (/<li> Place: \s* (.*?) <\/li>/x) {
  29                 $location = $1;
  30                 next;
  31         }
  32         if (/<li> Date: \s* (.*?) <\/li>/x) {
  33                 $date = $1;
  34                 next;
  35         }
  36         if (/<li> Machine: \s* (.*?) <\/li>/x) {
  37                 $mix = $1;
  38                 next;
  39         }
  40         if (/<li> Scoring \s* system: \s* (.*?) <\/li>/x) {
  41                 $system = $1;
  42
  43                 print "INSERT INTO tournaments \n";
  44                 print "  (season, tournamentname, country, location, \"date\", machine, scoringsystem) VALUES (\n";
  45                 print "    (SELECT season FROM seasons WHERE seasonname='$season' ),\n";
  46                 print "    '$name',\n";
  47                 print "    (SELECT country FROM countries WHERE countryname='$country'),\n";
  48                 print "    '$location',\n";
  49                 printf "    '%s',\n", Date::Manip::UnixDate($date, '%Y-%m-%d');
  50                 print "    (SELECT machine FROM machines WHERE machinename='$mix'),\n";
  51                 print "    (SELECT scoringsystem FROM scoringsystems WHERE scoringsystemname='$system')\n";
  52                 print ");\n";
  53
  54                 next;
  55         }
  56
  57         # Player rankings
  58         if (/^ \s* <li> ( \d+) \. \s* place: \s* <a.*?> ( .*? ) (?: \s* \( ( [A-Z]+ ) \) )? <\/a> , \s* with \s* ( \d+ ) \s* points/x) {
  59                 my ($ranking, $nick, $code, $points) = ($1, $2, $3, $4);
  60
  61                 # Woot, evil
  62                 print "INSERT INTO players (player, nick, country, club) \n";
  63                 print "WITH code_lookup AS ( \n";
  64                 print "  SELECT ( SELECT country FROM countries WHERE countryname='Norway' ) AS country, club FROM clubs WHERE clubcode='$code'\n";
  65                 print "  UNION ALL SELECT country, NULL FROM countries WHERE countrycode='$code' \n";
  66                 print "  UNION ALL SELECT country, NULL FROM countries WHERE countrycode='NOR' \n";
  67                 print ") \n";
  68                 print "  SELECT nextval('players_player_seq'), \n";
  69                 print "  '$nick', \n";
  70                 print "  ( SELECT country FROM code_lookup LIMIT 1 ), \n";
  71                 print "  ( SELECT club FROM code_lookup LIMIT 1 ) \n";
  72                 print "WHERE '$nick' NOT IN ( SELECT nick FROM players );\n";
  73
  74                 print "INSERT INTO tournamentrankings (tournament, ranking, player, points) VALUES (\n";
  75                 print "   (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
  76                 print "   $ranking,\n";
  77                 print "   (SELECT player FROM players WHERE nick='$nick'),\n";
  78                 print "   $points);\n";
  79         }
  80
  81         # New round
  82         if (/<h2><a \s* name="round\d+">Runde \s* (\d+) \s* <\/a><\/h2\>/x) {
  83                 $round = $1;
  84
  85                 print "INSERT INTO rounds (tournament, round, randomsongs, chosensongs) \n";
  86                 print "  VALUES (\n";
  87                 print "   (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
  88                 print "   $round,\n";
  89                 print "   0, 0);\n";  # Don't worry, we'll fix it later :-P
  90         }
  91
  92         # New group
  93         if (/<input \s* type="hidden" \s* name="group" \s* value=" ( \d+ )" \s* \/>/x) {
  94                 $group = $1;
  95                 $position = 1;
  96                 @rsongs = ();
  97                 $chosensongs = 0;
  98
  99                 print "INSERT INTO groups (tournament, round, parallel) VALUES (\n";
 100                 print "   (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
 101                 print "   $round, $group);\n";
 102         }
 103
 104         # New random song
 105         if (/<th \s* colspan="3"><a.*?> ( .*?) <\/a><\/th>/x) {
 106                 my $song = $1;
 107                 $song =~ s/'/''/g;
 108                 push @rsongs, $song;
 109
 110                 print "INSERT INTO roundrandomsongs (tournament, round, parallel, song) VALUES (\n";
 111                 print "   (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
 112                 print "   $round, $group,\n";
 113                 print "   (SELECT song FROM songs WHERE title='$song')\n";
 114                 print ");\n";
 115
 116                 # Correct the random songs in the table
 117                 printf "UPDATE rounds SET randomsongs=%u WHERE \n", scalar @rsongs;
 118                 print "  tournament=(SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )) AND \n";
 119                 print "  round=$round;\n";
 120         }
 121
 122         # New self-selected song
 123         if (/<th \s* colspan="4">Self-selected<\/th>/x) {
 124                 $chosensongs++;
 125                 printf "UPDATE rounds SET chosensongs=$chosensongs WHERE \n";
 126                 print "  tournament=(SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )) AND \n";
 127                 print "  round=$round;\n";
 128         }
 129
 130         # Player's results (header)
 131         if (/^\s* <a \s* href=" .*? \/player\.pl \? id=\d+"> (.*?) (?: \s* \( ( [A-Z]+ ) \) )? <\/a> \s*$/x) {
 132                 $player = $1;
 133
 134                 printf "INSERT INTO roundparticipation (tournament, round, parallel, player, position) VALUES (\n";
 135                 print "   (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
 136                 print "   $round, $group,\n";
 137                 print "   (SELECT player FROM players WHERE nick='$player'),\n";
 138                 print "   $position\n";
 139                 print ");\n";
 140                 ++$position;
 141
 142                 chomp ($_ = <>);
 143                 die unless $_ =~ /<\/th>/;
 144
 145                 # Self-selected songs
 146                 for my $i (1..scalar @rsongs) {
 147                         chomp ($_ = <>);
 148                         die unless $_ =~ /<td \s* class="score"> (\d+) <\/td>/x;
 149                         my $score = $1;
 150
 151                         chomp ($_ = <>);
 152                         die unless $_ =~ /<td \s* class="playmode"> (\S+) <\/td>/x;
 153                         my $playmode = $1;
 154
 155                         chomp ($_ = <>);
 156                         die unless $_ =~ /<td \s* class="difficulty"> (\S+) <\/td>/x;
 157                         my $difficulty = $1;
 158
 159                         printf "INSERT INTO scores (tournament, round, parallel, player, songnumber, song, playmode, difficulty, chosen, score) VALUES (\n";
 160                         print "   (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
 161                         print "   $round, $group,\n";
 162                         print "   (SELECT player FROM players WHERE nick='$player'),\n";
 163                         print "   $i,\n";
 164                         print "   (SELECT song FROM songs WHERE title='$rsongs[$i-1]'),\n";
 165                         print "   '$playmode',\n";
 166                         print "   '$difficulty',\n";
 167                         print "   'f',\n";
 168                         print "   $score);\n";
 169                 }
 170
 171                 # Chosen songs
 172                 for my $i (1..$chosensongs) {
 173                         my $songnumber = $i + scalar @rsongs;
 174
 175                         chomp ($_ = <>);
 176                         die unless $_ =~ /<td \s* class="score"> (\d+) <\/td>/x;
 177                         my $score = $1;
 178
 179                         chomp ($_ = <>);
 180                         die unless $_ =~ /<td \s* class="playmode"> (\S+) <\/td>/x;
 181                         my $playmode = $1;
 182
 183                         chomp ($_ = <>);
 184                         die unless $_ =~ /<td \s* class="difficulty"> (\S+) <\/td>/x;
 185                         my $difficulty = $1;
 186
 187                         chomp ($_ = <>);
 188                         die unless $_ =~ /<td><a.*?> (.*?) <\/a><\/td>/x;
 189                         my $song = $1;
 190                         $song =~ s/'/''/g;
 191
 192                         printf "INSERT INTO scores (tournament, round, parallel, player, songnumber, song, playmode, difficulty, chosen, score) VALUES (\n";
 193                         print "   (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
 194                         print "   $round, $group,\n";
 195                         print "   (SELECT player FROM players WHERE nick='$player'),\n";
 196                         print "   $songnumber,\n";
 197                         print "   (SELECT song FROM songs WHERE title='$song'),\n";
 198                         print "   '$playmode',\n";
 199                         print "   '$difficulty',\n";
 200                         print "   't',\n";
 201                         print "   $score);\n";
 202                 }
 203         }
 204 }
 205
 206 printf "commit;\n";