Индивидуальная статистика v2 New

-- пока фильтры только на время
/*
25.09.2018 Казанцев В.С. Добавлены 2 специальных кусочка, для задач B2B VIP + исходящие. Везде увеличено счетчики задач с 15 до 17.
В последнем динамическом запросе не уверен в значениях @i and @j, их не менял.
*/
/*
declare @DateStart datetime;
declare @DateStop  datetime;
declare @TimeStart datetime;
declare @TimeStop  datetime;
declare @Operators table (id uniqueidentifier)
declare @TypeTime bit = 'False'
declare @TypeReport nvarchar(250)
    select 1, 'к каждой дате'
    union all
    select 2, 'к дате начала и конца'

declare @TASK_LIST table (id uniqueidentifier)

insert into @TASK_LIST select ID from A_TaskManager_Tasks

set @DateStart = '2018-09-17 00:00:00.000'
set @DateStop  = '2018-09-17 23:59:59.000'
set @TimeStart = '1900-01-01 08:00:00.000'
set @TimeStop  = '1900-01-01 23:59:59.000'

insert into @Operators
select ID from A_Users
*/
--values ('24E8D331-FE51-465D-B17D-2520E3F6D350')
--insert into @Operators
--values ('F714AD6F-A6FD-480B-B09B-194C19D75056')

declare @TempTime datetime;

if (@TimeStop = '1900-01-01 23:59:00.000')
set @TimeStop = '1900-01-01 23:59:59.999'

if (@TypeTime = 'True')
begin
    set @TempTime = @TimeStart;
    set @TimeStart = @TimeStop;
    set @TimeStop = @TempTime;
end


if (@TypeReport is null)

set @TypeReport = 1;


declare @idchain table(idchain uniqueidentifier)
declare @allcalls table(idchain uniqueidentifier, idtask uniqueidentifier, idoperator uniqueidentifier, state int,[ANSWER] INT)
declare @groupcalls table (idoperator uniqueidentifier, idtask uniqueidentifier, Increase int, Accept int, Lose int,[ANSWER] INT)
create table #result (idoperator uniqueidentifier
,I1 int,A1 int,L1 int,T1 INT
,I2 int,A2 int,L2 int,T2 INT
,I3 int,A3 int,L3 int,T3 INT
,I4 int,A4 int,L4 int,T4 INT
,I5 int,A5 int,L5 int,T5 INT
,I6 int,A6 int,L6 int,T6 INT
,I7 int,A7 int,L7 int,T7 INT
,I8 int,A8 int,L8 int,T8 INT
,I9 int,A9 int,L9 int,T9 INT
,I10 int,A10 int,L10 int,T10 INT
,I11 int,A11 int,L11 int,T11 INT
,I12 int,A12 int,L12 int,T12 INT
,I13 int,A13 int,L13 int,T13 INT
,I14 int,A14 int,L14 int,T14 INT
,I15 int,A15 int,L15 int,T15 INT
,I16 int,A16 int,L16 int,T16 INT
,I17 int,A17 int,L17 int,T17 INT
);
declare @Tasks table (ind1 int IDENTITY(1,1) PRIMARY KEY, ind nvarchar(2), id uniqueidentifier, name nvarchar(250), sum int)
declare @State table (ind int, name nvarchar(250), s nvarchar(2))

insert into @Tasks(ind,id,name,sum)
select SUBSTRING(B.Name, 1, 1), B.id, SUBSTRING(B.Name, 3, LEN(B.Name) - 3), 0
from oktell..tbl_Dashbord as A
left outer join oktell_cc_temp..A_Cube_CC_Cat_Task as B
on A.TaskID = B.id

/*специальный кусочек для задачи User-Class Courier, т.к. ее нету в дашбордах*/
insert into @Tasks(ind,id,name,sum)
select MAX(ind)+1, '39AC2F24-1E7D-4D98-9EB0-29270A05D53C', 'Исходящие звонки B2B VIP',0 from @Tasks
/*конец кусочка*/

/*специальный кусочек для задачи User-Class Courier, т.к. ее нету в дашбордах*/
insert into @Tasks(ind,id,name,sum)
select MAX(ind)+1, '63C1876A-2FC0-4A62-8B16-B0515D20A5D1', 'B2B VIP',0 from @Tasks
/*конец кусочка*/

