6 # Parses tournament results from our own HTML (!).
8 my ($season, $country, $location, $date, $mix, $system);
9 my ($name, $round, $group, $player, $position, $chosensongs, @rsongs);
14 if (/\s* (.*?) \s* <\/h1>/x) {
18 if (/<li> Season: \s* (.*?) <\/li>/x) {
20 $season =~ s/Standalone/Frittstående/;
21 $season =~ s/Norwegian national championship/Norgesmesterskap/;
24 if (/<li> Country: \s* (.*?) <\/li>/x) {
28 if (/<li> Place: \s* (.*?) <\/li>/x) {
32 if (/<li> Date: \s* (.*?) <\/li>/x) {
36 if (/<li> Machine: \s* (.*?) <\/li>/x) {
40 if (/<li> Scoring \s* system: \s* (.*?) <\/li>/x) {
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";
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";
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);
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";
68 print " SELECT nextval('players_player_seq'), \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";
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";
77 print " (SELECT player FROM players WHERE nick='$nick'),\n";
82 if (/<h2><a \s* name="round\d+">Runde \s* (\d+) \s* <\/a><\/h2\>/x) {
85 print "INSERT INTO rounds (tournament, round, randomsongs, chosensongs) \n";
87 print " (SELECT tournament FROM tournaments WHERE tournamentname='$name' AND season=( SELECT season FROM seasons WHERE seasonname='$season' )),\n";
89 print " 0, 0);\n"; # Don't worry, we'll fix it later :-P
93 if (/<input \s* type="hidden" \s* name="group" \s* value=" ( \d+ )" \s* \/>/x) {
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";
105 if (/<th \s* colspan="3"><a.*?> ( .*?) <\/a><\/th>/x) {
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";
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";
122 # New self-selected song
123 if (/<th \s* colspan="4">Self-selected<\/th>/x) {
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";
130 # Player's results (header)
131 if (/^\s* <a \s* href=" .*? \/player\.pl \? id=\d+"> (.*?) (?: \s* \( ( [A-Z]+ ) \) )? <\/a> \s*$/x) {
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";
143 die unless $_ =~ /<\/th>/;
145 # Self-selected songs
146 for my $i (1..scalar @rsongs) {
148 die unless $_ =~ /<td \s* class="score"> (\d+) <\/td>/x;
152 die unless $_ =~ /<td \s* class="playmode"> (\S+) <\/td>/x;
156 die unless $_ =~ /<td \s* class="difficulty"> (\S+) <\/td>/x;
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";
164 print " (SELECT song FROM songs WHERE title='$rsongs[$i-1]'),\n";
165 print " '$playmode',\n";
166 print " '$difficulty',\n";
172 for my $i (1..$chosensongs) {
173 my $songnumber = $i + scalar @rsongs;
176 die unless $_ =~ /<td \s* class="score"> (\d+) <\/td>/x;
180 die unless $_ =~ /<td \s* class="playmode"> (\S+) <\/td>/x;
184 die unless $_ =~ /<td \s* class="difficulty"> (\S+) <\/td>/x;
188 die unless $_ =~ /<td><a.*?> (.*?) <\/a><\/td>/x;
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";