3
FQaZj                 @   sp   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d Z
dd Zdd Zdd ZdS )    )
sqlConnectNc        
   
   C   s   t j } d}| j|}d}| j|}d}| j|}d}| j|}t|d t|d t|d t|d t|d t|d t|d t|d d}	|	S )NuU   SELECT SUM(`Number of runs`) run_num, COUNT(1) project_num FROM `mbodymap_projects`zUSELECT COUNT(DISTINCT project_id) project_num, COUNT(1) run_num FROM mbodymap_sampleszSELECT count(DISTINCT project_id) project_num, COUNT(t1.run_id) run_num from mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.run_id=t2.accession_idzSELECT count(DISTINCT project_id) project_num, COUNT(t1.run_id) run_num from mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.run_id=t2.accession_id AND t2.QCStatus=0run_numproject_num)Zfirst_run_numZfirst_project_numZsecond_run_numZsecond_project_numZthird_run_numZthird_project_numZforth_run_numZforth_project_num)r   MySQLConnectionqueryOneint)
connectsql_1firstsql_2secondZsql_3ZthirdZsql_4Zforthresult r   9/var/www/html/mybodymap/server/service/dataPageService.pygetDataIntro   s$    










r   c          
   C   s  g }t j }d}|j|}t|}| dkrd}x|D ]}|dkr4dj|d }	|j|	}
|
d krfd}nd}d	j|d }|j|d
 }dj|d }|j|}|j|d ||d ||d |d |d |d7 }q4W nd}x |D ]}|| d | d kr|d7 }q| d | d |  k o0| | d kn  rdj|d }	|j|	}
