/**
* Initialize the QueryBuilder object to generate reports from
*
* @param ReportGeneratorEvent $event
*
* @return void
*/
public function onReportGenerate(ReportGeneratorEvent $event)
{
$context = $event->getContext();
$qb = $event->getQueryBuilder();
switch ($context) {
case '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 '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');
break;
}
$event->setQueryBuilder($qb);
}
/**
* Initialize the QueryBuilder object to generate reports from
*
* @param ReportGeneratorEvent $event
*
* @return void
*/
public function onReportGenerate(ReportGeneratorEvent $event)
{
$context = $event->getContext();
$queryBuilder = $event->getQueryBuilder();
if ($context == 'assets') {
$queryBuilder->from(MAUTIC_TABLE_PREFIX . 'assets', 'a');
$event->addCategoryLeftJoin($queryBuilder, 'a');
} elseif ($context == 'asset.downloads') {
$event->applyDateFilters($queryBuilder, 'date_download', 'ad');
$queryBuilder->from(MAUTIC_TABLE_PREFIX . 'asset_downloads', 'ad')->leftJoin('ad', MAUTIC_TABLE_PREFIX . 'assets', 'a', 'a.id = ad.asset_id');
$event->addCategoryLeftJoin($queryBuilder, 'a');
$event->addLeadLeftJoin($queryBuilder, 'ad');
$event->addIpAddressLeftJoin($queryBuilder, 'ad');
}
$event->setQueryBuilder($queryBuilder);
}
/**
* Initialize the QueryBuilder object to generate reports from.
*
* @param ReportGeneratorEvent $event
*/
public function onReportGenerate(ReportGeneratorEvent $event)
{
$context = $event->getContext();
$qb = $event->getQueryBuilder();
switch ($context) {
case 'forms':
$qb->from(MAUTIC_TABLE_PREFIX . 'forms', 'f');
$event->addCategoryLeftJoin($qb, 'f');
break;
case 'form.submissions':
$event->applyDateFilters($qb, 'date_submitted', 'fs');
$qb->from(MAUTIC_TABLE_PREFIX . 'form_submissions', 'fs')->leftJoin('fs', MAUTIC_TABLE_PREFIX . 'forms', 'f', 'f.id = fs.form_id')->leftJoin('fs', MAUTIC_TABLE_PREFIX . 'pages', 'p', 'p.id = fs.page_id');
$event->addCategoryLeftJoin($qb, 'f');
$event->addLeadLeftJoin($qb, 'fs');
$event->addIpAddressLeftJoin($qb, 'fs');
break;
}
$event->setQueryBuilder($qb);
}
/**
* Initialize the QueryBuilder object to generate reports from.
*
* @param ReportGeneratorEvent $event
*/
public function onReportGenerate(ReportGeneratorEvent $event)
{
$context = $event->getContext();
$qb = $event->getQueryBuilder();
switch ($context) {
case 'emails':
$qb->from(MAUTIC_TABLE_PREFIX . 'emails', 'e')->leftJoin('e', MAUTIC_TABLE_PREFIX . 'emails', 'vp', 'vp.id = e.variant_parent_id');
$event->addCategoryLeftJoin($qb, 'e');
break;
case 'email.stats':
$event->applyDateFilters($qb, 'date_sent', 'es');
$qb->from(MAUTIC_TABLE_PREFIX . 'email_stats', 'es')->leftJoin('es', MAUTIC_TABLE_PREFIX . 'emails', 'e', 'e.id = es.email_id')->leftJoin('e', MAUTIC_TABLE_PREFIX . 'emails', 'vp', 'vp.id = e.variant_parent_id');
$event->addCategoryLeftJoin($qb, 'e');
$event->addLeadLeftJoin($qb, 'es');
$event->addIpAddressLeftJoin($qb, 'es');
break;
}
$event->setQueryBuilder($qb);
}
/**
* Initialize the QueryBuilder object to generate reports from
*
* @param ReportGeneratorEvent $event
*
* @return void
*/
public function onReportGenerate(ReportGeneratorEvent $event)
{
$context = $event->getContext();
if ($context == 'emails') {
$qb = $this->factory->getEntityManager()->getConnection()->createQueryBuilder();
$qb->from(MAUTIC_TABLE_PREFIX . 'emails', 'e')->leftJoin('e', MAUTIC_TABLE_PREFIX . 'emails', 'vp', 'vp.id = e.variant_parent_id');
$event->addCategoryLeftJoin($qb, 'e');
$event->setQueryBuilder($qb);
} elseif ($context == 'email.stats') {
$qb = $this->factory->getEntityManager()->getConnection()->createQueryBuilder();
$qb->from(MAUTIC_TABLE_PREFIX . 'email_stats', 'es')->leftJoin('es', MAUTIC_TABLE_PREFIX . 'emails', 'e', 'e.id = es.email_id')->leftJoin('e', MAUTIC_TABLE_PREFIX . 'emails', 'vp', 'vp.id = e.variant_parent_id');
$event->addCategoryLeftJoin($qb, 'e');
$event->addLeadLeftJoin($qb, 'es');
$event->addIpAddressLeftJoin($qb, 'es');
$event->setQueryBuilder($qb);
}
}
/**
* Initialize the QueryBuilder object to generate reports from
*
* @param ReportGeneratorEvent $event
*
* @return void
*/
public function onReportGenerate(ReportGeneratorEvent $event)
{
$context = $event->getContext();
if ($context == 'forms') {
$qb = $this->factory->getEntityManager()->getConnection()->createQueryBuilder();
$qb->from(MAUTIC_TABLE_PREFIX . 'forms', 'f');
$event->addCategoryLeftJoin($qb, 'f');
$event->setQueryBuilder($qb);
} elseif ($context == 'form.submissions') {
$qb = $this->factory->getEntityManager()->getConnection()->createQueryBuilder();
$qb->from(MAUTIC_TABLE_PREFIX . 'form_submissions', 'fs')->leftJoin('fs', MAUTIC_TABLE_PREFIX . 'forms', 'f', 'f.id = fs.form_id')->leftJoin('fs', MAUTIC_TABLE_PREFIX . 'pages', 'p', 'p.id = fs.page_id');
$event->addCategoryLeftJoin($qb, 'f');
$event->addLeadLeftJoin($qb, 'fs');
$event->addIpAddressLeftJoin($qb, 'fs');
$event->setQueryBuilder($qb);
}
}
/**
* Initialize the QueryBuilder object to generate reports from
*
* @param ReportGeneratorEvent $event
*
* @return void
*/
public function onReportGenerate(ReportGeneratorEvent $event)
{
$context = $event->getContext();
if ($context == 'assets') {
$queryBuilder = $this->factory->getEntityManager()->getConnection()->createQueryBuilder();
$queryBuilder->from(MAUTIC_TABLE_PREFIX . 'assets', 'a');
$event->addCategoryLeftJoin($queryBuilder, 'a');
$event->setQueryBuilder($queryBuilder);
} elseif ($context == 'asset.downloads') {
$queryBuilder = $this->factory->getEntityManager()->getConnection()->createQueryBuilder();
$queryBuilder->from(MAUTIC_TABLE_PREFIX . 'asset_downloads', 'ad')->leftJoin('ad', MAUTIC_TABLE_PREFIX . 'assets', 'a', 'a.id = ad.asset_id');
$event->addCategoryLeftJoin($queryBuilder, 'a');
$event->addLeadLeftJoin($queryBuilder, 'ad');
$event->addIpAddressLeftJoin($queryBuilder, 'ad');
$event->setQueryBuilder($queryBuilder);
}
}
public function onReportGenerate(ReportGeneratorEvent $event)
{
$context = $event->getContext();
if ($context === 'task') {
$queryBuilder = $this->factory->getDatabase()->createQueryBuilder();
$queryBuilder->from('tasks', 't')->leftJoin('t', 'users', 'u', 'u.id = t.assign_user_id');
$event->addLeadLeftJoin($queryBuilder, 't');
$event->setQueryBuilder($queryBuilder);
}
if ($context === 'opportunity') {
$queryBuilder = $this->factory->getDatabase()->createQueryBuilder();
$queryBuilder->from('opportunities', 'op')->leftJoin('op', 'users', 'u', 'u.id = op.owner_user_id');
$event->addLeadLeftJoin($queryBuilder, 'op');
$event->setQueryBuilder($queryBuilder);
}
}
/**
* Initialize the QueryBuilder object to generate reports from
*
* @param ReportGeneratorEvent $event
*
* @return void
*/
public function onReportGenerate(ReportGeneratorEvent $event)
{
$context = $event->getContext();
if ($context == 'pages') {
$qb = $this->factory->getEntityManager()->getConnection()->createQueryBuilder();
$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');
$event->setQueryBuilder($qb);
} elseif ($context == 'page.hits') {
$qb = $this->factory->getEntityManager()->getConnection()->createQueryBuilder();
$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 . 'emails', 'e', 'e.id = ph.email_id')->leftJoin('ph', MAUTIC_TABLE_PREFIX . 'page_redirects', 'r', 'r.id = ph.redirect_id');
$event->addIpAddressLeftJoin($qb, 'ph');
$event->addCategoryLeftJoin($qb, 'p');
$event->addLeadLeftJoin($qb, 'ph');
$event->setQueryBuilder($qb);
}
}
/**
* Initialize the QueryBuilder object to generate reports from.
*
* @param ReportGeneratorEvent $event
*/
public function onReportGenerate(ReportGeneratorEvent $event)
{
$context = $event->getContext();
$qb = $event->getQueryBuilder();
switch ($context) {
case 'leads':
$event->applyDateFilters($qb, 'date_added', 'l');
$qb->from(MAUTIC_TABLE_PREFIX . 'leads', 'l');
if ($event->hasColumn(['u.first_name', 'u.last_name']) || $event->hasFilter(['u.first_name', 'u.last_name'])) {
$qb->leftJoin('l', MAUTIC_TABLE_PREFIX . 'users', 'u', 'u.id = l.owner_id');
}
if ($event->hasColumn('i.ip_address') || $event->hasFilter('i.ip_address')) {
$qb->leftJoin('l', MAUTIC_TABLE_PREFIX . 'lead_ips_xref', 'lip', 'lip.lead_id = l.id');
$event->addIpAddressLeftJoin($qb, 'lip');
}
if ($event->hasFilter('s.leadlist_id')) {
$qb->join('l', MAUTIC_TABLE_PREFIX . 'lead_lists_leads', 's', 's.lead_id = l.id AND s.manually_removed = 0');
}
break;
case 'lead.pointlog':
$event->applyDateFilters($qb, 'date_added', 'lp');
$qb->from(MAUTIC_TABLE_PREFIX . 'lead_points_change_log', 'lp')->leftJoin('lp', MAUTIC_TABLE_PREFIX . 'leads', 'l', 'l.id = lp.lead_id');
if ($event->hasColumn(['u.first_name', 'u.last_name']) || $event->hasFilter(['u.first_name', 'u.last_name'])) {
$qb->leftJoin('l', MAUTIC_TABLE_PREFIX . 'users', 'u', 'u.id = l.owner_id');
}
if ($event->hasColumn('i.ip_address') || $event->hasFilter('i.ip_address')) {
$qb->leftJoin('l', MAUTIC_TABLE_PREFIX . 'lead_ips_xref', 'lip', 'lip.lead_id = l.id');
$event->addIpAddressLeftJoin($qb, 'lp');
}
break;
case 'contact.attribution.multi':
case 'contact.attribution.first':
case 'contact.attribution.last':
$localDateTriggered = 'CONVERT_TZ(log.date_triggered,\'UTC\',\'' . date_default_timezone_get() . '\')';
$event->applyDateFilters($qb, 'attribution_date', 'l', true);
$qb->from(MAUTIC_TABLE_PREFIX . 'leads', 'l')->join('l', MAUTIC_TABLE_PREFIX . 'campaign_lead_event_log', 'log', 'l.id = log.lead_id')->leftJoin('l', MAUTIC_TABLE_PREFIX . 'stages', 's', 'l.stage_id = s.id')->join('log', MAUTIC_TABLE_PREFIX . 'campaign_events', 'e', 'log.event_id = e.id')->join('log', MAUTIC_TABLE_PREFIX . 'campaigns', 'c', 'log.campaign_id = c.id')->andWhere($qb->expr()->andX($qb->expr()->eq('e.event_type', $qb->expr()->literal('decision')), $qb->expr()->eq('log.is_scheduled', 0), $qb->expr()->isNotNull('l.attribution'), $qb->expr()->neq('l.attribution', 0), $qb->expr()->lte("DATE({$localDateTriggered})", 'DATE(l.attribution_date)')));
if ($event->hasColumn(['u.first_name', 'u.last_name']) || $event->hasFilter(['u.first_name', 'u.last_name'])) {
$qb->leftJoin('l', MAUTIC_TABLE_PREFIX . 'users', 'u', 'u.id = l.owner_id');
}
if ($event->hasColumn('i.ip_address') || $event->hasFilter('i.ip_address')) {
$event->addIpAddressLeftJoin($qb, 'log');
}
if ($event->hasColumn(['cat.id', 'cat.title']) || $event->hasColumn(['cat.id', 'cat.title'])) {
$event->addCategoryLeftJoin($qb, 'c', 'cat');
}
$subQ = clone $qb;
$subQ->resetQueryParts();
$alias = str_replace('contact.attribution.', '', $context);
$expr = $subQ->expr()->andX($subQ->expr()->eq("{$alias}e.event_type", $subQ->expr()->literal('decision')), $subQ->expr()->eq("{$alias}log.lead_id", 'log.lead_id'));
$subsetFilters = ['log.campaign_id', 'c.name', 'channel', 'channel_action', 'e.name'];
if ($event->hasFilter($subsetFilters)) {
// Must use the same filters for determining the min of a given subset
$filters = $event->getReport()->getFilters();
foreach ($filters as $filter) {
if (in_array($filter['column'], $subsetFilters)) {
$filterParam = $event->createParameterName();
if (isset($filter['formula'])) {
$x = "({$filter['formula']}) as {$alias}_{$filter['column']}";
} else {
$x = $alias . $filter['column'];
}
$expr->add($expr->{$filter['operator']}($x, ":{$filterParam}"));
$qb->setParameter($filterParam, $filter['value']);
}
}
}
$subQ->from(MAUTIC_TABLE_PREFIX . 'campaign_lead_event_log', "{$alias}log")->join("{$alias}log", MAUTIC_TABLE_PREFIX . 'campaign_events', "{$alias}e", "{$alias}log.event_id = {$alias}e.id")->join("{$alias}e", MAUTIC_TABLE_PREFIX . 'campaigns', "{$alias}c", "{$alias}e.campaign_id = {$alias}c.id")->where($expr);
if ('multi' != $alias) {
// Get the min/max row and group by lead for first touch or last touch events
$func = 'first' == $alias ? 'min' : 'max';
$subQ->select("{$func}({$alias}log.date_triggered)")->setMaxResults(1);
$qb->andWhere($qb->expr()->eq('log.date_triggered', sprintf('(%s)', $subQ->getSQL())))->groupBy('l.id');
} else {
// Get the total count of records for this lead that match the filters to divide the attribution by
$subQ->select('count(*)')->groupBy("{$alias}log.lead_id");
$qb->addSelect(sprintf('(%s) activity_count', $subQ->getSQL()));
}
break;
}
$event->setQueryBuilder($qb);
}
/**
* Initialize the QueryBuilder object to generate reports from
*
* @param ReportGeneratorEvent $event
*
* @return void
*/
public function onReportGenerate(ReportGeneratorEvent $event)
{
$context = $event->getContext();
if ($context == 'leads') {
$qb = $this->factory->getEntityManager()->getConnection()->createQueryBuilder();
$qb->from(MAUTIC_TABLE_PREFIX . 'leads', 'l');
$qb->leftJoin('l', MAUTIC_TABLE_PREFIX . 'users', 'u', 'u.id = l.owner_id');
$event->setQueryBuilder($qb);
} elseif ($context == 'lead.pointlog') {
$qb = $this->factory->getEntityManager()->getConnection()->createQueryBuilder();
$qb->from(MAUTIC_TABLE_PREFIX . 'lead_points_change_log', 'lp')->leftJoin('lp', MAUTIC_TABLE_PREFIX . 'leads', 'l', 'l.id = lp.lead_id')->leftJoin('l', MAUTIC_TABLE_PREFIX . 'users', 'u', 'u.id = l.owner_id');
$event->addIpAddressLeftJoin($qb, 'lp');
$event->setQueryBuilder($qb);
}
}