from utils import sqlConnect


def introData():
    connect = sqlConnect.MySQLConnection()
    # intro表格的数据
    # species和diseases的数量
    sqlIntro = "SELECT FORMAT(COUNT(DISTINCT ncbi_taxon_id),0) species_num, FORMAT(COUNT(DISTINCT disease),0) disease_num FROM mbodymap_species_to_samples"
    speciesAndDiseaseNum = connect.queryOne(sqlIntro)
    # 收集样本总量，已分析总量，QC为0和1的量，部位数量
    sqlTotal = "SELECT SUM(run_num) run_num, SUM(loaded_run_num) loaded_run_num, SUM(QC_0_num) QC_0_num, SUM(QC_1_num) QC_1_num, COUNT(DISTINCT BodySite) site_num FROM mbodymap_intro"
    total = connect.queryOne(sqlTotal)
    # project数量
    sqlProjects = "SELECT COUNT(DISTINCT `NCBI Project ID`) project_num FROM mbodymap_projects"
    project_num = connect.queryOne(sqlProjects)
    connect.close()
    result = {
        'species_num': speciesAndDiseaseNum['species_num'],
        'disease_num': speciesAndDiseaseNum['disease_num'],
        'run_num': int(total['run_num']),
        'loaded_run_num': int(total['loaded_run_num']),
        'QC_0_num': int(total['QC_0_num']),
        'QC_1_num': int(total['QC_1_num']),
        'site_num': total['site_num'],
        'project_num': project_num['project_num']
    }
    return result

def picAndTblData():
    connect = sqlConnect.MySQLConnection()
    largeTotal = smallTotal = largeN = largeAn = smallN = smallAn = l = s = 0
    large = ['Rectum', 'Colon', 'Cecum']
    small = ['Ileum', 'Duodenum']
    result = []
    # 找出所有的site
    sql_sites = 'SELECT BodySite, run_num FROM mbodymap_intro'
    findSite = connect.query(sql_sites)
    for item in findSite:
        site = item['BodySite']
        # 找出每个部位所有的disease
        sql_disease = 'SELECT disease FROM mbodymap_samples WHERE BodySite="%s"' % (site)
        findDisease = connect.query(sql_disease)
        normal = abnormal = 0
        # disease=D006262时normal加1，否则abnormal加1
        for it in findDisease:
            if it['disease'] == "D006262":
                normal += 1
            else:
                abnormal += 1
        # 若为大小肠中的部位，则累计各个部位的noemal和abnormal
        if site in large:
            largeN += normal
            largeAn += abnormal
            largeTotal += item['run_num']
            l += 1
            if l >= len(large):
                result.append({
                    'site': 'Large intestine',
                    'normalString': '{:,}'.format(largeN),
                    'abnormalString': '{:,}'.format(largeAn),
                    'normal': largeN,
                    'abnormal': largeAn,
                    'run_num_total': largeTotal
                    # 'disease': it
                })
        elif site in small:
            smallN += normal
            smallAn += abnormal
            smallTotal += item['run_num']
            s += 1
            if s >= len(small):
                result.append({
                    'site': 'Small intestine',
                    'normalString': '{:,}'.format(smallN),
                    'abnormalString': '{:,}'.format(smallAn),
                    'normal': smallN,
                    'abnormal': smallAn,
                    'run_num_total': smallTotal
                    # 'disease': it
                })
        else:
            result.append({
                'site': item['BodySite'],
                'normalString': '{:,}'.format(normal),
                'abnormalString': '{:,}'.format(abnormal),
                'normal': normal,
                'abnormal': abnormal,
                'run_num_total': item['run_num']
                # 'disease': it
            })
    return result