src/Repository/OfferRepository.php line 782

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