Код Виталия

/*
Доработал: Казанцев В.С.
Дата: 17.08.2018
Изменения: Добавлены перезвоны по задачам: Транспортный привлечение=Перезвон Транспортный привлечение, B2D Premium==Перезвон b2d-premium
B2D Night=Перезвон b2d-night, Driver-Class Courier=Перезвон Courier driver,
User-class courier B2C\User-class courier B2B = Перезвон Courier (Общий на B2C и B2B)
24.09.2018 Казанцев В.С. - Для адекватного срабатывания условия ELSE добавлены промежуточные переменные datetime, заменены в параметрах имена.
*/

/*
declare @D1 datetime;
declare @D2  datetime;
declare @T1 datetime;
declare @T2  datetime;

set @D1 = '2018-12-11 00:00:00'
set @D2  = '2018-12-11 23:59:00' -- не включительно.
set @T1 = '1900-01-01 12:00:00'
set @T2  = '1900-01-01 12:20:00'
declare @TypeTime nvarchar(50)
declare @TypeReport int=1
*/

declare @D1 datetime;
declare @D2  datetime;
declare @T1 datetime;
declare @T2  datetime;

set @D1 = convert(datetime,@DT1,103)
set @D2  = convert(datetime,@DT2,103)-- не включительно.
set @T1 = convert(datetime,@TM1,103)
set @T2 = convert(datetime,@TM2,103)

declare @TempTime datetime;

if (@TypeTime = 'True')
begin
    set @TempTime = @T1;
    set @T1 = @T2;
    set @T2 = @TempTime;
end

if (@TypeReport is null)
    set @TypeReport = 1;


DECLARE @TABLE_ TABLE  ( CON_TYPE INT, USERID UNIQUEIDENTIFIER , [TASKID]  UNIQUEIDENTIFIER, [IN_TALK] INT,[IN_IVR] INT, [LENQUEUE] INT,waittime int)

IF (@TypeReport = 1)
BEGIN
--INSERT INTO @TABLE_ (CON_TYPE,USERID,[TASKID],[IN_TALK],[IN_IVR],[LENQUEUE],waittime)
SELECT  [A_Stat_Connections_1x1].[ConnectionType] AS CON_TYPE
,[A_Stat_Connections_1x1_NEXT].[BUserId] AS USERID
,(SELECT  TOP(1) IdTask  FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]AS [A_Cube_CC_EffortConnections] with(nolock)
WHERE IdChain = [A_Stat_Connections_1x1].IdChain)/*[IdConn] = [A_Stat_Connections_1x1].Id)*/ AS [TaskId]
 ,DATEDIFF(SS,[A_Stat_Connections_1x1_NEXT].[TimeAnswer],[A_Stat_Connections_1x1_NEXT].[TimeStop]) AS  [IN_TALK]
 ,DATEDIFF(SS,[A_Stat_Connections_1x1].[TimeStart],[A_Stat_Connections_1x1].[TimeStOP]) AS [IN_IVR]
 ,(SELECT TOP (1) LenQueue  FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]AS [A_Cube_CC_EffortConnections] WHERE IdChain = [A_Stat_Connections_1x1].IdChain) AS LenQueue
