o
    FQa4                     @   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     t  }d}g }	| D ]}
|
dkr|d7 }q
|
dkr|d7 }q
|d|
 7 }q
|d}d|||}||}d	}tt|d
 ddD ]}|dkrYt|| d d
krY|||  |d
7 }qBt|}|d
krd	}|D ]4}
|dkrd|
d }|	|}|d u rd}nd}|	
|
d |
d |
d t|
d |d |d
7 }qjned	}|D ]`}
||d
 | d
 kr|d
7 }q|d
 | d
 |  k r|| d
 krn n.d|
d }|	|}|d u rd}nd}|	
|
d |
d |
d t|
d |d |d
7 }||| d
 kr nqt|	||}||fS )N Large intestine'Rectum','Colon','Cecum',Small intestine'Ileum','Duodenum','%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	   Kselect * 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iZ
sql_markerZ	if_markerr   result r5   ;/var/www/html/mybodymap/server/service/taxonsPageService.py
getSpecies   s   






,

r7   c                 C      t  }| D ]Y}d|d ||}||}||d< d|d }||}|d ur7d|d< t|d |d< d	|d }	||	}
|
d urKd|d
< d|d }||}|d ur_d|d< q| S )Nz_SELECT * FROM mbodymap_species_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}r   secondTableData<select attributes from cross_db_links where ncbi_taxon_id={}TifMvp
attributesmvpDataz3select * from gmrepo_species where ncbi_taxon_id={}ifGmrepo0select * from hmdad_taxon where ncbi_taxon_id={}ifHmdadr   r   r   r   r   jsonloadsZarrr(   r)   r*   r-   sqlr4   sql_mvpmvp
sql_gmrepogmrepo	sql_hmdadhmdadr5   r5   r6   r!   R   s,   




r!   c                 C   r8   )Nz]SELECT * FROM mbodymap_genus_to_site_relative_abundance WHERE ncbi_taxon_id={} ORDER BY {} {}r   r9   r:   Tr;   r<   r=   z1select * from gmrepo_genus where ncbi_taxon_id={}r>   r?   r@   rA   rD   r5   r5   r6   getTaxonsPageGenusDataj   s,   




rL   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   rM   rN   filter_species_numfilter_genus_num)r   r   r   r   )r*   Zsql_sample_numr   Zsql_species_numrM   Zsql_genus_numrN   Zsql_filter_species_numrO   Zsql_filter_genus_numrP   species_intror5   r5   r6   getSpeciesIntro   s$   rR   c                 C      t  }g }d| |}||}|D ]8}d|d |}||d }d|d }	||	d }
||
|d |d |d |d	 |d
 t|d q|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="{}"SELECT 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diseaser2   )SELECT term FROM mesh_data WHERE uid="{}"termloaded_uid_num_QC1relative_abundance_stdrelative_abundance_avgrelative_abundance_med	phenotypeZmesh_idZloaded_uid_numZstdZavgZmedr2   r   r   r   r   r   r    r   r   siter*   r4   Zsql_phenotype
phenotypesr]   Z"sql_total_disease_of_taxon_in_siteZtotal_disease_of_taxon_in_sitesql_disease_namer   r5   r5   r6   getSpeciesPhenotype   s.   

	rc   c              	   C   r   )Nr   r   r   r	   r
   r   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   r   r   FTr   r   r   )r   r   r   r   r   r   r   r   r   r   r    rL   r"   r5   r5   r6   getGenus   s   






,

re   c                 C   rS   )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="{}"rT   rU   r2   rV   rW   rX   rY   rZ   r[   r\   r^   r_   r5   r5   r6   getGenusPhenotype   s.   

	rf   )Zservice.taxonsToSiteServicer   r   utilsr   rB   r7   r!   rL   rR   rc   re   rf   r5   r5   r5   r6   <module>   s    LK