Exemple #1
0
    def get_nonconsented_scanned_barcodes(self, kit_id):
        """Returns list of barcodes that have been scanned but not consented

        Parameters
        ----------
        kit_id : str
            The supplied kit identifier to check for barcodes.

        Returns
        -------
        list of str
            The barcodes, if any, that have been scanned but not consented
        """
        sql = """SELECT barcode
                 FROM ag_kit_barcodes
                 INNER JOIN ag_kit USING (ag_kit_id)
                 RIGHT JOIN ag_login USING (ag_login_id)
                 LEFT JOIN barcode USING (barcode)
                 FULL JOIN ag.source_barcodes_surveys USING (barcode)
                 WHERE ag.source_barcodes_surveys.survey_id IS NULL
                 AND scan_date IS NOT NULL
                 AND ag_login_id = %s"""
        with TRN:
            user = self.get_user_for_kit(kit_id)
            TRN.add(sql, [user])
            return TRN.execute_fetchflatten()
 def _get_unverified_kits(self):
     """Gets list of unverified kit IDs, Helper function for tests"""
     sql = """SELECT supplied_kit_id
              FROM AG_KIT
              WHERE NOT kit_verified = 'y'"""
     with TRN:
         TRN.add(sql)
         return TRN.execute_fetchflatten()
Exemple #3
0
 def _get_unverified_kits(self):
     """Gets list of unverified kit IDs, Helper function for tests"""
     sql = """SELECT supplied_kit_id
              FROM AG_KIT
              WHERE NOT kit_verified = 'y'"""
     with TRN:
         TRN.add(sql)
         return TRN.execute_fetchflatten()
Exemple #4
0
 def getAvailableBarcodes(self, ag_login_id):
     sql = """SELECT barcode
              FROM ag_kit_barcodes
              INNER JOIN ag_kit USING (ag_kit_id)
              WHERE coalesce(sample_date::text, '') = ''
              AND kit_verified = 'y' AND ag_login_id = %s"""
     with TRN:
         TRN.add(sql, [ag_login_id])
         return TRN.execute_fetchflatten()
Exemple #5
0
 def getAnimalParticipants(self, ag_login_id):
     sql = """SELECT DISTINCT participant_name from ag.ag_login_surveys
              JOIN ag.survey_answers USING (survey_id)
              JOIN ag.group_questions gq USING (survey_question_id)
              JOIN ag.surveys ags USING (survey_group)
              WHERE ag_login_id = %s AND ags.survey_id = %s"""
     with TRN:
         TRN.add(sql, [ag_login_id, 2])
         return TRN.execute_fetchflatten()
 def getAnimalParticipants(self, ag_login_id):
     sql = """SELECT participant_name from ag.ag_login_surveys
              JOIN ag.survey_answers USING (survey_id)
              JOIN ag.group_questions gq USING (survey_question_id)
              JOIN ag.surveys ags USING (survey_group)
              WHERE ag_login_id = %s AND ags.survey_id = %s"""
     with TRN:
         TRN.add(sql, [ag_login_id, 2])
         return TRN.execute_fetchflatten()
 def getAvailableBarcodes(self, ag_login_id):
     sql = """SELECT barcode
              FROM ag_kit_barcodes
              INNER JOIN ag_kit USING (ag_kit_id)
              WHERE coalesce(sample_date::text, '') = ''
              AND kit_verified = 'y' AND ag_login_id = %s"""
     with TRN:
         TRN.add(sql, [ag_login_id])
         return TRN.execute_fetchflatten()