,(select avg(datediff(ss,timestart,timestop)) from oktell.dbo.A_stat_Connections_1x1 where idchain =[A_Stat_Connections_1x1].idchain and bstr='ivr')
+
isnull((select avg(datediff(ss,datetimestart,datetimestop)) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock) where idchain in(select distinct idchain from oktell.dbo.tbl_log_twotasks where dt between @D1 and @D2
 and cast(cast(dt as time(0)) as datetime) between @T1 and @T2) and IdOperator='ab000000-0000-0000-0000-000000000000 ' and IdTask='319EC8C2-FE14-4C6C-AFA2-01FCBC718C14'),0)
 +
 isnull((select avg(datediff(ss,datetimestart,datetimestop)) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock) where idchain in(select distinct idchain from oktell.dbo.tbl_log_twotasks where dt between @D1 and @D2
 and cast(cast(dt as time(0)) as datetime) between @T1 and @T2) and IdOperator='ab000000-0000-0000-0000-000000000000 ' and IdTask='6830E035-0316-4799-9034-6CBBD6D89C4E'),0) qwe
  into #TEMPGETTALL
  FROM [oktell].[dbo].[A_Stat_Connections_1x1]  AS [A_Stat_Connections_1x1] with(nolock)
  LEFT JOIN  [oktell].[dbo].[A_Stat_Connections_1x1] AS [A_Stat_Connections_1x1_NEXT]
  ON [A_Stat_Connections_1x1_NEXT].[ID] = [A_Stat_Connections_1x1].IdNext
  WHERE [A_Stat_Connections_1x1].[ConnectionType] in(4) and [A_Stat_Connections_1x1].IdPrev IS NULL
  AND cast(A_Stat_Connections_1x1.TimeStart as date) between @D1 and @D2
  and cast(cast(A_Stat_Connections_1x1.TimeStart as time)as datetime) between @T1 and @T2
--Возможно зря убрал
--AND [A_Stat_Connections_1x1].[FromCC] = 1
  UNION ALL
  SELECT [A_Stat_Connections_1x1].[ConnectionType]
  ,[A_Stat_Connections_1x1].[AUserId]
  ,(SELECT  TOP(1) IdTask  FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]AS [A_Cube_CC_EffortConnections] with(nolock)
   WHERE IdChain = [A_Stat_Connections_1x1].IdChain)/*[IdConn] = [A_Stat_Connections_1x1].Id)*/ AS [TaskId]
  ,DATEDIFF(SS,[TimeAnswer],[TimeStop])AS  [IN_TALK]
  ,DATEDIFF(SS,[TimeStart],[TimeAnswer])AS  [IN_IVR]
  ,(SELECT TOP (1) LenQueue  FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]AS [A_Cube_CC_EffortConnections] with(nolock)
  WHERE IdChain = [A_Stat_Connections_1x1].IdChain) AS LenQueue
,(select avg(datediff(ss,timestart,timestop)) from oktell.dbo.A_stat_Connections_1x1 where idchain =[A_Stat_Connections_1x1].idchain and bstr='ivr')
+
isnull((select avg(datediff(ss,datetimestart,datetimestop)) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock) where idchain in(select distinct idchain from oktell.dbo.tbl_log_twotasks where dt between @D1 and @D2
 and cast(cast(dt as time(0)) as datetime) between @T1 and @T2) and IdOperator='ab000000-0000-0000-0000-000000000000 ' and IdTask='319EC8C2-FE14-4C6C-AFA2-01FCBC718C14'),0)
 +
 isnull((select avg(datediff(ss,datetimestart,datetimestop)) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock) where idchain in(select distinct idchain from oktell.dbo.tbl_log_twotasks where dt between @D1 and @D2
 and cast(cast(dt as time(0)) as datetime) between @T1 and @T2) and IdOperator='ab000000-0000-0000-0000-000000000000 ' and IdTask='6830E035-0316-4799-9034-6CBBD6D89C4E'),0)
   FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS [A_Stat_Connections_1x1] with(nolock)
  WHERE [A_Stat_Connections_1x1].[ConnectionType] IN(1) and [A_Stat_Connections_1x1].IdPrev IS NULL
  AND cast(A_Stat_Connections_1x1.TimeStart as date) between @D1 and @D2
  and cast(cast(A_Stat_Connections_1x1.TimeStart as time)as datetime) between @T1 and @T2
  AND [A_Stat_Connections_1x1].[FromCC] = 1
