midware_ws/NetLib/FirstStep/proc_DataAna...

create procedure [dbo].[proc_DataAnalysis]
@nAgentID	int,
@nLogInID	int,
@BeginTime	datetime,
@EndTime	datetime
as 

declare @answer_ext					bigint		--内线应答次数
declare @answer_trunk				bigint		--外线应答次数
declare @call_in_ext				bigint		--内线呼入次数
declare @call_in_trunk				bigint		--外线呼入次数
declare @call_in_totalTime_ext		bigint		--内线呼入总时长
declare @call_in_totalTime_trunk	bigint		--外线呼入总时长
declare @call_out_ext				bigint		--呼出内线次数
declare @call_out_trunk				bigint		--呼出外线次数
declare @call_out_totalTime_ext		bigint		--呼出内线总时长
declare @call_out_totalTime_trunk	bigint		--呼出外线总时长
declare @call_out_success_ext		bigint		--呼出内线成功次数
declare @call_out_success_trunk		bigint		--呼出外线成功次数
declare @over_work_totalTime		bigint		--话后处理总时长
declare @alerting_totalTime			bigint		--振铃总时长
declare @transfer_count				bigint		--转移次数
declare @be_transfer_count			bigint		--被转移次数
declare @conference_count			bigint		--三方会议次数
declare @be_conference_count		bigint		--被三方会议次数
declare @over_alerting_count		bigint		--久振未接数

declare @TalkTimesCount				bigint		--通话时长临时变量

--内线应答次数,内线呼入总时长
select @answer_ext=count(1),@call_in_totalTime_ext=isnull(sum(periodtalking),0) from dbo.rep_ext_call_in where calltype <> 1 and isanswer = 1 and CalleeAgentID = @nAgentID and TimeHangUp between @BeginTime and @EndTime

--外线应答次数,外线呼入总时长
select @answer_trunk=count(1),@call_in_totalTime_trunk=isnull(sum(periodtalking),0) from dbo.rep_ext_call_in where calltype = 1  and isanswer = 1 and CalleeAgentID = @nAgentID and TimeHangUp between @BeginTime and @EndTime

--内线呼入次数
select @call_in_ext=count(1) from dbo.rep_ext_call_in where calltype <> 1 and CalleeAgentID = @nAgentID and TimeHangUp between @BeginTime and @EndTime

--外线呼入次数
select @call_in_trunk=count(1) from dbo.rep_ext_call_in where calltype = 1  and CalleeAgentID = @nAgentID and TimeHangUp between @BeginTime and @EndTime

--呼出内线总次数
select @call_out_ext=count(1) from dbo.rep_ext_call_out where peerlinetype = 0 and CallerAgentID = @nAgentID and TimeHangUp between @BeginTime and @EndTime

--呼出外线总次数
select @call_out_trunk=count(1) from dbo.rep_ext_call_out where peerlinetype = 1 and CallerAgentID = @nAgentID and TimeHangUp between @BeginTime and @EndTime

--呼出内线总时长
select @call_out_totalTime_ext=isnull(sum(periodtalking),0) from dbo.rep_ext_call_out where peerlinetype = 0 and CallerAgentID = @nAgentID and TimeHangUp between @BeginTime and @EndTime

--呼出外线总时长
select @call_out_totalTime_trunk=isnull(sum(periodtalking),0) from dbo.rep_ext_call_out where peerlinetype = 1 and CallerAgentID = @nAgentID and TimeHangUp between @BeginTime and @EndTime

--呼出内线成功次数
select @call_out_success_ext=count(1) from dbo.rep_ext_call_out where peerlinetype = 0 and iscalloutsucceed = 1 and CallerAgentID = @nAgentID and TimeHangUp between @BeginTime and @EndTime

--呼出外线成功次数
select @call_out_success_trunk=count(1) from dbo.rep_ext_call_out where peerlinetype = 1 and iscalloutsucceed = 1 and CallerAgentID = @nAgentID and TimeHangUp between @BeginTime and @EndTime

--话后处理总时长
select @over_work_totalTime=isnull(sum(Duration),0) from dbo.rep_agent_state where State = 4 and AgentId = @nAgentID and LoginId = @nLogInID

--振铃总时长
select @alerting_totalTime=isnull(sum(PeriodAlerting),0) from dbo.rep_ext_call_in where CalleeAgentID = @nAgentID and TimeHangUp between @BeginTime and @EndTime

--转移次数
select @transfer_count=Count(1) from dbo.rep_transfer	where  CallerAgentID = @nAgentID and TimeBeginTransfer between @BeginTime and @EndTime

--被转移次数
select @be_transfer_count=Count(1) from dbo.rep_transfer	where  CalleeAgentID = @nAgentID and TimeBeginTransfer between @BeginTime and @EndTime

--三方会议次数
select @conference_count=Count(1) from dbo.rep_conference where  CallerAgentID = @nAgentID and TimeConference between @BeginTime and @EndTime

--被三方会议次数
select @be_conference_count=Count(1) from dbo.rep_conference where  CalleeAgentID = @nAgentID and TimeConference between @BeginTime and @EndTime

--久振未接数 默认值为大于10秒
select @over_alerting_count=Count(1) from dbo.rep_ext_call_in where PeriodAlerting > 10 and CalleeAgentID = @nAgentID and TimeAlerting  between @BeginTime and @EndTime

set @TalkTimesCount = @call_in_totalTime_ext + @call_in_totalTime_trunk + @call_out_totalTime_ext + @call_out_totalTime_trunk

--空闲时长=签入时长-通话时长-置忙时长-话后处理时长
update dbo.rep_agent_detail 
set FreeTimes = LoginTimes - @TalkTimesCount - ReposeTimes - @over_work_totalTime,
	TalkTimes = @TalkTimesCount,
	AnswerNumExt = @answer_ext,
	AnswerNumTrunk = @answer_trunk,
	CallInNumExt = @call_in_ext,
	CallInNumTrunk = @call_in_trunk,
	CallInTimesTotalExt = @call_in_totalTime_ext,
	CallInTimesTotalTrunk = @call_in_totalTime_trunk,
	CallOutNumExt = @call_out_ext,
	CallOutNumTrunk = @call_out_trunk,
	CallOutTimesTotalExt = @call_out_totalTime_ext,
	CallOutTimesTotalTrunk = @call_out_totalTime_trunk,
	CallOutOkNumExt = @call_out_success_ext,
	CallOutOkNumTrunk = @call_out_success_trunk,
	OverWorkTimesTotal = @over_work_totalTime,
	TimeAlertingTotal = @alerting_totalTime,
	TransferNum = @transfer_count,
	BeTransferNum = @be_transfer_count,
	ConferenceNum = @conference_count,
	BeConferenceNum = @be_conference_count,
	OverAlertingNum = @over_alerting_count
where AgentId = @nAgentID and LoginId = @nLogInID

update dbo.rep_agent_detail
set FreeTimes = 0
where AgentId = @nAgentID and LoginId = @nLogInID and FreeTimes<0