X-Git-Url: https://git.sesse.net/?p=ccbs;a=blobdiff_plain;f=parse%2Fparse-ddreurope-tournament.pl;h=3539946c458e6e9b1c64825ed9cf850ba99b4ad6;hp=6c42f5794eab8b366ca452ac38fdefaf38201b0e;hb=12f6f1727b74581d318163d769318a5794076935;hpb=c8ced6134e4cea84de6f8da5e6611a9ccce2b2dd diff --git a/parse/parse-ddreurope-tournament.pl b/parse/parse-ddreurope-tournament.pl index 6c42f57..3539946 100644 --- a/parse/parse-ddreurope-tournament.pl +++ b/parse/parse-ddreurope-tournament.pl @@ -6,7 +6,7 @@ use Date::Manip; # Parses tournament results from the DDR Europe result list my $season = shift; -my ($name, $round, @rsongs); +my ($name, $round, $group, $player, $position, @rsongs); die "Missing season (give on command line)." if (!defined($season)); print "begin;\n"; @@ -26,16 +26,64 @@ while (<>) { $mix =~ s/Euromix/EuroMix/; print "INSERT INTO tournaments \n"; - print " (season, name, country, location, \"date\", machine, scoringsystem) VALUES (\n"; - print " (SELECT season FROM seasons WHERE name='$season'),\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 name='$country'),\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 name='$mix'),\n"; - print " (SELECT scoringsystem FROM scoringsystems WHERE name='$system')\n"; + print " (SELECT machine FROM machines WHERE machinename='$mix'),\n"; + print " (SELECT scoringsystem FROM scoringsystems WHERE scoringsystemname='$system')\n"; print ");\n"; } + + # Player's results (header) + if (/^ .*? class="link"> (.*?) <\/a> $/x) { + $player = $1; + + printf "INSERT INTO roundparticipation (tournament, round, parallel, player, position) VALUES (\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " $round, $group,\n"; + print " (SELECT player FROM players WHERE nick='$player'),\n"; + print " $position\n"; + print ");\n"; + ++$position; + } + + # Player's results (score) + if (//x) { + my $i = 0; + + # random songs + while (s/ (\d+) <\/td>//x) { + printf "INSERT INTO scores (tournament, round, parallel, player, songnumber, song, chosen, score) VALUES (\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " $round, $group,\n"; + print " (SELECT player FROM players WHERE nick='$player'),\n"; + print " $i,\n"; + print " (SELECT song FROM songs WHERE lower(title)=lower('$rsongs[$i]')),\n"; + print " 'f',\n"; + print " $1);\n"; + ++$i; + } + + # chosen songs + while (s/ .*? class="link"> (.*?) <\/a> .*? (\d+) <\/td>//x) { + my $song = song_map($1); + my $score = $2; + $song =~ s/'/\\'/g; + + printf "INSERT INTO scores (tournament, round, parallel, player, songnumber, song, chosen, score) VALUES (\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " $round, $group,\n"; + print " (SELECT player FROM players WHERE nick='$player'),\n"; + print " $i,\n"; + print " (SELECT song FROM songs WHERE lower(title)=lower('$song')),\n"; + print " 't',\n"; + print " $score);\n"; + ++$i; + } + } # New round if (/

