/// <summary>
/// Basic CRUD methods for EmployeeTime information. EmployeeTimeDM is the model being used here.
/// </summary>
#region TIME DAL METHODS
public static void CreateTime(EmployeeTimeDM time)
{
try
{
//Creating a way of adding new user information to my database
using (SqlCommand cmd = new SqlCommand("CREATE_EMPLOYEE_TIME", SqlConnect.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Other_Total", time.Other_Total);
cmd.Parameters.AddWithValue("@Other_Available", time.Other_Available);
cmd.Parameters.AddWithValue("@Other_Used", time.Other_Used);
cmd.Parameters.AddWithValue("@Payed_Total", time.Payed_Total);
cmd.Parameters.AddWithValue("@Payed_Available", time.Payed_Available);
cmd.Parameters.AddWithValue("@Payed_Used", time.Payed_Used);
SqlConnect.Connection.Open();
cmd.ExecuteNonQuery();
SqlConnect.Connection.Close();
}
}
catch (Exception e)
{
SqlConnect.Connection.Close();
throw e;
}
}
public static void UpdateTime(EmployeeTimeDM time)
{
try
{
using (SqlCommand cmd = new SqlCommand("UPDATE_EMPLOYEE_TIME", SqlConnect.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Time_ID", time.Time_ID);
cmd.Parameters.AddWithValue("@Other_Total", time.Other_Total);
cmd.Parameters.AddWithValue("@Other_Available", time.Other_Available);
cmd.Parameters.AddWithValue("@Other_Used", time.Other_Used);
cmd.Parameters.AddWithValue("@Payed_Total", time.Payed_Total);
cmd.Parameters.AddWithValue("@Payed_Available", time.Payed_Available);
cmd.Parameters.AddWithValue("@Payed_Used", time.Payed_Used);
SqlConnect.Connection.Open();
cmd.ExecuteNonQuery();
SqlConnect.Connection.Close();
}
}
catch (Exception e)
{
SqlConnect.Connection.Close();
throw (e);
}
}
public static void DeleteTime(EmployeeTimeDM time)
{
try
{
using (SqlCommand cmd = new SqlCommand("DELETE_ADDRESS", SqlConnect.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Time_ID", time.Time_ID);
SqlConnect.Connection.Open();
cmd.ExecuteNonQuery();
SqlConnect.Connection.Close();
}
}
catch (Exception ex)
{
SqlConnect.Connection.Close();
//Write to error log
throw ex;
}
}
public static List <EmployeeTimeDM> ReadAllTime()
{
List <EmployeeTimeDM> timeList = new List <EmployeeTimeDM>();
try
{
using (SqlCommand cmd = new SqlCommand("READ_ALL_TIME", SqlConnect.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlConnect.Connection.Open();
using (var reader = cmd.ExecuteReader())
{
if (!reader.HasRows)
{
return(timeList);
}
while (reader.Read())
{
var time = new EmployeeTimeDM
{
Time_ID = (Int64)reader["Time_ID"],
Other_Total = (decimal)reader["Other_Total"],
Other_Available = (decimal)reader["Other_Available"],
Other_Used = (decimal)reader["Other_Used"],
Payed_Total = (decimal)reader["Payed_Total"],
Payed_Available = (decimal)reader["Payed_Available"],
Payed_Used = (decimal)reader["Payed_Used"]
};
timeList.Add(time);
}
}
SqlConnect.Connection.Close();
}
return(timeList);
}
catch (Exception ex)
{
SqlConnect.Connection.Close();
throw ex;
}
}
public static EmployeeTimeDM ReadTimeByID(string timeId)
{
try
{
EmployeeTimeDM time = new EmployeeTimeDM();
using (SqlCommand cmd = new SqlCommand("READ_TIME_BY_ID", SqlConnect.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlConnect.Connection.Open();
cmd.Parameters.AddWithValue("@Time_ID", timeId);
using (var reader = cmd.ExecuteReader())
{
if (!reader.HasRows)
{
return(time);
}
while (reader.Read())
{
time.Time_ID = (Int64)reader["Time_ID"];
time.Other_Total = (decimal)reader["Other_Total"];
time.Other_Available = (decimal)reader["Other_Available"];
time.Other_Used = (decimal)reader["Other_Used"];
time.Payed_Total = (decimal)reader["Payed_Total"];
time.Payed_Available = (decimal)reader["Payed_Available"];
time.Payed_Used = (decimal)reader["Payed_Used"];
}
}
SqlConnect.Connection.Close();
}
return(time);
}
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();
}
}