GreenO

from datetime import date
import config
import logging
import cx_Oracle
from DataElements import Group
from DataElements import Member
from DataElements import Membership
from threading import Thread
import time
 
logger = logging.getLogger("LMSfeed.Database")
 
def getCols(rset,upper):
        cols={}
        count=0
        logger.debug(rset)
        for (name,type,display_size,internal_size,precision,scale,null_ok) in rset.description:
                if upper:
                        cols[name.upper()]=count
                else:
                        cols[name.lower()]=count
                count+=1
        return cols
 
class KeepAlive(Thread):
    """
        This keepalive thread should sit back and watch the DB connection and restart it if a DB error is encountered.
    """
    logger.debug("Starting Database KeepAlive Class")
    def __init__ (self,otherClass):
        """
            Init class. Pass in calling class so that this may call the conn object and the connection method.
 
        """
        Thread.__init__(self)
        self.otherClass = otherClass
 
    def run(self):
        """
 
        When executed it monitors the conn object in the object passed in by init. If a simple
        select count(*) from dual fails then restart the connection. If any error is encountered
        try to rebuild the connection via .connect
 
        """
        sql = "select 1 from dual"
        while True:
            logger.debug("Testing connection to " + self.otherClass.ezConStr)
            try:
                con = self.otherClass.pool.acquire()
                curs = con.cursor()
                rset = curs.execute(sql)
 
                rows = rset.fetchall()
 
                ## We just want to excersize the db connection. 
                ## If it did not fail in the fetchall then its not going to.
                ## http://www.oracle.com/technology/pub/articles/prez-python-queries.html
                curs.close()
 
                self.otherClass.pool.release(con)
                curs=None
                con=None
            except Exception, detail:
                logger.error("**** UNCAUGHT EXCEPTION ****")
                logger.error("Caught in EnterpriseDocument.py endElement name==group")
                logger.error(detail)  
                logger.debug("Connection to " + self.otherClass.ezConStr + " seems broken, trying to rebuild")
                self.otherClass.connect()
 
            time.sleep(config.dbPollingInterval)
 
 
