public static void AddNewProject(
string projectName,
DateTime startDate,
DateTime? endDate = null,
string projectDescription = null,
params int[] employeesId
)
{
var project = new Project
{
Name = projectName,
Description = projectDescription,
StartDate = startDate,
EndDate = endDate,
};
using (var db = new SoftUniEntities())
{
foreach (var id in employeesId)
{
project.Employees.Add(db.Employees.Find(id));
}
db.Projects.Add(project);
db.SaveChanges();
}
}
public static IEnumerable FindAllEmplooyeesWithProjectsSql()
{
var query = @"SELECT *
FROM [SoftUni].[dbo].[Employees] e
JOIN EmployeesProjects ep
ON e.EmployeeID = ep.EmployeeID
JOIN Projects p
ON p.ProjectID = ep.ProjectID
WHERE YEAR(p.StartDate) >= 2002";
using (var db = new SoftUniEntities())
{
var emoloyeesWithProjects = db.Database.SqlQuery<Employee>(query).ToList();
var employees = new Dictionary<int, string>();
foreach (var employee in emoloyeesWithProjects)
{
if (!employees.ContainsKey(employee.EmployeeID))
{
employees.Add(employee.EmployeeID, String.Format("{0} {1}", employee.FirstName, employee.LastName));
}
}
return employees;
}
}
//06.Adding a new address and updating Employee
private static void AddAddress(SoftUniEntities context)
{
//var address = new Address()
//{
// AddressText = "Vitoshka 15",
// TownID = 4
//};
//context.Addresses.Add(address);
//context.SaveChanges();
//Employee emp = context.Employees.FirstOrDefault(e => e.LastName == "Nakov");
//emp.Address = address;
//context.SaveChanges();
var address = new Address()
{
AddressText = "Vitoshka 15",
TownID = 4
};
Employee emp = context.Employees.FirstOrDefault(e => e.LastName == "Nakov");
emp.Address = address;
context.SaveChanges();
var addresses = context.Employees.OrderByDescending(e => e.AddressID).Take(10).ToList();
foreach (var e in addresses)
{
Console.WriteLine(e.Address.AddressText);
}
}
private static void CallStoredProcedure(SoftUniEntities context)
{
string[] name = Console.ReadLine().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).ToArray();
SqlParameter firstName = new SqlParameter("@firstName", name[0]);
SqlParameter lastName = new SqlParameter("@lastName", name[1]);
//Procedure body
//CREATE PROCEDURE GetEmployeeProjects @firstName NVARCHAR(MAX),
//@lastName NVARCHAR(MAX)
//AS
//SELECT CONCAT(p.Name, ' - ', SUBSTRING(p.Description, 1, 30), '..., ', p.StartDate) FROM Employees AS e
//LEFT JOIN EmployeesProjects AS ep
//ON e.EmployeeID = ep.EmployeeID
//LEFT JOIN Projects AS p
//ON ep.ProjectID = p.ProjectID
//WHERE e.FirstName = @firstName and e.LastName = @lastName
var projects = context.Database.SqlQuery <string>($"execute GetEmployeeProjects @firstName,@lastName", firstName, lastName).ToList();
foreach (var projcet in projects)
{
Console.WriteLine(projcet);
}
}
public static DbRawSqlQuery<Project> GetAllProjectsForGivenEmployee(string firstName, string lastName)
{
var db = new SoftUniEntities();
var projects = db.Database.SqlQuery<Project>("upsGetAllProjectsForGivenEmployee @firstName = {0}, @lastName = {1}", firstName, lastName);
return projects;
}
public static void Add(Employee employee)
{
var context = new SoftUniEntities();
context.Employees.Add(employee);
context.SaveChanges();
}
public static void Delete(Employee employee)
{
var context = new SoftUniEntities();
context.Employees.Remove(employee);
context.SaveChanges();
}
public static void DeleteEmployee(int employeeId)
{
using (var db = new SoftUniEntities())
{
db.Employees.Remove(db.Employees.Find(employeeId));
db.SaveChanges();
}
}
public static void InsertEmployee(Employee employee)
{
using (var db = new SoftUniEntities())
{
db.Employees.Add(employee);
db.SaveChanges();
}
}
//13.Find Employee by First name starting with 'SA'
private static void FindEmployeeByFirstName(SoftUniEntities context)
{
var employees = context.Employees.Where(e => e.FirstName.ToLower().StartsWith("sa"));
foreach (var e in employees)
{
Console.WriteLine($"{e.FirstName} {e.LastName} - {e.JobTitle} - (${e.Salary:F4})");
}
}
public static void Modify(Employee employee)
{
var context = new SoftUniEntities();
context.Employees.Attach(employee);
context.Entry(employee).State = EntityState.Modified;
context.SaveChanges();
}
public static IEnumerable FindAllEmployeesWithProjectsAfter(int year)
{
using (var db = new SoftUniEntities())
{
var employees = db.Employees.Where(p => p.Projects.All(q => q.StartDate.Year >= year)).ToList();
return employees;
}
}
public static ICollection EmployeesByDepartmentAndManager(string departmentName, string managerFirstName, string managerLastName)
{
using (var db = new SoftUniEntities())
{
var employees = db.Employees.Where(p => p.Departments.All(q => q.Name == departmentName)
&& p.FirstName == managerFirstName && p.LastName == managerLastName).ToList();
return employees;
}
}
static void Main(string[] args)
{
var context = new SoftUniEntities();
//17.Call Stored Procedure
//CallStoredProcedure(context);
//18.Employees Max Salaries
MaxSalaries(context);
}
//09.Employee with id 147
private static void EmployeeWithId(SoftUniEntities context)
{
var e = context.Employees.Find(147);
Console.WriteLine($"{e.FirstName} {e.LastName} {e.JobTitle}");
foreach (var p in e.Projects.OrderBy(p => p.Name))
{
Console.WriteLine($"{p.Name}");
}
}
//04.Employees with salary over 50 000
private static void SalaryOver(SoftUniEntities context)
{
var employees = context.Employees
.Where(e => e.Salary > 50000)
.Select(c => c.FirstName);
foreach (var emp in employees)
{
Console.WriteLine(emp);
}
}
private static void MaxSalaries(SoftUniEntities context)
{
foreach (var dep in context.Departments)
{
decimal maxSalary = context.Employees.Where(e => e.Department.Name == dep.Name).ToList().Max(x => x.Salary);
if (maxSalary < 30000 || maxSalary > 70000)
{
Console.WriteLine($"{dep.Name} - {maxSalary:F2}");
}
}
}
//05.Employees from Seattle
private static void EmployeesFromSeattle(SoftUniEntities context)
{
var employees = context.Employees
.Where(e => e.Department.Name == "Research and Development")
.OrderBy(e => e.Salary)
.ThenByDescending(e => e.FirstName);
foreach (var emp in employees)
{
Console.WriteLine($"{emp.FirstName} {emp.LastName} from {emp.Department.Name} - ${emp.Salary:F2}");
}
}
//08.Address bt Town Name
private static void AddressByTown(SoftUniEntities context)
{
var addresses = context.Addresses
.OrderByDescending(a => a.Employees.Count)
.ThenBy(t => t.Town.Name)
.Take(10)
.ToList();
foreach (var a in addresses)
{
Console.WriteLine($"{a.AddressText}, {a.Town.Name} - {a.Employees.Count} employees");
}
}
//11.Find latest 10 projects
private static void FindLatestProjects(SoftUniEntities context)
{
CultureInfo ci = new CultureInfo("en-US");
Thread.CurrentThread.CurrentCulture = ci;
var projects = context.Projects
.OrderByDescending(p => p.StartDate)
.Take(10)
.OrderBy(p => p.Name);
foreach (var p in projects)
{
Console.WriteLine($"{p.Name} {p.Description} {p.StartDate:M/d/yyyy h:mm:ss tt} {p.EndDate:M/d/yyyy h:mm:ss tt}");
}
}
public void Initialize()
{
this.db = new SoftUniEntities();
this.employee = new Employee
{
EmployeeID = int.MaxValue,
FirstName = "Test",
MiddleName = "User",
LastName = "Name",
DepartmentID = 1,
JobTitle = "CCleaner",
HireDate = new DateTime(2000, 03, 07),
Salary = 2000.50m
};
}
//07.Find Employees in period
private static void EmployeesInPeriod(SoftUniEntities context)
{
var employyes = context.Employees
.Where(e => e.Projects.Count(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003) > 0)
.Take(30);
foreach (var e in employyes)
{
Console.WriteLine($"{e.FirstName} {e.LastName} {e.Employee1.FirstName}");
foreach (var p in e.Projects)
{
Console.WriteLine($"--{p.Name} {p.StartDate} {p.EndDate}");
}
}
}
//10.Departments with more than 5 employees
private static void DepartmentsWithMoreEmployees(SoftUniEntities context)
{
var department = context.Departments
.Where(d => d.Employees.Count > 5)
.OrderBy(c => c.Employees.Count).ToList();
foreach (var d in department)
{
Console.WriteLine($"{d.Name} {d.Employee.FirstName}");
foreach (var em in d.Employees)
{
Console.WriteLine($"{em.FirstName} {em.LastName} {em.JobTitle}");
}
}
}
//15.Delete project by ID
private static void DeletProject(SoftUniEntities context)
{
var projects = context.Projects.Find(2);
foreach (var emp in projects.Employees)
{
emp.Projects.Remove(projects);
}
context.Projects.Remove(projects);
context.SaveChanges();
var result = context.Projects.Take(10);
foreach (var pr in result)
{
Console.WriteLine(pr.Name);
}
}
//12.Increase Salary
private static void IncreaseSalary(SoftUniEntities context)
{
IEnumerable <Employee> employees = context.Employees
.Where(
e => e.Department.Name == "Engineering" ||
e.Department.Name == "Tool Design" ||
e.Department.Name == "Marketing" ||
e.Department.Name == "Information Services");
foreach (Employee e in employees)
{
e.Salary += e.Salary * 0.12M;
Console.WriteLine($"{e.FirstName} {e.LastName} (${e.Salary})");
}
context.SaveChanges();
}
public static void Main(string[] args)
{
var grContext = new GringottsContext();
var context = new SoftUniEntities();
//FullInformation(context);
//SalaryOver(context);
//EmployeesFromSeattle(context);
//AddAddress(context);
//EmployeesInPeriod(context);
//AddressByTown(context);
//EmployeeWithId(context);
//DepartmentsWithMoreEmployees(context);
//FindLatestProjects(context);
//IncreaseSalary(context);
//FindEmployeeByFirstName(context);
//FirstLetter(grContext);
//DeletProject(context);
}
//03.Employees Full Information
private static void FullInformation(SoftUniEntities context)
{
var employes = context.Employees
.Select(c => new
{
c.EmployeeID,
c.FirstName,
c.LastName,
c.MiddleName,
c.JobTitle,
c.Salary
})
.OrderBy(e => e.EmployeeID)
.ToList();
foreach (var emp in employes)
{
Console.WriteLine($"{emp.FirstName} {emp.LastName} {emp.MiddleName} {emp.JobTitle} {emp.Salary}");
}
}
public static void Main()
{
using (var db = new SoftUniEntities())
{
//var employee = db.Employees.Find(1);
//Employee.ModifyEmployee(employee, FirstName: "Gay");
// TEST
//Console.WriteLine(db.Employees.Find(1).FirstName);
// TEST
//Console.WriteLine(Employee.EntityObjectToString(employee));
//var emoloyeesWithProjects = Employee.FindAllEmployeesWithProjectsAfter(2002);
//var emoloyeesWithProjects = Employee.FindAllEmplooyeesWithProjectsSql();
//foreach (var emp in emoloyeesWithProjects)
//{
// TEST
//Console.WriteLine(emp);
//}
// TEST
//Employee.EmployeesByDepartmentAndManager("", "", "")
// TEST
//Project.AddNewProject("Nina", DateTime.Now, employeesId: new []{1, 2, 3});
var projects = Project.GetAllProjectsForGivenEmployee("Gay", "Gilbert");
foreach (Project project in projects)
{
Console.WriteLine("Name: {0}; Description: {1}; Start Date: {2}; End Date: {3}",
project.Name, project.Description ?? "None", project.StartDate, project.EndDate);
}
}
}
public static void ModifyEmployee(
Employee employee,
string FirstName = null,
string MiddleName = null,
string LastName = null,
string JobTitle = null,
int? DepartmentID = null,
int? ManagerID = null,
DateTime? HireDate = null,
decimal? Salary = null,
int? AddressID = null
)
{
using (var db = new SoftUniEntities())
{
var emp = db.Employees.Find(employee.EmployeeID);
if (FirstName != null)
{
emp.FirstName = FirstName;
}
if (MiddleName != null)
{
emp.MiddleName = MiddleName;
}
if (LastName != null)
{
emp.LastName = LastName;
}
if (JobTitle != null)
{
emp.JobTitle = JobTitle;
}
if (DepartmentID != null)
{
emp.DepartmentID = (int)DepartmentID;
}
if (ManagerID != null)
{
emp.ManagerID = ManagerID;
}
if (HireDate != null)
{
emp.HireDate = (DateTime)HireDate;
}
if (Salary != null)
{
emp.Salary = (decimal)Salary;
}
if (AddressID != null)
{
emp.AddressID = AddressID;
}
db.SaveChanges();
}
}
public static void Main()
{
// Problem 3
var context = new SoftUniEntities();
// Task 1
var employees = context.Employees
.Where(e => e.Projects.Count(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003) > 0)
.Select(e => new
{
e.FirstName,
e.LastName,
Manager = context.Employees.Where(m => m.EmployeeID == e.ManagerID).Select(m => m.FirstName + " " + m.LastName).FirstOrDefault(),
Projects = e.Projects.Select(p => new
{
p.Name,
p.StartDate,
p.EndDate
})
});
// Task 2
var addresses =
context.Addresses
.OrderByDescending(a => a.Employees.Count)
.ThenBy(a => a.Town.Name).Take(10)
.Select(a => new
{
a.AddressText,
Town = a.Town.Name,
EmployeeCount = a.Employees.Count
});
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2} employees", address.AddressText, address.Town, address.EmployeeCount);
}
// Task 3
var employee = context.Employees.Where(e => e.EmployeeID == 147).Select(e => new
{
e.FirstName,
e.LastName,
e.JobTitle,
Projects = e.Projects.OrderByDescending(p => p.Name).Select(p => p.Name)
}).FirstOrDefault();
Console.WriteLine("{0} {1} ({2}) - {3}", employee.FirstName, employee.LastName, employee.JobTitle, string.Join(", ", employee.Projects));
// Task 4
var departments = context.Departments
.Where(d => d.Employees.Count > 5)
.OrderBy(d => d.Employees.Count)
.Select(d => new
{
d.Name,
Manager = context.Employees.Where(m => m.EmployeeID == d.ManagerID).Select(m => m.FirstName + " " + m.LastName).FirstOrDefault(),
Employees = d.Employees.Select(e => new
{
e.FirstName,
e.LastName,
e.HireDate,
e.JobTitle
})
});
foreach (var department in departments)
{
Console.WriteLine("--{0} - Manager: {1}, Employees: {2}", department.Name, department.Manager, department.Employees.Count());
}
}
public static void Main()
{
// Problem 3
var context = new SoftUniEntities();
// Task 1
var employees = context.Employees
.Where(e => e.Projects.Count(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003) > 0)
.Select(e => new
{
e.FirstName,
e.LastName,
Manager = context.Employees.Where(m => m.EmployeeID == e.ManagerID).Select(m => m.FirstName + " " + m.LastName).FirstOrDefault(),
Projects = e.Projects.Select(p => new
{
p.Name,
p.StartDate,
p.EndDate
})
});
// Task 2
var addresses =
context.Addresses
.OrderByDescending(a => a.Employees.Count)
.ThenBy(a => a.Town.Name).Take(10)
.Select(a => new
{
a.AddressText,
Town = a.Town.Name,
EmployeeCount = a.Employees.Count
});
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2} employees", address.AddressText, address.Town, address.EmployeeCount);
}
// Task 3
var employee = context.Employees.Where(e => e.EmployeeID == 147).Select(e => new
{
e.FirstName,
e.LastName,
e.JobTitle,
Projects = e.Projects.OrderByDescending(p => p.Name).Select(p => p.Name)
}).FirstOrDefault();
Console.WriteLine("{0} {1} ({2}) - {3}", employee.FirstName, employee.LastName, employee.JobTitle, string.Join(", ", employee.Projects));
// Task 4
var departments = context.Departments
.Where(d => d.Employees.Count > 5)
.OrderBy(d => d.Employees.Count)
.Select(d => new
{
d.Name,
Manager = context.Employees.Where(m => m.EmployeeID == d.ManagerID).Select(m => m.FirstName + " " + m.LastName).FirstOrDefault(),
Employees = d.Employees.Select(e => new
{
e.FirstName,
e.LastName,
e.HireDate,
e.JobTitle
})
});
foreach (var department in departments)
{
Console.WriteLine("--{0} - Manager: {1}, Employees: {2}", department.Name, department.Manager, department.Employees.Count());
}
}
public static Employee FindByKey(object key)
{
var context = new SoftUniEntities();
return(context.Employees.Find(key));
}
