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
