package com.ejie.ab39a.dao;
import com.ejie.x38.dao.RowNumResultSetExtractor;
import com.ejie.x38.dto.JQGridManager;
import com.ejie.x38.dto.JQGridRequestDto;
import com.ejie.x38.dto.TableRowDto;
import com.gfi.constants.Constants;
import com.gfi.utils.UtilSQL;
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.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.ab39a.model.Elemento;

/**
 * ElementoDaoImpl.
 *  
 */
 
@Repository
@Transactional
public class ElementoDaoImpl implements ElementoDao {
    
    private static final String[] ORDER_BY_WHITE_LIST = new String[] {"ID_ELEMENTO", "ORDEN", "NIVEL", "PONDERACION", "TIENEELEBI", "NOMBRE", "NOMBRECAS", "ID_EJE", "ID_ELEMENTOPADRE", "HELBURU", "HELBURUCAS"};
    
    private JdbcTemplate jdbcTemplate;
	
	/*
	 * ROW_MAPPERS
	 */
	private RowMapper<Elemento> rwMap = new RowMapper<Elemento>() {
	    @Override
		public Elemento mapRow(ResultSet resultSet, int rowNum) throws SQLException {
           return new Elemento(
               resultSet.getLong("IDELEMENTO"), resultSet.getLong("ORDEN"), resultSet.getLong("NIVEL"), resultSet.getLong("PONDERACION"), resultSet.getInt("TIENEELEBI"), resultSet.getString("NOMBRE"), resultSet.getString("NOMBRECAS"), resultSet.getLong("IDEJE"), resultSet.getLong("IDELEMENTOPADRE"), resultSet.getString("HELBURU"), resultSet.getString("HELBURUCAS")
           ); } } ;
           
