package com.ejie.m62fb.dao.registroVPP;

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

public class InquilinoadDaoSql {

	/**
	 * StringBuilder initilization value
	 */
	public static final int STRING_BUILDER_INIT = 4096;
	public static final String SEQUENCE_NAME = "";
	
	//Primary Keys de las diferentes Tablas a usar
	public static final String PK_INQUILINOAD = "ID_INQUILINO";
	public static final String PK_ALIAS_INQUILINOAD = " IDINQUILINO ";

	
	// Tablas a usar, llamada a Clase genérica
	public static final String TABLA_INQUILINOAD = BDConstantes.INQUILINOAD;
	public static final String ALIAS_INQUILINOAD = " INQUILINOAD";
//	public static final String ALIAS_INQUILINOADFORROWMAP = " INQUILINOAD";
	public static final String TABLA_PERSONA= BDConstantes.PERSONA ;
	public static final String ALIAS_PERSONA = " T2";
	public static final String TABLA_INQUILINO = BDConstantes.INQUILINO;
	public static final String ALIAS_INQUILINO = " T5";
	
	// Genéricas
	public static final String GENERIC_SELECT_COUNT = " SELECT COUNT(1) ";
	public static final String GENERIC_SELECT = " SELECT ";
	public static final String GENERIC_INSERT_INTO = " INSERT INTO ";
	public static final String GENERIC_UPDATE = " UPDATE ";
	public static final String GENERIC_FROM = " FROM ";
	public static final String GENERIC_WHERE_ALL = " WHERE 1=1 ";
	public static final String SELECT_ABSTRACT_CLASS_SQL_AS_ALIAS_INQAD = 
				", "+ALIAS_INQUILINOAD+".FEC_ALTA FECALTA, "+
				ALIAS_INQUILINOAD+".FEH_ULT_MODIF FEHULTMODIF, "+
				ALIAS_INQUILINOAD+".COD_USU_ULT_MODIF CODUSUULTMODIF ";

	
	//SQL para formar las QUERYS GENERICAS 
	public static final String GENERIC_CLAUSES_SELECT = 				
				ALIAS_INQUILINOAD+"."+InquilinoadDaoSql.PK_INQUILINOAD + PK_ALIAS_INQUILINOAD+", "+
				ALIAS_INQUILINOAD+".ID_PERSONA IDPERSONA, "+
				ALIAS_INQUILINOAD+".NUM_INQUILINO NUMINQUILINO, " +
				ALIAS_INQUILINOAD+".IND_ALOJ_ADAPTADA INDALOJADAPTADA, " +
				ALIAS_INQUILINOAD+".ID_ALOJ_REG IDALOJREG, " +
				ALIAS_INQUILINOAD +".ID_CONTRATO IDCONTRATO " +
				SELECT_ABSTRACT_CLASS_SQL_AS_ALIAS_INQAD;

	public static final String GENERIC_CLAUSES_FROM =					
				TABLA_INQUILINOAD + ALIAS_INQUILINOAD;
	
	//Condiciones
	public static final String AND_IDINQUILINOAD_EQUALS = 
				" AND "+ InquilinoadDaoSql.PK_INQUILINOAD + "=? ";
	
	//Actualizamos fecha de modificación
	public static final String AND_WHERE_FOR_UPDATE = 
				" AND FEH_ULT_MODIF = ?";
	
	
	//SQL para formar la QUERY de findAllLike
	//Condicionantes
	public static final String AND_FIND_ALL_LIKE_INQUILINO_EQUALS_CONTRACT =
				" AND "+ALIAS_INQUILINOAD+".ID_CONTRATO = ? ";
	
	public static final String SQL_FIND_ALL_LIKE = 	
				GENERIC_SELECT + GENERIC_CLAUSES_SELECT +
				GENERIC_FROM + GENERIC_CLAUSES_FROM +
				GENERIC_WHERE_ALL;
				//Condicionantes
//				AND_FIND_ALL_LIKE_INQUILINO_EQUALS_CONTRACT;
	
	
	//SQL para formar la QUERY de findAllLikeCount
	public static final String SQL_FIND_ALL_LIKE_COUNT = 	
				GENERIC_SELECT_COUNT +
				GENERIC_FROM + GENERIC_CLAUSES_FROM +
				GENERIC_WHERE_ALL;
				//Condicionantes
//				AND_FIND_ALL_LIKE_INQUILINO_EQUALS_CONTRACT;
	
	//SQL para formar la QUERY de find
	//Condicionantes
	public static final String AND_FIND_BY_IDINQUILINO =			
				" AND "+ALIAS_INQUILINOAD+"."+InquilinoadDaoSql.PK_INQUILINOAD +" = ? ";
	
