Raphaël G. Git Repositories - airbundle/blob - Repository/SessionRepository.php

   1 <?php
   2
   3 namespace Rapsys\AirBundle\Repository;
   4
   5 use Symfony\Component\Translation\TranslatorInterface;
   6 use Doctrine\DBAL\Types\Type;
   7 use Doctrine\ORM\Query\ResultSetMapping;
   8 use Doctrine\ORM\Query;
   9
  10 /**
  11  * SessionRepository
  12  */
  13 class SessionRepository extends \Doctrine\ORM\EntityRepository {
  14         ///Set accuweather max number of daily pages
  15         const ACCUWEATHER_DAILY = 12;
  16
  17         ///Set accuweather max number of hourly pages
  18         const ACCUWEATHER_HOURLY = 3;
  19
  20         ///Set guest delay
  21         const GUEST_DELAY = 2;
  22
  23         ///Set regular delay
  24         const REGULAR_DELAY = 3;
  25
  26         ///Set senior
  27         const SENIOR_DELAY = 4;
  28
  29         ///Set glyphs
  30         //TODO: document utf-8 codes ?
  31         const GLYPHS = [
  32                 //Slots
  33                 'Morning' => '🌅', #0001f305
  34                 'Afternoon' => '☀️', #2600
  35                 'Evening' => '🌇', #0001f307
  36                 'After' => '✨', #2728
  37                 //Weathers
  38                 'Cleary' => '☀', #2600
  39                 'Sunny' => '⛅', #26c5
  40                 'Cloudy' => '☁', #2601
  41                 'Winty' => '❄️', #2744
  42                 'Rainy' => '🌂', #0001f302
  43                 'Stormy' => '☔' #2614
  44         ];
  45
  46         /**
  47          * Find session by location, slot and date
  48          *
  49          * @param $location The location
  50          * @param $slot The slot
  51          * @param $date The datetime
  52          */
  53         public function findOneByLocationSlotDate($location, $slot, $date) {
  54                 //Return sessions
  55                 return $this->getEntityManager()
  56                         ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.slot = :slot AND s.date = :date)')
  57                         ->setParameter('location', $location)
  58                         ->setParameter('slot', $slot)
  59                         ->setParameter('date', $date)
  60                         ->getSingleResult();
  61         }
  62
  63         /**
  64          * Find sessions by date period
  65          *
  66          * @param $period The date period
  67          */
  68         public function findAllByDatePeriod($period) {
  69                 //Return sessions
  70                 return $this->getEntityManager()
  71                         ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE s.date BETWEEN :begin AND :end')
  72                         ->setParameter('begin', $period->getStartDate())
  73                         ->setParameter('end', $period->getEndDate())
  74                         ->getResult();
  75         }
  76
  77         /**
  78          * Find sessions by location and date period
  79          *
  80          * @param $location The location
  81          * @param $period The date period
  82          */
  83         public function findAllByLocationDatePeriod($location, $period) {
  84                 //Return sessions
  85                 return $this->getEntityManager()
  86                         ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.date BETWEEN :begin AND :end)')
  87                         ->setParameter('location', $location)
  88                         ->setParameter('begin', $period->getStartDate())
  89                         ->setParameter('end', $period->getEndDate())
  90                         ->getResult();
  91         }
  92
  93         /**
  94          * Find one session by location and user id within last month
  95          *
  96          * @param $location The location id
  97          * @param $user The user id
  98          */
  99         public function findOneWithinLastMonthByLocationUser($location, $user) {
 100                 //Get entity manager
 101                 $em = $this->getEntityManager();
 102
 103                 //Get quote strategy
 104                 $qs = $em->getConfiguration()->getQuoteStrategy();
 105                 $dp = $em->getConnection()->getDatabasePlatform();
 106
 107                 //Get quoted table names
 108                 //XXX: this allow to make this code table name independent
 109                 $tables = [
 110                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 111                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 112                         "\t" => '',
 113                         "\n" => ' '
 114                 ];
 115
 116                 //Set the request
 117                 //XXX: give the gooddelay to guest just in case
 118                 $req =<<<SQL
 119 SELECT s.id
 120 FROM RapsysAirBundle:Session s
 121 JOIN RapsysAirBundle:Application a ON (a.id = s.application_id AND a.user_id = :uid AND (a.canceled IS NULL OR TIMESTAMPDIFF(DAY, a.canceled, ADDTIME(s.date, s.begin)) < 1))
 122 WHERE s.location_id = :lid AND s.date >= DATE_ADD(DATE_SUB(NOW(), INTERVAL 1 MONTH), INTERVAL :gooddelay DAY)
 123 SQL;
 124
 125                 //Replace bundle entity name by table name
 126                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 127
 128                 //Get result set mapping instance
 129                 $rsm = new ResultSetMapping();
 130
 131                 //Declare all fields
 132                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 133                 $rsm->addScalarResult('id', 'id', 'integer')
 134                         ->addIndexByScalar('id');
 135
 136                 //Return result
 137                 return $em
 138                         ->createNativeQuery($req, $rsm)
 139                         ->setParameter('lid', $location)
 140                         ->setParameter('uid', $user)
 141                         ->setParameter('gooddelay', self::SENIOR_DELAY)
 142                         ->getOneOrNullResult();
 143         }
 144
 145         /**
 146          * Fetch sessions by date period
 147          *
 148          * @param $period The date period
 149          * @param $locale The locale
 150          */
 151         public function fetchAllByDatePeriod($period, $locale = null) {
 152                 //Get entity manager
 153                 $em = $this->getEntityManager();
 154
 155                 //Get quote strategy
 156                 $qs = $em->getConfiguration()->getQuoteStrategy();
 157                 $dp = $em->getConnection()->getDatabasePlatform();
 158
 159                 //Get quoted table names
 160                 //XXX: this allow to make this code table name independent
 161                 $tables = [
 162                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 163                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 164                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 165                         'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp),
 166                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
 167                         ':afterid' => 4,
 168                         "\t" => '',
 169                         "\n" => ' '
 170                 ];
 171
 172                 //Set the request
 173                 //TODO: exclude opera and others ?
 174                 $req = <<<SQL
 175 SELECT
 176         s.id,
 177         s.date,
 178         s.locked,
 179         s.updated,
 180         ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
 181         ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
 182         s.location_id AS l_id,
 183         l.address AS l_address,
 184         l.zipcode AS l_zipcode,
 185         l.city AS l_city,
 186         l.short AS l_short,
 187         l.title AS l_title,
 188         l.latitude AS l_latitude,
 189         l.longitude AS l_longitude,
 190         s.application_id AS a_id,
 191         a.canceled AS a_canceled,
 192         a.user_id AS au_id,
 193         au.forename AS au_forename,
 194         au.pseudonym AS au_pseudonym,
 195         p.id AS p_id,
 196         p.description AS p_description,
 197         p.class AS p_class,
 198         p.short AS p_short,
 199         p.rate AS p_rate,
 200         p.contact AS p_contact,
 201         p.donate AS p_donate,
 202         p.link AS p_link,
 203         p.profile AS p_profile
 204 FROM RapsysAirBundle:Session AS s
 205 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 206 JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 207 JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 208 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
 209 WHERE s.date BETWEEN :begin AND :end
 210 ORDER BY NULL
 211 SQL;
 212
 213                 //Replace bundle entity name by table name
 214                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 215
 216                 //Get result set mapping instance
 217                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 218                 $rsm = new ResultSetMapping();
 219
 220                 //Declare all fields
 221                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 222                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 223                 $rsm->addScalarResult('id', 'id', 'integer')
 224                         ->addScalarResult('date', 'date', 'date')
 225                         ->addScalarResult('locked', 'locked', 'datetime')
 226                         ->addScalarResult('updated', 'updated', 'datetime')
 227                         ->addScalarResult('start', 'start', 'datetime')
 228                         ->addScalarResult('stop', 'stop', 'datetime')
 229                         ->addScalarResult('l_id', 'l_id', 'integer')
 230                         ->addScalarResult('l_address', 'l_address', 'string')
 231                         ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
 232                         ->addScalarResult('l_city', 'l_city', 'string')
 233                         ->addScalarResult('l_latitude', 'l_latitude', 'float')
 234                         ->addScalarResult('l_longitude', 'l_longitude', 'float')
 235                         ->addScalarResult('l_short', 'l_short', 'string')
 236                         ->addScalarResult('l_title', 'l_title', 'string')
 237                         ->addScalarResult('t_id', 't_id', 'integer')
 238                         ->addScalarResult('t_title', 't_title', 'string')
 239                         ->addScalarResult('a_id', 'a_id', 'integer')
 240                         ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
 241                         ->addScalarResult('au_id', 'au_id', 'integer')
 242                         ->addScalarResult('au_forename', 'au_forename', 'string')
 243                         ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
 244                         ->addScalarResult('p_id', 'p_id', 'integer')
 245                         ->addScalarResult('p_description', 'p_description', 'string')
 246                         ->addScalarResult('p_class', 'p_class', 'string')
 247                         ->addScalarResult('p_short', 'p_short', 'string')
 248                         ->addScalarResult('p_rate', 'p_rate', 'integer')
 249                         ->addScalarResult('p_contact', 'p_contact', 'string')
 250                         ->addScalarResult('p_donate', 'p_donate', 'string')
 251                         ->addScalarResult('p_link', 'p_link', 'string')
 252                         ->addScalarResult('p_profile', 'p_profile', 'string')
 253                         ->addIndexByScalar('id');
 254
 255                 //Fetch result
 256                 $res = $em
 257                         ->createNativeQuery($req, $rsm)
 258                         ->setParameter('begin', $period->getStartDate())
 259                         ->setParameter('end', $period->getEndDate())
 260                         ->setParameter('locale', $locale);
 261
 262                 //Return result
 263                 return $res->getResult();
 264         }
 265
 266         /**
 267          * Fetch session by id
 268          *
 269          * @param $id The session id
 270          * @param $locale The locale
 271          * @return array The session data
 272          */
 273         public function fetchOneById($id, $locale = null) {
 274                 //Get entity manager
 275                 $em = $this->getEntityManager();
 276
 277                 //Get quote strategy
 278                 $qs = $em->getConfiguration()->getQuoteStrategy();
 279                 $dp = $em->getConnection()->getDatabasePlatform();
 280
 281                 //Get quoted table names
 282                 //XXX: this allow to make this code table name independent
 283                 $tables = [
 284                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 285                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 286                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 287                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 288                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 289                         'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp),
 290                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 291                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
 292                         ':afterid' => 4,
 293                         "\t" => '',
 294                         "\n" => ' '
 295                 ];
 296
 297                 //Set the request
 298                 //TODO: compute scores ?
 299                 //TODO: compute delivery date ? (J-3/J-4 ?)
 300                 $req =<<<SQL
 301 SELECT
 302         s.id,
 303         s.date,
 304         s.begin,
 305         ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
 306         s.length,
 307         ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
 308         s.rainfall,
 309         s.rainrisk,
 310         s.realfeel,
 311         s.realfeelmin,
 312         s.realfeelmax,
 313         s.temperature,
 314         s.temperaturemin,
 315         s.temperaturemax,
 316         s.locked,
 317         s.created,
 318         s.updated,
 319         s.location_id AS l_id,
 320         l.short AS l_short,
 321         l.title AS l_title,
 322         l.address AS l_address,
 323         l.zipcode AS l_zipcode,
 324         l.city AS l_city,
 325         l.latitude AS l_latitude,
 326         l.longitude AS l_longitude,
 327         l.updated AS l_updated,
 328         s.slot_id AS t_id,
 329         t.title AS t_title,
 330         t.updated AS t_updated,
 331         s.application_id AS a_id,
 332         a.canceled AS a_canceled,
 333         a.user_id AS au_id,
 334         au.pseudonym AS au_pseudonym,
 335         p.id AS p_id,
 336         p.description AS p_description,
 337         p.class AS p_class,
 338         p.contact AS p_contact,
 339         p.donate AS p_donate,
 340         p.link AS p_link,
 341         p.profile AS p_profile,
 342         p.rate AS p_rate,
 343         p.hat AS p_hat,
 344         p.updated AS p_updated,
 345         GROUP_CONCAT(sa.id ORDER BY sa.user_id SEPARATOR "\\n") AS sa_id,
 346         GROUP_CONCAT(IFNULL(sa.score, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_score,
 347         GROUP_CONCAT(sa.created ORDER BY sa.user_id SEPARATOR "\\n") AS sa_created,
 348         GROUP_CONCAT(sa.updated ORDER BY sa.user_id SEPARATOR "\\n") AS sa_updated,
 349         GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled,
 350         GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
 351         GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
 352 FROM RapsysAirBundle:Session AS s
 353 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 354 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 355 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 356 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 357 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
 358 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 359 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 360 WHERE s.id = :sid
 361 GROUP BY s.id
 362 ORDER BY NULL
 363 SQL;
 364
 365                 //Replace bundle entity name by table name
 366                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 367
 368                 //Get result set mapping instance
 369                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 370                 $rsm = new ResultSetMapping();
 371
 372                 //Declare all fields
 373                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 374                 $rsm->addScalarResult('id', 'id', 'integer')
 375                         ->addScalarResult('date', 'date', 'date')
 376                         ->addScalarResult('begin', 'begin', 'time')
 377                         ->addScalarResult('start', 'start', 'datetime')
 378                         ->addScalarResult('length', 'length', 'time')
 379                         ->addScalarResult('stop', 'stop', 'datetime')
 380                         ->addScalarResult('rainfall', 'rainfall', 'float')
 381                         ->addScalarResult('rainrisk', 'rainrisk', 'float')
 382                         ->addScalarResult('realfeel', 'realfeel', 'float')
 383                         ->addScalarResult('realfeelmin', 'realfeelmin', 'float')
 384                         ->addScalarResult('realfeelmax', 'realfeelmax', 'float')
 385                         ->addScalarResult('temperature', 'temperature', 'float')
 386                         ->addScalarResult('temperaturemin', 'temperaturemin', 'float')
 387                         ->addScalarResult('temperaturemax', 'temperaturemax', 'float')
 388                         ->addScalarResult('locked', 'locked', 'datetime')
 389                         ->addScalarResult('created', 'created', 'datetime')
 390                         ->addScalarResult('updated', 'updated', 'datetime')
 391                         ->addScalarResult('l_id', 'l_id', 'integer')
 392                         ->addScalarResult('l_short', 'l_short', 'string')
 393                         ->addScalarResult('l_title', 'l_title', 'string')
 394                         ->addScalarResult('l_address', 'l_address', 'string')
 395                         ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
 396                         ->addScalarResult('l_city', 'l_city', 'string')
 397                         ->addScalarResult('l_latitude', 'l_latitude', 'float')
 398                         ->addScalarResult('l_longitude', 'l_longitude', 'float')
 399                         ->addScalarResult('l_updated', 'l_updated', 'datetime')
 400                         ->addScalarResult('t_id', 't_id', 'integer')
 401                         ->addScalarResult('t_title', 't_title', 'string')
 402                         ->addScalarResult('t_updated', 't_updated', 'datetime')
 403                         ->addScalarResult('a_id', 'a_id', 'integer')
 404                         ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
 405                         ->addScalarResult('au_id', 'au_id', 'integer')
 406                         ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
 407                         ->addScalarResult('p_id', 'p_id', 'integer')
 408                         ->addScalarResult('p_description', 'p_description', 'text')
 409                         ->addScalarResult('p_class', 'p_class', 'text')
 410                         ->addScalarResult('p_contact', 'p_contact', 'text')
 411                         ->addScalarResult('p_donate', 'p_donate', 'text')
 412                         ->addScalarResult('p_link', 'p_link', 'text')
 413                         ->addScalarResult('p_profile', 'p_profile', 'text')
 414                         ->addScalarResult('p_rate', 'p_rate', 'integer')
 415                         ->addScalarResult('p_hat', 'p_hat', 'boolean')
 416                         ->addScalarResult('p_updated', 'p_updated', 'datetime')
 417                         //XXX: is a string because of \n separator
 418                         ->addScalarResult('sa_id', 'sa_id', 'string')
 419                         //XXX: is a string because of \n separator
 420                         ->addScalarResult('sa_score', 'sa_score', 'string')
 421                         //XXX: is a string because of \n separator
 422                         ->addScalarResult('sa_created', 'sa_created', 'string')
 423                         //XXX: is a string because of \n separator
 424                         ->addScalarResult('sa_updated', 'sa_updated', 'string')
 425                         //XXX: is a string because of \n separator
 426                         ->addScalarResult('sa_canceled', 'sa_canceled', 'string')
 427                         //XXX: is a string because of \n separator
 428                         ->addScalarResult('sau_id', 'sau_id', 'string')
 429                         //XXX: is a string because of \n separator
 430                         ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
 431                         ->addIndexByScalar('id');
 432
 433                 //Return result
 434                 return $em
 435                         ->createNativeQuery($req, $rsm)
 436                         ->setParameter('sid', $id)
 437                         ->setParameter('locale', $locale)
 438                         ->getOneOrNullResult();
 439         }
 440
 441         /**
 442          * Fetch sessions calendar with translated location by date period
 443          *
 444          * @param $translator The TranslatorInterface instance
 445          * @param $period The date period
 446          * @param $locationId The location id
 447          * @param $sessionId The session id
 448          * @param $granted The session is granted
 449          */
 450         public function fetchCalendarByDatePeriod(TranslatorInterface $translator, $period, $locationId = null, $sessionId = null, $granted = false, $locale = null) {
 451                 //Get entity manager
 452                 $em = $this->getEntityManager();
 453
 454                 //Get quote strategy
 455                 $qs = $em->getConfiguration()->getQuoteStrategy();
 456                 $dp = $em->getConnection()->getDatabasePlatform();
 457
 458                 //Get quoted table names
 459                 //XXX: this allow to make this code table name independent
 460                 $tables = [
 461                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 462                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 463                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 464                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 465                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 466                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 467                         'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp),
 468                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
 469                         ':afterid' => 4,
 470                         "\t" => '',
 471                         "\n" => ' '
 472                 ];
 473
 474                 //Init granted sql
 475                 $grantSql = '';
 476
 477                 //When granted is set
 478                 if (empty($granted)) {
 479                         //Set application and user as optional
 480                         $grantSql = 'LEFT ';
 481                 }
 482
 483                 //Init location sql
 484                 $locationSql = '';
 485
 486                 //When location id is set
 487                 if (!empty($locationId)) {
 488                         //Add location id clause
 489                         $locationSql = "\n\t".'AND s.location_id = :lid';
 490                 }
 491
 492                 //Set the request
 493                 $req = <<<SQL
 494
 495 SELECT
 496         s.id,
 497         s.date,
 498         s.rainrisk,
 499         s.rainfall,
 500         s.realfeel,
 501         s.temperature,
 502         s.locked,
 503         ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
 504         ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
 505         s.location_id AS l_id,
 506         l.short AS l_short,
 507         l.title AS l_title,
 508         s.slot_id AS t_id,
 509         t.title AS t_title,
 510         s.application_id AS a_id,
 511         a.canceled AS a_canceled,
 512         a.user_id AS au_id,
 513         au.pseudonym AS au_pseudonym,
 514         p.rate AS p_rate,
 515         p.hat AS p_hat,
 516         GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
 517         GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
 518 FROM RapsysAirBundle:Session AS s
 519 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 520 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 521 ${grantSql}JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 522 ${grantSql}JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 523 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 524 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
 525 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 526 WHERE s.date BETWEEN :begin AND :end${locationSql}
 527 GROUP BY s.id
 528 ORDER BY NULL
 529 SQL;
 530
 531                 //Replace bundle entity name by table name
 532                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 533
 534                 //Get result set mapping instance
 535                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 536                 $rsm = new ResultSetMapping();
 537
 538                 //Declare all fields
 539                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 540                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 541                 $rsm->addScalarResult('id', 'id', 'integer')
 542                         ->addScalarResult('date', 'date', 'date')
 543                         ->addScalarResult('rainrisk', 'rainrisk', 'float')
 544                         ->addScalarResult('rainfall', 'rainfall', 'float')
 545                         ->addScalarResult('realfeel', 'realfeel', 'float')
 546                         ->addScalarResult('temperature', 'temperature', 'float')
 547                         ->addScalarResult('locked', 'locked', 'datetime')
 548                         ->addScalarResult('start', 'start', 'datetime')
 549                         ->addScalarResult('stop', 'stop', 'datetime')
 550                         ->addScalarResult('t_id', 't_id', 'integer')
 551                         ->addScalarResult('t_title', 't_title', 'string')
 552                         ->addScalarResult('l_id', 'l_id', 'integer')
 553                         ->addScalarResult('l_short', 'l_short', 'string')
 554                         ->addScalarResult('l_title', 'l_title', 'string')
 555                         ->addScalarResult('a_id', 'a_id', 'integer')
 556                         ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
 557                         ->addScalarResult('au_id', 'au_id', 'integer')
 558                         ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
 559                         ->addScalarResult('p_rate', 'p_rate', 'integer')
 560                         ->addScalarResult('p_hat', 'p_hat', 'boolean')
 561                         //XXX: is a string because of \n separator
 562                         ->addScalarResult('sau_id', 'sau_id', 'string')
 563                         //XXX: is a string because of \n separator
 564                         ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
 565                         ->addIndexByScalar('id');
 566
 567                 //Fetch result
 568                 $res = $em
 569                         ->createNativeQuery($req, $rsm)
 570                         ->setParameter('begin', $period->getStartDate())
 571                         ->setParameter('end', $period->getEndDate())
 572                         ->setParameter('locale', $locale);
 573
 574                 //Add optional location id
 575                 if (!empty($locationId)) {
 576                         $res->setParameter('lid', $locationId);
 577                 }
 578
 579                 //Get result
 580                 $res = $res->getResult();
 581
 582                 //Init calendar
 583                 $calendar = [];
 584
 585                 //Init month
 586                 $month = null;
 587
 588                 //Iterate on each day
 589                 foreach($period as $date) {
 590                         //Init day in calendar
 591                         $calendar[$Ymd = $date->format('Ymd')] = [
 592                                 'title' => $translator->trans($date->format('l')).' '.$date->format('d'),
 593                                 'class' => [],
 594                                 'sessions' => []
 595                         ];
 596
 597                         //Detect month change
 598                         if ($month != $date->format('m')) {
 599                                 $month = $date->format('m');
 600                                 //Append month for first day of month
 601                                 //XXX: except if today to avoid double add
 602                                 if ($date->format('U') != strtotime('today')) {
 603                                         $calendar[$Ymd]['title'] .= '/'.$month;
 604                                 }
 605                         }
 606                         //Deal with today
 607                         if ($date->format('U') == ($today = strtotime('today'))) {
 608                                 $calendar[$Ymd]['title'] .= '/'.$month;
 609                                 $calendar[$Ymd]['current'] = true;
 610                                 $calendar[$Ymd]['class'][] = 'current';
 611                         }
 612                         //Disable passed days
 613                         if ($date->format('U') < $today) {
 614                                 $calendar[$Ymd]['disabled'] = true;
 615                                 $calendar[$Ymd]['class'][] = 'disabled';
 616                         }
 617                         //Set next month days
 618                         if ($date->format('m') > date('m')) {
 619                                 $calendar[$Ymd]['next'] = true;
 620                                 #$calendar[$Ymd]['class'][] = 'next';
 621                         }
 622
 623                         //Detect sunday
 624                         if ($date->format('w') == 0) {
 625                                 $calendar[$Ymd]['class'][] = 'sunday';
 626                         }
 627
 628                         //Iterate on each session to find the one of the day
 629                         foreach($res as $session) {
 630                                 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
 631                                         //Count number of application
 632                                         $count = count(explode("\n", $session['sau_id']));
 633
 634                                         //Compute classes
 635                                         $class = [];
 636                                         if (!empty($session['a_id'])) {
 637                                                 $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
 638                                                 if (!empty($session['a_canceled'])) {
 639                                                         $class[] = 'canceled';
 640                                                 } else {
 641                                                         $class[] = 'granted';
 642                                                 }
 643                                         } elseif ($count > 1) {
 644                                                 $class[] = 'disputed';
 645                                         } elseif (!empty($session['locked'])) {
 646                                                 $class[] = 'locked';
 647                                         } else {
 648                                                 $class[] = 'pending';
 649                                         }
 650
 651                                         if ($sessionId == $session['id']) {
 652                                                 $class[] = 'highlight';
 653                                         }
 654
 655                                         //Set temperature
 656                                         //XXX: realfeel may be null, temperature should not
 657                                         $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
 658
 659                                         //Compute weather
 660                                         //XXX: rainfall may be null
 661                                         if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
 662                                                 $weather = self::GLYPHS['Stormy'];
 663                                         } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
 664                                                 $weather = self::GLYPHS['Rainy'];
 665                                         } elseif ($temperature > 24) {
 666                                                 $weather = self::GLYPHS['Cleary'];
 667                                         } elseif ($temperature > 17) {
 668                                                 $weather = self::GLYPHS['Sunny'];
 669                                         } elseif ($temperature > 10) {
 670                                                 $weather = self::GLYPHS['Cloudy'];
 671                                         } elseif ($temperature !== null) {
 672                                                 $weather = self::GLYPHS['Winty'];
 673                                         } else {
 674                                                 $weather = null;
 675                                         }
 676
 677                                         //Init weathertitle
 678                                         $weathertitle = [];
 679
 680                                         //Check if realfeel is available
 681                                         if ($session['realfeel'] !== null) {
 682                                                 $weathertitle[] = $session['realfeel'].'°R';
 683                                         }
 684
 685                                         //Check if temperature is available
 686                                         if ($session['temperature'] !== null) {
 687                                                 $weathertitle[] = $session['temperature'].'°C';
 688                                         }
 689
 690                                         //Check if rainrisk is available
 691                                         if ($session['rainrisk'] !== null) {
 692                                                 $weathertitle[] = ($session['rainrisk']*100).'%';
 693                                         }
 694
 695                                         //Check if rainfall is available
 696                                         if ($session['rainfall'] !== null) {
 697                                                 $weathertitle[] = $session['rainfall'].'mm';
 698                                         }
 699
 700                                         //Set applications
 701                                         $applications = [
 702                                                 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).$translator->trans(':')
 703                                         ];
 704
 705                                         //Fetch pseudonyms from session applications
 706                                         $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
 707
 708                                         //Set pseudonym
 709                                         $pseudonym = null;
 710
 711                                         //Check that session is not granted
 712                                         if (empty($session['a_id'])) {
 713                                                 //With location id and unique application
 714                                                 if ($count == 1) {
 715                                                         //Set unique application pseudonym
 716                                                         $pseudonym = $session['sau_pseudonym'];
 717                                                 }
 718                                         //Session is granted
 719                                         } else {
 720                                                 //Replace granted application
 721                                                 $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
 722
 723                                                 //Set pseudonym
 724                                                 $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
 725                                         }
 726
 727                                         //Add the session
 728                                         $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%05d', $session['id'])] = [
 729                                                 'id' => $session['id'],
 730                                                 'start' => $session['start'],
 731                                                 'stop' => $session['stop'],
 732                                                 'location' => $translator->trans($session['l_short']),
 733                                                 'pseudonym' => $pseudonym,
 734                                                 'class' => $class,
 735                                                 'slot' => self::GLYPHS[$session['t_title']],
 736                                                 'slottitle' => $translator->trans($session['t_title']),
 737                                                 'weather' => $weather,
 738                                                 'weathertitle' => implode(' ', $weathertitle),
 739                                                 'applications' => $applications,
 740                                                 'rate' => $session['p_rate'],
 741                                                 'hat' => $session['p_hat']
 742                                         ];
 743                                 }
 744                         }
 745
 746                         //Sort sessions
 747                         ksort($calendar[$Ymd]['sessions']);
 748                 }
 749
 750                 //Send result
 751                 return $calendar;
 752         }
 753
 754         /**
 755          * Fetch sessions calendar with translated location by date period and user
 756          *
 757          * @param $translator The TranslatorInterface instance
 758          * @param $period The date period
 759          * @param $userId The user id
 760          * @param $sessionId The session id
 761          */
 762         public function fetchUserCalendarByDatePeriod(TranslatorInterface $translator, $period, $userId = null, $sessionId = null, $locale = null) {
 763                 //Get entity manager
 764                 $em = $this->getEntityManager();
 765
 766                 //Get quote strategy
 767                 $qs = $em->getConfiguration()->getQuoteStrategy();
 768                 $dp = $em->getConnection()->getDatabasePlatform();
 769
 770                 //Get quoted table names
 771                 //XXX: this allow to make this code table name independent
 772                 $tables = [
 773                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 774                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 775                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 776                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 777                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 778                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 779                         'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp),
 780                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
 781                         ':afterid' => 4,
 782                         "\t" => '',
 783                         "\n" => ' '
 784                 ];
 785
 786                 //Init user sql
 787                 $userJoinSql = $userWhereSql = '';
 788
 789                 //When user id is set
 790                 if (!empty($userId)) {
 791                         //Add user join
 792                         $userJoinSql = 'JOIN RapsysAirBundle:Application AS sua ON (sua.session_id = s.id)'."\n";
 793                         //Add user id clause
 794                         $userWhereSql = "\n\t".'AND sua.user_id = :uid';
 795                 }
 796
 797                 //Set the request
 798                 //TODO: change as_u_* in sau_*, a_u_* in au_*, etc, see request up
 799                 $req = <<<SQL
 800 SELECT
 801         s.id,
 802         s.date,
 803         s.rainrisk,
 804         s.rainfall,
 805         s.realfeel,
 806         s.temperature,
 807         s.locked,
 808         ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
 809         ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
 810         s.location_id AS l_id,
 811         l.short AS l_short,
 812         l.title AS l_title,
 813         s.slot_id AS t_id,
 814         t.title AS t_title,
 815         s.application_id AS a_id,
 816         a.user_id AS au_id,
 817         au.pseudonym AS au_pseudonym,
 818         p.rate AS p_rate,
 819         p.hat AS p_hat,
 820         GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
 821         GROUP_CONCAT(CONCAT("- ", sau.pseudonym) ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
 822 FROM RapsysAirBundle:Session AS s
 823 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 824 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 825 ${userJoinSql}LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 826 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
 827 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 828 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 829 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 830 WHERE s.date BETWEEN :begin AND :end${userWhereSql}
 831 GROUP BY s.id
 832 ORDER BY NULL
 833 SQL;
 834
 835                 //Replace bundle entity name by table name
 836                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 837
 838                 //Get result set mapping instance
 839                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 840                 $rsm = new ResultSetMapping();
 841
 842                 //Declare all fields
 843                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 844                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 845                 $rsm->addScalarResult('id', 'id', 'integer')
 846                         ->addScalarResult('date', 'date', 'date')
 847                         ->addScalarResult('rainrisk', 'rainrisk', 'float')
 848                         ->addScalarResult('rainfall', 'rainfall', 'float')
 849                         ->addScalarResult('realfeel', 'realfeel', 'float')
 850                         ->addScalarResult('temperature', 'temperature', 'float')
 851                         ->addScalarResult('locked', 'locked', 'datetime')
 852                         ->addScalarResult('start', 'start', 'datetime')
 853                         ->addScalarResult('stop', 'stop', 'datetime')
 854                         ->addScalarResult('t_id', 't_id', 'integer')
 855                         ->addScalarResult('t_title', 't_title', 'string')
 856                         ->addScalarResult('l_id', 'l_id', 'integer')
 857                         ->addScalarResult('l_short', 'l_short', 'string')
 858                         ->addScalarResult('l_title', 'l_title', 'string')
 859                         ->addScalarResult('a_id', 'a_id', 'integer')
 860                         ->addScalarResult('au_id', 'au_id', 'integer')
 861                         ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
 862                         ->addScalarResult('p_rate', 'p_rate', 'integer')
 863                         ->addScalarResult('p_hat', 'p_hat', 'boolean')
 864                         //XXX: is a string because of \n separator
 865                         ->addScalarResult('sau_id', 'sau_id', 'string')
 866                         //XXX: is a string because of \n separator
 867                         ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
 868                         ->addIndexByScalar('id');
 869
 870                 //Fetch result
 871                 $res = $em
 872                         ->createNativeQuery($req, $rsm)
 873                         ->setParameter('begin', $period->getStartDate())
 874                         ->setParameter('end', $period->getEndDate())
 875                         ->setParameter('uid', $userId)
 876                         ->setParameter('locale', $locale)
 877                         ->getResult();
 878
 879                 //Init calendar
 880                 $calendar = [];
 881
 882                 //Init month
 883                 $month = null;
 884
 885                 //Iterate on each day
 886                 foreach($period as $date) {
 887                         //Init day in calendar
 888                         $calendar[$Ymd = $date->format('Ymd')] = [
 889                                 'title' => $translator->trans($date->format('l')).' '.$date->format('d'),
 890                                 'class' => [],
 891                                 'sessions' => []
 892                         ];
 893
 894                         //Detect month change
 895                         if ($month != $date->format('m')) {
 896                                 $month = $date->format('m');
 897                                 //Append month for first day of month
 898                                 //XXX: except if today to avoid double add
 899                                 if ($date->format('U') != strtotime('today')) {
 900                                         $calendar[$Ymd]['title'] .= '/'.$month;
 901                                 }
 902                         }
 903                         //Deal with today
 904                         if ($date->format('U') == ($today = strtotime('today'))) {
 905                                 $calendar[$Ymd]['title'] .= '/'.$month;
 906                                 $calendar[$Ymd]['current'] = true;
 907                                 $calendar[$Ymd]['class'][] = 'current';
 908                         }
 909                         //Disable passed days
 910                         if ($date->format('U') < $today) {
 911                                 $calendar[$Ymd]['disabled'] = true;
 912                                 $calendar[$Ymd]['class'][] = 'disabled';
 913                         }
 914                         //Set next month days
 915                         if ($date->format('m') > date('m')) {
 916                                 $calendar[$Ymd]['next'] = true;
 917                                 #$calendar[$Ymd]['class'][] = 'next';
 918                         }
 919
 920                         //Detect sunday
 921                         if ($date->format('w') == 0) {
 922                                 $calendar[$Ymd]['class'][] = 'sunday';
 923                         }
 924
 925                         //Iterate on each session to find the one of the day
 926                         foreach($res as $session) {
 927                                 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
 928                                         //Count number of application
 929                                         $count = count(explode("\n", $session['sau_id']));
 930
 931                                         //Compute classes
 932                                         $class = [];
 933                                         if (!empty($session['a_id'])) {
 934                                                 $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
 935                                                 if ($session['au_id'] == $userId) {
 936                                                         $class[] = 'granted';
 937                                                 } else {
 938                                                         $class[] = 'disputed';
 939                                                 }
 940                                         } elseif ($count > 1) {
 941                                                 $class[] = 'disputed';
 942                                         } elseif (!empty($session['locked'])) {
 943                                                 $class[] = 'locked';
 944                                         } else {
 945                                                 $class[] = 'pending';
 946                                         }
 947
 948                                         if ($sessionId == $session['id']) {
 949                                                 $class[] = 'highlight';
 950                                         }
 951
 952                                         //Set temperature
 953                                         //XXX: realfeel may be null, temperature should not
 954                                         $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
 955
 956                                         //Compute weather
 957                                         //XXX: rainfall may be null
 958                                         if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
 959                                                 $weather = self::GLYPHS['Stormy'];
 960                                         } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
 961                                                 $weather = self::GLYPHS['Rainy'];
 962                                         } elseif ($temperature > 24) {
 963                                                 $weather = self::GLYPHS['Cleary'];
 964                                         } elseif ($temperature > 17) {
 965                                                 $weather = self::GLYPHS['Sunny'];
 966                                         } elseif ($temperature > 10) {
 967                                                 $weather = self::GLYPHS['Cloudy'];
 968                                         } elseif ($temperature !== null) {
 969                                                 $weather = self::GLYPHS['Winty'];
 970                                         } else {
 971                                                 $weather = null;
 972                                         }
 973
 974                                         //Init weathertitle
 975                                         $weathertitle = [];
 976
 977                                         //Check if realfeel is available
 978                                         if ($session['realfeel'] !== null) {
 979                                                 $weathertitle[] = $session['realfeel'].'°R';
 980                                         }
 981
 982                                         //Check if temperature is available
 983                                         if ($session['temperature'] !== null) {
 984                                                 $weathertitle[] = $session['temperature'].'°C';
 985                                         }
 986
 987                                         //Check if rainrisk is available
 988                                         if ($session['rainrisk'] !== null) {
 989                                                 $weathertitle[] = ($session['rainrisk']*100).'%';
 990                                         }
 991
 992                                         //Check if rainfall is available
 993                                         if ($session['rainfall'] !== null) {
 994                                                 $weathertitle[] = $session['rainfall'].'mm';
 995                                         }
 996
 997                                         //Set applications
 998                                         $applications = [
 999                                                 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).$translator->trans(':')
1000                                         ];
1001
1002                                         //Fetch pseudonyms from session applications
1003                                         $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
1004
1005                                         //Set pseudonym
1006                                         $pseudonym = null;
1007
1008                                         //Check that session is not granted
1009                                         if (empty($session['a_id'])) {
1010                                                 //With location id and unique application
1011                                                 if ($count == 1) {
1012                                                         //Set unique application pseudonym
1013                                                         $pseudonym = $session['sau_pseudonym'];
1014                                                 }
1015                                         //Session is granted
1016                                         } else {
1017                                                 //Replace granted application
1018                                                 $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
1019
1020                                                 //Set pseudonym
1021                                                 $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
1022                                         }
1023
1024                                         //Set title
1025                                         $title = $translator->trans($session['l_title']).($count > 1 ? ' ['.$count.']':'');
1026
1027                                         //Add the session
1028                                         $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [
1029                                                 'id' => $session['id'],
1030                                                 'start' => $session['start'],
1031                                                 'stop' => $session['stop'],
1032                                                 'location' => $translator->trans($session['l_short']),
1033                                                 'pseudonym' => $pseudonym,
1034                                                 'class' => $class,
1035                                                 'slot' => self::GLYPHS[$session['t_title']],
1036                                                 'slottitle' => $translator->trans($session['t_title']),
1037                                                 'weather' => $weather,
1038                                                 'weathertitle' => implode(' ', $weathertitle),
1039                                                 'applications' => $applications,
1040                                                 'rate' => $session['p_rate'],
1041                                                 'hat' => $session['p_hat']
1042                                         ];
1043                                 }
1044                         }
1045
1046                         //Sort sessions
1047                         ksort($calendar[$Ymd]['sessions']);
1048                 }
1049
1050                 //Send result
1051                 return $calendar;
1052         }
1053
1054         /**
1055          * Find all session pending hourly weather
1056          *
1057          * @return array<Session> The sessions to update
1058          */
1059         public function findAllPendingHourlyWeather() {
1060                 //Get entity manager
1061                 $em = $this->getEntityManager();
1062
1063                 //Get quote strategy
1064                 $qs = $em->getConfiguration()->getQuoteStrategy();
1065                 $dp = $em->getConnection()->getDatabasePlatform();
1066
1067                 //Get quoted table names
1068                 //XXX: this allow to make this code table name independent
1069                 $tables = [
1070                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1071                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
1072                         //Accuweather
1073                         ':accuhourly' => self::ACCUWEATHER_HOURLY,
1074                         //Delay
1075                         ':afterid' => 4,
1076                         "\t" => '',
1077                         "\n" => ' '
1078                 ];
1079
1080                 //Select all sessions starting and stopping in the next 3 days
1081                 //XXX: select session starting after now and stopping before date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
1082                 $req = <<<SQL
1083 SELECT s.id, s.slot_id, s.location_id, s.date, s.begin, s.length, s.rainfall, s.rainrisk, s.realfeel, s.realfeelmin, s.realfeelmax, s.temperature, s.temperaturemin, s.temperaturemax, l.zipcode
1084 FROM RapsysAirBundle:Session AS s
1085 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1086 WHERE ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) >= NOW() AND ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) < DATE(ADDDATE(NOW(), INTERVAL :accuhourly DAY))
1087 SQL;
1088
1089                 //Replace bundle entity name by table name
1090                 $req = str_replace(array_keys($tables), array_values($tables), $req);
1091
1092                 //Get result set mapping instance
1093                 $rsm = new ResultSetMapping();
1094
1095                 //Declare all fields
1096                 $rsm
1097                         ->addEntityResult('RapsysAirBundle:Session', 's')
1098                         ->addFieldResult('s', 'id', 'id')
1099                         ->addFieldResult('s', 'date', 'date')
1100                         ->addFieldResult('s', 'begin', 'begin')
1101                         ->addFieldResult('s', 'length', 'length')
1102                         ->addFieldResult('s', 'rainfall', 'rainfall')
1103                         ->addFieldResult('s', 'rainrisk', 'rainrisk')
1104                         ->addFieldResult('s', 'realfeel', 'realfeel')
1105                         ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1106                         ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1107                         ->addFieldResult('s', 'temperature', 'temperature')
1108                         ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1109                         ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1110                         ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
1111                         ->addFieldResult('o', 'slot_id', 'id')
1112                         ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
1113                         ->addFieldResult('l', 'location_id', 'id')
1114                         ->addFieldResult('l', 'zipcode', 'zipcode')
1115                         ->addIndexBy('s', 'id');
1116
1117                 //Send result
1118                 return $em
1119                         ->createNativeQuery($req, $rsm)
1120                         ->getResult();
1121         }
1122
1123         /**
1124          * Find all session pending daily weather
1125          *
1126          * @return array<Session> The sessions to update
1127          */
1128         public function findAllPendingDailyWeather() {
1129                 //Get entity manager
1130                 $em = $this->getEntityManager();
1131
1132                 //Get quote strategy
1133                 $qs = $em->getConfiguration()->getQuoteStrategy();
1134                 $dp = $em->getConnection()->getDatabasePlatform();
1135
1136                 //Get quoted table names
1137                 //XXX: this allow to make this code table name independent
1138                 $tables = [
1139                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1140                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
1141                         //Accuweather
1142                         ':accudaily' => self::ACCUWEATHER_DAILY,
1143                         ':accuhourly' => self::ACCUWEATHER_HOURLY,
1144                         //Delay
1145                         ':afterid' => 4,
1146                         "\t" => '',
1147                         "\n" => ' '
1148                 ];
1149
1150                 //Select all sessions stopping after next 3 days
1151                 //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
1152                 $req = <<<SQL
1153 SELECT s.id, s.slot_id, s.location_id, s.date, s.begin, s.length, s.rainfall, s.rainrisk, s.realfeel, s.realfeelmin, s.realfeelmax, s.temperature, s.temperaturemin, s.temperaturemax, l.zipcode
1154 FROM RapsysAirBundle:Session AS s
1155 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1156 WHERE ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) >= DATE(ADDDATE(NOW(), INTERVAL :accuhourly DAY)) AND ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) < DATE(ADDDATE(NOW(), INTERVAL :accudaily DAY))
1157 SQL;
1158
1159                 //Replace bundle entity name by table name
1160                 $req = str_replace(array_keys($tables), array_values($tables), $req);
1161
1162                 //Get result set mapping instance
1163                 $rsm = new ResultSetMapping();
1164
1165                 //Declare all fields
1166                 $rsm
1167                         ->addEntityResult('RapsysAirBundle:Session', 's')
1168                         ->addFieldResult('s', 'id', 'id')
1169                         ->addFieldResult('s', 'date', 'date')
1170                         ->addFieldResult('s', 'begin', 'begin')
1171                         ->addFieldResult('s', 'length', 'length')
1172                         ->addFieldResult('s', 'rainfall', 'rainfall')
1173                         ->addFieldResult('s', 'rainrisk', 'rainrisk')
1174                         ->addFieldResult('s', 'realfeel', 'realfeel')
1175                         ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1176                         ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1177                         ->addFieldResult('s', 'temperature', 'temperature')
1178                         ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1179                         ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1180                         ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
1181                         ->addFieldResult('o', 'slot_id', 'id')
1182                         ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
1183                         ->addFieldResult('l', 'location_id', 'id')
1184                         ->addFieldResult('l', 'zipcode', 'zipcode')
1185                         ->addIndexBy('s', 'id');
1186
1187                 //Send result
1188                 return $em
1189                         ->createNativeQuery($req, $rsm)
1190                         ->getResult();
1191         }
1192
1193         /**
1194          * Find every session pending application
1195          *
1196          * @return array<Session> The sessions to update
1197          */
1198         public function findAllPendingApplication() {
1199                 //Get entity manager
1200                 $em = $this->getEntityManager();
1201
1202                 //Get quote strategy
1203                 $qs = $em->getConfiguration()->getQuoteStrategy();
1204                 $dp = $em->getConnection()->getDatabasePlatform();
1205
1206                 //Get quoted table names
1207                 //XXX: this allow to make this code table name independent
1208                 $tables = [
1209                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
1210                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1211                         //Delay
1212                         ':regulardelay' => self::REGULAR_DELAY * 24 * 3600,
1213                         ':seniordelay' => self::SENIOR_DELAY * 24 * 3600,
1214                         //Slot
1215                         ':afterid' => 4,
1216                         "\t" => '',
1217                         "\n" => ' '
1218                 ];
1219
1220                 //Select all sessions not locked without application or canceled application within attribution period
1221                 //XXX: DIFF(start, now) <= IF(DIFF(start, created) <= SENIOR_DELAY in DAY, DIFF(start, created) * 3 / 4, SENIOR_DELAY)
1222                 //TODO: remonter les données pour le mail ?
1223                 $req =<<<SQL
1224 SELECT s.id
1225 FROM RapsysAirBundle:Session as s
1226 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id AND a.canceled IS NULL)
1227 JOIN RapsysAirBundle:Application AS a2 ON (a2.session_id = s.id AND a2.canceled IS NULL)
1228 WHERE s.locked IS NULL AND a.id IS NULL AND
1229 TIME_TO_SEC(TIMEDIFF(@dt_start := ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) <= IF(
1230         TIME_TO_SEC(@td_sc := TIMEDIFF(@dt_start, s.created)) <= :seniordelay,
1231         ROUND(TIME_TO_SEC(@td_sc) * :regulardelay / :seniordelay),
1232         :seniordelay
1233 )
1234 GROUP BY s.id
1235 ORDER BY @dt_start ASC, s.created ASC
1236 SQL;
1237
1238
1239                 //Replace bundle entity name by table name
1240                 $req = str_replace(array_keys($tables), array_values($tables), $req);
1241
1242                 //Get result set mapping instance
1243                 $rsm = new ResultSetMapping();
1244
1245                 //Declare all fields
1246                 $rsm
1247                         ->addEntityResult('RapsysAirBundle:Session', 's')
1248                         ->addFieldResult('s', 'id', 'id')
1249                         ->addIndexBy('s', 'id');
1250
1251                 //Send result
1252                 return $em
1253                         ->createNativeQuery($req, $rsm)
1254                         ->getResult();
1255         }
1256
1257         /**
1258          * Fetch session best application by session id
1259          *
1260          * @param int $id The session id
1261          * @return Application|null The application or null
1262          */
1263         public function findBestApplicationById($id) {
1264                 //Get entity manager
1265                 $em = $this->getEntityManager();
1266
1267                 //Get quote strategy
1268                 $qs = $em->getConfiguration()->getQuoteStrategy();
1269                 $dp = $em->getConnection()->getDatabasePlatform();
1270
1271                 //Get quoted table names
1272                 //XXX: this allow to make this code table name independent
1273                 $tables = [
1274                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
1275                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
1276                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
1277                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1278                         //XXX: Set limit used to workaround mariadb subselect optimization
1279                         ':limit' => PHP_INT_MAX,
1280                         //Delay
1281                         ':guestdelay' => self::GUEST_DELAY * 24 * 3600,
1282                         ':regulardelay' => self::REGULAR_DELAY * 24 * 3600,
1283                         ':seniordelay' => self::SENIOR_DELAY * 24 * 3600,
1284                         //Group
1285                         ':guestid' => 2,
1286                         ':regularid' => 3,
1287                         ':seniorid' => 4,
1288                         //Slot
1289                         ':afternoonid' => 2,
1290                         ':eveningid' => 3,
1291                         ':afterid' => 4,
1292                         //XXX: days since last session after which guest regain normal priority
1293                         ':guestwait' => 30,
1294                         //XXX: session count until considered at regular delay
1295                         ':scount' => 5,
1296                         //XXX: pn_ratio over which considered at regular delay
1297                         ':pnratio' => 1,
1298                         //XXX: tr_ratio diff over which considered at regular delay
1299                         ':trdiff' => 5,
1300                         "\t" => '',
1301                         "\n" => ' '
1302                 ];
1303
1304                 /**
1305                  * Query session applications ranked by location score, global score, created and user_id
1306                  *
1307                  * @xxx guest (or less) with application on location within 30 day are only considered within guestdelay
1308                  *
1309                  * @xxx regular (or less) premium application on hotspot are only considered within regulardelay
1310                  *
1311                  * @xxx senior (or less) with 5 or less session on location are only considered within seniordelay
1312                  *
1313                  * @xxx senior (or less) with l_pn_ratio >= 1 are only considered within seniordelay
1314                  *
1315                  * @xxx senior (or less) with l_tr_ratio >= (o_tr_ratio + 5) are only considered within seniordelay
1316                  *
1317                  * @xxx only consider session within one year (may be unaccurate by the day with after session)
1318                  *
1319                  * @xxx rainfall may not be accessible for previous session and other session at d-4 (only at d-2)
1320                  *
1321                  * @todo ??? feedback the data to inform the rejected users ???
1322                  */
1323                 $req = <<<SQL
1324 SELECT e.id, e.l_score AS score
1325 FROM (
1326         SELECT
1327                 d.id,
1328                 d.user_id,
1329                 d.l_count,
1330                 d.l_score,
1331                 d.l_tr_ratio,
1332                 d.l_pn_ratio,
1333                 d.l_previous,
1334                 d.g_score,
1335                 d.o_tr_ratio,
1336                 MAX(gu.group_id) AS group_id,
1337                 d.remaining,
1338                 d.premium,
1339                 d.hotspot,
1340                 d.created
1341         FROM (
1342                 SELECT
1343                         c.id,
1344                         c.user_id,
1345                         c.l_count,
1346                         c.l_score,
1347                         c.l_tr_ratio,
1348                         c.l_pn_ratio,
1349                         c.l_previous,
1350                         c.g_score,
1351                         AVG(IF(a4.id IS NOT NULL AND s4.temperature IS NOT NULL AND s4.rainfall IS NOT NULL, s4.temperature/(1+s4.rainfall), NULL)) AS o_tr_ratio,
1352                         c.remaining,
1353                         c.premium,
1354                         c.hotspot,
1355                         c.created
1356                 FROM (
1357                         SELECT
1358                                 b.id,
1359                                 b.user_id,
1360                                 b.session_id,
1361                                 b.date,
1362                                 b.location_id,
1363                                 b.l_count,
1364                                 b.l_score,
1365                                 b.l_tr_ratio,
1366                                 b.l_pn_ratio,
1367                                 b.l_previous,
1368                                 SUM(IF(a3.id IS NOT NULL, 1/ABS(DATEDIFF(ADDDATE(b.date, INTERVAL IF(b.slot_id = :afterid, 1, 0) DAY), ADDDATE(s3.date, INTERVAL IF(s3.slot_id = :afterid, 1, 0) DAY))), 0)) AS g_score,
1369                                 b.remaining,
1370                                 b.premium,
1371                                 b.hotspot,
1372                                 b.created
1373                         FROM (
1374                                 SELECT
1375                                         a.id,
1376                                         a.user_id,
1377                                         s.id AS session_id,
1378                                         s.date AS date,
1379                                         s.slot_id,
1380                                         s.location_id,
1381                                         COUNT(a2.id) AS l_count,
1382                                         SUM(IF(a2.id IS NOT NULL, 1/ABS(DATEDIFF(ADDDATE(s.date, INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), ADDDATE(s2.date, INTERVAL IF(s2.slot_id = :afterid, 1, 0) DAY))), 0)) AS l_score,
1383                                         AVG(IF(a2.id IS NOT NULL AND s2.temperature IS NOT NULL AND s2.rainfall IS NOT NULL, s2.temperature/(1+s2.rainfall), NULL)) AS l_tr_ratio,
1384                                         (SUM(IF(a2.id IS NOT NULL AND s2.premium = 1, 1, 0))+1)/(SUM(IF(a2.id IS NOT NULL AND s2.premium = 0, 1, 0))+1) AS l_pn_ratio,
1385                                         MIN(IF(a2.id IS NOT NULL, DATEDIFF(ADDDATE(s.date, INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), ADDDATE(s2.date, INTERVAL IF(s2.slot_id = :afterid, 1, 0) DAY)), NULL)) AS l_previous,
1386                                         TIME_TO_SEC(TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) AS remaining,
1387                                         s.premium,
1388                                         l.hotspot,
1389                                         a.created
1390                                 FROM RapsysAirBundle:Session AS s
1391                                 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1392                                 JOIN RapsysAirBundle:Application AS a ON (a.session_id = s.id AND a.canceled IS NULL)
1393                                 LEFT JOIN RapsysAirBundle:Session AS s2 ON (s2.id != s.id AND s2.location_id = s.location_id AND s2.slot_id IN (:afternoonid, :eveningid) AND s2.application_id IS NOT NULL AND s2.locked IS NULL AND s2.date > s.date - INTERVAL 1 YEAR)
1394                                 LEFT JOIN RapsysAirBundle:Application AS a2 ON (a2.id = s2.application_id AND a2.user_id = a.user_id AND (a2.canceled IS NULL OR TIMESTAMPDIFF(DAY, a2.canceled, ADDDATE(ADDTIME(s2.date, s2.begin), INTERVAL IF(s2.slot_id = :afterid, 1, 0) DAY)) < 1))
1395                                 WHERE s.id = :sid
1396                                 GROUP BY a.id
1397                                 ORDER BY NULL
1398                                 LIMIT 0, :limit
1399                         ) AS b
1400                         LEFT JOIN RapsysAirBundle:Session AS s3 ON (s3.id != b.session_id AND s3.application_id IS NOT NULL AND s3.locked IS NULL AND s3.date > b.date - INTERVAL 1 YEAR)
1401                         LEFT JOIN RapsysAirBundle:Application AS a3 ON (a3.id = s3.application_id AND a3.user_id = b.user_id AND (a3.canceled IS NULL OR TIMESTAMPDIFF(DAY, a3.canceled, ADDDATE(ADDTIME(s3.date, s3.begin), INTERVAL IF(s3.slot_id = :afterid, 1, 0) DAY)) < 1))
1402                         GROUP BY b.id
1403                         ORDER BY NULL
1404                         LIMIT 0, :limit
1405                 ) AS c
1406                 LEFT JOIN RapsysAirBundle:Session AS s4 ON (s4.id != c.session_id AND s4.location_id = c.location_id AND s4.application_id IS NOT NULL AND s4.locked IS NULL AND s4.date > c.date - INTERVAL 1 YEAR)
1407                 LEFT JOIN RapsysAirBundle:Application AS a4 ON (a4.id = s4.application_id AND a4.user_id != c.user_id AND (a4.canceled IS NULL OR TIMESTAMPDIFF(DAY, a4.canceled, ADDDATE(ADDTIME(s4.date, s4.begin), INTERVAL IF(s4.slot_id = :afterid, 1, 0) DAY)) < 1))
1408                 GROUP BY c.id
1409                 ORDER BY NULL
1410                 LIMIT 0, :limit
1411         ) AS d
1412         LEFT JOIN RapsysAirBundle:GroupUser AS gu ON (gu.user_id = d.user_id)
1413         GROUP BY d.id
1414         LIMIT 0, :limit
1415 ) AS e
1416 WHERE
1417         IF(e.group_id <= :guestid AND e.l_previous <= :guestwait, e.remaining <= :guestdelay, 1) AND
1418         IF(e.group_id <= :regularid AND e.premium = 1 AND e.hotspot = 1, e.remaining <= :regulardelay, 1) AND
1419         IF(e.group_id <= :seniorid AND e.l_count <= :scount, e.remaining <= :regulardelay, 1) AND
1420         IF(e.group_id <= :seniorid AND e.l_pn_ratio >= :pnratio, e.remaining <= :regulardelay, 1) AND
1421         IF(e.group_id <= :seniorid AND e.l_tr_ratio >= (e.o_tr_ratio + :trdiff), e.remaining <= :regulardelay, 1)
1422 ORDER BY e.l_score ASC, e.g_score ASC, e.created ASC, e.user_id ASC
1423 SQL;
1424
1425                 //Replace bundle entity name by table name
1426                 $req = str_replace(array_keys($tables), array_values($tables), $req);
1427
1428                 //Set update request
1429                 $upreq = 'UPDATE RapsysAirBundle:Application SET score = :score, updated = NOW() WHERE id = :id';
1430
1431                 //Replace bundle entity name by table name
1432                 $upreq = str_replace(array_keys($tables), array_values($tables), $upreq);
1433
1434                 //Get result set mapping instance
1435                 $rsm = new ResultSetMapping();
1436
1437                 //Declare all fields
1438                 $rsm
1439                         ->addEntityResult('RapsysAirBundle:Application', 'a')
1440                         ->addFieldResult('a', 'id', 'id')
1441                         ->addFieldResult('a', 'score', 'score')
1442                         ->addIndexBy('a', 'id');
1443
1444                 //Get result
1445                 //XXX: setting limit in subqueries is required to prevent mariadb optimisation
1446                 $applications = $em
1447                         ->createNativeQuery($req, $rsm)
1448                         ->setParameter('sid', $id)
1449                         //XXX: removed, we update score before returning best candidate
1450                         //->getOneOrNullResult(Query::HYDRATE_SINGLE_SCALAR);
1451                         ->getResult();
1452
1453                 //Init ret
1454                 $ret = null;
1455
1456                 //Update score
1457                 foreach($applications as $application) {
1458                         //Check if we already saved best candidate
1459                         if ($ret === null) {
1460                                 //Return first application
1461                                 $ret = $application;
1462                         }
1463
1464                         //Update application updated field
1465                         //XXX: updated field is not modified for user with bad behaviour as application is not retrieved until delay is reached
1466                         $em->getConnection()->executeUpdate($upreq, ['id' => $application->getId(), 'score' => $application->getScore()], ['id' => Type::INTEGER, 'score' => Type::FLOAT]);
1467                 }
1468
1469                 //Return best ranked application
1470                 return $ret;
1471         }
1472
1473
1474         /**
1475          * Rekey sessions and applications by chronological session id
1476          *
1477          * @return bool The rekey success or failure
1478          */
1479         function rekey(): bool {
1480                 //Get entity manager
1481                 $em = $this->getEntityManager();
1482
1483                 //Get connection
1484                 $cnx = $em->getConnection();
1485
1486                 //Get quote strategy
1487                 $qs = $em->getConfiguration()->getQuoteStrategy();
1488                 $dp = $em->getConnection()->getDatabasePlatform();
1489
1490                 //Get quoted table names
1491                 //XXX: this allow to make this code table name independent
1492                 $tables = [
1493                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
1494                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1495                         ':afterid' => 4,
1496                         "\t" => '',
1497                         "\n" => ' '
1498                 ];
1499
1500                 //Set the request
1501                 $req = <<<SQL
1502 SELECT
1503         a.id,
1504         a.sa_id
1505 FROM (
1506         SELECT
1507                 s.id,
1508                 s.date,
1509                 s.begin,
1510                 s.slot_id,
1511                 GROUP_CONCAT(sa.id ORDER BY sa.id SEPARATOR "\\n") AS sa_id
1512         FROM RapsysAirBundle:Session AS s
1513         LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
1514         GROUP BY s.id
1515         ORDER BY NULL
1516 ) AS a
1517 ORDER BY ADDDATE(ADDTIME(a.date, a.begin), INTERVAL IF(a.slot_id = :afterid, 1, 0) DAY) ASC
1518 SQL;
1519
1520                 //Replace bundle entity name by table name
1521                 $req = str_replace(array_keys($tables), array_values($tables), $req);
1522
1523                 //Get result set mapping instance
1524                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1525                 $rsm = new ResultSetMapping();
1526
1527                 //Declare all fields
1528                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1529                 //addScalarResult($sqlColName, $resColName, $type = 'string');
1530                 $rsm->addScalarResult('id', 'id', 'integer')
1531                         ->addScalarResult('sa_id', 'sa_id', 'string');
1532                         #->addIndexByScalar('id');
1533
1534                 //Fetch result
1535                 $rnq = $em->createNativeQuery($req, $rsm);
1536
1537                 //Get result set
1538                 $res = $rnq->getResult();
1539
1540                 //Start transaction
1541                 $cnx->beginTransaction();
1542
1543                 //Set update session request
1544                 $sreq = <<<SQL
1545 UPDATE RapsysAirBundle:Session
1546 SET id = :nid, updated = NOW()
1547 WHERE id = :id
1548 SQL;
1549
1550                 //Replace bundle entity name by table name
1551                 $sreq = str_replace(array_keys($tables), array_values($tables), $sreq);
1552
1553                 //Set update application request
1554                 $areq = <<<SQL
1555 UPDATE RapsysAirBundle:Application
1556 SET session_id = :nid, updated = NOW()
1557 WHERE session_id = :id
1558 SQL;
1559
1560                 //Replace bundle entity name by table name
1561                 $areq = str_replace(array_keys($tables), array_values($tables), $areq);
1562
1563                 //Set max value
1564                 $max = max(array_keys($res));
1565
1566                 try {
1567                         //Prepare session to update
1568                         foreach($res as $id => $data) {
1569                                 //Set temp id
1570                                 $res[$id]['t_id'] = $max + $id + 1;
1571
1572                                 //Set new id
1573                                 $res[$id]['n_id'] = $id + 1;
1574
1575                                 //Explode application ids
1576                                 $res[$id]['sa_id'] = explode("\n", $data['sa_id']);
1577
1578                                 //Without change
1579                                 if ($res[$id]['n_id'] == $res[$id]['id']) {
1580                                         //Remove unchanged session
1581                                         unset($res[$id]);
1582                                 }
1583                         }
1584
1585                         //With changes
1586                         if (!empty($res)) {
1587                                 //Disable foreign key checks
1588                                 $cnx->prepare('SET foreign_key_checks = 0')->execute();
1589
1590                                 //Update to temp id
1591                                 foreach($res as $id => $data) {
1592                                         //Run session update
1593                                         $cnx->executeUpdate($sreq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
1594
1595                                         //Run applications update
1596                                         $cnx->executeUpdate($areq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
1597                                 }
1598
1599                                 //Update to new id
1600                                 foreach($res as $id => $data) {
1601                                         //Run session update
1602                                         $cnx->executeUpdate($sreq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]);
1603
1604                                         //Run applications update
1605                                         $cnx->executeUpdate($areq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]);
1606                                 }
1607
1608                                 //Restore foreign key checks
1609                                 $cnx->prepare('SET foreign_key_checks = 1')->execute();
1610
1611                                 //Commit transaction
1612                                 $cnx->commit();
1613
1614                                 //Set update auto_increment request
1615                                 $ireq = <<<SQL
1616 ALTER TABLE RapsysAirBundle:Session
1617 auto_increment = 1
1618 SQL;
1619
1620                                 //Replace bundle entity name by table name
1621                                 $ireq = str_replace(array_keys($tables), array_values($tables), $ireq);
1622
1623                                 //Reset auto_increment
1624                                 $cnx->exec($ireq);
1625                         //Without changes
1626                         } else {
1627                                 //Rollback transaction
1628                                 $cnx->rollback();
1629                         }
1630                 } catch(\Exception $e) {
1631                         //Rollback transaction
1632                         $cnx->rollback();
1633
1634                         //Throw exception
1635                         throw $e;
1636                 }
1637
1638                 //Return success
1639                 return true;
1640         }
1641 }