/**
* Get report data for view rendering
*
* @param $entity
* @param $formFactory
* @param array $options
*
* @return array
*/
public function getReportData($entity, $formFactory = null, $options = [])
{
// Clone dateFrom/dateTo because they handled separately in charts
$chartDateFrom = isset($options['dateFrom']) ? clone $options['dateFrom'] : new \DateTime('-30 days');
$chartDateTo = isset($options['dateTo']) ? clone $options['dateTo'] : new \DateTime();
$debugData = [];
if (isset($options['dateFrom'])) {
// Fix date ranges if applicable
if (!isset($options['dateTo'])) {
$options['dateTo'] = new \DateTime();
}
// Fix the time frames
if ($options['dateFrom'] == $options['dateTo']) {
$options['dateTo']->modify('+1 day');
}
// Adjust dateTo to be end of day or to current hour if today
$now = new \DateTime();
if ($now->format('Y-m-d') == $options['dateTo']->format('Y-m-d')) {
$options['dateTo'] = $now;
} else {
$options['dateTo']->setTime(23, 59, 59);
}
// Convert date ranges to UTC for fetching tabular data
$options['dateFrom']->setTimeZone(new \DateTimeZone('UTC'));
$options['dateTo']->setTimeZone(new \DateTimeZone('UTC'));
}
$paginate = !empty($options['paginate']);
$reportPage = isset($options['reportPage']) ? $options['reportPage'] : 1;
$data = $graphs = [];
$reportGenerator = new ReportGenerator($this->dispatcher, $this->em->getConnection(), $entity, $formFactory);
$selectedColumns = $entity->getColumns();
$totalResults = $limit = 0;
// Prepare the query builder
$tableDetails = $this->getTableData($entity->getSource());
// Build a reference for column to data column (without table prefix)
$dataColumns = [];
foreach ($tableDetails['columns'] as $dbColumn => &$columnData) {
$dataColumns[$columnData['alias']] = $dbColumn;
}
$orderBy = $this->session->get('mautic.report.' . $entity->getId() . '.orderby', '');
$orderByDir = $this->session->get('mautic.report.' . $entity->getId() . '.orderbydir', 'ASC');
$dataOptions = ['order' => !empty($orderBy) ? [$orderBy, $orderByDir] : false, 'columns' => $tableDetails['columns'], 'filters' => isset($tableDetails['filters']) ? $tableDetails['filters'] : $tableDetails['columns'], 'dateFrom' => isset($options['dateFrom']) ? $options['dateFrom'] : null, 'dateTo' => isset($options['dateTo']) ? $options['dateTo'] : null, 'dynamicFilters' => isset($options['dynamicFilters']) ? $options['dynamicFilters'] : []];
/** @var \Doctrine\DBAL\Query\QueryBuilder $query */
$query = $reportGenerator->getQuery($dataOptions);
$options['translator'] = $this->translator;
$contentTemplate = $reportGenerator->getContentTemplate();
//set what page currently on so that we can return here after form submission/cancellation
$this->session->set('mautic.report.' . $entity->getId() . '.page', $reportPage);
// Reset the orderBy as it causes errors in graphs and the count query in table data
$parts = $query->getQueryParts();
$order = $parts['orderBy'];
$query->resetQueryPart('orderBy');
if (empty($options['ignoreGraphData'])) {
$chartQuery = new ChartQuery($this->em->getConnection(), $chartDateFrom, $chartDateTo);
$options['chartQuery'] = $chartQuery;
// Check to see if this is an update from AJAX
$selectedGraphs = !empty($options['graphName']) ? [$options['graphName']] : $entity->getGraphs();
if (!empty($selectedGraphs)) {
$availableGraphs = $this->getGraphData($entity->getSource());
if (empty($query)) {
$query = $reportGenerator->getQuery();
}
$eventGraphs = [];
$defaultGraphOptions = $options;
$defaultGraphOptions['dateFrom'] = $chartDateFrom;
$defaultGraphOptions['dateTo'] = $chartDateTo;
foreach ($selectedGraphs as $g) {
if (isset($availableGraphs[$g])) {
$graphOptions = isset($availableGraphs[$g]['options']) ? $availableGraphs[$g]['options'] : [];
$graphOptions = array_merge($defaultGraphOptions, $graphOptions);
$eventGraphs[$g] = ['options' => $graphOptions, 'type' => $availableGraphs[$g]['type']];
}
}
$event = new ReportGraphEvent($entity, $eventGraphs, $query);
$this->dispatcher->dispatch(ReportEvents::REPORT_ON_GRAPH_GENERATE, $event);
$graphs = $event->getGraphs();
unset($defaultGraphOptions);
}
}
if (empty($options['ignoreTableData']) && !empty($selectedColumns)) {
if ($paginate) {
// Build the options array to pass into the query
$limit = $this->session->get('mautic.report.' . $entity->getId() . '.limit', $this->defaultPageLimit);
$start = $reportPage === 1 ? 0 : ($reportPage - 1) * $limit;
if ($start < 0) {
$start = 0;
}
// Must make two queries here, one to get count and one to select data
$select = $parts['select'];
// Get the count
$query->select('COUNT(*) as count');
$result = $query->execute()->fetchAll();
$totalResults = !empty($result[0]['count']) ? $result[0]['count'] : 0;
// Set the limit and get the results
if ($limit > 0) {
$query->setFirstResult($start)->setMaxResults($limit);
}
$query->select($select);
$query->add('orderBy', $order);
}
$queryTime = microtime(true);
$data = $query->execute()->fetchAll();
$queryTime = round((microtime(true) - $queryTime) * 1000);
if ($queryTime >= 1000) {
$queryTime *= 1000;
$queryTime .= "s";
} else {
$queryTime .= "ms";
}
if (!$paginate) {
$totalResults = count($data);
}
// Allow plugin to manipulate the data
$event = new ReportDataEvent($entity, $data, $totalResults, $dataOptions);
$this->dispatcher->dispatch(ReportEvents::REPORT_ON_DISPLAY, $event);
$data = $event->getData();
}
if (MAUTIC_ENV == 'dev') {
$debugData['query'] = $query->getSQL();
$params = $query->getParameters();
foreach ($params as $name => $param) {
$debugData['query'] = str_replace(":{$name}", "'{$param}'", $debugData['query']);
}
$debugData['query_time'] = isset($queryTime) ? $queryTime : "N/A";
}
return ['totalResults' => $totalResults, 'data' => $data, 'dataColumns' => $dataColumns, 'graphs' => $graphs, 'contentTemplate' => $contentTemplate, 'columns' => $tableDetails['columns'], 'limit' => $paginate ? $limit : 0, 'dateFrom' => $dataOptions['dateFrom'], 'dateTo' => $dataOptions['dateTo'], 'debug' => $debugData];
}
/**
* Get report data for view rendering
*
* @param $entity
* @param int $reportPage
*
* @return array
*/
public function getReportData($entity, $formFactory, $options = array())
{
$paginate = !empty($options['paginate']);
$reportPage = isset($options['reportPage']) ? $options['reportPage'] : 1;
$data = $graphs = array();
$reportGenerator = new ReportGenerator($this->factory->getSecurityContext(), $formFactory, $entity);
$selectedColumns = $entity->getColumns();
$totalResults = $limit = 0;
// Prepare the query builder
$columns = $this->getTableData($entity->getSource());
$orderBy = $this->factory->getSession()->get('mautic.report.' . $entity->getId() . '.orderby', '');
$orderByDir = $this->factory->getSession()->get('mautic.report.' . $entity->getId() . '.orderbydir', 'ASC');
$dataOptions = array('order' => !empty($orderBy) ? array($orderBy, $orderByDir) : false, 'dispatcher' => $this->factory->getDispatcher(), 'columns' => $columns['columns']);
/** @var \Doctrine\DBAL\Query\QueryBuilder $query */
$query = $reportGenerator->getQuery($dataOptions);
$filters = $this->factory->getSession()->get('mautic.report.' . $entity->getId() . '.filters', array());
if (!empty($filters)) {
$filterParameters = array();
$filterExpressions = $query->expr()->andX();
$repo = $this->getRepository();
foreach ($filters as $f) {
list($expr, $parameters) = $repo->getFilterExpr($query, $f);
$filterExpressions->add($expr);
if (is_array($parameters)) {
$filterParameters = array_merge($filterParameters, $parameters);
}
}
$query->andWhere($filterExpressions);
$query->setParameters($parameters);
}
$contentTemplate = $reportGenerator->getContentTemplate();
//set what page currently on so that we can return here after form submission/cancellation
$this->factory->getSession()->set('mautic.report.' . $entity->getId() . '.page', $reportPage);
// Reset the orderBy as it causes errors in graphs and the count query in table data
$parts = $query->getQueryParts();
$order = $parts['orderBy'];
$query->resetQueryPart('orderBy');
if (empty($options['ignoreGraphData'])) {
// Check to see if this is an update from AJAX
$selectedGraphs = !empty($options['graphName']) ? array($options['graphName']) : $entity->getGraphs();
if (!empty($selectedGraphs)) {
$availableGraphs = $this->getGraphData($entity->getSource());
if (empty($query)) {
$query = $reportGenerator->getQuery();
}
$eventGraphs = array();
foreach ($selectedGraphs as $g) {
if (isset($availableGraphs[$g])) {
$graphOptions = isset($availableGraphs[$g]['options']) ? $availableGraphs[$g]['options'] : array();
if (!empty($options['graphName'])) {
$graphOptions = array_merge($graphOptions, $options);
}
$eventGraphs[$g] = array('options' => $graphOptions, 'type' => $availableGraphs[$g]['type']);
}
}
$event = new ReportGraphEvent($entity, $eventGraphs, $query);
$this->factory->getDispatcher()->dispatch(ReportEvents::REPORT_ON_GRAPH_GENERATE, $event);
$graphs = $event->getGraphs();
}
}
if (empty($options['ignoreTableData']) && !empty($selectedColumns)) {
if ($paginate) {
// Build the options array to pass into the query
$limit = $this->factory->getSession()->get('mautic.report.' . $entity->getId() . '.limit', $this->factory->getParameter('default_pagelimit'));
$start = $reportPage === 1 ? 0 : ($reportPage - 1) * $limit;
if ($start < 0) {
$start = 0;
}
// Must make two queries here, one to get count and one to select data
$select = $parts['select'];
// Get the count
$query->select('COUNT(*) as count');
$result = $query->execute()->fetchAll();
$totalResults = !empty($result[0]['count']) ? $result[0]['count'] : 0;
// Set the limit and get the results
if ($limit > 0) {
$query->setFirstResult($start)->setMaxResults($limit);
}
$query->select($select);
$query->add('orderBy', $order);
}
$data = $query->execute()->fetchAll();
if (!$paginate) {
$totalResults = count($data);
}
}
return array('totalResults' => $totalResults, 'data' => $data, 'graphs' => $graphs, 'contentTemplate' => $contentTemplate, 'columns' => $columns['columns'], 'limit' => $paginate ? $limit : 0);
}