|
d kr`d}nd}d	j|d }|j|d
 }dj|d }|j|}|j|d ||d ||d |d |d |d7 }|| | d krP qW ||fS )Nu   SELECT `NCBI Project ID`, `Original Project description`, `Number of runs`, `Related Publications` FROM `mbodymap_projects`   r   	   zJselect * from curated_lefse_analysis_results where project_id="{}" limit 1u   NCBI Project IDFTzSELECT COUNT(t1.run_id) processed_num FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_idprocessed_numzSELECT 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.uidu   Number of runsu   Related Publicationsu   Original Project description)	projectIdprocessed_runs
total_runsrelated_phenotypeRelated_publicationdescriptionmarker)r   r   querylenformatr   append)pagelimitproject_resultr   sql_projectsprojectstotaliproject
sql_marker	if_markerr   sql_processed_runs_numprocessed_runs_numsql_phenotypes
phenotypesr   r   r   getProjectData   sx    







0






r-   c             C   s  g }t j }d}|j|}t|}| dkrd}x|D ]}|dkr4dj|d |d }	|j|	}
|
d krpd|d	< n|
d	 |d	< |j|d |d
 |d |d |d |d |d |d |d |d |d |d	 d |d7 }q4W nd}x
|D ] }|| d | d kr|d7 }q| d | d |  k o:| | d kn  rdj|d |d }	|j|	}
|
d krtd|d	< n|
d	 |d	< |j|d |d
 |d |d |d |d |d |d |d |d |d |d	 d |d7 }|| | d krP qW ||fS )NzSELECT project_id, BodySite, run_id, experiment_type, instrument_model, nr_reads_sequenced, phenotype, host_age, sex, BMI, country FROM mbodymap_samplesr   r   r   zSELECT t2.QCStatus FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id="{}" AND t1.run_id=t2.accession_id
project_idrun_id QCStatusBodySiteexperiment_typeinstrument_modelnr_reads_sequenced	phenotypehost_agesexBMIcountry)r.   r2   r/   r3   r4   r5   r6   r7   r8   r9   r:   	QC_Status)r   r   r   r   r   r   r   )r   r    
run_resultr   sql_runsrunsr$   r%   item
sql_run_QCQCr   r   r   
getRunDataf   st    



0


rB   c          	   C   s   t j }dj| }t|j|d }dj| }t|j|d }dj| }|j|}dj| }|j|}	| || |d |	|d |d ||d	}
|
S )
NzSELECT COUNT(t1.run_id) num FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1numzSELECT COUNT(t1.run_id) num FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=0u   SELECT `Original Project description`, `Number of runs`, `Related Publications` FROM `mbodymap_projects` WHERE `NCBI Project ID`="{}"zSELECT 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.uidu   Number of runsu   Related Publicationsu   Original Project description)r   r   r   r   r   r   Zqc0Zqc1)r   r   r   r   r   r   )r   r   Zsql_qc1_runsZqc1_runsZsql_qc0_runsZqc0_runssql_projectr&   r+   r,   r!   r   r   r   getProjectDataIntro   s,    

rE   c             C   s   t j }dj| }|j|}tt| x|D ]}dj| |d }|j|}|d kr^d|d< n|d |d< |d dks|d dks|d d	krd
|d< d|d< q.|d dks|d dkrd
|d< d|d< q.d|d< |d |d< q.W |S )NzSELECT BodySite, run_id, experiment_type, instrument_model, nr_reads_sequenced, phenotype, host_age, sex, BMI, country FROM mbodymap_samples WHERE project_id="{}"zSELECT t2.QCStatus FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id="{}" AND t1.run_id=t2.accession_idr/   r0   r1   r2   RectumColonCecumguturlNamezLarge IntestineurlParamIleumDuodenumzSmall Intestinebodysite)r   r   r   r   printr   r   )r   r   Zsql_all_runsZall_runsrunr@   rA   r   r   r   getProjectRunsData   s*    



$

rQ   c             C   sp  t j }dj| }|j|}dj|d }|j|}dj|d }|j|}|d krZd}n|d }dj|d }|j|}	||d< |	|d	< d
j| }
|j|
}|d krd|d< ||d< n6|d dkrt| |d< ni |d< |d |d< ||d< |d dks|d dks|d dkr*d|d< d|d< nB|d dksF|d dkrXd|d< d|d< nd|d< |d |d< |S )NzSELECT BodySite, Disease, project_id, experiment_type, instrument_model, nr_reads_sequenced, phenotype, host_age, sex, BMI, country, more_info FROM mbodymap_samples WHERE run_id="{}"u   SELECT `Original Project description` info, `Number of runs` total_runs, `Related Publications` related_publication FROM mbodymap_projects WHERE `NCBI Project ID`="{}"r.   zSELECT COUNT(t1.run_id) processed_num FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE t1.project_id="{}" AND t1.run_id=t2.accession_idr   r   zSELECT 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.uidr   r   zDSELECT QCStatus FROM mbodymap_loaded_samples WHERE accession_id="{}"emptyr1   project_infor   plotDatar2   rF   rG   rH   rI   rJ   zLarge IntestinerK   rL   rM   zSmall IntestinerN   )r   r   r   r   r   
getRunPlot)runIdr   Zsql_runrP   Zsql_project_inforS   r)   r*   r+   r,   Zsql_QCStatusr1   r   r   r   getRunData2   sJ    









*

rW   c             C   s  t j }g }g }g }g }g }g }g }g }	g }
g }d}d}ddg}dj| }|j|d }x|D ]}dj||}|j|}dj||}|j|d }x|D ]}d	j|d
 }|j|d }|dkr|j||d
 |d d |j|d  |j| ||d 7 }q|j||d
 |d d |
j|d  |j| ||d 7 }qW dj||}|j|d }|dkr|jdd|d |jdd|| d |j| |jd |j||  |jd q`|jdd|d |jdd|| d |
j| |jd |
j||  |jd q`W x0|D ](}|jt|d d |	j|d  q W x0|D ](}|jt|d d |j|d  qRW ||	|||||
|||d
}|S )Nr   speciesgenusz?SELECT uid FROM mbodymap_loaded_samples WHERE accession_id="{}"uidzSELECT ncbi_taxon_id, relative_abundance FROM `mbodymap_relative_species_abundances` where loaded_uid={} AND taxon_rank_level="{}" AND ncbi_taxon_id!=-1 ORDER BY relative_abundance DESC LIMIT 10zSELECT SUM(relative_abundance) all_sum FROM `mbodymap_relative_species_abundances` where loaded_uid={} AND taxon_rank_level="{}" AND ncbi_taxon_id!=-1Zall_sumz6SELECT name FROM biosql_taxon_2 WHERE ncbi_taxon_id={}ncbi_taxon_idnamerelative_abundance)r\   r[   r]   zSELECT relative_abundance FROM `mbodymap_relative_species_abundances` where loaded_uid={} AND taxon_rank_level="{}" AND ncbi_taxon_id=-1Unknownr   ZOthersz.1f)
	genusBarX	genusBarYspeciesPiePlotValuespeciesPiePlotLabelspeciesBarXspeciesBarYgenusPiePlotValuegenusPiePlotLabelZgenusDownloadDataZspeciesDownloadDatarg   )r   r   r   r   r   r   )rV   r   ZspeciesBarPlotrc   rd   ra   rb   ZgenusBarPlotr_   r`   re   rf   Zspecies_tenZ	genus_tenZranksZsql_uidrZ   ZrankZsql_tenZtenZsql_allallr?   Zsql_namer\   Zsql_unKnownZunKnownZgenusBarPlot_itemZspeciesBarPlot_itemrT   r   r   r   rU     s    














rU   c              C   sD   t j } d}| j|}d}| j|d }|d |d t|d}|S )Nz{SELECT COUNT(DISTINCT project_id) project_num, COUNT(DISTINCT phenotype2) phenotype_num FROM curated_lefse_analysis_summaryzVselect sum(distinct project_sample_num) sample_num from curated_lefse_analysis_summary
sample_numr   phenotype_num)r   rj   ri   )r   r   r   r   )r   r	   Zresult_1r   rC   r   r   r   r   markerDataIntron  s    
rk   c              C   s~  t j } g }d}| j|}x|D ]}dj|d }| j|}|jdddd g }xH|D ]@}dj|d |d	 }	| j|	}
|j|d	 |d
 |
