def prep_template_get_req(prep_id, user_id):
    """Gets the json of the full prep template

    Parameters
    ----------
    prep_id : int
        PrepTemplate id to get info for
    user_id : str
        User requesting the sample template info

    Returns
    -------
    dict of objects
    {'status': status,
     'message': message,
     'template': {sample: {column: value, ...}, ...}
    """
    exists = _check_prep_template_exists(int(prep_id))
    if exists['status'] != 'success':
        return exists

    prep = PrepTemplate(int(prep_id))
    access_error = check_access(prep.study_id, user_id)
    if access_error:
        return access_error
    df = prep.to_dataframe()
    return {'status': 'success',
            'message': '',
            'template': df.to_dict(orient='index')}

def prep_template_filepaths_get_req(prep_id, user_id):
    """Returns all filepaths attached to a prep template

    Parameters
    ----------
    prep_id : int
        The current prep template id
    user_id : int
        The current user object id

    Returns
    -------
    dict of objects
        {'status': status,
         'message': message,
         'filepaths': [(filepath_id, filepath), ...]}
    """
    exists = _check_prep_template_exists(int(prep_id))
    if exists['status'] != 'success':
        return exists

    prep = PrepTemplate(int(prep_id))
    access_error = check_access(prep.study_id, user_id)
    if access_error:
        return access_error
    return {'status': 'success',
            'message': '',
            'filepaths': prep.get_filepaths()
            }

    def test_prep_template_post_req(self):
        obs = prep_template_post_req(1, '[email protected]', 'update.txt',
                                     '16S', name="  ")
        exp = {'status': 'warning',
               'message': [
                    ('Some columns required to generate a QIIME-compliant '
                     'mapping file are not present in the template. A '
                     'placeholder value (XXQIITAXX) has been used to populate '
                     'these columns. Missing columns: BarcodeSequence, '
                     'LinkerPrimerSequence'),
                    ('Some functionality will be disabled due to missing '
                     'columns:'),
                    ('\tDemultiplexing with multiple input files disabled.: '
                     'barcode, primer, run_prefix;'),
                    '\tDemultiplexing disabled.: barcode;',
                    ('\tEBI submission disabled: center_name, '
                     'experiment_design_description, instrument_model, '
                     'library_construction_protocol, platform.'),
                    ('See the Templates tutorial for a description of these '
                     'fields.')],
               'file': 'update.txt',
               'id': 'ignored in test'}

        self.assertItemsEqual(obs['message'].split('\n'), exp['message'])
        self.assertEqual(obs['status'], exp['status'])
        self.assertEqual(obs['file'], exp['file'])
        self.assertIsInstance(obs['id'], int)

        # Make sure new prep template added
        prep = PrepTemplate(obs['id'])
        self.assertEqual(prep.data_type(), '16S')
        self.assertEqual([x for x in prep.keys()], ['1.SKD6.640190'])
        self.assertEqual([x._to_dict() for x in prep.values()],
                         [{'new_col': 'new_value'}])
        self.assertEqual(prep.name, "Prep information %s" % prep.id)

    def test_dataframe_from_template(self):
        template = PrepTemplate(1)
        obs = template.to_dataframe()

        # 27 samples
        self.assertEqual(len(obs), 27)
        self.assertTrue(set(obs.index), {
            u'SKB1.640202', u'SKB2.640194', u'SKB3.640195', u'SKB4.640189',
            u'SKB5.640181', u'SKB6.640176', u'SKB7.640196', u'SKB8.640193',
            u'SKB9.640200', u'SKD1.640179', u'SKD2.640178', u'SKD3.640198',
            u'SKD4.640185', u'SKD5.640186', u'SKD6.640190', u'SKD7.640191',
            u'SKD8.640184', u'SKD9.640182', u'SKM1.640183', u'SKM2.640199',
            u'SKM3.640197', u'SKM4.640180', u'SKM5.640177', u'SKM6.640187',
            u'SKM7.640188', u'SKM8.640201', u'SKM9.640192'})

        self.assertTrue(set(obs.columns), {
            u'tot_org_carb', u'common_name', u'has_extracted_data',
            u'required_sample_info_status', u'water_content_soil',
            u'env_feature', u'assigned_from_geo', u'altitude', u'env_biome',
            u'texture', u'has_physical_specimen', u'description_duplicate',
            u'physical_location', u'latitude', u'ph', u'host_taxid',
            u'elevation', u'description', u'collection_timestamp',
            u'taxon_id', u'samp_salinity', u'host_subject_id', u'sample_type',
            u'season_environment', u'temp', u'country', u'longitude',
            u'tot_nitro', u'depth', u'anonymized_name', u'target_subfragment',
            u'sample_center', u'samp_size', u'run_date', u'experiment_center',
            u'pcr_primers', u'center_name', u'barcodesequence', u'run_center',
            u'run_prefix', u'library_construction_protocol', u'emp_status',
            u'linkerprimersequence', u'experiment_design_description',
            u'target_gene', u'center_project_name', u'illumina_technology',
            u'sequencing_meth', u'platform', u'experiment_title',
            u'study_center'})

    def delete_prep_template(self, study, user, callback):
        """Delete the selected prep template

        Parameters
        ----------
        study : Study
            The current study object
        user : User
            The current user object
        callback : function
            The callback function to call with the results once the processing
            is done
        """
        prep_template_id = int(self.get_argument('prep_template_id'))
        prep_id = prep_template_id

        try:
            PrepTemplate.delete(prep_template_id)
            msg = ("Prep template %d has been deleted" % prep_template_id)
            msg_level = "success"
            prep_id = None
        except Exception as e:
            msg = ("Couldn't remove prep template: %s" % str(e))
            msg_level = "danger"

        callback((msg, msg_level, 'prep_template_tab', prep_id, None))

