ОДФ Исход

DECLARE @DateStart datetime;
DECLARE @DateEnd   datetime;
DECLARE @TimeStart datetime;
DECLARE @TimeEnd   datetime;

SET @DateStart = '20190718 00:00:00.000'
SET @DateEnd = '20190718 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 ='B2B in ride'

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], num_drive
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 NOT IN (6, 7, 13) -- строка добавлена по AI-11600 14.11.2018, раньше было без нее

SELECT DISTINCT D1.IdChain
    , D2.DateWrite
    , D1.CallerID
    , D2.idtask
    , D1.Operator
    , D1.Casy_type
    , D1.Sub_type
    , D1.Sub_3
    , D1.result
    , D1.num_drive
INTO #olp_dialog_report
FROM #dialogtabletemp D1
LEFT JOIN #dialogtabletemp as D2
    ON D1.IdChain = D2.IdChain

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, num_drive int
    )
INSERT INTO @final
SELECT DISTINCT od1.idchain
    , OD2.DateWrite
    , OD2.CallerID
    , OD2.idtask
    , OD2.Operator
    , OD2.Casy_type
    , OD2.Sub_type
    , OD2.Sub_3
    , TM.MARK
    , NULL
    , OD2.result
    , OD2.num_drive
FROM #olp_dialog_report as OD1
LEFT JOIN oktell.dbo.A_Users au
    ON au.name=OD1.Operator
LEFT JOIN #olp_dialog_report as OD2
    ON OD1.IdChain = OD2.IdChain
LEFT JOIN oktell.dbo.tbl_MARK as TM
    ON TM.IDHCAIN = OD1.IdChain
LEFT JOIN oktell_cc_temp.dbo.A_Cube_CC_EffortConnections eff with (nolock)
    ON eff.IdChain=OD1.IdChain
WHERE OD1.DateWrite BETWEEN @DateStart AND @DateEnd
    AND CAST(cast(OD1.DateWrite as time)AS datetime) BETWEEN @TimeStart AND @TimeEnd
    AND OD1.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

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) [Оценка Оператором]
    , IIF(num_drive != -1, result, NULL) [Номер поездки]
    ,'http://gett.oktell.olp.site:4055/download/byscript?name=Test_voice&startparam1='+CAST(idchain as nvarchar(50))+'&async=0&timeout=10' [Ссылка на запись]
INTO #result_temp
FROM @final as od
LEFT JOIN [oktell].dbo.[A_TaskManager_Tasks] as tsk
    ON tsk.Id = od.idtask

DELETE @final


IF(@type=1 AND (@AbonentNumber='' OR @AbonentNumber IS NULL))
BEGIN
    SELECT * FROM #result_temp
    ORDER BY [Оператор]
END
IF((@type=0 or @type is null) and ( @AbonentNumber='' OR @AbonentNumber IS NULL))
BEGIN
    SELECT * FROM #result_temp
    WHERE casy_type IS NULL OR casy_type=''
    ORDER BY [Дата, Время]
END
IF((@type=0 OR @type IS NULL) AND ( @AbonentNumber<>'' OR @AbonentNumber IS NOT NULL))
BEGIN
    SELECT * FROM #result_temp
    WHERE (casy_type IS NULL OR casy_type='' ) AND [Номер абонента] LIKE '%'+@AbonentNumber+'%'
    ORDER BY [Дата, Время]
END
IF(@type=1 AND ( @AbonentNumber<>'' OR @AbonentNumber IS NOT NULL))
begin
    SELECT * FROM #result_temp
    where [Номер абонента] LIKE '%'+@AbonentNumber+'%'
end

DROP TABLE #result_temp