DELIMITER $$

USE `sisgo`$$

DROP PROCEDURE IF EXISTS `revision_detalles`$$

CREATE DEFINER=`sisgo`@`localhost` PROCEDURE `revision_detalles`( nevision INT )
BEGIN

CREATE TEMPORARY TABLE Temprevision1  
 
 
 
DROP  TEMPORARY TABLE IF EXISTS Temprevisiontt; 
DROP  TEMPORARY TABLE IF EXISTS Temprevision1; 
DROP  TEMPORARY TABLE IF EXISTS Temprevision2;
DROP  TEMPORARY TABLE IF EXISTS Temprevision3; 
DROP  TEMPORARY TABLE IF EXISTS Temprevision4; 
DROP  TEMPORARY TABLE IF EXISTS Temprevision5; 
DROP  TEMPORARY TABLE IF EXISTS Temprevision6; 
DROP  TEMPORARY TABLE IF EXISTS Temprevision7; 
DROP  TEMPORARY TABLE IF EXISTS Temprevision8; 
DROP  TEMPORARY TABLE IF EXISTS Temprevision9; 
DROP  TEMPORARY TABLE IF EXISTS Temprevision10; 

SET @id =nevision ;
SET @t_metros := ROUND((SELECT total_metros FROM revision WHERE id= @id)/10,-1);
SET @i =0;SET @i2 =0;SET @i3 =0;SET @i4 =0;SET @i5 =0;SET @i6 =0;SET @i7 =0;SET @i8 =0;SET @i9 =0;SET @i10 =0;
 
CREATE TEMPORARY TABLE Temprevisiontt 
SELECT (CASE WHEN (metro=@t_metros+1) OR metro=(2*@t_metros+1) OR metro=(3*@t_metros+1) OR metro=(4*@t_metros+1) OR metro=(5*@t_metros+1) OR metro=(6*@t_metros+1) OR metro=(7*@t_metros+1) OR metro=(8*@t_metros+1) OR metro=(9*@t_metros+1) OR metro=(10*@t_metros+1) THEN @i := 1 ELSE  @i := @i + 1 END) AS fila ,d.id,d.revision_id,d.metro , d.defecto,d.defecto_continuo FROM revision_detalle d  
WHERE d.revision_id=@id 
AND d.metro<=(SELECT total_metros FROM revision WHERE id=d.revision_id)
ORDER BY d.metro;

CREATE TEMPORARY TABLE Temprevision1 SELECT * FROM Temprevisiontt WHERE metro<=@t_metros;
CREATE TEMPORARY TABLE Temprevision2 SELECT fila AS fila2 ,id AS d2,revision_id AS revision_id2,metro AS metro2, defecto AS defecto2,defecto_continuo AS defecto_continuo2 FROM Temprevisiontt WHERE metro>@t_metros AND  metro<=@t_metros*2;
CREATE TEMPORARY TABLE Temprevision3 SELECT fila AS fila3 ,id AS d3,revision_id AS revision_id3,metro AS metro3, defecto AS defecto3,defecto_continuo AS defecto_continuo3 FROM Temprevisiontt WHERE metro>@t_metros*2 AND  metro<=@t_metros*3;

CREATE TEMPORARY TABLE Temprevision4 SELECT fila AS fila4 ,id AS d4,revision_id AS revision_id4,metro AS metro4, defecto AS defecto4,defecto_continuo AS defecto_continuo4 FROM Temprevisiontt WHERE metro>@t_metros*3 AND  metro<=@t_metros*4;
CREATE TEMPORARY TABLE Temprevision5 SELECT fila AS fila5 ,id AS d5,revision_id AS revision_id5,metro AS metro5, defecto AS defecto5,defecto_continuo AS defecto_continuo5 FROM Temprevisiontt WHERE metro>@t_metros*4 AND  metro<=@t_metros*5;
CREATE TEMPORARY TABLE Temprevision6 SELECT fila AS fila6 ,id AS d6,revision_id AS revision_id6,metro AS metro6, defecto AS defecto6,defecto_continuo AS defecto_continuo7 FROM Temprevisiontt WHERE metro>@t_metros*5 AND  metro<=@t_metros*6;
CREATE TEMPORARY TABLE Temprevision7 SELECT fila AS fila7 ,id AS d7,revision_id AS revision_id7,metro AS metro3, defecto AS defecto7,defecto_continuo AS defecto_continuo8 FROM Temprevisiontt WHERE metro>@t_metros*6 AND  metro<=@t_metros*7;
CREATE TEMPORARY TABLE Temprevision8 SELECT fila AS fila8 ,id AS d8,revision_id AS revision_id8,metro AS metro8, defecto AS defecto8,defecto_continuo AS defecto_continuo9 FROM Temprevisiontt WHERE metro>@t_metros*7 AND  metro<=@t_metros*8;
CREATE TEMPORARY TABLE Temprevision9 SELECT fila AS fila9 ,id AS d9,revision_id AS revision_id9,metro AS metro9, defecto AS defecto9,defecto_continuo AS defecto_continuo9 FROM Temprevisiontt WHERE metro>@t_metros*8 AND  metro<=@t_metros*9;
CREATE TEMPORARY TABLE Temprevision10 SELECT fila AS fila10 ,id AS d10,revision_id AS revision_id10,metro AS metro10, defecto AS defecto10,defecto_continuo AS defecto_continuo10 FROM Temprevisiontt WHERE metro>@t_metros*9 AND  metro<=@t_metros*10;


SET @i =0;

SELECT t1.*,t2.*,t3.*, t4.*,t5.*,t6.*, t7.*,t8.*,t9.*, t10.* FROM Temprevision1 t1 
CROSS JOIN Temprevision2 t2 ON t1.fila=t2.fila2  
CROSS JOIN Temprevision3 t3 ON t1.fila=t3.fila3
CROSS JOIN Temprevision4 t4 ON t1.fila=t4.fila4
CROSS JOIN Temprevision5 t5 ON t1.fila=t5.fila5
CROSS JOIN Temprevision6 t6 ON t1.fila=t6.fila6
CROSS JOIN Temprevision7 t7 ON t1.fila=t7.fila7
CROSS JOIN Temprevision8 t8 ON t1.fila=t8.fila8
CROSS JOIN Temprevision9 t9 ON t1.fila=t9.fila9
CROSS JOIN Temprevision10 t10 ON t1.fila=t10.fila10 ; 














END$$

DELIMITER ;