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

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

 /**
  * 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 {
//.........这里部分代码省略.........

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