def handle(self, domain, old_data_source_id, new_data_source_id, **options):
old_config, _ = get_datasource_config(old_data_source_id, domain)
new_config, _ = get_datasource_config(new_data_source_id, domain)
assert old_config.referenced_doc_type == new_config.referenced_doc_type
old_filter = old_config.get_case_type_or_xmlns_filter()
new_filter = new_config.get_case_type_or_xmlns_filter()
assert set(old_filter) == set(new_filter)
old_adapter = get_indicator_adapter(old_config)
new_adapter = get_indicator_adapter(new_config)
old_table = old_adapter.get_table()
new_table = new_adapter.get_table()
assert hasattr(old_table.columns, options['date_column'])
column = getattr(old_table.columns, options['date_column'])
new_adapter.build_table(initiated_by=options['initiated'], source='migrate_ucr')
end_date = date(2016, 1, 1)
query = self.insert_query(old_table, new_table, column, end_date=end_date)
self.run_query(new_adapter, query)
start_date = end_date
end_date = end_date + relativedelta(months=1)
while start_date < date.today():
query = self.insert_query(old_table, new_table, column, start_date, end_date)
self.run_query(new_adapter, query)
start_date += relativedelta(months=1)
end_date += relativedelta(months=1)
query = self.insert_query(old_table, new_table, column, start_date)
self.run_query(new_adapter, query)
def handle(self, domain, old_data_source_id, new_data_source_id, **options):
old_config, _ = get_datasource_config(old_data_source_id, domain)
new_config, _ = get_datasource_config(new_data_source_id, domain)
assert old_config.referenced_doc_type == new_config.referenced_doc_type
old_filter = old_config.get_case_type_or_xmlns_filter()
new_filter = new_config.get_case_type_or_xmlns_filter()
assert set(old_filter) == set(new_filter)
old_adapter = get_indicator_adapter(old_config)
new_adapter = get_indicator_adapter(new_config)
old_table = old_adapter.get_table()
new_table = new_adapter.get_table()
assert hasattr(old_table.columns, options['date_column'])
column = getattr(old_table.columns, options['date_column'])
new_adapter.build_table(initiated_by=options['initiated'], source='migrate_ucr')
end_date = date(2016, 1, 1)
query = self.insert_query(old_table, new_table, column, end_date=end_date)
self.run_query(new_adapter, query)
start_date = end_date
end_date = end_date + relativedelta(months=1)
while start_date < date.today():
query = self.insert_query(old_table, new_table, column, start_date, end_date)
self.run_query(new_adapter, query)
start_date += relativedelta(months=1)
end_date += relativedelta(months=1)
query = self.insert_query(old_table, new_table, column, start_date)
self.run_query(new_adapter, query)
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, case_type, data_source_ids, **options):
configs = []
for data_source_id in data_source_ids:
config, _ = get_datasource_config(data_source_id, domain)
assert config.asynchronous
assert config.referenced_doc_type == CASE_DOC_TYPE
configs.append(config)
fake_change_doc = {'doc_type': CASE_DOC_TYPE, 'domain': domain}
doc_store = get_document_store(domain, CASE_DOC_TYPE)
case_accessor = doc_store.case_accessors
case_ids = case_accessor.get_case_ids_in_domain(type=case_type)
num_case_ids = len(case_ids)
print("inserting %d docs" % num_case_ids)
for config in configs:
adapter = get_indicator_adapter(config, can_handle_laboratory=True)
adapter.build_table()
# normally called after rebuilding finishes
adapter.after_table_build()
config_ids = [config._id for config in configs]
for i, case_id in enumerate(case_ids):
change = FakeChange(case_id, fake_change_doc)
AsyncIndicator.update_indicators(change, config_ids)
if i % 1000 == 0:
print("inserted %d / %d docs" % (i, num_case_ids))
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 recalculate_stagnant_cases():
domain = 'icds-cas'
config_ids = [
'static-icds-cas-static-ccs_record_cases_monthly_v2',
'static-icds-cas-static-ccs_record_cases_monthly_tableau_v2',
'static-icds-cas-static-child_cases_monthly_v2',
]
stagnant_cases = set()
for config_id in config_ids:
config, is_static = get_datasource_config(config_id, domain)
adapter = get_indicator_adapter(config)
case_ids = _find_stagnant_cases(adapter)
celery_task_logger.info(
"Found {} stagnant cases in config {}".format(len(case_ids), config_id)
)
stagnant_cases = stagnant_cases.union(set(case_ids))
celery_task_logger.info(
"Total number of stagant cases is now {}".format(len(stagnant_cases))
)
case_accessor = CaseAccessors(domain)
num_stagnant_cases = len(stagnant_cases)
current_case_num = 0
for case_ids in chunked(stagnant_cases, 1000):
current_case_num += len(case_ids)
cases = case_accessor.get_cases(list(case_ids))
for case in cases:
publish_case_saved(case, send_post_save_signal=False)
celery_task_logger.info(
"Resaved {} / {} cases".format(current_case_num, num_stagnant_cases)
)
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 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 _get_config(self):
config, _ = get_datasource_config(self.ucr_name, self.domain)
config.configured_indicators = [
ind for ind in config.configured_indicators
if ind['column_id'] not in LOCATION_COLUMNS
]
return config
def recalculate_stagnant_cases():
domain = 'icds-cas'
config_ids = [
'static-icds-cas-static-ccs_record_cases_monthly_v2',
'static-icds-cas-static-ccs_record_cases_monthly_tableau_v2',
'static-icds-cas-static-child_cases_monthly_v2',
'static-icds-cas-static-child_cases_monthly_tableau_v2',
]
stagnant_cases = set()
for config_id in config_ids:
config, is_static = get_datasource_config(config_id, domain)
adapter = get_indicator_adapter(config)
case_ids = _find_stagnant_cases(adapter)
celery_task_logger.info("Found {} stagnant cases in config {}".format(
len(case_ids), config_id))
stagnant_cases = stagnant_cases.union(set(case_ids))
celery_task_logger.info(
"Total number of stagant cases is now {}".format(
len(stagnant_cases)))
case_accessor = CaseAccessors(domain)
num_stagnant_cases = len(stagnant_cases)
current_case_num = 0
for case_ids in chunked(stagnant_cases, 1000):
current_case_num += len(case_ids)
cases = case_accessor.get_cases(list(case_ids))
for case in cases:
publish_case_saved(case, send_post_save_signal=False)
celery_task_logger.info("Resaved {} / {} cases".format(
current_case_num, num_stagnant_cases))
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_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 handle(self, domain, data_source_id, *args, **kwargs):
config, _ = get_datasource_config(data_source_id, domain)
adapter = get_indicator_adapter(config)
q = adapter.get_query_object()
document_store = get_document_store_for_doc_type(domain, config.referenced_doc_type)
bad_rows = []
for row in with_progress_bar(q, length=q.count()):
doc_id = row.doc_id
doc = document_store.get_document(doc_id)
current_rows = config.get_all_values(doc)
if len(current_rows) > 1:
raise ValueError("this command doesn't work for datasources returning multiple rows per doc")
try:
current_row = current_rows[0]
except KeyError:
continue
# don't compare the 'inserted_at' columns
current_row = [val for val in current_row if val.column.database_column_name != 'inserted_at']
for val in current_row:
try:
inserted_value = getattr(row, val.column.database_column_name)
if (inserted_value != val.value
or row.inserted_at.replace(tzinfo=pytz.utc) < parse_datetime(doc['server_modified_on'])):
bad_rows.append({
'doc_id': row.doc_id,
'column_name': val.column.database_column_name,
'inserted_at': row.inserted_at.isoformat(),
'server_modified_on': doc['server_modified_on'],
'stored_value': getattr(row, val.column.database_column_name),
'desired_value': val.value,
'message': ('column mismatch'
if inserted_value != val.value else "modified date early"),
})
except AttributeError:
bad_rows.append({
'doc_id': row.doc_id,
'column_name': val.column.database_column_name,
'inserted_at': 'missing',
'server_modified_on': doc['server_modified_on'],
'stored_value': 'missing',
'desired_value': val.value,
'message': 'doc missing',
})
filename = 'datasource_mismatches_{}_{}.csv'.format(
data_source_id[-8:],
datetime.utcnow().strftime("%Y-%m-%d-%H-%M-%S")
)
with open(filename, 'w', encoding='utf-8') as f:
headers = ['doc_id', 'column_name', 'inserted_at', 'server_modified_on',
'stored_value', 'desired_value', 'message']
writer = csv.DictWriter(f, headers)
writer.writeheader()
writer.writerows(bad_rows)
print("Found {} mismatches. Check {} for more details".format(len(bad_rows), filename))
def handle(self, domain, type_, case_type_or_xmlns, data_source_ids,
**options):
assert type_ in ('xform', 'case')
self.referenced_type = CASE_DOC_TYPE if type_ == 'case' else XFORM_DOC_TYPE
configs = []
for data_source_id in data_source_ids:
config, _ = get_datasource_config(data_source_id, domain)
assert config.asynchronous
assert config.referenced_doc_type == self.referenced_type
configs.append(config)
for config in configs:
adapter = get_indicator_adapter(config)
adapter.build_table()
self.domain = domain
self.case_type_or_xmlns = case_type_or_xmlns
self.bulk = options['bulk']
self.database = options['database']
self.config_ids = [config._id for config in configs]
ids = []
for id_ in self._get_ids_to_process():
ids.append(id_)
if len(ids) > 999:
self._save_ids(ids)
ids = []
self._save_ids(ids)
for config in configs:
if not config.is_static:
config.meta.build.rebuilt_asynchronously = True
config.save()
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_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 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 delete_report(request, domain, report_id):
if not (toggle_enabled(request, toggles.USER_CONFIGURABLE_REPORTS)
or toggle_enabled(request, toggles.REPORT_BUILDER)
or toggle_enabled(request, toggles.REPORT_BUILDER_BETA_GROUP)
or has_report_builder_add_on_privilege(request)):
raise Http404()
config = get_document_or_404(ReportConfiguration, domain, report_id)
# Delete the data source too if it's not being used by any other reports.
try:
data_source, __ = get_datasource_config(config.config_id, domain)
except DataSourceConfigurationNotFoundError:
# It's possible the data source has already been deleted, but that's fine with us.
pass
else:
if data_source.get_report_count() <= 1:
# No other reports reference this data source.
data_source.deactivate()
config.delete()
did_purge_something = purge_report_from_mobile_ucr(config)
messages.success(request, _(u'Report "{}" deleted!').format(config.title))
if did_purge_something:
messages.warning(
request,
_(u"This report was used in one or more applications. "
"It has been removed from there too.")
)
redirect = request.GET.get("redirect", None)
if not redirect:
redirect = reverse('configurable_reports_home', args=[domain])
return HttpResponseRedirect(redirect)
def handle(self, domain, type_, case_type_or_xmlns, data_source_ids, **options):
assert type_ in ('xform', 'case')
self.referenced_type = CASE_DOC_TYPE if type_ == 'case' else XFORM_DOC_TYPE
configs = []
for data_source_id in data_source_ids:
config, _ = get_datasource_config(data_source_id, domain)
assert config.asynchronous
assert config.referenced_doc_type == self.referenced_type
configs.append(config)
for config in configs:
adapter = get_indicator_adapter(config)
adapter.build_table(initiated_by=options['initiated'], source='async_rebuild_table')
self.domain = domain
self.case_type_or_xmlns = case_type_or_xmlns
self.bulk = options['bulk']
self.database = options['database']
self.config_ids = [config._id for config in configs]
ids = []
for id_ in self._get_ids_to_process():
ids.append(id_)
if len(ids) > 999:
self._save_ids(ids)
ids = []
self._save_ids(ids)
for config in configs:
if not config.is_static:
config.meta.build.rebuilt_asynchronously = True
config.save()
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 handle(self, domain, case_type, data_source_ids, **options):
configs = []
for data_source_id in data_source_ids:
config, _ = get_datasource_config(data_source_id, domain)
assert config.asynchronous
assert config.referenced_doc_type == CASE_DOC_TYPE
configs.append(config)
fake_change_doc = {'doc_type': CASE_DOC_TYPE, 'domain': domain}
for config in configs:
adapter = get_indicator_adapter(config, can_handle_laboratory=True)
adapter.build_table()
# normally called after rebuilding finishes
adapter.after_table_build()
self.domain = domain
self.case_type = case_type
config_ids = [config._id for config in configs]
for case_id in self._get_case_ids_to_process():
change = FakeChange(case_id, fake_change_doc)
AsyncIndicator.update_from_kafka_change(change, config_ids)
for config in configs:
if not config.is_static:
config.meta.build.rebuilt_asynchronously = True
config.save()
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 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_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 delete_report(request, domain, report_id):
if not (toggle_enabled(request, toggles.USER_CONFIGURABLE_REPORTS)
or toggle_enabled(request, toggles.REPORT_BUILDER)
or toggle_enabled(request, toggles.REPORT_BUILDER_BETA_GROUP)
or has_report_builder_add_on_privilege(request)):
raise Http404()
config = get_document_or_404(ReportConfiguration, domain, report_id)
# Delete the data source too if it's not being used by any other reports.
try:
data_source, __ = get_datasource_config(config.config_id, domain)
except DataSourceConfigurationNotFoundError:
# It's possible the data source has already been deleted, but that's fine with us.
pass
else:
if data_source.get_report_count() <= 1:
# No other reports reference this data source.
data_source.deactivate()
config.delete()
did_purge_something = purge_report_from_mobile_ucr(config)
messages.success(request, _(u'Report "{}" deleted!').format(config.title))
if did_purge_something:
messages.warning(
request,
_(u"This report was used in one or more applications. "
"It has been removed from there too."))
redirect = request.GET.get("redirect", None)
if not redirect:
redirect = reverse('configurable_reports_home', args=[domain])
return HttpResponseRedirect(redirect)
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 handle(self, domain, data_source_id, doc_id, **options):
config, _ = get_datasource_config(data_source_id, domain)
doc_type = config.referenced_doc_type
doc_store = get_document_store_for_doc_type(domain, doc_type)
doc = doc_store.get_document(doc_id)
sort_by = options['sort']
local_variables = {'config': config, 'doc': doc}
cProfile.runctx('config.get_all_values(doc)', {}, local_variables, 'ucr_stats.log')
print_profile_stats('ucr_stats.log', sort_by)
def handle(self, domain, data_source_id, doc_id, **options):
config, _ = get_datasource_config(data_source_id, domain)
doc_type = config.referenced_doc_type
doc_store = get_document_store(domain, doc_type)
doc = doc_store.get_document(doc_id)
sort_by = options['sort']
local_variables = {'config': config, 'doc': doc}
cProfile.runctx('config.get_all_values(doc)', {}, local_variables,
'ucr_stats.log')
print_profile_stats('ucr_stats.log', sort_by)
def handle(self, *args, **options):
fake_change_doc = {'doc_type': CASE_DOC_TYPE, 'domain': DOMAIN}
for data_source_id in DATA_SOURCES:
print("processing data source %s" % data_source_id)
data_source, is_static = get_datasource_config(data_source_id, DOMAIN)
assert is_static
adapter = get_indicator_adapter(data_source)
table = adapter.get_table()
for case_id in self._get_case_ids_to_process(adapter, table, data_source_id):
change = FakeChange(case_id, fake_change_doc)
AsyncIndicator.update_from_kafka_change(change, [data_source_id])
def handle(self, case_type, **options):
# get the configurations for the case type
db_config = DB_CONFIGS[case_type]
old_config, _ = get_datasource_config(db_config.old_config_id, DOMAIN)
new_config, _ = get_datasource_config(db_config.new_config_id, DOMAIN)
old_columns = [c.id for c in old_config.get_columns()]
new_columns = [c.id for c in new_config.get_columns()]
# verify that the old columns are subset of the new columns
# new columns are allowed to be null
assert set(old_columns).issubset(set(new_columns))
self.old_table = db_config.old_table
self.new_table = db_config.new_table
# create a column string
quoted_columns = []
for col in old_columns:
if col.islower():
quoted_columns.append('%s' % col)
else:
quoted_columns.append('"%s"' % col)
self.column_string = ', '.join(quoted_columns)
self.select_column_string = ', '.join(
['A.' + col for col in quoted_columns])
sql_command = self._sql_command(db_config.has_repeat_iteration)
if options['dry_run']:
print(sql_command)
else:
print("count of old table %s: " % self.old_table)
print(self.get_count(self.old_table))
print("count of new table %s: " % self.new_table)
print(self.get_count(self.new_table))
with connections['icds-ucr'].cursor() as cursor:
cursor.execute(sql_command)
print("count of new table %s: " % self.new_table)
print(self.get_count(self.new_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 handle(self, *args, **options):
data_source, is_static = get_datasource_config(DATA_SOURCE_NAME, DOMAIN)
assert is_static
adapter = get_indicator_adapter(data_source)
table = adapter.get_table()
for state_id in STATE_IDS:
null_query = adapter.session_helper.Session.query(table.columns.doc_id)
null_query = null_query.filter(
table.columns.state_id == state_id,
table.columns.aadhar_date == None
).all()
AsyncIndicator.objects.bulk_create([
create_async_indicator(doc_id[0])
for doc_id in null_query
], batch_size=500)
def handle(self, *args, **options):
data_source, is_static = get_datasource_config(DATA_SOURCE_NAME,
DOMAIN)
assert is_static
adapter = get_indicator_adapter(data_source)
table = adapter.get_table()
for state_id in STATE_IDS:
null_query = adapter.session_helper.Session.query(
table.columns.doc_id)
null_query = null_query.filter(
table.columns.state_id == state_id,
table.columns.aadhar_date == None).all()
AsyncIndicator.objects.bulk_create(
[create_async_indicator(doc_id[0]) for doc_id in null_query],
batch_size=500)
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 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 test_pnc_form(self):
config, _ = get_datasource_config(self.ucr_name, 'icds-cas')
config.configured_indicators = [
ind for ind in config.configured_indicators
if ind['column_id'] != 'state_id'
]
form_json = convert_xform_to_json(self.get_xml('pnc_form_v10326'))
form_json = {
'form': form_json,
'domain': self.domain,
'xmlns': form_json['@xmlns'],
'doc_type': 'XFormInstance',
}
ucr_result = config.get_all_values(form_json)
for row in ucr_result:
row = {
i.column.database_column_name: i.value
for i in row
if i.column.database_column_name not in BLACKLISTED_COLUMNS
}
self.assertEqual(
{
"doc_id": None,
"repeat_iteration": 0,
"timeend": None,
"ccs_record_case_id":
"081cc405-5598-430f-ac8f-39cc4a1fdb30",
"child_health_case_id":
"252d8e20-c698-4c94-a5a9-53bbf8972b64",
"counsel_adequate_bf": None,
"counsel_breast": None,
"counsel_exclusive_bf": 1,
"counsel_increase_food_bf": 1,
"counsel_methods": None,
"counsel_only_milk": None,
"skin_to_skin": None,
"is_ebf": 1,
"water_or_milk": None,
"other_milk_to_child": 0,
"tea_other": None,
"eating": None,
"not_breastfeeding": None
}, row)
def test_ebf_form(self):
config, _ = get_datasource_config(self.ucr_name, 'icds-cas')
config.configured_indicators = [
ind for ind in config.configured_indicators
if ind['column_id'] != 'state_id'
]
form_json = convert_xform_to_json(self.get_xml('ebf_form_v10326'))
form_json = {
'form': form_json,
'domain': self.domain,
'xmlns': form_json['@xmlns'],
'doc_type': 'XFormInstance',
}
ucr_result = config.get_all_values(form_json)
for row in ucr_result:
row = {
i.column.database_column_name: i.value
for i in row
if i.column.database_column_name not in BLACKLISTED_COLUMNS
}
self.assertEqual(
{
"doc_id": None,
"repeat_iteration": 0,
"timeend": None,
"ccs_record_case_id":
"d53c940c-3bf3-44f7-97a1-f43fcbe74359",
"child_health_case_id":
"03f39da4-8ea3-4108-b8a8-1b58fdb4a698",
"counsel_adequate_bf": None,
"counsel_breast": None,
"counsel_exclusive_bf": None,
"counsel_increase_food_bf": None,
"counsel_methods": None,
"counsel_only_milk": 1,
"skin_to_skin": None,
"is_ebf": 1,
"water_or_milk": 0,
"other_milk_to_child": None,
"tea_other": 0,
"eating": 0,
"not_breastfeeding": None
}, row)
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_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 handle(self, domain, data_source_id, doc_id, **options):
config, _ = get_datasource_config(data_source_id, domain)
doc_type = config.referenced_doc_type
doc_store = get_document_store(domain, doc_type)
doc = doc_store.get_document(doc_id)
local_variables = {'config': config, 'doc': doc}
cProfile.runctx('config.get_all_values(doc)', {}, local_variables,
'ucr_stats.log')
p = pstats.Stats('ucr_stats.log')
p.sort_stats('time')
print("Top 10 functions by time\n")
p.print_stats(10)
print("Specs timing\n")
p.print_stats('userreports.*specs.*\(__call__\)')
print("Socket recvs\n")
p.print_stats('recv')
print("Doc retrievals\n")
p.print_stats('document_store.*\(get_document\)')
print("Postgres queries\n")
p.print_stats('execute.*psycopg')
print("ES queries\n")
p.print_stats('es_query.py.*\(run\)')
print("""
Note: Due to overhead in profiling, these times are much larger than the real times.
Next Steps:
1) choose one of the previous calls to investigate
2) use print_callees or print_callers to follow the calls
* usage https://docs.python.org/2/library/profile.html#pstats.Stats.print_stats
3) check out branch je/time-ucr to get logs for processing time of each column
(you'll likely need to rebase it on latest master)
""")
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 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 _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 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 _get_data_source(id_):
return get_datasource_config(id_, self.project.name)[0]
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 get_datasource_config_or_404(config_id, domain):
try:
return get_datasource_config(config_id, domain)
except DataSourceConfigurationNotFoundError:
raise Http404
def data_source(self):
return get_datasource_config(self.primary_data_source_id.hex, self.domain)[0]
def config(self):
from corehq.apps.userreports.models import get_datasource_config
if self._config is None:
self._config, _ = get_datasource_config(self._config_id, self.domain)
return self._config
def config(self):
if self._config is None:
self._config, _ = get_datasource_config(self._config_id, self.domain, self.data_source_type)
return self._config
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)