def prep_template_delete_req(prep_id, user_id):
    """Delete the prep template

    Parameters
    ----------
    prep_id : int
        The prep template to update
    user_id : str
        The current user object id

    Returns
    -------
    dict of str
        {'status': status,
         'message': message}
    """
    exists = _check_prep_template_exists(int(prep_id))
    if exists['status'] != 'success':
        return exists

    prep = PrepTemplate(int(prep_id))
    access_error = check_access(prep.study_id, user_id)
    if access_error:
        return access_error
    msg = ''
    status = 'success'
    try:
        PrepTemplate.delete(prep.id)
    except Exception as e:
        msg = str(e)
        status = 'error'

    return {'status': status,
            'message': msg}

    def update_investigation_type(self, study, user, callback):
        """Updates the investigation type of a prep template

        Parameters
        ----------
        study : Study
            The current study object
        user : User
            The current user object
        callback : function
            The callback function to call with the results once the processing
            is done
        """
        msg = "investigation type successfully updated"
        msg_level = "success"

        ppd_id = int(self.get_argument('ppd_id'))

        prep_id = self.get_argument('prep_id')
        edit_investigation_type = self.get_argument('edit-investigation-type',
                                                    None)
        edit_user_defined_investigation_type = self.get_argument(
            'edit-user-defined-investigation-type', None)
        edit_new_investigation_type = self.get_argument(
            'edit-new-investigation-type', None)

        pt = PrepTemplate(prep_id)

        investigation_type = self._process_investigation_type(
            edit_investigation_type, edit_user_defined_investigation_type,
            edit_new_investigation_type)

        try:
            pt.investigation_type = investigation_type
        except QiitaDBColumnError as e:
            msg = html_error_message % (", invalid investigation type: ",
                                        investigation_type, str(e))
            msg = convert_text_html(msg)
            msg_level = "danger"

        if ppd_id == 0:
            top_tab = "prep_template_tab"
            sub_tab = prep_id
            prep_tab = None
        else:
            top_tab = "preprocessed_data_tab"
            sub_tab = ppd_id
            prep_tab = None

        callback((msg, msg_level, top_tab, sub_tab, prep_tab))

    def test_copy_artifact(self):
        # Failure test
        job = self._create_job('copy_artifact',
                               {'artifact': 1, 'prep_template': 1})

        private_task(job.id)
        self.assertEqual(job.status, 'error')
        self.assertIn("Prep template 1 already has an artifact associated",
                      job.log.msg)

        # Success test
        metadata_dict = {
            'SKB8.640193': {'center_name': 'ANL',
                            'primer': 'GTGCCAGCMGCCGCGGTAA',
                            'barcode': 'GTCCGCAAGTTA',
                            'run_prefix': "s_G1_L001_sequences",
                            'platform': 'ILLUMINA',
                            'instrument_model': 'Illumina MiSeq',
                            'library_construction_protocol': 'AAAA',
                            'experiment_design_description': 'BBBB'}}
        metadata = pd.DataFrame.from_dict(metadata_dict, orient='index',
                                          dtype=str)
        prep = PrepTemplate.create(metadata, Study(1), "16S")
        job = self._create_job('copy_artifact', {'artifact': 1,
                                                 'prep_template': prep.id})
        private_task(job.id)
        self.assertEqual(job.status, 'success')

