3
C`q1                 @   sX   d dl mZmZ d dlmZ dd Zdd Zdd Zd	d
 Zdd Z	dd Z
dd ZdS )    )getSpeciesDatagetGenusData)
sqlConnectc          	   C   s  t j }d}g }	x<| D ]4}
|
dkr,|d7 }q|
dkr>|d7 }q|d|
 7 }qW |jd}dj|||}|j|}t|}|dkrd}x|D ]B}
|dkr|	j|
d |
d |
d djt|
d d |d7 }qW nd}x|D ]}
||d | d kr|d7 } q|d | d |  k o*|| d kn  rh|	j|
d |
d |
d djt|
d d |d7 }||| d k rP  qW t|	||}||fS )N zLarge intestinez'Rectum',z'Colon'z'Cecum'zSmall intestinez'Ileum'z
'Duodenum'z'%s'zSELECT DISTINCT ncbi_taxon_id, name, site_num, SUM(loaded_uid_num) sample_num FROM mbodymap_species_to_site WHERE BodySite IN ({}) AND loaded_uid_num>1 AND med>=0.0001 GROUP BY ncbi_taxon_id, name, site_num ORDER BY {} {}   r   	   namencbi_taxon_idsite_numz{:,}
sample_num)r	   r
   r   r   z	'Rectum',z'Rectum','Colon'z'Rectum','Colon',z'Rectum','Colon','Cecum'z'Rectum','Colon','Cecum',z'Ileum',z'Ileum','Duodenum'z'Ileum','Duodenum',z'%s',)	r   MySQLConnectionrstripformatquerylenappendintgetTaxonsPageSpeciseData)checkedSitespagelimitpropName	propOrder
secondNamesecondOrderconnectsearchspecies_resultitemsql_speciesspeciestotaliresult r%   ;/var/www/html/mybodymap/server/service/taxonsPageService.py
getSpecies<   sP    







0r'   c             C   s>   t j }x0| D ](}dj|d ||}|j|}||d< qW | S )Nz_SELECT * FROM mbodymap_species_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}r
   secondTableData)r   r   r   r   )arrr   r   r   r   sqlr$   r%   r%   r&   r   k   s    

r   c             C   sF   t j }x8| D ]0}dj|d ||}t| |j|}||d< qW | S )Nz]SELECT * FROM mbodymap_genus_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}r
   r(   )r   r   r   printr   )r)   r   r   r   r   r*   r$   r%   r%   r&   getTaxonsPageGenusDatau   s    

r,   c              C   s   t j } d}t| j|d }d}| j|d }d}| j|d }d}| j|d }d}	| j|	d }
d	j|d	j|d	j|d	j|d	j|
d
}|S )Nz9SELECT SUM(loaded_run_num) sample_num FROM mbodymap_intror   zNSELECT COUNT(DISTINCT ncbi_taxon_id) species_num FROM mbodymap_species_to_sitespecies_numzJSELECT COUNT(DISTINCT ncbi_taxon_id) genus_num FROM mbodymap_genus_to_site	genus_numzuSELECT COUNT(DISTINCT ncbi_taxon_id) species_num FROM mbodymap_species_to_site WHERE loaded_uid_num>1 AND med>=0.0001zqSELECT COUNT(DISTINCT ncbi_taxon_id) genus_num FROM mbodymap_genus_to_site WHERE loaded_uid_num>1 AND med>=0.0001z{:,})r   r-   r.   filter_species_numfilter_genus_num)r   r   r   queryOner   )r   Zsql_sample_numr   Zsql_species_numr-   Zsql_genus_numr.   Zsql_filter_species_numr/   Zsql_filter_genus_numr0   species_intror%   r%   r&   getSpeciesIntro   s"    r3   c          
   C   s   t j }g }dj| |}|j|}xx|D ]p}dj|d |}|j|d }dj|d }	|j|	d }
|j|
|d |d |d |d	 |d
 t|d q(W |S )NzSELECT disease, loaded_uid_num_QC1, relative_abundance_std, relative_abundance_avg, relative_abundance_med FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}"zSELECT COUNT(1) total FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1diseaser"   z)SELECT term FROM mesh_data WHERE uid="{}"termloaded_uid_num_QC1relative_abundance_stdrelative_abundance_avgrelative_abundance_med)	phenotypemesh_idloaded_uid_numstdavgmedr"   )r   r   r   r   r1   r   r   )r
   siter   r$   sql_phenotype
phenotypesr:   "sql_total_disease_of_taxon_in_sitetotal_disease_of_taxon_in_sitesql_disease_namer	   r%   r%   r&   getSpeciesPhenotype   s(    

rF   c          	   C   s  t j }d}g }	x<| D ]4}
|
dkr,|d7 }q|
dkr>|d7 }q|d|
 7 }qW |jd}dj|||}|j|}t|}|dkrd}x|D ]B}
|dkr|	j|
d |
d |
d djt|
d d |d7 }qW nd}x|D ]}
||d | d kr|d7 } q|d | d |  k o*|| d kn  rh|	j|
d |
d |
d djt|
d d |d7 }||| d k rP  qW t|	||}||fS )Nr   zLarge intestinez'Rectum'r   z'Colon'z'Cecum'zSmall intestinez'Ileum'z
'Duodenum'z'%s'zSELECT DISTINCT ncbi_taxon_id, name, site_num, SUM(loaded_uid_num) sample_num FROM mbodymap_genus_to_site WHERE BodySite IN ({}) AND loaded_uid_num>1 AND med>=0.0001 GROUP BY ncbi_taxon_id, name, site_num ORDER BY {} {}r   r   r   r	   r
   r   z{:,}r   )r	   r
   r   r   z	'Rectum',z'Rectum','Colon'z'Rectum','Colon',z'Rectum','Colon','Cecum'z'Rectum','Colon','Cecum',z'Ileum',z'Ileum','Duodenum'z'Ileum','Duodenum',z'%s',)	r   r   r   r   r   r   r   r   r,   )r   r   r   r   r   r   r   r   r   r   r   r    r!   r"   r#   r$   r%   r%   r&   getGenus   sP    







0rG   c          
   C   s   t j }g }dj| |}t| |j|}xx|D ]p}dj|d |}|j|d }dj|d }	|j|	d }
|j|
|d |d |d |d	 |d
 t|d q0W |S )NzSELECT disease, loaded_uid_num_QC1, relative_abundance_std, relative_abundance_avg, relative_abundance_med FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}"zSELECT COUNT(1) total FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1r4   r"   z)SELECT term FROM mesh_data WHERE uid="{}"r5   r6   r7   r8   r9   )r:   r;   r<   r=   r>   r?   r"   )r   r   r   r+   r   r1   r   r   )r
   r@   r   r$   rA   rB   r:   rC   rD   rE   r	   r%   r%   r&   getGenusPhenotype   s*    

rH   N)Zservice.taxonsToSiteServicer   r   utilsr   r'   r   r,   r3   rF   rG   rH   r%   r%   r%   r&   <module>   s   :/
E