/**
* @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);
}
}
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;
}
/**
* 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;
}
/**
* {@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;
}
/**
* 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;
}
/**
* @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();
}
/**
* @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;
}
/**
* Marks messages as read
*
* @param $toUserId
* @param $fromUserId
* @param null $upToId
*/
public function markRead($toUserId, $fromUserId, $upToId = 0)
{
$now = new DateTimeHelper();
$q = $this->_em->getConnection()->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'chats')->set('is_read', ':true')->setParameter('true', true, 'boolean')->set('date_read', ':readDate')->where($q->expr()->andX($q->expr()->eq('from_user', ':from'), $q->expr()->eq('to_user', ':to'), $q->expr()->lte('id', ':id')))->setParameter('readDate', $now->toUtcString())->setParameter('from', $fromUserId)->setParameter('to', $toUserId)->setParameter('id', $upToId)->execute();
}
/**
* @param string|Stat $stat
* @param $request
* @param bool $viaBrowser
*/
public function hitEmail($stat, $request, $viaBrowser = false)
{
if (!$stat instanceof Stat) {
$stat = $this->getEmailStatus($stat);
}
if (!$stat) {
return;
}
$email = $stat->getEmail();
if ((int) $stat->isRead()) {
if ($viaBrowser && !$stat->getViewedInBrowser()) {
//opened via browser so note it
$stat->setViewedInBrowser($viaBrowser);
}
}
$readDateTime = new DateTimeHelper();
$stat->setLastOpened($readDateTime->getDateTime());
$lead = $stat->getLead();
if ($lead !== null) {
// Set the lead as current lead
$this->leadModel->setCurrentLead($lead);
}
$firstTime = false;
if (!$stat->getIsRead()) {
$firstTime = true;
$stat->setIsRead(true);
$stat->setDateRead($readDateTime->getDateTime());
// Only up counts if associated with both an email and lead
if ($email && $lead) {
try {
$this->getRepository()->upCount($email->getId(), 'read', 1, $email->isVariant());
} catch (\Exception $exception) {
error_log($exception);
}
}
}
if ($viaBrowser) {
$stat->setViewedInBrowser($viaBrowser);
}
$stat->addOpenDetails(['datetime' => $readDateTime->toUtcString(), 'useragent' => $request->server->get('HTTP_USER_AGENT'), 'inBrowser' => $viaBrowser]);
//check for existing IP
$ipAddress = $this->ipLookupHelper->getIpAddress();
$stat->setIpAddress($ipAddress);
if ($this->dispatcher->hasListeners(EmailEvents::EMAIL_ON_OPEN)) {
$event = new EmailOpenEvent($stat, $request, $firstTime);
$this->dispatcher->dispatch(EmailEvents::EMAIL_ON_OPEN, $event);
}
//device granularity
$dd = new DeviceDetector($request->server->get('HTTP_USER_AGENT'));
$dd->parse();
$deviceRepo = $this->leadModel->getDeviceRepository();
$emailOpenDevice = $deviceRepo->getDevice(null, $lead, $dd->getDeviceName(), $dd->getBrand(), $dd->getModel());
if (empty($emailOpenDevice)) {
$emailOpenDevice = new LeadDevice();
$emailOpenDevice->setClientInfo($dd->getClient());
$emailOpenDevice->setDevice($dd->getDeviceName());
$emailOpenDevice->setDeviceBrand($dd->getBrand());
$emailOpenDevice->setDeviceModel($dd->getModel());
$emailOpenDevice->setDeviceOs($dd->getOs());
$emailOpenDevice->setDateOpen($readDateTime->toUtcString());
$emailOpenDevice->setLead($lead);
try {
$this->em->persist($emailOpenDevice);
$this->em->flush($emailOpenDevice);
} catch (\Exception $exception) {
if (MAUTIC_ENV === 'dev') {
throw $exception;
} else {
$this->logger->addError($exception->getMessage(), ['exception' => $exception]);
}
}
} else {
$emailOpenDevice = $deviceRepo->getEntity($emailOpenDevice['id']);
}
if ($email) {
$this->em->persist($email);
$this->em->flush($email);
}
if (isset($emailOpenDevice) and is_object($emailOpenDevice)) {
$emailOpenStat = new StatDevice();
$emailOpenStat->setIpAddress($ipAddress);
$emailOpenStat->setDevice($emailOpenDevice);
$emailOpenStat->setDateOpened($readDateTime->toUtcString());
$emailOpenStat->setStat($stat);
$this->em->persist($emailOpenStat);
$this->em->flush($emailOpenStat);
}
$this->em->persist($stat);
$this->em->flush();
}
/**
* @param $user
*/
public function setLastActive($user)
{
$now = new DateTimeHelper();
$conn = $this->_em->getConnection();
$conn->update(MAUTIC_TABLE_PREFIX . 'users', array('last_active' => $now->toUtcString()), array('id' => (int) $user->getId()));
}
/**
* @param Message $message
* @param bool|false $allowBounce
* @param bool|false $allowUnsubscribe
*
* @return bool
*/
public function analyzeMessage(Message $message, $allowBounce = false, $allowUnsubscribe = false)
{
$dtHelper = new DateTimeHelper();
// Assume is an unsubscribe
$isUnsubscribe = $allowUnsubscribe;
$isBounce = false;
$toEmail = reset($message->to);
// Check for bounce emails via + notation if applicable
foreach ($message->to as $to => $name) {
if (strpos($to, '+bounce') !== false) {
$isBounce = true;
$isUnsubscribe = false;
$toEmail = $to;
break;
} elseif (strpos($to, '+unsubscribe')) {
$isBounce = false;
$isUnsubscribe = true;
$toEmail = $to;
break;
}
}
$this->logger->debug("Analyzing message to {$message->toString}");
// If message from Amazon SNS collect bounces and complaints
if ($message->fromAddress == '*****@*****.**') {
$message = json_decode(strtok($message->textPlain, "\n"), true);
if ($message['notificationType'] == 'Bounce') {
$isBounce = true;
$isUnsubscribe = false;
$toEmail = $message['mail']['source'];
$amazonEmail = $message['bounce']['bouncedRecipients'][0]['emailAddress'];
} elseif ($message['notificationType'] == 'Complaint') {
$isBounce = false;
$isUnsubscribe = true;
$toEmail = $message['mail']['source'];
$amazonEmail = $message['complaint']['complainedRecipients'][0]['emailAddress'];
}
}
// Parse the to email if applicable
if (preg_match('#^(.*?)\\+(.*?)@(.*?)$#', $toEmail, $parts)) {
if (strstr($parts[2], '_')) {
// Has an ID hash so use it to find the lead
list($ignore, $hashId) = explode('_', $parts[2]);
}
}
$messageDetails = array();
if ($allowBounce) {
// If message from Amazon SNS fill details and don't process further
if (isset($amazonEmail)) {
$messageDetails['email'] = $amazonEmail;
$messageDetails['rule_cat'] = 'unknown';
$messageDetails['rule_no'] = '0013';
$messageDetails['bounce_type'] = 'hard';
$messageDetails['remove'] = 1;
} else {
if (!empty($message->dsnReport)) {
// Parse the bounce
$dsnMessage = $message->dsnMessage ? $message->dsnMessage : $message->textPlain;
$dsnReport = $message->dsnReport;
$this->logger->addDebug('Delivery report found in message.');
// Try parsing the report
$messageDetails = $this->parseDsn($dsnMessage, $dsnReport);
}
if (empty($messageDetails['email']) || $messageDetails['rule_cat'] == 'unrecognized') {
// Check for the X-Failed-Recipients header
$bouncedEmail = isset($message->xHeaders['x-failed-recipients']) ? $message->xHeaders['x-failed-recipients'] : null;
if ($bouncedEmail) {
// Definitely a bounced email but need to find the reason
$this->logger->debug('Email found through x-failed-recipients header but need to search for a reason.');
} else {
$this->logger->debug('Bounce email or reason not found so attempting to parse the body.');
}
// Let's try parsing through the body parser
$messageDetails = $this->parseBody($message->textPlain, $bouncedEmail);
}
if (!$isBounce && !empty($messageDetails['email'])) {
// Bounce was found in message content
$isBounce = true;
$isUnsubscribe = false;
}
}
}
if (!$isBounce && !$isUnsubscribe) {
$this->logger->debug('No reason found to process.');
return false;
}
// Search for the lead
$stat = $leadId = $leadEmail = $emailId = null;
if (!empty($hashId)) {
$q = $this->db->createQueryBuilder();
// Search by hashId
$q->select('*')->from(MAUTIC_TABLE_PREFIX . 'email_stats', 's')->where($q->expr()->eq('s.tracking_hash', ':hash'))->setParameter('hash', $hashId);
$results = $q->execute()->fetchAll();
if (count($results)) {
$stat = $results[0];
$leadId = $stat['lead_id'];
$leadEmail = $stat['email_address'];
$emailId = $stat['email_id'];
$this->logger->debug('Stat found with ID# ' . $stat['id']);
}
unset($results);
}
if (!$leadId) {
if ($isBounce) {
if (!empty($messageDetails['email'])) {
$leadEmail = $messageDetails['email'];
} else {
// Email not found for the bounce so abort
$this->logger->error('BOUNCE ERROR: A lead could be found from the bounce email. From: ' . $message->fromAddress . '; To: ' . $message->toString . '; Subject: ' . $message->subject);
return false;
}
} else {
$leadEmail = $message->fromAddress;
$this->logger->debug('From address used: ' . $leadEmail);
}
// Search by first part and domain of email to find cases like me+mautic@domain.com
list($email, $domain) = explode('@', strtolower($leadEmail));
$email = $email . '%';
$domain = '%@' . $domain;
$q = $this->db->createQueryBuilder();
$q->select('l.id, l.email')->from(MAUTIC_TABLE_PREFIX . 'leads', 'l')->where($q->expr()->orX($q->expr()->eq('LOWER(l.email)', ':leademail'), $q->expr()->andX($q->expr()->like('LOWER(l.email)', ':email'), $q->expr()->like('LOWER(l.email)', ':domain'))))->setParameter('leademail', strtolower($leadEmail))->setParameter('email', strtolower($email))->setParameter('domain', strtolower($domain));
$foundLeads = $q->execute()->fetchAll();
foreach ($foundLeads as $lead) {
if (strtolower($lead['email']) == strtolower($leadEmail)) {
// Exact match
$leadId = $lead['id'];
break;
} elseif (strpos($lead['email'], '+') === false) {
// Not a plus style email so not a match
break;
}
if (preg_match('#^(.*?)\\+(.*?)@(.*?)$#', $lead['email'], $parts)) {
$email = $parts[1] . '@' . $parts[3];
if (strtolower($email) == strtolower($leadEmail)) {
$this->logger->debug('Lead found through + alias: ' . $lead['email']);
$leadId = $lead['id'];
$leadEmail = $lead['email'];
}
}
}
$this->logger->debug('Lead ID: ' . ($leadId ? $leadId : 'not found'));
}
if (!$leadId) {
// A lead still could not be found
return false;
}
// Set message details for unsubscribe requests
if ($isUnsubscribe) {
$messageDetails = array('remove' => true, 'email' => $leadEmail, 'rule_cat' => 'unsubscribed', 'rule_no' => '0000');
}
if ($isBounce && $stat) {
// Update the stat with some details
$openDetails = unserialize($stat['open_details']);
if (!is_array($openDetails)) {
$openDetails = array();
}
$openDetails['bounces'][] = array('datetime' => $dtHelper->toUtcString(), 'reason' => $messageDetails['rule_cat'], 'code' => $messageDetails['rule_no'], 'type' => $messageDetails['bounce_type'] === false ? 'unknown' : $messageDetails['bounce_type']);
$this->db->update(MAUTIC_TABLE_PREFIX . 'email_stats', array('open_details' => serialize($openDetails), 'retry_count' => ++$stat['retry_count'], 'is_failed' => $messageDetails['remove'] || $stat['retry_count'] == 5 ? 1 : 0), array('id' => $stat['id']));
$this->logger->debug('Stat updated');
}
// Is this a hard bounce or AN unsubscribe?
if ($messageDetails['remove'] || $stat && $stat['retry_count'] >= 5) {
$this->logger->debug('Adding DNC entry for ' . $leadEmail);
// Check for an existing DNC entry
$q = $this->db->createQueryBuilder();
$q->select('dnc.id')->from(MAUTIC_TABLE_PREFIX . 'lead_donotcontact', 'dnc')->where('dnc.channel = "email"')->where($q->expr()->eq('dnc.lead_id', ':leadId'))->setParameter('leadId', $leadId);
try {
$exists = $q->execute()->fetchColumn();
} catch (\Exception $e) {
$this->logger->error($e->getMessage());
}
if (!empty($exists)) {
$this->logger->debug('A DNC entry already exists for ' . $leadEmail);
} else {
$this->logger->debug('Existing not found so creating a new one.');
// Create a DNC entry
try {
$this->db->insert(MAUTIC_TABLE_PREFIX . 'lead_donotcontact', array('lead_id' => $leadId, 'channel' => 'email', 'channel_id' => $emailId, 'date_added' => $dtHelper->toUtcString(), 'reason' => $isUnsubscribe ? DoNotContact::UNSUBSCRIBED : DoNotContact::BOUNCED, 'comments' => $this->factory->getTranslator()->trans('mautic.email.bounce.reason.' . $messageDetails['rule_cat'])));
} catch (\Exception $e) {
$this->logger->error($e->getMessage());
}
}
}
$this->logger->debug(print_r($messageDetails, true));
}
/**
* 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;
}
/**
* 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;
}
/**
* @param Schema $schema
*/
public function postUp(Schema $schema)
{
$em = $this->factory->getEntityManager();
// Migrate asset download messages to form message
$q = $this->connection->createQueryBuilder();
$q->select('fa.properties, fa.form_id')->from(MAUTIC_TABLE_PREFIX . 'form_actions', 'fa')->where($q->expr()->eq('fa.type', $q->expr()->literal('asset.download')));
$results = $q->execute()->fetchAll();
foreach ($results as $r) {
$properties = unserialize($r['properties']);
if (is_array($properties) && !empty($properties['message'])) {
$this->connection->update(MAUTIC_TABLE_PREFIX . 'forms', array('post_action' => 'message', 'post_action_property' => $properties['message']), array('id' => $r['form_id']));
}
}
// Set save_result to true for most form fields
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'form_fields')->set('save_result', ':true')->where($q->expr()->andX($q->expr()->neq('type', $q->expr()->literal('button')), $q->expr()->neq('type', $q->expr()->literal('freetext')), $q->expr()->neq('type', $q->expr()->literal('captcha'))))->setParameter('true', true, 'boolean')->execute();
// Find and migrate the lead.create actions
// Get a list of lead field entities
$results = $this->connection->createQueryBuilder()->select('f.id, f.alias')->from(MAUTIC_TABLE_PREFIX . 'lead_fields', 'f')->execute()->fetchAll();
$fields = array();
foreach ($results as $field) {
$fields[$field['id']] = $field['alias'];
}
unset($results);
// Get all the actions that are lead.create
$q = $this->connection->createQueryBuilder();
$actions = $q->select('a.id, a.properties, a.form_id, a.action_order, f.created_by, f.created_by_user')->from(MAUTIC_TABLE_PREFIX . 'form_actions', 'a')->join('a', MAUTIC_TABLE_PREFIX . 'forms', 'f', 'a.form_id = f.id')->where($q->expr()->eq('a.type', $q->expr()->literal('lead.create')))->execute()->fetchAll();
$formFieldMatches = array();
$actionEntities = array();
$deleteActions = array();
foreach ($actions as $action) {
try {
$properties = unserialize($action['properties']);
foreach ($properties['mappedFields'] as $leadFieldId => $formFieldId) {
if (!empty($formFieldId)) {
$formFieldMatches[$leadFieldId][] = $formFieldId;
}
}
if (!empty($properties['points'])) {
// Create a new point action
$formAction = new Action();
$formAction->setType('lead.pointschange');
$formAction->setName('Migrated');
$formAction->setDescription('<p>Migrated during 1.1.0 upgrade. The Create/Update Lead form submit action is now obsolete.</p>');
$formAction->setForm($em->getReference('MauticFormBundle:Form', $action['form_id']));
$formAction->setOrder($action['action_order']);
$formAction->setProperties(array('operator' => 'plus', 'points' => $properties['points']));
$actionEntities[] = $formAction;
unset($formAction);
}
$deleteActions[] = $action['id'];
} catch (\Exception $e) {
}
}
if (!empty($actionEntities)) {
$this->factory->getModel('point')->getRepository()->saveEntities($actionEntities);
$em->clear('Mautic\\FormBundle\\Entity\\Action');
}
foreach ($formFieldMatches as $leadFieldId => $formFieldIds) {
if (!isset($fields[$leadFieldId])) {
continue;
}
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'form_fields')->set('lead_field', $q->expr()->literal($fields[$leadFieldId]))->where($q->expr()->in('id', $formFieldIds))->execute();
}
if (!empty($deleteActions)) {
$q = $this->connection->createQueryBuilder();
$q->delete(MAUTIC_TABLE_PREFIX . 'form_actions')->where($q->expr()->in('id', $deleteActions))->execute();
}
// Set captcha form fields to required
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'form_fields')->set('is_required', ':true')->where($q->expr()->eq('type', $q->expr()->literal('captcha')))->setParameter('true', true, 'boolean')->execute();
// Set all forms as standalone
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'forms')->set('form_type', $q->expr()->literal('standalone'))->execute();
// Rebuild all the forms
/** @var \Mautic\FormBundle\Model\FormModel $formModel */
$formModel = $this->factory->getModel('form');
$formRepo = $formModel->getRepository();
$q = $formRepo->createQueryBuilder('f')->select('f, ff')->leftJoin('f.fields', 'ff');
$forms = $q->getQuery()->getResult();
if (!empty($forms)) {
foreach ($forms as $form) {
// Rebuild the forms
$formModel->generateHtml($form, false);
}
$formRepo->saveEntities($forms);
$em->clear('Mautic\\FormBundle\\Entity\\Form');
}
// Clear template for custom mode
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'pages')->set('template', $q->expr()->literal(''))->where($q->expr()->eq('content_mode', $q->expr()->literal('custom')))->execute();
// Convert email landing page hits to redirects
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'page_hits')->set('email_id', 'source_id')->where($q->expr()->eq('source', $q->expr()->literal('email')), $q->expr()->isNull('email_id'))->execute();
$q = $this->connection->createQueryBuilder();
$clicks = $q->select('ph.url, ph.email_id, count(distinct(ph.tracking_id)) as unique_click_count, count(ph.tracking_id) as click_count')->from(MAUTIC_TABLE_PREFIX . 'page_hits', 'ph')->where($q->expr()->andX($q->expr()->isNotNull('email_id'), $q->expr()->isNotNull('page_id')))->groupBy('ph.url, ph.email_id')->execute()->fetchAll();
// See which already have URLs created as redirects
$redirectEntities = array();
foreach ($clicks as $click) {
$redirect = new Redirect();
$redirect->setDateAdded(new \DateTime());
$redirect->setEmail($em->getReference('MauticEmailBundle:Email', $click['email_id']));
$redirect->setUrl($click['url']);
$redirect->setHits($click['click_count']);
$redirect->setUniqueHits($click['unique_click_count']);
$redirect->setRedirectId();
$redirectEntities[] = $redirect;
}
if (!empty($redirectEntities)) {
$this->factory->getModel('page.redirect')->getRepository()->saveEntities($redirectEntities);
$em->clear('Mautic\\PageBundle\\Entity\\Redirect');
}
// Copy subjects as names
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'emails')->set('name', 'subject')->execute();
// Clear template for custom mode
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'emails')->set('template', $q->expr()->literal(''))->where($q->expr()->eq('content_mode', $q->expr()->literal('custom')))->execute();
// Assume all as templates to start
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'emails')->set('email_type', $q->expr()->literal('template'))->execute();
// Get a list of emails that have been sent to lead lists
$q = $this->connection->createQueryBuilder();
$q->select('s.email_id, count(*) as sent_count, sum(case when s.is_read then 1 else 0 end) as read_count')->from(MAUTIC_TABLE_PREFIX . 'email_stats', 's')->where($q->expr()->isNotNull('s.list_id'))->groupBy('s.email_id');
$results = $q->execute()->fetchAll();
if (!empty($results)) {
$templateEmails = array();
foreach ($results as $email) {
$templateEmails[$email['email_id']] = $email;
}
$templateEmailIds = array_keys($templateEmails);
/** @var \Mautic\EmailBundle\Model\EmailModel $emailModel */
$emailModel = $this->factory->getModel('email');
$emails = $emailModel->getEntities(array('iterator_mode' => true, 'filter' => array('force' => array(array('column' => 'e.id', 'expr' => 'in', 'value' => $templateEmailIds)))));
$persistListEmails = $persistTemplateEmails = $variants = array();
// Clone since the ID may be in a bunch of serialized properties then convert new to a list based email
while (($row = $emails->next()) !== false) {
/** @var \Mautic\EmailBundle\Entity\Email $templateEmail */
$templateEmail = reset($row);
$id = $templateEmail->getId();
$listEmail = clone $templateEmail;
$listEmail->setEmailType('list');
$listEmail->clearVariants();
$listEmail->clearStats();
$listSentCount = $templateEmails[$id]['sent_count'];
$listReadCount = $templateEmails[$id]['read_count'];
$currentSentCount = $templateEmail->getSentCount();
$currentReadCount = $templateEmail->getReadCount();
// Assume the difference between the current counts and the list counts are template related
$templateEmail->setSentCount($currentSentCount - $listSentCount);
$templateEmail->setReadCount($currentReadCount - $listReadCount);
// Set the list email stats
$listEmail->setSentCount($listSentCount);
$listEmail->setReadCount($listReadCount);
// Special cases for variants
if ($variantStartDate = $templateEmail->getVariantStartDate()) {
// Take note that this email needs to also have it's variants
if (!in_array($id, $variants)) {
$variants[] = $id;
}
$dtHelper = new DateTimeHelper($variantStartDate);
$q = $this->connection->createQueryBuilder();
$q->select('s.email_id, count(*) as sent_count, sum(case when s.is_read then 1 else 0 end) as read_count')->from(MAUTIC_TABLE_PREFIX . 'email_stats', 's')->where($q->expr()->andX($q->expr()->isNotNull('s.list_id'), $q->expr()->eq('s.email_id', $id), $q->expr()->gte('s.date_sent', $q->expr()->literal($dtHelper->toUtcString('Y-m-d H:i:s')))))->groupBy('s.email_id');
$results = $q->execute()->fetchAll();
$variantListSentCount = $results[0]['sent_count'];
$variantListReadCount = $results[0]['read_count'];
$variantCurrentSentCount = $templateEmail->getVariantSentCount();
$variantCurrentReadCount = $templateEmail->getVariantReadCount();
// Assume the difference between the current counts and the list counts are template related
$templateEmail->setVariantSentCount($variantCurrentSentCount - $variantListSentCount);
$templateEmail->setVariantReadCount($variantCurrentReadCount - $variantListReadCount);
// Set the list email stats
$listEmail->setVariantSentCount($variantListSentCount);
$listEmail->setVariantReadCount($variantListReadCount);
}
$persistListEmails[$id] = $listEmail;
$persistTemplateEmails[$id] = $templateEmail;
unset($listEmail, $templateEmail);
}
$repo = $emailModel->getRepository();
// Update template emails; no need to run through audit log stuff so just use repo
$repo->saveEntities($persistTemplateEmails);
// Create new list emails and tell audit log to use system
define('MAUTIC_IGNORE_AUDITLOG_USER', 1);
$emailModel->saveEntities($persistListEmails);
// Clone variants
$persistVariants = array();
$processedVariants = array();
foreach ($variants as $templateEmailId) {
if ($persistTemplateEmails[$templateEmailId]->isVariant(true)) {
// A variant of another so get parent
$templateParent = $persistTemplateEmails[$templateEmailId]->getVariantParent();
} else {
$templateParent = $persistTemplateEmails[$templateEmailId];
}
if (in_array($templateParent->getId(), $processedVariants)) {
continue;
}
$processedVariants[] = $templateParent->getId();
// Get the children to clone each one
$children = $templateParent->getVariantChildren();
// If the parent is not already cloned, then do so
/** @var \Mautic\EmailBundle\Entity\Email $listParent */
if (!isset($persistListEmails[$templateParent->getId()])) {
$listParent = clone $templateParent;
$listParent->setEmailType('list');
$listParent->clearVariants();
$listParent->clearStats();
$persistVariants[$templateParent->getId()] = $listParent;
} else {
$listParent = $persistListEmails[$templateParent->getId()];
}
unset($templateParent);
/** @var \Mautic\EmailBundle\Entity\Email $templateChild */
foreach ($children as $templateChild) {
// If the variant already exists, then just set the parent and save
if (isset($persistListEmails[$templateChild->getId()])) {
$persistListEmails[$templateChild->getId()]->setVariantParent($listParent);
$persistVariants[$templateChild->getId()] = $persistListEmails[$templateChild->getId()];
continue;
}
$listChild = clone $templateChild;
$listChild->clearStats();
$listChild->setEmailType('list');
$listChild->setVariantParent($listParent);
$persistVariants[$templateChild->getId()] = $listChild;
unset($listChild, $templateChild);
}
unset($listParent, $children);
}
// Create new variants
$emailModel->saveEntities($persistVariants);
// Now update lead log stats, page hit stats, and email stats
foreach ($persistListEmails as $templateId => $listEmail) {
// Update page hits
$sq = $this->connection->createQueryBuilder();
$sq->select('es.lead_id')->from(MAUTIC_TABLE_PREFIX . 'email_stats', 'es')->where($sq->expr()->andX($sq->expr()->eq('es.email_id', $templateId), $q->expr()->isNotNull('es.list_id')));
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'page_hits')->set('email_id', $listEmail->getId())->where('lead_id IN ' . sprintf('(%s)', $sq->getSql()) . ' AND email_id = ' . $templateId)->execute();
// Update download hits
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'asset_downloads')->set('email_id', $listEmail->getId())->where('lead_id IN ' . sprintf('(%s)', $sq->getSql()) . ' AND email_id = ' . $templateId)->execute();
$q = $this->connection->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX . 'email_stats')->set('email_id', $listEmail->getId())->where($q->expr()->andX($q->expr()->isNotNull('list_id'), $q->expr()->eq('email_id', $templateId)))->execute();
unset($listEmail, $persistListEmails[$templateId]);
}
// Delete all lead list cross references for the emails converted to templates
$q = $this->connection->createQueryBuilder();
$q->delete(MAUTIC_TABLE_PREFIX . 'email_list_xref')->where($q->expr()->in('email_id', $templateEmailIds))->execute();
} else {
// Delete all lead list cross references
$q = $this->connection->createQueryBuilder();
$q->delete(MAUTIC_TABLE_PREFIX . 'email_list_xref')->execute();
}
}
/**
* {@inheritdoc}
*
* @param Email $entity
* @param $unlock
*
* @return mixed
*/
public function saveEntity($entity, $unlock = true)
{
$now = new DateTimeHelper();
$type = $entity->getEmailType();
if (empty($type)) {
// Just in case JS failed
$entity->setEmailType('template');
}
// Ensure that list emails are published
if ($entity->getEmailType() == 'list') {
$entity->setIsPublished(true);
$entity->setPublishDown(null);
$entity->setPublishUp(null);
}
//set the author for new pages
if (!$entity->isNew()) {
//increase the revision
$revision = $entity->getRevision();
$revision++;
$entity->setRevision($revision);
}
// Ensure links in template content don't have encoded ampersands
if ($entity->getTemplate()) {
$content = $entity->getContent();
foreach ($content as $key => $value) {
$content[$key] = $this->cleanUrlsInContent($value);
}
$entity->setContent($content);
} else {
// Ensure links in HTML don't have encoded ampersands
$htmlContent = $this->cleanUrlsInContent($entity->getCustomHtml());
$entity->setCustomHtml($htmlContent);
}
// Ensure links in PLAIN TEXT don't have encoded ampersands
$plainContent = $this->cleanUrlsInContent($entity->getPlainText());
$entity->setPlainText($plainContent);
// Reset the variant hit and start date if there are any changes and if this is an A/B test
// Do it here in addition to the blanket resetVariants call so that it's available to the event listeners
$changes = $entity->getChanges();
$parent = $entity->getVariantParent();
if ($parent !== null && !empty($changes) && empty($this->inConversion)) {
$entity->setVariantSentCount(0);
$entity->setVariantReadCount(0);
$entity->setVariantStartDate($now->getDateTime());
}
parent::saveEntity($entity, $unlock);
// If parent, add this entity as a child of the parent so that it populates the list in the tab (due to Doctrine hanging on to entities in memory)
if ($parent) {
$parent->addVariantChild($entity);
}
// Reset associated variants if applicable due to changes
if ($entity->isVariant() && !empty($changes) && empty($this->inConversion)) {
$dateString = $now->toUtcString();
$parentId = !empty($parent) ? $parent->getId() : $entity->getId();
$this->getRepository()->resetVariants($parentId, $dateString);
//if the parent was changed, then that parent/children must also be reset
if (isset($changes['variantParent'])) {
$this->getRepository()->resetVariants($changes['variantParent'][0], $dateString);
}
}
}
/**
* Get the user's social profile data from cache or integrations if indicated
*
* @param \Mautic\LeadBundle\Entity\Lead $lead
* @param array $fields
* @param bool $refresh
* @param string $specificIntegration
* @param bool $persistLead
* @param bool $returnSettings
*
* @return array
*/
public function getUserProfiles($lead, $fields = array(), $refresh = false, $specificIntegration = null, $persistLead = true, $returnSettings = false)
{
$socialCache = $lead->getSocialCache();
$featureSettings = array();
if ($refresh) {
//regenerate from integrations
$now = new DateTimeHelper();
//check to see if there are social profiles activated
$socialIntegrations = $this->getIntegrationObjects($specificIntegration, array('public_profile', 'public_activity'));
/* @var \MauticPlugin\MauticSocialBundle\Integration\SocialIntegration $sn */
foreach ($socialIntegrations as $integration => $sn) {
$settings = $sn->getIntegrationSettings();
$features = $settings->getSupportedFeatures();
$identifierField = $this->getUserIdentifierField($sn, $fields);
if ($returnSettings) {
$featureSettings[$integration] = $settings->getFeatureSettings();
}
if ($identifierField && $settings->isPublished()) {
$profile = !isset($socialCache[$integration]) ? array() : $socialCache[$integration];
//clear the cache
unset($profile['profile'], $profile['activity']);
if (in_array('public_profile', $features) && $sn->isAuthorized()) {
$sn->getUserData($identifierField, $profile);
}
if (in_array('public_activity', $features) && $sn->isAuthorized()) {
$sn->getPublicActivity($identifierField, $profile);
}
if (!empty($profile['profile']) || !empty($profile['activity'])) {
if (!isset($socialCache[$integration])) {
$socialCache[$integration] = array();
}
$socialCache[$integration]['profile'] = !empty($profile['profile']) ? $profile['profile'] : array();
$socialCache[$integration]['activity'] = !empty($profile['activity']) ? $profile['activity'] : array();
$socialCache[$integration]['lastRefresh'] = $now->toUtcString();
}
} elseif (isset($socialCache[$integration])) {
//integration is now not applicable
unset($socialCache[$integration]);
}
}
if ($persistLead && !empty($socialCache)) {
$lead->setSocialCache($socialCache);
$this->factory->getEntityManager()->getRepository('MauticLeadBundle:Lead')->saveEntity($lead);
}
} elseif ($returnSettings) {
$socialIntegrations = $this->getIntegrationObjects($specificIntegration, array('public_profile', 'public_activity'));
foreach ($socialIntegrations as $integration => $sn) {
$settings = $sn->getIntegrationSettings();
$featureSettings[$integration] = $settings->getFeatureSettings();
}
}
if ($specificIntegration) {
return $returnSettings ? array(array($specificIntegration => $socialCache[$specificIntegration]), $featureSettings) : array($specificIntegration => $socialCache[$specificIntegration]);
}
return $returnSettings ? array($socialCache, $featureSettings) : $socialCache;
}
/**
* Rebuild lead lists
*
* @param LeadList $entity
* @param int $limit
* @param bool $maxLeads
* @param OutputInterface $output
*
* @return int
*/
public function rebuildListLeads(LeadList $entity, $limit = 1000, $maxLeads = false, OutputInterface $output = null)
{
defined('MAUTIC_REBUILDING_LEAD_LISTS') or define('MAUTIC_REBUILDING_LEAD_LISTS', 1);
$id = $entity->getId();
$list = array('id' => $id, 'filters' => $entity->getFilters());
$dtHelper = new DateTimeHelper();
$batchLimiters = array('dateTime' => $dtHelper->toUtcString());
$localDateTime = $dtHelper->getLocalDateTime();
// Get a count of leads to add
$newLeadsCount = $this->getLeadsByList($list, true, array('countOnly' => true, 'newOnly' => true, 'batchLimiters' => $batchLimiters));
// Ensure the same list is used each batch
$batchLimiters['maxId'] = (int) $newLeadsCount[$id]['maxId'];
// Number of total leads to process
$leadCount = (int) $newLeadsCount[$id]['count'];
if ($output) {
$output->writeln($this->translator->trans('mautic.lead.list.rebuild.to_be_added', array('%leads%' => $leadCount, '%batch%' => $limit)));
}
// Handle by batches
$start = $lastRoundPercentage = $leadsProcessed = 0;
// Try to save some memory
gc_enable();
if ($leadCount) {
$maxCount = $maxLeads ? $maxLeads : $leadCount;
if ($output) {
$progress = ProgressBarHelper::init($output, $maxCount);
$progress->start();
}
// Add leads
while ($start < $leadCount) {
// Keep CPU down for large lists; sleep per $limit batch
$this->batchSleep();
$newLeadList = $this->getLeadsByList($list, true, array('newOnly' => true, 'limit' => $limit, 'batchLimiters' => $batchLimiters));
if (empty($newLeadList[$id])) {
// Somehow ran out of leads so break out
break;
}
foreach ($newLeadList[$id] as $l) {
$this->addLead($l, $entity, false, true, -1, $localDateTime);
unset($l);
$leadsProcessed++;
if ($output && $leadsProcessed < $maxCount) {
$progress->setProgress($leadsProcessed);
}
if ($maxLeads && $leadsProcessed >= $maxLeads) {
break;
}
}
$start += $limit;
// Dispatch batch event
if ($this->dispatcher->hasListeners(LeadEvents::LEAD_LIST_BATCH_CHANGE)) {
$event = new ListChangeEvent($newLeadList[$id], $entity, true);
$this->dispatcher->dispatch(LeadEvents::LEAD_LIST_BATCH_CHANGE, $event);
unset($event);
}
unset($newLeadList);
// Free some memory
gc_collect_cycles();
if ($maxLeads && $leadsProcessed >= $maxLeads) {
if ($output) {
$progress->finish();
$output->writeln('');
}
return $leadsProcessed;
}
}
if ($output) {
$progress->finish();
$output->writeln('');
}
}
// Get a count of leads to be removed
$removeLeadCount = $this->getLeadsByList($list, true, array('countOnly' => true, 'nonMembersOnly' => true, 'batchLimiters' => $batchLimiters));
// Restart batching
$start = $lastRoundPercentage = 0;
$leadCount = $removeLeadCount[$id]['count'];
if ($output) {
$output->writeln($this->translator->trans('mautic.lead.list.rebuild.to_be_removed', array('%leads%' => $leadCount, '%batch%' => $limit)));
}
if ($leadCount) {
$maxCount = $maxLeads ? $maxLeads : $leadCount;
if ($output) {
$progress = ProgressBarHelper::init($output, $maxCount);
$progress->start();
}
// Remove leads
while ($start < $leadCount) {
// Keep CPU down for large lists; sleep per $limit batch
$this->batchSleep();
$removeLeadList = $this->getLeadsByList($list, true, array('limit' => $limit, 'nonMembersOnly' => true, 'batchLimiters' => $batchLimiters));
if (empty($removeLeadList[$id])) {
// Somehow ran out of leads so break out
break;
}
foreach ($removeLeadList[$id] as $l) {
$this->removeLead($l, $entity, false, true, true);
$leadsProcessed++;
if ($output && $leadsProcessed < $maxCount) {
$progress->setProgress($leadsProcessed);
}
if ($maxLeads && $leadsProcessed >= $maxLeads) {
break;
}
}
// Dispatch batch event
if ($this->dispatcher->hasListeners(LeadEvents::LEAD_LIST_BATCH_CHANGE)) {
$event = new ListChangeEvent($removeLeadList[$id], $entity, false);
$this->dispatcher->dispatch(LeadEvents::LEAD_LIST_BATCH_CHANGE, $event);
unset($event);
}
$start += $limit;
unset($removeLeadList);
// Free some memory
gc_collect_cycles();
if ($maxLeads && $leadsProcessed >= $maxLeads) {
if ($output) {
$progress->finish();
$output->writeln('');
}
return $leadsProcessed;
}
}
if ($output) {
$progress->finish();
$output->writeln('');
}
}
return $leadsProcessed;
}
/**
* Get a lead's email stat
*
* @param integer $leadId
* @param array $options
*
* @return array
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getLeadStats($leadId, array $options = array())
{
$query = $this->createQueryBuilder('s');
$query->select('IDENTITY(s.sms) AS sms_id, s.id, s.dateSent, e.title, IDENTITY(s.list) AS list_id, l.name as list_name, s.trackingHash as idHash')->leftJoin('MauticSmsBundle:Sms', 'e', 'WITH', 'e.id = s.sms')->leftJoin('MauticLeadBundle:LeadList', 'l', 'WITH', 'l.id = s.list')->where($query->expr()->eq('IDENTITY(s.lead)', $leadId));
if (isset($options['search']) && $options['search']) {
$query->andWhere($query->expr()->like('e.title', $query->expr()->literal('%' . $options['search'] . '%')));
}
if (isset($options['order'])) {
list($orderBy, $orderByDir) = $options['order'];
switch ($orderBy) {
case 'eventLabel':
$orderBy = 'e.title';
break;
case 'timestamp':
default:
$orderBy = 's.dateSent';
break;
}
$query->orderBy($orderBy, $orderByDir);
}
if (!empty($options['limit'])) {
$query->setMaxResults($options['limit']);
if (!empty($options['start'])) {
$query->setFirstResult($options['start']);
}
}
if (isset($options['fromDate']) && $options['fromDate']) {
$dt = new DateTimeHelper($options['fromDate']);
$query->andWhere($query->expr()->gte('s.dateSent', $query->expr()->literal($dt->toUtcString())));
}
$stats = $query->getQuery()->getArrayResult();
return $stats;
}
/**
* 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;
}
/**
* 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();
}
/**
* {@inheritdoc}
*
* @param Page $entity
* @param bool $unlock
*/
public function saveEntity($entity, $unlock = true)
{
if (empty($this->inConversion)) {
$alias = $entity->getAlias();
if (empty($alias)) {
$alias = $entity->getTitle();
}
$alias = $this->cleanAlias($alias, '', false, '-');
//make sure alias is not already taken
$repo = $this->getRepository();
$testAlias = $alias;
$count = $repo->checkUniqueAlias($testAlias, $entity);
$aliasTag = $count;
while ($count) {
$testAlias = $alias . $aliasTag;
$count = $repo->checkUniqueAlias($testAlias, $entity);
$aliasTag++;
}
if ($testAlias != $alias) {
$alias = $testAlias;
}
$entity->setAlias($alias);
}
$now = new DateTimeHelper();
//set the author for new pages
$isNew = $entity->isNew();
if (!$isNew) {
//increase the revision
$revision = $entity->getRevision();
$revision++;
$entity->setRevision($revision);
}
// Reset the variant hit and start date if there are any changes and if this is an A/B test
// Do it here in addition to the blanket resetVariants call so that it's available to the event listeners
$changes = $entity->getChanges();
$parent = $entity->getVariantParent();
if ($parent !== null && !empty($changes) && empty($this->inConversion)) {
$entity->setVariantHits(0);
$entity->setVariantStartDate($now->getDateTime());
}
parent::saveEntity($entity, $unlock);
// If parent, add this entity as a child of the parent so that it populates the list in the tab (due to Doctrine hanging on to entities in memory)
if ($parent) {
$parent->addVariantChild($entity);
}
if ($translationParent = $entity->getTranslationParent()) {
$translationParent->addTranslationChild($entity);
}
// Reset associated variants if applicable due to changes
if ($entity->isVariant() && !empty($changes) && empty($this->inConversion)) {
$dateString = $now->toUtcString();
$parentId = !empty($parent) ? $parent->getId() : $entity->getId();
$this->getRepository()->resetVariants($parentId, $dateString);
//if the parent was changed, then that parent/children must also be reset
if (isset($changes['variantParent'])) {
$this->getRepository()->resetVariants($changes['variantParent'][0], $dateString);
}
}
}
/**
* 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));
}
/**
* 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();
}
/**
* Updates lead's lastActive with now date/time.
*
* @param int $leadId
*/
public function updateLastActive($leadId)
{
$dt = new DateTimeHelper();
$fields = ['last_active' => $dt->toUtcString()];
$this->getEntityManager()->getConnection()->update(MAUTIC_TABLE_PREFIX . 'leads', $fields, ['id' => $leadId]);
}
/**
* Get download count by email by linking emails that have been associated with a page hit that has the
* same tracking ID as an asset download tracking ID and thus assumed happened in the same session
*
* @param $emailId
* @param \DateTime $fromDate
*
* @return mixed
*/
public function getDownloadCountsByEmail($emailId, \DateTime $fromDate = null)
{
//link email to page hit tracking id to download tracking id
$q = $this->_em->getConnection()->createQueryBuilder();
$q->select('count(distinct(a.tracking_id)) as count, e.id, e.subject as name, e.variant_sent_count as total')->from(MAUTIC_TABLE_PREFIX . 'asset_downloads', 'a')->join('a', MAUTIC_TABLE_PREFIX . 'emails', 'e', 'a.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', ':email'))->setParameter('email', (int) $emailId);
}
$q->andWhere('a.code = 200');
if ($fromDate != null) {
$dh = new DateTimeHelper($fromDate);
$q->andWhere($q->expr()->gte('a.date_download', ':date'))->setParameter('date', $dh->toUtcString());
}
$results = $q->execute()->fetchAll();
$downloads = array();
foreach ($results as $r) {
$downloads[$r['id']] = $r;
}
return $downloads;
}
