package com.ejie.ad74a.dao.iml;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ejie.ad74a.dao.PagosDao;
import com.ejie.ad74a.model.Pagos;
import com.ejie.ad74a.model.PrestadorServicio;
import com.ejie.ad74a.util.UtilSQL;
import com.ejie.x38.dto.TableManager;
import com.ejie.x38.dto.TableRequestDto;

/**
 * PagosDaoImpl generated by UDA, 15-sep-2023 12:25:35.
 * 
 * @author UDA
 */

@Repository
@Transactional
public class PagosDaoImpl implements PagosDao {

	private static final String FROM_PAGOS_T1 = " FROM PAGOS t1 ";
	private static final String PRESTADOR_SERVICIO_T2 = ",PRESTADOR_SERVICIO t2 ";
	private static final String WHERE_1_1 = " WHERE 1=1 ";
	private static final String ID_PAGO_74 = "IDPAGO74";
	private static final String ANYO_PAGO_74 = "ANYOPAGO74";
	private static final String MES_PAGO_74 = "MESPAGO74";
	private static final String ID_PRESTADOR_74 = "IDPRESTADOR74";
	private static final String F_ALTA_74 = "FALTA74";
	private static final String USU_ALTA_74 = "USUALTA74";
	private static final String F_MODIF_74 = "FMODIF74";
	private static final String USU_MODIF_74 = "USUMODIF74";
	private static final String ID_PRESTADOR_73 = "IDPRESTADOR73";
	private static final String IDENTIFICADOR_73 = "IDENTIFICADOR73";
	private static final String RAZON_SOCIAL_73 = "RAZONSOCIAL73";
	private static final String F_INICIO_SERVICIO_73 = "FINICIOSERVICIO73";
	private static final String F_FIN_SERVICIO_73 = "FFINSERVICIO73";
	private static final String F_ALTA_73 = "FALTA73";
	private static final String USU_ALTA_73 = "USUALTA73";
	private static final String F_MODIF_73 = "FMODIF73";
	private static final String USU_MODIF_73 = "USUMODIF73";
	private static final String NUMBENEF = "NUMBENEF";
	private static final String NUMDIAS = "NUMDIAS";
	private static final String TARIFA = "TARIFA";
	private static final String NUMTARIFAS = "NUMTARIFAS";
	private static final String TOTAL = "TOTAL";

	protected static final String[] ORDER_BY_WHITE_LIST = new String[] { ANYO_PAGO_74, MES_PAGO_74, ID_PRESTADOR_74,
			F_ALTA_74, USU_ALTA_74, F_MODIF_74, USU_MODIF_74, NUMBENEF, NUMDIAS, TARIFA, TOTAL, RAZON_SOCIAL_73 };

	private JdbcTemplate jdbcTemplate;

