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 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
  16 use Symfony\Component\Routing\RouterInterface;
  17
  18 /**
  19  * LocationRepository
  20  *
  21  * @TODO: use new window function syntax https://mariadb.com/kb/en/window-functions-overview/ MAX(updated) OVER (PARTITION updated) AS modified ???
  22  */
  23 class LocationRepository extends Repository {
  24         /**
  25          * Find locations
  26          *
  27          * @return array
  28          */
  29         public function findAll(): array {
  30                 //Get all locations index by id
  31                 return $this->createQueryBuilder('location', 'location.id')->getQuery()->getResult();
  32         }
  33
  34         /**
  35          * Find locations as array
  36          *
  37          * @param DatePeriod $period The period
  38          * @return array The locations array
  39          */
  40         public function findAllAsArray(\DatePeriod $period): array {
  41                 //Set the request
  42                 //TODO: ajouter pays ???
  43                 $req = <<<SQL
  44 SELECT
  45         l.id,
  46         l.title,
  47         l.latitude,
  48         l.longitude,
  49         l.indoor,
  50         l.updated
  51 FROM RapsysAirBundle:Location AS l
  52 LEFT JOIN RapsysAirBundle:Session AS s ON (l.id = s.location_id)
  53 GROUP BY l.id
  54 ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, COUNT(s.id) DESC, l.id
  55 SQL;
  56
  57                 //Replace bundle entity name by table name
  58                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
  59
  60                 //Get result set mapping instance
  61                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
  62                 $rsm = new ResultSetMapping();
  63
  64                 //Declare all fields
  65                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
  66                 //addScalarResult($sqlColName, $resColName, $type = 'string');
  67                 $rsm->addScalarResult('id', 'id', 'integer')
  68                         ->addScalarResult('title', 'title', 'string')
  69                         ->addScalarResult('latitude', 'latitude', 'float')
  70                         ->addScalarResult('longitude', 'longitude', 'float')
  71                         ->addScalarResult('indoor', 'indoor', 'boolean')
  72                         ->addScalarResult('count', 'count', 'integer')
  73                         ->addScalarResult('updated', 'updated', 'datetime');
  74
  75                 //Get result
  76                 $result = $this->_em
  77                         ->createNativeQuery($req, $rsm)
  78                         ->setParameter('begin', $period->getStartDate())
  79                         ->setParameter('end', $period->getEndDate())
  80                         ->getArrayResult();
  81
  82                 //Set return
  83                 $return = [];
  84
  85                 //Iterate on each city
  86                 foreach($result as $data) {
  87                         //Add to return
  88                         $return[] = [
  89                                 'id' => $data['id'],
  90                                 'title' => $title = $this->translator->trans($data['title']),
  91                                 'latitude' => $data['latitude'],
  92                                 'longitude' => $data['longitude'],
  93                                 'updated' => $data['updated'],
  94                                 //XXX: Useless ???
  95                                 'slug' => $location = $this->slugger->slug($title),
  96                                 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $this->slugger->slug($location)])
  97                         ];
  98                 }
  99
 100                 //Return return
 101                 return $return;
 102         }
 103
 104         /**
 105          * Find cities as array
 106          *
 107          * @param DatePeriod $period The period
 108          * @param int $count The session count
 109          * @return array The cities array
 110          */
 111         public function findCitiesAsArray(\DatePeriod $period, int $count = 1): array {
 112                 //Set the request
 113                 $req = <<<SQL
 114 SELECT
 115         SUBSTRING(a.zipcode, 1, 2) AS id,
 116         a.city AS city,
 117         ROUND(AVG(a.latitude), 6) AS latitude,
 118         ROUND(AVG(a.longitude), 6) AS longitude,
 119         GROUP_CONCAT(a.id ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS ids,
 120         GROUP_CONCAT(a.title ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS titles,
 121         GROUP_CONCAT(a.latitude ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS latitudes,
 122         GROUP_CONCAT(a.longitude ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS longitudes,
 123         GROUP_CONCAT(a.indoor ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS indoors,
 124         GROUP_CONCAT(a.count ORDER BY a.pcount DESC, a.count DESC, a.id SEPARATOR "\\n") AS counts,
 125         MAX(a.updated) AS modified
 126 FROM (
 127         SELECT
 128                 l.id,
 129                 l.city,
 130                 l.title,
 131                 l.latitude,
 132                 l.longitude,
 133                 l.indoor,
 134                 l.updated,
 135                 l.zipcode,
 136                 COUNT(s.id) AS count,
 137                 COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) AS pcount
 138         FROM RapsysAirBundle:Location AS l
 139         LEFT JOIN RapsysAirBundle:Session AS s ON (l.id = s.location_id)
 140         GROUP BY l.id
 141         ORDER BY NULL
 142         LIMIT 0, :limit
 143 ) AS a
 144 GROUP BY a.city, SUBSTRING(a.zipcode, 1, 3)
 145 ORDER BY a.city, SUBSTRING(a.zipcode, 1, 3)
 146 SQL;
 147
 148                 //Replace bundle entity name by table name
 149                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 150
 151                 //Get result set mapping instance
 152                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 153                 $rsm = new ResultSetMapping();
 154
 155                 //Declare all fields
 156                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 157                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 158                 $rsm->addScalarResult('id', 'id', 'integer')
 159                         ->addScalarResult('city', 'city', 'string')
 160                         ->addScalarResult('latitude', 'latitude', 'float')
 161                         ->addScalarResult('longitude', 'longitude', 'float')
 162                         //XXX: is a string because of \n separator
 163                         ->addScalarResult('ids', 'ids', 'string')
 164                         //XXX: is a string because of \n separator
 165                         ->addScalarResult('titles', 'titles', 'string')
 166                         //XXX: is a string because of \n separator
 167                         ->addScalarResult('latitudes', 'latitudes', 'string')
 168                         //XXX: is a string because of \n separator
 169                         ->addScalarResult('longitudes', 'longitudes', 'string')
 170                         //XXX: is a string because of \n separator
 171                         ->addScalarResult('indoors', 'indoors', 'string')
 172                         //XXX: is a string because of \n separator
 173                         ->addScalarResult('counts', 'counts', 'string')
 174                         ->addScalarResult('modified', 'modified', 'datetime')
 175                         ->addIndexByScalar('city');
 176
 177                 //Get result
 178                 $result = $this->_em
 179                         ->createNativeQuery($req, $rsm)
 180                         ->setParameter('begin', $period->getStartDate())
 181                         ->setParameter('end', $period->getEndDate())
 182                         ->getArrayResult();
 183
 184                 //Set return
 185                 $return = [];
 186
 187                 //Iterate on each city
 188                 foreach($result as $city => $data) {
 189                         //Set titles
 190                         $titles = explode("\n", $data['titles']);
 191
 192                         //Set latitudes
 193                         $latitudes = explode("\n", $data['latitudes']);
 194
 195                         //Set longitudes
 196                         $longitudes = explode("\n", $data['longitudes']);
 197
 198                         //Set indoors
 199                         $indoors = explode("\n", $data['indoors']);
 200
 201                         //Set counts
 202                         $counts = explode("\n", $data['counts']);
 203
 204                         //With unsufficient count
 205                         if ($count && $counts[0] < $count) {
 206                                 //Skip empty city
 207                                 //XXX: count are sorted so only check first
 208                                 continue;
 209                         }
 210
 211                         //Set locations
 212                         $data['locations'] = [];
 213
 214                         //Iterate on each location
 215                         foreach(explode("\n", $data['ids']) as $k => $id) {
 216                                 //With unsufficient count
 217                                 if ($count && $counts[$k] < $count) {
 218                                         //Skip empty city
 219                                         //XXX: count are sorted so only check first
 220                                         continue;
 221                                 }
 222
 223                                 //Add location
 224                                 $data['locations'][] = [
 225                                         'id' => $id,
 226                                         'title' => $location = $this->translator->trans($titles[$k]),
 227                                         'latitude' => floatval($latitudes[$k]),
 228                                         'longitude' => floatval($longitudes[$k]),
 229                                         'indoor' => $indoors[$k] == 0 ? $this->translator->trans('outdoor') : $this->translator->trans('indoor'),
 230                                         'link' => $this->router->generate('rapsys_air_location_view', ['id' => $id, 'location' => $this->slugger->slug($location)])
 231                                 ];
 232                         }
 233
 234                         //Add to return
 235                         $return[$city] = [
 236                                 'id' => $data['id'],
 237                                 'city' => $data['city'],
 238                                 'in' => $this->translator->trans('in '.$data['city']),
 239                                 'indoors' => array_map(function ($v) { return $v == 0 ? $this->translator->trans('outdoor') : $this->translator->trans('indoor'); }, array_unique($indoors)),
 240                                 'multimap' => $this->translator->trans($data['city'].' sector map'),
 241                                 'latitude' => $data['latitude'],
 242                                 'longitude' => $data['longitude'],
 243                                 'modified' => $data['modified'],
 244                                 //XXX: Useless ???
 245                                 'slug' => $city = $this->slugger->slug($data['city']),
 246                                 'link' => $this->router->generate('rapsys_air_city_view', ['city' => $city, 'latitude' => $data['latitude'], 'longitude' => $data['longitude']]),
 247                                 'locations' => $data['locations']
 248                         ];
 249                 }
 250
 251                 //Return return
 252                 return $return;
 253         }
 254
 255         /**
 256          * Find city by latitude and longitude as array
 257          *
 258          * @param float $latitude The latitude
 259          * @param float $longitude The longitude
 260          * @return ?array The cities array
 261          */
 262         public function findCityByLatitudeLongitudeAsArray(float $latitude, float $longitude): ?array {
 263                 //Set the request
 264                 $req = <<<SQL
 265 SELECT
 266         SUBSTRING(l.zipcode, 1, 2) AS id,
 267         l.city AS city,
 268         ROUND(AVG(l.latitude), 6) AS latitude,
 269         ROUND(AVG(l.longitude), 6) AS longitude,
 270         MAX(l.updated) AS updated
 271 FROM RapsysAirBundle:Location AS l
 272 GROUP BY city, SUBSTRING(l.zipcode, 1, 3)
 273 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()
 274 LIMIT 0, 1
 275 SQL;
 276
 277                 //Replace bundle entity name by table name
 278                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 279
 280                 //Get result set mapping instance
 281                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 282                 $rsm = new ResultSetMapping();
 283
 284                 //Declare all fields
 285                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 286                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 287                 $rsm->addScalarResult('id', 'id', 'integer')
 288                         ->addScalarResult('city', 'city', 'string')
 289                         ->addScalarResult('latitude', 'latitude', 'float')
 290                         ->addScalarResult('longitude', 'longitude', 'float')
 291                         ->addScalarResult('updated', 'updated', 'datetime')
 292                         ->addIndexByScalar('city');
 293
 294                 //Get result
 295                 $result = $this->_em
 296                         ->createNativeQuery($req, $rsm)
 297                         ->setParameter('latitude', $latitude)
 298                         ->setParameter('longitude', $longitude)
 299                         ->getOneOrNullResult();
 300
 301                 //Without result
 302                 if ($result === null) {
 303                         //Return result
 304                         return $result;
 305                 }
 306
 307                 //Return result
 308                 return [
 309                         'id' => $result['id'],
 310                         'city' => $result['city'],
 311                         'latitude' => $result['latitude'],
 312                         'longitude' => $result['longitude'],
 313                         'updated' => $result['updated'],
 314                         'in' => $this->translator->trans('in '.$result['city']),
 315                         'multimap' => $this->translator->trans($result['city'].' sector map'),
 316                         //XXX: Useless ???
 317                         'slug' => $slug = $this->slugger->slug($result['city']),
 318                         'link' => $this->router->generate('rapsys_air_city_view', ['city' => $slug, 'latitude' => $result['latitude'], 'longitude' => $result['longitude']])
 319                 ];
 320         }
 321
 322         /**
 323          * Find locations by latitude and longitude sorted by period as array
 324          *
 325          * @TODO: find all other locations when current one has no sessions ???
 326          *
 327          * @param float $latitude The latitude
 328          * @param float $longitude The longitude
 329          * @param DatePeriod $period The period
 330          * @param int $count The session count
 331          * @param float $distance The distance
 332          * @return array The locations array
 333          */
 334         public function findAllByLatitudeLongitudeAsArray(float $latitude, float $longitude, \DatePeriod $period, int $count = 1, float $distance = 15): array {
 335                 //Set earth radius
 336                 $radius = 40030.17/2/pi();
 337
 338                 //Compute min latitude
 339                 $minlat = min(rad2deg(asin(sin(deg2rad($latitude))*cos($distance/$radius) + cos(deg2rad($latitude))*sin($distance/$radius)*cos(deg2rad(180)))), $latitude);
 340
 341                 //Compute max latitude
 342                 $maxlat = max(rad2deg(asin(sin(deg2rad($latitude))*cos($distance/$radius) + cos(deg2rad($latitude))*sin($distance/$radius)*cos(deg2rad(0)))), $latitude);
 343
 344                 //Compute min longitude
 345                 $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;
 346
 347                 //Compute max longi
 348                 $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;
 349
 350                 //Set the request
 351                 //TODO: see old request before commit to sort session count, distance and then by id ?
 352                 //TODO: see to sort by future session count, historical session count, distance and then by id ?
 353                 //TODO: do the same for cities and city ?
 354                 $req = <<<SQL
 355 SELECT
 356         a.id,
 357         a.title,
 358         a.latitude,
 359         a.longitude,
 360         a.updated,
 361         MAX(a.updated) AS modified,
 362         COUNT(s.id) AS count
 363 FROM (
 364         SELECT
 365                 l.id,
 366                 l.title,
 367                 l.latitude,
 368                 l.longitude,
 369                 l.updated
 370         FROM RapsysAirBundle:Location AS l
 371         WHERE l.latitude BETWEEN :minlat AND :maxlat AND l.longitude BETWEEN :minlong AND :maxlong
 372         LIMIT 0, :limit
 373 ) AS a
 374 LEFT JOIN RapsysAirBundle:Session s ON (s.location_id = a.id)
 375 GROUP BY a.id
 376 ORDER BY COUNT(IF(s.date BETWEEN :begin AND :end, s.id, NULL)) DESC, count DESC, a.id
 377 SQL;
 378
 379                 //Replace bundle entity name by table name
 380                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 381
 382                 //Get result set mapping instance
 383                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 384                 $rsm = new ResultSetMapping();
 385
 386                 //Declare all fields
 387                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 388                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 389                 $rsm->addScalarResult('id', 'id', 'integer')
 390                         ->addScalarResult('title', 'title', 'string')
 391                         ->addScalarResult('latitude', 'latitude', 'float')
 392                         ->addScalarResult('longitude', 'longitude', 'float')
 393                         ->addScalarResult('updated', 'updated', 'datetime')
 394                         ->addScalarResult('modified', 'modified', 'datetime')
 395                         ->addScalarResult('count', 'count', 'integer');
 396
 397                 //Get result
 398                 $result = $this->_em
 399                         ->createNativeQuery($req, $rsm)
 400                         ->setParameter('begin', $period->getStartDate())
 401                         ->setParameter('end', $period->getEndDate())
 402                         ->setParameter('minlat', $minlat)
 403                         ->setParameter('maxlat', $maxlat)
 404                         ->setParameter('minlong', $minlong)
 405                         ->setParameter('maxlong', $maxlong)
 406                         ->getArrayResult();
 407
 408                 //Set return
 409                 $return = [];
 410
 411                 //Iterate on each location
 412                 foreach($result as $id => $data) {
 413                         //With active locations
 414                         if ($count && $data['count'] < $count) {
 415                                 //Skip unactive locations
 416                                 continue;
 417                         }
 418
 419                         //Add location
 420                         $return[$id] = [
 421                                 'id' => $data['id'],
 422                                 'title' => $title = $this->translator->trans($data['title']),
 423                                 'latitude' => $data['latitude'],
 424                                 'longitude' => $data['longitude'],
 425                                 'updated' => $data['updated'],
 426                                 'modified' => $data['modified'],
 427                                 'count' => $data['count'],
 428                                 'slug' => $slug = $this->slugger->slug($title),
 429                                 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $slug])
 430                         ];
 431                 }
 432
 433                 //Return return
 434                 return $return;
 435         }
 436
 437         /**
 438          * Find locations by user id sorted by period as array
 439          *
 440          * @param int $userId The user id
 441          * @param DatePeriod $period The period
 442          * @return array The locations array
 443          */
 444         public function findAllByUserIdAsArray(int $userId, \DatePeriod $period, $distance = 15): array {
 445                 //Set the request
 446                 //TODO: ajouter pays ???
 447                 $req = <<<SQL
 448 SELECT
 449         a.id,
 450         a.title,
 451         a.city,
 452         a.latitude,
 453         a.longitude,
 454         a.updated,
 455         a.modified,
 456         a.pcount,
 457         COUNT(s3.id) AS tcount
 458 FROM (
 459         SELECT
 460                 b.id,
 461                 b.title,
 462                 b.city,
 463                 b.latitude,
 464                 b.longitude,
 465                 b.updated,
 466                 COUNT(s2.id) AS pcount,
 467                 MAX(b.updated) AS modified
 468         FROM (
 469                 SELECT
 470                         l2.id,
 471                         l2.city,
 472                         l2.title,
 473                         l2.latitude,
 474                         l2.longitude,
 475                         l2.updated
 476                 FROM (
 477                         SELECT
 478                                 l.id,
 479                                 l.latitude,
 480                                 l.longitude
 481                         FROM applications AS a
 482                         JOIN sessions AS s ON (s.id = a.session_id)
 483                         JOIN locations AS l ON (l.id = s.location_id)
 484                         WHERE a.user_id = :id
 485                         GROUP BY l.id
 486                         ORDER BY NULL
 487                         LIMIT 0, :limit
 488                 ) AS a
 489                 JOIN locations AS l2
 490                 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
 491                 GROUP BY l2.id
 492                 ORDER BY NULL
 493                 LIMIT 0, :limit
 494         ) AS b
 495         LEFT JOIN sessions AS s2 ON (s2.location_id = b.id AND s2.date BETWEEN :begin AND :end)
 496         GROUP BY b.id
 497         ORDER BY NULL
 498         LIMIT 0, :limit
 499 ) AS a
 500 LEFT JOIN sessions AS s3 ON (s3.location_id = a.id)
 501 GROUP BY a.id
 502 ORDER BY pcount DESC, tcount DESC, a.id
 503 SQL;
 504
 505                 //Replace bundle entity name by table name
 506                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 507
 508                 //Get result set mapping instance
 509                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 510                 $rsm = new ResultSetMapping();
 511
 512                 //Declare all fields
 513                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 514                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 515                 $rsm->addScalarResult('id', 'id', 'integer')
 516                         ->addScalarResult('title', 'title', 'string')
 517                         ->addScalarResult('city', 'city', 'string')
 518                         ->addScalarResult('latitude', 'latitude', 'float')
 519                         ->addScalarResult('longitude', 'longitude', 'float')
 520                         ->addScalarResult('updated', 'updated', 'datetime')
 521                         ->addScalarResult('pcount', 'pcount', 'integer')
 522                         ->addScalarResult('tcount', 'tcount', 'integer')
 523                         ->addScalarResult('modified', 'modified', 'datetime');
 524
 525                 //Get result
 526                 $result = $this->_em
 527                         ->createNativeQuery($req, $rsm)
 528                         ->setParameter('begin', $period->getStartDate())
 529                         ->setParameter('end', $period->getEndDate())
 530                         ->setParameter('id', $userId)
 531                         ->setParameter('distance', $distance)
 532                         ->getArrayResult();
 533
 534                 //Set return
 535                 $return = [];
 536
 537                 //Iterate on each location
 538                 foreach($result as $id => $data) {
 539                         //With active locations
 540                         if (!empty($result[0]['tcount']) && empty($data['tcount'])) {
 541                                 //Skip unactive locations
 542                                 break;
 543                         }
 544
 545                         //Add location
 546                         $return[$id] = [
 547                                 'id' => $data['id'],
 548                                 'city' => $data['city'],
 549                                 'title' => $title = $this->translator->trans($data['title']),
 550                                 'at' => $this->translator->trans('at '.$data['title']),
 551                                 'miniature' => $this->translator->trans($data['title'].' miniature'),
 552                                 'latitude' => $data['latitude'],
 553                                 'longitude' => $data['longitude'],
 554                                 'updated' => $data['updated'],
 555                                 'pcount' => $data['pcount'],
 556                                 'tcount' => $data['tcount'],
 557                                 'modified' => $data['modified'],
 558                                 'slug' => $slug = $this->slugger->slug($title),
 559                                 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $data['id'], 'location' => $slug])
 560                         ];
 561                 }
 562
 563                 //Return return
 564                 return $return;
 565         }
 566
 567         /**
 568          * Find location as array by id
 569          *
 570          * @param int $id The location id
 571          * @param string $locale The locale
 572          * @return array The location data
 573          */
 574         public function findOneByIdAsArray(int $id, string $locale): ?array {
 575                 //Set the request
 576                 $req = <<<SQL
 577 SELECT
 578         l.id,
 579         l.title,
 580         l.city,
 581         l.latitude,
 582         l.longitude,
 583         l.indoor,
 584         l.zipcode,
 585         MAX(l2.updated) AS updated,
 586         SUBSTRING(l.zipcode, 1, 2) AS city_id,
 587         ROUND(AVG(l2.latitude), 6) AS city_latitude,
 588         ROUND(AVG(l2.longitude), 6) AS city_longitude
 589 FROM RapsysAirBundle:Location AS l
 590 JOIN RapsysAirBundle:Location AS l2 ON (l2.city = l.city AND SUBSTRING(l.zipcode, 1, 3) = SUBSTRING(l.zipcode, 1, 3))
 591 WHERE l.id = :id
 592 GROUP BY l.id
 593 LIMIT 0, 1
 594 SQL;
 595
 596                 //Replace bundle entity name by table name
 597                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 598
 599                 //Get result set mapping instance
 600                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 601                 $rsm = new ResultSetMapping();
 602
 603                 //Declare all fields
 604                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 605                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 606                 $rsm->addScalarResult('id', 'id', 'integer')
 607                         ->addScalarResult('title', 'title', 'string')
 608                         ->addScalarResult('city', 'city', 'string')
 609                         ->addScalarResult('latitude', 'latitude', 'float')
 610                         ->addScalarResult('longitude', 'longitude', 'float')
 611                         ->addScalarResult('indoor', 'indoor', 'boolean')
 612                         ->addScalarResult('zipcode', 'zipcode', 'string')
 613                         ->addScalarResult('updated', 'updated', 'datetime')
 614                         ->addScalarResult('city_id', 'city_id', 'integer')
 615                         ->addScalarResult('city_latitude', 'city_latitude', 'float')
 616                         ->addScalarResult('city_longitude', 'city_longitude', 'float')
 617                         ->addIndexByScalar('id');
 618
 619                 //Get result
 620                 $result = $this->_em
 621                         ->createNativeQuery($req, $rsm)
 622                         ->setParameter('id', $id)
 623                         ->getOneOrNullResult();
 624
 625                 //Without result
 626                 if ($result === null) {
 627                         //Return result
 628                         return $result;
 629                 }
 630
 631                 //Set alternates
 632                 $result['alternates'] = [];
 633
 634                 //Set route
 635                 $route = 'rapsys_air_location_view';
 636
 637                 //Set route params
 638                 $routeParams = ['id' => $id];
 639
 640                 //Iterate on each languages
 641                 foreach($this->languages as $languageId => $language) {
 642                         //Without current locale
 643                         if ($languageId !== $locale) {
 644                                 //Set titles
 645                                 $titles = [];
 646
 647                                 //Set route params locale
 648                                 $routeParams['_locale'] = $languageId;
 649
 650                                 //Set route params location
 651                                 $routeParams['location'] = $this->slugger->slug($this->translator->trans($result['title'], [], null, $languageId));
 652
 653                                 //Iterate on each locales
 654                                 foreach(array_keys($this->languages) as $other) {
 655                                         //Without other locale
 656                                         if ($other !== $languageId) {
 657                                                 //Set other locale title
 658                                                 $titles[$other] = $this->translator->trans($language, [], null, $other);
 659                                         }
 660                                 }
 661
 662                                 //Add alternates locale
 663                                 $result['alternates'][substr($languageId, 0, 2)] = $result['alternates'][str_replace('_', '-', $languageId)] = [
 664                                         'absolute' => $this->router->generate($route, $routeParams, UrlGeneratorInterface::ABSOLUTE_URL),
 665                                         'relative' => $this->router->generate($route, $routeParams),
 666                                         'title' => implode('/', $titles),
 667                                         'translated' => $this->translator->trans($language, [], null, $languageId)
 668                                 ];
 669                         }
 670                 }
 671
 672                 //Return result
 673                 return [
 674                         'id' => $result['id'],
 675                         'city' => [
 676                                 'id' => $result['city_id'],
 677                                 'title' => $result['city'],
 678                                 'in' => $this->translator->trans('in '.$result['city']),
 679                                 'link' => $this->router->generate('rapsys_air_city_view', ['city' => $result['city'], 'latitude' => $result['city_latitude'], 'longitude' => $result['city_longitude']])
 680                         ],
 681                         'title' => $title = $this->translator->trans($result['title']),
 682                         'latitude' => $result['latitude'],
 683                         'longitude' => $result['longitude'],
 684                         'indoor' => $result['indoor'],
 685                         'updated' => $result['updated'],
 686                         'around' => $this->translator->trans('around '.$result['title']),
 687                         'at' => $this->translator->trans('at '.$result['title']),
 688                         'atin' => $this->translator->trans('at '.$result['title']).' '.$this->translator->trans('in '.$result['city']),
 689                         'multimap' => $this->translator->trans($result['title'].' sector map'),
 690                         //XXX: Useless ???
 691                         'slug' => $slug = $this->slugger->slug($title),
 692                         'link' => $this->router->generate($route, ['_locale' => $locale, 'location' => $slug]+$routeParams),
 693                         'alternates' => $result['alternates']
 694                 ];
 695         }
 696
 697         /**
 698          * Find complementary locations by session id
 699          *
 700          * @param int $id The session id
 701          * @return array The other locations
 702          */
 703         public function findComplementBySessionId(int $id): array {
 704                 //Fetch complement locations
 705                 $ret = $this->getEntityManager()
 706                           ->createQuery('SELECT l.id, l.title FROM RapsysAirBundle:Session s LEFT JOIN RapsysAirBundle:Session s2 WITH s2.id != s.id AND s2.slot = s.slot AND s2.date = s.date LEFT JOIN RapsysAirBundle:Location l WITH l.id != s.location AND (l.id != s2.location OR s2.location IS NULL) WHERE s.id = :sid GROUP BY l.id ORDER BY l.id')
 707                         ->setParameter('sid', $id)
 708                         ->getArrayResult();
 709
 710                 //TODO: try to improve with:
 711                 #->addIndexByScalar('city');
 712
 713                 //Rekey array
 714                 $ret = array_column($ret, 'id', 'title');
 715
 716                 return $ret;
 717         }
 718
 719         /**
 720          * Find locations by user id
 721          *
 722          * @param int $id The user id
 723          * @return array The user locations
 724          */
 725         public function findByUserId(int $userId): array {
 726                 //Set the request
 727                 $req = 'SELECT l.id, l.title
 728 FROM RapsysAirBundle:UserLocation AS ul
 729 JOIN RapsysAirBundle:Location AS l ON (l.id = ul.location_id)
 730 WHERE ul.user_id = :id';
 731
 732                 //Replace bundle entity name by table name
 733                 $req = str_replace($this->tableKeys, $this->tableValues, $req);
 734
 735                 //Get result set mapping instance
 736                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 737                 $rsm = new ResultSetMapping();
 738
 739                 //Declare result set for our request
 740                 $rsm->addEntityResult('RapsysAirBundle:Location', 'l')
 741                         ->addFieldResult('l', 'id', 'id')
 742                         ->addFieldResult('l', 'title', 'title');
 743
 744                 //Send result
 745                 return $this->_em
 746                         ->createNativeQuery($req, $rsm)
 747                         ->setParameter('id', $userId)
 748                         ->getResult();
 749         }
 750 }