/** * Adds events to the calendar. * * @param CalendarGeneratorEvent $event */ public function onCalendarGenerate(CalendarGeneratorEvent $event) { $dates = $event->getDates(); $query = $this->em->getConnection()->createQueryBuilder(); $query->select('es.email_id, e.subject AS title, COUNT(es.id) AS quantity, es.date_sent AS start, e.plain_text AS description, cat.color, es.lead_id, l.firstname, l.lastname, l.email')->from(MAUTIC_TABLE_PREFIX . 'email_stats', 'es')->leftJoin('es', MAUTIC_TABLE_PREFIX . 'emails', 'e', 'es.email_id = e.id')->leftJoin('e', MAUTIC_TABLE_PREFIX . 'categories', 'cat', 'cat.id = e.category_id AND cat.bundle=:bundle')->leftJoin('es', MAUTIC_TABLE_PREFIX . 'leads', 'l', 'l.id = es.lead_id')->where($query->expr()->andX($query->expr()->gte('es.date_sent', ':start'), $query->expr()->lte('es.date_sent', ':end')))->groupBy('e.id, es.email_id, e.subject, es.date_sent, e.plain_text, cat.color, es.lead_id, l.firstname, l.lastname, l.email')->setParameter('bundle', 'email')->setParameter('start', $dates['start_date'])->setParameter('end', $dates['end_date'])->setFirstResult(0)->setMaxResults(15); $results = $query->execute()->fetchAll(); // We need to convert the date to a ISO8601 compliant string foreach ($results as &$object) { $date = new DateTimeHelper($object['start']); $object['start'] = $date->toLocalString(\DateTime::ISO8601); if ($object['email_id']) { $object['url'] = $this->router->generate('mautic_email_action', ['objectAction' => 'view', 'objectId' => $object['email_id']], true); $object['attr'] = 'data-toggle="ajax"'; $object['description'] = html_entity_decode($object['description']); $object['title'] = $this->translator->trans('mautic.email.event.sent', ['%email%' => $object['title'], '%x%' => $object['quantity']]); } else { if ($object['firstname'] || $object['lastname']) { $contactName = trim($object['firstname'] . ' ' . $object['lastname']); } elseif ($object['email']) { $contactName = $object['email']; } else { $contactName = $this->translator->trans('mautic.lead.lead.anonymous'); } $details = $this->translator->trans('mautic.email.event.sent.direct', ['%contact%' => $contactName]); $object['url'] = $this->router->generate('mautic_contact_action', ['objectAction' => 'view', 'objectId' => $object['lead_id']], true); $object['attr'] = 'data-toggle="ajax"'; $object['title'] = $details; $object['description'] = $details; } } $event->addEvents($results); }
/** * @param $emailIds * @param \DateTime $fromDate * * @return array */ public function getDevice($statIds, $lead, $deviceName = null, $deviceBrand = null, $deviceModel = null, \DateTime $fromDate = null, \DateTime $toDate = null) { $sq = $this->_em->getConnection()->createQueryBuilder(); $sq->select('es.id as id, es.device as device')->from(MAUTIC_TABLE_PREFIX . 'lead_devices', 'es'); if (!empty($statIds)) { $inIds = !is_array($statIds) ? [(int) $statIds] : $statIds; $sq->where($sq->expr()->in('es.id', $inIds)); } if ($deviceName !== null) { $sq->where($sq->expr()->eq('es.device', ':device'))->setParameter('device', $deviceName); } if ($deviceBrand !== null) { $sq->where($sq->expr()->eq('es.device_brand', ':deviceBrand'))->setParameter('deviceBrand', $deviceBrand); } if ($deviceModel !== null) { $sq->where($sq->expr()->eq('es.device_model', ':deviceModel'))->setParameter('deviceModel', $deviceModel); } if ($lead !== null) { $sq->where($sq->expr()->eq('es.lead_id', $lead->getId())); } if ($fromDate !== null) { //make sure the date is UTC $dt = new DateTimeHelper($fromDate); $sq->andWhere($sq->expr()->gte('es.date_added', $sq->expr()->literal($dt->toUtcString()))); } if ($toDate !== null) { //make sure the date is UTC $dt = new DateTimeHelper($toDate); $sq->andWhere($sq->expr()->lte('es.date_added', $sq->expr()->literal($dt->toUtcString()))); } //get totals $device = $sq->execute()->fetchAll(); return !empty($device) ? $device[0] : []; }
/** * Adds events to the calendar * * @param CalendarGeneratorEvent $event * * @return void */ public function onCalendarGenerate(CalendarGeneratorEvent $event) { $dates = $event->getDates(); $router = $this->factory->getRouter(); $now = new DateTimeHelper(); $commonSelect = 'cl.campaign_id, c.name AS campaign_name, l.firstname, l.lastname, ce.type AS event_type, ce.name as event_name, cat.color'; $eventTypes = array(); $eventTypes['triggered'] = array('dateName' => 'cl.date_triggered'); $eventTypes['upcoming'] = array('dateName' => 'cl.trigger_date'); $query = $this->factory->getEntityManager()->getConnection()->createQueryBuilder(); $query->from(MAUTIC_TABLE_PREFIX . 'campaign_lead_event_log', 'cl')->leftJoin('cl', MAUTIC_TABLE_PREFIX . 'campaigns', 'c', 'cl.campaign_id = c.id')->leftJoin('cl', MAUTIC_TABLE_PREFIX . 'leads', 'l', 'cl.lead_id = l.id')->leftJoin('cl', MAUTIC_TABLE_PREFIX . 'campaign_events', 'ce', 'cl.event_id = ce.id')->leftJoin('cl', MAUTIC_TABLE_PREFIX . 'categories', 'cat', 'cat.id = c.category_id AND cat.bundle="campaign"')->setParameter('start', $dates['start_date'])->setParameter('end', $dates['end_date'])->setFirstResult(0)->setMaxResults(50); foreach ($eventTypes as $eventKey => $eventType) { $query->select($commonSelect . ', ' . $eventType['dateName'] . ' AS start')->where($query->expr()->andX($query->expr()->gte($eventType['dateName'], ':start'), $query->expr()->lte($eventType['dateName'], ':end'))); if ($eventKey == 'upcoming') { $query->andWhere($query->expr()->gte($eventType['dateName'], ':now'))->setParameter('now', $now->toUtcString()); } $results = $query->execute()->fetchAll(); // echo "<pre>";var_dump($results);die("</pre>"); // We need to convert the date to a ISO8601 compliant string foreach ($results as &$object) { if ($object['firstname'] || $object['lastname']) { $leadName = $object['firstname'] . ' ' . $object['lastname']; } else { $leadName = $this->translator->trans('mautic.lead.lead.anonymous'); } $date = new DateTimeHelper($object['start']); $object['start'] = $date->toLocalString(\DateTime::ISO8601); $object['url'] = $router->generate('mautic_campaign_action', array('objectAction' => 'view', 'objectId' => $object['campaign_id']), true); $object['attr'] = 'data-toggle="ajax"'; $object['description'] = $this->translator->trans('mautic.campaign.event.' . $eventKey . '.description', array('%campaign%' => $object['campaign_name'], '%lead%' => $leadName)); $object['title'] = $this->translator->trans('mautic.campaign.event.' . $eventKey, array('%event%' => $object['event_name'])); } $event->addEvents($results); } }
/** * Adds events to the calendar. * * @param CalendarGeneratorEvent $event * * @todo This method is only a model and should be removed when actual data is being populated */ public function onCalendarGenerate(CalendarGeneratorEvent $event) { $dates = $event->getDates(); $commonSelect = 'p.title, p.id as page_id, c.color'; $eventTypes = ['publish.up' => ['dateName' => 'publish_up', 'setter' => 'PublishUp'], 'publish.down' => ['dateName' => 'publish_down', 'setter' => 'PublishDown']]; $query = $this->em->getConnection()->createQueryBuilder(); $query->from(MAUTIC_TABLE_PREFIX . 'pages', 'p')->leftJoin('p', MAUTIC_TABLE_PREFIX . 'categories', 'c', 'c.id = p.category_id AND c.bundle=:bundle')->setParameter('bundle', 'page')->setParameter('start', $dates['start_date'])->setParameter('end', $dates['end_date'])->setFirstResult(0)->setMaxResults(50); foreach ($eventTypes as $eventKey => $eventType) { $query->select($commonSelect . ', ' . $eventType['dateName'] . ' AS start')->where($query->expr()->andX($query->expr()->gte('p.' . $eventType['dateName'], ':start'), $query->expr()->lte('p.' . $eventType['dateName'], ':end'))); $results = $query->execute()->fetchAll(); // We need to convert the date to a ISO8601 compliant string foreach ($results as &$object) { $date = new DateTimeHelper($object['start']); $eventTitle = $this->translator->trans('mautic.page.event.' . $eventKey, ['%page%' => $object['title']]); $object['start'] = $date->toLocalString(\DateTime::ISO8601); $object['setter'] = $eventType['setter']; $object['entityId'] = $object['page_id']; $object['entityType'] = 'page'; $object['editable'] = true; $object['url'] = $this->router->generate('mautic_calendar_action', ['objectAction' => 'edit', 'source' => 'page', 'objectId' => $object['page_id'], 'startDate' => $date->toLocalString()], true); $object['viewUrl'] = $this->router->generate('mautic_page_action', ['objectAction' => 'view', 'objectId' => $object['page_id']], true); $object['attr'] = ['data-toggle' => 'ajaxmodal', 'data-target' => '#CalendarEditModal', 'data-header' => $eventTitle]; $object['description'] = $this->translator->trans('mautic.page.event.' . $eventKey . '.description', ['%page%' => $object['title']]); $object['title'] = $eventTitle; } $event->addEvents($results); } }
/** * Last active updates every 2 minutes. If it didn't get updated, it means the user closed their browser and are thus * now offline * */ public function updateOnlineStatuses() { $dt = new DateTimeHelper(); $offlineDelay = $dt->getUtcDateTime(); $offlineDelay->setTimestamp(strtotime('15 minutes ago')); $q = $this->_em->createQueryBuilder()->update('MauticUserBundle:User', 'u')->set('u.onlineStatus', ':status')->where('u.lastActive <= :delay')->setParameter('delay', $offlineDelay)->setParameter('status', 'offline'); $q->getQuery()->execute(); }
/** * @param Lead $lead * @param array $options * * @return array */ public function getLeadIpLogs(Lead $lead, array $options = []) { $qb = $this->getEntityManager()->getConnection()->createQueryBuilder(); $sqb = $this->getEntityManager()->getConnection()->createQueryBuilder(); // Just a check to ensure reused IDs (happens with innodb) doesn't infect data $dt = new DateTimeHelper($lead->getDateAdded(), 'Y-m-d H:i:s', 'local'); $sqb->select('MAX(l.date_added) as date_added, l.ip_address')->from(MAUTIC_TABLE_PREFIX . 'audit_log', 'l')->where($sqb->expr()->andX($sqb->expr()->eq('l.bundle', $sqb->expr()->literal('lead')), $sqb->expr()->eq('l.object', $sqb->expr()->literal('lead')), $sqb->expr()->eq('l.action', $sqb->expr()->literal('ipadded')), $sqb->expr()->eq('l.object_id', $lead->getId()), $sqb->expr()->gte('l.date_added', $sqb->expr()->literal($dt->getUtcTimestamp()))))->groupBy('l.ip_address'); $qb->select('ip.date_added, ip.ip_address')->from(sprintf('(%s)', $sqb->getSQL()), 'ip'); return $this->getTimelineResults($qb, $options, 'ip.ip_address', 'ip.date_added', [], ['date_added']); }
public function reverseTransform($rawFilters) { if (!is_array($rawFilters)) { return array(); } foreach ($rawFilters as $k => $f) { if ($f['type'] == 'datetime') { $dt = new DateTimeHelper($f['filter'], 'Y-m-d H:i', 'local'); $rawFilters[$k]['filter'] = $dt->toUtcString(); } } return $rawFilters; }
/** * Adds events to the calendar * * @param CalendarGeneratorEvent $event * * @return void */ public function onCalendarGenerate(CalendarGeneratorEvent $event) { $dates = $event->getDates(); $query = $this->factory->getEntityManager()->getConnection()->createQueryBuilder(); $query->select('fs.referer AS url, f.name AS title, fs.date_submitted AS start')->from(MAUTIC_TABLE_PREFIX . 'form_submissions', 'fs')->leftJoin('fs', MAUTIC_TABLE_PREFIX . 'forms', 'f', 'fs.form_id = f.id')->where($query->expr()->andX($query->expr()->gte('fs.date_submitted', ':start'), $query->expr()->lte('fs.date_submitted', ':end')))->setParameter('start', $dates['start_date'])->setParameter('end', $dates['end_date'])->setFirstResult(0)->setMaxResults(5); $results = $query->execute()->fetchAll(); // We need to convert the date to a ISO8601 compliant string foreach ($results as &$object) { $date = new DateTimeHelper($object['start']); $object['start'] = $date->toLocalString(\DateTime::ISO8601); $object['title'] = $this->translator->trans('mautic.form.event.submission', array('%form%' => $object['title'])); } $event->addEvents($results); }
/** * @param \DateTime $value * @param AbstractPlatform $platform * * @return string|null */ public function convertToDatabaseValue($value, AbstractPlatform $platform) { if ($value === null) { return null; } if (!self::$utc) { self::$utc = new \DateTimeZone('UTC'); } if (!is_object($value)) { $dateHelper = new DateTimeHelper($value); $value = $dateHelper->getDateTime(); } $tz = $value->getTimeZone(); $utcDatetime = new \DateTime($value->format($platform->getDateTimeFormatString()), $tz); $utcDatetime->setTimezone(self::$utc); return $utcDatetime->format($platform->getDateTimeFormatString()); }
/** * Form format to database format * * @param mixed $rawFilters * * @return array|mixed */ public function reverseTransform($rawFilters) { if (!is_array($rawFilters)) { return array(); } $rawFilters = array_values($rawFilters); foreach ($rawFilters as $k => $f) { if ($f['type'] == 'datetime') { if (in_array($f['filter'], $this->relativeDateStrings)) { continue; } $dt = new DateTimeHelper($f['filter'], 'Y-m-d H:i', 'local'); $rawFilters[$k]['filter'] = $dt->toUtcString(); } } return $rawFilters; }
/** * Get sent counts based grouped by dynamic content Id. * * @param array $dynamicContentIds * @param \DateTime $fromDate * * @return array */ public function getSentCounts($dynamicContentIds = [], \DateTime $fromDate = null) { $q = $this->_em->getConnection()->createQueryBuilder(); $q->select('s.dynamic_content_id, count(s.id) as sent_count')->from(MAUTIC_TABLE_PREFIX . 'dynamic_content_stats', 's')->andWhere($q->expr()->in('e.dynamic_content_id', $dynamicContentIds)); if ($fromDate !== null) { //make sure the date is UTC $dt = new DateTimeHelper($fromDate); $q->andWhere($q->expr()->gte('e.date_sent', $q->expr()->literal($dt->toUtcString()))); } $q->groupBy('e.dynamic_content_id'); //get a total number of sent emails first $results = $q->execute()->fetchAll(); $counts = []; foreach ($results as $r) { $counts[$r['dynamic_content_id']] = $r['sent_count']; } return $counts; }
/** * {@inheritdoc} * * @return array */ public function reverseTransform($filters) { if (!is_array($filters)) { return []; } foreach ($filters as &$f) { if (!isset($this->columns[$f['column']])) { // Likely being called by form.pre_set_data after post return $filters; } $type = $this->columns[$f['column']]['type']; if (in_array($type, ['datetime', 'date', 'time'])) { $dt = new DateTimeHelper($f['value'], '', 'local'); $f['value'] = $dt->toUtcString(); } } return $filters; }
/** * Adds events to the calendar * * @param CalendarGeneratorEvent $event * * @return void */ public function onCalendarGenerate(CalendarGeneratorEvent $event) { $dates = $event->getDates(); $router = $this->factory->getRouter(); $query = $this->factory->getEntityManager()->getConnection()->createQueryBuilder(); $query->select('es.email_id, e.subject AS title, COUNT(es.id) AS quantity, es.date_sent AS start, e.plain_text AS description, cat.color')->from(MAUTIC_TABLE_PREFIX . 'email_stats', 'es')->leftJoin('es', MAUTIC_TABLE_PREFIX . 'emails', 'e', 'es.email_id = e.id')->leftJoin('e', MAUTIC_TABLE_PREFIX . 'categories', 'cat', 'cat.id = e.category_id AND cat.bundle="email"')->where($query->expr()->andX($query->expr()->gte('es.date_sent', ':start'), $query->expr()->lte('es.date_sent', ':end')))->groupBy('e.id, es.email_id, e.subject, es.date_sent, e.plain_text, cat.color')->setParameter('start', $dates['start_date'])->setParameter('end', $dates['end_date'])->setFirstResult(0)->setMaxResults(15); $results = $query->execute()->fetchAll(); // We need to convert the date to a ISO8601 compliant string foreach ($results as &$object) { $date = new DateTimeHelper($object['start']); $object['start'] = $date->toLocalString(\DateTime::ISO8601); $object['url'] = $router->generate('mautic_email_action', array('objectAction' => 'view', 'objectId' => $object['email_id']), true); $object['attr'] = 'data-toggle="ajax"'; $object['description'] = html_entity_decode($object['description']); $object['title'] = $this->translator->trans('mautic.email.event.sent', array('%email%' => $object['title'], '%x%' => $object['quantity'])); } $event->addEvents($results); }
/** * Adds events to the calendar * * @param CalendarGeneratorEvent $event * * @return void */ public function onCalendarGenerate(CalendarGeneratorEvent $event) { $dates = $event->getDates(); $router = $this->factory->getRouter(); // Lead Notes $query = $this->factory->getEntityManager()->getConnection()->createQueryBuilder(); $query->select('ln.lead_id, l.firstname, l.lastname, ln.date_time AS start, ln.text AS description, ln.type')->from(MAUTIC_TABLE_PREFIX . 'lead_notes', 'ln')->leftJoin('ln', MAUTIC_TABLE_PREFIX . 'leads', 'l', 'ln.lead_id = l.id')->where($query->expr()->andX($query->expr()->gte('ln.date_time', ':start'), $query->expr()->lte('ln.date_time', ':end')))->setParameter('start', $dates['start_date'])->setParameter('end', $dates['end_date'])->setFirstResult(0)->setMaxResults(100); $results = $query->execute()->fetchAll(); // We need to convert the date to a ISO8601 compliant string foreach ($results as &$object) { if ($object['firstname'] || $object['lastname']) { $leadName = $object['firstname'] . ' ' . $object['lastname']; } else { $leadName = $this->translator->trans('mautic.lead.lead.anonymous'); } $date = new DateTimeHelper($object['start']); $object['start'] = $date->toLocalString(\DateTime::ISO8601); $object['url'] = $router->generate('mautic_contact_action', array('objectAction' => 'view', 'objectId' => $object['lead_id']), true); $object['attr'] = 'data-toggle="ajax"'; $object['description'] = strip_tags(html_entity_decode($object['description'])); switch ($object['type']) { default: case 'general': $icon = 'fa-file-text'; break; case 'email': $icon = 'fa-send'; break; case 'call': $icon = 'fa-phone'; break; case 'meeting': $icon = 'fa-group'; break; } $object['iconClass'] = 'fa fa-fw ' . $icon; $object['title'] = $leadName; //$object['title'] .= ' (' . $this->translator->trans('mautic.lead.note.type.' . $object['type']) . ')'; } $event->addEvents($results); }
/** * @param $emailIds * @param \DateTime $fromDate * * @return array */ public function getDeviceStats($emailIds, \DateTime $fromDate = null, \DateTime $toDate = null) { $qb = $this->getEntityManager()->getConnection()->createQueryBuilder(); $qb->select('count(es.id) as count, d.device as device, es.list_id')->from(MAUTIC_TABLE_PREFIX . 'email_stats_devices', 'ed')->join('ed', MAUTIC_TABLE_PREFIX . 'lead_devices', 'd', 'd.id = ed.device_id')->join('ed', MAUTIC_TABLE_PREFIX . 'email_stats', 'es', 'es.id = ed.stat_id'); if ($emailIds != null) { if (!is_array($emailIds)) { $emailIds = [(int) $emailIds]; } $qb->where($qb->expr()->in('es.email_id', $emailIds)); } $qb->groupBy('es.list_id, d.device'); if ($fromDate !== null) { //make sure the date is UTC $dt = new DateTimeHelper($fromDate); $qb->andWhere($qb->expr()->gte('es.date_read', $qb->expr()->literal($dt->toUtcString()))); } if ($toDate !== null) { //make sure the date is UTC $dt = new DateTimeHelper($toDate); $qb->andWhere($qb->expr()->lte('es.date_read', $qb->expr()->literal($dt->toUtcString()))); } return $qb->execute()->fetchAll(); }
/** * Returns date/time like Today, 10:00 AM * * @param $datetime * @param string $timezone * @param string $fromFormat */ public function toText($datetime, $timezone = 'local', $fromFormat = 'Y-m-d H:i:s') { if (empty($datetime)) { return ''; } $this->helper->setDateTime($datetime, $fromFormat, $timezone); $textDate = $this->helper->getTextDate(); $dt = $this->helper->getLocalDateTime(); if ($textDate) { return $this->translator->trans('mautic.core.date.' . $textDate, array('%time%' => $dt->format("g:i a"))); } else { $interval = $this->helper->getDiff('now', null, true); return $this->translator->trans('mautic.core.date.ago', array('%days%' => $interval->days)); } }
/** * Get a lead's upcoming events. * * @param array $options * * @return array * * @throws \Doctrine\ORM\NoResultException * @throws \Doctrine\ORM\NonUniqueResultException */ public function getUpcomingEvents(array $options = null) { $leadIps = []; $query = $this->_em->getConnection()->createQueryBuilder(); $today = new DateTimeHelper(); $query->from(MAUTIC_TABLE_PREFIX . 'campaign_lead_event_log', 'll')->select('ll.event_id, ll.campaign_id, ll.trigger_date, ll.lead_id, e.name AS event_name, e.description AS event_description, c.name AS campaign_name, c.description AS campaign_description, CONCAT(CONCAT(l.firstname, \' \'), l.lastname) AS lead_name')->leftJoin('ll', MAUTIC_TABLE_PREFIX . 'campaign_events', 'e', 'e.id = ll.event_id')->leftJoin('ll', MAUTIC_TABLE_PREFIX . 'campaigns', 'c', 'c.id = e.campaign_id')->leftJoin('ll', MAUTIC_TABLE_PREFIX . 'leads', 'l', 'l.id = ll.lead_id')->where($query->expr()->gte('ll.trigger_date', ':today'))->setParameter('today', $today->toUtcString()); if (isset($options['lead'])) { /** @var \Mautic\CoreBundle\Entity\IpAddress $ip */ foreach ($options['lead']->getIpAddresses() as $ip) { $leadIps[] = $ip->getId(); } $query->andWhere('ll.lead_id = :leadId')->setParameter('leadId', $options['lead']->getId()); } if (isset($options['scheduled'])) { $query->andWhere('ll.is_scheduled = :scheduled')->setParameter('scheduled', $options['scheduled'], 'boolean'); } if (isset($options['eventType'])) { $query->andwhere('e.event_type = :eventType')->setParameter('eventType', $options['eventType']); } if (isset($options['type'])) { $query->andwhere('e.type = :type')->setParameter('type', $options['type']); } if (isset($options['limit'])) { $query->setMaxResults($options['limit']); } else { $query->setMaxResults(10); } $query->orderBy('ll.trigger_date'); if (!empty($ipIds)) { $query->orWhere('ll.ip_address IN (' . implode(',', $ipIds) . ')'); } if (!empty($options['canViewOthers']) && isset($this->currentUser)) { $query->andWhere('c.created_by = :userId')->setParameter('userId', $this->currentUser->getId()); } return $query->execute()->fetchAll(); }
/** * Returns date/time like Today, 10:00 AM * * @param $datetime * @param string $timezone * @param string $fromFormat * @param bool $forceDateForNonText If true, return as full date/time rather than "29 days ago" * * @return string */ public function toText($datetime, $timezone = 'local', $fromFormat = 'Y-m-d H:i:s', $forceDateForNonText = false) { if (empty($datetime)) { return ''; } $this->helper->setDateTime($datetime, $fromFormat, $timezone); $textDate = $this->helper->getTextDate(); $dt = $this->helper->getLocalDateTime(); if ($textDate) { return $this->translator->trans('mautic.core.date.' . $textDate, array('%time%' => $dt->format("g:i a"))); } else { $interval = $this->helper->getDiff('now', null, true); if ($interval->invert && !$forceDateForNonText) { // In the past return $this->translator->trans('mautic.core.date.ago', array('%days%' => $interval->days)); } else { // In the future return $this->toFullConcat($datetime, $timezone, $fromFormat); } } }
/** * Fetch the events * * @return array Events sorted by timestamp with most recent event first */ public function getEvents($returnGrouped = false) { $events = $this->events; $byDate = array(); // Group by date foreach ($events as $e) { if (!$e['timestamp'] instanceof \DateTime) { $dt = new DateTimeHelper($e['timestamp'], 'Y-m-d H:i:s', 'UTC'); $e['timestamp'] = $dt->getDateTime(); unset($dt); } $dateString = $e['timestamp']->format('Y-m-d H:i'); if (!isset($byDate[$dateString])) { $byDate[$dateString] = array(); } $byDate[$dateString][] = $e; } // Sort by date krsort($byDate); // Sort by certain event actions $order = array('lead.ipadded', 'page.hit', 'form.submitted', 'asset.download', 'lead.merge', 'lead.create', 'lead.identified'); $events = array(); foreach ($byDate as $date => $dateEvents) { usort($dateEvents, function ($a, $b) use($order) { if (!in_array($a['event'], $order) || !in_array($b['event'], $order)) { // No specific order so push to the end return 1; } $pos_a = array_search($a['event'], $order); $pos_b = array_search($b['event'], $order); return $pos_a - $pos_b; }); $byDate[$date] = $dateEvents; $events = array_merge($events, array_reverse($dateEvents)); } return $returnGrouped ? $byDate : $events; }
/** * @param FormBuilderInterface $builder * @param array $options */ public function buildForm(FormBuilderInterface $builder, array $options) { $builder->addEventSubscriber(new CleanFormSubscriber()); $builder->addEventSubscriber(new FormExitSubscriber('lead.lead', $options)); if (!$options['isShortForm']) { $transformer = new IdToEntityModelTransformer($this->factory->getEntityManager(), 'MauticUserBundle:User'); $builder->add($builder->create('owner', 'user_list', array('label' => 'mautic.lead.lead.field.owner', 'label_attr' => array('class' => 'control-label'), 'attr' => array('class' => 'form-control'), 'required' => false, 'multiple' => false))->addModelTransformer($transformer)); $imageChoices = array('gravatar' => 'Gravatar', 'custom' => 'mautic.lead.lead.field.custom_avatar'); $cache = $options['data']->getSocialCache(); if (count($cache)) { foreach ($cache as $key => $data) { $imageChoices[$key] = $key; } } $builder->add('preferred_profile_image', 'choice', array('choices' => $imageChoices, 'label' => 'mautic.lead.lead.field.preferred_profile', 'label_attr' => array('class' => 'control-label'), 'required' => true, 'multiple' => false, 'attr' => array('class' => 'form-control'))); $builder->add('custom_avatar', 'file', array('label' => false, 'label_attr' => array('class' => 'control-label'), 'required' => false, 'attr' => array('class' => 'form-control'), 'mapped' => false, 'constraints' => array(new File(array('mimeTypes' => array('image/gif', 'image/jpeg', 'image/png'), 'mimeTypesMessage' => 'mautic.lead.avatar.types_invalid'))))); } $fieldValues = !empty($options['data']) ? $options['data']->getFields() : array('filter' => array('isVisible' => true)); foreach ($options['fields'] as $field) { $attr = array('class' => 'form-control'); $properties = $field['properties']; $type = $field['type']; $required = $field['isRequired']; $alias = $field['alias']; $group = $field['group']; $value = isset($fieldValues[$group][$alias]['value']) ? $fieldValues[$group][$alias]['value'] : $field['defaultValue']; $constraints = array(); if ($required) { $constraints[] = new NotBlank(array('message' => 'mautic.lead.customfield.notblank')); } if ($type == 'number') { if (empty($properties['precision'])) { $properties['precision'] = null; } else { $properties['precision'] = (int) $properties['precision']; } $builder->add($alias, $type, array('required' => $required, 'label' => $field['label'], 'label_attr' => array('class' => 'control-label'), 'attr' => $attr, 'data' => isset($fieldValues[$group][$alias]['value']) ? (double) $fieldValues[$group][$alias]['value'] : (double) $field['defaultValue'], 'mapped' => false, 'constraints' => $constraints, 'precision' => $properties['precision'], 'rounding_mode' => (int) $properties['roundmode'])); } elseif (in_array($type, array('date', 'datetime', 'time'))) { $attr['data-toggle'] = $type; $opts = array('required' => $required, 'label' => $field['label'], 'label_attr' => array('class' => 'control-label'), 'widget' => 'single_text', 'attr' => $attr, 'mapped' => false, 'input' => 'string', 'html5' => false, 'constraints' => $constraints); $dtHelper = new DateTimeHelper($value, null, 'local'); if ($type == 'datetime') { $opts['model_timezone'] = 'UTC'; $opts['view_timezone'] = date_default_timezone_get(); $opts['format'] = 'yyyy-MM-dd HH:mm'; $opts['with_seconds'] = false; $opts['data'] = !empty($value) ? $dtHelper->toLocalString('Y-m-d H:i:s') : null; } elseif ($type == 'date') { $opts['data'] = !empty($value) ? $dtHelper->toLocalString('Y-m-d') : null; } else { $opts['data'] = !empty($value) ? $dtHelper->toLocalString('H:i:s') : null; } $builder->add($alias, $type, $opts); } elseif ($type == 'select' || $type == 'boolean') { $choices = array(); if ($type == 'select' && !empty($properties['list'])) { $list = explode('|', $properties['list']); foreach ($list as $l) { $l = trim($l); $choices[$l] = $l; } $expanded = false; } if ($type == 'boolean' && !empty($properties['yes']) && !empty($properties['no'])) { $expanded = true; $choices = array(1 => $properties['yes'], 0 => $properties['no']); $attr = array(); } if (!empty($choices)) { $builder->add($alias, 'choice', array('choices' => $choices, 'required' => $required, 'label' => $field['label'], 'label_attr' => array('class' => 'control-label'), 'data' => $type == 'boolean' ? (int) $value : $value, 'attr' => $attr, 'mapped' => false, 'multiple' => false, 'empty_value' => false, 'expanded' => $expanded, 'constraints' => $constraints)); } } elseif ($type == 'country' || $type == 'region' || $type == 'timezone') { if ($type == 'country') { $choices = FormFieldHelper::getCountryChoices(); } elseif ($type == 'region') { $choices = FormFieldHelper::getRegionChoices(); } else { $choices = FormFieldHelper::getTimezonesChoices(); } $builder->add($alias, 'choice', array('choices' => $choices, 'required' => $required, 'label' => $field['label'], 'label_attr' => array('class' => 'control-label'), 'data' => $value, 'attr' => array('class' => 'form-control', 'data-placeholder' => $field['label']), 'mapped' => false, 'multiple' => false, 'expanded' => false, 'constraints' => $constraints)); } else { if ($type == 'lookup') { $type = "text"; $attr['data-toggle'] = 'field-lookup'; $attr['data-target'] = $alias; if (!empty($properties['list'])) { $attr['data-options'] = $properties['list']; } } $builder->add($alias, $type, array('required' => $field['isRequired'], 'label' => $field['label'], 'label_attr' => array('class' => 'control-label'), 'attr' => $attr, 'data' => $value, 'mapped' => false, 'constraints' => $constraints)); } } $builder->add('tags', 'lead_tag', array('by_reference' => false, 'attr' => array('data-placeholder' => $this->factory->getTranslator()->trans('mautic.lead.tags.select_or_create'), 'data-no-results-text' => $this->factory->getTranslator()->trans('mautic.lead.tags.enter_to_create'), 'data-allow-add' => 'true', 'onchange' => 'Mautic.createLeadTag(this)'))); if (!$options['isShortForm']) { $builder->add('buttons', 'form_buttons'); } else { $builder->add('buttons', 'form_buttons', array('apply_text' => false, 'save_text' => 'mautic.core.form.save')); } if (!empty($options["action"])) { $builder->setAction($options["action"]); } }
/** * Get submission count by email by linking emails that have been associated with a page hit that has the * same tracking ID as a form submission tracking ID and thus assumed happened in the same session * * @param $emailId * @param \DateTime $fromDate * * @return mixed */ public function getSubmissionCountsByEmail($emailId, \DateTime $fromDate = null) { //link email to page hit tracking id to form submission tracking id $q = $this->_em->getConnection()->createQueryBuilder(); $q->select('count(distinct(s.tracking_id)) as count, e.id, e.subject as name, e.variant_sent_count as total')->from(MAUTIC_TABLE_PREFIX . 'form_submissions', 's')->join('s', MAUTIC_TABLE_PREFIX . 'page_hits', 'h', 's.tracking_id = h.tracking_id')->join('h', MAUTIC_TABLE_PREFIX . 'emails', 'e', 'h.email_id = e.id'); if (is_array($emailId)) { $q->where($q->expr()->in('e.id', $emailId))->groupBy('e.id, e.subject, e.variant_sent_count'); } else { $q->where($q->expr()->eq('e.id', ':id'))->setParameter('id', (int) $emailId); } if ($fromDate != null) { $dh = new DateTimeHelper($fromDate); $q->andWhere($q->expr()->gte('s.date_submitted', ':date'))->setParameter('date', $dh->toUtcString()); } $results = $q->execute()->fetchAll(); return $results; }
/** * Check the publish status of an entity based on publish up and down datetimes * * @return string early|expired|published|unpublished * @throws \BadMethodCallException */ public function getPublishStatus() { $dt = new DateTimeHelper(); $current = $dt->getLocalDateTime(); if (!$this->isPublished(false)) { return 'unpublished'; } $status = 'published'; if (method_exists($this, 'getPublishUp')) { $up = $this->getPublishUp(); if (!empty($up) && $current <= $up) { $status = 'pending'; } } if (method_exists($this, 'getPublishDown')) { $down = $this->getPublishDown(); if (!empty($down) && $current >= $down) { $status = 'expired'; } } return $status; }
/** * Updates lead's lastActive with now date/time * * @param integer $leadId */ public function updateLastActive($leadId) { $dt = new DateTimeHelper(); $fields = array('last_active' => $dt->toUtcString()); $this->_em->getConnection()->update(MAUTIC_TABLE_PREFIX . 'leads', $fields, array('id' => $leadId)); }
/** * {@inheritdoc} * * @param \Mautic\LeadBundle\Entity\Lead &$entity * @param $parameters * @param $form * @param string $action */ protected function preSaveEntity(&$entity, $form, $parameters, $action = 'edit') { //Since the request can be from 3rd party, check for an IP address if included if (isset($parameters['ipAddress'])) { $ip = $parameters['ipAddress']; unset($parameters['ipAddress']); $ipAddress = $this->factory->getIpAddress($ip); if (!$entity->getIpAddresses()->contains($ipAddress)) { $entity->addIpAddress($ipAddress); } } // Check for tag string if (isset($parameters['tags'])) { $this->model->modifyTags($entity, $parameters['tags']); unset($parameters['tags']); } if (isset($parameters['companies'])) { $this->model->modifyCompanies($entity, $parameters['companies']); unset($parameters['companies']); } // Check for lastActive date if (isset($parameters['lastActive'])) { $lastActive = new DateTimeHelper($parameters['lastActive']); $entity->setLastActive($lastActive->getDateTime()); } //set the custom field values //pull the data from the form in order to apply the form's formatting foreach ($form as $f) { $parameters[$f->getName()] = $f->getData(); } $this->model->setFieldValues($entity, $parameters, true); }
/** * Get sent counts based grouped by email Id * * @param array $emailIds * * @return array */ public function getSentCounts($emailIds = array(), \DateTime $fromDate = null) { $q = $this->_em->getConnection()->createQueryBuilder(); $q->select('e.email_id, count(e.id) as sentcount')->from(MAUTIC_TABLE_PREFIX . 'email_stats', 'e')->where($q->expr()->andX($q->expr()->in('e.email_id', $emailIds), $q->expr()->eq('e.is_failed', ':false')))->setParameter('false', false, 'boolean'); if ($fromDate !== null) { //make sure the date is UTC $dt = new DateTimeHelper($fromDate); $q->andWhere($q->expr()->gte('e.date_read', $q->expr()->literal($dt->toUtcString()))); } $q->groupBy('e.email_id'); //get a total number of sent emails first $results = $q->execute()->fetchAll(); $counts = array(); foreach ($results as $r) { $counts[$r['email_id']] = $r['sentcount']; } return $counts; }
private function getFormFields(FormBuilderInterface $builder, array $options, $object = 'lead') { $fieldValues = []; $isObject = false; if (!empty($options['data'])) { $isObject = is_object($options['data']); $fieldValues = $isObject ? $options['data']->getFields() : $options['data']; } $mapped = !$isObject; foreach ($options['fields'] as $field) { if ($field['isPublished'] === false || $field['object'] !== $object) { continue; } $attr = ['class' => 'form-control']; $properties = $field['properties']; $type = $field['type']; $required = $isObject ? $field['isRequired'] : false; $alias = $field['alias']; $group = $field['group']; if ($field['isUniqueIdentifer']) { $attr['data-unique-identifier'] = $field['alias']; } if ($isObject) { $value = isset($fieldValues[$group][$alias]['value']) ? $fieldValues[$group][$alias]['value'] : $field['defaultValue']; } else { $value = isset($fieldValues[$alias]) ? $fieldValues[$alias] : ''; } $constraints = []; if ($required && empty($options['ignore_required_constraints'])) { $constraints[] = new NotBlank(['message' => 'mautic.lead.customfield.notblank']); } switch ($type) { case 'number': if (empty($properties['precision'])) { $properties['precision'] = null; } else { $properties['precision'] = (int) $properties['precision']; } if ('' === $value) { // Prevent transform errors $value = null; } $builder->add($alias, $type, ['required' => $required, 'label' => $field['label'], 'label_attr' => ['class' => 'control-label'], 'attr' => $attr, 'data' => null !== $value ? (double) $value : $value, 'mapped' => $mapped, 'constraints' => $constraints, 'precision' => $properties['precision'], 'rounding_mode' => isset($properties['roundmode']) ? (int) $properties['roundmode'] : 0]); break; case 'date': case 'datetime': case 'time': $attr['data-toggle'] = $type; $opts = ['required' => $required, 'label' => $field['label'], 'label_attr' => ['class' => 'control-label'], 'widget' => 'single_text', 'attr' => $attr, 'mapped' => $mapped, 'input' => 'string', 'html5' => false, 'constraints' => $constraints]; if ($value) { try { $dtHelper = new DateTimeHelper($value, null, 'local'); } catch (\Exception $e) { // Rather return empty value than break the page $value = null; } } if ($type == 'datetime') { $opts['model_timezone'] = 'UTC'; $opts['view_timezone'] = date_default_timezone_get(); $opts['format'] = 'yyyy-MM-dd HH:mm'; $opts['with_seconds'] = false; $opts['data'] = !empty($value) ? $dtHelper->toLocalString('Y-m-d H:i:s') : null; } elseif ($type == 'date') { $opts['data'] = !empty($value) ? $dtHelper->toLocalString('Y-m-d') : null; } else { $opts['data'] = !empty($value) ? $dtHelper->toLocalString('H:i:s') : null; } $builder->add($alias, $type, $opts); break; case 'select': case 'multiselect': case 'boolean': $typeProperties = ['required' => $required, 'label' => $field['label'], 'label_attr' => ['class' => 'control-label'], 'attr' => $attr, 'mapped' => $mapped, 'multiple' => false, 'constraints' => $constraints]; $choiceType = 'choice'; $emptyValue = ''; if (in_array($type, ['select', 'multiselect']) && !empty($properties['list'])) { $typeProperties['choices'] = FormFieldHelper::parseList($properties['list']); $typeProperties['expanded'] = false; $typeProperties['multiple'] = 'multiselect' === $type; } if ($type == 'boolean' && !empty($properties['yes']) && !empty($properties['no'])) { $choiceType = 'yesno_button_group'; $typeProperties['expanded'] = true; $typeProperties['yes_label'] = $properties['yes']; $typeProperties['no_label'] = $properties['no']; $typeProperties['attr'] = []; $emptyValue = ' x '; if ($value !== '' && $value !== null) { $value = (int) $value; } } $typeProperties['data'] = $type === 'multiselect' ? FormFieldHelper::parseList($value) : $value; $typeProperties['empty_value'] = $emptyValue; $builder->add($alias, $choiceType, $typeProperties); break; case 'country': case 'region': case 'timezone': case 'locale': switch ($type) { case 'country': $choices = FormFieldHelper::getCountryChoices(); break; case 'region': $choices = FormFieldHelper::getRegionChoices(); break; case 'timezone': $choices = FormFieldHelper::getTimezonesChoices(); break; case 'locale': $choices = FormFieldHelper::getLocaleChoices(); break; } $builder->add($alias, 'choice', ['choices' => $choices, 'required' => $required, 'label' => $field['label'], 'label_attr' => ['class' => 'control-label'], 'data' => $value, 'attr' => ['class' => 'form-control', 'data-placeholder' => $field['label']], 'mapped' => $mapped, 'multiple' => false, 'expanded' => false, 'constraints' => $constraints]); break; default: if ($type == 'lookup') { $type = 'text'; $attr['data-toggle'] = 'field-lookup'; $attr['data-action'] = 'lead:fieldList'; $attr['data-target'] = $alias; if (!empty($properties['list'])) { $attr['data-options'] = $properties['list']; } } $builder->add($alias, $type, ['required' => $field['isRequired'], 'label' => $field['label'], 'label_attr' => ['class' => 'control-label'], 'attr' => $attr, 'data' => $value, 'mapped' => $mapped, 'constraints' => $constraints]); break; } } }
/** * Update a hit with the the time the user left * * @param int $lastHitId */ public function updateHitDateLeft($lastHitId) { $dt = new DateTimeHelper(); $q = $this->_em->getConnection()->createQueryBuilder(); $q->update(MAUTIC_TABLE_PREFIX . 'page_hits')->set('date_left', ':datetime')->where('id = ' . (int) $lastHitId)->setParameter('datetime', $dt->toUtcString()); $q->execute(); }
/** * Fetch the events. * * @return array Events sorted by timestamp with most recent event first */ public function getEvents() { if (empty($this->events)) { return []; } $events = call_user_func_array('array_merge', $this->events); foreach ($events as &$e) { if (!$e['timestamp'] instanceof \DateTime) { $dt = new DateTimeHelper($e['timestamp'], 'Y-m-d H:i:s', 'UTC'); $e['timestamp'] = $dt->getDateTime(); unset($dt); } } if (!empty($this->orderBy)) { usort($events, function ($a, $b) { switch ($this->orderBy[0]) { case 'eventLabel': $aLabel = ''; if (isset($a['eventLabel'])) { $aLabel = is_array($a['eventLabel']) ? $a['eventLabel']['label'] : $a['eventLabel']; } $bLabel = ''; if (isset($b['eventLabel'])) { $bLabel = is_array($b['eventLabel']) ? $b['eventLabel']['label'] : $b['eventLabel']; } return strnatcmp($aLabel, $bLabel); case 'timestamp': if ($a['timestamp'] == $b['timestamp']) { $aPriority = isset($a['eventPriority']) ? (int) $a['eventPriority'] : 0; $bPriority = isset($b['eventPriority']) ? (int) $b['eventPriority'] : 0; return $aPriority - $bPriority; } return $a['timestamp'] < $b['timestamp'] ? -1 : 1; } }); if ($this->orderBy[1] == 'DESC') { $events = array_reverse($events); } } return $events; }
/** * Returns a andX Expr() that takes into account isPublished, publishUp and publishDown dates * The Expr() sets a :now and :true parameter that must be set in the calling function * * @param $q * @param null $alias * @param bool $setNowParameter * @param bool $setTrueParameter * * @return mixed */ public function getPublishedByDateExpression($q, $alias = null, $setNowParameter = true, $setTrueParameter = true) { $isORM = $q instanceof QueryBuilder; if ($alias === null) { $alias = $this->getTableAlias(); } if ($setNowParameter) { $now = new \DateTime(); if (!$isORM) { $dtHelper = new DateTimeHelper($now); $now = $dtHelper->toUtcString(); } $q->setParameter('now', $now); } if ($setTrueParameter) { $q->setParameter('true', true, 'boolean'); } if ($isORM) { $pub = 'isPublished'; $pubUp = 'publishUp'; $pubDown = 'publishDown'; } else { $pub = 'is_published'; $pubUp = 'publish_up'; $pubDown = 'publish_down'; } $expr = $q->expr()->andX($q->expr()->eq("{$alias}.{$pub}", ':true'), $q->expr()->orX($q->expr()->isNull("{$alias}.{$pubUp}"), $q->expr()->lte("{$alias}.{$pubUp}", ':now')), $q->expr()->orX($q->expr()->isNull("{$alias}.{$pubDown}"), $q->expr()->gte("{$alias}.{$pubDown}", ':now'))); return $expr; }
/** * @param $filters * @param $parameters * @param \Doctrine\DBAL\Query\QueryBuilder $q * @param bool|false $not * @param null|int $leadId * * @return \Doctrine\DBAL\Query\Expression\CompositeExpression */ public function getListFilterExpr($filters, &$parameters, QueryBuilder $q, $not = false, $leadId = null) { static $leadTable; if (!count($filters)) { return $q->expr()->andX(); } // Get table columns if (null === $leadTable) { $schema = $this->_em->getConnection()->getSchemaManager(); /** @var \Doctrine\DBAL\Schema\Column[] $leadTable */ $leadTable = $schema->listTableColumns(MAUTIC_TABLE_PREFIX . 'leads'); } $options = $this->getFilterExpressionFunctions(); $groups = array(); $groupExpr = $q->expr()->andX(); foreach ($filters as $k => $details) { $column = isset($leadTable[$details['field']]) ? $leadTable[$details['field']] : false; //DBAL does not have a not() function so we have to use the opposite $func = !$not ? $options[$details['operator']]['expr'] : $options[$details['operator']]['negate_expr']; $field = "l.{$details['field']}"; // Format the field based on platform specific functions that DBAL doesn't support natively if ($column) { $formatter = AbstractFormatter::createFormatter($this->_em->getConnection()); $columnType = $column->getType(); switch ($details['type']) { case 'datetime': if (!$columnType instanceof UTCDateTimeType) { $field = $formatter->toDateTime($field); } break; case 'date': if (!$columnType instanceof DateType && !$columnType instanceof UTCDateTimeType) { $field = $formatter->toDate($field); } break; case 'time': if (!$columnType instanceof TimeType && !$columnType instanceof UTCDateTimeType) { $field = $formatter->toTime($field); } break; case 'number': if (!$columnType instanceof IntegerType && !$columnType instanceof FloatType) { $field = $formatter->toNumeric($field); } break; } } //the next one will determine the group $glue = isset($filters[$k + 1]) ? $filters[$k + 1]['glue'] : $details['glue']; if ($glue == "or" || $details['glue'] == 'or') { // Create a new group of andX expressions if ($groupExpr->count()) { $groups[] = $groupExpr; $groupExpr = $q->expr()->andX(); } } $parameter = $this->generateRandomParameterName(); $exprParameter = ":{$parameter}"; $ignoreAutoFilter = false; // Special handling of relative date strings if ($details['type'] == 'datetime' || $details['type'] == 'date') { $relativeDateStrings = $this->getRelativeDateStrings(); // Check if the column type is a date/time stamp $isTimestamp = $columnType instanceof UTCDateTimeType || $details['type'] == 'datetime'; $getDate = function (&$string) use($isTimestamp, $relativeDateStrings, &$details, &$func, $not) { $key = array_search($string, $relativeDateStrings); $dtHelper = new DateTimeHelper('midnight today', null, 'local'); $requiresBetween = in_array($func, array('eq', 'neq')) && $isTimestamp; $timeframe = str_replace('mautic.lead.list.', '', $key); $modifier = false; $isRelative = true; switch ($timeframe) { case 'today': case 'tomorrow': case 'yesterday': if ($timeframe == 'yesterday') { $dtHelper->modify('-1 day'); } elseif ($timeframe == 'tomorrow') { $dtHelper->modify('+1 day'); } // Today = 2015-08-28 00:00:00 if ($requiresBetween) { // eq: // field >= 2015-08-28 00:00:00 // field < 2015-08-29 00:00:00 // neq: // field < 2015-08-28 00:00:00 // field >= 2015-08-29 00:00:00 $modifier = '+1 day'; } else { // lt: // field < 2015-08-28 00:00:00 // gt: // field > 2015-08-28 23:59:59 // lte: // field <= 2015-08-28 23:59:59 // gte: // field >= 2015-08-28 00:00:00 if (in_array($func, array('gt', 'lte'))) { $modifier = '+1 day -1 second'; } } break; case 'week_last': case 'week_next': case 'week_this': $interval = str_replace('week_', '', $timeframe); $dtHelper->setDateTime('midnight monday ' . $interval . ' week', null); // This week: Monday 2015-08-24 00:00:00 if ($requiresBetween) { // eq: // field >= Mon 2015-08-24 00:00:00 // field < Mon 2015-08-31 00:00:00 // neq: // field < Mon 2015-08-24 00:00:00 // field >= Mon 2015-08-31 00:00:00 $modifier = '+1 week'; } else { // lt: // field < Mon 2015-08-24 00:00:00 // gt: // field > Sun 2015-08-30 23:59:59 // lte: // field <= Sun 2015-08-30 23:59:59 // gte: // field >= Mon 2015-08-24 00:00:00 if (in_array($func, array('gt', 'lte'))) { $modifier = '+1 week -1 second'; } } break; case 'month_last': case 'month_next': case 'month_this': $interval = substr($key, -4); $dtHelper->setDateTime('midnight first day of ' . $interval . ' month', null); // This month: 2015-08-01 00:00:00 if ($requiresBetween) { // eq: // field >= 2015-08-01 00:00:00 // field < 2015-09:01 00:00:00 // neq: // field < 2015-08-01 00:00:00 // field >= 2016-09-01 00:00:00 $modifier = '+1 month'; } else { // lt: // field < 2015-08-01 00:00:00 // gt: // field > 2015-08-31 23:59:59 // lte: // field <= 2015-08-31 23:59:59 // gte: // field >= 2015-08-01 00:00:00 if (in_array($func, array('gt', 'lte'))) { $modifier = '+1 month -1 second'; } } break; case 'year_last': case 'year_next': case 'year_this': $interval = substr($key, -4); $dtHelper->setDateTime('midnight first day of ' . $interval . ' year', null); // This year: 2015-01-01 00:00:00 if ($requiresBetween) { // eq: // field >= 2015-01-01 00:00:00 // field < 2016-01-01 00:00:00 // neq: // field < 2015-01-01 00:00:00 // field >= 2016-01-01 00:00:00 $modifier = '+1 year'; } else { // lt: // field < 2015-01-01 00:00:00 // gt: // field > 2015-12-31 23:59:59 // lte: // field <= 2015-12-31 23:59:59 // gte: // field >= 2015-01-01 00:00:00 if (in_array($func, array('gt', 'lte'))) { $modifier = '+1 year -1 second'; } } break; default: $isRelative = false; break; } if ($isRelative) { if ($requiresBetween) { $startWith = $isTimestamp ? $dtHelper->toUtcString('Y-m-d H:i:s') : $dtHelper->toUtcString('Y-m-d'); $dtHelper->modify($modifier); $endWith = $isTimestamp ? $dtHelper->toUtcString('Y-m-d H:i:s') : $dtHelper->toUtcString('Y-m-d'); // Use a between statement $func = $func == 'neq' ? 'notBetween' : 'between'; $details['filter'] = array($startWith, $endWith); } else { if ($modifier) { $dtHelper->modify($modifier); } $details['filter'] = $isTimestamp ? $dtHelper->toUtcString('Y-m-d H:i:s') : $dtHelper->toUtcString('Y-m-d'); } } }; if (is_array($details['filter'])) { foreach ($details['filter'] as &$filterValue) { $getDate($filterValue); } } else { $getDate($details['filter']); } } // Generate a unique alias $alias = $this->generateRandomParameterName(); switch ($details['field']) { case 'dnc_bounced': case 'dnc_unsubscribed': // Special handling of do not email $column = str_replace('dnc_', '', $details['field']); $func = $func == 'eq' && $details['filter'] || $func == 'neq' && !$details['filter'] ? 'EXISTS' : 'NOT EXISTS'; $subqb = $this->_em->getConnection()->createQueryBuilder()->select('null')->from(MAUTIC_TABLE_PREFIX . 'email_donotemail', $alias)->where($q->expr()->andX($q->expr()->eq($alias . '.' . $column, $exprParameter), $q->expr()->eq($alias . '.lead_id', 'l.id'))); // Specific lead if (!empty($leadId)) { $subqb->andWhere($subqb->expr()->eq($alias . '.lead_id', $leadId)); } $groupExpr->add(sprintf('%s (%s)', $func, $subqb->getSQL())); // Filter will always be true and differentiated via EXISTS/NOT EXISTS $details['filter'] = true; break; case 'leadlist': case 'tags': // Special handling of lead lists and tags $func = in_array($func, array('eq', 'in')) ? 'EXISTS' : 'NOT EXISTS'; if ($details['field'] == 'leadlist') { $table = 'lead_lists_leads'; $column = 'leadlist_id'; } else { $table = 'lead_tags_xref'; $column = 'tag_id'; } // DBAL requires an array for in() $ignoreAutoFilter = true; foreach ($details['filter'] as &$value) { $value = (int) $value; } $subExpr = $q->expr()->andX($q->expr()->in(sprintf('%s.%s', $alias, $column), $details['filter']), $q->expr()->eq($alias . '.lead_id', 'l.id')); $subqb = $this->_em->getConnection()->createQueryBuilder()->select('null')->from(MAUTIC_TABLE_PREFIX . $table, $alias); // Specific lead if (!empty($leadId)) { $subExpr->add($subqb->expr()->eq($alias . '.lead_id', $leadId)); } if ($table == 'lead_lists_leads') { $falseParameter = $this->generateRandomParameterName(); $subExpr->add($subqb->expr()->eq($alias . '.manually_removed', ":{$falseParameter}")); $parameters[$falseParameter] = false; } $subqb->where($subExpr); $groupExpr->add(sprintf('%s (%s)', $func, $subqb->getSQL())); break; default: switch ($func) { case 'in': case 'notIn': foreach ($details['filter'] as &$value) { $value = $q->expr()->literal(InputHelper::clean($value)); } $groupExpr->add($q->expr()->{$func}($field, $details['filter'])); $ignoreAutoFilter = true; break; case 'between': case 'notBetween': // Filter should be saved with double || to separate options $parameter2 = $this->generateRandomParameterName(); $parameters[$parameter] = $details['filter'][0]; $parameters[$parameter2] = $details['filter'][1]; $exprParameter2 = ":{$parameter2}"; $ignoreAutoFilter = true; if ($func == 'between') { $groupExpr->add($q->expr()->andX($q->expr()->gte($field, $exprParameter), $q->expr()->lt($field, $exprParameter2))); } else { $groupExpr->add($q->expr()->andX($q->expr()->lt($field, $exprParameter), $q->expr()->gte($field, $exprParameter2))); } break; case 'notEmpty': $groupExpr->add($q->expr()->andX($q->expr()->isNotNull($field), $q->expr()->neq($field, $q->expr()->literal('')))); break; case 'empty': $groupExpr->add($q->expr()->orX($q->expr()->isNull($field), $q->expr()->eq($field, $q->expr()->literal('')))); break; case 'neq': $groupExpr->add($q->expr()->orX($q->expr()->isNull($field), $q->expr()->neq($field, $exprParameter))); break; case 'like': case 'notLike': if (strpos($details['filter'], '%') === false) { $details['filter'] = '%' . $details['filter'] . '%'; } default: $groupExpr->add($q->expr()->{$func}($field, $exprParameter)); break; } } if (!$ignoreAutoFilter) { if (!is_array($details['filter'])) { switch ($details['type']) { case 'number': $details['filter'] = (double) $details['filter']; break; case 'boolean': $details['filter'] = (bool) $details['filter']; break; } } $parameters[$parameter] = $details['filter']; } } // Get the last of the filters if ($groupExpr->count()) { $groups[] = $groupExpr; } if (count($groups) === 1) { // Only one andX expression $expr = $groups[0]; } else { // Sets of expressions grouped by OR $orX = $q->expr()->orX(); $orX->addMultiple($groups); // Wrap in a andX for other functions to append $expr = $q->expr()->andX($orX); } return $expr; }