]> Raphaël G. Git Repositories - airbundle/blob - Repository/LocationRepository.php
4c479b41ff2c4e54e4e76f20dbdccbb9efe46d4b
[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('rapsysair_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.modified) 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 GREATEST(l.created, l.updated, COALESCE(s.created, 0), COALESCE(s.updated, 0)) AS modified,
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, 2)
148 ORDER BY a.city, a.zipcode
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('rapsysair_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('rapsysair_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, 2)
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('rapsysair_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.created,
364 a.updated,
365 MAX(GREATEST(a.modified, COALESCE(s.created, 0), COALESCE(s.updated, 0))) AS modified,
366 COUNT(s.id) AS count
367 FROM (
368 SELECT
369 l.id,
370 l.title,
371 l.latitude,
372 l.longitude,
373 l.created,
374 l.updated,
375 GREATEST(l.created, l.updated) AS modified
376 FROM Rapsys\AirBundle\Entity\Location AS l
377 WHERE l.latitude BETWEEN :minlat AND :maxlat AND l.longitude BETWEEN :minlong AND :maxlong
378 LIMIT 0, :limit
379 ) AS a
380 LEFT JOIN Rapsys\AirBundle\Entity\Session s ON (s.location_id = a.id)
381 GROUP BY a.id
382 ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, count DESC, a.id
383 SQL;
384
385 //Replace bundle entity name by table name
386 $req = str_replace($this->tableKeys, $this->tableValues, $req);
387
388 //Get result set mapping instance
389 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
390 $rsm = new ResultSetMapping();
391
392 //Declare all fields
393 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
394 //addScalarResult($sqlColName, $resColName, $type = 'string');
395 $rsm->addScalarResult('id', 'id', 'integer')
396 ->addScalarResult('title', 'title', 'string')
397 ->addScalarResult('latitude', 'latitude', 'float')
398 ->addScalarResult('longitude', 'longitude', 'float')
399 ->addScalarResult('created', 'created', 'datetime')
400 ->addScalarResult('updated', 'updated', 'datetime')
401 ->addScalarResult('modified', 'modified', 'datetime')
402 ->addScalarResult('count', 'count', 'integer');
403
404 //Get result
405 $result = $this->_em
406 ->createNativeQuery($req, $rsm)
407 ->setParameter('begin', $period->getStartDate())
408 ->setParameter('end', $period->getEndDate())
409 ->setParameter('minlat', $minlat)
410 ->setParameter('maxlat', $maxlat)
411 ->setParameter('minlong', $minlong)
412 ->setParameter('maxlong', $maxlong)
413 ->getArrayResult();
414
415 //Set return
416 $return = [];
417
418 //Iterate on each location
419 foreach($result as $id => $data) {
420 //With active locations
421 if ($count && $data['count'] < $count) {
422 //Skip unactive locations
423 continue;
424 }
425
426 //Add location
427 $return[$id] = [
428 'id' => $data['id'],
429 'title' => $title = $this->translator->trans($data['title']),
430 'latitude' => $data['latitude'],
431 'longitude' => $data['longitude'],
432 'created' => $data['created'],
433 'updated' => $data['updated'],
434 'modified' => $data['modified'],
435 'count' => $data['count'],
436 'slug' => $slug = $this->slugger->slug($title),
437 'link' => $this->router->generate('rapsysair_location_view', ['id' => $data['id'], 'location' => $slug])
438 ];
439 }
440
441 //Return return
442 return $return;
443 }
444
445 /**
446 * Find locations by user id sorted by period as array
447 *
448 * @param int $userId The user id
449 * @param DatePeriod $period The period
450 * @return array The locations array
451 */
452 public function findAllByUserIdAsArray(int $userId, \DatePeriod $period, $distance = 15): array {
453 //Set the request
454 //TODO: ajouter pays ???
455 $req = <<<SQL
456 SELECT
457 a.id,
458 a.title,
459 a.city,
460 a.latitude,
461 a.longitude,
462 a.updated,
463 a.modified,
464 a.pcount,
465 COUNT(s3.id) AS tcount
466 FROM (
467 SELECT
468 b.id,
469 b.title,
470 b.city,
471 b.latitude,
472 b.longitude,
473 b.updated,
474 COUNT(s2.id) AS pcount,
475 MAX(b.updated) AS modified
476 FROM (
477 SELECT
478 l2.id,
479 l2.city,
480 l2.title,
481 l2.latitude,
482 l2.longitude,
483 l2.updated
484 FROM (
485 SELECT
486 l.id,
487 l.latitude,
488 l.longitude
489 FROM applications AS a
490 JOIN sessions AS s ON (s.id = a.session_id)
491 JOIN locations AS l ON (l.id = s.location_id)
492 WHERE a.user_id = :id
493 GROUP BY l.id
494 ORDER BY NULL
495 LIMIT 0, :limit
496 ) AS a
497 JOIN locations AS l2
498 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
499 GROUP BY l2.id
500 ORDER BY NULL
501 LIMIT 0, :limit
502 ) AS b
503 LEFT JOIN sessions AS s2 ON (s2.location_id = b.id AND s2.date BETWEEN :begin AND :end)
504 GROUP BY b.id
505 ORDER BY NULL
506 LIMIT 0, :limit
507 ) AS a
508 LEFT JOIN sessions AS s3 ON (s3.location_id = a.id)
509 GROUP BY a.id
510 ORDER BY pcount DESC, tcount DESC, a.id
511 SQL;
512
513 //Replace bundle entity name by table name
514 $req = str_replace($this->tableKeys, $this->tableValues, $req);
515
516 //Get result set mapping instance
517 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
518 $rsm = new ResultSetMapping();
519
520 //Declare all fields
521 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
522 //addScalarResult($sqlColName, $resColName, $type = 'string');
523 $rsm->addScalarResult('id', 'id', 'integer')
524 ->addScalarResult('title', 'title', 'string')
525 ->addScalarResult('city', 'city', 'string')
526 ->addScalarResult('latitude', 'latitude', 'float')
527 ->addScalarResult('longitude', 'longitude', 'float')
528 ->addScalarResult('updated', 'updated', 'datetime')
529 ->addScalarResult('pcount', 'pcount', 'integer')
530 ->addScalarResult('tcount', 'tcount', 'integer')
531 ->addScalarResult('modified', 'modified', 'datetime');
532
533 //Get result
534 $result = $this->_em
535 ->createNativeQuery($req, $rsm)
536 ->setParameter('begin', $period->getStartDate())
537 ->setParameter('end', $period->getEndDate())
538 ->setParameter('id', $userId)
539 ->setParameter('distance', $distance)
540 ->getArrayResult();
541
542 //Set return
543 $return = [];
544
545 //Iterate on each location
546 foreach($result as $id => $data) {
547 //With active locations
548 if (!empty($result[0]['tcount']) && empty($data['tcount'])) {
549 //Skip unactive locations
550 break;
551 }
552
553 //Add location
554 $return[$id] = [
555 'id' => $data['id'],
556 'city' => $data['city'],
557 'title' => $title = $this->translator->trans($data['title']),
558 'at' => $this->translator->trans('at '.$data['title']),
559 'miniature' => $this->translator->trans($data['title'].' miniature'),
560 'latitude' => $data['latitude'],
561 'longitude' => $data['longitude'],
562 'updated' => $data['updated'],
563 'pcount' => $data['pcount'],
564 'tcount' => $data['tcount'],
565 'modified' => $data['modified'],
566 'slug' => $slug = $this->slugger->slug($title),
567 'link' => $this->router->generate('rapsysair_location_view', ['id' => $data['id'], 'location' => $slug])
568 ];
569 }
570
571 //Return return
572 return $return;
573 }
574
575 /**
576 * Find location as array by id
577 *
578 * @param int $id The location id
579 * @param string $locale The locale
580 * @return array The location data
581 */
582 public function findOneByIdAsArray(int $id, string $locale): ?array {
583 //Set the request
584 $req = <<<SQL
585 SELECT
586 l.id,
587 l.title,
588 l.city,
589 l.latitude,
590 l.longitude,
591 l.indoor,
592 l.zipcode,
593 MAX(l2.updated) AS updated,
594 SUBSTRING(l.zipcode, 1, 2) AS city_id,
595 ROUND(AVG(l2.latitude), 6) AS city_latitude,
596 ROUND(AVG(l2.longitude), 6) AS city_longitude
597 FROM Rapsys\AirBundle\Entity\Location AS l
598 JOIN Rapsys\AirBundle\Entity\Location AS l2 ON (l2.city = l.city AND SUBSTRING(l2.zipcode, 1, 3) = SUBSTRING(l.zipcode, 1, 3))
599 WHERE l.id = :id
600 GROUP BY l.id
601 LIMIT 0, 1
602 SQL;
603
604 //Replace bundle entity name by table name
605 $req = str_replace($this->tableKeys, $this->tableValues, $req);
606
607 //Get result set mapping instance
608 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
609 $rsm = new ResultSetMapping();
610
611 //Declare all fields
612 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
613 //addScalarResult($sqlColName, $resColName, $type = 'string');
614 $rsm->addScalarResult('id', 'id', 'integer')
615 ->addScalarResult('title', 'title', 'string')
616 ->addScalarResult('city', 'city', 'string')
617 ->addScalarResult('latitude', 'latitude', 'float')
618 ->addScalarResult('longitude', 'longitude', 'float')
619 ->addScalarResult('indoor', 'indoor', 'boolean')
620 ->addScalarResult('zipcode', 'zipcode', 'string')
621 ->addScalarResult('updated', 'updated', 'datetime')
622 ->addScalarResult('city_id', 'city_id', 'integer')
623 ->addScalarResult('city_latitude', 'city_latitude', 'float')
624 ->addScalarResult('city_longitude', 'city_longitude', 'float')
625 ->addIndexByScalar('id');
626
627 //Get result
628 $result = $this->_em
629 ->createNativeQuery($req, $rsm)
630 ->setParameter('id', $id)
631 ->getOneOrNullResult();
632
633 //Without result
634 if ($result === null) {
635 //Return result
636 return $result;
637 }
638
639 //Set alternates
640 $result['alternates'] = [];
641
642 //Set route
643 $route = 'rapsysair_location_view';
644
645 //Set route params
646 $routeParams = ['id' => $id];
647
648 //Iterate on each languages
649 foreach($this->languages as $languageId => $language) {
650 //Without current locale
651 if ($languageId !== $locale) {
652 //Set titles
653 $titles = [];
654
655 //Set route params locale
656 $routeParams['_locale'] = $languageId;
657
658 //Set route params location
659 $routeParams['location'] = $this->slugger->slug($this->translator->trans($result['title'], [], null, $languageId));
660
661 //Iterate on each locales
662 foreach(array_keys($this->languages) as $other) {
663 //Without other locale
664 if ($other !== $languageId) {
665 //Set other locale title
666 $titles[$other] = $this->translator->trans($language, [], null, $other);
667 }
668 }
669
670 //Add alternates locale
671 $result['alternates'][substr($languageId, 0, 2)] = $result['alternates'][str_replace('_', '-', $languageId)] = [
672 'absolute' => $this->router->generate($route, $routeParams, UrlGeneratorInterface::ABSOLUTE_URL),
673 'relative' => $this->router->generate($route, $routeParams),
674 'title' => implode('/', $titles),
675 'translated' => $this->translator->trans($language, [], null, $languageId)
676 ];
677 }
678 }
679
680 //Return result
681 return [
682 'id' => $result['id'],
683 'city' => [
684 'id' => $result['city_id'],
685 'title' => $result['city'],
686 'in' => $this->translator->trans('in '.$result['city']),
687 'link' => $this->router->generate('rapsysair_city_view', ['city' => $result['city'], 'latitude' => $result['city_latitude'], 'longitude' => $result['city_longitude']])
688 ],
689 'title' => $title = $this->translator->trans($result['title']),
690 'latitude' => $result['latitude'],
691 'longitude' => $result['longitude'],
692 'indoor' => $result['indoor'],
693 'updated' => $result['updated'],
694 'around' => $this->translator->trans('around '.$result['title']),
695 'at' => $this->translator->trans('at '.$result['title']),
696 'atin' => $this->translator->trans('at '.$result['title']).' '.$this->translator->trans('in '.$result['city']),
697 'multimap' => $this->translator->trans($result['title'].' sector map'),
698 //XXX: Useless ???
699 'slug' => $slug = $this->slugger->slug($title),
700 'link' => $this->router->generate($route, ['_locale' => $locale, 'location' => $slug]+$routeParams),
701 'alternates' => $result['alternates']
702 ];
703 }
704
705 /**
706 * Find complementary locations by session id
707 *
708 * @param int $id The session id
709 * @return array The other locations
710 */
711 public function findComplementBySessionId(int $id): array {
712 //Fetch complement locations
713 $ret = $this->getEntityManager()
714 ->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')
715 ->setParameter('sid', $id)
716 ->getArrayResult();
717
718 //TODO: try to improve with:
719 #->addIndexByScalar('city');
720
721 //Rekey array
722 $ret = array_column($ret, 'id', 'title');
723
724 return $ret;
725 }
726
727 /**
728 * Find locations by user id
729 *
730 * @param int $id The user id
731 * @return array The user locations
732 */
733 public function findByUserId(int $userId): array {
734 //Set the request
735 $req = 'SELECT l.id, l.title
736 FROM Rapsys\AirBundle\Entity\UserLocation AS ul
737 JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = ul.location_id)
738 WHERE ul.user_id = :id';
739
740 //Replace bundle entity name by table name
741 $req = str_replace($this->tableKeys, $this->tableValues, $req);
742
743 //Get result set mapping instance
744 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
745 $rsm = new ResultSetMapping();
746
747 //Declare result set for our request
748 $rsm->addEntityResult('Rapsys\AirBundle\Entity\Location', 'l')
749 ->addFieldResult('l', 'id', 'id')
750 ->addFieldResult('l', 'title', 'title');
751
752 //Send result
753 return $this->_em
754 ->createNativeQuery($req, $rsm)
755 ->setParameter('id', $userId)
756 ->getResult();
757 }
758 }