public static CompanyReport GetCompanyReport(this UnitOfWork unit, int year,
int month, ModelFactory factory)
{
int days = DateTime.DaysInMonth(year, month);
DateTime currentDate = new DateTime(year, month, days);
//taking into consideration employee/project who quit/ended in the given month/year
var numEmployees = unit.Employees.Get().Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
var numProjects = unit.Projects.Get().Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
//pm utilization
int pmCount = unit.Employees.Get(x => x.RoleId == "MGR")
.Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
var pmWorkingDays = unit.Days.Get(x => x.Type == DayType.WorkingDay &&
x.Date.Month == month &&
x.Date.Year == year)
.Where(x => x.Employee.RoleId == "MGR" &&
x.Employee.BeginDate <= currentDate &&
(x.Employee.EndDate == null ||
x.Employee.EndDate > currentDate ||
x.Employee.EndDate.Value.Month == month))
.Count();
decimal?pmUtil = 0;
if ((noDaysInMonth(year, month) * pmCount) != 0)
{
pmUtil = Math.Round((decimal)(pmWorkingDays / (decimal)(noDaysInMonth(year, month) * pmCount)) * 100, 2);
}
//qa utilization
int qaCount = unit.Employees.Get(x => x.RoleId == "QAE")
.Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
var qaWorkingDays = unit.Days.Get(x => x.Type == DayType.WorkingDay &&
x.Date.Month == month &&
x.Date.Year == year)
.Where(x => x.Employee.RoleId == "QAE" &&
x.Employee.BeginDate <= currentDate &&
(x.Employee.EndDate == null ||
x.Employee.EndDate > currentDate ||
x.Employee.EndDate.Value.Month == month))
.Count();
decimal qaUtil = 0;
if (noDaysInMonth(year, month) * qaCount != 0)
{
qaUtil = Math.Round((decimal)(qaWorkingDays / (decimal)(noDaysInMonth(year, month) * qaCount)) * 100, 2);
}
//dev utilization
int devCount = unit.Employees.Get(x => x.RoleId == "DEV")
.Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
var devWorkingDays = unit.Days.Get(x => x.Type == DayType.WorkingDay &&
x.Date.Month == month &&
x.Date.Year == year)
.Where(x => x.Employee.RoleId == "DEV" &&
x.Employee.BeginDate <= currentDate &&
(x.Employee.EndDate == null ||
x.Employee.EndDate > currentDate ||
x.Employee.EndDate.Value.Month == month))
.Count();
decimal devUtil = 0;
if (noDaysInMonth(year, month) * devCount != 0)
{
devUtil = Math.Round((decimal)(devWorkingDays / (decimal)(noDaysInMonth(year, month) * devCount)) * 100, 2);
}
//uiux utilization
int uiuxCount = unit.Employees.Get(x => x.RoleId == "UIX")
.Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
var uiuxWorkingDays = unit.Days.Get(x => x.Type == DayType.WorkingDay &&
x.Date.Month == month &&
x.Date.Year == year)
.Where(x => x.Employee.RoleId == "UIX" &&
x.Employee.BeginDate <= currentDate &&
(x.Employee.EndDate == null ||
x.Employee.EndDate > currentDate ||
x.Employee.EndDate.Value.Month == month))
.Count();
decimal uiuxUtil = 0;
if (noDaysInMonth(year, month) * uiuxCount != 0)
{
uiuxUtil = Math.Round((decimal)(uiuxWorkingDays / (decimal)(noDaysInMonth(year, month) * uiuxCount)) * 100, 2);
}
//total working hours for every employee
var totalHours = unit.Days.Get()
.Where(x => x.Date.Year == year && x.Date.Month == month)
.Select(x => (int?)x.Hours)
.Sum() ?? 0;
//var totalHours = unit.Teams.Get().SelectMany(x => x.Engagements).Select(x => x.Employee)
// .SelectMany(x => x.Days)
// .Where(x => x.Date.Month == month && x.Date.Year == year && x.Type == DayType.WorkingDay)
// .Select(y => (int?)y.Hours)
// .Sum() ?? 0;
int maxPossibleTotalHours = noDaysInMonth(year, month) * 8 * numEmployees;
int missingEntriesTotal = 0;
int daysInMonth = noDaysInMonth(year, month);
var allTeams = unit.Teams.Get().ToList();
List <CompanyReportTeams> overtimeTeams = new List <CompanyReportTeams>();
foreach (var team in allTeams)
{
decimal overtimehours = 0;
var employees = unit.Engagements.Get().Where(x => x.Team.Id == team.Id).Select(x => x.Employee).ToList();
var projects = team.Projects;
int missingEntriesTeam = 0;
decimal hoursTeam = 0;
decimal hoursTeam1 = 0;
foreach (var employee in employees)
{
decimal hoursEmp = 0;
int daysCount = 0;
decimal totHours = 0;
foreach (var project in projects)
{
var hoursPro = unit.Days.Get().Where(x => x.Date.Month == month && x.Date.Year == year && employee.Id == x.Employee.Id)
.SelectMany(t => t.Tasks)
.Where(x => x.Project.Id == project.Id)
.Select(h => h.Hours)
.DefaultIfEmpty(0)
.Sum();
totHours += hoursPro;
}
hoursTeam1 += totHours;
foreach (var day in employee.Days)
{
if (day.Hours > 8 && day.Date.Month == month && day.Date.Year == year && day.Type == DayType.WorkingDay)
{
overtimehours += day.Hours - 8;
}
if (day.Date.Month == month && day.Date.Year == year && day.Type == DayType.WorkingDay)
{
hoursEmp += day.Hours;
daysCount++;
}
}
hoursTeam += hoursEmp;
missingEntriesTeam += (daysInMonth - daysCount);
}
int maxHours = noDaysInMonth(year, month) * employees.Count() * 8;
var teamUtil = Math.Round(((double)hoursTeam1 / maxHours) * 100, 2);
CompanyReportTeams teamToAdd = new CompanyReportTeams()
{
TeamName = team.Name,
OvertimeHours = overtimehours,
TeamMissingHours = missingEntriesTeam,
TeamHours = hoursTeam1,
Utilization = teamUtil
};
missingEntriesTotal += missingEntriesTeam;
overtimeTeams.Add(teamToAdd);
}
var allProjects = unit.Projects.Get().Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.ToList();
List <CompanyReportProjects> revenueProjects = new List <CompanyReportProjects>();
foreach (var project in allProjects)
{
CompanyReportProjects projectToAdd = new CompanyReportProjects()
{
ProjectName = project.Name,
Revenue = project.Amount
};
revenueProjects.Add(projectToAdd);
}
//creating a report
var companyReport = new CompanyReport
{
NumEmployees = numEmployees,
NumProjects = numProjects,
TotalHours = totalHours,
MaxPossibleTotalHours = maxPossibleTotalHours,
MissingEntries = missingEntriesTotal,
PMUtilization = pmUtil.Value,
PMCount = pmCount,
DEVUtilization = devUtil,
DEVCount = devCount,
QAUtilization = qaUtil,
QACount = qaCount,
UIUXUtilization = uiuxUtil,
UIUXCount = uiuxCount,
OvertimeHoursTeams = overtimeTeams,
RevenueProjects = revenueProjects
};
return(companyReport);
}
public static CompanyReport GetCompanyReport(this UnitOfWork unit, int year, int month, ModelFactory factory)
{
var AllTeams = unit.Teams.Get().ToList();
List <CompanyReportTeams> Teams = new List <CompanyReportTeams>();
List <CompanyReportProjects> Projects = new List <CompanyReportProjects>();
var employeesWithMostOvertime = unit.Days.Get()
.Where(d => d.Hours > 8 && d.Date.Month == month && d.Date.Year == year)
.GroupBy(d => new { d.Employee, d.Hours })
.Select(d => new OvertimeEmployees {
Name = d.Key.Employee.FirstName + " " + d.Key.Employee.LastName, SumHours = d.Sum(w => w.Hours - 8)
}).OrderByDescending(t => t.SumHours)
.ToList();
var totalForProjects = unit.Tasks.Get()
.Where(p => p.Day.Date.Year == year && p.Day.Date.Month == month)
.GroupBy(d => new { d.Project.Name })
.Select(t => new TotalForProjects {
Name = t.Key.Name, OvertimeHours = t.Sum(w => w.Hours)
}).ToList();
foreach (var team in AllTeams)
{
decimal?overtimehours = 0;
var employees = unit.Engagements.Get().Where(x => x.Team.Id == team.Id).Select(x => x.Employee).ToList();
foreach (var employee in employees)
{
foreach (var day in employee.Days)
{
if (day.Hours > 8 && day.Date.Month == month && day.Date.Year == year)
{
overtimehours += day.Hours - 8;
}
}
}
CompanyReportTeams teamToAdd = new CompanyReportTeams()
{
TeamName = team.Name,
OvertimeHours = overtimehours
};
Teams.Add(teamToAdd);
}
int days = DateTime.DaysInMonth(year, month);
DateTime currentDate = new DateTime(year, month, days);
//taking into consideration employee/project who quit/ended in the given month/year
var numEmployees = unit.Employees.Get().Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
var numProjects = unit.Projects.Get().Where(x => x.StartDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
//total working hours for every employee
var totalHours = unit.Days.Get().Where(x => x.Type == DayType.WorkingDay &&
x.Date.Year == year && x.Date.Month == month)
.Select(x => (int?)x.Hours)
.Sum() ?? 0;
int maxPossibleTotalHours = noDaysInMonth(year, month) * 8 * numEmployees;
//pm utilization
int pmCount = unit.Employees.Get().Where(x => x.Roles.Id == "MGR")
.Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
var pmWorkingDays = unit.Days.Get().Where(x => x.Type == DayType.WorkingDay &&
x.Date.Month == month &&
x.Date.Year == year)
.Where(x => x.Employee.Roles.Id == "MGR" &&
x.Employee.BeginDate <= currentDate &&
(x.Employee.EndDate == null ||
x.Employee.EndDate > currentDate ||
x.Employee.EndDate.Value.Month == month))
.Count();
decimal?pmUtil = 0;
if ((noDaysInMonth(year, month) * pmCount) != 0)
{
pmUtil = Math.Round((decimal)(pmWorkingDays / (decimal)(noDaysInMonth(year, month) * pmCount)) * 100, 2);
}
//qa utilization
int qaCount = unit.Employees.Get().Where(x => x.Roles.Id == "QAE")
.Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
var qaWorkingDays = unit.Days.Get().Where(x => x.Type == DayType.WorkingDay &&
x.Date.Month == month &&
x.Date.Year == year)
.Where(x => x.Employee.Roles.Id == "QAE" &&
x.Employee.BeginDate <= currentDate &&
(x.Employee.EndDate == null ||
x.Employee.EndDate > currentDate ||
x.Employee.EndDate.Value.Month == month))
.Count();
decimal qaUtil = 0;
if (noDaysInMonth(year, month) * qaCount != 0)
{
qaUtil = Math.Round((decimal)(qaWorkingDays / (decimal)(noDaysInMonth(year, month) * qaCount)) * 100, 2);
}
//dev utilization
int devCount = unit.Employees.Get().Where(x => x.Roles.Id == "DEV")
.Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
var devWorkingDays = unit.Days.Get().Where(x => x.Type == DayType.WorkingDay &&
x.Date.Month == month &&
x.Date.Year == year)
.Where(x => x.Employee.Roles.Id == "DEV" &&
x.Employee.BeginDate <= currentDate &&
(x.Employee.EndDate == null ||
x.Employee.EndDate > currentDate ||
x.Employee.EndDate.Value.Month == month))
.Count();
decimal devUtil = 0;
if (noDaysInMonth(year, month) * devCount != 0)
{
devUtil = Math.Round((decimal)(devWorkingDays / (decimal)(noDaysInMonth(year, month) * devCount)) * 100, 2);
}
//uiux utilization
int uiuxCount = unit.Employees.Get().Where(x => x.Roles.Id == "UIX")
.Where(x => x.BeginDate <= currentDate &&
(x.EndDate == null ||
x.EndDate > currentDate ||
x.EndDate.Value.Month == month))
.Count();
var uiuxWorkingDays = unit.Days.Get().Where(x => x.Type == DayType.WorkingDay &&
x.Date.Month == month &&
x.Date.Year == year)
.Where(x => x.Employee.Roles.Id == "UIX" &&
x.Employee.BeginDate <= currentDate &&
(x.Employee.EndDate == null ||
x.Employee.EndDate > currentDate ||
x.Employee.EndDate.Value.Month == month))
.Count();
decimal uiuxUtil = 0;
if (noDaysInMonth(year, month) * uiuxCount != 0)
{
uiuxUtil = Math.Round((decimal)(uiuxWorkingDays / (decimal)(noDaysInMonth(year, month) * uiuxCount)) * 100, 2);
}
//var overtimeTeams = unit.Days.Get().Where(x => x.Date.Year == year && x.Date.Month == month)
// .Select(x => new CompanyReportTeams()
// {
// TeamName = x.Employee.Engagements.Select(y => y.Team.Name).FirstOrDefault(),
// //x.Tasks.Select(y => y.Project.Team.Name).FirstOrDefault(),
// OvertimeHours = 0
// })
// .OrderBy(y => y.TeamName)
// .ToList();
var companyReport = new CompanyReport
{
OvertimeEmployees = employeesWithMostOvertime,
NumEmployees = numEmployees,
NumProjects = numProjects,
TotalHours = totalHours,
MaxPossibleTotalHours = maxPossibleTotalHours,
PMUtilization = pmUtil.Value,
PMCount = pmCount,
DEVUtilization = devUtil,
DEVCount = devCount,
QAUtilization = qaUtil,
QACount = qaCount,
UIUXUtilization = uiuxUtil,
UIUXCount = uiuxCount,
OvertimeHoursTeams = Teams,
TotalForProjects = totalForProjects
//RevenueProjects = revenueProjects
};
return(companyReport);
}