/**
 * 
 */
package com.ejie.aa21b.dao.ws;

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.slf4j.Logger;
import org.slf4j.LoggerFactory;
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.dao.Aa21b20s01DaoImpl;
import com.ejie.aa21b.model.ws.ExpActualizaWs;
import com.ejie.aa21b.model.ws.ExpResumenWs;
import com.ejie.aa21b.model.ws.ExpedientesWs;
import com.ejie.aa21b.model.ws.WhereWs;

/**
 *  
 * 
 */
@Repository
@Transactional
public class ExpedientesWsDaoImpl implements ExpedientesWsDao {

	private static final Logger logger = LoggerFactory
			.getLogger(ExpedientesWsDaoImpl.class);

	private JdbcTemplate jdbcTemplate;
	/*
	 * ROW_MAPPERS
	 */
	private RowMapper<ExpedientesWs> rwMap = new RowMapper<ExpedientesWs>() {
		public ExpedientesWs mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {

			ExpedientesWs expeWs = new ExpedientesWs();

			ExpActualizaWs expeActualizaWs = new ExpActualizaWs();
			expeActualizaWs.setAexpe(resultSet.getLong("AEXPE20"));
			expeActualizaWs.setCexpe(resultSet.getString("CEXPE20"));
			expeActualizaWs.setNexpe(resultSet.getInt("NEXPE20"));
			expeActualizaWs.setObjeto(resultSet.getString("OBJETO20"));
			expeActualizaWs.setRefExp(resultSet.getString("REFEXP"));
			expeActualizaWs.setUbica(resultSet.getString("UBICA20"));

			ExpActualizaWs expActDetalle = new ExpActualizaWs();
			ArrayList<ExpActualizaWs> expActDetalleLista = new ArrayList<ExpActualizaWs>();
			expActDetalleLista.add(expActDetalle);

			expeWs.setListaExpActualiza(expActDetalleLista);
			return expeWs;
		}
	};

	/*
	 * ROW_MAPPERS
	 */
	private RowMapper<ExpActualizaWs> rwMapActualizaExp = new RowMapper<ExpActualizaWs>() {
		public ExpActualizaWs mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {

			ExpActualizaWs expeActualizaWs = new ExpActualizaWs();
			expeActualizaWs.setAexpe(resultSet.getLong("AEXPE20"));
			expeActualizaWs.setCexpe(resultSet.getString("CEXPE20"));
			expeActualizaWs.setNexpe(resultSet.getInt("NEXPE20"));
			expeActualizaWs.setObjeto(resultSet.getString("OBJETO20"));
			expeActualizaWs.setRefExp(resultSet.getString("REFEXP"));
			expeActualizaWs.setUbica(resultSet.getString("UBICA20"));

			return expeActualizaWs;
		}
	};

	private RowMapper<ExpResumenWs> rwMapBuscaExp = new RowMapper<ExpResumenWs>() {
		public ExpResumenWs mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {

			ExpResumenWs expeBuscaWs = new ExpResumenWs();
			expeBuscaWs.setAexpe(resultSet.getLong("AEXPE20"));
			expeBuscaWs.setCexpe(resultSet.getString("CEXPE20"));
			expeBuscaWs.setNexpe(resultSet.getInt("NEXPE20"));
			expeBuscaWs.setObjeto(resultSet.getString("OBJETO20"));
			expeBuscaWs.setRefExp(resultSet.getString("REFEXP"));

			return expeBuscaWs;
		}
	};

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

	/**
	 * Finds a list of rows in the Aa21b20s01 table.
	 * 
	 * @param expedientesWs
	 *            ExpedientesWs
	 * @return List<ExpedientesWs>
	 */
	@Transactional(readOnly = true)
	public List<ExpedientesWs> findAll() {
		ExpedientesWsDaoImpl.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.OBJETO20 OBJETO20, t1.UBICA20 UBICA20 ");
		query.append("FROM AA21B20S01 t1 ");

		// Where clause & Params
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");

		query.append(where);

		ExpedientesWsDaoImpl.logger.info("findAll");
		ExpedientesWsDaoImpl.logger.info("SQL = " + query.toString());

		List<ExpedientesWs> listaExp = this.jdbcTemplate.query(
				query.toString(), this.rwMap);

		ExpedientesWsDaoImpl.logger.info("Fin  List<ExpedientesWs> findAll");

		return listaExp;

	}

