CREATE DEFINER=`root`@`localhost` PROCEDURE `freeswitchcdr`.`get_login_logout_table`(
IN start_dt DateTime,
IN end_dt DateTime,
IN curr_datetime DateTime
-- IN tab_number INT
)
BEGIN
DECLARE delta_time_sec INTEGER DEFAULT 86400; -- служебная переменная, которая заводится для того чтобы найти залогиненного в указанную дельту
DECLARE tmp_id INTEGER DEFAULT NULL;
DECLARE count_tmp INTEGER DEFAULT 0;
DECLARE tmp_dt DATETIME;
DECLARE tmp_phone, tmp_oper, tmp_status VARCHAR(50);
DECLARE finished INTEGER DEFAULT 0;
DECLARE my_cursor CURSOR FOR
SELECT
calldate, accountcode, agent_id, in_out FROM freeswitchcdr.cdr
WHERE
calldate >= start_dt
and
calldate <= end_dt
and
in_out in ('reg', 'unreg')
ORDER BY calldate ASC ;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;
DROP TEMPORARY TABLE IF EXISTS login_logout_agregate;
TRUNCATE temp_report;
CREATE TEMPORARY TABLE login_logout_agregate
(
Id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY(Id), INDEX(Id),
Oper Varchar(50),
Phone Varchar(50),
LoginDate DateTime,
LogoutDate DateTime
);
OPEN my_cursor;
get_agregate: LOOP
FETCH my_cursor
INTO tmp_dt, tmp_phone, tmp_oper, tmp_status;
IF finished = 1 THEN
LEAVE get_agregate;
END IF;
IF tmp_status = 'reg' THEN
SET tmp_id := (SELECT Id FROM login_logout_agregate WHERE Phone = tmp_phone and LogoutDate IS NULL ORDER BY Id DESC LIMIT 1);
IF(tmp_id IS NOT NULL) THEN
UPDATE login_logout_agregate SET LogoutDate = tmp_dt WHERE Id = tmp_id;
SET tmp_id := NULL;
END IF;
INSERT login_logout_agregate(Oper, Phone, LoginDate)
VALUES (tmp_oper, tmp_phone, tmp_dt);
ELSEIF tmp_status = 'unreg' THEN
-- проверяем на наличие первого анрега в таблице, считаем что старт работы был в начале смены
SET count_tmp := (select count(Id) from login_logout_agregate where Phone = tmp_phone);
IF count_tmp = 0 THEN
-- получаем оператора который был залогинен на этом телефоне до разлогина, поиск производим по дельте
SET tmp_oper := (SELECT agent_id FROM freeswitchcdr.cdr WHERE calldate >= DATE_ADD(start_dt, INTERVAL -delta_time_sec SECOND) and calldate <= start_dt and
in_out = 'reg' and accountcode = tmp_phone ORDER BY calldate DESC LIMIT 1);
INSERT login_logout_agregate(Oper, Phone, LoginDate, LogoutDate)
VALUES (tmp_oper, tmp_phone, start_dt, tmp_dt);
SET count_tmp := 0;
ELSE
SET tmp_id := (SELECT Id FROM login_logout_agregate WHERE Phone = tmp_phone and LogoutDate IS NULL ORDER BY Id DESC LIMIT 1);
IF(tmp_id IS NOT NULL) THEN
UPDATE login_logout_agregate SET LogoutDate = tmp_dt WHERE Id = tmp_id;
SET tmp_id := NULL;
ELSE
INSERT login_logout_agregate(Oper, Phone, LogoutDate)
VALUES (tmp_oper, tmp_phone, tmp_dt);
END IF;
END IF;
END IF;
END LOOP get_agregate;
CLOSE my_cursor;
-- тк в таблице присутствуют артефакты по анрегу то финальный вывод выполняем вот так
/*
SELECT Id, Oper, Phone, LoginDate, IFNULL(LogoutDate, end_dt) AS LogoutDate
FROM login_logout_agregate
WHERE Oper IS NOT NULL AND LoginDate IS NOT NULL ORDER BY Id ASC;
*/
-- если хочется посмотреть на артефакты то стоит произвести вывод как, закомментировав верхнее
/*
SELECT Id, Oper, Phone, LoginDate, IFNULL(LogoutDate, end_dt) AS LogoutDate
FROM login_logout_agregate ORDER BY Id ASC;
*/
-- добавляем строки в таблицу отчета, где в поле agent_id укажем всех, кто принимал звонки в указанный период
INSERT INTO temp_report ( agent_id )
SELECT DISTINCT agent_id
FROM freeswitchcdr.cdr
WHERE (calldate BETWEEN start_dt AND end_dt)
AND agent_id > 0
AND in_out = 'in';
-- обновляем данные в таблице temp_report. считаем количество принятых вызовов для каждого диспетчера
UPDATE temp_report
SET quantity_recive_calls =
(SELECT COUNT(id) As q_recieved_call_by_agent
FROM freeswitchcdr.cdr
WHERE (calldate BETWEEN start_dt AND end_dt)
AND agent_id = temp_report.agent_id
AND in_out = 'in'
AND disposition = 'NORMAL_CLEARING'
AND recordingfile IS NOT NULL);
UPDATE temp_report
SET quantity_missed_calls =
(SELECT COUNT(DISTINCT real_caller_uuid)
FROM freeswitchcdr.cdr
WHERE (calldate BETWEEN start_dt AND end_dt)
AND agent_id = temp_report.agent_id
AND real_caller_uuid NOT IN (SELECT real_caller_uuid
FROM freeswitchcdr.cdr
WHERE (calldate BETWEEN start_dt AND end_dt)
AND in_out = 'in'
AND disposition = 'NORMAL_CLEARING'
AND recordingfile IS NOT NULL
AND (real_caller_uuid <> uniqueid)
AND (in_out = 'in')
AND agent_id > 0));
-- обновляем данные в таблице temp_report. считаем количество исходящих вызовов для каждого диспетчера
-- НЕ СЧИТАЕТ, ТАК КАК НЕТ agent_id
/*
UPDATE temp_report
SET quantity_outbound_calls =
(SELECT COUNT(id)
FROM freeswitchcdr.cdr
WHERE (calldate BETWEEN start_dt AND end_dt)
AND agent_id = temp_report.agent_id
AND billsec > 7
AND in_out = 'out');
*/
-- обновляем данные в таблице temp_report. считаем общее время в разговоре для каждого диспетчера
UPDATE temp_report
SET time_in_call =
(SELECT SUM(billsec)
FROM freeswitchcdr.cdr
WHERE (calldate BETWEEN start_dt AND end_dt)
AND agent_id = temp_report.agent_id
AND in_out = 'in'
AND disposition = 'NORMAL_CLEARING'
AND recordingfile IS NOT NULL);
-- обновляем данные в таблице temp_report. считаем среднее время в разговоре для каждого диспетчера
UPDATE temp_report
SET avg_time_in_inbound_calls =
(SELECT AVG(billsec)
FROM freeswitchcdr.cdr
WHERE (calldate BETWEEN start_dt AND end_dt)
AND agent_id = temp_report.agent_id
AND in_out = 'in'
AND disposition = 'NORMAL_CLEARING'
AND recordingfile IS NOT NULL);
-- обновляем данные в таблице temp_report. считаем среднее время реакции
UPDATE temp_report
SET avg_time_of_reaction =
(SELECT AVG((duration)-(billsec)) AS react_time
FROM freeswitchcdr.cdr
WHERE (calldate BETWEEN start_dt AND end_dt)
AND agent_id = temp_report.agent_id
AND in_out = 'in'
AND disposition = 'NORMAL_CLEARING'
AND recordingfile IS NOT NULL);
/*
IF tab_number = 0 THEN
SELECT DISTINCT Oper AS WorkedOperList
FROM login_logout_agregate;
-- выводим статистику по всем операторам
SELECT TIMESTAMPDIFF(SECOND , LoginDate, LogoutDate) as result
FROM (SELECT Id, Oper, Phone, LoginDate, IFNULL(LogoutDate, end_dt) AS LogoutDate
FROM login_logout_agregate
WHERE Oper IS NOT NULL AND LoginDate IS NOT NULL ORDER BY Id ASC) as myalias
WHERE Oper IS NOT NULL;
ELSEIF tab_number = 1 THEN
-- тк в таблице присутствуют артефакты по анрегу то финальный вывод выполняем вот так
SELECT Id, Oper, Phone, LoginDate, IFNULL(LogoutDate, end_dt) AS LogoutDate
FROM login_logout_agregate
WHERE Oper IS NOT NULL AND LoginDate IS NOT NULL ORDER BY Id ASC;
ELSEIF tab_number = 2 THEN
-- если хочется посмотреть на артефакты то стоит произвести вывод как, закомментировав верхнее
SELECT Id, Oper, Phone, LoginDate, IFNULL(LogoutDate, end_dt) AS LogoutDate
FROM login_logout_agregate ORDER BY Id ASC;
SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, LoginDate, LogoutDate)) as result
FROM (SELECT Id, Oper, Phone, LoginDate, IFNULL(LogoutDate, end_dt) AS LogoutDate
FROM login_logout_agregate
WHERE Oper IS NOT NULL AND LoginDate IS NOT NULL ORDER BY Id ASC) as myalias
WHERE Oper = tab_number;
ELSE
SELECT SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, LoginDate, LogoutDate))) as result
FROM (SELECT Id, Oper, Phone, LoginDate, IFNULL(LogoutDate, end_dt) AS LogoutDate
FROM login_logout_agregate
WHERE Oper IS NOT NULL AND LoginDate IS NOT NULL ORDER BY Id ASC) as myalias
WHERE Oper = tab_number;
END IF;
*/
-- выводим для теста таблицу login_logout_agregate
SELECT Id, Oper, Phone, LoginDate, IFNULL(LogoutDate, end_dt) AS LogoutDate
FROM login_logout_agregate
WHERE Oper IS NOT NULL AND LoginDate IS NOT NULL ORDER BY Id ASC;
-- обновляем данные в таблице temp_report считаем время в работе для каждого диспетчера
UPDATE temp_report
-- SET time_in_call_hms = SEC_TO_TIME(time_in_call) ;
SET time_in_call_hms = (SELECT CONCAT(FLOOR(time_in_call/3600), ':',
TIME_FORMAT(SEC_TO_TIME(time_in_call % 3600), '%i:%s')));
UPDATE temp_report
-- SET avg_time_in_inbound_calls_hms = SEC_TO_TIME(avg_time_in_inbound_calls) ;
SET avg_time_in_inbound_calls_hms = (SELECT CONCAT(FLOOR(avg_time_in_inbound_calls/3600), ':',
TIME_FORMAT(SEC_TO_TIME(avg_time_in_inbound_calls % 3600), '%i:%s')));
UPDATE temp_report
-- SET avg_time_of_reaction_hms = SEC_TO_TIME(avg_time_of_reaction) ;
SET avg_time_of_reaction_hms = (SELECT CONCAT(FLOOR(avg_time_of_reaction/3600), ':',
TIME_FORMAT(SEC_TO_TIME(avg_time_of_reaction % 3600), '%i:%s')));
UPDATE temp_report
SET worktime_in_register_hms = (SELECT CONCAT(FLOOR(worktime_in_register/3600), ':',
TIME_FORMAT(SEC_TO_TIME(worktime_in_register % 3600), '%i:%s')));
UPDATE temp_report
SET worktime_in_register =
(SELECT SUM(TIMESTAMPDIFF(SECOND, LoginDate, LogoutDate)) as res2
-- FROM (SELECT Id, Oper, Phone, IFNULL(LoginDate, start_dt) As LoginDate, IFNULL(LogoutDate, curr_datetime) AS LogoutDate
FROM (SELECT Id, Oper, Phone, LoginDate, IFNULL(LogoutDate, curr_datetime) AS LogoutDate
FROM login_logout_agregate
WHERE Oper = agent_id
AND LoginDate IS NOT NULL ORDER BY Id ASC) as res3);
SELECT * FROM temp_report;
-- DROP TEMPORARY TABLE IF EXISTS login_logout_agregate;
END
Telegram: DB developers channel