/**
* 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;
}
/**
* 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 getTableOrder()
{
$this->__initializer__ && $this->__initializer__->__invoke($this, 'getTableOrder', array());
return parent::getTableOrder();
}