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 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);
}
