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 session by id
 147          *
 148          * @param $id The session id
 149          * @param $locale The locale
 150          * @return array The session data
 151          */
 152         public function fetchOneById($id, $locale = null) {
 153                 //Get entity manager
 154                 $em = $this->getEntityManager();
 155
 156                 //Get quote strategy
 157                 $qs = $em->getConfiguration()->getQuoteStrategy();
 158                 $dp = $em->getConnection()->getDatabasePlatform();
 159
 160                 //Get quoted table names
 161                 //XXX: this allow to make this code table name independent
 162                 $tables = [
 163                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 164                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 165                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 166                         'RapsysAirBundle:Link' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Link'), $dp),
 167                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 168                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 169                         'RapsysAirBundle:Snippet' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Snippet'), $dp),
 170                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 171                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
 172                         ':afterid' => 4,
 173                         "\t" => '',
 174                         "\n" => ' '
 175                 ];
 176
 177                 //Set the request
 178                 //TODO: compute scores ?
 179                 //TODO: compute delivery date ? (J-3/J-4 ?)
 180                 $req =<<<SQL
 181 SELECT
 182         s.id,
 183         s.date,
 184         s.begin,
 185         ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
 186         s.length,
 187         ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
 188         s.rainfall,
 189         s.rainrisk,
 190         s.realfeel,
 191         s.realfeelmin,
 192         s.realfeelmax,
 193         s.temperature,
 194         s.temperaturemin,
 195         s.temperaturemax,
 196         s.locked,
 197         s.created,
 198         s.updated,
 199         s.location_id AS l_id,
 200         l.short AS l_short,
 201         l.title AS l_title,
 202         l.address AS l_address,
 203         l.zipcode AS l_zipcode,
 204         l.city AS l_city,
 205         l.latitude AS l_latitude,
 206         l.longitude AS l_longitude,
 207         s.slot_id AS t_id,
 208         t.title AS t_title,
 209         s.application_id AS a_id,
 210         a.user_id AS au_id,
 211         au.pseudonym AS au_pseudonym,
 212         p.id AS p_id,
 213         p.description AS p_description,
 214         GROUP_CONCAT(i.type ORDER BY i.id SEPARATOR "\\n") AS i_type,
 215         GROUP_CONCAT(i.url ORDER BY i.id SEPARATOR "\\n") AS i_url,
 216         GROUP_CONCAT(sa.id ORDER BY sa.user_id SEPARATOR "\\n") AS sa_id,
 217         GROUP_CONCAT(IFNULL(sa.score, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_score,
 218         GROUP_CONCAT(sa.created ORDER BY sa.user_id SEPARATOR "\\n") AS sa_created,
 219         GROUP_CONCAT(sa.updated ORDER BY sa.user_id SEPARATOR "\\n") AS sa_updated,
 220         GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled,
 221         GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
 222         GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
 223 FROM RapsysAirBundle:Session AS s
 224 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 225 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 226 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 227 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 228 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
 229 LEFT JOIN RapsysAirBundle:Link AS i ON (i.user_id = a.user_id)
 230 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 231 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 232 WHERE s.id = :sid
 233 GROUP BY s.id
 234 ORDER BY NULL
 235 SQL;
 236
 237                 //Replace bundle entity name by table name
 238                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 239
 240                 //Get result set mapping instance
 241                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 242                 $rsm = new ResultSetMapping();
 243
 244                 //Declare all fields
 245                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 246                 $rsm->addScalarResult('id', 'id', 'integer')
 247                         ->addScalarResult('date', 'date', 'date')
 248                         ->addScalarResult('begin', 'begin', 'time')
 249                         ->addScalarResult('start', 'start', 'datetime')
 250                         ->addScalarResult('length', 'length', 'time')
 251                         ->addScalarResult('stop', 'stop', 'datetime')
 252                         ->addScalarResult('rainfall', 'rainfall', 'float')
 253                         ->addScalarResult('rainrisk', 'rainrisk', 'float')
 254                         ->addScalarResult('realfeel', 'realfeel', 'float')
 255                         ->addScalarResult('realfeelmin', 'realfeelmin', 'float')
 256                         ->addScalarResult('realfeelmax', 'realfeelmax', 'float')
 257                         ->addScalarResult('temperature', 'temperature', 'float')
 258                         ->addScalarResult('temperaturemin', 'temperaturemin', 'float')
 259                         ->addScalarResult('temperaturemax', 'temperaturemax', 'float')
 260                         ->addScalarResult('locked', 'locked', 'datetime')
 261                         ->addScalarResult('created', 'created', 'datetime')
 262                         ->addScalarResult('updated', 'updated', 'datetime')
 263                         ->addScalarResult('l_id', 'l_id', 'integer')
 264                         ->addScalarResult('l_short', 'l_short', 'string')
 265                         ->addScalarResult('l_title', 'l_title', 'string')
 266                         ->addScalarResult('l_address', 'l_address', 'string')
 267                         ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
 268                         ->addScalarResult('l_city', 'l_city', 'string')
 269                         ->addScalarResult('l_latitude', 'l_latitude', 'float')
 270                         ->addScalarResult('l_longitude', 'l_longitude', 'float')
 271                         ->addScalarResult('t_id', 't_id', 'integer')
 272                         ->addScalarResult('t_title', 't_title', 'string')
 273                         ->addScalarResult('a_id', 'a_id', 'integer')
 274                         ->addScalarResult('au_id', 'au_id', 'integer')
 275                         ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
 276                         ->addScalarResult('i_type', 'i_type', 'string')
 277                         ->addScalarResult('i_url', 'i_url', 'string')
 278                         ->addScalarResult('p_id', 'p_id', 'integer')
 279                         ->addScalarResult('p_description', 'p_description', 'text')
 280                         //XXX: is a string because of \n separator
 281                         ->addScalarResult('sa_id', 'sa_id', 'string')
 282                         //XXX: is a string because of \n separator
 283                         ->addScalarResult('sa_score', 'sa_score', 'string')
 284                         //XXX: is a string because of \n separator
 285                         ->addScalarResult('sa_created', 'sa_created', 'string')
 286                         //XXX: is a string because of \n separator
 287                         ->addScalarResult('sa_updated', 'sa_updated', 'string')
 288                         //XXX: is a string because of \n separator
 289                         ->addScalarResult('sa_canceled', 'sa_canceled', 'string')
 290                         //XXX: is a string because of \n separator
 291                         ->addScalarResult('sau_id', 'sau_id', 'string')
 292                         //XXX: is a string because of \n separator
 293                         ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
 294                         ->addIndexByScalar('id');
 295
 296                 //Return result
 297                 return $em
 298                         ->createNativeQuery($req, $rsm)
 299                         ->setParameter('sid', $id)
 300                         ->setParameter('locale', $locale)
 301                         ->getOneOrNullResult();
 302         }
 303
 304         /**
 305          * Fetch sessions calendar with translated location by date period
 306          *
 307          * @param $translator The TranslatorInterface instance
 308          * @param $period The date period
 309          * @param $locationId The location id
 310          * @param $sessionId The session id
 311          * @param $granted The session is granted
 312          */
 313         public function fetchCalendarByDatePeriod(TranslatorInterface $translator, $period, $locationId = null, $sessionId = null, $granted = false) {
 314                 //Get entity manager
 315                 $em = $this->getEntityManager();
 316
 317                 //Get quote strategy
 318                 $qs = $em->getConfiguration()->getQuoteStrategy();
 319                 $dp = $em->getConnection()->getDatabasePlatform();
 320
 321                 //Get quoted table names
 322                 //XXX: this allow to make this code table name independent
 323                 $tables = [
 324                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 325                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 326                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 327                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 328                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 329                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 330                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
 331                         ':afterid' => 4,
 332                         "\t" => '',
 333                         "\n" => ' '
 334                 ];
 335
 336                 //Init granted sql
 337                 $grantSql = '';
 338
 339                 //When granted is set
 340                 if (empty($granted)) {
 341                         //Set application and user as optional
 342                         $grantSql = 'LEFT ';
 343                 }
 344
 345                 //Init location sql
 346                 $locationSql = '';
 347
 348                 //When location id is set
 349                 if (!empty($locationId)) {
 350                         //Add location id clause
 351                         $locationSql = "\n\t".'AND s.location_id = :lid';
 352                 }
 353
 354                 //Set the request
 355                 $req = <<<SQL
 356 SELECT
 357         s.id,
 358         s.date,
 359         s.rainrisk,
 360         s.rainfall,
 361         s.realfeel,
 362         s.temperature,
 363         s.locked,
 364         ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
 365         ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
 366         s.location_id AS l_id,
 367         l.short AS l_short,
 368         l.title AS l_title,
 369         s.slot_id AS t_id,
 370         t.title AS t_title,
 371         s.application_id AS a_id,
 372         a.user_id AS au_id,
 373         au.pseudonym AS au_pseudonym,
 374         GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
 375         GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
 376 FROM RapsysAirBundle:Session AS s
 377 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 378 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 379 ${grantSql}JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 380 ${grantSql}JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 381 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 382 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 383 WHERE s.date BETWEEN :begin AND :end${locationSql}
 384 GROUP BY s.id
 385 ORDER BY NULL
 386 SQL;
 387
 388                 //Replace bundle entity name by table name
 389                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 390
 391                 //Get result set mapping instance
 392                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 393                 $rsm = new ResultSetMapping();
 394
 395                 //Declare all fields
 396                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 397                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 398                 $rsm->addScalarResult('id', 'id', 'integer')
 399                         ->addScalarResult('date', 'date', 'date')
 400                         ->addScalarResult('rainrisk', 'rainrisk', 'float')
 401                         ->addScalarResult('rainfall', 'rainfall', 'float')
 402                         ->addScalarResult('realfeel', 'realfeel', 'float')
 403                         ->addScalarResult('temperature', 'temperature', 'float')
 404                         ->addScalarResult('locked', 'locked', 'datetime')
 405                         ->addScalarResult('start', 'start', 'datetime')
 406                         ->addScalarResult('stop', 'stop', 'datetime')
 407                         ->addScalarResult('t_id', 't_id', 'integer')
 408                         ->addScalarResult('t_title', 't_title', 'string')
 409                         ->addScalarResult('l_id', 'l_id', 'integer')
 410                         ->addScalarResult('l_short', 'l_short', 'string')
 411                         ->addScalarResult('l_title', 'l_title', 'string')
 412                         ->addScalarResult('a_id', 'a_id', 'integer')
 413                         ->addScalarResult('au_id', 'au_id', 'integer')
 414                         ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
 415                         //XXX: is a string because of \n separator
 416                         ->addScalarResult('sau_id', 'sau_id', 'string')
 417                         //XXX: is a string because of \n separator
 418                         ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
 419                         ->addIndexByScalar('id');
 420
 421                 //Fetch result
 422                 $res = $em
 423                         ->createNativeQuery($req, $rsm)
 424                         ->setParameter('begin', $period->getStartDate())
 425                         ->setParameter('end', $period->getEndDate());
 426
 427                 //Add optional location id
 428                 if (!empty($locationId)) {
 429                         $res->setParameter('lid', $locationId);
 430                 }
 431
 432                 //Get result
 433                 $res = $res->getResult();
 434
 435                 //Init calendar
 436                 $calendar = [];
 437
 438                 //Init month
 439                 $month = null;
 440
 441                 //Iterate on each day
 442                 foreach($period as $date) {
 443                         //Init day in calendar
 444                         $calendar[$Ymd = $date->format('Ymd')] = [
 445                                 'title' => $translator->trans($date->format('l')).' '.$date->format('d'),
 446                                 'class' => [],
 447                                 'sessions' => []
 448                         ];
 449
 450                         //Detect month change
 451                         if ($month != $date->format('m')) {
 452                                 $month = $date->format('m');
 453                                 //Append month for first day of month
 454                                 //XXX: except if today to avoid double add
 455                                 if ($date->format('U') != strtotime('today')) {
 456                                         $calendar[$Ymd]['title'] .= '/'.$month;
 457                                 }
 458                         }
 459                         //Deal with today
 460                         if ($date->format('U') == ($today = strtotime('today'))) {
 461                                 $calendar[$Ymd]['title'] .= '/'.$month;
 462                                 $calendar[$Ymd]['current'] = true;
 463                                 $calendar[$Ymd]['class'][] = 'current';
 464                         }
 465                         //Disable passed days
 466                         if ($date->format('U') < $today) {
 467                                 $calendar[$Ymd]['disabled'] = true;
 468                                 $calendar[$Ymd]['class'][] = 'disabled';
 469                         }
 470                         //Set next month days
 471                         if ($date->format('m') > date('m')) {
 472                                 $calendar[$Ymd]['next'] = true;
 473                                 #$calendar[$Ymd]['class'][] = 'next';
 474                         }
 475
 476                         //Detect sunday
 477                         if ($date->format('w') == 0) {
 478                                 $calendar[$Ymd]['class'][] = 'sunday';
 479                         }
 480
 481                         //Iterate on each session to find the one of the day
 482                         foreach($res as $session) {
 483                                 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
 484                                         //Count number of application
 485                                         $count = count(explode("\n", $session['sau_id']));
 486
 487                                         //Compute classes
 488                                         $class = [];
 489                                         if (!empty($session['a_id'])) {
 490                                                 $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
 491                                                 $class[] = 'granted';
 492                                         } elseif ($count > 1) {
 493                                                 $class[] = 'disputed';
 494                                         } elseif (!empty($session['locked'])) {
 495                                                 $class[] = 'locked';
 496                                         } else {
 497                                                 $class[] = 'pending';
 498                                         }
 499
 500                                         if ($sessionId == $session['id']) {
 501                                                 $class[] = 'highlight';
 502                                         }
 503
 504                                         //Set temperature
 505                                         //XXX: realfeel may be null, temperature should not
 506                                         $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
 507
 508                                         //Compute weather
 509                                         //XXX: rainfall may be null
 510                                         if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
 511                                                 $weather = self::GLYPHS['Stormy'];
 512                                         } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
 513                                                 $weather = self::GLYPHS['Rainy'];
 514                                         } elseif ($temperature > 24) {
 515                                                 $weather = self::GLYPHS['Cleary'];
 516                                         } elseif ($temperature > 17) {
 517                                                 $weather = self::GLYPHS['Sunny'];
 518                                         } elseif ($temperature > 10) {
 519                                                 $weather = self::GLYPHS['Cloudy'];
 520                                         } elseif ($temperature !== null) {
 521                                                 $weather = self::GLYPHS['Winty'];
 522                                         } else {
 523                                                 $weather = null;
 524                                         }
 525
 526                                         //Init weathertitle
 527                                         $weathertitle = [];
 528
 529                                         //Check if realfeel is available
 530                                         if ($session['realfeel'] !== null) {
 531                                                 $weathertitle[] = $session['realfeel'].'°R';
 532                                         }
 533
 534                                         //Check if temperature is available
 535                                         if ($session['temperature'] !== null) {
 536                                                 $weathertitle[] = $session['temperature'].'°C';
 537                                         }
 538
 539                                         //Check if rainrisk is available
 540                                         if ($session['rainrisk'] !== null) {
 541                                                 $weathertitle[] = ($session['rainrisk']*100).'%';
 542                                         }
 543
 544                                         //Check if rainfall is available
 545                                         if ($session['rainfall'] !== null) {
 546                                                 $weathertitle[] = $session['rainfall'].'mm';
 547                                         }
 548
 549                                         //Set applications
 550                                         $applications = [
 551                                                 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).$translator->trans(':')
 552                                         ];
 553
 554                                         //Fetch pseudonyms from session applications
 555                                         $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
 556
 557                                         //Set pseudonym
 558                                         $pseudonym = null;
 559
 560                                         //Check that session is not granted
 561                                         if (empty($session['a_id'])) {
 562                                                 //With location id and unique application
 563                                                 if ($count == 1) {
 564                                                         //Set unique application pseudonym
 565                                                         $pseudonym = $session['sau_pseudonym'];
 566                                                 }
 567                                         //Session is granted
 568                                         } else {
 569                                                 //Replace granted application
 570                                                 $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
 571
 572                                                 //Set pseudonym
 573                                                 $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
 574                                         }
 575
 576                                         //Add the session
 577                                         $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [
 578                                                 'id' => $session['id'],
 579                                                 'start' => $session['start'],
 580                                                 'stop' => $session['stop'],
 581                                                 'location' => $translator->trans($session['l_short']),
 582                                                 'pseudonym' => $pseudonym,
 583                                                 'class' => $class,
 584                                                 'slot' => self::GLYPHS[$session['t_title']],
 585                                                 'slottitle' => $translator->trans($session['t_title']),
 586                                                 'weather' => $weather,
 587                                                 'weathertitle' => implode(' ', $weathertitle),
 588                                                 'applications' => $applications
 589                                         ];
 590                                 }
 591                         }
 592
 593                         //Sort sessions
 594                         ksort($calendar[$Ymd]['sessions']);
 595                 }
 596
 597                 //Send result
 598                 return $calendar;
 599         }
 600
 601         /**
 602          * Fetch sessions calendar with translated location by date period and user
 603          *
 604          * @param $translator The TranslatorInterface instance
 605          * @param $period The date period
 606          * @param $userId The user id
 607          * @param $sessionId The session id
 608          */
 609         public function fetchUserCalendarByDatePeriod(TranslatorInterface $translator, $period, $userId = null, $sessionId = null) {
 610                 //Get entity manager
 611                 $em = $this->getEntityManager();
 612
 613                 //Get quote strategy
 614                 $qs = $em->getConfiguration()->getQuoteStrategy();
 615                 $dp = $em->getConnection()->getDatabasePlatform();
 616
 617                 //Get quoted table names
 618                 //XXX: this allow to make this code table name independent
 619                 $tables = [
 620                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 621                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 622                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 623                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 624                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 625                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 626                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
 627                         ':afterid' => 4,
 628                         "\t" => '',
 629                         "\n" => ' '
 630                 ];
 631
 632                 //Init user sql
 633                 $userJoinSql = $userWhereSql = '';
 634
 635                 //When user id is set
 636                 if (!empty($userId)) {
 637                         //Add user join
 638                         $userJoinSql = 'JOIN RapsysAirBundle:Application AS sua ON (sua.session_id = s.id)'."\n";
 639                         //Add user id clause
 640                         $userWhereSql = "\n\t".'AND sua.user_id = :uid';
 641                 }
 642
 643                 //Set the request
 644                 //TODO: change as_u_* in sau_*, a_u_* in au_*, etc, see request up
 645                 $req = <<<SQL
 646 SELECT
 647         s.id,
 648         s.date,
 649         s.rainrisk,
 650         s.rainfall,
 651         s.realfeel,
 652         s.temperature,
 653         s.locked,
 654         ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
 655         ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
 656         s.location_id AS l_id,
 657         l.short AS l_short,
 658         l.title AS l_title,
 659         s.slot_id AS t_id,
 660         t.title AS t_title,
 661         s.application_id AS a_id,
 662         a.user_id AS au_id,
 663         au.pseudonym AS au_pseudonym,
 664         GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
 665         GROUP_CONCAT(CONCAT("- ", sau.pseudonym) ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
 666 FROM RapsysAirBundle:Session AS s
 667 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 668 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 669 ${userJoinSql}LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 670 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 671 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 672 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 673 WHERE s.date BETWEEN :begin AND :end${userWhereSql}
 674 GROUP BY s.id
 675 ORDER BY NULL
 676 SQL;
 677
 678                 //Replace bundle entity name by table name
 679                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 680
 681                 //Get result set mapping instance
 682                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 683                 $rsm = new ResultSetMapping();
 684
 685                 //Declare all fields
 686                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 687                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 688                 $rsm->addScalarResult('id', 'id', 'integer')
 689                         ->addScalarResult('date', 'date', 'date')
 690                         ->addScalarResult('rainrisk', 'rainrisk', 'float')
 691                         ->addScalarResult('rainfall', 'rainfall', 'float')
 692                         ->addScalarResult('realfeel', 'realfeel', 'float')
 693                         ->addScalarResult('temperature', 'temperature', 'float')
 694                         ->addScalarResult('locked', 'locked', 'datetime')
 695                         ->addScalarResult('start', 'start', 'datetime')
 696                         ->addScalarResult('stop', 'stop', 'datetime')
 697                         ->addScalarResult('t_id', 't_id', 'integer')
 698                         ->addScalarResult('t_title', 't_title', 'string')
 699                         ->addScalarResult('l_id', 'l_id', 'integer')
 700                         ->addScalarResult('l_short', 'l_short', 'string')
 701                         ->addScalarResult('l_title', 'l_title', 'string')
 702                         ->addScalarResult('a_id', 'a_id', 'integer')
 703                         ->addScalarResult('au_id', 'au_id', 'integer')
 704                         ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
 705                         //XXX: is a string because of \n separator
 706                         ->addScalarResult('sau_id', 'sau_id', 'string')
 707                         //XXX: is a string because of \n separator
 708                         ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
 709                         ->addIndexByScalar('id');
 710
 711                 //Fetch result
 712                 $res = $em
 713                         ->createNativeQuery($req, $rsm)
 714                         ->setParameter('begin', $period->getStartDate())
 715                         ->setParameter('end', $period->getEndDate())
 716                         ->setParameter('uid', $userId)
 717                         ->getResult();
 718
 719                 //Init calendar
 720                 $calendar = [];
 721
 722                 //Init month
 723                 $month = null;
 724
 725                 //Iterate on each day
 726                 foreach($period as $date) {
 727                         //Init day in calendar
 728                         $calendar[$Ymd = $date->format('Ymd')] = [
 729                                 'title' => $translator->trans($date->format('l')).' '.$date->format('d'),
 730                                 'class' => [],
 731                                 'sessions' => []
 732                         ];
 733
 734                         //Detect month change
 735                         if ($month != $date->format('m')) {
 736                                 $month = $date->format('m');
 737                                 //Append month for first day of month
 738                                 //XXX: except if today to avoid double add
 739                                 if ($date->format('U') != strtotime('today')) {
 740                                         $calendar[$Ymd]['title'] .= '/'.$month;
 741                                 }
 742                         }
 743                         //Deal with today
 744                         if ($date->format('U') == ($today = strtotime('today'))) {
 745                                 $calendar[$Ymd]['title'] .= '/'.$month;
 746                                 $calendar[$Ymd]['current'] = true;
 747                                 $calendar[$Ymd]['class'][] = 'current';
 748                         }
 749                         //Disable passed days
 750                         if ($date->format('U') < $today) {
 751                                 $calendar[$Ymd]['disabled'] = true;
 752                                 $calendar[$Ymd]['class'][] = 'disabled';
 753                         }
 754                         //Set next month days
 755                         if ($date->format('m') > date('m')) {
 756                                 $calendar[$Ymd]['next'] = true;
 757                                 #$calendar[$Ymd]['class'][] = 'next';
 758                         }
 759
 760                         //Detect sunday
 761                         if ($date->format('w') == 0) {
 762                                 $calendar[$Ymd]['class'][] = 'sunday';
 763                         }
 764
 765                         //Iterate on each session to find the one of the day
 766                         foreach($res as $session) {
 767                                 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
 768                                         //Count number of application
 769                                         $count = count(explode("\n", $session['sau_id']));
 770
 771                                         //Compute classes
 772                                         $class = [];
 773                                         if (!empty($session['a_id'])) {
 774                                                 $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
 775                                                 if ($session['au_id'] == $userId) {
 776                                                         $class[] = 'granted';
 777                                                 } else {
 778                                                         $class[] = 'disputed';
 779                                                 }
 780                                         } elseif ($count > 1) {
 781                                                 $class[] = 'disputed';
 782                                         } elseif (!empty($session['locked'])) {
 783                                                 $class[] = 'locked';
 784                                         } else {
 785                                                 $class[] = 'pending';
 786                                         }
 787
 788                                         if ($sessionId == $session['id']) {
 789                                                 $class[] = 'highlight';
 790                                         }
 791
 792                                         //Set temperature
 793                                         //XXX: realfeel may be null, temperature should not
 794                                         $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
 795
 796                                         //Compute weather
 797                                         //XXX: rainfall may be null
 798                                         if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
 799                                                 $weather = self::GLYPHS['Stormy'];
 800                                         } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
 801                                                 $weather = self::GLYPHS['Rainy'];
 802                                         } elseif ($temperature > 24) {
 803                                                 $weather = self::GLYPHS['Cleary'];
 804                                         } elseif ($temperature > 17) {
 805                                                 $weather = self::GLYPHS['Sunny'];
 806                                         } elseif ($temperature > 10) {
 807                                                 $weather = self::GLYPHS['Cloudy'];
 808                                         } elseif ($temperature !== null) {
 809                                                 $weather = self::GLYPHS['Winty'];
 810                                         } else {
 811                                                 $weather = null;
 812                                         }
 813
 814                                         //Init weathertitle
 815                                         $weathertitle = [];
 816
 817                                         //Check if realfeel is available
 818                                         if ($session['realfeel'] !== null) {
 819                                                 $weathertitle[] = $session['realfeel'].'°R';
 820                                         }
 821
 822                                         //Check if temperature is available
 823                                         if ($session['temperature'] !== null) {
 824                                                 $weathertitle[] = $session['temperature'].'°C';
 825                                         }
 826
 827                                         //Check if rainrisk is available
 828                                         if ($session['rainrisk'] !== null) {
 829                                                 $weathertitle[] = ($session['rainrisk']*100).'%';
 830                                         }
 831
 832                                         //Check if rainfall is available
 833                                         if ($session['rainfall'] !== null) {
 834                                                 $weathertitle[] = $session['rainfall'].'mm';
 835                                         }
 836
 837                                         //Set applications
 838                                         $applications = [
 839                                                 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).$translator->trans(':')
 840                                         ];
 841
 842                                         //Fetch pseudonyms from session applications
 843                                         $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
 844
 845                                         //Set pseudonym
 846                                         $pseudonym = null;
 847
 848                                         //Check that session is not granted
 849                                         if (empty($session['a_id'])) {
 850                                                 //With location id and unique application
 851                                                 if ($count == 1) {
 852                                                         //Set unique application pseudonym
 853                                                         $pseudonym = $session['sau_pseudonym'];
 854                                                 }
 855                                         //Session is granted
 856                                         } else {
 857                                                 //Replace granted application
 858                                                 $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
 859
 860                                                 //Set pseudonym
 861                                                 $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
 862                                         }
 863
 864                                         //Set title
 865                                         $title = $translator->trans($session['l_title']).($count > 1 ? ' ['.$count.']':'');
 866
 867                                         //Add the session
 868                                         $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [
 869                                                 'id' => $session['id'],
 870                                                 'start' => $session['start'],
 871                                                 'stop' => $session['stop'],
 872                                                 'location' => $translator->trans($session['l_short']),
 873                                                 'pseudonym' => $pseudonym,
 874                                                 'class' => $class,
 875                                                 'slot' => self::GLYPHS[$session['t_title']],
 876                                                 'slottitle' => $translator->trans($session['t_title']),
 877                                                 'weather' => $weather,
 878                                                 'weathertitle' => implode(' ', $weathertitle),
 879                                                 'applications' => $applications
 880                                         ];
 881                                 }
 882                         }
 883
 884                         //Sort sessions
 885                         ksort($calendar[$Ymd]['sessions']);
 886                 }
 887
 888                 //Send result
 889                 return $calendar;
 890         }
 891
 892         /**
 893          * Find all session pending hourly weather
 894          *
 895          * @return array<Session> The sessions to update
 896          */
 897         public function findAllPendingHourlyWeather() {
 898                 //Get entity manager
 899                 $em = $this->getEntityManager();
 900
 901                 //Get quote strategy
 902                 $qs = $em->getConfiguration()->getQuoteStrategy();
 903                 $dp = $em->getConnection()->getDatabasePlatform();
 904
 905                 //Get quoted table names
 906                 //XXX: this allow to make this code table name independent
 907                 $tables = [
 908                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 909                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 910                         //Accuweather
 911                         ':accuhourly' => self::ACCUWEATHER_HOURLY,
 912                         //Delay
 913                         ':afterid' => 4,
 914                         "\t" => '',
 915                         "\n" => ' '
 916                 ];
 917
 918                 //Select all sessions starting and stopping in the next 3 days
 919                 //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)
 920                 $req = <<<SQL
 921 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
 922 FROM RapsysAirBundle:Session AS s
 923 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 924 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))
 925 SQL;
 926
 927                 //Replace bundle entity name by table name
 928                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 929
 930                 //Get result set mapping instance
 931                 $rsm = new ResultSetMapping();
 932
 933                 //Declare all fields
 934                 $rsm
 935                         ->addEntityResult('RapsysAirBundle:Session', 's')
 936                         ->addFieldResult('s', 'id', 'id')
 937                         ->addFieldResult('s', 'date', 'date')
 938                         ->addFieldResult('s', 'begin', 'begin')
 939                         ->addFieldResult('s', 'length', 'length')
 940                         ->addFieldResult('s', 'rainfall', 'rainfall')
 941                         ->addFieldResult('s', 'rainrisk', 'rainrisk')
 942                         ->addFieldResult('s', 'realfeel', 'realfeel')
 943                         ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
 944                         ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
 945                         ->addFieldResult('s', 'temperature', 'temperature')
 946                         ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
 947                         ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
 948                         ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
 949                         ->addFieldResult('o', 'slot_id', 'id')
 950                         ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
 951                         ->addFieldResult('l', 'location_id', 'id')
 952                         ->addFieldResult('l', 'zipcode', 'zipcode')
 953                         ->addIndexBy('s', 'id');
 954
 955                 //Send result
 956                 return $em
 957                         ->createNativeQuery($req, $rsm)
 958                         ->getResult();
 959         }
 960
 961         /**
 962          * Find all session pending daily weather
 963          *
 964          * @return array<Session> The sessions to update
 965          */
 966         public function findAllPendingDailyWeather() {
 967                 //Get entity manager
 968                 $em = $this->getEntityManager();
 969
 970                 //Get quote strategy
 971                 $qs = $em->getConfiguration()->getQuoteStrategy();
 972                 $dp = $em->getConnection()->getDatabasePlatform();
 973
 974                 //Get quoted table names
 975                 //XXX: this allow to make this code table name independent
 976                 $tables = [
 977                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 978                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 979                         //Accuweather
 980                         ':accudaily' => self::ACCUWEATHER_DAILY,
 981                         ':accuhourly' => self::ACCUWEATHER_HOURLY,
 982                         //Delay
 983                         ':afterid' => 4,
 984                         "\t" => '',
 985                         "\n" => ' '
 986                 ];
 987
 988                 //Select all sessions stopping after next 3 days
 989                 //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
 990                 $req = <<<SQL
 991 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
 992 FROM RapsysAirBundle:Session AS s
 993 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 994 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))
 995 SQL;
 996
 997                 //Replace bundle entity name by table name
 998                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 999
