cursor.sql

CREATE DEFINER=`root`@`localhost` PROCEDURE `resultcb`()
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BLOCK1: BEGIN
	DECLARE v_id INT;
	DECLARE v_ani VARCHAR(15);
	DECLARE v_skill VARCHAR(6);
	DECLARE v_st INT;
	DECLARE v_dtup TIMESTAMP;
    
	DECLARE v_ucid VARCHAR(20) DEFAULT NULL;
	DECLARE v_login VARCHAR(6) DEFAULT NULL;
	DECLARE v_t1 TIMESTAMP DEFAULT NULL;
	DECLARE v_t2 TIMESTAMP DEFAULT NULL;
	
	DECLARE v_hangout VARCHAR(1) DEFAULT NULL;
	DECLARE v_tempo INT;
	
	
	DECLARE done INT DEFAULT 0;
	
	DECLARE v_qnt INT DEFAULT 0;
	
	DECLARE cur CURSOR FOR SELECT id, ani, vdn, st, dtup FROM ivr.clientes;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	OPEN cur;
    
	SET SQL_SAFE_UPDATES = 0;
	DELETE FROM ivr.escallback;
	SET SQL_SAFE_UPDATES = 1;
	
	get_cliente: LOOP
		FETCH cur INTO v_id, v_ani, v_skill, v_st, v_dtup;
		IF done THEN
			LEAVE get_cliente;
      END IF;
        
		select count(1) INTO v_qnt from ivr.alertcallback where idcliente = v_id;
        
		IF v_qnt > 0 THEN
			SELECT ucid, login, t1, t2 INTO v_ucid, v_login, v_t1, v_t2
				FROM ivr.alertcallback WHERE idcliente = v_id ORDER BY t1 DESC LIMIT 1;
                
			BLOCK2: BEGIN
					DECLARE v_evento VARCHAR(1) DEFAULT NULL;
					DECLARE v_dth TIMESTAMP DEFAULT NULL;

					DECLARE qnt1 INT DEFAULT 0;
					DECLARE qnt8 INT DEFAULT 0;
					DECLARE temptmp TIMESTAMP DEFAULT NULL;
					
					DECLARE done_evento INT DEFAULT 0;
					DECLARE cur_evento CURSOR FOR SELECT evento, dth FROM logcallevent WHERE ucid1 = v_ucid order by dth;
					DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_evento = 1;
					OPEN cur_evento;
					get_evento: LOOP
						FETCH cur_evento INTO v_evento, v_dtup;
						IF done_evento THEN
							LEAVE get_evento;
						END IF;
						
						CASE v_evento
							WHEN '1' THEN 
								BEGIN
									SET qnt1 = qnt1 + 1;
									SET temptmp = v_dtup;
								END;
							WHEN '8' THEN
								BEGIN
									SET qnt8 = qnt8 + 1;
									IF qnt8 = 1 THEN
										SET v_tempo = TIMESTAMPDIFF(SECOND, temptmp, v_dtup);	
									END IF;
								END;
							WHEN '3' THEN LEAVE get_evento;
							WHEN 'M' THEN LEAVE get_evento;
							ELSE 
								BEGIN
								END;
						END CASE;
					END LOOP get_evento;
				   
					IF qnt1 > 0 THEN
						CASE qnt8 
							WHEN 1 THEN SET v_hangout = 'A';
							WHEN 2 THEN SET v_hangout = 'C';
							ELSE 
								BEGIN
									SET v_hangout = '0';
									SET v_tempo = NULL;
								END;
						END CASE;
					
					ELSE
						SET v_hangout = 'X';
					END IF;
					
					SET qnt1 = 0;
					SET qnt8 = 0;
						
					CLOSE cur_evento;
				
				END BLOCK2;
		END IF;

		INSERT INTO ivr.escallback (id, ani, skill, st, dtup, qnt, ucid, login, t1, t2, hangout, tempoc) 
			VALUES (v_id, v_ani, v_skill, v_st, v_dtup, v_qnt, v_ucid, v_login, v_t1, v_t2, v_hangout, v_tempo);
        
		SET v_ucid  = NULL;
		SET v_login  = NULL;
		SET v_t1  = NULL;
		SET v_t2  = NULL;
		SET v_hangout = NULL;
		SET v_tempo = NULL;
		
	END LOOP get_cliente;
	CLOSE cur;

	SELECT e.id, e.ani, e.skill, e.dtup, e.qnt, e.t2 as LastTry,e.login, e.hangout, SEC_TO_TIME(tempoc) as conversa from escallback e;

END BLOCK1

table.sql

-- select date_format('2016-03-22 09:56:31', '%Y-%m-%d %T' ) hora;


CREATE TABLE `clientes` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`ani` VARCHAR(15) NOT NULL,
	`vdn` VARCHAR(6) NULL DEFAULT NULL,
	`st` INT(11) NULL DEFAULT '0',
	`dtup` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=12
;

insert into clientes (ani, vdn, st, dtup) 
values ('512133507567', '1982', 9, date_format('1982-01-07 21:56:31', '%Y-%m-%d %T' ));

CREATE TABLE `alertcallback` (
	`ucid` VARCHAR(20) NOT NULL,
	`login` VARCHAR(6) NOT NULL,
	`ext` VARCHAR(6) NOT NULL,
	`t1` TIMESTAMP NULL DEFAULT NULL,
	`idcliente` INT(11) NULL DEFAULT NULL,
	`t2` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`ucid`),
	INDEX `fkcliente` (`idcliente`),
	CONSTRAINT `fkcliente` FOREIGN KEY (`idcliente`) REFERENCES `clientes` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `logcallevent` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`ucid1` VARCHAR(20) NOT NULL,
	`ucid2` VARCHAR(20) NOT NULL,
	`evento` VARCHAR(1) NOT NULL,
	`dth` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `FK_logcallevent_alertcallback` (`ucid1`),
	CONSTRAINT `FK_logcallevent_alertcallback` FOREIGN KEY (`ucid1`) REFERENCES `alertcallback` (`ucid`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=23
;


CREATE TABLE `escallback` (
	`id` INT(11) NOT NULL DEFAULT '0',
	`ani` VARCHAR(15) NULL DEFAULT NULL,
	`skill` VARCHAR(4) NULL DEFAULT NULL,
	`st` INT(11) NULL DEFAULT NULL,
	`dtup` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`qnt` INT(11) NULL DEFAULT NULL,
	`ucid` VARCHAR(20) NULL DEFAULT NULL,
	`login` VARCHAR(6) NULL DEFAULT NULL,
	`t1` TIMESTAMP NULL DEFAULT NULL,
	`t2` TIMESTAMP NULL DEFAULT NULL,
	`hangout` VARCHAR(1) NULL DEFAULT NULL,
	`tempoc` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;