DROP PROCEDURE IF EXISTS `addAgentCurrentCall`; DELIMITER // CREATE PROCEDURE `addAgentCurrentCall`(IN `agent_id` INT, IN `extension_no` VARCHAR(20), IN `linked_id` VARCHAR(200), IN `caller_id` VARCHAR(20), IN `call_type` VARCHAR(20)) NO SQL BEGIN INSERT INTO agent_current_call(agent_id,extension_no,linked_id,caller_id,call_type) values(agent_id,extension_no,linked_id,caller_id,call_type); END// DELIMITER ; -- Dumping structure for procedure version1.addCdrData DROP PROCEDURE IF EXISTS `addCdrData`; DELIMITER // CREATE PROCEDURE `addCdrData`( IN `unique_id` VARCHAR(25), IN `linked_id` VARCHAR(25), IN `trunk_channel` VARCHAR(25), IN `exten` VARCHAR(20), IN `caller_id` VARCHAR(20), IN `call_type` VARCHAR(15), IN `did` VARCHAR(20), IN `start_time` VARCHAR(50), IN `acd_time` VARCHAR(50), IN `exten_ring_time` VARCHAR(50), IN `exten_time` VARCHAR(50), IN `exten_ans_time` VARCHAR(50), IN `end_time` VARCHAR(50), IN `acd` VARCHAR(15), IN `cause` CHAR(50), IN `agent_disconnect` INT(1), IN `out_call_type` VARCHAR(15), IN `dtmf` TINYTEXT, IN `feedback` TINYTEXT, IN `process_id` INT, IN `campaign_id` INT, IN `mapid` INT, IN `userfield` VARCHAR(15), IN `hold_duration` INT, IN `call_hangup_reason` VARCHAR(150), IN `duration` INT, IN `billsec` INT, IN `recording_file` VARCHAR(200), IN `end_call` INT(1), IN `disposition` VARCHAR(100), IN `ivr_name` VARCHAR(200) ) NO SQL DETERMINISTIC BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET @agent_name_var=NULL; SET @client_id_var=NULL; SET @process_id_var=NULL; SET @login_process_var=NULL; SET @crm_id_var=NULL; SET @map_id_var=NULL; SET @current_campaign_var=NULL; SET @campaign_id_var=NULL; SET @connected=0; IF exten_ans_time is not NULL and exten_ans_time!='' THEN SET @connected=1; ELSEIF call_type='Outgoing' AND disposition='ANSWER' THEN SET @connected=1; END IF; IF exten!='' and exten IS NOT NULL THEN select agent_name,login_process,current_crm_id,current_map_id,current_campaign into @agent_name_var,@login_process_var,@crm_id_var,@map_id_var,@current_campaign_var from agent_current_states WHERE login_extension_no=exten; END IF; IF campaign_id!='' AND campaign_id>0 AND campaign_id IS NOT NULL THEN SELECT client_id,campaign.process_id into @client_id_var,@process_id_var from campaign JOIN process on campaign.process_id=process.id JOIN client_master ON client_master.id=process.client_id where campaign.id=campaign_id; SET @campaign_id_var=campaign_id; ELSE IF (did IS NULL OR did='') AND (acd iS NULL OR acd='') THEN SELECT client_id,campaign.process_id,campaign.id into @client_id_var,@process_id_var,@campaign_id_var from campaign JOIN process on campaign.process_id=process.id JOIN client_master ON client_master.id=process.client_id where campaign.id=@current_campaign_var LIMIT 0,1; ELSE if (@current_campaign_var IS NOT NULL AND @current_campaign_var!='' AND @current_campaign_var>0) then SELECT client_id,campaign.process_id,campaign.id into @client_id_var,@process_id_var,@campaign_id_var from campaign JOIN process on campaign.process_id=process.id where campaign.id=@current_campaign_var LIMIT 0,1; ELSE SET @client_id_var=NULL; SET @process_id_var=NULL; SET @campaign_id_var=NULL; SELECT client_id,campaign.process_id,campaign.id into @client_id_var,@process_id_var,@campaign_id_var from campaign JOIN process on campaign.process_id=process.id WHERE (( IFNULL(did,'')!='' AND did_no=did AND ifnull(did_no,'')!='' ) and (IFNULL(acd,'')!='' AND san_acd_code=acd )) OR ((ifnull(did_no,'')='' OR IFNULL(did,'')='' )and IFNULL(acd,'')!='' and (san_acd_code=acd )) OR (IFNULL(acd,'')='' AND IFNULL(did,'')!='' and did_no=did) LIMIT 0,1; END IF; END IF; END IF; SET @dynSQL=CONCAT('INSERT INTO ',Case when IfNULL(@client_id_var,"0")="0" then 'cdr' ELSE CONCAT('cdr_',IfNULL(@client_id_var,"0"))END ,'( `unique_id`, `linked_id`, `trunk_channel`, `exten`, `caller_id`, `call_type`, `did`, `start_time`, `acd_time`, `exten_ring_time`, `exten_time`, `exten_ans_time`, `end_time`, `acd`, `cause`, `agent_disconnect`, `out_call_type`, `dtmf`, `feedback`, `process_id`, `campaign_id`, `map_id`, `crm_id`, `master_id`, `userfield`, `hold_duration`, `agent`, `call_hangup_reason`, `duration`, `billsec`, `recording_file`,`end_call` , `connected`,`disposition`,`ivr_name` )VALUES(',case when unique_id IS NULL OR unique_id='' THEN "NULL" ELSE CONCAT("'",unique_id,"'") END ,',', case when linked_id IS NULL OR linked_id='' THEN "NULL" ELSE CONCAT("'",linked_id,"'") END ,',', case when trunk_channel IS NULL OR trunk_channel='' THEN "NULL" ELSE CONCAT("'",trunk_channel,"'") END ,',', case when exten IS NULL OR exten='' THEN "NULL" ELSE CONCAT("'",exten,"'") END ,',', case when caller_id IS NULL OR caller_id='' THEN "NULL" ELSE CONCAT("'",caller_id,"'") END ,',', case when call_type IS NULL OR call_type='' THEN "NULL" ELSE CONCAT("'",call_type,"'") END ,',', case when did IS NULL OR did='' THEN "NULL" ELSE CONCAT("'",did,"'") END ,',', case when start_time IS NULL OR start_time='' THEN "NULL" ELSE CONCAT("'",start_time,"'") END ,',', case when acd_time IS NULL OR acd_time='' THEN "NULL" ELSE CONCAT("'",acd_time,"'") END ,',', case when exten_ring_time IS NULL OR exten_ring_time='' THEN "NULL" ELSE CONCAT("'",exten_ring_time,"'") END ,',', case when exten_time IS NULL OR exten_time='' THEN "NULL" ELSE CONCAT("'",exten_time,"'") END ,',', case when exten_ans_time IS NULL OR exten_ans_time='' THEN "NULL" ELSE CONCAT("'",exten_ans_time,"'") END ,',', case when end_time IS NULL OR end_time='' THEN "NULL" ELSE CONCAT("'",end_time,"'") END ,',', case when acd IS NULL OR acd='' THEN "NULL" ELSE CONCAT("'",acd,"'") END ,',', case when cause IS NULL OR cause='' THEN "NULL" ELSE CONCAT("'",cause,"'") END ,',', case when agent_disconnect IS NULL OR agent_disconnect='' THEN "NULL" ELSE CONCAT("'",agent_disconnect,"'") END ,',', case when out_call_type IS NULL OR out_call_type='' THEN "NULL" ELSE CONCAT("'",out_call_type,"'") END ,',', case when dtmf IS NULL OR dtmf='' THEN "NULL" ELSE CONCAT("'",dtmf,"'") END ,',', case when feedback IS NULL OR feedback='' THEN "NULL" ELSE CONCAT("'",feedback,"'") END ,',', case when @process_id_var IS NULL OR @process_id_var='' THEN "NULL" ELSE CONCAT("'",@process_id_var,"'") END ,',', case when @campaign_id_var IS NULL OR @campaign_id_var='' THEN "NULL" ELSE CONCAT("'",@campaign_id_var,"'") END ,',', case when ifnull(mapid,0)>0 then CONCAT("'",mapid,"'") when @map_id_var IS NULL OR @map_id_var='' THEN "NULL" ELSE CONCAT("'",@map_id_var,"'") END ,',', case when @crm_id_var IS NULL OR @crm_id_var='' THEN "NULL" ELSE CONCAT("'",@crm_id_var,"'") END ,',', case when @master_id_var IS NULL OR @master_id_var='' THEN "NULL" ELSE CONCAT("'",@master_id_var,"'") END ,',', case when userfield IS NULL OR userfield='' THEN "NULL" ELSE CONCAT("'",userfield,"'") END ,',', case when hold_duration IS NULL OR hold_duration='' THEN "NULL" ELSE CONCAT("'",hold_duration,"'") END ,',', case when @agent_name_var IS NULL OR @agent_name_var='' THEN "NULL" ELSE CONCAT("'",@agent_name_var,"'") END ,',', case when call_hangup_reason IS NULL OR call_hangup_reason='' THEN "NULL" ELSE CONCAT("'",call_hangup_reason,"'") END ,',', case when duration IS NULL OR duration='' THEN "NULL" ELSE CONCAT("'",duration,"'") END ,',', case when billsec IS NULL OR billsec='' THEN "NULL" ELSE CONCAT("'",billsec,"'") END ,',', case when recording_file IS NULL OR recording_file='' THEN "NULL" ELSE CONCAT("'",recording_file,"'") END ,',', case when IFNULL(end_call,0)=0 THEN "0" ELSE CONCAT("'",end_call,"'") END ,',', @connected,',',case when disposition IS NULL OR disposition='' THEN "NULL" ELSE CONCAT("'",disposition,"'") END ,',', case when ivr_name IS NULL OR ivr_name='' THEN "NULL" ELSE CONCAT("'",ivr_name,"'") END,')'); PREPARE stmt1 FROM @dynSQL; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; SET @cdrid=LAST_INSERT_ID(); if IFNULL(out_call_type,"")="PD" AND IFNULL(@process_id_var,"")!="" AND exten_ans_time IS null AND ifnull(mapid,0)>0 AND ifnull(end_call,0)=1 then CALL `addSystemDisposeLog`(IFNULL(@process_id_var,"0") ,ifnull(mapid,0),linked_id,unique_id,ifnull(caller_id,""),ifnull(cause,0),IFNULL(call_hangup_reason,""),IFNULL(dtmf,""),IFNULL(duration,0)); END if; IF end_call=1 AND call_type='Incoming' AND IFNULL(@client_id_var,0)>0 AND IFNULL(@cdrid,0)>0 AND IFNULL(@connected,0)=0 THEN CALL `set_callback_on_missedcall`(@client_id_var, did,acd, @cdrid,caller_id,if(acd_time IS NULL,'IVR','QUEUE')); END IF; CREATE TABLE if NOT EXISTS `cdrtemp` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT,`unique_id` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',`linked_id` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',`trunk_channel` VARCHAR(200) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',`exten` VARCHAR(16) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',`caller_id` VARCHAR(16) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',`call_type` VARCHAR(16) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',`did` VARCHAR(16) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `start_time` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `acd_time` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `exten_ring_time` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `exten_time` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `exten_ans_time` DATETIME NULL DEFAULT NULL, `end_time` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `acd` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `cause` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `agent_disconnect` INT(1) NULL DEFAULT NULL, `out_call_type` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `dtmf` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `feedback` VARCHAR(5) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `process_id` INT(11) NULL DEFAULT NULL, `campaign_id` INT(11) NULL DEFAULT NULL, `map_id` INT(11) NULL DEFAULT NULL, `crm_id` BIGINT(20) NULL DEFAULT NULL, `master_id` INT(11) NULL DEFAULT NULL, `userfield` VARCHAR(150) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `hold_duration` INT(3) NULL DEFAULT NULL, `agent` VARCHAR(150) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `call_hangup_reason` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `duration` INT(11) NULL DEFAULT NULL, `billsec` INT(11) NULL DEFAULT NULL, `recording_file` VARCHAR(250) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `log` TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', `connected` INT(1) NULL DEFAULT '0', `end_call` INT(1) NOT NULL DEFAULT '0', `disposition` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',`cid` INT(11) NULL DEFAULT NULL, `ivr_name` VARCHAR(200) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci', PRIMARY KEY (`id`) USING BTREE) COLLATE='latin1_swedish_ci' ENGINE=InnoDB ; SET @cdrtempSQL=CONCAT('INSERT INTO cdrtemp (cid,`unique_id`,`linked_id`,`trunk_channel`,`exten`,`caller_id`,`call_type`,`did`,`start_time`,`acd_time`,`exten_ring_time`,`exten_time`,`exten_ans_time`,`end_time`,`acd`,`cause`,`agent_disconnect`,`out_call_type`,`dtmf`,`feedback`,`process_id`,`campaign_id`,`map_id`,`crm_id`,`master_id`,`userfield`,`hold_duration`,`agent`,`call_hangup_reason`,`duration`,`billsec`,`recording_file`,`end_call`,`connected`,`disposition`,`ivr_name`) VALUES(',Case when IfNULL(@client_id_var,"0")="0" then "0" ELSE @client_id_var END ,',',case when unique_id IS NULL OR unique_id='' THEN "NULL" ELSE CONCAT("'",unique_id,"'") END ,',', case when linked_id IS NULL OR linked_id='' THEN "NULL" ELSE CONCAT("'",linked_id,"'") END ,',', case when trunk_channel IS NULL OR trunk_channel='' THEN "NULL" ELSE CONCAT("'",trunk_channel,"'") END ,',', case when exten IS NULL OR exten='' THEN "NULL" ELSE CONCAT("'",exten,"'") END ,',', case when caller_id IS NULL OR caller_id='' THEN "NULL" ELSE CONCAT("'",caller_id,"'") END ,',', case when call_type IS NULL OR call_type='' THEN "NULL" ELSE CONCAT("'",call_type,"'") END ,',', case when did IS NULL OR did='' THEN "NULL" ELSE CONCAT("'",did,"'") END ,',', case when start_time IS NULL OR start_time='' THEN "NULL" ELSE CONCAT("'",start_time,"'") END ,',', case when acd_time IS NULL OR acd_time='' THEN "NULL" ELSE CONCAT("'",acd_time,"'") END ,',', case when exten_ring_time IS NULL OR exten_ring_time='' THEN "NULL" ELSE CONCAT("'",exten_ring_time,"'") END ,',', case when exten_time IS NULL OR exten_time='' THEN "NULL" ELSE CONCAT("'",exten_time,"'") END ,',', case when exten_ans_time IS NULL OR exten_ans_time='' THEN "NULL" ELSE CONCAT("'",exten_ans_time,"'") END ,',', case when end_time IS NULL OR end_time='' THEN "NULL" ELSE CONCAT("'",end_time,"'") END ,',', case when acd IS NULL OR acd='' THEN "NULL" ELSE CONCAT("'",acd,"'") END ,',', case when cause IS NULL OR cause='' THEN "NULL" ELSE CONCAT("'",cause,"'") END ,',', case when agent_disconnect IS NULL OR agent_disconnect='' THEN "NULL" ELSE CONCAT("'",agent_disconnect,"'") END ,',', case when out_call_type IS NULL OR out_call_type='' THEN "NULL" ELSE CONCAT("'",out_call_type,"'") END ,',', case when dtmf IS NULL OR dtmf='' THEN "NULL" ELSE CONCAT("'",dtmf,"'") END ,',', case when feedback IS NULL OR feedback='' THEN "NULL" ELSE CONCAT("'",feedback,"'") END ,',', case when @process_id_var IS NULL OR @process_id_var='' THEN "NULL" ELSE CONCAT("'",@process_id_var,"'") END ,',', case when @campaign_id_var IS NULL OR @campaign_id_var='' THEN "NULL" ELSE CONCAT("'",@campaign_id_var,"'") END ,',', case when ifnull(mapid,0)>0 then CONCAT("'",mapid,"'") when @map_id_var IS NULL OR @map_id_var='' THEN "NULL" ELSE CONCAT("'",@map_id_var,"'") END ,',', case when @crm_id_var IS NULL OR @crm_id_var='' THEN "NULL" ELSE CONCAT("'",@crm_id_var,"'") END ,',', case when @master_id_var IS NULL OR @master_id_var='' THEN "NULL" ELSE CONCAT("'",@master_id_var,"'") END ,',', case when userfield IS NULL OR userfield='' THEN "NULL" ELSE CONCAT("'",userfield,"'") END ,',', case when hold_duration IS NULL OR hold_duration='' THEN "NULL" ELSE CONCAT("'",hold_duration,"'") END ,',', case when @agent_name_var IS NULL OR @agent_name_var='' THEN "NULL" ELSE CONCAT("'",@agent_name_var,"'") END ,',', case when call_hangup_reason IS NULL OR call_hangup_reason='' THEN "NULL" ELSE CONCAT("'",call_hangup_reason,"'") END ,',', case when duration IS NULL OR duration='' THEN "NULL" ELSE CONCAT("'",duration,"'") END ,',', case when billsec IS NULL OR billsec='' THEN "NULL" ELSE CONCAT("'",billsec,"'") END ,',', case when recording_file IS NULL OR recording_file='' THEN "NULL" ELSE CONCAT("'",recording_file,"'") END ,',', case when IFNULL(end_call,0)=0 THEN "0" ELSE CONCAT("'",end_call,"'") END ,',', @connected,',',case when disposition IS NULL OR disposition='' THEN "NULL" ELSE CONCAT("'",disposition,"'") END ,',', case when ivr_name IS NULL OR ivr_name='' THEN "NULL" ELSE CONCAT("'",ivr_name,"'") END,')'); PREPARE stmtcdrtempSQL FROM @cdrtempSQL; EXECUTE stmtcdrtempSQL; DEALLOCATE PREPARE stmtcdrtempSQL; if IFNULL(@process_id_var,'')!='' AND IFNULL(feedback,'')!='' AND IFNULL(linked_id,'')!='' then SET @updatefeedback = CONCAT('update process_table_log_',@process_id_var,' set feedback="',feedback, '" where cdr_id="',linked_id,'"'); PREPARE stmtupdatefeedback FROM @updatefeedback; EXECUTE stmtupdatefeedback; DEALLOCATE PREPARE stmtupdatefeedback; END if; if ifnull(acd,0)>0 AND (exten_ans_time IS NULL) AND end_call=1 then SELECT missed_call_email_template,email_on_missed_call,process.id,campaign.id,client_id INTO @missed_call_email_template,@email_on_missed_call,@process_id_new,@campaign_id,@client_id FROM campaign join process ON campaign.process_id=process.id WHERE san_acd_code=acd; CALL addEmailData(@process_id_new,@campaign_id,@client_id,@agent_name_var,@missed_call_email_template,@email_on_missed_call); END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.addSystemDisposeLog DROP PROCEDURE IF EXISTS `addSystemDisposeLog`; DELIMITER // CREATE PROCEDURE `addSystemDisposeLog`( IN `pid` INT, IN `mapid` INT, IN `linked_id` VARCHAR(50), IN `uniqueid` VARCHAR(50), IN `phoneno` VARCHAR(50), IN `hangupcausecode` VARCHAR(10), IN `hangupcause` VARCHAR(50), IN `lastivrdigit` VARCHAR(50), IN `durn` INT ) BEGIN SELECT log_table_name INTO @Logtable FROM formbuilder WHERE process_id=pid AND log_table_name NOT LIKE '%grid%'; if ifnull(hangupcause,"")="" AND IFNULL(hangupcausecode,"")!="" then SET @hangupcause=(SELECT name FROM hangupclause WHERE CODE=hangupcausecode LIMIT 1); ELSE SET @hangupcause=hangupcause; end if; if IFNULL(@Logtable,'')!='' then SET @SystemDisposeLog=CONCAT('INSERT INTO ',@Logtable,' (parent_id,log_phone_no,campaign,camp_map_id,start_time,end_time,HangupCauseCode,HangupCause,CallType,disposition,LastIVRDigits,CallConnected,cdr_id,duration,unique_id) select cm.master_id,"',IFNULL(phoneno,''),'",cm.campaign_id,"',ifnull(mapid,0),'",NOW(),NOW(),"',IFNULL(hangupcausecode,0),'","',IFNULL(@hangupcause,""),'","System Dispose",NULL,"',IFNULL(lastivrdigit,''),'","0","',ifnull(linked_id,''),'","0","',IFNULL(uniqueid,''),'" from campaign_mapping_',pid,' cm where cm.map_id="',ifnull(mapid,0),'";'); PREPARE SystemDisposeLogstmt FROM @SystemDisposeLog; EXECUTE SystemDisposeLogstmt; DEALLOCATE PREPARE SystemDisposeLogstmt; SET @lastlogid=LAST_INSERT_ID(); SET @UpdateCampaignMapping=CONCAT('update campaign_mapping_',pid,' set LastLogId="',@lastlogid,'"/*',if(IFNULL(durn,0)=0,',dialed=0',''),'*/,disposition=NULL,sub_disposition=NULL,HangupCause=',IFNULL(hangupcausecode,0),' where map_id= "',ifnull(mapid,0),'";'); PREPARE UpdateCampaignMappingstmt FROM @UpdateCampaignMapping; EXECUTE UpdateCampaignMappingstmt; DEALLOCATE PREPARE UpdateCampaignMappingstmt; END if; END// DELIMITER ; -- Dumping structure for procedure version1.aftercallanswerupdate DROP PROCEDURE IF EXISTS `aftercallanswerupdate`; DELIMITER // CREATE PROCEDURE `aftercallanswerupdate`( IN `pid` INT, IN `masterid` INT, IN `mapid` INT, IN `agentid` INT, IN `extno` INT ) BEGIN INSERT INTO `aftercallanswerupdatelog` (`pid`,`masterid`,`mapid`,`agentid`,`extno`) VALUES(pid,masterid,mapid,agentid,extno); END// DELIMITER ; -- Dumping structure for procedure version1.aftersaveupdate DROP PROCEDURE IF EXISTS `aftersaveupdate`; DELIMITER // CREATE PROCEDURE `aftersaveupdate`( IN `processid` VARCHAR(15), IN `campaignid` VARCHAR(15), IN `logid` VARCHAR(15), IN `mapid` VARCHAR(15), IN `masterid` VARCHAR(15), IN `maindisposition` VARCHAR(100), IN `subdisposition` VARCHAR(100), IN `start_time` VARCHAR(30), IN `end_time` VARCHAR(30), IN `setcallbacktime` VARCHAR(30), IN `sqlcommand` TEXT, IN `proc_agent_id` INT ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; INSERT INTO aftersaveupdatelog(logid,mapid,masterid,disposition,subdisposition,start_time,end_time,setcallbacktime,processid,campaignid,sqlcommand,proc_agent_id) VALUES(logid,mapid,masterid,maindisposition,subdisposition,start_time,end_time,setcallbacktime,processid,campaignid,sqlcommand,proc_agent_id); SET @sms_count = 0; SELECT COUNT(1) INTO @sms_count FROM sms_schedule WHERE added_on>CURDATE() AND process_id=processid AND master_id=masterid AND ifnull(sms_sent,0) = 1; if(IFNULL(@sms_count,0) > 0) then UPDATE sms_schedule SET sms=0 WHERE added_on>CURDATE() AND process_id=processid AND process_id=processid AND master_id=masterid; END if; if ifnull(sqlcommand,"")!="" then SET @sqlcommand = replace(sqlcommand,'`','"'); PREPARE stmtsqlcommand FROM @sqlcommand; EXECUTE stmtsqlcommand; DEALLOCATE PREPARE stmtsqlcommand; END if; if ifnull(logid,"")!="" then SET @updatefeedback = CONCAT('update process_table_log_',processid,' pl left join cdr_1 cdr on cdr.linked_id=pl.cdr_id set pl.feedback=cdr.feedback where pl.id=',logid,' AND cdr.feedback is not null '); PREPARE stmtupdatefeedback FROM @updatefeedback; EXECUTE stmtupdatefeedback; DEALLOCATE PREPARE stmtupdatefeedback; END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.afteruploadupdate DROP PROCEDURE IF EXISTS `afteruploadupdate`; DELIMITER // CREATE PROCEDURE `afteruploadupdate`( IN `processid` VARCHAR(10), IN `campaignid` VARCHAR(10), IN `fileid` VARCHAR(10) ) BEGIN INSERT INTO afteruploadupdatelog(`insertdate`,`processid`,`campaignid`,`fileid`) VALUES(NOW(),processid,campaignid,fileid); if processid=4 then SET @SQL=CONCAT('UPDATE process_table_',processid,' JOIN campaign_mapping_',processid,' ON id=master_id SET parent_id=process_table_',processid,'.id WHERE fileid=',fileid); PREPARE stmt FROM @sql; execute stmt; DEALLOCATE PREPARE stmt; END if; END// DELIMITER ; -- Dumping structure for procedure version1.crm_data_purge DROP PROCEDURE IF EXISTS `crm_data_purge`; DELIMITER // CREATE PROCEDURE `crm_data_purge`( IN `type` VARCHAR(50), IN `process_id` INT, IN `fromdate` VARCHAR(25), IN `todate` VARCHAR(25), IN `cond` VARCHAR(500) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @ERRNO = MYSQL_ERRNO, @MESSAGE_TEXT = MESSAGE_TEXT; SELECT CONCAT('MySQL ERROR: ', @ERRNO, ': ', @MESSAGE_TEXT) AS Error_Message; ROLLBACK; END; START TRANSACTION; SET @clientid=(SELECT client_id FROM process WHERE id=process_id); if TYPE = 'cdr_report' then SET @SQL = CONCAT('DELETE FROM cdr_',@clientid,' WHERE start_time>"',fromdate,'" and start_time<"',todate,'" ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END if; if TYPE ='ams_report' then SET @SQL = CONCAT('DELETE FROM agent_login_log_',process_id,' WHERE start_time > "',fromdate,'" and start_time<"',todate,'" ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END if; if TYPE ='call_log_report' then SET @SQL = CONCAT('DELETE FROM process_table_log_',process_id,' WHERE start_time > "',fromdate,'" and start_time<"',todate,'" ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @SQL = CONCAT('DELETE FROM campaign_mapping_',process_id,' WHERE lastcalltime IS NOT NULL AND lastcalltime > "',fromdate,'" and lastcalltime < "',todate,'" AND ifnull(map_id,'''') NOT IN (SELECT ifnull(camp_map_id,'''') FROM process_table_log_',process_id,') ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @SQL = CONCAT('DELETE FROM process_table_sub_log_',process_id,' WHERE ifnull(crm_id,'''') NOT IN (SELECT ifnull(crm_id,'''') FROM process_table_log_',process_id,') ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END if; if TYPE ='master_data' then SET @SQL = CONCAT('DELETE FROM process_table_',process_id,' WHERE id NOT IN (SELECT master_id FROM campaign_mapping_',process_id,') ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END if; if TYPE ='extra_log' then SET @SQL = CONCAT('DELETE FROM after_disposed_',process_id,' WHERE log_date>"',fromdate,'" and log_date<"',todate,'" ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @SQL = CONCAT('DELETE FROM email_log_',process_id,' WHERE added_on>"',fromdate,'" and added_on<"',todate,'" ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @SQL = CONCAT('DELETE from sms_log_',process_id,' WHERE added_on> "',fromdate,'" and added_on<"',todate,'" ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @SQL = CONCAT('DELETE FROM updatecrmstatelog WHERE logdate>"',fromdate,'" logdate<"',todate,'" ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @SQL = CONCAT('DELETE FROM aftersaveupdatelog WHERE insert_date>"',fromdate,'" insert_date<"',todate,'" ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @SQL = CONCAT('DELETE FROM afteruploadupdatelog WHERE insertdate>"',fromdate,'" insertdate<"',todate,'" ',ifnull(cond,'')); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END if; COMMIT; END// DELIMITER ; -- Dumping structure for procedure version1.duplicate_record_inFile DROP PROCEDURE IF EXISTS `duplicate_record_inFile`; DELIMITER // CREATE PROCEDURE `duplicate_record_inFile`( IN `primary_field_name` VARCHAR(50), IN `tableName` VARCHAR(50), IN `temptableName` VARCHAR(50) ) BEGIN SET @r=1; SET @uniq=''; DROP TABLE IF EXISTS temptableName; SET @SQL = CONCAT('CREATE TABLE ',tableName,' SELECT @r:=if(@uniq!=',primary_field_name,',1,@r+1)rn, @uniq:=',primary_field_name, ' UniqueColValue ,',temptableName,'.* FROM ',temptableName,' ORDER BY ',primary_field_name); PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END// DELIMITER ; -- Dumping structure for procedure version1.getAcdWiseAgentLoginCount DROP PROCEDURE IF EXISTS `getAcdWiseAgentLoginCount`; DELIMITER // CREATE PROCEDURE `getAcdWiseAgentLoginCount`( IN `pid` VARCHAR(50), IN `team_leader_id` INT, IN `camp_id` VARCHAR(50) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET @query1 =CONCAT('SELECT upper(process.name) as process_name,campaign.end_date,campaign.dial_mode,campaign.name AS"CampaignName",campaign.id AS campaign_id,campaign.san_acd_code AS "Acd_code",count(1) AS "AgentLogin", sum(case when agent_status.name in(''idle'',''Manual Off'') then 1 else 0 end) AS "Idle", sum(case when agent_status.name in(''On Break'') then 1 else 0 end) AS"Break", sum(case when agent_status.name in(''Incoming'',''Outgoing'',''Manual'') then 1 else 0 end) AS"OnCall", sum(case when agent_status.name in(''Wrapup'') then 1 else 0 end) AS"Wrapup" FROM agent_current_states join agent on agent_current_states.agent_id=agent.id JOIN agent_login_campaign ON agent_current_states.agent_id=agent_login_campaign.agent_id JOIN campaign ON campaign.id=agent_login_campaign.Campaign_id LEFT JOIN process ON process.id = campaign.process_id LEFT JOIN agent_status ON agent_status.id=agent_current_states.status WHERE IFNULL(agent_current_states.status,0)not in(2,0) and campaign.Process_id in (',Case when ifnull(pid,'')!='' then pid ELSE '' END ,') ', case when IFNULL(team_leader_id,0)>0 then CONCAT(' AND agent.team_leader="',team_leader_id,'"') ELSE '' END , case when IFNULL(camp_id,'')!='' then CONCAT(' AND campaign.id in (',camp_id,')') ELSE '' END,' GROUP BY process.name,campaign.end_date,campaign.dial_mode,campaign.name,campaign.san_acd_code ORDER BY campaign.name,campaign.san_acd_code;'); PREPARE stmt3 FROM @query1; execute stmt3; DEALLOCATE PREPARE stmt3; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getAgentCurrentStatus DROP PROCEDURE IF EXISTS `getAgentCurrentStatus`; DELIMITER // CREATE PROCEDURE `getAgentCurrentStatus`() NO SQL BEGIN SELECT `agent`.*, `agentgroup`.`name` AS `group_name`, `agent_status`.`name` AS `status_name`, `agent_status`.`color_code`, `agentbreak`.`name` AS `break_name`, SEC_TO_TIME( TIMEDIFF(NOW(), status_change_tm)) AS duration FROM `agent` LEFT JOIN `agentgroup` ON `agent`.`group_id` = `agentgroup`.`id` LEFT JOIN `agent_status` ON `agent_status`.`id` = `agent`.`status` LEFT JOIN `agentbreak` ON `agentbreak`.`id` = `agent`.`break_id` WHERE `login_process` = '12' AND `agent`.`status` NOT IN(0, 2) AND `agent`.`status_change_tm` >= CURDATE() ORDER BY `agent`.`name`; END// DELIMITER ; -- Dumping structure for procedure version1.getAgentLoginLogoutSummary DROP PROCEDURE IF EXISTS `getAgentLoginLogoutSummary`; DELIMITER // CREATE PROCEDURE `getAgentLoginLogoutSummary`( IN `pid` VARCHAR(10), IN `search_string` LONGTEXT, IN `orderby_string` LONGTEXT, IN `page_count` INT, IN `row_count` INT ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET @columns = 'SELECT agent_login_log.agent_name AS `Agent Name`, agent_status.name AS `Status`, DATE_FORMAT(agent_login_log.start_time, "%d-%m-%Y %H:%i:%s") AS `Log Date` '; SET @table = CONCAT(' FROM agent_login_log_',pid,' agent_login_log'); SET @join = ' LEFT JOIN agent_status agent_status ON(agent_status.id = agent_login_log.status) '; IF search_string != '' THEN SET @where = (CONCAT(' WHERE agent_login_log.status IN(1, 2) AND ' , search_string)); ELSE SET @where = ' WHERE agent_login_log.status IN(1, 2)'; END IF; IF orderby_string != '' THEN SET @order = CONCAT(' ORDER BY ', orderby_string); ELSE SET @ORDER = ''; END IF; IF page_count >= 0 AND row_count > 0 THEN SET @limit = CONCAT(' LIMIT ', page_count, ', ', row_count); ELSEIF row_count > 0 THEN SET @limit = CONCAT(' LIMIT ', row_count); ELSE SET @limit = ' LIMIT 20'; END IF; SET @query_string = CONCAT(@columns, " ", @table, " ", @join, " ", @where, " ", @order, " ", @limit); PREPARE stmagent_login_log FROM @query_string; EXECUTE stmagent_login_log; DEALLOCATE PREPARE stmagent_login_log; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getAgentMissedData DROP PROCEDURE IF EXISTS `getAgentMissedData`; DELIMITER // CREATE PROCEDURE `getAgentMissedData`( IN `RowCount` VARCHAR(50), IN `pid` INT, IN `agentid` INT, IN `from_date` VARCHAR(50), IN `to_date` VARCHAR(50) ) BEGIN SET @clientid=(SELECT client_id FROM process WHERE id=pid); SET @extension_no = (SELECT extension_no FROM agent WHERE id = agentid); SET @camp_ids = (SELECT GROUP_CONCAT(campaign_id) FROM agent_login_campaign WHERE agent_id = agentid); if(IFNULL(RowCount,'')='') then SET @QUERY='select count(1) as cnt'; else SET @QUERY='SELECT * ' ; END if; set @query1=concat('FROM (select camp.id as Camp_Id,camp.name AS Campaign,cdr.caller_id phone_no, Case when acd_time IS not null and exten_ring_time IS null then "Queue" when exten_ring_time IS NOT NULL AND exten_ans_time IS NULL then "Agent Missed" else "IVR" end "CallEndAt", case when (ifnull(cdr.acd_time,cdr.start_time)>pl1.start_time OR pl1.start_time IS NULL) then "Not Dialed" ELSE "Dialed" END "DialingStatus",cdr.start_time "Call_Time",exten,camp.san_acd_code AS Acd from cdr_',@clientid,' cdr LEFT JOIN campaign camp ON camp.id = cdr.campaign_id LEFT JOIN (SELECT MAX(start_time)start_time,log_phone_no FROM process_table_log_',pid,' WHERE start_time>DATE_ADD(CURDATE(),INTERVAL -7 DAY) and start_time>="',CONCAT(from_date,' 00:00:00'),'" GROUP BY log_phone_no) pl1 ON right(cdr.caller_id,8)=right(pl1.log_phone_no,8) WHERE cdr.start_time>DATE_ADD(CURDATE(),INTERVAL -7 DAY) and cdr.start_time>="',CONCAT(from_date,' 00:00:00'),'" and cdr.start_time<="',CONCAT(to_date,' 23:59:59'),'" AND call_type="Incoming" ',if(IFNULL(@camp_ids,'')!='',CONCAT(' AND cdr.campaign_id in (',@camp_ids,')'),''),' AND cdr.end_call=1 AND cdr.exten_ans_time IS null)tab where DialingStatus="Not Dialed" and ((CallEndAt = "Agent Missed" and exten="',IFNULL(@extension_no,''),'") or exten is null) order by Call_Time desc'); SET @QUERY2=CONCAT(@QUERY,' ',@query1); #SELECT @QUERY2; PREPARE stmt FROM @QUERY2; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getAgentsLogData DROP PROCEDURE IF EXISTS `getAgentsLogData`; DELIMITER // CREATE PROCEDURE `getAgentsLogData`(IN `pid` VARCHAR(10), IN `report_type` TEXT, IN `search_string` LONGTEXT, IN `page_count` INT, IN `row_count` INT) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; IF report_type='log_date_wise' THEN SET @COLUMNS = 'SELECT '; ELSEIF report_type='date_wise_log' THEN SET @COLUMNS = 'SELECT '; ELSEIF report_type='extension_no_wise' THEN SET @COLUMNS = 'SELECT '; ELSEIF report_type='status_wise' THEN SET @COLUMNS = 'SELECT '; ELSEIF report_type='agent_wise' THEN SET @COLUMNS = 'SELECT '; ELSEIF report_type='break_wise' THEN SET @COLUMNS = 'SELECT '; ELSEIF report_type='agent_break_wise' THEN SET @COLUMNS = 'SELECT '; ELSEIF report_type='agent_status_wise' THEN SET @COLUMNS = 'SELECT '; END IF; SET @COLUMNS = CONCAT( @COLUMNS, ' agent_login_log.agent_name AS `Agent Name`, agent_login_log.extension_no AS `Ext. No.`, DATE_FORMAT(agent_login_log.start_time, "%d-%m-%Y %H:%i:%s") AS `Log Date`, process.name AS `Process`, campaign.name AS `Campaign`, agent_status.name AS `Status`, agent_login_log.caller_id AS `Caller ID`, agent_login_log.phone_no AS `Called No`, agentbreak.name AS `Break`, agent_login_log.break_minutes AS `Break Time`, SEC_TO_TIME(agent_login_log.duration) AS `Duration` '); SET @table = CONCAT(' FROM agent_login_log_',pid,' agent_login_log'); SET @join = ' LEFT JOIN agent_status ON(agent_status.id = agent_login_log.status) LEFT JOIN agentbreak ON(agentbreak.id = agent_login_log.break_id) LEFT JOIN process ON(process.id = agent_login_log.process_id) LEFT JOIN campaign ON(campaign.id = agent_login_log.campaign_id)'; IF report_type='log_date_wise' THEN SET @WHERE = CONCAT(' WHERE 1=1 AND ',CASE WHEN IFNULL(search_string,"")!="" THEN search_string ELSE "" END , ' ORDER BY DATE_FORMAT(agent_login_log.start_time, "%d-%m-%Y %H:%i:%s")'); ELSEIF report_type='date_wise_log' THEN SET @WHERE = CONCAT(' WHERE 1=1 AND ',CASE WHEN IFNULL(search_string,"")!="" THEN search_string ELSE "" END , ' ORDER BY DATE_FORMAT(agent_login_log.start_time, "%d-%m-%Y %H:%i:%s")'); ELSEIF report_type='extension_no_wise' THEN SET @WHERE = CONCAT(' WHERE 1=1 AND ',CASE WHEN IFNULL(search_string,"")!="" THEN search_string ELSE "" END , ' ORDER BY DATE_FORMAT(agent_login_log.start_time, "%d-%m-%Y %H:%i:%s"), agent_login_log.extension_no'); ELSEIF report_type='status_wise' THEN SET @WHERE = CONCAT(' WHERE 1=1 AND ',CASE WHEN IFNULL(search_string,"")!="" THEN search_string ELSE "" END , ' ORDER BY agent_status.name,DATE_FORMAT(agent_login_log.start_time, "%d-%m-%Y %H:%i:%s")'); ELSEIF report_type='agent_wise' THEN SET @WHERE = CONCAT(' WHERE 1=1 AND ',CASE WHEN IFNULL(search_string,"")!="" THEN search_string ELSE "" END , ' ORDER BY agent_login_log.agent_name,DATE_FORMAT(agent_login_log.start_time, "%d-%m-%Y %H:%i:%s")'); ELSEIF report_type='break_wise' THEN SET @WHERE = CONCAT(' WHERE 1=1 AND agent_login_log.`status`=4 and IFNULL(agentbreak.name,"")!="" AND ',CASE WHEN IFNULL(search_string,"")!="" THEN search_string ELSE "" END , ' ORDER BY agentbreak.name,DATE_FORMAT(agent_login_log.start_time, "%d-%m-%Y %H:%i:%s")'); ELSEIF report_type='agent_break_wise' THEN SET @WHERE = CONCAT(' WHERE 1=1 AND agent_login_log.`status`=4 and IFNULL(agentbreak.name,"")!="" AND ',CASE WHEN IFNULL(search_string,"")!="" THEN search_string ELSE "" END , ' ORDER BY agent_login_log.agent_name,DATE_FORMAT(agent_login_log.start_time, "%d-%m-%Y %H:%i:%s")'); ELSEIF report_type='agent_status_wise' THEN SET @WHERE = CONCAT(' WHERE 1=1 AND ',CASE WHEN IFNULL(search_string,"")!="" THEN search_string ELSE "" END , ' ORDER BY agent_login_log.agent_name, agent_login_log.status,DATE_FORMAT(agent_login_log.start_time, "%d-%m-%Y %H:%i:%s")'); END IF; IF page_count >= 0 AND row_count > 0 THEN SET @limit = CONCAT(' LIMIT ', page_count, ', ', row_count); ELSEIF row_count > 0 THEN SET @limit = CONCAT(' LIMIT ', row_count); ELSE SET @limit = ' LIMIT 20'; END IF; SET @query_string = CONCAT(@COLUMNS, " ", @table, " ", @join, " ", @WHERE, " ", " ", @limit); PREPARE stmt_agent_login_log FROM @query_string; EXECUTE stmt_agent_login_log; DEALLOCATE PREPARE stmt_agent_login_log; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getAMSAgentSummary DROP PROCEDURE IF EXISTS `getAMSAgentSummary`; DELIMITER // CREATE PROCEDURE `getAMSAgentSummary`( IN `pid` VARCHAR(10), IN `search_string` LONGTEXT, IN `get_total` TINYINT, IN `get_breakwise` TINYINT, IN `file_path` VARCHAR(500) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET SESSION group_concat_max_len = 1000000; SELECT client_id INTO @client_id FROM process WHERE id=pid LIMIT 1; SET @droptable1 = CONCAT('drop table if exists tempams_',replace(CURDATE(),'-','')); PREPARE stmt_droptable1 FROM @droptable1; EXECUTE stmt_droptable1; DEALLOCATE PREPARE stmt_droptable1; IF get_breakwise>0 THEN SET @Break =CONCAT(',',(SELECT group_concat(CONCAT ('sec_to_time(sum(case when agentbreak.id="',id,'" then TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END ) ) As "',NAME,'"')) FROM agentbreak WHERE client_id=@client_id)); ELSE SET @Break=''; END IF; IF get_total > 0 THEN SET @columns = 'SELECT "Total" AS `Agent Name`'; ELSE SET @columns = 'SELECT agent_login_log.agent_name AS `Agent Name`'; END IF; if IFNULL(file_path,'')!='' then SET @COLUMNS1 = 'SELECT "Total" AS `Agent Name`'; END if; SET @columns = CONCAT(@columns, ',SUM(CASE WHEN agent_login_log.status IN (6,23,13) THEN 1 ELSE 0 END) Offered, SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END) Ansd,SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END) Abnd, SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time THEN 1 ELSE 0 END) Outgoing_Call, SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected=1*/ THEN 1 ELSE 0 END) Con_Outgoing_Call, SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN 1 ELSE 0 END) Break1, SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN 1 ELSE 0 END) Break2'); if IFNULL(file_path,'')!='' then SET @COLUMNS1 = CONCAT(@COLUMNS1, ',SUM(CASE WHEN agent_login_log.status IN (6,23,13) THEN 1 ELSE 0 END) Offered, SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END) Ansd,SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END) Abnd, SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time THEN 1 ELSE 0 END) Outgoing_Call, SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected=1*/ THEN 1 ELSE 0 END) Con_Outgoing_Call, SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN 1 ELSE 0 END) Break1, SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN 1 ELSE 0 END) Break2'); END if; SET @columns = CONCAT(@COLUMNS ,@Break); if IFNULL(file_path,'')!='' then SET @COLUMNS1 = CONCAT(@COLUMNS1 ,@Break); END if; SET @columns = CONCAT(@columns, ', SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END) Hold ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Ring_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="0"*/ THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Dialing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in(3,10) THEN TIMESTAMPDIFF(SECOND,start_time, end_time) ELSE 0 END)) Idle_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Incoming_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Outgoing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Hold_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break1_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break2_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Wrapup_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Abndn_Time ,SEC_TO_TIME((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END) + SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))) Total_Talk ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (3,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Staffed ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/NULLIF((SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0),0)) Avg_Talk ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/NULLIF(SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END),0),0),0)) Avg_Abnd ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/NULLIF(SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END),0),0),0)) Avg_Hold ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/NULLIF(SUM(CASE WHEN agent_login_log.status=5 THEN 1 ELSE 0 END),0),0),0)) Avg_Wrapup ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/NULLIF(SUM(CASE WHEN agent_login_log.status IN (6,23,13) THEN 1 ELSE 0 END),0),0),0)) ASA ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/NULLIF((SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0),0)) AHT ,Round((SUM(CASE WHEN agent_login_log.status IN (5,6,23,7,8) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)*100)/NULLIF(SUM(CASE WHEN agent_login_log.status IN (3,4,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END),0),2) Utilization_Per ' ,Case when get_total = 0 then ',TIME(MIN(CASE WHEN agent_login_log.status=1 THEN start_time END)) Login_Time' ELSE ',"" Login_Time' end ,Case when get_total = 0 then ',TIME(MAX(start_time)) Logout_Time' ELSE ' ,"" Logout_Time' end ,Case when get_total = 0 then ',SEC_TO_TIME(TIMESTAMPDIFF(SECOND,MIN(CASE WHEN agent_login_log.status=1 THEN start_time END),MAX(start_time))) Login_Hrs' ELSE ' ,"" Login_Hrs' end ,',SUM(CASE WHEN agent_login_log.status=1 THEN 1 ELSE 0 END) Login_Count, SUM(CASE WHEN agent_login_log.status=2 THEN 1 ELSE 0 END) Logout_Count'); if IFNULL(file_path,'')!='' then SET @COLUMNS1 = CONCAT(@COLUMNS1, ', SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END) Hold ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Ring_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="0"*/ THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Dialing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in(3,10) THEN TIMESTAMPDIFF(SECOND,start_time, end_time) ELSE 0 END)) Idle_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Incoming_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Outgoing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Hold_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break1_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break2_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Wrapup_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Abndn_Time ,SEC_TO_TIME((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END) + SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))) Total_Talk ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (3,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Staffed ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/NULLIF((SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0),0)) Avg_Talk ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/NULLIF(SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END),0),0),0)) Avg_Abnd ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/NULLIF(SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END),0),0),0)) Avg_Hold ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/NULLIF(SUM(CASE WHEN agent_login_log.status=5 THEN 1 ELSE 0 END),0),0),0)) Avg_Wrapup ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/NULLIF(SUM(CASE WHEN agent_login_log.status IN (6,23,13) THEN 1 ELSE 0 END),0),0),0)) ASA ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/NULLIF((SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0),0)) AHT ,Round((SUM(CASE WHEN agent_login_log.status IN (5,6,23,7,8) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)*100)/NULLIF(SUM(CASE WHEN agent_login_log.status IN (3,4,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END),0),2) Utilization_Per ,"" Login_Time ,"" Logout_Time ,"" Login_Hrs ,SUM(CASE WHEN agent_login_log.status=1 THEN 1 ELSE 0 END) Login_Count, SUM(CASE WHEN agent_login_log.status=2 THEN 1 ELSE 0 END) Logout_Count'); END if; SET @table = CONCAT(' FROM agent_login_log_',pid,' agent_login_log LEFT JOIN agent_status ON agent_status.id = agent_login_log.status LEFT JOIN agentbreak ON agentbreak.id = agent_login_log.break_id LEFT JOIN agent ON agent.name = agent_login_log.agent_name '); SET @where = ' WHERE agent_login_log.agent_name IS NOT NULL AND agent_login_log.agent_name != "" '; IF search_string != '' THEN SET @where = CONCAT(@where, 'AND ' , search_string); END IF; IF get_total > 0 THEN SET @group = ''; SET @order = ''; ELSE SET @group = ' GROUP BY agent_login_log.agent_name'; SET @order = ' ORDER BY agent_login_log.agent_name'; END IF; if IFNULL(file_path,'')!='' then SET @createtemptable = CONCAT('create table tempams_',replace(CURDATE(),'-',''),' as ',@columns, " ", @table, " ", @where, " ", @group, " ", @ORDER); PREPARE stmt_createtemptable FROM @createtemptable; EXECUTE stmt_createtemptable; DEALLOCATE PREPARE stmt_createtemptable; END if; if IFNULL(file_path,'')!='' then SET @columntemptable = CONCAT('select group_concat(concat(''"'',column_name,''"'')) into @tablecolumnname from information_schema.columns where table_name="tempams_',replace(CURDATE(),'-',''),'" order by ordinal_position'); PREPARE stmt_columntemptable FROM @columntemptable; EXECUTE stmt_columntemptable; DEALLOCATE PREPARE stmt_columntemptable; END if; if IFNULL(file_path,'')='' then SET @query_string = CONCAT(@columns, " ", @table, " ", @where, " ", @group, " ", @order); else SET @query_string = CONCAT('select ',@tablecolumnname, ' UNION ALL (' ,@columns, " ", @table, " ", @where, " ", @GROUP,') UNION ALL (',@COLUMNS1, " ", @table, " ", @where, " ", @ORDER,' into OUTFILE ''',file_path,''' FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"'' LINES TERMINATED BY ''\n'');'); END if; PREPARE stmt_agent_login_log FROM @query_string; EXECUTE stmt_agent_login_log; DEALLOCATE PREPARE stmt_agent_login_log; SET @droptable = CONCAT('drop table if exists tempams_',replace(CURDATE(),'-','')); PREPARE stmt_droptable FROM @droptable; EXECUTE stmt_droptable; DEALLOCATE PREPARE stmt_droptable; if IFNULL(file_path,'')!='' then SELECT file_path; END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getAMSAgentSummaryCampaignWise DROP PROCEDURE IF EXISTS `getAMSAgentSummaryCampaignWise`; DELIMITER // CREATE PROCEDURE `getAMSAgentSummaryCampaignWise`( IN `pid` VARCHAR(10), IN `search_string` LONGTEXT, IN `get_total` TINYINT ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; IF get_total > 0 THEN SET @columns = 'SELECT "Total" AS `Agent Name`'; ELSE SET @columns = 'SELECT agent_login_log.campaign_id, campaign.name AS `Campaign Name`,agent_login_log.agent_name AS `Agent Name`'; END IF; SET @columns = CONCAT(@columns, ',SUM(CASE WHEN agent_login_log.status IN (6,13) THEN 1 ELSE 0 END) Offered, SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END) Ansd,SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END) Abnd, SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time THEN 1 ELSE 0 END) Outgoing_Call, SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected=1*/ THEN 1 ELSE 0 END) Con_Outgoing_Call, SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN 1 ELSE 0 END) Break1, SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN 1 ELSE 0 END) Break2 , SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END) Hold ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Ring_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="0"*/ THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Dialing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in(3,10) THEN TIMESTAMPDIFF(SECOND,start_time, end_time) ELSE 0 END)) Idle_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Incoming_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Outgoing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Hold_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break1_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break2_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Wrapup_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Abndn_Time ,SEC_TO_TIME((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END) + SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))) Total_Talk ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (3,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Staffed ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/(SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0)) Avg_Talk ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END),0),0)) Avg_Abnd ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END),0),0)) Avg_Hold ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/SUM(CASE WHEN agent_login_log.status=5 THEN 1 ELSE 0 END),0),0)) Avg_Wrapup ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/SUM(CASE WHEN agent_login_log.status IN (6,23,13) THEN 1 ELSE 0 END),0),0)) ASA ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/(SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0)) AHT ,Round((SUM(CASE WHEN agent_login_log.status IN (5,6,23,7,8) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)*100)/SUM(CASE WHEN agent_login_log.status IN (3,4,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END),2) Utilization_Per ' ,Case when get_total = 0 then ',TIME(MIN(CASE WHEN agent_login_log.status=1 THEN start_time END)) Login_Time' ELSE ',"" Login_Time' end ,Case when get_total = 0 then ',TIME(MAX(start_time)) Logout_Time' ELSE ' ,"" Logout_Time' end ,Case when get_total = 0 then ',SEC_TO_TIME(TIMESTAMPDIFF(SECOND,MIN(CASE WHEN agent_login_log.status=1 THEN start_time END),MAX(start_time))) Login_Hrs' ELSE ' ,"" Login_Hrs' end ,',SUM(CASE WHEN agent_login_log.status=1 THEN 1 ELSE 0 END) Login_Count, SUM(CASE WHEN agent_login_log.status=2 THEN 1 ELSE 0 END) Logout_Count'); SET @table = CONCAT(' FROM agent_login_log_',pid,' agent_login_log LEFT JOIN campaign ON campaign.id=agent_login_log.campaign_id LEFT JOIN agent_status ON agent_status.id = agent_login_log.status LEFT JOIN agentbreak ON agentbreak.id = agent_login_log.break_id LEFT JOIN agent ON agent.name = agent_login_log.agent_name '); SET @where = ' WHERE agent_login_log.agent_name IS NOT NULL AND agent_login_log.agent_name != "" '; IF search_string != '' THEN SET @where = CONCAT(@where, 'AND ' , search_string); END IF; IF get_total > 0 THEN SET @group = ' GROUP BY agent_login_log.campaign_id'; SET @order = ' ORDER BY agent_login_log.campaign_id'; ELSE SET @group = ' GROUP BY agent_login_log.campaign_id,agent_login_log.agent_name'; SET @order = ' ORDER BY agent_login_log.campaign_id,agent_login_log.agent_name'; END IF; SET @query_string = CONCAT(@columns, " ", @table, " ", @where, " ", @group, " ", @order); PREPARE stmt_agent_login_log FROM @query_string; EXECUTE stmt_agent_login_log; DEALLOCATE PREPARE stmt_agent_login_log; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getAMSAgentSummaryDateWise DROP PROCEDURE IF EXISTS `getAMSAgentSummaryDateWise`; DELIMITER // CREATE PROCEDURE `getAMSAgentSummaryDateWise`( IN `pid` VARCHAR(10), IN `search_string` LONGTEXT, IN `get_total` TINYINT, IN `file_path` VARCHAR(500) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET SESSION group_concat_max_len = 1000000; SELECT client_id INTO @client_id FROM process WHERE id=pid LIMIT 1; SET @droptable1 = CONCAT('drop table if exists tempams_',replace(CURDATE(),'-','')); PREPARE stmt_droptable1 FROM @droptable1; EXECUTE stmt_droptable1; DEALLOCATE PREPARE stmt_droptable1; IF get_total > 0 and file_path='' THEN SET @columns = 'SELECT "Total" AS `Agent Name`'; END IF; IF get_total = 0 and file_path='' THEN SET @columns= 'SELECT agent_login_log.agent_name AS `Agent Name`,DATE_FORMAT(start_time, "%d-%m-%Y") AS `Date`'; END IF; IF get_total > 0 and file_path!='' THEN SET @columns = 'SELECT "Total" AS `Agent Name`,"Total" as `Date` '; END if; IF get_total = 0 and file_path!='' THEN SET @columns= 'SELECT agent_login_log.agent_name AS `Agent Name`,DATE_FORMAT(start_time, "%d-%m-%Y") AS `Date`'; END IF; if IFNULL(file_path,'')!='' then SET @COLUMNS1 = 'SELECT "Total" AS `Agent Name`,"Total" as `Date` '; else SET @COLUMNS1='SELECT "Total" AS `Agent Name`'; END if; SET @columns = CONCAT(@columns, ',SUM(CASE WHEN agent_login_log.status IN (6,13) THEN 1 ELSE 0 END) Offered, SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END) Ansd,SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END) Abnd, SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time THEN 1 ELSE 0 END) Outgoing_Call, SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected=1*/ THEN 1 ELSE 0 END) Con_Outgoing_Call, SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN 1 ELSE 0 END) Break1, SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN 1 ELSE 0 END) Break2 , SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END) Hold ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Ring_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="0"*/ THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Dialing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in(3,10) THEN TIMESTAMPDIFF(SECOND,start_time, end_time) ELSE 0 END)) Idle_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Incoming_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Outgoing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Hold_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break1_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break2_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Wrapup_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Abndn_Time ,SEC_TO_TIME((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END) + SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))) Total_Talk ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (3,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Staffed ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/nullif((SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0),0)) Avg_Talk ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/nullif(SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END),0),0),0)) Avg_Abnd ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/nullif(SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END),0),0),0)) Avg_Hold ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/nullif(SUM(CASE WHEN agent_login_log.status=5 THEN 1 ELSE 0 END),0),0),0)) Avg_Wrapup ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/nullif(SUM(CASE WHEN agent_login_log.status IN (6,23,13) THEN 1 ELSE 0 END),0),0),0)) ASA ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/nullif((SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0),0)) AHT ,Round((SUM(CASE WHEN agent_login_log.status IN (5,6,23,7,8) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)*100)/nullif(SUM(CASE WHEN agent_login_log.status IN (3,4,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END),0),2) Utilization_Per ' ,Case when get_total = 0 then ',TIME(MIN(CASE WHEN agent_login_log.status=1 THEN start_time END)) Login_Time' ELSE ',"" Login_Time' end ,Case when get_total = 0 then ',TIME(MAX(start_time)) Logout_Time' ELSE ' ,"" Logout_Time' end ,Case when get_total = 0 then ',SEC_TO_TIME(TIMESTAMPDIFF(SECOND,MIN(CASE WHEN agent_login_log.status=1 THEN start_time END),MAX(start_time))) Login_Hrs' ELSE ' ,"" Login_Hrs' end ,',SUM(CASE WHEN agent_login_log.status=1 THEN 1 ELSE 0 END) Login_Count, SUM(CASE WHEN agent_login_log.status=2 THEN 1 ELSE 0 END) Logout_Count'); if IFNULL(file_path,'')!='' then SET @COLUMNS1 = CONCAT(@COLUMNS1, ',SUM(CASE WHEN agent_login_log.status IN (6,13) THEN 1 ELSE 0 END) Offered, SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END) Ansd,SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END) Abnd, SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time THEN 1 ELSE 0 END) Outgoing_Call, SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected=1*/ THEN 1 ELSE 0 END) Con_Outgoing_Call, SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN 1 ELSE 0 END) Break1, SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN 1 ELSE 0 END) Break2 , SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END) Hold ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Ring_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="0"*/ THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Dialing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in(3,10) THEN TIMESTAMPDIFF(SECOND,start_time, end_time) ELSE 0 END)) Idle_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Incoming_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Outgoing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Hold_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break1_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break2_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Wrapup_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Abndn_Time ,SEC_TO_TIME((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END) + SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))) Total_Talk ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (3,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Staffed ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/nullif((SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0),0)) Avg_Talk ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/nullif(SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END),0),0),0)) Avg_Abnd ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/nullif(SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END),0),0),0)) Avg_Hold ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/nullif(SUM(CASE WHEN agent_login_log.status=5 THEN 1 ELSE 0 END),0),0),0)) Avg_Wrapup ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/nullif(SUM(CASE WHEN agent_login_log.status IN (6,23,13) THEN 1 ELSE 0 END),0),0),0)) ASA ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/nullif((SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0),0)) AHT ,Round((SUM(CASE WHEN agent_login_log.status IN (5,6,23,7,8) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)*100)/nullif(SUM(CASE WHEN agent_login_log.status IN (3,4,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END),0),2) Utilization_Per ,"" Login_Time ,"" Logout_Time ,"" Login_Hrs ,SUM(CASE WHEN agent_login_log.status=1 THEN 1 ELSE 0 END) Login_Count, SUM(CASE WHEN agent_login_log.status=2 THEN 1 ELSE 0 END) Logout_Count'); END if; SET @table = CONCAT(' FROM agent_login_log_',pid,' agent_login_log LEFT JOIN agent_status ON agent_status.id = agent_login_log.status LEFT JOIN agentbreak ON agentbreak.id = agent_login_log.break_id LEFT JOIN agent ON agent.name = agent_login_log.agent_name '); SET @where = ' WHERE agent_login_log.agent_name IS NOT NULL AND agent_login_log.agent_name != "" '; IF search_string != '' THEN SET @where = CONCAT(@where, 'AND ' , search_string); END IF; IF get_total > 0 THEN SET @GROUP = ''; SET @order = ''; ELSE SET @group = ' GROUP BY agent_login_log.agent_name,DATE_FORMAT(start_time, "%d-%m-%Y") '; SET @order = ' ORDER BY agent_login_log.agent_name,DATE_FORMAT(start_time, "%d-%m-%Y") '; END IF; if IFNULL(file_path,'')!='' then SET @createtemptable = CONCAT('create table tempams_',replace(CURDATE(),'-',''),' as ',@columns, " ", @table, " ", @where, " ", @group, " ", @ORDER); PREPARE stmt_createtemptable FROM @createtemptable; EXECUTE stmt_createtemptable; DEALLOCATE PREPARE stmt_createtemptable; END if; if IFNULL(file_path,'')!='' then SET @columntemptable = CONCAT('select group_concat(concat(''"'',column_name,''"'')) into @tablecolumnname from information_schema.columns where table_name="tempams_',replace(CURDATE(),'-',''),'" order by ordinal_position'); PREPARE stmt_columntemptable FROM @columntemptable; EXECUTE stmt_columntemptable; DEALLOCATE PREPARE stmt_columntemptable; END if; if IFNULL(file_path,'')='' then SET @query_string = CONCAT(@columns, " ", @table, " ", @where, " ", @group, " ", @order); else SET @query_string = CONCAT('select ',@tablecolumnname, ' UNION ALL (' ,@columns, " ", @table, " ", @where, " ", @GROUP,') UNION ALL (',@COLUMNS1, " ", @table, " ", @where, " ", @ORDER,' into OUTFILE ''',file_path,''' FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"'' LINES TERMINATED BY ''\n'');'); END if; PREPARE stmt_agent_login_log FROM @query_string; EXECUTE stmt_agent_login_log; DEALLOCATE PREPARE stmt_agent_login_log; SET @droptable = CONCAT('drop table if exists tempams_',replace(CURDATE(),'-','')); PREPARE stmt_droptable FROM @droptable; EXECUTE stmt_droptable; DEALLOCATE PREPARE stmt_droptable; if IFNULL(file_path,'')!='' then SELECT file_path; END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getAmsAgentSummaryOnScreen DROP PROCEDURE IF EXISTS `getAmsAgentSummaryOnScreen`; DELIMITER // CREATE PROCEDURE `getAmsAgentSummaryOnScreen`( IN `pid` INT, IN `agentid` VARCHAR(50) ) BEGIN SET @CURRENT_DATE=CURRENT_DATE(); SELECT STATUS,agent_current_states.status_change_tm INTO @currentstatus,@laststatetime FROM agent_current_states WHERE agent_id=agentid; if ifnull(pid,0)>0 then SET @amsagentsummaryonscreen=CONCAT(' SELECT agent_login_log.agent_name, ifnull(SUM(CASE WHEN agent_login_log.`status` IN (6,23) THEN 1 ELSE 0 END),0) PD_Incoming_Ansd_Call, ifnull(SUM(CASE WHEN agent_login_log.`status` IN (7) and agent_login_log.start_time!=agent_login_log.end_time THEN 1 ELSE 0 END),0)Manual_Total_Outgoing_Call, ifnull(SUM(CASE WHEN agent_login_log.`status` IN (7) and agent_login_log.start_time!=agent_login_log.end_time and agent_login_log.call_connected="1" THEN 1 ELSE 0 END),0)Manual_Total_Con_Outgoing ,sec_to_time(ifnull(SUM(case when STATUS!=2 then TIMESTAMPDIFF(SECOND,start_time,end_time) END )+',if(@currentstatus not IN (0,2),TIMESTAMPDIFF(SECOND,@laststatetime ,NOW()) ,0),',0))Login_Time ,sec_to_time(ifnull(SUM(case when agent_login_log.`status`="5" then TIMESTAMPDIFF(second,start_time,ifnull(end_time,NOw()))ELSE 0 END )+',if(@currentstatus=5,TIMESTAMPDIFF(SECOND,@laststatetime ,NOW()) ,0),',0))Wrapup_Time ,sec_to_time(ifnull(SUM(case when agent_login_log.`status`="3" then TIMESTAMPDIFF(second,start_time,ifnull(end_time,NOw()))ELSE 0 END )+',if(@currentstatus =3,TIMESTAMPDIFF(SECOND,@laststatetime ,NOW()) ,0),',0))Idle_Time ,sec_to_time(ifnull(SUM(case when agent_login_log.`status`="4" then TIMESTAMPDIFF(second,start_time,ifnull(end_time,NOw()))ELSE 0 END )+',if(@currentstatus =4,TIMESTAMPDIFF(SECOND,@laststatetime ,NOW()) ,0),',0))Break_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (3,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, ifnull(end_time,NOw())) ELSE 0 END)) Staff_Time ,sec_to_time(ifnull(SUM(case when agent_login_log.`status` IN("6","23","7") then TIMESTAMPDIFF(second,start_time,ifnull(end_time,NOw()))ELSE 0 END )+',if(@currentstatus IN(6,23,7,8),TIMESTAMPDIFF(SECOND,@laststatetime ,NOW()) ,0),',0))Talk_Time FROM agent_login_log_',pid,' agent_login_log LEFT JOIN agentbreak ON agentbreak.id = agent_login_log.break_id WHERE start_time>="',@CURRENT_DATE,'" AND agent_id="',agentid,'";'); ELSE set @amsagentsummaryonscreen=' SELECT "" Agent_Name,"00:00:00" Login_Time,"00:00:00" Wrapup_Time, "00:00:00" Idle_Time,"00:00:00" Break_Time,"00:00:00" Talk_Time LIMIT 0;'; END if; PREPARE amsagentsummaryonscreensmt1 FROM @amsagentsummaryonscreen; EXECUTE amsagentsummaryonscreensmt1; DEALLOCATE PREPARE amsagentsummaryonscreensmt1; END// DELIMITER ; -- Dumping structure for procedure version1.getAMSAgentSummaryTimeWise DROP PROCEDURE IF EXISTS `getAMSAgentSummaryTimeWise`; DELIMITER // CREATE PROCEDURE `getAMSAgentSummaryTimeWise`( IN `pid` VARCHAR(10), IN `search_string` LONGTEXT, IN `get_total` INT ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; IF get_total > 0 THEN SET @columns = 'SELECT "Total" AS `Agent Name`'; ELSE SET @columns = 'SELECT agent_login_log.agent_name AS `Agent Name`,CONCAT(Case When LENGTH(HOUR(start_time))<2 Then "0" ELSE "" END,HOUR(start_time),"-",Case When LENGTH(HOUR(start_time)+1)<2 Then "0" ELSE "" END,HOUR(start_time)+1) AS `Time`'; END IF; SET @columns = CONCAT(@columns, ',SUM(CASE WHEN agent_login_log.status IN (6,23,13) THEN 1 ELSE 0 END) Offered, SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END) Ansd,SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END) Abnd, SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time THEN 1 ELSE 0 END) Outgoing_Call, SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected=1*/ THEN 1 ELSE 0 END) Con_Outgoing_Call, SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN 1 ELSE 0 END) Break1, SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN 1 ELSE 0 END) Break2 , SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END) Hold ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Ring_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="0"*/ THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Dialing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in(3,10) THEN TIMESTAMPDIFF(SECOND,start_time, end_time) ELSE 0 END)) Idle_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Incoming_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (7) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)) Outgoing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Hold_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break1_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Break2_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Wrapup_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Abndn_Time ,SEC_TO_TIME((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END) + SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))) Total_Talk ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (3,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, start_time, end_time) ELSE 0 END)) Staffed ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7,8) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/(SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0)) Avg_Talk ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END),0),0)) Avg_Abnd ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END),0),0)) Avg_Hold ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/SUM(CASE WHEN agent_login_log.status=5 THEN 1 ELSE 0 END),0),0)) Avg_Wrapup ,SEC_TO_TIME(Round(Ifnull(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)/SUM(CASE WHEN agent_login_log.status IN (6,23,13) THEN 1 ELSE 0 END),0),0)) ASA ,SEC_TO_TIME(Round(Ifnull((SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END))/(SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) /*and agent_login_log.call_connected="1"*/ THEN 1 ELSE 0 END)),0),0)) AHT ,Round((SUM(CASE WHEN agent_login_log.status IN (5,6,23,7,8) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END)*100)/SUM(CASE WHEN agent_login_log.status IN (3,4,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND,start_time,end_time) ELSE 0 END),2) Utilization_Per ' ,Case when get_total = 0 then ',TIME(MIN(CASE WHEN agent_login_log.status=1 THEN start_time END)) Login_Time' ELSE ',"" Login_Time' end ,Case when get_total = 0 then ',TIME(MAX(start_time)) Logout_Time' ELSE ' ,"" Logout_Time' end ,Case when get_total = 0 then ',SEC_TO_TIME(TIMESTAMPDIFF(SECOND,MIN(CASE WHEN agent_login_log.status=1 THEN start_time END),MAX(start_time))) Login_Hrs' ELSE ' ,"" Login_Hrs' end ,',SUM(CASE WHEN agent_login_log.status=1 THEN 1 ELSE 0 END) Login_Count, SUM(CASE WHEN agent_login_log.status=2 THEN 1 ELSE 0 END) Logout_Count'); SET @table = CONCAT(' FROM agent_login_log_',pid,' agent_login_log LEFT JOIN agent_status ON agent_status.id = agent_login_log.status LEFT JOIN agentbreak ON agentbreak.id = agent_login_log.break_id LEFT JOIN agent ON agent.name = agent_login_log.agent_name '); SET @where = ' WHERE agent_login_log.agent_name IS NOT NULL AND agent_login_log.agent_name != "" '; IF search_string != '' THEN SET @where = CONCAT(@where, 'AND ' , search_string); END IF; IF get_total > 0 THEN SET @group = ' GROUP BY agent_login_log.agent_name'; SET @order = ' ORDER BY agent_login_log.agent_name'; ELSE SET @GROUP = ' GROUP BY agent_login_log.agent_name,CONCAT(Case When LENGTH(HOUR(start_time))<2 Then "0" ELSE "" END,HOUR(start_time),"-",Case When LENGTH(HOUR(start_time)+1)<2 Then "0" ELSE "" END,HOUR(start_time)+1)'; SET @order = ' ORDER BY agent_login_log.agent_name,CONCAT(Case When LENGTH(HOUR(start_time))<2 Then "0" ELSE "" END,HOUR(start_time),"-",Case When LENGTH(HOUR(start_time)+1)<2 Then "0" ELSE "" END,HOUR(start_time)+1)'; END IF; SET @query_string = CONCAT(@columns, " ", @table, " ", @where, " ", @group, " ", @order); PREPARE stmt_agent_login_log FROM @query_string; EXECUTE stmt_agent_login_log; DEALLOCATE PREPARE stmt_agent_login_log; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getAMSAgentSummaryWithCDR DROP PROCEDURE IF EXISTS `getAMSAgentSummaryWithCDR`; DELIMITER // CREATE PROCEDURE `getAMSAgentSummaryWithCDR`( IN `pid` VARCHAR(10), IN `fromdate` DATETIME, IN `todate` DATETIME, IN `rowlimit` VARCHAR(50) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET @droptable1 = CONCAT('drop table if exists temp_test'); PREPARE stmt_droptable1 FROM @droptable1; EXECUTE stmt_droptable1; DEALLOCATE PREPARE stmt_droptable1; SET @sql1 = CONCAT('create table temp_test as SELECT agent_login_log.agent_name , SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN 1 ELSE 0 END) Break1, SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN 1 ELSE 0 END) Break2 , SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END) Hold ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND, agent_login_log.start_time, agent_login_log.end_time) ELSE 0 END)) Ring_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (8) and agent_login_log.start_time!=agent_login_log.end_time /*and agent_login_log.call_connected="0"*/ THEN TIMESTAMPDIFF(SECOND, agent_login_log.start_time, agent_login_log.end_time) ELSE 0 END)) Dialing_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status in(3,10) THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time, agent_login_log.end_time) ELSE 0 END)) Idle_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND, agent_login_log.start_time, agent_login_log.end_time) ELSE 0 END)) Hold_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND ifnull(staff_time,0)=0 THEN TIMESTAMPDIFF(SECOND, agent_login_log.start_time, agent_login_log.end_time) ELSE 0 END)) Break1_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, agent_login_log.start_time, agent_login_log.end_time) ELSE 0 END)) Break2_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND, agent_login_log.start_time, agent_login_log.end_time) ELSE 0 END)) Wrapup_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND, agent_login_log.start_time, agent_login_log.end_time) ELSE 0 END)) Abndn_Time ,SEC_TO_TIME(SUM(CASE WHEN agent_login_log.status IN (3,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND, agent_login_log.start_time, agent_login_log.end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=4 AND staff_time=1 THEN TIMESTAMPDIFF(SECOND, agent_login_log.start_time, agent_login_log.end_time) ELSE 0 END)) Staffed ,SEC_TO_TIME(Round(IFNULL(IFNULL((SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END)),0)/NULLIF(SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) THEN 1 ELSE 0 END),0),0),0)) Avg_Talk ,SEC_TO_TIME(ROUND(IFNULL(IFNULL((SUM(CASE WHEN agent_login_log.status=13 THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END)),0)/NULLIF(SUM(CASE WHEN agent_login_log.status=13 THEN 1 ELSE 0 END),0),0),0)) Avg_Abnd ,SEC_TO_TIME(Round(IFNULL(IFNULL((SUM(CASE WHEN agent_login_log.status=9 THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END)),0)/NULLIF(SUM(CASE WHEN agent_login_log.status=9 THEN 1 ELSE 0 END),0),0),0)) Avg_Hold ,SEC_TO_TIME(Round(IFNULL(IFNULL((SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END)),0)/NULLIF(SUM(CASE WHEN agent_login_log.status=5 THEN 1 ELSE 0 END),0),0),0)) Avg_Wrapup ,SEC_TO_TIME(Round(IFNULL(SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END)/NULLIF(SUM(CASE WHEN agent_login_log.status IN (6,23,13) THEN 1 ELSE 0 END),0),0),0)) ASA ,SEC_TO_TIME(Round(IFNULL((SUM(CASE WHEN agent_login_log.status=12 THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status=5 THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status in (6,23) THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END))/NULLIF((SUM(CASE WHEN agent_login_log.status in (6,23) THEN 1 ELSE 0 END)+SUM(CASE WHEN agent_login_log.status IN (7) THEN 1 ELSE 0 END)),0),0),0)) AHT ,Round((ifnull(SUM(CASE WHEN agent_login_log.status IN (5,6,23,7,8) THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END),0)*100)/NULLIF(SUM(CASE WHEN agent_login_log.status IN (3,4,5,6,23,7,8,9,10,12,13) THEN TIMESTAMPDIFF(SECOND,agent_login_log.start_time,agent_login_log.end_time) ELSE 0 END),0),2) Utilization_Per ,TIME(MIN(CASE WHEN agent_login_log.status=1 THEN agent_login_log.start_time END)) Login_Time ,TIME(MAX(agent_login_log.start_time)) Logout_Time ,SEC_TO_TIME(TIMESTAMPDIFF(SECOND,MIN(CASE WHEN agent_login_log.status=1 THEN agent_login_log.start_time END), MAX(agent_login_log.start_time))) Login_Hrs ,SUM(CASE WHEN agent_login_log.status=1 THEN 1 ELSE 0 END) Login_Count, SUM(CASE WHEN agent_login_log.status=2 THEN 1 ELSE 0 END) Logout_Count FROM agent_login_log_1 agent_login_log JOIN campaign ON campaign.id=agent_login_log.campaign_id LEFT JOIN agent_status ON agent_status.id = agent_login_log.status LEFT JOIN agentbreak ON agentbreak.id = agent_login_log.break_id LEFT JOIN agent ON agent.name = agent_login_log.agent_name WHERE agent_login_log.agent_name IS NOT NULL AND agent_login_log.agent_name != "" and agent_login_log.process_id="',pid,'" AND start_time >="',fromdate,'" AND start_time <="',todate,'" GROUP BY agent_login_log.agent_name ORDER BY agent_login_log.agent_name ; '); PREPARE stmtsql1 FROM @sql1; EXECUTE stmtsql1; DEALLOCATE PREPARE stmtsql1; SET @droptable1 = CONCAT('drop table if exists temp_test1'); PREPARE stmt_droptable1 FROM @droptable1; EXECUTE stmt_droptable1; DEALLOCATE PREPARE stmt_droptable1; SET @SQL2 = CONCAT('create table temp_test1 as select Agent, ifnull(Sum(Case when ((acd_time IS NOT NULL and ifnull(acd,"")!="") OR (ifnull(out_call_type,"")="PD" and acd_time IS NOT NULL and ifnull(acd,"")!="")) or (call_type="Incoming" and exten_ring_time is not null and exten_ans_time is not null and acd_time is null and campaign_id is not null) then 1 ELSE 0 END),0)Offered, ifnull(Sum(Case when ((acd_time IS NOT NULL and ifnull(acd,"")!="") OR (ifnull(out_call_type,"")="PD" and acd_time IS NOT NULL and ifnull(acd,"")!="") or (call_type="Incoming" and exten_ring_time is not null and exten_ans_time is not null and acd_time is null and campaign_id is not NULL)) AND exten_ans_time IS NOT NULL then 1 ELSE 0 END),0)Ansd, ifnull(Sum(Case when ((acd_time IS NOT NULL and ifnull(acd,"")!="") OR (ifnull(out_call_type,"")="PD" and acd_time IS NOT NULL and ifnull(acd,"")!="")) AND exten_ans_time IS NULL then 1 ELSE 0 END),0)Abnd, ifnull(Sum(Case when acd_time IS NULL AND ifnull(out_call_type,"")!="PD" AND IFNULL(call_type,"")="Outgoing" then 1 ELSE 0 END),0) Outgoing_Call, ifnull(Sum(Case when acd_time IS NULL AND ifnull(out_call_type,"")!="PD" AND IFNULL(call_type,"")="Outgoing" AND connected=1 then 1 ELSE 0 END),0)Con_Outgoing_Call, SEC_TO_TIME(SUM(Case When agent IS NOT NULL and exten_ans_time IS NOT NULL AND call_type="Incoming" AND connected=1 Then TIMESTAMPDIFF(SECOND,exten_ans_time,end_time) ELSE 0 END)) Incoming_Time, SEC_TO_TIME(SUM(Case when acd_time IS NULL AND call_type="Outgoing" AND connected=1 then TIMESTAMPDIFF(SECOND,exten_ans_time,end_time) ELSE 0 END )) Outgoing_Time, SEC_TO_TIME(SUM(case when exten_ring_time IS NOT NULL AND exten_ans_time IS NOT NULL AND exten_ring_time<=exten_ans_time THEN TIMESTAMPDIFF(second, exten_ring_time, exten_ans_time) when exten_ring_time IS NOT NULL AND exten_ans_time IS NULL THEN TIMESTAMPDIFF(second, exten_ring_time, end_time ) ELSE "00:00:00" end)) Ring_Durn, SEC_TO_TIME((SUM(Case When agent IS NOT NULL and exten_ans_time IS NOT NULL AND call_type="Incoming" AND connected=1 Then TIMESTAMPDIFF(SECOND,exten_ans_time,end_time) ELSE 0 END)+SUM(Case when acd_time IS NULL AND call_type="Outgoing" AND connected=1 then TIMESTAMPDIFF(SECOND,exten_ans_time,end_time) ELSE 0 END ))) Total_Talk from cdr_1 WHERE agent IS NOT NULL AND agent != "" and process_id="',pid,'" AND start_time >="',fromdate,'" AND start_time <="',todate,'" GROUP BY agent ORDER BY agent'); PREPARE stmtsql2 FROM @sql2; EXECUTE stmtsql2; DEALLOCATE PREPARE stmtsql2; set @query = concat('select agent_name,Offered,Ansd,Abnd,Outgoing_Call,Con_Outgoing_Call,Break1,Break2,Hold,Ring_Durn,Dialing_Time,Idle_Time,Incoming_Time,Outgoing_Time,Hold_Time,Break1_Time,Break2_Time,Wrapup_Time,Abndn_Time, Total_Talk,Staffed,Avg_Talk,Avg_Abnd,Avg_Hold,Avg_Wrapup,ASA,AHT,Utilization_Per,Login_Time,Logout_Time,Login_Hrs,Login_Count,Logout_Count FROM temp_test t1 join temp_test1 t2 on t1.agent_name=t2.agent'); If (ifnull(rowlimit,"")!="" OR ifnull(rowlimit,"")!=NULL) then SET @QUERY=CONCAT(@QUERY,' limit ',rowlimit,' ;'); ELSE SET @QUERY=CONCAT('select Count(1) cnt from ( ',@QUERY,') rowcount;'); END if; SELECT @sql1; SELECT @sql2; SELECT @QUERY; PREPARE stmt_agent_login_log FROM @query; EXECUTE stmt_agent_login_log; DEALLOCATE PREPARE stmt_agent_login_log; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getCallback DROP PROCEDURE IF EXISTS `getCallback`; DELIMITER // CREATE PROCEDURE `getCallback`( IN `pid` VARCHAR(50), IN `cid` VARCHAR(50), IN `agent_id` VARCHAR(100), IN `setdial` INT, IN `phone_no_field` VARCHAR(50), IN `callingorder` TINYTEXT, IN `CallbackfromAnyCampaign` INT, IN `AgentMapping` INT, IN `agentsetdnc` INT, IN `callbackpopuptime` INT ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET @Callingorder=NULL; SET @PhoneNo=NULL; SET @Mastertab=NULL; SET @AgentMapping=0; SET @CallbackfromAnyCampaign=0; SET @AgentMapping=0; SET @agentsetdnc=0; SET @Srno=0; SET @PhoneNo=case when IFNULL(phone_no_field,'')!='' then phone_no_field ELSE (SELECT formbuilder_field.field_name FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid and ifnull(formbuilder_field.primary_phone_no,0)=1 LIMIT 1) end; SET @Callingorder=case when ifnull(callingorder,'')!='' then callingorder ELSE (SELECT GROUP_CONCAT( CONCAT( formbuilder_field.field_name,' ',formbuilder_field.order_by) ) FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid) end; SET @CallbackfromAnyCampaign=CallbackfromAnyCampaign; SET @AgentMapping=case when (IFNULL(AgentMapping,0)>0) then AgentMapping ELSE (SELECT ifnull(cl_bc_agt_map,0) FROM process_setting WHERE process_id=pid LIMIT 1) end; SET @agentsetdnc=agentsetdnc; SET @callbackpopuptime=callbackpopuptime; SET @Mastertab=CONCAT('process_table_',pid); IF IFNULL(@PhoneNo,'')!='' THEN SET @query1 =CONCAT('SELECT replace(replace(T1.',@PhoneNo,',char(10),''''),char(13),'''') phone_no,T2.master_id,T2.CallAgainTime,T2.CallAgainNo,T2.CallAgainType,T2.campaign_id,T2.map_id,now() Start_Time into @phone_no,@master_id,@CallAgainTime,@CallAgainNo,@CallAgainType,@campaign_id,@map_id,@Start_Time FROM ',@Mastertab,' AS T1 JOIN campaign_mapping_',pid,' AS T2 on T1.id=T2.master_id JOIN importmaster_',pid,' AS T3 on T3.id=T2.FileId JOIN campaign on campaign.id=T2.campaign_id WHERE ',if(@callbackpopuptime>0,'1=1','1=2'),' AND (IFNULL(T3.active_file,0)=1 OR (IFNULL(T3.active_file,0)=0 AND IFNULL(T3.retain_callback,0)=1 AND T2.CallAgainTime is not null )) AND ( T3.valid_upto is null OR T3.valid_upto<=CallAgainTime) AND T3.mark_for_deletion=0 AND T2.dialed=0 AND IFNULL(T2.StopCalling,0)=0 AND IFNULL(T2.DoNotCall,0)=0 AND ifnull(T2.Callbackdial_on_Hold,0)=0 AND CallAgainTime0 OR (T2.Agent_id="',agent_id,'" AND Ifnull(T2.NoOfAttempts,0)=0))' ,if(@agentsetdnc=1,' AND ifnull(T1.isDNC,0)=0 ',"") ,if(@AgentMapping=1,CONCAT('AND (( agent_id IS NULL OR MapToAgent = 0 OR MapToAgent is NULL) OR (MapToAgent = 1 AND T2.Agent_id="',agent_id,'"))'),"") ,if(@CallbackfromAnyCampaign=1 , "" ,CONCAT(' AND T2.campaign_id IN(',cid,')' )),' ORDER BY T2.CallAgainTime asc LIMIT 1 ; '); ELSE SET @map_id=0; END IF; #SELECT @QUERY1; PREPARE stmt3 FROM @query1; execute stmt3; DEALLOCATE PREPARE stmt3; if IFNULL(@map_id,0)>0 and ifnull(setdial,0)>0 then SET @QUERY2=CONCAT('update campaign_mapping_',pid,' set Dialed=1,NoOfAttempts=ifnull(NoOfAttempts,0)+1 where map_id="',@map_id,'";'); PREPARE updatestmt FROM @QUERY2; execute updatestmt; DEALLOCATE PREPARE updatestmt; INSERT INTO check_duplicate_dial(map_id,agent_id,insertdate) VALUES(@map_id,agent_id,NOW()); END if; if IFNULL(@map_id,0)>0 then SELECT @phone_no phone_no,@master_id master_id,@CallAgainTime CallAgainTime,@CallAgainNo CallAgainNo,@CallAgainType CallAgainType,@campaign_id campaign_id,@map_id map_id,@Start_Time Start_Time; ELSE SELECT "phone_no","master_id","CallAgainTime","CallAgainNo","CallAgainType","campaign_id","map_id","Start_Time" LIMIT 0; END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getCallLog DROP PROCEDURE IF EXISTS `getCallLog`; DELIMITER // CREATE PROCEDURE `getCallLog`( IN `pid` VARCHAR(10), IN `wherecondition` LONGTEXT, IN `rowlimit` VARCHAR(20), IN `file_path` VARCHAR(200), IN `hide_phone_no` VARCHAR(1) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET SESSION group_concat_max_len = 1000000; SET @droptable1 = CONCAT('drop table if exists tempcalllog_',replace(CURDATE(),'-',''),',tempcalllogdata'); PREPARE stmt_droptable1 FROM @droptable1; EXECUTE stmt_droptable1; DEALLOCATE PREPARE stmt_droptable1; SET @MasterColumn=(SELECT GROUP_CONCAT(CONCAT(case when hide_phone_no=1 AND data_type='phone' then CONCAT('CONCAT("******",RIGHT(mast_tbl.',field_name,',4))',field_name) ELSE case when hide_phone_no=1 AND mask=1 then CONCAT('CONCAT("******",RIGHT(mast_tbl.',field_name,',4))',field_name) else CONCAT('mast_tbl.',field_name) end end)) FROM formbuilder_field JOIN formbuilder ON formbuilder_field.form_id=formbuilder.id WHERE process_id=pid AND create_log=0); SET @LogColumn=(SELECT GROUP_CONCAT(case when hide_phone_no=1 AND mask=1 then CONCAT('CONCAT("******",RIGHT(log_tbl.',field_name,',4))',field_name) else CONCAT('log_tbl.',field_name) END ) FROM formbuilder_field JOIN formbuilder ON formbuilder_field.form_id=formbuilder.id WHERE process_id=pid AND create_log=1); SET @AttachmentCol = (SELECT CONCAT('concat(',GROUP_CONCAT(CONCAT('IFNULL(',field_name,',"")') SEPARATOR ',",",'),')') FROM formbuilder_field JOIN formbuilder ON formbuilder_field.form_id=formbuilder.id WHERE process_id=pid AND field_type='attachment'); if IFNULL(rowlimit,'')='' AND IFNULL(file_path,'')='' then SET @selectquery='SELECT COUNT(1) no_of_rec '; ELSE SET @selectquery=CONCAT('SELECT log_tbl.id as log_id, log_tbl.campaign as camaign_id , log_tbl.parent_id as master_id,log_tbl.camp_map_id as map_id, San_transfer_to`TransferredTo`,SAN_transfer_no`TransferredFrom`, log_tbl.cdr_id,',if(hide_phone_no=1,'CONCAT("******",RIGHT(log_tbl.log_phone_no,4))','log_tbl.log_phone_no'),' "dialed_phone_no",calltype as CallType,call_type `SubCallType`,case when ifnull(log_tbl.CallConnected,0)=1 then "C" else "M" end Status, camp.name as campaign_name,log_tbl.extension_no ,log_tbl.DID_No,log_tbl.agent_name ,agent.employee_code,TIMEDIFF(end_time, start_time) as crm_duration,SEC_TO_TIME(log_tbl.wrapup_durn)`Wrapup_Durn`, /*(select SEC_TO_TIME(sum(case when bill_duration IS NULL THEN 0 else bill_duration end)) from process_table_sub_log_',pid,' where crm_id=log_tbl.crm_id and crm_id!=0) as*/ sub_log.call_duration, log_tbl.HangupCause,log_tbl.crm_id,log_tbl.start_time,log_tbl.end_time,log_tbl.callback_time as callback_time,cmpMap.CallAgainNo as callback_no,log_tbl.disposition, log_tbl.sub_disposition,cmpMap.NoOfAttempts,log_tbl.SAN_Transfer_To AS Transfer_To,log_tbl.SAN_Transfer_No AS Transfer_No,log_tbl.Latitude,log_tbl.Longitude',if(IFNULL(@AttachmentCol,'')='','',CONCAT(',',IFNULL(@AttachmentCol,''))),' Doc_Attached ,',IFNULL(@MasterColumn,''),CONCAT(',',IFNULL(@LogColumn,''))); END if; SET @fromquery=CONCAT(' FROM process_table_',pid,' mast_tbl join process_table_log_',pid,' log_tbl ON log_tbl.parent_id=mast_tbl.id LEFT JOIN agent ON agent.name=log_tbl.agent_name LEFT JOIN campaign camp ON camp.id=log_tbl.campaign LEFT JOIN campaign_mapping_',pid,' cmpMap ON cmpMap.master_id=mast_tbl.id and cmpMap.campaign_id=log_tbl.campaign and cmpMap.map_id=log_tbl.camp_map_id LEFT JOIN (select SEC_TO_TIME(sum(case when bill_duration IS NULL THEN 0 else bill_duration end))call_duration,crm_id,call_type from process_table_sub_log_',pid,' where crm_id!=0 group by crm_id)sub_log on sub_log.crm_id=log_tbl.crm_id '); SET @orderby=CONCAT('where 1=1 ',wherecondition,' ORDER BY start_time ASC ',case when ifnull(rowlimit,'')='' then '' ELSE CONCAT(' LIMIT ',rowlimit) END); SET @sql=CONCAT(@selectquery,' ',@fromquery,' ',@orderby); if IFNULL(file_path,'')!='' then SET @sql=CONCAT('create table tempcalllog_',replace(CURDATE(),'-',''),' as ',@selectquery,' ',@fromquery, ' where 1=2 ',';'); END if; PREPARE stmtsql FROM @sql; execute stmtsql; DEALLOCATE PREPARE stmtsql; if IFNULL(file_path,'')!='' then SET @columntemptable = CONCAT('select group_concat(concat(''"'',column_name,''"'')) into @tablecolumnname from information_schema.columns where table_name="tempcalllog_',replace(CURDATE(),'-',''),'" order by ordinal_position'); PREPARE stmt_columntemptable FROM @columntemptable; EXECUTE stmt_columntemptable; DEALLOCATE PREPARE stmt_columntemptable; SET @createdatatable=CONCAT('create table tempcalllogdata as ',@selectquery," ",@fromquery,' ',@orderby); PREPARE stmt_createdatatable FROM @createdatatable; EXECUTE stmt_createdatatable; DEALLOCATE PREPARE stmt_createdatatable; SET @columntemptablenew = CONCAT('select group_concat(concat(''IFNULL(REPLACE(REPLACE(REPLACE('',column_name,'',CHAR(10),''''''''),CHAR(13),''''''''),CHAR(34),''''''''),"")'',column_name)) into @tablecolumnnamenew from information_schema.columns where table_name="tempcalllogdata" order by ordinal_position'); PREPARE stmt_columntemptablenew FROM @columntemptablenew; EXECUTE stmt_columntemptablenew; DEALLOCATE PREPARE stmt_columntemptablenew; SET @query_string = CONCAT('select ',@tablecolumnname, ' UNION ALL (select ',@tablecolumnnamenew,' from tempcalllogdata order by start_time into OUTFILE ''',file_path,''' FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''\"'' ESCAPED BY '''' LINES TERMINATED BY ''\n'');'); PREPARE stmt_query_string FROM @query_string; EXECUTE stmt_query_string; DEALLOCATE PREPARE stmt_query_string; SET @droptable = CONCAT('drop table if exists tempcalllog_',replace(CURDATE(),'-',''),',tempcalllogdata'); PREPARE stmt_droptable FROM @droptable; EXECUTE stmt_droptable; DEALLOCATE PREPARE stmt_droptable; SELECT SUBSTRING(file_path,LOCATE('excelexports',file_path)) AS file_path; END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getDatabaseUtilization DROP PROCEDURE IF EXISTS `getDatabaseUtilization`; DELIMITER // CREATE PROCEDURE `getDatabaseUtilization`() BEGIN SELECT TABLE_NAME AS `Table`,Table_Rows, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() ORDER BY table_rows DESC,(DATA_LENGTH + INDEX_LENGTH) DESC LIMIT 10; SELECT table_schema Database_Name,SUM(table_rows)Total_Table_Rows,ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS `Size (MB)` FROM information_schema.tables WHERE table_schema=DATABASE() GROUP BY table_schema; END// DELIMITER ; -- Dumping structure for procedure version1.GetLogTrail DROP PROCEDURE IF EXISTS `GetLogTrail`; DELIMITER // CREATE PROCEDURE `GetLogTrail`( IN `pid` INT, IN `cid` INT, IN `masterid` BIGINT, IN `rectype` VARCHAR(30) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET SESSION group_concat_max_len = 1000000; SET @Call_Log_Count =0; SET @Call_Log_Any_Campaign=0; SET @Call_Log_Active_File=0; SET @PhoneNoLog=""; SET @logtable=""; SET @mastertable=""; SET @ColumnName=""; SELECT campaign.cl_lg_disp_no,campaign.call_log_from_any_campaign,campaign.call_log_from_active_file_only INTO @Call_Log_Count,@Call_Log_Any_Campaign,@Call_Log_Active_File FROM campaign WHERE id =cid; SELECT formbuilder.table_name,log_table_name INTO @mastertable,@logtable FROM formbuilder WHERE formbuilder.process_id=pid AND form_type='master'; SELECT GROUP_CONCAT(CONCAT(case when mask>0 then CONCAT('CONCAT("XXXXXXXX",','RIGHT(',if(formbuilder_field.create_log=1,' t1.',' PM.'),formbuilder_field.field_name,',4))') ELSE formbuilder_field.field_name END)) INTO @ColumnName FROM formbuilder_field join formbuilder ON formbuilder_field.form_id=formbuilder.id WHERE formbuilder.process_id=pid AND form_type='master' AND ShowinLogTrail='1' ; if rectype='call' then if IFNULL(pid,0)>0 AND ifnull(cid,0)>0 AND @logtable!="" AND @mastertable!="" then SET @SqlQuery=CONCAT('SELECT t1.id Log_Id,t4.name Campaign,file_name "FileName",t1.log_phone_no DialedNo,Agent_Name,Start_Time ,Pick_Time,End_Time,t1.callback_time, t1.CallType,t1.disposition "Disposition",t1.sub_disposition SubDisposition ', case when @ColumnName IS NOT NULL AND @ColumnName!='' then CONCAT(',',@ColumnName) ELSE '' END, ' FROM ',@logtable,' t1 JOIN ',@mastertable,' PM on t1.parent_id=PM.id JOIN campaign_mapping_',pid,' t2 ON t1.camp_map_id = t2.map_id JOIN campaign t4 ON t2.campaign_id = t4.id JOIN importmaster_',pid,' t3 ON t2.FileId = t3.id WHERE t1.disposition IS NOT NULL ', case when (@PhoneNoLog!='') then CONCAT(' AND log_phone_no="' ,@PhoneNoLog,'"') ELSE CONCAT(' AND t1.parent_id="' ,masterid,'"' ) END, case when @Call_Log_Active_File=1 then ' AND ifnull(t3.active_file,0) = 1 ' ELSE "" END ,case when @Call_Log_Any_Campaign=0 then CONCAT(' AND t1.campaign ="',cid,'"') ELSE "" END , ' ORDER BY Log_Id DESC limit ',case when IFNULL(@Call_Log_Count,0)>0 then @Call_Log_Count ELSE 0 end,' '); ELSE SET @SqlQuery='Select "Log_Id","Campaign","FileName","Agent_Name","Start_Time","Pick_Time","End_Time","callback_time","CallType","Disposition"'; END if; PREPARE GetLogTrailCallstmt FROM @SqlQuery; execute GetLogTrailCallstmt; DEALLOCATE PREPARE GetLogTrailCallstmt; elseif rectype='whatsapp' then set @SqlQuerySMS=CONCAT('SELECT added_on "message_sent_time",sms_log.receiver phone_no,agent.NAME "agent_name",message ,if(whatsapp_sent=1,"Yes","No")"is_sent_on_whatsapp" ,if(sms_sent=1,"Yes","No") "is_sent_on_sms" FROM sms_log left JOIN agent ON sms_log.agent_id=agent.id WHERE process_id="',pid,'" AND master_id="',masterid,'" and sms_log.`status`=1 ORDER BY sms_log.id desc limit ',case when IFNULL(@Call_Log_Count,0)>0 then @Call_Log_Count ELSE 0 end,' '); PREPARE GetLogTrailSMSstmt FROM @SqlQuerySMS; execute GetLogTrailSMSstmt; DEALLOCATE PREPARE GetLogTrailSMSstmt; elseif rectype='email' then set @SqlQueryEmail=CONCAT('SELECT added_on "email_sent_time",emailto"email_sent_to",agent.NAME "agent_name",subject,body email_body FROM email_log left JOIN agent ON email_log.agent_id=agent.id WHERE process_id="',pid,'" AND master_id="',masterid,'" and email_log.`status`=1 ORDER BY email_log.id desc limit ',case when IFNULL(@Call_Log_Count,0)>0 then @Call_Log_Count ELSE 0 end,' '); PREPARE GetLogTrailEmailstmt FROM @SqlQueryEmail; execute GetLogTrailEmailstmt; DEALLOCATE PREPARE GetLogTrailEmailstmt; ELSE SELECT 'Wrong Data Type'Message; END if; SELECT @SqlQueryEmail,@SqlQuerySMS,@SqlQuery; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getMissedCallDashboard DROP PROCEDURE IF EXISTS `getMissedCallDashboard`; DELIMITER // CREATE PROCEDURE `getMissedCallDashboard`( IN `DialStatus` VARCHAR(50), IN `rowlimit` VARCHAR(50) ) BEGIN if(IFNULL(rowlimit,'')='') then SET @QUERY='select count(1) '; else SET @QUERY='SELECT * ' ; END if; set @query1=concat('FROM (select caller_id PhoneNo, Case when acd_time IS not null and exten_ring_time IS null then "Queue" when exten_ring_time IS NOT NULL AND exten_ans_time IS NULL then "Agent Missed" when acd_time IS null then "IVR" end "CallEndAt", case when (cdr.start_time>pl1.start_time OR pl1.start_time IS NULL) then "Not Dialed" ELSE "Dialed" END "DialingStatus",cdr.start_time "Call Time" from cdr_1 cdr LEFT JOIN (SELECT MAX(start_time)start_time,log_phone_no FROM process_table_log_1 WHERE start_time>CURDATE() GROUP BY log_phone_no) pl1 ON right(cdr.caller_id,10)=right(pl1.log_phone_no,10) WHERE cdr.start_time>CURDATE() AND call_type="Incoming" AND cdr.end_call=1 AND cdr.exten_ans_time IS null AND acd_time IS NOT NULL and ifnull(acd,"")!="" AND cdr.campaign_id=1)tab ' ,if(IFNULL(DialStatus,'')='','',CONCAT(' where DialingStatus="',DialStatus,'"'))); SET @QUERY2=CONCAT(@QUERY,' ',@query1); #SELECT @QUERY2; PREPARE stmt FROM @QUERY2; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getPDCampaignData DROP PROCEDURE IF EXISTS `getPDCampaignData`; DELIMITER // CREATE PROCEDURE `getPDCampaignData`() NO SQL BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET @maxcalldialed=(SELECT max_pd_call_dial FROM company); SET @PDCampaignData=CONCAT('SELECT campaign.id campaign_id,process.id Process_id,process.name Process_name,campaign.name Campaign_name, ifnull(process_setting.hide_ph_no_agt_screen,0) AS "san_hide_number",process.pro_reco_fld AS"san_rec_folder", process_setting.dial_no_random,campaign.dial_on_idle,case when campaign.`type` = "IVR" then campaign.ivr_no else campaign.san_acd_code end acd_code,trunkgroup.name AS "Trunck_Group_Id", campaign.pref_dial_pre As "pref_dial_pre",campaign.type AS "campaign_type" ,campaign.ivr_no,campaign.ivr_call,campaign.pacing,campaign.dyn_pacing,campaign.enable_agent_mapping,process_setting.cl_bc_agt_map, campaign.max_dial_tm,campaign.max_call_tm,campaign.caller_id,IFNULL(cmpcontext.name,processcontext.name) AS "Context", case when round(Count(agent_current_states.agent_name)*campaign.pacing,0)>"',@maxcalldialed,'" then "',@maxcalldialed,'" ELSE round(Count(agent_current_states.agent_name)*campaign.pacing,0) end TotalDialNo, ifnull(campaign.minPDlist,250) min_pd_list,ifnull(campaign.maxPDlist,500) max_pd_list FROM agent_current_states JOIN agent_login_campaign ON agent_current_states.agent_id=agent_login_campaign.agent_id JOIN campaign ON agent_login_campaign.campaign_id=campaign.id JOIN process ON campaign.process_id=process.id JOIN process_setting ON process_setting.process_id=process.id LEFT JOIN context cmpcontext on cmpcontext.id=campaign.context LEFT JOIN context processcontext on processcontext.id=campaign.context LEFT JOIN trunkgroup on campaign.san_trunkgroup_id=trunkgroup.id WHERE campaign.active_campaign=1 AND process.isActive=1 and campaign.dial_mode = "Predictive" AND ifnull(agent_current_states.login_extension_no,"") !="" and ifnull(agent_current_states.manual_on,0)=0 and campaign.`type` in("Both","Outbound") /*AND ifnull(campaign.manual_mode_on,0)=0*/ AND ((ifnull(campaign.dial_on_idle,0)=0 AND agent_current_states.`status` NOT IN("8","10","4","2","1","0")) OR (ifnull(campaign.dial_on_idle,0)=1 AND agent_current_states.`status` IN("3","11"))) AND campaign.start_date<=CURDATE() AND campaign.end_date>=CURDATE() AND NOW() BETWEEN CONCAT(CURDATE()," ",campaign.start_time_hh,":",campaign.start_time_mm,":00") AND CONCAT(CURDATE()," ",campaign.end_time_hh,":",campaign.end_time_mm,":00") GROUP BY campaign.id ,process.id ,process.name,process_setting.dial_no_random,campaign.dial_on_idle,acd_code,campaign.pref_dial_pre,campaign.type ,campaign.ivr_no,campaign.ivr_call,campaign.pacing,campaign.dyn_pacing,campaign.enable_agent_mapping,process_setting.cl_bc_agt_map, campaign.max_dial_tm,campaign.max_call_tm,campaign.caller_id,process.pro_reco_fld,campaign.context,process_setting.hide_ph_no_agt_screen ',if((SELECT 1 FROM campaign WHERE `type` = "IVR" LIMIT 1)=1,' union all SELECT campaign.id campaign_id,process.id Process_id,process.name Process_name,campaign.name Campaign_name, ifnull(process_setting.hide_ph_no_agt_screen,0) AS "san_hide_number",process.pro_reco_fld AS"san_rec_folder", process_setting.dial_no_random,campaign.dial_on_idle,case when campaign.`type` = "IVR" then campaign.ivr_no else campaign.san_acd_code end acd_code,trunkgroup.name AS "Trunck_Group_Id", campaign.pref_dial_pre As "Prefix_To_Dail",campaign.type AS "campaign_type" ,campaign.ivr_no,campaign.ivr_call,campaign.pacing,campaign.dyn_pacing,campaign.enable_agent_mapping,process_setting.cl_bc_agt_map, campaign.max_dial_tm,campaign.max_call_tm,campaign.caller_id,IFNULL(cmpcontext.name,processcontext.name) AS "Context", campaign.ivr_call TotalDialNo,ifnull(campaign.minPDlist,250) min_pd_list,ifnull(campaign.maxPDlist,500) max_pd_list FROM campaign JOIN process ON campaign.process_id = process.id JOIN process_setting ON process_setting.process_id = process.id LEFT JOIN context cmpcontext on cmpcontext.id = campaign.context LEFT JOIN context processcontext on processcontext.id = campaign.context LEFT JOIN trunkgroup on campaign.san_trunkgroup_id=trunkgroup.id WHERE campaign.active_campaign=1 AND process.isActive = 1 AND campaign.dial_mode = "Predictive" AND campaign.`type` = "IVR" AND campaign.start_date<=CURDATE() AND campaign.end_date>=CURDATE() AND NOW() BETWEEN CONCAT(CURDATE(),'' '',campaign.start_time_hh,'':'',campaign.start_time_mm,'':00'') AND CONCAT(CURDATE(),'' '',campaign.end_time_hh,'':'',campaign.end_time_mm,'':00'') GROUP BY campaign.id ,process.id,acd_code ,process.name,process_setting.dial_no_random,campaign.dial_on_idle,acd_code,campaign.pref_dial_pre,campaign.type ,campaign.ivr_no,campaign.ivr_call,campaign.pacing,campaign.dyn_pacing,campaign.enable_agent_mapping,process_setting.cl_bc_agt_map, campaign.max_dial_tm,campaign.max_call_tm,campaign.caller_id,process.pro_reco_fld,campaign.context,process_setting.hide_ph_no_agt_screen ;','')); PREPARE stmt3 FROM @PDCampaignData; execute stmt3; DEALLOCATE PREPARE stmt3; SELECT @PDCampaignData; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getPDCampaignDataObj DROP PROCEDURE IF EXISTS `getPDCampaignDataObj`; DELIMITER // CREATE PROCEDURE `getPDCampaignDataObj`() BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET @maxcalldialed=(SELECT max_pd_call_dial FROM company); SET @PDCampaignData=CONCAT('SELECT campaign.id campaign_id,process.id Process_id,process.name Process_name,campaign.name Campaign_name, ifnull(process_setting.hide_ph_no_agt_screen,0) AS "san_hide_number",ifnull(process_setting.dial_no_random,0) as "dial_no_random",ifnull(campaign.max_PD_channel,0) as "max_pd_channel",ifnull(campaign.maxPDlist,1000) as "max_data_list",ifnull(campaign.minPDlist,100) as "min_data_list",ifnull(process_setting.dial_altrnt_no,0) as "dial_altrnt_no",process.pro_reco_fld AS"san_rec_folder", campaign.dial_on_idle,case when campaign.`type` = "IVR" then campaign.ivr_no else campaign.san_acd_code end acd_code,trunkgroup.name AS "Trunck_Group_Id", campaign.pref_dial_pre As "pref_dial_pre",campaign.type AS "campaign_type" ,campaign.ivr_no,ifnull(campaign.ivr_call,0) AS "ivr_call",campaign.pacing,campaign.dyn_pacing,campaign.enable_agent_mapping,process_setting.cl_bc_agt_map, campaign.max_dial_tm,campaign.max_call_tm,campaign.caller_id,IFNULL(cmpcontext.name,processcontext.name) AS "Context", case when campaign.`type` = "IVR" then campaign.ivr_call else 0 end as "TotalDialNo", ',@maxcalldialed,' as "CompanyMaxDial", DATE_FORMAT(campaign.start_date,"%Y-%m-%d") as "start_date",DATE_FORMAT(campaign.end_date,"%Y-%m-%d") as "end_date", CONCAT(campaign.start_time_hh,":",campaign.start_time_mm) as "start_time", CONCAT(campaign.end_time_hh,":",campaign.end_time_mm) as "end_time",2000 as timeout FROM campaign JOIN process ON campaign.process_id=process.id JOIN process_setting ON process_setting.process_id=process.id LEFT JOIN context cmpcontext on cmpcontext.id=campaign.context LEFT JOIN context processcontext on processcontext.id=campaign.context LEFT JOIN trunkgroup on campaign.san_trunkgroup_id=trunkgroup.id WHERE campaign.active_campaign=1 AND process.isActive=1 and campaign.dial_mode = "Predictive" ;'); PREPARE stmt3 FROM @PDCampaignData; execute stmt3; DEALLOCATE PREPARE stmt3; SELECT @PDCampaignData; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getPDDialingData DROP PROCEDURE IF EXISTS `getPDDialingData`; DELIMITER // CREATE PROCEDURE `getPDDialingData`( IN `pid` VARCHAR(10), IN `cid` VARCHAR(10), IN `calllimit` VARCHAR(5) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET @Mastertab=''; SET @PhoneNo=''; SET @Callingorder=''; SET @RecFile_Name=''; SET @AlternateNo=''; SET @client_id=''; SET @check_dnc=''; SET @IsRendome=''; SET @dialalternateno=''; SET @Mastertab=CONCAT('process_table_',pid) ; SET @PhoneNo=(SELECT formbuilder_field.field_name FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid and ifnull(formbuilder_field.primary_phone_no,0)=1 AND formbuilder_field.data_type='Phone' LIMIT 1); SET @Callingorder=(SELECT GROUP_CONCAT( CONCAT('T1.' ,formbuilder_field.field_name,' ',formbuilder_field.order_by) ) FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid ); SET @RecFile_Name=(SELECT replace(group_concat(CONCAT('ifnull(T1.',formbuilder_field.field_name,',"")')),'),','),"_",') FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid and ifnull(formbuilder_field.add_in_recording_file,0)=1); SELECT client_id,ifnull(check_dnc,0),ifnull(process_setting.dial_no_random,0),ifnull(process_setting.dial_altrnt_no,0) INTO @client_id,@check_dnc,@IsRendome,@dialalternateno FROM process LEFT JOIN process_setting ON process.id=process_setting.process_id WHERE process.id=pid LIMIT 1; SET @AlternateNo = case when @dialalternateno=1 then (SELECT group_concat(CONCAT('if(ifnull(T1.',formbuilder_field.field_name,','''')="",''''',',CONCAT(",",T1.',formbuilder_field.field_name,'))')) FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid AND ifnull(formbuilder_field.primary_phone_no,0)=0 and formbuilder_field.data_type='Phone') ELSE '' END; IF IFNULL(@PhoneNo,'')!='' THEN DROP TABLE if EXISTS tempimportmaster; SET @importquery = CONCAT('create temporary table tempimportmaster as select * from importmaster_',pid,' where id > 1 and IFNULL(active_file,0)=1 AND mark_for_deletion=0 AND ( valid_upto is null OR valid_upto>=now())'); PREPARE stmt4 FROM @importquery; execute stmt4; DEALLOCATE PREPARE stmt4; SET @query1 =CONCAT('SELECT T1.Id Master_id,T2.map_id ,concat(',case when @AlternateNo='' then CONCAT("case when IFNULL(T2.CallAgainNo,'''')!='''' then T2.CallAgainNo else T1.",@PhoneNo,' END') else CONCAT("case when IFNULL(T2.CallAgainNo,'''')!='''' then T2.CallAgainNo else T1.",@PhoneNo,' end',',',@AlternateNo) END,') Phone_No,T2.campaign_id,T2.agent_id,',case when @RecFile_Name IS NOT NULL then CONCAT('Concat(',@RecFile_Name,')') ELSE ' "" ' END,' recfile_name,T1.san_unique_id,IFNULL(isDNC,0) as isDNC, T3.id fileid,T3.file_name,T3.priority,',IFNULL(@Callingorder,'""'),' Callingorder from ',@Mastertab,' AS T1 JOIN campaign_mapping_',pid,' AS T2 on T1.id=T2.master_id JOIN tempimportmaster AS T3 on T3.id=T2.FileId JOIN campaign on campaign.id=T2.campaign_id ',case when @check_dnc=1 then CONCAT(' LEFT JOIN dnd_',@client_id,' t4 ON T1.',@PhoneNo,'=t4.phone_no AND t4.process_id=',pid,' AND t4.campaign_id=',cid) ELSE "" END,' WHERE T2.dialed=0 AND IFNULL(T2.DoNotCall,0)=0 AND IFNULL(T2.StopCalling,0)=0 AND IFNULL(T2.MapToAgent,0)=0 AND (T2.CallAgainTime IS NULL ) -- AND IFNULL(T1.isDNC,0)=0 AND T2.campaign_id IN(',cid,') and ifnull(T1.',@PhoneNo,',"")!="" ',case when @check_dnc=1 then CONCAT(' AND (t4.id IS NULL AND (t4.dnd_end_date IS NULL OR t4.dnd_end_date>NOW())) ') ELSE "" END,' ORDER BY T3.priority,',Case when IFNULL(@Callingorder,'')!='' then Concat(@Callingorder ,' ,T2.NoOfAttempts ASC ') else ' T2.NoOfAttempts ASC ' END ,Case when @IsRendome='1' then ',Rand() asc ' else ' ' END , ' LIMIT ',calllimit,' ; '); ELSE SET @query1 = ' SELECT "" Master_id,"" map_id,"" PhoneNO,"" campaign_id,"" agent_id,"" recfile_name,"" san_unique_id Limit 0;'; END IF; PREPARE stmt3 FROM @query1; execute stmt3; DEALLOCATE PREPARE stmt3; SELECT @query1; DROP TABLE if EXISTS tempimportmaster; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getprogressivedata DROP PROCEDURE IF EXISTS `getprogressivedata`; DELIMITER // CREATE PROCEDURE `getprogressivedata`( IN `pid` VARCHAR(50), IN `cid` VARCHAR(50), IN `agent_id` VARCHAR(100), IN `setdialed` INT, IN `disposition` VARCHAR(50), IN `phone_no_field` VARCHAR(50), IN `callingorder` TINYTEXT, IN `agent_mapping` INT, IN `client_id` INT, IN `check_dnc` INT, IN `dial_random_no` INT ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET @map_id=0; SET @Mastertab=""; SET @Mastertab=CONCAT('process_table_',pid) ; SET @PhoneNo=case when ifnull(phone_no_field,'')!='' then phone_no_field ELSE (SELECT formbuilder_field.field_name FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid and ifnull(formbuilder_field.primary_phone_no,0)=1 AND formbuilder_field.data_type='Phone' LIMIT 1) end; SET @Callingorder=case when IFNULL(callingorder,'')!='' then replace(CONCAT('T1.',callingorder),',',',T1.') ELSE (SELECT GROUP_CONCAT( CONCAT('T1.',formbuilder_field.field_name,' ',formbuilder_field.order_by) ) FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid ) end; SET @AgentMapping=case when (IFNULL(agent_mapping,0)>0) then agent_mapping ELSE (SELECT enable_agent_mapping FROM campaign WHERE id=cid LIMIT 1) end; SET @PrimaryField = (SELECT formbuilder_field.field_name FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid and ifnull(formbuilder_field.primary_field,0)=1 LIMIT 1); SET @client_id=client_id; SET @check_dnc=check_dnc; SET @IsRendome=dial_random_no; IF IFNULL(@PhoneNo,'')!='' AND IFNULL(@Mastertab,'')!='' THEN DROP TABLE if EXISTS tempimportmaster1; SET @importquery = CONCAT('create temporary table tempimportmaster1 as select * from importmaster_',pid,' where id > 1 and IFNULL(active_file,0)=1 AND mark_for_deletion=0 AND ( valid_upto is null OR valid_upto>=now())'); PREPARE stmt4 FROM @importquery; execute stmt4; DEALLOCATE PREPARE stmt4; SET @query1 =CONCAT('SELECT T2.master_id,T2.map_id,replace(replace(T1.',IFNULL(@PhoneNo,""),',char(10),''''),char(13),'''') phone_no,T1.',IFNULL(@PrimaryField,""),' primary_field into @master_id,@map_id,@phone_no,@primary_field from ',@Mastertab,' AS T1 JOIN campaign_mapping_',pid,' AS T2 on T1.id=T2.master_id JOIN tempimportmaster1 AS T3 on T3.id=T2.FileId JOIN campaign on campaign.id=T2.campaign_id ',case when @check_dnc=1 then CONCAT(' LEFT JOIN dnd_',@client_id,' t4 ON T1.',@PhoneNo,'=t4.phone_no AND t4.process_id=',pid) ELSE "" END,' WHERE T2.dialed=0 AND (T2.DoNotCall=0 or T2.DoNotCall is null) AND (T2.StopCalling is null or T2.StopCalling=0) AND (T2.CallAgainTime IS NULL ) AND (T1.isDNC is null or T1.isDNC=0 ) ',case when @check_dnc=1 then CONCAT(' AND (t4.id IS NULL AND t4.dnd_end_date IS NULL OR t4.dnd_end_date0 AND ifnull(setdialed,0)>0 then SET @QUERY2 =CONCAT('UPDATE campaign_mapping_',pid,' set dialed=1,NoOfAttempts=ifnull(NoOfAttempts,0)+1 WHERE map_id="',@map_id,'" ;'); PREPARE stmt3 FROM @QUERY2; execute stmt3; DEALLOCATE PREPARE stmt3; END if; if @map_id>0 then SELECT @master_id master_id,@map_id map_id,@phone_no phone_no,@primary_field primary_field; ELSE SELECT "master_id","map_id" ,"phone_no","primary_field" LIMIT 0; END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getSearchCallback DROP PROCEDURE IF EXISTS `getSearchCallback`; DELIMITER // CREATE PROCEDURE `getSearchCallback`( IN `pid` VARCHAR(50), IN `cid` VARCHAR(50), IN `agent_id` VARCHAR(100), IN `wherecondition` TEXT, IN `rownum` VARCHAR(20), IN `phone_no_field` VARCHAR(50), IN `callingorder` TINYTEXT, IN `master_fields` TEXT, IN `log_fields` TEXT, IN `CallbackfromAnyCampaign` INT, IN `AgentMapping` INT, IN `agentsetdnc` INT, IN `Callbackpopup` INT ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET @Callingorder=NULL; SET @filedlist=""; SET @filedlist1=""; SET @PhoneNo=NULL; SET @Mastertab=NULL; SET @AgentMapping=0; SET @CallbackfromAnyCampaign=0; SET @AgentMapping=0; SET @agentsetdnc=0; SET @Srno=0; SET @SearchCallback=NULL; SET @Callbackpopup=0; SET @logtable=NULL; SET @Mastertab=CONCAT('process_table_',pid); SET @logtable=CONCAT('process_table_log_',pid); SET @PhoneNo=case when ifnull(phone_no_field,'')!='' then CONCAT('replace(replace(',phone_no_field,',char(10),''''),char(13),'''')') ELSE (SELECT CONCAT('replace(replace(',formbuilder_field.field_name,',char(10),''''),char(13),'''')') FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid and ifnull(formbuilder_field.primary_phone_no,0)=1) END; SET @Callingorder=case when ifnull(callingorder,'')!='' then callingorder ELSE (SELECT GROUP_CONCAT( CONCAT( formbuilder_field.field_name,' ',formbuilder_field.order_by) ) FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid AND formbuilder_field.order_by IS NOT null) END; SET @filedlist=case when IFNULL(master_fields,'')='' then (SELECT GROUP_CONCAT( CONCAT('T1.',formbuilder_field.field_name) ) FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid AND formbuilder_field.show_in_call_back=1 AND formbuilder_field.create_log=0) else CONCAT('T1.',REPLACE(master_fields,',',',T1.')) end; SET @filedlist1=case when IFNULL(log_fields,'')='' then (SELECT GROUP_CONCAT( CONCAT('T4.','"',formbuilder_field.field_name,'"') ) FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid AND formbuilder_field.show_in_call_back=1 AND formbuilder_field.create_log=1) else CONCAT('T4.',REPLACE(log_fields,',',',T4.')) end; SET @CallbackfromAnyCampaign=CallbackfromAnyCampaign; SET @AgentMapping=case when (IFNULL(AgentMapping,0)>0) then AgentMapping ELSE (SELECT enable_agent_mapping FROM campaign WHERE id=cid LIMIT 1) end; SET @agentsetdnc=agentsetdnc; SET @Callbackpopup=Callbackpopup; IF IFNULL(@PhoneNo,'')!='' THEN if rownum IS NULL OR rownum='' then SET @selectquery ='select Count(1) row_num '; SET @orderquery=' '; ELSE SET @selectquery =CONCAT('SELECT T2.master_id,T2.map_id, ',@PhoneNo,' phone_no,T2.CallAgainTime "CallAgainTime",T2.CallAgainNo "CallAgainNo",T2.CallAgainType "CallAgainType",campaign.name "campaign_name",agent.name agent_name,T2.disposition Disposition,T4.sub_disposition Sub_Disposition,T2.mapping_remarks',if( IFNULL(@filedlist,"")!="" , CONCAT(',',IFNULL(@filedlist,"")) , "" ),if( IFNULL(@filedlist1,"")!="" , CONCAT(',',IFNULL(@filedlist1,"")) , "" ),' ' ); SET @orderquery=CONCAT(' ORDER BY T2.CallAgainTime asc ',if(rownum IS NULL OR rownum =""," ",CONCAT(' limit ',rownum)),' '); END if; SET @fromquery =CONCAT(' FROM ',@Mastertab,' AS T1 JOIN campaign_mapping_',pid,' AS T2 on T1.id=T2.master_id JOIN importmaster_',pid,' AS T3 on T3.id=T2.FileId JOIN campaign on campaign.id=T2.campaign_id LEFT JOIN agent on T2.Agent_id= agent.id LEFT JOIN ',@logtable,' AS T4 on T2.lastlogid=T4.id WHERE 1=1 AND ',if(ifnull(wherecondition,'')='1=1',CONCAT('CallAgainTime>="',CURDATE(),' 00:00:00" AND CallAgainTime<="',CURDATE(),' 23:59:59"'),ifnull(wherecondition,'')),' AND (IFNULL(T3.active_file,0)=1 OR (IFNULL(T3.active_file,0)=0 AND IFNULL(T3.retain_callback,0)=1 AND T2.CallAgainTime is not null )) AND ( T3.valid_upto is null OR T3.valid_upto<=CallAgainTime) AND T3.mark_for_deletion=0 AND T2.dialed=0 AND IFNULL(T2.StopCalling,0)=0 AND IFNULL(T2.DoNotCall,0)=0 AND ifnull(T2.Callbackdial_on_Hold,0)=0 AND 1=if(campaign.dial_mode="Predictive" and (T2.MapToAgent = 0 OR T2.MapToAgent IS NULL),2,1) AND ifnull(T2.CallAgainNo,',IFNULL(@PhoneNo,''),') not in(SELECT current_phone_no FROM agent_current_states WHERE ifnull(current_phone_no,"")!="") AND T2.map_id not in(select map_id from check_duplicate_dial) AND (T2.Agent_id is null OR Ifnull(T2.NoOfAttempts,0)>0 OR (T2.Agent_id="',agent_id,'" AND Ifnull(T2.NoOfAttempts,0)=0))' ,if(@agentsetdnc=1,' AND ifnull(T1.isDNC,0)=0 ',"") ,if(@AgentMapping=1,CONCAT('AND (( T2.agent_id IS NULL OR MapToAgent = 0 OR MapToAgent is NULL) OR (MapToAgent = 1 AND T2.Agent_id="',agent_id,'"))'),"") ,if(@CallbackfromAnyCampaign=1 , "" ,CONCAT(' AND T2.campaign_id IN(',cid,')' )) ); SET @SearchCallback= CONCAT(@selectquery,' ',@fromquery,' ',@orderquery,' ;'); ELSE SET @SearchCallback =CONCAT('SELECT "master_id","map_id", "phone_no","CallAgainTime","CallAgainNo","CallAgainType","campaign_name", "agent_name" ',case when @filedlist IS NOT NULL AND @filedlist !='' then CONCAT(',',@filedlist) ELSE '' END,case when @filedlist1 IS NOT NULL AND @filedlist1 !='' then CONCAT(',',@filedlist1) ELSE '' END ,' LIMIT 0;'); END if; SELECT @SearchCallback; PREPARE stmt3 FROM @SearchCallback; execute stmt3; DEALLOCATE PREPARE stmt3; SELECT @SearchCallback campaign_name,@AgentMapping agent_name; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.getTrunkWiseCall DROP PROCEDURE IF EXISTS `getTrunkWiseCall`; DELIMITER // CREATE PROCEDURE `getTrunkWiseCall`( IN `cur_client_id` INT, IN `pid` VARCHAR(50), IN `cid` VARCHAR(50), IN `from_date` VARCHAR(25), IN `to_date` VARCHAR(25) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET SESSION group_concat_max_len = 1000000; SELECT GROUP_CONCAT('trunk_no NOT LIKE CONCAT("%SIP-",',extension_no,',"%") ' SEPARATOR ' and ') INTO @exten FROM extension; SET @cdrtable = case when (LEFT(from_date,10)=CURDATE() AND LEFT(to_date,10)=CURDATE()) then 'cdrtemp' ELSE CONCAT('cdr_',ifnull(cur_client_id,0)) END; SET @process_id = case when IFNULL(pid,'') = '' then (SELECT GROUP_CONCAT(id) FROM process WHERE client_id=cur_client_id) ELSE pid END; SET @campaign_id = case when IFNULL(cid,'') = '' then (SELECT GROUP_CONCAT(campaign.id) FROM campaign JOIN process ON campaign.process_id=process.id WHERE client_id=cur_client_id) ELSE cid END; SET @query1 = CONCAT('select trunk_no "Trunk No",dialed Dialed,connected Connected,disconnect_in_0_sec "Dropped_in_5_Sec",ROUND((connected*100)/dialed,2) "Connect %",ROUND((disconnected*100)/dialed,2) "Drop %" from ( SELECT case when cdr.trunk_channel LIKE "SIP%" then REPLACE(SUBSTRING_INDEX(cdr.trunk_channel, "-", 1),"/","-") ELSE REPLACE(SUBSTRING_INDEX(cdr.trunk_channel, "/", 2),"DAHDI/i","PRI-") END trunk_no ,count(*) dialed,sum(case when connected = 1 then 1 else 0 end) connected, sum(case when connected = 0 then 1 else 0 end) disconnected,sum(case when duration <= 5 and connected = 0 then 1 else 0 end) disconnect_in_0_sec from ',@cdrtable,' cdr left join campaign on cdr.campaign_id=campaign.id left join process on cdr.process_id=process.id where start_time>DATE_ADD(CURDATE(),INTERVAL -7 DAY) and start_time >= "',from_date,'" and start_time<= "',to_date,'" AND end_call=1 and cdr.process_id in (',@process_id,') and cdr.campaign_id in (',@campaign_id,') group by 1 ) as call_details where 1=1 and ',@exten,' order by 5 DESC'); #SELECT @query1; PREPARE stmt FROM @query1; execute stmt; DEALLOCATE PREPARE stmt; SET @query2 = CONCAT('select "Total" as "Trunk No",SUM(Dialed) Dialed,SUM(Connected) Connected,SUM(Dropped_in_5_Sec) Dropped_in_5_Sec,SUM(Connect_percent) "Connect %",SUM(Drop_percent) "Drop %" from ( select trunk_no "Trunk No",dialed Dialed,connected Connected,disconnect_in_0_sec "Dropped_in_5_Sec",ROUND((connected*100)/dialed,2) "Connect_percent",ROUND((disconnected*100)/dialed,2) "Drop_percent" from ( SELECT case when cdr.trunk_channel LIKE "SIP%" then REPLACE(SUBSTRING_INDEX(cdr.trunk_channel, "-", 1),"/","-") ELSE REPLACE(SUBSTRING_INDEX(cdr.trunk_channel, "/", 2),"DAHDI/i","PRI-") END trunk_no ,count(*) dialed,sum(case when connected = 1 then 1 else 0 end) connected, sum(case when connected = 0 then 1 else 0 end) disconnected,sum(case when duration <= 5 and connected = 0 then 1 else 0 end) disconnect_in_0_sec from ',@cdrtable,' cdr left join campaign on cdr.campaign_id=campaign.id left join process on cdr.process_id=process.id where start_time>DATE_ADD(CURDATE(),INTERVAL -7 DAY) and start_time >= "',from_date,'" and start_time<= "',to_date,'" AND end_call=1 and cdr.process_id in (',@process_id,') and cdr.campaign_id in (',@campaign_id,') group by 1 ) as call_details where 1=1 and ',@exten,' )tab '); PREPARE stmt FROM @QUERY2; execute stmt; DEALLOCATE PREPARE stmt; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.get_callback_list DROP PROCEDURE IF EXISTS `get_callback_list`; DELIMITER // CREATE PROCEDURE `get_callback_list`( IN `RowCount` VARCHAR(50), IN `pid` VARCHAR(50), IN `FromDate` VARCHAR(20), IN `ToDate` VARCHAR(20) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET @PhoneNoField=NULL; SET @ColName=NULL; SET SESSION group_concat_max_len = 1000000; SELECT GROUP_CONCAT(if(formbuilder_field.create_log=1,CONCAT('pl.',formbuilder_field.field_name),CONCAT('pm.',formbuilder_field.field_name))) INTO @ColName from formbuilder JOIN formbuilder_field ON formbuilder.id=formbuilder_field.form_id WHERE formbuilder.form_type='master' AND process_id=pid AND formbuilder_field.show_in_call_back=1 AND formbuilder_field.primary_phone_no=0 ; SELECT CONCAT('replace(replace(',formbuilder_field.field_name,',char(10),''''),char(13),'''')') INTO @PhoneNoField FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=pid and ifnull(formbuilder_field.primary_phone_no,0)=1 AND formbuilder_field.data_type='Phone' LIMIT 1; If IFNULL(@PhoneNoField,'')!='' then SET @QUERY1=CONCAT('select cm.master_id,cm.map_id, ',@PhoneNoField,' Phone_Number,c.name campaign_name,cm.CallAgainTime CallbackTime, cm.CallAgainType,agent_id, CallAgainNo ,CallbackType,pl.disposition,pl.sub_disposition ', if(IFNULL(@ColName,'')!='',CONCAT(',',@ColName),''),' FROM process_table_',pid,' pm JOIN campaign_mapping_',pid,' cm ON pm.id=cm.master_id JOIN campaign c on cm.campaign_id=c.id LEFT JOIN (SELECT max(id)lastlog, parent_id from process_table_',pid,'_log pl group by parent_id )lpl on lpl.parent_id=pm.id LEFT JOIN process_table_',pid,'_log pl on lpl.lastlog=pl.id WHERE dialed="0" AND CallAgainTime IS NOT NULL', case when IFNULL(FromDate,'')!="" then CONCAT(' AND CallAgainTime>="',FromDate,'" ') ELSE '' END, case when IFNULL(ToDate,'')!='' then CONCAT(' AND CallAgainTime<="',ToDate,'"') ELSE'' END ); if ifnull(Rowcount,"")!="" then SET @QUERY1=CONCAT( @QUERY1, case when IFNULL(Rowcount,"")!="" then CONCAT(' Limit ',Rowcount) ELSE "" END ,' ;' ); ELSE SET @QUERY1=CONCAT('Select count(1) cnt from (',@QUERY1, ')cnt ;'); END if; END if; PREPARE CR_callback_Report FROM @QUERY1; EXECUTE CR_callback_Report; DEALLOCATE PREPARE CR_callback_Report; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; END// DELIMITER ; -- Dumping structure for procedure version1.get_CallLogReport DROP PROCEDURE IF EXISTS `get_CallLogReport`; DELIMITER // CREATE PROCEDURE `get_CallLogReport`( IN `pid` VARCHAR(100), IN `custom_field` VARCHAR(3000), IN `custom_condition` VARCHAR(2000), IN `custom_order` VARCHAR(500), IN `pagelimit` VARCHAR(500), IN `tablename` VARCHAR(100) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET SESSION group_concat_max_len = 1000000; SET @Mastertab=NULL; SET @logtab=NULL; SET @sublogtab=NULL; if IFNULL(tablename,'')!='' then SET @droptable1 = CONCAT('drop table if exists ',tablename); PREPARE stmt_droptable1 FROM @droptable1; EXECUTE stmt_droptable1; DEALLOCATE PREPARE stmt_droptable1; END if; SELECT IFNULL(allow_agt_pl_reco,0) INTO @AgentToPlayRec FROM process_setting WHERE process_id=pid; if ifnull(pid,0)>0 then SELECT TABLE_NAME,log_table_name,sub_log_table_name INTO @Mastertab,@logtab,@sublogtab FROM formbuilder WHERE Process_id=pid AND form_type='master' LIMIT 1; SELECT group_concat(case when formbuilder_field.create_log=0 then CONCAT( 'CONCAT("******",RIGHT(t1.',formbuilder_field.field_name,',1) As "',formbuilder_field.field_description,'"') END) , group_concat(case when formbuilder_field.create_log=1 then CONCAT(' t2.',formbuilder_field.field_name,' As "',formbuilder_field.field_description,'"') END ) INTO @process_column_listMaster,@process_column_listlog FROM formbuilder_field JOIN formbuilder ON formbuilder.id=formbuilder_field.form_id WHERE Process_id=pid AND formbuilder_field.sh_on_cl_lg=1; SET @process_column_list=CONCAT(if( IFNULL(@process_column_listMaster,"")!="" ,CONCAT(',', @process_column_listMaster) , "" ), ',campaign.name as "Campaign Name",t2.agent_name as "Agent Name",t2.start_time As "Start_Time",t2.pick_time As "Pick_Time",t2.end_time As "End_Time",t2.duration As"Crm_Duration",sublog.Call_Duration ,t2.disposition AS "Disposition",t2.sub_disposition As "Sub_Disposition"',if( IFNULL(@process_column_listlog,"")!="" , CONCAT(',',@process_column_listlog) , "" )); SET @SELECTQUERY=CONCAT('SELECT t2.parent_id AS"Masterid",t2.id AS "LogId" , t2.campaign As "Camp_id" ',Case when IFNULL(custom_field,'') NOT IN('All','') then CONCAT(',',custom_field) when IFNULL(custom_field,'')='All' then IFNULL(@process_column_list,'') ELSE '' END,if(@AgentToPlayRec=1," ,ifnull(recoding_file,'No-Recording-Found!') recoding_file","") ); SET @TABLEQUERY=CONCAT(' FROM ',@Mastertab,' t1 LEFT join branch_master ON t1.branch_id=branch_master.id RIGHT JOIN ',@logtab,' t2 ON t2.parent_id=t1.id LEFT JOIN campaign on t2.campaign=campaign.id LEFT JOIN campaign_mapping_',pid,' cmpMap ON cmpMap.map_id=t2.camp_map_id /*campaign_mapping_',pid,'.map_id=t2.camp_map_id*/ LEFT JOIN user ON user.id=t2.team_leader LEFT JOIN agent ON agent.name=t2.agent_name LEFT JOIN (SELECT crm_id ,SUM(ifnull(bill_duration,0)) as''Call_Duration'',group_concat(reco_file)recoding_file FROM ',@sublogtab,' WHERE nullif(crm_id,''0'')!=''0'' AND ifnull(bill_duration,0)>0 GROUP BY crm_id)sublog ON sublog.crm_id=t2.crm_id where 1=1 ',Case when IfNULL(custom_condition,'')!='' then CONCAT(' and ',custom_condition) ELSE ' ' END ); if IFNULL(pagelimit,'')='' AND IFNULL(tablename,'')='' then SET @CALL_LOG_QUERY=CONCAT('select count(1) as rowcount ',@TABLEQUERY); ELSEIF IFNULL(pagelimit,'')!='' AND IFNULL(tablename,'')='' then SET @CALL_LOG_QUERY=CONCAT(@SELECTQUERY,' ',@TABLEQUERY,' Order by ',Case when IfNULL(custom_order,'')!='' then custom_order ELSE ' t2.id ' END ,Case when IfNULL(pagelimit,'')!='' then CONCAT(' limit ',pagelimit) ELSE ' limit 0,100 ' END); ELSE SET @CALL_LOG_QUERY=CONCAT('create table ',tablename,' as ',@SELECTQUERY,' ',@TABLEQUERY,' Order by ',Case when IfNULL(custom_order,'')!='' then custom_order ELSE ' t2.id ' END); END if; ELSE SET @CALL_LOG_QUERY='select "select valid process" AS "ERROR"'; END if; #SELECT @CALL_LOG_QUERY; PREPARE process_call_log_stmt FROM @CALL_LOG_QUERY; EXECUTE process_call_log_stmt; DEALLOCATE PREPARE process_call_log_stmt; if IFNULL(tablename,'')!='' then SELECT 1 return_value; END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; END// DELIMITER ; -- Dumping structure for procedure version1.get_CDRData DROP PROCEDURE IF EXISTS `get_CDRData`; DELIMITER // CREATE PROCEDURE `get_CDRData`( IN `ReportType` VARCHAR(50), IN `PID` VARCHAR(50), IN `team_leader_id` INT, IN `camp_ids` VARCHAR(50) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET SESSION group_concat_max_len=1000000; SET @clientid=(SELECT group_concat(ifnull(client_id,0)) FROM process WHERE id IN (pid)); SET @CdrData=CONCAT('SELECT ',Case when ReportType='CampainWise' then ' campaign.name `CampaignName`,campaign.id `Campaign_Id`, campaign.san_acd_code acd_code, ' ELSE '' END, ' group_concat(distinct campaign.Process_id) `Process_Id`, Sum(Case when ((acd_time IS NOT NULL and ifnull(acd,"")!="") OR (ifnull(out_call_type,"")="PD" and acd_time IS NOT NULL and ifnull(acd,"")!="") OR call_type="Incoming") then 1 ELSE 0 END)`Offered_Call`, Sum(Case when ((acd_time IS NOT NULL and ifnull(acd,"")!="") OR (ifnull(out_call_type,"")="PD" and acd_time IS NOT NULL and ifnull(acd,"")!="") OR call_type="Incoming") AND exten_ans_time IS NOT NULL then 1 ELSE 0 END)`Ans_Call`, Sum(Case when ((acd_time IS NOT NULL and ifnull(acd,"")!="") OR (ifnull(out_call_type,"")="PD" and acd_time IS NOT NULL and ifnull(acd,"")!="") OR call_type="Incoming") AND exten_ans_time IS NULL then 1 ELSE 0 END)`Abn_Call`, ROUND((IFNULL(SUM(CASE WHEN acd_time IS NOT NULL and exten_ans_time IS NOT NULL AND ifnull(acd,"")!="" THEN 1 ELSE 0 END),0)*100.0)/NULLIF(SUM(CASE WHEN ifnull(acd,"")!="" AND acd_time IS NOT NULL THEN 1 ELSE 0 END),0),2)`SLA`, Sum(Case when acd_time IS NULL AND ifnull(out_call_type,"")!="PD" AND IFNULL(call_type,"")="Outgoing" then 1 ELSE 0 END)`Outgoing_Call`, Sum(Case when acd_time IS NULL AND ifnull(out_call_type,"")!="PD" AND IFNULL(call_type,"")="Outgoing" AND connected=1 then 1 ELSE 0 END)`Con_Outgoing`, /*ROUND((IFNULL(SUM(case when acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL then TIMESTAMPDIFF(second, exten_ans_time, end_time) ELSE 0 END),0)*100.0)/NULLIF((Sum(Case when acd_time IS NULL AND ifnull(out_call_type,"")!="PD" AND IFNULL(call_type,"")="Outgoing" then 1 ELSE 0 END)+Sum(Case when acd_time IS NOT NULL AND IFNULL(call_type,"")="Incoming" then 1 ELSE 0 END)),0),2)`AHT`, Round(Sum(Case when ((acd_time IS NOT NULL and ifnull(acd,"")!="") OR (ifnull(out_call_type,"")="PD" and acd_time IS NOT NULL and ifnull(acd,"")!="")) AND exten_ans_time IS NOT NULL then TIMESTAMPDIFF(second, exten_ans_time, end_time) ELSE 0 END)/Sum(Case when ((acd_time IS NOT NULL and ifnull(acd,"")!="") OR (ifnull(out_call_type,"")="PD" and acd_time IS NOT NULL and ifnull(acd,"")!="")) then 1 ELSE 0 END),2)`ATT`*/ SEC_TO_TIME(round(SUM(duration)/sum(Case when exten_ans_time is not null and cdr.connected>0 then 1 ELSE 0 END),0))`AHT`, SEC_TO_TIME(round(Sum(Case when exten_ans_time is not null then TIMESTAMPDIFF(second,exten_ans_time,end_time) ELSE 0 END)/sum(Case when exten_ans_time is not null and cdr.connected>0 then 1 ELSE 0 END),0))`ATT` FROM /*cdr_',@clientid,'*/ cdrtemp cdr LEFT JOIN campaign ON cdr.campaign_id=campaign.id LEFT JOIN `process` ON `process`.`id`=`cdr`.`process_id` LEFT JOIN `trunk` ON cdr.trunk_channel=`trunk`.name LEFT JOIN agent on cdr.agent=agent.name WHERE 1=1 and end_call=1 and Start_time>=curdate() AND ifnull(cdr.out_call_type,"")!="TR" AND ifnull(campaign.NAME,"")!="" ',Case when IFNULL(PID,"")!="" then CONCAT(' AND campaign.Process_id in (',PID,') ') ELSE ' ' END,case when IFNULL(team_leader_id,0)>0 then CONCAT(' AND ((ifnull(cdr.agent,"")="" AND ifnull(cdr.exten,"")="" ) OR (ifnull(cdr.agent,"")!="" AND agent.team_leader="',team_leader_id,'" ) ) ')ELSE '' END , ' ',case when ifnull(camp_ids,'')!='' then CONCAT(' AND campaign.id in (',camp_ids,')') ELSE '' end,' ',Case when ReportType='CampainWise' then 'GROUP BY campaign.name,campaign.san_acd_code,campaign.id,campaign.Process_id ' ELSE ' /*campaign.Process_id*/' END); PREPARE getcdrdata FROM @CdrData; execute getcdrdata; DEALLOCATE PREPARE getcdrdata; #SELECT @CdrData; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.get_CDRReport DROP PROCEDURE IF EXISTS `get_CDRReport`; DELIMITER // CREATE PROCEDURE `get_CDRReport`( IN `Clintid` INT, IN `Fromdate` VARCHAR(20), IN `Todate` VARCHAR(20), IN `cond` VARCHAR(500), IN `ReportType` VARCHAR(100), IN `IsCount` INT, IN `Orderby` VARCHAR(300), IN `PageLimit` VARCHAR(50), IN `IsExportReport` VARCHAR(50), IN `ReportFIlePath` VARCHAR(100), IN `tl_id` VARCHAR(100) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET @query1=NULL; If (SELECT 1 FROM client_master WHERE id=Clintid LIMIT 1)=1 Then SET @CdrTable=case when (LEFT(Fromdate,10)=CURDATE() AND LEFT(Todate,10)=CURDATE()) then 'cdrtemp' ELSE CONCAT('cdr_',ifnull(Clintid,0)) end; SET @LinkedIdALLData=CONCAT(' FROM ',@CdrTable,' cdr LEFT JOIN campaign ON cdr.campaign_id=campaign.id LEFT JOIN `process` ON `process`.`id`=`cdr`.`process_id`LEFT JOIN `trunk`ON cdr.trunk_channel=`trunk`.name left join hangupclause on hangupclause.code=cdr.cause LEFT JOIN agent on cdr.agent=agent.name LEFT JOIN user on user.id=agent.team_leader WHERE 1=1 ',case when ifnull(Fromdate,'') then CONCAT(' and Start_time>="',FromDate,'" ') ELSE '' END,case when ifnull(ToDate,'') then CONCAT(' and Start_time<="',ToDate,'" ') ELSE ''END,ifnull(cond,''), case when IFNULL(tl_id,'')='' then '' else CONCAT(' and user.id="',tl_id,'"') end); SET @LinkedIdLastData=CONCAT(' FROM ',@CdrTable,' cdr /*JOIN (select max(id)lastid,linked_id from ',@CdrTable,' where 1=1 ',case when ifnull(Fromdate,'') then CONCAT(' and Start_time>="',FromDate,'" ') ELSE '' END,case when ifnull(ToDate,'') then CONCAT(' and Start_time<="',ToDate,'" ') ELSE '' END,' group by linked_id ) Last_CDR on Last_CDR.lastid=cdr.id */ ',case when ReportType='Trunk Billed Summary' then ' JOIN extension on cdr.exten=extension.extension_no ' ELSE '' END ,' LEFT JOIN campaign ON cdr.campaign_id=campaign.id LEFT JOIN `process` ON `process`.`id`=`cdr`.`process_id` LEFT JOIN `trunk`ON cdr.trunk_channel=`trunk`.name left join hangupclause on hangupclause.code=cdr.cause LEFT JOIN agent on cdr.agent=agent.name LEFT JOIN user on user.id=agent.team_leader WHERE 1=1 and end_call=1 AND ifnull(cdr.out_call_type,"")!="TR" AND ifnull(campaign.name,"")!="" ',case when ifnull(Fromdate,'') then CONCAT(' and Start_time>="',FromDate,'" ') ELSE '' END,case when ifnull(ToDate,'') then CONCAT(' and Start_time<="',ToDate,'" ') ELSE ''END,ifnull(cond,''), case when IFNULL(tl_id,'')='' then '' else CONCAT('and user.id="',tl_id,'"') end); If (ReportType ='Date Wise CDR' OR ReportType ='Queue Wise CDR' OR ReportType ='Extension Wise CDR') Then If IsCount=1 then SET @SelectVar ='SELECT Count(1) LogCount '; ELSE SET @SelectVar =' SELECT cdr.exten "Exten",cdr.did "DID_No",Concat(campaign.name,"-",ifnull(cdr.acd,""))"Group_Name",cdr.caller_id "Called_No",Start_time,cdr.end_time"End_Time", case when exten_ring_time IS NOT NULL AND exten_ans_time IS NOT NULL AND exten_ring_time<=exten_ans_time THEN SEC_TO_TIME(TIMESTAMPDIFF(second, exten_ring_time, exten_ans_time)) when exten_ring_time IS NOT NULL AND exten_ans_time IS NULL THEN SEC_TO_TIME(TIMESTAMPDIFF(second, exten_ring_time, end_time )) ELSE "00:00:00" end AS "Ring_Durn",Case when call_type="Outgoing" then "00:00:00" ELSE SEC_TO_TIME(TIMESTAMPDIFF(second, start_time, case when acd_time IS NOT NULL and acd_time!="0000-00-00 00:00:00" then acd_time when (acd_time IS NULL or acd_time="0000-00-00 00:00:00") and exten_ring_time IS NOT NULL THEN exten_ring_time else end_time end)) end AS "IVR_Dur", case when acd_time IS NOT NULL AND acd_time!="0000-00-00 00:00:00" AND exten_ring_time IS NOT NULL and acd_time<=exten_ring_time THEN SEC_TO_TIME(TIMESTAMPDIFF(second, acd_time, exten_ring_time)) when acd_time IS NOT NULL and acd_time!="0000-00-00 00:00:00" AND (exten_ring_time IS NULL or ( exten_ring_time IS NOT NULL and acd_time>exten_ring_time)) AND exten_ans_time IS NOT NULL THEN SEC_TO_TIME(TIMESTAMPDIFF(second, acd_time, exten_ans_time)) when acd_time IS NOT NULL AND acd_time!="0000-00-00 00:00:00" AND (exten_ring_time IS NULL or ( exten_ring_time IS NOT NULL and acd_time>exten_ring_time)) AND exten_ans_time IS NULL THEN SEC_TO_TIME(TIMESTAMPDIFF(second, acd_time, end_time)) ELSE "00:00:00" end AS "Queue_Durn", Case when exten_ans_time is not null then SEC_TO_TIME(TIMESTAMPDIFF(second, exten_ans_time,end_time)) ELSE "00:00:00" end AS "Agent_Durn",case when sec_to_time(hold_duration) IS NULL then "00:00:00" else sec_to_time(hold_duration) END "Hold_Durn", SEC_TO_TIME(duration) AS "Durn",case when Ifnull(cdr.connected,0)>0 then "C" ELSE "M" END "C/M", case when acd_time is not null and exten_ans_time IS NULL and exten_ring_time is not null THEN "Agent Missed" when exten_ans_time is NOT NULL or (call_type="Outgoing" and exten_ring_time is not null) THEN "Agent" when exten_ans_time IS NULL and (acd_time IS NULL or acd_time="0000-00-00 00:00:00" or acd_time="") AND call_type="Incoming" THEN "IVR" when (acd_time IS NOT NULL and acd_time!="0000-00-00 00:00:00") and exten_ans_time is NULL THEN "Queue" ELSE "" end AS "Call_End_At", cdr.agent "Agent Name", CONCAT(user.first_name," ",user.last_name) "Team Leader",IFNULL(trunk.display_name,cdr.trunk_channel) "Trunk_No", hangupclause.name AS "Cause" ,dtmf AS "DTMF",feedback as "Feedback",call_type AS "Call_Type" ,case when agent_disconnect is NOT NULL and agent_disconnect=1 then "A" WHEN agent_disconnect=0 and agent_disconnect IS NOT NULL THEN "C" else "" end AS "A/C" ,acd AS "ACD",cdr.linked_id AS "Linked_ID",cdr.userfield AS "User_Field",cdr.ivr_name AS "IVR_Name" '; END If; SET @SelectVar1 =' SELECT "Exten" as "Exten","DID_No" as "DID_No","Group" As "Group","Called_No" as "Called_No","Start_Time" As "Start_Time","End_Time" AS "End_Time","Ring_Durn" AS "Ring_Durn","IVR_Dur" AS "IVR_Dur","Queue_Durn" AS "Queue_Durn","Agent_Durn" AS "Agent_Durn","Hold_Durn" AS "Hold_Durn","Durn" AS "Durn","C/M" AS "C/M","Call_End_At" AS "Call_End_At","Agent_Name" As "Agent_Name","Team_Leader" AS "Team_Leader","Trunk_No" AS "Trunk_No","Cause" AS "Cause" ,"DTMF" AS "DTMF","FEEDBACK" AS "FEEDBACK","Call_Type" AS "Call_Type","A/C" AS "A/C","ACD" AS "ACD","Linked_ID" AS "Linked_ID","User_Field" AS "User_Field","IVR_Name" AS "IVR_Name" Union ALL( '; SET @ExterWhereCond=' AND ifnull(cdr.exten,"")!="" '; SET @ExtenWiseOrderVar=CONCAT(' Order by cdr.exten asc ',Case when ifnull(Orderby,'')!='' then CONCAT(' , ',Orderby) ELSE ' , cdr.Start_time asc' end,Case when ifnull(PageLimit,'')!='' then PageLimit ELSE '' END ); SET @TableVar= Concat(Case when ReportType='Date Wise CDR' then @LinkedIdALLData else @LinkedIdLastData END,case when ReportType='Extension Wise CDR' then @ExterWhereCond ELSE "" END ); SET @OtherOrderVar =CONCAT(Case when ifnull(Orderby,'')!='' then CONCAT(' Order By ',Orderby) ELSE ' Order By cdr.Id desc' end,Case when ifnull(PageLimit,'')!='' then PageLimit ELSE '' END); SET @OrderVar= Case when ReportType='Extension Wise CDR' then @ExtenWiseOrderVar ELSE @OtherOrderVar END; SET @GroupRec=''; END if; If ReportType='DID Wise Summary' Then If IsCount=1 then SET @SelectVar ='SELECT Count(Distinct cdr.did) LogCount'; ELSE SET @SelectVar =' SELECT cdr.did "DID_No",COUNT(1)Total_Call,Sum(case when cdr.exten_ans_time IS NULL then 1 ELSE 0 END)Total_Missed, Sum(case when (acd_time="0000-00-00 00:00:00" or acd_time IS NULL) then 1 ELSE 0 END)IVR_Missed, Sum(case when acd_time IS NOT NULL AND acd_time!="0000-00-00 00:00:00" AND cdr.exten_ans_time IS NULL then 1 ELSE 0 END)Queue_Missed, Sum(case when cdr.exten_ans_time IS NOT NULL then 1 ELSE 0 END) "Ans", ROUND((Sum(case when (acd_time IS null or acd_time="0000-00-00 00:00:00")then 1 ELSE 0 END)*100.0)/COUNT(1),2) "IVR_Missed%", ROUND((Sum(case when acd_time IS NOT NULL AND acd_time!="0000-00-00 00:00:00" AND cdr.exten_ans_time IS NULL then 1 ELSE 0 END)*100.0)/COUNT(1),2)"Queue_Missed%", ROUND((Sum(case when cdr.exten_ans_time IS NOT NULL then 1 ELSE 0 END)*100.0)/COUNT(1),2) "Ans%", ROUND((Sum(case when cdr.exten_ans_time IS NULL then 1 ELSE 0 END)*100.0)/COUNT(1),2) "Total_Missed%"'; END If; SET @SelectVar1 =' SELECT "DID_No" as "DID_No","Total_Call" AS "Total_Call","Total_Missed" AS "Total_Missed","IVR_Missed" AS "IVR_Missed","Queue_Missed" AS "Queue_Missed","Ans" AS "Ans","IVR_Missed%" AS "IVR_Missed%","Queue_Missed%" AS "Queue_Missed%","Ans%" AS "Ans%", "Total_Missed%" AS "Total_Missed%" Union ALL ( '; SET @GroupRec='Group by cdr.did '; SET @ExterWhereCond=' AND call_type="Incoming" AND Ifnull(cdr.did,"")!="" '; SET @TableVar= CONCAT( @LinkedIdLastData ,@ExterWhereCond) ; SET @OrderVar =CONCAT(Case when ifnull(Orderby,'')!='' then CONCAT(' Order By ',Orderby) ELSE ' ' end,Case when ifnull(PageLimit,'')!='' then PageLimit ELSE '' END); END if ; If ReportType='Campaign Wise Summary' Then If IsCount=1 then SET @SelectVar ='SELECT Count(Distinct campaign.name) LogCount'; ELSE SET @SelectVar =' SELECT CONCAT(campaign.name,"-",campaign.san_acd_code) "Campaign_Name",sum(Case when acd_time IS NULL AND call_type="Outgoing" then 1 ELSE 0 END )Total_Dialed, SUM(Case when acd_time IS NULL AND call_type="Outgoing" AND connected="1" then 1 ELSE 0 END )Total_Connected_Dialed, SEC_TO_TIME(SUM(Case when acd_time IS NULL AND call_type="Outgoing" AND connected="1" then TIMESTAMPDIFF(SECOND,exten_ans_time,end_time) ELSE 0 END ))Connected_Dialed_Time, SUM(Case when acd_time IS NOT NULL then 1 ELSE 0 END )Total_Offered_Call, SUM(case when acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL then 1 ELSE 0 END) Total_Answer_Call, SEC_TO_TIME(SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL Then TIMESTAMPDIFF(SECOND,acd_time,end_time) ELSE 0 END))Answer_Call_Time, SEC_TO_TIME(round((sum(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL Then TIMESTAMPDIFF(SECOND,acd_time,end_time) ELSE 0 END))/sum(Case when acd_time IS NOT NULL then 1 ELSE 0 END ),0))AS "AVG_Answer_Call_Time", SEC_TO_TIME((Min(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL Then TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) ELSE 0 END)))AS "Min_Answer_Call_Time", SEC_TO_TIME((Max(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL Then TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) ELSE 0 END)))AS "Max_Answer_Call_Time" , ROUND(IFNULL((Sum(case when acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL then 1 ELSE 0 END)*100.0)/NULLIF(sum(Case when acd_time IS NOT NULL then 1 ELSE 0 END ),0),0),2) "Answer_Call_%", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time)<=10 Then 1 ELSE 0 END) AS "Answer_STE_1_With_IN_10_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) BETWEEN 11 AND 20 Then 1 ELSE 0 END) AS "Answer_STE_2_Between_10_20_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) BETWEEN 21 AND 30 Then 1 ELSE 0 END) AS "Answer_STE_3_Between_20_30_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) BETWEEN 31 AND 40 Then 1 ELSE 0 END) AS "Answer_STE_4_Between_30_40_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) BETWEEN 41 AND 50 Then 1 ELSE 0 END) AS "Answer_STE_5_Between_40_50_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) BETWEEN 51 AND 60 Then 1 ELSE 0 END) AS "Answer_STE_6_Between_50_60_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) >60 Then 1 ELSE 0 END) AS "Answer_STE_7_Greater_Then_60_Sec" , SUM(case when acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL then 1 ELSE 0 END)Total_Abndon_Call, SEC_TO_TIME(ifnull(round((sum(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL Then TIMESTAMPDIFF(SECOND,acd_time,end_time) ELSE 0 END))/NULLIF(sum(Case when acd_time IS NOT NULL then 1 ELSE 0 END ),0),0),0))AS "AVG_Abndon_Call_Time", SEC_TO_TIME((Min(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL Then TIMESTAMPDIFF(SECOND,acd_time,end_time) ELSE 0 END)))AS "Min_Abndon_Call_Time", SEC_TO_TIME((Max(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL Then TIMESTAMPDIFF(SECOND,acd_time,end_time) ELSE 0 END)))AS "Max_Abndon_Call_Time" , ROUND(IFNULL((Sum(case when acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL then 1 ELSE 0 END)*100.0)/nullif(sum(Case when acd_time IS NOT NULL then 1 ELSE 0 END ),0),0),2) "Abndon_Call%", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time)<=10 Then 1 ELSE 0 END) AS "Abndon_STE_1_With_IN_10_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) BETWEEN 11 AND 20 Then 1 ELSE 0 END) AS "Abndon_STE_2_Between_10_20_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) BETWEEN 21 AND 30 Then 1 ELSE 0 END) AS "Abndon_STE_3_Between_20_30_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) BETWEEN 31 AND 40 Then 1 ELSE 0 END) AS "Abndon_STE_4_Between_30_40_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) BETWEEN 41 AND 50 Then 1 ELSE 0 END) AS "Abndon_STE_5_Between_40_50_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) BETWEEN 51 AND 60 Then 1 ELSE 0 END) AS "Abndon_STE_6_Between_50_60_Sec", SUM(Case When acd_time IS NOT NULL AND cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) >60 Then 1 ELSE 0 END) AS "Abndon_STE_7_Greater_Then_60_Sec"'; END If; SET @SelectVar1 =' SELECT "Campaign_Name" AS "Campaign_Name","Total_Dialed" AS "Total_Dialed","Total_Connected_Dialed" AS "Total_Connected_Dialed","Connected_Dialed_Time" AS "Connected_Dialed_Time","Total_Offered_Call" AS "Total_Offered_Call","Total_Answer_Call" AS "Total_Answer_Call","Answer_Call_Time" AS "Answer_Call_Time","AVG_Answer_Call_Time" AS "AVG_Answer_Call_Time","Min_Answer_Call_Time" AS "Min_Answer_Call_Time","Max_Answer_Call_Time" AS "Max_Answer_Call_Time", "Answer_Call_%" AS "Answer_Call_%","Answer_STE_1_With_IN_10_Sec" AS "Answer_STE_1_With_IN_10_Sec", "Answer_STE_2_Between_10_20_Sec" AS "Answer_STE_2_Between_10_20_Sec", "Answer_STE_3_Between_20_30_Sec" AS "Answer_STE_3_Between_20_30_Sec", "Answer_STE_4_Between_30_40_Sec" AS "Answer_STE_4_Between_30_40_Sec","Answer_STE_5_Between_40_50_Sec" AS "Answer_STE_5_Between_40_50_Sec", "Answer_STE_6_Between_50_60_Sec" AS "Answer_STE_6_Between_50_60_Sec", "Answer_STE_7_Greater_Then_60_Sec" AS "Answer_STE_7_Greater_Then_60_Sec" , "Total_Abndon_Call" AS "Total_Abndon_Call","AVG_Abndon_Call_Time" AS "AVG_Abndon_Call_Time","Min_Abndon_Call_Time" AS "Min_Abndon_Call_Time","Max_Abndon_Call_Time"AS "Max_Abndon_Call_Time" ,"Abndon_Call%" AS "Abndon_Call%","Abndon_STE_1_With_IN_10_Sec" AS "Abndon_STE_1_With_IN_10_Sec","Abndon_STE_2_Between_10_20_Sec" AS "Abndon_STE_2_Between_10_20_Sec","Abndon_STE_3_Between_20_30_Sec" AS "Abndon_STE_3_Between_20_30_Sec","Abndon_STE_4_Between_30_40_Sec" AS "Abndon_STE_4_Between_30_40_Sec","Abndon_STE_5_Between_40_50_Sec" AS "Abndon_STE_5_Between_40_50_Sec","Abndon_STE_6_Between_50_60_Sec" AS "Abndon_STE_6_Between_50_60_Sec","Abndon_STE_7_Greater_Then_60_Sec" AS "Abndon_STE_7_Greater_Then_60_Sec" Union ALL ( '; SET @GroupRec=' Group by CONCAT(campaign.name,"-",campaign.san_acd_code) '; SET @ExterWhereCond=' AND cdr.campaign_id IS NOT NULL '; SET @TableVar= CONCAT( @LinkedIdLastData ,@ExterWhereCond) ; SET @OrderVar =CONCAT(Case when ifnull(Orderby,'')!='' then CONCAT(' Order By ',Orderby) ELSE ' ' end,Case when ifnull(PageLimit,'')!='' then PageLimit ELSE '' END); END If; If ReportType='Group Answer Summary' Then If IsCount=1 then SET @SelectVar ='SELECT Count(Distinct campaign.name) LogCount'; ELSE SET @SelectVar =' SELECT concat(campaign.name,"-",cdr.acd) "Campaign_Name",COUNT(1) "Total_Offered_Call", sum(Case When cdr.exten_ans_time IS NOT NULL Then 1 ELSE 0 END)AS "Total_Ans_Call", sum(Case When cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time)<=10 Then 1 ELSE 0 END) AS "STE_1_With_IN_10_Sec", sum(Case When cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) BETWEEN 11 AND 20 Then 1 ELSE 0 END) AS "STE_2_Between_10_20_Sec", sum(Case When cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) BETWEEN 21 AND 30 Then 1 ELSE 0 END) AS "STE_3_Between_20_30_Sec", sum(Case When cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) BETWEEN 31 AND 40 Then 1 ELSE 0 END) AS "STE_4_Between_30_40_Sec", sum(Case When cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) BETWEEN 41 AND 50 Then 1 ELSE 0 END) AS "STE_5_Between_40_50_Sec", sum(Case When cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) BETWEEN 51 AND 60 Then 1 ELSE 0 END) AS "STE_6_Between_50_60_Sec", sum(Case When cdr.exten_ans_time IS NOT NULL AND TIMESTAMPDIFF(SECOND,acd_time,exten_ans_time) >60 Then 1 ELSE 0 END) AS "STE_7_Greater_Then_60_Sec" '; END If; SET @SelectVar1 =' SELECT "Campaign_Name" as "Campaign_Name","Total_Offered_Call" AS "Total_Offered_Call","Total_Ans_Call" AS "Total_Ans_Call","STE_1_With_IN_10_Sec" AS "STE_1_With_IN_10_Sec","STE_2_Between_10_20_Sec" AS "STE_2_Between_10_20_Sec", "STE_3_Between_20_30_Sec" AS "STE_3_Between_20_30_Sec", "STE_4_Between_30_40_Sec" AS "STE_4_Between_30_40_Sec","STE_5_Between_40_50_Sec" AS "STE_5_Between_40_50_Sec","STE_6_Between_50_60_Sec" AS "STE_6_Between_50_60_Sec","STE_7_Greater_Then_60_Sec" AS "STE_7_Greater_Then_60_Sec" Union ALL ( '; SET @GroupRec='Group by concat(campaign.name,"-",cdr.acd) '; SET @ExterWhereCond=' AND call_type="Incoming" AND ifnull(Ifnull(campaign.name,cdr.acd),"")!="" AND acd_time IS NOT NULL AND acd_time!="0000-00-00 00:00:00" '; SET @TableVar= CONCAT( @LinkedIdLastData ,@ExterWhereCond) ; SET @OrderVar =CONCAT(Case when ifnull(Orderby,'')!='' then CONCAT(' Order By ',Orderby) ELSE ' ' end,Case when ifnull(PageLimit,'')!='' then PageLimit ELSE '' END); END If; If ReportType='Group Missed Summary' Then If IsCount=1 then SET @SelectVar ='SELECT Count(Distinct campaign.name) LogCount'; ELSE SET @SelectVar =' SELECT concat(campaign.name,"-",cdr.acd) "Campaign_Name",COUNT(1) "Total_Offered_Call",sum(Case When cdr.exten_ans_time IS NULL Then 1 ELSE 0 END)AS "Total_MIssed_Call",round((sum(Case When cdr.exten_ans_time IS NULL Then 1 ELSE 0 END)*100.0)/COUNT(1),2)AS "Total_MIssed_Call%",sum(Case When cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time)<=10 Then 1 ELSE 0 END) AS "STE_1_With_IN_10_Sec",sum(Case When cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) BETWEEN 11 AND 20 Then 1 ELSE 0 END) AS "STE_2_Between_10_20_Sec",sum(Case When cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) BETWEEN 21 AND 30 Then 1 ELSE 0 END) AS "STE_3_Between_20_30_Sec",sum(Case When cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) BETWEEN 31 AND 40 Then 1 ELSE 0 END) AS "STE_4_Between_30_40_Sec",sum(Case When cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) BETWEEN 41 AND 50 Then 1 ELSE 0 END) AS "STE_5_Between_40_50_Sec", sum(Case When cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) BETWEEN 51 AND 60 Then 1 ELSE 0 END) AS "STE_6_Between_50_60_Sec",sum(Case When cdr.exten_ans_time IS NULL AND TIMESTAMPDIFF(SECOND,acd_time,end_time) >60 Then 1 ELSE 0 END) AS "STE_7_Greater_Then_60_Sec",SEC_TO_TIME(round((sum(Case When cdr.exten_ans_time IS NULL Then TIMESTAMPDIFF(SECOND,acd_time,end_time) ELSE 0 END))/COUNT(1),0))AS "AVG_Missed_Call_Time",SEC_TO_TIME((Min(Case When cdr.exten_ans_time IS NULL Then TIMESTAMPDIFF(SECOND,acd_time,end_time) ELSE 0 END)))AS "Min_Missed_Call_Time",SEC_TO_TIME((Max(Case When cdr.exten_ans_time IS NULL Then TIMESTAMPDIFF(SECOND,acd_time,end_time) ELSE 0 END)))AS "Max_Missed_Call_Time" '; END If; SET @SelectVar1 =' SELECT "Campaign_Name" as "Campaign_Name","Total_Call_Offered" AS "Total_Call_Offered","Total_MIssed_Call" AS "Total_MIssed_Call","Total_MIssed_Call%" AS "Total_MIssed_Call%","STE_1_With_IN_10_Sec" AS "STE_1_With_IN_10_Sec","STE_2_Between_10_20_Sec" AS "STE_2_Between_10_20_Sec", "STE_3_Between_20_30_Sec" AS "STE_3_Between_20_30_Sec", "STE_4_Between_30_40_Sec" AS "STE_4_Between_30_40_Sec","STE_5_Between_40_50_Sec" AS "STE_5_Between_40_50_Sec","STE_6_Between_50_60_Sec" AS "STE_6_Between_50_60_Sec","STE_7_Greater_Then_60_Sec" AS "STE_7_Greater_Then_60_Sec","AVG_Missed_Call_Time" AS "AVG_Missed_Call_Time","Min_Missed_Call_Time" AS "Min_Missed_Call_Time","Max_Missed_Call_Time" AS "Max_Missed_Call_Time" Union ALL ( '; SET @GroupRec='Group by concat(campaign.name,"-",cdr.acd) '; SET @ExterWhereCond=' AND call_type="Incoming" AND ifnull(Ifnull(campaign.name,cdr.acd),"")!="" AND acd_time IS NOT NULL AND acd_time!="0000-00-00 00:00:00" '; SET @TableVar= CONCAT( @LinkedIdLastData ,@ExterWhereCond) ; SET @OrderVar =CONCAT(Case when ifnull(Orderby,'')!='' then CONCAT(' Order By ',Orderby) ELSE ' ' end,Case when ifnull(PageLimit,'')!='' then PageLimit ELSE '' END); END If; If ReportType='Trunk Wise Summary' Then If IsCount=1 then SET @SelectVar ='SELECT Count(Distinct trunk.display_name) LogCount'; ELSE SET @SelectVar =' SELECT trunk.display_name`Trunk_Name`,sum(case when call_type="Incoming" and cdr.acd_time IS NOT NULL and cdr.acd!="" then 1 ELSE 0 END)Offered_Call,sum(case when call_type="Incoming" and cdr.acd_time IS NOT NULL and cdr.exten_ans_time IS NOT NULL and cdr.acd!="" and cdr.connected="1" then 1 ELSE 0 END) Answered_Call,sum(case when call_type="Incoming" and cdr.acd_time IS NOT NULL and cdr.acd!="" and cdr.connected="0" then 1 ELSE 0 END) Abandon_Call,sum(case when call_type="Incoming" and cdr.acd_time IS NOT NULL and cdr.exten_ans_time IS NOT NULL and cdr.acd!="" and cdr.connected="1" then TIMESTAMPDIFF(Minute, cdr.exten_ans_time, end_time) ELSE 0 END) Inbound_Minute,sum(case when call_type="outgoing" and (cdr.acd_time IS NULL or(cdr.acd_time IS NOT NULL and cdr.acd!="")) then 1 ELSE 0 END) Outbound_Call,sum(case when call_type="outgoing" and ((cdr.acd_time IS NULL and cdr.exten_ans_time IS NOT NULL and cdr.exten_ans_time IS NOT NULL)or(cdr.acd_time IS NOT NULL and cdr.acd!="" and cdr.exten_ans_time IS NOT NULL)) and cdr.connected="1" then 1 ELSE 0 END) Connected_Call,sum(case when call_type="outgoing" and ((cdr.acd_time IS NULL and cdr.exten_ans_time IS NOT NULL and cdr.exten_ans_time IS NOT NULL)or(cdr.acd_time IS NOT NULL and cdr.acd!="" and cdr.exten_ans_time IS NOT NULL)) and cdr.connected="1" then TIMESTAMPDIFF(Minute, cdr.exten_ans_time, end_time) ELSE 0 END) Outbound_Minute,Count(distinct case when ifnull(cdr.caller_id,"")!="" then cdr.caller_id ELSE NULL END) Unique_Call,Count(distinct case when ifnull(cdr.caller_id,"")!="" and connected="1" then cdr.caller_id ELSE NULL END) Unique_Connected_Call'; END If; SET @SelectVar1 =' SELECT "Trunk_Name" as "Trunk_Name","Offered_Call" AS "Offered_Call","Answered_Call" AS "Answered_Call","Abandon_Call" AS "Abandon_Call","Inbound_Minute" AS"Inbound_Minute","Outbound_Call" AS "Outbound_Call","Connected_Call" AS "Connected_Call","Outbound_Minute" AS "Outbound_Minute","Unique_Call" AS "Unique_Call","Unique_Connected_Call" AS "Unique_Connected_Call" Union ALL ( '; SET @GroupRec='Group by trunk.display_name '; SET @ExterWhereCond=' AND ifnull(cdr.trunk_channel,"")!="" '; SET @TableVar= CONCAT( @LinkedIdLastData ,@ExterWhereCond) ; SET @OrderVar =CONCAT(Case when ifnull(Orderby,'')!='' then CONCAT(' Order By ',Orderby) ELSE ' ' end,Case when ifnull(PageLimit,'')!='' then PageLimit ELSE '' END); END If; If ReportType='Missed Call Summary' Then SET @PId=NULL; SET @getproc=CONCAT(' Select MAX( cdr.Process_id) into @PId FROM ',@CdrTable,' cdr \r\n\t\t\t\t\t\t\t \tLEFT JOIN campaign ON cdr.campaign_id=campaign.id\r\n\t\t\t\t\t\t\t\t\t\twhere 1=1',case when ifnull(Fromdate,'') then CONCAT(' and Start_time>="',FromDate,'" ') ELSE '' END,case when ifnull(ToDate,'') then CONCAT(' and Start_time<="',ToDate,'" ') ELSE ''END,' ',ifnull(cond,'')); PREPARE getprocstmt FROM @getproc; execute getprocstmt; DEALLOCATE PREPARE getprocstmt; if IFNULL(@PId,0)>0 then If IsCount=1 then SET @SelectVar ='SELECT Count(1) LogCount'; ELSE SET @SelectVar =' SELECT cdr.caller_id Phone_No, cdr.end_time Miss_Call_time,last_time Called_Time,case when LastCallLog.log_phone_no IS null then "Pending" ELSE "Dialed" END Call_Status,PL.agent_name Agent_Name,campaign.name Campaign_Name'; END If; SET @SelectVar1 =' SELECT "Phone_No" as "Phone_No","Miss_Call_time" AS "Miss_Call_time","Called_Time" AS "Called_Time","Call_Status" AS "Call_Status","Agent_Name" AS "Agent_Name","Campaign_Name" AS "Campaign_Name" Union ALL ( '; SET @GroupRec=' '; SET @ExterWhereCond=' AND char_length(cdr.caller_id)>=10 '; SET @TableVar= CONCAT(' FROM (SELECT * from ',@CdrTable,' cdr WHERE 1=1 AND cdr.end_call=1 AND (ifnull(cdr.acd,"")!="") AND cdr.call_type="incoming" AND cdr.exten_ring_time IS null ',case when ifnull(Fromdate,'') then CONCAT(' and Start_time>="',FromDate,'" ') ELSE '' END,case when ifnull(ToDate,'') then CONCAT(' and Start_time<="',ToDate,'" ') ELSE ''END,' ',IFNULL(@ExterWhereCond,''),' ',ifnull(cond,''), ' )cdr \r\n\t\t\t\t\t\t\t \tLEFT JOIN campaign ON cdr.campaign_id=campaign.id \r\n\t\t\t\t\t\t\t\t\t\t LEFT JOIN (SELECT log_phone_no,MAX(id)last_call_id,MAX(Start_time) last_time\r\n\t\t\t\t\t\t\t\t\t\t\t\t FROM process_table_log_',@PId,' GROUP BY log_phone_no)LastCallLog \r\n\t\t\t\t\t\t\t\t\t\t\t\t ON right(cdr.caller_id,10)=LastCallLog.log_phone_no AND LastCallLog.last_time>cdr.end_time\r\n\t\t\t\t\t\t\t\t\t\t\t\t LEFT JOIN process_table_log_',@PId,' PL on PL.id=LastCallLog.last_call_id WHERE 1=1 ') ; SET @OrderVar =CONCAT(Case when ifnull(Orderby,'')!='' then CONCAT(' Order By ',Orderby) ELSE ' Order By Miss_Call_time desc' end,Case when ifnull(PageLimit,'')!='' then PageLimit ELSE '' END); ELSE SET @SelectVar='select * from(SELECT "" as "Phone_No","" AS "Miss_Call_time","" AS "Called_Time","" AS "Call_Status")test where 1=2 '; SET @SelectVar1 =' SELECT "Phone_No" as "Phone_No","Miss_Call_time" AS "Miss_Call_time","Called_Time" AS "Called_Time","Call_Status" AS "Call_Status" Union ALL ( '; SET @GroupRec=' '; SET @ExterWhereCond=''; SET @TableVar= ''; SET @OrderVar =''; END IF; END If; IF ifnull(IsCount,0)=0 then SET @query1=concat(IFNULL(@SelectVar,''),ifnull(@TableVar,''),IFNULL(@GroupRec,''),IFNULL(@OrderVar,'')); IF IFNULL(ReportFIlePath,'')!='' AND IFNULL(IsExportReport,'0')='1' Then SET @query1=concat(@SelectVar1,@query1,' into OUTFILE ''',ReportFIlePath,''' FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"'' LINES TERMINATED BY ''\n'' );'); END if; ELSE SET @query1=concat(IFNULL(@SelectVar,''),ifnull(@TableVar,'')); END if; END IF; #SELECT @query1; PREPARE stmt3 FROM @query1; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; IF IFNULL(ReportFIlePath,'')!='' AND IFNULL(IsExportReport,'0')='1' Then SELECT ReportFIlePath; END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.get_file_data DROP PROCEDURE IF EXISTS `get_file_data`; DELIMITER // CREATE PROCEDURE `get_file_data`( IN `pid` INT, IN `cid` INT, IN `fileid` INT, IN `recordtype` VARCHAR(30), IN `isexport` INT, IN `ReportFIlePath` VARCHAR(100) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; SET SESSION group_concat_max_len = 1000000; SET @fieldlist=NULL,@fieldlist1=NULL,@mastertblname=NULL,@logtblname=NULL; SELECT GROUP_CONCAT(CONCAT('IFNULL(REPLACE(REPLACE(REPLACE(',if(formbuilder_field.create_log=1,'pl.','pm.'),field_name,',CHAR(10),''''),CHAR(13),''''),CHAR(34),''''),"")',field_name) ORDER BY formbuilder_field.create_log ASC, formbuilder_field.field_id),GROUP_CONCAT(CONCAT('"',field_name,'"') ORDER BY formbuilder_field.create_log ASC, formbuilder_field.field_id),formbuilder.table_name,formbuilder.log_table_name INTO @fieldlist,@fieldlist1,@mastertblname1,@logtblname FROM formbuilder_field JOIN formbuilder ON formbuilder_field.form_id=formbuilder.id WHERE process_id=pid AND formbuilder.form_type='Master' GROUP BY formbuilder.table_name,formbuilder.log_table_name; SET @getfiledata=CONCAT('SELECT master_id Master_Id,IM.file_name File_Name,IM.Upload_date File_Insert_Date,cm.CallAgainTime Call_Again_Time,agent.name agent_name ',case when IFNULL(@fieldlist,'')='' then '' ELSE ',' END,IFNULL(@fieldlist,''),',logcount No_Of_Attempts FROM ',@mastertblname1,' pm JOIN campaign_mapping_',pid,' cm ON pm.id=cm.master_id JOIN importmaster_',pid,' IM ON cm.FileId=IM.id LEFT JOIN (select max(id)lastlog,count(1)logcount,parent_id,camp_map_id from ',@logtblname,' pl group by parent_id,camp_map_id )lpl on cm.map_id=lpl.camp_map_id AND cm.LastLogId=lpl.lastlog LEFT JOIN ',@logtblname,' pl on lpl.lastlog=pl.id left join agent on cm.agent_id=agent.id where 1=1 ',case when ifnull(cid,0)>0 then CONCAT(' and cm.campaign_id="',cid,'" ') ELSE "" END ,' and cm.FileId="',fileid,'" ', case when ifnull(recordtype,"")="DND" then ' AND (DoNotCall = "1" or isDNC="1")' when ifnull(recordtype,"")="DialedNumber" then ' AND (Dialed = 1 OR (Dialed = 0 AND CallAgainTime IS NOT NULL)) ' when ifnull(recordtype,"")="Agent Disposed" then ' AND ( dialed=1 AND dialed IS NOT NULL AND (cm.disposition IS NOT NULL AND cm.disposition!="") ) ' when ifnull(recordtype,"")="System Disposed" then ' AND ( dialed=1 AND dialed IS NOT NULL AND (cm.disposition IS NULL OR cm.disposition="") )' when ifnull(recordtype,"")="NotDialed" then ' AND (Dialed = 0 /*AND CallAgainTime IS NULL*/) AND (DoNotCall = "1" AND isDNC="1") ' when ifnull(recordtype,"")="Freash Number" then ' AND (Dialed = 0 AND CallAgainTime IS NULL AND (cm.Disposition is NULL OR cm.Disposition="") AND (cm.HangupCause IS NULL OR cm.HangupCause="")) AND (ifnull(DoNotCall,0) = "0" AND ifnull(isDNC,0)="0" and ifnull(StopCalling,0) =0) ' when ifnull(recordtype,"")="Resheduled" then ' AND (Dialed = 0 AND CallAgainTime IS NOT NULL AND LastCallTime IS NOT NULL) AND (DoNotCall = "1" AND isDNC="1")' when ifnull(recordtype,"")="Call Again" then ' AND (Dialed = 0 AND CallAgainTime IS NULL AND ((cm.Disposition is NOT NULL and cm.Disposition!="") OR cm.HangupCause IS NOT NULL)) /*AND (DoNotCall = "1" AND isDNC="1")*/ AND (ifnull(DoNotCall,0)=0 or ifnull(isDNC,0)=0)' when ifnull(recordtype,"")="stop calling" then ' AND (StopCalling =1) ' ELSE ' AND 1=1 ' END, ' '); if ifnull(isexport,0)=1 then SET @getfiledata= CONCAT(' select "Master_Id","file_name","File_InsertDate","Call_Again_Time","agent_name" ',case when IFNULL(@fieldlist1,"")="" then '' ELSE ',' END,IFNULL(@fieldlist1,""),',"No_Of_Attempts" union All ' ,@getfiledata, ' into OUTFILE "',ReportFIlePath,'" FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY ''"'' LINES TERMINATED BY "\n" ;'); end if; PREPARE getfiledata1 FROM @getfiledata; EXECUTE getfiledata1; DEALLOCATE PREPARE getfiledata1; if IFNULL(ReportFIlePath,'')!='' AND ifnull(isexport,0)=1 then SELECT ReportFIlePath; END if; SELECT @getfiledata; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.get_file_data_count DROP PROCEDURE IF EXISTS `get_file_data_count`; DELIMITER // CREATE PROCEDURE `get_file_data_count`( IN `pid` INT, IN `campid` VARCHAR(10), IN `filestatus` VARCHAR(1), IN `file_id` VARCHAR(10), IN `datalimit` VARCHAR(10) ) BEGIN SET @getcountdata=CONCAT('SELECT `impo`.id,`impo`.file_name,`campaign`.name campaign_name,`impo`.active_file,`impo`.priority,`impo`.mark_for_deletion,`impo`.retain_callback,`impo`.valid_upto,`impo`.last_dnd_update, SUM(CASE WHEN (Dialed = 1 OR (Dialed = 0 AND ifnull(CallAgainTime,"")!="")) THEN 1 ELSE 0 END) as dialed_no, SUM(CASE WHEN (Dialed = 0 /*AND ifnull(CallAgainTime,"")=""*/) AND (ifnull(DoNotCall,0)=0 AND ifnull(isDNC,0)=0) THEN 1 ELSE 0 END) as not_dialed_no, sum(1) as total, SUM(CASE WHEN (Dialed = 1 AND ifnull(Disposition,"")!="") THEN 1 ELSE 0 END) AgentDisposed, SUM(CASE WHEN (Dialed = 1 AND ifnull(Disposition,"")="" ) THEN 1 ELSE 0 END) SystemDisposed, SUM(CASE WHEN (Dialed = 0 AND ifnull(CallAgainTime,"")="" AND ifnull(Disposition,"")="" AND ifnull(HangupCause,"")="") AND (ifnull(DoNotCall,0)=0 AND ifnull(isDNC,0)=0 and ifnull(StopCalling,0) =0) THEN 1 ELSE 0 END) FreshNumber, SUM(CASE WHEN (StopCalling =1 ) THEN 1 ELSE 0 END) stopcalling, SUM(CASE WHEN (Dialed = 0 AND ifnull(CallAgainTime,"")!="" AND ifnull(LastCallTime,"")!="") AND (ifnull(DoNotCall,0)=0 AND ifnull(isDNC,0)=0) THEN 1 ELSE 0 END) Resheduled, SUM(CASE WHEN (Dialed = 0 AND ifnull(CallAgainTime,"")="" AND (ifnull(Disposition,"")!="" OR ifnull(HangupCause,"")!="")) AND (ifnull(DoNotCall,0)=0 or ifnull(isDNC,0)=0) THEN 1 ELSE 0 END) CallAgain, SUM(CASE WHEN (ifnull(DoNotCall,0) = 1 or ifnull(isDNC,0)=1) THEN 1 ELSE 0 END) DND FROM campaign_mapping_',pid,' `cam_map` JOIN process_table_',pid,' `pm` ON `pm`.`id`=`cam_map`.`master_id` JOIN importmaster_',pid,' `impo` ON `cam_map`.`FileId`=`impo`.`id` left join campaign on `cam_map`.campaign_id=campaign.id WHERE `impo`.id != 1 AND ifnull(`impo`.`mark_for_deletion`,0) = "0" ',case when campid='' then '' ELSE CONCAT(' AND cam_map.campaign_id= ',campid) END,case when filestatus='' then '' ELSE CONCAT(' AND impo.active_file= ',filestatus) END,case when file_id='' then '' ELSE CONCAT(' AND cam_map.fileid= ',file_id) END,' GROUP BY `impo`.id,`impo`.file_name,`campaign`.name,`impo`.active_file,`impo`.priority,`impo`.mark_for_deletion,`impo`.retain_callback,`impo`.valid_upto,`impo`.last_dnd_update ORDER BY `impo`.id desc ',case when datalimit='' then '' else CONCAT('LIMIT ',datalimit) END); #SELECT @getcountdata; PREPARE getcountdata FROM @getcountdata; EXECUTE getcountdata; DEALLOCATE PREPARE getcountdata; END// DELIMITER ; -- Dumping structure for procedure version1.get_primary_field_velidation DROP PROCEDURE IF EXISTS `get_primary_field_velidation`; DELIMITER // CREATE PROCEDURE `get_primary_field_velidation`( IN `pid` INT, IN `col_name` VARCHAR(100) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @ERRNO = MYSQL_ERRNO, @MESSAGE_TEXT = MESSAGE_TEXT; SELECT NULL can_create_primary_field,NULL primary_field_name,CONCAT('MySQL ERROR: ', @ERRNO, ': ', @MESSAGE_TEXT) AS Error_Message; ROLLBACK; END; START TRANSACTION; SET @uniq_field=NULL; SET @cnt=NULL; SET @QUERY1=CONCAT('select count(1) cnt into @cnt from process_table_',pid,' group by ',col_name,' having count(1)>1 limit 1 ;'); PREPARE stmtgetprimaryfieldvelidation1 FROM @QUERY1; EXECUTE stmtgetprimaryfieldvelidation1; DEALLOCATE PREPARE stmtgetprimaryfieldvelidation1; SET @QUERY2=CONCAT('SELECT column_name into @uniq_field FROM information_schema.`COLUMNS` WHERE TABLE_NAME="process_table_',pid,'" AND Table_schema=DATABASE() AND column_key="UNI" AND Is_nullable="No"; '); PREPARE stmtgetprimaryfieldvelidation2 FROM @QUERY2; EXECUTE stmtgetprimaryfieldvelidation2; DEALLOCATE PREPARE stmtgetprimaryfieldvelidation2; SET @QUERY3=CONCAT('select 1 cnt into @IsNull from process_table_',pid,' where ',col_name,' IS NULL limit 1 ;'); PREPARE stmtgetprimaryfieldvelidation1 FROM @QUERY1; EXECUTE stmtgetprimaryfieldvelidation1; DEALLOCATE PREPARE stmtgetprimaryfieldvelidation1; SELECT if(Ifnull(@cnt,0)>0 or IFNULL(@uniq_field,'')!='' OR @IsNull=1,'0','1')can_create_primary_field,@uniq_field primary_field_name,col_name RequestedColumn,case when @cnt>1 then 'Duplicate Value' when @uniq_field=col_name then 'Primary Field Already Exists' when @IsNull=1 then 'Column Have Null Value, Remove Null First.' else NULL end Error_Message; COMMIT; END// DELIMITER ; -- Dumping structure for procedure version1.purgeDatabaseReport DROP PROCEDURE IF EXISTS `purgeDatabaseReport`; DELIMITER // CREATE PROCEDURE `purgeDatabaseReport`( IN `fromdate` VARCHAR(25), IN `todate` VARCHAR(25) ) BEGIN SET SESSION group_concat_max_len = 10000000; SET @selectquery = CONCAT('SELECT DATE(api_hit_time)Purge_Date,api_hit_time Purge_Start_Time,TIMEDIFF(MAX(api_complete_time),MIN(api_hit_time)) Overall_Time, TIMEDIFF(MAX(proc_complete_time),MIN(proc_hit_time)) Purge_Time, api_hit_status Purge_Status, ifnull(db_backup_status,"No Backup Taken") Backup_Status,db_backup_path, TIMEDIFF(MAX(db_backup_complete),MIN(db_backup_start)) Backup_Time, TIMEDIFF(MAX(optimize_complete_time),MIN(optimize_hit_time)) Shrink_Time, ip IP_Address,CONCAT(''{"data":['',GROUP_CONCAT(CONCAT(''{"process_name":"'',ifnull(p.name,''NA''),''","client_name":"'',ifnull(c.name,''NA''),''","purged_before":"'',ifnull(trim(SUBSTRING_INDEX(reverse(SUBSTRING_INDEX(reverse(client_process_date),'','',2)),'','',1)),''NA''),''"}'')),'']}'') Process_Client_Name '); SET @fromquery = CONCAT(' FROM purge_data_logs pg left JOIN process p ON pg.process_id=p.id left JOIN client_master c ON pg.client_id=c.id WHERE api_hit_time>="',fromdate,' 00:00:00" AND api_hit_time<="',todate,' 23:59:59" GROUP BY DATE(api_hit_time),api_hit_time,api_hit_status,db_backup_status,db_backup_path,ip '); SET @fullquery = CONCAT(@selectquery, ' ' ,@fromquery); PREPARE stmtfullquery FROM @fullquery; EXECUTE stmtfullquery; DEALLOCATE PREPARE stmtfullquery; END// DELIMITER ; -- Dumping structure for procedure version1.purgeDataClientProcessWise DROP PROCEDURE IF EXISTS `purgeDataClientProcessWise`; DELIMITER // CREATE PROCEDURE `purgeDataClientProcessWise`( IN `clientid` INT, IN `processid` INT, IN `fromdate` VARCHAR(25), IN `todate` VARCHAR(25), IN `tablename` VARCHAR(50) ) BEGIN SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; if(SELECT 1 FROM process WHERE id=processid AND client_id=clientid)=1 then if tablename='agent' then SET @SQLRemoveAgent=CONCAT('delete from agent_login_log_',processid,' where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and start_time>="',fromdate,'"')),' and start_time<="',todate,'"'); PREPARE stmtSQLRemoveAgent FROM @SQLRemoveAgent; EXECUTE stmtSQLRemoveAgent; DEALLOCATE PREPARE stmtSQLRemoveAgent; ELSEIF tablename='cdr' then SET @SQLRemoveCDR=CONCAT('delete from cdr_',clientid,' where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and start_time>="',fromdate,'"')),' and start_time<="',todate,'"'); PREPARE stmtSQLRemoveCDR FROM @SQLRemoveCDR; EXECUTE stmtSQLRemoveCDR; DEALLOCATE PREPARE stmtSQLRemoveCDR; ELSEIF tablename='log' then SET @SQLRemoveLog=CONCAT('delete from process_table_log_',processid,' where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and start_time>="',fromdate,'"')),' and start_time<="',todate,'"'); PREPARE stmtSQLRemoveLog FROM @SQLRemoveLog; EXECUTE stmtSQLRemoveLog; DEALLOCATE PREPARE stmtSQLRemoveLog; ELSEIF tablename='campaign' then SET @SQLCreateLogTemp=CONCAT('create table process_table_log_',processid,'_bak select distinct camp_map_id from process_table_log_',processid); PREPARE stmtSQLCreateLogTemp FROM @SQLCreateLogTemp; EXECUTE stmtSQLCreateLogTemp; DEALLOCATE PREPARE stmtSQLCreateLogTemp; SET @SQLCreateIndexLogTemp=CONCAT('alter table process_table_log_',processid,'_bak add index tempcampmapid(camp_map_id) using btree'); PREPARE stmtSQLCreateIndexLogTemp FROM @SQLCreateIndexLogTemp; EXECUTE stmtSQLCreateIndexLogTemp; DEALLOCATE PREPARE stmtSQLCreateIndexLogTemp; SET @SQLDeleteNULLValue=CONCAT('delete from process_table_log_',processid,'_bak where camp_map_id is null'); PREPARE stmtSQLDeleteNULLValue FROM @SQLDeleteNULLValue; EXECUTE stmtSQLDeleteNULLValue; DEALLOCATE PREPARE stmtSQLDeleteNULLValue; SET @SQLRemoveCampaign=CONCAT('delete from campaign_mapping_',processid,' where 1=1 and lastcalltime is not null ', if(IFNULL(fromdate,'')='','',CONCAT(' and lastcalltime>="',fromdate,'"')),' and lastcalltime<="',todate,'" AND map_id NOT IN (select camp_map_id from process_table_log_',processid,'_bak)'); PREPARE stmtSQLRemoveCampaign FROM @SQLRemoveCampaign; EXECUTE stmtSQLRemoveCampaign; DEALLOCATE PREPARE stmtSQLRemoveCampaign; SET @SQLDropLogTemp=CONCAT('drop table process_table_log_',processid,'_bak'); PREPARE stmtSQLDropLogTemp FROM @SQLDropLogTemp; EXECUTE stmtSQLDropLogTemp; DEALLOCATE PREPARE stmtSQLDropLogTemp; ELSEIF tablename='master' then SET @SQLRemoveMaster=CONCAT('delete from process_table_',processid,' where id not in (select master_id from campaign_mapping_',processid,')'); PREPARE stmtSQLRemoveMaster FROM @SQLRemoveMaster; EXECUTE stmtSQLRemoveMaster; DEALLOCATE PREPARE stmtSQLRemoveMaster; ELSEIF tablename='all' then SET @SQLRemoveAgentAll=CONCAT('delete from agent_login_log_',processid,' where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and start_time>="',fromdate,'"')),' and start_time<="',todate,'"'); PREPARE stmtSQLRemoveAgentAll FROM @SQLRemoveAgentAll; EXECUTE stmtSQLRemoveAgentAll; DEALLOCATE PREPARE stmtSQLRemoveAgentAll; SELECT @SQLRemoveAgentAll; SET @SQLRemoveLogAll=CONCAT('delete from process_table_log_',processid,' where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and start_time>="',fromdate,'"')),' and start_time<="',todate,'"'); PREPARE stmtSQLRemoveLogAll FROM @SQLRemoveLogAll; EXECUTE stmtSQLRemoveLogAll; DEALLOCATE PREPARE stmtSQLRemoveLogAll; SET @SQLDropLogTempIfExistsAll=CONCAT('drop table if exists process_table_log_',processid,'_bak'); PREPARE stmtSQLDropLogTempIfExistsAll FROM @SQLDropLogTempIfExistsAll; EXECUTE stmtSQLDropLogTempIfExistsAll; DEALLOCATE PREPARE stmtSQLDropLogTempIfExistsAll; SET @SQLCreateLogTempAll=CONCAT('create table process_table_log_',processid,'_bak select distinct camp_map_id from process_table_log_',processid); PREPARE stmtSQLCreateLogTempAll FROM @SQLCreateLogTempAll; EXECUTE stmtSQLCreateLogTempAll; DEALLOCATE PREPARE stmtSQLCreateLogTempAll; SET @SQLCreateIndexLogTempAll=CONCAT('alter table process_table_log_',processid,'_bak add index tempcampmapid(camp_map_id) using btree'); PREPARE stmtSQLCreateIndexLogTempAll FROM @SQLCreateIndexLogTempAll; EXECUTE stmtSQLCreateIndexLogTempAll; DEALLOCATE PREPARE stmtSQLCreateIndexLogTempAll; SET @SQLDeleteNULLValueAll=CONCAT('delete from process_table_log_',processid,'_bak where camp_map_id is null'); PREPARE stmtSQLDeleteNULLValueAll FROM @SQLDeleteNULLValueAll; EXECUTE stmtSQLDeleteNULLValueAll; DEALLOCATE PREPARE stmtSQLDeleteNULLValueAll; SET @SQLRemoveCampaignAll=CONCAT('delete from campaign_mapping_',processid,' where 1=1 and lastcalltime is not null ', if(IFNULL(fromdate,'')='','',CONCAT(' and lastcalltime>="',fromdate,'"')),' and lastcalltime<="',todate,'" AND map_id NOT IN (select camp_map_id from process_table_log_',processid,'_bak)'); PREPARE stmtSQLRemoveCampaignAll FROM @SQLRemoveCampaignAll; EXECUTE stmtSQLRemoveCampaignAll; DEALLOCATE PREPARE stmtSQLRemoveCampaignAll; SET @SQLDropLogTempAll=CONCAT('drop table process_table_log_',processid,'_bak'); PREPARE stmtSQLDropLogTempAll FROM @SQLDropLogTempAll; EXECUTE stmtSQLDropLogTempAll; DEALLOCATE PREPARE stmtSQLDropLogTempAll; SET @SQLDropCampTempIfExistsAll=CONCAT('drop table if exists campaign_mapping_',processid,'_bak'); PREPARE stmtSQLDropCampTempIfExistsAll FROM @SQLDropCampTempIfExistsAll; EXECUTE stmtSQLDropCampTempIfExistsAll; DEALLOCATE PREPARE stmtSQLDropCampTempIfExistsAll; SET @SQLCreateCampTempAll=CONCAT('create table campaign_mapping_',processid,'_bak select distinct master_id from campaign_mapping_',processid); PREPARE stmtSQLCreateCampTempAll FROM @SQLCreateCampTempAll; EXECUTE stmtSQLCreateCampTempAll; DEALLOCATE PREPARE stmtSQLCreateCampTempAll; SET @SQLCreateIndexCampTempAll=CONCAT('alter table campaign_mapping_',processid,'_bak add index tempmasterid(master_id) using btree'); PREPARE stmtSQLCreateIndexCampTempAll FROM @SQLCreateIndexCampTempAll; EXECUTE stmtSQLCreateIndexCampTempAll; DEALLOCATE PREPARE stmtSQLCreateIndexCampTempAll; SET @SQLDeleteCampNULLValueAll=CONCAT('delete from campaign_mapping_',processid,'_bak where master_id is null'); PREPARE stmtSQLDeleteCampNULLValueAll FROM @SQLDeleteCampNULLValueAll; EXECUTE stmtSQLDeleteCampNULLValueAll; DEALLOCATE PREPARE stmtSQLDeleteCampNULLValueAll; SET @SQLRemoveMasterAll=CONCAT('delete from process_table_',processid,' where id not in (select master_id from campaign_mapping_',processid,'_bak)'); PREPARE stmtSQLRemoveMasterAll FROM @SQLRemoveMasterAll; EXECUTE stmtSQLRemoveMasterAll; DEALLOCATE PREPARE stmtSQLRemoveMasterAll; SET @SQLDropCampTempAll=CONCAT('drop table campaign_mapping_',processid,'_bak'); PREPARE stmtSQLDropCampTempAll FROM @SQLDropCampTempAll; EXECUTE stmtSQLDropCampTempAll; DEALLOCATE PREPARE stmtSQLDropCampTempAll; SET @SQLRemoveCDRAll=CONCAT('delete from cdr_',clientid,' where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and start_time>="',fromdate,'"')),' and start_time<="',todate,'"'); PREPARE stmtSQLRemoveCDRAll FROM @SQLRemoveCDRAll; EXECUTE stmtSQLRemoveCDRAll; DEALLOCATE PREPARE stmtSQLRemoveCDRAll; SET @SQLRemoveSMSALL=CONCAT('delete from sms_log_',processid,' where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and added_on>="',fromdate,'"')),' and added_on<="',todate,'"'); PREPARE stmtSQLRemoveSMSALL FROM @SQLRemoveSMSALL; EXECUTE stmtSQLRemoveSMSALL; DEALLOCATE PREPARE stmtSQLRemoveSMSALL; SET @SQLRemoveEMAILALL=CONCAT('delete from email_log_',processid,' where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and added_on>="',fromdate,'"')),' and added_on<="',todate,'"'); PREPARE stmtSQLRemoveEMAILALL FROM @SQLRemoveEMAILALL; EXECUTE stmtSQLRemoveEMAILALL; DEALLOCATE PREPARE stmtSQLRemoveEMAILALL; SET @SQLRemoveADALL=CONCAT('delete from after_disposed_',processid,' where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and log_date>="',fromdate,'"')),' and log_date<="',todate,'"'); PREPARE stmtSQLRemoveADALL FROM @SQLRemoveADALL; EXECUTE stmtSQLRemoveADALL; DEALLOCATE PREPARE stmtSQLRemoveADALL; END if; SELECT CONCAT('drop table ',GROUP_CONCAT(TABLE_NAME),';') INTO @drptmptable FROM information_schema.`TABLES` WHERE table_schema=DATABASE() AND (TABLE_NAME LIKE 'import_tmp%' OR TABLE_NAME LIKE 'calllogagent_%' OR TABLE_NAME LIKE 'customreporttbl_%'); if IFNULL(@drptmptable,'')!='' then PREPARE drptmptbl FROM @drptmptable; EXECUTE drptmptbl; DEALLOCATE PREPARE drptmptbl; END if; END if; if (SELECT `fn_table_exists`(DATABASE(), 'api_import_log'))=1 then delete from api_import_log where inserted_at="',fromdate,'"')),' and inserted_at<="',todate,'"'); PREPARE stmtSQLAttendanceDelete FROM @SQLAttendanceDelete; EXECUTE stmtSQLAttendanceDelete; DEALLOCATE PREPARE stmtSQLAttendanceDelete; END if; if (SELECT `fn_table_exists`(DATABASE(), 'conference_call_log'))=1 then SET @SQLConfDelete = CONCAT('delete from conference_call_log where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and current_time>="',fromdate,'"')),' and current_time<="',todate,'"'); PREPARE stmtSQLConfDelete FROM @SQLConfDelete; EXECUTE stmtSQLConfDelete; DEALLOCATE PREPARE stmtSQLConfDelete; END if; if (SELECT `fn_table_exists`(DATABASE(), 'event_log_incoming'))=1 then SET @SQLEvtLogDelete = CONCAT('delete from event_log_incoming where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and created_date>="',fromdate,'"')),' and created_date<="',todate,'"'); PREPARE stmtSQLEvtLogDelete FROM @SQLEvtLogDelete; EXECUTE stmtSQLEvtLogDelete; DEALLOCATE PREPARE stmtSQLEvtLogDelete; END if; if (SELECT `fn_table_exists`(DATABASE(), 'query_profiler'))=1 then delete from query_profiler where created_date < CURDATE(); END if; if (SELECT `fn_table_exists`(DATABASE(), 'software_logs'))=1 then delete from software_logs where time < DATE_ADD(CURDATE(),INTERVAL -2 DAY); END if; if (SELECT `fn_table_exists`(DATABASE(), 'addEmailDataLog'))=1 then delete from addEmailDataLog where log_date < DATE_ADD(CURDATE(),INTERVAL -2 DAY); END if; if (SELECT `fn_table_exists`(DATABASE(), 'aftersaveupdatelog'))=1 then delete from aftersaveupdatelog where insert_date < DATE_ADD(CURDATE(),INTERVAL -2 DAY); END if; if (SELECT `fn_table_exists`(DATABASE(), 'afteruploadupdatelog'))=1 then delete from afteruploadupdatelog where insertdate < DATE_ADD(CURDATE(),INTERVAL -2 DAY); END if; if (SELECT `fn_table_exists`(DATABASE(), 'custom_query_runtime_log'))=1 then delete from custom_query_runtime_log where cr_date < DATE_ADD(CURDATE(),INTERVAL -2 DAY); END if; if (SELECT `fn_table_exists`(DATABASE(), 'updatecrmstatelog'))=1 then delete from updatecrmstatelog where logdate < DATE_ADD(CURDATE(),INTERVAL -2 DAY); END if; if (SELECT `fn_table_exists`(DATABASE(), 'user_log'))=1 then SET @SQLUserLogDelete = CONCAT('delete from user_log where 1=1', if(IFNULL(fromdate,'')='','',CONCAT(' and added_on>="',fromdate,'"')),' and added_on<="',todate,'"'); PREPARE stmtSQLUserLogDelete FROM @SQLUserLogDelete; EXECUTE stmtSQLUserLogDelete; DEALLOCATE PREPARE stmtSQLUserLogDelete; END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END// DELIMITER ; -- Dumping structure for procedure version1.purgeExtraLogTables DROP PROCEDURE IF EXISTS `purgeExtraLogTables`; DELIMITER // CREATE PROCEDURE `purgeExtraLogTables`() BEGIN DECLARE done INT DEFAULT 0; DECLARE tablename VARCHAR(128) DEFAULT NULL; DECLARE columnname VARCHAR(128) DEFAULT NULL; DECLARE noofdays INT DEFAULT 0; DECLARE tab CURSOR FOR SELECT table_name,column_name,no_of_days from purge_data_tables; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN tab; label: LOOP fetch tab INTO tablename,columnname,noofdays; IF done = 1 THEN LEAVE label; END if; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; if (SELECT `fn_table_exists`(DATABASE(), tablename))=1 then SET @PurgeTableName=CONCAT('delete from ',tablename,' where ',columnname,' < DATE_ADD(CURDATE(),INTERVAL -',noofdays,' DAY)'); PREPARE stmtPurgeTableName FROM @PurgeTableName; EXECUTE stmtPurgeTableName; DEALLOCATE PREPARE stmtPurgeTableName; END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; END LOOP; close tab; END// DELIMITER ; -- Dumping structure for procedure version1.purgeOptimizeDatabase DROP PROCEDURE IF EXISTS `purgeOptimizeDatabase`; DELIMITER // CREATE PROCEDURE `purgeOptimizeDatabase`() BEGIN DECLARE done INT DEFAULT 0; DECLARE optimizequery VARCHAR(500) DEFAULT NULL; DECLARE tab CURSOR FOR SELECT CONCAT('OPTIMIZE TABLE ',TABLE_NAME) FROM information_schema.`TABLES` WHERE table_schema=DATABASE(); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN tab; label: LOOP fetch tab INTO optimizequery; IF done = 1 THEN LEAVE label; END if; SET @OptimizeQuery = optimizequery; PREPARE stmtOptimizeQuery FROM @OptimizeQuery; EXECUTE stmtOptimizeQuery; DEALLOCATE PREPARE stmtOptimizeQuery; END LOOP; close tab; END// DELIMITER ; -- Dumping structure for procedure version1.removeCdrTempData DROP PROCEDURE IF EXISTS `removeCdrTempData`; DELIMITER // CREATE PROCEDURE `removeCdrTempData`() BEGIN DELETE FROM cdrtemp WHERE start_time0 AND @isretrun=0 then SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; if crmbreakid IS NOT NULL AND crmbreakid>0 then SELECT agentbreak.break_time INTO @break_minute FROM agentbreak WHERE agentbreak.id=crmbreakid; END if; if (ifnull(crmstate,0)IN ("1") ) then SET @agentloginsert=CONCAT('insert into agent_login_log_',crmpid,'(agent_name,agent_id,break_id,start_time,end_time,duration ,ip,status,break_minutes,extension_no,phone_no,campaign_id,process_id,crm_id,DID_No,caller_id,call_connected,remark,linked_id,logoutby) values("',crmagent,'","',IFNULL(@agent_id,0),'","0","',NOW(),'","',NOW(),'","',0,'","","',crmstate,'","0","', IFNULL(crmexten,""),'","","',IFNULL(crmcid,"0"),'","',IFNULL(crmpid,"0"),'",NULL,NULL,NULL,0,"', IFNULL(crmremark,""),'","', IFNULL(crmlinkedid,""),'","', IFNULL(crmlogoutby,"0"),'");'); INSERT INTO attendance_login_logout_log(agent_id,process_id,campaign_id,client_id,login_time,logout_time,duration) VALUES(IFNULL(@agent_id,0),IFNULL(crmpid,"0"),IFNULL(crmcid,"0"),IFNULL(@client_id,"0"),NOW(),NULL,0); ELSEIF (ifnull(crmstate,0)IN ("2","18","19") ) then if((ifnull(crmstate,0) = '19' AND ifnull(crmlogoutby,0)=1) OR ifnull(crmstate,0)IN ("2","18")) then SET @agentloginsert2=CONCAT('insert into agent_login_log_',if(crmpid IS NULL OR crmpid=0,@process_id,crmpid),' (agent_name,agent_id,break_id,start_time,end_time,duration,ip,status,break_minutes,extension_no,phone_no,campaign_id ,process_id,crm_id,DID_No,caller_id,call_connected,remark,linked_id) select agent_name,agent_id,if(status="1",-1,ifnull(break_id,0)),status_change_tm,Now(), TIMESTAMPDIFF(second,status_change_tm,NOW()),agent_ip,if(status="1",3,status),break_minutes,login_extension_no ,current_phone_no,current_campaign,login_process,current_crm_id,DIDNo,if(status="6",current_phone_no,""),call_connected,remark,linked_id FROM agent_current_states WHERE agent_name="',crmagent,'" and ifnull(status,0) not in(0,2); '); PREPARE stmtagentloginsert2 FROM @agentloginsert2; EXECUTE stmtagentloginsert2; DEALLOCATE PREPARE stmtagentloginsert2; END if; SET @agentloginsert=CONCAT('insert into agent_login_log_',crmpid,'(agent_name,agent_id,break_id,start_time,end_time,duration ,ip,status,break_minutes,extension_no,phone_no,campaign_id,process_id,crm_id,DID_No,caller_id,call_connected,remark,linked_id,logoutby) values("',crmagent,'","',IFNULL(@agent_id,0),'","0","',NOW(),'","',NOW(),'","',0,'","","',crmstate,'","0","', IFNULL(crmexten,""),'","","',IFNULL(crmcid,"0"),'","',IFNULL(crmpid,"0"),'",NULL,NULL,NULL,0,"', IFNULL(crmremark,""),'","', IFNULL(crmlinkedid,""),'","', IFNULL(crmlogoutby,"0"),'");'); SET @duration = (SELECT TIMESTAMPDIFF(second,status_change_tm,NOW()) FROM agent_current_states WHERE agent_name=crmagent); INSERT INTO attendance_login_logout_log(agent_id,process_id,campaign_id,client_id,login_time,logout_time,duration) VALUES(IFNULL(@agent_id,0),IFNULL(crmpid,"0"),IFNULL(crmcid,"0"),IFNULL(@client_id,"0"),NULL,NOW(),IFNULL(@duration,0)); elseif (ifnull(crmstate,0)NOT IN("0","1","2","11","13","18","19") ) then SET @agentloginsert=CONCAT('insert into agent_login_log_',if(crmpid IS NULL OR crmpid=0,@process_id,crmpid),' (agent_name,agent_id,break_id,start_time,end_time,duration,ip,status,break_minutes,extension_no,phone_no,campaign_id ,process_id,crm_id,DID_No,caller_id,call_connected,remark,linked_id,hold_durn) select agent_name,agent_id,if(status="1",-1,ifnull(break_id,0)),status_change_tm,Now(), TIMESTAMPDIFF(second,status_change_tm,NOW()),agent_ip,if(status="1",3,status),break_minutes,login_extension_no ,current_phone_no,current_campaign,login_process,current_crm_id,DIDNo,if(status="6",current_phone_no,""),call_connected,remark,linked_id,ams_hold FROM agent_current_states WHERE agent_name="',crmagent,'" and ifnull(status,0) not in(0,2); '); elseif (ifnull(crmstate,0) IN("13","11") ) then SET @agentloginsert1=CONCAT('insert into agent_login_log_',if(crmpid IS NULL OR crmpid=0,@process_id,crmpid),' (agent_name,agent_id,break_id,start_time,end_time,duration,ip,status,break_minutes,extension_no,phone_no,campaign_id ,process_id,crm_id,DID_No,caller_id,call_connected,remark,linked_id) select agent_name,agent_id,if(status="1",-1,ifnull(break_id,0)),status_change_tm,Now(), TIMESTAMPDIFF(second,status_change_tm,NOW()),agent_ip,if(status="1",3,status),break_minutes,login_extension_no ,current_phone_no,current_campaign,login_process,current_crm_id,DIDNo,if(status="6",current_phone_no,""),call_connected,remark,linked_id FROM agent_current_states WHERE agent_name="',crmagent,'" and ifnull(status,0) not in(0,2); '); PREPARE stmtagentloginsert1 FROM @agentloginsert1; EXECUTE stmtagentloginsert1; DEALLOCATE PREPARE stmtagentloginsert1; SET @agentloginsert=CONCAT('insert into agent_login_log_',if(crmpid IS NULL OR crmpid=0,@process_id,crmpid),' (agent_name,agent_id,break_id,start_time,end_time,duration,ip,status,break_minutes,extension_no,phone_no,campaign_id ,process_id,crm_id,DID_No,caller_id,call_connected,remark,linked_id) values("',crmagent,'","',IFNULL(@agent_id,0),'","0","',NOW(),'","',NOW(),'","',0,'","","',crmstate,'","0","', IF(crmexten IS NULL AND crmexten ="",@exten,crmexten),'","',Ifnull(crmphoneno,""), '","',if(crmcid IS NULL OR crmcid=0,@campaig_id,crmcid), '","',if(crmpid IS NULL OR crmpid=0 ,@process_id,crmpid),'",NULL,"',Ifnull(crmdid,""),'","',Ifnull(crmphoneno,""), '","',if(crmcallconnected IS NULL,0,crmcallconnected),'","', IFNULL(crmremark,""),'","', IFNULL(crmlinkedid,""),'");'); END if; PREPARE stmtagentloginsert FROM @agentloginsert; EXECUTE stmtagentloginsert; DEALLOCATE PREPARE stmtagentloginsert; if (ifnull(crmstate,0) IN("13") ) then SET @PhoneNoField=NULL; SET @masterid=NULL; SET @mapid=NULL; SELECT formbuilder_field.field_name INTO @PhoneNoField FROM formbuilder_field join process on formbuilder_field.form_id=process.form_id WHERE process.id=if(crmpid IS NULL OR crmpid=0,@process_id,crmpid) and ifnull(formbuilder_field.primary_phone_no,0)=1 AND formbuilder_field.data_type='Phone' LIMIT 1; if IFNULL(@PhoneNoField,"")!="" AND ifnull(crmphoneno,"")!="" then SET @getmaster=CONCAT(' Select pm.id,cm.map_id into @masterid,@mapid from process_table_',if(crmpid IS NULL OR crmpid=0,@process_id,crmpid), ' pm join campaign_mapping_',if(crmpid IS NULL OR crmpid=0,@process_id,crmpid),' cm where ',@PhoneNoField,'="',right(crmphoneno,10),'" order by cm.map_id desc limit 1 ; '); PREPARE stmtgetmaster FROM @getmaster; execute stmtgetmaster; DEALLOCATE PREPARE stmtgetmaster; if (IFNULL(@masterid,0)>0 and IFNULL(@mapid,0)>0) then SET @callloginsert=CONCAT('insert into process_table_log_',if(crmpid IS NULL OR crmpid=0,@process_id,crmpid),' (parent_id,camp_map_id,agent_name,agent_id,log_phone_no,start_time,pick_time,end_time,duration, disposition,campaign,cdr_id,extension_no,CallType,CallConnected) values("',IFNULL(@masterid,0),'","',IFNULL(@mapid,0),'","',ifnull(crmagent,""),'","',IFNULL(@agent_id,0),'", "',crmphoneno,'",',if(crmstart_time IS NOT null,CONCAT('"',crmstart_time,'"'),"NULL"),',', if(crmpick_time IS NOT null,CONCAT('"',crmpick_time,'"'),"NULL"),',NOW(),','"', ifnull(TIMESTAMPDIFF(second,crmstart_time,NOW()),0),'","AgentMissed", "',if(crmcid IS NULL OR crmcid=0,@campaig_id,crmcid),'","',IFNULL(crmlinkedid,""),'", "',IF(crmexten IS NULL AND crmexten ="",@exten,crmexten),'","',crm_call_type,'", "',if(crmcallconnected IS NULL,0,crmcallconnected),'");'); PREPARE stmtagentcallloginsert FROM @callloginsert; EXECUTE stmtagentcallloginsert; DEALLOCATE PREPARE stmtagentcallloginsert; END if; END if; END if; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ; if (ifnull(crmstate,0) IN("1") AND ifnull(isupdate,0)=1) then UPDATE agent_current_states SET current_crm_id="0",current_campaign=crmcid,login_process=crmpid,login_extension_no=crmexten ,status=crmstate,agent_current_states.status_change_tm=NOW(), break_id=0, break_time=NULL,break_minutes=0,current_phone_no="",DIDNo="",call_connected=0, remark=crmremark ,linked_id=NULL,acd_code=crm_acd_code,call_type=NULL, trunk_channel=NULL,manual_on=0,current_map_id=NULL,current_master_id=NULL, last_ivr_digit=NULL,wrapup_durn=0,hold_durn=0,ams_hold=0,hangup_cause_code=NULL,ext_channel_id=NULL,is_mute=0,last_campaign_call_type=NULL WHERE agent_name=crmagent; SELECT @agent_id,crmstate,crmagent; elseif (ifnull(crmstate,0) IN("2","18","19") AND ifnull(isupdate,0)=1) then UPDATE agent_current_states SET current_crm_id="0",current_campaign=0, login_process=0,login_extension_no=NULL,STATUS="2",status_change_tm=NOW(), break_id=0, break_time=NULL,break_minutes=0,current_phone_no="",DIDNo="",call_connected=0, remark=crmremark ,linked_id=NULL,acd_code=crm_acd_code,call_type=NULL, trunk_channel=NULL,manual_on=0,current_map_id=NULL,current_master_id=NULL, last_ivr_digit=NULL,wrapup_durn=0,hold_durn=0,ams_hold=0,hangup_cause_code=NULL,ext_channel_id=NULL,is_mute=0, san_transfer_to=NULL,san_transfer_no=NULL,last_phone_no=NULL,last_campaign_call_type=NULL WHERE agent_name=crmagent; if ifnull(@agent_id,0)>0 then DELETE FROM agent_login_campaign WHERE agent_login_campaign.agent_id=@agent_id; DELETE FROM agent_login_token WHERE agent_login_token.agent_id=@agent_id; END if; elseif (ifnull(crmstate,0) IN(13) ) then UPDATE agent_current_states SET current_campaign=if(crmcid IS NULL OR crmcid=0,current_campaign,crmcid), login_process=if(crmpid IS NULL OR crmpid=0 ,login_process,crmpid), STATUS= case when start_time IS not null then if(IFNULL(@presatus,'0')='12',3,5) when manual_on=1 AND start_time IS null then 10 ELSE 3 end ,status_change_tm=NOW(), break_id=0,break_minutes=0, current_phone_no=case when start_time IS not null then current_phone_no ELSE "" end,DIDNo=crmdid, call_connected=if(crmcallconnected IS NULL,0,crmcallconnected), remark=crmremark,linked_id=crmlinkedid ,acd_code=crm_acd_code,call_type=crm_call_type,trunk_channel=crm_trunk_channel, agent_current_states.current_map_id=NULL,agent_current_states.current_master_id=NULL, san_transfer_to=NULL,san_transfer_no=NULL,last_campaign_call_type=NULL WHERE agent_name=crmagent; elseif (ifnull(crmstate,0) IN("3","10","11") ) then UPDATE agent_current_states SET current_crm_id=0, current_campaign=if(crmcid IS NULL OR crmcid=0,current_campaign,crmcid), login_process=if(crmpid IS NULL OR crmpid=0 ,login_process,crmpid), status=if(crmstate='11',3,crmstate),status_change_tm=NOW(), break_id=if(crmbreakid IS NULL OR crmbreakid=0,0,crmbreakid), break_time=if(crmbreakid IS not NULL and crmbreakid>0,now(),NULL), break_minutes=if(@break_minute IS NULL OR @break_minute=0,0,@break_minute), current_phone_no= "",ams_phone_no="",DIDNo=NULL,call_connected=0,remark=crmremark, linked_id=NULL,acd_code=crm_acd_code,call_type=NULL,trunk_channel=NULL, manual_on=CASE WHEN crmstate="10" THEN 1 WHEN crmstate="11" THEN 0 ELSE manual_on END, current_map_id=NULL,current_master_id=NULL,start_time=NULL,pick_time=NULL, last_ivr_digit=NULL,wrapup_durn=0,hold_durn=0,ams_hold=0,hangup_cause_code=NULL,ext_channel_id=NULL,is_mute=0, san_transfer_to=NULL,san_transfer_no=NULL,last_phone_no=NULL,last_campaign_call_type=NULL WHERE agent_name=crmagent; elseif (ifnull(crmstate,0)NOT IN("0","1","2","13","3","10","11","18","19") AND IFNULL(@exten,"")!="") then UPDATE agent_current_states SET current_campaign=if(crmcid IS NULL OR crmcid=0,current_campaign,crmcid), login_process=if(crmpid IS NULL OR crmpid=0 ,login_process,crmpid), wrapup_durn=case when ifnull(@presatus,'0')='5' AND ifnull(crmstate,'0') IN('12','16') then IFNULL(wrapup_durn,0)+TIMESTAMPDIFF(second,status_change_tm,NOW()) else wrapup_durn END , status=crmstate,status_change_tm=NOW(), break_id=if(crmbreakid IS NULL OR crmbreakid=0,0,crmbreakid), break_time=if(crmbreakid IS not NULL and crmbreakid>0,now(),NULL), break_minutes=if(@break_minute IS NULL OR @break_minute=0,0,@break_minute), current_phone_no= case when crmstate="5" then current_phone_no when ifnull(current_phone_no,"")!="" then current_phone_no else crmphoneno end, ams_phone_no= crmphoneno, DIDNo=crmdid, call_connected=if(crmcallconnected IS NULL,0,crmcallconnected),remark=crmremark,linked_id=crmlinkedid ,acd_code=crm_acd_code,call_type=crm_call_type,trunk_channel=crm_trunk_channel, current_map_id=current_map_id,current_master_id=current_master_id, last_ivr_digit=crmlastivrdigit, hold_durn=if(ifnull(crmholddurn,0)>0 ,IFNULL(hold_durn,0)+crmholddurn,hold_durn),ams_hold=ifnull(crmholddurn,0),hangup_cause_code=crmhangupcausecode, ext_channel_id=crmext_channel_id,current_chat_tm=if(ifnull(crmstate,0)=25,NOW(),current_chat_tm) WHERE agent_name=crmagent; END if; if (ifnull(crmstate,0) IN("3","10","11") AND IFNULL(@agent_id,0)>0) then DELETE FROM check_duplicate_dial WHERE agent_id=@agent_id; DELETE FROM agent_current_calling WHERE agent_id=@agent_id AND process_id=if(crmpid IS NULL OR crmpid=0,IFNULL(@process_id,0),crmpid) AND campaign_id=if(crmcid IS NULL OR crmcid=0,@campaig_id,crmcid); END if; END if; SELECT @PhoneNoField,@getmaster,@callloginsert,IFNULL(@masterid,0), IFNULL(@mapid,0),`crmpid`,`crmcid`,`crmagent`,@presatus,`crmexten`,`crmstate`,`crmbreakid`,`crmphoneno`,`crmdid`,`crmcallconnected`,`crmremark`,crmlinkedid,date_formAT(date_add(NOW(), INTERVAL @break_minute MINUTE ),'%d %M %Y %H:%i:%s')break_time,crmstart_time,crmpick_time; END// DELIMITER ;