class SiteDaoTest(SqlTestBase):
def setUp(self):
super(SiteDaoTest, self).setUp()
self.site_dao = SiteDao()
def test_get_no_sites(self):
self.assertIsNone(self.site_dao.get(9999))
self.assertIsNone(
self.site_dao.get_by_google_group('*****@*****.**'))
def test_insert(self):
site = Site(siteName='site',
googleGroup='*****@*****.**',
consortiumName='consortium',
mayolinkClientNumber=12345,
hpoId=PITT_HPO_ID)
created_site = self.site_dao.insert(site)
new_site = self.site_dao.get(created_site.siteId)
site.siteId = created_site.siteId
self.assertEquals(site.asdict(), new_site.asdict())
self.assertEquals(
site.asdict(),
self.site_dao.get_by_google_group(
'*****@*****.**').asdict())
def test_update(self):
site = Site(siteName='site',
googleGroup='*****@*****.**',
consortiumName='consortium',
mayolinkClientNumber=12345,
hpoId=PITT_HPO_ID)
created_site = self.site_dao.insert(site)
new_site = Site(siteId=created_site.siteId,
siteName='site2',
googleGroup='*****@*****.**',
consortiumName='consortium2',
mayolinkClientNumber=123456,
hpoId=UNSET_HPO_ID)
self.site_dao.update(new_site)
fetched_site = self.site_dao.get(created_site.siteId)
self.assertEquals(new_site.asdict(), fetched_site.asdict())
self.assertEquals(
new_site.asdict(),
self.site_dao.get_by_google_group(
'*****@*****.**').asdict())
self.assertIsNone(
self.site_dao.get_by_google_group('*****@*****.**'))
def get_site_id_by_site_value(obj):
if 'site' in obj:
from dao.site_dao import SiteDao
site_dao = SiteDao()
site = site_dao.get_by_google_group(obj['site']['value'])
if site is not None:
return site.siteId
return None
class SiteImporter(CsvImporter):
def __init__(self):
args = parser.parse_args()
self.organization_dao = OrganizationDao()
self.stub_geocoding = args.stub_geocoding
self.ACTIVE = SiteStatus.ACTIVE
self.status_exception_list = ['hpo-site-walgreensphoenix']
self.instance = args.instance
self.creds_file = args.creds_file
self.new_sites_list = []
self.project = None
if args.project:
self.project = args.project
if self.project in ENV_LIST:
self.environment = ' ' + self.project.split('-')[-1].upper()
else:
self.environment = ' ' + ENV_TEST.split('-')[-1].upper()
super(SiteImporter,
self).__init__('site', SiteDao(), 'siteId', 'googleGroup', [
SITE_ORGANIZATION_ID_COLUMN, SITE_SITE_ID_COLUMN,
SITE_SITE_COLUMN, SITE_STATUS_COLUMN + self.environment,
ENROLLING_STATUS_COLUMN + self.environment,
DIGITAL_SCHEDULING_STATUS_COLUMN + self.environment
])
def run(self, filename, dry_run):
super(SiteImporter, self).run(filename, dry_run)
insert_participants = False
if not dry_run:
if self.environment:
current_env = ENV_STABLE
if self.environment.strip() == 'STABLE' and len(
self.new_sites_list) > 0:
from googleapiclient.discovery import build
logging.info(
'Starting reboot of app instances to insert new test participants'
)
service = build('appengine', 'v1', cache_discovery=False)
request = service.apps().services().versions().list(
appsId=current_env, servicesId='default')
versions = request.execute()
for version in versions['versions']:
if version['servingStatus'] == 'SERVING':
_id = version['id']
request = service.apps().services().versions(
).instances().list(servicesId='default',
versionsId=_id,
appsId=current_env)
instances = request.execute()
try:
for instance in instances['instances']:
sha = instance['name'].split('/')[-1]
delete_instance = service.apps().services(
).versions().instances().delete(
appsId=current_env,
servicesId='default',
versionsId=_id,
instancesId=sha)
response = delete_instance.execute()
if response['done']:
insert_participants = True
logging.info(
'Reboot of instance: %s in stable complete.',
instance['name'])
else:
logging.warn(
'Not able to reboot instance on server, Error: %s',
response)
except KeyError:
logging.warn('No running instance for %s',
version['name'])
if insert_participants:
logging.info('Starting import of test participants.')
self._insert_new_participants(self.new_sites_list)
def delete_sql_statement(self, session, str_list):
sql = """
DELETE FROM site
WHERE site_id IN ({str_list})
AND NOT EXISTS(
SELECT * FROM participant WHERE site_id = site.site_id)
AND NOT EXISTS(
SELECT * FROM participant_summary WHERE site_id = site.site_id
OR physical_measurements_finalized_site_id = site.site_id
OR physical_measurements_created_site_id = site.site_id
OR biospecimen_source_site_id = site.site_id
OR biospecimen_collected_site_id = site.site_id
OR biospecimen_processed_site_id = site.site_id
OR biospecimen_finalized_site_id = site.site_id
)
AND NOT EXISTS(
SELECT * FROM participant_history WHERE site_id = site.site_id)
AND NOT EXISTS(
SELECT * FROM physical_measurements WHERE created_site_id = site.site_id
OR finalized_site_id = site.site_id)
AND NOT EXISTS(
SELECT * FROM biobank_order WHERE finalized_site_id = site.site_id
OR source_site_id = site.site_id
OR collected_site_id = site.site_id
OR processed_site_id = site.site_id
)
""".format(str_list=str_list)
session.execute(sql)
def _cleanup_old_entities(self, session, row_list, dry_run):
log_prefix = '(dry run) ' if dry_run else ''
self.site_dao = SiteDao()
existing_sites = set(site.googleGroup
for site in self.site_dao.get_all())
site_group_list_from_sheet = [
str(row[SITE_SITE_ID_COLUMN].lower()) for row in row_list
]
sites_to_remove = existing_sites - set(site_group_list_from_sheet)
if sites_to_remove:
site_id_list = []
for site in sites_to_remove:
logging.info(
log_prefix +
'Deleting old Site no longer in Google sheet: %s', site)
old_site = self.site_dao.get_by_google_group(site)
if old_site and old_site.isObsolete != ObsoleteStatus.OBSOLETE:
site_id_list.append(old_site.siteId)
self.deletion_count += 1
elif old_site and old_site.isObsolete == ObsoleteStatus.OBSOLETE:
logging.info(
'Not attempting to delete site [%s] with existing obsolete status',
old_site.googleGroup)
if site_id_list and not dry_run:
str_list = ','.join([str(i) for i in site_id_list])
logging.info(log_prefix + 'Marking old site as obsolete : %s',
old_site)
sql = """ UPDATE site
SET is_obsolete = 1
WHERE site_id in ({site_id_list})""".format(site_id_list=str_list)
session.execute(sql)
self.site_dao._invalidate_cache()
# Try to delete old sites.
self.delete_sql_statement(session, str_list)
def _insert_new_participants(self, entity):
num_participants = 0
participants = {
'zip_code': '20001',
'date_of_birth': '1933-3-3',
'gender_identity': 'GenderIdentity_Woman',
'withdrawalStatus': 'NOT_WITHDRAWN',
'suspensionStatus': 'NOT_SUSPENDED'
}
client = Client('rdr/v1', False, self.creds_file, self.instance)
client_log.setLevel(logging.WARN)
questionnaire_to_questions, consent_questionnaire_id_and_version = _setup_questionnaires(
client)
consent_questions = questionnaire_to_questions[
consent_questionnaire_id_and_version]
for site in entity:
for participant, v in enumerate(range(1, 21), 1):
num_participants += 1
participant = participants
participant.update(
{'last_name': site.googleGroup.split('-')[-1]})
participant.update({'first_name': 'Participant {}'.format(v)})
participant.update({'site': site.googleGroup})
import_participant(participant, client,
consent_questionnaire_id_and_version,
questionnaire_to_questions,
consent_questions, num_participants)
logging.info('%d participants imported.' % num_participants)
def _entity_from_row(self, row):
google_group = row[SITE_SITE_ID_COLUMN].lower()
organization = self.organization_dao.get_by_external_id(
row[SITE_ORGANIZATION_ID_COLUMN].upper())
if organization is None:
logging.warn('Invalid organization ID %s importing site %s',
row[SITE_ORGANIZATION_ID_COLUMN].upper(),
google_group)
self.errors.append(
'Invalid organization ID {} importing site {}'.format(
row[SITE_ORGANIZATION_ID_COLUMN].upper(), google_group))
return None
launch_date = None
launch_date_str = row.get(SITE_LAUNCH_DATE_COLUMN)
if launch_date_str:
try:
launch_date = parse(launch_date_str).date()
except ValueError:
logging.warn('Invalid launch date %s for site %s',
launch_date_str, google_group)
self.errors.append('Invalid launch date {} for site {}'.format(
launch_date_str, google_group))
return None
name = row[SITE_SITE_COLUMN]
mayolink_client_number = None
mayolink_client_number_str = row.get(
SITE_MAYOLINK_CLIENT_NUMBER_COLUMN)
if mayolink_client_number_str:
try:
mayolink_client_number = int(mayolink_client_number_str)
except ValueError:
logging.warn('Invalid Mayolink Client # %s for site %s',
mayolink_client_number_str, google_group)
self.errors.append(
'Invalid Mayolink Client # {} for site {}'.format(
mayolink_client_number_str, google_group))
return None
notes = row.get(SITE_NOTES_COLUMN)
notes_es = row.get(SITE_NOTES_COLUMN_ES)
try:
site_status = SiteStatus(row[SITE_STATUS_COLUMN +
self.environment].upper())
except TypeError:
logging.warn('Invalid site status %s for site %s',
row[SITE_STATUS_COLUMN + self.environment],
google_group)
self.errors.append('Invalid site status {} for site {}'.format(
row[SITE_STATUS_COLUMN + self.environment], google_group))
return None
try:
enrolling_status = EnrollingStatus(row[ENROLLING_STATUS_COLUMN +
self.environment].upper())
except TypeError:
logging.warn('Invalid enrollment site status %s for site %s',
row[ENROLLING_STATUS_COLUMN + self.environment],
google_group)
self.errors.append(
'Invalid enrollment site status {} for site {}'.format(
row[ENROLLING_STATUS_COLUMN + self.environment],
google_group))
directions = row.get(SITE_DIRECTIONS_COLUMN)
physical_location_name = row.get(SITE_PHYSICAL_LOCATION_NAME_COLUMN)
address_1 = row.get(SITE_ADDRESS_1_COLUMN)
address_2 = row.get(SITE_ADDRESS_2_COLUMN)
city = row.get(SITE_CITY_COLUMN)
state = row.get(SITE_STATE_COLUMN)
zip_code = row.get(SITE_ZIP_COLUMN)
phone = row.get(SITE_PHONE_COLUMN)
admin_email_addresses = row.get(SITE_ADMIN_EMAIL_ADDRESSES_COLUMN)
link = row.get(SITE_LINK_COLUMN)
digital_scheduling_status = DigitalSchedulingStatus(
row[DIGITAL_SCHEDULING_STATUS_COLUMN + self.environment].upper())
schedule_instructions = row.get(SCHEDULING_INSTRUCTIONS)
schedule_instructions_es = row.get(SCHEDULING_INSTRUCTIONS_ES)
return Site(siteName=name,
googleGroup=google_group,
mayolinkClientNumber=mayolink_client_number,
organizationId=organization.organizationId,
hpoId=organization.hpoId,
siteStatus=site_status,
enrollingStatus=enrolling_status,
digitalSchedulingStatus=digital_scheduling_status,
scheduleInstructions=schedule_instructions,
scheduleInstructions_ES=schedule_instructions_es,
launchDate=launch_date,
notes=notes,
notes_ES=notes_es,
directions=directions,
physicalLocationName=physical_location_name,
address1=address_1,
address2=address_2,
city=city,
state=state,
zipCode=zip_code,
phoneNumber=phone,
adminEmails=admin_email_addresses,
link=link)
def _update_entity(self, entity, existing_entity, session, dry_run):
self._populate_lat_lng_and_time_zone(entity, existing_entity)
if entity.siteStatus == self.ACTIVE and (entity.latitude == None
or entity.longitude == None):
self.errors.append(
'Skipped active site without geocoding: {}'.format(
entity.googleGroup))
return None, True
return super(SiteImporter,
self)._update_entity(entity, existing_entity, session,
dry_run)
def _insert_entity(self, entity, existing_map, session, dry_run):
self._populate_lat_lng_and_time_zone(entity, None)
if entity.siteStatus == self.ACTIVE and (entity.latitude == None
or entity.longitude == None):
self.errors.append(
'Skipped active site without geocoding: {}'.format(
entity.googleGroup))
return False
self.new_sites_list.append(entity)
super(SiteImporter, self)._insert_entity(entity, existing_map, session,
dry_run)
def _populate_lat_lng_and_time_zone(self, site, existing_site):
if site.address1 and site.city and site.state:
if existing_site:
if (existing_site.address1 == site.address1
and existing_site.city == site.city
and existing_site.state == site.state
and existing_site.latitude is not None
and existing_site.longitude is not None
and existing_site.timeZoneId is not None):
# Address didn't change, use the existing lat/lng and time zone.
site.latitude = existing_site.latitude
site.longitude = existing_site.longitude
site.timeZoneId = existing_site.timeZoneId
return
if self.stub_geocoding:
# Set dummy latitude and longitude when importing sites locally / on a CircleCI box.
site.latitude = 32.176
site.longitude = -110.93
site.timeZoneId = 'America/Phoenix'
else:
latitude, longitude = self._get_lat_long_for_site(
site.address1, site.city, site.state)
site.latitude = latitude
site.longitude = longitude
if latitude and longitude:
site.timeZoneId = self._get_time_zone(latitude, longitude)
else:
if site.googleGroup not in self.status_exception_list:
if site.siteStatus == self.ACTIVE:
self.errors.append(
'Active site must have valid address. Site: {}, Group: {}'
.format(site.siteName, site.googleGroup))
def _get_lat_long_for_site(self, address_1, city, state):
self.full_address = address_1 + ' ' + city + ' ' + state
try:
self.api_key = os.environ.get('API_KEY')
self.gmaps = googlemaps.Client(key=self.api_key)
try:
geocode_result = self.gmaps.geocode(address_1 + '' + city +
' ' + state)[0]
except IndexError:
self.errors.append(
'Bad address for {}, could not geocode.'.format(
self.full_address))
return None, None
if geocode_result:
geometry = geocode_result.get('geometry')
if geometry:
location = geometry.get('location')
if location:
latitude = location.get('lat')
longitude = location.get('lng')
return latitude, longitude
else:
logging.warn('Can not find lat/long for %s',
self.full_address)
self.errors.append('Can not find lat/long for {}'.format(
self.full_address))
return None, None
else:
logging.warn('Geocode results failed for %s.',
self.full_address)
self.errors.append('Geocode results failed for {}'.format(
self.full_address))
return None, None
except ValueError as e:
logging.exception('Invalid geocode key: %s. ERROR: %s',
self.api_key, e)
self.errors.append('Invalid geocode key: {}. ERROR: {}'.format(
self.api_key, e))
return None, None
except IndexError as e:
logging.exception(
'Geocoding failure Check that address is correct. ERROR: %s',
e)
self.errors.append(
'Geocoding failured Check that address is correct. ERROR: {}'.
format(self.api_key, e))
return None, None
def _get_time_zone(self, latitude, longitude):
time_zone = self.gmaps.timezone(location=(latitude, longitude))
if time_zone['status'] == 'OK':
time_zone_id = time_zone['timeZoneId']
return time_zone_id
else:
logging.info('can not retrieve time zone from %s',
self.full_address)
self.errors.append('Can not retrieve time zone from {}'.format(
self.full_address))
return None
class SiteDaoTest(SqlTestBase):
def setUp(self):
super(SiteDaoTest, self).setUp()
self.site_dao = SiteDao()
self.participant_dao = ParticipantDao()
self.ps_dao = ParticipantSummaryDao()
self.ps_history = ParticipantHistoryDao()
def test_get_no_sites(self):
self.assertIsNone(self.site_dao.get(9999))
self.assertIsNone(
self.site_dao.get_by_google_group('*****@*****.**'))
def test_insert(self):
site = Site(siteName='site',
googleGroup='*****@*****.**',
mayolinkClientNumber=12345,
hpoId=PITT_HPO_ID)
created_site = self.site_dao.insert(site)
new_site = self.site_dao.get(created_site.siteId)
site.siteId = created_site.siteId
self.assertEquals(site.asdict(), new_site.asdict())
self.assertEquals(
site.asdict(),
self.site_dao.get_by_google_group(
'*****@*****.**').asdict())
def test_update(self):
site = Site(siteName='site',
googleGroup='*****@*****.**',
mayolinkClientNumber=12345,
hpoId=PITT_HPO_ID)
created_site = self.site_dao.insert(site)
new_site = Site(siteId=created_site.siteId,
siteName='site2',
googleGroup='*****@*****.**',
mayolinkClientNumber=123456,
hpoId=UNSET_HPO_ID)
self.site_dao.update(new_site)
fetched_site = self.site_dao.get(created_site.siteId)
self.assertEquals(new_site.asdict(), fetched_site.asdict())
self.assertEquals(
new_site.asdict(),
self.site_dao.get_by_google_group(
'*****@*****.**').asdict())
self.assertIsNone(
self.site_dao.get_by_google_group('*****@*****.**'))
def test_participant_pairing_updates_on_change(self):
TIME = datetime.datetime(2018, 1, 1)
TIME2 = datetime.datetime(2018, 1, 2)
provider_link = '[{"organization": {"reference": "Organization/AZ_TUCSON"}, "primary": true}]'
site = Site(siteName='site',
googleGroup='*****@*****.**',
mayolinkClientNumber=12345,
hpoId=PITT_HPO_ID,
organizationId=PITT_ORG_ID)
created_site = self.site_dao.insert(site)
with FakeClock(TIME):
p = Participant(participantId=1,
biobankId=2,
siteId=created_site.siteId)
self.participant_dao.insert(p)
fetch_p = self.participant_dao.get(p.participantId)
updated_p = self.participant_dao.get(fetch_p.participantId)
p_summary = self.ps_dao.insert(self.participant_summary(updated_p))
with FakeClock(TIME2):
update_site_parent = Site(siteId=created_site.siteId,
siteName='site2',
googleGroup='*****@*****.**',
mayolinkClientNumber=123456,
hpoId=AZ_HPO_ID,
organizationId=AZ_ORG_ID)
self.site_dao.update(update_site_parent)
updated_p = self.participant_dao.get(fetch_p.participantId)
ps = self.ps_dao.get(p_summary.participantId)
ph = self.ps_history.get([updated_p.participantId, 1])
self.assertEquals(update_site_parent.hpoId, updated_p.hpoId)
self.assertEquals(update_site_parent.organizationId,
updated_p.organizationId)
self.assertEquals(ps.organizationId, update_site_parent.organizationId)
self.assertEquals(ps.hpoId, update_site_parent.hpoId)
self.assertEquals(ps.organizationId, update_site_parent.organizationId)
self.assertEquals(ph.organizationId, update_site_parent.organizationId)
self.assertEquals(updated_p.providerLink, provider_link)
self.assertEquals(ps.lastModified, TIME2)
class ParticipantSummaryDao(UpdatableDao):
def __init__(self):
super(ParticipantSummaryDao,
self).__init__(ParticipantSummary,
order_by_ending=_ORDER_BY_ENDING)
self.hpo_dao = HPODao()
self.code_dao = CodeDao()
self.site_dao = SiteDao()
self.organization_dao = OrganizationDao()
def get_id(self, obj):
return obj.participantId
def _validate_update(self, session, obj, existing_obj): # pylint: disable=unused-argument
"""Participant summaries don't have a version value; drop it from validation logic."""
if not existing_obj:
raise NotFound('%s with id %s does not exist' %
(self.model_type.__name__, id))
def _has_withdrawn_filter(self, query):
for field_filter in query.field_filters:
if (field_filter.field_name == 'withdrawalStatus'
and field_filter.value == WithdrawalStatus.NO_USE):
return True
if field_filter.field_name == 'withdrawalTime' and field_filter.value is not None:
return True
return False
def _get_non_withdrawn_filter_field(self, query):
"""Returns the first field referenced in query filters which isn't in
WITHDRAWN_PARTICIPANT_FIELDS."""
for field_filter in query.field_filters:
if not field_filter.field_name in WITHDRAWN_PARTICIPANT_FIELDS:
return field_filter.field_name
return None
def _initialize_query(self, session, query_def):
non_withdrawn_field = self._get_non_withdrawn_filter_field(query_def)
if self._has_withdrawn_filter(query_def):
if non_withdrawn_field:
raise BadRequest(
"Can't query on %s for withdrawn participants" %
non_withdrawn_field)
# When querying for withdrawn participants, ensure that the only fields being filtered on or
# ordered by are in WITHDRAWN_PARTICIPANT_FIELDS.
return super(ParticipantSummaryDao,
self)._initialize_query(session, query_def)
else:
query = super(ParticipantSummaryDao,
self)._initialize_query(session, query_def)
if non_withdrawn_field:
# When querying on fields that aren't available for withdrawn participants,
# ensure that we only return participants
# who have not withdrawn or withdrew in the past 48 hours.
withdrawn_visible_start = clock.CLOCK.now(
) - WITHDRAWN_PARTICIPANT_VISIBILITY_TIME
return query.filter(
or_(
ParticipantSummary.withdrawalStatus !=
WithdrawalStatus.NO_USE,
ParticipantSummary.withdrawalTime >=
withdrawn_visible_start))
else:
# When querying on fields that are available for withdrawn participants, return everybody;
# withdrawn participants will have all but WITHDRAWN_PARTICIPANT_FIELDS cleared out 48
# hours after withdrawing.
return query
def _get_order_by_ending(self, query):
if self._has_withdrawn_filter(query):
return _WITHDRAWN_ORDER_BY_ENDING
return self.order_by_ending
def _add_order_by(self, query, order_by, field_names, fields):
if order_by.field_name in _CODE_FILTER_FIELDS:
return super(ParticipantSummaryDao, self)._add_order_by(
query, OrderBy(order_by.field_name + 'Id', order_by.ascending),
field_names, fields)
return super(ParticipantSummaryDao,
self)._add_order_by(query, order_by, field_names, fields)
def make_query_filter(self, field_name, value):
"""Handle HPO and code values when parsing filter values."""
if field_name == 'biobankId':
value = from_client_biobank_id(value, log_exception=True)
if field_name == 'hpoId' or field_name == 'awardee':
hpo = self.hpo_dao.get_by_name(value)
if not hpo:
raise BadRequest('No HPO found with name %s' % value)
if field_name == 'awardee':
field_name = 'hpoId'
return super(ParticipantSummaryDao,
self).make_query_filter(field_name, hpo.hpoId)
if field_name == 'organization':
organization = self.organization_dao.get_by_external_id(value)
if not organization:
raise BadRequest('No organization found with name %s' % value)
return super(ParticipantSummaryDao,
self).make_query_filter(field_name + 'Id',
organization.organizationId)
if field_name in _SITE_FIELDS:
if value == UNSET:
return super(ParticipantSummaryDao,
self).make_query_filter(field_name + 'Id', None)
site = self.site_dao.get_by_google_group(value)
if not site:
raise BadRequest('No site found with google group %s' % value)
return super(ParticipantSummaryDao,
self).make_query_filter(field_name + 'Id',
site.siteId)
if field_name in _CODE_FILTER_FIELDS:
if value == UNSET:
return super(ParticipantSummaryDao,
self).make_query_filter(field_name + 'Id', None)
# Note: we do not at present support querying for UNMAPPED code values.
code = self.code_dao.get_code(PPI_SYSTEM, value)
if not code:
raise BadRequest('No code found: %s' % value)
return super(ParticipantSummaryDao,
self).make_query_filter(field_name + 'Id',
code.codeId)
return super(ParticipantSummaryDao,
self).make_query_filter(field_name, value)
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."""
now = clock.CLOCK.now()
sample_sql, sample_params = _get_sample_sql_and_params(now)
baseline_tests_sql, baseline_tests_params = _get_baseline_sql_and_params(
)
dna_tests_sql, dna_tests_params = _get_dna_isolates_sql_and_params()
sample_status_time_sql = _get_sample_status_time_sql_and_params()
sample_status_time_params = {}
counts_sql = """
UPDATE
participant_summary
SET
num_baseline_samples_arrived = {baseline_tests_sql},
samples_to_isolate_dna = {dna_tests_sql},
last_modified = :now
WHERE
num_baseline_samples_arrived != {baseline_tests_sql} OR
samples_to_isolate_dna != {dna_tests_sql}
""".format(baseline_tests_sql=baseline_tests_sql,
dna_tests_sql=dna_tests_sql)
counts_params = {'now': now}
counts_params.update(baseline_tests_params)
counts_params.update(dna_tests_params)
enrollment_status_sql = _ENROLLMENT_STATUS_SQL
enrollment_status_params = {
'submitted': int(QuestionnaireStatus.SUBMITTED),
'unset': int(QuestionnaireStatus.UNSET),
'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),
'now': now
}
# If participant_id is provided, add the participant ID filter to all update statements.
if participant_id:
sample_sql += ' AND participant_id = :participant_id'
sample_params['participant_id'] = participant_id
counts_sql += ' AND participant_id = :participant_id'
counts_params['participant_id'] = participant_id
enrollment_status_sql += ' AND participant_id = :participant_id'
enrollment_status_params['participant_id'] = participant_id
sample_status_time_sql += ' AND a.participant_id = :participant_id'
sample_status_time_params['participant_id'] = participant_id
sample_sql = replace_null_safe_equals(sample_sql)
counts_sql = replace_null_safe_equals(counts_sql)
with self.session() as session:
session.execute(sample_sql, sample_params)
session.execute(counts_sql, counts_params)
session.execute(enrollment_status_sql, enrollment_status_params)
# TODO: Change this to the optimized sql in _update_dv_stored_samples()
session.execute(sample_status_time_sql, sample_status_time_params)
def _get_num_baseline_ppi_modules(self):
return len(
config.getSettingList(config.BASELINE_PPI_QUESTIONNAIRE_FIELDS))
def update_enrollment_status(self, summary):
"""Updates the enrollment status field on the provided participant summary to
the correct value based on the other fields on it. Called after
a questionnaire response or physical measurements are submitted."""
consent = (summary.consentForStudyEnrollment == QuestionnaireStatus.SUBMITTED and
summary.consentForElectronicHealthRecords == QuestionnaireStatus.SUBMITTED) or \
(summary.consentForStudyEnrollment == QuestionnaireStatus.SUBMITTED and
summary.consentForElectronicHealthRecords is None and
summary.consentForDvElectronicHealthRecordsSharing == QuestionnaireStatus.SUBMITTED)
enrollment_status = self.calculate_enrollment_status(
consent, summary.numCompletedBaselinePPIModules,
summary.physicalMeasurementsStatus, summary.samplesToIsolateDNA)
summary.enrollmentStatusMemberTime = self.calculate_member_time(
consent, summary)
summary.enrollmentStatusCoreOrderedSampleTime = self.calculate_core_ordered_sample_time(
consent, summary)
summary.enrollmentStatusCoreStoredSampleTime = self.calculate_core_stored_sample_time(
consent, summary)
# Update last modified date if status changes
if summary.enrollmentStatus != enrollment_status:
summary.lastModified = clock.CLOCK.now()
summary.enrollmentStatus = enrollment_status
def calculate_enrollment_status(self, consent,
num_completed_baseline_ppi_modules,
physical_measurements_status,
samples_to_isolate_dna):
if consent:
if (num_completed_baseline_ppi_modules
== self._get_num_baseline_ppi_modules()
and physical_measurements_status
== PhysicalMeasurementsStatus.COMPLETED
and samples_to_isolate_dna == SampleStatus.RECEIVED):
return EnrollmentStatus.FULL_PARTICIPANT
return EnrollmentStatus.MEMBER
return EnrollmentStatus.INTERESTED
def calculate_member_time(self, consent, participant_summary):
if consent and participant_summary.enrollmentStatusMemberTime is not None:
return participant_summary.enrollmentStatusMemberTime
elif consent:
if participant_summary.consentForElectronicHealthRecords is None and \
participant_summary.consentForDvElectronicHealthRecordsSharing == \
QuestionnaireStatus.SUBMITTED:
return participant_summary.consentForDvElectronicHealthRecordsSharingTime
return participant_summary.consentForElectronicHealthRecordsTime
else:
return None
def calculate_core_stored_sample_time(self, consent, participant_summary):
if consent and \
participant_summary.numCompletedBaselinePPIModules == \
self._get_num_baseline_ppi_modules() and \
participant_summary.physicalMeasurementsStatus == PhysicalMeasurementsStatus.COMPLETED and \
participant_summary.samplesToIsolateDNA == SampleStatus.RECEIVED:
max_core_sample_time = self.calculate_max_core_sample_time(
participant_summary, field_name_prefix='sampleStatus')
if max_core_sample_time and participant_summary.enrollmentStatusCoreStoredSampleTime:
return participant_summary.enrollmentStatusCoreStoredSampleTime
else:
return max_core_sample_time
else:
return None
def calculate_core_ordered_sample_time(self, consent, participant_summary):
if consent and \
participant_summary.numCompletedBaselinePPIModules == \
self._get_num_baseline_ppi_modules() and \
participant_summary.physicalMeasurementsStatus == PhysicalMeasurementsStatus.COMPLETED:
max_core_sample_time = self.calculate_max_core_sample_time(
participant_summary, field_name_prefix='sampleOrderStatus')
if max_core_sample_time and participant_summary.enrollmentStatusCoreOrderedSampleTime:
return participant_summary.enrollmentStatusCoreOrderedSampleTime
else:
return max_core_sample_time
else:
return None
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 calculate_distinct_visits(self,
pid,
finalized_time,
id_,
amendment=False):
""" Participants may get PM or biobank samples on same day. This should be considered as
a single visit in terms of program payment to participant.
return Boolean: true if there has not been an order on same date."""
from dao.biobank_order_dao import BiobankOrderDao
from dao.physical_measurements_dao import PhysicalMeasurementsDao
day_has_order, day_has_measurement = False, False
existing_orders = BiobankOrderDao().get_biobank_orders_for_participant(
pid)
ordered_samples = BiobankOrderDao(
).get_ordered_samples_for_participant(pid)
existing_measurements = PhysicalMeasurementsDao(
).get_measuremnets_for_participant(pid)
order_id_to_finalized_date = {
sample.biobankOrderId: sample.finalized.date()
for sample in ordered_samples if sample.finalized
}
if existing_orders and finalized_time:
for order in existing_orders:
order_finalized_date = order_id_to_finalized_date.get(
order.biobankOrderId)
if order_finalized_date == finalized_time.date() and order.biobankOrderId != id_ and \
order.orderStatus != BiobankOrderStatus.CANCELLED:
day_has_order = True
elif order.biobankOrderId == id_ and order.orderStatus == BiobankOrderStatus.AMENDED:
day_has_order = True
elif not finalized_time and amendment:
day_has_order = True
if existing_measurements and finalized_time:
for measurement in existing_measurements:
if not measurement.finalized:
continue
if measurement.finalized.date() == finalized_time.date() and measurement.physicalMeasurementsId\
!= id_:
day_has_measurement = True
is_distinct_visit = not (day_has_order or day_has_measurement)
return is_distinct_visit
def to_client_json(self, model):
result = model.asdict()
# Participants that withdrew more than 48 hours ago should have fields other than
# WITHDRAWN_PARTICIPANT_FIELDS cleared.
if (model.withdrawalStatus == WithdrawalStatus.NO_USE and
(model.withdrawalTime is None or model.withdrawalTime <
clock.CLOCK.now() - WITHDRAWN_PARTICIPANT_VISIBILITY_TIME)):
result = {k: result.get(k) for k in WITHDRAWN_PARTICIPANT_FIELDS}
elif model.withdrawalStatus != WithdrawalStatus.NO_USE and \
model.suspensionStatus == SuspensionStatus.NO_CONTACT:
for i in SUSPENDED_PARTICIPANT_FIELDS:
result[i] = UNSET
result['participantId'] = to_client_participant_id(model.participantId)
biobank_id = result.get('biobankId')
if biobank_id:
result['biobankId'] = to_client_biobank_id(biobank_id)
date_of_birth = result.get('dateOfBirth')
if date_of_birth:
result['ageRange'] = get_bucketed_age(date_of_birth,
clock.CLOCK.now())
else:
result['ageRange'] = UNSET
if result.get('primaryLanguage') is None:
result['primaryLanguage'] = UNSET
if 'organizationId' in result:
result['organization'] = result['organizationId']
del result['organizationId']
format_json_org(result, self.organization_dao, 'organization')
format_json_hpo(result, self.hpo_dao, 'hpoId')
result['awardee'] = result['hpoId']
_initialize_field_type_sets()
for fieldname in _DATE_FIELDS:
format_json_date(result, fieldname)
for fieldname in _CODE_FIELDS:
format_json_code(result, self.code_dao, fieldname)
for fieldname in _ENUM_FIELDS:
format_json_enum(result, fieldname)
for fieldname in _SITE_FIELDS:
format_json_site(result, self.site_dao, fieldname)
if (model.withdrawalStatus == WithdrawalStatus.NO_USE
or model.suspensionStatus == SuspensionStatus.NO_CONTACT):
result['recontactMethod'] = 'NO_CONTACT'
# Strip None values.
result = {k: v for k, v in result.iteritems() if v is not None}
return result
def _decode_token(self, query_def, fields):
""" If token exists in participant_summary api, decode and use lastModified to add a buffer
of 60 seconds. This ensures when a _sync link is used no one is missed. This will return
at a minimum, the last participant and any more that have been modified in the previous 60
seconds. Duplicate participants returned should be handled on the client side."""
decoded_vals = super(ParticipantSummaryDao,
self)._decode_token(query_def, fields)
if query_def.order_by and (query_def.order_by.field_name
== 'lastModified'
and query_def.always_return_token is True
and query_def.backfill_sync is True):
decoded_vals[0] = decoded_vals[0] - datetime.timedelta(
seconds=config.LAST_MODIFIED_BUFFER_SECONDS)
return decoded_vals
@staticmethod
def update_ehr_status(summary, update_time):
summary.ehrStatus = EhrStatus.PRESENT
if not summary.ehrReceiptTime:
summary.ehrReceiptTime = update_time
summary.ehrUpdateTime = update_time
return summary
class OrganizationHierarchySyncDao(BaseDao):
def __init__(self):
super(OrganizationHierarchySyncDao, self).__init__(HPO)
self.hpo_dao = HPODao()
self.organization_dao = OrganizationDao()
self.site_dao = SiteDao()
def from_client_json(self,
resource_json,
id_=None,
expected_version=None,
client_id=None): # pylint: disable=unused-argument
try:
fhir_org = lib_fhir.fhirclient_3_0_0.models.organization.Organization(
resource_json)
except FHIRValidationError:
raise BadRequest('Invalid FHIR format in payload data.')
if not fhir_org.meta or not fhir_org.meta.versionId:
raise BadRequest('No versionId info found in payload data.')
try:
fhir_org.version = int(fhir_org.meta.versionId)
except ValueError:
raise BadRequest('Invalid versionId in payload data.')
return fhir_org
def to_client_json(self, hierarchy_org_obj):
return hierarchy_org_obj.as_json()
def get_etag(self, id_, pid): # pylint: disable=unused-argument
return None
def update(self, hierarchy_org_obj):
obj_type = self._get_type(hierarchy_org_obj)
operation_funcs = {
'AWARDEE': self._update_awardee,
'ORGANIZATION': self._update_organization,
'SITE': self._update_site
}
if obj_type not in operation_funcs:
raise BadRequest('No awardee-type info found in payload data.')
operation_funcs[obj_type](hierarchy_org_obj)
def _update_awardee(self, hierarchy_org_obj):
if hierarchy_org_obj.id is None:
raise BadRequest('No id found in payload data.')
awardee_id = self._get_value_from_identifier(
hierarchy_org_obj, HIERARCHY_CONTENT_SYSTEM_PREFIX + 'awardee-id')
if awardee_id is None:
raise BadRequest(
'No organization-identifier info found in payload data.')
is_obsolete = ObsoleteStatus(
'OBSOLETE') if not hierarchy_org_obj.active else None
awardee_type = self._get_value_from_extention(
hierarchy_org_obj,
HIERARCHY_CONTENT_SYSTEM_PREFIX + 'awardee-type')
try:
organization_type = OrganizationType(awardee_type)
if organization_type == OrganizationType.UNSET:
organization_type = None
except TypeError:
raise BadRequest(
'Invalid organization type {} for awardee {}'.format(
awardee_type, awardee_id))
entity = HPO(name=awardee_id.upper(),
displayName=hierarchy_org_obj.name,
organizationType=organization_type,
isObsolete=is_obsolete,
resourceId=hierarchy_org_obj.id)
existing_map = {
entity.name: entity
for entity in self.hpo_dao.get_all()
}
existing_entity = existing_map.get(entity.name)
with self.hpo_dao.session() as session:
if existing_entity:
hpo_id = existing_entity.hpoId
new_dict = entity.asdict()
new_dict['hpoId'] = None
existing_dict = existing_entity.asdict()
existing_dict['hpoId'] = None
if existing_dict == new_dict:
logging.info('Not updating {}.'.format(new_dict['name']))
else:
existing_entity.displayName = entity.displayName
existing_entity.organizationType = entity.organizationType
existing_entity.isObsolete = entity.isObsolete
existing_entity.resourceId = entity.resourceId
self.hpo_dao.update_with_session(session, existing_entity)
else:
entity.hpoId = len(existing_map)
hpo_id = entity.hpoId
self.hpo_dao.insert_with_session(session, entity)
bq_hpo_update_by_id(hpo_id)
def _update_organization(self, hierarchy_org_obj):
if hierarchy_org_obj.id is None:
raise BadRequest('No id found in payload data.')
organization_id = self._get_value_from_identifier(
hierarchy_org_obj,
HIERARCHY_CONTENT_SYSTEM_PREFIX + 'organization-id')
if organization_id is None:
raise BadRequest(
'No organization-identifier info found in payload data.')
is_obsolete = ObsoleteStatus(
'OBSOLETE') if not hierarchy_org_obj.active else None
resource_id = self._get_reference(hierarchy_org_obj)
hpo = self.hpo_dao.get_by_resource_id(resource_id)
if hpo is None:
raise BadRequest(
'Invalid partOf reference {} importing organization {}'.format(
resource_id, organization_id))
entity = Organization(externalId=organization_id.upper(),
displayName=hierarchy_org_obj.name,
hpoId=hpo.hpoId,
isObsolete=is_obsolete,
resourceId=hierarchy_org_obj.id)
existing_map = {
entity.externalId: entity
for entity in self.organization_dao.get_all()
}
existing_entity = existing_map.get(entity.externalId)
with self.organization_dao.session() as session:
if existing_entity:
new_dict = entity.asdict()
new_dict['organizationId'] = None
existing_dict = existing_entity.asdict()
existing_dict['organizationId'] = None
if existing_dict == new_dict:
logging.info('Not updating {}.'.format(
new_dict['externalId']))
else:
existing_entity.displayName = entity.displayName
existing_entity.hpoId = entity.hpoId
existing_entity.isObsolete = entity.isObsolete
existing_entity.resourceId = entity.resourceId
self.organization_dao.update_with_session(
session, existing_entity)
else:
self.organization_dao.insert_with_session(session, entity)
org_id = self.organization_dao.get_by_external_id(
organization_id.upper()).organizationId
bq_organization_update_by_id(org_id)
def _update_site(self, hierarchy_org_obj):
if hierarchy_org_obj.id is None:
raise BadRequest('No id found in payload data.')
google_group = self._get_value_from_identifier(
hierarchy_org_obj, HIERARCHY_CONTENT_SYSTEM_PREFIX + 'site-id')
if google_group is None:
raise BadRequest(
'No organization-identifier info found in payload data.')
google_group = google_group.lower()
is_obsolete = ObsoleteStatus(
'OBSOLETE') if not hierarchy_org_obj.active else None
resource_id = self._get_reference(hierarchy_org_obj)
organization = self.organization_dao.get_by_resource_id(resource_id)
if organization is None:
raise BadRequest(
'Invalid partOf reference {} importing site {}'.format(
resource_id, google_group))
launch_date = None
launch_date_str = self._get_value_from_extention(
hierarchy_org_obj,
HIERARCHY_CONTENT_SYSTEM_PREFIX + 'anticipatedLaunchDate',
'valueDate')
if launch_date_str:
try:
launch_date = parse(launch_date_str).date()
except ValueError:
raise BadRequest('Invalid launch date {} for site {}'.format(
launch_date_str, google_group))
name = hierarchy_org_obj.name
mayolink_client_number = None
mayolink_client_number_str = self._get_value_from_identifier(
hierarchy_org_obj,
HIERARCHY_CONTENT_SYSTEM_PREFIX + 'mayo-link-identifier')
if mayolink_client_number_str:
try:
mayolink_client_number = int(mayolink_client_number_str)
except ValueError:
raise BadRequest(
'Invalid Mayolink Client # {} for site {}'.format(
mayolink_client_number_str, google_group))
notes = self._get_value_from_extention(
hierarchy_org_obj, HIERARCHY_CONTENT_SYSTEM_PREFIX + 'notes')
site_status_bool = self._get_value_from_extention(
hierarchy_org_obj,
HIERARCHY_CONTENT_SYSTEM_PREFIX + 'schedulingStatusActive',
'valueBoolean')
try:
site_status = SiteStatus(
'ACTIVE' if site_status_bool else 'INACTIVE')
except TypeError:
raise BadRequest('Invalid site status {} for site {}'.format(
site_status, google_group))
enrolling_status_bool = self._get_value_from_extention(
hierarchy_org_obj,
HIERARCHY_CONTENT_SYSTEM_PREFIX + 'enrollmentStatusActive',
'valueBoolean')
try:
enrolling_status = EnrollingStatus(
'ACTIVE' if enrolling_status_bool else 'INACTIVE')
except TypeError:
raise BadRequest(
'Invalid enrollment site status {} for site {}'.format(
enrolling_status_bool, google_group))
digital_scheduling_bool = self._get_value_from_extention(
hierarchy_org_obj,
HIERARCHY_CONTENT_SYSTEM_PREFIX + 'digitalSchedulingStatusActive',
'valueBoolean')
try:
digital_scheduling_status = DigitalSchedulingStatus(
'ACTIVE' if digital_scheduling_bool else 'INACTIVE')
except TypeError:
raise BadRequest(
'Invalid digital scheduling status {} for site {}'.format(
digital_scheduling_bool, google_group))
directions = self._get_value_from_extention(
hierarchy_org_obj, HIERARCHY_CONTENT_SYSTEM_PREFIX + 'directions')
physical_location_name = self._get_value_from_extention(
hierarchy_org_obj,
HIERARCHY_CONTENT_SYSTEM_PREFIX + 'locationName')
address_1, address_2, city, state, zip_code = self._get_address(
hierarchy_org_obj)
phone = self._get_contact_point(hierarchy_org_obj, 'phone')
admin_email_addresses = self._get_contact_point(
hierarchy_org_obj, 'email')
link = self._get_contact_point(hierarchy_org_obj, 'url')
schedule_instructions = self._get_value_from_extention(
hierarchy_org_obj,
HIERARCHY_CONTENT_SYSTEM_PREFIX + 'schedulingInstructions')
entity = Site(siteName=name,
googleGroup=google_group,
mayolinkClientNumber=mayolink_client_number,
organizationId=organization.organizationId,
hpoId=organization.hpoId,
siteStatus=site_status,
enrollingStatus=enrolling_status,
digitalSchedulingStatus=digital_scheduling_status,
scheduleInstructions=schedule_instructions,
scheduleInstructions_ES='',
launchDate=launch_date,
notes=notes,
notes_ES='',
directions=directions,
physicalLocationName=physical_location_name,
address1=address_1,
address2=address_2,
city=city,
state=state,
zipCode=zip_code,
phoneNumber=phone,
adminEmails=admin_email_addresses,
link=link,
isObsolete=is_obsolete,
resourceId=hierarchy_org_obj.id)
existing_map = {
entity.googleGroup: entity
for entity in self.site_dao.get_all()
}
existing_entity = existing_map.get(entity.googleGroup)
with self.site_dao.session() as session:
if existing_entity:
self._populate_lat_lng_and_time_zone(entity, existing_entity)
if entity.siteStatus == SiteStatus.ACTIVE and \
(entity.latitude is None or entity.longitude is None):
raise BadRequest(
'Active site without geocoding: {}'.format(
entity.googleGroup))
new_dict = entity.asdict()
new_dict['siteId'] = None
existing_dict = existing_entity.asdict()
existing_dict['siteId'] = None
if existing_dict == new_dict:
logging.info('Not updating {}.'.format(
new_dict['googleGroup']))
else:
for k, v in entity.asdict().iteritems():
if k != 'siteId' and k != 'googleGroup':
setattr(existing_entity, k, v)
self.site_dao.update_with_session(session, existing_entity)
else:
self._populate_lat_lng_and_time_zone(entity, None)
if entity.siteStatus == SiteStatus.ACTIVE and \
(entity.latitude is None or entity.longitude is None):
raise BadRequest(
'Active site without geocoding: {}'.format(
entity.googleGroup))
self.site_dao.insert_with_session(session, entity)
site_id = self.site_dao.get_by_google_group(google_group).siteId
bq_site_update_by_id(site_id)
def _get_type(self, hierarchy_org_obj):
obj_type = None
type_arr = hierarchy_org_obj.type
for type_item in type_arr:
code_arr = type_item.coding
for code_item in code_arr:
if code_item.system == HIERARCHY_CONTENT_SYSTEM_PREFIX + 'type':
obj_type = code_item.code
break
return obj_type
def _get_value_from_identifier(self, hierarchy_org_obj, system):
identifier_arr = hierarchy_org_obj.identifier
for identifier in identifier_arr:
if identifier.system == system:
return identifier.value
else:
return None
def _get_value_from_extention(self,
hierarchy_org_obj,
url,
value_key='valueString'):
extension_arr = hierarchy_org_obj.extension
for extension in extension_arr:
if extension.url == url:
ext_json = extension.as_json()
return ext_json[value_key]
else:
return None
def _get_contact_point(self, hierarchy_org_obj, code):
contact_arr = hierarchy_org_obj.contact
for contact in contact_arr:
telecom_arr = contact.telecom
for telecom in telecom_arr:
if telecom.system == code:
return telecom.value
else:
return None
def _get_address(self, hierarchy_org_obj):
address = hierarchy_org_obj.address[0]
address_1 = address.line[0] if len(address.line) > 0 else ''
address_2 = address.line[1] if len(address.line) > 1 else ''
city = address.city
state = address.state
postal_code = address.postalCode
return address_1, address_2, city, state, postal_code
def _get_reference(self, hierarchy_org_obj):
try:
return hierarchy_org_obj.partOf.reference.split('/')[1]
except IndexError:
return None
def _populate_lat_lng_and_time_zone(self, site, existing_site):
if site.address1 and site.city and site.state:
if existing_site:
if (existing_site.address1 == site.address1
and existing_site.city == site.city
and existing_site.state == site.state
and existing_site.latitude is not None
and existing_site.longitude is not None
and existing_site.timeZoneId is not None):
# Address didn't change, use the existing lat/lng and time zone.
site.latitude = existing_site.latitude
site.longitude = existing_site.longitude
site.timeZoneId = existing_site.timeZoneId
return
latitude, longitude = self._get_lat_long_for_site(
site.address1, site.city, site.state)
site.latitude = latitude
site.longitude = longitude
if latitude and longitude:
site.timeZoneId = self._get_time_zone(latitude, longitude)
else:
if site.googleGroup not in self.status_exception_list:
if site.siteStatus == self.ACTIVE:
logging.warn(
'Active site must have valid address. Site: {}, Group: {}'
.format(site.siteName, site.googleGroup))
def _get_lat_long_for_site(self, address_1, city, state):
self.full_address = address_1 + ' ' + city + ' ' + state
try:
self.api_key = os.environ.get('API_KEY')
self.gmaps = googlemaps.Client(key=self.api_key)
try:
geocode_result = self.gmaps.geocode(address_1 + '' + city +
' ' + state)[0]
except IndexError:
logging.warn('Bad address for {}, could not geocode.'.format(
self.full_address))
return None, None
if geocode_result:
geometry = geocode_result.get('geometry')
if geometry:
location = geometry.get('location')
if location:
latitude = location.get('lat')
longitude = location.get('lng')
return latitude, longitude
else:
logging.warn('Can not find lat/long for %s',
self.full_address)
return None, None
else:
logging.warn('Geocode results failed for %s.',
self.full_address)
return None, None
except ValueError as e:
logging.exception('Invalid geocode key: %s. ERROR: %s',
self.api_key, e)
return None, None
except IndexError as e:
logging.exception(
'Geocoding failure Check that address is correct. ERROR: %s',
e)
return None, None
def _get_time_zone(self, latitude, longitude):
time_zone = self.gmaps.timezone(location=(latitude, longitude))
if time_zone['status'] == 'OK':
time_zone_id = time_zone['timeZoneId']
return time_zone_id
else:
logging.info('can not retrieve time zone from %s',
self.full_address)
return None
class ParticipantSummaryDao(UpdatableDao):
def __init__(self):
super(ParticipantSummaryDao,
self).__init__(ParticipantSummary,
order_by_ending=_ORDER_BY_ENDING)
self.hpo_dao = HPODao()
self.code_dao = CodeDao()
self.site_dao = SiteDao()
self.organization_dao = OrganizationDao()
def get_id(self, obj):
return obj.participantId
def get_by_email(self, email):
with self.session() as session:
return session.query(ParticipantSummary).filter(
ParticipantSummary.email == email).all()
def _validate_update(self, session, obj, existing_obj): # pylint: disable=unused-argument
"""Participant summaries don't have a version value; drop it from validation logic."""
if not existing_obj:
raise NotFound('%s with id %s does not exist' %
(self.model_type.__name__, id))
def _has_withdrawn_filter(self, query):
for field_filter in query.field_filters:
if (field_filter.field_name == 'withdrawalStatus'
and field_filter.value == WithdrawalStatus.NO_USE):
return True
if field_filter.field_name == 'withdrawalTime' and field_filter.value is not None:
return True
return False
def _get_non_withdrawn_filter_field(self, query):
"""Returns the first field referenced in query filters which isn't in
WITHDRAWN_PARTICIPANT_FIELDS."""
for field_filter in query.field_filters:
if not field_filter.field_name in WITHDRAWN_PARTICIPANT_FIELDS:
return field_filter.field_name
return None
def _initialize_query(self, session, query_def):
non_withdrawn_field = self._get_non_withdrawn_filter_field(query_def)
if self._has_withdrawn_filter(query_def):
if non_withdrawn_field:
raise BadRequest(
"Can't query on %s for withdrawn participants" %
non_withdrawn_field)
# When querying for withdrawn participants, ensure that the only fields being filtered on or
# ordered by are in WITHDRAWN_PARTICIPANT_FIELDS.
return super(ParticipantSummaryDao,
self)._initialize_query(session, query_def)
else:
query = super(ParticipantSummaryDao,
self)._initialize_query(session, query_def)
if non_withdrawn_field:
# When querying on fields that aren't available for withdrawn participants,
# ensure that we only return participants
# who have not withdrawn or withdrew in the past 48 hours.
withdrawn_visible_start = clock.CLOCK.now(
) - WITHDRAWN_PARTICIPANT_VISIBILITY_TIME
return query.filter(
or_(
ParticipantSummary.withdrawalStatus !=
WithdrawalStatus.NO_USE,
ParticipantSummary.withdrawalTime >=
withdrawn_visible_start))
else:
# When querying on fields that are available for withdrawn participants, return everybody;
# withdrawn participants will have all but WITHDRAWN_PARTICIPANT_FIELDS cleared out 48
# hours after withdrawing.
return query
def _get_order_by_ending(self, query):
if self._has_withdrawn_filter(query):
return _WITHDRAWN_ORDER_BY_ENDING
return self.order_by_ending
def _add_order_by(self, query, order_by, field_names, fields):
if order_by.field_name in _CODE_FILTER_FIELDS:
return super(ParticipantSummaryDao, self)._add_order_by(
query, OrderBy(order_by.field_name + 'Id', order_by.ascending),
field_names, fields)
return super(ParticipantSummaryDao,
self)._add_order_by(query, order_by, field_names, fields)
def make_query_filter(self, field_name, value):
"""Handle HPO and code values when parsing filter values."""
if field_name == 'hpoId' or field_name == 'awardee':
hpo = self.hpo_dao.get_by_name(value)
if not hpo:
raise BadRequest('No HPO found with name %s' % value)
if field_name == 'awardee':
field_name = 'hpoId'
return super(ParticipantSummaryDao,
self).make_query_filter(field_name, hpo.hpoId)
if field_name == 'organization':
organization = self.organization_dao.get_by_external_id(value)
if not organization:
raise BadRequest('No organization found with name %s' % value)
return super(ParticipantSummaryDao,
self).make_query_filter(field_name + 'Id',
organization.organizationId)
if field_name in _SITE_FIELDS:
if value == UNSET:
return super(ParticipantSummaryDao,
self).make_query_filter(field_name + 'Id', None)
site = self.site_dao.get_by_google_group(value)
if not site:
raise BadRequest('No site found with google group %s' % value)
return super(ParticipantSummaryDao,
self).make_query_filter(field_name + 'Id',
site.siteId)
if field_name in _CODE_FILTER_FIELDS:
if value == UNSET:
return super(ParticipantSummaryDao,
self).make_query_filter(field_name + 'Id', None)
# Note: we do not at present support querying for UNMAPPED code values.
code = self.code_dao.get_code(PPI_SYSTEM, value)
if not code:
raise BadRequest('No code found: %s' % value)
return super(ParticipantSummaryDao,
self).make_query_filter(field_name + 'Id',
code.codeId)
return super(ParticipantSummaryDao,
self).make_query_filter(field_name, value)
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_num_baseline_ppi_modules(self):
return len(
config.getSettingList(config.BASELINE_PPI_QUESTIONNAIRE_FIELDS))
def update_enrollment_status(self, summary):
"""Updates the enrollment status field on the provided participant summary to
the correct value based on the other fields on it. Called after
a questionnaire response or physical measurements are submitted."""
consent = (summary.consentForStudyEnrollment
== QuestionnaireStatus.SUBMITTED
and summary.consentForElectronicHealthRecords
== QuestionnaireStatus.SUBMITTED)
enrollment_status = self.calculate_enrollment_status(
consent, summary.numCompletedBaselinePPIModules,
summary.physicalMeasurementsStatus, summary.samplesToIsolateDNA)
summary.enrollmentStatus = enrollment_status
def calculate_enrollment_status(self, consent_for_study_enrollment_and_ehr,
num_completed_baseline_ppi_modules,
physical_measurements_status,
samples_to_isolate_dna):
if consent_for_study_enrollment_and_ehr:
if (num_completed_baseline_ppi_modules
== self._get_num_baseline_ppi_modules()
and physical_measurements_status
== PhysicalMeasurementsStatus.COMPLETED
and samples_to_isolate_dna == SampleStatus.RECEIVED):
return EnrollmentStatus.FULL_PARTICIPANT
return EnrollmentStatus.MEMBER
return EnrollmentStatus.INTERESTED
def to_client_json(self, model):
result = model.asdict()
# Participants that withdrew more than 48 hours ago should have fields other than
# WITHDRAWN_PARTICIPANT_FIELDS cleared.
if (model.withdrawalStatus == WithdrawalStatus.NO_USE
and model.withdrawalTime <
clock.CLOCK.now() - WITHDRAWN_PARTICIPANT_VISIBILITY_TIME):
result = {k: result.get(k) for k in WITHDRAWN_PARTICIPANT_FIELDS}
result['participantId'] = to_client_participant_id(model.participantId)
biobank_id = result.get('biobankId')
if biobank_id:
result['biobankId'] = to_client_biobank_id(biobank_id)
date_of_birth = result.get('dateOfBirth')
if date_of_birth:
result['ageRange'] = get_bucketed_age(date_of_birth,
clock.CLOCK.now())
else:
result['ageRange'] = UNSET
if 'organizationId' in result:
result['organization'] = result['organizationId']
del result['organizationId']
format_json_org(result, self.organization_dao, 'organization')
format_json_hpo(result, self.hpo_dao, 'hpoId')
result['awardee'] = result['hpoId']
_initialize_field_type_sets()
for fieldname in _DATE_FIELDS:
format_json_date(result, fieldname)
for fieldname in _CODE_FIELDS:
format_json_code(result, self.code_dao, fieldname)
for fieldname in _ENUM_FIELDS:
format_json_enum(result, fieldname)
for fieldname in _SITE_FIELDS:
format_json_site(result, self.site_dao, fieldname)
if (model.withdrawalStatus == WithdrawalStatus.NO_USE
or model.suspensionStatus == SuspensionStatus.NO_CONTACT):
result['recontactMethod'] = 'NO_CONTACT'
# Strip None values.
result = {k: v for k, v in result.iteritems() if v is not None}
return result
def _decode_token(self, query_def, fields):
""" If token exists in participant_summary api, decode and use lastModified to add a buffer
of 60 seconds. This ensures when a _sync link is used no one is missed. This will return
at a minimum, the last participant and any more that have been modified in the previous 60
seconds. Duplicate participants returned should be handled on the client side."""
decoded_vals = super(ParticipantSummaryDao,
self)._decode_token(query_def, fields)
if query_def.order_by and (query_def.order_by.field_name
== 'lastModified'
and query_def.always_return_token == True):
decoded_vals[0] = decoded_vals[0] - datetime.timedelta(
seconds=config.LAST_MODIFIED_BUFFER_SECONDS)
return decoded_vals