def test_should_not_aggregate_excluded_child_period_values(self):
# Given
child_project_2 = self.create_child_project('Child project 2')
self.child_project.import_results()
child_project_2.import_results()
child_project_2.aggregate_to_parent = False
child_project_2.save()
child_period = self.get_child_period(self.period)
child_period_2 = self.get_child_period(self.period, child_project_2)
child_numerator = 6
child_denominator = 10
# When
self.create_indicator_period_update(numerator=child_numerator,
denominator=child_denominator,
indicator_period=child_period)
self.create_indicator_period_update(numerator=child_numerator,
denominator=child_denominator,
indicator_period=child_period_2)
# Then
period = IndicatorPeriod.objects.get(id=self.period.id)
percentage = calculate_percentage(child_numerator, child_denominator)
self.assertDecimalEqual(percentage, period.actual_value)
self.assertDecimalEqual(child_numerator, period.numerator)
self.assertDecimalEqual(child_denominator, period.denominator)
def test_should_not_aggregate_child_period_value(self):
# Given
self.parent_project.aggregate_children = False
self.parent_project.save()
numerator = 5
denominator = 10
percentage = calculate_percentage(numerator, denominator)
self.period.numerator = numerator
self.period.denominator = denominator
self.period.save()
self.child_project.import_results()
child_numerator = 6
child_denominator = 10
child_indicator_period = self.get_child_period(self.period)
# When
self.create_indicator_period_update(
numerator=child_numerator,
denominator=child_denominator,
indicator_period=child_indicator_period
)
# Then
period = IndicatorPeriod.objects.get(id=self.period.id)
self.assertDecimalEqual(percentage, period.actual_value)
self.assertDecimalEqual(numerator, period.numerator)
self.assertDecimalEqual(denominator, period.denominator)
def actual_value(self):
if self.is_qualitative:
return None
if self.is_percentage:
return calculate_percentage(self.updates_numerator,
self.updates_denominator)
return self.updates_value
def test_should_not_aggregate_excluded_child_period_values(self):
# Given
child_project_2 = self.create_child_project('Child project 2')
self.child_project.import_results()
child_project_2.import_results()
child_project_2.aggregate_to_parent = False
child_project_2.save()
child_period = self.get_child_period(self.period)
child_period_2 = self.get_child_period(self.period, child_project_2)
child_numerator = 6
child_denominator = 10
# When
self.create_indicator_period_update(
numerator=child_numerator,
denominator=child_denominator,
indicator_period=child_period
)
self.create_indicator_period_update(
numerator=child_numerator,
denominator=child_denominator,
indicator_period=child_period_2
)
# Then
period = IndicatorPeriod.objects.get(id=self.period.id)
percentage = calculate_percentage(child_numerator, child_denominator)
self.assertDecimalEqual(percentage, period.actual_value)
self.assertDecimalEqual(child_numerator, period.numerator)
self.assertDecimalEqual(child_denominator, period.denominator)
def disaggregations(self):
if self._disaggregations is None:
self._disaggregations = {}
for period in self.labeled_periods:
for d in period.disaggregations.values():
category = d['category']
type = d['type']
if category not in self.disaggregations:
self._disaggregations[category] = {}
if type not in self._disaggregations[category]:
self._disaggregations[category][type] = {
'value': 0,
'numerator': 0,
'denominator': 0
}
self._disaggregations[category][type]['value'] += (
d['value'] or 0)
self._disaggregations[category][type]['numerator'] += (
d['numerator'] or 0)
self._disaggregations[category][type]['denominator'] += (
d['denominator'] or 0)
if self.type == IndicatorType.PERCENTAGE:
for category, types in self._disaggregations.items():
for type in types.keys():
self._disaggregations[category][type][
'value'] = calculate_percentage(
self._disaggregations[category][type]
['numerator'], self._disaggregations[category]
[type]['denominator'])
return self._disaggregations
def disaggregations(self):
if self._disaggregations is None:
self._disaggregations = {}
for period in self.periods:
for d in period.disaggregations.all():
category = d.dimension_value.name.name
type = d.dimension_value.value
if category not in self._disaggregations:
self._disaggregations[category] = {}
if type not in self._disaggregations[category]:
self._disaggregations[category][type] = {
'value': 0,
'numerator': 0,
'denominator': 0
}
self._disaggregations[category][type]['value'] += (d.value
or 0)
self._disaggregations[category][type]['numerator'] += (
d.numerator or 0)
self._disaggregations[category][type]['denominator'] += (
d.denominator or 0)
if self.is_percentage:
for category, types in self._disaggregations.items():
for type in types.keys():
self._disaggregations[category][type][
'value'] = calculate_percentage(
self._disaggregations[category][type]
['numerator'], self._disaggregations[category]
[type]['denominator'])
return self._disaggregations
def test_should_not_aggregate_child_period_value(self):
# Given
self.parent_project.aggregate_children = False
self.parent_project.save()
numerator = 5
denominator = 10
percentage = calculate_percentage(numerator, denominator)
self.period.numerator = numerator
self.period.denominator = denominator
self.period.save()
self.child_project.import_results()
child_numerator = 6
child_denominator = 10
child_indicator_period = self.get_child_period(self.period)
# When
self.create_indicator_period_update(
numerator=child_numerator,
denominator=child_denominator,
indicator_period=child_indicator_period)
# Then
period = IndicatorPeriod.objects.get(id=self.period.id)
self.assertDecimalEqual(percentage, period.actual_value)
self.assertDecimalEqual(numerator, period.numerator)
self.assertDecimalEqual(denominator, period.denominator)
def get_disaggregation_of(self, category, type):
key = (category, type)
if key not in self.disaggregations:
return None
if self.type == IndicatorType.UNIT:
return self.disaggregations[key]['value']
d = self.disaggregations[key]
return calculate_percentage(d['numerator'], d['denominator'])
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 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 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 progress(self):
if self._progress is None:
actual_values = 0
target_values = 0
for period in self.periods:
actual_values += period.actual_value
target_values += period.target_value
self._progress = calculate_percentage(actual_values, self.target_value or target_values)
return self._progress
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 test_should_set_period_actual_value(self):
# Given
numerator = 40
denominator = 100
actual_value = calculate_percentage(numerator, denominator)
period = IndicatorPeriod.objects.get(id=self.period.id)
# When
period.numerator = numerator
period.denominator = denominator
# This should be over-written
period.actual_value = actual_value * 200
period.save()
# Then
period = IndicatorPeriod.objects.get(id=self.period.id)
self.assertEqual(numerator, period.numerator)
self.assertEqual(denominator, period.denominator)
self.assertDecimalEqual(actual_value, period.actual_value)
def test_should_set_period_actual_value(self):
# Given
numerator = 40
denominator = 100
actual_value = calculate_percentage(numerator, denominator)
period = IndicatorPeriod.objects.get(id=self.period.id)
# When
period.numerator = numerator
period.denominator = denominator
# This should be over-written
period.actual_value = actual_value * 200
period.save()
# Then
period = IndicatorPeriod.objects.get(id=self.period.id)
self.assertEqual(numerator, period.numerator)
self.assertEqual(denominator, period.denominator)
self.assertDecimalEqual(actual_value, period.actual_value)
def _build(self):
if self._updates is not None:
return
self._updates = []
self._total_value = 0
if self.type == IndicatorType.PERCENTAGE:
self._total_numerator = 0
self._total_denominator = 0
self._disaggregations = {}
for update in self.period.data.all():
self._updates.append(UpdateProxy(update))
if update.status != IndicatorPeriodData.STATUS_APPROVED_CODE:
continue
if self.type == IndicatorType.PERCENTAGE:
if update.numerator is not None and update.denominator is not None:
self._total_numerator += update.numerator
self._total_denominator += update.denominator
elif update.value:
self._total_value += update.value
for d in update.disaggregations.all():
key = (d.dimension_value.name.name, d.dimension_value.value)
if key not in self._disaggregations:
self._disaggregations[key] = {
'category': d.dimension_value.name.name,
'type': d.dimension_value.value,
'value': 0,
'numerator': d.numerator,
'denominator': d.denominator,
}
self._disaggregations[key]['value'] += d.value
if self.type == IndicatorType.PERCENTAGE and self._total_denominator > 0:
self._total_value = calculate_percentage(self._total_numerator,
self._total_denominator)
def test_aggregate_quantitative_percentage_with_disaggregation(self):
# Given
root = ProjectFixtureBuilder()\
.with_disaggregations({
'Gender': ['Male', 'Female'],
})\
.with_results([{
'title': 'Result #1',
'indicators': [{
'title': 'Indicator #1',
'type': QUANTITATIVE,
'measure': PERCENTAGE_MEASURE,
'periods': [
{
'period_start': date(2010, 1, 1),
'period_end': date(2010, 12, 31),
'label': 'L1',
},
{
'period_start': date(2011, 1, 1),
'period_end': date(2011, 12, 31),
'label': 'L2',
},
{
'period_start': date(2012, 1, 1),
'period_end': date(2012, 12, 31),
'label': 'L3',
},
]
}]
}])\
.with_contributors([
{'title': 'Contrib #1', 'contributors': [{'title': 'Subcon #1.1'}]},
{'title': 'Contrib #2'}
])\
.build()
l1 = root.get_label('L1')
l2 = root.get_label('L2')
l3 = root.get_label('L3')
contrib2 = root.get_contributor(title='Contrib #2')
subcon1_1 = root.get_contributor(title='Subcon #1.1')
user = self.create_user('*****@*****.**', 'password', is_admin=True)
contrib2.get_period(period_start=date(2010, 1, 1)).set_label(l1)\
.add_update(user, numerator=1, denominator=10, disaggregations={
'Gender': {
'Female': {'numerator': 1, 'denominator': 10}
}
})
contrib2.get_period(period_start=date(2011, 1, 1)).set_label(l2)\
.add_update(user, numerator=2, denominator=10, disaggregations={
'Gender': {
'Male': {'numerator': 1, 'denominator': 5},
'Female': {'numerator': 1, 'denominator': 5}
}
})
contrib2.get_period(period_start=date(2012, 1, 1)).set_label(l3)\
.add_update(user, numerator=3, denominator=10, disaggregations={
'Gender': {
'Male': {'numerator': 1, 'denominator': 5},
'Female': {'numerator': 2, 'denominator': 5}
}
})
subcon1_1.get_period(period_start=date(2010, 1, 1))\
.add_update(user, numerator=4, denominator=10, disaggregations={
'Gender': {
'Male': {'numerator': 2, 'denominator': 5},
'Female': {'numerator': 2, 'denominator': 5}
}
})
subcon1_1.get_period(period_start=date(2011, 1, 1)).set_label(l1)\
.add_update(user, numerator=5, denominator=10, disaggregations={
'Gender': {
'Male': {'numerator': 2, 'denominator': 5},
'Female': {'numerator': 3, 'denominator': 5}
}
})
subcon1_1.get_period(period_start=date(2012, 1, 1)).set_label(l2)\
.add_update(user, numerator=6, denominator=10, disaggregations={
'Gender': {
'Male': {'numerator': 3, 'denominator': 5},
'Female': {'numerator': 3, 'denominator': 5}
}
})
# When
indicators = build_view_object(root.object)
# Then
self.assertEqual(1, len(indicators))
indicator = indicators[0]
pl1 = indicator.get_labeled_period('L1')
pl2 = indicator.get_labeled_period('L2')
pl3 = indicator.get_labeled_period('L3')
self.assertEqual(calculate_percentage(2, 5), pl1.get_disaggregation_of('Gender', 'Male'))
self.assertEqual(calculate_percentage(4, 15), pl1.get_disaggregation_of('Gender', 'Female'))
self.assertEqual(calculate_percentage(4, 10), pl2.get_disaggregation_of('Gender', 'Male'))
self.assertEqual(calculate_percentage(4, 10), pl2.get_disaggregation_of('Gender', 'Female'))
self.assertEqual(calculate_percentage(1, 5), pl3.get_disaggregation_of('Gender', 'Male'))
self.assertEqual(calculate_percentage(2, 5), pl3.get_disaggregation_of('Gender', 'Female'))
def progress(self):
if self._progress is None:
self._progress = calculate_percentage(self.actual_value, self.target_value)
return self._progress
def _transform_contributor(period, is_percentage):
value = _force_decimal(period.actual_value)
is_qualitative = period.indicator.type == QUALITATIVE
# FIXME: Not sure why the value < 1 check is being used, if it is a float
# comparison issue, we need to resolve it in a better fashion.
# Return early if there are not updates and value is "0" for quantitative updates
if not is_qualitative and value < 1 and period.data.count() < 1:
return None, None
project = period.indicator.result.project
if not project.aggregate_to_parent:
return None, None
country = project.primary_location.country if project.primary_location else None
updates = _transform_updates(period)
updates_value, updates_numerator, updates_denominator = None, None, None
if is_percentage:
updates_numerator, updates_denominator = _extract_percentage_updates(
updates)
updates_value = calculate_percentage(updates_numerator,
updates_denominator)
else:
updates_value = _calculate_update_values(updates)
if is_qualitative:
target = period.target_value
else:
target = _force_decimal(period.target_value)
contributor = {
'project_id':
project.id,
'project_title':
project.title,
'project_subtitle':
project.subtitle,
'period_id':
period.id,
'country': {
'iso_code': country.iso_code
} if country else None,
'actual_comment':
period.actual_comment.split(' | ') if period.actual_comment else None,
'actual_value':
value,
'actual_numerator':
None,
'actual_denominator':
None,
'target_value':
target,
'score_index':
period.score_index,
'score_indices':
period.score_indices,
'updates':
updates,
'updates_value':
updates_value,
'updates_numerator':
updates_numerator,
'updates_denominator':
updates_denominator,
'contributors': [],
'disaggregation_contributions': [],
'disaggregation_targets':
_transform_disaggregation_targets(period),
}
return contributor, project.aggregate_children
def _transform_period_contributions_node(node, aggregate_targets=False):
period = node['item']
is_percentage = period.indicator.measure == PERCENTAGE_MEASURE
is_qualitative = period.indicator.type == QUALITATIVE
actual_numerator, actual_denominator = None, None
updates_value, updates_numerator, updates_denominator = None, None, None
contributors, countries, aggregates, disaggregations = _transform_contributions_hierarchy(
node['children'], is_percentage)
aggregated_value, aggregated_numerator, aggregated_denominator = aggregates
updates = _transform_updates(period)
if is_percentage:
updates_numerator, updates_denominator = _extract_percentage_updates(
updates)
updates_value = calculate_percentage(updates_numerator,
updates_denominator)
actual_numerator, actual_denominator = updates_numerator, updates_denominator
if aggregated_numerator:
actual_numerator += aggregated_numerator
if aggregated_denominator:
actual_denominator += aggregated_denominator
actual_value = calculate_percentage(actual_numerator,
actual_denominator)
else:
actual_value = _force_decimal(period.actual_value)
updates_value = _calculate_update_values(updates)
if is_qualitative:
target = period.target_value
elif aggregate_targets and not is_percentage:
target = _aggregate_targets(node)
else:
target = _force_decimal(period.target_value)
result = {
'period_id':
period.id,
'period_start':
period.period_start,
'period_end':
period.period_end,
'locked':
period.locked,
'actual_comment':
period.actual_comment.split(' | ') if period.actual_comment else None,
'actual_value':
actual_value,
'actual_numerator':
actual_numerator,
'actual_denominator':
actual_denominator,
'can_add_update':
period.can_save_update(),
'target_value':
target,
'countries':
countries,
'updates':
updates,
'updates_value':
updates_value,
'updates_numerator':
updates_numerator,
'updates_denominator':
updates_denominator,
'updates_score_index':
period.score_index,
'updates_score_indices':
period.score_indices,
'contributors':
contributors,
'disaggregation_contributions':
list(disaggregations.values()),
'disaggregation_targets':
_transform_disaggregation_targets(period),
}
return result
def percent_of(numerator, denominator):
return calculate_percentage(numerator, denominator)
def actual_value(self):
if self.type == IndicatorType.UNIT:
return ensure_decimal(self.value)
else:
return calculate_percentage(self.numerator, self.denominator)
def progress(self):
return calculate_percentage(self.actual_value, self.target_value)
def render_report(request, program_id):
program = get_object_or_404(Project.objects.prefetch_related('results'),
pk=program_id)
start_date = utils.parse_date(request.GET.get('period_start', '').strip())
end_date = utils.parse_date(request.GET.get('period_end', '').strip())
project_view = build_view_object(
program, start_date or datetime(1900, 1, 1), end_date
or (datetime.today() + relativedelta(years=10)))
results_by_types = {}
for result in project_view.results:
type = result.iati_type_name
if not type:
continue
if type not in results_by_types:
results_by_types[type] = []
results_by_types[type].append(result)
if not results_by_types:
results_by_types = {'Sheet1': []}
report_title = 'Programme Overview Report{}'.format(': {} - {}'.format(
'' if start_date is None else start_date.strftime('%d-%m-%Y'),
'' if end_date is None else end_date.strftime('%d-%m-%Y')
) if start_date is not None or end_date is not None else '')
disaggregations = get_disaggregations(program)
disaggregation_types_length = 0
for category, types in disaggregations.items():
disaggregation_types_length += len(types.keys())
disaggregations_last_colnum = 7 + (disaggregation_types_length * 2)
wb = Workbook()
for type, results in results_by_types.items():
ws = wb.new_sheet(type)
ws.set_col_style(1, Style(size=60))
ws.set_col_style(2, Style(size=10))
ws.set_col_style(3, Style(size=70))
ws.set_col_style(4, Style(size=25))
ws.set_col_style(5, Style(size=25))
ws.set_col_style(6, Style(size=25))
ws.set_col_style(7, Style(size=25))
# r1
ws.set_row_style(1, Style(size=41))
ws.set_cell_style(1, 1, Style(font=Font(bold=True, size=24)))
ws.set_cell_value(1, 1, report_title)
ws.range('A1', 'C1').merge()
# r2
ws.set_row_style(2, Style(size=36))
ws.set_cell_style(2, 1, Style(font=Font(bold=True, size=12)))
ws.set_cell_value(2, 1, 'Programme title')
ws.set_cell_style(2, 2, Style(font=Font(size=12)))
ws.set_cell_value(2, 2, program.title)
ws.range('B2', 'F2').merge()
# r3
ws.set_row_style(3, Style(size=36))
ws.set_cell_style(3, 1, Style(font=Font(bold=True, size=12)))
ws.set_cell_value(3, 1, 'Result type')
ws.set_cell_style(3, 2, Style(font=Font(size=12)))
ws.set_cell_value(3, 2, '' if type == 'Sheet1' else type)
ws.range('B3', 'C3').merge()
# r4
row = 5
for result in results:
# r5
ws.set_row_style(row, Style(size=36))
result_header1_style = Style(
font=Font(bold=True, size=12, color=Color(255, 255, 255)),
fill=Fill(background=Color(89, 89, 89)))
for i in range(1, 8):
ws.set_cell_style(row, i, result_header1_style)
ws.set_cell_value(row, 1, 'Result title:')
ws.set_cell_value(row, 4, 'Result description:')
if disaggregation_types_length:
ws.set_cell_style(
row, 8,
Style(font=Font(bold=True,
size=12,
color=Color(255, 255, 255)),
alignment=Alignment(horizontal='center'),
fill=Fill(background=Color(89, 89, 89))))
ws.set_cell_value(row, 8, 'Disaggregations')
ws.range(
'H' + str(row),
utils.xl_column_name(disaggregations_last_colnum) +
str(row)).merge()
row += 1
# r6
ws.set_row_style(row, Style(size=42))
result_header2_style = Style(
font=Font(size=12, color=Color(255, 255, 255)),
alignment=Alignment(wrap_text=True),
fill=Fill(background=Color(89, 89, 89)))
result_header_disaggregation_style = Style(
font=Font(size=12, color=Color(255, 255, 255)),
alignment=Alignment(wrap_text=True, horizontal='center'),
fill=Fill(background=Color(89, 89, 89)))
ws.range('A' + str(row), 'C' + str(row)).merge()
ws.set_cell_style(row, 1, result_header2_style)
ws.set_cell_value(row, 1, result.title)
ws.range('D' + str(row), 'G' + str(row)).merge()
ws.set_cell_style(row, 4, result_header2_style)
ws.set_cell_value(row, 4, result.description)
if disaggregation_types_length:
col = 8
for category, types in disaggregations.items():
ws.set_cell_style(row, col,
result_header_disaggregation_style)
ws.set_cell_value(row, col, category.upper())
type_length = len(types.keys()) * 2
next_col = col + type_length
ws.range(
utils.xl_column_name(col) + str(row),
utils.xl_column_name(next_col - 1) + str(row)).merge()
col = next_col
row += 1
for indicator in result.indicators:
# r7
ws.set_row_style(row, Style(size=36))
row7_style = Style(font=Font(bold=True, size=12),
fill=Fill(background=Color(211, 211, 211)))
for i in range(1, disaggregations_last_colnum + 1):
ws.set_cell_style(row, i, row7_style)
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_value(row, 1, 'Indicator title')
ws.set_cell_value(row, 2, 'Indicator description')
ws.set_cell_value(row, 4, 'Indicator type:')
if disaggregation_types_length:
col = 8
types = [
t for ts in disaggregations.values()
for t in ts.keys()
]
for type in types:
ws.set_cell_value(row, col, type)
next_col = col + 2
ws.range(
utils.xl_column_name(col) + str(row),
utils.xl_column_name(next_col - 1) +
str(row)).merge()
col = next_col
row += 1
# r8
row8_style = Style(fill=Fill(background=Color(211, 211, 211)),
alignment=Alignment(wrap_text=True))
for i in range(1, disaggregations_last_colnum + 1):
ws.set_cell_style(row, i, row8_style)
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_value(row, 1, indicator.title)
ws.set_cell_value(row, 2, indicator.description)
ws.set_cell_value(
row, 4, 'Qualitative'
if indicator.is_qualitative else 'Quantitative')
if disaggregation_types_length:
col = 8
while col <= disaggregations_last_colnum:
ws.set_cell_value(row, col, 'value')
col += 1
ws.set_cell_value(row, col, 'target')
col += 1
row += 1
for period in indicator.periods:
# r9
ws.set_row_style(row, Style(size=36))
row9_style = Style(
font=Font(bold=True, size=12),
fill=Fill(background=Color(220, 230, 242)))
for i in range(1, disaggregations_last_colnum + 1):
ws.set_cell_style(row, i, row9_style)
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_value(row, 1, 'Reporting Period:')
ws.set_cell_value(row, 2, 'Number of contrributors')
ws.set_cell_value(row, 4, 'Countries')
ws.set_cell_value(row, 5, 'Aggregated Actual Value')
ws.set_cell_value(row, 6, 'Target value')
ws.set_cell_value(row, 7, '% of Contribution')
row += 1
# r10
number_of_contributors = len(period.contributors)
row10_style = Style(
font=Font(size=12),
fill=Fill(background=Color(220, 230, 242)))
for i in range(1, 7):
ws.set_cell_style(row, i, row10_style)
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_value(
row, 1, '{} - {}'.format(period.period_start,
period.period_end))
ws.set_cell_value(row, 2, number_of_contributors)
ws.set_cell_value(row, 4, len(period.countries))
ws.set_cell_value(row, 5, period.actual_value)
ws.set_cell_value(row, 6, period.target_value)
ws.set_cell_style(
row, 7,
Style(alignment=Alignment(horizontal='right'),
font=Font(size=12),
fill=Fill(background=Color(220, 230, 242))))
ws.set_cell_value(row, 7, '100%')
if disaggregation_types_length:
for i in range(8, disaggregations_last_colnum + 1):
ws.set_cell_style(row, i, row10_style)
col = 8
for category, types in disaggregations.items():
for type in [t for t in types.keys()]:
ws.set_cell_value(
row, col,
period.get_disaggregation_contribution_of(
category, type) or '')
col += 1
ws.set_cell_value(
row, col,
period.get_disaggregation_target_of(
category, type) or '')
col += 1
row += 1
if not number_of_contributors:
continue
for contrib in period.contributors:
# r11
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_style(row, 2, Style(font=Font(bold=True)))
ws.set_cell_value(row, 2, 'Level 1 contributor:')
row += 1
# r12
ws.set_row_style(row, Style(size=30))
ws.range('B' + str(row), 'C' + str(row)).merge()
ws.set_cell_style(
row, 2,
Style(alignment=Alignment(wrap_text=True,
vertical='top')))
ws.set_cell_value(row, 2, contrib.project.title)
ws.set_cell_style(
row, 4,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(
row, 4, getattr(contrib.country, 'name', ' '))
ws.set_cell_value(row, 5, contrib.updates.total_value)
ws.set_cell_value(row, 6, contrib.target_value)
ws.set_cell_style(
row, 7,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(
row, 7, '{}%'.format(
calculate_percentage(
contrib.updates.total_value,
period.actual_value)))
if disaggregation_types_length:
col = 8
for category, types in disaggregations.items():
for type in [t for t in types.keys()]:
ws.set_cell_value(
row, col,
contrib.get_disaggregation_of(
category, type) or '')
col += 1
ws.set_cell_value(
row, col,
contrib.get_disaggregation_target_of(
category, type) or '')
col += 1
row += 1
if len(contrib.contributors) < 1:
continue
# r13
ws.set_cell_style(row, 3, Style(font=Font(bold=True)))
ws.set_cell_value(row, 3, 'Level 2 sub-contributors:')
row += 1
for subcontrib in contrib.contributors:
# r14
ws.set_cell_style(
row, 3,
Style(alignment=Alignment(wrap_text=True)))
ws.set_cell_value(row, 3, subcontrib.project.title)
ws.set_cell_style(
row, 4,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(
row, 4, getattr(subcontrib.country, 'name',
' '))
ws.set_cell_value(row, 5, subcontrib.actual_value)
ws.set_cell_value(row, 6, subcontrib.target_value)
ws.set_cell_style(
row, 7,
Style(alignment=Alignment(horizontal='right')))
ws.set_cell_value(
row, 7, '{}%'.format(
calculate_percentage(
subcontrib.actual_value,
period.actual_value)))
if disaggregation_types_length:
col = 8
for category, types in disaggregations.items():
for type in [t for t in types.keys()]:
ws.set_cell_value(
row, col,
subcontrib.get_disaggregation_of(
category, type) or '')
col += 1
ws.set_cell_value(
row, col,
subcontrib.
get_disaggregation_target_of(
category, type) or '')
col += 1
row += 1
# output
filename = '{}-{}-program-overview-report.xlsx'.format(
datetime.today().strftime('%Y%b%d'), program.id)
return utils.make_excel_response(wb, filename)
def actual_value(self):
if self._actual_value is None:
self._actual_value = calculate_percentage(self.actual_numerator, self.actual_denominator) \
if self.type == IndicatorType.PERCENTAGE \
else ensure_decimal(self.period.actual_value)
return self._actual_value
