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