	private RowMapper<Elemento> rwMapPK = new RowMapper<Elemento>() {
	    @Override
		public Elemento mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			return new Elemento(
				resultSet.getLong("IDELEMENTO")
           ); } } ;

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

	/*
	 * OPERACIONES CRUD
	 */

    /**
     * Inserts a single row in the Elemento table.
     *
     * @param elemento Elemento
     * @return Elemento
     */
    @Override
	public Elemento add(Elemento elemento) {
    	StringBuilder query = new StringBuilder("INSERT INTO ELEMENTO (ID_ELEMENTO, ORDEN, NIVEL, PONDERACION, TIENEELEBI, NOMBRE, NOMBRECAS, ID_EJE, ID_ELEMENTOPADRE, HELBURU, HELBURUCAS) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
		this.jdbcTemplate.update(query.toString(), elemento.getIdElemento(), elemento.getOrden(), elemento.getNivel(), elemento.getPonderacion(), elemento.getTieneelebi(), elemento.getNombre(), elemento.getNombrecas(), elemento.getIdEje(), elemento.getIdElementopadre(), elemento.getHelburu(), elemento.getHelburucas());
		return elemento;
	}

    /**
     * Updates a single row in the Elemento table.
     *
     * @param elemento Elemento
     * @return Elemento
     */
    @Override
    public Elemento update(Elemento elemento) {
		StringBuilder query = new StringBuilder("UPDATE ELEMENTO SET ORDEN=?, NIVEL=?, PONDERACION=?, TIENEELEBI=?, NOMBRE=?, NOMBRECAS=?, ID_EJE=?, ID_ELEMENTOPADRE=?, HELBURU=?, HELBURUCAS=?");
		query.append(" WHERE ID_ELEMENTO=?");
		this.jdbcTemplate.update(query.toString(), elemento.getOrden(), elemento.getNivel(), elemento.getPonderacion(), elemento.getTieneelebi(), elemento.getNombre(), elemento.getNombrecas(), elemento.getIdEje(), elemento.getIdElementopadre(), elemento.getHelburu(), elemento.getHelburucas(), elemento.getIdElemento());
		return elemento;
	}

    /**
     * Finds a single row in the Elemento table.
     *
     * @param elemento Elemento
     * @return Elemento
     */
    @Override
    @Transactional (readOnly = true)
    public Elemento find(Elemento elemento) {
		// SELECT - FROM
		StringBuilder query = new StringBuilder(this.getSelectFromQuery(true));
		// WHERE 
		query.append(" WHERE t1.ID_ELEMENTO = ?  ");
		
		List<Elemento> elementoList = this.jdbcTemplate.query(query.toString(), this.rwMap, elemento.getIdElemento());
		return DataAccessUtils.uniqueResult(elementoList);
    }

    /**
     * Removes a single row in the Elemento table.
     *
     * @param elemento Elemento
     */
    @Override
    public void remove(Elemento elemento) {
		String query = "DELETE FROM ELEMENTO WHERE ID_ELEMENTO=?";
		this.jdbcTemplate.update(query, elemento.getIdElemento());
    }
    
   /**
    * Finds a list of rows in the Elemento table.
    * 
    * @param elemento Elemento
    * @param jqGridRequestDto JQGridRequestDto
    * @return List<Elemento> 
    */
    @Override
	@Transactional (readOnly = true)
    public List<Elemento> findAll(Elemento elemento, JQGridRequestDto jqGridRequestDto) {
		// SELECT - FROM
		StringBuilder query = new StringBuilder(this.getSelectFromQuery(true));
		
		//Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(elemento); 
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);
		
		List<?> params = (List<?>) mapaWhere.get("params");

		if (jqGridRequestDto != null) {
			query = JQGridManager.getPaginationQuery(jqGridRequestDto, query, ElementoDaoImpl.ORDER_BY_WHITE_LIST);
		}
		
		return this.jdbcTemplate.query(query.toString(), this.rwMap, params.toArray());
	}
	
	/**
	 * Finds rows in the Elemento table using like.
     * 
     * @param elemento Elemento
     * @param jqGridRequestDto JQGridRequestDto
     * @param startsWith Boolean
     * @return List<Elemento>
     */
    @Override
	@Transactional (readOnly = true)
    public List<Elemento> findAllLike(Elemento elemento, JQGridRequestDto jqGridRequestDto, Boolean startsWith) {
		// SELECT - FROM
		StringBuilder query = new StringBuilder(this.getSelectFromQuery(true));
      	
		//Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(elemento,startsWith); 
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);

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

		if (jqGridRequestDto != null) {
			query = JQGridManager.getPaginationQuery(jqGridRequestDto, query, ElementoDaoImpl.ORDER_BY_WHITE_LIST);
		}
		
		return this.jdbcTemplate.query(query.toString(), this.rwMap, params.toArray());
	}

	/*
	 * OPERACIONES RUP_TABLE
	 */
	
    /**
     * Counts rows in the Elemento table.
     * 
     * @param elemento Elemento
     * @return Long
     */
    @Override
    @Transactional (readOnly = true)
    public Long findAllCount(Elemento elemento) {
		StringBuilder query = new StringBuilder("SELECT COUNT(1) FROM ELEMENTO t1 ");
		
		//Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(elemento); 
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);		
		
		List<?> params = (List<?>) mapaWhere.get("params");
		
		return this.jdbcTemplate.queryForObject(query.toString(), params.toArray(), Long.class);
	}	
	
	/**
	 * Counts rows in the Elemento table using like.
     * 
     * @param elemento Elemento
     * @param startsWith Boolean
     * @return Long 
     */
    @Override 
	@Transactional (readOnly = true)
    public Long findAllLikeCount(Elemento elemento, Boolean startsWith) {
		StringBuilder query = new StringBuilder("SELECT COUNT(1) FROM ELEMENTO t1 ");

		//Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(elemento,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.queryForObject(query.toString(), params.toArray(), Long.class);
	}

	/**
	 * Reorder the data list of Elemento selected for rup_table
     * 
     * @param elemento Elemento
     * @param jqGridRequestDto JQGridRequestDto
     * @param startsWith Boolean
     * @return List<TableRowDto<Elemento>> 
     */
	@Override 
	public List<TableRowDto<Elemento>> reorderSelection(Elemento elemento, JQGridRequestDto jqGridRequestDto, Boolean startsWith) {
		// SELECT - FROM
		StringBuilder query = new StringBuilder(this.getSelectFromQuery(true));

		// FILTRADO
		//Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(elemento,startsWith); 
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);
		
		// Parámetros de filtrado
		@SuppressWarnings(value = "unchecked")
		List<Object> filterParamList = (List<Object>) mapaWhere.get("params");		
		
		// SQL para la reordenación
		StringBuilder sbReorderSelectionSQL =  JQGridManager.getReorderQuery(query, jqGridRequestDto, Elemento.class, filterParamList, "IDELEMENTO" );

		return this.jdbcTemplate.query(sbReorderSelectionSQL.toString(), new RowNumResultSetExtractor<Elemento>(this.rwMapPK, jqGridRequestDto), filterParamList.toArray());
	}

	/**
	 * Remove multiple method for rup_table
     * 
     * @param filterElemento Elemento
     * @param jqGridRequestDto JQGridRequestDto
     * @param startsWith Boolean 
     */
	@Override
	public void removeMultiple(Elemento filterElemento, JQGridRequestDto jqGridRequestDto, Boolean startsWith) {
		// SELECT - FROM
		/** TODO: select por clave */
		StringBuilder query = new StringBuilder(this.getSelectFromQuery(true));
		
		// WHERE
		Map<String, Object> mapaWhere = this.getWhereLikeMap(filterElemento, startsWith); 
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);
		
		@SuppressWarnings(value = "unchecked")
		List<Object> params = (List<Object>) mapaWhere.get("params");

		StringBuilder sbRemoveMultipleSQL = JQGridManager.getRemoveMultipleQuery(jqGridRequestDto, Elemento.class, query, params, "IDELEMENTO");
		this.jdbcTemplate.update(sbRemoveMultipleSQL.toString(), params.toArray());
	}

	/*
	 * MÉTODOS PRIVADOS
	 */
	 
	/**
	 * Devuelve la Select con todos los campos. Incluye el From si se indica.
	 * 
	 * @param includeFrom
	 *            boolean
	 * @return String
	 */
	private String getSelectFromQuery(boolean includeFrom) {
		StringBuilder query = new StringBuilder("SELECT t1.ID_ELEMENTO IDELEMENTO, t1.ORDEN ORDEN, t1.NIVEL NIVEL, t1.PONDERACION PONDERACION, t1.TIENEELEBI TIENEELEBI, t1.NOMBRE NOMBRE, t1.NOMBRECAS NOMBRECAS, t1.ID_EJE IDEJE, t1.ID_ELEMENTOPADRE IDELEMENTOPADRE, t1.HELBURU HELBURU, t1.HELBURUCAS HELBURUCAS ");
		if(includeFrom){
			query.append(" FROM ELEMENTO t1 ");
		}
		return query.toString();
	}
	 	
	/**
	 * Returns a map with the needed value to create the conditions to filter by 
	 * the Elemento entity 
	 * 
	 * @param elemento Elemento
	 *            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 (Elemento elemento){
		
		StringBuilder where = new StringBuilder(Constants.STRING_BUILDER_INIT);
		List<Object> params = new ArrayList<Object>();

		if (elemento != null) {
			if (elemento.getIdElemento() != null) {
				UtilSQL.createFilterSQL(where, params, "t1.ID_ELEMENTO", elemento.getIdElemento());
			}
			if (elemento.getOrden() != null) {
				UtilSQL.createFilterSQL(where, params, "t1.ORDEN", elemento.getOrden());
			}
			if (elemento.getNivel() != null) {
				UtilSQL.createFilterSQL(where, params, "t1.NIVEL", elemento.getNivel());
			}
			if (elemento.getPonderacion() != null) {
				UtilSQL.createFilterSQL(where, params, "t1.PONDERACION", elemento.getPonderacion());
			}
			if (elemento.getTieneelebi() != null) {
				UtilSQL.createFilterSQL(where, params, "t1.TIENEELEBI", elemento.getTieneelebi());
			}
			if (elemento.getNombre() != null) {
				UtilSQL.createFilterSQL(where, params, "t1.NOMBRE", elemento.getNombre());
			}
			if (elemento.getNombrecas() != null) {
				UtilSQL.createFilterSQL(where, params, "t1.NOMBRECAS", elemento.getNombrecas());
			}
			if (elemento.getIdEje() != null) {
				UtilSQL.createFilterSQL(where, params, "t1.ID_EJE", elemento.getIdEje());
			}
			if (elemento.getIdElementopadre() != null) {
				UtilSQL.createFilterSQL(where, params, "t1.ID_ELEMENTOPADRE", elemento.getIdElementopadre());
			}
			if (elemento.getHelburu() != null) {
				UtilSQL.createFilterSQL(where, params, "t1.HELBURU", elemento.getHelburu());
			}
			if (elemento.getHelburucas() != null) {
				UtilSQL.createFilterSQL(where, params, "t1.HELBURUCAS", elemento.getHelburucas());
			}
		}

		Map<String,Object> mapWhere = new HashMap<String, Object>();
		mapWhere.put("query", where);
		mapWhere.put("params", params);
		
		return mapWhere;		
	}
	
	/**
	 * Returns a map with the needed value to create the conditions to filter by  
	 * the Elemento entity 
	 * 
	 * @param elemento Elemento
	 *            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.
	 */
	private Map<String, Object> getWhereLikeMap (Elemento elemento, Boolean startsWith){
		
		StringBuilder where = new StringBuilder(Constants.STRING_BUILDER_INIT);
		List<Object> params = new ArrayList<Object>();

		if (elemento != null) {
			if (elemento.getIdElemento() != null) {
				UtilSQL.createFilterLikeSQL(where, params, "t1.ID_ELEMENTO", elemento.getIdElemento(), startsWith);
			}
			if (elemento.getOrden() != null) {
				UtilSQL.createFilterLikeSQL(where, params, "t1.ORDEN", elemento.getOrden(), startsWith);
			}
			if (elemento.getNivel() != null) {
				UtilSQL.createFilterLikeSQL(where, params, "t1.NIVEL", elemento.getNivel(), startsWith);
			}
			if (elemento.getPonderacion() != null) {
				UtilSQL.createFilterLikeSQL(where, params, "t1.PONDERACION", elemento.getPonderacion(), startsWith);
			}
			if (elemento.getTieneelebi() != null) {
				UtilSQL.createFilterLikeSQL(where, params, "t1.TIENEELEBI", elemento.getTieneelebi(), startsWith);
			}
			if (elemento.getNombre() != null) {
				UtilSQL.createFilterLikeSQL(where, params, "t1.NOMBRE", elemento.getNombre(), startsWith);
			}
			if (elemento.getNombrecas() != null) {
				UtilSQL.createFilterLikeSQL(where, params, "t1.NOMBRECAS", elemento.getNombrecas(), startsWith);
			}
			if (elemento.getIdEje() != null) {
				UtilSQL.createFilterLikeSQL(where, params, "t1.ID_EJE", elemento.getIdEje(), startsWith);
			}
			if (elemento.getIdElementopadre() != null) {
				UtilSQL.createFilterLikeSQL(where, params, "t1.ID_ELEMENTOPADRE", elemento.getIdElementopadre(), startsWith);
			}
			if (elemento.getHelburu() != null) {
				UtilSQL.createFilterLikeSQL(where, params, "t1.HELBURU", elemento.getHelburu(), startsWith);
			}
			if (elemento.getHelburucas() != null) {
				UtilSQL.createFilterLikeSQL(where, params, "t1.HELBURUCAS", elemento.getHelburucas(), startsWith);
			}
		}

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

