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_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 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 or ccs_record.add is NULL)
) 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 _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
woman.migration_status IS DISTINCT FROM 'migrated' 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()
]