예제 #1
0
def retrieveMarkerNameForGroupByRange(cursor, InbredSetName, Chr, MbStart, MbEnd):

	MarkerName = []

	SpeciesId = webqtlDatabaseFunction.retrieveSpeciesId(cursor, InbredSetName)

	GenoFreezeId = retrieveGenoFreezeId(cursor, InbredSetName)

	MbStartClause = ''
	MbEndClause = ''

	try:
		MbStartClause = 'and  Mb >= %s ' % float(MbStart)
	except:
		pass

	try:
		MbEndClause = 'and  Mb <= %s' % float(MbEnd)
	except:
		pass


	cmd = "SELECT Geno.Name FROM Geno, GenoXRef WHERE Geno.SpeciesId=%s and Chr='%s' " % (SpeciesId, Chr) + MbStartClause + MbEndClause +  " and GenoXRef.GenoFreezeId=%s and GenoXRef.GenoId=Geno.Id and GenoXRef.Used_for_mapping='Y' order by Mb" % (GenoFreezeId)

	cursor.execute(cmd)

	results = cursor.fetchall()
	for one_result in results:
		MarkerName.append( one_result[0] )

	return MarkerName
예제 #2
0
def addGeno(cursor, GenoId, InbredSetName, MarkerWebID, fd):

	SpeciesId = webqtlDatabaseFunction.retrieveSpeciesId(cursor, InbredSetName)

	Name = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Name' )
	Chr = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Chr' )
	Mb = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Mb' )
	Sequence = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Sequence' )
	Source = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Source' )

	chr_num = get_chr_num (cursor, Chr, SpeciesId)

	cmd = "INSERT INTO Geno (Id, SpeciesId, Name, Marker_Name, Chr, Mb, Sequence, Source, chr_num) VALUES (%s, %s, '%s', '%s', '%s', %s, '%s', '%s', %s )" % (GenoId, SpeciesId, Name, Name, Chr, Mb, Sequence, Source, chr_num)
	cursor.execute(cmd)
예제 #3
0
def updateGeno(cursor, GenoId, InbredSetName, MarkerWebID, fd):

	SpeciesId = webqtlDatabaseFunction.retrieveSpeciesId(cursor, InbredSetName)

	Chr = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Chr' )
	cmd = "UPDATE Geno SET Chr='%s' WHERE Id=%s" % (Chr, GenoId)
	cursor.execute(cmd)

	chr_num = get_chr_num (cursor, Chr, SpeciesId)
	cmd = "UPDATE Geno SET chr_num=%s WHERE Id=%s" % (chr_num, GenoId)
	cursor.execute(cmd)

	Mb = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Mb' )
	cmd = "UPDATE Geno SET Mb=%s WHERE Id=%s" % (Mb, GenoId)
	cursor.execute(cmd)

	Sequence = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Sequence' )
	cmd = "UPDATE Geno SET Sequence='%s' WHERE Id=%s" % (Sequence, GenoId)
	cursor.execute(cmd)

	Source = fd.formdata.getvalue( MarkerWebID + markerName_Feild_Separator + 'Source' )
	cmd = "UPDATE Geno SET Source='%s' WHERE Id=%s" % (Source, GenoId)
	cursor.execute(cmd)
예제 #4
0
def retrieveMarkerInfoForGroup(cursor, MarkerName, InbredSetName):
	"""
	@type cursor: MySQLdb.connect.cursor
	@type MarkerName: string
	
	@rtype: list
	@return: the Marker's Id, Name, Chr, cM, Mb, Sequence, Source
	"""


	SpeciesId = webqtlDatabaseFunction.retrieveSpeciesId(cursor, InbredSetName)

	GenoFreezeId = retrieveGenoFreezeId(cursor, InbredSetName)
	
	cmd = ','.join( MarkerInfoField )
	cmd = "SELECT Geno.Id," + cmd + " FROM Geno, GenoXRef WHERE Geno.SpeciesId=%s and Geno.Name='%s' and GenoXRef.GenoFreezeId=%s and GenoXRef.GenoId=Geno.Id" % (SpeciesId, MarkerName, GenoFreezeId)
	cursor.execute(cmd)
	result = cursor.fetchone()
	
	if result:
		return result
	else:
		return None