d d qXW |j|d |d q W g }d}| j|}|jdddd x|D ]}|d	 dkrd}ndj|d	 }| j|}g }xD|D ]<}dj|d |d	 }| j|}|j|d |d d qW |j|d	 |d
 |d qW ||dS )Nz>select distinct project_id from curated_lefse_analysis_resultszeselect distinct phenotype2, phenotype2_name from curated_lefse_analysis_results where project_id="{}"r.   r   D006262ZHealth)
phenotype2phenotype2_namezpselect count(1) sample_num from curated_lefse_analysis_group_to_samples where project_id="{}" and phenotype="{}"rm   rn   ri   )r6   phenotype_nameri   )r&   phenozOselect distinct phenotype2, phenotype2_name from curated_lefse_analysis_resultszYselect distinct project_id from curated_lefse_analysis_results where phenotype1="D006262"zTselect distinct project_id from curated_lefse_analysis_results where phenotype2="{}")r&   ri   )r6   ro   r&   )r!   phenotype_result)r   r   r   r   insertr   r   )r   r!   rD   r&   r?   	sql_phenorp   Zproject_phenotype_resultZ
pheno_itemZsql_pheno_resultZpheno_resultrq   sql_phenotyper6   Zphenotype_peoject_resultZproject_itemZsql_proj_resultZproj_resultr   r   r   markerDataTable~  sR    









ru   c          	   C   s  t j }dj| }|j|}|d kr,d}|S dj| }|j|}dj| }|j|d }dj| }	|j|	d }
||d< |
|d< d	j| }|j||d
< dj| }|j|}g }xB|D ]:}dj| |d |d }|j|}t||d< |j| qW dj| }t|j|}g }x:|D ]2}dj| |d }|j|}||d< |j| qW g }x|D ]}dj| |d |d }|j|}g }g }x:|D ]2}|j|d  |j|d d |d  d  qW |j|d |d |d |d ||d qVW ||||d}|S d S )NzJselect * from curated_lefse_analysis_results where project_id="{}" limit 1Fzselect count(1) comparisons, count(distinct ncbi_taxon_id) taxa, count(distinct bodysite) bodysite from curated_lefse_analysis_results where project_id="{}"zselect count(distinct ncbi_taxon_id) taxa from curated_lefse_analysis_results where project_id="{}" and taxon_rank_level="species"taxazselect count(distinct ncbi_taxon_id) taxa from curated_lefse_analysis_results where project_id="{}" and taxon_rank_level="genus"rX   rY   zselect distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name, bodysite, count(ncbi_taxon_id) taxa_num FROM `curated_lefse_analysis_results` WHERE project_id="{}"  GROUP BY phenotype1, phenotype1_name, phenotype2, phenotype2_name, bodysitetablezSELECT distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name FROM `curated_lefse_analysis_results` WHERE project_id="{}"zselect distinct ncbi_taxon_id, taxa_name, taxon_rank_level, bodysite, lda from `curated_lefse_analysis_results` where project_id="{}" and phenotype1="{}" and phenotype2="{}"
phenotype1rm   zdselect distinct ncbi_taxon_id, taxa_name from `curated_lefse_analysis_results` where project_id="{}"zselect distinct phenotype1, phenotype1_name, phenotype2, phenotype2_name, bodysite, taxon_rank_level, lda from `curated_lefse_analysis_results` where project_id="{}" and ncbi_taxon_id={}r[   rp   zselect ncbi_taxon_id, taxa_name, bodysite, lda from curated_lefse_analysis_results WHERE project_id="{}" and phenotype1="{}" and phenotype2="{}"ldaZ	taxa_namez (rN   )phenotype1_namern   )rx   rm   r{   rn   xaxisyaxis)introphenotype_table
taxa_tablebar)r   r   r   r   r   getTaxonsDatabaser   )r   r   r'   r(   r   	sql_intror~   Zsql_intro_speciesZintro_speciesZsql_intro_genusZintro_genusZsql_introTablers   rp   r   r?   Zsql_taxarv   r   Z	taxa_itembarDataZbar_itemsql_phenoData	phenoDatar|   r}   r   r   r   r   getMarkerProjects  s~    









$r   c       	      C   s   t j }x| D ]}dj|d }|j|}|d k	rLd|d< tj|d |d< dj|d }|j|}|d k	rtd|d< d	j|d }|j|}|d k	rd|d
< qW | S )Nz<select attributes from cross_db_links where ncbi_taxon_id={}r[   TifMvp
attributesmvpDataz3select * from gmrepo_species where ncbi_taxon_id={}ifGmrepoz0select * from hmdad_taxon where ncbi_taxon_id={}ifHmdad)r   r   r   r   jsonloads)	arrr   r?   sql_mvpmvp
sql_gmrepogmrepo	sql_hmdadhmdadr   r   r   r     s     



r   )utilsr   r   r   r-   rB   rE   rQ   rW   rU   rk   ru   r   r   r   r   r   r   <module>   s   GC,c5R