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"))) ) )