/**
* @testdox The getUnitInterval returns the correct interval objects
*
* @covers \Mautic\CoreBundle\Helper\Chart\LineChart::getUnitInterval
*/
public function testGetUnitInterval()
{
$units = ['Y' => new \DateInterval('P1Y'), 'm' => new \DateInterval('P1M'), 'W' => new \DateInterval('P1W'), 'd' => new \DateInterval('P1D'), 'H' => new \DateInterval('PT1H'), 'i' => new \DateInterval('PT1M'), 's' => new \DateInterval('PT1S')];
foreach ($units as $unit => $expected) {
$chart = new LineChart($unit, new \DateTime(), new \DateTime());
$interval = $chart->getUnitInterval();
$this->assertEquals($expected, $interval);
}
}
/**
* @param Lead $lead
* @param \DateTime|null $fromDate
* @param \DateTime|null $toDate
*
* @return mixed
*/
protected function getEngagementData(Lead $lead, \DateTime $fromDate = null, \DateTime $toDate = null)
{
$translator = $this->get('translator');
if (null == $fromDate) {
$fromDate = new \DateTime('first day of this month 00:00:00');
$fromDate->modify('-6 months');
}
if (null == $toDate) {
$toDate = new \DateTime();
}
$lineChart = new LineChart(null, $fromDate, $toDate);
$chartQuery = new ChartQuery($this->getDoctrine()->getConnection(), $fromDate, $toDate);
/** @var LeadModel $model */
$model = $this->getModel('lead');
$engagements = $model->getEngagementCount($lead, $fromDate, $toDate, 'm', $chartQuery);
$lineChart->setDataset($translator->trans('mautic.lead.graph.line.all_engagements'), $engagements['byUnit']);
$pointStats = $chartQuery->fetchTimeData('lead_points_change_log', 'date_added', ['lead_id' => $lead->getId()]);
$lineChart->setDataset($translator->trans('mautic.lead.graph.line.points'), $pointStats);
return $lineChart->render();
}
/**
* Get line chart data of emails sent and read
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param \DateTime $dateFrom
* @param \DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param boolean $canViewOthers
*
* @return array
*/
public function getEmailsLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = [], $canViewOthers = true)
{
$flag = null;
if (isset($filter['flag'])) {
$flag = $filter['flag'];
unset($filter['flag']);
}
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
if ($flag == 'sent_and_opened_and_failed' || $flag == 'all' || $flag == 'sent_and_opened' || !$flag) {
$q = $query->prepareTimeDataQuery('email_stats', 'date_sent', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.email.sent.emails'), $data);
}
if ($flag == 'sent_and_opened_and_failed' || $flag == 'all' || $flag == 'sent_and_opened' || $flag == 'opened') {
$q = $query->prepareTimeDataQuery('email_stats', 'date_read', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.email.read.emails'), $data);
}
if ($flag == 'sent_and_opened_and_failed' || $flag == 'all' || $flag == 'failed') {
$q = $query->prepareTimeDataQuery('email_stats', 'date_sent', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$q->andWhere($q->expr()->eq('t.is_failed', ':true'))->setParameter('true', true, 'boolean');
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.email.failed.emails'), $data);
}
if ($flag == 'all' || $flag == 'clicked') {
$q = $query->prepareTimeDataQuery('page_hits', 'date_hit', [])->join('t', MAUTIC_TABLE_PREFIX . 'channel_url_trackables', 'cut', 't.redirect_id = cut.redirect_id')->andWhere('cut.channel = :channel')->setParameter('channel', 'email');
if (isset($filter['email_id'])) {
if (is_array($filter['email_id'])) {
$q->andWhere('cut.channel_id IN(:channel_id)');
$q->setParameter('channel_id', implode(',', $filter['email_id']));
} else {
$q->andWhere('cut.channel_id = :channel_id');
$q->setParameter('channel_id', $filter['email_id']);
}
}
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.email.clicked'), $data);
}
if ($flag == 'all' || $flag == 'unsubscribed') {
$data = $this->getDncLineChartDataset($query, $filter, DoNotContact::UNSUBSCRIBED, $canViewOthers);
$chart->setDataset($this->translator->trans('mautic.email.unsubscribed'), $data);
}
if ($flag == 'all' || $flag == 'bounced') {
$data = $this->getDncLineChartDataset($query, $filter, DoNotContact::BOUNCED, $canViewOthers);
$chart->setDataset($this->translator->trans('mautic.email.bounced'), $data);
}
return $chart->render();
}
/**
* Get line chart data of campaign events
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param boolean $canViewOthers
*
* @return array
*/
public function getEventLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = array(), $canViewOthers = true)
{
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = $chart->getChartQuery($this->em->getConnection());
$q = $query->prepareTimeDataQuery('campaign_lead_event_log', 'date_triggered', $filter);
if (!$canViewOthers) {
$q->join('t', MAUTIC_TABLE_PREFIX . 'campaigns', 'c', 'c.id = c.campaign_id')->andWhere('c.created_by = :userId')->setParameter('userId', $this->factory->getUser()->getId());
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->factory->getTranslator()->trans('mautic.campaign.triggered.events'), $data);
return $chart->render();
}
/**
* Get bar chart data of hits
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param boolean $canViewOthers
*
* @return array
*/
public function getLeadsLineChartData($unit, $dateFrom, $dateTo, $dateFormat = null, $filter = array(), $canViewOthers = true)
{
$flag = null;
$topLists = null;
$allLeadsT = $this->factory->getTranslator()->trans('mautic.lead.all.leads');
$identifiedT = $this->factory->getTranslator()->trans('mautic.lead.identified');
$anonymousT = $this->factory->getTranslator()->trans('mautic.lead.lead.anonymous');
if (isset($filter['flag'])) {
$flag = $filter['flag'];
unset($filter['flag']);
}
if (!$canViewOthers) {
$filter['owner_id'] = $this->factory->getUser()->getId();
}
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = $chart->getChartQuery($this->em->getConnection());
$anonymousFilter = $filter;
$anonymousFilter['date_identified'] = array('expression' => 'isNull');
$identifiedFilter = $filter;
$identifiedFilter['date_identified'] = array('expression' => 'isNotNull');
if ($flag == 'top') {
$topLists = $this->factory->getModel('lead.list')->getTopLists(6, $dateFrom, $dateTo);
if ($topLists) {
foreach ($topLists as $list) {
$filter['leadlist_id'] = array('value' => $list['id'], 'list_column_name' => 't.id');
$all = $query->fetchTimeData('leads', 'date_added', $filter);
$chart->setDataset($list['name'] . ': ' . $allLeadsT, $all);
}
}
} elseif ($flag == 'topIdentifiedVsAnonymous') {
$topLists = $this->factory->getModel('lead.list')->getTopLists(3, $dateFrom, $dateTo);
if ($topLists) {
foreach ($topLists as $list) {
$anonymousFilter['leadlist_id'] = array('value' => $list['id'], 'list_column_name' => 't.id');
$identifiedFilter['leadlist_id'] = array('value' => $list['id'], 'list_column_name' => 't.id');
$identified = $query->fetchTimeData('leads', 'date_added', $identifiedFilter);
$anonymous = $query->fetchTimeData('leads', 'date_added', $anonymousFilter);
$chart->setDataset($list['name'] . ': ' . $identifiedT, $identified);
$chart->setDataset($list['name'] . ': ' . $anonymousT, $anonymous);
}
}
} elseif ($flag == 'identified') {
$identified = $query->fetchTimeData('leads', 'date_added', $identifiedFilter);
$chart->setDataset($identifiedT, $identified);
} elseif ($flag == 'anonymous') {
$anonymous = $query->fetchTimeData('leads', 'date_added', $anonymousFilter);
$chart->setDataset($anonymousT, $anonymous);
} elseif ($flag == 'identifiedVsAnonymous') {
$identified = $query->fetchTimeData('leads', 'date_added', $identifiedFilter);
$anonymous = $query->fetchTimeData('leads', 'date_added', $anonymousFilter);
$chart->setDataset($identifiedT, $identified);
$chart->setDataset($anonymousT, $anonymous);
} else {
$all = $query->fetchTimeData('leads', 'date_added', $filter);
$chart->setDataset($allLeadsT, $all);
}
return $chart->render();
}
/**
* Get line chart data of emails sent and read
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param boolean $canViewOthers
*
* @return array
*/
public function getEmailsLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = array(), $canViewOthers = true)
{
$flag = null;
if (isset($filter['flag'])) {
$flag = $filter['flag'];
unset($filter['flag']);
}
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = $chart->getChartQuery($this->em->getConnection());
if ($flag == 'sent_and_opened_and_failed' || $flag == 'sent_and_opened' || !$flag) {
$q = $query->prepareTimeDataQuery('email_stats', 'date_sent', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->factory->getTranslator()->trans('mautic.email.sent.emails'), $data);
}
if ($flag == 'sent_and_opened_and_failed' || $flag == 'sent_and_opened' || $flag == 'opened') {
$q = $query->prepareTimeDataQuery('email_stats', 'date_read', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->factory->getTranslator()->trans('mautic.email.read.emails'), $data);
}
if ($flag == 'sent_and_opened_and_failed' || $flag == 'failed') {
$q = $query->prepareTimeDataQuery('email_stats', 'date_sent', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$q->andWhere($q->expr()->eq('t.is_failed', ':true'))->setParameter('true', true, 'boolean');
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->factory->getTranslator()->trans('mautic.email.failed.emails'), $data);
}
return $chart->render();
}
/**
* Get line chart data of stages.
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param bool $canViewOthers
*
* @return array
*/
public function getStageLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = [], $canViewOthers = true)
{
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
$q = $query->prepareTimeDataQuery('lead_stages_change_log', 'date_added', $filter);
if (!$canViewOthers) {
$q->join('t', MAUTIC_TABLE_PREFIX . 'leads', 'l', 'l.id = t.lead_id')->andWhere('l.owner_id = :userId')->setParameter('userId', $this->factory->getUser()->getId());
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->factory->getTranslator()->trans('mautic.stage.changes'), $data);
return $chart->render();
}
/**
* 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 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;
$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
*
* @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);
}
}
/**
* Loads a specific form into the detailed panel
*
* @param int $objectId
*
* @return JsonResponse|\Symfony\Component\HttpFoundation\Response
*/
public function viewAction($objectId)
{
if (!$this->get('mautic.security')->isGranted('plugin:mauticSocial:monitoring:view')) {
return $this->accessDenied();
}
$session = $this->get('session');
/** @var \MauticPlugin\MauticSocialBundle\Model\MonitoringModel $model */
$model = $this->getModel('social.monitoring');
/** @var \MauticPlugin\MauticSocialBundle\Entity\PostCountRepository $postCountRepo */
$postCountRepo = $this->getModel('social.postcount')->getRepository();
$security = $this->container->get('mautic.security');
$monitoringEntity = $model->getEntity($objectId);
//set the asset we came from
$page = $session->get('mautic.social.monitoring.page', 1);
$tmpl = $this->request->isXmlHttpRequest() ? $this->request->get('tmpl', 'details') : 'details';
if ($monitoringEntity === null) {
//set the return URL
$returnUrl = $this->generateUrl('mautic_social_index', ['page' => $page]);
return $this->postActionRedirect(['returnUrl' => $returnUrl, 'viewParameters' => ['page' => $page], 'contentTemplate' => 'MauticSocialMonitoringBundle:Monitoring:index', 'passthroughVars' => ['activeLink' => '#mautic_social_index', 'mauticContent' => 'monitoring'], 'flashes' => [['type' => 'error', 'msg' => 'mautic.social.monitoring.error.notfound', 'msgVars' => ['%id%' => $objectId]]]]);
}
// Audit Log
$logs = $this->getModel('core.auditLog')->getLogForObject('monitoring', $objectId);
$returnUrl = $this->generateUrl('mautic_social_action', ['objectAction' => 'view', 'objectId' => $monitoringEntity->getId()]);
// Init the date range filter form
$dateRangeValues = $this->request->get('daterange', []);
$dateRangeForm = $this->get('form.factory')->create('daterange', $dateRangeValues, ['action' => $returnUrl]);
$dateFrom = new \DateTime($dateRangeForm['date_from']->getData());
$dateTo = new \DateTime($dateRangeForm['date_to']->getData());
$chart = new LineChart(null, $dateFrom, $dateTo);
$leadStats = $postCountRepo->getLeadStatsPost($dateFrom, $dateTo, ['monitor_id' => $monitoringEntity->getId()]);
$chart->setDataset($this->get('translator')->trans('mautic.social.twitter.tweet.count'), $leadStats);
return $this->delegateView(['returnUrl' => $returnUrl, 'viewParameters' => ['activeMonitoring' => $monitoringEntity, 'logs' => $logs, 'tmpl' => $tmpl, 'security' => $security, 'leadStats' => $chart->render(), 'monitorLeads' => $this->forward('MauticSocialBundle:Monitoring:contacts', ['objectId' => $monitoringEntity->getId(), 'page' => $page, 'ignoreAjax' => true])->getContent(), 'dateRangeForm' => $dateRangeForm->createView()], 'contentTemplate' => 'MauticSocialBundle:Monitoring:' . $tmpl . '.html.php', 'passthroughVars' => ['activeLink' => '#mautic_social_index', 'mauticContent' => 'monitoring']]);
}
/**
* Get line chart data of hits.
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param \DateTime $dateFrom
* @param \DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param bool $canViewOthers
*
* @return array
*/
public function getHitsLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = [], $canViewOthers = true)
{
$flag = null;
if (isset($filter['flag'])) {
$flag = $filter['flag'];
unset($filter['flag']);
}
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
if (!$flag || $flag === 'total_and_unique') {
$q = $query->prepareTimeDataQuery('push_notification_stats', 'date_sent', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.notification.show.total.sent'), $data);
}
return $chart->render();
}
/**
* Get line chart data of hits
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @param string $dateFormat
* @param array $filter
*
* @return array
*/
public function getCampaignMetricsLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = array())
{
$events = array();
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
$contacts = $query->fetchTimeData('campaign_leads', 'date_added', $filter);
$chart->setDataset($this->translator->trans('mautic.campaign.campaign.leads'), $contacts);
if (isset($filter['campaign_id'])) {
$rawEvents = $this->getEventRepository()->getCampaignEvents($filter['campaign_id']);
// Group events by type
if ($rawEvents) {
foreach ($rawEvents as $event) {
if (isset($events[$event['type']])) {
$events[$event['type']][] = $event['id'];
} else {
$events[$event['type']] = array($event['id']);
}
}
}
if ($events) {
foreach ($events as $type => $eventIds) {
$filter['event_id'] = $eventIds;
$q = $query->prepareTimeDataQuery('campaign_lead_event_log', 'date_triggered', $filter);
$rawData = $q->execute()->fetchAll();
if (!empty($rawData)) {
$triggers = $query->completeTimeData($rawData);
$chart->setDataset($this->translator->trans('mautic.campaign.' . $type), $triggers);
}
}
unset($filter['event_id']);
}
}
return $chart->render();
}
/**
* Get line chart data of hits
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param boolean $canViewOthers
*
* @return array
*/
public function getHitsLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = array(), $canViewOthers = true)
{
$flag = null;
if (isset($filter['flag'])) {
$flag = $filter['flag'];
unset($filter['flag']);
}
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = $chart->getChartQuery($this->factory->getEntityManager()->getConnection());
if (!$flag || $flag == 'total_and_unique') {
$q = $query->prepareTimeDataQuery('page_hits', 'date_hit', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->factory->getTranslator()->trans('mautic.page.show.total.visits'), $data);
}
if ($flag == 'unique' || $flag == 'total_and_unique') {
$filter['groupBy'] = 'lead_id';
$q = $query->prepareTimeDataQuery('page_hits', 'date_hit', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->factory->getTranslator()->trans('mautic.page.show.unique.visits'), $data);
unset($filter['groupBy']);
}
return $chart->render();
}
/**
* Get line chart data of submissions
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param boolean $canViewOthers
*
* @return array
*/
public function getSubmissionsLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = array(), $canViewOthers = true)
{
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = $chart->getChartQuery($this->factory->getEntityManager()->getConnection());
$q = $query->prepareTimeDataQuery('form_submissions', 'date_submitted', $filter);
if (!$canViewOthers) {
$q->join('t', MAUTIC_TABLE_PREFIX . 'forms', 'f', 'f.id = t.form_id')->andWhere('f.created_by = :userId')->setParameter('userId', $this->factory->getUser()->getId());
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->factory->getTranslator()->trans('mautic.form.submission.count'), $data);
return $chart->render();
}
/**
* Get line chart data of downloads.
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param \DateTime $dateFrom
* @param \DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param bool $canViewOthers
*
* @return array
*/
public function getDownloadsLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = [], $canViewOthers = true)
{
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
$q = $query->prepareTimeDataQuery('asset_downloads', 'date_download', $filter);
if (!$canViewOthers) {
$q->join('t', MAUTIC_TABLE_PREFIX . 'assets', 'a', 'a.id = t.asset_id')->andWhere('a.created_by = :userId')->setParameter('userId', $this->userHelper->getUser()->getId());
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.asset.downloadcount'), $data);
return $chart->render();
}
/**
* Get line chart data of hits
*
* @param char $unit {@link php.net/manual/en/function.date.php#refsect1-function.date-parameters}
* @param \DateTime $dateFrom
* @param \DateTime $dateTo
* @param string $dateFormat
* @param array $filter
* @param boolean $canViewOthers
*
* @return array
*/
public function getHitsLineChartData($unit, \DateTime $dateFrom, \DateTime $dateTo, $dateFormat = null, $filter = array(), $canViewOthers = true)
{
$flag = null;
if (isset($filter['flag'])) {
$flag = $filter['flag'];
unset($filter['flag']);
}
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo);
if (!$flag || $flag === 'total_and_unique') {
$q = $query->prepareTimeDataQuery('dynamic_content_stats', 'date_sent', $filter);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.dynamicContent.show.total.views'), $data);
}
if ($flag === 'unique' || $flag === 'total_and_unique') {
$q = $query->prepareTimeDataQuery('dynamic_content_stats', 'date_sent', $filter);
$q->groupBy('t.lead_id, t.date_sent');
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.dynamicContent.show.unique.views'), $data);
}
return $chart->render();
}
/**
* 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
*/
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);
}
}
/**
* @param Focus $focus
* @param $unit
* @param \DateTime|null $dateFrom
* @param \DateTime|null $dateTo
* @param null $dateFormat
* @param bool $canViewOthers
*
* @return array
*/
public function getStats(Focus $focus, $unit, \DateTime $dateFrom = null, \DateTime $dateTo = null, $dateFormat = null, $canViewOthers = true)
{
$chart = new LineChart($unit, $dateFrom, $dateTo, $dateFormat);
$query = new ChartQuery($this->em->getConnection(), $dateFrom, $dateTo, $unit);
$q = $query->prepareTimeDataQuery('focus_stats', 'date_added');
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.focus.graph.views'), $data);
if ($focus->getType() != 'notification') {
if ($focus->getType() == 'link') {
$q = $query->prepareTimeDataQuery('focus_stats', 'date_added', ['type' => Stat::TYPE_CLICK]);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.focus.graph.clicks'), $data);
} else {
$q = $query->prepareTimeDataQuery('focus_stats', 'date_added', ['type' => Stat::TYPE_FORM]);
if (!$canViewOthers) {
$this->limitQueryToCreator($q);
}
$data = $query->loadAndBuildTimeData($q);
$chart->setDataset($this->translator->trans('mautic.focus.graph.submissions'), $data);
}
}
return $chart->render();
}