package com.ejie.m62fb.dao.registroVPP;

import com.ejie.m62fb.dao.SQLGenericas;
import com.ejie.m62fb.utils.constants.BDConstantes;

public class AcuerdoViviendaDaoSql {

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

	public static final String SEQUENCE_NAME = "";

	public static final String TABLA = BDConstantes.ACUERDO_VIVIENDA;
	public static final String VISTA = BDConstantes.VIS_ACUERDO_RESERVA;
	public static final String ALIAS = " ACU";

	public static final String SQL_INSERT = "INSERT INTO" + TABLA
			+ "(ID_M62R38T0, FEC_ACUERDO"
			+ ", DES_ENTIDAD_RECEP, DES_DPTO_RECEP, IND_MODIF_SIN_DOC"
			+ SQLGenericas.INSERT_ABSTRACT_CLASS_SQL + ") VALUES (" + "?,?"
			+ ",?,?,?" + SQLGenericas.INSERT_ABSTRACT_CLASS_QUESTION_TAGS + ")";

	public static final String SQL_SELECT = "SELECT" //
			+ AcuerdoViviendaDaoSql.ALIAS
			+ ".ID_M62R41T0 IDM62R41T0, "
			+ AcuerdoViviendaDaoSql.ALIAS
			+ ".ID_M62R38T0 IDM62R38T0, "
			+ AcuerdoViviendaDaoSql.ALIAS
			+ ".FEC_ACUERDO FECACUERDO, "
			+ AcuerdoViviendaDaoSql.ALIAS
			+ ".DES_ENTIDAD_RECEP DESENTIDADRECEP, "
			+ AcuerdoViviendaDaoSql.ALIAS
			+ ".DES_DPTO_RECEP DESDPTORECEP, "
			+ AcuerdoViviendaDaoSql.ALIAS
			+ ".IND_MODIF_SIN_DOC INDMODIFSINDOC, "
			+ AcuerdoViviendaDaoSql.ALIAS
			+ ".NUM_VIV_RESERVADAS NUMVIVRESERVADAS, "
			+ AcuerdoViviendaDaoSql.ALIAS
			+ ".IMC_PVP_TOTAL IMCPVPTOTAL, "
			+ AcuerdoViviendaDaoSql.ALIAS
			+ ".DES_ENTIDAD_SOLIC DESENTIDADSOLIC, "
			+ AcuerdoViviendaDaoSql.ALIAS
			+ ".DES_DPTO_SOLIC DESDPTOSOLIC, "
			+ AcuerdoViviendaDaoSql.ALIAS
			+ ".NUM_EXPEDIENTE_PLATEA NUMEXPEDIENTEPLATEA";

	private static final String SQL_FROM = " FROM " + VISTA + ALIAS;

	public static final String SQL_WHERE_ALL = " WHERE 1=1 ";

	private static final String SQL_WHERE = SQL_WHERE_ALL + " AND " + ALIAS
			+ ".ID_M62R41T0 = ?";

	private static final String SQL_WHERE_BY_SOLICITUD = SQL_WHERE_ALL
			+ " AND " + ALIAS + ".ID_M62R38T0 = ?";

	public static final String SQL_SELECT_COUNT = "SELECT COUNT(1)";

	public static final String SQL_FIND = SQL_SELECT + SQL_FROM + SQL_WHERE;

	public static final String SQL_FIND_ALL = SQL_SELECT + SQL_FROM;

	public static final String SQL_FIND_ALL_COUNT = SQL_SELECT_COUNT + SQL_FROM;

	public static final String SQL_FIND_BY_SOLICITUD = SQL_SELECT + SQL_FROM
			+ SQL_WHERE_BY_SOLICITUD;
	
	public static final String SQL_TODAS_VIVIENDAS_SOLICITADAS_TIENEN_VIVIENDAS_RESERVADAS=
		"SELECT COUNT(1) FROM VIVIENDAS_SOLICITADAS VS WHERE VS.ID_M62R38T0 = ? " +
		"AND VS.ID_M62R40T0 " +
		"NOT IN ( SELECT VR.ID_M62R40T0 FROM VIVIENDAS_RESERVADAS VR WHERE VR.ID_M62R40T0=VS.ID_M62R40T0)";

}