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