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\Common\Collections\ArrayCollection;
use Doctrine\DBAL\ArrayParameterType;
use Doctrine\DBAL\Exception\DriverException;
use Doctrine\DBAL\Query\Expression\CompositeExpression;
use Doctrine\DBAL\Query\QueryBuilder;
use Mautic\CoreBundle\Entity\CommonRepository;
use Mautic\CoreBundle\Helper\DateTimeHelper;
use Mautic\CoreBundle\Helper\SearchStringHelper;
use Mautic\LeadBundle\Controller\ListController;
use Mautic\LeadBundle\Event\LeadBuildSearchEvent;
use Mautic\LeadBundle\LeadEvents;
use Mautic\LeadBundle\Segment\Query\QueryBuilder as SegmentQueryBuilder;
use Mautic\PointBundle\Model\TriggerModel;
use Symfony\Component\EventDispatcher\EventDispatcherInterface;
/**
* @extends CommonRepository<Lead>
*/
class LeadRepository extends CommonRepository implements CustomFieldRepositoryInterface
{
use CustomFieldRepositoryTrait {
prepareDbalFieldsForSave as defaultPrepareDbalFieldsForSave;
}
use ExpressionHelperTrait;
use OperatorListTrait;
/**
* @var EventDispatcherInterface
*/
protected $dispatcher;
/**
* @var array
*/
private $availableSocialFields = [];
/**
* @var array
*/
private $availableSearchFields = [];
/**
* Required to get the color based on a lead's points.
*
* @var TriggerModel
*/
private $triggerModel;
/**
* @var ListLeadRepository
*/
private $listLeadRepository;
/**
* Used by search functions to search social profiles.
*/
public function setAvailableSocialFields(array $fields): void
{
$this->availableSocialFields = $fields;
}
/**
* Used by search functions to search using aliases as commands.
*/
public function setAvailableSearchFields(array $fields): void
{
$this->availableSearchFields = $fields;
}
/**
* Sets trigger model.
*/
public function setTriggerModel(TriggerModel $triggerModel): void
{
$this->triggerModel = $triggerModel;
}
public function setDispatcher(EventDispatcherInterface $dispatcher): void
{
$this->dispatcher = $dispatcher;
}
public function setListLeadRepository(ListLeadRepository $listLeadRepository): void
{
$this->listLeadRepository = $listLeadRepository;
}
/**
* Get a list of leads based on field value.
*
* @param string $field
* @param string[]|string $value
* @param ?int $ignoreId
* @param bool $indexByColumn
*
* @return array
*/
public function getLeadsByFieldValue($field, $value, $ignoreId = null, $indexByColumn = false)
{
$results = $this->getEntities([
'qb' => $this->buildQueryForGetLeadsByFieldValue($field, $value, $ignoreId),
'ignore_paginator' => true,
]);
if (!$indexByColumn) {
return $results;
}
return array_combine(array_map(fn (Lead $lead) => $lead->getFieldValue($field), $results), $results);
}
/**
* Builds the query for the getLeadsByFieldValue method.
*
* @internal
*
* @param string $field
* @param string[]|string $value
* @param ?int $ignoreId
*
* @return QueryBuilder
*/
protected function buildQueryForGetLeadsByFieldValue($field, $value, $ignoreId = null)
{
$col = 'l.'.$field;
$q = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select('l.id')
->from(MAUTIC_TABLE_PREFIX.'leads', 'l');
if ($ignoreId) {
$q->where('l.id != :ignoreId')
->setParameter('ignoreId', $ignoreId);
}
if (is_array($value)) {
/**
* Bind each value to specific named parameters.
*
* @see https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/query-builder.html#line-number-0a267d5a2c69797a7656aae33fcc140d16b0a566-72
*/
$valueParams = [];
for ($i = 0; $i < count($value); ++$i) {
$valueParams[':'.$this->generateRandomParameterName()] = $value[$i];
}
$q->andWhere(
$q->expr()->in($col, array_keys($valueParams))
);
foreach ($valueParams as $param => $value) {
$q->setParameter(ltrim($param, ':'), $value);
}
return $q;
}
$q->andWhere("$col = :search")->setParameter('search', $value);
return $q;
}
/**
* @return Lead[]
*/
public function getContactsByEmail($email)
{
$contacts = $this->getLeadsByFieldValue('email', $email);
// Attempt to search for contacts without a + suffix
if (empty($contacts) && preg_match('#^(.*?)\+(.*?)@(.*?)$#', $email, $parts)) {
$email = $parts[1].'@'.$parts[3];
$contacts = $this->getLeadsByFieldValue('email', $email);
}
return $contacts;
}
/**
* @param string[] $emails
*
* @return int[]|array
*/
public function getContactIdsByEmails(array $emails): array
{
$result = $this->getEntityManager()
->createQuery("
SELECT c.id
FROM Mautic\LeadBundle\Entity\Lead c
WHERE c.email IN (:emails)
")
->setParameter('emails', $emails, ArrayParameterType::STRING)
->getArrayResult();
return array_map(
fn ($row): int => (int) $row['id'],
$result
);
}
/**
* Get a list of lead entities.
*
* @param int $leadId
* @param int $limit
*
* @return array
*/
public function getLeadsByUniqueFields($uniqueFieldsWithData, $leadId = null, $limit = null)
{
$results = $this->getLeadFieldsByUniqueFields($uniqueFieldsWithData, 'l.*', $leadId, $limit);
// Collect the IDs
$leads = [];
foreach ($results as $r) {
$leads[$r['id']] = $r;
}
// Get entities
$q = $this->getEntityManager()->createQueryBuilder()
->select('l')
->from(Lead::class, 'l');
$q->where(
$q->expr()->in('l.id', ':ids')
)
->setParameter('ids', array_keys($leads))
->orderBy('l.dateAdded', \Doctrine\Common\Collections\Criteria::DESC)
->addOrderBy('l.id', \Doctrine\Common\Collections\Criteria::DESC);
$entities = $q->getQuery()
->getResult();
/** @var Lead $lead */
foreach ($entities as $lead) {
$lead->setAvailableSocialFields($this->availableSocialFields);
if (!empty($this->triggerModel)) {
$lead->setColor($this->triggerModel->getColorForLeadPoints($lead->getPoints()));
}
$lead->setFields(
$this->formatFieldValues($leads[$lead->getId()])
);
}
return $entities;
}
/**
* Get list of lead Ids by unique field data.
*
* @param iterable<mixed> $uniqueFieldsWithData is an array of columns & values to filter by
* @param int|null $leadId is the current lead id. Added to query to skip and find other leads
* @param int|null $limit Limit count of results to return
*
* @return array<array{id: string}>
*/
public function getLeadIdsByUniqueFields($uniqueFieldsWithData, ?int $leadId = null, ?int $limit = null): array
{
return $this->getLeadFieldsByUniqueFields($uniqueFieldsWithData, 'l.id', $leadId, $limit);
}
/**
* @param iterable<mixed> $uniqueFieldsWithData
*
* @return array<array<mixed>>
*/
private function getLeadFieldsByUniqueFields($uniqueFieldsWithData, string $select, ?int $leadId = null, ?int $limit = null): array
{
$q = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select($select)
->from(MAUTIC_TABLE_PREFIX.'leads', 'l');
foreach ($uniqueFieldsWithData as $col => $val) {
$q->{$this->getUniqueIdentifiersWherePart()}("l.$col = :".$col)
->setParameter($col, $val);
}
// if we have a lead ID lets use it
if ($leadId > 0) {
// make sure that its not the id we already have
$q->andWhere('l.id != '.$leadId);
}
if ($limit > 0) {
$q->setMaxResults($limit);
}
return $q->executeQuery()->fetchAllAssociative();
}
/**
* @param string $email
* @param bool $all Set to true to return all matching lead id's
*
* @return array|null
*/
public function getLeadByEmail($email, $all = false)
{
$q = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select('l.id')
->from(MAUTIC_TABLE_PREFIX.'leads', 'l')
->where('email = :search')
->setParameter('search', $email);
$result = $q->executeQuery()->fetchAllAssociative();
if (count($result)) {
return $all ? $result : $result[0];
} else {
return;
}
}
/**
* Get leads by IP address.
*
* @param bool $byId
*
* @return array
*/
public function getLeadsByIp($ip, $byId = false)
{
$q = $this->createQueryBuilder('l')
->leftJoin('l.ipAddresses', 'i');
$col = ($byId) ? 'i.id' : 'i.ipAddress';
$q->where($col.' = :ip')
->setParameter('ip', $ip)
->orderBy('l.dateAdded', \Doctrine\Common\Collections\Criteria::DESC);
$results = $q->getQuery()->getResult();
/** @var Lead $lead */
foreach ($results as $lead) {
$lead->setAvailableSocialFields($this->availableSocialFields);
}
return $results;
}
/**
* @return array
*/
public function getLead($id)
{
$fq = $this->getEntityManager()->getConnection()->createQueryBuilder();
$fq->select('l.*')
->from(MAUTIC_TABLE_PREFIX.'leads', 'l')
->where('l.id = '.$id);
$results = $fq->executeQuery()->fetchAllAssociative();
return $results[0] ?? [];
}
/**
* @param int $id
*/
public function getEntity($id = 0): ?Lead
{
try {
$q = $this->createQueryBuilder($this->getTableAlias());
if (is_array($id)) {
$this->buildSelectClause($q, $id);
$contactId = (int) $id['id'];
} else {
$q->select('l, u, i')
->leftJoin('l.ipAddresses', 'i')
->leftJoin('l.owner', 'u');
$contactId = $id;
}
$q->andWhere($this->getTableAlias().'.id = :id')
->setParameter('id', (int) $contactId);
$entity = $q->getQuery()->getSingleResult();
} catch (\Exception) {
$entity = null;
}
if (null === $entity) {
return $entity;
}
if ($entity->getFields()) {
// Pulled from Doctrine memory so don't make unnecessary queries as this has already happened
return $entity;
}
if (!empty($this->triggerModel)) {
$entity->setColor($this->triggerModel->getColorForLeadPoints($entity->getPoints()));
}
$fieldValues = $this->getFieldValues($id);
$entity->setFields($fieldValues);
$entity->setAvailableSocialFields($this->availableSocialFields);
return $entity;
}
/**
* Get a contact entity with the primary company data populated.
*
* The primary company data will be a flat array on the entity
* with a key of `primaryCompany`
*
* @param mixed $entity
*
* @return mixed|null
*/
public function getEntityWithPrimaryCompany($entity)
{
if (is_int($entity)) {
$entity = $this->getEntity($entity);
}
if ($entity instanceof Lead) {
$id = $entity->getId();
$companies = $this->getEntityManager()->getRepository(Company::class)->getCompaniesForContacts([$id]);
if (!empty($companies[$id])) {
$primary = null;
foreach ($companies as $company) {
if (isset($company['is_primary']) && 1 == $company['is_primary']) {
$primary = $company;
}
}
if (empty($primary)) {
$primary = $companies[$id][0];
}
$entity->setPrimaryCompany($primary);
}
}
return $entity;
}
/**
* Get a list of leads.
*
* @return array
*/
public function getEntities(array $args = [])
{
$contacts = $this->getEntitiesWithCustomFields(
'lead',
$args,
function ($r): void {
if (!empty($this->triggerModel)) {
$r->setColor($this->triggerModel->getColorForLeadPoints($r->getPoints()));
}
$r->setAvailableSocialFields($this->availableSocialFields);
}
);
$contactCount = isset($contacts['results']) ? count($contacts['results']) : count($contacts);
if ($contactCount && (!empty($args['withPrimaryCompany']) || !empty($args['withChannelRules']))) {
$withTotalCount = (array_key_exists('withTotalCount', $args) && $args['withTotalCount']);
/** @var Lead[] $tmpContacts */
$tmpContacts = ($withTotalCount) ? $contacts['results'] : $contacts;
$withCompanies = !empty($args['withPrimaryCompany']);
$withPreferences = !empty($args['withChannelRules']);
$contactIds = array_keys($tmpContacts);
if ($withCompanies) {
$companies = $this->getEntityManager()->getRepository(Company::class)->getCompaniesForContacts($contactIds);
}
if ($withPreferences) {
/** @var FrequencyRuleRepository $frequencyRepo */
$frequencyRepo = $this->getEntityManager()->getRepository(FrequencyRule::class);
$frequencyRules = $frequencyRepo->getFrequencyRules(null, $contactIds);
/** @var DoNotContactRepository $dncRepository */
$dncRepository = $this->getEntityManager()->getRepository(DoNotContact::class);
$dncRules = $dncRepository->getChannelList(null, $contactIds);
}
foreach ($contactIds as $id) {
if ($withCompanies && isset($companies[$id]) && !empty($companies[$id])) {
$primary = null;
// Try to find the primary company
foreach ($companies[$id] as $company) {
if (1 == $company['is_primary']) {
$primary = $company;
}
}
// If no primary was found, just grab the first
if (empty($primary)) {
$primary = $companies[$id][0];
}
if (is_array($tmpContacts[$id])) {
$tmpContacts[$id]['primaryCompany'] = $primary;
} elseif ($tmpContacts[$id] instanceof Lead) {
$tmpContacts[$id]->setPrimaryCompany($primary);
}
}
if ($withPreferences) {
$contactFrequencyRules = $frequencyRules[$id] ?? [];
$contactDncRules = $dncRules[$id] ?? [];
$channelRules = Lead::generateChannelRules($contactFrequencyRules, $contactDncRules);
if (is_array($tmpContacts[$id])) {
$tmpContacts[$id]['channelRules'] = $channelRules;
} elseif ($tmpContacts[$id] instanceof Lead) {
$tmpContacts[$id]->setChannelRules($channelRules);
}
}
}
if ($withTotalCount) {
$contacts['results'] = $tmpContacts;
} else {
$contacts = $tmpContacts;
}
}
return $contacts;
}
public function getFieldGroups(): array
{
return ['core', 'social', 'personal', 'professional'];
}
/**
* @return QueryBuilder
*/
public function getEntitiesDbalQueryBuilder()
{
$alias = $this->getTableAlias();
return (new SegmentQueryBuilder($this->getEntityManager()->getConnection()))
->from(MAUTIC_TABLE_PREFIX.'leads', $alias);
}
/**
* @param mixed[] $args
*
* @return \Doctrine\ORM\QueryBuilder
*/
public function getEntitiesOrmQueryBuilder($order, array $args=[])
{
$alias = $this->getTableAlias();
$select = [$alias, 'u', $order];
$q = $this->getEntityManager()->createQueryBuilder();
$joinIpAddresses = $args['joinIpAddresses'] ?? true;
if ($joinIpAddresses) {
$select[] = 'i';
}
$q->select($select)
->from(Lead::class, $alias, $alias.'.id')
->leftJoin($alias.'.owner', 'u')
->indexBy($alias, $alias.'.id');
if ($joinIpAddresses) {
$q->leftJoin($alias.'.ipAddresses', 'i');
}
return $q;
}
/**
* Get contacts for a specific channel entity.
*
* @param array $args same as getEntity/getEntities
* @param string $joinTable
* @param int $entityId
* @param array $filters
* @param string $entityColumnName
* @param array|null $additionalJoins [ ['type' => 'join|leftJoin', 'from_alias' => '', 'table' => '', 'condition' => ''], ... ]
*/
public function getEntityContacts(
$args,
$joinTable,
$entityId,
$filters = [],
$entityColumnName = 'id',
array $additionalJoins = null,
$contactColumnName = 'lead_id',
\DateTimeInterface $dateFrom = null,
\DateTimeInterface $dateTo = null
): array {
$qb = $this->getEntitiesDbalQueryBuilder();
if (empty($contactColumnName)) {
$contactColumnName = 'lead_id';
}
$joinCondition = $qb->expr()->and(
$qb->expr()->eq($this->getTableAlias().'.id', 'entity.'.$contactColumnName)
);
if ($entityId && $entityColumnName) {
$joinCondition = $joinCondition->with(
$qb->expr()->eq("entity.{$entityColumnName}", (int) $entityId)
);
}
if (!empty($joinTable)) {
$qb->join(
$this->getTableAlias(),
MAUTIC_TABLE_PREFIX.$joinTable,
'entity',
$joinCondition
);
}
if (is_array($additionalJoins)) {
foreach ($additionalJoins as $t) {
$qb->{$t['type']}(
$t['from_alias'],
MAUTIC_TABLE_PREFIX.$t['table'],
$t['alias'],
$t['condition']
);
}
}
if ($filters) {
$expr = null;
foreach ($filters as $column => $value) {
if (is_array($value)) {
$this->buildWhereClauseFromArray($qb, [$value]);
} else {
if (!str_contains($column, '.')) {
$column = "entity.$column";
}
if (null === $expr) {
$expr = CompositeExpression::and($qb->expr()->eq($column, $qb->createNamedParameter($value)));
$qb->andWhere($expr);
continue;
}
$expr = $expr->with(
$qb->expr()->eq($column, $qb->createNamedParameter($value))
);
$qb->andWhere($expr);
}
}
}
$args['qb'] = $qb;
$args['count'] = (ListController::ROUTE_SEGMENT_CONTACTS == $args['route']) ? $this->listLeadRepository->getContactsCountBySegment($entityId, $filters) : null;
if ($dateFrom && $dateTo) {
$qb->andWhere('entity.date_added BETWEEN FROM_UNIXTIME(:dateFrom) AND FROM_UNIXTIME(:dateTo)')
->setParameter('dateFrom', $dateFrom->getTimestamp(), \PDO::PARAM_INT)
->setParameter('dateTo', $dateTo->getTimestamp(), \PDO::PARAM_INT);
}
return $this->getEntities($args);
}
/**
* Adds the "catch all" where clause to the QueryBuilder.
*
* @param \Doctrine\ORM\QueryBuilder|QueryBuilder $q
*/
protected function addCatchAllWhereClause($q, $filter): array
{
$columns = array_merge(
[
'l.firstname',
'l.lastname',
'l.email',
'l.company',
'l.city',
'l.state',
'l.zipcode',
'l.country',
],
$this->availableSocialFields
);
return $this->addStandardCatchAllWhereClause($q, $filter, $columns);
}
/**
* Adds the command where clause to the QueryBuilder.
*
* @param QueryBuilder $q
*/
protected function addSearchCommandWhereClause($q, $filter): array
{
$command = $filter->command;
$string = $filter->string;
$unique = $this->generateRandomParameterName();
$returnParameter = false; // returning a parameter that is not used will lead to a Doctrine error
[$expr, $parameters] = parent::addSearchCommandWhereClause($q, $filter);
// DBAL QueryBuilder does not have an expr()->not() function; boo!!
// This will be switched by some commands that use join tables as NOT EXISTS queries will be used
$exprType = ($filter->not) ? 'negate_expr' : 'expr';
$operators = $this->getFilterExpressionFunctions();
$operators = array_merge($operators, [
'null' => [
'expr' => 'isNull',
'negate_expr' => 'isNotNull',
],
]);
$innerJoinTables = (isset($this->advancedFilterCommands[$command])
&& SearchStringHelper::COMMAND_NEGATE !== $this->advancedFilterCommands[$command]);
$likeExpr = $operators['like'][$exprType];
$eqExpr = $operators['='][$exprType];
$nullExpr = $operators['null'][$exprType];
$inExpr = $operators['in'][$exprType];
switch ($command) {
case $this->translator->trans('mautic.lead.lead.searchcommand.isanonymous'):
case $this->translator->trans('mautic.lead.lead.searchcommand.isanonymous', [], null, 'en_US'):
$expr = $q->expr()->$nullExpr('l.date_identified');
break;
case $this->translator->trans('mautic.core.searchcommand.ismine'):
case $this->translator->trans('mautic.core.searchcommand.ismine', [], null, 'en_US'):
$expr = $q->expr()->$eqExpr('l.owner_id', $this->currentUser->getId());
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.isunowned'):
case $this->translator->trans('mautic.lead.lead.searchcommand.isunowned', [], null, 'en_US'):
$expr = $q->expr()->or(
$q->expr()->$eqExpr('l.owner_id', 0),
$q->expr()->$nullExpr('l.owner_id')
);
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.owner'):
case $this->translator->trans('mautic.lead.lead.searchcommand.owner', [], null, 'en_US'):
$q->leftJoin($this->getTableAlias(), MAUTIC_TABLE_PREFIX.'users', 'u', "u.id = {$this->getTableAlias()}.owner_id");
$expr = $q->expr()->or(
$q->expr()->$likeExpr('u.first_name', ':'.$unique),
$q->expr()->$likeExpr('u.last_name', ':'.$unique)
);
$returnParameter = true;
break;
case $this->translator->trans('mautic.core.searchcommand.name'):
case $this->translator->trans('mautic.core.searchcommand.name', [], null, 'en_US'):
$expr = $q->expr()->or(
$q->expr()->$likeExpr('l.firstname', ":$unique"),
$q->expr()->$likeExpr('l.lastname', ":$unique")
);
$returnParameter = true;
break;
case $this->translator->trans('mautic.core.searchcommand.email'):
case $this->translator->trans('mautic.core.searchcommand.email', [], null, 'en_US'):
$expr = $q->expr()->$likeExpr('l.email', ":$unique");
$returnParameter = true;
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.list'):
case $this->translator->trans('mautic.lead.lead.searchcommand.list', [], null, 'en_US'):
$sq = $this->getEntityManager()->getConnection()->createQueryBuilder();
$sq->select('1')
->from(MAUTIC_TABLE_PREFIX.'lead_lists_leads', 'lla')
->where(
$q->expr()->and(
$q->expr()->eq('l.id', 'lla.lead_id'),
$q->expr()->eq('lla.manually_removed', 0),
$q->expr()->in('lla.leadlist_id', ":$unique")
)
);
$from = $q->getQueryPart('from')[0];
$q->resetQueryPart('from');
$q->add('from', ['hint' => 'USE INDEX FOR JOIN ('.MAUTIC_TABLE_PREFIX.'lead_date_added)'] + $from, true);
$filter->strict = true;
$q->andWhere($q->expr()->{$filter->not ? 'notExists' : 'exists'}($sq->getSQL()));
$q->setParameter($unique, $this->getListIdsByAlias($string) ?: [0], ArrayParameterType::INTEGER);
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.company_id'):
case $this->translator->trans('mautic.lead.lead.searchcommand.company_id', [], null, 'en_US'):
$this->applySearchQueryRelationship(
$q,
[
[
'from_alias' => 'l',
'table' => 'companies_leads',
'alias' => 'comp_lead',
'condition' => 'l.id = comp_lead.lead_id',
],
],
$innerJoinTables,
$this->generateFilterExpression($q, 'comp_lead.company_id', $eqExpr, $unique, null)
);
$filter->strict = true;
$returnParameter = true;
break;
case $this->translator->trans('mautic.core.searchcommand.ip'):
case $this->translator->trans('mautic.core.searchcommand.ip', [], null, 'en_US'):
$this->applySearchQueryRelationship(
$q,
[
[
'from_alias' => 'l',
'table' => 'lead_ips_xref',
'alias' => 'ip_lead',
'condition' => 'l.id = ip_lead.lead_id',
],
[
'from_alias' => 'ip_lead',
'table' => 'ip_addresses',
'alias' => 'ip',
'condition' => 'ip_lead.ip_id = ip.id',
],
],
$innerJoinTables,
$this->generateFilterExpression($q, 'ip.ip_address', $likeExpr, $unique, null)
);
$returnParameter = true;
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.duplicate'):
case $this->translator->trans('mautic.lead.lead.searchcommand.duplicate', [], null, 'en_US'):
$prateek = explode('+', $string);
$imploder = [];
foreach ($prateek as $value) {
$list = $this->getEntityManager()->getRepository(LeadList::class)->findOneByAlias($value);
$imploder[] = ((!empty($list)) ? (int) $list->getId() : 0);
}
// logic. In query, Sum(manually_removed) should be less than the current)
$pluck = count($imploder);
$imploder = (string) implode(',', $imploder);
$sq = $this->getEntityManager()->getConnection()->createQueryBuilder();
$sq->select('duplicate.lead_id')
->from(MAUTIC_TABLE_PREFIX.'lead_lists_leads', 'duplicate')
->where(
$q->expr()->and(
$q->expr()->in('duplicate.leadlist_id', $imploder),
$q->expr()->eq('duplicate.manually_removed', 0)
)
)
->groupBy('duplicate.lead_id')
->having("COUNT(duplicate.lead_id) = $pluck");
$expr = $q->expr()->$inExpr('l.id', sprintf('(%s)', $sq->getSQL()));
$returnParameter = true;
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.tag'):
case $this->translator->trans('mautic.lead.lead.searchcommand.tag', [], null, 'en_US'):
$this->applySearchQueryRelationship(
$q,
[
[
'from_alias' => 'l',
'table' => 'lead_tags_xref',
'alias' => 'xtag',
'condition' => 'l.id = xtag.lead_id',
],
[
'from_alias' => 'xtag',
'table' => 'lead_tags',
'alias' => 'tag',
'condition' => 'xtag.tag_id = tag.id',
],
],
$innerJoinTables,
$this->generateFilterExpression($q, 'tag.tag', $likeExpr, $unique, null)
);
$returnParameter = true;
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.company'):
case $this->translator->trans('mautic.lead.lead.searchcommand.company', [], null, 'en_US'):
$this->applySearchQueryRelationship(
$q,
[
[
'from_alias' => 'l',
'table' => 'companies_leads',
'alias' => 'comp_lead',
'condition' => 'l.id = comp_lead.lead_id',
],
[
'from_alias' => 'comp_lead',
'table' => 'companies',
'alias' => 'comp',
'condition' => 'comp_lead.company_id = comp.id',
],
],
$innerJoinTables,
$this->generateFilterExpression($q, 'comp.companyname', $likeExpr, $unique, null)
);
$returnParameter = true;
break;
case $this->translator->trans('mautic.lead.lead.searchcommand.stage'):
case $this->translator->trans('mautic.lead.lead.searchcommand.stage', [], null, 'en_US'):
$this->applySearchQueryRelationship(
$q,
[
[
'from_alias' => 'l',
'table' => 'stages',
'alias' => 's',
'condition' => 'l.stage_id = s.id',
],
],
$innerJoinTables,
$this->generateFilterExpression($q, 's.name', $likeExpr, $unique, null)
);
$returnParameter = true;
break;
default:
if (in_array($command, $this->availableSearchFields)) {
$expr = $q->expr()->$likeExpr("l.$command", ":$unique");
}
$returnParameter = true;
break;
}
if ($this->dispatcher) {
$event = new LeadBuildSearchEvent($filter->string, $filter->command, $unique, $filter->not, $q);
$this->dispatcher->dispatch($event, LeadEvents::LEAD_BUILD_SEARCH_COMMANDS);
if ($event->isSearchDone()) {
$returnParameter = $event->getReturnParameters();
$filter->strict = $event->getStrict();
$expr = $event->getSubQuery();
$parameters = array_merge($parameters, $event->getParameters());
}
}
if ($returnParameter) {
$string = ($filter->strict) ? $filter->string : "{$filter->string}%";
$parameters[$unique] = $string;
}
return [
$expr,
$parameters,
];
}
/**
* @return string[]
*/
public function getSearchCommands(): array
{
$commands = [
'mautic.lead.lead.searchcommand.isanonymous',
'mautic.core.searchcommand.ismine',
'mautic.lead.lead.searchcommand.isunowned',
'mautic.lead.lead.searchcommand.list',
'mautic.core.searchcommand.name',
'mautic.lead.lead.searchcommand.company',
'mautic.lead.lead.searchcommand.company_id',
'mautic.core.searchcommand.email',
'mautic.lead.lead.searchcommand.owner',
'mautic.core.searchcommand.ip',
'mautic.lead.lead.searchcommand.tag',
'mautic.lead.lead.searchcommand.stage',
'mautic.lead.lead.searchcommand.duplicate',
'mautic.lead.lead.searchcommand.email_sent',
'mautic.lead.lead.searchcommand.email_read',
'mautic.lead.lead.searchcommand.email_queued',
'mautic.lead.lead.searchcommand.email_pending',
'mautic.lead.lead.searchcommand.page_source',
'mautic.lead.lead.searchcommand.page_source_id',
'mautic.lead.lead.searchcommand.import_id',
'mautic.lead.lead.searchcommand.import_action',
'mautic.lead.lead.searchcommand.page_id',
'mautic.lead.lead.searchcommand.sms_sent',
'mautic.lead.lead.searchcommand.web_sent',
'mautic.lead.lead.searchcommand.mobile_sent',
];
if (!empty($this->availableSearchFields)) {
$commands = array_merge($commands, $this->availableSearchFields);
}
return array_merge($commands, parent::getSearchCommands());
}
/**
* Returns the array of columns with the default order.
*/
protected function getDefaultOrder(): array
{
return [
['l.last_active', 'DESC'],
];
}
/**
* Updates lead's lastActive with now date/time.
*
* @param int $leadId
*/
public function updateLastActive($leadId, ?\DateTimeInterface $lastActiveDate = null): void
{
if (!$leadId) {
// Prevent unnecessary queries like:
// `UPDATE leads SET last_active = ... WHERE id IS NULL`
return;
}
$dt = new DateTimeHelper($lastActiveDate ?? '');
$fields = ['last_active' => $dt->toUtcString()];
$this->getEntityManager()->getConnection()->update(MAUTIC_TABLE_PREFIX.'leads', $fields, ['id' => $leadId]);
}
/**
* Gets the ID of the latest ID.
*
* @return int
*/
public function getMaxLeadId()
{
$result = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select('max(id) as max_lead_id')
->from(MAUTIC_TABLE_PREFIX.'leads', 'l')
->executeQuery()
->fetchAllAssociative();
return $result[0]['max_lead_id'];
}
/**
* Gets names, signature and email of the user(lead owner).
*
* @param int $ownerId
*
* @return array|false
*/
public function getLeadOwner($ownerId)
{
if (!$ownerId) {
return false;
}
$q = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select('u.id, u.first_name, u.last_name, u.email, u.position, u.signature')
->from(MAUTIC_TABLE_PREFIX.'users', 'u')
->where('u.id = :ownerId')
->setParameter('ownerId', (int) $ownerId);
$result = $q->executeQuery()->fetchAssociative();
// Fix the HTML markup
if (is_array($result)) {
foreach ($result as &$field) {
$field = is_string($field) ? html_entity_decode($field) : $field;
}
}
return $result;
}
/**
* Check Lead segments by ids.
*
* @param array<int> $stages
*/
public function isContactInOneOfStages(Lead $lead, array $stages = []): bool
{
if (empty($stages)) {
return false;
}
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q->select('s.id')
->from(MAUTIC_TABLE_PREFIX.'stages', 's');
$q->join('s', MAUTIC_TABLE_PREFIX.'leads', 'l', 'l.stage_id = s.id')
->where(
$q->expr()->and(
$q->expr()->in('s.id', ':stageIds'),
$q->expr()->eq('l.id', ':leadId')
)
)
->setParameter('stageIds', $stages, ArrayParameterType::INTEGER)
->setParameter('leadId', $lead->getId());
return (bool) $q->executeQuery()->fetchOne();
}
/**
* Check lead owner.
*
* @param array $ownerIds
*/
public function checkLeadOwner(Lead $lead, $ownerIds = []): bool
{
if (empty($ownerIds)) {
return false;
}
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q->select('u.id')
->from(MAUTIC_TABLE_PREFIX.'users', 'u')
->join('u', MAUTIC_TABLE_PREFIX.'leads', 'l', 'l.owner_id = u.id')
->where(
$q->expr()->and(
$q->expr()->in('u.id', ':ownerIds'),
$q->expr()->eq('l.id', ':leadId')
)
)
->setParameter('ownerIds', implode(',', $ownerIds))
->setParameter('leadId', $lead->getId());
return (bool) $q->executeQuery()->fetchOne();
}
public function getContacts(array $contactIds): array
{
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$qb->select('l.*')->from(MAUTIC_TABLE_PREFIX.'leads', 'l')
->where(
$qb->expr()->in('l.id', $contactIds)
);
$results = $qb->executeQuery()->fetchAllAssociative();
if ($results) {
$contacts = [];
foreach ($results as $result) {
$contacts[$result['id']] = $result;
}
return $contacts;
}
return [];
}
/**
* @return ArrayCollection<int, Lead>
*/
public function getContactCollection(array $ids): ArrayCollection
{
if (empty($ids)) {
return new ArrayCollection();
}
$contacts = $this->getEntities(
[
'filter' => [
'force' => [
[
'column' => 'l.id',
'expr' => 'in',
'value' => $ids,
],
],
],
'orderBy' => 'l.id',
'orderByDir' => 'asc',
'withPrimaryCompany' => true,
'withChannelRules' => true,
]
);
return new ArrayCollection($contacts);
}
public function getTableAlias(): string
{
return 'l';
}
/**
* Get the count of identified contacts.
*/
public function getIdentifiedContactCount(): int
{
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select('count(*)')
->from($this->getTableName(), $this->getTableAlias());
$qb->where(
$qb->expr()->isNotNull($this->getTableAlias().'.date_identified')
);
return (int) $qb->executeQuery()->fetchOne();
}
/**
* @param string[] $uniqueFields
*/
public function getContactCountWithDuplicateValues(array $uniqueFields): int
{
$sql = $this->buildDuplicateValuesQuery($uniqueFields);
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$qb->select('count(*)')->from(sprintf('(%s)', $sql), 'sub');
return (int) $qb->executeQuery()->fetchOne();
}
/**
* @param string[] $uniqueFields
*
* @return string[]
*/
public function getDuplicatedContactIds(array $uniqueFields): array
{
return $this->getEntityManager()->getConnection()->fetchFirstColumn(
$this->buildDuplicateValuesQuery($uniqueFields)
);
}
/**
* Get the next contact after an specific ID; mainly used in deduplication.
*/
public function getNextIdentifiedContact($lastId): ?Lead
{
$alias = $this->getTableAlias();
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select("$alias.id")
->from($this->getTableName(), $this->getTableAlias());
$qb->where(
$qb->expr()->and(
$qb->expr()->gt("$alias.id", (int) $lastId),
$qb->expr()->isNotNull("$alias.date_identified")
)
)
->orderBy("$alias.id")
->setMaxResults(1);
$next = $qb->executeQuery()->fetchOne();
return ($next) ? $this->getEntity($next) : null;
}
/**
* @param array $tables $tables[0] should be primary table
* @param bool $innerJoinTables
* @param mixed $whereExpression
* @param mixed $having
*/
public function applySearchQueryRelationship(QueryBuilder $q, array $tables, $innerJoinTables, $whereExpression = null, $having = null): void
{
$primaryTable = $tables[0];
unset($tables[0]);
$joinType = ($innerJoinTables) ? 'join' : 'leftJoin';
$this->useDistinctCount = true;
if (!preg_match('/"'.preg_quote($primaryTable['alias'], '/').'"/i', json_encode($q->getQueryPart('join')))) {
$q->$joinType(
$primaryTable['from_alias'],
MAUTIC_TABLE_PREFIX.$primaryTable['table'],
$primaryTable['alias'],
$primaryTable['condition']
);
}
foreach ($tables as $table) {
$exists = false;
$joins = $q->getQueryPart('join');
if (isset($joins[$table['from_alias']])) {
foreach ($joins[$table['from_alias']] as $standingJoin) {
if ($standingJoin['joinAlias'] === $table['alias']) { // There can be just one alias
$exists = true;
break;
}
}
}
if (!$exists) {
$q->$joinType(
$table['from_alias'],
MAUTIC_TABLE_PREFIX.$table['table'],
$table['alias'],
$table['condition']
);
}
}
if ($whereExpression) {
$q->andWhere($whereExpression);
}
if ($having) {
$q->andHaving($having);
}
$q->groupBy('l.id');
}
/**
* @param int $tries
*/
protected function updateContactPoints(array $changes, $id, $tries = 1): int
{
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder()
->update(MAUTIC_TABLE_PREFIX.'leads')
->where('id = '.$id);
$ph = 0;
// Keep operator in same order as was used in Lead::adjustPoints() in order to be congruent with what was calculated in PHP
// Again ignoring Aunt Sally here (PEMDAS)
foreach ($changes as $operator => $points) {
$qb->set('points', 'points '.$operator.' :points'.$ph)
->setParameter('points'.$ph, $points, \PDO::PARAM_INT);
++$ph;
}
try {
$qb->executeStatement();
} catch (DriverException $exception) {
$message = $exception->getMessage();
if (str_contains($message, 'Deadlock') && $tries <= 3) {
++$tries;
$this->updateContactPoints($changes, $id, $tries);
}
}
// Query new points
return (int) $this->getEntityManager()->getConnection()->createQueryBuilder()
->select('l.points')
->from(MAUTIC_TABLE_PREFIX.'leads', 'l')
->where('l.id = '.$id)
->executeQuery()
->fetchOne();
}
/**
* @param Lead $entity
*/
protected function postSaveEntity($entity)
{
// Check if points need to be appended
if ($entity->getPointChanges()) {
$newPoints = $this->updateContactPoints($entity->getPointChanges(), $entity->getId());
// Set actual points so that code using getPoints knows the true value
$entity->setActualPoints($newPoints);
$changes = $entity->getChanges();
if (isset($changes['points'])) {
// Let's adjust the points to be more accurate in the change log
$changes['points'][1] = $newPoints;
$entity->setChanges($changes);
}
}
}
protected function prepareDbalFieldsForSave(&$fields)
{
// Do not save points as they are handled by postSaveEntity
unset($fields['points']);
$this->defaultPrepareDbalFieldsForSave($fields);
}
/**
* @param string[] $uniqueFields
*/
private function buildDuplicateValuesQuery(array $uniqueFields): string
{
$fieldsAliases = array_map(fn ($uniqueField) => $this->getTableAlias().'.'.$uniqueField, $uniqueFields);
if ($this->uniqueIdentifiersOperatorIs(CompositeExpression::TYPE_AND)) {
return $this->getDuplicateValuesQuery($fieldsAliases)->getSQL();
}
$queries = array_map(
fn ($fieldAlias) => $this->getDuplicateValuesQuery([$fieldAlias])->getSQL(),
$fieldsAliases
);
$unionQueries = implode(' UNION ', $queries);
return $this->getEntityManager()->getConnection()->createQueryBuilder()
->select('minId')
->from("({$unionQueries})", 'duplicate_values')
->groupBy('minId');
}
/**
* @param string[] $fieldsAliases
*/
private function getDuplicateValuesQuery(array $fieldsAliases): QueryBuilder
{
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select(array_merge(["MIN({$this->getTableAlias()}.id) as minId"], $fieldsAliases))
->from($this->getTableName(), $this->getTableAlias());
$andWhere = [$qb->expr()->isNotNull($this->getTableAlias().'.date_identified')];
foreach ($fieldsAliases as $field) {
$andWhere[] = $qb->expr()->isNotNull($field);
}
$qb->where($qb->expr()->and(...$andWhere));
$qb->groupBy($fieldsAliases);
$qb->having('count(*) > 1');
return $qb;
}
/**
* @return string[]
*/
private function getListIdsByAlias(string $alias): array
{
return $this->getEntityManager()
->getConnection()
->createQueryBuilder()
->select('list.id')
->from(MAUTIC_TABLE_PREFIX.'lead_lists', 'list')
->where('list.alias = :alias')
->setParameter('alias', $alias)
->executeQuery()
->fetchFirstColumn();
}
}