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