예제 #5
0
    def getCollectionTableBody(self, traitList=None, formName=None, species=""):

        tblobj_body = []

        className = "fs12 fwn b1 c222"

        for thisTrait in traitList:
            tr = []

            if not thisTrait.haveinfo:
                thisTrait.retrieveInfo(QTL=1)

            trId = str(thisTrait)

            oneRadioName = thisTrait.getName()

            tr.append(TDCell(HT.TD(" ", align="center", valign="center", Class=className)))
            tr.append(
                TDCell(
                    HT.TD(
                        HT.Input(type="radio", name=oneRadioName, value="primary"),
                        align="center",
                        valign="center",
                        Class=className,
                    )
                )
            )
            tr.append(
                TDCell(
                    HT.TD(
                        HT.Input(type="radio", name=oneRadioName, value="control"),
                        align="center",
                        valign="center",
                        Class=className,
                    )
                )
            )
            tr.append(
                TDCell(
                    HT.TD(
                        HT.Input(type="radio", name=oneRadioName, value="target", checked="true"),
                        align="center",
                        valign="center",
                        Class=className,
                    )
                )
            )
            tr.append(
                TDCell(
                    HT.TD(
                        HT.Input(type="radio", name=oneRadioName, value="ignored"),
                        align="center",
                        valign="center",
                        Class=className,
                    )
                )
            )

            tr.append(
                TDCell(
                    HT.TD(thisTrait.db.displayname, Class="fs12 fwn b1 c222"),
                    thisTrait.db.displayname,
                    thisTrait.db.displayname.upper(),
                )
            )

            tr.append(
                TDCell(
                    HT.TD(
                        HT.Href(
                            text=thisTrait.name,
                            url="javascript:showDatabase3('%s','%s','%s','')"
                            % (formName, thisTrait.db.name, thisTrait.name),
                            Class="fs12 fwn",
                        ),
                        nowrap="yes",
                        align="left",
                        Class=className,
                    ),
                    str(thisTrait.name),
                    thisTrait.name,
                )
            )

            # description column
            if thisTrait.db.type == "Publish":
                PhenotypeString = thisTrait.post_publication_description
                if thisTrait.confidential:
                    if not webqtlUtil.hasAccessToConfidentialPhenotypeTrait(
                        privilege=self.privilege, userName=self.userName, authorized_users=thisTrait.authorized_users
                    ):
                        PhenotypeString = thisTrait.pre_publication_description
                tr.append(TDCell(HT.TD(PhenotypeString, Class=className), PhenotypeString, PhenotypeString.upper()))
            elif thisTrait.db.type == "ProbeSet" or thisTrait.db.type == "Temp":
                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

                tr.append(TDCell(HT.TD(description_string, Class=className), description_string, description_string))
            else:
                tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz"))

            # location column
            if thisTrait.db.type == "Publish":
                tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz"))
            else:
                # ZS: trait_location_value is used for sorting
                trait_location_repr = "N/A"
                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))

                tr.append(
                    TDCell(
                        HT.TD(trait_location_repr, nowrap="yes", Class=className),
                        trait_location_repr,
                        trait_location_value,
                    )
                )

            if thisTrait.db.type == "ProbeSet":
                self.cursor.execute(
                    """
                                        select ProbeSetXRef.mean from ProbeSetXRef, ProbeSet
                                        where ProbeSetXRef.ProbeSetFreezeId = %d and
                                                ProbeSet.Id = ProbeSetXRef.ProbeSetId and
                                                ProbeSet.Name = '%s'
                                """
                    % (thisTrait.db.id, thisTrait.name)
                )
                result = self.cursor.fetchone()
                if result:
                    if result[0]:
                        mean = result[0]
                    else:
                        mean = 0
                else:
                    mean = 0

                # XZ, 06/05/2009: It is neccessary to turn on nowrap
                repr = "%2.3f" % mean
                tr.append(TDCell(HT.TD(repr, Class=className, align="right", nowrap="ON"), repr, mean))

            elif thisTrait.db.type == "Publish":
                self.cursor.execute(
                    """
                                select count(PublishData.value), sum(PublishData.value) from PublishData, PublishXRef, PublishFreeze
                                where PublishData.Id = PublishXRef.DataId and 
                                        PublishXRef.Id = %s and
                                        PublishXRef.InbredSetId = PublishFreeze.InbredSetId and
                                        PublishFreeze.Id = %d
                                """
                    % (thisTrait.name, thisTrait.db.id)
                )
                result = self.cursor.fetchone()

                if result:
                    if result[0] and result[1]:
                        mean = result[1] / result[0]
                    else:
                        mean = 0
                else:
                    mean = 0

                repr = "%2.3f" % mean
                tr.append(TDCell(HT.TD(repr, Class=className, align="right", nowrap="ON"), repr, mean))
            else:
                tr.append(TDCell(HT.TD("--", Class=className, align="left", nowrap="ON"), "--", 0))

            # Number of cases
            n_cases_value = 0
            n_cases_repr = "--"
            if thisTrait.db.type == "Publish":
                self.cursor.execute(
                    """
                                select count(PublishData.value) from PublishData, PublishXRef, PublishFreeze
                                where PublishData.Id = PublishXRef.DataId and 
                                        PublishXRef.Id = %s and
                                        PublishXRef.InbredSetId = PublishFreeze.InbredSetId and
                                        PublishFreeze.Id = %d
                                """
                    % (thisTrait.name, thisTrait.db.id)
                )
                result = self.cursor.fetchone()

                if result:
                    if result[0]:
                        n_cases_value = result[0]
                        n_cases_repr = result[0]

                if n_cases_value == "--":
                    tr.append(
                        TDCell(
                            HT.TD(n_cases_repr, Class=className, align="left", nowrap="on"), n_cases_repr, n_cases_value
                        )
                    )
                else:
                    tr.append(
                        TDCell(
                            HT.TD(n_cases_repr, Class=className, align="right", nowrap="on"),
                            n_cases_repr,
                            n_cases_value,
                        )
                    )

            elif thisTrait.db.type == "ProbeSet":
                self.cursor.execute(
                    """
                                select count(ProbeSetData.value) from ProbeSet, ProbeSetXRef, ProbeSetData, ProbeSetFreeze
                                where ProbeSet.Name='%s' and
                                        ProbeSetXRef.ProbeSetId = ProbeSet.Id and
                                        ProbeSetXRef.DataId = ProbeSetData.Id and
                                        ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and
                                        ProbeSetFreeze.Name = '%s'
                                """
                    % (thisTrait.name, thisTrait.db.name)
                )
                result = self.cursor.fetchone()

                if result:
                    if result[0]:
                        n_cases_value = result[0]
                        n_cases_repr = result[0]
                if n_cases_value == "--":
                    tr.append(
                        TDCell(
                            HT.TD(n_cases_repr, Class=className, align="left", nowrap="on"), n_cases_repr, n_cases_value
                        )
                    )
                else:
                    tr.append(
                        TDCell(
                            HT.TD(n_cases_repr, Class=className, align="right", nowrap="on"),
                            n_cases_repr,
                            n_cases_value,
                        )
                    )

            elif thisTrait.db.type == "Geno":
                self.cursor.execute(
                    """
                                select count(GenoData.value) from GenoData, GenoXRef, GenoFreeze, Geno, Strain
                                where Geno.SpeciesId = %s and Geno.Name='%s' and
                                        GenoXRef.GenoId = Geno.Id and
                                        GenoXRef.DataId = GenoData.Id and
                                        GenoXRef.GenoFreezeId = GenoFreeze.Id and
                                        GenoData.StrainId = Strain.Id and
                                        GenoFreeze.Name = '%s'
                                """
                    % (
                        webqtlDatabaseFunction.retrieveSpeciesId(self.cursor, thisTrait.db.riset),
                        thisTrait.name,
                        thisTrait.db.name,
                    )
                )
                result = self.cursor.fetchone()

                if result:
                    if result[0]:
                        n_cases_value = result[0]
                        n_cases_repr = result[0]
                if n_cases_value == "--":
                    tr.append(
                        TDCell(
                            HT.TD(n_cases_repr, Class=className, align="left", nowrap="on"), n_cases_repr, n_cases_value
                        )
                    )
                else:
                    tr.append(
                        TDCell(
                            HT.TD(n_cases_repr, Class=className, align="right", nowrap="on"),
                            n_cases_repr,
                            n_cases_value,
                        )
                    )

            else:
                tr.append(
                    TDCell(HT.TD(n_cases_repr, Class=className, align="left", nowrap="on"), n_cases_repr, n_cases_value)
                )

            if thisTrait.db.type != "Geno":
                # LRS and its location
                LRS_score_repr = "--"
                LRS_score_value = 0
                LRS_location_repr = "--"
                LRS_location_value = 1000000
                LRS_flag = 1

                # Max LRS and its Locus location
                if hasattr(thisTrait, "lrs") and hasattr(thisTrait, "locus") and thisTrait.lrs and thisTrait.locus:
                    self.cursor.execute(
                        """
                                        select Geno.Chr, Geno.Mb from Geno, Species
                                        where Species.Name = '%s' and
                                                Geno.Name = '%s' and
                                                Geno.SpeciesId = Species.Id
                                        """
                        % (species, thisTrait.locus)
                    )
                    result = self.cursor.fetchone()

                    if result:
                        if result[0] and result[1]:
                            LRS_Chr = result[0]
                            LRS_Mb = result[1]

                            # XZ: LRS_location_value is used for sorting
                            try:
                                LRS_location_value = int(LRS_Chr) * 1000 + float(LRS_Mb)
                            except:
                                if LRS_Chr.upper() == "X":
                                    LRS_location_value = 20 * 1000 + float(LRS_Mb)
                                else:
                                    LRS_location_value = ord(str(LRS_chr).upper()[0]) * 1000 + float(LRS_Mb)

                            LRS_score_repr = "%3.1f" % thisTrait.lrs
                            LRS_score_value = thisTrait.lrs
                            LRS_location_repr = "Chr%s: %.6f" % (LRS_Chr, float(LRS_Mb))
                            LRS_flag = 0

                            tr.append(
                                TDCell(
                                    HT.TD(LRS_score_repr, Class=className, align="right", nowrap="on"),
                                    LRS_score_repr,
                                    LRS_score_value,
                                )
                            )
                            tr.append(
                                TDCell(HT.TD(LRS_location_repr, Class=className), LRS_location_repr, LRS_location_value)
                            )

                if LRS_flag:
                    tr.append(TDCell(HT.TD(LRS_score_repr, Class=className), LRS_score_repr, LRS_score_value))
                    tr.append(TDCell(HT.TD(LRS_location_repr, Class=className), LRS_location_repr, LRS_location_value))
            else:
                tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", 0))
                tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", 1000000))

            tblobj_body.append(tr)

        return tblobj_body
	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 &deg;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'] = ''
