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