package com.ejie.u74a.dao;

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

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

import org.springframework.context.i18n.LocaleContextHolder;
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.u74a.model.Combo;

/**
 * Clase que accede a la base de datos para obtener datos de usuario. Tablas: PERSONAS, PERFILES y PERFILPERSONA
 * 
 *  
 */
@Repository()
@Transactional()
public class ComboDaoImpl implements ComboDao {

	private JdbcTemplate jdbcTemplate;

	/**
	 * Clase que especifica el mapeo de las columnas para la carga de un combo
	 */
	private static final class ComboRowMapper implements RowMapper<Combo> {

		/*
		 * (non-Javadoc)
		 * 
		 * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
		 */
		@Override()
		public Combo mapRow(ResultSet rs, int rowNum) throws SQLException {

			return new Combo(rs.getString("VALUE"), rs.getString("TEXT"));

		}
	}

	/**
	 * Método para establecer el datasource.
	 * 
	 * @param dataSource DataSource
	 */
	@Resource()
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.ejie.u74a.dao.UsuarioDao#findByCodigoUsuario(int)
	 */
	@Transactional(readOnly = true)
	@Override()
	public List<Combo> findMunicipiosByProvincia(int codProvincia) {

		StringBuilder query = new StringBuilder();
		query.append("SELECT CODMUNICIP AS VALUE, NOMMUNICIP AS TEXT ");
		query.append(" FROM MUNICIPIO WHERE CODPROVINC = ? ");
		query.append(" ORDER BY TEXT");

		return this.jdbcTemplate.query(query.toString(), new ComboRowMapper(), codProvincia);

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.ejie.u74a.dao.ComboDao#findLocalidadesByMunicipio(int, int)
	 */
	@Transactional(readOnly = true)
	@Override()
	public List<Combo> findLocalidadesByMunicipio(int codProvincia, int codMunicipio) {

		StringBuilder query = new StringBuilder();
		query.append("SELECT CODLOCALID AS VALUE, NOMLOCALID AS TEXT ");
		query.append(" FROM LOCALIDAD WHERE CODMUNICIP = ? AND CODPROVINC = ? ");
		query.append(" ORDER BY TEXT");

		return this.jdbcTemplate.query(query.toString(), new ComboRowMapper(), codMunicipio, codProvincia);

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.ejie.u74a.dao.ComboDao#findProvincias()
	 */
	@Transactional(readOnly = true)
	@Override()
	public List<Combo> findProvincias() {

		StringBuilder query = new StringBuilder();
		query.append("SELECT CODPROVINC AS VALUE, ");
		if (new Locale("eu").equals(LocaleContextHolder.getLocale())) {
			query.append(" NOMPROVINCE AS TEXT ");
		} else {
			query.append(" NOMPROVINC AS TEXT ");
		}
		query.append(" FROM PROVINCIA ");
		query.append(" WHERE CODPROVINC NOT IN (1,20,48) "); // todas excepto las de euskadi
		if (new Locale("eu").equals(LocaleContextHolder.getLocale())) {
			query.append(" ORDER BY  NOMPROVINC");
		} else {
			query.append(" ORDER BY  NOMPROVINCE");
		}

		return this.jdbcTemplate.query(query.toString(), new ComboRowMapper());

	}

}
