B
    |ùÓ`C  ã               @   s    d dl mZ dd„ Zdd„ ZdS )é    )Ú
sqlConnectc             C   s    t  ¡ }d | ¡}| |¡}|S )Nz)SELECT note FROM mesh_data WHERE uid="{}")r   ÚMySQLConnectionÚformatÚqueryOne)Ú	phenotypeÚconnectÚ	sql_introZphenotype_intro© r	   úWC:\Users\Administrator\Desktop\mbodymap\mbodymap-server\service\phenotypePageService.pyÚ	introData   s    

r   c          
   C   s”  t  ¡ }d | |¡}| |¡}d | |¡}| |¡d }t|ƒ}|d |¡d |¡dœ}d | |¡}	| |	¡}
d | |¡}| |¡d }t|
ƒ}|
d |¡d |¡dœ}d	 || ¡}| |¡d }d
 || ¡}| |¡}g }x|D ]ˆ}d |d ¡}| |¡}d |d ¡}| |¡}| |d |r.|d nd|r>|d nd|rN|d nd|r^|d nd|dœ¡ qâW d || ¡}| |¡}|||||dœS )Na  SELECT ncbi_taxon_id, name, loaded_uid_num, relative_abundance_sum, relative_abundance_std, relative_abundance_avg, relative_abundance_med FROM mbodymap_species_to_samples WHERE disease="{}" AND BodySite="{}" AND loaded_uid_num>1 AND relative_abundance_med>0.0001zYSELECT COUNT(1) num FROM mbodymap_species_to_samples WHERE disease="{}" AND BodySite="{}"Únumz{:,})Úspecies_phenotype_tblÚspecies_totalÚspecies_filter_totala  SELECT ncbi_taxon_id, name, loaded_uid_num, relative_abundance_sum, relative_abundance_std, relative_abundance_avg, relative_abundance_med FROM mbodymap_genus_to_samples WHERE disease="{}" AND BodySite="{}" AND loaded_uid_num>1 AND relative_abundance_med>0.0001zWSELECT COUNT(1) num FROM mbodymap_genus_to_samples WHERE disease="{}" AND BodySite="{}")Úgenus_phenotype_tblÚgenus_totalÚgenus_filter_totalz`SELECT COUNT(DISTINCT project_id) num FROM mbodymap_samples WHERE BodySite="{}" AND disease="{}"zUSELECT DISTINCT project_id FROM mbodymap_samples WHERE BodySite="{}" AND disease="{}"uÏ   SELECT `OriginalÂ ProjectÂ description` description, `NumberÂ ofÂ samples` sample_num, `NumberÂ ofÂ runs` runs_num, `RelatedÂ Publications` publication FROM `mbodymap_projects` WHERE `NCBIÂ ProjectÂ ID`="{}"Z
project_idz³SELECT DISTINCT t1.disease, t3.term FROM mbodymap_samples t1, mbodymap_loaded_samples t2, mesh_data t3 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id AND t1.disease=t3.uidÚdescriptionÚNAZ
sample_numÚruns_numÚpublication)Úprojectr   Z
total_runsZprocessed_runsr   Zrelated_phenotypea   SELECT t1.project_id, t1.run_id, t1.experiment_type, t1.instrument_model, t1.BodySite_info, t1.country, t1.sex, t1.host_age, t2.QCStatus FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE BodySite="{}" AND disease="{}" AND t1.run_id=t2.accession_id)Úspecies_resultÚgenus_resultÚproject_numÚprojects_phenotype_tblÚsamples_phenotype_tbl)r   r   r   Úqueryr   ÚlenÚappend)r   Úsiter   Zsql_species_tblr   Zsql_species_totalr   r   r   Zsql_genus_tblr   Zsql_genus_totalr   r   r   Zsql_project_numr   Zsql_projectZprojectsr   r   Zsql_project_tblZproject_tblZsql_phenotypesZ
phenotypesZsql_sum_tblr   r	   r	   r
   ÚtblData   s^    








r!   N)Úutilsr   r   r!   r	   r	   r	   r
   Ú<module>   s   