New title layout

[airbundle] / Repository / SessionRepository.php

diff --git a/Repository/SessionRepository.php b/Repository/SessionRepository.php
index 7c6f57417bd4c1964a07c816bb4028cd32d49691..13318fb16db2c31d02c3538f15c7764dc6df081f 100644 (file)
--- a/Repository/SessionRepository.php
+++ b/Repository/SessionRepository.php
@@ -12,13 +12,21 @@
 namespace Rapsys\AirBundle\Repository;
 
 use Doctrine\DBAL\Types\Types;
 namespace Rapsys\AirBundle\Repository;
 
 use Doctrine\DBAL\Types\Types;

+use Doctrine\ORM\AbstractQuery;

 use Doctrine\ORM\Query\ResultSetMapping;
 use Doctrine\ORM\Query\ResultSetMapping;

+

 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
 
 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
 

+use Rapsys\AirBundle\Entity\Application;
+use Rapsys\AirBundle\Entity\Location;
+use Rapsys\AirBundle\Entity\Session;
+use Rapsys\AirBundle\Entity\Slot;
+use Rapsys\AirBundle\Repository;
+

 /**
  * SessionRepository
  */
 /**
  * SessionRepository
  */

-class SessionRepository extends EntityRepository {
+class SessionRepository extends Repository {

        ///Set glyphs
        //TODO: document utf-8 codes ?
        //TODO: use unknown == ? symbol by default ???
        ///Set glyphs
        //TODO: document utf-8 codes ?
        //TODO: use unknown == ? symbol by default ???


@@ -47,10 +55,9 @@ class SessionRepository extends EntityRepository {
         * Find session as array by id
         *
         * @param int $id The session id
         * Find session as array by id
         *
         * @param int $id The session id

-        * @param string $locale The locale

         * @return array The session data
         */
         * @return array The session data
         */

-       public function findOneByIdAsArray(int $id, string $locale): ?array {
+       public function findOneByIdAsArray(int $id): ?array {

                //Set the request
                $req =<<<SQL
 SELECT
                //Set the request
                $req =<<<SQL
 SELECT


@@ -107,16 +114,16 @@ SELECT
        GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled,
        GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
        GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
        GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled,
        GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
        GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym

-FROM RapsysAirBundle:Session AS s
-JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
-JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
-LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
-LEFT JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
-LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
-LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
-LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
-LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
-LEFT JOIN RapsysAirBundle:Dance AS sad ON (sad.id = sa.dance_id)
+FROM Rapsys\AirBundle\Entity\Session AS s
+JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
+JOIN Rapsys\AirBundle\Entity\Slot AS t ON (t.id = s.slot_id)
+LEFT JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id)
+LEFT JOIN Rapsys\AirBundle\Entity\Dance AS ad ON (ad.id = a.dance_id)
+LEFT JOIN Rapsys\AirBundle\Entity\User AS au ON (au.id = a.user_id)
+LEFT JOIN Rapsys\AirBundle\Entity\Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
+LEFT JOIN Rapsys\AirBundle\Entity\Application AS sa ON (sa.session_id = s.id)
+LEFT JOIN Rapsys\AirBundle\Entity\User AS sau ON (sau.id = sa.user_id)
+LEFT JOIN Rapsys\AirBundle\Entity\Dance AS sad ON (sad.id = sa.dance_id)

 WHERE s.id = :id
 GROUP BY s.id
 ORDER BY NULL
 WHERE s.id = :id
 GROUP BY s.id
 ORDER BY NULL


@@ -197,7 +204,6 @@ SQL;
                $result = $this->_em
                        ->createNativeQuery($req, $rsm)
                        ->setParameter('id', $id)
                $result = $this->_em
                        ->createNativeQuery($req, $rsm)
                        ->setParameter('id', $id)

-                       ->setParameter('locale', $locale)

                        ->getOneOrNullResult();
 
                //Without result
                        ->getOneOrNullResult();
 
                //Without result


@@ -369,9 +375,9 @@ SQL;
                        }
 
                        //With current locale
                        }
 
                        //With current locale

-                       if ($fallback === $locale) {
+                       if ($fallback === $this->locale) {

                                //Set current locale title
                                //Set current locale title

-                               $titles[$locale] = $this->translator->trans($this->languages[$locale]);
+                               $titles[$this->locale] = $this->translator->trans($this->languages[$this->locale]);

                        //Without current locale
                        } else {
                                //Iterate on other locales
                        //Without current locale
                        } else {
                                //Iterate on other locales


@@ -409,38 +415,13 @@ SQL;
         * Find sessions as calendar array by date period
         *
         * @param DatePeriod $period The date period
         * Find sessions as calendar array by date period
         *
         * @param DatePeriod $period The date period

-        * @param string $locale The locale

         * @param ?bool $granted The session is granted
         * @param ?float $latitude The latitude
         * @param ?float $longitude The longitude
         * @param ?int $userId The user id
         * @return array The session data
         */
         * @param ?bool $granted The session is granted
         * @param ?float $latitude The latitude
         * @param ?float $longitude The longitude
         * @param ?int $userId The user id
         * @return array The session data
         */

-
-       //TODO: calculer un titre de la page intelligent ?
-       //(utiliser la liste des villes par exemple ???)
-
-       //TODO: finir cette merde pour toutes les pages de listing !!!
-
-       //TODO: compute a max updated ???
-
-       //TODO: à priori si l'userId est fourni, qu'on veut pas juste les granted et que l'userId correspond pas à l'utilisateur à qui ça a été attribué et que la personne a demandé la session, on a envie de lui afficher le disputed ???
-       //(ce serait même plus un refused, voir le cas où c'est pas encore attribué, comment ça se passe ???)
-
-       //TODO: la gestion des sessions pas granted et contestées est à faire au niveau du contrôleur userView si l'user est connecté
-       //On peut utiliser le système des applications, l'id user dans application et l'id utilisateur connecté pour faire la détection
-       //Ça se résume à coller un $class[] = 'disputed' ou 'lost' ? sur la session ???
-
-       #TODO: mettre en grisé les sessions pas granted
-       #TODO: mettre en highlight (violet) les sessions de la location ou de l'user si fourni
-               //TODO: penser au cas où on est sur un utilisateur, on veut mettre
-                                               //TODO: faire la détection des séances perdues par l'utilisateur ???
-                                               //XXX: à priori ça va se jouer au niveau du contrôleur où on va setter ça
-                                                       /*} elseif (!empty($userId) && $session['au_id'] != $userId) {
-                                                       $class[] = 'disputed';*/
-
-
-       public function findAllByPeriodAsArray(\DatePeriod $period, string $locale, ?bool $granted = null, ?float $latitude = null, ?float $longitude = null, ?int $userId = null) {
+       public function findAllByPeriodAsCalendarArray(\DatePeriod $period, ?bool $granted = null, ?float $latitude = null, ?float $longitude = null, ?int $userId = null): array {

                //Init granted sql
                $grantSql = '';
 
                //Init granted sql
                $grantSql = '';
 


@@ -459,7 +440,7 @@ SQL;
                        //XXX: get every location between 0 and 15 km of latitude and longitude
                        $req = <<<SQL
 SELECT l.id
                        //XXX: get every location between 0 and 15 km of latitude and longitude
                        $req = <<<SQL
 SELECT l.id

-FROM RapsysAirBundle:Location AS l
+FROM Rapsys\AirBundle\Entity\Location AS l

 WHERE ACOS(SIN(RADIANS(:latitude))*SIN(RADIANS(l.latitude))+COS(RADIANS(:latitude))*COS(RADIANS(l.latitude))*COS(RADIANS(:longitude - l.longitude)))*40030.17/2/PI() BETWEEN 0 AND 15
 SQL;
 
 WHERE ACOS(SIN(RADIANS(:latitude))*SIN(RADIANS(l.latitude))+COS(RADIANS(:latitude))*COS(RADIANS(l.latitude))*COS(RADIANS(:longitude - l.longitude)))*40030.17/2/PI() BETWEEN 0 AND 15
 SQL;
 


@@ -497,15 +478,15 @@ SQL;
 SELECT l2.id
 FROM (
        SELECT l.id, l.latitude, l.longitude
 SELECT l2.id
 FROM (
        SELECT l.id, l.latitude, l.longitude

-       FROM RapsysAirBundle:Application AS a
-       JOIN RapsysAirBundle:Session AS s ON (s.id = a.session_id)
-       JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
+       FROM Rapsys\AirBundle\Entity\Application AS a
+       JOIN Rapsys\AirBundle\Entity\Session AS s ON (s.id = a.session_id)
+       JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)

        WHERE a.user_id = :id
        GROUP BY l.id
        ORDER BY NULL
        LIMIT 0, :limit
 ) AS a
        WHERE a.user_id = :id
        GROUP BY l.id
        ORDER BY NULL
        LIMIT 0, :limit
 ) AS a

-JOIN RapsysAirBundle:Location AS l2
+JOIN Rapsys\AirBundle\Entity\Location AS l2

 WHERE ACOS(SIN(RADIANS(a.latitude))*SIN(RADIANS(l2.latitude))+COS(RADIANS(a.latitude))*COS(RADIANS(l2.latitude))*COS(RADIANS(a.longitude - l2.longitude)))*40030.17/2/PI() BETWEEN 0 AND 15
 GROUP BY l2.id
 ORDER BY NULL
 WHERE ACOS(SIN(RADIANS(a.latitude))*SIN(RADIANS(l2.latitude))+COS(RADIANS(a.latitude))*COS(RADIANS(l2.latitude))*COS(RADIANS(a.longitude - l2.longitude)))*40030.17/2/PI() BETWEEN 0 AND 15
 GROUP BY l2.id
 ORDER BY NULL


@@ -554,6 +535,7 @@ SELECT
        ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
        s.location_id AS l_id,
        l.title AS l_title,
        ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
        s.location_id AS l_id,
        l.title AS l_title,

+       l.address AS l_address,

        l.zipcode AS l_zipcode,
        l.city AS l_city,
        l.latitude AS l_latitude,
        l.zipcode AS l_zipcode,
        l.city AS l_city,
        l.latitude AS l_latitude,


@@ -568,25 +550,26 @@ SELECT
        ad.type AS ad_type,
        a.user_id AS au_id,
        au.pseudonym AS au_pseudonym,
        ad.type AS ad_type,
        a.user_id AS au_id,
        au.pseudonym AS au_pseudonym,

-       p.rate AS p_rate,

        p.hat AS p_hat,
        p.hat AS p_hat,

+       p.rate AS p_rate,
+       p.short AS p_short,

        GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
        GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym,
        GROUP_CONCAT(sa.dance_id ORDER BY sa.user_id SEPARATOR "\\n") AS sad_id,
        GROUP_CONCAT(sad.name ORDER BY sa.user_id SEPARATOR "\\n") AS sad_name,
        GROUP_CONCAT(sad.type ORDER BY sa.user_id SEPARATOR "\\n") AS sad_type,
        GREATEST(COALESCE(s.updated, 0), COALESCE(l.updated, 0), COALESCE(p.updated, 0), COALESCE(MAX(sa.updated), 0), COALESCE(MAX(sau.updated), 0), COALESCE(MAX(sad.updated), 0)) AS modified
        GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
        GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym,
        GROUP_CONCAT(sa.dance_id ORDER BY sa.user_id SEPARATOR "\\n") AS sad_id,
        GROUP_CONCAT(sad.name ORDER BY sa.user_id SEPARATOR "\\n") AS sad_name,
        GROUP_CONCAT(sad.type ORDER BY sa.user_id SEPARATOR "\\n") AS sad_type,
        GREATEST(COALESCE(s.updated, 0), COALESCE(l.updated, 0), COALESCE(p.updated, 0), COALESCE(MAX(sa.updated), 0), COALESCE(MAX(sau.updated), 0), COALESCE(MAX(sad.updated), 0)) AS modified

-FROM RapsysAirBundle:Session AS s
-JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
-JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
-${grantSql}JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
-${grantSql}JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
-${grantSql}JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
-LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
-LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
-LEFT JOIN RapsysAirBundle:Dance AS sad ON (sad.id = sa.dance_id)
-LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
-WHERE s.date BETWEEN :begin AND :end${locationSql}
+FROM Rapsys\AirBundle\Entity\Session AS s
+JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
+JOIN Rapsys\AirBundle\Entity\Slot AS t ON (t.id = s.slot_id)
+{$grantSql}JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id)
+{$grantSql}JOIN Rapsys\AirBundle\Entity\Dance AS ad ON (ad.id = a.dance_id)
+{$grantSql}JOIN Rapsys\AirBundle\Entity\User AS au ON (au.id = a.user_id)
+LEFT JOIN Rapsys\AirBundle\Entity\Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
+LEFT JOIN Rapsys\AirBundle\Entity\Application AS sa ON (sa.session_id = s.id)
+LEFT JOIN Rapsys\AirBundle\Entity\Dance AS sad ON (sad.id = sa.dance_id)
+LEFT JOIN Rapsys\AirBundle\Entity\User AS sau ON (sau.id = sa.user_id)
+WHERE s.date BETWEEN :begin AND :end{$locationSql}

 GROUP BY s.id
 ORDER BY NULL
 SQL;
 GROUP BY s.id
 ORDER BY NULL
 SQL;


@@ -615,6 +598,7 @@ SQL;
                        ->addScalarResult('t_title', 't_title', 'string')
                        ->addScalarResult('l_id', 'l_id', 'integer')
                        ->addScalarResult('l_title', 'l_title', 'string')
                        ->addScalarResult('t_title', 't_title', 'string')
                        ->addScalarResult('l_id', 'l_id', 'integer')
                        ->addScalarResult('l_title', 'l_title', 'string')

+                       ->addScalarResult('l_address', 'l_address', 'string')

                        ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
                        ->addScalarResult('l_city', 'l_city', 'string')
                        ->addScalarResult('l_latitude', 'l_latitude', 'float')
                        ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
                        ->addScalarResult('l_city', 'l_city', 'string')
                        ->addScalarResult('l_latitude', 'l_latitude', 'float')


@@ -627,8 +611,9 @@ SQL;
                        ->addScalarResult('ad_type', 'ad_type', 'string')
                        ->addScalarResult('au_id', 'au_id', 'integer')
                        ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
                        ->addScalarResult('ad_type', 'ad_type', 'string')
                        ->addScalarResult('au_id', 'au_id', 'integer')
                        ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')

-                       ->addScalarResult('p_rate', 'p_rate', 'integer')

                        ->addScalarResult('p_hat', 'p_hat', 'boolean')
                        ->addScalarResult('p_hat', 'p_hat', 'boolean')

+                       ->addScalarResult('p_rate', 'p_rate', 'integer')
+                       ->addScalarResult('p_short', 'p_short', 'string')

                        //XXX: is a string because of \n separator
                        ->addScalarResult('sau_id', 'sau_id', 'string')
                        //XXX: is a string because of \n separator
                        //XXX: is a string because of \n separator
                        ->addScalarResult('sau_id', 'sau_id', 'string')
                        //XXX: is a string because of \n separator


@@ -645,8 +630,7 @@ SQL;
                $res = $this->_em
                        ->createNativeQuery($req, $rsm)
                        ->setParameter('begin', $period->getStartDate())
                $res = $this->_em
                        ->createNativeQuery($req, $rsm)
                        ->setParameter('begin', $period->getStartDate())

-                       ->setParameter('end', $period->getEndDate())
-                       ->setParameter('locale', $locale);
+                       ->setParameter('end', $period->getEndDate());

 
                //Add optional location ids
                if (!empty($locationIds)) {
 
                //Add optional location ids
                if (!empty($locationIds)) {


@@ -847,6 +831,7 @@ SQL;
                                                $rate = [
                                                        'glyph' => self::GLYPHS['Free'],
                                                        'rate' => null,
                                                $rate = [
                                                        'glyph' => self::GLYPHS['Free'],
                                                        'rate' => null,

+                                                       'short' => $session['p_short'],

                                                        'title' => $this->translator->trans('Free')
                                                ];
 
                                                        'title' => $this->translator->trans('Free')
                                                ];
 


@@ -861,7 +846,7 @@ SQL;
                                                                $rate['rate'] = $session['p_rate'];
 
                                                                //Set title
                                                                $rate['rate'] = $session['p_rate'];
 
                                                                //Set title

-                                                               $rate['title'] = $this->translator->trans('To the hat, ideally %rate% €', ['%rate%' => $session['p_rate']]);
+                                                               $rate['title'] = $this->translator->trans('%rate%€ to the hat', ['%rate%' => $session['p_rate']]);

                                                        //Without rate
                                                        } else {
                                                                //Set title
                                                        //Without rate
                                                        } else {
                                                                //Set title


@@ -920,6 +905,7 @@ SQL;
                                                'location' => [
                                                        'id' => $session['l_id'],
                                                        'title' => $this->translator->trans($session['l_title']),
                                                'location' => [
                                                        'id' => $session['l_id'],
                                                        'title' => $this->translator->trans($session['l_title']),

+                                                       'address' => $session['l_address'],

                                                        'latitude' => $session['l_latitude'],
                                                        'longitude' => $session['l_longitude'],
                                                        'indoor' => $session['l_indoor'],
                                                        'latitude' => $session['l_latitude'],
                                                        'longitude' => $session['l_longitude'],
                                                        'indoor' => $session['l_indoor'],


@@ -935,6 +921,7 @@ SQL;
                                                        'title' => $this->translator->trans($session['t_title'])
                                                ],
                                                'rate' => $rate,
                                                        'title' => $this->translator->trans($session['t_title'])
                                                ],
                                                'rate' => $rate,

+                                               'modified' => $session['modified'],

                                                'applications' => $applications
                                        ];
                                }
                                                'applications' => $applications
                                        ];
                                }


@@ -948,67 +935,50 @@ SQL;
                return $calendar;
        }
 
                return $calendar;
        }
 

