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.webqtlDataset import webqtlDataset from base.webqtlTrait import webqtlTrait from base.templatePage import templatePage from utility import webqtlUtil from dbFunction import webqtlDatabaseFunction import logging logging.basicConfig(filename="/tmp/gn_log", 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: webqtlDataset(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: webqtlDataset(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', "") self.ORkeyword = fd.formdata.getfirst('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("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: 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 = """ """ 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