<?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.transaccion.fees.daos.FeesTransaccionDAO">

	<resultMap id="feesTminimos" type="FeesTMinimoDTO">
		<result property="desde"       column="DESDE" />
		<result property="hasta"       column="HASTA" />
		<result property="categoria"   column="CATEGORIAMG" />
		<result property="seccion"     column="SECCION" />
 	    <result property="revenue"     column="REVENUE" />
 	    <result property="idCategoria" column="ID_FS_CATEGORIAMG" /> 	    
 	    <collection property="valores" column="cat" ofType="FeesTMinValoresDTO" javaType="ArrayList" resultMap="feesTminValores"/>  						
	</resultMap>
	
	<resultMap id="feesTminValores" type="FeesTMinValoresDTO">
		<result property="inicio"   column="FECHA_INICIO" />
		<result property="fin"      column="FECHA_FIN" />
		<result property="valor"    column="MINIMO" />
		<result property="seccion"  column="SECCION" />		  						
	</resultMap>
	
	<resultMap id="feesTPaises" type="FeesTImpuestoDTO">
		<result property="impuesto"  column="IMPUESTO" />
		<result property="pais"      column="DESCRIPCION" />
		<result property="idPais"    column="ID_PAIS" />
		<result property="aplica"    column="APLICA" />
		<result property="incluido"  column="INCLUIDO" />				  					
	</resultMap>
	
	<resultMap id="feesTRevenue" type="FeesTRevenueDTO">
		<result property="release"     column="RELEASE_DATE" />
		<result property="ddaPto"      column="P_DDA" />
		<result property="estudioPto"  column="P_ESTUDIO" />
		<result property="desde"       column="DESDE" />
		<result property="hasta"       column="HASTA" />						  					
	</resultMap>
	
	<resultMap id="feesTPrecio" type="FeesTPrecioDTO">
		<result property="release"   column="RELEASE" />
		<result property="formato"   column="FORMATO" />
		<result property="idPais"    column="ID_PAIS" />
		<result property="moneda"    column="MONEDA_LOCAL" />
		<result property="dolar"     column="DOLAR" />				  					
	</resultMap>

	<resultMap id="feesTFormatos" type="FormatoDTO">
		<result property="idFormato"     column="ID_FORMATO" />
		<result property="descripcion"   column="DESCRIPCION" />				  					
	</resultMap>
 				
 	<resultMap id="tipoCambio" type="TipoCambioTDTO">
		<result property="id"     column="ID_TIPOCAMBIO" />
		<result property="desc"   column="DESCRIPCION" />				  					
	</resultMap>
				
	<!-- Consultas para fees premier -->	
	<select id="obtenerMinimosAnioValoresPremier" resultMap="feesTminValores" parameterType="map">
		Select to_char(FECHA_INICIO, 'mm/dd/yyyy') FECHA_INICIO, to_char(FECHA_FIN, 'mm/dd/yyyy') FECHA_FIN, MINIMO
		       from DDA_T_CNT_FS_MINIMOANNIO where SECCION = 'RP' and ID_CONTRATO = #{idContrato}
		       order by DDA_T_CNT_FS_MINIMOANNIO.ID_MINIMO 		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerMinimosPremier" resultMap="feesTminimos" parameterType="map">
		Select mg.ID_FS_CATEGORIAMG, mg.DESDE, mg.HASTA, mg.CATEGORIAMG,
       		to_char(cat.FECHA_INICIO, 'mm/dd/yyyy') FECHA_INICIO,  to_char(cat.FECHA_FIN, 'mm/dd/yyyy') FECHA_FIN, cat.MINIMO
       		from DDA_T_CNT_FS_CATEGORIAMG mg 
       		inner join DDA_T_CNT_FS_MINIMOCAT cat ON mg.ID_FS_CATEGORIAMG = cat.ID_FS_CATEGORIAMG
       		where SECCION = 'RP' and ID_CONTRATO = #{idContrato}
       		order by cat.ID_MINIMO 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerReleasePremier" resultMap="feesTminimos" parameterType="map">
		   Select DESDE, HASTA, RELEASE_DATE CATEGORIAMG, ID_RELEASE_DATE ID_FS_CATEGORIAMG from DDA_T_CNT_FS_RELEASEDATE where ID_CONTRATO = #{idContrato} order by ID_RELEASE_DATE  		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerPaisesPremier" resultMap="feesTPaises" parameterType="map">
			select distinct(DDA_C_PAIS_VENTA.DESCRIPCION), DDA_C_PAIS_VENTA.ID_PAIS, DDA_C_PAIS_VENTA.IMPUESTO from DDA_C_FILIAL
		       inner join DDA_C_EMPRESA_VENTA         ON DDA_C_FILIAL.ID_EMPRESA              = DDA_C_EMPRESA_VENTA.ID_EMPRESA
		       inner join DDA_C_PAIS_VENTA            ON DDA_C_EMPRESA_VENTA.ID_PAIS          = DDA_C_PAIS_VENTA.ID_PAIS
		       inner join DDA_T_CONTRATO_DEAL_FILIAL  ON DDA_T_CONTRATO_DEAL_FILIAL.ID_FILIAL = DDA_C_FILIAL.ID_FILIAL
               where DDA_T_CONTRATO_DEAL_FILIAL.ID_CONTRATO = #{idContrato}
		       order by DDA_C_PAIS_VENTA.DESCRIPCION   				         		     		  			       		   				    		 		 				         		     		  			       		   				    
	</select>

	<select id="obtenerImpuestosPremier" resultMap="feesTPaises" parameterType="map">
		select ID_PAIS, APLICA, INCLUIDO from DDA_T_CNT_FS_IMPUESTO where ID_CONTRATO = #{idContrato} and SECCION = 'RP' 		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerRevenuePremier" resultMap="feesTRevenue" parameterType="map">
		 select P_DDA, P_ESTUDIO, RELEASE_DATE from DDA_T_CNT_FS_REVENUE_RP r 
         inner join DDA_T_CNT_FS_RELEASEDATE c on r.ID_RELEASE_DATE = c.ID_RELEASE_DATE
         where r.ID_CONTRATO = #{idContrato} order by RELEASE_DATE 		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerPrecioPremier" resultMap="feesTPrecio" parameterType="map">
		 select r.RELEASE_DATE RELEASE, f.DESCRIPCION FORMATO, v.ID_PAIS, v.MONEDA_LOCAL, v.DOLAR from DDA_T_CNT_FS_PPE_RP v
			     inner join DDA_C_FORMATO f on v.ID_FORMATO = f.ID_FORMATO
			     inner join DDA_T_CNT_FS_RELEASEDATE r on v.ID_RELEASE_DATE = r.ID_RELEASE_DATE	
			     where v.ID_CONTRATO = #{idContrato}	      
			     order by v.ID_PAIS, r.ID_RELEASE_DATE, f.ID_FORMATO 		 				         		     		  			       		   				    
	</select>
		
	<select id="obtenerNumRegPrecios" resultType="int" parameterType="map">
		 select count(*) from DDA_T_CNT_FS_PPE_RP where ID_CONTRATO = #{idContrato} group by ID_PAIS      		     		  			       		   				    
	</select>
		 
	<select id="obtenerFormatos" resultType="String" >
		 select descripcion from DDA_C_FORMATO order by ID_FORMATO      		     		  			       		   				    
	</select>
	
	<select id="getFormatos" resultMap="feesTFormatos" >
		 select descripcion, ID_FORMATO from DDA_C_FORMATO order by ID_FORMATO      		     		  			       		   				    
	</select>
	
	<!-- Consultas para fees libreria -->
	<select id="obtenerMinimosAnioValoresLibreria" resultMap="feesTminValores" parameterType="map">
		Select to_char(FECHA_INICIO, 'mm/dd/yyyy') FECHA_INICIO, to_char(FECHA_FIN, 'mm/dd/yyyy') FECHA_FIN, MINIMO
		       from DDA_T_CNT_FS_MINIMOANNIO where SECCION = 'RL' and ID_CONTRATO = #{idContrato}
		       order by DDA_T_CNT_FS_MINIMOANNIO.ID_MINIMO 		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerMinimosLibreria" resultMap="feesTminimos" parameterType="map">
		Select mg.ID_FS_CATEGORIAMG, mg.DESDE, mg.HASTA, mg.CATEGORIAMG,
       		to_char(cat.FECHA_INICIO, 'mm/dd/yyyy') FECHA_INICIO,  to_char(cat.FECHA_FIN, 'mm/dd/yyyy') FECHA_FIN, cat.MINIMO
       		from DDA_T_CNT_FS_CATEGORIAMG mg 
       		inner join DDA_T_CNT_FS_MINIMOCAT cat ON mg.ID_FS_CATEGORIAMG = cat.ID_FS_CATEGORIAMG
       		where SECCION = 'RL' and ID_CONTRATO = #{idContrato} and REVENUE = 0
       		order by cat.ID_MINIMO 		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerMinimosLibreriaRevenue" resultMap="feesTminimos" parameterType="map">
		Select mg.DESDE, mg.HASTA, mg.CATEGORIAMG, mg.ID_FS_CATEGORIAMG       		
       		from DDA_T_CNT_FS_CATEGORIAMG mg        		
       		where SECCION = 'RL' and ID_CONTRATO = #{idContrato}  		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerImpuestosLibreria" resultMap="feesTPaises" parameterType="map">
		select ID_PAIS, APLICA, INCLUIDO from DDA_T_CNT_FS_IMPUESTO where ID_CONTRATO = #{idContrato} and SECCION = 'RL' 		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerPrecioLibreria" resultMap="feesTPrecio" parameterType="map">
		 select f.DESCRIPCION FORMATO, v.ID_PAIS, v.MONEDA_LOCAL, v.DOLARES DOLAR
          		from DDA_T_CNT_FS_PPE_RL v
			    inner join DDA_C_FORMATO f on v.ID_FORMATO = f.ID_FORMATO			     
          		where v.ID_CONTRATO = #{idContrato}
			    order by v.ID_PAIS, f.ID_FORMATO      		     		  			       		   				    
	</select>			 				
			
	<select id="obtenerRevenueGlobalLiberia" resultMap="feesTRevenue" parameterType="map">
		select P_DLA P_DDA, P_ESTUDIO P_ESTUDIO from DDA_T_CNT_FS_REVENUE_RLGL where ID_CONTRATO = #{idContrato} 		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerRevenueBoxLibreria" resultMap="feesTRevenue" parameterType="map">
		select DESDE, HASTA, P_ESTUDIO P_ESTUDIO, P_DLA P_DDA, CATEGORIAMG RELEASE_DATE from DDA_T_CNT_FS_REVENUE_RLMG r
         inner join DDA_T_CNT_FS_CATEGORIAMG ca on r.ID_FS_CATEGORIAMG = ca.ID_FS_CATEGORIAMG
         where ca.ID_CONTRATO = #{idContrato} 		 				         		     		  			       		   				    
	</select>
		     
		     
	<!-- Consultas para fees est -->
	<select id="obtenerMinimosAnioValoresEst" resultMap="feesTminValores" parameterType="map">
		Select to_char(FECHA_INICIO, 'mm/dd/yyyy') FECHA_INICIO, to_char(FECHA_FIN, 'mm/dd/yyyy') FECHA_FIN, MINIMO
		       from DDA_T_CNT_FS_MINIMOANNIO where SECCION = 'VT' and ID_CONTRATO = #{idContrato}
		       order by DDA_T_CNT_FS_MINIMOANNIO.ID_MINIMO 		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerMinimosEst" resultMap="feesTminimos" parameterType="map">
		Select mg.DESDE, mg.HASTA, mg.CATEGORIAMG,
       		to_char(cat.FECHA_INICIO, 'mm/dd/yyyy') FECHA_INICIO,  to_char(cat.FECHA_FIN, 'mm/dd/yyyy') FECHA_FIN, cat.MINIMO
       		from DDA_T_CNT_FS_CATEGORIAMG mg 
       		inner join DDA_T_CNT_FS_MINIMOCAT cat ON mg.ID_FS_CATEGORIAMG = cat.ID_FS_CATEGORIAMG
       		where SECCION = 'VT' and ID_CONTRATO = #{idContrato}
       		order by cat.ID_MINIMO 		 				     		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerImpuestosEst" resultMap="feesTPaises" parameterType="map">
		select ID_PAIS, APLICA, INCLUIDO from DDA_T_CNT_FS_IMPUESTO where ID_CONTRATO = #{idContrato} and SECCION = 'VT' 		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerTiers" resultMap="feesTFormatos" >
		 select ID_TIER ID_FORMATO, DESCRIPCION from DDA_C_TIER order by ORDEN      		     		  			       		   				    
	</select>
	
	<select id="obtenerPrecioMovies" resultMap="feesTPrecio" parameterType="map">
		   select t.DESCRIPCION as RELEASE, f.DESCRIPCION as FORMATO, v.ID_PAIS, v.MONEDA_LOCAL, v.DOLARES DOLAR
           from DDA_T_CNT_FS_PPE_DTOM v
			     inner join DDA_C_FORMATO f on v.ID_FORMATO = f.ID_FORMATO
			     inner join DDA_C_TIER    t on v.ID_TIER = t.ID_TIER
			     where v.ID_CONTRATO = #{idContrato}
			     order by v.ID_PAIS, t.ORDEN, f.ID_FORMATO 	 		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerNumRegPreciosMovies" resultType="int" parameterType="map">
		 select count(*) from DDA_T_CNT_FS_PPE_DTOM where ID_CONTRATO = #{idContrato} group by ID_PAIS      		     		  			       		   				    
	</select>
	
	<select id="obtenerRevenueMovies" resultMap="feesTRevenue" parameterType="map">		
         select r.P_DLA P_DDA, r.P_ESTUDIO, t.DESCRIPCION RELEASE_DATE
	           from DDA_T_CNT_FS_REVENUE_DTOM r 
	           inner join DDA_C_TIER t on r.ID_TIER = t.ID_TIER
	           where r.ID_CONTRATO = #{idContrato} 
	           order by t.ORDEN                    		 				         		     		  			       		   				   
	</select>   
	
	<select id="obtenerCatSeries" resultMap="feesTFormatos" >
		 SELECT ID_CATEGORIA_SERIE ID_FORMATO, DESCRIPCION from DDA_C_CATEGORIA_SERIE ORDER BY ORDEN      		     		  			       		   				    
	</select>
	
	<select id="obtenerPrecioSeries" resultMap="feesTPrecio" parameterType="map">
		   select t.DESCRIPCION as RELEASE, f.DESCRIPCION as FORMATO, v.ID_PAIS, v.MONEDA_LOCAL, v.DOLARES DOLAR
           from DDA_T_CNT_FS_PPE_DTOS v
			     inner join DDA_C_FORMATO f on v.ID_FORMATO = f.ID_FORMATO
			     inner join DDA_C_CATEGORIA_SERIE t on v.ID_CATEGORIA_SERIE = t.ID_CATEGORIA_SERIE
			     where v.ID_CONTRATO = #{idContrato}
			     order by v.ID_PAIS, t.ORDEN, f.ID_FORMATO 	 		 				         		     		  			       		   				    
	</select>
	
	<select id="obtenerNumRegPreciosSeries" resultType="int" parameterType="map">
		 select count(*) from DDA_T_CNT_FS_PPE_DTOS where ID_CONTRATO = #{idContrato} group by ID_PAIS      		     		  			       		   				    
	</select>
	
	<select id="obtenerRevenueSeries" resultMap="feesTRevenue" parameterType="map">		
         select r.P_DLA P_DDA, r.P_ESTUDIO, t.DESCRIPCION RELEASE_DATE
	           from DDA_T_CNT_FS_REVENUE_DTOS r 
	           inner join DDA_C_CATEGORIA_SERIE t on r.ID_CATEGORIA_SERIE = t.ID_CATEGORIA_SERIE
	           where r.ID_CONTRATO = #{idContrato}
	           order by t.ORDEN                    		 				         		     		  			       		   				   
	</select>
	
	<select id="getTipoCambio" resultMap="tipoCambio" >
		  SELECT ID_TIPOCAMBIO, DESCRIPCION FROM DDA_C_TIPO_CAMBIO order by ORDEN desc      		     		  			       		   				    
	</select>
	
	<select id="obtenerTipoCambio" resultType="int" >
		 SELECT ID_TIPOCAMBIO FROM DDA_T_CNT_FS where ID_CONTRATO = #{idContrato}      		     		  			       		   				    
	</select>
	
	<select id="obtenerSeccionMinimos" resultType="String" >
		 SELECT distinct(SECCION) FROM DDA_T_CNT_FS_CATEGORIAMG where ID_CONTRATO = #{idContrato}      		     		  			       		   				    
	</select>
	
	<select id="obtenerSeccionAnio" resultType="String" >
		 SELECT distinct(SECCION) FROM DDA_T_CNT_FS_MINIMOANNIO where ID_CONTRATO = #{idContrato}      		     		  			       		   				    
	</select>
</mapper>