]> RaphaΓ«l G. Git Repositories - airbundle/blob - Repository/SessionRepository.php
Rapsys Git / airbundle / blob
? search:


d7cc5bef4ce531fd7a7665a4abd93c507fcae11e
[airbundle] / Repository / SessionRepository.php
1 <?php declare(strict_types=1);
2
3 /*
4 * This file is part of the Rapsys AirBundle package.
5 *
6 * (c) RaphaΓ«l Gertz <symfony@rapsys.eu>
7 *
8 * For the full copyright and license information, please view the LICENSE
9 * file that was distributed with this source code.
10 */
11
12 namespace Rapsys\AirBundle\Repository;
13
14 use Doctrine\DBAL\Types\Types;
15 use Doctrine\ORM\Query\ResultSetMapping;
16 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
17
18 use Rapsys\AirBundle\Entity\Application;
19 use Rapsys\AirBundle\Entity\Location;
20 use Rapsys\AirBundle\Entity\Session;
21 use Rapsys\AirBundle\Entity\Slot;
22
23 /**
24 * SessionRepository
25 */
26 class SessionRepository extends EntityRepository {
27 ///Set glyphs
28 //TODO: document utf-8 codes ?
29 //TODO: use unknown == ? symbol by default ???
30 //πŸ’ƒ<= dancer #0001f483
31 //πŸ’ƒ<= tanguera #0001f483
32 const GLYPHS = [
33 //Slots
34 'Morning' => 'πŸŒ…', #0001f305
35 'Afternoon' => 'β˜€οΈ', #2600
36 'Evening' => 'πŸŒ‡', #0001f307
37 'After' => '✨', #2728
38 //Weathers
39 'Cleary' => 'β˜€', #2600
40 'Sunny' => 'β›…', #26c5
41 'Cloudy' => '☁', #2601
42 'Winty' => '❄️', #2744
43 'Rainy' => 'πŸŒ‚', #0001f302
44 'Stormy' => 'β˜”', #2614
45 //Rate
46 'Euro' => '€', #20ac
47 'Free' => '🍺', #0001f37a
48 'Hat' => '🎩' #0001f3a9
49 ];
50
51 /**
52 * Find session as array by id
53 *
54 * @param int $id The session id
55 * @return array The session data
56 */
57 public function findOneByIdAsArray(int $id): ?array {
58 //Set the request
59 $req =<<<SQL
60 SELECT
61 s.id,
62 s.date,
63 s.begin,
64 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
65 s.length,
66 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
67 s.rainfall,
68 s.rainrisk,
69 s.realfeel,
70 s.realfeelmin,
71 s.realfeelmax,
72 s.temperature,
73 s.temperaturemin,
74 s.temperaturemax,
75 s.locked,
76 s.created,
77 s.updated,
78 s.location_id AS l_id,
79 l.title AS l_title,
80 l.description AS l_description,
81 l.address AS l_address,
82 l.zipcode AS l_zipcode,
83 l.city AS l_city,
84 l.latitude AS l_latitude,
85 l.longitude AS l_longitude,
86 l.indoor AS l_indoor,
87 l.updated AS l_updated,
88 s.slot_id AS t_id,
89 t.title AS t_title,
90 s.application_id AS a_id,
91 a.canceled AS a_canceled,
92 a.dance_id AS ad_id,
93 ad.name AS ad_name,
94 ad.type AS ad_type,
95 a.user_id AS au_id,
96 au.pseudonym AS au_pseudonym,
97 p.id AS p_id,
98 p.description AS p_description,
99 p.class AS p_class,
100 p.contact AS p_contact,
101 p.donate AS p_donate,
102 p.link AS p_link,
103 p.profile AS p_profile,
104 p.rate AS p_rate,
105 p.hat AS p_hat,
106 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,
107 GROUP_CONCAT(sa.id ORDER BY sa.user_id SEPARATOR "\\n") AS sa_id,
108 GROUP_CONCAT(IFNULL(sa.score, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_score,
109 GROUP_CONCAT(sa.created ORDER BY sa.user_id SEPARATOR "\\n") AS sa_created,
110 GROUP_CONCAT(sa.updated ORDER BY sa.user_id SEPARATOR "\\n") AS sa_updated,
111 GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled,
112 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
113 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
114 FROM RapsysAirBundle:Session AS s
115 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
116 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
117 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
118 LEFT JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
119 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
120 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
121 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
122 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
123 LEFT JOIN RapsysAirBundle:Dance AS sad ON (sad.id = sa.dance_id)
124 WHERE s.id = :id
125 GROUP BY s.id
126 ORDER BY NULL
127 SQL;
128
129 //Replace bundle entity name by table name
130 $req = str_replace($this->tableKeys, $this->tableValues, $req);
131
132 //Get result set mapping instance
133 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
134 $rsm = new ResultSetMapping();
135
136 //Declare all fields
137 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
138 $rsm->addScalarResult('id', 'id', 'integer')
139 ->addScalarResult('date', 'date', 'date')
140 ->addScalarResult('begin', 'begin', 'time')
141 ->addScalarResult('start', 'start', 'datetime')
142 ->addScalarResult('length', 'length', 'time')
143 ->addScalarResult('stop', 'stop', 'datetime')
144 ->addScalarResult('rainfall', 'rainfall', 'float')
145 ->addScalarResult('rainrisk', 'rainrisk', 'float')
146 ->addScalarResult('realfeel', 'realfeel', 'float')
147 ->addScalarResult('realfeelmin', 'realfeelmin', 'float')
148 ->addScalarResult('realfeelmax', 'realfeelmax', 'float')
149 ->addScalarResult('temperature', 'temperature', 'float')
150 ->addScalarResult('temperaturemin', 'temperaturemin', 'float')
151 ->addScalarResult('temperaturemax', 'temperaturemax', 'float')
152 ->addScalarResult('locked', 'locked', 'datetime')
153 ->addScalarResult('created', 'created', 'datetime')
154 ->addScalarResult('updated', 'updated', 'datetime')
155 ->addScalarResult('l_id', 'l_id', 'integer')
156 ->addScalarResult('l_title', 'l_title', 'string')
157 ->addScalarResult('l_description', 'l_description', 'string')
158 ->addScalarResult('l_address', 'l_address', 'string')
159 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
160 ->addScalarResult('l_city', 'l_city', 'string')
161 ->addScalarResult('l_latitude', 'l_latitude', 'float')
162 ->addScalarResult('l_longitude', 'l_longitude', 'float')
163 ->addScalarResult('l_indoor', 'l_indoor', 'boolean')
164 ->addScalarResult('l_updated', 'l_updated', 'datetime')
165 ->addScalarResult('t_id', 't_id', 'integer')
166 ->addScalarResult('t_title', 't_title', 'string')
167 ->addScalarResult('a_id', 'a_id', 'integer')
168 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
169 ->addScalarResult('ad_id', 'ad_id', 'integer')
170 ->addScalarResult('ad_name', 'ad_name', 'string')
171 ->addScalarResult('ad_type', 'ad_type', 'string')
172 ->addScalarResult('au_id', 'au_id', 'integer')
173 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
174 ->addScalarResult('p_id', 'p_id', 'integer')
175 ->addScalarResult('p_description', 'p_description', 'text')
176 ->addScalarResult('p_class', 'p_class', 'text')
177 ->addScalarResult('p_contact', 'p_contact', 'text')
178 ->addScalarResult('p_donate', 'p_donate', 'text')
179 ->addScalarResult('p_link', 'p_link', 'text')
180 ->addScalarResult('p_profile', 'p_profile', 'text')
181 ->addScalarResult('p_rate', 'p_rate', 'integer')
182 ->addScalarResult('p_hat', 'p_hat', 'boolean')
183 ->addScalarResult('modified', 'modified', 'datetime')
184 //XXX: is a string because of \n separator
185 ->addScalarResult('sa_id', 'sa_id', 'string')
186 //XXX: is a string because of \n separator
187 ->addScalarResult('sa_score', 'sa_score', 'string')
188 //XXX: is a string because of \n separator
189 ->addScalarResult('sa_created', 'sa_created', 'string')
190 //XXX: is a string because of \n separator
191 ->addScalarResult('sa_updated', 'sa_updated', 'string')
192 //XXX: is a string because of \n separator
193 ->addScalarResult('sa_canceled', 'sa_canceled', 'string')
194 //XXX: is a string because of \n separator
195 ->addScalarResult('sau_id', 'sau_id', 'string')
196 //XXX: is a string because of \n separator
197 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
198 ->addIndexByScalar('id');
199
200 //Set result
201 $result = $this->_em
202 ->createNativeQuery($req, $rsm)
203 ->setParameter('id', $id)
204 ->getOneOrNullResult();
205
206 //Without result
207 if ($result === null) {
208 //Return result
209 return $result;
210 }
211
212 //Set route
213 $route = 'rapsys_air_session_view';
214
215 //Set route params
216 $routeParams = ['id' => $id, 'location' => $this->slugger->slug($this->translator->trans($result['l_title']))];
217
218 //Set session
219 $session = [
220 'id' => $id,
221 'date' => $result['date'],
222 'begin' => $result['begin'],
223 'start' => $result['start'],
224 'length' => $result['length'],
225 'stop' => $result['stop'],
226 'rainfall' => $result['rainfall'] !== null ? $result['rainfall'].' mm' : $result['rainfall'],
227 'rainrisk' => $result['rainrisk'] !== null ? ($result['rainrisk']*100).' %' : $result['rainrisk'],
228 'realfeel' => $result['realfeel'] !== null ? $result['realfeel'].' Β°C' : $result['realfeel'],
229 'realfeelmin' => $result['realfeelmin'] !== null ? $result['realfeelmin'].' Β°C' : $result['realfeelmin'],
230 'realfeelmax' => $result['realfeelmax'] !== null ? $result['realfeelmax'].' Β°C' : $result['realfeelmax'],
231 'temperature' => $result['temperature'] !== null ? $result['temperature'].' Β°C' : $result['temperature'],
232 'temperaturemin' => $result['temperaturemin'] !== null ? $result['temperaturemin'].' Β°C' : $result['temperaturemin'],
233 'temperaturemax' => $result['temperaturemax'] !== null ? $result['temperaturemax'].' Β°C' : $result['temperaturemax'],
234 'locked' => $result['locked'],
235 'created' => $result['created'],
236 'updated' => $result['updated'],
237 'title' => $this->translator->trans('Session %id%', ['%id%' => $id]),
238 'modified' => $result['modified'],
239 'application' => null,
240 'location' => [
241 'id' => $result['l_id'],
242 'at' => $this->translator->trans('at '.$result['l_title']),
243 'title' => $locationTitle = $this->translator->trans($result['l_title']),
244 'description' => $this->translator->trans($result['l_description']??'None'),
245 'address' => $result['l_address'],
246 'zipcode' => $result['l_zipcode'],
247 'city' => $result['l_city'],
248 'in' => $this->translator->trans('in '.$result['l_city']),
249 'map' => $this->translator->trans($result['l_title'].' access map'),
250 'multimap' => $this->translator->trans($result['l_title'].' sector map'),
251 'latitude' => $result['l_latitude'],
252 'longitude' => $result['l_longitude'],
253 'indoor' => $result['l_indoor'],
254 'slug' => $routeParams['location'],
255 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $result['l_id'], 'location' => $routeParams['location']])
256 ],
257 'slot' => [
258 'id' => $result['t_id'],
259 'the' => $this->translator->trans('the '.lcfirst($result['t_title'])),
260 'title' => $this->translator->trans($result['t_title'])
261 ],
262 'snippet' => null,
263 'applications' => null
264 ];
265
266 //With application
267 if (!empty($result['a_id'])) {
268 $session['application'] = [
269 'dance' => [
270 'id' => $result['ad_id'],
271 'title' => $this->translator->trans($result['ad_name'].' '.lcfirst($result['ad_type'])),
272 'name' => $this->translator->trans($result['ad_name']),
273 'type' => $this->translator->trans($result['ad_type']),
274 'slug' => $routeParams['dance'] = $this->slugger->slug($this->translator->trans($result['ad_name'].' '.lcfirst($result['ad_type']))),
275 'link' => $this->router->generate('rapsys_air_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']))])
276 ],
277 'user' => [
278 'id' => $result['au_id'],
279 'by' => $this->translator->trans('by %pseudonym%', [ '%pseudonym%' => $result['au_pseudonym'] ]),
280 'title' => $result['au_pseudonym'],
281 'slug' => $routeParams['user'] = $this->slugger->slug($result['au_pseudonym']),
282 'link' => $result['au_id'] == 1 && $routeParams['user'] == 'milonga-raphael' ? $this->router->generate('rapsys_air_user_milongaraphael') : $this->router->generate('rapsys_air_user_view', ['id' => $result['au_id'], 'user' => $routeParams['user']]),
283 'contact' => $this->router->generate('rapsys_air_contact', ['id' => $result['au_id'], 'user' => $routeParams['user']])
284 ],
285 'id' => $result['a_id'],
286 'canceled' => $result['a_canceled']
287 ];
288 }
289
290 //With snippet
291 if (!empty($result['p_id'])) {
292 $session['snippet'] = [
293 'id' => $result['p_id'],
294 'description' => $result['p_description'],
295 'class' => $result['p_class'],
296 'contact' => $result['p_contact'],
297 'donate' => $result['p_donate'],
298 'link' => $result['p_link'],
299 'profile' => $result['p_profile'],
300 'rate' => $result['p_rate'],
301 'hat' => $result['p_hat']
302 ];
303 }
304
305 //With applications
306 if (!empty($result['sa_id'])) {
307 //Extract applications id
308 $result['sa_id'] = explode("\n", $result['sa_id']);
309 //Extract applications score
310 //XXX: score may be null before grant or for bad behaviour, replace NULL with 'NULL' to avoid silent drop in mysql
311 $result['sa_score'] = array_map(function($v){return $v==='NULL'?null:$v;}, explode("\n", $result['sa_score']));
312 //Extract applications created
313 $result['sa_created'] = array_map(function($v){return new \DateTime($v);}, explode("\n", $result['sa_created']));
314 //Extract applications updated
315 $result['sa_updated'] = array_map(function($v){return new \DateTime($v);}, explode("\n", $result['sa_updated']));
316 //Extract applications canceled
317 //XXX: canceled is null before cancelation, replace NULL with 'NULL' to avoid silent drop in mysql
318 $result['sa_canceled'] = array_map(function($v){return $v==='NULL'?null:new \DateTime($v);}, explode("\n", $result['sa_canceled']));
319
320 //Extract applications user id
321 $result['sau_id'] = explode("\n", $result['sau_id']);
322 //Extract applications user pseudonym
323 $result['sau_pseudonym'] = explode("\n", $result['sau_pseudonym']);
324
325 //Init applications
326 $session['applications'] = [];
327
328 //Iterate on each applications id
329 foreach($result['sa_id'] as $i => $sa_id) {
330 $session['applications'][$sa_id] = [
331 'user' => null,
332 'score' => $result['sa_score'][$i],
333 'created' => $result['sa_created'][$i],
334 'updated' => $result['sa_updated'][$i],
335 'canceled' => $result['sa_canceled'][$i]
336 ];
337 if (!empty($result['sau_id'][$i])) {
338 $session['applications'][$sa_id]['user'] = [
339 'id' => $result['sau_id'][$i],
340 'title' => $result['sau_pseudonym'][$i],
341 'slug' => $this->slugger->slug($result['sau_pseudonym'][$i])
342 ];
343 }
344 }
345 }
346
347 //Set link
348 $session['link'] = $this->router->generate($route, $routeParams);
349
350 //Set canonical
351 $session['canonical'] = $this->router->generate($route, $routeParams, UrlGeneratorInterface::ABSOLUTE_URL);
352
353 //Set alternates
354 $session['alternates'] = [];
355
356 //Iterate on each locales
357 foreach($this->translator->getFallbackLocales() as $fallback) {
358 //Set titles
359 $titles = [];
360
361 //Set route params location
362 $routeParams['location'] = $this->slugger->slug($this->translator->trans($result['l_title'], [], null, $fallback));
363
364 //With route params dance
365 if (!empty($routeParams['dance'])) {
366 $routeParams['dance'] = $this->slugger->slug($this->translator->trans($result['ad_name'].' '.lcfirst($result['ad_type']), [], null, $fallback));
367 }
368
369 //With route params user
370 if (!empty($routeParams['user'])) {
371 $routeParams['user'] = $this->slugger->slug($result['au_pseudonym']);
372 }
373
374 //With current locale
375 if ($fallback === $this->locale) {
376 //Set current locale title
377 $titles[$this->locale] = $this->translator->trans($this->languages[$this->locale]);
378 //Without current locale
379 } else {
380 //Iterate on other locales
381 foreach(array_diff($this->translator->getFallbackLocales(), [$fallback]) as $other) {
382 //Set other locale title
383 $titles[$other] = $this->translator->trans($this->languages[$fallback], [], null, $other);
384 }
385
386 //Add alternates locale
387 $session['alternates'][str_replace('_', '-', $fallback)] = [
388 'absolute' => $this->router->generate($route, ['_locale' => $fallback]+$routeParams, UrlGeneratorInterface::ABSOLUTE_URL),
389 'relative' => $this->router->generate($route, ['_locale' => $fallback]+$routeParams),
390 'title' => implode('/', $titles),
391 'translated' => $this->translator->trans($this->languages[$fallback], [], null, $fallback)
392 ];
393 }
394
395 //Add alternates shorter locale
396 if (empty($parameters['alternates'][$shortFallback = substr($fallback, 0, 2)])) {
397 //Set locale locales context
398 $session['alternates'][$shortFallback] = [
399 'absolute' => $this->router->generate($route, ['_locale' => $fallback]+$routeParams, UrlGeneratorInterface::ABSOLUTE_URL),
400 'relative' => $this->router->generate($route, ['_locale' => $fallback]+$routeParams),
401 'title' => implode('/', $titles),
402 'translated' => $this->translator->trans($this->languages[$fallback], [], null, $fallback)
403 ];
404 }
405 }
406
407 //Return session
408 return $session;
409 }
410
411 /**
412 * Find sessions as calendar array by date period
413 *
414 * @param DatePeriod $period The date period
415 * @param ?bool $granted The session is granted
416 * @param ?float $latitude The latitude
417 * @param ?float $longitude The longitude
418 * @param ?int $userId The user id
419 * @return array The session data
420 */
421 public function findAllByPeriodAsCalendarArray(\DatePeriod $period, ?bool $granted = null, ?float $latitude = null, ?float $longitude = null, ?int $userId = null): array {
422 //Init granted sql
423 $grantSql = '';
424
425 //When granted is set
426 if (empty($granted)) {
427 //Set application and user as optional
428 $grantSql = 'LEFT ';
429 }
430
431 //Init location sql
432 $locationSql = '';
433
434 //When latitude and longitude
435 if ($latitude !== null && $longitude !== null) {
436 //Set the request
437 //XXX: get every location between 0 and 15 km of latitude and longitude
438 $req = <<<SQL
439 SELECT l.id
440 FROM RapsysAirBundle:Location AS l
441 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
442 SQL;
443
444 //Replace bundle entity name by table name
445 $req = str_replace($this->tableKeys, $this->tableValues, $req);
446
447 //Get result set mapping instance
448 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
449 $rsm = new ResultSetMapping();
450
451 //Declare all fields
452 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
453 //addScalarResult($sqlColName, $resColName, $type = 'string');
454 $rsm->addScalarResult('id', 'id', 'integer')
455 ->addIndexByScalar('id');
456
457 //Set location ids
458 //XXX: check that latitude and longitude have not be swapped !!!
459 //XXX: latitude ~= 48.x longitude ~= 2.x
460 $locationIds = array_keys(
461 $this->_em
462 ->createNativeQuery($req, $rsm)
463 ->setParameter('latitude', $latitude)
464 ->setParameter('longitude', $longitude)
465 ->getArrayResult()
466 );
467
468 //Add location id clause
469 $locationSql = "\n\t".'AND s.location_id IN (:lids)';
470 //When user id
471 } elseif ($userId !== null) {
472 //Set the request
473 //XXX: get every location between 0 and 15 km
474 $req = <<<SQL
475 SELECT l2.id
476 FROM (
477 SELECT l.id, l.latitude, l.longitude
478 FROM RapsysAirBundle:Application AS a
479 JOIN RapsysAirBundle:Session AS s ON (s.id = a.session_id)
480 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
481 WHERE a.user_id = :id
482 GROUP BY l.id
483 ORDER BY NULL
484 LIMIT 0, :limit
485 ) AS a
486 JOIN RapsysAirBundle:Location AS l2
487 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
488 GROUP BY l2.id
489 ORDER BY NULL
490 SQL;
491
492 //Replace bundle entity name by table name
493 $req = str_replace($this->tableKeys, $this->tableValues, $req);
494
495 //Get result set mapping instance
496 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
497 $rsm = new ResultSetMapping();
498
499 //Declare all fields
500 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
501 //addScalarResult($sqlColName, $resColName, $type = 'string');
502 $rsm->addScalarResult('id', 'id', 'integer')
503 ->addIndexByScalar('id');
504
505 //Set location ids
506 $locationIds = array_keys(
507 $this->_em
508 ->createNativeQuery($req, $rsm)
509 ->setParameter('id', $userId)
510 ->getArrayResult()
511 );
512
513 //With location ids
514 if (!empty($locationIds)) {
515 //Add location id clause
516 $locationSql = "\n\t".'AND s.location_id IN (:lids)';
517 }
518 }
519
520 //Set the request
521 $req = <<<SQL
522
523 SELECT
524 s.id,
525 s.date,
526 s.rainrisk,
527 s.rainfall,
528 s.realfeel,
529 s.temperature,
530 s.locked,
531 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
532 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
533 s.location_id AS l_id,
534 l.title AS l_title,
535 l.address AS l_address,
536 l.zipcode AS l_zipcode,
537 l.city AS l_city,
538 l.latitude AS l_latitude,
539 l.longitude AS l_longitude,
540 l.indoor AS l_indoor,
541 s.slot_id AS t_id,
542 t.title AS t_title,
543 s.application_id AS a_id,
544 a.canceled AS a_canceled,
545 a.dance_id AS ad_id,
546 ad.name AS ad_name,
547 ad.type AS ad_type,
548 a.user_id AS au_id,
549 au.pseudonym AS au_pseudonym,
550 p.hat AS p_hat,
551 p.rate AS p_rate,
552 p.short AS p_short,
553 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
554 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym,
555 GROUP_CONCAT(sa.dance_id ORDER BY sa.user_id SEPARATOR "\\n") AS sad_id,
556 GROUP_CONCAT(sad.name ORDER BY sa.user_id SEPARATOR "\\n") AS sad_name,
557 GROUP_CONCAT(sad.type ORDER BY sa.user_id SEPARATOR "\\n") AS sad_type,
558 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
559 FROM RapsysAirBundle:Session AS s
560 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
561 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
562 {$grantSql}JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
563 {$grantSql}JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
564 {$grantSql}JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
565 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
566 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
567 LEFT JOIN RapsysAirBundle:Dance AS sad ON (sad.id = sa.dance_id)
568 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
569 WHERE s.date BETWEEN :begin AND :end{$locationSql}
570 GROUP BY s.id
571 ORDER BY NULL
572 SQL;
573
574 //Replace bundle entity name by table name
575 $req = str_replace($this->tableKeys, $this->tableValues, $req);
576
577 //Get result set mapping instance
578 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
579 $rsm = new ResultSetMapping();
580
581 //Declare all fields
582 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
583 //addScalarResult($sqlColName, $resColName, $type = 'string');
584 $rsm->addScalarResult('id', 'id', 'integer')
585 ->addScalarResult('date', 'date', 'date')
586 ->addScalarResult('rainrisk', 'rainrisk', 'float')
587 ->addScalarResult('rainfall', 'rainfall', 'float')
588 ->addScalarResult('realfeel', 'realfeel', 'float')
589 ->addScalarResult('temperature', 'temperature', 'float')
590 ->addScalarResult('locked', 'locked', 'datetime')
591 ->addScalarResult('start', 'start', 'datetime')
592 ->addScalarResult('stop', 'stop', 'datetime')
593 ->addScalarResult('modified', 'modified', 'datetime')
594 ->addScalarResult('t_id', 't_id', 'integer')
595 ->addScalarResult('t_title', 't_title', 'string')
596 ->addScalarResult('l_id', 'l_id', 'integer')
597 ->addScalarResult('l_title', 'l_title', 'string')
598 ->addScalarResult('l_address', 'l_address', 'string')
599 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
600 ->addScalarResult('l_city', 'l_city', 'string')
601 ->addScalarResult('l_latitude', 'l_latitude', 'float')
602 ->addScalarResult('l_longitude', 'l_longitude', 'float')
603 ->addScalarResult('l_indoor', 'l_indoor', 'boolean')
604 ->addScalarResult('a_id', 'a_id', 'integer')
605 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
606 ->addScalarResult('ad_id', 'ad_id', 'string')
607 ->addScalarResult('ad_name', 'ad_name', 'string')
608 ->addScalarResult('ad_type', 'ad_type', 'string')
609 ->addScalarResult('au_id', 'au_id', 'integer')
610 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
611 ->addScalarResult('p_hat', 'p_hat', 'boolean')
612 ->addScalarResult('p_rate', 'p_rate', 'integer')
613 ->addScalarResult('p_short', 'p_short', 'string')
614 //XXX: is a string because of \n separator
615 ->addScalarResult('sau_id', 'sau_id', 'string')
616 //XXX: is a string because of \n separator
617 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
618 //XXX: is a string because of \n separator
619 ->addScalarResult('sad_id', 'sad_id', 'string')
620 //XXX: is a string because of \n separator
621 ->addScalarResult('sad_name', 'sad_name', 'string')
622 //XXX: is a string because of \n separator
623 ->addScalarResult('sad_type', 'sad_type', 'string')
624 ->addIndexByScalar('id');
625
626 //Fetch result
627 $res = $this->_em
628 ->createNativeQuery($req, $rsm)
629 ->setParameter('begin', $period->getStartDate())
630 ->setParameter('end', $period->getEndDate());
631
632 //Add optional location ids
633 if (!empty($locationIds)) {
634 $res->setParameter('lids', $locationIds);
635 }
636
637 //Get result
638 $result = $res->getResult();
639
640 //Init calendar
641 $calendar = [];
642
643 //Init month
644 $month = null;
645
646 //Set route
647 $route = 'rapsys_air_session_view';
648
649 //Iterate on each day
650 foreach($period as $date) {
651 //Init day in calendar
652 $calendar[$Ymd = $date->format('Ymd')] = [
653 'title' => $this->translator->trans($date->format('l')).' '.$date->format('d'),
654 'modified' => null,
655 'class' => [],
656 'sessions' => []
657 ];
658
659 //Detect month change
660 if ($month != $date->format('m')) {
661 $month = $date->format('m');
662 //Append month for first day of month
663 //XXX: except if today to avoid double add
664 if ($date->format('U') != strtotime('today')) {
665 $calendar[$Ymd]['title'] .= '/'.$month;
666 }
667 }
668 //Deal with today
669 if ($date->format('U') == ($today = strtotime('today'))) {
670 $calendar[$Ymd]['title'] .= '/'.$month;
671 $calendar[$Ymd]['current'] = true;
672 $calendar[$Ymd]['class'][] = 'current';
673 }
674 //Disable passed days
675 if ($date->format('U') < $today) {
676 $calendar[$Ymd]['disabled'] = true;
677 $calendar[$Ymd]['class'][] = 'disabled';
678 }
679 //Set next month days
680 if ($date->format('m') > date('m')) {
681 $calendar[$Ymd]['next'] = true;
682 #$calendar[$Ymd]['class'][] = 'next';
683 }
684
685 //Detect sunday
686 if ($date->format('w') == 0) {
687 $calendar[$Ymd]['class'][] = 'sunday';
688 }
689
690 //Iterate on each session to find the one of the day
691 foreach($result as $session) {
692 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
693 //With empty or greatest modified
694 if ($calendar[$Ymd]['modified'] === null || $session['modified'] >= $calendar[$Ymd]['modified']) {
695 //Update modified
696 $calendar[$Ymd]['modified'] = $session['modified'];
697 }
698
699 //Set applications
700 $applications = array_combine($candidates = explode("\n", $session['sau_id']), explode("\n", $session['sau_pseudonym']));
701
702 //Compute classes
703 $class = [];
704
705 //With locked
706 if (!empty($session['locked'])) {
707 $class[] = 'locked';
708 //Without locked
709 } else {
710 //With application
711 if (!empty($session['a_id'])) {
712 //With canceled session
713 if (!empty($session['a_canceled'])) {
714 $class[] = 'canceled';
715 //With disputed session
716 } elseif ($userId !== null && $session['au_id'] != $userId && !empty($candidates[$userId])) {
717 $class[] = 'disputed';
718 //Session is granted
719 } else {
720 $class[] = 'granted';
721 }
722
723 //With user id
724 if ($userId !== null && $session['au_id'] == $userId) {
725 $class[] = 'highlight';
726 }
727 } else {
728 $class[] = 'pending';
729 }
730
731 //With latitude and longitude
732 if ($latitude !== null && $longitude !== null && $session['l_latitude'] == $latitude && $session['l_longitude'] == $longitude) {
733 $class[] = 'highlight';
734 }
735 }
736
737 //Set temperature
738 $temperature = [
739 'glyph' => self::GLYPHS['Cleary'],
740 'title' => []
741 ];
742
743 //Compute temperature glyph
744 //XXX: temperature may be null
745 if ($session['temperature'] >= 17 && $session['temperature'] < 24) {
746 $temperature['glyph'] = self::GLYPHS['Sunny'];
747 } elseif ($session['temperature'] >= 10 && $session['temperature'] < 17) {
748 $temperature['glyph'] = self::GLYPHS['Cloudy'];
749 } elseif ($session['temperature'] !== null && $session['temperature'] < 10) {
750 $temperature['glyph'] = self::GLYPHS['Winty'];
751 }
752
753 //Check if temperature is available
754 if ($session['temperature'] !== null) {
755 $temperature['title'][] = $session['temperature'].'Β°C';
756 }
757
758 //Check if realfeel is available
759 if ($session['realfeel'] !== null) {
760 $temperature['title'][] = $session['realfeel'].'Β°R';
761 }
762
763 //Compute temperature title
764 $temperature['title'] = implode(' ', $temperature['title']);
765
766 //Set rain
767 $rain = [
768 'glyph' => self::GLYPHS['Cleary'],
769 'title' => []
770 ];
771
772 //Compute rain glyph
773 //XXX: rainfall and rainrisk may be null
774 if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
775 $rain['glyph'] = self::GLYPHS['Stormy'];
776 } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
777 $rain['glyph'] = self::GLYPHS['Rainy'];
778 }
779
780 //Check if rainrisk is available
781 if ($session['rainrisk'] !== null) {
782 $rain['title'][] = ($session['rainrisk']*100).'%';
783 }
784
785 //Check if rainfall is available
786 if ($session['rainfall'] !== null) {
787 $rain['title'][] = $session['rainfall'].'mm';
788 }
789
790 //Compute rain title
791 $rain['title'] = implode(' ', $rain['title']);
792
793 //Set application
794 $application = null;
795
796 //Set rate
797 $rate = null;
798
799 //Set route params
800 $routeParams = ['id' => $session['id'], 'location' => $this->slugger->slug($this->translator->trans($session['l_title']))];
801
802 //With application
803 if (!empty($session['a_id'])) {
804 //Set dance
805 $routeParams['dance'] = $this->slugger->slug($dance = $this->translator->trans($session['ad_name'].' '.lcfirst($session['ad_type'])));
806
807 //Set user
808 $routeParams['user'] = $this->slugger->slug($session['au_pseudonym']);
809
810 //Set title
811 $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'])]);
812
813 //Set pseudonym
814 $application = [
815 'dance' => [
816 'id' => $session['ad_id'],
817 'name' => $this->translator->trans($session['ad_name']),
818 'type' => $this->translator->trans($session['ad_type']),
819 'title' => $dance
820 ],
821 'user' => [
822 'id' => $session['au_id'],
823 'title' => $session['au_pseudonym']
824 ]
825 ];
826
827 //Set rate
828 $rate = [
829 'glyph' => self::GLYPHS['Free'],
830 'rate' => null,
831 'short' => $session['p_short'],
832 'title' => $this->translator->trans('Free')
833 ];
834
835 //With hat
836 if (!empty($session['p_hat'])) {
837 //Set glyph
838 $rate['glyph'] = self::GLYPHS['Hat'];
839
840 //With rate
841 if (!empty($session['p_rate'])) {
842 //Set rate
843 $rate['rate'] = $session['p_rate'];
844
845 //Set title
846 $rate['title'] = $this->translator->trans('%rate%€ to the hat', ['%rate%' => $session['p_rate']]);
847 //Without rate
848 } else {
849 //Set title
850 $rate['title'] = $this->translator->trans('To the hat');
851 }
852 //With rate
853 } elseif (!empty($session['p_rate'])) {
854 //Set glyph
855 $rate['glyph'] = self::GLYPHS['Euro'];
856
857 //Set rate
858 $rate['rate'] = $session['p_rate'];
859
860 //Set title
861 $rate['title'] = $session['p_rate'].' €';
862 }
863 //With unique application
864 } elseif (count($applications) == 1) {
865 //Set dance
866 $dance = $this->translator->trans($session['sad_name'].' '.lcfirst($session['sad_type']));
867
868 //Set title
869 $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'])]);
870
871 //Set pseudonym
872 $application = [
873 'dance' => [
874 'id' => $session['sad_id'],
875 'name' => $this->translator->trans($session['sad_name']),
876 'type' => $this->translator->trans($session['sad_type']),
877 'title' => $dance
878 ],
879 'user' => [
880 'id' => $session['sau_id'],
881 'title' => $session['sau_pseudonym']
882 ]
883 ];
884
885 //TODO: glyph stuff ???
886 //Without application
887 } else {
888 //Set title
889 $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'])]);
890 }
891
892 //Add the session
893 $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%05d', $session['id'])] = [
894 'id' => $session['id'],
895 'start' => $session['start'],
896 'stop' => $session['stop'],
897 'class' => $class,
898 'temperature' => $temperature,
899 'rain' => $rain,
900 'title' => $title,
901 'link' => $this->router->generate($route, $routeParams),
902 'location' => [
903 'id' => $session['l_id'],
904 'title' => $this->translator->trans($session['l_title']),
905 'address' => $session['l_address'],
906 'latitude' => $session['l_latitude'],
907 'longitude' => $session['l_longitude'],
908 'indoor' => $session['l_indoor'],
909 'at' => $at = $this->translator->trans('at '.$session['l_title']),
910 'in' => $in = $this->translator->trans('in '.$session['l_city']),
911 'atin' => $at.' '.$in,
912 'city' => $session['l_city'],
913 'zipcode' => $session['l_zipcode']
914 ],
915 'application' => $application,
916 'slot' => [
917 'glyph' => self::GLYPHS[$session['t_title']],
918 'title' => $this->translator->trans($session['t_title'])
919 ],
920 'rate' => $rate,
921 'modified' => $session['modified'],
922 'applications' => $applications
923 ];
924 }
925 }
926
927 //Sort sessions
928 ksort($calendar[$Ymd]['sessions']);
929 }
930
931 //Send result
932 return $calendar;
933 }
934
935 /**
936 * Find session by location, slot and date
937 *
938 * @param Location $location The location
939 * @param Slot $slot The slot
940 * @param DateTime $date The datetime
941 * @return ?Session The found session
942 */
943 public function findOneByLocationSlotDate(Location $location, Slot $slot, \DateTime $date): ?Session {
944 //Return sessions
945 return $this->getEntityManager()
946 ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.slot = :slot AND s.date = :date)')
947 ->setParameter('location', $location)
948 ->setParameter('slot', $slot)
949 ->setParameter('date', $date)
950 ->getSingleResult();
951 }
952
953 /**
954 * Fetch sessions by date period
955 *
956 * @XXX: used in calendar command
957 *
958 * @param DatePeriod $period The date period
959 * @return array The session array
960 */
961 public function fetchAllByDatePeriod(\DatePeriod $period): array {
962 //Set the request
963 //TODO: exclude opera and others ?
964 $req = <<<SQL
965 SELECT
966 s.id,
967 s.date,
968 s.locked,
969 s.updated,
970 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
971 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
972 s.location_id AS l_id,
973 l.address AS l_address,
974 l.zipcode AS l_zipcode,
975 l.city AS l_city,
976 l.title AS l_title,
977 l.description AS l_description,
978 l.latitude AS l_latitude,
979 l.longitude AS l_longitude,
980 s.application_id AS a_id,
981 a.canceled AS a_canceled,
982 ad.name AS ad_name,
983 ad.type AS ad_type,
984 a.user_id AS au_id,
985 au.forename AS au_forename,
986 au.pseudonym AS au_pseudonym,
987 p.id AS p_id,
988 p.description AS p_description,
989 p.class AS p_class,
990 p.short AS p_short,
991 p.hat AS p_hat,
992 p.rate AS p_rate,
993 p.contact AS p_contact,
994 p.donate AS p_donate,
995 p.link AS p_link,
996 p.profile AS p_profile
997 FROM RapsysAirBundle:Session AS s
998 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
999 JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
1000 JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
1001 JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
1002 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
1003 WHERE s.date BETWEEN :begin AND :end
1004 ORDER BY NULL
1005 SQL;
1006
1007 //Replace bundle entity name by table name
1008 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1009
1010 //Get result set mapping instance
1011 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1012 $rsm = new ResultSetMapping();
1013
1014 //Declare all fields
1015 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1016 //addScalarResult($sqlColName, $resColName, $type = 'string');
1017 $rsm->addScalarResult('id', 'id', 'integer')
1018 ->addScalarResult('date', 'date', 'date')
1019 ->addScalarResult('locked', 'locked', 'datetime')
1020 ->addScalarResult('updated', 'updated', 'datetime')
1021 ->addScalarResult('start', 'start', 'datetime')
1022 ->addScalarResult('stop', 'stop', 'datetime')
1023 ->addScalarResult('l_id', 'l_id', 'integer')
1024 ->addScalarResult('l_address', 'l_address', 'string')
1025 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
1026 ->addScalarResult('l_city', 'l_city', 'string')
1027 ->addScalarResult('l_latitude', 'l_latitude', 'float')
1028 ->addScalarResult('l_longitude', 'l_longitude', 'float')
1029 ->addScalarResult('l_title', 'l_title', 'string')
1030 ->addScalarResult('l_description', 'l_description', 'string')
1031 ->addScalarResult('t_id', 't_id', 'integer')
1032 ->addScalarResult('t_title', 't_title', 'string')
1033 ->addScalarResult('a_id', 'a_id', 'integer')
1034 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
1035 ->addScalarResult('ad_name', 'ad_name', 'string')
1036 ->addScalarResult('ad_type', 'ad_type', 'string')
1037 ->addScalarResult('au_id', 'au_id', 'integer')
1038 ->addScalarResult('au_forename', 'au_forename', 'string')
1039 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
1040 ->addScalarResult('p_id', 'p_id', 'integer')
1041 ->addScalarResult('p_description', 'p_description', 'string')
1042 ->addScalarResult('p_class', 'p_class', 'string')
1043 ->addScalarResult('p_short', 'p_short', 'string')
1044 ->addScalarResult('p_hat', 'p_hat', 'integer')
1045 ->addScalarResult('p_rate', 'p_rate', 'integer')
1046 ->addScalarResult('p_contact', 'p_contact', 'string')
1047 ->addScalarResult('p_donate', 'p_donate', 'string')
1048 ->addScalarResult('p_link', 'p_link', 'string')
1049 ->addScalarResult('p_profile', 'p_profile', 'string')
1050 ->addIndexByScalar('id');
1051
1052 //Fetch result
1053 $res = $this->_em
1054 ->createNativeQuery($req, $rsm)
1055 ->setParameter('begin', $period->getStartDate())
1056 ->setParameter('end', $period->getEndDate());
1057
1058 //Return result
1059 return $res->getResult();
1060 }
1061
1062 /**
1063 * Fetch sessions calendar with translated location by date period and user
1064 *
1065 * @param DatePeriod $period The date period
1066 * @param ?int $userId The user id
1067 * @param ?int $sessionId The session id
1068 */
1069 public function fetchUserCalendarByDatePeriod(\DatePeriod $period, ?int $userId = null, ?int $sessionId = null): array {
1070 //Init user sql
1071 $userJoinSql = $userWhereSql = '';
1072
1073 //When user id is set
1074 if (!empty($userId)) {
1075 //Add user join
1076 $userJoinSql = 'JOIN RapsysAirBundle:Application AS sua ON (sua.session_id = s.id)'."\n";
1077 //Add user id clause
1078 $userWhereSql = "\n\t".'AND sua.user_id = :uid';
1079 }
1080
1081 //Set the request
1082 //TODO: change as_u_* in sau_*, a_u_* in au_*, etc, see request up
1083 $req = <<<SQL
1084 SELECT
1085 s.id,
1086 s.date,
1087 s.rainrisk,
1088 s.rainfall,
1089 s.realfeel,
1090 s.temperature,
1091 s.locked,
1092 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
1093 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
1094 s.location_id AS l_id,
1095 l.title AS l_title,
1096 s.slot_id AS t_id,
1097 t.title AS t_title,
1098 s.application_id AS a_id,
1099 ad.name AS ad_name,
1100 ad.type AS ad_type,
1101 a.user_id AS au_id,
1102 au.pseudonym AS au_pseudonym,
1103 p.rate AS p_rate,
1104 p.hat AS p_hat,
1105 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
1106 GROUP_CONCAT(CONCAT("- ", sau.pseudonym) ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
1107 FROM RapsysAirBundle:Session AS s
1108 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1109 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
1110 {$userJoinSql}LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
1111 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
1112 LEFT JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
1113 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
1114 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
1115 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
1116 WHERE s.date BETWEEN :begin AND :end{$userWhereSql}
1117 GROUP BY s.id
1118 ORDER BY NULL
1119 SQL;
1120
1121 //Replace bundle entity name by table name
1122 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1123
1124 //Get result set mapping instance
1125 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1126 $rsm = new ResultSetMapping();
1127
1128 //Declare all fields
1129 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1130 //addScalarResult($sqlColName, $resColName, $type = 'string');
1131 $rsm->addScalarResult('id', 'id', 'integer')
1132 ->addScalarResult('date', 'date', 'date')
1133 ->addScalarResult('rainrisk', 'rainrisk', 'float')
1134 ->addScalarResult('rainfall', 'rainfall', 'float')
1135 ->addScalarResult('realfeel', 'realfeel', 'float')
1136 ->addScalarResult('temperature', 'temperature', 'float')
1137 ->addScalarResult('locked', 'locked', 'datetime')
1138 ->addScalarResult('start', 'start', 'datetime')
1139 ->addScalarResult('stop', 'stop', 'datetime')
1140 ->addScalarResult('t_id', 't_id', 'integer')
1141 ->addScalarResult('t_title', 't_title', 'string')
1142 ->addScalarResult('l_id', 'l_id', 'integer')
1143 ->addScalarResult('l_title', 'l_title', 'string')
1144 ->addScalarResult('a_id', 'a_id', 'integer')
1145 ->addScalarResult('ad_name', 'ad_name', 'string')
1146 ->addScalarResult('ad_type', 'ad_type', 'string')
1147 ->addScalarResult('au_id', 'au_id', 'integer')
1148 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
1149 ->addScalarResult('p_rate', 'p_rate', 'integer')
1150 ->addScalarResult('p_hat', 'p_hat', 'boolean')
1151 //XXX: is a string because of \n separator
1152 ->addScalarResult('sau_id', 'sau_id', 'string')
1153 //XXX: is a string because of \n separator
1154 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
1155 ->addIndexByScalar('id');
1156
1157 //Fetch result
1158 $res = $this->_em
1159 ->createNativeQuery($req, $rsm)
1160 ->setParameter('begin', $period->getStartDate())
1161 ->setParameter('end', $period->getEndDate())
1162 ->setParameter('uid', $userId)
1163 ->getResult();
1164
1165 //Init calendar
1166 $calendar = [];
1167
1168 //Init month
1169 $month = null;
1170
1171 //Iterate on each day
1172 foreach($period as $date) {
1173 //Init day in calendar
1174 $calendar[$Ymd = $date->format('Ymd')] = [
1175 'title' => $this->translator->trans($date->format('l')).' '.$date->format('d'),
1176 'class' => [],
1177 'sessions' => []
1178 ];
1179
1180 //Detect month change
1181 if ($month != $date->format('m')) {
1182 $month = $date->format('m');
1183 //Append month for first day of month
1184 //XXX: except if today to avoid double add
1185 if ($date->format('U') != strtotime('today')) {
1186 $calendar[$Ymd]['title'] .= '/'.$month;
1187 }
1188 }
1189 //Deal with today
1190 if ($date->format('U') == ($today = strtotime('today'))) {
1191 $calendar[$Ymd]['title'] .= '/'.$month;
1192 $calendar[$Ymd]['current'] = true;
1193 $calendar[$Ymd]['class'][] = 'current';
1194 }
1195 //Disable passed days
1196 if ($date->format('U') < $today) {
1197 $calendar[$Ymd]['disabled'] = true;
1198 $calendar[$Ymd]['class'][] = 'disabled';
1199 }
1200 //Set next month days
1201 if ($date->format('m') > date('m')) {
1202 $calendar[$Ymd]['next'] = true;
1203 #$calendar[$Ymd]['class'][] = 'next';
1204 }
1205
1206 //Detect sunday
1207 if ($date->format('w') == 0) {
1208 $calendar[$Ymd]['class'][] = 'sunday';
1209 }
1210
1211 //Iterate on each session to find the one of the day
1212 foreach($res as $session) {
1213 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
1214 //Count number of application
1215 $count = count(explode("\n", $session['sau_id']));
1216
1217 //Compute classes
1218 $class = [];
1219 if (!empty($session['a_id'])) {
1220 $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
1221 if ($session['au_id'] == $userId) {
1222 $class[] = 'granted';
1223 } else {
1224 $class[] = 'disputed';
1225 }
1226 } elseif ($count > 1) {
1227 $class[] = 'disputed';
1228 } elseif (!empty($session['locked'])) {
1229 $class[] = 'locked';
1230 } else {
1231 $class[] = 'pending';
1232 }
1233
1234 if ($sessionId == $session['id']) {
1235 $class[] = 'highlight';
1236 }
1237
1238 //Set temperature
1239 //XXX: realfeel may be null, temperature should not
1240 $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
1241
1242 //Compute weather
1243 //XXX: rainfall may be null
1244 if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
1245 $weather = self::GLYPHS['Stormy'];
1246 } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
1247 $weather = self::GLYPHS['Rainy'];
1248 } elseif ($temperature > 24) {
1249 $weather = self::GLYPHS['Cleary'];
1250 } elseif ($temperature > 17) {
1251 $weather = self::GLYPHS['Sunny'];
1252 } elseif ($temperature > 10) {
1253 $weather = self::GLYPHS['Cloudy'];
1254 } elseif ($temperature !== null) {
1255 $weather = self::GLYPHS['Winty'];
1256 } else {
1257 $weather = null;
1258 }
1259
1260 //Init weathertitle
1261 $weathertitle = [];
1262
1263 //Check if realfeel is available
1264 if ($session['realfeel'] !== null) {
1265 $weathertitle[] = $session['realfeel'].'Β°R';
1266 }
1267
1268 //Check if temperature is available
1269 if ($session['temperature'] !== null) {
1270 $weathertitle[] = $session['temperature'].'Β°C';
1271 }
1272
1273 //Check if rainrisk is available
1274 if ($session['rainrisk'] !== null) {
1275 $weathertitle[] = ($session['rainrisk']*100).'%';
1276 }
1277
1278 //Check if rainfall is available
1279 if ($session['rainfall'] !== null) {
1280 $weathertitle[] = $session['rainfall'].'mm';
1281 }
1282
1283 //Set applications
1284 $applications = [
1285 0 => $this->translator->trans($session['t_title']).' '.$this->translator->trans('at '.$session['l_title']).$this->translator->trans(':')
1286 ];
1287
1288 //Fetch pseudonyms from session applications
1289 $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
1290
1291 //Set dance
1292 $dance = null;
1293
1294 //Set pseudonym
1295 $pseudonym = null;
1296
1297 //Check that session is not granted
1298 if (empty($session['a_id'])) {
1299 //With location id and unique application
1300 if ($count == 1) {
1301 //Set unique application pseudonym
1302 $pseudonym = $session['sau_pseudonym'];
1303 }
1304 //Session is granted
1305 } else {
1306 //Replace granted application
1307 $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
1308
1309 //Set dance
1310 $dance = $this->translator->trans($session['ad_name'].' '.lcfirst($session['ad_type']));
1311
1312 //Set pseudonym
1313 $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
1314 }
1315
1316 //Set title
1317 $title = $this->translator->trans($session['l_title']).($count > 1 ? ' ['.$count.']':'');
1318
1319 //Add the session
1320 $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [
1321 'id' => $session['id'],
1322 'start' => $session['start'],
1323 'stop' => $session['stop'],
1324 'location' => $this->translator->trans($session['l_title']),
1325 'dance' => $dance,
1326 'pseudonym' => $pseudonym,
1327 'class' => $class,
1328 'slot' => self::GLYPHS[$session['t_title']],
1329 'slottitle' => $this->translator->trans($session['t_title']),
1330 'weather' => $weather,
1331 'weathertitle' => implode(' ', $weathertitle),
1332 'applications' => $applications,
1333 'rate' => $session['p_rate'],
1334 'hat' => $session['p_hat']
1335 ];
1336 }
1337 }
1338
1339 //Sort sessions
1340 ksort($calendar[$Ymd]['sessions']);
1341 }
1342
1343 //Send result
1344 return $calendar;
1345 }
1346
1347 /**
1348 * Find all session pending hourly weather
1349 *
1350 * @return array The sessions to update
1351 */
1352 public function findAllPendingHourlyWeather(): array {
1353 //Select all sessions starting and stopping in the next 3 days
1354 //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)
1355 $req = <<<SQL
1356 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
1357 FROM RapsysAirBundle:Session AS s
1358 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1359 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))
1360 SQL;
1361
1362 //Replace bundle entity name by table name
1363 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1364
1365 //Get result set mapping instance
1366 $rsm = new ResultSetMapping();
1367
1368 //Declare all fields
1369 $rsm
1370 ->addEntityResult('RapsysAirBundle:Session', 's')
1371 ->addFieldResult('s', 'id', 'id')
1372 ->addFieldResult('s', 'date', 'date')
1373 ->addFieldResult('s', 'begin', 'begin')
1374 ->addFieldResult('s', 'length', 'length')
1375 ->addFieldResult('s', 'rainfall', 'rainfall')
1376 ->addFieldResult('s', 'rainrisk', 'rainrisk')
1377 ->addFieldResult('s', 'realfeel', 'realfeel')
1378 ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1379 ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1380 ->addFieldResult('s', 'temperature', 'temperature')
1381 ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1382 ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1383 ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
1384 ->addFieldResult('o', 'slot_id', 'id')
1385 ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
1386 ->addFieldResult('l', 'location_id', 'id')
1387 ->addFieldResult('l', 'zipcode', 'zipcode')
1388 ->addIndexBy('s', 'id');
1389
1390 //Send result
1391 return $this->_em
1392 ->createNativeQuery($req, $rsm)
1393 ->getResult();
1394 }
1395
1396 /**
1397 * Find all session pending daily weather
1398 *
1399 * @return array The sessions to update
1400 */
1401 public function findAllPendingDailyWeather(): array {
1402 //Select all sessions stopping after next 3 days
1403 //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
1404 $req = <<<SQL
1405 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
1406 FROM RapsysAirBundle:Session AS s
1407 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1408 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))
1409 SQL;
1410
1411 //Replace bundle entity name by table name
1412 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1413
1414 //Get result set mapping instance
1415 $rsm = new ResultSetMapping();
1416
1417 //Declare all fields
1418 $rsm
1419 ->addEntityResult('RapsysAirBundle:Session', 's')
1420 ->addFieldResult('s', 'id', 'id')
1421 ->addFieldResult('s', 'date', 'date')
1422 ->addFieldResult('s', 'begin', 'begin')
1423 ->addFieldResult('s', 'length', 'length')
1424 ->addFieldResult('s', 'rainfall', 'rainfall')
1425 ->addFieldResult('s', 'rainrisk', 'rainrisk')
1426 ->addFieldResult('s', 'realfeel', 'realfeel')
1427 ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1428 ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1429 ->addFieldResult('s', 'temperature', 'temperature')
1430 ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1431 ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1432 ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
1433 ->addFieldResult('o', 'slot_id', 'id')
1434 ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
1435 ->addFieldResult('l', 'location_id', 'id')
1436 ->addFieldResult('l', 'zipcode', 'zipcode')
1437 ->addIndexBy('s', 'id');
1438
1439 //Send result
1440 return $this->_em
1441 ->createNativeQuery($req, $rsm)
1442 ->getResult();
1443 }
1444
1445 /**
1446 * Find every session pending application
1447 *
1448 * @return array The sessions to update
1449 */
1450 public function findAllPendingApplication(): array {
1451 //Select all sessions not locked without application or canceled application within attribution period
1452 //XXX: DIFF(start, now) <= IF(DIFF(start, created) <= SENIOR_DELAY in DAY, DIFF(start, created) * 3 / 4, SENIOR_DELAY)
1453 //TODO: remonter les donnΓ©es pour le mail ?
1454 $req =<<<SQL
1455 SELECT s.id
1456 FROM RapsysAirBundle:Session as s
1457 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id AND a.canceled IS NULL)
1458 JOIN RapsysAirBundle:Application AS a2 ON (a2.session_id = s.id AND a2.canceled IS NULL)
1459 WHERE s.locked IS NULL AND s.application_id IS NULL AND
1460 (UNIX_TIMESTAMP(@dt_start := ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY)) - UNIX_TIMESTAMP()) <= IF(
1461 (@td_sc := UNIX_TIMESTAMP(@dt_start) - UNIX_TIMESTAMP(s.created)) <= :seniordelay,
1462 ROUND(@td_sc * :regulardelay / :seniordelay),
1463 :seniordelay
1464 )
1465 GROUP BY s.id
1466 ORDER BY @dt_start ASC, s.created ASC
1467 SQL;
1468
1469 //Replace bundle entity name by table name
1470 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1471
1472 //Get result set mapping instance
1473 $rsm = new ResultSetMapping();
1474
1475 //Declare all fields
1476 $rsm
1477 ->addEntityResult('RapsysAirBundle:Session', 's')
1478 ->addFieldResult('s', 'id', 'id')
1479 ->addIndexBy('s', 'id');
1480
1481 //Send result
1482 return $this->_em
1483 ->createNativeQuery($req, $rsm)
1484 ->getResult();
1485 }
1486
1487 /**
1488 * Fetch session best application by session id
1489 *
1490 * @param int $id The session id
1491 * @return ?Application The application or null
1492 */
1493 public function findBestApplicationById(int $id): ?Application {
1494 /**
1495 * Query session applications ranked by location score, global score, created and user_id
1496 *
1497 * @xxx guest (or less) with application on location within 30 day are only considered within guestdelay
1498 *
1499 * @xxx regular (or less) premium application on hotspot are only considered within regulardelay
1500 *
1501 * @xxx senior (or less) with 5 or less session on location are only considered within seniordelay
1502 *
1503 * @xxx senior (or less) with l_pn_ratio >= 1 are only considered within seniordelay
1504 *
1505 * @xxx senior (or less) with l_tr_ratio >= (o_tr_ratio + 5) are only considered within seniordelay
1506 *
1507 * @xxx only consider session within one year (may be unaccurate by the day with after session)
1508 *
1509 * @xxx rainfall may not be accessible for previous session and other session at d-4 (only at d-2)
1510 *
1511 * @todo ??? feedback the data to inform the rejected users ???
1512 */
1513 $req = <<<SQL
1514 SELECT e.id, e.l_score AS score
1515 FROM (
1516 SELECT
1517 d.id,
1518 d.user_id,
1519 d.l_count,
1520 d.l_score,
1521 d.l_tr_ratio,
1522 d.l_pn_ratio,
1523 d.l_previous,
1524 d.g_score,
1525 d.o_tr_ratio,
1526 MAX(ug.group_id) AS group_id,
1527 d.remaining,
1528 d.premium,
1529 d.hotspot,
1530 d.created
1531 FROM (
1532 SELECT
1533 c.id,
1534 c.user_id,
1535 c.l_count,
1536 c.l_score,
1537 c.l_tr_ratio,
1538 c.l_pn_ratio,
1539 c.l_previous,
1540 c.g_score,
1541 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,
1542 c.remaining,
1543 c.premium,
1544 c.hotspot,
1545 c.created
1546 FROM (
1547 SELECT
1548 b.id,
1549 b.user_id,
1550 b.session_id,
1551 b.date,
1552 b.location_id,
1553 b.l_count,
1554 b.l_score,
1555 b.l_tr_ratio,
1556 b.l_pn_ratio,
1557 b.l_previous,
1558 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,
1559 b.remaining,
1560 b.premium,
1561 b.hotspot,
1562 b.created
1563 FROM (
1564 SELECT
1565 a.id,
1566 a.user_id,
1567 s.id AS session_id,
1568 s.date AS date,
1569 s.slot_id,
1570 s.location_id,
1571 COUNT(a2.id) AS l_count,
1572 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,
1573 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,
1574 (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,
1575 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,
1576 UNIX_TIMESTAMP(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY)) - UNIX_TIMESTAMP() AS remaining,
1577 s.premium,
1578 l.hotspot,
1579 a.created
1580 FROM RapsysAirBundle:Session AS s
1581 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1582 JOIN RapsysAirBundle:Application AS a ON (a.session_id = s.id AND a.canceled IS NULL)
1583 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)
1584 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))
1585 WHERE s.id = :sid
1586 GROUP BY a.id
1587 ORDER BY NULL
1588 LIMIT 0, :limit
1589 ) AS b
1590 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)
1591 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))
1592 GROUP BY b.id
1593 ORDER BY NULL
1594 LIMIT 0, :limit
1595 ) AS c
1596 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)
1597 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))
1598 GROUP BY c.id
1599 ORDER BY NULL
1600 LIMIT 0, :limit
1601 ) AS d
1602 LEFT JOIN RapsysAirBundle:UserGroup AS ug ON (ug.user_id = d.user_id)
1603 GROUP BY d.id
1604 LIMIT 0, :limit
1605 ) AS e
1606 WHERE
1607 IF(e.group_id <= :guestid AND e.l_previous <= :guestwait, e.remaining <= :guestdelay, 1) AND
1608 IF(e.group_id <= :regularid AND e.premium = 1 AND e.hotspot = 1, e.remaining <= :regulardelay, 1) AND
1609 IF(e.group_id <= :seniorid AND e.l_count <= :scount, e.remaining <= :regulardelay, 1) AND
1610 IF(e.group_id <= :seniorid AND e.l_pn_ratio >= :pnratio, e.remaining <= :regulardelay, 1) AND
1611 IF(e.group_id <= :seniorid AND e.l_tr_ratio >= (e.o_tr_ratio + :trdiff), e.remaining <= :regulardelay, 1)
1612 ORDER BY e.l_score ASC, e.g_score ASC, e.created ASC, e.user_id ASC
1613 SQL;
1614
1615 //Replace bundle entity name by table name
1616 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1617
1618 //Set update request
1619 $upreq = 'UPDATE RapsysAirBundle:Application SET score = :score, updated = NOW() WHERE id = :id';
1620
1621 //Replace bundle entity name by table name
1622 $upreq = str_replace($this->tableKeys, $this->tableValues, $upreq);
1623
1624 //Get result set mapping instance
1625 $rsm = new ResultSetMapping();
1626
1627 //Declare all fields
1628 $rsm
1629 ->addEntityResult('RapsysAirBundle:Application', 'a')
1630 ->addFieldResult('a', 'id', 'id')
1631 ->addFieldResult('a', 'score', 'score')
1632 ->addIndexBy('a', 'id');
1633
1634 //Get result
1635 //XXX: setting limit in subqueries is required to prevent mariadb optimisation
1636 $applications = $this->_em
1637 ->createNativeQuery($req, $rsm)
1638 ->setParameter('sid', $id)
1639 ->getResult();
1640
1641 //Init ret
1642 $ret = null;
1643
1644 //Update score
1645 foreach($applications as $application) {
1646 //Check if we already saved best candidate
1647 if ($ret === null) {
1648 //Return first application
1649 $ret = $application;
1650 }
1651
1652 //Update application updated field
1653 //XXX: updated field is not modified for user with bad behaviour as application is not retrieved until delay is reached
1654 $this->_em->getConnection()->executeUpdate($upreq, ['id' => $application->getId(), 'score' => $application->getScore()], ['id' => Types::INTEGER, 'score' => Types::FLOAT]);
1655 }
1656
1657 //Return best ranked application
1658 return $ret;
1659 }
1660
1661 /**
1662 * Rekey sessions and applications by chronological session id
1663 *
1664 * @return bool The rekey success or failure
1665 */
1666 function rekey(): bool {
1667 //Get connection
1668 $cnx = $this->_em->getConnection();
1669
1670 //Set the request
1671 $req = <<<SQL
1672 SELECT
1673 a.id,
1674 a.sa_id
1675 FROM (
1676 SELECT
1677 s.id,
1678 s.date,
1679 s.begin,
1680 s.slot_id,
1681 GROUP_CONCAT(sa.id ORDER BY sa.id SEPARATOR "\\n") AS sa_id
1682 FROM RapsysAirBundle:Session AS s
1683 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
1684 GROUP BY s.id
1685 ORDER BY NULL
1686 ) AS a
1687 ORDER BY ADDDATE(ADDTIME(a.date, a.begin), INTERVAL IF(a.slot_id = :afterid, 1, 0) DAY) ASC
1688 SQL;
1689
1690 //Replace bundle entity name by table name
1691 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1692
1693 //Get result set mapping instance
1694 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1695 $rsm = new ResultSetMapping();
1696
1697 //Declare all fields
1698 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1699 //addScalarResult($sqlColName, $resColName, $type = 'string');
1700 $rsm->addScalarResult('id', 'id', 'integer')
1701 ->addScalarResult('sa_id', 'sa_id', 'string');
1702 #->addIndexByScalar('id');
1703
1704 //Fetch result
1705 $rnq = $this->_em->createNativeQuery($req, $rsm);
1706
1707 //Get result set
1708 $res = $rnq->getResult();
1709
1710 //Start transaction
1711 $cnx->beginTransaction();
1712
1713 //Set update session request
1714 $sreq = <<<SQL
1715 UPDATE RapsysAirBundle:Session
1716 SET id = :nid, updated = NOW()
1717 WHERE id = :id
1718 SQL;
1719
1720 //Replace bundle entity name by table name
1721 $sreq = str_replace($this->tableKeys, $this->tableValues, $sreq);
1722
1723 //Set update application request
1724 $areq = <<<SQL
1725 UPDATE RapsysAirBundle:Application
1726 SET session_id = :nid, updated = NOW()
1727 WHERE session_id = :id
1728 SQL;
1729
1730 //Replace bundle entity name by table name
1731 $areq = str_replace($this->tableKeys, $this->tableValues, $areq);
1732
1733 //Set max value
1734 $max = max(array_keys($res));
1735
1736 try {
1737 //Prepare session to update
1738 foreach($res as $id => $data) {
1739 //Set temp id
1740 $res[$id]['t_id'] = $max + $id + 1;
1741
1742 //Set new id
1743 $res[$id]['n_id'] = $id + 1;
1744
1745 //Explode application ids
1746 $res[$id]['sa_id'] = explode("\n", $data['sa_id']);
1747
1748 //Without change
1749 if ($res[$id]['n_id'] == $res[$id]['id']) {
1750 //Remove unchanged session
1751 unset($res[$id]);
1752 }
1753 }
1754
1755 //With changes
1756 if (!empty($res)) {
1757 //Disable foreign key checks
1758 $cnx->prepare('SET foreign_key_checks = 0')->execute();
1759
1760 //Update to temp id
1761 foreach($res as $id => $data) {
1762 //Run session update
1763 $cnx->executeUpdate($sreq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
1764
1765 //Run applications update
1766 $cnx->executeUpdate($areq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
1767 }
1768
1769 //Update to new id
1770 foreach($res as $id => $data) {
1771 //Run session update
1772 $cnx->executeUpdate($sreq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]);
1773
1774 //Run applications update
1775 $cnx->executeUpdate($areq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]);
1776 }
1777
1778 //Restore foreign key checks
1779 $cnx->prepare('SET foreign_key_checks = 1')->execute();
1780
1781 //Commit transaction
1782 $cnx->commit();
1783
1784 //Set update auto_increment request
1785 $ireq = <<<SQL
1786 ALTER TABLE RapsysAirBundle:Session
1787 auto_increment = 1
1788 SQL;
1789
1790 //Replace bundle entity name by table name
1791 $ireq = str_replace($this->tableKeys, $this->tableValues, $ireq);
1792
1793 //Reset auto_increment
1794 $cnx->exec($ireq);
1795 //Without changes
1796 } else {
1797 //Rollback transaction
1798 $cnx->rollback();
1799 }
1800 } catch(\Exception $e) {
1801 //Rollback transaction
1802 $cnx->rollback();
1803
1804 //Throw exception
1805 throw $e;
1806 }
1807
1808 //Return success
1809 return true;
1810 }
1811 }
Symfony bundle rapsys/airbundle used to handle libre air features