public static ObservableCollection <Session> ListerSession()
{
using (var db = new DBAirAtlantiqueContext())
{
ObservableCollection <Session> ListerSessionDB = new ObservableCollection <Session>();
var Sessions = from session in db.Sessions orderby session.DateDebut where session.SessionValider == false select session;
foreach (Session session in Sessions)
{
var requeteOF = from of in db.OrganismeFormation where of.ID == session.OrganismeFomationConcerne.ID select of;
session.OrganismeFomationConcerne = requeteOF.First();
var requeteO = from o in db.Organismes where session.OrganismeFomationConcerne.OrganismeConcerne.OrganismeID == o.OrganismeID select o;
session.OrganismeFomationConcerne.OrganismeConcerne = requeteO.First();
var requetteF = from f in db.Formations where f.FormationID == session.OrganismeFomationConcerne.FormationConcerne.FormationID select f;
session.OrganismeFomationConcerne.FormationConcerne = requetteF.First();
ListerSessionDB.Add(session);
}
return(ListerSessionDB);
}
}
public static Employee ConctructeurEmploye(Employee employee)
{
using (var db = new DBAirAtlantiqueContext())
{
if (employee.CesSessions.Count == 0)
{
var sessions = from es in db.SessionEmployees where es.EmployeeConcerne.Matricule == employee.Matricule select es;
foreach (SessionEmployee es in sessions)
{
var requeteS = from s in db.Sessions where s.SessionID == es.SessionConcerne.SessionID select s;
es.SessionConcerne = requeteS.First();
employee.CesSessions.Add(es);
}
}
if (employee.CesFormations.Count() == 0)
{
var formations = from ef in db.EmployeeFormation where ef.EmployeeConcerne.Matricule == employee.Matricule select ef;
foreach (EmployeeFormation ef in formations)
{
var requeteF = from f in db.Formations where f.FormationID == ef.FormationConcerne.FormationID select f;
ef.FormationConcerne = requeteF.First();
employee.CesFormations.Add(ef);
}
}
return(employee);
}
}
public static Poste ConstructeurPoste(Poste poste)
{
using (var db = new DBAirAtlantiqueContext())
{
if (poste.ListeFormationMini.Count() == 0)
{
var formations = from pf in db.PosteFormation where pf.PosteConcerne.PosteID == poste.PosteID select pf;
foreach (PosteFormation pf in formations)
{
var requeteFormation = from f in db.Formations where f.FormationID == pf.FormationConcerne.FormationID select f;
pf.FormationConcerne = requeteFormation.First();
poste.ListeFormationMini.Add(pf);
}
}
if (poste.ListeEmployee.Count() == 0)
{
var employees = from e in db.Employees where e.PosteAttribuer.PosteID == poste.PosteID select e;
foreach (Employee pf in employees)
{
poste.ListeEmployee.Add(pf);
}
}
}
return(poste);
}
public static ObservableCollection <EmployeeFormation> ListerEmployeeFormationManquante()
{
using (var db = new DBAirAtlantiqueContext())
{
ObservableCollection <EmployeeFormation> listerEmployeeFormationDB = new ObservableCollection <EmployeeFormation>();
IQueryable <Poste> requettePoste = from p in db.Postes select p;
foreach (Poste poste in requettePoste)
{
var requetteFormationPoste = from f in db.Formations join pf in db.PosteFormation on f.FormationID equals pf.FormationConcerne.FormationID where pf.PosteConcerne.PosteID == poste.PosteID select f;
var requetteEmployeePoste = from e in db.Employees where e.PosteAttribuer.PosteID == poste.PosteID select e;
foreach (Employee employee in requetteEmployeePoste)
{
var requetteFormationEmployee = from f in db.Formations join ef in db.EmployeeFormation on f.FormationID equals ef.FormationConcerne.FormationID where ef.EmployeeConcerne.Matricule == employee.Matricule select f;
IQueryable <Formation> FormationManquante = requetteFormationPoste.Except(requetteFormationEmployee);
foreach (Formation formation in FormationManquante)
{
EmployeeFormation employeeformation = new EmployeeFormation
{
FormationConcerne = formation,
EmployeeConcerne = employee,
};
listerEmployeeFormationDB.Add(employeeformation);
}
}
}
return(listerEmployeeFormationDB);
}
}
public static Session ConstructeurSession(Session session)
{
using (var db = new DBAirAtlantiqueContext())
{
if (session.ListeEmployeeConcernees.Count() == 0)
{
var employees = from es in db.SessionEmployees where es.SessionConcerne.SessionID == session.SessionID select es;
foreach (SessionEmployee es in employees)
{
var requeteE = from e in db.Employees where e.Matricule == es.EmployeeConcerne.Matricule select e;
es.EmployeeConcerne = requeteE.First();
session.ListeEmployeeConcernees.Add(es);
}
}
var requetteOrganismeFormation = from of in db.OrganismeFormation where of.ID == session.OrganismeFomationConcerne.ID select of;
session.OrganismeFomationConcerne = requetteOrganismeFormation.First();
var requetteFormation = from f in db.Formations where session.OrganismeFomationConcerne.FormationConcerne.FormationID == f.FormationID select f;
session.OrganismeFomationConcerne.FormationConcerne = requetteFormation.First();
var requetteOrganisme = from o in db.Organismes where o.OrganismeID == session.OrganismeFomationConcerne.OrganismeConcerne.OrganismeID select o;
session.OrganismeFomationConcerne.OrganismeConcerne = requetteOrganisme.First();
return(session);
}
}
public static Formation ConstructeurFormation(Formation formation)
{
using (var db = new DBAirAtlantiqueContext())
{
if (formation.ListOrganismes.Count() == 0)
{
var Organismes = from of in db.OrganismeFormation join o in db.Organismes on of.OrganismeConcerne.OrganismeID equals o.OrganismeID where of.FormationConcerne.FormationID == formation.FormationID select of;
foreach (OrganismeFormation of in Organismes)
{
var requeteOrganisme = from o in db.Organismes where o.OrganismeID == of.OrganismeConcerne.OrganismeID select o;
of.OrganismeConcerne = requeteOrganisme.First();
formation.ListOrganismes.Add(of);
}
}
if (formation.ListEmployees.Count() == 0)
{
var Employees = from ef in db.EmployeeFormation where ef.FormationConcerne.FormationID == formation.FormationID select ef;
foreach (EmployeeFormation ef in Employees)
{
var requeteEmployee = from e in db.Employees where e.Matricule == ef.EmployeeConcerne.Matricule select e;
ef.EmployeeConcerne = requeteEmployee.First();
formation.ListEmployees.Add(ef);
}
}
}
return(formation);
}
public static void AjouterFormationDB(Formation formationNouveau)
{
using (var db = new DBAirAtlantiqueContext())
{
db.Formations.Add(formationNouveau);
db.SaveChanges();
}
}
public static void AjouterOrganisme(Organisme organismeNouveau)
{
using (var db = new DBAirAtlantiqueContext())
{
db.Organismes.Add(organismeNouveau);
db.SaveChanges();
}
}
private void AjouterPoste()
{
using (var db = new DBAirAtlantiqueContext())
{
db.Postes.Add(PosteResultat);
db.SaveChanges();
}
navigate.NavigateToAjouterPoste();
}
public static void AjouterOF(OrganismeFormation organismeFormationNouveau)
{
using (var db = new DBAirAtlantiqueContext())
{
var requeteFormation = from f in db.Formations where f.FormationID == organismeFormationNouveau.FormationConcerne.FormationID select f;
organismeFormationNouveau.FormationConcerne = requeteFormation.FirstOrDefault();
var requeteOrganisme = from o in db.Organismes where o.OrganismeID == organismeFormationNouveau.OrganismeConcerne.OrganismeID select o;
organismeFormationNouveau.OrganismeConcerne = requeteOrganisme.FirstOrDefault();
db.OrganismeFormation.Add(organismeFormationNouveau);
db.SaveChanges();
}
}
public static void AjouterEmployeBDD(Employee employeeNouveau, Poste posteConcerne)
{
using (var db = new DBAirAtlantiqueContext())
{
var requettePoste = from p in db.Postes where posteConcerne.PosteID == p.PosteID select p;
employeeNouveau.PosteAttribuer = requettePoste.First();
db.Employees.Add(employeeNouveau);
db.SaveChanges();
}
}
public static ObservableCollection <Organisme> ListerOrganisme()
{
using (var db = new DBAirAtlantiqueContext())
{
ObservableCollection <Organisme> ListerOrganismeDB = new ObservableCollection <Organisme>();
var Organimes = from item in db.Organismes select item;
foreach (Organisme item in Organimes)
{
ListerOrganismeDB.Add(item);
}
return(ListerOrganismeDB);
}
}
public static ObservableCollection <Employee> ListerEmployes()
{
using (var db = new DBAirAtlantiqueContext())
{
ObservableCollection <Employee> ListerEmployeeDB = new ObservableCollection <Employee>();
var Employes = from item in db.Employees orderby item.Matricule select item;
foreach (Employee item in Employes)
{
ListerEmployeeDB.Add(item);
}
return(ListerEmployeeDB);
}
}
public static ObservableCollection <Formation> ListerFormation()
{
using (var db = new DBAirAtlantiqueContext())
{
ObservableCollection <Formation> ListerFormationDB = new ObservableCollection <Formation>();
var formations = from item in db.Formations orderby item.Nom select item;
foreach (Formation formation in formations)
{
ListerFormationDB.Add(formation);
}
return(ListerFormationDB);
}
}
public static ObservableCollection <Session> ListerSessionFinisNonValider()
{
using (var db = new DBAirAtlantiqueContext())
{
ObservableCollection <Session> ListerSessionDB = new ObservableCollection <Session>();
var Sessions = from session in db.Sessions orderby session.DateDebut where session.DateFin < DateTime.Now where session.SessionValider == false select session;
foreach (Session session in Sessions)
{
ListerSessionDB.Add(DAOSession.ConstructeurSession(session));
}
return(ListerSessionDB);
}
}
public static ObservableCollection <Poste> ListerPoste()
{
using (var db = new DBAirAtlantiqueContext())
{
ObservableCollection <Poste> ListerPosteDB = new ObservableCollection <Poste>();
var Postes = from item in db.Postes select item;
foreach (Poste item in Postes)
{
ListerPosteDB.Add(item);
}
return(ListerPosteDB);
}
}
public static ObservableCollection <Session> ListerSessionPourUnOrganisme(Organisme organismeConcerne)
{
using (var db = new DBAirAtlantiqueContext())
{
ObservableCollection <Session> ListerSessionDB = new ObservableCollection <Session>();
var sessions = from s in db.Sessions where s.OrganismeFomationConcerne.OrganismeConcerne.OrganismeID == organismeConcerne.OrganismeID select s;
foreach (Session session in sessions)
{
var requeteFormation = from f in db.Formations where f.FormationID == session.OrganismeFomationConcerne.FormationConcerne.FormationID select f;
session.OrganismeFomationConcerne.FormationConcerne = requeteFormation.First();
ListerSessionDB.Add(session);
}
return(ListerSessionDB);
}
}
public static ObservableCollection <Formation> ListerFormationPourUnEmployee(Employee employeeConcerne)
{
using (var db = new DBAirAtlantiqueContext())
{
ObservableCollection <Formation> ListerFormationDB = new ObservableCollection <Formation>();
IQueryable <Formation> FormationManquante = from item in db.Formations select item;
IQueryable <Formation> FormationsDejaObtenu = from f in db.Formations join ef in db.EmployeeFormation on f.FormationID equals ef.FormationConcerne.FormationID where ef.EmployeeConcerne.Matricule == employeeConcerne.Matricule select f;
FormationManquante = FormationManquante.Except(FormationsDejaObtenu);
foreach (Formation item in FormationManquante)
{
ListerFormationDB.Add(item);
}
return(ListerFormationDB);
}
}
public static ObservableCollection <Formation> ListerFormationObligatoirePoste(Poste posteConcerne)
{
using (var db = new DBAirAtlantiqueContext())
{
ObservableCollection <Formation> ListerFormationDB = new ObservableCollection <Formation>();
IQueryable <Formation> Formations = from item in db.Formations orderby item.Nom select item;
IQueryable <Formation> FormationAcquise = from f in db.Formations join pf in db.PosteFormation on f.FormationID equals pf.FormationConcerne.FormationID where pf.PosteConcerne.PosteID == posteConcerne.PosteID select f;
Formations = Formations.Except(FormationAcquise);
foreach (Formation item in Formations)
{
ListerFormationDB.Add(DAOFormation.ConstructeurFormation(item));
}
return(ListerFormationDB);
}
}
public static void AjouterPosteFormation(Formation formationConcerne, Poste posteConcerne)
{
using (var db = new DBAirAtlantiqueContext())
{
var requetteFormation = from f in db.Formations where f.FormationID == formationConcerne.FormationID select f;
var requettePoste = from p in db.Postes where p.PosteID == posteConcerne.PosteID select p;
PosteFormation posteFormationResultat = new PosteFormation
{
PosteConcerne = requettePoste.First(),
FormationConcerne = requetteFormation.First()
};
db.PosteFormation.Add(posteFormationResultat);
db.SaveChanges();
}
}
public static void AjouterEmployeeFormation(EmployeeFormation employeeFormationNouveau)
{
{
using (var db = new DBAirAtlantiqueContext())
{
var requetteFormation = from f in db.Formations where f.FormationID == employeeFormationNouveau.FormationConcerne.FormationID select f;
var requetteEmployee = from e in db.Employees where e.Matricule == employeeFormationNouveau.EmployeeConcerne.Matricule select e;
employeeFormationNouveau.EmployeeConcerne = requetteEmployee.First();
employeeFormationNouveau.FormationConcerne = requetteFormation.First();
db.EmployeeFormation.Add(employeeFormationNouveau);
db.SaveChanges();
}
}
}
public static Organisme ConstructeurOrganisme(Organisme organisme)
{
using (var db = new DBAirAtlantiqueContext())
{
if (organisme.FormationsProposer.Count() == 0)
{
var formations = from of in db.OrganismeFormation where of.OrganismeConcerne.OrganismeID == organisme.OrganismeID select of;
foreach (OrganismeFormation of in formations)
{
var requeteFormation = from f in db.Formations where f.FormationID == of.FormationConcerne.FormationID select f;
of.FormationConcerne = requeteFormation.First();
organisme.FormationsProposer.Add(of);
}
}
}
return(organisme);
}
public static void AjouterSession(Session sessionNouvelle, OrganismeFormation organismeFormationConcerne)
{
using (var db = new DBAirAtlantiqueContext())
{
var requetteOF = from of in db.OrganismeFormation where of.OrganismeConcerne.OrganismeID == organismeFormationConcerne.OrganismeConcerne.OrganismeID where of.FormationConcerne.FormationID == organismeFormationConcerne.FormationConcerne.FormationID select of;
if (sessionNouvelle.Lieu == null)
{
sessionNouvelle.Lieu = "Nantes";
}
sessionNouvelle.OrganismeFomationConcerne = requetteOF.First();
db.Sessions.Add(sessionNouvelle);
db.SaveChanges();
}
}
public static void AjouterSessionEmployee(SessionEmployee sEConcerne)
{
using (var db = new DBAirAtlantiqueContext())
{
var requetteE = from e in db.Employees where sEConcerne.EmployeeConcerne.Matricule == e.Matricule select e;
var requetteS = from s in db.Sessions where sEConcerne.SessionConcerne.SessionID == s.SessionID select s;
SessionEmployee _sessionEmployeeNouveau = new SessionEmployee
{
SessionConcerne = requetteS.First(),
EmployeeConcerne = requetteE.First()
};
db.SessionEmployees.Add(_sessionEmployeeNouveau);
db.SaveChanges();
}
}
public static void ValiderSession(Session sessionConcernee)
{
using (var db = new DBAirAtlantiqueContext())
{
foreach (SessionEmployee employee in sessionConcernee.ListeEmployeeConcernees)
{
var requetteE = from e in db.Employees where e.Matricule == employee.EmployeeConcerne.Matricule select e;
var requetteF = from f in db.Formations where f.FormationID == sessionConcernee.OrganismeFomationConcerne.FormationConcerne.FormationID select f;
EmployeeFormation employeeFormationNew = new EmployeeFormation
{
EmployeeConcerne = requetteE.First(),
FormationConcerne = requetteF.First()
};
db.EmployeeFormation.Add(employeeFormationNew);
}
var requetteS = from s in db.Sessions where sessionConcernee.SessionID == s.SessionID select s;
requetteS.First().SessionValider = true;
db.SaveChanges();
}
}
public static ObservableCollection <Employee> ListerEmployeeManquantAUneSession(Session sessionConcerne)
{
using (var db = new DBAirAtlantiqueContext())
{
ObservableCollection <Employee> ListerEmployeeManquantDB = new ObservableCollection <Employee>();
IQueryable <Employee> employeesDejaFormerDB = from e in db.Employees join ef in db.EmployeeFormation on e.Matricule equals ef.EmployeeConcerne.Matricule where ef.FormationConcerne.FormationID == sessionConcerne.OrganismeFomationConcerne.FormationConcerne.FormationID select e;
IQueryable <Employee> employeesTotalDB = from e in db.Employees select e;
IQueryable <Employee> employeesDejaSessionDB = from e in db.Employees join se in db.SessionEmployees on e.Matricule equals se.EmployeeConcerne.Matricule join s in db.Sessions on se.SessionConcerne.SessionID equals s.SessionID where sessionConcerne.OrganismeFomationConcerne.FormationConcerne.FormationID == s.OrganismeFomationConcerne.FormationConcerne.FormationID select e;
IQueryable <Employee> employeeManquant = employeesTotalDB.Except(employeesDejaFormerDB);
employeeManquant = employeeManquant.Except(employeesDejaSessionDB);
foreach (Employee employee in employeeManquant)
{
ListerEmployeeManquantDB.Add(employee);
}
return(ListerEmployeeManquantDB);
}
}
