public function testFilter()
{
$source = clone $this->source;
/*
* LIKE
*/
$filter = new Filter();
$filter->setFromColumn($this->colVolumne, '~7');
$source->addFilter($filter);
$source->execute();
$this->assertEquals(2, $source->getPaginatorAdapter()->count());
}
public function testFilter()
{
$source = clone $this->source;
$this->assertNull($source->getData()->getDQLPart('where'));
/*
* LIKE
*/
$filter = new Filter();
$filter->setFromColumn($this->colUserDisplayName, '~7');
$source->addFilter($filter);
$source->execute();
$this->assertInstanceOf('Doctrine\\ORM\\Query\\Expr\\Andx', $source->getData()->getDQLPart('where'));
}
/**
* Does the value get filtered?
*
* @param array $row
* @throws \Exception
* @return boolean
*/
public function applyFilter(array $row)
{
$wasTrueOneTime = false;
$isApply = false;
foreach ($this->getFilter()->getValues() as $filterValue) {
$filter = $this->getFilter();
$col = $filter->getColumn();
$value = $row[$col->getUniqueId()];
$value = $col->getType()->getFilterValue($value);
if ($filter->getOperator() == DatagridFilter::BETWEEN) {
//BETWEEN have to be tested in one call
$isApply = DatagridFilter::isApply($value, $this->getFilter()->getValues(), $filter->getOperator());
return $isApply;
} else {
$isApply = DatagridFilter::isApply($value, $filterValue, $filter->getOperator());
}
if (true === $isApply) {
$wasTrueOneTime = true;
}
switch ($filter->getOperator()) {
case DatagridFilter::NOT_LIKE:
case DatagridFilter::NOT_LIKE_LEFT:
case DatagridFilter::NOT_LIKE_RIGHT:
case DatagridFilter::NOT_EQUAL:
case DatagridFilter::NOT_IN:
if (false === $isApply) {
// normally one "match" is okay -> so it's applied
// but e.g. NOT_LIKE is not allowed to match so even if the othere rules are true
// it has to fail!
return false;
}
break;
}
}
if (false === $isApply && true === $wasTrueOneTime) {
return true;
}
return $isApply;
}
/**
* Get the default filter conditions defined for the columns
*
* @return array
*/
public function getFiltersDefault()
{
$filters = [];
// @todo skip this, if $grid->isUserFilterEnabled() ?
if ($this->getRequest() instanceof ConsoleRequest || $this->getRequest() instanceof HttpRequest && !$this->getRequest()->isPost()) {
foreach ($this->getColumns() as $column) {
/* @var $column \ZfcDatagrid\Column\AbstractColumn */
if ($column->hasFilterDefaultValue() === true) {
$filter = new Filter();
$filter->setFromColumn($column, $column->getFilterDefaultValue());
$filters[] = $filter;
$column->setFilterActive($filter->getDisplayColumnValue());
}
}
}
return $filters;
}
/**
*
* @param array $columns
* @return string
*/
public function __invoke(array $columns)
{
$return = [];
foreach ($columns as $column) {
/* @var $column \ZfcDatagrid\Column\AbstractColumn */
$options = ['name' => (string) $column->getUniqueId(), 'index' => (string) $column->getUniqueId(), 'label' => $this->translate((string) $column->getLabel()), 'width' => $column->getWidth(), 'hidden' => (bool) $column->isHidden(), 'sortable' => (bool) $column->isUserSortEnabled(), 'search' => (bool) $column->isUserFilterEnabled()];
/**
* Formatting
*/
$formatter = $this->getFormatter($column);
if ($formatter != '') {
$options['formatter'] = (string) $formatter;
}
if ($column->getType() instanceof Type\Number) {
$options['align'] = (string) 'right';
}
/**
* Cellattr
*/
$rendererParameters = $column->getRendererParameters('jqGrid');
if (isset($rendererParameters['cellattr'])) {
$options['cellattr'] = (string) $rendererParameters['cellattr'];
}
/**
* Filtering
*/
$searchoptions = [];
$searchoptions['clearSearch'] = false;
if ($column->hasFilterSelectOptions() === true) {
$options['stype'] = 'select';
$searchoptions['value'] = $column->getFilterSelectOptions();
}
if ($column->hasFilterDefaultValue() === true) {
$filter = new \ZfcDatagrid\Filter();
$filter->setFromColumn($column, $column->getFilterDefaultValue());
$searchoptions['defaultValue'] = $filter->getDisplayColumnValue();
}
if (count($searchoptions) > 0) {
$options['searchoptions'] = $searchoptions;
}
/**
* Because with json_encode we get problems, it's custom made!
*/
$colModel = [];
foreach ($options as $key => $value) {
if (is_array($value)) {
$value = json_encode($value);
} elseif (is_bool($value)) {
if (true === $value) {
$value = 'true';
} else {
$value = 'false';
}
} elseif ('formatter' == $key) {
if (stripos($value, 'formatter') === false && stripos($value, 'function') === false) {
$value = '"' . $value . '"';
}
} elseif ('cellattr' == $key) {
// SKIP THIS
} else {
$value = '"' . $value . '"';
}
$colModel[] = (string) $key . ': ' . $value;
}
$return[] = '{' . implode(',', $colModel) . '}';
}
return '[' . implode(',', $return) . ']';
}
/**
* @param DatagridFilter $filter
* @throws \Exception
*/
public function applyFilter(DatagridFilter $filter)
{
$select = $this->getSelect();
$adapter = $this->getSql()->getAdapter();
$qi = function ($name) use($adapter) {
return $adapter->getPlatform()->quoteIdentifier($name);
};
$col = $filter->getColumn();
if (!$col instanceof Column\Select) {
throw new \Exception('This column cannot be filtered: ' . $col->getUniqueId());
}
$colString = $col->getSelectPart1();
if ($col->getSelectPart2() != '') {
$colString .= '.' . $col->getSelectPart2();
}
if ($col instanceof Column\Select && $col->hasFilterSelectExpression()) {
$colString = sprintf($col->getFilterSelectExpression(), $colString);
}
$values = $filter->getValues();
$wheres = [];
foreach ($values as $value) {
$where = new Where();
switch ($filter->getOperator()) {
case DatagridFilter::LIKE:
$wheres[] = $where->like($colString, '%' . $value . '%');
break;
case DatagridFilter::LIKE_LEFT:
$wheres[] = $where->like($colString, '%' . $value);
break;
case DatagridFilter::LIKE_RIGHT:
$wheres[] = $where->like($colString, $value . '%');
break;
case DatagridFilter::NOT_LIKE:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value . '%']);
break;
case DatagridFilter::NOT_LIKE_LEFT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value]);
break;
case DatagridFilter::NOT_LIKE_RIGHT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', [$value . '%']);
break;
case DatagridFilter::EQUAL:
$wheres[] = $where->equalTo($colString, $value);
break;
case DatagridFilter::NOT_EQUAL:
$wheres[] = $where->notEqualTo($colString, $value);
break;
case DatagridFilter::GREATER_EQUAL:
$wheres[] = $where->greaterThanOrEqualTo($colString, $value);
break;
case DatagridFilter::GREATER:
$wheres[] = $where->greaterThan($colString, $value);
break;
case DatagridFilter::LESS_EQUAL:
$wheres[] = $where->lessThanOrEqualTo($colString, $value);
break;
case DatagridFilter::LESS:
$wheres[] = $where->lessThan($colString, $value);
break;
case DatagridFilter::BETWEEN:
$wheres[] = $where->between($colString, $values[0], $values[1]);
break 2;
default:
throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
break;
}
}
if (!empty($wheres)) {
$set = new PredicateSet($wheres, PredicateSet::OP_OR);
$select->where->andPredicate($set);
}
}
public function testIsApplyInvalidArgumentException()
{
$filter = new Filter();
$this->setExpectedException('\\InvalidArgumentException');
$filter->isApply(123, 100, 'UndefinedFilter');
}
public function testBetween()
{
$filter = new \ZfcDatagrid\Filter();
$filter->setFromColumn($this->column, '15 <> 30');
$this->assertEquals(Filter::BETWEEN, $filter->getOperator());
$filterArray = new FilterArray($filter);
$this->assertTrue($filterArray->applyFilter(['myCol' => '15']));
$this->assertTrue($filterArray->applyFilter(['myCol' => '20']));
$this->assertTrue($filterArray->applyFilter(['myCol' => '30']));
$this->assertFalse($filterArray->applyFilter(['myCol' => '14']));
$this->assertFalse($filterArray->applyFilter(['myCol' => '31']));
}
/**
* Display this action on this row?
*
* @param array $row
* @return boolean
*/
public function isDisplayed(array $row)
{
if ($this->hasShowOnValues() === false) {
return true;
}
$isDisplayed = false;
foreach ($this->getShowOnValues() as $rule) {
$value = '';
if (isset($row[$rule['column']->getUniqueId()])) {
$value = $row[$rule['column']->getUniqueId()];
}
if ($rule['value'] instanceof AbstractColumn) {
if (isset($row[$rule['value']->getUniqueId()])) {
$ruleValue = $row[$rule['value']->getUniqueId()];
} else {
$ruleValue = '';
}
} else {
$ruleValue = $rule['value'];
}
$isDisplayedMatch = Filter::isApply($value, $ruleValue, $rule['comparison']);
if ($this->getShowOnValueOperator() == 'OR' && true === $isDisplayedMatch) {
// For OR one match is enough
return true;
} elseif ($this->getShowOnValueOperator() == 'AND' && false === $isDisplayedMatch) {
return false;
} else {
$isDisplayed = $isDisplayedMatch;
}
}
return $isDisplayed;
}
/**
* Get the default filter conditions defined for the columns
*
* @return Filter[]
*/
public function getFiltersDefault()
{
$filters = [];
foreach ($this->getColumns() as $column) {
/* @var $column \ZfcDatagrid\Column\AbstractColumn */
if ($column->hasFilterDefaultValue() === true) {
$filter = new Filter();
$filter->setFromColumn($column, $column->getFilterDefaultValue());
$filters[] = $filter;
$column->setFilterActive($filter->getDisplayColumnValue());
}
}
return $filters;
}
/**
* @param DatagridFilter $filter
* @throws \InvalidArgumentException
*/
public function applyFilter(DatagridFilter $filter)
{
$qb = $this->getQueryBuilder();
$expr = new Expr();
$column = $filter->getColumn();
$colString = $column->getSelectPart1();
if ($column->getSelectPart2() != '') {
$colString .= '.' . $column->getSelectPart2();
}
if ($column instanceof Column\Select && $column->hasFilterSelectExpression()) {
$colString = sprintf($column->getFilterSelectExpression(), $colString);
}
$values = $filter->getValues();
$wheres = [];
foreach ($values as $key => $value) {
$valueParameterName = ':' . str_replace('.', '', $column->getUniqueId() . $key);
switch ($filter->getOperator()) {
case DatagridFilter::LIKE:
$wheres[] = $expr->like($colString, $valueParameterName);
$qb->setParameter($valueParameterName, '%' . $value . '%');
break;
case DatagridFilter::LIKE_LEFT:
$wheres[] = $expr->like($colString, $valueParameterName);
$qb->setParameter($valueParameterName, '%' . $value);
break;
case DatagridFilter::LIKE_RIGHT:
$wheres[] = $expr->like($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value . '%');
break;
case DatagridFilter::NOT_LIKE:
$wheres[] = $expr->notLike($colString, $valueParameterName);
$qb->setParameter($valueParameterName, '%' . $value . '%');
break;
case DatagridFilter::NOT_LIKE_LEFT:
$wheres[] = $expr->notLike($colString, $valueParameterName);
$qb->setParameter($valueParameterName, '%' . $value);
break;
case DatagridFilter::NOT_LIKE_RIGHT:
$wheres[] = $expr->notLike($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value . '%');
break;
case DatagridFilter::EQUAL:
$wheres[] = $expr->eq($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::NOT_EQUAL:
$wheres[] = $expr->neq($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::GREATER_EQUAL:
$wheres[] = $expr->gte($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::GREATER:
$wheres[] = $expr->gt($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::LESS_EQUAL:
$wheres[] = $expr->lte($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::LESS:
$wheres[] = $expr->lt($colString, $valueParameterName);
$qb->setParameter($valueParameterName, $value);
break;
case DatagridFilter::BETWEEN:
$minParameterName = ':' . str_replace('.', '', $colString . '0');
$maxParameterName = ':' . str_replace('.', '', $colString . '1');
$wheres[] = $expr->between($colString, $minParameterName, $maxParameterName);
$qb->setParameter($minParameterName, $values[0]);
$qb->setParameter($maxParameterName, $values[1]);
break 2;
default:
throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
break;
}
}
if (count($wheres) > 0) {
$orWhere = $qb->expr()->orX();
$orWhere->addMultiple($wheres);
$qb->andWhere($orWhere);
}
}
