from utils import sqlConnect
import json


def getDataIntro():
    connect = sqlConnect.MySQLConnection()
    # 懒得起名了，sql1234分别对应1234行的数据
    sql_1 = 'SELECT SUM(`Number of runs`) run_num, COUNT(1) project_num FROM `mbodymap_projects`'
    first = connect.queryOne(sql_1)
    # sql_first_run_num = 'SELECT SUM(run_num) run_num FROM mbodymap_intro'
    # first_run_num = connect.queryOne(sql_first_run_num)['run_num']
    sql_2 = 'SELECT COUNT(DISTINCT project_id) project_num, COUNT(1) run_num FROM mbodymap_samples'
    second = connect.queryOne(sql_2)
    sql_3 = 'SELECT count(DISTINCT project_id) project_num, COUNT(t1.run_id) run_num from mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.run_id=t2.accession_id'
    third = connect.queryOne(sql_3)
    sql_4 = 'SELECT count(DISTINCT project_id) project_num, COUNT(t1.run_id) run_num from mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.run_id=t2.accession_id AND t2.QCStatus=0'
    forth = connect.queryOne(sql_4)
    result = {
        'first_run_num': int(first['run_num']),
        'first_project_num': int(first['project_num']),
        'second_run_num': int(second['run_num']),
        'second_project_num': int(second['project_num']),
        'third_run_num': int(third['run_num']),
        'third_project_num': int(third['project_num']),
        'forth_run_num': int(forth['run_num']),
        'forth_project_num': int(forth['project_num']),
    }
    return result


def getProjectData(page, limit):
    project_result = []
    connect = sqlConnect.MySQLConnection()
    sql_projects = 'SELECT `NCBI Project ID`, `Original Project description`, `Number of runs`, `Related Publications` FROM `mbodymap_projects`'
    projects = connect.query(sql_projects)
    total = len(projects)
    if page == 1:
        i = 0
        for project in projects:
            if i <= 9:
                # -----------    判断是否是marker ------------------------------
                sql_marker = 'select * from curated_lefse_analysis_results where project_id="{}" limit 1'.format(
                    project['NCBI\xa0Project\xa0ID'])
                if_marker = connect.queryOne(sql_marker)
                if if_marker is None:
                    marker = False
                else:
                    marker = True
                sql_processed_runs_num = 'SELECT COUNT(t1.run_id) processed_num FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id'.format(
                    project['NCBI\xa0Project\xa0ID'])
                processed_runs_num = connect.queryOne(sql_processed_runs_num)['processed_num']
                sql_phenotypes = 'SELECT DISTINCT t1.disease, t3.term FROM mbodymap_samples t1, mbodymap_loaded_samples t2, mesh_data t3 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id AND t1.disease=t3.uid'.format(
                    project['NCBI\xa0Project\xa0ID'])
                phenotypes = connect.query(sql_phenotypes)
                project_result.append({
                    'projectId': project['NCBI\xa0Project\xa0ID'],
                    'processed_runs': processed_runs_num,
                    'total_runs': project['Number\xa0of\xa0runs'],
                    'related_phenotype': phenotypes,
                    'Related_publication': project['Related\xa0Publications'],
                    'description': project['Original\xa0Project\xa0description'],
                    'marker': marker
                })
                i += 1
    else:
        i = 0
        for project in projects:
            if i <= (page - 1) * limit - 1:
                i += 1
                continue
            if (page - 1) * limit - 1 < i <= page * limit - 1:
                # -----------    判断是否是marker ------------------------------
                sql_marker = 'select * from curated_lefse_analysis_results where project_id="{}" limit 1'.format(
                    project['NCBI\xa0Project\xa0ID'])
                if_marker = connect.queryOne(sql_marker)
                if if_marker is None:
                    marker = False
                else:
                    marker = True
                sql_processed_runs_num = 'SELECT COUNT(t1.run_id) processed_num FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id'.format(
                    project['NCBI\xa0Project\xa0ID'])
                processed_runs_num = connect.queryOne(sql_processed_runs_num)['processed_num']
                sql_phenotypes = 'SELECT DISTINCT t1.disease, t3.term FROM mbodymap_samples t1, mbodymap_loaded_samples t2, mesh_data t3 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id AND t1.disease=t3.uid'.format(
                    project['NCBI\xa0Project\xa0ID'])
                phenotypes = connect.query(sql_phenotypes)
                project_result.append({
                    'projectId': project['NCBI\xa0Project\xa0ID'],
                    'processed_runs': processed_runs_num,
                    'total_runs': project['Number\xa0of\xa0runs'],
                    'related_phenotype': phenotypes,
                    'Related_publication': project['Related\xa0Publications'],
                    'description': project['Original\xa0Project\xa0description'],
                    'marker': marker
                })
                i += 1
            if i > page * limit - 1:
                break

    return project_result, total


def getRunData(page, limit):
    run_result = []
    connect = sqlConnect.MySQLConnection()
    sql_runs = 'SELECT project_id, BodySite, run_id, experiment_type, instrument_model, nr_reads_sequenced, phenotype, host_age, sex, BMI, country FROM mbodymap_samples'
    runs = connect.query(sql_runs)
    total = len(runs)
    if page == 1:
        i = 0
        for item in runs:
            if i <= 9:
                sql_run_QC = 'SELECT t2.QCStatus FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id="{}" AND t1.run_id=t2.accession_id'.format(
                    item['project_id'], item['run_id'])
                QC = connect.queryOne(sql_run_QC)
                if QC is None:
                    item['QCStatus'] = ''
                else:
                    item['QCStatus'] = QC['QCStatus']
                run_result.append({
                    'project_id': item['project_id'],
                    'BodySite': item['BodySite'],
                    'run_id': item['run_id'],
                    'experiment_type': item['experiment_type'],
                    'instrument_model': item['instrument_model'],
                    'nr_reads_sequenced': item['nr_reads_sequenced'],
                    'phenotype': item['phenotype'],
                    'host_age': item['host_age'],
                    'sex': item['sex'],
                    'BMI': item['BMI'],
                    'country': item['country'],
                    'QC_Status': item['QCStatus']
                })
                i += 1
    else:
        i = 0
        for item in runs:
            if i <= (page - 1) * limit - 1:
                i += 1
                continue
            if (page - 1) * limit - 1 < i <= page * limit - 1:
                sql_run_QC = 'SELECT t2.QCStatus FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id="{}" AND t1.run_id=t2.accession_id'.format(
                    item['project_id'], item['run_id'])
                QC = connect.queryOne(sql_run_QC)
                if QC is None:
                    item['QCStatus'] = ''
                else:
                    item['QCStatus'] = QC['QCStatus']
                run_result.append({
                    'project_id': item['project_id'],
                    'BodySite': item['BodySite'],
                    'run_id': item['run_id'],
                    'experiment_type': item['experiment_type'],
                    'instrument_model': item['instrument_model'],
                    'nr_reads_sequenced': item['nr_reads_sequenced'],
                    'phenotype': item['phenotype'],
                    'host_age': item['host_age'],
                    'sex': item['sex'],
                    'BMI': item['BMI'],
                    'country': item['country'],
                    'QC_Status': item['QCStatus']
                })
                i += 1
            if i > page * limit - 1:
                break

    return run_result, total


def getProjectDataIntro(projectId):
    connect = sqlConnect.MySQLConnection()
    sql_qc1_runs = 'SELECT COUNT(t1.run_id) num FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1'.format(
        projectId)
    qc1_runs = int(connect.queryOne(sql_qc1_runs)['num'])
    sql_qc0_runs = 'SELECT COUNT(t1.run_id) num FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=0'.format(
        projectId)
    qc0_runs = int(connect.queryOne(sql_qc0_runs)['num'])
    sql_project = 'SELECT `Original Project description`, `Number of runs`, `Related Publications` FROM `mbodymap_projects` WHERE `NCBI Project ID`="{}"'.format(
        projectId)
    project = connect.queryOne(sql_project)
    sql_phenotypes = 'SELECT DISTINCT t1.disease, t3.term FROM mbodymap_samples t1, mbodymap_loaded_samples t2, mesh_data t3 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id AND t1.disease=t3.uid'.format(
        projectId)
    phenotypes = connect.query(sql_phenotypes)
    project_result = {
        'projectId': projectId,
        'processed_runs': qc1_runs + qc0_runs,
        'total_runs': project['Number\xa0of\xa0runs'],
        'related_phenotype': phenotypes,
        'Related_publication': project['Related\xa0Publications'],
        'description': project['Original\xa0Project\xa0description'],
        'qc0': qc0_runs,
        'qc1': qc1_runs
    }
    return project_result


def getProjectRunsData(projectId):
    connect = sqlConnect.MySQLConnection()
    ## 所有的run
    sql_all_runs = 'SELECT BodySite, run_id, experiment_type, instrument_model, nr_reads_sequenced, phenotype, host_age, sex, BMI, country FROM mbodymap_samples WHERE project_id="{}"'.format(
        projectId)
    all_runs = connect.query(sql_all_runs)
    print(len(all_runs))
    for run in all_runs:
        sql_run_QC = 'SELECT t2.QCStatus FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id="{}" AND t1.run_id=t2.accession_id'.format(
            projectId, run['run_id'])
        QC = connect.queryOne(sql_run_QC)
        if QC is None:
            run['QCStatus'] = ''
        else:
            run['QCStatus'] = QC['QCStatus']
        if run['BodySite'] == 'Rectum' or run['BodySite'] == 'Colon' or run['BodySite'] == 'Cecum':
            run['urlName'] = 'gut'
            run['urlParam'] = 'Large Intestine'
        elif run['BodySite'] == 'Ileum' or run['BodySite'] == 'Duodenum':
            run['urlName'] = 'gut'
            run['urlParam'] = 'Small Intestine'
        else:
            run['urlName'] = 'bodysite'
            run['urlParam'] = run['BodySite']
    return all_runs


def getRunData2(runId):
    connect = sqlConnect.MySQLConnection()
    sql_run = 'SELECT BodySite, Disease, project_id, experiment_type, instrument_model, nr_reads_sequenced, phenotype, host_age, sex, BMI, country, more_info FROM mbodymap_samples WHERE run_id="{}"'.format(
        runId)
    run = connect.queryOne(sql_run)
    sql_project_info = 'SELECT `Original Project description` info, `Number of runs` total_runs, `Related Publications` related_publication FROM mbodymap_projects WHERE `NCBI Project ID`="{}"'.format(
        run['project_id'])
    project_info = connect.queryOne(sql_project_info)
    sql_processed_runs_num = 'SELECT COUNT(t1.run_id) processed_num FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id'.format(
        run['project_id'])
    processed_runs_num = connect.queryOne(sql_processed_runs_num)
    if processed_runs_num is None:
        processed_runs_num = 0
    else:
        processed_runs_num = processed_runs_num['processed_num']
    sql_phenotypes = 'SELECT DISTINCT t1.disease, t3.term FROM mbodymap_samples t1, mbodymap_loaded_samples t2, mesh_data t3 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id AND t1.disease=t3.uid'.format(
        run['project_id'])
    phenotypes = connect.query(sql_phenotypes)
    project_info['processed_runs'] = processed_runs_num
    project_info['related_phenotype'] = phenotypes
    sql_QCStatus = 'SELECT QCStatus FROM mbodymap_loaded_samples WHERE accession_id="{}"'.format(runId)
    QCStatus = connect.queryOne(sql_QCStatus)
    if QCStatus is None:
        run['QCStatus'] = 'empty'
        run['project_info'] = project_info
    else:
        if QCStatus['QCStatus'] == 1:
            run['plotData'] = getRunPlot(runId)
        else:
            run['plotData'] = {}
        run['QCStatus'] = QCStatus['QCStatus']
        run['project_info'] = project_info
    if run['BodySite'] == 'Rectum' or run['BodySite'] == 'Colon' or run['BodySite'] == 'Cecum':
        run['urlName'] = 'gut'
        run['urlParam'] = 'Large Intestine'
    elif run['BodySite'] == 'Ileum' or run['BodySite'] == 'Duodenum':
        run['urlName'] = 'gut'
        run['urlParam'] = 'Small Intestine'
    else:
        run['urlName'] = 'bodysite'
        run['urlParam'] = run['BodySite']
    return run


def getRunPlot(runId):
    connect = sqlConnect.MySQLConnection()
    speciesBarPlot = []
    speciesBarX = []
    speciesBarY = []
    speciesPiePlotValue = []
    speciesPiePlotLabel = []
    genusBarPlot = []
    genusBarX = []
    genusBarY = []
    genusPiePlotValue = []
    genusPiePlotLabel = []
    species_ten = 0
    genus_ten = 0
    ranks = ['species', 'genus']
    sql_uid = 'SELECT uid FROM mbodymap_loaded_samples WHERE accession_id="{}"'.format(runId)
    uid = connect.queryOne(sql_uid)['uid']
    for rank in ranks:
        sql_ten = 'SELECT ncbi_taxon_id, relative_abundance FROM `mbodymap_relative_species_abundances` where loaded_uid={} AND taxon_rank_level="{}" AND ncbi_taxon_id!=-1 ORDER BY relative_abundance DESC LIMIT 10'.format(
            uid, rank)
        ten = connect.query(sql_ten)
        sql_all = 'SELECT SUM(relative_abundance) all_sum FROM `mbodymap_relative_species_abundances` where loaded_uid={} AND taxon_rank_level="{}" AND ncbi_taxon_id!=-1'.format(
            uid, rank)
        all = connect.queryOne(sql_all)['all_sum']
        for item in ten:
            sql_name = 'SELECT name FROM biosql_taxon_2 WHERE ncbi_taxon_id={}'.format(item['ncbi_taxon_id'])
            name = connect.queryOne(sql_name)['name']
            if rank == 'species':
                speciesBarPlot.append({
                    'name': name,
                    'ncbi_taxon_id': item['ncbi_taxon_id'],
                    'relative_abundance': item['relative_abundance']
                })
                speciesPiePlotValue.append(item['relative_abundance'])
                speciesPiePlotLabel.append(name)
                species_ten += item['relative_abundance']
            else:
                genusBarPlot.append({
                    'name': name,
                    'ncbi_taxon_id': item['ncbi_taxon_id'],
                    'relative_abundance': item['relative_abundance']
                })
                genusPiePlotValue.append(item['relative_abundance'])
                genusPiePlotLabel.append(name)
                genus_ten += item['relative_abundance']
        sql_unKnown = 'SELECT relative_abundance FROM `mbodymap_relative_species_abundances` where loaded_uid={} AND taxon_rank_level="{}" AND ncbi_taxon_id=-1'.format(
            uid, rank)
        unKnown = connect.queryOne(sql_unKnown)['relative_abundance']
        if rank == 'species':
            speciesBarPlot.append({
                'name': 'Unknown',
                'ncbi_taxon_id': -1,
                'relative_abundance': unKnown
            })
            speciesBarPlot.append({
                'name': 'Others',
                'ncbi_taxon_id': 0,
                'relative_abundance': all - species_ten
            })
            speciesPiePlotValue.append(unKnown)
            speciesPiePlotLabel.append('Unknown')
            speciesPiePlotValue.append(all - species_ten)
            speciesPiePlotLabel.append('Others')
        else:
            genusBarPlot.append({
                'name': 'Unknown',
                'ncbi_taxon_id': -1,
                'relative_abundance': unKnown
            })
            genusBarPlot.append({
                'name': 'Others',
                'ncbi_taxon_id': 0,
                'relative_abundance': all - species_ten
            })
            genusPiePlotValue.append(unKnown)
            genusPiePlotLabel.append('Unknown')
            genusPiePlotValue.append(all - species_ten)
            genusPiePlotLabel.append('Others')
    for genusBarPlot_item in genusBarPlot:
        genusBarX.append(format(genusBarPlot_item['relative_abundance'], '.1f'))
        genusBarY.append(genusBarPlot_item['name'])
    for speciesBarPlot_item in speciesBarPlot:
        speciesBarX.append(format(speciesBarPlot_item['relative_abundance'], '.1f'))
        speciesBarY.append(speciesBarPlot_item['name'])
    plotData = {
        'genusBarX': genusBarX,
        'genusBarY': genusBarY,
        'speciesPiePlotValue': speciesPiePlotValue,
        'speciesPiePlotLabel': speciesPiePlotLabel,
        'speciesBarX': speciesBarX,
        'speciesBarY': speciesBarY,
        'genusPiePlotValue': genusPiePlotValue,
        'genusPiePlotLabel': genusPiePlotLabel,
        'genusDownloadData': genusBarPlot,
        'speciesDownloadData': speciesBarPlot
    }
    return plotData


def markerDataIntro():
    connect = sqlConnect.MySQLConnection()
    # project number and phenotype number
    sql_1 = 'SELECT COUNT(DISTINCT project_id) project_num, COUNT(DISTINCT phenotype2) phenotype_num FROM curated_lefse_analysis_summary'
    result_1 = connect.queryOne(sql_1)
    # sample number
    sql_2 = 'select sum(distinct project_sample_num) sample_num from curated_lefse_analysis_summary'
    num = connect.queryOne(sql_2)['sample_num']
    result = {
        'project_num': result_1['project_num'],
        'phenotype_num': result_1['phenotype_num'],
        'sample_num': int(num)
    }
    return result


def markerDataTable():
    connect = sqlConnect.MySQLConnection()
    # 按project归类的数据
    project_result = []
    sql_project = 'select distinct project_id from curated_lefse_analysis_results'
    project = connect.query(sql_project)
    for item in project:
        sql_pheno = 'select distinct phenotype2, phenotype2_name from curated_lefse_analysis_results where project_id="{}"'.format(item['project_id'])
        pheno = connect.query(sql_pheno)
        pheno.insert(0, {'phenotype2': 'D006262', 'phenotype2_name': 'Health'})
        # print(pheno)
        project_phenotype_result = []
        for pheno_item in pheno:
            sql_pheno_result = 'select count(1) sample_num from curated_lefse_analysis_group_to_samples where project_id="{}" and phenotype="{}"'.format(
                item['project_id'], pheno_item['phenotype2'])
            pheno_result = connect.queryOne(sql_pheno_result)
            project_phenotype_result.append({
                'phenotype': pheno_item['phenotype2'],
                'phenotype_name': pheno_item['phenotype2_name'],
                'sample_num': pheno_result['sample_num']
            })
        project_result.append({
            'project': item['project_id'],
            'pheno': project_phenotype_result
        })
    # print(project_result)
    # 按phenotype归类的数据
    phenotype_result = []
    sql_phenotype = 'select distinct phenotype2, phenotype2_name from curated_lefse_analysis_results'
    phenotype = connect.query(sql_phenotype)
    pheno.insert(0, {'phenotype2': 'D006262', 'phenotype2_name': 'Health'})
    for item in phenotype:
        if item['phenotype2'] == 'D006262':
            sql_project = 'select distinct project_id from curated_lefse_analysis_results where phenotype1="D006262"'
        else:
            sql_project = 'select distinct project_id from curated_lefse_analysis_results where phenotype2="{}"'.format(item['phenotype2'])
        project = connect.query(sql_project)
        phenotype_peoject_result = []
        for project_item in project:
            sql_proj_result = 'select count(1) sample_num from curated_lefse_analysis_group_to_samples where project_id="{}" and phenotype="{}"'.format(project_item['project_id'], item['phenotype2'])
            proj_result = connect.queryOne(sql_proj_result)
            phenotype_peoject_result.append({
                'project': project_item['project_id'],
                'sample_num': proj_result['sample_num']
            })
        phenotype_result.append({
            'phenotype': item['phenotype2'],
            'phenotype_name': item['phenotype2_name'],
            'project': phenotype_peoject_result
        })
    return {'project_result': project_result, 'phenotype_result': phenotype_result}


def getMarkerProjects(projectId):
    connect = sqlConnect.MySQLConnection()
    # -----------    判断是否是marker ------------------------------
    sql_marker = 'select * from curated_lefse_analysis_results where project_id="{}" limit 1'.format(
        projectId)
    if_marker = connect.queryOne(sql_marker)
    if if_marker is None:
        marker = False
        return marker
    else:
        # ----------    marker intro    ---------------------------------
        sql_intro = 'select count(1) comparisons, count(distinct ncbi_taxon_id) taxa, count(distinct bodysite) bodysite from curated_lefse_analysis_results where project_id="{}"'.format(
            projectId)
        intro = connect.queryOne(sql_intro)
        sql_intro_species = 'select count(distinct ncbi_taxon_id) taxa from curated_lefse_analysis_results where project_id="{}" and taxon_rank_level="species"'.format(
            projectId)
        intro_species = connect.queryOne(sql_intro_species)['taxa']
        sql_intro_genus = 'select count(distinct ncbi_taxon_id) taxa from curated_lefse_analysis_results where project_id="{}" and taxon_rank_level="genus"'.format(
            projectId)
        intro_genus = connect.queryOne(sql_intro_genus)['taxa']
        intro['species'] = intro_species
        intro['genus'] = intro_genus
        sql_introTable = 'select distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name, bodysite, count(ncbi_taxon_id) taxa_num FROM `curated_lefse_analysis_results` WHERE project_id="{}" ' \
                         ' GROUP BY phenotype1, phenotype1_name, phenotype2, phenotype2_name, bodysite'.format(projectId)
        intro['table'] = connect.query(sql_introTable)
        # ------------  marker table    --------------------------------
        # 以phenotype进行分类
        sql_pheno = 'SELECT distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name FROM `curated_lefse_analysis_results` WHERE project_id="{}"'.format(
            projectId)
        pheno = connect.query(sql_pheno)
        phenotype_table = []
        for item in pheno:
            sql_taxa = 'select distinct ncbi_taxon_id, taxa_name, taxon_rank_level, bodysite, lda from `curated_lefse_analysis_results` where project_id="{}" and phenotype1="{}" and phenotype2="{}"'.format(
                projectId, item['phenotype1'], item['phenotype2']
            )
            taxa = connect.query(sql_taxa)
            item['taxa'] = getTaxonsDatabase(taxa)
            phenotype_table.append(item)
        # 以 marker taxa 分类
        sql_taxa = 'select distinct ncbi_taxon_id, taxa_name from `curated_lefse_analysis_results` where project_id="{}"'.format(
            projectId)
        taxa = getTaxonsDatabase(connect.query(sql_taxa))
        taxa_table = []
        for taxa_item in taxa:
            sql_pheno = 'select distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name, bodysite, ' \
                        'taxon_rank_level, lda from `curated_lefse_analysis_results` where project_id="{}" and ncbi_taxon_id={}'.format(
                projectId, taxa_item['ncbi_taxon_id'])
            pheno = connect.query(sql_pheno)
            taxa_item['pheno'] = pheno
            taxa_table.append(taxa_item)
        # ------------- bar data    ----------------------------------------
        barData = []
        for bar_item in pheno:
            sql_phenoData = 'select ncbi_taxon_id, taxa_name, bodysite, lda from curated_lefse_analysis_results WHERE project_id="{}" and phenotype1="{}" and phenotype2="{}"'.format(
                projectId, bar_item['phenotype1'], bar_item['phenotype2']
            )
            # print(sql_phenoData)
            phenoData = connect.query(sql_phenoData)
            xaxis = []
            yaxis = []
            for item in phenoData:
                xaxis.append(item['lda'])
                yaxis.append(item['taxa_name'] + ' (' + item['bodysite'] + ')')
            barData.append({
                'phenotype1': bar_item['phenotype1'],
                'phenotype2': bar_item['phenotype2'],
                'phenotype1_name': bar_item['phenotype1_name'],
                'phenotype2_name': bar_item['phenotype2_name'],
                'xaxis': xaxis,
                'yaxis': yaxis
            })

        # --------------    result  -----------------------------------
        result = {
            'intro': intro,
            'phenotype_table': phenotype_table,
            'taxa_table': taxa_table,
            'bar': barData
        }
        return result


def getTaxonsDatabase(arr):
    connect = sqlConnect.MySQLConnection()
    for item in arr:
        sql_mvp = 'select attributes from cross_db_links where ncbi_taxon_id={}'.format(item['ncbi_taxon_id'])
        mvp = connect.queryOne(sql_mvp)
        if mvp is not None:
            item['ifMvp'] = True
            item['mvpData'] = json.loads(mvp['attributes'])
            # print(type(mvp['attributes']))
        sql_gmrepo = 'select * from gmrepo_species where ncbi_taxon_id={}'.format(item['ncbi_taxon_id'])
        gmrepo = connect.queryOne(sql_gmrepo)
        if gmrepo is not None:
            item['ifGmrepo'] = True
        sql_hmdad = 'select * from hmdad_taxon where ncbi_taxon_id={}'.format(item['ncbi_taxon_id'])
        hmdad = connect.queryOne(sql_hmdad)
        if hmdad is not None:
            item['ifHmdad'] = True
    return arr