Symfony Profiler

<?php
namespace Slivki\Repository;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;
use League\Period\Period;
use Slivki\Entity\Banner;
use Slivki\Entity\CacheReloadScheduler;
use Slivki\Entity\Category;
use Slivki\Entity\City;
use Slivki\Entity\Director;
use Slivki\Entity\EntityDescription;
use Slivki\Entity\GeoLocation;
use Slivki\Entity\Media;
use Slivki\Entity\Offer;
use Slivki\Entity\OfferExtension;
use Slivki\Entity\OfferPayedCategory;
use Slivki\Entity\PurchaseCount;
use Slivki\Entity\OfferOrder;
use Slivki\Entity\Sale;
use Slivki\Entity\Seo;
use Slivki\Entity\SiteSettings;
use Slivki\Entity\UserGroup;
use Slivki\Entity\Visit;
use Slivki\Exception\OfferNotFoundException;
use Slivki\Services\ImageService;
use Slivki\Util\Logger;
use Slivki\Util\SoftCache;
use Slivki\Entity\User;
use Slivki\Entity\EntityOption;
use Symfony\Component\Config\Definition\Exception\Exception;
class OfferRepository extends EntityRepository {
const CACHE_NAME = "offers";
const CACHE_NAME_TEASERS = 'offers-teasers';
const CACHE_NAME_TEASERS_MOBILE = 'teasers-mobile';
const LOCKED_KEY = "lock";
const SORTED_OFFERS_CACHE_KEY = "sorted-offers-list-";
const SORTED_OFFERS_CACHE_NAME = "sortedOffersByPosition";
const SORT_MODE_DEFAULT = 'po-promokodam';
const SORT_MODE_BY_VISIT = 'po-prosmotram';
const SORT_MODE_BY_RATING = 'po-ocenkam';
const COMPANIES_RATING_DATA_CACHE_KEY = 'company-rating-data-';
const MANAGER_WORST_RATING_CACHE_KEY = 'manager-worst-rating-';
const DIRECTOR_WORST_RATING_CACHE_KEY = 'director-worst-rating-';
const OFFER_POSITIONS_CACHE_KEY = 'positions';
const OFFER_LOCATIONS_CACHE_KEY = 'locations';
const FILTER_DATA_CACHE_KEY = 'filterData';
const NEW_SUBCATEGORY_CACHE_KEY = '-new';
const RESIZED_IMAGE_PREFIX = '_resized_';
const CACHE_NAME_GEO_LOCATION_DATA = 'offer-geolocation-data';
const OFFER_CITIES_CACHE_NAME = 'offer-cities';
private static $purchaseCountListByCategory;
private static $purchaseCountList;
private static $allOffersList;
private static $offerRatingList;
private static $offersOnlyPayedMonthlyPurchaseCount;
/**
* @throws OfferNotFoundException
*/
public function getById(int $offerId): Offer
{
$queryBuilder = $this->createQueryBuilder('offer');
$expr = $queryBuilder->expr();
$offer = $queryBuilder
->andWhere($expr->eq('offer.ID', ':offerId'))
->setParameter('offerId', $offerId)
->getQuery()
->getOneOrNullResult();
if (!$offer instanceof Offer) {
throw OfferNotFoundException::missingId($offerId);
}
return $offer;
}
public function getActiveOffersByCategoryID($categoryID) {
ini_set('memory_limit', '4g');
$softCache = new SoftCache(self::CACHE_NAME);
$category = $this->getEntityManager()->getRepository(Category::class)->findCached($categoryID);
$offerList = [];
if(isset($category['entityList'])) {
$offerList = $softCache->getMulti($category['entityList']);
}
if (!$offerList) {
return [];
}
$offerListCached = [];
foreach ($offerList as $key => $offer) {
if ($offer) {
$offerListCached[$key] = $offer;
}
}
return $offerListCached;
}
public function getAllActiveOffersCached() {
$entityList = [];
$categoryList = $this->getEntityManager()->getRepository(Category::class)->getAllActiveCategoriesFromCache();
foreach ($categoryList as $category) {
if ($category['category']->getDomainObjectID() == Category::OFFER_CATEGORY_ID) {
if (isset($category['entityList']) && is_array($category['entityList'])) {
$entityList = array_merge($entityList, $category['entityList']);
} else {
Logger::instance('getAllActiveOffersCached')->info('Category ' . $category['category']->getID() . ' entityList not array');
}
}
}
$entityList = array_unique($entityList);
$softCache = new SoftCache(self::CACHE_NAME);
$offerList = $softCache->getMulti($entityList);
return $offerList;
}
public function setAllOffersList() {
self::$allOffersList = $this->getAllActiveOffersNoCache();
}
public function getAllActiveOffersNoCache(){
$dql = "select offer from Slivki:Offer offer
where offer.active = true
and offer.activeSince < CURRENT_TIMESTAMP()
and offer.activeTill > CURRENT_TIMESTAMP()
order by offer.active desc";
$offers = $this->getEntityManager()->createQuery($dql)->getResult();
return $offers;
}
public function getActiveOffersByCategoryIDNoCache($categoryID) {
$dql = "select offer from Slivki:Offer offer
join offer.categories category
where category.ID = :categoryID
and offer.active = true
and offer.activeSince < CURRENT_TIMESTAMP()
and offer.activeTill > CURRENT_TIMESTAMP()
order by offer.active desc";
$offerList = $this->getEntityManager()->createQuery($dql)->setParameter("categoryID", $categoryID)->getResult();
return $offerList;
}
public function getAdvOfferList() {
return $this->getAllActiveOffersNoCache();
}
public function getAllOffersByCategoryID($categoryID) {
$dql = "select offer from Slivki:Offer offer join offer.categories category where category.ID = :categoryID order by offer.active desc";
$offerList = $this->getEntityManager()->createQuery($dql)->setParameter("categoryID", $categoryID)->getResult();
return $offerList;
}
public function getCountActiveOffersByCategory(Category $category) {
$offerList = $this->getActiveOffersByCategoryID($category->getID());
if (!$offerList) {
return 0;
}
return count($offerList);
}
/**
* @return boolean
*/
public function isOfferFreeForUser(Offer $offer, User $user = null) {
if (!$offer->isInFreeCodesCategory()) {
return false;
}
if ($offer->isFree() || !$user || $user->getID() == User::FAKE_USER_ID) {
return true;
}
$orderStatus = OfferOrder::STATUS_CONFIRM_FREE_IMPLICITLY;
$dql = "select count(orderDetails) as amount from Slivki:OfferOrder offerOrder join offerOrder.offerOrderDetails orderDetails
where offerOrder.userID = :userID and offerOrder.offerID = :offerID and offerOrder.status = :status and offerOrder.createdOn between :from and :to";
$query = $this->getEntityManager()->createQuery($dql);
$query->setParameter('userID', $user->getID());
$query->setParameter('offerID', $offer->getID());
$query->setParameter('status', $orderStatus);
$query->setParameter("from", date_format(new \DateTime(), 'Y-m-d 00:00:00'));
$query->setParameter("to", date_format(new \DateTime(), 'Y-m-d 23:59:59'));
$result = $query->getOneOrNullResult(Query::HYDRATE_SCALAR);
if ($offer->getID() == Offer::PETROL_OFFER_ID) {
return $result['amount'] < 2;
} else {
return $result['amount'] == 0;
}
}
/**
* @return \Slivki\Entity\Offer
* @deprecated
*/
public function findCached($offerID) {
$softCache = new SoftCache(self::CACHE_NAME);
return $softCache->get($offerID);
}
/**
* @return \Slivki\Entity\Offer|null
* @deprecated
*/
public function getAnyWay($offerID) {
$offer = $this->findCached($offerID);
if (!$offer) {
$offer = $this->find($offerID);
}
return $offer;
}
public function getUsedCodesCount($offerID) {
$dql = "select count(details) from Slivki:OfferOrderDetails details where details.offerOrderID=:offerID";
$query = $this->getEntityManager()->createQuery($dql)->setParameter("offerID", $offerID);
$usedCodesCount = $query->getSingleScalarResult();
return $usedCodesCount;
}
public function getOfferMonthlyPurchaseCount($offerID, $days = 30) {
if ($days == 30) {
$sql = "select purchase_count_last_month_with_correction from purchase_count where entity_id = " . $offerID;
return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn();
}
$sql = "select count(*) from offer_code inner join offer_order on offer_code.offer_order_id = offer_order.id
where offer_order.offer_id = $offerID and offer_code.created_on > (now() + '- $days days')";
return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn();
}
public function getOfferList($ids) {
$softCache = new SoftCache(self::CACHE_NAME);
$offerList = $softCache->getMulti($ids);
if (!$offerList) {
return [];
}
return $offerList;
}
public function getCodeCost(Offer $offer = null, $codesCount = null) {
if ($offer && $offer->getID() == 283793) {
return 0;
}
$siteSettingsRepository = $this->getEntityManager()->getRepository(SiteSettings::class);
if (!$offer) {
return $siteSettingsRepository->getSiteSettingsCached()->getCodeCost();
}
if ($offer->getCodeCostByCount()) {
$codeCost = null;
foreach ($offer->getCodeCostByCount() as $count => $cost) {
if ($count > $codesCount) {
break;
}
$codeCost = $cost;
}
return $codeCost;
}
if ($offer->getCodeCost() !== null) {
return $offer->getCodeCost();
}
return $siteSettingsRepository->getSiteSettingsCached()->getCodeCost();
}
public function getOfferExtensionCodeCost(OfferExtension $extension) {
if ($extension->getCodePrice() != null) {
return $extension->getCodePrice();
}
return $this->getCodeCost($extension->getOffer());
}
/** @deprecated */
public function putOfferToCache(Offer $offer) {
$softCache = new SoftCache(self::CACHE_NAME);
$cacheKey = $offer->getID() . self::LOCKED_KEY;
while (!$softCache->add($cacheKey, self::LOCKED_KEY, 60));
$softCache->set($offer->getID(), $offer, 0);
$softCache->set($cacheKey, null, 1);
}
public function reloadCache() {
$dql = "select offer, descriptions, offerCodePools, geoLocations, category from Slivki:Offer offer index by offer.ID inner join offer.categories category
left join offer.descriptions as descriptions left join offer.offerCodePools offerCodePools left join offer.geoLocations as geoLocations
where category.active = true and offer.active = true and CURRENT_TIMESTAMP() between offer.activeSince and offer.activeTill
and offer.hidden = false";
$offerList = $query = $this->getEntityManager()->createQuery($dql)->getResult();
$offerListByCategory = [];
$tagList = [];
$offerListCached = [];
$cityList = [];
$mediaRepository = $this->getEntityManager()->getRepository(Media::class);
foreach ($offerList as $offer) {
$this->loadOfferData($offer, $mediaRepository, $tagList, $offerListByCategory);
$offerListCached[$offer->getID()] = $offer;
}
$categoryListCached = [];
$categoryList = $this->getEntityManager()->getRepository(Category::class)->getActiveCategoriesNotCached(Category::OFFER_CATEGORY_ID);
foreach ($categoryList as $category) {
if (!isset($offerListByCategory[$category->getID()])) {
continue;
}
$offerList = $offerListByCategory[$category->getID()];
if (count($offerList) == 0) {
continue;
}
$category->getParentCategories()->toArray();
$category->setEntityCount(count($offerList));
$category->setHotFeedIconMedia($mediaRepository->getСategoryHotFeedIconMedia($category->getID()));
$category->getCity()->getID();
$city = $category->getCity();
if (!isset($cityList[$city->getID()])) {
$cityList[$city->getID()] = $city;
}
$categoryListCached[$category->getID()] = ['category' => $category, 'entityList' => $offerList];
}
$this->getEntityManager()->clear();
$softCache = new SoftCache(self::CACHE_NAME);
$softCache->setMulti($offerListCached, 0);
$softCache = new SoftCache(CategoryRepository::CACHE_NAME);
$softCache->set(CategoryRepository::ALL_CATEGORIES_CACHE_KEY, $categoryListCached, 0);
$softCache->setMulti($categoryListCached, 0);
$softCache = new SoftCache(CityRepository::CACHE_NAME);
$softCache->set(CityRepository::CACHE_KEY_ACTIVE, $cityList, 0);
}
/** @deprecated */
public function reloadCacheForOneOffer($offerID, $source = '') {
$offerOrderRepository = $this->getEntityManager()->getRepository(OfferOrder::class);
$logger = Logger::instance('reloadCacheForOneOffer');
try {
$logger->info('Lock code pool for offer ' . $offerID);
$offerOrderRepository->lockCodePool($offerID);
/** @var \Slivki\Entity\Offer $offer */
$offer = $this->find($offerID);
if (!$offer->isActive() || !$offer->isInActivePeriod() || $offer->isHidden()) {
$this->removeOfferFromCache($offerID);
$logger->info('Unlock code pool for offer ' . $offerID . '. Offer is not active');
$offerOrderRepository->unlockCodePool($offerID);
return;
}
if ($offer->getDescriptionByType(EntityDescription::TYPE_OFFER_CONDITIONS_ID) == '') {
Logger::instance('DESCRIPTION-DEBUG')->info("$offerID empty description. $source");
}
$mediaRepository = $this->getEntityManager()->getRepository(Media::class);
$tagList = [];
$offerListByCategory = [];
$this->loadOfferData($offer, $mediaRepository, $tagList, $offerListByCategory);
foreach ($offer->getGeoLocations() as $geoLocation) {
$geoLocation->getPhoneNumbers()->toArray();
}
$softCache = new SoftCache(self::CACHE_NAME);
$softCache->set($offerID, $offer, 0);
$logger->info('Lock code pool for offer ' . $offerID);
$offerOrderRepository->unlockCodePool($offerID);
$categoryList = $this->getEntityManager()->getRepository(Category::class)->getAllActiveCategoriesFromCache();
$categoryListChanged = false;
$categoryRepository = $this->getEntityManager()->getRepository(Category::class);
$cityList = $this->getEntityManager()->getRepository(City::class)->getActiveCitiesCached();
foreach ($offer->getCategories() as $category) {
if (!isset($categoryList[$category->getID()])) {
$category->getBanners()->toArray();
$category->setEntityCount(1);
$categoryForCache['category'] = $category;
$categoryForCache['entityList'][] = $offerID;
$categoryList[$category->getID()] = $categoryForCache;
$categoryListChanged = true;
$categoryRepository->putCategoryToCache($categoryForCache);
}
$city = $category->getCity();
if ($city && !isset($cityList[$city->getID()])) {
$cityList[$city->getID()] = $city;
}
}
foreach ($offerListByCategory as $categoryID => $category) {
if (!isset($categoryList[$categoryID]['entityList'])) {
$categoryList[$categoryID]['entityList'] = [];
}
if (!in_array($offerID, $categoryList[$categoryID]['entityList'])) {
$categoryList[$categoryID]['entityList'][] = $offerID;
$categoryList[$categoryID]['category']->setEntityCount(count($categoryList[$categoryID]['entityList']));
$categoryListChanged = true;
$categoryRepository->putCategoryToCache($categoryList[$categoryID]);
}
}
foreach ($categoryList as $categoryID => $category) {
if (!isset($category['entityList']) || !$category['entityList']) {
continue;
}
$index = array_search($offerID, $category['entityList']);
if (false !== $index && !isset($offerListByCategory[$categoryID])) {
unset($categoryList[$categoryID]['entityList'][$index]);
$categoryList[$categoryID]['category']->setEntityCount(count($categoryList[$categoryID]['entityList']));
$categoryListChanged = true;
$categoryRepository->putCategoryToCache($categoryList[$categoryID]);
}
}
if ($categoryListChanged) {
$categoryRepository->cacheAllActiveCategories($categoryList);
}
$softCache = new SoftCache(CityRepository::CACHE_NAME);
$softCache->set(CityRepository::CACHE_KEY_ACTIVE, $cityList, 0);
} catch (Exception $e) {
$logger->info('Unlock code pool for offer ' . $offerID . '. ' . $e->getMessage());
$offerOrderRepository->unlockCodePool($offerID);
}
return $offer;
}
private function loadOfferData(Offer &$offer, MediaRepository &$mediaRepository, &$tagList, &$offerListByCategory) {
$offerID = $offer->getID();
if ($offer->getTeaserMedia()) {
$offer->getTeaserMedia()->getID();
}
$offer->setHotFeedIconMedia($mediaRepository->getOfferHotFeedIconMedia($offerID));
$offer->setDetailMeidas($mediaRepository->getOfferDetailMedias($offerID));
$offer->setShopMedias($mediaRepository->getOfferShopMedias($offerID));
$offer->getPhoneNumbers()->toArray();
$offer->getGiftCertificates()->toArray();
$offer->getExtensions()->toArray();
$offer->getOfferCodePools()->toArray();
$offer->getGeoLocations()->toArray();
$offer->getPhoneNumbers()->toArray();
$offer->getExtensions()->toArray();
$offer->getGiftCertificates()->toArray();
$offer->getSupplierPhotoMedias()->toArray();
foreach ($offer->getCategories() as $category) {
$offerListByCategory[$category->getID()][] = $offer->getID();
}
if($offer->isActiveCurrencyCalculator()) {
$offer->getBankCurrency()->getRate();
}
foreach ($offer->getDescriptions()->toArray() as $description) {
$description->getEntityDescriptionSliderImages()->toArray();
}
}
public function removeOfferFromCache($offerID) {
$softCache = new SoftCache(self::CACHE_NAME);
$offer = $softCache->get($offerID);
if (!$offer) {
return;
}
$softCache->delete($offerID);
$categoryRepository = $this->getEntityManager()->getRepository(Category::class);
$offersByCategory = $categoryRepository->getAllActiveCategoriesFromCache();
$arrayChanged = false;
foreach ($offersByCategory as $key => $category) {
if ($category['category']->getDomainObjectID() == Category::OFFER_CATEGORY_ID) {
if (!isset($category['entityList']) || !is_array($category['entityList'])) {
continue;
}
$entityCount = count($category['entityList']);
$offersByCategory[$key]['entityList'] = array_diff($category['entityList'], [$offerID]);
if (count($offersByCategory[$key]['entityList']) != $entityCount) {
$arrayChanged = true;
if (count($offersByCategory[$key]['entityList']) == 0) {
unset($offersByCategory[$key]);
}
}
}
}
if ($arrayChanged) {
$categoryRepository->cacheAllActiveCategories($offersByCategory);
}
}
public function getWorstManagerRating() {
$softCache = new SoftCache(self::CACHE_NAME);
return $softCache->get(self::MANAGER_WORST_RATING_CACHE_KEY, '');
}
public function getWorstDirectorsRating() {
$softCache = new SoftCache(self::CACHE_NAME);
return $softCache->get(self::DIRECTOR_WORST_RATING_CACHE_KEY, '');
}
public function getWorstManagerRatingData() {
$managerList = $this->getEntityManager()->getRepository(UserGroup::class)->findOneByName('MANAGER')->getUsers();
$result = [];
foreach ($managerList as $manager) {
$offerList = $manager->getOffers();
$offerCount = count($offerList);
if ($offerCount == 0) {
continue;
}
$worstOfferCount = 0;
/** @var Offer $offer */
foreach ($offerList as $offer) {
if ($offer->hasCategoryType(Category::WORST_CATEGORY_TYPE)) {
$worstOfferCount++;
}
}
$result[] = [
'managerID' => $manager->getID(),
'managerName' => $manager->getManagerName(),
'offerCount' => $offerCount,
'worstOfferCount' => $worstOfferCount,
'percentage' => 100 * $worstOfferCount/$offerCount
];
}
usort($result, function ($manager, $manager1) {
return $manager['percentage'] >= $manager1['percentage'] ? -1 : 1;
});
return $result;
}
public function getOffersOnlyPayedMonthlyPurchaseCount($offerID) {
if (!self::$offersOnlyPayedMonthlyPurchaseCount) {
$dbConnection = $this->getEntityManager()->getConnection();
$payedStatuses = [
OfferOrder::STATUS_CONFIRM,
OfferOrder::STATUS_EXTENSION_ORDER_CONFIRM,
OfferOrder::STATUS_HALF_CONFIRM,
OfferOrder::STATUS_BALANCE_CONFIRM
];
$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
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";
$offersOnlyPayedMonthlyPurchaseCount = $dbConnection->executeQuery($sql)->fetchAll();
foreach ($offersOnlyPayedMonthlyPurchaseCount as $item) {
self::$offersOnlyPayedMonthlyPurchaseCount[$item['offer_id']] = $item['amount'];
}
}
return isset(self::$offersOnlyPayedMonthlyPurchaseCount[$offerID]) ? self::$offersOnlyPayedMonthlyPurchaseCount[$offerID] : 0;
}
private function getPurchaseByCategory($categoryID, $dateFrom, $dateTo) {
$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
inner join category2entity on offer_code_pool.offer_id = category2entity.entity_id where category2entity.category_id = $categoryID
and offer_order_details.created_on between '$dateFrom' and '$dateTo'";
$result = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetch(\PDO::FETCH_COLUMN);
return (int)$result;
}
private function compareBannersByPosition(Banner $banner, Banner $banner1) {
if ($banner->getPositionRow() < $banner1->getPositionRow()) {
return -1;
}
if ($banner->getPositionRow() == $banner1->getPositionRow()) {
return $banner->getPositionColumn() > $banner1->getPositionColumn() ? 1 : -1;
}
return 1;
}
public function getSortedOffersByPosition($categoryID) {
$dql = "select position.entityID from Slivki:OfferCategoryPosition as position where position.categoryID = :categoryID order by position.position asc";
$offersListSorted = $this->getEntityManager()->createQuery($dql)->setParameter('categoryID', $categoryID)->getResult();
return $offersListSorted;
}
public function getOffersSortedByVisitCount($categoryID, $daysCount) {
$sql = "select category2entity.entity_id as \"entityID\" from category2entity left join
(select entity_id, count(*) as amount from visit where entity_type_id = " . Category::OFFER_CATEGORY_ID . " and created_on > now() + '-$daysCount days' group by 1) as visit_count
on category2entity.entity_id = visit_count.entity_id where category_id = $categoryID order by coalesce(visit_count.amount, 0) desc";
return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchAll();
}
public function getOffersSortedByRating($categoryID) {
$sql = "select category2entity.entity_id as \"entityID\", round(coalesce(rating.amount, 0), 1) from category2entity left join
(select entity_id, avg(rating) as amount from comment where type_id = " . Category::OFFER_CATEGORY_ID . " and rating > 0 and checked and not hidden group by 1) as rating
on category2entity.entity_id = rating.entity_id where category_id = $categoryID order by round(coalesce(rating.amount, 0), 1) desc";
return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchAll();
}
public function getTeaserBanners(Category $category, $teaserInARowCount = 3 ) {
$category = $this->getEntityManager()->merge($category);
$teaserBanners = $category->getActiveBannersByType(Banner::TYPE_TEASER_BANNER);
$teaserVerticalBanners = $category->getActiveBannersByType(Banner::TYPE_TEASER_VERTICAL_BANNER);
$teaserBanners = array_merge($teaserBanners, $teaserVerticalBanners);
$teaserBannersPosition = array();
$withVerticalBannersRowList = array();
foreach ($teaserBanners as $banner) {
$position = ($banner->getPositionRow() - 1) * 3 + $banner->getPositionColumn();
if($banner->isActive()) {
$teaserBannersPosition[$position] = $banner;
if($banner->getTypeID() == Banner::TYPE_TEASER_VERTICAL_BANNER) {
$withVerticalBannersRowList[] = (int)ceil($position / $teaserInARowCount);
}
};
}
return array(
'teaserBanners' => $teaserBannersPosition,
'withVerticalBannersRowList' => $withVerticalBannersRowList
);
}
public function getOffersByCategoryWithPositions($categoryID, $limit = null) {
$dql = "select offer, offerCategoryPosition.position from Slivki:Offer offer
left join Slivki:OfferCategoryPosition offerCategoryPosition with offerCategoryPosition.entityID = offer.ID and offerCategoryPosition.categoryID = :categoryID
inner join offer.categories category where category.ID = :categoryID
order by offerCategoryPosition.position";
$query = $this->getEntityManager()->createQuery($dql);
$query->setParameter('categoryID', $categoryID);
if ($limit) {
$query->setMaxResults((int)$limit);
}
return $query->getResult();
}
public function getActiveOffersByCategoryWithoutTags($domainObjectID, $cityID) {
$dql = "select offer.* from offer
inner join category2entity on offer.id = category2entity.entity_id
inner join category on category2entity.category_id = category.id
inner join entity_option on offer.id = entity_option.entity_id
where city_id = $cityID and category.domain_object_id = $domainObjectID and offer.active and entity_option.name = '".EntityOption::OPTION_OFFERS_WITHOUT_TAGS."'
and offer.active_till > now()
group by offer.id";
return $this->getEntityManager()->getConnection()->executeQuery($dql)->fetchAll(Query::HYDRATE_ARRAY);
}
public function getActiveOffersByCategoryWithoutTagsAllCity() {
$dql = "select offer.* from offer
inner join category2entity on offer.id = category2entity.entity_id
inner join category on category2entity.category_id = category.id
inner join entity_option on offer.id = entity_option.entity_id
where offer.active and entity_option.name = '".EntityOption::OPTION_OFFERS_WITHOUT_TAGS."'
and offer.active_till > now()
group by offer.id";
return $this->getEntityManager()->getConnection()->executeQuery($dql)->fetchAll(Query::HYDRATE_ARRAY);
}
public function getOffersOnReview($domainObjectID, $cityID) {
$dql = "select offer.* from offer
inner join category2entity on offer.id = category2entity.entity_id
inner join category on category2entity.category_id = category.id
where city_id = $cityID and category.domain_object_id = $domainObjectID and offer.review
and offer.on_review group by offer.id";
return $this->getEntityManager()->getConnection()->executeQuery($dql)->fetchAll(Query::HYDRATE_ARRAY);
}
public function getOffersByCategorySortedByPopularity(Category $category) {
$categoryID = $category->getID();
$offerList = $this->getActiveOffersByCategoryIDNoCache($categoryID);
if (!$offerList) {
return false;
}
if ($categoryID != Category::NEW_OFFER_CATEGORY_ID) {
$dql = "select purchaseCount from Slivki:PurchaseCount purchaseCount index by purchaseCount.entityID
inner join Slivki:Offer offer with purchaseCount.entityID = offer.ID
inner join offer.categories category where category.ID = :categoryID";
$query = $this->getEntityManager()->createQuery($dql);
$query->setParameter('categoryID', $categoryID);
$query->execute();
self::$purchaseCountListByCategory = $query->getResult();
if (self::$purchaseCountListByCategory) {
usort($offerList, ['\Slivki\Repository\OfferRepository', 'compareOffersByPurchaseCount']);
self::$purchaseCountListByCategory = null;
}
} else {
usort($offerList, ['\Slivki\Repository\OfferRepository', 'compareOffersByRenewedOn']);
}
$payedPositions = $this->getEntityManager()->getRepository(OfferPayedCategory::class)->findBy(
['categoryID' => $categoryID], ['position' => 'asc']);
if ($payedPositions) {
foreach ($payedPositions as $position) {
foreach ($offerList as $key => $offer) {
if ($offer->getID() == $position->getEntityID()) {
unset($offerList[$key]);
array_splice($offerList, $position->getPosition() - 1, 0, [$offer]);
break;
}
}
}
}
return $offerList;
}
private static function compareOffersByID(Offer $offer, Offer $offer1) {
return $offer->getID() > $offer1->getID() ? -1 : 1;
}
private static function compareOffersByRenewedOn(Offer $offer, Offer $offer1) {
if ($offer->getRenewedOn() == $offer1->getRenewedOn()) {
return $offer->getID() > $offer1->getID() ? 1 : -1;
}
return $offer->getRenewedOn() > $offer1->getRenewedOn() ? -1 : 1;
}
private static function compareOffersByPurchaseCount(Offer $offer, Offer $offer1) {
/** @var \Slivki\Entity\PurchaseCount $purchaseCount */
/** @var \Slivki\Entity\PurchaseCount $purchaseCount1 */
$purchaseCount = isset(self::$purchaseCountListByCategory[$offer->getID()]) ? self::$purchaseCountListByCategory[$offer->getID()] : new PurchaseCount();
$purchaseCount1 = isset(self::$purchaseCountListByCategory[$offer1->getID()]) ? self::$purchaseCountListByCategory[$offer1->getID()] : new PurchaseCount();
$purchaseCountKoeff = $offer->getPurchaseKoeff() ?: ($offer->isInFreeCodesCategory() ? 3 : 1);
$purchaseCountKoeff1 = $offer1->getPurchaseKoeff() ?: ($offer->isInFreeCodesCategory() ? 3 : 1);
if ($purchaseCount->getPurchaseCountRecent()/$purchaseCountKoeff == $purchaseCount1->getPurchaseCountRecent()/$purchaseCountKoeff1) {
if ($purchaseCount->getPurchaseCountLastMonth()/$purchaseCountKoeff == $purchaseCount1->getPurchaseCountLastMonth()/$purchaseCountKoeff1) {
if ($purchaseCount->getPurchaseCount()/$purchaseCountKoeff == $purchaseCount1->getPurchaseCount()/$purchaseCountKoeff1) {
return $offer->getID() > $offer1->getID() ? 1 : -1;
}
return $purchaseCount->getPurchaseCount()/$purchaseCountKoeff > $purchaseCount1->getPurchaseCount()/$purchaseCountKoeff1 ? -1 : 1;
} else {
return $purchaseCount->getPurchaseCountLastMonth()/$purchaseCountKoeff > $purchaseCount1->getPurchaseCountLastMonth()/$purchaseCountKoeff1 ? -1 : 1;
}
} else {
return $purchaseCount->getPurchaseCountRecent()/$purchaseCountKoeff > $purchaseCount1->getPurchaseCountRecent()/$purchaseCountKoeff1 ? -1 : 1;
}
}
public function findPastOfferCached($offerID) {
$softCache = new SoftCache(self::CACHE_NAME);
$cacheKey = 'pastOffer-0-' . $offerID;
$offer = $softCache->get($cacheKey);
if ($offer) {
return $offer;
}
$offer = $this->find($offerID);
if (!$offer) {
return null;
}
$offer->getOfferCodePools()->toArray();
$offer->getGeoLocations()->toArray();
$softCache->set($cacheKey, $offer, 0);
return $offer;
}
public function getVisitCount($offer, $today = false) {
if ($today) {
$dateFrom = new \DateTime('-1 days');
} else {
$dateFrom = new \DateTime('-30 days');
}
$dql = "select count(visit.ID) from Slivki:Visit visit
where visit.entityID = :offerID and visit.entityTypeID = :entityTypeID and visit.createdOn > :dateFrom and visit.createdOn > :offerActiveSince";
return $this->getEntityManager()->createQuery($dql)
->setParameter('dateFrom', $dateFrom)
->setParameter('offerID', $offer->getID())
->setParameter('offerActiveSince', $offer->getActiveSince())
->setParameter('entityTypeID', Category::OFFER_CATEGORY_ID)
->getSingleScalarResult();
}
public function getLastPurchaseTime($offerID) {
$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
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";
$lastPurchaseTime = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn();
if ($lastPurchaseTime) {
$lastPurchaseTime = \DateTime::createFromFormat('Y-m-d H:i:s', $lastPurchaseTime);
}
return $lastPurchaseTime;
}
public function getRecentPurchaseCount($offerID) {
$purchaseData = $this->getEntityManager()->getRepository(PurchaseCount::class)->findOneByEntityID($offerID);
if (!$purchaseData) {
return 0;
}
return $purchaseData->getPurchaseCountLastDay();
}
public function getTeaserWatermark($offerID) {
$offer = $this->find($offerID);
if (!$offer) {
return [];
}
$mediaRepository = $this->getEntityManager()->getRepository(Media::class);
$watermark = $mediaRepository->getOfferTeaserLogo($offerID);
if ($watermark) {
return [
'watermark' => $watermark,
'width' => $offer->getTeaserLogoWidth(),
'height' => $offer->getTeaserLogoHeight()
];
}
$directors = $offer->getDirectors();
/** @var Director $director */
foreach ($directors as $director) {
$watermark = $mediaRepository->getDirectorLogo($director->getID());
if ($watermark) {
return [
'watermark' => $watermark,
'width' => $director->getTeaserLogoWidth(),
'height' => $director->getTeaserLogoHeight()
];
}
}
return [];
}
public function scheduleOfferReload($offerID) {
$scheduler = new CacheReloadScheduler();
$scheduler->setType(CacheReloadScheduler::TYPE_OFFER);
$scheduler->setEntityID($offerID);
$entityManager = $this->getEntityManager();
$entityManager->persist($scheduler);
$entityManager->flush($scheduler);
}
public function getActiveOffersCount($cityID = City::DEFAULT_CITY_ID) {
$sql = "select count(distinct offer.id) from offer inner join category2entity on offer.id = category2entity.entity_id
inner join category on category2entity.category_id = category.id
where offer.active and category.city_id = " . (int)$cityID . " and now() between active_since and active_till";
return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn(0);
}
public function getActiveOffersCountCached($cityID = City::DEFAULT_CITY_ID) {
$softCache = new SoftCache(self::CACHE_NAME);
$cacheKey = 'activeCount-' . $cityID;
$offersCount = $softCache->get($cacheKey);
if (!$offersCount) {
$offersCount = $this->getActiveOffersCount($cityID);
$softCache->set($cacheKey, $offersCount, 30 * 60);
}
return $offersCount;
}
public function getOfferGeoLocations(Offer $offer) {
$seoRepository = $this->getEntityManager()->getRepository(Seo::class);
$result = [];
/** @var GeoLocation $geoLocation */
foreach($offer->getGeoLocations() as $geoLocation) {
$geoLocationStreet = $geoLocation->getStreet() ? $geoLocation->getStreet() . ', ' : '';
$geoLocationHouse = $geoLocation->getHouse() ? $geoLocation->getHouse() : '';
$geoLocationDescription = $geoLocation->getDescription() ? $geoLocation->getDescription() . ' - ' : '';
$geoLocationInfos['markerAnnotation'] = $geoLocationDescription . $geoLocationStreet . $geoLocationHouse;
$geoLocationInfos['latitude'] = $geoLocation->getLatitude();
$geoLocationInfos['longitude'] = $geoLocation->getLongitude();
$geoLocationInfos['offerURI'] = $seoRepository->getOfferURL($offer->getID())->getMainAlias();
$result[]['geoLocationInfos'][] = $geoLocationInfos;
}
return $result;
}
public function getOfferGeoLocationData(Offer $offer, $userGeoLocation, ImageService $imageService, $jsonEncode = true, $baseURL = '') {
$seoRepository = $this->getEntityManager()->getRepository(Seo::class);
$features = [];
$url = '';
$seo = $seoRepository->getByEntity(SeoRepository::RESOURCE_URL_OFFER_DETAILS, $offer->getID());
if ($seo) {
$url = $seo->getMainAlias();
}
$closestLocationID = 0;
$offerGeoLocations = $offer->getGeoLocations();
foreach ($offerGeoLocations as $geoLocation) {
$caption = $geoLocation->getStreet() . ', ' . $geoLocation->getHouse();
$teaserURL = '';
if ($offer->getTeaserMedia()) {
$teaserURL = $baseURL . $imageService->getImageURL($offer->getTeaserMedia(), 0, 0);
}
$features[] = [
'type' => 'Feature',
'id' => $geoLocation->getID(),
'geometry' => [
'type' => 'Point',
'coordinates' => [$geoLocation->getLatitude(), $geoLocation->getLongitude()]
],
'properties' => [
'iconClass' => 'always-caption',
'iconContent' => '',
'locationID' => $geoLocation->getID(),
'offerID' => $offer->getID(),
'offerTitle' => $offer->getTitle(),
'teaserURL' => $teaserURL,
'offerType' => $offer->getOfferType(),
'url' => $url,
'hintContent' => $caption
]
];
}
$getLocationData = ['type' => 'FeatureCollection', 'features' => $features];
if ($jsonEncode) {
$data = json_encode($getLocationData);
} else {
$data = $getLocationData;
}
return $data;
}
public function getOfferIDListByCategory($categoryID) {
$sql = "select category2entity.entity_id from category2entity inner join offer on category2entity.entity_id = offer.id
where category2entity.category_id = " . (int)$categoryID;
return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchAll(\PDO::FETCH_COLUMN);
}
public function getLastPurchaseDate($entityID, $userID) {
$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
where offer_order.offer_id = $entityID and offer_order.user_id = $userID order by offer_order_details.id desc limit 1";
$lastPurchaseDate = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn();
return \DateTime::createFromFormat('Y-m-d', $lastPurchaseDate);
}
public function getOfferCityIDList(Offer $offer) {
$softCache = new SoftCache(self::OFFER_CITIES_CACHE_NAME);
$cityIDList = $softCache->get($offer->getID());
if (!$cityIDList) {
$cityIDList = $this->reloadOfferCityIDListCache($offer->getID());
}
return $cityIDList;
}
public function reloadOfferCityIDListCache($offerID) {
/** @var Offer $offer */
$offer = $this->find($offerID);
if (!$offer) {
return false;
}
$cityIDList = [];
/** @var Category $category */
foreach ($offer->getCategories() as $category) {
$categoryID = $category->getID();
if ($categoryID == Category::COUNTRY_CATEGORY_ID || $category->isChildOfRecursive(Category::COUNTRY_CATEGORY_ID)
|| $categoryID == Category::NEW_OFFER_CATEGORY_ID || $category->isChildOfRecursive(Category::NEW_OFFER_CATEGORY_ID)) {
continue;
}
if ($category->getCity() && !in_array($category->getCity()->getID(), $cityIDList)) {
$cityIDList[] = $category->getCity()->getID();
}
}
if (empty($cityIDList)) {
$cityIDList = [City::DEFAULT_CITY_ID];
}
$softCache = new SoftCache(self::OFFER_CITIES_CACHE_NAME);
$softCache->set($offerID, $cityIDList, 0);
return $cityIDList;
}
public function getOfferCity($offerID): ?City
{
$offer = $this->find($offerID);
/** @var Offer $offer */
$categories = $offer->getCategories();
if (null !== $offer->getDefaultCategoryID()) {
$defaultCategoryId = (int) $offer->getDefaultCategoryID();
foreach ($categories as $category) {
/** @var Category $category */
if ($category->getID() === $defaultCategoryId) {
return $category->getCity();
}
}
}
return $offer->getCityByFirstCategory();
}
public function isOfferOwner(Offer $offer, User $user) {
if (!$user->hasRole(UserGroup::ROLE_SUPPLIER_ID)) {
return false;
}
foreach ($offer->getDirectors() as $director) {
if ($user->getEmail() == $director->getEmail()) {
return true;
}
}
return false;
}
public function getExtensions(Offer $offer) {
$dql = 'select extension from Slivki:FoodOfferExtension extension index by extension.partnerItemID where extension.offer = :offer and extension.active = true';
$query = $this->getEntityManager()->createQuery($dql);
$query->setParameter('offer', $offer);
return $query->getResult();
}
public function getExtensionsByShippingType(Offer $offer, string $shippingType): array
{
$dql = '
SELECT
e
FROM Slivki:FoodOfferExtension AS e INDEX BY e.partnerItemID
WHERE e.offer = :offer
AND e.active = true
AND e.is' . ucfirst($shippingType) . ' = true
';
$query = $this->getEntityManager()->createQuery($dql);
$query->setParameter('offer', $offer);
return $query->getResult();
}
public function getExtensionVariants(OfferExtension $offerExtension) {
$dql = 'select extensionVariant from Slivki:OfferExtensionVariant extensionVariant index by extensionVariant.partnerID where extensionVariant.offerExtension = :offerExtension';
$query = $this->getEntityManager()->createQuery($dql);
$query->setParameter('offerExtension', $offerExtension);
return $query->getResult();
}
public function getDirector($offerID) {
$dql = 'select director from Slivki:Director director join director.offers offer where offer.ID = :offerID';
$query = $this->getEntityManager()->createQuery($dql);
$query->setParameter('offerID', $offerID);
$result = $query->execute();
if ($result) {
return $result[0];
}
return false;
}
public function getOfferConversion($offerID) {
$entityManager = $this->getEntityManager();
$visitRepository = $entityManager->getRepository(Visit::class);
$purchaseCountRepository = $entityManager->getRepository(PurchaseCount::class);
$visitCount = $visitRepository->getVisitCount($offerID, Visit::TYPE_OFFER, 30);
$purchaseCount = $purchaseCountRepository->findOneBy(['entityID' => $offerID]);
if (!$visitCount || !$purchaseCount || !$purchaseCount->getPurchaseCountLastMonth()) {
return 0;
}
return ceil(100 * ($purchaseCount->getPurchaseCountLastMonth() / $visitCount));
}
public function getCityID(int $offerID) : int {
return $this->getEntityManager()->getConnection()->executeQuery(
"select coalesce(max(city_id), " . City::DEFAULT_CITY_ID . ") from category"
. " inner join category2entity on category.id = category2entity.category_id"
. " where category2entity.entity_id = " . $offerID
)->fetchColumn();
}
private function getHasPurchaseOffersByCityIdQuery(
int $cityId,
Period $period
) : QueryBuilder {
$qb = $this->createQueryBuilder('hasPurchaseOffer');
$expr = $qb->expr();
return $qb->innerJoin('hasPurchaseOffer.offerOrders', 'offerOrder')
->innerJoin('hasPurchaseOffer.categories', 'hasPurchaseOfferCategories')
->andWhere($expr->eq('hasPurchaseOfferCategories.city', ':cityId'))
->andWhere($expr->gt('offerOrder.status', ':status'))
->andWhere($expr->between('offerOrder.createdOn', ':dateFrom', ':dateTo'))
->distinct()
->setParameters([
'cityId' => $cityId,
'status' => OfferOrder::STATUS_INIT,
'dateFrom' => $period->getStartDate(),
'dateTo' => $period->getEndDate(),
]);
}
/**
* @return Offer[]
*/
public function getHasNotPurchaseOffersByCityId(
int $cityId,
Period $period
) : iterable {
$qb = $this->createQueryBuilder('offer');
$expr = $qb->expr();
$getHasPurchaseOffersByCityIdDql = $this->getHasPurchaseOffersByCityIdQuery($cityId, $period)->getDQL();
return $qb->innerJoin('offer.categories', 'categories')
->andWhere($expr->eq('categories.city', ':cityId'))
->andWhere($expr->eq('offer.active', ':active'))
->andWhere($expr->between(':now', 'offer.activeSince', 'offer.activeTill'))
->andWhere($expr->lt('offer.activeSince', ':dateTo'))
->andWhere(sprintf('offer NOT IN (%s)', $getHasPurchaseOffersByCityIdDql))
->setParameters([
'cityId' => $cityId,
'status' => OfferOrder::STATUS_INIT,
'active' => true,
'dateFrom' => $period->getStartDate(),
'dateTo' => $period->getEndDate(),
'now' => (new \DateTimeImmutable())->format('Y-m-d H:i:s'),
])
->getQuery()
->getResult();
}
/**
* @return Offer[]
*/
public function getOffersOnlineOrdersStat(int $onlineOrderAllowedRemovedAtDaysBefore): array
{
$qb = $this->createQueryBuilder('offer');
$expr = $qb->expr();
return $qb
->andWhere(
$expr->orX(
$expr->isNotNull('offer.allowedOnlineOrderTypes'),
$expr->gt('offer.onlineOrderAllowedRemovedAt', ':onlineOrderAllowedRemovedAt'),
)
)
->setParameters([
'onlineOrderAllowedRemovedAt' => (new \DateTimeImmutable())
->modify(\sprintf('-%d days', $onlineOrderAllowedRemovedAtDaysBefore))
->format('Y-m-d H:i:s'),
])
->getQuery()
->getResult();
}
/**
* @return Offer[]
*/
public function getActiveOffersByCityId(int $cityId): array
{
$queryBuilder = $this->createQueryBuilder('offer');
$expr = $queryBuilder->expr();
return $queryBuilder
->innerJoin('offer.categories', 'category')
->innerJoin('category.city', 'city')
->andWhere($expr->eq('city.ID', ':cityId'))
->andWhere($expr->eq('offer.active', ':active'))
->andWhere($expr->neq('offer.hidden', ':hidden'))
->andWhere($expr->between(':now', 'offer.activeSince', 'offer.activeTill'))
->setParameters([
'cityId' => $cityId,
'active' => true,
'hidden' => true,
'now' => (new \DateTimeImmutable())->format('Y-m-d H:i:s'),
])
->getQuery()
->getResult();
}
}

src/Repository/OfferRepository.php line 845