src/Repository/ProfileRepository.php line 98

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\Account\Advertiser;
  11. use App\Entity\Location\City;
  12. use App\Entity\Location\MapCoordinate;
  13. use App\Entity\Profile\Genders;
  14. use App\Entity\Profile\Photo;
  15. use App\Entity\Profile\Profile;
  16. use App\Entity\Sales\Profile\AdBoardPlacement;
  17. use App\Entity\Sales\Profile\AdBoardPlacementType;
  18. use App\Entity\Sales\Profile\PlacementHiding;
  19. use App\Entity\Saloon\Saloon;
  20. use App\Entity\User;
  21. use App\Repository\ReadModel\CityReadModel;
  22. use App\Repository\ReadModel\ProfileApartmentPricingReadModel;
  23. use App\Repository\ReadModel\ProfileListingReadModel;
  24. use App\Repository\ReadModel\ProfileMapReadModel;
  25. use App\Repository\ReadModel\ProfilePersonParametersReadModel;
  26. use App\Repository\ReadModel\ProfilePlacementHidingDetailReadModel;
  27. use App\Repository\ReadModel\ProfilePlacementPriceDetailReadModel;
  28. use App\Repository\ReadModel\ProfileTakeOutPricingReadModel;
  29. use App\Repository\ReadModel\ProvidedServiceReadModel;
  30. use App\Repository\ReadModel\StationLineReadModel;
  31. use App\Repository\ReadModel\StationReadModel;
  32. use App\Service\Features;
  33. use App\Specification\Profile\ProfileIdINOrderedByINValues;
  34. use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
  35. use Doctrine\ORM\AbstractQuery;
  36. use Doctrine\Persistence\ManagerRegistry;
  37. use Doctrine\DBAL\Statement;
  38. use Doctrine\ORM\QueryBuilder;
  39. use Happyr\DoctrineSpecification\Filter\Filter;
  40. use Happyr\DoctrineSpecification\Query\QueryModifier;
  41. use Porpaginas\Doctrine\ORM\ORMQueryResult;
  43. class ProfileRepository extends ServiceEntityRepository
  44. {
  45.     use SpecificationTrait;
  46.     use EntityIteratorTrait;
  48.     private Features $features;
  50.     public function __construct(ManagerRegistry $registry, Features $features)
  51.     {
  52.         parent::__construct($registry, Profile::class);
  53.         $this->features = $features;
  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 findPreviewByOwner(Advertiser $owner, int $limit): array
  458.     {
  459.         return $this->createQueryBuilder('profile')
  460.             ->addSelect('city')
  461.             ->join('profile.city', 'city')
  462.             ->andWhere('profile.owner = :owner')
  463.             ->andWhere('profile.deletedAt IS NULL')
  464.             ->setParameter('owner', $owner)
  465.             ->orderBy('profile.id', 'DESC')
  466.             ->setMaxResults($limit)
  467.             ->getQuery()
  468.             ->getResult();
  469.     }
  471.     public function getTimezonesListByUser(User $owner): array
  472.     {
  473.         $q = $this->_em->createQuery(sprintf("
  474.                 SELECT c
  475.                 FROM %s c
  476.                 WHERE c.id IN (
  477.                     SELECT DISTINCT(c2.id) 
  478.                     FROM %s p
  479.                     JOIN p.city c2
  480.                     WHERE p.owner = :user
  481.                 )
  482.             ", $this->_em->getClassMetadata(City::class)->name, $this->_em->getClassMetadata(Profile::class)->name))
  483.             ->setParameter('user', $owner);
  485.         return $q->getResult();
  486.     }
  488.     /**
  489.      * Список анкет, привязанных к аккаунту
  490.      *
  491.      * @return Profile[]
  492.      */
  493.     public function ofOwner(User $owner): array
  494.     {
  495.         $qb = $this->createQueryBuilder('profile')
  496.             ->andWhere('profile.owner = :owner')
  497.             ->setParameter('owner', $owner);
  499.         return $qb->getQuery()->getResult();
  500.     }
  502.     public function ofOwnerPaged(User $owner, array $genders = [Genders::FEMALE]): ORMQueryResult
  503.     {
  504.         $qb = $this->createQueryBuilder('profile')
  505.             ->andWhere('profile.owner = :owner')
  506.             ->setParameter('owner', $owner)
  507.             ->andWhere('profile.personParameters.gender IN (:genders)')
  508.             ->setParameter('genders', $genders);
  510.         return new ORMQueryResult($qb);
  511.     }
  513.     public function searchLinkableToSaloonByOwner(User $owner, ?string $query, int $limit = 20): array
  514.     {
  515.         $qb = $this->createQueryBuilder('profile')
  516.             ->andWhere('profile.owner = :owner')
  517.             ->setParameter('owner', $owner)
  518.             ->orderBy('profile.id', 'DESC')
  519.             ->setMaxResults($limit)
  520.         ;
  522.         if ($query) {
  523.             $qb
  524.                 ->andWhere('LOWER(JSON_UNQUOTE(JSON_EXTRACT(profile.name, :json_path))) LIKE :query')
  525.                 ->setParameter('json_path', '$.ru')
  526.                 ->setParameter('query', '%' . addcslashes(mb_strtolower(trim($query)), '%_') . '%')
  527.             ;
  528.         }
  530.         return $qb->getQuery()->getResult();
  531.     }
  533.     public function findLinkableToSaloonByOwnerAndIds(User $owner, array $ids): array
  534.     {
  535.         $ids = array_values(array_unique(array_filter(array_map('intval', $ids))));
  536.         if (empty($ids)) {
  537.             return [];
  538.         }
  540.         return $this->createQueryBuilder('profile')
  541.             ->andWhere('profile.owner = :owner')
  542.             ->andWhere('profile.id IN (:ids)')
  543.             ->setParameter('owner', $owner)
  544.             ->setParameter('ids', $ids)
  545.             ->getQuery()
  546.             ->getResult()
  547.         ;
  548.     }
  550.     public function findPublicProfilesBySaloon(Saloon $saloon, int $limit = 6, int $offset = 0): array
  551.     {
  552.         $profiles = $this->createPublicProfilesBySaloonQueryBuilder($saloon)
  553.             ->addSelect('placement')
  554.             ->orderBy('profile.id', 'DESC')
  555.             ->setMaxResults($limit)
  556.             ->setFirstResult($offset)
  557.             ->getQuery()
  558.             ->getResult()
  559.         ;
  561.         $this->loadPublicProfilePreviewRelations($profiles);
  563.         return $profiles;
  564.     }
  566.     public function countPublicProfilesBySaloon(Saloon $saloon): int
  567.     {
  568.         return (int)$this->createPublicProfilesBySaloonQueryBuilder($saloon)
  569.             ->select('COUNT(DISTINCT profile.id)')
  570.             ->getQuery()
  571.             ->getSingleScalarResult()
  572.         ;
  573.     }
  575.     public function findPublicProfilesBySaloonCircular(Saloon $saloon, int $limit, int $offset, int $total): array
  576.     {
  577.         if ($total <= 0 || $limit <= 0) {
  578.             return [];
  579.         }
  581.         $offset %= $total;
  582.         $firstChunkLimit = min($limit, $total - $offset);
  583.         $profiles = $this->findPublicProfilesBySaloon($saloon, $firstChunkLimit, $offset);
  585.         if (count($profiles) < $limit && $offset > 0) {
  586.             $profiles = array_merge(
  587.                 $profiles,
  588.                 $this->findPublicProfilesBySaloon($saloon, $limit - count($profiles), 0)
  589.             );
  590.         }
  592.         return $profiles;
  593.     }
  595.     public function findPublicProfilesBySaloonRotatedByPlacementStatus(Saloon $saloon, int $limit, int $offset, int $rotationSeed): array
  596.     {
  597.         if ($limit <= 0) {
  598.             return [];
  599.         }
  601.         $profiles = $this->createPublicProfilesBySaloonQueryBuilder($saloon)
  602.             ->addSelect('placement')
  603.             ->orderBy('placement.type', 'DESC')
  604.             ->addOrderBy('placement.placedAt', 'DESC')
  605.             ->addOrderBy('profile.id', 'DESC')
  606.             ->getQuery()
  607.             ->getResult()
  608.         ;
  610.         $profiles = array_slice($this->rotateProfilesWithinPlacementTypes($profiles, $rotationSeed), $offset, $limit);
  612.         $this->loadPublicProfilePreviewRelations($profiles);
  614.         return $profiles;
  615.     }
  617.     private function rotateProfilesWithinPlacementTypes(array $profiles, int $rotationSeed): array
  618.     {
  619.         $profilesByPlacementType = [];
  621.         foreach ($profiles as $profile) {
  622.             $profilesByPlacementType[$this->getProfilePlacementPriority($profile)][] = $profile;
  623.         }
  625.         krsort($profilesByPlacementType, SORT_NUMERIC);
  627.         $rotatedProfiles = [];
  628.         foreach ($profilesByPlacementType as $profilesGroup) {
  629.             $profilesGroupCount = count($profilesGroup);
  630.             $groupOffset = $profilesGroupCount > 0 ? $rotationSeed % $profilesGroupCount : 0;
  632.             if (0 === $groupOffset) {
  633.                 $rotatedProfiles = array_merge($rotatedProfiles, $profilesGroup);
  635.                 continue;
  636.             }
  638.             $rotatedProfiles = array_merge(
  639.                 $rotatedProfiles,
  640.                 array_slice($profilesGroup, $groupOffset),
  641.                 array_slice($profilesGroup, 0, $groupOffset)
  642.             );
  643.         }
  645.         return $rotatedProfiles;
  646.     }
  648.     private function getProfilePlacementPriority(Profile $profile): int
  649.     {
  650.         $placement = $profile->getAdBoardPlacement();
  652.         return $placement instanceof AdBoardPlacement ? $placement->getType()->getValue() : 0;
  653.     }
  655.     private function createPublicProfilesBySaloonQueryBuilder(Saloon $saloon): QueryBuilder
  656.     {
  657.         return $this->createQueryBuilder('profile')
  658.             ->leftJoin('profile.adBoardPlacement', 'placement')
  659.             ->leftJoin('profile.placementHiding', 'placement_hiding')
  660.             ->andWhere('profile.saloon = :saloon')
  661.             ->andWhere('profile.moderationStatus = :moderation_status')
  662.             ->andWhere('placement_hiding IS NULL')
  663.             ->setParameter('saloon', $saloon)
  664.             ->setParameter('moderation_status', Profile::MODERATION_STATUS_APPROVED)
  665.         ;
  666.     }
  668.     private function loadPublicProfilePreviewRelations(array $profiles): void
  669.     {
  670.         if (empty($profiles)) {
  671.             return;
  672.         }
  674.         $this->createQueryBuilder('profile')
  675.             ->leftJoin('profile.city', 'city')
  676.             ->leftJoin('profile.stations', 'station')
  677.             ->leftJoin('profile.avatar', 'avatar')
  678.             ->leftJoin('profile.photos', 'photo')
  679.             ->addSelect('city')
  680.             ->addSelect('station')
  681.             ->addSelect('avatar')
  682.             ->addSelect('photo')
  683.             ->andWhere('profile IN (:profiles)')
  684.             ->setParameter('profiles', $profiles)
  685.             ->getQuery()
  686.             ->getResult()
  687.         ;
  688.     }
  690.     public function ofOwnerAndMasseurTypeWithPlacementFilterAndNameFilterIterateAll(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): \Generator
  691.     {
  692.         $query = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur)->getQuery();
  694.         foreach ($query->iterate() as $row) {
  695.             yield $row[0];
  696.         }
  697.     }
  699.     private function queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): QueryBuilder
  700.     {
  701.         $qb = $this->createQueryBuilder('profile')
  702.             ->andWhere('profile.owner = :owner')
  703.             ->setParameter('owner', $owner);
  705.         switch ($placementTypeFilter) {
  706.             case 'paid':
  707.                 $qb->join('profile.adBoardPlacement', 'placement')
  708.                     ->andWhere('placement.type != :placement_type')
  709.                     ->setParameter('placement_type', AdBoardPlacementType::FREE);
  710.                 break;
  711.             case 'free':
  712.                 $qb->join('profile.adBoardPlacement', 'placement')
  713.                     ->andWhere('placement.type = :placement_type')
  714.                     ->setParameter('placement_type', AdBoardPlacementType::FREE);
  715.                 break;
  716.             case 'ultra-vip':
  717.                 $qb->join('profile.adBoardPlacement', 'placement')
  718.                     ->andWhere('placement.type = :placement_type')
  719.                     ->setParameter('placement_type', AdBoardPlacementType::ULTRA_VIP);
  720.                 break;
  721.             case 'vip':
  722.                 $qb->join('profile.adBoardPlacement', 'placement')
  723.                     ->andWhere('placement.type = :placement_type')
  724.                     ->setParameter('placement_type', AdBoardPlacementType::VIP);
  725.                 break;
  726.             case 'standard':
  727.                 $qb->join('profile.adBoardPlacement', 'placement')
  728.                     ->andWhere('placement.type = :placement_type')
  729.                     ->setParameter('placement_type', AdBoardPlacementType::STANDARD);
  730.                 break;
  731.             case 'hidden':
  732.                 $qb->join('profile.placementHiding', 'placement_hiding');
  733.                 break;
  734.             case 'all':
  735.             default:
  736.                 break;
  737.         }
  739.         if ($nameFilter) {
  740.             $nameExpr = $qb->expr()->orX(
  741.                 'LOWER(JSON_UNQUOTE(JSON_EXTRACT(profile.name, :jsonPath))) LIKE :name_filter',
  742.                 \sprintf("REGEXP_REPLACE(profile.phoneNumber, '-| ', '') LIKE :name_filter"),
  743.                 'LOWER(profile.phoneNumber) LIKE :name_filter',
  744.                 \sprintf("REGEXP_REPLACE(profile.phoneNumber, '\+7', '8') LIKE :name_filter"),
  745.             );
  746.             $qb->setParameter('jsonPath', '$.ru');
  747.             $qb->setParameter('name_filter', '%' . addcslashes(mb_strtolower(str_replace(['(', ')', ' ', '-'], '', $nameFilter)), '%_') . '%');
  749.             $qb->andWhere($nameExpr);
  750.         }
  752.         if (null !== $isMasseur) {
  753.             $qb->andWhere('profile.masseur = :is_masseur')
  754.                 ->setParameter('is_masseur', $isMasseur);
  755.         }
  757.         return $qb;
  758.     }
  760.     public function ofOwnerAndMasseurTypeWithPlacementFilterAndNameFilterPaged(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): ORMQueryResult
  761.     {
  762.         $qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
  764.         //сортируем анкеты по статусу UltraVip->Vip->Standard->Free->Hidden
  765.         $aliases = $qb->getAllAliases();
  766.         if (false == in_array('placement', $aliases))
  767.             $qb->leftJoin('profile.adBoardPlacement', 'placement');
  768.         if (false == in_array('placement_hiding', $aliases))
  769.             $qb->leftJoin('profile.placementHiding', 'placement_hiding');
  770.         $qb->addSelect('IF(placement_hiding.id IS NULL, 0, 1) as HIDDEN is_hidden');
  771.         $qb->addOrderBy('placement.type', 'DESC');
  772.         $qb->addOrderBy('placement.placedAt', 'DESC');
  773.         $qb->addOrderBy('is_hidden', 'ASC');
  775.         return new ORMQueryResult($qb);
  776.     }
  778.     public function idsOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): array
  779.     {
  780.         $qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
  781.         $qb->select('profile.id');
  783.         return $qb->getQuery()->getResult('column_hydrator');
  784.     }
  786.     public function countOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): int
  787.     {
  788.         $qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
  789.         $qb->select('count(profile.id)')
  790.             ->setMaxResults(1);
  792.         return (int)$qb->getQuery()->getSingleScalarResult();
  793.     }
  795.     /**
  796.      * @deprecated
  797.      */
  798.     public function hydrateProfileRow(array $row): ProfileListingReadModel
  799.     {
  800.         $profile = new ProfileListingReadModel();
  801.         $profile->id = $row['id'];
  802.         $profile->city = $row['city'];
  803.         $profile->uriIdentity = $row['uriIdentity'];
  804.         $profile->name = $row['name'];
  805.         $profile->description = $row['description'];
  806.         $profile->phoneNumber = $row['phoneNumber'];
  808.         $profile->approved = $row['approved'];
  810.         $now = new \DateTimeImmutable('now');
  811.         $hasRunningTopPlacement = false;
  812.         foreach ($row['topPlacements'] as $topPlacement) {
  813.             if ($topPlacement['placedAt'] <= $now && $now <= $topPlacement['expiresAt'])
  814.                 $hasRunningTopPlacement = true;
  815.         }
  816.         $profile->active = null !== $row['adBoardPlacement'] || $hasRunningTopPlacement;
  818.         $profile->hidden = null != $row['placementHiding'];
  820.         $profile->personParameters = new ProfilePersonParametersReadModel();
  821.         $profile->personParameters->age = $row['personParameters.age'];
  822.         $profile->personParameters->height = $row['personParameters.height'];
  823.         $profile->personParameters->weight = $row['personParameters.weight'];
  824.         $profile->personParameters->breastSize = $row['personParameters.breastSize'];
  825.         $profile->personParameters->bodyType = $row['personParameters.bodyType'];
  826.         $profile->personParameters->hairColor = $row['personParameters.hairColor'];
  827.         $profile->personParameters->privateHaircut = $row['personParameters.privateHaircut'];
  828.         $profile->personParameters->nationality = $row['personParameters.nationality'];
  829.         $profile->personParameters->hasTattoo = $row['personParameters.hasTattoo'];
  830.         $profile->personParameters->hasPiercing = $row['personParameters.hasPiercing'];
  832.         $profile->stations = $row['stations'];
  834.         $profile->avatar = $row['avatar'];
  836.         foreach ($row['photos'] as $photo)
  837.             if ($photo['main'])
  838.                 $profile->mainPhoto = $photo;
  841.         $profile->mainPhoto = null;
  842.         $profile->photos = [];
  843.         $profile->selfies = [];
  844.         foreach ($row['photos'] as $photo) {
  845.             if ($photo['main'])
  846.                 $profile->mainPhoto = $photo;
  848.             if ($photo['type'] == Photo::TYPE_PHOTO)
  849.                 $profile->photos[] = $photo;
  851.             if ($photo['type'] == Photo::TYPE_SELFIE)
  852.                 $profile->selfies[] = $photo;
  853.         }
  855.         $profile->videos = $row['videos'];
  856.         $profile->comments = $row['comments'];
  858.         $profile->apartmentsPricing = new ProfileApartmentPricingReadModel();
  859.         $profile->apartmentsPricing->oneHourPrice = $row['apartmentsPricing.oneHourPrice'];
  860.         $profile->apartmentsPricing->twoHoursPrice = $row['apartmentsPricing.twoHoursPrice'];
  861.         $profile->apartmentsPricing->nightPrice = $row['apartmentsPricing.nightPrice'];
  863.         $profile->takeOutPricing = new ProfileTakeOutPricingReadModel();
  864.         $profile->takeOutPricing->oneHourPrice = $row['takeOutPricing.oneHourPrice'];
  865.         $profile->takeOutPricing->twoHoursPrice = $row['takeOutPricing.twoHoursPrice'];
  866.         $profile->takeOutPricing->nightPrice = $row['takeOutPricing.nightPrice'];
  868.         return $profile;
  869.     }
  871.     public function deletedByPeriod(\DateTimeInterface $start, \DateTimeInterface $end): array
  872.     {
  873.         $qb = $this->createQueryBuilder('profile')
  874.             ->join('profile.city', 'city')
  875.             ->select('profile.uriIdentity _profile')
  876.             ->addSelect('city.uriIdentity _city')
  877.             ->andWhere('profile.deletedAt >= :start')
  878.             ->andWhere('profile.deletedAt <= :end')
  879.             ->setParameter('start', $start)
  880.             ->setParameter('end', $end);
  881.         return $qb->getQuery()->getResult();
  882.     }
  884.     public function listForMapMatchingSpec(Filter|QueryModifier $specification, int $coordinatesRoundPrecision = 3): array
  885.     {
  886.         $this->getEntityManager()->getConnection()->executeQuery("
  887.             SET SESSION group_concat_max_len = 100000;
  888.         ");
  890.         /** @var QueryBuilder $qb */
  891.         $qb = $this->createQueryBuilder($dqlAlias = 'p');
  892.         $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));
  893.         $qb->groupBy('coords');
  895.         $specification->modify($qb, $dqlAlias);
  896.         $qb->andWhere($specification->getFilter($qb, $dqlAlias));
  898.         return $qb->getQuery()->getResult();
  899.     }
  901.     public function fetchListingByIds(ProfileIdINOrderedByINValues $specification): array
  902.     {
  903.         $ids = implode(',', $specification->getIds());
  905.         $mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
  906.         $mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
  908.         $sql = "
  909.             SELECT 
  910.                 p.*, JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru')) 
  911.                     as `name`, 
  912.                 JSON_UNQUOTE(JSON_EXTRACT(p.description, '$.ru')) 
  913.                     as `description`,
  914.                 (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) 
  915.                     as `avatar_path`,
  916.                 (SELECT type FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1) 
  917.                     as `adboard_placement_type`,
  918.                 (SELECT position FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1) 
  919.                     as `adboard_placement_position`,
  920.                 c.id 
  921.                     as `city_id`, 
  922.                 JSON_UNQUOTE(JSON_EXTRACT(c.name, '$.ru')) 
  923.                     as `city_name`, 
  924.                 c.uri_identity 
  925.                     as `city_uri_identity`,
  926.                 c.country_code 
  927.                     as `city_country_code`,
  928.                 EXISTS(SELECT * FROM profile_top_placements ptp WHERE p.id = ptp.profile_id AND (NOW() BETWEEN ptp.placed_at AND ptp.expires_at))
  929.                     as `has_top_placement`,
  930.                 EXISTS(SELECT * FROM placement_hidings ph WHERE p.id = ph.profile_id AND ph.entity_type = 'profile') 
  931.                     as `has_placement_hiding`,
  932.                 (SELECT COUNT(*) FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL) 
  933.                     as `comments_count`,
  934.                 (SELECT COUNT(*) FROM profile_media_files pmf_photo WHERE p.id = pmf_photo.profile_id AND pmf_photo.type = 'photo') 
  935.                     as `photos_count`,
  936.                 (SELECT COUNT(*) FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video') 
  937.                     as `videos_count`,
  938.                 (SELECT COUNT(*) FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie') 
  939.                     as `selfies_count`,
  940.                 p.primary_station_id 
  941.             FROM profiles `p`
  942.             JOIN cities `c` ON c.id = p.city_id 
  943.             WHERE p.id IN ($ids)
  944.             ORDER BY FIELD(p.id,$ids)";
  946.         $connection = $this->getEntityManager()->getConnection();
  947.         $result = $connection->executeQuery($sql);
  948.         $profiles = $result->fetchAllAssociative();
  950.         $sql = "SELECT 
  951.                     cs.id 
  952.                         as `id`,
  953.                     JSON_UNQUOTE(JSON_EXTRACT(cs.name, '$.ru')) 
  954.                         as `name`, 
  955.                     cs.uri_identity 
  956.                         as `uriIdentity`, 
  957.                     ps.profile_id
  958.                         as `profile_id`,
  959.                     csl.name
  960.                         as `line_name`,
  961.                     csl.color
  962.                         as `line_color`
  963.                 FROM profile_stations ps
  964.                 JOIN city_stations cs ON ps.station_id = cs.id 
  965.                 LEFT JOIN city_subway_station_lines cssl ON cssl.station_id = cs.id
  966.                 LEFT JOIN city_subway_lines csl ON csl.id = cssl.line_id
  967.                 WHERE ps.profile_id IN ($ids)";
  968.         $result = $connection->executeQuery($sql);
  969.         $stations = $result->fetchAllAssociative();
  971.         $sql = "SELECT 
  972.                     s.id 
  973.                         as `id`,
  974.                     JSON_UNQUOTE(JSON_EXTRACT(s.name, '$.ru')) 
  975.                         as `name`, 
  976.                     s.group 
  977.                         as `group`, 
  978.                     s.uri_identity 
  979.                         as `uriIdentity`,
  980.                     pps.profile_id
  981.                         as `profile_id`,
  982.                     pps.service_condition
  983.                         as `condition`,
  984.                     pps.extra_charge
  985.                         as `extra_charge`,
  986.                     pps.comment
  987.                         as `comment`
  988.                 FROM profile_provided_services pps
  989.                 JOIN services s ON pps.service_id = s.id 
  990.                 WHERE pps.profile_id IN ($ids)
  991.                 ORDER BY s.group ASC, s.sort ASC, s.id ASC";
  992.         $result = $connection->executeQuery($sql);
  993.         $providedServices = $result->fetchAllAssociative();
  995.         $result = array_map(function ($profile) use ($stations, $providedServices): ProfileListingReadModel {
  996.             return $this->hydrateProfileRow2($profile, $stations, $providedServices);
  997.         }, $profiles);
  999.         return $result;
  1000.     }
  1002.     public function hydrateProfileRow2(array $row, array $stations, array $services): ProfileListingReadModel
  1003.     {
  1004.         $profile = new ProfileListingReadModel();
  1005.         $profile->id = $row['id'];
  1007.         $profile->moderationStatus = $row['moderation_status'];
  1009.         $profile->city = new CityReadModel();
  1010.         $profile->city->id = $row['city_id'];
  1011.         $profile->city->name = $row['city_name'];
  1012.         $profile->city->uriIdentity = $row['city_uri_identity'];
  1013.         $profile->city->countryCode = $row['city_country_code'];
  1015.         $profile->uriIdentity = $row['uri_identity'];
  1016.         $profile->name = $row['name'];
  1017.         $profile->description = $row['description'];
  1018.         $profile->phoneNumber = $row['phone_number'];
  1020.         $profile->approved = (bool)$row['is_approved'];
  1022.         $profile->isUltraVip = $row['adboard_placement_type'] == AdBoardPlacement::POSITION_GROUP_ULTRA_VIP;
  1023.         $profile->isVip = $row['adboard_placement_type'] == AdBoardPlacement::POSITION_GROUP_VIP;
  1024.         $profile->isStandard = false !== array_search(
  1025.                 $row['adboard_placement_type'],
  1026.                 [
  1027.                     AdBoardPlacement::POSITION_GROUP_STANDARD_APPROVED, AdBoardPlacement::POSITION_GROUP_STANDARD,
  1028.                     AdBoardPlacement::POSITION_GROUP_WITHOUT_OWNER_APPROVED, AdBoardPlacement::POSITION_GROUP_WITHOUT_OWNER
  1029.                 ]
  1030.             );
  1031.         $profile->position = $row['adboard_placement_position'];
  1033.         $profile->active = null !== $row['adboard_placement_type'] || $row['has_top_placement'];
  1034.         $profile->hidden = $row['has_placement_hiding'] == true;
  1036.         $profile->personParameters = new ProfilePersonParametersReadModel();
  1037.         $profile->personParameters->age = $row['person_age'];
  1038.         $profile->personParameters->height = $row['person_height'];
  1039.         $profile->personParameters->weight = $row['person_weight'];
  1040.         $profile->personParameters->breastSize = $row['person_breast_size'];
  1041.         $profile->personParameters->bodyType = $row['person_body_type'];
  1042.         $profile->personParameters->hairColor = $row['person_hair_color'];
  1043.         $profile->personParameters->privateHaircut = $row['person_private_haircut'];
  1044.         $profile->personParameters->nationality = $row['person_nationality'];
  1045.         $profile->personParameters->hasTattoo = $row['person_has_tattoo'];
  1046.         $profile->personParameters->hasPiercing = $row['person_has_piercing'];
  1047.         $profile->stations = [];
  1048.         foreach ($stations as $station) {
  1049.             if ($profile->id !== $station['profile_id'])
  1050.                 continue;
  1051.             $profileStation = $profile->stations[$station['id']] ?? new StationReadModel($station['id'], $station['uriIdentity'], $station['name'], []);
  1052.             if (null !== $station['line_name']) {
  1053.                 $profileStation->lines[] = new StationLineReadModel($station['line_name'], $station['line_color']);
  1054.             }
  1055.             $profile->stations[$station['id']] = $profileStation;
  1056.         }
  1058.         $primaryId = (int)$row['primary_station_id'];
  1060.         if (!empty($profile->stations)) {
  1061.             uasort($profile->stations, function (StationReadModel $a, StationReadModel $b) use ($primaryId) {
  1062.                 $aPrimary = $a->id === $primaryId;
  1063.                 $bPrimary = $b->id === $primaryId;
  1065.                 if ($aPrimary !== $bPrimary) {
  1066.                     return $aPrimary ? -1 : 1;
  1067.                 }
  1069.                 return strnatcasecmp($a->name, $b->name);
  1070.             });
  1071.         }
  1072.         if ($primaryId) {
  1073.             $profile->primaryStation = $profile->stations[$primaryId] ?? null;
  1074.         }
  1076.         $profile->providedServices = [];
  1077.         foreach ($services as $service) {
  1078.             if ($profile->id !== $service['profile_id'])
  1079.                 continue;
  1080.             $providedService = $profile->providedServices[$service['id']] ?? new ProvidedServiceReadModel(
  1081.                 $service['id'], $service['name'], $service['group'], $service['uriIdentity'],
  1082.                 $service['condition'], $service['extra_charge'], $service['comment']
  1083.             );
  1084.             $profile->providedServices[$service['id']] = $providedService;
  1085.         }
  1087.         $profile->selfies = $row['selfies_count'] ?? 0;
  1088.         $profile->videos = $row['videos_count'] ?? 0;
  1089.         $profile->photos = $row['photos_count'] ?? 0;
  1091.         $avatar = [
  1092.             'path' => $row['avatar_path'] ?? '',
  1093.             'type' => $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO
  1094.         ];
  1095.         if ($this->features->crop_avatar()) {
  1096.             $profile->avatar = $avatar;
  1097.         } else {
  1098.             $profile->mainPhoto = $avatar;
  1099.         }
  1101.         $profile->comments = $row['comments_count'] ?? 0;
  1103.         $profile->apartmentsPricing = new ProfileApartmentPricingReadModel();
  1104.         $profile->apartmentsPricing->oneHourPrice = $row['apartments_one_hour_price'];
  1105.         $profile->apartmentsPricing->twoHoursPrice = $row['apartments_two_hours_price'];
  1106.         $profile->apartmentsPricing->nightPrice = $row['apartments_night_price'];
  1108.         $profile->takeOutPricing = new ProfileTakeOutPricingReadModel();
  1109.         $profile->takeOutPricing->oneHourPrice = $row['take_out_one_hour_price'];
  1110.         $profile->takeOutPricing->twoHoursPrice = $row['take_out_two_hours_price'];
  1111.         $profile->takeOutPricing->nightPrice = $row['take_out_night_price'];
  1112.         $profile->takeOutPricing->locations = $row['take_out_locations'] ? array_map('intval', explode(',', $row['take_out_locations'])) : [];
  1114.         $profile->seo = $row['seo'] ? json_decode($row['seo'], true) : null;
  1116.         return $profile;
  1117.     }
  1119.     public function fetchMapProfilesByIds(ProfileIdINOrderedByINValues $specification): array
  1120.     {
  1121.         $ids = implode(',', $specification->getIds());
  1123.         $mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
  1124.         $mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
  1126.         $sql = "
  1127.             SELECT 
  1128.                 p.id, p.uri_identity, p.map_latitude, p.map_longitude, p.phone_number, p.is_masseur, p.is_approved,
  1129.                 p.person_age, p.person_breast_size, p.person_height, p.person_weight, pap.type as placement_type, p.primary_station_id,
  1130.                 JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru')) 
  1131.                     as `name`,
  1132.                 (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) 
  1133.                     as `avatar_path`,
  1134.                 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,
  1135.                 GROUP_CONCAT(ps.station_id) as `stations`,
  1136.                 GROUP_CONCAT(pps.service_id) as `services`,
  1137.                 EXISTS(SELECT * FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL) 
  1138.                     as `has_comments`,
  1139.                 EXISTS(SELECT * FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video') 
  1140.                     as `has_videos`,
  1141.                 EXISTS(SELECT * FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie') 
  1142.                     as `has_selfies`,
  1143.                 EXISTS(SELECT * FROM profile_top_placements ptp WHERE p.id = ptp.profile_id AND (NOW() BETWEEN ptp.placed_at AND ptp.expires_at))
  1144.                     as `has_top_placement`
  1145.             FROM profiles `p`
  1146.             LEFT JOIN profile_stations ps ON ps.profile_id = p.id
  1147.             LEFT JOIN profile_provided_services pps ON pps.profile_id = p.id
  1148.             LEFT JOIN profile_adboard_placements pap ON pap.profile_id = p.id
  1149.             WHERE p.id IN ($ids)
  1150.             GROUP BY p.id
  1151.             "; // AND p.map_latitude IS NOT NULL AND p.map_longitude IS NOT NULL; ORDER BY FIELD(p.id,$ids)
  1153.         $result = $this->getEntityManager()->getConnection()->executeQuery($sql);
  1154.         $profiles = $result->fetchAllAssociative();
  1156.         $result = array_map(function ($profile): ProfileMapReadModel {
  1157.             return $this->hydrateMapProfileRow($profile);
  1158.         }, $profiles);
  1160.         return $result;
  1161.     }
  1163.     public function hydrateMapProfileRow(array $row): ProfileMapReadModel
  1164.     {
  1165.         $profile = new ProfileMapReadModel();
  1166.         $profile->id = $row['id'];
  1168.         $profile->uriIdentity = $row['uri_identity'];
  1169.         $profile->name = $row['name'];
  1170.         $profile->phoneNumber = $row['phone_number'];
  1172.         $profile->avatar = ['path' => $row['avatar_path'] ?? '', 'type' => $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO];
  1174.         $profile->mapLatitude = $row['map_latitude'];
  1175.         $profile->mapLongitude = $row['map_longitude'];
  1177.         $profile->age = $row['person_age'];
  1178.         $profile->breastSize = $row['person_breast_size'];
  1179.         $profile->height = $row['person_height'];
  1180.         $profile->weight = $row['person_weight'];
  1182.         $profile->isMasseur = $row['is_masseur'];
  1183.         $profile->isApproved = $row['is_approved'];
  1184.         $profile->hasComments = $row['has_comments'];
  1185.         $profile->hasSelfies = $row['has_selfies'];
  1186.         $profile->hasVideos = $row['has_videos'];
  1188.         $profile->apartmentOneHourPrice = $row['apartments_one_hour_price'];
  1189.         $profile->apartmentTwoHoursPrice = $row['apartments_two_hours_price'];
  1190.         $profile->apartmentNightPrice = $row['apartments_night_price'];
  1192.         $profile->takeOutOneHourPrice = $row['take_out_one_hour_price'];
  1193.         $profile->takeOutTwoHoursPrice = $row['take_out_two_hours_price'];
  1194.         $profile->takeOutNightPrice = $row['take_out_night_price'];
  1196.         $profile->station = $row['primary_station_id'] ?? ($row['stations'] ? explode(',', $row['stations'])[0] : null);
  1197.         $profile->services = $row['services'] ? array_unique(explode(',', $row['services'])) : [];
  1199.         $profile->isPaid = $row['placement_type'] >= AdBoardPlacement::POSITION_GROUP_STANDARD || $row['has_top_placement'] !== null;
  1201. //        $prices = [ $row['apartments_one_hour_price'], $row['apartments_two_hours_price'], $row['apartments_night_price'],
  1202. //            $row['take_out_one_hour_price'], $row['take_out_two_hours_price'], $row['take_out_night_price'] ];
  1203. //        $prices = array_filter($prices, function($item) {
  1204. //            return $item != null;
  1205. //        });
  1206. //        $profile->price = count($prices) ? min($prices) : null;
  1208.         return $profile;
  1209.     }
  1211.     public function fetchAccountProfileListByIds(ProfileIdINOrderedByINValues $specification): array
  1212.     {
  1213.         $ids = implode(',', $specification->getIds());
  1215.         $mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
  1216.         $mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
  1218.         $sql = "
  1219.             SELECT 
  1220.                 p.*, JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru')) 
  1221.                     as `name`, 
  1222.                 JSON_UNQUOTE(JSON_EXTRACT(p.description, '$.ru')) 
  1223.                     as `description`,
  1224.                 (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) 
  1225.                     as `avatar_path`,
  1226.                 (SELECT type FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1) 
  1227.                     as `adboard_placement_type`,
  1228.                 c.id 
  1229.                     as `city_id`, 
  1230.                 JSON_UNQUOTE(JSON_EXTRACT(c.name, '$.ru')) 
  1231.                     as `city_name`, 
  1232.                 c.uri_identity 
  1233.                     as `city_uri_identity`,
  1234.                 c.country_code 
  1235.                     as `city_country_code`,
  1236.                 EXISTS(SELECT * FROM profile_top_placements ptp WHERE p.id = ptp.profile_id AND (NOW() BETWEEN ptp.placed_at AND ptp.expires_at))
  1237.                     as `has_top_placement`,
  1238.                 EXISTS(SELECT * FROM placement_hidings ph WHERE p.id = ph.profile_id AND ph.entity_type = 'profile') 
  1239.                     as `has_placement_hiding`,
  1240.                 (SELECT COUNT(*) FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL) 
  1241.                     as `comments_count`,
  1242.                 (SELECT COUNT(*) FROM profile_media_files pmf_photo WHERE p.id = pmf_photo.profile_id AND pmf_photo.type = 'photo') 
  1243.                     as `photos_count`,
  1244.                 (SELECT COUNT(*) FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video') 
  1245.                     as `videos_count`,
  1246.                 (SELECT COUNT(*) FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie') 
  1247.                     as `selfies_count`,
  1248.                 p.primary_station_id 
  1249.             FROM profiles `p`
  1250.             JOIN cities `c` ON c.id = p.city_id 
  1251.             WHERE p.id IN ($ids)
  1252.             ORDER BY FIELD(p.id,$ids)";
  1254.         $connection = $this->getEntityManager()->getConnection();
  1255.         $result = $connection->executeQuery($sql);
  1256.         $profiles = $result->fetchAllAssociative();
  1258.         $sql = "SELECT 
  1259.                     JSON_UNQUOTE(JSON_EXTRACT(cs.name, '$.ru')) 
  1260.                         as `name`, 
  1261.                     cs.uri_identity 
  1262.                         as `uriIdentity`, 
  1263.                     ps.profile_id
  1264.                         as `profile_id` 
  1265.                 FROM profile_stations ps
  1266.                 JOIN city_stations cs ON ps.station_id = cs.id                 
  1267.                 WHERE ps.profile_id IN ($ids)";
  1268.         $result = $connection->executeQuery($sql);
  1269.         $stations = $result->fetchAllAssociative();
  1271.         $sql = "SELECT 
  1272.                     s.id 
  1273.                         as `id`,
  1274.                     JSON_UNQUOTE(JSON_EXTRACT(s.name, '$.ru')) 
  1275.                         as `name`, 
  1276.                     s.group 
  1277.                         as `group`, 
  1278.                     s.uri_identity 
  1279.                         as `uriIdentity`,
  1280.                     pps.profile_id
  1281.                         as `profile_id`,
  1282.                     pps.service_condition
  1283.                         as `condition`,
  1284.                     pps.extra_charge
  1285.                         as `extra_charge`,
  1286.                     pps.comment
  1287.                         as `comment`
  1288.                 FROM profile_provided_services pps
  1289.                 JOIN services s ON pps.service_id = s.id 
  1290.                 WHERE pps.profile_id IN ($ids)
  1291.                 ORDER BY s.group ASC, s.sort ASC, s.id ASC";
  1292.         $result = $connection->executeQuery($sql);
  1293.         $providedServices = $result->fetchAllAssociative();
  1295.         $result = array_map(function ($profile) use ($stations, $providedServices): ProfileListingReadModel {
  1296.             return $this->hydrateProfileRow2($profile, $stations, $providedServices);
  1297.         }, $profiles);
  1299.         return $result;
  1300.     }
  1302.     public function getCommentedProfilesPaged(User $owner): ORMQueryResult
  1303.     {
  1304.         $qb = $this->createQueryBuilder('profile')
  1305.             ->join('profile.comments', 'comment')
  1306.             ->andWhere('profile.owner = :owner')
  1307.             ->setParameter('owner', $owner)
  1308.             ->orderBy('comment.createdAt', 'DESC');
  1310.         return new ORMQueryResult($qb);
  1311.     }
  1313.     /**
  1314.      * @return ProfilePlacementPriceDetailReadModel[]
  1315.      */
  1316.     public function fetchOfOwnerPlacedPriceDetails(User $owner): array
  1317.     {
  1318.         $sql = "
  1319.             SELECT 
  1320.                 p.id, p.is_approved, psp.price_amount
  1321.             FROM profiles `p`
  1322.             JOIN profile_adboard_placements pap ON pap.profile_id = p.id AND pap.placement_price_id IS NOT NULL
  1323.             JOIN paid_service_prices psp ON pap.placement_price_id = psp.id
  1324.             WHERE p.user_id = {$owner->getId()}
  1325.         ";
  1327.         $result = $this->getEntityManager()->getConnection()->executeQuery($sql);
  1328.         $profiles = $result->fetchAllAssociative();
  1330.         return array_map(function (array $row): ProfilePlacementPriceDetailReadModel {
  1331.             return new ProfilePlacementPriceDetailReadModel(
  1332.                 $row['id'], $row['is_approved'], $row['price_amount'] / 24
  1333.             );
  1334.         }, $profiles);
  1335.     }
  1337.     /**
  1338.      * @return ProfilePlacementHidingDetailReadModel[]
  1339.      */
  1340.     public function fetchOfOwnerHiddenDetails(User $owner): array
  1341.     {
  1342.         $sql = "
  1343.             SELECT 
  1344.                 p.id, p.is_approved
  1345.             FROM profiles `p`
  1346.             JOIN placement_hidings ph ON ph.profile_id = p.id
  1347.             WHERE p.user_id = {$owner->getId()}
  1348.         ";
  1350.         $result = $this->getEntityManager()->getConnection()->executeQuery($sql);
  1351.         $profiles = $result->fetchAllAssociative();
  1353.         return array_map(function (array $row): ProfilePlacementHidingDetailReadModel {
  1354.             return new ProfilePlacementHidingDetailReadModel(
  1355.                 $row['id'], $row['is_approved'], true
  1356.             );
  1357.         }, $profiles);
  1358.     }
  1360.     protected function modifyListingQueryBuilder(QueryBuilder $qb, string $alias): void
  1361.     {
  1362.         $qb
  1363.             ->addSelect('city')
  1364.             ->addSelect('station')
  1365.             ->addSelect('photo')
  1366.             ->addSelect('video')
  1367.             ->addSelect('comment')
  1368.             ->addSelect('avatar')
  1369.             ->join(sprintf('%s.city', $alias), 'city');
  1370.         if (!in_array('station', $qb->getAllAliases()))
  1371.             $qb->leftJoin(sprintf('%s.stations', $alias), 'station');
  1372.         if (!in_array('photo', $qb->getAllAliases()))
  1373.             $qb->leftJoin(sprintf('%s.photos', $alias), 'photo');
  1374.         if (!in_array('video', $qb->getAllAliases()))
  1375.             $qb->leftJoin(sprintf('%s.videos', $alias), 'video');
  1376.         if (!in_array('avatar', $qb->getAllAliases()))
  1377.             $qb->leftJoin(sprintf('%s.avatar', $alias), 'avatar');
  1378.         if (!in_array('comment', $qb->getAllAliases()))
  1379.             $qb->leftJoin(sprintf('%s.comments', $alias), 'comment');
  1381.         $this->addFemaleGenderFilterToQb($qb, $alias);
  1383.         //TODO убрать, если все ок
  1384.         //$this->excludeHavingPlacementHiding($qb, $alias);
  1386.         if (!in_array('profile_adboard_placement', $qb->getAllAliases())) {
  1387.             $qb
  1388.                 ->leftJoin(sprintf('%s.adBoardPlacement', $alias), 'profile_adboard_placement');
  1389.         }
  1390.         $qb->addSelect('profile_adboard_placement');
  1392.         if (!in_array('profile_top_placement', $qb->getAllAliases())) {
  1393.             $qb
  1394.                 ->leftJoin(sprintf('%s.topPlacements', $alias), 'profile_top_placement');
  1395.         }
  1396.         $qb->addSelect('profile_top_placement');
  1398.         //if($this->features->free_profiles()) {
  1399.         if (!in_array('placement_hiding', $qb->getAllAliases())) {
  1400.             $qb
  1401.                 ->leftJoin(sprintf('%s.placementHiding', $alias), 'placement_hiding');
  1402.         }
  1403.         $qb->addSelect('placement_hiding');
  1404.         //}
  1405.     }
  1407.     protected function addActiveFilterToQb(QueryBuilder $qb, string $dqlAlias)
  1408.     {
  1409.         if (!in_array('profile_adboard_placement', $qb->getAllAliases())) {
  1410.             $qb
  1411.                 ->join(sprintf('%s.adBoardPlacement', $dqlAlias), 'profile_adboard_placement');
  1412.         }
  1413.     }
  1415.     private function excludeHavingPlacementHiding(QueryBuilder $qb, $alias): void
  1416.     {
  1417.         if ($this->features->free_profiles()) {
  1418. //            if (!in_array('placement_hiding', $qb->getAllAliases())) {
  1419. //                $qb
  1420. //                    ->leftJoin(sprintf('%s.placementHiding', $alias), 'placement_hiding')
  1421. //                    ->andWhere(sprintf('placement_hiding IS NULL'))
  1422. //                ;
  1423. //        }
  1425.             $sub = new QueryBuilder($qb->getEntityManager());
  1426.             $sub->select("exclude_hidden_placement_hiding");
  1427.             $sub->from($qb->getEntityManager()->getClassMetadata(PlacementHiding::class)->name, "exclude_hidden_placement_hiding");
  1428.             $sub->andWhere(sprintf('exclude_hidden_placement_hiding.profile = %s', $alias));
  1430.             $qb->andWhere($qb->expr()->not($qb->expr()->exists($sub->getDQL())));
  1431.         }
  1432.     }
  1433. }