]> Raphaël G. Git Repositories - airbundle/blob - Repository/LocationRepository.php
Add Repository namespace
[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 RapsysAirBundle:Location AS l
55 LEFT JOIN RapsysAirBundle: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 RapsysAirBundle:Location AS l
142 LEFT JOIN RapsysAirBundle: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 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()
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 RapsysAirBundle: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 RapsysAirBundle: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 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))
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 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)
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 RapsysAirBundle:UserLocation AS ul
732 JOIN RapsysAirBundle: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('RapsysAirBundle: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 }