B
    PaZj  ã               @   sp   d dl mZ d dlZdd„ Zdd„ Zdd„ Zd	d
„ Zdd„ Zdd„ Zdd„ Z	dd„ Z
dd„ Zdd„ Zdd„ ZdS )é    )Ú
sqlConnectNc        
   
   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)
ÚconnectÚsql_1ÚfirstÚ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}x¤|D ]œ}|dkr4d |d ¡}	| |	¡}
|
d krfd}n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 r4| | d krÊn n’d |d ¡}	| |	¡}
|
d kr`d}nd}d	 |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   é	   zJselect * from curated_lefse_analysis_results where project_id="{}" limit 1u   NCBIÂ ProjectÂ IDFTz‘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Ú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Úmarker)r   r   ÚqueryÚlenÚformatr   Úappend)ÚpageÚlimitÚproject_resultr   Úsql_projectsÚprojectsÚtotalÚiÚprojectÚ
sql_markerÚ	if_markerr   Úsql_processed_runs_numÚprocessed_runs_numÚsql_phenotypesÚ
phenotypesr   r   r   ÚgetProjectData   sx    







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/   r3   r0   r4   r5   r6   r7   r8   r9   r:   r;   Ú	QC_Status)r   r   r   r   r   r   r   )r    r!   Ú
run_resultr   Úsql_runsÚrunsr%   r&   ÚitemÚ
sql_run_QCÚQCr   r   r   Ú
getRunDataf   st    



0


rC   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,    

rF   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_idr0   r1   r2   r3   Ú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ÚrunrA   rB   r   r   r   ÚgetProjectRunsDataÄ   s*    



$

rR   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="{}"Úemptyr2   Úproject_infor   ÚplotDatar3   rG   rH   rI   rJ   rK   zLarge IntestinerL   rM   rN   zSmall IntestinerO   )r   r   r   r   r   Ú
getRunPlot)ÚrunIdr   Zsql_runrQ   Zsql_project_inforT   r*   r+   r,   r-   Zsql_QCStatusr2   r   r   r   ÚgetRunData2ß   sJ    









*

rX   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)r]   r\   r^   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   )rW   r   ZspeciesBarPlotre   rf   rc   rd   ZgenusBarPlotra   rb   rg   rh   Zspecies_tenZ	genus_tenZranksZsql_uidr[   ZrankZsql_tenZtenZsql_allÚallr@   Zsql_namer]   Zsql_unKnownZunKnownZgenusBarPlot_itemZspeciesBarPlot_itemrU   r   r   r   rV     s¬    














rV   c              C   sD   t  ¡ } d}|  |¡}d}|  |¡d }|d |d t|ƒdœ}|S )Nz{SELECT COUNT(DISTINCT project_id) project_num, COUNT(DISTINCT phenotype2) phenotype_num FROM curated_lefse_analysis_summaryzVselect sum(distinct project_sample_num) sample_num from curated_lefse_analysis_summaryÚ
sample_numr   Úphenotype_num)r   rk   rj   )r   r   r   r   )r   r	   Zresult_1r   rD   r   r   r   r   ÚmarkerDataIntron  s    
rl   c              C   s~  t  ¡ } g }d}|  |¡}x˜|D ]}d |d ¡}|  |¡}| ddddœ¡ g }xH|D ]@}d |d |d	 ¡}	|  |	¡}
| |d	 |d
 |
d dœ¡ qXW | |d |dœ¡ q W g }d}|  |¡}| ddddœ¡ xš|D ]’}|d	 dkrôd}nd |d	 ¡}|  |¡}g }xD|D ]<}d |d |d	 ¡}|  |¡}| |d |d dœ¡ qW | |d	 |d
 |dœ¡ qÞW ||dœS )Nz>select distinct project_id from curated_lefse_analysis_resultszeselect distinct phenotype2, phenotype2_name from curated_lefse_analysis_results where project_id="{}"r/   r   ÚD006262ZHealth)Ú
phenotype2Úphenotype2_namezpselect count(1) sample_num from curated_lefse_analysis_group_to_samples where project_id="{}" and phenotype="{}"rn   ro   rj   )r7   Úphenotype_namerj   )r'   ÚphenozOselect distinct phenotype2, phenotype2_name from curated_lefse_analysis_resultszYselect distinct project_id from curated_lefse_analysis_results where phenotype1="D006262"zTselect distinct project_id from curated_lefse_analysis_results where phenotype2="{}")r'   rj   )r7   rp   r'   )r"   Úphenotype_result)r   r   r   r   Úinsertr   r   )r   r"   rE   r'   r@   Ú	sql_phenorq   Zproject_phenotype_resultZ
pheno_itemZsql_pheno_resultZpheno_resultrr   Úsql_phenotyper7   Zphenotype_peoject_resultZproject_itemZsql_proj_resultZproj_resultr   r   r   ÚmarkerDataTable~  sR    









rv   c          
   C   s  t  ¡ }d | ¡}| |¡}|d kr,d}|S d | ¡}| |¡}d | ¡}| |¡d }d | ¡}	| |	¡d }
||d< |
|d< d	 | ¡}| |¡|d
< d | ¡}| |¡}g }xB|D ]:}d | |d |d ¡}| |¡}t|ƒ|d< | |¡ q¶W d | ¡}t| |¡ƒ}g }x:|D ]2}d | |d ¡}| |¡}||d< | |¡ qW g }xš|D ]’}d | |d |d ¡}| |¡}g }g }x:|D ]2}| |d ¡ | |d d |d  d ¡ qˆW | |d |d |d |d ||dœ¡ qVW ||||dœ}|S d S )NzJselect * from curated_lefse_analysis_results where project_id="{}" limit 1Fzœselect count(1) comparisons, count(distinct ncbi_taxon_id) taxa, count(distinct bodysite) bodysite from curated_lefse_analysis_results where project_id="{}"z‚select count(distinct ncbi_taxon_id) taxa from curated_lefse_analysis_results where project_id="{}" and taxon_rank_level="species"Útaxaz€select count(distinct ncbi_taxon_id) taxa from curated_lefse_analysis_results where project_id="{}" and taxon_rank_level="genus"rY   rZ   zú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Útablez„SELECT distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name FROM `curated_lefse_analysis_results` WHERE project_id="{}"z­select distinct ncbi_taxon_id, taxa_name, taxon_rank_level, bodysite, lda from `curated_lefse_analysis_results` where project_id="{}" and phenotype1="{}" and phenotype2="{}"Ú
phenotype1rn   zdselect distinct ncbi_taxon_id, taxa_name from `curated_lefse_analysis_results` where project_id="{}"zº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={}r\   rq   zselect ncbi_taxon_id, taxa_name, bodysite, lda from curated_lefse_analysis_results WHERE project_id="{}" and phenotype1="{}" and phenotype2="{}"ÚldaZ	taxa_namez (rO   ú)Úphenotype1_namero   )ry   rn   r|   ro   ÚxaxisÚyaxis)ÚintroÚphenotype_tableÚ
taxa_tableÚbar)r   r   r   r   r   ÚgetTaxonsDatabaser   )r   r   r(   r)   r   Ú	sql_intror   Zsql_intro_speciesZintro_speciesZsql_intro_genusZintro_genusZsql_introTablert   rq   r€   r@   Zsql_taxarw   r   Z	taxa_itemÚbarDataZbar_itemÚsql_phenoDataÚ	phenoDatar}   r~   r   r   r   r   ÚgetMarkerProjects³  s~    









$rˆ   c       	      C   s¤   t  ¡ }x–| D ]Ž}d |d ¡}| |¡}|d k	rLd|d< t |d ¡|d< d |d ¡}| |¡}|d k	rtd|d< d	 |d ¡}| |¡}|d k	rd|d
< qW | S )Nz<select attributes from cross_db_links where ncbi_taxon_id={}r\   TÚifMvpÚ
attributesÚmvpDataz3select * from gmrepo_species where ncbi_taxon_id={}ÚifGmrepoz0select * from hmdad_taxon where ncbi_taxon_id={}ÚifHmdad)r   r   r   r   ÚjsonÚloads)	Úarrr   r@   Úsql_mvpÚmvpÚ
sql_gmrepoÚgmrepoÚ	sql_hmdadÚhmdadr   r   r   rƒ     s     



rƒ   )Úutilsr   rŽ   r   r.   rC   rF   rR   rX   rV   rl   rv   rˆ   rƒ   r   r   r   r   Ú<module>   s   GC,c5R