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