public static void updateIntervenant(long?idPersonnel, long idCours)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "UPDATE cours set personnel_id = @personnel_id WHERE id = @idCours";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@idCours", idCours);
_cmd.Parameters.AddWithValue("@personnel_id", idPersonnel);
_cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("Exception : " + e);
}
_cmd.Dispose();
}
public static Ratio update(Ratio obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "UPDATE ratio set ratio = @ratio WHERE type_id = @typeId AND categorie_id = @categorieId ";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@categorieId", obj.categoriePersonnel.id);
_cmd.Parameters.AddWithValue("@typeId", obj.typeCours.id);
_cmd.Parameters.AddWithValue("@ratio", obj.ratio);
_cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(obj);
}
public static void update(Cours Cours)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "UPDATE cours set ec_id = @ecId, volume = @volume, type_id = @idTypeCours, groupe = @groupe WHERE id = @idCours";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@idCours", Cours.id);
_cmd.Parameters.AddWithValue("@ecId", Cours.elementConstitutif.id);
_cmd.Parameters.AddWithValue("@volume", Cours.volumeHoraire);
_cmd.Parameters.AddWithValue("@idTypeCours", Cours.typeCours.id);
_cmd.Parameters.AddWithValue("@groupe", Cours.numeroGroupe);
_cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Debug.WriteLine("Exception : " + e);
}
_cmd.Dispose();
}
public static Ratio create(Ratio obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "INSERT INTO ratio (ratio, type_id, categorie_id) VALUES (@ratio ,@typeId, @categorieId) ";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@ratio", obj.ratio);
_cmd.Parameters.AddWithValue("@typeId", obj.typeCours.id);
_cmd.Parameters.AddWithValue("@categorieId", obj.categoriePersonnel.id);
_cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(obj);
}
public static Periode create(Periode obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "INSERT INTO periode (id, libelle, annee_id) VALUES (@id,@libelle,@annee) ";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", obj.id);
_cmd.Parameters.AddWithValue("@libelle", obj.libelle);
_cmd.Parameters.AddWithValue("@annee", obj.annee.id);
_cmd.ExecuteNonQuery();
obj.id = _cmd.LastInsertedId;
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(obj);
}
public static ElementConstitutif update(ElementConstitutif obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "UPDATE element_constitutif set libelle = @libelle, ue_id = @ue_id WHERE id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", obj.id);
_cmd.Parameters.AddWithValue("@libelle", obj.libelle);
_cmd.Parameters.AddWithValue("@ue_id", obj.uniteEnseignement.id);
_cmd.ExecuteNonQuery();
obj.id = _cmd.LastInsertedId;
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(obj);
}
public static Personnel update(Personnel obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "UPDATE personnel set nom = @nom, prenom = @prenom, categorie_id = @categId WHERE id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", obj.id);
_cmd.Parameters.AddWithValue("@nom", obj.nom);
_cmd.Parameters.AddWithValue("@prenom", obj.prenom);
_cmd.Parameters.AddWithValue("@categId", obj.categoriePersonnel.id);
_cmd.ExecuteNonQuery();
obj.id = _cmd.LastInsertedId;
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(obj);
}
public static CategoriePersonnel update(CategoriePersonnel obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "UPDATE categorie_personnel set libelle = @libelle, volume_horaire = @volume WHERE id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", obj.id);
_cmd.Parameters.AddWithValue("@libelle", obj.libelle);
_cmd.Parameters.AddWithValue("@volume", obj.volumeHoraire);
_cmd.ExecuteNonQuery();
obj.id = _cmd.LastInsertedId;
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(obj);
}
public static Personnel create(Personnel obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "INSERT INTO personnel (id, prenom, nom , categorie_id) VALUES (@id ,@prenom ,@nom, @categorieId) ";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", obj.id);
_cmd.Parameters.AddWithValue("@prenom", obj.prenom);
_cmd.Parameters.AddWithValue("@nom", obj.nom);
_cmd.Parameters.AddWithValue("@categorieId", obj.categoriePersonnel.id);
_cmd.ExecuteNonQuery();
obj.id = _cmd.LastInsertedId;
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(obj);
}
public static Periode update(Periode obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "UPDATE periode set libelle = @libelle, annee_id = @anneeId WHERE id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", obj.id);
_cmd.Parameters.AddWithValue("@libelle", obj.libelle);
_cmd.Parameters.AddWithValue("@anneeId", obj.annee.id);
_cmd.ExecuteNonQuery();
obj.id = _cmd.LastInsertedId;
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(obj);
}
public static void delete(Periode obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "DELETE FROM periode WHERE id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", obj.id);
_cmd.ExecuteNonQuery();
obj.id = _cmd.LastInsertedId;
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
}
public static void delete(Ratio obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "DELETE FROM ratio WHERE type_id = @typeId, categorie_id = @categorieId";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@categorieId", obj.categoriePersonnel.id);
_cmd.Parameters.AddWithValue("@typeId", obj.typeCours.id);
_cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
}
public static TypeCours update(TypeCours obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "UPDATE type_cours set libelle = @libelle WHERE id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", obj.id);
_cmd.Parameters.AddWithValue("@libelle", obj.libelle);
_cmd.ExecuteNonQuery();
obj.id = _cmd.LastInsertedId;
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return obj;
}
public static ElementConstitutif create(ElementConstitutif obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "INSERT INTO element_constitutif (id, libelle, ue_id) VALUES (@id,@libelle,@UniteEnseignement) ";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", obj.id);
_cmd.Parameters.AddWithValue("@libelle", obj.libelle);
_cmd.Parameters.AddWithValue("@UniteEnseignement", obj.uniteEnseignement.id);
_cmd.ExecuteNonQuery();
obj.id = _cmd.LastInsertedId;
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(obj);
}
/// <summary>
/// Retourne l'ensemble des diplomes
/// </summary>
/// <returns>diplomes</returns>
public static List <Diplome> findAll()
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
List <Diplome> resultats = new List <Diplome>();
try
{
sql = "SELECT diplome.id AS diplomeID, diplome.libelle AS diplomeLibelle "
+ "FROM diplome";
_cmd.CommandText = sql;
reader = _cmd.ExecuteReader();
while (reader.Read())
{
Diplome temp = populateDiplome(reader);
resultats.Add(temp);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultats);
}
public static List <ElementConstitutif> findByUniteEnseignement(long id)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
List <ElementConstitutif> resultats = new List <ElementConstitutif>();
try
{
sql = "SELECT element_constitutif.id as elemConstID, element_constitutif.libelle as elemConstLibelle, "
+ "unite_enseignement.id as uniteEnsID, unite_enseignement.libelle as uniteEnsLibelle, "
+ "periode.id AS periodeID, periode.libelle AS periodeLibelle, "
+ "annee.id AS anneeId, annee.libelle AS anneeLibelle, "
+ "diplome.id AS diplomeID, diplome.libelle AS diplomeLibelle "
+ "FROM element_constitutif "
+ "JOIN unite_enseignement on element_constitutif.ue_id = unite_enseignement.id "
+ "JOIN periode ON unite_enseignement.periode_id = periode.id "
+ "JOIN annee ON periode.annee_id = annee.id "
+ "JOIN diplome ON annee.diplome_id = diplome.id "
+ "WHERE unite_enseignement.id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", id);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
Diplome tempDiplome = DiplomeDAO.populateDiplome(reader);
Annee tempAnnee = AnneeDAO.populateAnnee(reader);
tempAnnee.diplome = tempDiplome;
Periode tempPeriode = PeriodeDAO.populatePeriode(reader);
tempPeriode.annee = tempAnnee;
UniteEnseignement tempUnite = UniteEnseignementDAO.populateUniteEnseignement(reader);
tempUnite.periode = tempPeriode;
ElementConstitutif tempElementConstitutif = populateElementConstitutif(reader);
tempElementConstitutif.uniteEnseignement = tempUnite;
resultats.Add(tempElementConstitutif);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultats);
}
public static List <UniteEnseignement> findByLibelle(String libelle)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
List <UniteEnseignement> resultats = new List <UniteEnseignement>();
try
{
sql = "SELECT unite_enseignement.id as uniteEnsID, unite_enseignement.libelle as uniteEnsLibelle, "
+ "periode.id AS periodeID, periode.libelle AS periodeLibelle, "
+ "annee.id AS anneeId, annee.libelle AS anneeLibelle, "
+ "diplome.id AS diplomeID, diplome.libelle AS diplomeLibelle "
+ "FROM unite_enseignement "
+ "JOIN periode ON unite_enseignement.periode_id = periode.id "
+ "JOIN annee ON periode.annee_id = annee.id "
+ "JOIN diplome ON annee.diplome_id = diplome.id "
+ "WHERE unite_enseignement.libelle LIKE @libelle";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@libelle", libelle);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
UniteEnseignement tempUnite = populateUniteEnseignement(reader);
Diplome tempDiplome = DiplomeDAO.populateDiplome(reader);
Annee tempAnnee = AnneeDAO.populateAnnee(reader);
tempAnnee.diplome = tempDiplome;
Periode tempPeriode = PeriodeDAO.populatePeriode(reader);
tempPeriode.annee = tempAnnee;
tempUnite.periode = tempPeriode;
resultats.Add(tempUnite);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultats);
}
public static List <Periode> findAll()
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
List <Periode> resultats = new List <Periode>();
try
{
sql = "SELECT periode.id as periodeID, periode.libelle as periodeLibelle, "
+ "annee.id as anneeId, annee.libelle as anneeLibelle ,"
+ "diplome.id as diplomeID, diplome.libelle as diplomeLibelle "
+ "FROM periode "
+ "join annee on periode.annee_id = annee.id "
+ "join diplome on annee.diplome_id = diplome.id";
_cmd.CommandText = sql;
reader = _cmd.ExecuteReader();
while (reader.Read())
{
Periode temp = populatePeriode(reader);
Diplome tempDiplome = DiplomeDAO.populateDiplome(reader);
Annee tempAnnee = AnneeDAO.populateAnnee(reader);
tempAnnee.diplome = tempDiplome;
temp.annee = tempAnnee;
resultats.Add(temp);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultats);
}
public static Periode find(long id)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
Periode resultat = new Periode();
try
{
sql = "SELECT periode.id AS periodeID, periode.libelle AS periodeLibelle, "
+ "annee.id AS anneeId, annee.libelle AS anneeLibelle, "
+ "diplome.id AS diplomeID, diplome.libelle AS diplomeLibelle "
+ "FROM periode "
+ "JOIN annee ON periode.annee_id = annee.id "
+ "JOIN diplome ON annee.diplome_id = diplome.id "
+ "WHERE periode.id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", id);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
resultat = populatePeriode(reader);
Diplome tempDiplome = DiplomeDAO.populateDiplome(reader);
Annee tempAnnee = AnneeDAO.populateAnnee(reader);
tempAnnee.diplome = tempDiplome;
resultat.annee = tempAnnee;
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultat);
}
public static Ratio find(long id)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
Ratio resultat = new Ratio();
try
{
sql = "SELECT ratio.ratio AS ratio, "
+ "type_cours.id AS typeCoursID, type_cours.libelle AS typeCoursLibelle "
+ "categorie_personnel.id AS categID, categorie_personnel.libelle AS categLibelle, categorie_personnel.volume_horaire as categVolume "
+ "FROM ratio "
+ "JOIN categorie_personnel ON ratio.categorie_id = categorie_personnel.id "
+ "JOIN type_cours ON ratio.type_id = type_cours.id " +
"WHERE type_id = @typeId, categorie_id = @categorieId";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", id);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
resultat = populateRatio(reader);
CategoriePersonnel d = CategoriePersonnelDAO.populateCategoriePersonnel(reader);
TypeCours t = TypeCoursDAO.populateTypeCours(reader);
resultat.categoriePersonnel = d;
resultat.typeCours = t;
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultat);
}
public static List <Ratio> findAll()
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
List <Ratio> resultats = new List <Ratio>();
try
{
sql = "SELECT ratio.ratio AS ratio, "
+ "type_cours.id AS typeCoursID, type_cours.libelle AS typeCoursLibelle, "
+ "categorie_personnel.id AS categID, categorie_personnel.libelle AS categLibelle, categorie_personnel.volume_horaire as categVolume "
+ "FROM ratio "
+ "JOIN categorie_personnel ON ratio.categorie_id = categorie_personnel.id "
+ "JOIN type_cours ON ratio.type_id = type_cours.id";
_cmd.CommandText = sql;
reader = _cmd.ExecuteReader();
while (reader.Read())
{
Ratio temp = populateRatio(reader);
CategoriePersonnel d = CategoriePersonnelDAO.populateCategoriePersonnel(reader);
TypeCours t = TypeCoursDAO.populateTypeCours(reader);
temp.categoriePersonnel = d;
temp.typeCours = t;
resultats.Add(temp);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultats);
}
public static List <Personnel> findByPrenom(String prenom)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
List <Personnel> resultats = new List <Personnel>();
try
{
sql = "SELECT personnel.id AS personnelId, personnel.nom AS personnelNom, personnel.prenom AS personnelPrenom, "
+ "categorie_personnel.id AS categID, categorie_personnel.libelle AS categLibelle, categorie_personnel.volume_horaire as categVolume "
+ "FROM personnel "
+ "JOIN categorie_personnel ON personnel.categorie_id = categorie_personnel.id "
+ "WHERE personnel.prenom LIKE @prenom";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@prenom", prenom);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
Personnel temp = populatePersonnel(reader);
CategoriePersonnel d = CategoriePersonnelDAO.populateCategoriePersonnel(reader);
temp.categoriePersonnel = d;
resultats.Add(temp);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultats);
}
public static Cours create(Cours obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "INSERT INTO cours (id, ec_id, volume,type_id,personnel_id,groupe) " +
"VALUES (@id,@ec_id,@volume,@type_id,@personnel_id,@groupe) ";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", obj.id);
_cmd.Parameters.AddWithValue("@ec_id", obj.elementConstitutif.id);
_cmd.Parameters.AddWithValue("@volume", obj.volumeHoraire);
_cmd.Parameters.AddWithValue("@type_id", obj.typeCours.id);
if (obj.intervenant != null)
{
_cmd.Parameters.AddWithValue("@personnel_id", obj.intervenant.id);
}
else
{
_cmd.Parameters.AddWithValue("@personnel_id", DBNull.Value);
}
_cmd.Parameters.AddWithValue("@groupe", obj.numeroGroupe);
_cmd.ExecuteNonQuery();
obj.id = _cmd.LastInsertedId;
}
catch (Exception e)
{
Debug.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(obj);
}
public static List <Annee> findByLibelle(String libelle)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
List <Annee> annees = new List <Annee>();
try
{
sql = "SELECT annee.id AS anneeId, annee.libelle AS anneeLibelle, "
+ "diplome.libelle AS diplomeLibelle, diplome.id AS diplomeId "
+ "FROM annee "
+ "JOIN diplome ON annee.diplome_id = diplome.id "
+ "AND annee.libelle LIKE @libelle";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@libelle", libelle);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
Annee temp = populateAnnee(reader);
Diplome d = DiplomeDAO.populateDiplome(reader);
temp.diplome = d;
annees.Add(temp);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(annees);
}
public static Personnel find(long id)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
Personnel resultat = new Personnel();
try
{
sql = "SELECT personnel.id AS personnelId, personnel.nom AS personnelNom, personnel.prenom AS personnelPrenom, "
+ "categorie_personnel.id AS categID, categorie_personnel.libelle AS categLibelle, categorie_personnel.volume_horaire as categVolume "
+ "FROM personnel "
+ "JOIN categorie_personnel ON personnel.categorie_id = categorie_personnel.id "
+ "WHERE personnel.id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", id);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
resultat = populatePersonnel(reader);
CategoriePersonnel d = CategoriePersonnelDAO.populateCategoriePersonnel(reader);
resultat.categoriePersonnel = d;
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultat);
}
public static Annee find(long id)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
Annee resultat = new Annee();
try
{
sql = "SELECT annee.id AS anneeId, annee.libelle AS anneeLibelle, "
+ "diplome.libelle AS diplomeLibelle, diplome.id AS diplomeId "
+ "FROM annee JOIN diplome ON annee.diplome_id = diplome.id "
+ "WHERE annee.id = @id";
// sql = "SELECT * FROM annee WHERE id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", id);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
resultat = populateAnnee(reader);
Diplome d = DiplomeDAO.populateDiplome(reader);
resultat.diplome = d;
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultat);
}
public static int getVolumeCoursByPersonnel(long idPersonnel)
{
int res = 0;
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
try
{
sql = "select sum(volume) as volume from cours where personnel_id = @idPersonnel";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@idPersonnel", idPersonnel);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
if (!reader.IsDBNull(reader.GetOrdinal("volume")))
{
res = Convert.ToInt32(reader["volume"]);
}
}
reader.Close();
}
catch (Exception e)
{
Debug.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(res);
}
public static List<TypeCours> findByLibelle(String libelle)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
List<TypeCours> resultats = new List<TypeCours>();
try
{
sql = "SELECT type_cours.id AS typeCoursID, type_cours.libelle AS typeCoursLibelle "
+ "FROM type_cours "
+ "WHERE libelle LIKE @libelle";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@libelle", libelle);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
TypeCours resultat = populateTypeCours(reader);
resultats.Add(resultat);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return resultats;
}
public static TypeCours find(long id)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
TypeCours resultat = new TypeCours();
try
{
sql = "SELECT type_cours.id AS typeCoursID, type_cours.libelle AS typeCoursLibelle "
+ "FROM type_cours "
+ "WHERE id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", id);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
resultat = populateTypeCours(reader);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return resultat;
}
public static List <Diplome> findBy(String attribute, String value)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
List <Diplome> dips = new List <Diplome>();
try
{
sql = "SELECT * FROM diplome WHERE @attribute LIKE @value";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@attribute", attribute);
_cmd.Parameters.AddWithValue("@value", value);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
Diplome resultat = new Diplome();
resultat.id = Convert.ToInt64(reader["id"]);
resultat.libelle = (String)reader["libelle"];
dips.Add(resultat);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(dips);
}
