/**
* @param QueryBuilder $qb
* @param string $dqlAlias
*/
public function modify(QueryBuilder $qb, $dqlAlias)
{
if ($this->dqlAlias !== null) {
$dqlAlias = $this->dqlAlias;
}
$qb->addGroupBy(sprintf('%s.%s', $dqlAlias, $this->field));
}
/**
* Prepare query builder to get modules list
*
* @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare
*
* @return void
*/
protected function addGroupByCondition(\Doctrine\ORM\QueryBuilder $queryBuilder)
{
$queryBuilder->addGroupBy('m.name')->addGroupBy('m.author');
}
/**
*
* @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;
}
/**
* Add 'Group By product_id' expression
*
* @param \Doctrine\ORM\QueryBuilder $qb Query builder routine
*
* @return void
*/
protected function addGroupByProductId(\Doctrine\ORM\QueryBuilder $qb)
{
$qb->addGroupBy('p.product_id');
}
/**
* @param DateTime $start
* @param DateTime $end
* @param QueryBuilder $qb
* @param $entityField
*/
public function addDatePartsSelect(DateTime $start, DateTime $end, QueryBuilder $qb, $entityField)
{
switch ($this->getFormatStrings($start, $end)['viewType']) {
case 'year':
$qb->addSelect(sprintf('%s as yearCreated', $this->getEnforcedTimezoneFunction('YEAR', $entityField)));
$qb->addGroupBy('yearCreated');
break;
case 'month':
$qb->addSelect(sprintf('%s as yearCreated', $this->getEnforcedTimezoneFunction('YEAR', $entityField)));
$qb->addSelect(sprintf('%s as monthCreated', $this->getEnforcedTimezoneFunction('MONTH', $entityField)));
$qb->addGroupBy('yearCreated');
$qb->addGroupBy('monthCreated');
break;
case 'date':
$qb->addSelect(sprintf("%s as yearCreated", $this->getEnforcedTimezoneFunction('YEAR', $entityField)));
$qb->addSelect(sprintf('%s as weekCreated', $this->getEnforcedTimezoneFunction('WEEK', $entityField)));
$qb->addGroupBy('yearCreated');
$qb->addGroupBy('weekCreated');
break;
case 'day':
$qb->addSelect(sprintf("%s as yearCreated", $this->getEnforcedTimezoneFunction('YEAR', $entityField)));
$qb->addSelect(sprintf("%s as monthCreated", $this->getEnforcedTimezoneFunction('MONTH', $entityField)));
$qb->addSelect(sprintf("%s as dayCreated", $this->getEnforcedTimezoneFunction('DAY', $entityField)));
$qb->addGroupBy('yearCreated');
$qb->addGroupBy('monthCreated');
$qb->addGroupBy('dayCreated');
break;
case 'time':
$qb->addSelect(sprintf('%s as dateCreated', $this->getEnforcedTimezoneFunction('DATE', $entityField)));
$qb->addSelect(sprintf('%s as hourCreated', $this->getEnforcedTimezoneFunction('HOUR', $entityField)));
$qb->addGroupBy('dateCreated');
$qb->addGroupBy('hourCreated');
break;
}
}
/**
* @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();
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()) {
$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);
foreach ($filters as $filter) {
$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));
}
}
//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->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, $hasHavingClause), "?{$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->having($sub);
} elseif ($isDisjunction) {
$where->add($sub);
}
}
if ($column->getType() === 'array') {
$serializeColumns[] = $column->getId();
}
}
if ($where->count() > 0) {
$this->query->where($where);
}
foreach ($this->joins as $alias => $field) {
$this->query->leftJoin($field, $alias);
$this->querySelectfromSource->leftJoin($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();
// 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);
}
//call overridden prepareRow or associated closure
if (($modifiedRow = $this->prepareRow($row)) != null) {
$result->addRow($modifiedRow);
}
}
return $result;
}
/**
* Traite les extras pour la requête.
*
* @param \Doctrine\ORM\QueryBuilder $queryBuilder QueryBuilder
* @param array $extras Extras
* @return \Doctrine\ORM\QueryBuilder QueryBuilder à jour
*/
private function processQueryBuilderExtras(QueryBuilder $queryBuilder, array $extras)
{
if (isset($extras[self::SELECTS])) {
foreach ($extras[self::SELECTS] as $select => $selectAlias) {
if (self::SELECT_JOIN == $selectAlias) {
$queryBuilder->addSelect($select);
} else {
$queryBuilder->addSelect($this->getCompleteProperty($select) . ' AS ' . $selectAlias);
}
}
}
if (isset($extras[self::LEFT_JOINS])) {
foreach ($extras[self::LEFT_JOINS] as $leftJoin => $leftJoinAlias) {
$queryBuilder->leftJoin($this->getCompleteProperty($leftJoin), $leftJoinAlias);
}
}
if (isset($extras[self::INNER_JOINS])) {
foreach ($extras[self::INNER_JOINS] as $innerJoin => $innerJoinAlias) {
$queryBuilder->innerJoin($this->getCompleteProperty($innerJoin), $innerJoinAlias);
}
}
if (isset($extras[self::GROUP_BYS])) {
foreach ($extras[self::GROUP_BYS] as $groupBy) {
if (isset($extras[self::SELECTS]) && in_array($groupBy, array_values($extras[self::SELECTS]))) {
$queryBuilder->addGroupBy($groupBy);
} else {
$queryBuilder->addGroupBy($this->getCompleteProperty($groupBy));
}
}
}
return $queryBuilder;
}
