package com.ejie.aa21b.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
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.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.Aa21b02s01;
import com.ejie.aa21b.model.Aa21b13s01;
import com.ejie.aa21b.model.Aa21bServiciosTiposFases;
import com.ejie.x38.dto.JQGridManager;
import com.ejie.x38.dto.JQGridRequestDto;

/**
 * Aa21bServiciosTiposFasesDaoImpl  
 * 
 *  
 */

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

	/*
	 * ROW_MAPPERS
	 */
	private RowMapper<Aa21bServiciosTiposFases> rwMap = new RowMapper<Aa21bServiciosTiposFases>() {
		public Aa21bServiciosTiposFases mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {
			return new Aa21bServiciosTiposFases(resultSet.getString("CSERV13"),
					resultSet.getString("DSERV13"),
					resultSet.getString("ESERV13"),
					resultSet.getString("SITU13"),
					resultSet.getString("CTIPOE02"),
					resultSet.getString("CTRAMI02"),
					resultSet.getString("DTIPOE02"),
					resultSet.getString("ETIPOE02"),
					resultSet.getString("SITU02"),
					resultSet.getLong("CFASE01"),
					resultSet.getString("DFASE01"),
					resultSet.getString("EFASE01"),
					resultSet.getString("CODPID01"),
					resultSet.getString("TDOCU01"),
					resultSet.getString("SITU01"));

		}
	};

	/**
	 * 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 Aa21b13s01 table.
	 * 
	 * @param aa21bServiciosTiposFases
	 *            Aa21bServiciosTiposFases
	 * @param jqGridRequestDto
	 *            JQGridRequestDto
	 * @param listaServicios
	 *            List<Aa21b13s01>
	 * @param listaTipos
	 *            List<Aa21b02s01>
	 * @return List<Aa21bServiciosTiposFases>
	 */
	@Transactional(readOnly = true)
	public List<Aa21bServiciosTiposFases> findAll(
			Aa21bServiciosTiposFases aa21bServiciosTiposFases,
			JQGridRequestDto jqGridRequestDto, List<Aa21b13s01> listaServicios,
			List<Aa21b02s01> listaTipos) {
		Aa21bServiciosTiposFasesDaoImpl.logger.info("M�todo findAll");
		StringBuilder query = new StringBuilder(
				"SELECT  t1.CSERV13 CSERV13,t1.DSERV13 DSERV13,t1.ESERV13 ESERV13,t1.SITU13 SITU13 ");
		query.append(" , t2.CTIPOE02 CTIPOE02,t2.CTRAMI02 CTRAMI02,t2.DTIPOE02 DTIPOE02,t2.ETIPOE02 ETIPOE02,t2.SITU02 SITU02 ");
		query.append(" , t3.CFASE01 CFASE01,t3.DFASE01 DFASE01,t3.SITU01 SITU01,t3.codPid01 codPid01,t3.TDOCU01 TDOCU01,t3.EFASE01 EFASE01 ");
		query.append("FROM AA21B13S01 t1 ");
		query.append(" LEFT JOIN AA21b02S01 t2 ON t1.CSERV13 = t2.CSERV02 ");
		query.append(" LEFT JOIN AA21B01S01 t3 ON t1.CSERV13 = t3.CSERV01 AND t2.CTIPOE02 = t3.CTIPOE01 ");

		// Where clause & Params
		Map<String, ?> mapaWhere = this.getWhereMap(aa21bServiciosTiposFases,
				listaServicios, listaTipos);
		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);
		}
		Aa21bServiciosTiposFasesDaoImpl.logger.info("findAll");
		Aa21bServiciosTiposFasesDaoImpl.logger
				.info("SQL = " + query.toString());
		Aa21bServiciosTiposFasesDaoImpl.logger.info("PARAMS" + params);
		return (List<Aa21bServiciosTiposFases>) this.jdbcTemplate.query(
				query.toString(), this.rwMap, params.toArray());
	}

	/**
	 * Returns a map with the needed value to create the conditions to filter by
	 * the Aa21bServiciosTiposFases entity
	 * 
	 * @param aa21bServiciosTiposFases
	 *            Aa21bServiciosTiposFases 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(
			Aa21bServiciosTiposFases aa21bServiciosTiposFases,
			List<Aa21b13s01> listaServicios, List<Aa21b02s01> listaTipos) {

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

		if (listaServicios != null) {
			if (listaServicios.size() > 0) {
				where.append(" AND (");
				Aa21b13s01 serviciosAux = null;
				for (Iterator<Aa21b13s01> iterator = listaServicios.iterator(); iterator
						.hasNext();) {

					serviciosAux = (Aa21b13s01) iterator.next();
					where.append(" t1.CSERV13 = ? ");
					params.add(serviciosAux.getCServ13());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getDServ13() != null) {

			where.append(" AND UPPER(t1.DSERV13) like ? ESCAPE  '\\'");
			params.add("%"
					+ aa21bServiciosTiposFases.getDServ13().toUpperCase() + "%");
			where.append(" AND t1.DSERV13 IS NOT NULL");

		}
		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getEServ13() != null) {
			where.append(" AND UPPER(t1.ESERV13) like ? ESCAPE  '\\'");
			params.add("%"
					+ aa21bServiciosTiposFases.getEServ13().toUpperCase() + "%");
			where.append(" AND t1.ESERV13 IS NOT NULL");

		}
		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getSitu13() != null) {
			where.append(" AND (t1.SITU13 = ? )");
			params.add(aa21bServiciosTiposFases.getSitu13());
		}
		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getIctipoe02Text() != null) {
			where.append(" AND (UPPER(t2.CTIPOE02) like ? ESCAPE  '\\' )");
			params.add("%"
					+ aa21bServiciosTiposFases.getIctipoe02Text().toUpperCase()
					+ "%");
		}

		if (listaTipos != null) {
			if (listaTipos.size() > 0) {
				where.append(" AND (");
				Aa21b02s01 serviciosAux = null;
				for (Iterator<Aa21b02s01> iterator = listaTipos.iterator(); iterator
						.hasNext();) {

					serviciosAux = (Aa21b02s01) iterator.next();
					where.append(" t2.CTIPOE02 = ? ");
					params.add(serviciosAux.getCtipoe02());
					if (iterator.hasNext()) {
						where.append(" OR ");
					}
				}
				where.append(") ");
			}
		}

		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getCtrami02() != null) {
			where.append(" AND t2.CTRAMI02 = ?");
			params.add(aa21bServiciosTiposFases.getCtrami02());
		}
		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getDtipoe02() != null) {

			where.append(" AND UPPER(t2.DTIPOE02) like ? ESCAPE  '\\'");
			params.add("%"
					+ aa21bServiciosTiposFases.getDtipoe02().toUpperCase()
					+ "%");
			where.append(" AND t2.DTIPOE02 IS NOT NULL");

		}
		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getEtipoe02() != null) {

			where.append(" AND UPPER(t2.ETIPOE02) like ? ESCAPE  '\\'");
			params.add("%"
					+ aa21bServiciosTiposFases.getEtipoe02().toUpperCase()
					+ "%");
			where.append(" AND t2.ETIPOE02 IS NOT NULL");

		}
		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getSitu02() != null) {
			where.append(" AND ( t2.SITU02 = ? or t2.SITU02 is null)");
			params.add(aa21bServiciosTiposFases.getSitu02());
		}
		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getCfase01() != null) {
			where.append(" AND t3.CFASE01 = ?");
			params.add(aa21bServiciosTiposFases.getCfase01());
		}

		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getDfase01() != null) {

			where.append(" AND UPPER(t3.DFASE01) like ? ESCAPE  '\\'");
			params.add("%"
					+ aa21bServiciosTiposFases.getDfase01().toUpperCase() + "%");
			where.append(" AND t3.DFASE01 IS NOT NULL");

		}
		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getSitu01() != null) {
			where.append(" AND( t3.SITU01 = ?  or  t3.SITU01 is null)");
			params.add(aa21bServiciosTiposFases.getSitu01());
		}

		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getCodPid01() != null) {
			where.append(" AND t3.codPid01 = ?");
			params.add(aa21bServiciosTiposFases.getCodPid01());
		}
		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getTdocu01() != null) {
			where.append(" AND t3.TDOCU01 = ?");
			params.add(aa21bServiciosTiposFases.getTdocu01());
		}

		if (aa21bServiciosTiposFases != null
				&& aa21bServiciosTiposFases.getEfase01() != null) {

			where.append(" AND UPPER(t3.EFASE01) like ? ESCAPE  '\\'");
			params.add("%"
					+ aa21bServiciosTiposFases.getEfase01().toUpperCase() + "%");
			where.append(" AND t3.EFASE01 IS NOT NULL");

		}

		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;
}
