public List <Tripulacao> GetList()
{
List <Tripulacao> model = new List <Tripulacao>();
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM TRIPULACAO";
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Tripulacao trip = new Tripulacao();
trip.id = (decimal)reader["ID"];
trip.pilotoCPF = reader["PILOTOCPF"].ToString();
trip.copilotoCPF = reader["COPILOTOCPF"].ToString();
trip.comissario1CPF = reader["COMISSARIO1CPF"].ToString();
trip.comissario2CPF = reader["COMISSARIO2CPF"].ToString();
model.Add(trip);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(model);
}
public List <Localizacao> GetList()
{
List <Localizacao> model = new List <Localizacao>();
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM LOCALIZACAO";
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Localizacao localizacao = new Localizacao();
localizacao.id = (decimal)reader["ID"];
localizacao.nome = reader["NOME"].ToString();
localizacao.cidade = reader["CIDADE"].ToString();
localizacao.estado = reader["ESTADO"].ToString();
model.Add(localizacao);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(model);
}
public List <Funcionario> GetList()
{
List <Funcionario> model = new List <Funcionario>();
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM FUNCIONARIO";
try {
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Funcionario funcionario = new Funcionario();
funcionario.cpf = reader["CPF"].ToString();
funcionario.nome = reader["NOME"].ToString();
funcionario.cargo = reader["CARGO"].ToString();
DateTime date = (DateTime)reader["DATACONTRATACAO"];
funcionario.dataContratacao = date.ToString("dd/MM/yyyy");
funcionario.hrVoo = (decimal)reader["HORASVOO"];
model.Add(funcionario);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(model);
}
public List <Passagem> GetPassagem()
{
List <Passagem> model = new List <Passagem>();
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM PASSAGEM";
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Passagem pass = new Passagem();
pass.passageiroCPF = reader["PASSAGEIROCPF"].ToString();
pass.viagemId = (decimal)reader["VIAGEMID"];
pass.poltrona = (decimal)reader["POLTRONA"];
model.Add(pass);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(model);
}
public List <Passageiro> getListPassageiro()
{
List <Passageiro> model = new List <Passageiro>();
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM PASSAGEIRO";
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Passageiro passageiro = new Passageiro();
passageiro.cpf = reader["CPF"].ToString();
passageiro.nome = reader["NOME"].ToString();
DateTime date = (DateTime)reader["DATANASCIMENTO"];
passageiro.dataNascimento = date.ToString("dd/MM/yyyy");
model.Add(passageiro);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(model);
}
public List <Aviao> getListAviao()
{
List <Aviao> model = new List <Aviao>();
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM AVIAO";
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Aviao aviao = new Aviao();
aviao.id = (decimal)reader["ID"];
aviao.modelo = reader["MODELO"].ToString();
DateTime date = (DateTime)reader["DATAFABRICACAO"];
aviao.dataFabricacao = date.ToString("dd/MM/yyyy");
DateTime date_2 = (DateTime)reader["DATAULTIMAMANUTENCAO"];
aviao.dataUltimaManutencao = date_2.ToString("dd/MM/yyyy");
aviao.capacidade = (decimal)reader["CAPACIDADE"];
model.Add(aviao);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(model);
}
public void UpdateViagem(int id, int numeroPassagens, double preco, string data, int origemId, int destinoId, int tripulacaoId, int aviaoId, decimal hrVoo)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "UPDATE VIAGEM SET NUMEROPASSAGENS=" + numeroPassagens +
", PRECO = " + preco +
", DATA = '" + data +
"', ORIGEMID = " + origemId +
", DESTINOID = " + destinoId +
", TRIPULACAOID = " + tripulacaoId +
", AVIAOID = " + aviaoId +
", HORASVOO = " + hrVoo +
" WHERE ID=" + id;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void UpdateFuncionario(string cpf, string nome, string cargo, string dataContratacao, decimal hrVoo)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "UPDATE FUNCIONARIO SET " +
"NOME='" + nome +
"', CARGO='" + cargo +
"', DATACONTRATACAO=TO_DATE('" + dataContratacao + "', 'DD/MM/YYYY')" +
", HORASVOO=" + hrVoo +
" WHERE CPF='" + cpf + "'";
OracleCommand command_test = connection.CreateCommand();
command_test.CommandText = "SELECT * FROM FUNCIONARIO WHERE CPF='" + cpf + "'";
try
{
connection.Open();
OracleDataReader reader = command_test.ExecuteReader();
if (reader.HasRows)
{
command.ExecuteNonQuery();
}
else
{
throw new Exception("Id não existe");
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public List <AnaliseFuncionario> FuncionarioTrip()
{
List <AnaliseFuncionario> model = new List <AnaliseFuncionario>();
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = " SELECT F.CPF" +
" , F.NOME "+
" , F.CARGO "+
" , F.HORASVOO "+
" , T.ID "+
" FROM FUNCIONARIO F" +
" LEFT JOIN TRIPULACAO T" +
" ON F.CPF = T.PILOTOCPF " +
" OR F.CPF = T.COPILOTOCPF " +
" OR F.CPF = T.COMISSARIO1CPF" +
" OR F.CPF = T.COMISSARIO2CPF" +
//" WHERE T.ID IS NULL" +
" ORDER BY T.ID";
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
AnaliseFuncionario analise = new AnaliseFuncionario();
analise.cpf = reader["cpf"].ToString();
analise.nome = reader["nome"].ToString();
analise.cargo = reader["cargo"].ToString();
analise.hrVoo = (decimal)reader["horasvoo"];
if (Convert.IsDBNull(reader["id"]))
{
analise.idTripulacao = "Sem tripulação";
}
else
{
analise.idTripulacao = reader["id"].ToString();
}
model.Add(analise);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(model);
}
public void DeleteFuncionario(string cpf)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "DELETE FROM FUNCIONARIO WHERE CPF='" + cpf + "'";
try
{
connection.Open();
command.ExecuteNonQuery();
}catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void InsertLocalizacao(int id, string nome, string cidade, string estado)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO LOCALIZACAO VALUES(" + id + ",'" + nome + "','" + cidade + "','" + estado + "')";
try
{
connection.Open();
command.ExecuteNonQuery();
}catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public List <AnalisePassageiro> IdadePassageiros()
{
List <AnalisePassageiro> model = new List <AnalisePassageiro>();
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = " SELECT PASSAGEIRO.CPF " +
" , PASSAGEIRO.NOME"+
" , L2.CIDADE"+
" , COUNT(L2.CIDADE) \"QUANT\""+
" ,FLOOR((sysdate-PASSAGEIRO.DATANASCIMENTO)/ 365) \"IDADE\"" +
" FROM PASSAGEIRO" +
" INNER JOIN PASSAGEM" +
" ON PASSAGEIRO.CPF = PASSAGEM.PASSAGEIROCPF" +
" INNER JOIN VIAGEM" +
" ON VIAGEM.ID = PASSAGEM.VIAGEMID" +
" INNER JOIN LOCALIZACAO L1" +
" ON VIAGEM.ORIGEMID = L1.ID " +
" INNER JOIN LOCALIZACAO L2" +
" ON VIAGEM.DESTINOID = L2.ID" +
" WHERE PASSAGEIRO.CPF IN (SELECT p.CPF FROM PASSAGEIRO p WHERE FLOOR((sysdate-p.DATANASCIMENTO)/365) >= 65) " +
" GROUP BY L2.CIDADE, PASSAGEIRO.CPF, PASSAGEIRO.NOME, FLOOR((sysdate-PASSAGEIRO.DATANASCIMENTO)/ 365)";
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
AnalisePassageiro analise = new AnalisePassageiro();
analise.cpf = reader["CPF"].ToString();
analise.nome = reader["NOME"].ToString();
analise.destino = reader["CIDADE"].ToString();
analise.quant = (decimal)reader["QUANT"];
analise.idade = reader["IDADE"].ToString();
model.Add(analise);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(model);
}
public void DeletePassagem(string passageiroCPF, int viagemId)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "DELETE FROM PASSAGEM WHERE PASSAGEIROCPF='" + passageiroCPF + "' AND VIAGEMID = " + viagemId;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void UpdatePassagem(string passageiroCPF, int viagemId, int poltrona)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "UPDATE PASSAGEM SET POLTRONA = " + poltrona + "WHERE PASSAGEIROCPF='" + passageiroCPF + "' AND VIAGEMID = " + viagemId;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void InserePassagem(string passageiroCPF, int viagemId, int poltrona)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO PASSAGEM VALUES ('" + passageiroCPF + "'," + viagemId + "," + poltrona + ")";
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void DeletePassageiro(string cpf, string nome, string dataNascimento)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "DELETE FROM PASSAGEIRO WHERE CPF = '" + cpf + "'";
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void DeleteAviao(int id)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "DELETE FROM AVIAO WHERE ID=" + id;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void InsertPassageiro(string cpf, string nome, string dataNascimento)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO passageiro VALUES ('" + cpf + "', '" + nome + "',TO_DATE('" + dataNascimento + "', 'DD/MM/YYYY'))";
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public List <AnalisePassageiro> ListaPassageiros()
{
List <AnalisePassageiro> model = new List <AnalisePassageiro>();
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "SELECT PASSAGEIRO.CPF" +
" , PASSAGEIRO.NOME" +
" , PASSAGEM.POLTRONA" +
" , L1.CIDADE AS \"ORIGEM\"" +
" , L2.CIDADE AS \"DESTINO\"" +
" , VIAGEM.HORASVOO AS \"TEMPO DE VIAGEM\" " +
"FROM PASSAGEIRO " +
"INNER JOIN PASSAGEM " +
"ON PASSAGEIRO.CPF = PASSAGEM.PASSAGEIROCPF " +
"INNER JOIN VIAGEM " +
"ON VIAGEM.ID = PASSAGEM.VIAGEMID " +
"INNER JOIN LOCALIZACAO L1 " +
"ON VIAGEM.ORIGEMID = L1.ID " +
"INNER JOIN LOCALIZACAO L2 " +
"ON VIAGEM.DESTINOID = L2.ID";
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
AnalisePassageiro analise = new AnalisePassageiro();
analise.cpf = reader["CPF"].ToString();
analise.nome = reader["NOME"].ToString();
analise.poltrona = (decimal)reader["POLTRONA"];
analise.origem = reader["ORIGEM"].ToString();
analise.destino = reader["DESTINO"].ToString();
analise.tempoViagem = (decimal)reader["TEMPO DE VIAGEM"];
model.Add(analise);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(model);
}
public List <AnalisePassageiro> MultViagens()
{
List <AnalisePassageiro> model = new List <AnalisePassageiro>();
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = " SELECT PASSAGEIRO.CPF " +
" , PASSAGEIRO.NOME"+
" , L2.CIDADE"+
" , COUNT(L2.CIDADE) AS \"NUMVEZES\""+
" FROM PASSAGEIRO" +
" INNER JOIN PASSAGEM" +
" ON PASSAGEIRO.CPF = PASSAGEM.PASSAGEIROCPF" +
" INNER JOIN VIAGEM" +
" ON VIAGEM.ID = PASSAGEM.VIAGEMID" +
" INNER JOIN LOCALIZACAO L1" +
" ON VIAGEM.ORIGEMID = L1.ID " +
" INNER JOIN LOCALIZACAO L2" +
" ON VIAGEM.DESTINOID = L2.ID" +
" HAVING COUNT(L2.CIDADE) >1" +
" GROUP BY L2.CIDADE, PASSAGEIRO.CPF, PASSAGEIRO.NOME";
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
AnalisePassageiro analise = new AnalisePassageiro();
analise.cpf = reader["CPF"].ToString();
analise.nome = reader["NOME"].ToString();
analise.destino = reader["CIDADE"].ToString();
analise.quant = (decimal)reader["NUMVEZES"];
model.Add(analise);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(model);
}
public void InsertFuncionario(string cpf, string nome, string cargo, string dataContratacao, decimal hrVoo)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO FUNCIONARIO VALUES ('" + cpf + "','" + nome + "','" + cargo + "',TO_DATE('" + dataContratacao +
"','DD/MM/YYYY')," + hrVoo + ")";
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void UpdatePassageiro(string cpf, string nome, string dataNascimento)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "UPDATE PASSAGEIRO SET NOME = '" + nome + "'" +
", DATANASCIMENTO = '" + dataNascimento + "' WHERE CPF ='" + cpf + "'";
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void InsereTripulacao(int id, string pilotoCPF, string copilotoCPF, string comissario1CPF, string comissario2CPF)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO TRIPULACAO VALUES (" + id + ",'" + pilotoCPF + "','" + copilotoCPF + "','" + comissario1CPF + "','" +
comissario2CPF + "')";
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void InsereViagem(int id, int numeroPassagens, double preco, string data, int origemId, int destinoId, int tripulacaoId, int aviaoId, decimal hrVoo)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO VIAGEM VALUES (" + id + "," + numeroPassagens + "," + preco + ",'" + data + "'," + origemId +
"," + destinoId + "," + tripulacaoId + "," + aviaoId + "," + hrVoo + ")";
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void InsertAviao(int id, string modelo, string dataFabricacao, string dataUltimaManutencao, int capacidade)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO AVIAO VALUES (" + id + ",'" + modelo + "',TO_DATE('" + dataFabricacao + "', 'DD/MM/YYYY'),TO_DATE('" +
dataUltimaManutencao + "', 'DD/MM/YYYY')," + capacidade + ")";
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void UpdateLocalizacao(int id, string nome, string cidade, string estado)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "UPDATE LOCALIZACAO SET NOME='" + nome +
"', CIDADE ='" + cidade +
"', ESTADO = '" + estado +
"' WHERE ID = " + id;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void UpdateTripulacao(int id, string pilotoCPF, string copilotoCPF, string comissario1CPF, string comissario2CPF)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "UPDATE TRIPULACAO SET PILOTOCPF='" + pilotoCPF +
"', COPILOTOCPF='" + copilotoCPF +
"', COMISSARIO1CPF='" + comissario1CPF +
"', COMISSARIO2CPF='" + comissario2CPF + "'" +
"WHERE ID = " + id;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public void UpdateAviao(int id, string modelo, string dataFabricacao, string dataUltimaManutencao, int capacidade)
{
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "UPDATE AVIAO SET" +
" MODELO = '" + modelo +
"', DATAFABRICACAO = TO_DATE('" + dataFabricacao + "', 'DD/MM/YYYY')" +
", DATAULTIMAMANUTENCAO = TO_DATE('" + dataUltimaManutencao + "', 'DD/MM/YYYY')" +
", CAPACIDADE = " + capacidade +
" WHERE ID = " + id;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
public List <Viagem> GetViagens()
{
List <Viagem> model = new List <Viagem>();
using (OracleConnection connection = ConnectionBD.Connection())
{
OracleCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM VIAGEM";
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Viagem viagem = new Viagem();
viagem.id = (decimal)reader["ID"];
viagem.numeroPassagens = (decimal)reader["NUMEROPASSAGENS"];
viagem.preco = (decimal)reader["PRECO"];
DateTime date = (DateTime)reader["DATA"];
viagem.data = date.ToString("dd/MM/yyyy");
viagem.origemId = (decimal)reader["ORIGEMID"];
viagem.destinoId = (decimal)reader["DESTINOID"];
viagem.tripulacaoId = (decimal)reader["TRIPULACAOID"];
viagem.aviaoId = (decimal)reader["AVIAOID"];
viagem.hrVoo = (decimal)reader["HORASVOO"];
model.Add(viagem);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
return(model);
}
