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_sample_status_time_sql_and_params():
"""Gets SQL that to update enrollmentStatusCoreStoredSampleTime field
on the participant summary.
"""
dns_test_list = config.getSettingList(config.DNA_SAMPLE_TEST_CODES)
status_time_sql = '%s' % ','.join([
"""COALESCE(sample_status_%s_time, '3000-01-01')""" % item
for item in dns_test_list
])
baseline_ppi_module_fields = config.getSettingList(
config.BASELINE_PPI_QUESTIONNAIRE_FIELDS, [])
baseline_ppi_module_sql = '%s' % ','.join([
"""%s_time""" % re.sub('(?<!^)(?=[A-Z])', '_', item).lower()
for item in baseline_ppi_module_fields
])
sub_sql = """
SELECT
participant_id,
GREATEST(
CASE WHEN enrollment_status_member_time IS NOT NULL THEN enrollment_status_member_time
ELSE consent_for_electronic_health_records_time
END,
physical_measurements_finalized_time,
{baseline_ppi_module_sql},
CASE WHEN
LEAST(
{status_time_sql}
) = '3000-01-01' THEN NULL
ELSE LEAST(
{status_time_sql}
)
END
) AS new_core_stored_sample_time
FROM
participant_summary
""".format(status_time_sql=status_time_sql,
baseline_ppi_module_sql=baseline_ppi_module_sql)
sql = """
UPDATE
participant_summary AS a
INNER JOIN ({sub_sql}) AS b ON a.participant_id = b.participant_id
SET
a.enrollment_status_core_stored_sample_time = b.new_core_stored_sample_time
WHERE a.enrollment_status = 3
AND a.enrollment_status_core_stored_sample_time IS NULL
""".format(sub_sql=sub_sql)
return sql
def alert_on_exceptions_wrapper(*args, **kwargs):
try:
return func(*args, **kwargs)
except biobank_samples_pipeline.DataError as e:
# This is for CSVs older than 24h; we only want to send alerts in prod, where we expect
# regular CSV uploads. In other environments, it's OK to just abort the CSV import if there's
# no new data.
biobank_recipients = config.getSettingList(
config.BIOBANK_STATUS_MAIL_RECIPIENTS, default=[])
if not e.external or (e.external and biobank_recipients):
send_failure_alert(
func.__name__,
'Data error in Biobank samples pipeline: %s' % e,
log_exc_info=True,
extra_recipients=biobank_recipients)
else:
# Don't alert for stale CSVs except in prod (where external recipients are configured).
logging.info('Not alerting on external-only DataError (%s).',
e)
return json.dumps({'data_error': str(e)})
except:
send_failure_alert(
func.__name__,
'Exception in cron: %s' % traceback.format_exc())
raise
def process_genotyping_manifest_files():
bucket_names = config.getSettingList(config.GENOMIC_CENTER_BUCKET_NAME)
genotyping_folder_name = config.getSetting(
GENOMIC_GENOTYPING_SAMPLE_MANIFEST_FOLDER_NAME)
for bucket_name in bucket_names:
process_genotyping_manifest_file_from_bucket(bucket_name,
genotyping_folder_name)
def num_baseline_samples_arrived(summary):
baseline_sample_test_codes = config.getSettingList(
config.BASELINE_SAMPLE_TEST_CODES, [])
samples_arrived = summary.get('samplesArrived')
if not samples_arrived:
return extraction.ExtractionResult(0)
count = sum(1 for test_code in baseline_sample_test_codes
if test_code in samples_arrived)
return extraction.ExtractionResult(count)
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 delete_service_account_keys():
days_to_delete = config.getSetting(config.DAYS_TO_DELETE_KEYS)
service_accounts_with_long_lived_keys = config.getSettingList(
config.SERVICE_ACCOUNTS_WITH_LONG_LIVED_KEYS, default=[])
app_id = app_identity.get_application_id()
if app_id is None:
return
project_name = 'projects/' + app_id
try:
service = discovery.build('iam', 'v1')
request = service.projects().serviceAccounts().list(name=project_name)
response = request.execute()
accounts = response['accounts']
for account in accounts:
if account['email'] in service_accounts_with_long_lived_keys:
logging.info(
'Skip key expiration check for Service Account {}'.format(
account))
continue
serviceaccount = project_name + '/serviceAccounts/' + account[
'email']
request = service.projects().serviceAccounts().keys().list(
name=serviceaccount, keyTypes='USER_MANAGED')
response = request.execute()
if 'keys' in response:
keys = response['keys']
for key in keys:
keyname = key['name']
startdate = datetime.strptime(key['validAfterTime'],
'%Y-%m-%dT%H:%M:%SZ')
key_age_days = (datetime.utcnow() - startdate).days
if key_age_days >= days_to_delete:
logging.warning(
'Deleting service Account key older than {} days [{}]: {}'
.format(days_to_delete, key_age_days, keyname))
delete_request = service.projects().serviceAccounts(
).keys().delete(name=keyname)
delete_request.execute()
else:
logging.info(
'Service Account key is {} days old: {}'.format(
key_age_days, keyname))
except KeyError:
logging.info(
'No Service Accounts found in project "{}"'.format(app_id))
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)
def send_failure_alert(job_name,
message,
log_exc_info=False,
extra_recipients=None):
"""Sends an alert email for a failed job."""
subject = '%s failed in %s' % (job_name, app_identity.get_application_id())
# This sender needs to be authorized per-environment in Email Authorized Senders,
# see https://cloud.google.com/appengine/docs/standard/python/mail/.
sender = config.getSetting(config.INTERNAL_STATUS_MAIL_SENDER)
to_list = config.getSettingList(config.INTERNAL_STATUS_MAIL_RECIPIENTS)
if extra_recipients is not None:
to_list += extra_recipients
logging.error('%s: %s (email will be sent from %r to %r)',
subject,
message,
sender,
to_list,
exc_info=log_exc_info)
mail.send_mail(sender, to_list, subject, message)
def calculate_max_core_sample_time(self,
participant_summary,
field_name_prefix='sampleStatus'):
keys = [
field_name_prefix + '%sTime' % test
for test in config.getSettingList(config.DNA_SAMPLE_TEST_CODES)
]
sample_time_list = \
[v for k, v in participant_summary if k in keys and v is not None]
sample_time = min(sample_time_list) if sample_time_list else None
if sample_time is not None:
return max([
sample_time, participant_summary.enrollmentStatusMemberTime,
participant_summary.questionnaireOnTheBasicsTime,
participant_summary.questionnaireOnLifestyleTime,
participant_summary.questionnaireOnOverallHealthTime,
participant_summary.physicalMeasurementsFinalizedTime
])
else:
return None
def count_completed_ppi_modules(participant_summary):
ppi_module_fields = config.getSettingList(config.PPI_QUESTIONNAIRE_FIELDS,
[])
return sum(1 for field in ppi_module_fields if getattr(
participant_summary, field) == QuestionnaireStatus.SUBMITTED)
def _prep_biobank_info(self, p_id, ro_session):
"""
Look up biobank orders
:param p_id: participant id
:param ro_session: Readonly DAO session object
:return:
"""
data = {}
orders = list()
baseline_tests = [
"1ED04", "1ED10", "1HEP4", "1PST8", "2PST8", "1SST8", "2SST8",
"1PS08", "1SS08", "1UR10", "1CFD9", "1PXR2", "1UR90", "2ED10"
]
try:
baseline_tests = config.getSettingList(
'baseline_sample_test_codes')
except ValueError:
pass
except AssertionError: # unittest errors because of GCP SDK
pass
dna_tests = ["1ED10", "2ED10", "1ED04", "1SAL", "1SAL2"]
try:
dna_tests = config.getSettingList('dna_sample_test_codes')
except ValueError:
pass
except AssertionError: # unittest errors because of GCP SDK
pass
sql = """
select bo.biobank_order_id, bo.created, bo.collected_site_id, bo.processed_site_id, bo.finalized_site_id,
bos.test, bos.collected, bos.processed, bos.finalized, bo.order_status,
bss.confirmed as bb_confirmed, bss.created as bb_created, bss.disposed as bb_disposed,
bss.status as bb_status, (
select count(1) from biobank_dv_order bdo where bdo.biobank_order_id = bo.biobank_order_id
) as dv_order
from biobank_order bo inner join biobank_ordered_sample bos on bo.biobank_order_id = bos.order_id
inner join biobank_order_identifier boi on bo.biobank_order_id = boi.biobank_order_id
left outer join
biobank_stored_sample bss on boi.`value` = bss.biobank_order_identifier and bos.test = bss.test
where boi.`system` = 'https://www.pmi-ops.org' and bo.participant_id = :pid
order by bo.biobank_order_id, bos.test;
"""
cursor = ro_session.execute(sql, {'pid': p_id})
results = [r for r in cursor]
# loop through results and create one order record for each biobank_order_id value.
for row in results:
if not filter(
lambda order: order['bbo_biobank_order_id'] == row.
biobank_order_id, orders):
orders.append({
'bbo_biobank_order_id':
row.biobank_order_id,
'bbo_created':
row.created,
'bbo_status':
str(
BiobankOrderStatus(row.order_status) if row.
order_status else BiobankOrderStatus.UNSET),
'bbo_status_id':
int(
BiobankOrderStatus(row.order_status) if row.
order_status else BiobankOrderStatus.UNSET),
'bbo_dv_order':
0 if row.dv_order == 0 else 1, # Boolean field
'bbo_collected_site':
self._lookup_site_name(row.collected_site_id, ro_session),
'bbo_collected_site_id':
row.collected_site_id,
'bbo_processed_site':
self._lookup_site_name(row.processed_site_id, ro_session),
'bbo_processed_site_id':
row.processed_site_id,
'bbo_finalized_site':
self._lookup_site_name(row.finalized_site_id, ro_session),
'bbo_finalized_site_id':
row.finalized_site_id,
})
# loop through results again and add each sample to it's order.
for row in results:
# get the order list index for this sample record
try:
idx = orders.index(
filter(
lambda order: order['bbo_biobank_order_id'] == row.
biobank_order_id, orders)[0])
except IndexError:
continue
# if we haven't added any samples to this order, create an empty list.
if 'bbo_samples' not in orders[idx]:
orders[idx]['bbo_samples'] = list()
# append the sample to the order
orders[idx]['bbo_samples'].append({
'bbs_test':
row.test,
'bbs_baseline_test':
1 if row.test in baseline_tests else 0, # Boolean field
'bbs_dna_test':
1 if row.test in dna_tests else 0, # Boolean field
'bbs_collected':
row.collected,
'bbs_processed':
row.processed,
'bbs_finalized':
row.finalized,
'bbs_confirmed':
row.bb_confirmed,
'bbs_status':
str(SampleStatus.RECEIVED) if row.bb_confirmed else None,
'bbs_status_id':
int(SampleStatus.RECEIVED) if row.bb_confirmed else None,
'bbs_created':
row.bb_created,
'bbs_disposed':
row.bb_disposed,
'bbs_disposed_reason':
str(SampleStatus(row.bb_status)) if row.bb_status else None,
'bbs_disposed_reason_id':
int(SampleStatus(row.bb_status)) if row.bb_status else None,
})
if len(orders) > 0:
data['biobank_orders'] = orders
return data
def _prep_modules(self, p_id, ro_session):
"""
Find all questionnaire modules the participant has completed and loop through them.
:param p_id: participant id
:param ro_session: Readonly DAO session object
:return: dict
"""
code_id_query = ro_session.query(func.max(QuestionnaireConcept.codeId)).\
filter(QuestionnaireResponse.questionnaireId ==
QuestionnaireConcept.questionnaireId).label('codeId')
query = ro_session.query(
QuestionnaireResponse.questionnaireResponseId, QuestionnaireResponse.authored,
QuestionnaireResponse.created, QuestionnaireResponse.language, code_id_query).\
filter(QuestionnaireResponse.participantId == p_id).\
order_by(QuestionnaireResponse.questionnaireResponseId)
# sql = self.dao.query_to_text(query)
results = query.all()
data = dict()
modules = list()
consents = list()
baseline_modules = ['TheBasics', 'OverallHealth', 'Lifestyle']
try:
baseline_modules = config.getSettingList(
'baseline_ppi_questionnaire_fields')
except ValueError:
pass
except AssertionError: # unittest errors because of GCP SDK
pass
consent_modules = {
# module: question code string
'DVEHRSharing': 'DVEHRSharing_AreYouInterested',
'EHRConsentPII': 'EHRConsentPII_ConsentPermission',
}
if results:
for row in results:
module_name = self._lookup_code_value(row.codeId, ro_session)
modules.append({
'mod_module':
module_name,
'mod_baseline_module':
1
if module_name in baseline_modules else 0, # Boolean field
'mod_authored':
row.authored,
'mod_created':
row.created,
'mod_language':
row.language,
'mod_status':
BQModuleStatusEnum.SUBMITTED.name,
'mod_status_id':
BQModuleStatusEnum.SUBMITTED.value,
})
# check if this is a module with consents.
if module_name not in consent_modules:
continue
qnans = self.ro_dao.call_proc('sp_get_questionnaire_answers',
args=[module_name, p_id])
if qnans and len(qnans) > 0:
qnan = BQRecord(
schema=None,
data=qnans[0]) # use only most recent questionnaire.
consents.append({
'consent':
consent_modules[module_name],
'consent_id':
self._lookup_code_id(consent_modules[module_name],
ro_session),
'consent_date':
parser.parse(qnan.authored).date()
if qnan.authored else None,
'consent_value':
qnan[consent_modules[module_name]],
'consent_value_id':
self._lookup_code_id(
qnan[consent_modules[module_name]], ro_session),
})
if len(modules) > 0:
data['modules'] = modules
if len(consents) > 0:
data['consents'] = consents
return data
def _calculate_enrollment_status(self, summary):
"""
Calculate the participant's enrollment status
:param summary: summary data
:return: dict
"""
if 'consents' not in summary:
return {}
try:
baseline_modules = config.getSettingList(
'baseline_ppi_questionnaire_fields')
except ValueError:
baseline_modules = ['TheBasics', 'OverallHealth', 'Lifestyle']
study_consent = ehr_consent = dvehr_consent = pm_complete = False
status = None
# iterate over consents
for consent in summary['consents']:
if consent['consent'] == 'ConsentPII':
study_consent = True
if consent['consent'] == 'EHRConsentPII_ConsentPermission' and \
consent['consent_value'] == 'ConsentPermission_Yes':
ehr_consent = True
if consent['consent'] == 'DVEHRSharing_AreYouInterested' and \
consent['consent_value'] == 'DVEHRSharing_Yes':
dvehr_consent = True
# check physical measurements
if 'pm' in summary and summary['pm']:
for row in summary['pm']:
if row['pm_status_id'] == int(
PhysicalMeasurementsStatus.COMPLETED):
pm_complete = True
break
baseline_module_count = dna_sample_count = 0
if 'modules' in summary:
baseline_module_count = len(
filter(lambda module: module['mod_baseline_module'] == 1,
summary['modules']))
if 'biobank_orders' in summary:
for order in summary['biobank_orders']:
if 'bbo_samples' in order:
dna_sample_count += len(
filter(lambda sample: sample['bbs_dna_test'] == 1,
order['bbo_samples']))
if study_consent:
status = EnrollmentStatus.INTERESTED
if ehr_consent or dvehr_consent:
status = EnrollmentStatus.MEMBER
if pm_complete and 'modules' in summary and baseline_module_count == len(baseline_modules) and \
dna_sample_count > 0:
status = EnrollmentStatus.FULL_PARTICIPANT
# TODO: Get Enrollment dates for additional fields -> participant_summary_dao.py:499
# TODO: Calculate EHR status and dates -> participant_summary_dao.py:707
data = {
'enrollment_status': str(status) if status else None,
'enrollment_status_id': int(status) if status else None,
}
return data
def num_completed_baseline_ppi_modules(summary):
baseline_ppi_module_fields = config.getSettingList(
config.BASELINE_PPI_QUESTIONNAIRE_FIELDS, [])
count = sum(1 for field in baseline_ppi_module_fields
if summary.get(field) == 'SUBMITTED')
return extraction.ExtractionResult(count)
def _get_organization_metrics_query(cutoff_date, organization_ids=None):
def make_sum_bool_field(condition_expression):
return sqlalchemy.func.cast(
sqlalchemy.func.sum(
sqlalchemy.func.if_(condition_expression, 1, 0)),
sqlalchemy.Integer)
ppi_baseline_module_count = len(
config.getSettingList(config.BASELINE_PPI_QUESTIONNAIRE_FIELDS))
can_be_included = ((ParticipantSummary.withdrawalStatus
== WithdrawalStatus.NOT_WITHDRAWN)
& Participant.isGhostId.isnot(True))
# condition expression components
was_signed_up = Participant.signUpTime <= cutoff_date
had_consented_for_study = (
(ParticipantSummary.consentForStudyEnrollment
== QuestionnaireStatus.SUBMITTED)
&
(ParticipantSummary.consentForStudyEnrollmentTime <= cutoff_date))
had_consented_for_ehr = (
(ParticipantSummary.consentForElectronicHealthRecords
== QuestionnaireStatus.SUBMITTED)
& (ParticipantSummary.consentForElectronicHealthRecordsTime <=
cutoff_date))
had_completed_ppi = (ParticipantSummary.numCompletedBaselinePPIModules
>= ppi_baseline_module_count)
had_physical_measurements = ParticipantSummary.physicalMeasurementsFinalizedTime <= cutoff_date
had_biosample = ParticipantSummary.biospecimenOrderTime <= cutoff_date
had_ehr_receipt = ((ParticipantSummary.ehrStatus == EhrStatus.PRESENT)
&
(ParticipantSummary.ehrReceiptTime <= cutoff_date))
# condition expressions
was_participant = can_be_included & was_signed_up
was_primary = was_participant & had_consented_for_study
was_ehr_consented = was_primary & had_consented_for_ehr
was_core = (was_ehr_consented
& had_completed_ppi
& had_physical_measurements
& had_biosample)
had_ehr_data = was_ehr_consented & had_ehr_receipt
# build query
receipt_subquery = (sqlalchemy.select([
EhrReceipt.organizationId.label('organization_id'),
sqlalchemy.func.max(
EhrReceipt.receiptTime).label('ehr_receipt_time')
]).select_from(EhrReceipt).group_by(
EhrReceipt.organizationId).alias('receipt_subquery'))
fields = [
Organization.externalId.label('organization_id'),
Organization.displayName.label('organization_name'),
make_sum_bool_field(was_participant).label('total_participants'),
make_sum_bool_field(was_primary).label('total_primary_consented'),
make_sum_bool_field(was_ehr_consented).label(
'total_ehr_consented'),
make_sum_bool_field(was_core).label('total_core_participants'),
make_sum_bool_field(had_ehr_data).label('total_ehr_data_received'),
sqlalchemy.func.date(receipt_subquery.c.ehr_receipt_time).label(
'last_ehr_submission_date'),
]
joined_tables = sqlalchemy.join(
sqlalchemy.join(
sqlalchemy.outerjoin(
Organization,
receipt_subquery,
Organization.organizationId ==
receipt_subquery.c.organization_id,
), ParticipantSummary, ParticipantSummary.organizationId ==
Organization.organizationId), Participant,
Participant.participantId == ParticipantSummary.participantId)
query = (sqlalchemy.select(fields).select_from(joined_tables).group_by(
Organization.organizationId))
if organization_ids:
query = query.where(
Organization.organizationId.in_(organization_ids))
return query
def _get_baseline_ppi_module_fields(): return config.getSettingList(config.BASELINE_PPI_QUESTIONNAIRE_FIELDS, [])
def _get_num_baseline_ppi_modules(self):
return len(
config.getSettingList(config.BASELINE_PPI_QUESTIONNAIRE_FIELDS))
