src/Repository/OfferRepository.php line 784

Open in your IDE?
  1. <?php
  3. namespace Slivki\Repository;
  5. use DateTimeImmutable;
  6. use Doctrine\ORM\AbstractQuery;
  7. use Doctrine\ORM\EntityRepository;
  8. use Doctrine\ORM\Query;
  9. use Doctrine\ORM\QueryBuilder;
  10. use League\Period\Period;
  11. use Slivki\Dao\Statistic\PurchasesByOfferDao;
  12. use Slivki\Entity\Banner;
  13. use Slivki\Entity\CacheReloadScheduler;
  14. use Slivki\Entity\Category;
  15. use Slivki\Entity\City;
  16. use Slivki\Entity\Director;
  17. use Slivki\Entity\EntityDescription;
  18. use Slivki\Entity\GeoLocation;
  19. use Slivki\Entity\Media;
  20. use Slivki\Entity\Offer;
  21. use Slivki\Entity\OfferExtension;
  22. use Slivki\Entity\OfferPayedCategory;
  23. use Slivki\Entity\PurchaseCount;
  24. use Slivki\Entity\OfferOrder;
  25. use Slivki\Entity\Seo;
  26. use Slivki\Entity\SiteSettings;
  27. use Slivki\Entity\UserGroup;
  28. use Slivki\Entity\Visit;
  29. use Slivki\Exception\OfferNotFoundException;
  30. use Slivki\Services\ImageService;
  31. use Slivki\Util\Logger;
  32. use Slivki\Util\SoftCache;
  33. use Slivki\Entity\User;
  34. use Slivki\Entity\EntityOption;
  35. use Symfony\Component\Config\Definition\Exception\Exception;
  37. class OfferRepository extends EntityRepository {
  38.     const CACHE_NAME = "offers";
  39.     const CACHE_NAME_TEASERS = 'offers-teasers';
  40.     const CACHE_NAME_TEASERS_MOBILE = 'teasers-mobile';
  41.     const LOCKED_KEY = "lock";
  42.     const SORTED_OFFERS_CACHE_KEY = "sorted-offers-list-";
  43.     const SORTED_OFFERS_CACHE_NAME = "sortedOffersByPosition";
  44.     const SORT_MODE_DEFAULT = 'po-promokodam';
  45.     const SORT_MODE_BY_VISIT = 'po-prosmotram';
  46.     const SORT_MODE_BY_RATING = 'po-ocenkam';
  47.     const COMPANIES_RATING_DATA_CACHE_KEY = 'company-rating-data-';
  48.     const OFFER_POSITIONS_CACHE_KEY = 'positions';
  49.     const OFFER_LOCATIONS_CACHE_KEY = 'locations';
  50.     const FILTER_DATA_CACHE_KEY = 'filterData';
  51.     const NEW_SUBCATEGORY_CACHE_KEY = '-new';
  52.     const RESIZED_IMAGE_PREFIX = '_resized_';
  53.     const CACHE_NAME_GEO_LOCATION_DATA = 'offer-geolocation-data';
  54.     const OFFER_CITIES_CACHE_NAME = 'offer-cities';
  56.     private static $purchaseCountListByCategory;
  57.     private static $purchaseCountList;
  58.     private static $allOffersList;
  59.     private static $offerRatingList;
  60.     private static $offersOnlyPayedMonthlyPurchaseCount;
  62.     /**
  63.      * @throws OfferNotFoundException
  64.      */
  65.     public function getById(int $offerId): Offer
  66.     {
  67.         $queryBuilder = $this->createQueryBuilder('offer');
  68.         $expr = $queryBuilder->expr();
  70.         $offer = $queryBuilder
  71.             ->andWhere($expr->eq('offer.ID', ':offerId'))
  72.             ->setParameter('offerId', $offerId)
  73.             ->getQuery()
  74.             ->getOneOrNullResult();
  76.         if (!$offer instanceof Offer) {
  77.             throw OfferNotFoundException::missingId($offerId);
  78.         }
  80.         return $offer;
  81.     }
  83.     public function getActiveOffersByCategoryID($categoryID) {
  84.         ini_set('memory_limit', '4g');
  85.         $softCache = new SoftCache(self::CACHE_NAME);
  86.         $category = $this->getEntityManager()->getRepository(Category::class)->findCached($categoryID);
  87.         $offerList = [];
  88.         if(isset($category['entityList'])) {
  89.             $offerList = $softCache->getMulti($category['entityList']);
  90.         }
  91.         if (!$offerList) {
  92.             return [];
  93.         }
  94.         $offerListCached = [];
  95.         foreach ($offerList as $key => $offer) {
  96.             if ($offer) {
  97.                 $offerListCached[$key] = $offer;
  98.             }
  99.         }
  101.         return $offerListCached;
  102.     }
  104.     public function getAllActiveOffersCached() {
  105.         $entityList = [];
  106.         $categoryList = $this->getEntityManager()->getRepository(Category::class)->getAllActiveCategoriesFromCache();
  107.         foreach ($categoryList as $category) {
  108.             if ($category['category']->getDomainObjectID() == Category::OFFER_CATEGORY_ID) {
  109.                 if (isset($category['entityList']) && is_array($category['entityList'])) {
  110.                     $entityList = array_merge($entityList, $category['entityList']);
  111.                 } else {
  112.                     Logger::instance('getAllActiveOffersCached')->info('Category ' . $category['category']->getID() . ' entityList not array');
  113.                 }
  114.             }
  115.         }
  116.         $entityList = array_unique($entityList);
  117.         $softCache = new SoftCache(self::CACHE_NAME);
  118.         $offerList = $softCache->getMulti($entityList);
  120.         return $offerList;
  121.     }
  123.     public function setAllOffersList() {
  124.         self::$allOffersList = $this->getAllActiveOffersNoCache();
  125.     }
  127.     public function getAllActiveOffersNoCache(){
  128.         $dql = "select offer from Slivki:Offer offer
  129.                 where offer.active = true
  130.                 and offer.activeSince < CURRENT_TIMESTAMP() 
  131.                 and offer.activeTill > CURRENT_TIMESTAMP()
  132.                 order by offer.active desc";
  133.         $offers = $this->getEntityManager()->createQuery($dql)->getResult();
  134.         return $offers;
  135.     }
  137.     public function getActiveOffersByCategoryIDNoCache($categoryID) {
  138.         $dql = "select offer from Slivki:Offer offer
  139.             join offer.categories category
  140.             where category.ID = :categoryID
  141.               and offer.active = true
  142.               and offer.activeSince < CURRENT_TIMESTAMP()
  143.               and offer.activeTill > CURRENT_TIMESTAMP()
  144.             order by offer.active desc";
  145.         $offerList = $this->getEntityManager()->createQuery($dql)->setParameter("categoryID", $categoryID)->getResult();
  146.         return $offerList;
  147.     }
  149.     public function getAdvOfferList() {
  150.         return $this->getAllActiveOffersNoCache();
  151.     }
  153.     public function getAllOffersByCategoryID($categoryID) {
  154.         $dql = "select offer from Slivki:Offer offer join offer.categories category where category.ID = :categoryID order by offer.active desc";
  155.         $offerList = $this->getEntityManager()->createQuery($dql)->setParameter("categoryID", $categoryID)->getResult();
  156.         return $offerList;
  157.     }
  159.     public function getCountActiveOffersByCategory(Category $category) {
  160.         $offerList = $this->getActiveOffersByCategoryID($category->getID());
  161.         if (!$offerList) {
  162.             return 0;
  163.         }
  164.         return count($offerList);
  165.     }
  167.     /**
  168.      * @return boolean
  169.      */
  170.     public function isOfferFreeForUser(Offer $offer, User $user = null) {
  171.         if (!$offer->isInFreeCodesCategory()) {
  172.             return false;
  173.         }
  174.         if ($offer->isFree() || !$user || $user->getID() == User::FAKE_USER_ID) {
  175.             return true;
  176.         }
  177.         $orderStatus = OfferOrder::STATUS_CONFIRM_FREE_IMPLICITLY;
  178.         $dql = "select count(orderDetails) as amount from Slivki:OfferOrder offerOrder join offerOrder.offerOrderDetails orderDetails
  179.         where offerOrder.userID = :userID and offerOrder.offerID = :offerID and offerOrder.status = :status and offerOrder.createdOn between :from and :to";
  180.         $query = $this->getEntityManager()->createQuery($dql);
  181.         $query->setParameter('userID', $user->getID());
  182.         $query->setParameter('offerID', $offer->getID());
  183.         $query->setParameter('status', $orderStatus);
  184.         $query->setParameter("from", date_format(new \DateTime(), 'Y-m-d 00:00:00'));
  185.         $query->setParameter("to", date_format(new \DateTime(), 'Y-m-d 23:59:59'));
  186.         $result = $query->getOneOrNullResult(Query::HYDRATE_SCALAR);
  187.         if ($offer->getID() == Offer::PETROL_OFFER_ID) {
  188.             return $result['amount'] < 2;
  189.         } else {
  190.             return $result['amount'] == 0;
  191.         }
  192.     }
  194.     /**
  195.      * @return \Slivki\Entity\Offer
  196.      * @deprecated
  197.      */
  198.     public function findCached($offerID) {
  199.         $softCache = new SoftCache(self::CACHE_NAME);
  201.         return $softCache->get($offerID);
  202.     }
  204.     /**
  205.      * @return \Slivki\Entity\Offer|null
  206.      * @deprecated
  207.      */
  208.     public function getAnyWay($offerID) {
  209.         $offer = $this->findCached($offerID);
  210.         if (!$offer) {
  211.             $offer = $this->find($offerID);
  212.         }
  214.         return $offer;
  215.     }
  217.     public function getUsedCodesCount($offerID) {
  218.         $dql = "select count(details) from  Slivki:OfferOrderDetails details where details.offerOrderID=:offerID";
  219.         $query = $this->getEntityManager()->createQuery($dql)->setParameter("offerID", $offerID);
  220.         $usedCodesCount = $query->getSingleScalarResult();
  221.         return $usedCodesCount;
  222.     }
  224.     public function getOfferMonthlyPurchaseCount($offerID, $days = 30) {
  225.         if ($days == 30) {
  226.             $sql = "select purchase_count_last_month_with_correction from purchase_count where entity_id = " . $offerID;
  227.             return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn();
  228.         }
  229.         $sql = "select count(*) from offer_code inner join offer_order on offer_code.offer_order_id = offer_order.id
  230.             where offer_order.offer_id = $offerID and offer_code.created_on > (now() + '- $days days')";
  231.         return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn();
  232.     }
  234.     public function getOfferList($ids) {
  235.         $softCache = new SoftCache(self::CACHE_NAME);
  236.         $offerList =  $softCache->getMulti($ids);
  237.         if (!$offerList) {
  238.             return [];
  239.         }
  241.         return $offerList;
  242.     }
  244.     public function getCodeCost(Offer $offer = null, $codesCount = null)
  245.     {
  246.         if ($offer) {
  247.             $offerId = $offer->getID();
  249.             if ($offerId === 283793) {
  250.                 return 0;
  251.             }
  252.         }
  254.         $siteSettingsRepository = $this->getEntityManager()->getRepository(SiteSettings::class);
  255.         if (!$offer) {
  256.             return $siteSettingsRepository->getSiteSettingsCached()->getCodeCost();
  257.         }
  259.         if ($offer->getCodeCostByCount()) {
  260.             $codeCost = null;
  261.             foreach ($offer->getCodeCostByCount() as $count => $cost) {
  262.                 if ($count > $codesCount) {
  263.                     break;
  264.                 }
  265.                 $codeCost = $cost;
  266.             }
  268.             return $codeCost;
  269.         }
  271.         return $offer->getCodeCost() ?? $siteSettingsRepository->getSiteSettingsCached()->getCodeCost();
  272.     }
  274.     public function getOfferExtensionCodeCost(OfferExtension $extension) {
  275.         if ($extension->getCodePrice() != null) {
  276.             return $extension->getCodePrice();
  277.         }
  278.         return $this->getCodeCost($extension->getOffer());
  279.     }
  281.     /** @deprecated  */
  282.     public function putOfferToCache(Offer $offer) {
  283.         $softCache = new SoftCache(self::CACHE_NAME);
  284.         $cacheKey = $offer->getID() . self::LOCKED_KEY;
  285.         while (!$softCache->add($cacheKey, self::LOCKED_KEY, 60));
  286.         $softCache->set($offer->getID(), $offer, 0);
  287.         $softCache->set($cacheKey, null, 1);
  288.     }
  290.     public function reloadCache() {
  291.         $dql = "select offer, descriptions, offerCodePools, geoLocations, category from Slivki:Offer offer index by offer.ID inner join offer.categories category
  292.           left join offer.descriptions as descriptions left join offer.offerCodePools offerCodePools left join offer.geoLocations as geoLocations  
  293.           where category.active = true and offer.active = true and CURRENT_TIMESTAMP() between offer.activeSince and offer.activeTill
  294.           and offer.hidden = false";
  295.         $offerList = $query = $this->getEntityManager()->createQuery($dql)->getResult();
  296.         $offerListByCategory = [];
  297.         $tagList = [];
  298.         $offerListCached = [];
  299.         $cityList = [];
  300.         $mediaRepository = $this->getEntityManager()->getRepository(Media::class);
  301.         foreach ($offerList as $offer) {
  302.             $this->loadOfferData($offer, $mediaRepository, $tagList, $offerListByCategory);
  303.             $offerListCached[$offer->getID()] = $offer;
  304.         }
  305.         $categoryListCached = [];
  306.         $categoryList = $this->getEntityManager()->getRepository(Category::class)->getActiveCategoriesNotCached(Category::OFFER_CATEGORY_ID);
  307.         foreach ($categoryList as $category) {
  308.             if (!isset($offerListByCategory[$category->getID()])) {
  309.                 continue;
  310.             }
  311.             $offerList = $offerListByCategory[$category->getID()];
  312.             if (count($offerList) == 0) {
  313.                 continue;
  314.             }
  315.             $category->getParentCategories()->toArray();
  316.             $category->setEntityCount(count($offerList));
  317.             $category->setHotFeedIconMedia($mediaRepository->getСategoryHotFeedIconMedia($category->getID()));
  318.             $category->getCity()->getID();
  319.             $city = $category->getCity();
  320.             if (!isset($cityList[$city->getID()])) {
  321.                 $cityList[$city->getID()] = $city;
  322.             }
  323.             $categoryListCached[$category->getID()] = ['category' => $category, 'entityList' => $offerList];
  324.         }
  325.         $this->getEntityManager()->clear();
  326.         $softCache = new SoftCache(self::CACHE_NAME);
  327.         $softCache->setMulti($offerListCached, 0);
  328.         $softCache = new SoftCache(CategoryRepository::CACHE_NAME);
  329.         $softCache->set(CategoryRepository::ALL_CATEGORIES_CACHE_KEY, $categoryListCached, 0);
  330.         $softCache->setMulti($categoryListCached, 0);
  331.         $softCache = new SoftCache(CityRepository::CACHE_NAME);
  332.         $softCache->set(CityRepository::CACHE_KEY_ACTIVE, $cityList, 0);
  333.     }
  335.     /** @deprecated  */
  336.     public function reloadCacheForOneOffer($offerID, $source = '') {
  337.         $offerOrderRepository = $this->getEntityManager()->getRepository(OfferOrder::class);
  338.         $logger = Logger::instance('reloadCacheForOneOffer');
  339.         try {
  340.             $logger->info('Lock code pool for offer ' . $offerID);
  341.             $offerOrderRepository->lockCodePool($offerID);
  342.             /** @var \Slivki\Entity\Offer $offer */
  343.             $offer = $this->find($offerID);
  344.             if (!$offer->isActive() || !$offer->isInActivePeriod() || $offer->isHidden()) {
  345.                 $this->removeOfferFromCache($offerID);
  346.                 $logger->info('Unlock code pool for offer ' . $offerID . '. Offer is not active');
  347.                 $offerOrderRepository->unlockCodePool($offerID);
  348.                 return;
  349.             }
  350.             if ($offer->getDescriptionByType(EntityDescription::TYPE_OFFER_CONDITIONS_ID) == '') {
  351.                 Logger::instance('DESCRIPTION-DEBUG')->info("$offerID empty description. $source");
  352.             }
  353.             $mediaRepository = $this->getEntityManager()->getRepository(Media::class);
  354.             $tagList = [];
  355.             $offerListByCategory = [];
  356.             $this->loadOfferData($offer, $mediaRepository, $tagList, $offerListByCategory);
  357.             foreach ($offer->getGeoLocations() as $geoLocation) {
  358.                 $geoLocation->getPhoneNumbers()->toArray();
  359.             }
  360.             $softCache = new SoftCache(self::CACHE_NAME);
  361.             $softCache->set($offerID, $offer, 0);
  362.             $logger->info('Unlock code pool for offer ' . $offerID);
  363.             $offerOrderRepository->unlockCodePool($offerID);
  364.             $categoryList = $this->getEntityManager()->getRepository(Category::class)->getAllActiveCategoriesFromCache();
  365.             $categoryListChanged = false;
  366.             $categoryRepository = $this->getEntityManager()->getRepository(Category::class);
  367.             $cityList = $this->getEntityManager()->getRepository(City::class)->getActiveCitiesCached();
  368.             foreach ($offer->getCategories() as $category) {
  369.                 if (!isset($categoryList[$category->getID()])) {
  370.                     $category->getBanners()->toArray();
  371.                     $category->setEntityCount(1);
  372.                     $categoryForCache['category'] = $category;
  373.                     $categoryForCache['entityList'][] = $offerID;
  374.                     $categoryList[$category->getID()] = $categoryForCache;
  375.                     $categoryListChanged = true;
  376.                     //$categoryRepository->putCategoryToCache($categoryForCache);
  377.                 }
  378.                 $city = $category->getCity();
  379.                 if ($city && !isset($cityList[$city->getID()])) {
  380.                     $cityList[$city->getID()] = $city;
  381.                 }
  382.             }
  383.             foreach ($offerListByCategory as $categoryID => $category) {
  384.                 if (!isset($categoryList[$categoryID]['entityList'])) {
  385.                     $categoryList[$categoryID]['entityList'] = [];
  386.                 }
  387.                 if (!in_array($offerID, $categoryList[$categoryID]['entityList'])) {
  388.                     $categoryList[$categoryID]['entityList'][] = $offerID;
  389.                     $categoryList[$categoryID]['category']->setEntityCount(count($categoryList[$categoryID]['entityList']));
  390.                     $categoryListChanged = true;
  391.                     //$categoryRepository->putCategoryToCache($categoryList[$categoryID]);
  392.                 }
  393.             }
  394.             foreach ($categoryList as $categoryID => $category) {
  395.                 if (!isset($category['entityList']) || !$category['entityList']) {
  396.                     continue;
  397.                 }
  398.                 $index = array_search($offerID, $category['entityList']);
  399.                 if (false !== $index && !isset($offerListByCategory[$categoryID])) {
  400.                     unset($categoryList[$categoryID]['entityList'][$index]);
  401.                     $categoryList[$categoryID]['category']->setEntityCount(count($categoryList[$categoryID]['entityList']));
  402.                     $categoryListChanged = true;
  403.                     //$categoryRepository->putCategoryToCache($categoryList[$categoryID]);
  404.                 }
  405.             }
  406.             if ($categoryListChanged) {
  407.                 $categoryRepository->cacheAllActiveCategories($categoryList);
  408.             }
  410.             $softCache = new SoftCache(CityRepository::CACHE_NAME);
  411.             $softCache->set(CityRepository::CACHE_KEY_ACTIVE, $cityList, 0);
  412.         } catch (Exception $e) {
  413.             $logger->info('Unlock code pool for offer ' . $offerID . '. ' . $e->getMessage());
  414.             $offerOrderRepository->unlockCodePool($offerID);
  415.         }
  416.         return $offer;
  417.     }
  419.     private function loadOfferData(Offer &$offer, MediaRepository &$mediaRepository, &$tagList, &$offerListByCategory) {
  420.         $offerID = $offer->getID();
  421.         if ($offer->getTeaserMedia()) {
  422.             $offer->getTeaserMedia()->getID();
  423.         }
  424.         $offer->setHotFeedIconMedia($mediaRepository->getOfferHotFeedIconMedia($offerID));
  425.         $offer->setDetailMeidas($mediaRepository->getOfferDetailMedias($offerID));
  426.         $offer->setShopMedias($mediaRepository->getOfferShopMedias($offerID));
  427.         $offer->getPhoneNumbers()->toArray();
  428.         $offer->getGiftCertificates()->toArray();
  429.         $offer->getExtensions()->toArray();
  430.         $offer->getOfferCodePools()->toArray();
  431.         $offer->getGeoLocations()->toArray();
  432.         $offer->getPhoneNumbers()->toArray();
  433.         $offer->getExtensions()->toArray();
  434.         $offer->getGiftCertificates()->toArray();
  435.         $offer->getSupplierPhotoMedias()->toArray();
  436.         foreach ($offer->getCategories() as $category) {
  437.             $offerListByCategory[$category->getID()][] = $offer->getID();
  438.         }
  439.         if($offer->isActiveCurrencyCalculator()) {
  440.             $offer->getBankCurrency()->getRate();
  441.         }
  442.         foreach ($offer->getDescriptions()->toArray() as $description) {
  443.             $description->getEntityDescriptionSliderImages()->toArray();
  444.         }
  445.     }
  447.     public function removeOfferFromCache($offerID) {
  448.         $softCache = new SoftCache(self::CACHE_NAME);
  449.         $offer = $softCache->get($offerID);
  450.         if (!$offer) {
  451.             return;
  452.         }
  453.         $softCache->delete($offerID);
  454.         $categoryRepository = $this->getEntityManager()->getRepository(Category::class);
  455.         $offersByCategory = $categoryRepository->getAllActiveCategoriesFromCache();
  456.         $arrayChanged = false;
  457.         foreach ($offersByCategory as $key => $category) {
  458.             if ($category['category']->getDomainObjectID() == Category::OFFER_CATEGORY_ID) {
  459.                 if (!isset($category['entityList']) || !is_array($category['entityList'])) {
  460.                     continue;
  461.                 }
  462.                 $entityCount = count($category['entityList']);
  463.                 $offersByCategory[$key]['entityList'] = array_diff($category['entityList'], [$offerID]);
  464.                 if (count($offersByCategory[$key]['entityList']) != $entityCount) {
  465.                     $arrayChanged = true;
  466.                     if (count($offersByCategory[$key]['entityList']) == 0) {
  467.                         unset($offersByCategory[$key]);
  468.                     }
  469.                 }
  470.             }
  471.         }
  472.         if ($arrayChanged) {
  473.             $categoryRepository->cacheAllActiveCategories($offersByCategory);
  474.         }
  475.     }
  477.     public function getOffersOnlyPayedMonthlyPurchaseCount($offerID) {
  478.         if (!self::$offersOnlyPayedMonthlyPurchaseCount) {
  479.             $dbConnection = $this->getEntityManager()->getConnection();
  480.             $payedStatuses = [
  481.                 OfferOrder::STATUS_CONFIRM,
  482.                 OfferOrder::STATUS_EXTENSION_ORDER_CONFIRM,
  483.                 OfferOrder::STATUS_HALF_CONFIRM,
  484.                 OfferOrder::STATUS_BALANCE_CONFIRM
  485.             ];
  486.             $sql = "select offer_order.offer_id, count(*) as amount from offer_code inner join offer_order on offer_code.offer_order_id = offer_order.id 
  487.                 where offer_order.status in (" . join(',', $payedStatuses). ") and offer_code.created_on > date_trunc('day', now() + '-30 days')::timestamp without time zone group by 1";
  488.             $offersOnlyPayedMonthlyPurchaseCount = $dbConnection->executeQuery($sql)->fetchAll();
  489.             foreach ($offersOnlyPayedMonthlyPurchaseCount as $item) {
  490.                 self::$offersOnlyPayedMonthlyPurchaseCount[$item['offer_id']] = $item['amount'];
  491.             }
  492.         }
  493.         return isset(self::$offersOnlyPayedMonthlyPurchaseCount[$offerID]) ? self::$offersOnlyPayedMonthlyPurchaseCount[$offerID] : 0;
  494.     }
  496.     private function getPurchaseByCategory($categoryID, $dateFrom, $dateTo) {
  497.         $sql = "select count(*) as amount from offer_order_details inner join offer_code_pool on offer_order_details.offer_code_pool_id = offer_code_pool.id  
  498.           inner join category2entity on offer_code_pool.offer_id = category2entity.entity_id where category2entity.category_id = $categoryID   
  499.           and offer_order_details.created_on between '$dateFrom' and '$dateTo'";
  500.         $result = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetch(\PDO::FETCH_COLUMN);
  502.         return (int)$result;
  503.     }
  505.     private function compareBannersByPosition(Banner $banner, Banner $banner1) {
  506.         if ($banner->getPositionRow() < $banner1->getPositionRow()) {
  507.             return -1;
  508.         }
  509.         if ($banner->getPositionRow() == $banner1->getPositionRow()) {
  510.             return $banner->getPositionColumn() > $banner1->getPositionColumn() ? 1 : -1;
  511.         }
  513.         return 1;
  514.     }
  516.     public function getSortedOffersByPosition($categoryID) {
  517.         $dql = "select position.entityID from Slivki:OfferCategoryPosition as position where position.categoryID = :categoryID order by position.position asc";
  518.         $offersListSorted = $this->getEntityManager()->createQuery($dql)->setParameter('categoryID', $categoryID)->getResult();
  519.         return $offersListSorted;
  520.     }
  522.     public function getTeaserBanners(Category $category, $teaserInARowCount = 3 ) {
  523.         $category = $this->getEntityManager()->merge($category);
  524.         $teaserBanners = $category->getActiveBannersByType(Banner::TYPE_TEASER_BANNER);
  525.         $teaserVerticalBanners = $category->getActiveBannersByType(Banner::TYPE_TEASER_VERTICAL_BANNER);
  526.         $teaserBanners = array_merge($teaserBanners, $teaserVerticalBanners);
  527.         $teaserBannersPosition = array();
  528.         $withVerticalBannersRowList = array();
  529.         foreach ($teaserBanners as $banner) {
  530.             $position = ($banner->getPositionRow() - 1) * 3 + $banner->getPositionColumn();
  531.             if($banner->isActive()) {
  532.                 $teaserBannersPosition[$position] = $banner;
  533.                 if($banner->getTypeID() == Banner::TYPE_TEASER_VERTICAL_BANNER) {
  534.                     $withVerticalBannersRowList[] = (int)ceil($position / $teaserInARowCount);
  535.                 }
  536.             };
  537.         }
  538.         return array(
  539.             'teaserBanners' => $teaserBannersPosition,
  540.             'withVerticalBannersRowList' => $withVerticalBannersRowList
  541.         );
  543.     }
  545.     public function getOffersByCategoryWithPositions($categoryID, $limit = null) {
  546.         $dql = "select offer, offerCategoryPosition.position from Slivki:Offer offer
  547.           left join Slivki:OfferCategoryPosition offerCategoryPosition with offerCategoryPosition.entityID = offer.ID and offerCategoryPosition.categoryID = :categoryID
  548.           inner join offer.categories category where category.ID = :categoryID
  549.           order by offerCategoryPosition.position";
  550.         $query = $this->getEntityManager()->createQuery($dql);
  551.         $query->setParameter('categoryID', $categoryID);
  552.         if ($limit) {
  553.             $query->setMaxResults((int)$limit);
  554.         }
  555.         return $query->getResult();
  556.     }
  558.     public function getActiveOffersByCategoryWithoutTags($domainObjectID, $cityID) {
  559.         $dql = "select offer.* from offer 
  560.             inner join category2entity on offer.id = category2entity.entity_id 
  561.             inner join category on category2entity.category_id = category.id 
  562.             inner join entity_option on offer.id = entity_option.entity_id
  563.             where city_id = $cityID and category.domain_object_id = $domainObjectID and offer.active and entity_option.name = '".EntityOption::OPTION_OFFERS_WITHOUT_TAGS."'
  564.             and offer.active_till > now()
  565.             group by offer.id";
  566.         return $this->getEntityManager()->getConnection()->executeQuery($dql)->fetchAll(Query::HYDRATE_ARRAY);
  567.     }
  569.     public function getActiveOffersByCategoryWithoutTagsAllCity() {
  570.         $dql = "select offer.* from offer 
  571.             inner join category2entity on offer.id = category2entity.entity_id 
  572.             inner join category on category2entity.category_id = category.id 
  573.             inner join entity_option on offer.id = entity_option.entity_id
  574.             where offer.active and entity_option.name = '".EntityOption::OPTION_OFFERS_WITHOUT_TAGS."'
  575.             and offer.active_till > now()
  576.             group by offer.id";
  577.         return $this->getEntityManager()->getConnection()->executeQuery($dql)->fetchAll(Query::HYDRATE_ARRAY);
  578.     }
  580.     public function getOffersOnReview($domainObjectID, $cityID) {
  581.         $dql = "select offer.* from offer 
  582.             inner join category2entity on offer.id = category2entity.entity_id 
  583.             inner join category on category2entity.category_id = category.id 
  584.             where city_id = $cityID and category.domain_object_id = $domainObjectID and offer.review 
  585.             and offer.on_review group by offer.id";
  586.         return $this->getEntityManager()->getConnection()->executeQuery($dql)->fetchAll(Query::HYDRATE_ARRAY);
  587.     }
  589.     public function getOffersByCategorySortedByPopularity(Category $category) {
  590.         $categoryID = $category->getID();
  591.         $offerList = $this->getActiveOffersByCategoryIDNoCache($categoryID);
  592.         if (!$offerList) {
  593.             return false;
  594.         }
  595.         if ($categoryID != Category::NEW_OFFER_CATEGORY_ID) {
  596.             $dql = "select purchaseCount from Slivki:PurchaseCount purchaseCount index by purchaseCount.entityID
  597.           inner join Slivki:Offer offer with purchaseCount.entityID = offer.ID
  598.           inner join offer.categories category where category.ID = :categoryID";
  599.             $query = $this->getEntityManager()->createQuery($dql);
  600.             $query->setParameter('categoryID', $categoryID);
  601.             $query->execute();
  602.             self::$purchaseCountListByCategory = $query->getResult();
  603.             if (self::$purchaseCountListByCategory) {
  604.                 usort($offerList, ['\Slivki\Repository\OfferRepository', 'compareOffersByPurchaseCount']);
  605.                 self::$purchaseCountListByCategory = null;
  606.             }
  607.         } else {
  608.             usort($offerList, ['\Slivki\Repository\OfferRepository', 'compareOffersByRenewedOn']);
  609.         }
  610.         $payedPositions = $this->getEntityManager()->getRepository(OfferPayedCategory::class)->findBy(
  611.             ['categoryID' => $categoryID], ['position' => 'asc']);
  612.         if ($payedPositions) {
  613.             foreach ($payedPositions as $position) {
  614.                 foreach ($offerList as $key => $offer) {
  615.                     if ($offer->getID() == $position->getEntityID()) {
  616.                         unset($offerList[$key]);
  617.                         array_splice($offerList, $position->getPosition() - 1, 0, [$offer]);
  618.                         break;
  619.                     }
  620.                 }
  621.             }
  622.         }
  623.         return $offerList;
  624.     }
  626.     private static function compareOffersByID(Offer $offer, Offer $offer1) {
  627.         return $offer->getID() > $offer1->getID() ? -1 : 1;
  628.     }
  630.     private static function compareOffersByRenewedOn(Offer $offer, Offer $offer1) {
  631.         if ($offer->getRenewedOn() == $offer1->getRenewedOn()) {
  632.             return $offer->getID() > $offer1->getID() ? 1 : -1;
  633.         }
  634.         return $offer->getRenewedOn() > $offer1->getRenewedOn() ? -1 : 1;
  635.     }
  637.     private static function compareOffersByPurchaseCount(Offer $offer, Offer $offer1) {
  638.         /** @var \Slivki\Entity\PurchaseCount $purchaseCount */
  639.         /** @var \Slivki\Entity\PurchaseCount $purchaseCount1 */
  640.         $purchaseCount = isset(self::$purchaseCountListByCategory[$offer->getID()]) ? self::$purchaseCountListByCategory[$offer->getID()] : new PurchaseCount();
  641.         $purchaseCount1 = isset(self::$purchaseCountListByCategory[$offer1->getID()]) ? self::$purchaseCountListByCategory[$offer1->getID()] : new PurchaseCount();
  642.         $purchaseCountKoeff = $offer->getPurchaseKoeff() ?: ($offer->isInFreeCodesCategory() ? 3 : 1);
  643.         $purchaseCountKoeff1 = $offer1->getPurchaseKoeff() ?: ($offer->isInFreeCodesCategory() ? 3 : 1);
  644.         if ($purchaseCount->getPurchaseCountRecent()/$purchaseCountKoeff == $purchaseCount1->getPurchaseCountRecent()/$purchaseCountKoeff1) {
  645.             if ($purchaseCount->getPurchaseCountLastMonth()/$purchaseCountKoeff == $purchaseCount1->getPurchaseCountLastMonth()/$purchaseCountKoeff1) {
  646.                 if ($purchaseCount->getPurchaseCount()/$purchaseCountKoeff == $purchaseCount1->getPurchaseCount()/$purchaseCountKoeff1) {
  647.                     return $offer->getID() > $offer1->getID() ? 1 : -1;
  648.                 }
  649.                 return $purchaseCount->getPurchaseCount()/$purchaseCountKoeff > $purchaseCount1->getPurchaseCount()/$purchaseCountKoeff1 ? -1 : 1;
  650.             } else {
  651.                 return $purchaseCount->getPurchaseCountLastMonth()/$purchaseCountKoeff > $purchaseCount1->getPurchaseCountLastMonth()/$purchaseCountKoeff1 ? -1 : 1;
  652.             }
  653.         } else {
  654.             return $purchaseCount->getPurchaseCountRecent()/$purchaseCountKoeff > $purchaseCount1->getPurchaseCountRecent()/$purchaseCountKoeff1 ? -1 : 1;
  655.         }
  656.     }
  658.     public function findPastOfferCached($offerID) {
  659.         $softCache = new SoftCache(self::CACHE_NAME);
  660.         $cacheKey = 'pastOffer-0-' . $offerID;
  661.         $offer = $softCache->get($cacheKey);
  662.         if ($offer) {
  663.             return $offer;
  664.         }
  665.         $offer = $this->find($offerID);
  666.         if (!$offer) {
  667.             return null;
  668.         }
  669.         $offer->getOfferCodePools()->toArray();
  670.         $offer->getGeoLocations()->toArray();
  671.         $softCache->set($cacheKey, $offer, 0);
  673.         return $offer;
  674.     }
  676.     public function getVisitCount($offer, $today = false) {
  677.         if ($today) {
  678.             $dateFrom = new \DateTime('-1 days');
  679.         } else {
  680.             $dateFrom = new \DateTime('-30 days');
  681.         }
  682.         $dql = "select count(visit.ID) from Slivki:Visit visit 
  683.           where visit.entityID = :offerID and visit.entityTypeID = :entityTypeID and visit.createdOn > :dateFrom and visit.createdOn > :offerActiveSince";
  684.         return $this->getEntityManager()->createQuery($dql)
  685.             ->setParameter('dateFrom', $dateFrom)
  686.             ->setParameter('offerID', $offer->getID())
  687.             ->setParameter('offerActiveSince', $offer->getActiveSince())
  688.             ->setParameter('entityTypeID', Category::OFFER_CATEGORY_ID)
  689.             ->getSingleScalarResult();
  690.     }
  692.     public function getLastPurchaseTime($offerID) {
  693.         $sql = "select offer_order_details.created_on from offer_order_details inner join offer_order on offer_order_details.offer_order_id = offer_order.id
  694.           where offer_order.offer_id = $offerID and status > 0 and offer_order_details.created_on > now() + '-12 hours' order by offer_order_details.id desc limit 1";
  695.         $lastPurchaseTime = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn();
  696.         if ($lastPurchaseTime) {
  697.             $lastPurchaseTime = \DateTime::createFromFormat('Y-m-d H:i:s', $lastPurchaseTime);
  698.         }
  699.         return $lastPurchaseTime;
  700.     }
  702.     public function getRecentPurchaseCount($offerID) {
  703.         $purchaseData = $this->getEntityManager()->getRepository(PurchaseCount::class)->findOneByEntityID($offerID);
  704.         if (!$purchaseData) {
  705.             return 0;
  706.         }
  707.         return $purchaseData->getPurchaseCountLastDay();
  708.     }
  710.     public function getTeaserWatermark($offerID) {
  711.         $offer = $this->find($offerID);
  712.         if (!$offer) {
  713.             return [];
  714.         }
  715.         $mediaRepository = $this->getEntityManager()->getRepository(Media::class);
  716.         $watermark = $mediaRepository->getOfferTeaserLogo($offerID);
  717.         if ($watermark) {
  718.             return [
  719.                 'watermark' => $watermark,
  720.                 'width' => $offer->getTeaserLogoWidth(),
  721.                 'height' => $offer->getTeaserLogoHeight()
  722.             ];
  723.         }
  724.         $directors = $offer->getDirectors();
  725.         /** @var Director $director */
  726.         foreach ($directors as $director) {
  727.             $watermark = $mediaRepository->getDirectorLogo($director->getID());
  728.             if ($watermark) {
  729.                 return [
  730.                     'watermark' => $watermark,
  731.                     'width' => $director->getTeaserLogoWidth(),
  732.                     'height' => $director->getTeaserLogoHeight()
  733.                 ];
  734.             }
  735.         }
  736.         return [];
  737.     }
  739.     public function scheduleOfferReload($offerID) {
  740.         $scheduler = new CacheReloadScheduler();
  741.         $scheduler->setType(CacheReloadScheduler::TYPE_OFFER);
  742.         $scheduler->setEntityID($offerID);
  743.         $entityManager = $this->getEntityManager();
  744.         $entityManager->persist($scheduler);
  745.         $entityManager->flush($scheduler);
  746.     }
  748.     public function getActiveOffersCount($cityID = City::DEFAULT_CITY_ID) {
  749.         $sql = "select count(distinct offer.id) from offer inner join category2entity on offer.id = category2entity.entity_id
  750.           inner join category on category2entity.category_id = category.id 
  751.           where offer.active and category.city_id = " . (int)$cityID . " and now() between active_since and active_till";
  752.         return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn(0);
  753.     }
  755.     public function getActiveOffersCountCached($cityID = City::DEFAULT_CITY_ID) {
  756.         $softCache = new SoftCache(self::CACHE_NAME);
  757.         $cacheKey = 'activeCount-' . $cityID;
  758.         $offersCount = $softCache->get($cacheKey);
  759.         if (!$offersCount) {
  760.             $offersCount = $this->getActiveOffersCount($cityID);
  761.             $softCache->set($cacheKey, $offersCount, 30 * 60);
  762.         }
  763.         return $offersCount;
  764.     }
  766.     public function getOfferGeoLocations(Offer $offer) {
  767.         $seoRepository = $this->getEntityManager()->getRepository(Seo::class);
  768.         $result = [];
  769.         /** @var GeoLocation $geoLocation */
  770.         foreach($offer->getGeoLocations() as $geoLocation) {
  771.             $geoLocationStreet = $geoLocation->getStreet() ? $geoLocation->getStreet() . ', ' : '';
  772.             $geoLocationHouse = $geoLocation->getHouse() ? $geoLocation->getHouse() : '';
  773.             $geoLocationDescription = $geoLocation->getDescription() ? $geoLocation->getDescription() . ' - ' : '';
  774.             $geoLocationInfos['markerAnnotation'] = $geoLocationDescription . $geoLocationStreet . $geoLocationHouse;
  775.             $geoLocationInfos['latitude'] = $geoLocation->getLatitude();
  776.             $geoLocationInfos['longitude'] = $geoLocation->getLongitude();
  777.             $geoLocationInfos['offerURI'] = $seoRepository->getOfferURL($offer->getID())->getMainAlias();
  778.             $result[]['geoLocationInfos'][] = $geoLocationInfos;
  779.         }
  781.         return $result;
  782.     }
  784.     public function getOfferGeoLocationData(Offer $offer, $userGeoLocation, ImageService $imageService, $jsonEncode = true, $baseURL = '') {
  785.         $seoRepository = $this->getEntityManager()->getRepository(Seo::class);
  786.         $features = [];
  787.         $url = '';
  788.         $seo = $seoRepository->getByEntity(SeoRepository::RESOURCE_URL_OFFER_DETAILS, $offer->getID());
  789.         if ($seo) {
  790.             $url = $seo->getMainAlias();
  791.         }
  792.         $closestLocationID = 0;
  793.         $offerGeoLocations = $offer->getGeoLocations();
  794.         foreach ($offerGeoLocations as $geoLocation) {
  795.             $caption = $geoLocation->getStreet() . ', ' . $geoLocation->getHouse();
  796.             $teaserURL = '';
  797.             if ($offer->getTeaserMedia()) {
  798.                 $teaserURL = $baseURL . $imageService->getImageURL($offer->getTeaserMedia(), 0, 0);
  799.             }
  800.             $features[] = [
  801.                 'type' => 'Feature',
  802.                 'id' => $geoLocation->getID(),
  803.                 'geometry' => [
  804.                     'type' => 'Point',
  805.                     'coordinates' => [$geoLocation->getLatitude(), $geoLocation->getLongitude()]
  806.                 ],
  807.                 'properties' => [
  808.                     'iconClass' => 'always-caption',
  809.                     'iconContent' => '',
  810.                     'locationID' => $geoLocation->getID(),
  811.                     'offerID' => $offer->getID(),
  812.                     'offerTitle' => $offer->getTitle(),
  813.                     'teaserURL' => $teaserURL,
  814.                     'offerType' => $offer->getOfferType(),
  815.                     'url' => $url,
  816.                     'hintContent' => $caption
  817.                 ]
  818.             ];
  819.         }
  820.         $getLocationData = ['type' => 'FeatureCollection', 'features' => $features];
  822.         if ($jsonEncode) {
  823.             $data = json_encode($getLocationData);
  824.         } else {
  825.             $data = $getLocationData;
  826.         }
  828.         return $data;
  829.     }
  831.     public function getOfferIDListByCategory($categoryID) {
  832.         $sql = "select category2entity.entity_id from category2entity inner join offer on category2entity.entity_id = offer.id 
  833.               where category2entity.category_id = " . (int)$categoryID;
  834.         return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchAll(\PDO::FETCH_COLUMN);
  835.     }
  837.     public function getLastPurchaseDate($entityID, $userID) {
  838.         $sql = "select offer_order_details.created_on::date from offer_order_details inner join offer_order on offer_order_details.offer_order_id = offer_order.id
  839.             where offer_order.offer_id = $entityID and offer_order.user_id = $userID order by offer_order_details.id desc limit 1";
  840.         $lastPurchaseDate = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn();
  841.         return \DateTime::createFromFormat('Y-m-d', $lastPurchaseDate);
  842.     }
  844.     public function getOfferCityIDList(Offer $offer) {
  845.         $softCache = new SoftCache(self::OFFER_CITIES_CACHE_NAME);
  846.         $cityIDList = $softCache->get($offer->getID());
  847.         if (!$cityIDList) {
  848.             $cityIDList = $this->reloadOfferCityIDListCache($offer->getID());
  849.         }
  850.         return $cityIDList;
  851.     }
  853.     public function reloadOfferCityIDListCache($offerID) {
  854.         /** @var Offer $offer */
  855.         $offer = $this->find($offerID);
  856.         if (!$offer) {
  857.             return false;
  858.         }
  859.         $cityIDList = [];
  860.         /** @var Category $category */
  861.         foreach ($offer->getCategories() as $category) {
  862.             $categoryID = $category->getID();
  863.             if ($categoryID == Category::COUNTRY_CATEGORY_ID || $category->isChildOfRecursive(Category::COUNTRY_CATEGORY_ID)
  864.                 || $categoryID == Category::NEW_OFFER_CATEGORY_ID || $category->isChildOfRecursive(Category::NEW_OFFER_CATEGORY_ID)) {
  865.                 continue;
  866.             }
  867.             if ($category->getCity() && !in_array($category->getCity()->getID(), $cityIDList)) {
  868.                 $cityIDList[] = $category->getCity()->getID();
  869.             }
  870.         }
  871.         if (empty($cityIDList)) {
  872.             $cityIDList = [City::DEFAULT_CITY_ID];
  873.         }
  874.         $softCache = new SoftCache(self::OFFER_CITIES_CACHE_NAME);
  875.         $softCache->set($offerID, $cityIDList, 0);
  876.         return $cityIDList;
  877.     }
  879.     public function getOfferCity($offerID): ?City
  880.     {
  881.         $offer = $this->find($offerID);
  882.         /** @var Offer $offer */
  883.         $categories = $offer->getCategories();
  885.         if (null !== $offer->getDefaultCategoryID()) {
  886.             $defaultCategoryId = (int) $offer->getDefaultCategoryID();
  887.             foreach ($categories as $category) {
  888.                 /** @var Category $category */
  889.                 if ($category->getID() === $defaultCategoryId) {
  890.                     return $category->getCity();
  891.                 }
  892.             }
  893.         }
  895.         return $offer->getCityByFirstCategory();
  896.     }
  898.     public function isOfferOwner(Offer $offer, User $user) {
  899.         if (!$user->hasRole(UserGroup::ROLE_SUPPLIER_ID)) {
  900.             return false;
  901.         }
  902.         foreach ($offer->getDirectors() as $director) {
  903.             if ($user->getEmail() == $director->getEmail()) {
  904.                 return true;
  905.             }
  906.         }
  907.         return false;
  908.     }
  910.     public function getExtensions(Offer $offer) {
  911.         $dql = 'select extension from Slivki:FoodOfferExtension extension index by extension.partnerItemID where extension.offer = :offer and extension.active = true';
  912.         $query = $this->getEntityManager()->createQuery($dql);
  913.         $query->setParameter('offer', $offer);
  914.         return $query->getResult();
  915.     }
  917.     public function getExtensionsByShippingType(Offer $offer, string $shippingType): array
  918.     {
  919.         $dql = '
  920.                 SELECT 
  921.                     e 
  922.                 FROM Slivki:FoodOfferExtension AS e INDEX BY e.partnerItemID 
  923.                 WHERE e.offer = :offer 
  924.                     AND e.active = true
  925.                     AND e.is' . ucfirst($shippingType) . ' = true
  926.                 ';
  927.         $query = $this->getEntityManager()->createQuery($dql);
  928.         $query->setParameter('offer', $offer);
  929.         return $query->getResult();
  930.     }
  932.     public function getExtensionVariants(OfferExtension $offerExtension) {
  933.         $dql = 'select extensionVariant from Slivki:OfferExtensionVariant extensionVariant index by extensionVariant.partnerID where extensionVariant.offerExtension = :offerExtension';
  934.         $query = $this->getEntityManager()->createQuery($dql);
  935.         $query->setParameter('offerExtension', $offerExtension);
  936.         return $query->getResult();
  937.     }
  939.     public function getDirector($offerID) {
  940.         $dql = 'select director from Slivki:Director director join director.offers offer where offer.ID = :offerID';
  941.         $query = $this->getEntityManager()->createQuery($dql);
  942.         $query->setParameter('offerID', $offerID);
  943.         $result = $query->execute();
  944.         if ($result) {
  945.             return $result[0];
  946.         }
  948.         return false;
  949.     }
  951.     public function getOfferConversion($offerID) {
  952.         $entityManager = $this->getEntityManager();
  953.         $visitRepository = $entityManager->getRepository(Visit::class);
  954.         $purchaseCountRepository = $entityManager->getRepository(PurchaseCount::class);
  956.         $visitCount = $visitRepository->getVisitCount($offerID, Visit::TYPE_OFFER, 30);
  957.         $purchaseCount = $purchaseCountRepository->findOneBy(['entityID' => $offerID]);
  959.         if (!$visitCount || !$purchaseCount || !$purchaseCount->getPurchaseCountLastMonth()) {
  960.             return 0;
  961.         }
  963.         return ceil(100 * ($purchaseCount->getPurchaseCountLastMonth() / $visitCount));
  964.     }
  966.     public function getCityID(int $offerID) : int {
  967.         return $this->getEntityManager()->getConnection()->executeQuery(
  968.             "select coalesce(max(city_id), " . City::DEFAULT_CITY_ID . ") from category"
  969.             . " inner join category2entity on category.id = category2entity.category_id"
  970.             . " where category2entity.entity_id = " . $offerID
  971.         )->fetchColumn();
  972.     }
  974.     private function getHasPurchaseOffersByCityIdQuery(
  975.         int $cityId,
  976.         Period $period
  977.     ) : QueryBuilder {
  978.         $qb = $this->createQueryBuilder('hasPurchaseOffer');
  979.         $expr = $qb->expr();
  981.         return  $qb->innerJoin('hasPurchaseOffer.offerOrders', 'offerOrder')
  982.             ->innerJoin('hasPurchaseOffer.categories', 'hasPurchaseOfferCategories')
  983.             ->andWhere($expr->eq('hasPurchaseOfferCategories.city', ':cityId'))
  984.             ->andWhere($expr->gt('offerOrder.status', ':status'))
  985.             ->andWhere($expr->between('offerOrder.createdOn', ':dateFrom', ':dateTo'))
  986.             ->distinct()
  987.             ->setParameters([
  988.                 'cityId' => $cityId,
  989.                 'status' => OfferOrder::STATUS_INIT,
  990.                 'dateFrom' => $period->getStartDate(),
  991.                 'dateTo' => $period->getEndDate(),
  992.             ]);
  993.     }
  995.     /**
  996.      * @return Offer[]
  997.      */
  998.     public function getHasNotPurchaseOffersByCityId(
  999.         int $cityId,
  1000.         Period $period
  1001.     ) : iterable {
  1002.         $qb = $this->createQueryBuilder('offer');
  1003.         $expr = $qb->expr();
  1004.         $getHasPurchaseOffersByCityIdDql = $this->getHasPurchaseOffersByCityIdQuery($cityId, $period)->getDQL();
  1006.         return  $qb->innerJoin('offer.categories', 'categories')
  1007.             ->andWhere($expr->eq('categories.city', ':cityId'))
  1008.             ->andWhere($expr->eq('offer.active', ':active'))
  1009.             ->andWhere($expr->between(':now', 'offer.activeSince', 'offer.activeTill'))
  1010.             ->andWhere($expr->lt('offer.activeSince', ':dateTo'))
  1011.             ->andWhere(sprintf('offer NOT IN (%s)', $getHasPurchaseOffersByCityIdDql))
  1012.             ->setParameters([
  1013.                 'cityId' => $cityId,
  1014.                 'status' => OfferOrder::STATUS_INIT,
  1015.                 'active' => true,
  1016.                 'dateFrom' => $period->getStartDate(),
  1017.                 'dateTo' => $period->getEndDate(),
  1018.                 'now' => (new \DateTimeImmutable())->format('Y-m-d H:i:s'),
  1019.             ])
  1020.             ->getQuery()
  1021.             ->getResult();
  1022.     }
  1024.     public function getHasNotPurchaseNewOffers(int $managerId, int $limit): iterable
  1025.     {
  1026.         $qb = $this->createQueryBuilder('offer');
  1027.         $expr = $qb->expr();
  1028.         $hasPurchaseOffersQuery = $this->getEntityManager()->createQueryBuilder();
  1030.         $purchasedOffers = $hasPurchaseOffersQuery
  1031.             ->select('purchase.entityID')
  1032.             ->from('Slivki:PurchaseCount', 'purchase')
  1033.             ->andWhere($expr->gt('purchase.purchaseCount', '0'))
  1034.             ->getQuery()
  1035.             ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN);
  1037.         $qb
  1038.             ->andWhere($expr->eq('offer.active', ':active'))
  1039.             ->andWhere($expr->neq('offer.withoutCodes', ':codeless'))
  1040.             ->andWhere($expr->between(':now', 'offer.activeSince', 'offer.activeTill'))
  1041.             ->andWhere(sprintf('offer.ID NOT IN (%s)', implode(',', $purchasedOffers)))
  1042.             ->andWhere($expr->gte('offer.activeSince', ':newOfferDays'))
  1043.             ->setParameters([
  1044.                 'codeless' => true,
  1045.                 'active' => true,
  1046.                 'now' => (new \DateTimeImmutable())->format('Y-m-d H:i:s'),
  1047.                 'newOfferDays' => (new DateTimeImmutable(sprintf('-%d days', PurchasesByOfferDao::NEW_OFFERS_DAYS)))->format('Y-m-d H:i:s'),
  1048.             ]);
  1050.         if ($managerId > 0) {
  1051.             $qb
  1052.                 ->andWhere($expr->eq('offer.manager', ':managerId'))
  1053.                 ->setParameter('managerId', $managerId);
  1054.         }
  1056.         return $qb
  1057.             ->orderBy('offer.title', 'ASC')
  1058.             ->setMaxResults($limit)
  1059.             ->getQuery()
  1060.             ->getResult();
  1061.     }
  1063.     /**
  1064.      * @return Offer[]
  1065.      */
  1066.     public function getActiveOffersByCityId(int $cityId): array
  1067.     {
  1068.         $queryBuilder = $this->createQueryBuilder('offer');
  1069.         $expr = $queryBuilder->expr();
  1071.         return $queryBuilder
  1072.             ->innerJoin('offer.categories', 'category')
  1073.             ->innerJoin('category.city', 'city')
  1074.             ->andWhere($expr->eq('city.ID', ':cityId'))
  1075.             ->andWhere($expr->eq('offer.active', ':active'))
  1076.             ->andWhere($expr->neq('offer.hidden', ':hidden'))
  1077.             ->andWhere($expr->between(':now', 'offer.activeSince', 'offer.activeTill'))
  1078.             ->setParameters([
  1079.                 'cityId' => $cityId,
  1080.                 'active' => true,
  1081.                 'hidden' => true,
  1082.                 'now' => (new \DateTimeImmutable())->format('Y-m-d H:i:s'),
  1083.             ])
  1084.             ->getQuery()
  1085.             ->getResult();
  1086.     }
  1087. }