]> Raphaël G. Git Repositories - airbundle/blob - Repository/LocationRepository.php
Add date location index
[airbundle] / Repository / LocationRepository.php
1 <?php declare(strict_types=1);
2
3 /*
4 * This file is part of the Rapsys AirBundle package.
5 *
6 * (c) Raphaël Gertz <symfony@rapsys.eu>
7 *
8 * For the full copyright and license information, please view the LICENSE
9 * file that was distributed with this source code.
10 */
11
12 namespace Rapsys\AirBundle\Repository;
13
14 use Doctrine\ORM\Query\ResultSetMapping;
15
16 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
17 use Symfony\Component\Routing\RouterInterface;
18
19 use Rapsys\AirBundle\Repository;
20
21 /**
22 * LocationRepository
23 *
24 * @TODO: use new window function syntax https://mariadb.com/kb/en/window-functions-overview/ MAX(updated) OVER (PARTITION updated) AS modified ???
25 */
26 class LocationRepository extends Repository {
27 /**
28 * Find locations
29 *
30 * @return array
31 */
32 public function findAll(): array {
33 //Get all locations index by id
34 return $this->createQueryBuilder('location', 'location.id')->getQuery()->getResult();
35 }
36
37 /**
38 * Find locations as array
39 *
40 * @param DatePeriod $period The period
41 * @return array The locations array
42 */
43 public function findAllAsArray(\DatePeriod $period): array {
44 //Set the request
45 //TODO: ajouter pays ???
46 $req = <<<SQL
47 SELECT
48 l.id,
49 l.title,
50 l.latitude,
51 l.longitude,
52 l.indoor,
53 l.updated
54 FROM Rapsys\AirBundle\Entity\Location AS l
55 LEFT JOIN Rapsys\AirBundle\Entity\Session AS s ON (l.id = s.location_id)
56 GROUP BY l.id
57 ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, COUNT(s.id) DESC, l.id
58 SQL;
59
60 //Replace bundle entity name by table name
61 $req = str_replace($this->tableKeys, $this->tableValues, $req);
62
63 //Get result set mapping instance
64 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
65 $rsm = new ResultSetMapping();
66
67 //Declare all fields
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');
77
78 //Get result
79 $result = $this->_em
80 ->createNativeQuery($req, $rsm)
81 ->setParameter('begin', $period->getStartDate())
82 ->setParameter('end', $period->getEndDate())
83 ->getArrayResult();
84
85 //Set return
86 $return = [];
87
88 //Iterate on each city
89 foreach($result as $data) {
90 //Add to return
91 $return[] = [
92 'id' => $data['id'],
93 'title' => $title = $this->translator->trans($data['title']),
94 'latitude' => $data['latitude'],
95 'longitude' => $data['longitude'],
96 'updated' => $data['updated'],
97 //XXX: Useless ???
98 'slug' => $location = $this->slugger->slug($title),
99 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $this->slugger->slug($location)])
100 ];
101 }
102
103 //Return return
104 return $return;
105 }
106
107 /**
108 * Find cities as array
109 *
110 * @param DatePeriod $period The period
111 * @param int $count The session count
112 * @return array The cities array
113 */
114 public function findCitiesAsArray(\DatePeriod $period, int $count = 1): array {
115 //Set the request
116 $req = <<<SQL
117 SELECT
118 SUBSTRING(a.zipcode, 1, 2) AS id,
119 a.city AS city,
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
129 FROM (
130 SELECT
131 l.id,
132 l.city,
133 l.title,
134 l.latitude,
135 l.longitude,
136 l.indoor,
137 l.updated,
138 l.zipcode,
139 COUNT(s.id) AS count,
140 COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) AS pcount
141 FROM Rapsys\AirBundle\Entity\Location AS l
142 LEFT JOIN Rapsys\AirBundle\Entity\Session AS s ON (l.id = s.location_id)
143 GROUP BY l.id
144 ORDER BY NULL
145 LIMIT 0, :limit
146 ) AS a
147 GROUP BY a.city, SUBSTRING(a.zipcode, 1, 3)
148 ORDER BY a.city, SUBSTRING(a.zipcode, 1, 3)
149 SQL;
150
151 //Replace bundle entity name by table name
152 $req = str_replace($this->tableKeys, $this->tableValues, $req);
153
154 //Get result set mapping instance
155 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
156 $rsm = new ResultSetMapping();
157
158 //Declare all fields
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');
179
180 //Get result
181 $result = $this->_em
182 ->createNativeQuery($req, $rsm)
183 ->setParameter('begin', $period->getStartDate())
184 ->setParameter('end', $period->getEndDate())
185 ->getArrayResult();
186
187 //Set return
188 $return = [];
189
190 //Iterate on each city
191 foreach($result as $city => $data) {
192 //Set titles
193 $titles = explode("\n", $data['titles']);
194
195 //Set latitudes
196 $latitudes = explode("\n", $data['latitudes']);
197
198 //Set longitudes
199 $longitudes = explode("\n", $data['longitudes']);
200
201 //Set indoors
202 $indoors = explode("\n", $data['indoors']);
203
204 //Set counts
205 $counts = explode("\n", $data['counts']);
206
207 //With unsufficient count
208 if ($count && $counts[0] < $count) {
209 //Skip empty city
210 //XXX: count are sorted so only check first
211 continue;
212 }
213
214 //Set locations
215 $data['locations'] = [];
216
217 //Iterate on each location
218 foreach(explode("\n", $data['ids']) as $k => $id) {
219 //With unsufficient count
220 if ($count && $counts[$k] < $count) {
221 //Skip empty city
222 //XXX: count are sorted so only check first
223 continue;
224 }
225
226 //Add location
227 $data['locations'][] = [
228 'id' => $id,
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)])
234 ];
235 }
236
237 //Add to return
238 $return[$city] = [
239 'id' => $data['id'],
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'],
247 //XXX: Useless ???
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']
251 ];
252 }
253
254 //Return return
255 return $return;
256 }
257
258 /**
259 * Find city by latitude and longitude as array
260 *
261 * @param float $latitude The latitude
262 * @param float $longitude The longitude
263 * @return ?array The cities array
264 */
265 public function findCityByLatitudeLongitudeAsArray(float $latitude, float $longitude): ?array {
266 //Set the request
267 $req = <<<SQL
268 SELECT
269 SUBSTRING(l.zipcode, 1, 2) AS id,
270 l.city AS city,
271 ROUND(AVG(l.latitude), 6) AS latitude,
272 ROUND(AVG(l.longitude), 6) AS longitude,
273 MAX(l.updated) AS updated
274 FROM Rapsys\AirBundle\Entity\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()
277 LIMIT 0, 1
278 SQL;
279
280 //Replace bundle entity name by table name
281 $req = str_replace($this->tableKeys, $this->tableValues, $req);
282
283 //Get result set mapping instance
284 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
285 $rsm = new ResultSetMapping();
286
287 //Declare all fields
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');
296
297 //Get result
298 $result = $this->_em
299 ->createNativeQuery($req, $rsm)
300 ->setParameter('latitude', $latitude)
301 ->setParameter('longitude', $longitude)
302 ->getOneOrNullResult();
303
304 //Without result
305 if ($result === null) {
306 //Return result
307 return $result;
308 }
309
310 //Return result
311 return [
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'),
319 //XXX: Useless ???
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']])
322 ];
323 }
324
325 /**
326 * Find locations by latitude and longitude sorted by period as array
327 *
328 * @TODO: find all other locations when current one has no sessions ???
329 *
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
336 */
337 public function findAllByLatitudeLongitudeAsArray(float $latitude, float $longitude, \DatePeriod $period, int $count = 1, float $distance = 15): array {
338 //Set earth radius
339 $radius = 40030.17/2/pi();
340
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);
343
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);
346
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;
349
350 //Compute max longi
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;
352
353 //Set the request
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 ?
357 $req = <<<SQL
358 SELECT
359 a.id,
360 a.title,
361 a.latitude,
362 a.longitude,
363 a.updated,
364 MAX(a.updated) AS modified,
365 COUNT(s.id) AS count
366 FROM (
367 SELECT
368 l.id,
369 l.title,
370 l.latitude,
371 l.longitude,
372 l.updated
373 FROM Rapsys\AirBundle\Entity\Location AS l
374 WHERE l.latitude BETWEEN :minlat AND :maxlat AND l.longitude BETWEEN :minlong AND :maxlong
375 LIMIT 0, :limit
376 ) AS a
377 LEFT JOIN Rapsys\AirBundle\Entity\Session s ON (s.location_id = a.id)
378 GROUP BY a.id
379 ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, count DESC, a.id
380 SQL;
381
382 //Replace bundle entity name by table name
383 $req = str_replace($this->tableKeys, $this->tableValues, $req);
384
385 //Get result set mapping instance
386 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
387 $rsm = new ResultSetMapping();
388
389 //Declare all fields
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');
399
400 //Get result
401 $result = $this->_em
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)
409 ->getArrayResult();
410
411 //Set return
412 $return = [];
413
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
419 continue;
420 }
421
422 //Add location
423 $return[$id] = [
424 'id' => $data['id'],
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])
433 ];
434 }
435
436 //Return return
437 return $return;
438 }
439
440 /**
441 * Find locations by user id sorted by period as array
442 *
443 * @param int $userId The user id
444 * @param DatePeriod $period The period
445 * @return array The locations array
446 */
447 public function findAllByUserIdAsArray(int $userId, \DatePeriod $period, $distance = 15): array {
448 //Set the request
449 //TODO: ajouter pays ???
450 $req = <<<SQL
451 SELECT
452 a.id,
453 a.title,
454 a.city,
455 a.latitude,
456 a.longitude,
457 a.updated,
458 a.modified,
459 a.pcount,
460 COUNT(s3.id) AS tcount
461 FROM (
462 SELECT
463 b.id,
464 b.title,
465 b.city,
466 b.latitude,
467 b.longitude,
468 b.updated,
469 COUNT(s2.id) AS pcount,
470 MAX(b.updated) AS modified
471 FROM (
472 SELECT
473 l2.id,
474 l2.city,
475 l2.title,
476 l2.latitude,
477 l2.longitude,
478 l2.updated
479 FROM (
480 SELECT
481 l.id,
482 l.latitude,
483 l.longitude
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
488 GROUP BY l.id
489 ORDER BY NULL
490 LIMIT 0, :limit
491 ) AS a
492 JOIN locations AS l2
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
494 GROUP BY l2.id
495 ORDER BY NULL
496 LIMIT 0, :limit
497 ) AS b
498 LEFT JOIN sessions AS s2 ON (s2.location_id = b.id AND s2.date BETWEEN :begin AND :end)
499 GROUP BY b.id
500 ORDER BY NULL
501 LIMIT 0, :limit
502 ) AS a
503 LEFT JOIN sessions AS s3 ON (s3.location_id = a.id)
504 GROUP BY a.id
505 ORDER BY pcount DESC, tcount DESC, a.id
506 SQL;
507
508 //Replace bundle entity name by table name
509 $req = str_replace($this->tableKeys, $this->tableValues, $req);
510
511 //Get result set mapping instance
512 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
513 $rsm = new ResultSetMapping();
514
515 //Declare all fields
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');
527
528 //Get result
529 $result = $this->_em
530 ->createNativeQuery($req, $rsm)
531 ->setParameter('begin', $period->getStartDate())
532 ->setParameter('end', $period->getEndDate())
533 ->setParameter('id', $userId)
534 ->setParameter('distance', $distance)
535 ->getArrayResult();
536
537 //Set return
538 $return = [];
539
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
545 break;
546 }
547
548 //Add location
549 $return[$id] = [
550 'id' => $data['id'],
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])
563 ];
564 }
565
566 //Return return
567 return $return;
568 }
569
570 /**
571 * Find location as array by id
572 *
573 * @param int $id The location id
574 * @param string $locale The locale
575 * @return array The location data
576 */
577 public function findOneByIdAsArray(int $id, string $locale): ?array {
578 //Set the request
579 $req = <<<SQL
580 SELECT
581 l.id,
582 l.title,
583 l.city,
584 l.latitude,
585 l.longitude,
586 l.indoor,
587 l.zipcode,
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 Rapsys\AirBundle\Entity\Location AS l
593 JOIN Rapsys\AirBundle\Entity\Location AS l2 ON (l2.city = l.city AND SUBSTRING(l.zipcode, 1, 3) = SUBSTRING(l.zipcode, 1, 3))
594 WHERE l.id = :id
595 GROUP BY l.id
596 LIMIT 0, 1
597 SQL;
598
599 //Replace bundle entity name by table name
600 $req = str_replace($this->tableKeys, $this->tableValues, $req);
601
602 //Get result set mapping instance
603 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
604 $rsm = new ResultSetMapping();
605
606 //Declare all fields
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');
621
622 //Get result
623 $result = $this->_em
624 ->createNativeQuery($req, $rsm)
625 ->setParameter('id', $id)
626 ->getOneOrNullResult();
627
628 //Without result
629 if ($result === null) {
630 //Return result
631 return $result;
632 }
633
634 //Set alternates
635 $result['alternates'] = [];
636
637 //Set route
638 $route = 'rapsys_air_location_view';
639
640 //Set route params
641 $routeParams = ['id' => $id];
642
643 //Iterate on each languages
644 foreach($this->languages as $languageId => $language) {
645 //Without current locale
646 if ($languageId !== $locale) {
647 //Set titles
648 $titles = [];
649
650 //Set route params locale
651 $routeParams['_locale'] = $languageId;
652
653 //Set route params location
654 $routeParams['location'] = $this->slugger->slug($this->translator->trans($result['title'], [], null, $languageId));
655
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);
662 }
663 }
664
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)
671 ];
672 }
673 }
674
675 //Return result
676 return [
677 'id' => $result['id'],
678 'city' => [
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']])
683 ],
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'),
693 //XXX: Useless ???
694 'slug' => $slug = $this->slugger->slug($title),
695 'link' => $this->router->generate($route, ['_locale' => $locale, 'location' => $slug]+$routeParams),
696 'alternates' => $result['alternates']
697 ];
698 }
699
700 /**
701 * Find complementary locations by session id
702 *
703 * @param int $id The session id
704 * @return array The other locations
705 */
706 public function findComplementBySessionId(int $id): array {
707 //Fetch complement locations
708 $ret = $this->getEntityManager()
709 ->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')
710 ->setParameter('sid', $id)
711 ->getArrayResult();
712
713 //TODO: try to improve with:
714 #->addIndexByScalar('city');
715
716 //Rekey array
717 $ret = array_column($ret, 'id', 'title');
718
719 return $ret;
720 }
721
722 /**
723 * Find locations by user id
724 *
725 * @param int $id The user id
726 * @return array The user locations
727 */
728 public function findByUserId(int $userId): array {
729 //Set the request
730 $req = 'SELECT l.id, l.title
731 FROM Rapsys\AirBundle\Entity\UserLocation AS ul
732 JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = ul.location_id)
733 WHERE ul.user_id = :id';
734
735 //Replace bundle entity name by table name
736 $req = str_replace($this->tableKeys, $this->tableValues, $req);
737
738 //Get result set mapping instance
739 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
740 $rsm = new ResultSetMapping();
741
742 //Declare result set for our request
743 $rsm->addEntityResult('Rapsys\AirBundle\Entity\Location', 'l')
744 ->addFieldResult('l', 'id', 'id')
745 ->addFieldResult('l', 'title', 'title');
746
747 //Send result
748 return $this->_em
749 ->createNativeQuery($req, $rsm)
750 ->setParameter('id', $userId)
751 ->getResult();
752 }
753 }