Raphaël G. Git Repositories - airbundle/blob - Repository/SessionRepository.php.complete.sql

   1 SELECT f.*
   2 FROM (
   3         # sql request
   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
   6         FROM (
   7                 # Select all applications with other user scores from global excluding canceled applications (in time) and locked sessions
   8                 SELECT
   9                         d.id,
  10                         d.session_id,
  11                         d.session_date,
  12                         d.user_id,
  13                         d.ls_count,
  14                         d.ls_score,
  15                         d.ls_tr_ratio,
  16                         d.ls_temp,
  17                         d.ls_pn_ratio,
  18                         d.ls_previous,
  19                         d.l_count,
  20                         d.l_score,
  21                         d.l_tr_ratio,
  22                         d.l_temp,
  23                         d.l_pn_ratio,
  24                         d.l_previous,
  25                         d.g_count,
  26                         d.g_score,
  27                         d.g_tr_ratio,
  28                         d.g_temp,
  29                         d.g_pn_ratio,
  30                         d.g_previous,
  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,
  38                         d.remaining,
  39                         d.premium,
  40                         d.hotspot,
  41                         d.created
  42                 FROM (
  43                         # Select all applications with scores from global excluding canceled applications (in time) and locked sessions
  44                         SELECT
  45                                 c.id,
  46                                 c.session_id,
  47                                 c.session_date,
  48                                 c.user_id,
  49                                 c.ls_count,
  50                                 c.ls_score,
  51                                 c.ls_tr_ratio,
  52                                 c.ls_temp,
  53                                 c.ls_pn_ratio,
  54                                 c.ls_previous,
  55                                 c.l_count,
  56                                 c.l_score,
  57                                 c.l_tr_ratio,
  58                                 c.l_temp,
  59                                 c.l_pn_ratio,
  60                                 c.l_previous,
  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,
  69                                 c.remaining,
  70                                 c.premium,
  71                                 c.hotspot,
  72                                 c.created
  73                         FROM (
  74                                 # Select all applications with scores from same location excluding canceled applications (in time) and locked sessions
  75                                 SELECT
  76                                         b.id,
  77                                         b.session_id,
  78                                         b.session_date,
  79                                         /*remonter location_id ici, on veut prendre la température pour la même location + slot_id IN (2, 3)*/
  80                                         b.user_id,
  81                                         b.ls_count,
  82                                         b.ls_score,
  83                                         b.ls_tr_ratio,
  84                                         b.ls_temp,
  85                                         b.ls_pn_ratio,
  86                                         b.ls_previous,
  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)*/
  96                                         b.remaining,
  97                                         b.premium,
  98                                         b.hotspot,
  99                                         b.created
 100                                 FROM (
 101                                         # Select all applications with scores from same location+slot excluding canceled applications (in time) and locked sessions
 102                                         SELECT
 103                                                 a.id,
 104                                                 s.id AS session_id,
 105                                                 ADDDATE(s.date, INTERVAL IF(s2.slot_id = 4, 1, 0) DAY) AS session_date,
 106                                                 s.location_id,
 107                                                 a.user_id,
 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,
 118                                                 s.premium,
 119                                                 l.hotspot,
 120                                                 a.created
 121                                         FROM sessions AS s
 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))
 126                                         WHERE s.id = 12
 127                                         GROUP BY a.id
 128                                         ORDER BY NULL
 129                                         LIMIT 0, 1000000
 130                                 ) AS b
 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))
 133                                 GROUP BY b.id
 134                                 ORDER BY NULL
 135                                 LIMIT 0, 1000000
 136                         ) AS c
 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))
 139                         GROUP BY c.id
 140                         ORDER BY NULL
 141                         LIMIT 0, 1000000
 142                 ) AS d
 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))
 145                 GROUP BY d.id
 146                 ORDER BY NULL
 147                 LIMIT 0, 1000000
 148         ) AS e
 149         LEFT JOIN groups_users AS gu ON (gu.user_id = e.user_id)
 150         GROUP BY e.id
 151 ) AS f
 152 /*TODO: vérifier comment se comporte o_tr_ratio quand on a zéro réservation ^_^ à l'initialisation au hasard */
 153 WHERE
 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