def delete_publishdata_publishxrefid(publishxrefid, inbredsetid):
    cursor, con = utilities.get_cursor()
    sql = """
        DELETE PublishData
        FROM PublishXRef,PublishData
        WHERE PublishXRef.`Id`=%s
        AND PublishXRef.`InbredSetId`=%s
        AND PublishXRef.`DataId`=PublishData.`Id`
        """
    cursor.execute(sql, (publishxrefid, inbredsetid))
    sql = """
        DELETE PublishSE
        FROM PublishXRef,PublishSE
        WHERE PublishXRef.`Id`=%s
        AND PublishXRef.`InbredSetId`=%s
        AND PublishXRef.`DataId`=PublishSE.`DataId`
        """
    cursor.execute(sql, (publishxrefid, inbredsetid))
    sql = """
        DELETE NStrain
        FROM PublishXRef,NStrain
        WHERE PublishXRef.`Id`=%s
        AND PublishXRef.`InbredSetId`=%s
        AND PublishXRef.`DataId`=NStrain.`DataId`
        """
    cursor.execute(sql, (publishxrefid, inbredsetid))
    con.close()

def check_or_insert_geno(params, marker):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT Geno.`Id`
        FROM Geno
        WHERE Geno.`SpeciesId`=%s
        AND Geno.`Name` like %s
        """
    cursor.execute(sql, (speciesid, locus)) #This is correct
    result = cursor.fetchone()
    if result:
        geno_id = result[0]
        print("get geno record: ", geno_id)
    else:
        sql = """
            INSERT INTO Geno
            SET
            Geno.`SpeciesId`=%s,
            Geno.`Name`=%s,
            Geno.`Marker_Name`=%s,
            Geno.`Chr`=%s,
            Geno.`Mb`=%s
            """
        cursor.execute(sql, (species_id, locus, locus, chr, mb))
        row_count = cursor.rowcount
        geno_id = con.insert_id()
        print("INSERT INTO Geno: %d record: %d" % (row_count, geno_id))
    return geno_id

def insert_strain(inbredsetid, strainname, updatestrainxref=None):
    speciesid = get_species(inbredsetid)[0]
    cursor, con = utilities.get_cursor()
    sql = """
        INSERT INTO Strain
        SET
        Strain.`Name`=%s,
        Strain.`Name2`=%s,
        Strain.`SpeciesId`=%s
        """
    cursor.execute(sql, (strainname, strainname, speciesid))
    strainid = con.insert_id()
    if updatestrainxref:
        sql = """
            SELECT StrainXRef.`OrderId`
            FROM StrainXRef
            where StrainXRef.`InbredSetId`=%s
            ORDER BY StrainXRef.`OrderId` DESC
            LIMIT 1
            """
        cursor.execute(sql, (inbredsetid))
        re = cursor.fetchone()
        orderid = re[0] + 1
        #
        sql = """
            INSERT INTO StrainXRef
            SET
            StrainXRef.`InbredSetId`=%s,
            StrainXRef.`StrainId`=%s,
            StrainXRef.`OrderId`=%s,
            StrainXRef.`Used_for_mapping`=%s,
            StrainXRef.`PedigreeStatus`=%s
            """
        cursor.execute(sql, (inbredsetid, strainid, orderid, "N", None))

def delete(publishxrefid, inbredsetid):
    cursor = utilities.get_cursor()
    sql = """
        DELETE Phenotype
        FROM PublishXRef,Phenotype
        WHERE PublishXRef.`Id`=%s
        AND PublishXRef.`InbredSetId`=%s
        AND PublishXRef.`PhenotypeId`=Phenotype.`Id`
        """
    cursor.execute(sql, (publishxrefid, inbredsetid))
    sql = """
        DELETE PublishData
        FROM PublishXRef,PublishData
        WHERE PublishXRef.`Id`=%s
        AND PublishXRef.`InbredSetId`=%s
        AND PublishXRef.`DataId`=PublishData.`Id`
        """
    cursor.execute(sql, (publishxrefid, inbredsetid))
    sql = """
        DELETE PublishXRef
        FROM PublishXRef
        WHERE PublishXRef.`Id`=%s
        AND PublishXRef.`InbredSetId`=%s
        """
    cursor.execute(sql, (publishxrefid, inbredsetid))

def check_or_insert_geno(config_dic, marker_dic):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT Geno.`Id`
        FROM Geno
        WHERE Geno.`SpeciesId`=%s
        AND Geno.`Name` like %s
        """
    cursor.execute(sql, (config_dic["speciesid"], marker_dic['locus']))
    result = cursor.fetchone()
    if result:
        genoid = result[0]
        print("get geno record: %d" % genoid)
    else:
        sql = """
            INSERT INTO Geno
            SET
            Geno.`SpeciesId`=%s,
            Geno.`Name`=%s,
            Geno.`Marker_Name`=%s,
            Geno.`Chr`=%s,
            Geno.`Mb`=%s
            """
        cursor.execute(sql, (config_dic['speciesid'], marker_dic['locus'], marker_dic['locus'], marker_dic['chromosome'], marker_dic['mb']))
        rowcount = cursor.rowcount
        genoid = con.insert_id()
        print("INSERT INTO Geno: %d record: %d" % (rowcount, genoid))
    return genoid

def get_probesetxref(probesetfreezeid):
    cursor = utilities.get_cursor()
    sql = """
        SELECT ProbeSetXRef.`ProbeSetId`, ProbeSetXRef.`DataId`
        FROM ProbeSetXRef
        WHERE ProbeSetXRef.`ProbeSetFreezeId`=%s
        """
    cursor.execute(sql, (probesetfreezeid))
    return cursor.fetchall()

