Pull in changes from --baby.

[ccbs] / html / do-start-round.pl

diff --git a/html/do-start-round.pl b/html/do-start-round.pl
index d843be2fb6e882374ca602535e3bf57e97d867ce..36844233cc019663b6f8b06cfb813345e09a6371 100755 (executable)
--- a/html/do-start-round.pl
+++ b/html/do-start-round.pl
@@ -52,7 +52,7 @@ if ($round == 1) {
        # Get the total list of scores for each player in this round, and pick
        # out the best N
        $people = [];
-       my $q = $dbh->prepare('SELECT parallel,player,SUM(score) AS score FROM scores WHERE tournament=? AND round=? GROUP BY parallel,player ORDER BY parallel, SUM(score) DESC');
+       my $q = $dbh->prepare('SELECT parallel,player,SUM(score)/SUM(CASE WHEN chosen THEN 10 ELSE feetrating END) AS score FROM scores NATURAL JOIN tournaments NATURAL JOIN max_single_feetrating WHERE tournament=? AND round=? GROUP BY parallel,player ORDER BY parallel, SUM(score) DESC');
        $q->execute($tournament, $round - 1);
        
        my ($parallel,$num_from_this_parallel);
@@ -70,36 +70,52 @@ if ($round == 1) {
 
 if ($num_groups == 1) {
        # Everybody's in the same group
+       my @speople = sort { $a->{'score'} <=> $b->{'score'} } @$people;
+
        my $position = 1;
-       for my $p (@$people) {
+       for my $p (@speople) {
                $dbh->do('INSERT INTO roundparticipation (tournament, round, parallel, player, position) VALUES (?, ?, ?, ?, ?)', undef,
                        $tournament, $round, 0, $p->{'player'}, $position);
                $position++;
        }
 } else {
        # Zigzag people to get the most fair groups possible
-       my $group = 1;
-       my $direction = 1;
-       my $position = 1;
-       for my $p (@$people) {
-               $dbh->do('INSERT INTO roundparticipation (tournament, round, parallel, player, position) VALUES (?, ?, ?, ?, ?)', undef,
-                               $tournament, $round, $group, $p->{'player'}, $position);
+       my @speople = sort { $b->{'score'} <=> $a->{'score'} } @$people;
 
-               if ($group + $direction < 1 || $group + $direction > $num_groups) {
+       my @ngroups = ();
+       for my $g (1..$num_groups) {
+               push @ngroups, [];
+       }
+       
+       my $group = 0;
+       my $direction = 1;
+       for my $p (@speople) {
+               push @{$ngroups[$group]}, $p->{'player'};
+               if ($group + $direction < 0 || $group + $direction >= $num_groups) {
                        $direction = -$direction;
-                       $position++;
                } else {
                        $group += $direction;
                }
        }
+
+       $group = 1;
+       for my $g (@ngroups) {
+               my @gpeople = reverse @$g;
+               for my $position (0..$#gpeople) {
+                       $dbh->do('INSERT INTO roundparticipation (tournament, round, parallel, player, position) VALUES (?, ?, ?, ?, ?)', undef,
+                               $tournament, $round, $group, $gpeople[$position], $position + 1);
+               }
+               $group++;
+       }
 }
 
-# Pick random songs for the groups
+# Pick random songs for the groups (don't pick challenge-only songs; slightly
+# clumsy, should we have used an IN subquery instead?)
 for my $g (1..$num_groups) {
        my $gg = ($num_groups == 1) ? 0 : $g;
 
        for my $s (1..$num_random) {
-               my $ref = $dbh->selectrow_hashref('SELECT * FROM machinesongs WHERE song NOT IN ( SELECT song FROM randomsongsused ) AND machine=( SELECT machine FROM tournaments WHERE tournament=? ) ORDER BY random() LIMIT 1',
+               my $ref = $dbh->selectrow_hashref('SELECT * FROM machinesongs NATURAL JOIN songratings WHERE song NOT IN ( SELECT song FROM randomsongsused ) AND machine=( SELECT machine FROM tournaments WHERE tournament=? ) AND playmode=\'single\' AND difficulty=\'expert\' ORDER BY random() LIMIT 1',
                        undef, $tournament);
                if (!defined($ref)) {
                        ccbs::user_error('Det er ikke flere sanger igjen i sangvelgeren!');