package com.ejie.aa20b.dao;

import com.ejie.x38.dto.Pagination;
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.context.i18n.LocaleContextHolder;
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.aa20b.model.Clase;
import com.ejie.aa20b.model.Gamas;
import com.ejie.aa20b.model.Servicios;
import com.ejie.aa20b.util.Aa20bConstantes;

/**
 * GamasDaoImpl generated by UDA, 02-mar-2015 11:32:36.
 * 
 * @author UDA
 */

@Repository
@Transactional
public class GamasDaoImpl implements GamasDao {
	private JdbcTemplate jdbcTemplate;
	private RowMapper<Gamas> rwMap = new RowMapper<Gamas>() {
		public Gamas mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			Gamas gama = new Gamas();

			gama.setIdgama(resultSet.getLong("IDGAMA"));
			gama.setIdclase(resultSet.getInt("IDCLASE"));
			gama.setCgama(resultSet.getString("CGAMA"));
			gama.setEgama(resultSet.getString("EGAMA"));
			gama.setSemanas(resultSet.getLong("SEMANAS"));
			gama.setFeini(resultSet.getDate("FEINI"));
			gama.setBaja(resultSet.getString("BAJA"));
			gama.setContrata(resultSet.getString("CONTRATA"));
			gama.setIdservicio(resultSet.getInt("IDSERVICIO"));

			Servicios servicio = new Servicios();
			servicio.setIdservicio(resultSet.getLong("IDSERVICIO"));
			servicio.setCservicio(resultSet.getString("CSERVICIO") == null ? ""
					: resultSet.getString("CSERVICIO"));
			servicio.setEservicio(resultSet.getString("ESERVICIO") == null ? ""
					: resultSet.getString("ESERVICIO"));
			gama.setServicio(servicio);

			Clase clase = new Clase();
			clase.setIdclase(resultSet.getInt("IDCLASE"));
			clase.setCclase(resultSet.getString("CCLASE") == null ? ""
					: resultSet.getString("CCLASE"));
			clase.setEclase(resultSet.getString("ECLASE") == null ? ""
					: resultSet.getString("ECLASE"));
			clase.setBaja(resultSet.getString("BAJACLASE"));
			gama.setClase(clase);

			return gama;
		}
	};

	private RowMapper<Clase> rwMapClase = new RowMapper<Clase>() {
		public Clase mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new Clase(resultSet.getInt("IDCLASE"),
					resultSet.getString("CCLASE"),
					resultSet.getString("ECLASE"), resultSet.getString("BAJA"),
					resultSet.getDate("FEINI"), resultSet.getString("USUPUBLI"));
		}
	};

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

	/**
	 * Inserts a single row in the Gamas table.
	 * 
	 * @param gamas
	 *            Pagination
	 * @return Gamas
	 */
	public Gamas add(Gamas gamas) {
		// Obtener el id correlativo
		Long idCorrelativo = this.jdbcTemplate
				.queryForLong("SELECT GAMA_SEQ.NEXTVAL FROM DUAL");

		gamas.setIdgama(idCorrelativo);

		String query = "INSERT INTO GAMAS (IDGAMA, IDCLASE, CGAMA, EGAMA, SEMANAS, FEINI, BAJA, CONTRATA, IDSERVICIO) VALUES (?,?,?,?,?,?,?,?,?)";
		this.jdbcTemplate.update(query, gamas.getIdgama(), gamas.getIdclase(),
				gamas.getCgama(), gamas.getEgama(), gamas.getSemanas(),
				gamas.getFeini(),
				gamas.getBaja() == null ? "0" : gamas.getBaja(),
				gamas.getContrata() == null ? "0" : gamas.getContrata(),
				gamas.getIdservicio());
		return gamas;
	}

	/**
	 * Updates a single row in the Gamas table.
	 * 
	 * @param gamas
	 *            Pagination
	 * @return Gamas
	 */
	public Gamas update(Gamas gamas) {
		String query = "UPDATE GAMAS SET IDCLASE=?, CGAMA=?, EGAMA=?, SEMANAS=?, FEINI=?, BAJA=?, CONTRATA=?, IDSERVICIO=?  WHERE IDGAMA=?";
		this.jdbcTemplate.update(query, gamas.getIdclase(), gamas.getCgama(),
				gamas.getEgama(), gamas.getSemanas(), gamas.getFeini(),
				gamas.getBaja() == null ? "0" : gamas.getBaja(),
				gamas.getContrata() == null ? "0" : gamas.getContrata(),
				gamas.getIdservicio(), gamas.getIdgama());
		return gamas;
	}

	/**
	 * Marcar o desmarca para contrata todas las gamas de una clase.
	 * 
	 * @param gamas
	 *            Gamas
	 * @return Gamas
	 */
	public Gamas marcarParaContra(Gamas gamas) {
		String query = "UPDATE GAMAS SET CONTRATA=? WHERE IDCLASE=?";
		this.jdbcTemplate
				.update(query, gamas.getContrata(), gamas.getIdclase());
		return gamas;
	}

	/**
	 * Finds a single row in the Gamas table.
	 * 
	 * @param gamas
	 *            Pagination
	 * @return Gamas
	 */
	@Transactional(readOnly = true)
	public Gamas find(Gamas gamas) {
		String query = "SELECT t1.IDGAMA IDGAMA, t1.IDCLASE IDCLASE, t2.CCLASE CCLASE, t2.ECLASE ECLASE,T2.BAJA BAJACLASE, t1.CGAMA CGAMA, t1.EGAMA EGAMA, t1.SEMANAS SEMANAS, t1.FEINI FEINI, t1.BAJA BAJA, t1.CONTRATA CONTRATA, t1.IDSERVICIO IDSERVICIO, t3.CSERVICIO CSERVICIO, t3.ESERVICIO ESERVICIO  FROM GAMAS t1, CLASE t2, SERVICIOS t3  WHERE t1.IDCLASE=t2.IDCLASE(+) AND t1.IDSERVICIO = t3.IDSERVICIO(+) AND t1.IDGAMA = ?  ";

		List<Gamas> gamasList = this.jdbcTemplate.query(query, this.rwMap,
				gamas.getIdgama());
		return (Gamas) DataAccessUtils.uniqueResult(gamasList);
	}

	/**
	 * Removes a single row in the Gamas table.
	 * 
	 * @param gamas
	 *            Pagination
	 * @return
	 */
	public void remove(Gamas gamas) {
		String query = "DELETE FROM GAMAS WHERE IDGAMA=?";
		this.jdbcTemplate.update(query, gamas.getIdgama());
	}

	/**
	 * Finds a List of rows in the Gamas table.
	 * 
	 * @param gamas
	 *            Gamas
	 * @param pagination
	 *            Pagination
	 * @return List
	 */
	@Transactional(readOnly = true)
	public List<Gamas> findAll(Gamas gamas, Pagination pagination) {
		StringBuilder query = new StringBuilder(
				"SELECT  t1.IDGAMA IDGAMA,t1.IDCLASE IDCLASE, t2.CCLASE CCLASE, t2.ECLASE ECLASE,T2.BAJA BAJACLASE,t1.CGAMA CGAMA,t1.EGAMA EGAMA,t1.SEMANAS SEMANAS,t1.FEINI FEINI,t1.BAJA BAJA,t1.CONTRATA CONTRATA, t1.IDSERVICIO IDSERVICIO, t3.CSERVICIO CSERVICIO, t3.ESERVICIO ESERVICIO ");
		query.append("FROM GAMAS t1, CLASE t2, SERVICIOS t3  ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(gamas);
		StringBuilder where = new StringBuilder(
				" WHERE t1.IDCLASE=t2.IDCLASE(+) AND t1.IDSERVICIO = t3.IDSERVICIO(+) ");
		where.append(mapaWhere.get("query"));
		query.append(where);

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

		if (pagination != null) {
			query = pagination.getPaginationQuery(query);
		} else {
			if (LocaleContextHolder.getLocale().getLanguage()
					.equals(Aa20bConstantes.IDIOMA_EUSKERA))
				query.append(" ORDER BY EGAMA");
			else
				query.append(" ORDER BY CGAMA");
		}

		return (List<Gamas>) this.jdbcTemplate.query(query.toString(),
				this.rwMap, params.toArray());
	}

	@Transactional(readOnly = true)
	public List<Clase> findClasesGamaServicio(Gamas gamas, Pagination pagination) {
		StringBuilder query = new StringBuilder(
				"SELECT  DISTINCT(t2.IDCLASE),t2.IDCLASE IDCLASE, t2.CCLASE CCLASE, t2.ECLASE ECLASE,t2.BAJA BAJA, t2.FEINI FEINI, t2.USUPUBLI USUPUBLI ");
		query.append("FROM GAMAS t1, CLASE t2 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMapClase(gamas);
		StringBuilder where = new StringBuilder(
				" WHERE t1.IDCLASE=t2.IDCLASE(+) AND t2.BAJA = 0 ");
		where.append(mapaWhere.get("query"));
		query.append(where);

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

		if (pagination != null) {
			query = pagination.getPaginationQuery(query);
		}

		return (List<Clase>) this.jdbcTemplate.query(query.toString(),
				this.rwMapClase, params.toArray());
	}

	/**
	 * Counts rows in the Gamas table.
	 * 
	 * @param gamas
	 *            Gamas
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllCount(Gamas gamas) {
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM GAMAS t1, CLASE t2, SERVICIOS t3 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(gamas);
		StringBuilder where = new StringBuilder(
				" WHERE t1.IDCLASE=t2.IDCLASE(+) AND t1.IDSERVICIO = t3.IDSERVICIO(+) ");
		where.append(mapaWhere.get("query"));
		query.append(where);

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

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

	/**
	 * Finds rows in the Gamas table using like.
	 * 
	 * @param gamas
	 *            Gamas
	 * @param pagination
	 *            Pagination
	 * @param startsWith
	 *            Boolean
	 * @return List
	 */
	@Transactional(readOnly = true)
	public List<Gamas> findAllLike(Gamas gamas, Pagination pagination,
			Boolean startsWith) {
		StringBuilder query = new StringBuilder(
				"SELECT  t1.IDGAMA IDGAMA,t1.IDCLASE IDCLASE, t2.CCLASE CCLASE, t2.ECLASE ECLASE,T2.BAJA BAJACLASE,t1.CGAMA CGAMA,t1.EGAMA EGAMA,t1.SEMANAS SEMANAS,t1.FEINI FEINI,t1.BAJA BAJA,t1.CONTRATA CONTRATA, t1.IDSERVICIO IDSERVICIO, t3.CSERVICIO CSERVICIO, t3.ESERVICIO ESERVICIO ");
		query.append("FROM GAMAS t1, CLASE t2, SERVICIOS t3 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(gamas, startsWith);
		StringBuilder where = new StringBuilder(
				" WHERE t1.IDCLASE=t2.IDCLASE(+) AND t1.IDSERVICIO = t3.IDSERVICIO(+) ");
		where.append(mapaWhere.get("query"));
		query.append(where);

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

		if (pagination != null) {
			query = pagination.getPaginationQuery(query);
		}

		return (List<Gamas>) this.jdbcTemplate.query(query.toString(),
				this.rwMap, params.toArray());
	}

	/**
	 * Counts rows in the Gamas table using like.
	 * 
	 * @param gamas
	 *            Gamas
	 * @param startsWith
	 *            Boolean
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllLikeCount(Gamas gamas, Boolean startsWith) {
		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM GAMAS t1, CLASE t2, SERVICIOS t3    ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereLikeMap(gamas, startsWith);
		StringBuilder where = new StringBuilder(
				" WHERE t1.IDCLASE=t2.IDCLASE(+) AND t1.IDSERVICIO = t3.IDSERVICIO(+) ");
		where.append(mapaWhere.get("query"));
		query.append(where);

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

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

	/**
	 * Finds a single row in the Gamas table.
	 * 
	 * @param idGama
	 *            String
	 * @return Gamas
	 */
	@Transactional(readOnly = true)
	public Gamas obtenerGama(String idGama) {
		String query = "SELECT  t1.IDGAMA IDGAMA,t1.IDCLASE IDCLASE, t2.CCLASE CCLASE, t2.ECLASE ECLASE,T2.BAJA BAJACLASE,t1.CGAMA CGAMA,t1.EGAMA EGAMA,t1.SEMANAS SEMANAS,t1.FEINI FEINI,t1.BAJA BAJA,t1.CONTRATA CONTRATA, t1.IDSERVICIO IDSERVICIO, t3.CSERVICIO CSERVICIO, t3.ESERVICIO ESERVICIO "
				+ "  FROM GAMAS t1, CLASE t2, SERVICIOS t3 WHERE t1.IDCLASE=t2.IDCLASE(+) AND t1.IDSERVICIO = t3.IDSERVICIO(+) AND t1.IDGAMA = ? ";

		List<Gamas> gamasList = this.jdbcTemplate.query(query, this.rwMap,
				idGama);
		return (Gamas) DataAccessUtils.uniqueResult(gamasList);
	}

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

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

		if (gamas != null && gamas.getIdgama() != null) {
			where.append(" AND t1.IDGAMA = ?");
			params.add(gamas.getIdgama());
		}
		if (gamas != null && gamas.getIdclase() != null) {
			where.append(" AND t1.IDCLASE = ?");
			params.add(gamas.getIdclase());
		}
		if (gamas != null && gamas.getCgama() != null) {
			where.append(" AND t1.CGAMA = ?");
			params.add(gamas.getCgama());
		}
		if (gamas != null && gamas.getEgama() != null) {
			where.append(" AND t1.EGAMA = ?");
			params.add(gamas.getEgama());
		}
		if (gamas != null && gamas.getSemanas() != null) {
			where.append(" AND t1.SEMANAS = ?");
			params.add(gamas.getSemanas());
		}
		if (gamas != null && gamas.getFeini() != null) {
			where.append(" AND t1.FEINI = ?");
			params.add(gamas.getFeini());
		}
		if (gamas != null && gamas.getBaja() != null) {
			if (gamas.getBaja().equals("0")) {
				where.append(" AND (t1.BAJA = ? OR t1.BAJA IS NULL) ");
			} else {
				where.append(" AND t1.BAJA = ?");
			}

			params.add(gamas.getBaja());
		}
		if (gamas != null && gamas.getContrata() != null) {
			where.append(" AND t1.CONTRATA = ?");
			params.add(gamas.getContrata());
		}
		if (gamas != null && gamas.getIdservicio() != null
				&& gamas.getIdservicio() != 0) {
			where.append(" AND t1.IDSERVICIO = ?");
			params.add(gamas.getIdservicio());
		}

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

		return mapWhere;
	}

	private Map<String, ?> getWhereMapClase(Gamas gamas) {

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

		if (gamas != null && gamas.getIdservicio() != null
				&& gamas.getIdservicio() != 0) {
			where.append(" AND t1.IDSERVICIO = ?");
			params.add(gamas.getIdservicio());
		}

		if (gamas != null && gamas.getIdclase() != null
				&& gamas.getIdclase() != 0) {
			where.append(" AND t1.IDCLASE = ?");
			params.add(gamas.getIdclase());
		}

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

		return mapWhere;
	}

	// CHECKSTYLE:ON CyclomaticComplexity - Generación de código de UDA

	/**
	 * Returns a map with the needed value to create the conditions to filter by
	 * the Gamas entity
	 * 
	 * @param gamas
	 *            Gamas 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, ?> getWhereLikeMap(Gamas gamas, Boolean startsWith) {

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

		if (gamas != null && gamas.getIdgama() != null) {
			where.append(" AND t1.IDGAMA = ?");
			params.add(gamas.getIdgama());
		}
		if (gamas != null && gamas.getIdclase() != null) {
			where.append(" AND t1.IDCLASE = ?");
			params.add(gamas.getIdclase());
		}
		if (gamas != null && gamas.getClase() != null
				&& gamas.getClase().getCclase() != null) {
			where.append(" AND UPPER(t2.CCLASE) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(gamas.getClase().getCclase().toUpperCase() + "%");
			} else {
				params.add("%" + gamas.getClase().getCclase().toUpperCase()
						+ "%");
			}
			where.append(" AND t2.CCLASE IS NOT NULL");
		}
		if (gamas != null && gamas.getClase() != null
				&& gamas.getClase().getEclase() != null) {
			where.append(" AND UPPER(t2.ECLASE) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(gamas.getClase().getEclase().toUpperCase() + "%");
			} else {
				params.add("%" + gamas.getClase().getEclase().toUpperCase()
						+ "%");
			}
			where.append(" AND t2.ECLASE IS NOT NULL");
		}

		if (gamas != null && gamas.getCgama() != null) {
			where.append(" AND UPPER(t1.CGAMA) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(gamas.getCgama().toUpperCase() + "%");
			} else {
				params.add("%" + gamas.getCgama().toUpperCase() + "%");
			}
			where.append(" AND t1.CGAMA IS NOT NULL");
		}
		if (gamas != null && gamas.getEgama() != null) {
			where.append(" AND UPPER(t1.EGAMA) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(gamas.getEgama().toUpperCase() + "%");
			} else {
				params.add("%" + gamas.getEgama().toUpperCase() + "%");
			}
			where.append(" AND t1.EGAMA IS NOT NULL");
		}
		if (gamas != null && gamas.getSemanas() != null) {
			where.append(" AND t1.SEMANAS = ?");
			params.add(gamas.getSemanas());
		}
		if (gamas != null && gamas.getFeini() != null) {
			where.append(" AND t1.FEINI = ?");
			params.add(gamas.getFeini());
		}
		if (gamas != null && gamas.getBaja() != null) {
			where.append(" AND UPPER(t1.BAJA) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(gamas.getBaja().toUpperCase() + "%");
			} else {
				params.add("%" + gamas.getBaja().toUpperCase() + "%");
			}
			where.append(" AND t1.BAJA IS NOT NULL");
		}
		if (gamas != null && gamas.getContrata() != null) {
			where.append(" AND UPPER(t1.CONTRATA) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(gamas.getContrata().toUpperCase() + "%");
			} else {
				params.add("%" + gamas.getContrata().toUpperCase() + "%");
			}
			where.append(" AND t1.CONTRATA IS NOT NULL");
		}
		if (gamas != null && gamas.getIdservicio() != null
				&& gamas.getIdservicio() != 0) {
			where.append(" AND t1.IDSERVICIO = ?");
			params.add(gamas.getIdservicio());
		}
		if (gamas != null && gamas.getServicio() != null
				&& gamas.getServicio().getCservicio() != null) {
			where.append(" AND UPPER(t3.CSERVICIO) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(gamas.getServicio().getCservicio().toUpperCase()
						+ "%");
			} else {
				params.add("%"
						+ gamas.getServicio().getCservicio().toUpperCase()
						+ "%");
			}
			where.append(" AND t3.CSERVICIO IS NOT NULL");
		}
		if (gamas != null && gamas.getServicio() != null
				&& gamas.getServicio().getEservicio() != null) {
			where.append(" AND UPPER(t3.ESERVICIO) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(gamas.getServicio().getEservicio().toUpperCase()
						+ "%");
			} else {
				params.add("%"
						+ gamas.getServicio().getEservicio().toUpperCase()
						+ "%");
			}
			where.append(" AND t3.ESERVICIO IS NOT NULL");
		}
		Map<String, Object> mapWhere = new HashMap<String, Object>();
		mapWhere.put("query", where);
		mapWhere.put("params", params);

		return mapWhere;
	}

	// CHECKSTYLE:ON CyclomaticComplexity - Generación de código de UDA

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