path: root/gn2/wqflask/api/router.py

# GN2 API

import os
import io
import csv
import json
import datetime
import requests

from zipfile import ZipFile, ZIP_DEFLATED


import flask
from flask import current_app
from gn2.wqflask.database import database_connection
from flask import request
from flask import make_response
from flask import send_file

from gn2.wqflask import app

from gn2.wqflask.api import correlation, mapping, gen_menu

from gn2.utility.tools import flat_files, get_setting

from gn2.wqflask.database import database_connection


version = "pre1"


@app.route("/api/v_{}/".format(version))
def hello_world():
    return flask.jsonify({"hello": "world"})


@app.route("/api/v_{}/species".format(version))
def get_species_list():
    species_list = []
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        cursor.execute(
            "SELECT SpeciesId, Name, FullName, TaxonomyId FROM Species"
        )
        for species in cursor.fetchall():
            species_dict = {
            "Id": species[0],
            "Name": species[1],
            "FullName": species[2],
            "TaxonomyId": species[3]
            }
            species_list.append(species_dict)
    return flask.jsonify(species_list)


@app.route("/api/v_{}/species/<path:species_name>".format(version))
@app.route("/api/v_{}/species/<path:species_name>.<path:file_format>".format(version))
def get_species_info(species_name, file_format="json"):
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        cursor.execute(
            "SELECT SpeciesId, Name, FullName, TaxonomyId "
            "FROM Species WHERE (Name=%s OR FullName=%s "
            "OR SpeciesName=%s)", ((species_name,)*3))
        _species = cursor.fetchone()
        species_dict = {
            "Id": _species[0],
            "Name": _species[1],
            "FullName": _species[2],
            "TaxonomyId": _species[3]
        }

        return flask.jsonify(species_dict)


@app.route("/api/v_{}/groups".format(version))
@app.route("/api/v_{}/groups/<path:species_name>".format(version))
def get_groups_list(species_name=None):
    _groups = ()
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        if species_name:
            cursor.execute(
                "SELECT InbredSet.InbredSetId, "
                "InbredSet.SpeciesId, InbredSet.InbredSetName, "
                "InbredSet.Name, InbredSet.FullName, "
                "InbredSet.public, IFNULL(InbredSet.MappingMethodId, "
                "'None'), IFNULL(InbredSet.GeneticType, 'None') "
                "FROM InbredSet, Species WHERE "
                "InbredSet.SpeciesId = Species.Id AND "
                "(Species.Name = %s OR Species.FullName=%s "
                "OR Species.SpeciesName=%s)", ((species_name,) * 3)
            )
        else:
            cursor.execute(
                "SELECT InbredSet.InbredSetId, "
                "InbredSet.SpeciesId, InbredSet.InbredSetName, "
                "InbredSet.Name, InbredSet.FullName, "
                "InbredSet.public, IFNULL(InbredSet.MappingMethodId, "
                "'None'), IFNULL(InbredSet.GeneticType, 'None') "
                "FROM InbredSet"
            )
        _groups = cursor.fetchall()

    if _groups:
        groups_list = []
        for group in _groups:
            group_dict = {
                "Id": group[0],
                "SpeciesId": group[1],
                "DisplayName": group[2],
                "Name": group[3],
                "FullName": group[4],
                "public": group[5],
                "MappingMethodId": group[6],
                "GeneticType": group[7]
            }
            groups_list.append(group_dict)
        return flask.jsonify(groups_list)
    return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")


@app.route("/api/v_{}/group/<path:group_name>".format(version))
@app.route("/api/v_{}/group/<path:group_name>.<path:file_format>".format(version))
@app.route("/api/v_{}/group/<path:species_name>/<path:group_name>".format(version))
@app.route("/api/v_{}/group/<path:species_name>/<path:group_name>.<path:file_format>".format(version))
def get_group_info(group_name, species_name=None, file_format="json"):
    group = tuple()
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        if species_name:
            cursor.execute(
                "SELECT InbredSet.InbredSetId, InbredSet.SpeciesId, "
                "InbredSet.InbredSetName, InbredSet.Name, "
                "InbredSet.FullName, InbredSet.public, "
                "IFNULL(InbredSet.MappingMethodId, 'None'), "
                "IFNULL(InbredSet.GeneticType, 'None') "
                "FROM InbredSet, Species WHERE "
                "InbredSet.SpeciesId = Species.Id "
                "AND (InbredSet.InbredSetName = %s OR "
                "InbredSet.Name = %s OR InbredSet.FullName = %s) "
                "AND (Species.Name = %s OR "
                "Species.FullName = %s OR Species.SpeciesName = %s)",
                (*(group_name,)*3, *(species_name,)*3)
            )
        else:
            cursor.execute(
                "SELECT InbredSet.InbredSetId, InbredSet.SpeciesId, "
                "InbredSet.InbredSetName, InbredSet.Name, "
                "InbredSet.FullName, InbredSet.public, "
                "IFNULL(InbredSet.MappingMethodId, 'None'), "
                "IFNULL(InbredSet.GeneticType, 'None') "
                "FROM InbredSet WHERE "
                "(InbredSet.InbredSetName = %s OR "
                "InbredSet.Name = %s OR "
                "InbredSet.FullName = %s)",
                ((group_name,)*3)
            )
        group = cursor.fetchone()

    if group:
        group_dict = {
            "Id": group[0],
            "SpeciesId": group[1],
            "DisplayName": group[2],
            "Name": group[3],
            "FullName": group[4],
            "public": group[5],
            "MappingMethodId": group[6],
            "GeneticType": group[7]
        }

        return flask.jsonify(group_dict)
    else:
        return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")


