USE [oktell]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Бондарев В.Г.
-- Create date: 15.01.2021
-- Description:	LCR - Показатель, отражающий процент звонков, не принятых по каким либо причинам.
-- Учитываются звонки с результатом:
-- 13 - Абонент прервал ожидание в очереди
-- Параметры:
-- @date1: Дата начала периода
-- @date2: Дата окончания периода
-- @time1: Начальное время
-- @time2: Конечное время
-- @interval: Интервал для расчета периодов:
--  1 - Все время (по умолчанию)
--  2 - По месяцам
--  3 - По дням
--  4 - По часам
--  5 - По 30 минут
--  6 - По 15 минут
--  7 - По 10 минут
--  8 - По 05 минут
-- @task_id: GUID идентификатор задачи.
-- @wait_term: Ожидание (условие):
--	1 - Больше или равно
--	2 - Меньше
-- @wait_time: Время ожидания, секунды
-- =============================================
ALTER   FUNCTION [dbo].[bvgCalcLCRByTask2] 
(
	-- Add the parameters for the function here
	@date1 datetime, @date2 datetime, @time1 datetime, @time2 datetime, @interval int, @task_id uniqueidentifier, @wait_term int, @wait_time int
)
RETURNS 
@Result TABLE 
(
	-- Add the column definitions for the TABLE variable here
	Id int Not Null Identity, DTStart datetime, DTStop datetime, IdTask uniqueidentifier, 
	CntTotal int not Null default 0, CntLost int not Null default 0, LCR decimal(18,2) not Null default 0.00,
	Unique(DTStart, Id)
)
AS
BEGIN

	If (@task_id is Null) return;

-- Генерация таблицы периодов
	Declare @intervals table (Id int not null identity, DTStart DateTime, DTStop DateTime, DStart DateTime, DStop DateTime, TStart DateTime, TStop DateTime, Unique(DTStart, Id))
	Insert Into @intervals
	Select * From oktell.dbo.GetDateTimeIntervals2(@date1, @date2, @time1, @time2, @interval)
--------------------------------------------------------------------------------------------------

	Set @date1 = CONVERT(date, @date1)
	Set @date2 = CONVERT(date, @date2)
	Set @time1 = CONVERT(time, @time1)
	Set @time2 = CONVERT(time, @time2)

	Declare @dtstart datetime = @date1+@time1, 
			@dtstop datetime  = @date2+@time2;
	
	if (@interval is null)	set @interval = 1;
	if (@wait_term is null) set @wait_term = 1
	if (@wait_time < 0)		set @wait_time = 0;

-- Выборка всей коммутационной информации входящих звонков по заданным параметрам
	Declare @lcr_tmp Table(
		[Id] int Not Null Identity,
		[IdProject] [uniqueidentifier] NOT NULL,
		[IdTask] [uniqueidentifier] NOT NULL,
		[IsOutput] [bit] NOT NULL,
		[IdEffort] [uniqueidentifier] NOT NULL,
		[IdOperator] [uniqueidentifier] NULL,
		[IdInList] [int] NOT NULL,
		[AbonentNumber] [nvarchar](20) NOT NULL,
		[CallResult] [int] NOT NULL,
		[CallResultInfo] [nvarchar](100) NOT NULL,
		[DateTimeStart] [datetime] NOT NULL,
		[DateStart] [datetime] NOT NULL,
		[TimeStart] [datetime] NOT NULL,
		[DateTimeStop] [datetime] NOT NULL,
		[IdConn] [uniqueidentifier] NULL,
		[IdChain] [uniqueidentifier] NULL,
		[IdAUser] [uniqueidentifier] NULL,
		[IdBUser] [uniqueidentifier] NULL,
		[IdExtLine] [uniqueidentifier] NOT NULL,
		[LenTime] [float] NOT NULL,
		[LenQueue] [float] NOT NULL,
		[IsBetweenOp] [bit] NULL,
		[OpponentInfo] [nvarchar](200) NULL,
		[IsHandled] [bit] NOT NULL,
		[IsSuccess] [bit] NOT NULL,
		[IsRecorded] [bit] NULL,
		[IdRecDir] [int] NULL,
		[ALineNum] [nvarchar](12) NULL,
		[BLineNum] [nvarchar](12) NULL,
		[UserResult] [int] NULL,
		Unique([DateTimeStart], [DateStart], [TimeStart], Id)
	)
	Insert Into @lcr_tmp
	Select /*Distinct */ec.* 
	from oktell_cc_temp..A_Cube_CC_EffortConnections as ec
	where (ec.DateStart between @date1 and @date2) and (ec.TimeStart between @time1 and @time2) 
			and  ec.IdTask = @task_id
			and ec.IsOutput < 1 and ec.IdChain is not null
	Order By ec.DateTimeStart
--------------------------------------------------------------------------------------------------

