package com.ejie.y42b.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ejie.x38.dto.Pagination;
import com.ejie.y42b.constantes.Y42bAgendaConstantes;
import com.ejie.y42b.model.T17Portal;

/**
 * * T17PortalDaoImpl  
 * 
 *  
 */
@Repository()
@Transactional()
public class T17PortalDaoImpl implements T17PortalDao {
	private SimpleJdbcTemplate jdbcTemplate;
	private RowMapper<T17Portal> rwMap = new RowMapper<T17Portal>() {
		public T17Portal mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {

			T17Portal portal = null;
			portal = new T17Portal(resultSet.getLong("ID"),
					resultSet.getString("BLOQUE"), resultSet.getInt("NUMERO"),
					resultSet.getString("BIS"),
					resultSet.getString("CODIGOPOSTAL"),
					resultSet.getString("ACEPCION"),
					resultSet.getBigDecimal("DXED50"),
					resultSet.getBigDecimal("DYED50"),
					resultSet.getBigDecimal("DXETRS89"),
					resultSet.getBigDecimal("DYETRS89"),
					resultSet.getLong("CALLEID"),
					resultSet.getString("LOCALIDADID"),
					resultSet.getString("ESTADO"), resultSet.getLong("TRAMOID"));

			return portal;
		}
	};

	private RowMapper<T17Portal> rwMapCP = new RowMapper<T17Portal>() {
		public T17Portal mapRow(ResultSet resultSet, int rowNum)
				throws SQLException {

			T17Portal portal = new T17Portal();
			portal.setCodigoPostal(resultSet.getString("CODIGOPOSTAL"));

			return portal;
		}
	};

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

	/**
	 * Inserts a single row in the T17Portal table.
	 * 
	 * @param t17portal
	 *            Pagination
	 * @return T17Portal
	 */
	public T17Portal add(T17Portal t17portal) {

		String query = "INSERT INTO S17PORTAL( ID,BLOQUE,NUMERO,BIS,CODIGO_POSTAL,ACEPCION,DX_ED50,DY_ED50,DX_ETRS89,DY_ETRS89,CALLE_ID,LOCALIDAD_ID,ESTADO,TRAMO_ID)"
				+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		this.jdbcTemplate.update(query, t17portal.getId(),
				t17portal.getBloque(), t17portal.getNumero(),
				t17portal.getBis(), t17portal.getCodigoPostal(),
				t17portal.getAcepcion(), t17portal.getDxEd50(),
				t17portal.getDyEd50(), t17portal.getDxEtrs89(),
				t17portal.getDyEtrs89(), t17portal.getCalleId(),
				t17portal.getLocalidadId(), t17portal.getEstado(),
				t17portal.getTramoId());
		return t17portal;
	}

	/**
	 * Updates a single row in the T17Portal table.
	 * 
	 * @param t17portal
	 *            Pagination
	 * @return T17Portal
	 */
	public T17Portal update(T17Portal t17portal) {
		String query = "UPDATE S17PORTAL SET  WHERE ID=? AND BLOQUE=? AND NUMERO=? AND BIS=? AND CODIGO_POSTAL=? AND ACEPCION=? AND DX_ED50=? AND DY_ED50=? AND DX_ETRS89=? AND DY_ETRS89=? AND CALLE_ID=? AND LOCALIDAD_ID=? AND ESTADO=? AND TRAMO_ID=?";
		this.jdbcTemplate.update(query, t17portal.getId(),
				t17portal.getBloque(), t17portal.getNumero(),
				t17portal.getBis(), t17portal.getCodigoPostal(),
				t17portal.getAcepcion(), t17portal.getDxEd50(),
				t17portal.getDyEd50(), t17portal.getDxEtrs89(),
				t17portal.getDyEtrs89(), t17portal.getCalleId(),
				t17portal.getLocalidadId(), t17portal.getEstado(),
				t17portal.getTramoId());
		return t17portal;

	}

