/** * Initialize the QueryBuilder object to generate reports from. * * @param ReportGraphEvent $event */ public function onReportGraphGenerate(ReportGraphEvent $event) { // Context check, we only want to fire for Lead reports if (!$event->checkContext(['leads', 'lead.pointlog', 'contact.attribution.multi'])) { return; } $graphs = $event->getRequestedGraphs(); $qb = $event->getQueryBuilder(); $pointLogRepo = $this->leadModel->getPointLogRepository(); foreach ($graphs as $g) { $queryBuilder = clone $qb; $options = $event->getOptions($g); /** @var ChartQuery $chartQuery */ $chartQuery = clone $options['chartQuery']; $attributionQb = clone $queryBuilder; $chartQuery->applyDateFilters($queryBuilder, 'date_added', 'l'); switch ($g) { case 'mautic.lead.graph.pie.attribution_stages': case 'mautic.lead.graph.pie.attribution_campaigns': case 'mautic.lead.graph.pie.attribution_actions': case 'mautic.lead.graph.pie.attribution_channels': $attributionQb->resetQueryParts(['select', 'orderBy']); $outerQb = clone $attributionQb; $outerQb->resetQueryParts()->select('slice, sum(contact_attribution) as total_attribution')->groupBy('slice'); $groupBy = str_replace('mautic.lead.graph.pie.attribution_', '', $g); switch ($groupBy) { case 'stages': $attributionQb->select('CONCAT_WS(\':\', s.id, s.name) as slice, l.attribution as contact_attribution')->groupBy('l.id, s.id'); break; case 'campaigns': $attributionQb->select('CONCAT_WS(\':\', c.id, c.name) as slice, l.attribution as contact_attribution')->groupBy('l.id, c.id'); break; case 'actions': $attributionQb->select('SUBSTRING_INDEX(e.type, \'.\', -1) as slice, l.attribution as contact_attribution')->groupBy('l.id, SUBSTRING_INDEX(e.type, \'.\', -1)'); break; case 'channels': $attributionQb->select('SUBSTRING_INDEX(e.type, \'.\', 1) as slice, l.attribution as contact_attribution')->groupBy('l.id, SUBSTRING_INDEX(e.type, \'.\', 1)'); break; } $outerQb->from(sprintf('(%s) subq', $attributionQb->getSQL())); $outerQb->setParameters($attributionQb->getParameters()); $chart = new PieChart(); $data = $outerQb->execute()->fetchAll(); foreach ($data as $row) { switch ($groupBy) { case 'actions': $label = $this->channelActions[$row['slice']]; break; case 'channels': $label = $this->channels[$row['slice']]; break; default: $label = empty($row['slice']) ? $this->translator->trans('mautic.core.none') : $row['slice']; } $chart->setDataset($label, $row['total_attribution']); } $event->setGraph($g, ['data' => $chart->render(), 'name' => $g, 'iconClass' => 'fa-dollar']); break; case 'mautic.lead.graph.line.leads': $chart = new LineChart(null, $options['dateFrom'], $options['dateTo']); $chartQuery->modifyTimeDataQuery($queryBuilder, 'date_added', 'l'); $leads = $chartQuery->loadAndBuildTimeData($queryBuilder); $chart->setDataset($options['translator']->trans('mautic.lead.all.leads'), $leads); $queryBuilder->andwhere($qb->expr()->isNotNull('l.date_identified')); $identified = $chartQuery->loadAndBuildTimeData($queryBuilder); $chart->setDataset($options['translator']->trans('mautic.lead.identified'), $identified); $data = $chart->render(); $data['name'] = $g; $event->setGraph($g, $data); break; case 'mautic.lead.graph.line.points': $chart = new LineChart(null, $options['dateFrom'], $options['dateTo']); $chartQuery->modifyTimeDataQuery($queryBuilder, 'date_added', 'lp'); $leads = $chartQuery->loadAndBuildTimeData($queryBuilder); $chart->setDataset($options['translator']->trans('mautic.lead.graph.line.points'), $leads); $data = $chart->render(); $data['name'] = $g; $event->setGraph($g, $data); break; case 'mautic.lead.table.most.points': $queryBuilder->select('l.id, l.email as title, sum(lp.delta) as points')->groupBy('l.id, l.email')->orderBy('points', 'DESC'); $limit = 10; $offset = 0; $items = $pointLogRepo->getMostPoints($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-asterisk'; $graphData['link'] = 'mautic_contact_action'; $event->setGraph($g, $graphData); break; case 'mautic.lead.table.top.countries': $queryBuilder->select('l.country as title, count(l.country) as quantity')->groupBy('l.country')->orderBy('quantity', 'DESC'); $limit = 10; $offset = 0; $items = $pointLogRepo->getMostLeads($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-globe'; $event->setGraph($g, $graphData); break; case 'mautic.lead.table.top.cities': $queryBuilder->select('l.city as title, count(l.city) as quantity')->groupBy('l.city')->orderBy('quantity', 'DESC'); $limit = 10; $offset = 0; $items = $pointLogRepo->getMostLeads($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-university'; $event->setGraph($g, $graphData); break; case 'mautic.lead.table.top.events': $queryBuilder->select('lp.event_name as title, count(lp.event_name) as events')->groupBy('lp.event_name')->orderBy('events', 'DESC'); $limit = 10; $offset = 0; $items = $pointLogRepo->getMostPoints($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-calendar'; $event->setGraph($g, $graphData); break; case 'mautic.lead.table.top.actions': $queryBuilder->select('lp.action_name as title, count(lp.action_name) as actions')->groupBy('lp.action_name')->orderBy('actions', 'DESC'); $limit = 10; $offset = 0; $items = $pointLogRepo->getMostPoints($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-bolt'; $event->setGraph($g, $graphData); break; } unset($queryBuilder); } }