@app.route("/api/v_{}/datasets/<path:group_name>".format(version))
@app.route("/api/v_{}/datasets/<path:species_name>/<path:group_name>".format(version))
def get_datasets_for_group(group_name, species_name=None):
    _datasets = ()
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        if species_name:
            cursor.execute(
                "SELECT ProbeSetFreeze.Id, ProbeSetFreeze.ProbeFreezeId, "
                "ProbeSetFreeze.AvgID, ProbeSetFreeze.Name, "
                "ProbeSetFreeze.Name2, "
                "ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, "
                "ProbeSetFreeze.CreateTime, ProbeSetFreeze.public, "
                "ProbeSetFreeze.confidentiality, ProbeSetFreeze.DataScale "
                "FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Species "
                "WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
                "AND ProbeFreeze.InbredSetId = InbredSet.Id "
                "AND (InbredSet.Name = %s OR "
                "InbredSet.InbredSetName = %s OR "
                "InbredSet.FullName = %s) AND "
                "InbredSet.SpeciesId = Species.Id AND "
                "(Species.SpeciesName = %s OR "
                "Species.MenuName = %s OR Species.FullName = %s);",
                (*(group_name,)*3, *(species_name)*3)
            )
        else:
            cursor.execute(
                "SELECT ProbeSetFreeze.Id, ProbeSetFreeze.ProbeFreezeId, "
                "ProbeSetFreeze.AvgID, ProbeSetFreeze.Name, "
                "ProbeSetFreeze.Name2, ProbeSetFreeze.FullName, "
                "ProbeSetFreeze.ShortName, ProbeSetFreeze.CreateTime, "
                "ProbeSetFreeze.public, ProbeSetFreeze.confidentiality, "
                "ProbeSetFreeze.DataScale FROM ProbeSetFreeze, "
                "ProbeFreeze, InbredSet WHERE "
                "ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id "
                "AND ProbeFreeze.InbredSetId = InbredSet.Id "
                "AND (InbredSet.Name = %s OR "
                "InbredSet.InbredSetName = %s OR "
                "InbredSet.FullName = %s)",
                ((group_name,) * 3)
            )
        _datasets = cursor.fetchall()

    if _datasets:
        datasets_list = []
        for dataset in _datasets:
            dataset_dict = {
                "Id": dataset[0],
                "ProbeFreezeId": dataset[1],
                "AvgID": dataset[2],
                "Short_Abbreviation": dataset[3],
                "Long_Abbreviation": dataset[4],
                "FullName": dataset[5],
                "ShortName": dataset[6],
                "CreateTime": dataset[7],
                "public": dataset[8],
                "confidentiality": dataset[9],
                "DataScale": dataset[10]
            }
            datasets_list.append(dataset_dict)

        return flask.jsonify(datasets_list)
    else:
        return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")


