/// <summary> /// Get the last day of the previous absence - timeslot in between absences /// </summary> /// <param name="employee">Employee (object) from the data selected by user</param> /// <param name="firstDay">first day selected by user</param> /// <returns>last day of the previous absence</returns> public static DateTime FirstDayIsAfterPreviousAbsence(Employee employee, DateTime firstDay ) { DateTime lastDayPreviousAbsence = new DateTime(); string req = "SELECT MAX(datefin) "; req += "FROM absence "; req += "WHERE idpersonnel = @idpersonnel "; req += "AND datefin < ( "; req += "SELECT MIN(datedebut) "; req += "FROM absence "; req += "WHERE idpersonnel = @idpersonnel "; req += "AND datedebut > @firstDayPicked)"; Dictionary <string, object> parameters = new Dictionary <string, object> { { "@idpersonnel", employee.IdEmployee }, { "@firstDayPicked", firstDay } }; ConnexionDataBase cursor = ConnexionDataBase.GetInstance(connexionString); cursor.ReqSelect(req, parameters); while (cursor.Read()) { lastDayPreviousAbsence = (DateTime)cursor.Field("max(datefin)"); } cursor.Close(); return(lastDayPreviousAbsence); }
/// <summary> /// Get the list of absences stored in the database /// </summary> /// <param name="employee">Employee (object) from the data selected by user</param> /// <returns>List of absences from an employee</returns> public static List <Absence> GetTheAbsences(Employee employee) { List <Absence> theAbsences = new List <Absence>(); string req = "select a.idpersonnel as idpersonnel, a.datedebut as datedebut, m.idmotif as idmotif, a.datefin as datefin, m.libelle as libelle "; req += "from absence a join motif m using (idmotif) "; req += "where a.idpersonnel = @idpersonnel "; req += "order by datedebut DESC"; Dictionary <string, object> parameters = new Dictionary <string, object> { { "@idpersonnel", employee.IdEmployee } }; ConnexionDataBase cursor = ConnexionDataBase.GetInstance(connexionString); cursor.ReqSelect(req, parameters); while (cursor.Read()) { Absence absence = new Absence(employee, (DateTime)cursor.Field("datedebut"), (DateTime)cursor.Field("datefin"), (int)cursor.Field("idmotif"), (string)cursor.Field("libelle") ); theAbsences.Add(absence); } cursor.Close(); return(theAbsences); }
/// <summary> /// Get the maximum employee ID from the employee in the database /// </summary> /// <returns>integer (maximum ID of the employee) in the table EMPLOYEE</returns> public static int GetMaxEmployeeID() { string req = "select max(idpersonnel) from personnel"; ConnexionDataBase cursor = ConnexionDataBase.GetInstance(connexionString); cursor.ReqSelect(req, null); int max = 0; if (cursor.Read()) { max = (int)cursor.Field("max(idpersonnel)"); } cursor.Close(); return(max); }
/// <summary> /// Method to control the authentification /// </summary> /// <param name="login">login of the connexion</param> /// <param name="password">password of the connexion</param> /// <returns>true if the connexion is open, false otherwise</returns> public static Boolean ControlAuthentification(string login, string password) { string req = "select * from responsable "; req += "where login=@login and pwd=SHA2(@pwd, 256);"; Dictionary <string, object> parameters = new Dictionary <string, object> { { "@login", login }, { "@pwd", password } }; ConnexionDataBase cursor = ConnexionDataBase.GetInstance(connexionString); cursor.ReqSelect(req, parameters); if (cursor.Read()) { cursor.Close(); return(true); } else { cursor.Close(); return(false); } }
/// <summary> /// Get the list of absence reasons stored in the database /// </summary> /// <returns>List of absence reasons</returns> public static List <Reason> GetTheReasons() { List <Reason> theReasons = new List <Reason>(); string req = "select * from motif "; ConnexionDataBase cursor = ConnexionDataBase.GetInstance(connexionString); cursor.ReqSelect(req, null); while (cursor.Read()) { Reason reason = new Reason((int)cursor.Field("idmotif"), (string)cursor.Field("libelle")); theReasons.Add(reason); } cursor.Close(); return(theReasons); }
/// <summary> /// Get the list of departments stored in the database /// </summary> /// <returns>List of departments</returns> public static List <Department> GetTheDepartments() { List <Department> theDepartments = new List <Department>(); string req = "select * from service order by nom;"; ConnexionDataBase cursor = ConnexionDataBase.GetInstance(connexionString); cursor.ReqSelect(req, null); while (cursor.Read()) { Department department = new Department((int)cursor.Field("idservice"), (string)cursor.Field("nom")); theDepartments.Add(department); } cursor.Close(); return(theDepartments); }
/// <summary> /// Get the last day corresponding to an absence /// </summary> /// <param name="employee">Employee (object) from the data selected by user</param> /// <returns>Last day corresponding to an absence</returns> public static DateTime AbsenceAtTheEndOfTheCalendar(Employee employee) { DateTime max = new DateTime(); string req = "SELECT MAX(datefin) "; req += "FROM absence "; req += "WHERE idpersonnel = @idpersonnel"; Dictionary <string, object> parameters = new Dictionary <string, object> { { "@idpersonnel", employee.IdEmployee }, }; ConnexionDataBase cursor = ConnexionDataBase.GetInstance(connexionString); cursor.ReqSelect(req, parameters); while (cursor.Read()) { max = (DateTime)cursor.Field("max(datefin)"); } cursor.Close(); return(max); }
/// <summary> /// Get the list of all employees /// </summary> /// <returns>List of employees in the database</returns> public static List <Employee> GetTheEmployees() { List <Employee> theEmployee = new List <Employee>(); string req = "SELECT p.idpersonnel as idpersonnel, p.nom as nom, p.prenom as prenom, p.tel as tel, p.mail as mail, s.idservice as idservice, s.nom as service "; req += "from personnel p join service s on (p.idservice = s.idservice) "; req += "order by nom, prenom;"; ConnexionDataBase cursor = ConnexionDataBase.GetInstance(connexionString); cursor.ReqSelect(req, null); while (cursor.Read()) { Employee employee = new Employee((int)cursor.Field("idpersonnel"), (string)cursor.Field("nom"), (string)cursor.Field("prenom"), (string)cursor.Field("tel"), (string)cursor.Field("mail"), (int)cursor.Field("idservice"), (string)cursor.Field("service")); theEmployee.Add(employee); } cursor.Close(); return(theEmployee); }