/*специальный кусочек для задачи User-Class Courier, т.к. ее нету в дашбордах*/
insert into @Tasks(ind,id,name,sum)
select MAX(ind)+1, '1978EA8D-1838-4CF0-B5EE-70AEC7C88315', 'User-Class Courier',0 from @Tasks
/*конец кусочка*/

/*специальный кусочек для задачи B2B in ride, т.к. ее нету в дашбордах*/
insert into @Tasks(ind,id,name,sum)
select MAX(ind)+1, '1E7BA857-4A1A-4E76-98B0-24273C1120C7', 'B2B in ride',0 from @Tasks
/*конец кусочка*/

/*специальный кусочек для задачи Driver-Class Courier, т.к. ее нету в дашбордах*/
insert into @Tasks(ind,id,name,sum)
select MAX(ind)+1, '319EC8C2-FE14-4C6C-AFA2-01FCBC718C14', 'Driver-Class Courier',0 from @Tasks
/*конец кусочка*/

/*специальный кусочек для задачи Driver-Class Courier reserve, т.к. ее нету в дашбордах*/
insert into @Tasks(ind,id,name,sum)
select MAX(ind)+1, '6830E035-0316-4799-9034-6CBBD6D89C4E', 'Driver-Class Courier reserve',0 from @Tasks
/*конец кусочка*/

/*специальный кусочек для задачи B2C In ride, т.к. ее нету в дашбордах*/
insert into @Tasks(ind,id,name,sum)
select MAX(ind)+1, 'D69A7932-9761-44A4-8B03-E3BFD8577557', 'B2C In ride',0 from @Tasks
/*конец кусочка*/

/*специальный кусочек для задачи B2C Not in ride, т.к. ее нету в дашбордах*/
insert into @Tasks(ind,id,name,sum)
select MAX(ind)+1, 'B4CDB351-343C-4854-B024-090ED195F31C', 'B2C Not in ride',0 from @Tasks
/*конец кусочка*/

/*специальный кусочек для задачи B2D in corp ride, т.к. ее нету в дашбордах*/
insert into @Tasks(ind,id,name,sum)
select MAX(ind)+1, 'BBC7F13A-B219-413B-A8A3-4FC6D1325EC0', 'B2D in corp ride',0 from @Tasks
/*конец кусочка*/

/*специальный кусочек для задачи B2D Premium, т.к. ее нету в дашбордах*/
insert into @Tasks(ind,id,name,sum)
select MAX(ind)+1, '9DC79009-F8B6-4EF7-B405-98BE950C5B72', 'B2D Premium',0 from @Tasks
/*конец кусочка*/

/*специальный кусочек для задачи B2D Night, т.к. ее нету в дашбордах*/
insert into @Tasks(ind,id,name,sum)
select MAX(ind)+1, '4AB8D485-4F73-4F6B-B4A5-AAB8F2B3C1A7', 'B2D Night',0 from @Tasks
/*конец кусочка*/



insert into @State
values(1, 'Поступило', 'I')
insert into @State
values(2, 'Принято', 'A')
insert into @State
values(3, 'Пропущенно', 'L')
insert into @State
values(4, ' Ср. Время овтета(сек)', 'T')

if(@TypeReport=1)
begin
    insert into @idchain
    select distinct idchain from oktell..A_Stat_Connections_1x1
    where
        cast(TimeStart as date) between @DateStart and @DateStop
    and
        cast(cast(A_Stat_Connections_1x1.TimeStart as time)as datetime) between @TimeStart and @TimeStop
    and ConnectionType in (4, 7) and IdPrev is null and FROMCC = 1
end
else
begin
    insert into @idchain
    select distinct idchain from oktell..A_Stat_Connections_1x1
    where

    TimeStart between (cast(@DateStart as date) + @TimeStart) and
  (cast(@DateStop as date) + @TimeStop)
    and ConnectionType in (4, 7) and IdPrev is null and FROMCC = 1
end

insert into @allcalls (idoperator, idtask, idchain, state,[ANSWER])
select IdOperator, idtask, IdChain
    ,max(case when CallResult in (1,5) then 1 else 0 end)
    ,(SELECT  TOP(1) DATEDIFF(SS,[TimeStart],[TimeAnswer]) FROM [oktell].[dbo].[A_Stat_Connections_1x1]  AS [A_Stat_Connections_1x1] WHERE [A_Stat_Connections_1x1].[IdChain] = A_Cube_CC_EffortConnections.IdChain AND [A_Stat_Connections_1x1].[ConnectionType] IN (1,5) )
