/**
* @param QueryBuilder $qb
* @param string $dqlAlias
*
* @return string
*/
public function getFilter(QueryBuilder $qb, $dqlAlias)
{
if ($this->child instanceof Filter) {
$qb->having($this->child->getFilter($qb, $dqlAlias));
} else {
$qb->having($this->child);
}
}
/**
* @param QueryBuilder $qb
* @param Search $search
* @return QueryBuilder
*/
public function map(QueryBuilder $qb, Search $search)
{
$qb->select('c, s')->from('EtuModuleCovoitBundle:Covoit', 'c')->leftJoin('c.subscriptions', 's');
if (!$search->olds) {
$qb->andWhere('c.date > CURRENT_DATE()');
}
if ($search->startCity) {
$qb->andWhere('c.startCity = :startCity')->setParameter('startCity', $search->startCity->getId());
}
if ($search->endCity) {
$qb->andWhere('c.endCity = :endCity')->setParameter('endCity', $search->endCity->getId());
}
if ($search->date) {
if ($search->dateBeforeAfter) {
$before = clone $search->date;
$before->add(\DateInterval::createFromDateString('-1 day'));
$after = clone $search->date;
$after->add(\DateInterval::createFromDateString('1 day'));
$qb->andWhere('c.date BETWEEN :before AND :after')->setParameter('before', $before->format('Y-m-d') . ' 00:00:00')->setParameter('after', $after->format('Y-m-d') . ' 00:00:00');
} else {
$qb->andWhere('c.date = :date')->setParameter('date', $search->date->format('Y-m-d') . ' 00:00:00');
}
}
if ($search->priceMax) {
$qb->andWhere('c.price <= :priceMax')->setParameter('priceMax', $search->priceMax);
}
if ($search->hourMin) {
$qb->andWhere('c.startHour >= :hourMin')->setParameter('hourMin', $search->hourMin->format('H:i:s'));
}
if ($search->hourMax) {
$qb->andWhere('c.startHour <= :hourMax')->setParameter('hourMax', $search->hourMax->format('H:i:s'));
}
if ($search->keywords) {
$qb->andWhere('c.notes LIKE :keywords')->setParameter('keywords', '%' . implode('%', explode(' ', $search->keywords)) . '%');
}
if ($search->placesLeft) {
$qb->having('(c.capacity - COUNT(s)) >= :placesLeft')->setParameter('placesLeft', $search->placesLeft);
}
return $qb;
}
/**
* @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;
}
/**
* @param \Doctrine\ORM\QueryBuilder $queryBuilder
* @param $filter
*
* @return QueryBuilder
*/
private function processFilter(QueryBuilder $queryBuilder, $filter)
{
if (isset($filter['searchString']) && '' !== $filter['searchString']) {
$locationExpr = $queryBuilder->expr()->orX();
foreach (explode(' ', $filter['searchString']) as $keywordIndex => $keyword) {
$queryParameter = 'searchString' . $keywordIndex;
$queryBuilder->andWhere('event.title LIKE :' . $queryParameter);
$queryBuilder->setParameter($queryParameter, '%' . trim($keyword) . '%');
$locationExpr->add($queryBuilder->expr()->like('event.city', ':' . $queryParameter));
}
if (!isset($filter['lat']) && !isset($filter['long'])) {
$queryBuilder->orWhere($locationExpr);
}
}
if (isset($filter['eventIds'])) {
$queryBuilder->andWhere('event.id IN (:eventIds)');
$queryBuilder->setParameter('eventIds', $filter['eventIds']);
}
if (isset($filter['isTopEvent'])) {
$queryBuilder->andWhere('event.isTopEvent = :isTopEvent');
$queryBuilder->setParameter('isTopEvent', $filter['isTopEvent']);
}
if (isset($filter['categories']) && '' !== $filter['categories']) {
$queryBuilder->leftJoin('event.categories', 'eventCategories');
$queryBuilder->andWhere('eventCategories.id IN (:categories)');
$queryBuilder->setParameter('categories', $filter['categories']);
}
if (isset($filter['dateFrom']) && '' !== $filter['dateFrom']) {
/** @var DateTime $dateFrom */
$dateFrom = $filter['dateFrom'];
$queryBuilder->andWhere('event.startDate >= :startDate');
$queryBuilder->setParameter('startDate', $dateFrom->format('Y-m-d'));
}
if (isset($filter['dateFrom']) && '' !== $filter['dateFrom'] && (isset($filter['dateTo']) && '' !== $filter['dateTo'])) {
/** @var DateTime $dateTo */
$dateTo = $filter['dateTo'];
$queryBuilder->andWhere('event.startDate <= :startEnd');
$queryBuilder->setParameter('startEnd', $dateTo->format('Y-m-d'));
} elseif (isset($filter['dateTo']) && '' !== $filter['dateTo']) {
/** @var DateTime $dateTo */
$dateTo = $filter['dateTo'];
$queryBuilder->andWhere('event.startDate <= :startEnd');
$queryBuilder->setParameter('startEnd', $dateTo->format('Y-m-d'));
}
if (isset($filter['lat']) && isset($filter['long'])) {
$queryBuilder->addSelect('( 3959 * acos(cos(radians(:latitude)) ' . '* cos(radians(event.latitude)) ' . '* cos(radians(event.longitude) ' . '- radians(:longitude)) ' . '+ sin(radians(:latitude)) ' . '* sin(radians(event.latitude)))) AS distance');
$queryBuilder->setParameter('latitude', $filter['lat']);
$queryBuilder->setParameter('longitude', $filter['long']);
$queryBuilder->having('distance <= :area');
$queryBuilder->setParameter('area', $filter['area']);
$queryBuilder->orderBy('distance', 'ASC');
} else {
$queryBuilder->orderBy('event.startDate', 'ASC');
}
if (isset($filter['country'])) {
$queryBuilder->andWhere('event.country = :country');
$queryBuilder->setParameter('country', $filter['country']);
}
return $queryBuilder;
}
/**
* {@inheritdoc}
*/
public function parse($value, QueryBuilder $qb)
{
if (!is_array($value)) {
$value = Yaml::parse($value);
}
$processor = new Processor();
$value = $processor->processConfiguration(new QueryConfiguration(), $value);
if (!isset($value['from'])) {
throw new \RuntimeException('Missing mandatory "from" section');
}
foreach ((array) $value['from'] as $from) {
$qb->from($from['table'], $from['alias']);
}
if (isset($value['select'])) {
foreach ($value['select'] as $select) {
$qb->add('select', new Expr\Select($select), true);
}
}
if (isset($value['distinct'])) {
$qb->distinct((bool) $value['distinct']);
}
if (isset($value['groupBy'])) {
$qb->groupBy($value['groupBy']);
}
if (isset($value['having'])) {
$qb->having($value['having']);
}
$this->addJoin($qb, $value);
$this->addWhere($qb, $value);
$this->addOrder($qb, $value);
return $qb;
}
