Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\Enum\BusinessModelEnum;
use App\Entity\Freelancer;
use App\Entity\HearingBrand;
use App\Entity\Partner;
use App\Entity\Store;
use App\Entity\User;
use DateTime;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use InvalidArgumentException;
use Symfony\Bridge\Doctrine\RegistryInterface;
/**
* @method Store|null find($id, $lockMode = null, $lockVersion = null)
* @method Store|null findOneBy(array $criteria, array $orderBy = null)
* @method Store[] findAll()
* @method Store[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class StoreRepository extends AbstractEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Store::class);
}
/**
* Return stores managed by the user with estimatedReach
* @param User $user
* @return mixed
*/
public function findStoresOfUserWithEstimatedReach(User $user)
{
$qb = $this->findQBStoreOfUser($user);
$qb->leftJoin('s.estimatedReach', 'er');
$qb->addSelect('er');
return $qb->getQuery()->getResult();
}
/**
* Return QB of the store managed by the user
* @param User $user
* @return \Doctrine\ORM\QueryBuilder
*/
public function findQBStoreOfUser(User $user)
{
return $this->createQueryBuilder('s')
->join('s.administrator', 'ad')
->andWhere('ad.user = :user')
->setParameter('user', $user);
}
/**
* Return nb of stores disabled
* @param User $user
* @return mixed
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function findNbOfDisabledStores(User $user)
{
$qb = $this->findQBStoreOfUser($user);
$qb
->select('count(s)')
->andWhere('s.acquisitionEnabled = false')
->andWhere('s.deletedAt is not null')
->andWhere('s.state = :valid')
->setParameter('valid', Store::STATE_VALID);
return $qb->getQuery()->getSingleScalarResult();
}
/**
* Return stores not associated to a Admin
* @return mixed
*/
public function findQBStore()
{
$qb = $this->createQueryBuilder('s');
return $qb;
}
/**
* Return store around specific given point
*
* @param $latitude
* @param $longitude
* @param $distance
* @return mixed
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function findNearestStoreAroundLocation($latitude, $longitude, $distance)
{
$qb = $this->createQueryBuilder('s');
$qb = $this->addPositionCriteria($qb, $latitude, $longitude, $distance, 1);
return $qb->getQuery()->getOneOrNullResult();
}
/**
*
* Trouver les stores actifs présentables aux utilisateurs selon le rayon $distance
*
* @param $latitude
* @param $longitude
* @param $distance
* @return mixed
*/
public function findActiveStoresAroundLocation($latitude, $longitude, $distance)
{
// only valid stores
$qb = $this->createQueryBuilder('s')
->andWhere('s.state = :state')
->andWhere('s.deletedAt is NULL')
->setParameters(['state' => 'valid'])
->leftJoin('s.hearingBrand', 'hb')->addSelect('hb')
->leftJoin('s.administrator', 'ad')->addSelect('ad')
->leftJoin('s.storeCentralPurchasingRelations', 'scpr')->addSelect('scpr');
$qb = $this->addPositionCriteria($qb, $latitude, $longitude, $distance);
$this->addStoreIndexationCriteria($qb, 's', 'ad');
return $qb->getQuery()->getResult();
}
private function addStoreIndexationCriteria(QueryBuilder $qb, string $storeAlias = 'S', string $adminAlias = 'A')
{
$qb->leftJoin($storeAlias . '.storeIndexations', 'si', 'WITH', 'si.startAt <= :now and si.endAt >= :now')->setParameter('now', new \DateTime());
$qb->andWhere('( (' . $adminAlias . ' instance of App\Entity\Partner and ' . $adminAlias . '.isCustomer = true' . ' ) or (si.id is not null) or ' . $adminAlias . '.businessModelDeadline is not null )');
}
/**
* Return store around a specific given point for hearingBrand
*
* @return mixed
*/
public function findStoreAroundLocationForHearingBrand($latitude, $longitude, $distance, HearingBrand $hearingBrand)
{
$qb = $this->createQueryBuilder('s')
->join('s.hearingBrand', 'ad')
->andWhere('ad.id = :hearingBrand')
->setParameter('hearingBrand', $hearingBrand->getId());
$qb = $this->addPositionCriteria($qb, $latitude, $longitude, $distance, 1);
return $qb->getQuery()->getOneOrNullResult();
}
/**
* Return uniq store around specific given point for all partner
* @return mixed
*/
public function findStoreAroundLocationExceptPartner($latitude, $longitude, $distance)
{
$qb = $this->createQueryBuilder('s')
->where('s.administrator IS NULL');
//->where('ad NOT INSTANCE OF :class' )
//->setParameter('class', $this->getEntityManager()->getClassMetadata('App\Entity\Partner'));
//->setParameter('partner', $partner->getId());
$qb = $this->addPositionCriteria($qb, $latitude, $longitude, $distance);
return $qb;
}
public function findPreferredStore($latitude, $longitude, $distance)
{
}
/**
* Return uniq store around specific given point for customer MCA
* @return mixed
*/
public function findNoCustomerStoreAroundLocation($latitude, $longitude, $distance)
{
$qb = $this->createQueryBuilder('s')
->join('App\Entity\Administrator', 'a')
->where('a.isCustomer = false');
$qb = $this->addPositionCriteria($qb, $latitude, $longitude, $distance);
return $qb;
}
/**
* Return uniq store around specific given point for all partner
* @return mixed
*/
public function findStoreAroundLocationForPartner($latitude, $longitude, $distance)
{
$qb = $this->createQueryBuilder('s')
->join('s.administrator', 'ad')
->where('ad.isCustomer = true');
// ->setParameter('class', $this->getEntityManager()->getClassMetadata('App\Entity\Partner'));
//->setParameter('partner', $partner->getId());
$qb = $this->addPositionCriteria($qb, $latitude, $longitude, $distance);
return $qb;
}
/**
* Return stores not associated to a Admin around a given point
* //TODO add geoloc query
* @return mixed
*/
public function findStoreWithoutAdminAroundLocation($latitude, $longitude)
{
$qb = $this->findQBStoreWithoutAdmin();
$qb = $this->addPositionCriteria($qb, $latitude, $longitude);
$qb
->andWhere('s.administrator is null')
->setMaxResults(20);;
return $qb->getQuery()->getResult();
}
/**
* Return stores not associated to a Admin
* @return mixed
*/
public function findQBStoreWithoutAdmin()
{
$qb = $this->createQueryBuilder('s')
->andWhere('s.administrator is null')
->setMaxResults(20);;
return $qb;
}
/**
* Selectionner les centres éligibles à la livraison de prospect (algorithme de tri : SAW)
* Conditions :
* - ratio capping non atteint
* - est client MCA
* - est en acquisition enabled
* - pas de soft delete
* - est dans le reach par défaut (distanceMAX) et le reach imposé pour le centre (REACH)
* - (si geocoding est égal sur les 2 points, DISTANCE vaut null)
*
* @param $latitude
* @param $longitude
* @param int $distanceMax
* @param bool $isDealWithCapping
* @param bool $isDealWithFreelancer
* @param array $excludedAdmin array of admins to exclude
* @return mixed
*/
public function findStoreBySawAnalysis(
$latitude,
$longitude,
$distanceMax = Store::AREA_SEARCH,
$isDealWithCapping = true,
$isDealWithFreelancer = true,
array $excludedAdmin = [],
$includeBusinessModelPerformance = true,
int $ageLimit = null
)
{
$qb = $this->createQueryBuilder('S')
->addSelect(self::earthRadius . ' * acos(cos(radians(:latitude)) * cos(radians(S.latitude)) * cos(radians(S.longitude) - radians(:longitude)) + sin(radians(:latitude)) * sin(radians(S.latitude))) as DISTANCE')
->addSelect('A.monthlyCapping AS CAPPINGLIMIT')
->addSelect('A.leadCost AS LEADCOST')
->addSelect('A.numberOfLeadMonthly AS NBLEAD')
->join('S.administrator', 'A')
->leftJoin('S.storeCentralPurchasingRelations', 'scpr')
->leftJoin('scpr.centralPurchasing', 'cp')
->andWhere('A.isCustomer = true or (scpr.enabled = true and cp.nbCredit > 0) or (A.businessModel = :model_perf)')->setParameter('model_perf', BusinessModelEnum::PERFORMANCE)
->andWhere('S.acquisitionEnabled = true');
$this->addStoreIndexationCriteria($qb, 'S', 'A');
// Exclure les freelancer de la recherche
if (!$isDealWithFreelancer) {
$qb->andWhere('A NOT INSTANCE OF ' . Freelancer::class);
}
$qb
->groupBy('S')
->having('DISTANCE <= :distanceMax and (DISTANCE <= S.reach
OR S.reach is null)
OR DISTANCE is null')
->orderBy('DISTANCE');
if ($isDealWithCapping) {
$qb->andHaving('CAPPINGLIMIT is null
OR NBLEAD is null
OR NBLEAD < CAPPINGLIMIT');
}
if (!empty($excludedAdmin)) {
$qb->andWhere('A NOT IN (:admins) ')->setParameter('admins', $excludedAdmin);
}
if (!$includeBusinessModelPerformance) {
$qb
->andWhere('A.businessModel is null or A.businessModel != :model_perf')
->setParameter('model_perf', BusinessModelEnum::PERFORMANCE);
}
if ($ageLimit !== null) {
$qb
->andWhere('A.ageCapping is null or A.ageCapping <= :ageLimit')
->setParameter('ageLimit', $ageLimit);
}
$qb->setParameter('latitude', $latitude);
$qb->setParameter('longitude', $longitude);
$qb->setParameter('distanceMax', $distanceMax);
$results = $qb->getQuery()->getResult();
return $results;
}
/**
* Selectionner les centres éligibles à la livraison de prospect sans capping (algorithme de tri : SAW)
* Conditions :
* - est client MCA
* - est en acquisition enabled
* - pas de soft delete
* - est dans le reach par défaut (distanceMAX) et le reach imposé pour le centre (REACH)
* - (si geocoding est égal sur les 2 points, DISTANCE vaut null)
*
* @param $latitude
* @param $longitude
* @param int $distanceMax
* @return mixed
* @deprecated
*/
public function findStoreBySawAnalysisWithoutCapping($latitude, $longitude, $distanceMax = Store::AREA_SEARCH)
{
$em = $this->getEntityManager();
// OR S.reach is null permet de selectionner tous les stores sans reach
/*
$query = $em->createQuery('SELECT S, (' . self::earthRadius . ' * acos(cos(radians(:latitude)) * cos(radians(S.latitude)) * cos(radians(S.longitude) - radians(:longitude)) + sin(radians(:latitude)) * sin(radians(S.latitude)))) AS DISTANCE,
A.monthlyCapping AS CAPPINGLIMIT, A.leadCost AS LEADCOST,
A.numberOfLeadMonthly AS NBLEAD
FROM App\Entity\Store S
INNER JOIN App\Entity\Administrator A WITH A.id = S.administrator
LEFT JOIN App\Entity\CentralPurchasing CP WITH CP MEMBER OF S.centralPurchasingBodies
WHERE (A.isCustomer = true or CP.nbCredit > 0)
AND S.acquisitionEnabled = true
AND S.deletedAt IS NULL
GROUP BY S
HAVING (DISTANCE <= :distanceMax
AND (DISTANCE <= S.reach
OR S.reach is null)
OR DISTANCE is null)
ORDER BY DISTANCE
')->setParameters([
'latitude' => $latitude,
'longitude' => $longitude,
'distanceMax' => $distanceMax,
]);
$results = $query->getResult();
*/
$qb = $this->createQueryBuilder('S')
->addSelect(self::earthRadius . ' * acos(cos(radians(:latitude)) * cos(radians(S.latitude)) * cos(radians(S.longitude) - radians(:longitude)) + sin(radians(:latitude)) * sin(radians(S.latitude))) as DISTANCE')
->addSelect('A.monthlyCapping AS CAPPINGLIMIT')
->addSelect('A.leadCost AS LEADCOST')
->addSelect('A.numberOfLeadMonthly AS NBLEAD')
->join('S.administrator', 'A')
->leftJoin('S.centralPurchasingBodies', 'CP')
->andWhere('A.isCustomer = true or CP.nbCredit > 0')
->andWhere('S.acquisitionEnabled = true')
->groupBy('S')
->having('DISTANCE <= :distanceMax and (DISTANCE <= S.reach
OR S.reach is null)
OR DISTANCE is null')
->orderBy('DISTANCE');
$qb->setParameters([
'latitude' => $latitude,
'longitude' => $longitude,
'distanceMax' => $distanceMax,
]);
$results = $qb->getQuery()->getResult();
return $results;
}
/**
* Return all store order by estimated reach computation date
* @return mixed
*/
public function findStoreByDateTo()
{
$qb = $this->createQueryBuilder('s');
$qb
->leftJoin('s.estimatedReach', 'er')
->orderBy('er.id', 'desc')
->addOrderBy('er.dateTo', 'desc');;
return $qb->getQuery()->getResult();
}
/**
* MCA-784: Force delivery - Find stores for specific administrator IDs
* Ignores business rules (credits, capping, isCustomer, acquisitionEnabled)
* Only filters by distance and administrator ID
*
* @param array $administratorIds IDs des administrateurs ciblés
* @param float $latitude Latitude du prospect
* @param float $longitude Longitude du prospect
* @param int $distanceMax Distance maximale en km
* @return array|null Array de stores avec distances ou null si aucun résultat
*/
public function findStoresByAdministratorIds(
array $administratorIds,
$latitude,
$longitude,
$distanceMax = Store::AREA_SEARCH
)
{
$qb = $this->createQueryBuilder('S')
->select('S', 'A')
->innerJoin('S.administrator', 'A')
->addSelect(self::earthRadius . ' * acos(cos(radians(:latitude)) * cos(radians(S.latitude)) *
cos(radians(S.longitude) - radians(:longitude)) +
sin(radians(:latitude)) * sin(radians(S.latitude))) as DISTANCE')
->where('A.id IN (:adminIds)')
->andWhere('S.deletedAt IS NULL')
->setParameter('adminIds', $administratorIds)
->setParameter('latitude', $latitude)
->setParameter('longitude', $longitude)
->groupBy('S.id')
->having('DISTANCE <= :distanceMax')
->setParameter('distanceMax', $distanceMax)
->orderBy('DISTANCE', 'ASC');
return $qb->getQuery()->getResult();
}
/**
* @param string $phoneNumber
* @param string $email
* @return mixed
* @deprecated use findPosLinkedByProspectEmailOrTel
* Return all store requested by prospect with email or tel
* - for partner, only store created in the last 6 months
* - for freelancer, all stores
*/
public function findStoreLinkedToProspectWithEmailOrTel(string $phoneNumber, string $email)
{
$dateTreshold = new DateTime();
$dateTreshold->modify('-6 month');
$qb = $this->createQueryBuilder('s');
$qb
->join('s.prospectsOnStore', 'pos')
->join('pos.prospect', 'p')
->innerJoin('s.administrator', 'ad')
->andWhere('p.email = :email or p.phoneNumber = :phone')
->andWhere('( (ad INSTANCE OF ' . Partner::class . ' and pos.created > :date) OR ad INSTANCE OF ' . Freelancer::class . ')')->setParameter('date', $dateTreshold)
->setParameter('email', $email)
->setParameter('phone', $phoneNumber)
->orderBy('p.created', 'desc');
return $qb->getQuery()->getResult();
}
/**
* Return stores associated with active freelancer (having an account)
* @return QueryBuilder
*/
public function findQBStoresOfFreelancers(): QueryBuilder
{
$qb = $this->createQueryBuilder('s');
$qb
->innerJoin('s.administrator', 'a')
->andWhere('a INSTANCE OF ' . Freelancer::class)
->orderBy('s.id', 'asc');;
return $qb;
}
/**
* Return stores for sitemap
* @return mixed
*/
public function findStoresForSitemap()
{
$qb = $this->createQueryBuilder('s');
$qb
->andWhere('s.slug is not null')
->andWhere('s.storePage is not null')
->orderBy('s.id', 'desc');
return $qb->getQuery()->getResult();
}
/**
* Find store with Store Page
* @return array
*/
public function findStoresWithStorePage(): array
{
return $this->createQueryBuilder('s')
->where('s.storePage IS NOT NULL')
->orderBy('s.zipCode', 'ASC')
->getQuery()
->getResult();
}
/**
* Find store by departments code.
*
* @param string $departmentCode department code (13, 75, etc...)
* @param int|null $limit Number of store to search.
* @return array list of stores
*/
public function findStoreByDepartment(string $departmentCode, ?int $limit = null): array
{
$qb = $this->createQueryBuilder('s');
$qb->andWhere('SUBSTRING(s.zipCode, 1, 2) = :department')
->setParameter('department', $departmentCode);
if ($limit !== null) {
$qb->setMaxResults($limit);
}
return $qb
->getQuery()
->getResult();
}
/**
* Find store by departments code.
*
* @param array $departments list of departments code (13, 75, etc...)
* @param int|null $limit Number of store to search.
* @return array list of stores
*/
public function findStoreByCity(array $departments, ?int $limit = null): array
{
$qb = $this->createQueryBuilder('s');
$qb->andWhere('SUBSTRING(s.zipCode, 1, 2) IN (:departments)')
->setParameter('departments', $departments);
$qb->groupBy('s.city');
if ($limit !== null) {
$qb->setMaxResults($limit);
}
return $qb
->getQuery()
->getResult();
}
/**
* add criteria to limit to 'active' store:
* - store must have an admin
* - store must have 'acquisition enabled' (admin can disable acquisition on store basis)
* - if admin is a partner, this partner must be enabled (manually)
* - if admin is a freelancer, he must have credit
* @param QueryBuilder $qb
* @param string $storeAlias
*/
private function addEnabledStoresCriteria(QueryBuilder $qb, string $storeAlias = 's')
{
$qb->andWhere($storeAlias . '.acquisitionEnabled = true');
$qb->innerJoin($storeAlias . '.administrator', 'a');
$qb->andWhere('a.isCustomer = true');
}
/**
* add criteria to exlude stores out of their reach
* MUST be used with 'addPositionCriteria' to specify the origin of the distance
* @param QueryBuilder $qb
* @param string $storeAlias
*/
private function addStoreReachCriteria(QueryBuilder $qb, string $storeAlias = 's')
{
$qb->andHaving($storeAlias . '.reach > distance or ' . $storeAlias . '.reach is null');
}
}

src/Repository/StoreRepository.php line 126