X-Git-Url: https://git.rapsys.eu/airbundle/blobdiff_plain/b27f01bf7d1af9cd1bdd4b39ed1718b434b136db..HEAD:/Repository/LocationRepository.php diff --git a/Repository/LocationRepository.php b/Repository/LocationRepository.php index 234d0e2..9b621ef 100644 --- a/Repository/LocationRepository.php +++ b/Repository/LocationRepository.php @@ -1,85 +1,761 @@ -<?php +<?php declare(strict_types=1); + +/* + * This file is part of the Rapsys AirBundle package. + * + * (c) Raphaël Gertz <symfony@rapsys.eu> + * + * For the full copyright and license information, please view the LICENSE + * file that was distributed with this source code. + */ namespace Rapsys\AirBundle\Repository; -use Symfony\Component\Translation\TranslatorInterface; +use Doctrine\ORM\Query\ResultSetMapping; + +use Symfony\Component\Routing\Generator\UrlGeneratorInterface; +use Symfony\Component\Routing\RouterInterface; + +use Rapsys\AirBundle\Repository; /** * LocationRepository + * + * @TODO: use new window function syntax https://mariadb.com/kb/en/window-functions-overview/ MAX(updated) OVER (PARTITION updated) AS modified ??? */ -class LocationRepository extends \Doctrine\ORM\EntityRepository { +class LocationRepository extends Repository { /** - * Find complementary locations by session id + * Find locations * - * @param $id The session id - * @return array The other locations + * @return array */ - public function findComplementBySessionId($id) { - //Fetch complement locations - $ret = $this->getEntityManager() - ->createQuery('SELECT l.id, l.title FROM RapsysAirBundle:Session s LEFT JOIN RapsysAirBundle:Session s2 WITH s2.id != s.id AND s2.slot = s.slot AND s2.date = s.date LEFT JOIN RapsysAirBundle:Location l WITH l.id != s.location AND (l.id != s2.location OR s2.location IS NULL) WHERE s.id = :sid GROUP BY l.id ORDER BY l.id') - ->setParameter('sid', $id) + public function findAll(): array { + //Get all locations index by id + return $this->createQueryBuilder('location', 'location.id')->getQuery()->getResult(); + } + + /** + * Find locations as array + * + * @param DatePeriod $period The period + * @return array The locations array + */ + public function findAllAsArray(\DatePeriod $period): array { + //Set the request + //TODO: ajouter pays ??? + $req = <<<SQL +SELECT + l.id, + l.title, + l.latitude, + l.longitude, + l.indoor, + l.updated +FROM Rapsys\AirBundle\Entity\Location AS l +LEFT JOIN Rapsys\AirBundle\Entity\Session AS s ON (l.id = s.location_id) +GROUP BY l.id +ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, COUNT(s.id) DESC, l.id +SQL; + + //Replace bundle entity name by table name + $req = str_replace($this->tableKeys, $this->tableValues, $req); + + //Get result set mapping instance + //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php + $rsm = new ResultSetMapping(); + + //Declare all fields + //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php + //addScalarResult($sqlColName, $resColName, $type = 'string'); + $rsm->addScalarResult('id', 'id', 'integer') + ->addScalarResult('title', 'title', 'string') + ->addScalarResult('latitude', 'latitude', 'float') + ->addScalarResult('longitude', 'longitude', 'float') + ->addScalarResult('indoor', 'indoor', 'boolean') + ->addScalarResult('count', 'count', 'integer') + ->addScalarResult('updated', 'updated', 'datetime'); + + //Get result + $result = $this->_em + ->createNativeQuery($req, $rsm) + ->setParameter('begin', $period->getStartDate()) + ->setParameter('end', $period->getEndDate()) ->getArrayResult(); - //Rekey array - $ret = array_column($ret, 'id', 'title'); + //Set return + $return = []; + + //Iterate on each city + foreach($result as $data) { + //Add to return + $return[] = [ + 'id' => $data['id'], + 'title' => $title = $this->translator->trans($data['title']), + 'latitude' => $data['latitude'], + 'longitude' => $data['longitude'], + 'updated' => $data['updated'], + //XXX: Useless ??? + 'slug' => $location = $this->slugger->slug($title), + 'link' => $this->router->generate('rapsysair_location_view', ['id' => $data['id'], 'location' => $this->slugger->slug($location)]) + ]; + } - return $ret; + //Return return + return $return; } /** - * Fetch translated location with session by date period + * Find cities as array * - * @param $translator The TranslatorInterface instance - * @param $period The date period - * @param $granted The session is granted + * @param DatePeriod $period The period + * @param int $count The session count + * @return array The cities array */ - public function fetchTranslatedLocationByDatePeriod(TranslatorInterface $translator, $period, $granted = false) { - //Fetch sessions - $ret = $this->getEntityManager() - ->createQuery('SELECT l.id, l.title FROM RapsysAirBundle:Session s JOIN RapsysAirBundle:Location l WHERE '.($granted?'s.application IS NOT NULL AND ':'').'l.id = s.location AND s.date BETWEEN :begin AND :end GROUP BY l.id ORDER BY l.id') + public function findCitiesAsArray(\DatePeriod $period, int $count = 1): array { + //Set the request + $req = <<<SQL +SELECT + SUBSTRING(a.zipcode, 1, 2) AS id, + a.city AS city, + ROUND(AVG(a.latitude), 6) AS latitude, + ROUND(AVG(a.longitude), 6) AS longitude, + GROUP_CONCAT(a.id ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS ids, + GROUP_CONCAT(a.title ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS titles, + GROUP_CONCAT(a.latitude ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS latitudes, + GROUP_CONCAT(a.longitude ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS longitudes, + GROUP_CONCAT(a.indoor ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS indoors, + GROUP_CONCAT(a.count ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS counts, + MAX(a.modified) AS modified +FROM ( + SELECT + l.id, + l.city, + l.title, + l.latitude, + l.longitude, + l.indoor, + GREATEST(l.created, l.updated, COALESCE(s.created, '1970-01-01'), COALESCE(s.updated, '1970-01-01')) AS modified, + l.zipcode, + COUNT(s.id) AS count, + COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) AS pcount + FROM Rapsys\AirBundle\Entity\Location AS l + LEFT JOIN Rapsys\AirBundle\Entity\Session AS s ON (l.id = s.location_id) + GROUP BY l.id + ORDER BY NULL + LIMIT 0, :limit +) AS a +GROUP BY a.city, SUBSTRING(a.zipcode, 1, 2) +ORDER BY a.city, a.zipcode +SQL; + + //Replace bundle entity name by table name + $req = str_replace($this->tableKeys, $this->tableValues, $req); + + //Get result set mapping instance + //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php + $rsm = new ResultSetMapping(); + + //Declare all fields + //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php + //addScalarResult($sqlColName, $resColName, $type = 'string'); + $rsm->addScalarResult('id', 'id', 'integer') + ->addScalarResult('city', 'city', 'string') + ->addScalarResult('latitude', 'latitude', 'float') + ->addScalarResult('longitude', 'longitude', 'float') + //XXX: is a string because of \n separator + ->addScalarResult('ids', 'ids', 'string') + //XXX: is a string because of \n separator + ->addScalarResult('titles', 'titles', 'string') + //XXX: is a string because of \n separator + ->addScalarResult('latitudes', 'latitudes', 'string') + //XXX: is a string because of \n separator + ->addScalarResult('longitudes', 'longitudes', 'string') + //XXX: is a string because of \n separator + ->addScalarResult('indoors', 'indoors', 'string') + //XXX: is a string because of \n separator + ->addScalarResult('counts', 'counts', 'string') + ->addScalarResult('modified', 'modified', 'datetime') + ->addIndexByScalar('city'); + + //Get result + $result = $this->_em + ->createNativeQuery($req, $rsm) ->setParameter('begin', $period->getStartDate()) ->setParameter('end', $period->getEndDate()) - ->getResult(); + ->getArrayResult(); + + //Set return + $return = []; + + //Iterate on each city + foreach($result as $city => $data) { + //Set titles + $titles = explode("\n", $data['titles']); + + //Set latitudes + $latitudes = explode("\n", $data['latitudes']); + + //Set longitudes + $longitudes = explode("\n", $data['longitudes']); - //Rekey array - $ret = array_column($ret, 'title', 'id'); + //Set indoors + $indoors = explode("\n", $data['indoors']); - //Filter array - foreach($ret as $k => $v) { - $ret[$k] = $translator->trans($v); + //Set counts + $counts = explode("\n", $data['counts']); + + //With unsufficient count + if ($count && $counts[0] < $count) { + //Skip empty city + //XXX: count are sorted so only check first + continue; + } + + //Set locations + $data['locations'] = []; + + //Iterate on each location + foreach(explode("\n", $data['ids']) as $k => $id) { + //With unsufficient count + if ($count && $counts[$k] < $count) { + //Skip empty city + //XXX: count are sorted so only check first + continue; + } + + //Add location + $data['locations'][] = [ + 'id' => $id, + 'title' => $location = $this->translator->trans($titles[$k]), + 'latitude' => floatval($latitudes[$k]), + 'longitude' => floatval($longitudes[$k]), + 'indoor' => $indoors[$k] == 0 ? $this->translator->trans('outdoor') : $this->translator->trans('indoor'), + 'link' => $this->router->generate('rapsysair_location_view', ['id' => $id, 'location' => $this->slugger->slug($location)]) + ]; + } + + //Add to return + $return[$city] = [ + 'id' => $data['id'], + 'city' => $data['city'], + 'in' => $this->translator->trans('in '.$data['city']), + 'indoors' => array_map(function ($v) { return $v == 0 ? $this->translator->trans('outdoor') : $this->translator->trans('indoor'); }, array_unique($indoors)), + 'multimap' => $this->translator->trans($data['city'].' sector map'), + 'latitude' => $data['latitude'], + 'longitude' => $data['longitude'], + 'modified' => $data['modified'], + //XXX: Useless ??? + 'slug' => $city = $this->slugger->slug($data['city']), + 'link' => $this->router->generate('rapsysair_city_view', ['city' => $city, 'latitude' => $data['latitude'], 'longitude' => $data['longitude']]), + 'locations' => $data['locations'] + ]; } - //Send result - return $ret; + //Return return + return $return; } /** - * Fetch translated user location with session by date period + * Find city by latitude and longitude as array * - * @param $translator The TranslatorInterface instance - * @param $period The date period - * @param $userId The user uid + * @param float $latitude The latitude + * @param float $longitude The longitude + * @return ?array The cities array */ - public function fetchTranslatedUserLocationByDatePeriod(TranslatorInterface $translator, $period, $userId) { - //Fetch sessions - $ret = $this->getEntityManager() - ->createQuery('SELECT l.id, l.title FROM RapsysAirBundle:Application a JOIN RapsysAirBundle:Session s JOIN RapsysAirBundle:Location l WHERE a.user = :uid AND a.session = s.id AND s.date BETWEEN :begin AND :end AND s.location = l.id GROUP BY l.id ORDER BY l.id') -#SELECT l.id, l.title FROM RapsysAirBundle:Session s JOIN RapsysAirBundle:Application a JOIN RapsysAirBundle:Location l WHERE s.date BETWEEN :begin AND :end AND s.id = a.session AND l.id = s.location GROUP BY l.id ORDER BY l.id + public function findCityByLatitudeLongitudeAsArray(float $latitude, float $longitude): ?array { + //Set the request + $req = <<<SQL +SELECT + SUBSTRING(l.zipcode, 1, 2) AS id, + l.city AS city, + ROUND(AVG(l.latitude), 6) AS latitude, + ROUND(AVG(l.longitude), 6) AS longitude, + MAX(l.updated) AS updated +FROM Rapsys\AirBundle\Entity\Location AS l +GROUP BY city, SUBSTRING(l.zipcode, 1, 2) +ORDER BY ACOS(SIN(RADIANS(:latitude))*SIN(RADIANS(l.latitude))+COS(RADIANS(:latitude))*COS(RADIANS(l.latitude))*COS(RADIANS(:longitude - l.longitude)))*40030.17/2/PI() +LIMIT 0, 1 +SQL; + + //Replace bundle entity name by table name + $req = str_replace($this->tableKeys, $this->tableValues, $req); + + //Get result set mapping instance + //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php + $rsm = new ResultSetMapping(); + + //Declare all fields + //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php + //addScalarResult($sqlColName, $resColName, $type = 'string'); + $rsm->addScalarResult('id', 'id', 'integer') + ->addScalarResult('city', 'city', 'string') + ->addScalarResult('latitude', 'latitude', 'float') + ->addScalarResult('longitude', 'longitude', 'float') + ->addScalarResult('updated', 'updated', 'datetime') + ->addIndexByScalar('city'); + + //Get result + $result = $this->_em + ->createNativeQuery($req, $rsm) + ->setParameter('latitude', $latitude) + ->setParameter('longitude', $longitude) + ->getOneOrNullResult(); + + //Without result + if ($result === null) { + //Return result + return $result; + } + + //Return result + return [ + 'id' => $result['id'], + 'city' => $result['city'], + 'latitude' => $result['latitude'], + 'longitude' => $result['longitude'], + 'updated' => $result['updated'], + 'in' => $this->translator->trans('in '.$result['city']), + 'multimap' => $this->translator->trans($result['city'].' sector map'), + //XXX: Useless ??? + 'slug' => $slug = $this->slugger->slug($result['city']), + 'link' => $this->router->generate('rapsysair_city_view', ['city' => $slug, 'latitude' => $result['latitude'], 'longitude' => $result['longitude']]) + ]; + } + + /** + * Find locations by latitude and longitude sorted by period as array + * + * @TODO: find all other locations when current one has no sessions ??? + * + * @param float $latitude The latitude + * @param float $longitude The longitude + * @param DatePeriod $period The period + * @param int $count The session count + * @param float $distance The distance + * @return array The locations array + */ + public function findAllByLatitudeLongitudeAsArray(float $latitude, float $longitude, \DatePeriod $period, int $count = 1, float $distance = 15): array { + //Set earth radius + $radius = 40030.17/2/pi(); + + //Compute min latitude + $minlat = min(rad2deg(asin(sin(deg2rad($latitude))*cos($distance/$radius) + cos(deg2rad($latitude))*sin($distance/$radius)*cos(deg2rad(180)))), $latitude); + + //Compute max latitude + $maxlat = max(rad2deg(asin(sin(deg2rad($latitude))*cos($distance/$radius) + cos(deg2rad($latitude))*sin($distance/$radius)*cos(deg2rad(0)))), $latitude); + + //Compute min longitude + $minlong = fmod((rad2deg((deg2rad($longitude) + atan2(sin(deg2rad(-90))*sin($distance/$radius)*cos(deg2rad($minlat)), cos($distance/$radius) - sin(deg2rad($minlat)) * sin(deg2rad($minlat))))) + 180), 360) - 180; + + //Compute max longi + $maxlong = fmod((rad2deg((deg2rad($longitude) + atan2(sin(deg2rad(90))*sin($distance/$radius)*cos(deg2rad($maxlat)), cos($distance/$radius) - sin(deg2rad($maxlat)) * sin(deg2rad($maxlat))))) + 180), 360) - 180; + + //Set the request + //TODO: see old request before commit to sort session count, distance and then by id ? + //TODO: see to sort by future session count, historical session count, distance and then by id ? + //TODO: do the same for cities and city ? + $req = <<<SQL +SELECT + a.id, + a.title, + a.latitude, + a.longitude, + a.created, + a.updated, + MAX(GREATEST(a.modified, COALESCE(s.created, '1970-01-01'), COALESCE(s.updated, '1970-01-01'))) AS modified, + COUNT(s.id) AS count +FROM ( + SELECT + l.id, + l.title, + l.latitude, + l.longitude, + l.created, + l.updated, + GREATEST(l.created, l.updated) AS modified + FROM Rapsys\AirBundle\Entity\Location AS l + WHERE l.latitude BETWEEN :minlat AND :maxlat AND l.longitude BETWEEN :minlong AND :maxlong + LIMIT 0, :limit +) AS a +LEFT JOIN Rapsys\AirBundle\Entity\Session s ON (s.location_id = a.id) +GROUP BY a.id +ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, count DESC, a.id +SQL; + + //Replace bundle entity name by table name + $req = str_replace($this->tableKeys, $this->tableValues, $req); + + //Get result set mapping instance + //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php + $rsm = new ResultSetMapping(); + + //Declare all fields + //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php + //addScalarResult($sqlColName, $resColName, $type = 'string'); + $rsm->addScalarResult('id', 'id', 'integer') + ->addScalarResult('title', 'title', 'string') + ->addScalarResult('latitude', 'latitude', 'float') + ->addScalarResult('longitude', 'longitude', 'float') + ->addScalarResult('created', 'created', 'datetime') + ->addScalarResult('updated', 'updated', 'datetime') + ->addScalarResult('modified', 'modified', 'datetime') + ->addScalarResult('count', 'count', 'integer'); + + //Get result + $result = $this->_em + ->createNativeQuery($req, $rsm) ->setParameter('begin', $period->getStartDate()) ->setParameter('end', $period->getEndDate()) - ->setParameter('uid', $userId) - ->getResult(); + ->setParameter('minlat', $minlat) + ->setParameter('maxlat', $maxlat) + ->setParameter('minlong', $minlong) + ->setParameter('maxlong', $maxlong) + ->getArrayResult(); + + //Set return + $return = []; + + //Iterate on each location + foreach($result as $id => $data) { + //With active locations + if ($count && $data['count'] < $count) { + //Skip unactive locations + continue; + } + + //Add location + $return[$id] = [ + 'id' => $data['id'], + 'title' => $title = $this->translator->trans($data['title']), + 'latitude' => $data['latitude'], + 'longitude' => $data['longitude'], + 'created' => $data['created'], + 'updated' => $data['updated'], + 'modified' => $data['modified'], + 'count' => $data['count'], + 'slug' => $slug = $this->slugger->slug($title), + 'link' => $this->router->generate('rapsysair_location_view', ['id' => $data['id'], 'location' => $slug]) + ]; + } + + //Return return + return $return; + } + + /** + * Find locations by user id sorted by period as array + * + * @param int $userId The user id + * @param DatePeriod $period The period + * @return array The locations array + */ + public function findAllByUserIdAsArray(int $userId, \DatePeriod $period, $distance = 15): array { + //Set the request + //TODO: ajouter pays ??? + $req = <<<SQL +SELECT + a.id, + a.title, + a.city, + a.latitude, + a.longitude, + a.created, + a.updated, + MAX(GREATEST(a.modified, COALESCE(s3.created, '1970-01-01'), COALESCE(s3.updated, '1970-01-01'))) AS modified, + a.pcount, + COUNT(s3.id) AS tcount +FROM ( + SELECT + b.id, + b.title, + b.city, + b.latitude, + b.longitude, + b.created, + b.updated, + MAX(GREATEST(b.modified, COALESCE(s2.created, '1970-01-01'), COALESCE(s2.updated, '1970-01-01'))) AS modified, + COUNT(s2.id) AS pcount + FROM ( + SELECT + l2.id, + l2.city, + l2.title, + l2.latitude, + l2.longitude, + l2.created, + l2.updated, + GREATEST(l2.created, l2.updated) AS modified + FROM ( + SELECT + l.id, + l.latitude, + l.longitude + FROM applications AS a + JOIN sessions AS s ON (s.id = a.session_id) + JOIN locations AS l ON (l.id = s.location_id) + WHERE a.user_id = :id + GROUP BY l.id + ORDER BY NULL + LIMIT 0, :limit + ) AS a + JOIN locations AS l2 + WHERE ACOS(SIN(RADIANS(a.latitude))*SIN(RADIANS(l2.latitude))+COS(RADIANS(a.latitude))*COS(RADIANS(l2.latitude))*COS(RADIANS(a.longitude - l2.longitude)))*40030.17/2/PI() BETWEEN 0 AND :distance + GROUP BY l2.id + ORDER BY NULL + LIMIT 0, :limit + ) AS b + LEFT JOIN sessions AS s2 ON (s2.location_id = b.id AND s2.date BETWEEN :begin AND :end) + GROUP BY b.id + ORDER BY NULL + LIMIT 0, :limit +) AS a +LEFT JOIN sessions AS s3 ON (s3.location_id = a.id) +GROUP BY a.id +ORDER BY pcount DESC, tcount DESC, a.id +SQL; + + //Replace bundle entity name by table name + $req = str_replace($this->tableKeys, $this->tableValues, $req); + + //Get result set mapping instance + //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php + $rsm = new ResultSetMapping(); + + //Declare all fields + //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php + //addScalarResult($sqlColName, $resColName, $type = 'string'); + $rsm->addScalarResult('id', 'id', 'integer') + ->addScalarResult('title', 'title', 'string') + ->addScalarResult('city', 'city', 'string') + ->addScalarResult('latitude', 'latitude', 'float') + ->addScalarResult('longitude', 'longitude', 'float') + ->addScalarResult('created', 'created', 'datetime') + ->addScalarResult('updated', 'updated', 'datetime') + ->addScalarResult('modified', 'modified', 'datetime') + ->addScalarResult('pcount', 'pcount', 'integer') + ->addScalarResult('tcount', 'tcount', 'integer'); + + //Get result + $result = $this->_em + ->createNativeQuery($req, $rsm) + ->setParameter('begin', $period->getStartDate()) + ->setParameter('end', $period->getEndDate()) + ->setParameter('id', $userId) + ->setParameter('distance', $distance) + ->getArrayResult(); + + //Set return + $return = []; + + //Iterate on each location + foreach($result as $id => $data) { + //With active locations + if (!empty($result[0]['tcount']) && empty($data['tcount'])) { + //Skip unactive locations + break; + } + + //Add location + $return[$id] = [ + 'id' => $data['id'], + 'city' => $data['city'], + 'title' => $title = $this->translator->trans($data['title']), + 'at' => $this->translator->trans('at '.$data['title']), + 'miniature' => $this->translator->trans($data['title'].' miniature'), + 'latitude' => $data['latitude'], + 'longitude' => $data['longitude'], + 'created' => $data['created'], + 'updated' => $data['updated'], + 'modified' => $data['modified'], + 'pcount' => $data['pcount'], + 'tcount' => $data['tcount'], + 'slug' => $slug = $this->slugger->slug($title), + 'link' => $this->router->generate('rapsysair_location_view', ['id' => $data['id'], 'location' => $slug]) + ]; + } + + //Return return + return $return; + } + + /** + * Find location as array by id + * + * @param int $id The location id + * @param string $locale The locale + * @return array The location data + */ + public function findOneByIdAsArray(int $id, string $locale): ?array { + //Set the request + $req = <<<SQL +SELECT + l.id, + l.title, + l.city, + l.latitude, + l.longitude, + l.indoor, + l.zipcode, + MAX(GREATEST(l.created, l.updated, l2.created, l2.updated)) AS modified, + SUBSTRING(l.zipcode, 1, 2) AS city_id, + ROUND(AVG(l2.latitude), 6) AS city_latitude, + ROUND(AVG(l2.longitude), 6) AS city_longitude +FROM Rapsys\AirBundle\Entity\Location AS l +JOIN Rapsys\AirBundle\Entity\Location AS l2 ON (l2.city = l.city AND SUBSTRING(l2.zipcode, 1, 2) = SUBSTRING(l.zipcode, 1, 2)) +WHERE l.id = :id +GROUP BY l.id +LIMIT 0, 1 +SQL; + + //Replace bundle entity name by table name + $req = str_replace($this->tableKeys, $this->tableValues, $req); - //Rekey array - $ret = array_column($ret, 'title', 'id'); + //Get result set mapping instance + //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php + $rsm = new ResultSetMapping(); - //Filter array - foreach($ret as $k => $v) { - $ret[$k] = $translator->trans($v); + //Declare all fields + //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php + //addScalarResult($sqlColName, $resColName, $type = 'string'); + $rsm->addScalarResult('id', 'id', 'integer') + ->addScalarResult('title', 'title', 'string') + ->addScalarResult('city', 'city', 'string') + ->addScalarResult('latitude', 'latitude', 'float') + ->addScalarResult('longitude', 'longitude', 'float') + ->addScalarResult('indoor', 'indoor', 'boolean') + ->addScalarResult('zipcode', 'zipcode', 'string') + ->addScalarResult('modified', 'modified', 'datetime') + ->addScalarResult('city_id', 'city_id', 'integer') + ->addScalarResult('city_latitude', 'city_latitude', 'float') + ->addScalarResult('city_longitude', 'city_longitude', 'float') + ->addIndexByScalar('id'); + + //Get result + $result = $this->_em + ->createNativeQuery($req, $rsm) + ->setParameter('id', $id) + ->getOneOrNullResult(); + + //Without result + if ($result === null) { + //Return result + return $result; } + //Set alternates + $result['alternates'] = []; + + //Set route + $route = 'rapsysair_location_view'; + + //Set route params + $routeParams = ['id' => $id]; + + //Iterate on each languages + foreach($this->languages as $languageId => $language) { + //Without current locale + if ($languageId !== $locale) { + //Set titles + $titles = []; + + //Set route params locale + $routeParams['_locale'] = $languageId; + + //Set route params location + $routeParams['location'] = $this->slugger->slug($this->translator->trans($result['title'], [], null, $languageId)); + + //Iterate on each locales + foreach(array_keys($this->languages) as $other) { + //Without other locale + if ($other !== $languageId) { + //Set other locale title + $titles[$other] = $this->translator->trans($language, [], null, $other); + } + } + + //Add alternates locale + $result['alternates'][substr($languageId, 0, 2)] = $result['alternates'][str_replace('_', '-', $languageId)] = [ + 'absolute' => $this->router->generate($route, $routeParams, UrlGeneratorInterface::ABSOLUTE_URL), + 'relative' => $this->router->generate($route, $routeParams), + 'title' => implode('/', $titles), + 'translated' => $this->translator->trans($language, [], null, $languageId) + ]; + } + } + + //Return result + return [ + 'id' => $result['id'], + 'city' => [ + 'id' => $result['city_id'], + 'title' => $result['city'], + 'in' => $this->translator->trans('in '.$result['city']), + 'link' => $this->router->generate('rapsysair_city_view', ['city' => $result['city'], 'latitude' => $result['city_latitude'], 'longitude' => $result['city_longitude']]) + ], + 'title' => $title = $this->translator->trans($result['title']), + 'latitude' => $result['latitude'], + 'longitude' => $result['longitude'], + 'indoor' => $result['indoor'], + 'modified' => $result['modified'], + 'around' => $this->translator->trans('around '.$result['title']), + 'at' => $this->translator->trans('at '.$result['title']), + 'atin' => $this->translator->trans('at '.$result['title']).' '.$this->translator->trans('in '.$result['city']), + 'multimap' => $this->translator->trans($result['title'].' sector map'), + //XXX: Useless ??? + 'slug' => $slug = $this->slugger->slug($title), + 'link' => $this->router->generate($route, ['_locale' => $locale, 'location' => $slug]+$routeParams), + 'alternates' => $result['alternates'] + ]; + } + + /** + * Find complementary locations by session id + * + * @param int $id The session id + * @return array The other locations + */ + public function findComplementBySessionId(int $id): array { + //Fetch complement locations + return array_column( + $this->getEntityManager() + #->createQuery('SELECT l.id, l.title FROM Rapsys\AirBundle\Entity\Location l JOIN Rapsys\AirBundle\Entity\Session s WITH s.id = :sid LEFT JOIN Rapsys\AirBundle\Entity\Session s2 WITH s2.id != s.id AND s2.slot = s.slot AND s2.date = s.date WHERE l.id != s.location AND s2.location IS NULL GROUP BY l.id ORDER BY l.id') + ->createQuery('SELECT l.id, l.title FROM Rapsys\AirBundle\Entity\Session s LEFT JOIN Rapsys\AirBundle\Entity\Session s2 WITH s2.id != s.id AND s2.slot = s.slot AND s2.date = s.date LEFT JOIN Rapsys\AirBundle\Entity\Location l WITH l.id != s.location AND (l.id != s2.location OR s2.location IS NULL) WHERE s.id = :sid GROUP BY l.id ORDER BY l.id') + ->setParameter('sid', $id) + ->getArrayResult(), + 'id', + 'title' + ); + } + + /** + * Find locations by user id + * + * @param int $id The user id + * @return array The user locations + */ + public function findByUserId(int $userId): array { + //Set the request + $req = 'SELECT l.id, l.title +FROM Rapsys\AirBundle\Entity\UserLocation AS ul +JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = ul.location_id) +WHERE ul.user_id = :id'; + + //Replace bundle entity name by table name + $req = str_replace($this->tableKeys, $this->tableValues, $req); + + //Get result set mapping instance + //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php + $rsm = new ResultSetMapping(); + + //Declare result set for our request + $rsm->addEntityResult('Rapsys\AirBundle\Entity\Location', 'l') + ->addFieldResult('l', 'id', 'id') + ->addFieldResult('l', 'title', 'title'); + //Send result - return $ret; + return $this->_em + ->createNativeQuery($req, $rsm) + ->setParameter('id', $userId) + ->getResult(); } }