· 8 years ago · Mar 01, 2018, 02:36 PM
1BEGIN
2 DECLARE department_id BIGINT(20);
3 DECLARE utc TIME;
4 DECLARE reg_dt DATETIME;
5 DECLARE call_dt DATETIME;
6 DECLARE start_event_type INT(11);
7 DECLARE waiting INT(11);
8 DECLARE servicing INT(11);
9 DECLARE max_waiting_time INT(11);
10 DECLARE session_status INTEGER;
11 DECLARE device_id INT(11);
12 DECLARE flag_full_session TINYINT(1);
13 DECLARE start_dt DATETIME;
14 DECLARE finish_cycle BOOL;
15 DECLARE from_dt DATETIME;
16 DECLARE cur_client_sessions CURSOR FOR
17
18 SELECT
19 cs.department_id,
20 tz.utc,
21 DATE_ADD(cs.start_dt, INTERVAL -hour(tz.utc) HOUR),
22 DATE_ADD(cs.call_dt, INTERVAL -hour(tz.utc) HOUR),
23 cs.start_event_type,
24 cs.waiting,
25 cs.servicing,
26 cs.max_waiting_time,
27 cs.session_status
28 FROM client_sessions cs
29 INNER JOIN tmp_department_tree tdt ON tdt.id = cs.department_id
30 LEFT JOIN department d ON d.id = cs.department_id
31 LEFT JOIN city c ON c.id = d.city_id
32 LEFT JOIN time_zones tz ON tz.time_zone_id = c.time_zone_id
33 WHERE cs.start_dt > DATE_ADD(UTC_TIMESTAMP(), INTERVAL -2 DAY);
34-- WHERE cs.start_dt > from_dt;
35 DECLARE cur_operator_sessions CURSOR FOR
36 SELECT
37 os.department_id,
38 os.device_id,
39 MIN(os.flag_full_session) AS flag_full_session
40 FROM operator_sessions os
41 INNER JOIN tmp_department_tree tdt ON tdt.id = os.department_id
42 LEFT JOIN department d ON d.id = os.department_id
43 LEFT JOIN city c ON c.id = d.city_id
44 LEFT JOIN time_zones tz ON tz.time_zone_id = c.time_zone_id
45 WHERE os.finish_dt > date(DATE_ADD(UTC_TIMESTAMP(), INTERVAL hour(tz.utc) HOUR))
46 AND os.device_type = 6
47 GROUP BY os.department_id, os.device_id;
48 DECLARE CONTINUE HANDLER FOR NOT FOUND SET finish_cycle = TRUE;
49 DROP TEMPORARY TABLE IF EXISTS monitoring;
50 CREATE TEMPORARY TABLE monitoring (
51 `department_id` BIGINT(20),
52 `ws_open_count` INT(11),
53 `ws_total_count` INT(11),
54 `total_sessions` INT(11),
55 `service_total_sessions` INT(11),
56 `queue` INT(11),
57 `clients_count_excess` INT(11),
58 `sum_average_waiting` INT(11),
59 `sum_average_servicing` INT(11),
60 `reg_count` INT(11)
61 );
62
63 DROP TEMPORARY TABLE IF EXISTS tmp_department_tree;
64 CREATE TEMPORARY TABLE tmp_department_tree(`id` BIGINT(20));
65 CALL get_department_tree(departmentGroupId);
66
67 SET from_dt= DATE_ADD(UTC_TIMESTAMP(), INTERVAL -2 DAY);
68
69 OPEN cur_client_sessions;
70 SET finish_cycle = FALSE;
71 WHILE finish_cycle = FALSE DO
72 IF (department_id IS NOT NULL) THEN
73 SET start_dt = date(DATE_ADD(UTC_TIMESTAMP(), INTERVAL hour(utc) HOUR));
74 END IF;
75 IF (department_id IS NOT NULL) AND (reg_dt > start_dt)
76 AND ((SELECT
77 1
78 FROM monitoring m
79 WHERE m.department_id = department_id
80 LIMIT 1) IS NULL)
81 THEN
82 INSERT INTO monitoring (department_id, ws_open_count, ws_total_count, total_sessions, service_total_sessions, queue, clients_count_excess, sum_average_waiting, sum_average_servicing, reg_count)
83 VALUES (department_id, 0, 0, 0, 0, 0, 0, 0, 0, 0);
84 END IF;
85 IF (department_id IS NOT NULL) AND (reg_dt > start_dt)
86 THEN
87 UPDATE monitoring m
88 SET m.queue = CASE WHEN session_status = 0 THEN m.queue + 1
89 ELSE m.queue END,
90 m.clients_count_excess = CASE WHEN session_status > 0 AND waiting > max_waiting_time THEN
91 m.clients_count_excess + 1
92 WHEN session_status = 0 AND
93 TIMESTAMPDIFF(SECOND, UTC_TIMESTAMP(), reg_dt) > max_waiting_time THEN
94 m.clients_count_excess + 1
95 ELSE m.clients_count_excess END,
96 m.total_sessions = m.total_sessions + 1,
97 m.service_total_sessions = CASE WHEN session_status > 0 THEN m.total_sessions + 1
98 ELSE m.total_sessions END,
99 m.sum_average_waiting = CASE WHEN session_status = 0 THEN COALESCE(m.sum_average_waiting, 0) +
100 TIMESTAMPDIFF(SECOND, reg_dt, UTC_TIMESTAMP())
101 ELSE COALESCE(m.sum_average_waiting, 0) + waiting END,
102 m.sum_average_servicing = CASE WHEN session_status = 2 THEN COALESCE(m.sum_average_servicing, 0) + servicing
103 WHEN session_status = 1 THEN COALESCE(m.sum_average_servicing, 0) +
104 TIMESTAMPDIFF(SECOND, call_dt, UTC_TIMESTAMP())
105 ELSE m.sum_average_servicing END,
106 m.reg_count = CASE WHEN start_event_type = 6 THEN m.reg_count + 1
107 ELSE m.reg_count END
108 WHERE m.department_id = department_id;
109 END IF;
110 FETCH cur_client_sessions
111 INTO department_id, utc, reg_dt, call_dt, start_event_type, waiting, servicing, max_waiting_time, session_status;
112 END WHILE;
113 CLOSE cur_client_sessions;
114 OPEN cur_operator_sessions;
115 SET finish_cycle = FALSE;
116 SET department_id = NULL;
117 WHILE finish_cycle = FALSE DO
118 IF (department_id IS NOT NULL)
119 THEN
120 UPDATE monitoring m
121 SET m.ws_open_count = CASE WHEN flag_full_session = 0 THEN m.ws_open_count + 1
122 ELSE m.ws_open_count END,
123 m.ws_total_count = m.ws_total_count + 1
124 WHERE m.department_id = department_id;
125 END IF;
126 FETCH cur_operator_sessions
127 INTO department_id, device_id, flag_full_session;
128 END WHILE;
129 CLOSE cur_operator_sessions;
130 SELECT
131 d.id AS department_id,
132 d.code AS department_code,
133 d.name AS department_name,
134 c.time_zone_id AS time_zone_id,
135 coalesce(m.ws_open_count, 0) AS ws_open_count,
136 coalesce(m.ws_total_count, 0) AS ws_total_count,
137 coalesce(m.queue, 0) AS queue,
138 coalesce(m.clients_count_excess, 0) AS clients_count_excess,
139 coalesce(CASE WHEN m.total_sessions > 0 THEN (ROUND(m.sum_average_waiting / m.total_sessions)) END, 0) AS average_waiting,
140 coalesce(CASE WHEN m.service_total_sessions > 0 THEN (ROUND(m.sum_average_servicing /
141 m.service_total_sessions)) END, 0) AS average_servicing,
142 coalesce(m.reg_count, 0) AS reg_count,
143 d.last_event_dt AS max_event_time,
144-- (SELECT DATE_ADD(max(e.dt), INTERVAL -hour(tz.utc) HOUR)
145-- FROM `events` e
146-- WHERE e.department_id = d.id) AS max_event_time,
147
148 mad.exStatus AS department_status
149 FROM department d
150 INNER JOIN tmp_department_tree tdt ON tdt.id = d.id
151 LEFT JOIN monitoring m ON d.id = m.department_id OR m.department_id is null
152 LEFT JOIN city c ON c.id = d.city_id
153 LEFT JOIN time_zones tz ON tz.time_zone_id = c.time_zone_id
154 LEFT JOIN monitoring_active_departments mad ON mad.id = d.id
155 WHERE d.archive = 0 and d.departmentStatus = 0;
156 DROP TEMPORARY TABLE IF EXISTS tmp_department_tree;
157 DROP TEMPORARY TABLE IF EXISTS monitoring;
158 END