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