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