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 get_indicator_table(indicator_config, metadata, override_table_name=None): sql_columns = [column_to_sql(col) for col in indicator_config.get_columns()] table_name = override_table_name or get_table_name(indicator_config.domain, indicator_config.table_id) columns_by_col_id = {col.database_column_name.decode('utf-8') for col in indicator_config.get_columns()} extra_indices = [] for index in indicator_config.sql_column_indexes: if set(index.column_ids).issubset(columns_by_col_id): extra_indices.append(Index( _custom_index_name(table_name, index.column_ids), *index.column_ids )) else: _assert = soft_assert('{}@{}'.format('jemord', 'dimagi.com')) _assert(False, "Invalid index specified on {}".format(table_name)) break constraints = [PrimaryKeyConstraint(*indicator_config.pk_columns)] columns_and_indices = sql_columns + extra_indices + constraints # todo: needed to add extend_existing=True to support multiple calls to this function for the same table. # is that valid? return sqlalchemy.Table( table_name, metadata, extend_existing=True, *columns_and_indices )
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_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_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 test_add_nullable_column(self): self._setup_data_source('add_nullable_col') # assert new date isn't in the config insp = reflection.Inspector.from_engine(self.engine) table_name = get_table_name(self.config.domain, self.config.table_id) self.assertEqual( len([c for c in insp.get_columns(table_name) if c['name'] == 'new_date']), 0 ) # add the column to the config config = self._get_config('add_nullable_col') self.addCleanup(config.delete) config.configured_indicators.append({ "column_id": "new_date", "type": "raw", "display_name": "new_date opened", "datatype": "datetime", "property_name": "other_opened_on", "is_nullable": True }) config.save() adapter = get_indicator_adapter(config) engine = adapter.engine # mock rebuild table to ensure the column is added without rebuild table pillow = get_case_pillow(ucr_configs=[config]) pillow.processors[0].rebuild_table = mock.MagicMock() self.assertFalse(pillow.processors[0].rebuild_table.called) insp = reflection.Inspector.from_engine(engine) self.assertEqual( len([c for c in insp.get_columns(table_name) if c['name'] == 'new_date']), 1 )
def setUpModule(): if isinstance(Domain.get_db(), Mock): # needed to skip setUp for javascript tests thread on Travis return _call_center_domain_mock = mock.patch( 'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider' ) _call_center_domain_mock.start() domain = create_domain('champ-cameroon') with override_settings(SERVER_ENVIRONMENT='production'): configs = StaticDataSourceConfiguration.by_domain(domain.name) adapters = [get_indicator_adapter(config) for config in configs] for adapter in adapters: adapter.build_table() engine = connection_manager.get_engine(UCR_ENGINE_ID) metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) path = os.path.join(os.path.dirname(__file__), 'fixtures') for file_name in os.listdir(path): with open(os.path.join(path, file_name), encoding='utf-8') as f: table_name = get_table_name(domain.name, file_name[:-4]) table = metadata.tables[table_name] postgres_copy.copy_from( f, table, engine, format='csv' if six.PY3 else b'csv', null='' if six.PY3 else b'', header=True ) _call_center_domain_mock.stop()
def _get_tables_by_engine_id(self, data_sources, engine_id): tables_by_engine_id = defaultdict(set) for data_source in data_sources: if engine_id and data_source.engine_id != engine_id: continue table_name = get_table_name(data_source.domain, data_source.table_id) tables_by_engine_id[data_source.engine_id].add(table_name) return tables_by_engine_id
def get_indicator_table(indicator_config, custom_metadata=None): sql_columns = [column_to_sql(col) for col in indicator_config.get_columns()] table_name = get_table_name(indicator_config.domain, indicator_config.table_id) # todo: needed to add extend_existing=True to support multiple calls to this function for the same table. # is that valid? return sqlalchemy.Table( table_name, custom_metadata or metadata, extend_existing=True, *sql_columns )
def _assert_rows_in_ucr_table(self, config, rows): results = list(load_data_from_db(get_table_name(self.domain.name, config.table_id))) if not rows: self.assertEqual(results, []) return actual_rows = [{key: r[key] for key in rows[0]} for r in results] self.assertEqual( sorted(rows, key=lambda x: x['doc_id']), sorted(actual_rows, key=lambda x: x['doc_id']) )
def _update_ucr_table_mapping(): celery_task_logger.info("Started updating ucr_table_name_mapping table") for table in UCR_TABLE_NAME_MAPPING: if table.get('is_ucr', True): table_name = get_table_name(DASHBOARD_DOMAIN, table['name']) else: table_name = table['name'] UcrTableNameMapping.objects.update_or_create( table_type=table['type'], defaults={'table_name': table_name} ) celery_task_logger.info("Ended updating ucr_table_name_mapping table")
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 clean_table_id(self): # todo: validate table_id as [a-z][a-z0-9_]* table_id = self.cleaned_data['table_id'] table_name = get_table_name(self.domain, table_id) if len(table_name) > 63: # max table name length for postgres raise ValidationError( _('Table id is too long. Your table id and domain name must add up to fewer than 40 characters') ) for src in self.instance.by_domain(self.domain): if src.table_id == table_id and src.get_id != self.instance.get_id: raise ValidationError( _('A data source with this table id already exists. Table' ' ids must be unique') ) return table_id
def _assert_migrated(apps, schema_editor): if settings.UNIT_TESTING: return for engine_id, data_sources in _data_sources_by_engine_id().items(): with connection_manager.get_engine(engine_id).begin() as conn: for data_source in data_sources: legacy_table_name = get_legacy_table_name(data_source) new_table_name = get_table_name(data_source.domain, data_source.table_id) if ( table_exists(conn, legacy_table_name) and not table_exists(conn, new_table_name) ): print("") print(AUTO_MIGRATE_FAILED_MESSAGE) sys.exit(1)
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_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_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 tearDownModule(): if isinstance(Domain.get_db(), Mock): # needed to skip setUp for javascript tests thread on Travis return _call_center_domain_mock = mock.patch( 'corehq.apps.callcenter.data_source.call_center_data_source_configuration_provider' ) domain = Domain.get_by_name('test-pna') engine = connection_manager.get_engine(UCR_ENGINE_ID) metadata = sqlalchemy.MetaData(bind=engine) metadata.reflect(bind=engine, extend_existing=True) path = os.path.join(os.path.dirname(__file__), 'fixtures') for file_name in os.listdir(path): table_name = get_table_name(domain.name, file_name[:-4]) table = metadata.tables[table_name] table.drop() _call_center_domain_mock.start() domain.delete() _call_center_domain_mock.stop()
def test_add_non_nullable_column(self): self._setup_data_source('add_non_nullable_col') # assert new date isn't in the config insp = reflection.Inspector.from_engine(self.engine) table_name = get_table_name(self.config.domain, self.config.table_id) self.assertEqual( len([c for c in insp.get_columns(table_name) if c['name'] == 'new_date']), 0 ) # add the column to the config config = self._get_config('add_non_nullable_col') self.addCleanup(config.delete) config.configured_indicators.append({ "column_id": "new_date", "type": "raw", "display_name": "new_date opened", "datatype": "datetime", "property_name": "other_opened_on", "is_nullable": False }) config.save() adapter = get_indicator_adapter(config) engine = adapter.engine # mock rebuild table to ensure the table is rebuilt with mock.patch('corehq.apps.userreports.pillow.ConfigurableReportPillowProcessor.rebuild_table'): pillow = get_case_pillow(ucr_configs=[config]) self.assertTrue(pillow.processors[0].rebuild_table.called) # column doesn't exist because rebuild table was mocked insp = reflection.Inspector.from_engine(engine) self.assertEqual( len([c for c in insp.get_columns(table_name) if c['name'] == 'new_date']), 0 ) # Another time without the mock to ensure the column is there pillow = get_case_pillow(ucr_configs=[config]) insp = reflection.Inspector.from_engine(engine) self.assertEqual( len([c for c in insp.get_columns(table_name) if c['name'] == 'new_date']), 1 )
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_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 test_add_index(self): # build the table without an index self._setup_data_source('add_index') insp = reflection.Inspector.from_engine(self.engine) table_name = get_table_name(self.config.domain, self.config.table_id) self.assertEqual(len(insp.get_indexes(table_name)), 0) # add the index to the config config = self._get_config('add_index') self.addCleanup(config.delete) config.configured_indicators[0]['create_index'] = True config.save() adapter = get_indicator_adapter(config) # mock rebuild table to ensure the table isn't rebuilt when adding index pillow = get_case_pillow(ucr_configs=[config]) pillow.processors[0].rebuild_table = mock.MagicMock() pillow.processors[0].bootstrap([config]) self.assertFalse(pillow.processors[0].rebuild_table.called) engine = adapter.engine insp = reflection.Inspector.from_engine(engine) self.assertEqual(len(insp.get_indexes(table_name)), 1)
) from custom.aaa.tasks import ( update_agg_awc_table, update_agg_village_table, update_ccs_record_table, update_child_table, update_child_history_table, update_woman_table, update_woman_history_table, ) from custom.icds_reports.tests import CSVTestCase from six.moves import range FILE_NAME_TO_TABLE_MAPPING = { 'awc': get_table_name('reach-test', 'reach-awc_location'), 'ccs_record': get_table_name('reach-test', 'reach-ccs_record_cases'), 'child_health': get_table_name('reach-test', 'reach-child_health_cases'), 'eligible_couple_forms': get_table_name('reach-test', 'reach-eligible_couple_forms'), 'growth_monitoring': get_table_name('reach-test', 'reach-growth_monitoring_forms'), 'household': get_table_name('reach-test', 'reach-household_cases'), 'person': get_table_name('reach-test', 'reach-person_cases'), 'tasks': get_table_name('reach-test', 'reach-tasks_cases'), 'village':
def table_name(self): return get_table_name(self.config.domain, TABLE_ID)
from django.core.management.base import BaseCommand from django.db import connections from corehq.apps.userreports.util import get_table_name from corehq.sql_db.connections import ICDS_UCR_CITUS_ENGINE_ID new_table = get_table_name('icds-cas', 'static-child_cases_monthly_v2') old_table = get_table_name('icds-cas', 'static-child_cases_monthly_tableau_v2') migration_query = """ UPDATE "{new_table}" B SET pse_daily_attendance = A.pse_days_attended, pse_daily_attendance_male = CASE WHEN sex = 'M' THEN A.pse_days_attended ELSE NULL END, pse_daily_attendance_female = CASE WHEN sex = 'F' THEN A.pse_days_attended ELSE NULL END FROM "{old_table}" A WHERE A.doc_id = B.doc_id and A.month = immutable_date_cast(B.month_start) """.format(new_table=new_table, old_table=old_table) class Command(BaseCommand): def add_arguments(self, parser): parser.add_argument('--check', action='store_true', dest='check', default=False, help="Print query and output from EXPLAIN") def handle(self, check, **options): with connections[ICDS_UCR_CITUS_ENGINE_ID].cursor() as cursor:
def table_name(self): return get_table_name(self.config['domain'], "site_reporting_rates_report")
def table_name(self): return get_table_name(self.config['domain'], self.slug)
def ccs_record_case_ucr_tablename(self): return get_table_name(self.domain, 'static-ccs_record_cases')
def person_case_ucr_tablename(self): return get_table_name(self.domain, 'static-person_cases_v3')
def get_table(self, table_id): if not is_current_month( self.month_start) and ICDS_LOCATION_REASSIGNMENT_AGG.enabled( self.domain): return get_prev_agg_tablename(table_id) return get_table_name(self.domain, table_id)
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, supervisor_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, supervisor_id ) ut WHERE ut.month = agg_awc.month AND ut.awc_id = agg_awc.awc_id and agg_awc.supervisor_id=ut.supervisor_id; """.format( tablename=self.temporary_tablename, daily_attendance='daily_attendance', ), { 'start_date': self.month_start } # MAKE SURE YOU DID NOT RUIN PERFORMANCE yield """ UPDATE "{tablename}" agg_awc SET cases_household = ut.cases_household, is_launched = CASE WHEN ut.all_cases_household>0 THEN 'yes' ELSE 'no' END, num_launched_states = CASE WHEN ut.all_cases_household>0 THEN 1 ELSE 0 END, num_launched_districts = CASE WHEN ut.all_cases_household>0 THEN 1 ELSE 0 END, num_launched_blocks = CASE WHEN ut.all_cases_household>0 THEN 1 ELSE 0 END, num_launched_supervisors = CASE WHEN ut.all_cases_household>0 THEN 1 ELSE 0 END, num_launched_awcs = CASE WHEN ut.all_cases_household>0 THEN 1 ELSE 0 END FROM ( SELECT awc_id, supervisor_id, sum(open_count) AS cases_household, count(*) AS all_cases_household FROM "{household_cases}" WHERE opened_on<= %(end_date)s GROUP BY awc_id, supervisor_id ) ut WHERE ut.awc_id = agg_awc.awc_id and ut.supervisor_id=agg_awc.supervisor_id; """.format(tablename=self.temporary_tablename, household_cases=self.get_table('static-household_cases')), { 'end_date': self.month_end } 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, cases_person_adolescent_girls_11_14_all_v2 = ut.cases_person_adolescent_girls_11_14_all_v2, cases_person_adolescent_girls_11_14_out_of_school=0 FROM ( SELECT ucr.awc_id, ucr.supervisor_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_11yr)s > dob AND %(month_start_14yr)s <= dob AND sex = 'F' AND (agg_migration.is_migrated IS DISTINCT FROM 1 OR agg_migration.migration_date::date >= %(start_date)s) THEN 1 ELSE 0 END ) as cases_person_adolescent_girls_11_14_all_v2, 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' AND (agg_migration.is_migrated IS DISTINCT FROM 1 OR agg_migration.migration_date::date >= %(start_date)s) 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}" ucr LEFT JOIN "{migration_table}" agg_migration ON ( ucr.doc_id = agg_migration.person_case_id AND agg_migration.month = %(start_date)s AND ucr.supervisor_id = agg_migration.supervisor_id ) LEFT JOIN "{availing_services_table}" agg_availing ON ( ucr.doc_id = agg_availing.person_case_id AND agg_availing.month = %(start_date)s AND ucr.supervisor_id = agg_availing.supervisor_id ) WHERE (opened_on <= %(end_date)s AND (closed_on IS NULL OR closed_on >= %(start_date)s )) GROUP BY ucr.supervisor_id, ucr.awc_id) ut WHERE ut.awc_id = agg_awc.awc_id and ut.supervisor_id=agg_awc.supervisor_id; """.format( tablename=self.temporary_tablename, ucr_tablename=self.get_table('static-person_cases_v3'), migration_table=AGG_MIGRATION_TABLE, availing_services_table=AGG_AVAILING_SERVICES_TABLE, seeking_services= ("CASE WHEN " "((agg_availing.is_registered IS DISTINCT FROM 0 OR agg_availing.registration_date::date >= %(start_date)s) AND " "(agg_migration.is_migrated IS DISTINCT FROM 1 OR agg_migration.migration_date::date >= %(start_date)s)) " "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_start_14yr': self.month_start_14yr, 'month_end_15yr': self.month_end_15yr, 'month_start_18yr': self.month_start_18yr, } yield """ UPDATE "{tablename}" agg_awc SET cases_person_adolescent_girls_11_14_out_of_school = ut.girls_out_of_schoool FROM ( select ucr.awc_id, ucr.supervisor_id, SUM(CASE WHEN ( (out_of_school or re_out_of_school) AND (not admitted_in_school )) THEN 1 ELSE 0 END ) as girls_out_of_schoool from "{ucr_tablename}" ucr INNER JOIN "{adolescent_girls_table}" adolescent_girls_table ON ( ucr.doc_id = adolescent_girls_table.person_case_id AND ucr.supervisor_id = adolescent_girls_table.supervisor_id AND adolescent_girls_table.month=%(start_date)s ) LEFT JOIN "{migration_table}" agg_migration ON ( ucr.doc_id = agg_migration.person_case_id AND agg_migration.month = %(start_date)s AND ucr.supervisor_id = agg_migration.supervisor_id ) WHERE (opened_on <= %(end_date)s AND (closed_on IS NULL OR closed_on >= %(start_date)s )) AND (agg_migration.is_migrated IS DISTINCT FROM 1 OR agg_migration.migration_date::date >= %(start_date)s) GROUP BY ucr.awc_id, ucr.supervisor_id )ut where agg_awc.awc_id = ut.awc_id and ut.supervisor_id=agg_awc.supervisor_id; """.format( tablename=self.temporary_tablename, ucr_tablename=self.get_table('static-person_cases_v3'), adolescent_girls_table=AGG_ADOLESCENT_GIRLS_REGISTRATION_TABLE, migration_table=AGG_MIGRATION_TABLE), { 'start_date': self.month_start, 'end_date': self.month_end } 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, supervisor_id, sum(has_aadhar_id) as child_has_aadhar, sum(immunization_in_month) AS num_children_immunized FROM "{child_health_monthly}" WHERE month = %(month)s and valid_in_month = 1 GROUP BY awc_id, supervisor_id) ut WHERE ut.awc_id = agg_awc.awc_id and ut.supervisor_id = agg_awc.supervisor_id; """.format( tablename=self.temporary_tablename, child_health_monthly=self.child_temp_tablename, ), { "month": 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, supervisor_id, sum(anc_in_month) AS num_anc_visits, sum(has_aadhar_id) AS ccs_has_aadhar FROM "{ccs_record_monthly}" WHERE month = %(month)s and (pregnant = 1 OR lactating = 1) GROUP BY awc_id, supervisor_id) ut WHERE ut.awc_id = agg_awc.awc_id and ut.supervisor_id = agg_awc.supervisor_id; """.format(tablename=self.temporary_tablename, ccs_record_monthly="ccs_record_monthly"), { "month": 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, 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, supervisor_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, 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, supervisor_id) ut WHERE ut.month = agg_awc.month AND ut.awc_id = agg_awc.awc_id AND ut.supervisor_id = agg_awc.supervisor_id; """.format( tablename=self.temporary_tablename, usage_table=get_table_name(self.domain, 'static-usage_forms'), ), { 'start_date': self.month_start } 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, toilet_facility = ut.toilet_facility, type_toilet = ut.type_toilet, preschool_kit_available = ut.preschool_kit_available, preschool_kit_usable = ut.preschool_kit_usable, infra_functional_toilet = CASE WHEN ut.toilet_facility=1 THEN ut.infra_functional_toilet ELSE 0 END, 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, awc_with_gm_devices = ut.awc_with_gm_devices FROM ( SELECT awc_id, supervisor_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_facility, type_toilet, preschool_kit_available, preschool_kit_usable, 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, CASE WHEN GREATEST(adult_scale_available, adult_scale_usable, baby_scale_available, flat_scale_available, baby_scale_usable, infantometer_usable, stadiometer_usable, 0) > 0 THEN 1 ELSE 0 END as awc_with_gm_devices FROM icds_dashboard_infrastructure_forms WHERE month = %(start_date)s ) ut WHERE ut.awc_id = agg_awc.awc_id AND ut.supervisor_id = agg_awc.supervisor_id; -- could possibly add multicol indexes to make order by faster? """.format(tablename=self.temporary_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.temporary_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, supervisor_id as supervisor_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, supervisor_id) ut WHERE ut.awc_id = agg_awc.awc_id AND ut.supervisor_id = agg_awc.supervisor_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.temporary_tablename, awc_location_tablename='awc_location', ), {} 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, wer_eligible_0_2 = ut.wer_eligible_0_2, wer_weighed_0_2 = ut.wer_weighed_0_2, cases_person_beneficiary_v2 = ut.cases_child_health, thr_eligible_child = thr_eligible, thr_rations_21_plus_distributed_child = rations_21_plus_distributed FROM ( SELECT awc_id, supervisor_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, sum(CASE WHEN age_tranche in ('0','6','12','24') THEN wer_eligible ELSE 0 END) AS wer_eligible_0_2, sum(CASE WHEN age_tranche in ('0','6','12','24') THEN nutrition_status_weighed ELSE 0 END) AS wer_weighed_0_2, sum(thr_eligible) as thr_eligible, sum(rations_21_plus_distributed) as rations_21_plus_distributed FROM {agg_child_temp_tablename} WHERE month = %(start_date)s AND aggregation_level = 5 GROUP BY awc_id, month, supervisor_id ) ut WHERE ut.month = agg_awc.month AND ut.awc_id = agg_awc.awc_id and ut.supervisor_id=agg_awc.supervisor_id; """.format( tablename=self.temporary_tablename, agg_child_temp_tablename=self.agg_child_temp_tablename, ), { 'start_date': self.month_start } yield """ DROP TABLE IF EXISTS "tmp_agg_awc_5"; CREATE UNLOGGED TABLE "tmp_agg_awc_5" AS SELECT * FROM "{temporary_tablename}"; INSERT INTO "{tablename}" (SELECT * FROM "tmp_agg_awc_5"); DROP TABLE "tmp_agg_awc_5"; """.format( tablename=self.tablename, temporary_tablename=self.temporary_tablename, ), {} yield """ DROP TABLE IF EXISTS "tmp_home_visit"; CREATE UNLOGGED TABLE "tmp_home_visit" AS 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 AND agg_cf.supervisor_id=ucr.supervisor_id ) 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; 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, num_mother_thr_21_days = ut.rations_21_plus_distributed, num_mother_thr_eligible = ut.thr_eligible, cases_person_beneficiary_v2 = ( COALESCE(cases_person_beneficiary_v2, 0) + ut.cases_ccs_pregnant + ut.cases_ccs_lactating ), cases_ccs_lactating_reg_in_month = ut.lactating_registered_in_month, cases_ccs_pregnant_reg_in_month = ut.pregnant_registered_in_month, cases_ccs_lactating_all_reg_in_month = ut.lactating_all_registered_in_month, cases_ccs_pregnant_all_reg_in_month = ut.pregnant_all_registered_in_month, 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.lactating_registered_in_month) as lactating_registered_in_month, sum(agg_ccs_record_monthly.pregnant_registered_in_month) as pregnant_registered_in_month, sum(agg_ccs_record_monthly.lactating_all_registered_in_month) as lactating_all_registered_in_month, sum(agg_ccs_record_monthly.pregnant_all_registered_in_month) as pregnant_all_registered_in_month, sum(agg_ccs_record_monthly.rations_21_plus_distributed) AS rations_21_plus_distributed, sum(agg_ccs_record_monthly.thr_eligible) AS thr_eligible, 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 "tmp_home_visit" 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; DROP TABLE "tmp_home_visit"; """.format( tablename=self.tablename, ccs_record_case_ucr=self.get_table('static-ccs_record_cases'), agg_cf_table=AGG_CCS_RECORD_CF_TABLE, ), { 'start_date': self.month_start }
def table_name(self): return get_table_name(self.config['domain'], 'location_hierarchy')
def table_name(self): return get_table_name(self.config['domain'], "malaria_consortium")
def __init__(self, config): super(IndicatorESAdapter, self).__init__(config) self.es = get_es_new() self.table_name = get_table_name(config.domain, config.table_id).lower()
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 _old_ucr_tablename(self): doc_id = StaticDataSourceConfiguration.get_doc_id( self.domain, self.child_health_monthly_ucr_id) config, _ = get_datasource_config(doc_id, self.domain) return get_table_name(self.domain, config.table_id)
def table_name(self): return get_table_name(self.config['domain'], 'static-vhnd_form')
import postgres_copy import sqlalchemy from corehq.apps.locations.models import SQLLocation, LocationType from corehq.apps.userreports.models import StaticDataSourceConfiguration from corehq.apps.userreports.util import get_indicator_adapter, get_table_name from corehq.sql_db.connections import connection_manager, ICDS_UCR_CITUS_ENGINE_ID from custom.icds_reports.const import DISTRIBUTED_TABLES, REFERENCE_TABLES from custom.icds_reports.utils.migrations import create_citus_reference_table, create_citus_distributed_table, get_view_migrations from custom.icds_reports.tasks import (_aggregate_child_health_pnc_forms, _aggregate_bp_forms, _aggregate_gm_forms, drop_gm_indices) FILE_NAME_TO_TABLE_MAPPING = { 'awc_mgmt': get_table_name('icds-cas', 'static-awc_mgt_forms'), "ccs_cases": get_table_name('icds-cas', 'static-ccs_record_cases'), 'child_cases': get_table_name('icds-cas', 'static-child_health_cases'), 'daily_feeding': get_table_name('icds-cas', 'static-daily_feeding_forms'), 'household_cases': get_table_name('icds-cas', 'static-household_cases'), 'infrastructure': get_table_name('icds-cas', 'static-infrastructure_form'), 'infrastructure_v2': get_table_name('icds-cas', 'static-infrastructure_form_v2'), 'person_cases': get_table_name('icds-cas', 'static-person_cases_v3'), 'usage':
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 pregnant_tasks_cases_ucr_tablename(self): return get_table_name(self.domain, 'static-pregnant-tasks_cases')
def aggregation_query(self): month_start_string = month_formatter(self.month) child_tasks_ucr = get_table_name(self.domain, 'static-child_tasks_cases') child_health_monthly = get_child_health_tablename(self.month) columns = (('state_id', 'child_tasks.state_id'), ('supervisor_id', 'child_tasks.supervisor_id'), ('child_health_case_id', 'child_tasks.child_health_case_id'), ("month", self.month.strftime("'%Y-%m-%d'")), ("due_list_date_1g_dpt_1", "child_tasks.due_list_date_1g_dpt_1"), ("due_list_date_2g_dpt_2", "child_tasks.due_list_date_2g_dpt_2"), ("due_list_date_3g_dpt_3", "child_tasks.due_list_date_3g_dpt_3"), ("due_list_date_5g_dpt_booster", "child_tasks.due_list_date_5g_dpt_booster"), ("due_list_date_5g_dpt_booster1", "child_tasks.due_list_date_5g_dpt_booster1"), ("due_list_date_7gdpt_booster_2", "child_tasks.due_list_date_7gdpt_booster_2"), ("due_list_date_0g_hep_b_0", "child_tasks.due_list_date_0g_hep_b_0"), ("due_list_date_1g_hep_b_1", "child_tasks.due_list_date_1g_hep_b_1"), ("due_list_date_2g_hep_b_2", "child_tasks.due_list_date_2g_hep_b_2"), ("due_list_date_3g_hep_b_3", "child_tasks.due_list_date_3g_hep_b_3"), ("due_list_date_3g_ipv", "child_tasks.due_list_date_3g_ipv"), ("due_list_date_4g_je_1", "child_tasks.due_list_date_4g_je_1"), ("due_list_date_5g_je_2", "child_tasks.due_list_date_5g_je_2"), ("due_list_date_5g_measles_booster", "child_tasks.due_list_date_5g_measles_booster"), ("due_list_date_4g_measles", "child_tasks.due_list_date_4g_measles"), ("due_list_date_0g_opv_0", "child_tasks.due_list_date_0g_opv_0"), ("due_list_date_1g_opv_1", "child_tasks.due_list_date_1g_opv_1"), ("due_list_date_2g_opv_2", "child_tasks.due_list_date_2g_opv_2"), ("due_list_date_3g_opv_3", "child_tasks.due_list_date_3g_opv_3"), ("due_list_date_5g_opv_booster", "child_tasks.due_list_date_5g_opv_booster"), ("due_list_date_1g_penta_1", "child_tasks.due_list_date_1g_penta_1"), ("due_list_date_2g_penta_2", "child_tasks.due_list_date_2g_penta_2"), ("due_list_date_3g_penta_3", "child_tasks.due_list_date_3g_penta_3"), ("due_list_date_1g_rv_1", "child_tasks.due_list_date_1g_rv_1"), ("due_list_date_2g_rv_2", "child_tasks.due_list_date_2g_rv_2"), ("due_list_date_3g_rv_3", "child_tasks.due_list_date_3g_rv_3"), ("due_list_date_4g_vit_a_1", "child_tasks.due_list_date_4g_vit_a_1"), ("due_list_date_5g_vit_a_2", "child_tasks.due_list_date_5g_vit_a_2"), ("due_list_date_6g_vit_a_3", "child_tasks.due_list_date_6g_vit_a_3"), ("due_list_date_6g_vit_a_4", "child_tasks.due_list_date_6g_vit_a_4"), ("due_list_date_6g_vit_a_5", "child_tasks.due_list_date_6g_vit_a_5"), ("due_list_date_6g_vit_a_6", "child_tasks.due_list_date_6g_vit_a_6"), ("due_list_date_6g_vit_a_7", "child_tasks.due_list_date_6g_vit_a_7"), ("due_list_date_6g_vit_a_8", "child_tasks.due_list_date_6g_vit_a_8"), ("due_list_date_7g_vit_a_9", "child_tasks.due_list_date_7g_vit_a_9"), ("due_list_date_anc_1", "child_tasks.due_list_date_anc_1"), ("due_list_date_anc_2", "child_tasks.due_list_date_anc_2"), ("due_list_date_anc_3", "child_tasks.due_list_date_anc_3"), ("due_list_date_anc_4", "child_tasks.due_list_date_anc_4"), ("due_list_date_tt_1", "child_tasks.due_list_date_tt_1"), ("due_list_date_tt_2", "child_tasks.due_list_date_tt_2"), ("due_list_date_tt_booster", "child_tasks.due_list_date_tt_booster"), ("due_list_date_1g_bcg", "child_tasks.due_list_date_1g_bcg")) column_names = ", ".join([col[0] for col in columns]) calculations = ", ".join([col[1] for col in columns]) return f"""
def table_name(self): return get_table_name(self.domain, 'patient_summary')
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 table_name(self): return get_table_name(self.config['domain'], "static-sms-indicators-001")
def _get_count(config): table_name = get_table_name(config.domain, config.table_id) db_name = connection_manager.get_django_db_alias(config.engine_id) return _get_count_from_explain_raw( db_name, 'SELECT * FROM "%s"' % table_name, [])
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 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 table_name(self): return get_table_name(self.config['domain'], "malaria")
def _get_count(config): table_name = get_table_name(config.domain, config.table_id) db_name = connection_manager.get_django_db_alias(config.engine_id) query = ('SELECT * FROM "%s"' % table_name, []) return estimate_row_count(query, db_name)
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 table_name(self): return get_table_name(self.config['domain'], FOOD_CONSUMPTION)
def test_skip_destructive_rebuild(self): self.config = self._get_config('add_non_nullable_col') self.config.disable_destructive_rebuild = True self.config.save() get_case_pillow(ucr_configs=[self.config]) self.adapter = get_indicator_adapter(self.config) self.engine = self.adapter.engine # assert new date isn't in the config insp = reflection.Inspector.from_engine(self.engine) table_name = get_table_name(self.config.domain, self.config.table_id) self.assertEqual( len([ c for c in insp.get_columns(table_name) if c['name'] == 'new_date' ]), 0) # re-fetch from DB to bust object caches self.config = DataSourceConfiguration.get(self.config.data_source_id) # add the column to the config self.config.configured_indicators.append({ "column_id": "new_date", "type": "raw", "display_name": "new_date opened", "datatype": "datetime", "property_name": "other_opened_on", "is_nullable": False }) self.config.save() # re-fetch from DB to bust object caches self.config = DataSourceConfiguration.get(self.config.data_source_id) # bootstrap to trigger rebuild get_case_pillow(ucr_configs=[self.config]) logs = DataSourceActionLog.objects.filter( indicator_config_id=self.config.data_source_id, skip_destructive=True) self.assertEqual(1, len(logs)) self.assertEqual(logs[0].migration_diffs, [{ 'type': 'add_column', 'item_name': 'new_date' }]) # make the column allow nulls and check that it gets applied (since is non-destructive) self.config.configured_indicators[-1]['is_nullable'] = True self.config.save() # re-fetch from DB to bust object caches self.config = DataSourceConfiguration.get(self.config.data_source_id) # make sure change made it self.assertEqual(True, self.config.configured_indicators[-1]['is_nullable']) # bootstrap to trigger rebuild get_case_pillow(ucr_configs=[self.config]) # make sure we didn't add any more logs self.assertEqual( DataSourceActionLog.objects.filter( indicator_config_id=self.config.data_source_id, skip_destructive=True).count(), 1, ) # confirm the column was added to the table insp = reflection.Inspector.from_engine(self.engine) self.assertEqual( len([ c for c in insp.get_columns(table_name) if c['name'] == 'new_date' ]), 1)
def table_name(self): return get_table_name(self.config['domain'], TABLE_ID)
def table_name(self): return get_table_name(self.config['domain'], "weekly_forms")
def table_name(self): return get_table_name(self.domain, self.config.table_id)