view release on metacpan or  search on metacpan

lib/Apache/Voodoo/Table.pm  view on Meta::CPAN

			if (scalar(@{$clause}) eq 1) {
				push(@where,"$r->[0] = 1");
			}
			elsif (scalar(@{$clause}) == 3) {
				if ($clause->[1] =~ /^is(\s+not)?$/i && $clause->[2] =~ /^null$/i) {
					push(@where,join(" ",@{$clause}));
				}
				elsif ($clause->[1] =~ /^(=|!=|>|<|>=|<=)/) {
					push(@where,"$clause->[0] $clause->[1] ?");
					push(@values,$clause->[2]);
				}
				elsif ($clause->[1] =~ /^(not )?\s*like/i) {
					if ($dbh->get_info(17) eq "SQLite") {
						push(@where,"$clause->[0] $clause->[1] ? || '%'");
					}
					else {
						push(@where,"$clause->[0] $clause->[1] concat(?,'%')");
					}
					push(@values,$clause->[2]);
				}
			}
		}
		elsif (!$r) {
			push(@where,$clause);
		}
		else {
			return $self->exception("each entry in the search params list must either be a scalar or a 3 element array");
		}
	}

	my $where = ' ';

	if (scalar(@where)) {
		$where = "\nWHERE\n".join(" AND\n",@where)."\n";
	}

	if ($self->{'group_by'}) {
		$where .= "GROUP BY ".$self->{'group_by'}."\n";
	}

	# From the DBI docs. This will give us the database server name.
	my $is_mysql = ($dbh->get_info(17) eq "MySQL")?1:0;

	my $select_stmt =
		"SELECT". (($is_mysql)?" SQL_CALC_FOUND_ROWS ": " ").
		join(",\n",@columns)."\n".
		"FROM $self->{'table'}\n".
		join("\n",@joins).
		$where;


	my $n_desc = $desc;
	if (defined($sort)) {
		my $q = $self->{'list_sort'}->{$sort};

		# if we're sorting on the same key as before, then we have the chance to go descending
		if ($sort eq $last_sort) {
			if ($desc eq '1') {
				$q =~ s/,/ DESC, /g;
				$q .= " DESC";
				$n_desc = 0; # say that we are ascending the next time.
			}
			else {
				$n_desc = 1; # say that we are descending the next time.
			}
		}
		else {
			$n_desc = 1; # we just sorted ascending, so now we need to say to sort descending
			$desc = 0;
		}

		$select_stmt .= "ORDER BY $q\n";
	}
	else {
		# bogus, fry it.
		$sort      = undef;
		$last_sort = undef;
	}

	$select_stmt .= "LIMIT $count OFFSET $offset\n" unless $showall;

	$self->debug($select_stmt);
	my $page_set = $dbh->selectall_arrayref($select_stmt,undef,@values);

	my $res_count;
	if ($is_mysql) {
		$res_count = $dbh->selectall_arrayref("SELECT FOUND_ROWS()")->[0]->[0];
	}
	else {
		my $count_stmt = "SELECT count(*) FROM $self->{table} ".join("\n",@joins).$where;
		$res_count = $dbh->selectall_arrayref($count_stmt,undef,@values)->[0]->[0];
	}

	my %return;

	$return{'SORT_PARAMS'} = $self->mkurlparams(
		{
			'limit'     => $limit,
			'pattern'   => $pattern,
			'showall'   => $showall,
			'desc'      => $n_desc,
			'last_sort' => $sort
		}
	);

	$return{'LIMIT'}   = $self->prep_select($self->{'list_search_items'},$limit);
	$return{'PATTERN'} = $pattern;


	$return{'NUM_MATCHES'} = $res_count;

	################################################################################
	# prep data for the template
	################################################################################
	my %dates;
	foreach (@{$self->{'dates'}}) {
		$dates{$_} = 1;
	}

	my %times;
	foreach (@{$self->{'times'}}) {
		$times{$_} = 1;
	}

	foreach (@{$page_set}) {
		my %v;
		for (my $i=0; $i < @columns; $i++) {
			my $key = $columns[$i];