package com.ejie.aa21b.dao;

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.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.aa21b.model.Aa21b97s01;
import com.ejie.aa21b.model.Aa21b98s01;
import com.ejie.x38.dao.RowNumResultSetExtractor;
import com.ejie.x38.dto.JQGridManager;
import com.ejie.x38.dto.JQGridRequestDto;
import com.ejie.x38.dto.TableRowDto;

/**
 * Aa21bDesbloqueosTablasDaoImpl  
 * 
 *  
 */

@Repository
@Transactional
public class Aa21bDesbloqueosTablasDaoImpl implements Aa21bDesbloqueosTablasDao {
	private JdbcTemplate jdbcTemplate;
	private static final Logger logger = LoggerFactory
			.getLogger(Aa21bDesbloqueosTablasDaoImpl.class);

	/*
	 * ROW_MAPPERS
	 */
	private RowMapper<Aa21b97s01> rwMapTablas = new RowMapper<Aa21b97s01>() {
		public Aa21b97s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new Aa21b97s01(resultSet.getString("TABLA97"),
					resultSet.getString("DESC97"),
					resultSet.getString("DESE97"),
					resultSet.getString("CAMPO97"),
					resultSet.getString("CSERV97"), null);
		}
	};

	private RowMapper<Aa21b98s01> rwMapTablaDetalle = new RowMapper<Aa21b98s01>() {
		public Aa21b98s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new Aa21b98s01(resultSet.getString("TABLA98"),
					resultSet.getLong("ORDV98"),
					resultSet.getString("CAMPO98"),
					resultSet.getString("ALIAS98"),
					resultSet.getLong("LONG98"), resultSet.getString("TIPO98"),
					resultSet.getInt("CLAVE98"), resultSet.getInt("VIS98"));
		}
	};

	private RowMapper<Aa21b97s01> rwMapPK = new RowMapper<Aa21b97s01>() {
		public Aa21b97s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new Aa21b97s01(resultSet.getString("TABLA97"));
		}
	};

	private RowMapper<Aa21b97s01> rwMapDescTabla = new RowMapper<Aa21b97s01>() {
		public Aa21b97s01 mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			Aa21b97s01 aa21b97s01 = new Aa21b97s01();
			aa21b97s01.setDesc97(resultSet.getString("DESC97"));
			aa21b97s01.setDese97(resultSet.getString("DESE97"));
			return aa21b97s01;
		}
	};

	/**
	 * 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 Aa21b97s01 table.
	 * 
	 * @param aa21b97s01
	 *            Aa21b97s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @return List<Aa21b97s01>
	 */
	@Transactional(readOnly = true)
	public List<Aa21b97s01> findAll(Aa21b97s01 aa21b97s01,
			JQGridRequestDto jqGridRequestDto) {
		Aa21bDesbloqueosTablasDaoImpl.logger.info("Metodo findAll");
		StringBuilder query = new StringBuilder(
				"SELECT  t1.TABLA97 TABLA97,t1.DESC97 DESC97,NVL(t1.DESE97,t1.DESC97) DESE97,t1.CAMPO97 CAMPO97, t1.CSERV97 CSERV97 ");
		query.append("FROM AA21B97S01 t1 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(aa21b97s01);
		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);
		}
		Aa21bDesbloqueosTablasDaoImpl.logger.info("findAll");
		Aa21bDesbloqueosTablasDaoImpl.logger.info("SQL = " + query.toString());
		Aa21bDesbloqueosTablasDaoImpl.logger.info("PARAMS" + params);
		List<Aa21b97s01> listaTablasBloqueos = (List<Aa21b97s01>) this.jdbcTemplate
				.query(query.toString(), this.rwMapTablas, params.toArray());

		StringBuilder whereBloqPorTabla = new StringBuilder("");
		StringBuilder queryBloqPorTabla = new StringBuilder("");
		for (int i = 0; i < listaTablasBloqueos.size(); i++) {
			queryBloqPorTabla = new StringBuilder("");
			whereBloqPorTabla = new StringBuilder(" WHERE 1=1 ");
			Aa21b97s01 aa21b97s01Bloqueo = new Aa21b97s01();
			aa21b97s01Bloqueo = (Aa21b97s01) listaTablasBloqueos.get(i);
			if (aa21b97s01Bloqueo != null) {
				aa21b97s01Bloqueo.setCservusu(aa21b97s01.getCservusu());
				queryBloqPorTabla = new StringBuilder("SELECT COUNT(1) FROM "
						+ aa21b97s01Bloqueo.getTabla97() + " t1 ");

				// Where clause & Params
				Map<String, ?> mapaWhereBloqPorTabla = this
						.getWhereMapBloqPorTabla(aa21b97s01Bloqueo);

				whereBloqPorTabla.append(mapaWhereBloqPorTabla.get("query"));
				queryBloqPorTabla.append(whereBloqPorTabla);

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

				Aa21bDesbloqueosTablasDaoImpl.logger.info("findAll2");
				Aa21bDesbloqueosTablasDaoImpl.logger.info("SQL = "
						+ queryBloqPorTabla.toString());
				Aa21bDesbloqueosTablasDaoImpl.logger.info("PARAMS2" + params2);
				Long bloqueados = this.jdbcTemplate.queryForLong(
						queryBloqPorTabla.toString(), params2.toArray());

				aa21b97s01Bloqueo.setNumBloqueados(bloqueados);

				listaTablasBloqueos.set(i, aa21b97s01Bloqueo);
			}

		}

		return listaTablasBloqueos;

	}

	/**
	 * Counts rows in the Aa21b97s01 table.
	 * 
	 * @param aa21b97s01
	 *            Aa21b97s01
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllCount(Aa21b97s01 aa21b97s01) {
		Aa21bDesbloqueosTablasDaoImpl.logger.info("M�todo findAllCount");
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM AA21B97S01 t1 ");

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

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

		Aa21bDesbloqueosTablasDaoImpl.logger.info("findAllCount");
		Aa21bDesbloqueosTablasDaoImpl.logger.info("SQL = " + query.toString());
		Aa21bDesbloqueosTablasDaoImpl.logger.info("PARAMS" + params);

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

	/**
	 * Reorder the data list of Aa21b97s01 selected for rup_table
	 * 
	 * @param aa21b97s01
	 *            Aa21b97s01
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @return List<TableRowDto<Aa21b97s01>>
	 */
	@Override
	public List<TableRowDto<Aa21b97s01>> reorderSelection(
			Aa21b97s01 aa21b97s01, JQGridRequestDto jqGridRequestDto) {
		Aa21bDesbloqueosTablasDaoImpl.logger.info("M�todo reorderSelection");
		// SELECT
		StringBuilder query = new StringBuilder(
				"SELECT  t1.TABLA97 TABLA97,t1.DESC97 DESC97,t1.DESE97 DESE97,t1.CAMPO97 CAMPO97 ");
		// FROM
		query.append("FROM AA21B97S01 t1 ");
		// FILTRADO
		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(aa21b97s01);
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		where.append(mapaWhere.get("query"));
		query.append(where);

		// Parámetros de filtrado
		@SuppressWarnings("unchecked")
		List<Object> filterParamList = (List<Object>) mapaWhere.get("params");

		// SQL para la reordenación
		StringBuilder sbReorderSelectionSQL = JQGridManager.getReorderQuery(
				query, jqGridRequestDto, Aa21b97s01.class, filterParamList,
				"TABLA97");

		Aa21bDesbloqueosTablasDaoImpl.logger.info("reorderSelection");
		Aa21bDesbloqueosTablasDaoImpl.logger.info("SQL = "
				+ sbReorderSelectionSQL.toString());
		Aa21bDesbloqueosTablasDaoImpl.logger.info("PARAMs=" + filterParamList);

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

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

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

		where.append(" AND t1.CAMPO97 is not null ");

		if (aa21b97s01 != null && aa21b97s01.getTabla97() != null
				&& !aa21b97s01.getTabla97().trim().equals("")) {
			where.append(" AND t1.TABLA97 = ?");
			params.add(aa21b97s01.getTabla97());
		}

		if (aa21b97s01 != null && aa21b97s01.getDesc97() != null
				&& !aa21b97s01.getDesc97().trim().equals("")) {

			where.append(" AND UPPER(t1.DESC97) like ? ESCAPE  '\\'");
			params.add(aa21b97s01.getDesc97().toUpperCase() + "%");
			where.append(" AND t1.DESC97 IS NOT NULL");
		}

		if (aa21b97s01 != null && aa21b97s01.getDese97() != null
				&& aa21b97s01.getDese97().trim().equals("")) {
			where.append(" AND UPPER(t1.DESE97) like ? ESCAPE  '\\'");
			params.add(aa21b97s01.getDese97().toUpperCase() + "%");
			where.append(" AND t1.DESE97 IS NOT NULL");

		}

		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 Aa21b97s01 entity
	 * 
	 * @param aa21b97s01
	 *            Aa21b97s01 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, ?> getWhereMapBloqPorTabla(Aa21b97s01 aa21b97s01) {

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

		if (aa21b97s01 != null && aa21b97s01.getCampo97() != null) {
			where.append(" AND t1." + aa21b97s01.getCampo97() + "  = ? ");
			params.add("1");
		}

		if (aa21b97s01 != null && aa21b97s01.getCserv97() != null) {
			where.append(" AND t1." + aa21b97s01.getCserv97() + " = ? ");
			params.add(aa21b97s01.getCservusu());
		}
		/*
		 * if (aa21b12s01 != null && aa21b12s01.getCDepar12() != null) {
		 * where.append(" AND t1.CDEPAR12 = ?");
		 * params.add(aa21b12s01.getCDepar12()); }
		 */

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

		return mapWhere;
	}

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

	@Override
	public Aa21b97s01 find(Aa21b97s01 aa21b97s01) {
		Aa21bDesbloqueosTablasDaoImpl.logger.info("M�todo find");
		String query = "SELECT  t1.TABLA97 TABLA97,t1.DESC97 DESC97,t1.DESE97 DESE97,t1.CAMPO97 CAMPO97 FROM AA21B97S01 t1  WHERE t1.TABLA97 = ?  ";

		Aa21bDesbloqueosTablasDaoImpl.logger.info("SQL = " + query);
		Aa21bDesbloqueosTablasDaoImpl.logger.info("PARAM1pk="
				+ aa21b97s01.getTabla97());

		List<Aa21b97s01> aa21b97s01List = this.jdbcTemplate.query(query,
				this.rwMapTablas, aa21b97s01.getTabla97());

		return (Aa21b97s01) DataAccessUtils.uniqueResult(aa21b97s01List);
	}

	@Override
	public List<Aa21b98s01> findDetalle(Aa21b98s01 aa21b98s01) {
		Aa21bDesbloqueosTablasDaoImpl.logger.info("M�todo findDetalle");
		StringBuilder query = new StringBuilder(
				"SELECT  t1.TABLA98 TABLA98,t1.ORDV98 ORDV98,t1.CAMPO98 CAMPO98,t1.ALIAS98 ALIAS98, t1.LONG98 LONG98, t1.TIPO98 TIPO98, t1.CLAVE98 CLAVE98, t1.VIS98 VIS98 ");
		query.append("FROM AA21B98S01 t1 ");
		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		// Where clause & Params

		List<Object> params = new ArrayList<Object>();

		if (aa21b98s01 != null && aa21b98s01.getTabla98() != null
				&& !aa21b98s01.getTabla98().trim().equals("")) {
			where.append(" AND t1.TABLA98 = ?");
			params.add(aa21b98s01.getTabla98());
		}
		query.append(where);
		Aa21bDesbloqueosTablasDaoImpl.logger.info("findDetalle");
		Aa21bDesbloqueosTablasDaoImpl.logger.info("SQL = " + query.toString());
		Aa21bDesbloqueosTablasDaoImpl.logger.info("PARAMs=" + params);

		return (List<Aa21b98s01>) this.jdbcTemplate.query(query.toString(),
				this.rwMapTablaDetalle, params.toArray());

	}

	@Override
	public List<Aa21b97s01> findTabla(Aa21b97s01 aa21b97s01, Boolean startsWith) {
		Aa21bDesbloqueosTablasDaoImpl.logger.info("M�todo findTabla");
		List<Object> params = new ArrayList<Object>();

		StringBuilder where = new StringBuilder(" WHERE 1=1 ");
		StringBuilder order = new StringBuilder("");
		StringBuilder query = new StringBuilder(
				"SELECT DISTINCT t1.DESC97 DESC97, t1.DESE97 DESE97 ");
		query.append("FROM AA21B97S01 t1 ");
		if (aa21b97s01 != null && aa21b97s01.getDesc97() != null) {
			where.append(" AND UPPER(t1.DESC97) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add("%" + aa21b97s01.getDesc97().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b97s01.getDesc97().toUpperCase() + "%");
			}

			order = new StringBuilder(" ORDER BY DESC97 asc ");
		}
		if (aa21b97s01 != null && aa21b97s01.getDese97() != null) {
			where.append(" AND UPPER(t1.DESE97) like ? ESCAPE '\\'");
			if (startsWith) {
				params.add("%" + aa21b97s01.getDese97().toUpperCase() + "%");
			} else {
				params.add("%" + aa21b97s01.getDese97().toUpperCase() + "%");
			}

			order = new StringBuilder(" ORDER BY DESE97 asc ");

		}
		query.append(where);
		query.append(order);
		Aa21bDesbloqueosTablasDaoImpl.logger.info("findTabla");
		Aa21bDesbloqueosTablasDaoImpl.logger.info("SQL = " + query.toString());
		Aa21bDesbloqueosTablasDaoImpl.logger.info("PARAMs=" + params);
		return (List<Aa21b97s01>) this.jdbcTemplate.query(query.toString(),
				this.rwMapDescTabla, params.toArray());
	}

}