class ProjectHouseholdCharacteristic:
    def __init__(self,  pid,  charname,  datatype="" ):
        self.pid = pid
        self.charname = charname
        self.database = Database()
        #if characteristic is new add it to database
        if ( datatype != "" ):
            self.addCharacteristic( charname,  datatype )
            
    def addCharacteristic(self,  charname, datatype):
        # get the target table
        tbl = '''p%iHouseholdCharacteristics''' % (self.pid)
        # determine the data type
        if datatype == "1":
            vartype = "ENUM('Yes','No')"
        elif datatype == "2":
            vartype = "BIGINT"
        elif datatype == "3":
            vartype = "VARCHAR(250)"
        elif datatype == "4":
            vartype = "DOUBLE"
            
        # check if characteristic already exists
        query = '''SHOW COLUMNS FROM %s ''' % (tbl)
        
        self.database.open()
        rows = self.database.execSelectQuery(query)
        
        exists = False
        for row in rows:
            if ( row[0]  == charname ):
                exists = True
        
        # add or (if characteristic exists) modify characteristic 
        if ( exists ):
            queryAlter = '''ALTER TABLE `%s` CHANGE COLUMN `%s` %s ''' % (tbl, charname, vartype)
        else:
            queryAlter = '''ALTER TABLE `%s` ADD COLUMN `%s` %s ''' % (tbl, charname, vartype)
            
        self.database.execDefinitionQuery( queryAlter )
        self.database.close()
        
    def getName(self):
        return self.charname
