/// <summary>
/// Méthode permettant la création d'un retard.
/// </summary>
/// <param name="retard">Eleve que l'on souhaite créer dans la base.</param>
public static void Create(Retard retard)
{
string query = "INSERT INTO Retard (id, titre, description, file, idEleve) VALUES (null, @titre, @description, @file, @idEleve)";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@titre", retard.titre);
cmd.Parameters.AddWithValue("@description", retard.description);
cmd.Parameters.AddWithValue("@file", retard.file);
cmd.Parameters.AddWithValue("idEleve", retard.eleve.id);
// close Connection
int result = cmd.ExecuteNonQuery();
connection.CloseConnection();
}
}
}
/// <summary>
/// Méthode permettant la mise a jour d'un éléve.
/// </summary>
/// <param name="eleve">Eleve que l'on souhaite mettre a jour.</param>
public static void Update(Eleve eleve)
{
string query = "Update Eleve set pseudo = @pseudo , mail = @mail , mdp = @mdp , IdClasse = @idClasse, Photo_Profile = @photo WHERE Id = @id";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@id", eleve.id);
cmd.Parameters.AddWithValue("@pseudo", eleve.pseudo);
cmd.Parameters.AddWithValue("@mail", eleve.mail);
cmd.Parameters.AddWithValue("@mdp", eleve.MDP);
cmd.Parameters.AddWithValue("@idClasse", eleve.idClasse.id);
cmd.Parameters.AddWithValue("@photo", eleve.photo_profile);
// close Connection
int result = cmd.ExecuteNonQuery();
connection.CloseConnection();
}
}
}
public static List <Vote> getVoteByEleveRetard(long idEleve, int idRetard)
{
List <Vote> votes = new List <Vote>();
string query2 = "SELECT * FROM vote where idRetard=@idRetard AND idEleve=@idEleve;";
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create a data reader and Execute the command
using (MySqlCommand cmd = new MySqlCommand(query2, connection.GetConnection()))
{
cmd.Parameters.AddWithValue("@idRetard", idRetard);
cmd.Parameters.AddWithValue("@idEleve", idEleve);
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
// Read the data and store them in the list
while (dataReader.Read())
{
votes.Add(new Vote(dataReader.GetInt32(0), dataReader.GetInt32(1), dataReader.GetInt32(2), dataReader.GetDateTime(3), dataReader.GetInt32(4)));
}
}
}
}
return(votes);
}
public static List <Vote> upVote(long idRetard, long idEleve)
{
List <Vote> votes = new List <Vote>();
string query = "INSERT INTO vote (idEleve,idRetard,valeur) VALUES (@idEleve, @idRetard,@value)";
string query2 = "SELECT * FROM vote where idRetard=@id;";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
cmd.Parameters.AddWithValue("@idRetard", idRetard);
cmd.Parameters.AddWithValue("@idEleve", idEleve);
cmd.Parameters.AddWithValue("@value", 1);
int result = cmd.ExecuteNonQuery();
// close Connection
}
// Create a data reader and Execute the command
using (MySqlCommand cmd = new MySqlCommand(query2, connection.GetConnection()))
{
cmd.Parameters.AddWithValue("@id", idRetard);
MySqlDataReader dataReader = cmd.ExecuteReader();
// Read the data and store them in the list
while (dataReader.Read())
{
Vote vote = new Vote();
vote.id = dataReader.GetInt32(0);
vote.idEleve = dataReader.GetInt32(1);
vote.idRetard = dataReader.GetInt32(2);
vote.dateVote = dataReader.GetDateTime(3);
vote.valeur = dataReader.GetInt32(4);
votes.Add(vote);
}
}
connection.CloseConnection();
}
return(votes);
}
public static List <Vote> getVoteByRetard(long idRetard)
{
List <Vote> votes = new List <Vote>();
string query = "SELECT * FROM vote where idRetard=@id;";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection());
// shield sql injection
cmd.Parameters.AddWithValue("@id", idRetard);
// Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
// get the new vote list
while (dataReader.Read())
{
Vote vote = new Vote();
vote.id = dataReader.GetInt32(0);
vote.idEleve = dataReader.GetInt32(1);
vote.idRetard = dataReader.GetInt32(2);
vote.dateVote = dataReader.GetDateTime(3);
vote.valeur = dataReader.GetInt32(4);
votes.Add(vote);
}
// close Data Reader
dataReader.Close();
// close Connection
connection.CloseConnection();
// return list to be displayed
}
return(votes);
}
public static ObservableCollection <Tags> GetTags()
{
ObservableCollection <Tags> tags = new ObservableCollection <Tags>();
string query = "SELECT * FROM `tags`";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// Create a data reader and Execute the command
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
// Read the data and store them in the list
while (dataReader.Read())
{
Tags tag = new Tags();
tag.Id = dataReader.GetInt64(0);
tag.Libelle = dataReader.GetString(1);
tags.Add(tag);
}
// close Data Reader
dataReader.Close();
}
// close Connection
connection.CloseConnection();
}
// return list to be displayed
}
return(tags);
}
/// <summary>
/// Supprime la classe passé en paramètre dans la base de donnée.
/// </summary>
/// <param name="id">L'id de la classe que l'on souhaite supprimer.</param>
public static void Delete(int id)
{
string query = "DELETE FROM Classe WHERE Id = @id";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@id", id);
// close Connection
int result = cmd.ExecuteNonQuery();
connection.CloseConnection();
}
}
}
/// <summary>
/// Méthode permettant de recuperer un retard.
/// </summary>
/// <param name="idRetard">Id du retard que l'on souhaite recuperer.</param>
/// <returns>Retourne un retard.</returns>
public static List <Retard> getRetardByEleve(int idEleve)
{
List <Retard> retards = new List <Retard>();
string query = "SELECT * FROM retard where idEleve=@idEleve;";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@idEleve", idEleve);
// Create a data reader and Execute the command
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
// Read the data and store them in the list
while (dataReader.Read())
{
Retard retard = new Retard(dataReader.GetInt32(0), dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetInt32(4));
retards.Add(retard);
}
// close Data Reader
dataReader.Close();
}
// close Connection
connection.CloseConnection();
}
// return list to be displayed
}
return(retards);
}
/// <summary>
/// Méthode permettant la création d'un tag.
/// </summary>
/// <param name="tags">tag que l'on souhaite créer dans la base.</param>
public static void Create(Tags tags)
{
string query = "INSERT INTO tags (id, libelle) VALUES (null, @libelle)";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@libelle", tags.Libelle);
// close Connection
int result = cmd.ExecuteNonQuery();
connection.CloseConnection();
}
}
}
/// <summary>
/// Méthode permettant la récupération d'un éleve a l'aide de son pseudo ainsi que de son mdp.
/// </summary>
/// <param name="pseudo">Pseudo de l'éleve</param>
/// <param name="mdp">Mot de passe de l'éléve</param>
/// <returns>Retourne un éléve</returns>
public static Eleve GetEleveByPseudoAndMDP(string pseudo, string mdp)
{
Eleve eleve = null;
string query = "SELECT * FROM Eleve where pseudo=@pseudo and mdp=@mdp;";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@pseudo", pseudo);
cmd.Parameters.AddWithValue("@mdp", mdp);
// Create a data reader and Execute the command
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
// Read the data and store them in the list
while (dataReader.Read())
{
eleve = new Eleve(dataReader.GetInt32(0), dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetInt64(4), dataReader.GetString(5));
}
// close Data Reader
dataReader.Close();
}
// close Connection
connection.CloseConnection();
// return list to be displayed
}
}
return(eleve);
}
/// <summary>
/// Méthode permettant de recuperer un retard.
/// </summary>
/// <param name="idRetard">Id du retard que l'on souhaite recuperer.</param>
/// <returns>Retourne un retard.</returns>
public static Retard getLastRetard(Retard retardrecup)
{
Retard retard = null;
string query = "SELECT * FROM `retard` WHERE titre = @titre and description = @description order BY titre DESC LIMIT 1 ;";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@titre", retardrecup.titre);
cmd.Parameters.AddWithValue("@description", retardrecup.description);
// Create a data reader and Execute the command
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
// Read the data and store them in the list
while (dataReader.Read())
{
retard = new Retard(dataReader.GetInt32(0), dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetInt32(4));
}
// close Data Reader
dataReader.Close();
}
// close Connection
connection.CloseConnection();
}
// return list to be displayed
}
return(retard);
}
/// <summary>
/// Méthode permettant la création d'une classe.
/// </summary>
/// <param name="classe">Classe que l'on souhaite créer dans la base.</param>
public static void Create(Retards_Tags tagRetard)
{
string query = "INSERT INTO tags_retard (id, idRetard, idTags) VALUES (null, @idRetard, @idTags)";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@idRetard", tagRetard.Id_Retard);
cmd.Parameters.AddWithValue("@idTags", tagRetard.Id_Tags);
// close Connection
int result = cmd.ExecuteNonQuery();
connection.CloseConnection();
}
}
}
/// <summary>
/// Méthode permettant la création d'une classe.
/// </summary>
/// <param name="classe">Classe que l'on souhaite créer dans la base.</param>
public static void Create(Classe classe)
{
string query = "INSERT INTO Eleve (Id, Libelle, promo) VALUES (null, @libelle, @promo)";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@promo", classe.libelle);
cmd.Parameters.AddWithValue("@mail", classe.promo);
// close Connection
int result = cmd.ExecuteNonQuery();
connection.CloseConnection();
}
}
}
/// <summary>
/// Méthode permettant de recuperer un retard.
/// </summary>
/// <param name="idRetard">Id du retard que l'on souhaite recuperer.</param>
/// <returns>Retourne un retard.</returns>
public static List <TopXRetard> GetTopX(int idEleve, int limite)
{
List <TopXRetard> liste = new List <TopXRetard>();
string query = "SELECT *,((select count(valeur) from vote where idRetard=retardP.id AND valeur=1 )-(select count(valeur) as 'positive value' from vote where idRetard = retardP.id AND valeur = -1 )) as 'FinalValue' FROM retard retardP order by((select count(valeur) from vote where idRetard= retardP.id AND valeur = 1)-(select count(valeur) as 'positive value' from vote where idRetard = retardP.id AND valeur = -1 )) desc LIMIT @limite";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
cmd.Parameters.AddWithValue("@limite", limite);
// Create a data reader and Execute the command
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
// Read the data and store them in the list
while (dataReader.Read())
{
TopXRetard retard = new TopXRetard(dataReader.GetInt32(0), dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetInt32(4), idEleve, dataReader.GetInt32(5));
liste.Add(retard);
}
// close Data Reader
dataReader.Close();
}
// close Connection
connection.CloseConnection();
}
// return list to be displayed
}
return(liste);
}
/// <summary>
/// Méthode permettant la mise a jour d'une classe.
/// </summary>
/// <param name="classe">Classe que l'on souhaite mettre a jour.</param>
public static void Update(Classe classe)
{
string query = "Update Classe set Libelle = @libelle , promo = @promo WHERE Id = @id";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@id", classe.id);
cmd.Parameters.AddWithValue("@promo", classe.libelle);
cmd.Parameters.AddWithValue("@mail", classe.promo);
// close Connection
int result = cmd.ExecuteNonQuery();
connection.CloseConnection();
}
}
}
/// <summary>
/// Recupere toutes les classes présente dans la base de données.
/// </summary>
/// <returns>Retourne une liste de classe.</returns>
public static List <Classe> GetAllClasses()
{
List <Classe> Listclasse = new List <Classe>();
string query = "SELECT * FROM Classe;";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
// Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
// Read the data and store them in the list
while (dataReader.Read())
{
Classe classe = new Classe();
classe.id = dataReader.GetInt32(0);
classe.libelle = dataReader.GetString(1);
classe.promo = dataReader.GetDateTime(2);
Listclasse.Add(classe);
}
// close Data Reader
dataReader.Close();
// close Connection
connection.CloseConnection();
}
}
return(Listclasse);
}
/// <summary>
/// Recupere une classe en fonction de l'id passé en parametre.
/// </summary>
/// <param name="Id">l'id de la classe que l'on souhaite recuperer.</param>
/// <returns>Retourne une classe.</returns>
public static Classe GetClasse(long Id)
{
Classe classe = new Classe();
string query = "SELECT * FROM Classe where Id=@id;";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@id", Id);
// Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
// Read the data and store them in the list
while (dataReader.Read())
{
classe.id = dataReader.GetInt32(0);
classe.libelle = dataReader.GetString(1);
classe.promo = dataReader.GetDateTime(2);
}
// close Data Reader
dataReader.Close();
// close Connection
connection.CloseConnection();
}
}
return(classe);
}
/// <summary>
/// Méthode permettant la récupération d'un éleve a l'aide de son pseudo ainsi que de son mdp.
/// </summary>
/// <param name="pseudo">Pseudo de l'éleve</param>
/// <param name="mdp">Mot de passe de l'éléve</param>
/// <returns>Retourne un éléve</returns>
public static ObservableCollection <Eleve> GetEleves()
{
ObservableCollection <Eleve> eleves = new ObservableCollection <Eleve>();
string query = "SELECT * FROM Eleve ";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// Create a data reader and Execute the command
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
// Read the data and store them in the list
while (dataReader.Read())
{
Eleve eleve = new Eleve(dataReader.GetInt32(0), dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetInt64(4), dataReader.GetString(5));
eleves.Add(eleve);
}
// close Data Reader
dataReader.Close();
}
// close Connection
connection.CloseConnection();
// return list to be displayed
}
}
return(eleves);
}
/// <summary>
/// Méthode permettant la mise a jour d'un retard.
/// </summary>
/// <param name="retard">Retard que l'on souhaite mettre a jour.</param>
public static void Update(Retard retard)
{
string query = "Update retard set titre = @titre , description = @description , file = @file WHERE id = @id";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@id", retard.id);
cmd.Parameters.AddWithValue("@titre", retard.titre);
cmd.Parameters.AddWithValue("@description", retard.description);
cmd.Parameters.AddWithValue("@file", retard.file);
// close Connection
int result = cmd.ExecuteNonQuery();
connection.CloseConnection();
}
}
}
/// <summary>
/// Méthode permettant la création d'un éleve.
/// </summary>
/// <param name="eleve">Eleve que l'on souhaite créer dans la base.</param>
public static void Create(Eleve eleve)
{
string query = "INSERT INTO eleve (Id, pseudo, mail, mdp, IdClasse, Photo_Profile) VALUES (null, @pseudo, @mail, @mdp, @idClasse, @photo)";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// shield sql injection
cmd.Parameters.AddWithValue("@pseudo", eleve.pseudo);
cmd.Parameters.AddWithValue("@mail", eleve.mail);
cmd.Parameters.AddWithValue("@mdp", eleve.MDP);
cmd.Parameters.AddWithValue("@idClasse", eleve.idClasse.id);
cmd.Parameters.AddWithValue("@photo", eleve.photo_profile);
// close Connection
int result = cmd.ExecuteNonQuery();
connection.CloseConnection();
}
}
}
/// <summary>
/// Recupere tout les retards présent dans la base de donnée.
/// </summary>
/// <returns>Retourne une liste de retard.</returns>
public static ObservableCollection <Retard> GetRetards(int idUserConnecte, List <int> tags)
{
if (tags != null)
{
ObservableCollection <Retard> retards = new ObservableCollection <Retard>();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT DISTINCT re.id,re.titre,re.description,re.file, re.idEleve FROM retard re JOIN tags_retard tg_re on re.id=tg_re.idRetard WHERE tg_re.idTags in (");
foreach (var element in tags)
{
sb.Append(Convert.ToString(element));
sb.Append(",");
}
sb = sb.Remove(sb.Length - 1, 1);
sb.Append(")");
string query = Convert.ToString(sb);
//string query = "SELECT DISTINCT re.id,re.titre,re.description,re.file, re.idEleve FROM retard re JOIN tags_retard tg_re on re.id=tg_re.idRetard WHERE tg_re.idTags in (@params)";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
//cmd.Parameters.AddWithValue("@params", Convert.ToString(parametres));
// Create a data reader and Execute the command
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
// Read the data and store them in the list
while (dataReader.Read())
{
Retard retard = new Retard(dataReader.GetInt32(0), dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetInt32(4), idUserConnecte);
retards.Add(retard);
}
// close Data Reader
dataReader.Close();
}
// close Connection
connection.CloseConnection();
}
// return list to be displayed
}
return(retards);
}
else
{
ObservableCollection <Retard> retards = new ObservableCollection <Retard>();
string query = "SELECT * from retard order by id desc";
// Open connection
databaseConnexion connection = new databaseConnexion();
if (connection.OpenConnection() == true)
{
// Create Command
using (MySqlCommand cmd = new MySqlCommand(query, connection.GetConnection()))
{
// Create a data reader and Execute the command
using (MySqlDataReader dataReader = cmd.ExecuteReader())
{
// Read the data and store them in the list
while (dataReader.Read())
{
Retard retard = new Retard(dataReader.GetInt32(0), dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetInt32(4), idUserConnecte);
retards.Add(retard);
}
// close Data Reader
dataReader.Close();
}
// close Connection
connection.CloseConnection();
}
// return list to be displayed
}
return(retards);
}
}
