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\Query\Expression\CompositeExpression;
use Doctrine\ORM\QueryBuilder;
use Mautic\CoreBundle\Entity\CommonRepository;
use Mautic\LeadBundle\Event\CompanyBuildSearchEvent;
use Mautic\LeadBundle\LeadEvents;
use Symfony\Component\EventDispatcher\EventDispatcherInterface;
/**
* @extends CommonRepository<Company>
*/
class CompanyRepository extends CommonRepository implements CustomFieldRepositoryInterface
{
use CustomFieldRepositoryTrait;
/**
* @var array
*/
private $availableSearchFields = [];
/**
* @var EventDispatcherInterface|null
*/
private $dispatcher;
/**
* Used by search functions to search using aliases as commands.
*/
public function setAvailableSearchFields(array $fields): void
{
$this->availableSearchFields = $fields;
}
public function setDispatcher(EventDispatcherInterface $dispatcher): void
{
$this->dispatcher = $dispatcher;
}
/**
* @param int $id
*/
public function getEntity($id = 0): ?Company
{
try {
$q = $this->createQueryBuilder($this->getTableAlias());
if (is_array($id)) {
$this->buildSelectClause($q, $id);
$companyId = (int) $id['id'];
} else {
$companyId = $id;
}
$q->andWhere($this->getTableAlias().'.id = '.(int) $companyId);
$entity = $q->getQuery()->getSingleResult();
} catch (\Exception) {
$entity = null;
}
if (null === $entity) {
return null;
}
if ($entity->getFields()) {
// Pulled from Doctrine memory so don't make unnecessary queries as this has already happened
return $entity;
}
$fieldValues = $this->getFieldValues($id, true, 'company');
$entity->setFields($fieldValues);
return $entity;
}
/**
* Get a list of leads.
*
* @return array
*/
public function getEntities(array $args = [])
{
return $this->getEntitiesWithCustomFields('company', $args);
}
/**
* @return \Doctrine\DBAL\Query\QueryBuilder
*/
public function getEntitiesDbalQueryBuilder()
{
return $this->getEntityManager()->getConnection()->createQueryBuilder()
->from(MAUTIC_TABLE_PREFIX.'companies', $this->getTableAlias());
}
/**
* @param mixed[] $args
*
* @return QueryBuilder
*/
public function getEntitiesOrmQueryBuilder($order, array $args=[])
{
$q = $this->getEntityManager()->createQueryBuilder();
$q->select($this->getTableAlias().','.$order)
->from(Company::class, $this->getTableAlias(), $this->getTableAlias().'.id');
return $q;
}
/**
* Get the groups available for fields.
*/
public function getFieldGroups(): array
{
return ['core', 'professional', 'other'];
}
/**
* Get companies by lead.
*/
public function getCompaniesByLeadId($leadId, $companyId = null): array
{
$q = $this->getEntityManager()->getConnection()->createQueryBuilder();
$q->select('comp.*, cl.is_primary')
->from(MAUTIC_TABLE_PREFIX.'companies', 'comp')
->leftJoin('comp', MAUTIC_TABLE_PREFIX.'companies_leads', 'cl', 'cl.company_id = comp.id')
->where('cl.lead_id = :leadId')
->setParameter('leadId', $leadId)
->orderBy('cl.is_primary', 'DESC');
if ($companyId) {
$q->andWhere('comp.id = :companyId')->setParameter('companyId', $companyId);
}
return $q->executeQuery()->fetchAllAssociative();
}
public function getTableAlias(): string
{
return 'comp';
}
protected function addCatchAllWhereClause($q, $filter): array
{
return $this->addStandardCatchAllWhereClause(
$q,
$filter,
[
'comp.companyname',
'comp.companyemail',
]
);
}
protected function addSearchCommandWhereClause($q, $filter): array
{
[$expr, $parameters] = $this->addStandardSearchCommandWhereClause($q, $filter);
$unique = $this->generateRandomParameterName();
$returnParameter = true;
$command = $filter->command;
if (in_array($command, $this->availableSearchFields)) {
$expr = $q->expr()->like($this->getTableAlias().".$command", ":$unique");
}
if ($this->dispatcher) {
$event = new CompanyBuildSearchEvent($filter->string, $filter->command, $unique, $filter->not, $q);
$this->dispatcher->dispatch($event, LeadEvents::COMPANY_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 = $this->getStandardSearchCommands();
if (!empty($this->availableSearchFields)) {
$commands = array_merge($commands, $this->availableSearchFields);
}
return $commands;
}
/**
* @param bool $user
* @param string $id
*
* @return array|mixed
*/
public function getCompanies($user = false, $id = '')
{
$q = $this->_em->getConnection()->createQueryBuilder();
static $companies = [];
if ($user) {
$user = $this->currentUser;
}
$key = (int) $id;
if (isset($companies[$key])) {
return $companies[$key];
}
$q->select('comp.*, cl.is_primary')
->from(MAUTIC_TABLE_PREFIX.'companies', 'comp')
->leftJoin('comp', MAUTIC_TABLE_PREFIX.'companies_leads', 'cl', 'cl.company_id = comp.id');
if (!empty($id)) {
$q->where(
$q->expr()->eq('comp.id', $id)
);
}
if ($user) {
$q->andWhere('comp.created_by = :user');
$q->setParameter('user', $user->getId());
}
$q->orderBy('comp.companyname', 'ASC');
$results = $q->executeQuery()->fetchAllAssociative();
$companies[$key] = $results;
return $results;
}
/**
* Get a count of leads that belong to the company.
*
* @return array
*/
public function getLeadCount($companyIds)
{
$q = $this->_em->getConnection()->createQueryBuilder();
$q->select('count(cl.lead_id) as thecount, cl.company_id')
->from(MAUTIC_TABLE_PREFIX.'companies_leads', 'cl');
$returnArray = is_array($companyIds);
if (!$returnArray) {
$companyIds = [$companyIds];
}
$q->where(
$q->expr()->in('cl.company_id', $companyIds)
)
->groupBy('cl.company_id');
$result = $q->executeQuery()->fetchAllAssociative();
$return = [];
foreach ($result as $r) {
$return[$r['company_id']] = $r['thecount'];
}
// Ensure lists without leads have a value
foreach ($companyIds as $l) {
if (!isset($return[$l])) {
$return[$l] = 0;
}
}
return ($returnArray) ? $return : $return[$companyIds[0]];
}
/**
* Get a list of lists.
*
* @return array
*/
public function identifyCompany($companyName, $city = null, $country = null, $state = null)
{
$q = $this->_em->getConnection()->createQueryBuilder();
if (empty($companyName)) {
return [];
}
$q->select('comp.id, comp.companyname, comp.companycity, comp.companycountry, comp.companystate')
->from(MAUTIC_TABLE_PREFIX.'companies', 'comp');
$q->where(
$q->expr()->eq('comp.companyname', ':companyName')
)->setParameter('companyName', $companyName);
if ($city) {
$q->andWhere(
$q->expr()->eq('comp.companycity', ':city')
)->setParameter('city', $city);
}
if ($country) {
$q->andWhere(
$q->expr()->eq('comp.companycountry', ':country')
)->setParameter('country', $country);
}
if ($state) {
$q->andWhere(
$q->expr()->eq('comp.companystate', ':state')
)->setParameter('state', $state);
}
$results = $q->executeQuery()->fetchAllAssociative();
return ($results) ? $results[0] : null;
}
public function getCompaniesForContacts(array $contacts): array
{
if (!$contacts) {
return [];
}
$qb = $this->getEntityManager()->getConnection()->createQueryBuilder();
$qb->select('c.*, l.lead_id, l.is_primary')
->from(MAUTIC_TABLE_PREFIX.'companies', 'c')
->join('c', MAUTIC_TABLE_PREFIX.'companies_leads', 'l', 'l.company_id = c.id')
->where(
$qb->expr()->and(
$qb->expr()->in('l.lead_id', $contacts)
)
)
->orderBy('l.date_added, l.company_id', 'DESC'); // primary should be [0]
$companies = $qb->executeQuery()->fetchAllAssociative();
// Group companies per contact
$contactCompanies = [];
foreach ($companies as $company) {
if (!isset($contactCompanies[$company['lead_id']])) {
$contactCompanies[$company['lead_id']] = [];
}
$contactCompanies[$company['lead_id']][] = $company;
}
return $contactCompanies;
}
/**
* Get companies grouped by column.
*
* @param QueryBuilder $query
*
* @return array
*
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getCompaniesByGroup($query, $column)
{
$query->select('count(comp.id) as companies, '.$column)
->addGroupBy($column)
->andWhere(
$query->expr()->andX(
$query->expr()->isNotNull($column),
$query->expr()->neq($column, $query->expr()->literal(''))
)
);
return $query->execute()->fetchAllAssociative();
}
/**
* @param int $limit
* @param int $offset
*
* @return mixed
*/
public function getMostCompanies($query, $limit = 10, $offset = 0)
{
$query->setMaxResults($limit)
->setFirstResult($offset);
return $query->executeQuery()->fetchAllAssociative();
}
/**
* @param string $valueColumn
*/
public function getAjaxSimpleList(CompositeExpression $expr = null, array $parameters = [], $labelColumn = null, $valueColumn = 'id'): array
{
$q = $this->_em->getConnection()->createQueryBuilder();
$alias = $prefix = $this->getTableAlias();
if (!empty($prefix)) {
$prefix .= '.';
}
$tableName = $this->_em->getClassMetadata($this->getEntityName())->getTableName();
$class = '\\'.$this->getClassName();
$reflection = new \ReflectionClass(new $class());
// Get the label column if necessary
if (null == $labelColumn) {
if ($reflection->hasMethod('getTitle')) {
$labelColumn = 'title';
} else {
$labelColumn = 'name';
}
}
$q->select($prefix.$valueColumn.' as value,
case
when (comp.companycountry is not null and comp.companycity is not null) then concat(comp.companyname, \' <small>\', companycity,\', \', companycountry, \'</small>\')
when (comp.companycountry is not null) then concat(comp.companyname, \' <small>\', comp.companycountry, \'</small>\')
when (comp.companycity is not null) then concat(comp.companyname, \' <small>\', comp.companycity, \'</small>\')
else comp.companyname
end
as label')
->from($tableName, $alias)
->orderBy($prefix.$labelColumn);
if (null !== $expr && $expr->count()) {
$q->where($expr);
}
if (!empty($parameters)) {
$q->setParameters($parameters);
}
// Published only
if ($reflection->hasMethod('getIsPublished')) {
$q->andWhere(
$q->expr()->eq($prefix.'is_published', ':true')
)
->setParameter('true', true, 'boolean');
}
return $q->executeQuery()->fetchAllAssociative();
}
/**
* Get list of company Ids by unique field data.
*
* @param iterable<mixed> $uniqueFieldsWithData An array of columns & values to filter by
* @param int|null $companyId The current company id. Added to query to skip and find other companies
* @param int|null $limit Limit count of results to return
*
* @return array<array{id: string}>
*/
public function getCompanyIdsByUniqueFields($uniqueFieldsWithData, ?int $companyId = null, ?int $limit = null): array
{
return $this->getCompanyFieldsByUniqueFields($uniqueFieldsWithData, 'c.id', $companyId, $limit);
}
/**
* Get list of company Ids by unique field data.
*
* @param iterable<mixed> $uniqueFieldsWithData An array of columns & values to filter by
* @param int|null $companyId The current company id. Added to query to skip and find other companies
* @param int|null $limit Limit count of results to return
*
* @return array<array{id: string}>
*/
public function getCompanyFieldsByUniqueFields($uniqueFieldsWithData, string $select, ?int $companyId = null, ?int $limit = null): array
{
$q = $this->getEntityManager()->getConnection()->createQueryBuilder()
->select($select)
->from(MAUTIC_TABLE_PREFIX.'companies', 'c');
// loop through the fields and
foreach ($uniqueFieldsWithData as $col => $val) {
$q->{$this->getUniqueIdentifiersWherePart()}("c.$col = :".$col)
->setParameter($col, $val);
}
// if we have a company ID lets use it
if ($companyId > 0) {
// make sure that it's not the id we already have
$q->andWhere('c.id != :companyId')
->setParameter('companyId', $companyId);
}
if ($limit > 0) {
$q->setMaxResults($limit);
}
return $q->executeQuery()->fetchAllAssociative();
}
/**
* @return Company[]
*/
public function getCompaniesByUniqueFields(array $uniqueFieldsWithData, int $companyId = null, int $limit = null): array
{
$results = $this->getCompanyFieldsByUniqueFields($uniqueFieldsWithData, 'c.*', $companyId, $limit);
// Collect the IDs
$companies = [];
foreach ($results as $r) {
$companies[(int) $r['id']] = $r;
}
$q = $this->getEntityManager()->createQueryBuilder()
->select('c')
->from(Company::class, 'c');
$q->where(
$q->expr()->in('c.id', ':ids')
)
->setParameter('ids', array_keys($companies))
->orderBy('c.dateAdded', \Doctrine\Common\Collections\Criteria::DESC)
->addOrderBy('c.id', \Doctrine\Common\Collections\Criteria::DESC);
$entities = $q->getQuery()
->getResult();
/** @var Company $company */
foreach ($entities as $company) {
$company->setFields(
$this->formatFieldValues($companies[$company->getId()], true, 'company')
);
}
return $entities;
}
}