1 <?php
declare(strict_types
=1);
4 * This file is part of the Rapsys AirBundle package.
6 * (c) RaphaΓ«l Gertz <symfony@rapsys.eu>
8 * For the full copyright and license information, please view the LICENSE
9 * file that was distributed with this source code.
12 namespace Rapsys\AirBundle\Repository
;
14 use Doctrine\DBAL\Types\Types
;
15 use Doctrine\ORM\AbstractQuery
;
16 use Doctrine\ORM\Query\ResultSetMapping
;
18 use Symfony\Component\Routing\Generator\UrlGeneratorInterface
;
20 use Rapsys\AirBundle\Entity\Application
;
21 use Rapsys\AirBundle\Entity\Location
;
22 use Rapsys\AirBundle\Entity\Session
;
23 use Rapsys\AirBundle\Entity\Slot
;
24 use Rapsys\AirBundle\Repository
;
29 class SessionRepository
extends Repository
{
31 //TODO: document utf-8 codes ?
32 //TODO: use unknown == ? symbol by default ???
33 //π<= dancer #0001f483
34 //π<= tanguera #0001f483
37 'Morning' => 'π
', #0001f305
38 'Afternoon' => 'βοΈ', #2600
39 'Evening' => 'π', #0001f307
40 'After' => 'β¨', #2728
42 'Cleary' => 'β', #2600
43 'Sunny' => 'β
', #26c5
44 'Cloudy' => 'β', #2601
45 'Winty' => 'βοΈ', #2744
46 'Rainy' => 'π', #0001f302
47 'Stormy' => 'β', #2614
49 'Euro' => 'β¬', #20ac
50 'Free' => 'πΊ', #0001f37a
51 'Hat' => 'π©' #0001f3a9
55 * Find session as array by id
57 * @param int $id The session id
58 * @return array The session data
60 public function findOneByIdAsArray(int $id): ?array {
67 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
69 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
81 s.location_id AS l_id,
83 l.description AS l_description,
84 l.address AS l_address,
85 l.zipcode AS l_zipcode,
87 l.latitude AS l_latitude,
88 l.longitude AS l_longitude,
90 l.updated AS l_updated,
93 s.application_id AS a_id,
94 a.canceled AS a_canceled,
99 au.pseudonym AS au_pseudonym,
101 p.description AS p_description,
103 p.contact AS p_contact,
104 p.donate AS p_donate,
106 p.profile AS p_profile,
109 GREATEST(COALESCE(s.updated, 0), COALESCE(l.updated, 0), COALESCE(t.updated, 0), COALESCE(p.updated, 0), COALESCE(MAX(sa.updated), 0), COALESCE(MAX(sau.updated), 0), COALESCE(MAX(sad.updated), 0)) AS modified,
110 GROUP_CONCAT(sa.id ORDER BY sa.user_id SEPARATOR "\\n") AS sa_id,
111 GROUP_CONCAT(IFNULL(sa.score, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_score,
112 GROUP_CONCAT(sa.created ORDER BY sa.user_id SEPARATOR "\\n") AS sa_created,
113 GROUP_CONCAT(sa.updated ORDER BY sa.user_id SEPARATOR "\\n") AS sa_updated,
114 GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled,
115 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
116 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
117 FROM Rapsys\AirBundle\Entity\Session AS s
118 JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
119 JOIN Rapsys\AirBundle\Entity\Slot AS t ON (t.id = s.slot_id)
120 LEFT JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id)
121 LEFT JOIN Rapsys\AirBundle\Entity\Dance AS ad ON (ad.id = a.dance_id)
122 LEFT JOIN Rapsys\AirBundle\Entity\User AS au ON (au.id = a.user_id)
123 LEFT JOIN Rapsys\AirBundle\Entity\Snippet AS p ON (p.locale = :locale AND p.location_id = s.location_id AND p.user_id = a.user_id)
124 LEFT JOIN Rapsys\AirBundle\Entity\Application AS sa ON (sa.session_id = s.id)
125 LEFT JOIN Rapsys\AirBundle\Entity\User AS sau ON (sau.id = sa.user_id)
126 LEFT JOIN Rapsys\AirBundle\Entity\Dance AS sad ON (sad.id = sa.dance_id)
132 //Replace bundle entity name by table name
133 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
135 //Get result set mapping instance
136 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
137 $rsm = new ResultSetMapping();
140 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
141 $rsm->addScalarResult('id', 'id', 'integer')
142 ->addScalarResult('date', 'date', 'date')
143 ->addScalarResult('begin', 'begin', 'time')
144 ->addScalarResult('start', 'start', 'datetime')
145 ->addScalarResult('length', 'length', 'time')
146 ->addScalarResult('stop', 'stop', 'datetime')
147 ->addScalarResult('rainfall', 'rainfall', 'float')
148 ->addScalarResult('rainrisk', 'rainrisk', 'float')
149 ->addScalarResult('realfeel', 'realfeel', 'float')
150 ->addScalarResult('realfeelmin', 'realfeelmin', 'float')
151 ->addScalarResult('realfeelmax', 'realfeelmax', 'float')
152 ->addScalarResult('temperature', 'temperature', 'float')
153 ->addScalarResult('temperaturemin', 'temperaturemin', 'float')
154 ->addScalarResult('temperaturemax', 'temperaturemax', 'float')
155 ->addScalarResult('locked', 'locked', 'datetime')
156 ->addScalarResult('created', 'created', 'datetime')
157 ->addScalarResult('updated', 'updated', 'datetime')
158 ->addScalarResult('l_id', 'l_id', 'integer')
159 ->addScalarResult('l_title', 'l_title', 'string')
160 ->addScalarResult('l_description', 'l_description', 'string')
161 ->addScalarResult('l_address', 'l_address', 'string')
162 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
163 ->addScalarResult('l_city', 'l_city', 'string')
164 ->addScalarResult('l_latitude', 'l_latitude', 'float')
165 ->addScalarResult('l_longitude', 'l_longitude', 'float')
166 ->addScalarResult('l_indoor', 'l_indoor', 'boolean')
167 ->addScalarResult('l_updated', 'l_updated', 'datetime')
168 ->addScalarResult('t_id', 't_id', 'integer')
169 ->addScalarResult('t_title', 't_title', 'string')
170 ->addScalarResult('a_id', 'a_id', 'integer')
171 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
172 ->addScalarResult('ad_id', 'ad_id', 'integer')
173 ->addScalarResult('ad_name', 'ad_name', 'string')
174 ->addScalarResult('ad_type', 'ad_type', 'string')
175 ->addScalarResult('au_id', 'au_id', 'integer')
176 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
177 ->addScalarResult('p_id', 'p_id', 'integer')
178 ->addScalarResult('p_description', 'p_description', 'text')
179 ->addScalarResult('p_class', 'p_class', 'text')
180 ->addScalarResult('p_contact', 'p_contact', 'text')
181 ->addScalarResult('p_donate', 'p_donate', 'text')
182 ->addScalarResult('p_link', 'p_link', 'text')
183 ->addScalarResult('p_profile', 'p_profile', 'text')
184 ->addScalarResult('p_rate', 'p_rate', 'integer')
185 ->addScalarResult('p_hat', 'p_hat', 'boolean')
186 ->addScalarResult('modified', 'modified', 'datetime')
187 //XXX: is a string because of \n separator
188 ->addScalarResult('sa_id', 'sa_id', 'string')
189 //XXX: is a string because of \n separator
190 ->addScalarResult('sa_score', 'sa_score', 'string')
191 //XXX: is a string because of \n separator
192 ->addScalarResult('sa_created', 'sa_created', 'string')
193 //XXX: is a string because of \n separator
194 ->addScalarResult('sa_updated', 'sa_updated', 'string')
195 //XXX: is a string because of \n separator
196 ->addScalarResult('sa_canceled', 'sa_canceled', 'string')
197 //XXX: is a string because of \n separator
198 ->addScalarResult('sau_id', 'sau_id', 'string')
199 //XXX: is a string because of \n separator
200 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
201 ->addIndexByScalar('id');
205 ->createNativeQuery($req, $rsm)
206 ->setParameter('id', $id)
207 ->getOneOrNullResult();
210 if ($result === null) {
216 $route = 'rapsysair_session_view';
219 $routeParams = ['id' => $id, 'location' => $this->slugger
->slug($this->translator
->trans($result['l_title']))];
224 'date' => $result['date'],
225 'begin' => $result['begin'],
226 'start' => $result['start'],
227 'length' => $result['length'],
228 'stop' => $result['stop'],
229 'rainfall' => $result['rainfall'] !== null ? $result['rainfall'].' mm' : $result['rainfall'],
230 'rainrisk' => $result['rainrisk'] !== null ? ($result['rainrisk']*100).' %' : $result['rainrisk'],
231 'realfeel' => $result['realfeel'] !== null ? $result['realfeel'].' Β°C' : $result['realfeel'],
232 'realfeelmin' => $result['realfeelmin'] !== null ? $result['realfeelmin'].' Β°C' : $result['realfeelmin'],
233 'realfeelmax' => $result['realfeelmax'] !== null ? $result['realfeelmax'].' Β°C' : $result['realfeelmax'],
234 'temperature' => $result['temperature'] !== null ? $result['temperature'].' Β°C' : $result['temperature'],
235 'temperaturemin' => $result['temperaturemin'] !== null ? $result['temperaturemin'].' Β°C' : $result['temperaturemin'],
236 'temperaturemax' => $result['temperaturemax'] !== null ? $result['temperaturemax'].' Β°C' : $result['temperaturemax'],
237 'locked' => $result['locked'],
238 'created' => $result['created'],
239 'updated' => $result['updated'],
240 'title' => $this->translator
->trans('Session %id%', ['%id%' => $id]),
241 'modified' => $result['modified'],
242 'application' => null,
244 'id' => $result['l_id'],
245 'at' => $this->translator
->trans('at '.$result['l_title']),
246 'title' => $locationTitle = $this->translator
->trans($result['l_title']),
247 'description' => $this->translator
->trans($result['l_description']??'None'),
248 'address' => $result['l_address'],
249 'zipcode' => $result['l_zipcode'],
250 'city' => $result['l_city'],
251 'in' => $this->translator
->trans('in '.$result['l_city']),
252 'map' => $this->translator
->trans($result['l_title'].' access map'),
253 'multimap' => $this->translator
->trans($result['l_title'].' sector map'),
254 'latitude' => $result['l_latitude'],
255 'longitude' => $result['l_longitude'],
256 'indoor' => $result['l_indoor'],
257 'slug' => $routeParams['location'],
258 'link' => $this->router
->generate('rapsysair_location_view', ['id' => $result['l_id'], 'location' => $routeParams['location']])
261 'id' => $result['t_id'],
262 'the' => $this->translator
->trans('the '.lcfirst($result['t_title'])),
263 'title' => $this->translator
->trans($result['t_title'])
266 'applications' => null
270 if (!empty($result['a_id'])) {
271 $session['application'] = [
273 'id' => $result['ad_id'],
274 'title' => $this->translator
->trans($result['ad_name'].' '.lcfirst($result['ad_type'])),
275 'name' => $this->translator
->trans($result['ad_name']),
276 'type' => $this->translator
->trans($result['ad_type']),
277 'slug' => $routeParams['dance'] = $this->slugger
->slug($this->translator
->trans($result['ad_name'].' '.lcfirst($result['ad_type']))),
278 'link' => $this->router
->generate('rapsysair_dance_view', ['id' => $result['ad_id'], 'name' => $this->slugger
->slug($this->translator
->trans($result['ad_name'])), 'type' => $this->slugger
->slug($this->translator
->trans($result['ad_type']))])
281 'id' => $result['au_id'],
282 'by' => $this->translator
->trans('by %pseudonym%', [ '%pseudonym%' => $result['au_pseudonym'] ]),
283 'title' => $result['au_pseudonym'],
284 'slug' => $routeParams['user'] = $this->slugger
->slug($result['au_pseudonym']),
285 'link' => $result['au_id'] == 1 && $routeParams['user'] == 'milonga-raphael' ? $this->router
->generate('rapsysair_user_milongaraphael') : $this->router
->generate('rapsysair_user_view', ['id' => $result['au_id'], 'user' => $routeParams['user']]),
286 'contact' => $this->router
->generate('rapsysair_contact', ['id' => $result['au_id'], 'user' => $routeParams['user']])
288 'id' => $result['a_id'],
289 'canceled' => $result['a_canceled']
294 if (!empty($result['p_id'])) {
295 $session['snippet'] = [
296 'id' => $result['p_id'],
297 'description' => $result['p_description'],
298 'class' => $result['p_class'],
299 'contact' => $result['p_contact'],
300 'donate' => $result['p_donate'],
301 'link' => $result['p_link'],
302 'profile' => $result['p_profile'],
303 'rate' => $result['p_rate'],
304 'hat' => $result['p_hat']
309 if (!empty($result['sa_id'])) {
310 //Extract applications id
311 $result['sa_id'] = explode("\n", $result['sa_id']);
312 //Extract applications score
313 //XXX: score may be null before grant or for bad behaviour, replace NULL with 'NULL' to avoid silent drop in mysql
314 $result['sa_score'] = array_map(function($v){return $v
==='NULL'?null:$v
;}, explode("\n", $result['sa_score']));
315 //Extract applications created
316 $result['sa_created'] = array_map(function($v){return new \
DateTime($v
);}, explode("\n", $result['sa_created']));
317 //Extract applications updated
318 $result['sa_updated'] = array_map(function($v){return new \
DateTime($v
);}, explode("\n", $result['sa_updated']));
319 //Extract applications canceled
320 //XXX: canceled is null before cancelation, replace NULL with 'NULL' to avoid silent drop in mysql
321 $result['sa_canceled'] = array_map(function($v){return $v
==='NULL'?null:new \
DateTime($v
);}, explode("\n", $result['sa_canceled']));
323 //Extract applications user id
324 $result['sau_id'] = explode("\n", $result['sau_id']);
325 //Extract applications user pseudonym
326 $result['sau_pseudonym'] = explode("\n", $result['sau_pseudonym']);
329 $session['applications'] = [];
331 //Iterate on each applications id
332 foreach($result['sa_id'] as $i => $sa_id) {
333 $session['applications'][$sa_id] = [
335 'score' => $result['sa_score'][$i],
336 'created' => $result['sa_created'][$i],
337 'updated' => $result['sa_updated'][$i],
338 'canceled' => $result['sa_canceled'][$i]
340 if (!empty($result['sau_id'][$i])) {
341 $session['applications'][$sa_id]['user'] = [
342 'id' => $result['sau_id'][$i],
343 'title' => $result['sau_pseudonym'][$i],
344 'slug' => $this->slugger
->slug($result['sau_pseudonym'][$i])
351 $session['link'] = $this->router
->generate($route, $routeParams);
354 $session['canonical'] = $this->router
->generate($route, $routeParams, UrlGeneratorInterface
::ABSOLUTE_URL
);
357 $session['alternates'] = [];
359 //Iterate on each locales
360 foreach($this->translator
->getFallbackLocales() as $fallback) {
364 //Set route params location
365 $routeParams['location'] = $this->slugger
->slug($this->translator
->trans($result['l_title'], [], null, $fallback));
367 //With route params dance
368 if (!empty($routeParams['dance'])) {
369 $routeParams['dance'] = $this->slugger
->slug($this->translator
->trans($result['ad_name'].' '.lcfirst($result['ad_type']), [], null, $fallback));
372 //With route params user
373 if (!empty($routeParams['user'])) {
374 $routeParams['user'] = $this->slugger
->slug($result['au_pseudonym']);
377 //With current locale
378 if ($fallback === $this->locale
) {
379 //Set current locale title
380 $titles[$this->locale
] = $this->translator
->trans($this->languages
[$this->locale
]);
381 //Without current locale
383 //Iterate on other locales
384 foreach(array_diff($this->translator
->getFallbackLocales(), [$fallback]) as $other) {
385 //Set other locale title
386 $titles[$other] = $this->translator
->trans($this->languages
[$fallback], [], null, $other);
389 //Add alternates locale
390 $session['alternates'][str_replace('_', '-', $fallback)] = [
391 'absolute' => $this->router
->generate($route, ['_locale' => $fallback]+
$routeParams, UrlGeneratorInterface
::ABSOLUTE_URL
),
392 'relative' => $this->router
->generate($route, ['_locale' => $fallback]+
$routeParams),
393 'title' => implode('/', $titles),
394 'translated' => $this->translator
->trans($this->languages
[$fallback], [], null, $fallback)
398 //Add alternates shorter locale
399 if (empty($parameters['alternates'][$shortFallback = substr($fallback, 0, 2)])) {
400 //Set locale locales context
401 $session['alternates'][$shortFallback] = [
402 'absolute' => $this->router
->generate($route, ['_locale' => $fallback]+
$routeParams, UrlGeneratorInterface
::ABSOLUTE_URL
),
403 'relative' => $this->router
->generate($route, ['_locale' => $fallback]+
$routeParams),
404 'title' => implode('/', $titles),
405 'translated' => $this->translator
->trans($this->languages
[$fallback], [], null, $fallback)
415 * Find sessions as calendar array by date period
417 * @param DatePeriod $period The date period
418 * @param ?bool $granted The session is granted
419 * @param ?float $latitude The latitude
420 * @param ?float $longitude The longitude
421 * @param ?int $userId The user id
422 * @return array The session data
424 public function findAllByPeriodAsCalendarArray(\DatePeriod
$period, ?bool $granted = null, ?float $latitude = null, ?float $longitude = null, ?int $userId = null): array {
428 //When granted is set
429 if (empty($granted)) {
430 //Set application and user as optional
437 //When latitude and longitude
438 if ($latitude !== null && $longitude !== null) {
440 //XXX: get every location between 0 and 15 km of latitude and longitude
443 FROM Rapsys\AirBundle\Entity\Location AS l
444 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
447 //Replace bundle entity name by table name
448 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
450 //Get result set mapping instance
451 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
452 $rsm = new ResultSetMapping();
455 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
456 //addScalarResult($sqlColName, $resColName, $type = 'string');
457 $rsm->addScalarResult('id', 'id', 'integer')
458 ->addIndexByScalar('id');
461 //XXX: check that latitude and longitude have not be swapped !!!
462 //XXX: latitude ~= 48.x longitude ~= 2.x
463 $locationIds = array_keys(
465 ->createNativeQuery($req, $rsm)
466 ->setParameter('latitude', $latitude)
467 ->setParameter('longitude', $longitude)
471 //Add location id clause
472 $locationSql = "\n\t".'AND s.location_id IN (:lids)';
474 } elseif ($userId !== null) {
476 //XXX: get every location between 0 and 15 km
480 SELECT l.id, l.latitude, l.longitude
481 FROM Rapsys\AirBundle\Entity\Application AS a
482 JOIN Rapsys\AirBundle\Entity\Session AS s ON (s.id = a.session_id)
483 JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
484 WHERE a.user_id = :id
489 JOIN Rapsys\AirBundle\Entity\Location AS l2
490 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
495 //Replace bundle entity name by table name
496 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
498 //Get result set mapping instance
499 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
500 $rsm = new ResultSetMapping();
503 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
504 //addScalarResult($sqlColName, $resColName, $type = 'string');
505 $rsm->addScalarResult('id', 'id', 'integer')
506 ->addIndexByScalar('id');
509 $locationIds = array_keys(
511 ->createNativeQuery($req, $rsm)
512 ->setParameter('id', $userId)
517 if (!empty($locationIds)) {
518 //Add location id clause
519 $locationSql = "\n\t".'AND s.location_id IN (:lids)';
534 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
535 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
536 s.location_id AS l_id,
538 l.address AS l_address,
539 l.zipcode AS l_zipcode,
541 l.latitude AS l_latitude,
542 l.longitude AS l_longitude,
543 l.indoor AS l_indoor,
546 s.application_id AS a_id,
547 a.canceled AS a_canceled,
552 au.pseudonym AS au_pseudonym,
556 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
557 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym,
558 GROUP_CONCAT(sa.dance_id ORDER BY sa.user_id SEPARATOR "\\n") AS sad_id,
559 GROUP_CONCAT(sad.name ORDER BY sa.user_id SEPARATOR "\\n") AS sad_name,
560 GROUP_CONCAT(sad.type ORDER BY sa.user_id SEPARATOR "\\n") AS sad_type,
561 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
562 FROM Rapsys\AirBundle\Entity\Session AS s
563 JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
564 JOIN Rapsys\AirBundle\Entity\Slot AS t ON (t.id = s.slot_id)
565 {$grantSql}JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id)
566 {$grantSql}JOIN Rapsys\AirBundle\Entity\Dance AS ad ON (ad.id = a.dance_id)
567 {$grantSql}JOIN Rapsys\AirBundle\Entity\User AS au ON (au.id = a.user_id)
568 LEFT JOIN Rapsys\AirBundle\Entity\Snippet AS p ON (p.locale = :locale AND p.location_id = s.location_id AND p.user_id = a.user_id)
569 LEFT JOIN Rapsys\AirBundle\Entity\Application AS sa ON (sa.session_id = s.id)
570 LEFT JOIN Rapsys\AirBundle\Entity\Dance AS sad ON (sad.id = sa.dance_id)
571 LEFT JOIN Rapsys\AirBundle\Entity\User AS sau ON (sau.id = sa.user_id)
572 WHERE s.date BETWEEN :begin AND :end{$locationSql}
577 //Replace bundle entity name by table name
578 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
580 //Get result set mapping instance
581 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
582 $rsm = new ResultSetMapping();
585 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
586 //addScalarResult($sqlColName, $resColName, $type = 'string');
587 $rsm->addScalarResult('id', 'id', 'integer')
588 ->addScalarResult('date', 'date', 'date')
589 ->addScalarResult('rainrisk', 'rainrisk', 'float')
590 ->addScalarResult('rainfall', 'rainfall', 'float')
591 ->addScalarResult('realfeel', 'realfeel', 'float')
592 ->addScalarResult('temperature', 'temperature', 'float')
593 ->addScalarResult('locked', 'locked', 'datetime')
594 ->addScalarResult('start', 'start', 'datetime')
595 ->addScalarResult('stop', 'stop', 'datetime')
596 ->addScalarResult('modified', 'modified', 'datetime')
597 ->addScalarResult('t_id', 't_id', 'integer')
598 ->addScalarResult('t_title', 't_title', 'string')
599 ->addScalarResult('l_id', 'l_id', 'integer')
600 ->addScalarResult('l_title', 'l_title', 'string')
601 ->addScalarResult('l_address', 'l_address', 'string')
602 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
603 ->addScalarResult('l_city', 'l_city', 'string')
604 ->addScalarResult('l_latitude', 'l_latitude', 'float')
605 ->addScalarResult('l_longitude', 'l_longitude', 'float')
606 ->addScalarResult('l_indoor', 'l_indoor', 'boolean')
607 ->addScalarResult('a_id', 'a_id', 'integer')
608 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
609 ->addScalarResult('ad_id', 'ad_id', 'string')
610 ->addScalarResult('ad_name', 'ad_name', 'string')
611 ->addScalarResult('ad_type', 'ad_type', 'string')
612 ->addScalarResult('au_id', 'au_id', 'integer')
613 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
614 ->addScalarResult('p_hat', 'p_hat', 'boolean')
615 ->addScalarResult('p_rate', 'p_rate', 'integer')
616 ->addScalarResult('p_short', 'p_short', 'string')
617 //XXX: is a string because of \n separator
618 ->addScalarResult('sau_id', 'sau_id', 'string')
619 //XXX: is a string because of \n separator
620 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
621 //XXX: is a string because of \n separator
622 ->addScalarResult('sad_id', 'sad_id', 'string')
623 //XXX: is a string because of \n separator
624 ->addScalarResult('sad_name', 'sad_name', 'string')
625 //XXX: is a string because of \n separator
626 ->addScalarResult('sad_type', 'sad_type', 'string')
627 ->addIndexByScalar('id');
631 ->createNativeQuery($req, $rsm)
632 ->setParameter('begin', $period->getStartDate())
633 ->setParameter('end', $period->getEndDate());
635 //Add optional location ids
636 if (!empty($locationIds)) {
637 $res->setParameter('lids', $locationIds);
641 $result = $res->getResult();
650 $route = 'rapsysair_session_view';
652 //Iterate on each day
653 foreach($period as $date) {
654 //Init day in calendar
655 $calendar[$Ymd = $date->format('Ymd')] = [
656 'title' => $this->translator
->trans($date->format('l')).' '.$date->format('d'),
662 //Detect month change
663 if ($month != $date->format('m')) {
664 $month = $date->format('m');
665 //Append month for first day of month
666 //XXX: except if today to avoid double add
667 if ($date->format('U') != strtotime('today')) {
668 $calendar[$Ymd]['title'] .= '/'.$month;
672 if ($date->format('U') == ($today = strtotime('today'))) {
673 $calendar[$Ymd]['title'] .= '/'.$month;
674 $calendar[$Ymd]['current'] = true;
675 $calendar[$Ymd]['class'][] = 'current';
677 //Disable passed days
678 if ($date->format('U') < $today) {
679 $calendar[$Ymd]['disabled'] = true;
680 $calendar[$Ymd]['class'][] = 'disabled';
682 //Set next month days
683 if ($date->format('m') > date('m')) {
684 $calendar[$Ymd]['next'] = true;
685 #$calendar[$Ymd]['class'][] = 'next';
689 if ($date->format('w') == 0) {
690 $calendar[$Ymd]['class'][] = 'sunday';
693 //Iterate on each session to find the one of the day
694 foreach($result as $session) {
695 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
696 //With empty or greatest modified
697 if ($calendar[$Ymd]['modified'] === null || $session['modified'] >= $calendar[$Ymd]['modified']) {
699 $calendar[$Ymd]['modified'] = $session['modified'];
703 $applications = array_combine($candidates = explode("\n", $session['sau_id']), explode("\n", $session['sau_pseudonym']));
709 if (!empty($session['locked'])) {
714 if (!empty($session['a_id'])) {
715 //With canceled session
716 if (!empty($session['a_canceled'])) {
717 $class[] = 'canceled';
718 //With disputed session
719 } elseif ($userId !== null && $session['au_id'] != $userId && !empty($candidates[$userId])) {
720 $class[] = 'disputed';
723 $class[] = 'granted';
727 if ($userId !== null && $session['au_id'] == $userId) {
728 $class[] = 'highlight';
731 $class[] = 'pending';
734 //With latitude and longitude
735 if ($latitude !== null && $longitude !== null && $session['l_latitude'] == $latitude && $session['l_longitude'] == $longitude) {
736 $class[] = 'highlight';
742 'glyph' => self
::GLYPHS
['Cleary'],
746 //Compute temperature glyph
747 //XXX: temperature may be null
748 if ($session['temperature'] >= 17 && $session['temperature'] < 24) {
749 $temperature['glyph'] = self
::GLYPHS
['Sunny'];
750 } elseif ($session['temperature'] >= 10 && $session['temperature'] < 17) {
751 $temperature['glyph'] = self
::GLYPHS
['Cloudy'];
752 } elseif ($session['temperature'] !== null && $session['temperature'] < 10) {
753 $temperature['glyph'] = self
::GLYPHS
['Winty'];
756 //Check if temperature is available
757 if ($session['temperature'] !== null) {
758 $temperature['title'][] = $session['temperature'].'Β°C';
761 //Check if realfeel is available
762 if ($session['realfeel'] !== null) {
763 $temperature['title'][] = $session['realfeel'].'Β°R';
766 //Compute temperature title
767 $temperature['title'] = implode(' ', $temperature['title']);
771 'glyph' => self
::GLYPHS
['Cleary'],
776 //XXX: rainfall and rainrisk may be null
777 if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
778 $rain['glyph'] = self
::GLYPHS
['Stormy'];
779 } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
780 $rain['glyph'] = self
::GLYPHS
['Rainy'];
783 //Check if rainrisk is available
784 if ($session['rainrisk'] !== null) {
785 $rain['title'][] = ($session['rainrisk']*100).'%';
788 //Check if rainfall is available
789 if ($session['rainfall'] !== null) {
790 $rain['title'][] = $session['rainfall'].'mm';
794 $rain['title'] = implode(' ', $rain['title']);
803 $routeParams = ['id' => $session['id'], 'location' => $this->slugger
->slug($this->translator
->trans($session['l_title']))];
806 if (!empty($session['a_id'])) {
808 $routeParams['dance'] = $this->slugger
->slug($dance = $this->translator
->trans($session['ad_name'].' '.lcfirst($session['ad_type'])));
811 $routeParams['user'] = $this->slugger
->slug($session['au_pseudonym']);
814 $title = $this->translator
->trans('%dance% %id% by %pseudonym% %location% %city%', ['%dance%' => $dance, '%id%' => $session['id'], '%pseudonym%' => $session['au_pseudonym'], '%location%' => $this->translator
->trans('at '.$session['l_title']), '%city%' => $this->translator
->trans('in '.$session['l_city'])]);
819 'id' => $session['ad_id'],
820 'name' => $this->translator
->trans($session['ad_name']),
821 'type' => $this->translator
->trans($session['ad_type']),
825 'id' => $session['au_id'],
826 'title' => $session['au_pseudonym']
832 'glyph' => self
::GLYPHS
['Free'],
834 'short' => $session['p_short'],
835 'title' => $this->translator
->trans('Free')
839 if (!empty($session['p_hat'])) {
841 $rate['glyph'] = self
::GLYPHS
['Hat'];
844 if (!empty($session['p_rate'])) {
846 $rate['rate'] = $session['p_rate'];
849 $rate['title'] = $this->translator
->trans('%rate%β¬ to the hat', ['%rate%' => $session['p_rate']]);
853 $rate['title'] = $this->translator
->trans('To the hat');
856 } elseif (!empty($session['p_rate'])) {
858 $rate['glyph'] = self
::GLYPHS
['Euro'];
861 $rate['rate'] = $session['p_rate'];
864 $rate['title'] = $session['p_rate'].' β¬';
866 //With unique application
867 } elseif (count($applications) == 1) {
869 $dance = $this->translator
->trans($session['sad_name'].' '.lcfirst($session['sad_type']));
872 $title = $this->translator
->trans('%dance% %id% by %pseudonym% %location% %city%', ['%dance%' => $dance, '%id%' => $session['id'], '%pseudonym%' => $session['sau_pseudonym'], '%location%' => $this->translator
->trans('at '.$session['l_title']), '%city%' => $this->translator
->trans('in '.$session['l_city'])]);
877 'id' => $session['sad_id'],
878 'name' => $this->translator
->trans($session['sad_name']),
879 'type' => $this->translator
->trans($session['sad_type']),
883 'id' => $session['sau_id'],
884 'title' => $session['sau_pseudonym']
888 //TODO: glyph stuff ???
889 //Without application
892 $title = $this->translator
->trans('%slot% %id% %location%', ['%slot%' => $this->translator
->trans($session['t_title']), '%id%' => $session['id'], '%location%' => $this->translator
->trans('at '.$session['l_title'])]);
896 $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%05d', $session['id'])] = [
897 'id' => $session['id'],
898 'start' => $session['start'],
899 'stop' => $session['stop'],
901 'temperature' => $temperature,
904 'link' => $this->router
->generate($route, $routeParams),
906 'id' => $session['l_id'],
907 'title' => $this->translator
->trans($session['l_title']),
908 'address' => $session['l_address'],
909 'latitude' => $session['l_latitude'],
910 'longitude' => $session['l_longitude'],
911 'indoor' => $session['l_indoor'],
912 'at' => $at = $this->translator
->trans('at '.$session['l_title']),
913 'in' => $in = $this->translator
->trans('in '.$session['l_city']),
914 'atin' => $at.' '.$in,
915 'city' => $session['l_city'],
916 'zipcode' => $session['l_zipcode']
918 'application' => $application,
920 'glyph' => self
::GLYPHS
[$session['t_title']],
921 'title' => $this->translator
->trans($session['t_title'])
924 'modified' => $session['modified'],
925 'applications' => $applications
931 ksort($calendar[$Ymd]['sessions']);
940 * Find sessions by user id and synchronized date time
942 * @param int $userId The user id
943 * @param DateTime $synchronized The synchronized datetime
944 * @return array The session data
946 public function findAllByUserIdSynchronized(int $userId, \DateTime
$synchronized): array {
950 FROM Rapsys\AirBundle\Entity\UserDance AS ud
951 WHERE ud.user_id = :uid
954 //Replace bundle entity name by table name
955 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
957 //Get result set mapping instance
958 $rsm = new ResultSetMapping();
960 //Declare dance id field
961 $rsm->addScalarResult('dance_id', 'dance_id', 'integer');
968 $userDances = $this->_em
969 ->createNativeQuery($req, $rsm)
970 ->setParameter('uid', $userId)
971 ->getResult(AbstractQuery
::HYDRATE_SCALAR_COLUMN
)
974 $danceSql = ' AND a.dance_id IN (:dids)';
980 FROM Rapsys\AirBundle\Entity\UserSubscription AS us
981 WHERE us.subscriber_id = :uid
984 //Replace bundle entity name by table name
985 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
987 //Get result set mapping instance
988 $rsm = new ResultSetMapping();
990 //Declare user id field
991 $rsm->addScalarResult('user_id', 'user_id', 'integer');
993 //Set subscription sql part
994 $subscriptionSql = '';
996 //With user subscription
998 $userSubscriptions = $this->_em
999 ->createNativeQuery($req, $rsm)
1000 ->setParameter('uid', $userId)
1001 ->getResult(AbstractQuery
::HYDRATE_SCALAR_COLUMN
)
1003 //Set subscription sql part
1004 $subscriptionSql = ' AND a.user_id IN (:uids)';
1014 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
1015 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
1016 s.location_id AS l_id,
1017 l.address AS l_address,
1018 l.zipcode AS l_zipcode,
1021 l.description AS l_description,
1022 l.latitude AS l_latitude,
1023 l.longitude AS l_longitude,
1024 s.application_id AS a_id,
1025 a.canceled AS a_canceled,
1029 au.forename AS au_forename,
1030 au.pseudonym AS au_pseudonym,
1032 p.description AS p_description,
1037 p.contact AS p_contact,
1038 p.donate AS p_donate,
1040 p.profile AS p_profile
1041 FROM Rapsys\AirBundle\Entity\Session AS s
1042 JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
1043 JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id{$danceSql}{$subscriptionSql})
1044 JOIN Rapsys\AirBundle\Entity\Dance AS ad ON (ad.id = a.dance_id)
1045 JOIN Rapsys\AirBundle\Entity\User AS au ON (au.id = a.user_id)
1046 LEFT JOIN Rapsys\AirBundle\Entity\Snippet AS p ON (p.locale = :locale AND p.location_id = s.location_id AND p.user_id = a.user_id)
1047 WHERE GREATEST(s.created, s.updated, a.created, a.updated, ADDTIME(ADDTIME(s.date, s.begin), s.length)) >= :synchronized
1050 //Replace bundle entity name by table name
1051 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
1053 //Get result set mapping instance
1054 $rsm = new ResultSetMapping();
1056 //Declare all fields
1057 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1058 //addScalarResult($sqlColName, $resColName, $type = 'string');
1060 ->addScalarResult('id', 'id', 'integer')
1061 ->addScalarResult('date', 'date', 'date')
1062 ->addScalarResult('locked', 'locked', 'datetime')
1063 ->addScalarResult('updated', 'updated', 'datetime')
1064 ->addScalarResult('start', 'start', 'datetime')
1065 ->addScalarResult('stop', 'stop', 'datetime')
1066 ->addScalarResult('l_id', 'l_id', 'integer')
1067 ->addScalarResult('l_address', 'l_address', 'string')
1068 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
1069 ->addScalarResult('l_city', 'l_city', 'string')
1070 ->addScalarResult('l_latitude', 'l_latitude', 'float')
1071 ->addScalarResult('l_longitude', 'l_longitude', 'float')
1072 ->addScalarResult('l_title', 'l_title', 'string')
1073 ->addScalarResult('l_description', 'l_description', 'string')
1074 ->addScalarResult('t_id', 't_id', 'integer')
1075 ->addScalarResult('t_title', 't_title', 'string')
1076 ->addScalarResult('a_id', 'a_id', 'integer')
1077 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
1078 ->addScalarResult('ad_name', 'ad_name', 'string')
1079 ->addScalarResult('ad_type', 'ad_type', 'string')
1080 ->addScalarResult('au_id', 'au_id', 'integer')
1081 ->addScalarResult('au_forename', 'au_forename', 'string')
1082 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
1083 ->addScalarResult('p_id', 'p_id', 'integer')
1084 ->addScalarResult('p_description', 'p_description', 'string')
1085 ->addScalarResult('p_class', 'p_class', 'string')
1086 ->addScalarResult('p_short', 'p_short', 'string')
1087 ->addScalarResult('p_hat', 'p_hat', 'integer')
1088 ->addScalarResult('p_rate', 'p_rate', 'integer')
1089 ->addScalarResult('p_contact', 'p_contact', 'string')
1090 ->addScalarResult('p_donate', 'p_donate', 'string')
1091 ->addScalarResult('p_link', 'p_link', 'string')
1092 ->addScalarResult('p_profile', 'p_profile', 'string')
1093 ->addIndexByScalar('id');
1096 //TODO: XXX: finish here
1098 ->createNativeQuery($req, $rsm)
1099 ->setParameter('dids', $userDances)
1100 ->setParameter('uids', $userSubscriptions)
1101 ->setParameter('synchronized', $synchronized)
1106 * Find session by location, slot and date
1108 * @param Location $location The location
1109 * @param Slot $slot The slot
1110 * @param DateTime $date The datetime
1111 * @return ?Session The found session
1113 public function findOneByLocationSlotDate(Location
$location, Slot
$slot, \DateTime
$date): ?Session
{
1115 return $this->getEntityManager()
1116 ->createQuery('SELECT s FROM Rapsys\AirBundle\Entity\Session s WHERE (s.location = :location AND s.slot = :slot AND s.date = :date)')
1117 ->setParameter('location', $location)
1118 ->setParameter('slot', $slot)
1119 ->setParameter('date', $date)
1120 ->getSingleResult();
1124 * Fetch sessions by date period
1126 * @XXX: used in calendar command
1128 * @param DatePeriod $period The date period
1129 * @return array The session array
1131 public function fetchAllByDatePeriod(\DatePeriod
$period): array {
1133 //TODO: exclude opera and others ?
1140 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
1141 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
1142 s.location_id AS l_id,
1143 l.address AS l_address,
1144 l.zipcode AS l_zipcode,
1147 l.description AS l_description,
1148 l.latitude AS l_latitude,
1149 l.longitude AS l_longitude,
1150 s.application_id AS a_id,
1151 a.canceled AS a_canceled,
1155 au.forename AS au_forename,
1156 au.pseudonym AS au_pseudonym,
1158 p.description AS p_description,
1163 p.contact AS p_contact,
1164 p.donate AS p_donate,
1166 p.profile AS p_profile
1167 FROM Rapsys\AirBundle\Entity\Session AS s
1168 JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
1169 JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id)
1170 JOIN Rapsys\AirBundle\Entity\Dance AS ad ON (ad.id = a.dance_id)
1171 JOIN Rapsys\AirBundle\Entity\User AS au ON (au.id = a.user_id)
1172 LEFT JOIN Rapsys\AirBundle\Entity\Snippet AS p ON (p.locale = :locale AND p.location_id = s.location_id AND p.user_id = a.user_id)
1173 WHERE s.date BETWEEN :begin AND :end
1177 //Replace bundle entity name by table name
1178 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
1180 //Get result set mapping instance
1181 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1182 $rsm = new ResultSetMapping();
1184 //Declare all fields
1185 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1186 //addScalarResult($sqlColName, $resColName, $type = 'string');
1187 $rsm->addScalarResult('id', 'id', 'integer')
1188 ->addScalarResult('date', 'date', 'date')
1189 ->addScalarResult('locked', 'locked', 'datetime')
1190 ->addScalarResult('updated', 'updated', 'datetime')
1191 ->addScalarResult('start', 'start', 'datetime')
1192 ->addScalarResult('stop', 'stop', 'datetime')
1193 ->addScalarResult('l_id', 'l_id', 'integer')
1194 ->addScalarResult('l_address', 'l_address', 'string')
1195 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
1196 ->addScalarResult('l_city', 'l_city', 'string')
1197 ->addScalarResult('l_latitude', 'l_latitude', 'float')
1198 ->addScalarResult('l_longitude', 'l_longitude', 'float')
1199 ->addScalarResult('l_title', 'l_title', 'string')
1200 ->addScalarResult('l_description', 'l_description', 'string')
1201 ->addScalarResult('t_id', 't_id', 'integer')
1202 ->addScalarResult('t_title', 't_title', 'string')
1203 ->addScalarResult('a_id', 'a_id', 'integer')
1204 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
1205 ->addScalarResult('ad_name', 'ad_name', 'string')
1206 ->addScalarResult('ad_type', 'ad_type', 'string')
1207 ->addScalarResult('au_id', 'au_id', 'integer')
1208 ->addScalarResult('au_forename', 'au_forename', 'string')
1209 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
1210 ->addScalarResult('p_id', 'p_id', 'integer')
1211 ->addScalarResult('p_description', 'p_description', 'string')
1212 ->addScalarResult('p_class', 'p_class', 'string')
1213 ->addScalarResult('p_short', 'p_short', 'string')
1214 ->addScalarResult('p_hat', 'p_hat', 'integer')
1215 ->addScalarResult('p_rate', 'p_rate', 'integer')
1216 ->addScalarResult('p_contact', 'p_contact', 'string')
1217 ->addScalarResult('p_donate', 'p_donate', 'string')
1218 ->addScalarResult('p_link', 'p_link', 'string')
1219 ->addScalarResult('p_profile', 'p_profile', 'string')
1220 ->addIndexByScalar('id');
1224 ->createNativeQuery($req, $rsm)
1225 ->setParameter('begin', $period->getStartDate())
1226 ->setParameter('end', $period->getEndDate());
1229 return $res->getResult();
1233 * Find all session pending hourly weather
1235 * @return array The sessions to update
1237 public function findAllPendingHourlyWeather(): array {
1238 //Select all sessions starting and stopping in the next 3 days
1239 //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)
1241 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
1242 FROM Rapsys\AirBundle\Entity\Session AS s
1243 JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
1244 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))
1247 //Replace bundle entity name by table name
1248 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
1250 //Get result set mapping instance
1251 $rsm = new ResultSetMapping();
1253 //Declare all fields
1255 ->addEntityResult('Rapsys\AirBundle\Entity\Session', 's')
1256 ->addFieldResult('s', 'id', 'id')
1257 ->addFieldResult('s', 'date', 'date')
1258 ->addFieldResult('s', 'begin', 'begin')
1259 ->addFieldResult('s', 'length', 'length')
1260 ->addFieldResult('s', 'rainfall', 'rainfall')
1261 ->addFieldResult('s', 'rainrisk', 'rainrisk')
1262 ->addFieldResult('s', 'realfeel', 'realfeel')
1263 ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1264 ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1265 ->addFieldResult('s', 'temperature', 'temperature')
1266 ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1267 ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1268 ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Slot', 'o', 's', 'slot')
1269 ->addFieldResult('o', 'slot_id', 'id')
1270 ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Location', 'l', 's', 'location')
1271 ->addFieldResult('l', 'location_id', 'id')
1272 ->addFieldResult('l', 'zipcode', 'zipcode')
1273 ->addIndexBy('s', 'id');
1277 ->createNativeQuery($req, $rsm)
1282 * Find all session pending daily weather
1284 * @return array The sessions to update
1286 public function findAllPendingDailyWeather(): array {
1287 //Select all sessions stopping after next 3 days
1288 //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
1290 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
1291 FROM Rapsys\AirBundle\Entity\Session AS s
1292 JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
1293 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))
1296 //Replace bundle entity name by table name
1297 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
1299 //Get result set mapping instance
1300 $rsm = new ResultSetMapping();
1302 //Declare all fields
1304 ->addEntityResult('Rapsys\AirBundle\Entity\Session', 's')
1305 ->addFieldResult('s', 'id', 'id')
1306 ->addFieldResult('s', 'date', 'date')
1307 ->addFieldResult('s', 'begin', 'begin')
1308 ->addFieldResult('s', 'length', 'length')
1309 ->addFieldResult('s', 'rainfall', 'rainfall')
1310 ->addFieldResult('s', 'rainrisk', 'rainrisk')
1311 ->addFieldResult('s', 'realfeel', 'realfeel')
1312 ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1313 ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1314 ->addFieldResult('s', 'temperature', 'temperature')
1315 ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1316 ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1317 ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Slot', 'o', 's', 'slot')
1318 ->addFieldResult('o', 'slot_id', 'id')
1319 ->addJoinedEntityResult('Rapsys\AirBundle\Entity\Location', 'l', 's', 'location')
1320 ->addFieldResult('l', 'location_id', 'id')
1321 ->addFieldResult('l', 'zipcode', 'zipcode')
1322 ->addIndexBy('s', 'id');
1326 ->createNativeQuery($req, $rsm)
1331 * Find every session pending application
1333 * @return array The sessions to update
1335 public function findAllPendingApplication(): array {
1336 //Select all sessions not locked without application or canceled application within attribution period
1337 //XXX: DIFF(start, now) <= IF(DIFF(start, created) <= SENIOR_DELAY in DAY, DIFF(start, created) * 3 / 4, SENIOR_DELAY)
1338 //TODO: remonter les donnΓ©es pour le mail ?
1341 FROM Rapsys\AirBundle\Entity\Session as s
1342 LEFT JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.id = s.application_id AND a.canceled IS NULL)
1343 JOIN Rapsys\AirBundle\Entity\Application AS a2 ON (a2.session_id = s.id AND a2.canceled IS NULL)
1344 WHERE s.locked IS NULL AND s.application_id IS NULL AND
1345 (UNIX_TIMESTAMP(@dt_start := ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY)) - UNIX_TIMESTAMP()) <= IF(
1346 (@td_sc := UNIX_TIMESTAMP(@dt_start) - UNIX_TIMESTAMP(s.created)) <= :seniordelay,
1347 ROUND(@td_sc * :regulardelay / :seniordelay),
1351 ORDER BY @dt_start ASC, s.created ASC
1354 //Replace bundle entity name by table name
1355 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
1357 //Get result set mapping instance
1358 $rsm = new ResultSetMapping();
1360 //Declare all fields
1362 ->addEntityResult('Rapsys\AirBundle\Entity\Session', 's')
1363 ->addFieldResult('s', 'id', 'id')
1364 ->addIndexBy('s', 'id');
1368 ->createNativeQuery($req, $rsm)
1373 * Fetch session best application by session id
1375 * @param int $id The session id
1376 * @return ?Application The application or null
1378 public function findBestApplicationById(int $id): ?Application
{
1380 * Query session applications ranked by location score, global score, created and user_id
1382 * @xxx guest (or less) with application on location within 30 day are only considered within guestdelay
1384 * @xxx regular (or less) premium application on hotspot are only considered within regulardelay
1386 * @xxx senior (or less) with 5 or less session on location are only considered within seniordelay
1388 * @xxx senior (or less) with l_pn_ratio >= 1 are only considered within seniordelay
1390 * @xxx senior (or less) with l_tr_ratio >= (o_tr_ratio + 5) are only considered within seniordelay
1392 * @xxx only consider session within one year (may be unaccurate by the day with after session)
1394 * @xxx rainfall may not be accessible for previous session and other session at d-4 (only at d-2)
1396 * @todo ??? feedback the data to inform the rejected users ???
1399 SELECT e.id, e.l_score AS score
1411 MAX(ug.group_id) AS group_id,
1426 AVG(IF(a4.id IS NOT NULL AND s4.temperature IS NOT NULL AND s4.rainfall IS NOT NULL, s4.temperature/(1+s4.rainfall), NULL)) AS o_tr_ratio,
1443 SUM(IF(a3.id IS NOT NULL, 1/ABS(DATEDIFF(ADDDATE(b.date, INTERVAL IF(b.slot_id = :afterid, 1, 0) DAY), ADDDATE(s3.date, INTERVAL IF(s3.slot_id = :afterid, 1, 0) DAY))), 0)) AS g_score,
1456 COUNT(a2.id) AS l_count,
1457 SUM(IF(a2.id IS NOT NULL, 1/ABS(DATEDIFF(ADDDATE(s.date, INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), ADDDATE(s2.date, INTERVAL IF(s2.slot_id = :afterid, 1, 0) DAY))), 0)) AS l_score,
1458 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,
1459 (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,
1460 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,
1461 UNIX_TIMESTAMP(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY)) - UNIX_TIMESTAMP() AS remaining,
1465 FROM Rapsys\AirBundle\Entity\Session AS s
1466 JOIN Rapsys\AirBundle\Entity\Location AS l ON (l.id = s.location_id)
1467 JOIN Rapsys\AirBundle\Entity\Application AS a ON (a.session_id = s.id AND a.canceled IS NULL)
1468 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)
1469 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))
1475 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)
1476 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))
1481 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)
1482 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))
1487 LEFT JOIN Rapsys\AirBundle\Entity\UserGroup AS ug ON (ug.user_id = d.user_id)
1492 IF(e.group_id <= :guestid AND e.l_previous <= :guestwait, e.remaining <= :guestdelay, 1) AND
1493 IF(e.group_id <= :regularid AND e.premium = 1 AND e.hotspot = 1, e.remaining <= :regulardelay, 1) AND
1494 IF(e.group_id <= :seniorid AND e.l_count <= :scount, e.remaining <= :regulardelay, 1) AND
1495 IF(e.group_id <= :seniorid AND e.l_pn_ratio >= :pnratio, e.remaining <= :regulardelay, 1) AND
1496 IF(e.group_id <= :seniorid AND e.l_tr_ratio >= (e.o_tr_ratio + :trdiff), e.remaining <= :regulardelay, 1)
1497 ORDER BY e.l_score ASC, e.g_score ASC, e.created ASC, e.user_id ASC
1500 //Replace bundle entity name by table name
1501 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
1503 //Set update request
1504 $upreq = 'UPDATE Rapsys\AirBundle\Entity\Application SET score = :score, updated = NOW() WHERE id = :id';
1506 //Replace bundle entity name by table name
1507 $upreq = str_replace($this->tableKeys
, $this->tableValues
, $upreq);
1509 //Get result set mapping instance
1510 $rsm = new ResultSetMapping();
1512 //Declare all fields
1514 ->addEntityResult('Rapsys\AirBundle\Entity\Application', 'a')
1515 ->addFieldResult('a', 'id', 'id')
1516 ->addFieldResult('a', 'score', 'score')
1517 ->addIndexBy('a', 'id');
1520 //XXX: setting limit in subqueries is required to prevent mariadb optimisation
1521 $applications = $this->_em
1522 ->createNativeQuery($req, $rsm)
1523 ->setParameter('sid', $id)
1530 foreach($applications as $application) {
1531 //Check if we already saved best candidate
1532 if ($ret === null) {
1533 //Return first application
1534 $ret = $application;
1537 //Update application updated field
1538 //XXX: updated field is not modified for user with bad behaviour as application is not retrieved until delay is reached
1539 $this->_em
->getConnection()->executeUpdate($upreq, ['id' => $application->getId(), 'score' => $application->getScore()], ['id' => Types
::INTEGER, 'score' => Types
::FLOAT]);
1542 //Return best ranked application
1547 * Rekey sessions and applications by chronological session id
1549 * @return bool The rekey success or failure
1551 function rekey(): bool {
1553 $cnx = $this->_em
->getConnection();
1566 GROUP_CONCAT(sa.id ORDER BY sa.id SEPARATOR "\\n") AS sa_id
1567 FROM Rapsys\AirBundle\Entity\Session AS s
1568 LEFT JOIN Rapsys\AirBundle\Entity\Application AS sa ON (sa.session_id = s.id)
1572 ORDER BY ADDDATE(ADDTIME(a.date, a.begin), INTERVAL IF(a.slot_id = :afterid, 1, 0) DAY) ASC
1575 //Replace bundle entity name by table name
1576 $req = str_replace($this->tableKeys
, $this->tableValues
, $req);
1578 //Get result set mapping instance
1579 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1580 $rsm = new ResultSetMapping();
1582 //Declare all fields
1583 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1584 //addScalarResult($sqlColName, $resColName, $type = 'string');
1585 $rsm->addScalarResult('id', 'id', 'integer')
1586 ->addScalarResult('sa_id', 'sa_id', 'string');
1587 #->addIndexByScalar('id');
1590 $rnq = $this->_em
->createNativeQuery($req, $rsm);
1593 $res = $rnq->getResult();
1596 $cnx->beginTransaction();
1598 //Set update session request
1600 UPDATE Rapsys\AirBundle\Entity\Session
1601 SET id = :nid, updated = NOW()
1605 //Replace bundle entity name by table name
1606 $sreq = str_replace($this->tableKeys
, $this->tableValues
, $sreq);
1608 //Set update application request
1610 UPDATE Rapsys\AirBundle\Entity\Application
1611 SET session_id = :nid, updated = NOW()
1612 WHERE session_id = :id
1615 //Replace bundle entity name by table name
1616 $areq = str_replace($this->tableKeys
, $this->tableValues
, $areq);
1619 $max = max(array_keys($res));
1622 //Prepare session to update
1623 foreach($res as $id => $data) {
1625 $res[$id]['t_id'] = $max +
$id +
1;
1628 $res[$id]['n_id'] = $id +
1;
1630 //Explode application ids
1631 $res[$id]['sa_id'] = explode("\n", $data['sa_id']);
1634 if ($res[$id]['n_id'] == $res[$id]['id']) {
1635 //Remove unchanged session
1642 //Disable foreign key checks
1643 $cnx->prepare('SET foreign_key_checks = 0')->execute();
1646 foreach($res as $id => $data) {
1647 //Run session update
1648 $cnx->executeUpdate($sreq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
1650 //Run applications update
1651 $cnx->executeUpdate($areq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
1655 foreach($res as $id => $data) {
1656 //Run session update
1657 $cnx->executeUpdate($sreq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]);
1659 //Run applications update
1660 $cnx->executeUpdate($areq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]);
1663 //Restore foreign key checks
1664 $cnx->prepare('SET foreign_key_checks = 1')->execute();
1666 //Commit transaction
1669 //Set update auto_increment request
1671 ALTER TABLE Rapsys\AirBundle\Entity\Session
1675 //Replace bundle entity name by table name
1676 $ireq = str_replace($this->tableKeys
, $this->tableValues
, $ireq);
1678 //Reset auto_increment
1682 //Rollback transaction
1685 } catch(\Exception
$e) {
1686 //Rollback transaction