from oktell_cc_temp..A_Cube_CC_EffortConnections AS A_Cube_CC_EffortConnections
where
    IdChain in (select IdChain from @idchain)
and
    IdOperator is not null and IdOperator <> 'AB000000-0000-0000-0000-000000000000' and IdOperator <> 'BF000000-0000-0000-0000-000000000000'
and
    IdOperator in (select ID from @Operators)
and
    IdTask in (select id from @TASK_LIST)
group by IdOperator, IdChain, IdTask



insert into @groupcalls
select idoperator, idtask, isnull(COUNT(*),0), isnull(SUM(state),0), isnull(COUNT(*),0) - isnull(SUM(state),0),case when isnull(SUM(state),0) >0 then SUM(isnull([ANSWER],0))/isnull(SUM(state),0) else 0 end
from @allcalls
group by idtask, idoperator

insert into #result (idoperator,
                    I1, A1, L1, T1,
                    I2, A2, L2, T2,
                    I3, A3, L3, T3,
                    I4, A4, L4, T4,
                    I5, A5, L5, T5,
                    I6, A6, L6, T6,
                    I7, A7, L7, T7,
                    I8, A8, L8, T8,
                    I9, A9, L9, T9,
                    I10, A10, L10, T10,
                    I11, A11, L11, T11,
                    I12, A12, L12, T12,
                    I13, A13, L13, T13,
                    I14, A14, L14, T14,
                    I15, A15, L15, T15,
                    I16, A16, L16, T16,
                    I17, A17, L17, T17
)
select A.id,
isnull(Task1.Increase, 0), isnull(Task1.Accept, 0), isnull(Task1.Lose, 0),ISNULL(Task1.[ANSWER],0),
isnull(Task2.Increase, 0), isnull(Task2.Accept, 0), isnull(Task2.Lose, 0),ISNULL(Task2.[ANSWER],0),
isnull(Task3.Increase, 0), isnull(Task3.Accept, 0), isnull(Task3.Lose, 0),ISNULL(Task3.[ANSWER],0),
isnull(Task4.Increase, 0), isnull(Task4.Accept, 0), isnull(Task4.Lose, 0),ISNULL(Task4.[ANSWER],0),
isnull(Task5.Increase, 0), isnull(Task5.Accept, 0), isnull(Task5.Lose, 0),ISNULL(Task5.[ANSWER],0),
isnull(Task6.Increase, 0), isnull(Task6.Accept, 0), isnull(Task6.Lose, 0),ISNULL(Task6.[ANSWER],0),
isnull(Task7.Increase, 0), isnull(Task7.Accept, 0), isnull(Task7.Lose, 0),ISNULL(Task7.[ANSWER],0),
isnull(Task8.Increase, 0), isnull(Task8.Accept, 0), isnull(Task8.Lose, 0),ISNULL(Task8.[ANSWER],0),
isnull(Task9.Increase, 0), isnull(Task9.Accept, 0), isnull(Task9.Lose, 0),ISNULL(Task9.[ANSWER],0),
isnull(Task10.Increase, 0), isnull(Task10.Accept, 0), isnull(Task10.Lose, 0),ISNULL(Task10.[ANSWER],0),
isnull(Task11.Increase, 0), isnull(Task11.Accept, 0), isnull(Task11.Lose, 0),ISNULL(Task11.[ANSWER],0),
isnull(Task12.Increase, 0), isnull(Task12.Accept, 0), isnull(Task12.Lose, 0),ISNULL(Task12.[ANSWER],0),
isnull(Task13.Increase, 0), isnull(Task13.Accept, 0), isnull(Task13.Lose, 0),ISNULL(Task13.[ANSWER],0),
isnull(Task14.Increase, 0), isnull(Task14.Accept, 0), isnull(Task14.Lose, 0),ISNULL(Task14.[ANSWER],0),
isnull(Task15.Increase, 0), isnull(Task15.Accept, 0), isnull(Task15.Lose, 0),ISNULL(Task15.[ANSWER],0),
isnull(Task16.Increase, 0), isnull(Task16.Accept, 0), isnull(Task16.Lose, 0),ISNULL(Task16.[ANSWER],0),
isnull(Task17.Increase, 0), isnull(Task17.Accept, 0), isnull(Task17.Lose, 0),ISNULL(Task17.[ANSWER],0)

