package com.ejie.aa21b.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Formatter;
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.Aa21b07s01;
import com.ejie.aa21b.model.Aa21b13s01;
import com.ejie.aa21b.model.Aa21b14s01;
import com.ejie.aa21b.model.Aa21b15s01;
import com.ejie.aa21b.model.Aa21b20s01;
import com.ejie.aa21b.model.Aa21b20s01Consultas;
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;

/**
 * Aa21b20s01DaoImpl  
 * 
 *  
 */

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

	/*
	 * ROW_MAPPERS
	 */
	private RowMapper<Aa21b20s01> rwMap = new RowMapper<Aa21b20s01>() {
		public Aa21b20s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			Aa21b20s01 aux = new Aa21b20s01(resultSet.getString("CSERV20"),
					resultSet.getString("CEXPE20"),
					resultSet.getLong("AEXPE20"), resultSet.getInt("NEXPE20"),
					resultSet.getDate("FAPER20"),
					resultSet.getString("OBJETO20"),
					resultSet.getInt("BLOQ20"), resultSet.getDate("FCIER20"),
					resultSet.getString("REFER20"),
					resultSet.getLong("CRESPO20"),
					resultSet.getString("CPAIS20"),
					resultSet.getString("CPROV20"),
					resultSet.getString("CMUNI20"),
					resultSet.getString("OBSER20"),
					resultSet.getString("MONEDA20"),
					resultSet.getBigDecimal("EIMPOR20"),
					resultSet.getLong("PIMPOR20"),
					resultSet.getBigDecimal("EIMPORA20"),
					resultSet.getLong("PIMPORA20"),
					resultSet.getString("CDEPAR20"),
					resultSet.getString("CARCHI20"),
					resultSet.getString("UBICA20"),
					resultSet.getString("CESTA20"),
					resultSet.getString("USUB20"), resultSet.getDate("FVTO20"),
					resultSet.getDate("FAPER20"), resultSet.getDate("FAPER20"),
					resultSet.getDate("FCIER20"), resultSet.getDate("FCIER20"),
					resultSet.getDate("FVTO20"), resultSet.getDate("FVTO20"),
					resultSet.getString("DESTA"), resultSet.getString("EESTA"),
					resultSet.getString("REFEXP"),
					resultSet.getString("GRUPO"), resultSet.getString("CDOCU"),
					resultSet.getString("CFASE"),
					resultSet.getString("EXPANT20"));
			try {
				aux.setCaducadas(resultSet.getInt("caducadas"));
			} catch (Exception e) {
				aux.setCaducadas(0);
			}
			return aux;
		}
	};

	private RowMapper<Aa21b20s01> rwMapConGrupoTipoExp = new RowMapper<Aa21b20s01>() {
		public Aa21b20s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			Aa21b20s01 aux = new Aa21b20s01(resultSet.getString("CSERV20"),
					resultSet.getString("CEXPE20"),
					resultSet.getLong("AEXPE20"), resultSet.getInt("NEXPE20"),
					resultSet.getDate("FAPER20"),
					resultSet.getString("OBJETO20"),
					resultSet.getInt("BLOQ20"), resultSet.getDate("FCIER20"),
					resultSet.getString("REFER20"),
					resultSet.getLong("CRESPO20"),
					resultSet.getString("CPAIS20"),
					resultSet.getString("CPROV20"),
					resultSet.getString("CMUNI20"),
					resultSet.getString("OBSER20"),
					resultSet.getString("MONEDA20"),
					resultSet.getBigDecimal("EIMPOR20"),
					resultSet.getLong("PIMPOR20"),
					resultSet.getBigDecimal("EIMPORA20"),
					resultSet.getLong("PIMPORA20"),
					resultSet.getString("CDEPAR20"),
					resultSet.getString("CARCHI20"),
					resultSet.getString("UBICA20"),
					resultSet.getString("CESTA20"),
					resultSet.getString("USUB20"), resultSet.getDate("FVTO20"),
					resultSet.getDate("FAPER20"), resultSet.getDate("FAPER20"),
					resultSet.getDate("FCIER20"), resultSet.getDate("FCIER20"),
					resultSet.getDate("FVTO20"), resultSet.getDate("FVTO20"),
					resultSet.getString("DESTA"), resultSet.getString("EESTA"),
					resultSet.getString("REFEXP"),
					resultSet.getString("GRUPO"), resultSet.getString("CDOCU"),
					resultSet.getString("CFASE"),
					resultSet.getString("EXPANT20"),
					resultSet.getInt("GRUPOTIPOEXP"));

			try {
				aux.setCaducadas(resultSet.getInt("caducadas"));
			} catch (Exception e) {
				aux.setCaducadas(0);
			}
			return aux;
		}
	};

	private RowMapper<Aa21b20s01Consultas> rwMapConsultas = new RowMapper<Aa21b20s01Consultas>() {
		public Aa21b20s01Consultas mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new Aa21b20s01Consultas(resultSet.getLong("CSERV20"),
					resultSet.getString("DSERV20"),
					resultSet.getString("ESERV20"),
					resultSet.getString("CEXPE20"),
					resultSet.getLong("AEXPE20"), resultSet.getInt("NEXPE20"),
					resultSet.getDate("FAPER20"),
					resultSet.getString("OBJETO20"),
					resultSet.getInt("BLOQ20"), resultSet.getDate("FCIER20"),
					resultSet.getLong("CRESPO20"),
					resultSet.getString("CPAIS20"),
					resultSet.getString("CPROV20"),
					resultSet.getString("CMUNI20"),
					resultSet.getString("OBSER20"),
					resultSet.getBigDecimal("EIMPOR20"),
					resultSet.getBigDecimal("EIMPORA20"),
					resultSet.getString("CDEPAR20"),
					resultSet.getString("CARCHI20"),
					resultSet.getString("UBICA20"),
					resultSet.getString("CESTA20"),
					resultSet.getString("USUB20"), resultSet.getDate("FVTO20"),
					resultSet.getDate("FAPER20"), resultSet.getDate("FAPER20"),
					resultSet.getDate("FCIER20"), resultSet.getDate("FCIER20"),
					resultSet.getDate("FVTO20"), resultSet.getDate("FVTO20"),
					resultSet.getString("REFEXP"),
					resultSet.getString("DESTA20"),
					resultSet.getString("EESTA20"),
					resultSet.getString("DARCHI20"),
					resultSet.getString("EARCHI20"),
					resultSet.getString("DRESPO20"),
					resultSet.getString("DDEPAR20"),
					resultSet.getString("EDEPAR20"),
					resultSet.getString("DMUNI20"),
					resultSet.getString("EXPANT20"));
		}
	};

	private RowMapper<Aa21b20s01> rwMapPK = new RowMapper<Aa21b20s01>() {
		public Aa21b20s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new Aa21b20s01(resultSet.getLong("CSERV20"),
					resultSet.getString("CEXPE20"),
					resultSet.getLong("AEXPE20"), resultSet.getInt("NEXPE20"));
		}
	};

	private RowMapper<Aa21b20s01> rwMapExpAsociado = new RowMapper<Aa21b20s01>() {
		public Aa21b20s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			Aa21b20s01 ret = new Aa21b20s01(resultSet.getLong("CSERV20"),
					resultSet.getString("CEXPE20"),
					resultSet.getLong("AEXPE20"), resultSet.getInt("NEXPE20"),
					resultSet.getString("REFEXP"),
					resultSet.getString("CESTA20"),
					resultSet.getString("OBJETO20"));
			ret.setUbica20(resultSet.getString("UBICA20"));
			return ret;
		}
	};

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

			Aa21b20s01 aa21b20s01 = new Aa21b20s01(
					resultSet.getString("CSERV20"),
					resultSet.getString("CEXPE20"),
					resultSet.getLong("AEXPE20"), resultSet.getInt("NEXPE20"),
					resultSet.getDate("FAPER20"),
					resultSet.getString("OBJETO20"),
					resultSet.getInt("BLOQ20"), resultSet.getDate("FCIER20"),
					resultSet.getString("REFER20"),
					resultSet.getLong("CRESPO20"),
					resultSet.getString("CPAIS20"),
					resultSet.getString("CPROV20"),
					resultSet.getString("CMUNI20"),
					resultSet.getString("OBSER20"),
					resultSet.getString("MONEDA20"),
					resultSet.getBigDecimal("EIMPOR20"),
					resultSet.getLong("PIMPOR20"),
					resultSet.getBigDecimal("EIMPORA20"),
					resultSet.getLong("PIMPORA20"),
					resultSet.getString("CDEPAR20"),
					resultSet.getString("CARCHI20"),
					resultSet.getString("UBICA20"),
					resultSet.getString("CESTA20"),
					resultSet.getString("USUB20"), resultSet.getDate("FVTO20"),
					resultSet.getDate("FAPER20"), resultSet.getDate("FAPER20"),
					resultSet.getDate("FCIER20"), resultSet.getDate("FCIER20"),
					resultSet.getDate("FVTO20"), resultSet.getDate("FVTO20"),
					resultSet.getString("DESTA"), resultSet.getString("EESTA"),
					resultSet.getString("REFEXP"),
					resultSet.getString("GRUPO"), resultSet.getString("CDOCU"),
					resultSet.getString("CFASE"),
					resultSet.getString("EXPANT20"));

			JerarquiaDto<Aa21b20s01> jerarquia = new JerarquiaDto<Aa21b20s01>();
			jerarquia.setModel(aa21b20s01);
			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 Aa21b20s01 table.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @return Aa21b20s01
	 */
	public Aa21b20s01 add(Aa21b20s01 aa21b20s01) {
		Aa21b20s01DaoImpl.logger
				.info("Método add. Añade el expediente a la tabla 21");

		Formatter fmt = new Formatter();
		Integer correlativoNumExp = this
				.obtenerCorrelativoExpediente(aa21b20s01);

		aa21b20s01.setNexpe20(correlativoNumExp);
		aa21b20s01.setIdExpe(aa21b20s01.getCserv20() + "-"
				+ aa21b20s01.getCexpe20() + "-" + aa21b20s01.getAexpe20() + "-"
				+ correlativoNumExp);
		aa21b20s01.setRefExp(aa21b20s01.getCexpe20() + "/"
				+ fmt.format("%06d", correlativoNumExp) + "/"
				+ aa21b20s01.getAexpe20());

		String query = "INSERT INTO AA21B20S01 (CSERV20, CEXPE20, AEXPE20, NEXPE20, FAPER20, OBJETO20, BLOQ20, FCIER20, REFER20, CRESPO20, CPAIS20, CPROV20, CMUNI20, OBSER20, MONEDA20, EIMPOR20, PIMPOR20, EIMPORA20, PIMPORA20, CDEPAR20, CARCHI20, UBICA20, CESTA20, USUB20, FVTO20) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		Aa21b20s01DaoImpl.logger.info("SQL = " + query);
		Aa21b20s01DaoImpl.logger.info("PARAM1=" + aa21b20s01.getCserv20());
		Aa21b20s01DaoImpl.logger.info("PARAM2=" + aa21b20s01.getCexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM3=" + aa21b20s01.getAexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM4=" + correlativoNumExp);
		Aa21b20s01DaoImpl.logger.info("PARAM5=" + aa21b20s01.getFaper20());
		Aa21b20s01DaoImpl.logger.info("PARAM6=" + aa21b20s01.getObjeto20());
		Aa21b20s01DaoImpl.logger.info("PARAM7=" + 0);
		Aa21b20s01DaoImpl.logger.info("PARAM8=" + aa21b20s01.getFcier20());
		Aa21b20s01DaoImpl.logger.info("PARAM9=" + aa21b20s01.getRefer20());
		Aa21b20s01DaoImpl.logger.info("PARAM10=" + aa21b20s01.getCrespo20());
		Aa21b20s01DaoImpl.logger.info("PARAM11=" + aa21b20s01.getCpais20());
		Aa21b20s01DaoImpl.logger.info("PARAM12=" + aa21b20s01.getCprov20());
		Aa21b20s01DaoImpl.logger.info("PARAM13=" + aa21b20s01.getCmuni20());
		Aa21b20s01DaoImpl.logger.info("PARAM14=" + aa21b20s01.getObser20());
		Aa21b20s01DaoImpl.logger.info("PARAM15=" + aa21b20s01.getMoneda20());
		Aa21b20s01DaoImpl.logger.info("PARAM16=" + aa21b20s01.getEimpor20());
		Aa21b20s01DaoImpl.logger.info("PARAM17=" + aa21b20s01.getPimpor20());
		Aa21b20s01DaoImpl.logger.info("PARAM18=" + aa21b20s01.getEimpora20());
		Aa21b20s01DaoImpl.logger.info("PARAM19=" + aa21b20s01.getEimpora20());
		Aa21b20s01DaoImpl.logger.info("PARAM20=" + aa21b20s01.getEimpora20());
		Aa21b20s01DaoImpl.logger.info("PARAM21=" + aa21b20s01.getEimpora20());
		Aa21b20s01DaoImpl.logger.info("PARAM22=" + aa21b20s01.getEimpora20());
		Aa21b20s01DaoImpl.logger.info("PARAM23=" + aa21b20s01.getEimpora20());
		Aa21b20s01DaoImpl.logger.info("PARAM24=null");
		Aa21b20s01DaoImpl.logger.info("PARAM25=" + aa21b20s01.getEimpora20());

		this.jdbcTemplate.update(query, aa21b20s01.getCserv20(),
				aa21b20s01.getCexpe20(), aa21b20s01.getAexpe20(),
				correlativoNumExp, aa21b20s01.getFaper20(),
				aa21b20s01.getObjeto20(), 0, aa21b20s01.getFcier20(),
				aa21b20s01.getRefer20(), aa21b20s01.getCrespo20(),
				aa21b20s01.getCpais20(), aa21b20s01.getCprov20(),
				aa21b20s01.getCmuni20(), aa21b20s01.getObser20(),
				aa21b20s01.getMoneda20(), aa21b20s01.getEimpor20(),
				aa21b20s01.getPimpor20(), aa21b20s01.getEimpora20(),
				aa21b20s01.getPimpora20(), aa21b20s01.getCdepar20(),
				aa21b20s01.getCarchi20(), aa21b20s01.getUbica20(),
				aa21b20s01.getCesta20(), null, aa21b20s01.getFvto20());
		return aa21b20s01;
	}

	/**
	 * Finds rows in the Expedientes table using like.
	 * 
	 * @param expediente
	 *            Aa21b20s01
	 * 
	 * @return Integer
	 */

	@Transactional(readOnly = true)
	public Integer obtenerCorrelativoExpediente(Aa21b20s01 expediente) {

		Aa21b20s01DaoImpl.logger.info("Método obtenerCorrelativoExpediente");
		StringBuffer query = new StringBuffer(
				"SELECT  MAX(NEXPE20) FROM Aa21b20s01 where AEXPE20 = ? AND CEXPE20 = ? ");
		List<Object> params = new ArrayList<Object>();
		params.add(expediente.getAexpe20());
		params.add(expediente.getCexpe20());
		Aa21b20s01DaoImpl.logger.info("obtenerCorrelativoExpediente");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMs=" + params);
		return Integer.valueOf(this.jdbcTemplate.queryForInt(query.toString(),
				params.toArray())) + 1;
	}

	/**
	 * Finds rows in the Expedientes table using like.
	 * 
	 * Aa21b20s01
	 * 
	 * @return Integer
	 */
	@Transactional(readOnly = true)
	public String obtenerCorrelativoUbica() {

		Aa21b20s01DaoImpl.logger.info("Método obtenerCorrelativoUbica");
		// StringBuffer query = new
		// StringBuffer("SELECT  MAX(SUBSTR(UBICA20,3)) FROM Aa21b20s01 where CSERV20 = ? ");

		StringBuffer query = new StringBuffer(
				"SELECT  MAX(SUBSTR(UBICA20,3)) FROM Aa21b20s01 where CSERV20 = ? ");

		// Angel 14/09/2018 Solo cogemos los registros que empiezan por 'L/' y
		// el resto es numérico
		query.append(" AND SUBSTR(UBICA20,0,2)='L/'");
		query.append(" AND replace(translate(SUBSTR(UBICA20,3),'1234567890','0000000000'),'0',null) is null");

		List<Object> params = new ArrayList<Object>();
		params.add("93");

		Aa21b20s01DaoImpl.logger.info("obtenerCorrelativoUbica");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMs=" + params);
		return Integer.valueOf(
				this.jdbcTemplate.queryForInt(query.toString(),
						params.toArray()) + 1).toString();
	}

	/**
	 * Updates a single row in the Aa21b20s01 table.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @return Aa21b20s01
	 */
	public Aa21b20s01 update(Aa21b20s01 aa21b20s01) {
		Aa21b20s01DaoImpl.logger.info("Método update. Actualiza expediente");
		String query = "UPDATE AA21B20S01 SET FAPER20=?, OBJETO20=?, FCIER20=?, REFER20=?, CRESPO20=?, CPAIS20=?, CPROV20=?, CMUNI20=?, OBSER20=?, MONEDA20=?, EIMPOR20=?, PIMPOR20=?, EIMPORA20=?, PIMPORA20=?, CDEPAR20=?, CARCHI20=?, UBICA20=?, CESTA20=?, FVTO20=?, EXPANT20=? WHERE CSERV20=? AND CEXPE20=? AND AEXPE20=? AND NEXPE20=?";

		Aa21b20s01DaoImpl.logger.info("SQL = " + query);
		Aa21b20s01DaoImpl.logger.info("PARAM1=" + aa21b20s01.getFaper20());
		Aa21b20s01DaoImpl.logger.info("PARAM2=" + aa21b20s01.getFcier20());
		Aa21b20s01DaoImpl.logger.info("PARAM3=" + aa21b20s01.getCrespo20());
		Aa21b20s01DaoImpl.logger.info("PARAM4=" + aa21b20s01.getCprov20());
		Aa21b20s01DaoImpl.logger.info("PARAM5=" + 0);
		Aa21b20s01DaoImpl.logger.info("PARAM6=null");

		this.jdbcTemplate.update(query, aa21b20s01.getFaper20(),
				aa21b20s01.getObjeto20(), aa21b20s01.getFcier20(),
				aa21b20s01.getRefer20(), aa21b20s01.getCrespo20(),
				aa21b20s01.getCpais20(), aa21b20s01.getCprov20(),
				aa21b20s01.getCmuni20(), aa21b20s01.getObser20(),
				aa21b20s01.getMoneda20(), aa21b20s01.getEimpor20(),
				aa21b20s01.getPimpor20(), aa21b20s01.getEimpora20(),
				aa21b20s01.getPimpora20(), aa21b20s01.getCdepar20(),
				aa21b20s01.getCarchi20(), aa21b20s01.getUbica20(),
				aa21b20s01.getCesta20(), aa21b20s01.getFvto20(),
				aa21b20s01.getExpant20(), aa21b20s01.getCserv20(),
				aa21b20s01.getCexpe20(), aa21b20s01.getAexpe20(),
				aa21b20s01.getNexpe20());
		return aa21b20s01;
	}

	/**
	 * Updates a single row in the Aa21b20s01 table.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @return Aa21b20s01
	 */
	public Aa21b20s01 updateModificacionEspecial(Aa21b20s01 aa21b20s01) {
		Aa21b20s01DaoImpl.logger.info("Método update. Actualiza expediente");
		String query = "UPDATE AA21B20S01 SET FAPER20=? WHERE CSERV20=? AND CEXPE20=? AND AEXPE20=? AND NEXPE20=?";

		Aa21b20s01DaoImpl.logger.info("SQL = " + query);
		Aa21b20s01DaoImpl.logger.info("PARAM1=" + aa21b20s01.getAexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM2=" + aa21b20s01.getFaper20());
		Aa21b20s01DaoImpl.logger.info("PARAM3=" + aa21b20s01.getCserv20());
		Aa21b20s01DaoImpl.logger.info("PARAM4=" + aa21b20s01.getCexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM5=" + aa21b20s01.getAexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM6=" + aa21b20s01.getNexpe20());

		this.jdbcTemplate.update(query, aa21b20s01.getFaper20(),
				aa21b20s01.getCserv20(), aa21b20s01.getCexpe20(),
				aa21b20s01.getAexpe20(), aa21b20s01.getNexpe20());
		return aa21b20s01;
	}

	/**
	 * Actualiza el servicio del registro de entrada
	 * 
	 * @param aa21b20s01
	 * @param nuevoCexpe
	 * @param nuevoAexpe
	 * @param nuevoNexpe
	 * @return
	 */
	public Aa21b20s01 update(Aa21b20s01 aa21b20s01, String nuevoCexpe,
			long nuevoAexpe, int nuevoNexpe) {
		Aa21b20s01DaoImpl.logger.info("Método update. Actualiza expediente");
		String query = "UPDATE AA21B20S01 SET CEXPE20=?, AEXPE20=?,NEXPE20=?,EXPANT20=? WHERE CSERV20=? AND CEXPE20=? AND AEXPE20=? AND NEXPE20=?";

		Aa21b20s01DaoImpl.logger.info("SQL = " + query);
		Aa21b20s01DaoImpl.logger.info("PARAM1=" + nuevoCexpe);
		Aa21b20s01DaoImpl.logger.info("PARAM2=" + nuevoAexpe);
		Aa21b20s01DaoImpl.logger.info("PARAM3=" + nuevoNexpe);
		Aa21b20s01DaoImpl.logger.info("PARAM4=" + aa21b20s01.getExpant20());
		Aa21b20s01DaoImpl.logger.info("PARAM5=" + aa21b20s01.getCserv20());
		Aa21b20s01DaoImpl.logger.info("PARAM6=" + aa21b20s01.getCexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM7=" + aa21b20s01.getAexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM8=" + aa21b20s01.getNexpe20());

		this.jdbcTemplate.update(query, nuevoCexpe, nuevoAexpe, nuevoNexpe,
				aa21b20s01.getExpant20(), aa21b20s01.getCserv20(),
				aa21b20s01.getCexpe20(), aa21b20s01.getAexpe20(),
				aa21b20s01.getNexpe20());
		return aa21b20s01;
	}

	/**
	 * Updates a single row in the Aa21b20s01 table.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @return Aa21b20s01
	 */
	public Aa21b20s01 updateBloqueos(Aa21b20s01 aa21b20s01) {
		Aa21b20s01DaoImpl.logger.info("updateBloqueos");
		if (aa21b20s01 != null && aa21b20s01.getAexpe20() != null
				&& aa21b20s01.getNexpe20() != null) {
			String query = "UPDATE AA21B20S01 SET BLOQ20=?, USUB20=? WHERE CSERV20=? AND CEXPE20=? AND AEXPE20=? AND NEXPE20=?";
			Aa21b20s01DaoImpl.logger.info("SQL = " + query);
			Aa21b20s01DaoImpl.logger.info("PARAM1=" + aa21b20s01.getBloq20());
			Aa21b20s01DaoImpl.logger.info("PARAM2=" + aa21b20s01.getUsub20());
			Aa21b20s01DaoImpl.logger
					.info("PARAM3pk=" + aa21b20s01.getCserv20());
			Aa21b20s01DaoImpl.logger
					.info("PARAM4pk=" + aa21b20s01.getCexpe20());
			Aa21b20s01DaoImpl.logger
					.info("PARAM5pk=" + aa21b20s01.getAexpe20());
			Aa21b20s01DaoImpl.logger
					.info("PARAM6pk=" + aa21b20s01.getNexpe20());

			this.jdbcTemplate.update(query, aa21b20s01.getBloq20(),
					aa21b20s01.getUsub20(), aa21b20s01.getCserv20(),
					aa21b20s01.getCexpe20(), aa21b20s01.getAexpe20(),
					aa21b20s01.getNexpe20());
		} else if (aa21b20s01 != null && aa21b20s01.getCserv20() != null) {
			String query = "UPDATE AA21B20S01 SET BLOQ20=?, USUB20=? WHERE CSERV20=? ";
			Aa21b20s01DaoImpl.logger.info("SQL = " + query);
			Aa21b20s01DaoImpl.logger.info("PARAM1=" + 0);
			Aa21b20s01DaoImpl.logger.info("PARAM2=null");
			Aa21b20s01DaoImpl.logger.info("PARAM3=" + aa21b20s01.getCserv20());

			this.jdbcTemplate.update(query, 0, null, aa21b20s01.getCserv20());
		} else {
			String query = "UPDATE AA21B20S01 SET BLOQ20=?, USUB20=? ";
			Aa21b20s01DaoImpl.logger.info("SQL = " + query);
			Aa21b20s01DaoImpl.logger.info("PARAM1=" + 0);
			Aa21b20s01DaoImpl.logger.info("PARAM2=null");

			this.jdbcTemplate.update(query, 0, null);
		}

		return aa21b20s01;
	}

	/**
	 * Finds a single row in the Aa21b20s01 table.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @return Aa21b20s01
	 */
	@Transactional(readOnly = true)
	public Aa21b20s01 find(Aa21b20s01 aa21b20s01) {
		Aa21b20s01DaoImpl.logger.info("find");

		StringBuilder query = new StringBuilder(
				"SELECT t1.CSERV20 CSERV20, t1.CEXPE20 CEXPE20, t1.AEXPE20 AEXPE20, t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP, t1.FAPER20 FAPER20, t1.OBJETO20 OBJETO20, t1.BLOQ20 BLOQ20, t1.FCIER20 FCIER20, t1.REFER20 REFER20, t1.CRESPO20 CRESPO20, t1.CPAIS20 CPAIS20, t1.CPROV20 CPROV20, t1.CMUNI20 CMUNI20, t1.OBSER20 OBSER20, t1.MONEDA20 MONEDA20, t1.EIMPOR20 EIMPOR20, t1.PIMPOR20 PIMPOR20, t1.EIMPORA20 EIMPORA20, t1.PIMPORA20 PIMPORA20, t1.CDEPAR20 CDEPAR20, t1.CARCHI20 CARCHI20, t1.UBICA20 UBICA20, t1.CESTA20 CESTA20, t1.USUB20 USUB20, t1.FVTO20 FVTO20, t1.EXPANT20 EXPANT20, t07.DESTA07 DESTA, t07.EESTA07 EESTA, null GRUPO, NULL CDOCU,NULL CFASE");// t22.CGRUPO22
		// GRUPO,t22.CGRUPO22
		// CDOCU,
		// t22.CGRUPO22
		// CFASE
		// ");
		query.append(" FROM AA21B20S01 t1");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07 ");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");
		query.append(" WHERE t1.CSERV20 = ?   AND t1.CEXPE20 = ?   AND t1.AEXPE20 = ?   AND t1.NEXPE20 = ?  ");

		Aa21b20s01DaoImpl.logger.info("SQL = " + query);
		Aa21b20s01DaoImpl.logger.info("PARAM1pk=" + aa21b20s01.getCserv20());
		Aa21b20s01DaoImpl.logger.info("PARAM2pk=" + aa21b20s01.getCexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM3pk=" + aa21b20s01.getAexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM4pk=" + aa21b20s01.getNexpe20());

		List<Aa21b20s01> aa21b20s01List = this.jdbcTemplate.query(
				query.toString(), this.rwMap, aa21b20s01.getCserv20(),
				aa21b20s01.getCexpe20(), aa21b20s01.getAexpe20(),
				aa21b20s01.getNexpe20());
		return (Aa21b20s01) DataAccessUtils.uniqueResult(aa21b20s01List);
	}

	/**
	 * Finds a single row in the Aa21b20s01 table.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @return Aa21b20s01
	 */
	@Transactional(readOnly = true)
	public Aa21b20s01 findConGrupoTipoExp(Aa21b20s01 aa21b20s01) {
		Aa21b20s01DaoImpl.logger.info("find");

		StringBuilder query = new StringBuilder(
				"SELECT t1.CSERV20 CSERV20, t1.CEXPE20 CEXPE20, t1.AEXPE20 AEXPE20, t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,"
						+ " t1.FAPER20 FAPER20, t1.OBJETO20 OBJETO20, t1.BLOQ20 BLOQ20, t1.FCIER20 FCIER20, t1.REFER20 REFER20, t1.CRESPO20 CRESPO20, t1.CPAIS20 CPAIS20, t1.CPROV20 CPROV20, t1.CMUNI20 CMUNI20,"
						+ " t1.OBSER20 OBSER20, t1.MONEDA20 MONEDA20, t1.EIMPOR20 EIMPOR20, t1.PIMPOR20 PIMPOR20, t1.EIMPORA20 EIMPORA20, t1.PIMPORA20 PIMPORA20, t1.CDEPAR20 CDEPAR20, t1.CARCHI20 CARCHI20,"
						+ " t1.UBICA20 UBICA20, t1.CESTA20 CESTA20, t1.USUB20 USUB20, t1.FVTO20 FVTO20, t1.EXPANT20 EXPANT20, t07.DESTA07 DESTA, t07.EESTA07 EESTA, null GRUPO, NULL CDOCU,NULL CFASE, t30.CODGRUPO30 GRUPOTIPOEXP");
		query.append(" FROM AA21B20S01 t1");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07 ");

		query.append(" LEFT JOIN AA21B30S01 t30");
		query.append(" ON t1.CEXPE20    = t30.CTIPOE30 ");

		query.append(" WHERE t1.CSERV20 = ?   AND t1.CEXPE20 = ?   AND t1.AEXPE20 = ?   AND t1.NEXPE20 = ?  ");

		Aa21b20s01DaoImpl.logger.info("SQL = " + query);
		Aa21b20s01DaoImpl.logger.info("PARAM1pk=" + aa21b20s01.getCserv20());
		Aa21b20s01DaoImpl.logger.info("PARAM2pk=" + aa21b20s01.getCexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM3pk=" + aa21b20s01.getAexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM4pk=" + aa21b20s01.getNexpe20());

		List<Aa21b20s01> aa21b20s01List = this.jdbcTemplate.query(
				query.toString(), this.rwMapConGrupoTipoExp,
				aa21b20s01.getCserv20(), aa21b20s01.getCexpe20(),
				aa21b20s01.getAexpe20(), aa21b20s01.getNexpe20());
		return (Aa21b20s01) DataAccessUtils.uniqueResult(aa21b20s01List);
	}

	/**
	 * Removes a single row in the Aa21b20s01 table.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @return
	 */
	public void remove(Aa21b20s01 aa21b20s01) {
		Aa21b20s01DaoImpl.logger.info("remove");
		String query = "DELETE FROM AA21B20S01 WHERE CSERV20=? AND CEXPE20=? AND AEXPE20=? AND NEXPE20=?";

		Aa21b20s01DaoImpl.logger.info("SQL = " + query);
		Aa21b20s01DaoImpl.logger.info("PARAM1pk=" + aa21b20s01.getCserv20());
		Aa21b20s01DaoImpl.logger.info("PARAM2pk=" + aa21b20s01.getCexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM3pk=" + aa21b20s01.getAexpe20());
		Aa21b20s01DaoImpl.logger.info("PARAM4pk=" + aa21b20s01.getNexpe20());

		this.jdbcTemplate.update(query, aa21b20s01.getCserv20(),
				aa21b20s01.getCexpe20(), aa21b20s01.getAexpe20(),
				aa21b20s01.getNexpe20());
	}

	/**
	 * Finds a list of rows in the Aa21b20s01 table.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @return List<Aa21b20s01>
	 */
	@Transactional(readOnly = true)
	public List<Aa21b20s01> findAll(Aa21b20s01 aa21b20s01,
			JQGridRequestDto jqGridRequestDto) {
		Aa21b20s01DaoImpl.logger.info("findAll");
		StringBuilder query = new StringBuilder(
				"SELECT  t1.CSERV20 CSERV20,t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.REFER20 REFER20,t1.CRESPO20 CRESPO20,t1.CPAIS20 CPAIS20,t1.CPROV20 CPROV20,t1.CMUNI20 CMUNI20,t1.OBSER20 OBSER20,t1.MONEDA20 MONEDA20,t1.EIMPOR20 EIMPOR20,t1.PIMPOR20 PIMPOR20,t1.EIMPORA20 EIMPORA20,t1.PIMPORA20 PIMPORA20,t1.CDEPAR20 CDEPAR20,t1.CARCHI20 CARCHI20,t1.UBICA20 UBICA20,t1.CESTA20 CESTA20,t1.USUB20 USUB20,t1.FVTO20 FVTO20, t1.EXPANT EXPANT ");
		query.append(", t07.DESTA07 DESTA, t07.EESTA07 EESTA ");
		query.append(", NULL GRUPO ");
		query.append(", NULL CDOCU ");
		query.append(", NULL CFASE ");
		query.append("FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");

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

		// 20161020 INI - INIGO
		if (aa21b20s01.getServicioLogueado() != null
				&& aa21b20s01.getServicioLogueado().length() > 0) {
			where.append(" AND CSERV20='")
					.append(aa21b20s01.getServicioLogueado()).append("' ");
		}
		// 20161020 FIN - INIGO

		where.append(mapaWhere.get("query"));
		query.append(where);

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

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

		Aa21b20s01DaoImpl.logger.info("findAll");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMS" + params);

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

	/**
	 * Finds rows in the Aa21b20s01 table using like.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @param startsWith
	 *            Boolean
	 * @return List<Aa21b20s01>
	 */
	@Transactional(readOnly = true)
	public List<Aa21b20s01> findAllLike(Aa21b20s01 aa21b20s01,
			JQGridRequestDto jqGridRequestDto, Boolean startsWith) {
		Aa21b20s01DaoImpl.logger.info("findAllLike");
		StringBuilder query = new StringBuilder(
				"SELECT  t1.CSERV20 CSERV20,t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.REFER20 REFER20,t1.CRESPO20 CRESPO20,t1.CPAIS20 CPAIS20,t1.CPROV20 CPROV20,t1.CMUNI20 CMUNI20,t1.OBSER20 OBSER20,t1.MONEDA20 MONEDA20,t1.EIMPOR20 EIMPOR20,t1.PIMPOR20 PIMPOR20,t1.EIMPORA20 EIMPORA20,t1.PIMPORA20 PIMPORA20,t1.CDEPAR20 CDEPAR20,t1.CARCHI20 CARCHI20,t1.UBICA20 UBICA20,t1.CESTA20 CESTA20,t1.USUB20 USUB20,t1.FVTO20 FVTO20, t1.EXPANT20 EXPANT20 ");
		query.append(", t07.DESTA07 DESTA, t07.EESTA07 EESTA ");
		query.append(", NULL GRUPO ");
		query.append(", NULL CDOCU ");
		query.append(", NULL CFASE ");
		query.append(" FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07");
		if (aa21b20s01.getCdocu() != null && !aa21b20s01.getCdocu().equals("")) {
			query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		}
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");
		query.append(" LEFT JOIN AA21B02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02 ");

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

		// 20161018 INI - INIGO
		if (aa21b20s01.getAsociadosTemp() != null
				&& aa21b20s01.getAsociadosTemp().length() > 0) {
			where.append(" AND (CSERV20, CEXPE20, AEXPE20, NEXPE20) not in (")
					.append(aa21b20s01.getAsociadosTemp()).append(") ");
		}
		// 20161018 FIN - INIGO

		// 20161020 INI - INIGO
		if (aa21b20s01.getServicioLogueado() != null
				&& aa21b20s01.getServicioLogueado().length() > 0) {
			where.append(" AND CSERV20='")
					.append(aa21b20s01.getServicioLogueado()).append("' ");
		}
		// 20161020 FIN - INIGO

		where.append(mapaWhere.get("query"));
		query.append(where);

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

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

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

	/**
	 * Finds rows in the Aa21b20s01 table using like.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @param startsWith
	 *            Boolean
	 * @return List<Aa21b20s01>
	 */
	@Transactional(readOnly = true)
	public List<Aa21b20s01> findAllLikeConFase(Aa21b20s01 aa21b20s01,
			JQGridRequestDto jqGridRequestDto, Boolean startsWith) {
		Aa21b20s01DaoImpl.logger.info("findAllLike");
		StringBuilder query = new StringBuilder(
				"SELECT  t1.CSERV20 CSERV20,t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.REFER20 REFER20,t1.CRESPO20 CRESPO20,t1.CPAIS20 CPAIS20,t1.CPROV20 CPROV20,t1.CMUNI20 CMUNI20,t1.OBSER20 OBSER20,t1.MONEDA20 MONEDA20,t1.EIMPOR20 EIMPOR20,t1.PIMPOR20 PIMPOR20,t1.EIMPORA20 EIMPORA20,t1.PIMPORA20 PIMPORA20,t1.CDEPAR20 CDEPAR20,t1.CARCHI20 CARCHI20,t1.UBICA20 UBICA20,t1.CESTA20 CESTA20,t1.USUB20 USUB20,t1.FVTO20 FVTO20, t1.EXPANT20 EXPANT20 ");
		query.append(", t07.DESTA07 DESTA, t07.EESTA07 EESTA ");
		query.append(", NULL GRUPO ");
		query.append(", NULL CDOCU ");
		query.append(", NULL CFASE ");
		query.append(" FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07");
		query.append(" LEFT JOIN AA21B02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		query.append(" LEFT JOIN AA21B21S01 t21 ON t1.CSERV20 = t21.CSERV21 AND t1.CEXPE20 = t21.CEXPE21 AND t1.NEXPE20 = t21.NEXPE21 AND t1.AEXPE20 = t21.AEXPE21 ");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");

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

		// 20161020 INI - INIGO
		if (aa21b20s01.getServicioLogueado() != null
				&& aa21b20s01.getServicioLogueado().length() > 0) {
			where.append(" AND CSERV20='")
					.append(aa21b20s01.getServicioLogueado()).append("' ");
		}
		// 20161020 FIN - INIGO

		where.append(mapaWhere.get("query"));
		query.append(where);

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

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

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

	/*
	 * OPERACIONES RUP_TABLE
	 */

	/**
	 * Counts rows in the Aa21b20s01 table.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllCount(Aa21b20s01 aa21b20s01) {

		Aa21b20s01DaoImpl.logger.info("findAllCount");
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM AA21B20S01 t1 ");

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

		// 20161020 INI - INIGO
		if (aa21b20s01.getServicioLogueado() != null
				&& aa21b20s01.getServicioLogueado().length() > 0) {
			where.append(" AND CSERV20='")
					.append(aa21b20s01.getServicioLogueado()).append("' ");
		}
		// 20161020 FIN - INIGO

		where.append(mapaWhere.get("query"));
		query.append(where);

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

		Aa21b20s01DaoImpl.logger.info("findAllCount");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMS" + params);

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

	/**
	 * Counts rows in the Aa21b20s01 table using like.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @param startsWith
	 *            Boolean
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllLikeCount(Aa21b20s01 aa21b20s01, Boolean startsWith) {

		Aa21b20s01DaoImpl.logger.info("findAllLikeCount");
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07");
		if (aa21b20s01.getCdocu() != null && !aa21b20s01.getCdocu().equals("")) {
			query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		}
		query.append(" LEFT JOIN AA21b02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		// query.append(" LEFT JOIN AA21b22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");

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

		// 20161018 INI - INIGO
		if (aa21b20s01.getAsociadosTemp() != null
				&& aa21b20s01.getAsociadosTemp().length() > 0) {
			where.append(" AND (CSERV20, CEXPE20, AEXPE20, NEXPE20) not in (")
					.append(aa21b20s01.getAsociadosTemp()).append(") ");
		}
		// 20161018 FIN - INIGO

		// 20161020 INI - INIGO
		if (aa21b20s01.getServicioLogueado() != null
				&& aa21b20s01.getServicioLogueado().length() > 0) {
			where.append(" AND CSERV20='")
					.append(aa21b20s01.getServicioLogueado()).append("' ");
		}
		// 20161020 FIN - INIGO

		where.append(mapaWhere.get("query"));
		query.append(where);

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

		Aa21b20s01DaoImpl.logger.info("findAllLikeCount");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMS" + params);

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

	/**
	 * Counts rows in the Aa21b20s01 table using like.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @param startsWith
	 *            Boolean
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllLikeCountCopiaDocFaseExp(Aa21b20s01 aa21b20s01,
			Boolean startsWith) {

		Aa21b20s01DaoImpl.logger.info("findAllLikeCount");
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM AA21B20S01 t1 ");
		if (aa21b20s01.getCdocu() != null && !aa21b20s01.getCdocu().equals("")) {
			query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		}
		query.append(" LEFT JOIN AA21b02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		// query.append(" LEFT JOIN AA21b22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");

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

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

		Aa21b20s01DaoImpl.logger.info("findAllLikeCount");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMS" + params);

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

	/**
	 * Counts rows in the Aa21b20s01 table using like.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @param listaServicios
	 *            List<Aa21b13s01>
	 * @param listaTiposExpedientes
	 *            List<Aa21b02s01>
	 * @param listaResponsables
	 *            List<Aa21b15s01>
	 * @param listaArchivos
	 *            List<Aa21b07s01>
	 * @param listaEstados
	 *            List<Aa21b07s01>
	 * @param startsWith
	 *            Boolean
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllLikeCountMultiples(Aa21b20s01 aa21b20s01,
			List<Aa21b13s01> listaServicios,
			List<Aa21b02s01> listaTiposExpedientes,
			List<Aa21b15s01> listaResponsables, List<Aa21b14s01> listaArchivos,
			List<Aa21b07s01> listaEstados, Boolean startsWith) {

		Aa21b20s01DaoImpl.logger.info("findAllLikeCount");
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM AA21B20S01 t1 ");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		query.append(" LEFT JOIN AA21b02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		// query.append(" LEFT JOIN AA21b22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");

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

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

		Aa21b20s01DaoImpl.logger.info("findAllLikeCount");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMS" + params);

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

	/**
	 * Counts rows in the Aa21b20s01 table using like.
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @param startsWith
	 *            Boolean
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllLikeCountConFase(Aa21b20s01 aa21b20s01,
			Boolean startsWith) {

		Aa21b20s01DaoImpl.logger.info("findAllLikeCountConFase");
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B21S01 t21 ON t1.CSERV20 = t21.CSERV21 AND t1.CEXPE20 = t21.CEXPE21 AND t1.NEXPE20 = t21.NEXPE21 AND t1.AEXPE20 = t21.AEXPE21 ");
		query.append(" LEFT JOIN AA21b02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");

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

		// 20161020 INI - INIGO
		if (aa21b20s01.getServicioLogueado() != null
				&& aa21b20s01.getServicioLogueado().length() > 0) {
			where.append(" AND CSERV20='")
					.append(aa21b20s01.getServicioLogueado()).append("' ");
		}
		// 20161020 FIN - INIGO

		where.append(mapaWhere.get("query"));
		query.append(where);

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

		Aa21b20s01DaoImpl.logger.info("findAllLikeCount");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMS" + params);

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

	/**
	 * Reorder the data list of Aa21b20s01 selected for rup_table
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @param startsWith
	 *            Boolean
	 * @return List<TableRowDto<Aa21b20s01>>
	 */
	@Override
	public List<TableRowDto<Aa21b20s01>> reorderSelection(
			Aa21b20s01 aa21b20s01, JQGridRequestDto jqGridRequestDto,
			Boolean startsWith) {

		Aa21b20s01DaoImpl.logger.info("reorderSelection");
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT  t1.CSERV20 CSERV20,t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.REFER20 REFER20,t1.CRESPO20 CRESPO20,t1.CPAIS20 CPAIS20,t1.CPROV20 CPROV20,t1.CMUNI20 CMUNI20,t1.OBSER20 OBSER20,t1.MONEDA20 MONEDA20,t1.EIMPOR20 EIMPOR20,t1.PIMPOR20 PIMPOR20,t1.EIMPORA20 EIMPORA20,t1.PIMPORA20 PIMPORA20,t1.CDEPAR20 CDEPAR20,t1.CARCHI20 CARCHI20,t1.UBICA20 UBICA20,t1.CESTA20 CESTA20,t1.USUB20 USUB20,t1.FVTO20 FVTO20 ");
		query.append(", t07.DESTA07 DESTA, t07.EESTA07 EESTA ");
		query.append(", NULL GRUPO ");
		query.append(", NULL CDOCU ");
		query.append(", NULL CFASE ");
		query.append(" FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07");
		query.append(" LEFT JOIN AA21B02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");
		// FILTRADO
		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(aa21b20s01, 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, Aa21b20s01.class, filterParamList,
				"CSERV20,CEXPE20,AEXPE20,NEXPE20");

		Aa21b20s01DaoImpl.logger.info("reorderSelection");
		Aa21b20s01DaoImpl.logger.info("SQL = "
				+ sbReorderSelectionSQL.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMs=" + filterParamList);

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

	/**
	 * Reorder the data list of Aa21b20s01 selected for rup_table
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @param startsWith
	 *            Boolean
	 * @return List<TableRowDto<Aa21b20s01>>
	 */
	@Override
	public List<TableRowDto<Aa21b20s01>> reorderSelectionConFase(
			Aa21b20s01 aa21b20s01, JQGridRequestDto jqGridRequestDto,
			Boolean startsWith) {

		Aa21b20s01DaoImpl.logger.info("reorderSelection");
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT  t1.CSERV20 CSERV20,t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.REFER20 REFER20,t1.CRESPO20 CRESPO20,t1.CPAIS20 CPAIS20,t1.CPROV20 CPROV20,t1.CMUNI20 CMUNI20,t1.OBSER20 OBSER20,t1.MONEDA20 MONEDA20,t1.EIMPOR20 EIMPOR20,t1.PIMPOR20 PIMPOR20,t1.EIMPORA20 EIMPORA20,t1.PIMPORA20 PIMPORA20,t1.CDEPAR20 CDEPAR20,t1.CARCHI20 CARCHI20,t1.UBICA20 UBICA20,t1.CESTA20 CESTA20,t1.USUB20 USUB20,t1.FVTO20 FVTO20 ");
		query.append(", t07.DESTA07 DESTA, t07.EESTA07 EESTA ");
		query.append(", NULL GRUPO ");
		query.append(",NULL CDOCU ");
		query.append(", t21.CFASE21 CFASE ");
		query.append(" FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07");
		query.append(" LEFT JOIN AA21B02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		query.append(" LEFT JOIN AA21B21S01 t21 ON t1.CSERV20 = t21.CSERV21 AND t1.CEXPE20 = t21.CEXPE21 AND t1.NEXPE20 = t21.NEXPE21 AND t1.AEXPE20 = t21.AEXPE21 ");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");
		// FILTRADO
		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(aa21b20s01, 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, Aa21b20s01.class, filterParamList,
				"CSERV20,CEXPE20,AEXPE20,NEXPE20");

		Aa21b20s01DaoImpl.logger.info("reorderSelection");
		Aa21b20s01DaoImpl.logger.info("SQL = "
				+ sbReorderSelectionSQL.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMs=" + filterParamList);

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

	/**
	 * Search method for rup_table
	 * 
	 * @param filterParams
	 *            Aa21b20s01
	 * @param searchParams
	 *            Aa21b20s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @param startsWith
	 *            Boolean
	 * @return List<TableRowDto<Aa21b20s01>>
	 */
	@Override
	public List<TableRowDto<Aa21b20s01>> search(Aa21b20s01 filterParams,
			Aa21b20s01 searchParams, JQGridRequestDto jqGridRequestDto,
			Boolean startsWith) {

		Aa21b20s01DaoImpl.logger.info("search");
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT  t1.CSERV20 CSERV20,t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.REFER20 REFER20,t1.CRESPO20 CRESPO20,t1.CPAIS20 CPAIS20,t1.CPROV20 CPROV20,t1.CMUNI20 CMUNI20,t1.OBSER20 OBSER20,t1.MONEDA20 MONEDA20,t1.EIMPOR20 EIMPOR20,t1.PIMPOR20 PIMPOR20,t1.EIMPORA20 EIMPORA20,t1.PIMPORA20 PIMPORA20,t1.CDEPAR20 CDEPAR20,t1.CARCHI20 CARCHI20,t1.UBICA20 UBICA20,t1.CESTA20 CESTA20,t1.USUB20 USUB20,t1.FVTO20 FVTO20 ");
		query.append(", t07.DESTA07 DESTA, t07.EESTA07 EESTA ");
		query.append(", NULL GRUPO ");
		query.append(", NULL CDOCU ");
		query.append(", NULL CFASE ");
		query.append(" FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07");
		query.append(" LEFT JOIN AA21B02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		query.append(" LEFT JOIN AA21B21S01 t21 ON t1.CSERV20 = t21.CSERV21 AND t1.CEXPE20 = t21.CEXPE21 AND t1.NEXPE20 = t21.NEXPE21 AND t1.AEXPE20 = t21.AEXPE21 ");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");
		// 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, Aa21b20s01.class, filterParamList,
				searchSQL, searchParamList, from_alias,
				"CSERV20,CEXPE20,AEXPE20,NEXPE20");

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

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

	/**
	 * Remove multiple method for rup_table
	 * 
	 * @param filteraa21b20s01
	 *            Aa21b20s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @param startsWith
	 *            Boolean
	 */
	@Override
	public void removeMultiple(Aa21b20s01 filteraa21b20s01,
			JQGridRequestDto jqGridRequestDto, Boolean startsWith) {

		Aa21b20s01DaoImpl.logger.info("removeMultiple");
		// SELECT
		/** TODO: select por clave */
		StringBuilder query = new StringBuilder(
				"SELECT  t1.CSERV20 CSERV20,t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.REFER20 REFER20,t1.CRESPO20 CRESPO20,t1.CPAIS20 CPAIS20,t1.CPROV20 CPROV20,t1.CMUNI20 CMUNI20,t1.OBSER20 OBSER20,t1.MONEDA20 MONEDA20,t1.EIMPOR20 EIMPOR20,t1.PIMPOR20 PIMPOR20,t1.EIMPORA20 EIMPORA20,t1.PIMPORA20 PIMPORA20,t1.CDEPAR20 CDEPAR20,t1.CARCHI20 CARCHI20,t1.UBICA20 UBICA20,t1.CESTA20 CESTA20,t1.USUB20 USUB20,t1.FVTO20 FVTO20 ");
		query.append(", t07.DESTA07 DESTA, t07.EESTA07 EESTA ");
		query.append(", NULL GRUPO ");
		query.append(", NULL CDOCU ");
		query.append(", NULL CFASE ");
		query.append(" FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07");
		query.append(" LEFT JOIN AA21B02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		query.append(" LEFT JOIN AA21B21S01 t21 ON t1.CSERV20 = t21.CSERV21 AND t1.CEXPE20 = t21.CEXPE21 AND t1.NEXPE20 = t21.NEXPE21 AND t1.AEXPE20 = t21.AEXPE21 ");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");

		// WHERE
		Map<String, ?> mapaWhere = this.getWhereLikeMap(filteraa21b20s01,
				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, Aa21b20s01.class,
						query, params, "CSERV20,CEXPE20,AEXPE20,NEXPE20");

		Aa21b20s01DaoImpl.logger.info("removeMultiple");
		Aa21b20s01DaoImpl.logger
				.info("SQL = " + sbRemoveMultipleSQL.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMs=" + params);
		this.jdbcTemplate.update(sbRemoveMultipleSQL.toString(),
				params.toArray());
	}

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

		Aa21b20s01DaoImpl.logger.info("findAllLikeJerarquia");
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT t1.CSERV20 CSERV20,t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20,t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.REFER20 REFER20,t1.CRESPO20 CRESPO20,t1.CPAIS20 CPAIS20,t1.CPROV20 CPROV20,t1.CMUNI20 CMUNI20,t1.OBSER20 OBSER20,t1.MONEDA20 MONEDA20,t1.EIMPOR20 EIMPOR20,t1.PIMPOR20 PIMPOR20,t1.EIMPORA20 EIMPORA20,t1.PIMPORA20 PIMPORA20,t1.CDEPAR20 CDEPAR20,t1.CARCHI20 CARCHI20,t1.UBICA20 UBICA20,t1.CESTA20 CESTA20,t1.USUB20 USUB20,t1.FVTO20 FVTO20, t1.EXPANT20 EXPANT20, ");
		query.append(" t07.DESTA07 DESTA, t07.EESTA07 EESTA ");
		query.append(", t22.CGRUPO22 GRUPO ");
		query.append(", t22.CGRUPO22 CDOCU ");
		query.append(", t22.CGRUPO22 CFASE ");
		// FROM
		// StringBuilder from = new StringBuilder("FROM AA21B20S01 t1 ");
		List<String> from = new ArrayList<String>();
		from.add("AA21B20S01");
		// TABLAS_ALIAS
		List<String> from_alias = new ArrayList<String>();
		from_alias.add(" t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07 ");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");

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

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

	/**
	 * Count rows in hierarchy
	 * 
	 * @param filterAa21b20s01
	 *            Aa21b20s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @return Long
	 */
	@Override
	public Long findAllLikeCountJerarquia(Aa21b20s01 filterAa21b20s01,
			JQGridRequestDto jqGridRequestDto) {

		Aa21b20s01DaoImpl.logger.info("findAllLikeCountJerarquia");
		// FROM
		// StringBuilder from = new StringBuilder("FROM AA21B20S01 t1 ");
		List<String> from = new ArrayList<String>();
		from.add("AA21B20S01");

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

		// JERARQUIA
		// Especificar la columna padre correspondiente en lugar de la generada
		// por defecto (ID_PADRE)
		StringBuilder query = JQGridManagerJerarquia.getQueryCount(
				jqGridRequestDto, mapaWhere, "CSERV20,CEXPE20,AEXPE20,NEXPE20",
				"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");
		Aa21b20s01DaoImpl.logger.info("findAllLikeJerarquia");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMs=" + params);
		return this.jdbcTemplate.queryForLong(query.toString(),
				params.toArray());
	}

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

		Aa21b20s01DaoImpl.logger.info("findAllChild");
		// FROM
		// StringBuilder from = new StringBuilder("FROM AA21B20S01 t1 ");
		List<String> from = new ArrayList<String>();
		from.add("AA21B20S01");

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

		// MULTISELECCION
		// Especificar la columna padre correspondiente en lugar de la generada
		// por defecto (ID_PADRE)
		StringBuilder query = JQGridManagerJerarquia.getQueryChildren(
				jqGridRequestDto, mapaWhere, "CSERV20,CEXPE20,AEXPE20,NEXPE20",
				"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");
		Aa21b20s01DaoImpl.logger.info("findAllChild");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMs=" + params);
		return this.jdbcTemplate.query(query.toString(),
				new RowNumResultSetExtractor<Aa21b20s01>(this.rwMapPK,
						"CSERV20,CEXPE20,AEXPE20,NEXPE20"), params.toArray());
	}

	/*
	 * MÉTODOS PRIVADOS
	 */

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

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

		if (aa21b20s01 != null && aa21b20s01.getCserv20() != null) {
			where.append(" AND t1.CSERV20 = ?");
			params.add(aa21b20s01.getCserv20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCexpe20() != null) {
			where.append(" AND t1.CEXPE20 = ?");
			params.add(aa21b20s01.getCexpe20());
		}
		if (aa21b20s01 != null && aa21b20s01.getAexpe20() != null) {
			where.append(" AND t1.AEXPE20 = ?");
			params.add(aa21b20s01.getAexpe20());
		}
		if (aa21b20s01 != null && aa21b20s01.getNexpe20() != null) {
			where.append(" AND t1.NEXPE20 = ?");
			params.add(aa21b20s01.getNexpe20());
		}
		if (aa21b20s01 != null && aa21b20s01.getFaper20() != null) {
			where.append(" AND t1.FAPER20 = ?");
			params.add(aa21b20s01.getFaper20());
		}
		if (aa21b20s01 != null && aa21b20s01.getObjeto20() != null) {
			where.append(" AND t1.OBJETO20 = ?");
			params.add(aa21b20s01.getObjeto20());
		}
		if (aa21b20s01 != null && aa21b20s01.getBloq20() != null) {
			where.append(" AND t1.BLOQ20 = ?");
			params.add(aa21b20s01.getBloq20());
		}
		if (aa21b20s01 != null && aa21b20s01.getFcier20() != null) {
			where.append(" AND t1.FCIER20 = ?");
			params.add(aa21b20s01.getFcier20());
		}
		if (aa21b20s01 != null && aa21b20s01.getRefer20() != null) {
			where.append(" AND t1.REFER20 = ?");
			params.add(aa21b20s01.getRefer20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCrespo20() != null) {
			where.append(" AND t1.CRESPO20 = ?");
			params.add(aa21b20s01.getCrespo20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCpais20() != null) {
			where.append(" AND t1.CPAIS20 = ?");
			params.add(aa21b20s01.getCpais20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCprov20() != null) {
			where.append(" AND t1.CPROV20 = ?");
			params.add(aa21b20s01.getCprov20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCmuni20() != null) {
			where.append(" AND t1.CMUNI20 = ?");
			params.add(aa21b20s01.getCmuni20());
		}
		if (aa21b20s01 != null && aa21b20s01.getObser20() != null) {
			where.append(" AND t1.OBSER20 = ?");
			params.add(aa21b20s01.getObser20());
		}
		if (aa21b20s01 != null && aa21b20s01.getMoneda20() != null) {
			where.append(" AND t1.MONEDA20 = ?");
			params.add(aa21b20s01.getMoneda20());
		}
		if (aa21b20s01 != null && aa21b20s01.getEimpor20() != null) {
			where.append(" AND t1.EIMPOR20 = ?");
			params.add(aa21b20s01.getEimpor20());
		}
		if (aa21b20s01 != null && aa21b20s01.getPimpor20() != null) {
			where.append(" AND t1.PIMPOR20 = ?");
			params.add(aa21b20s01.getPimpor20());
		}
		if (aa21b20s01 != null && aa21b20s01.getEimpora20() != null) {
			where.append(" AND t1.EIMPORA20 = ?");
			params.add(aa21b20s01.getEimpora20());
		}
		if (aa21b20s01 != null && aa21b20s01.getPimpora20() != null) {
			where.append(" AND t1.PIMPORA20 = ?");
			params.add(aa21b20s01.getPimpora20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCdepar20() != null) {
			where.append(" AND t1.CDEPAR20 = ?");
			params.add(aa21b20s01.getCdepar20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCarchi20() != null) {
			where.append(" AND t1.CARCHI20 = ?");
			params.add(aa21b20s01.getCarchi20());
		}
		if (aa21b20s01 != null && aa21b20s01.getUbica20() != null) {
			where.append(" AND t1.UBICA20 = ?");
			params.add(aa21b20s01.getUbica20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCesta20() != null) {
			where.append(" AND t1.CESTA20 = ?");
			params.add(aa21b20s01.getCesta20());
		}
		if (aa21b20s01 != null && aa21b20s01.getUsub20() != null) {
			where.append(" AND t1.USUB20 = ?");
			params.add(aa21b20s01.getUsub20());
		}
		if (aa21b20s01 != null && aa21b20s01.getFvto20() != null) {
			where.append(" AND t1.FVTO20 = ?");
			params.add(aa21b20s01.getFvto20());
		}

		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 Aa21b20s01 entity
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01 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(Aa21b20s01 aa21b20s01,
			Boolean startsWith) {

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

		if (aa21b20s01 != null && aa21b20s01.getRefExp() != null) {
			where.append(" AND t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = ?");
			params.add(aa21b20s01.getRefExp());
		}

		if (aa21b20s01 != null && aa21b20s01.getCserv20() != null) {
			where.append(" AND t1.CSERV20 = ?");
			params.add(aa21b20s01.getCserv20());
		}

		if (aa21b20s01 != null && aa21b20s01.getGrupo() != null) {
			where.append(" AND t22.CGRUPO22 = ?");
			params.add(aa21b20s01.getGrupo());
		}

		if (aa21b20s01 != null && aa21b20s01.getCdocu() != null) {
			where.append(" AND t25.CDOCU25 = ?");
			params.add(aa21b20s01.getCdocu());
		}

		if (aa21b20s01 != null && aa21b20s01.getCfase() != null) {
			where.append(" AND t21.CFASE21 = ?");
			params.add(aa21b20s01.getCfase());
		}

		if (aa21b20s01 != null && aa21b20s01.getCexpe20() != null) {
			where.append(" AND t1.CEXPE20 = ? ");
			params.add(aa21b20s01.getCexpe20());
		}
		if (aa21b20s01 != null && aa21b20s01.getAexpe20() != null) {
			where.append(" AND t1.AEXPE20 = ?");
			params.add(aa21b20s01.getAexpe20());
		}
		if (aa21b20s01 != null && aa21b20s01.getNexpe20() != null) {
			where.append(" AND t1.NEXPE20 = ?");
			params.add(aa21b20s01.getNexpe20());
		}

		if (aa21b20s01 != null && aa21b20s01.getCtrami20() != null) {
			where.append(" AND t2.CTRAMI02 = ?");
			params.add(aa21b20s01.getCtrami20());
		}
		if (aa21b20s01 != null
				&& ((aa21b20s01.getFaper20desde() != null || aa21b20s01
						.getFaper20hasta() != null) && (aa21b20s01
						.getFcier20desde() != null || aa21b20s01
						.getFcier20hasta() != null))) {

			where.append(" AND ((");

			if (aa21b20s01.getFaper20desde() != null
					&& aa21b20s01.getFaper20hasta() != null) {
				where.append(" t1.FAPER20 >= ?");
				params.add(aa21b20s01.getFaper20desde());
				where.append(" AND t1.FAPER20 <= ?");
				params.add(aa21b20s01.getFaper20hasta());
			} else if (aa21b20s01.getFaper20desde() != null) {
				where.append(" t1.FAPER20 >= ?");
				params.add(aa21b20s01.getFaper20desde());
			} else if (aa21b20s01.getFaper20hasta() != null) {
				where.append(" t1.FAPER20 <= ?");
				params.add(aa21b20s01.getFaper20hasta());
			}

			where.append(") OR (");

			if (aa21b20s01.getFcier20desde() != null
					&& aa21b20s01.getFcier20hasta() != null) {
				where.append(" t1.FCIER20 >= ?");
				params.add(aa21b20s01.getFcier20desde());
				where.append(" AND t1.FCIER20 <= ?");
				params.add(aa21b20s01.getFcier20hasta());
			} else if (aa21b20s01.getFcier20desde() != null) {
				where.append(" t1.FCIER20 >= ?");
				params.add(aa21b20s01.getFcier20desde());
			} else if (aa21b20s01.getFcier20hasta() != null) {
				where.append(" t1.FCIER20 <= ?");
				params.add(aa21b20s01.getFcier20hasta());
			}

			where.append("))");
		} else {
			if (aa21b20s01 != null && aa21b20s01.getFaper20desde() != null) {
				where.append(" AND t1.FAPER20 >= ?");
				params.add(aa21b20s01.getFaper20desde());
			}
			if (aa21b20s01 != null && aa21b20s01.getFaper20hasta() != null) {
				where.append(" AND t1.FAPER20 <= ?");
				params.add(aa21b20s01.getFaper20hasta());
			}
			if (aa21b20s01 != null && aa21b20s01.getFcier20desde() != null) {
				where.append(" AND t1.FCIER20 >= ?");
				params.add(aa21b20s01.getFcier20desde());
			}
			if (aa21b20s01 != null && aa21b20s01.getFcier20hasta() != null) {
				where.append(" AND t1.FCIER20 <= ?");
				params.add(aa21b20s01.getFcier20hasta());
			}
		}

		if (aa21b20s01 != null && aa21b20s01.getObjeto20() != null) {
			where.append(" AND UPPER(t1.OBJETO20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getObjeto20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getObjeto20().toUpperCase() + "%");
			}
			where.append(" AND t1.OBJETO20 IS NOT NULL");
		}
		if (aa21b20s01 != null && aa21b20s01.getBloq20() != null) {
			where.append(" AND t1.BLOQ20 = ?");
			params.add(aa21b20s01.getBloq20());
		}

		if (aa21b20s01 != null && aa21b20s01.getRefer20() != null) {
			where.append(" AND UPPER(t1.REFER20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getRefer20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getRefer20().toUpperCase() + "%");
			}
			where.append(" AND t1.REFER20 IS NOT NULL");
		}
		if (aa21b20s01 != null && aa21b20s01.getCrespo20() != null) {
			where.append(" AND t1.CRESPO20 = ?");
			params.add(aa21b20s01.getCrespo20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCpais20() != null) {
			where.append(" AND t1.CPAIS20 = ? ");
			params.add(aa21b20s01.getCpais20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCprov20() != null) {
			where.append(" AND t1.CPROV20 = ? ");
			params.add(aa21b20s01.getCprov20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCmuni20() != null) {
			where.append(" AND t1.CMUNI20 = ? ");
			params.add(aa21b20s01.getCmuni20());
		}
		if (aa21b20s01 != null && aa21b20s01.getObser20() != null) {
			where.append(" AND UPPER(t1.OBSER20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getObser20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getObser20().toUpperCase() + "%");
			}
			where.append(" AND t1.OBSER20 IS NOT NULL");
		}
		if (aa21b20s01 != null && aa21b20s01.getMoneda20() != null) {
			where.append(" AND UPPER(t1.MONEDA20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getMoneda20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getMoneda20().toUpperCase() + "%");
			}
			where.append(" AND t1.MONEDA20 IS NOT NULL");
		}
		if (aa21b20s01 != null && aa21b20s01.getEimpor20() != null) {
			where.append(" AND t1.EIMPOR20 = ?");
			params.add(aa21b20s01.getEimpor20());
		}
		if (aa21b20s01 != null && aa21b20s01.getPimpor20() != null) {
			where.append(" AND t1.PIMPOR20 = ?");
			params.add(aa21b20s01.getPimpor20());
		}
		if (aa21b20s01 != null && aa21b20s01.getEimpora20() != null) {
			where.append(" AND t1.EIMPORA20 = ?");
			params.add(aa21b20s01.getEimpora20());
		}
		if (aa21b20s01 != null && aa21b20s01.getPimpora20() != null) {
			where.append(" AND t1.PIMPORA20 = ?");
			params.add(aa21b20s01.getPimpora20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCdepar20() != null) {
			where.append(" AND t1.CDEPAR20 = ?");
			params.add(aa21b20s01.getCdepar20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCarchi20() != null) {
			where.append(" AND t1.CARCHI20 = ? ");
			params.add(aa21b20s01.getCarchi20());
		}
		if (aa21b20s01 != null && aa21b20s01.getUbica20() != null) {
			where.append(" AND UPPER(t1.UBICA20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getUbica20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getUbica20().toUpperCase() + "%");
			}
			where.append(" AND t1.UBICA20 IS NOT NULL");
		}
		if (aa21b20s01 != null && aa21b20s01.getCesta20() != null) {
			where.append(" AND t1.CESTA20 = ? ");
			params.add(aa21b20s01.getCesta20());
		}
		if (aa21b20s01 != null && aa21b20s01.getUsub20() != null) {
			where.append(" AND t1.USUB20 = ? ");
			params.add(aa21b20s01.getUsub20());
		}
		if (aa21b20s01 != null && aa21b20s01.getFvto20desde() != null) {
			where.append(" AND t1.FVTO20 >= ?");
			params.add(aa21b20s01.getFvto20desde());
		}

		if (aa21b20s01 != null && aa21b20s01.getFvto20hasta() != null) {
			where.append(" AND t1.FVTO20 <= ?");
			params.add(aa21b20s01.getFvto20hasta());
		}

		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;

	/**
	 * Finds rows in the Expedientes table using like.
	 * 
	 * 
	 * @return Integer
	 */

	@Override
	public List<Aa21b20s01> findExpAsociados(String experel) {
		// TODO Auto-generated method stub
		Aa21b20s01DaoImpl.logger.info("findExpAsociados");

		// 20161115 - INIGO
		// PARA QUE NO SALGAN DUPLICADOS EN LA RELACIÓN DE EXPEDIENTES
		// ASOCIADOS, EN EL DETALLE DEL EXPEDIENTE (AÑADIDO EL DISTINCT).

		StringBuilder query = new StringBuilder(
				"SELECT DISTINCT t1.NEXPEREL22 REFEXP, t20.CSERV20 CSERV20, t20.CEXPE20 CEXPE20, t20.AEXPE20 AEXPE20, t20.NEXPE20 NEXPE20, t20.CESTA20 CESTA20, t20.OBJETO20 OBJETO20, t20.UBICA20 UBICA20 ");
		query.append("FROM AA21B22S01 t1 ");
		query.append(" LEFT JOIN AA21B20S01 t20 ON t1.NEXPEREL22 = t20.CEXPE20||'/'||trim(to_char(t20.NEXPE20,'000000'))||'/'||t20.AEXPE20");
		query.append(" WHERE t1.CGRUPO22 in (select cgrupo22 from AA21B22S01 where nexperel22=?) ");
		query.append(" ORDER BY CEXPE20, AEXPE20, NEXPE20");
		Aa21b20s01DaoImpl.logger.info("findExpAsociados");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAM1" + experel);

		return (List<Aa21b20s01>) this.jdbcTemplate.query(query.toString(),
				this.rwMapExpAsociado, experel);
	}

	/*
	 * @Override public void removeAsociados(ArrayList<String> listaDesagregar)
	 * { // TODO Auto-generated method stub
	 * 
	 * for (int i = 0; i < listaDesagregar.size(); i++) {
	 * Aa21b15s01DaoImpl.logger.info("removeAsignados"); String query =
	 * "UPDATE AA21B15S01 SET CSERV15=?  WHERE CUSU15=?";
	 * Aa21b15s01DaoImpl.logger.info("SQL = " + query);
	 * Aa21b15s01DaoImpl.logger.info("PARAM1= ");
	 * Aa21b15s01DaoImpl.logger.info("PARAM2pk=" + listaDesagregar.get(i));
	 * 
	 * this.jdbcTemplate.update(query, null, listaDesagregar.get(i)); }
	 * 
	 * }
	 */

	@Override
	public List<Aa21b20s01Consultas> findAllLikeConsultas(
			Aa21b20s01Consultas filterAa21b20s01,
			JQGridRequestDto jqGridRequestDto, List<Aa21b13s01> listaServicios,
			List<Aa21b02s01> listaTiposExpedientes,
			List<Aa21b15s01> listaResponsables, List<Aa21b14s01> listaArchivos,
			List<Aa21b07s01> listaEstados, Boolean startsWith) {

		Aa21b20s01DaoImpl.logger.info("findAllLikeConsultas");
		StringBuilder query = new StringBuilder(
				"SELECT  t1.CSERV20 CSERV20,t13.DSERV13 DSERV20, t13.ESERV13 ESERV20, ");
		query.append("t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20,t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,");
		query.append("t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.CRESPO20 CRESPO20, t15.DUSU15 DRESPO20,");
		query.append("t1.CPAIS20 CPAIS20, ");
		// query.append("t92.DS_O DPAIS20, ");
		query.append("t1.CPROV20 CPROV20, ");
		// query.append("t93.DS_O DPROV20,");
		query.append("t1.CMUNI20 CMUNI20, ");
		query.append("t94.DS_O DMUNI20, ");
		query.append("t1.OBSER20 OBSER20,t1.EIMPOR20 EIMPOR20,t1.EIMPORA20 EIMPORA20,");
		query.append("t1.CDEPAR20 CDEPAR20, ");
		query.append("t12.DDEPAR12 DDEPAR20, t12.EDEPAR12 EDEPAR20, ");
		query.append("t1.CARCHI20 CARCHI20, t14.DARCHI14 DARCHI20, t14.EARCHI14 EARCHI20,");
		query.append("t1.UBICA20 UBICA20,t1.EXPANT20 EXPANT20,t1.CESTA20 CESTA20, t7.DESTA07 DESTA20, t7.EESTA07 EESTA20, t1.USUB20 USUB20,t1.FVTO20 FVTO20 ");
		if (filterAa21b20s01.isConTareas()) {
			query.append(", sum( case when fprevi21 <trunc(sysdate) then 1 else 0 end )as caducadas ");
		}
		query.append(" FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B13S01 t13 ON t1.CSERV20 = t13.CSERV13");
		query.append(" LEFT JOIN AA21B15S01 t15 ON t1.CRESPO20 = t15.CUSU15");
		// query.append(" LEFT JOIN AA21B92S01 t92 ON t1.CPAIS20 = t92.ID");
		// query.append(" LEFT JOIN AA21B93S01 t93 ON t1.CPROV20 = t93.ID");
		query.append(" LEFT JOIN AA21B94S01 t94 ON t1.CMUNI20 = t94.ID AND t1.CPROV20 = t94.PROVINCIA_ID");
		query.append(" LEFT JOIN AA21B12S01 t12 ON t1.CDEPAR20 = to_char(t12.CDEPAR12)");
		query.append(" LEFT JOIN AA21B14S01 t14 ON t1.CARCHI20 = t14.CARCHI14");
		query.append(" LEFT JOIN AA21B07S01 t7 ON t1.CESTA20 = t7.CESTA07");
		query.append(" LEFT JOIN AA21B02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		if (filterAa21b20s01.isConTareas()) {
			query.append("  INNER JOIN AA21B21S01 t4");
			query.append(" ON t4.CSERV21   = t1.CSERV20");
			query.append(" AND t4.CEXPE21  = t1.CEXPE20");
			query.append(" AND t4.AEXPE21  = t1.AEXPE20");
			query.append(" AND t4.NEXPE21  = t1.NEXPE20");
		}
		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMapConsultas(
				filterAa21b20s01, listaServicios, listaTiposExpedientes,
				listaResponsables, listaArchivos, listaEstados, startsWith);
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);

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

		if (filterAa21b20s01.isConTareas()) {
			query.append(" GROUP BY");
			query.append(" t1.CSERV20 ,t13.DSERV13 , t13.ESERV13 , t1.CEXPE20 ,t1.AEXPE20 ,t1.NEXPE20 ,t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 ,  t1.FAPER20 ,");
			query.append(" t1.OBJETO20 ,t1.BLOQ20 ,t1.FCIER20 ,t1.CRESPO20 , t15.DUSU15 ,	t1.CPAIS20 , ");
			query.append(" t1.CPROV20 , t1.CMUNI20 , t94.DS_O , 	t1.OBSER20 ,t1.EIMPOR20 ,t1.EIMPORA20 ,	t1.CDEPAR20 ,t12.DDEPAR12 , t12.EDEPAR12 , ");
			query.append(" t1.CARCHI20 , t14.DARCHI14 , t14.EARCHI14 ,t1.UBICA20 ,t1.CESTA20 , t7.DESTA07 , t7.EESTA07 , t1.USUB20 ,t1.FVTO20, t1.EXPANT20  ");
		}
		if (jqGridRequestDto != null) {
			query = JQGridManager.getPaginationQuery(jqGridRequestDto, query);
		}
		Aa21b20s01DaoImpl.logger.info("findAllLikeConsultas");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMS" + params);
		return (List<Aa21b20s01Consultas>) this.jdbcTemplate.query(
				query.toString(), this.rwMapConsultas, params.toArray());
	}

	/**
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s01Consultas
	 * @param listaServicios
	 *            List<Aa21b13s01>
	 * @param listaTiposExpedientes
	 *            List<Aa21b02s01>
	 * @param listaResponsables
	 *            List<Aa21b15s01>
	 * @param listaArchivos
	 *            List<Aa21b14s01>
	 * @param listaEstados
	 *            List<Aa21b07s01>
	 * @param startsWith
	 *            boolean
	 * @return Map<String, ?>
	 */
	private Map<String, ?> getWhereLikeMapConsultas(
			Aa21b20s01Consultas aa21b20s01, List<Aa21b13s01> listaServicios,
			List<Aa21b02s01> listaTiposExpedientes,
			List<Aa21b15s01> listaResponsables, List<Aa21b14s01> listaArchivos,
			List<Aa21b07s01> listaEstados, boolean startsWith) {
		// TODO Auto-generated method stub

		StringBuilder where = new StringBuilder(
				Aa21b20s01DaoImpl.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.CSERV20 = ? ");
					params.add(serviciosAux.getCServ13());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

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

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

		if (listaResponsables != null) {
			if (listaResponsables.size() > 0) {
				where.append(" AND (");
				Aa21b15s01 responsablesAux = null;
				for (Iterator<Aa21b15s01> iterator = listaResponsables
						.iterator(); iterator.hasNext();) {

					responsablesAux = (Aa21b15s01) iterator.next();
					where.append(" t1.CRESPO20 = ? ");
					params.add(responsablesAux.getCusu15());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

		if (listaArchivos != null) {
			if (listaArchivos.size() > 0) {
				where.append(" AND (");
				Aa21b14s01 archivosAux = null;
				for (Iterator<Aa21b14s01> iterator = listaArchivos.iterator(); iterator
						.hasNext();) {

					archivosAux = (Aa21b14s01) iterator.next();
					where.append(" t1.CARCHI20 = ? ");
					params.add(archivosAux.getCarchi14());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

		if (listaEstados != null) {
			if (listaEstados.size() > 0) {
				where.append(" AND (");
				Aa21b07s01 estadosAux = null;
				for (Iterator<Aa21b07s01> iterator = listaEstados.iterator(); iterator
						.hasNext();) {

					estadosAux = (Aa21b07s01) iterator.next();
					where.append(" t1.CESTA20 = ? ");
					params.add(estadosAux.getCesta07());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

		if (aa21b20s01 != null && aa21b20s01.getRefExp() != null
				&& aa21b20s01.getRefExp().length() > 0) {
			where.append(" AND t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = ?");
			params.add(aa21b20s01.getRefExp());
		}

		// 20161020 - INI INIGO
		// if (aa21b20s01 != null && aa21b20s01.getGrupo() != null) {
		// where.append(" AND t22.CGRUPO22 = ?");
		// params.add(aa21b20s01.getGrupo());
		// }
		// 20161020 - FIN INIGO

		/*
		 * if (aa21b20s01 != null && aa21b20s01.getCdocu() != null) {
		 * where.append(" AND t25.CDOCU25 = ?");
		 * params.add(aa21b20s01.getCdocu()); }
		 */

		// 20161020 - INI INIGO
		// if (aa21b20s01 != null && aa21b20s01.getCfase() != null) {
		// where.append(" AND t21.CFASE21 = ?");
		// params.add(aa21b20s01.getCfase());
		// }
		// 20161020 - FIN INIGO

		if (aa21b20s01 != null && aa21b20s01.getAexpe20() != null) {
			where.append(" AND t1.AEXPE20 = ?");
			params.add(aa21b20s01.getAexpe20());
		}
		if (aa21b20s01 != null && aa21b20s01.getNexpe20() != null) {
			where.append(" AND t1.NEXPE20 = ?");
			params.add(aa21b20s01.getNexpe20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCtrami20() != null) {
			where.append(" AND t2.CTRAMI02 = ?");
			params.add(aa21b20s01.getCtrami20());
		}

		if (aa21b20s01 != null
				&& ((aa21b20s01.getFaper20desde() != null || aa21b20s01
						.getFaper20hasta() != null) && (aa21b20s01
						.getFcier20desde() != null || aa21b20s01
						.getFcier20hasta() != null))) {

			where.append(" AND ((");

			if (aa21b20s01.getFaper20desde() != null
					&& aa21b20s01.getFaper20hasta() != null) {
				where.append(" t1.FAPER20 >= ?");
				params.add(aa21b20s01.getFaper20desde());
				where.append(" AND t1.FAPER20 <= ?");
				params.add(aa21b20s01.getFaper20hasta());
			} else if (aa21b20s01.getFaper20desde() != null) {
				where.append(" t1.FAPER20 >= ?");
				params.add(aa21b20s01.getFaper20desde());
			} else if (aa21b20s01.getFaper20hasta() != null) {
				where.append(" t1.FAPER20 <= ?");
				params.add(aa21b20s01.getFaper20hasta());
			}

			where.append(") OR (");

			if (aa21b20s01.getFcier20desde() != null
					&& aa21b20s01.getFcier20hasta() != null) {
				where.append(" t1.FCIER20 >= ?");
				params.add(aa21b20s01.getFcier20desde());
				where.append(" AND t1.FCIER20 <= ?");
				params.add(aa21b20s01.getFcier20hasta());
			} else if (aa21b20s01.getFcier20desde() != null) {
				where.append(" t1.FCIER20 >= ?");
				params.add(aa21b20s01.getFcier20desde());
			} else if (aa21b20s01.getFcier20hasta() != null) {
				where.append(" t1.FCIER20 <= ?");
				params.add(aa21b20s01.getFcier20hasta());
			}

			where.append("))");
		} else {
			if (aa21b20s01 != null && aa21b20s01.getFaper20desde() != null) {
				where.append(" AND t1.FAPER20 >= ?");
				params.add(aa21b20s01.getFaper20desde());
			}
			if (aa21b20s01 != null && aa21b20s01.getFaper20hasta() != null) {
				where.append(" AND t1.FAPER20 <= ?");
				params.add(aa21b20s01.getFaper20hasta());
			}
			if (aa21b20s01 != null && aa21b20s01.getFcier20desde() != null) {
				where.append(" AND t1.FCIER20 >= ?");
				params.add(aa21b20s01.getFcier20desde());
			}
			if (aa21b20s01 != null && aa21b20s01.getFcier20hasta() != null) {
				where.append(" AND t1.FCIER20 <= ?");
				params.add(aa21b20s01.getFcier20hasta());
			}
		}

		if (aa21b20s01 != null && aa21b20s01.getObjeto20() != null) {
			where.append(" AND UPPER(t1.OBJETO20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getObjeto20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getObjeto20().toUpperCase() + "%");
			}
			where.append(" AND t1.OBJETO20 IS NOT NULL");
		}
		/*
		 * if (aa21b20s01 != null && aa21b20s01.getBloq20() != null) {
		 * where.append(" AND t1.BLOQ20 = ?");
		 * params.add(aa21b20s01.getBloq20()); }
		 */

		if (aa21b20s01 != null && aa21b20s01.getRefer20() != null
				&& aa21b20s01.getRefer20().length() > 0) {
			where.append(" AND UPPER(t1.REFER20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getRefer20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getRefer20().toUpperCase() + "%");
			}
			where.append(" AND t1.REFER20 IS NOT NULL");
		}

		if (aa21b20s01 != null && aa21b20s01.getCpais20() != null) {
			where.append(" AND t1.CPAIS20 = ? ");
			params.add(aa21b20s01.getCpais20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCprov20() != null) {
			where.append(" AND t1.CPROV20 = ? ");
			params.add(aa21b20s01.getCprov20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCmuni20() != null) {
			where.append(" AND t1.CMUNI20 = ? ");
			params.add(aa21b20s01.getCmuni20());
		}
		if (aa21b20s01 != null && aa21b20s01.getObser20() != null) {
			where.append(" AND UPPER(t1.OBSER20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getObser20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getObser20().toUpperCase() + "%");
			}
			where.append(" AND t1.OBSER20 IS NOT NULL");
		}

		// if (aa21b20s01 != null && aa21b20s01.getMoneda20() != null) {
		// where.append(" AND UPPER(t1.MONEDA20) like ? ESCAPE  '\\'");
		// if (startsWith) {
		// params.add(aa21b20s01.getMoneda20().toUpperCase() + "%");
		// } else {
		// params.add("%" + aa21b20s01.getMoneda20().toUpperCase() + "%");
		// }
		// where.append(" AND t1.MONEDA20 IS NOT NULL");
		// }

		if (aa21b20s01 != null && aa21b20s01.getEimpor20() != null) {
			where.append(" AND t1.EIMPOR20 = ?");
			params.add(aa21b20s01.getEimpor20());
		}

		// if (aa21b20s01 != null && aa21b20s01.getPimpor20() != null) {
		// where.append(" AND t1.PIMPOR20 = ?");
		// params.add(aa21b20s01.getPimpor20());
		// }

		if (aa21b20s01 != null && aa21b20s01.getEimpora20() != null) {
			where.append(" AND t1.EIMPORA20 = ?");
			params.add(aa21b20s01.getEimpora20());
		}

		// if (aa21b20s01 != null && aa21b20s01.getPimpora20() != null) {
		// where.append(" AND t1.PIMPORA20 = ?");
		// params.add(aa21b20s01.getPimpora20());
		// }

		if (aa21b20s01 != null && aa21b20s01.getCdepar20() != null) {
			where.append(" AND t1.CDEPAR20 = ?");
			params.add(aa21b20s01.getCdepar20());
		}

		if (aa21b20s01 != null && aa21b20s01.getUbica20() != null) {
			where.append(" AND UPPER(t1.UBICA20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getUbica20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getUbica20().toUpperCase() + "%");
			}
			where.append(" AND t1.UBICA20 IS NOT NULL");
		}

		if (aa21b20s01 != null && aa21b20s01.getUsub20() != null) {
			where.append(" AND t1.USUB20 = ? ");
			params.add(aa21b20s01.getUsub20());
		}
		if (aa21b20s01 != null && aa21b20s01.getFvto20desde() != null) {
			where.append(" AND t1.FVTO20 >= ?");
			params.add(aa21b20s01.getFvto20desde());
		}

		if (aa21b20s01 != null && aa21b20s01.getFvto20hasta() != null) {
			where.append(" AND t1.FVTO20 <= ?");
			params.add(aa21b20s01.getFvto20hasta());
		}
		if (aa21b20s01.isConTareas()) {
			where.append(" AND fprevi21 is not null 	and freal21 is  null");
		}
		Map<String, Object> mapWhere = new HashMap<String, Object>();
		mapWhere.put("query", where);
		mapWhere.put("params", params);

		return mapWhere;

	}

	@Override
	public Aa21b20s01 findPorReferencia(Aa21b20s01 expAsociado) {
		// TODO Auto-generated method stub
		Aa21b20s01DaoImpl.logger.info("findPorReferencia");

		StringBuilder query = new StringBuilder(
				"SELECT t1.CSERV20 CSERV20, t1.CEXPE20 CEXPE20, t1.AEXPE20 AEXPE20, t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP, t1.FAPER20 FAPER20, t1.OBJETO20 OBJETO20, t1.BLOQ20 BLOQ20, t1.FCIER20 FCIER20, t1.REFER20 REFER20, t1.CRESPO20 CRESPO20, t1.CPAIS20 CPAIS20, t1.CPROV20 CPROV20, t1.CMUNI20 CMUNI20, t1.OBSER20 OBSER20, t1.MONEDA20 MONEDA20, t1.EIMPOR20 EIMPOR20, t1.PIMPOR20 PIMPOR20, t1.EIMPORA20 EIMPORA20, t1.PIMPORA20 PIMPORA20, t1.CDEPAR20 CDEPAR20, t1.CARCHI20 CARCHI20, t1.UBICA20 UBICA20, t1.CESTA20 CESTA20, t1.USUB20 USUB20, t1.FVTO20 FVTO20, t1.EXPANT20 EXPANT20, t07.DESTA07 DESTA, t07.EESTA07 EESTA, NULL GRUPO,NULL CDOCU, NULL CFASE ");
		query.append(" FROM AA21B20S01 t1");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07 ");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");
		query.append(" WHERE t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = ?  ");

		Aa21b20s01DaoImpl.logger.info("SQL = " + query);
		Aa21b20s01DaoImpl.logger.info("PARAM1pk=" + expAsociado.getRefExp());

		List<Aa21b20s01> aa21b20s01List = this.jdbcTemplate.query(
				query.toString(), this.rwMap, expAsociado.getRefExp());
		return (Aa21b20s01) DataAccessUtils.uniqueResult(aa21b20s01List);
	}

	@Override
	public List<Aa21b20s01> findAllLikeMultiples(Aa21b20s01 filterAa21b20s01,
			JQGridRequestDto jqGridRequestDto, List<Aa21b13s01> listaServicios,
			List<Aa21b02s01> listaTiposExpedientes,
			List<Aa21b15s01> listaResponsables, List<Aa21b14s01> listaArchivos,
			List<Aa21b07s01> listaEstados, boolean startsWith) {

		// TODO Auto-generated method stub
		Aa21b20s01DaoImpl.logger.info("findAllLikeMultiples");
		StringBuilder query = new StringBuilder(
				"SELECT  t1.CSERV20 CSERV20,t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.REFER20 REFER20,t1.CRESPO20 CRESPO20,t1.CPAIS20 CPAIS20,t1.CPROV20 CPROV20,t1.CMUNI20 CMUNI20,t1.OBSER20 OBSER20,t1.MONEDA20 MONEDA20,t1.EIMPOR20 EIMPOR20,t1.PIMPOR20 PIMPOR20,t1.EIMPORA20 EIMPORA20,t1.PIMPORA20 PIMPORA20,t1.CDEPAR20 CDEPAR20,t1.CARCHI20 CARCHI20,t1.UBICA20 UBICA20,t1.CESTA20 CESTA20,t1.USUB20 USUB20,t1.FVTO20 FVTO20, t1.EXPANT20 EXPANT20 ");
		query.append(", t07.DESTA07 DESTA, t07.EESTA07 EESTA ");
		// query.append(", t22.CGRUPO22 GRUPO ");
		// query.append(", t22.CGRUPO22 CDOCU ");
		// query.append(", t22.CGRUPO22 CFASE ");
		query.append(", null GRUPO ");
		query.append(", null CDOCU ");
		query.append(", null CFASE ");
		if (filterAa21b20s01.isConTareas()) {
			query.append(", sum( case when fprevi21 <trunc(sysdate) then 1 else 0 end )as caducadas ");
		}
		query.append(" FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");
		query.append(" LEFT JOIN AA21B02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		if (filterAa21b20s01.isConTareas()) {
			// query.append(" inner join AA21B01S01 t3 on");
			// query.append(" t3.CSERV01    = t1.CSERV20");
			// query.append(" AND t3.CTIPOE01   = t1.CEXPE20");
			// query.append(" INNER JOIN AA21B21S01 t4");
			// query.append(" ON t4.CSERV21    = t3.CSERV01");
			// query.append(" AND t4.CEXPE21   = t3.CTIPOE01");
			// query.append(" AND t4.CFASE21   = t3.CFASE01");

			query.append("  INNER JOIN AA21B21S01 t4");
			query.append(" ON t4.CSERV21   = t1.CSERV20");
			query.append(" AND t4.CEXPE21  = t1.CEXPE20");
			query.append(" AND t4.AEXPE21  = t1.AEXPE20");
			query.append(" AND t4.NEXPE21  = t1.NEXPE20");
		}
		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMapMultiples(
				filterAa21b20s01, listaServicios, listaTiposExpedientes,
				listaResponsables, listaArchivos, listaEstados, startsWith);
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);
		if (filterAa21b20s01.isConTareas()) {

			query.append(" group by ");
			query.append(" t1.CSERV20 ,  t1.CEXPE20 ,  t1.AEXPE20 ,  t1.NEXPE20 ,  t1.CEXPE20  ||'/'  ||trim(TO_CHAR(t1.NEXPE20,'000000'))  ||'/'  ||t1.AEXPE20 ,");
			query.append("  t1.FAPER20 ,  t1.OBJETO20 ,  t1.BLOQ20 ,  t1.FCIER20 ,  t1.REFER20 ,  t1.CRESPO20 ,  t1.CPAIS20 ,  t1.CPROV20 ,  t1.CMUNI20 ,  t1.OBSER20 ,  t1.MONEDA20 ,  t1.EIMPOR20 ,  t1.PIMPOR20 ,  t1.EIMPORA20 ,");
			query.append("  t1.PIMPORA20 ,  t1.CDEPAR20 ,  t1.CARCHI20 ,  t1.UBICA20 ,  t1.CESTA20 ,  t1.USUB20 ,  t1.FVTO20  ,  t07.DESTA07 ,  t07.EESTA07  ");// ,
																																								// t22.CGRUPO22
																																								// ,
																																								// t22.CGRUPO22
																																								// ,
																																								// t22.CGRUPO22
																																								// ");
		}
		List<?> params = (List<?>) mapaWhere.get("params");

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

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

	/**
	 * 
	 * @param aa21b20s01
	 *            Aa21b20s10
	 * @param listaServicios
	 *            List<Aa21b13s01>
	 * @param listaTiposExpedientes
	 *            List<Aa21b02s01>
	 * @param listaResponsables
	 *            List<Aa21b15s01>
	 * @param listaArchivos
	 *            List<Aa21b14s01>
	 * @param listaEstados
	 *            List<Aa21b07s01>
	 * @param startsWith
	 *            boolean
	 * @return Map<String,?>
	 */
	private Map<String, ?> getWhereLikeMapMultiples(Aa21b20s01 aa21b20s01,
			List<Aa21b13s01> listaServicios,
			List<Aa21b02s01> listaTiposExpedientes,
			List<Aa21b15s01> listaResponsables, List<Aa21b14s01> listaArchivos,
			List<Aa21b07s01> listaEstados, boolean startsWith) {
		// TODO Auto-generated method stub

		StringBuilder where = new StringBuilder(
				Aa21b20s01DaoImpl.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.CSERV20 = ? ");
					params.add(serviciosAux.getCServ13());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

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

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

		if (listaResponsables != null) {
			if (listaResponsables.size() > 0) {
				where.append(" AND (");
				Aa21b15s01 responsablesAux = null;
				for (Iterator<Aa21b15s01> iterator = listaResponsables
						.iterator(); iterator.hasNext();) {

					responsablesAux = (Aa21b15s01) iterator.next();
					where.append(" t1.CRESPO20 = ? ");
					params.add(responsablesAux.getCusu15());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

		if (listaArchivos != null) {
			if (listaArchivos.size() > 0) {
				where.append(" AND (");
				Aa21b14s01 archivosAux = null;
				for (Iterator<Aa21b14s01> iterator = listaArchivos.iterator(); iterator
						.hasNext();) {

					archivosAux = (Aa21b14s01) iterator.next();
					where.append(" t1.CARCHI20 = ? ");
					params.add(archivosAux.getCarchi14());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

		if (listaEstados != null) {
			if (listaEstados.size() > 0) {
				where.append(" AND (");
				Aa21b07s01 estadosAux = null;
				for (Iterator<Aa21b07s01> iterator = listaEstados.iterator(); iterator
						.hasNext();) {

					estadosAux = (Aa21b07s01) iterator.next();
					where.append(" t1.CESTA20 = ? ");
					params.add(estadosAux.getCesta07());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

		if (aa21b20s01 != null && aa21b20s01.getRefExp() != null) {
			where.append(" AND t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = ?");
			params.add(aa21b20s01.getRefExp());
		}

		if (aa21b20s01 != null && aa21b20s01.getGrupo() != null) {
			where.append(" AND t22.CGRUPO22 = ?");
			params.add(aa21b20s01.getGrupo());
		}

		/*
		 * if (aa21b20s01 != null && aa21b20s01.getCdocu() != null) {
		 * where.append(" AND t25.CDOCU25 = ?");
		 * params.add(aa21b20s01.getCdocu()); }
		 */

		if (aa21b20s01 != null && aa21b20s01.getCfase() != null) {
			where.append(" AND t21.CFASE21 = ?");
			params.add(aa21b20s01.getCfase());
		}

		if (aa21b20s01 != null && aa21b20s01.getAexpe20() != null) {
			where.append(" AND t1.AEXPE20 = ?");
			params.add(aa21b20s01.getAexpe20());
		}
		if (aa21b20s01 != null && aa21b20s01.getNexpe20() != null) {
			where.append(" AND t1.NEXPE20 = ?");
			params.add(aa21b20s01.getNexpe20());
		}

		if (aa21b20s01 != null && aa21b20s01.getCtrami20() != null) {
			where.append(" AND t2.CTRAMI02 = ?");
			params.add(aa21b20s01.getCtrami20());
		}

		if (aa21b20s01 != null
				&& ((aa21b20s01.getFaper20desde() != null || aa21b20s01
						.getFaper20hasta() != null) && (aa21b20s01
						.getFcier20desde() != null || aa21b20s01
						.getFcier20hasta() != null))) {

			where.append(" AND ((");

			if (aa21b20s01.getFaper20desde() != null
					&& aa21b20s01.getFaper20hasta() != null) {
				where.append(" t1.FAPER20 >= ?");
				params.add(aa21b20s01.getFaper20desde());
				where.append(" AND t1.FAPER20 <= ?");
				params.add(aa21b20s01.getFaper20hasta());
			} else if (aa21b20s01.getFaper20desde() != null) {
				where.append(" t1.FAPER20 >= ?");
				params.add(aa21b20s01.getFaper20desde());
			} else if (aa21b20s01.getFaper20hasta() != null) {
				where.append(" t1.FAPER20 <= ?");
				params.add(aa21b20s01.getFaper20hasta());
			}

			where.append(") OR (");

			if (aa21b20s01.getFcier20desde() != null
					&& aa21b20s01.getFcier20hasta() != null) {
				where.append(" t1.FCIER20 >= ?");
				params.add(aa21b20s01.getFcier20desde());
				where.append(" AND t1.FCIER20 <= ?");
				params.add(aa21b20s01.getFcier20hasta());
			} else if (aa21b20s01.getFcier20desde() != null) {
				where.append(" t1.FCIER20 >= ?");
				params.add(aa21b20s01.getFcier20desde());
			} else if (aa21b20s01.getFcier20hasta() != null) {
				where.append(" t1.FCIER20 <= ?");
				params.add(aa21b20s01.getFcier20hasta());
			}

			where.append("))");
		} else {
			if (aa21b20s01 != null && aa21b20s01.getFaper20desde() != null) {
				where.append(" AND t1.FAPER20 >= ?");
				params.add(aa21b20s01.getFaper20desde());
			}
			if (aa21b20s01 != null && aa21b20s01.getFaper20hasta() != null) {
				where.append(" AND t1.FAPER20 <= ?");
				params.add(aa21b20s01.getFaper20hasta());
			}
			if (aa21b20s01 != null && aa21b20s01.getFcier20desde() != null) {
				where.append(" AND t1.FCIER20 >= ?");
				params.add(aa21b20s01.getFcier20desde());
			}
			if (aa21b20s01 != null && aa21b20s01.getFcier20hasta() != null) {
				where.append(" AND t1.FCIER20 <= ?");
				params.add(aa21b20s01.getFcier20hasta());
			}
		}

		if (aa21b20s01 != null && aa21b20s01.getObjeto20() != null) {
			where.append(" AND UPPER(t1.OBJETO20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getObjeto20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getObjeto20().toUpperCase() + "%");
			}
			where.append(" AND t1.OBJETO20 IS NOT NULL");
		}
		if (aa21b20s01 != null && aa21b20s01.getBloq20() != null) {
			where.append(" AND t1.BLOQ20 = ?");
			params.add(aa21b20s01.getBloq20());
		}

		if (aa21b20s01 != null && aa21b20s01.getRefer20() != null) {
			where.append(" AND UPPER(t1.REFER20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getRefer20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getRefer20().toUpperCase() + "%");
			}
			where.append(" AND t1.REFER20 IS NOT NULL");
		}
		if (aa21b20s01 != null && aa21b20s01.getCpais20() != null) {
			where.append(" AND t1.CPAIS20 = ? ");
			params.add(aa21b20s01.getCpais20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCprov20() != null) {
			where.append(" AND t1.CPROV20 = ? ");
			params.add(aa21b20s01.getCprov20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCmuni20() != null) {
			where.append(" AND t1.CMUNI20 = ? ");
			params.add(aa21b20s01.getCmuni20());
		}
		if (aa21b20s01 != null && aa21b20s01.getObser20() != null) {
			where.append(" AND UPPER(t1.OBSER20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getObser20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getObser20().toUpperCase() + "%");
			}
			where.append(" AND t1.OBSER20 IS NOT NULL");
		}
		if (aa21b20s01 != null && aa21b20s01.getMoneda20() != null) {
			where.append(" AND UPPER(t1.MONEDA20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getMoneda20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getMoneda20().toUpperCase() + "%");
			}
			where.append(" AND t1.MONEDA20 IS NOT NULL");
		}
		if (aa21b20s01 != null && aa21b20s01.getEimpor20() != null) {
			where.append(" AND t1.EIMPOR20 = ?");
			params.add(aa21b20s01.getEimpor20());
		}
		if (aa21b20s01 != null && aa21b20s01.getPimpor20() != null) {
			where.append(" AND t1.PIMPOR20 = ?");
			params.add(aa21b20s01.getPimpor20());
		}
		if (aa21b20s01 != null && aa21b20s01.getEimpora20() != null) {
			where.append(" AND t1.EIMPORA20 = ?");
			params.add(aa21b20s01.getEimpora20());
		}
		if (aa21b20s01 != null && aa21b20s01.getPimpora20() != null) {
			where.append(" AND t1.PIMPORA20 = ?");
			params.add(aa21b20s01.getPimpora20());
		}
		if (aa21b20s01 != null && aa21b20s01.getCdepar20() != null) {
			where.append(" AND t1.CDEPAR20 = ?");
			params.add(aa21b20s01.getCdepar20());
		}

		if (aa21b20s01 != null && aa21b20s01.getUbica20() != null) {
			where.append(" AND UPPER(t1.UBICA20) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(aa21b20s01.getUbica20().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b20s01.getUbica20().toUpperCase() + "%");
			}
			where.append(" AND t1.UBICA20 IS NOT NULL");
		}

		if (aa21b20s01 != null && aa21b20s01.getUsub20() != null) {
			where.append(" AND t1.USUB20 = ? ");
			params.add(aa21b20s01.getUsub20());
		}
		if (aa21b20s01 != null && aa21b20s01.getFvto20desde() != null) {
			where.append(" AND t1.FVTO20 >= ?");
			params.add(aa21b20s01.getFvto20desde());
		}

		if (aa21b20s01 != null && aa21b20s01.getFvto20hasta() != null) {
			where.append(" AND t1.FVTO20 <= ?");
			params.add(aa21b20s01.getFvto20hasta());
		}
		if (aa21b20s01.isConTareas()) {
			where.append(" AND fprevi21 is not null 	and freal21 is  null");
		}
		Map<String, Object> mapWhere = new HashMap<String, Object>();
		mapWhere.put("query", where);
		mapWhere.put("params", params);

		return mapWhere;
	}

	@Override
	public Long findAllLikeCountMultiples(Aa21b20s01 aa21b20s01,
			List<Aa21b13s01> listaServicios,
			List<Aa21b02s01> listaTiposExpedientes,
			List<Aa21b15s01> listaResponsables, List<Aa21b14s01> listaArchivos,
			List<Aa21b07s01> listaEstados, boolean startsWith) {
		// TODO Auto-generated method stub
		Aa21b20s01DaoImpl.logger.info("findAllLikeCountMultiples");
		StringBuilder query = new StringBuilder(
				"select count(*) from (SELECT  t1.CSERV20 CSERV20,t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.REFER20 REFER20,t1.CRESPO20 CRESPO20,t1.CPAIS20 CPAIS20,t1.CPROV20 CPROV20,t1.CMUNI20 CMUNI20,t1.OBSER20 OBSER20,t1.MONEDA20 MONEDA20,t1.EIMPOR20 EIMPOR20,t1.PIMPOR20 PIMPOR20,t1.EIMPORA20 EIMPORA20,t1.PIMPORA20 PIMPORA20,t1.CDEPAR20 CDEPAR20,t1.CARCHI20 CARCHI20,t1.UBICA20 UBICA20,t1.CESTA20 CESTA20,t1.USUB20 USUB20,t1.FVTO20 FVTO20 ");
		query.append(", t07.DESTA07 DESTA, t07.EESTA07 EESTA ");
		// query.append(", t22.CGRUPO22 GRUPO ");
		// query.append(", t22.CGRUPO22 CDOCU ");
		// query.append(", t22.CGRUPO22 CFASE ");
		query.append(", null GRUPO ");
		query.append(", null CDOCU ");
		query.append(", null CFASE ");
		if (aa21b20s01.isConTareas()) {
			query.append(", sum( case when fprevi21 <trunc(sysdate) then 1 else 0 end )as caducadas ");
		}
		query.append(" FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");
		query.append(" LEFT JOIN AA21B02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		if (aa21b20s01.isConTareas()) {
			// query.append(" inner join AA21B01S01 t3 on");
			// query.append(" t3.CSERV01    = t1.CSERV20");
			// query.append(" AND t3.CTIPOE01   = t1.CEXPE20");
			// query.append(" INNER JOIN AA21B21S01 t4");
			// query.append(" ON t4.CSERV21    = t3.CSERV01");
			// query.append(" AND t4.CEXPE21   = t3.CTIPOE01");
			// query.append(" AND t4.CFASE21   = t3.CFASE01");

			query.append("  INNER JOIN AA21B21S01 t4");
			query.append(" ON t4.CSERV21   = t1.CSERV20");
			query.append(" AND t4.CEXPE21  = t1.CEXPE20");
			query.append(" AND t4.AEXPE21  = t1.AEXPE20");
			query.append(" AND t4.NEXPE21  = t1.NEXPE20");
		}
		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMapMultiples(aa21b20s01,
				listaServicios, listaTiposExpedientes, listaResponsables,
				listaArchivos, listaEstados, startsWith);
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);
		if (aa21b20s01.isConTareas()) {

			query.append(" group by ");
			query.append(" t1.CSERV20 ,  t1.CEXPE20 ,  t1.AEXPE20 ,  t1.NEXPE20 ,  t1.CEXPE20  ||'/'  ||trim(TO_CHAR(t1.NEXPE20,'000000'))  ||'/'  ||t1.AEXPE20 ,");
			query.append("  t1.FAPER20 ,  t1.OBJETO20 ,  t1.BLOQ20 ,  t1.FCIER20 ,  t1.REFER20 ,  t1.CRESPO20 ,  t1.CPAIS20 ,  t1.CPROV20 ,  t1.CMUNI20 ,  t1.OBSER20 ,  t1.MONEDA20 ,  t1.EIMPOR20 ,  t1.PIMPOR20 ,  t1.EIMPORA20 ,");
			query.append("  t1.PIMPORA20 ,  t1.CDEPAR20 ,  t1.CARCHI20 ,  t1.UBICA20 ,  t1.CESTA20 ,  t1.USUB20 ,  t1.FVTO20  ,  t07.DESTA07 ,  t07.EESTA07 ");// ,
																																								// t22.CGRUPO22
																																								// ,
																																								// t22.CGRUPO22
																																								// ,
																																								// t22.CGRUPO22
																																								// ");
		}
		query.append(")");
		List<?> params = (List<?>) mapaWhere.get("params");

		Aa21b20s01DaoImpl.logger.info("findAllLikeCountMultiples");
		Aa21b20s01DaoImpl.logger.info("SQL = " + query.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMS" + params);

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

	@Override
	public List<TableRowDto<Aa21b20s01>> reorderSelectionMultiples(
			Aa21b20s01 filterAa21b20s01, JQGridRequestDto jqGridRequestDto,
			List<Aa21b13s01> listaServicios,
			List<Aa21b02s01> listaTiposExpedientes,
			List<Aa21b15s01> listaResponsables, List<Aa21b14s01> listaArchivos,
			List<Aa21b07s01> listaEstados, boolean startsWith) {
		// TODO Auto-generated method stub
		Aa21b20s01DaoImpl.logger.info("reorderSelection");
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT  t1.CSERV20 CSERV20,t1.CEXPE20 CEXPE20,t1.AEXPE20 AEXPE20,t1.NEXPE20 NEXPE20, t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 as REFEXP,  t1.FAPER20 FAPER20,t1.OBJETO20 OBJETO20,t1.BLOQ20 BLOQ20,t1.FCIER20 FCIER20,t1.REFER20 REFER20,t1.CRESPO20 CRESPO20,t1.CPAIS20 CPAIS20,t1.CPROV20 CPROV20,t1.CMUNI20 CMUNI20,t1.OBSER20 OBSER20,t1.MONEDA20 MONEDA20,t1.EIMPOR20 EIMPOR20,t1.PIMPOR20 PIMPOR20,t1.EIMPORA20 EIMPORA20,t1.PIMPORA20 PIMPORA20,t1.CDEPAR20 CDEPAR20,t1.CARCHI20 CARCHI20,t1.UBICA20 UBICA20,t1.CESTA20 CESTA20,t1.USUB20 USUB20,t1.FVTO20 FVTO20 ");
		query.append(", t07.DESTA07 DESTA, t07.EESTA07 EESTA ");
		query.append(", NULL GRUPO ");
		query.append(", NULL CDOCU ");
		query.append(", NULL CFASE ");
		if (filterAa21b20s01.isConTareas()) {
			query.append(", sum( case when fprevi21 <trunc(sysdate) then 1 else 0 end )as caducadas ");
		}
		query.append(" FROM AA21B20S01 t1 ");
		query.append(" LEFT JOIN AA21B07S01 t07 ON t1.CESTA20 = t07.CESTA07");
		query.append(" LEFT JOIN AA21B02S01 t2 ON t1.CEXPE20 = t2.CTIPOE02 and t1.cserv20=t2.CSERV02");
		// query.append(" LEFT JOIN AA21B25S01 t25 ON t1.CEXPE20 = t25.CEXPE25 AND t1.CSERV20 = t25.CSERV25 AND t1.NEXPE20 = t25.NEXPE25 AND t1.AEXPE20 = t25.AEXPE25 ");
		// query.append(" LEFT JOIN AA21B22S01 t22 ON t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = t22.NEXPEREL22");

		if (filterAa21b20s01.isConTareas()) {

			query.append("  INNER JOIN AA21B21S01 t4");
			query.append(" ON t4.CSERV21   = t1.CSERV20");
			query.append(" AND t4.CEXPE21  = t1.CEXPE20");
			query.append(" AND t4.AEXPE21  = t1.AEXPE20");
			query.append(" AND t4.NEXPE21  = t1.NEXPE20");
		}
		// FILTRADO
		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMapMultiples(
				filterAa21b20s01, listaServicios, listaTiposExpedientes,
				listaResponsables, listaArchivos, listaEstados, startsWith);
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);
		if (filterAa21b20s01.isConTareas()) {

			query.append(" group by ");
			query.append(" t1.CSERV20 ,  t1.CEXPE20 ,  t1.AEXPE20 ,  t1.NEXPE20 ,  t1.CEXPE20  ||'/'  ||trim(TO_CHAR(t1.NEXPE20,'000000'))  ||'/'  ||t1.AEXPE20 ,");
			query.append("  t1.FAPER20 ,  t1.OBJETO20 ,  t1.BLOQ20 ,  t1.FCIER20 ,  t1.REFER20 ,  t1.CRESPO20 ,  t1.CPAIS20 ,  t1.CPROV20 ,  t1.CMUNI20 ,  t1.OBSER20 ,  t1.MONEDA20 ,  t1.EIMPOR20 ,  t1.PIMPOR20 ,  t1.EIMPORA20 ,");
			query.append("  t1.PIMPORA20 ,  t1.CDEPAR20 ,  t1.CARCHI20 ,  t1.UBICA20 ,  t1.CESTA20 ,  t1.USUB20 ,  t1.FVTO20  ,  t07.DESTA07 ,  t07.EESTA07  ,  t22.CGRUPO22  ,  t22.CGRUPO22  ,  t22.CGRUPO22 ");
		}
		// 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, Aa21b20s01.class, filterParamList,
				"CSERV20,CEXPE20,AEXPE20,NEXPE20");

		Aa21b20s01DaoImpl.logger.info("reorderSelection");
		Aa21b20s01DaoImpl.logger.info("SQL = "
				+ sbReorderSelectionSQL.toString());
		Aa21b20s01DaoImpl.logger.info("PARAMs=" + filterParamList);

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

}