1000                 //Get result set mapping instance
1001                 $rsm = new ResultSetMapping();
1002
1003                 //Declare all fields
1004                 $rsm
1005                         ->addEntityResult('RapsysAirBundle:Session', 's')
1006                         ->addFieldResult('s', 'id', 'id')
1007                         ->addFieldResult('s', 'date', 'date')
1008                         ->addFieldResult('s', 'begin', 'begin')
1009                         ->addFieldResult('s', 'length', 'length')
1010                         ->addFieldResult('s', 'rainfall', 'rainfall')
1011                         ->addFieldResult('s', 'rainrisk', 'rainrisk')
1012                         ->addFieldResult('s', 'realfeel', 'realfeel')
1013                         ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1014                         ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1015                         ->addFieldResult('s', 'temperature', 'temperature')
1016                         ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1017                         ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1018                         ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
1019                         ->addFieldResult('o', 'slot_id', 'id')
1020                         ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
1021                         ->addFieldResult('l', 'location_id', 'id')
1022                         ->addFieldResult('l', 'zipcode', 'zipcode')
1023                         ->addIndexBy('s', 'id');
1024
1025                 //Send result
1026                 return $em
1027                         ->createNativeQuery($req, $rsm)
1028                         ->getResult();
1029         }
1030
1031         /**
1032          * Find every session pending application
1033          *
1034          * @return array<Session> The sessions to update
1035          */
1036         public function findAllPendingApplication() {
1037                 //Get entity manager
1038                 $em = $this->getEntityManager();
1039
1040                 //Get quote strategy
1041                 $qs = $em->getConfiguration()->getQuoteStrategy();
1042                 $dp = $em->getConnection()->getDatabasePlatform();
1043
1044                 //Get quoted table names
1045                 //XXX: this allow to make this code table name independent
1046                 $tables = [
1047                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
1048                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1049                         //Delay
1050                         ':regulardelay' => self::REGULAR_DELAY * 24 * 3600,
1051                         ':seniordelay' => self::SENIOR_DELAY * 24 * 3600,
1052                         //Slot
1053                         ':afterid' => 4,
1054                         "\t" => '',
1055                         "\n" => ' '
1056                 ];
1057
1058                 //Select all sessions not locked without application or canceled application within attribution period
1059                 //XXX: DIFF(start, now) <= IF(DIFF(start, created) <= SENIOR_DELAY in DAY, DIFF(start, created) * 3 / 4, SENIOR_DELAY)
1060                 //TODO: remonter les données pour le mail ?
1061                 $req =<<<SQL
1062 SELECT s.id
1063 FROM RapsysAirBundle:Session as s
1064 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id AND a.canceled IS NULL)
1065 JOIN RapsysAirBundle:Application AS a2 ON (a2.session_id = s.id AND a2.canceled IS NULL)
1066 WHERE s.locked IS NULL AND a.id IS NULL AND
1067 TIME_TO_SEC(TIMEDIFF(@dt_start := ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) <= IF(
1068         TIME_TO_SEC(@td_sc := TIMEDIFF(@dt_start, s.created)) <= :seniordelay,
1069         ROUND(TIME_TO_SEC(@td_sc) * :regulardelay / :seniordelay),
1070         :seniordelay
1071 )
1072 GROUP BY s.id
1073 ORDER BY @dt_start ASC, s.created ASC
1074 SQL;
1075
1076
1077                 //Replace bundle entity name by table name
1078                 $req = str_replace(array_keys($tables), array_values($tables), $req);
1079
1080                 //Get result set mapping instance
1081                 $rsm = new ResultSetMapping();
1082
1083                 //Declare all fields
1084                 $rsm
1085                         ->addEntityResult('RapsysAirBundle:Session', 's')
1086                         ->addFieldResult('s', 'id', 'id')
1087                         ->addIndexBy('s', 'id');
1088
1089                 //Send result
1090                 return $em
1091                         ->createNativeQuery($req, $rsm)
1092                         ->getResult();
1093         }
1094
1095         /**
1096          * Fetch session best application by session id
1097          *
1098          * @param int $id The session id
1099          * @return Application|null The application or null
1100          */
1101         public function findBestApplicationById($id) {
1102                 //Get entity manager
1103                 $em = $this->getEntityManager();
1104
1105                 //Get quote strategy
1106                 $qs = $em->getConfiguration()->getQuoteStrategy();
1107                 $dp = $em->getConnection()->getDatabasePlatform();
1108
1109                 //Get quoted table names
1110                 //XXX: this allow to make this code table name independent
1111                 $tables = [
1112                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
1113                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
1114                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
1115                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
1116                         //XXX: Set limit used to workaround mariadb subselect optimization
1117                         ':limit' => PHP_INT_MAX,
1118                         //Delay
1119                         ':guestdelay' => self::GUEST_DELAY * 24 * 3600,
1120                         ':regulardelay' => self::REGULAR_DELAY * 24 * 3600,
1121                         ':seniordelay' => self::SENIOR_DELAY * 24 * 3600,
1122                         //Group
1123                         ':guestid' => 2,
1124                         ':regularid' => 3,
1125                         ':seniorid' => 4,
1126                         //Slot
1127                         ':afternoonid' => 2,
1128                         ':eveningid' => 3,
1129                         ':afterid' => 4,
1130                         //XXX: days since last session after which guest regain normal priority
1131                         ':guestwait' => 30,
1132                         //XXX: session count until considered at regular delay
1133                         ':scount' => 5,
1134                         //XXX: pn_ratio over which considered at regular delay
1135                         ':pnratio' => 1,
1136                         //XXX: tr_ratio diff over which considered at regular delay
1137                         ':trdiff' => 5,
1138                         "\t" => '',
1139                         "\n" => ' '
1140                 ];
1141
1142                 /**
1143                  * Query session applications ranked by location score, global score, created and user_id
1144                  *
1145                  * @xxx guest (or less) with application on location within 30 day are only considered within guestdelay
1146                  *
1147                  * @xxx regular (or less) premium application on hotspot are only considered within regulardelay
1148                  *
1149                  * @xxx senior (or less) with 5 or less session on location are only considered within seniordelay
1150                  *
1151                  * @xxx senior (or less) with l_pn_ratio >= 1 are only considered within seniordelay
1152                  *
1153                  * @xxx senior (or less) with l_tr_ratio >= (o_tr_ratio + 5) are only considered within seniordelay
1154                  *
1155                  * @xxx only consider session within one year (may be unaccurate by the day with after session)
1156                  *
1157                  * @xxx rainfall may not be accessible for previous session and other session at d-4 (only at d-2)
1158                  *
1159                  * @todo ??? feedback the data to inform the rejected users ???
1160                  */
1161                 $req = <<<SQL
1162 SELECT e.id, e.l_score AS score
1163 FROM (
1164         SELECT
1165                 d.id,
1166                 d.user_id,
1167                 d.l_count,
1168                 d.l_score,
1169                 d.l_tr_ratio,
1170                 d.l_pn_ratio,
1171                 d.l_previous,
1172                 d.g_score,
1173                 d.o_tr_ratio,
1174                 MAX(gu.group_id) AS group_id,
1175                 d.remaining,
1176                 d.premium,
1177                 d.hotspot,
1178                 d.created
1179         FROM (
1180                 SELECT
1181                         c.id,
1182                         c.user_id,
1183                         c.l_count,
1184                         c.l_score,
1185                         c.l_tr_ratio,
1186                         c.l_pn_ratio,
1187                         c.l_previous,
1188                         c.g_score,
1189                         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,
1190                         c.remaining,
1191                         c.premium,
1192                         c.hotspot,
1193                         c.created
1194                 FROM (
1195                         SELECT
1196                                 b.id,
1197                                 b.user_id,
1198                                 b.session_id,
1199                                 b.date,
1200                                 b.location_id,
1201                                 b.l_count,
1202                                 b.l_score,
1203                                 b.l_tr_ratio,
1204                                 b.l_pn_ratio,
1205                                 b.l_previous,
1206                                 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,
1207                                 b.remaining,
1208                                 b.premium,
1209                                 b.hotspot,
1210                                 b.created
1211                         FROM (
1212                                 SELECT
1213                                         a.id,
1214                                         a.user_id,
1215                                         s.id AS session_id,
1216                                         s.date AS date,
1217                                         s.slot_id,
1218                                         s.location_id,
1219                                         COUNT(a2.id) AS l_count,
1220                                         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,
1221                                         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,
1222                                         (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,
1223                                         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,
1224                                         TIME_TO_SEC(TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) AS remaining,
1225                                         s.premium,
1226                                         l.hotspot,
1227                                         a.created
1228                                 FROM RapsysAirBundle:Session AS s
1229                                 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1230                                 JOIN RapsysAirBundle:Application AS a ON (a.session_id = s.id AND a.canceled IS NULL)
1231                                 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)
1232                                 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))
1233                                 WHERE s.id = :sid
1234                                 GROUP BY a.id
1235                                 ORDER BY NULL
1236                                 LIMIT 0, :limit
1237                         ) AS b
1238                         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)
1239                         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))
1240                         GROUP BY b.id
1241                         ORDER BY NULL
1242                         LIMIT 0, :limit
1243                 ) AS c
1244                 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)
1245                 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))
1246                 GROUP BY c.id
1247                 ORDER BY NULL
1248                 LIMIT 0, :limit
1249         ) AS d
1250         LEFT JOIN RapsysAirBundle:GroupUser AS gu ON (gu.user_id = d.user_id)
1251         GROUP BY d.id
1252         LIMIT 0, :limit
1253 ) AS e
1254 WHERE
1255         IF(e.group_id <= :guestid AND e.l_previous <= :guestwait, e.remaining <= :guestdelay, 1) AND
1256         IF(e.group_id <= :regularid AND e.premium = 1 AND e.hotspot = 1, e.remaining <= :regulardelay, 1) AND
1257         IF(e.group_id <= :seniorid AND e.l_count <= :scount, e.remaining <= :regulardelay, 1) AND
1258         IF(e.group_id <= :seniorid AND e.l_pn_ratio >= :pnratio, e.remaining <= :regulardelay, 1) AND
1259         IF(e.group_id <= :seniorid AND e.l_tr_ratio >= (e.o_tr_ratio + :trdiff), e.remaining <= :regulardelay, 1)
1260 ORDER BY e.l_score ASC, e.g_score ASC, e.created ASC, e.user_id ASC
1261 SQL;
1262
1263                 //Replace bundle entity name by table name
1264                 $req = str_replace(array_keys($tables), array_values($tables), $req);
1265
1266                 //Set update request
1267                 $upreq = 'UPDATE RapsysAirBundle:Application SET score = :score, updated = NOW() WHERE id = :id';
1268
1269                 //Replace bundle entity name by table name
1270                 $upreq = str_replace(array_keys($tables), array_values($tables), $upreq);
1271
1272                 //Get result set mapping instance
1273                 $rsm = new ResultSetMapping();
1274
1275                 //Declare all fields
1276                 $rsm
1277                         ->addEntityResult('RapsysAirBundle:Application', 'a')
1278                         ->addFieldResult('a', 'id', 'id')
1279                         ->addFieldResult('a', 'score', 'score')
1280                         ->addIndexBy('a', 'id');
1281
1282                 //Get result
1283                 //XXX: setting limit in subqueries is required to prevent mariadb optimisation
1284                 $applications = $em
1285                         ->createNativeQuery($req, $rsm)
1286                         ->setParameter('sid', $id)
1287                         //XXX: removed, we update score before returning best candidate
1288                         //->getOneOrNullResult(Query::HYDRATE_SINGLE_SCALAR);
1289                         ->getResult();
1290
1291                 //Init ret
1292                 $ret = null;
1293
1294                 //Update score
1295                 foreach($applications as $application) {
1296                         //Check if we already saved best candidate
1297                         if ($ret === null) {
1298                                 //Return first application
1299                                 $ret = $application;
1300                         }
1301
1302                         //Update application updated field
1303                         //XXX: updated field is not modified for user with bad behaviour as application is not retrieved until delay is reached
1304                         $em->getConnection()->executeUpdate($upreq, ['id' => $application->getId(), 'score' => $application->getScore()], ['id' => Type::INTEGER, 'score' => Type::FLOAT]);
1305                 }
1306
1307                 //Return best ranked application
1308                 return $ret;
1309         }
1310 }