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 void delete(CategoriePersonnel obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "DELETE FROM categorie_personnel WHERE id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", obj.id);
_cmd.ExecuteNonQuery();
obj.id = _cmd.LastInsertedId;
}
catch (Exception e)
{
Debug.WriteLine("Exception : " + e);
}
_cmd.Dispose();
}
/// <summary>
/// Evenement annuler
/// </summary>
private void valider(object sender, EventArgs e)
{
CategoriePersonnel categASupprimer = (CategoriePersonnel)this.categorieASupprimerCB.SelectedItem;
CategoriePersonnel categRemplacement = (CategoriePersonnel)this.categoriesCB.SelectedItem;
if (categASupprimer != null && categRemplacement != null)
{
List <Personnel> listePersonnel = PersonnelDAO.findAll();
foreach (Personnel p in listePersonnel)
{
if (p.categoriePersonnel.id == categASupprimer.id)
{
p.categoriePersonnel = categRemplacement;
PersonnelDAO.update(p);
}
}
CategoriePersonnelDAO.delete(categASupprimer);
this.Close();
}
else
{
// Initializes the variables to pass to the MessageBox.Show method.
string message = "Erreur lors de la saisie des données, il faut choisir une catégorie de personnel \n";
DiplomeView.afficherPopup(message);
}
}
/// <summary>
/// Evenement valider / modifier
/// </summary>
private void valider(object sender, EventArgs e)
{
CategoriePersonnel categorie = (CategoriePersonnel)categorieComboBox.SelectedItem;
Boolean nomIncorrect = string.IsNullOrWhiteSpace(nomBox.Text);
Boolean prenomIncorrect = string.IsNullOrWhiteSpace(prenomBox.Text);
Boolean categorieIncorrecte = categorie == null;
if (nomIncorrect || prenomIncorrect || categorieIncorrecte)
{
// Initializes the variables to pass to the MessageBox.Show method.
string message = "Erreur lors de la saisie des données \n";
message += nomIncorrect ? " le nom est vide" : "";
message += prenomIncorrect ? " le prenom est vide" : "";
message += categorieIncorrecte ? " la categorie est incorrecte" : "";
DiplomeView.afficherPopup(message);
}
else
{
Personnel p = new Personnel(this.nomBox.Text, this.prenomBox.Text, categorie);
if (input)
{
PersonnelDAO.create(p);
}
else
{
p.id = modifId;
PersonnelDAO.update(p);
}
this.Close();
}
}
public void TestFind()
{
// test du find simple
CategoriePersonnel resultat = creerCategoriePersonnel("TEST_categ");
CategoriePersonnel resultatFind = CategoriePersonnelDAO.find(resultat.id);
Assert.AreEqual("TEST_categ", resultatFind.libelle);
Assert.AreNotEqual(0, resultatFind.id);
}
/**
* Methodes pour aider aux tests
* **/
public static CategoriePersonnel creerCategoriePersonnel(String libelle)
{
CategoriePersonnel res = new CategoriePersonnel();
res.libelle = libelle;
res.volumeHoraire = 999;
CategoriePersonnel c = CategoriePersonnelDAO.create(res);
return(c);
}
public void TestCreationCategoriePersonnel()
{
// test la création d'une categorie et la recherche grace à son libelle
CategoriePersonnel resultat = creerCategoriePersonnel("TEST_categ");
Assert.AreNotEqual("", resultat.libelle);
Assert.AreEqual("TEST_categ", resultat.libelle);
Assert.AreEqual(999, resultat.volumeHoraire);
Assert.IsTrue(resultat.id > 0);
}
public static CategoriePersonnel populateCategoriePersonnel(MySqlDataReader reader)
{
CategoriePersonnel resultat = new CategoriePersonnel();
if (reader.IsDBNull(reader.GetOrdinal("categID")) || reader.IsDBNull(reader.GetOrdinal("categLibelle")) || reader.IsDBNull(reader.GetOrdinal("categVolume")))
{
return(null);
}
resultat.id = Convert.ToInt64(reader["categID"]);
resultat.libelle = (String)reader["categLibelle"];
resultat.volumeHoraire = Convert.ToInt32(reader["categVolume"]);
return(resultat);
}
/// <summary>
/// Evenement valider / modifier
/// </summary>
private void validerAction(object sender, EventArgs e)
{
categorie = (CategoriePersonnel)categorieCB.SelectedItem;
if (categorie != null)
{
categorie.volumeHoraire = Int32.Parse(volumeTextBox.Text);
CategoriePersonnelDAO.update(categorie);
}
if (ratioModif != null)
{
ratioModif.ratio = Convert.ToDouble(ratioTextBox.Text);
RatioDAO.update(ratioModif);
}
this.Close();
}
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);
}
private void categorieCB_SelectedIndexChanged(object sender, EventArgs e)
{
this.groupBox1.Enabled = true;
categorie = (CategoriePersonnel)categorieCB.SelectedItem;
List <TypeCours> tcs = TypeCoursDAO.findAll();
List <Ratio> listeRatio = RatioDAO.findAll();
this.typeCoursComboBox.Items.Clear();
this.volumeTextBox.Text = categorie.volumeHoraire.ToString();
foreach (Ratio ratio in listeRatio)
{
if (ratio.categoriePersonnel.id == categorie.id)
{
this.typeCoursComboBox.Items.Add(ratio.typeCours);
}
}
}
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 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 List <CategoriePersonnel> findByLibelle(String libelle)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
List <CategoriePersonnel> resultats = new List <CategoriePersonnel>();
try
{
sql = "SELECT categorie_personnel.id AS categID, categorie_personnel.libelle AS categLibelle, categorie_personnel.volume_horaire as categVolume "
+ "FROM categorie_personnel "
+ "WHERE libelle LIKE @libelle";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@libelle", libelle);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
CategoriePersonnel resultat = populateCategoriePersonnel(reader);
resultats.Add(resultat);
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultats);
}
/// <summary>
/// Evenement valider / modifier
/// </summary>
private void validerAction(object sender, EventArgs e)
{
CategoriePersonnel categorie = new CategoriePersonnel();
int volumeHoraire = 0;
Boolean categorieIncorrect = String.IsNullOrWhiteSpace(categorietextBox.Text);
Boolean volumeHoraireIncorrect = !Int32.TryParse(volumeTextBox.Text, out volumeHoraire);
if (categorieIncorrect || volumeHoraireIncorrect)
{
// Initializes the variables to pass to the MessageBox.Show method.
string message = "Erreur lors de la saisie des données \n";
message += volumeHoraireIncorrect ? " le volume horaire est incorrect" : "";
message += categorieIncorrect ? " le nom de la categorie est incorrect" : "";
DiplomeView.afficherPopup(message);
}
else
{
categorie.libelle = categorietextBox.Text;
categorie.volumeHoraire = volumeHoraire;
categorie = CategoriePersonnelDAO.create(categorie);
if (categorie != null)
{
foreach (TypeCours typeCours in TypeCoursDAO.findAll())
{
Ratio temp = new Ratio();
temp.ratio = 1;
temp.typeCours = typeCours;
temp.categoriePersonnel = categorie;
RatioDAO.create(temp);
}
this.Close();
}
}
}
public static List <Cours> findByPersonnel(long id)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
MySqlDataReader reader = null;
List <Cours> resultats = new List <Cours>();
try
{
sql = "SELECT cours.id as coursID, cours.volume as coursVolume, cours.groupe as coursGroupe, cours.ec_id, " +
"type_cours.id AS typeCoursID, type_cours.libelle AS typeCoursLibelle, " +
"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, " +
"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 cours " +
"LEFT JOIN type_cours on cours.type_id = type_cours.id " +
"LEFT JOIN element_constitutif on cours.ec_id = element_constitutif.id " +
"LEFT JOIN personnel on cours.personnel_id = personnel.id " +
"LEFT JOIN categorie_personnel ON personnel.categorie_id = categorie_personnel.id " +
"LEFT JOIN unite_enseignement on element_constitutif.ue_id = unite_enseignement.id " +
"LEFT JOIN periode ON unite_enseignement.periode_id = periode.id " +
"LEFT JOIN annee ON periode.annee_id = annee.id " +
"LEFT JOIN diplome ON annee.diplome_id = diplome.id " +
"WHERE personnel.id = @id";
_cmd.CommandText = sql;
_cmd.Parameters.AddWithValue("@id", id);
reader = _cmd.ExecuteReader();
while (reader.Read())
{
Cours resultat = populateCours(reader);
Diplome tempDiplome = DiplomeDAO.populateDiplome(reader);
Annee tempAnnee = AnneeDAO.populateAnnee(reader);
if (tempDiplome != null)
{
tempAnnee.diplome = tempDiplome;
}
Periode tempPeriode = PeriodeDAO.populatePeriode(reader);
if (tempPeriode != null)
{
tempPeriode.annee = tempAnnee;
}
UniteEnseignement tempUnite = UniteEnseignementDAO.populateUniteEnseignement(reader);
if (tempUnite != null)
{
tempUnite.periode = tempPeriode;
}
ElementConstitutif tempElemConstitutif = ElementConstitutifDAO.populateElementConstitutif(reader);
if (tempElemConstitutif != null)
{
tempElemConstitutif.uniteEnseignement = tempUnite;
}
// champ du cours
resultat = populateCours(reader);
// ajout element constitutif
resultat.elementConstitutif = tempElemConstitutif;
// ajout type de cours
TypeCours tempTypeCours = TypeCoursDAO.populateTypeCours(reader);
resultat.typeCours = tempTypeCours;
// ajout intervenant et sa categorie
CategoriePersonnel tempCategPersonnel = CategoriePersonnelDAO.populateCategoriePersonnel(reader);
Personnel tempPersonnel = PersonnelDAO.populatePersonnel(reader);
if (tempPersonnel != null)
{
tempPersonnel.categoriePersonnel = tempCategPersonnel;
}
resultat.intervenant = tempPersonnel;
resultats.Add(resultat);
}
reader.Close();
}
catch (Exception e)
{
Debug.WriteLine("Exception : " + e);
}
_cmd.Dispose();
return(resultats);
}
public static void supprimeCategoriePersonnel(CategoriePersonnel categoriePersonnel)
{
CategoriePersonnelDAO.delete(categoriePersonnel);
}
