<?xml version="1.0" encoding="utf-16"?>
<ReportFile Version="100901">
  <Report>
    <Id>a47da12a-d9d8-4ebe-b915-fc9e6060fe31</Id>
    <Name>_ Oktell: Соотношение пропущенных и принятых звонков</Name>
    <GroupName>_Интервальные отчеты; по Задачам; по Операторам</GroupName>
    <Type>1</Type>
    <ViewType>6</ViewType>
    <ViewTag>1</ViewTag>
    <Description>_Соотношение пропущенных, т.е. не принятых оператором и принятых звонков по входящим задачам.
Отображает общее количество, обслуженных и потерянных звонков по задачам и операторам в каждом интервале. В зависимости от выбранного условия меняется количество потерянных вызовов.
Условия:
1. Учитывать все потерянные вызовы
2. Учитывать потерянные только после X сек.</Description>
    <TimerSec>0</TimerSec>
    <Details IsNull="0" Compiled="1">
      <sqltext>_
/*declare @dt1 datetime = '2015-07-13 00:00:00.000'
declare @dt2 datetime = '2015-07-27 23:59:59.999'
declare @interval int = 60
declare @tasks table ([Id] uniqueidentifier)
	insert into @tasks select  [Id]
	from [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task]
	where isoutput = 0
declare @slist int = 2
declare @xline int = 5
declare @operators table (id uniqueidentifier)
*/

declare @mnog float
declare @temp1 table ( idchain uniqueidentifier, [CallResult] int, [datetimestart] datetime, [lenqueue] float )
declare @temp table ( idchain uniqueidentifier,  [suc] int, [unsuc] int, [int] datetime )

select @mnog = case @interval
	when 60 then 23.999999999999
	else 23.999999999999  -- по 60 мин
	end
if (@interval is null)
	set @interval = 60



if not exists (select * from @tasks)
	insert into @tasks
	SELECT  [Id]
	FROM [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task]
	Where isoutput = 0

if not exists (select * from @operators)
 Insert into @operators ( Id ) 
 select id from [oktell_cc_temp].[dbo].A_Cube_CC_Cat_OperatorInfo


insert into @temp1
select	idchain, callresult, datetimestart, lenqueue
from [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections] 
where isoutput = 0 
	and Datetimestart between @dt1 and @dt2 
	and IDTask in (select * from @tasks)
	and idchain is not null
	and IDOperator in (select * from @operators)
	--and ((IdOperator is null and (callresult in (13,6, 19, 23,24,26,27,30))) or IdOperator in (select id from @operators)) 
	
