Exemple #1
0
 /**
  * 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
  */
 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);
     }
 }