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); }