// Add a new absence for a staff member
public static bool AddAbsence(Absence absence)
{
Database db = Database.Instance();
if (db.OpenConnection())
{
// Check if the user details currently exist
if (!AbsenceExists(absence))
{
// Build the query string
String absenceQuery;
String startDate = absence.StartDate.ToString("yyyy-MM-dd HH:mm:ss");
String endDate = absence.EndDate.ToString("yyyy-MM-dd HH:mm:ss");
// Create the query string to be inserted
absenceQuery = "INSERT INTO absence VALUES(NULL, " + absence.StaffID +
", " + (int)absence.AbsenceType + ", '" + startDate + "', '" + endDate + "');";
// Insert the entry into the database and return the new staffID
db.Insert(absenceQuery);
// Close the connection
db.CloseConnection();
return true;
}
db.CloseConnection();
}
return false;
}
// Add a new absence for a member of staff
public bool AddAbsence(int staffID, int absenceType, string startDate, string endDate)
{
Absence absence = new Absence();
absence.StaffID = staffID;
absence.AbsenceType = (AbsenceType)absenceType;
absence.StartDate = Convert.ToDateTime(startDate);
absence.EndDate = Convert.ToDateTime(endDate);
return BusinessMetaLayer.AddAbsence(absence);
}
public void TestRemoveAbsenceFail()
{
// Arrange
ManagementController mc = new ManagementController();
bool expected = false ;
Absence absence = new Absence();
absence.StaffID = 10;
absence.AbsenceType = AbsenceType.Holiday;
absence.StartDate = new DateTime(2013, 12, 25);
absence.EndDate = new DateTime(2013, 12, 26);
// Act
bool actual = mc.RemoveAbsence(absence);
// Assert
Assert.AreEqual(expected, actual);
}
// Checks if an absence already exists
public static bool AbsenceExists(Absence absence)
{
Database db = Database.Instance();
// Format the appointment start and end time for mysql
String sqlFormattedStartDate = absence.StartDate.ToString("yyyy-MM-dd HH:mm:ss");
String sqlFormattedEndDate = absence.EndDate.ToString("yyyy-MM-dd HH:mm:ss");
// Check the number of rows that are returned
int numRows = db.Count("SELECT COUNT(*) FROM absence WHERE staffID='" + absence.StaffID +
"'and startDate='" + sqlFormattedStartDate +
"'and endDate='" + sqlFormattedEndDate + "';");
// If an appointment exists and there's one row
if (numRows > 0)
{
// The appointment exists already
return true;
}
return false;
}
// Remove an absence for a member of staff
public bool RemoveAbsence(Absence absence)
{
return BusinessMetaLayer.RemoveAbsence(absence);
}
// Handle the user double clicking a row in the grid
private void absenceDataGrid_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
// Check that the click row isn't a column header
if (e.RowIndex > -1)
{
// Set the selected absence
_selectedRow = e.RowIndex;
_selectedAbsence = _staff.Absences[_selectedRow];
}
// Update controls
absenceTypeCmbBx.Enabled = false;
startDateTxt.ReadOnly = true;
endDateTxt.ReadOnly = true;
if (UserSession.Instance().CurrentUser.Permissions == PermissionsFlag.Management)
{
addAbsenceBtn.Enabled = true;
saveAbsenceBtn.Enabled = false;
}
// Update fields on the form
RefreshForm();
}
// Remove an absence
private void modifyAbsenceBtn_Click(object sender, EventArgs e)
{
if (_managementController.RemoveAbsence(_selectedAbsence))
{
_selectedAbsence = null;
MessageBox.Show("Absence Deleted");
}
RefreshForm();
}
// Returns all absences for one member of staff
public static List<Absence> GetStaffAbsences(int staffID)
{
Database db = Database.Instance();
List<Absence> _absences = new List<Absence>();
if (db.OpenConnection())
{
String query;
query = "SELECT * FROM absence " +
"WHERE absence.staffID=" + staffID + " ORDER BY absence.startDate DESC;";
DbDataReader dr = db.Select(query);
// Create the staff data
// Read the data and store them in the list
while (dr.Read())
{
Absence absence = new Absence();
absence.StaffID = dr.GetInt32(1);
absence.AbsenceType = (AbsenceType) dr.GetInt32(2);
absence.StartDate = dr.GetDateTime(3);
absence.EndDate = dr.GetDateTime(4);
_absences.Add(absence);
}
dr.Close();
db.CloseConnection();
}
return _absences;
}
// Remove an absence for a staff member
public static bool RemoveAbsence(Absence absence)
{
Database db = Database.Instance();
if (db.OpenConnection())
{
// Check if the user details currently exist
if (AbsenceExists(absence))
{
// Build the query string
String absenceQuery;
String startDate = absence.StartDate.ToString("yyyy-MM-dd HH:mm:ss");
String endDate = absence.EndDate.ToString("yyyy-MM-dd HH:mm:ss");
// Create the query string to be inserted
absenceQuery = "DELETE FROM absence WHERE staffID='" + absence.StaffID +
"'and startDate='" + startDate +
"'and endDate='" + endDate + "';";
// Insert the entry into the database and return the new staffID
db.Delete(absenceQuery);
// Close the connection
db.CloseConnection();
return true;
}
db.CloseConnection();
}
return false;
}