Exemple #8
0
 def getHumanParticipants(self, ag_login_id):
     # get people from new survey setup
     sql = """SELECT DISTINCT participant_name from ag.ag_login_surveys
              LEFT JOIN ag.survey_answers USING (survey_id)
              JOIN ag.group_questions gq USING (survey_question_id)
              JOIN ag.surveys ags USING (survey_group)
              WHERE ag_login_id = %s AND ags.survey_id = %s"""
     with TRN:
         TRN.add(sql, [ag_login_id, 1])
         return TRN.execute_fetchflatten()
    def test_execute_fetchflatten(self):
        with TRN:
            sql = """INSERT INTO ag.test_table (str_column, int_column)
                     VALUES (%s, %s)"""
            args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
            TRN.add(sql, args, many=True)

            sql = "SELECT str_column, int_column FROM ag.test_table"
            TRN.add(sql)

            sql = "SELECT int_column FROM ag.test_table"
            TRN.add(sql)
            obs = TRN.execute_fetchflatten()
            self.assertEqual(obs, [1, 2, 3])

            sql = "SELECT 42"
            TRN.add(sql)
            obs = TRN.execute_fetchflatten(idx=3)
            self.assertEqual(obs, ['insert1', 1, 'insert2', 2, 'insert3', 3])
 def get_countries(self):
     """
     Returns
     -------
     list of str
      All country names in database"""
     with TRN:
         sql = 'SELECT country FROM ag.iso_country_lookup ORDER BY country'
         TRN.add(sql)
         return TRN.execute_fetchflatten()
    def test_execute_fetchflatten(self):
        with TRN:
            sql = """INSERT INTO ag.test_table (str_column, int_column)
                     VALUES (%s, %s)"""
            args = [['insert1', 1], ['insert2', 2], ['insert3', 3]]
            TRN.add(sql, args, many=True)

            sql = "SELECT str_column, int_column FROM ag.test_table"
            TRN.add(sql)

            sql = "SELECT int_column FROM ag.test_table"
            TRN.add(sql)
            obs = TRN.execute_fetchflatten()
            self.assertEqual(obs, [1, 2, 3])

            sql = "SELECT 42"
            TRN.add(sql)
            obs = TRN.execute_fetchflatten(idx=3)
            self.assertEqual(obs, ['insert1', 1, 'insert2', 2, 'insert3', 3])
Exemple #12
0
 def get_countries(self):
     """
     Returns
     -------
     list of str
      All country names in database"""
     with TRN:
         sql = 'SELECT country FROM ag.iso_country_lookup ORDER BY country'
         TRN.add(sql)
         return TRN.execute_fetchflatten()
 def getHumanParticipants(self, ag_login_id):
     # get people from new survey setup
     sql = """SELECT DISTINCT participant_name from ag.ag_login_surveys
              LEFT JOIN ag.survey_answers USING (survey_id)
              JOIN ag.group_questions gq USING (survey_question_id)
              JOIN ag.surveys ags USING (survey_group)
              WHERE ag_login_id = %s AND ags.survey_id = %s"""
     with TRN:
         TRN.add(sql, [ag_login_id, 1])
         return TRN.execute_fetchflatten()
    def getBarcodesByKit(self, kitid):
        """Returns a list of barcodes in a kit

        kitid is the supplied_kit_id from the ag_kit table
        """
        sql = """SELECT barcode
                 FROM ag_kit_barcodes
                 INNER JOIN ag_kit USING (ag_kit_id)
                 WHERE supplied_kit_id = %s"""
        with TRN:
            TRN.add(sql, [kitid])
            return TRN.execute_fetchflatten()
Exemple #15
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"""
        with TRN:
            TRN.add(sql, [kitid])
            return TRN.execute_fetchflatten()
Exemple #16
0
    def __init__(self, ID, group_name):
        with TRN:
            self.id = ID
            self.group_name = group_name
            self.set_response = None

            sql = """SELECT sr.{0}
                     FROM {1} q
                     JOIN {2} qr
                         ON q.survey_question_id = qr.survey_question_id
                     JOIN {3} sr
                         ON qr.response = sr.{0}
                     WHERE q.survey_question_id = %s
                     ORDER BY qr.display_index
                  """.format(_LOCALE_COLUMN, self._survey_question_table,
                             self._question_response_table,
                             self._response_table)
            TRN.add(sql, [self.id])
            self.responses = TRN.execute_fetchflatten()

            if not self.responses:
                self.responses = None

            sql = """SELECT survey_response_type
                     FROM {0}
                     WHERE survey_question_id = %s
                  """.format(self._response_type_table)
            TRN.add(sql, [self.id])
            self.response_type = TRN.execute_fetchlast()

            sql = """SELECT {0}
                     FROM {1}
                     WHERE survey_question_id = %s
                  """.format(_LOCALE_COLUMN, self._survey_question_table)
            TRN.add(sql, [self.id])
            self.question = TRN.execute_fetchlast()

            sql = """SELECT american
                     FROM {0}
                     WHERE survey_question_id = %s
                  """.format(self._survey_question_table)
            TRN.add(sql, [self.id])
            self.american_question = TRN.execute_fetchlast()

            self.triggers = self._triggers()
            self.qid = '_'.join(self.group_name.split() + [str(self.id)])

            element_ids, elements = self._interface_elements()
            self.interface_elements = elements
            self.interface_element_ids = [
                '%s_%d' % (self.qid, i) for i in element_ids
            ]
    def getAGKitIDsByEmail(self, email):
        """Returns a list of kitids based on email

        email is email address of login
        returns a list of kit_id's associated with the email or an empty list
        """
        with TRN:
            sql = """SELECT  supplied_kit_id
                     FROM ag_kit
                     INNER JOIN ag_login USING (ag_login_id)
                     WHERE email = %s"""
            TRN.add(sql, [email.lower()])
            return TRN.execute_fetchflatten()
Exemple #18
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
        """
        with TRN:
            sql = """SELECT  supplied_kit_id
                     FROM ag_kit
                     INNER JOIN ag_login USING (ag_login_id)
                     WHERE email = %s"""
            TRN.add(sql, [email.lower()])
            return TRN.execute_fetchflatten()
