def _child_health_monthly_table(state_ids, day):
helper = ChildHealthMonthlyAggregationHelper(state_ids, force_to_date(day))
celery_task_logger.info("Creating temporary table")
with get_cursor(ChildHealthMonthly) as cursor:
cursor.execute(helper.drop_temporary_table())
cursor.execute(helper.create_temporary_table())
# https://github.com/celery/celery/issues/4274
sub_aggregations = [
_child_health_helper.delay(query=query, params=params)
for query, params in helper.pre_aggregation_queries()
]
for sub_aggregation in sub_aggregations:
sub_aggregation.get(disable_sync_subtasks=False)
celery_task_logger.info("Inserting into child_health_monthly_table")
with transaction.atomic(using=db_for_read_write(ChildHealthMonthly)):
_run_custom_sql_script([
"SELECT create_new_table_for_month('child_health_monthly', %s)",
], day)
ChildHealthMonthly.aggregate(state_ids, force_to_date(day))
celery_task_logger.info("Dropping temporary table")
with get_cursor(ChildHealthMonthly) as cursor:
cursor.execute(helper.drop_temporary_table())
def config(self):
config = {
'domain': self.domain,
}
if self.request.GET.get('startdate'):
startdate = force_to_date(self.request.GET.get('startdate'))
else:
startdate = datetime.datetime.now()
if self.request.GET.get('enddate'):
enddate = force_to_date(self.request.GET.get('enddate'))
else:
enddate = datetime.datetime.now()
config['startdate'] = startdate
config['enddate'] = enddate
config['product_program'] = self.request.GET.get('product_program')
config['products'] = self.request.GET.get('products')
config['product_product'] = self.request.GET.get('product_product')
config['selected_location'] = self.request.GET.get('location_id')
if self.selected_location_type == "PPS":
config['pps_id'] = self.request.GET.get('location_id')
elif self.selected_location_type == "District":
config['district_id'] = self.request.GET.get('location_id')
elif self.selected_location_type == "Region":
config['region_id'] = self.request.GET.get('location_id')
return config
def prepare_export_reports(domain, selected_date, next_month_start,
selected_location, selected_ministry,
beneficiary_type):
location_filters = build_location_filters(selected_location,
selected_ministry,
with_child=False)
sort_column = 'name'
selected_date = force_to_date(selected_date)
next_month_start = force_to_date(next_month_start)
columns = []
data = []
if beneficiary_type == 'child':
columns = (
('name', 'Name'),
('age', 'Age'),
('gender', 'Gender'),
('lastImmunizationType', 'Last Immunization Type'),
('lastImmunizationDate', 'Last Immunization Date'),
)
data = ChildQueryHelper.list(domain, next_month_start,
location_filters, sort_column)
elif beneficiary_type == 'eligible_couple':
columns = (
('name', 'Name'),
('age', 'Age'),
('currentFamilyPlanningMethod', 'Current Family Planing Method'),
('adoptionDateOfFamilyPlaning',
'Adoption Date Of Family Planning'),
)
data = EligibleCoupleQueryHelper.list(domain, selected_date,
location_filters, sort_column)
month_end = selected_date + relativedelta(months=1) - relativedelta(
days=1)
data = EligibleCoupleQueryHelper.update_list(data, month_end)
elif beneficiary_type == 'pregnant_women':
columns = (
('name', 'Name'),
('age', 'Age'),
('pregMonth', 'Preg. Month'),
('highRiskPregnancy', 'High Risk Pregnancy'),
('noOfAncCheckUps', 'No. Of ANC Check-Ups'),
)
data = PregnantWomanQueryHelper.list(domain, selected_date,
location_filters, sort_column)
export_columns = [col[1] for col in columns]
export_data = [export_columns]
for row in data:
row_data = [row[col[0]] or 'N/A' for col in columns]
export_data.append(row_data)
return create_excel_file(domain, [[beneficiary_type, export_data]],
beneficiary_type, 'xlsx')
def prepare_export_reports(domain, selected_date, next_month_start, selected_location,
selected_ministry, beneficiary_type):
location_filters = build_location_filters(selected_location, selected_ministry, with_child=False)
sort_column = 'name'
selected_date = force_to_date(selected_date)
next_month_start = force_to_date(next_month_start)
columns = []
data = []
if beneficiary_type == 'child':
columns = (
('name', 'Name'),
('age', 'Age'),
('gender', 'Gender'),
('lastImmunizationType', 'Last Immunization Type'),
('lastImmunizationDate', 'Last Immunization Date'),
)
data = ChildQueryHelper.list(domain, next_month_start, location_filters, sort_column)
elif beneficiary_type == 'eligible_couple':
columns = (
('name', 'Name'),
('age', 'Age'),
('currentFamilyPlanningMethod', 'Current Family Planing Method'),
('adoptionDateOfFamilyPlaning', 'Adoption Date Of Family Planning'),
)
data = EligibleCoupleQueryHelper.list(domain, selected_date, location_filters, sort_column)
elif beneficiary_type == 'pregnant_women':
columns = (
('name', 'Name'),
('age', 'Age'),
('pregMonth', 'Preg. Month'),
('highRiskPregnancy', 'High Risk Pregnancy'),
('noOfAncCheckUps', 'No. Of ANC Check-Ups'),
)
data = PregnantWomanQueryHelper.list(domain, selected_date, location_filters, sort_column)
export_columns = [col[1] for col in columns]
export_data = [export_columns]
for row in data:
row_data = [row[col[0]] or 'N/A' for col in columns]
export_data.append(row_data)
return create_excel_file(
domain,
[[beneficiary_type, export_data]],
beneficiary_type,
'xlsx'
)
def config(self):
config = {
'domain': self.domain,
}
if self.request.GET.get('startdate'):
startdate = force_to_date(self.request.GET.get('startdate'))
else:
startdate = datetime.datetime.now()
if self.request.GET.get('enddate'):
enddate = force_to_date(self.request.GET.get('enddate'))
else:
enddate = datetime.datetime.now()
config['startdate'] = startdate
config['enddate'] = enddate
config['product_program'] = self.request.GET.get('program')
config['selected_location'] = self.request.GET.get('location_id')
return config
def _state_based_aggregation(model, day):
state_ids = (SQLLocation.objects.filter(
domain=DASHBOARD_DOMAIN,
location_type__name='state').values_list('location_id', flat=True))
agg_date = force_to_date(day)
for state_id in state_ids:
model.aggregate(state_id, agg_date)
def post(self, request, *args, **kwargs):
date_param = self.request.POST.get('date')
if not date_param:
messages.error(request, 'Date is required')
return redirect(self.urlname, domain=self.domain)
date = force_to_date(date_param)
run_aggregation(self.domain, date)
messages.success(request, 'Aggregation task has run.')
return redirect(self.urlname, domain=self.domain)
def post(self, request, *args, **kwargs):
date_param = self.request.POST.get('date')
if not date_param:
messages.error(request, 'Date is required')
return redirect(self.urlname, domain=self.domain)
date = force_to_date(date_param)
run_aggregation(self.domain, date)
messages.success(request, 'Aggregation task has run.')
return redirect(self.urlname, domain=self.domain)
def date_filter(self, date):
sorted_family_planning_method = sorted([
method for method in (self.fp_current_method_history or [])
if force_to_date(method[0]) <= date
], key=lambda method: force_to_date(method[0]))
sorted_preferred_family_planning_method = sorted([
method for method in (self.fp_preferred_method_history or [])
if force_to_date(method[0]) <= date
], key=lambda method: force_to_date(method[0]))
family_planning_forms = sorted(
timeend
for timeend in (self.family_planning_form_history or [])
if timeend <= date
)
return {
'family_planning_method': sorted_family_planning_method,
'preferred_family_planning_methods': sorted_preferred_family_planning_method,
'last_family_planning_form': family_planning_forms[-1] if family_planning_forms else None
}
def _child_health_monthly_table(state_ids, day):
helper = ChildHealthMonthlyAggregationHelper(state_ids, force_to_date(day))
celery_task_logger.info("Creating temporary table")
with get_cursor(ChildHealthMonthly) as cursor:
cursor.execute(helper.drop_temporary_table())
cursor.execute(helper.create_temporary_table())
sub_aggregations = group([
_child_health_helper.si(query=query, params=params)
for query, params in helper.pre_aggregation_queries()
]).apply_async()
sub_aggregations.get()
celery_task_logger.info("Inserting into child_health_monthly_table")
with transaction.atomic(using=db_for_read_write(ChildHealthMonthly)):
_run_custom_sql_script([
"SELECT create_new_table_for_month('child_health_monthly', %s)",
], day)
ChildHealthMonthly.aggregate(state_ids, force_to_date(day))
celery_task_logger.info("Dropping temporary table")
with get_cursor(ChildHealthMonthly) as cursor:
cursor.execute(helper.drop_temporary_table())
def _child_health_monthly_table(state_ids, day):
helper = ChildHealthMonthlyAggregationHelper(state_ids, force_to_date(day))
celery_task_logger.info("Creating temporary table")
with get_cursor(ChildHealthMonthly) as cursor:
cursor.execute(helper.drop_temporary_table())
cursor.execute(helper.create_temporary_table())
sub_aggregations = group([
_child_health_helper.si(query=query, params=params)
for query, params in helper.pre_aggregation_queries()
]).apply_async()
sub_aggregations.get()
celery_task_logger.info("Inserting into child_health_monthly_table")
with transaction.atomic(using=db_for_read_write(ChildHealthMonthly)):
_run_custom_sql_script([
"SELECT create_new_table_for_month('child_health_monthly', %s)",
], day)
ChildHealthMonthly.aggregate(state_ids, force_to_date(day))
celery_task_logger.info("Dropping temporary table")
with get_cursor(ChildHealthMonthly) as cursor:
cursor.execute(helper.drop_temporary_table())
def post(self, request, *args, **kwargs):
date_param = self.request.POST.get('date')
if not date_param:
messages.error(request, 'Date is required')
return redirect(self.urlname, domain=self.domain)
date = force_to_date(date_param)
update_child_table(self.domain)
update_child_history_table(self.domain)
update_ccs_record_table(self.domain)
update_woman_table(self.domain)
update_woman_history_table(self.domain)
update_agg_awc_table(self.domain, date)
update_agg_village_table(self.domain, date)
messages.success(request, 'Aggregation task has run.')
return redirect(self.urlname, domain=self.domain)
def icds_data_validation(day):
"""Checks all AWCs to validate that there will be no inconsistencies in the
reporting dashboard.
"""
# agg tables store the month like YYYY-MM-01
month = force_to_date(day)
month.replace(day=1)
return_values = ('state_name', 'district_name', 'block_name',
'supervisor_name', 'awc_name')
bad_wasting_awcs = AggChildHealthMonthly.objects.filter(
month=month,
aggregation_level=5).exclude(weighed_and_height_measured_in_month=(
F('wasting_moderate') + F('wasting_severe') +
F('wasting_normal'))).values_list(*return_values)
bad_stunting_awcs = AggChildHealthMonthly.objects.filter(
month=month, aggregation_level=5).exclude(
height_measured_in_month=(F('stunting_severe') +
F('stunting_moderate') +
F('stunting_normal'))).values_list(
*return_values)
bad_underweight_awcs = AggChildHealthMonthly.objects.filter(
month=month, aggregation_level=5).exclude(nutrition_status_weighed=(
F('nutrition_status_normal') +
F('nutrition_status_moderately_underweight') +
F('nutrition_status_severely_underweight'))).values_list(
*return_values)
bad_lbw_awcs = AggChildHealthMonthly.objects.filter(
month=month,
aggregation_level=5,
weighed_and_born_in_month__lt=F(
'low_birth_weight_in_month')).values_list(*return_values)
_send_data_validation_email(
return_values, month, {
'bad_wasting_awcs': bad_wasting_awcs,
'bad_stunting_awcs': bad_stunting_awcs,
'bad_underweight_awcs': bad_underweight_awcs,
'bad_lbw_awcs': bad_lbw_awcs,
})
def icds_data_validation(day):
"""Checks all AWCs to validate that there will be no inconsistencies in the
reporting dashboard.
"""
# agg tables store the month like YYYY-MM-01
month = force_to_date(day)
month.replace(day=1)
return_values = ('state_name', 'district_name', 'block_name', 'supervisor_name', 'awc_name')
bad_wasting_awcs = AggChildHealthMonthly.objects.filter(
month=month, aggregation_level=5
).exclude(
weighed_and_height_measured_in_month=(
F('wasting_moderate') + F('wasting_severe') + F('wasting_normal')
)
).values_list(*return_values)
bad_stunting_awcs = AggChildHealthMonthly.objects.filter(month=month, aggregation_level=5).exclude(
height_measured_in_month=(
F('stunting_severe') + F('stunting_moderate') + F('stunting_normal')
)
).values_list(*return_values)
bad_underweight_awcs = AggChildHealthMonthly.objects.filter(month=month, aggregation_level=5).exclude(
nutrition_status_weighed=(
F('nutrition_status_normal') +
F('nutrition_status_moderately_underweight') +
F('nutrition_status_severely_underweight')
)
).values_list(*return_values)
bad_lbw_awcs = AggChildHealthMonthly.objects.filter(
month=month, aggregation_level=5, weighed_and_born_in_month__lt=F('low_birth_weight_in_month')
).values_list(*return_values)
_send_data_validation_email(
return_values, month, {
'bad_wasting_awcs': bad_wasting_awcs,
'bad_stunting_awcs': bad_stunting_awcs,
'bad_underweight_awcs': bad_underweight_awcs,
'bad_lbw_awcs': bad_lbw_awcs,
})
def before_date(cls, child_health_case_id, date_):
ret = {
'weight_child_history': [],
'height_child_history': [],
'zscore_grading_wfa_history': [],
'zscore_grading_hfa_history': [],
'zscore_grading_wfh_history': [],
}
try:
child_history = ChildHistory.objects.get(child_health_case_id=child_health_case_id)
except ChildHistory.DoesNotExist:
return ret
for key in ret:
for history_date, history_value in getattr(child_history, key):
day = force_to_date(history_date)
if day < date_:
ret[key].append((day, history_value))
return ret
def before_date(cls, child_health_case_id, date_):
ret = {
'weight_child_history': [],
'height_child_history': [],
'zscore_grading_wfa_history': [],
'zscore_grading_hfa_history': [],
'zscore_grading_wfh_history': [],
}
try:
child_history = ChildHistory.objects.get(child_health_case_id=child_health_case_id)
except ChildHistory.DoesNotExist:
return ret
for key in ret:
for history_date, history_value in getattr(child_history, key):
day = force_to_date(history_date)
if day < date_:
ret[key].append((day, history_value))
return ret
def get_api_ag_school_data(month, state_id, last_person_case_id):
month_start = force_to_date(month).replace(day=1)
month_end = month_start + relativedelta(months=1, seconds=-1)
month_end_11yr = month_end - relativedelta(years=11)
month_start_14yr = month_start - relativedelta(years=14, seconds=-1)
school_data_query = BiharDemographicsView.objects.filter(
month=month,
state_id=state_id,
dob__lt=month_end_11yr,
dob__gte=month_start_14yr,
gender='F',
person_id__gt=last_person_case_id).order_by('person_id').values(
'person_id', 'person_name', 'out_of_school_status',
'last_class_attended_ever')
# To apply pagination on database query with data size length
limited_school_data = list(school_data_query[:CAS_API_PAGE_SIZE])
return limited_school_data, get_total_records_count(
BiharDemographicsView.__name__, month, state_id, month_end_11yr,
month_start_14yr)
def _agg_ls_table(day):
with transaction.atomic(using=db_for_read_write(AggLs)):
AggLs.aggregate(force_to_date(day))
def _agg_beneficiary_form(state_id, day):
with transaction.atomic(using=db_for_read_write(AggLs)):
AggregateBeneficiaryForm.aggregate(state_id, force_to_date(day))
def _agg_ls_awc_mgt_form(state_id, day):
with transaction.atomic(using=db_for_read_write(AggLs)):
AggregateLsAWCVisitForm.aggregate(state_id, force_to_date(day))
def _agg_child_health_table(day):
with transaction.atomic():
_run_custom_sql_script([
"SELECT create_new_aggregate_table_for_month('agg_child_health', %s)",
], day)
AggChildHealth.aggregate(force_to_date(day))
def _ccs_record_monthly_table(day):
with transaction.atomic(using=db_for_read_write(CcsRecordMonthly)):
_run_custom_sql_script([
"SELECT create_new_table_for_month('ccs_record_monthly', %s)",
], day)
CcsRecordMonthly.aggregate(force_to_date(day))
def get_date_du(case):
date = case.get_case_property('date_du')
if type(date) is not datetime:
return force_to_date(date)
return date
def _child_health_monthly_table(day):
with transaction.atomic():
_run_custom_sql_script([
"SELECT create_new_table_for_month('child_health_monthly', %s)",
], day)
ChildHealthMonthly.aggregate(force_to_date(day))
def migration_date(self):
return force_to_date(datetime.now() + timedelta(days=14))
def aggregate_awc_daily(day):
with transaction.atomic(using=db_for_read_write(AggAwcDaily)):
AggAwcDaily.aggregate(force_to_date(day))
def _agg_beneficiary_form(state_id, day):
with transaction.atomic(using=db_for_read_write(AggLs)):
AggregateBeneficiaryForm.aggregate(state_id, force_to_date(day))
def _agg_ccs_record_table(day):
with transaction.atomic(using=db_for_read_write(AggCcsRecord)):
_run_custom_sql_script([
"SELECT create_new_aggregate_table_for_month('agg_ccs_record', %s)",
], day)
AggCcsRecord.aggregate(force_to_date(day))
def _agg_ls_awc_mgt_form(state_id, day):
with transaction.atomic(using=db_for_read_write(AggLs)):
AggregateLsAWCVisitForm.aggregate(state_id, force_to_date(day))
def _agg_ls_table(day):
with transaction.atomic(using=db_for_read_write(AggLs)):
AggLs.aggregate(force_to_date(day))
def _daily_attendance_table(day):
DailyAttendance.aggregate(force_to_date(day))
def _agg_awc_table_weekly(day):
with transaction.atomic(using=db_for_read_write(AggAwc)):
AggAwc.weekly_aggregate(force_to_date(day))
def _agg_ls_vhnd_form(state_id, day):
with transaction.atomic(using=db_for_read_write(AggLs)):
AggregateLsVhndForm.aggregate(state_id, force_to_date(day))
def _agg_awc_table(day):
with transaction.atomic(using=db_for_read_write(AggAwc)):
_run_custom_sql_script([
"SELECT create_new_aggregate_table_for_month('agg_awc', %s)"
], day)
AggAwc.aggregate(force_to_date(day))
def _daily_attendance_table(day):
DailyAttendance.aggregate(force_to_date(day))
def get_date_du(case):
date = case.get_case_property('date_du')
if type(date) is not datetime:
return force_to_date(date)
return date
def _agg_awc_table(day):
with transaction.atomic(using=db_for_read_write(AggAwc)):
_run_custom_sql_script([
"SELECT create_new_aggregate_table_for_month('agg_awc', %s)"
], day)
AggAwc.aggregate(force_to_date(day))
def _ccs_record_monthly_table(day):
with transaction.atomic():
_run_custom_sql_script([
"SELECT create_new_table_for_month('ccs_record_monthly', %s)",
], day)
CcsRecordMonthly.aggregate(force_to_date(day))
def _agg_ls_vhnd_form(state_id, day):
with transaction.atomic(using=db_for_read_write(AggLs)):
AggregateLsVhndForm.aggregate(state_id, force_to_date(day))
def _agg_ccs_record_table(day):
with transaction.atomic():
_run_custom_sql_script([
"SELECT create_new_aggregate_table_for_month('agg_ccs_record', %s)",
], day)
AggCcsRecord.aggregate(force_to_date(day))
def get_delivery_date(encounter):
datestring = encounter.get_xform().xpath('delivery/date')
if datestring:
return force_to_date(datestring)
def aggregate_awc_daily(day):
with transaction.atomic(using=db_for_read_write(AggAwcDaily)):
AggAwcDaily.aggregate(force_to_date(day))
def _agg_child_health_table(day):
with transaction.atomic(using=db_for_read_write(AggChildHealth)):
_run_custom_sql_script([
"SELECT create_new_aggregate_table_for_month('agg_child_health', %s)",
], day)
AggChildHealth.aggregate(force_to_date(day))