//------------------------------ Entity Dashboard Query ------------------------------//
public static IQueryable <EntityGroup> GetEntityDashboard()
{
var careers = db.Careers.Include(e => e.Employee.EmployeeStatus.Select(c => c.EmployeeStatus))
.Where(e =>
//Select all active employees
(e.Employee.EmployeeStatus.GroupBy(x => x.EmployeeID)
.Select(y => y.OrderByDescending(i => i.EffectiveDate)
.FirstOrDefault()).Select(c => c.EmployeeStatus.Name).FirstOrDefault() == "Active") ||
//OR all contract employees
(e.Employee.EmployeeStatus.GroupBy(x => x.EmployeeID)
.Select(y => y.OrderByDescending(i => i.EffectiveDate)
.FirstOrDefault()).Select(c => c.EmployeeStatus.Name).FirstOrDefault() == "Contractor"))
// AND Select most recent career records
.Include(x => x.Employee)
.GroupBy(x => x.EmployeeID)
.Select(y => y.OrderByDescending(i => i.EffectiveDate)
.FirstOrDefault());
IQueryable <EntityGroup> data = from employee in careers
group employee by new { employee.BudgetArea.Abbreviation, employee.Department.Name } into EntityGroup
select new EntityGroup()
{
BudgetArea = EntityGroup.Key.Abbreviation,
Department = EntityGroup.Key.Name,
BAreaCount = EntityGroup.Count(),
DeptCount = EntityGroup.Count(),
Employees = careers.Where(e => e.Department.Name.Equals(EntityGroup.Key.Name)).Select(e => e.Employee).ToList()
};
return(data);
}
public static IQueryable<EntityGroup> GetDepartmentDashboard()
{
IQueryable<EntityGroup> data = from employee in db.Careers
.Include(e => e.Employee.EmployeeStatus.Select(c => c.EmployeeStatus))
.Where(e => (e.Employee.EmployeeStatus.GroupBy(x => x.EmployeeID)
.Select(y => y.OrderByDescending(i => i.EffectiveDate)
.FirstOrDefault()).Select(c => c.EmployeeStatus.Name).FirstOrDefault() == "Active") ||
(e.Employee.EmployeeStatus.GroupBy(x => x.EmployeeID)
.Select(y => y.OrderByDescending(i => i.EffectiveDate)
.FirstOrDefault()).Select(c => c.EmployeeStatus.Name).FirstOrDefault() == "Contractor"))
.Include(x => x.Employee)
.GroupBy(x => x.EmployeeID)
.Select(y => y.OrderByDescending(i => i.EffectiveDate)
.FirstOrDefault())
group employee by employee.Department.Name into EntityGroup
select new EntityGroup()
{
Department = EntityGroup.Key,
DeptCount = EntityGroup.Count()
};
return data;
}