/** * 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('asset.downloads')) { return; } $graphs = $event->getRequestedGraphs(); $qb = $event->getQueryBuilder(); $downloadRepo = $this->em->getRepository('MauticAssetBundle:Download'); foreach ($graphs as $g) { $options = $event->getOptions($g); $queryBuilder = clone $qb; $chartQuery = clone $options['chartQuery']; $chartQuery->applyDateFilters($queryBuilder, 'date_download', 'ad'); switch ($g) { case 'mautic.asset.graph.line.downloads': $chart = new LineChart(null, $options['dateFrom'], $options['dateTo']); $chartQuery->modifyTimeDataQuery($queryBuilder, 'date_download', 'ad'); $downloads = $chartQuery->loadAndBuildTimeData($queryBuilder); $chart->setDataset($options['translator']->trans($g), $downloads); $data = $chart->render(); $data['name'] = $g; $event->setGraph($g, $data); break; case 'mautic.asset.table.most.downloaded': $limit = 10; $offset = 0; $items = $downloadRepo->getMostDownloaded($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-download'; $graphData['link'] = 'mautic_asset_action'; $event->setGraph($g, $graphData); break; case 'mautic.asset.table.top.referrers': $limit = 10; $offset = 0; $items = $downloadRepo->getTopReferrers($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-download'; $graphData['link'] = 'mautic_asset_action'; $event->setGraph($g, $graphData); break; case 'mautic.asset.graph.pie.statuses': $items = $downloadRepo->getHttpStatuses($queryBuilder); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-globe'; $event->setGraph($g, $graphData); break; } unset($queryBuilder); } }
/** * 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('form.submissions')) { return; } $graphs = $event->getRequestedGraphs(); $qb = $event->getQueryBuilder(); $submissionRepo = $this->em->getRepository('MauticFormBundle:Submission'); foreach ($graphs as $g) { $options = $event->getOptions($g); $queryBuilder = clone $qb; $chartQuery = clone $options['chartQuery']; $chartQuery->applyDateFilters($queryBuilder, 'date_submitted', 'fs'); switch ($g) { case 'mautic.form.graph.line.submissions': $chart = new LineChart(null, $options['dateFrom'], $options['dateTo']); $chartQuery->modifyTimeDataQuery($queryBuilder, 'date_submitted', 'fs'); $hits = $chartQuery->loadAndBuildTimeData($queryBuilder); $chart->setDataset($options['translator']->trans($g), $hits); $data = $chart->render(); $data['name'] = $g; $event->setGraph($g, $data); break; break; case 'mautic.form.table.top.referrers': $limit = 10; $offset = 0; $items = $submissionRepo->getTopReferrers($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-sign-in'; $graphData['link'] = 'mautic_form_action'; $event->setGraph($g, $graphData); break; case 'mautic.form.table.most.submitted': $limit = 10; $offset = 0; $items = $submissionRepo->getMostSubmitted($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-check-square-o'; $graphData['link'] = 'mautic_form_action'; $event->setGraph($g, $graphData); break; } unset($queryBuilder); } }
/** * 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); } }
/** * Initialize the QueryBuilder object to generate reports from * * @param ReportGeneratorEvent $event * * @return void */ public function onReportGraphGenerate(ReportGraphEvent $event) { // Context check, we only want to fire for Lead reports if (!$event->checkContext('email.stats')) { return; } $graphs = $event->getRequestedGraphs(); $qb = $event->getQueryBuilder(); $statRepo = $this->factory->getEntityManager()->getRepository('MauticEmailBundle:Stat'); foreach ($graphs as $g) { $options = $event->getOptions($g); $queryBuilder = clone $qb; switch ($g) { case 'mautic.email.graph.line.stats': // Generate data for Stats line graph $unit = 'D'; $amount = 30; if (isset($options['amount'])) { $amount = $options['amount']; } if (isset($options['unit'])) { $unit = $options['unit']; } $timeStats = GraphHelper::prepareDatetimeLineGraphData($amount, $unit, array('sent', 'read', 'failed')); $queryBuilder->select('es.email_id as email, es.date_sent as "dateSent", es.date_read as "dateRead", es.is_failed'); $queryBuilder->andwhere($queryBuilder->expr()->gte('es.date_sent', ':date'))->setParameter('date', $timeStats['fromDate']->format('Y-m-d H:i:s')); $stats = $queryBuilder->execute()->fetchAll(); $timeStats = GraphHelper::mergeLineGraphData($timeStats, $stats, $unit, 0, 'dateSent'); $timeStats = GraphHelper::mergeLineGraphData($timeStats, $stats, $unit, 1, 'dateRead'); $timeStats = GraphHelper::mergeLineGraphData($timeStats, $stats, $unit, 2, 'dateSent', 'is_failed'); $timeStats['name'] = 'mautic.email.graph.line.stats'; $event->setGraph($g, $timeStats); break; case 'mautic.email.graph.pie.ignored.read.failed': $items = $statRepo->getIgnoredReadFailed($queryBuilder); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.email.graph.pie.ignored.read.failed'; $graphData['iconClass'] = 'fa-flag-checkered'; $event->setGraph($g, $graphData); break; case 'mautic.email.table.most.emails.sent': $queryBuilder->select('e.id, e.subject as title, count(es.id) as sent')->groupBy('e.id, e.subject')->orderBy('sent', 'DESC'); $limit = 10; $offset = 0; $items = $statRepo->getMostEmails($queryBuilder, $limit, $offset); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.email.table.most.emails.sent'; $graphData['iconClass'] = 'fa-paper-plane-o'; $graphData['link'] = 'mautic_email_action'; $event->setGraph($g, $graphData); break; case 'mautic.email.table.most.emails.read': $queryBuilder->select('e.id, e.subject as title, count(CASE WHEN es.is_read THEN 1 ELSE null END) as "read"')->groupBy('e.id, e.subject')->orderBy('"read"', 'DESC'); $limit = 10; $offset = 0; $items = $statRepo->getMostEmails($queryBuilder, $limit, $offset); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.email.table.most.emails.read'; $graphData['iconClass'] = 'fa-eye'; $graphData['link'] = 'mautic_email_action'; $event->setGraph($g, $graphData); break; case 'mautic.email.table.most.emails.failed': $queryBuilder->select('e.id, e.subject as title, count(CASE WHEN es.is_failed THEN 1 ELSE null END) as failed')->having('count(CASE WHEN es.is_failed THEN 1 ELSE null END) > 0')->groupBy('e.id, e.subject')->orderBy('failed', 'DESC'); $limit = 10; $offset = 0; $items = $statRepo->getMostEmails($queryBuilder, $limit, $offset); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.email.table.most.emails.failed'; $graphData['iconClass'] = 'fa-exclamation-triangle'; $graphData['link'] = 'mautic_email_action'; $event->setGraph($g, $graphData); break; case 'mautic.email.table.most.emails.read.percent': $queryBuilder->select('e.id, e.subject as title, round(e.read_count / e.sent_count * 100) as ratio')->groupBy('e.id, e.subject')->orderBy('ratio', 'DESC'); $limit = 10; $offset = 0; $items = $statRepo->getMostEmails($queryBuilder, $limit, $offset); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.email.table.most.emails.read.percent'; $graphData['iconClass'] = 'fa-tachometer'; $graphData['link'] = 'mautic_email_action'; $event->setGraph($g, $graphData); break; } unset($queryBuilder); } }
/** * Initialize the QueryBuilder object to generate reports from * * @param ReportGraphEvent $event * * @return void */ public function onReportGraphGenerate(ReportGraphEvent $event) { // Context check, we only want to fire for Lead reports if (!$event->checkContext(array('leads', 'lead.pointlog'))) { return; } $graphs = $event->getRequestedGraphs(); $qb = $event->getQueryBuilder(); $pointLogRepo = $this->factory->getEntityManager()->getRepository('MauticLeadBundle:PointsChangeLog'); foreach ($graphs as $g) { $options = $event->getOptions($g); $queryBuilder = clone $qb; switch ($g) { case 'mautic.lead.graph.line.leads': // Generate data for leads line graph $unit = 'D'; $amount = 30; if (isset($options['amount'])) { $amount = $options['amount']; } if (isset($options['unit'])) { $unit = $options['unit']; } $timeStats = GraphHelper::prepareDatetimeLineGraphData($amount, $unit, array('leads', 'emails')); $queryBuilder->select('l.id as lead, l.date_added as "dateAdded", LENGTH(l.email) > 0 as email'); $queryBuilder->andwhere($queryBuilder->expr()->gte('l.date_added', ':date'))->setParameter('date', $timeStats['fromDate']->format('Y-m-d H:i:s')); $leads = $queryBuilder->execute()->fetchAll(); $timeStats = GraphHelper::mergeLineGraphData($timeStats, $leads, $unit, 0, 'dateAdded'); $timeStats = GraphHelper::mergeLineGraphData($timeStats, $leads, $unit, 1, 'dateAdded', 'email'); $timeStats['name'] = 'mautic.lead.graph.line.leads'; $event->setGraph($g, $timeStats); break; case 'mautic.lead.graph.line.points': // Generate data for points line graph $unit = 'D'; $amount = 30; if (isset($options['amount'])) { $amount = $options['amount']; } if (isset($options['unit'])) { $unit = $options['unit']; } $timeStats = GraphHelper::prepareDatetimeLineGraphData($amount, $unit, array('points')); $queryBuilder->select('lp.lead_id as lead, lp.date_added as dateAdded, lp.delta'); $queryBuilder->andwhere($queryBuilder->expr()->gte('lp.date_added', ':date'))->setParameter('date', $timeStats['fromDate']->format('Y-m-d H:i:s')); $points = $queryBuilder->execute()->fetchAll(); $timeStats = GraphHelper::mergeLineGraphData($timeStats, $points, $unit, 0, 'dateAdded', 'delta'); $timeStats['name'] = 'mautic.lead.graph.line.points'; $event->setGraph($g, $timeStats); 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 = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.lead.table.most.points'; $graphData['iconClass'] = 'fa-asterisk'; $graphData['link'] = 'mautic_lead_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 = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.lead.table.top.countries'; $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 = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.lead.table.top.cities'; $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 = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.lead.table.top.events'; $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 = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.lead.table.top.actions'; $graphData['iconClass'] = 'fa-bolt'; $event->setGraph($g, $graphData); break; } unset($queryBuilder); } }
/** * Initialize the QueryBuilder object to generate reports from * * @param ReportGeneratorEvent $event * * @return void */ public function onReportGraphGenerate(ReportGraphEvent $event) { // Context check, we only want to fire for Lead reports if (!$event->checkContext('form.submissions')) { return; } $graphs = $event->getRequestedGraphs(); $qb = $event->getQueryBuilder(); $submissionRepo = $this->factory->getEntityManager()->getRepository('MauticFormBundle:Submission'); foreach ($graphs as $g) { $options = $event->getOptions($g); $queryBuilder = clone $qb; switch ($g) { case 'mautic.form.graph.line.submissions': // Generate data for submissions line graph $unit = 'D'; $amount = 30; if (isset($options['amount'])) { $amount = $options['amount']; } if (isset($options['unit'])) { $unit = $options['unit']; } $data = GraphHelper::prepareDatetimeLineGraphData($amount, $unit, array('submissions')); $queryBuilder->select('fs.form_id as form, fs.date_submitted as "dateSubmitted"'); $queryBuilder->andwhere($queryBuilder->expr()->gte('fs.date_submitted', ':date'))->setParameter('date', $data['fromDate']->format('Y-m-d H:i:s')); $submissions = $queryBuilder->execute()->fetchAll(); $timeStats = GraphHelper::mergeLineGraphData($data, $submissions, $unit, 0, 'dateSubmitted'); $timeStats['name'] = 'mautic.form.graph.line.submissions'; $event->setGraph($g, $timeStats); break; case 'mautic.form.table.top.referrers': $limit = 10; $offset = 0; $items = $submissionRepo->getTopReferrers($queryBuilder, $limit, $offset); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.form.table.top.referrers'; $graphData['iconClass'] = 'fa-sign-in'; $graphData['link'] = 'mautic_form_action'; $event->setGraph($g, $graphData); break; case 'mautic.form.table.most.submitted': $limit = 10; $offset = 0; $items = $submissionRepo->getMostSubmitted($queryBuilder, $limit, $offset); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.form.table.most.submitted'; $graphData['iconClass'] = 'fa-check-square-o'; $graphData['link'] = 'mautic_form_action'; $event->setGraph($g, $graphData); break; } unset($queryBuilder); } }
/** * 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('email.stats')) { return; } $graphs = $event->getRequestedGraphs(); $qb = $event->getQueryBuilder(); $statRepo = $this->em->getRepository('MauticEmailBundle:Stat'); foreach ($graphs as $g) { $options = $event->getOptions($g); $queryBuilder = clone $qb; $chartQuery = clone $options['chartQuery']; $origQuery = clone $queryBuilder; $chartQuery->applyDateFilters($queryBuilder, 'date_sent', 'es'); switch ($g) { case 'mautic.email.graph.line.stats': $chart = new LineChart(null, $options['dateFrom'], $options['dateTo']); $sendQuery = clone $queryBuilder; $readQuery = clone $origQuery; $failedQuery = clone $queryBuilder; $failedQuery->andWhere($qb->expr()->eq('es.is_failed', ':true')); $failedQuery->setParameter('true', true, 'boolean'); $chartQuery->applyDateFilters($readQuery, 'date_read', 'es'); $chartQuery->modifyTimeDataQuery($sendQuery, 'date_sent', 'es'); $chartQuery->modifyTimeDataQuery($readQuery, 'date_read', 'es'); $chartQuery->modifyTimeDataQuery($failedQuery, 'date_sent', 'es'); $sends = $chartQuery->loadAndBuildTimeData($sendQuery); $reads = $chartQuery->loadAndBuildTimeData($readQuery); $failes = $chartQuery->loadAndBuildTimeData($failedQuery); $chart->setDataset($options['translator']->trans('mautic.email.sent.emails'), $sends); $chart->setDataset($options['translator']->trans('mautic.email.read.emails'), $reads); $chart->setDataset($options['translator']->trans('mautic.email.failed.emails'), $failes); $data = $chart->render(); $data['name'] = $g; $event->setGraph($g, $data); break; case 'mautic.email.graph.pie.ignored.read.failed': $counts = $statRepo->getIgnoredReadFailed($queryBuilder); $chart = new PieChart(); $chart->setDataset($options['translator']->trans('mautic.email.read.emails'), $counts['read']); $chart->setDataset($options['translator']->trans('mautic.email.failed.emails'), $counts['failed']); $chart->setDataset($options['translator']->trans('mautic.email.ignored.emails'), $counts['ignored']); $event->setGraph($g, ['data' => $chart->render(), 'name' => $g, 'iconClass' => 'fa-flag-checkered']); break; case 'mautic.email.table.most.emails.sent': $queryBuilder->select('e.id, e.subject as title, count(es.id) as sent')->groupBy('e.id, e.subject')->orderBy('sent', 'DESC'); $limit = 10; $offset = 0; $items = $statRepo->getMostEmails($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-paper-plane-o'; $graphData['link'] = 'mautic_email_action'; $event->setGraph($g, $graphData); break; case 'mautic.email.table.most.emails.read': $queryBuilder->select('e.id, e.subject as title, count(CASE WHEN es.is_read THEN 1 ELSE null END) as "read"')->groupBy('e.id, e.subject')->orderBy('"read"', 'DESC'); $limit = 10; $offset = 0; $items = $statRepo->getMostEmails($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-eye'; $graphData['link'] = 'mautic_email_action'; $event->setGraph($g, $graphData); break; case 'mautic.email.table.most.emails.failed': $queryBuilder->select('e.id, e.subject as title, count(CASE WHEN es.is_failed THEN 1 ELSE null END) as failed')->having('count(CASE WHEN es.is_failed THEN 1 ELSE null END) > 0')->groupBy('e.id, e.subject')->orderBy('failed', 'DESC'); $limit = 10; $offset = 0; $items = $statRepo->getMostEmails($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-exclamation-triangle'; $graphData['link'] = 'mautic_email_action'; $event->setGraph($g, $graphData); break; case 'mautic.email.table.most.emails.read.percent': $queryBuilder->select('e.id, e.subject as title, round(e.read_count / e.sent_count * 100) as ratio')->groupBy('e.id, e.subject')->orderBy('ratio', 'DESC'); $limit = 10; $offset = 0; $items = $statRepo->getMostEmails($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-tachometer'; $graphData['link'] = 'mautic_email_action'; $event->setGraph($g, $graphData); break; } unset($queryBuilder); } }
/** * Initialize the QueryBuilder object to generate reports from * * @param ReportGraphEvent $event * * @return void */ public function onReportGraphGenerate(ReportGraphEvent $event) { // Context check, we only want to fire for Lead reports if (!$event->checkContext('page.hits')) { return; } $graphs = $event->getRequestedGraphs(); $qb = $event->getQueryBuilder(); $hitRepo = $this->factory->getEntityManager()->getRepository('MauticPageBundle:Hit'); foreach ($graphs as $g) { $options = $event->getOptions($g); $queryBuilder = clone $qb; switch ($g) { case 'mautic.page.graph.line.hits': // Generate data for Downloads line graph $unit = 'D'; $amount = 30; if (isset($options['amount'])) { $amount = $options['amount']; } if (isset($options['unit'])) { $unit = $options['unit']; } $data = GraphHelper::prepareDatetimeLineGraphData($amount, $unit, array('dateHit')); $queryBuilder->select('ph.page_id as page, ph.date_hit as "dateHit"'); $queryBuilder->andwhere($queryBuilder->expr()->gte('ph.date_hit', ':date'))->setParameter('date', $data['fromDate']->format('Y-m-d H:i:s')); $hits = $queryBuilder->execute()->fetchAll(); $timeStats = GraphHelper::mergeLineGraphData($data, $hits, $unit, 0, 'dateHit'); $timeStats['name'] = 'mautic.page.graph.line.hits'; $event->setGraph($g, $timeStats); break; case 'mautic.page.graph.line.time.on.site': // Generate data for Downloads line graph $unit = 'D'; $amount = 30; if (isset($options['amount'])) { $amount = $options['amount']; } if (isset($options['unit'])) { $unit = $options['unit']; } $data = GraphHelper::prepareDatetimeLineGraphData($amount, $unit, array('dateHit')); $queryBuilder->select('ph.page_id as page, ph.date_hit as "dateHit", ph.date_left as "dateLeft"'); $queryBuilder->andwhere($queryBuilder->expr()->gte('ph.date_hit', ':date'))->setParameter('date', $data['fromDate']->format('Y-m-d H:i:s')); $hits = $queryBuilder->execute()->fetchAll(); // Count time on site foreach ($hits as $key => $hit) { if ($hit['dateLeft']) { $dateHit = new \DateTime($hit['dateHit']); $dateLeft = new \DateTime($hit['dateLeft']); $hits[$key]['timeOnSite'] = $dateLeft->getTimestamp() - $dateHit->getTimestamp(); $hits[$key]['timeOnSiteDate'] = $hit['dateHit']; } else { $hits[$key]['timeOnSite'] = 0; $hits[$key]['timeOnSiteDate'] = $hit['dateHit']; } unset($hits[$key]['dateLeft']); } $timeStats = GraphHelper::mergeLineGraphData($data, $hits, $unit, 0, 'dateHit', 'timeOnSite', true); $timeStats['name'] = 'mautic.page.graph.line.time.on.site'; $event->setGraph($g, $timeStats); break; case 'mautic.page.graph.pie.time.on.site': $hitStats = $hitRepo->getDwellTimes(array(), $queryBuilder); $graphData = array(); $graphData['data'] = $hitStats['timesOnSite']; $graphData['name'] = 'mautic.page.graph.pie.time.on.site'; $graphData['iconClass'] = 'fa-clock-o'; $event->setGraph($g, $graphData); break; case 'mautic.page.graph.pie.new.vs.returning': if (!isset($hitstats)) { $hitStats = $hitRepo->getDwellTimes(array(), $queryBuilder); } $graphData = array(); $graphData['data'] = $hitStats['newVsReturning']; $graphData['name'] = 'mautic.page.graph.pie.new.vs.returning'; $graphData['iconClass'] = 'fa-bookmark-o'; $event->setGraph($g, $graphData); break; case 'mautic.page.graph.pie.languages': if (!isset($hitstats)) { $hitStats = $hitRepo->getDwellTimes(array(), $queryBuilder); } $graphData = array(); $graphData['data'] = $hitStats['languages']; $graphData['name'] = 'mautic.page.graph.pie.languages'; $graphData['iconClass'] = 'fa-globe'; $event->setGraph($g, $graphData); break; case 'mautic.page.table.referrers': $limit = 10; $offset = 0; $items = $hitRepo->getReferers($queryBuilder, $limit, $offset); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.page.table.referrers'; $graphData['iconClass'] = 'fa-sign-in'; $event->setGraph($g, $graphData); break; case 'mautic.page.table.most.visited': $limit = 10; $offset = 0; $items = $hitRepo->getMostVisited($queryBuilder, $limit, $offset); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.page.table.most.visited'; $graphData['iconClass'] = 'fa-eye'; $graphData['link'] = 'mautic_page_action'; $event->setGraph($g, $graphData); break; case 'mautic.page.table.most.visited.unique': $limit = 10; $offset = 0; $items = $hitRepo->getMostVisited($queryBuilder, $limit, $offset, 'p.unique_hits', 'sessions'); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.page.table.most.visited.unique'; $graphData['iconClass'] = 'fa-eye'; $graphData['link'] = 'mautic_page_action'; $event->setGraph($g, $graphData); break; } unset($queryBuilder); } }
/** * Initialize the QueryBuilder object to generate reports from * * @param ReportGraphEvent $event * * @return void */ public function onReportGraphGenerate(ReportGraphEvent $event) { // Context check, we only want to fire for Lead reports if (!$event->checkContext('page.hits')) { return; } $graphs = $event->getRequestedGraphs(); $qb = $event->getQueryBuilder(); $hitRepo = $this->factory->getEntityManager()->getRepository('MauticPageBundle:Hit'); foreach ($graphs as $g) { $options = $event->getOptions($g); $queryBuilder = clone $qb; /** @var ChartQuery $chartQuery */ $chartQuery = clone $options['chartQuery']; $chartQuery->applyDateFilters($queryBuilder, 'date_hit', 'ph'); switch ($g) { case 'mautic.page.graph.line.hits': $chart = new LineChart(null, $options['dateFrom'], $options['dateTo']); $chartQuery->modifyTimeDataQuery($queryBuilder, 'date_hit', 'ph'); $hits = $chartQuery->loadAndBuildTimeData($queryBuilder); $chart->setDataset($options['translator']->trans($g), $hits); $data = $chart->render(); $data['name'] = $g; $event->setGraph($g, $data); break; case 'mautic.page.graph.line.time.on.site': $chart = new LineChart(null, $options['dateFrom'], $options['dateTo']); $queryBuilder->select('TIMESTAMPDIFF(SECOND, ph.date_hit, ph.date_left) as data, ph.date_hit as date'); $queryBuilder->andWhere($qb->expr()->isNotNull('ph.date_left')); $hits = $chartQuery->loadAndBuildTimeData($queryBuilder); $chart->setDataset($options['translator']->trans($g), $hits); $data = $chart->render(); $data['name'] = $g; $event->setGraph($g, $data); break; case 'mautic.page.graph.pie.time.on.site': $timesOnSite = $hitRepo->getDwellTimeLabels(); $chart = new PieChart(); foreach ($timesOnSite as $time) { $q = clone $queryBuilder; $chartQuery->modifyCountDateDiffQuery($q, 'date_hit', 'date_left', $time['from'], $time['till'], 'ph'); $data = $chartQuery->fetchCountDateDiff($q); $chart->setDataset($time['label'], $data); } $event->setGraph($g, ['data' => $chart->render(), 'name' => $g, 'iconClass' => 'fa-clock-o']); break; case 'mautic.page.graph.pie.new.vs.returning': $chart = new PieChart(); $allQ = clone $queryBuilder; $uniqueQ = clone $queryBuilder; $chartQuery->modifyCountQuery($allQ, 'date_hit', [], 'ph'); $chartQuery->modifyCountQuery($uniqueQ, 'date_hit', ['getUnique' => true, 'selectAlso' => ['ph.page_id']], 'ph'); $all = $chartQuery->fetchCount($allQ); $unique = $chartQuery->fetchCount($uniqueQ); $returning = $all - $unique; $chart->setDataset($this->factory->getTranslator()->trans('mautic.page.unique'), $unique); $chart->setDataset($this->factory->getTranslator()->trans('mautic.page.graph.pie.new.vs.returning.returning'), $returning); $event->setGraph($g, ['data' => $chart->render(), 'name' => $g, 'iconClass' => 'fa-bookmark-o']); break; case 'mautic.page.graph.pie.languages': $queryBuilder->select('ph.page_language, COUNT(distinct(ph.id)) as the_count')->groupBy('ph.page_language')->andWhere($qb->expr()->isNotNull('ph.page_language')); $data = $queryBuilder->execute()->fetchAll(); $chart = new PieChart(); foreach ($data as $lang) { $chart->setDataset($lang['page_language'], $lang['the_count']); } $event->setGraph($g, ['data' => $chart->render(), 'name' => $g, 'iconClass' => 'fa-globe']); break; case 'mautic.page.graph.pie.devices': $queryBuilder->select('ds.device, COUNT(distinct(ph.id)) as the_count')->groupBy('ds.device'); $data = $queryBuilder->execute()->fetchAll(); $chart = new PieChart(); foreach ($data as $device) { $label = substr(empty($device['device']) ? $this->translator->trans('mautic.core.no.info') : $device['device'], 0, 12); $chart->setDataset($label, $device['the_count']); } $event->setGraph($g, ['data' => $chart->render(), 'name' => $g, 'iconClass' => 'fa-globe']); break; case 'mautic.page.table.referrers': $limit = 10; $offset = 0; $items = $hitRepo->getReferers($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-sign-in'; $event->setGraph($g, $graphData); break; case 'mautic.page.table.most.visited': $limit = 10; $offset = 0; $items = $hitRepo->getMostVisited($queryBuilder, $limit, $offset); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-eye'; $graphData['link'] = 'mautic_page_action'; $event->setGraph($g, $graphData); break; case 'mautic.page.table.most.visited.unique': $limit = 10; $offset = 0; $items = $hitRepo->getMostVisited($queryBuilder, $limit, $offset, 'p.unique_hits', 'sessions'); $graphData = []; $graphData['data'] = $items; $graphData['name'] = $g; $graphData['iconClass'] = 'fa-eye'; $graphData['link'] = 'mautic_page_action'; $event->setGraph($g, $graphData); break; } unset($queryBuilder); } }
/** * Initialize the QueryBuilder object to generate reports from * * @param ReportGraphEvent $event * * @return void */ public function onReportGraphGenerate(ReportGraphEvent $event) { // Context check, we only want to fire for Lead reports if (!$event->checkContext('asset.downloads')) { return; } $graphs = $event->getRequestedGraphs(); $qb = $event->getQueryBuilder(); $downloadRepo = $this->factory->getEntityManager()->getRepository('MauticAssetBundle:Download'); foreach ($graphs as $g) { $options = $event->getOptions($g); $queryBuilder = clone $qb; switch ($g) { case 'mautic.asset.graph.line.downloads': // Generate data for Downloads line graph $unit = 'D'; $amount = 30; if (isset($options['amount'])) { $amount = $options['amount']; } if (isset($options['unit'])) { $unit = $options['unit']; } $data = GraphHelper::prepareDatetimeLineGraphData($amount, $unit, array('downloaded')); $queryBuilder->select('ad.asset_id as asset, ad.date_download as "dateDownload"'); $queryBuilder->andwhere($queryBuilder->expr()->gte('ad.date_download', ':date'))->setParameter('date', $data['fromDate']->format('Y-m-d H:i:s')); $downloads = $queryBuilder->execute()->fetchAll(); $timeStats = GraphHelper::mergeLineGraphData($data, $downloads, $unit, 0, 'dateDownload'); $timeStats['name'] = 'mautic.asset.graph.line.downloads'; $event->setGraph($g, $timeStats); break; case 'mautic.asset.table.most.downloaded': $limit = 10; $offset = 0; $items = $downloadRepo->getMostDownloaded($queryBuilder, $limit, $offset); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.asset.table.most.downloaded'; $graphData['iconClass'] = 'fa-download'; $graphData['link'] = 'mautic_asset_action'; $event->setGraph($g, $graphData); break; case 'mautic.asset.table.top.referrers': $limit = 10; $offset = 0; $items = $downloadRepo->getTopReferrers($queryBuilder, $limit, $offset); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.asset.table.top.referrers'; $graphData['iconClass'] = 'fa-download'; $graphData['link'] = 'mautic_asset_action'; $event->setGraph($g, $graphData); break; case 'mautic.asset.graph.pie.statuses': $items = $downloadRepo->getHttpStatuses($queryBuilder); $graphData = array(); $graphData['data'] = $items; $graphData['name'] = 'mautic.asset.graph.pie.statuses'; $graphData['iconClass'] = 'fa-globe'; $event->setGraph($g, $graphData); break; } unset($queryBuilder); } }