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 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 }