Script.sql

USE master;  
GO  
IF DB_ID (N'ApplicationDB') IS NOT NULL
DROP DATABASE ApplicationDB;
GO
CREATE DATABASE ApplicationDB;  
GO  

USE ApplicationDB
GO

/*TABELAS*/
CREATE TABLE TIPOS_AERONAVES(
	IDTIPOAERONAVE	INT IDENTITY(1,1) PRIMARY KEY,
	DESCRICAO		VARCHAR(5) NOT NULL,
	ATIVO		BIT NOT NULL
)
GO

INSERT INTO TIPOS_AERONAVES (DESCRICAO, ATIVO) 
VALUES ('A320', 1), ('A330', 1), ('B777', 1) 

CREATE TABLE AERONAVES(
	IDAERONAVE		INT IDENTITY(1,1) PRIMARY KEY,
	MATRICULA		VARCHAR(7) NOT NULL,
	IDTIPOAERONAVE	INT FOREIGN KEY REFERENCES TIPOS_AERONAVES(IDTIPOAERONAVE) NOT NULL,
	ATIVO		BIT NOT NULL
)
GO

INSERT INTO AERONAVES (MATRICULA, IDTIPOAERONAVE, ATIVO)
VALUES ('PT-1234', 1, 1), ('PR-3333', 2, 1), ('PS-4444',3, 1)

CREATE TABLE AEROPORTOS(
	IDAEROPORTO	INT IDENTITY(1,1) PRIMARY KEY,
	SIGLA		VARCHAR(4) NOT NULL,
	ATIVO		BIT NOT NULL
)
GO

INSERT INTO AEROPORTOS (SIGLA, ATIVO)
VALUES ('SBGR', 1), ('SBSP', 1), ('SBCF', 1)

CREATE TABLE PLANO_VOO(
	IDPLANOVOO			INT IDENTITY(1,1) PRIMARY KEY,
	NUMEROVOO			VARCHAR(7) NOT NULL,
	DATA				DATETIME NOT NULL,
	IDAERONAVE			INT FOREIGN KEY REFERENCES AERONAVES (IDAERONAVE),
	IDAEROPORTOORIGEM	INT FOREIGN KEY REFERENCES AEROPORTOS (IDAEROPORTO),
	IDAEROPORTODESTINO	INT FOREIGN KEY REFERENCES AEROPORTOS (IDAEROPORTO),
	ATIVO				BIT NOT NULL
)
GO

INSERT INTO PLANO_VOO (NUMEROVOO, DATA, IDAERONAVE, IDAEROPORTOORIGEM, IDAEROPORTODESTINO, ATIVO)
VALUES ('TAM1234', GETDATE(), 1, 1, 2,1),
('AZU2222', GETDATE(), 2, 1, 3,1),
('TAP3333', GETDATE(), 3, 2, 3,1)
GO

/*PROCEDURES - PLANO DE VOO*/
CREATE PROCEDURE INSERIR_PLANO_VOO(
	@NUMEROVOO			VARCHAR(7),
	@DATA				DATETIME,
	@IDAERONAVE			INT,
	@IDAEROPORTOORIGEM	INT,
	@IDAEROPORTODESTINO	INT
)
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM [dbo].[PLANO_VOO] WHERE NUMEROVOO = @NUMEROVOO AND ATIVO = 1)
	BEGIN		
		INSERT INTO [dbo].[PLANO_VOO]
			   ([NUMEROVOO]
			   ,[DATA]
			   ,[IDAERONAVE]
			   ,[IDAEROPORTOORIGEM]
			   ,[IDAEROPORTODESTINO]
			   ,[ATIVO])
		 VALUES
			   (UPPER(@NUMEROVOO)			
			   ,@DATA				
			   ,@IDAERONAVE			
			   ,@IDAEROPORTOORIGEM
			   ,@IDAEROPORTODESTINO
			   ,1)
	END
	ELSE
		RAISERROR('Já existe um Plano de Vôo com este número',16,1);

END
GO

CREATE PROCEDURE ATUALIZAR_PLANO_VOO(
	@IDPLANOVOO			INT,
	@NUMEROVOO			VARCHAR(7),
	@DATA				DATETIME,
	@IDAERONAVE			INT,
	@IDAEROPORTOORIGEM	INT,
	@IDAEROPORTODESTINO	INT
)
AS
BEGIN
	DECLARE @NUMEROVOOATUAL VARCHAR(7)
	SELECT @NUMEROVOOATUAL = NUMEROVOO FROM [dbo].[PLANO_VOO] WHERE IDPLANOVOO = @IDPLANOVOO
	
	--VERIFICAR SE O USUÁRIO ESTÁ TENTANDO ALTERAR O NÚMERO DO VÔO PARA UM NÚMERO JÁ EXISTENTE
	IF @NUMEROVOOATUAL <> @NUMEROVOO
	BEGIN
		IF EXISTS (SELECT * FROM [dbo].[PLANO_VOO] WHERE NUMEROVOO = @NUMEROVOO AND ATIVO = 1)
		BEGIN
			RAISERROR('Já existe um Plano de Vôo com este número',16,1);
		END
	END
	
	UPDATE [dbo].[PLANO_VOO]
	   SET [NUMEROVOO] = UPPER(@NUMEROVOO)
		  ,[DATA] = @DATA
		  ,[IDAERONAVE] = @IDAERONAVE
		  ,[IDAEROPORTOORIGEM] = @IDAEROPORTOORIGEM
		  ,[IDAEROPORTODESTINO] = @IDAEROPORTODESTINO
	 WHERE IDPLANOVOO = @IDPLANOVOO