@app.route("/api/v_{}/dataset/<path:dataset_name>".format(version))
@app.route("/api/v_{}/dataset/<path:dataset_name>.<path:file_format>".format(version))
@app.route("/api/v_{}/dataset/<path:group_name>/<path:dataset_name>".format(version))
@app.route("/api/v_{}/dataset/<path:group_name>/<path:dataset_name>.<path:file_format>".format(version))
def get_dataset_info(dataset_name, group_name=None, file_format="json"):
    # ZS: First get ProbeSet (mRNA expression) datasets and then get Phenotype datasets

    # ZS: I figure I might as well return a list if there are multiple
    # matches, though I don"t know if this will actually happen in
    # practice
    datasets_list, dataset_dict = [], {}
    probeset_query = """
                SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, ProbeSetFreeze.FullName,
                       ProbeSetFreeze.ShortName, ProbeSetFreeze.DataScale, ProbeFreeze.TissueId,
                       Tissue.Name, ProbeSetFreeze.public, ProbeSetFreeze.confidentiality
                FROM ProbeSetFreeze, ProbeFreeze, Tissue
            """

    where_statement = """
                         WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND
                               ProbeFreeze.TissueId = Tissue.Id AND
                               ProbeSetFreeze.public > 0 AND
                               ProbeSetFreeze.confidentiality < 1 AND
                      """
    if dataset_name.isdigit():
        where_statement += """
                              ProbeSetFreeze.Id = "{}"
                           """.format(dataset_name)
    else:
        where_statement += """
                              (ProbeSetFreeze.Name = "{0}" OR ProbeSetFreeze.Name2 = "{0}" OR
                              ProbeSetFreeze.FullName = "{0}" OR ProbeSetFreeze.ShortName = "{0}")
                           """.format(dataset_name)
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        cursor.execute(f"{probeset_query}{where_statement}")

        if dataset := cursor.fetchone():
            dataset_dict = {
                "dataset_type": "mRNA expression",
                "id": dataset[0],
                "name": dataset[1],
                "full_name": dataset[2],
                "short_name": dataset[3],
                "data_scale": dataset[4],
                "tissue_id": dataset[5],
                "tissue": dataset[6],
                "public": dataset[7],
                "confidential": dataset[8]
            }

            datasets_list.append(dataset_dict)

        if group_name:
            cursor.execute(
                "SELECT PublishXRef.Id, "
                "Phenotype.Post_publication_abbreviation, "
                "Phenotype.Post_publication_description, "
                "Phenotype.Pre_publication_abbreviation, "
                "Phenotype.Pre_publication_description, "
                "Publication.PubMed_ID, Publication.Title, "
                "Publication.Year FROM PublishXRef, Phenotype, "
                "Publication, InbredSet, PublishFreeze WHERE "
                "PublishXRef.InbredSetId = InbredSet.Id "
                "AND PublishXRef.PhenotypeId = Phenotype.Id "
                "AND PublishXRef.PublicationId = Publication.Id "
                "AND PublishFreeze.InbredSetId = InbredSet.Id "
                "AND PublishFreeze.public > 0 AND "
                "PublishFreeze.confidentiality < 1 "
                "AND InbredSet.Name = %s AND PublishXRef.Id = %s",
                (group_name, dataset_name,)
            )

            if dataset := cursor.fetchone():
                if dataset[5]:
                    dataset_dict = {
                        "dataset_type": "phenotype",
                        "id": dataset[0],
                        "name": dataset[1],
                        "description": dataset[2],
                        "pubmed_id": dataset[5],
                        "title": dataset[6],
                        "year": dataset[7]
                    }
                elif dataset[4]:
                    dataset_dict = {
                        "dataset_type": "phenotype",
                        "id": dataset[0],
                        "name": dataset[3],
                        "description": dataset[4]
                    }
                else:
                    dataset_dict = {
                        "dataset_type": "phenotype",
                        "id": dataset[0]
                    }

                datasets_list.append(dataset_dict)

    if len(datasets_list) > 1:
        return flask.jsonify(datasets_list)
    elif len(datasets_list) == 1:
        return flask.jsonify(dataset_dict)
    else:
        return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")


