Exemplo n.º 1
0
    def getHumanParticipants(self, ag_login_id):
        conn_handler = SQLConnectionHandler()
        # get people from new survey setup
        return_res = []
        new_survey_sql = ("SELECT participant_name FROM ag_login_surveys "
                          "WHERE ag_login_id = %s")
        results = conn_handler.execute_fetchall(new_survey_sql, [ag_login_id])
        return_res.extend(row[0] for row in results)

        # get people from old surveys
        old_survey_sql = ("SELECT participant_name FROM ag_human_survey where "
                          "ag_login_id = %s")
        results = conn_handler.execute_fetchall(new_survey_sql, [ag_login_id])
        return_res.extend(row[0] for row in results)
        return return_res
 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"""
     conn_handler = SQLConnectionHandler()
     return [row[0] for row in conn_handler.execute_fetchall(sql, [ag_login_id, 2])]
Exemplo n.º 3
0
 def get_countries(self):
     """
     Returns
     -------
     list of str
      All country names in database"""
     conn_handler = SQLConnectionHandler()
     return [x[0] for x in conn_handler.execute_fetchall(
         'SELECT country FROM ag.iso_country_lookup ORDER BY country')]
Exemplo n.º 4
0
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
    conn_handler = SQLConnectionHandler()
    sql = "SELECT barcode FROM barcodes.barcode WHERE barcode::integer >= 800000000"
    bcs = conn_handler.execute_fetchall(sql)
    if bcs:
        raise EnvironmentError("Invalid barcodes found: %s" % ", ".join([x[0] for x in bcs]))
Exemplo n.º 5
0
 def search_participants(self, term):
     sql = """ select  cast(ag_login_id as varchar(100)) as ag_login_id
              from    ag_consent
              where   lower(participant_name) like %s or
              lower(participant_email) like %s"""
     conn_handler = SQLConnectionHandler()
     liketerm = '%%' + term + '%%'
     return [x[0] for x in conn_handler.execute_fetchall(
         sql, [liketerm, liketerm])]
Exemplo n.º 6
0
 def getHumanParticipants(self, ag_login_id):
     conn_handler = SQLConnectionHandler()
     # get people from new survey setup
     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"""
     results = conn_handler.execute_fetchall(sql, [ag_login_id, 1])
     return [row[0] for row in results]
    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"""

        conn_handler = SQLConnectionHandler()
        rows = conn_handler.execute_fetchall(sql, [ag_login_id, participant_name])
        barcodes = [dict(row) for row in rows]

        return barcodes
Exemplo n.º 8
0
class AGDataAccess(object):
    """Data Access implementation for all the American Gut web portal
    """
    # arbitrary, unique ID and value
    human_sites = ['Stool',
                   'Mouth',
                   'Right hand',
                   'Left hand',
                   'Forehead',
                   'Nares',
                   'Hair',
                   'Tears',
                   'Nasal mucus',
                   'Ear wax',
                   'Vaginal mucus']

    animal_sites = ['Stool',
                    'Mouth',
                    'Nares',
                    'Ears',
                    'Skin',
                    'Fur']

    general_sites = ['Animal Habitat',
                     'Biofilm',
                     'Dust',
                     'Food',
                     'Fermented Food',
                     'Indoor Surface',
                     'Outdoor Surface',
                     'Plant habitat',
                     'Soil',
                     'Sole of shoe',
                     'Water']

    def __init__(self, con=None):
        self.connection = None
        if con is None:
            self._open_connection()
        else:
            self.connection = con
        cur = self.get_cursor()
        cur.execute('set search_path TO ag, barcodes, public')

        self._sql = SQLConnectionHandler(con)

    def __del__(self):
        self.connection.close()

    def get_cursor(self):
        if self.connection.closed:
            self._open_connection()

        return self.connection.cursor()

    def _open_connection(self):
        self.connection = psycopg2.connect(
            user=AMGUT_CONFIG.user, password=AMGUT_CONFIG.password,
            database=AMGUT_CONFIG.database, host=AMGUT_CONFIG.host,
            port=AMGUT_CONFIG.port)

    #####################################
    # Helper Functions
    #####################################

    def _get_col_names_from_cursor(self, cur):
        if cur.description:
            return [x[0] for x in cur.description]
        else:
            return []

    #####################################
    # Users
    #####################################

    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.
        """
        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"""
        row = self._sql.execute_fetchone(sql, [username])
        if row:
            results = dict(row)

            if not bcrypt.verify(password, results['kit_password']):
                return False
            results['ag_login_id'] = str(results['ag_login_id'])

            return results
        else:
            return False

    def addAGLogin(self, email, name, address, city, state, zip_, country):
        clean_email = email.strip().lower()
        sql = "select ag_login_id from ag_login WHERE LOWER(email) = %s"
        cur = self.get_cursor()
        cur.execute(sql, [clean_email])
        ag_login_id = cur.fetchone()
        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")
            cur.execute(sql, [clean_email, name, address, city,
                              state, zip_, country])
            ag_login_id = cur.fetchone()
            self.connection.commit()
        return ag_login_id[0]

    def getAGBarcodeDetails(self, barcode):
        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_kit_barcodes
                  INNER JOIN ag_kit USING (ag_kit_id)
                  INNER JOIN ag_login USING (ag_login_id)
                  INNER JOIN barcode USING (barcode)
                  WHERE barcode = %s"""
        row = self._sql.execute_fetchone(sql, [barcode])

        row_dict = {}
        if row:
            row_dict = dict(row)
        return row_dict

    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"""
        row = self._sql.execute_fetchone(sql, [supplied_kit_id])

        kit_details = {}
        if row:
            kit_details = dict(row)
        return kit_details

    def registerHandoutKit(self, ag_login_id, supplied_kit_id):
        """
        Returns
        -------
        bool
            True:  success
            False: insert failed due to IntegrityError

        Notes
        -----
        Whatever is passed as kit_password will be added AS IS. This means you
        must hash the password before passing, if desired.
        """
        printresults = self.checkPrintResults(supplied_kit_id)
        if printresults is None:
            printresults = 'n'

        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_kits WHERE kit_id = %s;
            END $do$;
            """.format(ag_login_id, printresults)

        conn_handler = SQLConnectionHandler()
        try:
            conn_handler.execute(sql, [supplied_kit_id] * 3)
        except psycopg2.IntegrityError:
            logging.exception('Error on skid %s:' % ag_login_id)
            return False
        return True

    def deleteAGParticipantSurvey(self, ag_login_id, participant_name):
        # Remove user using old stype DB Schema
        self.get_cursor().callproc('ag_delete_participant',
                                   [ag_login_id, participant_name])
        self.connection.commit()

        # Remove user from new schema
        conn_handler = SQLConnectionHandler()
        sql = ("SELECT survey_id FROM ag_login_surveys WHERE ag_login_id = "
               "%s AND participant_name = %s")
        survey_id = conn_handler.execute_fetchone(
            sql, (ag_login_id, participant_name))[0]

        with conn_handler.get_postgres_cursor() as curr:
            sql = ("DELETE FROM survey_answers WHERE "
                   "survey_id = %s")
            curr.execute(sql, [survey_id])

            sql = ("DELETE FROM survey_answers_other WHERE "
                   "survey_id = %s")
            curr.execute(sql, [survey_id])

            # Reset survey attached to barcode(s)
            sql = ("UPDATE ag_kit_barcodes SET survey_id = NULL WHERE "
                   "survey_id = %s")
            curr.execute(sql, [survey_id])

            sql = "DELETE FROM promoted_survey_ids WHERE survey_id = %s"
            curr.execute(sql, [survey_id])

            # Delete last due to foreign keys
            sql = ("DELETE FROM ag_login_surveys WHERE "
                   "survey_id = %s")
            curr.execute(sql, [survey_id])

            sql = ("DELETE FROM ag_consent WHERE ag_login_id = "
                   "%s AND participant_name = %s")
            curr.execute(sql, [ag_login_id, participant_name])

    def getConsent(self, survey_id):
        conn_handler = SQLConnectionHandler()
        with conn_handler.get_postgres_cursor() as cur:
            cur.execute("""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])
            colnames = [x[0] for x in cur.description]
            result = cur.fetchone()
            if result:
                result = {k: v for k, v in zip(colnames, result)}
                if 'date_signed' in result:
                    result['date_signed'] = str(result['date_signed'])
                return result

    def logParticipantSample(self, ag_login_id, barcode, sample_site,
                             environment_sampled, sample_date, sample_time,
                             participant_name, notes):

        conn_handler = SQLConnectionHandler()
        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")

            survey_id = conn_handler.execute_fetchone(
                sql, (ag_login_id, participant_name))
            if survey_id:
                # remove the list encapulation
                survey_id = survey_id[0]
            else:
                raise RuntimeError("No survey ID for ag_login_id %s and "
                                   "participant name %s" % (ag_login_id,
                                                            participant_name))
        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"""
        conn_handler.execute(sql, [
            sample_site, environment_sampled, sample_date, sample_time,
            participant_name, notes, survey_id, barcode])
        self.connection.commit()

    def deleteSample(self, barcode, ag_login_id):
        """
        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
        """
        self.get_cursor().callproc('ag_delete_sample',
                                   [barcode, ag_login_id])
        self.connection.commit()

    def getHumanParticipants(self, ag_login_id):
        conn_handler = SQLConnectionHandler()
        # get people from new survey setup
        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"""
        results = conn_handler.execute_fetchall(sql, [ag_login_id, 1])
        return [row[0] for row in results]

    def is_old_survey(self, survey_id):
        conn_handler = SQLConnectionHandler()
        # check survey exists
        survey_answers = conn_handler.execute_fetchone(
            "SELECT exists(SELECT * FROM survey_answers WHERE survey_id = %s)",
            [survey_id])[0]
        survey_answers_other = conn_handler.execute_fetchone(
            "SELECT exists(SELECT * FROM survey_answers_other WHERE "
            "survey_id = %s)", [survey_id])[0]

        return all((survey_answers is False, survey_answers_other is False))

    def updateVioscreenStatus(self, survey_id, status):
        conn_handler = SQLConnectionHandler()
        sql = ("UPDATE ag_login_surveys SET vioscreen_status = %s WHERE "
               "survey_id = %s")
        conn_handler.execute(sql, (status, survey_id))

    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"""
        conn_handler = SQLConnectionHandler()
        return [row[0] for row in
                conn_handler.execute_fetchall(
                    sql, [ag_login_id, 2])]

    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"""

        conn_handler = SQLConnectionHandler()
        rows = conn_handler.execute_fetchall(
            sql, [ag_login_id, participant_name])
        barcodes = [dict(row) for row in rows]

        return barcodes

    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"""
        rows = self._sql.execute_fetchall(sql, [ag_login_id])
        barcodes = [dict(row) for row in rows]

        return barcodes

    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"""
        results = self._sql.execute_fetchall(sql, [ag_login_id])
        return [row[0] for row in results]

    def verifyKit(self, supplied_kit_id):
        """Set the KIT_VERIFIED for the supplied_kit_id to 'y'"""
        sql = """UPDATE AG_KIT
                 SET kit_verified='y'
                 WHERE supplied_kit_id=%s"""
        self._sql.execute(sql, [supplied_kit_id])

    def getMapMarkers(self):
        cur_completed = self.get_cursor()
        cur_ver = self.get_cursor()
        cur_ll = self.get_cursor()

        # fetch all latitide/longitude by kit id
        cur_ll.execute("""SELECT ak.supplied_kit_id, al.latitude, al.longitude
                          FROM ag_login al
                               INNER JOIN ag_kit ak
                               ON ak.ag_login_id=al.ag_login_id
                          WHERE al.latitude IS NOT NULL AND
                                al.longitude IS NOT NULL""")
        ll = {res[0]: (res[1], res[2]) for res in cur_ll.fetchall()}

        # determine all completed kits
        cur_completed.execute("""SELECT ak.supplied_kit_id
                                 FROM ag_kit ak
                                 WHERE (
                                       SELECT  count(*)
                                       FROM ag_kit_barcodes akb
                                       WHERE akb.ag_kit_id = ak.ag_kit_id
                                       ) =
                                       (
                                       SELECT  count(*)
                                       FROM ag_kit_barcodes akb
                                       WHERE akb.ag_kit_id = ak.ag_kit_id AND
                                             akb.site_sampled IS NOT NULL
                                       )""")
        completed = (res[0] for res in cur_completed.fetchall())

        # determine what kit are not verified
        cur_ver.execute("""SELECT supplied_kit_id, kit_verified
                           FROM ag_kit""")
        notverified = (res[0] for res in cur_ver.fetchall() if res[1] == 'n')

        # set green for completed kits
        res = {ll[kid]: '00FF00' for kid in completed if kid in ll}

        # set blue for unverified kits
        res.update({ll[kid]: '00B2FF' for kid in notverified if kid in ll})

        # set yellow for all others
        res.update({v: 'FFFF00' for k, v in ll.items() if v not in res})

        return [[lat, lng, c] for ((lat, lng), c) in res.items()]

    def handoutCheck(self, username, password):
        cursor = self.get_cursor()
        cursor.execute("""SELECT password
                          FROM ag.ag_handout_kits
                          WHERE kit_id=%s""", [username])
        to_check = cursor.fetchone()

        if not to_check:
            return False
        else:
            return bcrypt.verify(password, to_check[0])

    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
        """
        ag_login_id = self.get_user_for_kit(supplied_kit_id)
        cursor = self.get_cursor()
        cursor.execute("""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)""", [ag_login_id, barcode])
        return cursor.fetchone()[0]

    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
        """
        sql = """SELECT  supplied_kit_id
                 FROM ag_kit
                 INNER JOIN ag_login USING (ag_login_id)
                 WHERE email = %s"""
        return [row[0] for row in self._sql.execute_fetchall(
            sql, [email.lower()])]

    def ag_set_pass_change_code(self, email, kitid, pass_code):
        """updates ag_kit table with the supplied pass_code

        email is email address of participant
        kitid is supplied_kit_id in the ag_kit table
        pass_code is the password change verfication value
        """
        sql = """UPDATE ag_kit
                 SET pass_reset_code = %s,
                     pass_reset_time = clock_timestamp() + interval '2' hour
                 WHERE supplied_kit_id = %s AND ag_login_id in
                     (SELECT ag_login_id FROM ag_login WHERE email = %s)"""
        self._sql.execute(sql, [pass_code, kitid, email])

    def ag_update_kit_password(self, kit_id, password):
        """updates ag_kit table with password

        kit_id is supplied_kit_id in the ag_kit table
        password is the new password
        """
        password = bcrypt.encrypt(password)

        sql = """UPDATE AG_KIT
                 SET kit_password = %s, pass_reset_code = NULL
                 WHERE supplied_kit_id = %s"""
        self.connection.commit(sql, [password, kit_id])

    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)"""
        return self._sql.execute_fetchone(sql, [passcode, email, kitid])[0]

    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"""
        results = self._sql.execute_fetchall(sql, [kitid])
        return [row[0] for row in results]

    def checkPrintResults(self, kit_id):
        sql = "SELECT print_results FROM ag_handout_kits WHERE kit_id = %s"
        results = self._sql.execute_fetchone(sql, [kit_id])
        if results is None:
            return None
        else:
            return results[0].strip()

    def get_user_for_kit(self, supplied_kit_id):
        sql = ("select AK.ag_login_id from ag_kit AK "
               "join ag_login AL on AK.ag_login_id = AL.ag_login_id "
               "where AK.supplied_kit_id = %s")
        cursor = self.get_cursor()
        cursor.execute(sql, [supplied_kit_id])
        results = cursor.fetchone()
        if results:
            return results[0]
        else:
            raise RuntimeError("No user ID for kit %s" % supplied_kit_id)

    def get_menu_items(self, supplied_kit_id):
        """Returns information required to populate the menu of the website"""
        ag_login_id = self.get_user_for_kit(supplied_kit_id)
        info = self.getAGKitDetails(supplied_kit_id)

        kit_verified = False
        if info['kit_verified'] == 'y':
            kit_verified = True

        human_samples = {hs: self.getParticipantSamples(ag_login_id, hs)
                         for hs in self.getHumanParticipants(ag_login_id)}
        animal_samples = {ans: self.getParticipantSamples(ag_login_id, ans)
                          for ans in self.getAnimalParticipants(ag_login_id)}
        environmental_samples = self.getEnvironmentalSamples(ag_login_id)

        return (human_samples, animal_samples, environmental_samples,
                kit_verified)

    def check_if_consent_exists(self, ag_login_id, participant_name):
        """Return True if a consent already exists"""
        sql = """select exists(
                    select 1
                    from ag_consent
                    where ag_login_id=%s and
                        participant_name=%s)"""
        cursor = self.get_cursor()
        cursor.execute(sql, (ag_login_id, participant_name))
        return cursor.fetchone()[0]

    def get_user_info(self, supplied_kit_id):
        sql = """SELECT  cast(agl.ag_login_id as varchar(100)) as ag_login_id,
                        agl.email, agl.name, agl.address, agl.city,
                        agl.state, agl.zip, agl.country
                 from    ag_login agl
                        inner join ag_kit agk
                        on agl.ag_login_id = agk.ag_login_id
                 where   agk.supplied_kit_id = %s"""
        cursor = self.get_cursor()
        cursor.execute(sql, [supplied_kit_id])
        row = cursor.fetchone()
        col_names = self._get_col_names_from_cursor(cursor)

        user_data = {}
        if row:
            user_data = dict(zip(col_names, row))
            user_data['ag_login_id'] = str(user_data['ag_login_id'])

        return user_data

    def get_person_info(self, survey_id):
        # get question responses
        info = {'birth_month': 'Unspecified', 'birth_year': 'Unspecified', 'gender': 'Unspecified'}
        sql = ("SELECT q.american, sa.response FROM ag.survey_answers_other "
               " sa JOIN ag.ag_login_surveys ls ON sa.survey_id = ls.survey_id "
               "JOIN ag.survey_question q ON q.survey_question_id = sa.survey_question_id "
               "WHERE sa.survey_id = %s AND q.american IN ('Birth month:','Birth year:','Gender:')")
        cursor = self.get_cursor()
        cursor.execute(sql, [survey_id])
        rows = cursor.fetchall()

        for res in rows:
            value = json.loads(res[1])[0]
            if res[0] == 'Birth month:':
                info['birth_month'] = value
            elif res[0] == 'Birth year:':
                info['birth_year'] = value
            elif res[0] == 'Gender:':
                info['gender'] = value

        # get name from consent form
        sql = ("SELECT c.participant_name FROM ag.ag_consent c JOIN "
               "ag.ag_login_surveys ls ON c.ag_login_id = ls.ag_login_id WHERE "
               "ls.survey_id = %s")
        cursor.execute(sql, [survey_id])
        info["name"] = cursor.fetchone()[0]

        return info

    def get_barcode_results(self, supplied_kit_id):
        """Get the results associated with the login ID of the kit"""
        ag_login_id = self.get_user_for_kit(supplied_kit_id)
        cursor = self.get_cursor()

        sql = """SELECT akb.barcode, akb.participant_name
                 FROM ag_kit_barcodes akb
                 INNER JOIN ag_kit agk USING(ag_kit_id)
                 WHERE agk.ag_login_id = %s AND akb.results_ready = 'Y'"""

        cursor.execute(sql, [ag_login_id])
        results = cursor.fetchall()
        col_names = self._get_col_names_from_cursor(cursor)
        return [dict(zip(col_names, row)) for row in results]

    def get_login_info(self, ag_login_id):
        sql = """select  ag_login_id, email, name, address, city, state, zip,
                         country
                 from    ag_login
                 where   ag_login_id = %s"""
        cursor = self.get_cursor()
        cursor.execute(sql, [ag_login_id])
        col_names = [x[0] for x in cursor.description]
        results = [dict(zip(col_names, row)) for row in cursor.fetchall()]
        cursor.close()
        return results

#################################################
### GENERAL DATA ACCESS  #######################
################################################
# not sure where these should end up

    def get_survey_id(self, ag_login_id, participant_name):
        """Return the survey ID associated with a participant or None"""
        sql = """select survey_id
                 from ag_login_surveys
                 where ag_login_id=%s and participant_name=%s"""
        cursor = self.get_cursor()
        cursor.execute(sql, [ag_login_id, participant_name])
        id_ = cursor.fetchone()

        return id_[0] if id_ else None

    def get_countries(self):
        """
        Returns
        -------
        list of str
         All country names in database"""
        conn_handler = SQLConnectionHandler()
        return [x[0] for x in conn_handler.execute_fetchall(
            'SELECT country FROM ag.iso_country_lookup ORDER BY country')]