def prep_template_summary_get_req(prep_id, user_id):
    """Get the summarized prep template data for each metadata column

    Parameters
    ----------
    prep_id : int
        PrepTemplate id to get info for
    user_id : str
        User requesting the sample template info

    Returns
    -------
    dict of objects
        Dictionary object where the keys are the metadata categories
        and the values are list of tuples. Each tuple is an observed value in
        the category and the number of times its seen.
        Format {'status': status,
                'message': message,
                'num_samples': value,
                'category': [(val1, count1), (val2, count2), ...],
                'editable': bool}
    """
    exists = _check_prep_template_exists(int(prep_id))
    if exists['status'] != 'success':
        return exists

    prep = PrepTemplate(int(prep_id))
    access_error = check_access(prep.study_id, user_id)
    if access_error:
        return access_error

    editable = Study(prep.study_id).can_edit(User(user_id))
    df = prep.to_dataframe()
    out = {'num_samples': df.shape[0],
           'summary': [],
           'status': 'success',
           'message': '',
           'editable': editable}

    cols = sorted(list(df.columns))
    for column in cols:
        counts = df[column].value_counts()
        out['summary'].append(
            (str(column), [(str(key), counts[key])
                           for key in natsorted(counts.index)]))
    return out

 def remove_add_prep_template(self, fp_rpt, study, data_type_id,
                              investigation_type):
     """add prep templates"""
     pt_id = PrepTemplate.create(load_template_to_dataframe(fp_rpt),
                                 study, _to_int(data_type_id),
                                 investigation_type=investigation_type).id
     remove(fp_rpt)
     return pt_id

 def test_post(self):
     new_prep_id = get_count('qiita.prep_template') + 1
     arguments = {'study_id': '1',
                  'data-type': '16S',
                  'prep-file': 'new_template.txt'}
     response = self.post('/prep_template/', arguments)
     self.assertEqual(response.code, 200)
     # Check that the new prep template has been created
     self.assertTrue(PrepTemplate.exists(new_prep_id))

    def test_post_valid_study(self):
        prep = StringIO(EXP_PREP_TEMPLATE.format(1))
        prep_table = load_template_to_dataframe(prep)

        response = self.post('/api/v1/study/1/preparation?data_type=16S',
                             data=prep_table.T.to_dict(),
                             headers=self.headers, asjson=True)
        self.assertEqual(response.code, 201)
        exp = json_decode(response.body)
        exp_prep = PrepTemplate(exp['id']).to_dataframe()

        prep_table.index.name = 'sample_id'

        # sort columns to be comparable
        prep_table = prep_table[sorted(prep_table.columns.tolist())]
        exp_prep = exp_prep[sorted(exp_prep.columns.tolist())]
        exp_prep.drop('qiita_prep_id', axis=1, inplace=True)

        pd.util.testing.assert_frame_equal(prep_table, exp_prep)

