<?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.pago.daos.ReportePagoDAO">

	
	<resultMap id="reportePagoBase" type="ReportePagoBaseDTO">
		<result property="idContrato"		column="ID_CONTRATO" />
		<result property="idTituloCntorig"	column="ID_TITULO_CNTORIG" />
		<result property="nombreEstandar"	column="NOMBRE_ESTANDAR" />
		<result property="mesPago"			column="MES_PAGO" />
		<result property="monto"			column="MONTO" />	
	</resultMap>
	
	<resultMap id="detalleView" type="DetalleViewDTO" >
		<result property="mesPago"			column="MES_PAGO" />
		<result property="monto"			column="MONTO" />
	</resultMap>
	
	<resultMap id="reportePagoView" type="ReportePagoViewDTO">
		<result property="idTituloCntorig"	column="ID_TITULO_CNTORIG" />
		<result property="nombreEstandar"	column="NOMBRE_ESTANDAR" />
		<collection property="detalle" resultMap="detalleView" />
	</resultMap>
	
	<resultMap id="reportePago" type="ReportePagoDTO">
		<result property="idContrato"		column="ID_CONTRATO" />
		<result property="numeroContrato"	column="NUMERO_CONTRATO" />
		<collection property="detalleTitulos" resultMap="reportePagoView" />
	</resultMap>
	
	<resultMap id="detalleContrato" type="DetalleContratoDTO">
		<result property="idContrato"		column="ID_CONTRATO" javaType="java.lang.Long" jdbcType="NUMERIC" />
		<result property="numeroContrato"	column="NUMERO_CONTRATO" javaType="java.lang.String" jdbcType="VARCHAR" />
	</resultMap>
	
	<select id="obtenerReportePagoTitulos" resultMap="reportePagoBase">
		SELECT 
		    trpt.ID_CONTRATO, 
		    trpt.ID_TITULO_CNTORIG, 
		    trpt.NOMBRE_ESTANDAR, 
		    trp.MES_PAGO,
		    SUM(trp.MONTO) AS MONTO
		FROM 
		    DDA_T_REPORTE_PAGO_TITULO trpt JOIN DDA_T_REPORTE_PAGO trp
		ON 
		    (trpt.ID_CONTRATO = trp.ID_CONTRATO AND trpt.ID_TITULO_CNTORIG = trp.ID_TITULO_CNTORIG)
		WHERE
			<if test="idContrato != null"> trpt.ID_CONTRATO = #{idContrato} AND </if>
		   (trp.MES_PAGO BETWEEN TO_DATE('01/12/2015', 'dd-mm-yyyy' ) AND  TO_DATE('01/06/2016', 'dd-mm-yyyy' ))
		GROUP BY GROUPING SETS(
		    (trpt.ID_CONTRATO), 
		    (trpt.ID_CONTRATO, trp.MES_PAGO,trpt.ID_TITULO_CNTORIG, trpt.NOMBRE_ESTANDAR))
		ORDER BY 
		    trpt.ID_CONTRATO,DECODE( trpt.ID_TITULO_CNTORIG, -1, null, trpt.ID_TITULO_CNTORIG ) nulls first, 
		    trpt.NOMBRE_ESTANDAR NULLS FIRST
	</select>
	
	<select id="obtenerMapeoReportePagoTitulos" resultMap="reportePago">
		
		SELECT
		    trpt.ID_CONTRATO,
		    tc.NUMERO_CONTRATO,
		    trpt.ID_TITULO_CNTORIG, 
		    trpt.NOMBRE_ESTANDAR, 
		    trp.MES_PAGO,
		    SUM(trp.MONTO) AS MONTO
		FROM 
		    DDA_T_REPORTE_PAGO_TITULO trpt JOIN (DDA_T_REPORTE_PAGO trp JOIN DDA_T_CONTRATO tc ON (trp.ID_CONTRATO = tc.ID_CONTRATO))
		ON 
		    (trpt.ID_CONTRATO = trp.ID_CONTRATO AND trpt.ID_TITULO_CNTORIG = trp.ID_TITULO_CNTORIG)
		WHERE
		  <if test="idContrato != null"> trpt.ID_CONTRATO = #{idContrato} AND </if>
		   (trp.MES_PAGO BETWEEN #{fechaI} AND #{fechaF}) AND tc.ID_ESTUDIO = #{idEstudio}
		GROUP BY GROUPING SETS(
		    (trpt.ID_CONTRATO), 
		    (trpt.ID_CONTRATO,tc.NUMERO_CONTRATO, trp.MES_PAGO,trpt.ID_TITULO_CNTORIG, trpt.NOMBRE_ESTANDAR))
		ORDER BY 
		     trpt.ID_CONTRATO,DECODE( NVL( trpt.ID_TITULO_CNTORIG, 0 ), 0, 0, -1, 1, -2, 2, 3 ), 
		    trpt.NOMBRE_ESTANDAR NULLS FIRST
	
	</select>
	
	<select id="obtenerDetalleContratoPorEstudio" resultMap="detalleContrato">
		<!-- SELECT ID_CONTRATO, NUMERO_CONTRATO FROM DDA_T_CONTRATO WHERE ID_ESTUDIO = #{idEstudio} ORDER BY NUMERO_CONTRATO -->
		SELECT DISTINCT
		    tcnt.ID_CONTRATO, tcnt.NUMERO_CONTRATO 
		FROM 
		    DDA_T_REPORTE_PAGO trp JOIN DDA_T_CONTRATO tcnt 
		    ON (trp.ID_CONTRATO = tcnt.ID_CONTRATO) 
		WHERE 
		    tcnt.ID_ESTUDIO = #{idEstudio}
		ORDER BY 
		    tcnt.NUMERO_CONTRATO
	</select>
	
	<select id="obtenerDetalleContratoPorEstudioSAP" resultMap="detalleContrato">
		<!-- SELECT ID_CONTRATO, NUMERO_CONTRATO FROM DDA_T_CONTRATO WHERE ID_ESTUDIO = #{idEstudio} ORDER BY NUMERO_CONTRATO -->
		SELECT DISTINCT
		    tcnt.ID_CONTRATO, tcnt.NUMERO_CONTRATO 
		FROM 
		    DDA_T_REPORTE_PAGO trp 
		    JOIN DDA_T_CONTRATO tcnt ON trp.ID_CONTRATO = tcnt.ID_CONTRATO
		    JOIN DDA_T_CONTRATO_TITULO ct ON ct.ID_CONTRATO = tcnt.ID_CONTRATO
		WHERE 
		    tcnt.ID_ESTUDIO = #{idEstudio} AND 
		    (ct.ID_SAP IS NULL OR ct.FECHA_NOTIFSAP IS NULL)
		ORDER BY 
		    tcnt.NUMERO_CONTRATO
	</select>
	
	<select id="obtenerIdsEstudios" resultMap="detalleContrato">
		SELECT 
		<if test="campos != null">
			<foreach item="campo" collection="campos"  separator=",">
				#{campo}
			</foreach>
		</if>
		FROM DDA_T_CONTRATO
		<if test="valores != null"> 
			WHERE ID_ESTUDIO  IN 
			<foreach item="valor" collection="valores" open="(" separator="," close=")">
				#{valor}
			</foreach>
		</if>
		ORDER BY ID_ESTUDIO
	</select>
	
	<select id="obtenerIdContratos" resultType="java.lang.Long">
		SELECT ID_CONTRATO FROM DDA_T_CONTRATO
	</select>
	
	<select id="obtenerListas" resultType="java.util.List">
		SELECT NUMERO_CONTRATO, TIPO_AMORTIZACION FROM DDA_T_CONTRATO
	</select>
	
	<!-- reporte query -->
	
	<resultMap id="reporteRow" type="ReporteRowDTO">
		<result property="idContrato"		column="CONCEPTO"/>
		<result property="idContrato"		column="M1"/>
		<result property="idContrato"		column="M2"/>
		<result property="idContrato"		column="M3"/>
		<result property="idContrato"		column="M4"/>
		<result property="idContrato"		column="M5"/>
		<result property="idContrato"		column="M6"/>
		<result property="idContrato"		column="M7"/>
		<result property="idContrato"		column="M8"/>
		<result property="idContrato"		column="M9"/>
		<result property="idContrato"		column="M10"/>
		<result property="idContrato"		column="TOTAL"/>
	</resultMap>
	
	<select id="obtenerReporteRows" resultMap="reporteRow">

		select
		    NVL( NOMBRE_ESTANDAR, NVL( NUMERO_CONTRATO, 'Total' ) ) as CONCEPTO, 
		    M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, TOTAL
		from
		(
		    select
		        pg.ID_CONTRATO, 
		        cnt.NUMERO_CONTRATO,
		        pg.ID_TITULO_CNTORIG,
		        pgt.NOMBRE_ESTANDAR,         
		        NVL( TO_CHAR( pg.MES_PAGO, 'mm-yyyy' ), '-' ) as MES_PAGO, 
		        SUM( pg.MONTO ) as MONTO
		    from
		        DDA_T_CONTRATO cnt,
		        DDA_T_REPORTE_PAGO pg,
		        DDA_T_REPORTE_PAGO_TITULO pgt
		    where
		        cnt.ID_CONTRATO = pg.ID_CONTRATO
		    and pg.ID_CONTRATO = pgt.ID_CONTRATO
		    and pg.ID_TITULO_CNTORIG = pgt.ID_TITULO_CNTORIG
		    and pg.MES_PAGO between to_date('01-12-2015','dd-mm-yyyy') and to_date('01-09-2016','dd-mm-yyyy')
		    group by 
		        grouping sets
		        (
		            (),
		            (pg.MES_PAGO),
		            (pg.ID_CONTRATO, cnt.NUMERO_CONTRATO ),
		            (pg.ID_CONTRATO, cnt.NUMERO_CONTRATO, pg.MES_PAGO),
		            (pg.ID_CONTRATO, cnt.NUMERO_CONTRATO, pg.ID_TITULO_CNTORIG, pgt.NOMBRE_ESTANDAR),
		            (pg.ID_CONTRATO, cnt.NUMERO_CONTRATO, pg.ID_TITULO_CNTORIG, pgt.NOMBRE_ESTANDAR, pg.MES_PAGO)
		        )
		)
		PIVOT( SUM( MONTO ) FOR MES_PAGO in <foreach item="m" collection="mapa" open="(" separator="," close=")">#{m.key, javaType=String, jdbcType=VARCHAR} AS #{m.value, javaType=String, jdbcType=VARCHAR} </foreach> )
		ORDER BY 
		    NUMERO_CONTRATO nulls first, DECODE( NVL( ID_TITULO_CNTORIG, 0 ), 0, 0, -1, 1, -2, 2, 3 ), 
		    NOMBRE_ESTANDAR nulls first
	</select>
	
	

</mapper>