Exemple #19
0
    def __init__(self, ID, group_name):
        with TRN:
            self.id = ID
            self.group_name = group_name
            self.set_response = None

            sql = """SELECT sr.{0}
                     FROM {1} q
                     JOIN {2} qr
                         ON q.survey_question_id = qr.survey_question_id
                     JOIN {3} sr
                         ON qr.response = sr.{0}
                     WHERE q.survey_question_id = %s
                     ORDER BY qr.display_index
                  """.format(_LOCALE_COLUMN, self._survey_question_table,
                             self._question_response_table,
                             self._response_table)
            TRN.add(sql, [self.id])
            self.responses = TRN.execute_fetchflatten()

            if not self.responses:
                self.responses = None

            sql = """SELECT survey_response_type
                     FROM {0}
                     WHERE survey_question_id = %s
                  """.format(self._response_type_table)
            TRN.add(sql, [self.id])
            self.response_type = TRN.execute_fetchlast()

            sql = """SELECT {0}
                     FROM {1}
                     WHERE survey_question_id = %s
                  """.format(_LOCALE_COLUMN, self._survey_question_table)
            TRN.add(sql, [self.id])
            self.question = TRN.execute_fetchlast()

            sql = """SELECT american
                     FROM {0}
                     WHERE survey_question_id = %s
                  """.format(self._survey_question_table)
            TRN.add(sql, [self.id])
            self.american_question = TRN.execute_fetchlast()

            self.triggers = self._triggers()
            self.qid = '_'.join(self.group_name.split() + [str(self.id)])

            element_ids, elements = self._interface_elements()
            self.interface_elements = elements
            self.interface_element_ids = ['%s_%d' % (self.qid, i)
                                          for i in element_ids]
    def get_nonconsented_scanned_barcodes(self, kit_id):
        """Returns list of barcodes that have been scanned but not consented

        Parameters
        ----------
        kit_id : str
            The supplied kit identifier to check for barcodes.

        Returns
        -------
        list of str
            The barcodes, if any, that have been scanned but not consented
        """
        sql = """SELECT barcode
                 FROM ag_kit_barcodes
                 INNER JOIN ag_kit USING (ag_kit_id)
                 RIGHT JOIN ag_login USING (ag_login_id)
                 LEFT JOIN barcode USING (barcode)
                 WHERE survey_id IS NULL AND scan_date IS NOT NULL
                    AND ag_login_id = %s"""
        with TRN:
            user = self.get_user_for_kit(kit_id)
            TRN.add(sql, [user])
            return TRN.execute_fetchflatten()
Exemple #21
0
    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()
Exemple #22
0
    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()
Exemple #23
0
 def get_all_handout_kits(self):
     with TRN:
         sql = 'SELECT kit_id FROM ag.ag_handout_kits'
         TRN.add(sql)
         return TRN.execute_fetchflatten()
 def get_all_handout_kits(self):
     with TRN:
         sql = 'SELECT kit_id FROM ag.ag_handout_kits'
         TRN.add(sql)
         return TRN.execute_fetchflatten()