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