def handle(self, domains, **options): if not domains and not options['all']: raise CommandError('Specify specific domains or --all') all_domains = [domain.name for domain in get_call_center_domains() if domain.use_fixtures] if domains: for domain in domains: assert domain in all_domains, "Domain '{}' is not a Call Center domain".format(domain) else: domains = all_domains threshold = options['threshold'] domain_stats = get_call_center_data_source_stats(domains) for domain in domains: stats = domain_stats[domain] print('Checking domain:', domain) if stats.error: print('Error getting stats:\n', stats.error) continue for stat in stats.iter_data_source_stats(): diff = math.fabs(stat.ucr_percent - stat.es_percent) if diff > threshold: print("rebuilding data source '{}' in domain '{}': diff = {}".format( stat.name, domain, diff )) try: rebuild_indicators( StaticDataSourceConfiguration.get_doc_id(domain, TABLE_IDS[stat.name]) ) except Exception as e: sys.stderr.write("Error rebuilding data source '{}' in domain '{}':\n{}".format( stat.name, domain, e ))
def agg_from_child_health_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-child_health_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{child_tablename}" AS child ( domain, person_case_id, child_health_case_id, mother_case_id, opened_on, closed_on, birth_weight, breastfed_within_first, is_exclusive_breastfeeding, comp_feeding, diet_diversity, diet_quantity, hand_wash ) ( SELECT %(domain)s AS domain, person_case_id, doc_id, mother_case_id, opened_on, closed_on, birth_weight, breastfed_within_first, is_exclusive_breastfeeding, comp_feeding, diet_diversity, diet_quantity, hand_wash FROM "{child_health_cases_ucr_tablename}" child_health ) ON CONFLICT (child_health_case_id) DO UPDATE SET mother_case_id = EXCLUDED.mother_case_id, closed_on = EXCLUDED.closed_on, birth_weight = EXCLUDED.birth_weight, breastfed_within_first = EXCLUDED.breastfed_within_first, is_exclusive_breastfeeding = EXCLUDED.is_exclusive_breastfeeding, comp_feeding = EXCLUDED.comp_feeding, diet_diversity = EXCLUDED.diet_diversity, diet_quantity = EXCLUDED.diet_quantity, hand_wash = EXCLUDED.hand_wash """.format( child_tablename=cls._meta.db_table, child_health_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def handle(self, domain, file_path, *args, **options): data_source_id = StaticDataSourceConfiguration.get_doc_id( domain, PERSON_TABLE_ID) config = StaticDataSourceConfiguration.by_id(data_source_id) adapter = get_indicator_adapter(config) session_helper = connection_manager.get_session_helper( adapter.engine_id) person_table_name = get_table_name(domain, PERSON_TABLE_ID) awc_location_table_name = get_table_name(domain, AWC_LOCATION_TABLE_ID) session = session_helper.Session with open(os.path.join(os.path.dirname(__file__), 'sql_scripts', 'select_non_aadhar.sql'), encoding='utf-8') as f: sql_script = f.read() rows = session.execute( sql_script % { 'person_table_name': person_table_name, 'awc_location_table_name': awc_location_table_name }) with open(file_path, 'wb') as file_object: writer = csv.writer(file_object) writer.writerow([ 'Name of Beneficiary', 'Date of Birth', 'AWC', 'Block', 'District', 'State' ]) writer.writerows(rows)
def agg_from_child_health_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-child_health_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{child_tablename}" AS child ( domain, person_case_id, child_health_case_id, mother_case_id, opened_on, closed_on, birth_weight, breastfed_within_first, is_exclusive_breastfeeding, comp_feeding, diet_diversity, diet_quantity, hand_wash ) ( SELECT %(domain)s AS domain, person_case_id, doc_id, mother_case_id, opened_on, closed_on, birth_weight, breastfed_within_first, is_exclusive_breastfeeding, comp_feeding, diet_diversity, diet_quantity, hand_wash FROM "{child_health_cases_ucr_tablename}" child_health ) ON CONFLICT (child_health_case_id) DO UPDATE SET mother_case_id = EXCLUDED.mother_case_id, closed_on = EXCLUDED.closed_on, birth_weight = EXCLUDED.birth_weight, breastfed_within_first = EXCLUDED.breastfed_within_first, is_exclusive_breastfeeding = EXCLUDED.is_exclusive_breastfeeding, comp_feeding = EXCLUDED.comp_feeding, diet_diversity = EXCLUDED.diet_diversity, diet_quantity = EXCLUDED.diet_quantity, hand_wash = EXCLUDED.hand_wash """.format( child_tablename=cls._meta.db_table, child_health_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_person_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-person_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET household_case_id = person.household_case_id, dob = person.dob, sex = person.sex, name = person.name, migration_status = person.migration_status, has_aadhar_number = person.has_aadhar_number, contact_phone_number = person.contact_phone_number FROM ( SELECT household_case_id, doc_id, dob, sex, name, migration_status, aadhar_number IS NOT NULL and aadhar_number != '' AS has_aadhar_number, contact_phone_number FROM "{person_cases_ucr_tablename}" ) person WHERE child.person_case_id = person.doc_id """.format( child_tablename=cls._meta.db_table, person_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_household_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-household_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET awc_id = household.awc_owner_id, village_id = household.village_owner_id, hh_address = household.hh_address, hh_religion = household.hh_religion, hh_caste = household.hh_caste, hh_bpl_apl = household.hh_bpl_apl FROM ( SELECT doc_id, awc_owner_id, village_owner_id, hh_address, hh_religion, hh_caste, hh_bpl_apl FROM "{household_cases_ucr_tablename}" ) household WHERE child.household_case_id = household.doc_id """.format( child_tablename=cls._meta.db_table, household_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_eligible_couple_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-eligible_couple_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{woman_tablename}" AS woman SET fp_current_method_ranges = eligible_couple_fp.fp_current_method_ranges FROM ( SELECT person_case_id, array_agg(fp_current_method_range) AS fp_current_method_ranges FROM ( SELECT person_case_id, fp_current_method, daterange(timeend::date, next_timeend::date) AS fp_current_method_range FROM ( SELECT person_case_id, fp_current_method, timeend, LEAD(fp_current_method) OVER w AS next_fp_current_method, LEAD(timeend) OVER w AS next_timeend FROM "{eligible_couple_ucr_tablename}" WINDOW w AS (PARTITION BY person_case_id ORDER BY timeend ASC) ) AS _tmp_table ) eligible_couple WHERE fp_current_method != 'none' GROUP BY person_case_id ) AS eligible_couple_fp WHERE woman.person_case_id = eligible_couple_fp.person_case_id """.format( woman_tablename=cls._meta.db_table, eligible_couple_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_eligible_couple_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-eligible_couple_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{woman_history_tablename}" AS woman ( person_case_id, fp_current_method_history, fp_preferred_method_history, family_planning_form_history ) ( SELECT person_case_id, array_agg(fp_current_method) AS fp_current_method_history, array_agg(fp_preferred_method) AS fp_preferred_method_history, array_agg(timeend) AS family_planning_form_history FROM ( SELECT person_case_id, timeend, ARRAY[timeend::text, fp_current_method] AS fp_current_method, ARRAY[timeend::text, fp_preferred_method] AS fp_preferred_method FROM "{eligible_couple_ucr_tablename}" ) eligible_couple GROUP BY person_case_id ) ON CONFLICT (person_case_id) DO UPDATE SET fp_current_method_history = EXCLUDED.fp_current_method_history, fp_preferred_method_history = EXCLUDED.fp_preferred_method_history, family_planning_form_history = EXCLUDED.family_planning_form_history """.format( woman_history_tablename=cls._meta.db_table, eligible_couple_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_eligible_couple_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-eligible_couple_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{woman_tablename}" AS woman SET fp_current_method_ranges = eligible_couple_fp.fp_current_method_ranges FROM ( SELECT person_case_id, array_agg(fp_current_method_range) AS fp_current_method_ranges FROM ( SELECT person_case_id, fp_current_method, daterange(timeend::date, next_timeend::date) AS fp_current_method_range FROM ( SELECT person_case_id, fp_current_method, timeend, LEAD(fp_current_method) OVER w AS next_fp_current_method, LEAD(timeend) OVER w AS next_timeend FROM "{eligible_couple_ucr_tablename}" WINDOW w AS (PARTITION BY person_case_id ORDER BY timeend DESC) ) AS _tmp_table ) eligible_couple WHERE fp_current_method != 'none' GROUP BY person_case_id ) AS eligible_couple_fp WHERE woman.person_case_id = eligible_couple_fp.person_case_id """.format( woman_tablename=cls._meta.db_table, eligible_couple_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_delivery_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-delivery_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET ccs_record_case_id = delivery_forms.ccs_record_case_id, child_cried = delivery_forms.child_cried FROM ( SELECT child_health_case_id, LAST_VALUE(ccs_record_case_id) OVER w AS ccs_record_case_id, LAST_VALUE(child_cried) OVER w as child_cried FROM "{delivery_form_ucr_tablename}" WINDOW w AS (PARTITION BY child_health_case_id ORDER BY timeend DESC) ) AS delivery_forms WHERE child.child_health_case_id = delivery_forms.child_health_case_id """.format( child_tablename=cls._meta.db_table, delivery_form_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def agg_from_tasks_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-tasks_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) product_codes = ', '.join("'{}'".format(code) for code in PRODUCT_CODES) column_names = ', '.join('due_list_date_{}'.format(code) for code in PRODUCT_CODES) return """ UPDATE "{child_tablename}" AS child SET tasks_case_id = tasks.doc_id, last_immunization_type = tasks.last_immunization_type, last_immunization_date = tasks.last_immunization_date FROM ( SELECT doc_id AS doc_id, parent_case_id AS parent_case_id, LAST_VALUE(product_code) OVER w AS last_immunization_type, LAST_VALUE(product_date) OVER w AS last_immunization_date FROM ( SELECT doc_id, parent_case_id, unnest(array[{product_codes}]) AS product_code, unnest(array[{column_names}]) AS product_date FROM "{tasks_cases_ucr_tablename}" WHERE tasks_type = 'child' ) AS _tasks WHERE product_date != '1970-01-01' WINDOW w AS (PARTITION BY doc_id, parent_case_id ORDER BY product_date DESC) ) tasks WHERE child.child_health_case_id = tasks.parent_case_id """.format( child_tablename=cls._meta.db_table, tasks_cases_ucr_tablename=ucr_tablename, product_codes=product_codes, column_names=column_names, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def _init_table(cls, data_source_id): datasource_id = StaticDataSourceConfiguration.get_doc_id(cls.domain, data_source_id) datasource = StaticDataSourceConfiguration.by_id(datasource_id) adapter = get_indicator_adapter(datasource) adapter.build_table() cls.adapters.append(adapter) return adapter
def agg_from_ccs_record_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-ccs_record_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{woman_tablename}" AS woman SET pregnant_ranges = ccs_record.pregnant_ranges FROM ( SELECT person_case_id, array_agg(pregnant_range) as pregnant_ranges FROM( SELECT person_case_id, daterange(opened_on::date, add, '[]') as pregnant_range FROM "{ccs_record_cases_ucr_tablename}" WHERE opened_on < add OR add IS NULL GROUP BY person_case_id, pregnant_range ) AS _tmp_table GROUP BY person_case_id ) ccs_record WHERE woman.person_case_id = ccs_record.person_case_id """.format( woman_tablename=cls._meta.db_table, ccs_record_cases_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def agg_from_person_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-person_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET household_case_id = person.household_case_id, dob = person.dob, sex = person.sex, migration_status = person.migration_status, has_aadhar_number = person.has_aadhar_number, contact_phone_number = person.contact_phone_number FROM ( SELECT household_case_id, doc_id, dob, sex, migration_status, aadhar_number IS NOT NULL and aadhar_number != '' AS has_aadhar_number, contact_phone_number FROM "{person_cases_ucr_tablename}" ) person WHERE child.person_case_id = person.doc_id """.format( child_tablename=cls._meta.db_table, person_cases_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def agg_from_household_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-household_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET awc_id = household.awc_owner_id, village_id = household.village_owner_id, hh_address = household.hh_address, hh_religion = household.hh_religion, hh_caste = household.hh_caste, hh_bpl_apl = household.hh_bpl_apl FROM ( SELECT doc_id, awc_owner_id, village_owner_id, hh_address, hh_religion, hh_caste, hh_bpl_apl FROM "{household_cases_ucr_tablename}" ) household WHERE child.household_case_id = household.doc_id """.format( child_tablename=cls._meta.db_table, household_cases_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def agg_from_village_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-village_location') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET sc_id = village.sc_id, phc_id = village.phc_id, taluka_id = village.taluka_id, district_id = village.district_id, state_id = village.state_id FROM ( SELECT doc_id, sc_id, phc_id, taluka_id, district_id, state_id FROM "{village_location_ucr_tablename}" ) village WHERE child.village_id = village.doc_id """.format( child_tablename=cls._meta.db_table, village_location_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def handle(self, file_path, *args, **options): domain = 'icds-cas' data_source_id = StaticDataSourceConfiguration.get_doc_id(domain, PERSON_TABLE_ID) config = StaticDataSourceConfiguration.by_id(data_source_id) adapter = get_indicator_adapter(config) session_helper = connection_manager.get_session_helper(adapter.engine_id) person_table_name = get_table_name(domain, PERSON_TABLE_ID) awc_location_table_name = get_table_name(domain, AWC_LOCATION_TABLE_ID) session = session_helper.Session with open( os.path.join(os.path.dirname(__file__), 'sql_scripts', 'nos_of_deaths.sql'), encoding='utf-8' ) as f: sql_script = f.read() rows = session.execute( sql_script % { 'person_table_name': person_table_name, 'awc_location_table_name': awc_location_table_name } ) with open(file_path, 'w', encoding='utf-8') as file_object: writer = csv.writer(file_object) writer.writerow([ 'State', 'District', 'AWC', 'Month', 'Deaths', ]) writer.writerows(rows)
def agg_from_tasks_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-tasks_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) product_codes = ', '.join("'{}'".format(code) for code in PRODUCT_CODES) column_names = ', '.join('due_list_date_{}'.format(code) for code in PRODUCT_CODES) return """ UPDATE "{child_tablename}" AS child SET tasks_case_id = tasks.doc_id, last_immunization_type = tasks.last_immunization_type, last_immunization_date = tasks.last_immunization_date FROM ( SELECT doc_id AS doc_id, parent_case_id AS parent_case_id, LAST_VALUE(product_code) OVER w AS last_immunization_type, LAST_VALUE(product_date) OVER w AS last_immunization_date FROM ( SELECT doc_id, parent_case_id, unnest(array[{product_codes}]) AS product_code, unnest(array[{column_names}]) AS product_date FROM "{tasks_cases_ucr_tablename}" WHERE tasks_type = 'child' ) AS _tasks WHERE product_date != '1970-01-01' WINDOW w AS (PARTITION BY doc_id, parent_case_id ORDER BY product_date DESC) ) tasks WHERE child.child_health_case_id = tasks.parent_case_id """.format( child_tablename=cls._meta.db_table, tasks_cases_ucr_tablename=ucr_tablename, product_codes=product_codes, column_names=column_names, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def _make_data_source_for_domain(data_source_json, domain_name): from corehq.apps.userreports.models import StaticDataSourceConfiguration from corehq.apps.userreports.models import DataSourceConfiguration doc = deepcopy(data_source_json) doc['domain'] = domain_name doc['_id'] = StaticDataSourceConfiguration.get_doc_id(domain_name, doc['table_id']) return DataSourceConfiguration.wrap(doc)
def _make_data_source_for_domain(data_source_json, domain_name): from corehq.apps.userreports.models import StaticDataSourceConfiguration from corehq.apps.userreports.models import DataSourceConfiguration doc = deepcopy(data_source_json) doc['domain'] = domain_name doc['_id'] = StaticDataSourceConfiguration.get_doc_id(domain_name, doc['table_id']) return DataSourceConfiguration.wrap(doc)
def test_doc_filter_mapping(self, doc_type, data_source_id): config_id = StaticDataSourceConfiguration.get_doc_id( DASHBOARD_DOMAIN, data_source_id) config = StaticDataSourceConfiguration.by_id(config_id) doc_filters = UCR_MAPPING[doc_type][data_source_id] self.assertEqual(doc_type, config.referenced_doc_type) self.assertEqual(set(doc_filters), set(config.get_case_type_or_xmlns_filter()))
def agg_from_person_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-person_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{woman_tablename}" AS child ( domain, household_case_id, person_case_id, opened_on, closed_on, name, dob, marital_status, sex, migration_status, age_marriage, has_aadhar_number, husband_name, contact_phone_number, num_male_children_died, num_female_children_died, blood_group ) ( SELECT %(domain)s, household_case_id, doc_id, opened_on, closed_on, name, dob, marital_status, sex, migration_status, age_marriage, aadhar_number IS NOT NULL and aadhar_number != '' AS has_aadhar_number, husband_name, contact_phone_number, num_male_children_died, num_female_children_died, blood_group FROM "{person_cases_ucr_tablename}" person WHERE sex = 'F' AND date_part('year', age(dob)) BETWEEN 15 AND 49 ) ON CONFLICT (person_case_id) DO UPDATE SET closed_on = EXCLUDED.closed_on, name = EXCLUDED.name, dob = EXCLUDED.dob, marital_status = EXCLUDED.marital_status, sex = EXCLUDED.sex, migration_status = EXCLUDED.migration_status, age_marriage = EXCLUDED.age_marriage, has_aadhar_number = EXCLUDED.has_aadhar_number, husband_name = EXCLUDED.husband_name, contact_phone_number = EXCLUDED.contact_phone_number, num_male_children_died = EXCLUDED.num_male_children_died, num_female_children_died = EXCLUDED.num_female_children_died, blood_group = EXCLUDED.blood_group """.format( woman_tablename=cls._meta.db_table, person_cases_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def agg_from_person_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-person_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{woman_tablename}" AS child ( domain, household_case_id, person_case_id, opened_on, closed_on, name, dob, marital_status, sex, migration_status, age_marriage, has_aadhar_number, husband_name, contact_phone_number, num_male_children_died, num_female_children_died, blood_group ) ( SELECT %(domain)s, household_case_id, doc_id, opened_on, closed_on, name, dob, marital_status, sex, migration_status, age_marriage, aadhar_number IS NOT NULL and aadhar_number != '' AS has_aadhar_number, husband_name, contact_phone_number, num_male_children_died, num_female_children_died, blood_group FROM "{person_cases_ucr_tablename}" person WHERE sex = 'F' AND date_part('year', age(dob)) BETWEEN 15 AND 49 ) ON CONFLICT (person_case_id) DO UPDATE SET closed_on = EXCLUDED.closed_on, name = EXCLUDED.name, dob = EXCLUDED.dob, marital_status = EXCLUDED.marital_status, sex = EXCLUDED.sex, migration_status = EXCLUDED.migration_status, age_marriage = EXCLUDED.age_marriage, has_aadhar_number = EXCLUDED.has_aadhar_number, husband_name = EXCLUDED.husband_name, contact_phone_number = EXCLUDED.contact_phone_number, num_male_children_died = EXCLUDED.num_male_children_died, num_female_children_died = EXCLUDED.num_female_children_died, blood_group = EXCLUDED.blood_group """.format( woman_tablename=cls._meta.db_table, person_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_person_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-person_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET household_case_id = person.household_case_id FROM ( SELECT doc_id, household_case_id FROM "{person_cases_ucr_tablename}" ) person WHERE child.person_case_id = person.doc_id """.format( child_tablename=cls._meta.db_table, person_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_person_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-person_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET household_case_id = person.household_case_id FROM ( SELECT doc_id, household_case_id FROM "{person_cases_ucr_tablename}" ) person WHERE child.person_case_id = person.doc_id """.format( child_tablename=cls._meta.db_table, person_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def copy_ucr_datasources(self): datasource_map = {} datasources = get_datasources_for_domain(self.existing_domain) for datasource in datasources: datasource.meta.build.finished = False datasource.meta.build.initiated = None old_id, new_id = self.save_couch_copy(datasource, self.new_domain) datasource_map[old_id] = new_id for static_datasource in StaticDataSourceConfiguration.by_domain(self.existing_domain): table_id = static_datasource.get_id.replace( StaticDataSourceConfiguration._datasource_id_prefix + self.existing_domain + "-", "" ) new_id = StaticDataSourceConfiguration.get_doc_id(self.new_domain, table_id) # check that new datasource is in new domain's list of static datasources StaticDataSourceConfiguration.by_id(new_id) datasource_map[static_datasource.get_id] = new_id return datasource_map
def agg_from_awc_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-awc_location') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET supervisor_id = awc.supervisor_id, block_id = awc.block_id FROM ( SELECT doc_id, supervisor_id, block_id FROM "{awc_location_ucr_tablename}" ) awc WHERE child.awc_id = awc.doc_id """.format( child_tablename=cls._meta.db_table, awc_location_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_ccs_record_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-ccs_record_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{ccs_record_tablename}" AS ccs_record ( domain, person_case_id, ccs_record_case_id, opened_on, closed_on, hrp, child_birth_location, add, edd, lmp, preg_reg_date, woman_weight_at_preg_reg, pnc1_date, pnc2_date, pnc3_date, pnc4_date ) ( SELECT %(domain)s, person_case_id, doc_id, opened_on, closed_on, hrp, child_birth_location, add, edd, lmp, preg_reg_date, woman_weight_at_preg_reg, pnc1_date, pnc2_date, pnc3_date, pnc4_date FROM "{ccs_record_cases_ucr_tablename}" ccs_record_ucr ) ON CONFLICT (ccs_record_case_id) DO UPDATE SET closed_on = EXCLUDED.closed_on, hrp = EXCLUDED.hrp, child_birth_location = EXCLUDED.child_birth_location, add = EXCLUDED.add, edd = EXCLUDED.edd, lmp = EXCLUDED.lmp, preg_reg_date = EXCLUDED.preg_reg_date, woman_weight_at_preg_reg = EXCLUDED.woman_weight_at_preg_reg, pnc1_date = EXCLUDED.pnc1_date, pnc2_date = EXCLUDED.pnc2_date, pnc3_date = EXCLUDED.pnc3_date, pnc4_date = EXCLUDED.pnc4_date """.format( ccs_record_tablename=cls._meta.db_table, ccs_record_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_ccs_record_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-ccs_record_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{ccs_record_tablename}" AS ccs_record ( domain, person_case_id, ccs_record_case_id, opened_on, closed_on, hrp, child_birth_location, add, edd, lmp, preg_reg_date, woman_weight_at_preg_reg, pnc1_date, pnc2_date, pnc3_date, pnc4_date ) ( SELECT %(domain)s, person_case_id, doc_id, opened_on, closed_on, hrp, child_birth_location, add, edd, lmp, preg_reg_date, woman_weight_at_preg_reg, pnc1_date, pnc2_date, pnc3_date, pnc4_date FROM "{ccs_record_cases_ucr_tablename}" ccs_record_ucr ) ON CONFLICT (ccs_record_case_id) DO UPDATE SET closed_on = EXCLUDED.closed_on, hrp = EXCLUDED.hrp, child_birth_location = EXCLUDED.child_birth_location, add = EXCLUDED.add, edd = EXCLUDED.edd, lmp = EXCLUDED.lmp, preg_reg_date = EXCLUDED.preg_reg_date, woman_weight_at_preg_reg = EXCLUDED.woman_weight_at_preg_reg, pnc1_date = EXCLUDED.pnc1_date, pnc2_date = EXCLUDED.pnc2_date, pnc3_date = EXCLUDED.pnc3_date, pnc4_date = EXCLUDED.pnc4_date """.format( ccs_record_tablename=cls._meta.db_table, ccs_record_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_growth_monitoring_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-growth_monitoring_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{child_history_tablename}" AS child ( child_health_case_id, weight_child_history, height_child_history, zscore_grading_wfa_history, zscore_grading_hfa_history, zscore_grading_wfh_history ) ( SELECT child_health_case_id, array_agg(weight_child) AS weight_child_history, array_agg(height_child) AS height_child_history, array_agg(zscore_grading_wfh) AS zscore_grading_wfh_history, array_agg(zscore_grading_hfa) AS zscore_grading_hfa_history, array_agg(zscore_grading_wfa) AS zscore_grading_wfa_history FROM ( SELECT child_health_case_id, ARRAY[timeend::text, weight_child::text] AS weight_child, ARRAY[timeend::text, height_child::text] AS height_child, ARRAY[timeend::text, zscore_grading_wfh] AS zscore_grading_wfh, ARRAY[timeend::text, zscore_grading_hfa] AS zscore_grading_hfa, ARRAY[timeend::text, zscore_grading_wfa] AS zscore_grading_wfa FROM "{growth_monitoring_ucr_tablename}" ) growth_monitoring GROUP BY child_health_case_id ) ON CONFLICT (child_health_case_id) DO UPDATE SET weight_child_history = EXCLUDED.weight_child_history, height_child_history = EXCLUDED.height_child_history, zscore_grading_wfh_history = EXCLUDED.zscore_grading_wfh_history, zscore_grading_hfa_history = EXCLUDED.zscore_grading_hfa_history, zscore_grading_wfa_history = EXCLUDED.zscore_grading_wfa_history """.format( child_history_tablename=cls._meta.db_table, growth_monitoring_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def copy_ucr_datasources(self): datasource_map = {} datasources = get_datasources_for_domain(self.existing_domain) for datasource in datasources: datasource.meta.build.finished = False datasource.meta.build.initiated = None old_id, new_id = self.save_couch_copy(datasource, self.new_domain) datasource_map[old_id] = new_id for static_datasource in StaticDataSourceConfiguration.by_domain(self.existing_domain): table_id = static_datasource.get_id.replace( StaticDataSourceConfiguration._datasource_id_prefix + self.existing_domain + '-', '' ) new_id = StaticDataSourceConfiguration.get_doc_id(self.new_domain, table_id) # check that new datasource is in new domain's list of static datasources StaticDataSourceConfiguration.by_id(new_id) datasource_map[static_datasource.get_id] = new_id return datasource_map
def agg_from_birth_preparedness_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-birth_preparedness') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{ccs_record_tablename}" AS ccs_record SET num_anc_checkups = bp_forms.num_anc_checkups FROM ( SELECT ccs_record_case_id, COUNT(*) as num_anc_checkups FROM "{bp_forms_ucr_tablename}" WHERE ccs_record_case_id IS NOT NULL GROUP BY ccs_record_case_id ) bp_forms WHERE ccs_record.ccs_record_case_id = bp_forms.ccs_record_case_id """.format( ccs_record_tablename=cls._meta.db_table, bp_forms_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_birth_preparedness_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-birth_preparedness') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{ccs_record_tablename}" AS ccs_record SET num_anc_checkups = bp_forms.num_anc_checkups FROM ( SELECT ccs_record_case_id, COUNT(*) as num_anc_checkups FROM "{bp_forms_ucr_tablename}" WHERE ccs_record_case_id IS NOT NULL GROUP BY ccs_record_case_id ) bp_forms WHERE ccs_record.ccs_record_case_id = bp_forms.ccs_record_case_id """.format( ccs_record_tablename=cls._meta.db_table, bp_forms_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_delivery_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-delivery_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET ccs_record_case_id = delivery_forms.ccs_record_case_id, child_cried = delivery_forms.child_cried FROM ( SELECT child_health_case_id, LAST_VALUE(ccs_record_case_id) OVER w AS ccs_record_case_id, LAST_VALUE(child_cried) OVER w as child_cried FROM "{delivery_form_ucr_tablename}" WINDOW w AS (PARTITION BY child_health_case_id ORDER BY timeend DESC) ) AS delivery_forms WHERE child.child_health_case_id = delivery_forms.child_health_case_id """.format( child_tablename=cls._meta.db_table, delivery_form_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_tasks_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id( domain, 'reach-child_tasks_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{child_tablename}" AS child SET tasks_case_id = tasks.doc_id FROM ( SELECT doc_id, child_health_case_id FROM "{tasks_cases_ucr_tablename}" ) tasks WHERE child.child_health_case_id = tasks.child_health_case_id """.format( child_tablename=cls._meta.db_table, tasks_cases_ucr_tablename=ucr_tablename, ), { 'domain': domain, 'window_start': window_start, 'window_end': window_end }
def compute_awws_in_vhnd_timeframe(domain): """ This computes awws with vhsnd_date_past_month less than 37 days. Result is cached in local memory, so that indvidual reminder tasks per AWW/LS dont hit the database each time """ table = get_table_name(domain, 'static-vhnd_form') query = """ SELECT DISTINCT awc_id FROM "{table}" WHERE vhsnd_date_past_month > %(37_days_ago)s """.format(table=table) cutoff = datetime.now(tz=pytz.timezone('Asia/Kolkata')).date() query_params = {"37_days_ago": cutoff - timedelta(days=37)} datasource_id = StaticDataSourceConfiguration.get_doc_id(domain, 'static-vhnd_form') data_source = StaticDataSourceConfiguration.by_id(datasource_id) django_db = connection_manager.get_django_db_alias(data_source.engine_id) with connections[django_db].cursor() as cursor: cursor.execute(query, query_params) return {row[0] for row in cursor.fetchall()}
def agg_from_growth_monitoring_forms_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-growth_monitoring_forms') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ INSERT INTO "{child_history_tablename}" AS child ( child_health_case_id, weight_child_history, height_child_history, zscore_grading_wfa_history, zscore_grading_hfa_history, zscore_grading_wfh_history ) ( SELECT child_health_case_id, array_agg(weight_child) AS weight_child_history, array_agg(height_child) AS height_child_history, array_agg(zscore_grading_wfh) AS zscore_grading_wfh_history, array_agg(zscore_grading_hfa) AS zscore_grading_hfa_history, array_agg(zscore_grading_wfa) AS zscore_grading_wfa_history FROM ( SELECT child_health_case_id, ARRAY[timeend::text, weight_child::text] AS weight_child, ARRAY[timeend::text, height_child::text] AS height_child, ARRAY[timeend::text, zscore_grading_wfh] AS zscore_grading_wfh, ARRAY[timeend::text, zscore_grading_hfa] AS zscore_grading_hfa, ARRAY[timeend::text, zscore_grading_wfa] AS zscore_grading_wfa FROM "{growth_monitoring_ucr_tablename}" ) growth_monitoring GROUP BY child_health_case_id ) ON CONFLICT (child_health_case_id) DO UPDATE SET weight_child_history = EXCLUDED.weight_child_history, height_child_history = EXCLUDED.height_child_history, zscore_grading_wfh_history = EXCLUDED.zscore_grading_wfh_history, zscore_grading_hfa_history = EXCLUDED.zscore_grading_hfa_history, zscore_grading_wfa_history = EXCLUDED.zscore_grading_wfa_history """.format( child_history_tablename=cls._meta.db_table, growth_monitoring_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def agg_from_ccs_record_case_ucr(cls, domain, window_start, window_end): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, 'reach-ccs_record_cases') config, _ = get_datasource_config(doc_id, domain) ucr_tablename = get_table_name(domain, config.table_id) return """ UPDATE "{woman_tablename}" AS woman SET pregnant_ranges = ccs_record.pregnant_ranges FROM ( SELECT person_case_id, array_agg(pregnant_range) as pregnant_ranges FROM ( SELECT person_case_id, daterange(opened_on::date, add, '[]') as pregnant_range FROM "{ccs_record_cases_ucr_tablename}" WHERE opened_on < add OR add IS NULL GROUP BY person_case_id, pregnant_range ) AS _tmp_table GROUP BY person_case_id ) ccs_record WHERE woman.person_case_id = ccs_record.person_case_id """.format( woman_tablename=cls._meta.db_table, ccs_record_cases_ucr_tablename=ucr_tablename, ), {'domain': domain, 'window_start': window_start, 'window_end': window_end}
def get_asha_table_name(self): config = StaticDataSourceConfiguration.by_id( StaticDataSourceConfiguration.get_doc_id(DOMAIN, TABLE_ID) ) return get_table_name(config.domain, config.table_id)
def _get_adapter(cls, data_source_id): datasource_id = StaticDataSourceConfiguration.get_doc_id(cls.domain, data_source_id) datasource = StaticDataSourceConfiguration.by_id(datasource_id) return get_indicator_adapter(datasource)
def _ucr_eligible_couple_table(cls, domain): doc_id = StaticDataSourceConfiguration.get_doc_id(domain, cls.ucr_table) config, _ = get_datasource_config(doc_id, domain) return get_table_name(domain, config.table_id)
def _ucr_tablename(self, ucr_id): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, ucr_id) config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def person_case_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, 'static-person_cases_v3') config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def pregnant_tasks_cases_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, 'static-pregnant-tasks_cases') config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def ccs_record_monthly_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, self.ccs_record_monthly_ucr_id) config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def ucr_daily_attendance_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, self.ucr_daily_attendance_table) config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def get_asha_table(self, metadata): config = StaticDataSourceConfiguration.by_id( StaticDataSourceConfiguration.get_doc_id(DOMAIN, TABLE_ID) ) return get_indicator_table(config, custom_metadata=metadata)
def ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, self.ucr_data_source_id) config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def child_health_case_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, 'static-child_health_cases') config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def _old_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, self.ccs_record_monthly_ucr_id) config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def ccs_record_monthly_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, 'static-ccs_record_cases_monthly_tableau_v2') config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def _rebuild_datasource(domain): config_id = StaticDataSourceConfiguration.get_doc_id( domain, 'food_consumption_indicators') rebuild_indicators(config_id, source='populate_inddex_test_domain')
def person_case_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id( self.domain, 'static-person_cases_v3') config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def child_health_case_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id(self.domain, 'static-child_health_cases') config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def _get_adapter(cls, data_source_id): datasource_id = StaticDataSourceConfiguration.get_doc_id( cls.domain, data_source_id) datasource = StaticDataSourceConfiguration.by_id(datasource_id) return get_indicator_adapter(datasource)