END
ELSE
BEGIN
--INSERT INTO @TABLE_ (CON_TYPE,USERID,[TASKID],[IN_TALK],[IN_IVR],[LENQUEUE],waittime)
SELECT  [A_Stat_Connections_1x1].[ConnectionType] AS [IVR]
,[A_Stat_Connections_1x1_NEXT].[BUserId] AS [BUserId]
,(SELECT  TOP(1) IdTask  FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]AS [A_Cube_CC_EffortConnections] with(nolock)
WHERE IdChain = [A_Stat_Connections_1x1].IdChain)/*[IdConn] = [A_Stat_Connections_1x1].Id)*/ AS [TaskId]
 ,DATEDIFF(SS,[A_Stat_Connections_1x1_NEXT].[TimeAnswer],[A_Stat_Connections_1x1_NEXT].[TimeStop]) AS  [IN_TALK]
 ,DATEDIFF(SS,[A_Stat_Connections_1x1].[TimeStart],[A_Stat_Connections_1x1].[TimeStOP]) AS [IN_IVR]
 ,(SELECT TOP (1) LenQueue  FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]AS [A_Cube_CC_EffortConnections] with(nolock) WHERE IdChain = [A_Stat_Connections_1x1].IdChain) AS LenQueue
,(select avg(datediff(ss,timestart,timestop)) from oktell.dbo.A_stat_Connections_1x1 where idchain =[A_Stat_Connections_1x1].idchain and bstr='ivr')
+
isnull((select avg(datediff(ss,datetimestart,datetimestop)) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock) where idchain in(select distinct idchain from oktell.dbo.tbl_log_twotasks where dt between @D1 and @D2
 and cast(cast(dt as time(0)) as datetime) between @T1 and @T2) and IdOperator='ab000000-0000-0000-0000-000000000000 ' and IdTask='319EC8C2-FE14-4C6C-AFA2-01FCBC718C14'),0)
 +
 isnull((select avg(datediff(ss,datetimestart,datetimestop)) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock) where idchain in(select distinct idchain from oktell.dbo.tbl_log_twotasks where dt between @D1 and @D2
 and cast(cast(dt as time(0)) as datetime) between @T1 and @T2) and IdOperator='ab000000-0000-0000-0000-000000000000 ' and IdTask='6830E035-0316-4799-9034-6CBBD6D89C4E'),0) qwe
  into #TEMPGETTALL2
  FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS [A_Stat_Connections_1x1] with(nolock)
  LEFT JOIN  [oktell].[dbo].[A_Stat_Connections_1x1] AS [A_Stat_Connections_1x1_NEXT] with(nolock)
  ON [A_Stat_Connections_1x1_NEXT].[ID] = [A_Stat_Connections_1x1].IdNext
  WHERE A_Stat_Connections_1x1.TimeStart between @D1 and @D2 and
  [A_Stat_Connections_1x1].[ConnectionType] in(4) and [A_Stat_Connections_1x1].IdPrev IS NULL
--Возможно зря убрал
--AND [A_Stat_Connections_1x1].[FromCC] = 1
  UNION ALL
  SELECT [A_Stat_Connections_1x1].[ConnectionType]

  ,[A_Stat_Connections_1x1].[AUserId]
  ,(SELECT  TOP(1) IdTask  FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]AS [A_Cube_CC_EffortConnections]with(nolock)
  WHERE IdChain = [A_Stat_Connections_1x1].IdChain)/*[IdConn] = [A_Stat_Connections_1x1].Id)*/ AS [TaskId]

  ,DATEDIFF(SS,[TimeAnswer],[TimeStop])AS  [IN_TALK]
  ,DATEDIFF(SS,[TimeStart],[TimeAnswer])AS  [IN_IVR]
  ,(SELECT TOP (1) LenQueue  FROM [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]AS [A_Cube_CC_EffortConnections]  with(nolock)
  WHERE IdChain = [A_Stat_Connections_1x1].IdChain) AS LenQueue
