public static void AjoutCritere(Critere unCritere, Associer uneAssociation, int unIdOffre)
{
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
// Insert some data
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO critere (id_critere, libelle_critere) VALUES (DEFAULT, '" + unCritere.GetLibelle() + "')";
cmd.ExecuteNonQuery();
}
int id = -1;
using (var cmd2 = new NpgsqlCommand("SELECT id_critere FROM critere ORDER BY id_critere", conn))
using (var reader = cmd2.ExecuteReader())
while (reader.Read())
{
id = reader.GetInt32(0);
}
using (var cmd3 = new NpgsqlCommand())
{
cmd3.Connection = conn;
cmd3.CommandText = "INSERT INTO associer (id_critere, id_offre, coefficient) VALUES (" + id + ", " + unIdOffre + "," + uneAssociation.GetCoeff() + ")";
cmd3.ExecuteNonQuery();
}
conn.Close();
}
}
public static void AjouterEvaluation(Dictionary <string, int> lesLibelleNote, string commentaire, int bonusMalus, int idCand, string nomRH)
{
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
List <int> lesId = new List <int>();
List <int> lesNotes = new List <int>();
foreach (KeyValuePair <string, int> uneNote in lesLibelleNote)
{
string libelle = uneNote.Key;
using (var cmd2 = new NpgsqlCommand("SELECT id_critere FROM critere WHERE libelle_critere LIKE '" + libelle + "'", conn))
using (var reader = cmd2.ExecuteReader())
while (reader.Read())
{
lesId.Add(reader.GetInt32(0));
}
lesNotes.Add(uneNote.Value);
}
using (var cmd3 = new NpgsqlCommand())
{
cmd3.Connection = conn;
cmd3.CommandText = "INSERT INTO evaluation(id_eval, nom_prenom_rh, date_evaluation, bonusmalus, commentaire_eval, id_cand) VALUES (DEFAULT, '" + nomRH + "','" + DateTime.Now.ToShortDateString() + "','" + bonusMalus + "','" + commentaire + "','" + idCand + "')";
cmd3.ExecuteNonQuery();
}
int id = -1;
using (var cmd2 = new NpgsqlCommand("SELECT id_eval FROM evaluation ORDER BY id_eval", conn))
using (var reader = cmd2.ExecuteReader())
while (reader.Read())
{
id = reader.GetInt32(0);
}
Dictionary <int, int> lesIdNote = new Dictionary <int, int>();
for (int i = 0; i < lesId.Count; i++)
{
lesIdNote[lesId[i]] = lesNotes[i];
}
foreach (KeyValuePair <int, int> uneNote in lesIdNote)
{
using (var cmd3 = new NpgsqlCommand())
{
cmd3.Connection = conn;
cmd3.CommandText = "INSERT INTO noter (id_eval, id_critere, note) VALUES (" + id + ", " + uneNote.Key + "," + uneNote.Value + ")";
cmd3.ExecuteNonQuery();
}
}
conn.Close();
}
}
public static void ModifierDateLimite(int idOffre, DateTime uneDate)
{
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
// modifier la date
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "UPDATE offre_emplois SET date_limite_offre='" + uneDate + "' WHERE id_offre=" + idOffre;
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public static void ModifCoeff(string libelle, int coeff)
{
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
// modifier le coeff dans le forms Offres
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "UPDATE associer SET coefficient='" + coeff + "' WHERE id_critere=(SELECT id_critere FROM CRITERE WHERE libelle_critere = '" + libelle + "')";
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public static double GetNoteTot(int idEval)
{
double noteTot = 0;
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT noteTotal FROM NoteRH WHERE id_eval = " + idEval + " ORDER BY id_eval;", conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
noteTot = reader.GetInt32(0);
}
conn.Close();
}
return(noteTot);
}
public static int GetIdLastEval()
{
int idEval = -1;
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT id_eval FROM evaluation ORDER BY id_eval DESC LIMIT 1;", conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
idEval = reader.GetInt32(0);
}
conn.Close();
}
return(idEval);
}
public static List <Candidature> GetCandidature(int idOffre)
{
List <Candidature> lesCandidatures = new List <Candidature>();
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT id_cand, nom_cand, prenom_cand FROM candidature WHERE id_offre = " + idOffre, conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
Candidature c = new Candidature(reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
lesCandidatures.Add(c);
}
conn.Close();
}
return(lesCandidatures);
}
public static DateTime GetDateLimite(int idOffre)
{
DateTime uneDate = new DateTime();
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT id_offre,date_limite_offre FROM offre_emplois WHERE id_offre=" + idOffre, conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
uneDate = reader.GetDateTime(1);
}
conn.Close();
}
return(uneDate);
}
public static List <Offre> GetLesOffres()
{
List <Offre> listOffres = new List <Offre>();
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT id_offre,libelle, lieu FROM offre_emplois", conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
Offre uneOffre = new Offre(reader.GetInt32(0), reader.GetString(1), reader.GetString(2));
listOffres.Add(uneOffre);
}
conn.Close();
}
return(listOffres);
}
public static Dictionary <string, int> GetCritereCoeff(int idOffre)
{
Dictionary <string, int> critereCoeff = new Dictionary <string, int>();
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT libelle_critere, coefficient FROM associer INNER JOIN critere ON associer.id_critere = critere.id_critere WHERE id_offre = " + idOffre, conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
critereCoeff.Add(reader.GetString(0), reader.GetInt32(1));
}
conn.Close();
}
return(critereCoeff);
}
public static List <Evaluation> GetEvaluations(int idCand)
{
List <Evaluation> lesEvaluations = new List <Evaluation>();
Evaluation e = null;
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT id_eval, nom_prenom_rh, date_evaluation, bonusmalus, commentaire_eval FROM evaluation WHERE id_cand = " + idCand, conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
e = new Evaluation(reader.GetInt32(0), reader.GetString(1), reader.GetDateTime(2), reader.GetInt32(3), reader.GetString(4), GetNoteTot(reader.GetInt32(0)));
lesEvaluations.Add(e);
}
conn.Close();
}
return(lesEvaluations);
}
public static Dictionary <string, double> AfficherTableauBord(int idCand)
{
Dictionary <string, double> resul = new Dictionary <string, double>();
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
// connect à la bdd
conn.Open();
using (var cmd2 = new NpgsqlCommand("SELECT nom_prenom_RH, notetotal FROM EVALUATION E INNER JOIN CANDIDATURE C ON C.id_cand = E.id_cand WHERE E.id_cand =" + 1 + "ORDER BY notetotal desc", conn))
using (var reader = cmd2.ExecuteReader())
while (reader.Read())
{
resul.Add(reader.GetString(0), reader.GetDouble(1));
}
conn.Close();
}
return(resul);
}
public static List <Associer> GetLesAssociations()
{
List <Associer> listAssociation = new List <Associer>();
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT coefficient, id_critere, id_offre FROM associer ORDER BY id_critere", conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
Associer uneAssociation = new Associer(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2));
listAssociation.Add(uneAssociation);
}
conn.Close();
}
return(listAssociation);
}
public static List <Critere> GetLesCriteresByOffre(int unIdOffre)
{
//permet d'associer une offre à un ou plusieurs critères dans le form offre
List <Critere> listCritere = new List <Critere>();
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT libelle_critere FROM critere c INNER JOIN associer a ON a.id_critere = c.id_critere WHERE id_offre= " + unIdOffre, conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
Critere unCritere = new Critere(reader.GetString(0));
listCritere.Add(unCritere);
}
conn.Close();
}
return(listCritere);
}
public static Dictionary <string, int> ModifierCritere(string libelle, int idOffre)
{
Dictionary <string, int> critereCoeff = new Dictionary <string, int>();
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
// connect à la bdd
conn.Open();
//on récupère les données nécessaire pour les mettre dans le dictionnaire
using (var cmd2 = new NpgsqlCommand("SELECT libelle_critere, coefficient FROM critere C INNER JOIN associer A ON A.id_critere= C.id_critere WHERE C.id_critere= (SELECT id_critere FROM CRITERE WHERE libelle_critere = '" + libelle + "') AND id_offre=" + idOffre, conn))
using (var reader = cmd2.ExecuteReader())
while (reader.Read())
{
critereCoeff.Add(reader.GetString(0), reader.GetInt32(1));
}
conn.Close();
}
return(critereCoeff);
}
public TableauBord(int idOffre, string unNomRH)
{
this.idOffre = idOffre;
this.nomRH = unNomRH;
InitializeComponent();
NpgsqlConnection conn = new NpgsqlConnection(Connexion.Connecter());
conn.Open();
using (NpgsqlCommand cmd2 = new NpgsqlCommand("SELECT N.nom_cand, N.prenom_cand, M.notemoyenne,N.noteTotal, N.nom_prenom_rh FROM NoteRH N INNER JOIN NoteMoy M ON M.nom_cand = N.nom_cand AND M.prenom_cand = N.prenom_cand ORDER BY M.notemoyenne, N.nom_cand, N.prenom_cand ; ", conn))
using (NpgsqlDataReader reader = cmd2.ExecuteReader())
{
//detection de la première ligne
int first = 1;
//tant qu'on a des lignes sql
while (reader.Read())
{
bool verif = false;
int comp = 0;
//Colonne pour chaque l'évaluateur est marquer
for (int i = 0; i < dataGridViewTableauBord.Columns.Count; i++)
{
// si l'évalutateur est déjà entre dans la colonne
if (dataGridViewTableauBord.Columns[i].HeaderText == reader.GetString(4))
{
verif = true;
comp = i;
}
}
//si l'evaluateur n'est pas déjà dans la colonne
if (verif == false)
{
//on ajoute l'évaluateur dans la dernière colonne
dataGridViewTableauBord.Columns.Add(reader.GetString(4), reader.GetString(4));
comp = dataGridViewTableauBord.ColumnCount - 1;
}
//Row
if (first != 1)
{
//si les données sont identiques on les ajoute à la ligne existante
if (dataGridViewTableauBord.Rows[0].Cells[0].Value.ToString() == reader.GetString(0) + " " + reader.GetString(1))
{
//on met la note du candidat à l'évalutateur
dataGridViewTableauBord.Rows[0].Cells[comp].Value = reader.GetInt32(3);
}
//Sinon on créer une nouvele ligne aves le nom et prenom cand et sa moyenne
else
{
dataGridViewTableauBord.Rows.Add();
dataGridViewTableauBord.Rows[0].Cells[0].Value = reader.GetString(0) + " " + reader.GetString(1);
dataGridViewTableauBord.Rows[0].Cells[1].Value = reader.GetInt32(2);
dataGridViewTableauBord.Rows[0].Cells[comp].Value = reader.GetInt32(3);
}
}
//on ajoute sans ajouter de ligne
else
{
dataGridViewTableauBord.Rows[0].Cells[0].Value = reader.GetString(0) + " " + reader.GetString(1);
dataGridViewTableauBord.Rows[0].Cells[1].Value = reader.GetInt32(2);
dataGridViewTableauBord.Rows[0].Cells[comp].Value = reader.GetInt32(3);
first = -1;
}
}
;
}
}
public OffreCritereDRH(string unNomRH)
{
this.nomRH = unNomRH;
InitializeComponent();
//permet de ne pas supprimer à chaque fois les offres dans la bdd
string id = "-1";
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT id_offre FROM OFFRE_EMPLOIS ORDER BY id_offre", conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
id = reader.GetInt32(0).ToString();
}
conn.Close();
}
//Ouverture du web sercive et créattion du document xml
sioservicePortClient webServive = new sioservicePortClient();
string web = webServive.exportOffreList(id);
XmlDocument doc1 = new XmlDocument();
doc1.LoadXml(web);
XmlNodeList id_offre = doc1.GetElementsByTagName("id_offre");
XmlNodeList libelle = doc1.GetElementsByTagName("libelle");
XmlNodeList description = doc1.GetElementsByTagName("description");
XmlNodeList lieu = doc1.GetElementsByTagName("lieu");
XmlNodeList type_contrat = doc1.GetElementsByTagName("type_contrat");
XmlNodeList salaire = doc1.GetElementsByTagName("salaire");
XmlNodeList date_limite = doc1.GetElementsByTagName("date_limite");
XmlNodeList supprimer = doc1.GetElementsByTagName("supprimer");
//permet d'insérer les données qui se trouvent dans le xml dans la bdd posgres
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
for (int i = 0; i < id_offre.Count; i++)
{
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
//innerXML permet d'enlever les balises
cmd.CommandText = "INSERT INTO OFFRE_EMPLOIS (id_offre, libelle, description, lieu, type_contrat, salaire, date_limite, supprimer, date_limite_offre ) VALUES (" + id_offre[i].InnerXml + ",'" + libelle[i].InnerXml + "', '" + description[i].InnerXml + "','" + lieu[i].InnerXml + "','" + type_contrat[i].InnerXml + "','" + salaire[i].InnerXml + "', '" + date_limite[i].InnerXml + "', '" + supprimer[i].InnerXml + "', NOW())";
cmd.ExecuteNonQuery();
}
}
conn.Close();
}
string web2 = webServive.exportCandidatureList(id);
XmlDocument doc2 = new XmlDocument();
doc2.LoadXml(web2);
XmlNodeList id_candidature = doc2.GetElementsByTagName("id_candidature");
XmlNodeList nom_candidature = doc2.GetElementsByTagName("nom_candidature");
XmlNodeList prenom_candidature = doc2.GetElementsByTagName("prenom_candidature");
XmlNodeList date_candidature = doc2.GetElementsByTagName("date_candidature");
XmlNodeList id_offre_candidature = doc2.GetElementsByTagName("id_offre_candidature");
//permet d'insérer les données xml dans la bdd posgres
using (var conn = new NpgsqlConnection(Connexion.Connecter()))
{
conn.Open();
for (int i = 0; i < id_candidature.Count; i++)
{
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO CANDIDATURE (id_cand, nom_cand, prenom_cand, date_cand , statut_cand, id_offre ) VALUES (" + id_candidature[i].InnerXml + ",'" + nom_candidature[i].InnerXml + "', '" + prenom_candidature[i].InnerXml + "','" + date_candidature[i].InnerXml + "','Attente','" + id_offre_candidature[i].InnerXml + "')";
cmd.ExecuteNonQuery();
}
}
conn.Close();
}
//Pour afficher les offres au commencement de l'appli
foreach (Offre o in DAOOffre.GetLesOffres())
{
listBoxOffre.Items.Add(o.GetIdOffre() + "-" + o.GetLibelle() + "-" + o.GetLieu());
listBoxOffreID.Items.Add(o.GetIdOffre());
}
listBoxOffre.SetSelected(0, true);
}
