public CompanyDashboardModel GetCompanyDashboard(int year, int month)
{
CompanyDashboardModel companyDashboard = new CompanyDashboardModel();
decimal baseHours = GetMonthlyWorkingDays(year, month) * 8;
List <CompanyDashboardRawModel> rawData = _storedProcedures.GetStoredProcedure <CompanyDashboardRawModel>("CompanyWorkingHoursData", new int[] { year, month });
List <CompanyEmployeeHoursModel> employeeHours = _storedProcedures.GetStoredProcedure <CompanyEmployeeHoursModel>("EmployeeHoursByDayType", new int[] { year, month });
List <CompanyOvertimeModel> overtime = _storedProcedures.GetStoredProcedure <CompanyOvertimeModel>("CompanyOvertimeHours", new int[] { year, month });
List <MasterModel> activeTeams = new List <MasterModel>();
activeTeams.AddRange(rawData.GroupBy(x => new
{
Id = x.TeamId,
Name = x.TeamName
}).ToList().Select(x => new MasterModel
{
Id = x.Key.Id,
Name = x.Key.Name
}).ToList());
companyDashboard.EmployeesCount = rawData.GroupBy(x => x.EmployeeId).Count();
companyDashboard.ProjectsCount = rawData.GroupBy(x => x.ProjectId).Count();
companyDashboard.TotalHours = companyDashboard.EmployeesCount * baseHours;
companyDashboard.TotalWorkingHours = rawData.Sum(x => x.WorkingHours);
companyDashboard.Projects = GetCompanyProjectModels(rawData);
companyDashboard.Roles = GetRoleUtilization(rawData, baseHours);
companyDashboard.Teams = GetCompanyTeamModels(rawData, employeeHours, activeTeams, overtime);
GetCompanyMissingEntries(employeeHours, companyDashboard.Teams, baseHours, overtime);
return(companyDashboard);
}
public TeamDashboardModel GetTeamDashboardStored(int teamId, int year, int month)
{
Team team = _unit.Teams.Get(teamId);
TeamDashboardModel teamDashboard = new TeamDashboardModel();
List <TeamRawModel> rawData = _storedProcedures.GetStoredProcedure <TeamRawModel>("TeamDashboard", new int[] { team.Id, year, month });
List <TeamRawNonWorkingHoursModel> rawDataPTO = _storedProcedures.GetStoredProcedure <TeamRawNonWorkingHoursModel>("GetMemberPTOHours", new int[] { team.Id, year, month });
List <TeamRawNonWorkingHoursModel> rawDataOvertime = _storedProcedures.GetStoredProcedure <TeamRawNonWorkingHoursModel>("GetMemberOvertimeHours", new int[] { team.Id, year, month });
teamDashboard.Year = year;
teamDashboard.Month = month;
teamDashboard.Team = new MasterModel {
Id = team.Id, Name = team.Name
};
teamDashboard.NumberOfEmployees = rawData.GroupBy(x => x.EmployeeId).Count();
teamDashboard.TotalWorkingHours = rawData.Sum(x => x.Value);
List <TeamRawCountModel> rawDataProjectsCount = _storedProcedures.GetStoredProcedure <TeamRawCountModel>("CountProjects", new int[] { team.Id, year, month });
teamDashboard.NumberOfProjects = rawDataProjectsCount.Count;
decimal baseTotalHours = GetMonthlyWorkingDays(year, month) * 8;
teamDashboard.TotalHours = baseTotalHours * teamDashboard.NumberOfEmployees;
List <TeamRawModel> rawDataMissingEntries = GetMembersMissingEntries(team.Id, year, month, baseTotalHours);
foreach (TeamRawModel r in rawData)
{
teamDashboard.EmployeeTimes.Add(new TeamMemberDashboardModel
{
Employee = new MasterModel {
Id = r.EmployeeId, Name = r.EmployeeName
},
TotalHours = baseTotalHours,
Overtime = (rawDataOvertime == null || rawDataOvertime.FirstOrDefault(x => x.MemberId == r.EmployeeId) == null) ? 0 : rawDataOvertime.FirstOrDefault(x => x.MemberId == r.EmployeeId).Value,
PaidTimeOff = (rawDataPTO == null || rawDataPTO.FirstOrDefault(x => x.MemberId == r.EmployeeId) == null) ? 0 : rawDataPTO.FirstOrDefault(x => x.MemberId == r.EmployeeId).Value,
WorkingHours = r.Value,
MissingEntries = (rawDataMissingEntries == null || rawDataMissingEntries.FirstOrDefault(x => x.EmployeeId == r.EmployeeId) == null) ? 0 : rawDataMissingEntries.FirstOrDefault(x => x.EmployeeId == r.EmployeeId).Value,
});
}
return(teamDashboard);
}
public PersonalDashboardStoredModel GetPersonalDashboardStored(int empId, int year, int month)
{
PersonalDashboardStoredModel personalDashboard = new PersonalDashboardStoredModel();
List <PersonalDashboardRawModel> rawData = _storedProcedureService.GetStoredProcedure <PersonalDashboardRawModel>("personalDashboard", new int[] { empId, year, month });
decimal workingDaysInMonth = GetMonthlyWorkingDays(year, month) * 8;
decimal workingDaysInYear = GetYearlyWorkingDays(year) * 8;
if (rawData.Count > 0)
{
personalDashboard.PersonalDashboardHours = rawData[0];
// What if there's overtime?
personalDashboard.UtilizationMonthly = decimal.Round(((rawData[0].WorkingMonthly / workingDaysInMonth) * 100), 2, MidpointRounding.AwayFromZero);
personalDashboard.UtilizationYearly = decimal.Round(((rawData[0].WorkingYearly / workingDaysInYear) * 100), 2, MidpointRounding.AwayFromZero);
}
else
{
EmployeeModel employee = _unit.Employees.Get(empId).Create();
personalDashboard.PersonalDashboardHours = new PersonalDashboardRawModel {
EmployeeId = employee.Id, EmployeeName = employee.FullName
};
}
personalDashboard.BradfordFactor = GetBradfordFactor(empId, year);
return(personalDashboard);
}
public List <AnnualTimeModel> GetStored(int year)
{
List <AnnualTimeModel> result = new List <AnnualTimeModel>();
AnnualTimeModel total = new AnnualTimeModel {
Project = new MasterModel {
Id = 0, Name = "TOTAL"
}
};
List <AnnualRawModel> rawData = _storedProcedures.GetStoredProcedure <AnnualRawModel>("AnnualReport", new int[] { year });
/*
* var cmd = _unit.Context.Database.GetDbConnection().CreateCommand();
* cmd.CommandType = CommandType.Text;
* cmd.CommandText = $"select * from AnnualReport({year})";
* if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open();
* DbDataReader sql = cmd.ExecuteReader();
* List<AnnualRawModel> rawData = new List<AnnualRawModel>();
* if (sql.HasRows)
* {
* while (sql.Read())
* {
* rawData.Add(new AnnualRawModel
* {
* Id = sql.GetInt32(0),
* Name = sql.GetString(1),
* Month = sql.GetInt32(2),
* Hours = sql.GetDecimal(3)
* });
* }
*/
AnnualTimeModel atm = new AnnualTimeModel {
Project = new MasterModel {
Id = 0
}
};
foreach (AnnualRawModel item in rawData)
{
if (atm.Project.Id != item.Id)
{
if (atm.Project.Id != 0)
{
result.Add(atm);
}
atm = new AnnualTimeModel {
Project = new MasterModel {
Id = item.Id, Name = item.Name
}
};
total.Project.Id++;
}
atm.Hours[item.Month - 1] = item.Hours;
atm.Total += item.Hours;
total.Hours[item.Month - 1] += item.Hours;
total.Total += item.Hours;
}
if (atm.Project.Id != 0)
{
result.Add(atm);
}
//}
result.Add(total);
return(result);
}