src/Repository/ProfileRepository.php line 100

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;
  49.     private DistrictRepository $districts;
  51.     public function __construct(ManagerRegistry $registry, Features $features, DistrictRepository $districts)
  52.     {
  53.         parent::__construct($registry, Profile::class);
  54.         $this->features = $features;
  55.         $this->districts = $districts;
  56.     }
  58.     /**
  59.      * Возвращает итератор по данным, необходимым для генерации файлов sitemap, в виде массивов с
  60.      * следующими ключами:
  61.      *  - id
  62.      *  - uri
  63.      *  - updatedAt
  64.      *  - city_uri
  65.      *
  66.      * @return iterable<array{id: int, uri: string, updatedAt: \DateTimeImmutable, city_uri: string}>
  67.      */
  68.     public function sitemapItemsIterator(): iterable
  69.     {
  70.         $qb = $this->createQueryBuilder('profile')
  71.             ->select('profile.id, profile.uriIdentity AS uri, profile.updatedAt, city.uriIdentity AS city_uri')
  72.             ->join('profile.city', 'city')
  73.             ->andWhere('profile.deletedAt IS NULL');
  74.         $this->addModerationFilterToQb($qb, 'profile');
  76.         return $qb->getQuery()->toIterable([], AbstractQuery::HYDRATE_ARRAY);
  77.     }
  79.     protected function addModerationFilterToQb(QueryBuilder $qb, string $dqlAlias): void
  80.     {
  81.         if ($this->features->hard_moderation()) {
  82.             $qb->leftJoin(sprintf('%s.owner', $dqlAlias), 'owner');
  83.             $qb->andWhere(
  84.                 $qb->expr()->orX(
  85.                     sprintf('%s.moderationStatus = :status_passed', $dqlAlias),
  86.                     $qb->expr()->andX(
  87.                         sprintf('%s.moderationStatus = :status_waiting', $dqlAlias),
  88.                         'owner.trusted = true'
  89.                     )
  90.                 )
  91.             );
  92.             $qb->setParameter('status_passed', Profile::MODERATION_STATUS_APPROVED);
  93.             $qb->setParameter('status_waiting', Profile::MODERATION_STATUS_WAITING);
  94.         } else {
  95.             $qb->andWhere(sprintf('%s.moderationStatus IN (:statuses)', $dqlAlias));
  96.             $qb->setParameter('statuses', [Profile::MODERATION_STATUS_NOT_PASSED, Profile::MODERATION_STATUS_WAITING, Profile::MODERATION_STATUS_APPROVED]);
  97.         }
  98.     }
  100.     public function ofUriIdentityWithinCity(string $uriIdentity, City $city): ?Profile
  101.     {
  102.         return $this->findOneBy([
  103.             'uriIdentity' => $uriIdentity,
  104.             'city' => $city,
  105.         ]);
  106.     }
  108.     /**
  109.      * Метод проверки уникальности анкет по URI не должен использовать никаких фильтров, кроме URI и города,
  110.      * поэтому QueryBuilder не используется
  111.      * @see https://redminez.net/issues/27310
  112.      */
  113.     public function isUniqueUriIdentityExistWithinCity(string $uriIdentity, City $city): bool
  114.     {
  115.         $connection = $this->_em->getConnection();
  116.         $stmt = $connection->executeQuery('SELECT COUNT(id) FROM profiles WHERE uri_identity = ? AND city_id = ?', [$uriIdentity, $city->getId()]);
  117.         $count = $stmt->fetchOne();
  119.         return $count > 0;
  120.     }
  122.     public function countByCity(): array
  123.     {
  124.         $qb = $this->createQueryBuilder('profile')
  125.             ->select('IDENTITY(profile.city), COUNT(profile.id)')
  126.             ->groupBy('profile.city');
  128.         $this->addFemaleGenderFilterToQb($qb, 'profile');
  129.         $this->addModerationFilterToQb($qb, 'profile');
  130.         //$this->excludeHavingPlacementHiding($qb, 'profile');
  131.         $this->havingAdBoardPlacement($qb, 'profile');
  133.         $query = $qb->getQuery()
  134.             ->useResultCache(true)
  135.             ->setResultCacheLifetime(120);
  137.         $rawResult = $query->getScalarResult();
  138.         $indexedResult = [];
  139.         foreach ($rawResult as $row) {
  140.             $indexedResult[$row[1]] = $row[2];
  141.         }
  143.         return $indexedResult;
  144.     }
  146.     protected function addFemaleGenderFilterToQb(QueryBuilder $qb, string $alias): void
  147.     {
  148.         $this->addGenderFilterToQb($qb, $alias, [Genders::FEMALE]);
  149.     }
  151.     protected function addGenderFilterToQb(QueryBuilder $qb, string $alias, array $genders = [Genders::FEMALE]): void
  152.     {
  153.         $qb->andWhere(sprintf('%s.personParameters.gender IN (:genders)', $alias));
  154.         $qb->setParameter('genders', $genders);
  155.     }
  157.     private function havingAdBoardPlacement(QueryBuilder $qb, string $alias): void
  158.     {
  159.         $qb->join(sprintf('%s.adBoardPlacement', $alias), 'adboard_placement');
  160.     }
  162.     public function countByStations(): array
  163.     {
  164.         $qb = $this->createQueryBuilder('profiles')
  165.             ->select('stations.id, COUNT(profiles.id) as cnt')
  166.             ->join('profiles.stations', 'stations')
  167.             //это условие сильно затормжаживает запрос, но оно и не нужно при условии, что чужих(от других городов) станций у анкеты нет
  168.             //->where('profiles.city = stations.city')
  169.             ->groupBy('stations.id');
  171.         $this->addFemaleGenderFilterToQb($qb, 'profiles');
  172.         $this->addModerationFilterToQb($qb, 'profiles');
  173.         //$this->excludeHavingPlacementHiding($qb, 'profiles');
  174.         $this->havingAdBoardPlacement($qb, 'profiles');
  176.         $query = $qb->getQuery()
  177.             ->useResultCache(true)
  178.             ->setResultCacheLifetime(120);
  180.         $rawResult = $query->getScalarResult();
  181.         $indexedResult = [];
  182.         foreach ($rawResult as $row) {
  183.             $indexedResult[$row['id']] = $row['cnt'];
  184.         }
  186.         return $indexedResult;
  187.     }
  189.     public function countByDistricts(): array
  190.     {
  191.         $qb = $this->createQueryBuilder('profiles')
  192.             ->select('districts.id, COUNT(profiles.id) as cnt')
  193.             ->join('profiles.stations', 'stations')
  194.             ->join('stations.district', 'districts')
  195.             ->groupBy('districts.id');
  197.         $this->addFemaleGenderFilterToQb($qb, 'profiles');
  198.         $this->addModerationFilterToQb($qb, 'profiles');
  199.         //$this->excludeHavingPlacementHiding($qb, 'profiles');
  200.         $this->havingAdBoardPlacement($qb, 'profiles');
  202.         $query = $qb->getQuery()
  203.             ->useResultCache(true)
  204.             ->setResultCacheLifetime(120);
  206.         $rawResult = $query->getScalarResult();
  207.         $indexedResult = [];
  208.         foreach ($rawResult as $row) {
  209.             $indexedResult[$row['id']] = $row['cnt'];
  210.         }
  212.         return $indexedResult;
  213.     }
  215.     public function countByCounties(): array
  216.     {
  217.         $qb = $this->createQueryBuilder('profiles')
  218.             ->select('counties.id, COUNT(profiles.id) as cnt')
  219.             ->join('profiles.stations', 'stations')
  220.             ->join('stations.district', 'districts')
  221.             ->join('districts.county', 'counties')
  222.             ->groupBy('counties.id');
  224.         $this->addFemaleGenderFilterToQb($qb, 'profiles');
  225.         $this->addModerationFilterToQb($qb, 'profiles');
  226.         //$this->excludeHavingPlacementHiding($qb, 'profiles');
  227.         $this->havingAdBoardPlacement($qb, 'profiles');
  229.         $query = $qb->getQuery()
  230.             ->useResultCache(true)
  231.             ->setResultCacheLifetime(120);
  233.         $rawResult = $query->getScalarResult();
  234.         $indexedResult = [];
  235.         foreach ($rawResult as $row) {
  236.             $indexedResult[$row['id']] = $row['cnt'];
  237.         }
  239.         return $indexedResult;
  240.     }
  242.     /**
  243.      * @param array|int[] $ids
  244.      * @return Profile[]
  245.      */
  246.     public function findByIds(array $ids): array
  247.     {
  248.         return $this->createQueryBuilder('profile')
  249.             ->andWhere('profile.id IN (:ids)')
  250.             ->setParameter('ids', $ids)
  251.             ->orderBy('FIELD(profile.id,:ids2)')
  252.             ->setParameter('ids2', $ids)
  253.             ->getQuery()
  254.             ->getResult();
  255.     }
  257.     public function findByIdsIterate(array $ids): iterable
  258.     {
  259.         $qb = $this->createQueryBuilder('profile')
  260.             ->andWhere('profile.id IN (:ids)')
  261.             ->setParameter('ids', $ids)
  262.             ->orderBy('FIELD(profile.id,:ids2)')
  263.             ->setParameter('ids2', $ids);
  265.         return $this->iterateQueryBuilder($qb);
  266.     }
  268.     /**
  269.      * Список анкет указанного типа (массажистки или нет), привязанных к аккаунту
  270.      */
  271.     public function ofOwnerAndTypePaged(User $owner, bool $masseurs): ORMQueryResult
  272.     {
  273.         $qb = $this->createQueryBuilder('profile')
  274.             ->andWhere('profile.owner = :owner')
  275.             ->setParameter('owner', $owner)
  276.             ->andWhere('profile.masseur = :is_masseur')
  277.             ->setParameter('is_masseur', $masseurs);
  279.         return new ORMQueryResult($qb);
  280.     }
  282.     /**
  283.      * Список активных анкет, привязанных к аккаунту
  284.      */
  285.     public function activeAndOwnedBy(User $owner): ORMQueryResult
  286.     {
  287.         $qb = $this->createQueryBuilder('profile')
  288.             ->join('profile.adBoardPlacement', 'profile_adboard_placement')
  289.             ->andWhere('profile.owner = :owner')
  290.             ->setParameter('owner', $owner);
  292.         return new ORMQueryResult($qb);
  293.     }
  295.     /**
  296.      * Список активных или скрытых анкет, привязанных к аккаунту
  297.      *
  298.      * @return Profile[]|ORMQueryResult
  299.      */
  300.     public function activeOrHiddenAndOwnedBy(User $owner): ORMQueryResult
  301.     {
  302.         $qb = $this->createQueryBuilder('profile')
  303.             ->leftJoin('profile.adBoardPlacement', 'profile_adboard_placement')
  304.             ->leftJoin('profile.placementHiding', 'placement_hiding')
  305.             ->andWhere('profile_adboard_placement IS NOT NULL OR placement_hiding IS NOT NULL')
  306.             ->andWhere('profile.owner = :owner')
  307.             ->setParameter('owner', $owner);
  309.         return new ORMQueryResult($qb);
  310.     }
  312.     public function activePaidAdBoardPlacementAndOwnedBy(User $owner): ORMQueryResult
  313.     {
  314.         $qb = $this->createQueryBuilder('profile')
  315.             ->addSelect('profile_adboard_placement', 'placement_price', 'city', 'owner')
  316.             ->join('profile.adBoardPlacement', 'profile_adboard_placement')
  317.             ->leftJoin('profile_adboard_placement.placementPrice', 'placement_price')
  318.             ->join('profile.city', 'city')
  319.             ->join('profile.owner', 'owner')
  320.             ->andWhere('profile_adboard_placement.type <> :free_placement_type')
  321.             ->andWhere('profile.owner = :owner')
  322.             ->setParameter('free_placement_type', AdBoardPlacementType::FREE)
  323.             ->setParameter('owner', $owner);
  325.         return new ORMQueryResult($qb);
  326.     }
  328.     public function paidAdBoardPlacementChargeRowsOfOwner(User $owner): array
  329.     {
  330.         $qb = $this->createQueryBuilder('profile')
  331.             ->select([
  332.                 'profile.id AS profile_id',
  333.                 'profile.approved AS approved',
  334.                 'profile.masseur AS is_masseur',
  335.                 'profile.personParameters.gender AS gender',
  336.                 'profile_adboard_placement.type AS placement_type',
  337.                 'profile_adboard_placement.planManaged AS plan_managed',
  338.                 'placement_price.id AS placement_price_id',
  339.                 'placement_price.priceAmount AS price_amount',
  340.                 'placement_price.duration AS duration',
  341.                 'placement_price.currency AS currency',
  342.                 'placement_price.dynamicPriceMatrix AS dynamic_price_matrix',
  343.                 'city.id AS city_id',
  344.                 'city.cityPriceCategory AS city_price_category',
  345.                 'city.timezone AS timezone',
  346.                 'owner.currencyCode AS owner_currency',
  347.             ])
  348.             ->join('profile.adBoardPlacement', 'profile_adboard_placement')
  349.             ->leftJoin('profile_adboard_placement.placementPrice', 'placement_price')
  350.             ->join('profile.city', 'city')
  351.             ->join('profile.owner', 'owner')
  352.             ->andWhere('profile_adboard_placement.type <> :free_placement_type')
  353.             ->andWhere('profile.owner = :owner')
  354.             ->setParameter('free_placement_type', AdBoardPlacementType::FREE)
  355.             ->setParameter('owner', $owner);
  357.         return $qb->getQuery()->getArrayResult();
  358.     }
  360.     public function currentChargeableAndOwnedBy(User $owner): ORMQueryResult
  361.     {
  362.         $qb = $this->createQueryBuilder('profile')
  363.             ->addSelect('profile_adboard_placement', 'placement_price', 'placement_hiding', 'city', 'owner')
  364.             ->leftJoin('profile.adBoardPlacement', 'profile_adboard_placement')
  365.             ->leftJoin('profile_adboard_placement.placementPrice', 'placement_price')
  366.             ->leftJoin('profile.placementHiding', 'placement_hiding')
  367.             ->join('profile.city', 'city')
  368.             ->join('profile.owner', 'owner')
  369.             ->andWhere('(profile_adboard_placement IS NOT NULL AND profile_adboard_placement.type <> :free_placement_type) OR placement_hiding IS NOT NULL')
  370.             ->andWhere('profile.owner = :owner')
  371.             ->setParameter('free_placement_type', AdBoardPlacementType::FREE)
  372.             ->setParameter('owner', $owner);
  374.         return new ORMQueryResult($qb);
  375.     }
  377.     public function countFreeUnapprovedLimited(): int
  378.     {
  379.         $qb = $this->createQueryBuilder('profile')
  380.             ->select('count(profile)')
  381.             ->join('profile.adBoardPlacement', 'placement')
  382.             ->andWhere('placement.type = :placement_type')
  383.             ->setParameter('placement_type', AdBoardPlacementType::FREE)
  384.             ->leftJoin('profile.placementHiding', 'hiding')
  385.             ->andWhere('hiding IS NULL')
  386.             ->andWhere('profile.approved = false');
  388.         return (int)$qb->getQuery()->getSingleScalarResult();
  389.     }
  391.     public function iterateFreeUnapprovedLimited(int $limit): iterable
  392.     {
  393.         $qb = $this->createQueryBuilder('profile')
  394.             ->join('profile.adBoardPlacement', 'placement')
  395.             ->andWhere('placement.type = :placement_type')
  396.             ->setParameter('placement_type', AdBoardPlacementType::FREE)
  397.             ->leftJoin('profile.placementHiding', 'hiding')
  398.             ->andWhere('hiding IS NULL')
  399.             ->andWhere('profile.approved = false')
  400.             ->setMaxResults($limit);
  402.         return $this->iterateQueryBuilder($qb);
  403.     }
  405.     /**
  406.      * Число активных анкет, привязанных к аккаунту
  407.      */
  408.     public function countActiveOfOwner(User $owner, ?bool $isMasseur = false): int
  409.     {
  410.         $qb = $this->createQueryBuilder('profile')
  411.             ->select('COUNT(profile.id)')
  412.             ->join('profile.adBoardPlacement', 'profile_adboard_placement')
  413.             ->andWhere('profile.owner = :owner')
  414.             ->setParameter('owner', $owner);
  415.         if ($this->features->hard_moderation()) {
  416.             $qb->leftJoin('profile.owner', 'owner');
  417.             $qb->andWhere(
  418.                 $qb->expr()->orX(
  419.                     'profile.moderationStatus = :status_passed',
  420.                     $qb->expr()->andX(
  421.                         'profile.moderationStatus = :status_waiting',
  422.                         'owner.trusted = true'
  423.                     )
  424.                 )
  425.             );
  426.             $qb->setParameter('status_passed', Profile::MODERATION_STATUS_APPROVED);
  427.             $qb->setParameter('status_waiting', Profile::MODERATION_STATUS_WAITING);
  428.         } else {
  429.             $qb->andWhere('profile.moderationStatus IN (:statuses)')
  430.                 ->setParameter('statuses', [Profile::MODERATION_STATUS_NOT_PASSED, Profile::MODERATION_STATUS_WAITING, Profile::MODERATION_STATUS_APPROVED]);
  431.         }
  432.         if (null !== $isMasseur) {
  433.             $qb->andWhere('profile.masseur = :is_masseur')
  434.                 ->setParameter('is_masseur', $isMasseur);
  435.         }
  437.         return (int)$qb->getQuery()->getSingleScalarResult();
  438.     }
  440.     /**
  441.      * Число всех анкет, привязанных к аккаунту
  442.      */
  443.     public function countAllOfOwnerNotDeleted(User $owner, ?bool $isMasseur = false): int
  444.     {
  445.         $qb = $this->createQueryBuilder('profile')
  446.             ->select('COUNT(profile.id)')
  447.             ->andWhere('profile.owner = :owner')
  448.             ->setParameter('owner', $owner)
  449.             //потому что используется в т.ч. на тех страницах, где отключен фильтр вывода "только неудаленных"
  450.             ->andWhere('profile.deletedAt IS NULL');
  451.         if (null !== $isMasseur) {
  452.             $qb->andWhere('profile.masseur = :is_masseur')
  453.                 ->setParameter('is_masseur', $isMasseur);
  454.         }
  456.         return (int)$qb->getQuery()->getSingleScalarResult();
  457.     }
  459.     public function findPreviewByOwner(Advertiser $owner, int $limit): array
  460.     {
  461.         return $this->createQueryBuilder('profile')
  462.             ->addSelect('city')
  463.             ->join('profile.city', 'city')
  464.             ->andWhere('profile.owner = :owner')
  465.             ->andWhere('profile.deletedAt IS NULL')
  466.             ->setParameter('owner', $owner)
  467.             ->orderBy('profile.id', 'DESC')
  468.             ->setMaxResults($limit)
  469.             ->getQuery()
  470.             ->getResult();
  471.     }
  473.     public function getTimezonesListByUser(User $owner): array
  474.     {
  475.         $q = $this->_em->createQuery(sprintf("
  476.                 SELECT c
  477.                 FROM %s c
  478.                 WHERE c.id IN (
  479.                     SELECT DISTINCT(c2.id) 
  480.                     FROM %s p
  481.                     JOIN p.city c2
  482.                     WHERE p.owner = :user
  483.                 )
  484.             ", $this->_em->getClassMetadata(City::class)->name, $this->_em->getClassMetadata(Profile::class)->name))
  485.             ->setParameter('user', $owner);
  487.         return $q->getResult();
  488.     }
  490.     /**
  491.      * Список анкет, привязанных к аккаунту
  492.      *
  493.      * @return Profile[]
  494.      */
  495.     public function ofOwner(User $owner): array
  496.     {
  497.         $qb = $this->createQueryBuilder('profile')
  498.             ->andWhere('profile.owner = :owner')
  499.             ->setParameter('owner', $owner);
  501.         return $qb->getQuery()->getResult();
  502.     }
  504.     public function ofOwnerPaged(User $owner, array $genders = [Genders::FEMALE]): ORMQueryResult
  505.     {
  506.         $qb = $this->createQueryBuilder('profile')
  507.             ->andWhere('profile.owner = :owner')
  508.             ->setParameter('owner', $owner)
  509.             ->andWhere('profile.personParameters.gender IN (:genders)')
  510.             ->setParameter('genders', $genders);
  512.         return new ORMQueryResult($qb);
  513.     }
  515.     public function searchLinkableToSaloonByOwner(User $owner, ?string $query, int $limit = 20): array
  516.     {
  517.         $qb = $this->createQueryBuilder('profile')
  518.             ->andWhere('profile.owner = :owner')
  519.             ->setParameter('owner', $owner)
  520.             ->orderBy('profile.id', 'DESC')
  521.             ->setMaxResults($limit)
  522.         ;
  524.         if ($query) {
  525.             $qb
  526.                 ->andWhere('LOWER(JSON_UNQUOTE(JSON_EXTRACT(profile.name, :json_path))) LIKE :query')
  527.                 ->setParameter('json_path', '$.ru')
  528.                 ->setParameter('query', '%' . addcslashes(mb_strtolower(trim($query)), '%_') . '%')
  529.             ;
  530.         }
  532.         return $qb->getQuery()->getResult();
  533.     }
  535.     public function findLinkableToSaloonByOwnerAndIds(User $owner, array $ids): array
  536.     {
  537.         $ids = array_values(array_unique(array_filter(array_map('intval', $ids))));
  538.         if (empty($ids)) {
  539.             return [];
  540.         }
  542.         return $this->createQueryBuilder('profile')
  543.             ->andWhere('profile.owner = :owner')
  544.             ->andWhere('profile.id IN (:ids)')
  545.             ->setParameter('owner', $owner)
  546.             ->setParameter('ids', $ids)
  547.             ->getQuery()
  548.             ->getResult()
  549.         ;
  550.     }
  552.     public function findPublicProfilesBySaloon(Saloon $saloon, int $limit = 6, int $offset = 0): array
  553.     {
  554.         $profiles = $this->createPublicProfilesBySaloonQueryBuilder($saloon)
  555.             ->addSelect('placement')
  556.             ->orderBy('profile.id', 'DESC')
  557.             ->setMaxResults($limit)
  558.             ->setFirstResult($offset)
  559.             ->getQuery()
  560.             ->getResult()
  561.         ;
  563.         $this->loadPublicProfilePreviewRelations($profiles);
  565.         return $profiles;
  566.     }
  568.     public function countPublicProfilesBySaloon(Saloon $saloon): int
  569.     {
  570.         return (int)$this->createPublicProfilesBySaloonQueryBuilder($saloon)
  571.             ->select('COUNT(DISTINCT profile.id)')
  572.             ->getQuery()
  573.             ->getSingleScalarResult()
  574.         ;
  575.     }
  577.     public function findPublicProfilesBySaloonCircular(Saloon $saloon, int $limit, int $offset, int $total): array
  578.     {
  579.         if ($total <= 0 || $limit <= 0) {
  580.             return [];
  581.         }
  583.         $offset %= $total;
  584.         $firstChunkLimit = min($limit, $total - $offset);
  585.         $profiles = $this->findPublicProfilesBySaloon($saloon, $firstChunkLimit, $offset);
  587.         if (count($profiles) < $limit && $offset > 0) {
  588.             $profiles = array_merge(
  589.                 $profiles,
  590.                 $this->findPublicProfilesBySaloon($saloon, $limit - count($profiles), 0)
  591.             );
  592.         }
  594.         return $profiles;
  595.     }
  597.     public function findPublicProfilesBySaloonRotatedByPlacementStatus(Saloon $saloon, int $limit, int $offset, int $rotationSeed): array
  598.     {
  599.         if ($limit <= 0) {
  600.             return [];
  601.         }
  603.         $profiles = $this->createPublicProfilesBySaloonQueryBuilder($saloon)
  604.             ->addSelect('placement')
  605.             ->orderBy('placement.type', 'DESC')
  606.             ->addOrderBy('placement.placedAt', 'DESC')
  607.             ->addOrderBy('profile.id', 'DESC')
  608.             ->getQuery()
  609.             ->getResult()
  610.         ;
  612.         $profiles = array_slice($this->rotateProfilesWithinPlacementTypes($profiles, $rotationSeed), $offset, $limit);
  614.         $this->loadPublicProfilePreviewRelations($profiles);
  616.         return $profiles;
  617.     }
  619.     private function rotateProfilesWithinPlacementTypes(array $profiles, int $rotationSeed): array
  620.     {
  621.         $profilesByPlacementType = [];
  623.         foreach ($profiles as $profile) {
  624.             $profilesByPlacementType[$this->getProfilePlacementPriority($profile)][] = $profile;
  625.         }
  627.         krsort($profilesByPlacementType, SORT_NUMERIC);
  629.         $rotatedProfiles = [];
  630.         foreach ($profilesByPlacementType as $profilesGroup) {
  631.             $profilesGroupCount = count($profilesGroup);
  632.             $groupOffset = $profilesGroupCount > 0 ? $rotationSeed % $profilesGroupCount : 0;
  634.             if (0 === $groupOffset) {
  635.                 $rotatedProfiles = array_merge($rotatedProfiles, $profilesGroup);
  637.                 continue;
  638.             }
  640.             $rotatedProfiles = array_merge(
  641.                 $rotatedProfiles,
  642.                 array_slice($profilesGroup, $groupOffset),
  643.                 array_slice($profilesGroup, 0, $groupOffset)
  644.             );
  645.         }
  647.         return $rotatedProfiles;
  648.     }
  650.     private function getProfilePlacementPriority(Profile $profile): int
  651.     {
  652.         $placement = $profile->getAdBoardPlacement();
  654.         return $placement instanceof AdBoardPlacement ? $placement->getType()->getValue() : 0;
  655.     }
  657.     private function createPublicProfilesBySaloonQueryBuilder(Saloon $saloon): QueryBuilder
  658.     {
  659.         return $this->createQueryBuilder('profile')
  660.             ->leftJoin('profile.adBoardPlacement', 'placement')
  661.             ->leftJoin('profile.placementHiding', 'placement_hiding')
  662.             ->andWhere('profile.saloon = :saloon')
  663.             ->andWhere('profile.moderationStatus = :moderation_status')
  664.             ->andWhere('placement_hiding IS NULL')
  665.             ->setParameter('saloon', $saloon)
  666.             ->setParameter('moderation_status', Profile::MODERATION_STATUS_APPROVED)
  667.         ;
  668.     }
  670.     private function loadPublicProfilePreviewRelations(array $profiles): void
  671.     {
  672.         if (empty($profiles)) {
  673.             return;
  674.         }
  676.         $this->createQueryBuilder('profile')
  677.             ->leftJoin('profile.city', 'city')
  678.             ->leftJoin('profile.stations', 'station')
  679.             ->leftJoin('profile.avatar', 'avatar')
  680.             ->leftJoin('profile.photos', 'photo')
  681.             ->addSelect('city')
  682.             ->addSelect('station')
  683.             ->addSelect('avatar')
  684.             ->addSelect('photo')
  685.             ->andWhere('profile IN (:profiles)')
  686.             ->setParameter('profiles', $profiles)
  687.             ->getQuery()
  688.             ->getResult()
  689.         ;
  690.     }
  692.     public function ofOwnerAndMasseurTypeWithPlacementFilterAndNameFilterIterateAll(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): \Generator
  693.     {
  694.         $query = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur)->getQuery();
  696.         foreach ($query->iterate() as $row) {
  697.             yield $row[0];
  698.         }
  699.     }
  701.     private function queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): QueryBuilder
  702.     {
  703.         $qb = $this->createQueryBuilder('profile')
  704.             ->andWhere('profile.owner = :owner')
  705.             ->setParameter('owner', $owner);
  707.         switch ($placementTypeFilter) {
  708.             case 'paid':
  709.                 $qb->join('profile.adBoardPlacement', 'placement')
  710.                     ->andWhere('placement.type != :placement_type')
  711.                     ->setParameter('placement_type', AdBoardPlacementType::FREE);
  712.                 break;
  713.             case 'free':
  714.                 $qb->join('profile.adBoardPlacement', 'placement')
  715.                     ->andWhere('placement.type = :placement_type')
  716.                     ->setParameter('placement_type', AdBoardPlacementType::FREE);
  717.                 break;
  718.             case 'ultra-vip':
  719.                 $qb->join('profile.adBoardPlacement', 'placement')
  720.                     ->andWhere('placement.type = :placement_type')
  721.                     ->setParameter('placement_type', AdBoardPlacementType::ULTRA_VIP);
  722.                 break;
  723.             case 'vip':
  724.                 $qb->join('profile.adBoardPlacement', 'placement')
  725.                     ->andWhere('placement.type = :placement_type')
  726.                     ->setParameter('placement_type', AdBoardPlacementType::VIP);
  727.                 break;
  728.             case 'standard':
  729.                 $qb->join('profile.adBoardPlacement', 'placement')
  730.                     ->andWhere('placement.type = :placement_type')
  731.                     ->setParameter('placement_type', AdBoardPlacementType::STANDARD);
  732.                 break;
  733.             case 'hidden':
  734.                 $qb->join('profile.placementHiding', 'placement_hiding');
  735.                 break;
  736.             case 'all':
  737.             default:
  738.                 break;
  739.         }
  741.         if ($nameFilter) {
  742.             $nameExpr = $qb->expr()->orX(
  743.                 'LOWER(JSON_UNQUOTE(JSON_EXTRACT(profile.name, :jsonPath))) LIKE :name_filter',
  744.                 \sprintf("REGEXP_REPLACE(profile.phoneNumber, '-| ', '') LIKE :name_filter"),
  745.                 'LOWER(profile.phoneNumber) LIKE :name_filter',
  746.                 \sprintf("REGEXP_REPLACE(profile.phoneNumber, '\+7', '8') LIKE :name_filter"),
  747.             );
  748.             $qb->setParameter('jsonPath', '$.ru');
  749.             $qb->setParameter('name_filter', '%' . addcslashes(mb_strtolower(str_replace(['(', ')', ' ', '-'], '', $nameFilter)), '%_') . '%');
  751.             $qb->andWhere($nameExpr);
  752.         }
  754.         if (null !== $isMasseur) {
  755.             $qb->andWhere('profile.masseur = :is_masseur')
  756.                 ->setParameter('is_masseur', $isMasseur);
  757.         }
  759.         return $qb;
  760.     }
  762.     public function ofOwnerAndMasseurTypeWithPlacementFilterAndNameFilterPaged(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): ORMQueryResult
  763.     {
  764.         $qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
  766.         //сортируем анкеты по статусу UltraVip->Vip->Standard->Free->Hidden
  767.         $aliases = $qb->getAllAliases();
  768.         if (false == in_array('placement', $aliases))
  769.             $qb->leftJoin('profile.adBoardPlacement', 'placement');
  770.         if (false == in_array('placement_hiding', $aliases))
  771.             $qb->leftJoin('profile.placementHiding', 'placement_hiding');
  772.         $qb->addSelect('IF(placement_hiding.id IS NULL, 0, 1) as HIDDEN is_hidden');
  773.         $qb->addOrderBy('placement.type', 'DESC');
  774.         $qb->addOrderBy('placement.placedAt', 'DESC');
  775.         $qb->addOrderBy('is_hidden', 'ASC');
  777.         return new ORMQueryResult($qb);
  778.     }
  780.     public function idsOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): array
  781.     {
  782.         $qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
  783.         $qb->select('profile.id');
  785.         return $qb->getQuery()->getResult('column_hydrator');
  786.     }
  788.     public function countOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter(User $owner, string $placementTypeFilter, ?string $nameFilter, ?bool $isMasseur = null): int
  789.     {
  790.         $qb = $this->queryBuilderOfOwnerAndMasseurTypeWithPlacementFilterAndNameFilter($owner, $placementTypeFilter, $nameFilter, $isMasseur);
  791.         $qb->select('count(profile.id)')
  792.             ->setMaxResults(1);
  794.         return (int)$qb->getQuery()->getSingleScalarResult();
  795.     }
  797.     /**
  798.      * @deprecated
  799.      */
  800.     public function hydrateProfileRow(array $row): ProfileListingReadModel
  801.     {
  802.         $profile = new ProfileListingReadModel();
  803.         $profile->id = $row['id'];
  804.         $profile->city = $row['city'];
  805.         $profile->uriIdentity = $row['uriIdentity'];
  806.         $profile->name = $row['name'];
  807.         $profile->description = $row['description'];
  808.         $profile->phoneNumber = $row['phoneNumber'];
  810.         $profile->isMasseur = $row['masseur'];
  811.         $profile->approved = $row['approved'];
  813.         $now = new \DateTimeImmutable('now');
  814.         $hasRunningTopPlacement = false;
  815.         foreach ($row['topPlacements'] as $topPlacement) {
  816.             if ($topPlacement['placedAt'] <= $now && $now <= $topPlacement['expiresAt'])
  817.                 $hasRunningTopPlacement = true;
  818.         }
  819.         $profile->active = null !== $row['adBoardPlacement'] || $hasRunningTopPlacement;
  821.         $profile->hidden = null != $row['placementHiding'];
  823.         $profile->personParameters = new ProfilePersonParametersReadModel();
  824.         $profile->personParameters->age = $row['personParameters.age'];
  825.         $profile->personParameters->height = $row['personParameters.height'];
  826.         $profile->personParameters->weight = $row['personParameters.weight'];
  827.         $profile->personParameters->breastSize = $row['personParameters.breastSize'];
  828.         $profile->personParameters->bodyType = $row['personParameters.bodyType'];
  829.         $profile->personParameters->hairColor = $row['personParameters.hairColor'];
  830.         $profile->personParameters->privateHaircut = $row['personParameters.privateHaircut'];
  831.         $profile->personParameters->nationality = $row['personParameters.nationality'];
  832.         $profile->personParameters->hasTattoo = $row['personParameters.hasTattoo'];
  833.         $profile->personParameters->hasPiercing = $row['personParameters.hasPiercing'];
  835.         $profile->stations = $row['stations'];
  837.         $profile->avatar = $row['avatar'];
  839.         foreach ($row['photos'] as $photo)
  840.             if ($photo['main'])
  841.                 $profile->mainPhoto = $photo;
  844.         $profile->mainPhoto = null;
  845.         $profile->photos = [];
  846.         $profile->selfies = [];
  847.         foreach ($row['photos'] as $photo) {
  848.             if ($photo['main'])
  849.                 $profile->mainPhoto = $photo;
  851.             if ($photo['type'] == Photo::TYPE_PHOTO)
  852.                 $profile->photos[] = $photo;
  854.             if ($photo['type'] == Photo::TYPE_SELFIE)
  855.                 $profile->selfies[] = $photo;
  856.         }
  858.         $profile->videos = $row['videos'];
  859.         $profile->comments = $row['comments'];
  861.         $profile->apartmentsPricing = new ProfileApartmentPricingReadModel();
  862.         $profile->apartmentsPricing->oneHourPrice = $row['apartmentsPricing.oneHourPrice'];
  863.         $profile->apartmentsPricing->twoHoursPrice = $row['apartmentsPricing.twoHoursPrice'];
  864.         $profile->apartmentsPricing->nightPrice = $row['apartmentsPricing.nightPrice'];
  866.         $profile->takeOutPricing = new ProfileTakeOutPricingReadModel();
  867.         $profile->takeOutPricing->oneHourPrice = $row['takeOutPricing.oneHourPrice'];
  868.         $profile->takeOutPricing->twoHoursPrice = $row['takeOutPricing.twoHoursPrice'];
  869.         $profile->takeOutPricing->nightPrice = $row['takeOutPricing.nightPrice'];
  871.         return $profile;
  872.     }
  874.     public function deletedByPeriod(\DateTimeInterface $start, \DateTimeInterface $end): array
  875.     {
  876.         $qb = $this->createQueryBuilder('profile')
  877.             ->join('profile.city', 'city')
  878.             ->select('profile.uriIdentity _profile')
  879.             ->addSelect('city.uriIdentity _city')
  880.             ->andWhere('profile.deletedAt >= :start')
  881.             ->andWhere('profile.deletedAt <= :end')
  882.             ->setParameter('start', $start)
  883.             ->setParameter('end', $end);
  884.         return $qb->getQuery()->getResult();
  885.     }
  887.     public function listForMapMatchingSpec(Filter|QueryModifier $specification, int $coordinatesRoundPrecision = 3): array
  888.     {
  889.         $this->getEntityManager()->getConnection()->executeQuery("
  890.             SET SESSION group_concat_max_len = 100000;
  891.         ");
  893.         /** @var QueryBuilder $qb */
  894.         $qb = $this->createQueryBuilder($dqlAlias = 'p');
  895.         $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));
  896.         $qb->groupBy('coords');
  898.         $specification->modify($qb, $dqlAlias);
  899.         $qb->andWhere($specification->getFilter($qb, $dqlAlias));
  901.         return $qb->getQuery()->getResult();
  902.     }
  904.     public function fetchListingByIds(ProfileIdINOrderedByINValues $specification): array
  905.     {
  906.         $ids = implode(',', $specification->getIds());
  908.         $mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
  909.         $mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
  911.         $sql = "
  912.             SELECT 
  913.                 p.*, JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru')) 
  914.                     as `name`, 
  915.                 JSON_UNQUOTE(JSON_EXTRACT(p.description, '$.ru')) 
  916.                     as `description`,
  917.                 (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) 
  918.                     as `avatar_path`,
  919.                 (SELECT type FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1) 
  920.                     as `adboard_placement_type`,
  921.                 (SELECT position FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1) 
  922.                     as `adboard_placement_position`,
  923.                 c.id 
  924.                     as `city_id`, 
  925.                 JSON_UNQUOTE(JSON_EXTRACT(c.name, '$.ru')) 
  926.                     as `city_name`, 
  927.                 c.uri_identity 
  928.                     as `city_uri_identity`,
  929.                 c.country_code 
  930.                     as `city_country_code`,
  931.                 EXISTS(SELECT * FROM profile_top_placements ptp WHERE p.id = ptp.profile_id AND (NOW() BETWEEN ptp.placed_at AND ptp.expires_at))
  932.                     as `has_top_placement`,
  933.                 EXISTS(SELECT * FROM placement_hidings ph WHERE p.id = ph.profile_id AND ph.entity_type = 'profile') 
  934.                     as `has_placement_hiding`,
  935.                 (SELECT COUNT(*) FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL) 
  936.                     as `comments_count`,
  937.                 (SELECT COUNT(*) FROM profile_media_files pmf_photo WHERE p.id = pmf_photo.profile_id AND pmf_photo.type = 'photo') 
  938.                     as `photos_count`,
  939.                 (SELECT COUNT(*) FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video') 
  940.                     as `videos_count`,
  941.                 (SELECT COUNT(*) FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie') 
  942.                     as `selfies_count`,
  943.                 p.primary_station_id 
  944.             FROM profiles `p`
  945.             JOIN cities `c` ON c.id = p.city_id 
  946.             WHERE p.id IN ($ids)
  947.             ORDER BY FIELD(p.id,$ids)";
  949.         $connection = $this->getEntityManager()->getConnection();
  950.         $result = $connection->executeQuery($sql);
  951.         $profiles = $result->fetchAllAssociative();
  953.         $sql = "SELECT 
  954.                     cs.id 
  955.                         as `id`,
  956.                     JSON_UNQUOTE(JSON_EXTRACT(cs.name, '$.ru')) 
  957.                         as `name`, 
  958.                     cs.uri_identity 
  959.                         as `uriIdentity`, 
  960.                     ps.profile_id
  961.                         as `profile_id`,
  962.                     csl.name
  963.                         as `line_name`,
  964.                     csl.color
  965.                         as `line_color`,
  966.                     cs.county_id, cs.district_id
  967.                 FROM profile_stations ps
  968.                 JOIN city_stations cs ON ps.station_id = cs.id 
  969.                 LEFT JOIN city_subway_station_lines cssl ON cssl.station_id = cs.id
  970.                 LEFT JOIN city_subway_lines csl ON csl.id = cssl.line_id
  971.                 WHERE ps.profile_id IN ($ids)";
  972.         $result = $connection->executeQuery($sql);
  973.         $stations = $result->fetchAllAssociative();
  974.         $districtIds = array_unique(array_column($stations, 'district_id'));
  975.         $districts = $this->districts->ofIds($districtIds);
  977.         $sql = "SELECT 
  978.                     s.id 
  979.                         as `id`,
  980.                     JSON_UNQUOTE(JSON_EXTRACT(s.name, '$.ru')) 
  981.                         as `name`, 
  982.                     s.group 
  983.                         as `group`, 
  984.                     s.uri_identity 
  985.                         as `uriIdentity`,
  986.                     pps.profile_id
  987.                         as `profile_id`,
  988.                     pps.service_condition
  989.                         as `condition`,
  990.                     pps.extra_charge
  991.                         as `extra_charge`,
  992.                     pps.comment
  993.                         as `comment`
  994.                 FROM profile_provided_services pps
  995.                 JOIN services s ON pps.service_id = s.id 
  996.                 WHERE pps.profile_id IN ($ids)
  997.                 ORDER BY s.group ASC, s.sort ASC, s.id ASC";
  998.         $result = $connection->executeQuery($sql);
  999.         $providedServices = $result->fetchAllAssociative();
  1001.         $result = array_map(function ($profile) use ($stations, $districts, $providedServices): ProfileListingReadModel {
  1002.             return $this->hydrateProfileRow2($profile, $stations, $districts, $providedServices);
  1003.         }, $profiles);
  1005.         return $result;
  1006.     }
  1008.     public function hydrateProfileRow2(array $row, array $stations, array $districts, array $services): ProfileListingReadModel
  1009.     {
  1010.         $profile = new ProfileListingReadModel();
  1011.         $profile->id = $row['id'];
  1013.         $profile->moderationStatus = $row['moderation_status'];
  1015.         $profile->city = new CityReadModel();
  1016.         $profile->city->id = $row['city_id'];
  1017.         $profile->city->name = $row['city_name'];
  1018.         $profile->city->uriIdentity = $row['city_uri_identity'];
  1019.         $profile->city->countryCode = $row['city_country_code'];
  1021.         $profile->uriIdentity = $row['uri_identity'];
  1022.         $profile->name = $row['name'];
  1023.         $profile->description = $row['description'];
  1024.         $profile->phoneNumber = $row['phone_number'];
  1026.         $profile->isMasseur = (bool)$row['is_masseur'];
  1027.         $profile->approved = (bool)$row['is_approved'];
  1029.         $profile->isUltraVip = $row['adboard_placement_type'] == AdBoardPlacement::POSITION_GROUP_ULTRA_VIP;
  1030.         $profile->isVip = $row['adboard_placement_type'] == AdBoardPlacement::POSITION_GROUP_VIP;
  1031.         $profile->isStandard = false !== array_search(
  1032.                 $row['adboard_placement_type'],
  1033.                 [
  1034.                     AdBoardPlacement::POSITION_GROUP_STANDARD_APPROVED, AdBoardPlacement::POSITION_GROUP_STANDARD,
  1035.                     AdBoardPlacement::POSITION_GROUP_WITHOUT_OWNER_APPROVED, AdBoardPlacement::POSITION_GROUP_WITHOUT_OWNER
  1036.                 ]
  1037.             );
  1038.         $profile->position = $row['adboard_placement_position'];
  1040.         $profile->active = null !== $row['adboard_placement_type'] || $row['has_top_placement'];
  1041.         $profile->hidden = $row['has_placement_hiding'] == true;
  1043.         $profile->personParameters = new ProfilePersonParametersReadModel();
  1044.         $profile->personParameters->age = $row['person_age'];
  1045.         $profile->personParameters->height = $row['person_height'];
  1046.         $profile->personParameters->weight = $row['person_weight'];
  1047.         $profile->personParameters->breastSize = $row['person_breast_size'];
  1048.         $profile->personParameters->bodyType = $row['person_body_type'];
  1049.         $profile->personParameters->hairColor = $row['person_hair_color'];
  1050.         $profile->personParameters->privateHaircut = $row['person_private_haircut'];
  1051.         $profile->personParameters->nationality = $row['person_nationality'];
  1052.         $profile->personParameters->hasTattoo = $row['person_has_tattoo'];
  1053.         $profile->personParameters->hasPiercing = $row['person_has_piercing'];
  1054.         $profile->stations = [];
  1055.         $profile->districts = [];
  1056.         $profile->counties = [];
  1057.         foreach ($stations as $station) {
  1058.             if ($profile->id !== $station['profile_id'])
  1059.                 continue;
  1060.             $profileStation = $profile->stations[$station['id']] ?? new StationReadModel($station['id'], $station['uriIdentity'], $station['name'], []);
  1061.             if (null !== $station['line_name']) {
  1062.                 $profileStation->lines[] = new StationLineReadModel($station['line_name'], $station['line_color']);
  1063.             }
  1064.             $profile->stations[$station['id']] = $profileStation;
  1066.             if (array_key_exists($station['district_id'] ?? 0, $districts) && !array_key_exists($station['district_id'], $profile->districts)) {
  1067.                 $profile->districts[$station['district_id']] = $districts[$station['district_id']];
  1068.             }
  1069.         }
  1071.         $primaryId = (int)$row['primary_station_id'];
  1073.         if (!empty($profile->stations)) {
  1074.             uasort($profile->stations, function (StationReadModel $a, StationReadModel $b) use ($primaryId) {
  1075.                 $aPrimary = $a->id === $primaryId;
  1076.                 $bPrimary = $b->id === $primaryId;
  1078.                 if ($aPrimary !== $bPrimary) {
  1079.                     return $aPrimary ? -1 : 1;
  1080.                 }
  1082.                 return strnatcasecmp($a->name, $b->name);
  1083.             });
  1084.         }
  1085.         if ($primaryId) {
  1086.             $profile->primaryStation = $profile->stations[$primaryId] ?? null;
  1087.         }
  1089.         $profile->providedServices = [];
  1090.         foreach ($services as $service) {
  1091.             if ($profile->id !== $service['profile_id'])
  1092.                 continue;
  1093.             $providedService = $profile->providedServices[$service['id']] ?? new ProvidedServiceReadModel(
  1094.                 $service['id'], $service['name'], $service['group'], $service['uriIdentity'],
  1095.                 $service['condition'], $service['extra_charge'], $service['comment']
  1096.             );
  1097.             $profile->providedServices[$service['id']] = $providedService;
  1098.         }
  1100.         $profile->selfies = $row['selfies_count'] ?? 0;
  1101.         $profile->videos = $row['videos_count'] ?? 0;
  1102.         $profile->photos = $row['photos_count'] ?? 0;
  1104.         $avatar = [
  1105.             'path' => $row['avatar_path'] ?? '',
  1106.             'type' => $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO
  1107.         ];
  1108.         if ($this->features->crop_avatar()) {
  1109.             $profile->avatar = $avatar;
  1110.         } else {
  1111.             $profile->mainPhoto = $avatar;
  1112.         }
  1114.         $profile->comments = $row['comments_count'] ?? 0;
  1116.         $profile->apartmentsPricing = new ProfileApartmentPricingReadModel();
  1117.         $profile->apartmentsPricing->oneHourPrice = $row['apartments_one_hour_price'];
  1118.         $profile->apartmentsPricing->twoHoursPrice = $row['apartments_two_hours_price'];
  1119.         $profile->apartmentsPricing->nightPrice = $row['apartments_night_price'];
  1121.         $profile->takeOutPricing = new ProfileTakeOutPricingReadModel();
  1122.         $profile->takeOutPricing->oneHourPrice = $row['take_out_one_hour_price'];
  1123.         $profile->takeOutPricing->twoHoursPrice = $row['take_out_two_hours_price'];
  1124.         $profile->takeOutPricing->nightPrice = $row['take_out_night_price'];
  1125.         $profile->takeOutPricing->locations = $row['take_out_locations'] ? array_map('intval', explode(',', $row['take_out_locations'])) : [];
  1127.         $profile->seo = $row['seo'] ? json_decode($row['seo'], true) : null;
  1129.         return $profile;
  1130.     }
  1132.     public function fetchMapProfilesByIds(ProfileIdINOrderedByINValues $specification): array
  1133.     {
  1134.         $ids = implode(',', $specification->getIds());
  1136.         $mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
  1137.         $mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
  1139.         $sql = "
  1140.             SELECT 
  1141.                 p.id, p.uri_identity, p.map_latitude, p.map_longitude, p.phone_number, p.is_masseur, p.is_approved,
  1142.                 p.person_age, p.person_breast_size, p.person_height, p.person_weight, pap.type as placement_type, p.primary_station_id,
  1143.                 JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru')) 
  1144.                     as `name`,
  1145.                 (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) 
  1146.                     as `avatar_path`,
  1147.                 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,
  1148.                 GROUP_CONCAT(ps.station_id) as `stations`,
  1149.                 GROUP_CONCAT(pps.service_id) as `services`,
  1150.                 EXISTS(SELECT * FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL) 
  1151.                     as `has_comments`,
  1152.                 EXISTS(SELECT * FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video') 
  1153.                     as `has_videos`,
  1154.                 EXISTS(SELECT * FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie') 
  1155.                     as `has_selfies`,
  1156.                 EXISTS(SELECT * FROM profile_top_placements ptp WHERE p.id = ptp.profile_id AND (NOW() BETWEEN ptp.placed_at AND ptp.expires_at))
  1157.                     as `has_top_placement`
  1158.             FROM profiles `p`
  1159.             LEFT JOIN profile_stations ps ON ps.profile_id = p.id
  1160.             LEFT JOIN profile_provided_services pps ON pps.profile_id = p.id
  1161.             LEFT JOIN profile_adboard_placements pap ON pap.profile_id = p.id
  1162.             WHERE p.id IN ($ids)
  1163.             GROUP BY p.id
  1164.             "; // AND p.map_latitude IS NOT NULL AND p.map_longitude IS NOT NULL; ORDER BY FIELD(p.id,$ids)
  1166.         $result = $this->getEntityManager()->getConnection()->executeQuery($sql);
  1167.         $profiles = $result->fetchAllAssociative();
  1169.         $result = array_map(function ($profile): ProfileMapReadModel {
  1170.             return $this->hydrateMapProfileRow($profile);
  1171.         }, $profiles);
  1173.         return $result;
  1174.     }
  1176.     public function hydrateMapProfileRow(array $row): ProfileMapReadModel
  1177.     {
  1178.         $profile = new ProfileMapReadModel();
  1179.         $profile->id = $row['id'];
  1181.         $profile->uriIdentity = $row['uri_identity'];
  1182.         $profile->name = $row['name'];
  1183.         $profile->phoneNumber = $row['phone_number'];
  1185.         $profile->avatar = ['path' => $row['avatar_path'] ?? '', 'type' => $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO];
  1187.         $profile->mapLatitude = $row['map_latitude'];
  1188.         $profile->mapLongitude = $row['map_longitude'];
  1190.         $profile->age = $row['person_age'];
  1191.         $profile->breastSize = $row['person_breast_size'];
  1192.         $profile->height = $row['person_height'];
  1193.         $profile->weight = $row['person_weight'];
  1195.         $profile->isMasseur = $row['is_masseur'];
  1196.         $profile->isApproved = $row['is_approved'];
  1197.         $profile->hasComments = $row['has_comments'];
  1198.         $profile->hasSelfies = $row['has_selfies'];
  1199.         $profile->hasVideos = $row['has_videos'];
  1201.         $profile->apartmentOneHourPrice = $row['apartments_one_hour_price'];
  1202.         $profile->apartmentTwoHoursPrice = $row['apartments_two_hours_price'];
  1203.         $profile->apartmentNightPrice = $row['apartments_night_price'];
  1205.         $profile->takeOutOneHourPrice = $row['take_out_one_hour_price'];
  1206.         $profile->takeOutTwoHoursPrice = $row['take_out_two_hours_price'];
  1207.         $profile->takeOutNightPrice = $row['take_out_night_price'];
  1209.         $profile->station = $row['primary_station_id'] ?? ($row['stations'] ? explode(',', $row['stations'])[0] : null);
  1210.         $profile->services = $row['services'] ? array_unique(explode(',', $row['services'])) : [];
  1212.         $profile->isPaid = $row['placement_type'] >= AdBoardPlacement::POSITION_GROUP_STANDARD || $row['has_top_placement'] !== null;
  1214. //        $prices = [ $row['apartments_one_hour_price'], $row['apartments_two_hours_price'], $row['apartments_night_price'],
  1215. //            $row['take_out_one_hour_price'], $row['take_out_two_hours_price'], $row['take_out_night_price'] ];
  1216. //        $prices = array_filter($prices, function($item) {
  1217. //            return $item != null;
  1218. //        });
  1219. //        $profile->price = count($prices) ? min($prices) : null;
  1221.         return $profile;
  1222.     }
  1224.     public function fetchAccountProfileListByIds(ProfileIdINOrderedByINValues $specification): array
  1225.     {
  1226.         $ids = implode(',', $specification->getIds());
  1228.         $mediaType = $this->features->crop_avatar() ? Photo::TYPE_AVATAR : Photo::TYPE_PHOTO;
  1229.         $mediaIsMain = $this->features->crop_avatar() ? 0 : 1;
  1231.         $sql = "
  1232.             SELECT 
  1233.                 p.*, JSON_UNQUOTE(JSON_EXTRACT(p.name, '$.ru')) 
  1234.                     as `name`, 
  1235.                 JSON_UNQUOTE(JSON_EXTRACT(p.description, '$.ru')) 
  1236.                     as `description`,
  1237.                 (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) 
  1238.                     as `avatar_path`,
  1239.                 (SELECT type FROM profile_adboard_placements pap WHERE p.id = pap.profile_id LIMIT 1) 
  1240.                     as `adboard_placement_type`,
  1241.                 c.id 
  1242.                     as `city_id`, 
  1243.                 JSON_UNQUOTE(JSON_EXTRACT(c.name, '$.ru')) 
  1244.                     as `city_name`, 
  1245.                 c.uri_identity 
  1246.                     as `city_uri_identity`,
  1247.                 c.country_code 
  1248.                     as `city_country_code`,
  1249.                 EXISTS(SELECT * FROM profile_top_placements ptp WHERE p.id = ptp.profile_id AND (NOW() BETWEEN ptp.placed_at AND ptp.expires_at))
  1250.                     as `has_top_placement`,
  1251.                 EXISTS(SELECT * FROM placement_hidings ph WHERE p.id = ph.profile_id AND ph.entity_type = 'profile') 
  1252.                     as `has_placement_hiding`,
  1253.                 (SELECT COUNT(*) FROM profile_comments pc WHERE p.id = pc.profile_id AND pc.deleted_at is NULL) 
  1254.                     as `comments_count`,
  1255.                 (SELECT COUNT(*) FROM profile_media_files pmf_photo WHERE p.id = pmf_photo.profile_id AND pmf_photo.type = 'photo') 
  1256.                     as `photos_count`,
  1257.                 (SELECT COUNT(*) FROM profile_media_files pmf_video WHERE p.id = pmf_video.profile_id AND pmf_video.type = 'video') 
  1258.                     as `videos_count`,
  1259.                 (SELECT COUNT(*) FROM profile_media_files pmf_selfie WHERE p.id = pmf_selfie.profile_id AND pmf_selfie.type = 'selfie') 
  1260.                     as `selfies_count`,
  1261.                 p.primary_station_id 
  1262.             FROM profiles `p`
  1263.             JOIN cities `c` ON c.id = p.city_id 
  1264.             WHERE p.id IN ($ids)
  1265.             ORDER BY FIELD(p.id,$ids)";
  1267.         $connection = $this->getEntityManager()->getConnection();
  1268.         $result = $connection->executeQuery($sql);
  1269.         $profiles = $result->fetchAllAssociative();
  1271.         $sql = "SELECT 
  1272.                     JSON_UNQUOTE(JSON_EXTRACT(cs.name, '$.ru')) 
  1273.                         as `name`, 
  1274.                     cs.uri_identity 
  1275.                         as `uriIdentity`, 
  1276.                     ps.profile_id
  1277.                         as `profile_id`,
  1278.                     cs.district_id, cs.county_id
  1279.                 FROM profile_stations ps
  1280.                 JOIN city_stations cs ON ps.station_id = cs.id                 
  1281.                 WHERE ps.profile_id IN ($ids)";
  1282.         $result = $connection->executeQuery($sql);
  1283.         $stations = $result->fetchAllAssociative();
  1284.         $districtIds = array_unique(array_column($stations, 'district_id'));
  1285.         $districts = $this->districts->ofIds($districtIds);
  1287.         $sql = "SELECT 
  1288.                     s.id 
  1289.                         as `id`,
  1290.                     JSON_UNQUOTE(JSON_EXTRACT(s.name, '$.ru')) 
  1291.                         as `name`, 
  1292.                     s.group 
  1293.                         as `group`, 
  1294.                     s.uri_identity 
  1295.                         as `uriIdentity`,
  1296.                     pps.profile_id
  1297.                         as `profile_id`,
  1298.                     pps.service_condition
  1299.                         as `condition`,
  1300.                     pps.extra_charge
  1301.                         as `extra_charge`,
  1302.                     pps.comment
  1303.                         as `comment`
  1304.                 FROM profile_provided_services pps
  1305.                 JOIN services s ON pps.service_id = s.id 
  1306.                 WHERE pps.profile_id IN ($ids)
  1307.                 ORDER BY s.group ASC, s.sort ASC, s.id ASC";
  1308.         $result = $connection->executeQuery($sql);
  1309.         $providedServices = $result->fetchAllAssociative();
  1311.         $result = array_map(function ($profile) use ($stations, $districts, $providedServices): ProfileListingReadModel {
  1312.             return $this->hydrateProfileRow2($profile, $stations, $districts, $providedServices);
  1313.         }, $profiles);
  1315.         return $result;
  1316.     }
  1318.     public function getCommentedProfilesPaged(User $owner): ORMQueryResult
  1319.     {
  1320.         $qb = $this->createQueryBuilder('profile')
  1321.             ->join('profile.comments', 'comment')
  1322.             ->andWhere('profile.owner = :owner')
  1323.             ->setParameter('owner', $owner)
  1324.             ->orderBy('comment.createdAt', 'DESC');
  1326.         return new ORMQueryResult($qb);
  1327.     }
  1329.     /**
  1330.      * @return ProfilePlacementPriceDetailReadModel[]
  1331.      */
  1332.     public function fetchOfOwnerPlacedPriceDetails(User $owner): array
  1333.     {
  1334.         $sql = "
  1335.             SELECT 
  1336.                 p.id, p.is_approved, psp.price_amount
  1337.             FROM profiles `p`
  1338.             JOIN profile_adboard_placements pap ON pap.profile_id = p.id AND pap.placement_price_id IS NOT NULL
  1339.             JOIN paid_service_prices psp ON pap.placement_price_id = psp.id
  1340.             WHERE p.user_id = {$owner->getId()}
  1341.         ";
  1343.         $result = $this->getEntityManager()->getConnection()->executeQuery($sql);
  1344.         $profiles = $result->fetchAllAssociative();
  1346.         return array_map(function (array $row): ProfilePlacementPriceDetailReadModel {
  1347.             return new ProfilePlacementPriceDetailReadModel(
  1348.                 $row['id'], $row['is_approved'], $row['price_amount'] / 24
  1349.             );
  1350.         }, $profiles);
  1351.     }
  1353.     /**
  1354.      * @return ProfilePlacementHidingDetailReadModel[]
  1355.      */
  1356.     public function fetchOfOwnerHiddenDetails(User $owner): array
  1357.     {
  1358.         $sql = "
  1359.             SELECT 
  1360.                 p.id, p.is_approved
  1361.             FROM profiles `p`
  1362.             JOIN placement_hidings ph ON ph.profile_id = p.id
  1363.             WHERE p.user_id = {$owner->getId()}
  1364.         ";
  1366.         $result = $this->getEntityManager()->getConnection()->executeQuery($sql);
  1367.         $profiles = $result->fetchAllAssociative();
  1369.         return array_map(function (array $row): ProfilePlacementHidingDetailReadModel {
  1370.             return new ProfilePlacementHidingDetailReadModel(
  1371.                 $row['id'], $row['is_approved'], true
  1372.             );
  1373.         }, $profiles);
  1374.     }
  1376.     protected function modifyListingQueryBuilder(QueryBuilder $qb, string $alias): void
  1377.     {
  1378.         $qb
  1379.             ->addSelect('city')
  1380.             ->addSelect('station')
  1381.             ->addSelect('photo')
  1382.             ->addSelect('video')
  1383.             ->addSelect('comment')
  1384.             ->addSelect('avatar')
  1385.             ->join(sprintf('%s.city', $alias), 'city');
  1386.         if (!in_array('station', $qb->getAllAliases()))
  1387.             $qb->leftJoin(sprintf('%s.stations', $alias), 'station');
  1388.         if (!in_array('photo', $qb->getAllAliases()))
  1389.             $qb->leftJoin(sprintf('%s.photos', $alias), 'photo');
  1390.         if (!in_array('video', $qb->getAllAliases()))
  1391.             $qb->leftJoin(sprintf('%s.videos', $alias), 'video');
  1392.         if (!in_array('avatar', $qb->getAllAliases()))
  1393.             $qb->leftJoin(sprintf('%s.avatar', $alias), 'avatar');
  1394.         if (!in_array('comment', $qb->getAllAliases()))
  1395.             $qb->leftJoin(sprintf('%s.comments', $alias), 'comment');
  1397.         $this->addFemaleGenderFilterToQb($qb, $alias);
  1399.         //TODO убрать, если все ок
  1400.         //$this->excludeHavingPlacementHiding($qb, $alias);
  1402.         if (!in_array('profile_adboard_placement', $qb->getAllAliases())) {
  1403.             $qb
  1404.                 ->leftJoin(sprintf('%s.adBoardPlacement', $alias), 'profile_adboard_placement');
  1405.         }
  1406.         $qb->addSelect('profile_adboard_placement');
  1408.         if (!in_array('profile_top_placement', $qb->getAllAliases())) {
  1409.             $qb
  1410.                 ->leftJoin(sprintf('%s.topPlacements', $alias), 'profile_top_placement');
  1411.         }
  1412.         $qb->addSelect('profile_top_placement');
  1414.         //if($this->features->free_profiles()) {
  1415.         if (!in_array('placement_hiding', $qb->getAllAliases())) {
  1416.             $qb
  1417.                 ->leftJoin(sprintf('%s.placementHiding', $alias), 'placement_hiding');
  1418.         }
  1419.         $qb->addSelect('placement_hiding');
  1420.         //}
  1421.     }
  1423.     protected function addActiveFilterToQb(QueryBuilder $qb, string $dqlAlias)
  1424.     {
  1425.         if (!in_array('profile_adboard_placement', $qb->getAllAliases())) {
  1426.             $qb
  1427.                 ->join(sprintf('%s.adBoardPlacement', $dqlAlias), 'profile_adboard_placement');
  1428.         }
  1429.     }
  1431.     private function excludeHavingPlacementHiding(QueryBuilder $qb, $alias): void
  1432.     {
  1433.         if ($this->features->free_profiles()) {
  1434. //            if (!in_array('placement_hiding', $qb->getAllAliases())) {
  1435. //                $qb
  1436. //                    ->leftJoin(sprintf('%s.placementHiding', $alias), 'placement_hiding')
  1437. //                    ->andWhere(sprintf('placement_hiding IS NULL'))
  1438. //                ;
  1439. //        }
  1441.             $sub = new QueryBuilder($qb->getEntityManager());
  1442.             $sub->select("exclude_hidden_placement_hiding");
  1443.             $sub->from($qb->getEntityManager()->getClassMetadata(PlacementHiding::class)->name, "exclude_hidden_placement_hiding");
  1444.             $sub->andWhere(sprintf('exclude_hidden_placement_hiding.profile = %s', $alias));
  1446.             $qb->andWhere($qb->expr()->not($qb->expr()->exists($sub->getDQL())));
  1447.         }
  1448.     }
  1449. }