def get_leaving_learners_per_module(self, obj):
"""
For each module in course `obj`, get the number of learners who
haven't passed the course and whose activity furtherst in the course
was in that module.
"""
form = GenericFilterSet(
self.context["request"].GET,
CourseMembership.objects.none(),
request=self.context["request"],
).form
form.errors
from_date = form.cleaned_data.get("from_date")
to_date = form.cleaned_data.get("to_date")
if not to_date:
to_date = now()
if from_date and from_date > to_date:
to_date = from_date
subquery = CountSubquery(
LastActivityPerModule.objects.filter(
module_id=OuterRef("pk")).filter(timestamp__lt=to_date -
timedelta(weeks=6)))
if from_date:
subquery -= CountSubquery(
LastActivityPerModule.objects.filter(
module_id=OuterRef("pk")).filter(timestamp__lt=from_date -
timedelta(weeks=6)))
return list(
self._filter_current_branch(
obj.pk).get().modules.values_list("module_id").annotate(
user_count=subquery).order_by("order"))
def with_assignments(self):
return self.annotate(
assignments=CountSubquery(
ItemProgrammingAssignment.objects.filter(
branch_id=OuterRef("branches__pk")
)
)
+ CountSubquery(
ItemPeerAssignment.objects.filter(branch_id=OuterRef("branches__pk"))
)
)
def with_videos(self):
return self.annotate(
videos=CountSubquery(
Item.objects.filter(branch_id=OuterRef("branches__pk")).filter(
type__description=ItemType.LECTURE
)
)
)
def with_finished_learners(self, filter):
return self.annotate(
finished_learners=CountSubquery(
filter(
Grade.objects.filter(course_id=OuterRef("pk")).filter(
passing_state__in=[Grade.PASSED, Grade.VERIFIED_PASSED]
)
)
)
)
def with_submission_ratio(self, filter):
return self.annotate(submission_ratio=Coalesce(
Cast(
CountSubquery(
filter(
PeerSubmission.objects.filter(
peer_assignment__items=OuterRef("pk")).values_list(
"eitdigital_user_id").distinct())),
models.FloatField(),
) / NullIf(
CountSubquery(
filter(
CourseMembership.objects.filter(
course_id=OuterRef("branch__course_id")).
values_list("eitdigital_user_id").distinct())),
0,
output_field=models.FloatField(),
),
0,
))
def with_enrolled_learners(self, filter):
return self.annotate(
enrolled_learners=CountSubquery(
filter(
CourseMembership.objects.filter(course_id=OuterRef("pk")).filter(
role__in=[
CourseMembership.LEARNER,
CourseMembership.PRE_ENROLLED_LEARNER,
]
)
)
)
)
def get_number_of_attempts(self, obj):
"""
Return the number of users that have used a specific number of attempts
on the quiz within the given timespan. Every day on which a user
submitted at least one response is counted as an attempt.
"""
return list(
self.filter(Attempt.objects.filter(quiz=obj)).annotate(
number_of_attempts=CountSubquery(
GenericFilterSet(
self.context["request"].GET,
Attempt.objects.filter(
quiz_id=OuterRef("quiz_id"),
eitdigital_user_id=OuterRef("eitdigital_user_id"),
),
).qs)).values_list("number_of_attempts").order_by(
"number_of_attempts").annotate(
num_people=Count("number_of_attempts")))
def with_leaving_learners(self): # pragma: no cover
return self.annotate(
leaving_learners=CountSubquery(
CourseMembership.objects.filter(course_id=OuterRef("pk"))
.filter(
role__in=[
CourseMembership.LEARNER,
CourseMembership.PRE_ENROLLED_LEARNER,
]
)
.values("eitdigital_user_id")
.difference(
Grade.objects.filter(course_id=OuterRef("pk"))
.filter(passing_state__in=[Grade.PASSED, Grade.VERIFIED_PASSED])
.values("eitdigital_user_id")
)
.difference(
CourseProgress.objects.filter(course_id=OuterRef("pk"))
.filter(timestamp__gt=now() - timedelta(weeks=6))
.values("eitdigital_user_id")
)
)
)
def with_submissions(self, filter):
return self.annotate(submissions=CountSubquery(
filter(
PeerSubmission.objects.filter(
peer_assignment__items=OuterRef("pk")))))
def with_quizzes(self):
return self.annotate(
quizzes=CountSubquery(
ItemQuiz.objects.filter(branch_id=OuterRef("branches__pk"))
)
)
def with_modules(self):
return self.annotate(
modules=CountSubquery(
Module.objects.filter(branch_id=OuterRef("branches__pk"))
)
)
def with_paying_learners(self, filter):
return self.annotate(
paying_learners=CountSubquery(
CertificatePayment.objects.filter(course_id=OuterRef("pk"))
)
)
def with_cohorts(self, filter):
return self.annotate(
cohorts=CountSubquery(
filter(OnDemandSession.objects.filter(course_id=OuterRef("pk")))
)
)