3
C`C                 @   s    d dl mZ dd Zdd ZdS )    )
sqlConnectc             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|}dj| |}|j|d }t|}|dj|dj|d}dj| |}	|j|	}
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 |r.|d nd|r>|d nd|rN|d nd|r^|d nd|d qW dj|| }|j|}|||||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_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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   