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))