@app.route("/api/v_{}/traits/<path:dataset_name>".format(version), methods=("GET",))
@app.route("/api/v_{}/traits/<path:dataset_name>.<path:file_format>".format(version), methods=("GET",))
def fetch_traits(dataset_name, file_format="json"):
    trait_ids, trait_names, data_type, dataset_id = get_dataset_trait_ids(
        dataset_name, request.args)
    if ("ids_only" in request.args) and (len(trait_ids) > 0):
        if file_format == "json":
            filename = dataset_name + "_trait_ids.json"
            return flask.jsonify(trait_ids)
        else:
            filename = dataset_name + "_trait_ids.csv"

            si = io.StringIO()
            csv_writer = csv.writer(si)
            csv_writer.writerows([[trait_id] for trait_id in trait_ids])
            output = make_response(si.getvalue())
            output.headers["Content-Disposition"] = "attachment; filename=" + filename
            output.headers["Content-type"] = "text/csv"
            return output
    elif ("names_only" in request.args) and (len(trait_ids) > 0):
        if file_format == "json":
            filename = dataset_name + "_trait_names.json"
            return flask.jsonify(trait_names)
        else:
            filename = dataset_name + "_trait_names.csv"

            si = io.StringIO()
            csv_writer = csv.writer(si)
            csv_writer.writerows([[trait_name] for trait_name in trait_names])
            output = make_response(si.getvalue())
            output.headers["Content-Disposition"] = "attachment; filename=" + filename
            output.headers["Content-type"] = "text/csv"
            return output
    else:
        if len(trait_ids) > 0:
            if data_type == "ProbeSet":
                query = """
                            SELECT DISTINCT
                                ProbeSet.`Id`, ProbeSet.`Name`, ProbeSet.`Symbol`, ProbeSet.`description`,
                                ProbeSet.`Chr`, ProbeSet.`Mb`, ProbeSet.`alias`, ProbeSetXRef.`mean`,
                                ProbeSetXRef.`se`, ProbeSetXRef.`Locus`, ProbeSetXRef.`LRS`,
                                ProbeSetXRef.`pValue`, ProbeSetXRef.`additive`, ProbeSetXRef.`h2`,
                                Geno.`Chr`, Geno.`Mb`
                            FROM 
                                Species
                                INNER JOIN InbredSet ON InbredSet.`SpeciesId`= Species.`Id`
                                INNER JOIN ProbeFreeze ON ProbeFreeze.`InbredSetId` = InbredSet.`Id`
                                INNER JOIN Tissue ON ProbeFreeze.`TissueId` = Tissue.`Id`
                                INNER JOIN ProbeSetFreeze ON ProbeSetFreeze.`ProbeFreezeId` = ProbeFreeze.`Id`
                                INNER JOIN ProbeSetXRef ON ProbeSetXRef.`ProbeSetFreezeId` = ProbeSetFreeze.`Id`
                                INNER JOIN ProbeSet ON ProbeSet.`Id` = ProbeSetXRef.`ProbeSetId`
                                LEFT JOIN Geno ON ProbeSetXRef.`Locus` = Geno.`Name` AND Geno.`SpeciesId` = Species.`Id`
                            WHERE
                                ProbeSetXRef.ProbeSetFreezeId = "{0}"
                            ORDER BY
                                ProbeSet.Id"""

                field_list = ["Id", "Name", "Symbol", "Description", "Chr", "Mb", "Aliases", "Mean",
                              "SE", "Locus", "LRS", "P-Value", "Additive", "h2", "Peak Chr", "Peak Mb"]
            elif data_type == "Geno":
                query = """
                            SELECT
                                Geno.Id, Geno.Name, Geno.Marker_Name, Geno.Chr, Geno.Mb, Geno.Sequence, Geno.Source
                            FROM
                                Geno, GenoXRef, GenoFreeze
                            WHERE
                                GenoXRef.GenoFreezeId = "{0}" AND
                                GenoXRef.GenoId = Geno.Id AND
                                GenoXRef.GenoFreezeId = GenoFreeze.Id AND
                                GenoFreeze.public > 0 AND
                                GenoFreeze.confidentiality < 1
                            ORDER BY
                                Geno.Id
                        """

                field_list = ["Id", "Name", "Marker_Name",
                              "Chr", "Mb", "Sequence", "Source"]
            else:
                query = """SELECT PublishXRef.Id,
                        Phenotype.`Original_description`,
                        Publication.`Authors`,
                        Publication.`Year`,
                        Publication.`PubMed_ID`,
                        PublishXRef.`mean`,
                        PublishXRef.`LRS`,
                        PublishXRef.`additive`,
                        PublishXRef.`Locus`,
                        Geno.`Chr`, Geno.`Mb`
                        FROM Species
                            INNER JOIN InbredSet ON InbredSet.`SpeciesId` = Species.`Id`
                            INNER JOIN PublishXRef ON PublishXRef.`InbredSetId` = InbredSet.`Id`
                            INNER JOIN PublishFreeze ON PublishFreeze.`InbredSetId` = InbredSet.`Id`
                            INNER JOIN Publication ON Publication.`Id` = PublishXRef.`PublicationId`
                            INNER JOIN Phenotype ON Phenotype.`Id` = PublishXRef.`PhenotypeId`
                            LEFT JOIN Geno ON PublishXRef.Locus = Geno.Name AND Geno.SpeciesId = Species.Id
                        WHERE
                            PublishXRef.InbredSetId = {0} AND
                            PublishFreeze.InbredSetId = PublishXRef.InbredSetId AND
                            PublishFreeze.public > 0 AND
                            PublishFreeze.confidentiality < 1
                            ORDER BY
                                PublishXRef.Id"""

                field_list = ["Id", "Description", "Authors", "Year", "PubMedID", "Mean",
                              "LRS", "Additive", "Locus", "Chr", "Mb"]

            if 'limit_to' in request.args:
                limit_number = request.args['limit_to']
                query += "LIMIT " + str(limit_number)
            with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
                if file_format == "json":
                    filename = dataset_name + "_traits.json"
                    cursor.execute(query.format(dataset_id))
                    result_list = []
                    for result in cursor.fetchall():
                        trait_dict = {}
                        for i, field in enumerate(field_list):
                            if result[i]:
                                trait_dict[field] = result[i]
                        result_list.append(trait_dict)
                    return flask.jsonify(result_list)
                elif file_format == "csv":
                    filename = dataset_name + "_traits.csv"

                    results_list = []
                    header_list = []
                    header_list += field_list
                    results_list.append(header_list)
                    cursor.execute(query.format(dataset_id))
                    for result in cursor.fetchall():
                        results_list.append(result)

                    si = io.StringIO()
                    csv_writer = csv.writer(si)
                    csv_writer.writerows(results_list)
                    output = make_response(si.getvalue())
                    output.headers["Content-Disposition"] = "attachment; filename=" + filename
                    output.headers["Content-type"] = "text/csv"
                    return output
                else:
                    return return_error(
                        code=400,
                        source=request.url_rule.rule,
                        title="Invalid Output Format",
                        details="Current formats available are JSON and CSV, with CSV as default"
                    )
        else:
            return return_error(
                code=204,
                source=request.url_rule.rule,
                title="No Results",
                details="")


