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