3
D`B                 @   sH   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 )    )
sqlConnectc        
   
   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)
connectZsql_1firstZ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}xx|D ]p}|dkr4dj|d }	|j|	d }
dj|d }|j|}|j|d |
|d	 ||d
 |d d |d7 }q4W nd}x|D ]}|| d | d kr|d7 }q| d | d |  k o| | d kn  rhd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   	   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_idu   NCBI Project 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)r   r   querylenformatr   append)pagelimitproject_resultr   Zsql_projectsprojectstotaliprojectsql_processed_runs_numprocessed_runs_numsql_phenotypes
phenotypesr   r   r   getProjectData   s\    





.


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(   r,   r)   r-   r.   r/   r0   r1   r2   r3   r4   Z	QC_Status)r   r   r   r   r   r   r   )r   r   Z
run_resultr   sql_runsrunsr    r!   item
sql_run_QCQCr   r   r   
getRunDataQ   st    



0


r:   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*    


r=   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)   r*   r+   r,   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runr8   r9   r   r   r   getProjectRunsData   s*    



$

rI   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="{}"emptyr+   project_infor   plotDatar,   r>   r?   r@   rA   rB   zLarge IntestinerC   rD   rE   zSmall IntestinerF   )r   r   r   r   r   
getRunPlot)runIdr   Zsql_runrH   Zsql_project_inforK   r#   r$   r%   r&   Zsql_QCStatusr+   r   r   r   getRunData2   sH    








*

rO   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)rT   rS   rU   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speciesDownloadDatar_   )r   r   r   r   r   r   )rN   r   ZspeciesBarPlotr[   r\   rY   rZ   ZgenusBarPlotrW   rX   r]   r^   Zspecies_tenZ	genus_tenZranksZsql_uidrR   ZrankZsql_tenZtenZsql_allallr7   sql_namerT   Zsql_unKnownZunKnownZgenusBarPlot_itemZspeciesBarPlot_itemrL   r   r   r   rM      s    














rM   N)	utilsr   r   r'   r:   r=   rI   rO   rM   r   r   r   r   <module>   s   5C+