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 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 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) 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