Raphaël G. Git Repositories - airbundle/blob - 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