def test_post_process_location_calc_with_zero_value_entry(self): unit_type = IndicatorBlueprint.PERCENTAGE calc_type = IndicatorBlueprint.SUM display_type = IndicatorBlueprint.RATIO blueprint = RatioTypeIndicatorBlueprintFactory( unit=unit_type, calculation_formula_across_locations=calc_type, calculation_formula_across_periods=calc_type, display_type=display_type, ) partneractivity_reportable = RatioReportableToPartnerActivityProjectContextFactory( content_object=self.project_context, blueprint=blueprint) partneractivity_reportable.disaggregations.clear() add_disaggregations_to_reportable( partneractivity_reportable, disaggregation_targets=["age", "gender", "height"]) LocationWithReportableLocationGoalFactory( location=self.loc1, reportable=partneractivity_reportable, ) ir = ClusterIndicatorReportFactory( reportable=partneractivity_reportable, report_status=INDICATOR_REPORT_STATUS.due, ) # Creating Level-3 disaggregation location data for all locations generate_3_num_disagg_data(partneractivity_reportable, indicator_type="ratio") loc_data1 = ir.indicator_location_data.first() # Mark some data entries on location data 1 to be zero level_reported_3_key = None tuple_disaggregation = get_cast_dictionary_keys_as_tuple( loc_data1.disaggregation) for key in tuple_disaggregation: if len(key) == 3: level_reported_3_key = key break validated_data = copy.deepcopy(loc_data1.disaggregation) old_totals = validated_data['()'] loc_data1.disaggregation[str(level_reported_3_key)]['d'] = 0 loc_data1.disaggregation[str(level_reported_3_key)]['v'] = 0 loc_data1.disaggregation[str(level_reported_3_key)]['c'] = 0 loc_data1.save() RatioIndicatorDisaggregator.post_process(loc_data1) self.assertNotEqual(old_totals['c'], loc_data1.disaggregation['()']['c'])
def test_post_process_reporting_period_percentage_calc(self): unit_type = IndicatorBlueprint.PERCENTAGE calc_type = IndicatorBlueprint.SUM display_type = IndicatorBlueprint.RATIO blueprint = RatioTypeIndicatorBlueprintFactory( unit=unit_type, calculation_formula_across_locations=calc_type, calculation_formula_across_periods=calc_type, display_type=display_type, ) partneractivity_reportable = RatioReportableToPartnerActivityProjectContextFactory( content_object=self.project_context, blueprint=blueprint) partneractivity_reportable.disaggregations.clear() add_disaggregations_to_reportable( partneractivity_reportable, disaggregation_targets=["age", "gender", "height"]) LocationWithReportableLocationGoalFactory( location=self.loc1, reportable=partneractivity_reportable, ) LocationWithReportableLocationGoalFactory( location=self.loc2, reportable=partneractivity_reportable, ) for _ in range(2): ClusterIndicatorReportFactory( reportable=partneractivity_reportable, report_status=INDICATOR_REPORT_STATUS.due, ) # Creating Level-3 disaggregation location data for all locations generate_3_num_disagg_data(partneractivity_reportable, indicator_type="ratio") for loc_data in IndicatorLocationData.objects.filter( indicator_report__reportable=partneractivity_reportable): RatioIndicatorDisaggregator.post_process(loc_data) # Indicator total only gets calculated if it's accepted or is sent back for ir in partneractivity_reportable.indicator_reports.all(): ir.report_status = INDICATOR_REPORT_STATUS.accepted ir.save() latest_accepted_indicator_report = partneractivity_reportable.indicator_reports.order_by( '-time_period_start').first() self.assertEquals(partneractivity_reportable.total['c'] * 100, latest_accepted_indicator_report.total['c'] * 100)
def test_post_process_location_percentage_calc(self): unit_type = IndicatorBlueprint.PERCENTAGE calc_type = IndicatorBlueprint.SUM display_type = IndicatorBlueprint.PERCENTAGE blueprint = RatioTypeIndicatorBlueprintFactory( unit=unit_type, calculation_formula_across_locations=calc_type, calculation_formula_across_periods=calc_type, display_type=display_type, ) partneractivity_reportable = RatioReportableToPartnerActivityProjectContextFactory( content_object=self.project_context, blueprint=blueprint) partneractivity_reportable.disaggregations.clear() add_disaggregations_to_reportable( partneractivity_reportable, disaggregation_targets=["age", "gender", "height"]) LocationWithReportableLocationGoalFactory( location=self.loc1, reportable=partneractivity_reportable, ) LocationWithReportableLocationGoalFactory( location=self.loc2, reportable=partneractivity_reportable, ) ir = ClusterIndicatorReportFactory( reportable=partneractivity_reportable, report_status=INDICATOR_REPORT_STATUS.due, ) # Creating Level-3 disaggregation location data for all locations generate_3_num_disagg_data(partneractivity_reportable, indicator_type="ratio") v_total = 0 d_total = 0 for loc_data in ir.indicator_location_data.all(): RatioIndicatorDisaggregator.post_process(loc_data) v_total += loc_data.disaggregation['()']['v'] d_total += loc_data.disaggregation['()']['d'] ratio_value = v_total / (d_total * 1.0) self.assertEquals(ir.total['c'], ratio_value * 100)
def trigger_indicator_report_recalculation(sender, instance, **kwargs): """ Whenever an indicator blueprint is saved, IndicatorReport objects linked to this IndicatorBlueprint via its Reportable should all be recalculated for its total. """ irs = IndicatorReport.objects.filter( reportable__in=instance.reportables.all()) if instance.unit == IndicatorBlueprint.NUMBER: for ir in irs: QuantityIndicatorDisaggregator.calculate_indicator_report_total(ir) elif instance.unit == IndicatorBlueprint.PERCENTAGE: for ir in irs: RatioIndicatorDisaggregator.calculate_indicator_report_total(ir)
def test_post_process_location_ratio_calc(self): unit_type = IndicatorBlueprint.PERCENTAGE calc_type = IndicatorBlueprint.RATIO indicator = Reportable.objects.filter( blueprint__unit=unit_type, blueprint__calculation_formula_across_locations=calc_type, ).first() indicator_report = indicator.indicator_reports.first() v_total = 0 d_total = 0 ratio_value = 0 for loc_data in indicator_report.indicator_location_data.all(): RatioIndicatorDisaggregator.post_process(loc_data) v_total += loc_data.disaggregation['()']['v'] d_total += loc_data.disaggregation['()']['d'] ratio_value = v_total / (d_total * 1.0) self.assertEquals(indicator_report.total['c'], ratio_value)
def import_data(self): for self.sheet in self.wb.worksheets: # Find "Location ID" column location_column_id = None for column in range(1, MAX_COLUMNS): if self.sheet.cell(row=COLUMN_HASH_ID, column=column).value == "#loc+id": location_column_id = column break if not location_column_id: return "Cannot find Location ID column" # Find "Total" column total_column_id = None for column in range(1, MAX_COLUMNS): if self.sheet.cell(row=1, column=column).value == "Total": total_column_id = column break if not total_column_id: return "Cannot find Total column" # Find first Disaggregation Value column dis_data_column_start_id = None for column in range(1, MAX_COLUMNS): if not self.sheet.cell(row=COLUMN_HASH_ID, column=column).value: break if "#indicator+value" in self.sheet.cell(row=COLUMN_HASH_ID, column=column).value: dis_data_column_start_id = column break # Iterate over rows and save disaggregation values for row in range(COLUMN_HASH_ID + 1, self.sheet.max_row): # If row is empty, end of sheet if not self.sheet.cell(row=row, column=1).value: break # Get IndicatorLocationData ID try: ild_id = str(int(self.sheet.cell(row=row, column=location_column_id).value)) ind = IndicatorLocationData.objects.filter(pk=ild_id) # Check if indicator has parent (UNICEF) # If does, use parent to check partner if self.partner and ind.filter(indicator_report__parent__isnull=False): if not ind.filter( indicator_report__parent__reportable__partner_activity_project_contexts__project__partner=self.partner ).exists(): return "Parent of Indicator ID " \ + ild_id \ + " does not belong to partner " \ + str(self.partner) # Check if Partner is allowed to modify data elif self.partner and ind.filter( Q(**{ 'indicator_report__reportable__cluster_objectives' '__cluster__partner_projects__partner': self.partner }) | Q(**{ 'indicator_report__reportable' '__cluster_objectives__cluster_activities__partner_activities' '__partner': self.partner }) | Q(**{ 'indicator_report__reportable' '__cluster_activities__cluster_objective__cluster__partner_projects' '__partner': self.partner }) | Q(**{ 'indicator_report__reportable' '__cluster_activities__partner_activities' '__partner': self.partner }) | Q(**{ 'indicator_report__reportable' '__partner_activity_project_contexts__project' '__partner': self.partner }) | Q(**{ 'indicator_report__reportable' '__partner_projects' '__partner': self.partner })).count() == 0: return "Indicator ID " + ild_id + " does not belong to partner " + str(self.partner) indicator = IndicatorLocationData.objects.get( pk=int(self.sheet.cell(row=row, column=location_column_id).value) ) # Check if Indicator Report is not able to submit anymore if not indicator.indicator_report.can_import: transaction.rollback() return "Indicator in row {} is already submitted. Please remove row and try again.".format( row,) except IndicatorLocationData.DoesNotExist: return "Cannot find Indicator Location Data data for ID " \ + str(self.sheet.cell(row=row, column=location_column_id).value) blueprint = indicator.indicator_report.reportable.blueprint data = indicator.disaggregation # Prepare already_updated_row_value = False for column in range(dis_data_column_start_id if dis_data_column_start_id else total_column_id, total_column_id + 1): try: value = self.sheet.cell(row=row, column=column).value # Check if value is present in cell if value is not None: # Evaluate ID of Disaggregation Type dis_type_id = "()" dis_type_value = self.sheet.cell(row=2, column=column).value if dis_type_value: dis_type_value = sorted(list(map(int, str(dis_type_value).split(","))), key=int) dis_type_id = str(tuple(dis_type_value)) if dis_type_id not in data: # Check if data is proper disaggregation value for dt in dis_type_value: if not DisaggregationValue.objects.filter(pk=dt).exists(): transaction.rollback() return "Disaggregation {} does not exists".format( self.sheet.cell(row=4, column=column).value) # Check if filled disaggregation values belongs to their type dv = DisaggregationValue.objects.get(pk=dt) if dv.disaggregation.id not in indicator.disaggregation_reported_on: transaction.rollback() return "Disaggregation {} does not belong to this Indicator".format( self.sheet.cell(row=4, column=column).value) # Create value data[dis_type_id] = dict() already_updated_row_value = True # Update values if blueprint.unit == IndicatorBlueprint.NUMBER: data[dis_type_id]["v"] = value else: if isinstance(value, datetime): transaction.rollback() return "Value in column {}, row {} is Date Time. Please format row to Plain Text."\ .format(self.sheet.cell(row=4, column=column).value, row) values = value.split("/") data[dis_type_id]["v"] = int(values[0]) data[dis_type_id]["d"] = int(values[1]) else: # if value is not present, check if it should be # all rows need to updated # Evaluate ID of Disaggregation Type dis_type_value = self.sheet.cell(row=2, column=column).value if dis_type_value: dis_type_value = sorted(list(map(int, str(dis_type_value).split(","))), key=int) if dis_type_value: if len(dis_type_value) == indicator.level_reported: # Check if data is proper disaggregation value for dt in dis_type_value: dv = DisaggregationValue.objects.get(pk=dt) if dv.disaggregation.id in indicator.disaggregation_reported_on and \ already_updated_row_value: transaction.rollback() return "Please fulfill required value to column {}, row {}"\ .format(self.sheet.cell(row=4, column=column).value, row) except Exception: traceback.print_exc() transaction.rollback() return "Cannot assign disaggregation value to column {}, row {}"\ .format(self.sheet.cell(row=4, column=column).value, row) indicator.disaggregation = data indicator.save() if blueprint.unit == IndicatorBlueprint.NUMBER: QuantityIndicatorDisaggregator.post_process(indicator) if blueprint.unit == IndicatorBlueprint.PERCENTAGE: RatioIndicatorDisaggregator.post_process(indicator) return
def generate_1_num_disagg_data(reportable, indicator_type="quantity"): # IndicatorReport from QuantityReportable object - # IndicatorLocationData locations = Location.objects.all() for idx, indicator_report_from_reportable in enumerate( reportable.indicator_reports.all()): disagg_idx = 0 # 1 num_disaggregation & 0 level_reported location = locations[disagg_idx] if indicator_type == "quantity": disaggregation = { '()': { 'v': random.randint(50, 1000), 'd': 0, 'c': 0 } } elif indicator_type == "ratio": disaggregation = { '()': { 'v': random.randint(50, 1000), 'd': random.randint(2000, 4000), 'c': 0 } } location_data = IndicatorLocationDataFactory( indicator_report=indicator_report_from_reportable, location=location, num_disaggregation=1, level_reported=0, disaggregation_reported_on=list(), disaggregation=disaggregation, ) if indicator_type == "quantity": QuantityIndicatorDisaggregator.post_process(location_data) elif indicator_type == "ratio": RatioIndicatorDisaggregator.post_process(location_data) disagg_idx += 1 # 1 num_disaggregation & 1 level_reported disaggregation_comb_1_pairs = list( combinations( list( indicator_report_from_reportable.disaggregations. values_list('id', flat=True)), 1)) for pair in disaggregation_comb_1_pairs: location = locations[disagg_idx] location_data = IndicatorLocationDataFactory( indicator_report=indicator_report_from_reportable, location=location, num_disaggregation=1, level_reported=1, disaggregation_reported_on=pair, disaggregation=generate_data_combination_entries( indicator_report_from_reportable.disaggregation_values( id_only=True, filter_by_id__in=pair), indicator_type=indicator_type, r=1)) if indicator_type == "quantity": QuantityIndicatorDisaggregator.post_process(location_data) elif indicator_type == "ratio": RatioIndicatorDisaggregator.post_process(location_data) disagg_idx += 1
def import_data(self): partner_contribution = None challenges = None proposed_way_forward = None pd_output_narratives = dict() for idx, self.sheet in enumerate(self.wb.worksheets): if self.sheet.title.lower() == 'readme': continue # Find "Location ID" column location_column_id = None for column in range(1, MAX_COLUMNS): if self.sheet.cell(row=COLUMN_HASH_ID, column=column).value == "#loc+id": location_column_id = column break if not location_column_id: return "Cannot find Location ID column" # Find "Total" column total_column_id = None for column in range(1, MAX_COLUMNS): if self.sheet.cell(row=1, column=column).value == "Total": total_column_id = column break if not total_column_id: return "Cannot find Total column" # Find first Disaggregation Value column dis_data_column_start_id = None for column in range(1, MAX_COLUMNS): if not self.sheet.cell(row=COLUMN_HASH_ID, column=column).value: break if "#indicator+value" in self.sheet.cell(row=COLUMN_HASH_ID, column=column).value: dis_data_column_start_id = column break # Find "Progress" column progress_column_id = None for column in range(1, MAX_COLUMNS): if self.sheet.cell(row=COLUMN_HASH_ID, column=column).value == "#pr+id": progress_column_id = column break if not progress_column_id: return "Cannot find Progress ID column" # Other Info columns data retrieve # Partner contribution to date if not partner_contribution: partner_contribution = self.sheet.cell(row=COLUMN_HASH_ID + 1, column=9).value # Challenges/bottlenecks in the reporting period if not challenges: challenges = self.sheet.cell(row=COLUMN_HASH_ID + 1, column=11).value # Proposed way forward if not proposed_way_forward: proposed_way_forward = self.sheet.cell(row=COLUMN_HASH_ID + 1, column=12).value # ... and assign if is QPR try: progress_id = self.sheet.cell(row=COLUMN_HASH_ID + 1, column=progress_column_id).value pr = ProgressReport.objects.get(pk=progress_id) except ProgressReport.DoesNotExist: return "Cannot find Progress Report" # Iterate over rows and save disaggregation values for row in range(COLUMN_HASH_ID + 1, self.sheet.max_row): # If row is empty, end of sheet if not self.sheet.cell(row=row, column=1).value: # Update Other Info sheet if pr.report_type == common.QPR_TYPE: pr.partner_contribution_to_date = partner_contribution pr.challenges_in_the_reporting_period = challenges pr.proposed_way_forward = proposed_way_forward pr.save() break # Get IndicatorLocationData ID try: ild_id = str( int( self.sheet.cell(row=row, column=location_column_id).value)) ind = IndicatorLocationData.objects.filter(pk=ild_id) # Check if indicator has parent (UNICEF) # If does, use parent to check partner if self.partner and ind.filter( indicator_report__parent__isnull=False): if not ind.filter( indicator_report__parent__reportable__partner_activity_project_contexts__project__partner =self.partner).exists(): return "Parent of Indicator ID " \ + ild_id \ + " does not belong to partner " \ + str(self.partner) # Check if Partner is allowed to modify data elif self.partner and ind.filter( indicator_report__progress_report__programme_document__partner =self.partner).count() == 0: return "Indicator ID " + ild_id + " does not belong to partner " + str( self.partner) indicator = IndicatorLocationData.objects.get(pk=int( self.sheet.cell(row=row, column=location_column_id).value)) # Check if Indicator Report is not able to submit anymore if not indicator.indicator_report.can_import: transaction.rollback() return "Indicator in row {} is already submitted. Please remove row and try again.".format( row, ) except IndicatorLocationData.DoesNotExist: return "Cannot find Indicator Location Data data for ID " \ + str(self.sheet.cell(row=row, column=location_column_id).value) blueprint = indicator.indicator_report.reportable.blueprint data = indicator.disaggregation if pr.report_type == common.QPR_TYPE: narrative_assessment = self.sheet.cell(row=row, column=19).value llo = indicator.indicator_report.reportable.content_object if llo.id not in pd_output_narratives \ and (narrative_assessment is not None and narrative_assessment != ''): pd_output_narratives[llo.id] = narrative_assessment indicator.indicator_report.narrative_assessment = narrative_assessment indicator.indicator_report.save() pr.indicator_reports.filter( reportable__lower_level_outputs=llo).update( narrative_assessment=narrative_assessment) # Prepare already_updated_row_value = False for column in range( dis_data_column_start_id if dis_data_column_start_id else total_column_id, total_column_id + 1): try: value = self.sheet.cell(row=row, column=column).value # Check if value is present in cell if value is not None: # Evaluate ID of Disaggregation Type dis_type_id = "()" dis_type_value = self.sheet.cell( row=2, column=column).value if dis_type_value: dis_type_value = sorted(list( map(int, str(dis_type_value).split(","))), key=int) dis_type_id = str(tuple(dis_type_value)) if dis_type_id not in data: # Check if data is proper disaggregation value for dt in dis_type_value: if not DisaggregationValue.objects.filter( pk=dt).exists(): transaction.rollback() return "Disaggregation {} does not exists".format( self.sheet.cell( row=4, column=column).value) # Check if filled disaggregation values # belongs to their type dv = DisaggregationValue.objects.get(pk=dt) if dv.disaggregation.id not in indicator.disaggregation_reported_on: transaction.rollback() return "Disaggregation {} does not belong to this Indicator".format( self.sheet.cell( row=4, column=column).value) # Create value data[dis_type_id] = dict() already_updated_row_value = True # Update values if blueprint.unit == IndicatorBlueprint.NUMBER: data[dis_type_id]["v"] = int(value) else: if isinstance(value, datetime): transaction.rollback() return "Value in column {}, row {} is Date Time. Please format row to Plain Text."\ .format(self.sheet.cell(row=4, column=column).value, row) values = value.split("/") data[dis_type_id]["v"] = int(values[0]) data[dis_type_id]["d"] = int(values[1]) else: # if value is not present, check if it should be # all rows need to updated # Evaluate ID of Disaggregation Type dis_type_value = self.sheet.cell( row=2, column=column).value if dis_type_value: dis_type_value = sorted(list( map(int, str(dis_type_value).split(","))), key=int) if dis_type_value: if len(dis_type_value ) == indicator.level_reported: # Check if data is proper disaggregation # value for dt in dis_type_value: dv = DisaggregationValue.objects.get( pk=dt) if dv.disaggregation.id in indicator.disaggregation_reported_on and \ already_updated_row_value: transaction.rollback() return "Please fulfill required value to column {}, row {}"\ .format(self.sheet.cell(row=4, column=column).value, row) except Exception: traceback.print_exc() transaction.rollback() return "Cannot assign disaggregation value to column {}, row {}"\ .format(self.sheet.cell(row=4, column=column).value, row) indicator.disaggregation = data indicator.save() if blueprint.unit == IndicatorBlueprint.NUMBER: QuantityIndicatorDisaggregator.post_process(indicator) if blueprint.unit == IndicatorBlueprint.PERCENTAGE: RatioIndicatorDisaggregator.post_process(indicator) return