Exemple #2
0
class Project(IncomeSourceManager):
    def __init__(self, pid=0, projectname="", startdate="", enddate="", description="", currency=""):
        self.database = Database() 
        if ( pid != 0 ):
            if ( not self.getProjectDetails( pid ) ):
                return None
        else:
            self.addProject(projectname, startdate, enddate, description, currency)
            
    def getProjectDetails(self,  pid):
        self.database.open()
        query = "SELECT pid, projectname, startdate, enddate, description, currency FROM projects WHERE pid=%i " % (pid)
        rows = self.database.execSelectQuery( query )
        self.database.close()
        num = len(rows)
        if (num != 0):
            exists = True
            for row in rows:
                self.pid = row[0]
                self.projectname = row[1]
                self.startdate = row[2]
                self.enddate = row[3]
                self.description = row[4]
                self.currency = row[5]
        else:
            exists = False
        self.database.close()
        return exists
        
    def addProject(self, projectname, startdate, enddate, description, currency):
        # create INSERT INTO query
        query = '''INSERT INTO projects(projectname,startdate,enddate,description,currency) 
                     VALUES('%s','%s', '%s', '%s', '%s')''' % (projectname, startdate, enddate, description, currency)
        
        # execute query and commit changes
        self.database.open()
        self.database.execUpdateQuery( query )
        
        # get the ID of the newly inserted project
        query = "SELECT LAST_INSERT_ID()"
        rows = self.database.execSelectQuery( query )
        for row in rows:
            projectid = row[0]
            
        # create project specific tables (e.g. householdcharacteristics & personalcharacteristics)
        HouseholdCharacteristicsTableName 	= '''p%iHouseholdCharacteristics''' % (projectid)
        PersonalCharactericticsTableName	= '''p%iPersonalCharacteristics''' % (projectid)
        CropsTableName	        = '''p%iCrops''' % (projectid)
        EmploymentTableName	= '''p%iEmploymentTypes''' % (projectid)
        LivestockTableName	= '''p%iLivestock''' % (projectid)
        WildFoosTableName	= '''p%iWildFoods''' % (projectid)
        
        queryCreate = '''CREATE  TABLE IF NOT EXISTS `openihmdb`.`%s` (
							`hhid` INT(11) NOT NULL ,
							`pid` INT(11) NOT NULL ,
							PRIMARY KEY (`hhid`, `pid`) ,
							INDEX `fk_p%i_householdcharacteristics_households` (`hhid` ASC, `pid` ASC) ,
							CONSTRAINT `fk_p%i_householdcharacteristics_households`
							FOREIGN KEY (`hhid` , `pid` )
							REFERENCES `openihmdb`.`households` (`hhid` , `pid` )
							ON DELETE CASCADE
							ON UPDATE CASCADE)
						 ENGINE = InnoDB
						 DEFAULT CHARACTER SET = latin1;''' % (HouseholdCharacteristicsTableName, projectid, projectid)
                        
        self.database.execDefinitionQuery( queryCreate )
        
        queryCreate = '''CREATE  TABLE IF NOT EXISTS `openihmdb`.`%s` (
						  `personid` VARCHAR(20) NOT NULL ,
						  `hhid` INT(11) NOT NULL ,
						  `pid` INT(11) NOT NULL ,
						  PRIMARY KEY (`personid`, `hhid`, `pid`) ,
						  INDEX `fk_p%i_personalcharacteristics_householdmembers` (`personid` ASC, `hhid` ASC, `pid` ASC) ,
						  CONSTRAINT `fk_p%i_personalcharacteristics_householdmembers`
						  FOREIGN KEY (`personid` , `hhid` , `pid` )
						  REFERENCES `openihmdb`.`householdmembers` (`personid` , `hhid` , `pid` )
						  ON DELETE CASCADE
						  ON UPDATE CASCADE)
						 ENGINE = InnoDB
						 DEFAULT CHARACTER SET = latin1;''' % (PersonalCharactericticsTableName, projectid, projectid)
        
        self.database.execDefinitionQuery( queryCreate )

        queryCreate = '''CREATE  TABLE IF NOT EXISTS `openihmdb`.`%s` (
						  `foodtype` VARCHAR(50) NOT NULL ,
						  `energyvalueperunit` INT(11) NOT NULL ,
						  `measuringunit` VARCHAR(20),
						  PRIMARY KEY (`foodtype`) ,
						  INDEX (`foodtype` ASC) )
						 ENGINE = InnoDB
						 DEFAULT CHARACTER SET = latin1;''' % (CropsTableName)
        
        self.database.execDefinitionQuery( queryCreate )

        queryCreate = '''CREATE  TABLE IF NOT EXISTS `openihmdb`.`%s` (
						  `incomesource` VARCHAR(50) NOT NULL ,
						  `energyvalueperunit` INT(11) NOT NULL ,
						  `measuringunit` VARCHAR(20),
						  PRIMARY KEY (`incomesource`) ,
						  INDEX (`incomesource` ASC) )
						 ENGINE = InnoDB
						 DEFAULT CHARACTER SET = latin1;''' % (LivestockTableName)
        
        self.database.execDefinitionQuery( queryCreate )

        queryCreate = '''CREATE  TABLE IF NOT EXISTS `openihmdb`.`%s` (
						  `incomesource` VARCHAR(50) NOT NULL ,
						  `energyvalueperunit` INT(11) NOT NULL ,
						  `measuringunit` VARCHAR(20),
						  PRIMARY KEY (`incomesource`) ,
						  INDEX (`incomesource` ASC) )
						 ENGINE = InnoDB
						 DEFAULT CHARACTER SET = latin1;''' % (WildFoosTableName)
        
        self.database.execDefinitionQuery( queryCreate )
        
        self.database.close()
        
        # set project attributes to saved values
        self.pid = projectid
        self.projectname = projectname
        self.startdate = startdate
        self.enddate = enddate
        self.description = description
        self.currency = currency
        
    def getProjectID(self):
        return self.pid
    
    def getProjectName(self):
        return self.projectname
        
    def getStartDate(self):
        return self.startdate
    
    def getEndDate(self):
        return self.enddate
        
    def getDescription(self):
        return self.description
        
    def getCurrency(self):
        return self.currency
        
    def setData(self, projectname, startdate, enddate, description, currency):
        # create UPDATE query to update project
        query = '''UPDATE projects SET projectname='%s', startdate = '%s',enddate = '%s',description = '%s',currency = '%s'
                     WHERE pid=%i''' % (projectname, startdate, enddate, description, currency, self.pid)
    
        # execute query
        self.database.open()
        self.database.execUpdateQuery( query )
        self.database.close()
        
        # set project attributes to saved values
        self.projectname = projectname
        self.startdate = startdate
        self.enddate = enddate
        self.description = description
        self.currency = currency
        
    #-------------------------------------------------------------------------------------------------------
    #  Project Household Characteristics
    #-------------------------------------------------------------------------------------------------------
        
    def addHouseholdCharacteristic(self, char, datatype):
        char = ProjectHouseholdCharacteristic( self.pid,  char, datatype )
            
    def removeHouseholdCharacteristic(self, char):
         #get target table
        tbl = '''p%iHouseholdCharacteristics''' % (self.pid)
        #delete the characteristic field
        self.database.open()
            
        queryAlter = '''ALTER TABLE `%s` DROP `%s` ''' % (tbl, char)
        
        self.database.execDefinitionQuery(queryAlter)
        self.database.close()
        
    def getHouseholdCharacteristics(self):
        #get target table
        tbl = '''p%iHouseholdCharacteristics''' % (self.pid)
        
        query = '''SHOW COLUMNS FROM %s ''' % (tbl)
        
        self.database.open()
        rows = self.database.execSelectQuery(query)
        self.database.close()
        
        chars = []
        for row in rows:
            if (("hhid" != row[0]) and ("pid" != row[0])):
                char = ProjectHouseholdCharacteristic( self.pid,  row[0] )
                chars.append( char )
                
        return chars
        
    #-------------------------------------------------------------------------------------------------------
    #  Project Person Characteristics
    #-------------------------------------------------------------------------------------------------------
        
    def addPersonCharacteristic(self, char, datatype):
        char = ProjectPersonCharacteristic( self.pid,  char, datatype )
            
    def removePersonCharacteristic(self, char):
         #get target table
        tbl = '''p%iPersonalCharacteristics''' % (self.pid)
        #delete the characteristic field
        self.database.open()
            
        queryAlter = '''ALTER TABLE `%s` DROP `%s` ''' % (tbl, char)
        
        self.database.execDefinitionQuery(queryAlter)
        self.database.close()
        
    def getPersonCharacteristics(self):
        #get target table
        tbl = '''p%iPersonalCharacteristics''' % (self.pid)
        
        query = '''SHOW COLUMNS FROM %s ''' % (tbl)
        
        self.database.open()
        rows = self.database.execSelectQuery(query)
        self.database.close()
        
        chars = []
        for row in rows:
            if (("hhid" != row[0]) and ("personid" != row[0]) and ("pid" != row[0])):
                char = ProjectPersonCharacteristic( self.pid,  row[0] )
                chars.append( char )
                
        return chars
        
    #-------------------------------------------------------------------------------------------------------
    #  Project Households
    #-------------------------------------------------------------------------------------------------------
        
    def getHousehold(self,  hhid):
        household = Household( self.pid,  hhid )
        return household
        
    def addHousehold(self, hhid, householdname, dateofcollection):
        household = Household( self.pid,  hhid,  householdname, dateofcollection )
            
    def editHousehold(self, hhid, newhhid,  householdname, dateofcollection):
        household = Household( self.pid,  hhid )
        household.setData( householdname, dateofcollection,  newhhid )
        
    def delHousehold(self,  hhid):
        query = "DELETE FROM households WHERE pid=%s AND hhid=%s " % ( self.pid, hhid )
        self.database.open()
        self.database.execUpdateQuery( query )
        self.database.close()
        
    def getHouseholds(self):
        query = '''SELECT hhid FROM households WHERE pid=%s ''' % (self.pid)
        
        self.database.open()
        rows = self.database.execSelectQuery(query)
        self.database.close()
        
        households = []
        for row in rows:
            household = Household( self.pid,  row[0] )
            households.append( household )
                
        return households