Symfony Profiler

<?php
namespace App\Repository\Otpusk;
use App\Entity\Otpusk\Country;
use App\Services\HelperService;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use GeoIp2\Database\Reader;
class CountryRepository extends ServiceEntityRepository
{
private $helperService;
private $imageSource = 'https://www.otpusk.com/foto/2/800x600/';
public function __construct(ManagerRegistry $registry, HelperService $helperService)
{
parent::__construct($registry, Country::class);
$this->helperService = $helperService;
}
public function getCountryInfo($countryId, $locale = 'ru', $dbNormalizer)
{
$name = $dbNormalizer['country']['name'][$locale];
$nameCase = $dbNormalizer['country']['case'][$locale];
$nameCurrency = $dbNormalizer['country']['currency'][$locale];
$nameLanguage = $dbNormalizer['country']['language'][$locale];
$sql = $this->createQueryBuilder('c')
->select('c.id AS id, c.foodCost, c.coffeeCost, c.'.$nameCurrency.' as currencyName, c.'.$nameLanguage.' as language, c.timeLocal, c.priceSegment, c.flightTime, c.fCurrency as currency, c.fCode AS code, c.'.$name.' AS name, c.'.$nameCase.'Rd AS nameRd, c.'.$nameCase.'Vn AS nameVn, c.'.$nameCase.'Pr AS namePr, c.'.$nameCase.'Dt AS nameDt, c.fNameTr AS nameTr, c.fNameTr AS countryTr, c.'.$name.' AS countryName')
->where('c.id = :country')
->setParameter('country', $countryId)
->getQuery();
return $sql->getOneOrNullResult();
}
public function getIdByAlias($country, $city)
{
$sql = $this->createQueryBuilder('c')
->select('c.id AS countryId')
->where('c.fNameTr = :country')
->orWhere('c.fNameTr = :countryChange')
->orWhere('c.fNameTr = :countryChangeTwo')
->setParameter('country', $country)
->setParameter('countryChange', $country)
->setParameter('countryChangeTwo', str_replace('_', ' ', $country))
;
if(isset($city)){
$sql->addSelect('ct.id AS cityId')
->leftJoin('c.cities', 'ct')
->andWhere('ct.fNameTr = :city')
->setParameter('city', $city);
}
return $sql->getQuery()->getOneOrNullResult();
}
/**
* @param $country
* @param $city
* @param null $season
* @return mixed
*/
public function getSeoByAlias($market, $dbNormalizer, $country, $city, $season = null, $locale = 'ru')
{
$nameCase = $dbNormalizer['country']['case'][$locale];
$nameCity = $dbNormalizer['city']['name'][$locale];
$priceField = 'price'.ucfirst($market['currency']['code']);
$sql = $this->createQueryBuilder('c')
->select('c.'.$nameCase.'Vn AS nameVn, c.'.$nameCase.'Pr AS namePr, city.'.$nameCity.' AS fName, price.price, price.currency')
->leftJoin('c.cities', 'city')
->leftJoin('App\Entity\Otpusk\PriceIndex', 'price', \Doctrine\ORM\Query\Expr\Join::WITH, 'price.id = city.id AND price.type=\'city\'')
->leftJoin('App\Entity\Otpusk\ExchangeRate', 'e', \Doctrine\ORM\Query\Expr\Join::WITH, 'price.currency = e.id')
->where('c.fNameTr = :country OR c.fNameTr = :countryRp')
->groupBy('price.id')
->orderBy('price.price')
->setMaxResults(3)
->setParameters(array('country' => $country, 'countryRp' => str_replace('_', '-', $country)));
;
if(isset($city)){
$sql->addSelect('ct.'.$nameCity.'Vn AS cityNameVn, ct.'.$nameCity.'Pr AS cityNamePr')
->leftJoin('c.cities', 'ct')
->andWhere('ct.fNameTr = :city')
->setParameter('city', $city);
}
$result = $sql->getQuery()->execute();
$sql = $this->createQueryBuilder('c')
->select('MIN(p.'.$priceField.') AS lowPrice')
->andWhere('c.fNameTr = :country OR c.fNameTr = :countryRp')
->andWhere($this->helperService->getWhereForORM($market))
->andWhere('p.transport != \'no\'')
->andWhere('p.'.$priceField.' > 0')
->setParameters(array('country' => $country, 'countryRp' => str_replace('_', '-', $country)));
if(isset($city)){
$sql->leftJoin('c.cities', 'ct')
->leftJoin('App\Entity\Otpusk\PriceIndex', 'p', \Doctrine\ORM\Query\Expr\Join::WITH, 'p.id = ct.id')
->andWhere('ct.fNameTr = :city')
->setParameter('city', $city);
} else {
$sql->leftJoin('App\Entity\Otpusk\PriceIndex', 'p', \Doctrine\ORM\Query\Expr\Join::WITH, 'p.id = c.id');
}
if(isset($season)){
$sql->andWhere('p.date LIKE :month')
->setParameter('month', '%-'.$season.'-%');
}
$result['lowPrice'] = $sql->getQuery()->getOneOrNullResult();
return $result;
}
public function getCountryFaq($countryId, $type, $locale = 'ru', $dbNormalizer)
{
$params = array(
'country' => $countryId,
'type' => $type,
'bind' => 'country',
'lang' => $dbNormalizer['tRefs']['language'][$locale]
);
$conn = $this->getEntityManager()
->getConnection();
$sql = 'SELECT
f.fTitle AS title,
f.fText AS text,
f.fLang AS lang
FROM
otpusk.tCountries c
LEFT JOIN otpusk.tRefs f ON c.rec_id = f.fBindId
LEFT JOIN otpusk.tRefsType fb ON f.fRefTypeID = fb.rec_id
WHERE
c.rec_id = :country
AND f.fLang = :lang
AND fb.fName = :type
AND fb.fBindType = :bind
ORDER BY
f.fSort DESC';
$stmt = $conn->prepare($sql);
foreach ($params as $index => $param) {
$stmt->bindValue($index, $param);
}
return $stmt->execute()->fetchAll();
}
public function getCountriesDescription($arr, $type, $locale = 'ru', $dbNormalizer)
{
$countryIds = [0];
foreach ($arr as $one) {
$countryIds[] = $one['id'];
}
$params = array(
'type' => $type,
'bind' => 'country',
'lang' => $dbNormalizer['tRefs']['language'][$locale]
);
$conn = $this->getEntityManager()
->getConnection();
$sql = 'SELECT
c.rec_id AS id,
f.fText AS text
FROM
otpusk.tCountries c
LEFT JOIN otpusk.tRefs f ON c.rec_id = f.fBindId
LEFT JOIN otpusk.tRefsType fb ON f.fRefTypeID = fb.rec_id
WHERE
c.rec_id IN ('.implode(',',$countryIds).')
AND f.fLang = :lang
AND fb.fName = :type
AND fb.fBindType = :bind
ORDER BY
f.fSort DESC';
$stmt = $conn->prepare($sql);
foreach ($params as $index => $param) {
$stmt->bindValue($index, $param);
}
$result = $stmt->execute()->fetchAll();
foreach ($arr as $key => $one) {
$arr[$key]['description'] = null;
$found_key = array_search($one['id'], array_column($result, 'id'));
if (false !== $found_key) $arr[$key]['description'] = $result[$found_key];
}
return $arr;
}
public function getCountryFaqNew($countryId, $locale = 'ru', $season = null, $month = null, $isDeparture = false, $cityFrom, $isRubric = false, $dbNormalizer)
{
$code = (!$isRubric) ? 'countrytours' : 'countryresorts';
if ($isDeparture) $code .= 'departurecity';
if (!is_null($season)) $code .= $season;
if (!is_null($month)) $code .= $month;
$params = array(
'country' => $countryId,
'code' => $code,
'lang' => $dbNormalizer['tRefs']['language'][$locale]
);
$conn = $this->getEntityManager()
->getConnection();
$sql = 'SELECT
f.fTitle AS title,
f.fText AS text
FROM
otpusk.tCountries c
LEFT JOIN otpusk.tRefs f ON c.rec_id = f.fBindId
LEFT JOIN otpusk.tRefsType fb ON f.fRefTypeID = fb.rec_id
WHERE
c.rec_id = :country
AND f.fLang = :lang
AND fb.fCode = :code';
if ($isDeparture) {
$sql .= ' AND f.fFromCityId = :cityFrom';
$params['cityFrom'] = $cityFrom;
}
$sql .= ' ORDER BY f.fSort DESC';
$stmt = $conn->prepare($sql);
foreach ($params as $index => $param) {
$stmt->bindValue($index, $param);
}
$result = $stmt->execute()->fetchAll();
if (!empty($result)) return $result[0];
return null;
}
public function getByCountryAliases($dbNormalizer, $locale, string $countryAlias)
{
$name = $dbNormalizer['country']['case'][$locale];
$country = $this->createQueryBuilder('country')
->select('country.'.$name.'Rd as name')
->where('country.fNameTr = :countryAlias')
->orWhere('country.fNameTr = :countryAliasChanged')
->orWhere('country.fNameTr = :countryAliasChangedA')
->setParameter('countryAlias', $countryAlias)
->setParameter('countryAliasChanged', $countryAlias)
->setParameter('countryAliasChangedA', str_replace('_', ' ', $countryAlias))
->getQuery()
->getOneOrNullResult();
return $country;
}
/**
* @param int|null $countryId
* @param string $type
* @return array
*/
public function getTemperaturesByCountry($countryId)
{
$params = array(
'countryId' => $countryId,
);
$conn = $this->getEntityManager()
->getConnection();
$sql = 'SELECT t.temperatureType as type, AVG(t.jan) as Jan, AVG(t.feb) as Feb, AVG(t.mar) as Mar, AVG(t.apr) as Apr, AVG(t.may) as May, AVG(t.jun) as Jun, AVG(t.jul) as Jul, AVG(t.aug) as Aug, AVG(t.sep) as Sep, AVG(t.oct) as Oct, AVG(t.nov) as Nov, AVG(t.decm) as "Dec"
FROM otp_misc.temperatures t
WHERE t.countryId = :countryId
GROUP BY t.temperatureType';
$stmt = $conn->prepare($sql);
foreach ($params as $index => $param) {
$stmt->bindValue($index, $param);
}
return $stmt->execute()->fetchAll();
}
public function getByCategory($market, $category, $locale = 'ru')
{
$sql = $this->createQueryBuilder('c')
->join('c.countryCategoryValues', 'cv')
->leftJoin('cv.category', 'cat')
->where('cat.name = :category')
->andWhere('cv.market = :marketId')
->setParameter('category', $category)
->setParameter('marketId', $market['id'])
->orderBy('cv.position')
;
return $sql->getQuery()->getResult();
}
public function getExcursions()
{
$conn = $this->getEntityManager()
->getConnection();
$sql = 'SELECT c2.* FROM otp_tours.cruiseCategories c1
LEFT JOIN otp_tours.cruiseCategories c2 ON (c2.parentId=c1.id AND c2.isActive=1 AND c2.toShow=1)
WHERE c1.isActive=1 AND c1.name="Достопримечательности"
ORDER BY c2.position';
$stmt = $conn->prepare($sql);
$results = $stmt->execute()->fetchAll();
foreach ($results as $key => $result) {
$results[$key]['image'] = null;
$options = \json_decode($result['options'], 1);
if ($options && isset($options['catImage'])) $results[$key]['image'] = 'https://new.otpusk.com/img/category/'.$options['catImage'];
}
return $results;
}
public function getSimilarById($countryId, $locale = 'ru', $dbNormalizer)
{
$name = $dbNormalizer['country']['name'][$locale];
$result = $this->createQueryBuilder('c')
->select('cc.id, cc.'.$name.' as name, cc.fNameTr as slug, cc.image as image')
->join('c.similarCountries', 's')
->join('s.countrySimilarIn', 'cc')
->where('c.id = :countryId')
->setParameter('countryId', $countryId)
->orderBy('s.position', 'DESC')
->getQuery()
->getResult()
;
foreach ($result as $k => $v) {
$result[$k]['image'] = $this->getImagePath($v['image']);
}
return $result;
}
public function getImagePath($image)
{
if (!is_null($image)) {
return $this->imageSource.sprintf(
'%02d/%02d/%02d/%02d/',
($image / 100000000) % 100, ($image / 1000000) % 100, ($image / 10000) % 100, ($image / 100) % 100
) . $image . '.webp';
}
return null;
}
public function getCountryByIp($ip = null)
{
if (empty($ip)){
$ip = $_SERVER['REMOTE_ADDR'];
}
$project_dir = dirname($_SERVER['DOCUMENT_ROOT']);
if (!is_dir($project_dir)) $project_dir = '/app';
$reader = new Reader($project_dir.'/public/upload/GeoLite2/GeoLite2-Country/GeoLite2-Country.mmdb');
$record = $reader->country($ip);
$this->res = [
[$record->continent->names['ru'],$record->continent->names['en'], $record->continent->code],
[$record->country->names['ru'], $record->country->names['en'], $record->country->isoCode],
];
return $this->getIdByCountryName(
$record->country->isoCode,
$record->country->names['ru'],
$record->country->names['en'],
);
}
public function getIdByCountryName($code, $name, $nameEng)
{
$sql = $this->createQueryBuilder('c')
->where('c.fCountry = :name')
->orWhere('c.nameEng = :nameEng')
->orWhere('c.fCode = :code')
->orWhere('c.fIATA = :code')
->setParameter('name', $name)
->setParameter('nameEng', $nameEng)
->setParameter('code', $code)
;
$country = $sql->getQuery()->getResult();
return isset($country[0])?$country[0]->getId():null;
}
public function getRes(){
return $this->res;
}
}

src/Repository/Otpusk/CountryRepository.php line 203