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); }