-       /**
-        * Find session by location, slot and date
-        *
-        * @param $location The location
-        * @param $slot The slot
-        * @param $date The datetime
-        */
-       public function findOneByLocationSlotDate($location, $slot, $date) {
-               //Return sessions
-               return $this->getEntityManager()
-                       ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.slot = :slot AND s.date = :date)')
-                       ->setParameter('location', $location)
-                       ->setParameter('slot', $slot)
-                       ->setParameter('date', $date)
-                       ->getSingleResult();
-       }

 
        /**
 
        /**

-        * Find sessions by date period
+        * Find sessions by user id and synchronized date time

         *
         *

-        * @param $period The date period
+        * @param int $userId The user id
+        * @param DateTime $synchronized The synchronized datetime
+        * @return array The session data

         */
         */

-       public function findAllByDatePeriod($period) {
-               //Return sessions
-               return $this->getEntityManager()
-                       ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE s.date BETWEEN :begin AND :end')
-                       ->setParameter('begin', $period->getStartDate())
-                       ->setParameter('end', $period->getEndDate())
-                       ->getResult();
-       }
+       public function findAllByUserIdSynchronized(int $userId, \DateTime $synchronized): array {
+               //Set the request
+               $req = <<<SQL
+SELECT ud.dance_id
+FROM Rapsys\AirBundle\Entity\UserDance AS ud
+WHERE ud.user_id = :uid
+SQL;

 
 

-       /**
-        * Find sessions by location and date period
-        *
-        * @param $location The location
-        * @param $period The date period
-        */
-       public function findAllByLocationDatePeriod($location, $period) {
-               //Return sessions
-               return $this->getEntityManager()
-                       ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.date BETWEEN :begin AND :end)')
-                       ->setParameter('location', $location)
-                       ->setParameter('begin', $period->getStartDate())
-                       ->setParameter('end', $period->getEndDate())
-                       ->getResult();
-       }
+               //Replace bundle entity name by table name
+               $req = str_replace($this->tableKeys, $this->tableValues, $req);
+
+               //Get result set mapping instance
+               $rsm = new ResultSetMapping();
+
+               //Declare dance id field
+               $rsm->addScalarResult('dance_id', 'dance_id', 'integer');
+
+               //Set dance sql part
+               $danceSql = '';
+
+               //With user dance
+               if (
+                       $userDances = $this->_em
+                               ->createNativeQuery($req, $rsm)
+                               ->setParameter('uid', $userId)
+                               ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN)
+               ) {
+                       //Set dance sql part
+                       $danceSql = ' AND a.dance_id IN (:dids)';
+               }

 
 

