def get_kit_all_samples_by_kit_id(self, kit_id): sample_repo = SampleRepo(self._transaction) with self._transaction.cursor() as cur: cur.execute("SELECT " "ag_kit.ag_kit_id, " "ag_kit_barcodes.ag_kit_barcode_id " "FROM ag_kit LEFT JOIN ag_kit_barcodes ON " "ag_kit.ag_kit_id = ag_kit_barcodes.ag_kit_id " "WHERE " "ag_kit.ag_kit_id = %s", (kit_id,)) rows = cur.fetchall() if len(rows) == 0: return None else: samples = [sample_repo._get_sample_by_id(r[1]) for r in rows] return Kit(rows[0][0], samples)
def test_dissociate_sample_from_source_success(self): dummy_acct_id, dummy_source_id = create_dummy_source( "Bo", Source.SOURCE_TYPE_HUMAN, DUMMY_HUMAN_SOURCE, create_dummy_1=True) create_dummy_kit(dummy_acct_id, dummy_source_id) dummy_answered_survey_id = create_dummy_answered_survey( dummy_acct_id, dummy_source_id, dummy_sample_id=MOCK_SAMPLE_ID) base_url = '/api/accounts/{0}/sources/{1}/samples'.format( dummy_acct_id, dummy_source_id) sample_url = "{0}/{1}".format(base_url, MOCK_SAMPLE_ID) delete_resp = self.client.delete( '%s?%s' % (sample_url, self.default_lang_querystring), headers=self.dummy_auth ) # check response code self.assertEqual(204, delete_resp.status_code) # load the samples associated to this source get_response = self.client.get( '%s?%s' % (base_url, self.default_lang_querystring), headers=self.dummy_auth) # check response code self.assertEqual(200, get_response.status_code) # ensure there are zero samples associated with this source get_resp_obj = json.loads(get_response.data) self.assertEqual(get_resp_obj, []) # load the sample info _, expected_sample = create_dummy_sample_objects(False) with Transaction() as t: # make sure the sample's collection info is wiped sample_repo = SampleRepo(t) obs_sample = sample_repo._get_sample_by_id(MOCK_SAMPLE_ID) self.assertEqual(expected_sample.__dict__, obs_sample.__dict__) # make sure answered survey no longer associated with any samples answered_survey_repo = SurveyAnswersRepo(t) answered_survey_ids = answered_survey_repo.\ _get_survey_sample_associations(dummy_answered_survey_id) self.assertEqual([], answered_survey_ids)
def get_kit_unused_samples(self, supplied_kit_id): sample_repo = SampleRepo(self._transaction) # Business Logic: We now define an unclaimed sample as a sample with # a null source_id in ag_kit_barcodes with self._transaction.cursor() as cur: cur.execute("SELECT " "ag_kit.ag_kit_id, " "ag_kit_barcodes.ag_kit_barcode_id " "FROM ag_kit LEFT JOIN ag_kit_barcodes ON " "ag_kit.ag_kit_id = ag_kit_barcodes.ag_kit_id " "WHERE " "ag_kit.supplied_kit_id = %s AND " "ag_kit_barcodes.source_id is null", (supplied_kit_id,)) rows = cur.fetchall() if len(rows) == 0: return None else: samples = [sample_repo._get_sample_by_id(r[1]) for r in rows] return Kit(rows[0][0], samples)
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 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 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