DELIMITER $$

USE `sisgo`$$

DROP PROCEDURE IF EXISTS `revision_detalles`$$

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


 

DELETE FROM revision_detalle_temporal;
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 Temprevisiontt10; 
DROP  TEMPORARY TABLE IF EXISTS Temprevision10; 
SET @id :=nevision    ;
SET @t_metros1 := CEIL( (SELECT total_metros FROM revision WHERE id= @id)/10 );
SET @t_metros2 :=ROUND((SELECT total_metros FROM revision WHERE id= @id)/10,-1)+((SELECT total_metros FROM revision WHERE id= @id)-ROUND((SELECT total_metros FROM revision WHERE id= @id)/10,-1)*10);
SET @t_metros :=CASE WHEN  MOD((SELECT total_metros FROM revision WHERE id= @id),10)=0 THEN @t_metros1 ELSE @t_metros1  END;
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.puntos,d.ancho,d.angulo,d.ppp,d.defecto_continuo,'' AS nombre_defecto
 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,puntos AS puntos2, ancho AS ancho2,angulo AS angulo2,ppp AS ppp2, defecto_continuo AS defecto_continuo2, nombre_defecto AS nombre_defecto2 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,puntos AS puntos3, ancho AS ancho3,angulo AS angulo3,ppp AS ppp3, defecto_continuo AS defecto_continuo3, nombre_defecto AS nombre_defecto3 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,puntos AS puntos4, ancho AS ancho4,angulo AS angulo4,ppp AS ppp4, defecto_continuo AS defecto_continuo4, nombre_defecto AS nombre_defecto4 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,puntos AS puntos5, ancho AS ancho5,angulo AS angulo5,ppp AS ppp5, defecto_continuo AS defecto_continuo5, nombre_defecto AS nombre_defecto5 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,puntos AS puntos6, ancho AS ancho6,angulo AS angulo6,ppp AS ppp6, defecto_continuo AS defecto_continuo6, nombre_defecto AS nombre_defecto6 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,puntos AS puntos7, ancho AS ancho7,angulo AS angulo7,ppp AS ppp7, defecto_continuo AS defecto_continuo7, nombre_defecto AS nombre_defecto7 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,puntos AS puntos8, ancho AS ancho8,angulo AS angulo8,ppp AS ppp8, defecto_continuo AS defecto_continuo8, nombre_defecto AS nombre_defecto8 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,puntos AS puntos9, ancho AS ancho9,angulo AS angulo9,ppp AS ppp9, defecto_continuo AS defecto_continuo9, nombre_defecto AS nombre_defecto9 FROM Temprevisiontt WHERE metro>@t_metros*8 AND  metro<=@t_metros*9;
CREATE TEMPORARY TABLE Temprevisiontt10 SELECT fila AS fila10 ,id AS d10,revision_id AS revision_id10,metro AS metro10, defecto AS defecto10,puntos AS puntos10,  ancho AS ancho10,angulo AS angulo10,ppp AS ppp10,  defecto_continuo AS defecto_continuo10, nombre_defecto AS nombre_defecto10 FROM Temprevisiontt WHERE metro>@t_metros*9 AND  metro<=@t_metros*10;
CREATE TEMPORARY TABLE Temprevision10 SELECT fila AS fila10 ,0  AS d10,revision_id AS revision_id10,0  AS metro10,0 AS defecto10,0 AS puntos10,  0 AS ancho10,0 AS angulo10,0 AS ppp10, 0  AS defecto_continuo10, nombre_defecto AS nombre_defecto10 FROM Temprevision1 ;
UPDATE Temprevision10 p   JOIN Temprevisiontt10 t  ON t.fila10 = p.fila10 SET p.d10 = t.d10 ,p.metro10 = t.metro10,p.defecto10 = t.defecto10,p.puntos10 = t.puntos10,p.ancho10 = t.ancho10,p.angulo10 = t.angulo10,p.ppp10 = t.ppp10,p.defecto_continuo10 = t.defecto_continuo10 ;
SET @i =0;
INSERT INTO revision_detalle_temporal
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 ; 
UPDATE revision_detalle_temporal p   JOIN defectos t  ON t.codigo = p.defecto SET p.nombre_defecto = t.nombre;
UPDATE revision_detalle_temporal p   JOIN defectos t  ON t.codigo = p.defecto2 SET p.nombre_defecto2 = t.nombre;
UPDATE revision_detalle_temporal p   JOIN defectos t  ON t.codigo = p.defecto3 SET p.nombre_defecto3 = t.nombre;
UPDATE revision_detalle_temporal p   JOIN defectos t  ON t.codigo = p.defecto4 SET p.nombre_defecto4 = t.nombre;
UPDATE revision_detalle_temporal p   JOIN defectos t  ON t.codigo = p.defecto5 SET p.nombre_defecto5 = t.nombre;
UPDATE revision_detalle_temporal p   JOIN defectos t  ON t.codigo = p.defecto6 SET p.nombre_defecto6 = t.nombre;
UPDATE revision_detalle_temporal p   JOIN defectos t  ON t.codigo = p.defecto7 SET p.nombre_defecto7 = t.nombre;
UPDATE revision_detalle_temporal p   JOIN defectos t  ON t.codigo = p.defecto8 SET p.nombre_defecto8 = t.nombre;
UPDATE revision_detalle_temporal p   JOIN defectos t  ON t.codigo = p.defecto9 SET p.nombre_defecto9 = t.nombre;
UPDATE revision_detalle_temporal p   JOIN defectos t  ON t.codigo = p.defecto10 SET p.nombre_defecto10 = t.nombre;
 SELECT * FROM revision_detalle_temporal; 




END$$

DELIMITER ;