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\Query\QueryBuilder;
use Doctrine\ORM\Query;
use Doctrine\ORM\Tools\Pagination\Paginator;
use Mautic\ChannelBundle\Entity\MessageQueue;
use Mautic\CoreBundle\Entity\CommonRepository;
use Mautic\LeadBundle\Entity\DoNotContact;
/**
* @extends CommonRepository<Email>
*/
class EmailRepository extends CommonRepository
{
/**
* Get an array of do not email.
*
* @param array $leadIds
*/
public function getDoNotEmailList($leadIds = []): array
{
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q->select('l.id, l.email')
->from(MAUTIC_TABLE_PREFIX.'lead_donotcontact', 'dnc')
->leftJoin('dnc', MAUTIC_TABLE_PREFIX.'leads', 'l', 'l.id = dnc.lead_id')
->where($q->expr()->eq('dnc.channel', $q->expr()->literal('email')))
->andWhere($q->expr()->neq('l.email', $q->expr()->literal('')));
if ($leadIds) {
$q->andWhere(
$q->expr()->in('l.id', $leadIds)
);
}
$results = $q->executeQuery()->fetchAllAssociative();
$dnc = [];
foreach ($results as $r) {
$dnc[$r['id']] = strtolower($r['email']);
}
return $dnc;
}
/**
* Check to see if an email is set as do not contact.
*
* @param string $email
*
* @return bool
*/
public function checkDoNotEmail($email)
{
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q->select('dnc.*')
->from(MAUTIC_TABLE_PREFIX.'lead_donotcontact', 'dnc')
->leftJoin('dnc', MAUTIC_TABLE_PREFIX.'leads', 'l', 'l.id = dnc.lead_id')
->where($q->expr()->eq('dnc.channel', $q->expr()->literal('email')))
->andWhere('l.email = :email')
->setParameter('email', $email);
$results = $q->executeQuery()->fetchAllAssociative();
$dnc = count($results) ? $results[0] : null;
if (null === $dnc) {
return false;
}
$dnc['reason'] = (int) $dnc['reason'];
return [
'id' => $dnc['id'],
'unsubscribed' => (DoNotContact::UNSUBSCRIBED === $dnc['reason']),
'bounced' => (DoNotContact::BOUNCED === $dnc['reason']),
'manual' => (DoNotContact::MANUAL === $dnc['reason']),
'comments' => $dnc['comments'],
];
}
/**
* Delete DNC row.
*
* @param int $id
*/
public function deleteDoNotEmailEntry($id): void
{
$this->getEntityManager()->getConnection()->delete(MAUTIC_TABLE_PREFIX.'lead_donotcontact', ['id' => (int) $id]);
}
/**
* Get a list of entities.
*
* @return Paginator
*/
public function getEntities(array $args = [])
{
$q = $this->getEntityManager()
->createQueryBuilder()
->select('e')
->from(Email::class, 'e', 'e.id');
if (empty($args['iterator_mode']) && empty($args['iterable_mode'])) {
$q->leftJoin('e.category', 'c');
if (empty($args['ignoreListJoin']) && (!isset($args['email_type']) || 'list' == $args['email_type'])) {
$q->leftJoin('e.lists', 'l');
}
}
$args['qb'] = $q;
return parent::getEntities($args);
}
/**
* Get amounts of sent and read emails.
*
* @return array
*/
public function getSentReadCount()
{
// Get entities
$q = $this->getEntityManager()->createQueryBuilder();
$q->select('SUM(e.sentCount) as sent_count, SUM(e.readCount) as read_count')
->from(Email::class, 'e');
$results = $q->getQuery()->getSingleResult(Query::HYDRATE_ARRAY);
if (!isset($results['sent_count'])) {
$results['sent_count'] = 0;
}
if (!isset($results['read_count'])) {
$results['read_count'] = 0;
}
return $results;
}
/**
* @param int $emailId
* @param int[]|null $variantIds
* @param int[]|null $listIds
* @param bool $countOnly
* @param int|null $limit
* @param int|null $minContactId
* @param int|null $maxContactId
* @param bool $countWithMaxMin
* @param \DateTime|null $maxDate
*
* @return QueryBuilder|int|array
*/
public function getEmailPendingQuery(
$emailId,
$variantIds = null,
$listIds = null,
$countOnly = false,
$limit = null,
$minContactId = null,
$maxContactId = null,
$countWithMaxMin = false,
$maxDate = null,
int $maxThreads = null,
int $threadId = null
) {
// Do not include leads in the do not contact table
$dncQb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$dncQb->select('dnc.lead_id')
->from(MAUTIC_TABLE_PREFIX.'lead_donotcontact', 'dnc')
->where($dncQb->expr()->eq('dnc.channel', $dncQb->expr()->literal('email')));
// Do not include contacts where the message is pending in the message queue
$mqQb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$mqQb->select('mq.lead_id')
->from(MAUTIC_TABLE_PREFIX.'message_queue', 'mq')
->where(
$mqQb->expr()->and(
$mqQb->expr()->neq('mq.status', $mqQb->expr()->literal(MessageQueue::STATUS_SENT)),
$mqQb->expr()->eq('mq.channel', $mqQb->expr()->literal('email'))
)
);
// Do not include leads that have already been emailed
$statQb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$statQb->select('stat.lead_id')
->from(MAUTIC_TABLE_PREFIX.'email_stats', 'stat');
$statQb->andWhere($statQb->expr()->isNotNull('stat.lead_id'));
if ($variantIds) {
if (!in_array($emailId, $variantIds)) {
$variantIds[] = (string) $emailId;
}
$statQb->andWhere($statQb->expr()->in('stat.email_id', $variantIds));
$mqQb->andWhere($mqQb->expr()->in('mq.channel_id', $variantIds));
} else {
$statQb->andWhere($statQb->expr()->eq('stat.email_id', (int) $emailId));
$mqQb->andWhere($mqQb->expr()->eq('mq.channel_id', (int) $emailId));
}
// Only include those who belong to the associated lead lists
if (is_null($listIds)) {
// Get a list of lists associated with this email
$lists = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select('el.leadlist_id')
->from(MAUTIC_TABLE_PREFIX.'email_list_xref', 'el')
->where('el.email_id = '.(int) $emailId)
->executeQuery()
->fetchAllAssociative();
$listIds = array_column($lists, 'leadlist_id');
if (empty($listIds)) {
// Prevent fatal error
return ($countOnly) ? 0 : [];
}
} elseif (!is_array($listIds)) {
$listIds = [$listIds];
}
// Only include those in associated segments
$segmentQb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$segmentQb->select('ll.lead_id')
->from(MAUTIC_TABLE_PREFIX.'lead_lists_leads', 'll')
->where(
$segmentQb->expr()->and(
$segmentQb->expr()->in('ll.leadlist_id', $listIds),
$segmentQb->expr()->eq('ll.manually_removed', ':false')
)
);
if (null !== $maxDate) {
$segmentQb->andWhere($segmentQb->expr()->lte('ll.date_added', ':max_date'));
$segmentQb->setParameter('max_date', $maxDate, \Doctrine\DBAL\Types\Types::DATETIME_MUTABLE);
}
// Main query
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
if ($countOnly) {
$q->select('count(*) as count');
if ($countWithMaxMin) {
$q->addSelect('MIN(l.id) as min_id, MAX(l.id) as max_id');
}
} else {
$q->select('l.*');
}
$q->from(MAUTIC_TABLE_PREFIX.'leads', 'l')
->andWhere(sprintf('l.id IN (%s)', $segmentQb->getSQL()))
->andWhere(sprintf('l.id NOT IN (%s)', $dncQb->getSQL()))
->andWhere(sprintf('l.id NOT IN (%s)', $statQb->getSQL()))
->andWhere(sprintf('l.id NOT IN (%s)', $mqQb->getSQL()))
->setParameter('false', false, 'boolean');
$excludedListQb = $this->getExcludedListQuery((int) $emailId);
if ($excludedListQb) {
$q->andWhere(sprintf('l.id NOT IN (%s)', $excludedListQb->getSQL()));
}
// Do not include leads which are not subscribed to the category set for email.
$unsubscribeLeadsQb = $this->getCategoryUnsubscribedLeadsQuery((int) $emailId);
$q->andWhere(sprintf('l.id NOT IN (%s)', $unsubscribeLeadsQb->getSQL()));
$q = $this->setMinMaxIds($q, 'l.id', $minContactId, $maxContactId);
// Has an email
$q->andWhere(
$q->expr()->and(
$q->expr()->isNotNull('l.email'),
$q->expr()->neq('l.email', $q->expr()->literal(''))
)
);
if ($threadId && $maxThreads) {
if ($threadId <= $maxThreads) {
$q->andWhere('MOD((l.id + :threadShift), :maxThreads) = 0')
->setParameter('threadShift', $threadId - 1, \Doctrine\DBAL\ParameterType::INTEGER)
->setParameter('maxThreads', $maxThreads, \Doctrine\DBAL\ParameterType::INTEGER);
}
}
if (!empty($limit)) {
$q->setFirstResult(0)
->setMaxResults($limit);
}
return $q;
}
/**
* @param int $emailId
* @param int[]|null $variantIds
* @param int[]|null $listIds
* @param bool $countOnly
* @param int|null $limit
* @param int|null $minContactId
* @param int|null $maxContactId
* @param bool $countWithMaxMin
*
* @return array|int
*/
public function getEmailPendingLeads(
$emailId,
$variantIds = null,
$listIds = null,
$countOnly = false,
$limit = null,
$minContactId = null,
$maxContactId = null,
$countWithMaxMin = false,
int $maxThreads = null,
int $threadId = null
) {
$q = $this->getEmailPendingQuery(
$emailId,
$variantIds,
$listIds,
$countOnly,
$limit,
$minContactId,
$maxContactId,
$countWithMaxMin,
null,
$maxThreads,
$threadId
);
if (!($q instanceof QueryBuilder)) {
return $q;
}
$results = $q->executeQuery()->fetchAllAssociative();
if ($countOnly && $countWithMaxMin) {
// returns array in format ['count' => #, ['min_id' => #, 'max_id' => #]]
return $results[0];
} elseif ($countOnly) {
return (isset($results[0])) ? $results[0]['count'] : 0;
} else {
$leads = [];
foreach ($results as $r) {
$leads[$r['id']] = $r;
}
return $leads;
}
}
/**
* @param string|array<int|string> $search
* @param int $limit
* @param int $start
* @param bool $viewOther
* @param bool $topLevel
* @param string|null $emailType
* @param int|null $variantParentId
*
* @return array
*/
public function getEmailList($search = '', $limit = 10, $start = 0, $viewOther = false, $topLevel = false, $emailType = null, array $ignoreIds = [], $variantParentId = null)
{
$q = $this->createQueryBuilder('e');
$q->select('partial e.{id, subject, name, language}');
if (!empty($search)) {
if (is_array($search)) {
$search = array_map('intval', $search);
$q->andWhere($q->expr()->in('e.id', ':search'))
->setParameter('search', $search);
} else {
$q->andWhere($q->expr()->like('e.name', ':search'))
->setParameter('search', "%{$search}%");
}
}
if (!$viewOther) {
$q->andWhere($q->expr()->eq('e.createdBy', ':id'))
->setParameter('id', $this->currentUser->getId());
}
if ($topLevel) {
if (true === $topLevel || 'variant' == $topLevel) {
$q->andWhere($q->expr()->isNull('e.variantParent'));
} elseif ('translation' == $topLevel) {
$q->andWhere($q->expr()->isNull('e.translationParent'));
}
}
if ($variantParentId) {
$q->andWhere(
$q->expr()->andX(
$q->expr()->eq('IDENTITY(e.variantParent)', (int) $variantParentId),
$q->expr()->eq('e.id', (int) $variantParentId)
)
);
}
if (!empty($ignoreIds)) {
$q->andWhere($q->expr()->notIn('e.id', ':emailIds'))
->setParameter('emailIds', $ignoreIds);
}
if (!empty($emailType)) {
$q->andWhere(
$q->expr()->eq('e.emailType', $q->expr()->literal($emailType))
);
}
$q->orderBy('e.name');
if (!empty($limit)) {
$q->setFirstResult($start)
->setMaxResults($limit);
}
return $q->getQuery()->getArrayResult();
}
/**
* @param \Doctrine\ORM\QueryBuilder|QueryBuilder $q
* @param object $filter
*/
protected function addCatchAllWhereClause($q, $filter): array
{
return $this->addStandardCatchAllWhereClause($q, $filter, [
'e.name',
'e.subject',
]);
}
/**
* @param \Doctrine\ORM\QueryBuilder|QueryBuilder $q
* @param object $filter
*/
protected function addSearchCommandWhereClause($q, $filter): array
{
[$expr, $parameters] = $this->addStandardSearchCommandWhereClause($q, $filter);
if ($expr) {
return [$expr, $parameters];
}
$command = $filter->command;
$unique = $this->generateRandomParameterName();
$returnParameter = false; // returning a parameter that is not used will lead to a Doctrine error
switch ($command) {
case $this->translator->trans('mautic.core.searchcommand.lang'):
$langUnique = $this->generateRandomParameterName();
$langValue = $filter->string.'_%';
$forceParameters = [
$langUnique => $langValue,
$unique => $filter->string,
];
$expr = $q->expr()->orX(
$q->expr()->eq('e.language', ":$unique"),
$q->expr()->like('e.language', ":$langUnique")
);
$returnParameter = true;
break;
}
if ($expr && $filter->not) {
$expr = $q->expr()->not($expr);
}
if (!empty($forceParameters)) {
$parameters = $forceParameters;
} elseif ($returnParameter) {
$string = ($filter->strict) ? $filter->string : "%{$filter->string}%";
$parameters = ["$unique" => $string];
}
return [$expr, $parameters];
}
/**
* @return string[]
*/
public function getSearchCommands(): array
{
$commands = [
'mautic.core.searchcommand.ispublished',
'mautic.core.searchcommand.isunpublished',
'mautic.core.searchcommand.isuncategorized',
'mautic.core.searchcommand.ismine',
'mautic.core.searchcommand.category',
'mautic.core.searchcommand.lang',
];
return array_merge($commands, parent::getSearchCommands());
}
/**
* @return array<array<string>>
*/
protected function getDefaultOrder(): array
{
return [
['e.name', 'ASC'],
];
}
public function getTableAlias(): string
{
return 'e';
}
/**
* Resets variant_start_date, variant_read_count, variant_sent_count.
*
* @param string[]|string|int $relatedIds
* @param string $date
*/
public function resetVariants($relatedIds, $date): void
{
if (!is_array($relatedIds)) {
$relatedIds = [(string) $relatedIds];
}
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$qb->update(MAUTIC_TABLE_PREFIX.'emails')
->set('variant_read_count', 0)
->set('variant_sent_count', 0)
->set('variant_start_date', ':date')
->setParameter('date', $date)
->where(
$qb->expr()->in('id', $relatedIds)
)
->executeStatement();
}
/**
* Up the read/sent counts.
*
* @deprecated use upCountSent or incrementRead method
*
* @param int $id
* @param string $type
* @param int $increaseBy
* @param bool|false $variant
*/
public function upCount($id, $type = 'sent', $increaseBy = 1, $variant = false): void
{
if (!$increaseBy) {
return;
}
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q->update(MAUTIC_TABLE_PREFIX.'emails');
$q->set($type.'_count', $type.'_count + '.(int) $increaseBy);
$q->where('id = '.(int) $id);
if ($variant) {
$q->set('variant_'.$type.'_count', 'variant_'.$type.'_count + '.(int) $increaseBy);
}
// Try to execute 3 times before throwing the exception
// to increase the chance the update will do its stuff.
$retrialLimit = 3;
while ($retrialLimit >= 0) {
try {
$q->executeStatement();
return;
} catch (\Doctrine\DBAL\Exception $e) {
--$retrialLimit;
if (0 === $retrialLimit) {
throw $e;
}
}
}
}
public function upCountSent(int $id, int $increaseBy = 1, bool $variant = false): void
{
if ($increaseBy <= 0) {
return;
}
$connection = $this->getEntityManager()->getConnection();
$updateQuery = $connection->createQueryBuilder()
->update(MAUTIC_TABLE_PREFIX.'emails')
->set('sent_count', 'sent_count + :increaseBy')
->where('id = :id');
if ($variant) {
$updateQuery->set('variant_sent_count', 'variant_sent_count + :increaseBy');
}
$updateQuery
->setParameter('increaseBy', $increaseBy)
->setParameter('id', $id);
// Try to execute 3 times before throwing the exception
$retrialLimit = 3;
while ($retrialLimit >= 0) {
try {
$updateQuery->executeStatement();
return;
} catch (\Doctrine\DBAL\Exception $e) {
--$retrialLimit;
if (0 === $retrialLimit) {
throw $e;
}
}
}
}
public function incrementRead(int $emailId, int $statId, bool $isVariant = false): void
{
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$subQuery = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select('es.email_id')
->from(MAUTIC_TABLE_PREFIX.'email_stats', 'es')
->where('es.id = :statId')
->andWhere('es.is_read = 1');
$q->update(MAUTIC_TABLE_PREFIX.'emails', 'e')
->set('read_count', 'read_count + 1')
->where(
$q->expr()->and(
$q->expr()->eq('e.id', ':emailId'),
$q->expr()->notIn('e.id', $subQuery->getSQL())
)
)
->setParameter('emailId', $emailId)
->setParameter('statId', $statId);
if ($isVariant) {
$q->set('variant_read_count', 'variant_read_count + 1');
}
// Try to execute 3 times before throwing the exception
$retrialLimit = 3;
while ($retrialLimit >= 0) {
try {
$q->executeStatement();
return;
} catch (\Doctrine\DBAL\Exception $e) {
--$retrialLimit;
if (0 === $retrialLimit) {
throw $e;
}
}
}
}
/**
* @depreacated The method is replaced by getPublishedBroadcastsIterable
*
* @param int|null $id
*/
public function getPublishedBroadcasts($id = null): \Doctrine\ORM\Internal\Hydration\IterableResult
{
return $this->getPublishedBroadcastsQuery($id)->iterate();
}
/**
* @return iterable<Email>
*/
public function getPublishedBroadcastsIterable(?int $id = null): iterable
{
return $this->getPublishedBroadcastsQuery($id)->toIterable();
}
private function getPublishedBroadcastsQuery(?int $id = null): Query
{
$qb = $this->createQueryBuilder($this->getTableAlias());
$expr = $this->getPublishedByDateExpression($qb, null, true, true, false);
$expr->add(
$qb->expr()->eq($this->getTableAlias().'.emailType', $qb->expr()->literal('list'))
);
if (null !== $id && 0 !== $id) {
$expr->add(
$qb->expr()->eq($this->getTableAlias().'.id', (int) $id)
);
}
$qb->where($expr);
return $qb->getQuery();
}
/**
* Set Max and/or Min ID where conditions to the query builder.
*
* @param string $column
* @param int $minContactId
* @param int $maxContactId
*/
private function setMinMaxIds(QueryBuilder $q, $column, $minContactId, $maxContactId): QueryBuilder
{
if ($minContactId && is_numeric($minContactId)) {
$q->andWhere($column.' >= :minContactId');
$q->setParameter('minContactId', $minContactId);
}
if ($maxContactId && is_numeric($maxContactId)) {
$q->andWhere($column.' <= :maxContactId');
$q->setParameter('maxContactId', $maxContactId);
}
return $q;
}
/**
* Is one of emails unpublished?
*
* @deprecated to be removed in 6.0 with no replacement
*/
public function isOneUnpublished(array $ids): bool
{
$result = $this->getEntityManager()
->createQueryBuilder()
->select($this->getTableAlias().'.id')
->from(Email::class, $this->getTableAlias(), $this->getTableAlias().'.id')
->where($this->getTableAlias().'.id IN (:ids)')
->setParameter('ids', $ids)
->andWhere('e.isPublished = 0')
->setMaxResults(1)
->getQuery()
->getOneOrNullResult();
return (bool) $result;
}
private function getCategoryUnsubscribedLeadsQuery(int $emailId): QueryBuilder
{
$qb = $this->getEntityManager()->getConnection()
->createQueryBuilder();
return $qb->select('lc.lead_id')
->from(MAUTIC_TABLE_PREFIX.'lead_categories', 'lc')
->innerJoin('lc', MAUTIC_TABLE_PREFIX.'emails', 'e', 'e.category_id = lc.category_id')
->where($qb->expr()->eq('e.id', $emailId))
->andWhere('lc.manually_removed = 1');
}
private function getExcludedListQuery(int $emailId): ?QueryBuilder
{
$connection = $this->getEntityManager()
->getConnection();
$excludedListIds = $connection->createQueryBuilder()
->select('eel.leadlist_id')
->from(MAUTIC_TABLE_PREFIX.'email_list_excluded', 'eel')
->where('eel.email_id = :emailId')
->setParameter('emailId', $emailId)
->executeQuery()
->fetchFirstColumn();
if (!$excludedListIds) {
return null;
}
$queryBuilder = $connection->createQueryBuilder();
$queryBuilder->select('ll.lead_id')
->from(MAUTIC_TABLE_PREFIX.'lead_lists_leads', 'll')
->where($queryBuilder->expr()->in('ll.leadlist_id', $excludedListIds));
return $queryBuilder;
}
}