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/LeadBundle/Entity/ |
<?php
namespace Mautic\LeadBundle\Entity;
use Doctrine\DBAL\ArrayParameterType;
use Doctrine\DBAL\Query\QueryBuilder;
use Mautic\CoreBundle\Entity\CommonRepository;
use Mautic\UserBundle\Entity\User;
use Symfony\Component\EventDispatcher\EventDispatcherInterface;
/**
* @extends CommonRepository<LeadList>
*/
class LeadListRepository extends CommonRepository
{
use OperatorListTrait; // @deprecated to be removed in Mautic 3. Not used inside this class.
use ExpressionHelperTrait;
use RegexTrait;
/**
* @var bool
*/
protected $listFiltersInnerJoinCompany = false;
/**
* @var EventDispatcherInterface
*/
protected $dispatcher;
/**
* Flag to check if some segment filter on a company field exists.
*
* @var bool
*/
protected $hasCompanyFilter = false;
/**
* @var \Doctrine\DBAL\Schema\Column[]
*/
protected $leadTableSchema;
/**
* @var \Doctrine\DBAL\Schema\Column[]
*/
protected $companyTableSchema;
/**
* @param int $id
*/
public function getEntity($id = 0): ?LeadList
{
try {
return $this
->createQueryBuilder('l')
->where('l.id = :listId')
->setParameter('listId', $id)
->getQuery()
->getSingleResult();
} catch (\Exception) {
return null;
}
}
/**
* Get a list of lists.
*
* @param bool $user
* @param string $alias
* @param string $id
*
* @return array
*/
public function getLists(?User $user = null, $alias = '', $id = '')
{
$q = $this->getEntityManager()->createQueryBuilder()
->from(LeadList::class, 'l', 'l.id');
$q->select('partial l.{id, name, alias}')
->andWhere($q->expr()->eq('l.isPublished', ':true'))
->setParameter('true', true, 'boolean');
if ($user) {
$q->andWhere($q->expr()->eq('l.isGlobal', ':true'));
$q->orWhere('l.createdBy = :user');
$q->setParameter('user', $user->getId());
}
if (!empty($alias)) {
$q->andWhere('l.alias = :alias');
$q->setParameter('alias', $alias);
}
if (!empty($id)) {
$q->andWhere(
$q->expr()->neq('l.id', $id)
);
}
$q->orderBy('l.name');
return $q->getQuery()->getArrayResult();
}
/**
* Get lists for a specific lead.
*
* @param int|Lead[] $lead Lead ID or array of Leads
* @param bool $forList
* @param bool $singleArrayHydration
* @param bool $isPublic
*
* @return mixed
*/
public function getLeadLists($lead, $forList = false, $singleArrayHydration = false, $isPublic = false, $isPreferenceCenter = false)
{
if (is_array($lead)) {
$q = $this->getEntityManager()->createQueryBuilder()
->from(LeadList::class, 'l', 'l.id');
if ($forList) {
$q->select('partial l.{id, alias, name}, partial il.{lead, list, dateAdded, manuallyAdded, manuallyRemoved}');
} else {
$q->select('l, partial lead.{id}');
}
$q->leftJoin('l.leads', 'il')
->leftJoin('il.lead', 'lead');
$q->where(
$q->expr()->andX(
$q->expr()->in('lead.id', ':leads'),
$q->expr()->in('il.manuallyRemoved', ':false')
)
)
->setParameter('leads', $lead)
->setParameter('false', false, 'boolean');
if ($isPublic) {
$q->andWhere($q->expr()->eq('l.isGlobal', ':isPublic'))
->setParameter('isPublic', true, 'boolean');
}
if ($isPreferenceCenter) {
$q->andWhere($q->expr()->eq('l.isPreferenceCenter', ':isPreferenceCenter'))
->setParameter('isPreferenceCenter', true, 'boolean');
}
$result = $q->getQuery()->getArrayResult();
$return = [];
foreach ($result as $r) {
foreach ($r['leads'] as $l) {
$return[$l['lead_id']][$r['id']] = $r;
}
}
return $return;
} else {
$q = $this->getEntityManager()->createQueryBuilder()
->from(LeadList::class, 'l', 'l.id');
if ($forList) {
$q->select('partial l.{id, alias, name}, partial il.{lead, list, dateAdded, manuallyAdded, manuallyRemoved}');
} else {
$q->select('l');
}
$q->leftJoin('l.leads', 'il');
$q->where(
$q->expr()->andX(
$q->expr()->eq('IDENTITY(il.lead)', (int) $lead),
$q->expr()->in('il.manuallyRemoved', ':false')
)
)
->setParameter('false', false, 'boolean');
if ($isPublic) {
$q->andWhere($q->expr()->eq('l.isGlobal', ':isPublic'))
->setParameter('isPublic', true, 'boolean');
}
if ($isPreferenceCenter) {
$q->andWhere($q->expr()->eq('l.isPreferenceCenter', ':isPreferenceCenter'))
->setParameter('isPreferenceCenter', true, 'boolean');
}
return ($singleArrayHydration) ? $q->getQuery()->getArrayResult() : $q->getQuery()->getResult();
}
}
/**
* Check Lead segments by ids.
*/
public function checkLeadSegmentsByIds(Lead $lead, $ids): bool
{
if (empty($ids)) {
return false;
}
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$qb->select('ll.leadlist_id')
->from(MAUTIC_TABLE_PREFIX.'lead_lists_leads', 'll')
->where(
$qb->expr()->and(
$qb->expr()->in('ll.leadlist_id', ':ids'),
$qb->expr()->eq('ll.lead_id', ':leadId'),
$qb->expr()->eq('ll.manually_removed', 0)
)
)
->setParameter('leadId', $lead->getId())
->setParameter('ids', $ids, ArrayParameterType::INTEGER);
return (bool) $qb->executeQuery()->fetchOne();
}
/**
* Return a list of global lists.
*
* @return array
*/
public function getGlobalLists()
{
$q = $this->getEntityManager()->createQueryBuilder()
->from(LeadList::class, 'l', 'l.id');
$q->select('partial l.{id, name, alias}')
->where($q->expr()->eq('l.isPublished', 'true'))
->setParameter('true', true, 'boolean')
->andWhere($q->expr()->eq('l.isGlobal', ':true'))
->orderBy('l.name');
return $q->getQuery()->getArrayResult();
}
/**
* Return a list of global lists.
*
* @return array
*/
public function getPreferenceCenterList()
{
$q = $this->getEntityManager()->createQueryBuilder()
->from(LeadList::class, 'l', 'l.id');
$q->select('partial l.{id, name, publicName, alias}')
->where($q->expr()->eq('l.isPublished', 'true'))
->setParameter('true', true, 'boolean')
->andWhere($q->expr()->eq('l.isPreferenceCenter', ':true'))
->orderBy('l.name');
return $q->getQuery()->getArrayResult();
}
/**
* Get a count of leads that belong to the list.
*
* @param int|int[] $listIds
*
* @return array|int
*
* @throws \Exception
*/
public function getLeadCount($listIds)
{
if (!is_array($listIds)) {
$listIds = [$listIds];
}
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q->select('count(l.lead_id) as thecount, l.leadlist_id')
->from(MAUTIC_TABLE_PREFIX.'lead_lists_leads', 'l');
$countListIds = count($listIds);
if (1 === $countListIds) {
$q = $this->forceUseIndex($q, MAUTIC_TABLE_PREFIX.'manually_removed');
$expression = $q->expr()->eq('l.leadlist_id', $listIds[0]);
} else {
$expression = $q->expr()->in('l.leadlist_id', $listIds);
}
$q->where(
$expression,
$q->expr()->eq('l.manually_removed', ':false')
)
->setParameter('false', false, 'boolean')
->groupBy('l.leadlist_id');
$result = $q->executeQuery()->fetchAllAssociative();
$return = [];
foreach ($result as $r) {
$return[$r['leadlist_id']] = $r['thecount'];
}
// Ensure lists without leads have a value
foreach ($listIds as $l) {
if (!isset($return[$l])) {
$return[$l] = 0;
}
}
return (1 === $countListIds) ? $return[$listIds[0]] : $return;
}
private function forceUseIndex(QueryBuilder $qb, string $indexName): QueryBuilder
{
$fromPart = $qb->getQueryPart('from');
$fromPart[0]['alias'] = sprintf('%s USE INDEX (%s)', $fromPart[0]['alias'], $indexName);
$qb->resetQueryPart('from');
$qb->from($fromPart[0]['table'], $fromPart[0]['alias']);
return $qb;
}
public function arrangeFilters($filters): array
{
$objectFilters = [];
if (empty($filters)) {
$objectFilters['lead'][] = $filters;
}
foreach ($filters as $filter) {
$object = $filter['object'] ?? 'lead';
switch ($object) {
case 'company':
$objectFilters['company'][] = $filter;
break;
default:
$objectFilters['lead'][] = $filter;
break;
}
}
return $objectFilters;
}
public function setDispatcher(EventDispatcherInterface $dispatcher): void
{
$this->dispatcher = $dispatcher;
}
/**
* @return QueryBuilder
*/
protected function createFilterExpressionSubQuery($table, $alias, $column, $value, array &$parameters, $leadId = null, array $subQueryFilters = [])
{
$subQb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$subExpr = [];
foreach ($subQueryFilters as $subColumn => $subParameter) {
$subExpr[] = $subQb->expr()->eq($subColumn, ":$subParameter");
}
if ('leads' !== $table) {
$subExpr[] = $subQb->expr()->eq($alias.'.lead_id', 'l.id');
}
// Specific lead
if (!empty($leadId)) {
$columnName = ('leads' === $table) ? 'id' : 'lead_id';
$subExpr[] = $subQb->expr()->eq($alias.'.'.$columnName, $leadId);
}
if (null !== $value && !empty($column)) {
$subFilterParamter = $this->generateRandomParameterName();
$subFunc = 'eq';
if (is_array($value)) {
$subFunc = 'in';
$subExpr[] = $subQb->expr()->in(sprintf('%s.%s', $alias, $column), ":$subFilterParamter");
$parameters[$subFilterParamter] = ['value' => $value, 'type' => ArrayParameterType::STRING];
} else {
$parameters[$subFilterParamter] = $value;
}
$subExpr = $subQb->expr()->$subFunc(sprintf('%s.%s', $alias, $column), ":$subFilterParamter");
}
$subQb->expr()->and(...$subExpr);
$subQb->select('null')
->from(MAUTIC_TABLE_PREFIX.$table, $alias)
->where($subExpr);
return $subQb;
}
/**
* @param \Doctrine\ORM\QueryBuilder|QueryBuilder $q
*/
protected function addCatchAllWhereClause($q, $filter): array
{
return $this->addStandardCatchAllWhereClause(
$q,
$filter,
[
'l.name',
'l.alias',
]
);
}
/**
* @param \Doctrine\ORM\QueryBuilder|QueryBuilder $q
*/
protected function addSearchCommandWhereClause($q, $filter): array
{
[$expr, $parameters] = parent::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.lead.list.searchcommand.isglobal'):
case $this->translator->trans('mautic.lead.list.searchcommand.isglobal', [], null, 'en_US'):
$expr = $q->expr()->eq('l.isGlobal', ":$unique");
$forceParameters = [$unique => true];
break;
case $this->translator->trans('mautic.core.searchcommand.name'):
case $this->translator->trans('mautic.core.searchcommand.name', [], null, 'en_US'):
$expr = $q->expr()->like('l.name', ':'.$unique);
$returnParameter = true;
break;
}
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.lead.list.searchcommand.isglobal',
'mautic.core.searchcommand.ispublished',
'mautic.core.searchcommand.isunpublished',
'mautic.core.searchcommand.name',
'mautic.core.searchcommand.ismine',
'mautic.core.searchcommand.category',
];
return array_merge($commands, parent::getSearchCommands());
}
public function getRelativeDateStrings(): array
{
$keys = self::getRelativeDateTranslationKeys();
$strings = [];
foreach ($keys as $key) {
$strings[$key] = $this->translator->trans($key);
}
return $strings;
}
public static function getRelativeDateTranslationKeys(): array
{
return [
'mautic.lead.list.month_last',
'mautic.lead.list.month_next',
'mautic.lead.list.month_this',
'mautic.lead.list.today',
'mautic.lead.list.tomorrow',
'mautic.lead.list.yesterday',
'mautic.lead.list.week_last',
'mautic.lead.list.week_next',
'mautic.lead.list.week_this',
'mautic.lead.list.year_last',
'mautic.lead.list.year_next',
'mautic.lead.list.year_this',
'mautic.lead.list.anniversary',
];
}
/**
* @return array<array<string>>
*/
protected function getDefaultOrder(): array
{
return [
['l.name', 'ASC'],
];
}
public function getTableAlias(): string
{
return 'l';
}
public function leadListExists(int $id): bool
{
$tableName = MAUTIC_TABLE_PREFIX.'lead_lists';
$result = (int) $this->getEntityManager()->getConnection()
->executeQuery("SELECT EXISTS(SELECT 1 FROM {$tableName} WHERE id = {$id})")
->fetchOne();
return 1 === $result;
}
/**
* Returns array of campaigns related to this segment.
*
* @return array<int, string>
*/
public function getSegmentCampaigns(int $segmentId): array
{
$q = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select('clx.campaign_id, c.name')
->distinct()
->from(MAUTIC_TABLE_PREFIX.'campaign_leadlist_xref', 'clx')
->join('clx', MAUTIC_TABLE_PREFIX.'campaigns', 'c', 'c.id = clx.campaign_id');
$q->where(
$q->expr()->eq('clx.leadlist_id', $segmentId)
);
$lists = [];
$results = $q->executeQuery()->fetchAllAssociative();
foreach ($results as $row) {
$lists[$row['campaign_id']] = $row['name'];
}
return $lists;
}
public function isContactInAnySegment(int $contactId): bool
{
$tableName = MAUTIC_TABLE_PREFIX.'lead_lists_leads';
$sql = <<<SQL
SELECT leadlist_id
FROM $tableName
WHERE lead_id = ?
AND manually_removed = 0
LIMIT 1
SQL;
$segmentIds = $this->getEntityManager()->getConnection()
->executeQuery(
$sql,
[$contactId],
[\PDO::PARAM_INT]
)
->fetchFirstColumn();
return !empty($segmentIds);
}
public function isNotContactInAnySegment(int $contactId): bool
{
return !$this->isContactInAnySegment($contactId);
}
/**
* @param int[] $expectedSegmentIds
*/
public function isContactInSegments(int $contactId, array $expectedSegmentIds): bool
{
$segmentIds = $this->fetchContactToSegmentIdsRelationships($contactId, $expectedSegmentIds);
return !empty($segmentIds);
}
/**
* @param int[] $expectedSegmentIds
*/
public function isNotContactInSegments(int $contactId, array $expectedSegmentIds): bool
{
$segmentIds = $this->fetchContactToSegmentIdsRelationships($contactId, $expectedSegmentIds);
if (empty($segmentIds)) {
return true; // Contact is not associated wit any segment
}
foreach ($expectedSegmentIds as $expectedSegmentId) {
if (in_array($expectedSegmentId, $segmentIds)) { // No exact type comparison used!
return false;
}
}
return true;
}
/**
* @param int[] $expectedSegmentIds
*
* @return int[]
*/
private function fetchContactToSegmentIdsRelationships(int $contactId, array $expectedSegmentIds): array
{
$tableName = MAUTIC_TABLE_PREFIX.'lead_lists_leads';
$sql = <<<SQL
SELECT leadlist_id
FROM $tableName
WHERE lead_id = ?
AND leadlist_id IN (?)
AND manually_removed = 0
SQL;
return $this->getEntityManager()->getConnection()
->executeQuery(
$sql,
[$contactId, $expectedSegmentIds],
[
\PDO::PARAM_INT,
ArrayParameterType::INTEGER,
]
)
->fetchFirstColumn();
}
}