using SoftUni.Data;

using SoftUni.Models;

using System;

using System.Linq;

using System.Text;

​

namespace SoftUni

{

    public class StartUp

    {

        public static void Main(string[] args)

        {

            SoftUniContext context = new SoftUniContext();

​

            var result = GetEmployee147(context);

            Console.WriteLine(result);

        }

​

        //Problem 3

        public static string GetEmployeesFullInformation(SoftUniContext context)

        {

            StringBuilder sb = new StringBuilder();

​

            var employees = context

                .Employees

                .Select(e => new

                {

                    Id = e.EmployeeId,

                    result = String.Join(" ", e.FirstName, e.LastName, e.MiddleName, e.JobTitle),

                    Salary = e.Salary

                })

                .OrderBy(e => e.Id);

​

            foreach (var e in employees)

            {

                sb.AppendLine($"{e.result} {e.Salary:f2}");

            }

​

            return sb.ToString().TrimEnd();

        }

​

        //Problem 4

        public static string GetEmployeesWithSalaryOver50000(SoftUniContext context)

        {

            StringBuilder sb = new StringBuilder();

​

            var employees = context

                         .Employees

                         .Where(e => e.Salary > 50000)

                         .Select(e => new

                         {

                             Name = e.FirstName,

                             Salary = e.Salary

                         })                        

                         .OrderBy(e => e.Name);

​

            foreach (var emp in employees)

            {

                sb.AppendLine($"{emp.Name} - {emp.Salary:f2}");

            }

            return sb.ToString().TrimEnd();

        }

​

        //Problem 5

​

        public static string GetEmployeesFromResearchAndDevelopment(SoftUniContext context)

        {

            StringBuilder sb = new StringBuilder();

​

            var employees = context

                            .Employees

                            .Where(e => e.Department.Name == "Research and Development")

                            .OrderBy(e => e.Salary)

                            .ThenByDescending(e => e.FirstName)

                            .Select(e => new

                            {

                                result = String.Join(" ", e.FirstName, e.LastName),

                                DepartmentName = e.Department.Name,

                                e.Salary

                            });

​

            foreach (var emp in employees)

            {

                sb.AppendLine($"{emp.result} from {emp.DepartmentName} - ${emp.Salary:f2}");

            }

​

            return sb.ToString().TrimEnd();

        }

​

        //Problem 6

        public static string AddNewAddressToEmployee(SoftUniContext context)

        {

            StringBuilder sb = new StringBuilder();

​

            Address address = new Address()

                {

                AddressText = "Vitoshka 15",

                TownId = 4

                };

​

            Employee nakov = context

                .Employees

                .First(e => e.LastName == "Nakov");

​

            nakov.Address = address;

            context.SaveChanges();

​

            var result = context.Employees

                   .OrderByDescending(e => e.AddressId)

                   .Take(10)

                   .Select(e => new

                   {

                       e.Address.AddressText

                   })               

                   .ToList();

​

            foreach (var adresstext in result)

            {

                sb.AppendLine(adresstext.AddressText);

            }

            return sb.ToString().TrimEnd();

        }

        // Problem 7

​

        public static string GetEmployeesInPeriod(SoftUniContext context)

        {

            StringBuilder sb = new StringBuilder();

​

            var employees = context

                            .Employees

                            .Where(e => e.EmployeesProjects

                            .Any(ep => ep.Project.StartDate.Year >= 2001 && ep.Project.StartDate.Year <= 2003))

                            .Take(10)

                            .Select(e => new

                            {

                                e.FirstName,

                                e.LastName,

                                ManagerFirstName = e.Manager.FirstName,

                                ManagerLastName = e.Manager.LastName,

                                Projects = e.EmployeesProjects.Select(ep => new

                                {

                                    ep.Project.Name,

                                    ep.Project.StartDate,

                                    ep.Project.EndDate

                                })

                            });

​

            foreach (var emp in employees)

            {

                sb.AppendLine($"{emp.FirstName} {emp.LastName} - Manager: {emp.ManagerFirstName} {emp.ManagerLastName}");

​

                foreach (var ep in emp.Projects)

                {

                    var endDate = ep.EndDate?.ToString("M/d/yyyy h:mm:ss tt") ?? "not finished";

                    sb.AppendLine($"--{ep.Name} - {ep.StartDate.ToString("M/d/yyyy h:mm:ss tt")} - {endDate}");

                }

            }

​

            return sb.ToString().TrimEnd();                           

        }

        //Problem 8

        public static string GetAddressesByTown(SoftUniContext context)

        {

            StringBuilder sb = new StringBuilder();

​

            var employees = context

                            .Addresses

                            .OrderByDescending(a => a.Employees.Count)

                            .ThenBy(a => a.Town.Name)

                            .ThenBy(a => a.AddressText)

                            .Take(10)

                            .Select(a => new

                            {

                                a.AddressText,

                               town = a.Town.Name,

                               count = a.Employees.Count

                            });

            foreach (var address in employees)

            { 

                sb.AppendLine($"{address.AddressText}, {address.town} - {address.count} employees");

            }

​

            return sb.ToString().TrimEnd();

        }

        //Problem 9

        public static string GetEmployee147(SoftUniContext context)

        {

            StringBuilder sb = new StringBuilder();

​

            var employee = context

                           .Employees

                           .Where(e => e.EmployeeId == 147)

                           .Select(e => new

                           {

                               e.FirstName,

                               e.LastName,

                               e.JobTitle,

                               Projects = e.EmployeesProjects.Select(ep => ep.Project.Name.OrderBy(p => p).ToArray())                           

                           }) ;

​

            foreach (var emp in employee)

            {

                sb.AppendLine($"{emp.FirstName} {emp.LastName} - {emp.JobTitle}");

​

                foreach (var ep in emp.Projects)

                {                   

                    sb.Append(String.Join(Environment.NewLine, ep.ToString()));

                }

            }

​

            return sb.ToString().TrimEnd();

        }

​

    }

}

​