from utils import sqlConnect


def getData():
    connect = sqlConnect.MySQLConnection()
    sql_phenotypes = 'SELECT disease, disease_name, site_num, run_num, processed_num, valid_num, failed_num, species_num, genus_num FROM mbodymap_phenotype_index'
    phenotypes = connect.query(sql_phenotypes)
    sql_phenotypesWithValidRuns = 'SELECT COUNT(1) FROM mbodymap_phenotype_index WHERE valid_num!=0'
    total_valid = connect.queryOne(sql_phenotypesWithValidRuns)['COUNT(1)']
    return phenotypes, total_valid


def detailData(disease):
    connect = sqlConnect.MySQLConnection()
    sql_sites = 'SELECT DISTINCT BodySite FROM mbodymap_samples WHERE disease="{}"'.format(disease)
    sites = connect.query(sql_sites)
    site_result = []
    for site in sites:
        # 获取run id的数量
        sql_runs = 'SELECT COUNT(run_id) run_id_num FROM mbodymap_samples WHERE disease="{}" AND BodySite="{}"'.format(
            disease, site['BodySite'])
        runs_num = connect.queryOne(sql_runs)['run_id_num']
        # 获取processed valid failed runs的数量
        valid = 0
        failed = 0
        sql_loaded_runs = 'SELECT accession_id,QCStatus,uid FROM mbodymap_loaded_samples WHERE accession_id IN (SELECT run_id From mbodymap_samples WHERE disease="{}" AND BodySite="{}")'.format(
            disease, site['BodySite'])
        loaded_runs = connect.query(sql_loaded_runs)
        processed_num = len(loaded_runs)
        for loaded_run in loaded_runs:
            # valid failed
            if loaded_run['QCStatus'] == 1:
                valid += 1
            else:
                failed += 1
        # 查询有关species的数量
        sql_species = 'SELECT COUNT(DISTINCT ncbi_taxon_id) from mbodymap_species_to_samples WHERE BodySite="{}" AND disease="{}"'.format(
            site['BodySite'], disease)
        species = connect.queryOne(sql_species)['COUNT(DISTINCT ncbi_taxon_id)']
        # 疾病部位关联
        sql_association = 'select association from mbodymap_bodysite_disease_association where bodysite="{}" and disease="{}"'.format(
            site['BodySite'], disease
        )
        association = connect.queryOne(sql_association)['association']
        site_result.append({
            'site': site['BodySite'],
            'run_num': runs_num,
            'processed_num': processed_num,
            'valid_num': valid,
            'failed_num': failed,
            'species_num': species,
            'association': association
        })
    return site_result


def getName(disease):
    connect = sqlConnect.MySQLConnection()
    sql_name = 'SELECT term FROM mesh_data WHERE uid="{}"'.format(disease)
    name = connect.queryOne(sql_name)['term']
    return name