USE cdrs
DROP PROCEDURE mscSequence;
delimiter | 
CREATE PROCEDURE mscSequence(IN iDate DATE, IN msc CHAR(4))
BEGIN
  DECLARE sufix VARCHAR(15);
  DECLARE sufixT VARCHAR(15);
  DECLARE sufixY VARCHAR(15);
  DECLARE done INT DEFAULT 0;
  DECLARE counter INT DEFAULT 0;
  DECLARE hasHole INT DEFAULT 0;
  DECLARE ant int ;
  DECLARE dif int ;
  DECLARE curr int ;
  DECLARE hora time;
  DECLARE fecha date;  
  DECLARE diff INT;
  DECLARE minZeroSeq, tmp_max, max, min INT;
  DECLARE minZero time;
  SET hasHole = 0;
  SELECT CONCAT(msc,'_',CONVERT(DATE_FORMAT(iDate,'%Y%m%d'),CHAR(15))) into sufix;
  SELECT CONCAT(msc,'_',CONVERT(DATE_FORMAT(DATE_ADD(iDate, interval 1 day ),'%Y%m%d'),CHAR(15))) into sufixT;
  SELECT CONCAT(msc,'_',CONVERT(DATE_FORMAT(DATE_ADD(iDate, interval -1 day ),'%Y%m%d'),CHAR(15))) into sufixY;
  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_seq (sequencenumber int primary key, timestamp time key,startofchargingdate date key);
  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_q (minr int, maxr int,cuenta int, hora varchar(50),fecha date);

  TRUNCATE TABLE tmp_seq;
  TRUNCATE TABLE tmp_q;

  SET @sqlStmt = CONCAT('INSERT INTO tmp_seq SELECT sequencenumber,timestamp,startofchargingdate FROM ',sufix,' WHERE startofchargingdate!=\'0000-00-00\' UNION SELECT sequencenumber,timestamp,startofchargingdate FROM ',sufixY,' WHERE hour>12 AND sequencenumber > (SELECT MIN(sequencenumber) FROM ',sufix,' WHERE hour=0) AND startofchargingdate!=\'0000-00-00\' UNION SELECT sequencenumber,timestamp,startofchargingdate FROM ',sufixT,' WHERE hour<12 AND sequencenumber<(SELECT MAX(sequencenumber) FROM ',sufix,' WHERE timestamp BETWEEN \'23:58:00\' and \'23:59:59\') AND startofchargingdate!=\'0000-00-00\' ORDER BY 1');
	SELECT @sqlStmt;
  PREPARE stmtx FROM @sqlStmt;
  EXECUTE stmtx;
  DEALLOCATE PREPARE stmtx;
  
  SELECT MIN(timestamp),MIN(sequenceNumber) INTO minZero, minZeroSeq FROM tmp_seq WHERE sequenceNumber BETWEEN 0 AND 10;
  IF minZero IS NOT NULL THEN
--FIND THE MAX
	SELECT MAX(sequencenumber) INTO tmp_max FROM tmp_seq WHERE timestamp < minZero;
	SELECT MAX(sequencenumber) INTO max FROM tmp_seq WHERE sequenceNumber >= tmp_max;
--FIND THE MIN
	SELECT sequencenumber INTO min FROM tmp_seq WHERE date=iDate ORDER BY timestamp DESC LIMIT 1;
  END IF;

  IF minZero IS NULL THEN
  BEGIN
      DECLARE cur1 CURSOR FOR SELECT sequencenumber,timestamp,startofchargingdate FROM tmp_seq ORDER BY 1;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  END;
  ELSE
  BEGIN
      DECLARE cur1 CURSOR FOR (SELECT sequencenumber,timestamp,startofchargingdate FROM tmp_seq WHERE sequencenumber BETWEEN min AND max ORDER BY 1) UNION ALL (SELECT sequencenumber,timestamp,startofchargingdate FROM tmp_seq WHERE sequencenumber >= minZeroSeq AND sequencenumber < min ORDER BY 1);
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  END;
  END IF;


  OPEN cur1;

  REPEAT
    FETCH cur1 INTO curr,hora,fecha;
    IF NOT done THEN
       IF counter = 0 THEN
	SET ant = curr;
       ELSE
	IF curr <> ant + 1 THEN
	  SET dif = curr-ant-1;
	  INSERT INTO tmp_q values(ant+1,curr-1,dif,hora,fecha);
        END IF;
	SET ant = curr;
      END IF;
    SET counter = counter + 1 ;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  SELECT COUNT(*) FROM tmp_q WHERE fecha=iDate AND cuenta>2 INTO hasHole;
  DELETE FROM daily_queries.sequenceHoles WHERE date=iDate AND switch=msc;
  IF hasHole > 0 THEN
	  INSERT INTO daily_queries.sequenceHoles (`date`,switch,`type`,`from`,`to`,difference,observations) SELECT iDate,msc,'',minr,maxr,cuenta,'' FROM tmp_q WHERE fecha=iDate AND cuenta>2 ON DUPLICATE KEY UPDATE difference=tmp_q.cuenta;
  ELSE
	  INSERT INTO daily_queries.sequenceHoles (`date`,switch,`type`,`from`,`to`,difference,observations) VALUES (iDate,msc,'','','','','');
  END IF;
END|
delimiter ;