-# 2. If player A has a higher ranking in his/her group than player B, player A is higher.
-# 3. If player A has higher max score than player B, player A is higher.
-
-# Basically, #2 makes this impossible to achieve in pure SQL. We just have
-# to fetch one and one group and make the best out of it. Fetch out all the
-# parallels (in sorted order) and grab all players in turn.
-
-my $qscores = $dbh->prepare('SELECT parallel,player,SUM(score) AS sum_score,MAX(score) AS max_score FROM scores WHERE tournament=? AND round=? GROUP BY parallel,player ORDER BY SUM(score) DESC');
-for my $r (reverse (1..($last_sround-1))) {
- my @parallels = ();
- my $num_players = 0;
-
- $qscores->execute($tournament, $r);
-
- while (my $ref = $qscores->fetchrow_hashref()) {
- my $p = $ref->{'parallel'};
- if (!defined($parallels[$p])) {
- $parallels[$p] = [];
- }
-
- push @{$parallels[$p]}, {%$ref};
- $num_players++;
- }
-
- my $place = 0;
-
- # Grab players from the top until nobody's left
- while ($num_players > 0) {
- my @players_this_place = ();
- for my $p (@parallels) {
- next if (!defined($p->[$place]));
- $num_players--;
- next if ($already_ordered{$p->[$place]->{'player'}});
-
- push @players_this_place, $p->[$place];
- }
-
- @players_this_place = sort { $b->{'max_score'} <=> $a->{'max_score'} } @players_this_place;
-
- for my $s (@players_this_place) {
- $dbh->do('INSERT INTO tournamentrankings (tournament, ranking, player, points) VALUES (?,?,?,?)',
- undef, $tournament, $ranking, $s->{'player'}, points_for_place($ranking));
- $ranking++;
- $already_ordered{$s->{'player'}} = 1;
- }
-
- $place++;
- }
+# 2. If player A has a higher (max sum of songs)/(max feet of songs) (where
+# any chosen song counts for 10) than B, player A is higher.
+my $scores = ccbs::db_fetch_all($dbh, 'SELECT player FROM scores NATURAL JOIN tournaments NATURAL JOIN max_single_feetrating WHERE tournament=? GROUP BY round,player ORDER BY round DESC,SUM(score)/SUM(CASE WHEN chosen THEN 10 ELSE feetrating END) DESC',
+ $tournament);
+for my $s (@$scores) {
+ next if ($already_ordered{$s->{'player'}});
+ $dbh->do('INSERT INTO tournamentrankings (tournament, ranking, player, points) VALUES (?,?,?,?)',
+ undef, $tournament, $ranking, $s->{'player'}, points_for_place($ranking));
+ $ranking++;
+ $already_ordered{$s->{'player'}} = 1;
projects / ccbs / commitdiff