Server : Apache System : Linux server2.corals.io 4.18.0-348.2.1.el8_5.x86_64 #1 SMP Mon Nov 15 09:17:08 EST 2021 x86_64 User : corals ( 1002) PHP Version : 7.4.33 Disable Function : exec,passthru,shell_exec,system Directory : /home/corals/mautic.corals.io/app/bundles/PageBundle/EventListener/ |
<?php
namespace Mautic\PageBundle\EventListener;
use Mautic\CoreBundle\Helper\Chart\ChartQuery;
use Mautic\CoreBundle\Helper\Chart\LineChart;
use Mautic\CoreBundle\Helper\Chart\PieChart;
use Mautic\LeadBundle\Model\CompanyReportData;
use Mautic\PageBundle\Entity\HitRepository;
use Mautic\ReportBundle\Event\ReportBuilderEvent;
use Mautic\ReportBundle\Event\ReportGeneratorEvent;
use Mautic\ReportBundle\Event\ReportGraphEvent;
use Mautic\ReportBundle\ReportEvents;
use Symfony\Component\EventDispatcher\EventSubscriberInterface;
use Symfony\Contracts\Translation\TranslatorInterface;
class ReportSubscriber implements EventSubscriberInterface
{
public const CONTEXT_PAGES = 'pages';
public const CONTEXT_PAGE_HITS = 'page.hits';
public const CONTEXT_VIDEO_HITS = 'video.hits';
public function __construct(
private CompanyReportData $companyReportData,
private HitRepository $hitRepository,
private TranslatorInterface $translator
) {
}
public static function getSubscribedEvents(): array
{
return [
ReportEvents::REPORT_ON_BUILD => ['onReportBuilder', 0],
ReportEvents::REPORT_ON_GENERATE => ['onReportGenerate', 0],
ReportEvents::REPORT_ON_GRAPH_GENERATE => ['onReportGraphGenerate', 0],
];
}
/**
* Add available tables and columns to the report builder lookup.
*/
public function onReportBuilder(ReportBuilderEvent $event): void
{
if (!$event->checkContext([self::CONTEXT_PAGES, self::CONTEXT_PAGE_HITS, self::CONTEXT_VIDEO_HITS])) {
return;
}
$prefix = 'p.';
$translationPrefix = 'tp.';
$variantPrefix = 'vp.';
$columns = [
$prefix.'title' => [
'label' => 'mautic.core.title',
'type' => 'string',
],
$prefix.'alias' => [
'label' => 'mautic.core.alias',
'type' => 'string',
],
$prefix.'revision' => [
'label' => 'mautic.page.report.revision',
'type' => 'string',
],
$prefix.'hits' => [
'label' => 'mautic.page.field.hits',
'type' => 'int',
],
$prefix.'unique_hits' => [
'label' => 'mautic.page.field.unique_hits',
'type' => 'int',
],
$translationPrefix.'id' => [
'label' => 'mautic.page.report.translation_parent_id',
'type' => 'int',
],
$translationPrefix.'title' => [
'label' => 'mautic.page.report.translation_parent_title',
'type' => 'string',
],
$variantPrefix.'id' => [
'label' => 'mautic.page.report.variant_parent_id',
'type' => 'string',
],
$variantPrefix.'title' => [
'label' => 'mautic.page.report.variant_parent_title',
'type' => 'string',
],
$prefix.'lang' => [
'label' => 'mautic.core.language',
'type' => 'string',
],
$prefix.'variant_start_date' => [
'label' => 'mautic.page.report.variant_start_date',
'type' => 'datetime',
'groupByFormula' => 'DATE('.$prefix.'variant_start_date)',
],
$prefix.'variant_hits' => [
'label' => 'mautic.page.report.variant_hits',
'type' => 'int',
],
];
$columns = array_merge(
$columns,
$event->getStandardColumns('p.', ['name', 'description'], 'mautic_page_action'),
$event->getCategoryColumns()
);
$data = [
'display_name' => 'mautic.page.pages',
'columns' => $columns,
];
$event->addTable(self::CONTEXT_PAGES, $data);
if ($event->checkContext(self::CONTEXT_PAGE_HITS)) {
$hitPrefix = 'ph.';
$redirectHit = 'r.';
$hitColumns = [
$hitPrefix.'id' => [
'label' => 'mautic.page.report.hits.id',
'type' => 'int',
],
$hitPrefix.'date_hit' => [
'label' => 'mautic.page.report.hits.date_hit',
'type' => 'datetime',
'groupByFormula' => 'DATE('.$hitPrefix.'date_hit)',
],
$hitPrefix.'date_left' => [
'label' => 'mautic.page.report.hits.date_left',
'type' => 'datetime',
'groupByFormula' => 'DATE('.$hitPrefix.'date_left)',
],
$hitPrefix.'country' => [
'label' => 'mautic.page.report.hits.country',
'type' => 'string',
],
$hitPrefix.'region' => [
'label' => 'mautic.page.report.hits.region',
'type' => 'string',
],
$hitPrefix.'city' => [
'label' => 'mautic.page.report.hits.city',
'type' => 'string',
],
$hitPrefix.'isp' => [
'label' => 'mautic.page.report.hits.isp',
'type' => 'string',
],
$hitPrefix.'organization' => [
'label' => 'mautic.page.report.hits.organization',
'type' => 'string',
],
$hitPrefix.'code' => [
'label' => 'mautic.page.report.hits.code',
'type' => 'int',
],
$hitPrefix.'referer' => [
'label' => 'mautic.page.report.hits.referer',
'type' => 'string',
],
$hitPrefix.'url' => [
'label' => 'mautic.page.report.hits.url',
'type' => 'url',
],
$hitPrefix.'url_title' => [
'label' => 'mautic.page.report.hits.url_title',
'type' => 'string',
],
$hitPrefix.'user_agent' => [
'label' => 'mautic.page.report.hits.user_agent',
'type' => 'string',
],
$hitPrefix.'remote_host' => [
'label' => 'mautic.page.report.hits.remote_host',
'type' => 'string',
],
$hitPrefix.'browser_languages' => [
'label' => 'mautic.page.report.hits.browser_languages',
'type' => 'array',
],
$hitPrefix.'source' => [
'label' => 'mautic.report.field.source',
'type' => 'string',
],
$hitPrefix.'source_id' => [
'label' => 'mautic.report.field.source_id',
'type' => 'int',
],
$redirectHit.'url' => [
'label' => 'mautic.page.report.hits.redirect_url',
'type' => 'url',
],
$redirectHit.'hits' => [
'label' => 'mautic.page.report.hits.redirect_hit_count',
'type' => 'int',
],
$redirectHit.'unique_hits' => [
'label' => 'mautic.page.report.hits.redirect_unique_hits',
'type' => 'string',
],
'ds.device' => [
'label' => 'mautic.lead.device',
'type' => 'string',
],
'ds.device_brand' => [
'label' => 'mautic.lead.device_brand',
'type' => 'string',
],
'ds.device_model' => [
'label' => 'mautic.lead.device_model',
'type' => 'string',
],
'ds.device_os_name' => [
'label' => 'mautic.lead.device_os_name',
'type' => 'string',
],
'ds.device_os_shortname' => [
'label' => 'mautic.lead.device_os_shortname',
'type' => 'string',
],
'ds.device_os_version' => [
'label' => 'mautic.lead.device_os_version',
'type' => 'string',
],
'ds.device_os_platform' => [
'label' => 'mautic.lead.device_os_platform',
'type' => 'string',
],
];
$companyColumns = $this->companyReportData->getCompanyData();
$pageHitsColumns = array_merge(
$columns,
$hitColumns,
$event->getCampaignByChannelColumns(),
$event->getLeadColumns(),
$event->getIpColumn(),
$companyColumns
);
$data = [
'display_name' => 'mautic.page.hits',
'columns' => $pageHitsColumns,
];
$event->addTable(self::CONTEXT_PAGE_HITS, $data, self::CONTEXT_PAGES);
// Register graphs
$context = self::CONTEXT_PAGE_HITS;
$event->addGraph($context, 'line', 'mautic.page.graph.line.hits');
$event->addGraph($context, 'line', 'mautic.page.graph.line.time.on.site');
$event->addGraph($context, 'pie', 'mautic.page.graph.pie.time.on.site', ['translate' => false]);
$event->addGraph($context, 'pie', 'mautic.page.graph.pie.new.vs.returning');
$event->addGraph($context, 'pie', 'mautic.page.graph.pie.devices');
$event->addGraph($context, 'pie', 'mautic.page.graph.pie.languages', ['translate' => false]);
$event->addGraph($context, 'table', 'mautic.page.table.referrers');
$event->addGraph($context, 'table', 'mautic.page.table.most.visited');
$event->addGraph($context, 'table', 'mautic.page.table.most.visited.unique');
}
if ($event->checkContext(self::CONTEXT_VIDEO_HITS)) {
$hitPrefix = 'vh.';
$hitColumns = [
$hitPrefix.'id' => [
'label' => 'mautic.core.id',
'type' => 'int',
],
$hitPrefix.'date_hit' => [
'label' => 'mautic.page.report.hits.date_hit',
'type' => 'datetime',
'groupByFormula' => 'DATE('.$hitPrefix.'date_hit)',
],
$hitPrefix.'country' => [
'label' => 'mautic.page.report.hits.country',
'type' => 'string',
],
$hitPrefix.'region' => [
'label' => 'mautic.page.report.hits.region',
'type' => 'string',
],
$hitPrefix.'city' => [
'label' => 'mautic.page.report.hits.city',
'type' => 'string',
],
$hitPrefix.'isp' => [
'label' => 'mautic.page.report.hits.isp',
'type' => 'string',
],
$hitPrefix.'organization' => [
'label' => 'mautic.page.report.hits.organization',
'type' => 'string',
],
$hitPrefix.'code' => [
'label' => 'mautic.page.report.hits.code',
'type' => 'int',
],
$hitPrefix.'referer' => [
'label' => 'mautic.page.report.hits.referer',
'type' => 'string',
],
$hitPrefix.'url' => [
'label' => 'mautic.page.report.hits.url',
'type' => 'url',
],
$hitPrefix.'user_agent' => [
'label' => 'mautic.page.report.hits.user_agent',
'type' => 'string',
],
$hitPrefix.'remote_host' => [
'label' => 'mautic.page.report.hits.remote_host',
'type' => 'string',
],
$hitPrefix.'browser_languages' => [
'label' => 'mautic.page.report.hits.browser_languages',
'type' => 'array',
],
$hitPrefix.'channel' => [
'label' => 'mautic.report.field.source',
'type' => 'string',
],
$hitPrefix.'channel_id' => [
'label' => 'mautic.report.field.source_id',
'type' => 'int',
],
'time_watched' => [
'label' => 'mautic.page.report.hits.time_watched',
'type' => 'string',
'formula' => 'if('.$hitPrefix.'duration = 0,\'-\',SEC_TO_TIME('.$hitPrefix.'time_watched))',
],
'duration' => [
'label' => 'mautic.page.report.hits.duration',
'type' => 'string',
'formula' => 'if('.$hitPrefix.'duration = 0,\'-\',SEC_TO_TIME('.$hitPrefix.'duration))',
],
];
$data = [
'display_name' => 'mautic.'.self::CONTEXT_VIDEO_HITS,
'columns' => array_merge($hitColumns, $event->getLeadColumns(), $event->getIpColumn()),
];
$event->addTable(self::CONTEXT_VIDEO_HITS, $data, 'videos');
}
}
/**
* Initialize the QueryBuilder object to generate reports from.
*/
public function onReportGenerate(ReportGeneratorEvent $event): void
{
$context = $event->getContext();
$qb = $event->getQueryBuilder();
$hasGroupBy = $event->hasGroupBy();
switch ($context) {
case self::CONTEXT_PAGES:
$qb->from(MAUTIC_TABLE_PREFIX.'pages', 'p')
->leftJoin('p', MAUTIC_TABLE_PREFIX.'pages', 'tp', 'p.id = tp.id')
->leftJoin('p', MAUTIC_TABLE_PREFIX.'pages', 'vp', 'p.id = vp.id');
$event->addCategoryLeftJoin($qb, 'p');
break;
case self::CONTEXT_PAGE_HITS:
$event->applyDateFilters($qb, 'date_hit', 'ph');
$qb->from(MAUTIC_TABLE_PREFIX.'page_hits', 'ph')
->leftJoin('ph', MAUTIC_TABLE_PREFIX.'pages', 'p', 'ph.page_id = p.id')
->leftJoin('p', MAUTIC_TABLE_PREFIX.'pages', 'tp', 'p.id = tp.id')
->leftJoin('p', MAUTIC_TABLE_PREFIX.'pages', 'vp', 'p.id = vp.id')
->leftJoin('ph', MAUTIC_TABLE_PREFIX.'page_redirects', 'r', 'r.id = ph.redirect_id')
->leftJoin('ph', MAUTIC_TABLE_PREFIX.'lead_devices', 'ds', 'ds.id = ph.device_id');
$event->addIpAddressLeftJoin($qb, 'ph');
$event->addCategoryLeftJoin($qb, 'p');
$event->addLeadLeftJoin($qb, 'ph');
$event->addCampaignByChannelJoin($qb, 'p', 'page');
if ($this->companyReportData->eventHasCompanyColumns($event)) {
$event->addCompanyLeftJoin($qb);
}
break;
case 'video.hits':
if (!$hasGroupBy) {
$qb->groupBy('vh.id');
}
$event->applyDateFilters($qb, 'date_hit', 'vh');
$qb->from(MAUTIC_TABLE_PREFIX.'video_hits', 'vh');
$event->addIpAddressLeftJoin($qb, 'vh');
$event->addLeadLeftJoin($qb, 'vh');
break;
}
$event->setQueryBuilder($qb);
}
/**
* Initialize the QueryBuilder object to generate reports from.
*/
public function onReportGraphGenerate(ReportGraphEvent $event): void
{
// Context check, we only want to fire for Lead reports
if (!$event->checkContext(self::CONTEXT_PAGE_HITS)) {
return;
}
$graphs = $event->getRequestedGraphs();
$qb = $event->getQueryBuilder();
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 = $this->hitRepository->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' => 'ri-time-line',
]
);
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->translator->trans('mautic.page.unique'), $unique);
$chart->setDataset($this->translator->trans('mautic.page.graph.pie.new.vs.returning.returning'), $returning);
$event->setGraph(
$g,
[
'data' => $chart->render(),
'name' => $g,
'iconClass' => 'ri-information-2-line',
]
);
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()->fetchAllAssociative();
$chart = new PieChart();
foreach ($data as $lang) {
$chart->setDataset($lang['page_language'], $lang['the_count']);
}
$event->setGraph(
$g,
[
'data' => $chart->render(),
'name' => $g,
'iconClass' => 'ri-earth-line',
]
);
break;
case 'mautic.page.graph.pie.devices':
$queryBuilder->select('ds.device, COUNT(distinct(ph.id)) as the_count')
->groupBy('ds.device');
$data = $queryBuilder->execute()->fetchAllAssociative();
$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' => 'ri-earth-line',
]
);
break;
case 'mautic.page.table.referrers':
$limit = 10;
$offset = 0;
$items = $this->hitRepository->getReferers($queryBuilder, $limit, $offset);
$graphData = [];
$graphData['data'] = $items;
$graphData['name'] = $g;
$graphData['iconClass'] = 'ri-login-box-line';
$event->setGraph($g, $graphData);
break;
case 'mautic.page.table.most.visited':
$limit = 10;
$offset = 0;
$items = $this->hitRepository->getMostVisited($queryBuilder, $limit, $offset);
$graphData = [];
$graphData['data'] = $items;
$graphData['name'] = $g;
$graphData['iconClass'] = 'ri-eye-line';
$graphData['link'] = 'mautic_page_action';
$event->setGraph($g, $graphData);
break;
case 'mautic.page.table.most.visited.unique':
$limit = 10;
$offset = 0;
$items = $this->hitRepository->getMostVisited($queryBuilder, $limit, $offset, 'p.unique_hits', 'sessions');
$graphData = [];
$graphData['data'] = $items;
$graphData['name'] = $g;
$graphData['iconClass'] = 'ri-eye-line';
$graphData['link'] = 'mautic_page_action';
$event->setGraph($g, $graphData);
break;
}
unset($queryBuilder);
}
}
}