@app.route("/api/v_{}/sample_data/<path:dataset_name>".format(version))
@app.route("/api/v_{}/sample_data/<path:dataset_name>.<path:file_format>".format(version))
def all_sample_data(dataset_name, file_format="csv"):
    trait_ids, trait_names, data_type, dataset_id = get_dataset_trait_ids(
        dataset_name, request.args)

    if len(trait_ids) > 0:
        sample_list = get_samplelist(dataset_name)

        if data_type == "ProbeSet":
            query = """
                        SELECT
                            Strain.Name, Strain.Name2, ProbeSetData.value, ProbeSetData.Id, ProbeSetSE.error
                        FROM
                            (ProbeSetData, Strain, ProbeSetXRef, ProbeSetFreeze)
                        LEFT JOIN ProbeSetSE ON
                            (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId)
                        WHERE
                            ProbeSetXRef.ProbeSetFreezeId = "{0}" AND
                            ProbeSetXRef.ProbeSetId = "{1}" AND
                            ProbeSetXRef.DataId = ProbeSetData.Id AND
                            ProbeSetData.StrainId = Strain.Id AND
                            ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND
                            ProbeSetFreeze.public > 0 AND
                            ProbeSetFreeze.confidentiality < 1
                        ORDER BY
                            Strain.Name
                    """
        elif data_type == "Geno":
            query = """
                        SELECT
                            Strain.Name, Strain.Name2, GenoData.value, GenoData.Id, GenoSE.error
                        FROM
                            (GenoData, Strain, GenoXRef, GenoFreeze)
                        LEFT JOIN GenoSE ON
                            (GenoSE.DataId = GenoData.Id AND GenoSE.StrainId = GenoData.StrainId)
                        WHERE
                            GenoXRef.GenoFreezeId = "{0}" AND
                            GenoXRef.GenoId = "{1}" AND
                            GenoXRef.DataId = GenoData.Id AND
                            GenoData.StrainId = Strain.Id AND
                            GenoXRef.GenoFreezeId = GenoFreeze.Id AND
                            GenoFreeze.public > 0 AND
                            GenoFreeze.confidentiality < 1
                        ORDER BY
                            Strain.Name
                    """
        else:
            query = """
                        SELECT
                            Strain.Name, Strain.Name2, PublishData.value, PublishData.Id, PublishSE.error, NStrain.count
                        FROM
                            (PublishData, Strain, PublishXRef, PublishFreeze)
                        LEFT JOIN PublishSE ON
                            (PublishSE.DataId = PublishData.Id AND PublishSE.StrainId = PublishData.StrainId)
                        LEFT JOIN NStrain ON
                            (NStrain.DataId = PublishData.Id AND
                            NStrain.StrainId = PublishData.StrainId)
                        WHERE
                            PublishXRef.InbredSetId = "{0}" AND
                            PublishXRef.PhenotypeId = "{1}" AND
                            PublishData.Id = PublishXRef.DataId AND
                            PublishData.StrainId = Strain.Id AND
                            PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND
                            PublishFreeze.public > 0 AND
                            PublishFreeze.confidentiality < 1
                        ORDER BY
                            Strain.Name
                    """

        if file_format == "csv":
            filename = dataset_name + "_sample_data.csv"

            results_list = []
            header_list = []
            header_list.append("id")
            header_list += sample_list
            results_list.append(header_list)
            with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
                for i, trait_id in enumerate(trait_ids):
                    line_list = []
                    line_list.append(str(trait_names[i]))
                    cursor.execute(query.format(dataset_id, trait_id))
                    results = cursor.fetchall()
                    results_dict = {}
                    for item in results:
                        results_dict[item[0]] = item[2]
                    for sample in sample_list:
                        if sample in results_dict:
                            line_list.append(results_dict[sample])
                        else:
                            line_list.append("x")
                    results_list.append(line_list)

            results_list = list(map(list, zip(*results_list)))

            si = io.StringIO()
            csv_writer = csv.writer(si)
            csv_writer.writerows(results_list)
            output = make_response(si.getvalue())
            output.headers["Content-Disposition"] = "attachment; filename=" + filename
            output.headers["Content-type"] = "text/csv"
            return output
        else:
            return return_error(code=415, source=request.url_rule.rule, title="Unsupported file format", details="")
    else:
        return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")


