public static void updateFligt(Modele.ORM.flight Filght)
{
string query = "UPDATE flight SET Available_Places = @places WHERE id=@id";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
cmd.Parameters.AddWithValue("@id", Filght.Id);
cmd.Parameters.AddWithValue("@places", Filght.AvailablePlaces);
//Create a data reader and Execute the command
cmd.ExecuteNonQuery();
//close Connection
connection.CloseConnection();
}
}
public static void insertPlane(int type, int warehouse, bool Status)
{
Status = true;
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
string query = "INSERT INTO plane ( id_Type, id_Warehouse, Status)" +
" VALUES( @Type, @Warehouse, @Status)";
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//shield injection
cmd.Parameters.AddWithValue("@Type", type);
//cmd.Parameters.AddWithValue("@Crew", Plane.Crew);
cmd.Parameters.AddWithValue("@Warehouse", warehouse);
cmd.Parameters.AddWithValue("@Status", Status);
//Execute the command
cmd.ExecuteNonQuery();
//close Connection
connection.CloseConnection();
}
else
{
MessageBox.Show("Connexion failed !");
}
}
//Select statement
public static void insertFlight(Modele.ORM.plane APlane, string longdatetimeD, string longdatetimeA)
{
int seat = APlane.Type.NbSeatingPlaces;
int idPlane = APlane.IdPlane;
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
string query = "INSERT INTO flight ( id_Plane, Available_Places, Hours_Departure,Hours_Arrival)" +
" VALUES( @idPlane,@seat, @HoursD,@HoursA)";
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//shield injection
cmd.Parameters.AddWithValue("@seat", seat);
cmd.Parameters.AddWithValue("@idPlane", idPlane);
cmd.Parameters.AddWithValue("@HoursD", longdatetimeD);
cmd.Parameters.AddWithValue("@HoursA", longdatetimeA);
//Execute the command
cmd.ExecuteNonQuery();
//close Connection
connection.CloseConnection();
}
else
{
MessageBox.Show("Connexion failed !");
}
}
//Select statement
public static ObservableCollection <Modele.ORM.plane> getPlanes()
{
ObservableCollection <Modele.ORM.plane> Planes = new ObservableCollection <Modele.ORM.plane>();
string query = "SELECT * FROM plane;";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
int idplane = dataReader.GetInt32(0);
Modele.ORM.type Type = Modele.ORM.type.GetType(dataReader.GetInt32(1));
Modele.ORM.crew Crew;
if (dataReader[2] != DBNull.Value)
{
Crew = Modele.ORM.crew.GetCrew(dataReader.GetInt32(2));
}
else
{
Crew = null;
}
Modele.ORM.warehouse Warehouse = Modele.ORM.warehouse.GetWarehouse(dataReader.GetInt32(3));
bool status = dataReader.GetBoolean(4);
Modele.ORM.plane Plane = new Modele.ORM.plane(idplane, Type, Crew, Warehouse, status);
Planes.Add(Plane);
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
//return list to be displayed
return(Planes);
}
else
{
return(Planes);
}
}
//Select statement
public static Modele.ORM.salaried getSalaried(int idSalaried)
{
Modele.ORM.salaried Salaried = new Modele.ORM.salaried();
string query = "SELECT * FROM type where id=@idSalaried;";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//shield sql injection
cmd.Parameters.AddWithValue("@idSalaried", idSalaried);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
Salaried.IdSalaried = dataReader.GetInt32(0);
Salaried.Crew = Modele.ORM.crew.GetCrew(dataReader.GetInt32(1));
Salaried.Name = dataReader.GetString(2);
Salaried.FirstName = dataReader.GetString(3);
Salaried.RegistrationNumber = dataReader.GetInt32(4);
Salaried.Availibility = dataReader.GetBoolean(5);
Salaried.DateBirth = dataReader.GetDateTime(6);
Salaried.DateHiring = dataReader.GetDateTime(7);
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
//return list to be displayed
return(Salaried);
}
else
{
return(Salaried);
}
}
public static ObservableCollection <Modele.ORM.type> getTypes()
{
ObservableCollection <Modele.ORM.type> Types = new ObservableCollection <Modele.ORM.type>();
string query = "SELECT * FROM type;";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
Modele.ORM.type Type;
//Read the data and store them in the list
while (dataReader.Read())
{
Type = new Modele.ORM.type(
dataReader.GetInt32(0),
dataReader.GetString(1),
dataReader.GetString(2),
dataReader.GetInt32(3),
dataReader.GetInt32(4),
dataReader.GetInt32(5),
dataReader.GetInt32(6),
dataReader.GetInt32(7)
);
Types.Add(Type);
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
//return list to be displayed
return(Types);
}
else
{
return(Types);
}
}
//Select statement
public static Modele.ORM.type getType(int idType)
{
Modele.ORM.type Type = null;
string query = "SELECT * FROM type where id=@type;";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//shield sql injection
cmd.Parameters.AddWithValue("@type", idType);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
Type = new Modele.ORM.type(
dataReader.GetInt32(0),
dataReader.GetString(1),
dataReader.GetString(2),
dataReader.GetInt32(3),
dataReader.GetInt32(4),
dataReader.GetInt32(5),
dataReader.GetInt32(6),
dataReader.GetInt32(7)
);
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
//return list to be displayed
return(Type);
}
else
{
return(Type);
}
}
//Select statement
public static Modele.ORM.warehouse getWarehouse(int idWarehouse)
{
Modele.ORM.warehouse Warehouse = null;
string query = "SELECT * FROM warehouse where id=@warehouse;";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//shield sql injection
cmd.Parameters.AddWithValue("@warehouse", idWarehouse);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
Warehouse = new Modele.ORM.warehouse(
dataReader.GetInt32(0),
Modele.ORM.airport.GetAirport(dataReader.GetInt32(1)),
dataReader.GetString(2),
dataReader.GetBoolean(3)
);
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
//return list to be displayed
return(Warehouse);
}
else
{
return(Warehouse);
}
}
//Select statement
public static ObservableCollection <Modele.ORM.warehouse> getWarehouses()
{
Modele.ORM.warehouse Warehouse = null;
ObservableCollection <Modele.ORM.warehouse> Warehouses = new ObservableCollection <Modele.ORM.warehouse>();
string query = "SELECT id,name FROM warehouse;";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
Warehouse = new Modele.ORM.warehouse(
dataReader.GetInt32(0),
dataReader.GetString(1)
);
Warehouses.Add(Warehouse);
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
//return list to be displayed
return(Warehouses);
}
else
{
return(Warehouses);
}
}
//Select statement
public static ObservableCollection <Modele.ORM.flight> getFlights()
{
ObservableCollection <Modele.ORM.flight> Flights = new ObservableCollection <Modele.ORM.flight>();
string query = "SELECT * FROM flight;";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
int idvol = dataReader.GetInt32(0);
Modele.ORM.plane Plane = Modele.ORM.plane.getPlane(dataReader.GetInt32(1));
int freePlaces = dataReader.GetInt32(2);
DateTime hoursDeparture = dataReader.GetDateTime(3);
DateTime hoursArrival = dataReader.GetDateTime(3);
Modele.ORM.flight Flight = new Modele.ORM.flight(idvol, Plane, freePlaces, hoursDeparture, hoursArrival);
Flights.Add(Flight);
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
//return list to be displayed
return(Flights);
}
else
{
return(Flights);
}
}
//Select statement
public static Modele.ORM.airport getAirport(int idAirport)
{
Modele.ORM.airport Airport = new Modele.ORM.airport();
string query = "SELECT * FROM airport where id=@airport;";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//shield sql injection
cmd.Parameters.AddWithValue("@airport", idAirport);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
Airport.IdAirport = dataReader.GetInt32(0);
Airport.City = Modele.ORM.city.GetCity(dataReader.GetInt32(1));
Airport.Name = dataReader.GetString(2);
Airport.CodeAITA = dataReader.GetString(3);
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
//return list to be displayed
return(Airport);
}
else
{
return(Airport);
}
}
//Select statement
public static Modele.ORM.city getCity(int idCity)
{
Modele.ORM.city City = new Modele.ORM.city();
string query = "SELECT * FROM city where id=@city;";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//shield sql injection
cmd.Parameters.AddWithValue("@city", idCity);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
City.IdCity = dataReader.GetInt32(0);
City.Name = dataReader.GetString(1);
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
//return list to be displayed
return(City);
}
else
{
return(City);
}
}
public static ObservableCollection <Modele.ORM.crew> getCrews()
{
ObservableCollection <Modele.ORM.crew> Crews = null;
Modele.ORM.crew Crew;
string query = "SELECT * FROM crew;";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
dataReader.GetInt32(0);
dataReader.GetInt32(1);
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
//return list to be displayed
return(Crews);
}
else
{
return(Crews);
}
}
//Select statement
public static int howMuchFlights(string date)
{
string query = "SELECT count(*) FROM flight WHERE @date BETWEEN Hours_Departure AND Hours_Arrival;";
int countFlightTD = 0;
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
cmd.Parameters.AddWithValue("@date", date);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
countFlightTD = dataReader.GetInt32(0);
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
//return list to be displayed
return(countFlightTD);
}
else
{
return(countFlightTD);
}
}
public static void deleteFlight(int idFlight)
{
string query = "DELETE FROM `flight` WHERE id=@idflight";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
cmd.Parameters.AddWithValue("@idflight", idFlight);
cmd.ExecuteNonQuery();
//close Connection
connection.CloseConnection();
}
}
public static Modele.ORM.client TryConnection(string coMail, string CoPassword)
{
Modele.ORM.client Client = null;
string query = "SELECT * FROM clients where Mail=@Mail and Password=@Password;";
//Open connection
ConnexionWorkBench connection = new ConnexionWorkBench();
if (connection.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
//shield injection
cmd.Parameters.AddWithValue("@Mail", coMail);
cmd.Parameters.AddWithValue("@Password", CoPassword);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
Client = new Modele.ORM.client(dataReader.GetInt32(0), dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetString(4), dataReader.GetString(5), dataReader.GetInt32(6), dataReader.GetString(7), dataReader.GetInt32(8));
}
//close Data Reader
dataReader.Close();
//close Connection
connection.CloseConnection();
}
return(Client);
}