예제 #7
0
	def retrieveData(self, strainlist=[]):
		assert self.db and self.cursor

		if self.db.type == 'Temp':
			query = '''
				SELECT 
					Strain.Name, TempData.value, TempData.SE, TempData.NStrain, TempData.Id 
				FROM 
					TempData, Temp, Strain 
				WHERE 
					TempData.StrainId = Strain.Id AND 
					TempData.Id = Temp.DataId AND 
					Temp.name = '%s'
				Order BY
					Strain.Name
				''' % self.name
		#XZ, 03/02/2009: Xiaodong changed Data to PublishData, SE to PublishSE
		elif self.db.type == 'Publish': 
			query = '''
				SELECT 
					Strain.Name, PublishData.value, PublishSE.error, NStrain.count, PublishData.Id 
				FROM 
					(PublishData, Strain, PublishXRef, PublishFreeze)
				left join PublishSE on 
					(PublishSE.DataId = PublishData.Id AND PublishSE.StrainId = PublishData.StrainId)
				left join NStrain on 
					(NStrain.DataId = PublishData.Id AND 
					NStrain.StrainId = PublishData.StrainId) 
				WHERE 
					PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND 
					PublishData.Id = PublishXRef.DataId AND PublishXRef.Id = %s AND 
					PublishFreeze.Id = %d AND PublishData.StrainId = Strain.Id
				Order BY
					Strain.Name
				''' % (self.name, self.db.id)

                #XZ, 03/02/2009: Xiaodong changed Data to ProbeData, SE to ProbeSE
		elif self.cellid:
			#Probe Data
			query = '''
				SELECT 
					Strain.Name, ProbeData.value, ProbeSE.error, ProbeData.Id 
				FROM 
					(ProbeData, ProbeFreeze, ProbeSetFreeze, ProbeXRef, 
					Strain, Probe, ProbeSet)
				left join ProbeSE on 
					(ProbeSE.DataId = ProbeData.Id AND ProbeSE.StrainId = ProbeData.StrainId)
				WHERE 
					Probe.Name = '%s' AND ProbeSet.Name = '%s' AND 
					Probe.ProbeSetId = ProbeSet.Id AND 
					ProbeXRef.ProbeId = Probe.Id AND 
					ProbeXRef.ProbeFreezeId = ProbeFreeze.Id AND 
					ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND 
					ProbeSetFreeze.Name = '%s' AND 
					ProbeXRef.DataId = ProbeData.Id AND 
					ProbeData.StrainId = Strain.Id
				Order BY
					Strain.Name
				''' % (self.cellid, self.name, self.db.name)
                #XZ, 03/02/2009: Xiaodong added this block for ProbeSetData and ProbeSetSE
		elif self.db.type == 'ProbeSet':
			#ProbeSet Data
                        query = '''
                                SELECT
                                        Strain.Name, ProbeSetData.value, ProbeSetSE.error, ProbeSetData.Id
                                FROM
                                        (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef)
                                left join ProbeSetSE on
                                        (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId)
                                WHERE
                                        ProbeSet.Name = '%s' AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND
                                        ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND
                                        ProbeSetFreeze.Name = '%s' AND
                                        ProbeSetXRef.DataId = ProbeSetData.Id AND
                                        ProbeSetData.StrainId = Strain.Id
                                Order BY
                                        Strain.Name
                                ''' % (self.name, self.db.name)
                #XZ, 03/02/2009: Xiaodong changeded Data to GenoData, SE to GenoSE
		else:
			#Geno Data
			#XZ: The SpeciesId is not necessary, but it's nice to keep it to speed up database search.
			query = '''
				SELECT 
					Strain.Name, GenoData.value, GenoSE.error, GenoData.Id 
				FROM 
					(GenoData, GenoFreeze, Strain, Geno, GenoXRef)
				left join GenoSE on
					(GenoSE.DataId = GenoData.Id AND GenoSE.StrainId = GenoData.StrainId)
				WHERE 
					Geno.SpeciesId = %s AND Geno.Name = '%s' AND GenoXRef.GenoId = Geno.Id AND
					GenoXRef.GenoFreezeId = GenoFreeze.Id AND 
					GenoFreeze.Name = '%s' AND 
					GenoXRef.DataId = GenoData.Id AND 
					GenoData.StrainId = Strain.Id
				Order BY
					Strain.Name
				''' % (webqtlDatabaseFunction.retrieveSpeciesId(self.cursor, self.db.riset), self.name, self.db.name)

		
		self.cursor.execute(query)
		results = self.cursor.fetchall()
		self.data.clear()
		if results:
			self.mysqlid = results[0][-1]
			if strainlist:
				for item in results:
					if item[0] in strainlist:
						val = item[1]
						if val != None:
							var = item[2]
							ndata = None
							if self.db.type in ('Publish', 'Temp'):
								ndata = item[3]
							self.data[item[0]] = webqtlCaseData(val, var, ndata)
				#end for
			else:	
				for item in results:
					val = item[1]
					if val != None:
						var = item[2]
						ndata = None
						if self.db.type in ('Publish', 'Temp'):
							ndata = item[3]
						self.data[item[0]] = webqtlCaseData(val, var, ndata)
				#end for
			#end if
		else:
			pass
	def updateResultPage(self, fd, thisTrait):
	
		comments = []
		ctime = time.ctime()
		##Start Updating
		dataID = -1
		if thisTrait.db.type == 'Publish':
			self.cursor.execute("SelecT PublishXRef.InbredSetId, PublishXRef.DataId, PublishXRef.PublicationId, PublishXRef.PhenotypeId, PublishXRef.Sequence from PublishXRef, PublishFreeze where PublishXRef.InbredSetId= PublishFreeze.InbredSetId and  PublishFreeze.Name = '%s' and PublishXRef.Id = %s" % (thisTrait.db.name, thisTrait.name))
			PInbredSetId, dataID, PublicationId, PhenotypeId, Sequence = self.cursor.fetchall()[0]
			
		modifyField = self.formdata.getvalue('modifiedField')
		###Modify Trait Informations
		if modifyField:
			modifyField = string.split(modifyField, '::')
			comments += modifyField
			updateHomologeneid = False

			if thisTrait.db.type == 'Publish':
				PhenotypeItemUpdate = []
				PhenotypeItemValues = []
				PublicationItemUpdate = []
				PublicationItemValues = []

				for item in modifyField:
					itemvalue = self.formdata.getvalue(item)

					#XZ: identify Phenotype items
					if item in ['pre_publication_description', 'post_publication_description', 'original_description', 'pre_publication_abbreviation', 'post_publication_abbreviation', 'lab_code', 'submitter', 'owner', 'authorized_users', 'units']:
						if itemvalue != None: #XZ: the problem is that the item value can not be deleted
							PhenotypeItemUpdate.append('%s=%%s' % item)
							PhenotypeItemValues.append(itemvalue)

							continue #XZ: this is important to distinguish Phenotype item and Publication item

					elif item == "pubmed_id":
						#Only integer allowed in this field
						try:
							itemvalue = int(itemvalue)
						except:
							itemvalue = None
							
						#whether old PMID exists
						self.cursor.execute("SelecT PubMed_ID from Publication where Id = %d" % PublicationId)
						oldPMID = self.cursor.fetchone()
						if oldPMID:
							oldPMID = oldPMID[0]
						
						#whether new PMID already exists
						newPMID = None
						self.cursor.execute("SelecT Id from Publication where PubMed_ID = %d" % itemvalue)
						newPMID = self.cursor.fetchone()
						if newPMID:
							newPMID = newPMID[0]
						
						##the logic is still not very clear here
						if newPMID:
							#new PMID in record
							self.cursor.execute("Update PublishXRef set PublicationId = %d where InbredSetId=%d and PhenotypeId=%d and PublicationId=%d and Sequence=%d" % (newPMID, PInbredSetId, PhenotypeId, PublicationId, Sequence))
							#no need to update other fields
							PublicationItemUpdate = []
							break
						elif itemvalue:
							#have new PMID, but not in record or need to change
							self.cursor.execute("Update Publication set pubmed_id=%d where Id = %s" % (itemvalue,PublicationId))
						else:
							#no new PMID
							if oldPMID:
								#remove a pubmed_id, don't know if this ever gonna happen
								self.cursor.execute("SelecT max(Id) from Publication")
								maxId = self.cursor.fetchone()[0] + 1
								self.cursor.execute("SelecT * from Publication where Id = %d" % PublicationId)
								oldRecs = list(self.cursor.fetchone())
								oldRecs[0] = maxId
								oldRecs[1] = None
								NFields = ['%s'] * len(oldRecs)
								query = "insert into Publication Values (%s)" % string.join(NFields, ',')
								self.cursor.execute(query, tuple(oldRecs))
								self.cursor.execute("Update PublishXRef set PublicationId = %d where InbredSetId=%d and PhenotypeId=%d and PublicationId=%d and Sequence=%d" % (maxId, PInbredSetId, PhenotypeId, PublicationId, Sequence))
								PublicationId = maxId
								pass
							else:
								pass
						continue
					else:
						pass

					if itemvalue:						
						PublicationItemUpdate.append('%s=%%s' % item)
						PublicationItemValues.append(itemvalue)

				if PhenotypeItemUpdate:
                                        updateStr= string.join(PhenotypeItemUpdate,',')
                                        query = "Update Phenotype set %s where Id = %s" % (updateStr, PhenotypeId)
                                        self.cursor.execute(query,tuple(PhenotypeItemValues))

				if PublicationItemUpdate:
					updateStr= string.join(PublicationItemUpdate,',')
					query = "Update Publication set %s where Id = %s" % (updateStr, PublicationId)
					self.cursor.execute(query,tuple(PublicationItemValues))

			else: #ProbeSet or Genotype Data
				itemValues = []
	                        itemUpdate = []

				for item in modifyField:
					itemvalue = self.formdata.getvalue(item)
					if itemvalue != None:
						itemvalue = string.strip(itemvalue)
					else:
						pass
					if item == 'homologeneid':
						updateHomologeneid = True
						new_homologeneid = 0

						if itemvalue and len(itemvalue) > 0:
							try:
								new_homologeneid = int(itemvalue)
							except:
								heading = "Record Updating Result"
								detail = ["Can't update database. Homologeneid must be integer!"]
								self.error(heading=heading,detail=detail,error="Error")
								return
					else:
						itemUpdate.append('%s=%%s' % item) #XZ: Use %% to put a % in the output string
						itemValues.append(itemvalue)

				if itemUpdate:
					updateStr= string.join(itemUpdate,', ')
					comments = "%s modified %s at %s\n" % (self.userName, string.join(comments, ', '), ctime)
					if thisTrait.db.type == "ProbeSet":#XZ, June 29, 2010: The algorithm is not good. Need to fix it later.
					 	if thisTrait.chipid in (2,4):
					 		if thisTrait.name[-2:] == '_A':
								thisTrait.name = string.replace(thisTrait.name, '_A', '')
					 		elif thisTrait.name[-2:] == '_B':
								thisTrait.name = string.replace(thisTrait.name, '_B', '')
							else:
								pass
							query = "Update %s set %s where Name like '%s%%%%'" % (thisTrait.db.type,updateStr,thisTrait.name)
							self.cursor.execute(query,tuple(itemValues))
							self.cursor.execute("Update %s set comments = CONCAT(comments,'%s') where Name like '%s%%%%'" % (thisTrait.db.type, comments, thisTrait.name))
						elif thisTrait.sequence:
							query = "Update %s set %s where BlatSeq='%s'" % (thisTrait.db.type,updateStr,thisTrait.sequence)
							self.cursor.execute(query,tuple(itemValues))
							self.cursor.execute("Update %s set comments = CONCAT(comments,'%s') where BlatSeq='%s'" % (thisTrait.db.type, comments, thisTrait.sequence))
						else:
							query = "Update %s set %s where Name='%s'" % (thisTrait.db.type,updateStr,thisTrait.name)
							self.cursor.execute(query,tuple(itemValues))
							self.cursor.execute("Update %s set comments = CONCAT(comments,'%s') where Name='%s'" % (thisTrait.db.type, comments, thisTrait.name))
					else: #XZ: Genotype
						query = "Update %s set %s where SpeciesId=%s and Name='%s'" % (thisTrait.db.type,updateStr, webqtlDatabaseFunction.retrieveSpeciesId(self.cursor, thisTrait.db.riset), thisTrait.name)
						self.cursor.execute(query,tuple(itemValues))

				if updateHomologeneid: #XZ: to update homologene id must be after updating geneid.
				#XZ: In one species, one homologeneid can have multiple geneid. One geneid only can have one homologeneid.
				#XZ: In Homologene table, GeneId is unique.
                                #XZ: Geneid might just being updated.
                                        thisTrait = webqtlTrait(fullname=self.formdata.getvalue('fullname'), cursor=self.cursor)
                                        thisTrait.retrieveInfo()

					if not thisTrait.geneid:
                                        	heading = "Record Updating Result"
						detail = ["There is no geneid associated with this trait. Can't update homologeneid info"]
						self.error(heading=heading,detail=detail,error="Error")
						return
					else:
						query = """
							SELECT Species.TaxonomyId
							FROM Species, InbredSet
							WHERE InbredSet.Name = '%s' and InbredSet.SpeciesId = Species.Id
							""" % thisTrait.db.riset
						self.cursor.execute(query)
	                                        taxonomyId = self.cursor.fetchone()[0]

						if not new_homologeneid:
							query = """DELETE FROM Homologene WHERE GeneId=%s""" % thisTrait.geneid
                                                        self.cursor.execute(query)
						else:
							query = """SELECT GeneId FROM Homologene WHERE GeneId=%s""" % thisTrait.geneid
							self.cursor.execute(query)
							result = self.cursor.fetchone()

							if not result:
	                                                        query = """INSERT into Homologene (HomologeneId, GeneId, TaxonomyId) VALUES (%s, %s, %s)""" % (new_homologeneid, thisTrait.geneid, taxonomyId)
        	                                                self.cursor.execute(query)
							else:
								query = """UPDATE Homologene SET HomologeneId=%s WHERE GeneId=%s""" % (new_homologeneid, thisTrait.geneid)
								self.cursor.execute(query)


                                #XZ: It's critical to get lasted info first, then update gene level info across traits by geneid.
				#XZ: Need to build index on GeneId. Otherwise, it's too slow.
                                if thisTrait.db.type == 'ProbeSet':
                                        thisTrait = webqtlTrait(fullname=self.formdata.getvalue('fullname'), cursor=self.cursor)
                                        thisTrait.retrieveInfo()

					if thisTrait.geneid:
						if 'symbol' in modifyField:
							if thisTrait.symbol:
								query = """UPDATE ProbeSet SET Symbol='%s' WHERE GeneId=%s""" % (thisTrait.symbol, thisTrait.geneid)
							else:
                        	                                query = """UPDATE ProbeSet SET Symbol=NULL WHERE GeneId=%s""" % (thisTrait.geneid)
							self.cursor.execute(query)

						if 'alias' in modifyField:
							if thisTrait.alias:
								newalias = []
								for alias in re.split('[,;|\s]+', thisTrait.alias):
									if alias and 0<len(alias):
										newalias.append(alias)
								thisTrait.alias = '; '.join(newalias)
								query = """UPDATE ProbeSet SET alias='%s' WHERE GeneId=%s""" % (thisTrait.alias, thisTrait.geneid)
							else:
								query = """UPDATE ProbeSet SET alias=NULL WHERE GeneId=%s""" % (thisTrait.geneid)
							self.cursor.execute(query)

						if 'description' in modifyField:
							if thisTrait.description: #XZ: Attention, we must use "%s" instead of '%s'. Otherwise, to insert 3'UTR will generate error.
								query = """UPDATE ProbeSet SET description="%s" WHERE GeneId=%s""" % (thisTrait.description, thisTrait.geneid)
							else:
								query = """UPDATE ProbeSet SET description=NULL WHERE GeneId=%s""" % (thisTrait.geneid)
							self.cursor.execute(query)

						if 'strand_gene' in modifyField:
							if thisTrait.strand_gene:
								query = """UPDATE ProbeSet SET Strand_Gene='%s' WHERE GeneId=%s""" % (thisTrait.strand_gene, thisTrait.geneid)
							else:
								query = """UPDATE ProbeSet SET Strand_Gene=NULL WHERE GeneId=%s""" % (thisTrait.geneid)
							self.cursor.execute(query)

						if 'unigeneid' in modifyField:
							if thisTrait.unigeneid:
								query = """UPDATE ProbeSet SET UniGeneId='%s' WHERE GeneId=%s""" % (thisTrait.unigeneid, thisTrait.geneid)
							else:
								query = """UPDATE ProbeSet SET UniGeneId=NULL WHERE GeneId=%s""" % (thisTrait.geneid)
							self.cursor.execute(query)

						if 'refseq_transcriptid' in modifyField:
							if thisTrait.refseq_transcriptid:
								query = """UPDATE ProbeSet SET RefSeq_TranscriptId='%s' WHERE GeneId=%s""" % (thisTrait.refseq_transcriptid, thisTrait.geneid)
							else:
								query = """UPDATE ProbeSet SET RefSeq_TranscriptId=NULL WHERE GeneId=%s""" % (thisTrait.geneid)
							self.cursor.execute(query)

						if 'genbankid' in modifyField:
							if thisTrait.genbankid:
								query = """UPDATE ProbeSet SET GenbankId='%s' WHERE GeneId=%s""" % (thisTrait.genbankid, thisTrait.geneid)
							else:
								query = """UPDATE ProbeSet SET GenbankId=NULL WHERE GeneId=%s""" % (thisTrait.geneid)
							self.cursor.execute(query)

						if 'omim' in modifyField:
							if thisTrait.omim:
								query = """UPDATE ProbeSet SET OMIM='%s' WHERE GeneId=%s""" % (thisTrait.omim, thisTrait.geneid)
							else:
								query = """UPDATE ProbeSet SET OMIM=NULL WHERE GeneId=%s""" % (thisTrait.geneid)
							self.cursor.execute(query)


		###Modify Trait Data
		if thisTrait.db.type == 'Publish' and dataID > 0 and fd.formdata.getvalue("modifiedDataField"):
			StrainIds = []
			for item in fd.strainlist:
				self.cursor.execute('SelecT Id from Strain where Name = "%s"' % item)
				StrainId = self.cursor.fetchone()
				if not StrainId:
					raise ValueError
				else:
					StrainIds.append(StrainId[0])
			comments.append('Trait Value')
			#XZ, 03/05/2009: Xiaodong changed Data to PublishData, SE to PublishSE
			self.cursor.execute('delete from PublishData where Id = %d' % dataID)
			self.cursor.execute('delete from PublishSE where DataId = %d' % dataID)
			self.cursor.execute('delete from NStrain where DataId = %d' % dataID)
				
			for i, strain in enumerate(fd.strainlist):
				sId = StrainIds[i]
				if fd.allTraitData.has_key(strain):
					tdata = fd.allTraitData[strain]
					_val, _var, _N = tdata.val, tdata.var, tdata.N
					if _val != None:
						#XZ, 03/05/2009: Xiaodong changed Data to PublishData, SE to PublishSE
						self.cursor.execute('insert into PublishData values(%d, %d, %s)' % (dataID, sId, _val))
					if _var != None:
						self.cursor.execute('insert into PublishSE values(%d, %d, %s)' % (dataID, sId, _var))
					if _N != None:
						self.cursor.execute('insert into NStrain values(%d, %d, %s)' % (dataID, sId, _N))
				else:
					pass
			#end for
		else:
			pass
		TD_LR = HT.TD(valign="top", bgcolor="#eeeeee",height=200,width="100%")
		main_title = HT.Paragraph(" Record Updating Result", Class="title")

		TD_LR.append(main_title,HT.Blockquote('Successfully updated record %s in database ' % thisTrait.name, thisTrait.db.genHTML(), '.'))
		if thisTrait.db.type == 'Publish':
			comments = "%s modified %s at %s\n" % (self.userName, string.join(comments, ', '), ctime)
			self.cursor.execute("Update PublishXRef set comments = CONCAT(comments,'%s') where InbredSetId=%d and PhenotypeId=%d and PublicationId=%d and Sequence=%d" % (comments,  PInbredSetId, PhenotypeId, PublicationId, Sequence))
			
		if 0:
			heading = "Record Updating Result"
			detail = ["Can't update database. The server may be down at this time or you don't have the permission"]
			self.error(heading=heading,detail=detail,error="Error")
			return
		self.dict['body'] = str(TD_LR)