@app.route("/api/v_{}/sample_data/<path:dataset_name>/<path:trait_name>".format(version))
@app.route("/api/v_{}/sample_data/<path:dataset_name>/<path:trait_name>.<path:file_format>".format(version))
def trait_sample_data(dataset_name, trait_name, file_format="json"):
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        cursor.execute(
            "SELECT Strain.Name, Strain.Name2, "
            "ProbeSetData.value, ProbeSetData.Id, "
            "ProbeSetSE.error FROM (ProbeSetData, "
            "ProbeSetFreeze, Strain, ProbeSet, "
            "ProbeSetXRef) LEFT JOIN ProbeSetSE ON "
            "(ProbeSetSE.DataId = ProbeSetData.Id AND "
            "ProbeSetSE.StrainId = ProbeSetData.StrainId) "
            "WHERE ProbeSet.Name = %s AND "
            "ProbeSetXRef.ProbeSetId = ProbeSet.Id "
            "AND ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id "
            "AND ProbeSetFreeze.Name = %s AND "
            "ProbeSetXRef.DataId = ProbeSetData.Id "
            "AND ProbeSetData.StrainId = Strain.Id "
            "ORDER BY Strain.Name",
            (trait_name, dataset_name,)
        )

        sample_data = cursor.fetchall()
        if len(sample_data) > 0:
            sample_list = []
            for sample in sample_data:
                sample_dict = {
                    "sample_name": sample[0],
                    "sample_name_2": sample[1],
                    "value": sample[2],
                    "data_id": sample[3],
                }
                if sample[4]:
                    sample_dict["se"] = sample[4]
                sample_list.append(sample_dict)

            return flask.jsonify(sample_list)
        else:
            if not dataset_name.isdigit():
                group_id = get_group_id(dataset_name)
                if group_id:
                    dataset_or_group = group_id
                else:
                    dataset_or_group = dataset_name
            else:
                dataset_or_group = dataset_name

            cursor.execute(
                "SELECT DISTINCT Strain.Name, Strain.Name2, "
                "PublishData.value, PublishData.Id, PublishSE.error, "
                "NStrain.count FROM (PublishData, Strain, "
                "PublishXRef, PublishFreeze) LEFT JOIN "
                "PublishSE ON (PublishSE.DataId = PublishData.Id "
                "AND PublishSE.StrainId = PublishData.StrainId) "
                "LEFT JOIN NStrain ON "
                "(NStrain.DataId = PublishData.Id AND "
                "NStrain.StrainId = PublishData.StrainId) "
                "WHERE PublishXRef.InbredSetId = PublishFreeze.InbredSetId "
                "AND PublishData.Id = PublishXRef.DataId AND "
                "PublishXRef.Id = %s AND (PublishFreeze.Id = %s "
                "OR PublishFreeze.Name = %s OR "
                "PublishFreeze.ShortName = %s OR "
                "PublishXRef.InbredSetId = %s) AND "
                "PublishData.StrainId = Strain.Id "
                "ORDER BY Strain.Name",
                (trait_name, *(dataset_or_group,)*4)
            )
            if len(sample_data := cursor.fetchall()) > 0:
                sample_list = []
                for sample in sample_data:
                    sample_dict = {
                        "sample_name": sample[0],
                        "sample_name_2": sample[1],
                        "value": sample[2],
                        "data_id": sample[3]
                    }
                    if sample[4]:
                        sample_dict["se"] = sample[4]
                    if sample[5]:
                        sample_dict["n_cases"] = sample[5]
                    sample_list.append(sample_dict)

                return flask.jsonify(sample_list)
            else:
                return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")


@app.route("/api/v_{}/trait/<path:dataset_name>/<path:trait_name>".format(version))
@app.route("/api/v_{}/trait/<path:dataset_name>/<path:trait_name>.<path:file_format>".format(version))
@app.route("/api/v_{}/trait_info/<path:dataset_name>/<path:trait_name>".format(version))
@app.route("/api/v_{}/trait_info/<path:dataset_name>/<path:trait_name>.<path:file_format>".format(version))
def get_trait_info(dataset_name, trait_name, file_format="json"):
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        cursor.execute(
            "SELECT ProbeSet.Id, ProbeSet.Name, ProbeSet.Symbol, "
            "ProbeSet.description, ProbeSet.Chr, ProbeSet.Mb, "
            "ProbeSet.alias, ProbeSetXRef.mean, ProbeSetXRef.se, "
            "ProbeSetXRef.Locus, ProbeSetXRef.LRS, "
            "ProbeSetXRef.pValue, ProbeSetXRef.additive "
            "FROM ProbeSet, ProbeSetXRef, ProbeSetFreeze "
            "WHERE ProbeSet.Name = %s AND "
            "ProbeSetXRef.ProbeSetId = ProbeSet.Id AND "
            "ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id "
            "AND ProbeSetFreeze.Name = %s",
            (trait_name, dataset_name,)
        )
        if trait_info := cursor.fetchone():
            trait_dict = {
                "id": trait_info[0],
                "name": trait_info[1],
                "symbol": trait_info[2],
                "description": trait_info[3],
                "chr": trait_info[4],
                "mb": trait_info[5],
                "alias": trait_info[6],
                "mean": trait_info[7],
                "se": trait_info[8],
                "locus": trait_info[9],
                "lrs": trait_info[10],
                "p_value": trait_info[11],
                "additive": trait_info[12]
            }

            return flask.jsonify(trait_dict)
        else:
            # ZS: Check if the user input the dataset_name as BXDPublish, etc (which is always going to be the group name + "Publish"
            if "Publish" in dataset_name:
                dataset_name = dataset_name.replace("Publish", "")

            group_id = get_group_id(dataset_name)
            cursor.execute(
                "SELECT PublishXRef.PhenotypeId, "
                "PublishXRef.Locus, PublishXRef.LRS, "
                "PublishXRef.additive FROM "
                "PublishXRef WHERE "
                "PublishXRef.Id = %s AND "
                "PublishXRef.InbredSetId = %s",
                (trait_name, group_id,)
            )
            if trait_info := cursor.fetchone():
                trait_dict = {
                    "id": trait_info[0],
                    "locus": trait_info[1],
                    "lrs": trait_info[2],
                    "additive": trait_info[3]
                }

                return flask.jsonify(trait_dict)
            else:
                return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")


@app.route("/api/v_{}/correlation".format(version), methods=("GET",))
def get_corr_results():
    results = correlation.do_correlation(request.args)

    if len(results) > 0:
        # ZS: I think flask.jsonify expects a dict/list instead of JSON
        return flask.jsonify(results)
    else:
        return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")


@app.route("/api/v_{}/mapping".format(version), methods=("GET",))
def get_mapping_results():
    results, format = mapping.do_mapping_for_api(request.args)

    if len(results) > 0:
        if format == "csv":
            filename = "mapping_" + datetime.datetime.utcnow().strftime("%b_%d_%Y_%I:%M%p") + ".csv"

            si = io.StringIO()
            csv_writer = csv.writer(si)
            csv_writer.writerows(results)
            output = make_response(si.getvalue())
            output.headers["Content-Disposition"] = "attachment; filename=" + filename
            output.headers["Content-type"] = "text/csv"

            return output
        elif format == "json":
            return flask.jsonify(results)
        else:
            return return_error(code=415, source=request.url_rule.rule, title="Unsupported Format", details="")
    else:
        return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")


@app.route("/api/v_{}/genotypes/view/<string:group_name>".format(version))
def view_genotype_files(group_name):
    if os.path.isfile("{0}/{1}.json".format(flat_files("genotype"), group_name)):
        with open("{0}/{1}.json".format(flat_files("genotype"), group_name)) as geno_json:
            return flask.jsonify(json.load(geno_json))


@app.route("/api/v_{}/genotypes/<string:file_format>/<string:group_name>/<string:dataset_name>.zip".format(version))
@app.route("/api/v_{}/genotypes/<string:file_format>/<string:group_name>/<string:dataset_name>".format(version))
@app.route("/api/v_{}/genotypes/<string:file_format>/<string:group_name>.zip".format(version))
@app.route("/api/v_{}/genotypes/<string:file_format>/<string:group_name>".format(version))
@app.route("/api/v_{}/genotypes/<string:group_name>.<string:file_format>".format(version))
def get_genotypes(group_name, file_format="csv", dataset_name=None):
    limit_num = None
    if 'limit_to' in request.args:
        if request.args['limit_to'].isdigit():
            limit_num = int(request.args['limit_to'])

    si = io.StringIO()
    if file_format == "csv" or file_format == "geno":
        filename = group_name + ".geno"

        if os.path.isfile("{0}/{1}.geno".format(flat_files("genotype"), group_name)):
            output_lines = []
            with open("{0}/{1}.geno".format(flat_files("genotype"), group_name)) as genofile:
                i = 0
                for line in genofile:
                    if line[0] == "#" or line[0] == "@":
                        output_lines.append([line.strip()])
                    else:
                        if limit_num and i >= limit_num:
                            break
                        output_lines.append(line.split())
                        i += 1

            csv_writer = csv.writer(
                si, delimiter="\t", escapechar="\\", quoting=csv.QUOTE_NONE)
        else:
            return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")
    elif file_format == "rqtl2":
        memory_file = io.BytesIO()
        if dataset_name:
            filename = dataset_name
        else:
            filename = group_name

        if os.path.isfile("{0}/{1}_geno.csv".format(flat_files("genotype/rqtl2"), group_name)):
            yaml_file = json.load(
                open("{0}/{1}.json".format(flat_files("genotype/rqtl2"), group_name)))
            yaml_file["geno"] = filename + "_geno.csv"
            yaml_file["gmap"] = filename + "_gmap.csv"
            yaml_file["pheno"] = filename + "_pheno.csv"
            config_file = [filename + ".json", json.dumps(yaml_file)]
            #config_file = [filename + ".yaml", open("{0}/{1}.yaml".format(flat_files("genotype/rqtl2"), group_name))]
            geno_file = [filename + "_geno.csv",
                         open("{0}/{1}_geno.csv".format(flat_files("genotype/rqtl2"), group_name))]
            gmap_file = [filename + "_gmap.csv",
                         open("{0}/{1}_gmap.csv".format(flat_files("genotype/rqtl2"), group_name))]
            if dataset_name:
                phenotypes = requests.get(
                    "http://gn2.genenetwork.org/api/v_pre1/sample_data/" + dataset_name)
            else:
                phenotypes = requests.get(
                    "http://gn2.genenetwork.org/api/v_pre1/sample_data/" + group_name + "Publish")

            with ZipFile(memory_file, 'w', compression=ZIP_DEFLATED) as zf:
                zf.writestr(config_file[0], config_file[1])
                for this_file in [geno_file, gmap_file]:
                    zf.writestr(this_file[0], this_file[1].read())
                zf.writestr(filename + "_pheno.csv", phenotypes.content)

            memory_file.seek(0)

            return send_file(memory_file, attachment_filename=filename + ".zip", as_attachment=True)
        else:
            return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")
    else:
        filename = group_name + ".bimbam"

        if os.path.isfile("{0}/{1}.geno".format(flat_files("genotype"), group_name)):
            output_lines = []
            with open("{0}/{1}_geno.txt".format(flat_files("genotype/bimbam"), group_name)) as genofile:
                i = 0
                for line in genofile:
                    if limit_num and i >= limit_num:
                        break
                    output_lines.append([line.strip()
                                         for line in line.split(",")])
                    i += 1

            csv_writer = csv.writer(si, delimiter=",")
        else:
            return return_error(code=204, source=request.url_rule.rule, title="No Results", details="")

    csv_writer.writerows(output_lines)
    output = make_response(si.getvalue())
    output.headers["Content-Disposition"] = "attachment; filename=" + filename
    output.headers["Content-type"] = "text/csv"

    return output


