public static void delete(TypeCours obj)
{
MySqlConnection _connection = ConnectionMySql.getInstance();
MySqlCommand _cmd = new MySqlCommand();
_cmd.Connection = _connection;
String sql = "";
try
{
sql = "DELETE FROM type_cours 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 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 override InfosAssignation find(string nom)
{
InfosAssignation IA = null;
using (SqlCommand command_f = new SqlCommand("SELECT id, nom, id_ec, id_typecours, id_enseignant, nb_heures FROM infos_assignation WHERE nom='" + nom + "';", Connexion.getInstance()))
{
using (SqlDataReader reader_f = command_f.ExecuteReader())
{
if (reader_f.HasRows)
{
while (reader_f.Read())
{
AbstractDAOFactory factoSQL = AbstractDAOFactory.getFactory(types.SQL_FACTORY);
DAO <EC> TPSQLEC = factoSQL.getECDAO();
DAO <TypeCours> TPSQLTP = factoSQL.getTypeCoursDao();
DAO <Enseignant> TPSQLEns = factoSQL.getEnseignantDAO();
EC ec = TPSQLEC.find(reader_f.GetInt32(2));
TypeCours tp = TPSQLTP.find(reader_f.GetInt32(3));
Enseignant ens = TPSQLEns.find(reader_f.GetInt32(4));
IA = new InfosAssignation(reader_f.GetInt32(0), reader_f.GetString(1), ec, tp, ens, reader_f.GetDouble(5));
}
}
reader_f.Close();
}
// Connexion.getInstance().Close();
return(IA);
}
}
//Delete
private void button1_Click_1(object sender, EventArgs e)
{
TypeCours type = (TypeCours)listBox1.Items[listBox1.SelectedIndex];
List <Cours> cours = CoursDAO.findAllCours();
bool doDelete = true;
foreach (Cours c in cours)
{
if (c.typeCours.id == type.id)
{
MessageBox.Show("Au moins un cours est de ce type, veuillez le modifier avant la suppression",
"Note",
MessageBoxButtons.OK,
MessageBoxIcon.Exclamation,
MessageBoxDefaultButton.Button1);
doDelete = false;
break;
}
}
if (doDelete)
{
TypeCoursDAO.delete(type);
upadateList();
}
}
public override List <InfosAssignation> findAll()
{
List <InfosAssignation> IAs = new List <InfosAssignation>();
using (SqlCommand command_f = new SqlCommand("SELECT * FROM infos_assignation;", Connexion.getInstance()))
{
using (SqlDataReader reader_f = command_f.ExecuteReader())
{
if (reader_f.HasRows)
{
while (reader_f.Read())
{
AbstractDAOFactory factoSQL = AbstractDAOFactory.getFactory(types.SQL_FACTORY);
DAO <EC> TPSQLEC = factoSQL.getECDAO();
DAO <TypeCours> TPSQLTP = factoSQL.getTypeCoursDao();
DAO <Enseignant> TPSQLEns = factoSQL.getEnseignantDAO();
EC ec = TPSQLEC.find(reader_f.GetInt32(2));
TypeCours tp = reader_f.IsDBNull(3) ? default(TypeCours) : TPSQLTP.find(reader_f.GetInt32(3));
Enseignant ens = reader_f.IsDBNull(4) ? default(Enseignant) : TPSQLEns.find(reader_f.GetInt32(4));
IAs.Add(new InfosAssignation(reader_f.GetInt32(0), reader_f.GetString(1), ec, tp, ens, reader_f.GetDouble(5)));
}
}
}
}
return(IAs);
}
public override List <EquivalentTD> findAll()
{
List <EquivalentTD> eqtds = new List <EquivalentTD>();
using (SqlCommand command_f = new SqlCommand("SELECT * FROM equivalent_td;", Connexion.getInstance()))
{
using (SqlDataReader reader_f = command_f.ExecuteReader())
{
if (reader_f.HasRows)
{
while (reader_f.Read())
{
AbstractDAOFactory factoSQL = AbstractDAOFactory.getFactory(types.SQL_FACTORY);
DAO <Categorie> TPSQL = factoSQL.getCategorieDAO();
DAO <TypeCours> TPSQL2 = factoSQL.getTypeCoursDao();
Categorie categ = TPSQL.find(reader_f.GetInt32(1));
TypeCours tp = reader_f.IsDBNull(2) ? default(TypeCours) : TPSQL2.find(reader_f.GetInt32(2));
eqtds.Add(new EquivalentTD(reader_f.GetInt32(0), categ, tp, reader_f.GetDouble(3)));
}
}
}
}
return(eqtds);
}
private void typeCours_SelectedIndexChanged(object sender, EventArgs e)
{
typeCours = (TypeCours)typeCoursComboBox.SelectedItem;
List <Ratio> listeRatio = RatioDAO.findAll();
ratioModif = listeRatio.Find(x => x.categoriePersonnel.id == categorie.id && x.typeCours.id == typeCours.id);
this.ratioTextBox.Text = ratioModif.ratio.ToString();
}
public void TestFind()
{
// test du find simple
TypeCours resultatFind = TypeCoursDAO.find(1);
Assert.AreEqual("CM", resultatFind.libelle);
Assert.AreNotEqual(0, resultatFind.id);
}
public void TestCreationTypeCours()
{
// test la création d'un type de cours et la recherche grace à son libelle
TypeCours resultat = creerTypeCours("TEST_type_cours");
Assert.AreNotEqual("", resultat.libelle);
Assert.AreEqual("TEST_type_cours", resultat.libelle);
Assert.IsTrue(resultat.id > 0);
}
/**
* Methodes pour aider aux tests
* **/
public static TypeCours creerTypeCours(String libelle)
{
TypeCours res = new TypeCours();
res.libelle = libelle;
TypeCours c = TypeCoursDAO.create(res);
return(c);
}
public static TypeCours populateTypeCours(MySqlDataReader reader)
{
TypeCours resultat = new TypeCours();
if (reader.IsDBNull(reader.GetOrdinal("typeCoursID")) || reader.IsDBNull(reader.GetOrdinal("typeCoursLibelle")))
{
return null;
}
resultat.id = Convert.ToInt64(reader["typeCoursID"]);
resultat.libelle = (String)reader["typeCoursLibelle"];
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);
}
/// <summary>
/// Evenement valider / modifier
/// </summary>
private void valider(object sender, EventArgs e)
{
TypeCours typeCours = (TypeCours)this.typeBox.SelectedItem;
if (input)
{
CoursDAO.updateIntervenant(personnelId, ((Cours)this.coursBox.SelectedItem).id);
}
else
{
modifCours.volumeHoraire = Int32.Parse(this.heureBox.Text);
modifCours.numeroGroupe = this.groupBox.Text;
modifCours.typeCours.id = typeCours.id;
CoursDAO.update(modifCours);
}
this.Close();
}
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);
}
//Ajout
private void button1_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(textBox1.Text))
{
DiplomeView.afficherPopup("Le nom est vide");
}
else
{
TypeCours type = new TypeCours(textBox1.Text);
TypeCoursDAO.create(type);
upadateList();
List <CategoriePersonnel> catList = CategoriePersonnelDAO.findAll();
foreach (CategoriePersonnel cat in catList)
{
Ratio r = new Ratio(type, cat);
r.ratio = 1;
RatioDAO.create(r);
}
}
}
private void ComboBoxTypeCours_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
ComboBox comboBox = sender as ComboBox;
TypeCours typeCoursSelectionne = (TypeCours)comboBox.SelectedItem;
if (typeCoursSelectionne.Id == -1)
{
typeCoursSelectionne.Nom = "Nouveau type de cours";
TypeCours.create(typeCoursSelectionne);
infosAssignationSelect.TypeCours = typeCoursSelectionne;
InfosAssignation.update(infosAssignationSelect.Id, infosAssignationSelect);
tCs.Add(new TypeCours {
Nom = "Créer un type de cours...", Groupes = 1
});
}
else
{
infosAssignationSelect.TypeCours = typeCoursSelectionne;
InfosAssignation.update(infosAssignationSelect.Id, infosAssignationSelect);
}
}
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 override EquivalentTD find(int id)
{
EquivalentTD eqtd = null;
using (SqlCommand command_f = new SqlCommand("SELECT id, id_categorie_enseignant, id_type_cours, ratio_cours_td FROM equivalent_td WHERE id=" + id + ";", Connexion.getInstance()))
{
using (SqlDataReader reader_f = command_f.ExecuteReader())
{
if (reader_f.HasRows)
{
while (reader_f.Read())
{
AbstractDAOFactory factoSQL = AbstractDAOFactory.getFactory(types.SQL_FACTORY);
DAO <Categorie> TPSQL = factoSQL.getCategorieDAO();
DAO <TypeCours> TPSQL2 = factoSQL.getTypeCoursDao();
Categorie categ = TPSQL.find(reader_f.GetInt32(1));
TypeCours tp = TPSQL2.find(reader_f.GetInt32(2));
eqtd = new EquivalentTD(reader_f.GetInt32(0), categ, tp, reader_f.GetDouble(3));
reader_f.NextResult();
}
}
else
{
throw new Exception("Aucun objet avec cet id n'a été trouvé.");
}
reader_f.Close();
}
}
//Connexion.getInstance().Close();
return(eqtd);
}
public override InfosAssignation find(int id)
{
InfosAssignation IA = null;
using (SqlCommand command_f = new SqlCommand("SELECT id, nom, id_ec, id_typecours, id_enseignant, nb_heures FROM infos_assignation WHERE id=" + id + ";", Connexion.getInstance()))
{
using (SqlDataReader reader_f = command_f.ExecuteReader())
{
if (reader_f.HasRows)
{
while (reader_f.Read())
{
AbstractDAOFactory factoSQL = AbstractDAOFactory.getFactory(types.SQL_FACTORY);
DAO <EC> TPSQLEC = factoSQL.getECDAO();
DAO <TypeCours> TPSQLTP = factoSQL.getTypeCoursDao();
DAO <Enseignant> TPSQLEns = factoSQL.getEnseignantDAO();
EC ec = TPSQLEC.find(reader_f.GetInt32(2));
TypeCours tp = TPSQLTP.find(reader_f.GetInt32(3));
Enseignant ens = TPSQLEns.find(reader_f.GetInt32(4));
IA = new InfosAssignation(reader_f.GetInt32(0), reader_f.GetString(1), ec, tp, ens, reader_f.GetDouble(5));
reader_f.NextResult();
}
}
else
{
throw new Exception("Aucun objet avec cet id n'a été trouvé.");
}
reader_f.Close();
}
}
return(IA);
}
private void ComboBoxTypeCours_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
ComboBox comboBox = sender as ComboBox;
typeCoursSelectionne = (TypeCours)comboBox.SelectedItem;
if (typeCoursSelectionne.Id == -1)
{
typeCoursSelectionne.Nom = "Nouveau type de cours";
typeCours.create(typeCoursSelectionne);
equivalentTDSelectionne.TypeCours = typeCoursSelectionne;
equivalentTD.update(equivalentTDSelectionne.Id, equivalentTDSelectionne);
tCs.Add(new TypeCours {
Nom = "Créer un type de cours...", Groupes = 1
});
}
else
{
equivalentTDSelectionne.TypeCours = typeCoursSelectionne;
equivalentTD.update(equivalentTDSelectionne.Id, equivalentTDSelectionne);
}
service.Heures = GetHeures();
service.Information = GetInformation(service.Heures);
}
public static void supprimeTypeCours(TypeCours typeCours)
{
TypeCoursDAO.delete(typeCours);
}
private void ComboBoxTypeCours_TextSubmitted(ComboBox sender, ComboBoxTextSubmittedEventArgs args)
{
infosAssignationSelect.TypeCours.Nom = args.Text;
TypeCours.update(infosAssignationSelect.TypeCours.Id, infosAssignationSelect.TypeCours);
}
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);
}
/// <summary>
/// Evenement valider / modifier
/// </summary>
private void valider(object sender, EventArgs e)
{
Personnel intervenant = (Personnel)intervenantBox.SelectedItem;
TypeCours typeCours = (TypeCours)typeCoursBox.SelectedItem;
int volumeHoraire;
Boolean typeCoursIncorrect = typeCours == null;
Boolean volumeHoraireIncorrect = !Int32.TryParse(volumeBox.Text, out volumeHoraire);
if (typeCoursIncorrect || 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 += typeCoursIncorrect ? " le type de cours est incorrect" : "";
DiplomeView.afficherPopup(message);
}
else
{
if (input)
{
int nbGroupe = 0;
if (Int32.TryParse(groupeBox.Text, out nbGroupe))
{
List <Cours> cours = new List <Cours>();
for (int i = 0; i < nbGroupe; i++)
{
cours.Add(new Cours(elementConstitutif, intervenant, typeCours, typeCours.ToString() + (i + 1).ToString(), System.Convert.ToInt32(volumeBox.Text)));
}
foreach (Cours c in cours)
{
CoursDAO.create(c);
}
}
else
{
DiplomeView.afficherPopup("Nombre de groupe incorrect, un int est attendu");
}
}
else
{
if (String.IsNullOrWhiteSpace(groupeBox.Text))
{
DiplomeView.afficherPopup("Nom du groupe incorrect");
}
else
{
Cours cours = new Cours(elementConstitutif, intervenant, typeCours, groupeBox.Text, System.Convert.ToInt32(volumeBox.Text));
cours.id = coursModifie.id;
if (cours.intervenant != null)
{
CoursDAO.updateIntervenant(cours.intervenant.id, cours.id);
}
CoursDAO.update(cours);
this.Close();
}
}
this.Close();
}
}
private void TypeCours_ItemClick(object sender, ItemClickEventArgs e)
{
typeCoursSelect = (TypeCours)e.ClickedItem;
}
