Server : Apache System : Linux server2.corals.io 4.18.0-348.2.1.el8_5.x86_64 #1 SMP Mon Nov 15 09:17:08 EST 2021 x86_64 User : corals ( 1002) PHP Version : 7.4.33 Disable Function : exec,passthru,shell_exec,system Directory : /home/corals/mautic.corals.io/app/bundles/EmailBundle/Entity/ |
<?php
namespace Mautic\EmailBundle\Entity;
use Doctrine\DBAL\ArrayParameterType;
use Doctrine\DBAL\Exception;
use Mautic\CoreBundle\Entity\CommonRepository;
use Mautic\CoreBundle\Helper\Chart\ChartQuery;
use Mautic\CoreBundle\Helper\DateTimeHelper;
use Mautic\LeadBundle\Entity\TimelineTrait;
/**
* @extends CommonRepository<Stat>
*/
class StatRepository extends CommonRepository
{
use TimelineTrait;
/**
* @return mixed
*
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getEmailStatus($trackingHash)
{
$q = $this->createQueryBuilder('s');
$q->select('s')
->leftJoin('s.lead', 'l')
->leftJoin('s.email', 'e')
->where(
$q->expr()->eq('s.trackingHash', ':hash')
)
->setParameter('hash', $trackingHash);
$result = $q->getQuery()->getResult();
return (!empty($result)) ? $result[0] : null;
}
/**
* @param int $contactId
* @param int $emailId
*
* @return array
*/
public function getUniqueClickedLinksPerContactAndEmail($contactId, $emailId)
{
$q = $this->_em->getConnection()->createQueryBuilder();
$q->select('distinct ph.url, ph.date_hit')
->from(MAUTIC_TABLE_PREFIX.'page_hits', 'ph')
->where('ph.email_id = :emailId')
->andWhere('ph.lead_id = :leadId')
->setParameter('leadId', $contactId)
->setParameter('emailId', $emailId);
$result = $q->executeQuery()->fetchAllAssociative();
foreach ($result as $row) {
$data[$row['date_hit']] = $row['url'];
}
return $data;
}
/**
* @param int $limit
* @param int|null $createdByUserId
* @param int|null $companyId
* @param int|null $campaignId
* @param int|null $segmentId
*/
public function getSentEmailToContactData(
$limit,
\DateTime $dateFrom,
\DateTime $dateTo,
$createdByUserId = null,
$companyId = null,
$campaignId = null,
$segmentId = null
): array {
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q->select('s.id, s.lead_id, s.email_address, s.is_read, s.email_id, s.date_sent, s.date_read')
->from(MAUTIC_TABLE_PREFIX.'email_stats', 's')
->leftJoin('s', MAUTIC_TABLE_PREFIX.'emails', 'e', 's.email_id = e.id')
->addSelect('e.name AS email_name')
->leftJoin('s', MAUTIC_TABLE_PREFIX.'page_hits', 'ph', 'ph.source = "email" and ph.source_id = s.email_id and ph.lead_id = s.lead_id')
->addSelect('COUNT(ph.id) AS link_hits');
if (null !== $createdByUserId) {
$q->andWhere('e.created_by = :userId')
->setParameter('userId', $createdByUserId);
}
$q->andWhere('s.date_sent BETWEEN :dateFrom AND :dateTo')
->setParameter('dateFrom', $dateFrom->format('Y-m-d H:i:s'))
->setParameter('dateTo', $dateTo->format('Y-m-d H:i:s'));
$companyJoinOnExpr = $q->expr()->and(
$q->expr()->eq('s.lead_id', 'cl.lead_id')
);
if (!empty($companyId)) {
// Must force a one to one relationship
$companyJoinOnExpr->with(
$q->expr()->eq('cl.is_primary', 1)
);
}
$q->leftJoin('s', MAUTIC_TABLE_PREFIX.'companies_leads', 'cl', $companyJoinOnExpr)
->leftJoin('s', MAUTIC_TABLE_PREFIX.'companies', 'c', 'cl.company_id = c.id')
->addSelect('c.id AS company_id')
->addSelect('c.companyname AS company_name');
if (!empty($companyId)) {
$q->andWhere('cl.company_id = :companyId')
->setParameter('companyId', $companyId);
}
$q->leftJoin('s', MAUTIC_TABLE_PREFIX.'campaign_events', 'ce', 's.source = "campaign.event" and s.source_id = ce.id')
->leftJoin('ce', MAUTIC_TABLE_PREFIX.'campaigns', 'campaign', 'ce.campaign_id = campaign.id')
->addSelect('campaign.id AS campaign_id')
->addSelect('campaign.name AS campaign_name');
if (null !== $campaignId) {
$q->andWhere('ce.campaign_id = :campaignId')
->setParameter('campaignId', $campaignId);
}
$q->leftJoin('s', MAUTIC_TABLE_PREFIX.'lead_lists', 'll', 's.list_id = ll.id')
->addSelect('ll.id AS segment_id')
->addSelect('ll.name AS segment_name');
if (null !== $segmentId) {
$sb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$sb->select('null')
->from(MAUTIC_TABLE_PREFIX.'lead_lists_leads', 'lll')
->where(
$sb->expr()->and(
$sb->expr()->eq('lll.leadlist_id', ':segmentId'),
$sb->expr()->eq('lll.lead_id', 'ph.lead_id'),
$sb->expr()->eq('lll.manually_removed', 0)
)
);
// Filter for both broadcasts and campaign related segments
$q->andWhere(
$q->expr()->or(
$q->expr()->eq('s.list_id', ':segmentId'),
$q->expr()->and(
$q->expr()->isNull('s.list_id'),
sprintf('EXISTS (%s)', $sb->getSQL())
)
)
)
->setParameter('segmentId', $segmentId);
}
$q->setMaxResults($limit);
$q->groupBy('s.id');
$q->orderBy('s.id', 'DESC');
return $q->executeQuery()->fetchAllAssociative();
}
/**
* @param array<int,int|string>|int|null $emailIds
* @param array<int,int|string>|int|true|null $listId
*/
public function getSentStats($emailIds, $listId = null): array
{
if (!is_array($emailIds)) {
$emailIds = [(int) $emailIds];
}
$q = $this->_em->getConnection()->createQueryBuilder();
$q->select('s.lead_id')
->from(MAUTIC_TABLE_PREFIX.'email_stats', 's')
->where(
$q->expr()->in('s.email_id', $emailIds)
);
if ($listId) {
$q->andWhere('s.list_id = :list')
->setParameter('list', $listId);
}
$result = $q->executeQuery()->fetchAllAssociative();
// index by lead
$stats = [];
foreach ($result as $r) {
$stats[$r['lead_id']] = $r['lead_id'];
}
unset($result);
return $stats;
}
/**
* @param array<int,int|string>|int|null $emailIds
* @param array<int,int|string>|int|true|null $listId
* @param bool $combined
*
* @return array|int
*/
public function getSentCount($emailIds = null, $listId = null, ChartQuery $chartQuery = null, $combined = false)
{
return $this->getStatusCount('is_sent', $emailIds, $listId, $chartQuery, $combined);
}
/**
* @param array<int,int|string>|int|null $emailIds
* @param array<int,int|string>|int|null $listId
* @param bool $combined
*
* @return array|int
*/
public function getReadCount($emailIds = null, $listId = null, ChartQuery $chartQuery = null, $combined = false)
{
return $this->getStatusCount('is_read', $emailIds, $listId, $chartQuery, $combined);
}
/**
* @param array<int,int|string>|int|null $emailIds
* @param array<int,int|string>|int|true|null $listId
* @param bool $combined
*
* @return array|int
*/
public function getFailedCount($emailIds = null, $listId = null, ChartQuery $chartQuery = null, $combined = false)
{
return $this->getStatusCount('is_failed', $emailIds, $listId, $chartQuery, $combined);
}
/**
* @param string $column
* @param array<int,int|string>|int|null $emailIds
* @param array<int,int|string>|int|true|null $listId
* @param bool $combined
*
* @return array|int
*/
public function getStatusCount($column, $emailIds = null, $listId = null, ChartQuery $chartQuery = null, $combined = false)
{
$q = $this->_em->getConnection()->createQueryBuilder();
$q->select('count(s.id) as count')
->from(MAUTIC_TABLE_PREFIX.'email_stats', 's');
if ($emailIds) {
if (!is_array($emailIds)) {
$emailIds = [(int) $emailIds];
}
$q->where(
$q->expr()->in('s.email_id', $emailIds)
);
}
if ($listId) {
if (!$combined) {
if (true === $listId) {
$q->addSelect('s.list_id')
->groupBy('s.list_id');
} elseif (is_array($listId)) {
$q->andWhere(
$q->expr()->in('s.list_id', ':segmentIds')
);
$q->setParameter('segmentIds', $listId, ArrayParameterType::INTEGER);
$q->addSelect('s.list_id')
->groupBy('s.list_id');
} else {
$q->andWhere('s.list_id = :list_id')
->setParameter('list_id', $listId);
}
} else {
$subQ = $this->getEntityManager()->getConnection()->createQueryBuilder();
$subQ->select('null')
->from(MAUTIC_TABLE_PREFIX.'lead_lists_leads', 'list')
->andWhere(
$q->expr()->and(
$q->expr()->in('list.leadlist_id', array_map('intval', $listId)),
$q->expr()->eq('list.lead_id', 's.lead_id')
)
);
$q->andWhere(sprintf('EXISTS (%s)', $subQ->getSQL()));
}
}
if ('is_sent' === $column) {
$q->andWhere('s.is_failed = :false')
->setParameter('false', false, 'boolean');
} else {
$q->andWhere($column.' = :true')
->setParameter('true', true, 'boolean');
}
if ($chartQuery) {
if ('is_read' === $column) {
$chartQuery->applyDateFilters($q, 'date_read', 's');
} else {
$chartQuery->applyDateFilters($q, 'date_sent', 's');
}
}
$results = $q->executeQuery()->fetchAllAssociative();
if ((true === $listId || is_array($listId)) && !$combined) {
// Return list group of counts
$byList = [];
foreach ($results as $result) {
$byList[$result['list_id']] = $result['count'];
}
return $byList;
}
return (isset($results[0])) ? $results[0]['count'] : 0;
}
/**
* @param array<int,int|string>|int $emailIds
*/
public function getOpenedRates($emailIds, \DateTime $fromDate = null): array
{
$inIds = (!is_array($emailIds)) ? [$emailIds] : $emailIds;
$sq = $this->_em->getConnection()->createQueryBuilder();
$sq->select('e.email_id, count(e.id) as the_count')
->from(MAUTIC_TABLE_PREFIX.'email_stats', 'e')
->where(
$sq->expr()->and(
$sq->expr()->eq('e.is_failed', ':false'),
$sq->expr()->in('e.email_id', $inIds)
)
)->setParameter('false', false, 'boolean');
if (null !== $fromDate) {
// make sure the date is UTC
$dt = new DateTimeHelper($fromDate);
$sq->andWhere(
$sq->expr()->gte('e.date_sent', $sq->expr()->literal($dt->toUtcString()))
);
}
$sq->groupBy('e.email_id');
// get a total number of sent emails first
$totalCounts = $sq->executeQuery()->fetchAllAssociative();
$return = [];
foreach ($inIds as $id) {
$return[$id] = [
'totalCount' => 0,
'readCount' => 0,
'readRate' => 0,
];
}
foreach ($totalCounts as $t) {
if (null != $t['email_id']) {
$return[$t['email_id']]['totalCount'] = (int) $t['the_count'];
}
}
// now get a read count
$sq->andWhere('e.is_read = :true')
->setParameter('true', true, 'boolean');
$readCounts = $sq->executeQuery()->fetchAllAssociative();
foreach ($readCounts as $r) {
$return[$r['email_id']]['readCount'] = (int) $r['the_count'];
$return[$r['email_id']]['readRate'] = ($return[$r['email_id']]['totalCount']) ?
round(($r['the_count'] / $return[$r['email_id']]['totalCount']) * 100, 2) :
0;
}
return (!is_array($emailIds)) ? $return[$emailIds] : $return;
}
/**
* @param array<int,int|string>|int $emailIds
*
* @return array<int, array<string, mixed>>
*/
public function getOpenedStatIds($emailIds = null, $listId = null): array
{
$q = $this->_em->getConnection()->createQueryBuilder();
$q->select('s.id')
->from(MAUTIC_TABLE_PREFIX.'email_stats', 's');
if ($emailIds) {
if (!is_array($emailIds)) {
$emailIds = [(int) $emailIds];
}
$q->where(
$q->expr()->in('s.email_id', $emailIds)
);
}
$q->andWhere('open_count > 0');
if ($listId) {
$q->andWhere('s.list_id = '.(int) $listId);
}
return $q->executeQuery()->fetchAllAssociative();
}
/**
* Get a lead's email stat.
*
* @param int $leadId
*
* @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, s.lead_id'
)
->leftJoin('s', MAUTIC_TABLE_PREFIX.'lead_lists', 'l', 's.list_id = l.id');
}
$timestampColumn = 's.date_sent';
if (isset($options['state'])) {
$state = $options['state'];
if ('read' == $state) {
$timestampColumn = 's.date_read';
$query->andWhere(
$query->expr()->eq('s.is_read', 1)
);
} elseif ('failed' == $state) {
$query->andWhere(
$query->expr()->eq('s.is_failed', 1)
);
}
}
if (isset($options['search']) && $options['search']) {
$query->andWhere(
$query->expr()->or(
$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($timestampColumn, $query->expr()->literal($dt->toUtcString()))
);
}
$timeToReadParser = function (&$stat): void {
$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',
$timestampColumn,
['openDetails'],
['dateRead', 'dateSent'],
$timeToReadParser
);
}
/**
* Get counts for Sent, Read and Failed emails.
*
* @param QueryBuilder $query
*
* @return array
*
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getIgnoredReadFailed($query = null)
{
$query->select('count(es.id) as sent, count(CASE WHEN es.is_read THEN 1 ELSE null END) as "read", count(CASE WHEN es.is_failed THEN 1 ELSE null END) as failed');
$results = $query->execute()->fetchAssociative();
if ($results) {
$results['ignored'] = $results['sent'] - $results['read'] - $results['failed'];
unset($results['sent']);
} else {
$results['ignored'] = $results['sent'] = $results['read'] = $results['failed'] = 0;
}
return $results;
}
/**
* Get pie graph data for Sent, Read and Failed email count.
*
* @param QueryBuilder $query
*
* @return array
*
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getMostEmails($query, $limit = 10, $offset = 0)
{
$query
->setMaxResults($limit)
->setFirstResult($offset);
return $query->execute()->fetchAllAssociative();
}
/**
* Get sent counts based grouped by email Id.
*
* @param array $emailIds
*/
public function getSentCounts($emailIds = [], \DateTime $fromDate = null): array
{
$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()->and(
$q->expr()->in('e.email_id', $emailIds),
$q->expr()->eq('e.is_failed', ':false')
)
)->setParameter('false', false, 'boolean');
if (null !== $fromDate) {
// 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->executeQuery()->fetchAllAssociative();
$counts = [];
foreach ($results as $r) {
$counts[$r['email_id']] = $r['sentcount'];
}
return $counts;
}
/**
* Updates lead ID (e.g. after a lead merge).
*/
public function updateLead($fromLeadId, $toLeadId): void
{
$q = $this->_em->getConnection()->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX.'email_stats')
->set('lead_id', (int) $toLeadId)
->where('lead_id = '.(int) $fromLeadId)
->executeStatement();
}
/**
* Delete a stat.
*/
public function deleteStat($id): void
{
$this->getEntityManager()->getConnection()->delete(MAUTIC_TABLE_PREFIX.'email_stats', ['id' => (int) $id]);
}
public function deleteStats(array $ids): void
{
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$qb->delete(MAUTIC_TABLE_PREFIX.'email_stats')
->where(
$qb->expr()->in('id', $ids)
)
->executeStatement();
}
public function getTableAlias(): string
{
return 's';
}
/**
* @return array
*/
public function findContactEmailStats($leadId, $emailId)
{
return $this->createQueryBuilder('s')
->where('IDENTITY(s.lead) = :leadId AND IDENTITY(s.email) = :emailId')
->setParameter('leadId', (int) $leadId)
->setParameter('emailId', (int) $emailId)
->getQuery()
->getResult();
}
/**
* @return mixed
*/
public function checkContactsSentEmail($contacts, $emailId)
{
$query = $this->getEntityManager()->getConnection()->createQueryBuilder();
$query->from(MAUTIC_TABLE_PREFIX.'email_stats', 's');
$query->select('id, lead_id')
->where('s.email_id = :email')
->andWhere('s.lead_id in (:contacts)')
->andWhere('is_failed = 0')
->setParameter('email', $emailId)
->setParameter('contacts', $contacts);
return $query->executeQuery()->fetchAssociative();
}
/**
* @return array Formatted as [contactId => sentCount]
*/
public function getSentCountForContacts(array $contacts, $emailId): array
{
$query = $this->getEntityManager()->getConnection()->createQueryBuilder();
$query->from(MAUTIC_TABLE_PREFIX.'email_stats', 's');
$query->select('count(s.id) as sent_count, s.lead_id')
->where('s.email_id = :email')
->andWhere('s.lead_id in (:contacts)')
->andWhere('s.is_failed = 0')
->setParameter('email', $emailId)
->setParameter('contacts', $contacts, ArrayParameterType::INTEGER)
->groupBy('s.lead_id');
$results = $query->executeQuery()->fetchAllAssociative();
$contacts = [];
foreach ($results as $result) {
$contacts[$result['lead_id']] = $result['sent_count'];
}
return $contacts;
}
/**
* @param array<int> $contacts
*
* @return array<int, array<string, int|float>>
*
* @throws Exception
*/
public function getStatsSummaryForContacts(array $contacts): array
{
$queryBuilder = $this->getEntityManager()->getConnection()->createQueryBuilder();
$subQueryBuilder = $this->getEntityManager()->getConnection()->createQueryBuilder();
$leadAlias = 'l'; // leads
$statsAlias = 'es'; // email_stats
$subQueryAlias = 'sq'; // sub query
$cutAlias = 'cut'; // channel_url_trackables
$pageHitsAlias = 'ph'; // page_hits
// use sub query to get page hits for and unique page hits selected contacts
$subQueryBuilder->select(
"COUNT({$pageHitsAlias}.id) AS hits",
"COUNT(DISTINCT({$pageHitsAlias}.redirect_id)) AS unique_hits",
"{$cutAlias}.channel_id",
"{$pageHitsAlias}.lead_id"
)
->from(MAUTIC_TABLE_PREFIX.'channel_url_trackables', $cutAlias)
->join(
$cutAlias,
MAUTIC_TABLE_PREFIX.'page_hits',
$pageHitsAlias,
"{$cutAlias}.redirect_id = {$pageHitsAlias}.redirect_id AND {$cutAlias}.channel_id = {$pageHitsAlias}.source_id"
)
->where("{$cutAlias}.channel = 'email' AND {$pageHitsAlias}.source = 'email'")
->andWhere("{$pageHitsAlias}.lead_id in (:contacts)")
->setParameter('contacts', $contacts, ArrayParameterType::INTEGER)
->groupBy("{$cutAlias}.channel_id, {$pageHitsAlias}.lead_id");
// main query
$queryBuilder->select(
"{$leadAlias}.id AS `lead_id`",
"COUNT({$statsAlias}.id) AS `sent_count`",
"SUM(IF({$statsAlias}.is_read IS NULL, 0, {$statsAlias}.is_read)) AS `read_count`",
"SUM(IF({$subQueryAlias}.hits is NULL, 0, 1)) AS `clicked_through_count`",
)->from(MAUTIC_TABLE_PREFIX.'email_stats', $statsAlias)
->rightJoin(
$statsAlias,
MAUTIC_TABLE_PREFIX.'leads',
$leadAlias,
"{$statsAlias}.lead_id=l.id"
)->leftJoin(
$statsAlias,
"({$subQueryBuilder->getSQL()})",
$subQueryAlias,
"{$statsAlias}.email_id = {$subQueryAlias}.channel_id AND {$statsAlias}.lead_id = {$subQueryAlias}.lead_id"
)->andWhere("{$leadAlias}.id in (:contacts)")
->setParameter('contacts', $contacts, ArrayParameterType::INTEGER)
->groupBy("{$leadAlias}.id");
$results = $queryBuilder->executeQuery()->fetchAllAssociative();
$contacts = [];
foreach ($results as $result) {
$sentCount = (int) $result['sent_count'];
$readCount = (int) $result['read_count'];
$clickedCount = (int) $result['clicked_through_count'];
$contacts[(int) $result['lead_id']] = [
'sent_count' => $sentCount,
'read_count' => $readCount,
'clicked_count' => $clickedCount,
'open_rate' => round($sentCount > 0 ? ($readCount / $sentCount) : 0, 4),
'click_through_rate' => round($sentCount > 0 ? ($clickedCount / $sentCount) : 0, 4),
'click_through_open_rate' => round($readCount > 0 ? ($clickedCount / $readCount) : 0, 4),
];
}
return $contacts;
}
/**
* @param array<int> $emailsIds
* @param array<int> $eventsIds
*
* @return array<int, array<string, int|string>>
*
* @throws Exception
*/
public function getStatsSummaryByCountry(\DateTimeImmutable $dateFrom, \DateTimeImmutable $dateTo, array $emailsIds, string $sourceType = 'email', array $eventsIds = []): array
{
$queryBuilder = $this->getEntityManager()->getConnection()->createQueryBuilder();
$subQueryBuilder = $this->getEntityManager()->getConnection()->createQueryBuilder();
$leadAlias = 'l'; // leads
$statsAlias = 'es'; // email_stats
$subQueryAlias = 'sq'; // sub query
$cutAlias = 'cut'; // channel_url_trackables
$pageHitsAlias = 'ph'; // page_hits
// use sub query to get page hits for and unique page hits selected contacts
$subQueryBuilder->select(
"COUNT({$pageHitsAlias}.id) AS hits",
"COUNT(DISTINCT({$pageHitsAlias}.redirect_id)) AS unique_hits",
"{$cutAlias}.channel_id",
"{$pageHitsAlias}.lead_id"
)
->from(MAUTIC_TABLE_PREFIX.'channel_url_trackables', $cutAlias)
->join(
$cutAlias,
MAUTIC_TABLE_PREFIX.'page_hits',
$pageHitsAlias,
"{$cutAlias}.redirect_id = {$pageHitsAlias}.redirect_id AND {$cutAlias}.channel_id = {$pageHitsAlias}.source_id"
)
->where("{$cutAlias}.channel = 'email' AND {$pageHitsAlias}.source = 'email'")
->andWhere("{$cutAlias}.channel_id in (:emails)")
->groupBy("{$cutAlias}.channel_id, {$pageHitsAlias}.lead_id");
// main query
$queryBuilder->addSelect(
"COUNT({$statsAlias}.id) AS `sent_count`",
"SUM(IF({$statsAlias}.is_read IS NULL, 0, {$statsAlias}.is_read)) AS `read_count`",
"SUM(IF({$subQueryAlias}.hits is NULL, 0, 1)) AS `clicked_through_count`",
)->from(MAUTIC_TABLE_PREFIX.'email_stats', $statsAlias)
->rightJoin(
$statsAlias,
MAUTIC_TABLE_PREFIX.'leads',
$leadAlias,
"{$statsAlias}.lead_id=l.id"
)->leftJoin(
$statsAlias,
"({$subQueryBuilder->getSQL()})",
$subQueryAlias,
"{$statsAlias}.email_id = {$subQueryAlias}.channel_id AND {$statsAlias}.lead_id = {$subQueryAlias}.lead_id"
);
switch ($sourceType) {
case 'campaign':
$queryBuilder->addSelect("{$leadAlias}.country AS `country`")
->andWhere("{$statsAlias}.source_id in (:events)")
->andWhere("{$statsAlias}.source = :source")
->setParameter('emails', $emailsIds, ArrayParameterType::INTEGER)
->setParameter('events', $eventsIds, ArrayParameterType::INTEGER)
->setParameter('source', 'campaign.event');
break;
case 'email':
$queryBuilder->addSelect("{$leadAlias}.country AS `country`")
->andWhere("{$statsAlias}.email_id in (:emails)")
->setParameter('emails', $emailsIds, ArrayParameterType::INTEGER);
}
$queryBuilder->groupBy("{$leadAlias}.country");
$queryBuilder->andWhere("{$statsAlias}.date_sent BETWEEN :dateFrom AND :dateTo");
$queryBuilder->setParameter('dateFrom', $dateFrom->format(DateTimeHelper::FORMAT_DB));
$queryBuilder->setParameter('dateTo', $dateTo->setTime(23, 59, 59)->format('Y-m-d H:i:s'));
return $queryBuilder->executeQuery()->fetchAllAssociative();
}
}