public IList<PeriodePaie> RetrieveAll()
{
IList<PeriodePaie> result = new List<PeriodePaie>();
try
{
connexion = new MySqlConnexion();
string requete = "SELECT dateDebut, dateFin FROM periodepaies";
DataSet dataset = connexion.Query(requete);
DataTable table = dataset.Tables[0];
foreach (DataRow periode in table.Rows)
{
PeriodePaie P = ConstructPeriodePaie(periode);
result.Add(P);
}
}
catch (MySqlException)
{
throw;
}
return result;
}
//Ajouter un employé
public void AjoutUnEmploye(string Nom, string Prenom, string Poste, string Salaire,bool horsFonction,string date,string employeur, ObservableCollection<LiaisonProjetEmploye> Liaison)
{
try
{
connexion = new MySqlConnexion();
StringBuilder buildReq = new StringBuilder();
buildReq.Append("INSERT INTO Employes (nom,prenom,horsFonction) VALUES ('");
buildReq.Append(Nom);
buildReq.Append("' , '");
buildReq.Append(Prenom);
buildReq.Append("' , ");
buildReq.Append(horsFonction);
buildReq.Append(")");
connexion.Query(buildReq.ToString());
buildReq = new StringBuilder();
buildReq.Append("SELECT idEmploye FROM Employes WHERE nom = '");
buildReq.Append(Nom);
buildReq.Append("' AND prenom = '");
buildReq.Append(Prenom);
buildReq.Append("'");
DataSet IDdataSet = connexion.Query(buildReq.ToString());
string ID = IDdataSet.Tables[0].Rows[0].ItemArray[0].ToString();
buildReq = new StringBuilder();
buildReq.Append("INSERT INTO detailfinancies (titreEmploi,tauxHoraireNormal,tauxHoraireOver,idEmploye,dateEmbauche,employeur) VALUES ('");
buildReq.Append(Poste);
buildReq.Append("' , '");
Salaire = Salaire.Replace(',', '.');
buildReq.Append(Salaire);
buildReq.Append("' , '");
Salaire = Salaire.Replace('.', ',');
Double salaireOver = Convert.ToSingle(Salaire) * 3 / 2;
salaireOver = Math.Round( salaireOver,2);
string s = salaireOver.ToString().Replace(',', '.');
buildReq.Append(s);
buildReq.Append("' , ");
buildReq.Append(ID);
buildReq.Append(", '");
buildReq.Append(date);
buildReq.Append("' , '");
buildReq.Append(employeur);
buildReq.Append("')");
connexion.Query(buildReq.ToString());
UpdateProjetEmploye(Liaison, ID);
}
catch (MySqlException)
{
throw;
}
}
public bool insertPaie(Paie insertPaie, DateTime start, DateTime end, string idEmploye)
{
try
{
connexion = new MySqlConnexion();
/*{
System.Globalization.NumberFormatInfo nf = new System.Globalization.NumberFormatInfo()
{
NumberGroupSeparator = "."
};
Brute = float.Parse(Brute, nf);
Net = float.Parse(Net.ToString(), nf);
HeureSupp = float.Parse(HeureSupp.ToString(), nf);
temps = float.Parse(temps.ToString(), nf);
}*/
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO Paies (idEmploye, idPeriode, montantDueBrute, montantDueNet, nombreHeure,nombreHeureSupp) VALUES(");
sb.Append("(SELECT idEmploye FROM Employes WHERE idEmploye ='");
sb.Append(idEmploye);
sb.Append("'),(SELECT idPeriode FROM periodepaies WHERE dateDebut ='");
sb.Append(start);
sb.Append("' AND dateFin = '");
sb.Append(end);
sb.Append("'),");
sb.Append(insertPaie.MontantBrute.ToString().Replace(",", "."));
sb.Append(",");
sb.Append(insertPaie.MontantNet.ToString().Replace(",", "."));
sb.Append(",");
sb.Append(insertPaie.NombreHeure.ToString().Replace(",", "."));
sb.Append(",");
sb.Append(insertPaie.NombreHeureSupp.ToString().Replace(",", "."));
sb.Append(")");
/* string requete = "INSERT INTO Paies (idEmploye, idPeriode, montantDueBrute, montantDueNet, nombreHeure,nombreHeureSupp) VALUES("
+"(SELECT idEmploye FROM Employes WHERE idEmploye ='" + idEmploye + "'),"
+"(SELECT idPeriode FROM periodepaies WHERE dateDebut = '"+start+"' AND dateFin = '"+end+"'),"
+""+insertPaie.MontantBrute+","+insertPaie.MontantNet+","+insertPaie.NombreHeure+","+insertPaie.NombreHeureSupp+""
+")";*/
connexion.Query(sb.ToString());
return true;
}
catch (MySqlException)
{
return false;
}
}
public DataTable anciennePeriode(string id)
{
try
{
connexion = new MySqlConnexion();
string requete = "SELECT dateDebut,dateFin FROM periodepaies WHERE idPeriode="+id;
DataSet dataset = connexion.Query(requete);
DataTable table = dataset.Tables[0];
return table;
}
catch (MySqlException)
{
throw;
}
}
public bool registerPeriode(PeriodePaie p)
{
try
{
StringBuilder sb = new StringBuilder();
connexion = new MySqlConnexion();
sb.Append("INSERT INTO PeriodePaies (dateDebut, dateFin) VALUES('");
sb.Append(p.Debut);
sb.Append("', '");
sb.Append(p.Fin);
sb.Append("');");
string req = sb.ToString();
connexion.Query(req);
return true;
}
catch (MySqlException)
{
return false;
}
}
public void AjouterPhoto(Byte[] ImageData,string nom,string prenom,string format)
{
try
{
connexion = new MySqlConnexion();
connexion.AjouterPhoto(ImageData, nom + prenom , format);
StringBuilder buildReq = new StringBuilder();
buildReq.Append("SELECT idEmploye FROM Employes WHERE nom = '");
buildReq.Append(nom);
buildReq.Append("' AND prenom = '");
buildReq.Append(prenom);
buildReq.Append("'");
DataSet dataset = connexion.Query(buildReq.ToString());
string IDEmploye = dataset.Tables[0].Rows[0][0].ToString();
buildReq = new StringBuilder();
buildReq.Append("SELECT idPhoto FROM Photos WHERE nom LIKE '%");
buildReq.Append(nom+prenom);
buildReq.Append("%'");
dataset = connexion.Query(buildReq.ToString());
string IDPhoto = dataset.Tables[0].Rows[0][0].ToString();
buildReq = new StringBuilder();
buildReq.Append("UPDATE Employes SET idPhoto = ");
buildReq.Append(IDPhoto);
buildReq.Append(" WHERE idEmploye = ");
buildReq.Append(IDEmploye);
connexion.Query(buildReq.ToString());
}
catch (MySqlException)
{
throw;
}
}
//Modifier Informations d'un employé
public void UpdateInfoEmploye(Employe emp, bool hF,string employeur,string date)
{
try
{
// nom, prenom
connexion = new MySqlConnexion();
StringBuilder buildReq = new StringBuilder();
buildReq.Append("SELECT Employes.idEmploye,detailfinancies.titreEmploi, detailfinancies.tauxHoraireNormal, detailfinancies.tauxHoraireOver, Employes.nom, Employes.prenom, Employes.horsFonction,detailfinancies.employeur,DATE_FORMAT(detailfinancies.dateEmbauche, '%Y-%m-%d') AS 'dateEmbauche' FROM Employes ");
buildReq.Append(" INNER JOIN detailfinancies ON detailfinancies.idEmploye = ");
buildReq.Append(emp.ID);
DataSet result = connexion.Query(buildReq.ToString());
DataTable table = result.Tables[0];
if (emp.Nom != table.Rows[0]["nom"].ToString() || emp.Prenom != table.Rows[0]["prenom"].ToString() || hF != (bool)table.Rows[0]["horsFonction"])
{
buildReq = new StringBuilder();
buildReq.Append("UPDATE Employes SET nom = '");
buildReq.Append(emp.Nom);
buildReq.Append("', prenom = '");
buildReq.Append(emp.Prenom);
buildReq.Append("', horsFonction = ");
buildReq.Append(hF);
buildReq.Append(" WHERE idEmploye = ");
buildReq.Append(emp.ID);
}
connexion.Query(buildReq.ToString());
// string s = table.Rows[0]["tauxHoraireNormal"].ToString().Replace(',', '.');
//float salaire = Convert.ToSingle(s);
string poste = table.Rows[0]["titreEmploi"].ToString();
//Poste + salaire
if (emp.Poste != poste || emp.Salaire != Convert.ToSingle(table.Rows[0]["tauxHoraireNormal"]) || employeur != table.Rows[0]["employeur"].ToString() || date != table.Rows[0]["dateEmbauche"].ToString())
{
buildReq = new StringBuilder();
buildReq.Append("UPDATE detailfinancies SET titreEmploi = '");
buildReq.Append(emp.Poste);
buildReq.Append("', tauxHoraireOver = '");
double s = Math.Round((emp.Salaire * 3 / 2), 2); ;
string sOver = s.ToString().Replace(',','.');
buildReq.Append(sOver);
buildReq.Append("', tauxHoraireNormal = '");
string salaire = emp.Salaire.ToString().Replace(',','.');
buildReq.Append(salaire);
if(employeur.Length > 0)
{
buildReq.Append("' , employeur = '");
buildReq.Append(employeur);
}
buildReq.Append("' , dateEmbauche = '");
buildReq.Append(date);
buildReq.Append("' WHERE idEmploye = '");
buildReq.Append(emp.ID);
buildReq.Append("'");
}
connexion.Query(buildReq.ToString());
}
catch (MySqlException)
{
throw;
}
}
public void SupprimerPhoto(string ID)
{
connexion = new MySqlConnexion();
StringBuilder buildReq = new StringBuilder();
buildReq.Append("SELECT idPhoto FROM Employes WHERE idEmploye = ");
buildReq.Append(ID);
DataSet dataset = connexion.Query(buildReq.ToString());
string idPhoto = dataset.Tables[0].Rows[0][0].ToString();
if (idPhoto !="")
{
buildReq = new StringBuilder();
buildReq.Append("UPDATE Employes SET idPhoto = null WHERE idEmploye = ");
buildReq.Append(ID);
connexion.Query(buildReq.ToString());
buildReq = new StringBuilder();
buildReq.Append("DELETE FROM Photos WHERE idPhoto = ");
buildReq.Append(idPhoto);
connexion.Query(buildReq.ToString());
}
}
//Construction liste des employés
public IList<Employe> RetrieveAll()
{
IList<Employe> lstEmploye = new List<Employe>();
try
{
connexion = new MySqlConnexion();
StringBuilder buildReq = new StringBuilder();
buildReq.Append("SELECT Employes.idEmploye,detailfinancies.titreEmploi, detailfinancies.tauxHoraireNormal , detailfinancies.tauxHoraireOver, Employes.nom, Employes.prenom, Employes.horsFonction FROM Employes ");
buildReq.Append(" INNER JOIN detailfinancies ON detailfinancies.idEmploye = Employes.idEmploye ");
buildReq.Append(" ORDER BY ");
buildReq.Append(" horsFonction, idEmploye ASC ");
DataSet dataset = connexion.Query(buildReq.ToString());
DataTable table = dataset.Tables[0];
if (table.Rows.Count != 0)
foreach (DataRow employe in table.Rows)
lstEmploye.Add(ConstructEmploye(employe));
}
catch (MySqlException)
{
throw;
}
return lstEmploye;
}
public Byte[] GetPhoto(string ID)
{
try
{
if(ID.Length>0)
{
connexion = new MySqlConnexion();
StringBuilder buildReq = new StringBuilder();
buildReq.Append("SELECT idPhoto FROM Employes WHERE idEmploye = ");
buildReq.Append(ID);
DataSet dataset = connexion.Query(buildReq.ToString());
string idPhoto = dataset.Tables[0].Rows[0][0].ToString();
if (idPhoto.Length > 0 )
return connexion.GetCodePhoto(idPhoto);
else
return null;
}
return null;
}
catch (MySqlException)
{
throw;
}
}
//Get liaison des projets pour un employé
public IList<LiaisonProjetEmploye> GetLiaison(string EmpID)
{
IList<LiaisonProjetEmploye> result = new List<LiaisonProjetEmploye>();
try
{
connexion = new MySqlConnexion();
StringBuilder buildReq = new StringBuilder();
if(EmpID != null)
{
buildReq.Append("SELECT liaisonprojetemployes.idEmploye, Projets.nom ,liaisonprojetemployes.idProjet FROM Projets ");
buildReq.Append(" LEFT JOIN liaisonprojetemployes ON liaisonprojetemployes.idProjet = Projets.idProjet ");
buildReq.Append(" LEFT JOIN Employes ON Employes.idEmploye = liaisonprojetemployes.idEmploye ");
buildReq.Append(" WHERE liaisonprojetemployes.idEmploye = '");
buildReq.Append(EmpID);
buildReq.Append("'");
DataSet dataset = connexion.Query(buildReq.ToString());
DataTable table = dataset.Tables[0];
if (table.Rows.Count != 0)
foreach (DataRow liaison in table.Rows)
{
result.Add(ConstructLiaison(liaison, EmpID));
}
}
else
{
buildReq.Append("SELECT nom FROM Projets WHERE etat = 'ECS'");
DataSet dataset = connexion.Query(buildReq.ToString());
DataTable table = dataset.Tables[0];
if (table.Rows.Count != 0)
foreach (DataRow liaison in table.Rows)
{
result.Add(ConstructLiaison(liaison, EmpID));
}
}
}
catch (MySqlException)
{
throw;
}
return result;
}
public string tauxHorraire(string id)
{
try
{
connexion = new MySqlConnexion();
string requete = "SELECT tauxHoraireNormal FROM detailfinancies WHERE idEmploye=" + id;
DataSet dataset = connexion.Query(requete);
DataTable table = dataset.Tables[0];
return table.Rows[0][0].ToString();
}
catch (MySqlException)
{
throw;
}
}
//Verification d'ajout
public bool ExisteEmploye(string Nom, string Prenom, string ID)
{
try
{
connexion = new MySqlConnexion();
StringBuilder buildReq = new StringBuilder();
buildReq.Append("SELECT idEmploye FROM Employes WHERE nom = '");
buildReq.Append(Nom);
buildReq.Append("' AND prenom = '");
buildReq.Append(Prenom);
buildReq.Append("'");
DataSet dataset = connexion.Query(buildReq.ToString());
DataTable table = dataset.Tables[0];
if (table.Rows.Count == 0)
{
return false;
}
else
{
if (ID == dataset.Tables[0].Rows[0][0].ToString())
return false;
else
return true;
}
}
catch (MySqlException)
{
throw;
}
}
public float CompteursProjets(string IDEmp, string nomProj)
{
try
{
float S_temps = 0;
connexion = new MySqlConnexion();
StringBuilder buildReq = new StringBuilder();
buildReq = new StringBuilder();
buildReq.Append("SELECT idProjet FROM Projets WHERE nom = '");
buildReq.Append(nomProj);
buildReq.Append("'");
DataSet dataset = connexion.Query(buildReq.ToString());
string IDProj = dataset.Tables[0].Rows[0][0].ToString();
buildReq = new StringBuilder();
buildReq.Append("SELECT SUM(TIMESTAMPDIFF( MINUTE, dateTimerStart, dateTimerEnd)/60) as temps FROM compteurstemps WHERE idEmploye = ");
buildReq.Append(IDEmp);
buildReq.Append(" AND idProjet = ");
buildReq.Append(IDProj);
dataset = connexion.Query(buildReq.ToString());
string temps = dataset.Tables[0].Rows[0][0].ToString();
if (temps.Length > 0 )
S_temps = (float)Math.Round(Convert.ToSingle(temps), 2);
// float temps = (float)S_temps;
return S_temps;
}
catch (MySqlException)
{
throw;
}
}
public bool periodeGenere(DateTime start, DateTime end)
{
try
{
connexion = new MySqlConnexion();
connexion.Query("UPDATE periodepaies SET aEteGenere=1 WHERE dateDebut='" + start + "' AND dateFin='" + end +"'");
return true;
}
catch (MySqlException)
{
return false;
}
}
public bool updatePay(Paie updatePaie)
{
try
{
connexion = new MySqlConnexion();
/*
* montantDueBrute, montantDueNet, nombreHeure,
* nombreHeureSupp, montantPrime, montantIndemnites,
* montantAllocations, montantCommissions, montantPourboire
*/
StringBuilder req = new StringBuilder();
req.Append("UPDATE Paies SET ");
req.Append(" montantDueBrute='"); req.Append(updatePaie.MontantBrute.ToString().Replace(",","."));
req.Append("', montantDueNet='"); req.Append(updatePaie.MontantNet.ToString().Replace(",", "."));
req.Append("', nombreHeure='"); req.Append(updatePaie.NombreHeure.ToString().Replace(",", "."));
req.Append("', nombreHeureSupp='"); req.Append(updatePaie.NombreHeureSupp.ToString().Replace(",", "."));
req.Append("', montantPrime='"); req.Append(updatePaie.MontantPrime.ToString().Replace(",", "."));
req.Append("', montantIndemnites='"); req.Append(updatePaie.MontantIndemnite.ToString().Replace(",", "."));
req.Append("', montantAllocations='"); req.Append(updatePaie.MontantAllocations.ToString().Replace(",", "."));
req.Append("', montantCommissions='"); req.Append(updatePaie.MontantCommission.ToString().Replace(",", "."));
req.Append("', montantPourboire='"); req.Append(updatePaie.MontantPourboire.ToString().Replace(",", "."));
req.Append("', updatedetail=NOW() ");
req.Append(" WHERE idPaies='");
req.Append(updatePaie.ID.ToString());
req.Append("' ");
connexion.Query(req.ToString());
return true;
}
catch (MySqlException)
{
return false;
}
}
public void UpdateProjetEmploye(ObservableCollection<LiaisonProjetEmploye> liaisonPE,string ID)
{
try
{
connexion = new MySqlConnexion();
StringBuilder buildReq;
//supprimer tout concerné à cet employé
buildReq = new StringBuilder();
buildReq.Append("DELETE FROM liaisonprojetemployes WHERE idEmploye = '");
buildReq.Append(ID);
buildReq.Append("'");
connexion.Query(buildReq.ToString());
foreach (LiaisonProjetEmploye l in liaisonPE)
{
//Trouve ID de projet
buildReq = new StringBuilder();
buildReq.Append("SELECT idProjet FROM Projets WHERE nom = '");
buildReq.Append(l.ProjNom);
buildReq.Append("'");
DataSet dataset = connexion.Query(buildReq.ToString());
string idProjet = dataset.Tables[0].Rows[0][0].ToString();
//Insérer dans le BD si un projet est occupé
if (l.Occupe == true)
{
//Insérer de nouveau
buildReq = new StringBuilder();
buildReq.Append("INSERT INTO liaisonprojetemployes (idEmploye, idProjet) VALUES ('");
buildReq.Append(ID);
buildReq.Append("' , '");
buildReq.Append(idProjet);
buildReq.Append("') ");
connexion.Query(buildReq.ToString());
}
//supprimer si un projet n'est pas occupé
else
{
buildReq = new StringBuilder();
buildReq.Append("DELETE FROM liaisonprojetemployes WHERE idEmploye = '");
buildReq.Append(ID);
buildReq.Append("' AND idProjet = '");
buildReq.Append(idProjet);
buildReq.Append("' ");
connexion.Query(buildReq.ToString());
}
}
}
catch (MySqlException)
{
throw;
}
}
public IList<Paie> RetrieveAll()
{
IList<Paie> result = new List<Paie>();
try
{
connexion = new MySqlConnexion();
string requete = "SELECT paies.idPaies, CONCAT(Employes.prenom,' ',Employes.nom) AS name,"
+ "CONCAT(DATE(periodepaies.dateDebut),' aux ', DATE(periodepaies.dateFin)) AS periodeP, dateGenerationRapport, montantDueBrute,"
+ "montantDueNet, nombreHeure, nombreHeureSupp, montantPrime, montantIndemnites,montantAllocations,"
+ "montantCommissions,montantPourboire,paies.idPeriode, paies.idEmploye,detailfinancies.tauxHoraireNormal, paies.updatedetail "
+ "FROM Paies "
+ "INNER JOIN Employes ON Employes.idEmploye=Paies.idEmploye "
+ "INNER JOIN detailfinancies ON Employes.idEmploye=detailfinancies.idEmploye "
+ "INNER JOIN periodepaies ON periodepaies.idPeriode=paies.idPeriode ORDER BY name ASC, dateGenerationRapport DESC";
DataSet dataset = connexion.Query(requete);
DataTable table = dataset.Tables[0];
foreach (DataRow paie in table.Rows)
{
result.Add(ConstructPaie(paie));
}
}
catch (MySqlException)
{
throw;
}
return result;
}
public string getDateEmbauche(string ID)
{
connexion = new MySqlConnexion();
StringBuilder buildReq = new StringBuilder();
buildReq.Append("SELECT DATE_FORMAT(dateEmbauche, '%Y-%m-%d') AS 'dateEmbauche' FROM detailfinancies WHERE idEmploye = ");
buildReq.Append(ID);
DataSet dataset = connexion.Query(buildReq.ToString());
string date = dataset.Tables[0].Rows[0][0].ToString();
if (date.Length > 0)
return date;
return null;
}
// Ici nous commençons la génération des différentes paie en fonction des temps.
public float RetrieveCompteurs(String id, DateTime periodeDebut, DateTime periodeFin)
{
try
{
float S_temps = 0;
connexion = new MySqlConnexion();
string requete = "SELECT SUM(TIMESTAMPDIFF( MINUTE, dateTimerStart, dateTimerEnd)/60) as temps FROM compteurstemps WHERE idEmploye = '" + id + "'"
+ " AND dateTimerStart >= '" + periodeDebut + "' AND "
+ " dateTimerEnd <= '" + periodeFin + "'";
DataSet dataset = connexion.Query(requete);
//dataSet.Tables[tableIndex].Rows[rowIndex][colIndex]
DataTable table = dataset.Tables[0];
string y = table.Rows[0][0].ToString();
if(y != "")
{
S_temps = Convert.ToSingle(table.Rows[0][0].ToString());
}
// float temps = (float)S_temps;
return S_temps;
}
catch (MySqlException)
{
throw;
}
}
public string getEmployeur(string ID)
{
connexion = new MySqlConnexion();
StringBuilder buildReq = new StringBuilder();
buildReq.Append("SELECT employeur FROM detailfinancies WHERE idEmploye = ");
buildReq.Append(ID);
DataSet dataset = connexion.Query(buildReq.ToString());
string employeur = dataset.Tables[0].Rows[0][0].ToString();
if (employeur.Length > 0 )
return employeur;
return null;
}
public DataTable PeriodeTemps()
{
try
{
connexion = new MySqlConnexion();
string requete = "SELECT dateDebut,dateFin FROM periodepaies WHERE aEteGenere IS FALSE ORDER BY dateFin LIMIT 1";
DataSet dataset = connexion.Query(requete);
DataTable table = dataset.Tables[0];
return table;
}
catch (MySqlException)
{
throw;
}
}