	public static final String SQL_FIND = 	
				GENERIC_SELECT + GENERIC_CLAUSES_SELECT +
				GENERIC_FROM + GENERIC_CLAUSES_FROM +
				GENERIC_WHERE_ALL + 
				//Condicionantes
				AND_FIND_BY_IDINQUILINO;
	
	public static final String SQL_FIND_FOR_UPDATE =
				GENERIC_SELECT + GENERIC_CLAUSES_SELECT +
				GENERIC_FROM + GENERIC_CLAUSES_FROM + 
				GENERIC_WHERE_ALL + AND_WHERE_FOR_UPDATE +	AND_IDINQUILINOAD_EQUALS ;
		
	
	//SQL para formar la QUERY de verifiación de contratos no duplicados con Etxelaguntza
	public static final String CLAUSES_COUNT_CONTRACTS_FROM = 
				TABLA_INQUILINOAD + ALIAS_INQUILINOAD +" , "+ 
				TABLA_PERSONA + ALIAS_PERSONA +" , " + 
				TABLA_INQUILINO + ALIAS_INQUILINO;
	
	public static final String AND_COUNT_CONTRACTS_PERSONA_UNION_INQUILINO =
				" AND "+ALIAS_PERSONA+".ID_PERSONA = "+ALIAS_INQUILINOAD+".ID_PERSONA ";
	
	public static final String AND_COUNT_CONTRACTS_PERSONA_UNION_INQUILINOAD =	
				" AND "+ALIAS_PERSONA+".ID_PERSONA = "+ALIAS_INQUILINO+".ID_PERSONA ";
	
	public static final String AND_COUNT_CONTRACTS_IDPERSONA_EQUALS_INQUILINO_IDPERSONA = 
				" AND "+ALIAS_INQUILINO+".ID_PERSONA = ? ";
	
	public static final String AND_COUNT_CONTRACTS_IDCONTRATO_EQUALS_INQUILINOAD_IDCONTRATO = 
				" AND "+ALIAS_INQUILINOAD+".ID_CONTRATO = ? ";
	
	public static final String SQL_COUNT_CONTRACTS = 	
				GENERIC_SELECT_COUNT + 
				GENERIC_FROM + CLAUSES_COUNT_CONTRACTS_FROM +
				GENERIC_WHERE_ALL + AND_COUNT_CONTRACTS_PERSONA_UNION_INQUILINO +
				AND_COUNT_CONTRACTS_PERSONA_UNION_INQUILINOAD +
				//Condicionantes
				AND_COUNT_CONTRACTS_IDPERSONA_EQUALS_INQUILINO_IDPERSONA+
				AND_COUNT_CONTRACTS_IDCONTRATO_EQUALS_INQUILINOAD_IDCONTRATO ;
	
	
	//SQL para insertar inquilinoad
	public static final String SQL_ADD =	
				GENERIC_INSERT_INTO +
				InquilinoadDaoSql.TABLA_INQUILINOAD + 
		/*3*/	" ( ID_CONTRATO, ID_ALOJ_REG, " +
		/*3*/	"NUM_INQUILINO, ID_PERSONA, IND_ALOJ_ADAPTADA " +
				SQLGenericas.INSERT_ABSTRACT_CLASS_SQL + 
				SQLGenericas.getValuesSQLFor(5) +
				SQLGenericas.INSERT_ABSTRACT_CLASS_QUESTION_TAGS + ") ";
	
	
	/* ## SQL para actualizar inquilinoad **/
	public static final String SQL_EDIT =	
				GENERIC_UPDATE +
				InquilinoadDaoSql.TABLA_INQUILINOAD +
		/*3*/	" SET ID_CONTRATO=?, ID_ALOJ_REG=?, NUM_INQUILINO=?, " +
		/*2*/	" ID_PERSONA=?, IND_ALOJ_ADAPTADA=? " +
				SQLGenericas.UPDATE_ABSTRACT_CLASS_SQL +
				GENERIC_WHERE_ALL + 
				AND_IDINQUILINOAD_EQUALS ;
	
	//SQL para la eliminación de un Inquilino de un alojamiento dotacional
	public static final String SQL_DELETE = 
				"DELETE FROM"
				+  InquilinoadDaoSql.TABLA_INQUILINOAD
				+ " WHERE "+InquilinoadDaoSql.PK_INQUILINOAD+"=? ";
	
	
	public static final String SQL_GET_NUMINQUILINO = "SELECT MAX( "
				+ InquilinoadDaoSql.ALIAS_INQUILINOAD +".NUM_INQUILINO ) FROM " 
				+ InquilinoadDaoSql.TABLA_INQUILINOAD + " " + ALIAS_INQUILINOAD 
				+ " WHERE "+InquilinoadDaoSql.ALIAS_INQUILINOAD+".ID_CONTRATO =? ";
	
}