/// <summary>
/// This method runs on launch. It checks if the CSV files have been loaded into the DB
/// </summary>
public static void Initialize()
{
using (RegionsEmployeesContext context = new RegionsEmployeesContext())
{
if (!context.Employees.Any() && !context.Regions.Any())
{
GetRegionsFromCSV();
GetEmployeesFromCSV();
}
}
}
/// <summary>
/// Searches the DB for a region with the supplied region ID
/// </summary>
/// <param name="regionId"> Takes a region ID for searching </param>
/// <returns></returns>
public static Region GetRegion(int regionId)
{
using (var context = new RegionsEmployeesContext())
{
var query = from r in context.Regions
where r.RegionId == regionId
select r;
Region region = query.FirstOrDefault();
return(region);
}
}
/// <summary>
/// Creates a Region object and inserts it into the DB
/// </summary>
/// <param name="regionId"></param>
/// <param name="parentId"></param>
/// <param name="name"></param>
public static void InsertRegion(int regionId, int parentId, string name)
{
var region = new Region
{
Name = name,
RegionId = regionId,
ParentRegionId = parentId
};
using (var context = new RegionsEmployeesContext())
{
context.Regions.Add(region);
context.SaveChanges();
}
}
/// <summary>
/// Creates an Employee object and inserts it into the database
/// </summary>
/// <param name="regionId"></param>
/// <param name="name"></param>
/// <param name="surname"></param>
public static void InsertEmployee(int regionId, string name, string surname)
{
var employee = new Employee
{
Name = name,
Surname = surname,
Region = GetRegion(regionId)
};
using (var context = new RegionsEmployeesContext())
{
context.Employees.Add(employee);
context.Regions.Attach(employee.Region);
context.SaveChanges();
}
}
public IEnumerable <RegionModel> Get()
{
using (var context = new RegionsEmployeesContext())
{
var query = context.Regions
.Where(r => r.ParentRegionId == -1)
.OrderBy(r => r.Name)
.Select(r => new RegionModel()
{
RegionId = r.RegionId,
Name = r.Name,
Employees = r.RegionEmployees
.Select(e => new EmployeeModel()
{
Name = e.Name,
Surname = e.Surname
})
});
return(query.ToList());
}
}
/// <summary>
/// Controller that handles the search request passed through a text box on the index page
/// </summary>
/// <param name="regionId">ID to search by</param>
/// <returns>Returns a partial view for the Ajax method to display without posting</returns>
public PartialViewResult SearchRegion(int regionId)
{
//this.Server.MapPath(
using (var context = new RegionsEmployeesContext())
{
//Search Region table with region ID
var query = context.Regions.FirstOrDefault(r => r.RegionId == regionId);
//If result is null, return nothing
if (query == null)
{
return(default(PartialViewResult));
}
//If region exists, instruct EF to explicitly load employees in that region
var employeeList = GetEmployees(query);
return(PartialView(employeeList));
}
}
public RegionModel Get(int id)
{
using (var context = new RegionsEmployeesContext())
{
var query = context.Regions
.Where(r => r.RegionId == id)
.Select(r => new RegionModel()
{
RegionId = r.RegionId,
Name = r.Name,
Employees = r.RegionEmployees
.Select(e => new EmployeeModel()
{
Name = e.Name,
Surname = e.Surname
})
});
return(query.FirstOrDefault());
}
}
/// <summary>
/// Function which extracts all employees from related regions
/// </summary>
/// <param name="parentRegion">Region from which the function has to start with</param>
/// <returns>Returns a list of all employees related to a region</returns>
public static List <Employee> GetEmployees(Region parentRegion)
{
//Create an empty list, which we will populate and return at the end
var returnEmployeeList = new List <Employee>();
//Create a queue for regions (used so we can visit each region once)
var regionQueue = new Queue <Region>();
//Kick start the while loop by adding the parent region to queue
regionQueue.Enqueue(parentRegion);
using (var context = new RegionsEmployeesContext())
{
//While queue is not empty, repeat loop
while (regionQueue.Count != 0)
{
//Take the first region out of queue
var currentRegion = (Region)regionQueue.Dequeue();
//Add all employees of current region to the list
returnEmployeeList.AddRange(currentRegion.RegionEmployees);
//Check if current region has any children
var query = context.Regions
.Include(d => d.RegionEmployees)
.Where(r => r.ParentRegionId == currentRegion.RegionId);
foreach (var region in query)
{
//If it has children, add them to queue
regionQueue.Enqueue(region);
}
}
}
//When the queue is emptied, return a populated list of all employees
return(returnEmployeeList);
}