Отчет по показателям опера Северсталь

DECLARE @DATE_START datetime = '20190801 00:00'
DECLARE @DATE_END datetime = '20190802 23:59'

DECLARE @TIME_START DATETIME = '19000101 00:00'
DECLARE @TIME_END DATETIME = '19000101 23:59'

DECLARE @TASKS TABLE (Id uniqueidentifier)

INSERT INTO @tasks
SELECT id
FROM [oktell].[dbo].[A_TaskManager_Tasks]

DECLARE @OPERATORS TABLE (Id uniqueidentifier)

INSERT INTO @operators
SELECT Id
FROM [oktell_settings].[dbo].[A_Users]

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------ОТФИЛЬТРОВЫВАЕМ И СОЕДИНЯЕМ ТАБЛИЦЫ-----------------------------------------------------------
DECLARE @OperStates TABLE
    (
        IdTask uniqueidentifier
        , IdOperator uniqueidentifier
        , TimeStart datetime
        , [State] int
        , ICode int
        , LenTime int
        , UserState int
    );

WITH FilteredTable AS
(
    SELECT IdTask
        , IdOperator
        , DateTimeStart
        , [State]
        , ICode
        , LenTime
    FROM [oktell_cc_temp].[dbo].[A_Cube_CC_OperatorStates] WITH (NOLOCK)
    WHERE DateTimeStart BETWEEN @DATE_START AND @DATE_END
        AND TimeStart BETWEEN @TIME_START AND @TIME_END
        AND IdOperator IN (SELECT * FROM @OPERATORS)
)

INSERT INTO @OperStates
SELECT IdTask
    , IdOperator
    , DateTimeStart
    , OS.[State]
    , OS.ICode
    , CAST(LenTime as int)
    , US.[State]
FROM FilteredTable as OS
LEFT JOIN [oktell].[dbo].[A_UserStateHistory] as US
    ON US.UserId = OS.IdOperator AND OS.DateTimeStart = US.TimeChange

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

---------СЧИТАЕМ ПРИНЯТЫЕ--------------------------------------------------------------
DECLARE @Accepted TABLE(OperId uniqueidentifier, [Count] int, AvgLenTime int)

INSERT INTO @Accepted
SELECT IdOperator, COUNT(DISTINCT IdChain), AVG(LenTime)
FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
WHERE DateTimeStart BETWEEN @DATE_START AND @DATE_END
    AND TimeStart BETWEEN @TIME_START AND @TIME_END
    AND BLineNum <> 'IVR' AND IsOutput = 0
    AND IdTask IN (SELECT * FROM @TASKS)
    AND IdOperator IN (SELECT * FROM @OPERATORS)
    AND IsRecorded = 1
GROUP BY IdOperator
----------------------------------------------------------------------------------------

---------ФИЛЬТРУЕМ [oktell].[dbo].[A_Stat_Connections_1x1]------------------------------
DECLARE @StatConnections TABLE
    (
        AUserId uniqueidentifier, BUserId uniqueidentifier
        , TimeStart datetime, TimeStop datetime
        , ConnectionType int, IdChain uniqueidentifier
    )

INSERT INTO @StatConnections
SELECT AUserId, BUserId, TimeStart, TimeStop, ConnectionType, IdChain
FROM [oktell].[dbo].[A_Stat_Connections_1x1]
WHERE TimeStart BETWEEN @DATE_START AND @DATE_END
    AND CAST(TimeStart as time) BETWEEN CAST(@TIME_START as time) AND CAST(@TIME_END as time)
---------------------------------------------------------------------------------------

---------СЧИТАЕМ ВХОДЯЩИЕ--------------------------------------------------------------
DECLARE @Incoming TABLE(OperId uniqueidentifier, [Count] int, [LenTime] int)

INSERT INTO @Incoming
SELECT BUserId, COUNT(DISTINCT IdChain), SUM(DATEDIFF(SECOND, TimeStart, TimeStop))
FROM @StatConnections
WHERE ConnectionType = 5
    AND BUserId IN (SELECT * FROM @OPERATORS)
GROUP BY BUserId;
-----------------------------------------------------------------------------------------

---------СЧИТАЕМ ИСХОДЯЩИЕ--------------------------------------------------------------
DECLARE @Outgoing TABLE(OperId uniqueidentifier, [LenTime] int)

INSERT INTO @Outgoing
SELECT AUserId, SUM(DATEDIFF(SECOND, TimeStart, TimeStop))
FROM @StatConnections
WHERE ConnectionType = 1
    AND AUserId IN (SELECT * FROM @OPERATORS)
GROUP BY AUserId;
-----------------------------------------------------------------------------------------

