def is_deposited_ebi(self, barcode):
"""Check if barcode is deposited to EBI
Parameters
----------
barcode : str
Barcode to check
Returns
-------
bool
If the barcode has been deposited (True) or has not (False)
Raises
------
ValueError
Barcode is not a registered AG barcodes
"""
with TRN:
sql = """SELECT EXISTS(
SELECT 1 FROM ag.ag_kit_barcodes WHERE barcode = %s)"""
TRN.add(sql, [barcode])
if not TRN.execute_fetchlast():
raise ValueError('Barcode %s not a registered AG barcode' %
barcode)
sql = "SELECT deposited FROM ag.ag_kit_barcodes WHERE barcode = %s"
TRN.add(sql, [barcode])
return TRN.execute_fetchlast()
def is_deposited_ebi(self, barcode):
"""Check if barcode is deposited to EBI
Parameters
----------
barcode : str
Barcode to check
Returns
-------
bool
If the barcode has been deposited (True) or has not (False)
Raises
------
ValueError
Barcode is not a registered AG barcodes
"""
with TRN:
sql = """SELECT EXISTS(
SELECT 1 FROM ag.ag_kit_barcodes WHERE barcode = %s)"""
TRN.add(sql, [barcode])
if not TRN.execute_fetchlast():
raise ValueError('Barcode %s not a registered AG barcode' %
barcode)
sql = "SELECT deposited FROM ag.ag_kit_barcodes WHERE barcode = %s"
TRN.add(sql, [barcode])
return TRN.execute_fetchlast()
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 __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 patch_db(patches_dir=PATCHES_DIR, verbose=False):
"""Patches the database schema based on the settings table
Pulls the current patch from the settings table and applies all subsequent
patches found in the patches directory.
"""
with TRN:
TRN.add("SELECT current_patch FROM settings")
current_patch = TRN.execute_fetchlast()
current_patch_fp = join(patches_dir, current_patch)
sql_glob = join(patches_dir, '*.sql')
patch_files = natsorted(glob(sql_glob))
if current_patch == 'unpatched':
next_patch_index = 0
elif current_patch_fp not in patch_files:
raise RuntimeError("Cannot find patch file %s" % current_patch)
else:
next_patch_index = patch_files.index(current_patch_fp) + 1
patch_update_sql = "UPDATE settings SET current_patch = %s"
for patch_fp in patch_files[next_patch_index:]:
patch_filename = split(patch_fp)[-1]
with open(patch_fp, 'U') as patch_file:
if verbose:
echo('\tApplying patch %s...' % patch_filename)
TRN.add(patch_file.read())
TRN.add(patch_update_sql, [patch_filename])
def patch_db(patches_dir=PATCHES_DIR, verbose=False):
"""Patches the database schema based on the settings table
Pulls the current patch from the settings table and applies all subsequent
patches found in the patches directory.
"""
with TRN:
TRN.add("SELECT current_patch FROM settings")
current_patch = TRN.execute_fetchlast()
current_patch_fp = join(patches_dir, current_patch)
sql_glob = join(patches_dir, '*.sql')
patch_files = natsorted(glob(sql_glob))
if current_patch == 'unpatched':
next_patch_index = 0
elif current_patch_fp not in patch_files:
raise RuntimeError("Cannot find patch file %s" % current_patch)
else:
next_patch_index = patch_files.index(current_patch_fp) + 1
patch_update_sql = "UPDATE settings SET current_patch = %s"
for patch_fp in patch_files[next_patch_index:]:
patch_filename = split(patch_fp)[-1]
with open(patch_fp, 'U') as patch_file:
if verbose:
echo('\tApplying patch %s...' % patch_filename)
TRN.add(patch_file.read())
TRN.add(patch_update_sql, [patch_filename])
def deleteAGParticipantSurvey(self, ag_login_id, participant_name):
# Remove user from new schema
with TRN:
sql = """SELECT survey_id
FROM ag_login_surveys
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, (ag_login_id, participant_name))
survey_id = TRN.execute_fetchlast()
sql = "DELETE FROM survey_answers WHERE survey_id = %s"
TRN.add(sql, [survey_id])
sql = "DELETE FROM survey_answers_other WHERE survey_id = %s"
TRN.add(sql, [survey_id])
# Reset survey attached to barcode(s)
sql = """UPDATE ag_kit_barcodes
SET survey_id = NULL
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
sql = "DELETE FROM promoted_survey_ids WHERE survey_id = %s"
TRN.add(sql, [survey_id])
# Delete last due to foreign keys
sql = "DELETE FROM ag_login_surveys WHERE survey_id = %s"
TRN.add(sql, [survey_id])
sql = """DELETE FROM ag_consent
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, [ag_login_id, participant_name])
def american_name(self):
"""Gets the locale-specific name of the group"""
with TRN:
sql = """SELECT american
FROM {0}
WHERE group_order = %s""".format(self._group_table)
TRN.add(sql, [self.id])
return TRN.execute_fetchlast()
def american_name(self):
"""Gets the locale-specific name of the group"""
with TRN:
sql = """SELECT american
FROM {0}
WHERE group_order = %s""".format(self._group_table)
TRN.add(sql, [self.id])
return TRN.execute_fetchlast()
def tester():
self.assertEqual(TRN._contexts_entered, 1)
with TRN:
self.assertEqual(TRN._contexts_entered, 2)
sql = """SELECT EXISTS(
SELECT * FROM ag.test_table WHERE int_column=%s)"""
TRN.add(sql, [2])
self.assertTrue(TRN.execute_fetchlast())
self.assertEqual(TRN._contexts_entered, 1)
def name(self):
"""Gets the locale-specific name of the group"""
with TRN:
sql = """SELECT {0}
FROM {1}
WHERE group_order = %s""".format(_LOCALE_COLUMN,
self._group_table)
TRN.add(sql, [self.id])
return TRN.execute_fetchlast()
def check_if_consent_exists(self, ag_login_id, participant_name):
"""Return True if a consent already exists"""
with TRN:
sql = """SELECT EXISTS(
SELECT 1
FROM ag_consent
WHERE ag_login_id = %s AND participant_name = %s)"""
TRN.add(sql, [ag_login_id, participant_name])
return TRN.execute_fetchlast()
def name(self):
"""Gets the locale-specific name of the group"""
with TRN:
sql = """SELECT {0}
FROM {1}
WHERE group_order = %s""".format(_LOCALE_COLUMN,
self._group_table)
TRN.add(sql, [self.id])
return TRN.execute_fetchlast()
def tester():
self.assertEqual(TRN._contexts_entered, 1)
with TRN:
self.assertEqual(TRN._contexts_entered, 2)
sql = """SELECT EXISTS(
SELECT * FROM ag.test_table WHERE int_column=%s)"""
TRN.add(sql, [2])
self.assertTrue(TRN.execute_fetchlast())
self.assertEqual(TRN._contexts_entered, 1)
def check_if_consent_exists(self, ag_login_id, participant_name):
"""Return True if a consent already exists"""
with TRN:
sql = """SELECT EXISTS(
SELECT 1
FROM ag_consent
WHERE ag_login_id = %s AND participant_name = %s)"""
TRN.add(sql, [ag_login_id, participant_name])
return TRN.execute_fetchlast()
def patch_number():
# Make sure the system is using the latest patch before starting up.
with TRN:
TRN.add('SELECT current_patch FROM ag.settings')
system = TRN.execute_fetchlast()
patches_dir = join(dirname(abspath(__file__)), '../db/patches')
latest = sorted(listdir(patches_dir)).pop()
if latest != system:
raise EnvironmentError(("Not running latest patch. System: %s "
"Latest: %s") % (system, latest))
def test_execute_fetchlast(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
sql = """SELECT EXISTS(
SELECT * FROM ag.test_table WHERE int_column=%s)"""
TRN.add(sql, [2])
self.assertTrue(TRN.execute_fetchlast())
def test_execute_fetchlast(self):
with TRN:
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
TRN.add(sql, args, many=True)
sql = """SELECT EXISTS(
SELECT * FROM ag.test_table WHERE int_column=%s)"""
TRN.add(sql, [2])
self.assertTrue(TRN.execute_fetchlast())
def deleteSample(self, barcode, ag_login_id):
""" Removes by either releasing barcode back for relogging or withdraw
Parameters
----------
barcode : str
Barcode to delete
ag_login_id : UUID4
Login ID for the barcode
Notes
-----
Strictly speaking the ag_login_id isn't needed but it makes it really
hard to hack the function when you would need to know someone else's
login id (a GUID) to delete something maliciously.
If the barcode has never been scanned, assume a mis-log and wipe it so
barcode can be logged again. If barcode has been scanned, that means we
have recieved it and must withdraw it to delete it from the system.
"""
with TRN:
# Figure out if we've received the barcode or not
sql = "SELECT scan_date FROM barcode WHERE barcode = %s"
TRN.add(sql, [barcode])
received = TRN.execute_fetchlast()
if not received:
# Not recieved, so we release the barcode back to be relogged
set_text = """site_sampled = NULL,
sample_time = NULL, sample_date = NULL,
environment_sampled = NULL, notes = NULL"""
sql = "UPDATE barcode SET status = NULL WHERE barcode = %s"
TRN.add(sql, [barcode])
else:
# barcode already recieved, so we withdraw the barcode
set_text = "withdrawn = 'Y'"
sql = """UPDATE ag_kit_barcodes
SET {}
WHERE barcode IN (
SELECT akb.barcode
FROM ag_kit_barcodes akb
INNER JOIN ag_kit ak USING (ag_kit_id)
WHERE ak.ag_login_id = %s
AND akb.barcode = %s)""".format(set_text)
TRN.add(sql, [ag_login_id, barcode])
sql = """DELETE FROM ag.source_barcodes_surveys
WHERE barcode = %s"""
TRN.add(sql, [barcode])
def ag_verify_kit_password_change_code(self, email, kitid, passcode):
"""returns true if it still in the password change window
email is the email address of the participant
kitid is the supplied_kit_id in the ag_kit table
passcode is the password change verification value
"""
sql = """SELECT EXISTS(SELECT pass_reset_time
FROM ag.ag_kit
INNER JOIN ag.ag_login USING (ag_login_id)
WHERE pass_reset_code = %s and email = %s
AND supplied_kit_id = %s
AND NOW() < pass_reset_time)"""
with TRN:
TRN.add(sql, [passcode, email, kitid])
return TRN.execute_fetchlast()
def ag_verify_kit_password_change_code(self, email, kitid, passcode):
"""returns true if it still in the password change window
email is the email address of the participant
kitid is the supplied_kit_id in the ag_kit table
passcode is the password change verification value
"""
sql = """SELECT EXISTS(SELECT pass_reset_time
FROM ag.ag_kit
INNER JOIN ag.ag_login USING (ag_login_id)
WHERE pass_reset_code = %s and email = %s
AND supplied_kit_id = %s
AND NOW() < pass_reset_time)"""
with TRN:
TRN.add(sql, [passcode, email, kitid])
return TRN.execute_fetchlast()
def deleteSample(self, barcode, ag_login_id):
""" Removes by either releasing barcode back for relogging or withdraw
Parameters
----------
barcode : str
Barcode to delete
ag_login_id : UUID4
Login ID for the barcode
Notes
-----
Strictly speaking the ag_login_id isn't needed but it makes it really
hard to hack the function when you would need to know someone else's
login id (a GUID) to delete something maliciously.
If the barcode has never been scanned, assume a mis-log and wipe it so
barcode can be logged again. If barcode has been scanned, that means we
have recieved it and must withdraw it to delete it from the system.
"""
with TRN:
# Figure out if we've received the barcode or not
sql = "SELECT scan_date FROM barcode WHERE barcode = %s"
TRN.add(sql, [barcode])
received = TRN.execute_fetchlast()
if not received:
# Not recieved, so we release the barcode back to be relogged
set_text = """site_sampled = NULL,
sample_time = NULL, sample_date = NULL,
environment_sampled = NULL, notes = NULL,
survey_id = NULL"""
sql = "UPDATE barcode SET status = NULL WHERE barcode = %s"
TRN.add(sql, [barcode])
else:
# barcode already recieved, so we withdraw the barcode
set_text = "withdrawn = 'Y'"
sql = """UPDATE ag_kit_barcodes
SET {}
WHERE barcode IN (
SELECT akb.barcode
FROM ag_kit_barcodes akb
INNER JOIN ag_kit ak USING (ag_kit_id)
WHERE ak.ag_login_id = %s
AND akb.barcode = %s)""".format(set_text)
TRN.add(sql, [ag_login_id, barcode])
def addAGLogin(self, email, name, address, city, state, zip_, country):
"""Adds a new login or returns the login_id if email already exists
Parameters
----------
email : str
Email to register for user
name : str
Name to register for user
address : str
Street address to register for user
city : str
City to register for user
state : str
State to register for user
zip_ : str
Postal code to register for user
country : str
Country to register for user
Returns
-------
ag_login_id : str
UUID for new user, or existing user if email already in system
"""
with TRN:
clean_email = email.strip().lower()
ag_login_id = self.check_login_exists(email)
if not ag_login_id:
# create the login
sql = """INSERT INTO ag_login
(email, name, address, city, state, zip, country)
VALUES (%s, %s, %s, %s, %s, %s, %s)
RETURNING ag_login_id"""
TRN.add(
sql,
[clean_email, name, address, city, state, zip_, country])
ag_login_id = TRN.execute_fetchlast()
return ag_login_id
def addAGLogin(self, email, name, address, city, state, zip_, country):
"""Adds a new login or returns the login_id if email already exists
Parameters
----------
email : str
Email to register for user
name : str
Name to register for user
address : str
Street address to register for user
city : str
City to register for user
state : str
State to register for user
zip_ : str
Postal code to register for user
country : str
Country to register for user
Returns
-------
ag_login_id : str
UUID for new user, or existing user if email already in system
"""
with TRN:
clean_email = email.strip().lower()
ag_login_id = self.check_login_exists(email)
if not ag_login_id:
# create the login
sql = """INSERT INTO ag_login
(email, name, address, city, state, zip, country)
VALUES (%s, %s, %s, %s, %s, %s, %s)
RETURNING ag_login_id"""
TRN.add(sql, [clean_email, name, address, city, state, zip_,
country])
ag_login_id = TRN.execute_fetchlast()
return ag_login_id
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 check_access(self, supplied_kit_id, barcode):
"""Check if the user has access to the barcode
Parameters
----------
supplied_kit_id : str
The user's supplied kit ID
barcode : str
The barcode to check access for
Returns
-------
boolean
True if the user can access the barcode, False otherwise
"""
with TRN:
ag_login_id = self.get_user_for_kit(supplied_kit_id)
sql = """SELECT EXISTS (
SELECT barcode
FROM ag.ag_kit
JOIN ag.ag_kit_barcodes USING (ag_kit_id)
WHERE ag_login_id = %s AND barcode = %s)"""
TRN.add(sql, [ag_login_id, barcode])
return 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 check_access(self, supplied_kit_id, barcode):
"""Check if the user has access to the barcode
Parameters
----------
supplied_kit_id : str
The user's supplied kit ID
barcode : str
The barcode to check access for
Returns
-------
boolean
True if the user can access the barcode, False otherwise
"""
with TRN:
ag_login_id = self.get_user_for_kit(supplied_kit_id)
sql = """SELECT EXISTS (
SELECT barcode
FROM ag.ag_kit
JOIN ag.ag_kit_barcodes USING (ag_kit_id)
WHERE ag_login_id = %s AND barcode = %s)"""
TRN.add(sql, [ag_login_id, barcode])
return TRN.execute_fetchlast()
def store_survey(self, consent_details, with_fk_inserts,
without_fk_inserts):
"""Store a survey
Parameters
----------
consent_details : dict
Participant consent details
with_fk_inserts : list
[(str, int, str)] where str is the survey_id, int is a
survey_question.survey_question_id and str is a
survey_response.american
without_fk_inserts : list
[(str, int, str)] where str is the survey_id, int is a
survey_question.survey_question_id and str is a json representation
of the data to insert
"""
with TRN:
TRN.add("""SELECT EXISTS(
SELECT 1
FROM ag_login_surveys
WHERE survey_id=%s)""",
[consent_details['survey_id']])
if TRN.execute_fetchlast():
# if the survey exists, remove all its current answers
TRN.add("""DELETE FROM survey_answers
WHERE survey_id=%s""",
[consent_details['survey_id']])
TRN.add("""DELETE FROM survey_answers_other
WHERE survey_id=%s""",
[consent_details['survey_id']])
else:
# otherwise, we have a new survey
# If this is a primary survey, we need to add the consent
if 'secondary' not in consent_details:
TRN.add("""INSERT INTO ag_consent
(ag_login_id, participant_name, is_juvenile,
parent_1_name, parent_2_name,
deceased_parent, participant_email,
assent_obtainer, age_range, date_signed)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,
NOW())""",
(consent_details['login_id'],
consent_details['participant_name'],
consent_details['is_juvenile'],
consent_details['parent_1_name'],
consent_details['parent_2_name'],
consent_details['deceased_parent'],
consent_details['participant_email'],
consent_details['obtainer_name'],
consent_details['age_range']))
TRN.add("""INSERT INTO ag_login_surveys
(ag_login_id, survey_id, participant_name)
VALUES (%s, %s, %s)""",
(consent_details['login_id'],
consent_details['survey_id'],
consent_details['participant_name']))
# checks if user has previously been
# removed and has still revoked consent
sql = """SELECT ag_login_id FROM ag.consent_revoked"""
TRN.add(sql)
revoked = {result[0] for result in TRN.execute_fetchindex()}
# removes the user from the consent_revoked
# table if they are already in it
if consent_details['login_id'] in revoked:
sql = """DELETE FROM ag.consent_revoked
WHERE ag_login_id = %s
AND participant_name = %s
AND participant_email = %s"""
TRN.add(sql, [consent_details['login_id'],
consent_details['participant_name'],
consent_details['participant_email']])
TRN.execute()
# now we insert the answers
TRN.add("""INSERT INTO survey_answers
(survey_id, survey_question_id, response)
VALUES (%s, %s, %s)""", with_fk_inserts, many=True)
TRN.add("""INSERT INTO survey_answers_other
(survey_id,survey_question_id, response)
VALUES (%s, %s, %s)""", without_fk_inserts, many=True)
def registerHandoutKit(self, ag_login_id, supplied_kit_id):
"""Registeres a handout kit to a user
Parameters
----------
ag_login_id : str
UUID4 formatted string of login ID to associate with kit
supplied_kit_id : str
kit ID for the handout kit
Returns
-------
bool
True: success
False: insert failed due to IntegrityError
Raises
------
ValueError
Non-UUID4 value sent as ag_login_id
"""
with TRN:
# make sure properly formatted UUID passed in
UUID(ag_login_id, version=4)
printresults = self.checkPrintResults(supplied_kit_id)
# make sure login_id and skid exists
sql = """SELECT EXISTS(SELECT *
FROM ag.ag_login
WHERE ag_login_id = %s)"""
TRN.add(sql, [ag_login_id])
exists = TRN.execute_fetchlast()
if not exists:
return False
sql = """SELECT EXISTS(SELECT *
FROM ag.ag_handout_kits
WHERE kit_id = %s)"""
TRN.add(sql, [supplied_kit_id])
if not TRN.execute_fetchlast():
return False
sql = """
DO $do$
DECLARE
k_id uuid;
bc varchar;
BEGIN
INSERT INTO ag_kit
(ag_login_id, supplied_kit_id, kit_password, swabs_per_kit,
kit_verification_code, print_results)
SELECT '{0}', kit_id, password, swabs_per_kit,
verification_code, '{1}'
FROM ag_handout_kits WHERE kit_id = %s LIMIT 1
RETURNING ag_kit_id INTO k_id;
FOR bc IN
SELECT barcode
FROM ag_handout_barcodes
WHERE kit_id = %s
LOOP
INSERT INTO ag_kit_barcodes
(ag_kit_id, barcode, sample_barcode_file)
VALUES (k_id, bc, bc || '.jpg');
END LOOP;
DELETE FROM ag_handout_barcodes WHERE kit_id = %s;
DELETE FROM ag_handout_kits WHERE kit_id = %s;
END $do$;
""".format(ag_login_id, printresults)
TRN.add(sql, [supplied_kit_id] * 4)
try:
TRN.execute()
except psycopg2.IntegrityError:
logging.exception('Error on skid %s:' % ag_login_id)
return False
return True
def store_survey(self, consent_details, with_fk_inserts,
without_fk_inserts):
"""Store a survey
Parameters
----------
consent_details : dict
Participant consent details
with_fk_inserts : list
[(str, int, str)] where str is the survey_id, int is a
survey_question.survey_question_id and str is a
survey_response.american
without_fk_inserts : list
[(str, int, str)] where str is the survey_id, int is a
survey_question.survey_question_id and str is a json representation
of the data to insert
"""
with TRN:
TRN.add(
"""SELECT EXISTS(
SELECT 1
FROM ag_login_surveys
WHERE survey_id=%s)""", [consent_details['survey_id']])
if TRN.execute_fetchlast():
# if the survey exists, remove all its current answers
TRN.add(
"""DELETE FROM survey_answers
WHERE survey_id=%s""",
[consent_details['survey_id']])
TRN.add(
"""DELETE FROM survey_answers_other
WHERE survey_id=%s""",
[consent_details['survey_id']])
else:
# otherwise, we have a new survey
# If this is a primary survey, we need to add the consent
if 'secondary' not in consent_details:
TRN.add(
"""INSERT INTO ag_consent
(ag_login_id, participant_name, is_juvenile,
parent_1_name, parent_2_name,
deceased_parent, participant_email,
assent_obtainer, age_range, date_signed)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,
NOW())""",
(consent_details['login_id'],
consent_details['participant_name'],
consent_details['is_juvenile'],
consent_details['parent_1_name'],
consent_details['parent_2_name'],
consent_details['deceased_parent'],
consent_details['participant_email'],
consent_details['obtainer_name'],
consent_details['age_range']))
TRN.add(
"""INSERT INTO ag_login_surveys
(ag_login_id, survey_id, participant_name)
VALUES (%s, %s, %s)""",
(consent_details['login_id'], consent_details['survey_id'],
consent_details['participant_name']))
# now we insert the answers
TRN.add("""INSERT INTO survey_answers
(survey_id, survey_question_id, response)
VALUES (%s, %s, %s)""",
with_fk_inserts,
many=True)
TRN.add("""INSERT INTO survey_answers_other
(survey_id,survey_question_id, response)
VALUES (%s, %s, %s)""",
without_fk_inserts,
many=True)
def store_survey(self, consent_details, with_fk_inserts,
without_fk_inserts):
"""Store a survey
Parameters
----------
consent_details : dict
Participant consent details
with_fk_inserts : list
[(str, int, str)] where str is the survey_id, int is a
survey_question.survey_question_id and str is a
survey_response.american
without_fk_inserts : list
[(str, int, str)] where str is the survey_id, int is a
survey_question.survey_question_id and str is a json representation
of the data to insert
"""
with TRN:
TRN.add("""SELECT EXISTS(
SELECT 1
FROM ag_login_surveys
WHERE survey_id=%s)""",
[consent_details['survey_id']])
if TRN.execute_fetchlast():
# if the survey exists, remove all its current answers
TRN.add("""DELETE FROM survey_answers
WHERE survey_id=%s""",
[consent_details['survey_id']])
TRN.add("""DELETE FROM survey_answers_other
WHERE survey_id=%s""",
[consent_details['survey_id']])
else:
# otherwise, we have a new survey so we need to attach this
# survey ID to the consent and the login surveys join table
TRN.add("""INSERT INTO ag_consent
(ag_login_id, participant_name, is_juvenile,
parent_1_name, parent_2_name, deceased_parent,
participant_email, assent_obtainer, age_range,
date_signed)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())""",
(consent_details['login_id'],
consent_details['participant_name'],
consent_details['is_juvenile'],
consent_details['parent_1_name'],
consent_details['parent_2_name'],
consent_details['deceased_parent'],
consent_details['participant_email'],
consent_details['obtainer_name'],
consent_details['age_range']))
TRN.add("""INSERT INTO ag_login_surveys
(ag_login_id, survey_id, participant_name)
VALUES (%s, %s, %s)""",
(consent_details['login_id'],
consent_details['survey_id'],
consent_details['participant_name']))
# now we insert the answers
TRN.add("""INSERT INTO survey_answers
(survey_id, survey_question_id, response)
VALUES (%s, %s, %s)""", with_fk_inserts, many=True)
TRN.add("""INSERT INTO survey_answers_other
(survey_id,survey_question_id, response)
VALUES (%s, %s, %s)""", without_fk_inserts, many=True)
def registerHandoutKit(self, ag_login_id, supplied_kit_id):
"""Registeres a handout kit to a user
Parameters
----------
ag_login_id : str
UUID4 formatted string of login ID to associate with kit
supplied_kit_id : str
kit ID for the handout kit
Returns
-------
bool
True: success
False: insert failed due to IntegrityError
Raises
------
ValueError
Non-UUID4 value sent as ag_login_id
"""
with TRN:
# make sure properly formatted UUID passed in
UUID(ag_login_id, version=4)
printresults = self.checkPrintResults(supplied_kit_id)
# make sure login_id and skid exists
sql = """SELECT EXISTS(SELECT *
FROM ag.ag_login
WHERE ag_login_id = %s)"""
TRN.add(sql, [ag_login_id])
exists = TRN.execute_fetchlast()
if not exists:
return False
sql = """SELECT EXISTS(SELECT *
FROM ag.ag_handout_kits
WHERE kit_id = %s)"""
TRN.add(sql, [supplied_kit_id])
if not TRN.execute_fetchlast():
return False
sql = """
DO $do$
DECLARE
k_id uuid;
bc varchar;
BEGIN
INSERT INTO ag_kit
(ag_login_id, supplied_kit_id, kit_password, swabs_per_kit,
kit_verification_code, print_results)
SELECT '{0}', kit_id, password, swabs_per_kit,
verification_code, '{1}'
FROM ag_handout_kits WHERE kit_id = %s LIMIT 1
RETURNING ag_kit_id INTO k_id;
FOR bc IN
SELECT barcode
FROM ag_handout_barcodes
WHERE kit_id = %s
LOOP
INSERT INTO ag_kit_barcodes
(ag_kit_id, barcode, sample_barcode_file)
VALUES (k_id, bc, bc || '.jpg');
END LOOP;
DELETE FROM ag_handout_barcodes WHERE kit_id = %s;
DELETE FROM ag_handout_kits WHERE kit_id = %s;
END $do$;
""".format(ag_login_id, printresults)
TRN.add(sql, [supplied_kit_id] * 4)
try:
TRN.execute()
except psycopg2.IntegrityError:
logging.exception('Error on skid %s:' % ag_login_id)
return False
return True
