DELIMITER $$

USE `sisgo`$$


CREATE DEFINER=`sisgo`@`localhost` PROCEDURE  revision_datalle()


BEGIN

CREATE TEMPORARY TABLE Temprevision1  
 SELECT  d.id,0 AS revision_id,0 AS metro , 0 AS defecto,0 AS defecto_continuo,
d.id+ROUND((SELECT total_metros FROM revision WHERE id=d.revision_id)/10,-1) id2, 0 AS revision_id2,0 AS metro2 , 0 AS defecto2,0 AS defecto_continuo2,

d.id+ROUND((SELECT total_metros FROM revision WHERE id=d.revision_id)/10,-1)*2 id3, 0 AS revision_id3,0 AS metro3 , 0 AS defecto3,0 AS defecto_continuo3,

d.id+ROUND((SELECT total_metros FROM revision WHERE id=d.revision_id)/10,-1)*3 id4, 0 AS revision_id4,0 AS metro4 , 0 AS defecto4,0 AS defecto_continuo4,

d.id+ROUND((SELECT total_metros FROM revision WHERE id=d.revision_id)/10,-1)*4 id5, 0 AS revision_id5,0 AS metro5 , 0 AS defecto5,0 AS defecto_continuo5,

d.id+ROUND((SELECT total_metros FROM revision WHERE id=d.revision_id)/10,-1)*5 id6, 0 AS revision_id6, 0 AS metro6 , 0 AS defecto6,0 AS defecto_continuo6,

d.id+ROUND((SELECT total_metros FROM revision WHERE id=d.revision_id)/10,-1)*6 id7, 0 AS revision_id7,0 AS metro7 , 0 AS defecto7,0 AS defecto_continuo7,

d.id+ROUND((SELECT total_metros FROM revision WHERE id=d.revision_id)/10,-1)*7 id8, 0 AS revision_id8,0 AS metro8 , 0 AS defecto8,0 AS defecto_continuo8,

d.id+ROUND((SELECT total_metros FROM revision WHERE id=d.revision_id)/10,-1)*8 id9, 0 AS revision_id9, 0 AS metro9 , 0 AS defecto9,0 AS defecto_continuo9,

d.id+ROUND((SELECT total_metros FROM revision WHERE id=d.revision_id)/10,-1)*9 id10, 0 AS revision_id10,0 AS metro10 , 0 AS defecto10,0 AS defecto_continuo10


,'' AS nombre_defecto
,'' AS nombre_defecto2
,'' AS nombre_defecto3
,'' AS nombre_defecto4
,'' AS nombre_defecto5
,'' AS nombre_defecto6
,'' AS nombre_defecto7
,'' AS nombre_defecto8
,'' AS nombre_defecto9
,'' AS nombre_defecto10
,ROUND((SELECT total_metros FROM revision WHERE id=d.revision_id)/10,-1) AS  metros_columna
FROM revision_detalle d
 WHERE d.revision_id='2' 
AND d.metro<=ROUND((SELECT total_metros FROM revision WHERE id='2')/10,-1)
 ORDER BY d.metro;




CREATE TEMPORARY TABLE Temprevision2  
SELECT d.id,d.revision_id,d.metro , d.defecto,d.defecto_continuo
FROM revision_detalle d
 WHERE d.revision_id='2' 
AND d.metro<=(SELECT total_metros FROM revision WHERE id='2');




UPDATE Temprevision1 p  JOIN Temprevision2 t  ON t.id = p.id   SET 
p.revision_id = t.revision_id,
p.metro = t.metro,
p.defecto=t.defecto,
p.defecto_continuo=t.defecto_continuo;


UPDATE Temprevision1 p  JOIN Temprevision2 t  ON t.id = p.id2   SET 
p.revision_id2 = t.revision_id,
p.metro2 = t.metro,
p.defecto2=t.defecto,
p.defecto_continuo2=t.defecto_continuo;

UPDATE Temprevision1 p  JOIN Temprevision2 t  ON t.id = p.id3   SET 
p.revision_id3 = t.revision_id,
p.metro3 = t.metro,
p.defecto3=t.defecto,
p.defecto_continuo3=t.defecto_continuo;


UPDATE Temprevision1 p  JOIN Temprevision2 t  ON t.id = p.id4   SET 
p.revision_id4 = t.revision_id,
p.metro4 = t.metro,
p.defecto4=t.defecto,
p.defecto_continuo4=t.defecto_continuo;


UPDATE Temprevision1 p  JOIN Temprevision2 t  ON t.id = p.id5   SET 
p.revision_id5 = t.revision_id,
p.metro5 = t.metro,
p.defecto5=t.defecto,
p.defecto_continuo5=t.defecto_continuo;

UPDATE Temprevision1 p  JOIN Temprevision2 t  ON t.id = p.id6   SET 
p.revision_id6 = t.revision_id,
p.metro6 = t.metro,
p.defecto6=t.defecto,
p.defecto_continuo6=t.defecto_continuo;

UPDATE Temprevision1 p  JOIN Temprevision2 t  ON t.id = p.id7   SET 
p.revision_id7 = t.revision_id,
p.metro7 = t.metro,
p.defecto7=t.defecto,
p.defecto_continuo7=t.defecto_continuo;

UPDATE Temprevision1 p  JOIN Temprevision2 t  ON t.id = p.id8   SET 
p.revision_id8 = t.revision_id,
p.metro8 = t.metro,
p.defecto8=t.defecto,
p.defecto_continuo8=t.defecto_continuo;

UPDATE Temprevision1 p  JOIN Temprevision2 t  ON t.id = p.id9   SET 
p.revision_id9 = t.revision_id,
p.metro9 = t.metro,
p.defecto9=t.defecto,
p.defecto_continuo9=t.defecto_continuo;

UPDATE Temprevision1 p  JOIN Temprevision2 t  ON t.id = p.id10   SET 
p.revision_id10 = t.revision_id,
p.metro10 = t.metro,
p.defecto10=t.defecto,
p.defecto_continuo10=t.defecto_continuo;




SELECT * FROM Temprevision1 ;


END$$
DELIMITER;