--select * from @temp1 order by idchain
--select * from @temp1 where lenqueue &gt; 10 order by idchain
--select * from @temp1 where ([CallResult] = 5 or [CallResult] = 18 or lenqueue &lt;= 10)  order by idchain
--select * from @temp1 where ([CallResult] &lt;&gt; 5 and [CallResult] &lt;&gt; 18) and lenqueue &gt; 10 order by idchain
--select DateTimeStart, DateTimeStop, LenTime, LenQueue from [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
--	where ([CallResult] &lt;&gt; 5 and [CallResult] &lt;&gt; 18) and Datetimestart between @dt1 and @dt2 


		insert into @temp
		select [Idchain], [suc], [unsuc],
		[int]
		from (
			select	distinct [Idchain],
					[suc] = case 
					when exists (select * 
								from @temp1 tt2
								where tt2.idchain = tt1.idchain
								and ([CallResult] = 5 or [CallResult] = 18)) then 1
					else null
					end,
					
					[unsuc] = case @slist 
							  when 1 then
											case 
												when not exists (select * 
																		from @temp1 tt2
																		where tt2.idchain = tt1.idchain
																		and ([CallResult] = 5 or [CallResult] = 18)) then 1
												else null
											end
							   when 2 then
											case 
												when not exists (select * 
																		from @temp1 tt2
																		where tt2.idchain = tt1.idchain
																		and ([CallResult] = 5 or [CallResult] = 18 or lenqueue &lt;= @xline)) then 1
												else null
											end
							   end,
					
					[int] = case 						
						when @interval = 1 then cast(floor(cast (datetimestart as float)) as datetime)	
						when @interval = 7 then cast(floor(cast (datetimestart as float) - datepart (dw, datetimestart) + 1) as datetime)		
						when @interval = 12 then cast (cast(datepart (yy, datetimestart) as nvarchar (4)) + '-' + cast(datepart (mm, datetimestart) as nvarchar (2)) + '-01' as datetime)
						else 	
							cast(  
								( floor( cast(datetimestart as float) * @mnog) / @mnog - 
								floor(cast(datetimestart as float)) )
								as datetime)				
						end 
										
			from	@temp1 tt1
			) tb1

--select * from @temp order by int

select	[int] = case @interval
			when 60 then convert(nvarchar(5),[int],108) + '—' + convert(nvarchar(5),dateadd(hh,1,[int]),108)
			when 1 then convert(nvarchar(5),[int],4) + ' ' + cast(datepart(yy,[int]) as nvarchar(4)) -- + ' г.'
			when 7 then convert(nvarchar(5),[int] ,4) + '—' + 
      							convert(nvarchar(5), dateadd(dd, 7, [int]) ,4)
      		when 12 then case datepart(mm, [int])
      					when 1 then 'Январь '+ cast(datepart(yy, [int])as nvarchar(4))
						when 2 then 'Февраль '+ cast(datepart(yy, [int])as nvarchar(4))
      					when 3 then 'Март '+ cast(datepart(yy, [int])as nvarchar(4))
      					when 4 then 'Апрель '+ cast(datepart(yy, [int])as nvarchar(4))
      					when 5 then 'Май '+ cast(datepart(yy, [int])as nvarchar(4))
      					when 6 then 'Июнь '+ cast(datepart(yy, [int])as nvarchar(4))
      					when 7 then 'Июль '+ cast(datepart(yy, [int])as nvarchar(4))
      					when 8 then 'Август '+ cast(datepart(yy, [int])as nvarchar(4))
      					when 9 then 'Сентябрь '+ cast(datepart(yy, [int])as nvarchar(4))
      					when 10 then 'Октябрь '+ cast(datepart(yy, [int])as nvarchar(4))
      					when 11 then 'Ноябрь '+ cast(datepart(yy, [int])as nvarchar(4))
      					when 12 then 'Декабрь '+ cast(datepart(yy, [int])as nvarchar(4))
      					end
			else convert(nvarchar(5),[int],108) + '—' + convert(nvarchar(5),dateadd(mi,30,[int]),108)
			end, 
		count (*) [total],
		count (suc) [served],
		count (unsuc) [lost]
from @temp
where suc is not null or unsuc is not null
group by [int]</sqltext>
      <GroupFields1 Count="1">
        <gf100>_int</gf100>
        <gd100>_Интервал</gd100>
      </GroupFields1>
      <GroupFields2 Count="0" />
      <ValueFields Count="3">
        <vf00>_total</vf00>
        <vd00>_Всего звонков</vd00>
        <vs00>_</vs00>
        <vf01>_served</vf01>
        <vd01>_Обслужено</vd01>
        <vs01>_</vs01>
        <vf02>_lost</vf02>
        <vd02>_Потеряно</vd02>
        <vs02>_</vs02>
      </ValueFields>
      <Filter IsNull="0" Count="7">
        <fi00 IsNull="0">
          <Id>1</Id>
          <Caption>_Дата</Caption>
          <Code>_@dt1</Code>
          <FilterType>2</FilterType>
          <Query>_</Query>
        </fi00>
        <fi01 IsNull="0">
          <Id>2</Id>
          <Caption>_</Caption>
          <Code>_@dt2</Code>
          <FilterType>3</FilterType>
          <Query>_</Query>
        </fi01>
        <fi02 IsNull="0">
          <Id>3</Id>
          <Caption>_Задачи</Caption>
          <Code>_@tasks</Code>
          <FilterType>52</FilterType>
          <Query>_SELECT  [Id], [name]
FROM [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task]
Where isoutput = 0</Query>
        </fi02>
        <fi03 IsNull="0">
          <Id>4</Id>
          <Caption>_Операторы</Caption>
          <Code>_@operators</Code>
          <FilterType>13</FilterType>
          <Query>_</Query>
        </fi03>
        <fi04 IsNull="0">
          <Id>5</Id>
          <Caption>_Интервал</Caption>
          <Code>_@interval</Code>
          <FilterType>51</FilterType>
          <Query>_select 60, 'По 60 минут'
union all
select 1, 'По дням'
union all
select 7, 'По неделям'
union all
select 12, 'По месяцам'</Query>
        </fi04>
        <fi05 IsNull="0">
          <Id>6</Id>
          <Caption>_Условие</Caption>
          <Code>_@slist</Code>
          <FilterType>51</FilterType>
          <Query>_select 1, 'Учитывать все потерянные вызовы'
union all
select 2, 'Учитывать потерянные только после X сек.'</Query>
        </fi05>
        <fi06 IsNull="0">
          <Id>7</Id>
          <Caption>_X сек.</Caption>
          <Code>_@xline</Code>
          <FilterType>8</FilterType>
          <Query>_</Query>
        </fi06>
      </Filter>
    </Details>
  </Report>
</ReportFile>