package com.ejie.aa20b.dao;

import com.ejie.x38.dto.Pagination;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.context.i18n.LocaleContextHolder;
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.aa20b.model.Departamentos;
import com.ejie.aa20b.util.Aa20bConstantes;

/**
 * DepartamentosDaoImpl generated by UDA, 28-nov-2014 11:32:16.
 * 
 * @author UDA
 */

@Repository
@Transactional
public class DepartamentosDaoImpl implements DepartamentosDao {
	private JdbcTemplate jdbcTemplate;
	private RowMapper<Departamentos> rwMap = new RowMapper<Departamentos>() {
		public Departamentos mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new Departamentos(resultSet.getLong("IDDEPAR"),
					resultSet.getString("CDEPAR"),
					resultSet.getString("EDEPAR"), resultSet.getString("BAJA"),
					resultSet.getDate("FEINI"), resultSet.getString("CIDEPAR"));
		}
	};

	/**
	 * 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 Departamentos table.
	 * 
	 * @param departamentos
	 *            Pagination
	 * @return Departamentos
	 */
	public Departamentos add(Departamentos departamentos) {

		final Long nextId = this.jdbcTemplate
				.queryForLong("SELECT DPTO_SEQ.NEXTVAL FROM DUAL");

		departamentos.setIddepar(nextId);

		String query = "INSERT INTO DEPARTAMENTOS (IDDEPAR, CDEPAR, EDEPAR, BAJA, FEINI, CIDEPAR) VALUES (?,?,?,?,?,?)";
		String cdepar = departamentos.getCdepar();
		if (cdepar.length() > 50) {
			cdepar = cdepar.substring(0, 49);
		}
		String edepar = departamentos.getEdepar();
		if (edepar.length() > 50) {
			edepar = edepar.substring(0, 49);
		}

		this.jdbcTemplate
				.update(query,
						departamentos.getIddepar(),
						cdepar,
						edepar,
						departamentos.getBaja() == null ? "0" : departamentos
								.getBaja(), departamentos.getFeini(),
						departamentos.getCidepar());
		return departamentos;
	}

	/**
	 * Updates a single row in the Departamentos table.
	 * 
	 * @param departamentos
	 *            Pagination
	 * @return Departamentos
	 */
	public Departamentos update(Departamentos departamentos) {
		String query = "UPDATE DEPARTAMENTOS SET CDEPAR=?, EDEPAR=?, BAJA=?, FEINI=?, CIDEPAR=? WHERE IDDEPAR=?";
		this.jdbcTemplate
				.update(query, departamentos.getCdepar(), departamentos
						.getEdepar(), departamentos.getBaja() == null ? "0"
						: departamentos.getBaja(), departamentos.getFeini(),
						departamentos.getCidepar(), departamentos.getIddepar());
		return departamentos;
	}

	/**
	 * Poner de baja el dpto.
	 * 
	 * @param departamentos
	 *            Pagination
	 * @return Departamentos
	 */
	public Departamentos darBajaDpto(Departamentos departamentos) {
		String query = "UPDATE DEPARTAMENTOS SET BAJA='1' WHERE IDDEPAR=?";
		this.jdbcTemplate.update(query, departamentos.getIddepar());
		return departamentos;
	}

	/**
	 * Finds a single row in the Departamentos table.
	 * 
	 * @param departamentos
	 *            Pagination
	 * @return Departamentos
	 */
	@Transactional(readOnly = true)
	public Departamentos find(Departamentos departamentos) {
		String query = "SELECT t1.IDDEPAR IDDEPAR, t1.CDEPAR CDEPAR, t1.EDEPAR EDEPAR, t1.BAJA BAJA, t1.FEINI FEINI, t1.CIDEPAR CIDEPAR FROM DEPARTAMENTOS t1  WHERE t1.IDDEPAR = ?  ";

		List<Departamentos> departamentosList = this.jdbcTemplate.query(query,
				this.rwMap, departamentos.getIddepar());
		return (Departamentos) DataAccessUtils.uniqueResult(departamentosList);
	}

	/**
	 * obtener el iddepar asociado a un cidepar.
	 * 
	 * @param cidepar
	 *            String
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long obtenerIddepar(String cidepar) {
		String query = "SELECT t1.IDDEPAR IDDEPAR FROM DEPARTAMENTOS t1 WHERE t1.BAJA='0' AND t1.CIDEPAR = ? ";

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

	/**
	 * Removes a single row in the Departamentos table.
	 * 
	 * @param departamentos
	 *            Pagination
	 * @return
	 */
	public void remove(Departamentos departamentos) {
		String query = "DELETE FROM DEPARTAMENTOS WHERE IDDEPAR=?";
		this.jdbcTemplate.update(query, departamentos.getIddepar());
	}

	/**
	 * Finds a List of rows in the Departamentos table.
	 * 
	 * @param departamentos
	 *            Departamentos
	 * @param pagination
	 *            Pagination
	 * @return List
	 */
	@Transactional(readOnly = true)
	public List<Departamentos> findAll(Departamentos departamentos,
			Pagination pagination) {
		StringBuilder query = new StringBuilder(
				"SELECT  t1.IDDEPAR IDDEPAR,decode(baja, '1', '(baja) '||t1.cdepar,  t1.cdepar) CDEPAR,decode(baja, '1', '(baja) '||t1.edepar,  t1.cdepar) EDEPAR,t1.BAJA BAJA,t1.FEINI FEINI,t1.CIDEPAR CIDEPAR ");
		query.append("FROM DEPARTAMENTOS t1 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(departamentos);
		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("ORDER BY t1.BAJA");
			String idioma = LocaleContextHolder.getLocale().getLanguage();
			if (idioma.equals(Aa20bConstantes.IDIOMA_EUSKERA)) {
				query.append(", t1.EDEPAR");
			} else {
				query.append(", t1.CDEPAR");
			}
		}

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

	/**
	 * Counts rows in the Departamentos table.
	 * 
	 * @param departamentos
	 *            Departamentos
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllCount(Departamentos departamentos) {
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM DEPARTAMENTOS t1 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(departamentos);
		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 Departamentos table using like.
	 * 
	 * @param departamentos
	 *            Departamentos
	 * @param pagination
	 *            Pagination
	 * @param startsWith
	 *            Boolean
	 * @return List
	 */
	@Transactional(readOnly = true)
	public List<Departamentos> findAllLike(Departamentos departamentos,
			Pagination pagination, Boolean startsWith) {
		StringBuilder query = new StringBuilder(
				"SELECT  t1.IDDEPAR IDDEPAR,t1.CDEPAR CDEPAR,t1.EDEPAR EDEPAR,t1.BAJA BAJA,t1.FEINI FEINI,t1.CIDEPAR CIDEPAR ");
		query.append("FROM DEPARTAMENTOS t1 ");

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

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

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

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

	/**
	 * Si no existe el departamento lo da de alta y si existe y está de baja lo
	 * pone de alta.
	 * 
	 * @param codDpto
	 *            String
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long altaExisteDepartamento(Departamentos dpto) {

		Departamentos deptoAux = new Departamentos();

		deptoAux.setCidepar(dpto.getCidepar());

		List<Departamentos> listaDepartamentos = this.findAll(deptoAux, null);

		if (listaDepartamentos.size() == 0) { // No existe el código de
												// departamento
			// Darlo de alta en la tabla de departamentos
			dpto.setFeini(new Date());
			dpto.setBaja("0");
			Departamentos nuevoDepartamento = this.add(dpto);
			return nuevoDepartamento.getIddepar();

		} else if (listaDepartamentos.get(0).getBaja().equalsIgnoreCase("1")) {
			// El departamento está de baja. Se pone de alta
			Departamentos editDepartamento = this.updateAlta(dpto);
			return editDepartamento.getIddepar();
		}

		return listaDepartamentos.get(0).getIddepar();
	}

	/**
	 * Poner el departamento de alta.
	 * 
	 * @param departamentos
	 *            Pagination
	 * @return Departamentos
	 */
	public Departamentos updateAlta(Departamentos departamentos) {
		String query = "UPDATE DEPARTAMENTOS SET BAJA='0' WHERE IDDEPAR=? ";
		this.jdbcTemplate.update(query, departamentos.getIddepar());
		return departamentos;
	}

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

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

		if (departamentos != null && departamentos.getIddepar() != null) {
			where.append(" AND t1.IDDEPAR = ?");
			params.add(departamentos.getIddepar());
		}
		if (departamentos != null && departamentos.getCdepar() != null) {
			where.append(" AND t1.CDEPAR = ?");
			params.add(departamentos.getCdepar());
		}
		if (departamentos != null && departamentos.getEdepar() != null) {
			where.append(" AND t1.EDEPAR = ?");
			params.add(departamentos.getEdepar());
		}
		if (departamentos != null && departamentos.getBaja() != null) {
			where.append(" AND t1.BAJA = ?");
			params.add(departamentos.getBaja());
		}
		if (departamentos != null && departamentos.getFeini() != null) {
			where.append(" AND t1.FEINI = ?");
			params.add(departamentos.getFeini());
		}
		if (departamentos != null && departamentos.getCidepar() != null) {
			where.append(" AND t1.CIDEPAR = ?");
			params.add(departamentos.getCidepar());
		}

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

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

		if (departamentos != null && departamentos.getIddepar() != null) {
			where.append(" AND t1.IDDEPAR = ?");
			params.add(departamentos.getIddepar());
		}
		if (departamentos != null && departamentos.getCdepar() != null) {
			where.append(" AND UPPER(t1.CDEPAR) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(departamentos.getCdepar().toUpperCase() + "%");
			} else {
				params.add("%" + departamentos.getCdepar().toUpperCase() + "%");
			}
			where.append(" AND t1.CDEPAR IS NOT NULL");
		}
		if (departamentos != null && departamentos.getEdepar() != null) {
			where.append(" AND UPPER(t1.EDEPAR) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(departamentos.getEdepar().toUpperCase() + "%");
			} else {
				params.add("%" + departamentos.getEdepar().toUpperCase() + "%");
			}
			where.append(" AND t1.EDEPAR IS NOT NULL");
		}
		if (departamentos != null && departamentos.getBaja() != null) {
			where.append(" AND UPPER(t1.BAJA) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(departamentos.getBaja().toUpperCase() + "%");
			} else {
				params.add("%" + departamentos.getBaja().toUpperCase() + "%");
			}
			where.append(" AND t1.BAJA IS NOT NULL");
		}
		if (departamentos != null && departamentos.getFeini() != null) {
			where.append(" AND t1.FEINI = ?");
			params.add(departamentos.getFeini());
		}
		if (departamentos != null && departamentos.getCidepar() != null) {
			where.append(" AND UPPER(t1.CIDEPAR) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(departamentos.getCidepar().toUpperCase() + "%");
			} else {
				params.add("%" + departamentos.getCidepar().toUpperCase() + "%");
			}
			where.append(" AND t1.CIDEPAR 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;
}
