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