Symfony Profiler

<?php
/**
* Created by PhpStorm.
* User: adv
* Date: 06.12.15
* Time: 20:00
*/
namespace Slivki\Repository;
use Doctrine\ORM\Query;
use Doctrine\ORM\EntityRepository;
use Slivki\Entity\Category;
use Slivki\Entity\City;
use Slivki\Entity\Comment;
use Slivki\Entity\Media;
use Slivki\Entity\OfferPayedCategory;
use Slivki\Entity\Sale;
use Slivki\Entity\Seo;
use Slivki\Entity\TopSale;
use Slivki\Entity\VisitCounter;
use Slivki\Util\SoftCache;
use Slivki\Entity\Visit;
use Slivki\Util\TarantoolCache;
use Symfony\Component\HttpKernel\Exception\NotFoundHttpException;
class SaleRepository extends EntityRepository {
const CACHE_NAME = "Sale-0";
const CACHE_NAME_SIDEBAR = 'sidebar-2-';
const CACHE_NAME_PRODUCTS = 'sale-products-01-';
const GALLERY_CACHE_KEY = "sales-in-gallery-2-";
const ACTIVE_SALES_BY_CATEGORY = "activeByCategory";
const CACHE_NAME_TEASERS_FLIER = 'food-teaser-flier';
const CACHE_NAME_GEO_LOCATION_DATA = 'sale-geolocation-data';
const VISIT_COUNT_CACHE_KEY = 'visit-count';
const FLIER_VISIT_COUNT_CACHE_KEY = 'flier-visit-count';
const VISITED_SALE_COUNT_CACHE_KEY = 'visited-sales-count';
const VISITED_FLIER_COUNT_CACHE_KEY = 'visited-flier-count';
const CITIES_CACHE_KEY = 'flier-cities-';
const CACHE_KEY_BY_BRAND = '-brand-';
const CACHE_KEY_BY_CITY = '-city-';
const CACHE_KEY_BY_CATEGORY = '-category-';
const PRODUCT_TAGS_CACHE_KEY = '-tags-';
const SOS_CATEGORY_ID = 53;
const NEW_CATEGORY_ID = 59;
const POPULAR_SALE_CATEGORY_ID = 1978;
const FAKE_CATEGORY_ID = PHP_INT_MAX;
const RESIZED_IMAGE_PREFIX = '_resized_';
const HASWM_IMAGE_PREFIX = '_haswm_';
const WITHWM_IMAGE_PREFIX = '_65de6_';
const HOT_SALES_DURABILITY_DAYS = 7;
const API_HOST = 'http://supercheck.by';
const API_ROOT_URL = '/api/v2/';
public static $salePositionList = [];
/**
* @throws NotFoundHttpException
*/
public function getActiveSalesByCategoryID($categoryID) {
$softCache = new SoftCache(self::CACHE_NAME);
$saleListByCategory = $softCache->get(self::ACTIVE_SALES_BY_CATEGORY);
if (!$saleListByCategory) {
throw new NotFoundHttpException();
}
$saleList = [];
if (isset($saleListByCategory[$categoryID])) {
$saleList = $softCache->getMulti($saleListByCategory[$categoryID]);
}
if (!$saleList) {
return [];
}
$result = [];
foreach ($saleList as $sale) {
if ($sale != null) {
$result[] = $sale;
}
}
$saleList = $result;
$category = $this->getEntityManager()->getRepository(Category::class)->findCached($categoryID);
if (in_array($categoryID, [self::POPULAR_SALE_CATEGORY_ID, Category::FLIER_SALE_CATEGORY_ID]) ||
(isset($category['category']) && $category['category']->isChildOf(Category::FLIER_SALE_CATEGORY_ID))) {
$dql = "select salePosition from Slivki:OfferPayedCategory salePosition index by salePosition.entityID where salePosition.categoryID = :categoryID order by salePosition.position";
self::$salePositionList = $this->getEntityManager()->createQuery($dql)->setParameter('categoryID', $categoryID)->execute();
usort($saleList, ['\Slivki\Repository\SaleRepository', 'compareSalesByPosition']);
} else {
usort($saleList, ['\Slivki\Repository\SaleRepository', 'compareSalesBySince']);
}
return $saleList;
}
public function getAllSalesByCategoryID($categoryID) {
$dql = "select sale, offerPayedCategory.position, category from Slivki:Sale sale
join sale.categories category
left join Slivki:OfferPayedCategory offerPayedCategory
with offerPayedCategory.entityID = sale.ID and offerPayedCategory.categoryID = :categoryID
where category.ID = :categoryID
order by offerPayedCategory.position, sale.active desc, sale.since desc, sale.ID desc";
$saleList = $this->getEntityManager()->createQuery($dql)->setParameter("categoryID", $categoryID)->getResult();
return $saleList;
}
public function getSortedActiveSalesByCategoryID($categoryID) {
$dql = "select sale, offerPayedCategory.position from Slivki:Sale sale
join sale.categories category
left join Slivki:OfferPayedCategory offerPayedCategory
with offerPayedCategory.entityID = sale.ID and offerPayedCategory.categoryID = :categoryID
where category.ID = :categoryID and sale.active = true and sale.since < CURRENT_TIMESTAMP() and sale.till > CURRENT_TIMESTAMP()
order by offerPayedCategory.position, sale.since desc, sale.ID desc";
$saleList = $this->getEntityManager()->createQuery($dql)->setParameter("categoryID", $categoryID)->getResult();
return $saleList;
}
public function getAllActiveSales(){
$dql = "select sale from Slivki:Sale sale
where sale.active = true
and sale.since < CURRENT_TIMESTAMP()
and sale.till > CURRENT_TIMESTAMP()
order by sale.active desc";
$sales = $this->getEntityManager()->createQuery($dql)->getResult();
return $sales;
}
public function getActiveSaleByID($saleID) {
$softCache = new SoftCache(self::CACHE_NAME);
$sale = $softCache->get($saleID);
if ($sale && $sale->getCategories()->count() == 0 || true) {//TODO: delete after bugfix
$sale = $this->reloadCacheForSale($saleID);
}
return $sale;
}
public function reloadCache() {
$dql = "select sale, category, saleVersions, geoLocations from Slivki:Sale sale index by sale.ID
left join sale.versions saleVersions
left join sale.geoLocations geoLocations
inner join sale.categories category where category.active = true and sale.active = true
and sale.since < CURRENT_TIMESTAMP() and sale.till > CURRENT_TIMESTAMP() order by sale.since desc";
$saleList = $this->getEntityManager()->createQuery($dql)->getResult();
$saleListByCategory = [];
$mediaRepository = $this->getEntityManager()->getRepository(Media::class);
$commentRepository = $this->getEntityManager()->getRepository(Comment::class);
$cityList = [];
$salesByCity = [];
/** * @var \Slivki\Entity\Sale $sale */
foreach ($saleList as $saleID => $sale) {
$sale->getDirectors()->toArray();
$saleList[$saleID]->setIcon($mediaRepository->getSaleIconMedia($saleID));
$saleList[$saleID]->setHotFeedIconMedia($mediaRepository->getOfferHotFeedIconMedia($saleID));
$saleList[$saleID]->setAlternativeIcon($mediaRepository->getSaleAlternativeIconMedia($saleID));
$saleList[$saleID]->setFlierMedia($mediaRepository->getFlierImageMedia($saleID));
foreach ($sale->getDescriptions() as $saleDescription) {
$saleDescription->setDescription(str_replace(self::HASWM_IMAGE_PREFIX, self::WITHWM_IMAGE_PREFIX, $saleDescription->getDescription()));
$saleDescription->getEntityDescriptionSliderImages()->toArray();
}
$saleList[$saleID]->setRating($commentRepository->getEntityRating(Category::SALE_CATEGORY_ID, $saleID));
foreach ($sale->getCategories() as $category) {
$saleListByCategory[$category->getID()][] = $sale->getID();
}
}
$saleCategoryList = [];
$categoryList = $this->getEntityManager()->getRepository(Category::class)->getActiveCategoriesNotCached(Category::SALE_CATEGORY_ID);
foreach ($categoryList as $category) {
if (!isset($saleListByCategory[$category->getID()])) {
continue;
}
$category->setEntityCount(count($saleListByCategory[$category->getID()]));
$category->getParentCategories()->toArray();
$category->setHotFeedIconMedia($mediaRepository->getСategoryHotFeedIconMedia($category->getID()));
$saleCategoryList[$category->getID()] = ['category' => $category, 'entityList' => $saleListByCategory[$category->getID()]];
}
$softCache = new SoftCache(self::CACHE_NAME);
$softCache->setMulti($saleList, 0);
$softCache->set(self::ACTIVE_SALES_BY_CATEGORY, $saleListByCategory, 0);
$softCache = new SoftCache(CategoryRepository::CACHE_NAME);
$categoryListCached = $softCache->get(CategoryRepository::ALL_CATEGORIES_CACHE_KEY);
foreach ($saleCategoryList as $categoryID => $categoryData) {
$categoryListCached[$categoryID] = $categoryData;
}
$softCache->set(CategoryRepository::ALL_CATEGORIES_CACHE_KEY, $categoryListCached, 0);
$softCache->setMulti($saleCategoryList, 0);
$this->getEntityManager()->clear();
$this->reloadFliersCache($saleList);
}
/** @deprecated */
public function reloadCacheForSale($saleID) {
/** @var Sale $sale */
$sale = $this->find($saleID);
if (!$sale) {
return;
}
if (!$sale->isActive() || !$sale->isVisible()) {
$this->removeSaleFromCache($sale->getID());
return;
}
$mediaRepository = $this->getEntityManager()->getRepository(Media::class);
$sale->setIcon($mediaRepository->getSaleIconMedia($sale->getID()));
$sale->setHotFeedIconMedia($mediaRepository->getOfferHotFeedIconMedia($sale->getID()));
$sale->setAlternativeIcon($mediaRepository->getSaleAlternativeIconMedia($sale->getID()));
$sale->setFlierMedia($mediaRepository->getFlierImageMedia($saleID));
$sale->getDirectors()->toArray();
$sale->getGeoLocations()->toArray();
foreach ($sale->getDescriptions() as $saleDescription) {
$saleDescription->setDescription(str_replace(self::HASWM_IMAGE_PREFIX, self::WITHWM_IMAGE_PREFIX, $saleDescription->getDescription()));
}
$sale->setRating($this->getEntityManager()->getRepository(Comment::class)->getEntityRating(Category::SALE_CATEGORY_ID, $saleID));
foreach ($sale->getVersions()->toArray() as $version) {
foreach ($version->getDescriptions() as $description) {
$description->getEntityDescriptionSliderImages()->toArray();
}
}
$softCache = new SoftCache(self::CACHE_NAME);
$softCache->set($saleID, $sale, 0);
$salesByCategory = $softCache->get(self::ACTIVE_SALES_BY_CATEGORY);
foreach ($salesByCategory as $categoryID => $category) {
if (in_array($saleID, $category)) {
$categoryFound = false;
foreach ($sale->getCategories() as $saleCategory) {
if ($saleCategory->getID() == $categoryID) {
$categoryFound = true;
break;
}
}
if (!$categoryFound) {
$salesByCategory[$categoryID] = array_diff($salesByCategory[$categoryID], [$saleID]);
}
}
}
foreach ($sale->getCategories() as $category) {
if (!isset($salesByCategory[$category->getID()])) {
if ($category->isActive() || !$category->isPast()) {
$category->getBanners()->toArray();
$category->setEntityCount(1);
$categoryForCache['category'] = $category;
$categoryForCache['entityList'][] = $saleID;
$salesByCategory[$category->getID()][] = $saleID;
$this->getEntityManager()->getRepository(Category::class)->putCategoryToCache($categoryForCache);
}
} else {
if (!in_array($sale->getID(), $salesByCategory[$category->getID()])) {
$salesByCategory[$category->getID()][] = $sale->getID();
}
}
}
$softCache->set(self::ACTIVE_SALES_BY_CATEGORY, $salesByCategory, 0);
$this->getEntityManager()->getRepository(Seo::class)->reloadCacheForEntity(SeoRepository::RESOURCE_URL_SALE_DETAILS, $saleID);
return $sale;
}
public function reloadFliersCache($flierList = null) {
if (!$flierList) {
$flierList = $this->getActiveSalesByCategoryID(Category::FLIER_SALE_CATEGORY_ID);
}
$cityList = [];
foreach ($flierList as $flier) {
if ($flier->isInCategory(Category::FLIER_SALE_CATEGORY_ID)) {
foreach ($flier->getGeoLocations() as $geoLocation) {
$cityList[trim($geoLocation->getCity())][] = $flier->getID();
}
}
}
ksort($cityList);
$cityListSorted = [];
foreach ($cityList as $cityName => $fliers) {
$cityListSorted[mb_substr($cityName, 0, 1)][] = ['cityName' => $cityName, 'entityList' => $fliers];
}
$softCache = new SoftCache(self::CACHE_NAME);
$softCache->set(self::CITIES_CACHE_KEY, $cityListSorted, 0);
}
public function removeSaleFromCache($saleID) {
$softCache = new SoftCache(self::CACHE_NAME);
$sale = $softCache->get($saleID);
if (!$sale) {
return;
}
$softCache->delete($saleID);
$salesByCategory = $softCache->get(self::ACTIVE_SALES_BY_CATEGORY);
foreach ($salesByCategory as $key => $category) {
$salesByCategory[$key] = array_diff($category, [$saleID]);
}
$softCache->set(self::ACTIVE_SALES_BY_CATEGORY, $salesByCategory, 0);
}
private static function compareSalesBySince(Sale $sale, Sale $sale1) {
return $sale->getSince() > $sale1->getSince() ? -1 : 1;
}
private static function compareSalesByPosition(Sale $sale, Sale $sale1) {
$saleID = $sale->getID();
$sale1ID = $sale1->getID();
$salePosition = isset(self::$salePositionList[$saleID]) ? self::$salePositionList[$saleID]->getPosition() : INF;
$sale1Position = isset(self::$salePositionList[$sale1ID]) ? self::$salePositionList[$sale1ID]->getPosition() : INF;
if ($salePosition == $sale1Position) {
if ($sale->getSince() == $sale1->getSince()) {
return $sale->getID() > $sale1->getID() ? -1 : 1;
}
return $sale->getSince() > $sale1->getSince() ? -1 : 1;
}
return $salePosition > $sale1Position ? 1 : -1;
}
public function getCategoryBoxData($categoryID) {
$categoryRepository = $this->getEntityManager()->getRepository(Category::class);
$categoryList = $categoryRepository->getActiveCategories(0, array(Category::DEFAULT_CATEGORY_TYPE), Category::SALE_CATEGORY_ID);
foreach ($categoryList as $category) {
$saleList = null;
if ($category->getID() == $categoryID) {
$saleList = $this->getSalesSorted($categoryID);
}
$data['categoryList'][$category->getID()] = [
"category" => $category,
"saleList" => $saleList
];
}
return $data;
}
public function getCategoryBoxDataCached($categoryID, $reload = false) {
$softCache = new SoftCache(self::CACHE_NAME);
$cacheKey = 'categoryBox-17-' . $categoryID;
$categoryBoxData = $softCache->get($cacheKey);
if ($categoryBoxData && !$reload) {
return $categoryBoxData;
}
$categoryBoxData = $this->getCategoryBoxData($categoryID);
$softCache->set($cacheKey, $categoryBoxData, 0);
return $categoryBoxData;
}
public function getSalesSorted($categoryID) {
$saleList = $this->getActiveSalesByCategoryID($categoryID);
$payedPositions = $this->getEntityManager()->getRepository(OfferPayedCategory::class)->findBy(
['categoryID' => $categoryID], ['position' => 'asc']);
if ($payedPositions) {
foreach ($payedPositions as $position) {
foreach ($saleList as $key => $sale) {
if ($sale->getID() == $position->getEntityID()) {
unset($saleList[$key]);
array_splice($saleList, $position->getPosition() - 1, 0, [$sale]);
break;
}
}
}
}
return $saleList;
}
public function getActiveSalesCount() {
$sql = "select count(*) from sale where active and now() between since and till";
return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn(0);
}
public function getActiveSalesCountCached() {
$softCache = new SoftCache(self::CACHE_NAME);
$cacheKey = 'activeCount';
$salesCount = $softCache->get($cacheKey);
if (!$salesCount) {
$salesCount = $this->getActiveSalesCount();
$softCache->set($cacheKey, $salesCount, 30 * 60);
}
return $salesCount;
}
public function getTopMainSalesJson() {
$salesList = $this->getEntityManager()->getRepository(Sale::class)->findBy(['showOnMainPage' => true]);
$salesListJson = [];
foreach ($salesList as $sale) {
$tempSale = [];
$tempSale['id'] = $sale->getId();
$tempSale['topPosition'] = $sale->getTopPosition();
$salesListJson[] = $tempSale;
}
return $salesListJson;
}
public function getTopMainSales() {
$salesListJson = $this->getEntityManager()->getRepository(Sale::class)->findBy(['showOnMainPage' => true], ['topPosition' => 'asc']);
return $salesListJson;
}
public function getMainSales($cityID, $isMobileDevice = false, $offset = 0) {
$topSaleList = $this->getEntityManager()->getRepository(TopSale::class)->findBy(
['city' => $cityID],
['sortOrder' => 'ASC'],
3,
$offset
);
if (count($topSaleList) == 0 && $cityID != City::DEFAULT_CITY_ID) {
$topSaleList = $this->getEntityManager()->getRepository(TopSale::class)->findBy(
['city' => City::DEFAULT_CITY_ID],
['sortOrder' => 'ASC'],
3,
$offset
);
}
$result = [];
$seoRepository = $this->getEntityManager()->getRepository(Seo::class);
foreach ($topSaleList as $topSale) {
$sale = $topSale->getSale();
$data = [];
$data['media'] = $this->getEntityManager()->getRepository(Media::class)->getSaleMainIconMedia($sale->getID());
$data['sale'] = $sale;
if ($sale->getAlternativeURL()) {
$data['url'] = $sale->getAlternativeURL();
} else {
$seo = $seoRepository->getByEntity(SeoRepository::RESOURCE_URL_SALE_DETAILS, $sale->getID());
$data['url'] = $seo ? $seo->getMainAlias() : '';
}
$result[] = $data;
}
return $result;
}
/**
* @return Sale
*/
public function findCached($saleID) {
$softCache = new SoftCache(self::CACHE_NAME);
return $softCache->get($saleID);
}
public function getVisitCount($sale) {
$dql = "select count(visit.ID) from Slivki:Visit visit
where visit.entityID = :saleID and visit.entityTypeID = :entityTypeID and visit.createdOn > :dateFrom and visit.createdOn > :saleSince";
return $this->getEntityManager()->createQuery($dql)
->setParameter('dateFrom', new \DateTime('-30 days'))
->setParameter('saleID', $sale->getID())
->setParameter('saleSince', $sale->getSince())
->setParameter('entityTypeID', Category::SALE_CATEGORY_ID)
->getSingleScalarResult();
}
public function getRecentVisitCount($saleID) {
$sql = "select * from offer_category_sale where sale_id = $saleID limit 1";
$result = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchAll();
return $result[0]['recent_visit_count'];
}
public function getSaleRating($saleID) {
$dql = "select avg(comment.rating) rating from Slivki:Comment comment
where comment.entityID = :entityID and comment.typeID = :typeID and comment.rating > 0 and comment.checked = true and comment.confirmedPhone = true and comment.hidden != true";
$rating = $this->getEntityManager()->createQuery($dql)
->setParameter('entityID', $saleID)->setParameter('typeID', Comment::TYPE_SALE_COMMENT)->getResult(Query::HYDRATE_SCALAR);
return round($rating[0]['rating'], 1);
}
public function sortSaleListByRecentPopularity($saleList) {
if (count($saleList) == 0) {
return [];
}
$idList = [];
foreach ($saleList as $sale) {
$idList[] = $sale->getID();
}
$sql = 'select entity_id from visit_counter where type = ' . VisitCounter::TYPE_SALE . ' and entity_id in (' . implode(', ', $idList) . ') order by visit_count_recent desc';
$idList = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchAll();
$saleListSorted = [];
foreach ($idList as $item) {
$saleListSorted[] = $saleList[$item['entity_id']];
}
$notInsertedSales = array_udiff($saleListSorted, $saleList,
function ($sale, $sale1) {
return $sale->getID() - $sale1->getID();
}
);
if (count($notInsertedSales) > 0) {
$saleListSorted = array_merge($saleListSorted, $notInsertedSales);
}
return $saleListSorted;
}
/**
* @return Sale
*/
public function getInactiveSaleByUrl($url) {
$sql = "select sale.id from sale inner join seo on sale.id = seo.entity_id where seo.resource_url = 'Slivki:Sale:details' and seo.main_alias = '$url' and sale.till < now() limit 1";
$saleID = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn();
if (!$saleID) {
return null;
}
return $this->find($saleID);
}
public function getHotSales($limit = null, $offset = 0) {
$sql = "select sale.id from sale inner join category2entity on sale.id = category2entity.entity_id
where category_id = " . Category::NEW_SALE_CATEGORY_ID . " and sale.active and sale.since > now() - interval '" . self::HOT_SALES_DURABILITY_DAYS . "' day order by sale.since desc, sale.id desc offset " . $offset;
if ($limit) {
$sql .= ' limit ' . $limit;
}
$salIDList = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchAll(\PDO::FETCH_COLUMN);
$softCache = new SoftCache(self::CACHE_NAME);
$saleList = $softCache->getMulti($salIDList);
if (!$saleList) {
$saleList = [];
}
$saleListFiltered = [];
foreach ($saleList as $sale) {
if ($sale) {
$saleListFiltered[] = $sale;
}
}
return $saleListFiltered;
}
public function getAllActiveSalesCached() {
$categoryList = $this->getEntityManager()->getRepository(Category::class)->getActiveCategories(0,[Category::DEFAULT_CATEGORY_TYPE], Category::SALE_CATEGORY_ID);
$saleList = [];
foreach ($categoryList as $category) {
foreach ($this->getActiveSalesByCategoryID($category->getID()) as $sale) {
if (!isset($saleList[$sale->getID()])) {
$saleList[$sale->getID()] = $sale;
}
}
}
return $saleList;
}
public function getSaleIDSorted($sortBy, $categoryID = null, $offset = null, $limit = null, array $coordinates = null) {
$sql = 'select sale.id from sale';
$categoryCondition = '';
if ($categoryID) {
$sql .= ' inner join category2entity on sale.id = category2entity.entity_id';
$categoryCondition = ' and category2entity.category_id = ' . (int)$categoryID;
}
$orderBy = ' order by ';
switch ($sortBy) {
case 'new':
$orderBy .= 'sale.since desc';
break;
case 'views':
$sql .= ' inner join (select entity_id, sum(visit_count) as visit_count from stat_visit_count_aggregated where entity_type_id = ' . Visit::TYPE_SALE .
" and visit_date > now() + '-45 days' group by 1) as visit_count on sale.id = visit_count.entity_id";
$orderBy .= 'visit_count.visit_count desc';
break;
case 'location':
if ($coordinates) {
$sql .= ' inner join geo_location_relation on sale.id = geo_location_relation.entity_id inner join geo_location on geo_location_relation.location_id = geo_location.id';
$orderBy .= "min(earth_distance(ll_to_earth($coordinates[0], $coordinates[1]), ll_to_earth(coalesce(nullif(latitude, ''), '0')::float8, coalesce(nullif(longitude, ''), '0')::float8)))";
}
break;
default:
$orderBy .= 'sale.since desc';
break;
}
$sql .= ' where sale.active and now() between sale.since and sale.till' . $categoryCondition;
if ($coordinates) {
$sql .= ' group by 1 ';
}
$sql .= $orderBy;
if ($offset !== null && $limit) {
$sql .= ' offset ' . (int)$offset . ' limit ' . (int)$limit;
}
return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchAll(\PDO::FETCH_COLUMN);
}
public function getSalesWithProduct() {
$saleList = $this->getActiveSalesByCategoryID(Category::FLIER_SALE_CATEGORY_ID);
$result = [];
foreach ($saleList as $sale) {
if ($sale->getSaleProducts()->count() > 0) {
$result[] = $sale;
}
}
return $result;
}
public function getProducts() {
$dql = 'select product from Slivki:Product product join product.saleProducts saleProduct join saleProduct.sale sale where sale.active = true and sale.till > now() order by product.name asc';
return $this->getEntityManager()->createQuery($dql)->getResult();
}
public function getSaleProducts($offset, $limit = null) {
$dql = 'select product, saleProduct, saleVersion, sale, product, teaserMedia from Slivki:Product product join product.saleProducts saleProduct join saleProduct.saleVersion saleVersion join saleVersion.sale sale index by saleProduct.ID
join saleProduct.teaserMedia teaserMedia
where sale.active = true and sale.till > current_timestamp() and current_timestamp() between saleVersion.since and saleVersion.till + \'1 day\' order by sale.till desc, saleProduct.ID asc';
$query = $this->getEntityManager()->createQuery($dql);
$query->setFirstResult($offset);
if ($limit) {
$query->setMaxResults($limit);
}
return $query->getResult();
}
public function getSaleProductCategories() {
$dql = 'select category from Slivki:ProductCategory category join category.products product join product.saleProducts saleProduct join saleProduct.saleVersion saleVersion
join saleVersion.sale sale where sale.active = true order by category.name';
return $this->getEntityManager()->createQuery($dql)->getResult();
}
public function getSaleProductTopLevelCategories() {
$dql = 'select category from Slivki:ProductCategory category where category.parents is empty order by category.name';
return $this->getEntityManager()->createQuery($dql)->getResult();
}
public function reloadProductCache() {
ini_set('memory_limit', '1g');
$dbConnection = $this->getEntityManager()->getConnection();
$productList = $this->getSaleProducts(0);
$byTradingNetwork = [];
$byCategory = [];
$byCity = [];
$saleProductList = [];
$tagList = [];
foreach ($productList as $product) {
foreach ($product->getSaleProducts() as $saleProduct) {
if (!$saleProduct->getProduct()->getCategories()) {
continue;
}
$saleProductList[$saleProduct->getID()] = $saleProduct;
}
}
$sql = 'select product.id, product.name from product inner join sale_product on product.id = sale_product.product_id
inner join sale_version on sale_product.sale_version_id = sale_version.id';
$allProductList = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchAll(\PDO::FETCH_ASSOC);
foreach ($allProductList as $item) {
$position = mb_stripos($item['name'], '"');
if ($position > 0) {
$tag = trim(mb_strtolower(mb_substr($item['name'], 0, $position)));
$tagList[$tag][] = $item['id'];
}
}
foreach ($saleProductList as $key => $saleProduct) {
$sale = $saleProduct->getSaleVersion()->getSale();
$sale->getGeoLocations()->toArray();
$saleProduct->getProduct();
$sale->getDirectors()->first();
$saleProductList[$key] = $saleProduct;
foreach ($saleProduct->getProduct()->getCategories() as $category) {
$category->getProducts()->toArray();
}
$byTradingNetwork[$sale->getID()][] = $saleProduct->getID();
$sql = 'select parent_id from category2entity inner join category_child_relation on category2entity.category_id = category_child_relation.child_id where entity_id = ' . $saleProduct->getProduct()->getID();
foreach ($dbConnection->executeQuery($sql)->fetchAll(\PDO::FETCH_COLUMN) as $categoryID) {
$byCategory[$categoryID][] = $saleProduct->getID();
}
foreach ($sale->getGeoLocations() as $location) {
$byCity[mb_strtolower($location->getCity())][] = $saleProduct->getID();
}
}
$softCache = new SoftCache(self::CACHE_NAME_PRODUCTS);
$softCache->setMulti($saleProductList, 0);
$softCache->set(SoftCache::CACHE_KEY_ALL, $saleProductList, 0);
$softCache->set(self::CACHE_KEY_BY_BRAND, $byTradingNetwork, 0);
$softCache->set(self::CACHE_KEY_BY_CATEGORY, $byCategory, 0);
$softCache->set(self::CACHE_KEY_BY_CITY, $byCity, 0);
$softCache->set(self::PRODUCT_TAGS_CACHE_KEY, $tagList, 0);
dump('done');
}
public function getSaleProductsCached() {
return (new SoftCache(self::CACHE_NAME_PRODUCTS))->get(SoftCache::CACHE_KEY_ALL);
}
public function getSaleProductCached($saleProductID) {
return (new SoftCache(self::CACHE_NAME_PRODUCTS))->get($saleProductID);
}
public function getVideoguideAutorCategory(Sale $sale) {
/** @var Category $category */
foreach ($sale->getCategories() as $category) {
if ($category->isChildOf(Category::SALE_VIDEO_GUIDE_AUTORS_CATEGORY_ID)) {
return $category;
};
}
return null;
}
public function getSaleProductsIDByCategoryID($categoryID) {
$sql = 'select DISTINCT sale_product.id from sale_product
inner join sale_version on sale_product.sale_version_id = sale_version.id
inner join category2entity on sale_version.sale_id = category2entity.entity_id
inner join sale on category2entity.entity_id = sale.id
inner join media on media.entity_id = sale_product.id
where category2entity.category_id = ' . $categoryID . ' and sale.active and media.type = 1
and now() between sale_version.since and sale_version.till order by 1;';
return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchAll(\PDO::FETCH_COLUMN);
}
public function getSaleProductsByCategoryID($categoryID) {
$dql = 'select saleProduct, saleVersion from Slivki:SaleProduct saleProduct join saleProduct.saleVersion saleVersion join saleProduct.product product join product.categories category
where category.ID = :categoryID and current_date() between saleVersion.since and saleVersion.till';
$query = $this->getEntityManager()->createQuery($dql)->setParameter('categoryID',$categoryID);
return $query->getResult();
}
public function getSupercheckShops() {
$shopList = $this->sendSupercheckApiRequest('city/0/shops', false, false, null);
if (!$shopList) {
return [];
}
return $shopList;
}
private function sendSupercheckApiRequest($request, $body = false, $userToken = false, $httpHeader = 'json') {
$request = self::API_HOST . self::API_ROOT_URL . $request;
$curl = curl_init($request);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
$result = curl_exec($curl);
$statusCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
$result = json_decode($result);
if ($statusCode != 200) {
return false;
}
return $result->result;
}
}

src/Repository/SaleRepository.php line 129