B
    Ioa8  ã               @   s0   d dl mZ dd„ Zdd„ Zdd„ Zdd	„ Zd
S )é    )Ú
sqlConnectc          	   C   sê   t  ¡ }d |¡}| |¡d }d ||¡}| |¡d }d ||¡}| |¡d }	d ||¡}
| |
¡d }d	 ||| ¡}| |¡d
 }d | ¡}| |¡d }d |¡}| |¡d }|||d |¡d |	¡d |¡d |¡dœ}|S )Nz)SELECT note FROM mesh_data WHERE uid="{}"ÚnotezTSELECT COUNT(1) run_total FROM mbodymap_samples WHERE disease="{}" AND BodySite="{}"Ú	run_totalz•SELECT COUNT(1) run_processed FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_idÚrun_processedz£SELECT COUNT(1) run_valid FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1Ú	run_validztSELECT loaded_uid_num_QC1 FROM mbodymap_species_to_samples WHERE disease="{}" AND BodySite="{}" AND ncbi_taxon_id={}Úloaded_uid_num_QC1z6select name from biosql_taxon_2 where ncbi_taxon_id={}Únamez)select term from mesh_data where uid="{}"Útermz{:,})Úspecies_nameÚdisease_nameÚdesc_diseaseÚintro_disease_totalÚintro_disease_processedÚintro_disease_validÚintro_species_inDisease_valid)r   ÚMySQLConnectionÚformatÚqueryOne)Úncbi_taxon_idÚsiteÚdiseaseÚconnectÚsql_desc_diseaser   Úsql_intro_disease_totalr   Úsql_intro_disease_processedr   Úsql_intro_disease_validr   Ú!sql_intro_species_inDisease_validr   Úsql_species_namer
   Úsql_disease_namer   Úresult© r    úYC:\Users\Administrator\Desktop\mbodymap\mbodymap-server\service\taxonsFinalPageService.pyÚgetSpeciesIntroData   s6    



r"   c             C   s`  t  ¡ }|dkr|g}nd|g}g }g }d ||| ¡}| |¡d }t|ƒdk r^g g dœ}	nþxð|D ]æ}
d |
|¡}| |¡d }d |
¡}| |¡d }d	 | ||
¡}| |¡}|d krÄd
}n| |¡d }d ||
| ¡}| |¡}t|ƒ}|d d
kr
|d7 }n|d7 }g }|dkrDxHtdddƒD ]}| |¡ q.W n&x$td|d dƒD ]}| |¡ qVW g }g }x\|D ]T}d
}x2|D ]*}| |d ¡ |d |kr†|d7 }q†W | |t|ƒ d ¡ qxW |
dkr"| d|||dddiddidœ¡ | d||dddddiddidœ¡ qf| d|||ddœ¡ | d||dddœ¡ qfW ||dœ}	|	S )NÚD006262zíSELECT COUNT(1) total 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={}Útotalé
   )ÚlinePlotÚboxPlotzŸ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=1z)SELECT term FROM mesh_data WHERE uid="{}"r	   zxSELECT relative_abundance_max FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND disease="{}"r   Úrelative_abundance_maxz÷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={}é   é   éd   ée   ÚraÚscatterzlines+markersÚcolorÚgreen)ÚtypeÚxÚyr   ÚmodeÚmarkerÚlineÚboxÚOutliersg      à?)r1   r2   r   Ú	boxpointsÚwidthÚ	fillcolorr5   r6   )r1   r2   r3   r   r4   )r1   r2   r   r9   r:   )r   r   r   r   ÚintÚqueryÚrangeÚappend)r   r   r   r   ÚdiseaseListÚlinePlot_resultsÚboxPlot_resultsÚsql_runs_totalÚ
runs_totalÚresultsÚitemÚ"sql_total_disease_of_taxon_in_siteÚtotal_disease_of_taxon_in_siter   r   Ú
sql_max_raÚmax_raÚsql_all_reiative_abundanceÚall_reiative_abundanceÚmax_relative_abundanceÚroundXÚiÚroundYÚ	box_valueÚruleÚ
rule_valueÚrelative_abundancer    r    r!   ÚgetSpeciesPlotData'   sž    











rU   c          	   C   sê   t  ¡ }d |¡}| |¡d }d ||¡}| |¡d }d ||¡}| |¡d }	d ||¡}
| |
¡d }d	 ||| ¡}| |¡d
 }d | ¡}| |¡d }d |¡}| |¡d }|||d |¡d |	¡d |¡d |¡dœ}|S )Nz)SELECT note FROM mesh_data WHERE uid="{}"r   zTSELECT COUNT(1) run_total FROM mbodymap_samples WHERE disease="{}" AND BodySite="{}"r   z•SELECT COUNT(1) run_processed FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_idr   z£SELECT COUNT(1) run_valid FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1r   zrSELECT loaded_uid_num_QC1 FROM mbodymap_genus_to_samples WHERE disease="{}" AND BodySite="{}" AND ncbi_taxon_id={}r   z6select name from biosql_taxon_2 where ncbi_taxon_id={}r   z)select term from mesh_data where uid="{}"r	   z{:,})r
   r   r   r   r   r   r   )r   r   r   r   )r   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r
   r   r   r   r    r    r!   ÚgetGenusIntroData“   s6    



rV   c             C   sh  t  ¡ }|dkr|g}nd|g}g }g }d ||| ¡}| |¡d }t|ƒdk r^g g dœ}	nxø|D ]î}
d |
|¡}| |¡d }d |
¡}| |¡d }d	 | ||
¡}t|ƒ | |¡}|d krÌd
}n| |¡d }d ||
| ¡}| |¡}t|ƒ}|d d
kr|d7 }n|d7 }g }|dkrLxHtdddƒD ]}| |¡ q6W n&x$td|d dƒD ]}| |¡ q^W g }g }x\|D ]T}d
}x2|D ]*}| |d ¡ |d |krŽ|d7 }qŽW | |t|ƒ d ¡ q€W |
dkr*| d|||dddiddidœ¡ | d||dddddiddidœ¡ qf| d|||ddœ¡ | d||dddœ¡ qfW ||dœ}	|	S )Nr#   zíSELECT COUNT(1) total 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'   zŸ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=1z)SELECT term FROM mesh_data WHERE uid="{}"r	   zvSELECT relative_abundance_max FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND disease="{}"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.   zlines+markersr/   r0   )r1   r2   r3   r   r4   r5   r6   r7   r8   g      à?)r1   r2   r   r9   r:   r;   r5   r6   )r1   r2   r3   r   r4   )r1   r2   r   r9   r:   )	r   r   r   r   r<   Úprintr=   r>   r?   )r   r   r   r   r@   rA   rB   rC   rD   rE   rF   rG   rH   r   r   rI   rJ   rK   rL   rM   rN   rO   rP   rQ   rR   rS   rT   r    r    r!   ÚgetGenusPlotData·   s     











rX   N)Úutilsr   r"   rU   rV   rX   r    r    r    r!   Ú<module>   s   #l$