<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.mx.dla.dda.contrato.listado.daos.ListadoDAO">
	
	<resultMap type='ListadoBD' id='listadoResultBD'>	    
	    <result property='indentar'           column='INDENTAR'/>
	    <result property='idPadre'            column='LINK_PADRE'/>
	    <result property='idContrato'         column='ID_CONTRATO'/>
	    <result property='idContratoOriginal' column='ID_CONTRATO_ORIGINAL'/>	    
	    <result property='concepto'           column='CONCEPTO'/>
	    <result property='numeroContrato'     column='NUMERO_CONTRATO'/>
	    <result property='fechaInicio'        column='FECHA_INICIO'/>
	    <result property='fechaFin'           column='FECHA_FIN'/>
	    <result property='estatus'            column='ESTATUS'/>
	    <result property='numDocumentos'      column='VER'/>	    	    	     
	 </resultMap>

	 <select id="getContratosListado" resultMap='listadoResultBD' parameterType="RequestListadoDTO">
	 	SELECT 	/*getContratosListado*/
			    CASE 
			        WHEN cnt.ID_CONTRATO is null
			                THEN 0
			        WHEN cnt.ID_CONTRATO_ORIGINAL = cnt.ID_CONTRATO and cnt.ID_CONTRATO_PADRE is null 
			                THEN 1
			        WHEN cnt.ID_TIPO_MOVIMIENTO = 3 and cnt.ID_CONTRATO_PADRE is null 
			                THEN 2
			        WHEN cnt.ID_TIPO_MOVIMIENTO = 3 and cnt.ID_CONTRATO_PADRE is not null 
			                THEN 3
			        ELSE 2
			    END as INDENTAR,    
			    NVL( cnt.ID_CONTRATO_PADRE, cnt.ID_CONTRATO_ORIGINAL ) as LINK_PADRE,
			    cnt.ID_CONTRATO_ORIGINAL,
			    cnt.ID_CONTRATO,    
			    CASE 
			        WHEN cnt.ID_CONTRATO_ORIGINAL = cnt.ID_CONTRATO and cnt.ID_CONTRATO_PADRE is null 
			                THEN est.DESC_ESTUDIO
			        WHEN cnt.ID_TIPO_MOVIMIENTO = 3 or cnt.ID_TIPO_MOVIMIENTO = 4
			                THEN 'Enmienda'
			        WHEN cnt.ID_CONTRATO is null 
			                THEN gpo.DESC_GRUPO
			        ELSE 'Addendum'
			    END as CONCEPTO,
			    cnt.NUMERO_CONTRATO, 
			    to_char( cnt.FECHA_INICIO, 'mm/dd/yyyy' ) as FECHA_INICIO, 
			    to_char( cnt.FECHA_FIN, 'mm/dd/yyyy' ) as FECHA_FIN, 
			    ett.DESC_ESTATUS as ESTATUS,
			    COUNT( dto.ID_CONTRATO_DOCTO ) as VER,
			    CASE 
			        WHEN cnt.ID_CONTRATO is null 
                   		THEN gpo.COLAPSA
			        ELSE  est.ID_GRUPO
			    END as COLAPSABLE
	from
	    DDA_T_CONTRATO cnt 
	    inner join DDA_C_ESTUDIO est on cnt.ID_ESTUDIO = est.ID_ESTUDIO
	    inner join DDA_C_ESTUDIO_GRUPO gpo on est.ID_GRUPO = gpo.ID_GRUPO
	    inner join DDA_C_CONTRATO_ESTATUS ett on cnt.ID_ESTATUS = ett.ID_ESTATUS    
	    left join DDA_T_CONTRATO_DOCTO dto on cnt.ID_CONTRATO = dto.ID_CONTRATO
	    where cnt.ID_TIPO_CONTRATO = #{request.tipoContrato} 
	     <if test="request.estudios != null">
	         and est.ID_ESTUDIO IN 
			 <foreach item="id" index="index" collection="request.estudios" open="(" separator="," close=")">
	         		#{id}
	         </foreach>	        			   
	    </if>	
	    <!-- rfd -->        		   	    
	   <if test="request.contrato != 'Todos'">
	   <bind name="patternC" value="'%' + request.contrato + '%'" />
	         <!-- AND cnt.numero_contrato = #{request.contrato}  -->
	       <!--  AND upper(cnt.numero_contrato) like upper(#{patternC}) 	 --> 	        			   
	       AND   cnt.id_contrato in (
             select nvl(cnta.id_contrato_padre,cnta.id_contrato_original) from dda_t_contrato cnta where upper(cnta.numero_contrato) LIKE upper(#{patternC})
            union
            select nvl(cnta.id_contrato_original,cnta.id_contrato) from dda_t_contrato cnta where upper(cnta.numero_contrato) LIKE upper(#{patternC})
            union 
            select cnta.id_contrato from dda_t_contrato cnta where upper(cnta.numero_contrato) LIKE upper(#{patternC})
        	)
	   </if> 
	     <if test="request.idBv != 'Todos' or request.nombreEstandar != 'Todos' or request.nombreUnico != 'Todos'" >
	         AND cnt.id_contrato in ( 
	         
	         SELECT nvl(b.id_contrato_padre,b.id_contrato_original) id_contrato
        FROM dda_t_contrato_titulo a, dda_t_contrato b
        WHERE
            a.id_contrato = b.id_contrato
            <if test="request.nombreEstandar != 'Todos'">
	   		 <bind name="patternNE" value="'%' + request.nombreEstandar + '%'" />
			        <!-- AND DESC_TITULO = #{request.nombreEstandar} -->
			         AND upper(a.DESC_TITULO) like upper(#{patternNE}) 			 	        			   
			   </if>
			   <if test="request.idBv != 'Todos'">
			         AND a.ID_BV = #{request.idBv} 			 
			   </if>
			   <if test="request.nombreUnico != 'Todos'">
			   		 <bind name="patternNU" value="'%' + request.nombreUnico + '%'" />
			        <!-- AND NOMBRE_UNICO = #{request.nombreUnico} 			 -->
			         AND upper(a.NOMBRE_UNICO) like upper(#{patternNU})       			   
			   </if>
            
    union
    SELECT nvl(b.id_contrato_original,b.id_contrato) id_contrato--, a.id_contrato,a.NOMBRE_UNICO,a.DESC_TITULO,a.ID_BV, b.id_contrato_original, b.id_contrato_padre
        FROM dda_t_contrato_titulo a, dda_t_contrato b
        WHERE
            a.id_contrato = b.id_contrato
           <if test="request.nombreEstandar != 'Todos'">
	   		 <bind name="patternNE" value="'%' + request.nombreEstandar + '%'" />
			        <!-- AND DESC_TITULO = #{request.nombreEstandar} -->
			         AND upper(a.DESC_TITULO) like upper(#{patternNE}) 			 	        			   
			   </if>
			   <if test="request.idBv != 'Todos'">
			         AND a.ID_BV = #{request.idBv} 			 
			   </if>
			   <if test="request.nombreUnico != 'Todos'">
			   		 <bind name="patternNU" value="'%' + request.nombreUnico + '%'" />
			        <!-- AND NOMBRE_UNICO = #{request.nombreUnico} 			 -->
			         AND upper(a.NOMBRE_UNICO) like upper(#{patternNU})       			   
			   </if>
    union
    SELECT b.id_contrato--, a.id_contrato,a.NOMBRE_UNICO,a.DESC_TITULO,a.ID_BV, b.id_contrato_original, b.id_contrato_padre
        FROM dda_t_contrato_titulo a, dda_t_contrato b
        WHERE
            a.id_contrato = b.id_contrato
            <if test="request.nombreEstandar != 'Todos'">
	   		 <bind name="patternNE" value="'%' + request.nombreEstandar + '%'" />
			        <!-- AND DESC_TITULO = #{request.nombreEstandar} -->
			         AND upper(a.DESC_TITULO) like upper(#{patternNE}) 			 	        			   
			   </if>
			   <if test="request.idBv != 'Todos'">
			         AND a.ID_BV = #{request.idBv} 			 
			   </if>
			   <if test="request.nombreUnico != 'Todos'">
			   		 <bind name="patternNU" value="'%' + request.nombreUnico + '%'" />
			        <!-- AND NOMBRE_UNICO = #{request.nombreUnico} 			 -->
			         AND upper(a.NOMBRE_UNICO) like upper(#{patternNU})       			   
			   </if>
	   )  			 	        			   
	   </if> 
	    <!-- rfd -->
	    <!-- 
	    <if test="request.estudios != null and request.rechazado != null and request.rechazado != true">
	         AND cnt.ID_ESTATUS != 10 			 	        			   
	    </if>
	    	    
	    <if test="request.estudios == null and request.rechazado != null and request.rechazado != true">
	         where cnt.ID_ESTATUS != 10 			 	        			   
	    </if>
	     -->
		group by
			    grouping sets(
			                (
			                    cnt.ID_TIPO_MOVIMIENTO, cnt.ID_ESTATUS, 
			                    cnt.ID_CONTRATO, cnt.ID_CONTRATO_ORIGINAL, cnt.ID_CONTRATO_PADRE,
			                    est.ID_ESTUDIO, gpo.DESC_GRUPO, gpo.ORDEN, est.DESC_ESTUDIO, 
			                    est.ORDEN, cnt.ID_CONTRATO, cnt.NUMERO_CONTRATO, 
			                    cnt.FECHA_INICIO, cnt.FECHA_FIN, ett.DESC_ESTATUS, gpo.COLAPSA, est.ID_GRUPO
			                ),
			                (   gpo.DESC_GRUPO, gpo.ORDEN, gpo.COLAPSA, est.ID_GRUPO )
			            )
			order by gpo.ORDEN nulls first, est.ORDEN nulls first,
	            NVL( cnt.ID_CONTRATO_PADRE, cnt.ID_CONTRATO_ORIGINAL ) nulls first,
	            cnt.ID_CONTRATO_ORIGINAL,             
	            cnt.NUMERO_CONTRATO
	 </select> 	 	
  	   	   	   	  
	 <select id='crearAdendum' parameterType="map" statementType="CALLABLE">   		 
     		 { CALL DDA_CREARADDENDUM(#{idContrato},#{idMovimiento}, #{idContratoNuevo, mode=OUT, jdbcType=INTEGER} ) } 		     		  			       		   	
	 </select>
	 
	 <select id='crearEmnienda' parameterType="map" statementType="CALLABLE">   		 
     		 { CALL DDA_CREARENMIENDA(#{idContrato}, #{tipoContrato}, #{idMovimiento}, #{idNuevo, mode=OUT, jdbcType=INTEGER} ) } 		     		  			       		   	
	 </select>
	  	 
</mapper>