diff options
Diffstat (limited to 'web/webqtl/search/SearchResultPage.py')
| -rwxr-xr-x | web/webqtl/search/SearchResultPage.py | 1249 |
1 files changed, 0 insertions, 1249 deletions
diff --git a/web/webqtl/search/SearchResultPage.py b/web/webqtl/search/SearchResultPage.py deleted file mode 100755 index d62bb449..00000000 --- a/web/webqtl/search/SearchResultPage.py +++ /dev/null @@ -1,1249 +0,0 @@ -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 = """ - <!-- BEGIN FLOATING LAYER CODE //--> - <div id="warningLayer" style="padding:3px; border: 1px solid #222; - background-color: #fff; position:absolute;width:250px;left:100;top:100;visibility:hidden"> - <table border="0" width="250" class="cbrb" cellspacing="0" cellpadding="5"> - <tr> - <td width="100%"> - <table border="0" width="100%" cellspacing="0" cellpadding="0" height="36"> - <tr> - <td class="cbrb cw ff15 fwb" align="Center" width="100%" style="padding:4px"> - Sort Table - </td> - </tr> - <tr> - <td width="100%" bgcolor="#eeeeee" align="Center" style="padding:4px"> - <!-- PLACE YOUR CONTENT HERE //--> - Resorting this table <br> - <!-- END OF CONTENT AREA //--> - </td> - </tr> - </table> - </td> - </tr> - </table> - </div> - <!-- END FLOATING LAYER CODE //--> - - """ - - 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 - - |