def update_prep_template(prep_id, fp):
    """Updates a prep template

    Parameters
    ----------
    prep_id : int
        Prep template id to be updated
    fp : str
        The file path to the template file

    Returns
    -------
    dict of {str: str}
        A dict of the form {'status': str, 'message': str}
    """
    import warnings
    from os import remove
    from qiita_db.metadata_template.util import load_template_to_dataframe
    from qiita_db.metadata_template.prep_template import PrepTemplate

    msg = ''
    status = 'success'

    prep = PrepTemplate(prep_id)

    try:
        with warnings.catch_warnings(record=True) as warns:
            df = load_template_to_dataframe(fp)
            prep.extend(df)
            prep.update(df)
            remove(fp)

            if warns:
                msg = '\n'.join(set(str(w.message) for w in warns))
                status = 'warning'
    except Exception as e:
            status = 'danger'
            msg = str(e)

    return {'status': status, 'message': msg}

    def test_prep_template_post_req(self):
        new_id = get_count('qiita.prep_template') + 1
        obs = prep_template_post_req(1, '[email protected]', 'update.txt',
                                     '16S')
        exp = {'status': 'warning',
               'message': 'Sample names were already prefixed with the study '
                          'id.\nSome functionality will be disabled due to '
                          'missing columns:\n\tDemultiplexing with multiple '
                          'input files disabled. If your raw data includes '
                          'multiple raw input files, you will not be able to '
                          'preprocess your raw data: barcode, primer, '
                          'run_prefix;\n\tDemultiplexing disabled. You will '
                          'not be able to preprocess your raw data: barcode, '
                          'primer;\n\tEBI submission disabled: center_name, '
                          'experiment_design_description, instrument_model, '
                          'library_construction_protocol, platform, primer.'
                          '\nSee the Templates tutorial for a description of '
                          'these fields.\nSome columns required to generate a '
                          'QIIME-compliant mapping file are not present in the'
                          ' template. A placeholder value (XXQIITAXX) has been'
                          ' used to populate these columns. Missing columns: '
                          'BarcodeSequence, LinkerPrimerSequence',
               'file': 'update.txt',
               'id': new_id}
        self.assertItemsEqual(obs['message'].split('\n'),
                              exp['message'].split('\n'))
        self.assertEqual(obs['status'], exp['status'])
        self.assertEqual(obs['file'], exp['file'])
        self.assertEqual(obs['id'], exp['id'])

        # Make sure new prep template added
        prep = PrepTemplate(new_id)
        self.assertEqual(prep.data_type(), '16S')
        self.assertEqual([x for x in prep.keys()], ['1.SKD6.640190'])
        self.assertEqual([x._to_dict() for x in prep.values()],
                         [{'new_col': 'new_value'}])

def _check_prep_template_exists(prep_id):
    """Make sure a prep template exists in the system

    Parameters
    ----------
    prep_id : int or str castable to int
        PrepTemplate id to check

    Returns
    -------
    dict
        {'status': status,
         'message': msg}
    """
    if not PrepTemplate.exists(int(prep_id)):
        return {'status': 'error',
                'message': 'Prep template %d does not exist' % int(prep_id)
                }
    return {'status': 'success',
            'message': ''}

    def post(self, study_id, *args, **kwargs):
        data_type = self.get_argument('data_type')
        investigation_type = self.get_argument('investigation_type', None)

        study_id = self.safe_get_study(study_id)
        if study_id is None:
            return

        data = pd.DataFrame.from_dict(json_decode(self.request.body),
                                      orient='index')

        try:
            p = PrepTemplate.create(data, study_id, data_type,
                                    investigation_type)
        except QiitaError as e:
            self.fail(str(e), 406)
            return

        self.write({'id': p.id})
        self.set_status(201)
        self.finish()

    def test_prep_template_jobs_get_req(self):
        # Create a new template:
        metadata = pd.DataFrame.from_dict(
            {'SKD6.640190': {'center_name': 'ANL',
                             'target_subfragment': 'V4',
                             'center_project_name': 'Test Project',
                             'ebi_submission_accession': None,
                             'EMP_status': 'EMP',
                             'str_column': 'Value for sample 1',
                             'primer': 'GTGCCAGCMGCCGCGGTAA',
                             'barcode': 'GTCCGCAAGTTA',
                             'run_prefix': "s_G1_L001_sequences",
                             'platform': 'ILLUMINA',
                             'instrument_model': 'Illumina MiSeq',
                             'library_construction_protocol': 'AAAA',
                             'experiment_design_description': 'BBBB'}},
            orient='index', dtype=str)
        pt = PrepTemplate.create(metadata, Study(1), '16S')

        # Check that it returns an empty dictionary when there are no jobs
        # attached to the prep template
        self.assertEqual(prep_template_jobs_get_req(pt.id, '[email protected]'), {})

        # Create a job on the template
        prep_template_patch_req(
            '[email protected]', 'remove',
            '/%s/10/columns/target_subfragment/' % pt.id)
        # To ensure a deterministic result, wait until the job is completed
        self._wait_for_parallel_job('prep_template_%s' % pt.id)
        obs = prep_template_jobs_get_req(pt.id, '[email protected]')
        self.assertEqual(len(obs), 1)
        self.assertEqual(obs.values(),
                         [{'error': '', 'status': 'success', 'step': None}])

        obs = prep_template_jobs_get_req(pt.id, '[email protected]')
        exp = {'status': 'error',
               'message': 'User does not have access to study'}
        self.assertEqual(obs, exp)

            st_df = st.to_dataframe()[columns]
            # converting to datetime
            for col in columns:
                st_df[col] = st_df[col].apply(transform_date)
            st.update(st_df)

