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