public static List<Formation> listeFormations()
{
SqlConnection connexion = ConnexionSQL.CreationConnexion();
SqlCommand cmd = new SqlCommand(SELECT_FORMATIONS, connexion);
List<Formation> listeFormations = new List<Formation>();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Formation f = new Formation(reader.GetString(reader.GetOrdinal("CodeFormation")),reader.GetString(reader.GetOrdinal("LibelleCourt")));
listeFormations.Add(f);
}
return listeFormations;
}
public static List<Cours> listeCours(Formation pF)
{
SqlConnection connexion = ConnexionSQL.CreationConnexion();
SqlCommand cmd = new SqlCommand(SELECT_COURS, connexion);
List<Cours> listeCours = new List<Cours>();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Cours c = new Cours();
c.IdCours = reader.GetInt32(reader.GetOrdinal("IdCours"));
c.LibelleCours = reader.GetString(reader.GetOrdinal("LibelleCours"));
listeCours.Add(c);
}
connexion.Close();
return listeCours;
}
public static List<Stagiaire> getListeStagiaires(Formation pFormation, int pTypeFormation, String pFiltreNomPrenom)
{
String requete = "SELECT Stagiaire.CodeStagiaire FROM Stagiaire, PlanningIndividuelFormation ";
requete += " WHERE Stagiaire.CodeStagiaire=PlanningIndividuelFormation.CodeStagiaire ";
//filtre formation
if (pFormation != null && (pFormation.Code!="0" && pFormation.Libelle!="Toutes"))
{
requete += " AND PlanningIndividuelFormation.CodeFormation=@CodeFormation ";
}
//filtre type formation
if (pTypeFormation != 0)
{
if (pTypeFormation==Ressources.CONSTANTES.TYPE_FORMATION_CP)
{
requete += " AND PlanningIndividuelFormation.CodePromotion is null ";
}
else if (pTypeFormation==Ressources.CONSTANTES.TYPE_FORMATION_FC)
{
requete += " AND PlanningIndividuelFormation.CodePromotion is not null ";
}
}
//filtre nom/prenom
if (pFiltreNomPrenom != "")
{
requete += " AND (Stagiaire.Nom like ('%" + pFiltreNomPrenom.Trim() + "%') OR Stagiaire.Prenom like ('%" + pFiltreNomPrenom.Trim() + "%')) ";
}
requete += " ORDER BY Stagiaire.Nom, Stagiaire.Prenom";
SqlConnection connexion = ConnexionSQL.CreationConnexion();
SqlCommand cmd = new SqlCommand(requete, connexion);
if (pFormation != null) cmd.Parameters.AddWithValue("@CodeFormation", pFormation.Code.Trim());
SqlDataReader reader = cmd.ExecuteReader();
List<Stagiaire> listeStagiaires = new List<Stagiaire>();
while (reader.Read())
{
Stagiaire s = new Stagiaire();
s._id = reader.GetInt32(reader.GetOrdinal("CodeStagiaire"));
s = getStagiaire(s._id);
listeStagiaires.Add(s);
}
return listeStagiaires;
}
public void supprimerLienFormation(ECF pECF, Formation pForm)
{
ECFDAL.supprimerLienFormation(pECF, pForm);
}
public List<Stagiaire> getListeStagiaires(Formation pFormation, int pTypeFormation, String pFiltreNomPrenom)
{
return StagiairesDAL.getListeStagiaires(pFormation, pTypeFormation, pFiltreNomPrenom);
}
public static void supprimerLienFormation(ECF ecf, Formation form)
{
SqlConnection connexion = ConnexionSQL.CreationConnexion();
SqlCommand cmd = new SqlCommand(DELETE_LIEN_FORMATION, connexion);
cmd.Parameters.AddWithValue("@idECF", ecf.Id);
cmd.Parameters.AddWithValue("@codeFormation", form.Code);
cmd.ExecuteReader();
connexion.Close();
}
public static List<ECF> getListECFs()
{
List<ECF> lesECFs = new List<ECF>();
SqlConnection connexion = ConnexionSQL.CreationConnexion();
SqlCommand cmd = new SqlCommand(SELECT_ECFS, connexion);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ECF ecfTemp = new ECF();
ecfTemp.Id = reader.GetInt32(reader.GetOrdinal("idECF"));
ecfTemp.Code = reader.GetString(reader.GetOrdinal("code")).Trim();
ecfTemp.Libelle = reader.GetString(reader.GetOrdinal("libelle")).Trim();
if (reader["coefficient"] != DBNull.Value) ecfTemp.Coefficient = reader.GetDouble(reader.GetOrdinal("coefficient"));
ecfTemp.NotationNumerique = true;
if ((reader["typeNotation"] != DBNull.Value) && (reader.GetInt16(reader.GetOrdinal("typeNotation")) == Ressources.CONSTANTES.NOTATION_ACQUISITION))
{
ecfTemp.NotationNumerique = false;
}
if (reader["nbreVersions"] != DBNull.Value) ecfTemp.NbreVersion = reader.GetInt32(reader.GetOrdinal("nbreVersions"));
if (reader["commentaire"] != DBNull.Value) ecfTemp.Commentaire = reader.GetString(reader.GetOrdinal("commentaire")).Trim();
//Competences
SqlConnection c2 = ConnexionSQL.CreationConnexion();
SqlCommand cmd2 = new SqlCommand(SELECT_COMPS, c2);
cmd2.Parameters.Add(new SqlParameter("@lienECFComp",ecfTemp.Id));
SqlDataReader reader2 = cmd2.ExecuteReader();
List<Competence> lesComp = new List<Competence>();
while (reader2.Read())
{
Competence compTemp = new Competence();
compTemp.Id = reader2.GetInt32(reader2.GetOrdinal("idCompetence"));
compTemp.Code = reader2.GetString(reader2.GetOrdinal("code")).Trim();
compTemp.Libelle = reader2.GetString(reader2.GetOrdinal("libelle")).Trim();
lesComp.Add(compTemp);
}
c2.Close();
ecfTemp.Competences = lesComp;
//Formations
SqlConnection c3 = ConnexionSQL.CreationConnexion();
SqlCommand cmd3 = new SqlCommand(SELECT_FORMS, c3);
cmd3.Parameters.Add(new SqlParameter("@lienECFForm", ecfTemp.Id));
SqlDataReader reader3 = cmd3.ExecuteReader();
List<Formation> lesFormations = new List<Formation>();
while (reader3.Read())
{
Formation formTemp = new Formation();
formTemp.Code = reader3.GetString(reader3.GetOrdinal("CodeFormation"));
formTemp.Libelle = reader3.GetString(reader3.GetOrdinal("LibelleCourt")).Trim();
lesFormations.Add(formTemp);
}
c3.Close();
ecfTemp.Formations = lesFormations;
lesECFs.Add(ecfTemp);
}
connexion.Close();
return lesECFs;
}
public void ajouterLienFormation(ECF pECF, Formation pForm)
{
ECFDAL.ajouterLienFormation(pECF, pForm);
}
public void ajouterFormation(ECF pECF, Formation pForm)
{
pECF._formations.Add(pForm);
}
public void ajouterECF(Formation pFormation,ECF pECF)
{
pFormation._epreuves.Add(pECF);
}