def get_probeset(probesetid):
    cursor = utilities.get_cursor()
    sql = """
        SELECT ProbeSet.`Id`, ProbeSet.`Name`, ProbeSet.`Symbol`, ProbeSet.`description`, ProbeSet.`Probe_Target_Description`, ProbeSet.`Chr`, ProbeSet.`Mb`
        FROM ProbeSet
        WHERE ProbeSet.`Id`=%s
        """
    cursor.execute(sql, (probesetid))
    return cursor.fetchone()

def getexceptions():
    cursor = get_cursor("maintain_sb")
    sql = """SELECT domain.name, nameserver.name
    FROM domain, nameserver
    WHERE nameserver.name NOT LIKE "%ns_.oregonstate.edu%"
    AND nameserver.domain = domain.id"""

    cursor.execute(sql)
    return cursor.fetchall()

def get_publication(publicationid):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT Publication.`Authors`, Publication.`Abstract`
        FROM Publication
        WHERE Publication.`Id`=%s
        """
    cursor.execute(sql, (publicationid))
    return cursor.fetchone()

def get_genofreeze_byinbredsetid(inbredsetid):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT GenoFreeze.`Id`, GenoFreeze.`Name`, GenoFreeze.`FullName`, GenoFreeze.`InbredSetId`
        FROM GenoFreeze
        WHERE GenoFreeze.`InbredSetId`=%s
        """
    cursor.execute(sql, (inbredsetid))
    return cursor.fetchone()

def get_probesetfreeze(probesetfreezeid):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT ProbeSetFreeze.`Id`, ProbeSetFreeze.`Name`, ProbeSetFreeze.`FullName`
        FROM ProbeSetFreeze
        WHERE ProbeSetFreeze.`Id`=%s
        """
    cursor.execute(sql, (probesetfreezeid))
    return cursor.fetchone()

def get_publishxrefs(inbredsetid):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT PublishXRef.`Id`, PublishXRef.`PhenotypeId`, PublishXRef.`PublicationId`, PublishXRef.`DataId`
        FROM PublishXRef
        WHERE PublishXRef.`InbredSetId`=%s
        """
    cursor.execute(sql, (inbredsetid))
    return cursor.fetchall()

def get_phenotype(phenotypeid):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT Phenotype.`Original_description`, Phenotype.`Pre_publication_description`, Phenotype.`Post_publication_description`
        FROM Phenotype
        WHERE Phenotype.`Id`=%s
        """
    cursor.execute(sql, (phenotypeid))
    return cursor.fetchone()

def get_species(inbredsetid):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT Species.`Id`, Species.`Name`, Species.`MenuName`, Species.`FullName`
        FROM InbredSet, Species
        WHERE InbredSet.`Id`=%s
        AND InbredSet.`SpeciesId`=Species.`Id`
        """
    cursor.execute(sql, (inbredsetid))
    return cursor.fetchone()

def get_probesetxref_probesetfreezeid(locus, probesetfreezeid):
    cursor = utilities.get_cursor()
    sql = """
        SELECT ProbeSetXRef.`ProbeSetId`
        FROM ProbeSetXRef
        WHERE ProbeSetXRef.`ProbeSetFreezeId`=%s
        AND ProbeSetXRef.`Locus` LIKE %s
        """
    cursor.execute(sql, (probesetfreezeid, locus))
    return cursor.fetchall()

def get_publishdata(publishdataid):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT Strain.`Id`, Strain.`Name`, PublishData.`value`
        FROM PublishData, Strain
        WHERE PublishData.`Id`=%s
        AND PublishData.`StrainId`=Strain.`Id`
        """
    cursor.execute(sql, (publishdataid))
    return cursor.fetchall()

def get_probesetfreezes(inbredsetid):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT ProbeSetFreeze.`Id`, ProbeSetFreeze.`Name`, ProbeSetFreeze.`FullName`
        FROM ProbeSetFreeze, ProbeFreeze
        WHERE ProbeSetFreeze.`ProbeFreezeId`=ProbeFreeze.`Id`
        AND ProbeFreeze.`InbredSetId`=%s
        """
    cursor.execute(sql, (inbredsetid))
    return cursor.fetchall()

def get_probesetxref(probesetfreezeid):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT ProbeSetXRef.`ProbeSetId`, ProbeSetXRef.`DataId`, ProbeSetXRef.`Locus`, ProbeSetXRef.`LRS`, ProbeSetXRef.`pValue`,
			ProbeSetXRef.`mean`, ProbeSetXRef.`se`
        FROM ProbeSetXRef
        WHERE ProbeSetXRef.`ProbeSetFreezeId`=%s
        """
    cursor.execute(sql, (probesetfreezeid))
    return cursor.fetchall()

def get_probesetdata(probesetdataid):
    cursor = utilities.get_cursor()
    sql = """
        SELECT Strain.`Id`, Strain.`Name`, ProbeSetData.`value`
        FROM ProbeSetData, Strain
        WHERE ProbeSetData.`Id`=%s
        AND ProbeSetData.`StrainId`=Strain.`Id`;
        """
    cursor.execute(sql, (probesetdataid))
    return cursor.fetchall()

def get_strainxref(inbredsetid, strainid):
    cursor, con = utilities.get_cursor()
    sql = """
        SELECT StrainXRef.`StrainId`
        FROM StrainXRef
        WHERE StrainXRef.`InbredSetId`=%s
        AND StrainXRef.`StrainId`=%s
        """
    cursor.execute(sql, (inbredsetid, strainid))
    return cursor.fetchone()