def read_survey_templates(account_id, source_id, language_tag, token_info): _validate_account_access(token_info, account_id) # TODO: I don't think surveys have names... only survey groups have names. # So what can I pass down to the user that will make any sense here? # Note to future maintainers, # 2/21/20: currently the only way to figure this out # is to look through the "surveys" and "survey_group" tables, try: # select survey_id, american from surveys left join survey_group on # survey_group = group_order; with Transaction() as t: source_repo = SourceRepo(t) source = source_repo.get_source(account_id, source_id) if source is None: return jsonify(code=404, message="No source found"), 404 template_repo = SurveyTemplateRepo(t) if source.source_type == Source.SOURCE_TYPE_HUMAN: return jsonify([ template_repo.get_survey_template_link_info(x) for x in [1, 3, 4, 5, 6, SurveyTemplateRepo.VIOSCREEN_ID] ]), 200 elif source.source_type == Source.SOURCE_TYPE_ANIMAL: return jsonify([ template_repo.get_survey_template_link_info(x) for x in [2] ]), 200 else: return jsonify([]), 200
def get_samples_by_source(self, account_id, source_id, allow_revoked=False): sql = "{0}{1}".format( self.PARTIAL_SQL, " WHERE" " source.account_id = %s" " AND source.id = %s" " ORDER BY ag_kit_barcodes.barcode asc") with self._transaction.cursor() as cur: acct_repo = AccountRepo(self._transaction) if acct_repo.get_account(account_id) is None: raise NotFound("No such account") source_repo = SourceRepo(self._transaction) if source_repo.get_source(account_id, source_id, allow_revoked=allow_revoked) is None: raise NotFound("No such source") cur.execute(sql, (account_id, source_id)) samples = [] for sample_row in cur.fetchall(): samples.append(self._create_sample_obj(sample_row)) return samples
def upsert_vioscreen_status(self, account_id, source_id, survey_id, status): # Check that account has permissions for source: source_repo = SourceRepo(self._transaction) source_repo.get_source(account_id, source_id) # Check current survey status cur_status = self.get_vioscreen_status(survey_id) # If there is no status, insert a row. if cur_status is None: with self._transaction.cursor() as cur: cur.execute( "INSERT INTO ag_login_surveys(" "ag_login_id, survey_id, vioscreen_status, source_id) " "VALUES(%s, %s, %s, %s)", (account_id, survey_id, status, source_id)) else: # Else, upsert a status. with self._transaction.cursor() as cur: cur.execute( "UPDATE " "ag_login_surveys " "SET " "vioscreen_status = %s " "WHERE " "survey_id = %s", (status, survey_id)) if cur.rowcount != 1: raise NotFound("No such survey id: " + survey_id)
def update_sample_association(account_id, source_id, sample_id, body, token_info): _validate_account_access(token_info, account_id) # TODO: API layer doesn't understand that BadRequest can be thrown, # but that looks to be the right result if sample_site bad. # Need to update the api layer if we want to specify 400s. # (Or we leave api as is and say 400's can always be thrown if your # request is bad) with Transaction() as t: sample_repo = SampleRepo(t) source_repo = SourceRepo(t) source = source_repo.get_source(account_id, source_id) if source is None: return jsonify(code=404, message="No such source"), 404 needs_sample_site = source.source_type in [ Source.SOURCE_TYPE_HUMAN, Source.SOURCE_TYPE_ANIMAL ] precludes_sample_site = source.source_type == \ Source.SOURCE_TYPE_ENVIRONMENT sample_site_present = "sample_site" in body and \ body["sample_site"] is not None if needs_sample_site and not sample_site_present: # Human/Animal sources require sample_site to be set raise BadRequest("human/animal samples require sample_site") if precludes_sample_site and sample_site_present: raise BadRequest("environmental samples cannot specify " "sample_site") sample_datetime = body['sample_datetime'] try: sample_datetime = fromisotime(sample_datetime) except ValueError: raise BadRequest("Invalid sample_datetime") curdate = datetime.now(sample_datetime.tzinfo) lower_limit = curdate + relativedelta(years=-10) upper_limit = curdate + relativedelta(months=+1) if sample_datetime < lower_limit or sample_datetime > upper_limit: raise BadRequest('Invalid sample date') # sample_site will not be present if its environmental. this will # default to None if the key is not present sample_site = body.get('sample_site') sample_info = SampleInfo(sample_id, sample_datetime, sample_site, body["sample_notes"]) is_admin = token_grants_admin_access(token_info) sample_repo.update_info(account_id, source_id, sample_info, override_locked=is_admin) final_sample = sample_repo.get_sample(account_id, source_id, sample_id) t.commit() return jsonify(final_sample), 200
def read_sources(account_id, token_info, source_type=None): _validate_account_access(token_info, account_id) with Transaction() as t: source_repo = SourceRepo(t) sources = source_repo.get_sources_in_account(account_id, source_type) api_sources = [x.to_api() for x in sources] # TODO: Also support 404? Or is that not necessary? return jsonify(api_sources), 200
def read_source(account_id, source_id, token_info): _validate_account_access(token_info, account_id) with Transaction() as t: source_repo = SourceRepo(t) source = source_repo.get_source(account_id, source_id) if source is None: return jsonify(code=404, message=SRC_NOT_FOUND_MSG), 404 return jsonify(source.to_api()), 200
def tearDown(self): with Transaction() as t: sr = SourceRepo(t) sar = SurveyAnswersRepo(t) sar.delete_answered_survey(ACCOUNT_ID, SURVEY_ID) sr.delete_source(HUMAN_SOURCE.account_id, HUMAN_SOURCE.id) t.commit()
def delete_source(account_id, source_id, token_info): _validate_account_access(token_info, account_id) with Transaction() as t: source_repo = SourceRepo(t) if not source_repo.delete_source(account_id, source_id): return jsonify(code=404, message=SRC_NOT_FOUND_MSG), 404 # TODO: 422? t.commit() return '', 204
def setUp(self): with Transaction() as t: sr = SourceRepo(t) sar = SurveyAnswersRepo(t) sr.create_source(HUMAN_SOURCE) sar.submit_answered_survey(ACCOUNT_ID, HUMAN_SOURCE.id, 'en_US', 1, SURVEY_ANSWERS, SURVEY_ID) t.commit()
def test_scrub_not_human(self): with Transaction() as t: sr = SourceRepo(t) # make the source an animal cur = t.cursor() cur.execute( """UPDATE ag.source SET source_type=%s WHERE id=%s""", (Source.SOURCE_TYPE_ANIMAL, HUMAN_SOURCE.id)) with self.assertRaises(RepoException): sr.scrub(ACCOUNT_ID, HUMAN_SOURCE.id)
def delete_dummy_accts(): all_sample_ids = [] acct_ids = [ACCT_ID_1, ACCT_ID_2] with Transaction() as t: acct_repo = AccountRepo(t) source_repo = SourceRepo(t) survey_answers_repo = SurveyAnswersRepo(t) sample_repo = SampleRepo(t) for curr_acct_id in acct_ids: sources = source_repo.get_sources_in_account(curr_acct_id) for curr_source in sources: source_samples = sample_repo.get_samples_by_source( curr_acct_id, curr_source.id) sample_ids = [x.id for x in source_samples] all_sample_ids.extend(sample_ids) # Dissociate all samples linked to this source from all # answered surveys linked to this source, then delete all # answered surveys delete_dummy_answered_surveys_from_source_with_t( t, curr_acct_id, curr_source.id, sample_ids, survey_answers_repo) # Now dissociate all the samples from this source for curr_sample_id in sample_ids: sample_repo.dissociate_sample(curr_acct_id, curr_source.id, curr_sample_id) # Finally, delete the source source_repo.delete_source(curr_acct_id, curr_source.id) # Delete the account acct_repo.delete_account(curr_acct_id) # Belt and suspenders: these test emails are used by some tests outside # of this module as well, so can't be sure they are paired with the # above dummy account ids acct_repo.delete_account_by_email(TEST_EMAIL) acct_repo.delete_account_by_email(TEST_EMAIL_2) # Delete the kit and all samples that were attached to any sources # NB: This won't clean up any samples that were created but NOT # attached to any sources ... if len(all_sample_ids) == 0: all_sample_ids = None _remove_mock_kit(t, mock_sample_ids=all_sample_ids) t.commit()
def delete_source(account_id, source_id, token_info): _validate_account_access(token_info, account_id) with Transaction() as t: source_repo = SourceRepo(t) survey_answers_repo = SurveyAnswersRepo(t) answers = survey_answers_repo.list_answered_surveys( account_id, source_id) for survey_id in answers: survey_answers_repo.delete_answered_survey(account_id, survey_id) if not source_repo.delete_source(account_id, source_id): return jsonify(code=404, message=SRC_NOT_FOUND_MSG), 404 # TODO: 422? t.commit() return '', 204
def create_source(account_id, body, token_info): _validate_account_access(token_info, account_id) with Transaction() as t: source_repo = SourceRepo(t) source_id = str(uuid.uuid4()) name = body["source_name"] source_type = body['source_type'] if source_type == Source.SOURCE_TYPE_HUMAN: # TODO: Unfortunately, humans require a lot of special handling, # and we started mixing Source calls used for transforming to/ # from the database with source calls to/from the api. # Would be nice to split this out better. source_info = HumanInfo.from_dict(body, consent_date=date.today(), date_revoked=None) # the "legacy" value of the age_range enum is not valid to use when # creating a new source, so do not allow that. # NB: Not necessary to do this check when updating a source as # only source name and description (not age_range) may be updated. if source_info.age_range == "legacy": raise RepoException("Age range may not be set to legacy.") else: source_info = NonHumanInfo.from_dict(body) new_source = Source(source_id, account_id, source_type, name, source_info) source_repo.create_source(new_source) # Must pull from db to get creation_time, update_time s = source_repo.get_source(account_id, new_source.id) t.commit() response = jsonify(s.to_api()) response.status_code = 201 response.headers['Location'] = '/api/accounts/%s/sources/%s' % \ (account_id, source_id) return response
def create_dummy_source(name, source_type, content_dict, create_dummy_1=True, iss=ACCT_MOCK_ISS, sub=ACCT_MOCK_SUB): with Transaction() as t: dummy_source_id = SOURCE_ID_1 dummy_acct_id = _create_dummy_acct_from_t(t, create_dummy_1, iss, sub) source_repo = SourceRepo(t) if source_type == Source.SOURCE_TYPE_HUMAN: dummy_info_obj = HumanInfo.from_dict(content_dict, DUMMY_CONSENT_DATE, None) else: dummy_info_obj = NonHumanInfo.from_dict(content_dict) source_repo.create_source(Source(dummy_source_id, dummy_acct_id, source_type, name, dummy_info_obj)) t.commit() return dummy_acct_id, dummy_source_id
def delete_account(account_id, token_info): validate_admin_access(token_info) with Transaction() as t: acct_repo = AccountRepo(t) src_repo = SourceRepo(t) samp_repo = SampleRepo(t) sar_repo = SurveyAnswersRepo(t) acct = acct_repo.get_account(account_id) if acct is None: return jsonify(message="Account not found", code=404), 404 else: # the account is already scrubbed so let's stop early if acct.account_type == 'deleted': return None, 204 sample_count = 0 sources = src_repo.get_sources_in_account(account_id) for source in sources: samples = samp_repo.get_samples_by_source(account_id, source.id) has_samples = len(samples) > 0 sample_count += len(samples) for sample in samples: # we scrub rather than disassociate in the event that the # sample is in our freezers but not with an up-to-date scan samp_repo.scrub(account_id, source.id, sample.id) surveys = sar_repo.list_answered_surveys(account_id, source.id) if has_samples: # if we have samples, we need to scrub survey / source # free text for survey_id in surveys: sar_repo.scrub(account_id, source.id, survey_id) src_repo.scrub(account_id, source.id) else: # if we do not have associated samples, then the source # is safe to delete for survey_id in surveys: sar_repo.delete_answered_survey(account_id, survey_id) src_repo.delete_source(account_id, source.id) # an account is safe to delete if there are no associated samples if sample_count > 0: acct_repo.scrub(account_id) else: acct_repo.delete_account(account_id) t.commit() return None, 204
def delete_dummy_accts(): with Transaction() as t: source_repo = SourceRepo(t) survey_answers_repo = SurveyAnswersRepo(t) sources = source_repo.get_sources_in_account(ACCT_ID_1) for curr_source in sources: answers = survey_answers_repo.list_answered_surveys( ACCT_ID_1, curr_source.id) for survey_id in answers: survey_answers_repo.delete_answered_survey( ACCT_ID_1, survey_id) source_repo.delete_source(ACCT_ID_1, curr_source.id) acct_repo = AccountRepo(t) acct_repo.delete_account(ACCT_ID_1) acct_repo.delete_account(ACCT_ID_2) # Belt and suspenders: these test emails are used by some tests outside # of this module as well, so can't be sure they are paired with the # above dummy account ids acct_repo.delete_account_by_email(TEST_EMAIL) acct_repo.delete_account_by_email(TEST_EMAIL_2) t.commit()
def migrate_96(TRN): from microsetta_private_api.repo.source_repo import SourceRepo as sr TRN.add("SELECT id, source_name FROM source") rows = TRN.execute()[-1] for r in rows: hsi = sr.construct_host_subject_id(r[0], r[1]) TRN.add( """INSERT INTO source_host_subject_id (source_id, host_subject_id) VALUES (%s, %s)""", (r[0], hsi)) TRN.execute()
def get_samples_by_source(self, account_id, source_id): with self._transaction.cursor() as cur: acct_repo = AccountRepo(self._transaction) if acct_repo.get_account(account_id) is None: raise NotFound("No such account") source_repo = SourceRepo(self._transaction) if source_repo.get_source(account_id, source_id) is None: raise NotFound("No such source") cur.execute( "SELECT " "ag_kit_barcodes.ag_kit_barcode_id, " "ag_kit_barcodes.sample_date, " "ag_kit_barcodes.sample_time, " "ag_kit_barcodes.site_sampled, " "ag_kit_barcodes.notes, " "ag_kit_barcodes.barcode, " "barcode.scan_date " "FROM ag_kit_barcodes " "LEFT JOIN barcode " "USING (barcode) " "LEFT JOIN source " "ON ag_kit_barcodes.source_id = source.id " "WHERE " "source.account_id = %s AND " "source.id = %s " "ORDER BY barcode.barcode asc", (account_id, source_id)) samples = [] for sample_row in cur.fetchall(): barcode = sample_row[5] sample_projects = self._retrieve_projects(barcode) s = Sample.from_db(*sample_row, sample_projects) samples.append(s) return samples
def test_scrub(self): with Transaction() as t: sr = SourceRepo(t) sr.scrub(HUMAN_SOURCE.account_id, HUMAN_SOURCE.id) obs = sr.get_source(HUMAN_SOURCE.account_id, HUMAN_SOURCE.id, allow_revoked=False) self.assertEqual(obs, None) obs = sr.get_source(HUMAN_SOURCE.account_id, HUMAN_SOURCE.id, allow_revoked=True) self.assertNotEqual(obs.name, HUMAN_SOURCE.name) self.assertNotEqual(obs.source_data.email, HUMAN_SOURCE.source_data.email) self.assertTrue(obs.source_data.date_revoked is not None)
def update_source(account_id, source_id, body, token_info): _validate_account_access(token_info, account_id) with Transaction() as t: source_repo = SourceRepo(t) source = source_repo.get_source(account_id, source_id) if source is None: return jsonify(code=404, message=SRC_NOT_FOUND_MSG), 404 source.name = body["source_name"] # every type of source has a name but not every type has a description if getattr(source.source_data, "description", False): source.source_data.description = body.get( "source_description", None) source_repo.update_source_data_api_fields(source) # I wonder if there's some way to get the creation_time/update_time # during the insert/update... source = source_repo.get_source(account_id, source_id) t.commit() # TODO: 422? Not sure this can actually happen anymore ... return jsonify(source.to_api()), 200
def get_survey_metadata(self, sample_barcode, survey_template_id=None): ids = self._get_ids_relevant_to_barcode(sample_barcode) if ids is None: raise NotFound("No such barcode") account_id = ids.get('account_id') source_id = ids.get('source_id') sample_id = ids.get('sample_id') account = None source = None sample = None if sample_id is not None: sample_repo = SampleRepo(self._transaction) sample = sample_repo._get_sample_by_id(sample_id) if source_id is not None and account_id is not None: source_repo = SourceRepo(self._transaction) account_repo = AccountRepo(self._transaction) account = account_repo.get_account(account_id) source = source_repo.get_source(account_id, source_id) if source is None: raise RepoException("Barcode is not associated with a source") # TODO: This is my best understanding of how the data must be # transformed to get the host_subject_id, needs verification that it # generates the expected values for preexisting samples. prehash = account_id + source.name.lower() host_subject_id = sha512(prehash.encode()).hexdigest() survey_answers_repo = SurveyAnswersRepo(self._transaction) answer_ids = survey_answers_repo.list_answered_surveys_by_sample( account_id, source_id, sample_id) answer_to_template_map = {} for answer_id in answer_ids: template_id = survey_answers_repo.find_survey_template_id( answer_id) answer_to_template_map[answer_id] = template_id # if a survey template is specified, filter the returned surveys if survey_template_id is not None: # TODO: This schema is so awkward for this type of query... answers = [] for answer_id in answer_ids: if answer_to_template_map[answer_id] == survey_template_id: answers.append(answer_id) if len(answers) == 0: raise NotFound("This barcode is not associated with any " "surveys matching this template id") if len(answers) > 1: # I really hope this can't happen. (x . x) raise RepoException("This barcode is associated with more " "than one survey matching this template" " id") answer_ids = answers metadata_map = survey_answers_repo.build_metadata_map() all_survey_answers = [] for answer_id in answer_ids: answer_model = survey_answers_repo.get_answered_survey( account_id, source_id, answer_id, "en-US") survey_answers = {} for k in answer_model: new_k = metadata_map[int(k)] survey_answers[k] = [new_k, answer_model[k]] all_survey_answers.append({ "template": answer_to_template_map[answer_id], "response": survey_answers }) pulldown = { "sample_barcode": sample_barcode, "host_subject_id": host_subject_id, "account": account, "source": source, "sample": sample, "survey_answers": all_survey_answers } return pulldown
def retrieve_diagnostics_by_barcode(self, sample_barcode, grab_kit=True): def _rows_to_dicts_list(rows): return [dict(x) for x in rows] with self._transaction.dict_cursor() as cur: ids = self._get_ids_relevant_to_barcode(sample_barcode) if ids is None: ids = {} # default for not found is None sample_id = ids.get("sample_id") source_id = ids.get("source_id") account_id = ids.get("account_id") # NB: this is the true UUID kit id (the primary key of # ag.ag_kit), NOT the kit's participant-facing string "id" kit_id = ids.get("kit_id") account = None source = None sample = None kit = None # get sample object for this barcode, if any if sample_id is not None: sample_repo = SampleRepo(self._transaction) sample = sample_repo._get_sample_by_id(sample_id) # get account object for this barcode, if any if account_id is not None: account_repo = AccountRepo(self._transaction) account = account_repo.get_account(account_id) # and source object for this barcode, if any if source_id is not None: source_repo = SourceRepo(self._transaction) source = source_repo.get_source(account_id, source_id) # get (partial) projects_info list for this barcode query = f""" SELECT {p.DB_PROJ_NAME_KEY}, {p.IS_MICROSETTA_KEY}, {p.BANK_SAMPLES_KEY}, {p.PLATING_START_DATE_KEY} FROM barcodes.project INNER JOIN barcodes.project_barcode USING (project_id) WHERE barcode=%s;""" cur.execute(query, (sample_barcode, )) # this can't be None; worst-case is an empty list projects_info = _rows_to_dicts_list(cur.fetchall()) # get scans_info list for this barcode # NB: ORDER MATTERS here. Do not change the order unless you # are positive you know what already depends on it. cur.execute( "SELECT barcode_scan_id, barcode, " "scan_timestamp, sample_status, " "technician_notes " "FROM barcodes.barcode_scans " "WHERE barcode=%s " "ORDER BY scan_timestamp asc", (sample_barcode, )) # this can't be None; worst-case is an empty list scans_info = _rows_to_dicts_list(cur.fetchall()) latest_scan = None if len(scans_info) > 0: # NB: the correctness of this depends on the scans (queried # right above) being in ascending order by timestamp latest_scan = scans_info[len(scans_info) - 1] # get details about this barcode itself; CAN be None if the # barcode doesn't exist in db barcode_info = None cur.execute( "SELECT barcode, assigned_on, status, " "sample_postmark_date, biomass_remaining, " "sequencing_status, obsolete, " "create_date_time, kit_id " "FROM barcodes.barcode " "WHERE barcode = %s", (sample_barcode, )) barcode_row = cur.fetchone() if barcode_row is not None: barcode_info = dict(barcode_row) if account is None and source is None and sample is None and \ len(projects_info) == 0 and len(scans_info) == 0 \ and barcode_info is None: return None diagnostic = { "account": account, "source": source, "sample": sample, "latest_scan": latest_scan, "scans_info": scans_info, "barcode_info": barcode_info, "projects_info": projects_info } if grab_kit: # get kit object if kit_id is not None: kit_repo = KitRepo(self._transaction) kit = kit_repo.get_kit_all_samples_by_kit_id(kit_id) diagnostic["kit"] = kit return diagnostic
def setUp(self): with Transaction() as t: sr = SourceRepo(t) sr.create_source(HUMAN_SOURCE) t.commit()
def tearDown(self): with Transaction() as t: sr = SourceRepo(t) sr.delete_source(HUMAN_SOURCE.account_id, HUMAN_SOURCE.id) t.commit()
def test_scrub_bad_source(self): with Transaction() as t: sr = SourceRepo(t) with self.assertRaises(RepoException): sr.scrub(ACCOUNT_ID, 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa')
def retrieve_diagnostics_by_barcode(self, sample_barcode, grab_kit=True): with self._transaction.dict_cursor() as cur: ids = self._get_ids_relevant_to_barcode(sample_barcode) if ids is None: sample_id = None source_id = None account_id = None kit_id = None else: sample_id = ids["sample_id"] source_id = ids["source_id"] account_id = ids["account_id"] kit_id = ids["kit_id"] account = None source = None sample = None kit = None if sample_id is not None: sample_repo = SampleRepo(self._transaction) sample = sample_repo._get_sample_by_id(sample_id) if source_id is not None and account_id is not None: account_repo = AccountRepo(self._transaction) source_repo = SourceRepo(self._transaction) account = account_repo.get_account(account_id) source = source_repo.get_source(account_id, source_id) if kit_id is not None and grab_kit: kit_repo = KitRepo(self._transaction) kit = kit_repo.get_kit_all_samples_by_kit_id(kit_id) cur.execute("SELECT * from barcodes.barcode " "LEFT OUTER JOIN barcodes.project_barcode " "USING (barcode) " "LEFT OUTER JOIN barcodes.project " "USING (project_id) " "where barcode=%s", (sample_barcode,)) barcode_info = cur.fetchall() # How to unwrap a psycopg2 DictRow. I feel dirty. barcode_info = [{k: v for k, v in x.items()} for x in barcode_info] # Get Inceptioned!! # Collapse info from joined project_barcode and project tables # into array within barcode_info if barcode_info: first = barcode_info[0] first['projects'] = [ { 'project_id': r['project_id'], 'project': r['project'] } for r in barcode_info] del first['project_id'] del first['project'] barcode_info = first else: barcode_info = None if account is None and \ source is None and \ sample is None and \ barcode_info is None: return None diagnostic = { "barcode": sample_barcode, "account": account, "source": source, "sample": sample, "barcode_info": barcode_info } if grab_kit: diagnostic["kit"] = kit return diagnostic
def retrieve_diagnostics_by_barcode(self, sample_barcode, grab_kit=True): def _rows_to_dicts_list(rows): return [dict(x) for x in rows] with self._transaction.dict_cursor() as cur: ids = self._get_ids_relevant_to_barcode(sample_barcode) if ids is None: sample_id = None source_id = None account_id = None kit_id = None else: sample_id = ids["sample_id"] source_id = ids["source_id"] account_id = ids["account_id"] kit_id = ids["kit_id"] account = None source = None sample = None kit = None # get sample object for this barcode if sample_id is not None: sample_repo = SampleRepo(self._transaction) sample = sample_repo._get_sample_by_id(sample_id) # get account and source objects for this barcode if source_id is not None and account_id is not None: account_repo = AccountRepo(self._transaction) source_repo = SourceRepo(self._transaction) account = account_repo.get_account(account_id) source = source_repo.get_source(account_id, source_id) # get projects_info list for this barcode cur.execute("SELECT project, is_microsetta, " "bank_samples, plating_start_date " "FROM barcodes.project " "INNER JOIN barcodes.project_barcode " "USING (project_id) " "WHERE barcode=%s", (sample_barcode,)) # this can't be None; worst-case is an empty list projects_info = _rows_to_dicts_list(cur.fetchall()) # get scans_info list for this barcode # NB: ORDER MATTERS here. Do not change the order unless you # are positive you know what already depends on it. cur.execute("SELECT barcode_scan_id, barcode, " "scan_timestamp, sample_status, " "technician_notes " "FROM barcodes.barcode_scans " "WHERE barcode=%s " "ORDER BY scan_timestamp asc", (sample_barcode,)) # this can't be None; worst-case is an empty list scans_info = _rows_to_dicts_list(cur.fetchall()) latest_scan = None if len(scans_info) > 0: # NB: the correctness of this depends on the scans (queried # right above) being in ascending order by timestamp latest_scan = scans_info[len(scans_info)-1] # get details about this barcode itself; CAN be None if the # barcode doesn't exist in db barcode_info = None cur.execute("SELECT barcode, assigned_on, status, " "sample_postmark_date, biomass_remaining, " "sequencing_status, obsolete, " "create_date_time, kit_id " "FROM barcodes.barcode " "WHERE barcode = %s", (sample_barcode,)) barcode_row = cur.fetchone() if barcode_row is not None: barcode_info = dict(barcode_row) if account is None and source is None and sample is None and \ len(projects_info) == 0 and len(scans_info) == 0 \ and barcode_info is None: return None diagnostic = { "account": account, "source": source, "sample": sample, "latest_scan": latest_scan, "scans_info": scans_info, "barcode_info": barcode_info, "projects_info": projects_info } if grab_kit: # get kit object if kit_id is not None: kit_repo = KitRepo(self._transaction) kit = kit_repo.get_kit_all_samples_by_kit_id(kit_id) diagnostic["kit"] = kit return diagnostic
def query_email_stats(body, token_info): validate_admin_access(token_info) email_list = body.get("emails", []) project = body.get("project") results = [] with Transaction() as t: account_repo = AccountRepo(t) kit_repo = KitRepo(t) source_repo = SourceRepo(t) sample_repo = SampleRepo(t) for email in email_list: result = {'email': email, 'project': project} results.append(result) # can use internal lookup by email, because we have admin access account = account_repo._find_account_by_email(email) # noqa if account is None: result['summary'] = "No Account" continue else: result['account_id'] = account.id result['creation_time'] = account.creation_time result['kit_name'] = account.created_with_kit_id if account.created_with_kit_id is not None: unused = kit_repo.get_kit_unused_samples( account.created_with_kit_id ) if unused is None: result['unclaimed-samples-in-kit'] = 0 else: result['unclaimed-samples-in-kit'] = len(unused.samples) sample_statuses = defaultdict(int) sources = source_repo.get_sources_in_account(account.id) samples_in_project = 0 for source in sources: samples = sample_repo.get_samples_by_source(account.id, source.id) for sample in samples: if project is not None and \ project != "" and \ project not in sample.sample_projects: continue samples_in_project += 1 sample_status = sample_repo.get_sample_status( sample.barcode, sample._latest_scan_timestamp # noqa ) if sample_status is None: sample_status = "never-scanned" sample_statuses[sample_status] += 1 result.update(sample_statuses) if result.get('unclaimed-samples-in-kit', 0) > 0: result['summary'] = 'Possible Unreturned Samples' elif samples_in_project == 0: result['summary'] = "No Samples In Specified Project" elif result.get('sample-is-valid') == samples_in_project: result['summary'] = 'All Samples Valid' else: result['summary'] = 'May Require User Interaction' return jsonify(results), 200