/**
* @param \APY\DataGridBundle\Grid\Column\Column[] $columns
* @param int $page Page Number
* @param int $limit Rows Per Page
* @param int $gridDataJunction Grid data junction
* @return \APY\DataGridBundle\Grid\Rows
*/
public function execute($columns, $page = 0, $limit = 0, $maxResults = null, $gridDataJunction = Column::DATA_CONJUNCTION)
{
$this->query = $this->getQueryBuilder();
$this->querySelectfromSource = clone $this->query;
$bindIndex = 123;
$serializeColumns = array();
$where = $gridDataJunction === Column::DATA_CONJUNCTION ? $this->query->expr()->andx() : $this->query->expr()->orx();
$columnsById = array();
foreach ($columns as $column) {
$columnsById[$column->getId()] = $column;
}
foreach ($columns as $column) {
// If a column is a manual field, ie a.col*b.col as myfield, it is added to select from user.
if ($column->getIsManualField() === false) {
$fieldName = $this->getFieldName($column, true);
$this->query->addSelect($fieldName);
$this->querySelectfromSource->addSelect($fieldName);
}
if ($column->isSorted()) {
if ($column->getType() === 'join') {
$this->query->resetDQLPart('orderBy');
foreach ($column->getJoinColumns() as $columnName) {
$this->query->addOrderBy($this->getFieldName($columnsById[$columnName]), $column->getOrder());
}
} else {
$this->query->orderBy($this->getFieldName($column), $column->getOrder());
}
}
if ($column->isFiltered()) {
// Some attributes of the column can be changed in this function
$filters = $column->getFilters('entity');
$isDisjunction = $column->getDataJunction() === Column::DATA_DISJUNCTION;
$hasHavingClause = $column->hasDQLFunction() || $column->getIsAggregate();
$sub = $isDisjunction ? $this->query->expr()->orx() : ($hasHavingClause ? $this->query->expr()->andx() : $where);
/** @var Filter $filter */
foreach ($filters as $filter) {
$operator = $this->normalizeOperator($filter->getOperator());
$columnForFilter = $column->getType() !== 'join' ? $column : $columnsById[$filter->getColumnName()];
$fieldName = $this->getFieldName($columnForFilter, false);
$bindIndexPlaceholder = "?{$bindIndex}";
if (in_array($filter->getOperator(), array(Column::OPERATOR_LIKE, Column::OPERATOR_RLIKE, Column::OPERATOR_LLIKE, Column::OPERATOR_NLIKE))) {
$fieldName = "LOWER({$fieldName})";
$bindIndexPlaceholder = "LOWER({$bindIndexPlaceholder})";
}
$q = $this->query->expr()->{$operator}($fieldName, $bindIndexPlaceholder);
if ($filter->getOperator() == Column::OPERATOR_NLIKE || $filter->getOperator() == Column::OPERATOR_NSLIKE) {
$q = $this->query->expr()->not($q);
}
$sub->add($q);
if ($filter->getValue() !== null) {
$this->query->setParameter($bindIndex++, $this->normalizeValue($filter->getOperator(), $filter->getValue()));
}
}
if (method_exists($column, 'addFilterCondition')) {
$column->addFilterCondition($sub, $this->query);
}
if ($hasHavingClause) {
$this->query->andHaving($sub);
} elseif ($isDisjunction) {
$where->add($sub);
}
}
if ($column->getType() === 'array') {
$serializeColumns[] = $column->getId();
}
}
if ($where->count() > 0) {
//Using ->andWhere here to make sure we preserve any other where clauses present in the query builder
//the other where clauses may have come from an external builder
$this->query->andWhere($where);
}
foreach ($this->joins as $alias => $field) {
if (null !== $field['type'] && strtolower($field['type']) === 'inner') {
$join = 'join';
} else {
$join = 'leftJoin';
}
$this->query->{$join}($field['field'], $alias);
$this->querySelectfromSource->{$join}($field['field'], $alias);
}
if ($page > 0) {
$this->query->setFirstResult($page * $limit);
}
if ($limit > 0) {
if ($maxResults !== null && $maxResults - $page * $limit < $limit) {
$limit = $maxResults - $page * $limit;
}
$this->query->setMaxResults($limit);
} elseif ($maxResults !== null) {
$this->query->setMaxResults($maxResults);
}
if (!empty($this->groupBy)) {
$this->query->resetDQLPart('groupBy');
$this->querySelectfromSource->resetDQLPart('groupBy');
foreach ($this->groupBy as $field) {
$this->query->addGroupBy($this->getGroupByFieldName($field));
$this->querySelectfromSource->addGroupBy($this->getGroupByFieldName($field));
}
}
//call overridden prepareQuery or associated closure
$this->prepareQuery($this->query);
$hasJoin = $this->checkIfQueryHasFetchJoin($this->query);
$query = $this->query->getQuery();
foreach ($this->hints as $hintKey => $hintValue) {
$query->setHint($hintKey, $hintValue);
}
$items = new Paginator($query, $hasJoin);
$repository = $this->manager->getRepository($this->entityName);
// Force the primary field to get the entity in the manipulatorRow
$primaryColumnId = null;
foreach ($columns as $column) {
if ($column->isPrimary()) {
$primaryColumnId = $column->getId();
break;
}
}
// hydrate result
$result = new Rows();
foreach ($items as $item) {
$row = new Row();
foreach ($item as $key => $value) {
$key = str_replace('::', '.', $key);
if (in_array($key, $serializeColumns) && is_string($value)) {
$value = unserialize($value);
}
$row->setField($key, $value);
}
$row->setPrimaryField($primaryColumnId);
//Setting the representative repository for entity retrieving
$row->setRepository($repository);
//call overridden prepareRow or associated closure
if (($modifiedRow = $this->prepareRow($row)) != null) {
$result->addRow($modifiedRow);
}
}
return $result;
}
/**
*
* @param type $columns
* @param type $page
* @param type $limit
* @param type $maxResults
* @param type $gridDataJunction
* @return \APY\DataGridBundle\Grid\Rows
*/
public function execute($columns, $page = 0, $limit = 0, $maxResults = null, $gridDataJunction = Column::DATA_CONJUNCTION)
{
$this->query = $this->getQueryBuilder();
$this->querySelectfromSource = clone $this->query;
$bindIndex = 123;
$serializeColumns = array();
$where = $gridDataJunction === Column::DATA_CONJUNCTION ? $this->query->expr()->andx() : $this->query->expr()->orx();
foreach ($columns as $column) {
if (!in_array($column->getId(), $this->excludedColumns)) {
$fieldName = $this->getFieldName($column, true);
$this->query->addSelect($fieldName);
$this->querySelectfromSource->addSelect($fieldName);
if ($column->isSorted()) {
$this->query->orderBy($this->getFieldName($column), $column->getOrder());
}
if ($column->isFiltered()) {
// Some attributes of the column can be changed in this function
$filters = $column->getFilters('entity');
$isDisjunction = $column->getDataJunction() === Column::DATA_DISJUNCTION;
$hasHavingClause = $column->hasDQLFunction();
$sub = $isDisjunction ? $this->query->expr()->orx() : ($hasHavingClause ? $this->query->expr()->andx() : $where);
foreach ($filters as $filter) {
// \Doctrine\Common\Util\Debug::dump($column);
$operator = $this->normalizeOperator($filter->getOperator());
$q = $this->query->expr()->{$operator}($this->getFieldName($column, false), "?{$bindIndex}");
if ($filter->getOperator() == Column::OPERATOR_NLIKE) {
$q = $this->query->expr()->not($q);
}
$sub->add($q);
if ($filter->getValue() !== null) {
$this->query->setParameter($bindIndex++, $this->normalizeValue($filter->getOperator(), $filter->getValue()));
}
}
if ($hasHavingClause) {
$this->query->andHaving($sub);
} elseif ($isDisjunction) {
$where->add($sub);
}
}
if ($column->getType() === 'array') {
$serializeColumns[] = $column->getId();
}
}
}
if ($where->count() > 0) {
//Using ->andWhere here to make sure we preserve any other where clauses present in the query builder
//the other where clauses may have come from an external builder
$this->query->andWhere($where);
}
foreach ($this->joins as $alias => $field) {
if (null !== $field['type'] && strtolower($field['type']) === 'inner') {
$join = 'join';
} else {
$join = 'leftJoin';
}
$this->query->{$join}($field['field'], $alias);
$this->querySelectfromSource->{$join}($field['field'], $alias);
}
if ($page > 0) {
$this->query->setFirstResult($page * $limit);
}
if ($limit > 0) {
if ($maxResults !== null && $maxResults - $page * $limit < $limit) {
$limit = $maxResults - $page * $limit;
}
$this->query->setMaxResults($limit);
} elseif ($maxResults !== null) {
$this->query->setMaxResults($maxResults);
}
if (!empty($this->groupBy)) {
$this->query->resetDQLPart('groupBy');
$this->querySelectfromSource->resetDQLPart('groupBy');
foreach ($this->groupBy as $field) {
$this->query->addGroupBy($this->getGroupByFieldName($field));
$this->querySelectfromSource->addGroupBy($this->getGroupByFieldName($field));
}
}
if ($this->groupById === true) {
$this->query->addGroupBy($this->getGroupByFieldName('id'));
}
//call overridden prepareQuery or associated closure
$this->prepareQuery($this->query);
$query = $this->query->getQuery();
foreach ($this->hints as $hintKey => $hintValue) {
$query->setHint($hintKey, $hintValue);
}
$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'TMSolution\\DataGridBundle\\Walker\\MysqlWalker');
$query->setHint("mysqlWalker.count", true);
$items = $query->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);
$this->prepareTotalCount();
// $oids = array();
// foreach ($items as $item) {
//
// $oid = ObjectIdentity::fromDomainObject($item);
// $oids[] = $oid;
// }
//
// $this->aclProvider->findAcls($oids); // preload Acls from database
//
// foreach ($items as $item) {
// if (false === $this->securityContext->isGranted('VIEW', $item)) {
// // denied
// throw new AccessDeniedException();
// }
// }
// var_dump($items);
$repository = $this->manager->getRepository($this->entityName);
// Force the primary field to get the entity in the manipulatorRow
$primaryColumnId = null;
foreach ($columns as $column) {
if ($column->isPrimary()) {
$primaryColumnId = $column->getId();
break;
}
}
// hydrate result
$result = new Rows();
foreach ($items as $item) {
$row = new Row();
foreach ($item as $key => $value) {
$key = str_replace('::', '.', $key);
if (in_array($key, $serializeColumns) && is_string($value)) {
//echo $value."\n";
@($value = unserialize($value));
}
$row->setField($key, $value);
}
$row->setPrimaryField($primaryColumnId);
//Setting the representative repository for entity retrieving
$row->setRepository($repository);
//call overridden prepareRow or associated closure
if (($modifiedRow = $this->prepareRow($row)) != null) {
$result->addRow($modifiedRow);
}
}
return $result;
}
/**
* Find data from array|object
*
* @param \APY\DataGridBundle\Grid\Column\Column[] $columns
* @param int $page
* @param int $limit
* @return \APY\DataGridBundle\DataGrid\Rows
*/
public function executeFromData($columns, $page = 0, $limit = 0, $maxResults = null)
{
// Populate from data
$items = $this->getItemsFromData($columns);
$serializeColumns = array();
foreach ($this->data as $key => $item) {
$keep = true;
foreach ($columns as $column) {
$fieldName = $column->getField();
$fieldValue = $items[$key][$fieldName];
$dataIsNumeric = $column->getType() == 'number' || $column->getType() == 'boolean';
if ($column->getType() === 'array') {
$serializeColumns[] = $column->getId();
}
// Filter
if ($column->isFiltered()) {
// Some attributes of the column can be changed in this function
$filters = $column->getFilters('vector');
if ($column->getDataJunction() === Column\Column::DATA_DISJUNCTION) {
$disjunction = true;
$keep = false;
} else {
$disjunction = false;
$keep = true;
}
$found = false;
foreach ($filters as $filter) {
$operator = $filter->getOperator();
$value = $filter->getValue();
// Normalize value
if (!$dataIsNumeric && !$value instanceof \DateTime) {
$value = $this->prepareStringForLikeCompare($value);
switch ($operator) {
case Column\Column::OPERATOR_EQ:
$value = '/^' . preg_quote($value, '/') . '$/i';
break;
case Column\Column::OPERATOR_NEQ:
$value = '/^(?!' . preg_quote($value, '/') . '$).*$/i';
break;
case Column\Column::OPERATOR_LIKE:
$value = '/' . preg_quote($value, '/') . '/i';
break;
case Column\Column::OPERATOR_NLIKE:
$value = '/^((?!' . preg_quote($value, '/') . ').)*$/i';
break;
case Column\Column::OPERATOR_LLIKE:
$value = '/' . preg_quote($value, '/') . '$/i';
break;
case Column\Column::OPERATOR_RLIKE:
$value = '/^' . preg_quote($value, '/') . '/i';
break;
case Column\Column::OPERATOR_SLIKE:
$value = '/' . preg_quote($value, '/') . '/';
break;
case Column\Column::OPERATOR_NSLIKE:
$value = '/^((?!' . preg_quote($value, '/') . ').)*$/';
break;
case Column\Column::OPERATOR_LSLIKE:
$value = '/' . preg_quote($value, '/') . '$/';
break;
case Column\Column::OPERATOR_RSLIKE:
$value = '/^' . preg_quote($value, '/') . '/';
break;
}
}
// Test
switch ($operator) {
case Column\Column::OPERATOR_EQ:
if ($dataIsNumeric) {
$found = abs($fieldValue - $value) < 1.0E-5;
break;
}
case Column\Column::OPERATOR_NEQ:
if ($dataIsNumeric) {
$found = abs($fieldValue - $value) > 1.0E-5;
break;
}
case Column\Column::OPERATOR_LIKE:
case Column\Column::OPERATOR_NLIKE:
case Column\Column::OPERATOR_LLIKE:
case Column\Column::OPERATOR_RLIKE:
case Column\Column::OPERATOR_SLIKE:
case Column\Column::OPERATOR_NSLIKE:
case Column\Column::OPERATOR_LSLIKE:
case Column\Column::OPERATOR_RSLIKE:
$fieldValue = $this->prepareStringForLikeCompare($fieldValue, $column->getType());
$found = preg_match($value, $fieldValue);
break;
case Column\Column::OPERATOR_GT:
$found = $fieldValue > $value;
break;
case Column\Column::OPERATOR_GTE:
$found = $fieldValue >= $value;
break;
case Column\Column::OPERATOR_LT:
$found = $fieldValue < $value;
break;
case Column\Column::OPERATOR_LTE:
$found = $fieldValue <= $value;
break;
case Column\Column::OPERATOR_ISNULL:
$found = $fieldValue === null;
break;
case Column\Column::OPERATOR_ISNOTNULL:
$found = $fieldValue !== null;
break;
}
// AND
if (!$found && !$disjunction) {
$keep = false;
break;
}
// OR
if ($found && $disjunction) {
$keep = true;
break;
}
}
if (!$keep) {
unset($items[$key]);
break;
}
}
}
}
// Order
foreach ($columns as $column) {
if ($column->isSorted()) {
$sortType = SORT_REGULAR;
$sortedItems = array();
foreach ($items as $key => $item) {
$value = $item[$column->getField()];
// Format values for sorting and define the type of sort
switch ($column->getType()) {
case 'text':
$sortedItems[$key] = strtolower($value);
$sortType = SORT_STRING;
break;
case 'datetime':
case 'date':
case 'time':
if ($value instanceof \DateTime) {
$sortedItems[$key] = $value->getTimestamp();
} else {
$sortedItems[$key] = strtotime($value);
}
$sortType = SORT_NUMERIC;
break;
case 'boolean':
$sortedItems[$key] = $value ? 1 : 0;
$sortType = SORT_NUMERIC;
break;
case 'array':
$sortedItems[$key] = json_encode($value);
$sortType = SORT_STRING;
break;
case 'number':
$sortedItems[$key] = $value;
$sortType = SORT_NUMERIC;
break;
default:
$sortedItems[$key] = $value;
$sortType = SORT_REGULAR;
}
}
if (!empty($sortedItems)) {
array_multisort($sortedItems, $column->getOrder() == 'asc' ? SORT_ASC : SORT_DESC, $sortType, $items);
}
break;
}
}
$this->count = count($items);
// Pagination
if ($limit > 0) {
$maxResults = $maxResults !== null && $maxResults - $page * $limit < $limit ? $maxResults - $page * $limit : $limit;
$items = array_slice($items, $page * $limit, $maxResults);
} elseif ($maxResults !== null) {
$items = array_slice($items, 0, $maxResults);
}
$rows = new Rows();
foreach ($items as $item) {
$row = new Row();
if ($this instanceof Vector) {
$row->setPrimaryField($this->id);
}
foreach ($item as $fieldName => $fieldValue) {
if ($this instanceof Entity) {
if (in_array($fieldName, $serializeColumns)) {
if (is_string($fieldValue)) {
$fieldValue = unserialize($fieldValue);
}
}
}
$row->setField($fieldName, $fieldValue);
}
//call overridden prepareRow or associated closure
if (($modifiedRow = $this->prepareRow($row)) != null) {
$rows->addRow($modifiedRow);
}
}
$this->items = $items;
return $rows;
}
/**
* @param \APY\DataGridBundle\Grid\Column\Column[] $columns
* @param int $page Page Number
* @param int $limit Rows Per Page
* @param int $gridDataJunction Grid data junction
* @return \APY\DataGridBundle\Grid\Rows
*/
public function execute($columns, $page = 0, $limit = 0, $maxResults = null, $gridDataJunction = Column::DATA_CONJUNCTION)
{
$this->query = $this->manager->createQueryBuilder($this->class);
$this->query->from($this->class, self::TABLE_ALIAS);
$this->querySelectfromSource = clone $this->query;
$bindIndex = 123;
$serializeColumns = array();
$where = $gridDataJunction === Column::DATA_CONJUNCTION ? $this->query->expr()->andx() : $this->query->expr()->orx();
foreach ($columns as $column) {
$fieldName = $this->getFieldName($column, true);
$this->query->addSelect($fieldName);
$this->querySelectfromSource->addSelect($fieldName);
if ($column->isSorted()) {
$this->query->orderBy($this->getFieldName($column), $column->getOrder());
}
if ($column->isFiltered()) {
// Some attributes of the column can be changed in this function
$filters = $column->getFilters('entity');
$isDisjunction = $column->getDataJunction() === Column::DATA_DISJUNCTION;
$hasHavingClause = $column->hasDQLFunction();
$sub = $isDisjunction ? $this->query->expr()->orx() : ($hasHavingClause ? $this->query->expr()->andx() : $where);
foreach ($filters as $filter) {
$operator = $this->normalizeOperator($filter->getOperator());
$q = $this->query->expr()->{$operator}($this->getFieldName($column, false), "?{$bindIndex}");
if (in_array($filter->getOperator(), Column::$virtualNotOperators)) {
$q = $this->query->expr()->not($q);
}
$sub->add($q);
if ($filter->getValue() !== null) {
$this->query->setParameter($bindIndex++, $this->normalizeValue($filter->getOperator(), $filter->getValue()));
}
}
if ($hasHavingClause) {
$this->query->having($sub);
} elseif ($isDisjunction) {
$where->add($sub);
}
}
// Still useful?
if ($column->getType() === 'array') {
$serializeColumns[] = $column->getId();
}
}
if ($where->count() > 0) {
$this->query->where($where);
}
foreach ($this->joins as $alias => $field) {
$join = null !== $field['type'] && strtolower($field['type']) === 'inner' ? 'join' : 'leftJoin';
$this->query->{$join}($field['field'], $alias);
$this->querySelectfromSource->{$join}($field['field'], $alias);
}
if ($page > 0) {
$this->query->setFirstResult($page * $limit);
}
if ($limit > 0) {
if ($maxResults !== null && $maxResults - $page * $limit < $limit) {
$limit = $maxResults - $page * $limit;
}
$this->query->setMaxResults($limit);
} elseif ($maxResults !== null) {
$this->query->setMaxResults($maxResults);
}
if (!empty($this->groupBy)) {
$this->query->resetDQLPart('groupBy');
$this->querySelectfromSource->resetDQLPart('groupBy');
foreach ($this->groupBy as $field) {
$this->query->addGroupBy($this->getGroupByFieldName($field));
$this->querySelectfromSource->addGroupBy($this->getGroupByFieldName($field));
}
}
//call overridden prepareQuery or associated closure
$this->prepareQuery($this->query);
$query = $this->query->getQuery();
foreach ($this->hints as $hintKey => $hintValue) {
$query->setHint($hintKey, $hintValue);
}
$items = $query->getResult();
$repository = $this->manager->getRepository($this->entityName);
// Force the primary field to get the entity in the manipulatorRow
$primaryColumnId = null;
foreach ($columns as $column) {
if ($column->isPrimary()) {
$primaryColumnId = $column->getId();
break;
}
}
// hydrate result
$result = new Rows();
foreach ($items as $item) {
$row = new Row();
foreach ($item as $key => $value) {
$key = str_replace('::', '.', $key);
if (in_array($key, $serializeColumns) && is_string($value)) {
$value = unserialize($value);
}
$row->setField($key, $value);
}
$row->setPrimaryField($primaryColumnId);
//Setting the representative repository for entity retrieving
$row->setRepository($repository);
//call overridden prepareRow or associated closure
if (($modifiedRow = $this->prepareRow($row)) != null) {
$result->addRow($modifiedRow);
}
}
return $result;
}
/**
* @param \APY\DataGridBundle\Grid\Column\Column[] $columns
* @param int $page Page Number
* @param int $limit Rows Per Page
* @param int $gridDataJunction Grid data junction
* @return \APY\DataGridBundle\Grid\Rows
*/
public function execute($columns, $page = 0, $limit = 0, $maxResults = null, $gridDataJunction = Column::DATA_CONJUNCTION)
{
$this->query = $this->manager->createQueryBuilder($this->documentName);
foreach ($columns as $column) {
$this->query->select($column->getField());
if ($column->isSorted()) {
$this->query->sort($column->getField(), $column->getOrder());
}
if ($column->isPrimary()) {
$column->setFilterable(false);
} elseif ($column->isFiltered()) {
// Some attributes of the column can be changed in this function
$filters = $column->getFilters('document');
foreach ($filters as $filter) {
//normalize values
$operator = $this->normalizeOperator($filter->getOperator());
$value = $this->normalizeValue($filter->getOperator(), $filter->getValue());
if ($column->getDataJunction() === Column::DATA_DISJUNCTION) {
$this->query->addOr($this->query->expr()->field($column->getField())->{$operator}($value));
} else {
$this->query->field($column->getField())->{$operator}($value);
}
}
}
}
if ($page > 0) {
$this->query->skip($page * $limit);
}
if ($limit > 0) {
if ($maxResults !== null && $maxResults - $page * $limit < $limit) {
$limit = $maxResults - $page * $limit;
}
$this->query->limit($limit);
} elseif ($maxResults !== null) {
$this->query->limit($maxResults);
}
//call overridden prepareQuery or associated closure
$this->prepareQuery($this->query);
//execute and get results
$result = new Rows();
$cursor = $this->query->getQuery()->execute();
$this->count = $cursor->count();
foreach ($cursor as $resource) {
$row = new Row();
$properties = $this->getClassProperties($resource);
foreach ($columns as $column) {
if (isset($properties[$column->getId()])) {
$row->setField($column->getId(), $properties[$column->getId()]);
}
}
//call overridden prepareRow or associated closure
if (($modifiedRow = $this->prepareRow($row)) != null) {
$result->addRow($modifiedRow);
}
}
return $result;
}
/**
* @param \APY\DataGridBundle\Grid\Row $row
* @param Document $resource
* @throws \Exception if getter for field does not exists
* @return \APY\DataGridBundle\Grid\Row $row with referenced fields
*/
protected function addReferencedFields(Row $row, $resource)
{
foreach ($this->referencedColumns as $parent => $subColumns) {
$node = $this->getClassProperties($resource);
if (isset($node[strtolower($parent)])) {
$node = $node[strtolower($parent)];
foreach ($subColumns as $field) {
$getter = 'get' . ucfirst($field);
if (method_exists($node, $getter)) {
$row->setField($parent . '.' . $field, $node->{$getter}());
} else {
throw new \Exception(sprintf('Method %s for Document %s not exists', $getter, $this->referencedMappings[$parent]));
}
}
}
}
return $row;
}