diff options
Diffstat (limited to 'web/webqtl/genomeGraph/genAllDbResultPage.py')
| -rwxr-xr-x | web/webqtl/genomeGraph/genAllDbResultPage.py | 309 |
1 files changed, 309 insertions, 0 deletions
diff --git a/web/webqtl/genomeGraph/genAllDbResultPage.py b/web/webqtl/genomeGraph/genAllDbResultPage.py new file mode 100755 index 00000000..f0663a7c --- /dev/null +++ b/web/webqtl/genomeGraph/genAllDbResultPage.py @@ -0,0 +1,309 @@ +# Copyright (C) University of Tennessee Health Science Center, Memphis, TN. +# +# This program is free software: you can redistribute it and/or modify it +# under the terms of the GNU Affero General Public License +# as published by the Free Software Foundation, either version 3 of the +# License, or (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. +# See the GNU Affero General Public License for more details. +# +# This program is available from Source Forge: at GeneNetwork Project +# (sourceforge.net/projects/genenetwork/). +# +# Contact Drs. Robert W. Williams and Xiaodong Zhou (2010) +# at rwilliams@uthsc.edu and xzhou15@uthsc.edu +# +# +# +# This module is used by GeneNetwork project (www.genenetwork.org) +# +# Created by GeneNetwork Core Team 2010/08/10 +# +# Last updated by GeneNetwork Core Team 2010/10/20 + +import string +import os +import time + +from htmlgen import HTMLgen2 as HT + +from base import webqtlConfig +from utility import webqtlUtil +from base.webqtlDataset import webqtlDataset +from base.templatePage import templatePage + + +######################################### +# Genome Scan PAGE +######################################### +class genAllDbResultPage(templatePage): + + def __init__(self,fd): + + templatePage.__init__(self,fd) + + if not self.openMysql(): + return + + self.database = fd.formdata.getvalue('database', '') + db = webqtlDataset(self.database, self.cursor) + + try: + if db.type != "ProbeSet" or not db.id: + raise DbNameError + except: + print 'Content-type: text/html\n' + heading = "Download Results" + detail = ["Only results of microarray database are available to download."] + self.error(heading=heading,detail=detail) + self.write() + return + + + #XZ, protect confidential dataset. + userExist = None + self.cursor.execute('SELECT Id, Name, FullName, confidentiality, AuthorisedUsers FROM ProbeSetFreeze WHERE Name = "%s"' % self.database) + indId, indName, indFullName, indConfid, AuthorisedUsers = self.cursor.fetchall()[0] + if indConfid == 1 and userExist == None: + try: + + userExist = self.userName + + #for the dataset that confidentiality is 1 + #1. 'admin' and 'root' can see all of the dataset + #2. 'user' can see the dataset that AuthorisedUsers contains his id(stored in the Id field of User table) + if webqtlConfig.USERDICT[self.privilege] < webqtlConfig.USERDICT['admin']: + if not AuthorisedUsers: + userExist=None + else: + AuthorisedUsersList=AuthorisedUsers.split(',') + if not AuthorisedUsersList.__contains__(self.userName): + userExist=None + except: + pass + + if not userExist: + #Error, Confidential Database + heading = "Correlation Table" + detail = ["The %s database you selected is not open to the public at this time, please go back and select other database." % indFullName] + self.error(heading=heading,detail=detail,error="Confidential Database") + return + + self.cursor.execute(""" + Select + InbredSet.Name + From + ProbeSetFreeze, ProbeFreeze, InbredSet + whERE + ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND + ProbeFreeze.InbredSetId = InbredSet.Id AND + ProbeSetFreeze.Id = %d + """ % db.id) + thisRISet = self.cursor.fetchone()[0] + if thisRISet =='BXD300': + thisRISet = 'BXD' + + #XZ, 06/26/2009: It seems that this query is not neccessary. It doesn't return any result. + #XZ: It seems it is just for test purpose. The next try-except block does the real work. + #XZ: I think it should be deleted to shorten the response time. + #self.cursor.execute(""" + # Select + # ProbeSet.Name, ProbeSet.symbol, ProbeSet.description, ProbeSet.Chr, ProbeSet.Mb, ProbeSetXRef.Locus, + # ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.additive, ProbeSetXRef.mean + # From + # ProbeSet, ProbeSetXRef + # whERE + # ProbeSetXRef.ProbeSetFreezeId = %d AND + # ProbeSetXRef.ProbeSetId = ProbeSet.Id AND + # ProbeSetXRef.Locus is not NULL + # Order by + # ProbeSet.name_num + # """ % db.id) + + filename = os.path.join(webqtlConfig.SECUREDIR, db.name+'.result.xls') + + try: + import random234 + if random.choice(range(10)) == 0: + raise "ReCalculate" + fp = open(filename, 'rb') + text = fp.read() + fp.close() + except: + self.cursor.execute("Select ProbeSetXRef.ProbeSetId from ProbeSetXRef where ProbeSetFreezeId=%d" % db.id) + ProbeSetIds = self.cursor.fetchall() + self.mouseChrLengthDict, sum = self.readMouseGenome(thisRISet) + + if ProbeSetIds: + import reaper + markerGMb = {} + genotype_1 = reaper.Dataset() + genotype_1.read(os.path.join(webqtlConfig.GENODIR, thisRISet + '.geno')) + for chr in genotype_1: + chrlen = self.mouseChrLengthDict[chr.name] + for locus in chr: + markerGMb[locus.name] = [chr.name, locus.Mb, locus.Mb + chrlen] + + text = [] + text.append(['ProbeSetId', 'Symbol', 'Description', 'Target Description', 'Chr', 'TMb', 'TGMb', 'Locus', 'LRS', 'Additive', 'pvalue', 'markerChr', 'markerMb', 'markerGMb', 'meanExpression']) + ProbeSetIdList = [] + for ProbeSetId in ProbeSetIds: + ProbeSetIdList.append(ProbeSetId[0]) + if len(ProbeSetIdList)==1000: + ProbeSetIdStr = ','.join(map(str, ProbeSetIdList)) + ProbeSetIdList = [] + + cmd = """ + Select + ProbeSet.Name, ProbeSet.symbol, ProbeSet.description,ProbeSet.Probe_Target_Description,ProbeSet.Chr, ProbeSet.Mb, + ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, + ProbeSetXRef.additive, ProbeSetXRef.mean + From + ProbeSet, ProbeSetXRef + Where + ProbeSetXRef.ProbeSetFreezeId = %s AND + ProbeSetXRef.ProbeSetId = ProbeSet.Id AND + ProbeSetXRef.Locus is not NULL AND + ProbeSet.Id in (%s) + Order by + ProbeSet.name_num + """ % (db.id, ProbeSetIdStr) + + self.cursor.execute(cmd) + results = self.cursor.fetchall() + + for result in results: + _Id, _symbol, _desc,_targetDesc, _chr, _TMb, _marker, _lrs, _pvalue, _additive, _mean = result + if _marker == "-": + continue + if not _additive: + _additive = "" + + try: + _TGMb = _TMb + self.mouseChrLengthDict[string.strip(_chr)] + except: + _TGMb = "" + + result2 = [_Id, _symbol, _desc, _targetDesc, _chr, _TMb, _TGMb, _marker, _lrs, _additive, _pvalue] + try: + result2 += markerGMb[_marker] + except: + result2 += ['', '', ''] + result2 += [_mean] + text.append(map(str, result2)) + + #XZ, 06/29/2007: This block is dealing with the last several probesets that fall out of the 1000-probeset block. + if ProbeSetIdList: + ProbeSetIdStr = ','.join(map(str, ProbeSetIdList)) + + cmd = """ + Select + ProbeSet.Name, ProbeSet.symbol, ProbeSet.description,ProbeSet.Probe_Target_Description, ProbeSet.Chr, ProbeSet.Mb, + ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, + ProbeSetXRef.additive, ProbeSetXRef.mean + From + ProbeSet, ProbeSetXRef + Where + ProbeSetXRef.ProbeSetFreezeId = %s AND + ProbeSetXRef.ProbeSetId = ProbeSet.Id AND + ProbeSetXRef.Locus is not NULL AND + ProbeSet.Id in (%s) + Order by + ProbeSet.name_num + """ % (db.id, ProbeSetIdStr) + + self.cursor.execute(cmd) + results = self.cursor.fetchall() + + for result in results: + _Id, _symbol, _desc, _targetDesc,_chr, _TMb, _marker, _lrs, _pvalue, _additive, _mean = result + if _marker == "-": + continue + if not _additive: + _additive = "" + + try: + _TGMb = _TMb + self.mouseChrLengthDict[string.strip(_chr)] + except: + _TGMb = "" + + result2 = [_Id, _symbol, _desc,_targetDesc, _chr, _TMb, _TGMb, _marker, _lrs, _additive, _pvalue] + try: + result2 += markerGMb[_marker] + except: + result2 += ['', '', ''] + result2 += [_mean] + text.append(map(str, result2)) + + + import pyXLWriter as xl + # Create a new Excel workbook + workbook = xl.Writer(filename) + worksheet = workbook.add_worksheet() + heading = workbook.add_format(align = 'center', bold = 1, size=13, color = 'red') + titleStyle = workbook.add_format(align = 'left', bold = 0, size=14, border = 1, border_color="gray") + + worksheet.write([0, 0], "Data source: The GeneNetwork at http://www.genenetwork.org", titleStyle) + worksheet.write([1, 0], "Citations: Please see %s/reference.html" % webqtlConfig.PORTADDR, titleStyle) + worksheet.write([2, 0], "Database : %s" % db.fullname, titleStyle) + worksheet.write([3, 0], "Date : %s" % time.strftime("%B %d, %Y", time.gmtime()), titleStyle) + worksheet.write([4, 0], "Time : %s GMT" % time.strftime("%H:%M ", time.gmtime()), titleStyle) + worksheet.write([5, 0], "Status of data ownership: Possibly unpublished data; please see %s/statusandContact.html for details on sources, ownership, and usage of these data." % webqtlConfig.PORTADDR, titleStyle) + + table_row_start_index = 7 + nrow = table_row_start_index + for row in text: + for ncol, cell in enumerate(row): + if nrow == table_row_start_index: + worksheet.write([nrow, ncol], cell.strip(), heading) + worksheet.set_column([ncol, ncol], 20) + else: + worksheet.write([nrow, ncol], cell.strip()) + nrow += 1 + + worksheet.write([1+nrow, 0], "Funding for The GeneNetwork: NIAAA (U01AA13499, U24AA13513), NIDA, NIMH, and NIAAA (P20-DA21131), NCI MMHCC (U01CA105417), and NCRR (U01NR 105417)", titleStyle) + worksheet.write([2+nrow, 0], "PLEASE RETAIN DATA SOURCE INFORMATION WHENEVER POSSIBLE", titleStyle) + + workbook.close() + + fp = open(filename, 'rb') + text = fp.read() + fp.close() + else: + heading = "Download Results" + detail = ["Database calculation is not finished."] + self.error(heading=heading,detail=detail) + return + + self.content_type = 'application/xls' + self.content_disposition = 'attachment; filename=%s' % ('export-%s.xls' % time.strftime("%y-%m-%d-%H-%M")) + self.attachment = text + + def readMouseGenome(self, RISet): + ldict = {} + lengths = [] + sum = 0 + ##################################### + # Retrieve Chr Length Information + ##################################### + self.cursor.execute(""" + Select + Chr_Length.Name, Length from Chr_Length, InbredSet + where + Chr_Length.SpeciesId = InbredSet.SpeciesId AND + InbredSet.Name = '%s' + Order by + OrderId + """ % RISet) + lengths = self.cursor.fetchall() + ldict[lengths[0][0]] = 0 + prev = lengths[0][1]/1000000.0 + sum += lengths[0][1]/1000000.0 + for item in lengths[1:]: + ldict[item[0]] = prev + prev += item[1]/1000000.0 + sum += item[1]/1000000.0 + return ldict, sum |