After.sql

BEGIN
	DECLARE mID numeric default 0;
	DECLARE arg1 varchar(100);
	DECLARE arg2 varchar(100);
	DECLARE arg3 varchar(100);
	DECLARE arg4 varchar(100);
	DECLARE arg5 varchar(100);
	DECLARE mBilltype varchar (2) default '1';
	DECLARE mCallID numeric default 0;
	DECLARE mCampID numeric default 0;
	DECLARE mStatus varchar(100) default '';
	DECLARE totaltime numeric default 0;
	DECLARE mtenantID numeric default 0;
	DECLARE mcall_cost numeric default 0;
  DECLARE istatus NUMERIC DEFAULT 0;
	DECLARE mlead_id NUMERIC default 0;
	DECLARE mPhone VARCHAR(100);
	DECLARE msessionID INTEGER DEFAULT 0;
	DECLARE mRate numeric default 0;
	DECLARE units  numeric default 0;
	DECLARE TrashVar INTEGER UNSIGNED;

	DECLARE EXIT HANDLER
	FOR SQLSTATE '40001' # (ER_LOCK_DEADLOCK) Retry when deadlock occured
	BEGIN
			ROLLBACK AND NO CHAIN; # or COMMIT AND NO CHAIN; 
			 
			SELECT SLEEP(FLOOR(RAND() * 5)) INTO TrashVar;
							 
			CALL CallComplete(interface, calluniqueid, mdata);
	END;

	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	SET autocommit := 0;
	START TRANSACTION;

  SELECT calls.callid,calls.camp_id, calls.leadid, sessionid INTO mCallID,mCampID, mlead_id, msessionID FROM `calls`  WHERE calls.`cuniqueid`=calluniqueid LIMIT 1;	

	IF msessionID !=0 THEN	
		SELECT agentcmpsession.agentid, agentcmpsession.`status` from agentcmpsession WHERE sessionid=msessionID;
	ELSE 
		SELECT agents.agentid,agent_sessions.`status`  INTO mID, mStatus FROM agents 
		INNER JOIN  agent_sessions ON (agents.agentid=agent_sessions.agentid AND agent_sessions.active='y') where agent_code = interface  LIMIT 1;
	END IF;

  SELECT SUBSTRING_INDEX(mdata,'|',1) INTO arg1;
	SELECT SUBSTRING_INDEX(mdata,'|',-1) INTO arg3;
	SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(mdata,'|',2), '|', -1) INTO arg2;

	DELETE FROM callbacks WHERE lead_phone=mPhone;

	SET totaltime = CONVERT(arg2,UNSIGNED);
	IF mStatus='TALKING' THEN
		UPDATE `agentcmpsession` SET talked=talked+CONVERT(arg2,UNSIGNED),status='READY' where agentid=mID and active='y';
	  UPDATE `agent_sessions` SET statustime=CURRENT_TIMESTAMP,status='READY' WHERE agentid=mID and active='y' and status!='READY';

		UPDATE calls SET talked = talked + CONVERT(arg2, UNSIGNED) WHERE callid = mCallID;
		-- UPDATE agentcmpsession set avgwaiting=IF(connects>0, waiting, waiting/connects), avgwrapped=IF(connects>0, wrapped, wrapped/connects), avgtalked=IF(connects>0, talked, talked/connects), statustime=CURRENT_TIMESTAMP, status='WRAP' WHERE sessionid=msessionID;
		UPDATE queue_member_table SET paused=1 WHERE agentid=mID;
  ELSEIF mStatus='READY'  THEN
		UPDATE calls SET talked = talked + CONVERT(arg2, UNSIGNED) WHERE callid = mCallID;
		UPDATE agentcmpsession set avgwaiting=IF(connects>0, waiting, waiting/connects), avgwrapped=IF(connects>0, wrapped, wrapped/connects), avgtalked=IF(connects>0, talked, talked/connects), statustime=CURRENT_TIMESTAMP WHERE agentid=mID and active='y';
  ELSEIF mStatus='ONHOLD'  THEN
 		UPDATE `agentcmpsession` SET holding=holding+CONVERT(arg2,UNSIGNED) where agentid=mID and active='y';
	  UPDATE `agent_sessions` SET statustime=CURRENT_TIMESTAMP,status='READY' where agentid=mID and active='y' AND status!='READY';

		UPDATE calls SET aholdtime = aholdtime + CONVERT(arg2, UNSIGNED) WHERE callid = mCallID;
		-- UPDATE agentcmpsession set avgwaiting=IF(connects>0, waiting, waiting/connects), avgwrapped=IF(connects>0, wrapped, wrapped/connects), avgtalked=IF(connects>0, talked, talked/connects), statustime=CURRENT_TIMESTAMP, status='WRAP' WHERE sessionid=msessionID;
  ELSEIF mStatus='UNHOLD'  THEN
 		UPDATE `agentcmpsession` SET holding=holding+CONVERT(arg2,UNSIGNED) where agentid=mID and active='y';
	  UPDATE `agent_sessions` SET statustime=CURRENT_TIMESTAMP,status='READY' where agentid=mID and active='y' AND status!='READY';

		UPDATE calls SET aholdtime = aholdtime + CONVERT(arg2, UNSIGNED) WHERE callid = mCallID;
  ELSE 
		UPDATE calls SET talked = talked + CONVERT(arg2, UNSIGNED) WHERE callid = mCallID;
		UPDATE queue_member_table SET paused=1 WHERE agentid=mID;
 	END IF;
  	
  -- DELETE from call_status WHERE callId = calluniqueid;
	UPDATE dialer_campaings SET linesdialed=linesdialed-1 WHERE camp_id=mCampID;

	SET autocommit := 1;
	COMMIT;
END

Before.sql