if cols_prep:
    with TRN:
        # a few notes: just getting the preps with duplicated values; ignoring
        # column 'sample_id' and tables 'study_sample', 'prep_template',
        # 'prep_template_sample'
        sql = """SELECT table_name, array_agg(column_name::text)
                    FROM information_schema.columns
                    WHERE column_name IN %s
                        AND table_name LIKE 'prep_%%'
                        AND table_name NOT IN (
                            'prep_template', 'prep_template_sample')
                    GROUP BY table_name"""
        # note that we are looking for those columns with duplicated names in
        # the headers
        TRN.add(sql, [tuple(set(cols_prep))])
        for table, columns in viewitems(dict(TRN.execute_fetchindex())):
            # [1] the format is table_# so taking the #
            pt = PrepTemplate(int(table.split('_')[1]))
            # getting just the columns of interest
            pt_df = pt.to_dataframe()[columns]
            # converting to datetime
            for col in columns:
                pt_df[col] = pt_df[col].apply(transform_date)
            pt.update(pt_df)

    def generate_new_study_with_preprocessed_data(self):
        """Creates a new study up to the processed data for testing"""
        # ignoring warnings generated when adding templates
        simplefilter("ignore")
        info = {
            "timeseries_type_id": 1,
            "metadata_complete": True,
            "mixs_compliant": True,
            "number_samples_collected": 3,
            "number_samples_promised": 3,
            "study_alias": "Test EBI",
            "study_description": "Study for testing EBI",
            "study_abstract": "Study for testing EBI",
            "emp_person_id": StudyPerson(2),
            "principal_investigator_id": StudyPerson(3),
            "lab_person_id": StudyPerson(1)
        }
        study = Study.create(User('[email protected]'), "Test EBI study", [1], info)
        metadata_dict = {
            'Sample1': {'collection_timestamp': datetime(2015, 6, 1, 7, 0, 0),
                        'physical_specimen_location': 'location1',
                        'taxon_id': 9606,
                        'scientific_name': 'homo sapiens',
                        'Description': 'Test Sample 1'},
            'Sample2': {'collection_timestamp': datetime(2015, 6, 2, 7, 0, 0),
                        'physical_specimen_location': 'location1',
                        'taxon_id': 9606,
                        'scientific_name': 'homo sapiens',
                        'Description': 'Test Sample 2'},
            'Sample3': {'collection_timestamp': datetime(2015, 6, 3, 7, 0, 0),
                        'physical_specimen_location': 'location1',
                        'taxon_id': 9606,
                        'scientific_name': 'homo sapiens',
                        'Description': 'Test Sample 3'}
        }
        metadata = pd.DataFrame.from_dict(metadata_dict, orient='index',
                                          dtype=str)
        SampleTemplate.create(metadata, study)
        metadata_dict = {
            'Sample1': {'primer': 'GTGCCAGCMGCCGCGGTAA',
                        'barcode': 'CGTAGAGCTCTC',
                        'center_name': 'KnightLab',
                        'platform': 'ILLUMINA',
                        'instrument_model': 'Illumina MiSeq',
                        'library_construction_protocol': 'Protocol ABC',
                        'experiment_design_description': "Random value 1"},
            'Sample2': {'primer': 'GTGCCAGCMGCCGCGGTAA',
                        'barcode': 'CGTAGAGCTCTA',
                        'center_name': 'KnightLab',
                        'platform': 'ILLUMINA',
                        'instrument_model': 'Illumina MiSeq',
                        'library_construction_protocol': 'Protocol ABC',
                        'experiment_design_description': "Random value 2"},
            'Sample3': {'primer': 'GTGCCAGCMGCCGCGGTAA',
                        'barcode': 'CGTAGAGCTCTT',
                        'center_name': 'KnightLab',
                        'platform': 'ILLUMINA',
                        'instrument_model': 'Illumina MiSeq',
                        'library_construction_protocol': 'Protocol ABC',
                        'experiment_design_description': "Random value 3"},
        }
        metadata = pd.DataFrame.from_dict(metadata_dict, orient='index',
                                          dtype=str)
        pt = PrepTemplate.create(metadata, study, "16S", 'Metagenomics')
        fna_fp = join(self.temp_dir, 'seqs.fna')
        demux_fp = join(self.temp_dir, 'demux.seqs')
        with open(fna_fp, 'w') as f:
            f.write(FASTA_EXAMPLE_2.format(study.id))
        with File(demux_fp, 'w') as f:
            to_hdf5(fna_fp, f)

        ppd = Artifact.create(
            [(demux_fp, 6)], "Demultiplexed", prep_template=pt)

        return ppd

