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 user with bad behaviour delay
  21         const BAD_DELAY = 3;
  22
  23         ///Set user with good behaviour delay
  24         const GOOD_DELAY = 4;
  25
  26         ///Set glyphs
  27         //TODO: document utf-8 codes ?
  28         const GLYPHS = [
  29                 //Slots
  30                 'Morning' => '🌅', #0001f305
  31                 'Afternoon' => '☀️', #2600
  32                 'Evening' => '🌇', #0001f307
  33                 'After' => '✨', #2728
  34                 //Weathers
  35                 'Cleary' => '☀', #2600
  36                 'Sunny' => '⛅', #26c5
  37                 'Cloudy' => '☁', #2601
  38                 'Winty' => '❄️', #2744
  39                 'Rainy' => '🌂', #0001f302
  40                 'Stormy' => '☔' #2614
  41         ];
  42
  43         /**
  44          * Find session by location, slot and date
  45          *
  46          * @param $location The location
  47          * @param $slot The slot
  48          * @param $date The datetime
  49          */
  50         public function findOneByLocationSlotDate($location, $slot, $date) {
  51                 //Return sessions
  52                 return $this->getEntityManager()
  53                         ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.slot = :slot AND s.date = :date)')
  54                         ->setParameter('location', $location)
  55                         ->setParameter('slot', $slot)
  56                         ->setParameter('date', $date)
  57                         ->getSingleResult();
  58         }
  59
  60         /**
  61          * Find sessions by date period
  62          *
  63          * @param $period The date period
  64          */
  65         public function findAllByDatePeriod($period) {
  66                 //Return sessions
  67                 return $this->getEntityManager()
  68                         ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE s.date BETWEEN :begin AND :end')
  69                         ->setParameter('begin', $period->getStartDate())
  70                         ->setParameter('end', $period->getEndDate())
  71                         ->getResult();
  72         }
  73
  74         /**
  75          * Find sessions by location and date period
  76          *
  77          * @param $location The location
  78          * @param $period The date period
  79          */
  80         public function findAllByLocationDatePeriod($location, $period) {
  81                 //Return sessions
  82                 return $this->getEntityManager()
  83                         ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.date BETWEEN :begin AND :end)')
  84                         ->setParameter('location', $location)
  85                         ->setParameter('begin', $period->getStartDate())
  86                         ->setParameter('end', $period->getEndDate())
  87                         ->getResult();
  88         }
  89
  90         /**
  91          * Fetch session by id
  92          *
  93          * @param $id The session id
  94          * @return array The session data
  95          */
  96         public function fetchOneById($id) {
  97                 //Get entity manager
  98                 $em = $this->getEntityManager();
  99
 100                 //Get quote strategy
 101                 $qs = $em->getConfiguration()->getQuoteStrategy();
 102                 $dp = $em->getConnection()->getDatabasePlatform();
 103
 104                 //Get quoted table names
 105                 //XXX: this allow to make this code table name independent
 106                 $tables = [
 107                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 108                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 109                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 110                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 111                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 112                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 113                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp)
 114                 ];
 115
 116                 //Set the request
 117                 //TODO: compute scores ?
 118                 //TODO: compute delivery date ? (J-3/J-4 ?)
 119                 $req =<<<SQL
 120 SELECT
 121         s.id,
 122         s.date,
 123         s.begin,
 124         ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) AS start,
 125         s.length,
 126         ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) AS stop,
 127         s.rainfall,
 128         s.rainrisk,
 129         s.realfeel,
 130         s.realfeelmin,
 131         s.realfeelmax,
 132         s.temperature,
 133         s.temperaturemin,
 134         s.temperaturemax,
 135         s.created,
 136         s.updated,
 137         s.location_id AS l_id,
 138         l.short AS l_short,
 139         l.title AS l_title,
 140         l.address AS l_address,
 141         l.zipcode AS l_zipcode,
 142         l.city AS l_city,
 143         l.latitude AS l_latitude,
 144         l.longitude AS l_longitude,
 145         s.slot_id AS t_id,
 146         t.title AS t_title,
 147         s.application_id AS a_id,
 148         a.user_id AS au_id,
 149         au.pseudonym AS au_pseudonym,
 150         GROUP_CONCAT(sa.id ORDER BY sa.user_id SEPARATOR "\n") AS sa_id,
 151         GROUP_CONCAT(IFNULL(sa.score, 'NULL') ORDER BY sa.user_id SEPARATOR "\n") AS sa_score,
 152         GROUP_CONCAT(sa.created ORDER BY sa.user_id SEPARATOR "\n") AS sa_created,
 153         GROUP_CONCAT(sa.updated ORDER BY sa.user_id SEPARATOR "\n") AS sa_updated,
 154         GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\n") AS sa_canceled,
 155         GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\n") AS sau_id,
 156         GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\n") AS sau_pseudonym
 157 FROM RapsysAirBundle:Session AS s
 158 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 159 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 160 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 161 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 162 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 163 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 164 WHERE s.id = :sid
 165 GROUP BY s.id
 166 ORDER BY NULL
 167 SQL;
 168
 169                 //Replace bundle entity name by table name
 170                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 171
 172                 //Get result set mapping instance
 173                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 174                 $rsm = new ResultSetMapping();
 175
 176                 //Declare all fields
 177                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 178                 $rsm->addScalarResult('id', 'id', 'integer')
 179                         ->addScalarResult('date', 'date', 'date')
 180                         ->addScalarResult('begin', 'begin', 'time')
 181                         ->addScalarResult('start', 'start', 'datetime')
 182                         ->addScalarResult('length', 'length', 'time')
 183                         ->addScalarResult('stop', 'stop', 'datetime')
 184                         ->addScalarResult('rainfall', 'rainfall', 'float')
 185                         ->addScalarResult('rainrisk', 'rainrisk', 'float')
 186                         ->addScalarResult('realfeel', 'realfeel', 'float')
 187                         ->addScalarResult('realfeelmin', 'realfeelmin', 'float')
 188                         ->addScalarResult('realfeelmax', 'realfeelmax', 'float')
 189                         ->addScalarResult('temperature', 'temperature', 'float')
 190                         ->addScalarResult('temperaturemin', 'temperaturemin', 'float')
 191                         ->addScalarResult('temperaturemax', 'temperaturemax', 'float')
 192                         ->addScalarResult('created', 'created', 'datetime')
 193                         ->addScalarResult('updated', 'updated', 'datetime')
 194                         ->addScalarResult('l_id', 'l_id', 'integer')
 195                         ->addScalarResult('l_short', 'l_short', 'string')
 196                         ->addScalarResult('l_title', 'l_title', 'string')
 197                         ->addScalarResult('l_address', 'l_address', 'string')
 198                         ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
 199                         ->addScalarResult('l_city', 'l_city', 'string')
 200                         ->addScalarResult('l_latitude', 'l_latitude', 'float')
 201                         ->addScalarResult('l_longitude', 'l_longitude', 'float')
 202                         ->addScalarResult('t_id', 't_id', 'integer')
 203                         ->addScalarResult('t_title', 't_title', 'string')
 204                         ->addScalarResult('a_id', 'a_id', 'integer')
 205                         ->addScalarResult('au_id', 'au_id', 'integer')
 206                         ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
 207                         //XXX: is a string because of \n separator
 208                         ->addScalarResult('sa_id', 'sa_id', 'string')
 209                         //XXX: is a string because of \n separator
 210                         ->addScalarResult('sa_score', 'sa_score', 'string')
 211                         //XXX: is a string because of \n separator
 212                         ->addScalarResult('sa_created', 'sa_created', 'string')
 213                         //XXX: is a string because of \n separator
 214                         ->addScalarResult('sa_updated', 'sa_updated', 'string')
 215                         //XXX: is a string because of \n separator
 216                         ->addScalarResult('sa_canceled', 'sa_canceled', 'string')
 217                         //XXX: is a string because of \n separator
 218                         ->addScalarResult('sau_id', 'sau_id', 'string')
 219                         //XXX: is a string because of \n separator
 220                         ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
 221                         ->addIndexByScalar('id');
 222
 223                 //Return result
 224                 return $em
 225                         ->createNativeQuery($req, $rsm)
 226                         ->setParameter('sid', $id)
 227                         ->getOneOrNullResult();
 228         }
 229
 230         /**
 231          * Fetch sessions calendar with translated location by date period
 232          *
 233          * @param $translator The TranslatorInterface instance
 234          * @param $period The date period
 235          * @param $locationId The location id
 236          * @param $sessionId The session id
 237          * @param $granted The session is granted
 238          */
 239         public function fetchCalendarByDatePeriod(TranslatorInterface $translator, $period, $locationId = null, $sessionId = null, $granted = false) {
 240                 //Get entity manager
 241                 $em = $this->getEntityManager();
 242
 243                 //Get quote strategy
 244                 $qs = $em->getConfiguration()->getQuoteStrategy();
 245                 $dp = $em->getConnection()->getDatabasePlatform();
 246
 247                 //Get quoted table names
 248                 //XXX: this allow to make this code table name independent
 249                 $tables = [
 250                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 251                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 252                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 253                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 254                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 255                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 256                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
 257                         "\t" => '',
 258                         "\n" => ' '
 259                 ];
 260
 261                 //Set the request
 262                 //TODO: change as_u_* in sau_*, a_u_* in au_*, etc, see request up
 263                 $req = 'SELECT s.id, s.date, s.rainrisk, s.rainfall, s.realfeel, s.temperature, s.location_id AS l_id, l.short AS l_short, l.title AS l_title, s.slot_id AS t_id, t.title AS t_title, s.application_id AS a_id, a.user_id AS a_u_id, au.pseudonym AS a_u_pseudonym, GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\n") AS as_u_id, GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\n") AS as_u_pseudonym
 264                         FROM RapsysAirBundle:Session AS s
 265                         JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 266                         JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 267                         '.($granted?'':'LEFT ').'JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 268                         '.($granted?'':'LEFT ').'JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 269                         LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 270                         LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 271                         WHERE s.date BETWEEN :begin AND :end
 272                         '.($locationId?'AND s.location_id = :lid':'').'
 273                         GROUP BY s.id
 274                         ORDER BY NULL';
 275
 276                 //Replace bundle entity name by table name
 277                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 278
 279                 //Get result set mapping instance
 280                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 281                 $rsm = new ResultSetMapping();
 282
 283                 //Declare all fields
 284                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 285                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 286                 $rsm->addScalarResult('id', 'id', 'integer')
 287                         ->addScalarResult('date', 'date', 'date')
 288                         ->addScalarResult('rainrisk', 'rainrisk', 'float')
 289                         ->addScalarResult('rainfall', 'rainfall', 'float')
 290                         ->addScalarResult('realfeel', 'realfeel', 'float')
 291                         ->addScalarResult('temperature', 'temperature', 'float')
 292                         ->addScalarResult('t_id', 't_id', 'integer')
 293                         ->addScalarResult('t_title', 't_title', 'string')
 294                         ->addScalarResult('l_id', 'l_id', 'integer')
 295                         ->addScalarResult('l_short', 'l_short', 'string')
 296                         ->addScalarResult('l_title', 'l_title', 'string')
 297                         ->addScalarResult('a_id', 'a_id', 'integer')
 298                         ->addScalarResult('a_u_id', 'a_u_id', 'integer')
 299                         ->addScalarResult('a_u_pseudonym', 'a_u_pseudonym', 'string')
 300                         //XXX: is a string because of \n separator
 301                         ->addScalarResult('as_u_id', 'as_u_id', 'string')
 302                         //XXX: is a string because of \n separator
 303                         ->addScalarResult('as_u_pseudonym', 'as_u_pseudonym', 'string')
 304                         ->addIndexByScalar('id');
 305
 306                 //Fetch result
 307                 $res = $em
 308                         ->createNativeQuery($req, $rsm)
 309                         ->setParameter('begin', $period->getStartDate())
 310                         ->setParameter('end', $period->getEndDate())
 311                         ->setParameter('lid', $locationId)
 312                         ->getResult();
 313
 314                 //Init calendar
 315                 $calendar = [];
 316
 317                 //Init month
 318                 $month = null;
 319
 320                 //Iterate on each day
 321                 foreach($period as $date) {
 322                         //Init day in calendar
 323                         $calendar[$Ymd = $date->format('Ymd')] = [
 324                                 'title' => $date->format('d'),
 325                                 'class' => [],
 326                                 'sessions' => []
 327                         ];
 328
 329                         //Detect month change
 330                         if ($month != $date->format('m')) {
 331                                 $month = $date->format('m');
 332                                 //Append month for first day of month
 333                                 //XXX: except if today to avoid double add
 334                                 if ($date->format('U') != strtotime('today')) {
 335                                         $calendar[$Ymd]['title'] .= '/'.$month;
 336                                 }
 337                         }
 338                         //Deal with today
 339                         if ($date->format('U') == ($today = strtotime('today'))) {
 340                                 $calendar[$Ymd]['title'] .= '/'.$month;
 341                                 $calendar[$Ymd]['current'] = true;
 342                                 $calendar[$Ymd]['class'][] = 'current';
 343                         }
 344                         //Disable passed days
 345                         if ($date->format('U') < $today) {
 346                                 $calendar[$Ymd]['disabled'] = true;
 347                                 $calendar[$Ymd]['class'][] = 'disabled';
 348                         }
 349                         //Set next month days
 350                         if ($date->format('m') > date('m')) {
 351                                 $calendar[$Ymd]['next'] = true;
 352                                 $calendar[$Ymd]['class'][] = 'next';
 353                         }
 354
 355                         //Iterate on each session to find the one of the day
 356                         foreach($res as $session) {
 357                                 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
 358                                         //Count number of application
 359                                         $count = count(explode("\n", $session['as_u_id']));
 360
 361                                         //Compute classes
 362                                         $class = [];
 363                                         if (!empty($session['a_id'])) {
 364                                                 $applications = [ $session['a_u_id'] => $session['a_u_pseudonym'] ];
 365                                                 $class[] = 'granted';
 366                                         } elseif ($count == 0) {
 367                                                 $class[] = 'orphaned';
 368                                         } elseif ($count > 1) {
 369                                                 $class[] = 'disputed';
 370                                         } else {
 371                                                 $class[] = 'pending';
 372                                         }
 373
 374                                         if ($sessionId == $session['id']) {
 375                                                 $class[] = 'highlight';
 376                                         }
 377
 378                                         //Set temperature
 379                                         //XXX: realfeel may be null, temperature should not
 380                                         $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
 381
 382                                         //Compute weather
 383                                         //XXX: rainfall may be null
 384                                         if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
 385                                                 $weather = self::GLYPHS['Stormy'];
 386                                         } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
 387                                                 $weather = self::GLYPHS['Rainy'];
 388                                         } elseif ($temperature > 24) {
 389                                                 $weather = self::GLYPHS['Cleary'];
 390                                         } elseif ($temperature > 17) {
 391                                                 $weather = self::GLYPHS['Sunny'];
 392                                         } elseif ($temperature > 10) {
 393                                                 $weather = self::GLYPHS['Cloudy'];
 394                                         } elseif ($temperature !== null) {
 395                                                 $weather = self::GLYPHS['Winty'];
 396                                         } else {
 397                                                 $weather = null;
 398                                         }
 399
 400                                         //Init weathertitle
 401                                         $weathertitle = [];
 402
 403                                         //Check if realfeel is available
 404                                         if ($session['realfeel'] !== null) {
 405                                                 $weathertitle[] = $session['realfeel'].'°R';
 406                                         }
 407
 408                                         //Check if temperature is available
 409                                         if ($session['temperature'] !== null) {
 410                                                 $weathertitle[] = $session['temperature'].'°C';
 411                                         }
 412
 413                                         //Check if rainrisk is available
 414                                         if ($session['rainrisk'] !== null) {
 415                                                 $weathertitle[] = ($session['rainrisk']*100).'%';
 416                                         }
 417
 418                                         //Check if rainfall is available
 419                                         if ($session['rainfall'] !== null) {
 420                                                 $weathertitle[] = $session['rainfall'].'mm';
 421                                         }
 422
 423                                         //Set applications
 424                                         $applications = [
 425                                                 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).$translator->trans(':')
 426                                         ];
 427
 428                                         //Fetch pseudonyms from session applications
 429                                         $applications += array_combine(explode("\n", $session['as_u_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['as_u_pseudonym'])));
 430
 431                                         //Check that session is not granted
 432                                         if (empty($session['a_id'])) {
 433                                                 //With location id and unique application
 434                                                 if ($locationId && $count == 1) {
 435                                                         //Set unique application pseudonym as title
 436                                                         $title = $session['as_u_pseudonym'];
 437                                                 //Without location id or multiple application
 438                                                 } else {
 439                                                         //Set location title with optional count
 440                                                         $title = $translator->trans($session['l_title']).($count > 1 ? ' ['.$count.']':'');
 441                                                 }
 442                                         //Session is granted
 443                                         } else {
 444                                                 //Replace granted application
 445                                                 $applications[$session['a_u_id']] = '* '.$session['a_u_pseudonym'];
 446                                                 //Set pseudonym with optional location title and count
 447                                                 $title = $session['a_u_pseudonym'].($locationId?'':' '.$translator->trans('at '.$session['l_short'])).($count > 1 ? ' ['.$count.']':'');
 448                                         }
 449
 450                                         //Add the session
 451                                         $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [
 452                                                 'id' => $session['id'],
 453                                                 'title' => $title,
 454                                                 'class' => $class,
 455                                                 'slot' => self::GLYPHS[$session['t_title']],
 456                                                 'slottitle' => $translator->trans($session['t_title']),
 457                                                 'weather' => $weather,
 458                                                 'weathertitle' => implode(' ', $weathertitle),
 459                                                 'applications' => $applications
 460                                         ];
 461                                 }
 462                         }
 463
 464                         //Sort sessions
 465                         ksort($calendar[$Ymd]['sessions']);
 466                 }
 467
 468                 //Send result
 469                 return $calendar;
 470         }
 471
 472         /**
 473          * Fetch sessions calendar with translated location by date period and user
 474          *
 475          * @param $translator The TranslatorInterface instance
 476          * @param $period The date period
 477          * @param $userId The user id
 478          * @param $sessionId The session id
 479          */
 480         public function fetchUserCalendarByDatePeriod(TranslatorInterface $translator, $period, $userId = null, $sessionId = null) {
 481                 //Get entity manager
 482                 $em = $this->getEntityManager();
 483
 484                 //Get quote strategy
 485                 $qs = $em->getConfiguration()->getQuoteStrategy();
 486                 $dp = $em->getConnection()->getDatabasePlatform();
 487
 488                 //Get quoted table names
 489                 //XXX: this allow to make this code table name independent
 490                 $tables = [
 491                         'RapsysAirBundle:GroupUser' => $qs->getJoinTableName($em->getClassMetadata('RapsysAirBundle:User')->getAssociationMapping('groups'), $em->getClassMetadata('RapsysAirBundle:User'), $dp),
 492                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 493                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 494                         'RapsysAirBundle:Group' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Group'), $dp),
 495                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 496                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 497                         'RapsysAirBundle:User' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:User'), $dp),
 498                         "\t" => '',
 499                         "\n" => ' '
 500                 ];
 501
 502                 //Set the request
 503                 $req = 'SELECT s.id, s.date, s.rainrisk, s.rainfall, s.realfeel, s.temperature, s.location_id AS l_id, l.short AS l_short, l.title AS l_title, s.slot_id AS t_id, t.title AS t_title, s.application_id AS a_id, a.user_id AS a_u_id, au.pseudonym AS a_u_pseudonym, GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\n") AS as_u_id, GROUP_CONCAT(CONCAT("- ", sau.pseudonym) ORDER BY sa.user_id SEPARATOR "\n") AS as_u_pseudonym
 504                         FROM RapsysAirBundle:Session AS s
 505                         JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 506                         JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
 507                         '.($userId?'JOIN RapsysAirBundle:Application AS sua ON (sua.session_id = s.id)':'').'
 508                         LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
 509                         LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
 510                         LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
 511                         LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
 512                         WHERE s.date BETWEEN :begin AND :end
 513                         '.($userId?'AND sua.user_id = :uid':'').'
 514                         GROUP BY s.id
 515                         ORDER BY NULL';
 516
 517                 //Replace bundle entity name by table name
 518                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 519
 520                 //Get result set mapping instance
 521                 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
 522                 $rsm = new ResultSetMapping();
 523
 524                 //Declare all fields
 525                 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 526                 //addScalarResult($sqlColName, $resColName, $type = 'string');
 527                 $rsm->addScalarResult('id', 'id', 'integer')
 528                         ->addScalarResult('date', 'date', 'date')
 529                         ->addScalarResult('rainrisk', 'rainrisk', 'float')
 530                         ->addScalarResult('rainfall', 'rainfall', 'float')
 531                         ->addScalarResult('realfeel', 'realfeel', 'float')
 532                         ->addScalarResult('temperature', 'temperature', 'float')
 533                         ->addScalarResult('t_id', 't_id', 'integer')
 534                         ->addScalarResult('t_title', 't_title', 'string')
 535                         ->addScalarResult('l_id', 'l_id', 'integer')
 536                         ->addScalarResult('l_short', 'l_short', 'string')
 537                         ->addScalarResult('l_title', 'l_title', 'string')
 538                         ->addScalarResult('a_id', 'a_id', 'integer')
 539                         ->addScalarResult('a_u_id', 'a_u_id', 'integer')
 540                         ->addScalarResult('a_u_pseudonym', 'a_u_pseudonym', 'string')
 541                         //XXX: is a string because of \n separator
 542                         ->addScalarResult('as_u_id', 'as_u_id', 'string')
 543                         //XXX: is a string because of \n separator
 544                         ->addScalarResult('as_u_pseudonym', 'as_u_pseudonym', 'string')
 545                         ->addIndexByScalar('id');
 546
 547                 //Fetch result
 548                 $res = $em
 549                         ->createNativeQuery($req, $rsm)
 550                         ->setParameter('begin', $period->getStartDate())
 551                         ->setParameter('end', $period->getEndDate())
 552                         ->setParameter('uid', $userId)
 553                         ->getResult();
 554
 555                 //Init calendar
 556                 $calendar = [];
 557
 558                 //Init month
 559                 $month = null;
 560
 561                 //Iterate on each day
 562                 foreach($period as $date) {
 563                         //Init day in calendar
 564                         $calendar[$Ymd = $date->format('Ymd')] = [
 565                                 'title' => $date->format('d'),
 566                                 'class' => [],
 567                                 'sessions' => []
 568                         ];
 569
 570                         //Detect month change
 571                         if ($month != $date->format('m')) {
 572                                 $month = $date->format('m');
 573                                 //Append month for first day of month
 574                                 //XXX: except if today to avoid double add
 575                                 if ($date->format('U') != strtotime('today')) {
 576                                         $calendar[$Ymd]['title'] .= '/'.$month;
 577                                 }
 578                         }
 579                         //Deal with today
 580                         if ($date->format('U') == ($today = strtotime('today'))) {
 581                                 $calendar[$Ymd]['title'] .= '/'.$month;
 582                                 $calendar[$Ymd]['current'] = true;
 583                                 $calendar[$Ymd]['class'][] = 'current';
 584                         }
 585                         //Disable passed days
 586                         if ($date->format('U') < $today) {
 587                                 $calendar[$Ymd]['disabled'] = true;
 588                                 $calendar[$Ymd]['class'][] = 'disabled';
 589                         }
 590                         //Set next month days
 591                         if ($date->format('m') > date('m')) {
 592                                 $calendar[$Ymd]['next'] = true;
 593                                 $calendar[$Ymd]['class'][] = 'next';
 594                         }
 595
 596                         //Iterate on each session to find the one of the day
 597                         foreach($res as $session) {
 598                                 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
 599                                         //Count number of application
 600                                         $count = count(explode("\n", $session['as_u_id']));
 601
 602                                         //Compute classes
 603                                         $class = [];
 604                                         if (!empty($session['a_id'])) {
 605                                                 $applications = [ $session['a_u_id'] => $session['a_u_pseudonym'] ];
 606                                                 if ($session['a_u_id'] == $userId) {
 607                                                         $class[] = 'granted';
 608                                                 } else {
 609                                                         $class[] = 'disputed';
 610                                                 }
 611                                         } elseif ($count == 0) {
 612                                                 $class[] = 'orphaned';
 613                                         } elseif ($count > 1) {
 614                                                 $class[] = 'disputed';
 615                                         } else {
 616                                                 $class[] = 'pending';
 617                                         }
 618
 619                                         if ($sessionId == $session['id']) {
 620                                                 $class[] = 'highlight';
 621                                         }
 622
 623                                         //Set temperature
 624                                         //XXX: realfeel may be null, temperature should not
 625                                         $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
 626
 627                                         //Compute weather
 628                                         //XXX: rainfall may be null
 629                                         if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
 630                                                 $weather = self::GLYPHS['Stormy'];
 631                                         } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
 632                                                 $weather = self::GLYPHS['Rainy'];
 633                                         } elseif ($temperature > 24) {
 634                                                 $weather = self::GLYPHS['Cleary'];
 635                                         } elseif ($temperature > 17) {
 636                                                 $weather = self::GLYPHS['Sunny'];
 637                                         } elseif ($temperature > 10) {
 638                                                 $weather = self::GLYPHS['Cloudy'];
 639                                         } elseif ($temperature !== null) {
 640                                                 $weather = self::GLYPHS['Winty'];
 641                                         } else {
 642                                                 $weather = null;
 643                                         }
 644
 645                                         //Init weathertitle
 646                                         $weathertitle = [];
 647
 648                                         //Check if realfeel is available
 649                                         if ($session['realfeel'] !== null) {
 650                                                 $weathertitle[] = $session['realfeel'].'°R';
 651                                         }
 652
 653                                         //Check if temperature is available
 654                                         if ($session['temperature'] !== null) {
 655                                                 $weathertitle[] = $session['temperature'].'°C';
 656                                         }
 657
 658                                         //Check if rainrisk is available
 659                                         if ($session['rainrisk'] !== null) {
 660                                                 $weathertitle[] = ($session['rainrisk']*100).'%';
 661                                         }
 662
 663                                         //Check if rainfall is available
 664                                         if ($session['rainfall'] !== null) {
 665                                                 $weathertitle[] = $session['rainfall'].'mm';
 666                                         }
 667
 668                                         //Set applications
 669                                         $applications = [
 670                                                 0 => $translator->trans($session['t_title']).' '.$translator->trans('at '.$session['l_title']).$translator->trans(':')
 671                                         ];
 672
 673                                         //Check that session is not granted
 674                                         if (empty($session['a_id'])) {
 675                                                 //Fetch pseudonyms from session applications
 676                                                 $applications += array_combine(explode("\n", $session['as_u_id']), explode("\n", $session['as_u_pseudonym']));
 677                                         //Session is granted
 678                                         } else {
 679                                                 //Fetch pseudonyms from session applications
 680                                                 $applications += array_combine(explode("\n", $session['as_u_id']), explode("\n", $session['as_u_pseudonym']));
 681                                                 //Replace granted application
 682                                                 $applications[$session['a_u_id']] = '* '.$session['a_u_pseudonym'];
 683                                         }
 684
 685                                         //Set title
 686                                         $title = $translator->trans($session['l_title']).($count > 1 ? ' ['.$count.']':'');
 687
 688                                         //Add the session
 689                                         $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [
 690                                                 'id' => $session['id'],
 691                                                 'title' => $title,
 692                                                 'class' => $class,
 693                                                 'slot' => self::GLYPHS[$session['t_title']],
 694                                                 'slottitle' => $translator->trans($session['t_title']),
 695                                                 'weather' => $weather,
 696                                                 'weathertitle' => implode(' ', $weathertitle),
 697                                                 'applications' => $applications
 698                                         ];
 699                                 }
 700                         }
 701
 702                         //Sort sessions
 703                         ksort($calendar[$Ymd]['sessions']);
 704                 }
 705
 706                 //Send result
 707                 return $calendar;
 708         }
 709
 710         /**
 711          * Find every session pending application
 712          *
 713          * @return array<Session> The sessions to update
 714          */
 715         public function findAllPendingApplication() {
 716                 //Get entity manager
 717                 $em = $this->getEntityManager();
 718
 719                 //Get quote strategy
 720                 $qs = $em->getConfiguration()->getQuoteStrategy();
 721                 $dp = $em->getConnection()->getDatabasePlatform();
 722
 723                 //Get quoted table names
 724                 //XXX: this allow to make this code table name independent
 725                 $tables = [
 726                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 727                         "\t" => '',
 728                         "\n" => ' '
 729                 ];
 730
 731                 //Select all sessions without application attributed with diff(start, now) <= if(diff(start, created) <= GOOD_DELAY day, diff(start, created)*3/4, GOOD_DELAY day)
 732                 //XXX: consider only unfinished sessions with stop > now
 733                 //XXX: consider creation enough in the past when we are after 1/4 of the period between creation and start time
 734                 //XXX: we may remove ADDDATE(start, INTERVAL IF(s.slot_id = 4, 1, 0) DAY) used for after specificity
 735                 $req =<<<SQL
 736 SELECT s.id
 737 FROM RapsysAirBundle:Session AS s
 738 WHERE
 739         s.application_id IS NULL AND
 740         ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) > NOW() AND
 741         TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY), NOW()) <= IF(DATEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY), s.created) <= :gooddelay, SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY), s.created))*3/4), SEC_TO_TIME(:gooddelay*24*3600))
 742 ORDER BY ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) ASC, s.created ASC
 743 SQL;
 744
 745                 //Replace bundle entity name by table name
 746                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 747
 748                 //Get result set mapping instance
 749                 $rsm = new ResultSetMapping();
 750
 751                 //Declare all fields
 752                 $rsm
 753                         ->addEntityResult('RapsysAirBundle:Session', 's')
 754                         ->addFieldResult('s', 'id', 'id')
 755                         ->addIndexBy('s', 'id');
 756
 757                 //Send result
 758                 return $em
 759                         ->createNativeQuery($req, $rsm)
 760                         ->setParameter('limit', PHP_INT_MAX)
 761                         ->setParameter('gooddelay', self::GOOD_DELAY)
 762                         ->getResult();
 763         }
 764
 765         /**
 766          * Find all session pending hourly weather
 767          *
 768          * @return array<Session> The sessions to update
 769          */
 770         public function findAllPendingHourlyWeather() {
 771                 //Get entity manager
 772                 $em = $this->getEntityManager();
 773
 774                 //Get quote strategy
 775                 $qs = $em->getConfiguration()->getQuoteStrategy();
 776                 $dp = $em->getConnection()->getDatabasePlatform();
 777
 778                 //Get quoted table names
 779                 //XXX: this allow to make this code table name independent
 780                 $tables = [
 781                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 782                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 783                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 784                         "\t" => '',
 785                         "\n" => ' '
 786                 ];
 787
 788                 //Select all sessions starting and stopping in the next 3 days
 789                 //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)
 790                 //XXX: we may remove ADDDATE(start, INTERVAL IF(s.slot_id = 4, 1, 0) DAY) used for after specificity
 791                 $req = <<<SQL
 792 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, o.title
 793 FROM RapsysAirBundle:Session AS s
 794 JOIN RapsysAirBundle:Slot AS o ON (o.id = s.slot_id)
 795 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 796 WHERE ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) >= NOW() AND ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) < DATE(ADDDATE(NOW(), INTERVAL :accuhourly DAY))
 797 SQL;
 798
 799                 //Replace bundle entity name by table name
 800                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 801
 802                 //Get result set mapping instance
 803                 $rsm = new ResultSetMapping();
 804
 805                 //Declare all fields
 806                 $rsm
 807                         ->addEntityResult('RapsysAirBundle:Session', 's')
 808                         ->addFieldResult('s', 'id', 'id')
 809                         ->addFieldResult('s', 'date', 'date')
 810                         ->addFieldResult('s', 'begin', 'begin')
 811                         ->addFieldResult('s', 'length', 'length')
 812                         ->addFieldResult('s', 'rainfall', 'rainfall')
 813                         ->addFieldResult('s', 'rainrisk', 'rainrisk')
 814                         ->addFieldResult('s', 'realfeel', 'realfeel')
 815                         ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
 816                         ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
 817                         ->addFieldResult('s', 'temperature', 'temperature')
 818                         ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
 819                         ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
 820                         ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
 821                         ->addFieldResult('o', 'slot_id', 'id')
 822                         ->addFieldResult('o', 'title', 'title')
 823                         ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
 824                         ->addFieldResult('l', 'location_id', 'id')
 825                         ->addFieldResult('l', 'zipcode', 'zipcode')
 826                         ->addIndexBy('s', 'id');
 827
 828                 //Send result
 829                 return $em
 830                         ->createNativeQuery($req, $rsm)
 831                         ->setParameter('accuhourly', self::ACCUWEATHER_HOURLY)
 832                         ->getResult();
 833         }
 834
 835         /**
 836          * Find all session pending daily weather
 837          *
 838          * @return array<Session> The sessions to update
 839          */
 840         public function findAllPendingDailyWeather() {
 841                 //Get entity manager
 842                 $em = $this->getEntityManager();
 843
 844                 //Get quote strategy
 845                 $qs = $em->getConfiguration()->getQuoteStrategy();
 846                 $dp = $em->getConnection()->getDatabasePlatform();
 847
 848                 //Get quoted table names
 849                 //XXX: this allow to make this code table name independent
 850                 $tables = [
 851                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 852                         'RapsysAirBundle:Slot' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Slot'), $dp),
 853                         'RapsysAirBundle:Location' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Location'), $dp),
 854                         "\t" => '',
 855                         "\n" => ' '
 856                 ];
 857
 858                 //Select all sessions stopping after next 3 days
 859                 //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
 860                 //XXX: we may remove ADDDATE(start, INTERVAL IF(s.slot_id = 4, 1, 0) DAY) used for after specificity
 861                 $req = <<<SQL
 862 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, o.title
 863 FROM RapsysAirBundle:Session AS s
 864 JOIN RapsysAirBundle:Slot AS o ON (o.id = s.slot_id)
 865 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
 866 WHERE ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) >= DATE(ADDDATE(NOW(), INTERVAL :accuhourly DAY)) AND ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = 4, 1, 0) DAY) < DATE(ADDDATE(NOW(), INTERVAL :accudaily DAY))
 867 SQL;
 868
 869                 //Replace bundle entity name by table name
 870                 $req = str_replace(array_keys($tables), array_values($tables), $req);
 871
 872                 //Get result set mapping instance
 873                 $rsm = new ResultSetMapping();
 874
 875                 //Declare all fields
 876                 $rsm
 877                         ->addEntityResult('RapsysAirBundle:Session', 's')
 878                         ->addFieldResult('s', 'id', 'id')
 879                         ->addFieldResult('s', 'date', 'date')
 880                         ->addFieldResult('s', 'begin', 'begin')
 881                         ->addFieldResult('s', 'length', 'length')
 882                         ->addFieldResult('s', 'rainfall', 'rainfall')
 883                         ->addFieldResult('s', 'rainrisk', 'rainrisk')
 884                         ->addFieldResult('s', 'realfeel', 'realfeel')
 885                         ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
 886                         ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
 887                         ->addFieldResult('s', 'temperature', 'temperature')
 888                         ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
 889                         ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
 890                         ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
 891                         ->addFieldResult('o', 'slot_id', 'id')
 892                         ->addFieldResult('o', 'title', 'title')
 893                         ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
 894                         ->addFieldResult('l', 'location_id', 'id')
 895                         ->addFieldResult('l', 'zipcode', 'zipcode')
 896                         ->addIndexBy('s', 'id');
 897
 898                 //Send result
 899                 return $em
 900                         ->createNativeQuery($req, $rsm)
 901                         ->setParameter('accudaily', self::ACCUWEATHER_DAILY)
 902                         ->setParameter('accuhourly', self::ACCUWEATHER_HOURLY)
 903                         ->getResult();
 904         }
 905
 906         /**
 907          * Fetch session best application by session id
 908          *
 909          * @param int $id The session id
 910          * @return Application|null The application or null
 911          */
 912         public function findBestApplicationById($id) {
 913                 //Get entity manager
 914                 $em = $this->getEntityManager();
 915
 916                 //Get quote strategy
 917                 $qs = $em->getConfiguration()->getQuoteStrategy();
 918                 $dp = $em->getConnection()->getDatabasePlatform();
 919
 920                 //Get quoted table names
 921                 //XXX: this allow to make this code table name independent
 922                 $tables = [
 923                         'RapsysAirBundle:Application' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Application'), $dp),
 924                         'RapsysAirBundle:Session' => $qs->getTableName($em->getClassMetadata('RapsysAirBundle:Session'), $dp),
 925                         "\t" => '',
 926                         "\n" => ' '
 927                 ];
 928
 929                 /**
 930                  * Query session applications ranked by score, created and user_id
 931                  *
 932                  * @xxx User with bad behaviour application are excluded until remaining <= baddelay:
 933                  * - with (count(premium = 1)+1)/(count(premium = 0)+1) >= 1
 934                  * - with count(sessions) <= 5
 935                  * - with average(temperature) >= average(temperature other) + 10
 936                  *
 937                  * @xxx Magic happen on this line:
 938                  * WHERE IF(d.pnp_rate >= 1, d.remaining <= SEC_TO_TIME(:baddelay), 1) AND IF(d.s_count <= 5, d.remaining <= SEC_TO_TIME(:baddelay), 1) AND IF(d.tr_rate >= (d.otr_rate + 10), d.remaining <= SEC_TO_TIME(:baddelay), 1)
 939                  *
 940                  * @todo Limit score on last year only ???
 941                  * AND DATEDIFF(s.date, NOW()) <= 365
 942                  */
 943                 $req = <<<SQL
 944 SELECT d.id, d.score
 945 FROM (
 946         SELECT
 947                 c.id,
 948                 c.session_id,
 949                 c.user_id,
 950                 c.score,
 951                 c.remaining,
 952                 c.created,
 953                 c.s_count,
 954                 c.tr_rate,
 955                 AVG(IF(a4.id IS NOT NULL, s4.temperature/(1+s4.rainfall), NULL)) AS otr_rate,
 956                 c.pnp_rate
 957         FROM (
 958                 SELECT
 959                         b.id,
 960                         b.session_id,
 961                         b.user_id,
 962                         b.score,
 963                         b.remaining,
 964                         b.created,
 965                         COUNT(a3.id) AS s_count,
 966                         AVG(IF(a3.id IS NOT NULL AND s3.temperature IS NOT NULL AND s3.rainfall IS NOT NULL, s3.temperature/(1+s3.rainfall), NULL)) AS tr_rate,
 967                         (SUM(IF(a3.id IS NOT NULL AND s3.premium = 1, 1, 0))+1)/(SUM(IF(a3.id IS NOT NULL AND s3.premium = 0, 1, 0))+1) AS pnp_rate
 968                 FROM (
 969                         SELECT
 970                                 a.id,
 971                                 s.id AS session_id,
 972                                 a.user_id,
 973                                 SUM(IF(a2.id IS NOT NULL, 1/ABS(DATEDIFF(s.date, s2.date)), 0)) AS score,
 974                                 TIMEDIFF(ADDTIME(s.date, s.begin), NOW()) AS remaining,
 975                                 a.created
 976                         FROM sessions AS s
 977                         JOIN applications AS a ON (a.session_id = s.id AND a.canceled IS NULL)
 978                         LEFT JOIN sessions AS s2 ON (s2.id != s.id AND s2.location_id = s.location_id AND s2.slot_id = s.slot_id AND s2.application_id IS NOT NULL)
 979                         LEFT JOIN applications 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, ADDTIME(s2.date, s2.begin)) < 1))
 980                         WHERE s.id = :sid
 981                         GROUP BY a.id
 982                         ORDER BY NULL
 983                         LIMIT 0, :limit
 984                 ) AS b
 985                 LEFT JOIN sessions AS s3 ON (s3.id != b.session_id AND s3.application_id IS NOT NULL)
 986                 LEFT JOIN applications 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, ADDTIME(s3.date, s3.begin)) < 1))
 987                 GROUP BY b.id
 988                 ORDER BY NULL
 989                 LIMIT 0, :limit
 990         ) AS c
 991         LEFT JOIN sessions AS s4 ON (s4.id != c.session_id AND s4.application_id IS NOT NULL AND s4.temperature IS NOT NULL AND s4.rainfall IS NOT NULL)
 992         LEFT JOIN applications 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, ADDTIME(s4.date, s4.begin)) < 1))
 993         GROUP BY c.id
 994         ORDER BY NULL
 995         LIMIT 0, :limit
 996 ) AS d
 997 WHERE IF(d.pnp_rate >= 1, d.remaining <= SEC_TO_TIME(:baddelay), 1) AND IF(d.s_count <= 5, d.remaining <= SEC_TO_TIME(:baddelay), 1) AND IF(d.tr_rate >= (d.otr_rate + 10), d.remaining <= SEC_TO_TIME(:baddelay), 1)
 998 ORDER BY d.score ASC, d.created ASC, d.user_id ASC
 999 SQL;
