B
    ŒâOa®  ã               @   s(   d dl mZ d dlZdd„ Zdd„ ZdS )é    )Ú
sqlConnectNc             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 | |¡}| |¡}x–|D ]Ž}d |d ¡}| |¡}|d k	rbd|d< t |d ¡|d< d |d ¡}| |¡}	|	d k	rŠd|d	< d
 |d ¡}
| |
¡}|d k	r$d|d< q$W d | |¡}| |¡d }t|ƒ}|d |¡d |¡dœ}d | |¡}| |¡}xž|D ]–}d |d ¡}| |¡}|d k	rLd|d< t |d ¡|d< d |d ¡}| |¡}	|	d k	rvd|d	< d
 |d ¡}
| |
¡}|d k	rd|d< qW d | |¡}| |¡d }t|ƒ}|d |¡d |¡dœ}d || ¡}| |¡d }d || ¡}| |¡}g }x’|D ]Š}d |d ¡}| |¡}d |d ¡}| |¡} | |d |rf|d nd|rv|d nd|r†|d nd|r–|d nd| dœ¡ qW d || ¡}!| |!¡}"d  | |¡}#| |#¡d! }$d" | |¡}%| |%¡}&t|&ƒ}'d#}(x$|&D ]})|)d$ d%kr |(d%7 }(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.01z<select attributes from cross_db_links where ncbi_taxon_id={}Zncbi_taxon_idTZifMvpÚ
attributesZmvpDataz3select * from gmrepo_species where ncbi_taxon_id={}ZifGmrepoz0select * from hmdad_taxon where ncbi_taxon_id={}ZifHmdadzYSELECT 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.01z1select * from gmrepo_genus where ncbi_taxon_id={}zWSELECT 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`="{}"Ú
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ÚNAÚ
sample_numÚruns_numÚpublication)Úprojectr   Ú
total_runsÚprocessed_runsr   Ú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_idz…SELECT COUNT(run_id) run_id_num, COUNT(DISTINCT project_id) project_id_num FROM mbodymap_samples WHERE disease="{}" AND BodySite="{}"Ú
run_id_numzžSELECT accession_id,QCStatus,uid FROM mbodymap_loaded_samples WHERE accession_id IN (SELECT run_id From mbodymap_samples WHERE disease="{}" AND BodySite="{}")r   ÚQCStatusé   )r   Úprocessed_numÚvalid)Úspecies_resultÚgenus_resultÚproject_numÚprojects_phenotype_tblÚsamples_phenotype_tblÚrouter)	r   r   r   Úqueryr   ÚjsonÚloadsÚlenÚappend)+r   Úsiter   Zsql_species_tblr   Zspecies_itemZsql_mvpZmvpZ
sql_gmrepoZgmrepoZ	sql_hmdadZhmdadZsql_species_totalr   r   r#   Zsql_genus_tblr   Z
genus_itemZsql_genus_totalr   r   r$   Zsql_project_numr%   Úsql_projectÚprojectsr&   r   Úsql_project_tblÚproject_tblÚsql_phenotypesÚ
phenotypesÚsql_sum_tblr'   Úsql_runsr   Úsql_loaded_runsÚloaded_runsr!   r"   Ú
loaded_runr(   r	   r	   r
   ÚtblData   s²    





















r:   )Úutilsr   r*   r   r:   r	   r	   r	   r
   Ú<module>   s   