B
    Na4                 @   s`   d dl mZmZ d dlmZ d dl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)
sqlConnectNc          	   C   s<  t  }d}g }	x<| D ]4}
|
dkr,|d7 }q|
dkr>|d7 }q|d|
 7 }qW |d}d|||}||}d	}xNtt|d
 ddD ]6}|dkrt|| d d
kr|||  |d
7 }qW t|}|d
krPd	}xr|D ]j}
|dkrd|
d }|	|}|d krd}nd}|	
|
d |
d |
d t|
d |d |d
7 }qW nd	}x|D ]}
||d
 | d
 kr|d
7 }qZ|d
 | d
 |  k r|| d
 krn n^d|
d }|	|}|d krd}nd}|	
|
d |
d |
d t|
d |d |d
7 }||| d
 krZP qZW t|	||}||fS )N zLarge intestinez'Rectum','Colon','Cecum',zSmall intestinez'Ileum','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 ({}) GROUP BY ncbi_taxon_id, name, site_num ORDER BY {} {}r      i  
sample_num	   zKselect * from curated_lefse_analysis_results where ncbi_taxon_id={} limit 1ncbi_taxon_idFTnamesite_num)r   r   r   r	   marker)r   MySQLConnectionrstripformatqueryrangelenintremovequeryOneappendgetTaxonsPageSpeciseData)checkedSitespagelimitpropName	propOrder
secondNamesecondOrderconnectsearchspecies_resultitemsql_species_totalspecies_totaljktotali
sql_marker	if_markerr   result r.   TC:\Users\Administrator\Desktop\mbodymap\mbodymap-server\service\taxonsPageService.py
getSpecies   sx    













0




r0   c             C   s   t  }x| D ]}d|d ||}||}||d< d|d }||}|d k	rpd|d< t|d |d< d	|d }	||	}
|
d k	rd|d
< d|d }||}|d k	rd|d< qW | S )Nz_SELECT * FROM mbodymap_species_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}r   secondTableDataz<select attributes from cross_db_links where ncbi_taxon_id={}TifMvp
attributesmvpDataz3select * from gmrepo_species where ncbi_taxon_id={}ifGmrepoz0select * from hmdad_taxon where ncbi_taxon_id={}ifHmdad)r   r   r   r   r   jsonloads)arrr   r    r!   r$   sqlr-   sql_mvpmvp
sql_gmrepogmrepo	sql_hmdadhmdadr.   r.   r/   r   R   s(    




r   c             C   s   t  }x| D ]}d|d ||}||}||d< d|d }||}|d k	rpd|d< t|d |d< d	|d }	||	}
|
d k	rd|d
< d|d }||}|d k	rd|d< qW | S )Nz]SELECT * FROM mbodymap_genus_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}r   r1   z<select attributes from cross_db_links where ncbi_taxon_id={}Tr2   r3   r4   z1select * from gmrepo_genus where ncbi_taxon_id={}r5   z0select * from hmdad_taxon where ncbi_taxon_id={}r6   )r   r   r   r   r   r7   r8   )r9   r   r    r!   r$   r:   r-   r;   r<   r=   r>   r?   r@   r.   r.   r/   getTaxonsPageGenusDataj   s(    




rA   c              C   sz   t  } d}t| |d }d}| |d }d}| |d }d}| |d }d}	| |	d }
|||||
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_numzsSELECT COUNT(DISTINCT ncbi_taxon_id) species_num FROM mbodymap_species_to_site WHERE loaded_uid_num>1 AND med>=0.01zoSELECT COUNT(DISTINCT ncbi_taxon_id) genus_num FROM mbodymap_genus_to_site WHERE loaded_uid_num>1 AND med>=0.01)r	   rB   rC   filter_species_numfilter_genus_num)r   r   r   r   )r!   Zsql_sample_numr	   Zsql_species_numrB   Zsql_genus_numrC   Zsql_filter_species_numrD   Zsql_filter_genus_numrE   species_intror.   r.   r/   getSpeciesIntro   s"    rG   c             C   s   t  }g }d| |}||}xx|D ]p}d|d |}||d }d|d }	||	d }
||
|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   r   r   r   )r   siter!   r-   sql_phenotype
phenotypesrN   "sql_total_disease_of_taxon_in_sitetotal_disease_of_taxon_in_sitesql_disease_namer   r.   r.   r/   getSpeciesPhenotype   s(    

rZ   c          	   C   s<  t  }d}g }	x<| D ]4}
|
dkr,|d7 }q|
dkr>|d7 }q|d|
 7 }qW |d}d|||}||}d	}xNtt|d
 ddD ]6}|dkrt|| d d
kr|||  |d
7 }qW t|}|d
krPd	}xr|D ]j}
|dkrd|
d }|	|}|d krd}nd}|	
|
d |
d |
d t|
d |d |d
7 }qW nd	}x|D ]}
||d
 | d
 kr|d
7 }qZ|d
 | d
 |  k r|| d
 krn n^d|
d }|	|}|d krd}nd}|	
|
d |
d |
d t|
d |d |d
7 }||| d
 krZP qZW t|	||}||fS )Nr   zLarge intestinez'Rectum','Colon','Cecum',zSmall intestinez'Ileum','Duodenum',z'%s',r   zSELECT DISTINCT ncbi_taxon_id, name, site_num, SUM(loaded_uid_num) sample_num FROM mbodymap_genus_to_site WHERE BodySite IN ({}) GROUP BY ncbi_taxon_id, name, site_num ORDER BY {} {}r   r   r   2   r	   r
   zKselect * from curated_lefse_analysis_results where ncbi_taxon_id={} limit 1r   FTr   r   )r   r   r   r	   r   )r   r   r   r   r   r   r   r   r   r   r   rA   )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   sx    













0




r\   c             C   s   t  }g }d| |}||}xx|D ]p}d|d |}||d }d|d }	||	d }
||
|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_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=1rH   r)   z)SELECT term FROM mesh_data WHERE uid="{}"rI   rJ   rK   rL   rM   )rN   rO   rP   rQ   rR   rS   r)   )r   r   r   r   r   r   r   )r   rT   r!   r-   rU   rV   rN   rW   rX   rY   r   r.   r.   r/   getGenusPhenotype   s(    

r]   )Zservice.taxonsToSiteServicer   r   utilsr   r7   r0   r   rA   rG   rZ   r\   r]   r.   r.   r.   r/   <module>   s   LK