/**
  * Configures builder
  *
  * This method configures the ReportBuilder. It has to return a configured Doctrine DBAL QueryBuilder.
  *
  * @param array $options Options array
  *
  * @return \Doctrine\DBAL\Query\QueryBuilder
  */
 protected function configureBuilder(array $options)
 {
     $source = $this->entity->getSource();
     $fields = $this->entity->getColumns();
     $order = $this->entity->getTableOrder();
     // Trigger the REPORT_ON_GENERATE event to initialize the QueryBuilder
     /** @type \Symfony\Component\EventDispatcher\ContainerAwareEventDispatcher $dispatcher */
     $dispatcher = $options['dispatcher'];
     $event = new ReportGeneratorEvent($source);
     $dispatcher->dispatch(ReportEvents::REPORT_ON_GENERATE, $event);
     $queryBuilder = $event->getQueryBuilder();
     $selectColumns = array();
     foreach ($fields as $field) {
         if (isset($options['columns'][$field])) {
             $selectColumns[] = "{$field} as \"{$options['columns'][$field]['label']}\"";
         }
     }
     // Set Content Template
     $this->contentTemplate = $event->getContentTemplate();
     $queryBuilder->select(implode(', ', $selectColumns));
     // Add filters as AND values to the WHERE clause if present
     $filters = $this->entity->getFilters();
     if (count($filters)) {
         $expr = $queryBuilder->expr();
         $and = $expr->andX();
         foreach ($filters as $filter) {
             if ($filter['condition'] == 'notEmpty') {
                 $and->add($expr->isNotNull($filter['column']));
                 $and->add($expr->neq($filter['column'], $expr->literal('')));
             } elseif ($filter['condition'] == 'empty') {
                 $and->add($expr->isNull($filter['column']));
                 $and->add($expr->eq($filter['column'], $expr->literal('')));
             } else {
                 $and->add($expr->{$filter['condition']}($filter['column'], $expr->literal($filter['value'])));
             }
         }
         $queryBuilder->where($and);
     }
     if (!empty($options['order'])) {
         if (is_array($options['order'])) {
             list($column, $dir) = $options['order'];
         } else {
             $column = $options['order'];
             $dir = 'ASC';
         }
         $queryBuilder->orderBy($column, $dir);
     } elseif (!empty($order)) {
         foreach ($order as $o) {
             if (!empty($o['column'])) {
                 $queryBuilder->orderBy($o['column'], $o['direction']);
             }
         }
     }
     if (!empty($options['limit'])) {
         $queryBuilder->setFirstResult($options['start'])->setMaxResults($options['limit']);
     }
     return $queryBuilder;
 }
예제 #2
0
 /**
  * Configures builder.
  *
  * This method configures the ReportBuilder. It has to return a configured Doctrine DBAL QueryBuilder.
  *
  * @param array $options Options array
  *
  * @return \Doctrine\DBAL\Query\QueryBuilder
  */
 protected function configureBuilder(array $options)
 {
     // Trigger the REPORT_ON_GENERATE event to initialize the QueryBuilder
     /** @var ReportGeneratorEvent $event */
     $event = $this->dispatcher->dispatch(ReportEvents::REPORT_ON_GENERATE, new ReportGeneratorEvent($this->entity, $options, $this->db->createQueryBuilder()));
     // Build the QUERY
     $queryBuilder = $event->getQueryBuilder();
     // Set Content Template
     $this->contentTemplate = $event->getContentTemplate();
     // Build WHERE clause
     $filtersApplied = false;
     if (isset($options['dynamicFilters'])) {
         $filtersApplied = $this->applyFilters($options['dynamicFilters'], $queryBuilder, $options['filters']);
     }
     if (!$filtersApplied) {
         if (!($filterExpr = $event->getFilterExpression())) {
             $this->applyFilters($this->entity->getFilters(), $queryBuilder, $options['filters']);
         } else {
             $queryBuilder->andWhere($filterExpr);
         }
     }
     // Build ORDER BY clause
     if (!empty($options['order'])) {
         if (is_array($options['order'])) {
             if (isset($o['column'])) {
                 $queryBuilder->orderBy($options['order']['column'], $options['order']['direction']);
             } elseif (!empty($options['order'][0][1])) {
                 list($column, $dir) = $options['order'];
                 $queryBuilder->orderBy($column, $dir);
             } else {
                 foreach ($options['order'] as $order) {
                     $queryBuilder->orderBy($order);
                 }
             }
         } else {
             $queryBuilder->orderBy($options['order']);
         }
     } elseif ($order = $this->entity->getTableOrder()) {
         foreach ($order as $o) {
             if (!empty($o['column'])) {
                 $queryBuilder->orderBy($o['column'], $o['direction']);
             }
         }
     }
     // Build GROUP BY
     if (!empty($options['groupby'])) {
         if (is_array($options['groupby'])) {
             foreach ($options['groupby'] as $groupBy) {
                 $queryBuilder->addGroupBy($groupBy);
             }
         } else {
             $queryBuilder->groupBy($options['groupby']);
         }
     }
     // Build LIMIT clause
     if (!empty($options['limit'])) {
         $queryBuilder->setFirstResult($options['start'])->setMaxResults($options['limit']);
     }
     if (!empty($options['having'])) {
         if (is_array($options['having'])) {
             foreach ($options['having'] as $having) {
                 $queryBuilder->andHaving($having);
             }
         } else {
             $queryBuilder->having($options['having']);
         }
     }
     // Generate a count query in case a formula needs total number
     $countQuery = clone $queryBuilder;
     $countQuery->select('COUNT(*) as count');
     $countSql = sprintf('(%s)', $countQuery->getSQL());
     // Build SELECT clause
     if (!($selectColumns = $event->getSelectColumns())) {
         $fields = $this->entity->getColumns();
         foreach ($fields as $field) {
             if (isset($options['columns'][$field])) {
                 $select = '';
                 $select .= isset($options['columns'][$field]['formula']) ? $options['columns'][$field]['formula'] : $field;
                 if (strpos($select, '{{count}}')) {
                     $select = str_replace('{{count}}', $countSql, $select);
                 }
                 if (isset($options['columns'][$field]['alias'])) {
                     $select .= ' AS ' . $options['columns'][$field]['alias'];
                 }
                 $selectColumns[] = $select;
             }
         }
     }
     $queryBuilder->addSelect(implode(', ', $selectColumns));
     return $queryBuilder;
 }
 /**
  * {@inheritDoc}
  */
 public function getColumns()
 {
     $this->__initializer__ && $this->__initializer__->__invoke($this, 'getColumns', array());
     return parent::getColumns();
 }