class VistaDb:
    """
        class VistaDB
 
        This class handles access to the following Oracle tables
 
    """
    logger.debug("Starting Database VistaDB Class")
 
    def connect(self):
        """
 
        We will be using the connection pooling built into cx_Oracle
 
        """
        self.ezConStr = cx_Oracle.makedsn(config.dbVistaHost,config.dbVistaPort,config.dbVistaSid)
 
        self.pool = cx_Oracle.SessionPool(
                user=config.dbVistaUser,
                password=config.dbVistaPassword,
                dsn=self.ezConStr,
                min=0,
                max=20,
                increment=1,
                threaded=True)
        self.pool.timeout = 300
 
 
    def __init__(self,runKeepAlive=True):
                self.connect()
                if runKeepAlive:
                        keepAlive = KeepAlive(self)
                        keepAlive.start()
 
    ### GROUP LOGIC
 
    def getTypeLevelFromCode(self,code):
        """
 
        Translates a Vista English code into a IMS type level number.
 
        """
        if code == "Section":
            return 90
        if code == "Course":
            return 80
        if code == "Campus":
            return 30
        if code == "Division":
            return 25
        # Did not match any? 
        #TODO: Should I throw an error?
        return None
 
    def setAttrIfNotNull(self,group,attribute,value):
        """
 
        If the incoming value is not None and not some other value akin to None sent the attribute.
        If it is None, do nothing. Most of the time we don't set None's, we just don't set the attr.
        If a attr is set None somewhere else there is most likely an override.
 
        """
        if value != None:
                setattr(group,attribute,value)
 
    def getGroupBySourcedid(self,sourcedidid,sourcedidsource):
        """
 
        Return an EnterpriseDocument.Group object filled with info from Vista by passing sourcedid_id and sourcedid_source.
 
        """
        sql = """
                select * 
                from %s.learning_context 
                where 
                        source_id=:sourcedidid 
                        and source_name=:sourcedidsource 
                        and deleted_flag is null
        """%config.dbVistaSchema
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("source_id=" + sourcedidid)
        logger.debug("source_name=" + sourcedidsource)
        rset = curs.execute(sql,sourcedidid=sourcedidid,sourcedidsource=sourcedidsource)
        logger.debug("Got rset")
        group = self.processRsetToGroup(rset)
 
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
        return group
 
    def hasContent(self,sid,sname):
            """
            If course has content return True, else return false
            """
            ret = True
            sql = """
                select 
                        (select count(*) from %(schema)s.co_lc_assignment where learning_context_id = lc.id) +
                        (select count(*) from %(schema)s.co_lc_association where learning_context_id = lc.id) 
                from %(schema)s.learning_context lc 
                where 
                        source_id = :sourcedidId 
                        AND source_name = :sourcedidSource
 
                """%{'schema':config.dbVistaSchema}
 
            con = self.pool.acquire()
            curs = con.cursor()
 
            logger.debug("About to execute the following SQL")
            logger.debug(sql)
            logger.debug("sourcedidId=%s"%sid)
            logger.debug("sourcedidSource=%s"%sname)
            rset = curs.execute(sql,sourcedidId=sid,sourcedidSource=sname)
            row = curs.fetchone()
            if row:
                    contentItems = row[0]
                    if int(contentItems) == 0:
                            ret = False
            curs.close()
            self.pool.release(con)
            curs=None
            con=None
 
            return ret
 
    def getGroupById(self,lcid):
        """
 
        Return an EnterpriseDocument.Group objected filled with info from Vista by passing a Vista Learning Context ID
 
        """
        sql = """
                select * 
                from %s.learning_context 
                where 
                        id = :id
        """%config.dbVistaSchema
        if str(lcid) == "None":
            logger.debug("Not processing a Null")
            return None
 
        con = self.pool.acquire()
        curs = con.cursor()
 
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("id=" + str(lcid))
        rset = curs.execute(sql,id=lcid)
        group = self.processRsetToGroup(rset)
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None 
        return group  
 
    def getRollupParent(self,group):
        """
 
        Return the rollup parent as an EnterpriseDocument.Group object filled with info from Vista by passing in a child EnterpriseDocument.Group.
 
        """
        sql = """
                select master_lcid 
                from %s.XLIST_LC_MAPPING 
                where 
                        CHILD_SOURCE_ID=:child_source_id 
                        and CHILD_SOURCE_NAME=:child_source_name
        """%config.dbVistaSchema
 
        con = self.pool.acquire()
        curs = con.cursor()
 
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("childSourceId=" + group.sourcedidid)
        logger.debug("childSourceName=" + group.sourcedidsource)
        rset = curs.execute(sql,child_source_id=group.sourcedidid,child_source_name=group.sourcedidsource)
        masterLcid = None
        row = curs.fetchone()
        if row:
            group = self.getGroupById(row[0])
        else:
            group = None
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return group   
 
 
    def processRsetToGroup(self,rset):
        """
            processRsetToGroup(self,rset)
 
            Process a record set and return a new Group object
 
            We use a few SQL statments to get Group objects from VISTA_GROUP_INFO this code centralizes the the
            part that creates the group and populates it from the override DB
 
             This should be the ONLY place we get info from the override db to a group object because
             we make sure we handle overrides and make sure the object gets overrides applied. This func 
             acheves this by calling getGroupOverrides
        """
 
        group = None
 
        # Find where search col lives so we can map back
        cols = getCols(rset,True)
 
        row = rset.fetchone()
 
        logger.debug(row)
 
        if row:
            logger.debug("Got group")
            group = Group()
            self.setAttrIfNotNull(group,"sourcedidsource",row[cols["SOURCE_NAME"]])
            self.setAttrIfNotNull(group, "sourcedidid", row[cols["SOURCE_ID"]])
            if row[cols["DELIVERY_UNIT_TYPE"]] == "XLIST_MASTER":
                self.setAttrIfNotNull(group, "level", 85)
            else:   
                self.setAttrIfNotNull(group, "level", self.getTypeLevelFromCode(row[cols["TYPE_CODE"]]))
            self.setAttrIfNotNull(group, "descriptionshort", row[cols["NAME"]])
            self.setAttrIfNotNull(group, "descriptionlong", row[cols["DESCRIPTION"]].read())
            self.setAttrIfNotNull(group, "descriptionfull", row[cols["FULLDESCRIPTION"]].read())
            self.setAttrIfNotNull(group, "orgorgname", row[cols["ORGNAME"]])
            self.setAttrIfNotNull(group, "orgid", row[cols["ORGID"]])
 
            startDateEpoch = -1
            startDateEpoch = row[cols["STARTDATE_TIME"]]
 
            if startDateEpoch > 1:
                startDateEpoch = startDateEpoch/1000
                startDateTime = date.fromtimestamp(startDateEpoch)
                group.timeframebeginepoch = startDateEpoch
                group.timeframebegin = startDateTime.isoformat()
 
            self.setAttrIfNotNull(group, "timeframebeginrestrict", "1")
 
            endDateEpoch = -1
            endDateEpoch = row[cols["ENDDATE_TIME"]]
            if endDateEpoch > 1:
                endDateEpoch = endDateEpoch/1000
                endDateTime = date.fromtimestamp(endDateEpoch)
                group.timeframeendepoch = endDateEpoch
                group.timeframeend = endDateTime.isoformat()
 
            self.setAttrIfNotNull(group, "timeframeendrestrict", "1")
            self.setAttrIfNotNull(group, "timeframeadminperiod", row[cols["ADMINPERIOD"]])
            self.setAttrIfNotNull(group, "url", row[cols["URL"]])
            self.setAttrIfNotNull(group, "datasource", row[cols["DATASOURCE"]])
            self.setAttrIfNotNull(group, "vistadbid", row[cols["ID"]])
 
            # Get LC Parent
            lc_parent_id = row[cols["PARENT_ID"]]
            if lc_parent_id is not None and hasattr(group,"level"):
                logger.debug("lc_parent_id=%d and level=%s"%(lc_parent_id,group.level))
                lc_parent = self.getGroupById(lc_parent_id)
                group.lcparent=lc_parent
                group.lcparentid=lc_parent.sourcedidid
                group.lcparentsource=lc_parent.sourcedidsource
 
            rollupparent = self.getRollupParent(group)
            if rollupparent is not None:
                logger.debug("In Vista lcid = " + str(group.vistadbid) + "(" + group.sourcedidid + ":" + group.sourcedidsource + ") Rolls to lcid=" + str(rollupparent.vistadbid) + "(" + rollupparent.sourcedidid + ":" + rollupparent.sourcedidsource + ")")
                group.rollupparent=rollupparent
                group.rollupid=rollupparent.sourcedidid
                group.rollupsource=rollupparent.sourcedidsource
 
            return group
 
        else:
            logger.debug("Group not found")    
            return None
 
    ### ENROLLMENT LOGIC
 
    def getRoleSubrole(self, vistaRole):
        """
 
        Maps the string from the Vista DB into a EnterpriseDocument.Member.role which is an ordered list of role and subrole.
 
        """
        logger.debug("Starting getRoleSubrole with vistaRole =" + vistaRole)
        if vistaRole == "SINS":
            logger.debug("Returning 2,None")
            return (2,None)
        elif vistaRole == "SSTU":
            logger.debug("Returning 1,None")
            return (1,None)
        elif vistaRole == "SDES":
            logger.debug("Returning 3,None")
            return (3,None)
        elif vistaRole == "SAUD":
            logger.debug("Returning 1,AUD")
            return (1,"AUD")
        elif vistaRole == "STEA":
            logger.debug("Returning 2,TA")
            return (2,"TA")
 
        logger.error("Could not find match for vistaRole == " + vistaRole)
        return (None, None)
 
 
    def getMembershipBySourcedid(self,sourcedidid,sourcedidsource):
        """
 
        Gets enrollment for a section specified by sourcedidid, sourcedidsource from vista and maps that to
        a new EnterpriseDocument.Membership object.
 
        """
        sql = """
            select 
              p.remote_userid, p.sourcedid_source, p.webct_id_lowercase,
              rd.name, r.role_status, r.delete_status
            from %(schema)s.role r, %(schema)s.member m, %(schema)s.person p, %(schema)s.learning_context l, %(schema)s.role_definition rd
            where
              (r.member_id = m.id and m.person_id = p.id and m.learning_context_id = l.id and r.role_definition_id = rd.id)
            and p.demouser = 0
            and l.source_id = :source_id
            and l.source_name = :source_name
            """%{'schema':config.dbVistaSchema} ## This uses a different mapping schema
                                                ## so it can mark all the schema's with only one passed
 
        con = self.pool.acquire()
        curs = con.cursor()
 
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("source_id=" + sourcedidid)
        logger.debug("source_name=" + sourcedidsource)
        rset=curs.execute(sql,source_id=sourcedidid,source_name=sourcedidsource)
 
        cols = getCols(rset,False)
 
        membership = Membership()
        membership.sourcedidid = sourcedidid
        membership.sourcedidsource = sourcedidsource
        rows = rset.fetchall()
 
        for row in rows:
            member = Member()
            self.setAttrIfNotNull(member,"sourcedidid",row[cols["remote_userid"]])
            self.setAttrIfNotNull(member,"sourcedidsource",row[cols["sourcedid_source"]])
            self.setAttrIfNotNull(member,"userid",row[cols["webct_id_lowercase"]])
 
            (roletype, subrole) = self.getRoleSubrole(row[cols["name"]])
            self.setAttrIfNotNull(member,"roletype",roletype)
            self.setAttrIfNotNull(member,"subrole",subrole)
 
            if row[cols["delete_status"]] == 1:
                member.status = '0'
                member.restrict = '0'
            else:
                member.status = '1'
                if row[cols["role_status"]] == 1:
                    member.restrict='0'
                else:
                    member.restrict='1'
 
            membership.addMember(member)
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return membership  
 
