def update_from_biobank_stored_samples(self, participant_id=None):
"""Rewrites sample-related summary data. Call this after updating BiobankStoredSamples.
If participant_id is provided, only that participant will have their summary updated."""
baseline_tests_sql, baseline_tests_params = get_sql_and_params_for_array(
config.getSettingList(config.BASELINE_SAMPLE_TEST_CODES),
'baseline')
dna_tests_sql, dna_tests_params = get_sql_and_params_for_array(
config.getSettingList(config.DNA_SAMPLE_TEST_CODES), 'dna')
sample_sql, sample_params = _get_sample_sql_and_params()
sql = """
UPDATE
participant_summary
SET
num_baseline_samples_arrived = (
SELECT
COUNT(*)
FROM
biobank_stored_sample
WHERE
biobank_stored_sample.biobank_id = participant_summary.biobank_id
AND biobank_stored_sample.test IN %s
),
samples_to_isolate_dna = (
CASE WHEN EXISTS(SELECT * FROM biobank_stored_sample
WHERE biobank_stored_sample.biobank_id = participant_summary.biobank_id
AND biobank_stored_sample.test IN %s)
THEN :received ELSE :unset END
),
last_modified = :now
%s""" % (baseline_tests_sql, dna_tests_sql, sample_sql)
params = {
'received': int(SampleStatus.RECEIVED),
'unset': int(SampleStatus.UNSET),
'now': clock.CLOCK.now()
}
params.update(baseline_tests_params)
params.update(dna_tests_params)
params.update(sample_params)
enrollment_status_params = {
'submitted': int(QuestionnaireStatus.SUBMITTED),
'num_baseline_ppi_modules': self._get_num_baseline_ppi_modules(),
'completed': int(PhysicalMeasurementsStatus.COMPLETED),
'received': int(SampleStatus.RECEIVED),
'full_participant': int(EnrollmentStatus.FULL_PARTICIPANT),
'member': int(EnrollmentStatus.MEMBER),
'interested': int(EnrollmentStatus.INTERESTED)
}
enrollment_status_sql = _ENROLLMENT_STATUS_SQL
# If participant_id is provided, add the participant ID filter to both update statements.
if participant_id:
sql += _PARTICIPANT_ID_FILTER
params['participant_id'] = participant_id
enrollment_status_sql += _PARTICIPANT_ID_FILTER
enrollment_status_params['participant_id'] = participant_id
sql = replace_null_safe_equals(sql)
with self.session() as session:
session.execute(sql, params)
session.execute(enrollment_status_sql, enrollment_status_params)
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_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 _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))
# 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),
{"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.
code_dao = CodeDao()
race_question_code = code_dao.get_code(PPI_SYSTEM, RACE_QUESTION_CODE)
race_code_ids = []
for code_value in config.getSettingList(config.NATIVE_AMERICAN_RACE_CODES):
code = code_dao.get_code(PPI_SYSTEM, code_value)
race_code_ids.append(str(code.codeId))
race_codes_sql, params = get_sql_and_params_for_array(race_code_ids, 'race')
withdrawal_sql = _WITHDRAWAL_REPORT_SQL.format(race_codes_sql)
params['race_question_code_id'] = race_question_code.codeId
params['seven_days_ago'] = now - datetime.timedelta(days=7)
params['biobank_id_prefix'] = get_biobank_id_prefix()
exporter.run_export(path_withdrawals, replace_isodate(withdrawal_sql), params)
def _get_baseline_sql_and_params():
tests_sql, params = get_sql_and_params_for_array(
config.getSettingList(config.BASELINE_SAMPLE_TEST_CODES), 'baseline')
return ("""
(
SELECT
COUNT(*)
FROM
biobank_stored_sample
WHERE
biobank_stored_sample.biobank_id = participant_summary.biobank_id
AND biobank_stored_sample.confirmed IS NOT NULL
AND biobank_stored_sample.test IN %s
)
""" % (tests_sql), params)
def _get_dna_isolates_sql_and_params():
tests_sql, params = get_sql_and_params_for_array(
config.getSettingList(config.DNA_SAMPLE_TEST_CODES), 'dna')
params.update({
'received': int(SampleStatus.RECEIVED),
'unset': int(SampleStatus.UNSET)
})
return ("""
(
CASE WHEN EXISTS(SELECT * FROM biobank_stored_sample
WHERE biobank_stored_sample.biobank_id = participant_summary.biobank_id
AND biobank_stored_sample.confirmed IS NOT NULL
AND biobank_stored_sample.test IN %s)
THEN :received ELSE :unset END
)
""" % (tests_sql), params)
