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