src/Repository/ProfileRepository.php line 858

Open in your IDE?
  1. <?php
  2. /**
  3.  * Created by simpson <simpsonwork@gmail.com>
  4.  * Date: 2019-03-19
  5.  * Time: 22:23
  6.  */
  8. namespace App\Repository;
  10. use App\Entity\Location\City;
  11. use App\Entity\Location\MapCoordinate;
  12. use App\Entity\Profile\Genders;
  13. use App\Entity\Profile\Photo;
  14. use App\Entity\Profile\Profile;
  15. use App\Entity\Sales\Profile\AdBoardPlacement;
  16. use App\Entity\Sales\Profile\AdBoardPlacementType;
  17. use App\Entity\Sales\Profile\PlacementHiding;
  18. use App\Entity\User;
  19. use App\Repository\ReadModel\CityReadModel;
  20. use App\Repository\ReadModel\ProfileApartmentPricingReadModel;
  21. use App\Repository\ReadModel\ProfileListingReadModel;
  22. use App\Repository\ReadModel\ProfileMapReadModel;
  23. use App\Repository\ReadModel\ProfilePersonParametersReadModel;
  24. use App\Repository\ReadModel\ProfilePlacementHidingDetailReadModel;
  25. use App\Repository\ReadModel\ProfilePlacementPriceDetailReadModel;
  26. use App\Repository\ReadModel\ProfileTakeOutPricingReadModel;
  27. use App\Repository\ReadModel\ProvidedServiceReadModel;
  28. use App\Repository\ReadModel\StationLineReadModel;
  29. use App\Repository\ReadModel\StationReadModel;
  30. use App\Service\Features;
  31. use App\Specification\Profile\ProfileIdINOrderedByINValues;
  32. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  33. use Doctrine\ORM\AbstractQuery;
  34. use Doctrine\Persistence\ManagerRegistry;
  35. use Doctrine\DBAL\Statement;
  36. use Doctrine\ORM\QueryBuilder;
  37. use Happyr\DoctrineSpecification\Filter\Filter;
  38. use Happyr\DoctrineSpecification\Query\QueryModifier;
  39. use Porpaginas\Doctrine\ORM\ORMQueryResult;
  41. class ProfileRepository extends ServiceEntityRepository
  42. {
  43.     use SpecificationTrait;
  44.     use EntityIteratorTrait;
  46.     private Features $features;
  47.     private DistrictRepository $districts;
  49.     public function __construct(ManagerRegistry $registry, Features $features, DistrictRepository $districts)
  50.     {
  51.         parent::__construct($registry, Profile::class);
  52.         $this->features = $features;
  53.         $this->districts = $districts;
  54.     }
  56.     /**
  57.      * Возвращает итератор по данным, необходимым для генерации файлов sitemap, в виде массивов с
  58.      * следующими ключами:
  59.      *  - id
  60.      *  - uri
  61.      *  - updatedAt
  62.      *  - city_uri
  63.      *
  64.      * @return iterable<array{id: int, uri: string, updatedAt: \DateTimeImmutable, city_uri: string}>
  65.      */
  66.     public function sitemapItemsIterator(): iterable
  67.     {
  68.         $qb = $this->createQueryBuilder('profile')
  69.             ->select('profile.id, profile.uriIdentity AS uri, profile.updatedAt, city.uriIdentity AS city_uri')
  70.             ->join('profile.city', 'city')
  71.             ->andWhere('profile.deletedAt IS NULL');
  72.         $this->addModerationFilterToQb($qb, 'profile');
  74.         return $qb->getQuery()->toIterable([], AbstractQuery::HYDRATE_ARRAY);
  75.     }
  77.     protected function addModerationFilterToQb(QueryBuilder $qb, string $dqlAlias): void
  78.     {
  79.         if ($this->features->hard_moderation()) {
  80.             $qb->leftJoin(sprintf('%s.owner', $dqlAlias), 'owner');
  81.             $qb->andWhere(
  82.                 $qb->expr()->orX(
  83.                     sprintf('%s.moderationStatus = :status_passed', $dqlAlias),
  84.                     $qb->expr()->andX(
  85.                         sprintf('%s.moderationStatus = :status_waiting', $dqlAlias),
  86.                         'owner.trusted = true'
  87.                     )
  88.                 )
  89.             );
  90.             $qb->setParameter('status_passed', Profile::MODERATION_STATUS_APPROVED);
  91.             $qb->setParameter('status_waiting', Profile::MODERATION_STATUS_WAITING);
  92.         } else {
  93.             $qb->andWhere(sprintf('%s.moderationStatus IN (:statuses)', $dqlAlias));
  94.             $qb->setParameter('statuses', [Profile::MODERATION_STATUS_NOT_PASSED, Profile::MODERATION_STATUS_WAITING, Profile::MODERATION_STATUS_APPROVED]);
  95.         }
  96.     }
  98.     public function ofUriIdentityWithinCity(string $uriIdentity, City $city): ?Profile
  99.     {
  100.         return $this->findOneBy([
  101.             'uriIdentity' => $uriIdentity,
  102.             'city' => $city,
  103.         ]);
  104.     }
  106.     /**
  107.      * Метод проверки уникальности анкет по URI не должен использовать никаких фильтров, кроме URI и города,
  108.      * поэтому QueryBuilder не используется
  109.      * @see https://redminez.net/issues/27310
  110.      */
  111.     public function isUniqueUriIdentityExistWithinCity(string $uriIdentity, City $city): bool
  112.     {
  113.         $connection = $this->_em->getConnection();
  114.         $stmt = $connection->executeQuery('SELECT COUNT(id) FROM profiles WHERE uri_identity = ? AND city_id = ?', [$uriIdentity, $city->getId()]);
  115.         $count = $stmt->fetchOne();
  117.         return $count > 0;
  118.     }
  120.     public function countByCity(): array
  121.     {
  122.         $qb = $this->createQueryBuilder('profile')
  123.             ->select('IDENTITY(profile.city), COUNT(profile.id)')
  124.             ->groupBy('profile.city');
  126.         $this->addFemaleGenderFilterToQb($qb, 'profile');
  127.         $this->addModerationFilterToQb($qb, 'profile');
  128.         //$this->excludeHavingPlacementHiding($qb, 'profile');
  129.         $this->havingAdBoardPlacement($qb, 'profile');
  131.         $query = $qb->getQuery()
  132.             ->useResultCache(true)
  133.             ->setResultCacheLifetime(120);
  135.         $rawResult = $query->getScalarResult();
  136.         $indexedResult = [];
  137.         foreach ($rawResult as $row) {
  138.             $indexedResult[$row[1]] = $row[2];
  139.         }
  141.         return $indexedResult;
  142.     }
  144.     protected function addFemaleGenderFilterToQb(QueryBuilder $qb, string $alias): void
  145.     {
  146.         $this->addGenderFilterToQb($qb, $alias, [Genders::FEMALE]);
  147.     }
  149.     protected function addGenderFilterToQb(QueryBuilder $qb, string $alias, array $genders = [Genders::FEMALE]): void
  150.     {
  151.         $qb->andWhere(sprintf('%s.personParameters.gender IN (:genders)', $alias));
  152.         $qb->setParameter('genders', $genders);
  153.     }
  155.     private function havingAdBoardPlacement(QueryBuilder $qb, string $alias): void
  156.     {
  157.         $qb->join(sprintf('%s.adBoardPlacement', $alias), 'adboard_placement');
  158.     }
  160.     public function countByStations(): array
  161.     {
  162.         $qb = $this->createQueryBuilder('profiles')
  163.             ->select('stations.id, COUNT(profiles.id) as cnt')
  164.             ->join('profiles.stations', 'stations')
  165.             //это условие сильно затормжаживает запрос, но оно и не нужно при условии, что чужих(от других городов) станций у анкеты нет
  166.             //->where('profiles.city = stations.city')
  167.             ->groupBy('stations.id');
  169.         $this->addFemaleGenderFilterToQb($qb, 'profiles');
  170.         $this->addModerationFilterToQb($qb, 'profiles');
  171.         //$this->excludeHavingPlacementHiding($qb, 'profiles');
  172.         $this->havingAdBoardPlacement($qb, 'profiles');
  174.         $query = $qb->getQuery()
  175.             ->useResultCache(true)
  176.             ->setResultCacheLifetime(120);
  178.         $rawResult = $query->getScalarResult();
  179.         $indexedResult = [];
  180.         foreach ($rawResult as $row) {
  181.             $indexedResult[$row['id']] = $row['cnt'];
  182.         }
  184.         return $indexedResult;
  185.     }
  187.     public function countByDistricts(): array
  188.     {
  189.         $qb = $this->createQueryBuilder('profiles')
  190.             ->select('districts.id, COUNT(profiles.id) as cnt')
  191.             ->join('profiles.stations', 'stations')
  192.             ->join('stations.district', 'districts')
  193.             ->groupBy('districts.id');
  195.         $this->addFemaleGenderFilterToQb($qb, 'profiles');
  196.         $this->addModerationFilterToQb($qb, 'profiles');
  197.         //$this->excludeHavingPlacementHiding($qb, 'profiles');
  198.         $this->havingAdBoardPlacement($qb, 'profiles');
  200.         $query = $qb->getQuery()
  201.             ->useResultCache(true)
  202.             ->setResultCacheLifetime(120);
  204.         $rawResult = $query->getScalarResult();
  205.         $indexedResult = [];
  206.         foreach ($rawResult as $row) {
  207.             $indexedResult[$row['id']] = $row['cnt'];
  208.         }
  210.         return $indexedResult;
  211.     }
  213.     public function countByCounties(): array
  214.     {
  215.         $qb = $this->createQueryBuilder('profiles')
  216.             ->select('counties.id, COUNT(profiles.id) as cnt')
  217.             ->join('profiles.stations', 'stations')
  218.             ->join('stations.district', 'districts')
  219.             ->join('districts.county', 'counties')
  220.             ->groupBy('counties.id');
  222.         $this->addFemaleGenderFilterToQb($qb, 'profiles');
  223.         $this->addModerationFilterToQb($qb, 'profiles');
  224.         //$this->excludeHavingPlacementHiding($qb, 'profiles');
  225.         $this->havingAdBoardPlacement($qb, 'profiles');
  227.         $query = $qb->getQuery()
  228.             ->useResultCache(true)
  229.             ->setResultCacheLifetime(120);
  231.         $rawResult = $query->getScalarResult();
  232.         $indexedResult = [];
  233.         foreach ($rawResult as $row) {
  234.             $indexedResult[$row['id']] = $row['cnt'];
  235.         }
  237.         return $indexedResult;
  238.     }
  240.     /**
  241.      * @param array|int[] $ids
  242.      * @return Profile[]
  243.      */
  244.     public function findByIds(array $ids): array
  245.     {
  246.         return $this->createQueryBuilder('profile')
  247.             ->andWhere('profile.id IN (:ids)')
  248.             ->setParameter('ids', $ids)
  249.             ->orderBy('FIELD(profile.id,:ids2)')
  250.             ->setParameter('ids2', $ids)
  251.             ->getQuery()
  252.             ->getResult();
  253.     }
  255.     public function findByIdsIterate(array $ids): iterable
  256.     {
  257.         $qb = $this->createQueryBuilder('profile')
  258.             ->andWhere('profile.id IN (:ids)')
  259.             ->setParameter('ids', $ids)
  260.             ->orderBy('FIELD(profile.id,:ids2)')
  261.             ->setParameter('ids2', $ids);
  263.         return $this->iterateQueryBuilder($qb);
  264.     }
  266.     /**
  267.      * Список анкет указанного типа (массажистки или нет), привязанных к аккаунту
  268.      */
  269.     public function ofOwnerAndTypePaged(User $owner, bool $masseurs): ORMQueryResult
  270.     {
  271.         $qb = $this->createQueryBuilder('profile')
  272.             ->andWhere('profile.owner = :owner')
  273.             ->setParameter('owner', $owner)
  274.             ->andWhere('profile.masseur = :is_masseur')
  275.             ->setParameter('is_masseur', $masseurs);
  277.         return new ORMQueryResult($qb);
  278.     }
  280.     /**
  281.      * Список активных анкет, привязанных к аккаунту
  282.      */
  283.     public function activeAndOwnedBy(User $owner): ORMQueryResult
  284.     {
  285.         $qb = $this->createQueryBuilder('profile')
  286.             ->join('profile.adBoardPlacement', 'profile_adboard_placement')
  287.             ->andWhere('profile.owner = :owner')
  288.             ->setParameter('owner', $owner);
  290.         return new ORMQueryResult($qb);
  291.     }
  293.     /**
  294.      * Список активных или скрытых анкет, привязанных к аккаунту
  295.      *
  296.      * @return Profile[]|ORMQueryResult
  297.      */
  298.     public function activeOrHiddenAndOwnedBy(User $owner): ORMQueryResult
  299.     {
  300.         $qb = $this->createQueryBuilder('profile')
  301.             ->leftJoin('profile.adBoardPlacement', 'profile_adboard_placement')
  302.             ->leftJoin('profile.placementHiding', 'placement_hiding')
  303.             ->andWhere('profile_adboard_placement IS NOT NULL OR placement_hiding IS NOT NULL')
  304.             ->andWhere('profile.owner = :owner')
  305.             ->setParameter('owner', $owner);
  307.         return new ORMQueryResult($qb);
  308.     }
  310.     public function activePaidAdBoardPlacementAndOwnedBy(User $owner): ORMQueryResult
  311.     {
  312.         $qb = $this->createQueryBuilder('profile')
  313.             ->addSelect('profile_adboard_placement', 'placement_price', 'city', 'owner')
  314.             ->join('profile.adBoardPlacement', 'profile_adboard_placement')
  315.             ->leftJoin('profile_adboard_placement.placementPrice', 'placement_price')
  316.             ->join('profile.city', 'city')
  317.             ->join('profile.owner', 'owner')
  318.             ->andWhere('profile_adboard_placement.type <> :free_placement_type')
  319.             ->andWhere('profile.owner = :owner')
  320.             ->setParameter('free_placement_type', AdBoardPlacementType::FREE)
  321.             ->setParameter('owner', $owner);
  323.         return new ORMQueryResult($qb);
  324.     }
  326.     public function paidAdBoardPlacementChargeRowsOfOwner(User $owner): array
  327.     {
  328.         $qb = $this->createQueryBuilder('profile')
  329.             ->select([
  330.                 'profile.id AS profile_id',
  331.                 'profile.approved AS approved',
  332.                 'profile.masseur AS is_masseur',
  333.                 'profile.personParameters.gender AS gender',
  334.                 'profile_adboard_placement.type AS placement_type',
  335.                 'profile_adboard_placement.planManaged AS plan_managed',
  336.                 'placement_price.id AS placement_price_id',
  337.                 'placement_price.priceAmount AS price_amount',
  338.                 'placement_price.duration AS duration',
  339.                 'placement_price.currency AS currency',
  340.                 'placement_price.dynamicPriceMatrix AS dynamic_price_matrix',
  341.                 'city.id AS city_id',
  342.                 'city.cityPriceCategory AS city_price_category',
  343.                 'city.timezone AS timezone',
  344.                 'owner.currencyCode AS owner_currency',
  345.             ])
  346.             ->join('profile.adBoardPlacement', 'profile_adboard_placement')
  347.             ->leftJoin('profile_adboard_placement.placementPrice', 'placement_price')
  348.             ->join('profile.city', 'city')
  349.             ->join('profile.owner', 'owner')
  350.             ->andWhere('profile_adboard_placement.type <> :free_placement_type')
  351.             ->andWhere('profile.owner = :owner')
  352.             ->setParameter('free_placement_type', AdBoardPlacementType::FREE)
  353.             ->setParameter('owner', $owner);
  355.         return $qb->getQuery()->getArrayResult();
  356.     }
  358.     public function currentChargeableAndOwnedBy(User $owner): ORMQueryResult
  359.     {
  360.         $qb = $this->createQueryBuilder('profile')
  361.             ->addSelect('profile_adboard_placement', 'placement_price', 'placement_hiding', 'city', 'owner')
  362.             ->leftJoin('profile.adBoardPlacement', 'profile_adboard_placement')
  363.             ->leftJoin('profile_adboard_placement.placementPrice', 'placement_price')
  364.             ->leftJoin('profile.placementHiding', 'placement_hiding')
  365.             ->join('profile.city', 'city')
  366.             ->join('profile.owner', 'owner')
  367.             ->andWhere('(profile_adboard_placement IS NOT NULL AND profile_adboard_placement.type <> :free_placement_type) OR placement_hiding IS NOT NULL')
  368.             ->andWhere('profile.owner = :owner')
  369.             ->setParameter('free_placement_type', AdBoardPlacementType::FREE)
  370.             ->setParameter('owner', $owner);
  372.         return new ORMQueryResult($qb);
  373.     }
  375.     public function countFreeUnapprovedLimited(): int
  376.     {
  377.         $qb = $this->createQueryBuilder('profile')
  378.             ->select('count(profile)')
  379.             ->join('profile.adBoardPlacement', 'placement')
  380.             ->andWhere('placement.type = :placement_type')
  381.             ->setParameter('placement_type', AdBoardPlacementType::FREE)
  382.             ->leftJoin('profile.placementHiding', 'hiding')
  383.             ->andWhere('hiding IS NULL')
  384.             ->andWhere('profile.approved = false');
  386.         return (int)$qb->getQuery()->getSingleScalarResult();
  387.     }
  389.     public function iterateFreeUnapprovedLimited(int $limit): iterable
  390.     {
  391.         $qb = $this->createQueryBuilder('profile')
  392.             ->join('profile.adBoardPlacement', 'placement')
  393.             ->andWhere('placement.type = :placement_type')
  394.             ->setParameter('placement_type', AdBoardPlacementType::FREE)
  395.             ->leftJoin('profile.placementHiding', 'hiding')
  396.             ->andWhere('hiding IS NULL')
  397.             ->andWhere('profile.approved = false')
  398.             ->setMaxResults($limit);
  400.         return $this->iterateQueryBuilder($qb);
  401.     }
  403.     /**
  404.      * Число активных анкет, привязанных к аккаунту
  405.      */
  406.     public function countActiveOfOwner(User $owner, ?bool $isMasseur = false): int
  407.     {
  408.         $qb = $this->createQueryBuilder('profile')
  409.             ->select('COUNT(profile.id)')
  410.             ->join('profile.adBoardPlacement', 'profile_adboard_placement')
  411.             ->andWhere('profile.owner = :owner')
  412.             ->setParameter('owner', $owner);
  413.         if ($this->features->hard_moderation()) {
  414.             $qb->leftJoin('profile.owner', 'owner');
  415.             $qb->andWhere(
  416.                 $qb->expr()->orX(
  417.                     'profile.moderationStatus = :status_passed',
  418.                     $qb->expr()->andX(
  419.                         'profile.moderationStatus = :status_waiting',
  420.                         'owner.trusted = true'
  421.                     )
  422.                 )
  423.             );
  424.             $qb->setParameter('status_passed', Profile::MODERATION_STATUS_APPROVED);
  425.             $qb->setParameter('status_waiting', Profile::MODERATION_STATUS_WAITING);
  426.         } else {
  427.             $qb->andWhere('profile.moderationStatus IN (:statuses)')
  428.                 ->setParameter('statuses', [Profile::MODERATION_STATUS_NOT_PASSED, Profile::MODERATION_STATUS_WAITING, Profile::MODERATION_STATUS_APPROVED]);
  429.         }
  430.         if (null !== $isMasseur) {
  431.             $qb->andWhere('profile.masseur = :is_masseur')
  432.                 ->setParameter('is_masseur', $isMasseur);
  433.         }
  435.         return (int)$qb->getQuery()->getSingleScalarResult();
  436.     }
  438.     /**
  439.      * Число всех анкет, привязанных к аккаунту
  440.      */
  441.     public function countAllOfOwnerNotDeleted(User $owner, ?bool $isMasseur = false): int
  442.     {
  443.         $qb = $this->createQueryBuilder('profile')
  444.             ->select('COUNT(profile.id)')
  445.             ->andWhere('profile.owner = :owner')
  446.             ->setParameter('owner', $owner)
  447.             //потому что используется в т.ч. на тех страницах, где отключен фильтр вывода "только неудаленных"
  448.             ->andWhere('profile.deletedAt IS NULL');
  449.         if (null !== $isMasseur) {
  450.             $qb->andWhere('profile.masseur = :is_masseur')
  451.                 ->setParameter('is_masseur', $isMasseur);
  452.         }
  454.         return (int)$qb->getQuery()->getSingleScalarResult();
  455.     }
  457.     public function getTimezonesListByUser(User $owner): array
  458.     {
  459.         $q = $this->_em->createQuery(sprintf("
  460.                 SELECT c
  461.                 FROM %s c
  462.                 WHERE c.id IN (
  463.                     SELECT DISTINCT(c2.id) 
  464.                     FROM %s p
  465.                     JOIN p.city c2
  466.                     WHERE p.owner = :user
  467.                 )
  468.             ", $this->_em->getClassMetadata(City::class)->name, $this->_em->getClassMetadata(Profile::class)->name))
  469.             ->setParameter('user', $owner);
  471.         return $q->getResult();
  472.     }
  474.     /**
  475.      * Список анкет, привязанных к аккаунту
  476.      *
  477.      * @return Profile[]
  478.      */
  479.     public function ofOwner(User $owner): array
  480.     {
  481.         $qb = $this->createQueryBuilder('profile')
  482.             ->andWhere('profile.owner = :owner')
  483.             ->setParameter('owner', $owner);
  485.         return $qb->getQuery()->getResult();
  486.     }
  488.     public function ofOwnerPaged(User $owner, array $genders = [Genders::FEMALE]): ORMQueryResult
  489.     {
  490.         $qb = $this->createQueryBuilder('profile')
  491.             ->andWhere('profile.owner = :owner')
  492.             ->setParameter('owner', $owner)
  493.             ->andWhere('profile.personParameters.gender IN (:genders)')
  494.             ->setParameter('genders', $genders);
  496.         return new ORMQueryResult($qb);
  497.     }
  499.     public function ofOwnerAndMasseurTypeWithPlacementFilterAndNameFilterIterateAll(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): \Generator
  500.     {
  501.         $query = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur)->getQuery();
  503.         foreach ($query->iterate() as $row) {
  504.             yield $row[0];
  505.         }
  506.     }
  508.     private function queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): QueryBuilder
  509.     {
  510.         $qb = $this->createQueryBuilder('profile')
  511.             ->andWhere('profile.owner = :owner')
  512.             ->setParameter('owner', $owner);
  514.         switch ($placementTypeFilter) {
  515.             case 'paid':
  516.                 $qb->join('profile.adBoardPlacement', 'placement')
  517.                     ->andWhere('placement.type != :placement_type')
  518.                     ->setParameter('placement_type', AdBoardPlacementType::FREE);
  519.                 break;
  520.             case 'free':
  521.                 $qb->join('profile.adBoardPlacement', 'placement')
  522.                     ->andWhere('placement.type = :placement_type')
  523.                     ->setParameter('placement_type', AdBoardPlacementType::FREE);
  524.                 break;
  525.             case 'ultra-vip':
  526.                 $qb->join('profile.adBoardPlacement', 'placement')
  527.                     ->andWhere('placement.type = :placement_type')
  528.                     ->setParameter('placement_type', AdBoardPlacementType::ULTRA_VIP);
  529.                 break;
  530.             case 'vip':
  531.                 $qb->join('profile.adBoardPlacement', 'placement')
  532.                     ->andWhere('placement.type = :placement_type')
  533.                     ->setParameter('placement_type', AdBoardPlacementType::VIP);
  534.                 break;
  535.             case 'standard':
  536.                 $qb->join('profile.adBoardPlacement', 'placement')
  537.                     ->andWhere('placement.type = :placement_type')
  538.                     ->setParameter('placement_type', AdBoardPlacementType::STANDARD);
  539.                 break;
  540.             case 'hidden':
  541.                 $qb->join('profile.placementHiding', 'placement_hiding');
  542.                 break;
  543.             case 'all':
  544.             default:
  545.                 break;
  546.         }
  548.         if ($nameFilter) {
  549.             $nameExpr = $qb->expr()->orX(
  550.                 'LOWER(JSON_UNQUOTE(JSON_EXTRACT(profile.name, :jsonPath))) LIKE :name_filter',
  551.                 \sprintf("REGEXP_REPLACE(profile.phoneNumber, '-| ', '') LIKE :name_filter"),
  552.                 'LOWER(profile.phoneNumber) LIKE :name_filter',
  553.                 \sprintf("REGEXP_REPLACE(profile.phoneNumber, '\+7', '8') LIKE :name_filter"),
  554.             );
  555.             $qb->setParameter('jsonPath', '$.ru');
  556.             $qb->setParameter('name_filter', '%' . addcslashes(mb_strtolower(str_replace(['(', ')', ' ', '-'], '', $nameFilter)), '%_') . '%');
  558.             $qb->andWhere($nameExpr);
  559.         }
  561.         if (null !== $isMasseur) {
  562.             $qb->andWhere('profile.masseur = :is_masseur')
  563.                 ->setParameter('is_masseur', $isMasseur);
  564.         }
  566.         return $qb;
  567.     }
  569.     public function ofOwnerAndMasseurTypeWithPlacementFilterAndNameFilterPaged(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): ORMQueryResult
  570.     {
  571.         $qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
  573.         //сортируем анкеты по статусу UltraVip->Vip->Standard->Free->Hidden
  574.         $aliases = $qb->getAllAliases();
  575.         if (false == in_array('placement', $aliases))
  576.             $qb->leftJoin('profile.adBoardPlacement', 'placement');
  577.         if (false == in_array('placement_hiding', $aliases))
  578.             $qb->leftJoin('profile.placementHiding', 'placement_hiding');
  579.         $qb->addSelect('IF(placement_hiding.id IS NULL, 0, 1) as HIDDEN is_hidden');
  580.         $qb->addOrderBy('placement.type', 'DESC');
  581.         $qb->addOrderBy('placement.placedAt', 'DESC');
  582.         $qb->addOrderBy('is_hidden', 'ASC');
  584.         return new ORMQueryResult($qb);
  585.     }
  587.     public function idsOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): array
  588.     {
  589.         $qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
  590.         $qb->select('profile.id');
  592.         return $qb->getQuery()->getResult('column_hydrator');
  593.     }
  595.     public function countOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): int
  596.     {
  597.         $qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
  598.         $qb->select('count(profile.id)')
  599.             ->setMaxResults(1);
  601.         return (int)$qb->getQuery()->getSingleScalarResult();
  602.     }
  604.     /**
  605.      * @deprecated
  606.      */
  607.     public function hydrateProfileRow(array $row): ProfileListingReadModel
  608.     {
  609.         $profile = new ProfileListingReadModel();
  610.         $profile->id = $row['id'];
  611.         $profile->city = $row['city'];
  612.         $profile->uriIdentity = $row['uriIdentity'];
  613.         $profile->name = $row['name'];
  614.         $profile->description = $row['description'];
  615.         $profile->phoneNumber = $row['phoneNumber'];
  617.         $profile->approved = $row['approved'];
  619.         $now = new \DateTimeImmutable('now');
  620.         $hasRunningTopPlacement = false;
  621.         foreach ($row['topPlacements'] as $topPlacement) {
  622.             if ($topPlacement['placedAt'] <= $now && $now <= $topPlacement['expiresAt'])
  623.                 $hasRunningTopPlacement = true;
  624.         }
  625.         $profile->active = null !== $row['adBoardPlacement'] || $hasRunningTopPlacement;
  627.         $profile->hidden = null != $row['placementHiding'];
  629.         $profile->personParameters = new ProfilePersonParametersReadModel();
  630.         $profile->personParameters->age = $row['personParameters.age'];
  631.         $profile->personParameters->height = $row['personParameters.height'];
  632.         $profile->personParameters->weight = $row['personParameters.weight'];
  633.         $profile->personParameters->breastSize = $row['personParameters.breastSize'];
  634.         $profile->personParameters->bodyType = $row['personParameters.bodyType'];
  635.         $profile->personParameters->hairColor = $row['personParameters.hairColor'];
  636.         $profile->personParameters->privateHaircut = $row['personParameters.privateHaircut'];
  637.         $profile->personParameters->nationality = $row['personParameters.nationality'];
  638.         $profile->personParameters->hasTattoo = $row['personParameters.hasTattoo'];
  639.         $profile->personParameters->hasPiercing = $row['personParameters.hasPiercing'];
  641.         $profile->stations = $row['stations'];
  643.         $profile->avatar = $row['avatar'];
  645.         foreach ($row['photos'] as $photo)
  646.             if ($photo['main'])
  647.                 $profile->mainPhoto = $photo;
  650.         $profile->mainPhoto = null;
  651.         $profile->photos = [];
  652.         $profile->selfies = [];
  653.         foreach ($row['photos'] as $photo) {
  654.             if ($photo['main'])
  655.                 $profile->mainPhoto = $photo;
  657.             if ($photo['type'] == Photo::TYPE_PHOTO)
  658.                 $profile->photos[] = $photo;
  660.             if ($photo['type'] == Photo::TYPE_SELFIE)
  661.                 $profile->selfies[] = $photo;
  662.         }
  664.         $profile->videos = $row['videos'];
  665.         $profile->comments = $row['comments'];
  667.         $profile->apartmentsPricing = new ProfileApartmentPricingReadModel();
  668.         $profile->apartmentsPricing->oneHourPrice = $row['apartmentsPricing.oneHourPrice'];
  669.         $profile->apartmentsPricing->twoHoursPrice = $row['apartmentsPricing.twoHoursPrice'];
  670.         $profile->apartmentsPricing->nightPrice = $row['apartmentsPricing.nightPrice'];
  672.         $profile->takeOutPricing = new ProfileTakeOutPricingReadModel();
  673.         $profile->takeOutPricing->oneHourPrice = $row['takeOutPricing.oneHourPrice'];
  674.         $profile->takeOutPricing->twoHoursPrice = $row['takeOutPricing.twoHoursPrice'];
  675.         $profile->takeOutPricing->nightPrice = $row['takeOutPricing.nightPrice'];
  677.         return $profile;
  678.     }
  680.     public function deletedByPeriod(\DateTimeInterface $start, \DateTimeInterface $end): array
  681.     {
  682.         $qb = $this->createQueryBuilder('profile')
  683.             ->join('profile.city', 'city')
  684.             ->select('profile.uriIdentity _profile')
  685.             ->addSelect('city.uriIdentity _city')
  686.             ->andWhere('profile.deletedAt >= :start')
  687.             ->andWhere('profile.deletedAt <= :end')
  688.             ->setParameter('start', $start)
  689.             ->setParameter('end', $end);
  690.         return $qb->getQuery()->getResult();
  691.     }
  693.     public function listForMapMatchingSpec(Filter|QueryModifier $specification, int $coordinatesRoundPrecision = 3): array
  694.     {
  695.         $this->getEntityManager()->getConnection()->executeQuery("
  696.             SET SESSION group_concat_max_len = 100000;
  697.         ");
  699.         /** @var QueryBuilder $qb */
  700.         $qb = $this->createQueryBuilder($dqlAlias = 'p');
  701.         $qb->select(sprintf('GROUP_CONCAT(p.id), CONCAT(ROUND(MIN(p.mapCoordinate.latitude),5),\',\',ROUND(MIN(p.mapCoordinate.longitude),5)), count(p.id), CONCAT(ROUND(p.mapCoordinate.latitude,%1$s),\',\',ROUND(p.mapCoordinate.longitude,%1$s)) as coords, GROUP_CONCAT(p.masseur)', $coordinatesRoundPrecision));
  702.         $qb->groupBy('coords');
  704.         $specification->modify($qb, $dqlAlias);
  705.         $qb->andWhere($specification->getFilter($qb, $dqlAlias));
  707.         return $qb->getQuery()->getResult();
  708.     }
  710.     public function fetchListingByIds(ProfileIdINOrderedByINValues $specification): array
  711.     {
  712.         $ids = implode(',', $specification->getIds());
  714.         $mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
  715.         $mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
  717.         $sql = "
  718.             SELECT 
  719.                 p.*, JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru')) 
  720.                     as `name`, 
  721.                 JSON_UNQUOTE(JSON_EXTRACT(p.description, '$.ru')) 
  722.                     as `description`,
  723.                 (SELECT path FROM profile_media_files pmf_avatar WHERE p.id = pmf_avatar.profile_id AND pmf_avatar.type = '{$mediaType}' AND pmf_avatar.is_main = {$mediaIsMain} LIMIT 1) 
  724.                     as `avatar_path`,
  725.                 (SELECT type FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1) 
  726.                     as `adboard_placement_type`,
  727.                 (SELECT position FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1) 
  728.                     as `adboard_placement_position`,
  729.                 c.id 
  730.                     as `city_id`, 
  731.                 JSON_UNQUOTE(JSON_EXTRACT(c.name, '$.ru')) 
  732.                     as `city_name`, 
  733.                 c.uri_identity 
  734.                     as `city_uri_identity`,
  735.                 c.country_code 
  736.                     as `city_country_code`,
  737.                 EXISTS(SELECT * FROM profile_top_placements ptp WHERE p.id = ptp.profile_id AND (NOW() BETWEEN ptp.placed_at AND ptp.expires_at))
  738.                     as `has_top_placement`,
  739.                 EXISTS(SELECT * FROM placement_hidings ph WHERE p.id = ph.profile_id AND ph.entity_type = 'profile') 
  740.                     as `has_placement_hiding`,
  741.                 EXISTS(SELECT * FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL) 
  742.                     as `has_comments`,
  743.                 EXISTS(SELECT * FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video') 
  744.                     as `has_videos`,
  745.                 EXISTS(SELECT * FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie') 
  746.                     as `has_selfies`
  747.             FROM profiles `p`
  748.             JOIN cities `c` ON c.id = p.city_id 
  749.             WHERE p.id IN ($ids)
  750.             ORDER BY FIELD(p.id,$ids)";
  752.         $connection = $this->getEntityManager()->getConnection();
  753.         $result = $connection->executeQuery($sql);
  754.         $profiles = $result->fetchAllAssociative();
  756.         $sql = "SELECT 
  757.                     cs.id 
  758.                         as `id`,
  759.                     JSON_UNQUOTE(JSON_EXTRACT(cs.name, '$.ru')) 
  760.                         as `name`, 
  761.                     cs.uri_identity 
  762.                         as `uriIdentity`, 
  763.                     ps.profile_id
  764.                         as `profile_id`,
  765.                     csl.name
  766.                         as `line_name`,
  767.                     csl.color
  768.                         as `line_color`,
  769.                     cs.county_id, cs.district_id
  770.                 FROM profile_stations ps
  771.                 JOIN city_stations cs ON ps.station_id = cs.id 
  772.                 LEFT JOIN city_subway_station_lines cssl ON cssl.station_id = cs.id
  773.                 LEFT JOIN city_subway_lines csl ON csl.id = cssl.line_id
  774.                 WHERE ps.profile_id IN ($ids)";
  775.         $result = $connection->executeQuery($sql);
  776.         $stations = $result->fetchAllAssociative();
  777.         $districtIds = array_unique(array_column($stations, 'district_id'));
  778.         $districts = $this->districts->ofIds($districtIds);
  780.         $sql = "SELECT 
  781.                     s.id 
  782.                         as `id`,
  783.                     JSON_UNQUOTE(JSON_EXTRACT(s.name, '$.ru')) 
  784.                         as `name`, 
  785.                     s.group 
  786.                         as `group`, 
  787.                     s.uri_identity 
  788.                         as `uriIdentity`,
  789.                     pps.profile_id
  790.                         as `profile_id`,
  791.                     pps.service_condition
  792.                         as `condition`,
  793.                     pps.extra_charge
  794.                         as `extra_charge`,
  795.                     pps.comment
  796.                         as `comment`
  797.                 FROM profile_provided_services pps
  798.                 JOIN services s ON pps.service_id = s.id 
  799.                 WHERE pps.profile_id IN ($ids)
  800.                 ORDER BY s.group ASC, s.sort ASC, s.id ASC";
  801.         $result = $connection->executeQuery($sql);
  802.         $providedServices = $result->fetchAllAssociative();
  804.         $result = array_map(function ($profile) use ($stations, $districts, $providedServices): ProfileListingReadModel {
  805.             return $this->hydrateProfileRow2($profile, $stations, $districts, $providedServices);
  806.         }, $profiles);
  808.         return $result;
  809.     }
  811.     public function hydrateProfileRow2(array $row, array $stations, array $districts, array $services): ProfileListingReadModel
  812.     {
  813.         $profile = new ProfileListingReadModel();
  814.         $profile->id = $row['id'];
  816.         $profile->moderationStatus = $row['moderation_status'];
  818.         $profile->city = new CityReadModel();
  819.         $profile->city->id = $row['city_id'];
  820.         $profile->city->name = $row['city_name'];
  821.         $profile->city->uriIdentity = $row['city_uri_identity'];
  822.         $profile->city->countryCode = $row['city_country_code'];
  824.         $profile->uriIdentity = $row['uri_identity'];
  825.         $profile->name = $row['name'];
  826.         $profile->description = $row['description'];
  827.         $profile->phoneNumber = $row['phone_number'];
  829.         $profile->approved = (bool)$row['is_approved'];
  831.         $profile->isUltraVip = $row['adboard_placement_type'] == AdBoardPlacement::POSITION_GROUP_ULTRA_VIP;
  832.         $profile->isVip = $row['adboard_placement_type'] == AdBoardPlacement::POSITION_GROUP_VIP;
  833.         $profile->isStandard = false !== array_search(
  834.                 $row['adboard_placement_type'],
  835.                 [
  836.                     AdBoardPlacement::POSITION_GROUP_STANDARD_APPROVED, AdBoardPlacement::POSITION_GROUP_STANDARD,
  837.                     AdBoardPlacement::POSITION_GROUP_WITHOUT_OWNER_APPROVED, AdBoardPlacement::POSITION_GROUP_WITHOUT_OWNER
  838.                 ]
  839.             );
  840.         $profile->position = $row['adboard_placement_position'];
  842.         $profile->active = null !== $row['adboard_placement_type'] || $row['has_top_placement'];
  843.         $profile->hidden = $row['has_placement_hiding'] == true;
  845.         $profile->personParameters = new ProfilePersonParametersReadModel();
  846.         $profile->personParameters->age = $row['person_age'];
  847.         $profile->personParameters->height = $row['person_height'];
  848.         $profile->personParameters->weight = $row['person_weight'];
  849.         $profile->personParameters->breastSize = $row['person_breast_size'];
  850.         $profile->personParameters->bodyType = $row['person_body_type'];
  851.         $profile->personParameters->hairColor = $row['person_hair_color'];
  852.         $profile->personParameters->privateHaircut = $row['person_private_haircut'];
  853.         $profile->personParameters->nationality = $row['person_nationality'];
  854.         $profile->personParameters->hasTattoo = $row['person_has_tattoo'];
  855.         $profile->personParameters->hasPiercing = $row['person_has_piercing'];
  856.         $profile->stations = [];
  857.         $profile->districts = [];
  858.         $profile->counties = [];
  859.         foreach ($stations as $station) {
  860.             if ($profile->id !== $station['profile_id'])
  861.                 continue;
  862.             $profileStation = $profile->stations[$station['id']] ?? new StationReadModel($station['id'], $station['uriIdentity'], $station['name'], []);
  863.             if (null !== $station['line_name']) {
  864.                 $profileStation->lines[] = new StationLineReadModel($station['line_name'], $station['line_color']);
  865.             }
  866.             $profile->stations[$station['id']] = $profileStation;
  868.             if (array_key_exists($station['district_id'] ?? 0, $districts) && !array_key_exists($station['district_id'], $profile->districts)) {
  869.                 $profile->districts[$station['district_id']] = $districts[$station['district_id']];
  870.             }
  871.         }
  873.         $primaryId = (int)$row['primary_station_id'];
  875.         if (!empty($profile->stations)) {
  876.             uasort($profile->stations, function (StationReadModel $a, StationReadModel $b) use ($primaryId) {
  877.                 $aPrimary = $a->id === $primaryId;
  878.                 $bPrimary = $b->id === $primaryId;
  880.                 if ($aPrimary !== $bPrimary) {
  881.                     return $aPrimary ? -1 : 1;
  882.                 }
  884.                 return strnatcasecmp($a->name, $b->name);
  885.             });
  886.         }
  888.         $profile->providedServices = [];
  889.         foreach ($services as $service) {
  890.             if ($profile->id !== $service['profile_id'])
  891.                 continue;
  892.             $providedService = $profile->providedServices[$service['id']] ?? new ProvidedServiceReadModel(
  893.                 $service['id'], $service['name'], $service['group'], $service['uriIdentity'],
  894.                 $service['condition'], $service['extra_charge'], $service['comment']
  895.             );
  896.             $profile->providedServices[$service['id']] = $providedService;
  897.         }
  899.         $profile->selfies = $row['has_selfies'] ? [1] : [];
  900.         $profile->videos = $row['has_videos'] ? [1] : [];
  902.         $avatar = [
  903.             'path' => $row['avatar_path'] ?? '',
  904.             'type' => $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO
  905.         ];
  906.         if ($this->features->crop_avatar()) {
  907.             $profile->avatar = $avatar;
  908.         } else {
  909.             $profile->mainPhoto = $avatar;
  910.             $profile->photos = [];
  911.         }
  913.         $profile->comments = $row['has_comments'] ? [1] : [];
  915.         $profile->apartmentsPricing = new ProfileApartmentPricingReadModel();
  916.         $profile->apartmentsPricing->oneHourPrice = $row['apartments_one_hour_price'];
  917.         $profile->apartmentsPricing->twoHoursPrice = $row['apartments_two_hours_price'];
  918.         $profile->apartmentsPricing->nightPrice = $row['apartments_night_price'];
  920.         $profile->takeOutPricing = new ProfileTakeOutPricingReadModel();
  921.         $profile->takeOutPricing->oneHourPrice = $row['take_out_one_hour_price'];
  922.         $profile->takeOutPricing->twoHoursPrice = $row['take_out_two_hours_price'];
  923.         $profile->takeOutPricing->nightPrice = $row['take_out_night_price'];
  924.         $profile->takeOutPricing->locations = $row['take_out_locations'] ? array_map('intval', explode(',', $row['take_out_locations'])) : [];
  926.         $profile->seo = $row['seo'] ? json_decode($row['seo'], true) : null;
  928.         return $profile;
  929.     }
  931.     public function fetchMapProfilesByIds(ProfileIdINOrderedByINValues $specification): array
  932.     {
  933.         $ids = implode(',', $specification->getIds());
  935.         $mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
  936.         $mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
  938.         $sql = "
  939.             SELECT 
  940.                 p.id, p.uri_identity, p.map_latitude, p.map_longitude, p.phone_number, p.is_masseur, p.is_approved,
  941.                 p.person_age, p.person_breast_size, p.person_height, p.person_weight, pap.type as placement_type, p.primary_station_id,
  942.                 JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru')) 
  943.                     as `name`,
  944.                 (SELECT path FROM profile_media_files pmf_avatar WHERE p.id = pmf_avatar.profile_id AND pmf_avatar.type = '{$mediaType}' AND pmf_avatar.is_main = {$mediaIsMain} LIMIT 1) 
  945.                     as `avatar_path`,
  946.                 p.apartments_one_hour_price, p.apartments_two_hours_price, p.apartments_night_price, p.take_out_one_hour_price, p.take_out_two_hours_price, p.take_out_night_price,
  947.                 GROUP_CONCAT(ps.station_id) as `stations`,
  948.                 GROUP_CONCAT(pps.service_id) as `services`,
  949.                 EXISTS(SELECT * FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL) 
  950.                     as `has_comments`,
  951.                 EXISTS(SELECT * FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video') 
  952.                     as `has_videos`,
  953.                 EXISTS(SELECT * FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie') 
  954.                     as `has_selfies`,
  955.                 EXISTS(SELECT * FROM profile_top_placements ptp WHERE p.id = ptp.profile_id AND (NOW() BETWEEN ptp.placed_at AND ptp.expires_at))
  956.                     as `has_top_placement`
  957.             FROM profiles `p`
  958.             LEFT JOIN profile_stations ps ON ps.profile_id = p.id
  959.             LEFT JOIN profile_provided_services pps ON pps.profile_id = p.id
  960.             LEFT JOIN profile_adboard_placements pap ON pap.profile_id = p.id
  961.             WHERE p.id IN ($ids)
  962.             GROUP BY p.id
  963.             "; // AND p.map_latitude IS NOT NULL AND p.map_longitude IS NOT NULL; ORDER BY FIELD(p.id,$ids)
  965.         $result = $this->getEntityManager()->getConnection()->executeQuery($sql);
  966.         $profiles = $result->fetchAllAssociative();
  968.         $result = array_map(function ($profile): ProfileMapReadModel {
  969.             return $this->hydrateMapProfileRow($profile);
  970.         }, $profiles);
  972.         return $result;
  973.     }
  975.     public function hydrateMapProfileRow(array $row): ProfileMapReadModel
  976.     {
  977.         $profile = new ProfileMapReadModel();
  978.         $profile->id = $row['id'];
  980.         $profile->uriIdentity = $row['uri_identity'];
  981.         $profile->name = $row['name'];
  982.         $profile->phoneNumber = $row['phone_number'];
  984.         $profile->avatar = ['path' => $row['avatar_path'] ?? '', 'type' => $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO];
  986.         $profile->mapLatitude = $row['map_latitude'];
  987.         $profile->mapLongitude = $row['map_longitude'];
  989.         $profile->age = $row['person_age'];
  990.         $profile->breastSize = $row['person_breast_size'];
  991.         $profile->height = $row['person_height'];
  992.         $profile->weight = $row['person_weight'];
  994.         $profile->isMasseur = $row['is_masseur'];
  995.         $profile->isApproved = $row['is_approved'];
  996.         $profile->hasComments = $row['has_comments'];
  997.         $profile->hasSelfies = $row['has_selfies'];
  998.         $profile->hasVideos = $row['has_videos'];
  1000.         $profile->apartmentOneHourPrice = $row['apartments_one_hour_price'];
  1001.         $profile->apartmentTwoHoursPrice = $row['apartments_two_hours_price'];
  1002.         $profile->apartmentNightPrice = $row['apartments_night_price'];
  1004.         $profile->takeOutOneHourPrice = $row['take_out_one_hour_price'];
  1005.         $profile->takeOutTwoHoursPrice = $row['take_out_two_hours_price'];
  1006.         $profile->takeOutNightPrice = $row['take_out_night_price'];
  1008.         $profile->station = $row['primary_station_id'] ?? ($row['stations'] ? explode(',', $row['stations'])[0] : null);
  1009.         $profile->services = $row['services'] ? array_unique(explode(',', $row['services'])) : [];
  1011.         $profile->isPaid = $row['placement_type'] >= AdBoardPlacement::POSITION_GROUP_STANDARD || $row['has_top_placement'] !== null;
  1013. //        $prices = [ $row['apartments_one_hour_price'], $row['apartments_two_hours_price'], $row['apartments_night_price'],
  1014. //            $row['take_out_one_hour_price'], $row['take_out_two_hours_price'], $row['take_out_night_price'] ];
  1015. //        $prices = array_filter($prices, function($item) {
  1016. //            return $item != null;
  1017. //        });
  1018. //        $profile->price = count($prices) ? min($prices) : null;
  1020.         return $profile;
  1021.     }
  1023.     public function fetchAccountProfileListByIds(ProfileIdINOrderedByINValues $specification): array
  1024.     {
  1025.         $ids = implode(',', $specification->getIds());
  1027.         $mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
  1028.         $mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
  1030.         $sql = "
  1031.             SELECT 
  1032.                 p.*, JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru')) 
  1033.                     as `name`, 
  1034.                 JSON_UNQUOTE(JSON_EXTRACT(p.description, '$.ru')) 
  1035.                     as `description`,
  1036.                 (SELECT path FROM profile_media_files pmf_avatar WHERE p.id = pmf_avatar.profile_id AND pmf_avatar.type = '{$mediaType}' AND pmf_avatar.is_main = {$mediaIsMain} LIMIT 1) 
  1037.                     as `avatar_path`,
  1038.                 (SELECT type FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1) 
  1039.                     as `adboard_placement_type`,
  1040.                 c.id 
  1041.                     as `city_id`, 
  1042.                 JSON_UNQUOTE(JSON_EXTRACT(c.name, '$.ru')) 
  1043.                     as `city_name`, 
  1044.                 c.uri_identity 
  1045.                     as `city_uri_identity`,
  1046.                 c.country_code 
  1047.                     as `city_country_code`,
  1048.                 EXISTS(SELECT * FROM profile_top_placements ptp WHERE p.id = ptp.profile_id AND (NOW() BETWEEN ptp.placed_at AND ptp.expires_at))
  1049.                     as `has_top_placement`,
  1050.                 EXISTS(SELECT * FROM placement_hidings ph WHERE p.id = ph.profile_id AND ph.entity_type = 'profile') 
  1051.                     as `has_placement_hiding`,
  1052.                 EXISTS(SELECT * FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL) 
  1053.                     as `has_comments`,
  1054.                 EXISTS(SELECT * FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video') 
  1055.                     as `has_videos`,
  1056.                 EXISTS(SELECT * FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie') 
  1057.                     as `has_selfies`
  1058.             FROM profiles `p`
  1059.             JOIN cities `c` ON c.id = p.city_id 
  1060.             WHERE p.id IN ($ids)
  1061.             ORDER BY FIELD(p.id,$ids)";
  1063.         $connection = $this->getEntityManager()->getConnection();
  1064.         $result = $connection->executeQuery($sql);
  1065.         $profiles = $result->fetchAllAssociative();
  1067.         $sql = "SELECT 
  1068.                     JSON_UNQUOTE(JSON_EXTRACT(cs.name, '$.ru')) 
  1069.                         as `name`, 
  1070.                     cs.uri_identity 
  1071.                         as `uriIdentity`, 
  1072.                     ps.profile_id
  1073.                         as `profile_id`,
  1074.                     cs.district_id, cs.county_id
  1075.                 FROM profile_stations ps
  1076.                 JOIN city_stations cs ON ps.station_id = cs.id                 
  1077.                 WHERE ps.profile_id IN ($ids)";
  1078.         $result = $connection->executeQuery($sql);
  1079.         $stations = $result->fetchAllAssociative();
  1080.         $districtIds = array_unique(array_column($stations, 'district_id'));
  1081.         $districts = $this->districts->ofIds($districtIds);
  1083.         $sql = "SELECT 
  1084.                     s.id 
  1085.                         as `id`,
  1086.                     JSON_UNQUOTE(JSON_EXTRACT(s.name, '$.ru')) 
  1087.                         as `name`, 
  1088.                     s.group 
  1089.                         as `group`, 
  1090.                     s.uri_identity 
  1091.                         as `uriIdentity`,
  1092.                     pps.profile_id
  1093.                         as `profile_id`,
  1094.                     pps.service_condition
  1095.                         as `condition`,
  1096.                     pps.extra_charge
  1097.                         as `extra_charge`,
  1098.                     pps.comment
  1099.                         as `comment`
  1100.                 FROM profile_provided_services pps
  1101.                 JOIN services s ON pps.service_id = s.id 
  1102.                 WHERE pps.profile_id IN ($ids)
  1103.                 ORDER BY s.group ASC, s.sort ASC, s.id ASC";
  1104.         $result = $connection->executeQuery($sql);
  1105.         $providedServices = $result->fetchAllAssociative();
  1107.         $result = array_map(function ($profile) use ($stations, $districts, $providedServices): ProfileListingReadModel {
  1108.             return $this->hydrateProfileRow2($profile, $stations, $districts, $providedServices);
  1109.         }, $profiles);
  1111.         return $result;
  1112.     }
  1114.     public function getCommentedProfilesPaged(User $owner): ORMQueryResult
  1115.     {
  1116.         $qb = $this->createQueryBuilder('profile')
  1117.             ->join('profile.comments', 'comment')
  1118.             ->andWhere('profile.owner = :owner')
  1119.             ->setParameter('owner', $owner)
  1120.             ->orderBy('comment.createdAt', 'DESC');
  1122.         return new ORMQueryResult($qb);
  1123.     }
  1125.     /**
  1126.      * @return ProfilePlacementPriceDetailReadModel[]
  1127.      */
  1128.     public function fetchOfOwnerPlacedPriceDetails(User $owner): array
  1129.     {
  1130.         $sql = "
  1131.             SELECT 
  1132.                 p.id, p.is_approved, psp.price_amount
  1133.             FROM profiles `p`
  1134.             JOIN profile_adboard_placements pap ON pap.profile_id = p.id AND pap.placement_price_id IS NOT NULL
  1135.             JOIN paid_service_prices psp ON pap.placement_price_id = psp.id
  1136.             WHERE p.user_id = {$owner->getId()}
  1137.         ";
  1139.         $result = $this->getEntityManager()->getConnection()->executeQuery($sql);
  1140.         $profiles = $result->fetchAllAssociative();
  1142.         return array_map(function (array $row): ProfilePlacementPriceDetailReadModel {
  1143.             return new ProfilePlacementPriceDetailReadModel(
  1144.                 $row['id'], $row['is_approved'], $row['price_amount'] / 24
  1145.             );
  1146.         }, $profiles);
  1147.     }
  1149.     /**
  1150.      * @return ProfilePlacementHidingDetailReadModel[]
  1151.      */
  1152.     public function fetchOfOwnerHiddenDetails(User $owner): array
  1153.     {
  1154.         $sql = "
  1155.             SELECT 
  1156.                 p.id, p.is_approved
  1157.             FROM profiles `p`
  1158.             JOIN placement_hidings ph ON ph.profile_id = p.id
  1159.             WHERE p.user_id = {$owner->getId()}
  1160.         ";
  1162.         $result = $this->getEntityManager()->getConnection()->executeQuery($sql);
  1163.         $profiles = $result->fetchAllAssociative();
  1165.         return array_map(function (array $row): ProfilePlacementHidingDetailReadModel {
  1166.             return new ProfilePlacementHidingDetailReadModel(
  1167.                 $row['id'], $row['is_approved'], true
  1168.             );
  1169.         }, $profiles);
  1170.     }
  1172.     protected function modifyListingQueryBuilder(QueryBuilder $qb, string $alias): void
  1173.     {
  1174.         $qb
  1175.             ->addSelect('city')
  1176.             ->addSelect('station')
  1177.             ->addSelect('photo')
  1178.             ->addSelect('video')
  1179.             ->addSelect('comment')
  1180.             ->addSelect('avatar')
  1181.             ->join(sprintf('%s.city', $alias), 'city');
  1182.         if (!in_array('station', $qb->getAllAliases()))
  1183.             $qb->leftJoin(sprintf('%s.stations', $alias), 'station');
  1184.         if (!in_array('photo', $qb->getAllAliases()))
  1185.             $qb->leftJoin(sprintf('%s.photos', $alias), 'photo');
  1186.         if (!in_array('video', $qb->getAllAliases()))
  1187.             $qb->leftJoin(sprintf('%s.videos', $alias), 'video');
  1188.         if (!in_array('avatar', $qb->getAllAliases()))
  1189.             $qb->leftJoin(sprintf('%s.avatar', $alias), 'avatar');
  1190.         if (!in_array('comment', $qb->getAllAliases()))
  1191.             $qb->leftJoin(sprintf('%s.comments', $alias), 'comment');
  1193.         $this->addFemaleGenderFilterToQb($qb, $alias);
  1195.         //TODO убрать, если все ок
  1196.         //$this->excludeHavingPlacementHiding($qb, $alias);
  1198.         if (!in_array('profile_adboard_placement', $qb->getAllAliases())) {
  1199.             $qb
  1200.                 ->leftJoin(sprintf('%s.adBoardPlacement', $alias), 'profile_adboard_placement');
  1201.         }
  1202.         $qb->addSelect('profile_adboard_placement');
  1204.         if (!in_array('profile_top_placement', $qb->getAllAliases())) {
  1205.             $qb
  1206.                 ->leftJoin(sprintf('%s.topPlacements', $alias), 'profile_top_placement');
  1207.         }
  1208.         $qb->addSelect('profile_top_placement');
  1210.         //if($this->features->free_profiles()) {
  1211.         if (!in_array('placement_hiding', $qb->getAllAliases())) {
  1212.             $qb
  1213.                 ->leftJoin(sprintf('%s.placementHiding', $alias), 'placement_hiding');
  1214.         }
  1215.         $qb->addSelect('placement_hiding');
  1216.         //}
  1217.     }
  1219.     protected function addActiveFilterToQb(QueryBuilder $qb, string $dqlAlias)
  1220.     {
  1221.         if (!in_array('profile_adboard_placement', $qb->getAllAliases())) {
  1222.             $qb
  1223.                 ->join(sprintf('%s.adBoardPlacement', $dqlAlias), 'profile_adboard_placement');
  1224.         }
  1225.     }
  1227.     private function excludeHavingPlacementHiding(QueryBuilder $qb, $alias): void
  1228.     {
  1229.         if ($this->features->free_profiles()) {
  1230. //            if (!in_array('placement_hiding', $qb->getAllAliases())) {
  1231. //                $qb
  1232. //                    ->leftJoin(sprintf('%s.placementHiding', $alias), 'placement_hiding')
  1233. //                    ->andWhere(sprintf('placement_hiding IS NULL'))
  1234. //                ;
  1235. //        }
  1237.             $sub = new QueryBuilder($qb->getEntityManager());
  1238.             $sub->select("exclude_hidden_placement_hiding");
  1239.             $sub->from($qb->getEntityManager()->getClassMetadata(PlacementHiding::class)->name, "exclude_hidden_placement_hiding");
  1240.             $sub->andWhere(sprintf('exclude_hidden_placement_hiding.profile = %s', $alias));
  1242.             $qb->andWhere($qb->expr()->not($qb->expr()->exists($sub->getDQL())));
  1243.         }
  1244.     }
  1245. }