-       /**
-        * Find one session by location and user id within last month
-        *
-        * @param $location The location id
-        * @param $user The user id
-        */
-       public function findOneWithinLastMonthByLocationUser($location, $user) {

                //Set the request
                //Set the request

-               //XXX: give the gooddelay to guest just in case
-               $req =<<<SQL
-SELECT s.id
-FROM RapsysAirBundle:Session s
-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))
-WHERE s.location_id = :lid AND s.date >= DATE_ADD(DATE_SUB(NOW(), INTERVAL 1 MONTH), INTERVAL :gooddelay DAY)
+               $req = <<<SQL
+SELECT us.user_id
+FROM Rapsys\AirBundle\Entity\UserSubscription AS us
+WHERE us.subscriber_id = :uid

 SQL;
 
                //Replace bundle entity name by table name
 SQL;
 
                //Replace bundle entity name by table name


@@ -1017,29 +987,24 @@ SQL;
                //Get result set mapping instance
                $rsm = new ResultSetMapping();
 
                //Get result set mapping instance
                $rsm = new ResultSetMapping();
 

-               //Declare all fields
-               //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
-               $rsm->addScalarResult('id', 'id', 'integer')
-                       ->addIndexByScalar('id');
-
-               //Return result
-               return $this->_em
-                       ->createNativeQuery($req, $rsm)
-                       ->setParameter('lid', $location)
-                       ->setParameter('uid', $user)
-                       ->setParameter('gooddelay', self::SENIOR_DELAY)
-                       ->getOneOrNullResult();
-       }
+               //Declare user id field
+               $rsm->addScalarResult('user_id', 'user_id', 'integer');
+
+               //Set subscription sql part
+               $subscriptionSql = '';
+
+               //With user subscription
+               if (
+                       $userSubscriptions = $this->_em
+                               ->createNativeQuery($req, $rsm)
+                               ->setParameter('uid', $userId)
+                               ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN)
+               ) {
+                       //Set subscription sql part
+                       $subscriptionSql = ' AND a.user_id IN (:uids)';
+               }

 
 

-       /**
-        * Fetch sessions by date period
-        *
-        * @param $period The date period
-        * @param $locale The locale
-        */
-       public function fetchAllByDatePeriod($period, $locale = null) {

                //Set the request
                //Set the request

-               //TODO: exclude opera and others ?

                $req = <<<SQL
 SELECT
        s.id,
                $req = <<<SQL
 SELECT
        s.id,


@@ -1073,27 +1038,26 @@ SELECT
        p.donate AS p_donate,
        p.link AS p_link,
        p.profile AS p_profile
        p.donate AS p_donate,
        p.link AS p_link,
        p.profile AS p_profile

-FROM RapsysAirBundle:Session AS s
-JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
-JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
-JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
-JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
-LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
-WHERE s.date BETWEEN :begin AND :end
-ORDER BY NULL
+FROM Rapsys\AirBundle\Entity\Session AS s
+JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
+JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id{$danceSql}{$subscriptionSql})
+JOIN Rapsys\AirBundle\Entity\Dance AS ad ON (ad.id = a.dance_id)
+JOIN Rapsys\AirBundle\Entity\User AS au ON (au.id = a.user_id)
+LEFT JOIN Rapsys\AirBundle\Entity\Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
+WHERE GREATEST(s.created, s.updated, a.created, a.updated, ADDTIME(ADDTIME(s.date, s.begin), s.length)) >= :synchronized

 SQL;
 
                //Replace bundle entity name by table name
                $req = str_replace($this->tableKeys, $this->tableValues, $req);
 
                //Get result set mapping instance
 SQL;
 
                //Replace bundle entity name by table name
                $req = str_replace($this->tableKeys, $this->tableValues, $req);
 
                //Get result set mapping instance

-               //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php

                $rsm = new ResultSetMapping();
 
                //Declare all fields
                //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
                //addScalarResult($sqlColName, $resColName, $type = 'string');
                $rsm = new ResultSetMapping();
 
                //Declare all fields
                //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
                //addScalarResult($sqlColName, $resColName, $type = 'string');

-               $rsm->addScalarResult('id', 'id', 'integer')
+               $rsm
+                       ->addScalarResult('id', 'id', 'integer')

                        ->addScalarResult('date', 'date', 'date')
                        ->addScalarResult('locked', 'locked', 'datetime')
                        ->addScalarResult('updated', 'updated', 'datetime')
                        ->addScalarResult('date', 'date', 'date')
                        ->addScalarResult('locked', 'locked', 'datetime')
                        ->addScalarResult('updated', 'updated', 'datetime')


@@ -1128,100 +1092,85 @@ SQL;
                        ->addScalarResult('p_profile', 'p_profile', 'string')
                        ->addIndexByScalar('id');
 
                        ->addScalarResult('p_profile', 'p_profile', 'string')
                        ->addIndexByScalar('id');
 

-               //Fetch result
-               $res = $this->_em
+               //Return sessions
+               //TODO: XXX: finish here
+               return $this->_em

                        ->createNativeQuery($req, $rsm)
                        ->createNativeQuery($req, $rsm)

-                       ->setParameter('begin', $period->getStartDate())
-                       ->setParameter('end', $period->getEndDate())
-                       ->setParameter('locale', $locale);
+                       ->setParameter('dids', $userDances)
+                       ->setParameter('uids', $userSubscriptions)
+                       ->setParameter('synchronized', $synchronized)
+                       ->getArrayResult();
+       }

 
 

-               //Return result
-               return $res->getResult();
+       /**
+        * Find session by location, slot and date
+        *
+        * @param Location $location The location
+        * @param Slot $slot The slot
+        * @param DateTime $date The datetime
+        * @return ?Session The found session
+        */
+       public function findOneByLocationSlotDate(Location $location, Slot $slot, \DateTime $date): ?Session {
+               //Return sessions
+               return $this->getEntityManager()
+                       ->createQuery('SELECT s FROM Rapsys\AirBundle\Entity\Session s WHERE (s.location = :location AND s.slot = :slot AND s.date = :date)')
+                       ->setParameter('location', $location)
+                       ->setParameter('slot', $slot)
+                       ->setParameter('date', $date)
+                       ->getSingleResult();

        }
 
        /**
        }
 
        /**

-        * Fetch session by id
+        * Fetch sessions by date period

         *
         *

-        * @todo drop fetchOneById when migration is complete
-        * @todo FIX the updated computation, we can drop the group_concat for a MAX()
-        * @XXX DO NOT ADD a.updated as we already compute that with applications !!!
+        * @XXX: used in calendar command

         *
         *

-        * @param $id The session id
-        * @param $locale The locale
-        * @return array The session data
+        * @param DatePeriod $period The date period
+        * @return array The session array

         */
         */

