Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*declare @DateStart datetime;
- declare @DateEnd datetime;
- declare @TimeStart datetime;
- declare @TimeEnd datetime;
- set @DateStart = '20180821 00:00:00.000'
- set @DateEnd = '20180821 23:59:00.000'
- set @TimeStart = '19000101 00:00:00.000'
- set @TimeEnd = '19000101 23:59:00.000'
- declare @tasks table (id uniqueidentifier)
- declare @opers table ( id uniqueidentifier)
- insert into @opers
- select distinct id from oktell.dbo.A_Users
- insert into @tasks
- select id from oktell.dbo.A_TaskManager_Tasks where name ='B2D Premium'
- --- не очень хорошее решение в отношении выпадающего списка. Будет тормозить.
- declare @AbonentNumber nvarchar(250);
- set @AbonentNumber = ''
- declare @type int = 0*/
- SELECT od.IdChain,datewrite, callerID,od.idtask,Operator,Casy_type,Sub_type,Sub_3,RESULT
- INTO #dialogtabletemp
- FROM oktell..Olp_dialog od
- --left join oktell.dbo.A_Users au on au.name=od.Operator Старый джойн, возможно пригодится, заменен на eff
- LEFT JOIN oktell_cc_temp.dbo.A_Cube_CC_EffortConnections eff WITH (nolock) ON eff.IdChain=od.IdChain
- WHERE od.DateWrite BETWEEN @DateStart AND @DateEnd
- AND
- CAST(CAST(od.DateWrite AS TIME)AS datetime) BETWEEN @TimeStart AND @TimeEnd
- AND od.idtask IN (SELECT id FROM @tasks)
- --and au.ID in (select id from @opers)
- AND eff.IdOperator IN (SELECT id FROM @opers)
- AND CallResult !=6 -- строка добавлена по AI-11600 14.11.2018, раньше было без нее
- SELECT DISTINCT IdChain
- ,(SELECT top 1 DateWrite FROM #dialogtabletemp dd2 WHERE dd2.IdChain=dd1.IdChain ORDER BY DateWrite) DateWrite
- ,CallerID
- ,(SELECT top 1 idtask FROM #dialogtabletemp dd2 WHERE dd2.IdChain=dd1.IdChain ORDER BY DateWrite DESC) idtask
- ,Operator
- ,Casy_type
- ,Sub_type
- ,Sub_3
- ,RESULT
- INTO #olp_dialog_report
- FROM #dialogtabletemp dd1
- DROP TABLE #dialogtabletemp
- DECLARE @final TABLE (idchain uniqueidentifier,datewrite datetime ,callerid nvarchar(50),idtask uniqueidentifier,operator nvarchar(100),casy_type nvarchar(MAX),sub_type nvarchar(MAX),sub_3 nvarchar(MAX),mark INT,post INT, RESULT INT)
- INSERT INTO @final
- SELECT DISTINCT od.idchain
- ,isnull((SELECT top 1 datewrite FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite ASC),(SELECT top 1 datewrite FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite DESC))
- ,isnull((SELECT top 1 CallerID FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite ASC),(SELECT top 1 CallerID FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite DESC))
- ,isnull((SELECT top 1 idtask FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite ASC),(SELECT top 1 idtask FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite DESC))
- ,isnull((SELECT top 1 Operator FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite ASC ),(SELECT top 1 Operator FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite DESC ))
- ,isnull((SELECT top 1 Casy_type FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite ASC),(SELECT top 1 Casy_type FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite DESC))
- ,isnull((SELECT top 1 Sub_type FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite ASC),(SELECT top 1 Sub_type FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite DESC))
- ,isnull((SELECT top 1 Sub_3 FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite ASC),(SELECT top 1 Sub_3 FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite DESC))
- ,(SELECT top 1 mark FROM oktell.dbo.tbl_MARK WHERE idhcain=od.IdChain)[Mark]
- ,NULL
- ,isnull((SELECT top 1 RESULT FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite ASC),(SELECT top 1 RESULT FROM #olp_dialog_report WHERE IdChain=od.IdChain ORDER BY datewrite DESC))
- FROM #olp_dialog_report od
- LEFT JOIN oktell.dbo.A_Users au ON au.name=od.Operator
- --left join oktell_cc_temp.dbo.A_Cube_CC_EffortConnections eff with (nolock) on eff.IdChain=od.IdChain
- WHERE od.DateWrite BETWEEN @DateStart AND @DateEnd
- AND
- CAST(CAST(od.DateWrite AS TIME)AS datetime) BETWEEN @TimeStart AND @TimeEnd
- AND od.idtask IN (SELECT id FROM @tasks)
- --and eff.IdOperator in (select id from @opers)
- AND au.ID IN (SELECT id FROM @opers)
- DROP TABLE #olp_dialog_report
- UPDATE @final
- SET post = (
- CASE
- WHEN
- (
- SELECT SUM(lentime) FROM oktell_cc_temp.[dbo].[A_Cube_CC_OperatorStates] WHERE state=7 AND idoperator=au.ID AND ideffort IN(SELECT DISTINCT ideffort FROM oktell_cc_temp.dbo.A_Cube_CC_EffortConnections WHERE idchain=t.idchain)
- )>41
- THEN
- (SELECT datediff(SECOND,(SELECT top 1 datetimestop FROM oktell_cc_temp.dbo.A_Cube_CC_EffortConnections WITH (nolock) WHERE IdOperator = au.ID AND idchain=t.idchain ORDER BY datetimestop DESC),(SELECT MAX(DateTimeStop) FROM oktell_cc_temp.[dbo].[A_Cube_CC_OperatorStates] WHERE state=7 AND idoperator=au.ID AND ideffort IN(SELECT DISTINCT ideffort FROM oktell_cc_temp.dbo.A_Cube_CC_EffortConnections WHERE idchain=t.idchain))))
- ELSE
- (
- SELECT SUM(lentime) FROM oktell_cc_temp.[dbo].[A_Cube_CC_OperatorStates] WHERE state=7 AND idoperator=au.ID AND ideffort IN(SELECT DISTINCT ideffort FROM oktell_cc_temp.dbo.A_Cube_CC_EffortConnections WHERE idchain=t.idchain)
- )
- END
- )
- FROM @final t
- LEFT JOIN oktell.dbo.a_users au ON au.Name = t.operator
- IF(@TYPE=1 AND (@AbonentNumber='' OR @AbonentNumber IS NULL))
- BEGIN
- SELECT datewrite [Дата, Время],callerid[Номер абонента],tsk.Name[Задача],operator[Оператор],casy_type,sub_type,sub_3,mark[Оценка],CAST(dateadd(ss,post,'00:00') AS TIME(0)) post, iif(RESULT != -1, RESULT, NULL) [Оценка Оператором]
- ,'http://gett.oktell.olp.site:4055/download/byscript?name=Test_voice&startparam1='+CAST(idchain AS nvarchar(50))+'&async=0&timeout=10' [Ссылка на запись]
- FROM @final od
- LEFT JOIN oktell.dbo.A_TaskManager_Tasks tsk ON tsk.id=od.idtask
- ORDER BY Оператор
- END
- IF((@TYPE=0 OR @TYPE IS NULL) AND ( @AbonentNumber='' OR @AbonentNumber IS NULL))
- BEGIN
- SELECT datewrite [Дата, Время],callerid[Номер абонента],tsk.Name[Задача],operator[Оператор],casy_type,sub_type,sub_3,mark[Оценка],CAST(dateadd(ss,post,'00:00') AS TIME(0)) post, iif(RESULT != -1, RESULT, NULL) [Оценка Оператором]
- ,'http://gett.oktell.olp.site:4055/download/byscript?name=Test_voice&startparam1='+CAST(idchain AS nvarchar(50))+'&async=0&timeout=10' [Ссылка на запись]
- FROM @final od
- LEFT JOIN oktell.dbo.A_TaskManager_Tasks tsk ON tsk.id=od.idtask
- WHERE casy_type IS NULL OR casy_type=''
- ORDER BY datewrite
- END
- IF((@TYPE=0 OR @TYPE IS NULL) AND ( @AbonentNumber<>'' OR @AbonentNumber IS NOT NULL))
- BEGIN
- SELECT datewrite [Дата, Время],callerid[Номер абонента],tsk.Name[Задача],operator[Оператор],casy_type,sub_type,sub_3,mark[Оценка],CAST(dateadd(ss,post,'00:00') AS TIME(0)) post, iif(RESULT != -1, RESULT, NULL) [Оценка Оператором]
- ,'http://gett.oktell.olp.site:4055/download/byscript?name=Test_voice&startparam1='+CAST(idchain AS nvarchar(50))+'&async=0&timeout=10' [Ссылка на запись]
- FROM @final od
- LEFT JOIN oktell.dbo.A_TaskManager_Tasks tsk ON tsk.id=od.idtask
- WHERE (casy_type IS NULL OR casy_type='' ) AND callerid LIKE '%'+@AbonentNumber+'%'
- ORDER BY datewrite
- END
- IF(@TYPE=1 AND ( @AbonentNumber<>'' OR @AbonentNumber IS NOT NULL))
- BEGIN
- SELECT datewrite [Дата, Время],callerid[Номер абонента],tsk.Name[Задача],operator[Оператор],casy_type,sub_type,sub_3,mark[Оценка],CAST(dateadd(ss,post,'00:00') AS TIME(0)) post, iif(RESULT != -1, RESULT, NULL) [Оценка Оператором]
- ,'http://gett.oktell.olp.site:4055/download/byscript?name=Test_voice&startparam1='+CAST(idchain AS nvarchar(50))+'&async=0&timeout=10' [Ссылка на запись]
- FROM @final od
- LEFT JOIN oktell.dbo.A_TaskManager_Tasks tsk ON tsk.id=od.idtask
- WHERE callerid LIKE '%'+@AbonentNumber+'%'
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement