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

<mapper namespace="com.mx.dla.dda.catalogos.daos.CatalogosDAO">

	<resultMap id="catalogo"
		type="com.mx.dla.dda.catalogos.dtos.CatalogoDTO">
		<id property="value" column="ID" />
		<result property="desc" column="DESCRIP" />
	</resultMap>

	<select id="buscarPeriodosActivos" resultMap="catalogo" >
		select ID_PERIODO as ID, DESC_PERIODO as DESCRIP from DDA_C_PERIODO where ESTATUS = 1 ORDER by ID_PERIODO asc
	</select>
	
	<select id="buscarCategoriasActivas" resultMap="catalogo" >
		select ID_CATEGORIA as ID ,DESC_CATEGORIA as DESCRIP from DDA_C_CATEGORIA where ESTATUS = 1 ORDER by ID_CATEGORIA asc
	</select>
	
	<select id="buscarCategoriasMGPorEstudioActivas" resultMap="catalogo" parameterType="long" >
		select c.ID_CATEGORIA as ID, c.DESCRIPCION as DESCRIP from DDA_C_CATEGORIA_MG c
		inner join DDA_C_CATEGORIA_MG_ESTUDIO ce on c.ID_CATEGORIA =  ce.ID_CATEGORIA
		where ce.ID_ESTUDIO = #{idEstudio}
	</select>
	
	<select id="buscarFormatos" resultMap="catalogo" >
		select ID_FORMATO as ID ,DESCRIPCION as DESCRIP from DDA_C_FORMATO
	</select>
	
	<select id="buscarCatListaByContrato" resultMap="catalogo" >
		select ID_LISTA as ID,TO_CHAR(FECHA_INICIO, 'MM/DD/YYYY') as DESCRIP   from DDA_${prefix}_CONTRATO_LISTA where ID_CONTRATO=#{idContrato}
		order by ID_LISTA desc
	</select>
	
	<select id="buscarListaTitlos" resultType="string" >
       select DESC_TITULO from DDA_C_TITULO where ESTATUS=1 
	</select>
	
	<select id="buscarCatalogoTitulos" resultMap="catalogo" >
       select ID_TITULO  as ID ,DESC_TITULO as DESCRIP from DDA_C_TITULO where ESTATUS=1 
	</select>
	
	<select id="buscarListaTitlosPorEstudio" resultType="string" parameterType="long" >
       select DESC_TITULO from DDA_C_TITULO where ESTATUS=1 AND ID_ESTUDIO=#{idEstudio}
	</select>
	
	<select id="buscarCatalogoTitulosPorEstudio" resultMap="catalogo" parameterType="long"  >
       select ID_TITULO  as ID ,DESC_TITULO as DESCRIP from DDA_C_TITULO where ESTATUS=1 AND ID_ESTUDIO=#{idEstudio}
	</select>
	
	<select id="buscarCatalogoTitulosCPV" resultMap="catalogo" parameterType="long"  >
	select nombre_unico as DESCRIP ,ID_TITULO_CNT as ID from(
			SELECT nombre_unico,ID_TITULO_CNT, ROW_NUMBER()OVER (PARTITION BY nombre_unico ORDER BY ID_TITULO_CNT asc) AS cont
			FROM DDA_T_CONTRATO_TITULO where id_contrato in (
				SELECT id_contrato FROM DDA_T_CONTRATO where id_estudio = #{idEstudio}
			)
		) where cont = 1
		order by nombre_unico asc
	</select>
	
	<!-- rfd -->
	
	<select id="buscarContratosPorEstudio" resultMap="catalogo" parameterType="long"  >
		SELECT numero_contrato as DESCRIP ,id_contrato as ID FROM DDA_T_CONTRATO where id_estudio =#{idEstudio}
	</select>
	<select id="buscarCatalogoTitulosDC" resultMap="catalogo" parameterType="long">
	select nombre_unico as DESCRIP ,ID_TITULO_CNT as ID from(
			SELECT nombre_unico,ID_TITULO_CNT, ROW_NUMBER()OVER (PARTITION BY nombre_unico ORDER BY ID_TITULO_CNT asc) AS cont
			FROM DDA_T_CONTRATO_TITULO where id_contrato = #{idcontrato}
		) where cont = 1
		order by nombre_unico asc		
	</select>
	<select id="buscarCatalogoTemporadasDC" resultMap="catalogo" >
		SELECT numero_contrato as DESCRIP ,id_contrato as ID FROM DDA_T_CONTRATO where id_contrato =#{idcontrato}
	</select>
	<!-- rfd -->
	
	<select id="buscarCatalogoTemporadas" resultMap="catalogo"  >
      select distinct temporada as DESCRIP, temporada as ID from (
			select
			CASE 
			    WHEN temporada is null and episodio is not null THEN '00'
			    when temporada is null and episodio is null then 'PELICULA'
			    when temporada is not null and episodio is not null THEN temporada
			END as temporada
			from DDA_T_CONTRATO_TITULO where nombre_unico in (select nombre_unico from DDA_T_CONTRATO_TITULO where 
			ID_TITULO_CNT = #{nombreUnico})
		)
    </select>

	<resultMap id="estudio" type="EstudioDTO">
		<id property="idEstudio" column="ID_ESTUDIO" />
		<result property="descripcionEstudio" column="DESC_ESTUDIO" />
	</resultMap>
	
	<resultMap id="categoria" type="CategoriaDTO">
		<id property="id" column="ID_CATEGORIA" />
		<result property="descripcion" column="DESC_CATEGORIA" />
		<result property="estatus" column="ESTATUS" />
		<result property="seAgrupa" column="SE_AGRUPA" />
	</resultMap>
	
	<resultMap id="servicio" type="ServicioDTO">
		<id property="id" column="ID_TIPO_SERVICIO" />
		<result property="descripcion" column="DESC_SERVICIO" />
	</resultMap>
	
	<resultMap id="territorio" type="TerritorioDTO">
		<id property="id" column="ID_TERRITORIO" />
		<result property="descripcion" column="DESC_TERRITORIO" />
	</resultMap>
	
	<resultMap id="medioEntrega" type="MedioEntregaDTO">
		<id property="id" column="ID_MEDIO_ENTREGA" />
		<result property="descripcion" column="DESC_MEDIO_ENTREGA" />
	</resultMap>

    <resultMap id='estatus' type='EstatusDTO' >	    
	    <id     property='idEstatus'   column='ID_ESTATUS'/>
	    <result property='descEstatus' column='DESC_ESTATUS'/>
	    <result property='orden'       column='ORDEN'/>
	    <result property='bloqueo'     column='BLOQUEO'/>
	</resultMap>
	 
	<resultMap type='EmpresaVentaDTO' id='empresaVentaResult'>	    
	      <id     property='idEmpresa'    column='ID_EMPRESA'/>
	      <result property='idPais'       column='ID_PAIS'/>
	      <result property='descripcion'  column='DESCRIPCION'/>
	      <result property='estatus'      column='ESTATUS'/>
	      <result property='orden'        column='ORDEN'/>	      	  
	</resultMap>
	  
	<resultMap type='PaisVentaDTO' id='paisVentaResult'>	    
	      <id     property='idPais'          column='ID_PAIS'/>
	      <result property='idTerritorio'    column='ID_TERRITORIO'/>
	      <result property='idRegion'        column='ID_REGION'/>
	      <result property='descripcionPais' column='DESCRIPCION'/>
	      <result property='estatus'         column='ESTATUS'/>	    
	</resultMap>
	  
	<resultMap type='RegionVentaDTO' id='regionVentaResult'>	    
	      <id     property='idRegion'        column='ID_REGION'/>
	      <result property='descripcion'     column='DESCRIPCION'/>	      	   
	</resultMap>

    <resultMap type='ReleaseDTO' id='releaseResult'>	    
	      <id     property='idRelease'    column='ID_RELEASE'/>
	      <result property='descripcion'  column='DESCRIPCION'/>	    
	</resultMap>
	
	<resultMap type='FormatoDTO' id='formatoResult'>	    
	      <id     property='idFormato'    column='ID_FORMATO'/>
	      <result property='descripcion'  column='DESCRIPCION'/>	    
	</resultMap>	

	<!-- MAPPER PASA SELECT DE TIPO SUSCRIPTOR -->
	<resultMap id="resultTipoSuscriptor" type="TipoSuscriptorDTO">
		<id property="idTipoSuscriptor" column="ID_TIPO_SUSCRIPTOR" />
		<result property="descripcion" column="DESC_TIPO" />
		<result property="claveTipo" column="CLAVE_TIPO" />
		<result property="estatus" column="ESTATUS" />
	</resultMap>
	
	<!-- MAPPER PASA SELECT DE TIEMPO DE VIDA -->
	<resultMap id="resultTiempoVida" type="TiempoVidaDTO">
		<id property="idTiempoVida" column="ID_TIEMPO_VIDA" />
		<result property="descripcion" column="DESC_TIEMPO_VIDA" />
		<result property="estatus" column="ESTATUS" />
	</resultMap>
	
	<select id="obtenerEstudios" resultMap="estudio" parameterType="map">
		SELECT ID_ESTUDIO, DESC_ESTUDIO FROM DDA_C_ESTUDIO ORDER BY DESC_ESTUDIO ASC
	</select>
	
	<select id="obtenerCategorias" resultMap="categoria" parameterType="map">
		SELECT ID_CATEGORIA, DESC_CATEGORIA FROM DDA_C_CATEGORIA
	</select>
	
	<select id="obtenerServicios" resultMap="servicio" parameterType="map">
		SELECT ID_TIPO_SERVICIO, DESC_SERVICIO FROM DDA_C_SERVICIO_TIPO
	</select>
	
	<select id="obtenerTerritorios" resultMap="territorio" parameterType="map">
		SELECT ID_TERRITORIO, DESC_TERRITORIO FROM DDA_C_TERRITORIO
	</select>
	
	<select id="obtenerMediosEntrega" resultMap="medioEntrega" parameterType="map">
		SELECT ID_MEDIO_ENTREGA, DESC_MEDIO_ENTREGA FROM DDA_C_MEDIO_ENTREGA
	</select>
	
	<select id="obtenerSqIdContrato" resultType="int">
		select DDA_SQ1_CONTRATO.nextVal from DUAL
	</select>

	<select id='obtenerEstatus' resultMap='estatus'>   		 
     		  SELECT ID_ESTATUS, DESC_ESTATUS, ORDEN, BLOQUEO FROM DDA_C_CONTRATO_ESTATUS order by ORDEN		     		  			       		   	
	</select>
	
	<select id='getEmpresasVenta' resultMap='empresaVentaResult' >   		 
     		SELECT E.ID_EMPRESA, E.ID_PAIS, E.DESCRIPCION, E.ESTATUS,  E.ORDEN  
     		       FROM DDA_C_EMPRESA_VENTA E     		       
     		       <!-- ORDER BY ORDEN ASC -->  	
     		       ORDER BY DESCRIPCION ASC 		             		     		  			       		   		
	</select>

	<select id='getPaises'        resultMap='paisVentaResult' >   		 
     		SELECT ID_PAIS, ID_TERRITORIO, ID_TERRITORIO, ID_REGION, DESCRIPCION, ESTATUS FROM DDA_C_PAIS_VENTA ORDER BY DESCRIPCION     		            		       	        			             		     		  			       		   
	</select>
	
	<select id='getRegiones'        resultMap='regionVentaResult' >   		 
     		SELECT ID_REGION, DESCRIPCION FROM DDA_C_REGION_VENTA      		            		       	        			             		     		  			       		   
	</select>
	  
	<select id='getPaisXId' resultMap='paisVentaResult' parameterType='map'>   		 
     		SELECT ID_PAIS, ID_TERRITORIO, ID_REGION, ESTATUS,  DESCRIPCION  
     		       FROM DDA_C_PAIS_VENTA      		       
     		       WHERE  ID_PAIS = #{idPais} 			             		     		  			       		   		
	</select>
	  
	<select id='getRegionXId' resultMap='regionVentaResult' parameterType='map'>   		 
     		SELECT ID_REGION, DESCRIPCION  
     		       FROM DDA_C_REGION_VENTA      		       
     		       WHERE  ID_REGION = #{id} 			             		     		  			       		   		
	</select>
	
	<select id='getReleases' resultMap='releaseResult' >   		 
     		SELECT ID_RELEASE, DESCRIPCION FROM DDA_C_RELEASE_DATE		             		     		  			       		   		
	</select>
	
	<select id='getFormatos' resultMap='formatoResult' >   		 
     		SELECT ID_FORMATO, DESCRIPCION FROM DDA_C_FORMATO		             		     		  			       		   		
	</select>	  	 	

	<select id="buscarCategoriasMGPorEstudioYDesc" resultMap="catalogo"  >
		select c.ID_CATEGORIA as ID, c.DESCRIPCION as DESCRIP from DDA_C_CATEGORIA_MG c
		inner join DDA_C_CATEGORIA_MG_ESTUDIO ce on c.ID_CATEGORIA =  ce.ID_CATEGORIA
		where ce.ID_ESTUDIO = #{idEstudio} and c.DESCRIPCION = #{desc}
	</select>

	<select id="buscarCatalogoTiempoVida" resultMap="catalogo"  >
       select ID_TIEMPO_VIDA as ID, DESC_TIEMPO_VIDA as DESCRIP from DDA_C_TIEMPO_VIDA where ESTATUS =1
	</select>
	<select id="buscarTiempoVidaPorDesc" resultMap="catalogo" >
       select ID_TIEMPO_VIDA as ID, DESC_TIEMPO_VIDA as DESCRIP from DDA_C_TIEMPO_VIDA where ESTATUS =1 and DESC_TIEMPO_VIDA=#{desc}
	</select>
	
	<select id="buscarCatalogoReleaseDate" resultMap="catalogo"  >
       select ID_RELEASE as ID ,DESCRIPCION as DESCRIP from DDA_C_RELEASE_DATE 
	</select>
	<select id="buscarReleaseDatePorDesc" resultMap="catalogo" >
       select ID_RELEASE as ID ,DESCRIPCION as DESCRIP from DDA_C_RELEASE_DATE where DESCRIPCION=#{desc}
	</select>
	<select id="buscarEstudios" resultMap="catalogo" >
       select ID_ESTUDIO as ID ,SIGLA_DDA as DESCRIP from DDA_C_ESTUDIO
	</select>
	
 	<select id="buscarCategoriaPagoTodosLosPeriodos" resultMap="catalogo" >
		SELECT DISTINCT  CPC.DESC_CATEGORIA AS ID, CPC.DESC_CATEGORIA AS DESCRIP
		FROM DDA_T_CONTRATO_PAGO CP 
		JOIN DDA_T_CONTRATO_PAGO_CATEG CPC ON (CP.ID_CONTRATO_PAGO = CPC.ID_CONTRATO_PAGO) 
		WHERE   
		     CP.ID_CONTRATO = #{idContrato}
		     ORDER BY CPC.DESC_CATEGORIA ASC
	</select> 
		
	<select id="buscarCategoriaPagoPorDesc" resultMap="catalogo" >
		select 
			ID_PAGO_CATEG AS ID, DESC_CATEGORIA AS DESCRIP 
		from 
			DDA_T_CONTRATO_PAGO_CATEG  
		where 
			ID_CONTRATO = #{idContrato} 
			AND DESC_CATEGORIA = #{description}
	</select>
	
	<select id="buscarCategoriaPago" resultMap="catalogo" >		
		SELECT  
			CPC.ID_PAGO_CATEG AS ID, CPC.DESC_CATEGORIA AS DESCRIP 
		FROM   DDA_T_CONTRATO_PAGO_CATEG CPC  
		where CPC.ID_CONTRATO = #{idContrato}
		ORDER BY CPC.DESC_CATEGORIA ASC
	</select>
	

    <select id="obtenerTipoSuscriptor" resultMap="resultTipoSuscriptor" parameterType="map" >
			SELECT ID_TIPO_SUSCRIPTOR,DESC_TIPO,CLAVE_TIPO,ESTATUS FROM DDA_C_SUSCRIPTOR_TIPO WHERE ESTATUS=1
	</select>	
	
	<select id="buscarContratoMasRecientePorEstudio"  resultMap="catalogo" >
		select ID_CONTRATO_ORIGINAL as ID , max(ID_CONTRATO) as DESCRIP from DDA_T_CONTRATO where
			ID_ESTUDIO = #{idEstudio} 
			and ID_CONTRATO_ORIGINAL !=  #{idContratoOrig} 
			and (ID_ESTATUS = 4
			or ID_ESTATUS = 5)
			group BY ID_CONTRATO_ORIGINAL
	</select>
	
	<select id="buscaCategoriasPorContrato" resultMap="catalogo" >
		select c.ID_CATEGORIA as id , c.DESC_CATEGORIA as DESCRIP from DDA_T_CONTRATO_CATEGORIA tc
		inner join DDA_C_CATEGORIA c on c.ID_CATEGORIA = tc.ID_CATEGORIA
		where tc.ID_CONTRATO = #{idContrato}
		and c.ESTATUS =1
	</select>
	
	<select id="buscarListasByContrato" resultMap="catalogo" >
		select ID_LISTA as ID,TO_CHAR(FECHA_INICIO, 'YYYY') as DESCRIP   from DDA_${prefix}_CONTRATO_LISTA where ID_CONTRATO=#{idContrato}
		order by ID_LISTA desc
	</select>
	
	<select id="obtenCatalogoEmpresas" resultMap="catalogo" >
       SELECT ID_EMPRESA AS ID, DESC_EMPRESA AS DESCRIP  
       FROM DLA_C_EMPRESA 
       WHERE 1 = 1
         <if test="idEmpresas != null"> 
           AND ID_EMPRESA IN 
             <foreach item="id" index="index" collection="idEmpresas" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
         <if test="idPaises != null"> 
           AND ID_PAIS IN 
             <foreach item="id" index="index" collection="idPaises" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>      
         <if test="descEmpresas != null"> 
           AND DESC_EMPRESA IN 
             <foreach item="id" index="index" collection="descEmpresas" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
         <if test="staEmpresas != null"> 
           AND ESTATUS_EMPRESA IN 
             <foreach item="id" index="index" collection="staEmpresas" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
   	</select>

   	<select id="obtenCatalogoAreas" resultMap="catalogo" >
       SELECT ID_AREA AS ID, DESC_AREA AS DESCRIP  
       FROM DDA_C_AREA
       WHERE 1 = 1
         <if test="idAreas != null"> 
           AND ID_AREA IN 
             <foreach item="id" index="index" collection="idAreas" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
         <if test="descAreas != null"> 
           AND DESC_AREA IN 
             <foreach item="id" index="index" collection="descAreas" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>      
         <if test="indAutorizaContratoAreas != null"> 
           AND AUTORIZA_CONTRATO IN 
             <foreach item="id" index="index" collection="indAutorizaContratoAreas" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
         <if test="ordenAreas != null"> 
           AND ORDEN IN 
             <foreach item="id" index="index" collection="ordenAreas" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
   	</select>

   	<select id="obtenCatalogoPerfiles" resultMap="catalogo" >
       SELECT ID_PERFIL AS ID, DESC_PERFIL AS DESCRIP  
       FROM DLA_C_PERFIL
       WHERE 1 = 1
         <if test="idPerfiles != null"> 
           AND ID_PERFIL IN 
             <foreach item="id" index="index" collection="idPerfiles" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
         <if test="descPerfiles != null"> 
           AND DESC_PERFIL IN 
             <foreach item="id" index="index" collection="descPerfiles" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>      
         <if test="staPerfiles != null"> 
           AND ESTATUS_PERFIL IN 
             <foreach item="id" index="index" collection="staPerfiles" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
   	</select>

   	<select id="obtenCatalogoEstudios" resultMap="catalogo" >
       SELECT ID_ESTUDIO AS ID, DESC_ESTUDIO AS DESCRIP  
       FROM DDA_C_ESTUDIO
       WHERE 1 = 1
         <if test="idEstudios != null"> 
           AND ID_ESTUDIO IN 
             <foreach item="id" index="index" collection="idEstudios" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
         <if test="idSapEstudios != null"> 
           AND ID_SAP IN 
             <foreach item="id" index="index" collection="idSapEstudios" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>      
         <if test="descEstudios != null"> 
           AND DESC_ESTUDIO IN 
             <foreach item="id" index="index" collection="descEstudios" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
         <if test="monedaEstudios != null"> 
           AND MONEDA IN 
             <foreach item="id" index="index" collection="monedaEstudios" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
         <if test="codigoPaisEstudios != null"> 
           AND CODIGO_PAIS IN 
             <foreach item="id" index="index" collection="codigoPaisEstudios" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>      
         <if test="nombrePaisEstudios != null"> 
           AND NOMBRE_PAIS IN 
             <foreach item="id" index="index" collection="nombrePaisEstudios" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
         <if test="sociedadEstudios != null"> 
           AND SOCIEDAD IN 
             <foreach item="id" index="index" collection="sociedadEstudios" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
         <if test="siglaDdaEstudios != null"> 
           AND SIGLA_DDA IN 
             <foreach item="id" index="index" collection="siglaDdaEstudios" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>      
         <if test="ordenEstudios != null"> 
           AND ORDEN IN 
             <foreach item="id" index="index" collection="ordenEstudios" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
         <if test="idGroupEstudios != null"> 
           AND ID_GRUPO IN 
             <foreach item="id" index="index" collection="idGrupoEstudios" open="(" separator="," close=")">
   		           		#{id}
             </foreach>
         </if>
   	</select>
   	
<!--    	SELECT u.EXPEDIENTE, REGEXP_REPLACE(TRIM(u.PATERNO) || ' ' || TRIM(u.MATERNO) || ' ' || TRIM(u.NOMBRE), '\s{2,}', ' ') AS NOMBRE, 
       e.DESC_EMPRESA, e.ID_EMPRESA, a.DESC_AREA, a.ID_AREA, p.DESC_PERFIL, p.ID_PERFIL, u.EMAIL, 
       DECODE(u.ESTATUS, 1, 'Activo', 'Inactivo') AS ESTATUS, u.PASSWORD 
FROM DLA_C_USUARIO u
INNER JOIN DLA_C_EMPRESA e ON e.ID_EMPRESA = u.ID_EMPRESA
INNER JOIN DDA_C_AREA a ON a.ID_AREA = u.ID_AREA
INNER JOIN DLA_C_USUARIO_PERFIL up ON up.EXPEDIENTE = u.EXPEDIENTE
INNER JOIN DLA_C_PERFIL p ON p.ID_PERFIL = up.ID_PERFIL
ORDER BY u.ESTATUS DESC, e.DESC_EMPRESA, u.PATERNO -->
	
</mapper>