-       public function fetchOneById($id, $locale = null) {
+       public function fetchAllByDatePeriod(\DatePeriod $period): array {

                //Set the request
                //Set the request

-               //TODO: compute scores ?
-               //TODO: compute delivery date ? (J-3/J-4 ?)
-               $req =<<<SQL
+               //TODO: exclude opera and others ?
+               $req = <<<SQL

 SELECT
        s.id,
        s.date,
 SELECT
        s.id,
        s.date,

-       s.begin,
-       ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
-       s.length,
-       ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
-       s.rainfall,
-       s.rainrisk,
-       s.realfeel,
-       s.realfeelmin,
-       s.realfeelmax,
-       s.temperature,
-       s.temperaturemin,
-       s.temperaturemax,

        s.locked,
        s.locked,

-       s.created,

        s.updated,
        s.updated,

+       ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
+       ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,

        s.location_id AS l_id,
        s.location_id AS l_id,

-       l.title AS l_title,
-       l.description AS l_description,

        l.address AS l_address,
        l.zipcode AS l_zipcode,
        l.city AS l_city,
        l.address AS l_address,
        l.zipcode AS l_zipcode,
        l.city AS l_city,

+       l.title AS l_title,
+       l.description AS l_description,

        l.latitude AS l_latitude,
        l.longitude AS l_longitude,
        l.latitude AS l_latitude,
        l.longitude AS l_longitude,

-       l.indoor AS l_indoor,
-       l.updated AS l_updated,
-       s.slot_id AS t_id,
-       t.title AS t_title,
-       t.updated AS t_updated,

        s.application_id AS a_id,
        a.canceled AS a_canceled,
        s.application_id AS a_id,
        a.canceled AS a_canceled,

-       a.dance_id AS ad_id,

        ad.name AS ad_name,
        ad.type AS ad_type,
        ad.name AS ad_name,
        ad.type AS ad_type,

-       ad.updated AS ad_updated,

        a.user_id AS au_id,
        a.user_id AS au_id,

+       au.forename AS au_forename,

        au.pseudonym AS au_pseudonym,
        p.id AS p_id,
        p.description AS p_description,
        p.class AS p_class,
        au.pseudonym AS au_pseudonym,
        p.id AS p_id,
        p.description AS p_description,
        p.class AS p_class,

+       p.short AS p_short,
+       p.hat AS p_hat,
+       p.rate AS p_rate,

        p.contact AS p_contact,
        p.donate AS p_donate,
        p.link AS p_link,
        p.contact AS p_contact,
        p.donate AS p_donate,
        p.link AS p_link,

-       p.profile AS p_profile,
-       p.rate AS p_rate,
-       p.hat AS p_hat,
-       p.updated AS p_updated,
-       GROUP_CONCAT(sa.id ORDER BY sa.user_id SEPARATOR "\\n") AS sa_id,
-       GROUP_CONCAT(IFNULL(sa.score, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_score,
-       GROUP_CONCAT(sa.created ORDER BY sa.user_id SEPARATOR "\\n") AS sa_created,
-       GROUP_CONCAT(sa.updated ORDER BY sa.user_id SEPARATOR "\\n") AS sa_updated,
-       GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled,
-       GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
-       GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
-FROM RapsysAirBundle:Session AS s
-JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
-JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
-LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
-LEFT JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
-LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
-LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
-LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
-LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
-WHERE s.id = :id
-GROUP BY s.id
+       p.profile AS p_profile
+FROM Rapsys\AirBundle\Entity\Session AS s
+JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
+JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id)
+JOIN Rapsys\AirBundle\Entity\Dance AS ad ON (ad.id = a.dance_id)
+JOIN Rapsys\AirBundle\Entity\User AS au ON (au.id = a.user_id)
+LEFT JOIN Rapsys\AirBundle\Entity\Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
+WHERE s.date BETWEEN :begin AND :end

 ORDER BY NULL
 SQL;
 
 ORDER BY NULL
 SQL;
 


@@ -1234,705 +1183,94 @@ SQL;
 
                //Declare all fields
                //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
 
                //Declare all fields
                //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php

+               //addScalarResult($sqlColName, $resColName, $type = 'string');

                $rsm->addScalarResult('id', 'id', 'integer')
                        ->addScalarResult('date', 'date', 'date')
                $rsm->addScalarResult('id', 'id', 'integer')
                        ->addScalarResult('date', 'date', 'date')

-                       ->addScalarResult('begin', 'begin', 'time')
-                       ->addScalarResult('start', 'start', 'datetime')
-                       ->addScalarResult('length', 'length', 'time')
-                       ->addScalarResult('stop', 'stop', 'datetime')
-                       ->addScalarResult('rainfall', 'rainfall', 'float')
-                       ->addScalarResult('rainrisk', 'rainrisk', 'float')
-                       ->addScalarResult('realfeel', 'realfeel', 'float')
-                       ->addScalarResult('realfeelmin', 'realfeelmin', 'float')
-                       ->addScalarResult('realfeelmax', 'realfeelmax', 'float')
-                       ->addScalarResult('temperature', 'temperature', 'float')
-                       ->addScalarResult('temperaturemin', 'temperaturemin', 'float')
-                       ->addScalarResult('temperaturemax', 'temperaturemax', 'float')

                        ->addScalarResult('locked', 'locked', 'datetime')
                        ->addScalarResult('locked', 'locked', 'datetime')

-                       ->addScalarResult('created', 'created', 'datetime')

                        ->addScalarResult('updated', 'updated', 'datetime')
                        ->addScalarResult('updated', 'updated', 'datetime')

+                       ->addScalarResult('start', 'start', 'datetime')
+                       ->addScalarResult('stop', 'stop', 'datetime')

                        ->addScalarResult('l_id', 'l_id', 'integer')
                        ->addScalarResult('l_id', 'l_id', 'integer')

-                       ->addScalarResult('l_title', 'l_title', 'string')
-                       ->addScalarResult('l_description', 'l_description', 'string')

                        ->addScalarResult('l_address', 'l_address', 'string')
                        ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
                        ->addScalarResult('l_city', 'l_city', 'string')
                        ->addScalarResult('l_latitude', 'l_latitude', 'float')
                        ->addScalarResult('l_longitude', 'l_longitude', 'float')
                        ->addScalarResult('l_address', 'l_address', 'string')
                        ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
                        ->addScalarResult('l_city', 'l_city', 'string')
                        ->addScalarResult('l_latitude', 'l_latitude', 'float')
                        ->addScalarResult('l_longitude', 'l_longitude', 'float')

-                       ->addScalarResult('l_indoor', 'l_indoor', 'boolean')
-                       ->addScalarResult('l_updated', 'l_updated', 'datetime')
+                       ->addScalarResult('l_title', 'l_title', 'string')
+                       ->addScalarResult('l_description', 'l_description', 'string')

                        ->addScalarResult('t_id', 't_id', 'integer')
                        ->addScalarResult('t_title', 't_title', 'string')
                        ->addScalarResult('t_id', 't_id', 'integer')
                        ->addScalarResult('t_title', 't_title', 'string')

-                       ->addScalarResult('t_updated', 't_updated', 'datetime')

                        ->addScalarResult('a_id', 'a_id', 'integer')
                        ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
                        ->addScalarResult('a_id', 'a_id', 'integer')
                        ->addScalarResult('a_canceled', 'a_canceled', 'datetime')

-                       ->addScalarResult('ad_id', 'ad_id', 'integer')

                        ->addScalarResult('ad_name', 'ad_name', 'string')
                        ->addScalarResult('ad_type', 'ad_type', 'string')
                        ->addScalarResult('ad_name', 'ad_name', 'string')
                        ->addScalarResult('ad_type', 'ad_type', 'string')

-                       ->addScalarResult('ad_updated', 'ad_updated', 'datetime')

                        ->addScalarResult('au_id', 'au_id', 'integer')
                        ->addScalarResult('au_id', 'au_id', 'integer')

+                       ->addScalarResult('au_forename', 'au_forename', 'string')

                        ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
                        ->addScalarResult('p_id', 'p_id', 'integer')
                        ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
                        ->addScalarResult('p_id', 'p_id', 'integer')

-                       ->addScalarResult('p_description', 'p_description', 'text')
-                       ->addScalarResult('p_class', 'p_class', 'text')
-                       ->addScalarResult('p_contact', 'p_contact', 'text')
-                       ->addScalarResult('p_donate', 'p_donate', 'text')
-                       ->addScalarResult('p_link', 'p_link', 'text')
-                       ->addScalarResult('p_profile', 'p_profile', 'text')
+                       ->addScalarResult('p_description', 'p_description', 'string')
+                       ->addScalarResult('p_class', 'p_class', 'string')
+                       ->addScalarResult('p_short', 'p_short', 'string')
+                       ->addScalarResult('p_hat', 'p_hat', 'integer')

                        ->addScalarResult('p_rate', 'p_rate', 'integer')
                        ->addScalarResult('p_rate', 'p_rate', 'integer')

-                       ->addScalarResult('p_hat', 'p_hat', 'boolean')
-                       ->addScalarResult('p_updated', 'p_updated', 'datetime')
-                       //XXX: is a string because of \n separator
-                       ->addScalarResult('sa_id', 'sa_id', 'string')
-                       //XXX: is a string because of \n separator
-                       ->addScalarResult('sa_score', 'sa_score', 'string')
-                       //XXX: is a string because of \n separator
-                       ->addScalarResult('sa_created', 'sa_created', 'string')
-                       //XXX: is a string because of \n separator
-                       ->addScalarResult('sa_updated', 'sa_updated', 'string')
-                       //XXX: is a string because of \n separator
-                       ->addScalarResult('sa_canceled', 'sa_canceled', 'string')
-                       //XXX: is a string because of \n separator
-                       ->addScalarResult('sau_id', 'sau_id', 'string')
-                       //XXX: is a string because of \n separator
-                       ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
+                       ->addScalarResult('p_contact', 'p_contact', 'string')
+                       ->addScalarResult('p_donate', 'p_donate', 'string')
+                       ->addScalarResult('p_link', 'p_link', 'string')
+                       ->addScalarResult('p_profile', 'p_profile', 'string')

                        ->addIndexByScalar('id');
 
                        ->addIndexByScalar('id');
 

-               //Set result
-               return $this->_em
+               //Fetch result
+               $res = $this->_em

                        ->createNativeQuery($req, $rsm)
                        ->createNativeQuery($req, $rsm)

-                       ->setParameter('id', $id)
-                       ->setParameter('locale', $locale)
-                       ->getOneOrNullResult();
+                       ->setParameter('begin', $period->getStartDate())
+                       ->setParameter('end', $period->getEndDate());
+
+               //Return result
+               return $res->getResult();

        }
 
        /**
        }
 
        /**

-        * Fetch sessions calendar with translated location by date period
+        * Find all session pending hourly weather

         *
         *

-        * @param $period The date period
-        * @param $locationId The location id
-        * @param $sessionId The session id
-        * @param $granted The session is granted
+        * @return array The sessions to update

         */
         */

-       public function fetchCalendarByDatePeriod($period, $locationId = null, $sessionId = null, $granted = false, $locale = null) {
-               //Init granted sql
-               $grantSql = '';
-
-               //When granted is set
-               if (empty($granted)) {
-                       //Set application and user as optional
-                       $grantSql = 'LEFT ';
-               }
+       public function findAllPendingHourlyWeather(): array {
+               //Select all sessions starting and stopping in the next 3 days
+               //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)
+               $req = <<<SQL
+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
+FROM Rapsys\AirBundle\Entity\Session AS s
+JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
+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))
+SQL;

 
 

-               //Init location sql
-               $locationSql = '';
+               //Replace bundle entity name by table name
+               $req = str_replace($this->tableKeys, $this->tableValues, $req);

 
 

-               //When location id is set
-               if (!empty($locationId)) {
-                       //Add location id clause
-                       $locationSql = "\n\t".'AND s.location_id = :lid';
-               }
+               //Get result set mapping instance
+               $rsm = new ResultSetMapping();

 
 

-               //Set the request
-               $req = <<<SQL
-
-SELECT
-       s.id,
-       s.date,
-       s.rainrisk,
-       s.rainfall,
-       s.realfeel,
-       s.temperature,
-       s.locked,
-       ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
-       ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
-       s.location_id AS l_id,
-       l.title AS l_title,
-       s.slot_id AS t_id,
-       t.title AS t_title,
-       s.application_id AS a_id,
-       a.canceled AS a_canceled,
-       ad.name AS ad_name,
-       ad.type AS ad_type,
-       a.user_id AS au_id,
-       au.pseudonym AS au_pseudonym,
-       p.rate AS p_rate,
-       p.hat AS p_hat,
-       GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
-       GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
-FROM RapsysAirBundle:Session AS s
-JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
-JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
-${grantSql}JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
-${grantSql}JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
-${grantSql}JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
-LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
-LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
-LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
-WHERE s.date BETWEEN :begin AND :end${locationSql}
-GROUP BY s.id
-ORDER BY NULL
-SQL;
-
-               //Replace bundle entity name by table name
-               $req = str_replace($this->tableKeys, $this->tableValues, $req);
-
-               //Get result set mapping instance
-               //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
-               $rsm = new ResultSetMapping();
-
-               //Declare all fields
-               //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
-               //addScalarResult($sqlColName, $resColName, $type = 'string');
-               $rsm->addScalarResult('id', 'id', 'integer')
-                       ->addScalarResult('date', 'date', 'date')
-                       ->addScalarResult('rainrisk', 'rainrisk', 'float')
-                       ->addScalarResult('rainfall', 'rainfall', 'float')
-                       ->addScalarResult('realfeel', 'realfeel', 'float')
-                       ->addScalarResult('temperature', 'temperature', 'float')
-                       ->addScalarResult('locked', 'locked', 'datetime')
-                       ->addScalarResult('start', 'start', 'datetime')
-                       ->addScalarResult('stop', 'stop', 'datetime')
-                       ->addScalarResult('t_id', 't_id', 'integer')
-                       ->addScalarResult('t_title', 't_title', 'string')
-                       ->addScalarResult('l_id', 'l_id', 'integer')
-                       ->addScalarResult('l_title', 'l_title', 'string')
-                       ->addScalarResult('a_id', 'a_id', 'integer')
-                       ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
-                       ->addScalarResult('ad_name', 'ad_name', 'string')
-                       ->addScalarResult('ad_type', 'ad_type', 'string')
-                       ->addScalarResult('au_id', 'au_id', 'integer')
-                       ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
-                       ->addScalarResult('p_rate', 'p_rate', 'integer')
-                       ->addScalarResult('p_hat', 'p_hat', 'boolean')
-                       //XXX: is a string because of \n separator
-                       ->addScalarResult('sau_id', 'sau_id', 'string')
-                       //XXX: is a string because of \n separator
-                       ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
-                       ->addIndexByScalar('id');
-
-               //Fetch result
-               $res = $this->_em
-                       ->createNativeQuery($req, $rsm)
-                       ->setParameter('begin', $period->getStartDate())
-                       ->setParameter('end', $period->getEndDate())
-                       ->setParameter('locale', $locale);
-
-               //Add optional location id
-               if (!empty($locationId)) {
-                       $res->setParameter('lid', $locationId);
-               }
-
-               //Get result
-               $res = $res->getResult();
-
-               //Init calendar
-               $calendar = [];
-
-               //Init month
-               $month = null;
-
-               //Iterate on each day
-               foreach($period as $date) {
-                       //Init day in calendar
-                       $calendar[$Ymd = $date->format('Ymd')] = [
-                               'title' => $this->translator->trans($date->format('l')).' '.$date->format('d'),
-                               'class' => [],
-                               'sessions' => []
-                       ];
-
-                       //Detect month change
-                       if ($month != $date->format('m')) {
-                               $month = $date->format('m');
-                               //Append month for first day of month
-                               //XXX: except if today to avoid double add
-                               if ($date->format('U') != strtotime('today')) {
-                                       $calendar[$Ymd]['title'] .= '/'.$month;
-                               }
-                       }
-                       //Deal with today
-                       if ($date->format('U') == ($today = strtotime('today'))) {
-                               $calendar[$Ymd]['title'] .= '/'.$month;
-                               $calendar[$Ymd]['current'] = true;
-                               $calendar[$Ymd]['class'][] = 'current';
-                       }
-                       //Disable passed days
-                       if ($date->format('U') < $today) {
-                               $calendar[$Ymd]['disabled'] = true;
-                               $calendar[$Ymd]['class'][] = 'disabled';
-                       }
-                       //Set next month days
-                       if ($date->format('m') > date('m')) {
-                               $calendar[$Ymd]['next'] = true;
-                               #$calendar[$Ymd]['class'][] = 'next';
-                       }
-
-                       //Detect sunday
-                       if ($date->format('w') == 0) {
-                               $calendar[$Ymd]['class'][] = 'sunday';
-                       }
-
-                       //Iterate on each session to find the one of the day
-                       foreach($res as $session) {
-                               if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
-                                       //Count number of application
-                                       $count = count(explode("\n", $session['sau_id']));
-
-                                       //Compute classes
-                                       $class = [];
-                                       if (!empty($session['a_id'])) {
-                                               $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
-                                               if (!empty($session['a_canceled'])) {
-                                                       $class[] = 'canceled';
-                                               } else {
-                                                       $class[] = 'granted';
-                                               }
-                                       } elseif ($count > 1) {
-                                               $class[] = 'disputed';
-                                       } elseif (!empty($session['locked'])) {
-                                               $class[] = 'locked';
-                                       } else {
-                                               $class[] = 'pending';
-                                       }
-
-                                       if ($sessionId == $session['id']) {
-                                               $class[] = 'highlight';
-                                       }
-
-                                       //Set temperature
-                                       //XXX: realfeel may be null, temperature should not
-                                       $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
-
-                                       //Compute weather
-                                       //XXX: rainfall may be null
-                                       if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
-                                               $weather = self::GLYPHS['Stormy'];
-                                       } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
-                                               $weather = self::GLYPHS['Rainy'];
-                                       } elseif ($temperature > 24) {
-                                               $weather = self::GLYPHS['Cleary'];
-                                       } elseif ($temperature > 17) {
-                                               $weather = self::GLYPHS['Sunny'];
-                                       } elseif ($temperature > 10) {
-                                               $weather = self::GLYPHS['Cloudy'];
-                                       } elseif ($temperature !== null) {
-                                               $weather = self::GLYPHS['Winty'];
-                                       } else {
-                                               $weather = null;
-                                       }
-
-                                       //Init weathertitle
-                                       $weathertitle = [];
-
-                                       //Check if realfeel is available
-                                       if ($session['realfeel'] !== null) {
-                                               $weathertitle[] = $session['realfeel'].'°R';
-                                       }
-
-                                       //Check if temperature is available
-                                       if ($session['temperature'] !== null) {
-                                               $weathertitle[] = $session['temperature'].'°C';
-                                       }
-
-                                       //Check if rainrisk is available
-                                       if ($session['rainrisk'] !== null) {
-                                               $weathertitle[] = ($session['rainrisk']*100).'%';
-                                       }
-
-                                       //Check if rainfall is available
-                                       if ($session['rainfall'] !== null) {
-                                               $weathertitle[] = $session['rainfall'].'mm';
-                                       }
-
-                                       //Set applications
-                                       $applications = [
-                                               0 => $this->translator->trans($session['t_title']).' '.$this->translator->trans('at '.$session['l_title']).$this->translator->trans(':')
-                                       ];
-
-                                       //Fetch pseudonyms from session applications
-                                       $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
-
-                                       //Set dance
-                                       $dance = null;
-
-                                       //Set pseudonym
-                                       $pseudonym = null;
-
-                                       //Check that session is not granted
-                                       if (empty($session['a_id'])) {
-                                               //With location id and unique application
-                                               if ($count == 1) {
-                                                       //Set unique application pseudonym
-                                                       $pseudonym = $session['sau_pseudonym'];
-                                               }
-                                       //Session is granted
-                                       } else {
-                                               //Replace granted application
-                                               $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
-
-                                               //Set dance
-                                               $dance = $this->translator->trans($session['ad_name'].' '.lcfirst($session['ad_type']));
-
-                                               //Set pseudonym
-                                               $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
-                                       }
-
-                                       //Add the session
-                                       $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%05d', $session['id'])] = [
-                                               'id' => $session['id'],
-                                               'start' => $session['start'],
-                                               'stop' => $session['stop'],
-                                               'location' => $this->translator->trans($session['l_title']),
-                                               'dance' => $dance,
-                                               'pseudonym' => $pseudonym,
-                                               'class' => $class,
-                                               'slot' => self::GLYPHS[$session['t_title']],
-                                               'slottitle' => $this->translator->trans($session['t_title']),
-                                               'weather' => $weather,
-                                               'weathertitle' => implode(' ', $weathertitle),
-                                               'applications' => $applications,
-                                               'rate' => $session['p_rate'],
-                                               'hat' => $session['p_hat']
-                                       ];
-                               }
-                       }
-
-                       //Sort sessions
-                       ksort($calendar[$Ymd]['sessions']);
-               }
-
-               //Send result
-               return $calendar;
-       }
-
-       /**
-        * Fetch sessions calendar with translated location by date period and user
-        *
-        * @param $period The date period
-        * @param $userId The user id
-        * @param $sessionId The session id
-        */
-       public function fetchUserCalendarByDatePeriod($period, $userId = null, $sessionId = null, $locale = null) {
-               //Init user sql
-               $userJoinSql = $userWhereSql = '';
-
-               //When user id is set
-               if (!empty($userId)) {
-                       //Add user join
-                       $userJoinSql = 'JOIN RapsysAirBundle:Application AS sua ON (sua.session_id = s.id)'."\n";
-                       //Add user id clause
-                       $userWhereSql = "\n\t".'AND sua.user_id = :uid';
-               }
-
-               //Set the request
-               //TODO: change as_u_* in sau_*, a_u_* in au_*, etc, see request up
-               $req = <<<SQL
-SELECT
-       s.id,
-       s.date,
-       s.rainrisk,
-       s.rainfall,
-       s.realfeel,
-       s.temperature,
-       s.locked,
-       ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
-       ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
-       s.location_id AS l_id,
-       l.title AS l_title,
-       s.slot_id AS t_id,
-       t.title AS t_title,
-       s.application_id AS a_id,
-       ad.name AS ad_name,
-       ad.type AS ad_type,
-       a.user_id AS au_id,
-       au.pseudonym AS au_pseudonym,
-       p.rate AS p_rate,
-       p.hat AS p_hat,
-       GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
-       GROUP_CONCAT(CONCAT("- ", sau.pseudonym) ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
-FROM RapsysAirBundle:Session AS s
-JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
-JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
-${userJoinSql}LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
-LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
-LEFT JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
-LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
-LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
-LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
-WHERE s.date BETWEEN :begin AND :end${userWhereSql}
-GROUP BY s.id
-ORDER BY NULL
-SQL;
-
-               //Replace bundle entity name by table name
-               $req = str_replace($this->tableKeys, $this->tableValues, $req);
-
-               //Get result set mapping instance
-               //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
-               $rsm = new ResultSetMapping();
-
-               //Declare all fields
-               //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
-               //addScalarResult($sqlColName, $resColName, $type = 'string');
-               $rsm->addScalarResult('id', 'id', 'integer')
-                       ->addScalarResult('date', 'date', 'date')
-                       ->addScalarResult('rainrisk', 'rainrisk', 'float')
-                       ->addScalarResult('rainfall', 'rainfall', 'float')
-                       ->addScalarResult('realfeel', 'realfeel', 'float')
-                       ->addScalarResult('temperature', 'temperature', 'float')
-                       ->addScalarResult('locked', 'locked', 'datetime')
-                       ->addScalarResult('start', 'start', 'datetime')
-                       ->addScalarResult('stop', 'stop', 'datetime')
-                       ->addScalarResult('t_id', 't_id', 'integer')
-                       ->addScalarResult('t_title', 't_title', 'string')
-                       ->addScalarResult('l_id', 'l_id', 'integer')
-                       ->addScalarResult('l_title', 'l_title', 'string')
-                       ->addScalarResult('a_id', 'a_id', 'integer')
-                       ->addScalarResult('ad_name', 'ad_name', 'string')
-                       ->addScalarResult('ad_type', 'ad_type', 'string')
-                       ->addScalarResult('au_id', 'au_id', 'integer')
-                       ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
-                       ->addScalarResult('p_rate', 'p_rate', 'integer')
-                       ->addScalarResult('p_hat', 'p_hat', 'boolean')
-                       //XXX: is a string because of \n separator
-                       ->addScalarResult('sau_id', 'sau_id', 'string')
-                       //XXX: is a string because of \n separator
-                       ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
-                       ->addIndexByScalar('id');
-
-               //Fetch result
-               $res = $this->_em
-                       ->createNativeQuery($req, $rsm)
-                       ->setParameter('begin', $period->getStartDate())
-                       ->setParameter('end', $period->getEndDate())
-                       ->setParameter('uid', $userId)
-                       ->setParameter('locale', $locale)
-                       ->getResult();
-
-               //Init calendar
-               $calendar = [];
-
-               //Init month
-               $month = null;
-
-               //Iterate on each day
-               foreach($period as $date) {
-                       //Init day in calendar
-                       $calendar[$Ymd = $date->format('Ymd')] = [
-                               'title' => $this->translator->trans($date->format('l')).' '.$date->format('d'),
-                               'class' => [],
-                               'sessions' => []
-                       ];
-
-                       //Detect month change
-                       if ($month != $date->format('m')) {
-                               $month = $date->format('m');
-                               //Append month for first day of month
-                               //XXX: except if today to avoid double add
-                               if ($date->format('U') != strtotime('today')) {
-                                       $calendar[$Ymd]['title'] .= '/'.$month;
-                               }
-                       }
-                       //Deal with today
-                       if ($date->format('U') == ($today = strtotime('today'))) {
-                               $calendar[$Ymd]['title'] .= '/'.$month;
-                               $calendar[$Ymd]['current'] = true;
-                               $calendar[$Ymd]['class'][] = 'current';
-                       }
-                       //Disable passed days
-                       if ($date->format('U') < $today) {
-                               $calendar[$Ymd]['disabled'] = true;
-                               $calendar[$Ymd]['class'][] = 'disabled';
-                       }
-                       //Set next month days
-                       if ($date->format('m') > date('m')) {
-                               $calendar[$Ymd]['next'] = true;
-                               #$calendar[$Ymd]['class'][] = 'next';
-                       }
-
-                       //Detect sunday
-                       if ($date->format('w') == 0) {
-                               $calendar[$Ymd]['class'][] = 'sunday';
-                       }
-
-                       //Iterate on each session to find the one of the day
-                       foreach($res as $session) {
-                               if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
-                                       //Count number of application
-                                       $count = count(explode("\n", $session['sau_id']));
-
-                                       //Compute classes
-                                       $class = [];
-                                       if (!empty($session['a_id'])) {
-                                               $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
-                                               if ($session['au_id'] == $userId) {
-                                                       $class[] = 'granted';
-                                               } else {
-                                                       $class[] = 'disputed';
-                                               }
-                                       } elseif ($count > 1) {
-                                               $class[] = 'disputed';
-                                       } elseif (!empty($session['locked'])) {
-                                               $class[] = 'locked';
-                                       } else {
-                                               $class[] = 'pending';
-                                       }
-
-                                       if ($sessionId == $session['id']) {
-                                               $class[] = 'highlight';
-                                       }
-
-                                       //Set temperature
-                                       //XXX: realfeel may be null, temperature should not
-                                       $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
-
-                                       //Compute weather
-                                       //XXX: rainfall may be null
-                                       if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
-                                               $weather = self::GLYPHS['Stormy'];
-                                       } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
-                                               $weather = self::GLYPHS['Rainy'];
-                                       } elseif ($temperature > 24) {
-                                               $weather = self::GLYPHS['Cleary'];
-                                       } elseif ($temperature > 17) {
-                                               $weather = self::GLYPHS['Sunny'];
-                                       } elseif ($temperature > 10) {
-                                               $weather = self::GLYPHS['Cloudy'];
-                                       } elseif ($temperature !== null) {
-                                               $weather = self::GLYPHS['Winty'];
-                                       } else {
-                                               $weather = null;
-                                       }
-
-                                       //Init weathertitle
-                                       $weathertitle = [];
-
-                                       //Check if realfeel is available
-                                       if ($session['realfeel'] !== null) {
-                                               $weathertitle[] = $session['realfeel'].'°R';
-                                       }
-
-                                       //Check if temperature is available
-                                       if ($session['temperature'] !== null) {
-                                               $weathertitle[] = $session['temperature'].'°C';
-                                       }
-
-                                       //Check if rainrisk is available
-                                       if ($session['rainrisk'] !== null) {
-                                               $weathertitle[] = ($session['rainrisk']*100).'%';
-                                       }
-
-                                       //Check if rainfall is available
-                                       if ($session['rainfall'] !== null) {
-                                               $weathertitle[] = $session['rainfall'].'mm';
-                                       }
-
-                                       //Set applications
-                                       $applications = [
-                                               0 => $this->translator->trans($session['t_title']).' '.$this->translator->trans('at '.$session['l_title']).$this->translator->trans(':')
-                                       ];
-
-                                       //Fetch pseudonyms from session applications
-                                       $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
-
-                                       //Set dance
-                                       $dance = null;
-
-                                       //Set pseudonym
-                                       $pseudonym = null;
-
-                                       //Check that session is not granted
-                                       if (empty($session['a_id'])) {
-                                               //With location id and unique application
-                                               if ($count == 1) {
-                                                       //Set unique application pseudonym
-                                                       $pseudonym = $session['sau_pseudonym'];
-                                               }
-                                       //Session is granted
-                                       } else {
-                                               //Replace granted application
-                                               $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
-
-                                               //Set dance
-                                               $dance = $this->translator->trans($session['ad_name'].' '.lcfirst($session['ad_type']));
-
-                                               //Set pseudonym
-                                               $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
-                                       }
-
-                                       //Set title
-                                       $title = $this->translator->trans($session['l_title']).($count > 1 ? ' ['.$count.']':'');
-
-                                       //Add the session
-                                       $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [
-                                               'id' => $session['id'],
-                                               'start' => $session['start'],
-                                               'stop' => $session['stop'],
-                                               'location' => $this->translator->trans($session['l_title']),
-                                               'dance' => $dance,
-                                               'pseudonym' => $pseudonym,
-                                               'class' => $class,
-                                               'slot' => self::GLYPHS[$session['t_title']],
-                                               'slottitle' => $this->translator->trans($session['t_title']),
-                                               'weather' => $weather,
-                                               'weathertitle' => implode(' ', $weathertitle),
-                                               'applications' => $applications,
-                                               'rate' => $session['p_rate'],
-                                               'hat' => $session['p_hat']
-                                       ];
-                               }
-                       }
-
-                       //Sort sessions
-                       ksort($calendar[$Ymd]['sessions']);
-               }
-
-               //Send result
-               return $calendar;
-       }
-
-       /**
-        * Find all session pending hourly weather
-        *
-        * @return array<Session> The sessions to update
-        */
-       public function findAllPendingHourlyWeather() {
-               //Select all sessions starting and stopping in the next 3 days
-               //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)
-               $req = <<<SQL
-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
-FROM RapsysAirBundle:Session AS s
-JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
-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))
-SQL;
-
-               //Replace bundle entity name by table name
-               $req = str_replace($this->tableKeys, $this->tableValues, $req);
-
-               //Get result set mapping instance
-               $rsm = new ResultSetMapping();
-
-               //Declare all fields
-               $rsm
-                       ->addEntityResult('RapsysAirBundle:Session', 's')
-                       ->addFieldResult('s', 'id', 'id')
-                       ->addFieldResult('s', 'date', 'date')
-                       ->addFieldResult('s', 'begin', 'begin')
-                       ->addFieldResult('s', 'length', 'length')
-                       ->addFieldResult('s', 'rainfall', 'rainfall')
-                       ->addFieldResult('s', 'rainrisk', 'rainrisk')
-                       ->addFieldResult('s', 'realfeel', 'realfeel')
-                       ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
-                       ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
-                       ->addFieldResult('s', 'temperature', 'temperature')
-                       ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
-                       ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
-                       ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
-                       ->addFieldResult('o', 'slot_id', 'id')
-                       ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
-                       ->addFieldResult('l', 'location_id', 'id')
-                       ->addFieldResult('l', 'zipcode', 'zipcode')
-                       ->addIndexBy('s', 'id');
+               //Declare all fields
+               $rsm
+                       ->addEntityResult('Rapsys\AirBundle\Entity\Session', 's')
+                       ->addFieldResult('s', 'id', 'id')
+                       ->addFieldResult('s', 'date', 'date')
+                       ->addFieldResult('s', 'begin', 'begin')
+                       ->addFieldResult('s', 'length', 'length')
+                       ->addFieldResult('s', 'rainfall', 'rainfall')
+                       ->addFieldResult('s', 'rainrisk', 'rainrisk')
+                       ->addFieldResult('s', 'realfeel', 'realfeel')
+                       ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
+                       ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
+                       ->addFieldResult('s', 'temperature', 'temperature')
+                       ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
+                       ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
+                       ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Slot', 'o', 's', 'slot')
+                       ->addFieldResult('o', 'slot_id', 'id')
+                       ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Location', 'l', 's', 'location')
+                       ->addFieldResult('l', 'location_id', 'id')
+                       ->addFieldResult('l', 'zipcode', 'zipcode')
+                       ->addIndexBy('s', 'id');

 
                //Send result
                return $this->_em
 
                //Send result
                return $this->_em


