/**
* Returns date/time like Today, 10:00 AM
*
* @param $datetime
* @param string $timezone
* @param string $fromFormat
*/
public function toText($datetime, $timezone = 'local', $fromFormat = 'Y-m-d H:i:s')
{
if (empty($datetime)) {
return '';
}
$this->helper->setDateTime($datetime, $fromFormat, $timezone);
$textDate = $this->helper->getTextDate();
$dt = $this->helper->getLocalDateTime();
if ($textDate) {
return $this->translator->trans('mautic.core.date.' . $textDate, array('%time%' => $dt->format("g:i a")));
} else {
$interval = $this->helper->getDiff('now', null, true);
return $this->translator->trans('mautic.core.date.ago', array('%days%' => $interval->days));
}
}
/**
* Returns date/time like Today, 10:00 AM
*
* @param $datetime
* @param string $timezone
* @param string $fromFormat
* @param bool $forceDateForNonText If true, return as full date/time rather than "29 days ago"
*
* @return string
*/
public function toText($datetime, $timezone = 'local', $fromFormat = 'Y-m-d H:i:s', $forceDateForNonText = false)
{
if (empty($datetime)) {
return '';
}
$this->helper->setDateTime($datetime, $fromFormat, $timezone);
$textDate = $this->helper->getTextDate();
$dt = $this->helper->getLocalDateTime();
if ($textDate) {
return $this->translator->trans('mautic.core.date.' . $textDate, array('%time%' => $dt->format("g:i a")));
} else {
$interval = $this->helper->getDiff('now', null, true);
if ($interval->invert && !$forceDateForNonText) {
// In the past
return $this->translator->trans('mautic.core.date.ago', array('%days%' => $interval->days));
} else {
// In the future
return $this->toFullConcat($datetime, $timezone, $fromFormat);
}
}
}
/**
* 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.email) AS email_id, s.id, s.dateRead, s.dateSent, e.name, e.subject, s.isRead, s.isFailed, s.viewedInBrowser, s.retryCount, IDENTITY(s.list) AS list_id, l.name as list_name, s.trackingHash as idHash, s.openDetails')->leftJoin('MauticEmailBundle:Email', 'e', 'WITH', 'e.id = s.email')->leftJoin('MauticLeadBundle:LeadList', 'l', 'WITH', 'l.id = s.list')->where($query->expr()->andX($query->expr()->eq('IDENTITY(s.lead)', $leadId), $query->expr()->eq('s.isFailed', ':false')))->setParameter('false', false, 'boolean');
if (!empty($options['ipIds'])) {
$query->orWhere('s.ipAddress IN (' . implode(',', $options['ipIds']) . ')');
}
if (isset($options['filters']['search']) && $options['filters']['search']) {
$query->andWhere($query->expr()->orX($query->expr()->like('e.subject', $query->expr()->literal('%' . $options['filters']['search'] . '%')), $query->expr()->like('e.name', $query->expr()->literal('%' . $options['filters']['search'] . '%'))));
}
$stats = $query->getQuery()->getArrayResult();
foreach ($stats as &$stat) {
$dateSent = new DateTimeHelper($stat['dateSent']);
if (!empty($stat['dateSent']) && !empty($stat['dateRead'])) {
$stat['timeToRead'] = $dateSent->getDiff($stat['dateRead']);
} else {
$stat['timeToRead'] = false;
}
}
return $stats;
}
/**
* Get a lead's email stat.
*
* @param int $leadId
* @param array $options
*
* @return array
*
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getLeadStats($leadId, array $options = [])
{
$query = $this->getEntityManager()->getConnection()->createQueryBuilder();
$query->from(MAUTIC_TABLE_PREFIX . 'email_stats', 's')->leftJoin('s', MAUTIC_TABLE_PREFIX . 'emails', 'e', 's.email_id = e.id')->leftJoin('s', MAUTIC_TABLE_PREFIX . 'email_copies', 'ec', 's.copy_id = ec.id');
if ($leadId) {
$query->andWhere($query->expr()->eq('s.lead_id', (int) $leadId));
}
if (!empty($options['basic_select'])) {
$query->select('s.email_id, s.id, s.date_read as dateRead, s.date_sent as dateSent, e.subject, e.name as email_name, s.is_read as isRead, s.is_failed as isFailed, ec.subject as storedSubject');
} else {
$query->select('s.email_id, s.id, s.date_read as dateRead, s.date_sent as dateSent,e.subject, e.name as email_name, s.is_read as isRead, s.is_failed as isFailed, s.viewed_in_browser as viewedInBrowser, s.retry_count as retryCount, s.list_id, l.name as list_name, s.tracking_hash as idHash, s.open_details as openDetails, ec.subject as storedSubject')->leftJoin('s', MAUTIC_TABLE_PREFIX . 'lead_lists', 'l', 's.list_id = l.id');
}
if (isset($options['state'])) {
$state = $options['state'];
if ('read' == $state) {
$query->andWhere($query->expr()->eq('s.is_read', 1));
} elseif ('sent' == $state) {
// Get only those that have not been read yet
$query->andWhere($query->expr()->eq('s.is_read', 0));
$query->andWhere($query->expr()->eq('s.is_failed', 0));
} elseif ('failed' == $state) {
$query->andWhere($query->expr()->eq('s.is_failed', 1));
$state = 'sent';
}
} else {
$state = 'sent';
}
if (isset($options['search']) && $options['search']) {
$query->andWhere($query->expr()->orX($query->expr()->like('ec.subject', $query->expr()->literal('%' . $options['search'] . '%')), $query->expr()->like('e.subject', $query->expr()->literal('%' . $options['search'] . '%')), $query->expr()->like('e.name', $query->expr()->literal('%' . $options['search'] . '%'))));
}
if (isset($options['fromDate']) && $options['fromDate']) {
$dt = new DateTimeHelper($options['fromDate']);
$query->andWhere($query->expr()->gte('s.date_sent', $query->expr()->literal($dt->toUtcString())));
}
$timeToReadParser = function (&$stat) {
$dateSent = new DateTimeHelper($stat['dateSent']);
if (!empty($stat['dateSent']) && !empty($stat['dateRead'])) {
$stat['timeToRead'] = $dateSent->getDiff($stat['dateRead']);
} else {
$stat['timeToRead'] = false;
}
};
return $this->getTimelineResults($query, $options, 'storedSubject, e.subject', 's.date_' . $state, ['openDetails'], ['dateRead', 'dateSent'], $timeToReadParser);
}
/**
* 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.notification) AS notification_id, s.id, s.dateRead, s.dateSent, e.title, s.isRead, s.retryCount, IDENTITY(s.list) AS list_id, l.name as list_name, s.trackingHash as idHash, s.clickDetails')->leftJoin('MauticNotificationBundle:Notification', 'e', 'WITH', 'e.id = s.notification')->leftJoin('MauticLeadBundle:LeadList', 'l', 'WITH', 'l.id = s.list')->where($query->expr()->andX($query->expr()->eq('IDENTITY(s.lead)', $leadId), $query->expr()->eq('s.isFailed', ':false')))->setParameter('false', false, 'boolean');
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 = 'e.dateRead, e.dateSent';
break;
}
$query->orderBy($orderBy, $orderByDir);
}
if (!empty($options['limit'])) {
$query->setMaxResults($options['limit']);
if (!empty($options['start'])) {
$query->setFirstResult($options['start']);
}
}
$stats = $query->getQuery()->getArrayResult();
foreach ($stats as &$stat) {
$dateSent = new DateTimeHelper($stat['dateSent']);
if (!empty($stat['dateSent']) && !empty($stat['dateRead'])) {
$stat['timeToRead'] = $dateSent->getDiff($stat['dateRead']);
} else {
$stat['timeToRead'] = false;
}
}
return $stats;
}
