]> Raphaël G. Git Repositories - airbundle/blobdiff - Repository/LocationRepository.php
Rapsys Git / airbundle / blobdiff
? search:
summary | shortlog | log | commit | commitdiff | tree
raw | inline | side by side
Rename rapsysair:calendar2 command to rapsysair:calendar
[airbundle] / Repository / LocationRepository.php
diff --git a/Repository/LocationRepository.php b/Repository/LocationRepository.php
index a7b351ae0ad3e599baefaa5ed433ceeb11f21ef1..9b621ef3cafe2b073c511ee42867c2bd665bf8e4 100644 (file)
--- a/Repository/LocationRepository.php
+++ b/Repository/LocationRepository.php
@@ -1,128 +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;
 
 
 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
 
 /**
  * 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();
 
                        ->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;
        }
 
        /**
        }
 
        /**
-        * Find translated location title sorted 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 findTranslatedSortedByPeriod(TranslatorInterface $translator, $period, $userId = null) {
-               //Fetch sessions
-               $ret = $this->getEntityManager()
-                       ->createQuery(
-'SELECT l.id, l.title
-FROM RapsysAirBundle:Location l
-LEFT JOIN RapsysAirBundle:Session s WITH s.location = l.id AND s.date BETWEEN :begin AND :end
-LEFT JOIN RapsysAirBundle:Application a WITH a.id = s.application'.(!empty($userId)?' AND a.user = :uid':'').'
-GROUP BY l.id
-ORDER BY '.(!empty($userId)?'COUNT(a.id) DESC, ':'').'COUNT(s.id) DESC, 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('begin', $period->getStartDate())
-                       ->setParameter('end', $period->getEndDate());
+                       ->setParameter('end', $period->getEndDate())
+                       ->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']);
+
+                       //Set indoors
+                       $indoors = explode("\n", $data['indoors']);
+
+                       //Set counts
+                       $counts = explode("\n", $data['counts']);
 
 
-               //Set optional user id
-               if (!empty($userId)) {
-                       $ret->setParameter('uid', $userId);
+                       //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']
+                       ];
                }
 
                }
 
-               //Get Result
-               $ret = $ret->getResult();
+               //Return return
+               return $return;
+       }
 
 
-               //Rekey array
-               $ret = array_column($ret, 'title', 'id');
+       /**
+        * Find city by latitude and longitude as array
+        *
+        * @param float $latitude The latitude
+        * @param float $longitude The longitude
+        * @return ?array The cities array
+        */
+       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');
 
 
-               //Filter array
-               foreach($ret as $k => $v) {
-                       $ret[$k] = $translator->trans($v);
+               //Get result
+               $result = $this->_em
+                       ->createNativeQuery($req, $rsm)
+                       ->setParameter('latitude', $latitude)
+                       ->setParameter('longitude', $longitude)
+                       ->getOneOrNullResult();
+
+               //Without result
+               if ($result === null) {
+                       //Return result
+                       return $result;
                }
 
                }
 
-               //Send result
-               return $ret;
+               //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']])
+               ];
        }
 
        /**
        }
 
        /**
-        * Fetch translated location title with session by date period
+        * Find locations by latitude and longitude sorted by period as array
+        *
+        * @TODO: find all other locations when current one has no sessions ???
         *
         *
-        * @param $translator The TranslatorInterface instance
-        * @param $period The date period
-        * @param $granted The session is granted
-        * TODO: a dropper
+        * @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 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 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('begin', $period->getStartDate())
                        ->setParameter('end', $period->getEndDate())
-                       ->getResult();
+                       ->setParameter('minlat', $minlat)
+                       ->setParameter('maxlat', $maxlat)
+                       ->setParameter('minlong', $minlong)
+                       ->setParameter('maxlong', $maxlong)
+                       ->getArrayResult();
+
+               //Set return
+               $return = [];
 
 
-               //Rekey array
-               $ret = array_column($ret, 'title', 'id');
+               //Iterate on each location
+               foreach($result as $id => $data) {
+                       //With active locations
+                       if ($count && $data['count'] < $count) {
+                               //Skip unactive locations
+                               continue;
+                       }
 
 
-               //Filter array
-               foreach($ret as $k => $v) {
-                       $ret[$k] = $translator->trans($v);
+                       //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])
+                       ];
                }
 
                }
 
-               //Send result
-               return $ret;
+               //Return return
+               return $return;
        }
 
        /**
        }
 
        /**
-        * Fetch translated location title with user session by date period
+        * Find locations by user id sorted by period as array
         *
         *
-        * @param $translator The TranslatorInterface instance
-        * @param $period The date period
-        * @param $userId The user uid
-        * TODO: a dropper
+        * @param int $userId The user id
+        * @param DatePeriod $period The period
+        * @return array The locations 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 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('begin', $period->getStartDate())
                        ->setParameter('end', $period->getEndDate())
-                       ->setParameter('uid', $userId)
-                       ->getResult();
+                       ->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);
+
+               //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('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');
 
 
-               //Rekey array
-               $ret = array_column($ret, 'title', 'id');
+               //Get result
+               $result = $this->_em
+                       ->createNativeQuery($req, $rsm)
+                       ->setParameter('id', $id)
+                       ->getOneOrNullResult();
 
 
-               //Filter array
-               foreach($ret as $k => $v) {
-                       $ret[$k] = $translator->trans($v);
+               //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
                //Send result
-               return $ret;
+               return $this->_em
+                       ->createNativeQuery($req, $rsm)
+                       ->setParameter('id', $userId)
+                       ->getResult();
        }
 }
        }
 }
Symfony bundle rapsys/airbundle used to handle libre air features