]> Raphaël G. Git Repositories - airbundle/blob - Repository/LocationRepository.php
Add apple-touch-icon 120x120 for outdated iOS
[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 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
16 use Symfony\Component\Routing\RouterInterface;
17
18 /**
19 * LocationRepository
20 *
21 * @TODO: use new window function syntax https://mariadb.com/kb/en/window-functions-overview/ MAX(updated) OVER (PARTITION updated) AS modified ???
22 */
23 class LocationRepository extends EntityRepository {
24 /**
25 * Find locations
26 *
27 * @return array
28 */
29 public function findAll(): array {
30 //Get all locations index by id
31 return $this->createQueryBuilder('location', 'location.id')->getQuery()->getResult();
32 }
33
34 /**
35 * Find locations as array
36 *
37 * @param DatePeriod $period The period
38 * @return array The locations array
39 */
40 public function findAllAsArray(\DatePeriod $period): array {
41 //Set the request
42 //TODO: ajouter pays ???
43 $req = <<<SQL
44 SELECT
45 l.id,
46 l.title,
47 l.latitude,
48 l.longitude,
49 l.indoor,
50 l.updated
51 FROM RapsysAirBundle:Location AS l
52 LEFT JOIN RapsysAirBundle:Session AS s ON (l.id = s.location_id)
53 GROUP BY l.id
54 ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, COUNT(s.id) DESC, l.id
55 SQL;
56
57 //Replace bundle entity name by table name
58 $req = str_replace($this->tableKeys, $this->tableValues, $req);
59
60 //Get result set mapping instance
61 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
62 $rsm = new ResultSetMapping();
63
64 //Declare all fields
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');
74
75 //Get result
76 $result = $this->_em
77 ->createNativeQuery($req, $rsm)
78 ->setParameter('begin', $period->getStartDate())
79 ->setParameter('end', $period->getEndDate())
80 ->getArrayResult();
81
82 //Set return
83 $return = [];
84
85 //Iterate on each city
86 foreach($result as $data) {
87 //Add to return
88 $return[] = [
89 'id' => $data['id'],
90 'title' => $title = $this->translator->trans($data['title']),
91 'latitude' => $data['latitude'],
92 'longitude' => $data['longitude'],
93 'updated' => $data['updated'],
94 //XXX: Useless ???
95 'slug' => $location = $this->slugger->slug($title),
96 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $this->slugger->slug($location)])
97 ];
98 }
99
100 //Return return
101 return $return;
102 }
103
104 /**
105 * Find cities as array
106 *
107 * @param DatePeriod $period The period
108 * @param int $count The session count
109 * @return array The cities array
110 */
111 public function findCitiesAsArray(\DatePeriod $period, int $count = 1): array {
112 //Set the request
113 $req = <<<SQL
114 SELECT
115 SUBSTRING(a.zipcode, 1, 2) AS id,
116 a.city AS city,
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
126 FROM (
127 SELECT
128 l.id,
129 l.city,
130 l.title,
131 l.latitude,
132 l.longitude,
133 l.indoor,
134 l.updated,
135 l.zipcode,
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)
140 GROUP BY l.id
141 ORDER BY NULL
142 LIMIT 0, :limit
143 ) AS a
144 GROUP BY a.city, SUBSTRING(a.zipcode, 1, 3)
145 ORDER BY a.city, SUBSTRING(a.zipcode, 1, 3)
146 SQL;
147
148 //Replace bundle entity name by table name
149 $req = str_replace($this->tableKeys, $this->tableValues, $req);
150
151 //Get result set mapping instance
152 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
153 $rsm = new ResultSetMapping();
154
155 //Declare all fields
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');
176
177 //Get result
178 $result = $this->_em
179 ->createNativeQuery($req, $rsm)
180 ->setParameter('begin', $period->getStartDate())
181 ->setParameter('end', $period->getEndDate())
182 ->getArrayResult();
183
184 //Set return
185 $return = [];
186
187 //Iterate on each city
188 foreach($result as $city => $data) {
189 //Set titles
190 $titles = explode("\n", $data['titles']);
191
192 //Set latitudes
193 $latitudes = explode("\n", $data['latitudes']);
194
195 //Set longitudes
196 $longitudes = explode("\n", $data['longitudes']);
197
198 //Set indoors
199 $indoors = explode("\n", $data['indoors']);
200
201 //Set counts
202 $counts = explode("\n", $data['counts']);
203
204 //With unsufficient count
205 if ($count && $counts[0] < $count) {
206 //Skip empty city
207 //XXX: count are sorted so only check first
208 continue;
209 }
210
211 //Set locations
212 $data['locations'] = [];
213
214 //Iterate on each location
215 foreach(explode("\n", $data['ids']) as $k => $id) {
216 //With unsufficient count
217 if ($count && $counts[$k] < $count) {
218 //Skip empty city
219 //XXX: count are sorted so only check first
220 continue;
221 }
222
223 //Add location
224 $data['locations'][] = [
225 'id' => $id,
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)])
231 ];
232 }
233
234 //Add to return
235 $return[$city] = [
236 'id' => $data['id'],
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'],
244 //XXX: Useless ???
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']
248 ];
249 }
250
251 //Return return
252 return $return;
253 }
254
255 /**
256 * Find city by latitude and longitude as array
257 *
258 * @param float $latitude The latitude
259 * @param float $longitude The longitude
260 * @return ?array The cities array
261 */
262 public function findCityByLatitudeLongitudeAsArray(float $latitude, float $longitude): ?array {
263 //Set the request
264 $req = <<<SQL
265 SELECT
266 SUBSTRING(l.zipcode, 1, 2) AS id,
267 l.city AS city,
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()
274 LIMIT 0, 1
275 SQL;
276
277 //Replace bundle entity name by table name
278 $req = str_replace($this->tableKeys, $this->tableValues, $req);
279
280 //Get result set mapping instance
281 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
282 $rsm = new ResultSetMapping();
283
284 //Declare all fields
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');
293
294 //Get result
295 $result = $this->_em
296 ->createNativeQuery($req, $rsm)
297 ->setParameter('latitude', $latitude)
298 ->setParameter('longitude', $longitude)
299 ->getOneOrNullResult();
300
301 //Without result
302 if ($result === null) {
303 //Return result
304 return $result;
305 }
306
307 //Return result
308 return [
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'),
316 //XXX: Useless ???
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']])
319 ];
320 }
321
322 /**
323 * Find locations by latitude and longitude sorted by period as array
324 *
325 * @TODO: find all other locations when current one has no sessions ???
326 *
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
333 */
334 public function findAllByLatitudeLongitudeAsArray(float $latitude, float $longitude, \DatePeriod $period, int $count = 1, float $distance = 15): array {
335 //Set earth radius
336 $radius = 40030.17/2/pi();
337
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);
340
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);
343
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;
346
347 //Compute max longi
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;
349
350 //Set the request
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 ?
354 $req = <<<SQL
355 SELECT
356 a.id,
357 a.title,
358 a.latitude,
359 a.longitude,
360 a.updated,
361 MAX(a.updated) AS modified,
362 COUNT(s.id) AS count
363 FROM (
364 SELECT
365 l.id,
366 l.title,
367 l.latitude,
368 l.longitude,
369 l.updated
370 FROM RapsysAirBundle:Location AS l
371 WHERE l.latitude BETWEEN :minlat AND :maxlat AND l.longitude BETWEEN :minlong AND :maxlong
372 LIMIT 0, :limit
373 ) AS a
374 LEFT JOIN RapsysAirBundle:Session s ON (s.location_id = a.id)
375 GROUP BY a.id
376 ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, count DESC, a.id
377 SQL;
378
379 //Replace bundle entity name by table name
380 $req = str_replace($this->tableKeys, $this->tableValues, $req);
381
382 //Get result set mapping instance
383 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
384 $rsm = new ResultSetMapping();
385
386 //Declare all fields
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');
396
397 //Get result
398 $result = $this->_em
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)
406 ->getArrayResult();
407
408 //Set return
409 $return = [];
410
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
416 continue;
417 }
418
419 //Add location
420 $return[$id] = [
421 'id' => $data['id'],
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])
430 ];
431 }
432
433 //Return return
434 return $return;
435 }
436
437 /**
438 * Find locations by user id sorted by period as array
439 *
440 * @param int $userId The user id
441 * @param DatePeriod $period The period
442 * @return array The locations array
443 */
444 public function findAllByUserIdAsArray(int $userId, \DatePeriod $period, $distance = 15): array {
445 //Set the request
446 //TODO: ajouter pays ???
447 $req = <<<SQL
448 SELECT
449 a.id,
450 a.title,
451 a.city,
452 a.latitude,
453 a.longitude,
454 a.updated,
455 a.modified,
456 a.pcount,
457 COUNT(s3.id) AS tcount
458 FROM (
459 SELECT
460 b.id,
461 b.title,
462 b.city,
463 b.latitude,
464 b.longitude,
465 b.updated,
466 COUNT(s2.id) AS pcount,
467 MAX(b.updated) AS modified
468 FROM (
469 SELECT
470 l2.id,
471 l2.city,
472 l2.title,
473 l2.latitude,
474 l2.longitude,
475 l2.updated
476 FROM (
477 SELECT
478 l.id,
479 l.latitude,
480 l.longitude
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
485 GROUP BY l.id
486 ORDER BY NULL
487 LIMIT 0, :limit
488 ) AS a
489 JOIN locations AS l2
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
491 GROUP BY l2.id
492 ORDER BY NULL
493 LIMIT 0, :limit
494 ) AS b
495 LEFT JOIN sessions AS s2 ON (s2.location_id = b.id AND s2.date BETWEEN :begin AND :end)
496 GROUP BY b.id
497 ORDER BY NULL
498 LIMIT 0, :limit
499 ) AS a
500 LEFT JOIN sessions AS s3 ON (s3.location_id = a.id)
501 GROUP BY a.id
502 ORDER BY pcount DESC, tcount DESC, a.id
503 SQL;
504
505 //Replace bundle entity name by table name
506 $req = str_replace($this->tableKeys, $this->tableValues, $req);
507
508 //Get result set mapping instance
509 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
510 $rsm = new ResultSetMapping();
511
512 //Declare all fields
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');
524
525 //Get result
526 $result = $this->_em
527 ->createNativeQuery($req, $rsm)
528 ->setParameter('begin', $period->getStartDate())
529 ->setParameter('end', $period->getEndDate())
530 ->setParameter('id', $userId)
531 ->setParameter('distance', $distance)
532 ->getArrayResult();
533
534 //Set return
535 $return = [];
536
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
542 break;
543 }
544
545 //Add location
546 $return[$id] = [
547 'id' => $data['id'],
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])
560 ];
561 }
562
563 //Return return
564 return $return;
565 }
566
567 /**
568 * Find location as array by id
569 *
570 * @param int $id The location id
571 * @param string $locale The locale
572 * @return array The location data
573 */
574 public function findOneByIdAsArray(int $id, string $locale): ?array {
575 //Set the request
576 $req = <<<SQL
577 SELECT
578 l.id,
579 l.title,
580 l.city,
581 l.latitude,
582 l.longitude,
583 l.indoor,
584 l.zipcode,
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))
591 WHERE l.id = :id
592 GROUP BY l.id
593 LIMIT 0, 1
594 SQL;
595
596 //Replace bundle entity name by table name
597 $req = str_replace($this->tableKeys, $this->tableValues, $req);
598
599 //Get result set mapping instance
600 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
601 $rsm = new ResultSetMapping();
602
603 //Declare all fields
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');
618
619 //Get result
620 $result = $this->_em
621 ->createNativeQuery($req, $rsm)
622 ->setParameter('id', $id)
623 ->getOneOrNullResult();
624
625 //Without result
626 if ($result === null) {
627 //Return result
628 return $result;
629 }
630
631 //Set alternates
632 $result['alternates'] = [];
633
634 //Set route
635 $route = 'rapsys_air_location_view';
636
637 //Set route params
638 $routeParams = ['id' => $id];
639
640 //Iterate on each languages
641 foreach($this->languages as $languageId => $language) {
642 //Without current locale
643 if ($languageId !== $locale) {
644 //Set titles
645 $titles = [];
646
647 //Set route params locale
648 $routeParams['_locale'] = $languageId;
649
650 //Set route params location
651 $routeParams['location'] = $this->slugger->slug($this->translator->trans($result['title'], [], null, $languageId));
652
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);
659 }
660 }
661
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)
668 ];
669 }
670 }
671
672 //Return result
673 return [
674 'id' => $result['id'],
675 'city' => [
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']])
680 ],
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'),
690 //XXX: Useless ???
691 'slug' => $slug = $this->slugger->slug($title),
692 'link' => $this->router->generate($route, ['_locale' => $locale, 'location' => $slug]+$routeParams),
693 'alternates' => $result['alternates']
694 ];
695 }
696
697 /**
698 * Find complementary locations by session id
699 *
700 * @param int $id The session id
701 * @return array The other locations
702 */
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)
708 ->getArrayResult();
709
710 //TODO: try to improve with:
711 #->addIndexByScalar('city');
712
713 //Rekey array
714 $ret = array_column($ret, 'id', 'title');
715
716 return $ret;
717 }
718
719 /**
720 * Find locations by user id
721 *
722 * @param int $id The user id
723 * @return array The user locations
724 */
725 public function findByUserId(int $userId): array {
726 //Set the request
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';
731
732 //Replace bundle entity name by table name
733 $req = str_replace($this->tableKeys, $this->tableValues, $req);
734
735 //Get result set mapping instance
736 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
737 $rsm = new ResultSetMapping();
738
739 //Declare result set for our request
740 $rsm->addEntityResult('RapsysAirBundle:Location', 'l')
741 ->addFieldResult('l', 'id', 'id')
742 ->addFieldResult('l', 'title', 'title');
743
744 //Send result
745 return $this->_em
746 ->createNativeQuery($req, $rsm)
747 ->setParameter('id', $userId)
748 ->getResult();
749 }
750 }