-- Формируем выходную таблицу
	Insert Into @Result ( DTStart, DTStop, IdTask )
	Select i.DTStart, i.DTStop, ec.IdTask 
	From @intervals as i
	Inner Join (
		Select IdTask, Min(DateTimeStart) as DTStart, Max(DateTimeStop) dtStop--, IdOperator
		From @lcr_tmp as ec
		Group By IdChain, IdTask--, IdOperator
	) as ec on ec.DTStart between i.DTStart and i.DTStop
	Group By i.DTStart, i.DTStop, ec.IdTask--, ec.IdOperator
	Order By i.DTStart

--------------------------------------------------------------------------------------------------

	declare @tblCount table ([Id] int Not Null Identity, DTStart DateTime, DTStop DateTime, Cnt int, 
		IdTask uniqueidentifier, Unique(DTStart, Id))--, IdOperator uniqueidentifier)
	-- Количество поступивших вызовов. Число цепочек по входящим задачам
	Insert Into @tblCount 
	Select i.DTStart, i.DTStop, Count(ec.cnt), IdTask--, IdOperator 
	From (
		Select IdChain, Count(IdChain) Over(Partition By IdTask) as cnt, IdTask, Min(DateTimeStart) dtStart, Max(DateTimeStop) dtStop--, IdOperator  
		From @lcr_tmp
		Group by IdChain, IdTask--, IdOperator
	) ec
	Inner join @intervals i on ec.dtStart between i.DTStart and i.DTStop
--	Inner join @intervals i on ec.dtStart >= i.DTStart and ec.dtStop <= i.DTStop
		Group By i.DTStart, i.DTStop, ec.IdTask--, ec.IdOperator
		Order By i.DTStart
--------------------------------------------------------------------------------------------------

	Update o
		Set CntTotal = tmp.Cnt
	From @Result o, @tblCount tmp
	Where o.DTStart = tmp.DTStart
		and (o.IdTask = tmp.IdTask)

	Delete From @tblCount
------------------------------------------------------------------------------------------------------
/*
[oktell_cc_temp].[dbo].[A_Cube_CC_Cat_TaskResultTypes] — типы результатов звонков по задачам

[Id] [int] NOT NULL — id типа результата
[Name] [nvarchar](500) NOT NULL — название типа результата звонка
1 - Неопределен
2 - Занято
3 - Не отвечает
4 - Недостаточное время обработки
5 - Успех
6 - Пропуск оператором
7 - Оператор отсутствует, пропустил обратный вызов по исходящей задаче
8 - Перезвонить позже
12 - Исключение номера при синхронизации
13 - Абонент прервал ожидание в очереди
15 - Отбой из дозванивающихся
16 - Исключение номера из обработки
17 - Исключение абонента из обработки
18 - Неуспех
19 - Отбой из очереди
20 - Удален оператором
21 - Входящий звонок. Превышено число подключений
22 - Входящий звонок вне периода активности
23 - Входящий звонок вне расписания
24 - Входящий звонок в неактивную задачу
25 - Превышено время ожидания в очереди
26 - Входящий звонок. Операторы отсутствует
27 - Входящий звонок. Операторы заняты
28 - Входящий звонок. Сбой или задача не найдена
29 - Перезвонить на другой номер
30 - Абонент не найден в таблице
31 - Обнаружен факс
*/
-- Выбираем все принятые звонки
	Declare @CallTaken table (IdChain uniqueidentifier)
	Insert Into @CallTaken
	Select ct.IdChain
	From (Select IdChain From @lcr_tmp ec
		Where IdChain is not null and  (ec.CallResult in (5, 8, 18, 29) or (ec.CallResult = 1 and ec.IsRecorded = 1))
		Group by IdChain
	) as ct

-- Удаляем все принятые звонки
	Delete From @lcr_tmp
	Where IdChain in (Select IdChain From @CallTaken)

	Delete @CallTaken
------------------------------------------------------------------------------------------------------

	Insert Into @tblCount 
	Select i.DTStart, i.DTStop, Count(ec.cnt), ec.IdTask 
	From (
		Select IdChain, Count(IdChain) Over(Partition By IdTask) as cnt, IdTask, Min(DateTimeStart) dtStart, Max(DateTimeStop) dtStop, Sum(LenQueue) LenQueue 
		From @lcr_tmp ec
		--where ec.LenTime != 0
		Group by IdChain, IdTask--, IdOperator
	) ec
	Inner join @intervals i on ec.dtStart between i.DTStart and i.DTStop
--	Inner join @intervals i on ec.dtStart >= i.DTStart and ec.dtStop <= i.DTStop
	Where ec.IdChain is not null
		and ((@wait_term = 1 and ec.LenQueue >= @wait_time) or (@wait_term = 2 and ec.LenQueue < @wait_time))
	Group By i.DTStart, i.DTStop, ec.IdTask--, IdOperator
	Order By i.DTStart

	Update o
	Set CntLost = tmp.Cnt, 
		LCR = case  
			when tmp.Cnt = 0 then 0.0
			when CntTotal = 0 then 0.0
			else Round(100 * Cast(tmp.Cnt as real)/CntTotal, 2)
			end
	From @Result o, @tblCount tmp
	Where o.DTStart = tmp.DTStart
		and (o.IdTask = tmp.IdTask)


	RETURN 
END