	/**
	 * Finds a single row in the T17Portal table.
	 * 
	 * @param t17portal
	 *            Pagination
	 * @return T17Portal
	 */
	@Transactional(readOnly = true)
	public T17Portal find(T17Portal t17portal) {
		String query = "SELECT t1.ID ID, t1.BLOQUE BLOQUE, t1.NUMERO NUMERO, t1.BIS BIS, t1.CODIGO_POSTAL CODIGOPOSTAL, t1.ACEPCION ACEPCION, t1.DX_ED50 DXED50, t1.DY_ED50 DYED50, t1.DX_ETRS89 DXETRS89, t1.DY_ETRS89 DYETRS89, t1.CALLE_ID CALLEID, t1.LOCALIDAD_ID LOCALIDADID, t1.ESTADO ESTADO, t1.TRAMO_ID TRAMOID "
				+ "FROM S17PORTAL t1  "
				+ "WHERE t1.ID = ?   AND t1.BLOQUE = ?   AND t1.NUMERO = ?   AND t1.BIS = ?   AND t1.CODIGO_POSTAL = ?   AND t1.ACEPCION = ?   AND t1.DX_ED50 = ?   AND t1.DY_ED50 = ?   AND t1.DX_ETRS89 = ?   AND t1.DY_ETRS89 = ?   AND t1.CALLE_ID = ?   AND t1.LOCALIDAD_ID = ?   AND t1.ESTADO = ?   AND t1.TRAMO_ID = ?    ";
		return (T17Portal) this.jdbcTemplate.queryForObject(query, this.rwMap,
				t17portal.getId(), t17portal.getBloque(),
				t17portal.getNumero(), t17portal.getBis(),
				t17portal.getCodigoPostal(), t17portal.getAcepcion(),
				t17portal.getDxEd50(), t17portal.getDyEd50(),
				t17portal.getDxEtrs89(), t17portal.getDyEtrs89(),
				t17portal.getCalleId(), t17portal.getLocalidadId(),
				t17portal.getEstado(), t17portal.getTramoId());
	}

	/**
	 * Removes a single row in the T17Portal table.
	 * 
	 * @param t17portal
	 *            Pagination
	 * 
	 */
	public void remove(T17Portal t17portal) {
		String query = "DELETE  FROM S17PORTAL WHERE ID=? AND BLOQUE=? AND NUMERO=? AND BIS=? AND CODIGO_POSTAL=? AND ACEPCION=? AND DX_ED50=? AND DY_ED50=? AND DX_ETRS89=? AND DY_ETRS89=? AND CALLE_ID=? AND LOCALIDAD_ID=? AND ESTADO=? AND TRAMO_ID=?";
		this.jdbcTemplate.update(query, t17portal.getId(),
				t17portal.getBloque(), t17portal.getNumero(),
				t17portal.getBis(), t17portal.getCodigoPostal(),
				t17portal.getAcepcion(), t17portal.getDxEd50(),
				t17portal.getDyEd50(), t17portal.getDxEtrs89(),
				t17portal.getDyEtrs89(), t17portal.getCalleId(),
				t17portal.getLocalidadId(), t17portal.getEstado(),
				t17portal.getTramoId());

	}

