<?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">
								
	<insert id="guardarCatMinTrans"     parameterType="map" useGeneratedKeys="true"   keyProperty="categoria.idCategoria">
		
			<selectKey keyProperty="categoria.idCategoria" resultType="long" order="BEFORE">
				select DDA_SQT_CNT_FS_CATEGORIAMG.nextVal from DUAL
			</selectKey>								
							            
		    INSERT INTO DDA_T_CNT_FS_CATEGORIAMG ( ID_FS_CATEGORIAMG, ID_CONTRATO, DESDE, HASTA, CATEGORIAMG, SECCION, REVENUE )
			  VALUES
			  (
			    #{categoria.idCategoria},
			    #{idContrato},
			    #{categoria.desde},
			    #{categoria.hasta},
			    #{categoria.categoria},
			    #{categoria.seccion},
			    #{categoria.revenue}
			  )
	</insert>
	
	<update id="editarCatMinTrans" parameterType="map">	
			UPDATE DDA_T_CNT_FS_CATEGORIAMG
				SET 
					DESDE               = #{categoria.desde},
					HASTA               = #{categoria.hasta},
					CATEGORIAMG         = #{categoria.categoria}				
				WHERE 
				    ID_FS_CATEGORIAMG   = #{categoria.idCategoria}											            		   
	</update>
	
	<delete id="eliminarCatMinTrans" parameterType="map">	
			DELETE FROM DDA_T_CNT_FS_CATEGORIAMG WHERE ID_CONTRATO = #{idContrato} and SECCION = #{seccion}														            		  
	</delete>
	
	<delete id="eliminarValorCatMinTrans" parameterType="map">	
			DELETE FROM DDA_T_CNT_FS_MINIMOCAT
			WHERE ID_FS_CATEGORIAMG IN (SELECT ID_FS_CATEGORIAMG FROM  DDA_T_CNT_FS_CATEGORIAMG WHERE ID_CONTRATO = #{idContrato} and SECCION = #{seccion})														            		  
	</delete>
		
	<insert id="guardarValoresCatMin" parameterType="map">		
		INSERT ALL								
		<foreach item="v" collection="valores"  separator=" ">		    		   
		    INTO DDA_T_CNT_FS_MINIMOCAT ( ID_MINIMO, ID_FS_CATEGORIAMG, FECHA_INICIO, FECHA_FIN, MINIMO )
				  VALUES
				  (
				    extractvalue(dbms_xmlgen.getxmltype('select DDA_SQT_CNT_FS_MINIMOCAT.nextval from dual'),'//text()'),
				    #{v.idCat},
				    TO_DATE(#{v.inicio}, 'MM/DD/YY'),				    
				    TO_DATE(#{v.fin}, 'MM/DD/YY'),					     				    				    
				    #{v.valor}
				  )		    
		</foreach> 
		SELECT * FROM DUAL
	</insert>	
	
	<delete id="eliminarValorAnnioMinTrans" parameterType="map">	
			DELETE FROM DDA_T_CNT_FS_MINIMOANNIO WHERE ID_CONTRATO = #{idContrato} and SECCION = #{seccion}											            		  
	</delete>
	
	<insert id="guardarValoresAnnioMin" parameterType="map">		
		INSERT ALL								
		<foreach item="v" collection="valores"  separator=" ">		    		   		    
			INTO DDA_T_CNT_FS_MINIMOANNIO ( ID_MINIMO, ID_CONTRATO, FECHA_INICIO, FECHA_FIN, MINIMO, SECCION )
				  VALUES
				  (
				    extractvalue(dbms_xmlgen.getxmltype('select DDA_SQT_CNT_FS_MINIMOANNIO.nextval from dual'),'//text()'),
				    #{idContrato},
				    TO_DATE(#{v.inicio}, 'MM/DD/YY'),				    
				    TO_DATE(#{v.fin}, 'MM/DD/YY'),					     				    				    
				    #{v.valor},
				    #{v.seccion}
				  )		    		   
		</foreach> 
		SELECT * FROM DUAL
	</insert>	
	
	
		
	<insert id="guardarValoresReleasePremier" parameterType="map" useGeneratedKeys="true"   keyProperty="release.idCategoria">	
		<selectKey keyProperty="release.idCategoria" resultType="long" order="BEFORE">
			select DDA_SQT_CNT_FS_RELEASEDATE.nextVal from DUAL
		</selectKey>						
		
		INSERT
			INTO DDA_T_CNT_FS_RELEASEDATE ( ID_RELEASE_DATE, ID_CONTRATO, DESDE, HASTA, RELEASE_DATE )
			  VALUES
			  (
			    #{release.idCategoria},
			    #{idContrato},
			    #{release.desde},
			    #{release.hasta},
			    #{release.categoria}
			  )
	</insert>
	
	<update id="editarValoresReleasePremier" parameterType="map">			
		UPDATE DDA_T_CNT_FS_RELEASEDATE
			SET DESDE = #{release.desde},
			    HASTA = #{release.hasta},
			    RELEASE_DATE  = #{release.categoria}
			WHERE ID_RELEASE_DATE =  #{release.idCategoria}						
	</update>	
		
	<delete id="eliminarValoresImpuesto" parameterType="map">	
		DELETE FROM DDA_T_CNT_FS_IMPUESTO WHERE ID_CONTRATO = #{idContrato} and SECCION = #{seccion}	
	</delete>
	
	<insert id="guardarValoresImpuesto" parameterType="map" >										
		INSERT ALL								
		<foreach item="v" collection="impuestos"  separator=" ">		    		   		    
			INTO DDA_T_CNT_FS_IMPUESTO ( SECCION, ID_CONTRATO, ID_PAIS, APLICA, INCLUIDO )
			  VALUES
			  (
			    #{v.seccion},
			    #{idContrato},
			    #{v.idPais},
			    #{v.aplica},
			    #{v.incluido}
			  )		    		   
		</foreach> 
		SELECT * FROM DUAL					
	</insert>
	
	<delete id="eliminarValoresPrecioPremier" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_PPE_RP
	</delete>
	
	<insert id="guardarValoresPrecioPremier" parameterType="map" >										
		INSERT ALL								
		<foreach item="v" collection="precios"  separator=" ">		    		   		    
				INTO DDA_T_CNT_FS_PPE_RP ( ID_RELEASE_DATE, ID_FORMATO, ID_PAIS, MONEDA_LOCAL, DOLAR, ID_CONTRATO )
				  VALUES
				  (
				    #{v.idRelease},
				    #{v.idFormato},
				    #{v.idPais},
				    #{v.moneda},
				    #{v.dolar},
				    #{idContrato}
				  )		   
		</foreach> 
		SELECT * FROM DUAL					
	</insert>
	
	<delete id="eliminarValoresRevenuePremier" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_REVENUE_RP WHERE ID_CONTRATO = #{idContrato} 
	</delete>
	
	<insert id="guardarValoresRevenuePremier" parameterType="map" >										
		INSERT ALL								
		<foreach item="v" collection="revenues"  separator=" ">		    		   		    			
			INTO DDA_T_CNT_FS_REVENUE_RP ( ID_CONTRATO, ID_RELEASE_DATE, P_DDA, P_ESTUDIO )
				  VALUES
				  (
				    #{idContrato},
				    #{v.idRelease},
				    #{v.ddaPto},
				    #{v.estudioPto}
				  )	   
		</foreach> 
		SELECT * FROM DUAL					
	</insert>		
		
		
	<delete id="eliminarValoresPrecioLibreria" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_PPE_RL WHERE ID_CONTRATO = #{idContrato}
	</delete>
	
	<insert id="guardarValoresPrecioLibreria" parameterType="map" >										
		INSERT ALL								
		<foreach item="v" collection="precios"  separator=" ">		    		   		    
				INTO DDA_T_CNT_FS_PPE_RL ( ID_CONTRATO, ID_FORMATO, ID_PAIS, MONEDA_LOCAL, DOLARES )
				  VALUES
				  (
				    #{idContrato},
				    #{v.idFormato},
				    #{v.idPais},
				    #{v.moneda},
				    #{v.dolar}
				  )		   
		</foreach> 
		SELECT * FROM DUAL					
	</insert>
	
	<delete id="eliminarValoresPrecioMovies" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_PPE_DTOM WHERE ID_CONTRATO = #{idContrato}
	</delete>
	
	<insert id="guardarValoresPrecioMovies" parameterType="map" >	
		INSERT ALL								
		<foreach item="v" collection="precios"  separator=" ">									
		    INTO DDA_T_CNT_FS_PPE_DTOM ( ID_CONTRATO, ID_TIER, ID_FORMATO, ID_PAIS, MONEDA_LOCAL, DOLARES)
				  VALUES
				  (
				    #{idContrato},
				    #{v.idRelease},
				    #{v.idFormato},
				    #{v.idPais},
				    #{v.moneda},
				    #{v.dolar}
				  )		   	
		 </foreach> 
		SELECT * FROM DUAL							
	</insert>
	
	<delete id="eliminarValoresPrecioSeries" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_PPE_DTOS WHERE ID_CONTRATO = #{idContrato}
	</delete>
	
	<insert id="guardarValoresPrecioSeries" parameterType="map" >	
		INSERT ALL								
		<foreach item="v" collection="precios"  separator=" ">									
		    INTO DDA_T_CNT_FS_PPE_DTOS ( ID_CONTRATO, ID_CATEGORIA_SERIE, ID_FORMATO, ID_PAIS, MONEDA_LOCAL, DOLARES)
				  VALUES
				  (
				    #{idContrato},
				    #{v.idRelease},
				    #{v.idFormato},
				    #{v.idPais},
				    #{v.moneda},
				    #{v.dolar}
				  )		   	
		 </foreach> 
		SELECT * FROM DUAL							
	</insert>
	
	
	<delete id="eliminarValoresRevenueMovies" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_REVENUE_DTOM WHERE ID_CONTRATO = #{idContrato} 
	</delete>
	
	<insert id="guardarValoresRevenueMovies" parameterType="map" >										
		INSERT ALL								
		<foreach item="v" collection="revenues"  separator=" ">
			INTO DDA_T_CNT_FS_REVENUE_DTOM ( ID_CONTRATO, ID_TIER, P_DLA, P_ESTUDIO )
				  VALUES
				  (
				    #{idContrato},
				    #{v.idRelease},
				    #{v.ddaPto},
				    #{v.estudioPto}
				  )	   
		</foreach> 
		SELECT * FROM DUAL					
	</insert>
	
	
	<delete id="eliminarValoresRevenueSeries" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_REVENUE_DTOS WHERE ID_CONTRATO = #{idContrato} 
	</delete>
	
	<insert id="guardarValoresRevenueSeries" parameterType="map" >										
		INSERT ALL								
		<foreach item="v" collection="revenues"  separator=" ">		    		   		    			
			INTO DDA_T_CNT_FS_REVENUE_DTOS ( ID_CONTRATO, ID_CATEGORIA_SERIE, P_DLA, P_ESTUDIO )
				  VALUES
				  (
				    #{idContrato},
				    #{v.idRelease},
				    #{v.ddaPto},
				    #{v.estudioPto}
				  )	   
		</foreach> 
		SELECT * FROM DUAL					
	</insert>
	
	<delete id="eliminarCategMinimos" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_CATEGORIAMG WHERE ID_FS_CATEGORIAMG IN 
		   <foreach item="id" index="index" collection="ids" open="(" separator="," close=")">
	         		#{id}
	       </foreach>	   
	</delete>
	
	<delete id="eliminarCategValMinimos" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_MINIMOCAT WHERE ID_FS_CATEGORIAMG IN 
		   <foreach item="id" index="index" collection="ids" open="(" separator="," close=")">
	         		#{id}
	       </foreach>	   
	</delete>
	
	<delete id="eliminarCategRevenueXRelease" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_REVENUE_RP WHERE ID_RELEASE_DATE IN  
		   <foreach item="id" index="index" collection="ids" open="(" separator="," close=")">
	         		#{id}
	       </foreach>	   
	</delete>
	
	<delete id="eliminarCategPrecioxRelease" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_PPE_RP WHERE ID_RELEASE_DATE  IN  
		   <foreach item="id" index="index" collection="ids" open="(" separator="," close=")">
	         		#{id}
	       </foreach>	   
	</delete>
	
	<delete id="eliminarCategReleaseXRelease" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_RELEASEDATE WHERE ID_RELEASE_DATE IN  
		   <foreach item="id" index="index" collection="ids" open="(" separator="," close=")">
	         		#{id}
	       </foreach>	   
	</delete>
	
	
	<delete id="eliminarRevBoxLibreria" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_REVENUE_RLMG 
		   WHERE ID_FS_CATEGORIAMG in ( SELECT ID_FS_CATEGORIAMG FROM DDA_T_CNT_FS_CATEGORIAMG where ID_CONTRATO = #{idContrato} and SECCION = 'RL')  
	</delete>
	
	<insert id="guardarRevenueBox" parameterType="map" >										
		INSERT ALL							
		<foreach item="v" collection="revenues"  separator=" ">		    		   		    			
			INTO DDA_T_CNT_FS_REVENUE_RLMG ( ID_FS_CATEGORIAMG, P_ESTUDIO, P_DLA)
				  VALUES
				  (				    
				    #{v.idRelease},
				    #{v.ddaPto},
				    #{v.estudioPto}
				  )	   
		</foreach> 
		SELECT * FROM DUAL					
	</insert>
		
	<insert id="guardarRevenueGlobal" parameterType="map" >										
		INSERT INTO DDA_T_CNT_FS_REVENUE_RLGL ( ID_CONTRATO, P_ESTUDIO, P_DLA)
				  VALUES
				  (	#{idContrato},
				    #{revenue.ddaPto},
				    #{revenue.estudioPto}
				  )	   							
	</insert>
	
	<delete id="eliminarRevenueGlobal" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS_REVENUE_RLGL  WHERE ID_CONTRATO = #{idContrato}  
	</delete>
		
	
	<insert id="guardarTipoCambio" parameterType="map" >										
		INSERT INTO DDA_T_CNT_FS (ID_CONTRATO, ID_TIPOCAMBIO ) VALUES (#{idContrato}, #{idTipo})		   							
	</insert>
		
	<delete id="eliminarTipoCambio" parameterType="map">	
		   DELETE FROM DDA_T_CNT_FS WHERE ID_CONTRATO = #{idContrato}  
	</delete>
		
		
</mapper>