Notes

USE [Argo_BCD]
GO
/****** Object: StoredProcedure [dbo].[AeroportoSiglaCidade_Autocomplete_proc_NextGen] Script Date: 12/19/2017 9:26:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =================================================================
-- Author: <Lins>
-- Create date: <13/01/2014>
-- Description: <Códigos de aeroportos junto com o código da cidade>
-- Diogo - 05/11/2015 - WO 7378 - inclusão da coluna 'traduções'
-- Samara - 15/03/2016 - WO 8274 - novo modelo de tradução de cidades, estados, países e aeroportos
-- Valeria - 24-06-2016 - Valeria Pereira - Campo Sigla
-- Diogo - 15/07/2016 - M 20655 - inclusão da coluna CidadeId
-- Diogo - 28/10/2016 - M 30680 - quando @apenasComIata = 0 lista cidades junto do aeroporto quando digitado 3 caracteres, com aeroporto ordenado primeiro
-- Diogo - 16/09/2017 - M 43402 - quando digitado 3 caracteres e for apenasComIata obtem aeroportos e cidade pela sigla e nome
-- Luiz Silva - 25/10/2017 - M 46726 - ajuste para não retornarem aeroporto e cidades com a mesma SIGLA - exemplo MIA
-- =================================================================
ALTER PROCEDURE [dbo].[AeroportoSiglaCidade_Autocomplete_proc_NextGen]
(
@nome VARCHAR(50),
@ClienteId VARCHAR(255) = NULL,
@UserId VARCHAR(255) = NULL,
@BuscaNacional BIT = 0,
@apenasComIata BIT = 1,
@Idioma CHAR(5)
)
AS
-----// VERIFICA POLÍTICA //--
DECLARE @QtdeClienteId INT
DECLARE @QtdeEmpresaId INT
DECLARE @QtdeCentroCustoId INT
DECLARE @QtdeNivelId INT
DECLARE @QtdeUserId INT

DECLARE @EmpresaId VARCHAR(255)
DECLARE @CentroCustoId VARCHAR(255)
DECLARE @NivelId VARCHAR(255)
DECLARE @varEmpresaId VARCHAR(255)
DECLARE @varCentroCustoId VARCHAR(255)
DECLARE @varNivelId VARCHAR(255)
DECLARE @varUserId VARCHAR(255)

DECLARE @PaisId INT
DECLARE @Permissao BIT
SET @Permissao = 1

--// OBTEM EMPRESA, CCUSTO E NIVEL DO USUÁRIO E PAIS DO CLIENTE //--
SELECT @EmpresaId = EmpresaId, @CentroCustoId = CentroCusto, @NivelId = Nivel FROM Usuario (NOLOCK) WHERE UserId = @UserId
SELECT @PaisId = PaisId FROM Cliente (NOLOCK) WHERE ClienteId = @ClienteId

IF @BuscaNacional = 0
BEGIN
IF @Permissao = 1
BEGIN
SET @PaisId = NULL
END
END

IF @ClienteId IS NOT NULL
BEGIN
--// VERIFICA EM QUAIS CHAVES EXISTE POLÍTICA //--
SELECT @QtdeClienteId = COUNT(*), @QtdeEmpresaId = SUM(X.TotEmpresa), @QtdeCentroCustoId = SUM(X.TotCentroCusto), @QtdeNivelId = SUM(X.TotNivel), @QtdeUserId = SUM(X.TotUser)
FROM (SELECT CASE WHEN EmpresaId IS NULL THEN 0 ELSE 1 END AS TotEmpresa,
CASE WHEN CentroCustoId IS NULL THEN 0 ELSE 1 END AS TotCentroCusto,
CASE WHEN NivelId IS NULL THEN 0 ELSE 1 END AS TotNivel,
CASE WHEN UsuarioId IS NULL THEN 0 ELSE 1 END AS TotUser
FROM PoliticaCliente (NOLOCK)
WHERE ClienteId = @ClienteId
AND PoliticaId = 'VIAINT'
AND DataFinalizacao IS NULL
AND (CONVERT(Varchar(255), EmpresaId) = @EmpresaId OR CONVERT(Varchar(255), CentroCustoId) = @CentroCustoId OR CONVERT(Varchar(255), NivelId) = @NivelId
OR CONVERT(Varchar(255), UsuarioId) = @UserId OR (EmpresaId IS NULL AND CentroCustoId IS NULL AND NivelId IS NULL AND UsuarioId IS NULL))) AS X

--// OBTEM CHAVE DA POLÍTICA //--
IF @QtdeUserId > 0
BEGIN
SET @varUserId = @UserId
END
ELSE
BEGIN
IF @QtdeNivelId > 0
BEGIN
SET @varNivelId = @NivelId
END
ELSE
BEGIN
IF @QtdeCentroCustoId > 0
BEGIN
SET @varCentroCustoId = @CentroCustoId
END
ELSE
BEGIN
IF @QtdeEmpresaId > 0
BEGIN
SET @varEmpresaId = @EmpresaId
END
END
END
END

--// POSSUI PELOS MENOS UMA POLÍTICA //--
IF @QtdeClienteId > 0
BEGIN
SELECT @Permissao = Permissao
FROM PoliticaCliente (NOLOCK)
WHERE ClienteId = @ClienteId
AND ISNULL(CONVERT(Varchar(255), EmpresaId), '') = ISNULL(@varEmpresaId, '')
AND ISNULL(CONVERT(Varchar(255), CentroCustoId), '') = ISNULL(@varCentroCustoId, '')
AND ISNULL(CONVERT(Varchar(255), NivelId), '') = ISNULL(@varNivelId, '')
AND ISNULL(CONVERT(Varchar(255), UsuarioId), '') = ISNULL(@varUserId, '')
AND PoliticaId = 'VIAINT'
AND DataFinalizacao IS NULL
END
END

IF LEN(@nome) = 3
BEGIN
IF @apenasComIata = 1
BEGIN
--// RETORNA AEROPORTO COM A SIGLA DE 3 CARACTERES //--
SELECT ISNULL(CidadeTraducao.Nome,Cidade.Nome) + ISNULL(' / ' + Estado.Sigla, '') + ', ' + RTRIM(ISNULL(PaisTraducao.Nome,Pais.Nome)) +
CASE WHEN Aeroporto.Nome = Cidade.Nome THEN '' ELSE ' - ' + ISNULL(AeroportoTraducao.Nome,Aeroporto.Nome) END + ' (' + Aeroporto.Sigla + ')' AS nome_sigla,
Pais.PaisId,
Aeroporto.Sigla,
Aeroporto.CidadeId
FROM Aeroporto (NOLOCK)
INNER JOIN Cidade (NOLOCK) ON Aeroporto.CidadeId = Cidade.CidadeId
LEFT JOIN Estado (NOLOCK) ON Cidade.EstadoId = Estado.EstadoId
INNER JOIN Pais (NOLOCK) ON Cidade.PaisId = Pais.PaisId
LEFT JOIN CidadeTraducao (NOLOCK) ON Cidade.CidadeId = CidadeTraducao.CidadeId and CidadeTraducao.Idioma = @Idioma
LEFT JOIN PaisTraducao (NOLOCK) ON Pais.PaisId = PaisTraducao.PaisId and PaisTraducao.Idioma = @Idioma
LEFT JOIN AeroportoTraducao (NOLOCK) ON Aeroporto.Sigla = AeroportoTraducao.SiglaAeroporto and AeroportoTraducao.Idioma = @Idioma
WHERE Aeroporto.Sigla = @nome
AND (@PaisId IS NULL OR Pais.PaisId = @PaisId)
AND (Cidade.CidadeAtiva = 1)
AND Cidade.Sigla <> @nome
UNION
--// RETORNA AEROPORTO COM O NOME DE 3 CARACTERES //--
SELECT ISNULL(CidadeTraducao.Nome,Cidade.Nome) + ISNULL(' / ' + Estado.Sigla, '') + ', ' + RTRIM(ISNULL(PaisTraducao.Nome,Pais.Nome)) +
CASE WHEN Aeroporto.Nome = Cidade.Nome THEN '' ELSE ' - ' + ISNULL(AeroportoTraducao.Nome,Aeroporto.Nome) END + ' (' + Aeroporto.Sigla + ')' AS nome_sigla,
Pais.PaisId,
Aeroporto.Sigla,
Aeroporto.CidadeId
FROM Aeroporto (NOLOCK)
INNER JOIN Cidade (NOLOCK) ON Aeroporto.CidadeId = Cidade.CidadeId
LEFT JOIN Estado (NOLOCK) ON Cidade.EstadoId = Estado.EstadoId
INNER JOIN Pais (NOLOCK) ON Cidade.PaisId = Pais.PaisId
LEFT JOIN CidadeTraducao (NOLOCK) ON Cidade.CidadeId = CidadeTraducao.CidadeId and CidadeTraducao.Idioma = @Idioma
LEFT JOIN PaisTraducao (NOLOCK) ON Pais.PaisId = PaisTraducao.PaisId and PaisTraducao.Idioma = @Idioma
LEFT JOIN AeroportoTraducao (NOLOCK) ON Aeroporto.Sigla = AeroportoTraducao.SiglaAeroporto and AeroportoTraducao.Idioma = @Idioma
WHERE Aeroporto.Nome = @nome
AND (@PaisId IS NULL OR Pais.PaisId = @PaisId)
AND (Cidade.CidadeAtiva = 1)
AND Cidade.Sigla <> @nome
UNION
--// RETORNA CIDADE QUE CONTEM AEROPORTO COM A SIGLA DE 3 CARACTERES //--
SELECT ISNULL(CidadeTraducao.Nome,Cidade.Nome) + ISNULL(' / ' + Estado.Sigla, '') + ', ' + RTRIM(ISNULL(PaisTraducao.Nome,Pais.Nome)) +
' (' + ISNULL(Cidade.Sigla, Cidade.SiglaGDS) + ')' AS nome_sigla,
Pais.PaisId,
ISNULL(Cidade.Sigla, Cidade.SiglaGDS) AS Sigla,
Cidade.CidadeId
FROM Cidade (NOLOCK)
INNER JOIN Aeroporto (NOLOCK) ON Cidade.CidadeId = Aeroporto.CidadeId
LEFT JOIN Estado (NOLOCK) ON Cidade.EstadoId = Estado.EstadoId
INNER JOIN Pais (NOLOCK) ON Cidade.PaisId = Pais.PaisId
LEFT JOIN CidadeTraducao (NOLOCK) ON Cidade.CidadeId = CidadeTraducao.CidadeId and CidadeTraducao.Idioma = @Idioma
LEFT JOIN PaisTraducao (NOLOCK) ON Pais.PaisId = PaisTraducao.PaisId and PaisTraducao.Idioma = @Idioma
WHERE Cidade.Sigla = @nome
AND (@PaisId IS NULL OR Pais.PaisId = @PaisId)
AND (Cidade.CidadeAtiva = 1)
UNION
--// RETORNA CIDADE QUE CONTEM AEROPORTO COM O NOME DE 3 CARACTERES //--
SELECT ISNULL(CidadeTraducao.Nome,Cidade.Nome) + ISNULL(' / ' + Estado.Sigla, '') + ', ' + RTRIM(ISNULL(PaisTraducao.Nome,Pais.Nome)) +
ISNULL(' (' + ISNULL(Cidade.Sigla, Cidade.SiglaGDS) + ')', '') AS nome_sigla,
Pais.PaisId,
ISNULL(Cidade.Sigla, Cidade.SiglaGDS) AS Sigla,
Cidade.CidadeId
FROM Cidade (NOLOCK)
INNER JOIN Aeroporto (NOLOCK) ON Cidade.CidadeId = Aeroporto.CidadeId
LEFT JOIN Estado (NOLOCK) ON Cidade.EstadoId = Estado.EstadoId
INNER JOIN Pais (NOLOCK) ON Cidade.PaisId = Pais.PaisId
LEFT JOIN CidadeTraducao (NOLOCK) ON Cidade.CidadeId = CidadeTraducao.CidadeId and CidadeTraducao.Idioma = @Idioma
LEFT JOIN PaisTraducao (NOLOCK) ON Pais.PaisId = PaisTraducao.PaisId and PaisTraducao.Idioma = @Idioma
WHERE Cidade.Nome = @nome
AND (@PaisId IS NULL OR Pais.PaisId = @PaisId)
AND (Cidade.CidadeAtiva = 1)
RETURN
END
ELSE
BEGIN
SELECT TOP 150
X.Ordem, X.nome_sigla, X.PaisId, X.Sigla, X.CidadeId
FROM (
--// RETORNA PRIORIZADO AEROPORTO COM A SIGLA DE 3 CARACTERES //--
SELECT 1 AS Ordem,
ISNULL(CidadeTraducao.Nome,Cidade.Nome) + ISNULL(' / ' + Estado.Sigla, '') + ', ' + RTRIM(ISNULL(PaisTraducao.Nome,Pais.Nome)) +
CASE WHEN Aeroporto.Nome = Cidade.Nome THEN '' ELSE ' - ' + ISNULL(AeroportoTraducao.Nome,Aeroporto.Nome) END + ' (' + Aeroporto.Sigla + ')' AS nome_sigla,
Pais.PaisId,
Aeroporto.Sigla,
Aeroporto.CidadeId
FROM Aeroporto (NOLOCK)
INNER JOIN Cidade (NOLOCK) ON Aeroporto.CidadeId = Cidade.CidadeId
LEFT JOIN Estado (NOLOCK) ON Cidade.EstadoId = Estado.EstadoId
INNER JOIN Pais (NOLOCK) ON Cidade.PaisId = Pais.PaisId
LEFT JOIN CidadeTraducao (NOLOCK) ON Cidade.CidadeId = CidadeTraducao.CidadeId and CidadeTraducao.Idioma = @Idioma
LEFT JOIN PaisTraducao (NOLOCK) ON Pais.PaisId = PaisTraducao.PaisId and PaisTraducao.Idioma = @Idioma
LEFT JOIN AeroportoTraducao (NOLOCK) ON Aeroporto.Sigla = AeroportoTraducao.SiglaAeroporto and AeroportoTraducao.Idioma = @Idioma
WHERE Aeroporto.Sigla = @nome
AND (@PaisId IS NULL OR Pais.PaisId = @PaisId)
AND (Cidade.CidadeAtiva = 1)
UNION
--// RETORNA PRIORIZADO CIDADE COM A SIGLA DE 3 CARACTERES //--
SELECT 1 AS Ordem,
ISNULL(CidadeTraducao.Nome,Cidade.Nome) + ISNULL(' / ' + Estado.Sigla, '') + ', ' + RTRIM(ISNULL(PaisTraducao.Nome,Pais.Nome)) +
' (' + ISNULL(Cidade.Sigla,Cidade.SiglaGDS) + ')' AS nome_sigla,
Pais.PaisId,
Cidade.Sigla,
Cidade.CidadeId
FROM Cidade (NOLOCK)
LEFT JOIN Estado (NOLOCK) ON Cidade.EstadoId = Estado.EstadoId
INNER JOIN Pais (NOLOCK) ON Cidade.PaisId = Pais.PaisId
LEFT JOIN CidadeTraducao (NOLOCK) ON Cidade.CidadeId = CidadeTraducao.CidadeId and CidadeTraducao.Idioma = @Idioma
LEFT JOIN PaisTraducao (NOLOCK) ON Pais.PaisId = PaisTraducao.PaisId and PaisTraducao.Idioma = @Idioma
WHERE Cidade.Sigla = @nome
AND (@PaisId IS NULL OR Pais.PaisId = @PaisId)
AND (Cidade.CidadeAtiva = 1)
UNION
--// RETORNA PRIORIZADO CIDADE COM O NOME DE 3 CARACTERES //--
SELECT 1 AS Ordem,
ISNULL(CidadeTraducao.Nome,Cidade.Nome) + ISNULL(' / ' + Estado.Sigla, '') + ', ' + RTRIM(ISNULL(PaisTraducao.Nome,Pais.Nome)) +
ISNULL(' (' + ISNULL(Cidade.Sigla, Cidade.SiglaGDS) + ')', '') AS nome_sigla,
Pais.PaisId,
Cidade.Sigla,
Cidade.CidadeId
FROM Cidade (NOLOCK)
LEFT JOIN Estado (NOLOCK) ON Cidade.EstadoId = Estado.EstadoId
INNER JOIN Pais (NOLOCK) ON Cidade.PaisId = Pais.PaisId
LEFT JOIN CidadeTraducao (NOLOCK) ON Cidade.CidadeId = CidadeTraducao.CidadeId and CidadeTraducao.Idioma = @Idioma
LEFT JOIN PaisTraducao (NOLOCK) ON Pais.PaisId = PaisTraducao.PaisId and PaisTraducao.Idioma = @Idioma
WHERE ISNULL(CidadeTraducao.Nome,Cidade.Nome) = @nome
AND (@PaisId IS NULL OR Pais.PaisId = @PaisId)
AND (Cidade.CidadeAtiva = 1)
UNION
--// RETORNA CIDADES COM O TEXTO CONTIDO NO NOME //--
SELECT 2 AS Ordem,
ISNULL(CidadeTraducao.Nome,Cidade.Nome) + ISNULL(' / ' + Estado.Sigla, '') + ', ' + RTRIM(ISNULL(PaisTraducao.Nome,Pais.Nome)) +
ISNULL(' (' + ISNULL(Cidade.Sigla, Cidade.SiglaGDS) + ')', '') AS nome_sigla,
Pais.PaisId,
ISNULL(Cidade.Sigla, Cidade.SiglaGDS) AS Sigla,
Cidade.CidadeId
FROM Cidade (NOLOCK)
LEFT JOIN Estado (NOLOCK) ON Cidade.EstadoId = Estado.EstadoId
INNER JOIN Pais (NOLOCK) ON Cidade.PaisId = Pais.PaisId
LEFT JOIN CidadeTraducao (NOLOCK) ON Cidade.CidadeId = CidadeTraducao.CidadeId and CidadeTraducao.Idioma = @Idioma
LEFT JOIN PaisTraducao (NOLOCK) ON Pais.PaisId = PaisTraducao.PaisId and PaisTraducao.Idioma = @Idioma
WHERE ISNULL(CidadeTraducao.Nome,Cidade.Nome) LIKE '%' + @nome + '%'
AND (@PaisId IS NULL OR Pais.PaisId = @PaisId)
AND (Cidade.CidadeAtiva = 1)
) AS X
ORDER BY X.Ordem, X.nome_sigla
RETURN
END
END

--// LISTA AUMENTADA PARA 150 //--
SELECT TOP 150 X.nome_sigla, X.PaisId, X.Sigla, X.CidadeId
FROM (SELECT ISNULL(CidadeTraducao.Nome,Cidade.Nome) + ISNULL(' / ' + Estado.Sigla, '') + ', ' + RTRIM(ISNULL(PaisTraducao.Nome,Pais.Nome)) +
CASE WHEN Aeroporto.Nome = Cidade.Nome THEN '' ELSE ' - ' + ISNULL(AeroportoTraducao.Nome,Aeroporto.Nome) END + ' (' + Aeroporto.Sigla + ')' AS nome_sigla,
Pais.PaisId,
Aeroporto.Sigla,
Aeroporto.CidadeId
FROM Aeroporto (NOLOCK)
INNER JOIN Cidade (NOLOCK) ON Aeroporto.CidadeId = Cidade.CidadeId
LEFT JOIN Estado (NOLOCK) ON Cidade.EstadoId = Estado.EstadoId
INNER JOIN Pais (NOLOCK)ON Cidade.PaisId = Pais.PaisId
LEFT JOIN CidadeTraducao (NOLOCK) ON Cidade.CidadeId = CidadeTraducao.CidadeId and CidadeTraducao.Idioma = @Idioma
LEFT JOIN PaisTraducao (NOLOCK) ON Pais.PaisId = PaisTraducao.PaisId and PaisTraducao.Idioma = @Idioma
LEFT JOIN AeroportoTraducao (NOLOCK) ON Aeroporto.Sigla = AeroportoTraducao.SiglaAeroporto and AeroportoTraducao.Idioma = @Idioma
WHERE (ISNULL(AeroportoTraducao.Nome,Aeroporto.Nome) LIKE '%' + @nome + '%' OR ISNULL(CidadeTraducao.Nome,Cidade.Nome) LIKE '%' + @nome + '%')
AND (Cidade.CidadeAtiva = 1)
UNION
SELECT ISNULL(CidadeTraducao.Nome,Cidade.Nome) + ISNULL(' / ' + Estado.Sigla, '') + ', ' + RTRIM(ISNULL(PaisTraducao.Nome,Pais.Nome)) +
ISNULL(' (' + ISNULL(Cidade.Sigla, Cidade.SiglaGDS) + ')', '') AS nome_sigla,
Pais.PaisId,
ISNULL(Cidade.Sigla, Cidade.SiglaGDS) AS Sigla,
Cidade.CidadeId
FROM Cidade (NOLOCK)
LEFT JOIN Estado (NOLOCK) ON Cidade.EstadoId = Estado.EstadoId
INNER JOIN Pais (NOLOCK) ON Cidade.PaisId = Pais.PaisId
LEFT JOIN CidadeTraducao (NOLOCK) ON Cidade.CidadeId = CidadeTraducao.CidadeId and CidadeTraducao.Idioma = @Idioma
LEFT JOIN PaisTraducao (NOLOCK) ON Pais.PaisId = PaisTraducao.PaisId and PaisTraducao.Idioma = @Idioma
WHERE ISNULL(CidadeTraducao.Nome,Cidade.Nome) LIKE '%' + @nome + '%'
AND (Cidade.CidadeAtiva = 1)
AND @apenasComIata = 0
) AS X
WHERE (@PaisId IS NULL OR PaisId = @PaisId)
ORDER BY X.nome_sigla