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