1000
1001                 //Replace bundle entity name by table name
1002                 $req = str_replace(array_keys($tables), array_values($tables), $req);
1003
1004                 //Set update request
1005                 $upreq = 'UPDATE RapsysAirBundle:Application SET score = :score, updated = NOW() WHERE id = :id';
1006
1007                 //Replace bundle entity name by table name
1008                 $upreq = str_replace(array_keys($tables), array_values($tables), $upreq);
1009
1010                 //Get result set mapping instance
1011                 $rsm = new ResultSetMapping();
1012
1013                 //Declare all fields
1014                 $rsm
1015                         ->addEntityResult('RapsysAirBundle:Application', 'a')
1016                         ->addFieldResult('a', 'id', 'id')
1017                         ->addFieldResult('a', 'score', 'score')
1018                         ->addIndexBy('a', 'id');
1019
1020                 //Get result
1021                 //XXX: setting limit in subqueries is required to prevent mariadb optimisation
1022                 $applications = $em
1023                         ->createNativeQuery($req, $rsm)
1024                         ->setParameter('sid', $id)
1025                         ->setParameter('baddelay', self::BAD_DELAY*24*3600)
1026                         ->setParameter('limit', PHP_INT_MAX)
1027                         //XXX: removed, we update score before returning best candidate
1028                         //->getOneOrNullResult(Query::HYDRATE_SINGLE_SCALAR);
1029                         ->getResult();
1030
1031                 //Init ret
1032                 $ret = null;
1033
1034                 //Update score
1035                 foreach($applications as $application) {
1036                         //Check if we already saved best candidate
1037                         if ($ret === null) {
1038                                 //Return first application
1039                                 $ret = $application;
1040                         }
1041
1042                         //Update application updated field
1043                         //XXX: updated field is not modified for user with bad behaviour as application is not retrieved until delay is reached
1044                         $em->getConnection()->executeUpdate($upreq, ['id' => $application->getId(), 'score' => $application->getScore()], ['id' => Type::INTEGER, 'score' => Type::FLOAT]);
1045                 }
1046
1047                 //Return best ranked application
1048                 return $ret;
1049         }
1050 }