package com.ejie.m62fb.dao.registroVPP;

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;

import com.ejie.m62fb.model.Promocion;
import com.ejie.m62fb.model.direccion.Direccion;
import com.ejie.m62fb.utils.sql.BDUtils;
import com.ejie.m62fb.utils.sql.Utilidades;
import com.ejie.x38.dto.JQGridManager;
import com.ejie.x38.dto.JQGridRequestDto;

/**
 * PromocionDaoImpl
XXXX
 */
 
@Repository
@Transactional
public class PromocionDaoImpl implements PromocionDao {
    protected static final Promocion UtilsResultSet = null;

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

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

	 private RowMapper<Promocion> rwMap = new RowMapper<Promocion>() {
		 public Promocion mapRow(ResultSet resultSet, int rowNum) throws SQLException {	
			 Promocion promocion = new Promocion();
			 promocion.setIdPromocion(resultSet.getBigDecimal("ID_M62E24T0"));
			promocion.setNumExpediente(resultSet.getString("NUM_EXPEDIENTE"));
			  return promocion; 
	          } 
			} ;


	 /**
	     * Finds a single row in the M62r12t0 table.
	     *
	     * @param elemento Elemento
	     * @return Elemento
	     */
	    @Transactional (readOnly = true)
	    public Promocion find(Promocion promocion) {		
			String query = PromocionDaoSql.SQL_FIND;
			List<Promocion> promocionList = this.jdbcTemplate.query(query,
					this.rwMap, promocion.getIdPromocion());
			return DataAccessUtils.uniqueResult(promocionList);
	    }
	    /**
		 * Finds rows in the Persona table using like.
		 * 
		 * @param persona
		 *            Persona
		 * @param pagination
		 *            Pagination
		 * @param startsWith
		 *            Boolean
		 * @return List
		 */
		@Override
		@Transactional(readOnly = true, timeout = 500)
		public List<Promocion> findAllLike(Promocion promocion, JQGridRequestDto jqGridRequestDto,
				Boolean startsWith) {
			StringBuilder query = new StringBuilder(
					PromocionDaoSql.SQL_FIND_ALL);
	      	
			//Where clause & Params
			Map<String, ?> mapaWhere = this.getWhereLikeMap(promocion,startsWith); 
			StringBuilder where = new StringBuilder(
					PromocionDaoSql.SQL_WHERE_ALL);
			where.append(mapaWhere.get("query"));
			query.append(where);

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

			if (jqGridRequestDto != null) {
				query = JQGridManager.getPaginationQuery(jqGridRequestDto, query);
			}
			
			return (List<Promocion>) this.jdbcTemplate.query(query.toString(), this.rwMap, params.toArray());
		} 
	
	
		/**
		 * Returns a map with the needed value to create the conditions to filter by  
		 * the Elementos entity 
		 * 
		 * @param elemento Elementos
		 *            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, Object> getWhereLikeMap (Promocion promocion, Boolean startsWith){
			
			StringBuilder where = new StringBuilder(PromocionDaoImpl.STRING_BUILDER_INIT);
			List<Object> params = new ArrayList<Object>();

			if (promocion  != null  && promocion.getIdPromocion() != null ) {
				where.append(" AND ID_M62E24T0= ?");
				params.add(promocion.getIdPromocion());
			}
		
			if (promocion  != null  && promocion.getNumExpediente() != null ) {
				where.append(" AND UPPER(NUM_EXPEDIENTE) like ? ESCAPE  '\\'");
				if (startsWith){
					params.add(promocion.getNumExpediente().toUpperCase() +"%");
				}else{
					params.add("%"+promocion.getNumExpediente().toUpperCase() +"%");
				}
				where.append(" AND NUM_EXPEDIENTE IS NOT NULL");
		     }	
			Map<String,Object> mapWhere = new HashMap<String, Object>();
			mapWhere.put("query", where);
			mapWhere.put("params", params);
			
			return mapWhere;		
		}
		 /**
		 * Finds rows in the Persona table using like.
		 * 
		 * @param persona
		 *            Persona
		 * @param pagination
		 *            Pagination
		 * @param startsWith
		 *            Boolean
		 * @return List
		 * @throws SQLException 
		 */
		@Override
		@Transactional(readOnly = true, timeout = 500)
		public List<Promocion> buscarPromocionPorExpedYEmplazamiento(Promocion promocion, Direccion direccion) throws SQLException {
			StringBuilder query = new StringBuilder(
					PromocionDaoSql.SQL_FIND_EXP_INICIAL);
			List<Object> params = new ArrayList<Object>();
			if(promocion!=null){
				query.append(PromocionDaoSql.SQL_FIND_EXP_PROMO);
				params.add("%"+promocion.getNumExpediente().toUpperCase() +"%");
			}
			if (promocion!=null && direccion!=null){
				query.append(PromocionDaoSql.SQL_FIND_EXP_UNION);
			}
			if(direccion!=null){
				query.append(PromocionDaoSql.SQL_FIND_EXP_EMPLAZ);
				direccion=direccionesDao.completarDireccion(direccion);
				
				// Provincia
				if (direccion.getProvincia() != null
						&& direccion.getProvincia().getId() != null) {
					params.add(direccion.getProvincia().getId());
				}else{
					params.add(null);
				}
				// Municipio
				if (direccion.getMunicipio() != null
						&& direccion.getMunicipio().getId() != null) {
					params.add(direccion.getMunicipio().getId());
				}else{
					params.add(null);
				}
				// Calle
				if (direccion.getCalle() != null
						&& direccion.getCalle().getCalleCod() != null) {
					params.add(direccion.getCalle().getCalleCod());
				}else{
					params.add(null);
				}
				// Portal
				if (direccion.getPortal()!= null) {
					params.add(direccion.getPortal().getComboDesc());
					params.add(direccion.getPortal().getComboDesc());
				}else{
					params.add(null);
					params.add(null);
				}
			}
			query.append(PromocionDaoSql.SQL_FIND_EXP_FIN);
			//query = JQGridManager.getPaginationQuery(new JQGridRequestDto(Long.valueOf("20"), Long.valueOf("1"), "ORDEN", "DESC"), query);
			return (List<Promocion>) this.jdbcTemplate.query(query.toString(), this.rwMap, params.toArray());
		} 
}

