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