def _query_and_write_reports(exporter, now, report_type, path_received, path_missing, path_modified,
path_withdrawals):
"""Runs the reconciliation MySQL queries and writes result rows to the given CSV writers.
Note that due to syntax differences, the query runs on MySQL only (not SQLite in unit tests).
"""
report_cover_range = 10
if report_type == 'monthly':
report_cover_range = 60
# Gets all sample/order pairs where everything arrived, within the past n days.
received_predicate = lambda result: (result[_RECEIVED_TEST_INDEX] and
result[_SENT_COUNT_INDEX] <= result[_RECEIVED_COUNT_INDEX]
and
in_past_n_days(result, now, report_cover_range))
# Gets samples or orders where something has gone missing within the past n days, and if an order
# was placed, it was placed at least 36 hours ago.
missing_predicate = lambda result: ((result[_SENT_COUNT_INDEX] != result[_RECEIVED_COUNT_INDEX] or
(result[_SENT_FINALIZED_INDEX] and
not result[_RECEIVED_TEST_INDEX])) and
in_past_n_days(result, now, report_cover_range,
ordered_before=now - _THIRTY_SIX_HOURS_AGO))
# Gets samples or orders where something has modified within the past n days.
modified_predicate = lambda result: (result[_EDITED_CANCELLED_RESTORED_STATUS_FLAG_INDEX] and
in_past_n_days(result, now, report_cover_range))
code_dao = CodeDao()
race_question_code = code_dao.get_code(PPI_SYSTEM, RACE_QUESTION_CODE)
native_american_race_code = code_dao.get_code(PPI_SYSTEM, RACE_AIAN_CODE)
# break into three steps to avoid OOM issue
report_paths = [path_received, path_missing, path_modified]
report_predicates = [received_predicate, missing_predicate, modified_predicate]
for report_path, report_predicate in zip(report_paths, report_predicates):
with exporter.open_writer(report_path, report_predicate) as report_writer:
exporter.run_export_with_writer(report_writer, replace_isodate(_RECONCILIATION_REPORT_SQL),
{'race_question_code_id': race_question_code.codeId,
'native_american_race_code_id':
native_american_race_code.codeId,
'biobank_id_prefix': get_biobank_id_prefix(),
'pmi_ops_system': _PMI_OPS_SYSTEM,
'kit_id_system': _KIT_ID_SYSTEM,
'tracking_number_system': _TRACKING_NUMBER_SYSTEM,
'n_days_ago': now - datetime.timedelta(
days=(report_cover_range + 1))})
# Now generate the withdrawal report, within the past n days.
exporter.run_export(path_withdrawals, replace_isodate(_WITHDRAWAL_REPORT_SQL),
{'race_question_code_id': race_question_code.codeId,
'native_american_race_code_id': native_american_race_code.codeId,
'n_days_ago': now - datetime.timedelta(days=report_cover_range),
'biobank_id_prefix': get_biobank_id_prefix()})
def _query_and_write_reports(exporter, now, path_received, path_late,
path_missing, path_withdrawals):
"""Runs the reconciliation MySQL queries and writes result rows to the given CSV writers.
Note that due to syntax differences, the query runs on MySQL only (not SQLite in unit tests).
"""
# Gets all sample/order pairs where everything arrived, regardless of timing.
received_predicate = lambda result: (result[_RECEIVED_TEST_INDEX] and
result[_SENT_COUNT_INDEX] == result[
_RECEIVED_COUNT_INDEX])
# Gets orders for which the samples arrived, but they arrived late, within the past 7 days.
late_predicate = lambda result: (result[_ELAPSED_HOURS_INDEX] and int(
result[_ELAPSED_HOURS_INDEX]) >= 24 and in_past_week(result, now))
# Gets samples or orders where something has gone missing within the past 7 days, and if an order
# was placed, it was placed at least 36 hours ago.
missing_predicate = lambda result: (
(result[_SENT_COUNT_INDEX] != result[_RECEIVED_COUNT_INDEX] or (result[
_SENT_FINALIZED_INDEX] and not result[_RECEIVED_TEST_INDEX])) and
in_past_week(result, now, ordered_before=now - _THIRTY_SIX_HOURS_AGO))
code_dao = CodeDao()
race_question_code = code_dao.get_code(PPI_SYSTEM, RACE_QUESTION_CODE)
native_american_race_code = code_dao.get_code(PPI_SYSTEM, RACE_AIAN_CODE)
# Open three files and a database session; run the reconciliation query and pipe the output
# to the files, using per-file predicates to filter out results.
with exporter.open_writer(path_received, received_predicate) as received_writer, \
exporter.open_writer(path_late, late_predicate) as late_writer, \
exporter.open_writer(path_missing, missing_predicate) as missing_writer, \
database_factory.get_database().session() as session:
writer = CompositeSqlExportWriter(
[received_writer, late_writer, missing_writer])
exporter.run_export_with_session(
writer, session, replace_isodate(_RECONCILIATION_REPORT_SQL), {
'race_question_code_id': race_question_code.codeId,
'native_american_race_code_id':
native_american_race_code.codeId,
'biobank_id_prefix': get_biobank_id_prefix(),
'pmi_ops_system': _PMI_OPS_SYSTEM,
'kit_id_system': _KIT_ID_SYSTEM,
'tracking_number_system': _TRACKING_NUMBER_SYSTEM
})
# Now generate the withdrawal report.
exporter.run_export(
path_withdrawals, replace_isodate(_WITHDRAWAL_REPORT_SQL), {
'race_question_code_id': race_question_code.codeId,
'native_american_race_code_id': native_american_race_code.codeId,
'seven_days_ago': now - datetime.timedelta(days=7),
'biobank_id_prefix': get_biobank_id_prefix()
})
def _get_participant_sql(num_shards, shard_number):
module_time_fields = [
'ISODATE[ps.{0}] {0}'.format(
get_column_name(ParticipantSummary, field_name + 'Time'))
for field_name in QUESTIONNAIRE_MODULE_FIELD_NAMES
]
modules_sql = ', '.join(module_time_fields)
dna_tests_sql, params = get_sql_and_params_for_array(
config.getSettingList(config.DNA_SAMPLE_TEST_CODES), 'dna')
params.update(_get_params(num_shards, shard_number))
return replace_isodate(
_PARTICIPANT_SQL_TEMPLATE.format(dna_tests_sql, modules_sql)), params
def _get_answer_sql(num_shards, shard_number):
code_dao = CodeDao()
code_ids = []
question_codes = list(ANSWER_FIELD_TO_QUESTION_CODE.values())
question_codes.append(RACE_QUESTION_CODE)
question_codes.append(EHR_CONSENT_QUESTION_CODE)
for code_value in question_codes:
code = code_dao.get_code(PPI_SYSTEM, code_value)
code_ids.append(str(code.codeId))
params = _get_params(num_shards, shard_number)
params['unmapped'] = UNMAPPED
return replace_isodate(_ANSWER_QUERY.format((','.join(code_ids)))), params
def _get_participant_sql(num_shards, shard_number):
module_time_fields = [
'(CASE WHEN ps.{0} = :submitted THEN ISODATE[ps.{1}] ELSE NULL END) {1}'
.format(get_column_name(ParticipantSummary, field_name),
get_column_name(ParticipantSummary, field_name + 'Time'))
for field_name in NON_EHR_QUESTIONNAIRE_MODULE_FIELD_NAMES
]
modules_sql = ', '.join(module_time_fields)
dna_tests_sql, params = get_sql_and_params_for_array(
config.getSettingList(config.DNA_SAMPLE_TEST_CODES), 'dna')
params.update(_get_params(num_shards, shard_number))
params['submitted'] = int(QuestionnaireStatus.SUBMITTED)
return replace_isodate(
_PARTICIPANT_SQL_TEMPLATE.format(dna_tests_sql, modules_sql)), params
def _get_hpo_id_sql(num_shards, shard_number):
return replace_isodate(_HPO_ID_QUERY), _get_params(num_shards,
shard_number)