+ my $q = $dbh->prepare('SELECT DISTINCT(divisjon) FROM fotballserier se JOIN fotballspraak sp ON se.spraak=sp.id WHERE kultur=? AND sesong=? ORDER BY divisjon');
+ $q->execute($locale, $season);
+
+ while (my $ref = $q->fetchrow_hashref) {
+ push @divisions, $ref->{'divisjon'};
+ }
+
+ return @divisions;
+}
+
+sub get_subdivisions {
+ my ($dbh, $locale, $season, $division) = @_;
+
+ my @subdivisions = ();
+
+ my $q = $dbh->prepare('SELECT DISTINCT(avdeling) FROM fotballserier se JOIN fotballspraak sp ON se.spraak=sp.id WHERE kultur=? AND sesong=? AND divisjon=? ORDER BY avdeling');
+ $q->execute($locale, $season, $division);
+
+ while (my $ref = $q->fetchrow_hashref) {
+ push @subdivisions, $ref->{'avdeling'};
+ }
+
+ return @subdivisions;
+}
+
+sub get_division_selector {
+ my ($divisions, $division) = @_;
+
+ my @ret = ();
+ for my $d (@$divisions) {
+ my $parms = {
+ 'option' => $d,
+ 'option/value' => $d,
+ };
+ $parms->{'option/selected'} = 'selected' if ($d == $division);
+ push @ret, $parms;
+ }
+ return \@ret;
+}
+
+sub get_players_and_ratings {
+ my ($dbh, $locale, $season, $division, $subdivision) = @_;
+
+ my $q = $dbh->prepare('SELECT fotballdeltagere.id,fotballdeltagere.navn,rating,rating_stddev FROM fotballdeltagere JOIN fotballserier ON fotballdeltagere.serie=fotballserier.nr NATURAL JOIN spiller_kultur LEFT JOIN ratings ON fotballdeltagere.id=ratings.id WHERE kultur=? AND sesong=? AND divisjon=? AND avdeling=?');
+ $q->execute($locale, $season, $division, $subdivision);
+
+ while (my $ref = $q->fetchrow_hashref) {
+ my $id = $ref->{'id'};
+ $players{$id} = Encode::decode_utf8($ref->{'navn'});
+ $ratings{$id} = $ref->{'rating'};
+ $ratings_stddev{$id} = $ref->{'rating_stddev'};
+ }
+ $q->finish;
+}
+
+sub get_matches {
+ my ($dbh, $locale, $season, $division, $subdivision) = @_;
+
+ my @matches = ();
+ my $q = $dbh->prepare('
+ SELECT
+ d1.id AS p1, d2.id AS p2, maalfor AS score1, maalmot AS score2
+ FROM ( SELECT * FROM fotballresultater UNION ALL SELECT * FROM fotballresultater_2123 ) r
+ JOIN fotballserier s ON r.serie=s.nr
+ JOIN fotballspraak sp ON s.spraak=sp.id
+ JOIN fotballdeltagere d1 ON r.lagrecno=d1.nr AND r.serie=d1.serie
+ JOIN fotballdeltagere d2 ON r.motstander=d2.nr AND r.serie=d2.serie
+ WHERE
+ kultur=? AND sesong=? AND divisjon=? AND avdeling=?
+ AND lagrecno > motstander
+ ');
+ $q->execute($locale, $season, $division, $subdivision);
+
+ while (my $ref = $q->fetchrow_hashref) {
+ push @matches, [ $ref->{'p1'}, $ref->{'p2'}, $ref->{'score1'}, $ref->{'score2'} ];
+ }
+ $q->finish;
+
+ return @matches;
+}
+
+sub get_covariance_matrix {
+ my ($dbh, @players) = @_;
+
+ my $player_sql = '{' . join(',', @players ) . '}';
+ my $q = $dbh->prepare('SELECT * FROM covariance WHERE player1=ANY(?::smallint[]) AND player2=ANY(?::smallint[])', { pg_prepare_now => 0 });
+ $q->execute($player_sql, $player_sql);
+
+ my $cov = {};
+ while (my $ref = $q->fetchrow_hashref) {
+ $cov->{$ref->{'player1'}}{$ref->{'player2'}} = $ref->{'cov'};
+ }
+
+ return $cov;
+}
+
+sub write_parms_to_file {
+ my ($locale, $aux_parms, $match_stddev, $used_ratings, $used_cov) = @_;
+
+ wloh_common::set_locale($locale);
+
+ my @sorted_players = sort { $players{$a} cmp $players{$b} } keys %players;