package com.mx.dla.dda.reporte.pago.bos;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.mx.dla.dda.reporte.pago.daos.ReportePagoAlternoDAO;
import com.mx.dla.dda.reporte.pago.dtos.DetalleContratoDTO;
import com.mx.dla.dda.reporte.pago.dtos.ReporteAddOnRowDTO;
import com.mx.dla.dda.reporte.pago.dtos.ReporteCRowDTO;
import com.mx.dla.dda.reporte.pago.dtos.ReporteInterfazContingenciaDTO;
import com.mx.dla.dda.reporte.pago.dtos.ReporteRowDTO;
import com.mx.dla.global.bos.BaseBO;

public class ReportePagoAlternoImpl extends BaseBO implements ReportePagoAlternoDAO {

	private DataSource dataSource;

	public ReportePagoAlternoImpl(DataSource datasource) {
		this.dataSource = datasource;
	}

	@Override
	public List<DetalleContratoDTO> detalleDontratos() {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "SELECT ID_CONTRATO, NUMERO_CONTRATO FROM DDA_T_CONTRATO";
		List<DetalleContratoDTO> listaDetalleContrato = jdbcTemplate.query(sql, new RowMapper<DetalleContratoDTO>() {
			@Override
			public DetalleContratoDTO mapRow(ResultSet rs, int rowNumber) throws SQLException {
				DetalleContratoDTO detalleContrato = new DetalleContratoDTO();
				detalleContrato.setIdContrato(rs.getLong("ID_CONTRATO"));
				detalleContrato.setNumeroContrato(rs.getString("NUMERO_CONTRATO"));
				return detalleContrato;
			}
		});
		return listaDetalleContrato;
	}

	@Override
	public List<ReporteCRowDTO> obtenerReporteRowsCExcel(String idContrato, Long idEstudio) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		String sql = "select ' ' v, e.DESC_ESTUDIO a,C.ID_SAP b,T.DESC_TITULO c,t.ID_TITULO_CNT d,  "
				+ "				 '07UOVA0001' e,c.NUMERO_CONTRATO f,T.ID_BV g, to_char(T.COSTO,999999999.99) h,  "
				+ "                 T.FECHA_INICIO i,T.FECHA_CONTABILIZACION j,  "
				+ "				 SUBSTR(T.VIDA_UTIL ,0,2) k , " + "				 SUBSTR(T.VIDA_UTIL ,3) l  "
				+ "                 from DDA_T_CONTRATO C  "
				+ "				 INNER JOIN DDA_T_CONTRATO_TITULO T ON T.ID_CONTRATO =C.ID_CONTRATO  "
				+ "                    INNER JOIN dda_c_estudio e on e.id_estudio=c.id_estudio  "
				+ "				 where C.ID_ESTATUS=5  "
				+ "				 AND to_date(T.FECHA_INICIO,'dd/mm/yyyy') <= to_date(SYSDATE,'dd/mm/yyyy') "
				+ "				 AND (T.ID_SAP IS NULL OR T.FECHA_NOTIFSAP IS NULL)  ";

		if (idEstudio > 0) {
			sql = sql + " AND e.id_estudio =" + idEstudio;
		}