from @Operators as A
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 1))
     as [Task1]
     on A.id = Task1.idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 2))
     as [Task2]
     on A.id = Task2.idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 3))
     as [Task3]
     on A.id = Task3.idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 4))
     as [Task4]
     on A.id = Task4.idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 5))
     as [Task5]
     on A.id = [Task5].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 6))
     as [Task6]
     on A.id = [Task6].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 7))
     as [Task7]
     on A.id = [Task7].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 8))
     as [Task8]
     on A.id = [Task8].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 9))
     as [Task9]
     on A.id = [Task9].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 10))
     as [Task10]
     on A.id = [Task10].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 11))
     as [Task11]
     on A.id = [Task11].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 12))
     as [Task12]
     on A.id = [Task12].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 13))
     as [Task13]
     on A.id = [Task13].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 14))
     as [Task14]
     on A.id = [Task14].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 15))
     as [Task15]
     on A.id = [Task15].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 16))
     as [Task16]
     on A.id = [Task16].idoperator
left outer join
    (select B.idoperator as [idoperator], B.Increase as [Increase], B.Accept as [Accept], B.Lose as [Lose],B.[ANSWER] AS [ANSWER]
     from @groupcalls as B where B.idtask = (select id from @Tasks where ind1 = 17))
     as [Task17]
     on A.id = [Task17].idoperator


delete from #result
where I1 + I2 + I3 + I4 + I5 + I6 + I7 + I8 + I9 + I10 + I11 + I12 + I13 + I14 + I15 + I16 + I17
    + A1 + A2 + A3 + A4 + A5 + A6 + A7 + A8 + A9 + A10 + A11 + A12 + A13 + A14 + A15 + A16+ A17
    + L1 + L2 + L3 + L4 + L5 + L6 + L7 + L8 + L9 + L10 + L11 + L12 + L13 + L14 + L15 + L16 + L17 = 0

update @Tasks
set sum = isnull((select sum(Increase) from @groupcalls where idtask = A.id),0)
from @Tasks as A

declare @SQLText nvarchar(MAX);
declare @Name1 nvarchar(250);
declare @Name2 nvarchar(250);
declare @s nvarchar(4);
declare @i int;
declare @j int;
set @i = 1;
set @j = 1;
declare @sum int;
DECLARE @COUNT INT

SET @COUNT = (SELECT COUNT(*) FROM @TASK_LIST)

set @SQLText = 'select B.Name as ''Менеджер'',';
IF (SELECT COUNT(*) FROM @TASK_LIST) <17
BEGIN
while (@i <= 6)
begin
    set @j = 1;
    select @Name1 = Name, @sum = sum from @Tasks where ind1 = @i
    if (@sum > 0)
    begin
    while (@j <= 7)
    begin
        select @Name2 = Name, @s = s from @State where ind = @j;
        set @SQLText = @SQLText + 'A.' + @s + cast(@i as nvarchar(2)) + ' as ''' + @Name1 + '
' + @Name2 + ''','
        set @j = @j + 1;
    end
    end
    set @i = @i + 1;
end
END
set @SQLText = @SQLText + '
A.I1 + A.I2 + A.I3 + A.I4+A.I5 + A.I6 + A.I7 + A.I8 + A.I9 + A.I10 + A.I11 + A.I12 + A.I13 + A.I14 + A.I15 + A.I16 + A.I17 as ''Итого
Поступило'',
A.A1 + A.A2 + A.A3 + A.A4 + A.A5 + A.A6 + A.A7 + A.A8 + A.A9 + A.A10 + A.A11 + A.A12 + A.A13 + A.A14 + A.A15 + A.A16 + A.A17 as ''Итого
Принято'',
A.L1 + A.L2 + A.L3 + A.L4 + A.L5 + A.L6 + A.L7 + A.L8 + A.L9 + A.L10 + A.L11 + A.L12 + A.L13 + A.L14 + A.L15 + A.L16 + A.L17 as ''Итого
Пропущенно'',
(A.T1 + A.T2 + A.T3 + A.T4 + A.T5 + A.T6 + A.T7 + A.T8 + A.T9 + A.T10 + A.T11 + A.T12 + A.T13 + A.T14 + A.T15 + A.T16 + A.T17)/'+CAST(@COUNT AS NVARCHAR(10))+' AS [Итого среднее время ответа]
from
    #result as A
left outer join
    oktell..A_Users as B
on A.idoperator = B.id'

exec (@SQLText)


drop table #result