Middleware_5.1/FirstStep/proc_DataAna...

CREATE DEFINER=`root`@`%` PROCEDURE `proc_DataAnalysis`(nAgentID int,nLogInID int,BeginTime datetime,EndTime datetime)
BEGIN

DECLARE answer_ext                int;
DECLARE answer_trunk              int;
DECLARE call_in_ext               int;
DECLARE call_in_trunk             int;
DECLARE call_in_totalTime_ext     int;
DECLARE call_in_totalTime_trunk   int;
DECLARE call_out_ext              int;
DECLARE call_out_trunk            int;
DECLARE call_out_totalTime_ext    int;
DECLARE call_out_totalTime_trunk  int;
DECLARE call_out_success_ext      int;
DECLARE call_out_success_trunk    int;
DECLARE over_work_totalTime       int;
DECLARE alerting_totalTime        int;
DECLARE transfer_count            int;
DECLARE be_transfer_count         int;
DECLARE conference_count          int;
DECLARE be_conference_count       int;
DECLARE over_alerting_count       int;

DECLARE TalkTimesCount            int;

SELECT COUNT(1),IFNULL(SUM(periodtalking),0) into answer_ext,call_in_totalTime_ext FROM rep_ext_call_in WHERE calltype <> 1 AND isanswer = 1 AND CalleeAgentID = nAgentID AND TimeHangUp BETWEEN BeginTime AND EndTime;

SELECT COUNT(1),IFNULL(SUM(periodtalking),0) into answer_trunk,call_in_totalTime_trunk FROM rep_ext_call_in WHERE calltype = 1  AND isanswer = 1 AND CalleeAgentID = nAgentID AND TimeHangUp BETWEEN BeginTime AND EndTime;

SELECT COUNT(1) into call_in_ext FROM rep_ext_call_in WHERE calltype <> 1 AND CalleeAgentID = nAgentID AND TimeHangUp BETWEEN BeginTime AND EndTime;

SELECT COUNT(1) into call_in_trunk FROM rep_ext_call_in WHERE calltype = 1  AND CalleeAgentID = nAgentID AND TimeHangUp BETWEEN BeginTime AND EndTime;

SELECT COUNT(1) into call_out_ext FROM rep_ext_call_out WHERE peerlinetype = 0 AND CallerAgentID = nAgentID AND TimeHangUp BETWEEN BeginTime AND EndTime;

SELECT COUNT(1) into call_out_trunk FROM rep_ext_call_out WHERE peerlinetype = 1 AND CallerAgentID = nAgentID AND TimeHangUp BETWEEN BeginTime AND EndTime;

SELECT IFNULL(SUM(periodtalking),0) into call_out_totalTime_ext FROM rep_ext_call_out WHERE peerlinetype = 0 AND CallerAgentID = nAgentID AND TimeHangUp BETWEEN BeginTime AND EndTime;

SELECT IFNULL(SUM(periodtalking),0) into call_out_totalTime_trunk FROM rep_ext_call_out WHERE peerlinetype = 1 AND CallerAgentID = nAgentID AND TimeHangUp BETWEEN BeginTime AND EndTime;

SELECT COUNT(1) into call_out_success_ext FROM rep_ext_call_out WHERE peerlinetype = 0 AND iscalloutsucceed = 1 AND CallerAgentID = nAgentID AND TimeHangUp BETWEEN BeginTime AND EndTime;

SELECT COUNT(1) into call_out_success_trunk FROM rep_ext_call_out WHERE peerlinetype = 1 AND iscalloutsucceed = 1 AND CallerAgentID = nAgentID AND TimeHangUp BETWEEN BeginTime AND EndTime;

SELECT IFNULL(SUM(Duration),0) into over_work_totalTime FROM rep_agent_state WHERE State = 4 AND AgentId = nAgentID AND LoginId = nLogInID;

SELECT IFNULL(SUM(PeriodAlerting),0) into alerting_totalTime FROM rep_ext_call_in WHERE CalleeAgentID = nAgentID AND TimeHangUp BETWEEN BeginTime AND EndTime;

SELECT Count(1) into transfer_count FROM rep_transfer	WHERE  CallerAgentID = nAgentID AND TimeBeginTransfer BETWEEN BeginTime AND EndTime;

SELECT Count(1) into be_transfer_count FROM rep_transfer	WHERE  CalleeAgentID = nAgentID AND TimeBeginTransfer BETWEEN BeginTime AND EndTime;

SELECT Count(1) into conference_count FROM rep_conference WHERE  CallerAgentID = nAgentID AND TimeConference BETWEEN BeginTime AND EndTime;

SELECT Count(1) into be_conference_count FROM rep_conference WHERE  CalleeAgentID = nAgentID AND TimeConference BETWEEN BeginTime AND EndTime;

SELECT Count(1) into over_alerting_count FROM rep_ext_call_in WHERE PeriodAlerting > 10 AND CalleeAgentID = nAgentID AND TimeHangUp  BETWEEN BeginTime AND EndTime;


SET TalkTimesCount =  call_in_totalTime_ext + call_in_totalTime_trunk + call_out_totalTime_ext + call_out_totalTime_trunk;
UPDATE 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 rep_agent_detail
SET FreeTimes = 0
WHERE AgentId = nAgentID AND LoginId = nLogInID AND FreeTimes<0;

END