]> Raphaƫl G. Git Repositories - airbundle/blob - Repository/SessionRepository.php.full.sql
Add inherited doc
[airbundle] / Repository / SessionRepository.php.full.sql
1 SELECT
2 e.id,
3 e.user_id,
4 e.group_id,
5 e.hotspot,
6 e.premium,
7 e.created,
8 e.l_count,
9 e.l_score,
10 e.g_score,
11 e.l_pn_ratio,
12 e.l_tr_ratio,
13 e.o_tr_ratio,
14 e.l_previous,
15 e.remaining
16 FROM (
17 SELECT
18 d.id,
19 d.session_id,
20 d.session_date,
21 d.location_id,
22 d.user_id,
23 d.l_count,
24 d.l_score,
25 d.l_tr_ratio,
26 d.l_pn_ratio,
27 d.l_previous,
28 d.g_count,
29 d.g_score,
30 d.g_tr_ratio,
31 d.g_pn_ratio,
32 d.g_previous,
33 d.o_count,
34 d.o_score,
35 d.o_tr_ratio,
36 d.o_pn_ratio,
37 d.o_previous,
38 MAX(gu.group_id) AS group_id,
39 d.remaining,
40 d.premium,
41 d.hotspot,
42 d.created
43 FROM (
44 SELECT
45 c.id,
46 c.session_id,
47 c.session_date,
48 c.location_id,
49 c.user_id,
50 c.l_count,
51 c.l_score,
52 c.l_tr_ratio,
53 c.l_pn_ratio,
54 c.l_previous,
55 c.g_count,
56 c.g_score,
57 c.g_tr_ratio,
58 c.g_pn_ratio,
59 c.g_previous,
60 COUNT(a4.id) AS o_count,
61 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 o_score,
62 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,
63 (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 o_pn_ratio,
64 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 o_previous,
65 c.remaining,
66 c.premium,
67 c.hotspot,
68 c.created
69 FROM (
70 SELECT
71 b.id,
72 b.session_id,
73 b.session_date,
74 b.location_id,
75 b.user_id,
76 b.l_count,
77 b.l_score,
78 b.l_tr_ratio,
79 b.l_pn_ratio,
80 b.l_previous,
81 COUNT(a3.id) AS g_count,
82 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 g_score,
83 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 g_tr_ratio,
84 (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 g_pn_ratio,
85 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 g_previous,
86 b.remaining,
87 b.premium,
88 b.hotspot,
89 b.created
90 FROM (
91 SELECT
92 a.id,
93 s.id AS session_id,
94 ADDDATE(s.date, INTERVAL IF(s.slot_id = 4, 1, 0) DAY) AS session_date,
95 s.location_id,
96 a.user_id,
97 COUNT(a2.id) AS l_count,
98 SUM(IF(a2.id IS NOT NULL, 1/ABS(DATEDIFF(ADDDATE(s.date, INTERVAL IF(s.slot_id = 4, 1, 0) DAY), ADDDATE(s2.date, INTERVAL IF(s2.slot_id = 4, 1, 0) DAY))), 0)) AS l_score,
99 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,
100 (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,
101 MIN(IF(a2.id IS NOT NULL, DATEDIFF(ADDDATE(s.date, INTERVAL IF(s.slot_id = 4, 1, 0) DAY), ADDDATE(s2.date, INTERVAL IF(s2.slot_id = 4, 1, 0) DAY)), NULL)) AS l_previous,
102 TIMEDIFF(ADDDATE(ADDTIME(s.date, s.begin), INTERVAL IF(s.slot_id = 4, 1, 0) DAY), NOW()) AS remaining,
103 s.premium,
104 l.hotspot,
105 a.created
106 FROM sessions AS s
107 JOIN locations AS l ON (l.id = s.location_id)
108 JOIN applications AS a ON (a.session_id = s.id AND a.canceled IS NULL)
109 LEFT JOIN sessions AS s2 ON (s2.id != s.id AND s2.location_id = s.location_id AND s2.slot_id IN (2, 3) AND s2.application_id IS NOT NULL AND s2.locked IS NULL)
110 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))
111 WHERE s.id = 12
112 GROUP BY a.id
113 ORDER BY NULL
114 LIMIT 0, 1000000
115 ) AS b
116 LEFT JOIN sessions AS s3 ON (s3.id != b.session_id AND s3.application_id IS NOT NULL AND s3.locked IS NULL)
117 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))
118 GROUP BY b.id
119 ORDER BY NULL
120 LIMIT 0, 1000000
121 ) AS c
122 LEFT JOIN sessions 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)
123 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))
124 GROUP BY c.id
125 ORDER BY NULL
126 LIMIT 0, 1000000
127 ) AS d
128 LEFT JOIN groups_users AS gu ON (gu.user_id = d.user_id)
129 GROUP BY d.id
130 LIMIT 0, 1000000
131 ) AS e
132 WHERE
133 IF(e.group_id <= 2 AND e.l_previous <= 30, e.remaining <= SEC_TO_TIME(2*24*3600), 1) AND
134 IF(e.group_id <= 3 AND e.premium = 1 AND e.hotspot = 1, e.remaining <= SEC_TO_TIME(3*24*3600), 1) AND
135 IF(e.group_id <= 4 AND e.l_count <= 5, e.remaining <= SEC_TO_TIME(3*24*3600), 1) AND
136 IF(e.group_id <= 4 AND e.l_pn_ratio >= 1, e.remaining <= SEC_TO_TIME(3*24*3600), 1) AND
137 IF(e.group_id <= 4 AND e.l_tr_ratio >= (e.o_tr_ratio + 5), e.remaining <= SEC_TO_TIME(3*24*3600), 1)
138 ORDER BY e.l_score, e.g_score, e.created, e.user_id