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