public List <string> getLogradouroByLocalidade(string localidade, string logradouro)
{
try
{
sql.Append("SELECT CONCAT(CONCAT(DESIGNATION_ID,':'), REGEXP_SUBSTR(D.TEXT, '[^(]+', 1, 1)) AS LOGRADOURO ");
sql.Append("FROM DES_DESIGNATION D ");
sql.Append("WHERE D.PARENT_ID = ");
sql.Append("(SELECT D.DESIGNATION_ID ");
sql.Append("FROM DES_DESIGNATION D ");
sql.Append("WHERE D.DESCRIPTION = '" + localidade.ToUpper() + "' ");
sql.Append("AND D.DSY_ID = 10 ");
sql.Append("AND D.LEVEL_NO = 4 ");
sql.Append("AND D.DESIGLEVELID = 34) ");
sql.Append("AND D.DESIG_KIND_ID = 256 ");
sql.Append("AND D.TEXT LIKE '%" + logradouro.ToUpper() + "%' ");
sql.Append("AND D.DESIGLEVELID = 35 ");
sql.Append("AND D.DSY_ID = 10 ");
sql.Append("AND D.LEVEL_NO = 5 ");
sql.Append("ORDER BY D.TEXT ");
return(ConnectionOracleDal.ExecutaSqlReaderGeneric <string>(sql.ToString()));
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
sql.Clear();
}
}
public List <string> getMunicipioByEstado(string estado, string municipio)
{
try
{
sql.Append("SELECT CONCAT(CONCAT(DESIGNATION_ID,':'), D.DESCRIPTION) AS MUNICIPIO ");
sql.Append("FROM DES_DESIGNATION D ");
sql.Append("WHERE D.PARENT_ID = ");
sql.Append("(SELECT D.DESIGNATION_ID ");
sql.Append("FROM DES_DESIGNATION D ");
sql.Append("WHERE D.DESCRIPTION = '" + estado.ToUpper() + "' ");
sql.Append("AND D.DESIGLEVELID = 32 ");
sql.Append("AND D.DSY_ID = 10 ");
sql.Append("AND D.LEVEL_NO = 2) ");
sql.Append("AND D.DESCRIPTION LIKE '%" + municipio.ToUpper() + "%' ");
sql.Append("AND D.DESIGLEVELID = 33 ");
sql.Append("AND D.DSY_ID = 10 ");
sql.Append("AND D.LEVEL_NO = 3 ");
sql.Append("ORDER BY D.DESCRIPTION ");
return(ConnectionOracleDal.ExecutaSqlReaderGeneric <string>(sql.ToString()));
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
sql.Clear();
}
}
public List <string> getBairroByIdLogradouro(int idLogradouro)
{
try
{
sql.Append("SELECT D.TEXT AS BAIRRO ");
sql.Append("FROM DES_DESIGNATION D ");
sql.Append("WHERE D.PARENT_ID = " + idLogradouro + " ");
sql.Append("AND D.DESIG_KIND_ID = 262 ");
sql.Append("AND D.DESIGLEVELID = 36 ");
sql.Append("AND D.DSY_ID = 10 ");
sql.Append("AND D.LEVEL_NO = 6 ");
return(ConnectionOracleDal.ExecutaSqlReaderGeneric <string>(sql.ToString()));
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
sql.Clear();
}
}
public List <string> getLocalidadeByMunicipio(int idMunicipio, string localidade)
{
try
{
sql.Append("SELECT CONCAT(CONCAT(DESIGNATION_ID,':'), D.DESCRIPTION) AS LOCALIDADE ");
sql.Append("FROM DES_DESIGNATION D ");
sql.Append("WHERE D.PARENT_ID = " + idMunicipio + " ");
sql.Append("AND D.DESCRIPTION LIKE '%" + localidade.ToUpper() + "%' ");
sql.Append("AND D.DESIGLEVELID = 34 ");
sql.Append("AND D.DSY_ID = 10 ");
sql.Append("AND D.LEVEL_NO = 4 ");
sql.Append("ORDER BY D.DESCRIPTION ");
return(ConnectionOracleDal.ExecutaSqlReaderGeneric <string>(sql.ToString()));
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
sql.Clear();
}
}