,(select avg(datediff(ss,timestart,timestop)) from oktell.dbo.A_stat_Connections_1x1 where idchain =[A_Stat_Connections_1x1].idchain and bstr='ivr')
+
isnull((select avg(datediff(ss,datetimestart,datetimestop)) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock) where idchain in(select distinct idchain from oktell.dbo.tbl_log_twotasks where dt between @D1 and @D2
 and cast(cast(dt as time(0)) as datetime) between @T1 and @T2) and IdOperator='ab000000-0000-0000-0000-000000000000 ' and IdTask='319EC8C2-FE14-4C6C-AFA2-01FCBC718C14'),0)
 +
 isnull((select avg(datediff(ss,datetimestart,datetimestop)) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock) where idchain in(select distinct idchain from oktell.dbo.tbl_log_twotasks where dt between @D1 and @D2
 and cast(cast(dt as time(0)) as datetime) between @T1 and @T2) and IdOperator='ab000000-0000-0000-0000-000000000000 ' and IdTask='6830E035-0316-4799-9034-6CBBD6D89C4E'),0)
   FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS [A_Stat_Connections_1x1] with(nolock)
  WHERE  [A_Stat_Connections_1x1].[ConnectionType] IN(1) and [A_Stat_Connections_1x1].IdPrev IS NULL
  AND A_Stat_Connections_1x1.TimeStart between @D1 and @D2
  AND [A_Stat_Connections_1x1].[FromCC] = 1
END


IF OBJECT_ID('tempdb..#TEMPGETTALL', 'U') IS NOT NULL
begin
DELETE #TEMPGETTALL WHERE [TASKID] IS NULL
truncate table oktell.[dbo].[olp_report_stat_all]
insert into oktell.[dbo].[olp_report_stat_all] select * from #TEMPGETTALL
DROP TABLE #TEMPGETTALL
end
else
--IF OBJECT_ID('#TEMPGETTALL2', 'U') IS NOT NULL
begin
DELETE #TEMPGETTALL2 WHERE [TASKID] IS NULL
truncate table oktell.[dbo].[olp_report_stat_all]
insert into oktell.[dbo].[olp_report_stat_all] select * from #TEMPGETTALL2
DROP TABLE #TEMPGETTALL2
end


declare @max_res int
set @max_res =  (SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE
[TaskId] IN ('D2A5DC6B-1522-415F-8892-9FF8D362E011','1A930507-4D94-4BE2-8B43-1BE99846C76B','27D3E314-A989-41D7-9B6D-D9E8FA347CC1','5C23239B-D4B2-4E8F-9FAF-CA61AA34792D'
,'48657090-561F-4C9C-8F67-64FB19C9CD08','651BC4E1-F838-442A-B2C9-BD89D350537E','467388EF-77CD-4928-8568-5CEEF9CCDCBC',
'3A8C638D-1583-4951-8050-E272275DF1AD','951DBBBB-7383-487A-A3E7-B745D1F15BD0','C3351AFC-B114-4CF8-807D-F3561B261F90','21F63D17-0345-4CCE-B9E1-5027053104F2','8FD43D4D-D38E-41BA-A37E-0F531AE6D265'
,'B1014253-FEC2-497D-AF6E-9F11B240384B'
) )

declare @temp25 table (idtask uniqueidentifier, cnt int)

if (@TypeReport = 1)
begin
insert into @temp25
select distinct TASKID,(select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock) where DateTimeStart between @D1 and @D2
and cast(cast(DateTimeStart as time(0)) as datetime) between @T1 and @T2 and CallResult = 25 and IdTask = TASKID) from oktell.[dbo].[olp_report_stat_all]
end
else
begin
insert into @temp25
select distinct TASKID,(select count(distinct idchain) from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock) where DateTimeStart between @D1 and @D2
and CallResult = 25 and IdTask = TASKID) from oktell.[dbo].[olp_report_stat_all]
end

