3
FQa                 @   s(   d dl mZ d dlZdd Zdd ZdS )    )
sqlConnectNc             C   s    t j }dj| }|j|}|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 j }dj| |}|j|}x|D ]}dj|d }|j|}|d k	rbd|d< tj|d |d< dj|d }|j|}	|	d k	rd|d	< d
j|d }
|j|
}|d k	r$d|d< q$W dj| |}|j|d }t|}|dj|dj|d}dj| |}|j|}x|D ]}dj|d }|j|}|d k	rLd|d< tj|d |d< dj|d }|j|}	|	d k	rvd|d	< d
j|d }
|j|
}|d k	rd|d< qW dj| |}|j|d }t|}|dj|dj|d}dj|| }|j|d }dj|| }|j|}g }x|D ]}dj|d }|j|}dj|d }|j|} |j|d |rf|d nd|rv|d nd|r|d nd|r|d nd| d qW dj|| }!|j|!}"d j| |}#|j|#d! }$d"j| |}%|j|%}&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_idzSELECT 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_idzSELECT COUNT(run_id) run_id_num, COUNT(DISTINCT project_id) project_id_num FROM mbodymap_samples WHERE disease="{}" AND BodySite="{}"
run_id_numzSELECT 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   