def add_percentage_value(self, numerator, denominator, disaggregations={}):
return self._replace(
numerator=ensure_decimal(self.numerator) +
ensure_decimal(numerator),
denominator=ensure_decimal(self.denominator) +
ensure_decimal(denominator),
disaggregations=self._add_disaggregation(disaggregations))
def create(self, validated_data):
self._validate_disaggregations(
self._disaggregations_data,
value=ensure_decimal(validated_data.get('value', 0)),
numerator=ensure_decimal(validated_data.get('numerator', None)),
denominator=ensure_decimal(validated_data.get('denominator',
None)))
"""Over-ridden to handle nested writes."""
files = validated_data.pop('files', [])
photos = validated_data.pop('photos', [])
comments = validated_data.pop('comments', [])
update = super(IndicatorPeriodDataFrameworkSerializer,
self).create(validated_data)
for disaggregation in self._disaggregations_data:
disaggregation['update'] = update.id
if 'type_id' in disaggregation and 'dimension_value' not in disaggregation:
disaggregation['dimension_value'] = disaggregation['type_id']
serializer = DisaggregationSerializer(data=disaggregation)
serializer.is_valid(raise_exception=True)
serializer.create(serializer.validated_data)
for file in files:
IndicatorPeriodDataFile.objects.create(update=update, file=file)
for photo in photos:
IndicatorPeriodDataPhoto.objects.create(update=update, photo=photo)
for comment in comments:
IndicatorPeriodDataComment.objects.create(
data=update, user=update.user, comment=comment['comment'])
return update
def _transform_contributions_hierarchy(tree, is_percentage):
contributors = []
contributor_countries = []
aggregated_value = Decimal(0) if not is_percentage else None
aggregated_numerator = Decimal(0) if is_percentage else None
aggregated_denominator = Decimal(0) if is_percentage else None
disaggregations = {}
for node in tree:
contributor, countries = _transform_contributor_node(
node, is_percentage)
if contributor:
contributors.append(contributor)
contributor_countries = _merge_unique(contributor_countries,
countries)
if not is_percentage:
aggregated_value += contributor['actual_value']
else:
aggregated_numerator += contributor['actual_numerator']
aggregated_denominator += contributor['actual_denominator']
disaggregation_contributions = _extract_disaggregation_contributions(
contributor)
for key in disaggregation_contributions:
if key not in disaggregations:
disaggregations[key] = disaggregation_contributions[
key].copy()
else:
disaggregations[key]['value'] = ensure_decimal(
disaggregations[key]['value']) + ensure_decimal(
disaggregation_contributions[key]['value'])
aggregates = (aggregated_value, aggregated_numerator,
aggregated_denominator)
return contributors, contributor_countries, aggregates, disaggregations
def render_contributor(ws,
row,
result,
indicator,
period,
contributor,
aggregate_targets=False,
use_indicator_target=False,
disaggregations={},
level=1):
long_text_style = Style(alignment=Alignment(wrap_text=True))
ws.set_cell_style(row, 1, long_text_style)
ws.set_cell_value(row, 1, result.title)
ws.set_cell_style(row, 2, long_text_style)
ws.set_cell_value(row, 2, result.description)
ws.set_cell_style(row, 3, long_text_style)
ws.set_cell_value(row, 3, indicator.title)
ws.set_cell_value(row, 4, f"{period.period_start} - {period.period_end}")
ws.set_cell_value(row, 5, level)
ws.set_cell_style(row, 6, long_text_style)
ws.set_cell_value(row, 6, contributor.project.title)
ws.set_cell_style(row, 7, long_text_style)
ws.set_cell_value(row, 7, contributor.project.subtitle)
ws.set_cell_style(row, 8, long_text_style)
ws.set_cell_value(row, 8, contributor.project.country)
ws.set_cell_style(row, 9, long_text_style)
ws.set_cell_value(
row, 9, ', '.join(contributor.project.sectors)
if contributor.project.sectors else '')
ws.set_cell_value(row, 10,
maybe_decimal(contributor.indicator_baseline_value))
col = get_dynamic_column_start(aggregate_targets)
ws.set_cell_value(
row, col, contributor.indicator_target_value
if use_indicator_target else ensure_decimal(contributor.target_value))
col += 2
ws.set_cell_value(row, col, contributor.actual_value)
col += 1
if period.is_quantitative:
contribution = calculate_percentage(
ensure_decimal(contributor.updates_value),
ensure_decimal(period.aggregated_value))
ws.set_cell_style(row, col,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(row, col, f"{contribution}%")
col += 1
for category, types in disaggregations.items():
for type in [t for t in types.keys()]:
ws.set_cell_value(
row, col,
contributor.get_disaggregation_value(category, type) or '')
col += 1
ws.set_cell_value(
row, col,
contributor.get_disaggregation_target_value(
category, type) or '')
col += 1
return row + 1
def get_aggregated_disaggregation_value(self, category, type):
item = self._select_disaggregation(self.aggregated_disaggregations,
category, type)
if not item:
return None
if self.is_percentage:
return calculate_percentage(ensure_decimal(item.numerator),
ensure_decimal(item.denominator))
return item.value
def update(self, instance, validated_data):
self._validate_disaggregations(
self._disaggregations_data,
value=ensure_decimal(validated_data.get('value', instance.value)),
numerator=ensure_decimal(
validated_data.get('numerator', instance.numerator)),
denominator=ensure_decimal(
validated_data.get('denominator', instance.denominator)),
update=instance)
"""Over-ridden to handle nested updates."""
files = validated_data.pop('files', [])
photos = validated_data.pop('photos', [])
comments = validated_data.pop('comments', [])
super(IndicatorPeriodDataFrameworkSerializer,
self).update(instance, validated_data)
for disaggregation in self._disaggregations_data:
disaggregation['update'] = instance.id
serializer = DisaggregationSerializer(data=disaggregation)
serializer.is_valid(raise_exception=True)
disaggregation_instance, _ = instance.disaggregations.get_or_create(
update=instance,
dimension_value=serializer.validated_data['dimension_value'],
)
serializer.update(disaggregation_instance,
serializer.validated_data)
for file in files:
IndicatorPeriodDataFile.objects.create(update=instance, file=file)
for photo in photos:
IndicatorPeriodDataPhoto.objects.create(update=instance,
photo=photo)
for comment in comments:
comment_id = int(comment.get('id', 0))
comment_txt = str(comment.get('comment', ''))
if not comment_id:
IndicatorPeriodDataComment.objects.create(
data=instance,
user=instance.user,
comment=comment['comment'])
else:
comment_obj = IndicatorPeriodDataComment.objects.get(
id=comment_id)
if not comment_txt:
comment_obj.delete()
else:
comment_obj.comment = comment_txt
comment_obj.save()
return instance._meta.model.objects.select_related(
'period',
'user',
'approved_by',
).prefetch_related(
'comments',
'disaggregations',
).get(id=instance.id)
def _get_indicator_target(indicator, targets_at=None, aggregate_targets=False):
if targets_at != 'indicator':
return None
if indicator.type == QUALITATIVE:
return indicator.target_value
if indicator.measure == PERCENTAGE_MEASURE or not aggregate_targets:
return ensure_decimal(indicator.target_value)
hierarchy_ids = _get_indicator_hierarchy_ids(indicator)
result = Indicator.objects.filter(id__in=hierarchy_ids).aggregate(
Sum('target_value'))
return ensure_decimal(result['target_value__sum'])
def _extract_disaggregation_contributions(contributor):
disaggregations = {}
for update in contributor['updates']:
if update['status']['code'] == 'A':
for d in update['disaggregations']:
key = (d['category'], d['type'])
if key not in disaggregations:
disaggregations[key] = d.copy()
else:
disaggregations[key]['value'] = ensure_decimal(
disaggregations[key]['value']) + ensure_decimal(
d['value'])
return disaggregations
def updates_denominator(self):
if not self.is_percentage:
return None
value = 0
for update in self.approved_updates:
value += ensure_decimal(update.denominator)
return value
def pending_denominator(self):
if not self.is_percentage:
return None
value = 0
for update in self.pending_updates:
value += ensure_decimal(update.denominator)
return value
def aggregated_denominator(self):
if not self.is_percentage:
return None
value = self.updates_denominator
for contributor in self.contributors:
value += ensure_decimal(contributor.aggregated_denominator)
return value
def aggregated_value(self):
if self.is_percentage or self.is_qualitative:
return None
value = self.updates_value
for contributor in self.contributors:
value += ensure_decimal(contributor.aggregated_value)
return value
def test_ensure_decimal(self):
for expected, actual in [
(Decimal(10), '10'),
(Decimal(0), ''),
(Decimal(0), None),
]:
self.assertEqual(expected, ensure_decimal(actual))
def updates_value(self):
if self.is_percentage:
return None
value = 0
for update in self.approved_updates:
value += ensure_decimal(update.value)
return value
def get_indicators_by_country(project):
results = get_results_framework(project)
visitors = []
for result in results:
for indicator in result.indicators:
for period in indicator.periods:
visitors.append(ContributionPerCountryVisitor.collect(result, indicator, period))
by_countries = {}
for visitor in visitors:
indicator = visitor.indicator
period = visitor.period
for country_code, contribution in visitor.countries.items():
if country_code not in by_countries:
by_countries[country_code] = {}
by_country = by_countries[country_code]
if indicator.id not in by_country:
by_country[indicator.id] = {
'is_percentage': indicator.is_percentage,
'periods': {}
}
by_indicator = by_country[indicator.id]
by_indicator['periods'][period.id] = {
'period_start': period.period_start,
'period_end': period.period_end,
'period_target': ensure_decimal(period.target_value),
'aggregated_period_target': period.aggregated_target_value,
'target': contribution.target,
'value': contribution.value,
'numerator': contribution.numerator,
'denominator': contribution.denominator,
}
return by_countries
def _validate_disaggregations(self,
disaggregations,
value,
numerator=None,
denominator=None,
update=None):
adjustments = {}
for disaggregation in disaggregations:
type_id = disaggregation.get(
'type_id', disaggregation.get('dimension_value', None))
if type_id is None:
continue
if denominator is not None:
disaggregation_denominator = ensure_decimal(
disaggregation.get('denominator', 0))
if disaggregation_denominator > denominator:
raise serializers.ValidationError(
"disaggregations denominator should not exceed update denominator"
)
category = IndicatorDimensionValue.objects.get(pk=type_id).name
if category.id not in adjustments:
adjustments[category.id] = {
'values': 0,
'numerators': 0,
'type_ids': []
}
adjustments[category.id]['values'] += ensure_decimal(
disaggregation.get('value', 0))
adjustments[category.id]['numerators'] += ensure_decimal(
disaggregation.get('numerator', 0))
adjustments[category.id]['type_ids'].append(type_id)
for key, adjustment in adjustments.items():
unmodifieds = Disaggregation.objects.filter(update=update, dimension_value__name=key)\
.exclude(dimension_value__in=adjustment['type_ids'])\
.aggregate(values=Sum('value'))
total = adjustment['values'] + ensure_decimal(
unmodifieds['values'])
if numerator is not None and adjustment['numerators'] > numerator:
raise serializers.ValidationError(
"The disaggregation numerator should not exceed update numerator"
)
if total > value:
raise serializers.ValidationError(
"The accumulated disaggregations value should not exceed update value"
)
def test_should_be_able_to_patch_disaggregation_targets_values(self):
_, org = self.create_org_user('*****@*****.**', 'password')
project = ProjectFixtureBuilder()\
.with_partner(org, Partnership.IATI_REPORTING_ORGANISATION)\
.with_disaggregations({
'Gender': ['Male', 'Female'],
'Age': ['Children', 'Adults']
})\
.with_results([{
'title': 'Result #1',
'indicators': [{
'title': 'Indicator #1',
'periods': [{
'period_start': date(2010, 1, 1),
'period_end': date(2010, 12, 31),
'target_value': 10,
'disaggregation_targets': {
'Gender': {'Male': 5, 'Female': 5},
'Age': {'Adults': 10}
}
}]
}]
}]).build()
period = project.get_period(period_start=date(2010, 1, 1))
gender_female_target = period.get_disaggregation_target(
'Gender', 'Female')
age_adults_target = period.get_disaggregation_target('Age', 'Adults')
data = {
'target_value':
12,
'disaggregation_targets': [
{
'dimension_value': gender_female_target.dimension_value.id,
'value': 7
},
{
'dimension_value': age_adults_target.dimension_value.id,
'value': 12
},
]
}
response = self.send_request(period,
data,
username='******',
password='******')
self.assertEqual(response.status_code, 200)
updated_period = project.get_period(period_start=date(2010, 1, 1))
self.assertEqual(ensure_decimal(updated_period.target_value), 12)
self.assertEqual(
updated_period.get_disaggregation_target('Gender', 'Female').value,
7)
self.assertEqual(
updated_period.get_disaggregation_target('Age', 'Adults').value,
12)
def aggregated_value(self):
if self.is_qualitative:
return None
if self.is_percentage:
return calculate_percentage(self.aggregated_numerator, self.aggregated_denominator)
value = self.updates_value
for contributor in self.contributors:
value += ensure_decimal(contributor.aggregated_value)
return value
def target_value(self):
if self._target_value is None:
if self.type == IndicatorType.NARRATIVE:
self._target_value = self._real.target_value
elif self.aggregate_targets and self.type != IndicatorType.PERCENTAGE:
self._target_value = _aggregate_period_targets(
self._real, self._children)
else:
self._target_value = ensure_decimal(self._real.target_value)
return self._target_value
def aggregated_disaggregation_targets(
self) -> List[DisaggregationTargetData]:
items = {}
for d in self.disaggregation_targets:
key = (d.category, d.type)
if key not in items:
items[key] = None
items[key] = ensure_decimal(items[key]) + ensure_decimal(d.value)
for contributor in self.contributors:
for d in contributor.aggregated_disaggregation_targets:
key = (d.category, d.type)
if key not in items:
items[key] = None
items[key] = ensure_decimal(items[key]) + ensure_decimal(
d.value)
return [
DisaggregationTargetData(None, category, type, value)
for (category, type), value in items.items()
]
def pending_value(self):
if self.is_qualitative:
return None
if self.is_percentage:
return calculate_percentage(self.pending_numerator,
self.pending_denominator)
value = 0
for update in self.pending_updates:
value += ensure_decimal(update.value)
return value
def visit(self, contributor):
if not contributor.has_updates and contributor.target_value is None:
return
country_code = contributor.project.country_code
if country_code is None:
return
if country_code not in self.countries:
self.countries[country_code] = ContributionValue()
contribution = self.countries[country_code]
if contributor.target_value:
contribution.add_target(ensure_decimal(contributor.target_value))
if self.period.is_percentage:
contribution.add_fraction(contributor.updates_numerator, contributor.updates_denominator)
else:
contribution.add_value(contributor.updates_value)
def render_period(ws,
row,
result,
indicator,
period,
aggregate_targets=False,
use_indicator_target=False,
disaggregations={}):
long_text_style = Style(alignment=Alignment(wrap_text=True))
ws.set_cell_style(row, 1, long_text_style)
ws.set_cell_value(row, 1, result.title)
ws.set_cell_style(row, 2, long_text_style)
ws.set_cell_value(row, 2, result.description)
ws.set_cell_style(row, 3, long_text_style)
ws.set_cell_value(row, 3, indicator.title)
ws.set_cell_value(row, 4, f"{period.period_start} - {period.period_end}")
ws.set_cell_value(row, 10, maybe_decimal(indicator.baseline_value))
col = get_dynamic_column_start(aggregate_targets)
if aggregate_targets:
ws.set_cell_value(
row, AGGREGATED_TARGET_VALUE_COLUMN,
indicator.aggregated_target_value
if use_indicator_target else period.aggregated_target_value)
else:
ws.set_cell_value(
row, col, indicator.target_value
if use_indicator_target else ensure_decimal(period.target_value))
col += 1
ws.set_cell_value(row, col, period.aggregated_value)
if period.is_quantitative:
col += 3
for category, types in disaggregations.items():
for type in [t for t in types.keys()]:
ws.set_cell_value(
row, col,
period.get_aggregated_disaggregation_value(category, type)
or '')
col += 1
ws.set_cell_value(
row, col,
period.get_aggregated_disaggregation_target_value(
category, type) or '')
col += 1
return row + 1
def test_can_create_disaggregation_targets(self):
org, _ = self.create_org_user('*****@*****.**', 'password')
project = ProjectFixtureBuilder()\
.with_partner(org, Partnership.IATI_REPORTING_ORGANISATION)\
.with_disaggregations({
'Gender': ['Male', 'Female'],
'Age': ['Children', 'Adults']
})\
.with_results([{
'title': 'Result #1',
'indicators': [{
'title': 'Indicator #1',
'periods': [{
'period_start': date(2010, 1, 1),
'period_end': date(2010, 12, 31),
'target_value': 10,
}]
}]
}]).build()
period = project.get_period(period_start=date(2010, 1, 1))
male = project.get_disaggregation('Gender', 'Male')
female = project.get_disaggregation('Gender', 'Female')
data = {
'target_value': 12,
'disaggregation_targets': [
{'value': 8, 'dimension_value': male.id},
{'value': 10, 'dimension_value': female.id},
]
}
response = self.send_patch(period, data, username='******', password='******')
self.assertEqual(response.status_code, 200)
updated_period = project.get_period(period_start=date(2010, 1, 1))
self.assertEqual(ensure_decimal(updated_period.target_value), 12)
self.assertEqual(updated_period.get_disaggregation_target('Gender', 'Male').value, 8)
self.assertEqual(updated_period.get_disaggregation_target('Gender', 'Female').value, 10)
def get_disaggregation_target_of(self, category, type):
key = (category, type)
if key not in self.disaggregation_targets:
return None
return ensure_decimal(self.disaggregation_targets[key].value)
def target_value(self):
if self._target_value is None:
self._target_value = ensure_decimal(self._real.target_value)
return self._target_value
def actual_value(self):
if self._actual_value is None:
self._actual_value = ensure_decimal(self._real.actual_value)
return self._actual_value
def sum_of_period_values(self):
value = 0
for period in self._periods:
value += ensure_decimal(period.actual_value)
return value
def get_value(self, obj):
return ensure_decimal(obj.value)
def render_report(request, project_id):
queryset = Project.objects.prefetch_related(
'results', 'results__indicators', 'results__indicators__periods')
project = get_object_or_404(queryset, pk=project_id)
in_eutf_hierarchy = project.in_eutf_hierarchy()
wb = Workbook()
ws = wb.new_sheet('ResultsTable')
ws.set_col_style(1, Style(size=75))
ws.set_col_style(2, Style(size=75))
ws.set_col_style(3, Style(size=41))
ws.set_col_style(4, Style(size=18.5))
ws.set_col_style(5, Style(size=34))
ws.set_col_style(6, Style(size=37.5))
ws.set_col_style(7, Style(size=47.5))
ws.set_col_style(8, Style(size=20))
ws.set_col_style(9, Style(size=20))
ws.set_col_style(10, Style(size=34))
ws.set_col_style(11, Style(size=20))
ws.set_col_style(12, Style(size=20))
ws.set_col_style(13, Style(size=20))
ws.set_col_style(14, Style(size=24))
ws.set_col_style(15, Style(size=20.5))
ws.set_col_style(16, Style(size=30))
ws.set_col_style(17, Style(size=22))
ws.set_col_style(18, Style(size=21))
# r1
ws.set_row_style(1, Style(size=36))
ws.set_cell_style(
1, 1,
Style(font=Font(bold=True, size=18, color=Color(255, 255, 255)),
fill=Fill(background=Color(32, 56, 100)),
alignment=Alignment(horizontal='center')))
ws.set_cell_value(1, 1,
'Project Results and Indicators simple table report')
# r2
ws.set_row_style(2, Style(size=36))
for i in range(1, 19):
ws.set_cell_style(
2, i,
Style(font=Font(bold=True, size=14),
fill=Fill(background=Color(214, 234, 248)),
alignment=Alignment(horizontal='center'),
borders=Borders(top=Border(color=Color(0, 0, 0)),
bottom=Border(color=Color(0, 0, 0)))))
ws.set_cell_value(2, 1, 'Project name')
ws.set_cell_value(2, 2, 'Project subtitle')
ws.set_cell_value(2, 3, 'Result title')
ws.set_cell_value(2, 4, 'Result type')
ws.set_cell_value(2, 5, 'Result description')
ws.set_cell_value(2, 6, 'Indicator title')
ws.set_cell_value(2, 7, 'Indicator description')
ws.set_cell_value(2, 8, 'Baseline year')
ws.set_cell_value(2, 9, 'Baseline value')
ws.set_cell_value(2, 10, 'Baseline comment')
ws.set_cell_value(2, 11, 'Period start')
ws.set_cell_value(2, 12, 'Period end')
ws.set_cell_value(2, 13, 'Target value')
ws.set_cell_value(2, 14, 'Target comment')
ws.set_cell_value(2, 15, 'Actual value')
ws.set_cell_value(2, 16, 'Actual comment')
ws.set_cell_value(2, 17, 'Type')
ws.set_cell_value(2, 18, 'Aggregation status')
# r3
row = 3
ws.set_cell_value(row, 1, project.title)
ws.set_cell_value(row, 2, project.subtitle)
prev_type = ''
curr_type = ''
prev_agg_status = ''
curr_agg_status = ''
prev_indicator_type = ''
curr_indicator_type = ''
for result in project.results.exclude(type__exact='').all():
ws.set_cell_value(row, 3, result.title)
curr_type = result.iati_type().name
if curr_type != prev_type:
ws.set_cell_value(row, 4, curr_type)
prev_type = curr_type
ws.set_cell_style(row, 5, Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 5, result.description)
curr_agg_status = 'Yes' if result.aggregation_status else 'No'
if curr_agg_status != prev_agg_status:
ws.set_cell_value(row, 18, curr_agg_status)
prev_agg_status = curr_agg_status
for indicator in result.indicators.all():
ws.set_cell_style(row, 6,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 6, indicator.title)
ws.set_cell_style(row, 7,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 7, indicator.description)
ws.set_cell_value(row, 8, indicator.baseline_year)
ws.set_cell_value(row, 9, indicator.baseline_value)
ws.set_cell_style(row, 10,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 10, indicator.baseline_comment)
curr_indicator_type = 'Qualitative' if indicator.type == '2' else 'Quantitative'
if curr_indicator_type != prev_indicator_type:
ws.set_cell_value(row, 17, curr_indicator_type)
prev_indicator_type = curr_indicator_type
for period in indicator.periods.all():
ws.set_cell_value(
row, 11, utils.get_period_start(period, in_eutf_hierarchy))
ws.set_cell_value(
row, 12, utils.get_period_end(period, in_eutf_hierarchy))
ws.set_cell_value(row, 13, period.target_value)
ws.set_cell_style(row, 14,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 14, period.target_comment)
ws.set_cell_value(row, 15, ensure_decimal(period.actual_value))
ws.set_cell_style(row, 16,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 16, period.actual_comment)
ws.set_row_style(row, Style(size=68))
row += 1
filename = '{}-{}-eutf-project-results-indicators-report.xlsx'.format(
datetime.today().strftime('%Y%b%d'), project.id)
return utils.make_excel_response(wb, filename)