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