//Statistique Cotisation
public string AVGCotisation(Clubs leClub)
{
string moyenne = "";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT AVG(adherents.Cotisation) as Moyenne FROM adherents INNER JOIN clubs ON adherents.id_clubs = clubs.id WHERE clubs.Nom =@Nom";
command.Parameters.AddWithValue("@Nom", leClub.getNom());
using (MySqlDataReader dataReader = command.ExecuteReader())
{
Clubs LeClub = new Clubs();
while (dataReader.Read())
{
LeClub.setCPT((int)dataReader["Moyenne"]);
}
}
connection.Close();
moyenne = leClub.getCPT().ToString();
return(moyenne);
}
}
/// <summary>
/// methode setClub ajouter un club dans la base dedonnées
/// </summary>
/// <param name="leClub">est un abjet de la classe Clubs</param>
public void setClub(Clubs leClub)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO clubs(Nom, LienSite, Adresse, Ville, CodePostal, Telephone, Email, id_type) VALUES (@Nom, @LienSite, @Adresse, @Ville, @CodePostal, @Telephone, @Email, (SELECT id FROM Type WHERE Libelle = @Type))";
command.Parameters.AddWithValue("@Nom", leClub.getNom());
command.Parameters.AddWithValue("@LienSite", leClub.getLienSite());
command.Parameters.AddWithValue("@Adresse", leClub.getAdresse());
command.Parameters.AddWithValue("@Ville", leClub.getVille());
command.Parameters.AddWithValue("@CodePostal", leClub.getCPT());
command.Parameters.AddWithValue("@Telephone", leClub.getTel());
command.Parameters.AddWithValue("@Email", leClub.getEMail());
command.Parameters.AddWithValue("@Type", leClub.getType().getLibelle());
command.ExecuteNonQuery();
connection.Close();
}
}
/// <summary>
/// La méthode modifie un club en fonction de l'objet Clubs en parametre.
/// </summary>
/// <param name="Clubs">Leclub a modifié </param>
/// <returns>un club</returns>
public void UPDATEClub(Clubs leClub)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "UPDATE clubs SET Nom=@Nom, LienSite=@LienSite, Adresse=@Adresse, Ville=@Ville, CodePostal=@CodePostal, Telephone=@Telephone, Email=@Email, id_type= ( SELECT id FROM type WHERE Libelle=@Type ) WHERE id=@id";
command.Parameters.AddWithValue("@Id", leClub.getId());
command.Parameters.AddWithValue("@Nom", leClub.getNom());
command.Parameters.AddWithValue("@LienSite", leClub.getLienSite());
command.Parameters.AddWithValue("@Adresse", leClub.getAdresse());
command.Parameters.AddWithValue("@Ville", leClub.getVille());
command.Parameters.AddWithValue("@CodePostal", leClub.getCPT());
command.Parameters.AddWithValue("@Telephone", leClub.getTel());
command.Parameters.AddWithValue("@Email", leClub.getEMail());
command.Parameters.AddWithValue("@Type", leClub.getType().getLibelle());
command.ExecuteNonQuery();
connection.Close();
}
}