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