<?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.acumulateBuys.dao.ReporteAcumulateBuysDAO">
		 
	 <resultMap id="estudios" type="acumulateBuysEstudiosDTO">
	 	<result property="id_estudio"		column="ID_ESTUDIO" />
	 	<result property="desc_estudio"			column="DESC_ESTUDIO" />
	 </resultMap>
	   	  	  	  	 
	 
	 <select id="obtenerEstudios" resultMap="estudios" parameterType="map">
	                    SELECT distinct est.ID_ESTUDIO, est.DESC_ESTUDIO
								FROM    DDA_C_ESTUDIO est,    
								        DDA_T_CONTRATO cnt
								WHERE    est.ID_ESTUDIO = cnt.ID_ESTUDIO
									 and cnt.ID_TIPO_CONTRATO = 2
									 and est.ID_SAP is not null
								order by est.DESC_ESTUDIO          
	 </select>
	 
	 <select id="ObtenerEstudiosConfig" resultMap="estudios" parameterType="map">
	                    select distinct est.ID_ESTUDIO, est.DESC_ESTUDIO
								from    DDA_C_ESTUDIO est,    
								        DDA_T_CONTRATO cnt
								where    est.ID_ESTUDIO = cnt.ID_ESTUDIO
								and cnt.ID_TIPO_CONTRATO = 2
								and est.ID_SAP is not null
								and est.ID_ESTUDIO in (select ID_ESTUDIO from DDA_T_USUARIO_ESTUDIO WHERE EXPEDIENTE = #{user} )
								order by est.DESC_ESTUDIO         
	 </select>
	 
	 <select id="reporteAcumulateBuys" resultType="java.util.LinkedHashMap" parameterType="map">
	                 SELECT
						    NVL( ASSET_NAME, 'Grand Total' ) as AASSET_NAME
						    ${sumatorias_mensuales}
						from
						(
						    select
						        ASSET_NAME,
						        ASSET_NAME as ANORDEN,
						        ${meses_seleccionados}
						    from
						    (
						        select
						            tit.NOMBRE_ESTANDAR as ASSET_NAME,
						            TO_CHAR( cga.MES, 'mmyyyy' ) as MESPIVOT,
						            trs.CANTIDAD
						        from
						            DDA_T_CONTRATO cnt,
						            DDA_T_CARGA_TRANSACCION cga,
						            DDA_T_CARGA_TRANSACCION_TITULO trs,
						            DDA_T_CNT_TITULO tit
						        where
						                trs.ID_CONTRATO = cnt.ID_CONTRATO_ORIGINAL
						            and cga.ID_CARGA_TRANS = trs.ID_CARGA_TRANS
						            and trs.ID_CONTRATO = tit.ID_CONTRATO
						            and trs.ID_BV = tit.ID_BV
						            and trs.ID_FILIAL = tit.ID_FILIAL
						            and cnt.ID_ESTUDIO = #{id_estudio}
						            and trs.TYPE_ABONO = #{typeAbono}
						            and cnt.ID_ESTATUS in ( 5, 6, 8, 9 )                
						            ${condicionEstatusCarga}        
						            and cga.MES >= to_date(#{mesInicial} || '-' || #{anioInicial}, 'mm-yyyy') 
						            and cga.MES &lt;= to_date(#{mesFinal} || '-' || #{anioFinal}, 'mm-yyyy') 
						    )
						    PIVOT( SUM( CANTIDAD ) FOR 
						                            MESPIVOT in ( 
						                                        ${meses_pivote} )
						                            ) 
						)
						group by 
						    grouping sets
						    (
						        (),
						        (ASSET_NAME)
						    )
						order by ANORDEN nulls last
	                            
	 </select>	  
	 
</mapper>