<?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.reporte.amortizacion.daos.ReporteAmortizacionDAO">
	
	 <resultMap id="registroMonto" type="RegistroMontoDTO">
		<result property="temporada" 				column="TEMPORADA" />
		<result property="descripcion" 	    column="DESCRIPCION" />
		<result property="fecha" 		    column="FECHA" />
		<result property="monto"		    column="MONTO" />
		<result property="sistema" 			column="SISTEMA" />
		<result property="idSap" 			column="ID_SAP" />
		<result property="idBroadView" 			column="ID_BV" />
		<result property="idContrato" 			column="ID_CONTRATO" />
		<result property="numContrato" 			column="NUMERO_CONTRATO" />
		<result property="fechaFin" column="FECHA_FIN" />
		<result property="fechaInicio" column="FECHA_INICIO" />
		<result property="costo" column="COSTO" />
	 </resultMap>
	 
	 <resultMap id="resultsContratoAmortizacion" type="ContratoConsultaDTO">
		<result property="idContrato" column="ID_CONTRATO" />
		<result property="numeroContrato" column="NUMERO_CONTRATO" />
		<result property="fechaFin" column="FECHA_FIN" />
		<result property="fechaInicio" column="FECHA_INICIO" />
		<result property="idSap" column="ID_SAP" />
		<result property="sistema" column="SISTEMA" />
	</resultMap>
	  
	  <resultMap id="resultsRemanente" type="RemanenteDTO">
		<result property="diferenciador" column="DIFERENCIADOR" />
		<result property="remanente" column="REMANENTE" />
	</resultMap>
	
	<select id='obtenerRemanente' resultMap='resultsRemanente' parameterType="map">
          SELECT 
          <if test="idEstudio == null"> 
	          	<if test="numContrato == null"> 
	          		amc.ID_ESTUDIO 
	            </if>
          </if>
          <if test="idEstudio != null"> 
	          	<if test="numContrato == null"> 
	          		amc.NUMERO_CONTRATO
	            </if>
          </if>
          <if test="idEstudio != null"> 
	          	<if test="numContrato != null"> 
	          		amct.ID_TITULO_CNT
	            </if>
          </if>
          AS DIFERENCIADOR, ROUND(SUM(am.MONTO),2) AS REMANENTE FROM DDA_T_AMORTIZACION am
			INNER JOIN DDA_T_CONTRATO_TITULO amct ON am.ID_TITULO_CNTORIG = amct.ID_TITULO_CNTORIG
			INNER JOIN DDA_T_CONTRATO amc ON amct.ID_CONTRATO = amc.ID_CONTRATO
			WHERE TRUNC(am.MES) > TO_DATE(#{fecha}, 'MM/YYYY') AND amc.ID_ESTATUS IN (4, 5)
		 <if test="idEstudio == null"> 
	          	<if test="numContrato == null"> 
	          		GROUP BY amc.ID_ESTUDIO ORDER BY amc.ID_ESTUDIO
	            </if>
          </if>
          <if test="idEstudio != null"> 
	          	<if test="numContrato == null"> 
	          		AND  amc.ID_ESTUDIO = #{idEstudio}
	          		GROUP BY amc.NUMERO_CONTRATO ORDER BY amc.NUMERO_CONTRATO
	            </if>
          </if>
          <if test="idEstudio != null"> 
	          	<if test="numContrato != null"> 
	          		AND  amc.ID_ESTUDIO = #{idEstudio}
	          		AND  amc.ID_CONTRATO = #{numContrato}
	          		GROUP BY amct.ID_TITULO_CNT ORDER BY amct.ID_TITULO_CNT
	            </if>
          </if>
	</select>
	
	<select id='obtenerRemanenteSAP' resultMap='registroMonto' parameterType="map">
          SELECT 
          <if test="idEstudio == null"> 
	          	<if test="numContrato == null"> 
	          		amc.ID_ESTUDIO 
	            </if>
          </if>
          <if test="idEstudio != null"> 
	          	<if test="numContrato == null"> 
	          		amc.NUMERO_CONTRATO
	            </if>
          </if>
          <if test="idEstudio != null"> 
	          	<if test="numContrato != null"> 
	          		amct.ID_TITULO_CNT
	            </if>
          </if>
          AS DIFERENCIADOR, (ROUND(SUM(am.MONTO),2) * -1) AS REMANENTE FROM DDA_T_AMORTIZACION_SAP am
			INNER JOIN DDA_T_CONTRATO_TITULO amct ON am.ID_TITULO_CNTORIG = amct.ID_TITULO_CNTORIG
			INNER JOIN DDA_T_CONTRATO amc ON amct.ID_CONTRATO = amc.ID_CONTRATO
			WHERE TRUNC(am.MES) > TO_DATE(#{fecha}, 'MM/YYYY') AND amc.ID_ESTATUS IN (4, 5)
		 <if test="idEstudio == null"> 
	          	<if test="numContrato == null"> 
	          		GROUP BY amc.ID_ESTUDIO ORDER BY amc.ID_ESTUDIO
	            </if>
          </if>
          <if test="idEstudio != null"> 
	          	<if test="numContrato == null"> 
	          		AND  amc.ID_ESTUDIO = #{idEstudio}
	          		GROUP BY amc.NUMERO_CONTRATO ORDER BY amc.NUMERO_CONTRATO
	            </if>
          </if>
          <if test="idEstudio != null"> 
	          	<if test="numContrato != null"> 
	          		AND  amc.ID_ESTUDIO = #{idEstudio}
	          		AND  amc.ID_CONTRATO = #{numContrato}
	          		GROUP BY amct.ID_TITULO_CNT ORDER BY amct.ID_TITULO_CNT
	            </if>
          </if>
	</select>
	 
  	<select id='obtenMontosTotalesEstudio' resultMap='registroMonto' parameterType="map">
          SELECT  NULL AS IDENTIFICADOR, NULL AS DESCRIPCION,  
                  TRUNC(a.MES) AS FECHA, TRUNC(SUM(a.MONTO), 2) AS MONTO
           FROM DDA_T_AMORTIZACION a
            INNER JOIN DDA_T_CONTRATO_TITULO ct
              ON a.ID_TITULO_CNTORIG = ct.ID_TITULO_CNTORIG
            INNER JOIN ( SELECT
                            MAX(t.ID_CONTRATO) AS ID_CONTRATO,
                            t.NUMERO_CONTRATO, 
                            t.ID_CONTRATO_ORIGINAL
	      	               FROM DDA_T_CONTRATO t
	      	               WHERE 
	      	                  t.ID_ESTUDIO = #{idEstudio} AND
                            t.ID_ESTATUS IN (4, 5)       
                         GROUP BY  t.NUMERO_CONTRATO, 
                                   t.ID_CONTRATO_ORIGINAL) c
              ON ct.ID_CONTRATO = c.ID_CONTRATO
          WHERE 
                TRUNC(a.MES)
                BETWEEN TO_DATE(#{fecha}, 'MM/YYYY') AND
                        ADD_MONTHS(TO_DATE(#{fecha}, 'MM/YYYY'), 12)
          GROUP BY  TRUNC(a.MES)
          ORDER BY  TRUNC(a.MES) ASC
	</select>

  	<select id='obtenMontosTotalesContrato' resultMap='registroMonto' parameterType="map">
          SELECT round(sum(ct.costo),2) AS COSTO, max(contratosEst.ID_CONTRATO) AS ID_CONTRATO, min(contratosEst.NUMERO_CONTRATO) AS NUMERO_CONTRATO, NULL AS IDENTIFICADOR, NULL AS DESCRIPCION,  
		          TRUNC(a.MES) AS FECHA, TRUNC(SUM(a.MONTO), 2) AS MONTO, MIN(contratosEst.FECHA_INICIO) AS FECHA_INICIO, MAX(contratosEst.FECHA_FIN) AS FECHA_FIN,
		          MAX(contratosEst.SISTEMA) AS SISTEMA 
		   FROM DDA_T_AMORTIZACION a
		    INNER JOIN DDA_T_CONTRATO_TITULO ct
		      ON a.ID_TITULO_CNTORIG = ct.ID_TITULO_CNTORIG
		    INNER JOIN ( SELECT
		     MAX(t.ID_CONTRATO) AS ID_CONTRATO,
		     t.NUMERO_CONTRATO, MIN(t.FECHA_INICIO) AS FECHA_INICIO, MAX(t.FECHA_FIN) AS FECHA_FIN , MAX(ID_SAP) AS ID_SAP,
		     'DDA' AS SISTEMA
		  	FROM DDA_T_CONTRATO t
		  	WHERE 
		  	   t.ID_ESTUDIO = #{idEstudio} AND EXISTS(    
		         SELECT 1 
		         FROM DDA_T_AMORTIZACION a 
		          INNER JOIN DDA_T_CONTRATO_TITULO ct
		           ON a.ID_TITULO_CNTORIG = ct.ID_TITULO_CNTORIG
		         WHERE ct.ID_CONTRATO = t.ID_CONTRATO) AND
		  t.ID_ESTATUS IN (4, 5) 
		  AND TO_DATE(#{fecha}, 'MM/YYYY') &lt; t.FECHA_FIN
		  GROUP BY        
		     t.NUMERO_CONTRATO
		  	ORDER BY        
		     t.NUMERO_CONTRATO ASC ) contratosEst ON ct.ID_CONTRATO = contratosEst.ID_CONTRATO
		  WHERE TRUNC(a.MES)
		        BETWEEN TO_DATE(#{fecha}, 'MM/YYYY') AND
		                ADD_MONTHS(TO_DATE(#{fecha}, 'MM/YYYY'), #{meses}) 
		  GROUP BY  ct.ID_CONTRATO, TRUNC(a.MES)
		  ORDER BY  ct.ID_CONTRATO, TRUNC(a.MES) ASC
	</select>
	
	 <select id='obtenMontosTitulosContrato' resultMap='registroMonto' parameterType="map">
           SELECT  'DDA' as SISTEMA, NOMBRE_UNICO AS DESCRIPCION, TRUNC(a.MES) AS FECHA, SUM(a.MONTO) AS MONTO, ct.TEMPORADA,
           			MAX(ct.ID_SAP) AS ID_SAP, MAX(ct.ID_BV) AS ID_BV, round(sum(ct.costo),2) AS COSTO, MAX(ct.ID_TITULO_CNT) AS ID_CONTRATO
            FROM DDA_T_AMORTIZACION a
             INNER JOIN DDA_T_CONTRATO_TITULO ct
               ON a.ID_TITULO_CNTORIG = ct.ID_TITULO_CNTORIG
           WHERE ct.ID_CONTRATO = #{idContrato}  AND
                TRUNC(a.MES)
                BETWEEN TO_DATE(#{fecha}, 'MM/YYYY') AND
                        ADD_MONTHS(TO_DATE(#{fecha}, 'MM/YYYY'), 12)
           GROUP BY ct.NOMBRE_UNICO , TRUNC(a.MES), ct.TEMPORADA
           ORDER BY ct.NOMBRE_UNICO , TRUNC(a.MES) ASC
	</select>
	
	<select id="consultaContratosConAmoritzacionPorEstudio" resultMap="resultsContratoAmortizacion"
		parameterType="map">
          SELECT
             MAX(t.ID_CONTRATO) AS ID_CONTRATO,
             t.NUMERO_CONTRATO, MIN(t.FECHA_INICIO) AS FECHA_INICIO, MAX(t.FECHA_FIN) AS FECHA_FIN , MAX(ID_SAP) AS ID_SAP,
             'DDA' AS SISTEMA
	      	FROM DDA_T_CONTRATO t
	      	WHERE 
	      	   t.ID_ESTUDIO = #{idEstudio} AND EXISTS(    
                 SELECT 1 
                 FROM DDA_T_AMORTIZACION a 
                  INNER JOIN DDA_T_CONTRATO_TITULO ct
                   ON a.ID_TITULO_CNTORIG = ct.ID_TITULO_CNTORIG
                 WHERE ct.ID_CONTRATO = t.ID_CONTRATO) AND
          t.ID_ESTATUS IN (4, 5)       
          AND TO_DATE(#{fecha}, 'MM/YYYY') &lt; t.FECHA_FIN
          GROUP BY        
             t.NUMERO_CONTRATO
	      	ORDER BY        
             t.NUMERO_CONTRATO ASC
	</select>
	
	<!-- AMORTIZACION SAP -->
	<select id="consultaContratosConAmoritzacionPorEstudioSAP" resultMap="resultsContratoAmortizacion"
		parameterType="map">
          SELECT 
             MAX(t.ID_CONTRATO) AS ID_CONTRATO,
             t.NUMERO_CONTRATO, MIN(t.FECHA_INICIO) AS FECHA_INICIO, MAX(t.FECHA_FIN) AS FECHA_FIN , MAX(ID_SAP) AS ID_SAP,
             'SAP' AS SISTEMA
	      	FROM DDA_T_CONTRATO t
	      	WHERE 
	      	   t.ID_ESTUDIO = #{idEstudio} AND EXISTS(    
                 SELECT 1 
                 FROM DDA_T_AMORTIZACION_SAP a 
                  INNER JOIN DDA_T_CONTRATO_TITULO ct
                   ON a.ID_TITULO_CNTORIG = ct.ID_TITULO_CNTORIG
                 WHERE ct.ID_CONTRATO = t.ID_CONTRATO) AND
          t.ID_ESTATUS IN (4, 5)    
          AND TO_DATE(#{fecha}, 'MM/YYYY') &lt; t.FECHA_FIN   
          GROUP BY        
             t.NUMERO_CONTRATO
	      	ORDER BY        
             t.NUMERO_CONTRATO ASC
	</select>
	
	<select id='obtenMontosTotalesContratoSAP' resultMap='registroMonto' parameterType="map">
          SELECT round(sum(ct.costo),2) AS COSTO, max(contratosEst.ID_CONTRATO) AS ID_CONTRATO, min(contratosEst.NUMERO_CONTRATO) AS NUMERO_CONTRATO, NULL AS IDENTIFICADOR, NULL AS DESCRIPCION,  
		          TRUNC(a.MES) AS FECHA, (round(SUM(a.MONTO), 2)* - 1) AS MONTO, MIN(contratosEst.FECHA_INICIO) AS FECHA_INICIO, MAX(contratosEst.FECHA_FIN) AS FECHA_FIN,
		          MAX(contratosEst.SISTEMA) AS SISTEMA 
		   FROM DDA_T_AMORTIZACION_SAP a
		    INNER JOIN DDA_T_CONTRATO_TITULO ct
		      ON a.ID_TITULO_CNTORIG = ct.ID_TITULO_CNTORIG
		    INNER JOIN ( SELECT
		     MAX(t.ID_CONTRATO) AS ID_CONTRATO,
		     t.NUMERO_CONTRATO, MIN(t.FECHA_INICIO) AS FECHA_INICIO, MAX(t.FECHA_FIN) AS FECHA_FIN , MAX(ID_SAP) AS ID_SAP,
		     'SAP' AS SISTEMA
		  	FROM DDA_T_CONTRATO t
		  	WHERE 
		  	   t.ID_ESTUDIO = #{idEstudio} AND EXISTS(    
		         SELECT 1 
		         FROM DDA_T_AMORTIZACION_SAP a 
		          INNER JOIN DDA_T_CONTRATO_TITULO ct
		           ON a.ID_TITULO_CNTORIG = ct.ID_TITULO_CNTORIG
		         WHERE ct.ID_CONTRATO = t.ID_CONTRATO) AND
		  t.ID_ESTATUS IN (4, 5) 
		  AND TO_DATE(#{fecha}, 'MM/YYYY') &lt; t.FECHA_FIN
		  GROUP BY        
		     t.NUMERO_CONTRATO
		  	ORDER BY        
		     t.NUMERO_CONTRATO ASC ) contratosEst ON ct.ID_CONTRATO = contratosEst.ID_CONTRATO
		  WHERE TRUNC(a.MES)
		        BETWEEN TO_DATE(#{fecha}, 'MM/YYYY') AND
		                ADD_MONTHS(TO_DATE(#{fecha}, 'MM/YYYY'), #{meses}) 
		  GROUP BY  ct.ID_CONTRATO, TRUNC(a.MES)
		  ORDER BY  ct.ID_CONTRATO, TRUNC(a.MES) ASC
	</select>
	
	<select id='obtenMontosTotalesEstudioSAP' resultMap='registroMonto' parameterType="map">
          SELECT  NULL AS IDENTIFICADOR, NULL AS DESCRIPCION,  
                  TRUNC(a.MES) AS FECHA, (round(SUM(a.MONTO), 2)* - 1) AS MONTO
           FROM DDA_T_AMORTIZACION_SAP a
            INNER JOIN DDA_T_CONTRATO_TITULO ct
              ON a.ID_TITULO_CNTORIG = ct.ID_TITULO_CNTORIG
            INNER JOIN ( SELECT
                            MAX(t.ID_CONTRATO) AS ID_CONTRATO,
                            t.NUMERO_CONTRATO, 
                            t.ID_CONTRATO_ORIGINAL
	      	               FROM DDA_T_CONTRATO t
	      	               WHERE 
	      	                  t.ID_ESTUDIO = #{idEstudio} AND
                            t.ID_ESTATUS IN (4, 5)       
                         GROUP BY  t.NUMERO_CONTRATO, 
                                   t.ID_CONTRATO_ORIGINAL) c
              ON ct.ID_CONTRATO = c.ID_CONTRATO
          WHERE 
                TRUNC(a.MES)
                BETWEEN TO_DATE(#{fecha}, 'MM/YYYY') AND
                        ADD_MONTHS(TO_DATE(#{fecha}, 'MM/YYYY'), 12)
          GROUP BY  TRUNC(a.MES)
          ORDER BY  TRUNC(a.MES) ASC
	</select>
	
	<select id='obtenMontosTitulosContratoSAP' resultMap='registroMonto' parameterType="map">
           SELECT  'SAP' as SISTEMA, ct.NOMBRE_UNICO AS DESCRIPCION, TRUNC(a.MES) AS FECHA, (SUM(a.MONTO) * -1) AS MONTO, ct.TEMPORADA,
           			MAX(ct.ID_SAP) AS ID_SAP, MAX(ct.ID_BV) AS ID_BV, round(sum(ct.costo),2) AS COSTO, MAX(ct.ID_TITULO_CNT) AS ID_CONTRATO
            FROM DDA_T_AMORTIZACION_SAP a
             INNER JOIN DDA_T_CONTRATO_TITULO ct
               ON a.ID_TITULO_CNTORIG = ct.ID_TITULO_CNTORIG
           WHERE ct.ID_CONTRATO = #{idContrato}  AND
                TRUNC(a.MES)
                BETWEEN TO_DATE(#{fecha}, 'MM/YYYY') AND
                        ADD_MONTHS(TO_DATE(#{fecha}, 'MM/YYYY'), 12)
           GROUP BY ct.NOMBRE_UNICO , TRUNC(a.MES), ct.TEMPORADA
           ORDER BY ct.NOMBRE_UNICO , TRUNC(a.MES) ASC
	</select>
</mapper>