@@ -1943,15 +1281,15 @@ SQL;
        /**
         * Find all session pending daily weather
         *
        /**
         * Find all session pending daily weather
         *

-        * @return array<Session> The sessions to update
+        * @return array The sessions to update

         */
         */

-       public function findAllPendingDailyWeather() {
+       public function findAllPendingDailyWeather(): array {

                //Select all sessions stopping after next 3 days
                //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
                $req = <<<SQL
 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
                //Select all sessions stopping after next 3 days
                //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
                $req = <<<SQL
 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

-FROM RapsysAirBundle:Session AS s
-JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
+FROM Rapsys\AirBundle\Entity\Session AS s
+JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)

 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))
 SQL;
 
 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))
 SQL;
 


@@ -1963,7 +1301,7 @@ SQL;
 
                //Declare all fields
                $rsm
 
                //Declare all fields
                $rsm

-                       ->addEntityResult('RapsysAirBundle:Session', 's')
+                       ->addEntityResult('Rapsys\AirBundle\Entity\Session', 's')

                        ->addFieldResult('s', 'id', 'id')
                        ->addFieldResult('s', 'date', 'date')
                        ->addFieldResult('s', 'begin', 'begin')
                        ->addFieldResult('s', 'id', 'id')
                        ->addFieldResult('s', 'date', 'date')
                        ->addFieldResult('s', 'begin', 'begin')


