B
    ö,MaÆ  ã               @   s    d dl mZ dd„ Zdd„ ZdS )é    )Ú
sqlConnectc              C   sº   t  ¡ } d}|  |¡}d}|  |¡d }d}|  |¡d }d}|  |¡d }d}	|  |	¡}
d}|  |¡}|  ¡  |d	 |||t|
d
 ƒt|
d ƒt|
d ƒt|
d ƒ|
d |d dœ
}|S )Nz[SELECT FORMAT(COUNT(DISTINCT ncbi_taxon_id),0) species_num FROM mbodymap_species_to_sampleszWSELECT FORMAT(COUNT(DISTINCT ncbi_taxon_id),0) genus_num FROM mbodymap_genus_to_samplesÚ	genus_numz-SELECT COUNT(1) FROM mbodymap_phenotype_indexzCOUNT(1)z@SELECT COUNT(1) FROM mbodymap_phenotype_index WHERE valid_num!=0z¦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_introzCSELECT COUNT(DISTINCT project_id) project_num FROM mbodymap_samplesÚspecies_numÚrun_numÚloaded_run_numÚQC_0_numÚQC_1_numÚsite_numÚproject_num)
r   r   ÚdiseaseTotalÚdiseaseWithValidr   r   r   r   r	   r
   )r   ÚMySQLConnectionZqueryOneÚcloseÚint)ÚconnectZsqlIntroZspeciesAndDiseaseNumZ	sql_genusr   Zsql_diseaseTotalr   Zsql_diseaseWithValidr   ZsqlTotalÚtotalZsqlProjectsr
   Úresult© r   úRC:\Users\Administrator\Desktop\mbodymap\mbodymap-server\service\homePageService.pyÚ	introData   s2    






r   c           
   C   s   t  ¡ } d } } } } } } }}dddg}	ddg}
g }d}|  |¡}xL|D ]B}|d }d	| }|  |¡}d }}x*|D ]"}|d
 dkrœ|d7 }q‚|d7 }q‚W ||	kr
||7 }||7 }||d 7 }|d7 }|t|	ƒkr˜| dd |¡d |¡|||dœ¡ qT||
krl||7 }||7 }||d 7 }|d7 }|t|
ƒkr˜| dd |¡d |¡|||dœ¡ qT| |d d |¡d |¡|||d dœ¡ qTW |S )Nr   ZRectumZColonZCecumZIleumZDuodenumz,SELECT BodySite, run_num FROM mbodymap_introZBodySitez8SELECT disease FROM mbodymap_samples WHERE BodySite="%s"ZdiseaseZD006262é   r   zLarge intestinez{:,})ÚsiteZnormalStringZabnormalStringÚnormalÚabnormalZrun_num_totalzSmall intestine)r   r   ÚqueryÚlenÚappendÚformat)r   Z
largeTotalZ
smallTotalZlargeNZlargeAnZsmallNZsmallAnÚlÚsZlargeZsmallr   Z	sql_sitesZfindSiteÚitemr   Zsql_diseaseZfindDiseaser   r   Úitr   r   r   ÚpicAndTblData&   sd     






r"   N)Úutilsr   r   r"   r   r   r   r   Ú<module>   s   "