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 delCharacteristic(self, charname):
query = '''DELETE FROM householdcharacteristics WHERE pid=%s
AND hhid=%s AND characteristic='%s' ''' % ( self.pid, self.hhid, charname )
database = Database()
database.open()
database.execUpdateQuery( query )
database.close()
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, 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, 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 delCharacteristic(self, charname):
query = '''DELETE FROM personalcharacteristics WHERE pid=%s
AND hhid=%s AND personid='%s' AND characteristic='%s' ''' % ( self.pid, self.hhid, self.memberid, charname )
database = Database()
database.open()
database.execUpdateQuery( query )
database.close()
def delWildfoodsIncomes(self, incomeids):
database = Database()
database.open()
for incomeid in incomeids:
query = "DELETE FROM wildfoods WHERE pid=%s AND hhid=%s AND id=%s " % ( self.pid, self.hhid, incomeid )
database.execUpdateQuery( query )
database.close()
def delLivestockIncomes(self, incomeids):
database = Database()
database.open()
for incomeid in incomeids:
query = "DELETE FROM livestockincome WHERE pid=%s AND hhid=%s AND id=%s " % ( self.pid, self.hhid, incomeid )
database.execUpdateQuery( query )
database.close()
def delAssets(self, assetids):
database = Database()
database.open()
for assetid in assetids:
query = "DELETE FROM assets WHERE pid=%s AND hhid=%s AND assetid=%s " % ( self.pid, self.hhid, assetid )
database.execUpdateQuery( query )
database.close()
def delEmploymentIncomes(self, incomeids):
database = Database()
database.open()
for incomeid in incomeids:
query = "DELETE FROM employmentincome WHERE pid=%s AND hhid=%s AND id=%s " % ( self.pid, self.hhid, incomeid )
database.execUpdateQuery( query )
database.close()
def deleteProjectAsset(self, assetname):
''' Remove an asset from project '''
db = Database()
db.open()
assetname = common.getDbString( assetname )
query = "DELETE FROM projectassets WHERE pid=%s AND assetname='%s'" % (self.pid, assetname)
db.execUpdateQuery( query )
db.close()
def deleteIncomeSources(self, incomesources):
''' Deletes incomesources matching names in the array incomesources from database '''
db = Database()
db.open()
for incomesourcename in incomesources:
incomesourcename = common.getDbString( incomesourcename )
query = "DELETE FROM projectincomesources WHERE pid=%s AND incomesource='%s'" % (self.pid, incomesourcename)
db.execUpdateQuery( query )
db.close()
def setData(self, householdname, dateofcollection):
database = Database()
database.open()
query = '''INSERT INTO households(hhid,pid,dateofcollection,householdname)
VALUES(%s,%s, '%s', '%s')''' % (self.hhid, self.pid, dateofcollection, householdname)
# execute query
database.execUpdateQuery( query )
database.close()
# update household attributes
self.householdname = householdname
self.dateofcollection = dateofcollection
def editData(self, hhid, householdname, dateofcollection):
database = Database()
database.open()
query = '''UPDATE households SET hhid=%s, dateofcollection='%s', householdname='%s'
WHERE hhid=%s AND pid=%s''' % (hhid, dateofcollection, householdname, self.hhid, self.pid)
# execute query
database.execUpdateQuery( query )
database.close()
# update household attributes
self.hhid = hhid
self.householdname = householdname
self.dateofcollection = dateofcollection
def editData(self, charname, charvalue):
database = Database()
database.open()
query = ''' UPDATE personalcharacteristics SET characteristic='%s', charvalue='%s'
WHERE hhid=%s AND pid=%s AND personid='%s'
AND characteristic='%s' ''' % ( charname, charvalue, self.hhid, self.pid, self.personid, self.name)
# execute query
database.execUpdateQuery( query )
database.close()
# update asset attributes
self.name = charname
self.charvalue = charvalue
def setData(self, pid, hhid, charname, charvalue):
database = Database()
database.open()
query = '''INSERT INTO householdcharacteristics (pid,hhid, characteristic, charvalue )
VALUES(%s,%s,'%s','%s') ''' % ( pid, hhid, charname, charvalue )
# execute query
database.execUpdateQuery( query )
database.close()
# update attributes
self.pid = pid
self.hhid = hhid
self.name = charname
self.charvalue = charvalue
def editData(self, characteristic, chartype, datatype):
database = Database()
database.open()
query = ''' UPDATE projectcharacteristics SET characteristic='%s', chartype='%s', datatype=%s
WHERE pid=%s AND characteristic='%s' ''' % ( characteristic, chartype, datatype, self.pid, self.name)
# execute query
database.execUpdateQuery( query )
database.close()
# update characteristic attributes
self.name = characteristic
self.chartype = chartype
self.datatype = datatype
self.datatypestr = self.getStringDataType()
def editData(self, fooditem, unitofmeasure, percentage, priceperunit):
database = Database()
database.open()
query = ''' UPDATE diet SET fooditem='%s', unitofmeasure='%s', percentage=%s, priceperunit=%s
WHERE id=%s AND pid=%s ''' % ( fooditem,unitofmeasure,percentage, priceperunit, self.itemid, self.pid)
# execute query
database.execUpdateQuery( query )
database.close()
# update characteristic attributes
self.fooditem = fooditem
self.unitofmeasure = unitofmeasure
self.percentage = percentage
self.priceperunit = priceperunit
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
database = Database()
database.open()
database.execUpdateQuery( query )
database.close()
# set project attributes to saved values
self.projectname = projectname
self.startdate = startdate
self.enddate = enddate
self.description = description
self.currency = currency
def setData(self, characteristic, chartype, datatype, description):
database = Database()
database.open()
query = '''INSERT INTO globalcharacteristics (characteristic, chartype, datatype, description )
VALUES('%s','%s',%s,'%s') ''' % ( characteristic, chartype, datatype, description )
# execute query
database.execUpdateQuery( query )
database.close()
# update characteristic attributes
self.name = characteristic
self.chartype = chartype
self.datatype = datatype
self.datatypestr = self.getStringDataType()
self.description = description
def setData(self, pid, characteristic, chartype, datatype):
database = Database()
database.open()
query = '''INSERT INTO projectcharacteristics (pid, characteristic, chartype, datatype )
VALUES(%s,'%s','%s',%s) ''' % ( pid, characteristic, chartype, datatype )
# execute query
database.execUpdateQuery( query )
database.close()
# update characteristic attributes
self.pid = pid
self.name = characteristic
self.chartype = chartype
self.datatype = datatype
self.datatypestr = self.getStringDataType()
def editData(self, category, assettype, unitofmeasure, costperunit, numunits):
database = Database()
database.open()
query = ''' UPDATE assets SET assetcategory='%s', assettype='%s', unitofmeasure='%s', unitcost=%s, totalunits=%s
WHERE hhid=%s AND pid=%s
AND assetid=%s ''' % ( category, assettype, unitofmeasure, costperunit, numunits, self.hhid, self.pid, self.assetid)
# execute query
database.execUpdateQuery( query )
database.close()
# update asset attributes
self.category = category
self.assettype = assettype
self.unitofmeasure = unitofmeasure
self.costperunit = costperunit
self.numunits = numunits
class EditDietModelPrice:
def __init__(self, modelprice,foodname, projectid):
self.database = Database()
self.foodname = foodname
self.modelprice = modelprice
self.projectid = projectid
if ( self.foodname == "") or ( self.modelprice == ""):
return None
else:
self.setData()
def setData(self):
self.database.open()
query = '''UPDATE diet SET modelprice=%s WHERE fooditem='%s' AND pid=%s''' % (self.modelprice, self.foodname, self.projectid)
# execute query
self.database.execUpdateQuery( query )
self.database.close()
class EditStandardOfLivingModelPrice:
def __init__(self, modelprice, item,pid):
self.database = Database()
self.item = item
self.modelprice = modelprice
self.projectid = pid
if ( self.item == "") or ( self.modelprice == ""):
return None
else:
self.setData()
def setData(self):
self.database.open()
query = '''UPDATE standardofliving SET modelprice=%s WHERE summary='%s' AND pid=%s''' % (self.modelprice, self.item, self.projectid)
# execute query
self.database.execUpdateQuery( query )
self.database.close()
def editData(self, characteristic, chartype, datatype, description):
database = Database()
database.open()
query = ''' UPDATE globalcharacteristics SET characteristic='%s', chartype='%s', datatype=%s, description='%s'
WHERE characteristic='%s' ''' % ( characteristic, chartype, datatype, description, self.name)
# execute query
database.execUpdateQuery( query )
database.close()
# update characteristic attributes
self.name = characteristic
self.chartype = chartype
self.datatype = datatype
self.datatypestr = self.getStringDataType()
self.description = description
class EditIncomeSourcesModelPrice:
def __init__(self, incomesource,mpercentageincome, projectid):
self.database = Database()
self.incomesource = incomesource
self.percentageincome = mpercentageincome
self.projectid = projectid
if ( self.incomesource == "") or ( self.percentageincome == ""):
return None
else:
self.setData()
def setData(self):
query = '''UPDATE employmentincome SET preferenceincome=%s
WHERE incomesource='%s' AND pid=%s''' % (self.percentageincome, self.incomesource, self.projectid)
# execute query
self.database.open()
self.database.execUpdateQuery( query )
self.database.close()
def setData(self, pid, summary, scope, gender, agebottom, agetop, item, costperyear):
database = Database()
database.open()
query = '''INSERT INTO standardofliving (pid, summary, scope, gender, agebottom, agetop, item, costperyear )
VALUES(%s,'%s','%s','%s',%s,%s,'%s',%s) ''' % ( pid, summary, scope, gender, agebottom, agetop, item, costperyear )
# execute query
database.execUpdateQuery( query )
database.close()
# update characteristic attributes
self.pid = pid
self.summary = summary
self.scope = scope
self.gender = gender
self.agebottom = agebottom
self.agetop = agetop
self.item = item
self.costperyear = costperyear
def editData(self, summary, scope, gender, agebottom, agetop, item, costperyear):
database = Database()
database.open()
query = ''' UPDATE standardofliving SET summary='%s', scope='%s', gender='%s', agebottom=%s, agetop=%s,
item='%s', costperyear=%s WHERE pid=%s AND
summary='%s' ''' % ( summary, scope, gender, agebottom, agetop, item, costperyear, self.pid, self.summary)
# execute query
database.execUpdateQuery( query )
database.close()
# update characteristic attributes
self.summary = summary
self.scope = scope
self.gender = gender
self.agebottom = agebottom
self.agetop = agetop
self.item = item
self.costperyear = costperyear
def editData(self, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed):
database = Database()
database.open()
query = ''' UPDATE cropincome SET incomesource='%s', unitofmeasure='%s', unitsproduced=%s, unitssold=%s, unitprice=%s,
otheruses=%s, unitsconsumed=%s
WHERE hhid=%s AND pid=%s
AND id=%s ''' % ( incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed, self.hhid, self.pid, self.incomeid)
# execute query
database.execUpdateQuery( query )
database.close()
# update asset attributes
self.incomesource = incomesource
self.unitofmeasure = unitofmeasure
self.unitsproduced = unitsproduced
self.unitssold = unitssold
self.unitprice = unitprice
self.otheruses = otheruses
self.unitsconsumed = unitsconsumed
def createAsset( self, pid, assetname, assettype ):
''' Adds a new incomesource object '''
# connect to database
db = Database()
db.open()
# create INSERT INTO query
assetname = common.getDbString( assetname )
assettype = common.getDbString( assettype )
query = '''INSERT INTO projectassets(pid,assetname,assettype) VALUES(%s,'%s','%s')''' % (pid,assetname,assettype)
# execute query
db.execUpdateQuery(query)
# close database connection
db.close()
# set asset attributes to saved values
self.name = common.getViewString( assetname )
self.type = common.getViewString( assettype )
self.pid = pid