/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `Grilla3_AudienciasConObs`(
	IN `desde` INT,
	IN `hasta` INT,
	IN `soloAudiencias` INT,
	IN `excluirCanceladas` INT,
	IN `conSuperposicion` INT,
	IN `excluirOcultas` INT
)
BEGIN
    -- Variables
    DECLARE v_id, cant, horadesde, horahasta, finished INT DEFAULT 0;
    DECLARE v_juez_id, v_juez2_id, v_juez3_id, v_fiscal_id, v_defensor_id, v_fiscal2_id, v_defensor2_id, v_defensor3_id, v_defensor4_id, v_AudienciaId varchar(4) DEFAULT "";
    DECLARE v_room_id, v_room2_id, v_room3_id INT DEFAULT 0;
    DECLARE v_estado VARCHAR(25);
	 DECLARE cond_soloAudiencias, cond_excluirCanceladas, cond_excluirOcultas VARCHAR(50);
	

    -- Cursor y handler
    DECLARE cursor_grilla CURSOR FOR
        SELECT id, juez_id, juez2_id, juez3_id, fiscal_id, fiscal2_id, defensor_id, defensor2_id, defensor3_id, defensor4_id,
               start_time, end_time, estado, AudienciaId, room_id, room2_id, room3_id
        FROM tempGrilla;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

 
	IF soloAudiencias = 1 THEN
		SET cond_soloAudiencias = " AND ta.esAudiencia = 1 ";
	ELSE
		SET cond_soloAudiencias = "";
	END IF;	
	
	IF excluirCanceladas = 1 THEN
		SET cond_excluirCanceladas = " AND me.estado <> 'Cancelada'";
	ELSE
		SET cond_excluirCanceladas = "";
	END IF;		
	
	IF excluirOcultas = 1 THEN
		SET cond_excluirOcultas = " AND me.noMostrarEnGrilla = 0";
	ELSE
		SET cond_excluirOcultas = "";
	END IF;	

 -- Crear tabla temporal
    SET @sentencia_select =	CONCAT(
    "CREATE OR REPLACE TEMPORARY TABLE tempGrilla AS ",
     "SELECT ",
       "me.start_time, ",
       " salas(me.room_id, me.room2_id, me.room3_id, ' / ') AS TotSalas, ",
       " me.juez_id, me.description, me.name, ",
       " ta.tipoAudiencia AS tipoAudiencia, ",
       " me.fiscal_id, me.defensor_id, ",
       " md.nombre AS nombred1, mj.nombre AS nombreJ1, mf.nombre AS nombreF1, ",
       " me.id, me.estado, ",
       " mj2.nombre AS nombreJ2, mj3.nombre AS nombreJ3, ",
       " me.juez2_id, me.noContarEstaAudiencia, me.observaciones, ",
       " me.tipoAudiencia AS detalleAudiencia, ",
       " ta.IPP, ta.JO, mr.piso, me.end_time, ",
       " mFQ.nombre AS nombre2Fiscal, md2.nombre AS nombre2Defensor, ",
       " md3.nombre AS nombre3Defensor, md4.nombre AS nombre4Defensor,",
       " Op.nombre AS opNomre, ",
       " me.juez3_id, me.fiscal2_id, me.defensor2_id, me.defensor3_id, me.defensor4_id, ",
       " 0 AS supJuez1, 0 AS supJuez2, 0 AS supJuez3, ",
       " 0 AS supFiscal1, 0 AS supFiscal2, ",
       " 0 AS supDef1, 0 AS supDef2, 0 AS supDef3, 0 AS supDef4, ",
       " ta.id AS AudienciaId, ta.esAudiencia,ta.micolor,",
       " me.room_id, me.room2_id, me.room3_id, ",
       " 0 AS supSala1, 0 AS supSala2, 0 AS supSala3, ",
		 " mr.DescCorta S1, mr2.DescCorta S2, mr3.DescCorta S3, Op.nickname ",
    " FROM mrbs_entry me ",
    " INNER JOIN mrbs_fiscal mf ON me.fiscal_id = mf.fiscal_id ",
    " INNER JOIN mrbs_juez mj ON me.juez_id = mj.juez_id ",
    " INNER JOIN mrbs_defensor md ON me.defensor_id = md.defensor_id ",
    " INNER JOIN mrbs_room mr ON me.room_id = mr.id ",
    " INNER JOIN mrbs_area ma ON mr.area_id = ma.id ",
    " INNER JOIN mrbs_tipo_audiencia ta ON me.tipoAudiencia_id = ta.id ",
	 " LEFT JOIN mrbs_room mr2 ON me.room2_id = mr2.id ",
	 " LEFT JOIN mrbs_room mr3 ON me.room3_id = mr3.id	 ",
    " LEFT JOIN mrbs_juez mj2 ON me.juez2_id = mj2.juez_id ",
    " LEFT JOIN mrbs_juez mj3 ON me.juez3_id = mj3.juez_id ",
    " LEFT JOIN v_fiscquer mFQ ON me.fiscal2_id = mFQ.id ",
    " LEFT JOIN mrbs_defensor md2 ON me.defensor2_id = md2.defensor_id ",
    " LEFT JOIN mrbs_defensor md3 ON me.defensor3_id = md3.defensor_id ",
    " LEFT JOIN mrbs_defensor md4 ON me.defensor4_id = md4.defensor_id ",	     
    " LEFT JOIN v_operadores Op ON me.operador_id = Op.operador_id ",
    " WHERE me.instancia <> 'Segunda Instancia'",
	  cond_soloAudiencias, cond_excluirCanceladas, cond_excluirOcultas,
      " AND ( ",
       " (me.start_time > ", CAST(desde AS CHAR), " AND me.start_time < ", CAST(hasta AS CHAR), ") ",
        " OR ",
      " (me.start_time < ", CAST(desde AS CHAR), " AND me.end_time > ", CAST(desde AS CHAR), ") ",
    " )  ",
    " ORDER BY me.start_time ASC, me.end_time DESC;");

    PREPARE stmt3 FROM @sentencia_select;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;	

    -- Bucle del cursor
    OPEN cursor_grilla;

    revisarSuperposiciones:LOOP
        FETCH cursor_grilla INTO v_id, v_juez_id, v_juez2_id, v_juez3_id,
                                 v_fiscal_id, v_fiscal2_id, v_defensor_id, v_defensor2_id, v_defensor3_id, v_defensor4_id,
                                 horadesde, horahasta, v_estado, v_AudienciaId,
                                 v_room_id, v_room2_id, v_room3_id;
        IF finished = 1 THEN
            LEAVE revisarSuperposiciones;
        END IF;

        IF v_estado <> 'Cancelada' AND v_AudienciaId NOT IN ('IP63','IP64') THEN
            IF v_juez_id <> 'J000' THEN
                CALL chequearSuperposicionJuez(horadesde, horahasta, 'juez_id', v_juez_id, 'supJuez1', v_id);
            END IF;
            IF v_juez2_id <> 'J000' THEN
                CALL chequearSuperposicionJuez(horadesde, horahasta, 'juez2_id', v_juez2_id, 'supJuez2', v_id);
            END IF;
            IF v_juez3_id <> 'J000' THEN
                CALL chequearSuperposicionJuez(horadesde, horahasta, 'juez3_id', v_juez3_id, 'supJuez3', v_id);
            END IF;
            IF v_fiscal_id NOT IN ('FF00','F000') THEN
                CALL chequearSuperposicionFiscal(horadesde, horahasta, 'fiscal_id', v_fiscal_id, 'supFiscal1', v_id);
            END IF;
            IF v_fiscal2_id NOT IN ('FF00','F000') THEN
                CALL chequearSuperposicionFiscal(horadesde, horahasta, 'fiscal2_id', v_fiscal2_id, 'supFiscal2', v_id);
            END IF;
            IF v_defensor_id NOT IN ('DD00','D000','DP00') THEN
                CALL chequearSuperposicionDefensor(horadesde, horahasta, 'defensor_id', v_defensor_id, 'supDef1', v_id);
            END IF;
            IF v_defensor2_id NOT IN ('DD00','D000','DP00') THEN
                CALL chequearSuperposicionDefensor(horadesde, horahasta, 'defensor2_id', v_defensor2_id, 'supDef2', v_id);
            END IF;
            IF v_defensor3_id NOT IN ('DD00','D000','DP00') THEN
                CALL chequearSuperposicionDefensor(horadesde, horahasta, 'defensor3_id', v_defensor3_id, 'supDef3', v_id);
            END IF;
            IF v_defensor4_id NOT IN ('DD00','D000','DP00') THEN
                CALL chequearSuperposicionDefensor(horadesde, horahasta, 'defensor4_id', v_defensor4_id, 'supDef4', v_id);
            END IF;            
            IF v_room_id IS NOT NULL AND v_room_id <> 10 THEN
                CALL chequearSuperposicionSala(horadesde, horahasta, v_room_id, 'supSala1', v_id);
            END IF;
            IF v_room2_id IS NOT NULL AND v_room2_id <> 10 THEN
                CALL chequearSuperposicionSala(horadesde, horahasta, v_room2_id, 'supSala2', v_id);
            END IF;
            -- IF v_room3_id IS NOT NULL AND v_room3_id <> 10 THEN
            --    CALL chequearSuperposicionSala(horadesde, horahasta, v_room3_id, 'supSala3', v_id);
            -- END IF;
        END IF;
    END LOOP revisarSuperposiciones;

    CLOSE cursor_grilla;

    -- Devolver resultados
    SELECT * FROM tempGrilla ORDER BY TIME(FROM_UNIXTIME(start_time)) ASC, TIME(FROM_UNIXTIME(end_time)) DESC;
END//
DELIMITER ;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;