SELECT
[A_TaskManager_Tasks].[Name] AS [Задача]
,COUNT(CON_TYPE) AS [Поступило]
,COUNT(USERID) AS [Принято]---(select cnt from @temp25 where idtask = RESULT_IN.TASKID)  AS [Принято]
,COUNT(CON_TYPE)-COUNT(USERID)  AS [Пропущено]
,(select cnt from @temp25 where idtask = RESULT_IN.TASKID) [Пропущено по таймауту]
,CAST(DATEADD(SS,CASE WHEN COUNT(USERID)>0 THEN SUM([IN_TALK])/COUNT(USERID)  ELSE 0 END,'1900-01-01 00:00:00') AS TIME(0)) AS [Среднее время в разговоре]
,CAST(DATEADD(SS,CASE WHEN COUNT(CON_TYPE)  >0 THEN SUM([IN_IVR])/COUNT(CON_TYPE)ELSE 0 END,'1900-01-01 00:00:00') AS TIME(0))  AS [Среднее время в IVR]
,CASE  [A_TaskManager_Tasks].[Name]
WHEN '1 Клиентская линия (English)' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = 'D2A5DC6B-1522-415F-8892-9FF8D362E011')
WHEN '3 Корпоративные клиенты' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = '1A930507-4D94-4BE2-8B43-1BE99846C76B')
WHEN '5 Vip-премиум' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = '27D3E314-A989-41D7-9B6D-D9E8FA347CC1')
WHEN 'Транспортный привлечение' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = '651BC4E1-F838-442A-B2C9-BD89D350537E')
WHEN 'B2C In ride' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = '48657090-561F-4C9C-8F67-64FB19C9CD08')
WHEN 'B2C Not in ride' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = '467388EF-77CD-4928-8568-5CEEF9CCDCBC')
WHEN 'B2B in ride' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = '3A8C638D-1583-4951-8050-E272275DF1AD')
WHEN 'B2D in corp ride' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = '951DBBBB-7383-487A-A3E7-B745D1F15BD0')
WHEN '6 Tap Taxi' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = '5C23239B-D4B2-4E8F-9FAF-CA61AA34792D')
WHEN 'B2D Premium' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = 'C3351AFC-B114-4CF8-807D-F3561B261F90')
WHEN 'B2D Night' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = '21F63D17-0345-4CCE-B9E1-5027053104F2')
WHEN 'User-class courier B2C' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = '8FD43D4D-D38E-41BA-A37E-0F531AE6D265')
WHEN 'Driver-Class Courier' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = 'B1014253-FEC2-497D-AF6E-9F11B240384B')
WHEN 'User-Class Courier B2B' THEN
(SELECT COUNT(*) FROM oktell.[dbo].[olp_report_stat_all] WHERE [TaskId] = '8FD43D4D-D38E-41BA-A37E-0F531AE6D265')
END  AS [Перезвоны]
,cast(cast(DATEADD(SS,cast(avg(lenqueue)as int),'00:00:00')as datetime) as time(0)) [Время ожидания]
FROM oktell.[dbo].[olp_report_stat_all] AS [RESULT_IN]
LEFT JOIN [oktell_settings].[dbo].[A_TaskManager_Tasks] AS [A_TaskManager_Tasks] with(nolock)
ON [RESULT_IN].[TaskId] = [A_TaskManager_Tasks].[Id]
WHERE [RESULT_IN].CON_TYPE = 4
GROUP BY [A_TaskManager_Tasks].[Name],result_in.taskid
UNION ALL
SELECT
'Итого'
,COUNT(CON_TYPE)
,COUNT(USERID)  - (select sum(cnt) from @temp25)
,COUNT(CON_TYPE)-COUNT(USERID)
,(select sum(cnt) from @temp25)
,CAST(DATEADD(SS,CASE WHEN COUNT(USERID)>0 THEN SUM([IN_TALK])/COUNT(USERID)  ELSE 0 END,'1900-01-01 00:00:00') AS TIME(0)) AS [Среднее время в разговоре]
,CAST(DATEADD(SS,CASE WHEN COUNT(CON_TYPE)  >0 THEN SUM([IN_IVR])/COUNT(CON_TYPE)ELSE 0 END,'1900-01-01 00:00:00') AS TIME(0))  AS [Среднее время в IVR]
,@max_res
,cast(cast(DATEADD(SS,cast(avg(lenqueue)as int),'00:00:00')as datetime) as time(0))
FROM oktell.[dbo].[olp_report_stat_all] AS [RESULT_IN]