def get_nonconsented_scanned_barcodes(self, kit_id): """Returns list of barcodes that have been scanned but not consented Parameters ---------- kit_id : str The supplied kit identifier to check for barcodes. Returns ------- list of str The barcodes, if any, that have been scanned but not consented """ sql = """SELECT barcode FROM ag_kit_barcodes INNER JOIN ag_kit USING (ag_kit_id) RIGHT JOIN ag_login USING (ag_login_id) LEFT JOIN barcode USING (barcode) FULL JOIN ag.source_barcodes_surveys USING (barcode) WHERE ag.source_barcodes_surveys.survey_id IS NULL AND scan_date IS NOT NULL AND ag_login_id = %s""" with TRN: user = self.get_user_for_kit(kit_id) TRN.add(sql, [user]) return TRN.execute_fetchflatten()
def _get_unverified_kits(self): """Gets list of unverified kit IDs, Helper function for tests""" sql = """SELECT supplied_kit_id FROM AG_KIT WHERE NOT kit_verified = 'y'""" with TRN: TRN.add(sql) return TRN.execute_fetchflatten()
def _get_unverified_kits(self): """Gets list of unverified kit IDs, Helper function for tests""" sql = """SELECT supplied_kit_id FROM AG_KIT WHERE NOT kit_verified = 'y'""" with TRN: TRN.add(sql) return TRN.execute_fetchflatten()
def getAvailableBarcodes(self, ag_login_id): sql = """SELECT barcode FROM ag_kit_barcodes INNER JOIN ag_kit USING (ag_kit_id) WHERE coalesce(sample_date::text, '') = '' AND kit_verified = 'y' AND ag_login_id = %s""" with TRN: TRN.add(sql, [ag_login_id]) return TRN.execute_fetchflatten()
def getAnimalParticipants(self, ag_login_id): sql = """SELECT DISTINCT participant_name from ag.ag_login_surveys JOIN ag.survey_answers USING (survey_id) JOIN ag.group_questions gq USING (survey_question_id) JOIN ag.surveys ags USING (survey_group) WHERE ag_login_id = %s AND ags.survey_id = %s""" with TRN: TRN.add(sql, [ag_login_id, 2]) return TRN.execute_fetchflatten()
def getAnimalParticipants(self, ag_login_id): sql = """SELECT participant_name from ag.ag_login_surveys JOIN ag.survey_answers USING (survey_id) JOIN ag.group_questions gq USING (survey_question_id) JOIN ag.surveys ags USING (survey_group) WHERE ag_login_id = %s AND ags.survey_id = %s""" with TRN: TRN.add(sql, [ag_login_id, 2]) return TRN.execute_fetchflatten()
def getAvailableBarcodes(self, ag_login_id): sql = """SELECT barcode FROM ag_kit_barcodes INNER JOIN ag_kit USING (ag_kit_id) WHERE coalesce(sample_date::text, '') = '' AND kit_verified = 'y' AND ag_login_id = %s""" with TRN: TRN.add(sql, [ag_login_id]) return TRN.execute_fetchflatten()
def getHumanParticipants(self, ag_login_id): # get people from new survey setup sql = """SELECT DISTINCT participant_name from ag.ag_login_surveys LEFT JOIN ag.survey_answers USING (survey_id) JOIN ag.group_questions gq USING (survey_question_id) JOIN ag.surveys ags USING (survey_group) WHERE ag_login_id = %s AND ags.survey_id = %s""" with TRN: TRN.add(sql, [ag_login_id, 1]) return TRN.execute_fetchflatten()
def test_execute_fetchflatten(self): with TRN: sql = """INSERT INTO ag.test_table (str_column, int_column) VALUES (%s, %s)""" args = [['insert1', 1], ['insert2', 2], ['insert3', 3]] TRN.add(sql, args, many=True) sql = "SELECT str_column, int_column FROM ag.test_table" TRN.add(sql) sql = "SELECT int_column FROM ag.test_table" TRN.add(sql) obs = TRN.execute_fetchflatten() self.assertEqual(obs, [1, 2, 3]) sql = "SELECT 42" TRN.add(sql) obs = TRN.execute_fetchflatten(idx=3) self.assertEqual(obs, ['insert1', 1, 'insert2', 2, 'insert3', 3])
def get_countries(self): """ Returns ------- list of str All country names in database""" with TRN: sql = 'SELECT country FROM ag.iso_country_lookup ORDER BY country' TRN.add(sql) return TRN.execute_fetchflatten()
def test_execute_fetchflatten(self): with TRN: sql = """INSERT INTO ag.test_table (str_column, int_column) VALUES (%s, %s)""" args = [['insert1', 1], ['insert2', 2], ['insert3', 3]] TRN.add(sql, args, many=True) sql = "SELECT str_column, int_column FROM ag.test_table" TRN.add(sql) sql = "SELECT int_column FROM ag.test_table" TRN.add(sql) obs = TRN.execute_fetchflatten() self.assertEqual(obs, [1, 2, 3]) sql = "SELECT 42" TRN.add(sql) obs = TRN.execute_fetchflatten(idx=3) self.assertEqual(obs, ['insert1', 1, 'insert2', 2, 'insert3', 3])
def get_countries(self): """ Returns ------- list of str All country names in database""" with TRN: sql = 'SELECT country FROM ag.iso_country_lookup ORDER BY country' TRN.add(sql) return TRN.execute_fetchflatten()
def getHumanParticipants(self, ag_login_id): # get people from new survey setup sql = """SELECT DISTINCT participant_name from ag.ag_login_surveys LEFT JOIN ag.survey_answers USING (survey_id) JOIN ag.group_questions gq USING (survey_question_id) JOIN ag.surveys ags USING (survey_group) WHERE ag_login_id = %s AND ags.survey_id = %s""" with TRN: TRN.add(sql, [ag_login_id, 1]) return TRN.execute_fetchflatten()
def getBarcodesByKit(self, kitid): """Returns a list of barcodes in a kit kitid is the supplied_kit_id from the ag_kit table """ sql = """SELECT barcode FROM ag_kit_barcodes INNER JOIN ag_kit USING (ag_kit_id) WHERE supplied_kit_id = %s""" with TRN: TRN.add(sql, [kitid]) return TRN.execute_fetchflatten()
def getBarcodesByKit(self, kitid): """Returns a list of barcodes in a kit kitid is the supplied_kit_id from the ag_kit table """ sql = """SELECT barcode FROM ag_kit_barcodes INNER JOIN ag_kit USING (ag_kit_id) WHERE supplied_kit_id = %s""" with TRN: TRN.add(sql, [kitid]) return TRN.execute_fetchflatten()
def __init__(self, ID, group_name): with TRN: self.id = ID self.group_name = group_name self.set_response = None sql = """SELECT sr.{0} FROM {1} q JOIN {2} qr ON q.survey_question_id = qr.survey_question_id JOIN {3} sr ON qr.response = sr.{0} WHERE q.survey_question_id = %s ORDER BY qr.display_index """.format(_LOCALE_COLUMN, self._survey_question_table, self._question_response_table, self._response_table) TRN.add(sql, [self.id]) self.responses = TRN.execute_fetchflatten() if not self.responses: self.responses = None sql = """SELECT survey_response_type FROM {0} WHERE survey_question_id = %s """.format(self._response_type_table) TRN.add(sql, [self.id]) self.response_type = TRN.execute_fetchlast() sql = """SELECT {0} FROM {1} WHERE survey_question_id = %s """.format(_LOCALE_COLUMN, self._survey_question_table) TRN.add(sql, [self.id]) self.question = TRN.execute_fetchlast() sql = """SELECT american FROM {0} WHERE survey_question_id = %s """.format(self._survey_question_table) TRN.add(sql, [self.id]) self.american_question = TRN.execute_fetchlast() self.triggers = self._triggers() self.qid = '_'.join(self.group_name.split() + [str(self.id)]) element_ids, elements = self._interface_elements() self.interface_elements = elements self.interface_element_ids = [ '%s_%d' % (self.qid, i) for i in element_ids ]
def getAGKitIDsByEmail(self, email): """Returns a list of kitids based on email email is email address of login returns a list of kit_id's associated with the email or an empty list """ with TRN: sql = """SELECT supplied_kit_id FROM ag_kit INNER JOIN ag_login USING (ag_login_id) WHERE email = %s""" TRN.add(sql, [email.lower()]) return TRN.execute_fetchflatten()
def getAGKitIDsByEmail(self, email): """Returns a list of kitids based on email email is email address of login returns a list of kit_id's associated with the email or an empty list """ with TRN: sql = """SELECT supplied_kit_id FROM ag_kit INNER JOIN ag_login USING (ag_login_id) WHERE email = %s""" TRN.add(sql, [email.lower()]) return TRN.execute_fetchflatten()
def __init__(self, ID, group_name): with TRN: self.id = ID self.group_name = group_name self.set_response = None sql = """SELECT sr.{0} FROM {1} q JOIN {2} qr ON q.survey_question_id = qr.survey_question_id JOIN {3} sr ON qr.response = sr.{0} WHERE q.survey_question_id = %s ORDER BY qr.display_index """.format(_LOCALE_COLUMN, self._survey_question_table, self._question_response_table, self._response_table) TRN.add(sql, [self.id]) self.responses = TRN.execute_fetchflatten() if not self.responses: self.responses = None sql = """SELECT survey_response_type FROM {0} WHERE survey_question_id = %s """.format(self._response_type_table) TRN.add(sql, [self.id]) self.response_type = TRN.execute_fetchlast() sql = """SELECT {0} FROM {1} WHERE survey_question_id = %s """.format(_LOCALE_COLUMN, self._survey_question_table) TRN.add(sql, [self.id]) self.question = TRN.execute_fetchlast() sql = """SELECT american FROM {0} WHERE survey_question_id = %s """.format(self._survey_question_table) TRN.add(sql, [self.id]) self.american_question = TRN.execute_fetchlast() self.triggers = self._triggers() self.qid = '_'.join(self.group_name.split() + [str(self.id)]) element_ids, elements = self._interface_elements() self.interface_elements = elements self.interface_element_ids = ['%s_%d' % (self.qid, i) for i in element_ids]
def get_nonconsented_scanned_barcodes(self, kit_id): """Returns list of barcodes that have been scanned but not consented Parameters ---------- kit_id : str The supplied kit identifier to check for barcodes. Returns ------- list of str The barcodes, if any, that have been scanned but not consented """ sql = """SELECT barcode FROM ag_kit_barcodes INNER JOIN ag_kit USING (ag_kit_id) RIGHT JOIN ag_login USING (ag_login_id) LEFT JOIN barcode USING (barcode) WHERE survey_id IS NULL AND scan_date IS NOT NULL AND ag_login_id = %s""" with TRN: user = self.get_user_for_kit(kit_id) TRN.add(sql, [user]) return TRN.execute_fetchflatten()
def __init__(self, ID): self.id = ID with TRN: sql = """SELECT survey_group FROM {0} WHERE survey_id = %s ORDER BY survey_group""".format(self._surveys_table) TRN.add(sql, [self.id]) results = TRN.execute_fetchflatten() self.groups = [Group(x) for x in results] self.questions = {} self.question_types = {} for group in self.groups: for question in group.questions: self.question_types[question.id] = question.response_type self.questions[question.id] = question sql = """SELECT {0} FROM {1} WHERE american='Unspecified' """.format(_LOCALE_COLUMN, self._survey_response_table) TRN.add(sql) self.unspecified = TRN.execute_fetchlast()
def __init__(self, ID): self.id = ID with TRN: sql = """SELECT survey_group FROM {0} WHERE survey_id = %s ORDER BY survey_group""".format(self._surveys_table) TRN.add(sql, [self.id]) results = TRN.execute_fetchflatten() self.groups = [Group(x) for x in results] self.questions = {} self.question_types = {} for group in self.groups: for question in group.questions: self.question_types[question.id] = question.response_type self.questions[question.id] = question sql = """SELECT {0} FROM {1} WHERE american='Unspecified' """.format(_LOCALE_COLUMN, self._survey_response_table) TRN.add(sql) self.unspecified = TRN.execute_fetchlast()
def get_all_handout_kits(self): with TRN: sql = 'SELECT kit_id FROM ag.ag_handout_kits' TRN.add(sql) return TRN.execute_fetchflatten()
def get_all_handout_kits(self): with TRN: sql = 'SELECT kit_id FROM ag.ag_handout_kits' TRN.add(sql) return TRN.execute_fetchflatten()