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