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