o
    ‹FQa®  ã                   @   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	   ú>/var/www/html/mybodymap/server/service/phenotypePageService.pyÚ	introData   s   

r   c           +   
   C   s&  t  ¡ }d | |¡}| |¡}|D ]G}d |d ¡}| |¡}|d ur0d|d< t |d ¡|d< d |d ¡}| |¡}	|	d urDd|d	< d
 |d ¡}
| |
¡}|d urXd|d< qd | |¡}| |¡d }t|ƒ}|d |¡d |¡dœ}d | |¡}| |¡}|D ]G}d |d ¡}| |¡}|d ur¢d|d< t |d ¡|d< d |d ¡}| |¡}	|	d ur¶d|d	< d
 |d ¡}
| |
¡}|d urÊd|d< qƒd | |¡}| |¡d }t|ƒ}|d |¡d |¡dœ}d || ¡}| |¡d }d || ¡}| |¡}g }|D ]E}d |d ¡}| |¡}d |d ¡}| |¡} | |d |r*|d nd|r2|d nd|r:|d nd|rB|d nd| dœ¡ qd || ¡}!| |!¡}"d  | |¡}#| |#¡d! }$d" | |¡}%| |%¡}&t|&ƒ}'d#}(|&D ]})|)d$ d%kr‚|(d%7 }(qu|$|'|(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    