Round \s* (\d+) \s* <\/h3>/x) { @@ -43,35 +91,34 @@ while (<>) { print "INSERT INTO rounds (tournament, round, randomsongs, chosensongs) \n"; print " VALUES (\n"; - print " (SELECT tournament FROM tournaments WHERE name='$name'),\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; print " $round,\n"; print " 0, 0);\n"; # Don't worry, we'll fix it later :-P } # New group, and the random songs for it - if (/ ( Players | Group (\d+) )<\/th>/x) { + if (/ ( Players | Group \s* (\d+) )<\/th>/x) { # bit of an evil hack here - my $group = $2; + $group = $2; $group = 0 if ($1 eq 'Players'); + $position = 1; + print "INSERT INTO groups (tournament, round, parallel) VALUES (\n"; - print " (SELECT tournament FROM tournaments WHERE name='$name'),\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; print " $round, $group);\n"; # Find the random songs, if any @rsongs = (); while (s/ .*? "color:white"> (.*?) <\/a><\/th>//x) { - my $song = $1; - - # different names for songs - $song =~ s/Paranoia -Rebirth-/PARANOiA Rebirth/; - $song =~ s/The Center of the heart/The Centre of the Heart (Stonebridge Club Mix)/; + my $song = song_map($1); + $song =~ s/'/\\'/g; push @rsongs, $song; print "INSERT INTO roundrandomsongs (tournament, round, parallel, song) VALUES (\n"; - print " (SELECT tournament FROM tournaments WHERE name='$name'),\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; print " $round, $group,\n"; print " (SELECT song FROM songs WHERE lower(title)=lower('$song'))\n"; print ");\n"; @@ -79,16 +126,89 @@ while (<>) { # Correct the random songs in the table printf "UPDATE rounds SET randomsongs=%u WHERE \n", scalar @rsongs; - print " tournament=(SELECT tournament FROM tournaments WHERE name='$name') AND \n"; + print " tournament=(SELECT tournament FROM tournaments WHERE tournamentname='$name') AND \n"; print " round=$round;\n"; } - # Header for chosen song + # Header for chosen song (evil) if (/ <\/th>/x) { - printf "UPDATE rounds SET chosensongs=chosensongs+1 WHERE \n"; - print " tournament=(SELECT tournament FROM tournaments WHERE name='$name') AND \n"; + printf "UPDATE rounds SET chosensongs=1 WHERE \n"; + print " tournament=(SELECT tournament FROM tournaments WHERE tournamentname='$name') AND \n"; print " round=$round;\n"; } + + # First place + if (/A \s* well-earned \s* victory \s* for .*? class="link"> (.*?) <\/a> (?: , \s* who \s* got \s* (\d+) \s* point )?/x) { + my $nick = $1; + my $points = $2; + + # Whoa :-) + $points = 'NULL' unless defined($points); + + # Woot, evil + printf "INSERT INTO players SELECT nextval('players_player_seq') AS player, '%s' AS nick WHERE '%s' NOT IN ( SELECT nick FROM players );\n", $nick, $nick; + + print "INSERT INTO tournamentrankings (tournament, ranking, player, points) VALUES (\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " 1,\n"; + print " (SELECT player FROM players WHERE nick='$nick'),\n"; + print " $points);\n"; + } + + # All other places + while (s/(\d+) (?: st | nd | rd | th ) \s* place: .*? class="link"> (.*?) <\/a> \s* (?: got \s* (\d+) \s* point )?//x) { + my $ranking = $1; + my $nick = $2; + my $points = $3; + + # Whoa :-) + $points = 'NULL' unless defined($points); + + # Woot, evil + printf "INSERT INTO players SELECT nextval('players_player_seq') AS player, '%s' AS nick WHERE '%s' NOT IN ( SELECT nick FROM players );\n", $nick, $nick; + + print "INSERT INTO tournamentrankings (tournament, ranking, player, points) VALUES (\n"; + print " (SELECT tournament FROM tournaments WHERE tournamentname='$name'),\n"; + print " $ranking,\n"; + print " (SELECT player FROM players WHERE nick='$nick'),\n"; + print " $points);\n"; + } } printf "commit;\n"; + +# different names for songs +sub song_map { + my $song = shift; + + $song =~ s/Paranoia -Rebirth-/PARANOiA Rebirth/; + $song =~ s/The Center of the heart/The Centre of the Heart (Stonebridge Club Mix)/; + $song =~ s/Can't stop falling in love/Can't Stop Fallin' in Love/; + $song =~ s/B4U -B4 ZA Beat Mix-/B4U (B4 Za Beat Mix)/; + $song =~ s/Jam Jam Reggae/Jam Jam Reggae (AM Swing Mix)/; + $song =~ s/Trip Machine -Climax-/Trip Machine Climax/; + $song =~ s/Dont try to stop it/Don't Try to Stop It/; + $song =~ s/Healing Vision -AM-/Healing Vision (Angelic Mix)/; + $song =~ s/5,6,7,8/5, 6, 7, 8/; + $song =~ s/Keep On Movin/Keep On Movin'/; + $song =~ s/So Deep/So Deep (Perfect Sphere Mix)/; + $song =~ s/Aarons Party/Aaron's Party (Come Get It)/; + $song =~ s/Candy \*/Candy/; + $song =~ s/www\.blondie girl/www.blonde girl (MOMO Mix)/; + $song =~ s/DXY/DXY!/; + $song =~ s/Burning the floor/Burnin' the Floor/; + $song =~ s/Never Gonna Make/Never Gonna Make (Factory Team Mix)/; + $song =~ s/Max300/Max 300/; + $song =~ s/Era/era (nostalmix)/; + $song =~ s/Electro Tuned/Electro Tuned (the SubS Mix)/; + $song =~ s/Make a Jam/Make A Jam!/; + $song =~ s/Paranoia KCET -clean mix-/Paranoia KCET (Clean Mix)/; + $song =~ s/Cant Stop -Speed Mix-/Can't Stop Fallin' in Love (SPEED MIX)/; + $song =~ s/Love This Feelin/Love This Feelin'/; + $song =~ s/Trip Machine -Luv mix-/Trip Machine (Luv Mix)/; + $song =~ s/Let The Beat Hit Them/Let the Beat Hit 'em!/; + $song =~ s/Luv To Me/Luv to Me (AMD Mix)/; + $song =~ s/20 November/20th November/; + + return $song; +}