package com.ejie.ab04b.dao;

import com.ejie.x38.dao.RowNumResultSetExtractor;
import com.ejie.x38.dto.JQGridManager;
import com.ejie.x38.dto.JQGridManagerJerarquia;
import com.ejie.x38.dto.JQGridRequestDto;
import com.ejie.x38.dto.JerarquiaDto;
import com.ejie.x38.dto.TableRowDto;
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.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ejie.ab04b.model.VdatosReqApe;

/**
 * VdatosReqApeDaoImpl generated by UDA, 20-jun-2017 10:36:48.
 * 
 * @author UDA
 */

@Repository
@Transactional
public class VdatosReqApeDaoImpl implements VdatosReqApeDao {
	private JdbcTemplate jdbcTemplate;

	/*
	 * ROW_MAPPERS
	 */
	private RowMapper<VdatosReqApe> rwMap = new RowMapper<VdatosReqApe>() {
		public VdatosReqApe mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new VdatosReqApe(resultSet.getString("ANOAPE"),
					resultSet.getString("TERAPE"), resultSet.getInt("NUMAPE"),
					resultSet.getDate("FECPRE"), resultSet.getString("NOMCTR"),
					resultSet.getString("DIRCTR"),
					resultSet.getString("PRVCTR"),
					resultSet.getString("DESPRVCTR"),
					resultSet.getString("MUNCTR"),
					resultSet.getString("DESMUNCTR"),
					resultSet.getString("LOCCTR"),
					resultSet.getString("NOMLOCALID"),
					resultSet.getString("NOMEMP"), resultSet.getString("PIE1"),
					resultSet.getString("PIE2"),
					resultSet.getString("DESCFIRMA"),
					resultSet.getString("FIRMA"));
		}
	};

	private RowMapper<VdatosReqApe> rwMapPK = new RowMapper<VdatosReqApe>() {
		public VdatosReqApe mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new VdatosReqApe(resultSet.getString("ANOAPE"),
					resultSet.getString("TERAPE"), resultSet.getInt("NUMAPE"),
					resultSet.getDate("FECPRE"), resultSet.getString("NOMCTR"),
					resultSet.getString("DIRCTR"),
					resultSet.getString("PRVCTR"),
					resultSet.getString("DESPRVCTR"),
					resultSet.getString("MUNCTR"),
					resultSet.getString("DESMUNCTR"),
					resultSet.getString("LOCCTR"),
					resultSet.getString("NOMLOCALID"),
					resultSet.getString("NOMEMP"), resultSet.getString("PIE1"),
					resultSet.getString("PIE2"),
					resultSet.getString("DESCFIRMA"),
					resultSet.getString("FIRMA"));
		}
	};

	/**
	 * Rowmapper para Jerarquía
	 * 
 dataSource
	 *            DataSource

	 */
	private RowMapper<JerarquiaDto<VdatosReqApe>> rwMapJerarquia = new RowMapper<JerarquiaDto<VdatosReqApe>>() {
		public JerarquiaDto<VdatosReqApe> mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {

			VdatosReqApe vdatosreqape = new VdatosReqApe(
					resultSet.getString("ANOAPE"),
					resultSet.getString("TERAPE"), resultSet.getInt("NUMAPE"),
					resultSet.getDate("FECPRE"), resultSet.getString("NOMCTR"),
					resultSet.getString("DIRCTR"),
					resultSet.getString("PRVCTR"),
					resultSet.getString("DESPRVCTR"),
					resultSet.getString("MUNCTR"),
					resultSet.getString("DESMUNCTR"),
					resultSet.getString("LOCCTR"),
					resultSet.getString("NOMLOCALID"),
					resultSet.getString("NOMEMP"), resultSet.getString("PIE1"),
					resultSet.getString("PIE2"),
					resultSet.getString("DESCFIRMA"),
					resultSet.getString("FIRMA"));

			JerarquiaDto<VdatosReqApe> jerarquia = new JerarquiaDto<VdatosReqApe>();
			jerarquia.setModel(vdatosreqape);
			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.
	 * 
	 *  dataSource            DataSource
	 *
	 * @param dataSource the new data source
	 */
	@Resource
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	/*
	 * OPERACIONES CRUD
	 */

	/**
	 * Inserts a single row in the VdatosReqApe table.
	 * 
	 *  vdatosreqape
	 *            VdatosReqApe
	 *  VdatosReqApe
	 *
	 * @param vdatosreqape the vdatosreqape
	 * @return the vdatos req ape
	 */
	public VdatosReqApe add(VdatosReqApe vdatosreqape) {
		String query = "INSERT INTO VDATOS_REQ_APE (ANOAPE, TERAPE, NUMAPE, FECPRE, NOMCTR, DIRCTR, PRVCTR, DESPRVCTR, MUNCTR, DESMUNCTR, LOCCTR, NOMLOCALID, NOMEMP, PIE1, PIE2, DESCFIRMA, FIRMA) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		this.jdbcTemplate.update(query, vdatosreqape.getAnoape(),
				vdatosreqape.getTerape(), vdatosreqape.getNumape(),
				vdatosreqape.getFecpre(), vdatosreqape.getNomctr(),
				vdatosreqape.getDirctr(), vdatosreqape.getPrvctr(),
				vdatosreqape.getDesprvctr(), vdatosreqape.getMunctr(),
				vdatosreqape.getDesmunctr(), vdatosreqape.getLocctr(),
				vdatosreqape.getNomlocalid(), vdatosreqape.getNomemp(),
				vdatosreqape.getPie1(), vdatosreqape.getPie2(),
				vdatosreqape.getDescfirma(), vdatosreqape.getFirma());
		return vdatosreqape;
	}

	/**
	 * Updates a single row in the VdatosReqApe table.
	 * 
	 *  vdatosreqape
	 *            VdatosReqApe
	 *  VdatosReqApe
	 *
	 * @param vdatosreqape the vdatosreqape
	 * @return the vdatos req ape
	 */
	public VdatosReqApe update(VdatosReqApe vdatosreqape) {
		String query = "UPDATE VDATOS_REQ_APE SET  WHERE ANOAPE=? AND TERAPE=? AND NUMAPE=? AND FECPRE=? AND NOMCTR=? AND DIRCTR=? AND PRVCTR=? AND DESPRVCTR=? AND MUNCTR=? AND DESMUNCTR=? AND LOCCTR=? AND NOMLOCALID=? AND NOMEMP=? AND PIE1=? AND PIE2=? AND DESCFIRMA=? AND FIRMA=?";
		this.jdbcTemplate.update(query, vdatosreqape.getAnoape(),
				vdatosreqape.getTerape(), vdatosreqape.getNumape(),
				vdatosreqape.getFecpre(), vdatosreqape.getNomctr(),
				vdatosreqape.getDirctr(), vdatosreqape.getPrvctr(),
				vdatosreqape.getDesprvctr(), vdatosreqape.getMunctr(),
				vdatosreqape.getDesmunctr(), vdatosreqape.getLocctr(),
				vdatosreqape.getNomlocalid(), vdatosreqape.getNomemp(),
				vdatosreqape.getPie1(), vdatosreqape.getPie2(),
				vdatosreqape.getDescfirma(), vdatosreqape.getFirma());
		return vdatosreqape;
	}

	/**
	 * Finds a single row in the VdatosReqApe table.
	 * 
	 *  vdatosreqape
	 *            VdatosReqApe
	 *  VdatosReqApe
	 *
	 * @param vdatosreqape the vdatosreqape
	 * @return the vdatos req ape
	 */
	@Transactional(readOnly = true)
	public VdatosReqApe find(VdatosReqApe vdatosreqape) {
		String query = "SELECT t1.ANOAPE ANOAPE, t1.TERAPE TERAPE, t1.NUMAPE NUMAPE, t1.FECPRE FECPRE, t1.NOMCTR NOMCTR, t1.DIRCTR DIRCTR, t1.PRVCTR PRVCTR, t1.DESPRVCTR DESPRVCTR, t1.MUNCTR MUNCTR, t1.DESMUNCTR DESMUNCTR, t1.LOCCTR LOCCTR, t1.NOMLOCALID NOMLOCALID, t1.NOMEMP NOMEMP, t1.PIE1 PIE1, t1.PIE2 PIE2, t1.DESCFIRMA DESCFIRMA, t1.FIRMA FIRMA FROM VDATOS_REQ_APE t1  WHERE t1.ANOAPE = ?   AND t1.TERAPE = ?   AND t1.NUMAPE = ? ";

		List<VdatosReqApe> vdatosreqapeList = this.jdbcTemplate.query(query,
				this.rwMap, vdatosreqape.getAnoape(), vdatosreqape.getTerape(),
				vdatosreqape.getNumape());
		return (VdatosReqApe) DataAccessUtils.uniqueResult(vdatosreqapeList);
	}

	/**
	 * Removes a single row in the VdatosReqApe table.
	 * 
	 *  vdatosreqape            VdatosReqApe
	 *
	 * @param vdatosreqape the vdatosreqape
	 */
	public void remove(VdatosReqApe vdatosreqape) {
		String query = "DELETE FROM VDATOS_REQ_APE WHERE ANOAPE=? AND TERAPE=? AND NUMAPE=? AND FECPRE=? AND NOMCTR=? AND DIRCTR=? AND PRVCTR=? AND DESPRVCTR=? AND MUNCTR=? AND DESMUNCTR=? AND LOCCTR=? AND NOMLOCALID=? AND NOMEMP=? AND PIE1=? AND PIE2=? AND DESCFIRMA=? AND FIRMA=?";
		this.jdbcTemplate.update(query, vdatosreqape.getAnoape(),
				vdatosreqape.getTerape(), vdatosreqape.getNumape(),
				vdatosreqape.getFecpre(), vdatosreqape.getNomctr(),
				vdatosreqape.getDirctr(), vdatosreqape.getPrvctr(),
				vdatosreqape.getDesprvctr(), vdatosreqape.getMunctr(),
				vdatosreqape.getDesmunctr(), vdatosreqape.getLocctr(),
				vdatosreqape.getNomlocalid(), vdatosreqape.getNomemp(),
				vdatosreqape.getPie1(), vdatosreqape.getPie2(),
				vdatosreqape.getDescfirma(), vdatosreqape.getFirma());
	}

	/**
	 * Finds a list of rows in the VdatosReqApe table.
	 * 
	 *  vdatosreqape
	 *            VdatosReqApe
	 *  jqGridRequestDto
	 *            JQGridRequestDto
	 *  List<VdatosReqApe>
	 *
	 * @param vdatosreqape the vdatosreqape
	 * @param jqGridRequestDto the jq grid request dto
	 * @return the list
	 */
	@Transactional(readOnly = true)
	public List<VdatosReqApe> findAll(VdatosReqApe vdatosreqape,
			JQGridRequestDto jqGridRequestDto) {
		StringBuilder query = new StringBuilder(
				"SELECT  t1.ANOAPE ANOAPE,t1.TERAPE TERAPE,t1.NUMAPE NUMAPE,t1.FECPRE FECPRE,t1.NOMCTR NOMCTR,t1.DIRCTR DIRCTR,t1.PRVCTR PRVCTR,t1.DESPRVCTR DESPRVCTR,t1.MUNCTR MUNCTR,t1.DESMUNCTR DESMUNCTR,t1.LOCCTR LOCCTR,t1.NOMLOCALID NOMLOCALID,t1.NOMEMP NOMEMP,t1.PIE1 PIE1,t1.PIE2 PIE2,t1.DESCFIRMA DESCFIRMA,t1.FIRMA FIRMA ");
		query.append("FROM VDATOS_REQ_APE t1 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(vdatosreqape);
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);

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

		if (jqGridRequestDto != null) {
			query = JQGridManager.getPaginationQuery(jqGridRequestDto, query);
		}

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

	/**
	 * Finds rows in the VdatosReqApe table using like.
	 * 
	 *  vdatosreqape
	 *            VdatosReqApe
	 *  jqGridRequestDto
	 *            JQGridRequestDto
	 *  startsWith
	 *            Boolean
	 *  List<VdatosReqApe>
	 *
	 * @param vdatosreqape the vdatosreqape
	 * @param jqGridRequestDto the jq grid request dto
	 * @param startsWith the starts with
	 * @return the list
	 */
	@Transactional(readOnly = true)
	public List<VdatosReqApe> findAllLike(VdatosReqApe vdatosreqape,
			JQGridRequestDto jqGridRequestDto, Boolean startsWith) {
		StringBuilder query = new StringBuilder(
				"SELECT  t1.ANOAPE ANOAPE,t1.TERAPE TERAPE,t1.NUMAPE NUMAPE,t1.FECPRE FECPRE,t1.NOMCTR NOMCTR,t1.DIRCTR DIRCTR,t1.PRVCTR PRVCTR,t1.DESPRVCTR DESPRVCTR,t1.MUNCTR MUNCTR,t1.DESMUNCTR DESMUNCTR,t1.LOCCTR LOCCTR,t1.NOMLOCALID NOMLOCALID,t1.NOMEMP NOMEMP,t1.PIE1 PIE1,t1.PIE2 PIE2,t1.DESCFIRMA DESCFIRMA,t1.FIRMA FIRMA ");
		query.append("FROM VDATOS_REQ_APE t1 ");

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

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

		if (jqGridRequestDto != null) {
			query = JQGridManager.getPaginationQuery(jqGridRequestDto, query);
		}

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

	/*
	 * OPERACIONES RUP_TABLE
	 */

	/**
	 * Counts rows in the VdatosReqApe table.
	 * 
	 *  vdatosreqape
	 *            VdatosReqApe
	 *  Long
	 *
	 * @param vdatosreqape the vdatosreqape
	 * @return the long
	 */
	@Transactional(readOnly = true)
	public Long findAllCount(VdatosReqApe vdatosreqape) {
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM VDATOS_REQ_APE t1 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(vdatosreqape);
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);

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

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

	/**
	 * Counts rows in the VdatosReqApe table using like.
	 * 
	 *  vdatosreqape
	 *            VdatosReqApe
	 *  startsWith
	 *            Boolean
	 *  Long
	 *
	 * @param vdatosreqape the vdatosreqape
	 * @param startsWith the starts with
	 * @return the long
	 */
	@Transactional(readOnly = true)
	public Long findAllLikeCount(VdatosReqApe vdatosreqape, Boolean startsWith) {
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM VDATOS_REQ_APE t1 ");

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

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

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

	/**
	 * Reorder the data list of VdatosReqApe selected for rup_table.
	 * 
	 *  vdatosreqape            VdatosReqApe
	 *  jqGridRequestDto            JQGridRequestDto
	 *  startsWith            Boolean
	 *  List<TableRowDto<VdatosReqApe>>
	 *
	 * @param vdatosreqape the vdatosreqape
	 * @param jqGridRequestDto the jq grid request dto
	 * @param startsWith the starts with
	 * @return the list
	 */
	@Override
	public List<TableRowDto<VdatosReqApe>> reorderSelection(
			VdatosReqApe vdatosreqape, JQGridRequestDto jqGridRequestDto,
			Boolean startsWith) {
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT t1.ANOAPE ANOAPE,t1.TERAPE TERAPE,t1.NUMAPE NUMAPE,t1.FECPRE FECPRE,t1.NOMCTR NOMCTR,t1.DIRCTR DIRCTR,t1.PRVCTR PRVCTR,t1.DESPRVCTR DESPRVCTR,t1.MUNCTR MUNCTR,t1.DESMUNCTR DESMUNCTR,t1.LOCCTR LOCCTR,t1.NOMLOCALID NOMLOCALID,t1.NOMEMP NOMEMP,t1.PIE1 PIE1,t1.PIE2 PIE2,t1.DESCFIRMA DESCFIRMA,t1.FIRMA FIRMA ");
		// FROM
		query.append("FROM VDATOS_REQ_APE t1 ");
		// FILTRADO
		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(vdatosreqape,
				startsWith);
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		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 = JQGridManager
				.getReorderQuery(
						query,
						jqGridRequestDto,
						VdatosReqApe.class,
						filterParamList,
						"ANOAPE,TERAPE,NUMAPE,FECPRE,NOMCTR,DIRCTR,PRVCTR,DESPRVCTR,MUNCTR,DESMUNCTR,LOCCTR,NOMLOCALID,NOMEMP,PIE1,PIE2,DESCFIRMA,FIRMA");

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

	/**
	 * Search method for rup_table.
	 * 
	 *  filterParams            VdatosReqApe
	 *  searchParams            VdatosReqApe
	 *  jqGridRequestDto            JQGridRequestDto
	 *  startsWith            Boolean
	 *  List<TableRowDto<VdatosReqApe>>
	 *
	 * @param filterParams the filter params
	 * @param searchParams the search params
	 * @param jqGridRequestDto the jq grid request dto
	 * @param startsWith the starts with
	 * @return the list
	 */
	@Override
	public List<TableRowDto<VdatosReqApe>> search(VdatosReqApe filterParams,
			VdatosReqApe searchParams, JQGridRequestDto jqGridRequestDto,
			Boolean startsWith) {
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT t1.ANOAPE ANOAPE,t1.TERAPE TERAPE,t1.NUMAPE NUMAPE,t1.FECPRE FECPRE,t1.NOMCTR NOMCTR,t1.DIRCTR DIRCTR,t1.PRVCTR PRVCTR,t1.DESPRVCTR DESPRVCTR,t1.MUNCTR MUNCTR,t1.DESMUNCTR DESMUNCTR,t1.LOCCTR LOCCTR,t1.NOMLOCALID NOMLOCALID,t1.NOMEMP NOMEMP,t1.PIE1 PIE1,t1.PIE2 PIE2,t1.DESCFIRMA DESCFIRMA,t1.FIRMA FIRMA ");
		// FROM
		query.append("FROM VDATOS_REQ_APE t1 ");
		// TABLAS_ALIAS
		List<String> from_alias = new ArrayList<String>();
		from_alias.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 = JQGridManager
				.getSearchQuery(
						query,
						jqGridRequestDto,
						VdatosReqApe.class,
						filterParamList,
						searchSQL,
						searchParamList,
						from_alias,
						"ANOAPE,TERAPE,NUMAPE,FECPRE,NOMCTR,DIRCTR,PRVCTR,DESPRVCTR,MUNCTR,DESMUNCTR,LOCCTR,NOMLOCALID,NOMEMP,PIE1,PIE2,DESCFIRMA,FIRMA");

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

	/**
	 * Remove multiple method for rup_table.
	 * 
	 *  filtervdatosreqape            VdatosReqApe
	 *  jqGridRequestDto            JQGridRequestDto
	 *  startsWith            Boolean
	 *
	 * @param filtervdatosreqape the filtervdatosreqape
	 * @param jqGridRequestDto the jq grid request dto
	 * @param startsWith the starts with
	 */
	@Override
	public void removeMultiple(VdatosReqApe filtervdatosreqape,
			JQGridRequestDto jqGridRequestDto, Boolean startsWith) {
		// SELECT
		/** TODO: select por clave */
		StringBuilder query = new StringBuilder(
				"SELECT t1.ANOAPE ANOAPE,t1.TERAPE TERAPE,t1.NUMAPE NUMAPE,t1.FECPRE FECPRE,t1.NOMCTR NOMCTR,t1.DIRCTR DIRCTR,t1.PRVCTR PRVCTR,t1.DESPRVCTR DESPRVCTR,t1.MUNCTR MUNCTR,t1.DESMUNCTR DESMUNCTR,t1.LOCCTR LOCCTR,t1.NOMLOCALID NOMLOCALID,t1.NOMEMP NOMEMP,t1.PIE1 PIE1,t1.PIE2 PIE2,t1.DESCFIRMA DESCFIRMA,t1.FIRMA FIRMA ");
		// FROM
		query.append("FROM VDATOS_REQ_APE t1 ");

		// WHERE
		Map<String, Object> mapaWhere = this.getWhereLikeMap(
				filtervdatosreqape, startsWith);
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);

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

		StringBuilder sbRemoveMultipleSQL = JQGridManager
				.getRemoveMultipleQuery(
						jqGridRequestDto,
						VdatosReqApe.class,
						query,
						params,
						"ANOAPE,TERAPE,NUMAPE,FECPRE,NOMCTR,DIRCTR,PRVCTR,DESPRVCTR,MUNCTR,DESMUNCTR,LOCCTR,NOMLOCALID,NOMEMP,PIE1,PIE2,DESCFIRMA,FIRMA");
		this.jdbcTemplate.update(sbRemoveMultipleSQL.toString(),
				params.toArray());
	}

	/**
	 * Filter in the hierarchical VdatosReqApe table.
	 * 
	 *  filterVdatosReqApe
	 *            VdatosReqApe
	 *  jqGridRequestDto
	 *            JQGridRequestDto
	 *  List<JerarquiaDto<VdatosReqApe>>
	 *
	 * @param filterVdatosReqApe the filter vdatos req ape
	 * @param jqGridRequestDto the jq grid request dto
	 * @return the list
	 */
	@Override
	public List<JerarquiaDto<VdatosReqApe>> findAllLikeJerarquia(
			VdatosReqApe filterVdatosReqApe, JQGridRequestDto jqGridRequestDto) {
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT t1.ANOAPE ANOAPE,t1.TERAPE TERAPE,t1.NUMAPE NUMAPE,t1.FECPRE FECPRE,t1.NOMCTR NOMCTR,t1.DIRCTR DIRCTR,t1.PRVCTR PRVCTR,t1.DESPRVCTR DESPRVCTR,t1.MUNCTR MUNCTR,t1.DESMUNCTR DESMUNCTR,t1.LOCCTR LOCCTR,t1.NOMLOCALID NOMLOCALID,t1.NOMEMP NOMEMP,t1.PIE1 PIE1,t1.PIE2 PIE2,t1.DESCFIRMA DESCFIRMA,t1.FIRMA FIRMA ");
		// FROM
		// StringBuilder from = new StringBuilder("FROM VDATOS_REQ_APE t1 ");
		List<String> from = new ArrayList<String>();
		from.add("VDATOS_REQ_APE");
		// TABLAS_ALIAS
		List<String> from_alias = new ArrayList<String>();
		from_alias.add(" t1 ");

		// JOINS TABLAS
		/*
		 * Ejemplo de como indicar joins entre las tablas de la query
		 */
		/*
		 * StringBuilder joins = new StringBuilder("");
		 * joins.append("AND T1.ID=T2.ID_T1");
		 */

		// CONDICIONES (negocio)
		/*
		 * Ejemplo de como incluir condiciones de negocio en la consulta de la
		 * jerarquía
		 */
		/*
		 * StringBuilder businessFilters = new StringBuilder(); List<Object>
		 * businessParams = new ArrayList<Object>();
		 * businessFilters.append("   AND t1.EJIE = ?  ");
		 * businessParams.add("1");
		 */

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

		// JERARQUIA
		// Especificar la columna padre correspondiente en lugar de la generada
		// por defecto (ID_PADRE)
		query = JQGridManagerJerarquia
				.getQuery(
						jqGridRequestDto,
						query,
						mapaWhere,
						"ANOAPE,TERAPE,NUMAPE,FECPRE,NOMCTR,DIRCTR,PRVCTR,DESPRVCTR,MUNCTR,DESMUNCTR,LOCCTR,NOMLOCALID,NOMEMP,PIE1,PIE2,DESCFIRMA,FIRMA",
						"ID_PADRE", "NOMBRE", from, from_alias);

		/*
		 * Ejemplo de llamada en caso de indicar joins entre tablas y
		 * condiciones de negocio
		 */
		// query = JQGridManagerJerarquia.getQuery(jqGridRequestDto, query,
		// mapaWhere, "ID", "ID_PADRE", "NOMBRE", from, from_alias, joins,
		// businessFilters, businessParams);

		// PAGINACION
		if (jqGridRequestDto != null) {
			query = JQGridManagerJerarquia.getPaginationQuery(jqGridRequestDto,
					query);
		}

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

	/**
	 * Count rows in hierarchy.
	 * 
	 *  filterVdatosReqApe            VdatosReqApe
	 *  jqGridRequestDto            JQGridRequestDto
	 *  Long
	 *
	 * @param filterVdatosReqApe the filter vdatos req ape
	 * @param jqGridRequestDto the jq grid request dto
	 * @return the long
	 */
	@Override
	public Long findAllLikeCountJerarquia(VdatosReqApe filterVdatosReqApe,
			JQGridRequestDto jqGridRequestDto) {
		// FROM
		// StringBuilder from = new StringBuilder("FROM VDATOS_REQ_APE t1 ");
		List<String> from = new ArrayList<String>();
		from.add("VDATOS_REQ_APE");

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

		// JOINS TABLAS
		/*
		 * Ejemplo de como indicar joins entre las tablas de la query
		 */
		/*
		 * StringBuilder joins = new StringBuilder("");
		 * joins.append("AND T1.ID=T2.ID_T1");
		 */

		// CONDICIONES (negocio)
		/*
		 * Ejemplo de como incluir condiciones de negocio en la consulta de la
		 * jerarquía
		 */
		/*
		 * StringBuilder businessFilters = new StringBuilder(); List<Object>
		 * businessParams = new ArrayList<Object>();
		 * businessFilters.append("   AND t1.EJIE = ?  ");
		 * businessParams.add("1");
		 */

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

		// JERARQUIA
		// Especificar la columna padre correspondiente en lugar de la generada
		// por defecto (ID_PADRE)
		StringBuilder query = JQGridManagerJerarquia
				.getQueryCount(
						jqGridRequestDto,
						mapaWhere,
						"ANOAPE,TERAPE,NUMAPE,FECPRE,NOMCTR,DIRCTR,PRVCTR,DESPRVCTR,MUNCTR,DESMUNCTR,LOCCTR,NOMLOCALID,NOMEMP,PIE1,PIE2,DESCFIRMA,FIRMA",
						"ID_PADRE", from, from_alias);

		/*
		 * Ejemplo de llamada en caso de indicar joins entre tablas y
		 * condiciones de negocio
		 */
		// StringBuilder query =
		// JQGridManagerJerarquia.getQueryCount(jqGridRequestDto, mapaWhere,
		// "ID", "ID_PADRE", from, from_alias, joins, businessFilters,
		// businessParams);

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

	/**
	 * Find dependent hierarchical rows.
	 * 
	 *  filterVdatosReqApe            VdatosReqApe
	 *  jqGridRequestDto            JQGridRequestDto
	 *  List<TableRowDto<VdatosReqApe>>
	 *
	 * @param filterVdatosReqApe the filter vdatos req ape
	 * @param jqGridRequestDto the jq grid request dto
	 * @return the list
	 */
	@Override
	public List<TableRowDto<VdatosReqApe>> findAllChild(
			VdatosReqApe filterVdatosReqApe, JQGridRequestDto jqGridRequestDto) {

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

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

		// JOINS TABLAS
		/*
		 * Ejemplo de como indicar joins entre las tablas de la query
		 */
		/*
		 * StringBuilder joins = new StringBuilder("");
		 * joins.append("AND T1.ID=T2.ID_T1");
		 */

		// CONDICIONES (negocio)
		/*
		 * Ejemplo de como incluir condiciones de negocio en la consulta de la
		 * jerarquía
		 */
		/*
		 * StringBuilder businessFilters = new StringBuilder(); List<Object>
		 * businessParams = new ArrayList<Object>();
		 * businessFilters.append("   AND t1.EJIE = ?  ");
		 * businessParams.add("1");
		 */

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

		// MULTISELECCION
		// Especificar la columna padre correspondiente en lugar de la generada
		// por defecto (ID_PADRE)
		StringBuilder query = JQGridManagerJerarquia
				.getQueryChildren(
						jqGridRequestDto,
						mapaWhere,
						"ANOAPE,TERAPE,NUMAPE,FECPRE,NOMCTR,DIRCTR,PRVCTR,DESPRVCTR,MUNCTR,DESMUNCTR,LOCCTR,NOMLOCALID,NOMEMP,PIE1,PIE2,DESCFIRMA,FIRMA",
						"ID_PADRE", from, from_alias);

		/*
		 * Ejemplo de llamada en caso de indicar joins entre tablas y
		 * condiciones de negocio
		 */
		// StringBuilder query =
		// JQGridManagerJerarquia.getQueryChildren(jqGridRequestDto, mapaWhere,
		// "ID", "ID_PADRE", from, from_alias, joins, businessFilters,
		// businessParams);

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

		return this.jdbcTemplate
				.query(query.toString(),
						new RowNumResultSetExtractor<VdatosReqApe>(
								this.rwMapPK,
								"ANOAPE,TERAPE,NUMAPE,FECPRE,NOMCTR,DIRCTR,PRVCTR,DESPRVCTR,MUNCTR,DESMUNCTR,LOCCTR,NOMLOCALID,NOMEMP,PIE1,PIE2,DESCFIRMA,FIRMA"),
						params.toArray());
	}

	/*
	 * MÉTODOS PRIVADOS
	 */

	/**
	 * Returns a map with the needed value to create the conditions to filter by
	 * the VdatosReqApe entity.
	 * 
	 *  vdatosreqape            VdatosReqApe Bean with the criteria values to filter by.
	 *  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.
	 *
	 * @param vdatosreqape the vdatosreqape
	 * @return the where map
	 */
	// CHECKSTYLE:OFF CyclomaticComplexity - Generación de código de UDA
	private Map<String, ?> getWhereMap(VdatosReqApe vdatosreqape) {

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

		if (vdatosreqape != null && vdatosreqape.getAnoape() != null) {
			where.append(" AND t1.ANOAPE = ?");
			params.add(vdatosreqape.getAnoape());
		}
		if (vdatosreqape != null && vdatosreqape.getTerape() != null) {
			where.append(" AND t1.TERAPE = ?");
			params.add(vdatosreqape.getTerape());
		}
		if (vdatosreqape != null && vdatosreqape.getNumape() != null) {
			where.append(" AND t1.NUMAPE = ?");
			params.add(vdatosreqape.getNumape());
		}
		if (vdatosreqape != null && vdatosreqape.getFecpre() != null) {
			where.append(" AND t1.FECPRE = ?");
			params.add(vdatosreqape.getFecpre());
		}
		if (vdatosreqape != null && vdatosreqape.getNomctr() != null) {
			where.append(" AND t1.NOMCTR = ?");
			params.add(vdatosreqape.getNomctr());
		}
		if (vdatosreqape != null && vdatosreqape.getDirctr() != null) {
			where.append(" AND t1.DIRCTR = ?");
			params.add(vdatosreqape.getDirctr());
		}
		if (vdatosreqape != null && vdatosreqape.getPrvctr() != null) {
			where.append(" AND t1.PRVCTR = ?");
			params.add(vdatosreqape.getPrvctr());
		}
		if (vdatosreqape != null && vdatosreqape.getDesprvctr() != null) {
			where.append(" AND t1.DESPRVCTR = ?");
			params.add(vdatosreqape.getDesprvctr());
		}
		if (vdatosreqape != null && vdatosreqape.getMunctr() != null) {
			where.append(" AND t1.MUNCTR = ?");
			params.add(vdatosreqape.getMunctr());
		}
		if (vdatosreqape != null && vdatosreqape.getDesmunctr() != null) {
			where.append(" AND t1.DESMUNCTR = ?");
			params.add(vdatosreqape.getDesmunctr());
		}
		if (vdatosreqape != null && vdatosreqape.getLocctr() != null) {
			where.append(" AND t1.LOCCTR = ?");
			params.add(vdatosreqape.getLocctr());
		}
		if (vdatosreqape != null && vdatosreqape.getNomlocalid() != null) {
			where.append(" AND t1.NOMLOCALID = ?");
			params.add(vdatosreqape.getNomlocalid());
		}
		if (vdatosreqape != null && vdatosreqape.getNomemp() != null) {
			where.append(" AND t1.NOMEMP = ?");
			params.add(vdatosreqape.getNomemp());
		}
		if (vdatosreqape != null && vdatosreqape.getPie1() != null) {
			where.append(" AND t1.PIE1 = ?");
			params.add(vdatosreqape.getPie1());
		}
		if (vdatosreqape != null && vdatosreqape.getPie2() != null) {
			where.append(" AND t1.PIE2 = ?");
			params.add(vdatosreqape.getPie2());
		}
		if (vdatosreqape != null && vdatosreqape.getDescfirma() != null) {
			where.append(" AND t1.DESCFIRMA = ?");
			params.add(vdatosreqape.getDescfirma());
		}
		if (vdatosreqape != null && vdatosreqape.getFirma() != null) {
			where.append(" AND t1.FIRMA = ?");
			params.add(vdatosreqape.getFirma());
		}

		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

	/**
	 * Returns a map with the needed value to create the conditions to filter by
	 * the VdatosReqApe entity.
	 * 
	 *  vdatosreqape            VdatosReqApe Bean with the criteria values to filter by.
	 *  startsWith            Boolean
	 *  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.
	 *
	 * @param vdatosreqape the vdatosreqape
	 * @param startsWith the starts with
	 * @return the where like map
	 */
	// CHECKSTYLE:OFF CyclomaticComplexity - Generación de código de UDA
	private Map<String, Object> getWhereLikeMap(VdatosReqApe vdatosreqape,
			Boolean startsWith) {

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

		if (vdatosreqape != null && vdatosreqape.getAnoape() != null) {
			where.append(" AND UPPER(t1.ANOAPE) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getAnoape().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getAnoape().toUpperCase() + "%");
			}
			where.append(" AND t1.ANOAPE IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getTerape() != null) {
			where.append(" AND UPPER(t1.TERAPE) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getTerape().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getTerape().toUpperCase() + "%");
			}
			where.append(" AND t1.TERAPE IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getNumape() != null) {
			where.append(" AND t1.NUMAPE = ?");
			params.add(vdatosreqape.getNumape());
		}
		if (vdatosreqape != null && vdatosreqape.getFecpre() != null) {
			where.append(" AND t1.FECPRE = ?");
			params.add(vdatosreqape.getFecpre());
		}
		if (vdatosreqape != null && vdatosreqape.getNomctr() != null) {
			where.append(" AND UPPER(t1.NOMCTR) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getNomctr().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getNomctr().toUpperCase() + "%");
			}
			where.append(" AND t1.NOMCTR IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getDirctr() != null) {
			where.append(" AND UPPER(t1.DIRCTR) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getDirctr().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getDirctr().toUpperCase() + "%");
			}
			where.append(" AND t1.DIRCTR IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getPrvctr() != null) {
			where.append(" AND UPPER(t1.PRVCTR) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getPrvctr().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getPrvctr().toUpperCase() + "%");
			}
			where.append(" AND t1.PRVCTR IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getDesprvctr() != null) {
			where.append(" AND UPPER(t1.DESPRVCTR) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getDesprvctr().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getDesprvctr().toUpperCase()
						+ "%");
			}
			where.append(" AND t1.DESPRVCTR IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getMunctr() != null) {
			where.append(" AND UPPER(t1.MUNCTR) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getMunctr().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getMunctr().toUpperCase() + "%");
			}
			where.append(" AND t1.MUNCTR IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getDesmunctr() != null) {
			where.append(" AND UPPER(t1.DESMUNCTR) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getDesmunctr().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getDesmunctr().toUpperCase()
						+ "%");
			}
			where.append(" AND t1.DESMUNCTR IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getLocctr() != null) {
			where.append(" AND UPPER(t1.LOCCTR) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getLocctr().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getLocctr().toUpperCase() + "%");
			}
			where.append(" AND t1.LOCCTR IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getNomlocalid() != null) {
			where.append(" AND UPPER(t1.NOMLOCALID) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getNomlocalid().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getNomlocalid().toUpperCase()
						+ "%");
			}
			where.append(" AND t1.NOMLOCALID IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getNomemp() != null) {
			where.append(" AND UPPER(t1.NOMEMP) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getNomemp().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getNomemp().toUpperCase() + "%");
			}
			where.append(" AND t1.NOMEMP IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getPie1() != null) {
			where.append(" AND UPPER(t1.PIE1) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getPie1().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getPie1().toUpperCase() + "%");
			}
			where.append(" AND t1.PIE1 IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getPie2() != null) {
			where.append(" AND UPPER(t1.PIE2) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getPie2().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getPie2().toUpperCase() + "%");
			}
			where.append(" AND t1.PIE2 IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getDescfirma() != null) {
			where.append(" AND UPPER(t1.DESCFIRMA) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getDescfirma().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getDescfirma().toUpperCase()
						+ "%");
			}
			where.append(" AND t1.DESCFIRMA IS NOT NULL");
		}
		if (vdatosreqape != null && vdatosreqape.getFirma() != null) {
			where.append(" AND UPPER(t1.FIRMA) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(vdatosreqape.getFirma().toUpperCase() + "%");
			} else {
				params.add("%" + vdatosreqape.getFirma().toUpperCase() + "%");
			}
			where.append(" AND t1.FIRMA 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

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