Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- declare @type int = 0
- 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 @TypeClient table(id nvarchar(250), name nvarchar(250));
- insert into @TypeClient
- select distinct TypeClient, TypeClient from oktell..Olp_dialog
- declare @Status table(id nvarchar(250), name nvarchar(250))
- insert into @Status
- select distinct Status, Status from oktell..Olp_dialog
- declare @Solution table(id nvarchar(250), name nvarchar(250))
- insert into @Solution
- select distinct Solution, Solution from oktell..Olp_dialog
- declare @subjct2 int =1
- declare @Other nvarchar(250);
- set @Other = ''
- declare @AbonentNumber nvarchar(250);
- set @AbonentNumber = ''
- --@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
- declare @olp_dialog 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))
- --insert into @olp_dialog
- select od.IdChain,datewrite, callerID,od.idtask,Operator,Casy_type,Sub_type,Sub_3
- 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, раньше было без нее
- --insert into @olp_dialog
- 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
- 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)
- 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
- --case
- -- when
- -- (
- -- select sum(lentime) from oktell_cc_temp.[dbo].[A_Cube_CC_OperatorStates] where state=7 and idoperator=eff.IdOperator and ideffort in(select distinct ideffort from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections where idchain=od.IdChain)
- -- )>41
- --then
- -- (select datediff(SECOND,(select top 1 datetimestop from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with (nolock) where IdOperator = eff.IdOperator and idchain=od.IdChain order by datetimestop desc),(select max(DateTimeStop) from oktell_cc_temp.[dbo].[A_Cube_CC_OperatorStates] where state=7 and idoperator=eff.IdOperator and ideffort in(select distinct ideffort from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections where idchain=od.IdChain))))
- --else
- -- (
- -- select sum(lentime) from oktell_cc_temp.[dbo].[A_Cube_CC_OperatorStates] where state=7 and idoperator=eff.IdOperator and ideffort in(select distinct ideffort from oktell_cc_temp.dbo.A_Cube_CC_EffortConnections where idchain=od.IdChain)
- -- )
- --end
- 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
- DECLARE @ResultTable TABLE
- (
- Id uniqueidentifier,
- datewrite datetime, CallerId nvarchar(250), IdTask uniqueidentifier,
- [Name] nvarchar(250), Operator nvarchar(250)
- ,casy_type nvarchar(250), SubType nvarchar(250), Sub3 nvarchar(250), Mark int
- ,[Time] smalldatetime, Link nvarchar(250)
- )
- INSERT INTO @ResultTable
- SELECT idchain, datewrite [Дата, Время]
- ,callerid[Номер абонента]
- ,idtask
- ,tsk.Name[Задача]
- ,operator[Оператор]
- ,casy_type
- ,sub_type
- ,sub_3
- ,mark[Оценка]
- ,cast(dateadd(ss,post,'00:00') as time(0)) post
- ,'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
- DECLARE @ResultTable2 TABLE
- (
- Id uniqueidentifier,
- datewrite datetime, CallerId nvarchar(250), IdTask uniqueidentifier,
- [Name] nvarchar(250), Operator nvarchar(250)
- ,casy_type nvarchar(250), SubType nvarchar(250), Sub3 nvarchar(250), Mark int
- ,[Time] smalldatetime, Link nvarchar(250), OperMark int
- )
- INSERT INTO @ResultTable2
- SELECT *
- ,(SELECT TOP 1 result FROM [oktell].[dbo].[Olp_dialog] od WHERE od.IdChain = [@ResultTable].Id)
- FROM @ResultTable
- --select * from @ResultTable2
- IF (@type = 1 AND (@AbonentNumber='' or @AbonentNumber is null))
- BEGIN
- SELECT datewrite, CallerId ,[Name] ,Operator ,casy_type ,SubType ,Sub3 ,Mark ,[Time] ,Link ,OperMark
- FROM @ResultTable2
- ORDER BY Operator
- END
- IF (@type = 0 AND (@AbonentNumber='' or @AbonentNumber is null))
- BEGIN
- SELECT datewrite, CallerId ,[Name] ,Operator ,casy_type ,SubType ,Sub3 ,Mark ,[Time] ,Link ,OperMark
- FROM @ResultTable2
- where casy_type is null or casy_type=''
- order by datewrite
- END
- IF (@type=0 and ( @AbonentNumber<>'' or @AbonentNumber is not null))
- BEGIN
- SELECT datewrite, CallerId ,[Name] ,Operator ,casy_type ,SubType ,Sub3 ,Mark ,[Time] ,Link ,OperMark
- FROM @ResultTable2
- WHERE (casy_type is null or casy_type='' ) and callerid like '%'+@AbonentNumber+'%'
- ORDER BY datewrite
- END
- IF (@type = 1 AND (@AbonentNumber='' or @AbonentNumber is null))
- BEGIN
- SELECT datewrite, CallerId ,[Name] ,Operator ,casy_type ,SubType ,Sub3 ,Mark ,[Time] ,Link ,OperMark
- FROM @ResultTable2
- WHERE callerid LIKE '%'+@AbonentNumber+'%'
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement