3
C`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 j }t| |}g }g }g }g }dj| |}|j|}	x|	D ]}
dj|
d |}|j|d }dj|
d }|j|d }|j||
d t| d d	 d
j||
d | }|j|}t|
d }|d dkr|d7 }n|d7 }g }|dkrxHtdddD ]}|j| qW n&x$td|d dD ]}|j| q*W g }xN|D ]F}d}x$|D ]}|d |krV|d7 }qVW |j|t| d  qHW |
d dkr|jd|||dddiddid q@|jd|||dd q@W t	|dd dd}x0|D ](}|jt|d d |j|d  qW ||||d}|S )NzSELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10zSELECT 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_QC1d   )r   
prevalencezSELECT 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   >/var/www/html/mybodymap/server/service/taxonsDetailsService.py<lambda>L   s    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|    







rE   c          
   C   s4  t j }t| |}g }g }g }g }dj| |}|j|}	x|	D ]}
dj|
d |}|j|d }dj|
d }|j|d }|j||
d t| d d	 d
j||
d | }|j|}t|
d }|d dkr|d7 }n|d7 }g }|dkrxHtdddD ]}|j| qW n&x$td|d dD ]}|j| q*W g }xN|D ]F}d}x$|D ]}|d |krV|d7 }qVW |j|t| d  qHW |
d dkr|jd|||dddiddid q@|jd|||dd q@W t	|dd dd}x0|D ](}|jt|d d |j|d  qW ||||d}|S )NzSELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10zSELECT 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
   zSELECT 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      s    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.   r/   r!   r0   r1   r"   r#   r2   r3   r4   r5   r6   r7   r8   r9   r:   r;   r<   r=   r>   r?   r@   rA   rB   rC   rD   r   r   r   genusDetailsZ   s|    







rF   c             C   s   t j }g }dj| |}|j|}x|D ]}dj|d }|j|d }dj||d | }	|j|	}
g }x|
D ]}|j|d  qnW |d dkr|jd||d	d
dddiddid q(|jd||d	d
d q(W d|i}|S )NzSELECT 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   zSELECT 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   rI   rJ   boxPlot)r   r%   r&   r'   r(   r)   )r-   r.   r/   boxPlot_resultsr2   r3   r4   r7   r8   r9   r:   	box_valuerA   rD   r   r   r   getGenusBoxPlot   s@    



rO   c             C   s   t j }g }dj| |}|j|}x|D ]}dj|d }|j|d }dj||d | }	|j|	}
g }x|
D ]}|j|d  qnW |d dkr|jd||d	d
dddiddid q(|jd||d	d
d q(W d|i}|S )NzSELECT 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   zSELECT 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   rG   rH   g?r   r   )r   r   r   rI   rJ   rK   r   r   )r   r   r   rI   rJ   rL   )r   r%   r&   r'   r(   r)   )r-   r.   r/   rM   r2   r3   r4   r7   r8   r9   r:   rN   rA   rD   r   r   r   getSpeciesBoxPlot   s@    



rP   c             C   sJ   t j }dj| |}|j|}| |d t|d dj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   rQ   rR   )r   r%   r&   r(   r*   )r-   r.   r/   	sql_introintror   r   r   r   getSpeciesIntro  s    

rU   c             C   sJ   t j }dj| |}|j|}| |d t|d djt|d d}|S )NzmSELECT name, disease_num, loaded_uid_num FROM mbodymap_genus_to_site WHERE ncbi_taxon_id={} AND BodySite="{}"r   rQ   z{:,}rR   )r-   r   rQ   rR   )r   r%   r&   r(   r*   )r-   r.   r/   rS   rT   r   r   r   r   getGenusIntro  s    

rV   N)utilsr   service.taxonsPageServicer   r   rE   rF   rO   rP   rU   rV   r   r   r   r   <module>   s   UU//