/** * 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); }