view release on metacpan or  search on metacpan

lib/Bracket/Model/DBIC.pm  view on Meta::CPAN

package Bracket::Model::DBIC;

use strict;
use base 'Catalyst::Model::DBIC::Schema';
use List::Util qw( max );

__PACKAGE__->config(schema_class => 'Bracket::Schema',);

=head2 update_points

SQL update of points that is way faster than player_points action in Admin.
DRAWBACK: only tested on MySQL, may be MySQL specfic update.
SOLUTION: Find DBIC way of doing it?  Use sub-query.

Note: sqlite3 does not like the syntax on this update

=cut

sub update_points {
    my $self    = shift;
    my $storage = $self->schema->storage;
    return $storage->dbh_do(
        sub {
            my $self = shift;
            my $dbh  = shift;
            my $sth  = $dbh->prepare('delete from region_score;');
            $sth->execute;
            $sth = $dbh->prepare('
                insert into region_score
                (player, region, points)
                select player.id, region.id, 0
                from player, region
                where player.active = 1;'
            );
            $sth->execute;
            $sth = $dbh->prepare('
                update region_score region_score,
                (
                select  player_picks.player,
                        sum(game.round*(5 + game.lower_seed*team.seed)) as points,
                        team.region as region
                  from pick player_picks, pick perfect_picks, game game, team team
                 where perfect_picks.pick   = player_picks.pick 
                   and perfect_picks.game   = player_picks.game 
                   and player_picks.game    = game.id
                   and player_picks.pick    = team.id
                   and perfect_picks.player = 1
                   group by player_picks.player, team.region
                )  computed_player_points
                set region_score.points = computed_player_points.points
                where region_score.player = computed_player_points.player
                  and region_score.region = computed_player_points.region
                ;'
            );
            $sth->execute;
            $sth  = $dbh->prepare('
                update player player,
                (
                 select player, sum(points) as total_points from region_score
                 group by player
                ) region_scores
                set player.points = region_scores.total_points
                where player.id = region_scores.player;
            ');
            $sth->execute;

        }
    );
}

=heads2 count_region_picks

Count up how many picks a player has made for each region.
Displayed on Player home page.

=cut

sub count_region_picks {
    my ($self, $player_id) = @_;