Symfony Profiler

<?php
namespace App\Repository\Otpusk;
use App\Entity\Otpusk\HotelsRCache;
use App\Services\HelperService;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
/**
* @method HotelsRCache|null find($id, $lockMode = null, $lockVersion = null)
* @method HotelsRCache|null findOneBy(array $criteria, array $orderBy = null)
* @method HotelsRCache[] findAll()
* @method HotelsRCache[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class HotelsRCacheRepository extends ServiceEntityRepository
{
private $helperService;
private $imgUrl = 'https://newimg.otpusk.com/3_otpk/500x375/';
public function __construct(ManagerRegistry $registry, HelperService $helperService)
{
parent::__construct($registry, HotelsRCache::class);
$this->helperService = $helperService;
}
/**
* Main sql string for landing pages
*
* @param int|null $countryId
* @param int|null $cityId
* @param int|null $stars
* @param string|null $type
* @param int|null $budget
* @param string|null $currency
* @return string
*/
public function getMainSqlForToursPage($market, $dbNormalizer, $locale, int $countryId = null, int $cityId = null, int $stars = null, string $type = null, int $budget = null, string $currency = null, $vipStatus = false, int $cityFrom)
{
$countryField = $dbNormalizer['country']['name'][$locale];
$cityField = $dbNormalizer['city']['name'][$locale];
$priceField = 'price'.ucfirst($market['currency']['code']);
$sql = "SELECT h.id, h.name, h.cityId, city.{$cityField} AS cityName, h.countryId, country.{$countryField} AS countryName, h.stars, h.rating_avg AS rating, h.rating_by_site AS ratingBySite, p.offerId, p.operatorId, p.price, p.currency, p.{$priceField} as marketPrice, p.date, p.transport, p.food, p.length, CONCAT(:baseUrl, h.image) as desktop_gallery, h.hrefName, p.fromCityId, cityFrom.{$cityField}Rd as fromCityRd
FROM otpusk.hotelsRCache h
INNER JOIN otpusk.priceIndex p ON (h.id = p.rec_id)
LEFT JOIN tCountries AS country ON country.rec_id = h.countryId
LEFT JOIN tCities AS city ON city.rec_id = h.cityId
LEFT JOIN tCities AS cityFrom ON cityFrom.rec_id = p.fromCityId
WHERE p.date >= :dateBeg AND p.date <= :dateEnd
";
if ($cityFrom != $market['mainCities'][0]['id']) $sql .= ' AND p.fromCityId = :cityFrom';
if(isset($countryId) && $countryId > 0){
$sql .= ' AND h.countryId = :countryId';
}
if(isset($cityId)){
$sql .= ' AND h.cityId = :cityId';
}
if(isset($stars) && $stars > 0){
if($stars >= 3){
$sql .= ' AND h.stars = :stars';
} else {
$sql .= ' AND h.stars <= :stars';
}
}
if(isset($type)){
switch ($type){
case "friends":
$sql .= ' AND h.type_with_friends > 0';
break;
case "romantic":
$sql .= ' AND h.type_romantic_rest > 0';
break;
case "family":
$sql .= ' AND h.type_with_children > 0';
break;
case "peaceful":
$sql .= ' AND h.type_peaceful_rest > 0';
break;
case "hot":
$sql .= ' AND h.rating_avg >= 6';
break;
}
}
if ($vipStatus) $sql .= ' AND FIND_IN_SET(\'vip\', h.services)';
return $sql;
}
/**
* Execute sql for landing pages
*
* @param string|null $sql
* @param null $stars
* @param int|null $countryId
* @param int|null $cityId
* @param int $cityFrom
* @param null $period
* @param int|null $budget
* @return mixed
*/
public function getToursData($market, string $sql = null, $stars = null, int $countryId = null, int $cityId = null, $cityFrom, $period = null, int $budget = null)
{
$params = array(
'dateBeg' => $period['dateBeg'],
'dateEnd' => $period['dateEnd'],
'baseUrl' => $this->imgUrl,
);
if ($cityFrom != $market['mainCities'][0]['id']) $params['cityFrom'] = $cityFrom;
if(isset($countryId) && $countryId > 0){
$params['countryId'] = $countryId;
}
if(isset($cityId)){
$params['cityId'] = $cityId;
}
if(isset($stars)){
$params['stars'] = $stars;
}
if(isset($budget)){
$params['budget'] = $budget;
}
$conn = $this->getEntityManager()
->getConnection();
$stmt = $conn->prepare($sql);
foreach ($params as $index => $param) {
$stmt->bindValue($index, $param);
}
$tours = $this->addRatingBySites($stmt->execute()->fetchAll());
return $tours;
}
/**
* Additional information for best-quality tours
*
* @param null $type
* @param null $stars
* @param int|null $countryId
* @param int|null $cityId
* @param int $cityFrom
* @param null $period
* @param int|null $budget
* @param string|null $currency
* @return mixed
*/
public function getBestQualityTours($market, $dbNormalizer, $locale, $type = null, $stars = null, int $countryId = null, int $cityId = null, $cityFrom, $period = null, int $budget = null, string $currency = null, $isDeparture = false)
{
$sql = $this->getMainSqlForToursPage($market, $dbNormalizer, $locale, $countryId, $cityId, $stars, $type, $budget, $currency, false, $cityFrom);
if (!$isDeparture) {
$sql .= " AND " . $this->helperService->getWhere($market);
$sql .= " GROUP BY h.id ORDER BY " . $this->helperService->getOrderBy($market, 'fromCityId') . ", p.best DESC LIMIT 12";
} else {
$sql .= " GROUP BY h.id ORDER BY p.best DESC LIMIT 12";
}
return $this->getToursData($market, $sql, $stars, $countryId, $cityId, $cityFrom, $period, $budget);
}
/**
* Additional information for best-quality tours
*
* @param null $type
* @param null $stars
* @param int|null $countryId
* @param int|null $cityId
* @param int $cityFrom
* @param null $period
* @param int|null $budget
* @param string|null $currency
* @return mixed
*/
public function getBestHotelsTours($market, $dbNormalizer, $locale, $type = null, $stars = null, int $countryId = null, int $cityId = null, $cityFrom, $period = null, int $budget = null, string $currency = null, $isDeparture = false)
{
$sql = $this->getMainSqlForToursPage($market, $dbNormalizer, $locale, $countryId, $cityId, $stars, $type, $budget, $currency, false, $cityFrom);
$sql .= " AND h.rating_avg >= 6 ";
if (!$isDeparture) {
$sql .= " AND " . $this->helperService->getWhere($market);
$sql .= " GROUP BY h.id ORDER BY " . $this->helperService->getOrderBy($market, 'fromCityId') . ", h.rating_avg DESC LIMIT 12";
} else {
$sql .= " GROUP BY h.id ORDER BY h.rating_avg DESC LIMIT 12";
}
return $this->getToursData($market, $sql, $stars, $countryId, $cityId, $cityFrom, $period, $budget);
}
/**
* Additinal information for cheapest and hotest tours
*
* @param null $type
* @param int|null $countryId
* @param int|null $cityId
* @param int $cityFrom
* @param null $period
* @param null $budget
* @param null $currency
* @return mixed
*/
public function getCheapestTours($market, $dbNormalizer, $locale, $type = null, int $countryId = null, int $cityId = null, $cityFrom, $period = null, $budget = null, $currency = null, $vipStatus = false, $isDeparture = false)
{
$sql = $this->getMainSqlForToursPage($market, $dbNormalizer, $locale, $countryId, $cityId, null, $type, $budget, $currency, $vipStatus, $cityFrom);
if (!$isDeparture) {
$sql .= " AND " . $this->helperService->getWhere($market);
$sql .= " GROUP BY h.id ORDER BY " . $this->helperService->getOrderBy($market, 'fromCityId') . ", marketPrice LIMIT 12";
} else {
$sql .= " GROUP BY h.id ORDER BY marketPrice LIMIT 12";
}
return $this->getToursData($market, $sql, null, $countryId, $cityId, $cityFrom, $period, $budget);
}
/**
* Additinal information for tours by type
*
* @param string|null $type
* @param int|null $countryId
* @param int|null $cityId
* @param int $cityFrom
* @param null $period
* @param null $budget
* @param null $currency
* @param bool $order
* @return mixed
*/
public function getTypeTours($market, $dbNormalizer, $locale, string $type = null, int $countryId = null, int $cityId = null, $cityFrom, $period = null, $budget = null, $currency = null, $order = false, $withoutRating = false, $vipStatus = false, $isDeparture = false)
{
$sql = $this->getMainSqlForToursPage($market, $dbNormalizer, $locale, $countryId, $cityId, null, $type, $budget, $currency, $vipStatus, $cityFrom);
if (!$withoutRating) {
$sql .= " AND h.rating_avg >= 7 ";
} elseif (!$vipStatus) {
$sql .= " AND h.rating_avg < 7 ";
}
if (!$isDeparture) {
$sql .= " AND " . $this->helperService->getWhere($market);
}
if ($order) {
if (!$isDeparture) {
$sql .= " GROUP BY h.id ORDER BY " . $this->helperService->getOrderBy($market, 'fromCityId') . ", p.best DESC LIMIT 12";
} else {
$sql .= " GROUP BY h.id ORDER BY p.best DESC LIMIT 12";
}
} else {
if (!$isDeparture) {
$sql .= " GROUP BY h.id ORDER BY " . $this->helperService->getOrderBy($market, 'fromCityId') . ", marketPrice LIMIT 12";
} else {
$sql .= " GROUP BY h.id ORDER BY marketPrice LIMIT 12";
}
}
return $this->getToursData($market, $sql, null, $countryId, $cityId, $cityFrom, $period, $budget);
}
public function addRatingBySites($tours)
{
$sites = HotelsRCache::_SITENAMES;
foreach ($tours as $index => &$tour) {
$tour['ratingBySite'] = json_decode($tour['ratingBySite'], true);
if(is_array($tour['ratingBySite'])){
foreach ($tour['ratingBySite'] as &$item) {
$item['name'] = array_key_exists($item['site_id'], $sites) ? $sites[$item['site_id']] : '';
}
unset($item);
}
}
unset($tour);
return $tours;
}
/**
* @param $date
* @param int|null $countryId
* @param int|null $cityId
* @return mixed
* @throws \Doctrine\ORM\NoResultException
*/
public function getServicesCount($date, $countryId = null, $cityId = null, $period = null)
{
$params = array();
$conn = $this->getEntityManager()
->getConnection();
$sql = "SELECT MAX(h.services LIKE '%spa%') as spa,
MAX(h.services LIKE '%aquapark%') as aquapark,
MAX(h.services LIKE '%tennis_court%') as tennis_court,
MAX(h.services LIKE '%golf%') as golf,
MAX(h.services LIKE '%volleyball%') as volleyball,
MAX(h.services LIKE '%basketball%') as basketball,
MAX(h.services LIKE '%football%') as football,
MAX(h.services LIKE '%equestrian%') as equestrian,
MAX(h.services LIKE '%bikes%') as bikes,
MAX(h.services LIKE '%ski_slopes%') as ski_slopes,
MAX(h.services LIKE '%fitness%') as fitness,
MAX(h.services LIKE '%aerobics%') as aerobics,
MAX(h.services LIKE '%yoga%') as yoga,
MAX(h.services LIKE '%water_sports%') as water_sports,
MAX(h.services LIKE '%diving%') as diving,
MAX(h.services LIKE '%surfing%') as surfing,
MAX(h.services LIKE '%windsurfing%') as windsurfing,
MAX(h.services LIKE '%sauna%') as sauna,
MAX(h.services LIKE '%discotheque%') as discotheque
FROM otpusk.hotelsRCache AS h
INNER JOIN otpusk.priceIndex p ON (h.id = p.rec_id AND p.type='hotel' AND p.transport = h.countryTransport)";
if(isset($period)){
$sql .= " WHERE p.date >= :dateBeg AND p.date <= :dateEnd";
$params['dateBeg'] = $period['dateBeg'];
$params['dateEnd'] = $period['dateEnd'];
} else {
$sql .= " WHERE p.date > :date";
$params['date'] = $date;
}
if(isset($countryId)){
$sql .= ' AND h.countryId = :countryId';
$params['countryId'] = $countryId;
}
if(isset($cityId)){
$sql .= ' AND h.cityId = :cityId';
$params['cityId'] = $cityId;
}
$stmt = $conn->prepare($sql);
foreach ($params as $index => $param) {
$stmt->bindValue($index, $param);
}
return $stmt->execute()->fetchAll();
}
/**
* @param $date
* @param int|null $geoId
* @param string $type
* @return array
*/
public function getMonthlyInfo($market, int $geoId = null, $type)
{
$date = $this->getDateMonth();
if(!isset($geoId)) return array();
$conn = $this->getEntityManager()->getConnection();
$priceField = 'price'.ucfirst($market['currency']['code']);
$sql = 'SELECT MIN(pi.'.$priceField.') AS price, pi.date
FROM priceIndex AS pi
WHERE pi.date > :date AND pi.' . $type . ' = :geoId
GROUP BY MONTH(pi.date)';
// AND pi.fromCityId IN (:fromCityIds)
// $fromCityIds = [];
// foreach ($market['mainCities'] as $mainCity) {
// if (!is_null($mainCity['id'])) $fromCityIds[] = $mainCity['id'];
// }
$stmt = $conn->prepare($sql);
$stmt->bindValue('date', $date);
$stmt->bindValue('geoId', $geoId);
// $stmt->bindValue('fromCityIds', implode(',', $fromCityIds));
return $stmt->execute()->fetchAll();
}
public function getDateMonth()
{
$date = new \DateTime();
$day = $date->format('d');
if ($day > 15) {
$newDate = $date->modify('+1 day');
} else {
$newDate = $date->modify('last day of this month')->modify('-14 days');
}
$date = $newDate->format('Y-m-d');
return $date;
}
public function findHotToursForHP($market, $locale, $dbNormalizer)
{
$nameField = $dbNormalizer['hotTravels']['name'][$locale];
$countryField = $dbNormalizer['country']['name'][$locale];
$cityField = $dbNormalizer['city']['name'][$locale];
$priceField = 'fPrice'.ucfirst($market['currency']['code']);
$sql = "SELECT
ht.fType as type,
ht.fTourID as tourId,
ht.{$nameField} as name,
ht.fUrl as url,
ht.fDescription as description,
ht.fOperatorID AS operatorId,
ht.fCurrency AS currency,
ht.fPrice AS price,
ht.fAcmd AS acmd,
ht.fFood AS food,
ht.fTransport AS transport,
ht.fDate AS date,
ht.fDateFrom AS dateFrom,
ht.fDateTo AS dateTo,
ht.fLength AS length,
ht.fLengthFrom AS lengthFrom,
ht.fLengthTo AS lengthTo,
ht.fSort AS sort,
ht.fNoStat AS noStat,
ht.fPosition AS position,
ht.fOpen AS open,
ht.fClose AS close,
IF(ht.fStars='one',1,IF(ht.fStars='two',2,IF(ht.fStars='three' OR ht.fStars='hv1',3,IF(ht.fStars='four',4,IF(ht.fStars='five' OR ht.fStars='hv2',5,0))))) as star,
h.name AS hotel,
city.{$cityField} AS cityName,
h.cityCode AS city_url,
country.{$countryField} AS countryName,
h.countryHrefName AS country_url,
h.stars as stars,
h.rating_avg AS rating,
h.rating_by_site AS ratingBySite,
ht.fImage as image,
ht.{$priceField} as price
FROM tHotTravels AS ht
LEFT JOIN hotelsRCache AS h ON h.id = ht.fHotelID
LEFT JOIN tCountries AS country ON country.rec_id = h.countryId
LEFT JOIN tCities AS city ON city.rec_id = h.cityId
WHERE ht.fType IN ('tour', 'travel') AND ht.fBaseCountryID IN (".implode(',', $market['mainCountries']).") AND ht.fOpen <= NOW() AND ht.fClose > NOW() AND ht.fPrice > 0 AND ht.{$priceField} > 0 AND FIND_IN_SET(ht.fShow, 'main')
GROUP BY ht.rec_id
ORDER BY IF(ht.fPosition=0, 999, ht.fPosition) ASC, ht.fClose ASC
LIMIT 4
";
$conn = $this->getEntityManager()
->getConnection();
$stmt = $conn->prepare($sql);
$tours = $this->addRatingBySites($stmt->execute()->fetchAll());
return $tours;
}
}

src/Repository/Otpusk/HotelsRCacheRepository.php line 39