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