def test_post_process_reporting_period_max_calc(self):
unit_type = IndicatorBlueprint.NUMBER
calc_type = IndicatorBlueprint.MAX
blueprint = factories.QuantityTypeIndicatorBlueprintFactory(
unit=unit_type,
calculation_formula_across_locations=calc_type,
calculation_formula_across_periods=calc_type,
)
partneractivity_reportable = factories.QuantityReportableToPartnerActivityProjectContextFactory(
content_object=self.project_context, blueprint=blueprint
)
partneractivity_reportable.disaggregations.clear()
add_disaggregations_to_reportable(
partneractivity_reportable,
disaggregation_targets=["age", "gender", "height"]
)
factories.LocationWithReportableLocationGoalFactory(
location=self.loc1,
reportable=partneractivity_reportable,
)
factories.LocationWithReportableLocationGoalFactory(
location=self.loc2,
reportable=partneractivity_reportable,
)
for _ in range(2):
factories.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="quantity")
report_total = 0
for loc_data in IndicatorLocationData.objects.filter(indicator_report__reportable=partneractivity_reportable):
QuantityIndicatorDisaggregator.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()
if ir.total['c'] > report_total:
report_total = ir.total['c']
self.assertEquals(partneractivity_reportable.total['c'], report_total)
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_avg_calc(self):
unit_type = IndicatorBlueprint.NUMBER
calc_type = IndicatorBlueprint.AVG
blueprint = factories.QuantityTypeIndicatorBlueprintFactory(
unit=unit_type,
calculation_formula_across_locations=calc_type,
)
partneractivity_reportable = factories.QuantityReportableToPartnerActivityProjectContextFactory(
content_object=self.project_context, blueprint=blueprint
)
partneractivity_reportable.disaggregations.clear()
add_disaggregations_to_reportable(
partneractivity_reportable,
disaggregation_targets=["age", "gender", "height"]
)
factories.LocationWithReportableLocationGoalFactory(
location=self.loc1,
reportable=partneractivity_reportable,
)
factories.LocationWithReportableLocationGoalFactory(
location=self.loc2,
reportable=partneractivity_reportable,
)
ir = factories.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="quantity")
avg_value = 0
for loc_data in ir.indicator_location_data.all():
QuantityIndicatorDisaggregator.post_process(loc_data)
avg_value += loc_data.disaggregation['()']['c']
avg_value /= (ir.indicator_location_data.count() * 1.0)
self.assertEquals(ir.total['c'], avg_value)
def setUp(self):
self.workspace = factories.WorkspaceFactory()
self.response_plan = factories.ResponsePlanFactory(
workspace=self.workspace,
start=beginning_of_this_year,
end=end_of_this_year,
)
self.cluster = factories.ClusterFactory(type='cccm', response_plan=self.response_plan)
self.carto_table = factories.CartoDBTableFactory()
self.loc1 = factories.LocationFactory()
self.loc2 = factories.LocationFactory()
self.loc1.workspaces.add(self.workspace)
self.loc2.workspaces.add(self.workspace)
self.unicef_officer = factories.PersonFactory()
self.unicef_focal_point = factories.PersonFactory()
self.partner_focal_point = factories.PersonFactory()
self.objective = factories.ClusterObjectiveFactory(
cluster=self.cluster,
locations=[
self.loc1,
self.loc2,
]
)
self.activity = factories.ClusterActivityFactory(
cluster_objective=self.objective,
locations=[
self.loc1, self.loc2
]
)
self.partner = factories.PartnerFactory(country_code=faker.country_code(), clusters=[self.cluster, ])
self.user = factories.NonPartnerUserFactory()
self.partner_user = factories.PartnerUserFactory(partner=self.partner)
factories.ClusterPRPRoleFactory(user=self.user, workspace=self.workspace, cluster=self.cluster, role=PRP_ROLE_TYPES.cluster_imo)
factories.IPPRPRoleFactory(user=self.partner_user, workspace=self.workspace, role=PRP_ROLE_TYPES.ip_authorized_officer)
factories.IPPRPRoleFactory(user=self.partner_user, workspace=self.workspace, cluster=None, role=PRP_ROLE_TYPES.cluster_member)
self.project = factories.PartnerProjectFactory(
partner=self.partner,
clusters=[self.cluster],
locations=[self.loc1, self.loc2],
)
self.p_activity = factories.ClusterActivityPartnerActivityFactory(
partner=self.partner,
cluster_activity=self.activity,
)
self.project_context = factories.PartnerActivityProjectContextFactory(
project=self.project,
activity=self.p_activity,
start_date=datetime.date(today.year, 3, 1),
end_date=datetime.date(today.year, 10, 25),
)
self.sample_disaggregation_value_map = {
"height": ["tall", "medium", "short", "extrashort"],
"age": ["1-2m", "3-4m", "5-6m", '7-10m', '11-13m', '14-16m'],
"gender": ["male", "female", "other"],
}
blueprint = factories.QuantityTypeIndicatorBlueprintFactory(
unit=IndicatorBlueprint.NUMBER,
calculation_formula_across_locations=IndicatorBlueprint.SUM,
calculation_formula_across_periods=IndicatorBlueprint.SUM,
)
self.partneractivity_reportable = factories.QuantityReportableToPartnerActivityProjectContextFactory(
content_object=self.project_context, blueprint=blueprint
)
factories.LocationWithReportableLocationGoalFactory(
location=self.loc1,
reportable=self.partneractivity_reportable,
)
factories.LocationWithReportableLocationGoalFactory(
location=self.loc2,
reportable=self.partneractivity_reportable,
)
self.pd = factories.ProgrammeDocumentFactory(
workspace=self.workspace,
partner=self.partner,
sections=[factories.SectionFactory(), ],
unicef_officers=[self.unicef_officer, ],
unicef_focal_point=[self.unicef_focal_point, ],
partner_focal_point=[self.partner_focal_point, ]
)
for idx in range(2):
qpr_period = factories.QPRReportingPeriodDatesFactory(programme_document=self.pd)
factories.ProgressReportFactory(
start_date=qpr_period.start_date,
end_date=qpr_period.end_date,
due_date=qpr_period.due_date,
report_number=idx + 1,
report_type=qpr_period.report_type,
is_final=False,
programme_document=self.pd,
submitted_by=self.user,
submitting_user=self.user,
)
for idx in range(6):
hr_period = factories.HRReportingPeriodDatesFactory(programme_document=self.pd)
factories.ProgressReportFactory(
start_date=hr_period.start_date,
end_date=hr_period.end_date,
due_date=hr_period.due_date,
report_number=idx + 1,
report_type=hr_period.report_type,
is_final=False,
programme_document=self.pd,
submitted_by=self.user,
submitting_user=self.user,
)
self.cp_output = factories.PDResultLinkFactory(
programme_document=self.pd,
)
self.llo = factories.LowerLevelOutputFactory(
cp_output=self.cp_output,
)
self.llo_reportable = factories.QuantityReportableToLowerLevelOutputFactory(
content_object=self.llo,
blueprint=factories.QuantityTypeIndicatorBlueprintFactory(
unit=IndicatorBlueprint.NUMBER,
calculation_formula_across_locations=IndicatorBlueprint.SUM,
)
)
self.llo_reportable.disaggregations.clear()
self.partneractivity_reportable.disaggregations.clear()
# Create the disaggregations and values in the db for all response plans
# including one for no response plan as well
for disagg_name, values in self.sample_disaggregation_value_map.items():
disagg = factories.IPDisaggregationFactory(name=disagg_name)
cluster_disagg = factories.DisaggregationFactory(name=disagg_name, response_plan=self.response_plan)
self.llo_reportable.disaggregations.add(disagg)
self.partneractivity_reportable.disaggregations.add(cluster_disagg)
for value in values:
factories.DisaggregationValueFactory(
disaggregation=cluster_disagg,
value=value
)
factories.DisaggregationValueFactory(
disaggregation=disagg,
value=value
)
factories.LocationWithReportableLocationGoalFactory(
location=self.loc1,
reportable=self.llo_reportable,
)
factories.LocationWithReportableLocationGoalFactory(
location=self.loc2,
reportable=self.llo_reportable,
)
for _ in range(2):
factories.ClusterIndicatorReportFactory(
reportable=self.partneractivity_reportable,
report_status=INDICATOR_REPORT_STATUS.submitted,
)
# Creating Level-3 disaggregation location data for all locations
generate_3_num_disagg_data(self.partneractivity_reportable, indicator_type="quantity")
for loc_data in IndicatorLocationData.objects.filter(indicator_report__reportable=self.partneractivity_reportable):
QuantityIndicatorDisaggregator.post_process(loc_data)
for pr in self.pd.progress_reports.all():
factories.ProgressReportIndicatorReportFactory(
progress_report=pr,
reportable=self.llo_reportable,
report_status=INDICATOR_REPORT_STATUS.submitted,
overall_status=OVERALL_STATUS.met,
)
# Creating Level-3 disaggregation location data for all locations
generate_3_num_disagg_data(self.llo_reportable, indicator_type="quantity")
for loc_data in IndicatorLocationData.objects.filter(indicator_report__reportable=self.llo_reportable):
QuantityIndicatorDisaggregator.post_process(loc_data)
super().setUp()
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 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