class OverrideDb:
    """
        class OverrideDb
 
        This class handles access to the following Oracle tables:
            vista_ims_adm.vista_group_info            	Group objects are created
            vista_ims_adm.vista_enrollment_info       	Role objects are created
            vista_ims_adm.vista_member_info           	Used to find role records
            vista_ims_adm.vista_override_info         	Override records are applied to both Group and Role records    
	    vista_ims_adm.vista_group_destination_info  Where do these records feed to?  
 
    """
    logger.debug("Starting Database ODB Class")
 
    ## Overridable fields and their DB fields
    groupOverride = {
                  "LC_PARENT_ID":"lcparentdbid",
                  "ROLLUP_ID":"rollupdbid",
                  "TYPE_LEVEL":"level",
                  "SHORT_DESCRIPTION":"descriptionshort",
                  "LONG_DESCRIPTION":"descriptionlong",
                  "FULL_DESCRIPTION":"descriptionfull",
                  "ORG_ID":"orgid",
                  "ORG_NAME":"orgorgname",
                  "BKHIERARCHY":"extensionbkhierarchy",
                  "DIVISION":"extensiondivision",
                  "BEGIN_DATE":"timeframebegin",
                  "BEGIN_RESTRICT":"dbbeginrestrict",
                  "END_DATE":"timeframeend",
                  "END_RESTRICT":"dbbeginrestrict",
                  "ADMIN_PERIOD":"timeframeadminperiod",
                  "EMAIL":"email",
                  "URL":"url",
                  "WEBCT_TEMPLATE":"extensionwebcttemplate",
                  "CREATOR":"creator",
                  "EXPIRE_TS":"expirets",
                  "VISTA_DATA_SOURCE_ID":"datasource",
                  }
 
    roleOverride = {
                  "ROLE":"roletype",
                  "SUBROLE":"subrole",
                  "STATUS":"status",
                  "COMMENTS":"comments",
                  "RESTRICT":"restrict",
                  "EXPIRE_TS":"roleexpirets"
                  }
 
    memberOverride = {
                      "USERID":"userid",
                      "EXPIRE_TS":"memberexpirets"
                      }
 
    def connect(self):
        """
 
        We will be using the connection pooling built into cx_Oracle
 
        """
        self.ezConStr = cx_Oracle.makedsn(config.dbOverrideHost,config.dbOverridePort,config.dbOverrideSid)
        self.pool = cx_Oracle.SessionPool(
                user=config.dbOverrideUser,
                password=config.dbOverridePassword,
                dsn=self.ezConStr,
                min=0,
                max=20,
                increment=1,
                threaded=True)
        self.pool.timeout=300
 
    def __init__(self,runKeepAlive=True):
            self.connect()
            if runKeepAlive:
                    keepAlive = KeepAlive(self)
                    keepAlive.start()
            self.removeNonOverridable()
    def getAllEnrollmentsOfAllPeers(self,sourcedidId,sourcedidSource):
        sql = """
            select m.sourcedid_id,m.sourcedid_source,e.role,e.subrole from 
                %(schema)s.override_vista_member_info m
                inner join %(schema)s.override_vista_enrollment_info e on m.vista_member_id=e.vista_member_id
                  inner join %(schema)s.override_rollup_group_info g on e.vista_group_id = g.vista_group_id
                    where g.rollup_id in (
                        select rollup_id from  %(schema)s.override_rollup_group_info gg
                          where gg.sourcedid_id='%(sourcedidId)s' and gg.sourcedid_source='%(sourcedidSource)s'
                      )
                  and e.status='1'
        """%{'schema':config.dbOverrideSchema,'sourcedidId':sourcedidId,'sourcedidSource':sourcedidSource}
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
 
        rset = curs.execute(str(sql))
 
        rows = rset.fetchall()
        ret = []
        for row in rows:
            ret.append(str(row[0])+'.'+str(row[1])+'.'+str(row[2])+'.'+str(row[3])) 
 
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
        return ret 
 
    def removeNonOverridable(self):
        sql = """
                select table_name, field
                        from %s.lms_or_fields_info
                  """%config.dbOverrideSchema
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
 
        rset = curs.execute(sql)
 
        # Find where search col lives so we can map back
        cols = getCols(rset,True)
 
        rows = rset.fetchall()
        validOverrides = []
        for row in rows:
            validOverrides.append((row[cols["TABLE_NAME"]],row[cols["FIELD"]])) 
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        logger.debug("validOverrides="+str(validOverrides))
 
        for groupO in self.groupOverride.keys():
                if ("VISTA_GROUP_INFO",groupO) not in validOverrides:
                        del self.groupOverride[groupO]
        for memberO in self.memberOverride.keys():
                if ("VISTA_MEMBER_INFO",memberO) not in validOverrides:
                        del self.memberOverride[memberO]
        for roleO in self.roleOverride.keys():
                if("VISTA_ENROLLMENT_INFO",roleO) not in validOverrides:
                        del self.roleOverride[roleO]
 
        logger.debug("groupOverrideAfterRemoval="+str(self.groupOverride))
        logger.debug("memberOverrideAfterRemoval="+str(self.memberOverride))
        logger.debug("roleOverrideAfterRemoval="+str(self.roleOverride))
 
 
    ### HELPER FUNCTION
    def getListGroups(self,sql):
        """
            Helper function to execute SQL and get a list of groups back, A list of tuples are
            returned of sourcedid_id,sourcedid_source
        """
        ret = []
        logger.debug("started getListGroups with sql=" + sql)
        con = self.pool.acquire()
        curs = con.cursor()
        rset = curs.execute(sql)
        cols = getCols(rset,False) 
 
        rows = rset.fetchall()
 
        for row in rows:
            sourceid = row[cols["sourcedid_id"]]
            sourcename = row[cols["sourcedid_source"]]
            ret.append((sourceid,sourcename))
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return ret
 
    def setAttrIfNotNull(self,group,attribute,value):
        logger.debug("Trying to set " + str(attribute) + " to " + str(value))
        logger.debug("Will only set if this is null == " + str(getattr(group,attribute,None)))
        if value != None:
            if str(value) != "Null":
                logger.debug("Yup... Its null... Setting Attribute")
                setattr(group,attribute,value)
 
    ### GROUP LOGIC    
    def insertGroup(self,group):
        logger.debug("started insertGroup with sourcedidid=" + group.sourcedidid + " sourcedidsource=" + group.sourcedidsource)
        con = self.pool.acquire()
        curs = con.cursor()
        if hasattr(group,"lcparentid"):
            lcGroup = self.getGroup(group.get("lcparentid"),group.get("lcparentsource"))
            logger.debug("Assigning LC Parent = " + group.get("lcparentid") + ":" + group.get("lcparentsource"))
            group.lcdbid = lcGroup.dbid
 
        if group.get("rollupid"):
            rollupGroup = self.getGroup(group.get("rollupid"), group.get("rollupsource"))
            logger.debug("Assigning Rollup = " + group.get("rollupid") + ":" + group.get("rollupsource"))
            group.rollupdbid=rollupGroup.dbid
 
        sql = """
            insert into %s.VISTA_GROUP_INFO (
                SOURCEDID_ID, 
                SOURCEDID_SOURCE,
                LC_PARENT_ID,
                ROLLUP_ID,
                TYPE_LEVEL,
                SHORT_DESCRIPTION,
                LONG_DESCRIPTION,
                FULL_DESCRIPTION,
                ORG_ID,
                ORG_NAME,
                BKHIERARCHY,
                DIVISION,
                BEGIN_DATE,
                BEGIN_RESTRICT,
                END_DATE,
                END_RESTRICT,
                ADMIN_PERIOD,
                EMAIL,
                URL,
                WEBCT_TEMPLATE,
                CREATOR,
                EXPIRE_TS,
                VISTA_DATA_SOURCE_ID
            ) values (
            :sourcedid_id, 
            :sourcedid_source,
            :lc_parent_id,
            :rollup_id,
            :type_level,
            :short_description,
            :long_description,
            :full_description,
            :org_id,
            :org_name,
            :bkhierarchy,
            :division,
            :begin_date,
            :begin_restrict,
            :end_date,
            :end_restrict,
            :admin_period,
            :email,
            :url,
            :webct_template,
            :creator,
            :expire_ts,
            :vista_data_source_id
            )
        """%config.dbOverrideSchema
        logger.debug("Going to run sql=" + sql)
        param = {} 
        param["sourcedid_id"]=group.sourcedidid
        param["sourcedid_source"]=group.sourcedidsource
 
        param["lc_parent_id"]=group.get("lcdbid")
 
        param["rollup_id"]=group.get("rollupdbid")
 
        param["type_level"]= group.get("level")
        param["short_description"]= group.get("descriptionshort")
        param["long_description"]= group.get("descriptionlong")
        param["full_description"]= group.get("descriptionfull")
 
        param["org_id"]= group.get("orgid")
 
        param["org_name"]= group.get("orgorgname")
 
        param["bkhierarchy"]= group.get("extensionbkhierarchy")
 
        param["division"]= group.get("extensiondivision")
 
        param["begin_date"]= group.get("timeframebegin")
 
        param["begin_restrict"]= group.get("timeframebeginrestrict")
 
        param["end_date"]= group.get("timeframeend")
 
        param["end_restrict"]= group.get("timeframeendrestrict")
 
        param["admin_period"]= group.get("timeframeadminperiod")
 
        param["email"]= group.get("email")
 
        param["url"]= group.get("url")
 
        param["webct_template"]= group.get("extensionwebcttemplate")
 
        param["creator"]= config.feedName
 
        param["expire_ts"]= group.get("expirets")
 
        param["vista_data_source_id"]= group.get("datasource")
 
        for (key,value) in param.iteritems():
                logger.debug("%s=%s"%(key,value))
 
        curs.execute(sql,param)
        curs.close()
 
        con.commit()
        self.pool.release(con)
        curs=None
        con=None
 
 
    def updateGroup(self,group):
        if str(group) == "None":
            logger.debug("Not processing a None groupe ")
            return None
 
        # The Divisions and Content Seciotns don't have a LCParent
        if group.get("level")=="25" or group.get("level")=="85":
            lcGroup = None
        else:
            lcGroup = self.getGroup(group.get("lcparentid"),group.get("lcparentsource"))
 
        if group.get("rollupdbid") is not None:
            rollupGroup = self.getGroupFromId(group.get("rollupdbid"))
        elif group.get("rollupid") is not None and group.get("rollupsource") is not None:
            rollupGroup = self.getGroup(group.get("rollupid"), group.get("rollupsource"))
        else:
            rollupGroup = None
 
        sql = """
                update %s.VISTA_GROUP_INFO set
                LC_PARENT_ID=:lc_parent_id,
                ROLLUP_ID=:rollup_id,
                TYPE_LEVEL=:type_leve,
                SHORT_DESCRIPTION=:short_description,
                LONG_DESCRIPTION=:long_description,
                FULL_DESCRIPTIOn=:full_description,
                ORG_ID=:org_id,
                ORG_NAME=:org_name,
                BKHIERARCHY=:bkhierarchy,
                DIVISION=:division,
                BEGIN_DATE=:begin_date,
                BEGIN_RESTRICT=:begin_restrict,
                END_DATE=:end_date,
                END_RESTRICT=:end_restrict,
                ADMIN_PERIOD=:admin_period,
                EMAIL=:email,
                URL=:url,
                WEBCT_TEMPLATE=:webct_template,
                EXPIRE_TS=:expire_ts
 
                    where SOURCEDID_ID=:sourcedid_id and SOURCEDID_SOURCE=:sourcedid_source
                    """%config.dbOverrideSchema
        logger.debug("About to execute the following sql")
        logger.debug(sql)
 
        params = {}
 
        if lcGroup is not None:
            params["lc_parent_id"]= lcGroup.dbid
        else:
            params["lc_parent_id"]= None
 
        if rollupGroup is not None:
            params["rollup_id"]= rollupGroup.dbid
        else:
            params["rollup_id"]= None
 
 
        params["type_leve"]= group.get("level")
        params["short_description"]= group.get("descriptionshort")
        params["long_description"]= group.get("descriptionlong")
        params["full_description"]= group.get("descriptionfull")
 
        params["org_id"]= group.get("orgid")
 
        params["org_name"]= group.get("orgorgname")
 
        params["bkhierarchy"]= group.get("extensionbkhierarchy")
 
        params["division"]= group.get("extensiondivision")
 
        params["begin_date"]= group.get("timeframebegin")
 
        params["begin_restrict"]= group.get("timeframebeginrestrict")
 
        params["end_date"]= group.get("timeframeend")
 
        params["end_restrict"]= group.get("timeframeendrestrict")
 
        params["admin_period"]= group.get("timeframeadminperiod")
 
        params["email"]= group.get("email")
 
        params["url"]= group.get("url")
 
        params["webct_template"]= group.get("extensionwebcttemplate")
 
        params["expire_ts"]= group.get("expirets")
 
        params["sourcedid_id"]= group.sourcedidid
        params["sourcedid_source"]= group.sourcedidsource
 
        for (key,value) in params.iteritems():
                logger.debug("%s=%s"%(key,value))
 
        con = self.pool.acquire()
        curs = con.cursor()
        curs.execute(sql,params)
        curs.close()
 
        con.commit()
        self.pool.release(con)
        curs=None
        con=None
 
 
 
    def getGroupOverrides(self,group):
        """
            getGroupOverrides(self,group)
 
            Get override records from VISTA_OVERRIDE_INFO and save the values to a group object.
 
            The original value will be saved as orig_fieldname. This will allow access to the underlying 
            value and allow the func that send the object to the override DB to ignore the update. If you
            ever want to test for the existence of an override record see if there is an orig_field.
 
            Group object after this func:
                For every override record:
                group.field = value from vista_override_info
                group.orig_field = value from vista_group_info
        """
        logger.debug("Starting getGroupOverrides for " + group.sourcedidid)
 
        sql = """
                select VALUE,FIELD 
                from %s.vista_override_info 
                where 
                        table_name='VISTA_GROUP_INFO' 
                        and table_id=:table_id
        """%config.dbOverrideSchema
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("table_id=" + group.dbid)
        rset = curs.execute(sql,table_id=group.dbid)
 
        # Find where search col lives so we can map back
        cols = getCols(rset,True)
 
        rows = rset.fetchall()
 
        for row in rows:
            objectFieldName = self.groupOverride[row[cols["FIELD"]]]
            overrideValue = row[cols["VALUE"]]
            # If there is no object set at this instance then save orig_field ="" to signify a null is underlying
            origValue = getattr(group,objectFieldName,"")
            logger.debug("Setting " +  str(objectFieldName) + "=" + str(overrideValue))
            # Set the override values in the object
            setattr(group,"orig_" + objectFieldName, origValue)
            setattr(group,objectFieldName,overrideValue)
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
 
        # Get some details on overriden rollup and lc parents
        if hasattr(group,"orig_lcparentdbid"):
            if group.lcparentdbid != None:
                orig_parent = self.getGroupFromId(group.orig_lcparentdbid)
                logger.debug("Setting orig_lcparent,orig_lcparentid,orig_lcparentsource from " +group.orig_lcparentdbid)
                group.orig_lcparent=orig_parent
                if orig_parent == None:
                    group.orig_lcparentid=None
                    group.orig_lcparentsource=None
                else:
                    group.orig_lcparentid=orig_parent.sourcedidid
                    group.orig_lcparentsource=orig_parent.sourcedidsource
            else:
                logger.debug("Setting orig_lcparent,orig_lcparentid,orig_lcparentsource to None")
                group.orig_lcparent=None
                group.orig_lcparentid=None
                group.orig_lcparentsource=None
 
            parent = self.getGroupFromId(group.lcparentdbid)
            logger.debug("Setting lcparent,lcparentid,lcparentsource from " +group.lcparentdbid)
            group.lcparent=parent
            group.lcparentid=parent.sourcedidid
            group.lcparentsource=parent.sourcedidsource
 
        if hasattr(group,"orig_rollupdbid"):
            if group.orig_rollupdbid != None:
                orig_rollup = self.getGroupFromId(group.orig_rollupdbid)
                logger.debug("Setting orig_rollupparent,orig_rollupid,orig_rollupsource from " +group.orig_rollupdbid)
                group.orig_rollupparent=orig_rollup
                if orig_rollup == None:
                    group.orig_rollupid=None
                    group.orig_rollupsource=None
                else:
                    group.orig_rollupid=orig_rollup.sourcedidid
                    group.orig_rollupsource=orig_rollup.sourcedidsource
            else:
                logger.debug("Setting orig_rollupparent,orig_rollupid,orig_rollupsource to None")
                group.orig_rollupparent=None
                group.orig_rollupid=None
                group.orig_rollupsource=None
 
            if getattr(group,"rollupdbid",None) != None and getattr(group,"rollupdbid",None)!="": 
                rollup = self.getGroupFromId(group.rollupdbid)
                logger.debug("Setting rollupparent,rollupid,rollupsource from " +str(group.rollupdbid))
                group.rollupparent=rollup
                group.rollupid=rollup.sourcedidid
                group.rollupsource=rollup.sourcedidsource
            else:
                group.rollupparent=None
                group.rollupid=None
                group.rollupsource=None
 
    def processRsetToGroup(self,rset):
        """
            processRsetToGroup(self,rset)
 
            Process a record set and return a new Group object
 
            We use a few SQL statments to get Group objects from VISTA_GROUP_INFO this code centralizes the the
            part that creates the group and populates it from the override DB
 
             This should be the ONLY place we get info from the override db to a group object because
             we make sure we handle overrides and make sure the object gets overrides applied. This func 
             acheves this by calling getGroupOverrides
        """
 
        group = None
 
        # Find where search col lives so we can map back
        cols = getCols(rset,True)
 
        row = rset.fetchone()
 
        logger.debug(row)
 
        if row:
            logger.debug("Got group")
            group = Group()
 
            self.setAttrIfNotNull(group,"sourcedidsource",row[cols["SOURCEDID_SOURCE"]])
            self.setAttrIfNotNull(group, "sourcedidid", row[cols["SOURCEDID_ID"]])
            self.setAttrIfNotNull(group, "level", row[cols["TYPE_LEVEL"]])
            self.setAttrIfNotNull(group, "descriptionshort", row[cols["SHORT_DESCRIPTION"]])
            self.setAttrIfNotNull(group, "descriptionlong", row[cols["LONG_DESCRIPTION"]])
            self.setAttrIfNotNull(group, "descriptionfull", row[cols["FULL_DESCRIPTION"]])
            self.setAttrIfNotNull(group, "orgorgname", row[cols["ORG_NAME"]])
            self.setAttrIfNotNull(group, "orgid", row[cols["ORG_ID"]])
            self.setAttrIfNotNull(group, "timeframebegin", row[cols["BEGIN_DATE"]])
            self.setAttrIfNotNull(group, "timeframebeginrestrict", row[cols["BEGIN_RESTRICT"]])
            self.setAttrIfNotNull(group, "timeframeend", row[cols["END_DATE"]])
            self.setAttrIfNotNull(group, "timeframeendrestrict", row[cols["END_RESTRICT"]])
            self.setAttrIfNotNull(group, "timeframeadminperiod", row[cols["ADMIN_PERIOD"]])
            self.setAttrIfNotNull(group, "url", row[cols["URL"]])
            self.setAttrIfNotNull(group, "datasource", row[cols["VISTA_DATA_SOURCE_ID"]])
            self.setAttrIfNotNull(group, "extensionbkhierarchy", row[cols["BKHIERARCHY"]])
            self.setAttrIfNotNull(group, "extensiondivision", row[cols["DIVISION"]])
            self.setAttrIfNotNull(group, "dbid", row[cols["VISTA_GROUP_ID"]])
            self.setAttrIfNotNull(group, "lcparentdbid", row[cols["LC_PARENT_ID"]])
            self.setAttrIfNotNull(group, "rollupdbid", row[cols["ROLLUP_ID"]])
            self.setAttrIfNotNull(group, "creator", row[cols["CREATOR"]])
            self.setAttrIfNotNull(group, "expirets", row[cols["EXPIRE_TS"]])
            self.setAttrIfNotNull(group, "createts", row[cols["CREATE_TS"]])
            self.setAttrIfNotNull(group, "modifyts", row[cols["MODIFY_TS"]])
 
            # Save override data to the dataobject
            self.getGroupOverrides(group)
 
            # Get some details about Rollup and LCParents
            if hasattr(group,"lcparentdbid"):
                logger.debug("Trying to set lcparent for vista_Group_id=" + str(group.lcparentdbid))
                lcparent = self.getGroupFromId(group.lcparentdbid)
                group.lcparent=lcparent
                group.lcparentid=lcparent.sourcedidid
                group.lcparentsource=lcparent.sourcedidsource
 
            if getattr(group,"rollupdbid",None)!=None and getattr(group,"rollupdbid",None)!="":
                logger.debug("Trying to set rollup for vista_Group_id=" + str(group.rollupdbid))
                rollupparent = self.getGroupFromId(group.rollupdbid)
                group.rollupparent=rollupparent
                group.rollupid=rollupparent.sourcedidid
                group.rollupsource=rollupparent.sourcedidsource
 
            return group
 
        else:
            logger.debug("Group not found")    
            return None
 
 
    def getGroupDestinations(self,sourcedidid,sourcedidsource):
        """
		Returns a list of destinations for this group.
		This will return a tuple of groupid,primary,active
		"""
        if sourcedidid==None or sourcedidsource==None:
            logger.debug("Not processing a Null")
            return None
 
        sql = """
    		select g.LMS_DESTINATION_ID, g.PRIMARY, g.ACTIVE, d.URL 
    		from %(schema)s.LMS_GROUP_DESTINATION_INFO g, %(schema)s.LMS_DESTINATION_INFO d, %(schema)s.VISTA_GROUP_INFO gg
    		where
    		    (d.LMS_DESTINATION_ID=g.LMS_DESTINATION_ID and gg.VISTA_GROUP_ID=g.LMS_GROUP_ID)
    		    and
    			gg.sourcedid_id = :sourceid
    			and gg.sourcedid_source = :source
    	      """%{'schema':config.dbOverrideSchema}
        con=self.pool.acquire()
        curs=con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("sourcedid_id=%s"%sourcedidid)
        logger.debug("sourcedid_source=%s"%sourcedidsource)
        rset=curs.execute(sql,sourceid=sourcedidid, source=sourcedidsource)
        rows = rset.fetchall()
        retList=[]
        for row in rows: 
            retList.append((str(row[0]),str(row[1]),str(row[2]),str(row[3])))
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return retList
 
 
 
    def getGroupFromId(self,vistaGroupId):
        """
            getGroupFromId(self,vistaGroupId)
 
            Executes SQL to get a group from vista_group_info using the PK vista_group_id
 
        """
        if str(vistaGroupId) == "None":
            logger.debug("Not processing a Null")
            return None
 
        sql = """
                select * 
                from %s.vista_group_info 
                where 
                        vista_group_id=:vista_group_id
        """%config.dbOverrideSchema
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("vistaGroupId=" + vistaGroupId)
        rset=curs.execute(sql,vista_group_id=vistaGroupId)
 
        group = self.processRsetToGroup(rset)
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return group                        
 
    def getGroup(self, sourcedidid, sourcedidsource):
        """
            getGroup(self, sourcedidid, sourcedidsource)
 
            getGroup executes SQL to get a group using sourcedid_id and sourcedid_source from vista_group_info
 
        """
        logger.debug("Searching for sorucedidid=" + str(sourcedidid) + " sourcedidsource=" + str(sourcedidsource))
        if str(sourcedidid) == "None" or str(sourcedidsource) == "None":
            logger.debug("Not processing a None sourcedid or sourcedidsource ")
            return None
 
        sql = """
                select * 
                from %s.vista_group_info 
                where 
                        sourcedid_id=:sourcedid_id 
                        and sourcedid_source=:sourcedid_source
        """%config.dbOverrideSchema
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("sourcedidid=" + sourcedidid)
        logger.debug("sourcedidsource=" + sourcedidsource)
        rset = curs.execute(sql,sourcedid_id=sourcedidid,sourcedid_source=sourcedidsource) 
        group = self.processRsetToGroup(rset)
 
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return group
 
    def getGroupDbId(self,sourcedidid,sourcedidsource):
        """
            getGroupDbId(self,sourcedidid,sourcedidsource)
 
            Gets vista_group_id from vista_group_info.sourcedid_id and vista_group_info.sourcedid_source
 
            You can't or at least should never (And this codes does not care about) override sourcedid* values 
            so not checking for overrides is just fine!
        """
 
        logger.debug("Searching for group with sorucedidid=" + str(sourcedidid) + " sourcedidsource=" + str(sourcedidsource))
        if str(sourcedidid) == "None" or str(sourcedidsource) == "None":
            logger.debug("Not processing a None sourcedid or sourcedidsource ")
            return None
 
        sql = """
                select vista_group_id 
                from %s.vista_group_info 
                where 
                        sourcedid_id=:sourcedid_id 
                        and sourcedid_source=:sourcedid_source
        """%config.dbOverrideSchema
 
        con = self.pool.acquire()
        curs = con.cursor()
 
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("sourcedidid=" + sourcedidid)
        logger.debug("sourcedidsource=" + sourcedidsource)
        rset = curs.execute(sql,sourcedid_id=sourcedidid,sourcedid_source=sourcedidsource)
 
        row = rset.fetchone()
        if row:
            groupDbId = row[0]
        else:
            groupDbId=None
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return groupDbId
 
    def getSourcedidid(self,termCode,classNumber,sourcedidsource):
        """
            getSourcedidid(self,termCode,classNumber)
 
            Gets sourcedid_id from vista_group_info using the term code and the class number
 
            This returns type level 90 only, no content sections
        """
 
        logger.debug("Searching for group with term code=" + str(termCode) + " class number=" + str(classNumber))
        if str(termCode) == "None" or str(classNumber) == "None":
            logger.debug("Not processing a None termCode or classNumber ")
            return None
 
        sql = """
                select sourcedid_id 
                from %s.vista_group_info 
                where 
                        sourcedid_id like :sourcedid_id 
                        and sourcedid_source = :sourcedid_source 
                        and TYPE_LEVEL = 90
        """%config.dbOverrideSchema
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("termCode=" + termCode)
        logger.debug("classNumber=" + classNumber)
        rset = curs.execute(sql,sourcedid_id="%s-%-%s"%(termCode,classNumber),sourcedid_source=sourcedidsource)
 
        row = rset.fetchone()
        if row:
            groupDbId = row[0]
        else:
            groupDbId=None
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return groupDbId
 
    def isGroupDeleted(self,sourcedidid,sourcedidsource):
        sql = """
            select count(*) 
            from %s.lms_group_event_log_info 
            where
                sourcedid_id = :sourcedid_id
                and sourcedid_source = :sourcedid_source
                and lms_group_event_id = 'DELETE-VISTA-SECTION'
        """%config.dbOverrideSchema
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("sourcedidid=" + sourcedidid)
        logger.debug("sourcedidsource=" + sourcedidsource)
        rset = curs.execute(sql,sourcedid_id=sourcedidid,sourcedid_source=sourcedidsource)
        ret = False
        row = curs.fetchone()
        logger.debug(row)
        if row:
            if row[0] > 0:
                ret = True
 
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return ret
 
    ### ENROLLMENT LOGIC
    def getPersonIds(self,emplidOrRegid):
        emplid = None
        regid = None
 
        if len(emplidOrRegid) < 10 :
            emplid = str(int(emplidOrRegid))
        else:
            regid = emplidOrRegid
 
        if emplid != None:
            return self.getPersonIdsByEmplid(emplid)
 
        return self.getPersonIdsByRegid(regid)
 
    def getPersonIdsByRegid(self,regid):
        sql = """
                select userid, emplid, sourcedid_id , sourcedid_source 
                from %s.vista_member_info m 
                where 
                        m.sourcedid_id=:sourcedid_id
        """%config.dbOverrideSchema
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("regid=" + regid)
        rset = curs.execute(sql,sourcedid_id=regid)
 
        ret = None
        row = rset.fetchone()
        if row:
            emplid = row[1]
            uid = row[0]
            sourceid = row[2]
            sourcename = row[3]
            ret = (emplid,uid,sourceid,sourcename)
            logger.debug(ret)
 
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return ret
 
    def getPersonIdsByEmplid(self,emplid):
        sql = """
                select userid, emplid, sourcedid_id , sourcedid_source 
                from %s.vista_member_info m
                where
                        m.emplid=:eid
        """%config.dbOverrideSchema
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("emplid=" + emplid)
        rset = curs.execute(sql,eid=emplid)
        ret = None
        row = rset.fetchone()
        if row:
            emplid = row[1]
            uid = row[0]
            sourceid = row[2]
            sourcename = row[3]
            ret = (emplid,uid,sourceid,sourcename)
            logger.debug(ret)
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return ret
 
    def getMemberDbId(self,sourcedidid,sourcedidsource):
        sql = """
                select vista_member_id 
                from %s.vista_member_info
                where
                        sourcedid_id = :sourcedid_id
                        and sourcedid_source = :sourcedid_source
        """%config.dbOverrideSchema
 
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("sourcedid_id=" + sourcedidid)
        logger.debug("sourcedid_name=" + sourcedidsource)
        rset=curs.execute(sql,sourcedid_id=sourcedidid,sourcedid_source=sourcedidsource)
 
        row = rset.fetchone()
 
        ret = None
        if row:
            ret = row[0]
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return ret
 
    def getRoleOverrides(self,member):
        """
            getRoleOverrides(self,membership)
 
            Get override records from VISTA_OVERRIDE_INFO and save the values to a membership object.
 
            The original value will be saved as orig_fieldname. This will allow access to the underlying 
            value and allow the func that send the object to the override DB to ignore the update. If you
            ever want to test for the existence of an override record see if there is an orig_field.
 
            Member object after this func:
                For every override record:
                member.field = value from vista_override_info
                member.orig_field = value from vista_enrollment_info
        """
        logger.debug("Starting getRoleOverrides for " + member.sourcedidid)
 
        sql = """
                select field,value 
                from %s.vista_override_info 
                where 
                        table_name='VISTA_ENROLLMENT_INFO' 
                        and table_id=:table_id
        """%config.dbOverrideSchema
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("table_id=" + member.dbroleid)
        rset = curs.execute(sql,table_id=member.dbroleid)
        rows = rset.fetchall()
        for row in rows:
            objectFieldName = self.roleOverride[row[0]]
            overrideValue = row[1]
            # If there is no object set at this instance then save orig_field ="" to signify a null is underlying
            origValue = getattr(member,objectFieldName,"")
            logger.debug("Setting role override " +  str(objectFieldName) + "=" + str(overrideValue))
            # Set the override values in the object
            setattr(member,"orig_" + objectFieldName, origValue)
            setattr(member,objectFieldName,overrideValue)
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
 
    def getMemberOverrides(self,member):
        """
            getMemberOverrides(self,membership)
 
            Get override records from VISTA_OVERRIDE_INFO and save the values to a membership object.
 
            The original value will be saved as orig_fieldname. This will allow access to the underlying 
            value and allow the func that send the object to the override DB to ignore the update. If you
            ever want to test for the existence of an override record see if there is an orig_field.
 
            Member object after this func:
                For every override record:
                member.field = value from vista_override_info
                member.orig_field = value from vista_enrollment_info
        """
        logger.debug("Starting getMemberOverrides for " + member.sourcedidid)
 
        sql = """
                select field,value 
                from %s.vista_override_info 
                where
                        table_name='VISTA_MEMBER_INFO'
                        and table_id=:table_id
        """%config.dbOverrideSchema
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("table_id=" + member.dbmemberid)
        rset = curs.execute(sql,table_id=member.dbmemberid)
        rows = rset.fetchall()
        for row in rows:
            objectFieldName = self.memberOverride[row[0]]
            overrideValue = row[1]
            # If there is no object set at this instance then save orig_field ="" to signify a null is underlying
            origValue = getattr(member,objectFieldName,"")
            logger.debug("Setting member override" +  str(objectFieldName) + "=" + str(overrideValue))
            # Set the override values in the object
            setattr(member,"orig_" + objectFieldName, origValue)
            setattr(member,objectFieldName,overrideValue)
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
 
    def getMembership(self, sourcedidid,sourcedidsource):
        logger.debug("Starting getMembership")
 
        sql = """select 
                    g.vista_group_id, e.vista_enrollment_id, m.vista_member_id, m.sourcedid_id, m.sourcedid_source, m.userid, 
                    m.expire_ts as memberexpirets, e.expire_ts as roleexpirets, e.vista_data_source_id roledatasource, m.vista_Data_source_id memberdatasource,
                    e.role, e.subrole, e.status, e.comments, e.restrict, e.vista_data_source_id roledatasource, m.emplid,
                    g.sourcedid_id groupsourcedidid, g.sourcedid_source groupsourcedidsource
            from %(s)s.vista_enrollment_info e, %(s)s.vista_group_info g, %(s)s.vista_member_info m 
            where
                (g.vista_group_id = e.vista_group_id and e.vista_member_id = m.vista_member_id)
                and g.sourcedid_id = :sourcedid_id
                and g.sourcedid_source = :sourcedid_source
           """%{'s':config.dbOverrideSchema}
        con = self.pool.acquire()
        curs = con.cursor()
 
        logger.debug("About to execute the following SQL")
 
        logger.debug(sql)
        logger.debug("sourcedid_id=" + sourcedidid)
        logger.debug("sourcedid_name=" + sourcedidsource)
        rset = curs.execute(sql,sourcedid_id=sourcedidid,sourcedid_source=sourcedidsource)
        logger.debug(rset)
        membership = Membership()
        membership.db = True
        membership.datasource = config.datasource
        membership.sourcedidid = sourcedidid
        membership.sourcedidsource = sourcedidsource
 
        logger.debug("About to start loop")
        rows = rset.fetchall()
        cols = getCols(rset,False)
        logger.debug(cols)
        for row in rows:
            logger.debug("In loop")
            member = Member()
            logger.debug("memberexpirets=>" + str(row[cols["memberexpirets"]]))
            logger.debug("roleexpirets=>" + str(row[cols["roleexpirets"]]))
 
            self.setAttrIfNotNull(member, "dbmemberid", row[cols["vista_member_id"]])
            self.setAttrIfNotNull(member, "sourcedidid", row[cols["sourcedid_id"]])
            self.setAttrIfNotNull(member, "sourcedidsource", row[cols["sourcedid_source"]])
            self.setAttrIfNotNull(member, "userid", row[cols["userid"]])
            self.setAttrIfNotNull(member, "memberexpirets", row[cols["memberexpirets"]])
            self.setAttrIfNotNull(member, "roleexpirets", row[cols["roleexpirets"]])
            self.setAttrIfNotNull(member, "roledatasource", row[cols["roledatasource"]])
            self.setAttrIfNotNull(member, "memberdatasource", row[cols["memberdatasource"]])
            self.setAttrIfNotNull(member, "datasource", row[cols["roledatasource"]])
            self.setAttrIfNotNull(member, "dbroleid", row[cols["vista_enrollment_id"]])
            self.setAttrIfNotNull(member, "dbgroupid", row[cols["vista_group_id"]])
            self.setAttrIfNotNull(member, "roletype", row[cols["role"]])
            self.setAttrIfNotNull(member, "subrole", row[cols["subrole"]])
            self.setAttrIfNotNull(member, "status", row[cols["status"]])
            self.setAttrIfNotNull(member, "comments", row[cols["comments"]])
            self.setAttrIfNotNull(member, "restrict", row[cols["restrict"]])
            self.setAttrIfNotNull(member, "emplid", row[cols["emplid"]])
            self.setAttrIfNotNull(member, "groupsourcedidid", row[cols["groupsourcedidid"]])
            self.setAttrIfNotNull(member, "groupsourcedidsource", row[cols["groupsourcedidsource"]])
            member.db=True
            self.getMemberOverrides(member)
            self.getRoleOverrides(member)
 
            logger.debug(str(member))
 
            membership.addMember(member)
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return membership
 
    def updateMember(self,member):
        if not hasattr(member,"dbgroupid"):
                member.dbgroupid = self.getGroupDbId(member.groupsourcedidid, member.groupsourcedidsource)
 
        if not hasattr(member,"dbmemberid"):
                member.dbmemberid = self.getMemberDbId(member.sourcedidid, member.sourcedidsource)
 
        logger.debug("Started updateMember with dbgroupid=" + member.dbgroupid + " dbmemberid=" + member.dbmemberid)
 
        sql = """
                update %s.vista_enrollment_info set 
                status = :status,
                comments = :comments,
                expire_ts = :expire_ts
                where
                        vista_member_id= :vista_member_id
                        and vista_group_id=:vista_group_id
                        and role = :role
                        and subrole = :subrole
        """%config.dbOverrideSchema
        sqlNullSubrole = """
                update %s.vista_enrollment_info set 
                status = :status,
                comments = :comments,
                expire_ts = :expire_ts
                where
                        vista_member_id= :vista_member_id
                        and vista_group_id=:vista_group_id
                        and role = :role
                        and subrole is null
        """%config.dbOverrideSchema
 
        con = self.pool.acquire()
        curs = con.cursor()
 
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
 
        logger.debug("status=" + str(member.get("status")))
        logger.debug("comments=" + str(member.get("comments")))
        logger.debug("expire_Ts=" +str( member.get("expirets")))
        logger.debug("vista_member_id=" + str(member.get("dbmemberid")))
        logger.debug("vista_group_id=" + str(member.get("dbgroupid")))
        logger.debug("role=" + str(member.get("roletype")))
        logger.debug("subrole=" + str(member.get("subrole")))
 
        params = {}
        params["status"] = member.get("status")
        params["comments"] = member.get("comments")
        params["expire_ts"] = member.get("expirets")
        params["vista_member_id"] = member.get("dbmemberid")
        params["vista_group_id"] = member.get("dbgroupid")
        params["role"] = member.get("roletype")
        if member.get("subrole") != None:
                params["subrole"] = member.get("subrole")
                curs.execute(sql,params)
        else:
                curs.execute(sqlNullSubrole,params)
        curs.close()
 
        con.commit()
        self.pool.release(con)
        curs=None
        con=None
 
 
 
    def insertMember(self,member):
        if not hasattr(member,"dbgroupid"):
            member.dbgroupid = self.getGroupDbId(member.groupsourcedidid, member.groupsourcedidsource)
 
        if not hasattr(member,"dbmemberid"):
            member.dbmemberid = self.getMemberDbId(member.sourcedidid, member.sourcedidsource)
 
        logger.debug("started insertRole with dbgroupid=" + str(member.dbgroupid) + " dbmemberid=" + str(member.dbmemberid))
        sql = """
            insert into %s.VISTA_ENROLLMENT_INFO (
            VISTA_MEMBER_ID,
            VISTA_GROUP_ID,
            ROLE,
            SUBROLE,
            STATUS,
            COMMENTS,
            CREATOR,
            VISTA_DATA_SOURCE_ID
            )values(
            :vista_member_id,
            :vista_group_id,
            :role,
            :subrole,
            :status,
            :comments,
            :creator,
            :vista_data_source_id
            )
 
        """%config.dbOverrideSchema
 
        con = self.pool.acquire()
        curs = con.cursor()
 
        params={}
        params["vista_member_id"]=member.get("dbmemberid")
        params["vista_group_id"]=member.get("dbgroupid")
        params["role"]=member.get("roletype")
        params["subrole"]=member.get("subrole")
        params["status"]=member.get("status")
        params["comments"]=member.get("comments")
        params["creator"]=config.feedName
        params["vista_data_source_id"]=config.datasource
 
        curs.execute(sql,params)
        curs.close()
 
        con.commit()
        self.pool.release(con)
        curs=None
        con=None
 
    def getMemberExpire(self,sourcedidid,sourcedidsource):
        logger.debug("Starting getMemberExpire")
 
        sql = """
                select expire_ts
                from %s.vista_member_info m 
                where
                        m.sourcedid_id =  :sourcedid_id
                        and m.sourcedid_source = :sourcedid_source
        """%config.dbOverrideSchema
 
        con = self.pool.acquire()
        curs = con.cursor()
        logger.debug("About to execute the following SQL")
        logger.debug(sql)
        logger.debug("sourcedid_id=" + sourcedidid)
        logger.debug("sourcedid_name=" + sourcedidsource)
        rset = curs.execute(sql,sourcedid_id=sourcedidid,sourcedid_source=sourcedidsource)
        row = rset.fetchone()
        ret = None
        if row:
            ret = row[0]
        curs.close()
 
        self.pool.release(con)
        curs=None
        con=None
 
        return ret