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, TABLE_ID)
def table_name(self):
return get_table_name(self.config['domain'], "malaria")
def table_name(self):
return get_table_name(self.config['domain'], 'location_hierarchy')
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)
