//method for adding a new employee to the database
public bool CreateNewEmployee(string firstName, string lastName, string email, string phoneNumber, decimal salary, int departmentId)
{
try
{
CompanyRecordsContext context = new CompanyRecordsContext();
Employee employee = new Employee()
{
FirstName = firstName,
LastName = lastName,
Email = email,
PhoneNumber = phoneNumber,
Salary = salary,
DepartmentId = departmentId
};
context.Employees.Add(employee);
context.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
//method returns a list of all assigned employees and their departments
public List <EmpAndDeptAnonType> AllEmployeesAndDepartmentNames()
{
CompanyRecordsContext context = new CompanyRecordsContext();
var result = context.Employees
.Include(e => e.Department)
.Select(e => new EmpAndDeptAnonType(e.FirstName, e.LastName, e.Department.DepartmentName))
.ToList();
return(result);
}
//method returns a list of names of departments with no employees
public List <string> AllDepartmentsWithNoEmployee()
{
CompanyRecordsContext context = new CompanyRecordsContext();
string sql = "Select Employees.Email, Employees.DepartmentId, Departments.DepartmentName from Employees Full Outer Join Departments On Employees.DepartmentId = Departments.DepartmentId where Employees.Email is NULL";
var result = context.Departments
.FromSqlRaw(sql)
.Select(e => e.DepartmentName)
.ToList();
return(result);
}
//method returns a list of all employees earning over an inputted threshold
public List <AnonType> EmployeesEarningOver(decimal salary)
{
CompanyRecordsContext context = new CompanyRecordsContext();
var result = context.Employees
.Include(e => e.Department)
.Where(e => e.Salary > salary)
.Select(e => new AnonType(e.FirstName, e.LastName, e.Department.DepartmentName, e.Salary))
.ToList();
return(result);
}
//method for deleting an existing department
public bool DeleteDepartment(string departmentName)
{
try
{
CompanyRecordsContext context = new CompanyRecordsContext();
var department = context.Departments.Where(d => d.DepartmentName == departmentName)
.ToArray()[0];
context.Departments.Remove(department);
context.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
//method for deleting an existing employee
public bool DeleteEmployee(string firstname, string email)
{
try
{
CompanyRecordsContext context = new CompanyRecordsContext();
var emp = context.Employees.Where(e => e.FirstName == firstname && e.Email == email)
.ToArray()[0];
context.Employees.Remove(emp);
context.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
//method for adding a new department to the database
public bool CreateNewDepartment(string departmentName)
{
try
{
CompanyRecordsContext context = new CompanyRecordsContext();
var department = new Department()
{
DepartmentName = departmentName
};
context.Departments.Add(department);
context.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
//method for editing an existing employee
public bool UpdateEmployee(string firstname, string lastname, string email, string phoneNumber, decimal salary, int departmentId)
{
try
{
CompanyRecordsContext context = new CompanyRecordsContext();
var emp = context.Employees.Where(e => e.Email == email)
.ToArray()[0];
if (firstname != "")
{
emp.FirstName = firstname;
}
if (lastname != "")
{
emp.LastName = lastname;
}
if (phoneNumber != "")
{
emp.PhoneNumber = phoneNumber;
}
if (salary != 0)
{
emp.Salary = salary;
}
if (departmentId != 0)
{
emp.DepartmentId = departmentId;
}
context.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
