import string
import os
import cPickle
import re
from math import *
import time
import pyXLWriter as xl
import pp
import math
import datetime
from htmlgen import HTMLgen2 as HT
from base import webqtlConfig
from utility.THCell import THCell
from utility.TDCell import TDCell
from base.data_set import DataSet
from base.webqtlTrait import webqtlTrait
from base.templatePage import templatePage
from utility import webqtlUtil
from dbFunction import webqtlDatabaseFunction
import PubmedSearch
import logging
logging.basicConfig(filename="/tmp/gn_log_leiyan", level=logging.INFO)
_log = logging.getLogger("search")
class SearchResultPage(templatePage):
maxReturn = 3000
# NPerPage = 100
nkeywords = 0
def __init__(self, fd):
templatePage.__init__(self, fd)
if not self.openMysql():
return
self.dict['title'] = 'Search Results'
TD_LR = HT.TD(height=200,width="100%",bgColor='#eeeeee',valign="top")
self.database = fd.formdata.getfirst('database', '')
if not self.database or self.database == 'spacer':
#Error, No database selected
heading = "Search Result"
detail = ['''No database was selected for this search, please
go back and SELECT at least one database.''']
self.error(heading=heading,detail=detail,error="No Database Selected")
return
elif type(self.database) == type(""):
#convert database into a database list
#was used for multiple databases search, this
#feature has been abandoned,
self.database = string.split(self.database,',')
else:
pass
###########################################
# Names and IDs of RISet / F2 set
###########################################
if self.database == ['_allPublish']:
self.cursor.execute("""select PublishFreeze.Name, InbredSet.Name, InbredSet.Id from PublishFreeze,
InbredSet where PublishFreeze.Name not like 'BXD300%' and InbredSet.Id =
PublishFreeze.InbredSetId""")
results = self.cursor.fetchall()
self.database = map(lambda x: DataSet(x[0], self.cursor), results)
self.databaseCrosses = map(lambda x: x[1], results)
self.databaseCrossIds = map(lambda x: x[2], results)
self.singleCross = False
else:
self.database = map(lambda x: DataSet(x, self.cursor), self.database)
#currently, webqtl wouldn't allow multiple crosses
#for other than multiple publish db search
#so we can use the first database as example
if self.database[0].type=="Publish":
pass
elif self.database[0].type in ("Geno", "ProbeSet"):
#userExist = None
for individualDB in self.database:
self.cursor.execute('SELECT Id, Name, FullName, confidentiality, AuthorisedUsers FROM %sFreeze WHERE Name = "%s"' % (self.database[0].type, individualDB))
indId, indName, indFullName, confidential, AuthorisedUsers = self.cursor.fetchall()[0]
if confidential == 1:
access_to_confidential_dataset = 0
#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['user']:
access_to_confidential_dataset = 1
else:
AuthorisedUsersList=AuthorisedUsers.split(',')
if AuthorisedUsersList.__contains__(self.userName):
access_to_confidential_dataset = 1
if not access_to_confidential_dataset:
#Error, No database selected
heading = "Search Result"
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
else:
heading = "Search Result"
detail = ['''The database has not been established yet, please
go back and SELECT at least one database.''']
self.error(heading=heading,detail=detail,error="No Database Selected")
return
self.database[0].getRISet()
self.databaseCrosses = [self.database[0].riset]
self.databaseCrossIds = [self.database[0].risetid]
self.singleCross = True
#XZ, August 24,2010: Since self.singleCross = True, it's safe to assign one species Id.
self.speciesId = webqtlDatabaseFunction.retrieveSpeciesId(self.cursor, self.database[0].riset)
###########################################
# make sure search from same type of databases
###########################################
dbTypes = map(lambda X: X.type, self.database)
self.dbType = dbTypes[0]
for item in dbTypes:
if item != self.dbType:
heading = "Search Result"
detail = ["Search can only be performed among the same type of databases"]
self.error(heading=heading,detail=detail,error="Error")
return
if self.dbType == "Publish":
self.searchField = ['Phenotype.Post_publication_description', 'Phenotype.Pre_publication_description', 'Phenotype.Pre_publication_abbreviation', 'Phenotype.Post_publication_abbreviation', 'Phenotype.Lab_code', 'Publication.PubMed_ID', 'Publication.Abstract', 'Publication.Title', 'Publication.Authors', 'PublishXRef.Id']
elif self.dbType == "ProbeSet":
self.searchField = ['Name','Description','Probe_Target_Description','Symbol','Alias','GenbankId', 'UniGeneId','RefSeq_TranscriptId']
elif self.dbType == "Geno":
self.searchField = ['Name','Chr']
###########################################
# Search Options
###########################################
self.matchwhole = fd.formdata.getfirst('matchwhole')
#split result into pages
self.pageNumber = fd.formdata.getfirst('pageno', '0')
try:
self.pageNumber = int(self.pageNumber)
except:
self.pageNumber = 0
###########################################
# Generate Mysql Query
###########################################
geneIdListQuery = fd.formdata.getfirst('geneId', '')
if geneIdListQuery:
geneIdListQuery = string.replace(geneIdListQuery, ",", " ")
geneIdListQuery = " geneId=%s" % string.join(string.split(geneIdListQuery), "-")
self.ANDkeyword = fd.formdata.getfirst('ANDkeyword', "")
_log.info("self.ANDkeyword[1]: " + self.ANDkeyword)
pubmedSearchObject = PubmedSearch.PubmedSearch(self.ANDkeyword, self.database[0].id)
self.ANDkeyword = pubmedSearchObject.getNewS()
_log.info("self.ANDkeyword[2]: " + self.ANDkeyword)
self.ORkeyword = fd.formdata.getfirst('ORkeyword', "")
_log.info("self.ORkeyword[1]: " + self.ORkeyword)
pubmedSearchObject = PubmedSearch.PubmedSearch(self.ORkeyword, self.database[0].id)
self.ORkeyword = pubmedSearchObject.getNewS()
_log.info("self.ORkeyword[2]: "+self.ORkeyword)
self.ORkeyword += geneIdListQuery
self.ANDkeyword = self.ANDkeyword.replace("\\", "").strip()
self.ORkeyword = self.ORkeyword.replace("\\", "").strip()
#user defined sort option
self.orderByUserInput = fd.formdata.getfirst('orderByUserInput', "").strip()
#default sort option if user have not defined
self.orderByDefalut = ""
#XZ, Dec/16/2010: I add examples to help understand this block of code. See details in function pattersearch.
#XZ: self._1mPattern examples: WIKI=xxx, RIF=xxx, GO:0045202
self._1mPattern = re.compile('\s*(\S+)\s*[:=]\s*([a-zA-Z-\+\d\.]+)\s*')
#XZ: self._2mPattern examples: Mean=(15.0 16.0), Range=(10 100), LRS=(Low_LRS_limit, High_LRS_limit), pvalue=(Low_limit, High_limit), Range=(10 100)
self._2mPattern = re.compile('\s*(\S+)\s*[=in]{1,2}\s*\(\s*([-\d\.]+)[, \t]+([-\d\.]+)[, \t]*([-\d\.]*)\s*\)')
#XZ: self._3mPattern examples: Position=(Chr1 98 104), Pos=(Chr1 98 104), Mb=(Chr1 98 104), CisLRS=(Low_LRS_limit, High_LRS_limit, Mb_buffer), TransLRS=(Low_LRS_limit, High_LRS_limit, Mb_buffer)
self._3mPattern = re.compile('\s*(\S+)\s*[=in]{1,2}\s*\(\s*[Cc][Hh][Rr]([^, \t]+)[, \t]+([-\d\.]+)[, \t]+([-\d\.]+)\s*\)')
#XZ: self._5mPattern examples: LRS=(Low_LRS_limit, High_LRS_limit, ChrNN, Mb_Low_Limit, Mb_High_Limit)
self._5mPattern = re.compile('\s*(\S+)\s*[=in]{1,2}\s*\(\s*([-\d\.]+)[, \t]+([-\d\.]+)[, \t]+[Cc][Hh][Rr]([^, \t]+)[, \t]+([-\d\.]+)[, \t]+([-\d\.]+)\s*\)')
#Error, No keyword input
if not (self.ORkeyword or self.ANDkeyword):
heading = "Search Result"
detail = ["Please make sure to enter either your search terms (genes, traits, markers), or advanced search commands."]
self.error(heading=heading,detail=detail,error="No search terms were entered")
return
#query clauses
self.ANDQuery = []
self.ORQuery = []
#descriptions, one for OR search, one for AND search
self.ANDDescriptionText = []
self.ORDescriptionText = []
if not self.normalSearch():
return
if not self.patternSearch():
return
if not self.assembleQuery():
return
self.nresults = self.executeQuery()
if len(self.database) > 1:
dbUrl = "Multiple phenotype databases"
dbUrlLink = " were"
else:
dbUrl = self.database[0].genHTML()
dbUrlLink = " was"
SearchText = HT.Blockquote('GeneNetwork searched the ', dbUrl, ' for all records ')
if self.ORkeyword2:
NNN = len(self.ORkeyword2)
if NNN > 1:
SearchText.append(' that match the terms ')
else:
SearchText.append(' that match the term ')
for j, term in enumerate(self.ORkeyword2):
SearchText.append(HT.U(term))
if NNN > 1 and j < NNN-2:
SearchText.append(", ")
elif j == NNN-2:
SearchText.append(", or ")
else:
pass
if self.ORDescriptionText:
if self.ORkeyword2:
SearchText.append("; ")
else:
SearchText.append(" ")
for j, item in enumerate(self.ORDescriptionText):
SearchText.append(item)
if j < len(self.ORDescriptionText) -1:
SearchText.append(";")
if (self.ORkeyword2 or self.ORDescriptionText) and (self.ANDkeyword2 or self.ANDDescriptionText):
SearchText.append("; ")
if self.ANDkeyword2:
if (self.ORkeyword2 or self.ORDescriptionText):
SearchText.append(' records')
NNN = len(self.ANDkeyword2)
if NNN > 1:
SearchText.append(' that match the terms ')
else:
SearchText.append(' that match the term ')
for j, term in enumerate(self.ANDkeyword2):
SearchText.append(HT.U(term))
if NNN > 1 and j < NNN-2:
SearchText.append(", ")
elif j == NNN-2:
SearchText.append(", and ")
else:
pass
if self.ANDDescriptionText:
if self.ANDkeyword2:
SearchText.append(" and ")
else:
SearchText.append(" ")
for j, item in enumerate(self.ANDDescriptionText):
SearchText.append(item)
if j < len(self.ANDDescriptionText) -1:
SearchText.append(" and ")
SearchText.append(". ")
if self.nresults == 0:
heading = "Search Result"
detail = ["Sorry, GeneNetwork did not find any records matching your request. Please check the syntax or try the ANY rather than the ALL field."]
self.error(heading=heading,intro = SearchText.contents,detail=detail,error="Not Found")
return
elif self.nresults == 1:
SearchText.append(HT.P(), 'GeneNetwork found one record that matches your request. To study this record, click on its text below. To add this record to your Selection window, use the checkbox and then click the ', HT.Strong('Add to Collection'),' button. ')
elif self.nresults >= 1 and self.nresults <= self.maxReturn:
SearchText.append(HT.P(), 'GeneNetwork found a total of ', HT.Span(self.nresults, Class='fwb cr'), ' records. To study any one of these records, click on its ID below. To add one or more records to your Selection window, use the checkbox and then click the ' , HT.Strong('Add to Collection'),' button. ')
else:
SearchText.append(' A total of ',HT.Span(self.nresults, Class='fwb cr'), ' records were found.')
heading = "Search Result"
# Modified by Hongqiang Li
# detail = ["The terms you entered match %d records. Please modify your search to generate %d or fewer matches, or review " % (self.nresults, self.maxReturn), HT.Href(text='Search Help', target='_blank', url='http://web2qtl.utmem.edu/searchHelp.html', Class='fs14'), " to learn more about syntax and the use of wildcard characters."]
detail = ["The terms you entered match %d records. Please modify your search to generate %d or fewer matches, or review " % (self.nresults, self.maxReturn), HT.Href(text='Search Help', target='_blank', url='%s/searchHelp.html' % webqtlConfig.PORTADDR, Class='fs14'), " to learn more about syntax and the use of wildcard characters."]
#
self.error(heading=heading,intro = SearchText.contents,detail=detail,error="Over %d" % self.maxReturn)
return
TD_LR.append(HT.Paragraph('Search Results', Class="title"), SearchText)
self.genSearchResultTable(TD_LR)
self.dict['body'] = str(TD_LR)
self.dict['js1'] = ''
self.dict['js2'] = 'onLoad="pageOffset()"'
self.dict['layer'] = self.generateWarningLayer()
def genSearchResultTable(self, TD_LR):
pageTable = HT.TableLite(cellSpacing=2,cellPadding=0,width="100%",border=0)
lastone = False
for i, item in enumerate(self.results):
if not item:
continue
lastone = False
traitList = []
for k, item2 in enumerate(item):
j, ProbeSetID = item2[:2]
thisTrait = webqtlTrait(db=self.database[j], name=ProbeSetID, cursor=self.cursor)
traitList.append(thisTrait)
##############
# Excel file #
##############
filename= webqtlUtil.genRandStr("Search_")
xlsUrl = HT.Input(type='button', value = 'Download Table', onClick= "location.href='/tmp/%s.xls'" % filename, Class='button')
# Create a new Excel workbook
workbook = xl.Writer('%s.xls' % (webqtlConfig.TMPDIR+filename))
headingStyle = workbook.add_format(align = 'center', bold = 1, border = 1, size=13, fg_color = 0x1E, color="white")
#XZ, 3/18/2010: pay attention to the line number of header in this file. As of today, there are 7 lines.
worksheet = self.createExcelFileWithTitleAndFooter(workbook=workbook, db=thisTrait.db, returnNumber=len(traitList))
newrow = 7
tbl = HT.TableLite(cellSpacing=2,cellPadding=0,width="90%",border=0)
#seq = self.pageNumber*self.NPerPage+1 //Edited out because we show all results in one page now - Zach 2/22/11
seq = 1
RISet = self.databaseCrosses[i]
thisFormName = 'showDatabase'+RISet
selectall = HT.Href(url="#", onClick="checkAll(document.getElementsByName('%s')[0]);" % thisFormName)
selectall_img = HT.Image("/images/select_all2_final.jpg", name="selectall", alt="Select All", title="Select All", style="border:none;")
selectall.append(selectall_img)
reset = HT.Href(url="#", onClick="checkNone(document.getElementsByName('%s')[0]);" % thisFormName)
reset_img = HT.Image("/images/select_none2_final.jpg", alt="Select None", title="Select None", style="border:none;")
reset.append(reset_img)
selectinvert = HT.Href(url="#", onClick="checkInvert(document.getElementsByName('%s')[0]);" % thisFormName)
selectinvert_img = HT.Image("/images/invert_selection2_final.jpg", name="selectinvert", alt="Invert Selection", title="Invert Selection", style="border:none;")
selectinvert.append(selectinvert_img)
addselect = HT.Href(url="#")
addselect_img = HT.Image("/images/add_collection1_final.jpg", name="addselect", alt="Add To Collection", title="Add To Collection", style="border:none;")
addselect.append(addselect_img)
optionsTable = HT.TableLite(cellSpacing=2,cellPadding=0,width="20%",border=0)
optionsRow = HT.TR(HT.TD(selectall, width="25%"), HT.TD(reset, width="25%"), HT.TD(selectinvert, width="25%"), HT.TD(addselect, width="25%"))
labelsRow = HT.TR(HT.TD(" "*2,"Select", width="25%"), HT.TD(" ","Deselect", width="255"), HT.TD(" "*3,"Invert", width="25%"), HT.TD(" "*4,"Add", width="25%"))
optionsTable.append(optionsRow, labelsRow)
pageTable.append(HT.TR(HT.TD(optionsTable)), HT.TR(HT.TD(xlsUrl, height=40)))
tblobj = {}
mainfmName = thisFormName
species = webqtlDatabaseFunction.retrieveSpecies(cursor=self.cursor, RISet=RISet)
if thisTrait.db.type=="Geno":
tblobj['header'] = self.getTableHeaderForGeno(worksheet=worksheet, newrow=newrow, headingStyle=headingStyle)
newrow += 1
sortby = self.getSortByValue(datasetType="Geno")
tblobj['body'] = self.getTableBodyForGeno(traitList=traitList, formName=mainfmName, worksheet=worksheet, newrow=newrow)
workbook.close()
objfile = open('%s.obj' % (webqtlConfig.TMPDIR+filename), 'wb')
cPickle.dump(tblobj, objfile)
objfile.close()
div = HT.Div(webqtlUtil.genTableObj(tblobj, filename, sortby), Id="sortable")
pageTable.append(HT.TR(HT.TD(div)))
elif thisTrait.db.type=="Publish":
tblobj['header'] = self.getTableHeaderForPublish(worksheet=worksheet, newrow=newrow, headingStyle=headingStyle)
newrow += 1
sortby = self.getSortByValue(datasetType="Publish")
tblobj['body'] = self.getTableBodyForPublish(traitList=traitList, formName=mainfmName, worksheet=worksheet, newrow=newrow, species=species)
workbook.close()
objfile = open('%s.obj' % (webqtlConfig.TMPDIR+filename), 'wb')
cPickle.dump(tblobj, objfile)
objfile.close()
div = HT.Div(webqtlUtil.genTableObj(tblobj, filename, sortby), Id="sortable")
pageTable.append(HT.TR(HT.TD(div)))
elif thisTrait.db.type=="ProbeSet":
tblobj['header'] = self.getTableHeaderForProbeSet(worksheet=worksheet, newrow=newrow, headingStyle=headingStyle)
newrow += 1
sortby = self.getSortByValue(datasetType="ProbeSet")
tblobj['body'] = self.getTableBodyForProbeSet(traitList=traitList, formName=mainfmName, worksheet=worksheet, newrow=newrow, species=species)
workbook.close()
objfile = open('%s.obj' % (webqtlConfig.TMPDIR+filename), 'wb')
cPickle.dump(tblobj, objfile)
objfile.close()
div = HT.Div(webqtlUtil.genTableObj(tblobj, filename, sortby), Id="sortable")
pageTable.append(HT.TR(HT.TD(div)))
traitForm = HT.Form(cgi= os.path.join(webqtlConfig.CGIDIR, webqtlConfig.SCRIPTFILE), enctype='multipart/form-data', name=thisFormName, submit=HT.Input(type='hidden'))
hddn = {'FormID':'showDatabase','ProbeSetID':'_','database':'_','CellID':'_','RISet':RISet}
hddn['incparentsf1']='ON'
for key in hddn.keys():
traitForm.append(HT.Input(name=key, value=hddn[key], type='hidden'))
traitForm.append(HT.P(),pageTable)
TD_LR.append(traitForm)
if len(self.results) > 1 and i < len(self.results) - 1:
lastone = True
if lastone:
TD_LR.contents.pop()
def executeQuery(self):
##construct sorting
if self.dbType == "Publish":
sortQuery = " order by Publication_PubMed_ID desc, Phenotype_Name, thistable"
elif self.dbType == "Geno":
if not self.orderByUserInput:
if self.orderByDefalut:
self.orderByUserInput = self.orderByDefalut
else:
self.orderByUserInput = "POSITION"
if self.orderByUserInput.upper() in ["POS", "POSITION", "MB"]:
self.orderByUserInput = "POSITION"
else:
pass
self.orderByUserInput = self.orderByUserInput.upper()
self.orderByUserInputOrig = self.orderByUserInput[:]
if self.orderByUserInput == "NAME":
sortQuery = " order by Geno_Name, Geno_chr_num, Geno_Mb"
elif self.orderByUserInput == "SOURCE":
sortQuery = " order by Geno_Source2, Geno_chr_num, Geno_Mb"
else:
sortQuery = " order by Geno_chr_num, Geno_Mb"
#ProbeSet
else:
if not self.orderByUserInput:
if self.orderByDefalut:
self.orderByUserInput = self.orderByDefalut
else:
self.orderByUserInput = "POSITION"
self.orderByUserInput = self.orderByUserInput.upper()
self.orderByUserInputOrig = self.orderByUserInput[:]
#XZ: 8/18/2009: "POSITION-"
if self.orderByUserInput[-1] == '-':
self.orderByUserInput = self.orderByUserInput[:-1]
sortDesc = 'desc'
else:
sortDesc = ''
if self.orderByUserInput in ["MEAN", "LRS", "PVALUE"]:
#sortQuery = " order by T%s %s, TNAME, thistable desc" % (self.orderByUserInput, sortDesc)
sortQuery = " order by T%s desc, TNAME, thistable desc" % self.orderByUserInput
elif self.orderByUserInput in ["POS", "POSITION", "MB"]:
sortQuery = " order by TCHR_NUM %s, TMB %s, TNAME, thistable desc" % (sortDesc, sortDesc)
elif self.orderByUserInput == 'SYMBOL':
sortQuery = " order by TSYMBOL, thistable desc"
else:
sortQuery = " order by TNAME_NUM, thistable desc"
if self.singleCross:
if len(self.query) > 1:
searchQuery = map(lambda X:'(%s)' % X, self.query)
searchQuery = string.join(searchQuery, ' UNION ALL ')
else:
searchQuery = self.query[0]
searchQuery += sortQuery
#searchCountQuery retrieve all the results
searchCountQuery = [searchQuery]
#searchQuery = searchQuery + " limit %d,%d" % (self.pageNumber*self.NPerPage, self.NPerPage) // We removed the page limit - Zach 2/22/11
searchQuery = [searchQuery]
else:
searchCountQuery = searchQuery = map(lambda X: X+sortQuery, self.query)
allResults = []
self.results = []
for item in searchCountQuery:
start_time = datetime.datetime.now()
_log.info("111 Executing query: %s"%(item))
self.cursor.execute(item)
allResults.append(self.cursor.fetchall())
end_time = datetime.datetime.now()
_log.info("Total time: %s"%(end_time-start_time))
_log.info("Done executing queries")
#searchCountQuery retrieve all the results, for counting use only
if searchCountQuery != searchQuery:
for item in searchQuery:
_log.info("222 Executing query: %s"%(item))
self.cursor.execute(item)
self.results.append(self.cursor.fetchall())
else:
self.results = allResults
nresults = reduce(lambda Y,X:len(X)+Y, allResults, 0)
return nresults
def assembleQuery(self):
self.query = []
if self.ANDQuery or self.ORQuery:
clause = self.ORQuery[:]
for j, database in enumerate(self.database):
if self.ANDQuery:
clause.append(" (%s) " % string.join(self.ANDQuery, " AND "))
newclause = []
for item in clause:
##need to retrieve additional field which won't be used
##in the future, for sorting purpose only
if self.dbType == "Publish":
if item.find("Geno.name") < 0:
incGenoTbl = ""
else:
incGenoTbl = " Geno, "
newclause.append("SELECT %d, PublishXRef.Id, PublishFreeze.createtime as thistable, Publication.PubMed_ID as Publication_PubMed_ID, Phenotype.Post_publication_description as Phenotype_Name FROM %s PublishFreeze, Publication, PublishXRef, Phenotype WHERE PublishXRef.InbredSetId = %d and %s and PublishXRef.PhenotypeId = Phenotype.Id and PublishXRef.PublicationId = Publication.Id and PublishFreeze.Id = %d" % (j, incGenoTbl, self.databaseCrossIds[j], item, database.id))
elif self.dbType == "ProbeSet":
if item.find("GOgene") < 0:
incGoTbl = ""
else:
incGoTbl = " ,db_GeneOntology.term as GOterm, db_GeneOntology.association as GOassociation, db_GeneOntology.gene_product as GOgene_product "
if item.find("Geno.name") < 0:
incGenoTbl = ""
else:
incGenoTbl = " Geno, "
if item.find("GeneRIF_BASIC.") < 0:
incGeneRIFTbl = ""
else:
incGeneRIFTbl = " GeneRIF_BASIC, "
if item.find("GeneRIF.") < 0:
incGeneRIFTbl += ""
else:
incGeneRIFTbl += " GeneRIF, "
newclause.append("""SELECT distinct %d, ProbeSet.Name as TNAME, 0 as thistable,
ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS, ProbeSetXRef.PVALUE as TPVALUE,
ProbeSet.Chr_num as TCHR_NUM, ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL,
ProbeSet.name_num as TNAME_NUM FROM %s%s ProbeSetXRef, ProbeSet %s
WHERE %s and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = %d
""" % (j, incGeneRIFTbl, incGenoTbl, incGoTbl, item, database.id))
elif self.dbType == "Geno":
newclause.append("SELECT %d, Geno.Name, GenoFreeze.createtime as thistable, Geno.Name as Geno_Name, Geno.Source2 as Geno_Source2, Geno.chr_num as Geno_chr_num, Geno.Mb as Geno_Mb FROM GenoXRef, GenoFreeze, Geno WHERE %s and Geno.Id = GenoXRef.GenoId and GenoXRef.GenoFreezeId = GenoFreeze.Id and GenoFreeze.Id = %d"% (j, item, database.id))
else:
pass
searchQuery = map(lambda X:'(%s)' % X, newclause)
searchQuery = string.join(searchQuery, ' UNION ')
self.query.append(searchQuery)
return 1
else:
heading = "Search Result"
detail = ["No keyword was entered for this search, please go back and enter your keyword."]
self.error(heading=heading,detail=detail,error="No Keyword")
return 0
def normalSearch(self):
self.ANDkeyword2 = re.sub(self._1mPattern, '', self.ANDkeyword)
self.ANDkeyword2 = re.sub(self._2mPattern, '', self.ANDkeyword2)
self.ANDkeyword2 = re.sub(self._3mPattern, '', self.ANDkeyword2)
self.ANDkeyword2 = re.sub(self._5mPattern, '', self.ANDkeyword2)
##remove remain parethesis, could be input with syntax error
self.ANDkeyword2 = re.sub(re.compile('\s*\([\s\S]*\)'), '', self.ANDkeyword2)
self.ANDkeyword2 = self.encregexp(self.ANDkeyword2)
self.ORkeyword2 = re.sub(self._1mPattern, '', self.ORkeyword)
self.ORkeyword2 = re.sub(self._2mPattern, '', self.ORkeyword2)
self.ORkeyword2 = re.sub(self._3mPattern, '', self.ORkeyword2)
self.ORkeyword2 = re.sub(self._5mPattern, '', self.ORkeyword2)
##remove remain parethesis, could be input with syntax error
self.ORkeyword2 = re.sub(re.compile('\s*\([\s\S]*\)'), '', self.ORkeyword2)
self.ORkeyword2 = self.encregexp(self.ORkeyword2)
if self.ORkeyword2 or self.ANDkeyword2:
ANDFulltext = []
ORFulltext = []
for k, item in enumerate(self.ORkeyword2 + self.ANDkeyword2):
self.nkeywords += 1
if k >=len(self.ORkeyword2):
query = self.ANDQuery
DescriptionText = self.ANDDescriptionText
clausejoin = ' OR '
fulltext = ANDFulltext
else:
query = self.ORQuery
DescriptionText = self.ORDescriptionText
clausejoin = ' OR '
fulltext = ORFulltext
if self.dbType == "ProbeSet" and item.find('.') < 0 and item.find('\'') < 0:
fulltext.append(item)
else:
if self.matchwhole and item.find("'") < 0:
item = "[[:<:]]"+ item+"[[:>:]]"
clause2 = []
for field in self.searchField:
if self.dbType == "Publish":
clause2.append("%s REGEXP \"%s\"" % (field,item))
else:
clause2.append("%s REGEXP \"%s\"" % ("%s.%s" % (self.dbType,field),item))
clauseItem = "(%s)" % string.join(clause2, clausejoin)
query.append(" (%s) " % clauseItem)
if ANDFulltext:
clauseItem = " MATCH (ProbeSet.Name,ProbeSet.description,ProbeSet.symbol,alias,GenbankId, UniGeneId, Probe_Target_Description) AGAINST ('+%s' IN BOOLEAN MODE) " % string.join(ANDFulltext, " +")
self.ANDQuery.append(" (%s) " % clauseItem)
if ORFulltext:
clauseItem = " MATCH (ProbeSet.Name,ProbeSet.description,ProbeSet.symbol,alias,GenbankId, UniGeneId, Probe_Target_Description) AGAINST ('%s' IN BOOLEAN MODE) " % string.join(ORFulltext, " ")
self.ORQuery.append(" (%s) " % clauseItem)
else:
pass
return 1
def encregexp(self,str):
if not str:
return []
else:
wildcardkeyword = str.strip()
wildcardkeyword = string.replace(wildcardkeyword,',',' ')
wildcardkeyword = string.replace(wildcardkeyword,';',' ')
wildcardkeyword = wildcardkeyword.split()
NNN = len(wildcardkeyword)
for i in range(NNN):
keyword = wildcardkeyword[i]
keyword = string.replace(keyword,"*",".*")
keyword = string.replace(keyword,"?",".")
wildcardkeyword[i] = keyword#'[[:<:]]'+ keyword+'[[:>:]]'
return wildcardkeyword
def patternSearch(self):
# Lei Yan
##Process Inputs
m1_AND = self._1mPattern.findall(self.ANDkeyword)
m2_AND = self._2mPattern.findall(self.ANDkeyword)
m3_AND = self._3mPattern.findall(self.ANDkeyword)
m5_AND = self._5mPattern.findall(self.ANDkeyword)
m1_OR = self._1mPattern.findall(self.ORkeyword)
m2_OR = self._2mPattern.findall(self.ORkeyword)
m3_OR = self._3mPattern.findall(self.ORkeyword)
m5_OR = self._5mPattern.findall(self.ORkeyword)
#pattern search
if m1_AND or m1_OR or m2_AND or m2_OR or m3_AND or m3_OR or m5_AND or m5_OR:
self.orderByDefalut = 'PROBESETID'
_1Cmds = map(string.upper, map(lambda x:x[0], m1_AND + m1_OR))
_2Cmds = map(string.upper, map(lambda x:x[0], m2_AND + m2_OR))
_3Cmds = map(string.upper, map(lambda x:x[0], m3_AND + m3_OR))
_5Cmds = map(string.upper, map(lambda x:x[0], m5_AND + m5_OR))
self.nkeywords += len(_1Cmds) + len(_2Cmds) + len(_3Cmds)
if self.dbType == "Publish" and \
( (_2Cmds and reduce(lambda x, y: (y not in ["LRS"]) or x, _2Cmds, False))\
or (_5Cmds and reduce(lambda x, y: (y not in ["LRS"]) or x, _5Cmds, False)) ):
heading = "Search Result"
detail = ["Pattern search is not available for phenotype databases at this time."]
self.error(heading=heading,detail=detail,error="Error")
return 0
elif self.dbType == "ProbeSet" and \
((_2Cmds and reduce(lambda x, y: (y not in ["MEAN", "LRS", "PVALUE", "TRANSLRS", "CISLRS", "RANGE", "H2"]) or x, _2Cmds, False))\
or (_3Cmds and reduce(lambda x, y: (y not in ["POS", "POSITION", "MB"]) or x, _3Cmds, False))\
or (_5Cmds and reduce(lambda x, y: (y not in ["LRS"]) or x, _5Cmds, False))\
or (_1Cmds and reduce(lambda x, y: (y not in ["FLAG", "STRAND_PROBE", "STRAND_GENE", "GO", "WIKI", "RIF", "GENEID"]) or x, _1Cmds, False))):
heading = "Search Result"
detail = ["You entered at least one incorrect search command."]
self.error(heading=heading,detail=detail,error="Error")
return 0
elif self.dbType == "Geno" and (_1Cmds or _2Cmds or _5Cmds or (_3Cmds and reduce(lambda x, y: (y not in ["POS", "POSITION", "MB"]) or x, _3Cmds, False)) ):
heading = "Search Result"
detail = ["You entered at least one incorrect search command."]
self.error(heading=heading,detail=detail,error="Error")
return 0
else:
for k, item in enumerate(m1_OR+m1_AND):
if k >=len(m1_OR):
query = self.ANDQuery
DescriptionText = self.ANDDescriptionText
else:
query = self.ORQuery
DescriptionText = self.ORDescriptionText
if item[1] == '-':
strandName = 'minus'
elif item[1] == '+':
strandName = 'plus'
else:
strandName = item[1]
if item[0].upper() in ("FLAG"):
clauseItem = " %s.%s = %s " % (self.dbType, item[0], item[1])
DescriptionText.append(HT.Span(' with ', HT.U('FLAG'), ' equal to ', item[1]))
elif item[0].upper() in ("WIKI"):
clauseItem = " %s.symbol = GeneRIF.symbol and GeneRIF.versionId=0 and GeneRIF.display>0 and (GeneRIF.comment REGEXP \"%s\" or GeneRIF.initial = \"%s\") " % (self.dbType, "[[:<:]]"+ item[1]+"[[:>:]]", item[1])
DescriptionText.append(HT.Span(' with GeneWiki contains ', HT.U(item[1])))
elif item[0].upper() in ("RIF"):
clauseItem = " %s.symbol = GeneRIF_BASIC.symbol and MATCH (GeneRIF_BASIC.comment) AGAINST ('+%s' IN BOOLEAN MODE) " % (self.dbType, item[1])
DescriptionText.append(HT.Span(' with GeneRIF contains ', HT.U(item[1])))
elif item[0].upper() in ("GENEID"):
clauseItem = " %s.GeneId in ( %s ) " % (self.dbType, string.replace(item[1], '-', ', '))
DescriptionText.append(HT.Span(' with Entrez Gene ID in ', HT.U(string.replace(item[1], '-', ', '))))
elif item[0].upper() in ("GO"):
Field = 'GOterm.acc'
Id = 'GO:'+('0000000'+item[1])[-7:]
Statements = '%s.symbol=GOgene_product.symbol and GOassociation.gene_product_id=GOgene_product.id and GOterm.id=GOassociation.term_id' % (self.dbType);
clauseItem = " %s = '%s' and %s " % (Field, Id, Statements)
#self.incGoTbl = " ,db_GeneOntology.term as GOterm, db_GeneOntology.association as GOassociation, db_GeneOntology.gene_product as GOgene_product "
DescriptionText.append(HT.Span(' with ', HT.U('GO'), ' ID equal to ', Id))
else:
clauseItem = " %s.%s = '%s' " % (self.dbType, item[0], item[1])
if item[0].upper() in ["STRAND_PROBE"]:
DescriptionText.append(' with probe on the %s strand' % strandName)
elif item[0].upper() in ["STRAND_GENE"]:
DescriptionText.append(' with gene on the %s strand' % strandName)
else:
pass
query.append(" (%s) " % clauseItem)
for k, item in enumerate(m2_OR+m2_AND):
if k >=len(m2_OR):
query = self.ANDQuery
DescriptionText = self.ANDDescriptionText
else:
query = self.ORQuery
DescriptionText = self.ORDescriptionText
itemCmd = item[0]
lowerLimit = float(item[1])
upperLimit = float(item[2])
if itemCmd.upper() in ("TRANSLRS", "CISLRS"):
if item[3]:
mthresh = float(item[3])
clauseItem = " %sXRef.LRS > %2.7f and %sXRef.LRS < %2.7f " % \
(self.dbType, min(lowerLimit, upperLimit), self.dbType, max(lowerLimit, upperLimit))
if itemCmd.upper() == "CISLRS":
clauseItem += """ and %sXRef.Locus = Geno.name and Geno.SpeciesId = %s and %s.Chr = Geno.Chr and ABS(%s.Mb-Geno.Mb) < %2.7f """ % (self.dbType, self.speciesId, self.dbType, self.dbType, mthresh)
DescriptionText.append(HT.Span(' with a ', HT.U('cis-QTL'), ' having an LRS between %g and %g using a %g Mb exclusion buffer' % (min(lowerLimit, upperLimit), max(lowerLimit, upperLimit), mthresh)))
else:
clauseItem += """ and %sXRef.Locus = Geno.name and Geno.SpeciesId = %s and (%s.Chr != Geno.Chr or (%s.Chr != Geno.Chr and ABS(%s.Mb-Geno.Mb) > %2.7f)) """ % (self.dbType, self.speciesId, self.dbType, self.dbType, self.dbType, mthresh)
DescriptionText.append(HT.Span(' with a ', HT.U('trans-QTL'), ' having an LRS between %g and %g using a %g Mb exclusion buffer' % (min(lowerLimit, upperLimit), max(lowerLimit, upperLimit), mthresh)))
query.append(" (%s) " % clauseItem)
self.orderByDefalut = "LRS"
else:
pass
elif itemCmd.upper() in ("RANGE"):
#XZ, 03/05/2009: Xiaodong changed Data to ProbeSetData
clauseItem = " (select Pow(2, max(value) -min(value)) from ProbeSetData where Id = ProbeSetXRef.dataId) > %2.7f and (select Pow(2, max(value) -min(value)) from ProbeSetData where Id = ProbeSetXRef.dataId) < %2.7f " % (min(lowerLimit, upperLimit), max(lowerLimit, upperLimit))
query.append(" (%s) " % clauseItem)
DescriptionText.append(HT.Span(' with a range of expression that varied between %g and %g' % (min(lowerLimit, upperLimit), max(lowerLimit, upperLimit)), " (fold difference)"))
else:
clauseItem = " %sXRef.%s > %2.7f and %sXRef.%s < %2.7f " % \
(self.dbType, itemCmd, min(lowerLimit, upperLimit), self.dbType, itemCmd, max(lowerLimit, upperLimit))
query.append(" (%s) " % clauseItem)
self.orderByDefalut = itemCmd
DescriptionText.append(HT.Span(' with ', HT.U(itemCmd), ' between %g and %g' % (min(lowerLimit, upperLimit), max(lowerLimit, upperLimit))))
for k, item in enumerate(m3_OR+m3_AND):
if k >=len(m3_OR):
query = self.ANDQuery
DescriptionText = self.ANDDescriptionText
else:
query = self.ORQuery
DescriptionText = self.ORDescriptionText
itemCmd = item[0]
chrsch = item[1]
lowerLimit = float(item[2])
upperLimit = float(item[3])
fname = 'target genes'
if self.dbType == "ProbeSet":
clauseItem = " %s.Chr = '%s' and %s.Mb > %2.7f and %s.Mb < %2.7f " % \
(self.dbType, chrsch, self.dbType, min(lowerLimit, upperLimit), self.dbType, max(lowerLimit, upperLimit))
elif self.dbType == "Geno":
fname = 'loci'
clauseItem = " %s.Chr = '%s' and %s.Mb > %2.7f and %s.Mb < %2.7f " % \
(self.dbType, chrsch, self.dbType, min(lowerLimit, upperLimit), self.dbType, max(lowerLimit, upperLimit))
else:
continue
query.append(" (%s) " % clauseItem)
self.orderByDefalut = itemCmd
DescriptionText.append(HT.Span(' with ', HT.U('target genes'), ' on chromosome %s between %g and %g Mb' % \
(chrsch, min(lowerLimit, upperLimit), max(lowerLimit, upperLimit))))
for k, item in enumerate(m5_OR+m5_AND):
if k >=len(m5_OR):
query = self.ANDQuery
DescriptionText = self.ANDDescriptionText
else:
query = self.ORQuery
DescriptionText = self.ORDescriptionText
itemCmd = item[0]
lowerLimit = float(item[1])
upperLimit = float(item[2])
chrsch = item[3]
MblowerLimit = float(item[4])
MbupperLimit = float(item[5])
if self.dbType == "ProbeSet" or self.dbType == "Publish":
clauseItem = " %sXRef.LRS > %2.7f and %sXRef.LRS < %2.7f " % \
(self.dbType, min(lowerLimit, upperLimit), self.dbType, max(lowerLimit, upperLimit))
clauseItem += " and %sXRef.Locus = Geno.name and Geno.SpeciesId = %s and Geno.Chr = '%s' and Geno.Mb > %2.7f and Geno.Mb < %2.7f" \
% (self.dbType, self.speciesId, chrsch, min(MblowerLimit, MbupperLimit), max(MblowerLimit, MbupperLimit))
query.append(" (%s) " % clauseItem)
self.orderByDefalut = "MB"
DescriptionText.append(HT.Span(' with ', HT.U('LRS'), ' between %g and %g' % \
(min(lowerLimit, upperLimit), max(lowerLimit, upperLimit)), \
' on chromosome %s between %g and %g Mb' % \
(chrsch, min(MblowerLimit, MbupperLimit), max(MblowerLimit, MbupperLimit))))
pass
return 1
def generateWarningLayer(self):
layerString = """
Sort Table
|
Resorting this table
|
|
"""
return layerString
def getTableHeaderForGeno(self, worksheet=None, newrow=None, headingStyle=None):
tblobj_header = []
className = "fs13 fwb ffl b1 cw cbrb"
tblobj_header = [[THCell(HT.TD(' ', Class=className), sort=0),
THCell(HT.TD('Record', HT.BR(), 'ID', HT.BR(), Class=className), text='record_id', idx=1),
THCell(HT.TD('Location', HT.BR(), 'Chr and Mb', HT.BR(), Class=className), text='location', idx=2)]]
for ncol, item in enumerate(['Record ID', 'Location (Chr, Mb)']):
worksheet.write([newrow, ncol], item, headingStyle)
worksheet.set_column([ncol, ncol], 2*len(item))
return tblobj_header
def getTableBodyForGeno(self, traitList, formName=None, worksheet=None, newrow=None):
tblobj_body = []
className = "fs12 fwn ffl b1 c222"
for thisTrait in traitList:
tr = []
if not thisTrait.haveinfo:
thisTrait.retrieveInfo()
trId = str(thisTrait)
tr.append(TDCell(HT.TD(HT.Input(type="checkbox", Class="checkbox", name="searchResult",value=trId, onClick="highlight(this)"), nowrap="on", Class=className), text=trId))
tr.append(TDCell(HT.TD(HT.Href(text=thisTrait.name,url="javascript:showDatabase3('%s','%s','%s','')" % (formName, thisTrait.db.name, thisTrait.name), Class="fs12 fwn ffl"),align="left", Class=className), text=thisTrait.name, val=thisTrait.name.upper()))
#XZ: trait_location_value is used for sorting
trait_location_repr = 'N/A'
trait_location_value = 1000000
if thisTrait.chr and thisTrait.mb:
try:
trait_location_value = int(thisTrait.chr)*1000 + thisTrait.mb
except:
if thisTrait.chr.upper() == 'X':
trait_location_value = 20*1000 + thisTrait.mb
else:
trait_location_value = ord(str(thisTrait.chr).upper()[0])*1000 + thisTrait.mb
trait_location_repr = 'Chr%s: %.6f' % (thisTrait.chr, float(thisTrait.mb) )
tr.append(TDCell(HT.TD(trait_location_repr, Class="fs12 fwn b1 c222", nowrap="on"), trait_location_repr, trait_location_value))
tblobj_body.append(tr)
for ncol, item in enumerate([thisTrait.name, trait_location_repr]):
worksheet.write([newrow, ncol], item)
newrow += 1
return tblobj_body
def getTableHeaderForPublish(self, worksheet=None, newrow=None, headingStyle=None):
tblobj_header = []
className = "fs13 fwb ffl b1 cw cbrb"
tblobj_header = [[THCell(HT.TD(' ', Class=className, nowrap="on"), sort=0),
THCell(HT.TD('Record',HT.BR(), 'ID',HT.BR(), Class=className, nowrap="on"), text="recond_id", idx=1),
THCell(HT.TD('Phenotype',HT.BR(),HT.BR(), Class=className, nowrap="on"), text="pheno", idx=2),
THCell(HT.TD('Authors',HT.BR(),HT.BR(), Class=className, nowrap="on"), text="auth", idx=3),
THCell(HT.TD('Year',HT.BR(),HT.BR(), Class=className, nowrap="on"), text="year", idx=4),
THCell(HT.TD('Max',HT.BR(), 'LRS', HT.BR(), Class="fs13 fwb ffl b1 cw cbrb", nowrap="on"), text="lrs", idx=5),
THCell(HT.TD('Max LRS Location',HT.BR(),'Chr and Mb',HT.BR(), Class="fs13 fwb ffl b1 cw cbrb", nowrap="on"), text="lrs_location", idx=6)]]
for ncol, item in enumerate(["Record", "Phenotype", "Authors", "Year", "Pubmed Id", "Max LRS", "Max LRS Location (Chr: Mb)"]):
worksheet.write([newrow, ncol], item, headingStyle)
worksheet.set_column([ncol, ncol], 2*len(item))
return tblobj_header
def getTableBodyForPublish(self, traitList, formName=None, worksheet=None, newrow=None, species=''):
tblobj_body = []
className = "fs12 fwn b1 c222"
for thisTrait in traitList:
tr = []
if not thisTrait.haveinfo:
thisTrait.retrieveInfo(QTL=1)
trId = str(thisTrait)
tr.append(TDCell(HT.TD(HT.Input(type="checkbox", Class="checkbox", name="searchResult",value=trId, onClick="highlight(this)"), nowrap="on", Class=className), text=trId))
tr.append(TDCell(HT.TD(HT.Href(text=thisTrait.name,url="javascript:showDatabase3('%s','%s','%s','')" % (formName, thisTrait.db.name, thisTrait.name), Class="fs12 fwn"), nowrap="yes",align="center", Class=className),str(thisTrait.name), thisTrait.name))
PhenotypeString = thisTrait.post_publication_description
if thisTrait.confidential:
if not webqtlUtil.hasAccessToConfidentialPhenotypeTrait(privilege=self.privilege, userName=self.userName, authorized_users=thisTrait.authorized_users):
PhenotypeString = thisTrait.pre_publication_description
tr.append(TDCell(HT.TD(PhenotypeString, Class=className), PhenotypeString, PhenotypeString.upper()))
tr.append(TDCell(HT.TD(thisTrait.authors, Class="fs12 fwn b1 c222 fsI"),thisTrait.authors, thisTrait.authors.strip().upper()))
try:
PubMedLinkText = myear = repr = int(thisTrait.year)
except:
PubMedLinkText = repr = "N/A"
myear = 0
if thisTrait.pubmed_id:
PubMedLink = HT.Href(text= repr,url= webqtlConfig.PUBMEDLINK_URL % thisTrait.pubmed_id,target='_blank', Class="fs12 fwn")
else:
PubMedLink = repr
tr.append(TDCell(HT.TD(PubMedLink, Class=className, align='center'), repr, myear))
#LRS and its location
LRS_score_repr = 'N/A'
LRS_score_value = 0
LRS_location_repr = 'N/A'
LRS_location_value = 1000000
LRS_flag = 1
if thisTrait.lrs:
LRS_score_repr = '%3.1f' % thisTrait.lrs
LRS_score_value = thisTrait.lrs
tr.append(TDCell(HT.TD(LRS_score_repr, Class=className), LRS_score_repr, LRS_score_value))
self.cursor.execute("""
select Geno.Chr, Geno.Mb from Geno, Species
where Species.Name = '%s' and
Geno.Name = '%s' and
Geno.SpeciesId = Species.Id
""" % (species, thisTrait.locus))
result = self.cursor.fetchone()
if result:
if result[0] and result[1]:
LRS_Chr = result[0]
LRS_Mb = result[1]
#XZ: LRS_location_value is used for sorting
try:
LRS_location_value = int(LRS_Chr)*1000 + float(LRS_Mb)
except:
if LRS_Chr.upper() == 'X':
LRS_location_value = 20*1000 + float(LRS_Mb)
else:
LRS_location_value = ord(str(LRS_chr).upper()[0])*1000 + float(LRS_Mb)
LRS_location_repr = 'Chr%s: %.6f' % (LRS_Chr, float(LRS_Mb) )
LRS_flag = 0
tr.append(TDCell(HT.TD(LRS_location_repr, Class=className, nowrap="on"), LRS_location_repr, LRS_location_value))
else:
tr.append(TDCell(HT.TD("N/A", Class=className), "N/A", "N/A"))
tr.append(TDCell(HT.TD("N/A", Class=className), "N/A", "N/A"))
tblobj_body.append(tr)
for ncol, item in enumerate([thisTrait.name, PhenotypeString, thisTrait.authors, thisTrait.year, thisTrait.pubmed_id, LRS_score_repr, LRS_location_repr]):
worksheet.write([newrow, ncol], item)
newrow += 1
return tblobj_body
def getTableHeaderForProbeSet(self, worksheet=None, newrow=None, headingStyle=None):
tblobj_header = []
className = "fs13 fwb ffl b1 cw cbrb"
tblobj_header = [[THCell(HT.TD(' ', Class="fs13 fwb ffl b1 cw cbrb",nowrap='ON'), sort=0),
THCell(HT.TD('Record',HT.BR(), 'ID',HT.BR(), Class="fs13 fwb ffl b1 cw cbrb"), text="record_id", idx=1),
THCell(HT.TD('Symbol',HT.BR(),HT.BR(), Class="fs13 fwb ffl b1 cw cbrb"), text="symbol", idx=2),
THCell(HT.TD('Description',HT.BR(),HT.BR(), Class="fs13 fwb ffl b1 cw cbrb"), text="desc", idx=3),
THCell(HT.TD('Location',HT.BR(), 'Chr and Mb', HT.BR(), Class="fs13 fwb ffl b1 cw cbrb"), text="location", idx=4),
THCell(HT.TD('Mean',HT.BR(),'Expr',HT.BR(), Class="fs13 fwb ffl b1 cw cbrb"), text="mean", idx=5),
THCell(HT.TD('Max',HT.BR(),'LRS',HT.BR(), Class="fs13 fwb ffl b1 cw cbrb", nowrap='ON'), text="lrs", idx=6),
THCell(HT.TD('Max LRS Location',HT.BR(),'Chr and Mb',HT.BR(), Class="fs13 fwb ffl b1 cw cbrb", nowrap='ON'), text="lrs_location", idx=7)]]
for ncol, item in enumerate(['Record', 'Gene ID', 'Homologene ID', 'Symbol', 'Description', 'Location (Chr, Mb)', 'Mean Expr', 'Max LRS', 'Max LRS Location (Chr: Mb)']):
worksheet.write([newrow, ncol], item, headingStyle)
worksheet.set_column([ncol, ncol], 2*len(item))
return tblobj_header
def getTableBodyForProbeSet(self, traitList=[], primaryTrait=None, formName=None, worksheet=None, newrow=None, species=''):
tblobj_body = []
className = "fs12 fwn b1 c222"
for thisTrait in traitList:
if not thisTrait.haveinfo:
thisTrait.retrieveInfo(QTL=1)
if thisTrait.symbol:
pass
else:
thisTrait.symbol = "N/A"
tr = []
trId = str(thisTrait)
#XZ, 12/08/2008: checkbox
tr.append(TDCell(HT.TD(HT.Input(type="checkbox", Class="checkbox", name="searchResult",value=trId, onClick="highlight(this)"), nowrap="on", Class="fs12 fwn ffl b1 c222"), text=trId))
#XZ, 12/08/2008: probeset name
if thisTrait.cellid:
tr.append(TDCell(HT.TD(HT.Href(text=thisTrait.name, url="javascript:showDatabase3('%s','%s','%s','%s')" % (formName, thisTrait.db.name,thisTrait.name,thisTrait.cellid), Class="fs12 fwn"), Class=className), thisTrait.name, thisTrait.name.upper()))
else:
tr.append(TDCell(HT.TD(HT.Href(text=thisTrait.name, url="javascript:showDatabase3('%s','%s','%s','')" % (formName, thisTrait.db.name,thisTrait.name), Class="fs12 fwn"), Class=className), thisTrait.name, thisTrait.name.upper()))
if thisTrait.geneid:
symbolurl = HT.Href(text=thisTrait.symbol,target='_blank',url="http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?db=gene&cmd=Retrieve&dopt=Graphics&list_uids=%s" % thisTrait.geneid, Class="font_black fs12 fwn")
else:
symbolurl = HT.Href(text=thisTrait.symbol,target='_blank',url="http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?CMD=search&DB=gene&term=%s" % thisTrait.symbol, Class="font_black fs12 fwn")
#XZ, 12/08/2008: gene symbol
tr.append(TDCell(HT.TD(symbolurl, Class="fs12 fwn b1 c222 fsI"),thisTrait.symbol, thisTrait.symbol.upper()))
#XZ, 12/08/2008: description
#XZ, 06/05/2009: Rob asked to add probe target description
description_string = str(thisTrait.description).strip()
target_string = str(thisTrait.probe_target_description).strip()
description_display = ''
if len(description_string) > 1 and description_string != 'None':
description_display = description_string
else:
description_display = thisTrait.symbol
if len(description_display) > 1 and description_display != 'N/A' and len(target_string) > 1 and target_string != 'None':
description_display = description_display + '; ' + target_string.strip()
tr.append(TDCell(HT.TD(description_display, Class=className), description_display, description_display))
#XZ: trait_location_value is used for sorting
trait_location_repr = 'N/A'
trait_location_value = 1000000
if thisTrait.chr and thisTrait.mb:
try:
trait_location_value = int(thisTrait.chr)*1000 + thisTrait.mb
except:
if thisTrait.chr.upper() == 'X':
trait_location_value = 20*1000 + thisTrait.mb
else:
trait_location_value = ord(str(thisTrait.chr).upper()[0])*1000 + thisTrait.mb
trait_location_repr = 'Chr%s: %.6f' % (thisTrait.chr, float(thisTrait.mb) )
tr.append(TDCell(HT.TD(trait_location_repr, Class=className, nowrap="on"), trait_location_repr, trait_location_value))
#XZ, 01/12/08: This SQL query is much faster.
self.cursor.execute("""
select ProbeSetXRef.mean from ProbeSetXRef, ProbeSet
where ProbeSetXRef.ProbeSetFreezeId = %d and
ProbeSet.Id = ProbeSetXRef.ProbeSetId and
ProbeSet.Name = '%s'
""" % (thisTrait.db.id, thisTrait.name))
result = self.cursor.fetchone()
if result:
if result[0]:
mean = result[0]
else:
mean=0
else:
mean = 0
#XZ, 06/05/2009: It is neccessary to turn on nowrap
repr = "%2.3f" % mean
tr.append(TDCell(HT.TD(repr, Class=className, align='right', nowrap='ON'),repr, mean))
#LRS and its location
LRS_score_repr = 'N/A'
LRS_score_value = 0
LRS_location_repr = 'N/A'
LRS_location_value = 1000000
LRS_flag = 1
#Max LRS and its Locus location
if thisTrait.lrs and thisTrait.locus:
self.cursor.execute("""
select Geno.Chr, Geno.Mb from Geno, Species
where Species.Name = '%s' and
Geno.Name = '%s' and
Geno.SpeciesId = Species.Id
""" % (species, thisTrait.locus))
result = self.cursor.fetchone()
if result:
if result[0] and result[1]:
LRS_Chr = result[0]
LRS_Mb = result[1]
#XZ: LRS_location_value is used for sorting
try:
LRS_location_value = int(LRS_Chr)*1000 + float(LRS_Mb)
except:
if LRS_Chr.upper() == 'X':
LRS_location_value = 20*1000 + float(LRS_Mb)
else:
LRS_location_value = ord(str(LRS_chr).upper()[0])*1000 + float(LRS_Mb)
LRS_score_repr = '%3.1f' % thisTrait.lrs
LRS_score_value = thisTrait.lrs
LRS_location_repr = 'Chr%s: %.6f' % (LRS_Chr, float(LRS_Mb) )
LRS_flag = 0
#tr.append(TDCell(HT.TD(HT.Href(text=LRS_score_repr,url="javascript:showIntervalMapping('%s', '%s : %s')" % (formName, thisTrait.db.shortname, thisTrait.name), Class="fs12 fwn"), Class=className, align='right', nowrap="on"),LRS_score_repr, LRS_score_value))
tr.append(TDCell(HT.TD(LRS_score_repr, Class=className, align='right', nowrap="on"), LRS_score_repr, LRS_score_value))
tr.append(TDCell(HT.TD(LRS_location_repr, Class=className, nowrap="on"), LRS_location_repr, LRS_location_value))
if LRS_flag:
tr.append(TDCell(HT.TD(LRS_score_repr, Class=className), LRS_score_repr, LRS_score_value))
tr.append(TDCell(HT.TD(LRS_location_repr, Class=className), LRS_location_repr, LRS_location_value))
else:
tr.append(TDCell(HT.TD("N/A", Class=className), "N/A", "N/A"))
tr.append(TDCell(HT.TD("N/A", Class=className), "N/A", "N/A"))
tblobj_body.append(tr)
for ncol, item in enumerate([thisTrait.name, thisTrait.geneid, thisTrait.homologeneid, thisTrait.symbol, description_display, trait_location_repr, mean, LRS_score_repr, LRS_location_repr]):
worksheet.write([newrow, ncol], item)
newrow += 1
return tblobj_body
def createExcelFileWithTitleAndFooter(self, workbook=None, identification=None, db=None, returnNumber=None):
worksheet = workbook.add_worksheet()
titleStyle = workbook.add_format(align = 'left', bold = 0, size=14, border = 1, border_color="gray")
##Write title Info
# Modified by Hongqiang Li
worksheet.write([1, 0], "Citations: Please see %s/reference.html" % webqtlConfig.PORTADDR, titleStyle)
worksheet.write([1, 0], "Citations: Please see %s/reference.html" % webqtlConfig.PORTADDR, titleStyle)
worksheet.write([2, 0], "Trait : %s" % identification, titleStyle)
worksheet.write([3, 0], "Database : %s" % db.fullname, titleStyle)
worksheet.write([4, 0], "Date : %s" % time.strftime("%B %d, %Y", time.gmtime()), titleStyle)
worksheet.write([5, 0], "Time : %s GMT" % time.strftime("%H:%M ", time.gmtime()), titleStyle)
worksheet.write([6, 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)
#Write footer info
worksheet.write([9 + returnNumber, 0], "Funding for The GeneNetwork: NIAAA (U01AA13499, U24AA13513), NIDA, NIMH, and NIAAA (P20-DA21131), NCI MMHCC (U01CA105417), and NCRR (U01NR 105417)", titleStyle)
worksheet.write([10 + returnNumber, 0], "PLEASE RETAIN DATA SOURCE INFORMATION WHENEVER POSSIBLE", titleStyle)
return worksheet
def getSortByValue(self, datasetType=''):
if datasetType == 'Geno':
sortby = ("location", "up")
elif datasetType == 'ProbeSet':
sortby = ("symbol", "up")
else: #Phenotype
sortby = ("record_id", "down")
return sortby