B
    Pa|  ã               @   s@   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S )é    )Ú
sqlConnectNc          
   C   sæ   t  ¡ }d | ¡}| |¡}d d¡}| |¡}d d¡}| |¡}| |d t|d ƒd t|d ƒ¡d t|d	 ƒ¡d t|d
 ƒ¡dœ}|d k	r®d|d< t |d ¡|d< |d k	r¾d|d< d d¡}	| |	¡}
|
d k	râd|d< |S )NzÝSELECT name, COUNT(DISTINCT BodySite) site_num, COUNT(DISTINCT disease) disease_num, SUM(loaded_uid_num) total_runs, SUM(loaded_uid_num_QC1) valid_runs FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} GROUP BY namez<select attributes from cross_db_links where ncbi_taxon_id={}Úncbi_taxon_idz3select * from gmrepo_species where ncbi_taxon_id={}ÚnameÚsite_numz{:,}Ú
total_runsÚ
valid_runsÚdisease_num)r   r   r   r   r   r   TÚifMvpÚ
attributesÚmvpDataÚifGmrepoz0select * from hmdad_taxon where ncbi_taxon_id={}ÚifHmdad)r   ÚMySQLConnectionÚformatÚqueryOneÚintÚjsonÚloads)r   ÚconnectÚ	sql_introÚintroÚsql_mvpÚmvpÚ
sql_gmrepoÚgmrepoÚresultÚ	sql_hmdadÚhmdad© r   úVC:\Users\Administrator\Desktop\mbodymap\mbodymap-server\service\taxonsToSiteService.pyÚgetSpeciesIntro   s0    







r    c             C   s$   t  ¡ }d | ||¡}| |¡}|S )Nz_SELECT * FROM mbodymap_species_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {})r   r   r   Úquery)r   ÚpropNameÚ	propOrderr   Úsqlr   r   r   r   ÚgetSpeciesData"   s
    

r%   c          
   C   sæ   t  ¡ }d | ¡}| |¡}d d¡}| |¡}d d¡}| |¡}| |d t|d ƒd t|d ƒ¡d t|d	 ƒ¡d t|d
 ƒ¡dœ}|d k	r®d|d< t |d ¡|d< |d k	r¾d|d< d d¡}	| |	¡}
|
d k	râd|d< |S )NzÛSELECT name, COUNT(DISTINCT BodySite) site_num, COUNT(DISTINCT disease) disease_num, SUM(loaded_uid_num) total_runs, SUM(loaded_uid_num_QC1) valid_runs FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} GROUP BY namez<select attributes from cross_db_links where ncbi_taxon_id={}r   z1select * from gmrepo_genus where ncbi_taxon_id={}r   r   z{:,}r   r   r   )r   r   r   r   r   r   Tr	   r
   r   r   z0select * from hmdad_taxon where ncbi_taxon_id={}r   )r   r   r   r   r   r   r   )r   r   r   r   r   r   r   r   r   r   r   r   r   r   ÚgetGenusIntro*   s0    







r&   c             C   s$   t  ¡ }d | ||¡}| |¡}|S )Nz]SELECT * FROM mbodymap_genus_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {})r   r   r   r!   )r   r"   r#   r   r$   r   r   r   r   ÚgetGenusDataH   s
    

r'   c          
   C   s  t  ¡ }d | ¡}| |¡}|d kr,d}|S d | ¡}| |¡}d | ¡}| |¡}g }	d | ¡}
| |
¡}x–|D ]Ž}d | |d |d ¡}| |¡}g }g }x8|D ]0}| |d	 ¡ | |d
 d |d  d ¡ q¤W |	 |d |d |d |d ||dœ¡ qrW |||	dœ}|S d S )NzKselect * from curated_lefse_analysis_results where ncbi_taxon_id={} limit 1FzÈselect count(1) comparisons, count(distinct project_id) projects, count(distinct bodysite) bodysites, count(distinct phenotype2) unique_pheno from curated_lefse_analysis_results where ncbi_taxon_id={}zWSELECT * FROM `curated_lefse_analysis_results` WHERE ncbi_taxon_id={} ORDER BY lda DESCzƒselect distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name from curated_lefse_analysis_results WHERE ncbi_taxon_id={}zƒselect project_id, bodysite, lda from curated_lefse_analysis_results WHERE ncbi_taxon_id={} and phenotype1="{}" and phenotype2="{}"Ú
phenotype1Ú
phenotype2ZldaÚ
project_idz (Úbodysiteú)Úphenotype1_nameÚphenotype2_name)r(   r)   r-   r.   ÚxaxisÚyaxis)r   ÚtableZbar)r   r   r   r   r!   Úappend)r   r   Ú
sql_markerÚ	if_markerÚmarkerr   r   Z	sql_tabler1   ZbarDataZ	sql_phenoÚ
phenotypesZphenoZsql_phenoDataZ	phenoDatar/   r0   Úitemr   r   r   r   ÚgetMarkerSpeciesP   sJ    






"r8   )Úutilsr   r   r    r%   r&   r'   r8   r   r   r   r   Ú<module>   s   