/// <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);
}
