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(); $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 * * @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 == '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 * * @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); } }
/** * 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(); $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 */ 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); } }