]> RaphaΓ«l G. Git Repositories - airbundle/blob - Repository/SessionRepository.php
Rapsys Git / airbundle / blob
? search:
summary | shortlog | log | commit | commitdiff | tree
history | raw | HEAD
Rename rapsysair:calendar2 command to rapsysair:calendar
[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\AbstractQuery;
16 use Doctrine\ORM\Query\ResultSetMapping;
17
18 use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
19
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;
25
26 /**
27 * SessionRepository
28 */
29 class SessionRepository extends Repository {
30 ///Set glyphs
31 //TODO: document utf-8 codes ?
32 //TODO: use unknown == ? symbol by default ???
33 //πŸ’ƒ<= dancer #0001f483
34 //πŸ’ƒ<= tanguera #0001f483
35 const GLYPHS = [
36 //Slots
37 'Morning' => 'πŸŒ…', #0001f305
38 'Afternoon' => 'β˜€οΈ', #2600
39 'Evening' => 'πŸŒ‡', #0001f307
40 'After' => '✨', #2728
41 //Weathers
42 'Cleary' => 'β˜€', #2600
43 'Sunny' => 'β›…', #26c5
44 'Cloudy' => '☁', #2601
45 'Winty' => '❄️', #2744
46 'Rainy' => 'πŸŒ‚', #0001f302
47 'Stormy' => 'β˜”', #2614
48 //Rate
49 'Euro' => '€', #20ac
50 'Free' => '🍺', #0001f37a
51 'Hat' => '🎩' #0001f3a9
52 ];
53
54 /**
55 * Find session as array by id
56 *
57 * @param int $id The session id
58 * @return array The session data
59 */
60 public function findOneByIdAsArray(int $id): ?array {
61 //Set the request
62 $req =<<<SQL
63 SELECT
64 s.id,
65 s.date,
66 s.begin,
67 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
68 s.length,
69 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
70 s.rainfall,
71 s.rainrisk,
72 s.realfeel,
73 s.realfeelmin,
74 s.realfeelmax,
75 s.temperature,
76 s.temperaturemin,
77 s.temperaturemax,
78 s.locked,
79 s.created,
80 s.updated,
81 s.location_id AS l_id,
82 l.title AS l_title,
83 l.description AS l_description,
84 l.address AS l_address,
85 l.zipcode AS l_zipcode,
86 l.city AS l_city,
87 l.latitude AS l_latitude,
88 l.longitude AS l_longitude,
89 l.indoor AS l_indoor,
90 l.updated AS l_updated,
91 s.slot_id AS t_id,
92 t.title AS t_title,
93 s.application_id AS a_id,
94 a.canceled AS a_canceled,
95 a.dance_id AS ad_id,
96 ad.name AS ad_name,
97 ad.type AS ad_type,
98 a.user_id AS au_id,
99 au.pseudonym AS au_pseudonym,
100 p.id AS p_id,
101 p.description AS p_description,
102 p.class AS p_class,
103 p.contact AS p_contact,
104 p.donate AS p_donate,
105 p.link AS p_link,
106 p.profile AS p_profile,
107 p.rate AS p_rate,
108 p.hat AS p_hat,
109 GREATEST(s.created, s.updated, l.created, l.updated, t.created, t.updated, COALESCE(a.created, '1970-01-01'), COALESCE(a.updated, '1970-01-01'), COALESCE(ad.created, '1970-01-01'), COALESCE(ad.updated, '1970-01-01'), COALESCE(au.created, '1970-01-01'), COALESCE(au.updated, '1970-01-01'), COALESCE(p.created, '1970-01-01'), COALESCE(p.updated, '1970-01-01'), MAX(GREATEST(COALESCE(sa.created, '1970-01-01'), COALESCE(sa.updated, '1970-01-01'), COALESCE(sad.created, '1970-01-01'), COALESCE(sad.updated, '1970-01-01'), COALESCE(sau.created, '1970-01-01'), COALESCE(sau.updated, '1970-01-01')))) 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\Dance AS sad ON (sad.id = sa.dance_id)
126 LEFT JOIN Rapsys\AirBundle\Entity\User AS sau ON (sau.id = sa.user_id)
127 WHERE s.id = :id
128 GROUP BY s.id
129 ORDER BY NULL
130 SQL;
131
132 //Replace bundle entity name by table name
133 $req = str_replace($this->tableKeys, $this->tableValues, $req);
134
135 //Get result set mapping instance
136 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
137 $rsm = new ResultSetMapping();
138
139 //Declare all fields
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');
202
203 //Set result
204 $result = $this->_em
205 ->createNativeQuery($req, $rsm)
206 ->setParameter('id', $id)
207 ->getOneOrNullResult();
208
209 //Without result
210 if ($result === null) {
211 //Return result
212 return $result;
213 }
214
215 //Set route
216 $route = 'rapsysair_session_view';
217
218 //Set route params
219 $routeParams = ['id' => $id, 'location' => $this->slugger->slug($this->translator->trans($result['l_title']))];
220
221 //Set session
222 $session = [
223 'id' => $id,
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,
243 'location' => [
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']])
259 ],
260 'slot' => [
261 'id' => $result['t_id'],
262 'the' => $this->translator->trans('the '.lcfirst($result['t_title'])),
263 'title' => $this->translator->trans($result['t_title'])
264 ],
265 'snippet' => null,
266 'applications' => null
267 ];
268
269 //With application
270 if (!empty($result['a_id'])) {
271 $session['application'] = [
272 'dance' => [
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']))])
279 ],
280 'user' => [
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']])
287 ],
288 'id' => $result['a_id'],
289 'canceled' => $result['a_canceled']
290 ];
291 }
292
293 //With snippet
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']
305 ];
306 }
307
308 //With applications
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']));
322
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']);
327
328 //Init applications
329 $session['applications'] = [];
330
331 //Iterate on each applications id
332 foreach($result['sa_id'] as $i => $sa_id) {
333 $session['applications'][$sa_id] = [
334 'user' => null,
335 'score' => $result['sa_score'][$i],
336 'created' => $result['sa_created'][$i],
337 'updated' => $result['sa_updated'][$i],
338 'canceled' => $result['sa_canceled'][$i]
339 ];
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])
345 ];
346 }
347 }
348 }
349
350 //Set link
351 $session['link'] = $this->router->generate($route, $routeParams);
352
353 //Set canonical
354 $session['canonical'] = $this->router->generate($route, $routeParams, UrlGeneratorInterface::ABSOLUTE_URL);
355
356 //Set alternates
357 $session['alternates'] = [];
358
359 //Iterate on each locales
360 foreach($this->translator->getFallbackLocales() as $fallback) {
361 //Set titles
362 $titles = [];
363
364 //Set route params location
365 $routeParams['location'] = $this->slugger->slug($this->translator->trans($result['l_title'], [], null, $fallback));
366
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));
370 }
371
372 //With route params user
373 if (!empty($routeParams['user'])) {
374 $routeParams['user'] = $this->slugger->slug($result['au_pseudonym']);
375 }
376
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
382 } else {
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);
387 }
388
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)
395 ];
396 }
397
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)
406 ];
407 }
408 }
409
410 //Return session
411 return $session;
412 }
413
414 /**
415 * Find sessions as calendar array by date period
416 *
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
423 */
424 public function findAllByPeriodAsCalendarArray(\DatePeriod $period, ?bool $granted = null, ?float $latitude = null, ?float $longitude = null, ?int $userId = null): array {
425 //Init granted sql
426 $grantSql = '';
427
428 //When granted is set
429 if (empty($granted)) {
430 //Set application and user as optional
431 $grantSql = 'LEFT ';
432 }
433
434 //Init location sql
435 $locationSql = '';
436
437 //When latitude and longitude
438 if ($latitude !== null && $longitude !== null) {
439 //Set the request
440 //XXX: get every location between 0 and 15 km of latitude and longitude
441 $req = <<<SQL
442 SELECT l.id
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
445 SQL;
446
447 //Replace bundle entity name by table name
448 $req = str_replace($this->tableKeys, $this->tableValues, $req);
449
450 //Get result set mapping instance
451 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
452 $rsm = new ResultSetMapping();
453
454 //Declare all fields
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');
459
460 //Set location ids
461 //XXX: check that latitude and longitude have not be swapped !!!
462 //XXX: latitude ~= 48.x longitude ~= 2.x
463 $locationIds = array_keys(
464 $this->_em
465 ->createNativeQuery($req, $rsm)
466 ->setParameter('latitude', $latitude)
467 ->setParameter('longitude', $longitude)
468 ->getArrayResult()
469 );
470
471 //Add location id clause
472 $locationSql = "\n\t".'AND s.location_id IN (:lids)';
473 //When user id
474 } elseif ($userId !== null) {
475 //Set the request
476 //XXX: get every location between 0 and 15 km
477 $req = <<<SQL
478 SELECT l2.id
479 FROM (
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
485 GROUP BY l.id
486 ORDER BY NULL
487 LIMIT 0, :limit
488 ) AS a
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
491 GROUP BY l2.id
492 ORDER BY NULL
493 SQL;
494
495 //Replace bundle entity name by table name
496 $req = str_replace($this->tableKeys, $this->tableValues, $req);
497
498 //Get result set mapping instance
499 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
500 $rsm = new ResultSetMapping();
501
502 //Declare all fields
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');
507
508 //Set location ids
509 $locationIds = array_keys(
510 $this->_em
511 ->createNativeQuery($req, $rsm)
512 ->setParameter('id', $userId)
513 ->getArrayResult()
514 );
515
516 //With location ids
517 if (!empty($locationIds)) {
518 //Add location id clause
519 $locationSql = "\n\t".'AND s.location_id IN (:lids)';
520 }
521 }
522
523 //Set the request
524 $req = <<<SQL
525
526 SELECT
527 s.id,
528 s.date,
529 s.rainrisk,
530 s.rainfall,
531 s.realfeel,
532 s.temperature,
533 s.locked,
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,
537 l.title AS l_title,
538 l.address AS l_address,
539 l.zipcode AS l_zipcode,
540 l.city AS l_city,
541 l.latitude AS l_latitude,
542 l.longitude AS l_longitude,
543 l.indoor AS l_indoor,
544 s.slot_id AS t_id,
545 t.title AS t_title,
546 s.application_id AS a_id,
547 a.canceled AS a_canceled,
548 a.dance_id AS ad_id,
549 ad.name AS ad_name,
550 ad.type AS ad_type,
551 a.user_id AS au_id,
552 au.pseudonym AS au_pseudonym,
553 p.hat AS p_hat,
554 p.rate AS p_rate,
555 p.short AS p_short,
556 GREATEST(s.created, s.updated, l.created, l.updated, t.created, t.updated, COALESCE(a.created, '1970-01-01'), COALESCE(a.updated, '1970-01-01'), COALESCE(ad.created, '1970-01-01'), COALESCE(ad.updated, '1970-01-01'), COALESCE(au.created, '1970-01-01'), COALESCE(au.updated, '1970-01-01'), COALESCE(p.created, '1970-01-01'), COALESCE(p.updated, '1970-01-01'), MAX(GREATEST(COALESCE(sa.created, '1970-01-01'), COALESCE(sa.updated, '1970-01-01'), COALESCE(sad.created, '1970-01-01'), COALESCE(sad.updated, '1970-01-01'), COALESCE(sau.created, '1970-01-01'), COALESCE(sau.updated, '1970-01-01')))) AS modified,
557 GROUP_CONCAT(sa.dance_id ORDER BY sa.user_id SEPARATOR "\\n") AS sad_id,
558 GROUP_CONCAT(sad.name ORDER BY sa.user_id SEPARATOR "\\n") AS sad_name,
559 GROUP_CONCAT(sad.type ORDER BY sa.user_id SEPARATOR "\\n") AS sad_type,
560 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
561 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
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}
573 GROUP BY s.id
574 ORDER BY NULL
575 SQL;
576
577 //Replace bundle entity name by table name
578 $req = str_replace($this->tableKeys, $this->tableValues, $req);
579
580 //Get result set mapping instance
581 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
582 $rsm = new ResultSetMapping();
583
584 //Declare all fields
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('sad_id', 'sad_id', 'string')
619 //XXX: is a string because of \n separator
620 ->addScalarResult('sad_name', 'sad_name', 'string')
621 //XXX: is a string because of \n separator
622 ->addScalarResult('sad_type', 'sad_type', 'string')
623 //XXX: is a string because of \n separator
624 ->addScalarResult('sau_id', 'sau_id', 'string')
625 //XXX: is a string because of \n separator
626 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
627 ->addIndexByScalar('id');
628
629 //Fetch result
630 $res = $this->_em
631 ->createNativeQuery($req, $rsm)
632 ->setParameter('begin', $period->getStartDate())
633 ->setParameter('end', $period->getEndDate());
634
635 //Add optional location ids
636 if (!empty($locationIds)) {
637 $res->setParameter('lids', $locationIds);
638 }
639
640 //Get result
641 $result = $res->getResult();
642
643 //Init calendar
644 $calendar = [];
645
646 //Init month
647 $month = null;
648
649 //Set route
650 $route = 'rapsysair_session_view';
651
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'),
657 'modified' => null,
658 'class' => [],
659 'sessions' => []
660 ];
661
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;
669 }
670 }
671 //Deal with today
672 if ($date->format('U') == ($today = strtotime('today'))) {
673 $calendar[$Ymd]['title'] .= '/'.$month;
674 $calendar[$Ymd]['current'] = true;
675 $calendar[$Ymd]['class'][] = 'current';
676 }
677 //Disable passed days
678 if ($date->format('U') < $today) {
679 $calendar[$Ymd]['disabled'] = true;
680 $calendar[$Ymd]['class'][] = 'disabled';
681 }
682 //Set next month days
683 if ($date->format('m') > date('m')) {
684 $calendar[$Ymd]['next'] = true;
685 #$calendar[$Ymd]['class'][] = 'next';
686 }
687
688 //Detect sunday
689 if ($date->format('w') == 0) {
690 $calendar[$Ymd]['class'][] = 'sunday';
691 }
692
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']) {
698 //Update modified
699 $calendar[$Ymd]['modified'] = $session['modified'];
700 }
701
702 //Set applications
703 $applications = array_combine($candidates = explode("\n", $session['sau_id']), explode("\n", $session['sau_pseudonym']));
704
705 //Compute classes
706 $class = [];
707
708 //With locked
709 if (!empty($session['locked'])) {
710 $class[] = 'locked';
711 //Without locked
712 } else {
713 //With application
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';
721 //Session is granted
722 } else {
723 $class[] = 'granted';
724 }
725
726 //With user id
727 if ($userId !== null && $session['au_id'] == $userId) {
728 $class[] = 'highlight';
729 }
730 } else {
731 $class[] = 'pending';
732 }
733
734 //With latitude and longitude
735 if ($latitude !== null && $longitude !== null && $session['l_latitude'] == $latitude && $session['l_longitude'] == $longitude) {
736 $class[] = 'highlight';
737 }
738 }
739
740 //Set temperature
741 $temperature = [
742 'glyph' => self::GLYPHS['Cleary'],
743 'title' => []
744 ];
745
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'];
754 }
755
756 //Check if temperature is available
757 if ($session['temperature'] !== null) {
758 $temperature['title'][] = $session['temperature'].'Β°C';
759 }
760
761 //Check if realfeel is available
762 if ($session['realfeel'] !== null) {
763 $temperature['title'][] = $session['realfeel'].'Β°R';
764 }
765
766 //Compute temperature title
767 $temperature['title'] = implode(' ', $temperature['title']);
768
769 //Set rain
770 $rain = [
771 'glyph' => self::GLYPHS['Cleary'],
772 'title' => []
773 ];
774
775 //Compute rain glyph
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'];
781 }
782
783 //Check if rainrisk is available
784 if ($session['rainrisk'] !== null) {
785 $rain['title'][] = ($session['rainrisk']*100).'%';
786 }
787
788 //Check if rainfall is available
789 if ($session['rainfall'] !== null) {
790 $rain['title'][] = $session['rainfall'].'mm';
791 }
792
793 //Compute rain title
794 $rain['title'] = implode(' ', $rain['title']);
795
796 //Set application
797 $application = null;
798
799 //Set rate
800 $rate = null;
801
802 //Set route params
803 $routeParams = ['id' => $session['id'], 'location' => $this->slugger->slug($this->translator->trans($session['l_title']))];
804
805 //With application
806 if (!empty($session['a_id'])) {
807 //Set dance
808 $routeParams['dance'] = $this->slugger->slug($dance = $this->translator->trans($session['ad_name'].' '.lcfirst($session['ad_type'])));
809
810 //Set user
811 $routeParams['user'] = $this->slugger->slug($session['au_pseudonym']);
812
813 //Set title
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'])]);
815
816 //Set pseudonym
817 $application = [
818 'dance' => [
819 'id' => $session['ad_id'],
820 'name' => $this->translator->trans($session['ad_name']),
821 'type' => $this->translator->trans($session['ad_type']),
822 'title' => $dance
823 ],
824 'user' => [
825 'id' => $session['au_id'],
826 'title' => $session['au_pseudonym']
827 ]
828 ];
829
830 //Set rate
831 $rate = [
832 'glyph' => self::GLYPHS['Free'],
833 'rate' => null,
834 'short' => $session['p_short'],
835 'title' => $this->translator->trans('Free')
836 ];
837
838 //With hat
839 if (!empty($session['p_hat'])) {
840 //Set glyph
841 $rate['glyph'] = self::GLYPHS['Hat'];
842
843 //With rate
844 if (!empty($session['p_rate'])) {
845 //Set rate
846 $rate['rate'] = $session['p_rate'];
847
848 //Set title
849 $rate['title'] = $this->translator->trans('%rate%€ to the hat', ['%rate%' => $session['p_rate']]);
850 //Without rate
851 } else {
852 //Set title
853 $rate['title'] = $this->translator->trans('To the hat');
854 }
855 //With rate
856 } elseif (!empty($session['p_rate'])) {
857 //Set glyph
858 $rate['glyph'] = self::GLYPHS['Euro'];
859
860 //Set rate
861 $rate['rate'] = $session['p_rate'];
862
863 //Set title
864 $rate['title'] = $session['p_rate'].' €';
865 }
866 //With unique application
867 } elseif (count($applications) == 1) {
868 //Set dance
869 $dance = $this->translator->trans($session['sad_name'].' '.lcfirst($session['sad_type']));
870
871 //Set title
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'])]);
873
874 //Set pseudonym
875 $application = [
876 'dance' => [
877 'id' => $session['sad_id'],
878 'name' => $this->translator->trans($session['sad_name']),
879 'type' => $this->translator->trans($session['sad_type']),
880 'title' => $dance
881 ],
882 'user' => [
883 'id' => $session['sau_id'],
884 'title' => $session['sau_pseudonym']
885 ]
886 ];
887
888 //TODO: glyph stuff ???
889 //Without application
890 } else {
891 //Set title
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'])]);
893 }
894
895 //Add the session
896 $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%05d', $session['id'])] = [
897 'id' => $session['id'],
898 'start' => $session['start'],
899 'stop' => $session['stop'],
900 'class' => $class,
901 'temperature' => $temperature,
902 'rain' => $rain,
903 'title' => $title,
904 'link' => $this->router->generate($route, $routeParams),
905 'location' => [
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']
917 ],
918 'application' => $application,
919 'slot' => [
920 'glyph' => self::GLYPHS[$session['t_title']],
921 'title' => $this->translator->trans($session['t_title'])
922 ],
923 'rate' => $rate,
924 'modified' => $session['modified'],
925 'applications' => $applications
926 ];
927 }
928 }
929
930 //Sort sessions
931 ksort($calendar[$Ymd]['sessions']);
932 }
933
934 //Send result
935 return $calendar;
936 }
937
938
939 /**
940 * Find sessions by user id and synchronized date time
941 *
942 * @param int $userId The user id
943 * @param DateTime $synchronized The synchronized datetime
944 * @return array The session data
945 */
946 public function findAllByUserIdSynchronized(int $userId, \DateTime $synchronized = new \DateTime('1970-01-01')): array {
947 //Set the request
948 $req = <<<SQL
949 SELECT ud.dance_id
950 FROM Rapsys\AirBundle\Entity\UserDance AS ud
951 WHERE ud.user_id = :uid
952 SQL;
953
954 //Replace bundle entity name by table name
955 $req = str_replace($this->tableKeys, $this->tableValues, $req);
956
957 //Get result set mapping instance
958 $rsm = new ResultSetMapping();
959
960 //Declare dance id field
961 $rsm->addScalarResult('dance_id', 'dance_id', 'integer');
962
963 //Set dance sql part
964 $danceSql = '';
965
966 //With user dance
967 if (
968 $userDances = $this->_em
969 ->createNativeQuery($req, $rsm)
970 ->setParameter('uid', $userId)
971 ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN)
972 ) {
973 //Set dance sql part
974 $danceSql = ' AND a.dance_id IN (:dids)';
975 }
976
977 //Set the request
978 $req = <<<SQL
979 SELECT us.subscribed_id
980 FROM Rapsys\AirBundle\Entity\UserSubscription AS us
981 WHERE us.user_id = :uid
982 SQL;
983
984 //Replace bundle entity name by table name
985 $req = str_replace($this->tableKeys, $this->tableValues, $req);
986
987 //Get result set mapping instance
988 $rsm = new ResultSetMapping();
989
990 //Declare user id field
991 $rsm->addScalarResult('subscribed_id', 'subscribed_id', 'integer');
992
993 //Set subscription sql part
994 $subscriptionSql = '';
995
996 //With user subscription
997 if (
998 $userSubscriptions = $this->_em
999 ->createNativeQuery($req, $rsm)
1000 ->setParameter('uid', $userId)
1001 ->getResult(AbstractQuery::HYDRATE_SCALAR_COLUMN)
1002 ) {
1003 //Set subscription sql part
1004 $subscriptionSql = ' AND a.user_id IN (:uids)';
1005 }
1006
1007 //Set the request
1008 $req = <<<SQL
1009 SELECT
1010 s.id,
1011 s.date,
1012 s.locked,
1013 GREATEST(s.created, s.updated, l.created, l.updated, a.created, a.updated, ad.created, ad.updated, au.created, au.updated, COALESCE(p.created, '1970-01-01'), COALESCE(p.updated, '1970-01-01')) AS modified,
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,
1019 l.city AS l_city,
1020 l.title AS l_title,
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,
1026 ad.name AS ad_name,
1027 ad.type AS ad_type,
1028 a.user_id AS au_id,
1029 au.forename AS au_forename,
1030 au.pseudonym AS au_pseudonym,
1031 p.id AS p_id,
1032 p.description AS p_description,
1033 p.class AS p_class,
1034 p.short AS p_short,
1035 p.hat AS p_hat,
1036 p.rate AS p_rate,
1037 p.contact AS p_contact,
1038 p.donate AS p_donate,
1039 p.link AS p_link,
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(GREATEST(s.created, s.updated, l.created, l.updated, a.created, a.updated, ad.created, ad.updated, au.created, au.updated, COALESCE(p.created, '1970-01-01'), COALESCE(p.updated, '1970-01-01')), ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY)) >= :synchronized
1048 SQL;
1049
1050 //Replace bundle entity name by table name
1051 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1052
1053 //Get result set mapping instance
1054 $rsm = new ResultSetMapping();
1055
1056 //Declare all fields
1057 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1058 //addScalarResult($sqlColName, $resColName, $type = 'string');
1059 $rsm
1060 ->addScalarResult('id', 'id', 'integer')
1061 ->addScalarResult('date', 'date', 'date')
1062 ->addScalarResult('locked', 'locked', 'datetime')
1063 ->addScalarResult('modified', 'modified', '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');
1094
1095 //Return sessions
1096 //TODO: XXX: finish here
1097 return $this->_em
1098 ->createNativeQuery($req, $rsm)
1099 ->setParameter('dids', $userDances)
1100 ->setParameter('uids', $userSubscriptions)
1101 ->setParameter('synchronized', $synchronized)
1102 ->getResult(AbstractQuery::HYDRATE_ARRAY);
1103 }
1104
1105 /**
1106 * Find session by location, slot and date
1107 *
1108 * @param Location $location The location
1109 * @param Slot $slot The slot
1110 * @param DateTime $date The datetime
1111 * @return ?Session The found session
1112 */
1113 public function findOneByLocationSlotDate(Location $location, Slot $slot, \DateTime $date): ?Session {
1114 //Return sessions
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();
1121 }
1122
1123 /**
1124 * Fetch sessions by date period
1125 *
1126 * @XXX: used in calendar command
1127 *
1128 * @param DatePeriod $period The date period
1129 * @return array The session array
1130 */
1131 public function fetchAllByDatePeriod(\DatePeriod $period): array {
1132 //Set the request
1133 //TODO: exclude opera and others ?
1134 $req = <<<SQL
1135 SELECT
1136 s.id,
1137 s.date,
1138 s.locked,
1139 s.updated,
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,
1145 l.city AS l_city,
1146 l.title AS l_title,
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,
1152 ad.name AS ad_name,
1153 ad.type AS ad_type,
1154 a.user_id AS au_id,
1155 au.forename AS au_forename,
1156 au.pseudonym AS au_pseudonym,
1157 p.id AS p_id,
1158 p.description AS p_description,
1159 p.class AS p_class,
1160 p.short AS p_short,
1161 p.hat AS p_hat,
1162 p.rate AS p_rate,
1163 p.contact AS p_contact,
1164 p.donate AS p_donate,
1165 p.link AS p_link,
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
1174 ORDER BY NULL
1175 SQL;
1176
1177 //Replace bundle entity name by table name
1178 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1179
1180 //Get result set mapping instance
1181 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1182 $rsm = new ResultSetMapping();
1183
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');
1221
1222 //Fetch result
1223 $res = $this->_em
1224 ->createNativeQuery($req, $rsm)
1225 ->setParameter('begin', $period->getStartDate())
1226 ->setParameter('end', $period->getEndDate());
1227
1228 //Return result
1229 return $res->getResult();
1230 }
1231
1232 /**
1233 * Find all session pending hourly weather
1234 *
1235 * @return array The sessions to update
1236 */
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)
1240 $req = <<<SQL
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))
1245 SQL;
1246
1247 //Replace bundle entity name by table name
1248 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1249
1250 //Get result set mapping instance
1251 $rsm = new ResultSetMapping();
1252
1253 //Declare all fields
1254 $rsm
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');
1274
1275 //Send result
1276 return $this->_em
1277 ->createNativeQuery($req, $rsm)
1278 ->getResult();
1279 }
1280
1281 /**
1282 * Find all session pending daily weather
1283 *
1284 * @return array The sessions to update
1285 */
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)
1289 $req = <<<SQL
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))
1294 SQL;
1295
1296 //Replace bundle entity name by table name
1297 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1298
1299 //Get result set mapping instance
1300 $rsm = new ResultSetMapping();
1301
1302 //Declare all fields
1303 $rsm
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');
1323
1324 //Send result
1325 return $this->_em
1326 ->createNativeQuery($req, $rsm)
1327 ->getResult();
1328 }
1329
1330 /**
1331 * Find every session pending application
1332 *
1333 * @return array The sessions to update
1334 */
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 ?
1339 $req =<<<SQL
1340 SELECT s.id
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),
1348 :seniordelay
1349 )
1350 GROUP BY s.id
1351 ORDER BY @dt_start ASC, s.created ASC
1352 SQL;
1353
1354 //Replace bundle entity name by table name
1355 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1356
1357 //Get result set mapping instance
1358 $rsm = new ResultSetMapping();
1359
1360 //Declare all fields
1361 $rsm
1362 ->addEntityResult('Rapsys\AirBundle\Entity\Session', 's')
1363 ->addFieldResult('s', 'id', 'id')
1364 ->addIndexBy('s', 'id');
1365
1366 //Send result
1367 return $this->_em
1368 ->createNativeQuery($req, $rsm)
1369 ->getResult();
1370 }
1371
1372 /**
1373 * Fetch session best application by session id
1374 *
1375 * @param int $id The session id
1376 * @return ?Application The application or null
1377 */
1378 public function findBestApplicationById(int $id): ?Application {
1379 /**
1380 * Query session applications ranked by location score, global score, created and user_id
1381 *
1382 * @xxx guest (or less) with application on location within 30 day are only considered within guestdelay
1383 *
1384 * @xxx regular (or less) premium application on hotspot are only considered within regulardelay
1385 *
1386 * @xxx senior (or less) with 5 or less session on location are only considered within seniordelay
1387 *
1388 * @xxx senior (or less) with l_pn_ratio >= 1 are only considered within seniordelay
1389 *
1390 * @xxx senior (or less) with l_tr_ratio >= (o_tr_ratio + 5) are only considered within seniordelay
1391 *
1392 * @xxx only consider session within one year (may be unaccurate by the day with after session)
1393 *
1394 * @xxx rainfall may not be accessible for previous session and other session at d-4 (only at d-2)
1395 *
1396 * @todo ??? feedback the data to inform the rejected users ???
1397 */
1398 $req = <<<SQL
1399 SELECT e.id, e.l_score AS score
1400 FROM (
1401 SELECT
1402 d.id,
1403 d.user_id,
1404 d.l_count,
1405 d.l_score,
1406 d.l_tr_ratio,
1407 d.l_pn_ratio,
1408 d.l_previous,
1409 d.g_score,
1410 d.o_tr_ratio,
1411 MAX(ug.group_id) AS group_id,
1412 d.remaining,
1413 d.premium,
1414 d.hotspot,
1415 d.created
1416 FROM (
1417 SELECT
1418 c.id,
1419 c.user_id,
1420 c.l_count,
1421 c.l_score,
1422 c.l_tr_ratio,
1423 c.l_pn_ratio,
1424 c.l_previous,
1425 c.g_score,
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,
1427 c.remaining,
1428 c.premium,
1429 c.hotspot,
1430 c.created
1431 FROM (
1432 SELECT
1433 b.id,
1434 b.user_id,
1435 b.session_id,
1436 b.date,
1437 b.location_id,
1438 b.l_count,
1439 b.l_score,
1440 b.l_tr_ratio,
1441 b.l_pn_ratio,
1442 b.l_previous,
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,
1444 b.remaining,
1445 b.premium,
1446 b.hotspot,
1447 b.created
1448 FROM (
1449 SELECT
1450 a.id,
1451 a.user_id,
1452 s.id AS session_id,
1453 s.date AS date,
1454 s.slot_id,
1455 s.location_id,
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,
1462 s.premium,
1463 l.hotspot,
1464 a.created
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))
1470 WHERE s.id = :sid
1471 GROUP BY a.id
1472 ORDER BY NULL
1473 LIMIT 0, :limit
1474 ) AS b
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))
1477 GROUP BY b.id
1478 ORDER BY NULL
1479 LIMIT 0, :limit
1480 ) AS c
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))
1483 GROUP BY c.id
1484 ORDER BY NULL
1485 LIMIT 0, :limit
1486 ) AS d
1487 LEFT JOIN Rapsys\AirBundle\Entity\UserGroup AS ug ON (ug.user_id = d.user_id)
1488 GROUP BY d.id
1489 LIMIT 0, :limit
1490 ) AS e
1491 WHERE
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
1498 SQL;
1499
1500 //Replace bundle entity name by table name
1501 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1502
1503 //Set update request
1504 $upreq = 'UPDATE Rapsys\AirBundle\Entity\Application SET score = :score, updated = NOW() WHERE id = :id';
1505
1506 //Replace bundle entity name by table name
1507 $upreq = str_replace($this->tableKeys, $this->tableValues, $upreq);
1508
1509 //Get result set mapping instance
1510 $rsm = new ResultSetMapping();
1511
1512 //Declare all fields
1513 $rsm
1514 ->addEntityResult('Rapsys\AirBundle\Entity\Application', 'a')
1515 ->addFieldResult('a', 'id', 'id')
1516 ->addFieldResult('a', 'score', 'score')
1517 ->addIndexBy('a', 'id');
1518
1519 //Get result
1520 //XXX: setting limit in subqueries is required to prevent mariadb optimisation
1521 $applications = $this->_em
1522 ->createNativeQuery($req, $rsm)
1523 ->setParameter('sid', $id)
1524 ->getResult();
1525
1526 //Init ret
1527 $ret = null;
1528
1529 //Update score
1530 foreach($applications as $application) {
1531 //Check if we already saved best candidate
1532 if ($ret === null) {
1533 //Return first application
1534 $ret = $application;
1535 }
1536
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]);
1540 }
1541
1542 //Return best ranked application
1543 return $ret;
1544 }
1545
1546 /**
1547 * Rekey sessions and applications by chronological session id
1548 *
1549 * @return bool The rekey success or failure
1550 */
1551 function rekey(): bool {
1552 //Get connection
1553 $cnx = $this->_em->getConnection();
1554
1555 //Set the request
1556 $req = <<<SQL
1557 SELECT
1558 a.id,
1559 a.sa_id
1560 FROM (
1561 SELECT
1562 s.id,
1563 s.date,
1564 s.begin,
1565 s.slot_id,
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)
1569 GROUP BY s.id
1570 ORDER BY NULL
1571 ) AS a
1572 ORDER BY ADDDATE(ADDTIME(a.date, a.begin), INTERVAL IF(a.slot_id = :afterid, 1, 0) DAY) ASC
1573 SQL;
1574
1575 //Replace bundle entity name by table name
1576 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1577
1578 //Get result set mapping instance
1579 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1580 $rsm = new ResultSetMapping();
1581
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');
1588
1589 //Fetch result
1590 $rnq = $this->_em->createNativeQuery($req, $rsm);
1591
1592 //Get result set
1593 $res = $rnq->getResult();
1594
1595 //Start transaction
1596 $cnx->beginTransaction();
1597
1598 //Set update session request
1599 $sreq = <<<SQL
1600 UPDATE Rapsys\AirBundle\Entity\Session
1601 SET id = :nid, updated = NOW()
1602 WHERE id = :id
1603 SQL;
1604
1605 //Replace bundle entity name by table name
1606 $sreq = str_replace($this->tableKeys, $this->tableValues, $sreq);
1607
1608 //Set update application request
1609 $areq = <<<SQL
1610 UPDATE Rapsys\AirBundle\Entity\Application
1611 SET session_id = :nid, updated = NOW()
1612 WHERE session_id = :id
1613 SQL;
1614
1615 //Replace bundle entity name by table name
1616 $areq = str_replace($this->tableKeys, $this->tableValues, $areq);
1617
1618 //Set max value
1619 $max = max(array_keys($res));
1620
1621 try {
1622 //Prepare session to update
1623 foreach($res as $id => $data) {
1624 //Set temp id
1625 $res[$id]['t_id'] = $max + $id + 1;
1626
1627 //Set new id
1628 $res[$id]['n_id'] = $id + 1;
1629
1630 //Explode application ids
1631 $res[$id]['sa_id'] = explode("\n", $data['sa_id']);
1632
1633 //Without change
1634 if ($res[$id]['n_id'] == $res[$id]['id']) {
1635 //Remove unchanged session
1636 unset($res[$id]);
1637 }
1638 }
1639
1640 //With changes
1641 if (!empty($res)) {
1642 //Disable foreign key checks
1643 $cnx->prepare('SET foreign_key_checks = 0')->execute();
1644
1645 //Update to temp id
1646 foreach($res as $id => $data) {
1647 //Run session update
1648 $cnx->executeUpdate($sreq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
1649
1650 //Run applications update
1651 $cnx->executeUpdate($areq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
1652 }
1653
1654 //Update to new 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']]);
1658
1659 //Run applications update
1660 $cnx->executeUpdate($areq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]);
1661 }
1662
1663 //Restore foreign key checks
1664 $cnx->prepare('SET foreign_key_checks = 1')->execute();
1665
1666 //Commit transaction
1667 $cnx->commit();
1668
1669 //Set update auto_increment request
1670 $ireq = <<<SQL
1671 ALTER TABLE Rapsys\AirBundle\Entity\Session
1672 auto_increment = 1
1673 SQL;
1674
1675 //Replace bundle entity name by table name
1676 $ireq = str_replace($this->tableKeys, $this->tableValues, $ireq);
1677
1678 //Reset auto_increment
1679 $cnx->exec($ireq);
1680 //Without changes
1681 } else {
1682 //Rollback transaction
1683 $cnx->rollback();
1684 }
1685 } catch(\Exception $e) {
1686 //Rollback transaction
1687 $cnx->rollback();
1688
1689 //Throw exception
1690 throw $e;
1691 }
1692
1693 //Return success
1694 return true;
1695 }
1696 }
Symfony bundle rapsys/airbundle used to handle libre air features