]>
Raphaël G. Git Repositories - airbundle/blob - Repository/SessionRepository.php.complete.sql
4 SELECT e.
id , e.ls_score
, e.l_score
, e.g_score
, e.ls_previous
, e.l_previous
, e.g_previous
, e.created
, e.user_id
, e.premium
, e.hotspot
,
5 MAX ( gu.group_id
) AS group_id
7 #
Select all applications
with other
user scores
from global excluding canceled
applications ( in time ) and locked sessions
31 # Compute
count , score
, tr_ratio
, temp and pn_ratio
for global
32 COUNT ( a5.
id ) AS o_count
,
33 SUM ( IF ( a5.
id IS NOT NULL , 1 / ABS ( DATEDIFF ( d.session_date
, ADDDATE ( s5.
date , INTERVAL IF ( s5.slot_id
= 4 , 1 , 0 ) DAY ))), 0 )) AS o_score
,
34 AVG ( IF ( a5.
id IS NOT NULL AND s5.temperature
IS NOT NULL AND s5.rainfall
IS NOT NULL , s5.temperature
/( 1 + s5.rainfall
), NULL )) AS o_tr_ratio
,
35 AVG ( IF ( a5.
id IS NOT NULL AND s5.temperature
IS NOT NULL , s5.temperature
, NULL )) AS o_temp
,
36 ( SUM ( IF ( a5.
id IS NOT NULL AND s5.premium
= 1 , 1 , 0 ))+ 1 )/( SUM ( IF ( a5.
id IS NOT NULL AND s5.premium
= 0 , 1 , 0 ))+ 1 ) AS o_pn_ratio
,
37 MAX ( IF ( a5.
id IS NOT NULL , s5.
date , NULL )) AS o_previous
,
43 #
Select all applications
with scores
from global excluding canceled
applications ( in time ) and locked sessions
61 # Compute
count , score
, tr_ratio
, temp and pn_ratio
for global
62 COUNT ( a4.
id ) AS g_count
,
63 SUM ( IF ( a4.
id IS NOT NULL , 1 / ABS ( DATEDIFF ( c.session_date
, ADDDATE ( s4.
date , INTERVAL IF ( s4.slot_id
= 4 , 1 , 0 ) DAY ))), 0 )) AS g_score
,
64 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 g_tr_ratio
,
65 AVG ( IF ( a4.
id IS NOT NULL AND s4.temperature
IS NOT NULL , s4.temperature
, NULL )) AS g_temp
,
66 ( SUM ( IF ( a4.
id IS NOT NULL AND s4.premium
= 1 , 1 , 0 ))+ 1 )/( SUM ( IF ( a4.
id IS NOT NULL AND s4.premium
= 0 , 1 , 0 ))+ 1 ) AS g_pn_ratio
,
67 #
MIN ( IF ( a4.
id IS NOT NULL , DATEDIFF ( c.session_date
, ADDDATE ( s4.
date , INTERVAL IF ( s4.slot_id
= 4 , 1 , 0 ) DAY )), NULL )) AS g_previous
,
68 MIN ( IF ( a4.
id IS NOT NULL , DATEDIFF ( c.session_date
, s4.
date ), NULL )) AS g_previous
,
74 #
Select all applications
with scores
from same
location excluding canceled
applications ( in time ) and locked sessions
79 /*remonter location_id ici, on veut prendre la température pour la même location + slot_id IN (2, 3)*/
87 # Compute
count , score
, tr_ratio
, temp and pn_ratio
for same
location
88 COUNT ( a3.
id ) AS l_count
,
89 SUM ( IF ( a3.
id IS NOT NULL , 1 / ABS ( DATEDIFF ( b.session_date
, ADDDATE ( s3.
date , INTERVAL IF ( s3.slot_id
= 4 , 1 , 0 ) DAY ))), 0 )) AS l_score
,
90 AVG ( IF ( a3.
id IS NOT NULL AND s3.temperature
IS NOT NULL AND s3.rainfall
IS NOT NULL , s3.temperature
/( 1 + s3.rainfall
), NULL )) AS l_tr_ratio
,
91 AVG ( IF ( a3.
id IS NOT NULL AND s3.temperature
IS NOT NULL , s3.temperature
, NULL )) AS l_temp
,
92 ( SUM ( IF ( a3.
id IS NOT NULL AND s3.premium
= 1 , 1 , 0 ))+ 1 )/( SUM ( IF ( a3.
id IS NOT NULL AND s3.premium
= 0 , 1 , 0 ))+ 1 ) AS l_pn_ratio
,
93 #
MIN ( IF ( a3.
id IS NOT NULL , DATEDIFF ( b.session_date
, ADDDATE ( s3.
date , INTERVAL IF ( s3.slot_id
= 4 , 1 , 0 ) DAY )), NULL )) AS l_previous
,
94 MIN ( IF ( a3.
id IS NOT NULL , DATEDIFF ( b.session_date
, s3.
date ), NULL )) AS l_previous
,
95 /*#TODO: calculer les délais guest|regular ici ou plus haut et les remonter (au denier cran c'est mieux pour limiter la taille de la temp table)*/
101 #
Select all applications
with scores
from same
location + slot
excluding canceled
applications ( in time ) and locked sessions
105 ADDDATE ( s.
date , INTERVAL IF ( s2.slot_id
= 4 , 1 , 0 ) DAY ) AS session_date
,
108 # Compute
count , score
, tr_ratio
, temp and pn_ratio
for same
location and slot
109 COUNT ( a2.
id ) AS ls_count
,
110 SUM ( IF ( a2.
id IS NOT NULL , 1 / ABS ( DATEDIFF ( s.
date , ADDDATE ( s2.
date , INTERVAL IF ( s2.slot_id
= 4 , 1 , 0 ) DAY ))), 0 )) AS ls_score
,
111 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 ls_tr_ratio
,
112 AVG ( IF ( a2.
id IS NOT NULL AND s2.temperature
IS NOT NULL , s2.temperature
, NULL )) AS ls_temp
,
113 ( 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 ls_pn_ratio
,
114 #
MIN ( IF ( a2.
id IS NOT NULL , DATEDIFF ( s.
date , ADDDATE ( s2.
date , INTERVAL IF ( s2.slot_id
= 4 , 1 , 0 ) DAY )), NULL )) AS ls_previous
,
115 MIN ( IF ( a2.
id IS NOT NULL , DATEDIFF ( s.
date , s2.
date ), NULL )) AS ls_previous
,
116 /*#TODO: calculer les délais guest|regular ici ou plus haut et les remonter (au denier cran c'est mieux pour limiter la taille de la temp table)*/
117 TIMEDIFF ( ADDDATE ( ADDTIME ( s.
date , s.
begin ), INTERVAL IF ( s2.slot_id
= 4 , 1 , 0 ) DAY ), NOW ()) AS remaining
,
122 JOIN locations
AS l
ON ( l.
id = s.location_id
)
123 JOIN applications
AS a
ON ( a.session_id
= s.
id AND a.canceled
IS NULL )
124 LEFT JOIN sessions
AS s2
ON ( s2.
id != s.
id AND s2.location_id
= s.location_id
AND s2.slot_id
= s.slot_id
AND s2.application_id
IS NOT NULL AND s2.locked
IS NULL )
125 LEFT JOIN applications
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
= 4 , 1 , 0 ) DAY )) < 1 ))
131 LEFT JOIN sessions
AS s3
ON ( s3.
id != b.session_id
AND s3.location_id
= b.location_id
AND s3.application_id
IS NOT NULL AND s3.locked
IS NULL )
132 LEFT JOIN applications
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
= 4 , 1 , 0 ) DAY )) < 1 ))
137 LEFT JOIN sessions
AS s4
ON ( s4.
id != c.session_id
AND s4.application_id
IS NOT NULL AND s4.locked
IS NULL )
138 LEFT JOIN applications
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
= 4 , 1 , 0 ) DAY )) < 1 ))
143 LEFT JOIN sessions
AS s5
ON ( s5.
id != d.session_id
AND s5.application_id
IS NOT NULL AND s5.locked
IS NULL )
144 LEFT JOIN applications
AS a5
ON ( a5.
id = s5.application_id
AND a5.user_id
!= d.user_id
AND ( a5.canceled
IS NULL OR TIMESTAMPDIFF ( DAY , a5.canceled
, ADDDATE ( ADDTIME ( s5.
date , s5.
begin ), INTERVAL IF ( s5.slot_id
= 4 , 1 , 0 ) DAY )) < 1 ))
149 LEFT JOIN groups_users
AS gu
ON ( gu.user_id
= e.user_id
)
152 /*TODO: vérifier comment se comporte o_tr_ratio quand on a zéro réservation ^_^ à l'initialisation au hasard */
154 IF ( f.group_id
<= 2 AND f.l_previous
<= 30 , f.remaining
<= SEC_TO_TIME ( 2 * 24 * 3600 ), 1 ) AND
155 IF ( f.group_id
<= 3 AND f.premium
= 1 AND f.hotspot
= 1 , f.remaining
<= SEC_TO_TIME ( 3 * 24 * 3600 ), 1 ) AND
156 IF ( f.group_id
<= 4 AND f.l_count
<= 5 , f.remaining
<= SEC_TO_TIME ( 3 * 24 * 3600 ), 1 ) AND
157 IF ( f.group_id
<= 4 AND f.l_pn_ratio
>= 1 , f.remaining
<= SEC_TO_TIME ( 3 * 24 * 3600 ), 1 ) AND
158 IF ( f.group_id
<= 4 AND f.l_tr_ratio
>= ( f.o_tr_ratio
+ 5 ), f.remaining
<= SEC_TO_TIME ( 3 * 24 * 3600 ), 1 )
159 ORDER BY f.l_score
ASC , f.g_score
ASC , f.created
ASC , f.user_id
ASC