	/**
	 * Finds a List of rows in the T17Portal table.
	 * 
	 * @param t17portal
	 *            T17Portal
	 * @param pagination
	 *            Pagination
	 * @return List
	 */
	@Transactional(readOnly = true)
	public List<T17Portal> findAll(T17Portal t17portal, Pagination pagination) {
		StringBuilder where = new StringBuilder(Y42bAgendaConstantes.NUM_3000);
		List<Object> params = new ArrayList<Object>();
		where.append(" WHERE 1=1 	");
		StringBuilder query = new StringBuilder(
				"SELECT  t1.ID ID,t1.BLOQUE BLOQUE,t1.NUMERO NUMERO,t1.BIS BIS,t1.CODIGO_POSTAL CODIGOPOSTAL,t1.ACEPCION ACEPCION,t1.DX_ED50 DXED50,t1.DY_ED50 DYED50,t1.DX_ETRS89 DXETRS89,t1.DY_ETRS89 DYETRS89,t1.CALLE_ID CALLEID,t1.LOCALIDAD_ID LOCALIDADID,t1.ESTADO ESTADO,t1.TRAMO_ID TRAMOID "
						+ "FROM S17PORTAL t1 ");
		if (t17portal != null && t17portal.getId() != null) {
			where.append(" AND t1.ID = ?");
			params.add(t17portal.getId());
		}
		if (t17portal != null && t17portal.getBloque() != null) {
			where.append(" AND t1.BLOQUE = ?");
			params.add(t17portal.getBloque());
		}
		if (t17portal != null && t17portal.getNumero() != 0) {
			where.append(" AND t1.NUMERO = ?");
			params.add(t17portal.getNumero());
		}
		if (t17portal != null && t17portal.getBis() != null) {
			where.append(" AND t1.BIS = ?");
			params.add(t17portal.getBis());
		}
		if (t17portal != null && t17portal.getCodigoPostal() != null) {
			where.append(" AND t1.CODIGO_POSTAL = ?");
			params.add(t17portal.getCodigoPostal());
		}
		if (t17portal != null && t17portal.getAcepcion() != null) {
			where.append(" AND t1.ACEPCION = ?");
			params.add(t17portal.getAcepcion());
		}
		if (t17portal != null && t17portal.getDxEd50() != null) {
			where.append(" AND t1.DX_ED50 = ?");
			params.add(t17portal.getDxEd50());
		}
		if (t17portal != null && t17portal.getDyEd50() != null) {
			where.append(" AND t1.DY_ED50 = ?");
			params.add(t17portal.getDyEd50());
		}
		if (t17portal != null && t17portal.getDxEtrs89() != null) {
			where.append(" AND t1.DX_ETRS89 = ?");
			params.add(t17portal.getDxEtrs89());
		}
		if (t17portal != null && t17portal.getDyEtrs89() != null) {
			where.append(" AND t1.DY_ETRS89 = ?");
			params.add(t17portal.getDyEtrs89());
		}
		// if (t17portal != null && t17portal.getCalleId() != null) {
		where.append(" AND t1.CALLE_ID = ?");
		params.add(t17portal.getCalleId());
		// }
		// if (t17portal != null && t17portal.getLocalidadId() != null) {
		// where.append(" AND t1.LOCALIDAD_ID = ?");
		// params.add(t17portal.getLocalidadId());
		// }
		if (t17portal != null && t17portal.getEstado() != null) {
			where.append(" AND t1.ESTADO = ?");
			params.add(t17portal.getEstado());
		}
		if (t17portal != null && t17portal.getTramoId() != null) {
			where.append(" AND t1.TRAMO_ID = ?");
			params.add(t17portal.getTramoId());
		}
		query.append(where);

		StringBuilder order = new StringBuilder(Y42bAgendaConstantes.NUM_3000);
		// if (pagination != null) {
		// if (pagination.getSort() != null) {
		// //order.append(" ORDER BY " + pagination.getSort() + " " +
		// pagination.getAscDsc());
		order.append(" ORDER BY NUMERO ");
		// //order.append(" ORDER BY NUMERO ");
		query.append(order);
		// }

		// query = new
		// StringBuilder(PaginationManager.getQueryLimits(pagination,
		// query.toString()));
		// }

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

	/**
	 * Counts rows in the T17Portal table.
	 * 
	 * @param t17portal
	 *            T17Portal
	 * @return Long
	 */
	@Transactional(readOnly = true)
	public Long findAllCount(T17Portal t17portal) {

		StringBuilder where = new StringBuilder(Y42bAgendaConstantes.NUM_3000);
		List<Object> params = new ArrayList<Object>();
		where.append(" WHERE 1=1  ");

		StringBuilder query = new StringBuilder(
				"SELECT COUNT(1) FROM  S17PORTAL t1  ");
		if (t17portal != null && t17portal.getId() != null) {
			where.append(" AND t1.ID = ?");
			params.add(t17portal.getId());
		}
		if (t17portal != null && t17portal.getBloque() != null) {
			where.append(" AND t1.BLOQUE = ?");
			params.add(t17portal.getBloque());
		}
		if (t17portal != null && t17portal.getNumero() != 0) {
			where.append(" AND t1.NUMERO = ?");
			params.add(t17portal.getNumero());
		}
		if (t17portal != null && t17portal.getBis() != null) {
			where.append(" AND t1.BIS = ?");
			params.add(t17portal.getBis());
		}
		if (t17portal != null && t17portal.getCodigoPostal() != null) {
			where.append(" AND t1.CODIGO_POSTAL = ?");
			params.add(t17portal.getCodigoPostal());
		}
		if (t17portal != null && t17portal.getAcepcion() != null) {
			where.append(" AND t1.ACEPCION = ?");
			params.add(t17portal.getAcepcion());
		}
		if (t17portal != null && t17portal.getDxEd50() != null) {
			where.append(" AND t1.DX_ED50 = ?");
			params.add(t17portal.getDxEd50());
		}
		if (t17portal != null && t17portal.getDyEd50() != null) {
			where.append(" AND t1.DY_ED50 = ?");
			params.add(t17portal.getDyEd50());
		}
		if (t17portal != null && t17portal.getDxEtrs89() != null) {
			where.append(" AND t1.DX_ETRS89 = ?");
			params.add(t17portal.getDxEtrs89());
		}
		if (t17portal != null && t17portal.getDyEtrs89() != null) {
			where.append(" AND t1.DY_ETRS89 = ?");
			params.add(t17portal.getDyEtrs89());
		}
		if (t17portal != null && t17portal.getCalleId() != null) {
			where.append(" AND t1.CALLE_ID = ?");
			params.add(t17portal.getCalleId());
		}
		if (t17portal != null && t17portal.getLocalidadId() != null) {
			where.append(" AND t1.LOCALIDAD_ID = ?");
			params.add(t17portal.getLocalidadId());
		}
		if (t17portal != null && t17portal.getEstado() != null) {
			where.append(" AND t1.ESTADO = ?");
			params.add(t17portal.getEstado());
		}
		if (t17portal != null && t17portal.getTramoId() != null) {
			where.append(" AND t1.TRAMO_ID = ?");
			params.add(t17portal.getTramoId());
		}

		query.append(where);

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

	}

	/**
	 * Finds rows in the T17Portal table using like.
	 * 
	 * @param t17portal
	 *            T17Portal
	 * @param pagination
	 *            Pagination
	 * @param startsWith
	 *            Boolean
	 * @return List
	 */
	@Transactional(readOnly = true)
	public List<T17Portal> findAllLike(T17Portal t17portal,
			Pagination pagination, Boolean startsWith) {
		StringBuilder where = new StringBuilder(Y42bAgendaConstantes.NUM_3000);
		List<Object> params = new ArrayList<Object>();
		where.append(" WHERE 1=1 	");
		StringBuilder query = new StringBuilder(
				"SELECT  t1.ID ID,t1.BLOQUE BLOQUE,t1.NUMERO NUMERO,t1.BIS BIS,t1.CODIGO_POSTAL CODIGOPOSTAL,t1.ACEPCION ACEPCION,t1.DX_ED50 DXED50,t1.DY_ED50 DYED50,t1.DX_ETRS89 DXETRS89,t1.DY_ETRS89 DYETRS89,t1.CALLE_ID CALLEID,t1.LOCALIDAD_ID LOCALIDADID,t1.ESTADO ESTADO,t1.TRAMO_ID TRAMOID "
						+ "FROM S17PORTAL t1 ");
		if (t17portal != null && t17portal.getId() != null) {
			where.append(" AND t1.ID like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getId() + "%");
			} else {
				params.add("%" + t17portal.getId() + "%");
			}
			where.append(" AND t1.ID IS NOT NULL");

		}
		if (t17portal != null && t17portal.getBloque() != null) {
			where.append(" AND UPPER(t1.BLOQUE) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getBloque().toUpperCase() + "%");
			} else {
				params.add("%" + t17portal.getBloque().toUpperCase() + "%");
			}
			where.append(" AND t1.BLOQUE IS NOT NULL");

		}
		if (t17portal != null && t17portal.getNumero() != 0) {
			where.append(" AND t1.NUMERO like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getNumero() + "%");
			} else {
				params.add("%" + t17portal.getNumero() + "%");
			}
			where.append(" AND t1.NUMERO IS NOT NULL");

		}
		if (t17portal != null && t17portal.getBis() != null) {
			where.append(" AND UPPER(t1.BIS) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getBis().toUpperCase() + "%");
			} else {
				params.add("%" + t17portal.getBis().toUpperCase() + "%");
			}
			where.append(" AND t1.BIS IS NOT NULL");

		}
		if (t17portal != null && t17portal.getCodigoPostal() != null) {
			where.append(" AND UPPER(t1.CODIGO_POSTAL) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getCodigoPostal().toUpperCase() + "%");
			} else {
				params.add("%" + t17portal.getCodigoPostal().toUpperCase()
						+ "%");
			}
			where.append(" AND t1.CODIGO_POSTAL IS NOT NULL");

		}
		if (t17portal != null && t17portal.getAcepcion() != null) {
			where.append(" AND UPPER(t1.ACEPCION) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getAcepcion().toUpperCase() + "%");
			} else {
				params.add("%" + t17portal.getAcepcion().toUpperCase() + "%");
			}
			where.append(" AND t1.ACEPCION IS NOT NULL");

		}
		if (t17portal != null && t17portal.getDxEd50() != null) {
			where.append(" AND t1.DX_ED50 like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getDxEd50() + "%");
			} else {
				params.add("%" + t17portal.getDxEd50() + "%");
			}
			where.append(" AND t1.DX_ED50 IS NOT NULL");

		}
		if (t17portal != null && t17portal.getDyEd50() != null) {
			where.append(" AND t1.DY_ED50 like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getDyEd50() + "%");
			} else {
				params.add("%" + t17portal.getDyEd50() + "%");
			}
			where.append(" AND t1.DY_ED50 IS NOT NULL");

		}
		if (t17portal != null && t17portal.getDxEtrs89() != null) {
			where.append(" AND t1.DX_ETRS89 like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getDxEtrs89() + "%");
			} else {
				params.add("%" + t17portal.getDxEtrs89() + "%");
			}
			where.append(" AND t1.DX_ETRS89 IS NOT NULL");

		}
		if (t17portal != null && t17portal.getDyEtrs89() != null) {
			where.append(" AND t1.DY_ETRS89 like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getDyEtrs89() + "%");
			} else {
				params.add("%" + t17portal.getDyEtrs89() + "%");
			}
			where.append(" AND t1.DY_ETRS89 IS NOT NULL");

		}
		if (t17portal != null && t17portal.getCalleId() != null) {
			where.append(" AND t1.CALLE_ID like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getCalleId() + "%");
			} else {
				params.add("%" + t17portal.getCalleId() + "%");
			}
			where.append(" AND t1.CALLE_ID IS NOT NULL");

		}
		if (t17portal != null && t17portal.getLocalidadId() != null) {
			where.append(" AND t1.LOCALIDAD_ID like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getLocalidadId() + "%");
			} else {
				params.add("%" + t17portal.getLocalidadId() + "%");
			}
			where.append(" AND t1.LOCALIDAD_ID IS NOT NULL");

		}
		if (t17portal != null && t17portal.getEstado() != null) {
			where.append(" AND UPPER(t1.ESTADO) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getEstado().toUpperCase() + "%");
			} else {
				params.add("%" + t17portal.getEstado().toUpperCase() + "%");
			}
			where.append(" AND t1.ESTADO IS NOT NULL");

		}
		if (t17portal != null && t17portal.getTramoId() != null) {
			where.append(" AND t1.TRAMO_ID like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17portal.getTramoId() + "%");
			} else {
				params.add("%" + t17portal.getTramoId() + "%");
			}
			where.append(" AND t1.TRAMO_ID IS NOT NULL");

		}

		query.append(where);

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

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

	/**
	 * Finds a List of distinct CP rows in the T17Portal table.
	 * 
	 * @param t17Portal
	 *            T17Portal
	 * @param pagination
	 *            Pagination
	 * @param startsWith
	 *            Boolean
	 * @return List
	 */
	@Transactional(readOnly = true)
	public List<T17Portal> findAllLikeCodigoPostal(T17Portal t17Portal,
			Pagination pagination, Boolean startsWith) {
		StringBuilder query = new StringBuilder(Y42bAgendaConstantes.BUFFER_50);
		List<Object> params = new ArrayList<Object>();

		query.append("SELECT  distinct(t1.CODIGO_POSTAL) CODIGOPOSTAL FROM S17PORTAL t1 ");
		query.append("WHERE 1=1");
		// LIKE '4%'
		if (t17Portal != null && t17Portal.getCodigoPostal() != null) {
			query.append(" AND UPPER(t1.CODIGO_POSTAL) like ? ESCAPE  '\\'");
			if (startsWith) {
				params.add(t17Portal.getCodigoPostal().toUpperCase() + "%");
			} else {
				params.add("%" + t17Portal.getCodigoPostal().toUpperCase()
						+ "%");
			}
			query.append(" AND t1.CODIGO_POSTAL IS NOT NULL");
			query.append(" ORDER BY t1.CODIGO_POSTAL ASC");
		}

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

	/**
	 * Finds a List of distinct CP rows in the T17Portal table.
	 * 
	 * @param t17Portal
	 *            T17Portal
	 * @return T17Portal
	 */
	@Transactional(readOnly = true)
	public T17Portal findCodigoPostal(T17Portal t17Portal) {
		StringBuilder query = new StringBuilder();
		List<Object> params = new ArrayList<Object>();

		query.append("SELECT  distinct(t1.CODIGO_POSTAL) CODIGOPOSTAL FROM S17PORTAL t1 ");
		query.append("WHERE 1=1");

		if (t17Portal != null && t17Portal.getId() != null) {
			query.append(" AND t1.ID = ?");
			params.add(t17Portal.getId());
		}

		return (T17Portal) this.jdbcTemplate.queryForObject(query.toString(),
				rwMapCP, params.toArray());
	}

}
