public static void AddProject(
string name,
string description,
DateTime startDate,
List<int> employeesId = null,
DateTime? endDate = null)
{
var db = new SoftUniEntities1();
var employees = new List<Employee>();
foreach (var employeeId in employeesId)
{
var employee = db.Employees
.Where(e => e.EmployeeID == employeeId)
.FirstOrDefault();
if (employee != null)
{
employees.Add(employee);
}
}
var project = new Project
{
Name = name,
Description = description,
StartDate = startDate,
EndDate = endDate,
Employees = employees
};
db.Projects.Add(project);
db.SaveChanges();
Console.WriteLine("Project added");
}
public static IList<Employee> EmployeesByDepartmentAndManager(string department, string manager)
{
var db = new SoftUniEntities1();
// var employees = new List<Employee>();
int departmentId = db.Departments
.Where(d => d.Name == department)
.Select(d => d.DepartmentID)
.FirstOrDefault();
int managerId = db.Employees
.Where(e => (e.FirstName + " " + e.LastName) == manager)
.Select(e => e.EmployeeID)
.FirstOrDefault();
var employees = db.Employees
.Where(e => e.DepartmentID == departmentId && e.ManagerID == managerId)
.ToList();
return employees;
}
public static IList<Employee> NativeSQLQuery()
{
var db = new SoftUniEntities1();
string nativeQuery = @"SELECT distinct
e.[EmployeeID],
e.[FirstName],
e.[LastName],
e.[MiddleName],
e.[JobTitle],
e.[DepartmentID],
e.[ManagerID],
e.[HireDate],
e.[Salary],
e.[AddressID]
FROM [SoftUni].[dbo].[Employees] e
join EmployeesProjects ep on ep.EmployeeID = e.EmployeeID
join Projects p on ep.ProjectID = p.ProjectID
where YEAR(p.StartDate) = '2002'";
var employees = db.Database.SqlQuery<Employee>(nativeQuery);
return employees.ToList();
}
public static IList<Employee> FindEmployeesByProject()
{
var db = new SoftUniEntities1();
var employees = new List<Employee>();
var projectsEmployees = db.Projects
.Where(p => (int)(p.StartDate).Year == 2002)
.Select(p => p.Employees)
.ToList();
foreach (var employeesPr in projectsEmployees)
{
foreach (var employee in employeesPr)
{
if (!employees.Contains(employee))
{
employees.Add(employee);
}
}
}
return employees.ToList();
}
public override string ToString()
{
var db = new SoftUniEntities1();
StringBuilder employee = new StringBuilder();
string department = db.Departments
.Where(d => d.DepartmentID == this.DepartmentID)
.Select(d => d.Name)
.FirstOrDefault();
string manager = db.Employees
.Where(e => e.EmployeeID == this.ManagerID)
.Select(e => e.FirstName + " " + e.LastName)
.FirstOrDefault();
employee.AppendFormat("Id : {0}, Name: {1}", this.EmployeeID, this.FirstName);
if (!string.IsNullOrEmpty(this.MiddleName))
{
employee.AppendFormat(" {0}", this.MiddleName);
}
employee.AppendFormat(" {0}, Job Title: {1}, Department: {2}",
this.LastName,
this.JobTitle,
department);
if (!string.IsNullOrEmpty(manager))
{
employee.AppendFormat(", Manager: {0}", manager);
}
employee.AppendFormat(", Hire Date: {0}, Salary: {1}", this.HireDate, this.Salary);
string address = "";
int? townId = null;
if (this.AddressID != null)
{
townId = db.Addresses
.Where(a => a.AddressID == this.AddressID)
.Select(a => a.TownID)
.FirstOrDefault();
address = db.Addresses
.Where(a => a.AddressID == this.AddressID)
.Select(a => a.AddressText)
.FirstOrDefault();
employee.AppendFormat(", Address: {0}", address);
if (townId != null)
{
string town = db.Towns
.Where(t => t.TownID == townId)
.Select(t => t.Name)
.FirstOrDefault();
employee.AppendFormat(", Town: {0}", town);
}
}
return employee.ToString();
}
public static void AddNewEmployee(
string firstName,
string lastName,
string jobTitle,
string department,
DateTime hireDate,
decimal salary,
string middleName = null,
string manager = null,
string address = null,
string town = null)
{
var db = new SoftUniEntities1();
int newEmployeeDepartmentId = db.Departments
.Where(d => d.Name == department)
.Select(d => d.DepartmentID)
.FirstOrDefault();
int? employeeManagerId = null;
int? employeeAddressId = null;
int? employeeTownId = null;
if (!string.IsNullOrEmpty(manager))
{
employeeManagerId = db.Employees
.Where(e => e.FirstName + " " + e.LastName == manager)
.Select(e => e.EmployeeID)
.FirstOrDefault();
}
if (!string.IsNullOrEmpty(address))
{
if (!string.IsNullOrEmpty(town))
{
employeeTownId = db.Towns
.Where(t => t.Name == town)
.Select(t => t.TownID)
.FirstOrDefault();
}
var employeeAddress = new Address
{
AddressText = address,
TownID = employeeTownId
};
db.Addresses.Add(employeeAddress);
db.SaveChanges();
employeeAddressId = db.Addresses
.Max(a => a.AddressID);
}
var newEmployee = new Employee
{
FirstName = firstName,
LastName = lastName,
MiddleName = middleName,
JobTitle = jobTitle,
DepartmentID = newEmployeeDepartmentId,
ManagerID = employeeManagerId,
HireDate = hireDate,
Salary = salary,
AddressID = employeeAddressId
};
db.Employees.Add(newEmployee);
db.SaveChanges();
Console.WriteLine("Employee added");
}
public static void ModifyEmployee(
int employeeId,
string firstName = null,
string lastName = null,
string jobTitle = null,
string department = null,
DateTime? hireDate = null,
decimal? salary = null,
string middleName = null,
string manager = null,
string address = null,
string town = null)
{
var db = new SoftUniEntities1();
var employee = db.Employees.Where(e => e.EmployeeID == employeeId).FirstOrDefault();
if (!string.IsNullOrEmpty(firstName))
{
employee.FirstName = firstName;
db.SaveChanges();
}
if (!string.IsNullOrEmpty(lastName))
{
employee.LastName = lastName;
db.SaveChanges();
}
if (!string.IsNullOrEmpty(jobTitle))
{
employee.JobTitle = jobTitle;
db.SaveChanges();
}
if (!string.IsNullOrEmpty(department))
{
int newDepartmentId = db.Departments
.Where(d => d.Name == department)
.Select(d => d.DepartmentID)
.FirstOrDefault();
employee.DepartmentID = newDepartmentId;
db.SaveChanges();
}
if (hireDate != null)
{
employee.HireDate = (DateTime)hireDate;
db.SaveChanges();
}
if (salary != null)
{
employee.Salary = (decimal)salary;
db.SaveChanges();
}
if (!string.IsNullOrEmpty(middleName))
{
employee.MiddleName = middleName;
db.SaveChanges();
}
if (!string.IsNullOrEmpty(manager))
{
int newManagerId = db.Employees
.Where(e => e.FirstName + " " + e.LastName == manager)
.Select(e => e.EmployeeID)
.FirstOrDefault();
employee.ManagerID = newManagerId;
db.SaveChanges();
}
if (!string.IsNullOrEmpty(address))
{
employee.Address.AddressText = address;
db.SaveChanges();
}
if (!string.IsNullOrEmpty(town))
{
int newTownId = db.Towns
.Where(t => t.Name == town)
.Select(t => t.TownID)
.FirstOrDefault();
employee.Address.TownID = newTownId;
db.SaveChanges();
}
Console.WriteLine("Employee modifyng");
}
public static void RemoveEmployee(int employeeId)
{
var db = new SoftUniEntities1();
var employee = db.Employees
.Where(e => e.EmployeeID == employeeId)
.FirstOrDefault();
var address = db.Addresses
.Where(a => a.AddressID == employee.AddressID)
.FirstOrDefault();
db.Addresses.Remove(address);
db.Employees.Remove(employee);
db.SaveChanges();
Console.WriteLine("Employee deleted");
}
static void Main(string[] args)
{
var db = new SoftUniEntities1();
// Problem 2. Employee DAO Class
// Adding Employee
// ModifyngEmployees.AddNewEmployee("Jivko", "Jivkov", "QA Developer", "Production",
// new DateTime(2015, 7, 15), 1200, null, "Svetlin Nakov", "Nadezhda 12", "Sofia");
// Modifyng Employee
// ModifyngEmployees.ModifyEmployee(294, "Marko", "Marchev", null, null, null, 1500, null, null, null, "Nevada");
// Remove Employee
// ModifyngEmployees.RemoveEmployee(294);
/* ----------------------------------------------------------*/
// Problem 3. Employees with Projects after 2002
/* var employees = EmployeeSearch.FindEmployeesByProject()
.OrderBy(e => e.EmployeeID)
.ToList();
int countEmployees = employees.Count();
Console.WriteLine(countEmployees);
foreach (var employee in employees)
{
Console.WriteLine(employee.EmployeeID + ": " + employee.FirstName + " " + employee.LastName);
} */
/* ----------------------------------------------------------*/
// Problem 4. Native SQL Query
/* var employeesBySqlNative = EmployeeSearch.NativeSQLQuery()
.OrderBy(e => e.EmployeeID)
.ToList();
int countEmployees = employeesBySqlNative.Count();
Console.WriteLine(countEmployees);
foreach (var employee in employeesBySqlNative)
{
Console.WriteLine(employee.EmployeeID + ": " + employee.FirstName + " " + employee.LastName);
} */
/* ----------------------------------------------------------*/
// Problem 5. Employees by Department and Manager
/* var employees = EmployeeSearch.EmployeesByDepartmentAndManager("Sales", "Brian Welcker");
int countEmployees = employees.Count();
Console.WriteLine(countEmployees);
foreach (var employee in employees)
{
Console.WriteLine(employee.EmployeeID + ": " + employee.FirstName + " " + employee.LastName);
} */
/* ----------------------------------------------------------*/
// Problem 7. Employees with Corresponding Projects
// var employee = db.Employees.Where(e => e.EmployeeID == 290).FirstOrDefault();
// Console.WriteLine(employee.ToString());
/* ----------------------------------------------------------*/
// Problem 8. Insert a New Project
//var employeesId = new List<int>
//{
// 7, 10
//};
//CreateProject.AddProject(
// "New Project",
// "New Project Description",
// new DateTime(2015, 7, 17),
// employeesId,
// null);
//var employees = db.Projects
// .Where(p => p.ProjectID == 128)
// .Select(p => p.Employees)
// .ToList();
//foreach (var employee in employees)
//{
// foreach (var emp in employee)
// {
// Console.WriteLine(emp.FirstName + emp.LastName);
// }
//}
/* ----------------------------------------------------------*/
// Problem 9. Call a Stored Procedure
var projects = db.usp_SelectEmployeeProjects("JoLynn Dobney").ToList();
foreach (var project in projects)
{
Console.WriteLine(project);
}
}
