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


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