B
    ‹+Ô`¬4  ã               @   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œ}|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_QC1z{:,})Ú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   Úresult© r   úYC:\Users\Administrator\Desktop\mbodymap\mbodymap-server\service\taxonsFinalPageService.pyÚgetSpeciesIntroData   s*    

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="{}"ÚtermzxSELECT 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ÚyÚnameÚmodeÚmarkerÚlineÚboxÚOutliersgš™™™™™¹?)r,   r-   r/   Ú	boxpointsÚwidthÚ	fillcolorr1   r2   )r,   r-   r.   r/   r0   )r,   r-   r/   r5   r6   )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_siteÚsql_disease_nameÚdisease_nameÚ
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ž    











rS   c             C   s¶   t  ¡ }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   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   ÚgetGenusIntroData‹   s*    

rT   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*   r+   )r,   r-   r.   r/   r0   r1   r2   r3   r4   gš™™™™™¹?)r,   r-   r/   r5   r6   r7   r1   r2   )r,   r-   r.   r/   r0   )r,   r-   r/   r5   r6   )	r   r   r   r   r8   Úprintr9   r:   r;   )r   r   r   r   r<   r=   r>   r?   r@   rA   rB   rC   rD   rE   rF   rG   rH   rI   rJ   rK   rL   rM   rN   rO   rP   rQ   rR   r   r   r   ÚgetGenusPlotData§   s     











rV   N)Úutilsr   r   rS   rT   rV   r   r   r   r   Ú<module>   s   l