package com.ejie.ab39a.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
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.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ejie.ab39a.constantes.Constantes;
import com.ejie.ab39a.constantes.SecuenciasSQL;
import com.ejie.ab39a.constantes.TipoEntidadEnum;
import com.ejie.ab39a.model.AccionesPg;
import com.ejie.ab39a.model.ElementosArbol;
import com.ejie.ab39a.model.Entidades;
import com.ejie.ab39a.model.PlanesEstrategicos;
import com.ejie.ab39a.model.PlanesGestion;
import com.ejie.ab39a.model.Planificacion;
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.UtilDate;
import com.gfi.utils.UtilSQL;

/**
 * PlanesGestionDaoImpl.
 * 
 *  
 */

@Repository
@Transactional
public class PlanesGestionDaoImpl implements PlanesGestionDao {

    private static final String[] ORDER_BY_WHITE_LIST = new String[] { "CODPLANG", "CODPLANE", "ANYO", "ANYOINICIO",
            "DESENTIDAD", "FECHAMODIFICACION", "NOMBREEUS", "NOMBREPLANEEUS" };

    private JdbcTemplate jdbcTemplate;
    public static final Logger logger = LoggerFactory.getLogger(PlanesGestionDaoImpl.class);

    /*
     * ROW_MAPPERS
     */
    private RowMapper<PlanesGestion> rwMap = new RowMapper<PlanesGestion>() {
        @Override
        public PlanesGestion mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            PlanesGestion planGestion = new PlanesGestion();
            PlanesEstrategicos planEs = new PlanesEstrategicos();
            Entidades entidad = new Entidades();
            Planificacion plani = new Planificacion();
            planEs.setEntidad(entidad);
            planEs.setPlanificacion(plani);
            planGestion.setPlanesEstrategicos(planEs);

            try {
                planGestion.setCodplang(resultSet.getInt("CODPLANG"));
            } catch (SQLException sqle) { /* no existe la columna */
                PlanesGestionDaoImpl.logger.info("PlanesEstrategicosDaoImpl.rwMap: No existe la columna CODPLANG");
            }
            try {
                planGestion.setFechaModificacion((resultSet.getDate("FECHAMODIFICACION")));
            } catch (SQLException sqle) { /*
                                           * no existe la columna
                                           */
                PlanesGestionDaoImpl.logger.info("PlanesGestionDaoImpl.rwMap: No existe la columna FECHAMODIFICACION");
            }
            try {
                planGestion.setAnyo(resultSet.getLong("ANYO"));
            } catch (SQLException sqle) { /* no existe la columna */
                PlanesGestionDaoImpl.logger.info("PlanesEstrategicosDaoImpl.rwMap: No existe la columna ANYO");
            }
            try {
                planGestion.setNombre(resultSet.getString("NOMBRE"));
            } catch (SQLException sqle) { /* no existe la columna */
                PlanesGestionDaoImpl.logger.info("PlanesEstrategicosDaoImpl.rwMap: No existe la columna NOMBRE");
            }
            try {
                planGestion.setNombreeus(resultSet.getString("NOMBREEUS"));
            } catch (SQLException sqle) { /* no existe la columna */
                PlanesGestionDaoImpl.logger.info("PlanesEstrategicosDaoImpl.rwMap: No existe la columna NOMBREEUS");
            }

            try {
                planGestion.getPlanesEstrategicos().getEntidad().setDesentidad(resultSet.getString("DESENTIDAD"));
            } catch (SQLException sqle) { /* no existe la columna */
                PlanesGestionDaoImpl.logger.info("PlanesEstrategicosDaoImpl.rwMap: No existe la columna DESENTIDAD");
            }
            try {
                planGestion.getPlanesEstrategicos().getPlanificacion().setFechainicio(resultSet.getInt("FINICIO"));
            } catch (SQLException sqle) { /* no existe la columna */
                PlanesGestionDaoImpl.logger.info("PlanesEstrategicosDaoImpl.rwMap: No existe la columna FINICIO");
            }
            try {
                planGestion.getPlanesEstrategicos().getPlanificacion().setFechafin(resultSet.getInt("FFIN"));
            } catch (SQLException sqle) { /* no existe la columna */
                PlanesGestionDaoImpl.logger.info("PlanesEstrategicosDaoImpl.rwMap: No existe la columna FFIN");
            }

            try {
                planGestion.getPlanesEstrategicos().setAnyoinicio(resultSet.getLong("ANYOINICIO"));
            } catch (SQLException sqle) { /* no existe la columna */
                PlanesGestionDaoImpl.logger.info("PlanesEstrategicosDaoImpl.rwMap: No existe la columna ANYOINICIO");
            }

            try {
                planGestion.getPlanesEstrategicos().setAnyofin(resultSet.getLong("ANYOFIN"));
            } catch (SQLException sqle) { /* no existe la columna */
                PlanesGestionDaoImpl.logger.info("PlanesEstrategicosDaoImpl.rwMap: No existe la columna ANYOFIN");
            }

            try {
                planGestion.getPlanesEstrategicos().setNombre(resultSet.getString("NOMBREPLANE"));
            } catch (SQLException sqle) { /* no existe la columna */
                PlanesGestionDaoImpl.logger.info("PlanesEstrategicosDaoImpl.rwMap: No existe la columna NOMBREPLANE");
            }
            try {
                planGestion.getPlanesEstrategicos().setNombreeus(resultSet.getString("NOMBREPLANEEUS"));
            } catch (SQLException sqle) { /* no existe la columna */
                PlanesGestionDaoImpl.logger
                        .info("PlanesEstrategicosDaoImpl.rwMap: No existe la columna NOMBREPLANEEUS");
            }

            return planGestion;

        }
    };

    private RowMapper<PlanesGestion> rwMapPK = new RowMapper<PlanesGestion>() {
        @Override
        public PlanesGestion mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            return new PlanesGestion(resultSet.getInt("CODPLANG"));
        }
    };

    /**
     * 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 PlanesGestion table.
     *
     * @param planesgestion
     *            PlanesGestion
     * @return PlanesGestion
     */
    @Override
    public PlanesGestion add(PlanesGestion planesgestion) {
        StringBuilder query = new StringBuilder(
                "INSERT INTO PLANES_GESTION (CODPLANG, CODPLANE, ANYO,NOMBRE,NOMBREEUS, FECHA_MODIFICACION) VALUES (?,?,?,?,?,?)");
        Integer idPlanesGe = SecuenciasSQL.PLANES_GESTION.getNextInteger(jdbcTemplate);
        planesgestion.setCodplang(idPlanesGe);
        this.jdbcTemplate.update(query.toString(), planesgestion.getCodplang(),
                planesgestion.getPlanesEstrategicos().getCodplane(), planesgestion.getAnyo(), planesgestion.getNombre(),
                planesgestion.getNombreeus(), UtilDate.currentDate());
        return planesgestion;
    }

    /**
     * Updates a single row in the PlanesGestion table.
     *
     * @param planesgestion
     *            PlanesGestion
     * @return PlanesGestion
     */
    @Override
    public PlanesGestion update(PlanesGestion planesgestion) {
        StringBuilder query = new StringBuilder(
                "UPDATE PLANES_GESTION SET CODPLANE=?, ANYO=?,NOMBRE=?,NOMBREEUS=?, FECHA_MODIFICACION=SYSDATE");
        query.append(" WHERE CODPLANG=?");
        this.jdbcTemplate.update(query.toString(), planesgestion.getPlanesEstrategicos().getCodplane(),
                planesgestion.getAnyo(), planesgestion.getNombre(), planesgestion.getNombreeus(),
                planesgestion.getCodplang());
        return planesgestion;
    }

    /**
     * Finds a single row in the PlanesGestion table.
     *
     * @param planesgestion
     *            PlanesGestion
     * @return PlanesGestion
     */
    @Override
    @Transactional(readOnly = true)
    public PlanesGestion find(PlanesGestion planesgestion) {
        // SELECT - FROM
        List<Object> params = new ArrayList<Object>();
        StringBuilder query = new StringBuilder(this.getSelectCompletaPlanesGestionQuery(planesgestion, params));

        List<PlanesGestion> planesgestionList = this.jdbcTemplate.query(query.toString(), this.rwMap, params.toArray());
        return DataAccessUtils.uniqueResult(planesgestionList);
    }

    /**
     * Removes a single row in the PlanesGestion table.
     *
     * @param planesgestion
     *            PlanesGestion
     */
    @Override
    public void remove(PlanesGestion planesgestion) {
        // String query = "DELETE FROM PLANES_GESTION WHERE CODPLANG=?";
        String query = "UPDATE PLANES_GESTION SET BAJA=1,FECHA_BAJA=SYSDATE, FECHA_MODIFICACION=SYSDATE, CODTECNICOBAJA=? WHERE CODPLANG=?";
        this.jdbcTemplate.update(query, planesgestion.getPlanesEstrategicos().getCodTecnico(),
                planesgestion.getCodplang());
    }

    /**
     * Actualiza la fecha de actualización de un plan de gestion
     *
     * @param planesgestion
     *            PlanesGestion
     */
    @Override
    public void updateActualizar(PlanesGestion planesgestion) {
        String query = "UPDATE PLANES_GESTION SET FECHA_MODIFICACION=SYSDATE WHERE CODPLANG=?";
        this.jdbcTemplate.update(query, planesgestion.getCodplang());
    }

    /**
     * Actualiza la fecha de actualización de un plan de gestion
     *
     * @param planesgestion
     *            PlanesGestion
     */
    @Override
    public void updateActualizar(ElementosArbol elem) {
        String query = "UPDATE PLANES_GESTION SET FECHA_MODIFICACION=SYSDATE WHERE CODPLANG= (select CODPLANG from elementos_pg where CODELEMPG = ?)";
        this.jdbcTemplate.update(query, elem.getIdElementoA());
    }

    /**
     * Actualiza la fecha de actualización de un plan de gestion
     *
     * @param planesgestion
     *            PlanesGestion
     */
    @Override
    public void updateActualizar(AccionesPg accionesPg) {
        String query = "UPDATE PLANES_GESTION SET FECHA_MODIFICACION = SYSDATE WHERE CODPLANG= (select CODPLANG from elementos_pg where CODELEMPG = ?)";
        this.jdbcTemplate.update(query, accionesPg.getCodelempg());
    }
    /**
     * Finds a list of rows in the PlanesGestion table.
     * 
     * @param planesGestion
     *            PlanesGestion
     * @param jqGridRequestDto
     *            JQGridRequestDto
     * @return List<PlanesGestion>
     */
    @Override
    @Transactional(readOnly = true)
    public List<PlanesGestion> findAll(PlanesGestion planesGestion, JQGridRequestDto jqGridRequestDto) {
        // SELECT - FROM
        StringBuilder query = new StringBuilder(this.getSelectFromQuery(true));

        // Where clause & Params
        Map<String, ?> mapaWhere = this.getWhereMap(planesGestion);
        StringBuilder where = new StringBuilder(" WHERE 1=1 AND t1.BAJA=0 ");
        where.append(mapaWhere.get("query"));
        query.append(where);

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

        if (jqGridRequestDto != null) {
            query = JQGridManager.getPaginationQuery(jqGridRequestDto, query, PlanesGestionDaoImpl.ORDER_BY_WHITE_LIST);
        }

        return this.jdbcTemplate.query(query.toString(), this.rwMap, params.toArray());
    }

    /**
     * Finds rows in the PlanesGestion table using like.
     * 
     * @param planesGestion
     *            PlanesGestion
     * @param jqGridRequestDto
     *            JQGridRequestDto
     * @param startsWith
     *            Boolean
     * @return List<PlanesGestion>
     */
    @Override
    @Transactional(readOnly = true)
    public List<PlanesGestion> findAllLike(PlanesGestion planesGestion, JQGridRequestDto jqGridRequestDto,
            Boolean startsWith) {

        // Params
        List<Object> params = new ArrayList<Object>();
        StringBuilder query = new StringBuilder(this.getSelectCompletaPlanesGestionQuery(planesGestion, params));

        if (jqGridRequestDto != null) {
            query = JQGridManager.getPaginationQuery(jqGridRequestDto, query, PlanesGestionDaoImpl.ORDER_BY_WHITE_LIST);
        }

        return this.jdbcTemplate.query(query.toString(), this.rwMap, params.toArray());
    }

    /**
     * 
     * @param planesGestion
     *            PlanesGestion planesGestion
     * @param jqGridRequestDto
     *            JQGridRequestDto jqGridRequestDto
     * @param startsWith
     *            Boolean startsWith
     * @return List<PlanesGestion> List<PlanesGestion>
     */
    @Override
    @Transactional(readOnly = true)
    public List<PlanesGestion> findAllLikeCopiar(PlanesGestion planesGestion, JQGridRequestDto jqGridRequestDto,
            Boolean startsWith) {
        // SELECT - FROM
        StringBuilder query = new StringBuilder(
                "SELECT DISTINCT t1.CODPLANG CODPLANG, t1.CODPLANE CODPLANE, t1.ANYO ANYO,t1.NOMBRE,t1.NOMBREEUS, t2.CODENTIDAD CODENTIDAD, t3.DESENTIDAD DESENTIDAD, t4.CODPLANIFICACION CODPLANI, t4.DESCAS DESCASPLANI, t4.DESEUS DESCEUSPLANI, t2.ANYOINICIO ANYOINICIO,t2.ANYOFIN ANYOFIN FROM PLANES_GESTION t1, PLANES_ESTRATEGICOS t2, ENTIDADES t3, PLANIFICACION t4");
        if (Constantes.USUARIO_TEC_ENTIDAD.equals(planesGestion.getUsuarioConectado())) {
            query.append(" , PLANESESTR_TECNICOS t6 ");
        }

        // Where clause & Params
        Map<String, ?> mapaWhere = this.getWhereLikeMapCopiar(planesGestion, startsWith);
        StringBuilder where = new StringBuilder(
                " WHERE 1=1  AND t1.BAJA=0  AND t1.CODPLANE = t2.CODPLANE AND t2.CODENTIDAD = t3.CODENTIDAD AND t2.CODPLANIFICACION = t4.CODPLANIFICACION(+) ");
        if (Constantes.USUARIO_TEC_ENTIDAD.equals(planesGestion.getUsuarioConectado())) {
            where.append(" AND t1.CODPLANE = t6.CODPLANE AND t6.CODTECNICO = "
                    + planesGestion.getPlanesEstrategicos().getCodTecnico());
        }
        where.append(mapaWhere.get("query"));
        query.append(where);

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

        if (jqGridRequestDto != null) {
            query = JQGridManager.getPaginationQuery(jqGridRequestDto, query, PlanesGestionDaoImpl.ORDER_BY_WHITE_LIST);
        }

        return this.jdbcTemplate.query(query.toString(), this.rwMap, params.toArray());
    }

    /**
     * Recupera la select (union) para la busqueda de planes de gestion
     * 
     * @param planesGestion
     *            PlanesGestion
     * @param params
     *            List<Object>
     * @return String
     */
    private String getSelectCompletaPlanesGestionQuery(PlanesGestion planesGestion, List<Object> params) {
        StringBuilder query = new StringBuilder(Constantes.STRING_BUILDER_INIT);
        if (Constantes.USUARIO_TEC_ENTIDAD.equals(planesGestion.getUsuarioConectado())) {
            if (planesGestion.getPlanesEstrategicos() != null
                    && planesGestion.getPlanesEstrategicos().getEntidad() != null
                    && planesGestion.getPlanesEstrategicos().getEntidad().getTipoEntidad() != null) {
                if (planesGestion.getPlanesEstrategicos().getEntidad().getTipoEntidad()
                        .longValue() == TipoEntidadEnum.HERRI_ADMINISTRAZIOA.getId()) {

                    query.append(this.getSelectFromQueryTecnicoAdmon(planesGestion, params));

                } else if (planesGestion.getPlanesEstrategicos().getEntidad().getTipoEntidad()
                        .longValue() == TipoEntidadEnum.ENPRESA_PRIBATUA.getId()
                        || planesGestion.getPlanesEstrategicos().getEntidad().getTipoEntidad()
                                .longValue() == TipoEntidadEnum.ENTE_PUBLIKOA.getId()) {

                    query.append(this.getSelectFromQueryTecnicoEmpresa(planesGestion, params));

                }

            }
        } else if (Constantes.USUARIO_HPS.equals(planesGestion.getUsuarioConectado())) {
            // Informamos del filtro por Entidad

            if (planesGestion.getPlanesEstrategicos() != null
                    && planesGestion.getPlanesEstrategicos().getTipoEntidad() != null) {
                Integer tipoEntPG = Integer.parseInt(planesGestion.getPlanesEstrategicos().getTipoEntidad());

                if (tipoEntPG == Constantes.TODOS_TIPOENTI) {
                    query.append(this.getSelectFromQueryTecnicoAdmon(planesGestion, params));
                    query.append(" UNION ");
                    query.append(this.getSelectFromQueryTecnicoEmpresa(planesGestion, params));
                } else if (tipoEntPG == Constantes.HERRI_ADMINISTRAZIOA) {
                    query.append(this.getSelectFromQueryTecnicoAdmon(planesGestion, params));
                } else if (tipoEntPG == Constantes.ENTE_PUBLIKOA || tipoEntPG == Constantes.ENPRESA_PRIBATUA) {
                    query.append(this.getSelectFromQueryTecnicoEmpresa(planesGestion, params));
                    query.append("AND t3.TIPO_ENTIDAD =");
                    query.append(tipoEntPG);
                }
            } else {
                query.append(this.getSelectFromQueryTecnicoAdmon(planesGestion, params));
                query.append(" UNION ");
                query.append(this.getSelectFromQueryTecnicoEmpresa(planesGestion, params));
            }
        }
        return query.toString();
    }

    /*
     * OPERACIONES RUP_TABLE
     */

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

        // Where clause & Params
        Map<String, ?> mapaWhere = this.getWhereMap(planesGestion);
        StringBuilder where = new StringBuilder(" WHERE 1=1 AND t1.BAJA=0  ");
        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 PlanesGestion table using like.
     * 
     * @param planesGestion
     *            PlanesGestion
     * @param startsWith
     *            Boolean
     * @return Long
     */
    @Override
    @Transactional(readOnly = true)
    public Long findAllLikeCount(PlanesGestion planesGestion, Boolean startsWith) {
        StringBuilder query = new StringBuilder(Constantes.STRING_BUILDER_INIT);
        // Params
        List<Object> params = new ArrayList<Object>();
        query.append("SELECT COUNT(1) FROM ( ");
        query.append(this.getSelectCompletaPlanesGestionQuery(planesGestion, params));
        query.append(")");

        return this.jdbcTemplate.queryForObject(query.toString(), params.toArray(), Long.class);
    }

    /**
     * Counts rows in the PlanesGestion table using like.
     * 
     * @param planesGestion
     *            PlanesGestion
     * @param startsWith
     *            Boolean
     * @return Long
     */
    @Override
    @Transactional(readOnly = true)
    public Long findAllLikeCopiarCount(PlanesGestion planesGestion, Boolean startsWith) {
        StringBuilder query = new StringBuilder(
                "SELECT COUNT(distinct codplang) FROM PLANES_GESTION t1, PLANES_ESTRATEGICOS t2, ENTIDADES t3, PLANIFICACION t4 ");
        if (Constantes.USUARIO_TEC_ENTIDAD.equals(planesGestion.getUsuarioConectado())) {
            query.append(" , PLANESESTR_TECNICOS t6 ");
        }
        // Where clause & Params
        Map<String, ?> mapaWhere = this.getWhereLikeMapCopiar(planesGestion, startsWith);
        StringBuilder where = new StringBuilder(
                " WHERE 1=1 AND t1.BAJA=0  AND t1.CODPLANE = t2.CODPLANE AND t2.CODENTIDAD = t3.CODENTIDAD AND t2.CODPLANIFICACION = t4.CODPLANIFICACION(+) ");
        if (Constantes.USUARIO_TEC_ENTIDAD.equals(planesGestion.getUsuarioConectado())) {
            where.append(" AND t1.CODPLANE = t6.CODPLANE AND t6.CODTECNICO = "
                    + planesGestion.getPlanesEstrategicos().getCodTecnico());
        }

        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 PlanesGestion selected for rup_table
     * 
     * @param planesGestion
     *            PlanesGestion
     * @param jqGridRequestDto
     *            JQGridRequestDto
     * @param startsWith
     *            Boolean
     * @return List<TableRowDto<PlanesGestion>>
     */
    @Override
    public List<TableRowDto<PlanesGestion>> reorderSelection(PlanesGestion planesGestion,
            JQGridRequestDto jqGridRequestDto, Boolean startsWith) {
        // SELECT - FROM
        StringBuilder query = new StringBuilder(this.getSelectFromQuery(true));

        // FILTRADO
        // Where clause & Params
        Map<String, ?> mapaWhere = this.getWhereLikeMap(planesGestion, startsWith);
        StringBuilder where = new StringBuilder(" WHERE 1=1 AND t1.BAJA=0  ");
        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,
                PlanesGestion.class, filterParamList, "CODPLANG");

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

    /**
     * Remove multiple method for rup_table
     * 
     * @param filterPlanesGestion
     *            PlanesGestion
     * @param jqGridRequestDto
     *            JQGridRequestDto
     * @param startsWith
     *            Boolean
     */
    @Override
    public void removeMultiple(PlanesGestion filterPlanesGestion, JQGridRequestDto jqGridRequestDto,
            Boolean startsWith) {
        // SELECT - FROM

        StringBuilder query = new StringBuilder(this.getSelectFromQuery(true));

        // WHERE
        Map<String, Object> mapaWhere = this.getWhereLikeMap(filterPlanesGestion, startsWith);
        StringBuilder where = new StringBuilder(" WHERE 1=1 AND t1.BAJA=0  ");
        where.append(mapaWhere.get("query"));
        query.append(where);

        @SuppressWarnings(value = "unchecked")
        List<Object> params = (List<Object>) mapaWhere.get("params");

        StringBuilder sbRemoveMultipleSQL = JQGridManager.getRemoveMultipleQuery(jqGridRequestDto, PlanesGestion.class,
                query, params, "CODPLANG");
        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 DISTINCT t1.CODPLANG CODPLANG, t1.CODPLANE CODPLANE, t1.ANYO
        // ANYO, t2.CODENTIDAD CODENTIDAD, t3.DESENTIDAD DESENTIDAD");// ,
        // // t4.CODPLANIFICACION
        // // CODPLANI,
        // // t4.DESCAS
        // // DESCASPLANI,
        // // t4.DESEUS
        // // DESCEUSPLANI
        // // ");
        // if (includeFrom) {
        // query.append(" FROM PLANES_GESTION t1, PLANES_ESTRATEGICOS t2,
        // ENTIDADES t3 ");// ,
        // // PLANIFICACION
        // // t4
        // // ");
        // }
        // return query.toString();

        StringBuilder query = new StringBuilder(Constantes.STRING_BUILDER_INIT);
        query.append(
                "SELECT t1.CODPLANG CODPLANG,  t1.CODPLANE CODPLANE, t1.ANYO ANYO,t1.NOMBRE,t1.NOMBREEUS,  t2.CODENTIDAD CODENTIDAD, t3.DESENTIDAD DESENTIDAD ");
        if (includeFrom) {
            query.append(
                    "  FROM PLANES_GESTION t1 INNER JOIN PLANES_ESTRATEGICOS t2 ON t1.CODPLANE = t2.CODPLANE INNER JOIN ENTIDADES t3 ON t2.CODENTIDAD = t3.CODENTIDAD ");
        }
        return query.toString();
    }

    /**
     * Devuelve la Select con todos los campos. Incluye el From si se indica.
     * 
     * @return StringBuilder
     */
    private StringBuilder getSelectFromQueryTecnicoAdmon(PlanesGestion planesGestion, List<Object> params) {

        StringBuilder query = new StringBuilder(Constantes.STRING_BUILDER_INIT);
        query.append(
                " SELECT DISTINCT t1.CODPLANG CODPLANG, t1.FECHA_MODIFICACION FECHAMODIFICACION, t1.CODPLANE CODPLANE, t1.ANYO ANYO,t1.NOMBRE,t1.NOMBREEUS,  T2.CODENTIDAD CODENTIDAD, t3.DESENTIDAD DESENTIDAD, ");
        query.append(
                "t4.CODPLANIFICACION CODPLANI, t4.FECHAINICIO FINICIO, T4.FECHAFIN FFIN,t2.NOMBRE  AS NOMBREPLANE,  t2.NOMBREEUS AS NOMBREPLANEEUS, ");
        query.append("t4.FECHAINICIO ANYOINICIO, T4.FECHAFIN ANYOFIN ");
        query.append(
                " FROM (SELECT MAX(t5.CODVERSION) CODVERSION, T5.CODPLANE FROM PLANES_ESTRATEGICOS t5 GROUP BY T5.CODPLANE) PLANES,  ");
        query.append(" PLANES_GESTION t1, PLANES_ESTRATEGICOS T2, ENTIDADES t3, PLANIFICACION t4 ");
        if (Constantes.USUARIO_TEC_ENTIDAD.equals(planesGestion.getUsuarioConectado())) {
            query.append(" , PLANESESTR_TECNICOS t6 ");
        }
        query.append(this.getWhereTecnicoAdmon(planesGestion, params));
        return query;
    }

    /**
     * Devuelve la Select con todos los campos. Incluye el From si se indica.
     * 
     * @return StringBuilder
     */
    private StringBuilder getWhereTecnicoAdmon(PlanesGestion planesGestion, List<Object> params) {

        StringBuilder where = new StringBuilder(Constantes.STRING_BUILDER_INIT);
        where.append(" WHERE 1 = 1 AND t1.BAJA=0  ");
        where.append(" AND t1.CODPLANE = PLANES.CODPLANE ");
        where.append(" AND t2.CODPLANE = PLANES.CODPLANE ");
        where.append(" AND t2.CODVERSION = PLANES.CODVERSION ");
        where.append(" AND T3.CODENTIDAD=T2.CODENTIDAD ");
        where.append(" AND T3.TIPO_ENTIDAD=? ");
        params.add(TipoEntidadEnum.HERRI_ADMINISTRAZIOA.getId());
        where.append(" AND T2.CODPLANIFICACION = t4.CODPLANIFICACION ");
        if (Constantes.USUARIO_TEC_ENTIDAD.equals(planesGestion.getUsuarioConectado())) {
            where.append(" AND t1.CODPLANE = t6.CODPLANE ");
        }
        if (planesGestion.getAnyo() != null) {
            where.append("  AND t1.ANYO = ?");
            params.add(planesGestion.getAnyo());
        }
        if (planesGestion.getPlanesEstrategicos() != null
                && planesGestion.getPlanesEstrategicos().getEntidad() != null) {
            Entidades entidad = planesGestion.getPlanesEstrategicos().getEntidad();

            if (entidad.getCodentidad() != null) {
                where.append(" AND T2.CODENTIDAD = ? ");
                params.add(entidad.getCodentidad());
            }

            if (entidad.getDesentidad() != null && entidad.getDesentidad().trim().length() > 0) {
                UtilSQL.createFilterLikeSQL(where, params, "T3.DESENTIDAD", entidad.getDesentidad(), false);
                // where.append(" AND TRANSLATE (UPPER (t3.DESENTIDAD),
                // 'ÁÉÍÓÚÜ', 'AEIOUU') LIKE ?");
                // params.add("%" + entidad.getDesentidad().toUpperCase() +
                // "%");
            }
        }
        if (Constantes.USUARIO_TEC_ENTIDAD.equals(planesGestion.getUsuarioConectado())) {
            where.append(" AND  t6.CODTECNICO = ? ");
            params.add(planesGestion.getPlanesEstrategicos().getCodTecnico());
        }

        if (planesGestion.getCodplang() != null) {
            where.append(" AND t1.CODPLANG = ?");
            params.add(planesGestion.getCodplang());
        }
        return where;
    }

    /**
     * Devuelve la Select con todos los campos. Incluye el From si se indica.
     * 
     * @return StringBuilder
     */
    private StringBuilder getSelectFromQueryTecnicoEmpresa(PlanesGestion planesGestion, List<Object> params) {

        StringBuilder query = new StringBuilder(Constantes.STRING_BUILDER_INIT);
        query.append(
                " SELECT DISTINCT t1.CODPLANG CODPLANG, t1.FECHA_MODIFICACION FECHAMODIFICACION, t1.CODPLANE CODPLANE, t1.ANYO ANYO,t1.NOMBRE,t1.NOMBREEUS,  T2.CODENTIDAD CODENTIDAD, t3.DESENTIDAD DESENTIDAD, ");
        query.append(
                "TO_NUMBER(NULL) CODPLANI,  T2.ANYOINICIO FINICIO, T2.ANYOFIN FFIN,t2.NOMBRE  AS NOMBREPLANE,t2.NOMBREEUS AS NOMBREPLANEEUS, ");
        query.append("T2.ANYOINICIO ANYOINICIO, T2.ANYOFIN ANYOFIN");
        query.append(
                " FROM (SELECT MAX(T5.CODVERSION) CODVERSION, T5.CODPLANE CODPLANE FROM PLANES_ESTRATEGICOS t5 GROUP BY T5.CODPLANE) PLANES, ");
        query.append(" PLANES_GESTION t1, PLANES_ESTRATEGICOS t2, ENTIDADES t3 ");
        if (Constantes.USUARIO_TEC_ENTIDAD.equals(planesGestion.getUsuarioConectado())) {
            query.append(" , PLANESESTR_TECNICOS t6 ");
        }
        query.append(this.getWhereTecnicoEmpresa(planesGestion, params));
        return query;
    }

    /**
     * Devuelve la Select con todos los campos. Incluye el From si se indica.
     * 
     * @return StringBuilder
     */
    private StringBuilder getWhereTecnicoEmpresa(PlanesGestion planesGestion, List<Object> params) {

        StringBuilder where = new StringBuilder(Constantes.STRING_BUILDER_INIT);
        where.append("WHERE 1 = 1  AND t1.BAJA=0  ");
        where.append(" AND t1.CODPLANE = PLANES.CODPLANE ");
        where.append(" AND T2.CODPLANE=PLANES.CODPLANE ");
        where.append(" AND T2.CODVERSION=PLANES.CODVERSION ");
        where.append(" AND T3.CODENTIDAD=T2.CODENTIDAD ");
        if (Constantes.USUARIO_TEC_ENTIDAD.equals(planesGestion.getUsuarioConectado())) {
            where.append(" AND t1.CODPLANE  = t6.CODPLANE ");
        }
        where.append(" AND T3.TIPO_ENTIDAD IN (?,?) ");
        params.add(TipoEntidadEnum.ENPRESA_PRIBATUA.getId());
        params.add(TipoEntidadEnum.ENTE_PUBLIKOA.getId());

        if (Constantes.USUARIO_TEC_ENTIDAD.equals(planesGestion.getUsuarioConectado())) {
            where.append(" and t6.CODTECNICO = ? ");
            params.add(planesGestion.getPlanesEstrategicos().getCodTecnico());
        }

        if (planesGestion.getAnyo() != null) {
            where.append("  AND t1.ANYO = ?");
            params.add(planesGestion.getAnyo());
        }
        if (planesGestion.getPlanesEstrategicos() != null
                && planesGestion.getPlanesEstrategicos().getEntidad() != null) {
            Entidades entidad = planesGestion.getPlanesEstrategicos().getEntidad();

            if (entidad.getCodentidad() != null) {
                where.append(" AND T2.CODENTIDAD= ? ");
                params.add(entidad.getCodentidad());
            }

            if (entidad.getDesentidad() != null && entidad.getDesentidad().trim().length() > 0) {
                UtilSQL.createFilterLikeSQL(where, params, "T3.DESENTIDAD", entidad.getDesentidad(), false);
                // where.append(" AND TRANSLATE (UPPER (t3.DESENTIDAD),
                // 'ÁÉÍÓÚÜ', 'AEIOUU') LIKE ?");
                // params.add(
                // "%" +
                // planesGestion.getPlanesEstrategicos().getEntidad().getDesentidad().toUpperCase()
                // + "%");
            }
        }
        if (planesGestion.getCodplang() != null) {
            where.append(" AND t1.CODPLANG = ?");
            params.add(planesGestion.getCodplang());
        }
        return where;
    }

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

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

        if (planesGestion != null) {
            if (planesGestion.getCodplang() != null) {
                UtilSQL.createFilterSQL(where, params, "t1.CODPLANG", planesGestion.getCodplang());
            }
            if (planesGestion.getPlanesEstrategicos() != null
                    && planesGestion.getPlanesEstrategicos().getCodplane() != null) {
                UtilSQL.createFilterSQL(where, params, "t1.CODPLANE",
                        planesGestion.getPlanesEstrategicos().getCodplane());
            }
            if (planesGestion.getAnyo() != null) {
                UtilSQL.createFilterSQL(where, params, "t1.ANYO", planesGestion.getAnyo());
            }
        }

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

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

        if (planesGestion != null) {
            if (planesGestion.getCodplang() != null) {
                UtilSQL.createFilterLikeSQL(where, params, "t1.CODPLANG", planesGestion.getCodplang(), startsWith);
            }
            if (planesGestion.getPlanesEstrategicos() != null
                    && planesGestion.getPlanesEstrategicos().getCodplane() != null) {
                UtilSQL.createFilterLikeSQL(where, params, "t1.CODPLANE",
                        planesGestion.getPlanesEstrategicos().getCodplane(), startsWith);
            }
            if (planesGestion.getAnyo() != null) {
                UtilSQL.createFilterLikeSQL(where, params, "t1.ANYO", planesGestion.getAnyo(), startsWith);
            }
            if (planesGestion.getPlanesEstrategicos() != null
                    && planesGestion.getPlanesEstrategicos().getEntidad() != null
                    && planesGestion.getPlanesEstrategicos().getEntidad().getDesentidad() != null) {
                UtilSQL.createFilterLikeSQL(where, params, "t3.DESENTIDAD",
                        planesGestion.getPlanesEstrategicos().getEntidad().getDesentidad(), startsWith);
            }

            if (planesGestion.getPlanesEstrategicos() != null
                    && planesGestion.getPlanesEstrategicos().getEntidades() != null
                    && !planesGestion.getPlanesEstrategicos().getEntidades().isEmpty()) {
                where.append(UtilSQL.AND);
                where.append(" t2.CODENTIDAD IN (");
                for (Entidades entidad : planesGestion.getPlanesEstrategicos().getEntidades()) {
                    if (entidad.getTipoEntidad().intValue() == planesGestion.getPlanesEstrategicos().getEntidad()
                            .getTipoEntidad().intValue()) {
                        where.append("?,");
                        params.add(entidad.getCodentidad());
                    }
                }
                where = new StringBuilder(where.substring(0, where.length() - 1));
                where.append(") ");
            }
        }

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

        return mapWhere;
    }

    /**
     * 
     * @param planesGestion
     * @param startsWith
     * @return
     */
    private Map<String, Object> getWhereLikeMapCopiar(PlanesGestion planesGestion, Boolean startsWith) {

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

        if (planesGestion != null) {
            if (planesGestion.getCodplang() != null) {
                UtilSQL.createFilterLikeSQL(where, params, "t1.CODPLANG", planesGestion.getCodplang(), startsWith);
            }
            if (planesGestion.getPlanesEstrategicos() != null
                    && planesGestion.getPlanesEstrategicos().getCodplane() != null) {
                UtilSQL.createFilterLikeSQL(where, params, "t1.CODPLANE",
                        planesGestion.getPlanesEstrategicos().getCodplane(), startsWith);
            }
            if (planesGestion.getAnyo() != null) {
                UtilSQL.createFilterLikeSQL(where, params, "t1.ANYO", planesGestion.getAnyo(), startsWith);
            }
            if (planesGestion.getPlanesEstrategicos() != null
                    && planesGestion.getPlanesEstrategicos().getEntidad() != null
                    && planesGestion.getPlanesEstrategicos().getEntidad().getDesentidad() != null) {
                UtilSQL.createFilterLikeSQL(where, params, "t3.DESENTIDAD",
                        planesGestion.getPlanesEstrategicos().getEntidad().getDesentidad(), startsWith);
            }

            if (planesGestion.getPlanesEstrategicos() != null
                    && planesGestion.getPlanesEstrategicos().getEntidades() != null
                    && !planesGestion.getPlanesEstrategicos().getEntidades().isEmpty()) {
                where.append(UtilSQL.AND);
                where.append(" t2.CODENTIDAD IN (");
                for (Entidades entidad : planesGestion.getPlanesEstrategicos().getEntidades()) {
                    if (entidad.getTipoEntidad().intValue() == planesGestion.getPlanesEstrategicos().getEntidad()
                            .getTipoEntidad().intValue()) {
                        where.append("?,");
                        params.add(entidad.getCodentidad());
                    }
                }
                where = new StringBuilder(where.substring(0, where.length() - 1));
                where.append(") ");
            }
        }

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

        return mapWhere;
    }

    /**
     * Operación que carga los planes de gestión
     * 
     * @param planesGestion
     * @return void
     */
    @Override
    public void generarPlanesGestion(PlanesGestion planesGestion) {
        PlanesGestionDaoImpl.logger.info("generarPlanEstrategico Inicio");
        StringBuilder query = new StringBuilder(Constantes.STRING_BUILDER_INIT);
        query.append("{call GENERA_PLAN_GESTION (?)}");
        PlanesGestionDaoImpl.logger.info(query.toString());
        final String procedureCall = query.toString();
        final int codPlanGe = planesGestion.getCodplang();

        SqlParameter parametro = new SqlParameter(Types.NUMERIC);

        List<SqlParameter> paramArray = new ArrayList<SqlParameter>();
        paramArray.add(parametro);

        this.jdbcTemplate.call(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection connection) throws SQLException {

                CallableStatement callableStatement = connection.prepareCall(procedureCall);
                callableStatement.setInt(1, codPlanGe);
                PlanesGestionDaoImpl.logger.info("codPlanGe");
                return callableStatement;

            }
        }, paramArray);

    }

    /**
     * 
     * @param planesGestion
     * @return
     */
    @Override
    public Integer crearCopiaPlanGestion(Integer codplanorigen, Integer codplandestino) {
        PlanesGestionDaoImpl.logger.info("generarPlanGestion Inicio");
        StringBuilder query = new StringBuilder(Constantes.STRING_BUILDER_INIT);
        query.append("{call COPIA_PLAN_GESTION (?,?)}");
        PlanesGestionDaoImpl.logger.info(query.toString());
        final String procedureCall = query.toString();
        final int codPlanGesOrigen = codplanorigen;
        final int codPlanGesDestino = codplandestino;

        SqlParameter parametro = new SqlParameter(Types.NUMERIC);
        SqlParameter parametro2 = new SqlParameter(Types.NUMERIC);

        List<SqlParameter> paramArray = new ArrayList<SqlParameter>();
        paramArray.add(parametro);
        paramArray.add(parametro2);

        this.jdbcTemplate.call(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection connection) throws SQLException {

                CallableStatement callableStatement = connection.prepareCall(procedureCall);
                callableStatement.setInt(Constantes.NUM_UNO, codPlanGesOrigen);
                callableStatement.setInt(Constantes.NUM_DOS, codPlanGesDestino);
                PlanesGestionDaoImpl.logger.info("codPlanGes");
                PlanesGestionDaoImpl.logger.info("codEntidad");
                return callableStatement;

            }
        }, paramArray);
        return codPlanGesDestino;
    }
}
