def test_sources(self):
with TRN:
sql = """SELECT barcode
FROM source_barcodes_surveys
JOIN ag.ag_login_surveys USING (survey_id)
WHERE ag_login_id = %s AND survey_id = %s"""
exp_barcodes = ['000063476', '000063477', '000063478',
'000063479', '000063480', '000063481',
'000063482', '000063483', '000063484',
'000063487']
TRN.add(sql, ['0097e665-ea1d-483d-b248-402bcf6abf2a',
'5c7106b35dda787d'])
obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
self.assertEqual(set(exp_barcodes), set(obs_barcodes))
TRN.add(sql, ['0097e665-ea1d-483d-b248-402bcf6abf2a',
'fb6d5a66ef0dd8c7'])
obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
self.assertEqual(set(exp_barcodes), set(obs_barcodes))
exp_barcodes = ['000046215']
TRN.add(sql, ['0073af72-39e5-4bc8-9908-eff6c4ce2d6c',
'db2e324b45e34b97'])
obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
self.assertEqual(set(exp_barcodes), set(obs_barcodes))
TRN.add(sql, ['0073af72-39e5-4bc8-9908-eff6c4ce2d6c',
'eea585c6eb5dd4b5'])
obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
self.assertEqual(set(exp_barcodes), set(obs_barcodes))
TRN.add(sql, ['0073af72-39e5-4bc8-9908-eff6c4ce2d6c',
'fb420871cdcf5adb'])
obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
self.assertEqual(set(exp_barcodes), set(obs_barcodes))
def deleteAGParticipantSurvey(self, ag_login_id, participant_name):
# Remove user from new schema
with TRN:
sql = """SELECT survey_id, participant_email
FROM ag_login_surveys
JOIN ag_consent USING (ag_login_id, participant_name)
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, (ag_login_id, participant_name))
# collect all survey_ids and participant_names, since at least the
# former might be more than one.
survey_ids = set()
participant_emails = set()
for hit in TRN.execute_fetchindex():
survey_ids.add(hit[0])
participant_emails.add(hit[1])
sql = """SELECT barcode
FROM ag.source_barcodes_surveys
WHERE survey_id IN %s"""
TRN.add(sql, [tuple(survey_ids)])
barcodes = [x[0] for x in TRN.execute_fetchindex()]
sql = "DELETE FROM survey_answers WHERE survey_id IN %s"
TRN.add(sql, [tuple(survey_ids)])
sql = "DELETE FROM survey_answers_other WHERE survey_id IN %s"
TRN.add(sql, [tuple(survey_ids)])
# Reset survey attached to barcode(s)
for info in self.getParticipantSamples(ag_login_id,
participant_name):
self.deleteSample(info['barcode'], ag_login_id)
# Delete last due to foreign keys
sql = """DELETE FROM ag.source_barcodes_surveys
WHERE survey_id IN %s"""
TRN.add(sql, [tuple(survey_ids)])
# only delete barcode information, if this is the last survey for
# the given source, i.e. ag_login_id, participant_name combination
if len(survey_ids) == 1:
sql = """DELETE FROM ag.ag_kit_barcodes WHERE barcode IN %s"""
TRN.add(sql, [tuple(barcodes)])
sql = "DELETE FROM ag_login_surveys WHERE survey_id IN %s"
TRN.add(sql, [tuple(survey_ids)])
sql = """DELETE FROM ag_consent
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, [ag_login_id, participant_name])
sql = """INSERT INTO ag.consent_revoked
(ag_login_id,participant_name, participant_email)
VALUES (%s, %s, %s)"""
sql_args = [[ag_login_id, participant_name, pemail]
for pemail in participant_emails]
TRN.add(sql, sql_args, many=True)
TRN.execute()
def test_execute_fetchindex(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)
self.assertEqual(TRN.execute_fetchindex(), [['insert3', 3]])
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert4', 4], ['insert5', 5], ['insert6', 6]]
TRN.add(sql, args, many=True)
self.assertEqual(TRN.execute_fetchindex(3), [['insert4', 4]])
def test_execute_fetchindex(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)
self.assertEqual(TRN.execute_fetchindex(), [['insert3', 3]])
sql = """INSERT INTO ag.test_table (str_column, int_column)
VALUES (%s, %s) RETURNING str_column, int_column"""
args = [['insert4', 4], ['insert5', 5], ['insert6', 6]]
TRN.add(sql, args, many=True)
self.assertEqual(TRN.execute_fetchindex(3), [['insert4', 4]])
def test_oldtables(self):
# check that we have 13512 distinct "sources" in ag_login_surveys,
# which are of tuple ag_login_id, participant_name
with TRN:
sql = """SELECT COUNT(*)
FROM (SELECT DISTINCT ag_login_id, participant_name
FROM ag.ag_login_surveys) AS foo"""
TRN.add(sql, [])
num_sources = TRN.execute_fetchindex()[0][0]
self.assertEqual(num_sources, 13512)
with TRN:
sql = """SELECT COUNT(*)
FROM (SELECT DISTINCT ag_login_id, participant_name
FROM ag.ag_login_surveys
WHERE vioscreen_status IS NOT NULL) AS foo"""
TRN.add(sql, [])
num_vio_sources = TRN.execute_fetchindex()[0][0]
self.assertEqual(num_vio_sources, 1692)
# check number of unique surveys
with TRN:
sql = """SELECT COUNT(DISTINCT survey_id)
FROM ag.ag_login_surveys"""
TRN.add(sql, [])
num_surveys = TRN.execute_fetchindex()[0][0]
self.assertEqual(num_surveys, 13850)
# check number of sources with more than one survey
with TRN:
sql = """SELECT COUNT(*)
FROM (SELECT ag_login_id,
participant_name,
count(survey_id) AS cs
FROM ag.ag_login_surveys
GROUP BY ag_login_id, participant_name) AS foo
WHERE cs > 1"""
TRN.add(sql, [])
num_multi_sources = TRN.execute_fetchindex()[0][0]
self.assertEqual(num_multi_sources, 314)
# check number of consents
with TRN:
sql = """SELECT COUNT(*)
FROM (SELECT DISTINCT ag_login_id, participant_name
FROM ag.ag_consent) AS foo"""
TRN.add(sql, [])
num_consent = TRN.execute_fetchindex()[0][0]
self.assertEqual(num_consent, 13514)
def fetch_survey(self, survey_id):
"""Return {element_id: answer}
The answer is in the form of ["display_index"] or ["text"] depending on
if the answer has a foreign key or not. These data are serialized for
input into a WTForm.
"""
with TRN:
sql = """SELECT survey_question_id, display_index,
survey_response_type
FROM {0}
JOIN {1} USING (response, survey_question_id)
JOIN {2} USING (survey_question_id)
LEFT JOIN {3} USING (survey_question_id)
WHERE survey_id = %s AND retired = FALSE""".format(
self._survey_answers_table,
self._survey_question_response_table,
self._survey_question_response_type_table,
self._questions_table)
TRN.add(sql, [survey_id])
answers = TRN.execute_fetchindex()
TRN.add(
"""SELECT survey_question_id, response
FROM {0}
LEFT JOIN {1} using (survey_question_id)
WHERE survey_id = %s AND retired = FALSE""".format(
self._survey_answers_other_table, self._questions_table),
[survey_id])
answers_other = TRN.execute_fetchindex()
survey = defaultdict(list)
for qid, idx, qtype in answers:
eid = self.questions[qid].interface_element_ids[0]
if qtype == 'SINGLE':
survey[eid] = idx
else:
survey[eid].append(idx)
for qid, data in answers_other:
eid = self.questions[qid].interface_element_ids[0]
data = data.strip(' []"')
survey[eid] = data
if len(survey) == 0:
raise ValueError("Survey answers do not exist in DB: %s" %
survey_id)
return survey
def logParticipantSample(self, ag_login_id, barcode, sample_site,
environment_sampled, sample_date, sample_time,
participant_name, notes):
with TRN:
if sample_site is not None:
# Get survey id
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_fetchindex()
if not survey_id:
raise ValueError("No survey ID for ag_login_id %s and "
"participant name %s" %
(ag_login_id, participant_name))
survey_id = survey_id[0][0]
else:
# otherwise, it is an environmental sample
survey_id = None
# Add barcode info
sql = """UPDATE ag_kit_barcodes
SET site_sampled = %s, environment_sampled = %s,
sample_date = %s, sample_time = %s,
participant_name = %s, notes = %s, survey_id = %s
WHERE barcode = %s"""
TRN.add(sql, [sample_site, environment_sampled, sample_date,
sample_time, participant_name, notes, survey_id,
barcode])
def get_vioscreen_status(self, survey_id):
"""Retrieves the vioscreen status for a survey_id
Parameters
----------
survey_id : str
The survey to get status for
Returns
-------
int
Vioscreen status
Raises
------
ValueError
survey_id passed is not in the database
"""
with TRN:
sql = """SELECT vioscreen_status
FROM ag.ag_login_surveys
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
status = TRN.execute_fetchindex()
if not status:
raise ValueError("Survey ID %s not in database" % survey_id)
return status[0][0]
def get_survey_ids(self, ag_login_id, participant_name):
"""Return the survey IDs associated with a participant or None
Parameters
----------
ag_login_id : str
A valid login ID, that should be a test as a valid UUID
participant_name : str
A participant name
Returns
-------
dict or None
The survey IDs keyed to the survey id,
or None if a survey ID cannot be found.
Raises
------
ValueError
Unknown ag_login_id or participant_name passed
"""
with TRN:
sql = """SELECT DISTINCT s.survey_id, als.survey_id
FROM ag.ag_login_surveys als
LEFT JOIN ag.survey_answers sa USING (survey_id)
LEFT JOIN ag.group_questions gq USING (survey_question_id)
LEFT JOIN ag.surveys s USING (survey_group)
WHERE ag_login_id=%s AND participant_name=%s"""
TRN.add(sql, [ag_login_id, participant_name])
survey_id = TRN.execute_fetchindex()
if not survey_id:
raise ValueError("No survey ID found!")
return dict(i for i in survey_id)
def authenticateWebAppUser(self, username, password):
""" Attempts to validate authenticate the supplied username/password
Attempt to authenticate the user against the list of users in
web_app_user table. If successful, a dict with user innformation is
returned. If not, the function returns False.
"""
with TRN:
sql = """SELECT cast(ag_login_id as varchar(100)) as ag_login_id,
email, name, address, city,
state, zip, country,kit_password
FROM ag_login
INNER JOIN ag_kit USING (ag_login_id)
WHERE supplied_kit_id = %s"""
TRN.add(sql, [username])
row = TRN.execute_fetchindex()
if not row:
return False
results = dict(row[0])
if not bcrypt.verify(password, results['kit_password']):
return False
results['ag_login_id'] = str(results['ag_login_id'])
return results
def get_login_info(self, ag_login_id):
"""Get kit registration information
Parameters
----------
ag_login_id : str
A valid login ID, that should be a test as a valid UUID
Returns
-------
list of dict
A list of registration information associated with a common login
ID.
Raises
------
ValueError
Unknown ag_login_id passed
"""
with TRN:
sql = """SELECT ag_login_id, email, name, address, city, state,
zip, country
FROM ag_login
WHERE ag_login_id = %s"""
TRN.add(sql, [ag_login_id])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('ag_login_id not in database: %s' %
ag_login_id)
return [dict(row) for row in info]
def get_survey_id(self, ag_login_id, participant_name):
"""Return the survey ID associated with a participant or None
Parameters
----------
ag_login_id : str
A valid login ID, that should be a test as a valid UUID
participant_name : str
A participant name
Returns
-------
str or None
The survey ID, or None if a survey ID cannot be found.
Raises
------
ValueError
Unknown ag_login_id or participant_name passed
"""
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_fetchindex()
if not survey_id:
raise ValueError("No survey ID found!")
return survey_id[0][0]
def __init__(self, ID):
with TRN:
self.id = ID
n = self.american_name
sql = """SELECT gq.survey_question_id
FROM {0} sg
JOIN {1} gq ON sg.group_order = gq.survey_group
LEFT JOIN {2} sq USING (survey_question_id)
WHERE sg.group_order = %s AND sq.retired = FALSE
ORDER BY gq.display_index
""".format(self._group_table, self._group_questions_table,
self._questions_table)
TRN.add(sql, [self.id])
results = TRN.execute_fetchindex()
qs = [Question.factory(x[0], n) for x in results]
self.id_to_eid = {q.id: q.interface_element_ids for q in qs}
self.question_lookup = {q.id: q for q in qs}
self.questions = qs
self.supplemental_eids = set()
for q in qs:
for id_ in q.triggers:
triggered = self.question_lookup[id_]
triggered_eids = triggered.interface_element_ids
self.supplemental_eids.update(set(triggered_eids))
def _triggers(self):
"""What other question-response combinations this question can trigger
Returns
-------
tuple
(other_question_id, [triggering indices to that question])
"""
with TRN:
sql = """SELECT triggered_question, display_index
FROM {0} sst
JOIN {1} sqr
ON sst.survey_question_id=sqr.survey_question_id
AND sqr.response=sst.triggering_response
WHERE sst.survey_question_id = %s
ORDER BY triggered_question
""".format(self._supplemental_survey_table,
self._question_response_table)
TRN.add(sql, [self.id])
trigger_list = TRN.execute_fetchindex()
results = defaultdict(list)
for question, index in trigger_list:
results[question].append(index)
if results:
return results
else:
return ()
def authenticateWebAppUser(self, username, password):
""" Attempts to validate authenticate the supplied username/password
Attempt to authenticate the user against the list of users in
web_app_user table. If successful, a dict with user innformation is
returned. If not, the function returns False.
"""
with TRN:
sql = """SELECT cast(ag_login_id as varchar(100)) as ag_login_id,
email, name, address, city,
state, zip, country,kit_password
FROM ag_login
INNER JOIN ag_kit USING (ag_login_id)
WHERE supplied_kit_id = %s"""
TRN.add(sql, [username])
row = TRN.execute_fetchindex()
if not row:
return False
results = dict(row[0])
password = password.encode('utf-8')
if not bcrypt.checkpw(password, results['kit_password']):
return False
results['ag_login_id'] = str(results['ag_login_id'])
return results
def get_vioscreen_status(self, survey_id):
"""Retrieves the vioscreen status for a survey_id
Parameters
----------
survey_id : str
The survey to get status for
Returns
-------
int
Vioscreen status
Raises
------
ValueError
survey_id passed is not in the database
"""
with TRN:
sql = """SELECT vioscreen_status
FROM ag.ag_login_surveys
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
status = TRN.execute_fetchindex()
if not status:
raise ValueError("Survey ID %s not in database" % survey_id)
return status[0][0]
def __init__(self, ID):
with TRN:
self.id = ID
n = self.american_name
sql = """SELECT gq.survey_question_id
FROM {0} sg
JOIN {1} gq ON sg.group_order = gq.survey_group
LEFT JOIN {2} sq USING (survey_question_id)
WHERE sg.group_order = %s AND sq.retired = FALSE
ORDER BY gq.display_index
""".format(self._group_table, self._group_questions_table,
self._questions_table)
TRN.add(sql, [self.id])
results = TRN.execute_fetchindex()
qs = [Question.factory(x[0], n) for x in results]
self.id_to_eid = {q.id: q.interface_element_ids for q in qs}
self.question_lookup = {q.id: q for q in qs}
self.questions = qs
self.supplemental_eids = set()
for q in qs:
for id_ in q.triggers:
triggered = self.question_lookup[id_]
triggered_eids = triggered.interface_element_ids
self.supplemental_eids.update(set(triggered_eids))
def ut_get_participant_names_from_ag_login_id(self, ag_login_id):
""" Returns all participant_name(s) for a given ag_login_id.
For unit testing only!
Parameters
----------
ag_login_id : str
Existing ag_login_id.
Returns
-------
[[str]]
Example: ["Name - z\xc3\x96DOZ8(Z~'",
"Name - z\xc3\x96DOZ8(Z~'",
'Name - QpeY\xc3\xb8u#0\xc3\xa5<',
'Name - S)#@G]xOdL',
'Name - Y5"^&sGQiW',
'Name - L\xc3\xa7+c\r\xc3\xa5?\r\xc2\xbf!',
'Name - (~|w:S\xc3\x85#L\xc3\x84']
Raises
------
ValueError
If ag_login_id is not in DB.
"""
with TRN:
sql = """SELECT participant_name
FROM ag.ag_login_surveys
WHERE ag_login_id = %s"""
TRN.add(sql, [ag_login_id])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('ag_login_id not in database: %s' %
ag_login_id)
return [n[0] for n in info]
def get_login_info(self, ag_login_id):
"""Get kit registration information
Parameters
----------
ag_login_id : str
A valid login ID, that should be a test as a valid UUID
Returns
-------
list of dict
A list of registration information associated with a common login
ID.
Raises
------
ValueError
Unknown ag_login_id passed
"""
with TRN:
sql = """SELECT ag_login_id, email, name, address, city, state,
zip, country
FROM ag_login
WHERE ag_login_id = %s"""
TRN.add(sql, [ag_login_id])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('ag_login_id not in database: %s' %
ag_login_id)
return [dict(row) for row in info]
def ut_get_email_from_ag_login_id(self, ag_login_id):
""" Returns email for a given ag_login_id.
For unit testing only!
Parameters
----------
ag_login_id : str
Existing ag_login_id.
Returns
-------
str: email
Example: 'xX/tEv7O+T@6Ri7C.)LO'
Raises
------
ValueError
If ag_login_id is not in DB.
"""
with TRN:
sql = """SELECT email
FROM ag.ag_login
WHERE ag_login_id=%s"""
TRN.add(sql, [ag_login_id])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('No emails found.')
return info[0][0]
def ut_get_supplied_kit_id(self, ag_login_id):
""" Returns supplied_kit_id for a given ag_login_id.
For unit testing only!
Parameters
----------
ag_login_id : str
Existing ag_login_id.
Returns
-------
str
The supplied_kit_id for the given ag_login_id.
Example: 'DokBF'
Raises
------
ValueError
If ag_login_id is not in DB.
"""
with TRN:
sql = """SELECT supplied_kit_id
FROM ag.ag_kit
WHERE ag_login_id = %s"""
TRN.add(sql, [ag_login_id])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('ag_login_id not in database: %s' %
ag_login_id)
return info[0][0]
def ut_get_arbitrary_barcode(self, deposited=True):
""" Returns arbitrarily chosen barcode.
For unit testing only!
Parameters
----------
deposited : boolean
If true, pick a deposited barcode. Default = True
Returns
-------
str: barcode
Example: '000032951'
Raises
------
ValueError
If no barcodes can be found in the DB."""
with TRN:
sql = """SELECT barcode
FROM ag.ag_kit_barcodes
WHERE deposited=%s
LIMIT 1"""
TRN.add(sql, [deposited])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('No barcodes found.')
return info[0][0]
def _triggers(self):
"""What other question-response combinations this question can trigger
Returns
-------
tuple
(other_question_id, [triggering indices to that question])
"""
with TRN:
sql = """SELECT triggered_question, display_index
FROM {0} sst
JOIN {1} sqr
ON sst.survey_question_id=sqr.survey_question_id
AND sqr.response=sst.triggering_response
WHERE sst.survey_question_id = %s
ORDER BY triggered_question
""".format(self._supplemental_survey_table,
self._question_response_table)
TRN.add(sql, [self.id])
trigger_list = TRN.execute_fetchindex()
results = defaultdict(list)
for question, index in trigger_list:
results[question].append(index)
if results:
return results
else:
return ()
def ut_get_arbitrary_supplied_kit_id_scanned_unconsented(self):
""" Returns arbitrarily chosen supplied_kit_id and barcode which has
been scanned but is without consent.
For unit testing only!
Returns
-------
list of str: [supplied_kit_id, barcode]
example: ['fNIYa', '000001053']
Raises
------
ValueError
If no kits can be found in the DB that have been scanned and
are without consent."""
with TRN:
sql = """SELECT supplied_kit_id, barcode
FROM barcodes.barcode
JOIN ag.ag_kit_barcodes USING (barcode)
JOIN ag.ag_kit USING (ag_kit_id)
LEFT JOIN ag.source_barcodes_surveys USING (barcode)
WHERE barcodes.barcode.scan_date IS NOT NULL
AND ag.source_barcodes_surveys.survey_id IS NULL
LIMIT 1"""
TRN.add(sql, [])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('No kits found.')
return info[0]
def get_survey_ids(self, ag_login_id, participant_name):
"""Return the survey IDs associated with a participant or None
Parameters
----------
ag_login_id : str
A valid login ID, that should be a test as a valid UUID
participant_name : str
A participant name
Returns
-------
dict or None
The survey IDs keyed to the survey id,
or None if a survey ID cannot be found.
Raises
------
ValueError
Unknown ag_login_id or participant_name passed
"""
with TRN:
sql = """SELECT DISTINCT s.survey_id, als.survey_id
FROM ag.ag_login_surveys als
LEFT JOIN ag.survey_answers sa USING (survey_id)
LEFT JOIN ag.group_questions gq USING (survey_question_id)
LEFT JOIN ag.surveys s USING (survey_group)
WHERE ag_login_id=%s AND participant_name=%s"""
TRN.add(sql, [ag_login_id, participant_name])
survey_id = TRN.execute_fetchindex()
if not survey_id:
raise ValueError("No survey ID found!")
return dict(i for i in survey_id)
def ut_get_ag_login_id_from_barcode(self, barcode):
""" Returns ag_login_id for a given barcode.
For unit testing only!
Parameters
----------
barcode : str
The barcode for which the ag_login_id should be retrieved.
Returns
-------
str: ag_login_id
Example: 'd8592c74-9694-2135-e040-8a80115d6401'
Raises
------
ValueError
If the given barcode can not be found in the DB.
"""
with TRN:
sql = """SELECT ag.ag_kit.ag_login_id
FROM ag.ag_kit_barcodes
JOIN ag.ag_kit USING (ag_kit_id)
WHERE ag.ag_kit_barcodes.barcode = %s"""
TRN.add(sql, [barcode])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('Barcode "%s" not in DB' % barcode)
return info[0][0]
def fetch_survey(self, survey_id):
"""Return {element_id: answer}
The answer is in the form of ["display_index"] or ["text"] depending on
if the answer has a foreign key or not. These data are serialized for
input into a WTForm.
"""
with TRN:
sql = """SELECT survey_question_id, display_index,
survey_response_type
FROM {0}
JOIN {1} USING (response, survey_question_id)
JOIN {2} USING (survey_question_id)
LEFT JOIN {3} USING (survey_question_id)
WHERE survey_id = %s AND retired = FALSE""".format(
self._survey_answers_table,
self._survey_question_response_table,
self._survey_question_response_type_table,
self._questions_table)
TRN.add(sql, [survey_id])
answers = TRN.execute_fetchindex()
TRN.add("""SELECT survey_question_id, response
FROM {0}
LEFT JOIN {1} using (survey_question_id)
WHERE survey_id = %s AND retired = FALSE""".format(
self._survey_answers_other_table, self._questions_table),
[survey_id])
answers_other = TRN.execute_fetchindex()
survey = defaultdict(list)
for qid, idx, qtype in answers:
eid = self.questions[qid].interface_element_ids[0]
if qtype == 'SINGLE':
survey[eid] = idx
else:
survey[eid].append(idx)
for qid, data in answers_other:
eid = self.questions[qid].interface_element_ids[0]
data = data.strip(' []"')
survey[eid] = data
if len(survey) == 0:
raise ValueError("Survey answers do not exist in DB: %s" %
survey_id)
return survey
def handoutCheck(self, username, password):
with TRN:
sql = "SELECT password FROM ag.ag_handout_kits WHERE kit_id = %s"
TRN.add(sql, [username])
to_check = TRN.execute_fetchindex()
if not to_check:
return False
else:
return bcrypt.verify(password, to_check[0][0])
def handoutCheck(self, username, password):
with TRN:
password = password.encode('utf-8')
sql = "SELECT password FROM ag.ag_handout_kits WHERE kit_id = %s"
TRN.add(sql, [username])
to_check = TRN.execute_fetchindex()
if not to_check:
return False
else:
return bcrypt.checkpw(password, to_check[0][0])
def get_user_for_kit(self, supplied_kit_id):
with TRN:
sql = """SELECT ag_login_id
FROM ag.ag_kit
JOIN ag_login USING (ag_login_id)
WHERE supplied_kit_id = %s"""
TRN.add(sql, [supplied_kit_id])
results = TRN.execute_fetchindex()
if results:
return results[0][0]
else:
raise ValueError("No user ID for kit %s" % supplied_kit_id)
def get_user_for_kit(self, supplied_kit_id):
with TRN:
sql = """SELECT ag_login_id
FROM ag.ag_kit
JOIN ag_login USING (ag_login_id)
WHERE supplied_kit_id = %s"""
TRN.add(sql, [supplied_kit_id])
results = TRN.execute_fetchindex()
if results:
return results[0][0]
else:
raise ValueError("No user ID for kit %s" % supplied_kit_id)
def getParticipantSamples(self, ag_login_id, participant_name):
sql = """SELECT barcode, site_sampled, sample_date, sample_time,
notes, status
FROM ag_kit_barcodes akb
INNER JOIN barcode USING (barcode)
INNER JOIN ag_kit ak USING (ag_kit_id)
WHERE (site_sampled IS NOT NULL AND site_sampled::text <> '')
AND ag_login_id = %s AND participant_name = %s"""
with TRN:
TRN.add(sql, [ag_login_id, participant_name])
rows = TRN.execute_fetchindex()
return [dict(row) for row in rows]
def test_oldtables(self):
# check that we have 13512 distinct "sources" in ag_login_surveys,
# which are of tuple ag_login_id, participant_name
with TRN:
sql = """SELECT COUNT(*)
FROM (SELECT DISTINCT ag_login_id, participant_name
FROM ag.ag_login_surveys) AS foo"""
TRN.add(sql, [])
num_sources = TRN.execute_fetchindex()[0][0]
self.assertEqual(num_sources, 13512)
with TRN:
sql = """SELECT COUNT(*)
FROM (SELECT DISTINCT ag_login_id, participant_name
FROM ag.ag_login_surveys
WHERE vioscreen_status IS NOT NULL) AS foo"""
TRN.add(sql, [])
num_vio_sources = TRN.execute_fetchindex()[0][0]
self.assertEqual(num_vio_sources, 1692)
# check number of unique surveys
with TRN:
sql = """SELECT COUNT(DISTINCT survey_id)
FROM ag.ag_login_surveys"""
TRN.add(sql, [])
num_surveys = TRN.execute_fetchindex()[0][0]
self.assertEqual(num_surveys, 13850)
# check number of sources with more than one survey
with TRN:
sql = """SELECT COUNT(*)
FROM (SELECT ag_login_id,
participant_name,
count(survey_id) AS cs
FROM ag.ag_login_surveys
GROUP BY ag_login_id, participant_name) AS foo
WHERE cs > 1"""
TRN.add(sql, [])
num_multi_sources = TRN.execute_fetchindex()[0][0]
self.assertEqual(num_multi_sources, 314)
# check number of consents
with TRN:
sql = """SELECT COUNT(*)
FROM (SELECT DISTINCT ag_login_id, participant_name
FROM ag.ag_consent) AS foo"""
TRN.add(sql, [])
num_consent = TRN.execute_fetchindex()[0][0]
self.assertEqual(num_consent, 13514)
# why do we have two more consents than sources (both for ag_login_id
# e1934ceb-6e92-c36a-e040-8a80115d2d64)??
# check number of barcodes
with TRN:
sql = """SELECT COUNT(*)
FROM (SELECT DISTINCT barcode
FROM ag.ag_kit_barcodes) AS foo"""
TRN.add(sql, [])
num_barcodes = TRN.execute_fetchindex()[0][0]
self.assertEqual(num_barcodes, 28865)
def get_withdrawn(self):
"""Gets teh list of withdrawn participants and information
Returns
-------
list of tuple of strings
List of withdrawn participants, in the form
(ag_login_id, participant_name, participant_email, date_revoked)
"""
with TRN:
sql = "SELECT * FROM consent_revoked"
TRN.add(sql)
return TRN.execute_fetchindex()
def getEnvironmentalSamples(self, ag_login_id):
sql = """SELECT barcode, site_sampled, sample_date, sample_time,
notes, status
FROM ag_kit_barcodes
INNER JOIN barcode USING (barcode)
INNER JOIN ag_kit USING(ag_kit_id)
WHERE (environment_sampled IS NOT NULL AND
environment_sampled::text <> '')
AND ag_login_id = %s"""
with TRN:
TRN.add(sql, [ag_login_id])
rows = TRN.execute_fetchindex()
return [dict(row) for row in rows]
def test_sources(self):
with TRN:
sql = """SELECT barcode
FROM source_barcodes_surveys
JOIN ag.ag_login_surveys USING (survey_id)
WHERE ag_login_id = %s AND survey_id = %s"""
exp_barcodes = [
'000063476', '000063477', '000063478', '000063479',
'000063480', '000063481', '000063482', '000063483',
'000063484', '000063487'
]
TRN.add(
sql,
['0097e665-ea1d-483d-b248-402bcf6abf2a', '5c7106b35dda787d'])
obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
self.assertEqual(set(exp_barcodes), set(obs_barcodes))
TRN.add(
sql,
['0097e665-ea1d-483d-b248-402bcf6abf2a', 'fb6d5a66ef0dd8c7'])
obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
self.assertEqual(set(exp_barcodes), set(obs_barcodes))
exp_barcodes = ['000046215']
TRN.add(
sql,
['0073af72-39e5-4bc8-9908-eff6c4ce2d6c', 'db2e324b45e34b97'])
obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
self.assertEqual(set(exp_barcodes), set(obs_barcodes))
TRN.add(
sql,
['0073af72-39e5-4bc8-9908-eff6c4ce2d6c', 'eea585c6eb5dd4b5'])
obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
self.assertEqual(set(exp_barcodes), set(obs_barcodes))
TRN.add(
sql,
['0073af72-39e5-4bc8-9908-eff6c4ce2d6c', 'fb420871cdcf5adb'])
obs_barcodes = [x[0] for x in TRN.execute_fetchindex()]
self.assertEqual(set(exp_barcodes), set(obs_barcodes))
def get_withdrawn(self):
"""Gets teh list of withdrawn participants and information
Returns
-------
list of tuple of strings
List of withdrawn participants, in the form
(ag_login_id, participant_name, participant_email, date_revoked)
"""
with TRN:
sql = "SELECT * FROM consent_revoked"
TRN.add(sql)
return TRN.execute_fetchindex()
def barcodes_correct():
# For patch 0011 & 0012
# Needed because barcodes are added as last barcode in system + 1
# and system testing was using these larger barcodes. Now use 0-1000 range
with TRN:
sql = """SELECT barcode
FROM barcodes.barcode
WHERE barcode::integer >= 800000000"""
TRN.add(sql)
bcs = TRN.execute_fetchindex()
if bcs:
joined_bcs = ", ".join([x[0] for x in bcs])
raise EnvironmentError("Invalid barcodes found: %s" % joined_bcs)
def getEnvironmentalSamples(self, ag_login_id):
sql = """SELECT barcode, site_sampled, sample_date, sample_time,
notes, status
FROM ag_kit_barcodes
INNER JOIN barcode USING (barcode)
INNER JOIN ag_kit USING(ag_kit_id)
WHERE (environment_sampled IS NOT NULL AND
environment_sampled::text <> '')
AND ag_login_id = %s"""
with TRN:
TRN.add(sql, [ag_login_id])
rows = TRN.execute_fetchindex()
return [dict(row) for row in rows]
def delete_survey(self, survey_id):
ag_login_id = None
participant_name = None
with TRN:
sql = """SELECT ag_login_id, participant_name
FROM ag.ag_login_surveys
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
[ag_login_id, participant_name] = TRN.execute_fetchindex()[0]
num_surveys = None
with TRN:
sql = """SELECT COUNT(*) FROM ag.ag_login_surveys
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, [ag_login_id, participant_name])
num_surveys = TRN.execute_fetchindex()[0][0]
with TRN:
# delete survey answers
sql = """DELETE FROM ag.survey_answers WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
sql = """DELETE FROM ag.survey_answers_other
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
# delete source
sql = """DELETE FROM ag.ag_login_surveys WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
TRN.execute()
# delete consent if this survey is the only one for this source
if num_surveys == 1:
with TRN:
sql = """DELETE FROM ag.ag_consent
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, [ag_login_id, participant_name])
TRN.execute()
def delete_survey(self, survey_id):
ag_login_id = None
participant_name = None
with TRN:
sql = """SELECT ag_login_id, participant_name
FROM ag.ag_login_surveys
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
[ag_login_id, participant_name] = TRN.execute_fetchindex()[0]
num_surveys = None
with TRN:
sql = """SELECT COUNT(*) FROM ag.ag_login_surveys
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, [ag_login_id, participant_name])
num_surveys = TRN.execute_fetchindex()[0][0]
with TRN:
# delete survey answers
sql = """DELETE FROM ag.survey_answers WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
sql = """DELETE FROM ag.survey_answers_other
WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
# delete source
sql = """DELETE FROM ag.ag_login_surveys WHERE survey_id = %s"""
TRN.add(sql, [survey_id])
TRN.execute()
# delete consent if this survey is the only one for this source
if num_surveys == 1:
with TRN:
sql = """DELETE FROM ag.ag_consent
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, [ag_login_id, participant_name])
TRN.execute()
def getAGKitDetails(self, supplied_kit_id):
sql = """SELECT cast(ag_kit_id as varchar(100)),
supplied_kit_id, kit_password, swabs_per_kit, kit_verified,
kit_verification_code, verification_email_sent
FROM ag_kit
WHERE supplied_kit_id = %s"""
with TRN:
TRN.add(sql, [supplied_kit_id])
row = TRN.execute_fetchindex()
if not row:
raise ValueError('Supplied kit id does not exist in AG: %s' %
supplied_kit_id)
return dict(row[0])
def getAGBarcodeDetails(self, barcode):
"""Returns information about the barcode from both AG and standard info
Parameters
----------
barcode : str
Barcode to get information for
Returns
-------
dict
All barcode info, keyed to column name
Raises
------
ValueError
Barcode not found in AG information tables
"""
sql = """SELECT email,
cast(ag_kit_barcode_id as varchar(100)),
cast(ag_kit_id as varchar(100)),
barcode,
site_sampled,
environment_sampled,
sample_date,
sample_time,
participant_name,
notes,
refunded,
withdrawn,
moldy,
other,
other_text,
date_of_last_email,
overloaded,
name,
status
FROM ag.ag_kit_barcodes
LEFT JOIN barcodes.barcode USING (barcode)
LEFT JOIN ag.ag_kit USING (ag_kit_id)
LEFT JOIN ag.ag_login_surveys USING (ag_login_id)
LEFT JOIN ag.ag_login USING (ag_login_id)
WHERE barcode = %s"""
with TRN:
TRN.add(sql, [barcode])
row = TRN.execute_fetchindex()
if not row:
raise ValueError('Barcode does not exist in AG: %s' % barcode)
return dict(row[0])
def getAGKitDetails(self, supplied_kit_id):
sql = """SELECT cast(ag_kit_id as varchar(100)),
supplied_kit_id, kit_password, swabs_per_kit, kit_verified,
kit_verification_code, verification_email_sent
FROM ag_kit
WHERE supplied_kit_id = %s"""
with TRN:
TRN.add(sql, [supplied_kit_id])
row = TRN.execute_fetchindex()
if not row:
raise ValueError('Supplied kit id does not exist in AG: %s' %
supplied_kit_id)
return dict(row[0])
def get_user_info(self, supplied_kit_id):
with TRN:
sql = """SELECT CAST(ag_login_id AS VARCHAR(100)) AS ag_login_id,
email, name, address, city, state, zip, country
FROM ag_login
INNER JOIN ag_kit USING(ag_login_id)
WHERE supplied_kit_id = %s"""
TRN.add(sql, [supplied_kit_id])
row = TRN.execute_fetchindex()
if not row:
raise ValueError('Supplied kit id is not in DB: %s' %
supplied_kit_id)
user_data = dict(row[0])
user_data['ag_login_id'] = str(user_data['ag_login_id'])
return user_data
def get_user_info(self, supplied_kit_id):
with TRN:
sql = """SELECT CAST(ag_login_id AS VARCHAR(100)) AS ag_login_id,
email, name, address, city, state, zip, country
FROM ag_login
INNER JOIN ag_kit USING(ag_login_id)
WHERE supplied_kit_id = %s"""
TRN.add(sql, [supplied_kit_id])
row = TRN.execute_fetchindex()
if not row:
raise ValueError('Supplied kit id is not in DB: %s' %
supplied_kit_id)
user_data = dict(row[0])
user_data['ag_login_id'] = str(user_data['ag_login_id'])
return user_data
def get_participants_surveys(self,
ag_login_id,
participant_name,
locale='american'):
"""Returns all surveys (except external) for one participant for a
AG login.
Parameters
----------
ag_login_id : str
A valid login ID, that should be a test as a valid UUID.
participant_name : str
A participant name.
locale : str
The names for the surveys are fetched from table ag.survey_group.
For localization, there are columns for each language, which is set
by locale.
Returns
-------
List of lists or None
A list for surveys for the given participant of the given
ag_login_id. Each element is a list again [int, str, str]. Where
the first element is the survey group id, the second the survey_id
and the third is a speaking name for the survey.
None if no survey ID can be found for the combination of
participant and ag_login_id.
Raises
------
ValueError
Unknown ag_login_id or participant_name passed
"""
with TRN:
sql = """SELECT DISTINCT gq.survey_group, als.survey_id, sg.{0}
FROM ag.ag_login_surveys als
LEFT JOIN ag.survey_answers sa USING (survey_id)
LEFT JOIN ag.group_questions gq USING (survey_question_id)
LEFT JOIN ag.survey_group sg ON (survey_group=group_order)
WHERE als.ag_login_id = %s AND als.participant_name = %s
AND gq.survey_group < 0""".format(locale)
TRN.add(sql, [ag_login_id, participant_name])
surveys = TRN.execute_fetchindex()
if not surveys:
raise ValueError("No survey IDs found!")
return surveys
def getParticipantSamples(self, ag_login_id, participant_name):
sql = """SELECT DISTINCT
ag_kit_barcodes.barcode,
ag_kit_barcodes.site_sampled,
ag_kit_barcodes.sample_date,
ag_kit_barcodes.sample_time,
ag_kit_barcodes.notes,
barcodes.barcode.status
FROM ag.ag_login_surveys
JOIN ag.source_barcodes_surveys USING (survey_id)
JOIN ag.ag_kit_barcodes USING (barcode)
JOIN barcodes.barcode USING (barcode)
WHERE ag_login_id = %s
AND participant_name = %s
AND (site_sampled IS NOT NULL
AND site_sampled::text <> '')"""
with TRN:
TRN.add(sql, [ag_login_id, participant_name])
rows = TRN.execute_fetchindex()
return [dict(row) for row in rows]
def check_login_exists(self, email):
"""Checks if email for login already exists on system
Parameters
----------
email : str
Email for user to check
Returns
-------
ag_login_id or None
If exists, returns ag_login_id, else returns None
"""
with TRN:
clean_email = email.strip().lower()
sql = "SELECT ag_login_id FROM ag_login WHERE LOWER(email) = %s"
TRN.add(sql, [clean_email])
value = TRN.execute_fetchindex()
if value:
value = value[0][0]
return None if value == [] else value
def check_login_exists(self, email):
"""Checks if email for login already exists on system
Parameters
----------
email : str
Email for user to check
Returns
-------
ag_login_id or None
If exists, returns ag_login_id, else returns None
"""
with TRN:
clean_email = email.strip().lower()
sql = "SELECT ag_login_id FROM ag_login WHERE LOWER(email) = %s"
TRN.add(sql, [clean_email])
value = TRN.execute_fetchindex()
if value:
value = value[0][0]
return None if value == [] else value
def getConsent(self, survey_id):
with TRN:
TRN.add("""SELECT agc.participant_name,
agc.participant_email,
agc.parent_1_name,
agc.parent_2_name,
agc.is_juvenile,
agc.deceased_parent,
agc.ag_login_id,
agc.date_signed,
agc.assent_obtainer,
agc.age_range,
agl.survey_id
FROM ag_consent agc
JOIN ag_login_surveys agl
USING (ag_login_id, participant_name)
WHERE agl.survey_id = %s""", [survey_id])
result = TRN.execute_fetchindex()
if not result:
raise ValueError("Survey ID does not exist in DB: %s" %
survey_id)
return dict(result[0])
def ut_get_arbitrary_email(self):
""" Return arbitrarily chosen email.
For unit testing only!
Returns
-------
str: email
Example: 'a03E9u6ZAu@glA+)./Vn'
Raises
------
ValueError
If no emails be found in the DB."""
with TRN:
sql = """SELECT email
FROM ag.ag_login
LIMIT 1"""
TRN.add(sql, [])
info = TRN.execute_fetchindex()
if not info:
raise ValueError('No emails found.')
return info[0][0]
def getAGBarcodeDetails(self, barcode):
"""Returns information about the barcode from both AG and standard info
Parameters
----------
barcode : str
Barcode to get information for
Returns
-------
dict
All barcode info, keyed to column name
Raises
------
ValueError
Barcode not found in AG information tables
"""
sql = """SELECT DISTINCT email,
cast(ag_kit_barcode_id as varchar(100)),
cast(ag_kit_id as varchar(100)),
barcode, site_sampled, environment_sampled, sample_date,
sample_time, participant_name, notes, refunded, withdrawn,
moldy, other, other_text, date_of_last_email ,overloaded,
name, status
FROM ag_kit_barcodes
INNER JOIN ag_kit USING (ag_kit_id)
FULL OUTER JOIN ag_login_surveys
USING (survey_id, ag_login_id)
INNER JOIN ag_login USING (ag_login_id)
INNER JOIN barcode USING (barcode)
WHERE barcode = %s"""
with TRN:
TRN.add(sql, [barcode])
row = TRN.execute_fetchindex()
if not row:
raise ValueError('Barcode does not exist in AG: %s' % barcode)
return dict(row[0])
def get_barcode_results(self, supplied_kit_id):
"""Get the results associated with the login ID of the kit
Parameters
----------
supplied_kit_id : str
The user's supplied kit ID
Returns
-------
list of dict
A list of the dict of the barcode to participant name associated
with the login ID where results are ready.
"""
with TRN:
ag_login_id = self.get_user_for_kit(supplied_kit_id)
sql = """SELECT barcode, participant_name
FROM ag_kit_barcodes
INNER JOIN ag_kit USING (ag_kit_id)
WHERE ag_login_id = %s AND results_ready = 'Y'"""
TRN.add(sql, [ag_login_id])
return [dict(row) for row in TRN.execute_fetchindex()]
def checkPrintResults(self, kit_id):
"""Checks whether or not results are available for a given `kit_id`
Parameters
----------
kit_id : str
The supplied kit identifier to check for results availability.
Returns
-------
bool
Whether or not the results are ready for the supplied kit_id.
Notes
-----
If a `kit_id` does not exist this function will return False, as no
results would be available for a non-existent `kit_id`.
"""
with TRN:
sql = "SELECT print_results FROM ag_handout_kits WHERE kit_id = %s"
TRN.add(sql, [kit_id])
results = TRN.execute_fetchindex()
return False if not results else results[0][0]
def deleteAGParticipantSurvey(self, ag_login_id, participant_name):
# Remove user from new schema
with TRN:
sql = """SELECT survey_id, participant_email
FROM ag_login_surveys
JOIN ag_consent USING (ag_login_id, participant_name)
WHERE ag_login_id = %s AND participant_name = %s"""
TRN.add(sql, (ag_login_id, participant_name))
survey_id, participant_email = TRN.execute_fetchindex()[0]
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)
for info in self.getParticipantSamples(ag_login_id,
participant_name):
self.deleteSample(info['barcode'])
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])
sql = """INSERT INTO ag.consent_revoked
(ag_login_id,participant_name, participant_email)
VALUES (%s, %s, %s)"""
TRN.add(sql, [ag_login_id, participant_name, participant_email])
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 getMapMarkers(self):
with TRN:
sql = """SELECT country, count(country)::integer
FROM ag.ag_login GROUP BY country"""
TRN.add(sql)
return dict(TRN.execute_fetchindex())
