<?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.minimoGarantizado.daos.MinimoGarantizadoDAO">

<resultMap id="resultsMinimoGarantizadoConsulta" type="MinGarantizadoDTO">
		
		<result property="idCNTMG" 			column="ID_CNT_MG" />
 		<result property="numeroContrato"	column="NUMERO_CONTRATO" /> 	
        <result property="tipoPago" 		column="TIPO_PAGO" />
		<result property="estatus" 			column="ESTATUS" />
		<result property="estilo" 			column="ESTILO" />
		<result property="idBV" 			column="ID_BV" />
		<result property="nombreEstandar" 	column="NOMBRE_ESTANDAR" />
		<result property="fechaPago" 		column="FECHA_PAGO" />
		<result property="typePago" 		column="TYPE_PAGO" />
		<result property="aplicamg" 		column="APLICAMG" />
		<result property="monto" 			column="MONTO" />	
				
</resultMap> 
	
	<select id="consultaMinimoGarantizado" resultMap="resultsMinimoGarantizadoConsulta" parameterType="map">
		select
		   	ID_CNT_MG,
		    NUMERO_CONTRATO,
		    TIPO_PAGO,
		    ESTATUS,
		    CASE
		        WHEN TIPO_PAGO = 'MINIMO_TIT' THEN
		            'hijo'
		        WHEN APLICAMG = 'TOTAL' THEN
		            'padre'
		        WHEN ID_BV is null THEN
		            'contrato'
		        WHEN ID_BV is not null THEN
		            'titulo'
		    END as ESTILO,
		    ID_BV,
		    CASE
		        WHEN APLICAMG = 'TOTAL' THEN
		            'Mínimo garantizado global'       
		        ELSE
		            NVL( NOMBRE_ESTANDAR, NVL( NUMERO_CONTRATO, 'TOTAL') )
		    END as NOMBRE_ESTANDAR,
		    to_char( FECHA_PAGO, 'mm/dd/yyyy' ) as FECHA_PAGO,
		    TYPE_PAGO,    
		    APLICAMG,
		    MONTO    
		from
		(
		    select
		        pgo.ID_CONTRATO_ORIGINAL,
		        pgo.ID_CNT_MG,
		        cnt.NUMERO_CONTRATO,
		        pgo.TIPO_PAGO,
		        pgo.ESTATUS,
		        pgo.ID_BV,
		        pgo.NOMBRE_ESTANDAR,
		        pgo.FECHA_PAGO, 
		        NVL( pgo.TYPE_PAGO, '-' ) as TYPE_PAGO,    
		        DECODE( pgo.TIPO_PAGO, 'MINIMO_TIT', '', pgo.APLICAMG ) as APLICAMG,
		        SUM(pgo.MONTO ) as MONTO    
		    from
		        DDA_T_CNT_MG pgo,
		        DDA_T_CONTRATO cnt
		    where
		        pgo.ID_CONTRATO_ORIGINAL = #{IdContratoOrig} and 
		        pgo.ID_CONTRATO_ORIGINAL = cnt.ID_CONTRATO and
		        (pgo.TIPO_PAGO like 'MINIMO%' OR pgo.TIPO_PAGO is null)        
		    group by
		        grouping sets
		        (
		            ( pgo.ID_CONTRATO_ORIGINAL, cnt.NUMERO_CONTRATO, pgo.ESTATUS ),
		            (
		                pgo.ID_CONTRATO_ORIGINAL,
		                cnt.NUMERO_CONTRATO,
		                pgo.ID_CNT_MG,
		                pgo.ID_BV,
		                pgo.NOMBRE_ESTANDAR,
		                pgo.FECHA_PAGO,
		                pgo.TYPE_PAGO,
		                pgo.TIPO_PAGO,
		                DECODE( pgo.TIPO_PAGO, 'MINIMO_TIT', '', pgo.APLICAMG ),
		                pgo.ESTATUS
		            )
		        )
		    order by 
		        pgo.ID_CONTRATO_ORIGINAL nulls first,
		        pgo.ESTATUS desc,
		        DECODE( TYPE_PAGO, 'Type 1 Premium', 0, 'Type 1 Premier', 1, 'Type 2', 2, 'EST 1/EST 2', 3, 'EST 8', 4 ) nulls first, 
		        pgo.TIPO_PAGO,        
		        pgo.NOMBRE_ESTANDAR
		)
		 	   
        
	</select> 
	
</mapper>
