def aggregated_data(request): tables = [] table_head = ["Data description", "Value", "More info"] table_data = [] """ Overall statistics """ teacher_count = Teacher.objects.count() student_count = Student.objects.count() new_profiles_count = User.objects.filter(date_joined__gte=timezone.now() - timedelta(days=7)).count() table_data.append([ "Number of users", teacher_count + student_count, "Number of teachers + Number of students" ]) table_data.append([ "Number of new users (past week)", new_profiles_count, "Number of user profiles" ]) tables.append({ 'title': "Overall Statistics", 'description': "CFL site overall statistics", 'header': table_head, 'data': table_data }) """ School statistics """ table_data = [] table_data.append( ["Number of schools signed up", School.objects.count(), ""]) num_of_teachers_per_school = School.objects.annotate( num_teachers=Count('teacher_school')) stats_teachers_per_school = num_of_teachers_per_school.aggregate( Avg('num_teachers')) table_data.append([ "Average number of teachers per school", stats_teachers_per_school['num_teachers__avg'], "" ]) tables.append({ 'title': "Schools or Clubs", 'description': "", 'header': table_head, 'data': table_data }) """ Teacher statistics """ table_data = [] table_data.append(["Number of teachers signed up", teacher_count, ""]) table_data.append([ "Number of teachers not in a school", Teacher.objects.filter(school=None).count(), "" ]) table_data.append([ "Number of teachers with request pending to join a school", Teacher.objects.exclude(pending_join_request=None).count(), "" ]) table_data.append([ "Number of teachers with unverified email address", Teacher.objects.exclude( new_user__email_verifications__verified=True).count(), "" ]) otp_model_names = [model._meta.model_name for model in device_classes()] otp_query = Q() for model_name in otp_model_names: otp_query = otp_query | Q( **{"new_user__%s__name" % model_name: 'default'}) two_factor_teachers = Teacher.objects.filter(otp_query).distinct().count() table_data.append( ["Number of teachers setup with 2FA", two_factor_teachers, ""]) num_of_classes_per_teacher = Teacher.objects.annotate( num_classes=Count('class_teacher')) stats_classes_per_teacher = num_of_classes_per_teacher.aggregate( Avg('num_classes')) num_of_classes_per_active_teacher = num_of_classes_per_teacher.exclude( school=None) stats_classes_per_active_teacher = num_of_classes_per_active_teacher.aggregate( Avg('num_classes')) table_data.append([ "Average number of classes per teacher", stats_classes_per_teacher['num_classes__avg'], "" ]) table_data.append([ "Average number of classes per active teacher", stats_classes_per_active_teacher['num_classes__avg'], "Excludes teachers without a school" ]) table_data.append([ "Number of of teachers with no classes", num_of_classes_per_teacher.filter(num_classes=0).count(), "" ]) table_data.append([ "Number of of active teachers with no classes", num_of_classes_per_active_teacher.filter(num_classes=0).count(), "Excludes teachers without a school" ]) tables.append({ 'title': "Teachers", 'description': "", 'header': table_head, 'data': table_data }) """ Class statistics """ table_data = [] table_data.append(["Number of classes", Class.objects.count(), ""]) num_students_per_class = Class.objects.annotate( num_students=Count('students')) stats_students_per_class = num_students_per_class.aggregate( Avg('num_students')) stats_students_per_active_class = num_students_per_class.exclude(num_students=0) \ .aggregate(Avg('num_students')) table_data.append([ "Average number of students per class", stats_students_per_class['num_students__avg'], "" ]) table_data.append([ "Average number of students per active class", stats_students_per_active_class['num_students__avg'], "Excludes classes which are empty" ]) tables.append({ 'title': "Classes", 'description': "", 'header': table_head, 'data': table_data }) """ Student statistics """ table_data = [] table_data.append(["Number of students", student_count, ""]) independent_students = Student.objects.filter(class_field=None) table_data.append( ["Number of independent students", independent_students.count(), ""]) table_data.append([ "Number of independent students with unverified email address", Student.objects.exclude( new_user__email_verifications__verified=True).count(), "" ]) table_data.append([ "Number of school students", Student.objects.exclude(class_field=None).count(), "" ]) tables.append({ 'title': "Students", 'description': "", 'header': table_head, 'data': table_data }) """ Rapid Router Student Progress statistics """ table_data = [] students_with_attempts = Student.objects.annotate(num_attempts=Count('attempts')) \ .exclude(num_attempts=0) table_data.append([ "Number of students who have started RR", students_with_attempts.count(), "" ]) school_students_with_attempts = students_with_attempts.exclude( class_field=None) table_data.append([ "Number of school students who have started RR", school_students_with_attempts.count(), "" ]) independent_students_with_attempts = students_with_attempts.filter( class_field=None) table_data.append([ "Number of independent students who have started RR", independent_students_with_attempts.count(), "" ]) tables.append({ 'title': "Rapid Router Student Progress", 'description': "", 'header': table_head, 'data': table_data }) return render(request, 'portal/admin/aggregated_data.html', { 'tables': tables, })
def test_otp_login(self): """ A user must enter a code when they've enabled MFA. """ import django_otp # Verify initial conditions self.assertEqual( auth.get_user_model().objects.count(), 1, 'Wrong number of users') for device_model in django_otp.device_classes(): self.assertFalse( device_model.objects.filter(user=self.user).exists(), 'MFA devices exist before provisioning') # Authenticated view fails before logging in self.post('/user/verify_post/', data=self.post_data, status_code=403) # Provision MFA devices totp_device = self.user.totpdevice_set.create( name='Authenticator App') self.assertEqual( self.user.totpdevice_set.filter(confirmed=True).count(), 1, 'Wrong number of app MFA devices after provisioning') # Login prompts for MFA code on login after provisioning login_response = self.post( '/otp/login/', data=dict(username=self.USERNAME, password=self.PASS), status_code=200) # Authenticated view fails before verification self.post('/user/verify_post/', data=self.post_data, status_code=403) self.assertIn( 'key', login_response.json, 'Login response missing token') self.assertIn( 'otp_devices', login_response.json, 'Login response missing MFA/OTP devices') self.assertEqual( len(login_response.json['otp_devices']), 1, 'Login response wrong number of MFA/OTP devices') # Invalid token returns an error otp_response = self.post( '/otp/verify/', data=dict( otp_device=login_response.json['otp_devices'][0][0], otp_token='FOOWRONGTOKEN'), status_code=400) self.assertIn( '__all__', otp_response.json, 'Invalid MFA/OTP token verification response missing error') self.assertIn( 'token', otp_response.json['__all__'][0].lower(), 'Invalid MFA/OTP token verification response wrong message') # Send OTP verification code otp_response = self.post( '/otp/verify/', data=dict( otp_device=login_response.json['otp_devices'][0][0], otp_token=oath.TOTP( totp_device.bin_key, totp_device.step, totp_device.t0, totp_device.digits).token()), status_code=200) self.assertIn( 'success', otp_response.json, 'MFA/OTP token verification response missing success message') # Authenticated views work after verification post_response = self.post( '/user/verify_post/', data=self.post_data, status_code=200) self.assertEqual( post_response.data, self.post_data, 'Wrong post response')
def aggregated_data(request): tables = [] table_head = ["Data description", "Value", "More info"] table_data = [] """ Overall statistics """ teacher_count = Teacher.objects.count() student_count = Student.objects.count() new_profiles_count = User.objects.filter( date_joined__gte=timezone.now() - timedelta(days=7) ).count() table_data.append( [ "Number of users", teacher_count + student_count, "Number of teachers + Number of students", ] ) table_data.append( [ "Number of new users (past week)", new_profiles_count, "Number of user profiles", ] ) tables.append( { "title": "Overall Statistics", "description": "CFL site overall statistics", "header": table_head, "data": table_data, } ) """ School statistics """ table_data = [] table_data.append(["Number of schools signed up", School.objects.count(), ""]) num_of_teachers_per_school = School.objects.annotate( num_teachers=Count("teacher_school") ) stats_teachers_per_school = num_of_teachers_per_school.aggregate( Avg("num_teachers") ) table_data.append( [ "Average number of teachers per school", stats_teachers_per_school["num_teachers__avg"], "", ] ) tables.append( { "title": "Schools or Clubs", "description": "", "header": table_head, "data": table_data, } ) """ Teacher statistics """ table_data = [] table_data.append(["Number of teachers signed up", teacher_count, ""]) table_data.append( [ "Number of teachers not in a school", Teacher.objects.filter(school=None).count(), "", ] ) table_data.append( [ "Number of teachers with request pending to join a school", Teacher.objects.exclude(pending_join_request=None).count(), "", ] ) table_data.append( [ "Number of teachers with unverified email address", Teacher.objects.exclude( new_user__email_verifications__verified=True ).count(), "", ] ) otp_model_names = [model._meta.model_name for model in device_classes()] otp_query = Q() for model_name in otp_model_names: otp_query = otp_query | Q(**{"new_user__%s__name" % model_name: "default"}) two_factor_teachers = Teacher.objects.filter(otp_query).distinct().count() table_data.append(["Number of teachers setup with 2FA", two_factor_teachers, ""]) num_of_classes_per_teacher = Teacher.objects.annotate( num_classes=Count("class_teacher") ) stats_classes_per_teacher = num_of_classes_per_teacher.aggregate(Avg("num_classes")) num_of_classes_per_active_teacher = num_of_classes_per_teacher.exclude(school=None) stats_classes_per_active_teacher = num_of_classes_per_active_teacher.aggregate( Avg("num_classes") ) table_data.append( [ "Average number of classes per teacher", stats_classes_per_teacher["num_classes__avg"], "", ] ) table_data.append( [ "Average number of classes per active teacher", stats_classes_per_active_teacher["num_classes__avg"], "Excludes teachers without a school", ] ) table_data.append( [ "Number of of teachers with no classes", num_of_classes_per_teacher.filter(num_classes=0).count(), "", ] ) table_data.append( [ "Number of of active teachers with no classes", num_of_classes_per_active_teacher.filter(num_classes=0).count(), "Excludes teachers without a school", ] ) tables.append( { "title": "Teachers", "description": "", "header": table_head, "data": table_data, } ) """ Class statistics """ table_data = [] table_data.append(["Number of classes", Class.objects.count(), ""]) num_students_per_class = Class.objects.annotate(num_students=Count("students")) stats_students_per_class = num_students_per_class.aggregate(Avg("num_students")) stats_students_per_active_class = num_students_per_class.exclude( num_students=0 ).aggregate(Avg("num_students")) table_data.append( [ "Average number of students per class", stats_students_per_class["num_students__avg"], "", ] ) table_data.append( [ "Average number of students per active class", stats_students_per_active_class["num_students__avg"], "Excludes classes which are empty", ] ) tables.append( { "title": "Classes", "description": "", "header": table_head, "data": table_data, } ) """ Student statistics """ table_data = [] table_data.append(["Number of students", student_count, ""]) independent_students = Student.objects.filter(class_field=None) table_data.append( ["Number of independent students", independent_students.count(), ""] ) table_data.append( [ "Number of independent students with unverified email address", Student.objects.exclude( new_user__email_verifications__verified=True ).count(), "", ] ) table_data.append( [ "Number of school students", Student.objects.exclude(class_field=None).count(), "", ] ) tables.append( { "title": "Students", "description": "", "header": table_head, "data": table_data, } ) """ Rapid Router Student Progress statistics """ table_data = [] students_with_attempts = Student.objects.annotate( num_attempts=Count("attempts") ).exclude(num_attempts=0) table_data.append( ["Number of students who have started RR", students_with_attempts.count(), ""] ) school_students_with_attempts = students_with_attempts.exclude(class_field=None) table_data.append( [ "Number of school students who have started RR", school_students_with_attempts.count(), "", ] ) independent_students_with_attempts = students_with_attempts.filter(class_field=None) table_data.append( [ "Number of independent students who have started RR", independent_students_with_attempts.count(), "", ] ) tables.append( { "title": "Rapid Router Student Progress", "description": "", "header": table_head, "data": table_data, } ) return render(request, "portal/admin/aggregated_data.html", {"tables": tables})
def aggregated_data(request): tables = [] table_head = ["Data description", "Value", "More info"] table_data = [] """ Overall statistics """ teacher_count = Teacher.objects.count() student_count = Student.objects.count() new_profiles_count = UserProfile.objects.filter(user__date_joined__gte=timezone.now() - timedelta(days=7)).count() table_data.append(["Number of users", teacher_count+student_count, "Number of teachers + Number of students"]) table_data.append(["Number of new users (past week)", new_profiles_count, "Number of user profiles"]) tables.append({'title': "Overall Statistics", 'description': "CFL site overall statistics", 'header': table_head, 'data': table_data}) """ School statistics """ table_data = [] table_data.append(["Number of schools signed up", School.objects.count(), ""]) num_of_teachers_per_school = School.objects.annotate(num_teachers=Count('teacher_school')) stats_teachers_per_school = num_of_teachers_per_school.aggregate(Avg('num_teachers')) table_data.append(["Average number of teachers per school", stats_teachers_per_school['num_teachers__avg'], ""]) tables.append({'title': "Schools or Clubs", 'description': "", 'header': table_head, 'data': table_data}) """ Teacher statistics """ table_data = [] table_data.append(["Number of teachers signed up", teacher_count, ""]) table_data.append(["Number of teachers not in a school", Teacher.objects.filter(school=None).count(), ""]) table_data.append(["Number of teachers with request pending to join a school", Teacher.objects.exclude(pending_join_request=None).count(), ""]) table_data.append(["Number of teachers with unverified email address", Teacher.objects.filter(user__awaiting_email_verification=True).count(), ""]) otp_model_names = [model._meta.model_name for model in device_classes()] otp_query = Q() for model_name in otp_model_names: otp_query = otp_query | Q(**{"user__user__%s__name" % model_name: 'default'}) two_factor_teachers = Teacher.objects.filter(otp_query).distinct().count() table_data.append(["Number of teachers setup with 2FA", two_factor_teachers, ""]) num_of_classes_per_teacher = Teacher.objects.annotate(num_classes=Count('class_teacher')) stats_classes_per_teacher = num_of_classes_per_teacher.aggregate(Avg('num_classes')) num_of_classes_per_active_teacher = num_of_classes_per_teacher.exclude(school=None) stats_classes_per_active_teacher = num_of_classes_per_active_teacher.aggregate(Avg('num_classes')) table_data.append(["Average number of classes per teacher", stats_classes_per_teacher['num_classes__avg'], ""]) table_data.append(["Average number of classes per active teacher", stats_classes_per_active_teacher['num_classes__avg'], "Excludes teachers without a school"]) table_data.append(["Number of of teachers with no classes", num_of_classes_per_teacher.filter(num_classes=0).count(), ""]) table_data.append(["Number of of active teachers with no classes", num_of_classes_per_active_teacher.filter(num_classes=0).count(), "Excludes teachers without a school"]) tables.append({'title': "Teachers", 'description': "", 'header': table_head, 'data': table_data}) """ Class statistics """ table_data = [] table_data.append(["Number of classes", Class.objects.count(), ""]) num_students_per_class = Class.objects.annotate(num_students=Count('students')) stats_students_per_class = num_students_per_class.aggregate(Avg('num_students')) stats_students_per_active_class = num_students_per_class.exclude(num_students=0).aggregate(Avg('num_students')) table_data.append(["Average number of students per class", stats_students_per_class['num_students__avg'], ""]) table_data.append(["Average number of students per active class", stats_students_per_active_class['num_students__avg'], "Excludes classes which are empty"]) tables.append({'title': "Classes", 'description': "", 'header': table_head, 'data': table_data}) """ Student statistics """ table_data = [] table_data.append(["Number of students", student_count, ""]) independent_students = Student.objects.filter(class_field=None) table_data.append(["Number of independent students", independent_students.count(), ""]) table_data.append(["Number of independent students with unverified email address", independent_students.filter(user__awaiting_email_verification=True).count(), ""]) table_data.append(["Number of school students", Student.objects.exclude(class_field=None).count(), ""]) tables.append({'title': "Students", 'description': "", 'header': table_head, 'data': table_data}) """ Rapid Router Student Progress statistics """ table_data = [] students_with_attempts = Student.objects.annotate(num_attempts=Count('attempts')).exclude(num_attempts=0) table_data.append(["Number of students who have started RR", students_with_attempts.count(), ""]) school_students_with_attempts = students_with_attempts.exclude(class_field=None) table_data.append(["Number of school students who have started RR", school_students_with_attempts.count(), ""]) independent_students_with_attempts = students_with_attempts.filter(class_field=None) table_data.append(["Number of independent students who have started RR", independent_students_with_attempts.count(), ""]) tables.append({'title': "Rapid Router Student Progress", 'description': "", 'header': table_head, 'data': table_data}) """ Rapid Router Levels statistics """ table_data = [] num_user_levels = UserProfile.objects.annotate(num_custom_levels=Count('levels')).exclude(num_custom_levels=0) stats_user_levels = num_user_levels.aggregate(Avg('num_custom_levels')) table_data.append(["Number of users with custom levels", num_user_levels.count(), ""]) table_data.append(["Of users with custom levels, average number of custom levels", stats_user_levels['num_custom_levels__avg'], ""]) num_teacher_levels = num_user_levels.exclude(teacher=None) stats_teacher_levels = num_teacher_levels.aggregate(Avg('num_custom_levels')) table_data.append(["Number of teachers with custom levels", num_teacher_levels.count(), ""]) table_data.append(["Of teachers with custom levels, average number of custom levels", stats_teacher_levels['num_custom_levels__avg'], ""]) num_student_levels = num_user_levels.exclude(student=None) stats_student_levels = num_student_levels.aggregate(Avg('num_custom_levels')) table_data.append(["Number of students with custom levels", num_student_levels.count(), ""]) table_data.append(["Of students with custom levels, average number of custom levels", stats_student_levels['num_custom_levels__avg'], ""]) num_school_student_levels = num_student_levels.exclude(student__class_field=None) stats_school_student_levels = num_school_student_levels.aggregate(Avg('num_custom_levels')) table_data.append(["Number of school students with custom levels", num_school_student_levels.count(), ""]) table_data.append(["Of school students with custom levels, average number of custom levels", stats_school_student_levels['num_custom_levels__avg'], ""]) num_independent_student_levels = num_student_levels.filter(student__class_field=None) stats_independent_student_levels = num_independent_student_levels.aggregate(Avg('num_custom_levels')) table_data.append(["Number of independent students with custom levels", num_independent_student_levels.count(), ""]) table_data.append(["Of independent students with custom levels, average number of custom levels", stats_independent_student_levels['num_custom_levels__avg'], ""]) tables.append({'title': "Rapid Router Levels", 'description': "", 'header': table_head, 'data': table_data}) """ Rapid Router Workspaces statistics """ table_data = [] num_user_workspaces = UserProfile.objects.annotate(num_saved_workspaces=Count('workspaces')).exclude(num_saved_workspaces=0) stats_user_workspaces = num_user_workspaces.aggregate(Avg('num_saved_workspaces')) table_data.append(["Number of users with saved workspaces", num_user_workspaces.count(), ""]) table_data.append(["Of users with saved workspaces, average number of saved workspaces", stats_user_workspaces['num_saved_workspaces__avg'], ""]) num_teacher_workspaces = num_user_workspaces.exclude(teacher=None) stats_teacher_workspaces = num_teacher_workspaces.aggregate(Avg('num_saved_workspaces')) table_data.append(["Number of teachers with saved workspaces", num_teacher_workspaces.count(), ""]) table_data.append(["Of teachers with saved workspaces, average number of saved workspaces", stats_teacher_workspaces['num_saved_workspaces__avg'], ""]) num_student_workspaces = num_user_workspaces.exclude(student=None) stats_student_workspaces = num_student_workspaces.aggregate(Avg('num_saved_workspaces')) table_data.append(["Number of students with saved workspaces", num_student_workspaces.count(), ""]) table_data.append(["Of students with saved workspaces, average number of saved workspaces", stats_student_workspaces['num_saved_workspaces__avg'], ""]) num_school_student_workspaces = num_student_workspaces.exclude(student__class_field=None) stats_school_student_workspaces = num_school_student_workspaces.aggregate(Avg('num_saved_workspaces')) table_data.append(["Number of school students with saved workspaces", num_school_student_workspaces.count(), ""]) table_data.append(["Of school students with saved workspaces, average number of saved workspaces", stats_school_student_workspaces['num_saved_workspaces__avg'], ""]) num_independent_student_workspaces = num_student_workspaces.filter(student__class_field=None) stats_independent_student_workspaces = num_independent_student_workspaces.aggregate(Avg('num_saved_workspaces')) table_data.append(["Number of independent students with saved workspaces", num_independent_student_workspaces.count(), ""]) table_data.append(["Of independent students with saved workspaces, average number of saved workspaces", stats_independent_student_workspaces['num_saved_workspaces__avg'], ""]) tables.append({'title': "Rapid Router Workspaces", 'description': "", 'header': table_head, 'data': table_data}) return render(request, 'portal/admin/aggregated_data.html', { 'tables': tables, })
def aggregated_data(request): tables = [] table_head = ["Data description", "Value", "More info"] table_data = [] """ Overall statistics """ teacher_count = Teacher.objects.count() student_count = Student.objects.count() table_data.append(["Number of users", teacher_count + student_count, "Number of teachers + Number of students"]) tables.append( { "title": "Overall Statistics", "description": "CFL site overall statistics", "header": table_head, "data": table_data, } ) """ School statistics """ table_data = [] table_data.append(["Number of schools signed up", School.objects.count(), ""]) num_of_teachers_per_school = School.objects.annotate(num_teachers=Count("teacher_school")) stats_teachers_per_school = num_of_teachers_per_school.aggregate(Avg("num_teachers")) table_data.append(["Average number of teachers per school", stats_teachers_per_school["num_teachers__avg"], ""]) tables.append({"title": "Schools or Clubs", "description": "", "header": table_head, "data": table_data}) """ Teacher statistics """ table_data = [] table_data.append(["Number of teachers signed up", teacher_count, ""]) table_data.append(["Number of teachers not in a school", Teacher.objects.filter(school=None).count(), ""]) table_data.append( [ "Number of teachers with request pending to join a school", Teacher.objects.exclude(pending_join_request=None).count(), "", ] ) table_data.append( [ "Number of teachers with unverified email address", Teacher.objects.filter(user__awaiting_email_verification=True).count(), "", ] ) otp_model_names = [model._meta.model_name for model in device_classes()] otp_query = Q() for model_name in otp_model_names: otp_query = otp_query | Q(**{"user__user__%s__name" % model_name: "default"}) two_factor_teachers = Teacher.objects.filter(otp_query).distinct().count() table_data.append(["Number of teachers setup with 2FA", two_factor_teachers, ""]) num_of_classes_per_teacher = Teacher.objects.annotate(num_classes=Count("class_teacher")) stats_classes_per_teacher = num_of_classes_per_teacher.aggregate(Avg("num_classes")) num_of_classes_per_active_teacher = num_of_classes_per_teacher.exclude(school=None) stats_classes_per_active_teacher = num_of_classes_per_active_teacher.aggregate(Avg("num_classes")) table_data.append(["Average number of classes per teacher", stats_classes_per_teacher["num_classes__avg"], ""]) table_data.append( [ "Average number of classes per active teacher", stats_classes_per_active_teacher["num_classes__avg"], "Excludes teachers without a school", ] ) table_data.append( ["Number of of teachers with no classes", num_of_classes_per_teacher.filter(num_classes=0).count(), ""] ) table_data.append( [ "Number of of active teachers with no classes", num_of_classes_per_active_teacher.filter(num_classes=0).count(), "Excludes teachers without a school", ] ) tables.append({"title": "Teachers", "description": "", "header": table_head, "data": table_data}) """ Class statistics """ table_data = [] table_data.append(["Number of classes", Class.objects.count(), ""]) num_students_per_class = Class.objects.annotate(num_students=Count("students")) stats_students_per_class = num_students_per_class.aggregate(Avg("num_students")) stats_students_per_active_class = num_students_per_class.exclude(num_students=0).aggregate(Avg("num_students")) table_data.append(["Average number of students per class", stats_students_per_class["num_students__avg"], ""]) table_data.append( [ "Average number of students per active class", stats_students_per_active_class["num_students__avg"], "Excludes classes which are empty", ] ) tables.append({"title": "Classes", "description": "", "header": table_head, "data": table_data}) """ Student statistics """ table_data = [] table_data.append(["Number of students", student_count, ""]) independent_students = Student.objects.filter(class_field=None) table_data.append(["Number of independent students", independent_students.count(), ""]) table_data.append( [ "Number of independent students with unverified email address", independent_students.filter(user__awaiting_email_verification=True).count(), "", ] ) table_data.append(["Number of school students", Student.objects.exclude(class_field=None).count(), ""]) tables.append({"title": "Students", "description": "", "header": table_head, "data": table_data}) """ Rapid Router Student Progress statistics """ table_data = [] students_with_attempts = Student.objects.annotate(num_attempts=Count("attempts")).exclude(num_attempts=0) table_data.append(["Number of students who have started RR", students_with_attempts.count(), ""]) school_students_with_attempts = students_with_attempts.exclude(class_field=None) table_data.append(["Number of school students who have started RR", school_students_with_attempts.count(), ""]) independent_students_with_attempts = students_with_attempts.filter(class_field=None) table_data.append( ["Number of independent students who have started RR", independent_students_with_attempts.count(), ""] ) # TODO revisit this once episodes have been restructured, as this doesn't work because of episode being a PROPERTY of level... # Need to filter out so we're only looking at attempts on levels that could be relevant, and don't look at null scores # default_level_attempts = Attempt.objects.filter(level__default=True).exclude(level__episode=None).exclude(level__episode__in_development=True).exclude(score=None) # table_data.append(["Average score recorded on default RR levels", default_level_attempts.aggregate(Avg('score'))['score__avg'], ""]) # perfect_default_level_attempts = default_level_attempts.filter(score=20) # perfect_attempts = perfect_default_level_attempts.count() # all_attempts = default_level_attempts.count() # percentage = None # if all_attempts != 0: # percentage = (float(perfect_attempts)/float(all_attempts))*100 # table_data.append(["Percentage of perfect scores on default RR levels", percentage, ""]) # school_default_level_attempts = default_level_attempts.exclude(student__class_field=None) # table_data.append(["Average score recorded amongst school students on default RR levels", school_default_level_attempts.aggregate(Avg('score'))['score__avg'], ""]) # school_perfect_default_level_attempts = school_default_level_attempts.filter(score=20) # school_perfect_attempts = school_perfect_default_level_attempts.count() # school_all_attempts = school_default_level_attempts.count() # percentage = None # if school_all_attempts != 0: # percentage = (float(school_perfect_attempts)/float(school_all_attempts))*100 # table_data.append(["Percentage of perfect scores amongst school students on default RR levels", percentage, ""]) # independent_default_level_attempts = default_level_attempts.filter(student__class_field=None) # table_data.append(["Average score recorded amongst independent students on default RR levels", independent_default_level_attempts.aggregate(Avg('score'))['score__avg'], ""]) # independent_perfect_default_level_attempts = independent_default_level_attempts.filter(score=20) # independent_perfect_attempts = independent_perfect_default_level_attempts.count() # independent_all_attempts = independent_default_level_attempts.count() # percentage = None # if independent_all_attempts != 0: # percentage = (float(independent_perfect_attempts)/float(independent_all_attempts))*100 # table_data.append(["Percentage of perfect scores amongst independent students on default RR levels", percentage, ""]) # student_attempts_on_default_levels = default_level_attempts.values('student').annotate(num_completed=Count('level')) # school_student_attempts_on_default_levels = school_default_level_attempts.values('student').annotate(num_completed=Count('level')) # independent_student_attempts_on_default_levels = independent_default_level_attempts.values('student').annotate(num_completed=Count('level')) # avg_levels_completed = student_attempts_on_default_levels.aggregate(Avg('num_completed'))['num_completed__avg'] # table_data.append(["Average number of levels completed by students", avg_levels_completed, ""]) # avg_levels_completed = school_student_attempts_on_default_levels.aggregate(Avg('num_completed'))['num_completed__avg'] # table_data.append(["Average number of levels completed by school students", avg_levels_completed, ""]) # avg_levels_completed = independent_student_attempts_on_default_levels.aggregate(Avg('num_completed'))['num_completed__avg'] # table_data.append(["Average number of levels completed by independent students", avg_levels_completed, ""]) # default_levels = Level.objects.filter(default=True) # available_levels = [level for level in default_levels if level.episode and not level.episode.in_development] # print len(available_levels) tables.append( {"title": "Rapid Router Student Progress", "description": "", "header": table_head, "data": table_data} ) """ Rapid Router Levels statistics """ table_data = [] num_user_levels = UserProfile.objects.annotate(num_custom_levels=Count("levels")).exclude(num_custom_levels=0) stats_user_levels = num_user_levels.aggregate(Avg("num_custom_levels")) table_data.append(["Number of users with custom levels", num_user_levels.count(), ""]) table_data.append( [ "Of users with custom levels, average number of custom levels", stats_user_levels["num_custom_levels__avg"], "", ] ) num_teacher_levels = num_user_levels.exclude(teacher=None) stats_teacher_levels = num_teacher_levels.aggregate(Avg("num_custom_levels")) table_data.append(["Number of teachers with custom levels", num_teacher_levels.count(), ""]) table_data.append( [ "Of teachers with custom levels, average number of custom levels", stats_teacher_levels["num_custom_levels__avg"], "", ] ) num_student_levels = num_user_levels.exclude(student=None) stats_student_levels = num_student_levels.aggregate(Avg("num_custom_levels")) table_data.append(["Number of students with custom levels", num_student_levels.count(), ""]) table_data.append( [ "Of students with custom levels, average number of custom levels", stats_student_levels["num_custom_levels__avg"], "", ] ) num_school_student_levels = num_student_levels.exclude(student__class_field=None) stats_school_student_levels = num_school_student_levels.aggregate(Avg("num_custom_levels")) table_data.append(["Number of school students with custom levels", num_school_student_levels.count(), ""]) table_data.append( [ "Of school students with custom levels, average number of custom levels", stats_school_student_levels["num_custom_levels__avg"], "", ] ) num_independent_student_levels = num_student_levels.filter(student__class_field=None) stats_independent_student_levels = num_independent_student_levels.aggregate(Avg("num_custom_levels")) table_data.append(["Number of independent students with custom levels", num_independent_student_levels.count(), ""]) table_data.append( [ "Of independent students with custom levels, average number of custom levels", stats_independent_student_levels["num_custom_levels__avg"], "", ] ) tables.append({"title": "Rapid Router Levels", "description": "", "header": table_head, "data": table_data}) return render(request, "portal/admin/aggregated_data.html", {"tables": tables})