package com.ejie.ad74a.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
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.model.Pagos;
import com.ejie.ad74a.model.PrestadorServicio;
import com.ejie.ad74a.util.UtilSQL;
import com.ejie.x38.dao.RowNumResultSetExtractor;
import com.ejie.x38.dto.JerarquiaDto;
import com.ejie.x38.dto.TableManager;
import com.ejie.x38.dto.TableManagerJerarquia;
import com.ejie.x38.dto.TableRequestDto;
import com.ejie.x38.dto.TableRowDto;
import com.ejie.x38.json.JSONObject;

/**
 * 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> rwMapPK = new RowMapper<Pagos>() {
		public Pagos mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			return new Pagos(UtilSQL.getLong(resultSet, ID_PAGO_74));
		}
	};

	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;
		}
	};

	private RowMapper<JSONObject> rwMapPagosTerritorio = new RowMapper<JSONObject>() {
		public JSONObject mapRow(ResultSet resultSet, int rowNum) throws SQLException {

			JSONObject rowObj = new JSONObject();
			rowObj.put("idProvincia", UtilSQL.getLong(resultSet, "IDPROVINCIA"));
			rowObj.put("provinciaDescE", resultSet.getString("DESCPROVINCIAE"));
			rowObj.put("provinciaDescC", resultSet.getString("DESCPROVINCIAC"));
			rowObj.put("totalBeneficiarios", resultSet.getString("TOTALBENEFICIARIOS"));
			rowObj.put("totalDias", resultSet.getString("TOTALDIAS"));
			rowObj.put("totalImporte", resultSet.getString("TOTALIMPORTE"));

			return rowObj;
		}
	};

	/**
	 * Rowmapper para Jerarquía
	 *
	 * @param dataSource DataSource
	 * @return
	 */
	private RowMapper<JerarquiaDto<Pagos>> rwMapJerarquia = new RowMapper<JerarquiaDto<Pagos>>() {
		public JerarquiaDto<Pagos> mapRow(ResultSet resultSet, int rowNum) throws SQLException {

			Pagos pagos = new Pagos(UtilSQL.getLong(resultSet, ID_PAGO_74), UtilSQL.getLong(resultSet, ANYO_PAGO_74),
					resultSet.getString(MES_PAGO_74),
					new PrestadorServicio(UtilSQL.getLong(resultSet, ID_PRESTADOR_74)), resultSet.getDate(F_ALTA_74),
					resultSet.getString(USU_ALTA_74), resultSet.getDate(F_MODIF_74), resultSet.getString(USU_MODIF_74),
					UtilSQL.getLong(resultSet, NUMBENEF), UtilSQL.getLong(resultSet, NUMDIAS),
					resultSet.getDouble(TARIFA), UtilSQL.getLong(resultSet, NUMTARIFAS), resultSet.getDouble(TOTAL));

			JerarquiaDto<Pagos> jerarquia = new JerarquiaDto<>();
			jerarquia.setModel(pagos);
			jerarquia.setLevel(resultSet.getBigDecimal("LEVEL").intValue());
			jerarquia.setParentNodes(resultSet.getString("PARENTNODES"));
			jerarquia.setIsLeaf(Boolean.parseBoolean(resultSet.getString("ISLEAF")));
			jerarquia.setFilter(Boolean.parseBoolean(resultSet.getString("FILTER")));
			return jerarquia;
		}
	};

	/**
	 * 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());
	}

	/**
	 * Exporta Datos al clipBoard
	 *
	 */
	public List<Pagos> getMultiple(Pagos filterPagos, 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_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);

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

		@SuppressWarnings("unchecked")
		List<Object> params = (List<Object>) mapaWhere.get("params");

		StringBuilder sbMultipleSQL = TableManager.getSelectMultipleQuery(tableRequestDto, Pagos.class, params,
				"ID_PAGO_74");
		where.append(sbMultipleSQL);

		return this.jdbcTemplate.query(where.toString(), this.rwMap, params.toArray());
	}

	/**
	 * Finds a list of rows in the Pagos table.
	 *
	 * @param pagos           Pagos
	 * @param tableRequestDto TableRequestDto
	 * @return List<Pagos>
	 */
	@Transactional(readOnly = true)
	public List<Pagos> findAll(Pagos pagos, TableRequestDto tableRequestDto) {
		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);

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(pagos);
		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());
	}

	/**
	 * 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);
	}

	/**
	 * 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_75)  from pagos_beneficiario "
				+ " where 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());
	}

	/**
	 * Obtener las tarifas por territorio.
	 * 
	 * @param pagos
	 * @return List<JSONObject>
	 */
	@Transactional(readOnly = true)
	public List<JSONObject> findTarifasPorTerritorio(Pagos pagos) {
		StringBuilder query = new StringBuilder("SELECT DISTINCT ");
		query.append("t4.id_provincia_06 AS IDPROVINCIA, ");
		query.append("t5.ds_e AS DESCPROVINCIAE, ");
		query.append("t5.ds_c AS DESCPROVINCIAC, ");
		query.append(
				"COUNT(DISTINCT t3.id_solicitante_01) OVER (PARTITION BY t4.id_provincia_06) AS TOTALBENEFICIARIOS, ");
		query.append("SUM(t2.num_dias_75) OVER (PARTITION BY t4.id_provincia_06) AS TOTALDIAS, ");
		query.append("SUM(t2.tarifa_75 * t2.num_dias_75) OVER (PARTITION BY t4.id_provincia_06) AS TOTALIMPORTE ");
		query.append("FROM pagos t1 ");
		query.append("LEFT JOIN pagos_beneficiario t2 ON t1.id_pago_74 = t2.id_pago_75 ");
		query.append("LEFT JOIN solicitantes t3 ON t2.id_solicitante_75 = t3.id_solicitante_01 ");
		query.append("LEFT JOIN direcciones t4 ON t3.id_direccion_01 = t4.id_direccion_06 ");
		query.append("INNER JOIN t17_provincia t5 ON t5.id = t4.id_provincia_06 ");
		query.append("WHERE t1.id_pago_74 = ? ");
		query.append("ORDER BY t4.id_provincia_06");

		return (List<JSONObject>) this.jdbcTemplate.query(query.toString(), this.rwMapPagosTerritorio,
				pagos.getIdPago74());
	}

	/**
	 * Obtener las tarifas diarias.
	 * 
	 * @param pagos
	 * @return List<Pagos>
	 */
	@Transactional(readOnly = true)
	public List<JSONObject> findTarifasDiarias(Pagos pagos) {
		// TODO
		return new ArrayList<>();
	}

	/*
	 * OPERACIONES RUP_TABLE
	 */

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

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(pagos);
		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);
	}

	/**
	 * 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);
	}

	/**
	 * Reorder the data list of Pagos selected for rup_table
	 *
	 * @param pagos           Pagos
	 * @param tableRequestDto TableRequestDto
	 * @param startsWith      Boolean
	 * @return List<TableRowDto<Pagos>>
	 */
	@Override
	public List<TableRowDto<Pagos>> reorderSelection(Pagos pagos, TableRequestDto tableRequestDto, Boolean startsWith) {
		// SELECT
		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 ");
		// FROM
		query.append(FROM_PAGOS_T1);
		query.append(PRESTADOR_SERVICIO_T2);
		// FILTRADO
		// 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);

		// Parámetros de filtrado
		@SuppressWarnings("unchecked")
		List<Object> filterParamList = (List<Object>) mapaWhere.get("params");

		// SQL para la reordenación
		StringBuilder sbReorderSelectionSQL = TableManager.getReorderQuery(query, tableRequestDto, Pagos.class,
				filterParamList, ID_PAGO_74);

		return this.jdbcTemplate.query(sbReorderSelectionSQL.toString(),
				new RowNumResultSetExtractor<Pagos>(this.rwMapPK, tableRequestDto), filterParamList.toArray());
	}

	/**
	 * Search method for rup_table
	 *
	 * @param filterParams    Pagos
	 * @param searchParams    Pagos
	 * @param tableRequestDto TableRequestDto
	 * @param startsWith      Boolean
	 * @return List<TableRowDto<Pagos>>
	 */
	@Override
	public List<TableRowDto<Pagos>> search(Pagos filterParams, Pagos searchParams, TableRequestDto tableRequestDto,
			Boolean startsWith) {
		// SELECT
		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 ");
		// FROM
		query.append(FROM_PAGOS_T1);
		// TABLAS_ALIAS
		List<String> fromAlias = new ArrayList<>();
		fromAlias.add(" t1 ");

		// FILTRADO
		Map<String, ?> mapaWhereFilter = this.getWhereLikeMap(filterParams, startsWith);
		StringBuilder where = new StringBuilder(WHERE_1_1);
		where.append(mapaWhereFilter.get("query"));
		query.append(where);
		// Filter params
		@SuppressWarnings("unchecked")
		List<Object> filterParamList = (List<Object>) mapaWhereFilter.get("params");

		// BUSQUEDA
		Map<String, Object> mapaWhereSearch = this.getWhereLikeMap(searchParams, startsWith);
		String searchSQL = ((StringBuilder) mapaWhereSearch.get("query")).toString();
		// Search params
		@SuppressWarnings("unchecked")
		List<Object> searchParamList = (List<Object>) mapaWhereSearch.get("params");

		// SQL
		StringBuilder sbReorderSelectionSQL = TableManager.getSearchQuery(query, tableRequestDto, Pagos.class,
				filterParamList, searchSQL, searchParamList, fromAlias, ID_PAGO_74);

		return this.jdbcTemplate.query(sbReorderSelectionSQL.toString(),
				new RowNumResultSetExtractor<Pagos>(this.rwMapPK, tableRequestDto), filterParamList.toArray());
	}

	/**
	 * Remove multiple method for rup_table
	 *
	 * @param tableRequestDto TableRequestDto
	 */
	@Override
	public void removeMultiple(TableRequestDto tableRequestDto) {
		StringBuilder sbRemoveMultipleSQL = TableManager.getRemoveMultipleQuery(tableRequestDto, Pagos.class, "PAGOS",
				new String[] { ID_PAGO_74 });

		List<String> params = tableRequestDto.getMultiselection().getSelectedIds();

		this.jdbcTemplate.update(sbRemoveMultipleSQL.toString(), params.toArray());
	}

	/**
	 * Filter in the hierarchical Pagos table.
	 *
	 * @param filterPagos     Pagos
	 * @param tableRequestDto TableRequestDto
	 * @return List<JerarquiaDto<Pagos>>
	 */
	@Override
	public List<JerarquiaDto<Pagos>> findAllLikeJerarquia(Pagos filterPagos, TableRequestDto tableRequestDto) {
		// SELECT
		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 ");
		// FROM
		List<String> from = new ArrayList<>();
		from.add("PAGOS");
		// TABLAS_ALIAS
		List<String> fromAlias = new ArrayList<>();
		fromAlias.add(" t1 ");

		// FILTRO
		Map<String, ?> mapaWhere = this.getWhereLikeMap(filterPagos, false);

		// JERARQUIA
		// Especificar la columna padre correspondiente en lugar de la generada por
		// defecto (ID_PADRE)
		query = TableManagerJerarquia.getQuery(tableRequestDto, query, mapaWhere, ID_PAGO_74, "ID_PADRE", "NOMBRE",
				from, fromAlias);

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

		List<?> params = (List<?>) mapaWhere.get("params");
		return this.jdbcTemplate.query(query.toString(), this.rwMapJerarquia, params.toArray());
	}

	/**
	 * Count rows in hierarchy
	 *
	 * @param filterPagos     Pagos
	 * @param tableRequestDto TableRequestDto
	 * @return Long
	 */
	@Override
	public Long findAllLikeCountJerarquia(Pagos filterPagos, TableRequestDto tableRequestDto) {
		// FROM
		List<String> from = new ArrayList<>();
		from.add("PAGOS");

		// TABLAS_ALIAS
		List<String> fromAlias = new ArrayList<>();
		fromAlias.add(" t1 ");

		// FILTRO
		Map<String, ?> mapaWhere = this.getWhereLikeMap(filterPagos, false);

		// JERARQUIA
		StringBuilder query = TableManagerJerarquia.getQueryCount(tableRequestDto, mapaWhere, ID_PAGO_74, "ID_PADRE",
				from, fromAlias);

		List<?> params = (List<?>) mapaWhere.get("params");
		return this.jdbcTemplate.queryForObject(query.toString(), params.toArray(), Long.class);
	}

	/**
	 * Find dependent hierarchical rows
	 *
	 * @param filterPagos     Pagos
	 * @param tableRequestDto TableRequestDto
	 * @return List<TableRowDto<Pagos>>
	 */
	@Override
	public List<TableRowDto<Pagos>> findAllChild(Pagos filterPagos, TableRequestDto tableRequestDto) {

		// FROM
		List<String> from = new ArrayList<>();
		from.add("PAGOS");

		// TABLAS_ALIAS
		List<String> fromAlias = new ArrayList<>();
		fromAlias.add(" t1 ");

		// FILTRO
		Map<String, ?> mapaWhere = this.getWhereLikeMap(filterPagos, false);

		// MULTISELECCION
		// Especificar la columna padre correspondiente en lugar de la generada por
		// defecto (ID_PADRE)
		StringBuilder query = TableManagerJerarquia.getQueryChildren(tableRequestDto, mapaWhere, ID_PAGO_74, "ID_PADRE",
				from, fromAlias);

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

		return this.jdbcTemplate.query(query.toString(), new RowNumResultSetExtractor<Pagos>(this.rwMapPK, ID_PAGO_74),
				params.toArray());
	}

	/*
	 * MÉTODOS PRIVADOS
	 */

	/**
	 * 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.
	 * @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, ?> getWhereMap(Pagos pagos) {

		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 t2.RAZON_SOCIAL_73 = ?");
			params.add(pagos.getPrestadorServicio().getRazonSocial73());
		}
		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 t1.USU_ALTA_74 = ?");
			params.add(pagos.getUsuAlta74());
		}
		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 t1.USU_MODIF_74 = ?");
			params.add(pagos.getUsuModif74());
		}

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

		return mapWhere;
	}
	// CHECKSTYLE:ON CyclomaticComplexity - Generación de código de UDA

	/**
	 * 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;
	}

	// CHECKSTYLE:ON CyclomaticComplexity - Generación de código de UDA
	/**
	 * Get all anyos.
	 *
	 * @return List<Long>
	 */
	@Transactional(readOnly = true)
	public List<Long> findAllAnyos() {
		StringBuilder query = new StringBuilder("SELECT DISTINCT t1.ANYO_PAGO_74 FROM PAGOS t1 ");

		List<?> params = new ArrayList<>();

		return this.jdbcTemplate.queryForList(query.toString(), params.toArray(), Long.class);
	}

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