def __init__(self, fd): templatePage.__init__(self, fd) if not self.updMysql(): return fd.incparentsf1 = 1 if not fd.genotype: fd.readGenotype() fd.strainlist = fd.f1list + fd.strainlist fd.readData() if webqtlConfig.USERDICT[self.privilege] >= webqtlConfig.USERDICT['user']: pass else: heading = "Add to Published Database" detail = ["You don't have the permission to modify this database"] self.error(heading=heading,detail=detail,error="Error") return self.cursor.execute(""" SelecT PublishFreeze.Name from PublishFreeze, InbredSet where PublishFreeze.InbredSetId = InbredSet.Id AND InbredSet.Name = '%s'""" % fd.RISet) try: self.db = webqtlDataset(self.cursor.fetchone()[0], self.cursor) except: heading = "Add to Published Database" detail = ["The published database you requested has not been established"] self.error(heading=heading,detail=detail,error="Error") return status = fd.formdata.getvalue('curStatus') if status == 'insertResult': newRecord = self.readForm(fd) if not newRecord: return else: self.insertResultPage(fd, newRecord) elif status == 'insertCheck': newRecord = self.readForm(fd) if not newRecord: return else: self.insertCheckPage(fd, newRecord) else: self.dispFormPage(fd)
def checkInputPage(self, PublishFreeze_Name, traitID): TD_LR = HT.TD(height=200,width="100%",bgColor='#eeeeee') try: db = webqtlDataset(PublishFreeze_Name, self.cursor) thisTrait = webqtlTrait(db=db, cursor=self.cursor, name=traitID) thisTrait.retrieveInfo() setDescription = thisTrait.genHTML(dispFromDatabase=1, privilege=self.privilege, userName=self.userName, authorized_users=thisTrait.authorized_users) except: TD_LR.append( HT.Font('This trait is not found. Please go back to check if you selected correct Group Name and inputed correct trait ID.', color='red') ) return str(TD_LR) #TD_LR.append(HT.Font('Publish Freeze Name: %s' % PublishFreeze_Name, color='red'),HT.BR(), HT.Font('trait ID: %s' % traitID, color='red'), HT.BR()) formMain = HT.Form(cgi=os.path.join(webqtlConfig.CGIDIR, webqtlConfig.SCRIPTFILE), enctype='multipart/form-data', name='showDatabase', submit=HT.Input(type='hidden')) formMain.append( HT.Blockquote( HT.Font('The trait '), setDescription, HT.Font(' will be deleted.'), HT.BR(), HT.BR(), HT.Font('Please open the trait and make sure you do want to delete it.', color = 'red') ), HT.Input(type='hidden',name='FormID',value=''), HT.Input(type='hidden',name='database',value=''), HT.Input(type='hidden',name='ProbeSetID',value=''), HT.Input(type='hidden',name='CellID',value='') ) deletePhenotypeTraitForm = HT.Form(cgi= os.path.join(webqtlConfig.CGIDIR, webqtlConfig.SCRIPTFILE), enctype='multipart/form-data', name='deletePhenotypeTraitForm', submit=HT.Input(type='hidden')) deletePhenotypeTraitForm.append( HT.Input(type='Submit', value='Delete Trait', Class="button"), HT.Input(type='hidden',name='FormID',value='deletePhenotypeTrait'), HT.Input(type='hidden',name='ifVerified',value='GN@UTHSC'), HT.Input(type='hidden',name='status',value='delete'), HT.Input(type='hidden',name='PublishFreeze_Name',value=db), HT.Input(type='hidden',name='traitID',value=traitID) ) TD_LR.append(formMain, HT.BR(), HT.BR(), deletePhenotypeTraitForm) return str(TD_LR)
def __init__(self, fd): templatePage.__init__(self, fd) if not self.openMysql(): return fd.readGenotype() TD_LR = HT.TD(height=200,width="100%",bgColor='#eeeeee') self.database = fd.formdata.getfirst('database') self.ProbeSetID = fd.formdata.getfirst('ProbeSetID') self.CellID = fd.formdata.getfirst('CellID') self.db = webqtlDataset(self.database, self.cursor) thisTrait = webqtlTrait(db= self.db, cursor=self.cursor, name=self.ProbeSetID) #, cellid=CellID) thisTrait.retrieveInfo() try: self.cursor.execute('SELECT ProbeFreeze.Name FROM ProbeFreeze,ProbeSetFreeze WHERE ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId and ProbeSetFreeze.Name = "%s"' % self.db.name) self.probeDatabase = self.cursor.fetchall()[0][0] self.probeInfoDatabase = 'Probe' except: heading = 'Probe Information' intro = ['Trying to retrieve the probe information for ProbeSet ',HT.Span('%s' % self.ProbeSetID, Class="fwb cdg"),' in Database ',HT.Href(text='%s' % self.db.fullname,url=webqtlConfig.infopagehref % self.database)] detail = ['The information you just requested is not available at this time.'] self.error(heading=heading,intro=intro,detail=detail) return form = HT.Form(cgi= os.path.join(webqtlConfig.CGIDIR, webqtlConfig.SCRIPTFILE), enctype='multipart/form-data', name='showDatabase', submit=HT.Input(type='hidden')) hddn = {'FormID':'showDatabase','ProbeSetID':'_','database':'_','CellID':'_','RISet':fd.RISet, 'incparentsf1':'on'} if fd.RISet == 'BXD': hddn['parentsf1']='ON' for key in hddn.keys(): form.append(HT.Input(name=key, value=hddn[key], type='hidden')) #Buttons on search page linkinfo ="%s/probeInfo.html" % webqtlConfig.PORTADDR mintmap = "" probeinfo = HT.Input(type='button' ,name='mintmap',value='Info', onClick="openNewWin('%s');" % linkinfo, Class="button") cormatrix = HT.Href(url="#redirect", onClick="databaseFunc(document.getElementsByName('showDatabase')[0], 'corMatrix');") cormatrix_img = HT.Image("/images/correlation_matrix1_final.jpg", alt="Correlation Matrix and PCA", title="Correlation Matrix and PCA", style="border:none;") cormatrix.append(cormatrix_img) heatmap = HT.Href(url="#redirect", onClick="databaseFunc(document.getElementsByName('showDatabase')[0], 'heatmap');") heatmap_img = HT.Image("/images/heatmap2_final.jpg", name='mintmap', alt="QTL Heat Map and Clustering", title="QTL Heatmap and Clustering", style="border:none;") heatmap.append(heatmap_img) if self.ProbeSetID[-2:] in ('_A', '_B'): thisProbeSetID = self.ProbeSetID[:-2] else: thisProbeSetID = self.ProbeSetID thisurl = 'http://www.ensembl.org/Mus_musculus/featureview?type=AffyProbe&id=%s' % thisProbeSetID verifyButton = HT.Input(type="button",value="Verify Ensembl",onClick= "openNewWin('%s')" % thisurl, Class="button") addselect = HT.Input(type='button' ,name='addselect',value='Add to Collection', onClick="addRmvSelection('%s', this.form, 'addToSelection');" % fd.RISet,Class="button") selectall = HT.Input(type='button' ,name='selectall',value='Select All', onClick="checkAll(this.form);",Class="button") selectpm = HT.Input(type='button' ,name='selectall',value='Select PM', onClick="checkPM(this.form);",Class="button") selectmm = HT.Input(type='button' ,name='selectall',value='Select MM', onClick="checkMM(this.form);",Class="button") selectinvert = HT.Input(type='button' ,name='selectinvert',value='Select Invert', onClick="checkInvert(this.form);",Class="button") reset = HT.Input(type='reset',name='',value='Select None',Class="button") chrMenu = HT.Input(type='hidden',name='chromosomes',value='all') probedata = HT.Input(type='hidden',name='probedata',value='all') url_rudi_track = self.getProbeTrackURL(self.probeDatabase, self.ProbeSetID) if url_rudi_track: rudi_track = HT.Input(type='button', name='ruditrack', value='Probe Track', onClick="openNewWin('%s')"%url_rudi_track, Class="button") else: rudi_track = None pinfopage = "/probeInfo.html" #updated by NL: 07-22-2011 get chosenStrains _f1, _f12, _mat, _pat = webqtlUtil.ParInfo[fd.RISet] chosenStrains="%s,%s"%(_mat,_pat) tblobj = {} tblobj['header']=[] tblobj['header'].append([ THCell(HT.TD("", Class="cbrb cw fwb fs13 b1", rowspan=2,nowrap='ON'), sort=0), THCell(HT.TD(HT.Href(target="_PROBEINFO", url=pinfopage+"#probe", text=HT.Span('Probe', Class="cw fwb fs13")), HT.Sup(HT.Italic('1')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,nowrap='ON'), text="probe", idx=1), THCell(HT.TD(HT.Href(text=HT.Span('Sequence', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#Sequence"),HT.Sup(HT.Italic('2')), Class="cbrb cw fwb fs13 b1", align='center',rowspan=2,nowrap='ON'), text="seq", idx=2), THCell(HT.TD(HT.Href(text=HT.Span('bl2seq', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#bl2seq"),HT.Sup(HT.Italic('3')), Class="cbrb cw fwb fs13 b1", align='center',rowspan=2,nowrap='ON'), sort=0), THCell(HT.TD(HT.Href(text=HT.Span('Exons', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#Exon"),HT.Sup(HT.Italic('4')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,nowrap='ON'), sort=0), THCell(HT.TD(HT.Href(text=HT.Span('Tm °C', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#Tm"),HT.Sup(HT.Italic('5')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,nowrap='ON'), text="tm", idx=5), THCell(HT.TD(HT.Href(text=HT.Span('Stacking Energy K', HT.Sub('B'),'T', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#KBT"),HT.Sup(HT.Italic('6')), Class="cbrb cw fwb fs13 b1",align='center',colspan=2,NOWRAP="yes",nowrap='ON'), sort=0), THCell(HT.TD(HT.Href(text=HT.Span('Mean', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#Mean"),HT.Sup(HT.Italic('7')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,nowrap='ON'), text="mean", idx=8), THCell(HT.TD(HT.Href(text=HT.Span('Stdev', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#Stdev"),HT.Sup(HT.Italic('8')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,nowrap='ON'), text="std", idx=9), THCell(HT.TD(HT.Href(text=HT.Span('Probe h2', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#h2"),HT.Sup(HT.Italic('9')), Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,NOWRAP="yes"), text="h2", idx=10), THCell(HT.TD(HT.Href(text=HT.Span('Probe Location', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#location"), HT.Sup(HT.Italic('10')),Class="cbrb cw fwb fs13 b1",align='center',colspan=3)), THCell(HT.TD(HT.Href(text=HT.Span('SNPs', HT.BR(), '(Across all strains)', Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#snps"), HT.Sup(HT.Italic('11')),Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,NOWRAP="yes")), THCell(HT.TD(HT.Href(text=HT.Span('SNPs', HT.BR(),'(Different alleles only between %s and %s)'%(_mat,_pat), Class="cw fwb fs13"), target="_PROBEINFO", url=pinfopage+"#snps"), HT.Sup(HT.Italic('11')),Class="cbrb cw fwb fs13 b1",align='center',rowspan=2,NOWRAP="yes")) ]) tblobj['header'].append([ THCell(HT.TD(HT.Span('GSB', Class="cw fwb fs13"),align='center', Class="cbrb ffl fwb fs13 b1",), text="gsb", idx=6), THCell(HT.TD(HT.Span('NSB', Class="cw fwb fs13"),align='center', Class="cbrb ffl fwb fs13 b1",), text="nsb", idx=7), THCell(HT.TD(HT.Span('Chr', Class="cw fwb fs13"), align='center', Class="cbrb ffl2 fwb fs13 b1",)), THCell(HT.TD(HT.Span('Start', Class="cw fwb fs13"),align='center', Class="cbrb ffl fwb fs13 b1",)), THCell(HT.TD(HT.Span('End', Class="cw fwb fs13"),align='center', Class="cbrb ffl fwb fs13 b1",)), ]) tblobj['body'] = [] blatbutton = '' fetchField = ['Probe.Name','Probe.Sequence','Probe.ExonNo','Probe.Tm', 'Probe.E_GSB','Probe.E_NSB', 'ProbeH2.h2', 'ProbeH2.weight'] query = "SELECT %s FROM (Probe, ProbeSet, ProbeFreeze) left join ProbeH2 on ProbeH2.ProbeId = Probe.Id and ProbeH2.ProbeFreezeId = ProbeFreeze.Id WHERE ProbeSet.Name = '%s' and Probe.ProbeSetId = ProbeSet.Id and ProbeFreeze.Name = '%s' order by Probe.SerialOrder" % (string.join(fetchField,','), self.ProbeSetID, self.probeDatabase) self.cursor.execute(query) results = self.cursor.fetchall() blatsequence = "" # add by NL: get strains' name in SnpPattern database table strainsInSnpPatternDBtable=self.getStrainNameIndexPair() # after snpBrowserPage.py change to MVC, this function can be removed in this class and called from other class; allStrainNameList=[v[0] for v in strainsInSnpPatternDBtable] speciesid = webqtlDatabaseFunction.retrieveSpeciesId(cursor=self.cursor,RISet=fd.RISet) for result in results: """ ProbeId, CellID,Sequence,ExonNo,Tm, E_GSB,E_NSB = map(self.nullRecord,result) h2 = '' query = "SELECT h2 FROM ProbeH2 WHERE ProbeFreezeId = '%s' and ProbeId=%s" % (self.probeDatabase, ProbeId) self.cursor.execute(query) results = self.cursor.fetchall() """ CellID,Sequence,ExonNo,Tm, E_GSB,E_NSB,h2, weight = map(self.nullRecord,result) Average = '' STDEV = '' mean = -10000.0 stdev = -10000.0 try: thisTrait.cellid = CellID thisTrait.retrieveData() mean, median, var, stdev, sem, N = reaper.anova(thisTrait.exportInformative()[1]) if mean: Average = '%2.2f' % mean if stdev: STDEV = '%2.2f' % stdev except: pass if CellID == self.CellID: bkColor = "cbrdull fs11 b1" else: bkColor = "fs11 b1" seqcolor= '' if thisTrait.blatseq: blatsequence = thisTrait.blatseq if int(CellID[-1]) % 2 == 1: seqcolor= 'cdg' else: if int(CellID[-1]) % 2 == 1: seqcolor= 'cdg' blatsequence += string.strip(Sequence) if thisTrait.genbankid and (int(CellID[-1]) % 2 == 1): probeurl = 'http://www.ncbi.nlm.nih.gov/blast/bl2seq/wblast2.cgi?one=%s&sseq=%s' % (thisTrait.genbankid, Sequence) probefy1 = HT.Input(type="button",value="Blast",onClick= "openNewWin('%s')" % probeurl, Class="buttonsmaller") else: probefy1 = '' traitName = str(thisTrait) #XZ, Aug 08, 2011: Note that probesets on some affy chips are not name as "xxx_at" (i.e., Affy Mouse Gene 1.0 ST (GPL6246)). #EnsemblProbeSetID = self.ProbeSetID[0:self.ProbeSetID.index('_at')+3] EnsemblProbeSetID = self.ProbeSetID if '_at' in self.ProbeSetID: EnsemblProbeSetID = self.ProbeSetID[0:self.ProbeSetID.index('_at')+3] self.cursor.execute(''' SELECT EnsemblProbeLocation.* FROM EnsemblProbeLocation, EnsemblProbe, EnsemblChip, GeneChipEnsemblXRef, ProbeFreeze WHERE EnsemblProbeLocation.ProbeId=EnsemblProbe.Id and EnsemblProbe.ChipId=GeneChipEnsemblXRef.EnsemblChipId and GeneChipEnsemblXRef.GeneChipId=ProbeFreeze.ChipId and EnsemblProbe.Name=%s and EnsemblProbe.ProbeSet=%s and ProbeFreeze.Name=%s group by Chr, Start, End''' ,(CellID, EnsemblProbeSetID, self.probeDatabase)) LocationFields = self.cursor.fetchall() Chr='' Start='' End='' if (len(LocationFields)>=1): Chr,Start,End,Strand,MisMatch,ProbeId = map(self.nullRecord,LocationFields[0]) Start /= 1000000.0 End /= 1000000.0 if (len(LocationFields)>1): self.cursor.execute(''' SELECT ProbeSet.Chr, ProbeSet.Mb FROM ProbeSet, ProbeFreeze WHERE ProbeSet.ChipId=ProbeFreeze.ChipId and ProbeSet.Name=%s and ProbeFreeze.Name=%s''' ,(self.ProbeSetID, self.probeDatabase)) ProbeSetChr, ProbeSetMb = map(self.nullRecord,self.cursor.fetchall()[0]) self.cursor.execute(''' SELECT EnsemblProbeLocation.*, ABS(EnsemblProbeLocation.Start/1000000-%s) as Mb FROM EnsemblProbeLocation, EnsemblProbe, EnsemblChip, GeneChipEnsemblXRef, ProbeFreeze WHERE EnsemblProbeLocation.ProbeId=EnsemblProbe.Id and EnsemblProbe.ChipId=GeneChipEnsemblXRef.EnsemblChipId and GeneChipEnsemblXRef.GeneChipId=ProbeFreeze.ChipId and EnsemblProbe.Name=%s and EnsemblProbe.ProbeSet=%s and EnsemblProbeLocation.Chr=%s and ProbeFreeze.Name=%s order by Mb limit 1''' ,(ProbeSetMb, CellID, EnsemblProbeSetID, ProbeSetChr, self.probeDatabase)) NewLocationFields = self.cursor.fetchall() if (len(NewLocationFields)>0): Chr,Start,End,Strand,MisMatch,ProbeId,Mb = map(self.nullRecord,NewLocationFields[0]) Start /= 1000000.0 End /= 1000000.0 snp_collection = [] snpDiff_collection=[] startIndex=3 if Chr != '' and Start != '' and End != '' and speciesid != None: self.cursor.execute(''' SELECT a.SnpName, a.Id, b.* FROM SnpAll a, SnpPattern b WHERE a.Chromosome=%s and a.Position>=%s and a.Position<=%s and a.SpeciesId=%s and a.Id=b.SnpId''' ,(Chr, Start, End, speciesid)) #chr,Start, End, 1)) snpresults = self.cursor.fetchall() index1=allStrainNameList.index(_mat) #_mat index in results index2=allStrainNameList.index(_pat) #_pat index in results for v in snpresults: #updated by NL: 07-22-2011 check 'limit to' to get snpBrowser snpresults snp_collection.append(HT.Href(text=v[0], url=os.path.join(webqtlConfig.CGIDIR, "main.py?FormID=SnpBrowserResultPage&submitStatus=1&customStrain=1")+ "&geneName=%s" % v[0], Class="fs12 fwn", target="_blank")) snp_collection.append(HT.BR()) #updated by NL: 07-27-2011 link snp info for different allele only strain1_allele=v[startIndex+index1] strain2_allele=v[startIndex+index2] if strain1_allele!=strain2_allele: snpDiff_collection.append(HT.Href(text=v[0], url=os.path.join(webqtlConfig.CGIDIR, "main.py?FormID=SnpBrowserResultPage&submitStatus=1&customStrain=1&diffAlleles=1&chosenStrains=%s"%chosenStrains)+ "&geneName=%s" % v[0], Class="fs12 fwn", target="_blank")) snpDiff_collection.append(HT.BR()) tr = [] tr.append(TDCell(HT.TD(HT.Input(type="checkbox", Class='checkbox', name="searchResult",value=traitName, onClick="highlight(this)"), align="right", Class=bkColor, nowrap="on"), text=traitName)) tr.append(TDCell(HT.TD(HT.Href(text=CellID, url = "javascript:showDatabase2('%s','%s','%s');" % (self.database,self.ProbeSetID,CellID),Class="fs12 fwn"),Class=bkColor), traitName, traitName.upper())) tr.append(TDCell(HT.TD(Sequence, Class=bkColor + " %s ffmono fs14" % seqcolor),Sequence,Sequence.upper())) tr.append(TDCell(HT.TD(probefy1,align='center',Class=bkColor))) tr.append(TDCell(HT.TD(ExonNo,align='center',Class=bkColor))) try: TmValue = float(Tm) except: TmValue = 0.0 tr.append(TDCell(HT.TD(Tm,align='center',Class=bkColor), Tm, TmValue)) try: E_GSBValue = float(E_GSB) except: E_GSBValue = -10000.0 tr.append(TDCell(HT.TD(E_GSB,align='center',Class=bkColor), E_GSB, E_GSBValue)) try: E_NSBValue = float(E_NSB) except: E_NSBValue = -10000.0 tr.append(TDCell(HT.TD(E_NSB,align='center',Class=bkColor), E_NSB, E_NSBValue)) tr.append(TDCell(HT.TD(Average,align='center',Class=bkColor), Average, mean)) tr.append(TDCell(HT.TD(STDEV,align='center',Class=bkColor), STDEV, stdev)) try: h2Value = float(h2) except: h2Value = -10000.0 tr.append(TDCell(HT.TD(h2,align='center',Class=bkColor), h2, h2Value)) tr.append(TDCell(HT.TD(Chr,align='left',Class=bkColor))) tr.append(TDCell(HT.TD(Start,align='left',Class=bkColor))) tr.append(TDCell(HT.TD(End,align='left',Class=bkColor))) snp_td = HT.TD(align='left',Class=bkColor) for one_snp_href in snp_collection: snp_td.append(one_snp_href) tr.append(TDCell(snp_td)) #07-27-2011:add by NL: show SNP results for different allele only snpDiff_td= HT.TD(align='left', valign='top', Class=bkColor) for one_snpDiff_href in snpDiff_collection: snpDiff_td.append(one_snpDiff_href) tr.append(TDCell(snpDiff_td)) tblobj['body'].append(tr) # import cPickle filename = webqtlUtil.genRandStr("Probe_") objfile = open('%s.obj' % (webqtlConfig.TMPDIR+filename), 'wb') cPickle.dump(tblobj, objfile) objfile.close() # NL, 07/27/2010. genTableObj function has been moved from templatePage.py to webqtlUtil.py; div = HT.Div(webqtlUtil.genTableObj(tblobj=tblobj, file=filename, sortby=("", ""), tableID = "sortable", addIndex = "1"), Id="sortable") #UCSC _Species = webqtlDatabaseFunction.retrieveSpecies(cursor=self.cursor, RISet=fd.RISet) if _Species == "rat": thisurl = webqtlConfig.UCSC_BLAT % ('rat', 'rn3', blatsequence) elif _Species == "mouse": thisurl = webqtlConfig.UCSC_BLAT % ('mouse', 'mm9', blatsequence) else: thisurl = "" if thisurl: blatbutton = HT.Input(type='button' ,name='blatPM',value='Verify UCSC', onClick="window.open('%s','_blank')" % thisurl,Class="button") else: blatbutton = "" #GenBank genbankSeq = "" if thisTrait.genbankid: self.cursor.execute("SELECT Sequence FROM Genbank WHERE Id = '%s'" % thisTrait.genbankid ) genbankSeq = self.cursor.fetchone() if genbankSeq: genbankSeq = genbankSeq[0] if genbankSeq: if _Species == "rat": thisurl2 = webqtlConfig.UCSC_BLAT % ('rat', 'rn3', genbankSeq) if _Species == "mouse": thisurl2 = webqtlConfig.UCSC_BLAT % ('mouse', 'mm9', genbankSeq) else: thisurl2 = '' if thisurl2: blatbutton2 = HT.Input(type='button' ,name='blatPM',value='Verify GenBank', onClick="window.open('%s','_blank')" % thisurl2,Class="button") else: blatbutton2 = "" #Snp snpBrowser = "" if thisTrait.symbol and _Species == 'mouse': self.cursor.execute("select geneSymbol from GeneList where geneSymbol = %s", thisTrait.symbol) geneName = self.cursor.fetchone() if geneName: snpurl = os.path.join(webqtlConfig.CGIDIR, "main.py?FormID=snpBrowser") + "&geneName=%s" % geneName[0] else: if thisTrait.chr and thisTrait.mb: snpurl = os.path.join(webqtlConfig.CGIDIR, "main.py?FormID=snpBrowser") + \ "&chr=%s&start=%2.6f&end=%2.6f" % (thisTrait.chr, thisTrait.mb-0.002, thisTrait.mb+0.002) else: snpurl = "" if snpurl: snpBrowser = HT.Input(type="button",value="SNP Browser",onClick= \ "openNewWin('%s')" % snpurl, Class="button") else: snpBrowser = "" #end if heading = HT.Paragraph('Probe Information', Class="title") intro = HT.Paragraph('The table below lists information of all probes of probe set ',HT.Span(self.ProbeSetID, Class="fwb fs13"),' from database ', HT.Span(self.probeDatabase, Class="fwb fs13"), ".") buttons = HT.Paragraph(probedata,probeinfo,heatmap,cormatrix,blatbutton,blatbutton2,verifyButton,snpBrowser, HT.P(),selectall,selectpm,selectmm,selectinvert,reset,addselect) if rudi_track: buttons.append(rudi_track) form.append(buttons,div,HT.P()) TD_LR.append(heading,intro,form, HT.P()) self.dict['basehref'] = '' self.dict['body'] = str(TD_LR) self.dict['title'] = self.db.shortname + ' : ' + self.ProbeSetID +' / Probe Information' # updated by NL, javascript function xmlhttpPost(strURL, div, querystring) and function updatepage(Id, str) # have been moved to dhtml.js self.dict['js1'] = ''
def __init__(self,fd): templatePage.__init__(self,fd) if not self.openMysql(): return self.database = fd.formdata.getvalue('database', '') db = webqtlDataset(self.database, self.cursor) try: self.openURL = webqtlConfig.CGIDIR + webqtlConfig.SCRIPTFILE + \ '?FormID=showDatabase&database=%s&incparentsf1=1&ProbeSetID=' % self.database if db.type != "ProbeSet" or not db.id: raise DbNameError self.cursor.execute(""" Select InbredSet.Name From ProbeSetFreeze, ProbeFreeze, InbredSet whERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND ProbeFreeze.InbredSetId = InbredSet.Id AND ProbeSetFreeze.Id = %d """ % db.id) thisRISet = self.cursor.fetchone()[0] if thisRISet =='BXD300': thisRISet = 'BXD' ################################################## # exon data is too huge for GenoGraph, skip it # ################################################## self.cursor.execute('select count(*) from ProbeSetXRef where ProbeSetFreezeId=%d' % db.id) amount = self.cursor.fetchall() if amount: amount = amount[0][0] if amount>100000: heading = "Whole Transcriptome Mapping" detail = ["Whole Transcriptome Mapping is not available for this data set."] self.error(heading=heading,detail=detail) return self.cursor.execute(""" Select ProbeSet.Id, ProbeSet.Name, ProbeSet.Chr, ProbeSet.Mb, ProbeSetXRef.Locus, ProbeSetXRef.pValue From ProbeSet, ProbeSetXRef whERE ProbeSetXRef.ProbeSetFreezeId = %d AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetXRef.Locus is not NULL """ % db.id) results = self.cursor.fetchall() if results: self.mouseChrLengthDict, sum = self.readMouseGenome(thisRISet) import reaper markerGMb = {} genotype_1 = reaper.Dataset() genotype_1.read(os.path.join(webqtlConfig.GENODIR, thisRISet + '.geno')) for chr in genotype_1: chrlen = self.mouseChrLengthDict[chr.name] for locus in chr: markerGMb[locus.name] = locus.Mb + chrlen try: FDR = float(fd.formdata.getvalue("fdr", "")) except: FDR = 0.2 self.grid = fd.formdata.getvalue("grid", "") NNN = len(results) results = list(results) results.sort(self.cmppValue) MbCoord = [] MbCoord2 = [] for j in range(NNN, 0, -1): if results[j-1][-1] <= (FDR*j)/NNN: break if j > 0: for i in range(j-1, -1, -1): _Id, _probeset, _chr, _Mb, _marker, _pvalue = results[i] try: MbCoord.append([markerGMb[_marker], _Mb+self.mouseChrLengthDict[string.strip(_chr)], _probeset, _chr, _Mb, _marker, _pvalue]) except: pass filename=webqtlUtil.genRandStr("gScan_") canvas = pid.PILCanvas(size=(1280,880)) self.drawGraph(canvas, MbCoord, cLength=sum) canvas.save(os.path.join(webqtlConfig.IMGDIR, filename), format='png') canvasSVG = self.drawSVG(MbCoord, cLength=sum, size=(1280,880)) canvasSVG.toXml(os.path.join(webqtlConfig.IMGDIR, filename+'.svg')) #and write it to file img = HT.Embed(src='/image/'+filename+'.png', width=1280, height=880, border=0, alt='Genome Scan') img2 = HT.Embed(src='/image/'+filename+'.svg', width=1280, height=880, border=0) TD_LR = HT.TD(colspan=2,height=200,width="100%",bgColor='#eeeeee') heading = HT.Paragraph('Whole Transcriptome Mapping') heading.__setattr__("class","title") intro = HT.Blockquote() intro.append('The plot below is the Whole Transcriptome Mapping of Database ') intro.append(HT.Href(text=db.fullname, url = webqtlConfig.INFOPAGEHREF % db.name ,target='_blank',Class="normalsize")) intro.append(". %d from a total of %d ProbeSets were utilized to generate this graph." % (len(MbCoord), len(results))) mainfm = HT.Form(cgi = os.path.join(webqtlConfig.CGIDIR, webqtlConfig.SCRIPTFILE), enctype='multipart/form-data', \ name=webqtlUtil.genRandStr("fm_"), submit=HT.Input(type='hidden')) mainfm.append(HT.Input(name='database', value=self.database, type='hidden')) mainfm.append(HT.Input(name='FormID', value='transciptMapping', type='hidden')) mainfm.append("<BLOCKQUOTE>") mainfm.append("0 < FDR <= 1.0 ") mainfm.append(HT.Input(name='fdr', value=FDR, type='text')) mainfm.append(HT.Input(name='submit', value='Redraw Graph', type='submit', Class='button')) mainfm.append("</BLOCKQUOTE>") mainfm.append(""" <center> <div id="gallery"> <div class="on" title="img1"><span>Static</span></div> <div class="off" title="img2"><span>Interactive</span></div> </div> <div id="img1" class="show"> """) mainfm.append(img) mainfm.append(""" </div> <div id="img2" class="hide"> """) mainfm.append(img2) mainfm.append(""" </div> </center> """) TD_LR.append(heading, intro, HT.Paragraph(mainfm)) self.dict['title'] = 'Whole Transcriptome Mapping' self.dict['body'] = TD_LR else: heading = "Whole Transcriptome Mapping" detail = ["Database calculation is not finished."] self.error(heading=heading,detail=detail) return except: heading = "Whole Transcriptome Mapping" detail = ["Whole Transcriptome Mapping only apply to Microarray database."] self.error(heading=heading,detail=detail) return
def __init__(self,fd): templatePage.__init__(self,fd) if not self.openMysql(): return self.database = fd.formdata.getvalue('database', '') db = webqtlDataset(self.database, self.cursor) try: if db.type != "ProbeSet" or not db.id: raise DbNameError except: print 'Content-type: text/html\n' heading = "Download Results" detail = ["Only results of microarray database are available to download."] self.error(heading=heading,detail=detail) self.write() return #XZ, protect confidential dataset. userExist = None self.cursor.execute('SELECT Id, Name, FullName, confidentiality, AuthorisedUsers FROM ProbeSetFreeze WHERE Name = "%s"' % self.database) indId, indName, indFullName, indConfid, AuthorisedUsers = self.cursor.fetchall()[0] if indConfid == 1 and userExist == None: try: userExist = self.userName #for the dataset that confidentiality is 1 #1. 'admin' and 'root' can see all of the dataset #2. 'user' can see the dataset that AuthorisedUsers contains his id(stored in the Id field of User table) if webqtlConfig.USERDICT[self.privilege] < webqtlConfig.USERDICT['admin']: if not AuthorisedUsers: userExist=None else: AuthorisedUsersList=AuthorisedUsers.split(',') if not AuthorisedUsersList.__contains__(self.userName): userExist=None except: pass if not userExist: #Error, Confidential Database heading = "Correlation Table" detail = ["The %s database you selected is not open to the public at this time, please go back and select other database." % indFullName] self.error(heading=heading,detail=detail,error="Confidential Database") return self.cursor.execute(""" Select InbredSet.Name From ProbeSetFreeze, ProbeFreeze, InbredSet whERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND ProbeFreeze.InbredSetId = InbredSet.Id AND ProbeSetFreeze.Id = %d """ % db.id) thisRISet = self.cursor.fetchone()[0] if thisRISet =='BXD300': thisRISet = 'BXD' #XZ, 06/26/2009: It seems that this query is not neccessary. It doesn't return any result. #XZ: It seems it is just for test purpose. The next try-except block does the real work. #XZ: I think it should be deleted to shorten the response time. #self.cursor.execute(""" # Select # ProbeSet.Name, ProbeSet.symbol, ProbeSet.description, ProbeSet.Chr, ProbeSet.Mb, ProbeSetXRef.Locus, # ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.additive, ProbeSetXRef.mean # From # ProbeSet, ProbeSetXRef # whERE # ProbeSetXRef.ProbeSetFreezeId = %d AND # ProbeSetXRef.ProbeSetId = ProbeSet.Id AND # ProbeSetXRef.Locus is not NULL # Order by # ProbeSet.name_num # """ % db.id) filename = os.path.join(webqtlConfig.SECUREDIR, db.name+'.result.xls') try: import random234 if random.choice(range(10)) == 0: raise "ReCalculate" fp = open(filename, 'rb') text = fp.read() fp.close() except: self.cursor.execute("Select ProbeSetXRef.ProbeSetId from ProbeSetXRef where ProbeSetFreezeId=%d" % db.id) ProbeSetIds = self.cursor.fetchall() self.mouseChrLengthDict, sum = self.readMouseGenome(thisRISet) if ProbeSetIds: import reaper markerGMb = {} genotype_1 = reaper.Dataset() genotype_1.read(os.path.join(webqtlConfig.GENODIR, thisRISet + '.geno')) for chr in genotype_1: chrlen = self.mouseChrLengthDict[chr.name] for locus in chr: markerGMb[locus.name] = [chr.name, locus.Mb, locus.Mb + chrlen] text = [] text.append(['ProbeSetId', 'Symbol', 'Description', 'Target Description', 'Chr', 'TMb', 'TGMb', 'Locus', 'LRS', 'Additive', 'pvalue', 'markerChr', 'markerMb', 'markerGMb', 'meanExpression']) ProbeSetIdList = [] for ProbeSetId in ProbeSetIds: ProbeSetIdList.append(ProbeSetId[0]) if len(ProbeSetIdList)==1000: ProbeSetIdStr = ','.join(map(str, ProbeSetIdList)) ProbeSetIdList = [] cmd = """ Select ProbeSet.Name, ProbeSet.symbol, ProbeSet.description,ProbeSet.Probe_Target_Description,ProbeSet.Chr, ProbeSet.Mb, ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.additive, ProbeSetXRef.mean From ProbeSet, ProbeSetXRef Where ProbeSetXRef.ProbeSetFreezeId = %s AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetXRef.Locus is not NULL AND ProbeSet.Id in (%s) Order by ProbeSet.name_num """ % (db.id, ProbeSetIdStr) self.cursor.execute(cmd) results = self.cursor.fetchall() for result in results: _Id, _symbol, _desc,_targetDesc, _chr, _TMb, _marker, _lrs, _pvalue, _additive, _mean = result if _marker == "-": continue if not _additive: _additive = "" try: _TGMb = _TMb + self.mouseChrLengthDict[string.strip(_chr)] except: _TGMb = "" result2 = [_Id, _symbol, _desc, _targetDesc, _chr, _TMb, _TGMb, _marker, _lrs, _additive, _pvalue] try: result2 += markerGMb[_marker] except: result2 += ['', '', ''] result2 += [_mean] text.append(map(str, result2)) #XZ, 06/29/2007: This block is dealing with the last several probesets that fall out of the 1000-probeset block. if ProbeSetIdList: ProbeSetIdStr = ','.join(map(str, ProbeSetIdList)) cmd = """ Select ProbeSet.Name, ProbeSet.symbol, ProbeSet.description,ProbeSet.Probe_Target_Description, ProbeSet.Chr, ProbeSet.Mb, ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.additive, ProbeSetXRef.mean From ProbeSet, ProbeSetXRef Where ProbeSetXRef.ProbeSetFreezeId = %s AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetXRef.Locus is not NULL AND ProbeSet.Id in (%s) Order by ProbeSet.name_num """ % (db.id, ProbeSetIdStr) self.cursor.execute(cmd) results = self.cursor.fetchall() for result in results: _Id, _symbol, _desc, _targetDesc,_chr, _TMb, _marker, _lrs, _pvalue, _additive, _mean = result if _marker == "-": continue if not _additive: _additive = "" try: _TGMb = _TMb + self.mouseChrLengthDict[string.strip(_chr)] except: _TGMb = "" result2 = [_Id, _symbol, _desc,_targetDesc, _chr, _TMb, _TGMb, _marker, _lrs, _additive, _pvalue] try: result2 += markerGMb[_marker] except: result2 += ['', '', ''] result2 += [_mean] text.append(map(str, result2)) self.gentextfile(db, text) else: heading = "Download Results" detail = ["Database calculation is not finished."] self.error(heading=heading,detail=detail) return
def __init__(self,fd): templatePage.__init__(self,fd) if not self.openMysql(): return self.database = fd.formdata.getvalue('database', '') db = webqtlDataset(self.database, self.cursor) try: if db.type != "ProbeSet" or not db.id: raise DbNameError except: print 'Content-type: text/html\n' heading = "Download Results" detail = ["Only results of microarray database are available to download."] self.error(heading=heading,detail=detail) self.write() return #XZ, protect confidential dataset. userExist = None self.cursor.execute('SELECT Id, Name, FullName, confidentiality, AuthorisedUsers FROM ProbeSetFreeze WHERE Name = "%s"' % self.database) indId, indName, indFullName, indConfid, AuthorisedUsers = self.cursor.fetchall()[0] if indConfid == 1 and userExist == None: try: userExist = self.userName #for the dataset that confidentiality is 1 #1. 'admin' and 'root' can see all of the dataset #2. 'user' can see the dataset that AuthorisedUsers contains his id(stored in the Id field of User table) if webqtlConfig.USERDICT[self.privilege] < webqtlConfig.USERDICT['admin']: if not AuthorisedUsers: userExist=None else: AuthorisedUsersList=AuthorisedUsers.split(',') if not AuthorisedUsersList.__contains__(self.userName): userExist=None except: pass if not userExist: #Error, Confidential Database heading = "Correlation Table" detail = ["The %s database you selected is not open to the public at this time, please go back and select other database." % indFullName] self.error(heading=heading,detail=detail,error="Confidential Database") return self.cursor.execute(""" Select InbredSet.Name From ProbeSetFreeze, ProbeFreeze, InbredSet whERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND ProbeFreeze.InbredSetId = InbredSet.Id AND ProbeSetFreeze.Id = %d """ % db.id) thisRISet = self.cursor.fetchone()[0] if thisRISet =='BXD300': thisRISet = 'BXD' #XZ, 06/26/2009: It seems that this query is not neccessary. It doesn't return any result. #XZ: It seems it is just for test purpose. The next try-except block does the real work. #XZ: I think it should be deleted to shorten the response time. #self.cursor.execute(""" # Select # ProbeSet.Name, ProbeSet.symbol, ProbeSet.description, ProbeSet.Chr, ProbeSet.Mb, ProbeSetXRef.Locus, # ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.additive, ProbeSetXRef.mean # From # ProbeSet, ProbeSetXRef # whERE # ProbeSetXRef.ProbeSetFreezeId = %d AND # ProbeSetXRef.ProbeSetId = ProbeSet.Id AND # ProbeSetXRef.Locus is not NULL # Order by # ProbeSet.name_num # """ % db.id) filename = os.path.join(webqtlConfig.SECUREDIR, db.name+'.result.xls') try: import random234 if random.choice(range(10)) == 0: raise "ReCalculate" fp = open(filename, 'rb') text = fp.read() fp.close() except: self.cursor.execute("Select ProbeSetXRef.ProbeSetId from ProbeSetXRef where ProbeSetFreezeId=%d" % db.id) ProbeSetIds = self.cursor.fetchall() self.mouseChrLengthDict, sum = self.readMouseGenome(thisRISet) if ProbeSetIds: import reaper markerGMb = {} genotype_1 = reaper.Dataset() genotype_1.read(os.path.join(webqtlConfig.GENODIR, thisRISet + '.geno')) for chr in genotype_1: chrlen = self.mouseChrLengthDict[chr.name] for locus in chr: markerGMb[locus.name] = [chr.name, locus.Mb, locus.Mb + chrlen] text = [] text.append(['ProbeSetId', 'Symbol', 'Description', 'Target Description', 'Chr', 'TMb', 'TGMb', 'Locus', 'LRS', 'Additive', 'pvalue', 'markerChr', 'markerMb', 'markerGMb', 'meanExpression']) ProbeSetIdList = [] for ProbeSetId in ProbeSetIds: ProbeSetIdList.append(ProbeSetId[0]) if len(ProbeSetIdList)==1000: ProbeSetIdStr = ','.join(map(str, ProbeSetIdList)) ProbeSetIdList = [] cmd = """ Select ProbeSet.Name, ProbeSet.symbol, ProbeSet.description,ProbeSet.Probe_Target_Description,ProbeSet.Chr, ProbeSet.Mb, ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.additive, ProbeSetXRef.mean From ProbeSet, ProbeSetXRef Where ProbeSetXRef.ProbeSetFreezeId = %s AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetXRef.Locus is not NULL AND ProbeSet.Id in (%s) Order by ProbeSet.name_num """ % (db.id, ProbeSetIdStr) self.cursor.execute(cmd) results = self.cursor.fetchall() for result in results: _Id, _symbol, _desc,_targetDesc, _chr, _TMb, _marker, _lrs, _pvalue, _additive, _mean = result if _marker == "-": continue if not _additive: _additive = "" try: _TGMb = _TMb + self.mouseChrLengthDict[string.strip(_chr)] except: _TGMb = "" result2 = [_Id, _symbol, _desc, _targetDesc, _chr, _TMb, _TGMb, _marker, _lrs, _additive, _pvalue] try: result2 += markerGMb[_marker] except: result2 += ['', '', ''] result2 += [_mean] text.append(map(str, result2)) #XZ, 06/29/2007: This block is dealing with the last several probesets that fall out of the 1000-probeset block. if ProbeSetIdList: ProbeSetIdStr = ','.join(map(str, ProbeSetIdList)) cmd = """ Select ProbeSet.Name, ProbeSet.symbol, ProbeSet.description,ProbeSet.Probe_Target_Description, ProbeSet.Chr, ProbeSet.Mb, ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.additive, ProbeSetXRef.mean From ProbeSet, ProbeSetXRef Where ProbeSetXRef.ProbeSetFreezeId = %s AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetXRef.Locus is not NULL AND ProbeSet.Id in (%s) Order by ProbeSet.name_num """ % (db.id, ProbeSetIdStr) self.cursor.execute(cmd) results = self.cursor.fetchall() for result in results: _Id, _symbol, _desc, _targetDesc,_chr, _TMb, _marker, _lrs, _pvalue, _additive, _mean = result if _marker == "-": continue if not _additive: _additive = "" try: _TGMb = _TMb + self.mouseChrLengthDict[string.strip(_chr)] except: _TGMb = "" result2 = [_Id, _symbol, _desc,_targetDesc, _chr, _TMb, _TGMb, _marker, _lrs, _additive, _pvalue] try: result2 += markerGMb[_marker] except: result2 += ['', '', ''] result2 += [_mean] text.append(map(str, result2)) import pyXLWriter as xl # Create a new Excel workbook workbook = xl.Writer(filename) worksheet = workbook.add_worksheet() heading = workbook.add_format(align = 'center', bold = 1, size=13, color = 'red') titleStyle = workbook.add_format(align = 'left', bold = 0, size=14, border = 1, border_color="gray") worksheet.write([0, 0], "Data source: The GeneNetwork at http://www.genenetwork.org", titleStyle) worksheet.write([1, 0], "Citations: Please see %s/reference.html" % webqtlConfig.PORTADDR, titleStyle) worksheet.write([2, 0], "Database : %s" % db.fullname, titleStyle) worksheet.write([3, 0], "Date : %s" % time.strftime("%B %d, %Y", time.gmtime()), titleStyle) worksheet.write([4, 0], "Time : %s GMT" % time.strftime("%H:%M ", time.gmtime()), titleStyle) worksheet.write([5, 0], "Status of data ownership: Possibly unpublished data; please see %s/statusandContact.html for details on sources, ownership, and usage of these data." % webqtlConfig.PORTADDR, titleStyle) table_row_start_index = 7 nrow = table_row_start_index for row in text: for ncol, cell in enumerate(row): if nrow == table_row_start_index: worksheet.write([nrow, ncol], cell.strip(), heading) worksheet.set_column([ncol, ncol], 20) else: worksheet.write([nrow, ncol], cell.strip()) nrow += 1 worksheet.write([1+nrow, 0], "Funding for The GeneNetwork: NIAAA (U01AA13499, U24AA13513), NIDA, NIMH, and NIAAA (P20-DA21131), NCI MMHCC (U01CA105417), and NCRR (U01NR 105417)", titleStyle) worksheet.write([2+nrow, 0], "PLEASE RETAIN DATA SOURCE INFORMATION WHENEVER POSSIBLE", titleStyle) workbook.close() fp = open(filename, 'rb') text = fp.read() fp.close() else: heading = "Download Results" detail = ["Database calculation is not finished."] self.error(heading=heading,detail=detail) return self.content_type = 'application/xls' self.content_disposition = 'attachment; filename=%s' % ('export-%s.xls' % time.strftime("%y-%m-%d-%H-%M")) self.attachment = text
def createTableBodies(self, fd, species, tissue, ADMIN_search_dbs): this_species_txtOutput = [] #priority GeneName > refseq > genbankid this_species_trait_count = 0 #count of all traits in this species this_species_dataset_count = 0 #Number of datasets in this species row_count = 0 #Index number used in the first row of the trait table trait_tblobj_body = [] #body of table with the results themselves; tissue_tblobj_body = [] #body of table with the number of results for each tissue group className = "fs12 fwn b1 c222" for i, tissueGrp in enumerate(ADMIN_search_dbs.keys()): if tissue and tissue.upper() != tissueGrp.upper(): continue dbNames = ADMIN_search_dbs[tissueGrp] tissue_tr = [] #Table row for tissue group tissue_tr.append(TDCell(HT.TD('', Class=className))) tissue_tr.append(TDCell(HT.TD(tissueGrp.capitalize(), Class=className), tissueGrp, tissueGrp)) #Append cell with tissue name to row this_tissue_record_count = 0 #Count of the results for each tissue for dbName in dbNames: this_species_dataset_count += 1 thisDB = webqtlDataset(dbName, self.cursor) if fd.geneName: if fd.searchAlias: self.cursor.execute("""SELECT ProbeSet.Name FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef WHERE ProbeSetFreeze.Name = "%s" AND ProbeSetFreeze.Id = ProbeSetXRef.ProbeSetFreezeId AND MATCH (ProbeSet.symbol, alias) AGAINST ("+%s" IN BOOLEAN MODE) AND ProbeSet.Id = ProbeSetXRef.ProbeSetId""" % (dbName, fd.geneName)) else: self.cursor.execute("""SELECT ProbeSet.Name FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef WHERE ProbeSetFreeze.Name = "%s" AND ProbeSetFreeze.Id = ProbeSetXRef.ProbeSetFreezeId AND ProbeSet.symbol = "%s" AND ProbeSet.Id = ProbeSetXRef.ProbeSetId""" % (dbName, fd.geneName)) elif fd.refseq: # XZ, Oct/08/2009: Search for RefSeq ID is kind of tricky. One probeset can have multiple RefseqIDs that are delimited by ' /// ' (currently). # So I have to use 'like' instead of '=' in SQL query. But user search with one short string, for example 'NM_1', it will return thousands of results. # To prevent this, I set the restriction that the length of input Refseq ID must be at least 9 characters. Otherwise, do NOT start searching. # Even with the restriction of input RefSeqID, I'm still worried about the 'like' in SQL query. My concern is in future, there might be RefSeqIDs with # 10 characters whose first 9 characters are the same as the existing ones. So I decide to further check the result. We should also consider that the # RefSeqID in database may have version number such as "NM_177938.2". If the input RefSeqID is 'NM_177938', it should be matched. I think we should get rid of the version number in database. if len(fd.refseq) < 9: if fd.returnFmt != 'text': heading = "Search Result" detail = ["The RefSeq ID that you inputed is less than 9 characters. GeneNetwork thinks it is not a legitimate RefSeq ID and did not do the search. Please try to use a RefSeq ID with at least 9 characters."] self.error(heading=heading,detail=detail,error="Not Found") else: self.output = "#The gene name or IDs you submitted did not match any record in the databases available. You may try different gene names or tissue type." return else: sqlString = """SELECT ProbeSet.Id, ProbeSet.RefSeq_TranscriptId FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef WHERE ProbeSetFreeze.Name = "%s" AND ProbeSetFreeze.Id = ProbeSetXRef.ProbeSetFreezeId AND MATCH(ProbeSet.RefSeq_TranscriptId) AGAINST ("+%s" IN BOOLEAN MODE) AND ProbeSet.Id = ProbeSetXRef.ProbeSetId""" % (dbName, fd.refseq) self.cursor.execute(sqlString) results = self.cursor.fetchall() if results: Id_of_really_matched_probeset = [] for one_result in results: ProbeSet_Id, ProbeSet_RefSeq_TranscriptId = one_result multiple_RefSeqId = string.split(string.strip(ProbeSet_RefSeq_TranscriptId), '///') for one_RefSeqId in multiple_RefSeqId: tokens = string.split( one_RefSeqId, '.' ) one_RefSeqId_without_versionNum = string.strip(tokens[0]) if one_RefSeqId_without_versionNum == fd.refseq: Id_of_really_matched_probeset.append( ProbeSet_Id ) break if Id_of_really_matched_probeset: condition_string = " or ".join(["Id = %s" % one_ID for one_ID in Id_of_really_matched_probeset]) sqlString = """SELECT ProbeSet.Name from ProbeSet where (%s)""" % condition_string self.cursor.execute(sqlString) else: pass elif fd.genbankid: self.cursor.execute("""SELECT ProbeSet.Name FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef WHERE ProbeSetFreeze.Name = "%s" AND ProbeSetFreeze.Id = ProbeSetXRef.ProbeSetFreezeId AND ProbeSet.GenbankId = "%s" AND ProbeSet.Id = ProbeSetXRef.ProbeSetId""" % (dbName, fd.genbankid)) elif fd.geneid: self.cursor.execute("""SELECT ProbeSet.Name FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef WHERE ProbeSetFreeze.Name = "%s" AND ProbeSetFreeze.Id = ProbeSetXRef.ProbeSetFreezeId AND ProbeSet.GeneId = "%s" AND ProbeSet.Id = ProbeSetXRef.ProbeSetId""" % (dbName, fd.geneid)) else: continue results = self.cursor.fetchall() if len(results) > 0: this_tissue_record_count += len(results) this_species_trait_count += this_tissue_record_count for result in results: _ProbeSetID = result[0] thisTrait = webqtlTrait(db=thisDB, name=_ProbeSetID, cursor=self.cursor) results_tr = [] trId = str(thisTrait) _traitUrl = thisTrait.genHTML(dispFromDatabase=1) _traitName = str(thisTrait) #ZS: check box column results_tr.append(TDCell(HT.TD(str(row_count+1), HT.Input(type="checkbox", Class="checkallbox", name="searchResult",value=trId, onClick="highlight(this)"), nowrap="on", align="right", Class=className), str(row_count+1), row_count+1)) row_count += 1 #ZS: Tissue column results_tr.append(TDCell(HT.TD(tissueGrp.capitalize(), Class=className), tissueGrp, tissueGrp)) #ZS: Group column risetUrl = HT.Href(text=thisTrait.riset, url="http://www.genenetwork.org/%sCross.html#%s" % (species, thisTrait.riset), target="_blank", Class=className) results_tr.append(TDCell(HT.TD(risetUrl, Class=className), thisTrait.riset, thisTrait.riset)) #ZS: Dataset column results_tr.append(TDCell(HT.TD(HT.Href(text=thisTrait.db.fullname, url = webqtlConfig.INFOPAGEHREF % thisTrait.db.name, target='_blank', Class="fs13 fwn non_bold"), Class=className), thisTrait.db.name.upper(), thisTrait.db.name.upper())) #ZS: Trait ID column results_tr.append(TDCell(HT.TD(HT.Href(text=thisTrait.getGivenName(),url="javascript:showDatabase3('%s','%s','%s','')" % ('showDatabase', thisTrait.db.name, thisTrait.name), Class="fs12 fwn"), nowrap="yes",align="left", Class=className),str(thisTrait.name), thisTrait.name)) #ZS: Symbol column and Description column description_string = str(thisTrait.description).strip() if (thisTrait.db.type == "ProbeSet"): 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() description_string = description_display else: results_tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz")) results_tr.append(TDCell(HT.TD(description_string, Class=className), description_string, description_string)) #XZ: trait_location_value is used for sorting trait_location_repr = "--" trait_location_value = 1000000 if hasattr(thisTrait, 'chr') and hasattr(thisTrait, 'mb') and 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) ) results_tr.append(TDCell(HT.TD(trait_location_repr, nowrap='ON', Class=className), trait_location_repr, trait_location_value)) #ZS: Mean column 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 repr = "%2.3f" % mean results_tr.append(TDCell(HT.TD(repr, Class=className, align='right', nowrap='ON'),repr, mean)) trait_tblobj_body.append(results_tr) this_species_txtOutput.append([tissueGrp, thisTrait]) tissue_tr.append(TDCell(HT.TD(str(this_tissue_record_count), Class=className), str(this_tissue_record_count), this_tissue_record_count)) tissue_tblobj_body.append(tissue_tr) self.output = "self.output" return tissue_tblobj_body, trait_tblobj_body, this_species_dataset_count, this_species_trait_count, this_species_txtOutput