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