//This method asks the database for the journey that a certain user reserved.
public static List <Journey> GetReservedJourneyList(string userEmail)
{
CheckDataBaseConnection();
connection.Open();
NpgsqlCommand comm = new NpgsqlCommand("SELECT * FROM reserve as re, utilisateur as ut, trajet as tr WHERE ut.email = (@email) AND ut.email = re.email AND re.eid = tr.eid;", connection);
comm.Parameters.AddWithValue("email", userEmail);
NpgsqlDataReader result = comm.ExecuteReader();
List <Journey> output = new List <Journey>();
while (result.Read())
{
Journey j = new Journey((int)result["eid"], (string)result["adressedep"], (string)result["villedep"], (string)result["adressearr"], (string)result["villearr"], (string)result["hdep"], (string)result["harr"], (int)result["km"], (int)result["nbplaces"], (string)result["comment"], (bool)result["pets"], (bool)result["smoke"], (bool)result["music"], (bool)result["speak"]);
output.Add(j);
}
connection.Close();
return(output);
}
//This method put in the database a new journey, and link it in the table propose with the user proposing it.
public static void ProposeNewJourney(string userEmail, Journey j)
{
CheckDataBaseConnection();
connection.Open();
NpgsqlCommand pullData = new NpgsqlCommand("SELECT MAX(eid) FROM trajet;", connection);
NpgsqlDataReader result = pullData.ExecuteReader();
if (result.HasRows)
{
result.Read();
j.Eid = result.GetInt32(0) + 1;
}
else
{
j.Eid = 1;
}
connection.Close();
//Here we can see that the connection is checked and opened multiple times. It is done so to avoid two request being sent at once
CheckDataBaseConnection();
connection.Open();
NpgsqlCommand comm = new NpgsqlCommand("INSERT INTO trajet VALUES ((@eid), (@adDep), (@vilDep), (@adArr), (@vilArr), (@hdep), (@harr), (@km), (@pets), (@smoke), (@music), (@talk), (@comm), (@nbPl));", connection);
comm.Parameters.AddWithValue("eid", j.Eid); comm.Parameters.AddWithValue("km", j.Km);
comm.Parameters.AddWithValue("pets", j.Pets); comm.Parameters.AddWithValue("smoke", j.Smoke);
comm.Parameters.AddWithValue("comm", j.Comm); comm.Parameters.AddWithValue("adDep", j.AdressDep);
comm.Parameters.AddWithValue("vilDep", j.VilleDep); comm.Parameters.AddWithValue("harr", j.Harr);
comm.Parameters.AddWithValue("music", j.Music); comm.Parameters.AddWithValue("nbPl", j.NbPlaces);
comm.Parameters.AddWithValue("adArr", j.AdresseArr); comm.Parameters.AddWithValue("vilArr", j.VilleArr);
comm.Parameters.AddWithValue("hdep", j.Hdep); comm.Parameters.AddWithValue("talk", j.Talk);
comm.ExecuteReader();
connection.Close();
CheckDataBaseConnection();
connection.Open();
NpgsqlCommand comm2 = new NpgsqlCommand("INSERT INTO propose VALUES ((@email), (@eid));", connection);
comm2.Parameters.AddWithValue("email", userEmail); comm2.Parameters.AddWithValue("eid", j.Eid);
comm2.ExecuteReader();
connection.Close();
}