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('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 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, 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 Rapsys\AirBundle\Entity\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 Rapsys\AirBundle\Entity\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 Rapsys\AirBundle\Entity\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 Rapsys\AirBundle\Entity\Location AS l
 593 JOIN Rapsys\AirBundle\Entity\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 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')
 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 Rapsys\AirBundle\Entity\UserLocation AS ul
 732 JOIN Rapsys\AirBundle\Entity\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('Rapsys\AirBundle\Entity\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 }