B
    rØ`ÉB  ã               @   sH   d dl mZ dd„ Zdd„ Zdd„ Zdd	„ Zd
d„ Zdd„ Zdd„ ZdS )é    )Ú
sqlConnectc        
   
   C   sš   t  ¡ } d}|  |¡}d}|  |¡}d}|  |¡}d}|  |¡}t|d ƒt|d ƒt|d ƒt|d ƒt|d ƒt|d ƒt|d ƒt|d ƒdœ}	|	S )NuU   SELECT SUM(`NumberÂ ofÂ runs`) run_num, COUNT(1) project_num FROM `mbodymap_projects`zUSELECT COUNT(DISTINCT project_id) project_num, COUNT(1) run_num FROM mbodymap_sampleszœ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_idz®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Úrun_numÚproject_num)Zfirst_run_numZfirst_project_numZsecond_run_numZsecond_project_numZthird_run_numZthird_project_numZforth_run_numZforth_project_num)r   ÚMySQLConnectionÚqueryOneÚint)
ÚconnectZsql_1ÚfirstZsql_2ÚsecondZsql_3ÚthirdZsql_4ZforthÚresult© r   úRC:\Users\Administrator\Desktop\mbodymap\mbodymap-server\service\dataPageService.pyÚgetDataIntro   s$    










r   c          
   C   sˆ  g }t  ¡ }d}| |¡}t|ƒ}| dkrªd}xx|D ]p}|dkr4d |d ¡}	| |	¡d }
d |d ¡}| |¡}| |d |
|d	 ||d
 |d dœ¡ |d7 }q4W nÖd}xÐ|D ]È}|| d | d krÖ|d7 }q´| d | d |  k r| | d krjn ndd |d ¡}	| |	¡d }
d |d ¡}| |¡}| |d |
|d	 ||d
 |d dœ¡ |d7 }|| | d kr´P q´W ||fS )Nu‚   SELECT `NCBIÂ ProjectÂ ID`, `OriginalÂ ProjectÂ description`, `NumberÂ ofÂ runs`, `RelatedÂ Publications` FROM `mbodymap_projects`é   r   é	   z‘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_idu   NCBIÂ ProjectÂ IDÚprocessed_numz³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.uidu   NumberÂ ofÂ runsu   RelatedÂ Publicationsu   OriginalÂ ProjectÂ description)Ú	projectIdÚprocessed_runsÚ
total_runsÚrelated_phenotypeÚRelated_publicationÚdescription)r   r   ÚqueryÚlenÚformatr   Úappend)ÚpageÚlimitÚproject_resultr   Zsql_projectsÚprojectsÚtotalÚiÚprojectÚsql_processed_runs_numÚprocessed_runs_numÚsql_phenotypesÚ
phenotypesr   r   r   ÚgetProjectData   s\    





0


r(   c             C   sú  g }t  ¡ }d}| |¡}t|ƒ}| dkràd}x¬|D ]¤}|dkr4d |d |d ¡}	| |	¡}
|
d krpd|d	< n|
d	 |d	< | |d |d
 |d |d |d |d |d |d |d |d |d |d	 dœ¡ |d7 }q4W nd}x
|D ] }|| d | d kr|d7 }qì| d | d |  k r>| | d krÜn nšd |d |d ¡}	| |	¡}
|
d krtd|d	< n|
d	 |d	< | |d |d
 |d |d |d |d |d |d |d |d |d |d	 dœ¡ |d7 }|| | d krìP qìW ||fS )Nz˜SELECT project_id, BodySite, run_id, experiment_type, instrument_model, nr_reads_sequenced, phenotype, host_age, sex, BMI, country FROM mbodymap_samplesr   r   r   z‘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Ú
project_idÚrun_idÚ ÚQCStatusÚBodySiteÚexperiment_typeÚinstrument_modelÚnr_reads_sequencedÚ	phenotypeÚhost_ageÚsexÚBMIÚcountry)r)   r-   r*   r.   r/   r0   r1   r2   r3   r4   r5   Z	QC_Status)r   r   r   r   r   r   r   )r   r   Z
run_resultr   Úsql_runsÚrunsr!   r"   ÚitemÚ
sql_run_QCÚQCr   r   r   Ú
getRunDataQ   st    



0


r;   c          	   C   s’   t  ¡ }d | ¡}t| |¡d ƒ}d | ¡}t| |¡d ƒ}d | ¡}| |¡}d | ¡}| |¡}	| || |d |	|d |d ||d	œ}
|
S )
Nz™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Únumz™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=0uŒ   SELECT `OriginalÂ ProjectÂ description`, `NumberÂ ofÂ runs`, `RelatedÂ Publications` FROM `mbodymap_projects` WHERE `NCBIÂ ProjectÂ ID`="{}"z³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.uidu   NumberÂ ofÂ runsu   RelatedÂ Publicationsu   OriginalÂ ProjectÂ description)r   r   r   r   r   r   Zqc0Zqc1)r   r   r   r   r   r   )r   r   Zsql_qc1_runsZqc1_runsZsql_qc0_runsZqc0_runsÚsql_projectr#   r&   r'   r   r   r   r   ÚgetProjectDataIntro”   s*    


r>   c             C   sæ   t  ¡ }d | ¡}| |¡}tt|ƒƒ x¸|D ]°}d | |d ¡}| |¡}|d kr^d|d< n|d |d< |d dksŽ|d dksŽ|d d	kr d
|d< d|d< q.|d dks¸|d dkrÊd
|d< d|d< q.d|d< |d |d< q.W |S )Nz¢SELECT BodySite, run_id, experiment_type, instrument_model, nr_reads_sequenced, phenotype, host_age, sex, BMI, country FROM mbodymap_samples WHERE project_id="{}"z‘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_idr*   r+   r,   r-   ÚRectumÚColonÚCecumÚgutÚurlNamezLarge IntestineÚurlParamÚIleumÚDuodenumzSmall IntestineÚbodysite)r   r   r   r   Úprintr   r   )r   r   Zsql_all_runsZall_runsÚrunr9   r:   r   r   r   ÚgetProjectRunsData®   s*    



$

rJ   c             C   sp  t  ¡ }d | ¡}| |¡}d |d ¡}| |¡}d |d ¡}| |¡}|d krZd}n|d }d |d ¡}| |¡}	||d< |	|d	< d
 | ¡}
| |
¡}|d kr¸d|d< ||d< n6|d dkrÒt| ƒ|d< ni |d< |d |d< ||d< |d dks|d dks|d dkr*d|d< d|d< nB|d dksF|d dkrXd|d< d|d< nd|d< |d |d< |S )Nz¶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="{}"u®   SELECT `OriginalÂ ProjectÂ description` info, `NumberÂ ofÂ runs` total_runs, `RelatedÂ Publications` related_publication FROM mbodymap_projects WHERE `NCBIÂ ProjectÂ ID`="{}"r)   z‘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_idr   r   z³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.uidr   r   zDSELECT QCStatus FROM mbodymap_loaded_samples WHERE accession_id="{}"Úemptyr,   Úproject_infor   ÚplotDatar-   r?   r@   rA   rB   rC   zLarge IntestinerD   rE   rF   zSmall IntestinerG   )r   r   r   r   r   Ú
getRunPlot)ÚrunIdr   Zsql_runrI   Zsql_project_inforL   r$   r%   r&   r'   Zsql_QCStatusr,   r   r   r   ÚgetRunData2É   sH    








*

rP   c             C   sœ  t  ¡ }g }g }g }g }g }g }g }g }	g }
g }d}d}ddg}d | ¡}| |¡d }x¾|D ]´}d ||¡}| |¡}d ||¡}| |¡d }x°|D ]¨}d	 |d
 ¡}| |¡d }|dkr| ||d
 |d dœ¡ | |d ¡ | |¡ ||d 7 }qœ| ||d
 |d dœ¡ |
 |d ¡ | |¡ ||d 7 }qœW d ||¡}| |¡d }|dkrÂ| dd|dœ¡ | dd|| dœ¡ | |¡ | d¡ | || ¡ | d¡ q`| dd|dœ¡ | dd|| dœ¡ |
 |¡ | d¡ |
 || ¡ | d¡ q`W x0|D ](}| t|d dƒ¡ |	 |d ¡ q W x0|D ](}| t|d dƒ¡ | |d ¡ qRW ||	|||||
|||dœ
}|S )Nr   ÚspeciesÚgenusz?SELECT uid FROM mbodymap_loaded_samples WHERE accession_id="{}"ÚuidzÂ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 10z–SELECT SUM(relative_abundance) all_sum FROM `mbodymap_relative_species_abundances` where loaded_uid={} AND taxon_rank_level="{}" AND ncbi_taxon_id!=-1Zall_sumz6SELECT name FROM biosql_taxon_2 WHERE ncbi_taxon_id={}Úncbi_taxon_idÚnameÚrelative_abundance)rU   rT   rV   zˆSELECT relative_abundance FROM `mbodymap_relative_species_abundances` where loaded_uid={} AND taxon_rank_level="{}" AND ncbi_taxon_id=-1ÚUnknownéÿÿÿÿZOthersz.1f)
Ú	genusBarXÚ	genusBarYÚspeciesPiePlotValueÚspeciesPiePlotLabelÚspeciesBarXÚspeciesBarYÚgenusPiePlotValueÚgenusPiePlotLabelZgenusDownloadDataZspeciesDownloadData)r   r   r   r   r   r   )rO   r   ZspeciesBarPlotr]   r^   r[   r\   ZgenusBarPlotrY   rZ   r_   r`   Zspecies_tenZ	genus_tenZranksZsql_uidrS   ZrankZsql_tenZtenZsql_allÚallr8   Úsql_namerU   Zsql_unKnownZunKnownZgenusBarPlot_itemZspeciesBarPlot_itemrM   r   r   r   rN   ô   sª    














rN   N)	Úutilsr   r   r(   r;   r>   rJ   rP   rN   r   r   r   r   Ú<module>   s   5C+