1 <?php
declare(strict_types
=1);
4 * This file is part of the Rapsys AirBundle package.
6 * (c) Raphaël Gertz <symfony@rapsys.eu>
8 * For the full copyright and license information, please view the LICENSE
9 * file that was distributed with this source code.
12 namespace Rapsys\AirBundle\Repository
;
14 use Doctrine\ORM\Query\ResultSetMapping
;
16 use Symfony\Component\Routing\Generator\UrlGeneratorInterface
;
17 use Symfony\Component\Routing\RouterInterface
;
19 use Rapsys\AirBundle\Repository
;
24 * @TODO: use new window function syntax https://mariadb.com/kb/en/window-functions-overview/ MAX(updated) OVER (PARTITION updated) AS modified ???
26 class LocationRepository
extends Repository
{
32 public function findAll(): array {
33 //Get all locations index by id
34 return $this->createQueryBuilder('location', 'location.id')->getQuery()->getResult();
38 * Find locations as array
40 * @param DatePeriod $period The period
41 * @return array The locations array
43 public function findAllAsArray(\DatePeriod
$period): array {
45 //TODO: ajouter pays ???
54 FROM RapsysAirBundle:Location AS l
55 LEFT JOIN RapsysAirBundle:Session AS s ON (l.id = s.location_id)
57 ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, COUNT(s.id) DESC, l.id
60 //Replace bundle entity name by table name
61 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
63 //Get result set mapping instance
64 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
65 $rsm = new ResultSetMapping();
68 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
69 //addScalarResult($sqlColName, $resColName, $type = 'string');
70 $rsm->addScalarResult('id', 'id', 'integer')
71 ->addScalarResult('title', 'title', 'string')
72 ->addScalarResult('latitude', 'latitude', 'float')
73 ->addScalarResult('longitude', 'longitude', 'float')
74 ->addScalarResult('indoor', 'indoor', 'boolean')
75 ->addScalarResult('count', 'count', 'integer')
76 ->addScalarResult('updated', 'updated', 'datetime');
80 ->createNativeQuery($req, $rsm)
81 ->setParameter('begin', $period->getStartDate())
82 ->setParameter('end', $period->getEndDate())
88 //Iterate on each city
89 foreach($result as $data) {
93 'title' => $title = $this->translator
->trans($data['title']),
94 'latitude' => $data['latitude'],
95 'longitude' => $data['longitude'],
96 'updated' => $data['updated'],
98 'slug' => $location = $this->slugger
->slug($title),
99 'link' => $this->router
->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $this->slugger
->slug($location)])
108 * Find cities as array
110 * @param DatePeriod $period The period
111 * @param int $count The session count
112 * @return array The cities array
114 public function findCitiesAsArray(\DatePeriod
$period, int $count = 1): array {
118 SUBSTRING(a.zipcode, 1, 2) AS id,
120 ROUND(AVG(a.latitude), 6) AS latitude,
121 ROUND(AVG(a.longitude), 6) AS longitude,
122 GROUP_CONCAT(a.id ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS ids,
123 GROUP_CONCAT(a.title ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS titles,
124 GROUP_CONCAT(a.latitude ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS latitudes,
125 GROUP_CONCAT(a.longitude ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS longitudes,
126 GROUP_CONCAT(a.indoor ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS indoors,
127 GROUP_CONCAT(a.count ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS counts,
128 MAX(a.updated) AS modified
139 COUNT(s.id) AS count,
140 COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) AS pcount
141 FROM RapsysAirBundle:Location AS l
142 LEFT JOIN RapsysAirBundle:Session AS s ON (l.id = s.location_id)
147 GROUP BY a.city, SUBSTRING(a.zipcode, 1, 3)
148 ORDER BY a.city, SUBSTRING(a.zipcode, 1, 3)
151 //Replace bundle entity name by table name
152 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
154 //Get result set mapping instance
155 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
156 $rsm = new ResultSetMapping();
159 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
160 //addScalarResult($sqlColName, $resColName, $type = 'string');
161 $rsm->addScalarResult('id', 'id', 'integer')
162 ->addScalarResult('city', 'city', 'string')
163 ->addScalarResult('latitude', 'latitude', 'float')
164 ->addScalarResult('longitude', 'longitude', 'float')
165 //XXX: is a string because of \n separator
166 ->addScalarResult('ids', 'ids', 'string')
167 //XXX: is a string because of \n separator
168 ->addScalarResult('titles', 'titles', 'string')
169 //XXX: is a string because of \n separator
170 ->addScalarResult('latitudes', 'latitudes', 'string')
171 //XXX: is a string because of \n separator
172 ->addScalarResult('longitudes', 'longitudes', 'string')
173 //XXX: is a string because of \n separator
174 ->addScalarResult('indoors', 'indoors', 'string')
175 //XXX: is a string because of \n separator
176 ->addScalarResult('counts', 'counts', 'string')
177 ->addScalarResult('modified', 'modified', 'datetime')
178 ->addIndexByScalar('city');
182 ->createNativeQuery($req, $rsm)
183 ->setParameter('begin', $period->getStartDate())
184 ->setParameter('end', $period->getEndDate())
190 //Iterate on each city
191 foreach($result as $city => $data) {
193 $titles = explode("\n", $data['titles']);
196 $latitudes = explode("\n", $data['latitudes']);
199 $longitudes = explode("\n", $data['longitudes']);
202 $indoors = explode("\n", $data['indoors']);
205 $counts = explode("\n", $data['counts']);
207 //With unsufficient count
208 if ($count && $counts[0] < $count) {
210 //XXX: count are sorted so only check first
215 $data['locations'] = [];
217 //Iterate on each location
218 foreach(explode("\n", $data['ids']) as $k => $id) {
219 //With unsufficient count
220 if ($count && $counts[$k] < $count) {
222 //XXX: count are sorted so only check first
227 $data['locations'][] = [
229 'title' => $location = $this->translator
->trans($titles[$k]),
230 'latitude' => floatval($latitudes[$k]),
231 'longitude' => floatval($longitudes[$k]),
232 'indoor' => $indoors[$k] == 0 ? $this->translator
->trans('outdoor') : $this->translator
->trans('indoor'),
233 'link' => $this->router
->generate('rapsys_air_location_view', ['id' => $id, 'location' => $this->slugger
->slug($location)])
240 'city' => $data['city'],
241 'in' => $this->translator
->trans('in '.$data['city']),
242 'indoors' => array_map(function ($v) { return $v
== 0 ? $this
->translator
->trans('outdoor') : $this
->translator
->trans('indoor'); }, array_unique($indoors)),
243 'multimap' => $this->translator
->trans($data['city'].' sector map'),
244 'latitude' => $data['latitude'],
245 'longitude' => $data['longitude'],
246 'modified' => $data['modified'],
248 'slug' => $city = $this->slugger
->slug($data['city']),
249 'link' => $this->router
->generate('rapsys_air_city_view', ['city' => $city, 'latitude' => $data['latitude'], 'longitude' => $data['longitude']]),
250 'locations' => $data['locations']
259 * Find city by latitude and longitude as array
261 * @param float $latitude The latitude
262 * @param float $longitude The longitude
263 * @return ?array The cities array
265 public function findCityByLatitudeLongitudeAsArray(float $latitude, float $longitude): ?array {
269 SUBSTRING(l.zipcode, 1, 2) AS id,
271 ROUND(AVG(l.latitude), 6) AS latitude,
272 ROUND(AVG(l.longitude), 6) AS longitude,
273 MAX(l.updated) AS updated
274 FROM RapsysAirBundle:Location AS l
275 GROUP BY city, SUBSTRING(l.zipcode, 1, 3)
276 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()
280 //Replace bundle entity name by table name
281 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
283 //Get result set mapping instance
284 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
285 $rsm = new ResultSetMapping();
288 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
289 //addScalarResult($sqlColName, $resColName, $type = 'string');
290 $rsm->addScalarResult('id', 'id', 'integer')
291 ->addScalarResult('city', 'city', 'string')
292 ->addScalarResult('latitude', 'latitude', 'float')
293 ->addScalarResult('longitude', 'longitude', 'float')
294 ->addScalarResult('updated', 'updated', 'datetime')
295 ->addIndexByScalar('city');
299 ->createNativeQuery($req, $rsm)
300 ->setParameter('latitude', $latitude)
301 ->setParameter('longitude', $longitude)
302 ->getOneOrNullResult();
305 if ($result === null) {
312 'id' => $result['id'],
313 'city' => $result['city'],
314 'latitude' => $result['latitude'],
315 'longitude' => $result['longitude'],
316 'updated' => $result['updated'],
317 'in' => $this->translator
->trans('in '.$result['city']),
318 'multimap' => $this->translator
->trans($result['city'].' sector map'),
320 'slug' => $slug = $this->slugger
->slug($result['city']),
321 'link' => $this->router
->generate('rapsys_air_city_view', ['city' => $slug, 'latitude' => $result['latitude'], 'longitude' => $result['longitude']])
326 * Find locations by latitude and longitude sorted by period as array
328 * @TODO: find all other locations when current one has no sessions ???
330 * @param float $latitude The latitude
331 * @param float $longitude The longitude
332 * @param DatePeriod $period The period
333 * @param int $count The session count
334 * @param float $distance The distance
335 * @return array The locations array
337 public function findAllByLatitudeLongitudeAsArray(float $latitude, float $longitude, \DatePeriod
$period, int $count = 1, float $distance = 15): array {
339 $radius = 40030.17/2/pi();
341 //Compute min latitude
342 $minlat = min(rad2deg(asin(sin(deg2rad($latitude))*cos($distance/$radius) +
cos(deg2rad($latitude))*sin($distance/$radius)*cos(deg2rad(180)))), $latitude);
344 //Compute max latitude
345 $maxlat = max(rad2deg(asin(sin(deg2rad($latitude))*cos($distance/$radius) +
cos(deg2rad($latitude))*sin($distance/$radius)*cos(deg2rad(0)))), $latitude);
347 //Compute min longitude
348 $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;
351 $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;
354 //TODO: see old request before commit to sort session count, distance and then by id ?
355 //TODO: see to sort by future session count, historical session count, distance and then by id ?
356 //TODO: do the same for cities and city ?
364 MAX(a.updated) AS modified,
373 FROM RapsysAirBundle:Location AS l
374 WHERE l.latitude BETWEEN :minlat AND :maxlat AND l.longitude BETWEEN :minlong AND :maxlong
377 LEFT JOIN RapsysAirBundle:Session s ON (s.location_id = a.id)
379 ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, count DESC, a.id
382 //Replace bundle entity name by table name
383 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
385 //Get result set mapping instance
386 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
387 $rsm = new ResultSetMapping();
390 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
391 //addScalarResult($sqlColName, $resColName, $type = 'string');
392 $rsm->addScalarResult('id', 'id', 'integer')
393 ->addScalarResult('title', 'title', 'string')
394 ->addScalarResult('latitude', 'latitude', 'float')
395 ->addScalarResult('longitude', 'longitude', 'float')
396 ->addScalarResult('updated', 'updated', 'datetime')
397 ->addScalarResult('modified', 'modified', 'datetime')
398 ->addScalarResult('count', 'count', 'integer');
402 ->createNativeQuery($req, $rsm)
403 ->setParameter('begin', $period->getStartDate())
404 ->setParameter('end', $period->getEndDate())
405 ->setParameter('minlat', $minlat)
406 ->setParameter('maxlat', $maxlat)
407 ->setParameter('minlong', $minlong)
408 ->setParameter('maxlong', $maxlong)
414 //Iterate on each location
415 foreach($result as $id => $data) {
416 //With active locations
417 if ($count && $data['count'] < $count) {
418 //Skip unactive locations
425 'title' => $title = $this->translator
->trans($data['title']),
426 'latitude' => $data['latitude'],
427 'longitude' => $data['longitude'],
428 'updated' => $data['updated'],
429 'modified' => $data['modified'],
430 'count' => $data['count'],
431 'slug' => $slug = $this->slugger
->slug($title),
432 'link' => $this->router
->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $slug])
441 * Find locations by user id sorted by period as array
443 * @param int $userId The user id
444 * @param DatePeriod $period The period
445 * @return array The locations array
447 public function findAllByUserIdAsArray(int $userId, \DatePeriod
$period, $distance = 15): array {
449 //TODO: ajouter pays ???
460 COUNT(s3.id) AS tcount
469 COUNT(s2.id) AS pcount,
470 MAX(b.updated) AS modified
484 FROM applications AS a
485 JOIN sessions AS s ON (s.id = a.session_id)
486 JOIN locations AS l ON (l.id = s.location_id)
487 WHERE a.user_id = :id
493 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
498 LEFT JOIN sessions AS s2 ON (s2.location_id = b.id AND s2.date BETWEEN :begin AND :end)
503 LEFT JOIN sessions AS s3 ON (s3.location_id = a.id)
505 ORDER BY pcount DESC, tcount DESC, a.id
508 //Replace bundle entity name by table name
509 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
511 //Get result set mapping instance
512 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
513 $rsm = new ResultSetMapping();
516 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
517 //addScalarResult($sqlColName, $resColName, $type = 'string');
518 $rsm->addScalarResult('id', 'id', 'integer')
519 ->addScalarResult('title', 'title', 'string')
520 ->addScalarResult('city', 'city', 'string')
521 ->addScalarResult('latitude', 'latitude', 'float')
522 ->addScalarResult('longitude', 'longitude', 'float')
523 ->addScalarResult('updated', 'updated', 'datetime')
524 ->addScalarResult('pcount', 'pcount', 'integer')
525 ->addScalarResult('tcount', 'tcount', 'integer')
526 ->addScalarResult('modified', 'modified', 'datetime');
530 ->createNativeQuery($req, $rsm)
531 ->setParameter('begin', $period->getStartDate())
532 ->setParameter('end', $period->getEndDate())
533 ->setParameter('id', $userId)
534 ->setParameter('distance', $distance)
540 //Iterate on each location
541 foreach($result as $id => $data) {
542 //With active locations
543 if (!empty($result[0]['tcount']) && empty($data['tcount'])) {
544 //Skip unactive locations
551 'city' => $data['city'],
552 'title' => $title = $this->translator
->trans($data['title']),
553 'at' => $this->translator
->trans('at '.$data['title']),
554 'miniature' => $this->translator
->trans($data['title'].' miniature'),
555 'latitude' => $data['latitude'],
556 'longitude' => $data['longitude'],
557 'updated' => $data['updated'],
558 'pcount' => $data['pcount'],
559 'tcount' => $data['tcount'],
560 'modified' => $data['modified'],
561 'slug' => $slug = $this->slugger
->slug($title),
562 'link' => $this->router
->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $slug])
571 * Find location as array by id
573 * @param int $id The location id
574 * @param string $locale The locale
575 * @return array The location data
577 public function findOneByIdAsArray(int $id, string $locale): ?array {
588 MAX(l2.updated) AS updated,
589 SUBSTRING(l.zipcode, 1, 2) AS city_id,
590 ROUND(AVG(l2.latitude), 6) AS city_latitude,
591 ROUND(AVG(l2.longitude), 6) AS city_longitude
592 FROM RapsysAirBundle:Location AS l
593 JOIN RapsysAirBundle:Location AS l2 ON (l2.city = l.city AND SUBSTRING(l.zipcode, 1, 3) = SUBSTRING(l.zipcode, 1, 3))
599 //Replace bundle entity name by table name
600 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
602 //Get result set mapping instance
603 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
604 $rsm = new ResultSetMapping();
607 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
608 //addScalarResult($sqlColName, $resColName, $type = 'string');
609 $rsm->addScalarResult('id', 'id', 'integer')
610 ->addScalarResult('title', 'title', 'string')
611 ->addScalarResult('city', 'city', 'string')
612 ->addScalarResult('latitude', 'latitude', 'float')
613 ->addScalarResult('longitude', 'longitude', 'float')
614 ->addScalarResult('indoor', 'indoor', 'boolean')
615 ->addScalarResult('zipcode', 'zipcode', 'string')
616 ->addScalarResult('updated', 'updated', 'datetime')
617 ->addScalarResult('city_id', 'city_id', 'integer')
618 ->addScalarResult('city_latitude', 'city_latitude', 'float')
619 ->addScalarResult('city_longitude', 'city_longitude', 'float')
620 ->addIndexByScalar('id');
624 ->createNativeQuery($req, $rsm)
625 ->setParameter('id', $id)
626 ->getOneOrNullResult();
629 if ($result === null) {
635 $result['alternates'] = [];
638 $route = 'rapsys_air_location_view';
641 $routeParams = ['id' => $id];
643 //Iterate on each languages
644 foreach($this->languages
as $languageId => $language) {
645 //Without current locale
646 if ($languageId !== $locale) {
650 //Set route params locale
651 $routeParams['_locale'] = $languageId;
653 //Set route params location
654 $routeParams['location'] = $this->slugger
->slug($this->translator
->trans($result['title'], [], null, $languageId));
656 //Iterate on each locales
657 foreach(array_keys($this->languages
) as $other) {
658 //Without other locale
659 if ($other !== $languageId) {
660 //Set other locale title
661 $titles[$other] = $this->translator
->trans($language, [], null, $other);
665 //Add alternates locale
666 $result['alternates'][substr($languageId, 0, 2)] = $result['alternates'][str_replace('_', '-', $languageId)] = [
667 'absolute' => $this->router
->generate($route, $routeParams, UrlGeneratorInterface
::ABSOLUTE_URL
),
668 'relative' => $this->router
->generate($route, $routeParams),
669 'title' => implode('/', $titles),
670 'translated' => $this->translator
->trans($language, [], null, $languageId)
677 'id' => $result['id'],
679 'id' => $result['city_id'],
680 'title' => $result['city'],
681 'in' => $this->translator
->trans('in '.$result['city']),
682 'link' => $this->router
->generate('rapsys_air_city_view', ['city' => $result['city'], 'latitude' => $result['city_latitude'], 'longitude' => $result['city_longitude']])
684 'title' => $title = $this->translator
->trans($result['title']),
685 'latitude' => $result['latitude'],
686 'longitude' => $result['longitude'],
687 'indoor' => $result['indoor'],
688 'updated' => $result['updated'],
689 'around' => $this->translator
->trans('around '.$result['title']),
690 'at' => $this->translator
->trans('at '.$result['title']),
691 'atin' => $this->translator
->trans('at '.$result['title']).' '.$this->translator
->trans('in '.$result['city']),
692 'multimap' => $this->translator
->trans($result['title'].' sector map'),
694 'slug' => $slug = $this->slugger
->slug($title),
695 'link' => $this->router
->generate($route, ['_locale' => $locale, 'location' => $slug]+
$routeParams),
696 'alternates' => $result['alternates']
701 * Find complementary locations by session id
703 * @param int $id The session id
704 * @return array The other locations
706 public function findComplementBySessionId(int $id): array {
707 //Fetch complement locations
708 $ret = $this->getEntityManager()
709 ->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')
710 ->setParameter('sid', $id)
713 //TODO: try to improve with:
714 #->addIndexByScalar('city');
717 $ret = array_column($ret, 'id', 'title');
723 * Find locations by user id
725 * @param int $id The user id
726 * @return array The user locations
728 public function findByUserId(int $userId): array {
730 $req = 'SELECT l.id, l.title
731 FROM RapsysAirBundle:UserLocation AS ul
732 JOIN RapsysAirBundle:Location AS l ON (l.id = ul.location_id)
733 WHERE ul.user_id = :id';
735 //Replace bundle entity name by table name
736 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
738 //Get result set mapping instance
739 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
740 $rsm = new ResultSetMapping();
742 //Declare result set for our request
743 $rsm->addEntityResult('RapsysAirBundle:Location', 'l')
744 ->addFieldResult('l', 'id', 'id')
745 ->addFieldResult('l', 'title', 'title');
749 ->createNativeQuery($req, $rsm)
750 ->setParameter('id', $userId)