File Coverage

blib/lib/DBIx/Foo/SearchQuery.pm

Criterion	Covered	Total	%
statement	6	143	4.2
branch	0	62	0.0
condition	0	41	0.0
subroutine	2	22	9.0
pod	0	20	0.0
total	8	288	2.7

package DBIx::Foo::SearchQuery;

use strict;

use Storable;

sub new {

        my $class = $_[0];
		my $query = $_[1];
		my $page = $_[2] || 1;
		my $pagesize = $_[3] || 20;
		my $relation = $_[4] || ' and ';

        my $self = {
                query 	=> $query,
				page 	=> $page,
				pagesize => $pagesize,
				relation => $relation,
                debug 	=> 0
        };

        bless $self, $class;

        return $self;
}

####################################################
# Properties
#

sub IsEmptySearch {

	my $self = $_[0];

	return 0 if scalar keys %{$self->{filter_fields}} > 0;
	return 0 if scalar keys %{$self->{search_fields}} > 0;
	return 0 if scalar keys %{$self->{match_fields}} > 0;
	return 0 unless $self->{sql_fields};

	return 1;
}

sub CurrentPage {

	my $self = $_[0];

	return $self->{page};

}

sub PageSize {

	my $self = $_[0];

	return $self->{pagesize};

}


sub RowcountQuery {

	my $self = $_[0];

	my ($where, @args) = $self->WhereClause();

	my $query = $self->{query} . $where;

	$query = "select count(*) " . substr($query, index($query, "from"));

	return $query, @args;
}

sub DoSearch {

	my ($self, $dbh) = @_;

	my ($query, @args) = $self->Query();

	return $dbh->selectall_arrayref($query, { Slice => {} }, @args);
}

sub DoSearchAsArray {

	my ($self, $dbh) = @_;

	my ($query, @args) = $self->Query();

	return $dbh->selectall_arrayref($query, { Slice => () }, @args);
}

sub DoSearchAsColArray {

	my ($self, $dbh) = @_;

	my ($query, @args) = $self->Query();

	return $dbh->selectcol_arrayref($query, @args);
}

sub GetRowcount {

	my ($self, $dbh) = @_;

	my ($query, @args) = $self->RowcountQuery();

	return $dbh->selectrow_array($query, {}, @args);
}

sub Query {

	my $self = $_[0];

    my $logger = Log::Log4perl->get_logger('db.searchquery.query');

	my $caller = ( caller(2) )[3];

	my ($where, @args) = $self->WhereClause();

	my $query = $self->{query} . $where . $self->SortOrder . $self->Limit;

	$logger->debug("$query (" . join(", ", @args) . ") called by $caller");

	return $query, @args;
}

sub WhereClause {

	my $self = $_[0];

	my $search = "";
	my $filter = "";
	my $sql_fields = "";
	my $match = "";
	my $predicate = "";

	my @args;

	for my $field (keys %{$self->{search_fields}}) {

		$search .= $self->{relation} if $search;
		$search .= "$field like ?";
		push @args, '%' . $self->{search_fields}->{$field} . '%';
	}

	$search = "($search)" if scalar keys %{$self->{search_fields}} > 1;

	for my $field (keys %{$self->{filter_fields}}) {

		# TODO May need to handle various data types here e.g. numeric, date
		# but for now mysql will accept these as strings

		$filter .= " and " if $filter;

		if (ref($self->{filter_fields}->{$field}) eq 'ARRAY') {

			my @placeholders;

			foreach my $value (@{$self->{filter_fields}->{$field}}) {

				push @placeholders, '?';
				push @args, $value;
			}

			$filter .= "$field in (" . (join ',', @placeholders) . ')';
		}
		else {
			$filter .= "$field = ?";
			push @args, $self->{filter_fields}->{$field};
		}
	}

	for my $field (keys %{$self->{match_fields}}) {

		$match .= " and " if $match;

		$match .= "match ($field) against (?)";
		push @args, $self->{match_fields}->{$field};
	}

	$predicate = $self->{predicate_fields}->{predicate};

	if ($self->{sql_fields}) {
		for my $field (@{$self->{sql_fields}}) {

			$sql_fields .= " and " if $sql_fields;
			$sql_fields .= $field;
		}
	}

	# Match ... against ...

	my $where = '';

	$where = " where " if $filter || $search || $sql_fields || $match || $predicate;
	$where .= "$search " if $search;

	$where .= "and " if $search && $filter;
	$where .= "$filter " if $filter;

	$where .= "and " if ($filter || $search) && $sql_fields;
	$where .= "$sql_fields " if $sql_fields;

	$where .= "and " if ($filter || $search || $sql_fields) && $match;
	$where .= "$match " if $match;

	$where .= "and " if ($filter || $search || $sql_fields ||$match) && $predicate;
	$where .= "$predicate " if $predicate;

	return $where, @args;
}

sub SortOrder {

	my $self = $_[0];

	my $orderby;

	# TODO Add logic to build sort order clause from array
	if ($self->{sort}) {

		foreach my $sortfield (@{$self->{sort}}) {

			$orderby .= ", " if $orderby;

			$orderby .= $sortfield;
		}
	}

	$orderby = " order by $orderby" if $orderby;

	return $orderby;
}

sub Limit {

	my $self = $_[0];

	# TODO Add logic to build limit clause from object properties, and handle pages

	my $start = $self->{pagesize} * ($self->{page} - 1);
	my $rows = $self->{pagesize};

	return " limit $start, $rows";
}


####################################################
# Methods
#

sub addSearch
{
	my ($self, $field, $value) = @_;

	if ($value) {
		$self->{search_fields}->{$field} = $value;
	}
}


sub addFilter
{
	my ($self, $field, $value) = @_;

	if ($value) {
		$self->{filter_fields}->{$field} = $value;
	}
}

sub addMatch
{
	my ($self, $field, $value) = @_;

	if ($value) {
		$self->{match_fields}->{$field} = $value;
	}
}

sub addPredicate {

	my $self = $_[0];
	my $value = $_[1];

	$self->{predicate_fields}->{predicate} = $value;

}

sub addSql
{
	# Allows addition of free sql as an extra field
	# intended use e.g. $query->addSql("OtherID in (select OtherID from others where OtherName like '%" . $self->param('other') . "%')" ) if $self->param('other');

	my ($self, $value) = @_;

	$self->{sql_fields} = [] unless $self->{sql_fields};

	if ($value) {
		push @{$self->{sql_fields}}, $value;
	}
}

sub addSort {

	my $self = shift;
	my $field = shift;
	my $order = shift || "ASC";

	if (ref($field) eq 'HASH') {

		my $data = \%{$field};

		$field = $data->{sortfield};
		$order = $data->{sortorder};
	}

	if ($field) {
		push(@{$self->{sort}}, "$field $order");
	}
}

sub freeze {

	my $self = shift;

	return Storable::freeze($self);
}



1;