@@ -1976,9 +1314,9 @@ SQL;
                        ->addFieldResult('s', 'temperature', 'temperature')
                        ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
                        ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
                        ->addFieldResult('s', 'temperature', 'temperature')
                        ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
                        ->addFieldResult('s', 'temperaturemax', 'temperaturemax')

-                       ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
+                       ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Slot', 'o', 's', 'slot')

                        ->addFieldResult('o', 'slot_id', 'id')
                        ->addFieldResult('o', 'slot_id', 'id')

-                       ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
+                       ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Location', 'l', 's', 'location')

                        ->addFieldResult('l', 'location_id', 'id')
                        ->addFieldResult('l', 'zipcode', 'zipcode')
                        ->addIndexBy('s', 'id');
                        ->addFieldResult('l', 'location_id', 'id')
                        ->addFieldResult('l', 'zipcode', 'zipcode')
                        ->addIndexBy('s', 'id');


@@ -1992,21 +1330,21 @@ SQL;
        /**
         * Find every session pending application
         *
        /**
         * Find every session pending application
         *

-        * @return array<Session> The sessions to update
+        * @return array The sessions to update

         */
         */

-       public function findAllPendingApplication() {
+       public function findAllPendingApplication(): array {

                //Select all sessions not locked without application or canceled application within attribution period
                //XXX: DIFF(start, now) <= IF(DIFF(start, created) <= SENIOR_DELAY in DAY, DIFF(start, created) * 3 / 4, SENIOR_DELAY)
                //TODO: remonter les données pour le mail ?
                $req =<<<SQL
 SELECT s.id
                //Select all sessions not locked without application or canceled application within attribution period
                //XXX: DIFF(start, now) <= IF(DIFF(start, created) <= SENIOR_DELAY in DAY, DIFF(start, created) * 3 / 4, SENIOR_DELAY)
                //TODO: remonter les données pour le mail ?
                $req =<<<SQL
 SELECT s.id

-FROM RapsysAirBundle:Session as s
-LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id AND a.canceled IS NULL)
-JOIN RapsysAirBundle:Application AS a2 ON (a2.session_id = s.id AND a2.canceled IS NULL)
-WHERE s.locked IS NULL AND a.id IS NULL AND
-TIME_TO_SEC(TIMEDIFF(@dt_start := ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) <= IF(
-       TIME_TO_SEC(@td_sc := TIMEDIFF(@dt_start, s.created)) <= :seniordelay,
-       ROUND(TIME_TO_SEC(@td_sc) * :regulardelay / :seniordelay),
+FROM Rapsys\AirBundle\Entity\Session as s
+LEFT JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id AND a.canceled IS NULL)
+JOIN Rapsys\AirBundle\Entity\Application AS a2 ON (a2.session_id = s.id AND a2.canceled IS NULL)
+WHERE s.locked IS NULL AND s.application_id IS NULL AND
+(UNIX_TIMESTAMP(@dt_start := ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY)) - UNIX_TIMESTAMP()) <= IF(
+       (@td_sc := UNIX_TIMESTAMP(@dt_start) - UNIX_TIMESTAMP(s.created)) <= :seniordelay,
+       ROUND(@td_sc * :regulardelay / :seniordelay),

        :seniordelay
 )
 GROUP BY s.id
        :seniordelay
 )
 GROUP BY s.id


