/*TODO: extract copy-paste */ private CourseStatisticPageModel GetCourseStatisticsModel(CourseStatisticsParams param, int usersLimit) { var courseId = param.CourseId; var periodStart = param.PeriodStartDate; var periodFinish = param.PeriodFinishDate; var groupsIds = Request.GetMultipleValues("group"); var isInstructor = User.HasAccessFor(courseId, CourseRole.Instructor); var isStudent = !isInstructor; var currentUserId = User.Identity.GetUserId(); if (isStudent && !CanStudentViewGroupsStatistics(currentUserId, groupsIds)) { return(null); } var realPeriodFinish = periodFinish.Add(TimeSpan.FromDays(1)); var course = courseManager.GetCourse(courseId); var slidesIds = course.Slides.Select(s => s.Id).ToList(); var filterOptions = ControllerUtils.GetFilterOptionsByGroup <VisitsFilterOptions>(groupsRepo, User, courseId, groupsIds, allowSeeGroupForAnyMember: true); filterOptions.SlidesIds = slidesIds; filterOptions.PeriodStart = periodStart; filterOptions.PeriodFinish = realPeriodFinish; var usersIds = visitsRepo.GetVisitsInPeriod(filterOptions).DistinctBy(v => v.UserId).Select(v => v.UserId); /* If we filtered out users from one or several groups show them all */ if (filterOptions.UsersIds != null && !filterOptions.IsUserIdsSupplement) { usersIds = filterOptions.UsersIds; } var visitedUsers = usersIds .Join(db.Users, v => v, u => u.Id, (v, u) => new UnitStatisticUserInfo { UserId = u.Id, UserName = u.UserName, UserVisibleName = (u.LastName + u.FirstName != "" ? u.LastName + " " + u.FirstName : u.UserName).Trim() }) .ToList(); var isMore = visitedUsers.Count > usersLimit; var unitBySlide = course.Units.SelectMany(u => u.Slides.Select(s => Tuple.Create(u.Id, s.Id))).ToDictionary(p => p.Item2, p => p.Item1); var scoringGroups = course.Settings.Scoring.Groups; var visitedSlidesCountByUserAllTime = visitsRepo.GetVisitsInPeriod(filterOptions.WithPeriodStart(DateTime.MinValue).WithPeriodFinish(DateTime.MaxValue)) .GroupBy(v => v.UserId) .ToDictionary(g => g.Key, g => g.Count()); /* Get `usersLimit` best by slides count */ visitedUsers = visitedUsers .OrderByDescending(u => visitedSlidesCountByUserAllTime.GetOrDefault(u.UserId, 0)) .Take(usersLimit) .ToList(); var visitedUsersIds = visitedUsers.Select(v => v.UserId).ToList(); var visitedUsersGroups = groupsRepo.GetUsersGroupsIds(new List <string> { courseId }, visitedUsersIds, User, 10).ToDefaultDictionary(); /* From now fetch only filtered users' statistics */ filterOptions.UsersIds = visitedUsersIds; filterOptions.IsUserIdsSupplement = false; var scoreByUserUnitScoringGroup = ((IEnumerable <Visit>)visitsRepo.GetVisitsInPeriod(filterOptions)) .GroupBy(v => Tuple.Create(v.UserId, unitBySlide[v.SlideId], course.FindSlideById(v.SlideId)?.ScoringGroup)) .ToDictionary(g => g.Key, g => g.Sum(v => v.Score)) .ToDefaultDictionary(); var shouldBeSolvedSlides = course.Slides.Where(s => s.ShouldBeSolved).ToList(); var shouldBeSolvedSlidesIds = shouldBeSolvedSlides.Select(s => s.Id).ToList(); var shouldBeSolvedSlidesByUnitScoringGroup = shouldBeSolvedSlides .GroupBy(s => Tuple.Create(unitBySlide[s.Id], s.ScoringGroup)) .ToDictionary(g => g.Key, g => g.ToList()) .ToDefaultDictionary(); var scoreByUserAndSlide = ((IEnumerable <Visit>)visitsRepo.GetVisitsInPeriod(filterOptions.WithSlidesIds(shouldBeSolvedSlidesIds))) .GroupBy(v => Tuple.Create(v.UserId, v.SlideId)) .ToDictionary(g => g.Key, g => g.Sum(v => v.Score)) .ToDefaultDictionary(); var additionalScores = additionalScoresRepo .GetAdditionalScoresForUsers(courseId, visitedUsersIds) .ToDictionary(kv => kv.Key, kv => kv.Value.Score) .ToDefaultDictionary(); var usersGroupsIds = groupsRepo.GetUsersGroupsIds(courseId, visitedUsersIds); var enabledAdditionalScoringGroupsForGroups = groupsRepo.GetEnabledAdditionalScoringGroups(courseId) .GroupBy(e => e.GroupId) .ToDictionary(g => g.Key, g => g.Select(e => e.ScoringGroupId).ToList()); /* Filter out only scoring groups which are affected in selected groups */ var additionalScoringGroupsForFilteredGroups = ControllerUtils.GetEnabledAdditionalScoringGroupsForGroups(groupsRepo, course, groupsIds, User); scoringGroups = scoringGroups .Where(kv => kv.Value.MaxNotAdditionalScore > 0 || additionalScoringGroupsForFilteredGroups.Contains(kv.Key)) .ToDictionary(kv => kv.Key, kv => kv.Value) .ToSortedDictionary(); var groups = groupsRepo.GetAvailableForUserGroups(courseId, User); if (!isInstructor) { groups = groupsRepo.GetUserGroups(courseId, currentUserId); } var model = new CourseStatisticPageModel { IsInstructor = isInstructor, Course = course, SelectedGroupsIds = groupsIds, Groups = groups, PeriodStart = periodStart, PeriodFinish = periodFinish, VisitedUsers = visitedUsers, VisitedUsersIsMore = isMore, VisitedUsersGroups = visitedUsersGroups, ShouldBeSolvedSlidesByUnitScoringGroup = shouldBeSolvedSlidesByUnitScoringGroup, ScoringGroups = scoringGroups, ScoreByUserUnitScoringGroup = scoreByUserUnitScoringGroup, ScoreByUserAndSlide = scoreByUserAndSlide, AdditionalScores = additionalScores, UsersGroupsIds = usersGroupsIds, EnabledAdditionalScoringGroupsForGroups = enabledAdditionalScoringGroupsForGroups, }; return(model); }
private void FillCourseStatisticsExcelWorksheet(ExcelWorksheet worksheet, CourseStatisticPageModel model, bool onlyFullScores = false) { var builder = new ExcelWorksheetBuilder(worksheet); builder.AddStyleRule(s => s.Font.Bold = true); builder.AddCell(""); builder.AddCell(""); builder.AddCell("За весь курс", model.ScoringGroups.Count); builder.AddStyleRule(s => s.Border.Left.Style = ExcelBorderStyle.Thin); foreach (var unit in model.Course.Units) { var colspan = 0; foreach (var scoringGroup in model.GetUsingUnitScoringGroups(unit, model.ScoringGroups).Values) { var shouldBeSolvedSlides = model.ShouldBeSolvedSlidesByUnitScoringGroup[Tuple.Create(unit.Id, scoringGroup.Id)]; colspan += shouldBeSolvedSlides.Count + 1; if (shouldBeSolvedSlides.Count > 0 && scoringGroup.CanBeSetByInstructor) { colspan++; } } builder.AddCell(unit.Title, colspan); } builder.PopStyleRule(); // Border.Left builder.GoToNewLine(); builder.AddCell("Фамилия Имя"); builder.AddCell("Группа"); foreach (var scoringGroup in model.ScoringGroups.Values) { builder.AddCell(scoringGroup.Abbreviation); } foreach (var unit in model.Course.Units) { builder.AddStyleRuleForOneCell(s => s.Border.Left.Style = ExcelBorderStyle.Thin); foreach (var scoringGroup in model.GetUsingUnitScoringGroups(unit, model.ScoringGroups).Values) { var shouldBeSolvedSlides = model.ShouldBeSolvedSlidesByUnitScoringGroup[Tuple.Create(unit.Id, scoringGroup.Id)]; builder.AddCell(scoringGroup.Abbreviation); builder.AddStyleRule(s => s.TextRotation = 90); builder.AddStyleRule(s => s.Font.Bold = false); foreach (var slide in shouldBeSolvedSlides) { builder.AddCell($"{scoringGroup.Abbreviation}: {slide.Title}"); } if (shouldBeSolvedSlides.Count > 0 && scoringGroup.CanBeSetByInstructor) { builder.AddCell("Доп"); } builder.PopStyleRule(); builder.PopStyleRule(); } } builder.GoToNewLine(); builder.AddStyleRule(s => s.Border.Bottom.Style = ExcelBorderStyle.Thin); builder.AddStyleRuleForOneCell(s => { s.HorizontalAlignment = ExcelHorizontalAlignment.Right; s.Font.Size = 10; }); builder.AddCell("Максимум:", 2); foreach (var scoringGroup in model.ScoringGroups.Values) { builder.AddCell(model.Course.Units.Sum(unit => model.GetMaxScoreForUnitByScoringGroup(unit, scoringGroup))); } foreach (var unit in model.Course.Units) { builder.AddStyleRuleForOneCell(s => s.Border.Left.Style = ExcelBorderStyle.Thin); foreach (var scoringGroup in model.GetUsingUnitScoringGroups(unit, model.ScoringGroups).Values) { var shouldBeSolvedSlides = model.ShouldBeSolvedSlidesByUnitScoringGroup[Tuple.Create(unit.Id, scoringGroup.Id)]; builder.AddCell(model.GetMaxScoreForUnitByScoringGroup(unit, scoringGroup)); foreach (var slide in shouldBeSolvedSlides) { builder.AddCell(slide.MaxScore); } if (shouldBeSolvedSlides.Count > 0 && scoringGroup.CanBeSetByInstructor) { builder.AddCell(scoringGroup.MaxAdditionalScore); } } } builder.PopStyleRule(); // Bottom.Border builder.GoToNewLine(); builder.AddStyleRule(s => s.Font.Bold = false); foreach (var user in model.VisitedUsers) { builder.AddCell(user.UserVisibleName); var userGroups = model.Groups.Where(g => model.VisitedUsersGroups[user.UserId].Contains(g.Id)).Select(g => g.Name).ToList(); builder.AddCell(string.Join(", ", userGroups)); foreach (var scoringGroup in model.ScoringGroups.Values) { var scoringGroupScore = model.Course.Units.Sum(unit => model.GetTotalScoreForUserInUnitByScoringGroup(user.UserId, unit, scoringGroup)); var scoringGroupOnlyFullScore = model.Course.Units.Sum(unit => model.GetTotalOnlyFullScoreForUserInUnitByScoringGroup(user.UserId, unit, scoringGroup)); builder.AddCell(onlyFullScores ? scoringGroupOnlyFullScore : scoringGroupScore); } foreach (var unit in model.Course.Units) { builder.AddStyleRuleForOneCell(s => s.Border.Left.Style = ExcelBorderStyle.Thin); foreach (var scoringGroup in model.GetUsingUnitScoringGroups(unit, model.ScoringGroups).Values) { var shouldBeSolvedSlides = model.ShouldBeSolvedSlidesByUnitScoringGroup[Tuple.Create(unit.Id, scoringGroup.Id)]; var scoringGroupScore = model.GetTotalScoreForUserInUnitByScoringGroup(user.UserId, unit, scoringGroup); var scoringGroupOnlyFullScore = model.GetTotalOnlyFullScoreForUserInUnitByScoringGroup(user.UserId, unit, scoringGroup); builder.AddCell(onlyFullScores ? scoringGroupOnlyFullScore : scoringGroupScore); foreach (var slide in shouldBeSolvedSlides) { var slideScore = model.ScoreByUserAndSlide[Tuple.Create(user.UserId, slide.Id)]; builder.AddCell(onlyFullScores ? model.GetOnlyFullScore(slideScore, slide.MaxScore) : slideScore); } if (shouldBeSolvedSlides.Count > 0 && scoringGroup.CanBeSetByInstructor) { builder.AddCell(model.AdditionalScores[Tuple.Create(user.UserId, unit.Id, scoringGroup.Id)]); } } } builder.GoToNewLine(); } for (var column = 1; column <= builder.ColumnsCount; column++) { worksheet.Column(column).AutoFit(0.1); } }