3
C`4                 @   s0   d dl mZ dd Zdd Zdd Zdd	 Zd
S )    )
sqlConnectc             C   s   t j }dj|}|j|d }dj||}|j|d }dj||}|j|d }	dj||}
|j|
d }d	j||| }|j|d
 }|dj|dj|	dj|dj|d}|S )Nz)SELECT note FROM mesh_data WHERE uid="{}"notezTSELECT COUNT(1) run_total FROM mbodymap_samples WHERE disease="{}" AND BodySite="{}"	run_totalzSELECT COUNT(1) run_processed FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_idrun_processedzSELECT COUNT(1) run_valid FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1	run_validztSELECT loaded_uid_num_QC1 FROM mbodymap_species_to_samples WHERE disease="{}" AND BodySite="{}" AND ncbi_taxon_id={}loaded_uid_num_QC1z{:,})desc_diseaseintro_disease_totalintro_disease_processedintro_disease_validintro_species_inDisease_valid)r   MySQLConnectionformatqueryOne)ncbi_taxon_idsitediseaseconnectsql_desc_diseaser   sql_intro_disease_totalr	   sql_intro_disease_processedr
   sql_intro_disease_validr   !sql_intro_species_inDisease_validr   result r   @/var/www/html/mybodymap/server/service/taxonsFinalPageService.pygetSpeciesIntroData   s*    

r   c             C   s`  t j }|dkr|g}nd|g}g }g }dj||| }|j|d }t|dk r^g g d}	nx|D ]}
dj|
|}|j|d }dj|
}|j|d }d	j| ||
}|j|}|d krd
}n|j|d }dj||
| }|j|}t|}|d d
kr
|d7 }n|d7 }g }|dkrDxHtdddD ]}|j| q.W n&x$td|d dD ]}|j| qVW g }g }x\|D ]T}d
}x2|D ]*}|j|d  |d |kr|d7 }qW |j|t| d  qxW |
dkr"|jd|||dddiddid |jd||dddddiddid qf|jd|||dd |jd||ddd qfW ||d}	|	S )ND006262zSELECT COUNT(1) total FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}total
   )linePlotboxPlotzSELECT 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=1z)SELECT term FROM mesh_data WHERE uid="{}"termzxSELECT relative_abundance_max FROM mbodymap_species_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND disease="{}"r   relative_abundance_maxzSELECT t1.relative_abundance ra FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}      d   e   rascatterzlines+markerscolorgreen)typexynamemodemarkerlineboxOutliersg?)r,   r-   r/   	boxpointswidth	fillcolorr1   r2   )r,   r-   r.   r/   r0   )r,   r-   r/   r5   r6   )r   r   r   r   intqueryrangeappend)r   r   r   r   diseaseListlinePlot_resultsboxPlot_resultssql_runs_total
runs_totalresultsitem"sql_total_disease_of_taxon_in_sitetotal_disease_of_taxon_in_sitesql_disease_namedisease_name
sql_max_ramax_rasql_all_reiative_abundanceall_reiative_abundancemax_relative_abundanceroundXiroundY	box_valuerule
rule_valuerelative_abundancer   r   r   getSpeciesPlotData   s    











rS   c             C   s   t j }dj|}|j|d }dj||}|j|d }dj||}|j|d }	dj||}
|j|
d }d	j||| }|j|d
 }|dj|dj|	dj|dj|d}|S )Nz)SELECT note FROM mesh_data WHERE uid="{}"r   zTSELECT COUNT(1) run_total FROM mbodymap_samples WHERE disease="{}" AND BodySite="{}"r   zSELECT COUNT(1) run_processed FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_idr   zSELECT COUNT(1) run_valid FROM mbodymap_samples t1, mbodymap_loaded_samples t2 WHERE disease="{}" AND BodySite="{}" AND t1.run_id=t2.accession_id AND t2.QCStatus=1r   zrSELECT loaded_uid_num_QC1 FROM mbodymap_genus_to_samples WHERE disease="{}" AND BodySite="{}" AND ncbi_taxon_id={}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   getGenusIntroData   s*    

rT   c             C   sh  t j }|dkr|g}nd|g}g }g }dj||| }|j|d }t|dk r^g g d}	nx|D ]}
dj|
|}|j|d }dj|
}|j|d }d	j| ||
}t| |j|}|d krd
}n|j|d }dj||
| }|j|}t|}|d d
kr|d7 }n|d7 }g }|dkrLxHtdddD ]}|j| q6W n&x$td|d dD ]}|j| q^W g }g }x\|D ]T}d
}x2|D ]*}|j|d  |d |kr|d7 }qW |j|t| d  qW |
dkr*|jd|||dddiddid |jd||dddddiddid qf|jd|||dd |jd||ddd qfW ||d}	|	S )Nr   zSELECT COUNT(1) total FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}r   r   )r    r!   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=1z)SELECT term FROM mesh_data WHERE uid="{}"r"   zvSELECT relative_abundance_max FROM mbodymap_genus_to_samples WHERE ncbi_taxon_id={} AND BodySite="{}" AND disease="{}"r   r#   zSELECT t1.relative_abundance ra FROM mbodymap_relative_species_abundances t1, mbodymap_samples t2, mbodymap_loaded_samples t3 WHERE t2.BodySite="{}" AND t2.disease="{}" AND t2.run_id=t3.accession_id AND t1.loaded_uid=t3.uid AND t1.ncbi_taxon_id={}r$   r%   r&   r'   r(   r)   zlines+markersr*   r+   )r,   r-   r.   r/   r0   r1   r2   r3   r4   g?)r,   r-   r/   r5   r6   r7   r1   r2   )r,   r-   r.   r/   r0   )r,   r-   r/   r5   r6   )	r   r   r   r   r8   printr9   r:   r;   )r   r   r   r   r<   r=   r>   r?   r@   rA   rB   rC   rD   rE   rF   rG   rH   rI   rJ   rK   rL   rM   rN   rO   rP   rQ   rR   r   r   r   getGenusPlotData   s    











rV   N)utilsr   r   rS   rT   rV   r   r   r   r   <module>   s   l