+ //Return return
+ return $return;
+ }
+
+ /**
+ * 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');
+
+ //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('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);
+
+ //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');
+
+ //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');
+
Rapsys Git / airbundle / blobdiff