package com.ejie.m62fb.dao;

import com.ejie.m62fb.model.Adjudicatario;
import com.ejie.m62fb.model.persona.Persona;
import com.ejie.m62fb.model.tipos.TipoClave;
import com.ejie.x38.dto.Pagination;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
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;

/**
 * AdjudicatarioDaoImpl generated by UDA, 24-oct-2012 16:55:00.
 * 
XXXX
 */

@Repository
@Transactional
public class AdjudicatarioDaoImpl implements AdjudicatarioDao {
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private ComboP28Dao comboP28Dao;
	private RowMapper<Adjudicatario> rwMap = new RowMapper<Adjudicatario>() {
		public Adjudicatario mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			Adjudicatario adjudicatario = new Adjudicatario(resultSet.getBigDecimal("IDPERSONA"));
			TipoClave tipoClave= new TipoClave(	resultSet.getString("CODTIPOCLAVE"));
			adjudicatario.setTipoCodigoId((TipoClave) comboP28Dao.find(tipoClave));
			adjudicatario.setCodigoId(resultSet.getString("NIFTITULAR"));
			adjudicatario.setNombre(resultSet.getString("NOMNOMBRE"));
			adjudicatario.setApellido1(resultSet.getString("NOMAPELLIDO1"));
			adjudicatario.setApellido2(resultSet.getString("NOMAPELLIDO2"));
			return adjudicatario;
		}
	};

	/**
	 * Method use to set the datasource.
	 * 
	 * @param dataSource
	 *            DataSource
	 * @return
	 */
	@Resource
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	/**
	 * Inserts a single row in the Adjudicatario table.
	 * 
	 * @param adjudicatario
	 *            Pagination
	 * @return Adjudicatario
	 */
	public Adjudicatario add(Adjudicatario adjudicatario) {
		return null;
		/*
		 * java.sql.Date ahora = new java.sql.Date(new
		 * java.util.Date().getTime()); String query = "INSERT INTO M62P20S0 " +
		 * "(COD_TIPO_CLAVE, NIF_TITULAR, NUM_CORREL_NIF, NOM_NOMBRE, NOM_APELLIDO_1, "
		 * +
		 * "NOM_APELLIDO_2, COD_PROVINCIA, COD_MUNICIPIO, COD_CALLE, DEC_MANO,"
		 * +
		 * "COD_PORTAL, DEC_SIT_VIV, DDIRECCI, DES_CALLE,COD_POSTAL, FEC_NACIMIENTO, "
		 * +
		 * "IND_ESTADO_CIVIL, IND_SEXO, FEC_ALTA, EMAIL, NTELEFNO, NUM_TLFNO_2, "
		 * +
		 * "FEH_ULT_MODIF, COD_USU_ULT_MODIF, IDE_REGISTRO, COD_APLICACION) VALUES "
		 * + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		 * 
		 * // TODO: Tenemos que calcular el número correlativo para dar de alta
		 * el // nuevo CIF // REvisar que campo es el DESCALLE
		 * this.jdbcTemplate.update(query, promotor.getTipoCodigoId(), promotor
		 * .getCodigoId(), 0, promotor.getNombre(), promotor .getApellido1(),
		 * promotor.getApellido2(), , "", "", "", ahora, promotor
		 * .getDatosContacto().getEmail(), promotor
		 * .getDatosContacto().getTelefono(), promotor
		 * .getDatosContacto().getTelefonoSMS(), ahora, "M62EB", 0, "M62");
		 * 
		 * return promotor;
		 */
	}

	/**
	 * Updates a single row in the Adjudicatario table.
	 * 
	 * @param adjudicatario
	 *            Pagination
	 * @return Adjudicatario
	 */
	public Adjudicatario update(Adjudicatario adjudicatario) {
		String query = "UPDATE M62P20T0S00 SET COD_TIPO_CLAVE=?, NIF_TITULAR=?, NOM_APELLIDO_1=?, NOM_APELLIDO_2=?, NOM_NOMBRE=?, COD_PROVINCIA=?, COD_MUNICIPIO=?, COD_CALLE=?, DDIRECCI=?, COD_PORTAL=?, DEC_SIT_VIV=?, DEC_MANO=?, COD_POSTAL=?, NTELEFNO=?, NUM_TLFNO_2=?, EMAIL=? WHERE ID_PERSONA=?";
		this.jdbcTemplate.update(query, adjudicatario.getTipoCodigoId(),
				adjudicatario.getCodigoId(), adjudicatario.getApellido1(),
				adjudicatario.getApellido2(), adjudicatario.getNombre(),
				adjudicatario, adjudicatario.getIdPersona());
		return adjudicatario;
	}

	/**
	 * Finds a single row in the Adjudicatario table.
	 * 
	 * @param adjudicatario
	 *            Pagination
	 * @return Adjudicatario
	 */
	@Transactional(readOnly = true, timeout = 500)
	public Adjudicatario find(Adjudicatario adjudicatario) {
		String query = "SELECT t1.ID_PERSONA IDPERSONA, t1.COD_TIPO_CLAVE CODTIPOCLAVE, t1.NIF_TITULAR NIFTITULAR, t1.NOM_APELLIDO_1 NOMAPELLIDO1, t1.NOM_APELLIDO_2 NOMAPELLIDO2, t1.NOM_NOMBRE NOMNOMBRE, t1.COD_PROVINCIA CODPROVINCIA, t1.COD_MUNICIPIO CODMUNICIPIO, t1.COD_CALLE CODCALLE, t1.DDIRECCI DDIRECCI, t1.COD_PORTAL CODPORTAL, t1.DEC_SIT_VIV DECSITVIV, t1.DEC_MANO DECMANO, t1.COD_POSTAL CODPOSTAL, t1.NTELEFNO NUMTELEFNO, t1.NUM_TLFNO_2 NUMTELEFNO2, t1.EMAIL EMAIL FROM M62P20T0S00 t1  WHERE t1.ID_PERSONA = ?  ";
		List<Adjudicatario> adjudicatarioList = this.jdbcTemplate.query(query,
				this.rwMap, adjudicatario.getIdPersona());
		return (Adjudicatario) DataAccessUtils.uniqueResult(adjudicatarioList);
	}

	/**
	 * Removes a single row in the Adjudicatario table.
	 * 
	 * @param adjudicatario
	 *            Pagination
	 * @return
	 */
	public void remove(Adjudicatario adjudicatario) {
		String query = "DELETE FROM M62P20T0S00 WHERE ID_PERSONA=?";
		this.jdbcTemplate.update(query, adjudicatario.getIdPersona());
	}

	/**
	 * Finds a List of rows in the Adjudicatario table.
	 * 
	 * @param adjudicatario
	 *            Adjudicatario
	 * @param pagination
	 *            Pagination
	 * @return List
	 */
	@Transactional(readOnly = true, timeout = 500)
	public List<Adjudicatario> findAll(Adjudicatario adjudicatario,
			Pagination pagination) {
		StringBuilder query = new StringBuilder(
				"SELECT  t1.ID_PERSONA IDPERSONA,t1.COD_TIPO_CLAVE CODTIPOCLAVE,t1.NIF_TITULAR NIFTITULAR,t1.NOM_APELLIDO_1 NOMAPELLIDO1,t1.NOM_APELLIDO_2 NOMAPELLIDO2,t1.NOM_NOMBRE NOMNOMBRE,t1.COD_PROVINCIA CODPROVINCIA,t1.COD_MUNICIPIO CODMUNICIPIO,t1.COD_CALLE CODCALLE,t1.DDIRECCI DDIRECCI,t1.COD_PORTAL CODPORTAL,t1.DEC_SIT_VIV DECSITVIV,t1.DEC_MANO DECMANO,t1.COD_POSTAL CODPOSTAL,t1.NTELEFNO NUMTELEFNO,t1.NUM_TLFNO_2 NUMTELEFNO2,t1.EMAIL EMAIL ");
		query.append(" FROM M62P20T0S00 t1 ");

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

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

		if (pagination != null) {
			query = pagination.getPaginationQuery(query);
		}

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

	/**
	 * Counts rows in the Adjudicatario table.
	 * 
	 * @param adjudicatario
	 *            Adjudicatario
	 * @return Long
	 */
	@Transactional(readOnly = true, timeout = 500)
	public Long findAllCount(Adjudicatario adjudicatario) {
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM M62P20T0S00 t1 ");

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

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

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

	/**
	 * Finds rows in the Adjudicatario table using like.
	 * 
	 * @param adjudicatario
	 *            Adjudicatario
	 * @param pagination
	 *            Pagination
	 * @param startsWith
	 *            Boolean
	 * @return List
	 */
	@Transactional(readOnly = true, timeout = 500)
	public List<Adjudicatario> findAllLike(Adjudicatario adjudicatario,
			Pagination pagination, Boolean startsWith) {
		StringBuilder query = new StringBuilder(
				"SELECT  t1.ID_PERSONA IDPERSONA,t1.COD_TIPO_CLAVE CODTIPOCLAVE,t1.NIF_TITULAR NIFTITULAR,t1.NOM_APELLIDO_1 NOMAPELLIDO1,t1.NOM_APELLIDO_2 NOMAPELLIDO2,t1.NOM_NOMBRE NOMNOMBRE,t1.COD_PROVINCIA CODPROVINCIA,t1.COD_MUNICIPIO CODMUNICIPIO,t1.COD_CALLE CODCALLE,t1.DDIRECCI DDIRECCI,t1.COD_PORTAL CODPORTAL,t1.DEC_SIT_VIV DECSITVIV,t1.DEC_MANO DECMANO,t1.COD_POSTAL CODPOSTAL,t1.NTELEFNO NUMTELEFNO,t1.NUM_TLFNO_2 NUMTELEFNO2,t1.EMAIL EMAIL  ");
		query.append(" FROM M62P20T0S00 t1 ");

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

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

		if (pagination != null) {
			query = pagination.getPaginationQuery(query);
		} else {
			query.append(" AND ROWNUM < 20");
		}

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

	/**
	 * Counts rows in the Adjudicatario table using like.
	 * 
	 * @param adjudicatario
	 *            Adjudicatario
	 * @param startsWith
	 *            Boolean
	 * @return Long
	 */
	@Transactional(readOnly = true, timeout = 500)
	public Long findAllLikeCount(Adjudicatario adjudicatario, Boolean startsWith) {
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM M62P20T0S00 t1 ");

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

		// AND rownum <= 100
		where.append(mapaWhere.get("query"));
		query.append(where);

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

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

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

		StringBuffer where = new StringBuffer(
				AdjudicatarioDaoImpl.STRING_BUILDER_INIT);
		List<Object> params = new ArrayList<Object>();

		if (adjudicatario != null && adjudicatario.getIdPersona() != null) {
			where.append(" AND t1.ID_PERSONA = ?");
			params.add(adjudicatario.getIdPersona());
		}
		if (adjudicatario != null && adjudicatario.getTipoCodigoId() != null) {
			where.append(" AND t1.COD_TIPO_CLAVE = ?");
			params.add(adjudicatario.getTipoCodigoId());
		}
		where.append(" AND ( t1.COD_TIPO_CLAVE <> 'E' AND t1.COD_TIPO_CLAVE <> 'F' )");
		if (adjudicatario != null && adjudicatario.getCodigoId() != null) {
			where.append(" AND t1.NIF_TITULAR = ?");
			params.add(adjudicatario.getCodigoId());
		}
		if (adjudicatario != null && adjudicatario.getApellido1() != null) {
			where.append(" AND t1.NOM_APELLIDO_1 = ?");
			params.add(adjudicatario.getApellido1());
		}
		if (adjudicatario != null && adjudicatario.getApellido2() != null) {
			where.append(" AND t1.NOM_APELLIDO_2 = ?");
			params.add(adjudicatario.getApellido2());
		}
		if (adjudicatario != null && adjudicatario.getNombre() != null) {
			where.append(" AND t1.NOM_NOMBRE = ?");
			params.add(adjudicatario.getNombre());
		}

		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 Adjudicatario entity
	 * 
	 * @param Adjudicatario
	 *            Adjudicatario 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, ?> getWhereLikeMap(Adjudicatario adjudicatario,
			Boolean startsWith) {

		StringBuffer where = new StringBuffer(
				AdjudicatarioDaoImpl.STRING_BUILDER_INIT);
		List<Object> params = new ArrayList<Object>();

		if (adjudicatario != null && adjudicatario.getIdPersona() != null) {
			where.append(" AND t1.ID_PERSONA = ?");
			params.add(adjudicatario.getIdPersona());
		}
		if (adjudicatario != null && adjudicatario.getTipoCodigoId() != null) {
			where.append(" AND t1.COD_TIPO_CLAVE = ?");

			params.add(adjudicatario.getTipoCodigoId().getCodTabla());
		}
		if (adjudicatario != null && adjudicatario.getCodigoId() != null) {
			where.append(" AND UPPER(t1.NIF_TITULAR) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(adjudicatario.getCodigoId().toUpperCase() + "%");
			} else {
				params.add("%" + adjudicatario.getCodigoId().toUpperCase()
						+ "%");
			}
			where.append(" AND t1.NIF_TITULAR IS NOT NULL");
		}
		if (adjudicatario != null && adjudicatario.getApellido1() != null) {
			where.append(" AND UPPER(t1.NOM_APELLIDO_1) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(adjudicatario.getApellido1().toUpperCase() + "%");
			} else {
				params.add("%" + adjudicatario.getApellido1().toUpperCase()
						+ "%");
			}
			where.append(" AND t1.NOM_APELLIDO_1 IS NOT NULL");
		}
		if (adjudicatario != null && adjudicatario.getApellido2() != null) {
			where.append(" AND UPPER(t1.NOM_APELLIDO_2) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(adjudicatario.getApellido2().toUpperCase() + "%");
			} else {
				params.add("%" + adjudicatario.getApellido2().toUpperCase()
						+ "%");
			}
			where.append(" AND t1.NOM_APELLIDO_2 IS NOT NULL");
		}
		if (adjudicatario != null && adjudicatario.getNombre() != null) {
			where.append(" AND ( UPPER(t1.NOM_NOMBRE) || UPPER(t1.NOM_APELLIDO_1) || UPPER(t1.NOM_APELLIDO_2)) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(adjudicatario.getNombre().toUpperCase() + "%");
			} else {
				params.add("%" + adjudicatario.getNombre().toUpperCase() + "%");
			}
			where.append(" AND t1.NOM_NOMBRE IS NOT NULL");
		}

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

		return mapWhere;
	}

	// CHECKSTYLE:ON CyclomaticComplexity - Generación de código de UDA

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