public Employee GetEmpById(int id)
{
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
var cmd = new SqlCommand("select e.FirstName, e.LastName,e.HireDate, (m.FirstName + ' ' + m.LastName) as [ManagerName], Departments.DepartmentName, Departments.DepartmentID, e.LocationID, e.[Status],e.EmpID from Employee e inner join Departments on e.DepartmentID = Departments.DepartmentID inner join Employee m on m.EmpID = e.ManagerID inner join Location l on e.LocationID = l.LocationID WHERE e.EmpID = @empId order by e.LastName", cn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@empId";
param.Value = id;
cmd.Parameters.Add(param);
cn.Open();
var temp = new Employee();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
temp.Department = new Departments();
temp.FirstName = dr.GetString(0);
temp.LastName = dr.GetString(1);
temp.HireDate = dr["HireDate"] is DBNull ? DateTime.Now : dr.GetDateTime(2);
temp.ManagerName = dr.GetString(3);
temp.Department.DepartmentName = dr.GetString(4);
temp.Department.DepartmentID = dr.GetInt32(5);
temp.LocationID = dr.GetInt32(6);
temp.Status = dr["Status"] is DBNull ? String.Empty : dr.GetString(7);
temp.EmpID = dr.GetInt32(8);
}
}
return temp;
}
}
public List<Employee> GetEmpByDptID(int id)
{
var emps = new List<Employee>();
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
var cmd = new SqlCommand("select e.FirstName, e.LastName,e.HireDate, (m.FirstName + ' ' + m.LastName) as [ManagerName], Departments.DepartmentName, Departments.DepartmentID, l.[state], e.[Status] from Employee e inner join Departments on e.DepartmentID = Departments.DepartmentID inner join Employee m on m.EmpID = e.ManagerID inner join Location l on e.LocationID = l.LocationID WHERE e.DepartmentID = "+ id+" order by e.LastName", cn);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var temp = new Employee();
temp.Department = new Departments();
temp.FirstName = dr.GetString(0);
temp.LastName = dr.GetString(1);
temp.HireDate = dr["HireDate"] is DBNull ? DateTime.Now : dr.GetDateTime(2);
temp.ManagerName = dr.GetString(3);
temp.Department.DepartmentName = dr.GetString(4);
temp.Department.DepartmentID = dr.GetInt32(5);
temp.State = dr.GetString(6);
temp.Status = dr["Status"] is DBNull ? String.Empty : dr.GetString(7);
emps.Add(temp);
}
}
}
return emps;
}
public Employee SelectOneEmployee(int empId)
{
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
var p = new DynamicParameters();
p.Add("EmployeeID", empId);
Employee employee = new Employee();
employee = cn.Query<Employee>("GetEmployee", p, commandType: CommandType.StoredProcedure).ToList().FirstOrDefault();
return employee;
}
}
public void Put(Employee employee)
{
var ops = new EmployeeDirectoryOperations();
ops.UpdateEmployee(employee);
}
public Employee SelectOneEmployee(int empId)
{
var repo = new TimeSheetRepository();
Employee employee = new Employee();
employee = repo.SelectOneEmployee(empId);
var timeEntries = repo.GetAllTimeEntriesForOneEmp(empId);
decimal? totalHours = 0;
foreach (var hours in timeEntries)
{
totalHours += hours.HoursWorked;
}
employee.TotalHoursWorked = totalHours;
return employee;
}
public void UpdateEmployee(Employee employee)
{
var repo = new EmployeeDirectoryRepository();
repo.UpdateEmp(employee);
}
public void UpdateEmp(Employee updatedEmployee)
{
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
var p = new DynamicParameters();
p.Add("@firstName", updatedEmployee.FirstName);
p.Add("@lastName", updatedEmployee.LastName);
p.Add("@locationId", updatedEmployee.LocationID);
p.Add("@managerId", 11);
p.Add("@status", updatedEmployee.Status);
p.Add("@deptId", updatedEmployee.Department.DepartmentID);
p.Add("@empId", updatedEmployee.EmpID);
p.Add("@hireDate", updatedEmployee.HireDate);
cn.Query("UpdateEmployee", p, commandType: CommandType.StoredProcedure);
}
}