def eligible_couple_form_details(self, date_):
ucr_tablename = self._ucr_tablename('reach-eligible_couple_forms')
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(
"""
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::date) 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 "{ucr_tablename}"
WHERE person_case_id = %s AND timeend <= %s
ORDER BY timeend ASC
) eligible_couple
GROUP BY person_case_id
""".format(ucr_tablename=ucr_tablename),
[self.person_case_id, date_]
)
result = _dictfetchone(cursor)
return result
def allow_migrate(db, app_label):
"""
Return ``True`` if a app's migrations should be applied to the specified database otherwise
return ``False``.
Note: returning ``None`` is tantamount to returning ``True``
:return: Must return a boolean value, not None.
"""
if app_label == ICDS_REPORTS_APP:
db_alias = get_icds_ucr_db_alias()
return bool(db_alias and db_alias == db)
elif app_label == AAA_APP:
db_alias = get_aaa_db_alias()
return bool(db_alias and db_alias == db)
elif app_label == SYNCLOGS_APP:
return db == settings.SYNCLOGS_SQL_DB_ALIAS
elif app_label == WAREHOUSE_APP:
return db == settings.WAREHOUSE_DATABASE_ALIAS
if not settings.USE_PARTITIONED_DATABASE:
return app_label != PROXY_APP and db in ('default', None)
if app_label == PROXY_APP:
return db == partition_config.get_proxy_db()
elif app_label == BLOB_DB_APP and db == 'default':
return True
elif app_label in (FORM_PROCESSOR_APP, SCHEDULING_PARTITIONED_APP,
BLOB_DB_APP):
return (db == partition_config.get_proxy_db()
or db in partition_config.get_form_processing_dbs())
elif app_label == SQL_ACCESSORS_APP:
return db in partition_config.get_form_processing_dbs()
else:
return db == partition_config.get_main_db()
def update_table(domain, slug, method):
window_start = AggregationInformation.objects.filter(
step=slug, aggregation_window_end__isnull=False
).order_by('-created_at').values_list('aggregation_window_end').first()
if window_start is None:
window_start = datetime(1900, 1, 1)
else:
window_start = window_start[0]
window_end = datetime.utcnow()
agg_info = AggregationInformation.objects.create(
domain=domain,
step=slug,
aggregation_window_start=window_start,
aggregation_window_end=window_end,
)
# implement lock
agg_query, agg_params = method(domain, window_start, window_end)
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(agg_query, agg_params)
agg_info.end_time = datetime.utcnow()
agg_info.save()
def update_table(domain, slug, method):
window_start = AggregationInformation.objects.filter(
step=slug, aggregation_window_end__isnull=False).order_by(
'-created_at').values_list('aggregation_window_end').first()
if window_start is None:
window_start = datetime(1900, 1, 1)
else:
window_start = window_start[0]
window_end = datetime.utcnow()
agg_info = AggregationInformation.objects.create(
domain=domain,
step=slug,
aggregation_window_start=window_start,
aggregation_window_end=window_end,
)
# implement lock
agg_query, agg_params = method(domain, window_start, window_end)
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(agg_query, agg_params)
agg_info.end_time = datetime.utcnow()
agg_info.save()
def task_case_details(self):
ucr_tablename = self._ucr_tablename('reach-tasks_cases')
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute('SELECT * FROM "{}" WHERE doc_id = %s'.format(ucr_tablename), [self.tasks_case_id])
result = _dictfetchone(cursor)
return result
def delivery_form_details(self):
ucr_tablename = self._ucr_tablename('reach-delivery_forms')
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(
'SELECT * FROM "{}" WHERE ccs_record_case_id = %s'.format(ucr_tablename),
[self.ccs_record_case_id]
)
result = _dictfetchone(cursor)
return result
def birth_preparedness_form_details(self, date_):
ucr_tablename = self._ucr_tablename('reach-birth_preparedness')
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(
"""
SELECT * FROM "{}"
WHERE ccs_record_case_id = %s AND timeend IS NOT NULL AND timeend < %s
ORDER BY timeend DESC
""".format(ucr_tablename),
[self.ccs_record_case_id, date_]
)
result = _dictfetchall(cursor)
return result
def update_monthly_table(domain, slug, method, month):
window_start = month.replace(day=1)
window_end = window_start + relativedelta(months=1)
agg_info = AggregationInformation.objects.create(
domain=domain,
step=slug,
aggregation_window_start=window_start,
aggregation_window_end=window_end,
)
agg_query, agg_params = method(domain, window_start, window_end)
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(agg_query, agg_params)
agg_info.end_time = datetime.utcnow()
agg_info.save()
def postnatal_care_form_details(self, date_):
ucr_tablename = self._ucr_tablename('reach-postnatal_care')
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(
"""
SELECT * FROM "{}"
WHERE child_health_case_id = %s AND timeend IS NOT NULL AND timeend <= %s
ORDER BY timeend DESC
""".format(ucr_tablename),
[self.child_health_case_id, date_]
)
result = _dictfetchall(cursor)
return result
def update_monthly_table(domain, slug, method, month):
window_start = month.replace(day=1)
window_end = window_start + relativedelta(months=1)
agg_info = AggregationInformation.objects.create(
domain=domain,
step=slug,
aggregation_window_start=window_start,
aggregation_window_end=window_end,
)
agg_query, agg_params = method(domain, window_start, window_end)
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(agg_query, agg_params)
agg_info.end_time = datetime.utcnow()
agg_info.save()
def allow_migrate(db, app_label, model_name=None):
"""
Return ``True`` if a app's migrations should be applied to the specified database otherwise
return ``False``.
Note: returning ``None`` is tantamount to returning ``True``
:return: Must return a boolean value, not None.
"""
if db and not settings.DATABASES[db].get('MIGRATE', True):
return False
if app_label == ICDS_REPORTS_APP:
db_alias = get_icds_ucr_citus_db_alias()
return bool(db_alias and db_alias == db)
elif app_label == AAA_APP:
db_alias = get_aaa_db_alias()
return bool(db_alias and db_alias == db)
elif app_label == SYNCLOGS_APP:
return db == settings.SYNCLOGS_SQL_DB_ALIAS
if not settings.USE_PARTITIONED_DATABASE:
return app_label not in (PROXY_APP, PROXY_STANDBY_APP) and db in (
DEFAULT_DB_ALIAS, None)
if app_label == PROXY_APP:
return (db == plproxy_config.proxy_db or bool(
plproxy_standby_config and db == plproxy_standby_config.proxy_db))
if app_label == PROXY_STANDBY_APP:
return bool(plproxy_standby_config
and db == plproxy_standby_config.proxy_db)
elif app_label == BLOB_DB_APP and db == DEFAULT_DB_ALIAS:
return True
elif app_label == BLOB_DB_APP and model_name == 'blobexpiration':
return False
elif app_label in (FORM_PROCESSOR_APP, SCHEDULING_PARTITIONED_APP,
BLOB_DB_APP):
return (db == plproxy_config.proxy_db
or db in plproxy_config.form_processing_dbs
or bool(plproxy_standby_config
and db == plproxy_standby_config.proxy_db))
elif app_label == SQL_ACCESSORS_APP:
return db in plproxy_config.form_processing_dbs
else:
return db in (DEFAULT_DB_ALIAS, None)
def thr_form_details(self, date_):
ucr_tablename = self._ucr_tablename('reach-thr_forms')
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
# We only ever need the last THR form, so order by timeend and return one result
cursor.execute(
"""
SELECT * FROM "{}"
WHERE ccs_record_case_id = %s AND timeend IS NOT NULL AND timeend <= %s
ORDER BY timeend DESC
LIMIT 1
""".format(ucr_tablename),
[self.ccs_record_case_id, date_]
)
result = _dictfetchone(cursor)
return result
def list(cls, domain, date_, location_filters, sort_column):
location_query = ''
if location_filters:
location_query = [
"woman.{loc} = %({loc})s".format(loc=loc)
for loc in location_filters.keys()
]
location_query = " AND ".join(location_query)
location_query = location_query + " AND"
query, params = """
SELECT
(woman.person_case_id) AS "id",
woman.name AS "name",
woman.dob AS "dob",
((%(start_date)s - "woman"."dob") / 30.417)::INT AS "age_in_months",
EXTRACT('month' FROM age(ccs_record.preg_reg_date)) AS "pregMonth",
ccs_record.hrp AS "highRiskPregnancy",
ccs_record.num_anc_checkups AS "noOfAncCheckUps"
FROM "{woman_table}" woman
JOIN "{ccs_record_table}" ccs_record ON ccs_record.person_case_id=woman.person_case_id
WHERE (
woman.domain = %(domain)s AND
{location_where}
(daterange(%(start_date)s, %(end_date)s) && ANY(pregnant_ranges)) AND
ccs_record.add < %(start_date)s
) ORDER BY {sort_col}
""".format(location_where=location_query,
woman_table=Woman._meta.db_table,
ccs_record_table=CcsRecord._meta.db_table,
sort_col=sort_column), {
'domain': domain,
'start_date': date_,
'end_date': date_ + relativedelta(months=1),
}
params.update(location_filters)
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(query, params)
desc = cursor.description
return [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
def list(cls, domain, date_, location_filters, sort_column):
location_query = ''
if location_filters:
location_query = [
"woman.{loc} = %({loc})s".format(loc=loc) for loc in location_filters.keys()
]
location_query = " AND ".join(location_query)
location_query = location_query + " AND"
query, params = """
SELECT
(woman.person_case_id) AS "id",
woman.name AS "name",
woman.dob AS "dob",
EXTRACT('month' FROM age(ccs_record.preg_reg_date)) AS "pregMonth",
ccs_record.hrp AS "highRiskPregnancy",
ccs_record.num_anc_checkups AS "noOfAncCheckUps"
FROM "{woman_table}" woman
JOIN "{ccs_record_table}" ccs_record ON ccs_record.person_case_id=woman.person_case_id
WHERE (
woman.domain = %(domain)s AND
{location_where}
(daterange(%(start_date)s, %(end_date)s) && ANY(pregnant_ranges))
) ORDER BY {sort_col}
""".format(
location_where=location_query,
woman_table=Woman._meta.db_table,
ccs_record_table=CcsRecord._meta.db_table,
sort_col=sort_column
), {
'domain': domain,
'start_date': date_,
'end_date': date_ + relativedelta(months=1),
}
params.update(location_filters)
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(query, params)
desc = cursor.description
return [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
def allow_migrate(db, app_label):
"""
Return ``True`` if a app's migrations should be applied to the specified database otherwise
return ``False``.
Note: returning ``None`` is tantamount to returning ``True``
:return: Must return a boolean value, not None.
"""
if app_label == ICDS_REPORTS_APP:
db_alias = get_icds_ucr_db_alias()
return bool(db_alias and db_alias == db)
elif app_label == AAA_APP:
db_alias = get_aaa_db_alias()
return bool(db_alias and db_alias == db)
elif app_label == SYNCLOGS_APP:
return db == settings.SYNCLOGS_SQL_DB_ALIAS
elif app_label == WAREHOUSE_APP:
return db == settings.WAREHOUSE_DATABASE_ALIAS
if not settings.USE_PARTITIONED_DATABASE:
return app_label != PROXY_APP and db in ('default', None)
if app_label == PROXY_APP:
return db == partition_config.get_proxy_db()
elif app_label == BLOB_DB_APP and db == 'default':
return True
elif app_label in (FORM_PROCESSOR_APP, SCHEDULING_PARTITIONED_APP, BLOB_DB_APP):
return (
db == partition_config.get_proxy_db() or
db in partition_config.get_form_processing_dbs()
)
elif app_label == SQL_ACCESSORS_APP:
return db in partition_config.get_form_processing_dbs()
else:
return db == partition_config.get_main_db()
def _explain_query(cls, method, domain, window_start, window_end):
agg_query, agg_params = method(domain, window_start, window_end)
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute('explain ' + agg_query, agg_params)
return cls.__name__ + method.__name__, cursor.fetchall()
def list(cls, domain, date_, location_filters, sort_column):
location_query = ''
if location_filters:
location_query = [
"woman.{loc} = %({loc})s".format(loc=loc)
for loc in location_filters.keys()
]
location_query = " AND ".join(location_query)
location_query = location_query + " AND"
query, params = """
SELECT
woman.person_case_id AS "id",
woman.name AS "name",
woman.dob AS "dob",
((%(start_date)s - "woman"."dob") / 30.417)::INT AS "age_in_months",
eligible_couple."currentFamilyPlanningMethod" AS "currentFamilyPlanningMethod",
eligible_couple."adoptionDateOfFamilyPlaning" AS "adoptionDateOfFamilyPlaning"
FROM "{woman_table}" woman
LEFT JOIN (
SELECT
"{eligible_couple_table}".person_case_id,
"{eligible_couple_table}".timeend::date AS "adoptionDateOfFamilyPlaning",
"{eligible_couple_table}".fp_current_method AS "currentFamilyPlanningMethod"
FROM (
SELECT
person_case_id,
MAX(timeend) AS "timeend"
FROM "{eligible_couple_table}"
WHERE timeend <= %(end_date)s
GROUP BY person_case_id
) as last_eligible_couple
INNER JOIN "{eligible_couple_table}" ON
"{eligible_couple_table}".person_case_id=last_eligible_couple.person_case_id AND
"{eligible_couple_table}".timeend=last_eligible_couple.timeend
) eligible_couple ON eligible_couple.person_case_id=woman.person_case_id
WHERE (
woman.domain = %(domain)s AND
woman.marital_status = 'married' AND
NOT (
woman.migration_status = 'yes' AND woman.migration_status IS NOT NULL
) AND
{location_where}
dob BETWEEN %(dob_start_date)s AND %(dob_end_date)s AND
(
pregnant_ranges IS NULL OR
NOT daterange(%(start_date)s, %(end_date)s) && ANY(pregnant_ranges)
)
) ORDER BY {sort_col}
""".format(
location_where=location_query,
woman_table=Woman._meta.db_table,
eligible_couple_table=cls._ucr_eligible_couple_table(domain),
sort_col=sort_column), {
'domain': domain,
'dob_start_date': date_ - relativedelta(years=49),
'dob_end_date': date_ - relativedelta(years=15),
'start_date': date_,
'end_date': date_ + relativedelta(months=1),
}
params.update(location_filters)
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(query, params)
desc = cursor.description
return [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
def list(cls, domain, date_, location_filters, sort_column):
location_query = ''
if location_filters:
location_query = [
"woman.{loc} = %({loc})s".format(loc=loc) for loc in location_filters.keys()
]
location_query = " AND ".join(location_query)
location_query = location_query + " AND"
query, params = """
SELECT
woman.person_case_id AS "id",
woman.name AS "name",
woman.dob AS "dob",
eligible_couple."currentFamilyPlanningMethod" AS "currentFamilyPlanningMethod",
eligible_couple."adoptionDateOfFamilyPlaning" AS "adoptionDateOfFamilyPlaning"
FROM "{woman_table}" woman
LEFT JOIN (
SELECT
"{eligible_couple_table}".person_case_id,
"{eligible_couple_table}".timeend::date AS "adoptionDateOfFamilyPlaning",
"{eligible_couple_table}".fp_current_method AS "currentFamilyPlanningMethod"
FROM (
SELECT
person_case_id,
MAX(timeend) AS "timeend"
FROM "{eligible_couple_table}"
WHERE timeend <= %(end_date)s
GROUP BY person_case_id
) as last_eligible_couple
INNER JOIN "{eligible_couple_table}" ON
"{eligible_couple_table}".person_case_id=last_eligible_couple.person_case_id AND
"{eligible_couple_table}".timeend=last_eligible_couple.timeend
) eligible_couple ON eligible_couple.person_case_id=woman.person_case_id
WHERE (
woman.domain = %(domain)s AND
woman.marital_status = 'married' AND
NOT (
woman.migration_status = 'yes' AND woman.migration_status IS NOT NULL
) AND
{location_where}
dob BETWEEN %(dob_start_date)s AND %(dob_end_date)s AND
(
pregnant_ranges IS NULL OR
NOT daterange(%(start_date)s, %(end_date)s) && ANY(pregnant_ranges)
)
) ORDER BY {sort_col}
""".format(
location_where=location_query,
woman_table=Woman._meta.db_table,
eligible_couple_table=cls._ucr_eligible_couple_table(domain),
sort_col=sort_column
), {
'domain': domain,
'dob_start_date': date_ - relativedelta(years=49),
'dob_end_date': date_ - relativedelta(years=15),
'start_date': date_,
'end_date': date_ + relativedelta(months=1),
}
params.update(location_filters)
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute(query, params)
desc = cursor.description
return [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
def _explain_query(cls, method, domain, window_start, window_end):
agg_query, agg_params = method(domain, window_start, window_end)
db_alias = get_aaa_db_alias()
with connections[db_alias].cursor() as cursor:
cursor.execute('explain ' + agg_query, agg_params)
return cls.__name__ + method.__name__, cursor.fetchall()
