def test_calculate_average_course_distribution(self):
mommy.make(RatingAnswerCounter, question=self.question_grade, contribution=self.contribution1, answer=1, count=2)
course = self.evaluation.course
single_result = mommy.make(
Evaluation,
name_de="Single Result",
name_en="Single Result",
course=course,
weight=3,
is_single_result=True,
vote_start_datetime=datetime.now(),
vote_end_date=datetime.now().date(),
state="published",
)
single_result_questionnaire = Questionnaire.single_result_questionnaire()
single_result_question = single_result_questionnaire.questions.first()
contribution = mommy.make(Contribution, evaluation=single_result, contributor=None, questionnaires=[single_result_questionnaire])
mommy.make(RatingAnswerCounter, question=single_result_question, contribution=contribution, answer=2, count=1)
mommy.make(RatingAnswerCounter, question=single_result_question, contribution=contribution, answer=3, count=1)
distribution = calculate_average_course_distribution(course)
self.assertEqual(distribution[0], 0.25)
self.assertEqual(distribution[1], 0.375)
self.assertEqual(distribution[2], 0.375)
self.assertEqual(distribution[3], 0)
self.assertEqual(distribution[4], 0)
def test_calculate_average_course_distribution(self):
make_rating_answer_counters(self.question_grade, self.contribution1,
[2, 0, 0, 0, 0])
course = self.evaluation.course
single_result = baker.make(
Evaluation,
name_de="Single Result",
name_en="Single Result",
course=course,
weight=3,
is_single_result=True,
vote_start_datetime=datetime.now(),
vote_end_date=datetime.now().date(),
state=Evaluation.State.PUBLISHED,
)
single_result_questionnaire = Questionnaire.single_result_questionnaire(
)
single_result_question = single_result_questionnaire.questions.first()
contribution = baker.make(Contribution,
evaluation=single_result,
contributor=None,
questionnaires=[single_result_questionnaire])
make_rating_answer_counters(single_result_question, contribution,
[0, 1, 1, 0, 0])
cache_results(single_result)
cache_results(self.evaluation)
distribution = calculate_average_course_distribution(course)
self.assertEqual(distribution[0], 0.25)
self.assertEqual(distribution[1], 0.375)
self.assertEqual(distribution[2], 0.375)
self.assertEqual(distribution[3], 0)
self.assertEqual(distribution[4], 0)
def test_calculate_average_course_distribution(self):
baker.make(RatingAnswerCounter, question=self.question_grade, contribution=self.contribution1, answer=1, count=2)
course = self.evaluation.course
single_result = baker.make(
Evaluation,
name_de="Single Result",
name_en="Single Result",
course=course,
weight=3,
is_single_result=True,
vote_start_datetime=datetime.now(),
vote_end_date=datetime.now().date(),
state="published",
)
single_result_questionnaire = Questionnaire.single_result_questionnaire()
single_result_question = single_result_questionnaire.questions.first()
contribution = baker.make(Contribution, evaluation=single_result, contributor=None, questionnaires=[single_result_questionnaire])
baker.make(RatingAnswerCounter, question=single_result_question, contribution=contribution, answer=2, count=1)
baker.make(RatingAnswerCounter, question=single_result_question, contribution=contribution, answer=3, count=1)
cache_results(single_result)
cache_results(self.evaluation)
distribution = calculate_average_course_distribution(course)
self.assertEqual(distribution[0], 0.25)
self.assertEqual(distribution[1], 0.375)
self.assertEqual(distribution[2], 0.375)
self.assertEqual(distribution[3], 0)
self.assertEqual(distribution[4], 0)
def filter_evaluations(semesters, evaluation_states, degrees, course_types, contributor, include_not_enough_voters):
course_results_exist = False
evaluations_with_results = list()
used_questionnaires = set()
evaluations_filter = Q(course__semester__in=semesters, state__in=evaluation_states, course__degrees__in=degrees, course__type__in=course_types)
if contributor:
evaluations_filter = evaluations_filter & (Q(course__responsibles__in=[contributor]) | Q(contributions__contributor__in=[contributor]))
evaluations = Evaluation.objects.filter(evaluations_filter).distinct()
for evaluation in evaluations:
if evaluation.is_single_result:
continue
if not evaluation.can_publish_rating_results and not include_not_enough_voters:
continue
results = OrderedDict()
for contribution_result in get_results(evaluation).contribution_results:
for questionnaire_result in contribution_result.questionnaire_results:
# RatingQuestion.counts is a tuple of integers or None, if this tuple is all zero, we want to exclude it
if all(not question_result.question.is_rating_question or question_result.counts is None or sum(question_result.counts) == 0 for question_result in questionnaire_result.question_results):
continue
if not contributor or contribution_result.contributor is None or contribution_result.contributor == contributor:
results.setdefault(questionnaire_result.questionnaire.id, []).extend(questionnaire_result.question_results)
used_questionnaires.add(questionnaire_result.questionnaire)
evaluation.course_evaluations_count = evaluation.course.evaluations.count()
if evaluation.course_evaluations_count > 1:
course_results_exist = True
evaluation.weight_percentage = int((evaluation.weight / sum(evaluation.weight for evaluation in evaluation.course.evaluations.all())) * 100)
evaluation.course.avg_grade = distribution_to_grade(calculate_average_course_distribution(evaluation.course))
evaluations_with_results.append((evaluation, results))
evaluations_with_results.sort(key=lambda cr: (cr[0].course.semester.id, cr[0].course.type.order, cr[0].full_name))
used_questionnaires = sorted(used_questionnaires)
return evaluations_with_results, used_questionnaires, course_results_exist
def export(self, response, course_types_list, include_not_enough_voters=False, include_unpublished=False):
self.workbook = xlwt.Workbook()
self.init_styles(self.workbook)
counter = 1
course_results_exist = False
for course_types in course_types_list:
self.sheet = self.workbook.add_sheet("Sheet " + str(counter))
counter += 1
self.row = 0
self.col = 0
evaluations_with_results = list()
evaluation_states = ['published']
if include_unpublished:
evaluation_states.extend(['evaluated', 'reviewed'])
used_questionnaires = set()
for evaluation in self.semester.evaluations.filter(state__in=evaluation_states, course__type__in=course_types).all():
if evaluation.is_single_result:
continue
if not evaluation.can_publish_rating_results and not include_not_enough_voters:
continue
results = OrderedDict()
for questionnaire_result in collect_results(evaluation).questionnaire_results:
if all(not question_result.question.is_rating_question or question_result.counts is None for question_result in questionnaire_result.question_results):
continue
results.setdefault(questionnaire_result.questionnaire.id, []).extend(questionnaire_result.question_results)
used_questionnaires.add(questionnaire_result.questionnaire)
evaluation.course_evaluations_count = evaluation.course.evaluations.count()
if evaluation.course_evaluations_count > 1:
course_results_exist = True
evaluation.weight_percentage = int((evaluation.weight / sum(evaluation.weight for evaluation in evaluation.course.evaluations.all())) * 100)
evaluation.course.avg_grade = distribution_to_grade(calculate_average_course_distribution(evaluation.course))
evaluations_with_results.append((evaluation, results))
evaluations_with_results.sort(key=lambda cr: (cr[0].course.type.order, cr[0].full_name))
used_questionnaires = sorted(used_questionnaires)
course_type_names = [ct.name for ct in CourseType.objects.filter(pk__in=course_types)]
writec(self, _("Evaluation {0}\n\n{1}").format(self.semester.name, ", ".join(course_type_names)), "headline")
for evaluation, results in evaluations_with_results:
writec(self, evaluation.full_name, "evaluation")
writen(self, _("Course Degrees"), "bold")
for evaluation, results in evaluations_with_results:
writec(self, "\n".join([d.name for d in evaluation.course.degrees.all()]), "degree")
writen(self, _("Course Type"), "bold")
for evaluation, results in evaluations_with_results:
writec(self, evaluation.course.type.name, "border_left_right")
writen(self)
for evaluation, results in evaluations_with_results:
self.write_empty_cell_with_borders()
for questionnaire in used_questionnaires:
writen(self, questionnaire.name, "bold")
for evaluation, results in evaluations_with_results:
self.write_empty_cell_with_borders()
filtered_questions = self.filter_text_and_heading_questions(questionnaire.questions.all())
for question in filtered_questions:
if question.is_heading_question:
writen(self, question.text, "italic")
else:
writen(self, question.text)
for evaluation, results in evaluations_with_results:
if questionnaire.id not in results or question.is_heading_question:
self.write_empty_cell_with_borders()
continue
qn_results = results[questionnaire.id]
values = []
count_sum = 0
approval_count = 0
for grade_result in qn_results:
if grade_result.question.id == question.id:
if grade_result.has_answers:
values.append(grade_result.average * grade_result.count_sum)
count_sum += grade_result.count_sum
if grade_result.question.is_yes_no_question:
approval_count += grade_result.approval_count
if values:
avg = sum(values) / count_sum
if question.is_yes_no_question:
percent_approval = approval_count / count_sum if count_sum > 0 else 0
writec(self, "{:.0%}".format(percent_approval), self.grade_to_style(avg))
else:
writec(self, avg, self.grade_to_style(avg))
else:
self.write_empty_cell_with_borders()
writen(self)
for evaluation, results in evaluations_with_results:
self.write_empty_cell_with_borders()
writen(self, _("Overall Average Grade"), "bold")
for evaluation, results in evaluations_with_results:
avg = distribution_to_grade(calculate_average_distribution(evaluation))
if avg:
writec(self, avg, self.grade_to_style(avg))
else:
self.write_empty_cell_with_borders()
writen(self, _("Total voters/Total participants"), "bold")
for evaluation, results in evaluations_with_results:
writec(self, "{}/{}".format(evaluation.num_voters, evaluation.num_participants), "total_voters")
writen(self, _("Evaluation rate"), "bold")
for evaluation, results in evaluations_with_results:
# round down like in progress bar
percentage_participants = int((evaluation.num_voters / evaluation.num_participants) * 100) if evaluation.num_participants > 0 else 0
writec(self, "{}%".format(percentage_participants), "evaluation_rate")
if course_results_exist:
writen(self)
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
self.write_empty_cell_with_borders()
else:
self.write_empty_cell()
writen(self, _("Evaluation weight"), "bold")
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
writec(self, "{}%".format(evaluation.weight_percentage), "evaluation_weight")
else:
self.write_empty_cell()
writen(self, _("Course Grade"), "bold")
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
if evaluation.course.avg_grade:
writec(self, evaluation.course.avg_grade, self.grade_to_style(evaluation.course.avg_grade))
else:
self.write_empty_cell_with_borders()
else:
self.write_empty_cell()
writen(self)
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
writec(self, None, "border_top")
else:
self.write_empty_cell()
self.workbook.save(response)
def export(self,
response,
selection_list,
include_not_enough_voters=False,
include_unpublished=False):
self.workbook = xlwt.Workbook()
self.init_styles(self.workbook)
counter = 1
course_results_exist = False
for degrees, course_types in selection_list:
self.sheet = self.workbook.add_sheet("Sheet " + str(counter))
counter += 1
self.row = 0
self.col = 0
evaluations_with_results = list()
evaluation_states = ['published']
if include_unpublished:
evaluation_states.extend(['evaluated', 'reviewed'])
used_questionnaires = set()
for evaluation in self.semester.evaluations.filter(
state__in=evaluation_states,
course__degrees__in=degrees,
course__type__in=course_types).distinct():
if evaluation.is_single_result:
continue
if not evaluation.can_publish_rating_results and not include_not_enough_voters:
continue
results = OrderedDict()
for questionnaire_result in collect_results(
evaluation).questionnaire_results:
if all(not question_result.question.is_rating_question
or question_result.counts is None
for question_result in
questionnaire_result.question_results):
continue
results.setdefault(
questionnaire_result.questionnaire.id,
[]).extend(questionnaire_result.question_results)
used_questionnaires.add(questionnaire_result.questionnaire)
evaluation.course_evaluations_count = evaluation.course.evaluations.count(
)
if evaluation.course_evaluations_count > 1:
course_results_exist = True
evaluation.weight_percentage = int(
(evaluation.weight / sum(evaluation.weight
for evaluation in evaluation.
course.evaluations.all())) *
100)
evaluation.course.avg_grade = distribution_to_grade(
calculate_average_course_distribution(
evaluation.course))
evaluations_with_results.append((evaluation, results))
evaluations_with_results.sort(
key=lambda cr: (cr[0].course.type.order, cr[0].full_name))
used_questionnaires = sorted(used_questionnaires)
degree_names = [
degree.name for degree in Degree.objects.filter(pk__in=degrees)
]
course_type_names = [
course_type.name for course_type in CourseType.objects.filter(
pk__in=course_types)
]
writec(
self,
_("Evaluation {}\n\n{}\n\n{}").format(
self.semester.name, ", ".join(degree_names),
", ".join(course_type_names)), "headline")
for evaluation, results in evaluations_with_results:
writec(self, evaluation.full_name, "evaluation")
writen(self, _("Degrees"), "bold")
for evaluation, results in evaluations_with_results:
writec(
self, "\n".join(
[d.name for d in evaluation.course.degrees.all()]),
"degree")
writen(self, _("Course Type"), "bold")
for evaluation, results in evaluations_with_results:
writec(self, evaluation.course.type.name, "border_left_right")
writen(self)
for evaluation, results in evaluations_with_results:
self.write_empty_cell_with_borders()
for questionnaire in used_questionnaires:
writen(self, questionnaire.name, "bold")
for evaluation, results in evaluations_with_results:
self.write_empty_cell_with_borders()
filtered_questions = self.filter_text_and_heading_questions(
questionnaire.questions.all())
for question in filtered_questions:
if question.is_heading_question:
writen(self, question.text, "italic")
else:
writen(self, question.text)
for evaluation, results in evaluations_with_results:
if questionnaire.id not in results or question.is_heading_question:
self.write_empty_cell_with_borders()
continue
qn_results = results[questionnaire.id]
values = []
count_sum = 0
approval_count = 0
for grade_result in qn_results:
if grade_result.question.id == question.id:
if grade_result.has_answers:
values.append(grade_result.average *
grade_result.count_sum)
count_sum += grade_result.count_sum
if grade_result.question.is_yes_no_question:
approval_count += grade_result.approval_count
if values:
avg = sum(values) / count_sum
if question.is_yes_no_question:
percent_approval = approval_count / count_sum if count_sum > 0 else 0
writec(self, "{:.0%}".format(percent_approval),
self.grade_to_style(avg))
else:
writec(self, avg, self.grade_to_style(avg))
else:
self.write_empty_cell_with_borders()
writen(self)
for evaluation, results in evaluations_with_results:
self.write_empty_cell_with_borders()
writen(self, _("Overall Average Grade"), "bold")
for evaluation, results in evaluations_with_results:
avg = distribution_to_grade(
calculate_average_distribution(evaluation))
if avg:
writec(self, avg, self.grade_to_style(avg))
else:
self.write_empty_cell_with_borders()
writen(self, _("Total voters/Total participants"), "bold")
for evaluation, results in evaluations_with_results:
writec(
self, "{}/{}".format(evaluation.num_voters,
evaluation.num_participants),
"total_voters")
writen(self, _("Evaluation rate"), "bold")
for evaluation, results in evaluations_with_results:
# round down like in progress bar
percentage_participants = int(
(evaluation.num_voters / evaluation.num_participants) *
100) if evaluation.num_participants > 0 else 0
writec(self, "{}%".format(percentage_participants),
"evaluation_rate")
if course_results_exist:
writen(self)
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
self.write_empty_cell_with_borders()
else:
self.write_empty_cell()
writen(self, _("Evaluation weight"), "bold")
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
writec(self,
"{}%".format(evaluation.weight_percentage),
"evaluation_weight")
else:
self.write_empty_cell()
writen(self, _("Course Grade"), "bold")
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
if evaluation.course.avg_grade:
writec(
self, evaluation.course.avg_grade,
self.grade_to_style(
evaluation.course.avg_grade))
else:
self.write_empty_cell_with_borders()
else:
self.write_empty_cell()
writen(self)
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
writec(self, None, "border_top")
else:
self.write_empty_cell()
self.workbook.save(response)
def export(self,
response,
semesters,
selection_list,
include_not_enough_voters=False,
include_unpublished=False,
contributor=None):
# the excel file we're creating here is rather complex. However, from the nature of a single
# file, it doesn't make much sense to split up the code into different methods as they will
# always be tightly coupled based on the layout of the sheet. We thus think that one big method
# containing the business logic is okay here
# pylint: disable=too-many-locals, too-many-nested-blocks, too-many-branches, too-many-statements
workbook = xlwt.Workbook()
self.init_styles(workbook)
counter = 1
course_results_exist = False
for degrees, course_types in selection_list:
self.sheet = workbook.add_sheet("Sheet " + str(counter))
counter += 1
self.row = 0
self.col = 0
evaluations_with_results = list()
evaluation_states = ['published']
if include_unpublished:
evaluation_states.extend(['evaluated', 'reviewed'])
used_questionnaires = set()
evaluations_filter = Q(course__semester__in=semesters,
state__in=evaluation_states,
course__degrees__in=degrees,
course__type__in=course_types)
if contributor:
evaluations_filter = evaluations_filter & (
Q(course__responsibles__in=[contributor])
| Q(contributions__contributor__in=[contributor]))
evaluations = Evaluation.objects.filter(
evaluations_filter).distinct()
for evaluation in evaluations:
if evaluation.is_single_result:
continue
if not evaluation.can_publish_rating_results and not include_not_enough_voters:
continue
results = OrderedDict()
for contribution_result in collect_results(
evaluation).contribution_results:
for questionnaire_result in contribution_result.questionnaire_results:
if all(not question_result.question.is_rating_question
or question_result.counts is None
for question_result in
questionnaire_result.question_results):
continue
if not contributor or contribution_result.contributor is None or contribution_result.contributor == contributor:
results.setdefault(
questionnaire_result.questionnaire.id,
[]).extend(
questionnaire_result.question_results)
used_questionnaires.add(
questionnaire_result.questionnaire)
evaluation.course_evaluations_count = evaluation.course.evaluations.count(
)
if evaluation.course_evaluations_count > 1:
course_results_exist = True
evaluation.weight_percentage = int(
(evaluation.weight / sum(evaluation.weight
for evaluation in evaluation.
course.evaluations.all())) *
100)
evaluation.course.avg_grade = distribution_to_grade(
calculate_average_course_distribution(
evaluation.course))
evaluations_with_results.append((evaluation, results))
evaluations_with_results.sort(
key=lambda cr: (cr[0].course.semester.id, cr[0].course.type.
order, cr[0].full_name))
used_questionnaires = sorted(used_questionnaires)
export_name = "Evaluation"
if contributor:
export_name += "\n{}".format(contributor.full_name)
elif len(semesters) == 1:
export_name += "\n{}".format(semesters[0].name)
degree_names = [
degree.name for degree in Degree.objects.filter(pk__in=degrees)
]
course_type_names = [
course_type.name for course_type in CourseType.objects.filter(
pk__in=course_types)
]
writec(
self,
_("{}\n\n{}\n\n{}").format(export_name,
", ".join(degree_names),
", ".join(course_type_names)),
"headline")
for evaluation, results in evaluations_with_results:
title = evaluation.full_name
if len(semesters) > 1:
title += "\n{}".format(evaluation.course.semester.name)
responsible_names = [
responsible.full_name
for responsible in evaluation.course.responsibles.all()
]
title += "\n{}".format(", ".join(responsible_names))
writec(self, title, "evaluation")
writen(self, _("Degrees"), "bold")
for evaluation, results in evaluations_with_results:
writec(
self, "\n".join(
[d.name for d in evaluation.course.degrees.all()]),
"degree")
writen(self, _("Course Type"), "bold")
for evaluation, results in evaluations_with_results:
writec(self, evaluation.course.type.name, "border_left_right")
writen(self)
for evaluation, results in evaluations_with_results:
self.write_empty_cell_with_borders()
for questionnaire in used_questionnaires:
if contributor and questionnaire.type == Questionnaire.CONTRIBUTOR:
writen(
self, "{} ({})".format(questionnaire.name,
contributor.full_name), "bold")
else:
writen(self, questionnaire.name, "bold")
for evaluation, results in evaluations_with_results:
self.write_empty_cell_with_borders()
filtered_questions = self.filter_text_and_heading_questions(
questionnaire.questions.all())
for question in filtered_questions:
if question.is_heading_question:
writen(self, question.text, "italic")
else:
writen(self, question.text)
for evaluation, results in evaluations_with_results:
if questionnaire.id not in results or question.is_heading_question:
self.write_empty_cell_with_borders()
continue
qn_results = results[questionnaire.id]
values = []
count_sum = 0
approval_count = 0
for grade_result in qn_results:
if grade_result.question.id == question.id:
if grade_result.has_answers:
values.append(grade_result.average *
grade_result.count_sum)
count_sum += grade_result.count_sum
if grade_result.question.is_yes_no_question:
approval_count += grade_result.approval_count
if values:
avg = sum(values) / count_sum
if question.is_yes_no_question:
percent_approval = approval_count / count_sum if count_sum > 0 else 0
writec(self, "{:.0%}".format(percent_approval),
self.grade_to_style(avg))
else:
writec(self, avg, self.grade_to_style(avg))
else:
self.write_empty_cell_with_borders()
writen(self)
for evaluation, results in evaluations_with_results:
self.write_empty_cell_with_borders()
writen(self, _("Overall Average Grade"), "bold")
for evaluation, results in evaluations_with_results:
avg = distribution_to_grade(
calculate_average_distribution(evaluation))
if avg:
writec(self, avg, self.grade_to_style(avg))
else:
self.write_empty_cell_with_borders()
writen(self, _("Total voters/Total participants"), "bold")
for evaluation, results in evaluations_with_results:
writec(
self, "{}/{}".format(evaluation.num_voters,
evaluation.num_participants),
"total_voters")
writen(self, _("Evaluation rate"), "bold")
for evaluation, results in evaluations_with_results:
# round down like in progress bar
percentage_participants = int(
(evaluation.num_voters / evaluation.num_participants) *
100) if evaluation.num_participants > 0 else 0
writec(self, "{}%".format(percentage_participants),
"evaluation_rate")
if course_results_exist:
writen(self)
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
self.write_empty_cell_with_borders()
else:
self.write_empty_cell()
writen(self, _("Evaluation weight"), "bold")
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
writec(self,
"{}%".format(evaluation.weight_percentage),
"evaluation_weight")
else:
self.write_empty_cell()
writen(self, _("Course Grade"), "bold")
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
if evaluation.course.avg_grade:
writec(
self, evaluation.course.avg_grade,
self.grade_to_style(
evaluation.course.avg_grade))
else:
self.write_empty_cell_with_borders()
else:
self.write_empty_cell()
writen(self)
for evaluation, results in evaluations_with_results:
if evaluation.course_evaluations_count > 1:
writec(self, None, "border_top")
else:
self.write_empty_cell()
workbook.save(response)