/**
* Get a count of unique hits for the current tracking ID
*
* @param Page|Redirect $page
* @param string $trackingId
*
* @return int
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getHitCountForTrackingId($page, $trackingId)
{
$q = $this->createQueryBuilder('h')->select('count(h.id) as num');
if ($page instanceof Page) {
$q->where('IDENTITY(h.page) = ' . $page->getId());
} elseif ($page instanceof Redirect) {
$q->where('IDENTITY(h.redirect) = ' . $page->getId());
}
$q->andWhere('h.trackingId = :id')->setParameter('id', $trackingId);
$count = $q->getQuery()->getSingleResult();
return (int) $count['num'];
}
/**
* Determine if the page hit is a unique
*
* @param Page|Redirect $page
* @param string $trackingId
*
* @return bool
*/
public function isUniquePageHit($page, $trackingId)
{
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q2 = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q2->select('null')->from(MAUTIC_TABLE_PREFIX . 'page_hits', 'h');
$expr = $q2->expr()->andX($q2->expr()->eq('h.tracking_id', ':id'));
if ($page instanceof Page) {
$expr->add($q2->expr()->eq('h.page_id', $page->getId()));
} elseif ($page instanceof Redirect) {
$expr->add($q2->expr()->eq('h.redirect_id', $page->getId()));
}
$q2->where($expr);
$q->select('u.is_unique')->from(sprintf('(SELECT (NOT EXISTS (%s)) is_unique)', $q2->getSQL()), 'u')->setParameter('id', $trackingId);
return (bool) $q->execute()->fetchColumn();
}
/**
* @param $urls
* @param null $forEmail
* @param bool $createEntity
*
* @return array
*/
public function getRedirectListByUrls($urls, $forEmail = null, $createEntity = true)
{
$repo = $this->getRepository();
$redirects = $repo->findByUrls(array_values($urls), $forEmail);
$byUrl = array();
foreach ($redirects as $redirect) {
$byUrl[$redirect->getUrl()] = $redirect;
}
$return = array();
foreach ($urls as $key => $url) {
if (empty($url)) {
continue;
}
if (isset($byUrl[$url])) {
$return[$key] = $byUrl[$url];
} elseif ($createEntity) {
$redirect = new Redirect();
$redirect->setUrl($url);
$redirect->setEmail($forEmail);
$redirect->setRedirectId();
$this->setTimestamps($redirect, true);
$return[$key] = $redirect;
}
}
unset($redirects, $byUrl);
return $return;
}
/**
* @param $url
*
* @return Trackable
*/
protected function getTrackableEntity($url)
{
$redirect = new Redirect();
$redirect->setUrl($url);
$redirect->setRedirectId();
$trackable = new Trackable();
$trackable->setChannel('email')->setChannelId(1)->setRedirect($redirect)->setHits(rand(1, 10))->setUniqueHits(rand(1, 10));
return $trackable;
}
/**
* @param Request $request
* @param null|Redirect|Page $page
*
* @return array
*/
public function getHitQuery(Request $request, $page = null)
{
if ($page instanceof Redirect) {
//use the configured redirect URL
$pageURL = $page->getUrl();
} else {
//use current URL
// Tracking pixel is used
if (strpos($request->server->get('REQUEST_URI'), '/mtracking.gif') !== false) {
$pageURL = $request->server->get('HTTP_REFERER');
// if additional data were sent with the tracking pixel
if ($request->server->get('QUERY_STRING')) {
parse_str($request->server->get('QUERY_STRING'), $query);
// URL attr 'd' is encoded so let's decode it first.
$decoded = false;
if (isset($query['d'])) {
// parse_str auto urldecodes
$query = $this->decodeArrayFromUrl($query['d'], false);
$decoded = true;
}
if (is_array($query) && !empty($query)) {
if (isset($query['page_url'])) {
$pageURL = $query['page_url'];
if (!$decoded) {
$pageURL = urldecode($pageURL);
}
}
if (isset($query['page_referrer'])) {
if (!$decoded) {
$query['page_referrer'] = urldecode($query['page_referrer']);
}
}
if (isset($query['page_language'])) {
if (!$decoded) {
$query['page_language'] = urldecode($query['page_language']);
}
}
if (isset($query['page_title'])) {
if (!$decoded) {
$query['page_title'] = urldecode($query['page_title']);
}
}
if (isset($query['tags'])) {
if (!$decoded) {
$query['tags'] = urldecode($query['tags']);
}
}
}
}
} else {
$pageURL = 'http';
if ($request->server->get('HTTPS') == 'on') {
$pageURL .= 's';
}
$pageURL .= '://';
if ($request->server->get('SERVER_PORT') != '80') {
$pageURL .= $request->server->get('SERVER_NAME') . ':' . $request->server->get('SERVER_PORT') . $request->server->get('REQUEST_URI');
} else {
$pageURL .= $request->server->get('SERVER_NAME') . $request->server->get('REQUEST_URI');
}
}
}
if (!isset($query)) {
$query = $request->query->all();
}
// Set generated page url
$query['page_url'] = $pageURL;
// Process clickthrough if applicable
if (!empty($query['ct'])) {
$query['ct'] = $this->decodeArrayFromUrl($query['ct']);
}
return $query;
}
/**
* @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();
}
}
/**
* Create a Redirect entity for URL
*
* @param $url
*
* @return Redirect
*/
public function createRedirectEntity($url)
{
$redirect = new Redirect();
$redirect->setUrl($url);
$redirect->setRedirectId();
$this->setTimestamps($redirect, true);
return $redirect;
}
/**
* {@inheritDoc}
*/
public function __toString()
{
$this->__initializer__ && $this->__initializer__->__invoke($this, '__toString', array());
return parent::__toString();
}
