package com.ejie.aa21b.dao;

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

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

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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.aa21b.model.Aa21b02s01;
import com.ejie.aa21b.model.Aa21b13s01;
import com.ejie.aa21b.model.Aa21b21s01;
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;

/**
 * Aa21b21s01DaoImpl  
 * 
 *  
 */

@Repository
@Transactional
public class Aa21b21s01DaoImpl implements Aa21b21s01Dao {
	private JdbcTemplate jdbcTemplate;
	private static final Logger logger = LoggerFactory
			.getLogger(Aa21b21s01DaoImpl.class);

	/*
	 * ROW_MAPPERS
	 */
	private RowMapper<Aa21b21s01> rwMap = new RowMapper<Aa21b21s01>() {
		public Aa21b21s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			Aa21b21s01 ll = new Aa21b21s01(resultSet.getString("CSERV21"),
					resultSet.getString("CEXPE21"),
					resultSet.getString("AEXPE21"),
					resultSet.getInt("NEXPE21"),
					resultSet.getLong("CFASE21"),
					resultSet.getString("TDOCU21"),
					resultSet.getDate("FPREVI21"),
					resultSet.getDate("FREAL21"),
					resultSet.getString("DFASE21"),
					resultSet.getString("EFASE21"),
					resultSet.getString("EXTDOCU21"));
			ll.setCodPid21(resultSet.getString("CODPID21"));
			return ll;
		}
	};

	private RowMapper<Aa21b21s01> rwMapDocFaseExp = new RowMapper<Aa21b21s01>() {
		public Aa21b21s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			Aa21b21s01 ll = new Aa21b21s01(resultSet.getString("CSERV21"),
					resultSet.getString("CEXPE21"),
					resultSet.getString("AEXPE21"),
					resultSet.getInt("NEXPE21"), resultSet.getLong("CFASE21"),

					resultSet.getString("TDOCU21"),
					resultSet.getString("DOCU21"),
					resultSet.getString("EXTDOCU21"));
			ll.setCodPid21(resultSet.getString("CODPID21"));
			return ll;
		}
	};

	private RowMapper<Aa21b21s01> rwMapPK = new RowMapper<Aa21b21s01>() {
		public Aa21b21s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new Aa21b21s01(resultSet.getString("CSERV21"),
					resultSet.getString("CEXPE21"),
					resultSet.getString("AEXPE21"),
					resultSet.getInt("NEXPE21"), resultSet.getLong("CFASE21"));
		}
	};

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

			Aa21b21s01 aa21b21s01 = new Aa21b21s01(
					resultSet.getString("CSERV21"),
					resultSet.getString("CEXPE21"),
					resultSet.getString("AEXPE21"),
					resultSet.getInt("NEXPE21"),
					resultSet.getLong("CFASE21"),
					resultSet.getString("TDOCU21"),
					resultSet.getDate("FPREVI21"),
					resultSet.getDate("FREAL21"),
					resultSet.getString("DFASE21"),
					resultSet.getString("EFASE21"),
					resultSet.getString("EXTDOCU21"));

			JerarquiaDto<Aa21b21s01> jerarquia = new JerarquiaDto<Aa21b21s01>();
			jerarquia.setModel(aa21b21s01);
			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 Aa21b21s01 table.
	 * 
	 * @param aa21b21s01
	 *            Aa21b21s01
	 * @return Aa21b21s01
	 */
	public Aa21b21s01 add(Aa21b21s01 aa21b21s01) {
		Aa21b21s01DaoImpl.logger.info("M�todo add");
		String query = "INSERT INTO AA21B21S01 (CSERV21, CEXPE21, AEXPE21, NEXPE21, CFASE21,  TDOCU21, FPREVI21, FREAL21, EXTDOCU21,CODPID21) VALUES (?,?,?,?,?,?,?,?,?,?)";
		Aa21b21s01DaoImpl.logger.info("SQL = " + query);
		Aa21b21s01DaoImpl.logger.info("PARAM1=" + aa21b21s01.getCserv21());
		Aa21b21s01DaoImpl.logger.info("PARAM2=" + aa21b21s01.getCexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM3=" + aa21b21s01.getAexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM4=" + aa21b21s01.getNexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM5=" + aa21b21s01.getCfase21());

		Aa21b21s01DaoImpl.logger.info("PARAM6=" + aa21b21s01.getTdocu21());
		Aa21b21s01DaoImpl.logger.info("PARAM7=" + aa21b21s01.getFprevi21());
		Aa21b21s01DaoImpl.logger.info("PARAM8=" + aa21b21s01.getFreal21());
		Aa21b21s01DaoImpl.logger.info("PARAM9=" + 0);
		Aa21b21s01DaoImpl.logger.info("PARAM10=null");
		Aa21b21s01DaoImpl.logger.info("PARAM11=?" + aa21b21s01.getCodPid21());
		this.jdbcTemplate.update(query, aa21b21s01.getCserv21(),
				aa21b21s01.getCexpe21(), aa21b21s01.getAexpe21(),
				aa21b21s01.getNexpe21(), aa21b21s01.getCfase21(),
				aa21b21s01.getTdocu21(), aa21b21s01.getFprevi21(),
				aa21b21s01.getFreal21(), aa21b21s01.getExtdocu21(),
				aa21b21s01.getCodPid21());
		return aa21b21s01;
	}

	/**
	 * Updates a single row in the Aa21b21s01 table.
	 * 
	 * @param aa21b21s01
	 *            Aa21b21s01
	 * @return Aa21b21s01
	 */
	public Aa21b21s01 update(Aa21b21s01 aa21b21s01) {
		Aa21b21s01DaoImpl.logger.info("M�todo update");
		String query = "UPDATE AA21B21S01 SET  TDOCU21=?, FPREVI21=?, FREAL21=?, EXTDOCU21=?,CODPID21=? WHERE CSERV21=? AND CEXPE21=? AND AEXPE21=? AND NEXPE21=? AND CFASE21=?";
		Aa21b21s01DaoImpl.logger.info("SQL = " + query);

		Aa21b21s01DaoImpl.logger.info("PARAM1=" + aa21b21s01.getTdocu21());
		Aa21b21s01DaoImpl.logger.info("PARAM2=" + aa21b21s01.getFprevi21());
		Aa21b21s01DaoImpl.logger.info("PARAM3=" + aa21b21s01.getFreal21());
		Aa21b21s01DaoImpl.logger.info("PARAM4=" + aa21b21s01.getCodPid21());
		Aa21b21s01DaoImpl.logger.info("PARAM5=" + aa21b21s01.getCserv21());
		Aa21b21s01DaoImpl.logger.info("PARAM6=" + aa21b21s01.getCexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM7=" + aa21b21s01.getAexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM8=" + aa21b21s01.getNexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM9=" + aa21b21s01.getCfase21());

		this.jdbcTemplate.update(query, aa21b21s01.getTdocu21(),
				aa21b21s01.getFprevi21(), aa21b21s01.getFreal21(),
				aa21b21s01.getExtdocu21(), aa21b21s01.getCodPid21(),
				aa21b21s01.getCserv21(), aa21b21s01.getCexpe21(),
				aa21b21s01.getAexpe21(), aa21b21s01.getNexpe21(),
				aa21b21s01.getCfase21());
		return aa21b21s01;
	}

	/**
	 * Finds a single row in the Aa21b21s01 table.
	 * 
	 * @param aa21b21s01
	 *            Aa21b21s01
	 * @return Aa21b21s01
	 */
	@Transactional(readOnly = true)
	public Aa21b21s01 find(Aa21b21s01 aa21b21s01) {
		Aa21b21s01DaoImpl.logger.info("M�todo find");
		String query = "SELECT t1.codPID21 codpid21,t1.CSERV21 CSERV21, t1.CEXPE21 CEXPE21, t1.AEXPE21 AEXPE21, t1.NEXPE21 NEXPE21, t1.CFASE21 CFASE21,  t1.TDOCU21 TDOCU21, t1.FPREVI21 FPREVI21, t1.FREAL21 FREAL21, t2.DFASE01 DFASE21, t2.EFASE01 EFASE21,t1.EXTDOCU21 EXTDOCU21 FROM AA21B21S01 t1 LEFT JOIN AA21B01S01 t2 ON t1.CSERV21 = t2.CSERV01 AND t1.CEXPE21 = t2.CTIPOE01 AND t1.CFASE21 = t2.CFASE01 WHERE t1.CSERV21 = ?   AND t1.CEXPE21 = ?   AND t1.AEXPE21 = ?   AND t1.NEXPE21 = ?   AND t1.CFASE21 = ?  ";

		Aa21b21s01DaoImpl.logger.info("SQL = " + query);
		Aa21b21s01DaoImpl.logger.info("PARAM1=" + aa21b21s01.getCserv21());
		Aa21b21s01DaoImpl.logger.info("PARAM2=" + aa21b21s01.getCexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM3=" + aa21b21s01.getAexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM4=" + aa21b21s01.getNexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM5=" + aa21b21s01.getCfase21());
		List<Aa21b21s01> aa21b21s01List = this.jdbcTemplate.query(query,
				this.rwMap, aa21b21s01.getCserv21(), aa21b21s01.getCexpe21(),
				aa21b21s01.getAexpe21(), aa21b21s01.getNexpe21(),
				aa21b21s01.getCfase21());
		return (Aa21b21s01) DataAccessUtils.uniqueResult(aa21b21s01List);
	}

	/**
	 * Removes a single row in the Aa21b21s01 table.
	 * 
	 * @param aa21b21s01
	 *            Aa21b21s01
	 * @return
	 */
	public void remove(Aa21b21s01 aa21b21s01) {
		Aa21b21s01DaoImpl.logger.info("M�todo remove");
		String query = "DELETE FROM AA21B21S01 WHERE CSERV21=? AND CEXPE21=? AND AEXPE21=? AND NEXPE21=? AND CFASE21=?";
		Aa21b21s01DaoImpl.logger.info("SQL = " + query);
		Aa21b21s01DaoImpl.logger.info("PARAM1=" + aa21b21s01.getCserv21());
		Aa21b21s01DaoImpl.logger.info("PARAM2=" + aa21b21s01.getCexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM3=" + aa21b21s01.getAexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM4=" + aa21b21s01.getNexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM5=" + aa21b21s01.getCfase21());
		this.jdbcTemplate.update(query, aa21b21s01.getCserv21(),
				aa21b21s01.getCexpe21(), aa21b21s01.getAexpe21(),
				aa21b21s01.getNexpe21(), aa21b21s01.getCfase21());
	}

	/**
	 * Finds a list of rows in the Aa21b21s01 table.
	 * 
	 * @param aa21b21s01
	 *            Aa21b21s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @return List<Aa21b21s01>
	 */
	@Transactional(readOnly = true)
	public List<Aa21b21s01> findAll(Aa21b21s01 aa21b21s01,
			JQGridRequestDto jqGridRequestDto) {
		Aa21b21s01DaoImpl.logger.info("M�todo findAll");
		StringBuilder query = new StringBuilder(
				"SELECT  t1.codPID21 codpid21,t1.CSERV21 CSERV21,t1.CEXPE21 CEXPE21,t1.AEXPE21 AEXPE21,t1.NEXPE21 NEXPE21,t1.CFASE21 CFASE21,t1.TDOCU21 TDOCU21,t1.FPREVI21 FPREVI21,t1.FREAL21 FREAL21 ");
		query.append(",t2.DFASE01 DFASE21, t2.EFASE01 EFASE21, ");
		query.append("t1.EXTDOCU21 EXTDOCU21 ");
		query.append(" FROM AA21B21S01 t1 ");
		query.append(" LEFT JOIN AA21B01S01 t2 ON t1.CSERV21 = t2.CSERV01 AND t1.CEXPE21 = t2.CTIPOE01 AND t1.CFASE21 = t2.CFASE01 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(aa21b21s01);
		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);
		}
		Aa21b21s01DaoImpl.logger.info("findAll");
		Aa21b21s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b21s01DaoImpl.logger.info("PARAMS" + params);
		return (List<Aa21b21s01>) this.jdbcTemplate.query(query.toString(),
				this.rwMap, params.toArray());
	}

	/**
	 * Finds rows in the Aa21b21s01 table using like.
	 * 
	 * @param aa21b21s01
	 *            Aa21b21s01
	 * @param listaServicios
	 *            List<Aa21b13s01>
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @param listaTiposExpedientes
	 *            List<Aa21b02s01>
	 * @param startsWith
	 *            Boolean
	 * @return List<Aa21b21s01>
	 */
	@Transactional(readOnly = true)
	public List<Aa21b21s01> findAllLike(Aa21b21s01 aa21b21s01,
			JQGridRequestDto jqGridRequestDto, List<Aa21b13s01> listaServicios,
			List<Aa21b02s01> listaTiposExpedientes, Boolean startsWith) {

		Aa21b21s01DaoImpl.logger.info("M�todo findAllLike");
		StringBuilder query = new StringBuilder(
				"SELECT t1.codPID21 codpid21, t1.CSERV21 CSERV21,t1.CEXPE21 CEXPE21,t1.AEXPE21 AEXPE21,t1.NEXPE21 NEXPE21,t1.CFASE21 CFASE21,t1.TDOCU21 TDOCU21,t1.FPREVI21 FPREVI21,t1.FREAL21 FREAL21 ");
		query.append(",t2.DFASE01 DFASE21, t2.EFASE01 EFASE21, ");
		query.append("t1.EXTDOCU21 EXTDOCU21 ");
		query.append("FROM AA21B21S01 t1 ");
		query.append(" LEFT JOIN AA21B01S01 t2 ON t1.CSERV21 = t2.CSERV01 AND t1.CEXPE21 = t2.CTIPOE01 AND t1.CFASE21 = t2.CFASE01 ");

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

		Aa21b21s01DaoImpl.logger.info("findAllLike");
		Aa21b21s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b21s01DaoImpl.logger.info("PARAMS" + params);
		return (List<Aa21b21s01>) this.jdbcTemplate.query(query.toString(),
				this.rwMap, params.toArray());
	}

	/*
	 * OPERACIONES RUP_TABLE
	 */

	/**
	 * Counts rows in the Aa21b21s01 table.
	 * 
	 * @param aa21b21s01
	 *            Aa21b21s01
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllCount(Aa21b21s01 aa21b21s01) {
		Aa21b21s01DaoImpl.logger.info("M�todo findAllCount");
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM AA21B21S01 t1 ");

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

		List<?> params = (List<?>) mapaWhere.get("params");
		Aa21b21s01DaoImpl.logger.info("findAllCount");
		Aa21b21s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b21s01DaoImpl.logger.info("PARAMS" + params);
		return this.jdbcTemplate.queryForLong(query.toString(),
				params.toArray());
	}

	/**
	 * Counts rows in the Aa21b21s01 table using like.
	 * 
	 * @param aa21b21s01
	 *            Aa21b21s01
	 * @param listaServicios
	 *            List<Aa21b13s01>
	 * @param listaTiposExpedientes
	 *            List<Aa21b02s01>
	 * @param startsWith
	 *            Boolean
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllLikeCount(Aa21b21s01 aa21b21s01,
			List<Aa21b13s01> listaServicios,
			List<Aa21b02s01> listaTiposExpedientes, Boolean startsWith) {
		Aa21b21s01DaoImpl.logger.info("M�todo findAllLikeCount");
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM AA21B21S01 t1 ");

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

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

		Aa21b21s01DaoImpl.logger.info("findAllLikeCount");
		Aa21b21s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b21s01DaoImpl.logger.info("PARAMS" + params);
		return this.jdbcTemplate.queryForLong(query.toString(),
				params.toArray());
	}

	/**
	 * Reorder the data list of Aa21b21s01 selected for rup_table
	 * 
	 * @param aa21b21s01
	 *            Aa21b21s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @param startsWith
	 *            Boolean
	 * @return List<TableRowDto<Aa21b21s01>>
	 */
	@Override
	public List<TableRowDto<Aa21b21s01>> reorderSelection(
			Aa21b21s01 aa21b21s01, JQGridRequestDto jqGridRequestDto,
			Boolean startsWith) {
		Aa21b21s01DaoImpl.logger.info("M�todo reorderSelection");
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT t1.CSERV21 CSERV21,t1.CEXPE21 CEXPE21,t1.AEXPE21 AEXPE21,t1.NEXPE21 NEXPE21,t1.CFASE21 CFASE21,t1.TDOCU21 TDOCU21,t1.FPREVI21 FPREVI21,t1.FREAL21 FREAL21 ");
		// FROM
		query.append(",t2.DFASE01 DFASE21, t2.EFASE01 EFASE21, ");
		query.append("t1.EXTDOCU21 EXTDOCU21 ");
		query.append("FROM AA21B21S01 t1 ");
		query.append(" LEFT JOIN AA21B01S01 t2 ON t1.CSERV21 = t2.CSERV01 AND t1.CEXPE21 = t2.CTIPOE01 AND t1.CFASE21 = t2.CFASE01 ");
		// FILTRADO
		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(aa21b21s01, null, null,
				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, Aa21b21s01.class, filterParamList,
				"CSERV21,CEXPE21,AEXPE21,NEXPE21,CFASE21");

		Aa21b21s01DaoImpl.logger.info("reorderSelection");
		Aa21b21s01DaoImpl.logger.info("SQL = "
				+ sbReorderSelectionSQL.toString());
		Aa21b21s01DaoImpl.logger.info("PARAMs=" + filterParamList);
		return this.jdbcTemplate.query(sbReorderSelectionSQL.toString(),
				new RowNumResultSetExtractor<Aa21b21s01>(this.rwMapPK,
						jqGridRequestDto), filterParamList.toArray());
	}

	/**
	 * Search method for rup_table
	 * 
	 * @param filterParams
	 *            Aa21b21s01
	 * @param searchParams
	 *            Aa21b21s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @param startsWith
	 *            Boolean
	 * @return List<TableRowDto<Aa21b21s01>>
	 */
	@Override
	public List<TableRowDto<Aa21b21s01>> search(Aa21b21s01 filterParams,
			Aa21b21s01 searchParams, JQGridRequestDto jqGridRequestDto,
			Boolean startsWith) {
		Aa21b21s01DaoImpl.logger.info("M�todo search");
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT t1.CSERV21 CSERV21,t1.CEXPE21 CEXPE21,t1.AEXPE21 AEXPE21,t1.NEXPE21 NEXPE21,t1.CFASE21 CFASE21,t1.TDOCU21 TDOCU21,t1.FPREVI21 FPREVI21,t1.FREAL21 FREAL21 ");
		// FROM
		query.append(",t2.DFASE01 DFASE21, t2.EFASE01 EFASE21, ");
		query.append("t1.EXTDOCU21 EXTDOCU21 ");
		query.append("FROM AA21B21S01 t1 ");
		query.append(" LEFT JOIN AA21B01S01 t2 ON t1.CSERV21 = t2.CSERV01 AND t1.CEXPE21 = t2.CTIPOE01 AND t1.CFASE21 = t2.CFASE01 ");
		// TABLAS_ALIAS
		List<String> from_alias = new ArrayList<String>();
		from_alias.add(" t1 ");

		// FILTRADO
		Map<String, ?> mapaWhereFilter = this.getWhereLikeMap(filterParams,
				null, null, 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, null, null, 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, Aa21b21s01.class, filterParamList,
				searchSQL, searchParamList, from_alias,
				"CSERV21,CEXPE21,AEXPE21,NEXPE21,CFASE21");

		Aa21b21s01DaoImpl.logger.info("search");
		Aa21b21s01DaoImpl.logger.info("SQL = "
				+ sbReorderSelectionSQL.toString());
		Aa21b21s01DaoImpl.logger.info("PARAMs=" + filterParamList);

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

	/**
	 * Remove multiple method for rup_table
	 * 
	 * @param filteraa21b21s01
	 *            Aa21b21s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @param startsWith
	 *            Boolean
	 */
	@Override
	public void removeMultiple(Aa21b21s01 filteraa21b21s01,
			JQGridRequestDto jqGridRequestDto, Boolean startsWith) {
		Aa21b21s01DaoImpl.logger.info("M�todo removeMultiple");
		// SELECT
		/** TODO: select por clave */
		StringBuilder query = new StringBuilder(
				"SELECT t1.CSERV21 CSERV21,t1.CEXPE21 CEXPE21,t1.AEXPE21 AEXPE21,t1.NEXPE21 NEXPE21,t1.CFASE21 CFASE21,t1.TDOCU21 TDOCU21,t1.FPREVI21 FPREVI21,t1.FREAL21 FREAL21 ");
		// FROM
		query.append(",t2.DFASE01 DFASE21, t2.EFASE01 EFASE21, ");
		query.append("t1.EXTDOCU21 EXTDOCU21 ");
		query.append("FROM AA21B21S01 t1 ");
		query.append(" LEFT JOIN AA21B01S01 t2 ON t1.CSERV21 = t2.CSERV01 AND t1.CEXPE21 = t2.CTIPOE01 AND t1.CFASE21 = t2.CFASE01 ");

		// WHERE
		Map<String, Object> mapaWhere = this.getWhereLikeMap(filteraa21b21s01,
				null, null, 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, Aa21b21s01.class,
						query, params,
						"CSERV21,CEXPE21,AEXPE21,NEXPE21,CFASE21");
		Aa21b21s01DaoImpl.logger.info("removeMultiple");
		Aa21b21s01DaoImpl.logger
				.info("SQL = " + sbRemoveMultipleSQL.toString());
		Aa21b21s01DaoImpl.logger.info("PARAMs=" + params);
		this.jdbcTemplate.update(sbRemoveMultipleSQL.toString(),
				params.toArray());
	}

	/**
	 * Filter in the hierarchical Aa21b21s01 table.
	 * 
	 * @param filterAa21b21s01
	 *            Aa21b21s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @return List<JerarquiaDto<Aa21b21s01>>
	 */
	@Override
	public List<JerarquiaDto<Aa21b21s01>> findAllLikeJerarquia(
			Aa21b21s01 filterAa21b21s01, JQGridRequestDto jqGridRequestDto) {

		Aa21b21s01DaoImpl.logger.info("M�todo findAllLikeJerarquia");
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT t1.CSERV21 CSERV21,t1.CEXPE21 CEXPE21,t1.AEXPE21 AEXPE21,t1.NEXPE21 NEXPE21,t1.CFASE21 CFASE21,t1.TDOCU21 TDOCU21,t1.FPREVI21 FPREVI21,t1.FREAL21 FREAL21 ");
		query.append(",t2.DFASE01 DFASE21, t2.EFASE01 EFASE21, ");
		query.append("t1.EXTDOCU21 EXTDOCU21  ");
		// FROM
		// StringBuilder from = new StringBuilder("FROM AA21B21S01 t1 ");
		List<String> from = new ArrayList<String>();
		from.add("AA21B21S01");

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

		query.append(" LEFT JOIN AA21B01S01 t2 ON t1.CSERV21 = t2.CSERV01 AND t1.CEXPE21 = t2.CTIPOE01 AND t1.CFASE21 = t2.CFASE01 ");

		// 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(filterAa21b21s01, null,
				null, false);

		// JERARQUIA
		// Especificar la columna padre correspondiente en lugar de la generada
		// por defecto (ID_PADRE)
		query = JQGridManagerJerarquia.getQuery(jqGridRequestDto, query,
				mapaWhere, "CSERV21,CEXPE21,AEXPE21,NEXPE21,CFASE21",
				"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");
		Aa21b21s01DaoImpl.logger.info("findAllLikeJerarquia");
		Aa21b21s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b21s01DaoImpl.logger.info("PARAMs=" + params);
		return this.jdbcTemplate.query(query.toString(), this.rwMapJerarquia,
				params.toArray());
	}

	/**
	 * Count rows in hierarchy
	 * 
	 * @param filterAa21b21s01
	 *            Aa21b21s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @return Long
	 */
	@Override
	public Long findAllLikeCountJerarquia(Aa21b21s01 filterAa21b21s01,
			JQGridRequestDto jqGridRequestDto) {
		Aa21b21s01DaoImpl.logger.info("M�todo findAllLikeCountJerarquia");
		// FROM
		// StringBuilder from = new StringBuilder("FROM AA21B21S01 t1 ");
		List<String> from = new ArrayList<String>();
		from.add("AA21B21S01");

		// 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(filterAa21b21s01, null,
				null, false);

		// JERARQUIA
		// Especificar la columna padre correspondiente en lugar de la generada
		// por defecto (ID_PADRE)
		StringBuilder query = JQGridManagerJerarquia.getQueryCount(
				jqGridRequestDto, mapaWhere,
				"CSERV21,CEXPE21,AEXPE21,NEXPE21,CFASE21", "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");
		Aa21b21s01DaoImpl.logger.info("findAllLikeCountJerarquia");
		Aa21b21s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b21s01DaoImpl.logger.info("PARAMs=" + params);
		return this.jdbcTemplate.queryForLong(query.toString(),
				params.toArray());
	}

	/**
	 * Find dependent hierarchical rows
	 * 
	 * @param filterAa21b21s01
	 *            Aa21b21s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @return List<TableRowDto<Aa21b21s01>>
	 */
	@Override
	public List<TableRowDto<Aa21b21s01>> findAllChild(
			Aa21b21s01 filterAa21b21s01, JQGridRequestDto jqGridRequestDto) {

		Aa21b21s01DaoImpl.logger.info("M�todo findAllChild");
		// FROM
		// StringBuilder from = new StringBuilder("FROM AA21B21S01 t1 ");
		List<String> from = new ArrayList<String>();
		from.add("AA21B21S01");

		// 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(filterAa21b21s01, null,
				null, false);

		// MULTISELECCION
		// Especificar la columna padre correspondiente en lugar de la generada
		// por defecto (ID_PADRE)
		StringBuilder query = JQGridManagerJerarquia.getQueryChildren(
				jqGridRequestDto, mapaWhere,
				"CSERV21,CEXPE21,AEXPE21,NEXPE21,CFASE21", "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");
		Aa21b21s01DaoImpl.logger.info("findAllChild");
		Aa21b21s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b21s01DaoImpl.logger.info("PARAMs=" + params);
		return this.jdbcTemplate.query(query.toString(),
				new RowNumResultSetExtractor<Aa21b21s01>(this.rwMapPK,
						"CSERV21,CEXPE21,AEXPE21,NEXPE21,CFASE21"), params
						.toArray());
	}

	/*
	 * MÉTODOS PRIVADOS
	 */

	/**
	 * Returns a map with the needed value to create the conditions to filter by
	 * the Aa21b21s01 entity
	 * 
	 * @param aa21b21s01
	 *            Aa21b21s01 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(Aa21b21s01 aa21b21s01) {

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

		if (aa21b21s01 != null && aa21b21s01.getCserv21() != null) {
			where.append(" AND t1.CSERV21 = ?");
			params.add(aa21b21s01.getCserv21());
		}
		if (aa21b21s01 != null && aa21b21s01.getCexpe21() != null) {
			where.append(" AND t1.CEXPE21 = ?");
			params.add(aa21b21s01.getCexpe21());
		}
		if (aa21b21s01 != null && aa21b21s01.getAexpe21() != null) {
			where.append(" AND t1.AEXPE21 = ?");
			params.add(aa21b21s01.getAexpe21());
		}
		if (aa21b21s01 != null && aa21b21s01.getNexpe21() != null) {
			where.append(" AND t1.NEXPE21 = ?");
			params.add(aa21b21s01.getNexpe21());
		}
		if (aa21b21s01 != null && aa21b21s01.getCfase21() != null) {
			where.append(" AND t1.CFASE21 = ?");
			params.add(aa21b21s01.getCfase21());
		}

		if (aa21b21s01 != null && aa21b21s01.getTdocu21() != null) {
			where.append(" AND t1.TDOCU21 = ?");
			params.add(aa21b21s01.getTdocu21());
		}
		if (aa21b21s01 != null && aa21b21s01.getFprevi21() != null) {
			where.append(" AND t1.FPREVI21 = ?");
			params.add(aa21b21s01.getFprevi21());
		}
		if (aa21b21s01 != null && aa21b21s01.getFreal21() != null) {
			where.append(" AND t1.FREAL21 = ?");
			params.add(aa21b21s01.getFreal21());
		}

		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 Aa21b21s01 entity
	 * 
	 * @param aa21b21s01
	 *            Aa21b21s01 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(Aa21b21s01 aa21b21s01,
			List<Aa21b13s01> listaServicios,
			List<Aa21b02s01> listaTiposExpedientes, Boolean startsWith) {

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

		if (listaServicios != null) {
			if (listaServicios.size() > 0) {
				where.append(" AND (");
				Aa21b13s01 serviciosAux = null;
				for (Iterator<Aa21b13s01> iterator = listaServicios.iterator(); iterator
						.hasNext();) {

					serviciosAux = (Aa21b13s01) iterator.next();
					where.append(" t1.CSERV21 = ? ");
					params.add(serviciosAux.getCServ13());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

		if (listaTiposExpedientes != null) {
			if (listaTiposExpedientes.size() > 0) {
				where.append(" AND (");
				Aa21b02s01 serviciosAux = null;
				for (Iterator<Aa21b02s01> iterator = listaTiposExpedientes
						.iterator(); iterator.hasNext();) {

					serviciosAux = (Aa21b02s01) iterator.next();
					where.append(" t1.CEXPE21 = ? ");
					params.add(serviciosAux.getCtipoe02());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

		if (aa21b21s01 != null && aa21b21s01.getAexpe21() != null) {
			where.append(" AND UPPER(t1.AEXPE21) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b21s01.getAexpe21().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b21s01.getAexpe21().toUpperCase() + "%");
			}
			where.append(" AND t1.AEXPE21 IS NOT NULL");
		}
		if (aa21b21s01 != null && aa21b21s01.getNexpe21() != null) {
			where.append(" AND t1.NEXPE21 = ?");
			params.add(aa21b21s01.getNexpe21());
		}
		if (aa21b21s01 != null && aa21b21s01.getCfase21() != null) {
			where.append(" AND t1.CFASE21 = ?");
			params.add(aa21b21s01.getCfase21());
		}

		if (aa21b21s01 != null && aa21b21s01.getTdocu21() != null) {
			where.append(" AND UPPER(t1.TDOCU21) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b21s01.getTdocu21().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b21s01.getTdocu21().toUpperCase() + "%");
			}
			where.append(" AND t1.TDOCU21 IS NOT NULL");
		}
		if (aa21b21s01 != null && aa21b21s01.getFprevi21() != null) {
			where.append(" AND t1.FPREVI21 = ?");
			params.add(aa21b21s01.getFprevi21());
		}
		if (aa21b21s01 != null && aa21b21s01.getFreal21() != null) {
			where.append(" AND t1.FREAL21 = ?");
			params.add(aa21b21s01.getFreal21());
		}

		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;

	@Override
	public Aa21b21s01 findDocFaseExpediente(Aa21b21s01 aa21b21s01) {
		// TODO Auto-generated method stub
		Aa21b21s01DaoImpl.logger.info("M�todo findDocFaseExpediente");
		String query = "SELECT t1.codPID21 codpid21,t1.CSERV21 CSERV21, t1.CEXPE21 CEXPE21, t1.AEXPE21 AEXPE21, t1.NEXPE21 NEXPE21, t1.CFASE21 CFASE21,  t1.TDOCU21 TDOCU21, t1.EXTDOCU21 EXTDOCU21, t1.CODPID21 DOCU21 FROM AA21B21S01 t1  WHERE t1.CSERV21 = ?   AND t1.CEXPE21 = ?   AND t1.AEXPE21 = ?   AND t1.NEXPE21 = ?   AND t1.CFASE21 = ?  ";

		Aa21b21s01DaoImpl.logger.info("SQL = " + query);
		Aa21b21s01DaoImpl.logger.info("PARAM1=" + aa21b21s01.getCserv21());
		Aa21b21s01DaoImpl.logger.info("PARAM2=" + aa21b21s01.getCexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM3=" + aa21b21s01.getAexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM4=" + aa21b21s01.getNexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM5=" + aa21b21s01.getCfase21());
		List<Aa21b21s01> aa21b21s01List = this.jdbcTemplate.query(query,
				this.rwMapDocFaseExp, aa21b21s01.getCserv21(),
				aa21b21s01.getCexpe21(), aa21b21s01.getAexpe21(),
				aa21b21s01.getNexpe21(), aa21b21s01.getCfase21());
		return (Aa21b21s01) DataAccessUtils.uniqueResult(aa21b21s01List);
	}

	@Override
	public void updateDocumentoFaseExpediente(Aa21b21s01 aa21b21s01) {
		// TODO Auto-generated method stub
		Aa21b21s01DaoImpl.logger.info("M�todo update documento fase");

		String query = "UPDATE AA21B21S01 SET CODPID21=? WHERE CSERV21=? AND CEXPE21=? AND AEXPE21=? AND NEXPE21=? AND CFASE21=? ";

		Aa21b21s01DaoImpl.logger.info("SQL = " + query);

		Aa21b21s01DaoImpl.logger.info("PARAM1=" + aa21b21s01.getDocu21());
		Aa21b21s01DaoImpl.logger.info("PARAM2pk=" + aa21b21s01.getCserv21());
		Aa21b21s01DaoImpl.logger.info("PARAM3pk=" + aa21b21s01.getCexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM4pk=" + aa21b21s01.getAexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM5pk=" + aa21b21s01.getNexpe21());
		Aa21b21s01DaoImpl.logger.info("PARAM6pk=" + aa21b21s01.getCfase21());

		this.jdbcTemplate.update(query, aa21b21s01.getDocu21(),
				aa21b21s01.getCserv21(), aa21b21s01.getCexpe21(),
				aa21b21s01.getAexpe21(), aa21b21s01.getNexpe21(),
				aa21b21s01.getCfase21());

	}

	@Override
	public void removeDocumento(Aa21b21s01 aa21b21s01) {

		aa21b21s01.setDocu21("");
		updateDocumentoFaseExpediente(aa21b21s01);
	}
}
