/// <summary>
/// Basic CRUD methods for status information. StatusDM is the model being used here.
/// </summary>
#region STATUS DAL METHODS
public static void CreateStatus(StatusDM _status, long empID)
{
try
{
//Creating a way of adding new user information to my database
using (SqlCommand cmd = new SqlCommand("CREATE_WORK_STATUS", SqlConnect.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Employee_ID", empID);
cmd.Parameters.AddWithValue("@Employee_Status", _status.EmployeeStatus);
cmd.Parameters.AddWithValue("@Hire_Date", _status.HireDate.ToShortDateString());
cmd.Parameters.AddWithValue("@Pay_Type", _status.PayType);
cmd.Parameters.AddWithValue("@Service_Length", _status.ServiceLength);
cmd.Parameters.AddWithValue("@Employment_Type", _status.EmploymentType);
cmd.Parameters.AddWithValue("@Office_Location", _status.OfficeLocation);
cmd.Parameters.AddWithValue("@Termination_Date", _status.TerminationDate.ToShortDateString());
SqlConnect.Connection.Open();
cmd.ExecuteNonQuery();
SqlConnect.Connection.Close();
}
}
catch (Exception e)
{
SqlConnect.Connection.Close();
throw (e);
}
}
public static void UpdateStatus(StatusDM _status)
{
try
{
using (SqlCommand cmd = new SqlCommand("UPDATE_WORK_STATUS", SqlConnect.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Employee_Status", _status.EmployeeStatus);
cmd.Parameters.AddWithValue("@Hire_Date", _status.HireDate.ToShortDateString());
cmd.Parameters.AddWithValue("@Pay_Type", _status.PayType);
cmd.Parameters.AddWithValue("@Service_Length", _status.ServiceLength);
cmd.Parameters.AddWithValue("@Employment_Type", _status.EmploymentType);
cmd.Parameters.AddWithValue("@Office_Location", _status.OfficeLocation);
cmd.Parameters.AddWithValue("@Termination_Date", _status.TerminationDate.ToShortDateString());
cmd.Parameters.AddWithValue("@Work_Status_ID", _status.StatusId);
SqlConnect.Connection.Open();
cmd.ExecuteNonQuery();
SqlConnect.Connection.Close();
}
}
catch (Exception e)
{
SqlConnect.Connection.Close();
throw (e);
}
}
public static void DeleteStatus(StatusDM _status)
{
try
{
using (SqlCommand cmd = new SqlCommand("DELETE_WORK_STATUS", SqlConnect.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Work_Status_ID", _status.StatusId);
SqlConnect.Connection.Open();
cmd.ExecuteNonQuery();
SqlConnect.Connection.Close();
}
}
catch (Exception ex)
{
SqlConnect.Connection.Close();
//Write to error log
throw ex;
}
}
public static List <StatusDM> ReadAllStatus()
{
List <StatusDM> customerList = new List <StatusDM>();
try
{
using (SqlCommand cmd = new SqlCommand("READ_WORK_STATUS", SqlConnect.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlConnect.Connection.Open();
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var _status = new StatusDM();
_status.StatusId = (Int64)reader["Work_Status_ID"];
_status.EmployeeStatus = (string)reader["Employee_Status"];
_status.HireDate = (DateTime)reader["Hire_Date"];
_status.PayType = (string)reader["Pay_Type"];
_status.ServiceLength = (string)reader["Service_Length"];
_status.EmploymentType = (string)reader["Employement_Type"];
_status.OfficeLocation = (string)reader["Office_Location"];
_status.TerminationDate = (DateTime)reader["Termination_Date"];
customerList.Add(_status);
}
}
}
SqlConnect.Connection.Close();
}
return(customerList);
}
catch (Exception ex)
{
SqlConnect.Connection.Close();
throw ex;
}
}
public static StatusDM ReadStatusById(string statusId)
{
StatusDM _status = new StatusDM();
try
{
using (SqlCommand cmd = new SqlCommand("READ_WORK_STATUS_BY_ID", SqlConnect.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlConnect.Connection.Open();
cmd.Parameters.AddWithValue("@Status_ID", statusId);
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
_status.StatusId = (Int64)reader["Work_Status_ID"];
_status.EmployeeStatus = (string)reader["Employee_Status"];
_status.HireDate = (DateTime)reader["Hire_Date"];
_status.PayType = (string)reader["Pay_Type"];
_status.ServiceLength = (string)reader["Service_Length"];
_status.EmploymentType = (string)reader["Employement_Type"];
_status.OfficeLocation = (string)reader["Office_Location"];
_status.TerminationDate = (DateTime)reader["Termination_Date"];
}
}
}
SqlConnect.Connection.Close();
}
return(_status);
}
catch (Exception ex)
{
SqlConnect.Connection.Close();
throw ex;
}
}
public List <EmployeeDM> ReadEmployees()
{
List <EmployeeDM> employeeList = new List <EmployeeDM>();
try
{
SqlConnect.Connection.Open();
using (SqlCommand cmd = new SqlCommand("READ_EMPLOYEES", SqlConnect.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
using (var reader = cmd.ExecuteReader())
{
if (!reader.HasRows)
{
return(employeeList);
}
while (reader.Read())
{
//Creating objects of the modals inside the loop so that
//the object can be used for new information every iteration of the loop.
#region Modal Objects
EmployeeDM employee = new EmployeeDM();
AddressDM address = new AddressDM();
EmployeeTimeDM EmployeeTime = new EmployeeTimeDM();
PositionsDM position = new PositionsDM();
StatusDM Status = new StatusDM();
#endregion
#region Pulling Employee Table Information
employee.EmployeeId = (Int64)reader["Employee_ID"];
employee.EmployeeNumber = (string)reader["Employee_Number"];
employee.EmployeeName = (string)reader["Employee_Name"];
employee.EmployeeFirstName = (string)reader["Employee_FirstName"];
employee.EmployeeMiddle = (string)reader["Employee_MiddleName"];
employee.EmployeeLastName = (string)reader["Employee_LastName"];
employee.Age = (int)reader["Age"];
employee.BirthDate = (DateTime)reader["Birth_Date"];
if (reader["Team_ID"] != DBNull.Value)
{
employee.TeamID = (Int64)reader["Team_ID"];
}
if (reader["Role_ID"] != DBNull.Value)
{
employee.RoleID = (Int64)reader["Role_ID"];
}
if (reader["Assignment_ID"] != DBNull.Value)
{
employee.AssignmentID = (Int64)reader["Assignment_ID"];
}
#endregion
//#region Pulling Address Table Information
//address.Address = (string)reader["Address"];
//address.City = (string)reader["City"];
//address.State = (string)reader["State"];
//address.Country = (string)reader["Country"];
//address.Zip_Code = (int)reader["Zip_Code"];
//address.Phone = (string)reader["Phone"];
//address.Email = (string)reader["Email"];
//#endregion
//#region Pulls Employee Time Table Information
//EmployeeTime.Other_Total = (decimal)reader["Other_Total"];
//EmployeeTime.Other_Available = (decimal)reader["Other_Available"];
//EmployeeTime.Other_Used = (decimal)reader["Other_Used"];
//EmployeeTime.Payed_Total = (decimal)reader["Payed_Total"];
//EmployeeTime.Payed_Used = (decimal)reader["Payed_Total"];
//#endregion
//#region Pulls Employee Work Status Information
//Status.EmployeeStatus = (string)reader["Employee_Status"];
//Status.HireDate = (DateTime)reader["Hire_Date"];
//Status.PayType = (string)reader["Pay_Type"];
//Status.ServiceLength = (string)reader["Service_Length"];
//Status.EmploymentType = (string)reader["Employment_Type"];
//Status.OfficeLocation = (string)reader["Office_Location"];
//if (reader["Termination_Date"] != DBNull.Value)
// Status.TerminationDate = (DateTime)reader["Termination_Date"];
//#endregion
////Adding the object properties to the employment object to be used together for the view modal
//employee.address = address; employee.EmployeeTime = EmployeeTime; employee.Status = Status;
employeeList.Add(employee);
}
}
cmd.Connection.Close();
}
return(employeeList);
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlConnect.Connection.Close();
}
}
