3
FQa8                 @   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| }|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_QC1z6select name from biosql_taxon_2 where ncbi_taxon_id={}namez)select term from mesh_data where uid="{}"termz{:,})species_namedisease_name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   sql_species_namer
   sql_disease_namer   result r    @/var/www/html/mybodymap/server/service/taxonsFinalPageService.pygetSpeciesIntroData   s6    



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="{}"r	   zxSELECT 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yr   modemarkerlineboxOutliersg      ?)r1   r2   r   	boxpointswidth	fillcolorr5   r6   )r1   r2   r3   r   r4   )r1   r2   r   r9   r:   )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_siter   r   
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    











rU   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| }|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   z6select name from biosql_taxon_2 where ncbi_taxon_id={}r   z)select term from mesh_data where uid="{}"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!   getGenusIntroData   s6    



rV   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/   r0   )r1   r2   r3   r   r4   r5   r6   r7   r8   g      ?)r1   r2   r   r9   r:   r;   r5   r6   )r1   r2   r3   r   r4   )r1   r2   r   r9   r:   )	r   r   r   r   r<   printr=   r>   r?   )r   r   r   r   r@   rA   rB   rC   rD   rE   rF   rG   rH   r   r   rI   rJ   rK   rL   rM   rN   rO   rP   rQ   rR   rS   rT   r    r    r!   getGenusPlotData   s    











rX   N)utilsr   r"   rU   rV   rX   r    r    r    r!   <module>   s   #l$