B
    SŲ`V2  ć               @   sP   d dl mZ d dlmZmZ dd Zdd Zdd Zd	d
 Zdd Z	dd Z
dS )é    )Ś
sqlConnect)ŚgetSpeciesPhenotypeŚgetGenusPhenotypec             C   s4  t  ” }t| |}g }g }g }g }d | |”}| |”}	x¢|	D ]}
d |
d |”}| |”d }d |
d ”}| |”d }| ||
d t| d d	” d
 ||
d | ”}| |”}t|
d }|d dkrŽ|d7 }n|d7 }g }|dkrxHtdddD ]}| |” qW n&x$td|d dD ]}| |” q*W g }xN|D ]F}d}x$|D ]}|d |krV|d7 }qVW | |t| d ” qHW |
d dkrÄ| d|||dddiddid” q@| d|||dd” q@W t	|dd dd}x0|D ](}| t|d d” | |d ” qöW ||||d}|S )NzSELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10zSELECT COUNT(1) total FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1ŚdiseaseŚtotalz)SELECT term FROM mesh_data WHERE uid="{}"ŚtermŚloaded_uid_num_QC1éd   )r   Ś
prevalencez÷SELECT t1.relative_abundance ra FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}Śrelative_abundance_maxé   r   é   ée   ŚraŚD006262Śscatterzlines+markersŚcolorŚgreen)ŚtypeŚxŚyŚnameŚmodeŚmarkerŚline)r   r   r   r   r   c             S   s   | d S )Nr
   © )Śresultr   r   śWC:\Users\Administrator\Desktop\mbodymap\mbodymap-server\service\taxonsDetailsService.pyŚ<lambda>L   ó    z speciesDetails.<locals>.<lambda>T)ŚkeyŚreverser
   z.1f)ŚspeciesTableResŚxPlotŚyPlotŚlinePlot)
r   ŚMySQLConnectionr   ŚformatŚqueryŚqueryOneŚappendŚintŚrangeŚsorted)Śncbi_taxon_idŚsiteŚconnectr"   ŚbarPlot_resultsŚlinePlot_resultsr#   r$   Śsql_barPlotŚ
phenotypesŚ	phenotypeŚ"sql_total_disease_of_taxon_in_siteŚtotal_disease_of_taxon_in_siteŚsql_disease_nameŚdisease_nameŚsql_all_reiative_abundanceŚall_reiative_abundanceŚmax_relative_abundanceŚroundXŚiŚroundYŚruleŚ
rule_valueŚrelative_abundanceŚresults_sortedŚresults_sorted_itemŚresultsr   r   r   ŚspeciesDetails   s|    







rF   c             C   s4  t  ” }t| |}g }g }g }g }d | |”}| |”}	x¢|	D ]}
d |
d |”}| |”d }d |
d ”}| |”d }| ||
d t| d d	” d
 ||
d | ”}| |”}t|
d }|d dkrŽ|d7 }n|d7 }g }|dkrxHtdddD ]}| |” qW n&x$td|d dD ]}| |” q*W g }xN|D ]F}d}x$|D ]}|d |krV|d7 }qVW | |t| d ” qHW |
d dkrÄ| d|||dddiddid” q@| d|||dd” q@W t	|dd dd}x0|D ](}| t|d d” | |d ” qöW ||||d}|S )NzSELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10zSELECT COUNT(1) total FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1r   r   z)SELECT term FROM mesh_data WHERE uid="{}"r   r   r	   )r   r
   z÷SELECT t1.relative_abundance ra FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}r   r   r   r   r   r   r   r   zlines+markersr   r   )r   r   r   r   r   r   r   )r   r   r   r   r   c             S   s   | d S )Nr
   r   )r   r   r   r   r   ”   r   zgenusDetails.<locals>.<lambda>T)r    r!   r
   z.1f)r"   r#   r$   r%   )
r   r&   r   r'   r(   r)   r*   r+   r,   r-   )r.   r/   r0   r"   r1   r2   r#   r$   r3   r4   r5   r6   r7   r8   r9   r:   r;   r<   r=   r>   r?   r@   rA   rB   rC   rD   rE   r   r   r   ŚgenusDetailsZ   s|    







rG   c             C   sÜ   t  ” }g }d | |”}| |”}x¬|D ]¤}d |d ”}| |”d }d ||d | ”}	| |	”}
g }x|
D ]}| |d ” qnW |d dkr¶| d||d	d
dddiddid” q(| d||d	d
d” q(W d|i}|S )NzSELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10z)SELECT term FROM mesh_data WHERE uid="{}"r   r   z÷SELECT t1.relative_abundance ra FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}r   r   ŚboxŚOutliersg      ą?r   r   )r   r   r   Ś	boxpointsŚwidthŚ	fillcolorr   r   )r   r   r   rJ   rK   ŚboxPlot)r   r&   r'   r(   r)   r*   )r.   r/   r0   ŚboxPlot_resultsr3   r4   r5   r8   r9   r:   r;   Ś	box_valuerB   rE   r   r   r   ŚgetGenusBoxPlotÆ   s@    



rP   c             C   sÜ   t  ” }g }d | |”}| |”}x¬|D ]¤}d |d ”}| |”d }d ||d | ”}	| |	”}
g }x|
D ]}| |d ” qnW |d dkr¶| d||d	d
dddiddid” q(| d||d	d
d” q(W d|i}|S )NzSELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10z)SELECT term FROM mesh_data WHERE uid="{}"r   r   z÷SELECT t1.relative_abundance ra FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}r   r   rH   rI   g¹?r   r   )r   r   r   rJ   rK   rL   r   r   )r   r   r   rJ   rK   rM   )r   r&   r'   r(   r)   r*   )r.   r/   r0   rN   r3   r4   r5   r8   r9   r:   r;   rO   rB   rE   r   r   r   ŚgetSpeciesBoxPlotŽ   s@    



rQ   c             C   sJ   t  ” }d | |”}| |”}| |d t|d d t|d ”d}|S )NzoSELECT name, disease_num, loaded_uid_num FROM mbodymap_species_to_site WHERE ncbi_taxon_id={} AND BodySite="{}"r   Śdisease_numz{:,}Śloaded_uid_num)r.   r   rR   rS   )r   r&   r'   r)   r+   )r.   r/   r0   Ś	sql_introŚintror   r   r   r   ŚgetSpeciesIntro  s    

rV   c             C   sJ   t  ” }d | |”}| |”}| |d t|d d t|d ”d}|S )NzmSELECT name, disease_num, loaded_uid_num FROM mbodymap_genus_to_site WHERE ncbi_taxon_id={} AND BodySite="{}"r   rR   z{:,}rS   )r.   r   rR   rS   )r   r&   r'   r)   r+   )r.   r/   r0   rT   rU   r   r   r   r   ŚgetGenusIntro  s    

rW   N)Śutilsr   Śservice.taxonsPageServicer   r   rF   rG   rP   rQ   rV   rW   r   r   r   r   Ś<module>   s   UU//