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


7c6f57417bd4c1964a07c816bb4028cd32d49691
[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 /**
19 * SessionRepository
20 */
21 class SessionRepository extends EntityRepository {
22 ///Set glyphs
23 //TODO: document utf-8 codes ?
24 //TODO: use unknown == ? symbol by default ???
25 //πŸ’ƒ<= dancer #0001f483
26 //πŸ’ƒ<= tanguera #0001f483
27 const GLYPHS = [
28 //Slots
29 'Morning' => 'πŸŒ…', #0001f305
30 'Afternoon' => 'β˜€οΈ', #2600
31 'Evening' => 'πŸŒ‡', #0001f307
32 'After' => '✨', #2728
33 //Weathers
34 'Cleary' => 'β˜€', #2600
35 'Sunny' => 'β›…', #26c5
36 'Cloudy' => '☁', #2601
37 'Winty' => '❄️', #2744
38 'Rainy' => 'πŸŒ‚', #0001f302
39 'Stormy' => 'β˜”', #2614
40 //Rate
41 'Euro' => '€', #20ac
42 'Free' => '🍺', #0001f37a
43 'Hat' => '🎩' #0001f3a9
44 ];
45
46 /**
47 * Find session as array by id
48 *
49 * @param int $id The session id
50 * @param string $locale The locale
51 * @return array The session data
52 */
53 public function findOneByIdAsArray(int $id, string $locale): ?array {
54 //Set the request
55 $req =<<<SQL
56 SELECT
57 s.id,
58 s.date,
59 s.begin,
60 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
61 s.length,
62 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
63 s.rainfall,
64 s.rainrisk,
65 s.realfeel,
66 s.realfeelmin,
67 s.realfeelmax,
68 s.temperature,
69 s.temperaturemin,
70 s.temperaturemax,
71 s.locked,
72 s.created,
73 s.updated,
74 s.location_id AS l_id,
75 l.title AS l_title,
76 l.description AS l_description,
77 l.address AS l_address,
78 l.zipcode AS l_zipcode,
79 l.city AS l_city,
80 l.latitude AS l_latitude,
81 l.longitude AS l_longitude,
82 l.indoor AS l_indoor,
83 l.updated AS l_updated,
84 s.slot_id AS t_id,
85 t.title AS t_title,
86 s.application_id AS a_id,
87 a.canceled AS a_canceled,
88 a.dance_id AS ad_id,
89 ad.name AS ad_name,
90 ad.type AS ad_type,
91 a.user_id AS au_id,
92 au.pseudonym AS au_pseudonym,
93 p.id AS p_id,
94 p.description AS p_description,
95 p.class AS p_class,
96 p.contact AS p_contact,
97 p.donate AS p_donate,
98 p.link AS p_link,
99 p.profile AS p_profile,
100 p.rate AS p_rate,
101 p.hat AS p_hat,
102 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,
103 GROUP_CONCAT(sa.id ORDER BY sa.user_id SEPARATOR "\\n") AS sa_id,
104 GROUP_CONCAT(IFNULL(sa.score, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_score,
105 GROUP_CONCAT(sa.created ORDER BY sa.user_id SEPARATOR "\\n") AS sa_created,
106 GROUP_CONCAT(sa.updated ORDER BY sa.user_id SEPARATOR "\\n") AS sa_updated,
107 GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled,
108 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
109 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
110 FROM RapsysAirBundle:Session AS s
111 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
112 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
113 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
114 LEFT JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
115 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
116 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
117 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
118 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
119 LEFT JOIN RapsysAirBundle:Dance AS sad ON (sad.id = sa.dance_id)
120 WHERE s.id = :id
121 GROUP BY s.id
122 ORDER BY NULL
123 SQL;
124
125 //Replace bundle entity name by table name
126 $req = str_replace($this->tableKeys, $this->tableValues, $req);
127
128 //Get result set mapping instance
129 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
130 $rsm = new ResultSetMapping();
131
132 //Declare all fields
133 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
134 $rsm->addScalarResult('id', 'id', 'integer')
135 ->addScalarResult('date', 'date', 'date')
136 ->addScalarResult('begin', 'begin', 'time')
137 ->addScalarResult('start', 'start', 'datetime')
138 ->addScalarResult('length', 'length', 'time')
139 ->addScalarResult('stop', 'stop', 'datetime')
140 ->addScalarResult('rainfall', 'rainfall', 'float')
141 ->addScalarResult('rainrisk', 'rainrisk', 'float')
142 ->addScalarResult('realfeel', 'realfeel', 'float')
143 ->addScalarResult('realfeelmin', 'realfeelmin', 'float')
144 ->addScalarResult('realfeelmax', 'realfeelmax', 'float')
145 ->addScalarResult('temperature', 'temperature', 'float')
146 ->addScalarResult('temperaturemin', 'temperaturemin', 'float')
147 ->addScalarResult('temperaturemax', 'temperaturemax', 'float')
148 ->addScalarResult('locked', 'locked', 'datetime')
149 ->addScalarResult('created', 'created', 'datetime')
150 ->addScalarResult('updated', 'updated', 'datetime')
151 ->addScalarResult('l_id', 'l_id', 'integer')
152 ->addScalarResult('l_title', 'l_title', 'string')
153 ->addScalarResult('l_description', 'l_description', 'string')
154 ->addScalarResult('l_address', 'l_address', 'string')
155 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
156 ->addScalarResult('l_city', 'l_city', 'string')
157 ->addScalarResult('l_latitude', 'l_latitude', 'float')
158 ->addScalarResult('l_longitude', 'l_longitude', 'float')
159 ->addScalarResult('l_indoor', 'l_indoor', 'boolean')
160 ->addScalarResult('l_updated', 'l_updated', 'datetime')
161 ->addScalarResult('t_id', 't_id', 'integer')
162 ->addScalarResult('t_title', 't_title', 'string')
163 ->addScalarResult('a_id', 'a_id', 'integer')
164 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
165 ->addScalarResult('ad_id', 'ad_id', 'integer')
166 ->addScalarResult('ad_name', 'ad_name', 'string')
167 ->addScalarResult('ad_type', 'ad_type', 'string')
168 ->addScalarResult('au_id', 'au_id', 'integer')
169 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
170 ->addScalarResult('p_id', 'p_id', 'integer')
171 ->addScalarResult('p_description', 'p_description', 'text')
172 ->addScalarResult('p_class', 'p_class', 'text')
173 ->addScalarResult('p_contact', 'p_contact', 'text')
174 ->addScalarResult('p_donate', 'p_donate', 'text')
175 ->addScalarResult('p_link', 'p_link', 'text')
176 ->addScalarResult('p_profile', 'p_profile', 'text')
177 ->addScalarResult('p_rate', 'p_rate', 'integer')
178 ->addScalarResult('p_hat', 'p_hat', 'boolean')
179 ->addScalarResult('modified', 'modified', 'datetime')
180 //XXX: is a string because of \n separator
181 ->addScalarResult('sa_id', 'sa_id', 'string')
182 //XXX: is a string because of \n separator
183 ->addScalarResult('sa_score', 'sa_score', 'string')
184 //XXX: is a string because of \n separator
185 ->addScalarResult('sa_created', 'sa_created', 'string')
186 //XXX: is a string because of \n separator
187 ->addScalarResult('sa_updated', 'sa_updated', 'string')
188 //XXX: is a string because of \n separator
189 ->addScalarResult('sa_canceled', 'sa_canceled', 'string')
190 //XXX: is a string because of \n separator
191 ->addScalarResult('sau_id', 'sau_id', 'string')
192 //XXX: is a string because of \n separator
193 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
194 ->addIndexByScalar('id');
195
196 //Set result
197 $result = $this->_em
198 ->createNativeQuery($req, $rsm)
199 ->setParameter('id', $id)
200 ->setParameter('locale', $locale)
201 ->getOneOrNullResult();
202
203 //Without result
204 if ($result === null) {
205 //Return result
206 return $result;
207 }
208
209 //Set route
210 $route = 'rapsys_air_session_view';
211
212 //Set route params
213 $routeParams = ['id' => $id, 'location' => $this->slugger->slug($this->translator->trans($result['l_title']))];
214
215 //Set session
216 $session = [
217 'id' => $id,
218 'date' => $result['date'],
219 'begin' => $result['begin'],
220 'start' => $result['start'],
221 'length' => $result['length'],
222 'stop' => $result['stop'],
223 'rainfall' => $result['rainfall'] !== null ? $result['rainfall'].' mm' : $result['rainfall'],
224 'rainrisk' => $result['rainrisk'] !== null ? ($result['rainrisk']*100).' %' : $result['rainrisk'],
225 'realfeel' => $result['realfeel'] !== null ? $result['realfeel'].' Β°C' : $result['realfeel'],
226 'realfeelmin' => $result['realfeelmin'] !== null ? $result['realfeelmin'].' Β°C' : $result['realfeelmin'],
227 'realfeelmax' => $result['realfeelmax'] !== null ? $result['realfeelmax'].' Β°C' : $result['realfeelmax'],
228 'temperature' => $result['temperature'] !== null ? $result['temperature'].' Β°C' : $result['temperature'],
229 'temperaturemin' => $result['temperaturemin'] !== null ? $result['temperaturemin'].' Β°C' : $result['temperaturemin'],
230 'temperaturemax' => $result['temperaturemax'] !== null ? $result['temperaturemax'].' Β°C' : $result['temperaturemax'],
231 'locked' => $result['locked'],
232 'created' => $result['created'],
233 'updated' => $result['updated'],
234 'title' => $this->translator->trans('Session %id%', ['%id%' => $id]),
235 'modified' => $result['modified'],
236 'application' => null,
237 'location' => [
238 'id' => $result['l_id'],
239 'at' => $this->translator->trans('at '.$result['l_title']),
240 'title' => $locationTitle = $this->translator->trans($result['l_title']),
241 'description' => $this->translator->trans($result['l_description']??'None'),
242 'address' => $result['l_address'],
243 'zipcode' => $result['l_zipcode'],
244 'city' => $result['l_city'],
245 'in' => $this->translator->trans('in '.$result['l_city']),
246 'map' => $this->translator->trans($result['l_title'].' access map'),
247 'multimap' => $this->translator->trans($result['l_title'].' sector map'),
248 'latitude' => $result['l_latitude'],
249 'longitude' => $result['l_longitude'],
250 'indoor' => $result['l_indoor'],
251 'slug' => $routeParams['location'],
252 'link' => $this->router->generate('rapsys_air_location_view', ['id' => $result['l_id'], 'location' => $routeParams['location']])
253 ],
254 'slot' => [
255 'id' => $result['t_id'],
256 'the' => $this->translator->trans('the '.lcfirst($result['t_title'])),
257 'title' => $this->translator->trans($result['t_title'])
258 ],
259 'snippet' => null,
260 'applications' => null
261 ];
262
263 //With application
264 if (!empty($result['a_id'])) {
265 $session['application'] = [
266 'dance' => [
267 'id' => $result['ad_id'],
268 'title' => $this->translator->trans($result['ad_name'].' '.lcfirst($result['ad_type'])),
269 'name' => $this->translator->trans($result['ad_name']),
270 'type' => $this->translator->trans($result['ad_type']),
271 'slug' => $routeParams['dance'] = $this->slugger->slug($this->translator->trans($result['ad_name'].' '.lcfirst($result['ad_type']))),
272 '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']))])
273 ],
274 'user' => [
275 'id' => $result['au_id'],
276 'by' => $this->translator->trans('by %pseudonym%', [ '%pseudonym%' => $result['au_pseudonym'] ]),
277 'title' => $result['au_pseudonym'],
278 'slug' => $routeParams['user'] = $this->slugger->slug($result['au_pseudonym']),
279 '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']]),
280 'contact' => $this->router->generate('rapsys_air_contact', ['id' => $result['au_id'], 'user' => $routeParams['user']])
281 ],
282 'id' => $result['a_id'],
283 'canceled' => $result['a_canceled']
284 ];
285 }
286
287 //With snippet
288 if (!empty($result['p_id'])) {
289 $session['snippet'] = [
290 'id' => $result['p_id'],
291 'description' => $result['p_description'],
292 'class' => $result['p_class'],
293 'contact' => $result['p_contact'],
294 'donate' => $result['p_donate'],
295 'link' => $result['p_link'],
296 'profile' => $result['p_profile'],
297 'rate' => $result['p_rate'],
298 'hat' => $result['p_hat']
299 ];
300 }
301
302 //With applications
303 if (!empty($result['sa_id'])) {
304 //Extract applications id
305 $result['sa_id'] = explode("\n", $result['sa_id']);
306 //Extract applications score
307 //XXX: score may be null before grant or for bad behaviour, replace NULL with 'NULL' to avoid silent drop in mysql
308 $result['sa_score'] = array_map(function($v){return $v==='NULL'?null:$v;}, explode("\n", $result['sa_score']));
309 //Extract applications created
310 $result['sa_created'] = array_map(function($v){return new \DateTime($v);}, explode("\n", $result['sa_created']));
311 //Extract applications updated
312 $result['sa_updated'] = array_map(function($v){return new \DateTime($v);}, explode("\n", $result['sa_updated']));
313 //Extract applications canceled
314 //XXX: canceled is null before cancelation, replace NULL with 'NULL' to avoid silent drop in mysql
315 $result['sa_canceled'] = array_map(function($v){return $v==='NULL'?null:new \DateTime($v);}, explode("\n", $result['sa_canceled']));
316
317 //Extract applications user id
318 $result['sau_id'] = explode("\n", $result['sau_id']);
319 //Extract applications user pseudonym
320 $result['sau_pseudonym'] = explode("\n", $result['sau_pseudonym']);
321
322 //Init applications
323 $session['applications'] = [];
324
325 //Iterate on each applications id
326 foreach($result['sa_id'] as $i => $sa_id) {
327 $session['applications'][$sa_id] = [
328 'user' => null,
329 'score' => $result['sa_score'][$i],
330 'created' => $result['sa_created'][$i],
331 'updated' => $result['sa_updated'][$i],
332 'canceled' => $result['sa_canceled'][$i]
333 ];
334 if (!empty($result['sau_id'][$i])) {
335 $session['applications'][$sa_id]['user'] = [
336 'id' => $result['sau_id'][$i],
337 'title' => $result['sau_pseudonym'][$i],
338 'slug' => $this->slugger->slug($result['sau_pseudonym'][$i])
339 ];
340 }
341 }
342 }
343
344 //Set link
345 $session['link'] = $this->router->generate($route, $routeParams);
346
347 //Set canonical
348 $session['canonical'] = $this->router->generate($route, $routeParams, UrlGeneratorInterface::ABSOLUTE_URL);
349
350 //Set alternates
351 $session['alternates'] = [];
352
353 //Iterate on each locales
354 foreach($this->translator->getFallbackLocales() as $fallback) {
355 //Set titles
356 $titles = [];
357
358 //Set route params location
359 $routeParams['location'] = $this->slugger->slug($this->translator->trans($result['l_title'], [], null, $fallback));
360
361 //With route params dance
362 if (!empty($routeParams['dance'])) {
363 $routeParams['dance'] = $this->slugger->slug($this->translator->trans($result['ad_name'].' '.lcfirst($result['ad_type']), [], null, $fallback));
364 }
365
366 //With route params user
367 if (!empty($routeParams['user'])) {
368 $routeParams['user'] = $this->slugger->slug($result['au_pseudonym']);
369 }
370
371 //With current locale
372 if ($fallback === $locale) {
373 //Set current locale title
374 $titles[$locale] = $this->translator->trans($this->languages[$locale]);
375 //Without current locale
376 } else {
377 //Iterate on other locales
378 foreach(array_diff($this->translator->getFallbackLocales(), [$fallback]) as $other) {
379 //Set other locale title
380 $titles[$other] = $this->translator->trans($this->languages[$fallback], [], null, $other);
381 }
382
383 //Add alternates locale
384 $session['alternates'][str_replace('_', '-', $fallback)] = [
385 'absolute' => $this->router->generate($route, ['_locale' => $fallback]+$routeParams, UrlGeneratorInterface::ABSOLUTE_URL),
386 'relative' => $this->router->generate($route, ['_locale' => $fallback]+$routeParams),
387 'title' => implode('/', $titles),
388 'translated' => $this->translator->trans($this->languages[$fallback], [], null, $fallback)
389 ];
390 }
391
392 //Add alternates shorter locale
393 if (empty($parameters['alternates'][$shortFallback = substr($fallback, 0, 2)])) {
394 //Set locale locales context
395 $session['alternates'][$shortFallback] = [
396 'absolute' => $this->router->generate($route, ['_locale' => $fallback]+$routeParams, UrlGeneratorInterface::ABSOLUTE_URL),
397 'relative' => $this->router->generate($route, ['_locale' => $fallback]+$routeParams),
398 'title' => implode('/', $titles),
399 'translated' => $this->translator->trans($this->languages[$fallback], [], null, $fallback)
400 ];
401 }
402 }
403
404 //Return session
405 return $session;
406 }
407
408 /**
409 * Find sessions as calendar array by date period
410 *
411 * @param DatePeriod $period The date period
412 * @param string $locale The locale
413 * @param ?bool $granted The session is granted
414 * @param ?float $latitude The latitude
415 * @param ?float $longitude The longitude
416 * @param ?int $userId The user id
417 * @return array The session data
418 */
419
420 //TODO: calculer un titre de la page intelligent ?
421 //(utiliser la liste des villes par exemple ???)
422
423 //TODO: finir cette merde pour toutes les pages de listing !!!
424
425 //TODO: compute a max updated ???
426
427 //TODO: Γ  priori si l'userId est fourni, qu'on veut pas juste les granted et que l'userId correspond pas Γ  l'utilisateur Γ  qui Γ§a a Γ©tΓ© attribuΓ© et que la personne a demandΓ© la session, on a envie de lui afficher le disputed ???
428 //(ce serait mΓͺme plus un refused, voir le cas oΓΉ c'est pas encore attribuΓ©, comment Γ§a se passe ???)
429
430 //TODO: la gestion des sessions pas granted et contestΓ©es est Γ  faire au niveau du contrΓ΄leur userView si l'user est connectΓ©
431 //On peut utiliser le système des applications, l'id user dans application et l'id utilisateur connecté pour faire la détection
432 //Γ‡a se rΓ©sume Γ  coller un $class[] = 'disputed' ou 'lost' ? sur la session ???
433
434 #TODO: mettre en grisΓ© les sessions pas granted
435 #TODO: mettre en highlight (violet) les sessions de la location ou de l'user si fourni
436 //TODO: penser au cas oΓΉ on est sur un utilisateur, on veut mettre
437 //TODO: faire la dΓ©tection des sΓ©ances perdues par l'utilisateur ???
438 //XXX: Γ  priori Γ§a va se jouer au niveau du contrΓ΄leur oΓΉ on va setter Γ§a
439 /*} elseif (!empty($userId) && $session['au_id'] != $userId) {
440 $class[] = 'disputed';*/
441
442
443 public function findAllByPeriodAsArray(\DatePeriod $period, string $locale, ?bool $granted = null, ?float $latitude = null, ?float $longitude = null, ?int $userId = null) {
444 //Init granted sql
445 $grantSql = '';
446
447 //When granted is set
448 if (empty($granted)) {
449 //Set application and user as optional
450 $grantSql = 'LEFT ';
451 }
452
453 //Init location sql
454 $locationSql = '';
455
456 //When latitude and longitude
457 if ($latitude !== null && $longitude !== null) {
458 //Set the request
459 //XXX: get every location between 0 and 15 km of latitude and longitude
460 $req = <<<SQL
461 SELECT l.id
462 FROM RapsysAirBundle:Location AS l
463 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
464 SQL;
465
466 //Replace bundle entity name by table name
467 $req = str_replace($this->tableKeys, $this->tableValues, $req);
468
469 //Get result set mapping instance
470 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
471 $rsm = new ResultSetMapping();
472
473 //Declare all fields
474 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
475 //addScalarResult($sqlColName, $resColName, $type = 'string');
476 $rsm->addScalarResult('id', 'id', 'integer')
477 ->addIndexByScalar('id');
478
479 //Set location ids
480 //XXX: check that latitude and longitude have not be swapped !!!
481 //XXX: latitude ~= 48.x longitude ~= 2.x
482 $locationIds = array_keys(
483 $this->_em
484 ->createNativeQuery($req, $rsm)
485 ->setParameter('latitude', $latitude)
486 ->setParameter('longitude', $longitude)
487 ->getArrayResult()
488 );
489
490 //Add location id clause
491 $locationSql = "\n\t".'AND s.location_id IN (:lids)';
492 //When user id
493 } elseif ($userId !== null) {
494 //Set the request
495 //XXX: get every location between 0 and 15 km
496 $req = <<<SQL
497 SELECT l2.id
498 FROM (
499 SELECT l.id, l.latitude, l.longitude
500 FROM RapsysAirBundle:Application AS a
501 JOIN RapsysAirBundle:Session AS s ON (s.id = a.session_id)
502 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
503 WHERE a.user_id = :id
504 GROUP BY l.id
505 ORDER BY NULL
506 LIMIT 0, :limit
507 ) AS a
508 JOIN RapsysAirBundle:Location AS l2
509 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
510 GROUP BY l2.id
511 ORDER BY NULL
512 SQL;
513
514 //Replace bundle entity name by table name
515 $req = str_replace($this->tableKeys, $this->tableValues, $req);
516
517 //Get result set mapping instance
518 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
519 $rsm = new ResultSetMapping();
520
521 //Declare all fields
522 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
523 //addScalarResult($sqlColName, $resColName, $type = 'string');
524 $rsm->addScalarResult('id', 'id', 'integer')
525 ->addIndexByScalar('id');
526
527 //Set location ids
528 $locationIds = array_keys(
529 $this->_em
530 ->createNativeQuery($req, $rsm)
531 ->setParameter('id', $userId)
532 ->getArrayResult()
533 );
534
535 //With location ids
536 if (!empty($locationIds)) {
537 //Add location id clause
538 $locationSql = "\n\t".'AND s.location_id IN (:lids)';
539 }
540 }
541
542 //Set the request
543 $req = <<<SQL
544
545 SELECT
546 s.id,
547 s.date,
548 s.rainrisk,
549 s.rainfall,
550 s.realfeel,
551 s.temperature,
552 s.locked,
553 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
554 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
555 s.location_id AS l_id,
556 l.title AS l_title,
557 l.zipcode AS l_zipcode,
558 l.city AS l_city,
559 l.latitude AS l_latitude,
560 l.longitude AS l_longitude,
561 l.indoor AS l_indoor,
562 s.slot_id AS t_id,
563 t.title AS t_title,
564 s.application_id AS a_id,
565 a.canceled AS a_canceled,
566 a.dance_id AS ad_id,
567 ad.name AS ad_name,
568 ad.type AS ad_type,
569 a.user_id AS au_id,
570 au.pseudonym AS au_pseudonym,
571 p.rate AS p_rate,
572 p.hat AS p_hat,
573 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
574 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym,
575 GROUP_CONCAT(sa.dance_id ORDER BY sa.user_id SEPARATOR "\\n") AS sad_id,
576 GROUP_CONCAT(sad.name ORDER BY sa.user_id SEPARATOR "\\n") AS sad_name,
577 GROUP_CONCAT(sad.type ORDER BY sa.user_id SEPARATOR "\\n") AS sad_type,
578 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
579 FROM RapsysAirBundle:Session AS s
580 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
581 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
582 ${grantSql}JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
583 ${grantSql}JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
584 ${grantSql}JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
585 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
586 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
587 LEFT JOIN RapsysAirBundle:Dance AS sad ON (sad.id = sa.dance_id)
588 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
589 WHERE s.date BETWEEN :begin AND :end${locationSql}
590 GROUP BY s.id
591 ORDER BY NULL
592 SQL;
593
594 //Replace bundle entity name by table name
595 $req = str_replace($this->tableKeys, $this->tableValues, $req);
596
597 //Get result set mapping instance
598 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
599 $rsm = new ResultSetMapping();
600
601 //Declare all fields
602 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
603 //addScalarResult($sqlColName, $resColName, $type = 'string');
604 $rsm->addScalarResult('id', 'id', 'integer')
605 ->addScalarResult('date', 'date', 'date')
606 ->addScalarResult('rainrisk', 'rainrisk', 'float')
607 ->addScalarResult('rainfall', 'rainfall', 'float')
608 ->addScalarResult('realfeel', 'realfeel', 'float')
609 ->addScalarResult('temperature', 'temperature', 'float')
610 ->addScalarResult('locked', 'locked', 'datetime')
611 ->addScalarResult('start', 'start', 'datetime')
612 ->addScalarResult('stop', 'stop', 'datetime')
613 ->addScalarResult('modified', 'modified', 'datetime')
614 ->addScalarResult('t_id', 't_id', 'integer')
615 ->addScalarResult('t_title', 't_title', 'string')
616 ->addScalarResult('l_id', 'l_id', 'integer')
617 ->addScalarResult('l_title', 'l_title', 'string')
618 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
619 ->addScalarResult('l_city', 'l_city', 'string')
620 ->addScalarResult('l_latitude', 'l_latitude', 'float')
621 ->addScalarResult('l_longitude', 'l_longitude', 'float')
622 ->addScalarResult('l_indoor', 'l_indoor', 'boolean')
623 ->addScalarResult('a_id', 'a_id', 'integer')
624 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
625 ->addScalarResult('ad_id', 'ad_id', 'string')
626 ->addScalarResult('ad_name', 'ad_name', 'string')
627 ->addScalarResult('ad_type', 'ad_type', 'string')
628 ->addScalarResult('au_id', 'au_id', 'integer')
629 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
630 ->addScalarResult('p_rate', 'p_rate', 'integer')
631 ->addScalarResult('p_hat', 'p_hat', 'boolean')
632 //XXX: is a string because of \n separator
633 ->addScalarResult('sau_id', 'sau_id', 'string')
634 //XXX: is a string because of \n separator
635 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
636 //XXX: is a string because of \n separator
637 ->addScalarResult('sad_id', 'sad_id', 'string')
638 //XXX: is a string because of \n separator
639 ->addScalarResult('sad_name', 'sad_name', 'string')
640 //XXX: is a string because of \n separator
641 ->addScalarResult('sad_type', 'sad_type', 'string')
642 ->addIndexByScalar('id');
643
644 //Fetch result
645 $res = $this->_em
646 ->createNativeQuery($req, $rsm)
647 ->setParameter('begin', $period->getStartDate())
648 ->setParameter('end', $period->getEndDate())
649 ->setParameter('locale', $locale);
650
651 //Add optional location ids
652 if (!empty($locationIds)) {
653 $res->setParameter('lids', $locationIds);
654 }
655
656 //Get result
657 $result = $res->getResult();
658
659 //Init calendar
660 $calendar = [];
661
662 //Init month
663 $month = null;
664
665 //Set route
666 $route = 'rapsys_air_session_view';
667
668 //Iterate on each day
669 foreach($period as $date) {
670 //Init day in calendar
671 $calendar[$Ymd = $date->format('Ymd')] = [
672 'title' => $this->translator->trans($date->format('l')).' '.$date->format('d'),
673 'modified' => null,
674 'class' => [],
675 'sessions' => []
676 ];
677
678 //Detect month change
679 if ($month != $date->format('m')) {
680 $month = $date->format('m');
681 //Append month for first day of month
682 //XXX: except if today to avoid double add
683 if ($date->format('U') != strtotime('today')) {
684 $calendar[$Ymd]['title'] .= '/'.$month;
685 }
686 }
687 //Deal with today
688 if ($date->format('U') == ($today = strtotime('today'))) {
689 $calendar[$Ymd]['title'] .= '/'.$month;
690 $calendar[$Ymd]['current'] = true;
691 $calendar[$Ymd]['class'][] = 'current';
692 }
693 //Disable passed days
694 if ($date->format('U') < $today) {
695 $calendar[$Ymd]['disabled'] = true;
696 $calendar[$Ymd]['class'][] = 'disabled';
697 }
698 //Set next month days
699 if ($date->format('m') > date('m')) {
700 $calendar[$Ymd]['next'] = true;
701 #$calendar[$Ymd]['class'][] = 'next';
702 }
703
704 //Detect sunday
705 if ($date->format('w') == 0) {
706 $calendar[$Ymd]['class'][] = 'sunday';
707 }
708
709 //Iterate on each session to find the one of the day
710 foreach($result as $session) {
711 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
712 //With empty or greatest modified
713 if ($calendar[$Ymd]['modified'] === null || $session['modified'] >= $calendar[$Ymd]['modified']) {
714 //Update modified
715 $calendar[$Ymd]['modified'] = $session['modified'];
716 }
717
718 //Set applications
719 $applications = array_combine($candidates = explode("\n", $session['sau_id']), explode("\n", $session['sau_pseudonym']));
720
721 //Compute classes
722 $class = [];
723
724 //With locked
725 if (!empty($session['locked'])) {
726 $class[] = 'locked';
727 //Without locked
728 } else {
729 //With application
730 if (!empty($session['a_id'])) {
731 //With canceled session
732 if (!empty($session['a_canceled'])) {
733 $class[] = 'canceled';
734 //With disputed session
735 } elseif ($userId !== null && $session['au_id'] != $userId && !empty($candidates[$userId])) {
736 $class[] = 'disputed';
737 //Session is granted
738 } else {
739 $class[] = 'granted';
740 }
741
742 //With user id
743 if ($userId !== null && $session['au_id'] == $userId) {
744 $class[] = 'highlight';
745 }
746 } else {
747 $class[] = 'pending';
748 }
749
750 //With latitude and longitude
751 if ($latitude !== null && $longitude !== null && $session['l_latitude'] == $latitude && $session['l_longitude'] == $longitude) {
752 $class[] = 'highlight';
753 }
754 }
755
756 //Set temperature
757 $temperature = [
758 'glyph' => self::GLYPHS['Cleary'],
759 'title' => []
760 ];
761
762 //Compute temperature glyph
763 //XXX: temperature may be null
764 if ($session['temperature'] >= 17 && $session['temperature'] < 24) {
765 $temperature['glyph'] = self::GLYPHS['Sunny'];
766 } elseif ($session['temperature'] >= 10 && $session['temperature'] < 17) {
767 $temperature['glyph'] = self::GLYPHS['Cloudy'];
768 } elseif ($session['temperature'] !== null && $session['temperature'] < 10) {
769 $temperature['glyph'] = self::GLYPHS['Winty'];
770 }
771
772 //Check if temperature is available
773 if ($session['temperature'] !== null) {
774 $temperature['title'][] = $session['temperature'].'Β°C';
775 }
776
777 //Check if realfeel is available
778 if ($session['realfeel'] !== null) {
779 $temperature['title'][] = $session['realfeel'].'Β°R';
780 }
781
782 //Compute temperature title
783 $temperature['title'] = implode(' ', $temperature['title']);
784
785 //Set rain
786 $rain = [
787 'glyph' => self::GLYPHS['Cleary'],
788 'title' => []
789 ];
790
791 //Compute rain glyph
792 //XXX: rainfall and rainrisk may be null
793 if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
794 $rain['glyph'] = self::GLYPHS['Stormy'];
795 } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
796 $rain['glyph'] = self::GLYPHS['Rainy'];
797 }
798
799 //Check if rainrisk is available
800 if ($session['rainrisk'] !== null) {
801 $rain['title'][] = ($session['rainrisk']*100).'%';
802 }
803
804 //Check if rainfall is available
805 if ($session['rainfall'] !== null) {
806 $rain['title'][] = $session['rainfall'].'mm';
807 }
808
809 //Compute rain title
810 $rain['title'] = implode(' ', $rain['title']);
811
812 //Set application
813 $application = null;
814
815 //Set rate
816 $rate = null;
817
818 //Set route params
819 $routeParams = ['id' => $session['id'], 'location' => $this->slugger->slug($this->translator->trans($session['l_title']))];
820
821 //With application
822 if (!empty($session['a_id'])) {
823 //Set dance
824 $routeParams['dance'] = $this->slugger->slug($dance = $this->translator->trans($session['ad_name'].' '.lcfirst($session['ad_type'])));
825
826 //Set user
827 $routeParams['user'] = $this->slugger->slug($session['au_pseudonym']);
828
829 //Set title
830 $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'])]);
831
832 //Set pseudonym
833 $application = [
834 'dance' => [
835 'id' => $session['ad_id'],
836 'name' => $this->translator->trans($session['ad_name']),
837 'type' => $this->translator->trans($session['ad_type']),
838 'title' => $dance
839 ],
840 'user' => [
841 'id' => $session['au_id'],
842 'title' => $session['au_pseudonym']
843 ]
844 ];
845
846 //Set rate
847 $rate = [
848 'glyph' => self::GLYPHS['Free'],
849 'rate' => null,
850 'title' => $this->translator->trans('Free')
851 ];
852
853 //With hat
854 if (!empty($session['p_hat'])) {
855 //Set glyph
856 $rate['glyph'] = self::GLYPHS['Hat'];
857
858 //With rate
859 if (!empty($session['p_rate'])) {
860 //Set rate
861 $rate['rate'] = $session['p_rate'];
862
863 //Set title
864 $rate['title'] = $this->translator->trans('To the hat, ideally %rate% €', ['%rate%' => $session['p_rate']]);
865 //Without rate
866 } else {
867 //Set title
868 $rate['title'] = $this->translator->trans('To the hat');
869 }
870 //With rate
871 } elseif (!empty($session['p_rate'])) {
872 //Set glyph
873 $rate['glyph'] = self::GLYPHS['Euro'];
874
875 //Set rate
876 $rate['rate'] = $session['p_rate'];
877
878 //Set title
879 $rate['title'] = $session['p_rate'].' €';
880 }
881 //With unique application
882 } elseif (count($applications) == 1) {
883 //Set dance
884 $dance = $this->translator->trans($session['sad_name'].' '.lcfirst($session['sad_type']));
885
886 //Set title
887 $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'])]);
888
889 //Set pseudonym
890 $application = [
891 'dance' => [
892 'id' => $session['sad_id'],
893 'name' => $this->translator->trans($session['sad_name']),
894 'type' => $this->translator->trans($session['sad_type']),
895 'title' => $dance
896 ],
897 'user' => [
898 'id' => $session['sau_id'],
899 'title' => $session['sau_pseudonym']
900 ]
901 ];
902
903 //TODO: glyph stuff ???
904 //Without application
905 } else {
906 //Set title
907 $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'])]);
908 }
909
910 //Add the session
911 $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%05d', $session['id'])] = [
912 'id' => $session['id'],
913 'start' => $session['start'],
914 'stop' => $session['stop'],
915 'class' => $class,
916 'temperature' => $temperature,
917 'rain' => $rain,
918 'title' => $title,
919 'link' => $this->router->generate($route, $routeParams),
920 'location' => [
921 'id' => $session['l_id'],
922 'title' => $this->translator->trans($session['l_title']),
923 'latitude' => $session['l_latitude'],
924 'longitude' => $session['l_longitude'],
925 'indoor' => $session['l_indoor'],
926 'at' => $at = $this->translator->trans('at '.$session['l_title']),
927 'in' => $in = $this->translator->trans('in '.$session['l_city']),
928 'atin' => $at.' '.$in,
929 'city' => $session['l_city'],
930 'zipcode' => $session['l_zipcode']
931 ],
932 'application' => $application,
933 'slot' => [
934 'glyph' => self::GLYPHS[$session['t_title']],
935 'title' => $this->translator->trans($session['t_title'])
936 ],
937 'rate' => $rate,
938 'applications' => $applications
939 ];
940 }
941 }
942
943 //Sort sessions
944 ksort($calendar[$Ymd]['sessions']);
945 }
946
947 //Send result
948 return $calendar;
949 }
950
951 /**
952 * Find session by location, slot and date
953 *
954 * @param $location The location
955 * @param $slot The slot
956 * @param $date The datetime
957 */
958 public function findOneByLocationSlotDate($location, $slot, $date) {
959 //Return sessions
960 return $this->getEntityManager()
961 ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.slot = :slot AND s.date = :date)')
962 ->setParameter('location', $location)
963 ->setParameter('slot', $slot)
964 ->setParameter('date', $date)
965 ->getSingleResult();
966 }
967
968 /**
969 * Find sessions by date period
970 *
971 * @param $period The date period
972 */
973 public function findAllByDatePeriod($period) {
974 //Return sessions
975 return $this->getEntityManager()
976 ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE s.date BETWEEN :begin AND :end')
977 ->setParameter('begin', $period->getStartDate())
978 ->setParameter('end', $period->getEndDate())
979 ->getResult();
980 }
981
982 /**
983 * Find sessions by location and date period
984 *
985 * @param $location The location
986 * @param $period The date period
987 */
988 public function findAllByLocationDatePeriod($location, $period) {
989 //Return sessions
990 return $this->getEntityManager()
991 ->createQuery('SELECT s FROM RapsysAirBundle:Session s WHERE (s.location = :location AND s.date BETWEEN :begin AND :end)')
992 ->setParameter('location', $location)
993 ->setParameter('begin', $period->getStartDate())
994 ->setParameter('end', $period->getEndDate())
995 ->getResult();
996 }
997
998 /**
999 * Find one session by location and user id within last month
1000 *
1001 * @param $location The location id
1002 * @param $user The user id
1003 */
1004 public function findOneWithinLastMonthByLocationUser($location, $user) {
1005 //Set the request
1006 //XXX: give the gooddelay to guest just in case
1007 $req =<<<SQL
1008 SELECT s.id
1009 FROM RapsysAirBundle:Session s
1010 JOIN RapsysAirBundle:Application a ON (a.id = s.application_id AND a.user_id = :uid AND (a.canceled IS NULL OR TIMESTAMPDIFF(DAY, a.canceled, ADDTIME(s.date, s.begin)) < 1))
1011 WHERE s.location_id = :lid AND s.date >= DATE_ADD(DATE_SUB(NOW(), INTERVAL 1 MONTH), INTERVAL :gooddelay DAY)
1012 SQL;
1013
1014 //Replace bundle entity name by table name
1015 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1016
1017 //Get result set mapping instance
1018 $rsm = new ResultSetMapping();
1019
1020 //Declare all fields
1021 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1022 $rsm->addScalarResult('id', 'id', 'integer')
1023 ->addIndexByScalar('id');
1024
1025 //Return result
1026 return $this->_em
1027 ->createNativeQuery($req, $rsm)
1028 ->setParameter('lid', $location)
1029 ->setParameter('uid', $user)
1030 ->setParameter('gooddelay', self::SENIOR_DELAY)
1031 ->getOneOrNullResult();
1032 }
1033
1034 /**
1035 * Fetch sessions by date period
1036 *
1037 * @param $period The date period
1038 * @param $locale The locale
1039 */
1040 public function fetchAllByDatePeriod($period, $locale = null) {
1041 //Set the request
1042 //TODO: exclude opera and others ?
1043 $req = <<<SQL
1044 SELECT
1045 s.id,
1046 s.date,
1047 s.locked,
1048 s.updated,
1049 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
1050 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
1051 s.location_id AS l_id,
1052 l.address AS l_address,
1053 l.zipcode AS l_zipcode,
1054 l.city AS l_city,
1055 l.title AS l_title,
1056 l.description AS l_description,
1057 l.latitude AS l_latitude,
1058 l.longitude AS l_longitude,
1059 s.application_id AS a_id,
1060 a.canceled AS a_canceled,
1061 ad.name AS ad_name,
1062 ad.type AS ad_type,
1063 a.user_id AS au_id,
1064 au.forename AS au_forename,
1065 au.pseudonym AS au_pseudonym,
1066 p.id AS p_id,
1067 p.description AS p_description,
1068 p.class AS p_class,
1069 p.short AS p_short,
1070 p.hat AS p_hat,
1071 p.rate AS p_rate,
1072 p.contact AS p_contact,
1073 p.donate AS p_donate,
1074 p.link AS p_link,
1075 p.profile AS p_profile
1076 FROM RapsysAirBundle:Session AS s
1077 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1078 JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
1079 JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
1080 JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
1081 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
1082 WHERE s.date BETWEEN :begin AND :end
1083 ORDER BY NULL
1084 SQL;
1085
1086 //Replace bundle entity name by table name
1087 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1088
1089 //Get result set mapping instance
1090 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1091 $rsm = new ResultSetMapping();
1092
1093 //Declare all fields
1094 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1095 //addScalarResult($sqlColName, $resColName, $type = 'string');
1096 $rsm->addScalarResult('id', 'id', 'integer')
1097 ->addScalarResult('date', 'date', 'date')
1098 ->addScalarResult('locked', 'locked', 'datetime')
1099 ->addScalarResult('updated', 'updated', 'datetime')
1100 ->addScalarResult('start', 'start', 'datetime')
1101 ->addScalarResult('stop', 'stop', 'datetime')
1102 ->addScalarResult('l_id', 'l_id', 'integer')
1103 ->addScalarResult('l_address', 'l_address', 'string')
1104 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
1105 ->addScalarResult('l_city', 'l_city', 'string')
1106 ->addScalarResult('l_latitude', 'l_latitude', 'float')
1107 ->addScalarResult('l_longitude', 'l_longitude', 'float')
1108 ->addScalarResult('l_title', 'l_title', 'string')
1109 ->addScalarResult('l_description', 'l_description', 'string')
1110 ->addScalarResult('t_id', 't_id', 'integer')
1111 ->addScalarResult('t_title', 't_title', 'string')
1112 ->addScalarResult('a_id', 'a_id', 'integer')
1113 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
1114 ->addScalarResult('ad_name', 'ad_name', 'string')
1115 ->addScalarResult('ad_type', 'ad_type', 'string')
1116 ->addScalarResult('au_id', 'au_id', 'integer')
1117 ->addScalarResult('au_forename', 'au_forename', 'string')
1118 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
1119 ->addScalarResult('p_id', 'p_id', 'integer')
1120 ->addScalarResult('p_description', 'p_description', 'string')
1121 ->addScalarResult('p_class', 'p_class', 'string')
1122 ->addScalarResult('p_short', 'p_short', 'string')
1123 ->addScalarResult('p_hat', 'p_hat', 'integer')
1124 ->addScalarResult('p_rate', 'p_rate', 'integer')
1125 ->addScalarResult('p_contact', 'p_contact', 'string')
1126 ->addScalarResult('p_donate', 'p_donate', 'string')
1127 ->addScalarResult('p_link', 'p_link', 'string')
1128 ->addScalarResult('p_profile', 'p_profile', 'string')
1129 ->addIndexByScalar('id');
1130
1131 //Fetch result
1132 $res = $this->_em
1133 ->createNativeQuery($req, $rsm)
1134 ->setParameter('begin', $period->getStartDate())
1135 ->setParameter('end', $period->getEndDate())
1136 ->setParameter('locale', $locale);
1137
1138 //Return result
1139 return $res->getResult();
1140 }
1141
1142 /**
1143 * Fetch session by id
1144 *
1145 * @todo drop fetchOneById when migration is complete
1146 * @todo FIX the updated computation, we can drop the group_concat for a MAX()
1147 * @XXX DO NOT ADD a.updated as we already compute that with applications !!!
1148 *
1149 * @param $id The session id
1150 * @param $locale The locale
1151 * @return array The session data
1152 */
1153 public function fetchOneById($id, $locale = null) {
1154 //Set the request
1155 //TODO: compute scores ?
1156 //TODO: compute delivery date ? (J-3/J-4 ?)
1157 $req =<<<SQL
1158 SELECT
1159 s.id,
1160 s.date,
1161 s.begin,
1162 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
1163 s.length,
1164 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
1165 s.rainfall,
1166 s.rainrisk,
1167 s.realfeel,
1168 s.realfeelmin,
1169 s.realfeelmax,
1170 s.temperature,
1171 s.temperaturemin,
1172 s.temperaturemax,
1173 s.locked,
1174 s.created,
1175 s.updated,
1176 s.location_id AS l_id,
1177 l.title AS l_title,
1178 l.description AS l_description,
1179 l.address AS l_address,
1180 l.zipcode AS l_zipcode,
1181 l.city AS l_city,
1182 l.latitude AS l_latitude,
1183 l.longitude AS l_longitude,
1184 l.indoor AS l_indoor,
1185 l.updated AS l_updated,
1186 s.slot_id AS t_id,
1187 t.title AS t_title,
1188 t.updated AS t_updated,
1189 s.application_id AS a_id,
1190 a.canceled AS a_canceled,
1191 a.dance_id AS ad_id,
1192 ad.name AS ad_name,
1193 ad.type AS ad_type,
1194 ad.updated AS ad_updated,
1195 a.user_id AS au_id,
1196 au.pseudonym AS au_pseudonym,
1197 p.id AS p_id,
1198 p.description AS p_description,
1199 p.class AS p_class,
1200 p.contact AS p_contact,
1201 p.donate AS p_donate,
1202 p.link AS p_link,
1203 p.profile AS p_profile,
1204 p.rate AS p_rate,
1205 p.hat AS p_hat,
1206 p.updated AS p_updated,
1207 GROUP_CONCAT(sa.id ORDER BY sa.user_id SEPARATOR "\\n") AS sa_id,
1208 GROUP_CONCAT(IFNULL(sa.score, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_score,
1209 GROUP_CONCAT(sa.created ORDER BY sa.user_id SEPARATOR "\\n") AS sa_created,
1210 GROUP_CONCAT(sa.updated ORDER BY sa.user_id SEPARATOR "\\n") AS sa_updated,
1211 GROUP_CONCAT(IFNULL(sa.canceled, 'NULL') ORDER BY sa.user_id SEPARATOR "\\n") AS sa_canceled,
1212 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
1213 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
1214 FROM RapsysAirBundle:Session AS s
1215 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1216 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
1217 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
1218 LEFT JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
1219 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
1220 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
1221 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
1222 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
1223 WHERE s.id = :id
1224 GROUP BY s.id
1225 ORDER BY NULL
1226 SQL;
1227
1228 //Replace bundle entity name by table name
1229 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1230
1231 //Get result set mapping instance
1232 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1233 $rsm = new ResultSetMapping();
1234
1235 //Declare all fields
1236 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1237 $rsm->addScalarResult('id', 'id', 'integer')
1238 ->addScalarResult('date', 'date', 'date')
1239 ->addScalarResult('begin', 'begin', 'time')
1240 ->addScalarResult('start', 'start', 'datetime')
1241 ->addScalarResult('length', 'length', 'time')
1242 ->addScalarResult('stop', 'stop', 'datetime')
1243 ->addScalarResult('rainfall', 'rainfall', 'float')
1244 ->addScalarResult('rainrisk', 'rainrisk', 'float')
1245 ->addScalarResult('realfeel', 'realfeel', 'float')
1246 ->addScalarResult('realfeelmin', 'realfeelmin', 'float')
1247 ->addScalarResult('realfeelmax', 'realfeelmax', 'float')
1248 ->addScalarResult('temperature', 'temperature', 'float')
1249 ->addScalarResult('temperaturemin', 'temperaturemin', 'float')
1250 ->addScalarResult('temperaturemax', 'temperaturemax', 'float')
1251 ->addScalarResult('locked', 'locked', 'datetime')
1252 ->addScalarResult('created', 'created', 'datetime')
1253 ->addScalarResult('updated', 'updated', 'datetime')
1254 ->addScalarResult('l_id', 'l_id', 'integer')
1255 ->addScalarResult('l_title', 'l_title', 'string')
1256 ->addScalarResult('l_description', 'l_description', 'string')
1257 ->addScalarResult('l_address', 'l_address', 'string')
1258 ->addScalarResult('l_zipcode', 'l_zipcode', 'string')
1259 ->addScalarResult('l_city', 'l_city', 'string')
1260 ->addScalarResult('l_latitude', 'l_latitude', 'float')
1261 ->addScalarResult('l_longitude', 'l_longitude', 'float')
1262 ->addScalarResult('l_indoor', 'l_indoor', 'boolean')
1263 ->addScalarResult('l_updated', 'l_updated', 'datetime')
1264 ->addScalarResult('t_id', 't_id', 'integer')
1265 ->addScalarResult('t_title', 't_title', 'string')
1266 ->addScalarResult('t_updated', 't_updated', 'datetime')
1267 ->addScalarResult('a_id', 'a_id', 'integer')
1268 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
1269 ->addScalarResult('ad_id', 'ad_id', 'integer')
1270 ->addScalarResult('ad_name', 'ad_name', 'string')
1271 ->addScalarResult('ad_type', 'ad_type', 'string')
1272 ->addScalarResult('ad_updated', 'ad_updated', 'datetime')
1273 ->addScalarResult('au_id', 'au_id', 'integer')
1274 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
1275 ->addScalarResult('p_id', 'p_id', 'integer')
1276 ->addScalarResult('p_description', 'p_description', 'text')
1277 ->addScalarResult('p_class', 'p_class', 'text')
1278 ->addScalarResult('p_contact', 'p_contact', 'text')
1279 ->addScalarResult('p_donate', 'p_donate', 'text')
1280 ->addScalarResult('p_link', 'p_link', 'text')
1281 ->addScalarResult('p_profile', 'p_profile', 'text')
1282 ->addScalarResult('p_rate', 'p_rate', 'integer')
1283 ->addScalarResult('p_hat', 'p_hat', 'boolean')
1284 ->addScalarResult('p_updated', 'p_updated', 'datetime')
1285 //XXX: is a string because of \n separator
1286 ->addScalarResult('sa_id', 'sa_id', 'string')
1287 //XXX: is a string because of \n separator
1288 ->addScalarResult('sa_score', 'sa_score', 'string')
1289 //XXX: is a string because of \n separator
1290 ->addScalarResult('sa_created', 'sa_created', 'string')
1291 //XXX: is a string because of \n separator
1292 ->addScalarResult('sa_updated', 'sa_updated', 'string')
1293 //XXX: is a string because of \n separator
1294 ->addScalarResult('sa_canceled', 'sa_canceled', 'string')
1295 //XXX: is a string because of \n separator
1296 ->addScalarResult('sau_id', 'sau_id', 'string')
1297 //XXX: is a string because of \n separator
1298 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
1299 ->addIndexByScalar('id');
1300
1301 //Set result
1302 return $this->_em
1303 ->createNativeQuery($req, $rsm)
1304 ->setParameter('id', $id)
1305 ->setParameter('locale', $locale)
1306 ->getOneOrNullResult();
1307 }
1308
1309 /**
1310 * Fetch sessions calendar with translated location by date period
1311 *
1312 * @param $period The date period
1313 * @param $locationId The location id
1314 * @param $sessionId The session id
1315 * @param $granted The session is granted
1316 */
1317 public function fetchCalendarByDatePeriod($period, $locationId = null, $sessionId = null, $granted = false, $locale = null) {
1318 //Init granted sql
1319 $grantSql = '';
1320
1321 //When granted is set
1322 if (empty($granted)) {
1323 //Set application and user as optional
1324 $grantSql = 'LEFT ';
1325 }
1326
1327 //Init location sql
1328 $locationSql = '';
1329
1330 //When location id is set
1331 if (!empty($locationId)) {
1332 //Add location id clause
1333 $locationSql = "\n\t".'AND s.location_id = :lid';
1334 }
1335
1336 //Set the request
1337 $req = <<<SQL
1338
1339 SELECT
1340 s.id,
1341 s.date,
1342 s.rainrisk,
1343 s.rainfall,
1344 s.realfeel,
1345 s.temperature,
1346 s.locked,
1347 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
1348 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
1349 s.location_id AS l_id,
1350 l.title AS l_title,
1351 s.slot_id AS t_id,
1352 t.title AS t_title,
1353 s.application_id AS a_id,
1354 a.canceled AS a_canceled,
1355 ad.name AS ad_name,
1356 ad.type AS ad_type,
1357 a.user_id AS au_id,
1358 au.pseudonym AS au_pseudonym,
1359 p.rate AS p_rate,
1360 p.hat AS p_hat,
1361 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
1362 GROUP_CONCAT(sau.pseudonym ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
1363 FROM RapsysAirBundle:Session AS s
1364 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1365 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
1366 ${grantSql}JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
1367 ${grantSql}JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
1368 ${grantSql}JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
1369 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
1370 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
1371 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
1372 WHERE s.date BETWEEN :begin AND :end${locationSql}
1373 GROUP BY s.id
1374 ORDER BY NULL
1375 SQL;
1376
1377 //Replace bundle entity name by table name
1378 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1379
1380 //Get result set mapping instance
1381 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1382 $rsm = new ResultSetMapping();
1383
1384 //Declare all fields
1385 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1386 //addScalarResult($sqlColName, $resColName, $type = 'string');
1387 $rsm->addScalarResult('id', 'id', 'integer')
1388 ->addScalarResult('date', 'date', 'date')
1389 ->addScalarResult('rainrisk', 'rainrisk', 'float')
1390 ->addScalarResult('rainfall', 'rainfall', 'float')
1391 ->addScalarResult('realfeel', 'realfeel', 'float')
1392 ->addScalarResult('temperature', 'temperature', 'float')
1393 ->addScalarResult('locked', 'locked', 'datetime')
1394 ->addScalarResult('start', 'start', 'datetime')
1395 ->addScalarResult('stop', 'stop', 'datetime')
1396 ->addScalarResult('t_id', 't_id', 'integer')
1397 ->addScalarResult('t_title', 't_title', 'string')
1398 ->addScalarResult('l_id', 'l_id', 'integer')
1399 ->addScalarResult('l_title', 'l_title', 'string')
1400 ->addScalarResult('a_id', 'a_id', 'integer')
1401 ->addScalarResult('a_canceled', 'a_canceled', 'datetime')
1402 ->addScalarResult('ad_name', 'ad_name', 'string')
1403 ->addScalarResult('ad_type', 'ad_type', 'string')
1404 ->addScalarResult('au_id', 'au_id', 'integer')
1405 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
1406 ->addScalarResult('p_rate', 'p_rate', 'integer')
1407 ->addScalarResult('p_hat', 'p_hat', 'boolean')
1408 //XXX: is a string because of \n separator
1409 ->addScalarResult('sau_id', 'sau_id', 'string')
1410 //XXX: is a string because of \n separator
1411 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
1412 ->addIndexByScalar('id');
1413
1414 //Fetch result
1415 $res = $this->_em
1416 ->createNativeQuery($req, $rsm)
1417 ->setParameter('begin', $period->getStartDate())
1418 ->setParameter('end', $period->getEndDate())
1419 ->setParameter('locale', $locale);
1420
1421 //Add optional location id
1422 if (!empty($locationId)) {
1423 $res->setParameter('lid', $locationId);
1424 }
1425
1426 //Get result
1427 $res = $res->getResult();
1428
1429 //Init calendar
1430 $calendar = [];
1431
1432 //Init month
1433 $month = null;
1434
1435 //Iterate on each day
1436 foreach($period as $date) {
1437 //Init day in calendar
1438 $calendar[$Ymd = $date->format('Ymd')] = [
1439 'title' => $this->translator->trans($date->format('l')).' '.$date->format('d'),
1440 'class' => [],
1441 'sessions' => []
1442 ];
1443
1444 //Detect month change
1445 if ($month != $date->format('m')) {
1446 $month = $date->format('m');
1447 //Append month for first day of month
1448 //XXX: except if today to avoid double add
1449 if ($date->format('U') != strtotime('today')) {
1450 $calendar[$Ymd]['title'] .= '/'.$month;
1451 }
1452 }
1453 //Deal with today
1454 if ($date->format('U') == ($today = strtotime('today'))) {
1455 $calendar[$Ymd]['title'] .= '/'.$month;
1456 $calendar[$Ymd]['current'] = true;
1457 $calendar[$Ymd]['class'][] = 'current';
1458 }
1459 //Disable passed days
1460 if ($date->format('U') < $today) {
1461 $calendar[$Ymd]['disabled'] = true;
1462 $calendar[$Ymd]['class'][] = 'disabled';
1463 }
1464 //Set next month days
1465 if ($date->format('m') > date('m')) {
1466 $calendar[$Ymd]['next'] = true;
1467 #$calendar[$Ymd]['class'][] = 'next';
1468 }
1469
1470 //Detect sunday
1471 if ($date->format('w') == 0) {
1472 $calendar[$Ymd]['class'][] = 'sunday';
1473 }
1474
1475 //Iterate on each session to find the one of the day
1476 foreach($res as $session) {
1477 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
1478 //Count number of application
1479 $count = count(explode("\n", $session['sau_id']));
1480
1481 //Compute classes
1482 $class = [];
1483 if (!empty($session['a_id'])) {
1484 $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
1485 if (!empty($session['a_canceled'])) {
1486 $class[] = 'canceled';
1487 } else {
1488 $class[] = 'granted';
1489 }
1490 } elseif ($count > 1) {
1491 $class[] = 'disputed';
1492 } elseif (!empty($session['locked'])) {
1493 $class[] = 'locked';
1494 } else {
1495 $class[] = 'pending';
1496 }
1497
1498 if ($sessionId == $session['id']) {
1499 $class[] = 'highlight';
1500 }
1501
1502 //Set temperature
1503 //XXX: realfeel may be null, temperature should not
1504 $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
1505
1506 //Compute weather
1507 //XXX: rainfall may be null
1508 if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
1509 $weather = self::GLYPHS['Stormy'];
1510 } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
1511 $weather = self::GLYPHS['Rainy'];
1512 } elseif ($temperature > 24) {
1513 $weather = self::GLYPHS['Cleary'];
1514 } elseif ($temperature > 17) {
1515 $weather = self::GLYPHS['Sunny'];
1516 } elseif ($temperature > 10) {
1517 $weather = self::GLYPHS['Cloudy'];
1518 } elseif ($temperature !== null) {
1519 $weather = self::GLYPHS['Winty'];
1520 } else {
1521 $weather = null;
1522 }
1523
1524 //Init weathertitle
1525 $weathertitle = [];
1526
1527 //Check if realfeel is available
1528 if ($session['realfeel'] !== null) {
1529 $weathertitle[] = $session['realfeel'].'Β°R';
1530 }
1531
1532 //Check if temperature is available
1533 if ($session['temperature'] !== null) {
1534 $weathertitle[] = $session['temperature'].'Β°C';
1535 }
1536
1537 //Check if rainrisk is available
1538 if ($session['rainrisk'] !== null) {
1539 $weathertitle[] = ($session['rainrisk']*100).'%';
1540 }
1541
1542 //Check if rainfall is available
1543 if ($session['rainfall'] !== null) {
1544 $weathertitle[] = $session['rainfall'].'mm';
1545 }
1546
1547 //Set applications
1548 $applications = [
1549 0 => $this->translator->trans($session['t_title']).' '.$this->translator->trans('at '.$session['l_title']).$this->translator->trans(':')
1550 ];
1551
1552 //Fetch pseudonyms from session applications
1553 $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
1554
1555 //Set dance
1556 $dance = null;
1557
1558 //Set pseudonym
1559 $pseudonym = null;
1560
1561 //Check that session is not granted
1562 if (empty($session['a_id'])) {
1563 //With location id and unique application
1564 if ($count == 1) {
1565 //Set unique application pseudonym
1566 $pseudonym = $session['sau_pseudonym'];
1567 }
1568 //Session is granted
1569 } else {
1570 //Replace granted application
1571 $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
1572
1573 //Set dance
1574 $dance = $this->translator->trans($session['ad_name'].' '.lcfirst($session['ad_type']));
1575
1576 //Set pseudonym
1577 $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
1578 }
1579
1580 //Add the session
1581 $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%05d', $session['id'])] = [
1582 'id' => $session['id'],
1583 'start' => $session['start'],
1584 'stop' => $session['stop'],
1585 'location' => $this->translator->trans($session['l_title']),
1586 'dance' => $dance,
1587 'pseudonym' => $pseudonym,
1588 'class' => $class,
1589 'slot' => self::GLYPHS[$session['t_title']],
1590 'slottitle' => $this->translator->trans($session['t_title']),
1591 'weather' => $weather,
1592 'weathertitle' => implode(' ', $weathertitle),
1593 'applications' => $applications,
1594 'rate' => $session['p_rate'],
1595 'hat' => $session['p_hat']
1596 ];
1597 }
1598 }
1599
1600 //Sort sessions
1601 ksort($calendar[$Ymd]['sessions']);
1602 }
1603
1604 //Send result
1605 return $calendar;
1606 }
1607
1608 /**
1609 * Fetch sessions calendar with translated location by date period and user
1610 *
1611 * @param $period The date period
1612 * @param $userId The user id
1613 * @param $sessionId The session id
1614 */
1615 public function fetchUserCalendarByDatePeriod($period, $userId = null, $sessionId = null, $locale = null) {
1616 //Init user sql
1617 $userJoinSql = $userWhereSql = '';
1618
1619 //When user id is set
1620 if (!empty($userId)) {
1621 //Add user join
1622 $userJoinSql = 'JOIN RapsysAirBundle:Application AS sua ON (sua.session_id = s.id)'."\n";
1623 //Add user id clause
1624 $userWhereSql = "\n\t".'AND sua.user_id = :uid';
1625 }
1626
1627 //Set the request
1628 //TODO: change as_u_* in sau_*, a_u_* in au_*, etc, see request up
1629 $req = <<<SQL
1630 SELECT
1631 s.id,
1632 s.date,
1633 s.rainrisk,
1634 s.rainfall,
1635 s.realfeel,
1636 s.temperature,
1637 s.locked,
1638 ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS start,
1639 ADDDATE(ADDTIME(ADDTIME(s.date, s.begin), s.length), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY) AS stop,
1640 s.location_id AS l_id,
1641 l.title AS l_title,
1642 s.slot_id AS t_id,
1643 t.title AS t_title,
1644 s.application_id AS a_id,
1645 ad.name AS ad_name,
1646 ad.type AS ad_type,
1647 a.user_id AS au_id,
1648 au.pseudonym AS au_pseudonym,
1649 p.rate AS p_rate,
1650 p.hat AS p_hat,
1651 GROUP_CONCAT(sa.user_id ORDER BY sa.user_id SEPARATOR "\\n") AS sau_id,
1652 GROUP_CONCAT(CONCAT("- ", sau.pseudonym) ORDER BY sa.user_id SEPARATOR "\\n") AS sau_pseudonym
1653 FROM RapsysAirBundle:Session AS s
1654 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1655 JOIN RapsysAirBundle:Slot AS t ON (t.id = s.slot_id)
1656 ${userJoinSql}LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id)
1657 LEFT JOIN RapsysAirBundle:Snippet AS p ON (p.location_id = s.location_id AND p.user_id = a.user_id AND p.locale = :locale)
1658 LEFT JOIN RapsysAirBundle:Dance AS ad ON (ad.id = a.dance_id)
1659 LEFT JOIN RapsysAirBundle:User AS au ON (au.id = a.user_id)
1660 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
1661 LEFT JOIN RapsysAirBundle:User AS sau ON (sau.id = sa.user_id)
1662 WHERE s.date BETWEEN :begin AND :end${userWhereSql}
1663 GROUP BY s.id
1664 ORDER BY NULL
1665 SQL;
1666
1667 //Replace bundle entity name by table name
1668 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1669
1670 //Get result set mapping instance
1671 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
1672 $rsm = new ResultSetMapping();
1673
1674 //Declare all fields
1675 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
1676 //addScalarResult($sqlColName, $resColName, $type = 'string');
1677 $rsm->addScalarResult('id', 'id', 'integer')
1678 ->addScalarResult('date', 'date', 'date')
1679 ->addScalarResult('rainrisk', 'rainrisk', 'float')
1680 ->addScalarResult('rainfall', 'rainfall', 'float')
1681 ->addScalarResult('realfeel', 'realfeel', 'float')
1682 ->addScalarResult('temperature', 'temperature', 'float')
1683 ->addScalarResult('locked', 'locked', 'datetime')
1684 ->addScalarResult('start', 'start', 'datetime')
1685 ->addScalarResult('stop', 'stop', 'datetime')
1686 ->addScalarResult('t_id', 't_id', 'integer')
1687 ->addScalarResult('t_title', 't_title', 'string')
1688 ->addScalarResult('l_id', 'l_id', 'integer')
1689 ->addScalarResult('l_title', 'l_title', 'string')
1690 ->addScalarResult('a_id', 'a_id', 'integer')
1691 ->addScalarResult('ad_name', 'ad_name', 'string')
1692 ->addScalarResult('ad_type', 'ad_type', 'string')
1693 ->addScalarResult('au_id', 'au_id', 'integer')
1694 ->addScalarResult('au_pseudonym', 'au_pseudonym', 'string')
1695 ->addScalarResult('p_rate', 'p_rate', 'integer')
1696 ->addScalarResult('p_hat', 'p_hat', 'boolean')
1697 //XXX: is a string because of \n separator
1698 ->addScalarResult('sau_id', 'sau_id', 'string')
1699 //XXX: is a string because of \n separator
1700 ->addScalarResult('sau_pseudonym', 'sau_pseudonym', 'string')
1701 ->addIndexByScalar('id');
1702
1703 //Fetch result
1704 $res = $this->_em
1705 ->createNativeQuery($req, $rsm)
1706 ->setParameter('begin', $period->getStartDate())
1707 ->setParameter('end', $period->getEndDate())
1708 ->setParameter('uid', $userId)
1709 ->setParameter('locale', $locale)
1710 ->getResult();
1711
1712 //Init calendar
1713 $calendar = [];
1714
1715 //Init month
1716 $month = null;
1717
1718 //Iterate on each day
1719 foreach($period as $date) {
1720 //Init day in calendar
1721 $calendar[$Ymd = $date->format('Ymd')] = [
1722 'title' => $this->translator->trans($date->format('l')).' '.$date->format('d'),
1723 'class' => [],
1724 'sessions' => []
1725 ];
1726
1727 //Detect month change
1728 if ($month != $date->format('m')) {
1729 $month = $date->format('m');
1730 //Append month for first day of month
1731 //XXX: except if today to avoid double add
1732 if ($date->format('U') != strtotime('today')) {
1733 $calendar[$Ymd]['title'] .= '/'.$month;
1734 }
1735 }
1736 //Deal with today
1737 if ($date->format('U') == ($today = strtotime('today'))) {
1738 $calendar[$Ymd]['title'] .= '/'.$month;
1739 $calendar[$Ymd]['current'] = true;
1740 $calendar[$Ymd]['class'][] = 'current';
1741 }
1742 //Disable passed days
1743 if ($date->format('U') < $today) {
1744 $calendar[$Ymd]['disabled'] = true;
1745 $calendar[$Ymd]['class'][] = 'disabled';
1746 }
1747 //Set next month days
1748 if ($date->format('m') > date('m')) {
1749 $calendar[$Ymd]['next'] = true;
1750 #$calendar[$Ymd]['class'][] = 'next';
1751 }
1752
1753 //Detect sunday
1754 if ($date->format('w') == 0) {
1755 $calendar[$Ymd]['class'][] = 'sunday';
1756 }
1757
1758 //Iterate on each session to find the one of the day
1759 foreach($res as $session) {
1760 if (($sessionYmd = $session['date']->format('Ymd')) == $Ymd) {
1761 //Count number of application
1762 $count = count(explode("\n", $session['sau_id']));
1763
1764 //Compute classes
1765 $class = [];
1766 if (!empty($session['a_id'])) {
1767 $applications = [ $session['au_id'] => $session['au_pseudonym'] ];
1768 if ($session['au_id'] == $userId) {
1769 $class[] = 'granted';
1770 } else {
1771 $class[] = 'disputed';
1772 }
1773 } elseif ($count > 1) {
1774 $class[] = 'disputed';
1775 } elseif (!empty($session['locked'])) {
1776 $class[] = 'locked';
1777 } else {
1778 $class[] = 'pending';
1779 }
1780
1781 if ($sessionId == $session['id']) {
1782 $class[] = 'highlight';
1783 }
1784
1785 //Set temperature
1786 //XXX: realfeel may be null, temperature should not
1787 $temperature = $session['realfeel'] !== null ? $session['realfeel'] : $session['temperature'];
1788
1789 //Compute weather
1790 //XXX: rainfall may be null
1791 if ($session['rainrisk'] > 0.50 || $session['rainfall'] > 2) {
1792 $weather = self::GLYPHS['Stormy'];
1793 } elseif ($session['rainrisk'] > 0.40 || $session['rainfall'] > 1) {
1794 $weather = self::GLYPHS['Rainy'];
1795 } elseif ($temperature > 24) {
1796 $weather = self::GLYPHS['Cleary'];
1797 } elseif ($temperature > 17) {
1798 $weather = self::GLYPHS['Sunny'];
1799 } elseif ($temperature > 10) {
1800 $weather = self::GLYPHS['Cloudy'];
1801 } elseif ($temperature !== null) {
1802 $weather = self::GLYPHS['Winty'];
1803 } else {
1804 $weather = null;
1805 }
1806
1807 //Init weathertitle
1808 $weathertitle = [];
1809
1810 //Check if realfeel is available
1811 if ($session['realfeel'] !== null) {
1812 $weathertitle[] = $session['realfeel'].'Β°R';
1813 }
1814
1815 //Check if temperature is available
1816 if ($session['temperature'] !== null) {
1817 $weathertitle[] = $session['temperature'].'Β°C';
1818 }
1819
1820 //Check if rainrisk is available
1821 if ($session['rainrisk'] !== null) {
1822 $weathertitle[] = ($session['rainrisk']*100).'%';
1823 }
1824
1825 //Check if rainfall is available
1826 if ($session['rainfall'] !== null) {
1827 $weathertitle[] = $session['rainfall'].'mm';
1828 }
1829
1830 //Set applications
1831 $applications = [
1832 0 => $this->translator->trans($session['t_title']).' '.$this->translator->trans('at '.$session['l_title']).$this->translator->trans(':')
1833 ];
1834
1835 //Fetch pseudonyms from session applications
1836 $applications += array_combine(explode("\n", $session['sau_id']), array_map(function ($v) {return '- '.$v;}, explode("\n", $session['sau_pseudonym'])));
1837
1838 //Set dance
1839 $dance = null;
1840
1841 //Set pseudonym
1842 $pseudonym = null;
1843
1844 //Check that session is not granted
1845 if (empty($session['a_id'])) {
1846 //With location id and unique application
1847 if ($count == 1) {
1848 //Set unique application pseudonym
1849 $pseudonym = $session['sau_pseudonym'];
1850 }
1851 //Session is granted
1852 } else {
1853 //Replace granted application
1854 $applications[$session['au_id']] = '* '.$session['au_pseudonym'];
1855
1856 //Set dance
1857 $dance = $this->translator->trans($session['ad_name'].' '.lcfirst($session['ad_type']));
1858
1859 //Set pseudonym
1860 $pseudonym = $session['au_pseudonym'].($count > 1 ? ' ['.$count.']':'');
1861 }
1862
1863 //Set title
1864 $title = $this->translator->trans($session['l_title']).($count > 1 ? ' ['.$count.']':'');
1865
1866 //Add the session
1867 $calendar[$Ymd]['sessions'][$session['t_id'].sprintf('%02d', $session['l_id'])] = [
1868 'id' => $session['id'],
1869 'start' => $session['start'],
1870 'stop' => $session['stop'],
1871 'location' => $this->translator->trans($session['l_title']),
1872 'dance' => $dance,
1873 'pseudonym' => $pseudonym,
1874 'class' => $class,
1875 'slot' => self::GLYPHS[$session['t_title']],
1876 'slottitle' => $this->translator->trans($session['t_title']),
1877 'weather' => $weather,
1878 'weathertitle' => implode(' ', $weathertitle),
1879 'applications' => $applications,
1880 'rate' => $session['p_rate'],
1881 'hat' => $session['p_hat']
1882 ];
1883 }
1884 }
1885
1886 //Sort sessions
1887 ksort($calendar[$Ymd]['sessions']);
1888 }
1889
1890 //Send result
1891 return $calendar;
1892 }
1893
1894 /**
1895 * Find all session pending hourly weather
1896 *
1897 * @return array<Session> The sessions to update
1898 */
1899 public function findAllPendingHourlyWeather() {
1900 //Select all sessions starting and stopping in the next 3 days
1901 //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)
1902 $req = <<<SQL
1903 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
1904 FROM RapsysAirBundle:Session AS s
1905 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1906 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))
1907 SQL;
1908
1909 //Replace bundle entity name by table name
1910 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1911
1912 //Get result set mapping instance
1913 $rsm = new ResultSetMapping();
1914
1915 //Declare all fields
1916 $rsm
1917 ->addEntityResult('RapsysAirBundle:Session', 's')
1918 ->addFieldResult('s', 'id', 'id')
1919 ->addFieldResult('s', 'date', 'date')
1920 ->addFieldResult('s', 'begin', 'begin')
1921 ->addFieldResult('s', 'length', 'length')
1922 ->addFieldResult('s', 'rainfall', 'rainfall')
1923 ->addFieldResult('s', 'rainrisk', 'rainrisk')
1924 ->addFieldResult('s', 'realfeel', 'realfeel')
1925 ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1926 ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1927 ->addFieldResult('s', 'temperature', 'temperature')
1928 ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1929 ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1930 ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
1931 ->addFieldResult('o', 'slot_id', 'id')
1932 ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
1933 ->addFieldResult('l', 'location_id', 'id')
1934 ->addFieldResult('l', 'zipcode', 'zipcode')
1935 ->addIndexBy('s', 'id');
1936
1937 //Send result
1938 return $this->_em
1939 ->createNativeQuery($req, $rsm)
1940 ->getResult();
1941 }
1942
1943 /**
1944 * Find all session pending daily weather
1945 *
1946 * @return array<Session> The sessions to update
1947 */
1948 public function findAllPendingDailyWeather() {
1949 //Select all sessions stopping after next 3 days
1950 //XXX: select session stopping after or equal date(now)+3d as accuweather only provide hourly data for the next 3 days (INTERVAL 3 DAY)
1951 $req = <<<SQL
1952 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
1953 FROM RapsysAirBundle:Session AS s
1954 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
1955 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))
1956 SQL;
1957
1958 //Replace bundle entity name by table name
1959 $req = str_replace($this->tableKeys, $this->tableValues, $req);
1960
1961 //Get result set mapping instance
1962 $rsm = new ResultSetMapping();
1963
1964 //Declare all fields
1965 $rsm
1966 ->addEntityResult('RapsysAirBundle:Session', 's')
1967 ->addFieldResult('s', 'id', 'id')
1968 ->addFieldResult('s', 'date', 'date')
1969 ->addFieldResult('s', 'begin', 'begin')
1970 ->addFieldResult('s', 'length', 'length')
1971 ->addFieldResult('s', 'rainfall', 'rainfall')
1972 ->addFieldResult('s', 'rainrisk', 'rainrisk')
1973 ->addFieldResult('s', 'realfeel', 'realfeel')
1974 ->addFieldResult('s', 'realfeelmin', 'realfeelmin')
1975 ->addFieldResult('s', 'realfeelmax', 'realfeelmax')
1976 ->addFieldResult('s', 'temperature', 'temperature')
1977 ->addFieldResult('s', 'temperaturemin', 'temperaturemin')
1978 ->addFieldResult('s', 'temperaturemax', 'temperaturemax')
1979 ->addJoinedEntityResult('RapsysAirBundle:Slot', 'o', 's', 'slot')
1980 ->addFieldResult('o', 'slot_id', 'id')
1981 ->addJoinedEntityResult('RapsysAirBundle:Location', 'l', 's', 'location')
1982 ->addFieldResult('l', 'location_id', 'id')
1983 ->addFieldResult('l', 'zipcode', 'zipcode')
1984 ->addIndexBy('s', 'id');
1985
1986 //Send result
1987 return $this->_em
1988 ->createNativeQuery($req, $rsm)
1989 ->getResult();
1990 }
1991
1992 /**
1993 * Find every session pending application
1994 *
1995 * @return array<Session> The sessions to update
1996 */
1997 public function findAllPendingApplication() {
1998 //Select all sessions not locked without application or canceled application within attribution period
1999 //XXX: DIFF(start, now) <= IF(DIFF(start, created) <= SENIOR_DELAY in DAY, DIFF(start, created) * 3 / 4, SENIOR_DELAY)
2000 //TODO: remonter les donnΓ©es pour le mail ?
2001 $req =<<<SQL
2002 SELECT s.id
2003 FROM RapsysAirBundle:Session as s
2004 LEFT JOIN RapsysAirBundle:Application AS a ON (a.id = s.application_id AND a.canceled IS NULL)
2005 JOIN RapsysAirBundle:Application AS a2 ON (a2.session_id = s.id AND a2.canceled IS NULL)
2006 WHERE s.locked IS NULL AND a.id IS NULL AND
2007 TIME_TO_SEC(TIMEDIFF(@dt_start := ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) <= IF(
2008 TIME_TO_SEC(@td_sc := TIMEDIFF(@dt_start, s.created)) <= :seniordelay,
2009 ROUND(TIME_TO_SEC(@td_sc) * :regulardelay / :seniordelay),
2010 :seniordelay
2011 )
2012 GROUP BY s.id
2013 ORDER BY @dt_start ASC, s.created ASC
2014 SQL;
2015
2016 //Replace bundle entity name by table name
2017 $req = str_replace($this->tableKeys, $this->tableValues, $req);
2018
2019 //Get result set mapping instance
2020 $rsm = new ResultSetMapping();
2021
2022 //Declare all fields
2023 $rsm
2024 ->addEntityResult('RapsysAirBundle:Session', 's')
2025 ->addFieldResult('s', 'id', 'id')
2026 ->addIndexBy('s', 'id');
2027
2028 //Send result
2029 return $this->_em
2030 ->createNativeQuery($req, $rsm)
2031 ->getResult();
2032 }
2033
2034 /**
2035 * Fetch session best application by session id
2036 *
2037 * @param int $id The session id
2038 * @return Application|null The application or null
2039 */
2040 public function findBestApplicationById($id) {
2041 /**
2042 * Query session applications ranked by location score, global score, created and user_id
2043 *
2044 * @xxx guest (or less) with application on location within 30 day are only considered within guestdelay
2045 *
2046 * @xxx regular (or less) premium application on hotspot are only considered within regulardelay
2047 *
2048 * @xxx senior (or less) with 5 or less session on location are only considered within seniordelay
2049 *
2050 * @xxx senior (or less) with l_pn_ratio >= 1 are only considered within seniordelay
2051 *
2052 * @xxx senior (or less) with l_tr_ratio >= (o_tr_ratio + 5) are only considered within seniordelay
2053 *
2054 * @xxx only consider session within one year (may be unaccurate by the day with after session)
2055 *
2056 * @xxx rainfall may not be accessible for previous session and other session at d-4 (only at d-2)
2057 *
2058 * @todo ??? feedback the data to inform the rejected users ???
2059 */
2060 $req = <<<SQL
2061 SELECT e.id, e.l_score AS score
2062 FROM (
2063 SELECT
2064 d.id,
2065 d.user_id,
2066 d.l_count,
2067 d.l_score,
2068 d.l_tr_ratio,
2069 d.l_pn_ratio,
2070 d.l_previous,
2071 d.g_score,
2072 d.o_tr_ratio,
2073 MAX(ug.group_id) AS group_id,
2074 d.remaining,
2075 d.premium,
2076 d.hotspot,
2077 d.created
2078 FROM (
2079 SELECT
2080 c.id,
2081 c.user_id,
2082 c.l_count,
2083 c.l_score,
2084 c.l_tr_ratio,
2085 c.l_pn_ratio,
2086 c.l_previous,
2087 c.g_score,
2088 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,
2089 c.remaining,
2090 c.premium,
2091 c.hotspot,
2092 c.created
2093 FROM (
2094 SELECT
2095 b.id,
2096 b.user_id,
2097 b.session_id,
2098 b.date,
2099 b.location_id,
2100 b.l_count,
2101 b.l_score,
2102 b.l_tr_ratio,
2103 b.l_pn_ratio,
2104 b.l_previous,
2105 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,
2106 b.remaining,
2107 b.premium,
2108 b.hotspot,
2109 b.created
2110 FROM (
2111 SELECT
2112 a.id,
2113 a.user_id,
2114 s.id AS session_id,
2115 s.date AS date,
2116 s.slot_id,
2117 s.location_id,
2118 COUNT(a2.id) AS l_count,
2119 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,
2120 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,
2121 (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,
2122 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,
2123 TIME_TO_SEC(TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = :afterid, 1, 0) DAY), NOW())) AS remaining,
2124 s.premium,
2125 l.hotspot,
2126 a.created
2127 FROM RapsysAirBundle:Session AS s
2128 JOIN RapsysAirBundle:Location AS l ON (l.id = s.location_id)
2129 JOIN RapsysAirBundle:Application AS a ON (a.session_id = s.id AND a.canceled IS NULL)
2130 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)
2131 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))
2132 WHERE s.id = :sid
2133 GROUP BY a.id
2134 ORDER BY NULL
2135 LIMIT 0, :limit
2136 ) AS b
2137 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)
2138 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))
2139 GROUP BY b.id
2140 ORDER BY NULL
2141 LIMIT 0, :limit
2142 ) AS c
2143 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)
2144 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))
2145 GROUP BY c.id
2146 ORDER BY NULL
2147 LIMIT 0, :limit
2148 ) AS d
2149 LEFT JOIN RapsysAirBundle:UserGroup AS ug ON (ug.user_id = d.user_id)
2150 GROUP BY d.id
2151 LIMIT 0, :limit
2152 ) AS e
2153 WHERE
2154 IF(e.group_id <= :guestid AND e.l_previous <= :guestwait, e.remaining <= :guestdelay, 1) AND
2155 IF(e.group_id <= :regularid AND e.premium = 1 AND e.hotspot = 1, e.remaining <= :regulardelay, 1) AND
2156 IF(e.group_id <= :seniorid AND e.l_count <= :scount, e.remaining <= :regulardelay, 1) AND
2157 IF(e.group_id <= :seniorid AND e.l_pn_ratio >= :pnratio, e.remaining <= :regulardelay, 1) AND
2158 IF(e.group_id <= :seniorid AND e.l_tr_ratio >= (e.o_tr_ratio + :trdiff), e.remaining <= :regulardelay, 1)
2159 ORDER BY e.l_score ASC, e.g_score ASC, e.created ASC, e.user_id ASC
2160 SQL;
2161
2162 //Replace bundle entity name by table name
2163 $req = str_replace($this->tableKeys, $this->tableValues, $req);
2164
2165 //Set update request
2166 $upreq = 'UPDATE RapsysAirBundle:Application SET score = :score, updated = NOW() WHERE id = :id';
2167
2168 //Replace bundle entity name by table name
2169 $upreq = str_replace($this->tableKeys, $this->tableValues, $upreq);
2170
2171 //Get result set mapping instance
2172 $rsm = new ResultSetMapping();
2173
2174 //Declare all fields
2175 $rsm
2176 ->addEntityResult('RapsysAirBundle:Application', 'a')
2177 ->addFieldResult('a', 'id', 'id')
2178 ->addFieldResult('a', 'score', 'score')
2179 ->addIndexBy('a', 'id');
2180
2181 //Get result
2182 //XXX: setting limit in subqueries is required to prevent mariadb optimisation
2183 $applications = $this->_em
2184 ->createNativeQuery($req, $rsm)
2185 ->setParameter('sid', $id)
2186 ->getResult();
2187
2188 //Init ret
2189 $ret = null;
2190
2191 //Update score
2192 foreach($applications as $application) {
2193 //Check if we already saved best candidate
2194 if ($ret === null) {
2195 //Return first application
2196 $ret = $application;
2197 }
2198
2199 //Update application updated field
2200 //XXX: updated field is not modified for user with bad behaviour as application is not retrieved until delay is reached
2201 $this->_em->getConnection()->executeUpdate($upreq, ['id' => $application->getId(), 'score' => $application->getScore()], ['id' => Types::INTEGER, 'score' => Types::FLOAT]);
2202 }
2203
2204 //Return best ranked application
2205 return $ret;
2206 }
2207
2208
2209 /**
2210 * Rekey sessions and applications by chronological session id
2211 *
2212 * @return bool The rekey success or failure
2213 */
2214 function rekey(): bool {
2215 //Get connection
2216 $cnx = $this->_em->getConnection();
2217
2218 //Set the request
2219 $req = <<<SQL
2220 SELECT
2221 a.id,
2222 a.sa_id
2223 FROM (
2224 SELECT
2225 s.id,
2226 s.date,
2227 s.begin,
2228 s.slot_id,
2229 GROUP_CONCAT(sa.id ORDER BY sa.id SEPARATOR "\\n") AS sa_id
2230 FROM RapsysAirBundle:Session AS s
2231 LEFT JOIN RapsysAirBundle:Application AS sa ON (sa.session_id = s.id)
2232 GROUP BY s.id
2233 ORDER BY NULL
2234 ) AS a
2235 ORDER BY ADDDATE(ADDTIME(a.date, a.begin), INTERVAL IF(a.slot_id = :afterid, 1, 0) DAY) ASC
2236 SQL;
2237
2238 //Replace bundle entity name by table name
2239 $req = str_replace($this->tableKeys, $this->tableValues, $req);
2240
2241 //Get result set mapping instance
2242 //XXX: DEBUG: see ../blog.orig/src/Rapsys/BlogBundle/Repository/ArticleRepository.php
2243 $rsm = new ResultSetMapping();
2244
2245 //Declare all fields
2246 //XXX: see vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/Types.php
2247 //addScalarResult($sqlColName, $resColName, $type = 'string');
2248 $rsm->addScalarResult('id', 'id', 'integer')
2249 ->addScalarResult('sa_id', 'sa_id', 'string');
2250 #->addIndexByScalar('id');
2251
2252 //Fetch result
2253 $rnq = $this->_em->createNativeQuery($req, $rsm);
2254
2255 //Get result set
2256 $res = $rnq->getResult();
2257
2258 //Start transaction
2259 $cnx->beginTransaction();
2260
2261 //Set update session request
2262 $sreq = <<<SQL
2263 UPDATE RapsysAirBundle:Session
2264 SET id = :nid, updated = NOW()
2265 WHERE id = :id
2266 SQL;
2267
2268 //Replace bundle entity name by table name
2269 $sreq = str_replace($this->tableKeys, $this->tableValues, $sreq);
2270
2271 //Set update application request
2272 $areq = <<<SQL
2273 UPDATE RapsysAirBundle:Application
2274 SET session_id = :nid, updated = NOW()
2275 WHERE session_id = :id
2276 SQL;
2277
2278 //Replace bundle entity name by table name
2279 $areq = str_replace($this->tableKeys, $this->tableValues, $areq);
2280
2281 //Set max value
2282 $max = max(array_keys($res));
2283
2284 try {
2285 //Prepare session to update
2286 foreach($res as $id => $data) {
2287 //Set temp id
2288 $res[$id]['t_id'] = $max + $id + 1;
2289
2290 //Set new id
2291 $res[$id]['n_id'] = $id + 1;
2292
2293 //Explode application ids
2294 $res[$id]['sa_id'] = explode("\n", $data['sa_id']);
2295
2296 //Without change
2297 if ($res[$id]['n_id'] == $res[$id]['id']) {
2298 //Remove unchanged session
2299 unset($res[$id]);
2300 }
2301 }
2302
2303 //With changes
2304 if (!empty($res)) {
2305 //Disable foreign key checks
2306 $cnx->prepare('SET foreign_key_checks = 0')->execute();
2307
2308 //Update to temp id
2309 foreach($res as $id => $data) {
2310 //Run session update
2311 $cnx->executeUpdate($sreq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
2312
2313 //Run applications update
2314 $cnx->executeUpdate($areq, ['nid' => $res[$id]['t_id'], 'id' => $res[$id]['id']]);
2315 }
2316
2317 //Update to new id
2318 foreach($res as $id => $data) {
2319 //Run session update
2320 $cnx->executeUpdate($sreq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]);
2321
2322 //Run applications update
2323 $cnx->executeUpdate($areq, ['nid' => $res[$id]['n_id'], 'id' => $res[$id]['t_id']]);
2324 }
2325
2326 //Restore foreign key checks
2327 $cnx->prepare('SET foreign_key_checks = 1')->execute();
2328
2329 //Commit transaction
2330 $cnx->commit();
2331
2332 //Set update auto_increment request
2333 $ireq = <<<SQL
2334 ALTER TABLE RapsysAirBundle:Session
2335 auto_increment = 1
2336 SQL;
2337
2338 //Replace bundle entity name by table name
2339 $ireq = str_replace($this->tableKeys, $this->tableValues, $ireq);
2340
2341 //Reset auto_increment
2342 $cnx->exec($ireq);
2343 //Without changes
2344 } else {
2345 //Rollback transaction
2346 $cnx->rollback();
2347 }
2348 } catch(\Exception $e) {
2349 //Rollback transaction
2350 $cnx->rollback();
2351
2352 //Throw exception
2353 throw $e;
2354 }
2355
2356 //Return success
2357 return true;
2358 }
2359 }
Symfony bundle rapsys/airbundle used to handle libre air features