/// <summary> /// Adds a new position /// </summary> /// <param name="position">The position object to be added</param> /// <returns>True if insert is successful, false otherwise</returns> public bool AddPosition(Position position) { int positionResult = 0; string insertStatement = "INSERT INTO position([position_title],[isActive], [position_description]) " + "VALUES(@position_title, @isActive, @position_description)"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection)) { insertCommand.Transaction = transaction; insertCommand.Parameters.AddWithValue("@position_title", position.positionTitle); insertCommand.Parameters.AddWithValue("@isActive", position.isActive); insertCommand.Parameters.AddWithValue("@position_description", position.positionDescription); positionResult = insertCommand.ExecuteNonQuery(); } transaction.Commit(); } catch { transaction.Rollback(); } } return(positionResult >= 1 ? true : false); }
/// <summary> /// returns true if zipcode exists /// </summary> /// <param name="zipcode"></param> /// <returns></returns> public bool ZipCodeExisits(string zipcode) { bool doesExist = false; int count = 0; string sqlStatement = $" SELECT *" + $" FROM zipcode" + $" WHERE zipcode = @zipcode"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(sqlStatement, connection)) { command.Parameters.AddWithValue("@zipcode", zipcode); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { count++; } } if (count > 0) { doesExist = true; } } connection.Close(); } return(doesExist); }
/// <summary> /// Returns a list of Statuses /// </summary> /// <returns></returns> public List <Status> GetStatuses() { List <Status> statuses = new List <Status>(); string selectStatement = "SELECT id, status_description, isAbleToWork, status_title " + "FROM status"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand sqlCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = sqlCommand.ExecuteReader()) { while (reader.Read()) { Status status = new Status { Id = (int)reader["id"], StatusDescription = reader["status_description"].ToString(), IsAbleToWork = (bool)reader["isAbleToWork"], StatusTitle = reader["status_title"].ToString() }; statuses.Add(status); } } } } return(statuses); }
private bool DoesUserNameExists(string username) { bool doesExist = false; string sqlStatement = "Select count(*) FROM person Where username = @username"; try { using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand checkUsername = new SqlCommand(sqlStatement, connection)) { checkUsername.Parameters.AddWithValue("@username", username); Int32 count = 0; count = (Int32)checkUsername.ExecuteScalar(); if (count > 0) { doesExist = true; } } connection.Close(); } } catch (SqlException ex) { throw ex; } return(doesExist); }
/// <summary> /// Saves the accepted data with the accepted sql statement /// </summary> /// <typeparam name="model"></typeparam> /// <param name="sql"></param> /// <param name="data"></param> /// <returns></returns> private int SaveData <model>(string sql, model data) { using (IDbConnection cnn = ScheduleBuilder_DB_Connection.GetConnection()) { return(Convert.ToInt32(cnn.ExecuteScalar(sql, data))); } }
/// <summary> /// Get only the positions assigned to a specific person /// </summary> /// <param name="personID">The id of the person</param> /// <returns>A list of positions</returns> public List <Position> GetPersonPositions(int personID) { SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection(); List <Position> personPepositionList = new List <Position>(); string selectStatement = "SELECT p.id, p.position_title, p.isActive, p.position_description " + "FROM position p " + "JOIN assignedPosition AS ap ON p.id = ap.positionId " + "WHERE ap.personId = @personID"; using (connection) { connection.Open(); SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@personID", personID); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { Position position = new Position(); position.positionID = int.Parse(reader["id"].ToString()); position.positionTitle = reader["position_title"].ToString(); position.isActive = (bool)reader["isActive"]; position.positionDescription = reader["position_description"].ToString(); personPepositionList.Add(position); } } return(personPepositionList); }
/// <summary> /// Allows users to edit a previously created person /// </summary> /// <param name="editPerson"></param> public void EditPerson(Person editPerson) { string update = @"UPDATE dbo.person SET last_name = @lastName , first_name = @firstName , ssn = @ssn , gender = @gender , street_address = @streetAddress , phone = @phone , zipcode = @zipcode , username = @username , email = @email , statusId = @statusId , roleId = @roleId WHERE id = @id AND password = @password AND date_of_birth = @dateOfBirth"; int count = 0; try { using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand updateCommand = new SqlCommand(update, connection)) { updateCommand.Parameters.AddWithValue("@lastName", editPerson.LastName); updateCommand.Parameters.AddWithValue("@firstName", editPerson.FirstName); updateCommand.Parameters.AddWithValue("@dateOfBirth", editPerson.DateOfBirth); updateCommand.Parameters.AddWithValue("@roleId", editPerson.RoleId); if (String.IsNullOrEmpty(editPerson.Ssn)) { updateCommand.Parameters.AddWithValue("@ssn", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@ssn", editPerson.Ssn); } updateCommand.Parameters.AddWithValue("@gender", editPerson.Gender); updateCommand.Parameters.AddWithValue("@streetAddress", editPerson.StreetAddress); updateCommand.Parameters.AddWithValue("@phone", editPerson.Phone); updateCommand.Parameters.AddWithValue("@zipcode", editPerson.Zipcode); updateCommand.Parameters.AddWithValue("@username", editPerson.Username); updateCommand.Parameters.AddWithValue("@email", editPerson.Email); updateCommand.Parameters.AddWithValue("@id", editPerson.Id); updateCommand.Parameters.AddWithValue("@statusId", editPerson.StatusId); updateCommand.Parameters.AddWithValue("@password", editPerson.Password); count = updateCommand.ExecuteNonQuery(); } connection.Close(); } } catch (SqlException ex) { throw ex; } }
/// <summary> /// Returns a list of All Tasks /// </summary> /// <returns>A list of task objects</returns> public List <Task> GetAllTasks() { SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection(); List <Task> taskList = new List <Task>(); string selectStatement = "SELECT t.id, t.task_title, t.isActive, t.task_description, pt.roleId, p.position_title " + "FROM task t " + "RIGHT JOIN position_tasks AS pt ON t.id = pt.taskId " + "JOIN position AS p ON pt.roleId = p.id"; using (connection) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Task task = new Task(); task.TaskId = int.Parse(reader["id"].ToString()); task.Task_title = reader["task_title"].ToString(); task.IsActive = (bool)reader["isActive"]; task.Task_description = reader["task_description"].ToString(); task.PositionID = int.Parse(reader["roleId"].ToString()); task.PositionName = reader["position_title"].ToString(); taskList.Add(task); } } } } return(taskList); }
/// <summary> /// Retrieves all roles that a user can be /// </summary> /// <returns>List of roles</returns> public List <Role> GetRoles() { List <Role> roles = new List <Role>(); string selectStatement = "SELECT id, roleTitle, roleDescription " + "FROM role"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand sqlCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = sqlCommand.ExecuteReader()) { while (reader.Read()) { Role role = new Role { Id = (int)reader["id"], RoleTitle = (string)reader["roleTitle"], RoleDescription = (string)reader["roleDescription"] }; roles.Add(role); } } return(roles); } } }
/// <summary> /// Verifies a person's login information and retrieves full name and role /// </summary> /// <param name="username">As a string</param> /// <param name="password">As a string</param> /// <returns></returns> public DataTable GetLogin(string username, string password) { DataTable dt = new DataTable(); HashingService hash = new HashingService(); string selectStatement = "SELECT p.id, p.username, p.password, (p.first_name + ' ' + p.last_name) AS 'name', r.roleTitle " + "FROM person p " + "JOIN role r ON p.roleID = r.id " + "WHERE username = @username AND password = @password"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); SqlCommand sqlCommand = new SqlCommand(selectStatement, connection); sqlCommand.Parameters.AddWithValue("@username", username); sqlCommand.Parameters.AddWithValue("@password", hash.PasswordHashing(password)); SqlDataReader reader = sqlCommand.ExecuteReader(); dt.Columns.Add("id", typeof(int)); dt.Columns.Add("username", typeof(string)); dt.Columns.Add("password", typeof(string)); dt.Columns.Add("name", typeof(string)); dt.Columns.Add("roleTitle", typeof(string)); dt.Load(reader); } return(dt); }
/// <summary> /// /// </summary> /// <typeparam name="model"></typeparam> /// <param name="sql"></param> /// <returns></returns> public List <model> LoadData <model>(string sql) { using (IDbConnection cnn = ScheduleBuilder_DB_Connection.GetConnection()) { return(cnn.Query <model>(sql).AsList()); } }
/// <summary> /// Adds the accepted LunchBreakEnd value and attaches it to the shiftHourID /// </summary> /// <param name="scheduleShiftID">The shift hours id</param> /// <param name="now">The current date time for the clock out lunch end</param> public void ClockLunchEnd(int scheduleShiftID, DateTime now) { string insert = $" UPDATE ShiftHours" + $" SET acutalLunchBreakEnd = @lunchEndTime" + $" WHERE id = @shiftId"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); try { using (SqlCommand command = new SqlCommand(insert, connection)) { command.Parameters.AddWithValue("@shiftId", scheduleShiftID); command.Parameters.AddWithValue("@lunchEndTime", now); command.ExecuteNonQuery(); } connection.Close(); } catch (SqlException ex) { throw ex; } } }
/// <summary> /// Adds the accepted clockIn value and attaches it to the shiftHourID /// </summary> /// <param name="shiftHoursId">Shift hours id</param> /// <param name="clockIn">The date time of the clock in</param> public void ClockUserIn(int shiftHoursId, DateTime clockIn) { string insert = $" UPDATE ShiftHours" + $" SET actualStartTime = @clockInTime" + $" WHERE id = @shiftId"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); try { using (SqlCommand command = new SqlCommand(insert, connection)) { command.Parameters.AddWithValue("@shiftId", shiftHoursId); command.Parameters.AddWithValue("@clockInTime", clockIn); command.ExecuteNonQuery(); } connection.Close(); Shift shift = this.GetAllShifts(" WHERE sh.id = " + shiftHoursId.ToString()).FirstOrDefault <Shift>(); if (IsEmployeeLate(shift)) { this.AlertManagmentOfLateEmployee(shift); this.AlertUserOfLateClockin(shift); } } catch (SqlException ex) { throw ex; } } }
/// <summary> /// Gets a persons ID by their email /// </summary> /// <param name="email"></param> /// <returns></returns> public int GetIDByEmail(string email) { Person person = new Person(); string selectStatement = "SELECT id, email " + "FROM person " + "WHERE email = @email"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand sqlCommand = new SqlCommand(selectStatement, connection)) { sqlCommand.Parameters.AddWithValue("@email", email); using (SqlDataReader reader = sqlCommand.ExecuteReader()) { while (reader.Read()) { person.Id = (int)reader["id"]; person.Email = reader["email"].ToString(); } } } return(person.Id); } }
/// <summary> /// Updates a persons password with a new password based on email input in controller /// </summary> /// <param name="person"></param> /// <param name="newPassword"></param> public void UpdatePasswordOnly(Person person, string newPassword) { HashingService hashed = new HashingService(); string update = @"UPDATE dbo.person SET password = @password WHERE id = @id"; int count = 0; try { using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand updateCommand = new SqlCommand(update, connection)) { updateCommand.Parameters.AddWithValue("@password", hashed.PasswordHashing(newPassword)); updateCommand.Parameters.AddWithValue("@id", person.Id); count = updateCommand.ExecuteNonQuery(); } connection.Close(); } } catch (SqlException ex) { throw ex; } }
/// <summary> /// Connects a person to an assigned position /// </summary> /// <param name="person">The person's id as an integer</param> /// <param name="position">The position id as an integer</param> /// <returns>True if insert is successful, false otherwise</returns> public bool AddPositionToPerson(int person, int position) { int positionResult = 0; string insertStatement = "INSERT INTO assignedPosition([personId],[positionId]) " + "VALUES(@personId, @positionId)"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection)) { insertCommand.Transaction = transaction; insertCommand.Parameters.AddWithValue("@personId", person); insertCommand.Parameters.AddWithValue("@positionId", position); positionResult = insertCommand.ExecuteNonQuery(); } transaction.Commit(); } catch { transaction.Rollback(); } } return(positionResult >= 1 ? true : false); }
/// <summary> /// Gets a role's id by its title /// </summary> /// <param name="id"></param> /// <returns></returns> public int GetRoleIdByTitle(string roleTitle) { Role role = new Role(); string selectStatement = "SELECT id, roleTitle " + "FROM role " + "WHERE roleTitle = @roleTitle"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand sqlCommand = new SqlCommand(selectStatement, connection)) { sqlCommand.Parameters.AddWithValue("@roleTitle", roleTitle); using (SqlDataReader reader = sqlCommand.ExecuteReader()) { while (reader.Read()) { role.Id = (int)reader["id"]; role.RoleTitle = reader["roleTitle"].ToString(); } } } return(role.Id); } }
/// <summary> /// Finds PositionIDByUnavailable /// </summary> /// <returns>The id of the 'Unavailable' position</returns> public int FindPositionIDByUnavailable() { SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection(); string selectStatement = "SELECT id " + "FROM position " + "WHERE position_title = 'Unavailable'"; Position position = new Position(); using (connection) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { position.positionID = int.Parse(reader["id"].ToString()); } } } } return(position.positionID); }
/// <summary> /// Gets a status by its id /// </summary> /// <param name="id"></param> /// <returns></returns> public Status GetStatusByID(int id) { Status status = new Status(); string selectStatement = "SELECT id, status_description, isAbleToWork, status_title " + "FROM status " + "WHERE id = @id"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand sqlCommand = new SqlCommand(selectStatement, connection)) { sqlCommand.Parameters.AddWithValue("@id", id); using (SqlDataReader reader = sqlCommand.ExecuteReader()) { while (reader.Read()) { status.Id = (int)reader["id"]; status.StatusDescription = reader["status_description"].ToString(); status.IsAbleToWork = (bool)reader["isAbleToWork"]; status.StatusTitle = reader["status_title"].ToString(); } } } return(status); } }
/// <summary> /// Returns the NearestShift to the datetime now /// </summary> /// <param name="whereClause">Where clause to except date range</param> /// <returns>A shift object</returns> public Shift GetNearestShift(string whereClause) { SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection(); Shift shift = new Shift(); string query = $"SELECT TOP 1 s.id" + $", s.scheduleShiftId" + $", s.personId" + $", s.positionId" + $", sh.scheduledStartTime" + $", sh.scheduledEndTime" + $", sh.scheduledLunchBreakStartTime" + $", sh.scheduledLunchBreakEndTime" + $", sh.actualStartTime" + $", sh.actualEndTime" + $", sh.actualLunchBreakStart" + $", sh.acutalLunchBreakEnd" + $", p.first_name" + $", p.last_name" + $", ps.position_title " + $" FROM shift AS s " + $" JOIN shiftHours AS sh ON s.scheduleShiftId = sh.id " + $" JOIN person AS p ON s.personId = p.id " + $" JOIN position AS ps ON s.positionId = ps.id " + whereClause + $" ORDER BY ABS(DATEDIFF(MINUTE, scheduledStartTime, GETDATE()))"; using (connection) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(query, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { shift.shiftID = int.Parse(reader["id"].ToString()); shift.scheduleShiftID = int.Parse(reader["scheduleShiftId"].ToString()); shift.positionID = int.Parse(reader["positionId"].ToString()); shift.scheduledStartTime = (DateTime)reader["scheduledStartTime"]; shift.scheduledEndTime = (DateTime)reader["scheduledEndTime"]; shift.scheduledLunchBreakStart = reader["scheduledLunchBreakStartTime"] as DateTime?; shift.scheduledLunchBreakEnd = reader["scheduledLunchBreakEndTime"] as DateTime?; shift.actualStartTime = reader["actualStartTime"] as DateTime?; shift.actualEndTime = reader["actualEndTime"] as DateTime?; shift.actualLunchBreakStart = reader["actualLunchBreakStart"] as DateTime?; shift.actualLunchBreakEnd = reader["acutalLunchBreakEnd"] as DateTime?; shift.positionName = reader["position_title"].ToString(); shift.personLastName = reader["last_name"].ToString(); shift.personFirstName = reader["first_name"].ToString(); shift.personID = int.Parse(reader["personId"].ToString()); } } } } return(shift); }
/// <summary> /// Delete a shift and associated data /// </summary> /// <param name="shift">The shift object to be deleted</param> /// <returns>A boolean value based on success or failure of the delete</returns> public bool DeleteShift(Shift shift) { int shiftHoursResult = 0; int shiftResult = 0; int taskResult = 0; string deleteShiftHoursStatement = "DELETE FROM shiftHours " + "WHERE id = @id"; string deleteShiftStatement = "DELETE FROM shift " + "WHERE id = @id"; string deleteTaskStatement = "DELETE FROM assignedTask " + "WHERE shiftId = @shiftId"; //delete shift and shift hour entries using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { using (SqlCommand deleteTaskCommand = new SqlCommand(deleteTaskStatement, connection)) { deleteTaskCommand.Transaction = transaction; deleteTaskCommand.Parameters.AddWithValue("@shiftId", shift.shiftID); taskResult = deleteTaskCommand.ExecuteNonQuery(); } using (SqlCommand deleteShiftCommand = new SqlCommand(deleteShiftStatement, connection)) { deleteShiftCommand.Transaction = transaction; deleteShiftCommand.Parameters.AddWithValue("@id", shift.shiftID); shiftResult = deleteShiftCommand.ExecuteNonQuery(); } using (SqlCommand deleteShiftHoursCommand = new SqlCommand(deleteShiftHoursStatement, connection)) { deleteShiftHoursCommand.Transaction = transaction; deleteShiftHoursCommand.Parameters.AddWithValue("@id", shift.scheduleShiftID); shiftHoursResult = deleteShiftHoursCommand.ExecuteNonQuery(); } transaction.Commit(); } catch { transaction.Rollback(); } } return(shiftHoursResult == 1 && shiftResult >= 1 ? true : false); }
/// <summary> /// Get all shifts in the database /// </summary> /// <returns>A list of shift objects</returns> public List <Shift> GetAllShifts() { SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection(); List <Shift> shiftList = new List <Shift>(); string selectStatement = "SELECT s.id, s.scheduleShiftId, s.personId, s.positionId, s.notes, sh.scheduledStartTime, sh.scheduledEndTime, " + "sh.scheduledLunchBreakStartTime, sh.scheduledLunchBreakEndTime, sh.actualStartTime, sh.actualEndTime, sh.actualLunchBreakStart, " + "sh.acutalLunchBreakEnd, p.first_name, p.last_name, ps.position_title, STUFF((SELECT '; ' + CONVERT(varchar, a.taskId) " + "FROM assignedTask as a " + "WHERE a.shiftId = s.id " + "FOR XML PATH('')), 1, 1, '') [taskList] " + "FROM shift AS s " + "JOIN shiftHours AS sh ON s.scheduleShiftId = sh.id " + "JOIN person AS p ON s.personId = p.id " + "JOIN position AS ps ON s.positionId = ps.id"; using (connection) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Shift shift = new Shift(); shift.shiftID = int.Parse(reader["id"].ToString()); shift.scheduleShiftID = int.Parse(reader["scheduleShiftId"].ToString()); shift.personID = int.Parse(reader["personId"].ToString()); shift.positionID = int.Parse(reader["positionId"].ToString()); shift.Notes = reader["notes"].ToString(); shift.scheduledStartTime = (DateTime)reader["scheduledStartTime"]; shift.scheduledEndTime = (DateTime)reader["scheduledEndTime"]; shift.scheduledLunchBreakStart = reader["scheduledLunchBreakStartTime"] as DateTime?; shift.scheduledLunchBreakEnd = reader["scheduledLunchBreakEndTime"] as DateTime?; shift.actualStartTime = reader["actualStartTime"] as DateTime?; shift.actualEndTime = reader["actualEndTime"] as DateTime?; shift.actualLunchBreakStart = reader["actualLunchBreakStart"] as DateTime?; shift.actualLunchBreakEnd = reader["acutalLunchBreakEnd"] as DateTime?; shift.positionName = reader["position_title"].ToString(); shift.personLastName = reader["last_name"].ToString(); shift.personFirstName = reader["first_name"].ToString(); string taskIdList = reader["taskList"].ToString(); shift.TaskIdList = taskIdList == string.Empty ? new List <int>() : taskIdList.Split(';').Select(int.Parse).ToList(); shiftList.Add(shift); } } } } return(shiftList); }
/// <summary> /// Gets a person by their ID /// </summary> /// <param name="id"></param> /// <returns></returns> public Person GetPersonByID(int id) { Person person = new Person(); string selectStatement = "SELECT id" + ", last_name" + ", first_name" + ", date_of_birth" + ", ssn" + ", gender" + ", street_address" + ", phone" + ", zipcode" + ", username" + ", password" + ", roleId" + ", statusId" + ", email " + "FROM person " + "WHERE id = @id"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand sqlCommand = new SqlCommand(selectStatement, connection)) { sqlCommand.Parameters.AddWithValue("@id", id); using (SqlDataReader reader = sqlCommand.ExecuteReader()) { while (reader.Read()) { person.Id = (int)reader["id"]; person.LastName = reader["last_name"].ToString(); person.FirstName = reader["first_name"].ToString(); person.DateOfBirth = (DateTime)reader["date_of_birth"]; person.Ssn = reader["ssn"].ToString(); person.Gender = reader["gender"].ToString(); person.StreetAddress = reader["street_address"].ToString(); person.Phone = reader["phone"].ToString(); person.Zipcode = reader["zipcode"].ToString(); person.Username = reader["username"].ToString(); person.Password = reader["password"].ToString(); person.RoleId = (int)reader["roleId"]; person.StatusId = (int)reader["statusId"]; person.Email = reader["email"].ToString(); } } } return(person); } }
/// <summary> /// Update an existing task with a position /// </summary> /// <param name="task">A task object</param> /// <returns>True if successful, false otherwise</returns> public bool UpdatePositionTask(Task task) { string updateStatement = "UPDATE task " + "SET [task_title] = @task_title, " + "[isActive] = @isActive, " + "[task_description] = @task_description " + "WHERE id = @id"; string updateptStatement = "UPDATE position_tasks " + "SET [roleId] = @roleId " + "WHERE taskId = @taskId "; int taskResult = 0; int positionTaskResult = 0; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { using (SqlCommand updateCommand = new SqlCommand(updateStatement, connection)) { updateCommand.Transaction = transaction; updateCommand.Parameters.AddWithValue("@id", task.TaskId); updateCommand.Parameters.AddWithValue("@task_title", task.Task_title); updateCommand.Parameters.AddWithValue("@isActive", task.IsActive); updateCommand.Parameters.AddWithValue("@task_description", task.Task_description); taskResult = updateCommand.ExecuteNonQuery(); } using (SqlCommand updateCommand = new SqlCommand(updateptStatement, connection)) { updateCommand.Transaction = transaction; updateCommand.Parameters.AddWithValue("@taskId", task.TaskId); updateCommand.Parameters.AddWithValue("@roleId", task.PositionID); positionTaskResult = updateCommand.ExecuteNonQuery(); } transaction.Commit(); } catch { transaction.Rollback(); } } return(taskResult >= 1 && positionTaskResult >= 1 ? true : false); }
/// <summary> /// Allows Edits the shift from the orignal shift /// accepts two shifts to compare the changes /// </summary> /// <param name="shift">The shift object</param> /// <param name="orignalShift">The original shift object</param> /// <returns></returns> public bool EditShift(Shift shift, Shift orignalShift) { int shiftHoursResult = 0; string updateShiftHoursStatement = @"UPDATE shiftHours SET scheduledStartTime = @scheduledStartTime , scheduledEndTime = @scheduledEndTime , scheduledLunchBreakStartTime = @scheduledLunchBreakStartTime , scheduledLunchBreakEndTime = @scheduledLunchBreakEndTime , actualStartTime = @actualStartTime , actualEndTime = @actualEndTime , actualLunchBreakStart = @actualLunchBreakStart , acutalLunchBreakEnd = @actualLunchBreakEnd WHERE id = @id"; try { using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand updateShiftHoursCommand = new SqlCommand(updateShiftHoursStatement, connection)) { updateShiftHoursCommand.Parameters.AddWithValue("@scheduledStartTime", shift.scheduledStartTime); updateShiftHoursCommand.Parameters.AddWithValue("@scheduledEndTime", shift.scheduledEndTime); updateShiftHoursCommand.Parameters.AddWithValue("@scheduledLunchBreakStartTime", ((object)shift.scheduledLunchBreakStart) ?? DBNull.Value); updateShiftHoursCommand.Parameters.AddWithValue("@scheduledLunchBreakEndTime", ((object)shift.scheduledLunchBreakEnd) ?? DBNull.Value); updateShiftHoursCommand.Parameters.AddWithValue("@actualStartTime", ((object)shift.actualStartTime) ?? DBNull.Value); updateShiftHoursCommand.Parameters.AddWithValue("@actualEndTime", ((object)shift.actualEndTime) ?? DBNull.Value); updateShiftHoursCommand.Parameters.AddWithValue("@actualLunchBreakStart", ((object)shift.actualLunchBreakStart) ?? DBNull.Value); updateShiftHoursCommand.Parameters.AddWithValue("@actualLunchBreakEnd", ((object)shift.actualLunchBreakEnd) ?? DBNull.Value); updateShiftHoursCommand.Parameters.AddWithValue("@id", shift.scheduleShiftID); shiftHoursResult = updateShiftHoursCommand.ExecuteNonQuery(); } connection.Close(); } } catch (SqlException ex) { throw ex; } bool successfulChange = (shiftHoursResult == 1 ? true : false); if (successfulChange) { this.AlertTimeCardEdit(shift, orignalShift); } return(successfulChange); }
/// <summary> /// Sets the accepted person's status as seperated /// </summary> /// <param name="seperatePerson"></param> /// <returns></returns> public Person SeperateEmployee(Person seperatePerson) { string update = @"UPDATE dbo.person Set statusId = 4 WHERE id = @id"; try { using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand updateCommand = new SqlCommand(update, connection)) { updateCommand.Parameters.AddWithValue("@lastName", seperatePerson.LastName); updateCommand.Parameters.AddWithValue("@firstName", seperatePerson.FirstName); updateCommand.Parameters.AddWithValue("@dateOfBirth", seperatePerson.DateOfBirth); if (seperatePerson.Ssn == "") { updateCommand.Parameters.AddWithValue("@ssn", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@ssn", seperatePerson.Ssn); } updateCommand.Parameters.AddWithValue("@gender", seperatePerson.Gender); updateCommand.Parameters.AddWithValue("@streetAddress", seperatePerson.StreetAddress); updateCommand.Parameters.AddWithValue("@phone", seperatePerson.Phone); updateCommand.Parameters.AddWithValue("@zipcode", seperatePerson.Zipcode); updateCommand.Parameters.AddWithValue("@username", seperatePerson.Username); updateCommand.Parameters.AddWithValue("@email", seperatePerson.Email); updateCommand.Parameters.AddWithValue("@id", seperatePerson.Id); updateCommand.Parameters.AddWithValue("@roleId", seperatePerson.RoleId); updateCommand.Parameters.AddWithValue("@statusId", 4); updateCommand.Parameters.AddWithValue("@password", seperatePerson.Password); updateCommand.ExecuteNonQuery(); } connection.Close(); } } catch (SqlException ex) { throw ex; } return(seperatePerson); }
/// <summary> /// Returns list equal to the accepted where statement /// </summary> /// <returns>A list of shift objects</returns> public List <Shift> GetAllShifts(string whereClause) { SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection(); List <Shift> shiftList = new List <Shift>(); string selectStatement = "SELECT s.id, s.scheduleShiftId, s.personId, s.positionId, s.notes, sh.scheduledStartTime, sh.scheduledEndTime, " + "sh.scheduledLunchBreakStartTime, sh.scheduledLunchBreakEndTime, sh.actualStartTime, sh.actualEndTime, sh.actualLunchBreakStart, " + "sh.acutalLunchBreakEnd, p.first_name, p.last_name, ps.position_title " + "FROM shift AS s " + "JOIN shiftHours AS sh ON s.scheduleShiftId = sh.id " + "JOIN person AS p ON s.personId = p.id " + "JOIN position AS ps ON s.positionId = ps.id " + whereClause; using (connection) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Shift shift = new Shift(); shift.shiftID = int.Parse(reader["id"].ToString()); shift.scheduleShiftID = int.Parse(reader["scheduleShiftId"].ToString()); shift.personID = int.Parse(reader["personId"].ToString()); shift.positionID = int.Parse(reader["positionId"].ToString()); shift.Notes = reader["notes"].ToString(); shift.scheduledStartTime = (DateTime)reader["scheduledStartTime"]; shift.scheduledEndTime = (DateTime)reader["scheduledEndTime"]; shift.scheduledLunchBreakStart = reader["scheduledLunchBreakStartTime"] as DateTime?; shift.scheduledLunchBreakEnd = reader["scheduledLunchBreakEndTime"] as DateTime?; shift.actualStartTime = reader["actualStartTime"] as DateTime?; shift.actualEndTime = reader["actualEndTime"] as DateTime?; shift.actualLunchBreakStart = reader["actualLunchBreakStart"] as DateTime?; shift.actualLunchBreakEnd = reader["acutalLunchBreakEnd"] as DateTime?; shift.positionName = reader["position_title"].ToString(); shift.personLastName = reader["last_name"].ToString(); shift.personFirstName = reader["first_name"].ToString(); shiftList.Add(shift); } } } } return(shiftList); }
/// <summary> /// Adds a new task with a position /// </summary> /// <param name="task">A task object</param> /// <returns>true if successful, false if failure</returns> public bool AddPositionTask(Task task) { int taskresult = 0; int positiontaskresult = 0; string insertStatement = "INSERT INTO task([task_title],[isActive], [task_description]) " + "VALUES(@task_title, @isActive, @task_description);SELECT SCOPE_IDENTITY();"; string insertptStatement = "INSERT INTO position_tasks([taskId],[roleId]) " + "VALUES(@taskId, @roleId)"; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { int pk = -1; connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { using (SqlCommand insertCommand = new SqlCommand(insertStatement, connection)) { insertCommand.Transaction = transaction; insertCommand.Parameters.AddWithValue("@task_title", task.Task_title); insertCommand.Parameters.AddWithValue("@isActive", task.IsActive); insertCommand.Parameters.AddWithValue("@task_description", task.Task_description); pk = Convert.ToInt32(insertCommand.ExecuteScalar()); taskresult = 1; } using (SqlCommand insertCommand = new SqlCommand(insertptStatement, connection)) { insertCommand.Transaction = transaction; insertCommand.Parameters.AddWithValue("@taskId", pk); insertCommand.Parameters.AddWithValue("@roleId", task.PositionID); positiontaskresult = insertCommand.ExecuteNonQuery(); } transaction.Commit(); } catch (Exception e) { MessageBox.Show(e.ToString()); transaction.Rollback(); } } return(taskresult == 1 && positiontaskresult >= 1 ? true : false); }
/// <summary> /// Returns all ActivePositions /// </summary> /// <returns>A list of position objects</returns> public List <Position> GetAllActivePositions() { SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection(); List <Position> positionList = new List <Position>(); //May need to add in the position tasks to this later string selectStatement = "SELECT p.id, p.position_title, p.isActive, p.position_description " + "FROM position as p " + "WHERE p.isActive = 1"; using (connection) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { using (SqlDataReader reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Position position = new Position(); position.positionID = int.Parse(reader["id"].ToString()); position.positionTitle = reader["position_title"].ToString(); position.positionDescription = reader["position_description"].ToString(); if (position.positionTitle == "Unavailable") { //skips unavailable position } else { positionList.Add(position); } } } } } return(positionList); }
/// <summary> /// this method returns all employees /// </summary> /// <returns></returns> public List <Person> GetDesiredPersons(string whereClause) { List <Person> persons = new List <Person>(); string desiredEmployees = this.selectedPersons + whereClause; using (SqlConnection connection = ScheduleBuilder_DB_Connection.GetConnection()) { connection.Open(); using (SqlCommand command = new SqlCommand(desiredEmployees, connection)) { using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Person person = new Person(); person.Id = (int)reader["id"]; person.LastName = reader["last_name"].ToString(); person.FirstName = reader["first_name"].ToString(); person.DateOfBirth = (DateTime)reader["date_of_birth"]; person.Ssn = reader["ssn"].ToString(); person.Gender = reader["gender"].ToString(); person.StreetAddress = reader["street_address"].ToString(); person.Phone = reader["phone"].ToString(); person.Zipcode = reader["zipcode"].ToString(); person.Username = reader["username"].ToString(); person.Password = reader["password"].ToString(); person.RoleId = (int)reader["roleId"]; person.StatusId = (int)reader["statusId"]; person.Email = reader["email"].ToString(); persons.Add(person); } } return(persons); } } }