		if (!idContrato.isEmpty() && idContrato.length() > 0) {
			sql = sql + " AND c.ID_CONTRATO ='" + idContrato + "'";
		}
		logger.info("Query ejecutada: " + sql);
		List<ReporteCRowDTO> listaReporteRows = jdbcTemplate.query(sql, new RowMapper<ReporteCRowDTO>() {
			@Override
			public ReporteCRowDTO mapRow(ResultSet rs, int rowNumber) throws SQLException {
				ReporteCRowDTO reporteRow = new ReporteCRowDTO();
				reporteRow.setV(rs.getString("v"));
				reporteRow.setConcepto(rs.getString("a"));
				reporteRow.setM1(rs.getString("b"));
				reporteRow.setM2(rs.getString("c"));
				reporteRow.setM3(rs.getString("d"));
				reporteRow.setM4(rs.getString("e"));
				reporteRow.setM5(rs.getString("f"));
				reporteRow.setM6(rs.getString("g"));
				reporteRow.setM7(rs.getString("h"));
				reporteRow.setM8(rs.getString("i"));
				reporteRow.setM9(rs.getString("j"));
				reporteRow.setM10(rs.getString("k"));
				reporteRow.setM11(rs.getString("l"));
				return reporteRow;
			}
		});
		return listaReporteRows;
	}

	@Override
	public List<ReporteRowDTO> obtenerReporteRows(Long idContrato, Long idEstudio, Map<String, String> parametros) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String andIdContrato = "";
		String andIdEstudio = "";

		andIdEstudio = idEstudio != null ? "and cnt.ID_ESTUDIO = " + idEstudio : "";
		andIdContrato = idContrato != null ? "and cnt.ID_CONTRATO = " + idContrato : "";

		String sql = "select " + "NVL( NOMBRE_ESTANDAR, NVL( NUMERO_CONTRATO, 'Total' ) ) as CONCEPTO, "
				+ "M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12, M13, 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( "
				+ parametros.get("fechaInicio") + ",'dd-mm-yyyy') and to_date(" + parametros.get("fechaFin")
				+ ",'dd-mm-yyyy') " + andIdEstudio + andIdContrato + "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 ( " + parametros.get("M1") + " as M1, "
				+ parametros.get("M2") + " as M2, " + parametros.get("M3") + " as M3, " + parametros.get("M4")
				+ " as M4, " + parametros.get("M5") + " as M5, " + parametros.get("M6") + " as M6, "
				+ parametros.get("M7") + " as M7, " + parametros.get("M8") + " as M8, " + parametros.get("M9")
				+ " as M9, " + parametros.get("M10") + " as M10, " + parametros.get("M11") + " as M11, "
				+ parametros.get("M12") + " as M12, " + parametros.get("M13") + " as M13, '-' as TOTAL ) ) "
				+ "ORDER BY NUMERO_CONTRATO nulls first, DECODE( NVL( ID_TITULO_CNTORIG, 0 ), 0, 0, -1, 1, -2, 2, 3 ),  NOMBRE_ESTANDAR nulls first";

		List<ReporteRowDTO> listaReporteRows = jdbcTemplate.query(sql, new RowMapper<ReporteRowDTO>() {
			@Override
			public ReporteRowDTO mapRow(ResultSet rs, int rowNumber) throws SQLException {
				ReporteRowDTO reporteRow = new ReporteRowDTO();
				reporteRow.setConcepto(rs.getString("CONCEPTO"));
				reporteRow.setM1(rs.getDouble("M1"));
				reporteRow.setM2(rs.getDouble("M2"));
				reporteRow.setM3(rs.getDouble("M3"));
				reporteRow.setM4(rs.getDouble("M4"));
				reporteRow.setM5(rs.getDouble("M5"));
				reporteRow.setM6(rs.getDouble("M6"));
				reporteRow.setM7(rs.getDouble("M7"));
				reporteRow.setM8(rs.getDouble("M8"));
				reporteRow.setM9(rs.getDouble("M9"));
				reporteRow.setM10(rs.getDouble("M10"));
				reporteRow.setM11(rs.getDouble("M11"));
				reporteRow.setM12(rs.getDouble("M12"));
				reporteRow.setM13(rs.getDouble("M13"));
				reporteRow.setTotal(rs.getDouble("TOTAL"));
				return reporteRow;
			}
		});
		return listaReporteRows;
	}

	@Override
	public List<Map<String, String>> obtenerListaMapas() {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "SELECT NUMERO_CONTRATO, AMORTIZABLE FROM DDA_T_CONTRATO";

		List<Map<String, String>> listaMapas = jdbcTemplate.query(sql, new RowMapper<Map<String, String>>() {
			@Override
			public Map<String, String> mapRow(ResultSet rs, int rowNumber) throws SQLException {
				ResultSetMetaData rsmd = rs.getMetaData();
				int ncolumnas = rsmd.getColumnCount();
				Map<String, String> mapa = new HashMap<String, String>();
				for (int i = 1; i <= ncolumnas; i++) {
					mapa.put(rsmd.getColumnName(i), rs.getString(rsmd.getColumnName(i)));
				}
				return mapa;
			}
		});
		return listaMapas;
	}
	
	@Override
	public List<ReporteInterfazContingenciaDTO> obtenerReporteRowsC(String idContrato, Long idEstudio) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		StringBuilder query = new StringBuilder();

		query.append("select dtc.NUMERO_CONTRATO AS CONTRATO, dtct.DESC_TITULO AS titulo, dtct.id_bv AS idBroadview, dtct.fecha_inicio AS fechaInicio,dtct.fecha_fin AS fechaFin, ");
		query.append("SUBSTR(dtct.VIDA_UTIL ,0,2) anio, SUBSTR(dtct.VIDA_UTIL ,3) meses, dtct.costo, dtc.ID_CONTRATO AS idContrato ");
		query.append("FROM DDA_T_CONTRATO_TITULO dtct ");
		query.append("INNER JOIN DDA_T_CONTRATO dtc ON dtct.ID_CONTRATO = dtc.ID_CONTRATO ");
		query.append("where dtc.ID_ESTATUS = 5 ");
		query.append("and to_date(dtct.FECHA_INICIO,'dd/mm/yyyy') <= to_date(SYSDATE,'dd/mm/yyyy') ");
		query.append("and dtct.ID_SAP is null or dtct.FECHA_NOTIFSAP is NULL ");

		if(idEstudio>0) {
			query.append("AND dtc.id_estudio = ");
			query.append(idEstudio);
		}

		if(!idContrato.isEmpty()&&idContrato.length()>0) {
			query.append(" AND dtc.ID_CONTRATO = ");
			query.append(idContrato);
		}

		query.append(" order by dtct.FECHA_INICIO desc");
		System.out.println(query.toString());
		List<ReporteInterfazContingenciaDTO> listaReporteRows =  jdbcTemplate.query(query.toString(), 
				new BeanPropertyRowMapper<ReporteInterfazContingenciaDTO>(ReporteInterfazContingenciaDTO.class));

		return listaReporteRows;
	}
	
	@Override
	public List<ReporteInterfazContingenciaDTO> obtenerReporteRowsEnmienda(String idContrato) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		StringBuilder query = new StringBuilder();

		query.append("SELECT dtc.NUMERO_CONTRATO AS CONTRATO, DESC_TITULO AS titulo, a.ID_BV AS idBroadview, a.FECHA_INICIO AS fechaInicio, a.FECHA_FIN AS fechaFin, COSTO, ");
		query.append(" SUBSTR(VIDA_UTIL ,0,2) AS anio, SUBSTR(VIDA_UTIL ,3) AS meses, b.ID_CONTRATO AS idContrato  FROM DDA_T_CONTRATO_TITULO a "); 
		query.append(" JOIN DDA_T_CRN_CLIENTESAP b ON a.ID_TITULO_CNT = b.ID_TITULO_CNT  ");
		query.append(" JOIN DDA_T_CONTRATO dtc ON a.ID_CONTRATO = dtc.ID_CONTRATO" );
		query.append(" WHERE a.ID_CONTRATO =  ");
		query.append(idContrato);
		query.append(" AND to_date(a.FECHA_CONTABILIZACION,'dd/mm/yyyy') <= to_date(sysdate,'dd/mm/yyyy') ");
		query.append(" and FECHA_ENVIO_OK is NULL ");
		query.append(" order by a.FECHA_INICIO DESC ");
		System.out.println(query.toString());
		List<ReporteInterfazContingenciaDTO> listaReporteRows =  jdbcTemplate.query(query.toString(), 
				new BeanPropertyRowMapper<ReporteInterfazContingenciaDTO>(ReporteInterfazContingenciaDTO.class));

		return listaReporteRows;
	}

	@Override
	public List<ReporteCRowDTO> obtenerReporteRowsS(Date fechaInicio) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		String sql = " SELECT ID_CARGA aid, " + "TIPO_REPORTE a, " + "USUARIOS_PRUEBA b, " + "PAIS c, " + "ADD_ON d, "
				+ "ESTATUS_PAGO e, " + "ABONO f, " + "PRECIO_MONEDA_LOCAL g, " + "MEDIO_PAGO_NOMBRE h, "
				+ "CANTIDAD_CLIENTES i, " + "ID_PRODUCTO j, " + "NOMBRE_PRODUCTO k, " + "REGLA1_SUSCRITO_ESPECIAL l, "
				+ "REGLA1_MONEDA_MAYOR_CERO m, " + "MES_CARGA n, " + "FECHA_REGISTRO o FROM DDA_T_SUSCRIPTORES_M";

		List<ReporteCRowDTO> listaReporteRows = jdbcTemplate.query(sql, new RowMapper<ReporteCRowDTO>() {
			@Override
			public ReporteCRowDTO mapRow(ResultSet rs, int rowNumber) throws SQLException {
				ReporteCRowDTO reporteRow = new ReporteCRowDTO();
				reporteRow.setM1(rs.getString("a"));
				reporteRow.setM2(rs.getString("b"));
				reporteRow.setM3(rs.getString("c"));
				reporteRow.setM4(rs.getString("d"));
				reporteRow.setM5(rs.getString("e"));
				reporteRow.setM6(rs.getString("f"));
				reporteRow.setM7(rs.getString("g"));
				reporteRow.setM8(rs.getString("h"));
				reporteRow.setM9(rs.getString("i"));
				reporteRow.setM10(rs.getString("j"));
				reporteRow.setM11(rs.getString("k"));
				reporteRow.setM12(rs.getString("l"));
				reporteRow.setM13(rs.getString("m"));

				reporteRow.setTotal(rs.getString("n"));
				return reporteRow;
			}
		});
		return listaReporteRows;
	}

	@Override
	public List<ReporteAddOnRowDTO> obtenerReporteGeneral(Date fechaInicio, Long idEstudio, String notIn) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Calendar c = Calendar.getInstance();
		c.setTime(fechaInicio);

		int mes = c.get(Calendar.MONTH) + 1;
		int anio = c.get(Calendar.YEAR);

		String sql = "SELECT T.TIPO_REPORTE B,  T.PAIS E, T.ADD_ON F, MEDIO_PAGO G, T.MES_CARGA N, T.PRECIO_NETO P, T.TIPO_DE_CAMBIO Q, "
				+ "T.SUSCRIPTORES S, T.REVENUE_SHARE T, "
				+ "ROUND(T.PRECIO_NETO * T.SUSCRIPTORES,2) F1,"
				+ "ROUND(T.PRECIO_NETO * T.SUSCRIPTORES * (t.REVENUE_SHARE/100),2) F2,"
				+ "ROUND(T.PRECIO_NETO * T.SUSCRIPTORES * (t.REVENUE_SHARE/100) / T.TIPO_DE_CAMBIO,2) FD1, "
				+ "ROUND((T.SUSCRIPTORES * ( T.PRECIO_NETO  / 1.16 ) - ( 24.5 * T.SUSCRIPTORES ) ) * 2, 2) F4, "
				+ "ROUND((T.SUSCRIPTORES * ( T.PRECIO_NETO  / 1.16 ) - ( 24.5 * T.SUSCRIPTORES ) ) * 2 / T.TIPO_DE_CAMBIO,2) FD4, "
				+ "ROUND(T.PRECIO_NETO  * T.SUSCRIPTORES,2) F5 "
				+ "FROM DDA_CV_ADD_ONS_HISTORICO T "
				+ "WHERE EXTRACT(MONTH FROM T.MES_CARGA)= " + mes + " AND EXTRACT(YEAR FROM T.MES_CARGA)=" + anio + " AND T.ID_ESTUDIO = " + idEstudio 
				+ " AND T.PAIS NOT IN (" + notIn + ") "
				+ " ORDER BY E, P";
		
		System.out.println("Query ejecutada: " + sql);
		List<ReporteAddOnRowDTO> listaReporteRows = jdbcTemplate.query(sql, new RowMapper<ReporteAddOnRowDTO>() {
			@Override
			public ReporteAddOnRowDTO mapRow(ResultSet rs, int rowNumber) throws SQLException {
				ReporteAddOnRowDTO reporteRow = new ReporteAddOnRowDTO();

				reporteRow.setM2(rs.getString("B"));
				reporteRow.setM5(rs.getString("E"));
				reporteRow.setM6(rs.getString("F"));
				reporteRow.setM7(rs.getString("G"));
				reporteRow.setM14(textoFechaIniFinMes(rs.getString("N")));
				reporteRow.setM16(rs.getString("P"));
				reporteRow.setM17(rs.getString("Q"));
				reporteRow.setM19(rs.getString("S"));
				reporteRow.setM20(rs.getString("T"));
				reporteRow.setF1(rs.getString("F1"));
				reporteRow.setF2(rs.getString("F2"));
				reporteRow.setF4(rs.getString("F4"));
				reporteRow.setF5(rs.getString("F5"));
				reporteRow.setFd1(rs.getString("FD1"));
				reporteRow.setFd4(rs.getString("FD4"));

				return reporteRow;
			}
		});
		return listaReporteRows;
	}
	
	@Override
	public List<ReporteAddOnRowDTO> obtenerReporteGeneralHBO(Date fechaInicio, Long IdEstudio, String notIn) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Calendar c = Calendar.getInstance();
		c.setTime(fechaInicio);

		int mes = c.get(Calendar.MONTH) + 1;
		int annio = c.get(Calendar.YEAR);

		c.add(Calendar.MONTH, -1);

		String sql = "SELECT B,E,F,G,N,P,Q,SUM(S) S,T,"
				+ " TO_CHAR(P * SUM(S),'999999999.99') F1,"
				+ " TO_CHAR(P * SUM(S) * (T/100),'999999999.99') F2,"
				+ " TO_CHAR(P * SUM(S) * (T/100) / Q,'999999999.99') FD1,  "
				+ " TO_CHAR((SUM(S) * ( P / 1.16 ) - ( 24.5 * SUM(S)) * 2),'999999999.99') F4, "
				+ " TO_CHAR((SUM(S) * ( P / 1.16 ) - ( 24.5 * SUM(S)) * 2 / Q),'999999999.99') FD4,  "
				+ " TO_CHAR(P * SUM(S) ,'999999999.99') F5"
				+ " FROM "
				+ " (SELECT T.TIPO_REPORTE B, T.PAIS E, T.ADD_ON F, 'MEDIO_PAGO_NOMBRE' G, T.MES_CARGA N, 0 P, T.TIPO_DE_CAMBIO Q, T.PRECIO,"
				+ " CEIL(SUM(T.CANTIDAD_CLIENTES) - SUM(T.CANTIDAD_CLIENTES) * MIN(C.RED_CLIENTES)/100) S, T.REVENUE_SHARE T, C.RED_CLIENTES"
				+ " FROM DDA_T_CALCULO_M_ADD_ONS T  INNER JOIN DDA_C_ESTUDIO_ADD_ON C ON T.ID_ESTUDIO = C.ID_ESTUDIO"
				+ " WHERE (T.ESTATUS_REGLA1 = 1 AND T.ESTATUS_REGLA2 = 0) AND EXTRACT(MONTH FROM T.MES_CARGA)= " + mes + " AND EXTRACT(YEAR FROM T.MES_CARGA)=" + annio + " "
				+ " AND C.ID_ESTUDIO = " + IdEstudio + " AND T.PAIS NOT IN ('ARGENTINA')"
				+ " GROUP BY T.TIPO_REPORTE, T.PAIS, T.ADD_ON, T.MES_CARGA, T.TIPO_DE_CAMBIO, T.REVENUE_SHARE, T.PRECIO, C.RED_CLIENTES"
				+ " UNION ALL"
				+ " SELECT T.TIPO_REPORTE B, T.PAIS, T.ADD_ON F, "
				+ " CASE WHEN T.ADD_ON = 'HBO' AND T.MEDIO_PAGO_NOMBRE = 'ITUNES' THEN T.MEDIO_PAGO_NOMBRE ELSE 'MEDIO_PAGO_NOMBRE' END G, "
				+ " T.MES_CARGA N, MIN(T.PRECIO_NETO) AS P, "
				+ " T.TIPO_DE_CAMBIO Q, T.PRECIO, CEIL(SUM(T.CANTIDAD_CLIENTES) - SUM(T.CANTIDAD_CLIENTES) * MIN(C.RED_CLIENTES)/100) S, T.REVENUE_SHARE T, C.RED_CLIENTES"
				+ " FROM DDA_T_CALCULO_M_ADD_ONS T  INNER JOIN DDA_C_ESTUDIO_ADD_ON C ON T.ID_ESTUDIO = C.ID_ESTUDIO"
				+ " WHERE (T.ESTATUS_REGLA2 = 1 OR ESTATUS_REGLA1 = T.ESTATUS_REGLA2) AND EXTRACT(MONTH FROM T.MES_CARGA)= "
				+ mes + " AND EXTRACT(YEAR FROM T.MES_CARGA)=" + annio + " AND C.ID_ESTUDIO = " + IdEstudio + " AND T.PAIS NOT IN ('ARGENTINA')"
				+ " GROUP BY T.TIPO_REPORTE, T.PAIS,T.ADD_ON, T.MEDIO_PAGO_NOMBRE, T.MES_CARGA, T.PRECIO, T.TIPO_DE_CAMBIO, T.REVENUE_SHARE, C.RED_CLIENTES) reporte"
				+ " GROUP BY B,E,F,G,N,P,Q,T ORDER BY E, P";

		logger.info("Query ejecutada: " + sql);
		List<ReporteAddOnRowDTO> listaReporteRows = jdbcTemplate.query(sql, new RowMapper<ReporteAddOnRowDTO>() {
			@Override
			public ReporteAddOnRowDTO mapRow(ResultSet rs, int rowNumber) throws SQLException {
				ReporteAddOnRowDTO reporteRow = new ReporteAddOnRowDTO();
				reporteRow.setM2(rs.getString("B"));
				reporteRow.setM5(rs.getString("E"));
				reporteRow.setM6(rs.getString("F"));
				reporteRow.setM7(rs.getString("G"));
				reporteRow.setM14(textoFechaIniFinMes(rs.getString("N")));
				reporteRow.setM16(rs.getString("P"));
				reporteRow.setM17(rs.getString("Q"));
				reporteRow.setM19(rs.getString("S"));
				reporteRow.setM20(rs.getString("T"));
				reporteRow.setF1(rs.getString("F1"));
				reporteRow.setF2(rs.getString("F2"));
				reporteRow.setF4(rs.getString("F4"));
				reporteRow.setF5(rs.getString("F5"));
				reporteRow.setFd1(rs.getString("FD1"));
				reporteRow.setFd4(rs.getString("FD4"));

				return reporteRow;
			}
		});
		return listaReporteRows;
	}

	@Override
	public List<ReporteAddOnRowDTO> obtenerReporteKaraokeQello(Date fechaInicio, Long idEstudio, String notIn) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Calendar c = Calendar.getInstance();
		c.setTime(fechaInicio);

		int mes = c.get(Calendar.MONTH) + 1;
		int anio = c.get(Calendar.YEAR);

		String sql = "SELECT TIPO_REPORTE B, PAIS E, ADD_ON F, MEDIO_PAGO G, MES_CARGA N, PRECIO_NETO P, TIPO_DE_CAMBIO Q, "
				+ " SUSCRIPTORES S, REVENUE_SHARE T,"
				+ " TO_CHAR(PRECIO_NETO * SUSCRIPTORES,'999999999.99') AS R,"
				+ " TO_CHAR(PRECIO_NETO * SUSCRIPTORES * 0.5,'999999999.99') AS F3,"
				+ " TO_CHAR(PRECIO_NETO * SUSCRIPTORES * (REVENUE_SHARE/100),'999999999.99') AS T,"
				+ " TO_CHAR(CASE WHEN (PRECIO_NETO * SUSCRIPTORES * 0.5) > (PRECIO_NETO * SUSCRIPTORES * (REVENUE_SHARE/100))"
				+ " THEN (PRECIO_NETO * SUSCRIPTORES * 0.5) ELSE (PRECIO_NETO * SUSCRIPTORES * (REVENUE_SHARE/100))  END,'999999999.99') AS U,"
				+ " TO_CHAR(CASE WHEN (PRECIO_NETO * SUSCRIPTORES * 0.5) > (PRECIO_NETO * SUSCRIPTORES * (REVENUE_SHARE/100))"
				+ " THEN (PRECIO_NETO * SUSCRIPTORES * 0.5) /TIPO_DE_CAMBIO"
				+ " ELSE (PRECIO_NETO * SUSCRIPTORES * (REVENUE_SHARE/100))/TIPO_DE_CAMBIO  END,'999999999.99') AS F2"
				+ " FROM DDA_CV_ADD_ONS_HISTORICO"
				+ " WHERE EXTRACT(MONTH FROM MES_CARGA)= " + mes + " AND EXTRACT(YEAR FROM MES_CARGA)=" + anio 
				+ " AND ID_ESTUDIO = " + idEstudio + " AND PAIS NOT IN (" + notIn + ")"
				+ " ORDER BY PAIS, PRECIO_NETO";

		logger.info("Query ejecutada: " + sql);
		List<ReporteAddOnRowDTO> listaReporteRows = jdbcTemplate.query(sql, new RowMapper<ReporteAddOnRowDTO>() {
			@Override
			public ReporteAddOnRowDTO mapRow(ResultSet rs, int rowNumber) throws SQLException {
				ReporteAddOnRowDTO reporteRow = new ReporteAddOnRowDTO();
				reporteRow.setM2(rs.getString("B"));
				reporteRow.setM5(rs.getString("E"));
				reporteRow.setM14(textoFechaIniFinMes(rs.getString("N")));
				reporteRow.setM16(rs.getString("P"));
				reporteRow.setM18(rs.getString("R"));
				reporteRow.setM19(rs.getString("S"));
				reporteRow.setM20(rs.getString("T"));
				reporteRow.setM21(rs.getString("U"));
				reporteRow.setF2(rs.getString("F2"));
				reporteRow.setF3(rs.getString("F3"));

				return reporteRow;
			}
		});
		return listaReporteRows;
	}
	
	@Override
	public List<ReporteAddOnRowDTO> obtenerReporteKaraokeQelloArgentina(Date fechaInicio, Long IdEstudio) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Calendar c = Calendar.getInstance();
		c.setTime(fechaInicio);

		int mes = c.get(Calendar.MONTH) + 1;
		int annio = c.get(Calendar.YEAR);

		c.add(Calendar.MONTH, -1);

		String sql = " SELECT b,c,e,f,n,p,q,s,ts,"
				+ " TO_CHAR(p * s,'999999999.99') AS R, "
				+ " TO_CHAR(p * s * 0.5,'999999999.99') AS F3, "
				+ " TO_CHAR(p * s * (ts/100),'999999999.99') AS T, "
				+ " TO_CHAR(CASE WHEN (p * s * 0.5) >  (p * s * (ts/100)) THEN (p * s * 0.5) ELSE (p * s * (ts/100)) END,'999999999.99') AS U, "
				+ " TO_CHAR(CASE WHEN (p * s * 0.5) >  (p * s * (ts/100)) THEN (p * s * 0.5)/q ELSE (p * s * (ts/100))/q  END,'999999999.99') AS F2"
				+ " FROM (SELECT  t.tipo_reporte b, t.fecha_registro c, T.PAIS||' '||T.MEDIO_PAGO_NOMBRE E, t.add_on f, t.mes_carga n, "
				+ " CASE WHEN T.MEDIO_PAGO_NOMBRE = 'FIJA' THEN MIN(T.PRECIO_NETO_FIJO) ELSE MIN(T.PRECIO_NETO) END AS p, t.tipo_de_cambio q, "
				+ " CEIL(SUM(T.CANTIDAD_CLIENTES) - SUM(T.CANTIDAD_CLIENTES) * MIN(C.RED_CLIENTES)/100) AS s, t.revenue_share ts,t.precio, t.medio_pago_nombre"
				+ " FROM DDA_T_CALCULO_M_ADD_ONS T  "
				+ " INNER JOIN DDA_C_ESTUDIO_ADD_ON C ON T.ID_ESTUDIO = C.ID_ESTUDIO  AND"
				+ " EXTRACT(MONTH FROM t.mes_carga) = " + mes + " AND EXTRACT(YEAR FROM t.mes_carga) = " + annio + " AND c.id_estudio = " + IdEstudio + " AND T.PAIS = 'ARGENTINA'"
				+ " GROUP BY t.tipo_reporte, t.fecha_registro, t.pais, t.add_on, t.mes_carga, "
				+ " t.fecha_registro, t.tipo_de_cambio, t.tarifa_minima, t.revenue_share, t.precio, t.medio_pago_nombre) reporte"
				+ " GROUP BY b,c,e,f,n,p,q,s,ts ORDER BY e,p";

		logger.info("Query ejecutada: " + sql);
		List<ReporteAddOnRowDTO> listaReporteRows = jdbcTemplate.query(sql, new RowMapper<ReporteAddOnRowDTO>() {
			@Override
			public ReporteAddOnRowDTO mapRow(ResultSet rs, int rowNumber) throws SQLException {
				ReporteAddOnRowDTO reporteRow = new ReporteAddOnRowDTO();
				reporteRow.setM2(rs.getString("B"));
				reporteRow.setM5(rs.getString("E"));
				reporteRow.setM14(textoFechaIniFinMes(rs.getString("N")));
				reporteRow.setM16(rs.getString("P"));
				reporteRow.setM18(rs.getString("R"));
				reporteRow.setM19(rs.getString("S"));
				reporteRow.setM20(rs.getString("T"));
				reporteRow.setM21(rs.getString("U"));
				reporteRow.setF2(rs.getString("F2"));
				reporteRow.setF3(rs.getString("F3"));

				return reporteRow;
			}
		});
		return listaReporteRows;
	}

	@Override
	public List<ReporteAddOnRowDTO> obtenerReporteGeneralArgentina(Date fechaInicio, Long IdEstudio) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Calendar c = Calendar.getInstance();
		c.setTime(fechaInicio);

		int mes = c.get(Calendar.MONTH) + 1;
		int annio = c.get(Calendar.YEAR);

		c.add(Calendar.MONTH, -1);

		String sql = " SELECT B,E,F,G,N,P,Q,sum(S) S,T,"
				+ " TO_CHAR(P * SUM(S),'999999999.99') F1,"
				+ " TO_CHAR(P * SUM(S) * (T/100),'999999999.99') F2,"
				+ " TO_CHAR(P * SUM(S) * (T/100) / Q,'999999999.99') FD1,"
				+ " TO_CHAR((SUM(S) * ( P / 1.16 ) - ( 24.5 * SUM(S)) * 2),'999999999.99') F4,"
				+ " TO_CHAR((SUM(S) * ( P / 1.16 ) - ( 24.5 * SUM(S)) * 2 / Q),'999999999.99') FD4,"
				+ " TO_CHAR(P * CEIL(SUM(S) - SUM(S) * (MIN(RED_CLIENTES)/100)) ,'999999999.99') F5"
				+ " FROM "
				+ " (SELECT T.TIPO_REPORTE B, T.PAIS E, T.ADD_ON F, 'MEDIO_PAGO_NOMBRE' G, T.MES_CARGA N, 0 P, T.TIPO_DE_CAMBIO Q,  T.PRECIO,"
				+ " CEIL(SUM(T.CANTIDAD_CLIENTES) - SUM(T.CANTIDAD_CLIENTES) * MIN(C.RED_CLIENTES)/100) S, T.REVENUE_SHARE T, C.RED_CLIENTES"
				+ " FROM DDA_T_CALCULO_M_ADD_ONS T  INNER JOIN DDA_C_ESTUDIO_ADD_ON C ON T.ID_ESTUDIO = C.ID_ESTUDIO"
				+ " WHERE (T.ESTATUS_REGLA1 = 1 AND T.ESTATUS_REGLA2 = 0) AND EXTRACT(MONTH FROM T.MES_CARGA)= " + mes + " AND EXTRACT(YEAR FROM T.MES_CARGA)=" + annio + " AND C.ID_ESTUDIO = " + IdEstudio + " AND T.PAIS = 'ARGENTINA'"
				+ " GROUP BY T.TIPO_REPORTE, T.PAIS, T.ADD_ON, T.MES_CARGA, T.TIPO_DE_CAMBIO, T.REVENUE_SHARE,T.PRECIO_NETO,  T.PRECIO, C.RED_CLIENTES"
				+ " UNION ALL"
				+ " SELECT T.TIPO_REPORTE B, T.PAIS||' '||T.MEDIO_PAGO_NOMBRE E, T.ADD_ON F, T.MEDIO_PAGO_NOMBRE G, T.MES_CARGA N,"
				+ " CASE WHEN T.MEDIO_PAGO_NOMBRE = 'FIJA' THEN MIN(T.PRECIO_NETO_FIJO) ELSE MIN(T.PRECIO_NETO) END AS P, "
				+ " T.TIPO_DE_CAMBIO Q, T.PRECIO, CEIL(SUM(T.CANTIDAD_CLIENTES) - SUM(T.CANTIDAD_CLIENTES) * MIN(C.RED_CLIENTES)/100) S, T.REVENUE_SHARE T, C.RED_CLIENTES"
				+ " FROM DDA_T_CALCULO_M_ADD_ONS T  INNER JOIN DDA_C_ESTUDIO_ADD_ON C ON T.ID_ESTUDIO = C.ID_ESTUDIO"
				+ " WHERE (T.ESTATUS_REGLA2 = 1 OR ESTATUS_REGLA1 = T.ESTATUS_REGLA2) AND EXTRACT(MONTH FROM T.MES_CARGA)="
				+ mes + " AND EXTRACT(YEAR FROM T.MES_CARGA)=" + annio + " AND C.ID_ESTUDIO = " + IdEstudio + " AND T.PAIS = 'ARGENTINA'"
				+ " GROUP BY T.TIPO_REPORTE, T.PAIS,T.ADD_ON, MEDIO_PAGO_NOMBRE, T.MES_CARGA, T.PRECIO, T.TIPO_DE_CAMBIO, T.REVENUE_SHARE, C.RED_CLIENTES) reporte"
				+ " GROUP BY B,E,F,G,N,P,Q,T ORDER BY P,E";

		logger.info("Query ejecutada: " + sql);
		List<ReporteAddOnRowDTO> listaReporteRows = jdbcTemplate.query(sql, new RowMapper<ReporteAddOnRowDTO>() {
			@Override
			public ReporteAddOnRowDTO mapRow(ResultSet rs, int rowNumber) throws SQLException {
				ReporteAddOnRowDTO reporteRow = new ReporteAddOnRowDTO();
				reporteRow.setM2(rs.getString("B"));
				reporteRow.setM5(rs.getString("E"));
				reporteRow.setM6(rs.getString("F"));
				reporteRow.setM7(rs.getString("G"));
				reporteRow.setM14(textoFechaIniFinMes(rs.getString("N")));
				reporteRow.setM16(rs.getString("P"));
				reporteRow.setM17(rs.getString("Q"));
				reporteRow.setM19(rs.getString("S"));
				reporteRow.setM20(rs.getString("T"));
				reporteRow.setF1(rs.getString("F1"));
				reporteRow.setF2(rs.getString("F2"));
				reporteRow.setF4(rs.getString("F4"));
				reporteRow.setF5(rs.getString("F5"));
				reporteRow.setFd1(rs.getString("FD1"));
				reporteRow.setFd4(rs.getString("FD4"));

				return reporteRow;
			}
		});
		return listaReporteRows;
	}

	@Override
	public List<ReporteAddOnRowDTO> obtenerReporteGeneralMexico(Date fechaInicio, Long idEstudio) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Calendar c = Calendar.getInstance();
		c.setTime(fechaInicio);

		int mes = c.get(Calendar.MONTH) + 1;
		int anio = c.get(Calendar.YEAR);

		String sql = "SELECT T.TIPO_REPORTE B, T.PAIS E, T.ADD_ON F, MEDIO_PAGO G, T.MES_CARGA N, T.PRECIO_NETO P, T.TIPO_DE_CAMBIO Q,"
				+ "	T.SUSCRIPTORES S, T.REVENUE_SHARE T, '0'  F5, '0' FD4, '0' F3,"
				+ " CASE WHEN T.PRECIO_NETO = 0 THEN '0' ELSE"
				+ "	TO_CHAR(((T.SUSCRIPTORES * ( T.PRECIO_NETO / 1.16 ) - ( 24.5 * T.SUSCRIPTORES)) * 2) * (t.REVENUE_SHARE/100),'999999999.99') END F2, '0' F4,"
				+ " CASE WHEN T.PRECIO_NETO = 0 THEN '0' ELSE"
				+ "	TO_CHAR((T.SUSCRIPTORES * ( T.PRECIO_NETO / 1.16 ) - ( 24.5 * T.SUSCRIPTORES)) * 2,'999999999.99') END F1,"
				+ " CASE WHEN T.PRECIO_NETO = 0 THEN '0' ELSE"
				+ "	TO_CHAR(((T.SUSCRIPTORES * ( T.PRECIO_NETO / 1.16 ) - ( 24.5 * T.SUSCRIPTORES)) * 2) * (t.REVENUE_SHARE/100) / T.TIPO_DE_CAMBIO,'999999999.99') END FD1, '0' FD5"
				+ "	FROM DDA_CV_ADD_ONS_HISTORICO T"
				+ "	WHERE EXTRACT(MONTH FROM T.MES_CARGA)= " + mes + " AND EXTRACT(YEAR FROM T.MES_CARGA)=" + anio + " AND T.ID_ESTUDIO = " + idEstudio
				+ "	AND T.PAIS = 'MEXICO' ORDER BY E, P";

		System.out.println("Query ejecutada: " + sql);
		List<ReporteAddOnRowDTO> listaReporteRows = jdbcTemplate.query(sql, new RowMapper<ReporteAddOnRowDTO>() {
			@Override
			public ReporteAddOnRowDTO mapRow(ResultSet rs, int rowNumber) throws SQLException {
				ReporteAddOnRowDTO reporteRow = new ReporteAddOnRowDTO();
				reporteRow.setM2(rs.getString("B"));
				reporteRow.setM5(rs.getString("E"));
				reporteRow.setM6(rs.getString("F"));
				reporteRow.setM7(rs.getString("G"));
				reporteRow.setM14(textoFechaIniFinMes(rs.getString("N")));
				reporteRow.setM16(rs.getString("P"));
				reporteRow.setM17(rs.getString("Q"));
				reporteRow.setM19(rs.getString("S"));
				reporteRow.setM20(rs.getString("T"));
				reporteRow.setF1(rs.getString("F1"));
				reporteRow.setF2(rs.getString("F2"));
				reporteRow.setF3(rs.getString("F3"));
				reporteRow.setF4(rs.getString("F4"));
				reporteRow.setF5(rs.getString("F5"));
				reporteRow.setFd1(rs.getString("FD1"));
				reporteRow.setFd4(rs.getString("FD4"));
				reporteRow.setFd4(rs.getString("FD5"));

				return reporteRow;
			}
		});
		return listaReporteRows;
	}

	@Override
	public List<ReporteAddOnRowDTO> obtenerReporteFoxGeneral(Date fechaInicio, Long idEstudio) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Calendar c = Calendar.getInstance();
		c.setTime(fechaInicio);

		int mes = c.get(Calendar.MONTH) + 1;
		int anio = c.get(Calendar.YEAR);

		String sql = "SELECT TIPO_REPORTE B, PAIS E, ADD_ON F, MEDIO_PAGO G, MES_CARGA N, PRECIO_NETO P, TIPO_DE_CAMBIO Q, SUSCRIPTORES S, REVENUE_SHARE T,"
				+ "TO_CHAR(PRECIO_NETO * SUSCRIPTORES,'999999999.99')  F3,"
				+ "TO_CHAR(PRECIO_NETO * SUSCRIPTORES/ TIPO_DE_CAMBIO,'999999999.99') FD1,"
				+ "TO_CHAR((SUSCRIPTORES * PRECIO_NETO) * (REVENUE_SHARE/100),'999999999.99') F2,"
				+ "TO_CHAR((SUSCRIPTORES * PRECIO_NETO)  / TIPO_DE_CAMBIO,'999999999.99') F1, '0' F4, '0' FD4,"
				+ "TO_CHAR(PRECIO_NETO  * SUSCRIPTORES ,'999999999.99') F5 "
				+ "FROM DDA_CV_ADD_ONS_HISTORICO "
				+ "WHERE EXTRACT(MONTH FROM MES_CARGA)= " + mes + " AND EXTRACT(YEAR FROM MES_CARGA)= " + anio + " AND ID_ESTUDIO = " + idEstudio
				+ "AND PAIS = 'MEXICO' ORDER BY E, P";

		logger.info("Query ejecutada: " + sql);
		
		List<ReporteAddOnRowDTO> listaReporteRows = jdbcTemplate.query(sql, new RowMapper<ReporteAddOnRowDTO>() {
			@Override
			public ReporteAddOnRowDTO mapRow(ResultSet rs, int rowNumber) throws SQLException {
				ReporteAddOnRowDTO reporteRow = new ReporteAddOnRowDTO();
				reporteRow.setM2(rs.getString("B"));
				reporteRow.setM5(rs.getString("E"));
				reporteRow.setM6(rs.getString("F"));
				reporteRow.setM14(textoFechaIniFinMes(rs.getString("N")));
				reporteRow.setM16(rs.getString("P"));
				reporteRow.setM17(rs.getString("Q"));
				reporteRow.setM19(rs.getString("S"));
				reporteRow.setM20(rs.getString("T"));
				reporteRow.setF1(rs.getString("F1"));
				reporteRow.setF2(rs.getString("F2"));
				reporteRow.setF3(rs.getString("F3"));
				reporteRow.setF4(rs.getString("F4"));
				reporteRow.setF5(rs.getString("F5"));
				reporteRow.setFd1(rs.getString("FD1"));
				reporteRow.setFd4(rs.getString("FD4"));
				return reporteRow;
			}
		});
		return listaReporteRows;
	}

	private String textoFechaIniFinMes(String g) {
		String r = "";
		;
		System.out.println(g);
		SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			Date dataFormateada = formato.parse(g);
			SimpleDateFormat format = new SimpleDateFormat("dd MMMMMM yyyy");
			Calendar c = Calendar.getInstance();
			c.setTime(dataFormateada);
			c.add(Calendar.MONTH, 0);
			c.set(Calendar.DAY_OF_MONTH, 1); // Establecido en el 1, la fecha actual es el primer día del mes
			String first = format.format(c.getTime());

			// Obtener el último día del mes actual
			Calendar ca = Calendar.getInstance();
			ca.setTime(dataFormateada);
			ca.set(Calendar.DAY_OF_MONTH, ca.getActualMaximum(Calendar.DAY_OF_MONTH));
			String last = format.format(ca.getTime());

			r = first + " - " + last;

		} catch (ParseException e) {
			logger.error("Error. {}", e);
		}
		return r;
	}
}
