def getIncomeDetails(self, pid, hhid, incomeid): database = Database() database.open() query = '''SELECT id, sourcetype, sourceoftransfer, cashperyear, foodtype, unitofmeasure, unitsgiven, unitsconsumed, unitssold, priceperunit FROM transfers WHERE hhid=%s AND pid=%s AND id=%s ''' % ( hhid, pid, incomeid ) rows = database.execSelectQuery( query ) num = len(rows) if (num != 0): exists = True for row in rows: self.pid = pid self.hhid = hhid self.incomeid = incomeid self.sourcetype = row[1] self.sourceoftransfer = row[2] self.cashperyear = row[3] self.foodtype = row[4] self.unitofmeasure = row[5] self.unitsgiven = row[6] self.unitsconsumed = row[7] self.unitssold = row[8] self.priceperunit = row[9] else: exists = False database.close() return exists
def setData(self, pid, hhid, sourcetype, sourceoftransfer, cashperyear, foodtype, unitofmeasure, unitsgiven, unitsconsumed, unitssold, priceperunit): database = Database() database.open() query = '''INSERT INTO transfers(pid, hhid, sourcetype, sourceoftransfer, cashperyear, foodtype, unitofmeasure, unitsgiven, unitsconsumed, unitssold, priceperunit) VALUES(%s,%s,'%s','%s',%s,'%s','%s',%s, %s,%s,%s) ''' % ( pid, hhid, sourcetype, sourceoftransfer, cashperyear, foodtype, unitofmeasure, unitsgiven, unitsconsumed, unitssold, priceperunit ) # execute query database.execUpdateQuery( query ) query = "SELECT LAST_INSERT_ID()" rows = database.execSelectQuery( query ) for row in rows: incomeid = row[0] database.close() # update income attributes self.pid = pid self.hhid = hhid self.incomeid = incomeid self.sourcetype = sourcetype self.sourceoftransfer = sourceoftransfer self.cashperyear = cashperyear self.foodtype = foodtype self.unitofmeasure = unitofmeasure self.unitsgiven = unitsgiven self.unitsconsumed = unitsconsumed self.unitssold = unitssold self.priceperunit = priceperunit
def setData(self, pid, hhid, category, assettype, unitofmeasure, costperunit, numunits): database = Database() database.open() query = '''INSERT INTO assets (hhid, assetcategory, assettype, unitofmeasure, unitcost, totalunits, pid ) VALUES(%s,'%s','%s','%s',%s,%s,%s) ''' % ( hhid, category, assettype, unitofmeasure, costperunit, numunits, pid ) # execute query database.execUpdateQuery( query ) query = "SELECT LAST_INSERT_ID()" rows = database.execSelectQuery( query ) for row in rows: assetid = row[0] database.close() # update asset attributes self.pid = pid self.hhid = hhid self.assetid = assetid self.category = category self.assettype = assettype self.unitofmeasure = unitofmeasure self.costperunit = costperunit self.numunits = numunits
def setData(self, pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed): database = Database() database.open() query = '''INSERT INTO cropincome(pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed) VALUES(%s,%s,'%s','%s',%s,%s,%s,%s,%s) ''' % ( pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed ) print query # execute query database.execUpdateQuery( query ) query = "SELECT LAST_INSERT_ID()" rows = database.execSelectQuery( query ) for row in rows: incomeid = row[0] database.close() # update income attributes self.pid = pid self.hhid = hhid self.incomeid = incomeid self.incomesource = incomesource self.unitofmeasure = unitofmeasure self.unitsproduced = unitsproduced self.unitssold = unitssold self.unitprice = unitprice self.otheruses = otheruses self.unitsconsumed = unitsconsumed
def getAssetDetails(self, pid, assetname): ''' Retrieves an asset matching assetname ''' # connect to database db = Database() db.open() # query to retrieve asset details assetname = common.getDbString( assetname ) query = "SELECT pid, assetname, assettype FROM projectassets WHERE pid=%s AND assetname='%s' " % (pid, assetname) # get rows returned by query rows = db.execSelectQuery( query ) # get asset details from record and assign to object attrbutes num = len(rows) if (num != 0): exists = True for row in rows: self.pid = pid self.name = common.getViewString( row[1] ) self.type = common.getViewString( row[2] ) else: exists = False # close database connection db.close() return exists
def getIncomeDetails(self, pid, hhid, incomeid): database = Database() database.open() query = '''SELECT id, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed FROM cropincome WHERE hhid=%s AND pid=%s AND id=%s ''' % ( hhid, pid, incomeid ) rows = database.execSelectQuery( query ) num = len(rows) if (num != 0): exists = True for row in rows: self.pid = pid self.hhid = hhid self.incomeid = incomeid self.incomesource = row[1] self.unitofmeasure = row[2] self.unitsproduced = row[3] self.unitssold = row[4] self.unitprice = row[5] self.otheruses = row[6] self.unitsconsumed = row[7] else: exists = False 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 database = Database() database.open() database.execUpdateQuery( query ) # get the ID of the newly inserted project query = "SELECT LAST_INSERT_ID()" rows = database.execSelectQuery( query ) for row in rows: projectid = row[0] 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 getIncomeSourceDetails(self, pid, incomesourcename): ''' Retrieves a incomesource object corresponding to the given incomesourcename from database ''' # connect to database db = Database() db.open() # query to retrieve incomesource details incomesourcename = common.getDbString( incomesourcename ) query = "SELECT pid, incomesource, incometype FROM projectincomesources WHERE pid=%s AND incomesource='%s' " % (pid, incomesourcename) # get rows returned by query rows = db.execSelectQuery( query ) # get incomesource details from record and assign to object attrbutes num = len(rows) if (num != 0): exists = True for row in rows: self.pid = pid self.name = common.getViewString( row[1] ) self.type = common.getViewString( row[2] ) else: exists = False # close database connection db.close() return exists
def getMembers(self): query = "SELECT personid FROM householdmembers WHERE pid=%s AND hhid=%s" % ( self.pid, self.hhid ) database = Database() database.open() rows =database.execSelectQuery( query ) database.close() members = [] for row in rows: id = row[0] member = HouseholdMember(self.pid, self.hhid, id) members.append( member ) return members
def getEmploymentIncomes(self): query = "SELECT id FROM employmentincome WHERE pid=%s AND hhid=%s" % ( self.pid, self.hhid ) database = Database() database.open() rows =database.execSelectQuery( query ) database.close() empincomes = [] for row in rows: incomeid = row[0] empincome = HouseholdEmploymentIncome(incomeid, self.pid, self.hhid) empincomes.append( empincome ) return empincomes
def getCharacteristicsWithValues(self): query = "SELECT characteristic FROM householdcharacteristics WHERE pid=%s AND hhid=%s" % ( self.pid, self.hhid ) database = Database() database.open() rows =database.execSelectQuery( query ) database.close() chars = [] for row in rows: charname = row[0] char = HouseholdCharacteristic(self.pid, self.hhid, charname) chars.append( char ) return chars
def getTransferType(self, sourceoftransfer): query = """SELECT assistancetype FROM setup_transfers WHERE sourceoftransfer='%s' """ % sourceoftransfer db = Database() db.open() records = db.execSelectQuery(query) transfertype = "INTERNAL" for rec in records: transfertype = "INTERNAL" if (rec[0] == "INTERNAL" or rec[0] == "Unofficial") else "EXTERNAL" db.close() return transfertype
def getWildfoodsIncomes(self): query = "SELECT id FROM wildfoods WHERE pid=%s AND hhid=%s" % ( self.pid, self.hhid ) database = Database() database.open() rows =database.execSelectQuery( query ) database.close() wsincomes = [] for row in rows: incomeid = row[0] wsincome = HouseholdWildfoodsIncome(incomeid, self.pid, self.hhid) wsincomes.append( wsincome ) return wsincomes
def getHouseholds(self): query = '''SELECT hhid FROM households WHERE pid=%s ''' % (self.pid) database = Database() database.open() rows = database.execSelectQuery(query) database.close() households = [] for row in rows: household = Household( self.pid, row[0] ) households.append( household ) return households
def getTransferIncomes(self): query = "SELECT id FROM transfers WHERE pid=%s AND hhid=%s" % ( self.pid, self.hhid ) database = Database() database.open() rows =database.execSelectQuery( query ) database.close() transincomes = [] for row in rows: incomeid = row[0] transincome = HouseholdTransfersIncome(incomeid, self.pid, self.hhid) transincomes.append( transincome ) return transincomes
def getCropIncomes(self): query = "SELECT id FROM cropincome WHERE pid=%s AND hhid=%s" % ( self.pid, self.hhid ) database = Database() database.open() rows =database.execSelectQuery( query ) database.close() cropincomes = [] for row in rows: incomeid = row[0] cropincome = HouseholdCropIncome(incomeid, self.pid, self.hhid) cropincomes.append( cropincome ) return cropincomes
def getLivestockIncomes(self): query = "SELECT id FROM livestockincome WHERE pid=%s AND hhid=%s" % ( self.pid, self.hhid ) database = Database() database.open() rows =database.execSelectQuery( query ) database.close() livestockincomes = [] for row in rows: incomeid = row[0] livestockincome = HouseholdLivestockIncome(incomeid, self.pid, self.hhid) livestockincomes.append( livestockincome ) return livestockincomes
def getAssets(self): query = "SELECT assetid FROM assets WHERE pid=%s AND hhid=%s" % ( self.pid, self.hhid ) database = Database() database.open() rows =database.execSelectQuery( query ) database.close() assets = [] for row in rows: assetid = row[0] asset = HouseholdAsset(assetid, self.pid, self.hhid) assets.append( asset ) return assets
def getHouseholdDetails(self): database = Database() database.open() query = "SELECT householdname, dateofcollection FROM households WHERE pid=%s AND hhid=%s " % ( self.pid, self.hhid ) rows = database.execSelectQuery( query ) num = len(rows) if (num != 0): exists = True for row in rows: self.householdname = row[0] self.dateofcollection = row[1] else: exists = False database.close() return exists
def getCharacteristicsWithValues(self): query = '''SELECT characteristic FROM personalcharacteristics WHERE pid=%s AND hhid=%s AND personid='%s' ''' % ( self.pid, self.hhid, self.memberid ) database = Database() database.open() rows =database.execSelectQuery( query ) database.close() chars = [] for row in rows: charname = row[0] char = HouseholdMemberCharacteristic(self.pid, self.hhid, self.memberid, charname) chars.append( char ) return chars
def getCropAssets(self, assets): ''' Retrieves crop assets ''' query = '''SELECT foodtype FROM setup_crops''' db = Database() db.open() records = db.execSelectQuery( query ) for rec in records: assetname = rec[0] asset = GlobalAsset(assetname, "Crops") assets.append( asset ) db.close() return assets
def getEmploymentIncomes(self): ''' Retrieve employment incomes ''' # select query to get matchng food incomes query = '''SELECT incomesource FROM setup_employment''' db = Database() db.open() records = db.execSelectQuery( query ) incomesources = [] for rec in records: incomesource = rec[0] incomesources.append( incomesource ) db.close() return incomesources
def getLivestockAssets(self, assets): ''' Retrieve livestock assets ''' query = '''SELECT incomesource FROM setup_livestock''' db = Database() db.open() records = db.execSelectQuery( query ) for rec in records: assetname = rec[0] asset = GlobalAsset(assetname, "Livestock") assets.append( asset ) db.close() return assets
def getFoodIncomes(self,incometype=None): ''' Retrieve food (crop, livestock or wildfood incomes) ''' # select query to get matchng food incomes query = '''SELECT name FROM setup_foods_crops WHERE category='%s' ''' % ( incometype ) db = Database() db.open() records = db.execSelectQuery( query ) incomesources = [] for rec in records: incomesource = rec[0] incomesources.append( incomesource ) db.close() return incomesources
def getLandAssets(self, assets): ''' Retrieves landed assets ''' query = '''SELECT landtype FROM setup_landtypes''' db = Database() db.open() records = db.execSelectQuery( query ) for rec in records: assetname = rec[0] asset = GlobalAsset(assetname, "Land") assets.append( asset ) db.close() return assets
def getTradableGoods(self, assets): ''' Retrieve tradable goods ''' query = '''SELECT tradablegoodtype FROM setup_tradablegoods''' db = Database() db.open() records = db.execSelectQuery( query ) for rec in records: assetname = rec[0] asset = GlobalAsset(assetname, "Tradable Goods") assets.append( asset ) db.close() return assets
def getTransferIncomes(self): ''' Retrieve transfer incomes ''' # select query to get matchng food incomes query = '''SELECT assistancetype FROM setup_transfers''' db = Database() db.open() records = db.execSelectQuery( query ) incomesources = [] for rec in records: incomesource = rec[0] incomesources.append( incomesource ) db.close() return incomesources
def getProjectDietItems(self): strcondition = "WHERE pid=%s" % (self.pid) query = "SELECT id FROM diet %s" % strcondition database = Database() database.open() rows = database.execSelectQuery( query ) database.close() items = [] for row in rows: itemid = row[0] item = ProjectDietItem(self.pid, itemid) items.append( item ) return items
def getAllCharacteristics(self): query = "SELECT characteristic FROM projectcharacteristics WHERE pid=%s AND chartype='Household'" % ( self.pid ) database = Database() database.open() rows =database.execSelectQuery( query ) database.close() chars = [] for row in rows: charname = row[0] if self.existsCharacteristic(charname): char = HouseholdCharacteristic(self.pid, self.hhid, charname) else: char = UnsetCharacteristic(self.pid, self.hhid, charname) chars.append( char ) return chars
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