o
    FQaYH                     @   s`   d dl mZ d dlmZ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S )    )
sqlConnect)getSpeciesPhenotypegetGenusPhenotypeNc                 C     t  }t| |}g }g }g }g }d| |}||}	|	D ]}
d|
d |}||d }d|
d }||d }|||
d t| d d	 d
||
d | }||}t|
d }|d dkrl|d7 }n|d7 }g }|dkrtdddD ]}|| q|ntd|d dD ]}|| qg }|D ]}d}|D ]}|d |kr|d7 }q||t| d  q|
d dkr|d|||dddiddid q|d|||dd qt	|dd dd}|D ]}|t|d d ||d  q||||d}|S )NSELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10SELECT 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total)SELECT term FROM mesh_data WHERE uid="{}"termloaded_uid_num_QC1d   r   
prevalenceSELECT 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scatterlines+markerscolorgreentypexynamemodemarkerliner   r   r   r   r    c                 S      | d S Nr    resultr&   r&   >/var/www/html/mybodymap/server/service/taxonsDetailsService.py<lambda>M       z speciesDetails.<locals>.<lambda>Tkeyreverser   .1fspeciesTableResxPlotyPlotZlinePlot)
r   MySQLConnectionr   formatqueryqueryOneappendintrangesortedncbi_taxon_idsiteconnectr1   ZbarPlot_resultsZlinePlot_resultsr2   r3   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_abundanceZmax_relative_abundanceZroundXiZroundYruleZ
rule_valuerelative_abundanceZresults_sortedZresults_sorted_itemresultsr&   r&   r)   speciesDetails      






rM   c                 C   r   )NSELECT disease, loaded_uid_num_QC1, relative_abundance_max FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND loaded_uid_num_QC1>10r   r   r	   r
   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r#   c                 S   r$   r%   r&   r'   r&   r&   r)   r*      r+   zgenusDetails.<locals>.<lambda>Tr,   r   r/   r0   )
r   r4   r   r5   r6   r7   r8   r9   r:   r;   r<   r&   r&   r)   genusDetails[   rN   rP   c                 C      t  }g }d| |}||}|D ]P}d|d }||d }d||d | }	||	}
g }|
D ]	}||d  q5|d dkrX|d||d	d
dddiddid q|d||d	d
d qd|i}|S )NrO   r
   r   r   r   r   r   boxOutliers      ?r   r   r   r   r   	boxpointswidthZ	fillcolorr!   r"   r   r   r   rV   rW   boxPlotr   r4   r5   r6   r7   r8   r=   r>   r?   ZboxPlot_resultsr@   rA   rB   rE   rF   rG   rH   Z	box_valuerK   rL   r&   r&   r)   getGenusBoxPlot   N   




r\   c                 C   rQ   )Nr   r
   r   r   r   r   r   rR   rS   rT   r   r   rU   rX   rY   rZ   r[   r&   r&   r)   getSpeciesBoxPlot   r]   r^   c                 C      t  }d| |}||}dd}||}dd}||}dd}	||	}
| |d t|d dt|d	 |d
 d}|d urUd|d< t|d |d< |d ur]d|d< |
d ured|d< |S )Nz|SELECT name, disease_num, loaded_uid_num, have_health FROM mbodymap_species_to_site WHERE ncbi_taxon_id={} AND BodySite="{}"<select attributes from cross_db_links where ncbi_taxon_id={}r=   z3select * from gmrepo_species where ncbi_taxon_id={}0select * from hmdad_taxon where ncbi_taxon_id={}r   disease_num{:,}loaded_uid_numhave_healthr=   r   rb   rd   re   TifMvp
attributesmvpDataifGmrepoifHmdadr   r4   r5   r7   r9   jsonloadsr=   r>   r?   	sql_introintrosql_mvpmvp
sql_gmrepogmrepo	sql_hmdadhmdadr(   r&   r&   r)   getSpeciesIntro  s2   







rx   c                 C   r_   )NzzSELECT name, disease_num, loaded_uid_num, have_health FROM mbodymap_genus_to_site WHERE ncbi_taxon_id={} AND BodySite="{}"r`   r=   z1select * from gmrepo_genus where ncbi_taxon_id={}ra   r   rb   rc   rd   re   rf   Trg   rh   ri   rj   rk   rl   ro   r&   r&   r)   getGenusIntro5  s2   







ry   c                 C   s   t  }d| }||}|d u rd}|S d| |}||}|d u r+ddi}|S d| |}	||	}
d| |}||}d|
|d}|S )	NzKselect * from curated_lefse_analysis_results where ncbi_taxon_id={} limit 1Fz]select * from curated_lefse_analysis_results where ncbi_taxon_id={} and bodysite="{}" limit 1hasMarkerDatazselect 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={} and bodysite="{}"ziSELECT * FROM `curated_lefse_analysis_results` WHERE ncbi_taxon_id={} and bodysite="{}" ORDER BY lda DESCT)rz   rq   table)r   r4   r5   r7   r6   )r=   r>   r?   
sql_marker	if_markerr!   Zsql_marker_dataZif_marker_datar(   rp   rq   	sql_tabler{   r&   r&   r)   getMarkerSpeciesZ  s:   



r   )utilsr   service.taxonsPageServicer   r   rm   rM   rP   r\   r^   rx   ry   r   r&   r&   r&   r)   <module>   s    UU//'%