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

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