Raphaël G. Git Repositories - airbundle/blob - 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 }