def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month +
relativedelta(months=1))
return """
SELECT DISTINCT child_health_case_id AS case_id,
LAST_VALUE(supervisor_id) OVER w AS supervisor_id,
LAST_VALUE(timeend) OVER w AS latest_time_end,
MAX(counsel_increase_food_bf) OVER w AS counsel_increase_food_bf,
MAX(counsel_breast) OVER w AS counsel_breast,
MAX(skin_to_skin) OVER w AS skin_to_skin,
LAST_VALUE(is_ebf) OVER w AS is_ebf,
LAST_VALUE(water_or_milk) OVER w AS water_or_milk,
LAST_VALUE(other_milk_to_child) OVER w AS other_milk_to_child,
LAST_VALUE(tea_other) OVER w AS tea_other,
LAST_VALUE(eating) OVER w AS eating,
MAX(counsel_exclusive_bf) OVER w AS counsel_exclusive_bf,
MAX(counsel_only_milk) OVER w AS counsel_only_milk,
MAX(counsel_adequate_bf) OVER w AS counsel_adequate_bf,
LAST_VALUE(not_breastfeeding) OVER w AS not_breastfeeding
FROM "{ucr_tablename}"
WHERE timeend >= %(current_month_start)s AND
timeend < %(next_month_start)s AND
state_id = %(state_id)s AND
child_health_case_id IS NOT NULL
WINDOW w AS (
PARTITION BY child_health_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(ucr_tablename=self.ucr_tablename), {
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id
}
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
query_params = {
"month": month_formatter(month),
"state_id": self.state_id,
"current_month_start": current_month_start,
"next_month_start": next_month_start,
}
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed, days_ration_given_child
) (
SELECT DISTINCT ON (child_health_case_id)
%(state_id)s AS state_id,
LAST_VALUE(supervisor_id) over w AS supervisor_id,
%(month)s AS month,
child_health_case_id AS case_id,
MAX(timeend) over w AS latest_time_end_processed,
SUM(days_ration_given_child) over w AS days_ration_given_child
FROM "{ucr_tablename}"
WHERE state_id = %(state_id)s AND
timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND
child_health_case_id IS NOT NULL
WINDOW w AS (PARTITION BY child_health_case_id)
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=tablename
), query_params
def aggregation_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
query_params = {
"month": month_formatter(self.month),
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id,
}
return f"""
INSERT INTO "{self.aggregate_parent_table}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed,
sum_attended_child_ids, lunch_count
) (
SELECT DISTINCT ON (ucr.child_health_case_id)
ucr.state_id AS state_id,
ucr.supervisor_id,
%(month)s AS month,
ucr.child_health_case_id AS case_id,
MAX(ucr.timeend) OVER w AS latest_time_end_processed,
SUM(ucr.attended_child_ids) OVER w AS sum_attended_child_ids,
SUM(ucr.lunch) OVER w AS lunch_count
FROM "{self.ucr_tablename}" ucr INNER JOIN daily_attendance ON (
ucr.doc_id = daily_attendance.doc_id AND
ucr.supervisor_id = daily_attendance.supervisor_id AND
daily_attendance.month=%(current_month_start)s
)
WHERE ucr.timeend >= %(current_month_start)s AND ucr.timeend < %(next_month_start)s
AND ucr.child_health_case_id IS NOT NULL
AND ucr.state_id = %(state_id)s
WINDOW w AS (PARTITION BY ucr.supervisor_id, ucr.child_health_case_id)
)
""", query_params
def aggregate_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
next_month_start = self.month + relativedelta(months=1)
query_params = {
"state_id": self.state_id,
"start_date": month_formatter(month),
"end_date": month_formatter(next_month_start)
}
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, awc_id, thr_distribution_image_count, month
) (
SELECT
state_id,
supervisor_id,
awc_id,
COUNT(*) FILTER (WHERE NULLIF(photo_thr_packets_distributed,'') is not null) as thr_distribution_image_count,
%(start_date)s::DATE AS month
FROM "{ucr_tablename}"
WHERE submitted_on >= %(start_date)s AND submitted_on < %(end_date)s
AND state_id=%(state_id)s
GROUP BY state_id, supervisor_id, awc_id
)
""".format(ucr_tablename=self.ucr_tablename,
tablename=tablename), query_params
def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month +
relativedelta(months=1))
# We need two windows here because we want to filter the forms differently for two different
# columns.
# Window definitions inspired by https://stackoverflow.com/a/47223416.
# rank() is used because we want to find the dates of first 4 forms submitted for each case with
# last_visit_date and poshan_panchayat_date in the month separately.
return """
SELECT
supervisor_id,
child_health_case_id,
last_visit_date,
poshan_panchayat_date,
rank() OVER (
PARTITION BY supervisor_id, child_health_case_id
ORDER BY last_visit_date, timeend
) as rank
FROM "{ucr_tablename}"
WHERE
state_id = '{state_id}' AND
last_visit_date>=%(current_month_start)s AND
last_visit_date<%(next_month_start)s
""".format(ucr_tablename=self.ucr_tablename, state_id=self.state_id), {
"current_month_start": current_month_start,
"next_month_start": next_month_start
}
def data_from_ucr_query(self):
next_month_start = month_formatter(self.month +
relativedelta(months=1))
six_months_ago = month_formatter(self.month - relativedelta(months=6))
windows = [
self._window_helper(column_name)
for column_name in self.column_names
]
select_lines = ', '.join([window[0] for window in windows])
window_lines = ', '.join([window[1] for window in windows])
return """
SELECT
DISTINCT awc_id AS awc_id,
{select_lines}
FROM "{ucr_tablename}"
WHERE timeend >= %(six_months_ago)s AND timeend < %(next_month_start)s
AND state_id = %(state_id)s AND awc_id IS NOT NULL
WINDOW
{window_lines}
""".format(
ucr_tablename=self.ucr_tablename,
select_lines=select_lines,
window_lines=window_lines,
), {
"six_months_ago": six_months_ago,
"next_month_start": next_month_start,
"state_id": self.state_id,
}
def data_from_ucr_query(self):
month = self.month.replace(day=1)
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
query_params = {
"month": month_formatter(month),
"state_id": self.state_id,
"current_month_start": current_month_start,
"next_month_start": next_month_start,
}
return """
SELECT DISTINCT
%(state_id)s AS state_id,
supervisor_id,
awc_id,
%(month)s::DATE AS month,
person_case_id AS person_case_id,
LAST_VALUE(is_registered) OVER w AS is_registered,
LAST_VALUE(timeend) OVER w AS registration_date
FROM "{ucr_tablename}"
WHERE state_id = %(state_id)s AND
timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND
person_case_id IS NOT NULL
WINDOW w AS (
PARTITION BY supervisor_id, person_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=self.aggregate_parent_table,
), query_params
def aggregate_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
next_month_start = self.month + relativedelta(months=1)
query_params = {
"state_id": self.state_id,
"start_date": month_formatter(month),
"end_date": month_formatter(next_month_start)
}
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, vhnd_observed
) (
SELECT
state_id,
location_id as supervisor_id,
%(start_date)s::DATE AS month,
count(*) as vhnd_observed
FROM "{ucr_tablename}"
WHERE vhnd_date >= %(start_date)s AND vhnd_date < %(end_date)s
AND state_id=%(state_id)s
GROUP BY state_id,location_id
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=tablename
), query_params
def aggregate_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
next_month_start = self.month + relativedelta(months=1)
query_params = {
"state_id": self.state_id,
"start_date": month_formatter(month),
"end_date": month_formatter(next_month_start)
}
return """
CREATE TEMPORARY TABLE "{temp_table}" AS (
SELECT
state_id,
location_id as supervisor_id,
%(start_date)s::DATE AS month,
count(*) as beneficiary_vists
FROM "{ucr_tablename}"
WHERE state_id=%(state_id)s AND submitted_on >= %(start_date)s AND
submitted_on < %(end_date)s AND visit_type_entered IS NOT NULL AND
visit_type_entered <> ''
GROUP BY state_id,location_id
);
INSERT INTO "{tablename}" (
state_id, supervisor_id, month,beneficiary_vists
) (
SELECT * FROM "{temp_table}"
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=tablename,
temp_table="temp_{}".format(tablename)
), query_params
def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month +
relativedelta(months=1))
return """
SELECT DISTINCT ccs_record_case_id AS case_id,
LAST_VALUE(timeend) OVER w AS latest_time_end,
MAX(immediate_breastfeeding) OVER w AS immediate_breastfeeding,
LAST_VALUE(eating_extra) OVER w as eating_extra,
LAST_VALUE(resting) OVER w as resting,
LAST_VALUE(anc_weight) OVER w as anc_weight,
LAST_VALUE(anc_blood_pressure) OVER w as anc_blood_pressure,
LAST_VALUE(bp_sys) OVER w as bp_sys,
LAST_VALUE(bp_dia) OVER w as bp_dia,
LAST_VALUE(anc_hemoglobin) OVER w as anc_hemoglobin,
LAST_VALUE(bleeding) OVER w as bleeding,
LAST_VALUE(swelling) OVER w as swelling,
LAST_VALUE(blurred_vision) OVER w as blurred_vision,
LAST_VALUE(convulsions) OVER w as convulsions,
LAST_VALUE(rupture) OVER w as rupture,
LAST_VALUE(anemia) OVER w as anemia,
LAST_VALUE(anc_abnormalities) OVER w as anc_abnormalities,
SUM(CASE WHEN (unscheduled_visit=0 AND days_visit_late < 8) OR (timeend::DATE - next_visit) < 8 THEN 1 ELSE 0 END) OVER w as valid_visits
FROM "{ucr_tablename}"
WHERE timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND state_id = %(state_id)s
WINDOW w AS (
PARTITION BY ccs_record_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(ucr_tablename=self.ucr_tablename), {
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id
}
def data_from_ucr_query(self):
month = self.month.replace(day=1)
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
query_params = {
"month": month_formatter(month),
"state_id": self.state_id,
"current_month_start": current_month_start,
"next_month_start": next_month_start,
}
return """
SELECT DISTINCT
%(state_id)s AS state_id,
supervisor_id,
awc_id,
%(month)s::DATE AS month,
person_case_id,
LAST_VALUE(out_of_school) OVER w IS NOT NULL AND LAST_VALUE(out_of_school) OVER w ='yes' AS out_of_school,
LAST_VALUE(re_out_of_school) OVER w IS NOT NULL AND LAST_VALUE(re_out_of_school) OVER w ='yes' AS re_out_of_school,
LAST_VALUE(admitted_in_school) OVER w IS NOT NULL AND LAST_VALUE(admitted_in_school) OVER w='yes' AS admitted_in_school
from "{ucr_tablename}"
WHERE state_id = %(state_id)s AND timeend >= %(current_month_start)s AND
timeend < %(next_month_start)s
WINDOW w AS (
PARTITION BY supervisor_id, awc_id, person_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=self.aggregate_parent_table
), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params = {
"month": month_formatter(month),
"previous_month": month_formatter(month - relativedelta(months=1)),
"state_id": self.state_id
}
query_params.update(ucr_query_params)
return """
INSERT INTO "{tablename}" (
state_id, month, supervisor_id, case_id, latest_time_end_processed, valid_visits
) (
SELECT
%(state_id)s AS state_id,
%(month)s AS month,
COALESCE(ucr.supervisor_id, prev_month.supervisor_id) as supervisor_id,
COALESCE(ucr.case_id, prev_month.case_id) AS case_id,
GREATEST(ucr.latest_time_end, prev_month.latest_time_end_processed) AS latest_time_end_processed,
COALESCE(ucr.valid_visits, 0) as valid_visits
FROM ({ucr_table_query}) ucr
FULL OUTER JOIN "{tablename}" prev_month
ON ucr.case_id = prev_month.case_id AND ucr.supervisor_id = prev_month.supervisor_id
AND ucr.month::DATE=prev_month.month + INTERVAL '1 month'
WHERE coalesce(ucr.month, %(month)s) = %(month)s
AND coalesce(prev_month.month, %(previous_month)s) = %(previous_month)s
AND coalesce(prev_month.state_id, %(state_id)s) = %(state_id)s
)
""".format(ucr_table_query=ucr_query,
tablename=self.tablename), query_params
def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
return """
SELECT DISTINCT child_health_case_id AS case_id,
LAST_VALUE(supervisor_id) OVER w AS supervisor_id,
LAST_VALUE(timeend) OVER w AS latest_time_end,
MAX(counsel_increase_food_bf) OVER w AS counsel_increase_food_bf,
MAX(counsel_breast) OVER w AS counsel_breast,
MAX(skin_to_skin) OVER w AS skin_to_skin,
LAST_VALUE(is_ebf) OVER w AS is_ebf,
LAST_VALUE(water_or_milk) OVER w AS water_or_milk,
LAST_VALUE(other_milk_to_child) OVER w AS other_milk_to_child,
LAST_VALUE(tea_other) OVER w AS tea_other,
LAST_VALUE(eating) OVER w AS eating,
MAX(counsel_exclusive_bf) OVER w AS counsel_exclusive_bf,
MAX(counsel_only_milk) OVER w AS counsel_only_milk,
MAX(counsel_adequate_bf) OVER w AS counsel_adequate_bf,
LAST_VALUE(not_breastfeeding) OVER w AS not_breastfeeding
FROM "{ucr_tablename}"
WHERE timeend >= %(current_month_start)s AND
timeend < %(next_month_start)s AND
state_id = %(state_id)s AND
child_health_case_id IS NOT NULL
WINDOW w AS (
PARTITION BY child_health_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(ucr_tablename=self.ucr_tablename), {
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id
}
def aggregation_query(self):
month = self.month.replace(day=1)
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month +
relativedelta(months=1))
query_params = {
"month": month_formatter(month),
"state_id": self.state_id,
"current_month_start": current_month_start,
"next_month_start": next_month_start,
}
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed, days_ration_given_child
) (
SELECT DISTINCT ON (child_health_case_id)
%(state_id)s AS state_id,
LAST_VALUE(supervisor_id) over w AS supervisor_id,
%(month)s AS month,
child_health_case_id AS case_id,
MAX(timeend) over w AS latest_time_end_processed,
SUM(days_ration_given_child) over w AS days_ration_given_child
FROM "{ucr_tablename}"
WHERE state_id = %(state_id)s AND
timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND
child_health_case_id IS NOT NULL
WINDOW w AS (PARTITION BY supervisor_id, child_health_case_id)
)
""".format(ucr_tablename=self.ucr_tablename,
tablename=self.tablename), query_params
def aggregate_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
next_month_start = self.month + relativedelta(months=1)
query_params = {
"state_id": self.state_id,
"start_date": month_formatter(month),
"end_date": month_formatter(next_month_start)
}
return """
CREATE TEMPORARY TABLE "{temp_table}" AS (
SELECT
state_id,
location_id as supervisor_id,
%(start_date)s::DATE AS month,
count(*) as vhnd_observed
FROM "{ucr_tablename}"
WHERE vhnd_date >= %(start_date)s AND vhnd_date < %(end_date)s
AND state_id=%(state_id)s
GROUP BY state_id,location_id
);
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, vhnd_observed
) (
SELECT * FROM "{temp_table}"
)
""".format(ucr_tablename=self.ucr_tablename,
tablename=tablename,
temp_table="temp_{}".format(tablename)), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month +
relativedelta(months=1))
query_params = {
"month": month_formatter(month),
"state_id": self.state_id,
"current_month_start": current_month_start,
"next_month_start": next_month_start,
}
return """
INSERT INTO "{tablename}" (
state_id, month, case_id, latest_time_end_processed, days_ration_given_mother
) (
SELECT
%(state_id)s AS state_id,
%(month)s AS month,
ccs_record_case_id AS case_id,
MAX(timeend) AS latest_time_end_processed,
SUM(days_ration_given_mother) AS days_ration_given_mother
FROM "{ucr_tablename}"
WHERE state_id = %(state_id)s AND
timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND
ccs_record_case_id IS NOT NULL
GROUP BY ccs_record_case_id
)
""".format(ucr_tablename=self.ucr_tablename,
tablename=tablename), query_params
def data_from_ucr_query(self):
next_month_start = month_formatter(self.month + relativedelta(months=1))
six_months_ago = month_formatter(self.month - relativedelta(months=6))
windows = [
self._window_helper(column_name)
for column_name in self.column_names
]
select_lines = ', '.join([window[0] for window in windows])
window_lines = ', '.join([window[1] for window in windows])
return """
SELECT
DISTINCT awc_id AS awc_id,
{select_lines}
FROM "{ucr_tablename}"
WHERE timeend >= %(six_months_ago)s AND timeend < %(next_month_start)s
AND state_id = %(state_id)s AND awc_id IS NOT NULL
WINDOW
{window_lines}
""".format(
ucr_tablename=self.ucr_tablename,
select_lines=select_lines,
window_lines=window_lines,
), {
"six_months_ago": six_months_ago,
"next_month_start": next_month_start,
"state_id": self.state_id,
}
def aggregation_query(self):
month = self.month.replace(day=1)
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params = {
"month": month_formatter(month),
"state_id": self.state_id,
"previous_month": month_formatter(month - relativedelta(months=1)),
}
query_params.update(ucr_query_params)
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, person_case_id, is_migrated, migration_date
) (
SELECT
%(state_id)s AS state_id,
COALESCE(ucr.supervisor_id, prev_month.supervisor_id) AS supervisor_id,
%(month)s::DATE AS month,
COALESCE(ucr.person_case_id, prev_month.person_case_id) AS person_case_id,
COALESCE(ucr.is_migrated, prev_month.is_migrated) as is_migrated,
COALESCE(ucr.migration_date, prev_month.migration_date) as migration_date
FROM ({ucr_table_query}) ucr
FULL OUTER JOIN (
SELECT * FROM "{tablename}" WHERE month = %(previous_month)s AND state_id = %(state_id)s
) prev_month
ON ucr.person_case_id = prev_month.person_case_id AND ucr.supervisor_id = prev_month.supervisor_id
WHERE coalesce(ucr.month, %(month)s) = %(month)s
AND coalesce(prev_month.month, %(previous_month)s) = %(previous_month)s
AND coalesce(prev_month.state_id, %(state_id)s) = %(state_id)s
)
""".format(ucr_tablename=self.ucr_tablename,
tablename=self.aggregate_parent_table,
ucr_table_query=ucr_query), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
query_params = {
"month": month_formatter(month),
"state_id": self.state_id,
"current_month_start": current_month_start,
"next_month_start": next_month_start,
}
return """
INSERT INTO "{tablename}" (
state_id, month, case_id, latest_time_end_processed, days_ration_given_mother
) (
SELECT
%(state_id)s AS state_id,
%(month)s AS month,
ccs_record_case_id AS case_id,
MAX(timeend) AS latest_time_end_processed,
SUM(days_ration_given_mother) AS days_ration_given_mother
FROM "{ucr_tablename}"
WHERE state_id = %(state_id)s AND
timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND
ccs_record_case_id IS NOT NULL
GROUP BY ccs_record_case_id
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=tablename
), query_params
def aggregate_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
next_month_start = self.month + relativedelta(months=1)
query_params = {
"state_id": self.state_id,
"start_date": month_formatter(month),
"end_date": month_formatter(next_month_start)
}
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, beneficiary_vists
) (
SELECT
state_id,
location_id as supervisor_id,
%(start_date)s::DATE AS month,
count(*) as beneficiary_vists
FROM "{ucr_tablename}"
WHERE submitted_on >= %(start_date)s AND submitted_on < %(end_date)s
AND visit_type_entered is not null AND visit_type_entered <> ''
AND state_id=%(state_id)s
GROUP BY state_id,location_id
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=tablename
), query_params
def aggregation_query(self):
tablename = self.generate_child_tablename(self.month)
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
query_params = {
"month": month_formatter(self.month),
"state_id": self.state_id,
"current_month_start": current_month_start,
"next_month_start": next_month_start,
}
return """
INSERT INTO "{tablename}" (
state_id, month, case_id, latest_time_end_processed, sum_attended_child_ids,
lunch_count
) (
SELECT
%(state_id)s AS state_id,
%(month)s AS month,
child_health_case_id AS case_id,
MAX(timeend) AS latest_time_end_processed,
SUM(attended_child_ids) AS sum_attended_child_ids,
SUM(lunch) AS lunch_count
FROM "{ucr_tablename}"
WHERE state_id = %(state_id)s AND
timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND
child_health_case_id IS NOT NULL
GROUP BY child_health_case_id
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=tablename
), query_params
def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
return """
SELECT DISTINCT child_health_case_id AS case_id,
LAST_VALUE(timeend) OVER w AS latest_time_end,
MAX(play_comp_feeding_vid) OVER w AS play_comp_feeding_vid,
MAX(comp_feeding) OVER w AS comp_feeding_ever,
MAX(demo_comp_feeding) OVER w AS demo_comp_feeding,
MAX(counselled_pediatric_ifa) OVER w AS counselled_pediatric_ifa,
LAST_VALUE(comp_feeding) OVER w AS comp_feeding_latest,
LAST_VALUE(diet_diversity) OVER w AS diet_diversity,
LAST_VALUE(diet_quantity) OVER w AS diet_quantity,
LAST_VALUE(hand_wash) OVER w AS hand_wash
FROM "{ucr_tablename}"
WHERE timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND state_id = %(state_id)s
WINDOW w AS (
PARTITION BY child_health_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(ucr_tablename=self.ucr_tablename), {
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id
}
def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month +
relativedelta(months=1))
return """
SELECT DISTINCT ccs_record_case_id AS case_id,
%(current_month_start)s as month,
LAST_VALUE(timeend) OVER w AS latest_time_end,
SUM(
CASE WHEN (unscheduled_visit=0 AND days_visit_late < 8) OR (timeend::DATE - next_visit) < 8
THEN 1 ELSE 0 END
) OVER w as valid_visits,
supervisor_id as supervisor_id
FROM "{ucr_tablename}"
WHERE (
timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND
state_id = %(state_id)s AND ccs_record_case_id IS NOT NULL
)
WINDOW w AS (
PARTITION BY supervisor_id, ccs_record_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(ucr_tablename=self.ucr_tablename), {
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id
}
def aggregation_query(self):
tablename = self.generate_child_tablename(self.month)
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month +
relativedelta(months=1))
query_params = {
"month": month_formatter(self.month),
"state_id": self.state_id,
"current_month_start": current_month_start,
"next_month_start": next_month_start,
}
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed,
sum_attended_child_ids, lunch_count
) (
SELECT DISTINCT ON (child_health_case_id)
%(state_id)s AS state_id,
LAST_VALUE(supervisor_id) OVER w AS supervisor_id,
%(month)s AS month,
child_health_case_id AS case_id,
MAX(timeend) OVER w AS latest_time_end_processed,
SUM(attended_child_ids) OVER w AS sum_attended_child_ids,
SUM(lunch) OVER w AS lunch_count
FROM "{ucr_tablename}"
WHERE state_id = %(state_id)s AND
timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND
child_health_case_id IS NOT NULL
WINDOW w AS (PARTITION BY child_health_case_id)
)
""".format(ucr_tablename=self.ucr_tablename,
tablename=tablename), query_params
def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month +
relativedelta(months=1))
return """
SELECT DISTINCT child_health_case_id AS case_id,
LAST_VALUE(supervisor_id) OVER w AS supervisor_id,
LAST_VALUE(timeend) OVER w AS latest_time_end,
MAX(play_comp_feeding_vid) OVER w AS play_comp_feeding_vid,
MAX(comp_feeding) OVER w AS comp_feeding_ever,
MAX(demo_comp_feeding) OVER w AS demo_comp_feeding,
MAX(counselled_pediatric_ifa) OVER w AS counselled_pediatric_ifa,
LAST_VALUE(comp_feeding) OVER w AS comp_feeding_latest,
LAST_VALUE(diet_diversity) OVER w AS diet_diversity,
LAST_VALUE(diet_quantity) OVER w AS diet_quantity,
LAST_VALUE(hand_wash) OVER w AS hand_wash
FROM "{ucr_tablename}"
WHERE timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND state_id = %(state_id)s
WINDOW w AS (
PARTITION BY child_health_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(ucr_tablename=self.ucr_tablename), {
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id
}
def aggregate_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
next_month_start = self.month + relativedelta(months=1)
query_params = {
"state_id": self.state_id,
"start_date": month_formatter(month),
"end_date": month_formatter(next_month_start)
}
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, awc_visits
) (
SELECT
state_id,
location_id as supervisor_id,
%(start_date)s::DATE AS month,
count(*) as awc_visits
FROM "{ucr_tablename}"
WHERE submitted_on >= %(start_date)s AND submitted_on < %(end_date)s
AND location_entered is not null and location_entered <> ''
AND state_id=%(state_id)s
GROUP BY state_id,location_id
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=tablename
), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
next_month_start = self.month + relativedelta(months=1)
query_params = {
"state_id": self.state_id,
"start_date": month_formatter(month),
"end_date": month_formatter(next_month_start)
}
return """
DROP TABLE IF EXISTS "temp_thr";
CREATE TEMPORARY TABLE "temp_thr" AS
SELECT
state_id,
supervisor_id,
awc_id,
COUNT(*) FILTER (WHERE photo_thr_packets_distributed is not null) as thr_distribution_image_count,
%(start_date)s::DATE AS month
FROM "{ucr_tablename}"
WHERE submitted_on >= %(start_date)s AND submitted_on < %(end_date)s
AND state_id=%(state_id)s
GROUP BY state_id, supervisor_id, awc_id;
INSERT INTO "{tablename}" (
state_id, supervisor_id, awc_id, thr_distribution_image_count, month
)
SELECT * from "temp_thr";
""".format(ucr_tablename=self.ucr_tablename,
tablename=self.tablename), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
query_params = {
"month": month_formatter(month),
"previous_month": month_formatter(month - relativedelta(months=1)),
"state_id": self.state_id
}
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params.update(ucr_query_params)
# GREATEST calculations are for when we want to know if a thing has
# ever happened to a case.
# CASE WHEN calculations are for when we want to know if a case
# happened during the last form for this case. We must use CASE WHEN
# and not COALESCE as when questions are skipped they will be NULL
# and we want NULL in the aggregate table
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed, comp_feeding_ever,
demo_comp_feeding, counselled_pediatric_ifa, play_comp_feeding_vid,
comp_feeding_latest, diet_diversity, diet_quantity, hand_wash
) (
SELECT
%(state_id)s AS state_id,
COALESCE(ucr.supervisor_id, prev_month.supervisor_id) AS supervisor_id,
%(month)s AS month,
COALESCE(ucr.case_id, prev_month.case_id) AS case_id,
GREATEST(ucr.latest_time_end, prev_month.latest_time_end_processed) AS latest_time_end_processed,
GREATEST(ucr.comp_feeding_ever, prev_month.comp_feeding_ever) AS comp_feeding_ever,
GREATEST(ucr.demo_comp_feeding, prev_month.demo_comp_feeding) AS demo_comp_feeding,
GREATEST(ucr.counselled_pediatric_ifa, prev_month.counselled_pediatric_ifa) AS counselled_pediatric_ifa,
GREATEST(ucr.play_comp_feeding_vid, prev_month.play_comp_feeding_vid) AS play_comp_feeding_vid,
CASE WHEN ucr.latest_time_end IS NOT NULL
THEN ucr.comp_feeding_latest ELSE prev_month.comp_feeding_latest
END AS comp_feeding_latest,
CASE WHEN ucr.latest_time_end IS NOT NULL
THEN ucr.diet_diversity ELSE prev_month.diet_diversity
END AS diet_diversity,
CASE WHEN ucr.latest_time_end IS NOT NULL
THEN ucr.diet_quantity ELSE prev_month.diet_quantity
END AS diet_quantity,
CASE WHEN ucr.latest_time_end IS NOT NULL
THEN ucr.hand_wash ELSE prev_month.hand_wash
END AS hand_wash
FROM ({ucr_table_query}) ucr
FULL OUTER JOIN "{tablename}" prev_month
ON ucr.case_id = prev_month.case_id AND ucr.supervisor_id = prev_month.supervisor_id
AND ucr.month = prev_month.month + INTERVAL '1 month'
WHERE coalesce(ucr.month, %(month)s) = %(month)s
AND coalesce(prev_month.month, %(previous_month)s) = %(previous_month)s
AND coalesce(prev_month.state_id, %(state_id)s) = %(state_id)s
)
""".format(
tablename=self.tablename,
ucr_table_query=ucr_query
), query_params
def aggregation_query(self):
month_start_string = month_formatter(self.month)
month_end_string = month_formatter(self.month +
relativedelta(months=1, seconds=-1))
person_case_ucr = get_table_name(self.domain, 'static-person_cases_v3')
household_ucr = get_table_name(self.domain, 'static-household_cases')
columns = (
('state_id', 'person_list.state_id'),
('district_id', 'person_list.district_id'),
('block_id', 'person_list.block_id'),
('supervisor_id', 'person_list.supervisor_id'),
('awc_id', 'person_list.awc_id'),
('month', f"'{month_start_string}'"),
('person_id', 'person_list.doc_id'),
('person_name', 'person_list.name'),
('has_adhaar',
'CASE WHEN person_list.aadhar_date is not null THEN 1 ELSE 0 END'
),
('bank_account_number', 'person_list.bank_account_number'),
('ifsc_code', 'person_list.ifsc_code'),
('age_at_reg', 'person_list.age_at_reg'),
('dob', 'person_list.dob'),
('gender', 'person_list.sex'),
('blood_group', 'person_list.blood_group'),
('disabled', 'person_list.disabled'),
('disability_type', 'person_list.disability_type'),
('referral_status', 'person_list.referral_status'),
('migration_status', 'migration_tab.is_migrated'),
('resident', 'person_list.resident'),
('registered_status', 'person_list.registered_status'),
('rch_id', 'person_list.rch_id'),
('mcts_id', 'person_list.mcts_id'),
('phone_number', 'person_list.phone_number'),
('date_death', 'person_list.date_death'),
('site_death', 'person_list.site_death'),
('closed_on', 'person_list.closed_on'),
('reason_closure', 'person_list.reason_closure'),
('household_id', 'hh_list.doc_id'),
('household_name', 'hh_list.name'),
('hh_reg_date', 'hh_list.hh_reg_date'),
('hh_num', 'hh_list.hh_num'),
('hh_gps_location', 'hh_list.hh_gps_location'),
('hh_caste', 'hh_list.hh_caste'),
('hh_bpl_apl', 'hh_list.hh_bpl_apl'),
('hh_minority', 'hh_list.hh_minority'),
('hh_religion', 'hh_list.hh_religion'),
)
column_names = ", ".join([col[0] for col in columns])
calculations = ", ".join([col[1] for col in columns])
return f"""
def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month +
relativedelta(months=1))
return """
SELECT DISTINCT ccs_record_case_id AS case_id,
supervisor_id,
%(current_month_start)s::date as month,
LAST_VALUE(timeend) OVER w AS latest_time_end,
MAX(immediate_breastfeeding) OVER w AS immediate_breastfeeding,
MAX(play_birth_preparedness_vid) OVER w as play_birth_preparedness_vid,
MAX(counsel_preparation) OVER w as counsel_preparation,
MAX(play_family_planning_vid) OVER w as play_family_planning_vid,
MAX(conceive) OVER w as conceive,
MAX(counsel_accessible_ppfp) OVER w as counsel_accessible_ppfp,
LAST_VALUE(eating_extra) OVER w as eating_extra,
LAST_VALUE(resting) OVER w as resting,
LAST_VALUE(anc_weight) OVER w as anc_weight,
LAST_VALUE(anc_blood_pressure) OVER w as anc_blood_pressure,
LAST_VALUE(bp_sys) OVER w as bp_sys,
LAST_VALUE(bp_dia) OVER w as bp_dia,
LAST_VALUE(anc_hemoglobin) OVER w as anc_hemoglobin,
LAST_VALUE(bleeding) OVER w as bleeding,
LAST_VALUE(swelling) OVER w as swelling,
LAST_VALUE(blurred_vision) OVER w as blurred_vision,
LAST_VALUE(convulsions) OVER w as convulsions,
LAST_VALUE(rupture) OVER w as rupture,
LAST_VALUE(anemia) OVER w as anemia,
LAST_VALUE(anc_abnormalities) OVER w as anc_abnormalities,
LAST_VALUE(using_ifa) OVER w as using_ifa,
GREATEST(LAST_VALUE(ifa_last_seven_days) OVER w, 0) as ifa_last_seven_days,
LAST_VALUE(reason_no_ifa) OVER w as reason_no_ifa,
LAST_VALUE(new_ifa_tablets_total) OVER w as new_ifa_tablets_total,
SUM(CASE WHEN
(unscheduled_visit=0 AND days_visit_late < 8) OR (timeend::DATE - next_visit) < 8
THEN 1 ELSE 0 END
) OVER w as valid_visits
FROM "{ucr_tablename}"
WHERE timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND state_id = %(state_id)s
WINDOW w AS (
PARTITION BY supervisor_id, ccs_record_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(ucr_tablename=self.ucr_tablename), {
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id
}
def delete_previous_run_query(self):
return (
f'DELETE FROM "{self.aggregate_parent_table}" WHERE month=%(month)s AND state_id = %(state)s',
{
'month': month_formatter(self.month),
'state': self.state_id
})
def drop_table_query(self):
return (
'DELETE FROM "{tablename}" WHERE state_id = %(state_id)s and month=%(month)s'
.format(tablename=self.aggregate_parent_table), {
'state_id': self.state_id,
'month': month_formatter(self.month.replace(day=1))
})
def drop_table_query(self):
return (
'DELETE FROM "{}" WHERE month=%(month)s AND state_id = %(state)s'.
format(self.tablename), {
'month': month_formatter(self.month),
'state': self.state_id
})
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
previous_month_tablename = self.generate_child_tablename(month - relativedelta(months=1))
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params = {
"month": month_formatter(month),
"state_id": self.state_id
}
query_params.update(ucr_query_params)
# The '1970-01-01' is a fallback, this should never happen,
# but an unexpected NULL should not block other data
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed,
weight_child, weight_child_last_recorded,
height_child, height_child_last_recorded,
zscore_grading_wfa, zscore_grading_wfa_last_recorded,
zscore_grading_hfa, zscore_grading_hfa_last_recorded,
zscore_grading_wfh, zscore_grading_wfh_last_recorded,
muac_grading, muac_grading_last_recorded
) (
SELECT
%(state_id)s AS state_id,
COALESCE(ucr.supervisor_id, prev_month.supervisor_id) AS supervisor_id,
%(month)s AS month,
COALESCE(ucr.case_id, prev_month.case_id) AS case_id,
GREATEST(
ucr.weight_child_last_recorded,
ucr.height_child_last_recorded,
ucr.zscore_grading_wfa_last_recorded,
ucr.zscore_grading_hfa_last_recorded,
ucr.zscore_grading_wfh_last_recorded,
ucr.muac_grading_last_recorded,
prev_month.latest_time_end_processed,
'1970-01-01'
) AS latest_time_end_processed,
COALESCE(ucr.weight_child, prev_month.weight_child) AS weight_child,
GREATEST(ucr.weight_child_last_recorded, prev_month.weight_child_last_recorded) AS weight_child_last_recorded,
COALESCE(ucr.height_child, prev_month.height_child) AS height_child,
GREATEST(ucr.height_child_last_recorded, prev_month.height_child_last_recorded) AS height_child_last_recorded,
COALESCE(ucr.zscore_grading_wfa, prev_month.zscore_grading_wfa) AS zscore_grading_wfa,
GREATEST(ucr.zscore_grading_wfa_last_recorded, prev_month.zscore_grading_wfa_last_recorded) AS zscore_grading_wfa_last_recorded,
COALESCE(ucr.zscore_grading_hfa, prev_month.zscore_grading_hfa) AS zscore_grading_hfa,
GREATEST(ucr.zscore_grading_hfa_last_recorded, prev_month.zscore_grading_hfa_last_recorded) AS zscore_grading_hfa_last_recorded,
COALESCE(ucr.zscore_grading_wfh, prev_month.zscore_grading_wfh) AS zscore_grading_wfh,
GREATEST(ucr.zscore_grading_wfh_last_recorded, prev_month.zscore_grading_wfh_last_recorded) AS zscore_grading_wfh_last_recorded,
COALESCE(ucr.muac_grading, prev_month.muac_grading) AS muac_grading,
GREATEST(ucr.muac_grading_last_recorded, prev_month.muac_grading_last_recorded) AS muac_grading_last_recorded
FROM ({ucr_table_query}) ucr
FULL OUTER JOIN "{previous_month_tablename}" prev_month
ON ucr.case_id = prev_month.case_id
)
""".format(
ucr_table_query=ucr_query,
previous_month_tablename=previous_month_tablename,
tablename=tablename
), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
previous_month_tablename = self.generate_child_tablename(month - relativedelta(months=1))
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params = {
"month": month_formatter(month),
"state_id": self.state_id
}
query_params.update(ucr_query_params)
return """
INSERT INTO "{tablename}" (
state_id, month, case_id, latest_time_end_processed, valid_visits
) (
SELECT
%(state_id)s AS state_id,
%(month)s AS month,
COALESCE(ucr.case_id, prev_month.case_id) AS case_id,
GREATEST(ucr.latest_time_end, prev_month.latest_time_end_processed) AS latest_time_end_processed,
COALESCE(ucr.valid_visits, 0) as valid_visits
FROM ({ucr_table_query}) ucr
FULL OUTER JOIN "{previous_month_tablename}" prev_month
ON ucr.case_id = prev_month.case_id
)
""".format(
ucr_table_query=ucr_query,
previous_month_tablename=previous_month_tablename,
tablename=tablename
), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
previous_month_tablename = self.generate_child_tablename(month - relativedelta(months=1))
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params = {
"month": month_formatter(month),
"state_id": self.state_id
}
query_params.update(ucr_query_params)
# The '1970-01-01' is a fallback, this should never happen,
# but an unexpected NULL should not block other data
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed,
weight_child, weight_child_last_recorded,
height_child, height_child_last_recorded,
zscore_grading_wfa, zscore_grading_wfa_last_recorded,
zscore_grading_hfa, zscore_grading_hfa_last_recorded,
zscore_grading_wfh, zscore_grading_wfh_last_recorded,
muac_grading, muac_grading_last_recorded
) (
SELECT
%(state_id)s AS state_id,
COALESCE(ucr.supervisor_id, prev_month.supervisor_id) AS supervisor_id,
%(month)s AS month,
COALESCE(ucr.case_id, prev_month.case_id) AS case_id,
GREATEST(
ucr.weight_child_last_recorded,
ucr.height_child_last_recorded,
ucr.zscore_grading_wfa_last_recorded,
ucr.zscore_grading_hfa_last_recorded,
ucr.zscore_grading_wfh_last_recorded,
ucr.muac_grading_last_recorded,
prev_month.latest_time_end_processed,
'1970-01-01'
) AS latest_time_end_processed,
COALESCE(ucr.weight_child, prev_month.weight_child) AS weight_child,
GREATEST(ucr.weight_child_last_recorded, prev_month.weight_child_last_recorded) AS weight_child_last_recorded,
COALESCE(ucr.height_child, prev_month.height_child) AS height_child,
GREATEST(ucr.height_child_last_recorded, prev_month.height_child_last_recorded) AS height_child_last_recorded,
COALESCE(ucr.zscore_grading_wfa, prev_month.zscore_grading_wfa) AS zscore_grading_wfa,
GREATEST(ucr.zscore_grading_wfa_last_recorded, prev_month.zscore_grading_wfa_last_recorded) AS zscore_grading_wfa_last_recorded,
COALESCE(ucr.zscore_grading_hfa, prev_month.zscore_grading_hfa) AS zscore_grading_hfa,
GREATEST(ucr.zscore_grading_hfa_last_recorded, prev_month.zscore_grading_hfa_last_recorded) AS zscore_grading_hfa_last_recorded,
COALESCE(ucr.zscore_grading_wfh, prev_month.zscore_grading_wfh) AS zscore_grading_wfh,
GREATEST(ucr.zscore_grading_wfh_last_recorded, prev_month.zscore_grading_wfh_last_recorded) AS zscore_grading_wfh_last_recorded,
COALESCE(ucr.muac_grading, prev_month.muac_grading) AS muac_grading,
GREATEST(ucr.muac_grading_last_recorded, prev_month.muac_grading_last_recorded) AS muac_grading_last_recorded
FROM ({ucr_table_query}) ucr
FULL OUTER JOIN "{previous_month_tablename}" prev_month
ON ucr.case_id = prev_month.case_id
)
""".format(
ucr_table_query=ucr_query,
previous_month_tablename=previous_month_tablename,
tablename=tablename
), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
previous_month_tablename = self.generate_child_tablename(month -
relativedelta(
months=1))
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params = {
"month": month_formatter(month),
"state_id": self.state_id
}
query_params.update(ucr_query_params)
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed,
immediate_breastfeeding, anemia, eating_extra, resting,
anc_weight, anc_blood_pressure, bp_sys, bp_dia, anc_hemoglobin,
bleeding, swelling, blurred_vision, convulsions, rupture, anc_abnormalities, valid_visits,
play_birth_preparedness_vid, counsel_preparation, play_family_planning_vid, conceive,
counsel_accessible_ppfp, ifa_last_seven_days,using_ifa
) (
SELECT
%(state_id)s AS state_id,
COALESCE(ucr.supervisor_id, prev_month.supervisor_id) AS supervisor_id,
%(month)s AS month,
COALESCE(ucr.case_id, prev_month.case_id) AS case_id,
COALESCE(ucr.latest_time_end, prev_month.latest_time_end_processed) AS latest_time_end_processed,
GREATEST(ucr.immediate_breastfeeding, prev_month.immediate_breastfeeding) AS immediate_breastfeeding,
COALESCE(ucr.anemia,prev_month.anemia) AS anemia,
COALESCE(ucr.eating_extra,prev_month.eating_extra) AS eating_extra,
COALESCE(ucr.resting,prev_month.resting) AS resting,
COALESCE(ucr.anc_weight,prev_month.anc_weight) anc_weight,
COALESCE(ucr.anc_blood_pressure,prev_month.anc_blood_pressure) as anc_blood_pressure,
COALESCE(ucr.bp_sys,prev_month.bp_sys) as bp_sys,
COALESCE(ucr.bp_dia,prev_month.bp_dia) as bp_dia,
COALESCE(ucr.anc_hemoglobin,prev_month.anc_hemoglobin) as anc_hemoglobin,
COALESCE(ucr.bleeding,prev_month.bleeding) as bleeding,
COALESCE(ucr.swelling,prev_month.swelling) as swelling,
COALESCE(ucr.blurred_vision,prev_month.blurred_vision) as blurred_vision,
COALESCE(ucr.convulsions,prev_month.convulsions) as convulsions,
COALESCE(ucr.rupture,prev_month.rupture) as rupture,
COALESCE(ucr.anc_abnormalities,prev_month.anc_abnormalities) as anc_abnormalities,
COALESCE(ucr.valid_visits, 0) as valid_visits,
GREATEST(ucr.play_birth_preparedness_vid, prev_month.play_birth_preparedness_vid) as play_birth_preparedness_vid,
GREATEST(ucr.counsel_preparation, prev_month.counsel_preparation) as counsel_preparation,
GREATEST(ucr.play_family_planning_vid, prev_month.play_family_planning_vid) as play_family_planning_vid,
GREATEST(ucr.conceive,prev_month.conceive) as conceive,
GREATEST(ucr.counsel_accessible_ppfp, prev_month.counsel_accessible_ppfp) as counsel_accessible_ppfp,
COALESCE(ucr.ifa_last_seven_days, prev_month.ifa_last_seven_days) as ifa_last_seven_days,
COALESCE(ucr.using_ifa, prev_month.using_ifa) as using_ifa
FROM ({ucr_table_query}) ucr
FULL JOIN "{previous_month_tablename}" prev_month
ON ucr.case_id = prev_month.case_id
)
""".format(ucr_table_query=ucr_query,
previous_month_tablename=previous_month_tablename,
tablename=tablename), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
previous_month_tablename = self.generate_child_tablename(month - relativedelta(months=1))
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params = {
"month": month_formatter(month),
"state_id": self.state_id
}
query_params.update(ucr_query_params)
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed,
immediate_breastfeeding, anemia, eating_extra, resting,
anc_weight, anc_blood_pressure, bp_sys, bp_dia, anc_hemoglobin,
bleeding, swelling, blurred_vision, convulsions, rupture, anc_abnormalities, valid_visits,
play_birth_preparedness_vid, counsel_preparation, play_family_planning_vid, conceive,
counsel_accessible_ppfp, ifa_last_seven_days,using_ifa
) (
SELECT
%(state_id)s AS state_id,
COALESCE(ucr.supervisor_id, prev_month.supervisor_id) AS supervisor_id,
%(month)s AS month,
COALESCE(ucr.case_id, prev_month.case_id) AS case_id,
COALESCE(ucr.latest_time_end, prev_month.latest_time_end_processed) AS latest_time_end_processed,
GREATEST(ucr.immediate_breastfeeding, prev_month.immediate_breastfeeding) AS immediate_breastfeeding,
COALESCE(ucr.anemia,prev_month.anemia) AS anemia,
COALESCE(ucr.eating_extra,prev_month.eating_extra) AS eating_extra,
COALESCE(ucr.resting,prev_month.resting) AS resting,
COALESCE(ucr.anc_weight,prev_month.anc_weight) anc_weight,
COALESCE(ucr.anc_blood_pressure,prev_month.anc_blood_pressure) as anc_blood_pressure,
COALESCE(ucr.bp_sys,prev_month.bp_sys) as bp_sys,
COALESCE(ucr.bp_dia,prev_month.bp_dia) as bp_dia,
COALESCE(ucr.anc_hemoglobin,prev_month.anc_hemoglobin) as anc_hemoglobin,
COALESCE(ucr.bleeding,prev_month.bleeding) as bleeding,
COALESCE(ucr.swelling,prev_month.swelling) as swelling,
COALESCE(ucr.blurred_vision,prev_month.blurred_vision) as blurred_vision,
COALESCE(ucr.convulsions,prev_month.convulsions) as convulsions,
COALESCE(ucr.rupture,prev_month.rupture) as rupture,
COALESCE(ucr.anc_abnormalities,prev_month.anc_abnormalities) as anc_abnormalities,
COALESCE(ucr.valid_visits, 0) as valid_visits,
GREATEST(ucr.play_birth_preparedness_vid, prev_month.play_birth_preparedness_vid) as play_birth_preparedness_vid,
GREATEST(ucr.counsel_preparation, prev_month.counsel_preparation) as counsel_preparation,
GREATEST(ucr.play_family_planning_vid, prev_month.play_family_planning_vid) as play_family_planning_vid,
GREATEST(ucr.conceive,prev_month.conceive) as conceive,
GREATEST(ucr.counsel_accessible_ppfp, prev_month.counsel_accessible_ppfp) as counsel_accessible_ppfp,
COALESCE(ucr.ifa_last_seven_days, prev_month.ifa_last_seven_days) as ifa_last_seven_days,
COALESCE(ucr.using_ifa, prev_month.using_ifa) as using_ifa
FROM ({ucr_table_query}) ucr
FULL JOIN "{previous_month_tablename}" prev_month
ON ucr.case_id = prev_month.case_id
)
""".format(
ucr_table_query=ucr_query,
previous_month_tablename=previous_month_tablename,
tablename=tablename
), query_params
def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month +
relativedelta(months=1))
return """
SELECT
distinct case_id,
%(current_month_start)s as month,
supervisor_id,
LAST_VALUE(latest_time_end) OVER w AS latest_time_end,
MAX(counsel_methods) OVER w AS counsel_methods,
LAST_VALUE(is_ebf) OVER w as is_ebf,
LAST_VALUE(new_ifa_tablets_total) OVER w as new_ifa_tablets_total,
SUM(CASE WHEN (unscheduled_visit=0 AND days_visit_late < 8) OR
(latest_time_end::DATE - next_visit) < 8 THEN 1 ELSE 0 END) OVER w as valid_visits
from
(
SELECT
DISTINCT ccs_record_case_id AS case_id,
LAST_VALUE(timeend) OVER w AS latest_time_end,
MAX(counsel_methods) OVER w AS counsel_methods,
LAST_VALUE(is_ebf) OVER w as is_ebf,
LAST_VALUE(unscheduled_visit) OVER w as unscheduled_visit,
LAST_VALUE(days_visit_late) OVER w as days_visit_late,
LAST_VALUE(next_visit) OVER w as next_visit,
LAST_VALUE(new_ifa_tablets_total) OVER w as new_ifa_tablets_total,
supervisor_id
FROM "{ucr_tablename}"
WHERE timeend >= %(current_month_start)s
AND timeend < %(next_month_start)s
AND state_id = %(state_id)s
WINDOW w AS (
PARTITION BY doc_id, supervisor_id, ccs_record_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
) ut
WINDOW w AS (
PARTITION BY supervisor_id, case_id
ORDER BY latest_time_end RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(ucr_tablename=self.ucr_tablename), {
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id
}
def aggregation_query(self):
month = self.month.replace(day=1)
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params = {
"month": month_formatter(month),
"state_id": self.state_id,
"previous_month": month_formatter(month - relativedelta(months=1)),
}
query_params.update(ucr_query_params)
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, awc_id, month, person_case_id, out_of_school,
re_out_of_school, admitted_in_school
) (
SELECT
%(state_id)s AS state_id,
COALESCE(ucr.supervisor_id, prev_month.supervisor_id) AS supervisor_id,
COALESCE(ucr.awc_id, prev_month.awc_id) AS awc_id,
%(month)s::DATE AS month,
COALESCE(ucr.person_case_id, prev_month.person_case_id) AS person_case_id,
CASE WHEN ucr.person_case_id IS NOT NULL
THEN ucr.out_of_school IS NOT NULL AND ucr.out_of_school='yes'
ELSE prev_month.out_of_school IS NOT NULL AND prev_month.out_of_school END AS out_of_school,
CASE WHEN ucr.person_case_id IS NOT NULL
THEN ucr.re_out_of_school IS NOT NULL AND ucr.re_out_of_school='yes'
ELSE prev_month.re_out_of_school IS NOT NULL AND prev_month.re_out_of_school END AS re_out_of_school,
CASE WHEN ucr.person_case_id IS NOT NULL
THEN ucr.admitted_in_school IS NOT NULL AND ucr.admitted_in_school='yes'
ELSE prev_month.admitted_in_school IS NOT NULL AND prev_month.admitted_in_school END AS admitted_in_school
from ({ucr_table_query}) ucr
FULL OUTER JOIN (
SELECT * FROM "{tablename}" WHERE month = %(previous_month)s AND state_id = %(state_id)s
) prev_month
ON ucr.person_case_id = prev_month.person_case_id AND ucr.supervisor_id = prev_month.supervisor_id
WHERE coalesce(ucr.month, %(month)s) = %(month)s
AND coalesce(prev_month.month, %(previous_month)s) = %(previous_month)s
AND coalesce(prev_month.state_id, %(state_id)s) = %(state_id)s
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=self.aggregate_parent_table,
ucr_table_query=ucr_query
), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
previous_month_tablename = self.generate_child_tablename(month - relativedelta(months=1))
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params = {
"month": month_formatter(month),
"state_id": self.state_id
}
query_params.update(ucr_query_params)
# GREATEST calculations are for when we want to know if a thing has
# ever happened to a case.
# CASE WHEN calculations are for when we want to know if a case
# happened during the last form for this case. We must use CASE WHEN
# and not COALESCE as when questions are skipped they will be NULL
# and we want NULL in the aggregate table
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed, comp_feeding_ever,
demo_comp_feeding, counselled_pediatric_ifa, play_comp_feeding_vid,
comp_feeding_latest, diet_diversity, diet_quantity, hand_wash
) (
SELECT
%(state_id)s AS state_id,
COALESCE(ucr.supervisor_id, prev_month.supervisor_id) AS supervisor_id,
%(month)s AS month,
COALESCE(ucr.case_id, prev_month.case_id) AS case_id,
GREATEST(ucr.latest_time_end, prev_month.latest_time_end_processed) AS latest_time_end_processed,
GREATEST(ucr.comp_feeding_ever, prev_month.comp_feeding_ever) AS comp_feeding_ever,
GREATEST(ucr.demo_comp_feeding, prev_month.demo_comp_feeding) AS demo_comp_feeding,
GREATEST(ucr.counselled_pediatric_ifa, prev_month.counselled_pediatric_ifa) AS counselled_pediatric_ifa,
GREATEST(ucr.play_comp_feeding_vid, prev_month.play_comp_feeding_vid) AS play_comp_feeding_vid,
CASE WHEN ucr.latest_time_end IS NOT NULL
THEN ucr.comp_feeding_latest ELSE prev_month.comp_feeding_latest
END AS comp_feeding_latest,
CASE WHEN ucr.latest_time_end IS NOT NULL
THEN ucr.diet_diversity ELSE prev_month.diet_diversity
END AS diet_diversity,
CASE WHEN ucr.latest_time_end IS NOT NULL
THEN ucr.diet_quantity ELSE prev_month.diet_quantity
END AS diet_quantity,
CASE WHEN ucr.latest_time_end IS NOT NULL
THEN ucr.hand_wash ELSE prev_month.hand_wash
END AS hand_wash
FROM ({ucr_table_query}) ucr
FULL OUTER JOIN "{previous_month_tablename}" prev_month
ON ucr.case_id = prev_month.case_id
)
""".format(
ucr_table_query=ucr_query,
previous_month_tablename=previous_month_tablename,
tablename=tablename
), query_params
def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
return """
SELECT DISTINCT ccs_record_case_id AS case_id,
LAST_VALUE(supervisor_id) OVER w AS supervisor_id,
LAST_VALUE(timeend) OVER w AS latest_time_end,
MAX(immediate_breastfeeding) OVER w AS immediate_breastfeeding,
MAX(play_birth_preparedness_vid) OVER w as play_birth_preparedness_vid,
MAX(counsel_preparation) OVER w as counsel_preparation,
MAX(play_family_planning_vid) OVER w as play_family_planning_vid,
MAX(conceive) OVER w as conceive,
MAX(counsel_accessible_ppfp) OVER w as counsel_accessible_ppfp,
LAST_VALUE(eating_extra) OVER w as eating_extra,
LAST_VALUE(resting) OVER w as resting,
LAST_VALUE(anc_weight) OVER w as anc_weight,
LAST_VALUE(anc_blood_pressure) OVER w as anc_blood_pressure,
LAST_VALUE(bp_sys) OVER w as bp_sys,
LAST_VALUE(bp_dia) OVER w as bp_dia,
LAST_VALUE(anc_hemoglobin) OVER w as anc_hemoglobin,
LAST_VALUE(bleeding) OVER w as bleeding,
LAST_VALUE(swelling) OVER w as swelling,
LAST_VALUE(blurred_vision) OVER w as blurred_vision,
LAST_VALUE(convulsions) OVER w as convulsions,
LAST_VALUE(rupture) OVER w as rupture,
LAST_VALUE(anemia) OVER w as anemia,
LAST_VALUE(anc_abnormalities) OVER w as anc_abnormalities,
LAST_VALUE(using_ifa) OVER w as using_ifa,
GREATEST(LAST_VALUE(ifa_last_seven_days) OVER w, 0) as ifa_last_seven_days,
SUM(CASE WHEN (unscheduled_visit=0 AND days_visit_late < 8) OR (timeend::DATE - next_visit) < 8 THEN 1 ELSE 0 END) OVER w as valid_visits
FROM "{ucr_tablename}"
WHERE timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND state_id = %(state_id)s
WINDOW w AS (
PARTITION BY ccs_record_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(ucr_tablename=self.ucr_tablename), {
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id
}
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
query_params = {
"month": month_formatter(month),
"state_id": self.state_id,
"current_month_start": current_month_start,
"next_month_start": next_month_start,
}
return """
INSERT INTO "{tablename}" (
case_id, state_id, supervisor_id, month, latest_time_end_processed,
breastfed_at_birth, valid_visits, where_born
) (
SELECT
DISTINCT case_load_ccs_record0 AS case_id,
%(state_id)s AS state_id,
LAST_VALUE(supervisor_id) over w as supervisor_id,
%(month)s::DATE AS month,
LAST_VALUE(timeend) over w AS latest_time_end_processed,
LAST_VALUE(breastfed_at_birth) over w as breastfed_at_birth,
SUM(CASE WHEN (unscheduled_visit=0 AND days_visit_late < 8) OR
(timeend::DATE - next_visit) < 8 THEN 1 ELSE 0 END
) OVER w as valid_visits,
LAST_VALUE(where_born) OVER w AS where_born
FROM "{ucr_tablename}"
WHERE state_id = %(state_id)s AND
timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND
case_load_ccs_record0 IS NOT NULL
WINDOW w AS (
PARTITION BY case_load_ccs_record0
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=tablename
), query_params
def aggregate_query(self):
"""
Returns the base aggregate query which is used to insert all the locations
into the LS data table.
"""
columns = (
('state_id', 'location.state_id'),
('district_id', 'location.district_id'),
('block_id', 'location.block_id'),
('supervisor_id', 'location.supervisor_id'),
('month', "'{}'".format(month_formatter(self.month_start))),
('awc_visits', 'COALESCE(awc_table.awc_visits, 0)'),
('vhnd_observed', 'COALESCE(vhnd_table.vhnd_observed, 0)'),
('beneficiary_vists', 'COALESCE(beneficiary_table.beneficiary_vists, 0)'),
('aggregation_level', '4')
)
return """
INSERT INTO "{tablename}" (
{columns}
)
(
SELECT
{calculations}
from (select distinct state_id, district_id, block_id, supervisor_id from "{awc_location_ucr}" where (
state_is_test=0 AND
district_is_test=0 AND
block_is_test=0 AND
supervisor_is_test=0
)) location
LEFT JOIN "{awc_table}" awc_table on (
location.supervisor_id=awc_table.supervisor_id AND
awc_table.month = %(start_date)s
)
LEFT JOIN "{vhnd_table}" vhnd_table on (
location.supervisor_id = vhnd_table.supervisor_id AND
vhnd_table.month = %(start_date)s
)
LEFT JOIN "{beneficiary_table}" beneficiary_table on (
location.supervisor_id = beneficiary_table.supervisor_id AND
beneficiary_table.month = %(start_date)s
)
)
""".format(
tablename=self.tablename,
columns=", ".join([col[0] for col in columns]),
calculations=", ".join([col[1] for col in columns]),
awc_location_ucr=self._ucr_tablename(ucr_id=self.awc_location_ucr),
awc_table=AGG_LS_AWC_VISIT_TABLE,
vhnd_table=AGG_LS_VHND_TABLE,
beneficiary_table=AGG_LS_BENEFICIARY_TABLE
), {
'start_date': self.month_start
}
def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
return """
SELECT DISTINCT ccs_record_case_id AS case_id,
LAST_VALUE(timeend) OVER w AS latest_time_end,
MAX(counsel_methods) OVER w AS counsel_methods,
LAST_VALUE(is_ebf) OVER w as is_ebf,
SUM(CASE WHEN (unscheduled_visit=0 AND days_visit_late < 8) OR (timeend::DATE - next_visit) < 8 THEN 1 ELSE 0 END) OVER w as valid_visits
FROM "{ucr_tablename}"
WHERE timeend >= %(current_month_start)s AND timeend < %(next_month_start)s AND state_id = %(state_id)s
WINDOW w AS (
PARTITION BY ccs_record_case_id
ORDER BY timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(ucr_tablename=self.ucr_tablename), {
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id
}
def aggregation_query(self):
tablename = self.generate_child_tablename(self.month)
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
query_params = {
"month": month_formatter(self.month),
"state_id": self.state_id,
"current_month_start": current_month_start,
"next_month_start": next_month_start,
}
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed,
sum_attended_child_ids, lunch_count
) (
SELECT DISTINCT ON (ucr.child_health_case_id)
%(state_id)s AS state_id,
LAST_VALUE(ucr.supervisor_id) OVER w AS supervisor_id,
%(month)s AS month,
ucr.child_health_case_id AS case_id,
MAX(ucr.timeend) OVER w AS latest_time_end_processed,
SUM(ucr.attended_child_ids) OVER w AS sum_attended_child_ids,
SUM(ucr.lunch) OVER w AS lunch_count
FROM "{ucr_tablename}" ucr inner join daily_attendance ON (
ucr.doc_id = daily_attendance.doc_id AND
daily_attendance.month=%(current_month_start)s
)
WHERE ucr.state_id = %(state_id)s AND
ucr.timeend >= %(current_month_start)s AND ucr.timeend < %(next_month_start)s AND
ucr.child_health_case_id IS NOT NULL
WINDOW w AS (PARTITION BY ucr.child_health_case_id)
)
""".format(
ucr_tablename=self.ucr_tablename,
tablename=tablename
), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
previous_month_tablename = self.generate_child_tablename(month - relativedelta(months=1))
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params = {
"month": month_formatter(month),
"state_id": self.state_id
}
query_params.update(ucr_query_params)
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, case_id, latest_time_end_processed, counsel_increase_food_bf,
counsel_breast, skin_to_skin, is_ebf, water_or_milk, other_milk_to_child,
tea_other, eating, counsel_exclusive_bf, counsel_only_milk, counsel_adequate_bf,
not_breastfeeding
) (
SELECT
%(state_id)s AS state_id,
COALESCE(ucr.supervisor_id, prev_month.supervisor_id) AS supervisor_id,
%(month)s AS month,
COALESCE(ucr.case_id, prev_month.case_id) AS case_id,
GREATEST(ucr.latest_time_end, prev_month.latest_time_end_processed) AS latest_time_end_processed,
GREATEST(ucr.counsel_increase_food_bf, prev_month.counsel_increase_food_bf) AS counsel_increase_food_bf,
GREATEST(ucr.counsel_breast, prev_month.counsel_breast) AS counsel_breast,
GREATEST(ucr.skin_to_skin, prev_month.skin_to_skin) AS skin_to_skin,
ucr.is_ebf AS is_ebf,
ucr.water_or_milk AS water_or_milk,
ucr.other_milk_to_child AS other_milk_to_child,
ucr.tea_other AS tea_other,
ucr.eating AS eating,
GREATEST(ucr.counsel_exclusive_bf, prev_month.counsel_exclusive_bf) AS counsel_exclusive_bf,
GREATEST(ucr.counsel_only_milk, prev_month.counsel_only_milk) AS counsel_only_milk,
GREATEST(ucr.counsel_adequate_bf, prev_month.counsel_adequate_bf) AS counsel_adequate_bf,
ucr.not_breastfeeding AS not_breastfeeding
FROM ({ucr_table_query}) ucr
FULL OUTER JOIN "{previous_month_tablename}" prev_month
ON ucr.case_id = prev_month.case_id
)
""".format(
ucr_table_query=ucr_query,
previous_month_tablename=previous_month_tablename,
tablename=tablename
), query_params
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
ucr_query, ucr_query_params = self.data_from_ucr_query()
query_params = {
"month": month_formatter(month),
"state_id": self.state_id
}
query_params.update(ucr_query_params)
return """
INSERT INTO "{tablename}" (
state_id, supervisor_id, month, awc_id, latest_time_end_processed,
awc_building, source_drinking_water, toilet_functional,
electricity_awc, adequate_space_pse,
adult_scale_available, baby_scale_available, flat_scale_available,
adult_scale_usable, baby_scale_usable, cooking_utensils_usable,
infantometer_usable, medicine_kits_usable, stadiometer_usable
) (
SELECT
%(state_id)s AS state_id,
ucr.supervisor_id AS supervisor_id,
%(month)s AS month,
ucr.awc_id AS awc_id,
ucr.timeend as latest_time_end_processed,
ucr.awc_building as awc_building,
ucr.source_drinking_water as source_drinking_water,
ucr.toilet_functional as toilet_functional,
ucr.electricity_awc as electricity_awc,
ucr.adequate_space_pse as adequate_space_pse,
ucr.adult_scale_available as adult_scale_available,
ucr.baby_scale_available as baby_scale_available,
ucr.flat_scale_available as flat_scale_available,
ucr.adult_scale_usable as adult_scale_usable,
ucr.baby_scale_usable as baby_scale_usable,
ucr.cooking_utensils_usable as cooking_utensils_usable,
ucr.infantometer_usable as infantometer_usable,
ucr.medicine_kits_usable as medicine_kits_usable,
ucr.stadiometer_usable as stadiometer_usable
FROM ({ucr_table_query}) ucr
)
""".format(
ucr_table_query=ucr_query,
tablename=tablename
), query_params
def create_table_query(self, month=None):
month = month or self.month
month_string = month_formatter(month)
tablename = self.generate_child_tablename(month)
return """
CREATE TABLE IF NOT EXISTS "{child_tablename}" (
CHECK (month = %(month_string)s AND state_id = %(state_id)s),
LIKE "{parent_tablename}" INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
) INHERITS ("{parent_tablename}")
""".format(
parent_tablename=self.aggregate_parent_table,
child_tablename=tablename,
), {
"month_string": month_string,
"state_id": self.state_id
}
def _state_aggregation_query(self, state_id):
start_month_string = self.month.strftime("'%Y-%m-%d'::date")
end_month_string = (self.month + relativedelta(months=1) - relativedelta(days=1)).strftime("'%Y-%m-%d'::date")
age_in_days = "({} - child_health.dob)::integer".format(end_month_string)
age_in_months_end = "({} / 30.4 )".format(age_in_days)
age_in_months = "(({} - child_health.dob) / 30.4 )".format(start_month_string)
open_in_month = ("(({} - child_health.opened_on::date)::integer >= 0) AND (child_health.closed = 0 OR (child_health.closed_on::date - {})::integer > 0)").format(end_month_string, start_month_string)
alive_in_month = "(child_health.date_death IS NULL OR child_health.date_death - {} >= 0)".format(start_month_string)
seeking_services = "(child_health.is_availing = 1 AND child_health.is_migrated = 0)"
born_in_month = "({} AND child_health.dob BETWEEN {} AND {})".format(seeking_services, start_month_string, end_month_string)
valid_in_month = "({} AND {} AND {} AND {} <= 72)".format(open_in_month, alive_in_month, seeking_services, age_in_months)
pse_eligible = "({} AND {} > 36)".format(valid_in_month, age_in_months_end)
ebf_eligible = "({} AND {} <= 6)".format(valid_in_month, age_in_months)
wer_eligible = "({} AND {} <= 60)".format(valid_in_month, age_in_months)
cf_eligible = "({} AND {} > 6 AND {} <= 24)".format(valid_in_month, age_in_months_end, age_in_months)
cf_initiation_eligible = "({} AND {} > 6 AND {} <= 8)".format(valid_in_month, age_in_months_end, age_in_months)
thr_eligible = "({} AND {} > 6 AND {} <= 36)".format(valid_in_month, age_in_months_end, age_in_months)
pnc_eligible = "({} AND {} - child_health.dob > 0 AND {} - child_health.dob <= 20)".format(valid_in_month, end_month_string, start_month_string)
height_eligible = "({} AND {} > 6 AND {} <= 60)".format(valid_in_month, age_in_months_end, age_in_months)
fully_immunized_eligible = "({} AND {} > 12)".format(valid_in_month, age_in_months_end)
immunized_age_in_days = "(child_tasks.immun_one_year_date - child_health.dob)"
fully_immun_before_month = "(child_tasks.immun_one_year_date < {})".format(end_month_string)
columns = (
("awc_id", "child_health.awc_id"),
("case_id", "child_health.doc_id"),
("month", self.month.strftime("'%Y-%m-%d'")),
("sex", "child_health.sex"),
("age_tranche",
"CASE WHEN {age_in_days} <= 28 THEN 0 "
" WHEN {age_in_months} <= 6 THEN 6 "
" WHEN {age_in_months} <= 12 THEN 12 "
" WHEN {age_in_months} <= 24 THEN 24 "
" WHEN {age_in_months} <= 36 THEN 36 "
" WHEN {age_in_months} <= 48 THEN 48 "
" WHEN {age_in_months} <= 60 THEN 60 "
" WHEN {age_in_months} <= 72 THEN 72 "
"ELSE NULL END".format(age_in_days=age_in_days, age_in_months=age_in_months)),
("caste", "child_health.caste"),
("disabled", "child_health.disabled"),
("minority", "child_health.minority"),
("resident", "child_health.resident"),
("dob", "child_health.dob"),
("age_in_months", 'trunc({})'.format(age_in_months_end)),
("open_in_month", "CASE WHEN {} THEN 1 ELSE 0 END".format(open_in_month)),
("alive_in_month", "CASE WHEN {} THEN 1 ELSE 0 END".format(alive_in_month)),
("born_in_month", "CASE WHEN {} THEN 1 ELSE 0 END".format(born_in_month)),
("bf_at_birth_born_in_month", "CASE WHEN {} AND child_health.bf_at_birth = 'yes' THEN 1 ELSE 0 END".format(born_in_month)),
("low_birth_weight_born_in_month", "CASE WHEN {} AND child_health.lbw_open_count = 1 THEN 1 ELSE 0 END".format(born_in_month)),
("fully_immunized_eligible", "CASE WHEN {} THEN 1 ELSE 0 END".format(fully_immunized_eligible)),
("fully_immunized_on_time", "CASE WHEN {} AND {} <= 365 AND {} THEN 1 ELSE 0 END".format(fully_immunized_eligible, immunized_age_in_days, fully_immun_before_month)),
("fully_immunized_late", "CASE WHEN {} AND {} > 365 AND {} THEN 1 ELSE 0 END".format(fully_immunized_eligible, immunized_age_in_days, fully_immun_before_month)),
("has_aadhar_id",
"CASE WHEN person_cases.aadhar_date < {} THEN 1 ELSE 0 END".format(end_month_string)),
("valid_in_month", "CASE WHEN {} THEN 1 ELSE 0 END".format(valid_in_month)),
("valid_all_registered_in_month",
"CASE WHEN {} AND {} AND {} <= 72 AND child_health.is_migrated = 0 THEN 1 ELSE 0 END".format(open_in_month, alive_in_month, age_in_months)),
("person_name", "child_health.person_name"),
("mother_name", "child_health.mother_name"),
# PSE/DF Indicators
("pse_eligible", "CASE WHEN {} THEN 1 ELSE 0 END".format(pse_eligible)),
("pse_days_attended",
"CASE WHEN {} THEN COALESCE(df.sum_attended_child_ids, 0) ELSE NULL END".format(pse_eligible)),
("lunch_count", "COALESCE(df.lunch_count, 0)"),
# EBF Indicators
("ebf_eligible", "CASE WHEN {} THEN 1 ELSE 0 END".format(ebf_eligible)),
("ebf_in_month", "CASE WHEN {} THEN COALESCE(pnc.is_ebf, 0) ELSE 0 END".format(ebf_eligible)),
("ebf_not_breastfeeding_reason",
"CASE WHEN {} THEN pnc.not_breastfeeding ELSE NULL END".format(ebf_eligible)),
("ebf_drinking_liquid",
"CASE WHEN {} THEN GREATEST(pnc.water_or_milk, pnc.other_milk_to_child, pnc.tea_other, 0) ELSE 0 END".format(ebf_eligible)),
("ebf_eating",
"CASE WHEN {} THEN COALESCE(pnc.eating, 0) ELSE 0 END".format(ebf_eligible)),
("ebf_no_bf_no_milk", "0"),
("ebf_no_bf_pregnant_again", "0"),
("ebf_no_bf_child_too_old", "0"),
("ebf_no_bf_mother_sick", "0"),
("counsel_adequate_bf",
"CASE WHEN {} THEN COALESCE(pnc.counsel_adequate_bf, 0) ELSE 0 END".format(ebf_eligible)),
("ebf_no_info_recorded",
"CASE WHEN {} AND date_trunc('MONTH', pnc.latest_time_end_processed) = %(start_date)s THEN 0 ELSE (CASE WHEN {} THEN 1 ELSE 0 END) END".format(ebf_eligible, ebf_eligible)),
("counsel_ebf",
"CASE WHEN {} THEN GREATEST(pnc.counsel_exclusive_bf, pnc.counsel_only_milk, 0) ELSE 0 END".format(ebf_eligible)),
# PNC Indicators
("pnc_eligible", "CASE WHEN {} THEN 1 ELSE 0 END".format(pnc_eligible)),
("counsel_increase_food_bf",
"CASE WHEN {} THEN COALESCE(pnc.counsel_increase_food_bf, 0) ELSE 0 END".format(pnc_eligible)),
("counsel_manage_breast_problems",
"CASE WHEN {} THEN COALESCE(pnc.counsel_breast, 0) ELSE 0 END".format(pnc_eligible)),
("counsel_skin_to_skin",
"CASE WHEN {} THEN COALESCE(pnc.skin_to_skin, 0) ELSE 0 END".format(pnc_eligible)),
# GM Indicators
("wer_eligible", "CASE WHEN {} THEN 1 ELSE 0 END".format(wer_eligible)),
("nutrition_status_last_recorded",
"CASE "
"WHEN NOT {} THEN NULL "
"WHEN gm.zscore_grading_wfa = 1 THEN 'severely_underweight' "
"WHEN gm.zscore_grading_wfa = 2 THEN 'moderately_underweight' "
"WHEN gm.zscore_grading_wfa IN (3, 4) THEN 'normal' "
"ELSE 'unknown' END".format(wer_eligible)),
("current_month_nutrition_status",
"CASE "
"WHEN NOT {} THEN NULL "
"WHEN date_trunc('MONTH', gm.zscore_grading_wfa_last_recorded) != %(start_date)s THEN 'unweighed' "
"WHEN gm.zscore_grading_wfa = 1 THEN 'severely_underweight' "
"WHEN gm.zscore_grading_wfa = 2 THEN 'moderately_underweight' "
"WHEN gm.zscore_grading_wfa IN (3, 4) THEN 'normal' "
"ELSE 'unweighed' END".format(wer_eligible)),
("nutrition_status_weighed",
"CASE "
"WHEN {} AND date_trunc('MONTH', gm.zscore_grading_wfa_last_recorded) = %(start_date)s THEN 1 "
"ELSE 0 END".format(wer_eligible)),
("recorded_weight",
"CASE "
"WHEN NOT {} THEN NULL "
"WHEN date_trunc('MONTH', gm.weight_child_last_recorded) = %(start_date)s THEN gm.weight_child "
"ELSE NULL END".format(wer_eligible)),
("recorded_height",
"CASE "
"WHEN date_trunc('MONTH', gm.height_child_last_recorded) = %(start_date)s THEN gm.height_child "
"ELSE NULL END"),
("height_measured_in_month",
"CASE "
"WHEN date_trunc('MONTH', gm.height_child_last_recorded) = %(start_date)s AND {} THEN 1 "
"ELSE 0 END".format(height_eligible)),
("current_month_stunting",
"CASE "
"WHEN NOT {} THEN NULL "
"WHEN date_trunc('MONTH', gm.zscore_grading_hfa_last_recorded) != %(start_date)s THEN 'unmeasured' "
"WHEN gm.zscore_grading_hfa = 1 THEN 'severe' "
"WHEN gm.zscore_grading_hfa = 2 THEN 'moderate' "
"WHEN gm.zscore_grading_hfa = 3 THEN 'normal' "
"ELSE 'unmeasured' END".format(height_eligible)),
("stunting_last_recorded",
"CASE "
"WHEN NOT {} THEN NULL "
"WHEN gm.zscore_grading_hfa = 1 THEN 'severe' "
"WHEN gm.zscore_grading_hfa = 2 THEN 'moderate' "
"WHEN gm.zscore_grading_hfa = 3 THEN 'normal' "
"ELSE 'unknown' END".format(height_eligible)),
("wasting_last_recorded",
"CASE "
"WHEN NOT {} THEN NULL "
"WHEN gm.zscore_grading_wfh = 1 THEN 'severe' "
"WHEN gm.zscore_grading_wfh = 2 THEN 'moderate' "
"WHEN gm.zscore_grading_wfh = 3 THEN 'normal' "
"ELSE 'unknown' END".format(height_eligible)),
("current_month_wasting",
"CASE "
"WHEN NOT {} THEN NULL "
"WHEN date_trunc('MONTH', gm.zscore_grading_wfh_last_recorded) != %(start_date)s THEN 'unmeasured' "
"WHEN gm.zscore_grading_wfh = 1 THEN 'severe' "
"WHEN gm.zscore_grading_wfh = 2 THEN 'moderate' "
"WHEN gm.zscore_grading_wfh = 3 THEN 'normal' "
"ELSE 'unmeasured' END".format(height_eligible)),
("zscore_grading_hfa", "gm.zscore_grading_hfa"),
("zscore_grading_hfa_recorded_in_month",
"CASE WHEN (date_trunc('MONTH', gm.zscore_grading_hfa_last_recorded) = %(start_date)s) THEN 1 ELSE 0 END"),
("zscore_grading_wfh", "gm.zscore_grading_wfh"),
("zscore_grading_wfh_recorded_in_month",
"CASE WHEN (date_trunc('MONTH', gm.zscore_grading_wfh_last_recorded) = %(start_date)s) THEN 1 ELSE 0 END"),
("muac_grading", "gm.muac_grading"),
("muac_grading_recorded_in_month",
"CASE WHEN (date_trunc('MONTH', gm.muac_grading_last_recorded) = %(start_date)s) THEN 1 ELSE 0 END"),
# CF Indicators
("cf_eligible", "CASE WHEN {} THEN 1 ELSE 0 END".format(cf_eligible)),
("cf_initiation_eligible", "CASE WHEN {} THEN 1 ELSE 0 END".format(cf_initiation_eligible)),
("cf_in_month", "CASE WHEN {} THEN COALESCE(cf.comp_feeding_latest, 0) ELSE 0 END".format(cf_eligible)),
("cf_diet_diversity", "CASE WHEN {} THEN COALESCE(cf.diet_diversity, 0) ELSE 0 END".format(cf_eligible)),
("cf_diet_quantity", "CASE WHEN {} THEN COALESCE(cf.diet_quantity, 0) ELSE 0 END".format(cf_eligible)),
("cf_handwashing", "CASE WHEN {} THEN COALESCE(cf.hand_wash, 0) ELSE 0 END".format(cf_eligible)),
("cf_demo", "CASE WHEN {} THEN COALESCE(cf.demo_comp_feeding, 0) ELSE 0 END".format(cf_eligible)),
("counsel_pediatric_ifa",
"CASE WHEN {} THEN COALESCE(cf.counselled_pediatric_ifa, 0) ELSE 0 END".format(cf_eligible)),
("counsel_comp_feeding_vid",
"CASE WHEN {} THEN COALESCE(cf.play_comp_feeding_vid, 0) ELSE 0 END".format(cf_eligible)),
("cf_initiation_in_month",
"CASE WHEN {} THEN COALESCE(cf.comp_feeding_ever, 0) ELSE 0 END".format(cf_initiation_eligible)),
# THR Indicators
("thr_eligible", "CASE WHEN {} THEN 1 ELSE 0 END".format(thr_eligible)),
("num_rations_distributed",
"CASE WHEN {} THEN COALESCE(thr.days_ration_given_child, 0) ELSE NULL END".format(thr_eligible)),
("days_ration_given_child", "thr.days_ration_given_child"),
# Tasks case Indicators
("immunization_in_month", """
CASE WHEN
date_trunc('MONTH', child_tasks.due_list_date_1g_dpt_1) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_2g_dpt_2) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_3g_dpt_3) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_5g_dpt_booster) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_5g_dpt_booster1) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_7gdpt_booster_2) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_0g_hep_b_0) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_1g_hep_b_1) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_2g_hep_b_2) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_3g_hep_b_3) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_3g_ipv) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_4g_je_1) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_5g_je_2) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_5g_measles_booster) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_4g_measles) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_1g_penta_1) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_2g_penta_2) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_3g_penta_3) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_1g_rv_1) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_2g_rv_2) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_3g_rv_3) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_4g_vit_a_1) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_5g_vit_a_2) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_6g_vit_a_3) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_6g_vit_a_4) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_6g_vit_a_5) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_6g_vit_a_6) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_6g_vit_a_7) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_6g_vit_a_8) = %(start_date)s OR
date_trunc('MONTH', child_tasks.due_list_date_7g_vit_a_9) = %(start_date)s
THEN 1 ELSE NULL END
"""),
("mother_phone_number", "child_health.mother_phone_number"),
("date_death", "child_health.date_death"),
("mother_case_id", "child_health.mother_case_id")
)
return """
INSERT INTO "{tablename}" (
{columns}
) (SELECT
{calculations}
FROM "{child_health_case_ucr}" child_health
LEFT OUTER JOIN "{child_tasks_case_ucr}" child_tasks ON child_health.doc_id = child_tasks.child_health_case_id
AND child_health.state_id = child_tasks.state_id
AND lower(substring(child_tasks.state_id, '.{{3}}$'::text)) = %(state_id_last_3)s
LEFT OUTER JOIN "{person_cases_ucr}" person_cases ON child_health.mother_id = person_cases.doc_id
AND child_health.state_id = person_cases.state_id
AND lower(substring(person_cases.state_id, '.{{3}}$'::text)) = %(state_id_last_3)s
LEFT OUTER JOIN "{agg_cf_table}" cf ON child_health.doc_id = cf.case_id AND cf.month = %(start_date)s
AND child_health.state_id = cf.state_id
LEFT OUTER JOIN "{agg_thr_table}" thr ON child_health.doc_id = thr.case_id AND thr.month = %(start_date)s
AND child_health.state_id = thr.state_id
LEFT OUTER JOIN "{agg_gm_table}" gm ON child_health.doc_id = gm.case_id AND gm.month = %(start_date)s
AND child_health.state_id = gm.state_id
LEFT OUTER JOIN "{agg_pnc_table}" pnc ON child_health.doc_id = pnc.case_id AND pnc.month = %(start_date)s
AND child_health.state_id = pnc.state_id
LEFT OUTER JOIN "{agg_df_table}" df ON child_health.doc_id = df.case_id AND df.month = %(start_date)s
AND child_health.state_id = df.state_id
WHERE child_health.doc_id IS NOT NULL
AND child_health.state_id = %(state_id)s
AND lower(substring(child_health.state_id, '.{{3}}$'::text)) = %(state_id_last_3)s
ORDER BY child_health.awc_id
)
""".format(
tablename=self.temporary_tablename,
columns=", ".join([col[0] for col in columns]),
calculations=", ".join([col[1] for col in columns]),
agg_cf_table=AGG_COMP_FEEDING_TABLE,
agg_thr_table=AGG_CHILD_HEALTH_THR_TABLE,
child_health_case_ucr=self.child_health_case_ucr_tablename,
agg_gm_table=AGG_GROWTH_MONITORING_TABLE,
agg_pnc_table=AGG_CHILD_HEALTH_PNC_TABLE,
agg_df_table=AGG_DAILY_FEEDING_TABLE,
child_tasks_case_ucr=self.child_tasks_case_ucr_tablename,
person_cases_ucr=self.person_case_ucr_tablename
), {
"start_date": self.month,
"next_month": month_formatter(self.month + relativedelta(months=1)),
"state_id": state_id,
"state_id_last_3": state_id[-3:],
}
def updates(self):
yield """
UPDATE "{tablename}" agg_awc SET
awc_days_open = ut.awc_days_open,
awc_num_open = ut.awc_num_open,
awc_days_pse_conducted = ut.awc_days_pse_conducted
FROM (
SELECT
awc_id,
month,
sum(awc_open_count) AS awc_days_open,
CASE WHEN (sum(awc_open_count) > 0) THEN 1 ELSE 0 END AS awc_num_open,
sum(pse_conducted) as awc_days_pse_conducted
FROM "{daily_attendance}"
WHERE month = %(start_date)s GROUP BY awc_id, month
) ut
WHERE ut.month = agg_awc.month AND ut.awc_id = agg_awc.awc_id
""".format(
tablename=self.tablename,
daily_attendance='daily_attendance_{}'.format(month_formatter(self.month_start)),
), {
'start_date': self.month_start
}
yield """
UPDATE "{tablename}" agg_awc SET
cases_child_health = ut.cases_child_health,
cases_child_health_all = ut.cases_child_health_all,
wer_weighed = ut.wer_weighed,
wer_eligible = ut.wer_eligible,
cases_person_beneficiary_v2 = ut.cases_child_health
FROM (
SELECT
awc_id,
month,
sum(valid_in_month) AS cases_child_health,
sum(valid_all_registered_in_month) AS cases_child_health_all,
sum(nutrition_status_weighed) AS wer_weighed,
sum(wer_eligible) AS wer_eligible
FROM agg_child_health
WHERE month = %(start_date)s AND aggregation_level = 5 GROUP BY awc_id, month
) ut
WHERE ut.month = agg_awc.month AND ut.awc_id = agg_awc.awc_id;
""".format(
tablename=self.tablename,
), {
'start_date': self.month_start
}
yield """
UPDATE "{tablename}" agg_awc SET
cases_ccs_pregnant = ut.cases_ccs_pregnant,
cases_ccs_lactating = ut.cases_ccs_lactating,
cases_ccs_pregnant_all = ut.cases_ccs_pregnant_all,
cases_ccs_lactating_all = ut.cases_ccs_lactating_all,
cases_person_beneficiary_v2 = COALESCE(cases_person_beneficiary_v2, 0) + ut.cases_ccs_pregnant + ut.cases_ccs_lactating,
valid_visits = ut.valid_visits,
expected_visits = CASE WHEN ut.valid_visits>ut.expected_visits THEN ut.valid_visits ELSE ut.expected_visits END
FROM (
SELECT
agg_ccs_record_monthly.awc_id,
agg_ccs_record_monthly.month,
sum(agg_ccs_record_monthly.pregnant) AS cases_ccs_pregnant,
sum(agg_ccs_record_monthly.lactating) AS cases_ccs_lactating,
sum(agg_ccs_record_monthly.pregnant_all) AS cases_ccs_pregnant_all,
sum(agg_ccs_record_monthly.lactating_all) AS cases_ccs_lactating_all,
sum(agg_ccs_record_monthly.valid_visits) + COALESCE(home_visit.valid_visits, 0) AS valid_visits,
sum(agg_ccs_record_monthly.expected_visits) +
COALESCE(home_visit.expected_visits, 0) AS expected_visits
FROM agg_ccs_record_monthly
LEFT OUTER JOIN (
SELECT
ucr.awc_id,
%(start_date)s AS month,
SUM(COALESCE(agg_cf.valid_visits, 0)) AS valid_visits,
sum(0.39) AS expected_visits
FROM "{ccs_record_case_ucr}" ucr
LEFT OUTER JOIN "{agg_cf_table}" agg_cf ON (
ucr.case_id = agg_cf.case_id AND
agg_cf.month = %(start_date)s
)
WHERE %(start_date)s - add BETWEEN 184 AND 548 AND (ucr.closed_on IS NULL OR
date_trunc('month', ucr.closed_on)::DATE >= %(start_date)s) AND
date_trunc('month', ucr.opened_on) <= %(start_date)s
GROUP BY ucr.awc_id
) home_visit ON (
agg_ccs_record_monthly.awc_id = home_visit.awc_id AND
home_visit.month=agg_ccs_record_monthly.month
)
WHERE agg_ccs_record_monthly.month = %(start_date)s AND aggregation_level = 5
GROUP BY agg_ccs_record_monthly.awc_id,home_visit.valid_visits,
home_visit.expected_visits, agg_ccs_record_monthly.month
) ut
WHERE ut.month = agg_awc.month AND ut.awc_id = agg_awc.awc_id;
""".format(
tablename=self.tablename,
ccs_record_case_ucr=self._ucr_tablename('static-ccs_record_cases'),
agg_cf_table=AGG_CCS_RECORD_CF_TABLE,
), {
'start_date': self.month_start
}
yield """
UPDATE "{tablename}" agg_awc SET
cases_household = ut.cases_household
FROM (
SELECT
owner_id,
sum(open_count) AS cases_household
FROM "{household_cases}"
GROUP BY owner_id
) ut
WHERE ut.owner_id = agg_awc.awc_id
""".format(
tablename=self.tablename,
household_cases=self._ucr_tablename('static-household_cases'),
), {}
yield """
UPDATE "{tablename}" agg_awc SET
cases_person = ut.cases_person,
cases_person_all = ut.cases_person_all,
cases_person_adolescent_girls_11_14 = ut.cases_person_adolescent_girls_11_14,
cases_person_adolescent_girls_11_14_all = ut.cases_person_adolescent_girls_11_14_all,
cases_person_adolescent_girls_15_18 = ut.cases_person_adolescent_girls_15_18,
cases_person_adolescent_girls_15_18_all = ut.cases_person_adolescent_girls_15_18_all,
cases_person_referred = ut.cases_person_referred
FROM (
SELECT
awc_id,
sum({seeking_services}) AS cases_person,
count(*) AS cases_person_all,
sum(CASE WHEN %(month_end_11yr)s > dob AND %(month_start_15yr)s <= dob AND sex = 'F' THEN ({seeking_services}) ELSE 0 END) as cases_person_adolescent_girls_11_14,
sum(CASE WHEN %(month_end_11yr)s > dob AND %(month_start_15yr)s <= dob AND sex = 'F' THEN 1 ELSE 0 END) as cases_person_adolescent_girls_11_14_all,
sum(CASE WHEN %(month_end_15yr)s > dob AND %(month_start_18yr)s <= dob AND sex = 'F' THEN ({seeking_services}) ELSE 0 END) as cases_person_adolescent_girls_15_18,
sum(CASE WHEN %(month_end_15yr)s > dob AND %(month_start_18yr)s <= dob AND sex = 'F' THEN 1 ELSE 0 END) as cases_person_adolescent_girls_15_18_all,
sum(CASE WHEN last_referral_date BETWEEN %(start_date)s AND %(end_date)s THEN 1 ELSE 0 END) as cases_person_referred
FROM "{ucr_tablename}"
WHERE (opened_on <= %(end_date)s AND (closed_on IS NULL OR closed_on >= %(start_date)s ))
GROUP BY awc_id
) ut
WHERE ut.awc_id = agg_awc.awc_id;
""".format(
tablename=self.tablename,
ucr_tablename=self._ucr_tablename('static-person_cases_v3'),
seeking_services="(CASE WHEN registered_status IS DISTINCT FROM 0 AND migration_status IS DISTINCT FROM 1 THEN 1 ELSE 0 END)"
), {
'start_date': self.month_start,
'end_date': self.month_end,
'month_end_11yr': self.month_end_11yr,
'month_start_15yr': self.month_start_15yr,
'month_end_15yr': self.month_end_15yr,
'month_start_18yr': self.month_start_18yr,
}
yield """
UPDATE "{tablename}" agg_awc SET
cases_person_has_aadhaar_v2 = ut.child_has_aadhar,
num_children_immunized = ut.num_children_immunized
FROM (
SELECT
awc_id,
sum(has_aadhar_id) as child_has_aadhar,
sum(immunization_in_month) AS num_children_immunized
FROM "{child_health_monthly}"
WHERE valid_in_month = 1
GROUP BY awc_id
) ut
WHERE ut.awc_id = agg_awc.awc_id;
""".format(
tablename=self.tablename,
child_health_monthly="child_health_monthly_{}".format(self.month_start),
), {}
yield """
UPDATE "{tablename}" agg_awc SET
num_anc_visits = ut.num_anc_visits,
cases_person_has_aadhaar_v2 = COALESCE(cases_person_has_aadhaar_v2, 0) + ut.ccs_has_aadhar
FROM (
SELECT
awc_id,
sum(anc_in_month) AS num_anc_visits,
sum(has_aadhar_id) AS ccs_has_aadhar
FROM "{ccs_record_monthly}"
WHERE pregnant = 1 OR lactating = 1
GROUP BY awc_id
) ut
WHERE ut.awc_id = agg_awc.awc_id;
""".format(
tablename=self.tablename,
ccs_record_monthly="ccs_record_monthly_{}".format(self.month_start),
), {}
yield """
UPDATE "{tablename}" agg_awc SET
usage_num_pse = ut.usage_num_pse,
usage_num_gmp = ut.usage_num_gmp,
usage_num_thr = ut.usage_num_thr,
usage_num_hh_reg = ut.usage_num_hh_reg,
is_launched = ut.is_launched,
num_launched_states = ut.num_launched_awcs,
num_launched_districts = ut.num_launched_awcs,
num_launched_blocks = ut.num_launched_awcs,
num_launched_supervisors = ut.num_launched_awcs,
num_launched_awcs = ut.num_launched_awcs,
usage_num_add_person = ut.usage_num_add_person,
usage_num_add_pregnancy = ut.usage_num_add_pregnancy,
usage_num_home_visit = ut.usage_num_home_visit,
usage_num_bp_tri1 = ut.usage_num_bp_tri1,
usage_num_bp_tri2 = ut.usage_num_bp_tri2,
usage_num_bp_tri3 = ut.usage_num_bp_tri3,
usage_num_pnc = ut.usage_num_pnc,
usage_num_ebf = ut.usage_num_ebf,
usage_num_cf = ut.usage_num_cf,
usage_num_delivery = ut.usage_num_delivery,
usage_awc_num_active = ut.usage_awc_num_active,
usage_num_due_list_ccs = ut.usage_num_due_list_ccs,
usage_num_due_list_child_health = ut.usage_num_due_list_child_health
FROM (
SELECT
awc_id,
month,
sum(pse) AS usage_num_pse,
sum(gmp) AS usage_num_gmp,
sum(thr) AS usage_num_thr,
sum(add_household) AS usage_num_hh_reg,
CASE WHEN sum(add_household) > 0 THEN 'yes' ELSE 'no' END as is_launched,
CASE WHEN sum(add_household) > 0 THEN 1 ELSE 0 END as num_launched_awcs,
sum(add_person) AS usage_num_add_person,
sum(add_pregnancy) AS usage_num_add_pregnancy,
sum(home_visit) AS usage_num_home_visit,
sum(bp_tri1) AS usage_num_bp_tri1,
sum(bp_tri2) AS usage_num_bp_tri2,
sum(bp_tri3) AS usage_num_bp_tri3,
sum(pnc) AS usage_num_pnc,
sum(ebf) AS usage_num_ebf,
sum(cf) AS usage_num_cf,
sum(delivery) AS usage_num_delivery,
CASE WHEN (sum(due_list_ccs) + sum(due_list_child) + sum(pse) + sum(gmp) + sum(thr) + sum(home_visit) + sum(add_pregnancy) + sum(add_household)) >= 15 THEN 1 ELSE 0 END AS usage_awc_num_active,
sum(due_list_ccs) AS usage_num_due_list_ccs,
sum(due_list_child) AS usage_num_due_list_child_health
FROM "{usage_table}"
WHERE month = %(start_date)s GROUP BY awc_id, month
) ut
WHERE ut.month = agg_awc.month AND ut.awc_id = agg_awc.awc_id;
""".format(
tablename=self.tablename,
usage_table=self._ucr_tablename('static-usage_forms'),
), {
'start_date': self.month_start
}
yield """
UPDATE "{tablename}" agg_awc SET
is_launched = 'yes',
num_launched_awcs = 1
FROM (
SELECT DISTINCT(awc_id)
FROM agg_awc
WHERE month = %(prev_month)s AND num_launched_awcs > 0 AND aggregation_level=5
) ut
WHERE ut.awc_id = agg_awc.awc_id;
""".format(
tablename=self.tablename
), {
'prev_month': self.prev_month
}
yield """
UPDATE "{tablename}" agg_awc SET
infra_last_update_date = ut.infra_last_update_date,
infra_type_of_building = ut.infra_type_of_building,
infra_clean_water = ut.infra_clean_water,
infra_functional_toilet = ut.infra_functional_toilet,
infra_baby_weighing_scale = ut.infra_baby_weighing_scale,
infra_adult_weighing_scale = ut.infra_adult_weighing_scale,
infra_infant_weighing_scale = ut.infra_infant_weighing_scale,
infra_cooking_utensils = ut.infra_cooking_utensils,
infra_medicine_kits = ut.infra_medicine_kits,
infra_adequate_space_pse = ut.infra_adequate_space_pse,
electricity_awc = ut.electricity_awc,
infantometer = ut.infantometer,
stadiometer = ut.stadiometer
FROM (
SELECT
awc_id,
month,
latest_time_end_processed::date AS infra_last_update_date,
CASE
WHEN awc_building = 1 THEN 'pucca'
WHEN awc_building = 2 THEN 'semi_pucca'
WHEN awc_building = 3 THEN 'kuccha'
WHEN awc_building = 4 THEN 'partial_covered_space'
ELSE NULL END AS infra_type_of_building,
CASE WHEN source_drinking_water IN (1, 2, 3) THEN 1 ELSE 0 END AS infra_clean_water,
toilet_functional AS infra_functional_toilet,
baby_scale_usable AS infra_baby_weighing_scale,
GREATEST(adult_scale_available, adult_scale_usable, 0) AS infra_adult_weighing_scale,
GREATEST(baby_scale_available, flat_scale_available, baby_scale_usable, 0) AS infra_infant_weighing_scale,
cooking_utensils_usable AS infra_cooking_utensils,
medicine_kits_usable AS infra_medicine_kits,
CASE WHEN adequate_space_pse = 1 THEN 1 ELSE 0 END AS infra_adequate_space_pse,
electricity_awc AS electricity_awc,
infantometer_usable AS infantometer,
stadiometer_usable AS stadiometer
FROM icds_dashboard_infrastructure_forms
WHERE month = %(start_date)s
) ut
WHERE ut.awc_id = agg_awc.awc_id;
-- could possibly add multicol indexes to make order by faster?
""".format(
tablename=self.tablename,
), {
'start_date': self.month_start
}
yield """
UPDATE "{tablename}" agg_awc SET num_awc_infra_last_update =
CASE WHEN infra_last_update_date IS NOT NULL AND
%(month_start_6m)s < infra_last_update_date THEN 1 ELSE 0 END
""".format(
tablename=self.tablename
), {
'month_start_6m': self.month_start_6m
}
yield """
UPDATE "{tablename}" agg_awc SET
state_is_test = ut.state_is_test,
district_is_test = ut.district_is_test,
block_is_test = ut.block_is_test,
supervisor_is_test = ut.supervisor_is_test,
awc_is_test = ut.awc_is_test
FROM (
SELECT
doc_id as awc_id,
MAX(state_is_test) as state_is_test,
MAX(district_is_test) as district_is_test,
MAX(block_is_test) as block_is_test,
MAX(supervisor_is_test) as supervisor_is_test,
MAX(awc_is_test) as awc_is_test
FROM "{awc_location_tablename}"
GROUP BY awc_id
) ut
WHERE ut.awc_id = agg_awc.awc_id AND (
(
agg_awc.state_is_test IS NULL OR
agg_awc.district_is_test IS NULL OR
agg_awc.block_is_test IS NULL OR
agg_awc.supervisor_is_test IS NULL OR
agg_awc.awc_is_test IS NULL
) OR (
ut.state_is_test != agg_awc.state_is_test OR
ut.district_is_test != agg_awc.district_is_test OR
ut.block_is_test != agg_awc.block_is_test OR
ut.supervisor_is_test != agg_awc.supervisor_is_test OR
ut.awc_is_test != agg_awc.awc_is_test
)
)
""".format(
tablename=self.tablename,
awc_location_tablename='awc_location',
), {
}
def generate_child_tablename(self, month=None):
month = month or self.month
month_string = month_formatter(month)
hash_for_table = hashlib.md5((self.state_id + month_string).encode('utf-8')).hexdigest()[8:]
return self.aggregate_child_table_prefix + hash_for_table
def data_from_ucr_query(self):
current_month_start = month_formatter(self.month)
next_month_start = month_formatter(self.month + relativedelta(months=1))
# We need many windows here because we want the last time changed for each of these columns
# Window definitions inspired by https://stackoverflow.com/a/47223416
# The CASE/WHEN's are needed, because time end should be NULL when a form has not changed the value,
# but the windows include all forms (this works because we use LAST_VALUE and NULLs are sorted to the top
return """
SELECT
DISTINCT child_health_case_id AS case_id,
LAST_VALUE(weight_child) OVER weight_child AS weight_child,
CASE
WHEN LAST_VALUE(weight_child) OVER weight_child IS NULL THEN NULL
ELSE LAST_VALUE(timeend) OVER weight_child
END AS weight_child_last_recorded,
LAST_VALUE(height_child) OVER height_child AS height_child,
CASE
WHEN LAST_VALUE(height_child) OVER height_child IS NULL THEN NULL
ELSE LAST_VALUE(timeend) OVER height_child
END AS height_child_last_recorded,
CASE
WHEN LAST_VALUE(zscore_grading_wfa) OVER zscore_grading_wfa = 0 THEN NULL
ELSE LAST_VALUE(zscore_grading_wfa) OVER zscore_grading_wfa
END AS zscore_grading_wfa,
CASE
WHEN LAST_VALUE(zscore_grading_wfa) OVER zscore_grading_wfa = 0 THEN NULL
ELSE LAST_VALUE(timeend) OVER zscore_grading_wfa
END AS zscore_grading_wfa_last_recorded,
CASE
WHEN LAST_VALUE(zscore_grading_hfa) OVER zscore_grading_hfa = 0 THEN NULL
ELSE LAST_VALUE(zscore_grading_hfa) OVER zscore_grading_hfa
END AS zscore_grading_hfa,
CASE
WHEN LAST_VALUE(zscore_grading_hfa) OVER zscore_grading_hfa = 0 THEN NULL
ELSE LAST_VALUE(timeend) OVER zscore_grading_hfa
END AS zscore_grading_hfa_last_recorded,
CASE
WHEN LAST_VALUE(zscore_grading_wfh) OVER zscore_grading_wfh = 0 THEN NULL
ELSE LAST_VALUE(zscore_grading_wfh) OVER zscore_grading_wfh
END AS zscore_grading_wfh,
CASE
WHEN LAST_VALUE(zscore_grading_wfh) OVER zscore_grading_wfh = 0 THEN NULL
ELSE LAST_VALUE(timeend) OVER zscore_grading_wfh
END AS zscore_grading_wfh_last_recorded,
CASE
WHEN LAST_VALUE(muac_grading) OVER muac_grading = 0 THEN NULL
ELSE LAST_VALUE(muac_grading) OVER muac_grading
END AS muac_grading,
CASE
WHEN LAST_VALUE(muac_grading) OVER muac_grading = 0 THEN NULL
ELSE LAST_VALUE(timeend) OVER muac_grading
END AS muac_grading_last_recorded
FROM "{ucr_tablename}"
WHERE timeend >= %(current_month_start)s AND timeend < %(next_month_start)s
AND state_id = %(state_id)s AND child_health_case_id IS NOT NULL
WINDOW
weight_child AS (
PARTITION BY child_health_case_id
ORDER BY
CASE WHEN weight_child IS NULL THEN 0 ELSE 1 END ASC,
timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
),
height_child AS (
PARTITION BY child_health_case_id
ORDER BY
CASE WHEN height_child IS NULL THEN 0 ELSE 1 END ASC,
timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
),
zscore_grading_wfa AS (
PARTITION BY child_health_case_id
ORDER BY
CASE WHEN zscore_grading_wfa = 0 THEN 0 ELSE 1 END ASC,
timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
),
zscore_grading_hfa AS (
PARTITION BY child_health_case_id
ORDER BY
CASE WHEN zscore_grading_hfa = 0 THEN 0 ELSE 1 END ASC,
timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
),
zscore_grading_wfh AS (
PARTITION BY child_health_case_id
ORDER BY
CASE WHEN zscore_grading_wfh = 0 THEN 0 ELSE 1 END ASC,
timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
),
muac_grading AS (
PARTITION BY child_health_case_id
ORDER BY
CASE WHEN muac_grading = 0 THEN 0 ELSE 1 END ASC,
timeend RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
""".format(ucr_tablename=self.ucr_tablename), {
"current_month_start": current_month_start,
"next_month_start": next_month_start,
"state_id": self.state_id
}
def aggregation_query(self):
month = self.month.replace(day=1)
tablename = self.generate_child_tablename(month)
query_params = {
"month": month_formatter(month),
"state_id": self.state_id
}
return """
INSERT INTO "{tablename}" (
state_id, district_id, month, awc_id, block_id, supervisor_id, state_name, district_name, block_name,
supervisor_name, awc_name, aww_name, contact_phone_number, wer_weighed,
wer_eligible, awc_num_open, valid_visits, expected_visits
) (
SELECT
%(state_id)s AS state_id,
awcm.district_id,
%(month)s AS month,
awcm.awc_id,
awcm.block_id,
awcm.supervisor_id,
awcm.state_name,
awcm.district_name,
awcm.block_name,
awcm.supervisor_name,
awcm.awc_name,
awcm.aww_name,
awcm.contact_phone_number,
awcm.wer_weighed,
awcm.wer_eligible,
awcm.awc_days_open,
sum(ccsm.valid_visits),
sum(ccsm.expected_visits)
FROM agg_awc_monthly as awcm
INNER JOIN agg_ccs_record_monthly AS ccsm
ON ccsm.month=awcm.month AND ccsm.awc_id=awcm.awc_id AND ccsm.aggregation_level=awcm.aggregation_level
WHERE awcm.month = %(month)s AND awcm.state_id = %(state_id)s and awcm.aggregation_level=5
GROUP BY awcm.awc_id, awcm.block_id, awcm.supervisor_id, awcm.district_id, awcm.state_name, awcm.district_name,
awcm.block_name, awcm.supervisor_name, awcm.awc_name, awcm.aww_name,
awcm.contact_phone_number, awcm.wer_weighed, awcm.wer_eligible,
awcm.awc_days_open
);
/* update visits for cf cases (not in agg_ccs_record) */
UPDATE "{tablename}" perf
SET expected_visits = expected_visits + cf_data.expected,
valid_visits = valid_visits + cf_data.valid
FROM (
SELECT
SUM(0.39) AS expected,
SUM(COALESCE(agg_cf.valid_visits, 0)) as valid,
ucr.awc_id
FROM "{ccs_record_case_ucr}" ucr
LEFT OUTER JOIN "{agg_cf_table}" agg_cf ON ucr.doc_id = agg_cf.case_id AND agg_cf.month = %(month)s
WHERE %(month)s - add BETWEEN 184 AND 548 AND (closed_on IS NULL OR date_trunc('month', closed_on)::DATE > %(month)s) AND date_trunc('month', opened_on) <= %(month)s
GROUP BY ucr.awc_id
) cf_data
WHERE cf_data.awc_id = perf.awc_id
""".format(
tablename=tablename,
ccs_record_case_ucr=self.ccs_record_case_ucr_tablename,
agg_cf_table=AGG_CCS_RECORD_CF_TABLE,
), query_params
