/// <summary> /// sets the colour for the month based on the amount of people assigned /// </summary> /// <param name="activeMonth"></param> /// <returns></returns> public DateItem[] SetDaysForMonth(ActiveMonth activeMonth) { int days = DateTime.DaysInMonth(activeMonth.Year, activeMonth.Month); dateItems = new DateItem[days]; for (int i = 0; i < days; i++) { Color color = Color.Red; DateTime date = new DateTime(activeMonth.Year, activeMonth.Month, (i + 1)); dateItems[i] = new DateItem(); dateItems[i].Date = new DateTime(activeMonth.Year, activeMonth.Month, (i + 1)); foreach (Day day in this.days) { DateTime test = day.Date(); if (test == date) { //check how many people are assigned to each shift color = day.SetDayColor(); } } dateItems[i].BackColor1 = color; } return(dateItems); }
/// <summary> /// Unassignes an employee from a shift /// </summary> /// <param name="date"></param> /// <param name="shiftType"></param> /// <param name="employeeId"></param> /// <param name="activeMonth"></param> public void RemoveEmployee(DateTime date, int shiftType, int employeeId, ActiveMonth activeMonth) { MySqlConnection databaseConnection = new MySqlConnection(DatabaseInfo.connectionString); int shiftId = -1; foreach (Day day in days) { if (day.Date() == date) { foreach (Shift shift in day.GetShifts()) { if (shift.GetshiftType() == shiftType) { shiftId = shift.Id(); } } } } if (shiftId >= 0 && employeeId >= 0) { string insertQuery = $"DELETE FROM users_has_shift WHERE users_id = @userId AND shift_id = @shiftId;"; databaseConnection.Open(); MySqlCommand commandDatabase = new MySqlCommand(insertQuery, databaseConnection); commandDatabase.Parameters.AddWithValue("@userId", employeeId); commandDatabase.Parameters.AddWithValue("@shiftId", shiftId); commandDatabase.ExecuteNonQuery(); databaseConnection.Close(); } LoadShifts(activeMonth); }
/// <summary> /// gets all the shifts for the active month from the database and puts them into the "days" list /// </summary> /// <param name="activeMonth"></param> public void LoadShifts(ActiveMonth activeMonth) { MySqlConnection databaseConnection = new MySqlConnection(DatabaseInfo.connectionString); //DateTime date = DateTime.Now; DateTime firstDayOfMonth = new DateTime(activeMonth.Year, activeMonth.Month, 1); DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1); //select all the dates between the first day of the month and the last day of the month string query; query = "SELECT * FROM day WHERE "; query += $"CAST(day AS Date) >= CAST(N'{firstDayOfMonth.ToString("yyyy-MM-dd")}' AS Date) AND "; query += $"CAST(day AS Date) <= CAST(N'{lastDayOfMonth.ToString("yyyy-MM-dd")}' AS Date);"; MySqlCommand commandDatabase = new MySqlCommand(query, databaseConnection); commandDatabase.CommandTimeout = DatabaseInfo.connectionTimeout; MySqlDataReader reader; try { databaseConnection.Open(); reader = commandDatabase.ExecuteReader(); if (reader.HasRows)// check if any rows are found { days.Clear(); while (reader.Read()) //read each individual row { int id = Convert.ToInt32(reader["id"]); DateTime day = Convert.ToDateTime(reader["day"]); days.Add(new Day(id, day)); } databaseConnection.Close(); } else { databaseConnection.Close(); } databaseConnection.Close(); } catch (MySqlException ex) { throw ex; } catch (Exception ex) { databaseConnection.Close(); } foreach (Day day in days) { day.LoadShifts(); } }
/// <summary> /// assigns a new employee to a shift /// </summary> /// <param name="date"></param> /// <param name="shift"></param> /// <param name="person"></param> /// <param name="activeMonth"></param> public void SetShift(DateTime date, int shift, int person, ActiveMonth activeMonth) { long dayId = -1; long shiftId = -1; //check if there already employees assigned to the day foreach (Day day in days) { if (day.Date() == date) { dayId = day.Id(); } } //if no employees are assigned create a new day in the database if (dayId < 0) { //insert new day and shifts string insertQuery = $"INSERT INTO day(day) VALUES(CAST(N'{date.ToString("yyyy-MM-dd")}' AS Date));"; databaseConnection.Open(); MySqlCommand commandDatabase = new MySqlCommand(insertQuery, databaseConnection); //commandDatabase.Parameters.AddWithValue("@day", date.ToString("yyyy-MM-dd")); commandDatabase.ExecuteNonQuery(); dayId = commandDatabase.LastInsertedId; //if (result != null) { dayId = Convert.ToInt32(result); } databaseConnection.Close(); for (int i = 0; i < 3; i++) { //sets the default max assigned employees for a shift int max = 5; if (i == 2) { max = 2; } //sets the default min assigned employees for a shift int min = 2; //inserts a new shift and gets the id insertQuery = $"INSERT INTO shift(shifttype, min, max, day_id) VALUES(@shifttype, @min, @max, @dayId); select last_insert_id();"; databaseConnection.Open(); commandDatabase = new MySqlCommand(insertQuery, databaseConnection); commandDatabase.Parameters.AddWithValue("@shifttype", i); commandDatabase.Parameters.AddWithValue("@min", min); commandDatabase.Parameters.AddWithValue("@max", max); commandDatabase.Parameters.AddWithValue("@dayId", dayId); commandDatabase.ExecuteNonQuery(); //gets the id of the shift where the employee was inserted if (shift == i) { shiftId = commandDatabase.LastInsertedId; } databaseConnection.Close(); } } else { //selects an existing shift and gets the shift id string sql = $"SELECT id FROM shift WHERE day_id = {dayId} AND shifttype = {shift};"; MySqlCommand cmd = new MySqlCommand(sql, databaseConnection); databaseConnection.Open(); Object result = cmd.ExecuteScalar(); if (result != null) { shiftId = Convert.ToInt32(result); } databaseConnection.Close(); } //inserts the user into a shift based on shiftid and user id if (shiftId >= 0 && person >= 0) { string insertQuery = $"INSERT INTO users_has_shift(users_id, shift_id) VALUES(@userId, @shiftId); select last_insert_id();"; databaseConnection.Open(); MySqlCommand commandDatabase = new MySqlCommand(insertQuery, databaseConnection); commandDatabase.Parameters.AddWithValue("@userId", person); commandDatabase.Parameters.AddWithValue("@shiftId", shiftId); commandDatabase.ExecuteNonQuery(); databaseConnection.Close(); } try { LoadShifts(activeMonth); } catch (MySqlException ex) { throw ex; } }