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
;
15 use Symfony\Component\Routing\Generator\UrlGeneratorInterface
;
16 use Symfony\Component\Routing\RouterInterface
;
21 * @TODO: use new window function syntax https://mariadb.com/kb/en/window-functions-overview/ MAX(updated) OVER (PARTITION updated) AS modified ???
23 class LocationRepository
extends Repository
{
29 public function findAll(): array {
30 //Get all locations index by id
31 return $this->createQueryBuilder('location', 'location.id')->getQuery()->getResult();
35 * Find locations as array
37 * @param DatePeriod $period The period
38 * @return array The locations array
40 public function findAllAsArray(\DatePeriod
$period): array {
42 //TODO: ajouter pays ???
51 FROM RapsysAirBundle:Location AS l
52 LEFT JOIN RapsysAirBundle:Session AS s ON (l.id = s.location_id)
54 ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, COUNT(s.id) DESC, l.id
57 //Replace bundle entity name by table name
58 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
60 //Get result set mapping instance
61 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
62 $rsm = new ResultSetMapping();
65 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
66 //addScalarResult($sqlColName, $resColName, $type = 'string');
67 $rsm->addScalarResult('id', 'id', 'integer')
68 ->addScalarResult('title', 'title', 'string')
69 ->addScalarResult('latitude', 'latitude', 'float')
70 ->addScalarResult('longitude', 'longitude', 'float')
71 ->addScalarResult('indoor', 'indoor', 'boolean')
72 ->addScalarResult('count', 'count', 'integer')
73 ->addScalarResult('updated', 'updated', 'datetime');
77 ->createNativeQuery($req, $rsm)
78 ->setParameter('begin', $period->getStartDate())
79 ->setParameter('end', $period->getEndDate())
85 //Iterate on each city
86 foreach($result as $data) {
90 'title' => $title = $this->translator
->trans($data['title']),
91 'latitude' => $data['latitude'],
92 'longitude' => $data['longitude'],
93 'updated' => $data['updated'],
95 'slug' => $location = $this->slugger
->slug($title),
96 'link' => $this->router
->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $this->slugger
->slug($location)])
105 * Find cities as array
107 * @param DatePeriod $period The period
108 * @param int $count The session count
109 * @return array The cities array
111 public function findCitiesAsArray(\DatePeriod
$period, int $count = 1): array {
115 SUBSTRING(a.zipcode, 1, 2) AS id,
117 ROUND(AVG(a.latitude), 6) AS latitude,
118 ROUND(AVG(a.longitude), 6) AS longitude,
119 GROUP_CONCAT(a.id ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS ids,
120 GROUP_CONCAT(a.title ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS titles,
121 GROUP_CONCAT(a.latitude ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS latitudes,
122 GROUP_CONCAT(a.longitude ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS longitudes,
123 GROUP_CONCAT(a.indoor ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS indoors,
124 GROUP_CONCAT(a.count ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS counts,
125 MAX(a.updated) AS modified
136 COUNT(s.id) AS count,
137 COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) AS pcount
138 FROM RapsysAirBundle:Location AS l
139 LEFT JOIN RapsysAirBundle:Session AS s ON (l.id = s.location_id)
144 GROUP BY a.city, SUBSTRING(a.zipcode, 1, 3)
145 ORDER BY a.city, SUBSTRING(a.zipcode, 1, 3)
148 //Replace bundle entity name by table name
149 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
151 //Get result set mapping instance
152 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
153 $rsm = new ResultSetMapping();
156 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
157 //addScalarResult($sqlColName, $resColName, $type = 'string');
158 $rsm->addScalarResult('id', 'id', 'integer')
159 ->addScalarResult('city', 'city', 'string')
160 ->addScalarResult('latitude', 'latitude', 'float')
161 ->addScalarResult('longitude', 'longitude', 'float')
162 //XXX: is a string because of \n separator
163 ->addScalarResult('ids', 'ids', 'string')
164 //XXX: is a string because of \n separator
165 ->addScalarResult('titles', 'titles', 'string')
166 //XXX: is a string because of \n separator
167 ->addScalarResult('latitudes', 'latitudes', 'string')
168 //XXX: is a string because of \n separator
169 ->addScalarResult('longitudes', 'longitudes', 'string')
170 //XXX: is a string because of \n separator
171 ->addScalarResult('indoors', 'indoors', 'string')
172 //XXX: is a string because of \n separator
173 ->addScalarResult('counts', 'counts', 'string')
174 ->addScalarResult('modified', 'modified', 'datetime')
175 ->addIndexByScalar('city');
179 ->createNativeQuery($req, $rsm)
180 ->setParameter('begin', $period->getStartDate())
181 ->setParameter('end', $period->getEndDate())
187 //Iterate on each city
188 foreach($result as $city => $data) {
190 $titles = explode("\n", $data['titles']);
193 $latitudes = explode("\n", $data['latitudes']);
196 $longitudes = explode("\n", $data['longitudes']);
199 $indoors = explode("\n", $data['indoors']);
202 $counts = explode("\n", $data['counts']);
204 //With unsufficient count
205 if ($count && $counts[0] < $count) {
207 //XXX: count are sorted so only check first
212 $data['locations'] = [];
214 //Iterate on each location
215 foreach(explode("\n", $data['ids']) as $k => $id) {
216 //With unsufficient count
217 if ($count && $counts[$k] < $count) {
219 //XXX: count are sorted so only check first
224 $data['locations'][] = [
226 'title' => $location = $this->translator
->trans($titles[$k]),
227 'latitude' => floatval($latitudes[$k]),
228 'longitude' => floatval($longitudes[$k]),
229 'indoor' => $indoors[$k] == 0 ? $this->translator
->trans('outdoor') : $this->translator
->trans('indoor'),
230 'link' => $this->router
->generate('rapsys_air_location_view', ['id' => $id, 'location' => $this->slugger
->slug($location)])
237 'city' => $data['city'],
238 'in' => $this->translator
->trans('in '.$data['city']),
239 'indoors' => array_map(function ($v) { return $v
== 0 ? $this
->translator
->trans('outdoor') : $this
->translator
->trans('indoor'); }, array_unique($indoors)),
240 'multimap' => $this->translator
->trans($data['city'].' sector map'),
241 'latitude' => $data['latitude'],
242 'longitude' => $data['longitude'],
243 'modified' => $data['modified'],
245 'slug' => $city = $this->slugger
->slug($data['city']),
246 'link' => $this->router
->generate('rapsys_air_city_view', ['city' => $city, 'latitude' => $data['latitude'], 'longitude' => $data['longitude']]),
247 'locations' => $data['locations']
256 * Find city by latitude and longitude as array
258 * @param float $latitude The latitude
259 * @param float $longitude The longitude
260 * @return ?array The cities array
262 public function findCityByLatitudeLongitudeAsArray(float $latitude, float $longitude): ?array {
266 SUBSTRING(l.zipcode, 1, 2) AS id,
268 ROUND(AVG(l.latitude), 6) AS latitude,
269 ROUND(AVG(l.longitude), 6) AS longitude,
270 MAX(l.updated) AS updated
271 FROM RapsysAirBundle:Location AS l
272 GROUP BY city, SUBSTRING(l.zipcode, 1, 3)
273 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()
277 //Replace bundle entity name by table name
278 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
280 //Get result set mapping instance
281 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
282 $rsm = new ResultSetMapping();
285 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
286 //addScalarResult($sqlColName, $resColName, $type = 'string');
287 $rsm->addScalarResult('id', 'id', 'integer')
288 ->addScalarResult('city', 'city', 'string')
289 ->addScalarResult('latitude', 'latitude', 'float')
290 ->addScalarResult('longitude', 'longitude', 'float')
291 ->addScalarResult('updated', 'updated', 'datetime')
292 ->addIndexByScalar('city');
296 ->createNativeQuery($req, $rsm)
297 ->setParameter('latitude', $latitude)
298 ->setParameter('longitude', $longitude)
299 ->getOneOrNullResult();
302 if ($result === null) {
309 'id' => $result['id'],
310 'city' => $result['city'],
311 'latitude' => $result['latitude'],
312 'longitude' => $result['longitude'],
313 'updated' => $result['updated'],
314 'in' => $this->translator
->trans('in '.$result['city']),
315 'multimap' => $this->translator
->trans($result['city'].' sector map'),
317 'slug' => $slug = $this->slugger
->slug($result['city']),
318 'link' => $this->router
->generate('rapsys_air_city_view', ['city' => $slug, 'latitude' => $result['latitude'], 'longitude' => $result['longitude']])
323 * Find locations by latitude and longitude sorted by period as array
325 * @TODO: find all other locations when current one has no sessions ???
327 * @param float $latitude The latitude
328 * @param float $longitude The longitude
329 * @param DatePeriod $period The period
330 * @param int $count The session count
331 * @param float $distance The distance
332 * @return array The locations array
334 public function findAllByLatitudeLongitudeAsArray(float $latitude, float $longitude, \DatePeriod
$period, int $count = 1, float $distance = 15): array {
336 $radius = 40030.17/2/pi();
338 //Compute min latitude
339 $minlat = min(rad2deg(asin(sin(deg2rad($latitude))*cos($distance/$radius) +
cos(deg2rad($latitude))*sin($distance/$radius)*cos(deg2rad(180)))), $latitude);
341 //Compute max latitude
342 $maxlat = max(rad2deg(asin(sin(deg2rad($latitude))*cos($distance/$radius) +
cos(deg2rad($latitude))*sin($distance/$radius)*cos(deg2rad(0)))), $latitude);
344 //Compute min longitude
345 $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;
348 $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;
351 //TODO: see old request before commit to sort session count, distance and then by id ?
352 //TODO: see to sort by future session count, historical session count, distance and then by id ?
353 //TODO: do the same for cities and city ?
361 MAX(a.updated) AS modified,
370 FROM RapsysAirBundle:Location AS l
371 WHERE l.latitude BETWEEN :minlat AND :maxlat AND l.longitude BETWEEN :minlong AND :maxlong
374 LEFT JOIN RapsysAirBundle:Session s ON (s.location_id = a.id)
376 ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, count DESC, a.id
379 //Replace bundle entity name by table name
380 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
382 //Get result set mapping instance
383 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
384 $rsm = new ResultSetMapping();
387 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
388 //addScalarResult($sqlColName, $resColName, $type = 'string');
389 $rsm->addScalarResult('id', 'id', 'integer')
390 ->addScalarResult('title', 'title', 'string')
391 ->addScalarResult('latitude', 'latitude', 'float')
392 ->addScalarResult('longitude', 'longitude', 'float')
393 ->addScalarResult('updated', 'updated', 'datetime')
394 ->addScalarResult('modified', 'modified', 'datetime')
395 ->addScalarResult('count', 'count', 'integer');
399 ->createNativeQuery($req, $rsm)
400 ->setParameter('begin', $period->getStartDate())
401 ->setParameter('end', $period->getEndDate())
402 ->setParameter('minlat', $minlat)
403 ->setParameter('maxlat', $maxlat)
404 ->setParameter('minlong', $minlong)
405 ->setParameter('maxlong', $maxlong)
411 //Iterate on each location
412 foreach($result as $id => $data) {
413 //With active locations
414 if ($count && $data['count'] < $count) {
415 //Skip unactive locations
422 'title' => $title = $this->translator
->trans($data['title']),
423 'latitude' => $data['latitude'],
424 'longitude' => $data['longitude'],
425 'updated' => $data['updated'],
426 'modified' => $data['modified'],
427 'count' => $data['count'],
428 'slug' => $slug = $this->slugger
->slug($title),
429 'link' => $this->router
->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $slug])
438 * Find locations by user id sorted by period as array
440 * @param int $userId The user id
441 * @param DatePeriod $period The period
442 * @return array The locations array
444 public function findAllByUserIdAsArray(int $userId, \DatePeriod
$period, $distance = 15): array {
446 //TODO: ajouter pays ???
457 COUNT(s3.id) AS tcount
466 COUNT(s2.id) AS pcount,
467 MAX(b.updated) AS modified
481 FROM applications AS a
482 JOIN sessions AS s ON (s.id = a.session_id)
483 JOIN locations AS l ON (l.id = s.location_id)
484 WHERE a.user_id = :id
490 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
495 LEFT JOIN sessions AS s2 ON (s2.location_id = b.id AND s2.date BETWEEN :begin AND :end)
500 LEFT JOIN sessions AS s3 ON (s3.location_id = a.id)
502 ORDER BY pcount DESC, tcount DESC, a.id
505 //Replace bundle entity name by table name
506 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
508 //Get result set mapping instance
509 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
510 $rsm = new ResultSetMapping();
513 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
514 //addScalarResult($sqlColName, $resColName, $type = 'string');
515 $rsm->addScalarResult('id', 'id', 'integer')
516 ->addScalarResult('title', 'title', 'string')
517 ->addScalarResult('city', 'city', 'string')
518 ->addScalarResult('latitude', 'latitude', 'float')
519 ->addScalarResult('longitude', 'longitude', 'float')
520 ->addScalarResult('updated', 'updated', 'datetime')
521 ->addScalarResult('pcount', 'pcount', 'integer')
522 ->addScalarResult('tcount', 'tcount', 'integer')
523 ->addScalarResult('modified', 'modified', 'datetime');
527 ->createNativeQuery($req, $rsm)
528 ->setParameter('begin', $period->getStartDate())
529 ->setParameter('end', $period->getEndDate())
530 ->setParameter('id', $userId)
531 ->setParameter('distance', $distance)
537 //Iterate on each location
538 foreach($result as $id => $data) {
539 //With active locations
540 if (!empty($result[0]['tcount']) && empty($data['tcount'])) {
541 //Skip unactive locations
548 'city' => $data['city'],
549 'title' => $title = $this->translator
->trans($data['title']),
550 'at' => $this->translator
->trans('at '.$data['title']),
551 'miniature' => $this->translator
->trans($data['title'].' miniature'),
552 'latitude' => $data['latitude'],
553 'longitude' => $data['longitude'],
554 'updated' => $data['updated'],
555 'pcount' => $data['pcount'],
556 'tcount' => $data['tcount'],
557 'modified' => $data['modified'],
558 'slug' => $slug = $this->slugger
->slug($title),
559 'link' => $this->router
->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $slug])
568 * Find location as array by id
570 * @param int $id The location id
571 * @param string $locale The locale
572 * @return array The location data
574 public function findOneByIdAsArray(int $id, string $locale): ?array {
585 MAX(l2.updated) AS updated,
586 SUBSTRING(l.zipcode, 1, 2) AS city_id,
587 ROUND(AVG(l2.latitude), 6) AS city_latitude,
588 ROUND(AVG(l2.longitude), 6) AS city_longitude
589 FROM RapsysAirBundle:Location AS l
590 JOIN RapsysAirBundle:Location AS l2 ON (l2.city = l.city AND SUBSTRING(l.zipcode, 1, 3) = SUBSTRING(l.zipcode, 1, 3))
596 //Replace bundle entity name by table name
597 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
599 //Get result set mapping instance
600 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
601 $rsm = new ResultSetMapping();
604 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
605 //addScalarResult($sqlColName, $resColName, $type = 'string');
606 $rsm->addScalarResult('id', 'id', 'integer')
607 ->addScalarResult('title', 'title', 'string')
608 ->addScalarResult('city', 'city', 'string')
609 ->addScalarResult('latitude', 'latitude', 'float')
610 ->addScalarResult('longitude', 'longitude', 'float')
611 ->addScalarResult('indoor', 'indoor', 'boolean')
612 ->addScalarResult('zipcode', 'zipcode', 'string')
613 ->addScalarResult('updated', 'updated', 'datetime')
614 ->addScalarResult('city_id', 'city_id', 'integer')
615 ->addScalarResult('city_latitude', 'city_latitude', 'float')
616 ->addScalarResult('city_longitude', 'city_longitude', 'float')
617 ->addIndexByScalar('id');
621 ->createNativeQuery($req, $rsm)
622 ->setParameter('id', $id)
623 ->getOneOrNullResult();
626 if ($result === null) {
632 $result['alternates'] = [];
635 $route = 'rapsys_air_location_view';
638 $routeParams = ['id' => $id];
640 //Iterate on each languages
641 foreach($this->languages
as $languageId => $language) {
642 //Without current locale
643 if ($languageId !== $locale) {
647 //Set route params locale
648 $routeParams['_locale'] = $languageId;
650 //Set route params location
651 $routeParams['location'] = $this->slugger
->slug($this->translator
->trans($result['title'], [], null, $languageId));
653 //Iterate on each locales
654 foreach(array_keys($this->languages
) as $other) {
655 //Without other locale
656 if ($other !== $languageId) {
657 //Set other locale title
658 $titles[$other] = $this->translator
->trans($language, [], null, $other);
662 //Add alternates locale
663 $result['alternates'][substr($languageId, 0, 2)] = $result['alternates'][str_replace('_', '-', $languageId)] = [
664 'absolute' => $this->router
->generate($route, $routeParams, UrlGeneratorInterface
::ABSOLUTE_URL
),
665 'relative' => $this->router
->generate($route, $routeParams),
666 'title' => implode('/', $titles),
667 'translated' => $this->translator
->trans($language, [], null, $languageId)
674 'id' => $result['id'],
676 'id' => $result['city_id'],
677 'title' => $result['city'],
678 'in' => $this->translator
->trans('in '.$result['city']),
679 'link' => $this->router
->generate('rapsys_air_city_view', ['city' => $result['city'], 'latitude' => $result['city_latitude'], 'longitude' => $result['city_longitude']])
681 'title' => $title = $this->translator
->trans($result['title']),
682 'latitude' => $result['latitude'],
683 'longitude' => $result['longitude'],
684 'indoor' => $result['indoor'],
685 'updated' => $result['updated'],
686 'around' => $this->translator
->trans('around '.$result['title']),
687 'at' => $this->translator
->trans('at '.$result['title']),
688 'atin' => $this->translator
->trans('at '.$result['title']).' '.$this->translator
->trans('in '.$result['city']),
689 'multimap' => $this->translator
->trans($result['title'].' sector map'),
691 'slug' => $slug = $this->slugger
->slug($title),
692 'link' => $this->router
->generate($route, ['_locale' => $locale, 'location' => $slug]+
$routeParams),
693 'alternates' => $result['alternates']
698 * Find complementary locations by session id
700 * @param int $id The session id
701 * @return array The other locations
703 public function findComplementBySessionId(int $id): array {
704 //Fetch complement locations
705 $ret = $this->getEntityManager()
706 ->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')
707 ->setParameter('sid', $id)
710 //TODO: try to improve with:
711 #->addIndexByScalar('city');
714 $ret = array_column($ret, 'id', 'title');
720 * Find locations by user id
722 * @param int $id The user id
723 * @return array The user locations
725 public function findByUserId(int $userId): array {
727 $req = 'SELECT l.id, l.title
728 FROM RapsysAirBundle:UserLocation AS ul
729 JOIN RapsysAirBundle:Location AS l ON (l.id = ul.location_id)
730 WHERE ul.user_id = :id';
732 //Replace bundle entity name by table name
733 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
735 //Get result set mapping instance
736 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
737 $rsm = new ResultSetMapping();
739 //Declare result set for our request
740 $rsm->addEntityResult('RapsysAirBundle:Location', 'l')
741 ->addFieldResult('l', 'id', 'id')
742 ->addFieldResult('l', 'title', 'title');
746 ->createNativeQuery($req, $rsm)
747 ->setParameter('id', $userId)