------ПОДСЧИТЫВАЕМ ВСЕ ЗНАЧЕНИЯ----------------------------------------------------------
WITH FinalTable AS
(
    SELECT IdOperator
        , (SELECT TOP 1 TimeStart FROM @OperStates ORDER BY TimeStart) as TimeStart
        , SUM(LenTime) as SumTotal
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE [State] = 10                                           AND OS.IdOperator = IdOperator), 0) as Ready
        , ISNULL((SELECT TOP 1 [LenTime] FROM @Incoming WHERE OperId = OS.IdOperator), 0) as IncomingCallsTime
        , ISNULL((SELECT TOP 1 [LenTime] FROM @Outgoing WHERE OperId = OS.IdOperator), 0) as OutgoingCallsTime
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 101                                            AND OS.IdOperator = IdOperator), 0) as CallDirector
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 102                                            AND OS.IdOperator = IdOperator), 0) as Consult
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 103                                            AND OS.IdOperator = IdOperator), 0) as Training
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 104                                            AND OS.IdOperator = IdOperator), 0) as Lunch
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 105                                            AND OS.IdOperator = IdOperator), 0) as Break5Minutes
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ([State] = 9 AND UserState IS NULL OR UserState = 3)   AND OS.IdOperator = IdOperator), 0) as [Break]
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 106                                            AND OS.IdOperator = IdOperator), 0) as Solution
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 107                                            AND OS.IdOperator = IdOperator), 0) as Status1
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 108                                            AND OS.IdOperator = IdOperator), 0) as Status2
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 109                                            AND OS.IdOperator = IdOperator), 0) as Missing
        , ISNULL((SELECT SUM(LenTime) FROM @OperStates WHERE ICode = 110                                            AND OS.IdOperator = IdOperator), 0) as Post
        , ISNULL((SELECT TOP 1 [Count] FROM @Incoming WHERE OperId = OS.IdOperator), 0) as AllIncomingCallsCount
        , ISNULL((SELECT TOP 1 [Count] FROM @Accepted WHERE OperId = OS.IdOperator), 0) as AcceptedCount
        , ISNULL((SELECT TOP 1 AvgLenTime FROM @Accepted WHERE OperId = OS.IdOperator), 0) as AcceptedAvg
    FROM @OperStates AS OS
    GROUP BY (IdOperator)
)

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

SELECT OI.[Name] as 'Оператор'
    , FT.TimeStart as 'Время начала'
    , (SELECT [dbo].[GetTimeFromSecond](FT.SumTotal)) as 'Всего в работе'
    , (SELECT [dbo].[GetTimeFromSecond](FT.Ready)) as 'Готов'
    , (SELECT [dbo].[GetPersent](FT.Ready, FT.SumTotal)) as 'Готов / Всего в работе'
    , (SELECT [dbo].[GetTimeFromSecond](FT.IncomingCallsTime)) as 'Время входящих звонков'
    , (SELECT [dbo].[GetPersent](FT.IncomingCallsTime, FT.SumTotal)) as 'Входящих звонков / Всего в работе'
    , (SELECT [dbo].[GetTimeFromSecond](FT.OutgoingCallsTime)) as 'Время исходящих звонков'
    , (SELECT [dbo].[GetPersent](FT.OutgoingCallsTime, FT.SumTotal)) as 'Исходящих звонков / Всего в работе'
    , (SELECT [dbo].[GetTimeFromSecond](FT.CallDirector)) as 'Вызов к руководителю'
    , (SELECT [dbo].[GetTimeFromSecond](FT.Consult)) as 'Консультация'
    , (SELECT [dbo].[GetTimeFromSecond](FT.Training)) as 'Обучение'
    , (SELECT [dbo].[GetTimeFromSecond](FT.Lunch)) as 'Обед'
    , (SELECT [dbo].[GetTimeFromSecond](FT.Break5Minutes)) as 'Перерыв 5 минут'
    , (SELECT [dbo].[GetTimeFromSecond](FT.[Break])) as 'Перерыв'
    , (SELECT [dbo].[GetTimeFromSecond](FT.Solution)) as 'Решение обращения'
    , (SELECT [dbo].[GetTimeFromSecond](FT.Status1)) as 'Статус1'
    , (SELECT [dbo].[GetTimeFromSecond](FT.Status2)) as 'Статус2'
    , (SELECT [dbo].[GetTimeFromSecond](FT.Missing)) as 'Отсутствует'
    , (SELECT [dbo].[GetTimeFromSecond](FT.Post)) as 'Постобработка'
    , FT.AllIncomingCallsCount as 'Количество всех звонков'
    , FT.AcceptedCount as 'Принятые звонки'
    , (SELECT [dbo].[GetPersent](FT.AcceptedCount, FT.AllIncomingCallsCount)) as 'Принятые / Все'
    , (SELECT [dbo].[GetTimeFromSecond](FT.AcceptedAvg)) as 'Среднее время разговора'
FROM FinalTable as FT
LEFT JOIN [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo] as OI
     ON FT.IdOperator = OI.Id