def prep_template_post_req(study_id, user_id, prep_template, data_type,
                           investigation_type=None,
                           user_defined_investigation_type=None,
                           new_investigation_type=None, name=None):
    """Adds a prep template to the system

    Parameters
    ----------
    study_id : int
        Study to attach the prep template to
    user_id : str
        User adding the prep template
    prep_template : str
        Filepath to the prep template being added
    data_type : str
        Data type of the processed samples
    investigation_type: str, optional
        Existing investigation type to attach to the prep template
    user_defined_investigation_type: str, optional
        Existing user added investigation type to attach to the prep template
    new_investigation_type: str, optional
        Investigation type to add to the system
    name : str, optional
        The name of the new prep template

    Returns
    -------
    dict of str
        {'status': status,
         'message': message,
         'file': prep_template,
         'id': id}
    """
    access_error = check_access(study_id, user_id)
    if access_error:
        return access_error
    fp_rpt = check_fp(study_id, prep_template)
    if fp_rpt['status'] != 'success':
        # Unknown filepath, so return the error message
        return fp_rpt
    fp_rpt = fp_rpt['file']

    # Add new investigation type if needed
    investigation_type = _process_investigation_type(
        investigation_type, user_defined_investigation_type,
        new_investigation_type)

    msg = ''
    status = 'success'
    prep = None
    if name:
        name = name if name.strip() else None
    try:
        with warnings.catch_warnings(record=True) as warns:
            # deleting previous uploads and inserting new one
            prep = PrepTemplate.create(
                load_template_to_dataframe(fp_rpt), Study(study_id), data_type,
                investigation_type=investigation_type, name=name)
            remove(fp_rpt)

            # join all the warning messages into one. Note that this info
            # will be ignored if an exception is raised
            if warns:
                msg = '\n'.join(set(str(w.message) for w in warns))
                status = 'warning'
    except Exception as e:
        # Some error occurred while processing the prep template
        # Show the error to the user so he can fix the template
        status = 'error'
        msg = str(e)
    info = {'status': status,
            'message': msg,
            'file': prep_template,
            'id': prep.id if prep is not None else None}

    return info