B
    r`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  }d}g }	x<| D ]4}
|
dkr,|d7 }q|
dkr>|d7 }q|d|
 7 }qW |d}d|||}||}t|}|d	krd
}x|D ]B}
|dkr|	|
d |
d |
d dt|
d d |d	7 }qW nd
}x|D ]}
||d	 | d	 kr|d	7 }q|d	 | d	 |  k r*|| d	 krdn n6|	|
d |
d |
d dt|
d d |d	7 }||| d	 krP qW 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 ({}) 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   )	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%   TC:\Users\Administrator\Desktop\mbodymap\mbodymap-server\service\taxonsPageService.py
getSpecies<   sP    







0r'   c             C   s>   t  }x0| D ](}d|d ||}||}||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  }x8| D ]0}d|d ||}t| ||}||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  } d}t| |d }d}| |d }d}| |d }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_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  }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   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  }d}g }	x<| D ]4}
|
dkr,|d7 }q|
dkr>|d7 }q|d|
 7 }qW |d}d|||}||}t|}|d	krd
}x|D ]B}
|dkr|	|
d |
d |
d dt|
d d |d	7 }qW nd
}x|D ]}
||d	 | d	 kr|d	7 }q|d	 | d	 |  k r*|| d	 krdn n6|	|
d |
d |
d dt|
d d |d	7 }||| d	 krP qW 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 ({}) 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   )	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  }g }d| |}t| ||}xx|D ]p}d|d |}||d }d|d }	||	d }
||
|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