<?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="mesesAmortizacionDTO" type="MesesAmortizacionDTO">
	 	<id property="idContrato"		column="ID_CONTRATO"/>
	 	<result property="descripcion"	column="DESCRIPCION" />
	 	<result property="mes1"			column="MES_1" />
	 	<result property="mes2"			column="MES_2" />
	 	<result property="mes3"			column="MES_3" />
	 	<result property="mes4"			column="MES_4" />
	 	<result property="mes5"			column="MES_5" />
	 	<result property="mes6"			column="MES_6" />
	 	<result property="mes7"			column="MES_7" />
	 	<result property="mes8"			column="MES_8" />
	 	<result property="mes9"			column="MES_9" />
	 	<result property="mes10"		column="MES_10" />
	 	<result property="mes11"		column="MES_11" />
	 	<result property="mes12"		column="MES_12" />
	 	<result property="acumulado"	column="ACUMULADO" />
	 	<result property="temporada"	column="TEMPORADA" />
	 	<result property="sistema"	column="SISTEMA" />
	 	<result property="numContrato"	column="NUMERO_CONTRATO" />
	 </resultMap>
	   	  	  	  	  
	 <select id="obtenerMesesAmortizacion" resultMap="mesesAmortizacionDTO">
	 	select 
		    'DDA' as SISTEMA, ID_CONTRATO, NVL( NOMBRE_UNICO, NVL( NUMERO_CONTRATO, DESC_ESTUDIO ) ) as DESCRIPCION, TEMPORADA, NUMERO_CONTRATO,
		    ROUND( NVL( MES_1, 0 ), 2 ) as MES_1, ROUND(  NVL( MES_2, 0 ), 2 ) as MES_2, 
		    ROUND(NVL( MES_3, 0 ), 2 ) as MES_3, ROUND( NVL( MES_4, 0 ), 2 ) as MES_4, 
		    ROUND( NVL( MES_5, 0 ), 2 ) as MES_5, ROUND(  NVL( MES_6, 0 ), 2 ) as MES_6, 
		    ROUND(NVL( MES_7, 0 ), 2 ) as MES_7, ROUND( NVL( MES_8, 0 ), 2 ) as MES_8, 
		    ROUND(NVL( MES_9, 0 ), 2 ) as MES_9, ROUND( NVL( MES_10, 0 ), 2 ) as MES_10, 
		    ROUND( NVL( MES_11, 0 ), 2 ) as MES_11, ROUND( NVL( MES_12, 0 ), 2 ) as MES_12, 
		    ( ROUND( NVL( MES_1, 0 ), 2 ) + ROUND( NVL( MES_2, 0 ), 2 )  + ROUND( NVL( MES_3, 0 ), 2 )  + ROUND( NVL( MES_4, 0 ), 2 ) + 
		        ROUND( NVL( MES_5, 0 ), 2 )  + ROUND( NVL( MES_6, 0 ), 2 )  + ROUND( NVL( MES_7, 0 ), 2 )  + ROUND( NVL( MES_8, 0 ), 2 )  + 
		        ROUND( NVL( MES_9, 0 ), 2 )  + ROUND( NVL( MES_10, 0 ), 2 )  + ROUND( NVL( MES_11, 0 ), 2 )  + ROUND( NVL( MES_12, 0 ), 2 )  ) AS ACUMULADO
		from
		(
		    select 
		        ID_CONTRATO, DESC_ESTUDIO, NUMERO_CONTRATO, NOMBRE_UNICO, TEMPORADA,
		        SUM( MES_1 ) as MES_1, SUM( MES_2 ) as MES_2, SUM( MES_3 ) as MES_3,
		        SUM( MES_4 ) as MES_4, SUM( MES_5 ) as MES_5, SUM( MES_6 ) as MES_6,
		        SUM( MES_7 ) as MES_7, SUM( MES_8 ) as MES_8, SUM( MES_9 ) as MES_9,
		        SUM( MES_10 ) as MES_10, SUM( MES_11 ) as MES_11, SUM( MES_12 ) as MES_12
		    from
		    (
		        select
		            ID_CONTRATO, DESC_ESTUDIO, NUMERO_CONTRATO, NOMBRE_UNICO, TEMPORADA,
		            MES_1, MES_2, MES_3, MES_4, MES_5, MES_6, MES_7, MES_8, MES_9, MES_10, MES_11, MES_12
		        from
		        (
		            select
		                est.DESC_ESTUDIO,
		                con.ID_CONTRATO,
		                con.NUMERO_CONTRATO,
		                sub.NOMBRE_UNICO,
		                sub.MESES,
		                sub.MONTO,
		                sub.TEMPORADA
		            from
		            (
		                select 
		                    MAX( ctit.ID_CONTRATO ) as ID_CONTRATO,
		                    ctit.ID_TITULO_CNTORIG,
		                    ctit.NOMBRE_UNICO, 
		                    months_between( amor.MES, to_date(#{fecha},'mm/yyyy') ) as MESES, 
		                    TO_NUMBER( amor.MONTO ) as MONTO,
		                    ctit.TEMPORADA
		                from
		                    DDA_T_CONTRATO cnt,                
		                    DDA_T_CONTRATO_TITULO ctit,
		                    DDA_T_AMORTIZACION amor
		                where
		                        cnt.ID_CONTRATO = ctit.ID_CONTRATO
		                    and ctit.ID_TITULO_CNTORIG = amor.ID_TITULO_CNTORIG
		                    and (amor.MES BETWEEN to_date(#{fecha},'mm/yyyy') AND add_months( to_date(#{fecha},'mm/yyyy'), 12 ))
		                    and cnt.ID_ESTUDIO = #{idEstudio}
		                    and (cnt.ID_ESTATUS = 4 OR cnt.ID_ESTATUS = 5 OR cnt.ID_ESTATUS = 6 )
		                group by ctit.ID_TITULO_CNTORIG, NOMBRE_UNICO, ctit.TEMPORADA, amor.MES, MONTO
		            ) sub,
		                DDA_T_CONTRATO con,
		                DDA_C_ESTUDIO est
		            where
		                    sub.ID_CONTRATO = con.ID_CONTRATO
		                and con.ID_ESTUDIO = est.ID_ESTUDIO
		        )
		        PIVOT( SUM( MONTO ) FOR 
		            MESES in ( 0 as MES_1, 1 as MES_2, 2 as MES_3, 3 as MES_4, 4 as MES_5, 5 as MES_6,
		                        6 as MES_7, 7 as MES_8, 8 as MES_9, 9 as MES_10, 10 as MES_11, 11 as MES_12 )
		            )
		    )
		    group by
		        grouping sets
		            (
		                ( DESC_ESTUDIO ),
		                ( DESC_ESTUDIO, NUMERO_CONTRATO, ID_CONTRATO ),
		                ( DESC_ESTUDIO, NUMERO_CONTRATO, NOMBRE_UNICO, TEMPORADA )
		            )
		)ORDER BY NUMERO_CONTRATO nulls first, NOMBRE_UNICO nulls FIRST, TEMPORADA NULLS FIRST 
	 </select>
	 
	 <select id="obtenerMesesAmortizacionSAP" resultMap="mesesAmortizacionDTO">
	 	select 
		    'SAP' as SISTEMA, ID_CONTRATO, NVL( NOMBRE_UNICO, NVL( NUMERO_CONTRATO, DESC_ESTUDIO ) ) as DESCRIPCION, TEMPORADA, NUMERO_CONTRATO,
		    ROUND( NVL( MES_1, 0 ), 2 ) as MES_1, ROUND(  NVL( MES_2, 0 ), 2 ) as MES_2, 
		    ROUND(NVL( MES_3, 0 ), 2 ) as MES_3, ROUND( NVL( MES_4, 0 ), 2 ) as MES_4, 
		    ROUND( NVL( MES_5, 0 ), 2 ) as MES_5, ROUND(  NVL( MES_6, 0 ), 2 ) as MES_6, 
		    ROUND(NVL( MES_7, 0 ), 2 ) as MES_7, ROUND( NVL( MES_8, 0 ), 2 ) as MES_8, 
		    ROUND(NVL( MES_9, 0 ), 2 ) as MES_9, ROUND( NVL( MES_10, 0 ), 2 ) as MES_10, 
		    ROUND( NVL( MES_11, 0 ), 2 ) as MES_11, ROUND( NVL( MES_12, 0 ), 2 ) as MES_12, 
		    ( ROUND( NVL( MES_1, 0 ), 2 ) + ROUND( NVL( MES_2, 0 ), 2 )  + ROUND( NVL( MES_3, 0 ), 2 )  + ROUND( NVL( MES_4, 0 ), 2 ) + 
		        ROUND( NVL( MES_5, 0 ), 2 )  + ROUND( NVL( MES_6, 0 ), 2 )  + ROUND( NVL( MES_7, 0 ), 2 )  + ROUND( NVL( MES_8, 0 ), 2 )  + 
		        ROUND( NVL( MES_9, 0 ), 2 )  + ROUND( NVL( MES_10, 0 ), 2 )  + ROUND( NVL( MES_11, 0 ), 2 )  + ROUND( NVL( MES_12, 0 ), 2 )  ) AS ACUMULADO
		from
		(
		    select 
		        ID_CONTRATO, DESC_ESTUDIO, NUMERO_CONTRATO, NOMBRE_UNICO, TEMPORADA,
		        SUM( MES_1 ) as MES_1, SUM( MES_2 ) as MES_2, SUM( MES_3 ) as MES_3,
		        SUM( MES_4 ) as MES_4, SUM( MES_5 ) as MES_5, SUM( MES_6 ) as MES_6,
		        SUM( MES_7 ) as MES_7, SUM( MES_8 ) as MES_8, SUM( MES_9 ) as MES_9,
		        SUM( MES_10 ) as MES_10, SUM( MES_11 ) as MES_11, SUM( MES_12 ) as MES_12
		    from
		    (
		        select
		            ID_CONTRATO, DESC_ESTUDIO, NUMERO_CONTRATO, NOMBRE_UNICO, TEMPORADA,
		            MES_1, MES_2, MES_3, MES_4, MES_5, MES_6, MES_7, MES_8, MES_9, MES_10, MES_11, MES_12
		        from
		        (
		            select
		                est.DESC_ESTUDIO,
		                con.ID_CONTRATO,
		                con.NUMERO_CONTRATO,
		                sub.NOMBRE_UNICO,
		                sub.MESES,
		                sub.MONTO,
		                sub.TEMPORADA
		            from
		            (
		                select 
		                    MAX( ctit.ID_CONTRATO ) as ID_CONTRATO,
		                    ctit.ID_TITULO_CNTORIG,
		                    ctit.NOMBRE_UNICO, 
		                    months_between( amor.MES, to_date(#{fecha},'mm/yyyy') ) as MESES, 
		                    TO_NUMBER( amor.MONTO ) as MONTO,
		                    ctit.TEMPORADA
		                from
		                    DDA_T_CONTRATO cnt,                
		                    DDA_T_CONTRATO_TITULO ctit,
		                    DDA_T_AMORTIZACION_SAP amor
		                where
		                        cnt.ID_CONTRATO = ctit.ID_CONTRATO
		                    and ctit.ID_TITULO_CNTORIG = amor.ID_TITULO_CNTORIG
		                    and (amor.MES BETWEEN to_date(#{fecha},'mm/yyyy') AND add_months( to_date(#{fecha},'mm/yyyy'), 12 ))
		                    and cnt.ID_ESTUDIO = #{idEstudio}
		                    and (cnt.ID_ESTATUS = 4 OR cnt.ID_ESTATUS = 5 OR cnt.ID_ESTATUS = 6 )
		                group by ctit.ID_TITULO_CNTORIG, NOMBRE_UNICO, ctit.TEMPORADA, amor.MES, MONTO
		            ) sub,
		                DDA_T_CONTRATO con,
		                DDA_C_ESTUDIO est
		            where
		                    sub.ID_CONTRATO = con.ID_CONTRATO
		                and con.ID_ESTUDIO = est.ID_ESTUDIO
		        )
		        PIVOT( SUM( MONTO ) FOR 
		            MESES in ( 0 as MES_1, 1 as MES_2, 2 as MES_3, 3 as MES_4, 4 as MES_5, 5 as MES_6,
		                        6 as MES_7, 7 as MES_8, 8 as MES_9, 9 as MES_10, 10 as MES_11, 11 as MES_12 )
		            )
		    )
		    group by
		        grouping sets
		            (
		                ( DESC_ESTUDIO ),
		                ( DESC_ESTUDIO, NUMERO_CONTRATO, ID_CONTRATO ),
		                ( DESC_ESTUDIO, NUMERO_CONTRATO, NOMBRE_UNICO, TEMPORADA )
		            )
		)ORDER BY NUMERO_CONTRATO nulls first, NOMBRE_UNICO nulls FIRST, TEMPORADA NULLS FIRST 
	 </select>	  
	 
</mapper>