BEGIN
	DECLARE mID numeric default 0;
	DECLARE arg1 varchar(100);
	DECLARE arg2 varchar(100);
	DECLARE arg3 varchar(100);
	DECLARE arg4 varchar(100);
	DECLARE arg5 varchar(100);
	DECLARE mBilltype varchar (2) default '1';
	DECLARE mCallID numeric default 0;
	DECLARE mCampID numeric default 0;
	DECLARE mStatus varchar(100) default '';
	DECLARE totaltime numeric default 0;
	DECLARE mtenantID numeric default 0;
	DECLARE mcall_cost numeric default 0;
  DECLARE istatus NUMERIC DEFAULT 0;
	DECLARE mlead_id NUMERIC default 0;
	DECLARE mPhone VARCHAR(100);
	DECLARE msessionID INTEGER DEFAULT 0;
	DECLARE mRate numeric default 0;
	DECLARE units  numeric default 0;
	DECLARE TrashVar INTEGER UNSIGNED;

	DECLARE EXIT HANDLER
	FOR SQLSTATE '40001' # (ER_LOCK_DEADLOCK) Retry when deadlock occured
	BEGIN
			ROLLBACK AND NO CHAIN; # or COMMIT AND NO CHAIN; 
			 
			SELECT SLEEP(FLOOR(RAND() * 5)) INTO TrashVar;
							 
			CALL CallComplete(interface, calluniqueid, mdata);
	END;

	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	SET autocommit := 0;
	START TRANSACTION;

  SELECT calls.callid,calls.camp_id, calls.leadid, sessionid INTO mCallID,mCampID, mlead_id, msessionID FROM `calls`  WHERE calls.`cuniqueid`=calluniqueid LIMIT 1;	

	IF msessionID !=0 THEN	
		SELECT agentcmpsession.agentid, agentcmpsession.`status` from agentcmpsession WHERE sessionid=msessionID;
	ELSE 
		SELECT agents.agentid,agent_sessions.`status`  INTO mID, mStatus FROM agents 
		INNER JOIN  agent_sessions ON (agents.agentid=agent_sessions.agentid AND agent_sessions.active='y') where agent_code = interface  LIMIT 1;
	END IF;

  SELECT SUBSTRING_INDEX(mdata,'|',1) INTO arg1;
	SELECT SUBSTRING_INDEX(mdata,'|',-1) INTO arg3;
	SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(mdata,'|',2), '|', -1) INTO arg2;

	DELETE FROM callbacks WHERE lead_phone=mPhone;

	SET totaltime = CONVERT(arg2,UNSIGNED);
	IF mStatus='TALKING' THEN
		UPDATE `agentcmpsession` SET talked=talked+CONVERT(arg2,UNSIGNED),status='WRAP' where agentid=mID and active='y';
	  UPDATE `agent_sessions` SET statustime=CURRENT_TIMESTAMP,status='WRAP' WHERE agentid=mID and active='y' and status!='READY';

		UPDATE calls SET talked = talked + CONVERT(arg2, UNSIGNED) WHERE callid = mCallID;
		-- UPDATE agentcmpsession set avgwaiting=IF(connects>0, waiting, waiting/connects), avgwrapped=IF(connects>0, wrapped, wrapped/connects), avgtalked=IF(connects>0, talked, talked/connects), statustime=CURRENT_TIMESTAMP, status='WRAP' WHERE sessionid=msessionID;
		UPDATE queue_member_table SET paused=1 WHERE agentid=mID;
  ELSEIF mStatus='READY'  THEN
		UPDATE calls SET talked = talked + CONVERT(arg2, UNSIGNED) WHERE callid = mCallID;
		UPDATE agentcmpsession set avgwaiting=IF(connects>0, waiting, waiting/connects), avgwrapped=IF(connects>0, wrapped, wrapped/connects), avgtalked=IF(connects>0, talked, talked/connects), statustime=CURRENT_TIMESTAMP WHERE agentid=mID and active='y';
  ELSEIF mStatus='ONHOLD'  THEN
 		UPDATE `agentcmpsession` SET holding=holding+CONVERT(arg2,UNSIGNED) where agentid=mID and active='y';
	  UPDATE `agent_sessions` SET statustime=CURRENT_TIMESTAMP,status='WRAP' where agentid=mID and active='y' AND status!='READY';

		UPDATE calls SET aholdtime = aholdtime + CONVERT(arg2, UNSIGNED) WHERE callid = mCallID;
		-- UPDATE agentcmpsession set avgwaiting=IF(connects>0, waiting, waiting/connects), avgwrapped=IF(connects>0, wrapped, wrapped/connects), avgtalked=IF(connects>0, talked, talked/connects), statustime=CURRENT_TIMESTAMP, status='WRAP' WHERE sessionid=msessionID;
  ELSEIF mStatus='UNHOLD'  THEN
 		UPDATE `agentcmpsession` SET holding=holding+CONVERT(arg2,UNSIGNED) where agentid=mID and active='y';
	  UPDATE `agent_sessions` SET statustime=CURRENT_TIMESTAMP,status='WRAP' where agentid=mID and active='y' AND status!='READY';

		UPDATE calls SET aholdtime = aholdtime + CONVERT(arg2, UNSIGNED) WHERE callid = mCallID;
  ELSE 
		UPDATE calls SET talked = talked + CONVERT(arg2, UNSIGNED) WHERE callid = mCallID;
		UPDATE queue_member_table SET paused=1 WHERE agentid=mID;
 	END IF;
  	
  -- DELETE from call_status WHERE callId = calluniqueid;
	UPDATE dialer_campaings SET linesdialed=linesdialed-1 WHERE camp_id=mCampID;

	SET autocommit := 1;
	COMMIT;
END