public MV_Fonctionnaire(M_Fonctionnaire m)
{
nom = m.Nom;
prenom = m.Prenom;
departement = m.Departement.NomDepartement;
poste = m.Poste.Poste;
salaire = m.Salaire;
}
public List <M_Fonctionnaire> GetFonctionnaires(string nom, string poste)
{
string requete;
#region Requete sql
//Nom choisi seul
if (nom != "" && poste == "")
{
nom = "%" + nom + "%";
requete = "Select employee.id,lastName, firstName, name,title,annualSalary FROM employee INNER JOIN position ON positionID = position.id INNER JOIN department on departementID= department.id WHERE lastName LIKE '" + nom + "' order by lastName;";
}
//Poste choisi seul
else if (poste != "" && nom == "")
{
poste = "%" + poste + "%";
requete = "Select employee.id,lastName, firstName, name,title,annualSalary FROM employee INNER JOIN position ON positionID = position.id INNER JOIN department on departementID= department.id WHERE title LIKE '" + poste + "' order by lastName;";
}
//2 champs remplis
else if (poste != "" && nom != "")
{
poste = "%" + poste + "%";
nom = "%" + nom + "%";
requete = "Select employee.id,lastName, firstName, name,title,annualSalary FROM employee INNER JOIN position ON positionID = position.id INNER JOIN department on departementID= department.id WHERE lastName LIKE '" + nom + "' AND title LIKE '" + poste + "' order by lastName;";
}
else
{
requete = "Select employee.id,lastName, firstName, name,title,annualSalary FROM employee INNER JOIN position ON positionID = position.id INNER JOIN department on departementID= department.id order by lastName LIMIT 200";
}
#endregion
#region Connexion à la BDD
string connectionString = "SERVER=localhost ;PORT=3306;DATABASE=S6_MVC_Chicago;UID=esilvs6 ;PASSWORD= esilvs6 ; ";
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
MySqlDataReader reader;
command.CommandText = requete;
reader = command.ExecuteReader();
#endregion
while (reader.Read())
{
//A chaque tuple, on crée un Fonctionnaire.
M_Fonctionnaire m_Fonctionnaire = new M_Fonctionnaire(int.Parse(reader.GetValue(0).ToString()), reader.GetValue(1).ToString(), reader.GetValue(2).ToString(), new M_Departement(reader.GetValue(3).ToString()), new M_Poste(reader.GetValue(4).ToString()), int.Parse(reader.GetValue(5).ToString()));
ListeFonctionnaires.Add(m_Fonctionnaire);
}
connection.Close();
return(ListeFonctionnaires);
}
public M_Fonctionnaire AddFonctionnaire(string nom, string prenom, string poste, string deptmt, string salaire)
{
//Création d'id éventuel
int idPoste;
int idDepartment;
#region REQUETES
#region Requetes obtenir ID maximum
string requeteGetMaxIdDepartment = "SELECT max(id) FROM department;";
string requeteGetMaxIdPoste = "SELECT max(id) FROM position;";
#endregion
#region Requetes de vérification
string verifPosition = "SELECT id,title from position WHERE title like '" + poste + "'";
string verifDepartment = "SELECT id,name from department WHERE name like '" + deptmt + "'";
#endregion
#region Requetes d'insertion
string insererPoste = "INSERT INTO S6_MVC_Chicago.Position (title) VALUES ('" + poste + "');";
string insererDepartement = "INSERT INTO S6_MVC_Chicago.Department(name) VALUES('" + deptmt + "');";
string insererFonctionnaire;
#endregion
#endregion
#region Connexion BDD
string connectionString = "SERVER=localhost ;PORT=3306;DATABASE=S6_MVC_Chicago;UID=esilvs6 ;PASSWORD= esilvs6 ; ";
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
#endregion
#region Verification ID poste
command = connection.CreateCommand();
command.CommandText = verifPosition;
MySqlDataReader readerverifPosition;
readerverifPosition = command.ExecuteReader();
if (readerverifPosition.HasRows)
{
readerverifPosition.Read();
//On récupère cet id
idPoste = int.Parse(readerverifPosition.GetValue(0).ToString());
}
else
{
//On ajoute cet id et on récupere ce new id
connection.Close();
connection.Open();
command = connection.CreateCommand();
command.CommandText = insererPoste;
MySqlDataReader readerinsertion = command.ExecuteReader();
readerinsertion.Read();
connection.Close();
connection.Open();
command = connection.CreateCommand();
command.CommandText = requeteGetMaxIdPoste;
MySqlDataReader readerId = command.ExecuteReader();
readerId.Read();
idPoste = int.Parse(readerId.GetValue(0).ToString());
}
#endregion
#region Verification ID Departement
connection.Close();
connection.Open();
command = connection.CreateCommand();
command.CommandText = insererDepartement;
MySqlDataReader readerinsertiondpt = command.ExecuteReader();
readerinsertiondpt.Read();
connection.Close();
connection.Open();
MySqlDataReader readerDepartment;
command = connection.CreateCommand();
command.CommandText = verifDepartment;
readerDepartment = command.ExecuteReader();
if (readerDepartment.HasRows)
{
readerDepartment.Read();
//Recupere nouvel ID
idDepartment = int.Parse(readerDepartment.GetValue(0).ToString());
}
else
{
// Ajoute cet ID et on récupere ce new ID
connection.Close();
connection.Open();
command = connection.CreateCommand();
command.CommandText = requeteGetMaxIdDepartment;
MySqlDataReader readerId = command.ExecuteReader();
readerId.Read();
idDepartment = int.Parse(readerId.GetValue(0).ToString());
command.CommandText = insererDepartement;
Console.WriteLine(idDepartment);
}
#endregion
#region Insere finalement le fonctionnaire
try
{
connection.Close();
connection.Open();
command = connection.CreateCommand();
insererFonctionnaire = "INSERT INTO S6_MVC_Chicago.employee (lastName, firstName,positionID, departementID, annualSalary) VALUES ('" + nom + "','" + prenom + "','" + idPoste + "','" + idDepartment + "','" + salaire + "')";
command.CommandText = insererFonctionnaire;
MySqlDataReader fonctio = command.ExecuteReader();
fonctio.Read();
connection.Close();
}
catch (Exception e)
{
Console.WriteLine("Erreur : Fonctionnaire non ajouté\n" + e.Message);
}
connection.Open();
command = connection.CreateCommand();
string obtenirIdNewFonction = "Select max(id) from employee";
command.CommandText = obtenirIdNewFonction;
MySqlDataReader readerobtenirId = command.ExecuteReader();
readerobtenirId.Read();
int id = int.Parse(readerobtenirId.GetValue(0).ToString());
connection.Close();
#endregion
M_Fonctionnaire newM_Fonct = new M_Fonctionnaire(id, nom, prenom, new M_Departement(deptmt), new M_Poste(poste), int.Parse(salaire));
return(newM_Fonct);
}