Beispiel #1
0
        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();
            }
        }
Beispiel #2
0
        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();
            }
        }
Beispiel #3
0
        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();
            }
        }
Beispiel #4
0
        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();
            }
        }