END
GO

CREATE PROCEDURE BUSCAR_PLANO_VOO(
	@IDPLANOVOO	INT = NULL
)
AS
BEGIN
	SELECT IDPLANOVOO
      ,P.NUMEROVOO
      ,P.DATA
      ,P.IDAERONAVE
      ,P.IDAEROPORTOORIGEM
      ,P.IDAEROPORTODESTINO
	  ,A.MATRICULA
	  ,T.DESCRICAO AS TIPOAERONAVE
	  ,ORIGEM.SIGLA AS ORIGEM
	  ,DESTINO.SIGLA AS DESTINO
  FROM PLANO_VOO P
  INNER JOIN AERONAVES A ON P.IDAERONAVE = A.IDAERONAVE
  INNER JOIN TIPOS_AERONAVES T ON T.IDTIPOAERONAVE = A.IDTIPOAERONAVE
  INNER JOIN AEROPORTOS ORIGEM ON ORIGEM.IDAEROPORTO = P.IDAEROPORTOORIGEM
  INNER JOIN AEROPORTOS DESTINO ON DESTINO.IDAEROPORTO = P.IDAEROPORTODESTINO
  WHERE IDPLANOVOO = CASE WHEN @IDPLANOVOO IS NULL THEN IDPLANOVOO ELSE @IDPLANOVOO END 
  AND P.ATIVO = 1
END
GO

CREATE PROCEDURE EXCLUIR_PLANO_VOO(
	@IDPLANOVOO	INT
)
AS
BEGIN
	UPDATE PLANO_VOO SET ATIVO = 0 WHERE IDPLANOVOO = @IDPLANOVOO
END
GO

/*PROCEDURES - AERONAVES*/

CREATE PROCEDURE BUSCAR_AERONAVES(
	@IDAERONAVE	INT = NULL
)
AS
BEGIN
	SELECT IDAERONAVE
      ,MATRICULA
      ,IDTIPOAERONAVE
      ,ATIVO
  FROM AERONAVES
  WHERE IDAERONAVE = CASE WHEN @IDAERONAVE IS NULL THEN IDAERONAVE ELSE @IDAERONAVE END 
END
GO

/*PROCEDURES - TIPOS_AERONAVES*/
CREATE PROCEDURE BUSCAR_TIPOS_AERONAVES(
	@IDTIPOAERONAVE	INT = NULL
)
AS
BEGIN
	SELECT [IDTIPOAERONAVE]
      ,[DESCRICAO]
      ,[ATIVO]
  FROM [dbo].[TIPOS_AERONAVES]
  WHERE IDTIPOAERONAVE = CASE WHEN @IDTIPOAERONAVE IS NULL THEN IDTIPOAERONAVE ELSE @IDTIPOAERONAVE END 
END
GO

/*PROCEDURES - AEROPORTOS*/
CREATE PROCEDURE BUSCAR_AEROPORTOS(
	@IDAEROPORTO	INT = NULL
)
AS
BEGIN
	SELECT [IDAEROPORTO]
      ,[SIGLA]
      ,[ATIVO]
  FROM [dbo].[AEROPORTOS]
  WHERE IDAEROPORTO = CASE WHEN @IDAEROPORTO IS NULL THEN IDAEROPORTO ELSE @IDAEROPORTO END 
END


CREATE TABLE Users (
	IdUser	int identity(1,1) primary key,
	Name		varchar(200) not null, 
	Email		varchar(100) not null,
	Password		varchar(300) not null,
	Salt		varchar(300) not null,	
	Active		bit not null
)

CREATE TABLE Roles (
	IdRole	int identity(1,1) primary key,
	Description	varchar(200) not null,
	Active		bit not null
)

CREATE TABLE UserRoles(
	IdUserRole	int identity(1,1) primary key,
	IdUser	int foreign key references Usuario (IdUser),
	IdRole	int foreign key references Perfil (IdRole)
)

INSERT INTO ROLES (Description, Active) VALUES 
('Gerente', 1),
('Funcionario', 1),
('Coordenador', 1)