Gett taxi

/*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 @type int = 0*/

    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



    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
,'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
,'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
,'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
,'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
/*select
datewrite as [Дата/Время],
mark as [Оценка],
operator as [Оператор],
sub_3 as [Комментарий],
case when (post is null) then 'Нет' else 'Да' end as [Есть запись],
tasks.name as [Задача],
post as [Ссылка на запись]
from @final as f
join oktell..A_TaskManager_Tasks as tasks
on f.idtask=tasks.id*/
/*

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 = ''


*/