src/Repository/ProfileRepository.php line 96

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