package com.ejie.ad74a.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
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.apache.commons.lang.StringUtils;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
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.Expedientes;
import com.ejie.ad74a.model.MaestroTareas;
import com.ejie.ad74a.model.MaestroTramites;
import com.ejie.ad74a.model.NotifPostales;
import com.ejie.ad74a.model.Solicitantes;
import com.ejie.ad74a.model.TareasTramite;
import com.ejie.ad74a.model.TramitesExpediente;
import com.ejie.x38.dao.RowNumResultSetExtractor;
import com.ejie.x38.dto.TableManager;
import com.ejie.x38.dto.TableRequestDto;
import com.ejie.x38.dto.TableRowDto;

/**
 * NotifPostalesDaoImpl generated by UDA, 09-ene-2024 16:08:21.
 * 
 * @author UDA
 */

@Repository
@Transactional
public class NotifPostalesDaoImpl implements NotifPostalesDao {

	private static final String QUERY_WHERE = "query";
	private static final String PARAMS_WHERE = "params";

	protected static final String[] ORDER_BY_WHITE_LIST = new String[] { "IDNOTIFPOSTAL88", "TIPONOT88",
			"IDTAREATRAM88", "IDENVIOPOSTAL88", "IDCOLECCIONPOSTAL88", "POSTALINBOXID88", "CODSITUACIONPOSTAL88",
			"FECHASITUACION88", "FINALIZADA88", "SITUACIONPOSTALES88", "SITUACIONPOSTALEU88", "FREGISTRO11",
			"NUMEXPEDIENTE00", "DESCES37", "DESCEU37", "IDENTIFICADOR01", "NOMBRE01", "APELLIDO101", "APELLIDO201",
			"RETRAMITAR88" };

	private JdbcTemplate jdbcTemplate;

	public static final String SEQUENCE_NOTIFPOSTALES = "SEQ_NOTIF_POSTALES";
	private static final String SELECT_FROM = "SELECT t1.ID_NOTIF_POSTAL_88 IDNOTIFPOSTAL88, t1.TIPO_NOT_88 TIPONOT88, t1.ID_TAREA_TRAM_88 IDTAREATRAM88, "
			+ "t1.ID_ENVIO_POSTAL_88 IDENVIOPOSTAL88, t1.ID_COLECCION_POSTAL_88 IDCOLECCIONPOSTAL88, "
			+ "t1.POSTAL_INBOX_ID_88 POSTALINBOXID88, t1.COD_SITUACION_POSTAL_88 CODSITUACIONPOSTAL88, "
			+ "t1.FECHA_SITUACION_88 FECHASITUACION88, t1.FINALIZADA_88 FINALIZADA88, "
			+ "t1.SITUACION_POSTAL_ES_88 SITUACIONPOSTALES88, t1.SITUACION_POSTAL_EU_88 SITUACIONPOSTALEU88, t1.RETRAMITAR_88 RETRAMITAR88,"
			+ "t2.F_REGISTRO_11 FREGISTRO11, t2.NUM_REGISTRO_11 NUMREGISTRO11, t2.ID_NOTIFICACION_11 IDNOTIFICACION11, t2.TASKGUID_11 TASKGUID11, "
			+ "t3.ID_TRAMITE_EXP_10 IDTRAMITEEXP10, t4.ID_EXPEDIENTE_00 IDEXPEDIENTE00, t4.FOLDER_ID_00 FOLDERID00, t4.NUM_EXPEDIENTE_00 NUMEXPEDIENTE00, "
			+ "t5.DESC_ES_37 DESCES37, t5.DESC_EU_37 DESCEU37, t6.ID_SOLICITANTE_01 IDSOLICITANTE01, t6.IDENTIFICADOR_01 IDENTIFICADOR01, t6.NOMBRE_01 NOMBRE01, "
			+ "t6.APELLIDO1_01 APELLIDO101, t6.APELLIDO2_01 APELLIDO201, "
			+ "t7.TIPO_TAREA_38 TIPOTAREA38, t7.DESC_ES_38 DESCES38, t7.DESC_EU_38 DESCEU38 "
			+ "FROM NOTIF_POSTALES t1, TAREAS_TRAMITE t2, TRAMITES_EXPEDIENTE t3, EXPEDIENTES t4, MAESTRO_TRAMITES t5, SOLICITANTES t6, MAESTRO_TAREAS t7 ";