@@ -2021,7 +1359,7 @@ SQL;
 
                //Declare all fields
                $rsm
 
                //Declare all fields
                $rsm

-                       ->addEntityResult('RapsysAirBundle:Session', 's')
+                       ->addEntityResult('Rapsys\AirBundle\Entity\Session', 's')

                        ->addFieldResult('s', 'id', 'id')
                        ->addIndexBy('s', 'id');
 
                        ->addFieldResult('s', 'id', 'id')
                        ->addIndexBy('s', 'id');
 


@@ -2035,9 +1373,9 @@ SQL;
         * Fetch session best application by session id
         *
         * @param int $id The session id
         * Fetch session best application by session id
         *
         * @param int $id The session id

-        * @return Application|null The application or null
+        * @return ?Application The application or null

         */
         */

-       public function findBestApplicationById($id) {
+       public function findBestApplicationById(int $id): ?Application {

                /**
                 * Query session applications ranked by location score, global score, created and user_id
                 *
                /**
                 * Query session applications ranked by location score, global score, created and user_id
                 *


@@ -2120,33 +1458,33 @@ FROM (
                                        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,
                                        (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,
                                        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,
                                        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,
                                        (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,
                                        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,

-                                       TIME_TO_SEC(TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) AS remaining,
+                                       UNIX_TIMESTAMP(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY)) - UNIX_TIMESTAMP() AS remaining,

                                        s.premium,
                                        l.hotspot,
                                        a.created
                                        s.premium,
                                        l.hotspot,
                                        a.created

-                               FROM RapsysAirBundle:Session AS s
-                               JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
-                               JOIN RapsysAirBundle:Application AS a ON (a.session_id = s.id AND a.canceled IS NULL)
-                               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)
-                               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))
+                               FROM Rapsys\AirBundle\Entity\Session AS s
+                               JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
+                               JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.session_id = s.id AND a.canceled IS NULL)
+                               LEFT JOIN Rapsys\AirBundle\Entity\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)
+                               LEFT JOIN Rapsys\AirBundle\Entity\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))

                                WHERE s.id = :sid
                                GROUP BY a.id
                                ORDER BY NULL
                                LIMIT 0, :limit
                        ) AS b
                                WHERE s.id = :sid
                                GROUP BY a.id
                                ORDER BY NULL
                                LIMIT 0, :limit
                        ) AS b

-                       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)
-                       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))
+                       LEFT JOIN Rapsys\AirBundle\Entity\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)
+                       LEFT JOIN Rapsys\AirBundle\Entity\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))

                        GROUP BY b.id
                        ORDER BY NULL
                        LIMIT 0, :limit
                ) AS c
                        GROUP BY b.id
                        ORDER BY NULL
                        LIMIT 0, :limit
                ) AS c

-               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)
-               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))
+               LEFT JOIN Rapsys\AirBundle\Entity\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)
+               LEFT JOIN Rapsys\AirBundle\Entity\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))

                GROUP BY c.id
                ORDER BY NULL
                LIMIT 0, :limit
        ) AS d
                GROUP BY c.id
                ORDER BY NULL
                LIMIT 0, :limit
        ) AS d

-       LEFT JOIN RapsysAirBundle:UserGroup AS ug ON (ug.user_id = d.user_id)
+       LEFT JOIN Rapsys\AirBundle\Entity\UserGroup AS ug ON (ug.user_id = d.user_id)

        GROUP BY d.id
        LIMIT 0, :limit
 ) AS e
        GROUP BY d.id
        LIMIT 0, :limit
 ) AS e


@@ -2163,7 +1501,7 @@ SQL;
                $req = str_replace($this->tableKeys, $this->tableValues, $req);
 
                //Set update request
                $req = str_replace($this->tableKeys, $this->tableValues, $req);
 
                //Set update request

-               $upreq = 'UPDATE RapsysAirBundle:Application SET score = :score, updated = NOW() WHERE id = :id';
+               $upreq = 'UPDATE Rapsys\AirBundle\Entity\Application SET score = :score, updated = NOW() WHERE id = :id';

 
                //Replace bundle entity name by table name
                $upreq = str_replace($this->tableKeys, $this->tableValues, $upreq);
 
                //Replace bundle entity name by table name
                $upreq = str_replace($this->tableKeys, $this->tableValues, $upreq);


@@ -2173,7 +1511,7 @@ SQL;
 
                //Declare all fields
                $rsm
 
                //Declare all fields
                $rsm

-                       ->addEntityResult('RapsysAirBundle:Application', 'a')
+                       ->addEntityResult('Rapsys\AirBundle\Entity\Application', 'a')

                        ->addFieldResult('a', 'id', 'id')
                        ->addFieldResult('a', 'score', 'score')
                        ->addIndexBy('a', 'id');
                        ->addFieldResult('a', 'id', 'id')
                        ->addFieldResult('a', 'score', 'score')
                        ->addIndexBy('a', 'id');


@@ -2205,7 +1543,6 @@ SQL;
                return $ret;
        }
 
                return $ret;
        }
 

-

        /**
         * Rekey sessions and applications by chronological session id
         *
        /**
         * Rekey sessions and applications by chronological session id
         *


@@ -2227,8 +1564,8 @@ FROM (
                s.begin,
                s.slot_id,
                GROUP_CONCAT(sa.id ORDER BY sa.id SEPARATOR "\\n") AS sa_id
                s.begin,
                s.slot_id,
                GROUP_CONCAT(sa.id ORDER BY sa.id SEPARATOR "\\n") AS sa_id

-       FROM RapsysAirBundle:Session AS s
-       LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
+       FROM Rapsys\AirBundle\Entity\Session AS s
+       LEFT JOIN Rapsys\AirBundle\Entity\Application AS sa ON (sa.session_id = s.id)

        GROUP BY s.id
        ORDER BY NULL
 ) AS a
        GROUP BY s.id
        ORDER BY NULL
 ) AS a


@@ -2260,7 +1597,7 @@ SQL;
 
                //Set update session request
                $sreq = <<<SQL
 
                //Set update session request
                $sreq = <<<SQL

-UPDATE RapsysAirBundle:Session
+UPDATE Rapsys\AirBundle\Entity\Session

 SET id = :nid, updated = NOW()
 WHERE id = :id
 SQL;
 SET id = :nid, updated = NOW()
 WHERE id = :id
 SQL;


@@ -2270,7 +1607,7 @@ SQL;
 
                //Set update application request
                $areq = <<<SQL
 
                //Set update application request
                $areq = <<<SQL

-UPDATE RapsysAirBundle:Application
+UPDATE Rapsys\AirBundle\Entity\Application

 SET session_id = :nid, updated = NOW()
 WHERE session_id = :id
 SQL;
 SET session_id = :nid, updated = NOW()
 WHERE session_id = :id
 SQL;


@@ -2331,7 +1668,7 @@ SQL;
 
                                //Set update auto_increment request
                                $ireq = <<<SQL
 
                                //Set update auto_increment request
                                $ireq = <<<SQL

-ALTER TABLE RapsysAirBundle:Session
+ALTER TABLE Rapsys\AirBundle\Entity\Session

 auto_increment = 1
 SQL;
 
 auto_increment = 1
 SQL;