예제 #9
0
    def getCollectionTableBody(self, RISet=None, traitList=None, formName=None, species=''):

        tblobj_body = []

        className = "fs12 fwn b1 c222"

        for thisTrait in traitList:
            tr = []

            if not thisTrait.haveinfo:
                thisTrait.retrieveInfo(QTL=1)

            if thisTrait.riset != RISet:
                continue

            trId = str(thisTrait)

            #XZ: check box column
            tr.append(TDCell(HT.TD(HT.Input(type="checkbox", Class="checkallbox", name="searchResult",value=trId, onClick="highlight(this)"), nowrap="on", Class=className), text=trId))

            #XZ: Dataset column
            tr.append(TDCell(HT.TD(thisTrait.db.displayname, Class="fs12 fwn b1 c222"), thisTrait.db.displayname, thisTrait.db.displayname.upper()))

            #XZ: Trait ID column
            if thisTrait.cellid:
                tr.append(TDCell(HT.TD(HT.Href(text=thisTrait.cellid,url="javascript:showDatabase3('%s','%s','%s','%s')" % (formName, thisTrait.db.name, thisTrait.name, thisTrait.cellid), Class="fs12 fwn"), nowrap="yes",align="left", Class=className),str(thisTrait.cellid), thisTrait.cellid))
            else:
                tr.append(TDCell(HT.TD(HT.Href(text=thisTrait.getGivenName(),url="javascript:showDatabase3('%s','%s','%s','')" % (formName, thisTrait.db.name, thisTrait.name), Class="fs12 fwn"), nowrap="yes",align="left", Class=className),str(thisTrait.name), thisTrait.name))

            #XZ: Symbol column and Description column
            if (thisTrait.db.type == "Publish"):
                AbbreviationString = "--"
                if (thisTrait.post_publication_abbreviation != None):
                    AbbreviationString = thisTrait.post_publication_abbreviation
                PhenotypeString = thisTrait.post_publication_description
                if thisTrait.confidential:
                    if not webqtlUtil.hasAccessToConfidentialPhenotypeTrait(privilege=self.privilege, userName=self.userName, authorized_users=thisTrait.authorized_users):
                        if thisTrait.pre_publication_abbreviation:
                            AbbreviationString = thisTrait.pre_publication_abbreviation
                        else:
                            AbbreviationString = "--"
                        PhenotypeString = thisTrait.pre_publication_description

                if AbbreviationString == "--":
                    tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz"))
                else:
                    tr.append(TDCell(HT.TD(AbbreviationString, Class=className), AbbreviationString, AbbreviationString.upper()))
                if PhenotypeString:
                    PhenotypeString_upper = PhenotypeString.upper()
                else:
                    PhenotypeString_upper = PhenotypeString
                tr.append(TDCell(HT.TD(PhenotypeString, Class=className), PhenotypeString, PhenotypeString_upper))


            elif (thisTrait.db.type == "ProbeSet" or thisTrait.db.type == "Temp"):
                description_string = str(thisTrait.description).strip()
                if (thisTrait.db.type == "ProbeSet"):
                    if (thisTrait.symbol != None):
                        if thisTrait.geneid:
                            symbolurl = HT.Href(text=thisTrait.symbol,target='_blank',url="http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?db=gene&cmd=Retrieve&dopt=Graphics&list_uids=%s" % thisTrait.geneid, Class="font_black fs12 fwn")
                        else:
                            symbolurl = HT.Href(text=thisTrait.symbol,target='_blank',url="http://www.ncbi.nlm.nih.gov/entrez/query.fcgi?CMD=search&DB=gene&term=%s" % thisTrait.symbol, Class="font_black fs12 fwn")
                        tr.append(TDCell(HT.TD(symbolurl, align="left", Class="fs12 fwn b1 c222 fsI"), thisTrait.symbol, thisTrait.symbol))
                    else:
                        tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz"))
                    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:
                    tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz"))
                tr.append(TDCell(HT.TD(description_string, Class=className), description_string, description_string))
            else:
                if (thisTrait.name != None):
                    tr.append(TDCell(HT.TD(thisTrait.name, Class="fs12 fwn b1 c222"), thisTrait.name, thisTrait.name))
                else:
                    tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz"))
                tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz"))

            #XZ: Location column
            if (thisTrait.db.type == "Publish"):
                tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", "Zz"))
            else:
                if thisTrait.db.type == "ProbeSet" and thisTrait.cellid:
                    EnsemblProbeSetID = thisTrait.name
                    if '_at' in thisTrait.name:
                        EnsemblProbeSetID = thisTrait.name[0:thisTrait.name.index('_at')+3]

                    #These tables (Ensembl) were created by Xusheng Wang in 2010 and are mm9 (so they'll need to be changed at some point to be mm10.
                    self.cursor.execute('''
                                    SELECT EnsemblProbeLocation.*
                                    FROM EnsemblProbeLocation, EnsemblProbe, EnsemblChip, GeneChipEnsemblXRef, ProbeFreeze, ProbeSetFreeze
                                    WHERE EnsemblProbeLocation.ProbeId=EnsemblProbe.Id and EnsemblProbe.ChipId=GeneChipEnsemblXRef.EnsemblChipId and
                                            GeneChipEnsemblXRef.GeneChipId=ProbeFreeze.ChipId and EnsemblProbe.Name=%s and EnsemblProbe.ProbeSet=%s and
                                            ProbeSetFreeze.Id=%s and ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id group by Chr, Start, End'''
                                    ,(thisTrait.cellid, EnsemblProbeSetID, thisTrait.db.id))
                    LocationFields = self.cursor.fetchall()

                    Chr=''
                    Mb=''
                    Start=''
                    End=''
                    if (len(LocationFields)>=1):
                        Chr,Start,Start_2016,End,End_2016,Strand,MisMatch,ProbeId = map(self.nullRecord,LocationFields[0])
                        Start /= 1000000.0
                        End /= 1000000.0
                        Mb = Start
                    if (len(LocationFields)>1):
                        self.cursor.execute('''
                                        SELECT ProbeSet.Chr, ProbeSet.Mb FROM ProbeSet, ProbeFreeze, ProbeSetFreeze
                                        WHERE ProbeSet.ChipId=ProbeFreeze.ChipId and ProbeSet.Name=%s and ProbeSetFreeze.Id=%s and
                                                ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id'''
                                        ,(thisTrait.name, thisTrait.db.id))
                        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, ProbeSetFreeze
                                        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 ProbeSetFreeze.Id=%s and ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id order by Mb limit 1'''
                                        ,(ProbeSetMb, thisTrait.cellid, EnsemblProbeSetID, ProbeSetChr, thisTrait.db.id))
                        NewLocationFields = self.cursor.fetchall()
                        if (len(NewLocationFields)>0):
                            Chr,Start,Start_2016,End,End_2016,Strand,MisMatch,ProbeId,Mb = map(self.nullRecord,NewLocationFields[0])
                            Start /= 1000000.0
                            End /= 1000000.0
                            Mb = Start

                    #ZS: trait_location_value is used for sorting
                    trait_location_repr = "--"
                    trait_location_value = 1000000

                    if Chr and Mb:
                        try:
                            trait_location_value = int(Chr)*1000 + Mb
                        except:
                            if Chr.upper() == "X":
                                trait_location_value = 20*1000 + Mb
                            else:
                                trait_location_value = ord(str(Chr).upper()[0])*1000 + Mb

                        trait_location_repr = "Chr%s: %.6f" % (Chr, float(Mb) )

                    tr.append(TDCell(HT.TD(trait_location_repr, nowrap='ON', Class=className), trait_location_repr, trait_location_value))

                else:

                    #ZS: 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) )

                    tr.append(TDCell(HT.TD(trait_location_repr, nowrap='ON', Class=className), trait_location_repr, trait_location_value))

            #XZ: Mean column
            if (thisTrait.db.type == "ProbeSet"):
                if thisTrait.cellid:
                    mean = -10000.0
                    try:
                        thisTrait.retrieveData()
                        mean, median, var, stdev, sem, N = reaper.anova(thisTrait.exportInformative()[1])
                    except:
                        pass
                    repr = '%2.3f' % mean
                    mean = '%2.2f' % mean
                    tr.append(TDCell(HT.TD(repr, Class=className, align='right', nowrap='ON'),repr, mean))
                else:
                    self.cursor.execute("""
                            select ProbeSetXRef.mean from ProbeSetXRef, ProbeSet
                            where ProbeSetXRef.ProbeSetFreezeId = %d and
                                    ProbeSet.Id = ProbeSetXRef.ProbeSetId and
                                    ProbeSet.Name = '%s'
                    """ % (thisTrait.db.id, thisTrait.name))
                    result = self.cursor.fetchone()
                    if result:
                        if result[0]:
                            mean = result[0]
                        else:
                            mean=0
                    else:
                        mean = 0

                    #XZ, 06/05/2009: It is neccessary to turn on nowrap
                    repr = "%2.3f" % mean
                    tr.append(TDCell(HT.TD(repr, Class=className, align='right', nowrap='ON'),repr, mean))

            elif (thisTrait.db.type == "Publish"):
                self.cursor.execute("""
                select count(PublishData.value), sum(PublishData.value) from PublishData, PublishXRef, PublishFreeze
                where PublishData.Id = PublishXRef.DataId and
                        PublishXRef.Id = %s and
                        PublishXRef.InbredSetId = PublishFreeze.InbredSetId and
                        PublishFreeze.Id = %d
                """ % (thisTrait.name, thisTrait.db.id))
                result = self.cursor.fetchone()

                if result:
                    if result[0] and result[1]:
                        mean = result[1]/result[0]
                    else:
                        mean = 0
                else:
                    mean = 0

                repr = "%2.3f" % mean
                tr.append(TDCell(HT.TD(repr, Class=className, align='right', nowrap='ON'),repr, mean))
            else:
                tr.append(TDCell(HT.TD("--", Class=className, align='left', nowrap='ON'),"--", 0))

            #Number of cases
            n_cases_value = 0
            n_cases_repr = "--"
            if (thisTrait.db.type == "Publish"):
                self.cursor.execute("""
                select count(PublishData.value) from PublishData, PublishXRef, PublishFreeze
                where PublishData.Id = PublishXRef.DataId and
                        PublishXRef.Id = %s and
                        PublishXRef.InbredSetId = PublishFreeze.InbredSetId and
                        PublishFreeze.Id = %d
                """ % (thisTrait.name, thisTrait.db.id))
                result = self.cursor.fetchone()

                if result:
                    if result[0]:
                        n_cases_value = result[0]
                        n_cases_repr = result[0]

                if (n_cases_value == "--"):
                    tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='left', nowrap="on"), n_cases_repr, n_cases_value))
                else:
                    tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='right', nowrap="on"), n_cases_repr, n_cases_value))

            elif (thisTrait.db.type == "ProbeSet"):
                self.cursor.execute("""
                select count(ProbeSetData.value) from ProbeSet, ProbeSetXRef, ProbeSetData, ProbeSetFreeze
                where ProbeSet.Name='%s' and
                        ProbeSetXRef.ProbeSetId = ProbeSet.Id and
                        ProbeSetXRef.DataId = ProbeSetData.Id and
                        ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and
                        ProbeSetFreeze.Name = '%s'
                """ % (thisTrait.name, thisTrait.db.name))
                result = self.cursor.fetchone()

                if result:
                    if result[0]:
                        n_cases_value = result[0]
                        n_cases_repr = result[0]
                if (n_cases_value == "--"):
                    tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='left', nowrap="on"), n_cases_repr, n_cases_value))
                else:
                    tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='right', nowrap="on"), n_cases_repr, n_cases_value))

            elif (thisTrait.db.type == "Geno"):
                self.cursor.execute("""
                select count(GenoData.value) from GenoData, GenoXRef, GenoFreeze, Geno, Strain
                where Geno.SpeciesId = %s and Geno.Name='%s' and
                        GenoXRef.GenoId = Geno.Id and
                        GenoXRef.DataId = GenoData.Id and
                        GenoXRef.GenoFreezeId = GenoFreeze.Id and
                        GenoData.StrainId = Strain.Id and
                        GenoFreeze.Name = '%s'
                """ % (webqtlDatabaseFunction.retrieveSpeciesId(self.cursor, thisTrait.db.riset), thisTrait.name, thisTrait.db.name))
                result = self.cursor.fetchone()

                if result:
                    if result[0]:
                        n_cases_value = result[0]
                        n_cases_repr = result[0]
                if (n_cases_value == "--"):
                    tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='left', nowrap="on"), n_cases_repr, n_cases_value))
                else:
                    tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='right', nowrap="on"), n_cases_repr, n_cases_value))

            else:
                tr.append(TDCell(HT.TD(n_cases_repr, Class=className, align='left', nowrap="on"), n_cases_repr, n_cases_value))

            #XZ: Max LRS column and Max LRS Location column
            if (thisTrait.db.type != "Geno"):
                #LRS value
                LRS_score_repr = '--'
                LRS_score_value = 0
		if hasattr(thisTrait, 'lrs') and thisTrait.lrs:
			LRS_score_repr = '%3.1f' % thisTrait.lrs
			LRS_score_value = thisTrait.lrs
		tr.append(TDCell(HT.TD(LRS_score_repr, Class=className, align='right', nowrap="on"), LRS_score_repr, LRS_score_value))

		#LRS location
                LRS_location_repr = '--'
                LRS_location_value = 1000000
                LRS_flag = 1

                #Max LRS and its Locus location
                if hasattr(thisTrait, 'lrs') and hasattr(thisTrait, 'locus') and thisTrait.lrs and thisTrait.locus:
                    self.cursor.execute("""
                    select Geno.Chr, Geno.Mb from Geno, Species
                    where Species.Name = '%s' and
                            Geno.Name = '%s' and
                            Geno.SpeciesId = Species.Id
                    """ % (species, thisTrait.locus))
                    result = self.cursor.fetchone()

                    if result:
                        if result[0] and result[1]:
                            LRS_Chr = result[0]
                            LRS_Mb = result[1]

                            #XZ: LRS_location_value is used for sorting
                            try:
                                LRS_location_value = int(LRS_Chr)*1000 + float(LRS_Mb)
                            except:
                                if LRS_Chr.upper() == 'X':
                                    LRS_location_value = 20*1000 + float(LRS_Mb)
                                else:
                                    LRS_location_value = ord(str(LRS_chr).upper()[0])*1000 + float(LRS_Mb)

                            LRS_location_repr = 'Chr%s: %.6f' % (LRS_Chr, float(LRS_Mb))
                            LRS_flag = 0

                            tr.append(TDCell(HT.TD(LRS_location_repr, Class=className), LRS_location_repr, LRS_location_value))

                if LRS_flag:
                    tr.append(TDCell(HT.TD(LRS_location_repr, Class=className), LRS_location_repr, LRS_location_value))
            else:
                tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", 0))
                tr.append(TDCell(HT.TD("--", align="left", Class=className), "--", 1000000))

            try:
                additive = 	'%3.3f' % thisTrait.additive
            except Exception:
                additive = 	''
            tr.append(TDCell(HT.TD(additive, Class=className, align="right", nowrap="on"), additive, additive))
			
            tblobj_body.append(tr)

        return tblobj_body