	/*
	 * ROW_MAPPERS
	 */
	private RowMapper<NotifPostales> rwMap = new RowMapper<NotifPostales>() {
		public NotifPostales mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			// Datos del solicitante
			Solicitantes solicitantes = new Solicitantes(resultSet.getLong("IDSOLICITANTE01"));
			solicitantes.setIdentificador01(resultSet.getString("IDENTIFICADOR01"));
			solicitantes.setNombre01(resultSet.getString("NOMBRE01"));
			solicitantes.setApellido101(resultSet.getString("APELLIDO101"));
			solicitantes.setApellido201(resultSet.getString("APELLIDO201"));
			// Datos de expediente
			Expedientes expediente = new Expedientes(resultSet.getLong("IDEXPEDIENTE00"));
			expediente.setSolicitantes(solicitantes);
			expediente.setFolderId00(resultSet.getString("FOLDERID00"));
			expediente.setNumExpediente00(resultSet.getString("NUMEXPEDIENTE00"));
			// Datos de tramite
			MaestroTramites maestroTramite = new MaestroTramites(null, resultSet.getString("DESCES37"),
					resultSet.getString("DESCEU37"));
			TramitesExpediente tramitesExpediente = new TramitesExpediente(resultSet.getLong("IDTRAMITEEXP10"));
			tramitesExpediente.setExpediente(expediente);
			tramitesExpediente.setMaestroTramites(maestroTramite);
			// Datos de tarea
			MaestroTareas maestroTareas = new MaestroTareas();
			maestroTareas.setTipoTarea38(resultSet.getString("TIPOTAREA38"));
			maestroTareas.setDescEs38(resultSet.getString("DESCES38"));
			maestroTareas.setDescEu38(resultSet.getString("DESCEU38"));
			TareasTramite tareasTramite = new TareasTramite(resultSet.getLong("IDTAREATRAM88"));
			tareasTramite.setTaskguid11(resultSet.getString("TASKGUID11"));
			tareasTramite.setfRegistro11(resultSet.getDate("FREGISTRO11"));
			tareasTramite.setNumRegistro11(resultSet.getString("NUMREGISTRO11"));
			tareasTramite.setIdNotificacion11(resultSet.getString("IDNOTIFICACION11"));
			tareasTramite.setTramitesExpediente(tramitesExpediente);
			tareasTramite.setMaestroTareas(maestroTareas);
			// Datos de la notificacion postal
			return new NotifPostales(resultSet.getLong("IDNOTIFPOSTAL88"), resultSet.getString("TIPONOT88"),
					tareasTramite, resultSet.getInt("IDENVIOPOSTAL88"), resultSet.getInt("IDCOLECCIONPOSTAL88"),
					resultSet.getString("POSTALINBOXID88"), resultSet.getString("CODSITUACIONPOSTAL88"),
					resultSet.getDate("FECHASITUACION88"), resultSet.getInt("FINALIZADA88"),
					resultSet.getString("SITUACIONPOSTALES88"), resultSet.getString("SITUACIONPOSTALEU88"),
					resultSet.getInt("RETRAMITAR88"));
		}
	};

	private RowMapper<NotifPostales> rwMapPK = new RowMapper<NotifPostales>() {
		public NotifPostales mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			return new NotifPostales(resultSet.getLong("IDNOTIFPOSTAL88"));
		}
	};

	/**
	 * 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 NotifPostales table.
	 *
	 * @param notifpostales NotifPostales
	 * @return NotifPostales
	 */
	@Override
	public NotifPostales add(NotifPostales notifpostales) {
		StringBuilder query = new StringBuilder(STRING_BUILDER_INIT);
		query.append(
				"INSERT INTO NOTIF_POSTALES (ID_NOTIF_POSTAL_88, TIPO_NOT_88, ID_TAREA_TRAM_88, ID_ENVIO_POSTAL_88, ID_COLECCION_POSTAL_88, ")
				.append("POSTAL_INBOX_ID_88, COD_SITUACION_POSTAL_88, FECHA_SITUACION_88, FINALIZADA_88, SITUACION_POSTAL_ES_88, SITUACION_POSTAL_EU_88) ")
				.append("VALUES (").append(SEQUENCE_NOTIFPOSTALES).append(".NEXTVAL, ")
				.append(":tipoNot88, :idTareaTram, :idEnvioPostal, :idColeccionPostal, :postalInboxId, :codSituacionPostal, ")
				.append(":fechaSituacion, :finalizada, :situacionPostalEs, :situacionPostalEu)");

		// Por claridad en la definicion de argumentos pasa a usarse
		// NamedParameterJdbcTemplate
		MapSqlParameterSource parameters = new MapSqlParameterSource()
				.addValue("tipoNot88", notifpostales.getTipoNot88())
				.addValue("idTareaTram", notifpostales.getTareasTramite().getIdTareaTram11())
				.addValue("idEnvioPostal", notifpostales.getIdEnvioPostal88())
				.addValue("idColeccionPostal", notifpostales.getIdColeccionPostal88())
				.addValue("postalInboxId", notifpostales.getPostalInboxId88())
				.addValue("codSituacionPostal", notifpostales.getCodSituacionPostal88())
				.addValue("fechaSituacion", notifpostales.getFechaSituacion88())
				.addValue("finalizada", notifpostales.getFinalizada88())
				.addValue("situacionPostalEs", notifpostales.getSituacionPostalEs88())
				.addValue("situacionPostalEu", notifpostales.getSituacionPostalEu88());
		GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
		namedParameterJdbcTemplate.update(query.toString(), parameters, generatedKeyHolder,
				new String[] { "ID_NOTIF_POSTAL_88" });
		notifpostales.setIdNotifPostal88(generatedKeyHolder.getKey().longValue());
		return notifpostales;
	}

	private static final String UPDATE_QUERY = "UPDATE NOTIF_POSTALES SET ID_TAREA_TRAM_88=?, ID_ENVIO_POSTAL_88=?, ID_COLECCION_POSTAL_88=?, POSTAL_INBOX_ID_88=?, COD_SITUACION_POSTAL_88=?, FECHA_SITUACION_88=?, FINALIZADA_88=?, SITUACION_POSTAL_ES_88=?, SITUACION_POSTAL_EU_88=?, RETRAMITAR_88=? WHERE ID_NOTIF_POSTAL_88=?";

	/**
	 * Updates a single row in the NotifPostales table.
	 *
	 * @param notifpostales NotifPostales
	 * @return NotifPostales
	 */
	@Override
	public NotifPostales update(NotifPostales notifpostales) {
		this.jdbcTemplate.update(UPDATE_QUERY, notifpostales.getTareasTramite().getIdTareaTram11(),
				notifpostales.getIdEnvioPostal88(), notifpostales.getIdColeccionPostal88(),
				notifpostales.getPostalInboxId88(), notifpostales.getCodSituacionPostal88(),
				notifpostales.getFechaSituacion88(), notifpostales.getFinalizada88(),
				notifpostales.getSituacionPostalEs88(), notifpostales.getSituacionPostalEu88(),
				notifpostales.getRetramitar(), notifpostales.getIdNotifPostal88());
		return notifpostales;
	}

	@Override
	public List<NotifPostales> updateMultiple(List<NotifPostales> notifPostalesList) {
		this.jdbcTemplate.batchUpdate(UPDATE_QUERY, notifPostalesList, notifPostalesList.size(),
				new ParameterizedPreparedStatementSetter<NotifPostales>() {
					@Override
					public void setValues(PreparedStatement ps, NotifPostales item) throws SQLException {
						int i = 1;
						ps.setObject(i++, item.getTareasTramite().getIdTareaTram11());
						ps.setObject(i++, item.getIdEnvioPostal88());
						ps.setObject(i++, item.getIdColeccionPostal88());
						ps.setObject(i++, item.getPostalInboxId88());
						ps.setObject(i++, item.getCodSituacionPostal88());
						ps.setObject(i++, getSQLDate(item.getFechaSituacion88()));
						ps.setObject(i++, item.getFinalizada88());
						ps.setObject(i++, item.getSituacionPostalEs88());
						ps.setObject(i++, item.getSituacionPostalEu88());
						ps.setObject(i++, item.getRetramitar());
						ps.setObject(i, item.getIdNotifPostal88());
					}

					/*
					 * Prepara una fecha para ponerla en los parametros
					 */
					private java.sql.Date getSQLDate(Date date) {
						if (date != null) {
							return new java.sql.Date(date.getTime());
						}
						return null;
					}
				});
		return notifPostalesList;
	}

	/**
	 * Finds a single row in the NotifPostales table.
	 *
	 * @param notifpostales NotifPostales
	 * @return NotifPostales
	 */
	@Override
	@Transactional(readOnly = true)
	public NotifPostales find(NotifPostales notifpostales) {
		StringBuilder query = new StringBuilder(STRING_BUILDER_INIT);
		query.append(SELECT_FROM);
		query.append(
				" WHERE t2.ID_TAREA_TRAM_11 = t1.ID_TAREA_TRAM_88 AND t3.ID_TRAMITE_EXP_10 = t2.ID_TRAMITE_EXP_11 ")
				.append("AND t4.ID_EXPEDIENTE_00 = t3.ID_EXPEDIENTE_10 AND t6.ID_SOLICITANTE_01 = t4.ID_SOLICITANTE_00 ")
				.append("AND t7.ID_TAREA_38 = t2.ID_TAREA_11 ")
				.append("AND t5.ID_TRAMITE_37 = t3.ID_TRAMITE_10 AND t1.ID_NOTIF_POSTAL_88 = ?  ");
		List<NotifPostales> notifpostalesList = this.jdbcTemplate.query(query.toString(), this.rwMap,
				notifpostales.getIdNotifPostal88());
		return DataAccessUtils.uniqueResult(notifpostalesList);
	}

	/**
	 * Removes a single row in the NotifPostales table.
	 *
	 * @param notifpostales NotifPostales
	 * @return
	 */
	@Override
	public void remove(NotifPostales notifpostales) {
		String query = "DELETE FROM NOTIF_POSTALES WHERE ID_NOTIF_POSTAL_88=?";
		this.jdbcTemplate.update(query, notifpostales.getIdNotifPostal88());
	}

	/**
	 * Exporta Datos al clipBoard
	 *
	 */
	@Override
	public List<NotifPostales> getMultiple(NotifPostales filterNotifPostales, TableRequestDto tableRequestDto,
			Boolean startsWith) {
		StringBuilder query = new StringBuilder(STRING_BUILDER_INIT);
		query.append(SELECT_FROM);
		// Where clause & Params
		Map<String, Object> mapaWhere = this.getWhereLikeMap(filterNotifPostales, startsWith);
		StringBuilder where = new StringBuilder(query).append(" WHERE t2.ID_TAREA_TRAM_11 = t1.ID_TAREA_TRAM_88 ")
				.append("AND t3.ID_TRAMITE_EXP_10 = t2.ID_TRAMITE_EXP_11 AND t4.ID_EXPEDIENTE_00 = t3.ID_EXPEDIENTE_10 ")
				.append("AND t7.ID_TAREA_38 = t2.ID_TAREA_11 ")
				.append("AND t6.ID_SOLICITANTE_01 = t4.ID_SOLICITANTE_00 AND t5.ID_TRAMITE_37 = t3.ID_TRAMITE_10 ");
		where.append(mapaWhere.get(QUERY_WHERE));

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

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

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

	/**
	 * Finds rows in the NotifPostales table using like.
	 *
	 * @param notifpostales   NotifPostales
	 * @param tableRequestDto TableRequestDto
	 * @param startsWith      Boolean
	 * @return List<NotifPostales>
	 */
	@Override
	@Transactional(readOnly = true)
	public List<NotifPostales> findAllLike(NotifPostales notifpostales, TableRequestDto tableRequestDto,
			Boolean startsWith) {
		StringBuilder query = new StringBuilder(STRING_BUILDER_INIT);
		query.append(SELECT_FROM);

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(notifpostales, startsWith);
		StringBuilder where = new StringBuilder(STRING_BUILDER_INIT);
		where.append(
				" WHERE t2.ID_TAREA_TRAM_11 = t1.ID_TAREA_TRAM_88 AND t3.ID_TRAMITE_EXP_10 = t2.ID_TRAMITE_EXP_11 ")
				.append("AND t4.ID_EXPEDIENTE_00 = t3.ID_EXPEDIENTE_10 AND t6.ID_SOLICITANTE_01 = t4.ID_SOLICITANTE_00 ")
				.append("AND t7.ID_TAREA_38 = t2.ID_TAREA_11 ").append("AND t5.ID_TRAMITE_37 = t3.ID_TRAMITE_10 ");
		where.append(mapaWhere.get(QUERY_WHERE));
		query.append(where);

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

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

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

	/*
	 * OPERACIONES RUP_TABLE
	 */

	/**
	 * Counts rows in the NotifPostales table using like.
	 *
	 * @param notifpostales NotifPostales
	 * @param startsWith    Boolean
	 * @return Long
	 */
	@Override
	@Transactional(readOnly = true)
	public Long findAllLikeCount(NotifPostales notifpostales, Boolean startsWith) {
		StringBuilder query = new StringBuilder("SELECT COUNT(1) FROM NOTIF_POSTALES t1, TAREAS_TRAMITE t2, ");
		query.append(
				"TRAMITES_EXPEDIENTE t3, EXPEDIENTES t4, MAESTRO_TRAMITES t5, SOLICITANTES t6, MAESTRO_TAREAS t7 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(notifpostales, startsWith);
		StringBuilder where = new StringBuilder(STRING_BUILDER_INIT);
		where.append("WHERE t2.ID_TAREA_TRAM_11 = t1.ID_TAREA_TRAM_88 ")
				.append("AND t3.ID_TRAMITE_EXP_10 = t2.ID_TRAMITE_EXP_11 ")
				.append("AND t4.ID_EXPEDIENTE_00 = t3.ID_EXPEDIENTE_10 ")
				.append("AND t6.ID_SOLICITANTE_01 = t4.ID_SOLICITANTE_00 ")
				.append("AND t7.ID_TAREA_38 = t2.ID_TAREA_11 ").append("AND t5.ID_TRAMITE_37 = t3.ID_TRAMITE_10 ");
		where.append(mapaWhere.get(QUERY_WHERE));
		query.append(where);

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

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

	/**
	 * Reorder the data list of NotifPostales selected for rup_table
	 *
	 * @param notifpostales   NotifPostales
	 * @param tableRequestDto TableRequestDto
	 * @param startsWith      Boolean
	 * @return List<TableRowDto<NotifPostales>>
	 */
	@Override
	public List<TableRowDto<NotifPostales>> reorderSelection(NotifPostales notifpostales,
			TableRequestDto tableRequestDto, Boolean startsWith) {
		// SELECT
		StringBuilder query = new StringBuilder(STRING_BUILDER_INIT);
		query.append(SELECT_FROM);
		// FILTRADO
		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(notifpostales, startsWith);
		StringBuilder where = new StringBuilder(" WHERE t2.ID_TAREA_TRAM_11 = t1.ID_TAREA_TRAM_88 ")
				.append("AND t3.ID_TRAMITE_EXP_10 = t2.ID_TRAMITE_EXP_11 ")
				.append("AND t4.ID_EXPEDIENTE_00 = t3.ID_EXPEDIENTE_10 ")
				.append("AND t6.ID_SOLICITANTE_01 = t4.ID_SOLICITANTE_00 ")
				.append("AND t7.ID_TAREA_38 = t2.ID_TAREA_11 ").append("AND t5.ID_TRAMITE_37 = t3.ID_TRAMITE_10 ");
		where.append(mapaWhere.get(QUERY_WHERE));
		query.append(where);

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

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

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

	/**
	 * Removes multiple rows from the NotifPostales table.
	 *
	 * @param filterNotifPostales NotifPostales
	 * @param tableRequestDto     TableRequestDto
	 * @param startsWith          Boolean
	 */
	@Override
	public void removeMultiple(NotifPostales filterNotifPostales, TableRequestDto tableRequestDto, Boolean startsWith) {
		// Like clause and params
		Map<String, Object> mapWhereLike = this.getWhereLikeMap(filterNotifPostales, startsWith);

		// Delete query
		StringBuilder sbRemoveMultipleSQL = TableManager.getRemoveMultipleQuery(mapWhereLike, tableRequestDto,
				NotifPostales.class, "NOTIF_POSTALES", "t1", "IDNOTIFPOSTAL88");

		// Params list. Includes needed params for like and IN/NOT IN clauses
		@SuppressWarnings("unchecked")
		List<Object> params = (List<Object>) mapWhereLike.get(PARAMS_WHERE);
		params.addAll(tableRequestDto.getMultiselection().getSelectedIds());

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

	/*
	 * MÉTODOS PRIVADOS
	 */

	/**
	 * Returns a map with the needed value to create the conditions to filter by the
	 * NotifPostales entity
	 *
	 * @param notifpostales   NotifPostales Bean with the criteria values to filter
	 *                        by.
	 * @param startsWithParam 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
	@SuppressWarnings({ "java:S3776", "squid:MethodCyclomaticComplexity", "squid:S138" })
	private Map<String, Object> getWhereLikeMap(NotifPostales notifpostales, Boolean startsWithParam) {
		boolean startsWith = startsWithParam == null ? Boolean.FALSE : startsWithParam.booleanValue();
		StringBuilder where = new StringBuilder(NotifPostalesDaoImpl.STRING_BUILDER_INIT);
		List<Object> params = new ArrayList<>();

		if (notifpostales != null && notifpostales.getIdNotifPostal88() != null) {
			where.append(" AND t1.ID_NOTIF_POSTAL_88 = ?");
			params.add(notifpostales.getIdNotifPostal88());
		}
		if (notifpostales != null && notifpostales.getTipoNot88() != null) {
			where.append(" AND t1.TIPO_NOT_88 = ?");
			params.add(notifpostales.getTipoNot88());
		}
		if (notifpostales != null && notifpostales.getTareasTramite() != null
				&& notifpostales.getTareasTramite().getIdTareaTram11() != null) {
			where.append(" AND t1.ID_TAREA_TRAM_88 = ?");
			params.add(notifpostales.getTareasTramite().getIdTareaTram11());
		}
		if (notifpostales != null && notifpostales.getTareasTramite() != null
				&& StringUtils.isNotBlank(notifpostales.getTareasTramite().getTaskguid11())) {
			where.append(" AND t1.TASKGUID_11 = ?");
			params.add(notifpostales.getTareasTramite().getTaskguid11());
		}
		if (notifpostales != null && notifpostales.getIdEnvioPostal88() != null) {
			where.append(" AND t1.ID_ENVIO_POSTAL_88 = ?");
			params.add(notifpostales.getIdEnvioPostal88());
		}
		if (notifpostales != null && notifpostales.getIdColeccionPostal88() != null) {
			where.append(" AND t1.ID_COLECCION_POSTAL_88 = ?");
			params.add(notifpostales.getIdColeccionPostal88());
		}
		if (notifpostales != null && notifpostales.getPostalInboxId88() != null) {
			where.append(" AND UPPER(t1.POSTAL_INBOX_ID_88) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(notifpostales.getPostalInboxId88().toUpperCase() + "%");
			} else {
				params.add("%" + notifpostales.getPostalInboxId88().toUpperCase() + "%");
			}
			where.append(" AND t1.POSTAL_INBOX_ID_88 IS NOT NULL");
		}
		if (notifpostales != null && notifpostales.getCodSituacionPostal88() != null) {
			where.append(" AND UPPER(t1.COD_SITUACION_POSTAL_88) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(notifpostales.getCodSituacionPostal88().toUpperCase() + "%");
			} else {
				params.add("%" + notifpostales.getCodSituacionPostal88().toUpperCase() + "%");
			}
			where.append(" AND t1.COD_SITUACION_POSTAL_88 IS NOT NULL");
		}
		if (notifpostales != null && notifpostales.getFechaSituacion88() != null) {
			where.append(" AND t1.FECHA_SITUACION_88 = ?");
			params.add(notifpostales.getFechaSituacion88());
		}
		if (notifpostales != null && notifpostales.getFinalizada88() != null) {
			where.append(" AND t1.FINALIZADA_88 = ?");
			params.add(notifpostales.getFinalizada88());
		}
		if (notifpostales != null && notifpostales.getSituacionPostalEs88() != null) {
			where.append(" AND UPPER(t1.SITUACION_POSTAL_ES_88) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(notifpostales.getSituacionPostalEs88().toUpperCase() + "%");
			} else {
				params.add("%" + notifpostales.getSituacionPostalEs88().toUpperCase() + "%");
			}
			where.append(" AND t1.SITUACION_POSTAL_ES_88 IS NOT NULL");
		}
		if (notifpostales != null && notifpostales.getSituacionPostalEu88() != null) {
			where.append(" AND UPPER(t1.SITUACION_POSTAL_EU_88) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(notifpostales.getSituacionPostalEu88().toUpperCase() + "%");
			} else {
				params.add("%" + notifpostales.getSituacionPostalEu88().toUpperCase() + "%");
			}
			where.append(" AND t1.SITUACION_POSTAL_EU_88 IS NOT NULL");
		}

		// Como el solicitante esta tan anidado realizamos aqui una sola vez la
		// comprobacion de si
		// esta informado
		boolean hasSolicitante = (notifpostales != null && notifpostales.getTareasTramite() != null
				&& notifpostales.getTareasTramite().getTramitesExpediente() != null
				&& notifpostales.getTareasTramite().getTramitesExpediente().getExpediente() != null
				&& notifpostales.getTareasTramite().getTramitesExpediente().getExpediente().getSolicitantes() != null);
		Solicitantes solicitantes = hasSolicitante
				? notifpostales.getTareasTramite().getTramitesExpediente().getExpediente().getSolicitantes()
				: null;
		if (hasSolicitante && solicitantes.getIdSolicitante01() != null) {
			where.append(" AND t6.IDSOLICITANTE_01 = ?");
			params.add(solicitantes.getIdSolicitante01());
		}
		if (hasSolicitante && !StringUtils.isEmpty(solicitantes.getIdentificador01())) {
			where.append(" AND UPPER(t6.IDENTIFICADOR_01) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(solicitantes.getIdentificador01().toUpperCase() + "%");
			} else {
				params.add("%" + solicitantes.getIdentificador01().toUpperCase() + "%");
			}
			where.append(" AND t6.IDENTIFICADOR_01 IS NOT NULL");
		}
		if (hasSolicitante && !StringUtils.isEmpty(solicitantes.getNombre01())) {
			where.append(" AND UPPER(t6.NOMBRE_01) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(solicitantes.getNombre01().toUpperCase() + "%");
			} else {
				params.add("%" + solicitantes.getNombre01().toUpperCase() + "%");
			}
			where.append(" AND t6.NOMBRE_01 IS NOT NULL");
		}
		if (hasSolicitante && !StringUtils.isEmpty(solicitantes.getApellido101())) {
			where.append(" AND UPPER(t6.APELLIDO1_01) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(solicitantes.getApellido101().toUpperCase() + "%");
			} else {
				params.add("%" + solicitantes.getApellido101().toUpperCase() + "%");
			}
			where.append(" AND t6.APELLIDO1_01 IS NOT NULL");
		}
		if (hasSolicitante && !StringUtils.isEmpty(solicitantes.getApellido201())) {
			where.append(" AND UPPER(t6.APELLIDO2_01) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(solicitantes.getApellido201().toUpperCase() + "%");
			} else {
				params.add("%" + solicitantes.getApellido201().toUpperCase() + "%");
			}
			where.append(" AND t6.APELLIDO2_01 IS NOT NULL");
		}
		if (notifpostales.getTareasTramite() != null && notifpostales.getTareasTramite().getTramitesExpediente() != null
				&& notifpostales.getTareasTramite().getTramitesExpediente().getMaestroTramites() != null
				&& notifpostales.getTareasTramite().getTramitesExpediente().getMaestroTramites()
						.getIdTramite37() != null) {
			where.append(" AND t5.ID_TRAMITE_37 = ?");
			params.add(notifpostales.getTareasTramite().getTramitesExpediente().getMaestroTramites().getIdTramite37());
		}
		// Otros campos de filtrado

		if (notifpostales != null && notifpostales.getFechaRegistroInicio() != null) {
			where.append(" AND t2.F_REGISTRO_11 >= ?");
			params.add(notifpostales.getFechaRegistroInicio());
		}
		if (notifpostales != null && notifpostales.getFechaRegistroFin() != null) {
			where.append(" AND t2.F_REGISTRO_11 <= ?");
			params.add(notifpostales.getFechaRegistroFin());
		}
		if (notifpostales != null && notifpostales.getFechaSituacionInicio() != null) {
			where.append(" AND t1.FECHA_SITUACION_88 >= ?");
			params.add(notifpostales.getFechaSituacionInicio());
		}
		if (notifpostales != null && notifpostales.getFechaSituacionFin() != null) {
			where.append(" AND t1.FECHA_SITUACION_88 <= ?");
			params.add(notifpostales.getFechaSituacionFin());
		}
		if (notifpostales != null && notifpostales.getTareasTramite() != null
				&& notifpostales.getTareasTramite().getTramitesExpediente() != null
				&& notifpostales.getTareasTramite().getTramitesExpediente().getExpediente() != null
				&& !StringUtils.isEmpty(notifpostales.getTareasTramite().getTramitesExpediente().getExpediente()
						.getNumExpediente00())) {
			where.append(" AND UPPER(t4.NUM_EXPEDIENTE_00) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(notifpostales.getTareasTramite().getTramitesExpediente().getExpediente().getNumExpediente00()
						.toUpperCase() + "%");
			} else {
				params.add("%" + notifpostales.getTareasTramite().getTramitesExpediente().getExpediente()
						.getNumExpediente00().toUpperCase() + "%");
			}
		}
		if (notifpostales != null && notifpostales.getTareasTramite() != null
				&& notifpostales.getTareasTramite().getTramitesExpediente() != null
				&& notifpostales.getTareasTramite().getTramitesExpediente().getExpediente() != null && notifpostales
						.getTareasTramite().getTramitesExpediente().getExpediente().getIdExpediente00() != null) {
			where.append(" AND t4.ID_EXPEDIENTE_00 = ?");
			params.add(notifpostales.getTareasTramite().getTramitesExpediente().getExpediente().getIdExpediente00());
		}
		if (notifpostales != null && notifpostales.getTareasTramite() != null
				&& notifpostales.getTareasTramite().getTramitesExpediente() != null
				&& notifpostales.getTareasTramite().getTramitesExpediente().getExpediente() != null
				&& StringUtils.isNotEmpty(
						notifpostales.getTareasTramite().getTramitesExpediente().getExpediente().getFolderId00())) {
			where.append(" AND t4.FOLDER_ID_00 = ?");
			params.add(notifpostales.getTareasTramite().getTramitesExpediente().getExpediente().getFolderId00());
		}
		if (notifpostales != null && notifpostales.getTareasTramite() != null
				&& notifpostales.getTareasTramite().getMaestroTareas() != null
				&& StringUtils.isNotBlank(notifpostales.getTareasTramite().getMaestroTareas().getTipoTarea38())) {
			where.append(" AND t7.TIPO_TAREA_38 = ?");
			params.add(notifpostales.getTareasTramite().getMaestroTareas().getTipoTarea38());
		}

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

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

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