@app.route("/api/v_{}/gen_dropdown".format(version), methods=("GET",))
def gen_dropdown_menu():
    with database_connection(get_setting("SQL_URI")) as conn:
        results = gen_menu.gen_dropdown_json(conn)

    if len(results) > 0:
        return flask.jsonify(results)
    else:
        return return_error(code=500, source=request.url_rule.rule, title="Some error occurred", details="")


def return_error(code, source, title, details):
    json_ob = {"errors": [
        {
            "status": code,
            "source": {"pointer": source},
            "title": title,
            "detail": details
        }
    ]}

    return flask.jsonify(json_ob)


def get_dataset_trait_ids(dataset_name, start_vars):

    if 'limit_to' in start_vars:
        limit_string = "LIMIT " + str(start_vars['limit_to'])
    else:
        limit_string = ""
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        if "Geno" in dataset_name:
            data_type = "Geno"  # ZS: Need to pass back the dataset type
            cursor.execute(
                "SELECT GenoXRef.GenoId, Geno.Name, "
                "GenoXRef.GenoFreezeId FROM Geno, "
                "GenoXRef, GenoFreeze WHERE "
                "Geno.Id = GenoXRef.GenoId AND "
                "GenoXRef.GenoFreezeId = GenoFreeze.Id "
                f"AND GenoFreeze.Name = %s {limit_string}",
            (dataset_name,))

            results = cursor.fetchall()

            trait_ids = [result[0] for result in results]
            trait_names = [result[1] for result in results]
            dataset_id = results[0][2]
            return trait_ids, trait_names, data_type, dataset_id

        elif "Publish" in dataset_name or get_group_id(dataset_name):
            data_type = "Publish"
            dataset_name = dataset_name.replace("Publish", "")
            dataset_id = get_group_id(dataset_name)
            cursor.execute(
                "SELECT PublishXRef.PhenotypeId, "
                "PublishXRef.Id, InbredSet.InbredSetCode "
                "FROM PublishXRef, InbredSet WHERE "
                "PublishXRef.InbredSetId = %s AND "
                "InbredSet.Id = PublishXRef.InbredSetId "
                f"{limit_string}",
                (dataset_id,)
            )
            results = cursor.fetchall()

            trait_ids = [result[0] for result in results]
            trait_names = [str(result[2]) + "_" + str(result[1])
                           for result in results]

            return trait_ids, trait_names, data_type, dataset_id

        else:
            data_type = "ProbeSet"
            cursor.execute(
                "SELECT ProbeSetXRef.ProbeSetId, "
                "ProbeSet.Name, ProbeSetXRef.ProbeSetFreezeId "
                "FROM ProbeSet, ProbeSetXRef, "
                "ProbeSetFreeze WHERE "
                "ProbeSet.Id = ProbeSetXRef.ProbeSetId AND "
                "ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id "
                f"AND ProbeSetFreeze.Name = %s {limit_string}",
                (dataset_name,)
            )
            results = cursor.fetchall()
            trait_ids = [result[0] for result in results]
            trait_names = [result[1] for result in results]
            dataset_id = results[0][2]
            return trait_ids, trait_names, data_type, dataset_id


def get_samplelist(dataset_name):
    group_id = get_group_id_from_dataset(dataset_name)
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        cursor.execute(
            "SELECT Strain.Name FROM Strain, StrainXRef "
            "WHERE StrainXRef.StrainId = Strain.Id AND "
            "StrainXRef.InbredSetId = %s",
            (group_id,)
        )
        # sample list
        return [result[0] for result in cursor.fetchall()]


def get_group_id_from_dataset(dataset_name):
    result = ()
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        if "Publish" in dataset_name:
            cursor.execute(
                "SELECT InbredSet.Id FROM "
                "InbredSet, PublishFreeze "
                "WHERE PublishFreeze.InbredSetId = InbredSet.Id "
                "AND PublishFreeze.Name = %s",
                (dataset_name,)
            )
        elif "Geno" in dataset_name:
            cursor.execute(
                "SELECT InbredSet.Id FROM "
                "InbredSet, GenoFreeze WHERE "
                "GenoFreeze.InbredSetId = InbredSet.Id "
                "AND GenoFreeze.Name = %s",
                (dataset_name,)
            )
        else:
            cursor.execute(
                "SELECT InbredSet.Id FROM "
                "InbredSet, ProbeSetFreeze, "
                "ProbeFreeze WHERE "
                "ProbeFreeze.InbredSetId = InbredSet.Id "
                "AND ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId "
                "AND ProbeSetFreeze.Name = %s",
                (dataset_name,)
            )
        if result := cursor.fetchone():
            return result[0]
        return None


def get_group_id(group_name):
    with database_connection(get_setting("SQL_URI")) as conn, conn.cursor() as cursor:
        cursor.execute(
            "SELECT InbredSet.Id FROM InbredSet "
            "WHERE InbredSet.Name = %s",
            (group_name,)
        )
        if group_id := cursor.fetchone():
            return group_id[0]
        return None