	public static final String SEQ_PAGOS = "AD74A74T00_SEQ";
	/*
	 * ROW_MAPPERS
	 */
	private RowMapper<Pagos> rwMap = new RowMapper<Pagos>() {
		public Pagos mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			Pagos pagos = new Pagos(UtilSQL.getLong(resultSet, ID_PAGO_74));
			pagos.setAnyoPago74(UtilSQL.getLong(resultSet, ANYO_PAGO_74));
			pagos.setMesPago74(resultSet.getString(MES_PAGO_74));
			pagos.setfAlta74(resultSet.getDate(F_ALTA_74));
			pagos.setUsuAlta74(resultSet.getString(USU_ALTA_74));
			pagos.setfModif74(resultSet.getDate(F_MODIF_74));
			pagos.setUsuModif74(resultSet.getString(USU_MODIF_74));
			pagos.setNumBenef(UtilSQL.getLong(resultSet, NUMBENEF));
			pagos.setNumDias(UtilSQL.getLong(resultSet, NUMDIAS));
			pagos.setTarifa(resultSet.getDouble(TARIFA));
			pagos.setNumTarifas(UtilSQL.getLong(resultSet, NUMTARIFAS));
			pagos.setTotal(resultSet.getDouble(TOTAL));

			PrestadorServicio prestadorServicio = new PrestadorServicio(UtilSQL.getLong(resultSet, ID_PRESTADOR_73),
					resultSet.getString(IDENTIFICADOR_73), resultSet.getString(RAZON_SOCIAL_73),
					resultSet.getDate(F_INICIO_SERVICIO_73), resultSet.getDate(F_FIN_SERVICIO_73),
					resultSet.getDate(F_ALTA_73), resultSet.getString(USU_ALTA_73), resultSet.getDate(F_MODIF_73),
					resultSet.getString(USU_MODIF_73));

			pagos.setPrestadorServicio(prestadorServicio);

			return pagos;
		}
	};
	
	private RowMapper<Pagos> rwMapUltimaFecha = new RowMapper<Pagos>() {
		public Pagos mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			Pagos pagos = new Pagos();
			pagos.setAnyoPago74(UtilSQL.getLong(resultSet, ANYO_PAGO_74));
			pagos.setMesPago74(resultSet.getString(MES_PAGO_74));

			return pagos;
		}
	};

	/**
	 * Method use to set the datasource.
	 *
	 * @param dataSource DataSource
	 * @return
	 */
	@Resource
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	/*
	 * OPERACIONES CRUD
	 */

	/**
	 * Inserts a single row in the Pagos table.
	 *
	 * @param pagos Pagos
	 * @return Pagos
	 */
	public Pagos add(Pagos pagos) {

		GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();

		String query = "INSERT INTO PAGOS (ID_PAGO_74, ANYO_PAGO_74, MES_PAGO_74, ID_PRESTADOR_74, F_ALTA_74, USU_ALTA_74, F_MODIF_74, USU_MODIF_74) "
				+ "VALUES (" + SEQ_PAGOS
				+ ".NEXTVAL,:anyoPago74,:mesPago74,:idPrestador74,:fAlta74,:usuAlta74,:fModif74,:usuModif74)";

		Object getPrestadorIdPrestador = null;
		if (pagos.getPrestadorServicio() != null && pagos.getPrestadorServicio().getIdPrestador73() != null) {
			getPrestadorIdPrestador = pagos.getPrestadorServicio().getIdPrestador73();
		}

		MapSqlParameterSource parameters = new MapSqlParameterSource().addValue("anyoPago74", pagos.getAnyoPago74())
				.addValue("mesPago74", pagos.getMesPago74()).addValue("idPrestador74", getPrestadorIdPrestador)
				.addValue("fAlta74", pagos.getfAlta74()).addValue("usuAlta74", pagos.getUsuAlta74())
				.addValue("fModif74", pagos.getfModif74()).addValue("usuModif74", pagos.getUsuModif74());

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
		namedParameterJdbcTemplate.update(query, parameters, generatedKeyHolder, new String[] { "ID_PAGO_74" });
		pagos.setIdPago74(generatedKeyHolder.getKey().longValue());

		return pagos;
	}

	/**
	 * Updates a single row in the Pagos table.
	 *
	 * @param pagos Pagos
	 * @return Pagos
	 */
	public Pagos update(Pagos pagos) {
		String query = "UPDATE PAGOS " + "SET ANYO_PAGO_74=?, " + "MES_PAGO_74=?, " + "ID_PRESTADOR_74=?, "
				+ "F_MODIF_74=?, " + "USU_MODIF_74=? " + "WHERE ID_PAGO_74=?";

		Object getPrestadorIdPrestador = null;
		if (pagos.getPrestadorServicio() != null && pagos.getPrestadorServicio().getIdPrestador73() != null) {
			getPrestadorIdPrestador = pagos.getPrestadorServicio().getIdPrestador73();
		}

		this.jdbcTemplate.update(query, pagos.getAnyoPago74(), pagos.getMesPago74(), getPrestadorIdPrestador,
				pagos.getfModif74(), pagos.getUsuModif74(), pagos.getIdPago74());
		return pagos;
	}

	/**
	 * Finds a single row in the Pagos table.
	 *
	 * @param pagos Pagos
	 * @return Pagos
	 */
	@Transactional(readOnly = true)
	public Pagos find(Pagos pagos) {
		StringBuilder query = new StringBuilder("SELECT " + " T1.ID_PAGO_74 IDPAGO74," + " T1.ANYO_PAGO_74 ANYOPAGO74,"
				+ " T1.MES_PAGO_74 MESPAGO74," + " T1.ID_PRESTADOR_74 IDPRESTADOR74," + " T1.F_ALTA_74 FALTA74,"
				+ " T1.USU_ALTA_74 USUALTA74," + " T1.F_MODIF_74 FMODIF74," + " T1.USU_MODIF_74 USUMODIF74,"
				+ " T2.ID_PRESTADOR_73 IDPRESTADOR73," + " T2.IDENTIFICADOR_73 IDENTIFICADOR73,"
				+ " T2.RAZON_SOCIAL_73 RAZONSOCIAL73," + " T2.F_INICIO_SERVICIO_73 FINICIOSERVICIO73,"
				+ " T2.F_FIN_SERVICIO_73 FFINSERVICIO73," + " T2.F_ALTA_73 FALTA73," + " T2.USU_ALTA_73 USUALTA73,"
				+ " T2.F_MODIF_73 FMODIF73," + " T2.USU_MODIF_73 USUMODIF73, "
				+ " (SELECT COUNT(DISTINCT ID_SOLICITANTE_75) FROM PAGOS_BENEFICIARIO T3 WHERE T3.ID_PAGO_75 = T1.ID_PAGO_74) NUMBENEF, "
				+ " (SELECT SUM(T3.NUM_DIAS_75) FROM PAGOS_BENEFICIARIO T3 WHERE T3.ID_PAGO_75 = T1.ID_PAGO_74) NUMDIAS, "
				+ " (SELECT T2.TARIFA_75 " + "FROM (SELECT T1.ID_PAGO_75," + " T1.TARIFA_75 "
				+ "FROM PAGOS_BENEFICIARIO T1) T2 " + "WHERE T2.ID_PAGO_75 = T1.ID_PAGO_74 "
				+ "GROUP BY T2.TARIFA_75 FETCH FIRST 1 ROWS ONLY) TARIFA, "
				+ " (SELECT COUNT(DISTINCT TARIFA_75) FROM PAGOS_BENEFICIARIO T3 WHERE T3.ID_PAGO_75 = T1.ID_PAGO_74) NUMTARIFAS, "
				+

				" (SELECT SUM(TOTALES) AS TOTAL " + " FROM (SELECT T3.TARIFA_75*T3.NUM_DIAS_75 AS TOTALES "
				+ " FROM (SELECT T1.ID_PAGO_BENEFICIARIO_75, " + " T1.NUM_DIAS_75, " + " T1.ID_PAGO_75, "
				+ " T1.TARIFA_75 " + " FROM PAGOS_BENEFICIARIO T1 ) T3 "
				+ " WHERE T3.ID_PAGO_75 = T1.ID_PAGO_74)) TOTAL ");
		query.append(FROM_PAGOS_T1);
		query.append(PRESTADOR_SERVICIO_T2);
		query.append(WHERE_1_1);
		query.append(" AND t1.ID_PRESTADOR_74 = t2.ID_PRESTADOR_73 ");
		query.append(" AND t1.ID_PAGO_74 = ?");

		List<Pagos> pagosList = this.jdbcTemplate.query(query.toString(), this.rwMap, pagos.getIdPago74());
		return DataAccessUtils.uniqueResult(pagosList);
	}

	/**
	 * Removes a single row in the Pagos table.
	 *
	 * @param pagos Pagos
	 * @return
	 */
	public void remove(Pagos pagos) {
		String query = "DELETE FROM PAGOS " + "WHERE ID_PAGO_74=?";
		this.jdbcTemplate.update(query, pagos.getIdPago74());
	}

	/**
	 * Finds rows in the Pagos table using like.
	 *
	 * @param pagos           Pagos
	 * @param tableRequestDto TableRequestDto
	 * @param startsWith      Boolean
	 * @return List<Pagos>
	 */
	@Transactional(readOnly = true)
	public List<Pagos> findAllLike(Pagos pagos, TableRequestDto tableRequestDto, Boolean startsWith) {
		StringBuilder query = new StringBuilder("SELECT " + " T1.ID_PAGO_74 IDPAGO74," + " T1.ANYO_PAGO_74 ANYOPAGO74,"
				+ " T1.MES_PAGO_74 MESPAGO74," + " T1.ID_PRESTADOR_74 IDPRESTADOR74," + " T1.F_ALTA_74 FALTA74,"
				+ " T1.USU_ALTA_74 USUALTA74," + " T1.F_MODIF_74 FMODIF74," + " T1.USU_MODIF_74 USUMODIF74,"
				+ " T2.ID_PRESTADOR_73 IDPRESTADOR73," + " T2.IDENTIFICADOR_73 IDENTIFICADOR73,"
				+ " T2.RAZON_SOCIAL_73 RAZONSOCIAL73," + " T2.F_INICIO_SERVICIO_73 FINICIOSERVICIO73,"
				+ " T2.F_FIN_SERVICIO_73 FFINSERVICIO73," + " T2.F_ALTA_73 FALTA73," + " T2.USU_ALTA_73 USUALTA73,"
				+ " T2.F_MODIF_73 FMODIF73," + " T2.USU_MODIF_73 USUMODIF73, "
				+ " (SELECT COUNT(DISTINCT ID_SOLICITANTE_75) FROM PAGOS_BENEFICIARIO T3 WHERE T3.ID_PAGO_75 = T1.ID_PAGO_74) NUMBENEF, "
				+ " (SELECT SUM(T3.NUM_DIAS_75) FROM PAGOS_BENEFICIARIO T3 WHERE T3.ID_PAGO_75 = T1.ID_PAGO_74) NUMDIAS, "
				+ " (SELECT T2.TARIFA_75 " + "FROM (SELECT T1.ID_PAGO_75," + " T1.TARIFA_75 "
				+ "FROM PAGOS_BENEFICIARIO T1 ) T2 " + "WHERE T2.ID_PAGO_75 = T1.ID_PAGO_74 "
				+ "GROUP BY T2.TARIFA_75 FETCH FIRST 1 ROWS ONLY) TARIFA, "
				+ " (SELECT COUNT(DISTINCT TARIFA_76) FROM PAGOS_BENEFICIARIO T3 "
				+ " LEFT JOIN TARIFAS t4 ON (t3.ID_NIVEL_SOPORTE_75 = t4.ID_NIVEL_SOPORTE_76) "
				+ " WHERE (t4.F_VIGENCIA_HASTA_76 >= TO_DATE(TO_CHAR('01' || '/' || t1.mes_pago_74 || '/' || t1.anyo_pago_74), 'dd/mm/yyyy') OR t4.F_VIGENCIA_HASTA_76 IS NULL) "
				+ " AND t4.F_VIGENCIA_DESDE_76 <= TO_DATE(TO_CHAR(TO_CHAR(LAST_DAY(TO_DATE(t1.mes_pago_74 || '/01/' || t1.anyo_pago_74, 'MM/DD/YYYY')), 'dd') || '/' || t1.mes_pago_74 || '/' || t1.anyo_pago_74), 'dd/mm/yyyy') "
				+ " AND t3.id_pago_75 = t1.id_pago_74 ) numtarifas," + " (SELECT SUM(TOTALES) AS TOTAL "
				+ " FROM (SELECT T3.TARIFA_75*T3.NUM_DIAS_75 AS TOTALES " + " FROM (SELECT T1.ID_PAGO_BENEFICIARIO_75, "
				+ " T1.NUM_DIAS_75, " + " T1.ID_PAGO_75, " + " T1.TARIFA_75 " + " FROM PAGOS_BENEFICIARIO T1 ) T3 "
				+ " WHERE T3.ID_PAGO_75 = T1.ID_PAGO_74)) TOTAL ");

		query.append(FROM_PAGOS_T1);
		query.append(PRESTADOR_SERVICIO_T2);

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(pagos, startsWith);
		StringBuilder where = new StringBuilder(" WHERE t1.ID_PRESTADOR_74 = t2.ID_PRESTADOR_73 ");
		where.append(mapaWhere.get("query"));
		query.append(where);

		List<?> params = (List<?>) mapaWhere.get("params");

		if (tableRequestDto != null) {
			query = TableManager.getPaginationQuery(tableRequestDto, query, PagosDaoImpl.ORDER_BY_WHITE_LIST);
		}

		return (List<Pagos>) this.jdbcTemplate.query(query.toString(), this.rwMap, params.toArray());
	}

	/*
	 * OPERACIONES RUP_TABLE
	 */

	/**
	 * Counts rows in the Pagos table using like.
	 *
	 * @param pagos      Pagos
	 * @param startsWith Boolean
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllLikeCount(Pagos pagos, Boolean startsWith) {
		StringBuilder query = new StringBuilder("SELECT COUNT(1) FROM PAGOS t1, PRESTADOR_SERVICIO t2 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(pagos, startsWith);
		StringBuilder where = new StringBuilder(WHERE_1_1);
		where.append(" AND t1.ID_PRESTADOR_74 = t2.ID_PRESTADOR_73 ");
		where.append(mapaWhere.get("query"));
		query.append(where);

		List<?> params = (List<?>) mapaWhere.get("params");

		return this.jdbcTemplate.queryForObject(query.toString(), params.toArray(), Long.class);
	}
	
	/**
	 * Obtiene el ultimo a�o y mes de pagos.
	 * 
	 * @return Pagos
	 */
	@Transactional(readOnly = true)
	public Pagos obtenerUltimaFechaPago() {
		StringBuilder query = new StringBuilder(
				"SELECT anyo_pago_74 anyopago74, mes_pago_74 mespago74 FROM pagos ORDER BY anyopago74 DESC ,mes_pago_74 desc FETCH FIRST ROW ONLY");

		List<Pagos> pagosList = this.jdbcTemplate.query(query.toString(), this.rwMapUltimaFecha);
		return DataAccessUtils.uniqueResult(pagosList);
	}

	/**
	 * Returns a map with the needed value to create the conditions to filter by the
	 * Pagos entity
	 *
	 * @param pagos      Pagos Bean with the criteria values to filter by.
	 * @param startsWith Boolean
	 * @return Map created with two keys key query stores the sql query syntax key
	 *         params stores the parameter values to be used in the condition
	 *         sentence.
	 */
	// CHECKSTYLE:OFF CyclomaticComplexity - Generación de código de UDA
	private Map<String, Object> getWhereLikeMap(Pagos pagos, Boolean startsWith) {

		StringBuilder where = new StringBuilder(PagosDaoImpl.STRING_BUILDER_INIT);
		List<Object> params = new ArrayList<>();

		if (pagos != null && pagos.getIdPago74() != null) {
			where.append(" AND t1.ID_PAGO_74 = ?");
			params.add(pagos.getIdPago74());
		}
		if (pagos != null && pagos.getAnyoPago74() != null) {
			where.append(" AND t1.ANYO_PAGO_74 = ?");
			params.add(pagos.getAnyoPago74());
		}
		if (pagos != null && pagos.getMesPago74() != null) {
			where.append(" AND t1.MES_PAGO_74 = ?");
			params.add(pagos.getMesPago74());
		}
		if (pagos != null && pagos.getPrestadorServicio() != null
				&& pagos.getPrestadorServicio().getIdPrestador73() != null) {
			where.append(" AND t1.ID_PRESTADOR_74 = ?");
			params.add(pagos.getPrestadorServicio().getIdPrestador73());
		}
		if (pagos != null && pagos.getPrestadorServicio() != null
				&& pagos.getPrestadorServicio().getRazonSocial73() != null) {
			where.append(" AND UPPER(t2.RAZON_SOCIAL_73) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(pagos.getPrestadorServicio().getRazonSocial73().toUpperCase() + "%");
			} else {
				params.add("%" + pagos.getPrestadorServicio().getRazonSocial73().toUpperCase() + "%");
			}
			where.append(" AND t2.RAZON_SOCIAL_73 IS NOT NULL");
		}
		if (pagos != null && pagos.getfAlta74() != null) {
			where.append(" AND t1.F_ALTA_74 = ?");
			params.add(pagos.getfAlta74());
		}
		if (pagos != null && pagos.getUsuAlta74() != null) {
			where.append(" AND UPPER(t1.USU_ALTA_74) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(pagos.getUsuAlta74().toUpperCase() + "%");
			} else {
				params.add("%" + pagos.getUsuAlta74().toUpperCase() + "%");
			}
			where.append(" AND t1.USU_ALTA_74 IS NOT NULL");
		}
		if (pagos != null && pagos.getfModif74() != null) {
			where.append(" AND t1.F_MODIF_74 = ?");
			params.add(pagos.getfModif74());
		}
		if (pagos != null && pagos.getUsuModif74() != null) {
			where.append(" AND UPPER(t1.USU_MODIF_74) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(pagos.getUsuModif74().toUpperCase() + "%");
			} else {
				params.add("%" + pagos.getUsuModif74().toUpperCase() + "%");
			}
			where.append(" AND t1.USU_MODIF_74 IS NOT NULL");
		}

		Map<String, Object> mapWhere = new HashMap<String, Object>();
		mapWhere.put("query", where);
		mapWhere.put("params", params);

		return mapWhere;
	}

	/**
	 * StringBuilder initilization value
	 */
	public static final int STRING_BUILDER_INIT = 4096;
}
