package com.ejie.t17i.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

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

import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ejie.t17i.filters.ProvinciaFilter;
import com.ejie.t17i.model.Provincia;
import com.ejie.x38.dto.JQGridManager;
import com.ejie.x38.dto.JQGridRequestDto;

/**
 * <p>
 * Implementaci&oacute;n de servicios DAO para acceder a la tabla PROVINCIA de
 * NORA
 * </p>
 * 
XXXX
 * @see com.ejie.t17i.model.Provincia
 * @see com.ejie.t17i.dao.ProvinciaDao
 */
@Repository
@Transactional
public class ProvinciaDaoImpl implements ProvinciaDao {

	private JdbcTemplate jdbcTemplate;
	private final RowMapper<Provincia> rwMap = new RowMapper<Provincia>() {
		@Override
		public Provincia mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new Provincia(resultSet.getString("ID"),
					resultSet.getString("NOMBRE"),
					resultSet.getString("AUTONOMIAID"));
		}
	};

	/**
	 * M&eacute;todo que asigna el DataSource a usar para el acceso a BBDD.
	 * 
	 * @param dataSource
	 *            El DataSource a usar.
	 */
	@Resource
	public void setDataSource(final DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	@Transactional(readOnly = true)
	public Provincia find(final Provincia provincia) {
		if(provincia.getId()==null){return null;}
		List<Provincia> provinciaList = this.jdbcTemplate.query(
				ProvinciaDaoSql.SQL_FIND, this.rwMap, provincia.getId());
		return DataAccessUtils.uniqueResult(provinciaList);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	@Transactional(readOnly = true)
	public List<Provincia> findAll(final ProvinciaFilter provincia,
			final JQGridRequestDto jqGridRequestDto) {
		StringBuilder query = new StringBuilder(
				ProvinciaDaoSql.SQL_FIND_ALL.length())
				.append(ProvinciaDaoSql.SQL_FIND_ALL);
		final List<Object> params = new ArrayList<Object>();

		this.whereAppender(provincia, params, query);

		if (jqGridRequestDto != null) {
			query = JQGridManager.getPaginationQuery(jqGridRequestDto, query);
		} else {
			query.append(ProvinciaDaoSql.ORDER);
		}
		return this.jdbcTemplate.query(query.toString(), this.rwMap,
				params.toArray());
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	@Transactional(readOnly = true)
	public Long findAllCount(final ProvinciaFilter provincia) {

		final StringBuilder query = new StringBuilder(
				ProvinciaDaoSql.SQL_FIND_ALL_COUNT.length())
				.append(ProvinciaDaoSql.SQL_FIND_ALL_COUNT);
		final List<Object> params = new ArrayList<Object>();

		this.whereAppender(provincia, params, query);

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

	/**
	 * {@inheritDoc}
	 */
	@Override
	@Transactional(readOnly = true)
	public List<Provincia> findAllLike(final ProvinciaFilter provincia,
			final JQGridRequestDto jqGridRequestDto, final Boolean startsWith) {
		StringBuilder query = new StringBuilder(
				ProvinciaDaoSql.SQL_FIND_ALL.length())
				.append(ProvinciaDaoSql.SQL_FIND_ALL);
		final List<Object> params = new ArrayList<Object>();

		this.whereAppender(provincia, params, query);

		if (jqGridRequestDto != null) {
			query = JQGridManager.getPaginationQuery(jqGridRequestDto, query);
		} else {
			query.append(ProvinciaDaoSql.ORDER);
		}

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

	/**
	 * Funci&oacute;n que nos compone la clausula where y los par&aacute;metros.
	 * 
	 * @param filtro
	 *            ProvinciaFilter utilizado como filtro.
	 * @param params
	 *            Listado de parametros para la clausula where.
	 * @param where
	 *            Clausula where.
	 */
	protected void whereAppender(ProvinciaFilter filtro, List<Object> params,
			StringBuilder where) {

		if (filtro != null) {
			where.append(ProvinciaDaoSql.SQL_WHERE_ALL);
			if (filtro.getId() != null) {
				where.append(" AND " + ProvinciaDaoSql.ALIAS + ".ID = ? ");
				params.add(filtro.getId());
			}
			if (filtro.getNombre() != null && filtro.getNombre().length() > 0) {
				where.append(" AND UPPER(" + ProvinciaDaoSql.ALIAS
						+ ".DS_O) like ? ESCAPE  '\\'");
				params.add("%" + filtro.getNombre().toUpperCase() + "%");
			}
			if (filtro.getAutonomia() != null) {
				where.append(" AND " + ProvinciaDaoSql.ALIAS
						+ ".AUTONOMIA_ID = ? ");
				params.add(filtro.getAutonomia());
			}
			if (filtro.isSoloCAPV()) {
				where.append(" AND " + ProvinciaDaoSql.ALIAS
						+ ".ID IN ( ?, ?, ?) ");
				params.add("01");
				params.add("20");
				params.add("48");
			}
		}
	}
}
