package com.ejie.t17i.dao;

public class CalleDaoSql {

	/**
	 * StringBuilder initilization value
	 */
	public static final int STRING_BUILDER_INIT = 4096;
	public static final String SEQUENCE_NAME = "";
	public static final String TABLA = " t17i.T17_CALLE ";
	public static final String ORDER_ES = " ORDER BY UPPER(CALLE_DESC_ES || ' ' || TIPO_VIA_DESC_ES)";
	public static final String ORDER_EU = " ORDER BY UPPER(CALLE_DESC_EU || ' ' || TIPO_VIA_DESC_EU)";
	public static final String ALIAS = "CALLE";
	public static final String PK = "ID";
	public static final String CALLE_COD = "CALLE_COD";
	public static final String SQL_SELECT = "SELECT " + 
		CalleDaoSql.ALIAS + ".PROVINCIA_ID PROVINCIAID, " + 
		CalleDaoSql.ALIAS + ".ID ID, " + 
		CalleDaoSql.ALIAS + ".DS_O CALLE_DESC_ES, " + 
		"NVL("+CalleDaoSql.ALIAS + ".DS_E, " + 
		CalleDaoSql.ALIAS + ".DS_O) CALLE_DESC_EU, " + 
		CalleDaoSql.ALIAS + ".TIPO_VIA_ID TIPOVIAID," +
		CalleDaoSql.ALIAS + ".ESTADO ESTADO, " +
		CalleDaoSql.ALIAS + ".CALLE_COD CALLECOD, " +
		CalleDaoSql.ALIAS + ".MUNICIPIO_ID MUNICIPIOID ";
	public static final String SQL_SELECT_ALL = "SELECT PROVINCIAID,ID,CALLE_DESC_ES," +
			"CALLE_DESC_EU,TIPOVIAID,ESTADO,CALLECOD,MUNICIPIOID," +
			" TIPO_VIA_DESC_EU,TIPO_VIA_DESC_ES FROM ("+SQL_SELECT +
	/* TIPO_VIA*/ ", " +
	"decode(instr("+TipoViaDaoSql.ALIAS + ".DS_C, '/',1,1),0,"+
	TipoViaDaoSql.ALIAS + ".DS_C,substr("+
	TipoViaDaoSql.ALIAS + ".DS_C,1,instr("+TipoViaDaoSql.ALIAS + ".DS_C, '/',1,1)-1))TIPO_VIA_DESC_ES,"+
	"decode(instr("+TipoViaDaoSql.ALIAS + ".DS_E, '/',1,1),0,"+
	TipoViaDaoSql.ALIAS + ".DS_E,substr("+
	TipoViaDaoSql.ALIAS + ".DS_E,1,instr("+TipoViaDaoSql.ALIAS + ".DS_E, '/',1,1)-1))TIPO_VIA_DESC_EU ";
	public static final String SQL_SELECT_COUNT = "SELECT COUNT(1)";
	public static final String SQL_FROM = " FROM " + CalleDaoSql.TABLA
			+ " " + CalleDaoSql.ALIAS + " ";
	public static final String SQL_FROM_ALL = " FROM " + CalleDaoSql.TABLA
	+ " " + CalleDaoSql.ALIAS + ", " + TipoViaDaoSql.TABLA
	+ " " + TipoViaDaoSql.ALIAS;
	public static final String SQL_WHERE_ID = " WHERE " + PK + " = ? ";
	public static final String SQL_WHERE_BY_CALLE_COD = " WHERE " + CALLE_COD + "= ? ";
	public static final String SQL_WHERE_ALL = " WHERE " + CalleDaoSql.ALIAS + ".TIPO_VIA_ID = " +TipoViaDaoSql.ALIAS + ".ID (+) AND" + CalleDaoSql.ALIAS + ".PROVINCIA_ID = ? AND " + CalleDaoSql.ALIAS + ".MUNICIPIO_ID = ?";

	public static final String SQL_FIND = SQL_SELECT + SQL_FROM + SQL_WHERE_ID;
	public static final String SQL_FIND_BY_CALLE_COD = SQL_SELECT + SQL_FROM + SQL_WHERE_BY_CALLE_COD;
	public static final String SQL_FIND_ALL = SQL_SELECT_ALL + SQL_FROM_ALL;
	public static final String SQL_FIND_ALL_COUNT = SQL_SELECT_COUNT + SQL_FROM_ALL;

}
