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