	/**
	 * Finds a list of rows in the Aa21b20s01 table.
	 * 
	 * @return List<ExpActualizaWs>
	 */
	@Transactional(readOnly = true)
	public List<ExpActualizaWs> actualizaExpedientes() {
		ExpedientesWsDaoImpl.logger.info("actualizaExpedientes");
		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.OBJETO20 OBJETO20, t1.UBICA20 UBICA20 ");
		query.append("FROM AA21B20S01 t1 ");

		// Where clause & Params
		StringBuilder where = new StringBuilder(" WHERE t1.CSERV20 = '93' ");

		query.append(where);

		ExpedientesWsDaoImpl.logger.info("actualizaExpedientes");
		ExpedientesWsDaoImpl.logger.info("SQL = " + query.toString());

		List<ExpActualizaWs> listaExp = this.jdbcTemplate.query(
				query.toString(), this.rwMapActualizaExp);

		ExpedientesWsDaoImpl.logger
				.info("Fin  List<ExpActualizaWs> actualizaExpedientes");

		return listaExp;

	}

	/**
	 * Finds a list of rows in the Aa21b20s01 table.
	 * 
	 * @param whereEntrada
	 *            WhereWs
	 * @return List<ExpResumenWs>
	 */
	@Transactional(readOnly = true)
	public List<ExpResumenWs> buscaExpedientes(WhereWs whereEntrada) {
		ExpedientesWsDaoImpl.logger.info("buscaExpedientes");
		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.OBJETO20 OBJETO20, t1.UBICA20 UBICA20 ");
		query.append("FROM AA21B20S01 t1 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(whereEntrada);
		StringBuilder where = new StringBuilder(" WHERE t1.CSERV20 = '93' ");

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

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

		ExpedientesWsDaoImpl.logger.info("buscaExpedientes");
		ExpedientesWsDaoImpl.logger.info("SQL = " + query.toString());
		ExpedientesWsDaoImpl.logger.info("PARAMS" + params);

		List<ExpResumenWs> listaExp = this.jdbcTemplate.query(query.toString(),
				this.rwMapBuscaExp, params.toArray());

		ExpedientesWsDaoImpl.logger
				.info("Fin  List<ExpResumenWs> buscaExpedientes");

		return listaExp;

	}

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

		StringBuilder where = new StringBuilder(
				Aa21b20s01DaoImpl.STRING_BUILDER_INIT);
		List<Object> params = new ArrayList<Object>();
		/*
		 * if (whereEntrada != null && whereEntrada.getExpe() != null &&
		 * !"".equals(whereEntrada.getExpe())) { // El dato que viene es
		 * concatenación de CEXPE20 + ‘/’ + NEXPE20 + // ‘/’ + AEXPE20 String[]
		 * tokens = whereEntrada.getExpe().split("/"); if (tokens.length != 3) {
		 * // No tiene el formato correcto. ExpedientesWsDaoImpl.logger
		 * .info("getWhereMap - Formato de expediente incorrecto: " +
		 * whereEntrada.getExpe());
		 * where.append(" AND t1.CEXPE20 = ? AND t1.NEXPE20 = ? AND t1.AEXPE20 = ?"
		 * ); params.add("---"); params.add("999999"); params.add("9999"); }
		 * else { if (tokens[0] != null && !"".equals(tokens[0])) {
		 * where.append(" AND t1.CEXPE20 = ?"); params.add(tokens[0]); } if
		 * (tokens[1] != null && !"".equals(tokens[1])) {
		 * where.append(" AND t1.NEXPE20 = ?"); params.add(tokens[1]); } if
		 * (tokens[2] != null && !"".equals(tokens[2])) {
		 * where.append(" AND t1.AEXPE20 = ?"); params.add(tokens[2]); } } }
		 * 
		 * //where.append(
		 * " AND t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20 = ?"
		 * );
		 */
		if (whereEntrada != null && whereEntrada.getObjeto() != null
				&& !"".equals(whereEntrada.getObjeto())) {
			where.append("  AND UPPER(t1.OBJETO20) like ? ESCAPE  '\\'");
			params.add("%" + whereEntrada.getObjeto().toUpperCase() + "%");
			where.append(" AND t1.OBJETO20 IS NOT NULL");
		}
		if (whereEntrada != null && whereEntrada.getExpe() != null
				&& !"".equals(whereEntrada.getExpe())) {
			where.append(" AND (t1.CEXPE20||'/'||trim(to_char(t1.NEXPE20,'000000'))||'/'||t1.AEXPE20) like ? ESCAPE  '\\'");
			params.add("%" + whereEntrada.getExpe().toUpperCase() + "%");
		}

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

		return mapWhere;
	}

	public static final int STRING_BUILDER_INIT = 4096;
}
