• Files
• Commits
• Log
• Graph
• Stats

package LIMS::Model::Roles::DBIxSimple;

use Moose::Role;

has params => ( is => 'rw', isa => 'HashRef', default => sub { {} } );
has constraint => ( is => 'rw', isa => 'Str' ); 
has constraint_title => ( is => 'rw', isa => 'Str' ); # for chart & template titles

use DateTime::Format::MySQL;
# requires 'lims_dbix'; # LIMS::Model::Base method

use Data::Dumper;

sub get_cols {
	my ($self, $table) = @_;
	
	my $dbh = $self->lims_dbix;
	
	my $meta = $self->get_meta($table); # warn Dumper $meta;
	
	my @cols = keys %$meta; # warn Dumper \@cols;
	return \@cols;
}

sub get_meta {
	my ($self, $table) = @_;
	
	my $dbh = $self->lims_dbix;
	
	my $t = $dbh->query("show columns from $table")->hashes; # warn Dumper $t;
	
	my %meta = map { $_->{field} => $_ } @$t; # warn Dumper \%meta;	
	return \%meta;
}

sub get_enum_opts {
	my ($self, $table, $col) = @_;

	my $meta = $self->get_meta($table);
	
	my $col_type = $meta->{$col}->{type};
	
	my ($enum) = $col_type =~ /enum\((.*)\)/; # warn $enum;
	my @opts = sort grep { $_ =~ s/\'//g } split ',', $enum; # warn Dumper \@opts;
	return \@opts;
}

sub inflate_mysql_dates_to_datetime {
	my $self = shift;
	my $data = shift; # hashref
	my $cols = shift; # array(ref) of cols to inflate
	
	map {
		$data->{$_} = DateTime::Format::MySQL->parse_date($data->{$_});
	} grep $data->{$_}, @$cols;	
}

sub inflate_mysql_timestamp_to_datetime {
	my $self = shift;
	my $data = shift; # hashref
	my $cols = shift; # array(ref) of cols to inflate
	
	map {
		$data->{$_} = DateTime::Format::MySQL->parse_datetime($data->{$_});
	} grep $data->{$_}, @$cols;	
}

sub get_sql_with_constraint {
    my $self  = shift;
    my $entry = shift; # sql lib entry
    my $time_column = shift; # to set duration on
    my $default_constraint_type = shift || ''; # optional - to override return

    # set constraint & constraint_title params:    
    $self->_set_search_constraints($time_column, $default_constraint_type); 
    my $constraint = $self->constraint;

	my $sql = $self->sql_lib->retr($entry);
    $sql =~ s/%CONSTRAINT%/$constraint/; # warn $sql;

    return $sql;
}

=begin
my $type = $vars->{constraint_type};
my $method = "_build_${type}_sql";
my $sql = $self->$method($vars);
and have sub _build_lastyear_sql { ... } and such

you'll always have a constraint_type and you can dispatch by that,
e.g. simply use it to call a method named "handle_${constraint_type}_query"

the nice thing about dispatching to different methods per constraint type means
you can do things like put them into reusable roles and such
=cut

# sets __PACKAGE__->constraint & __PACKAGE__constraint_title:
sub _set_search_constraints {
    my $self = shift;
    my $time_column = shift; # warn $time_column;
    my $default_constraint_type = shift || ''; # optional - to override return

    my $vars = $self->params; # warn Dumper $vars;
    
    my $constraint_type # undef on 1st call, unless default set in model method 
        = $vars->{constraint_type} || $default_constraint_type || ''; 
    
    my ($constraint, $title);
    
    # for checkboxes it's safe to examine $constraint_type, for textboxes &
    # selects, need to test for defined query param as it's possible to submit an
    # empty field, where constraint_type is defined but equivalent param is undef
    if ( $constraint_type eq 'all_data' ) {
        $constraint = qq!year($time_column) IS NOT NULL!; # ie everything
        $title = 'all requests';
    }
    elsif ( $constraint_type eq 'this_year' ) {
        my $this_year = DateTime->now->year;
        $constraint = qq!year($time_column) = $this_year!;
        $title =  'year ' . $this_year;
    }
    elsif ( $constraint_type eq 'one_month' ) {
        $constraint = qq!$time_column between date_sub(curdate(), interval
            1 month) and curdate()!;
        $title = 'last 1 month';
    }
    elsif ( $constraint_type eq 'year_since' && $vars->{year} ) { # check var sent
        $constraint = qq!year($time_column) >= $vars->{year}!;
        $title = 'all from ' . $vars->{year};
    }
    # textboxes & select fields:
    elsif ( my $days = $vars->{days} ) { # $constraint_type eq 'days'
        $constraint = qq!$time_column between date_sub(curdate(), interval $days
            day) and curdate()!;
        $title = 'previous ' . $days . ' days';
    }
    elsif ( my $year = $vars->{year} ) { # $constraint_type eq 'year'
        $constraint = qq!year($time_column) = $year!;
        $title = 'year ' . $vars->{year};
    }
    # need both 'from' & 'to' dates:
    elsif ( $vars->{date_from} && $vars->{date_to} ) { # $constraint_type eq 'date_range'
        # NB: silently fails if date is invalid:
        my $start_date = LIMS::Local::Utils::date_to_mysql($vars->{date_from});
        my $end_date   = LIMS::Local::Utils::date_to_mysql($vars->{date_to});
        
        # need to ensure no date conversion error:
        if ($start_date && $end_date) {
            $constraint = qq!$time_column between '$start_date' and '$end_date'!;
            $title = 'between ' . $vars->{date_from} . ' & ' . $vars->{date_to};
        }
    }
    # set default to previous calendar year if no form constraint or invalid date: 
    $constraint ||= qq!$time_column between date_sub(curdate(), interval 1 year)
        and curdate()!;
    $title ||= 'last 365 days';
    
    # set package accessors:
    $self->constraint($constraint);
    $self->constraint_title($title);
}

1;