def getProjectsMatching(self, pid="", ptitle=""):
SQLcondition = ""
if ( pid != "" ):
SQLcondition = " WHERE pid=%s" % pid
if ( ptitle != "" ):
if ( SQLcondition == "" ):
SQLcondition = " WHERE projectname LIKE '%" + "%s" % ( ptitle ) + "%'"
else:
SQLcondition = SQLcondition + " OR projectname LIKE '%" + "%s" % ( ptitle ) + "%'"
query = ''' SELECT pid FROM projects%s''' % ( SQLcondition )
database = Database()
database.open()
rows = database.execSelectQuery( query )
database.close()
projects = []
for row in rows:
pid = row[0]
project = Project(pid)
projects.append( project )
return projects
def exportHouseholds(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, householdname, dateofcollection FROM households
WHERE pid=%s''' % project.pid
households = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for household in households:
householdline = '''INSERT INTO households(pid,hhid,householdname,dateofcollection)
VALUES({pid},%s, '%s', '%s')<endl>\n''' % (household[0], household[1], household[2])
ihmFile.write(householdline)
ihmFile.close()
self.exportHouseholdCharacteristics(project, filename)
self.exportHouseholdAssets(project, filename)
self.exportHouseholdExpenditure(project, filename)
self.exportHouseholdCropIncome(project, filename)
self.exportHouseholdLivestockIncome(project, filename)
self.exportHouseholdWildfoodsIncome(project, filename)
self.exportHouseholdEmploymentIncome(project, filename)
self.exportHouseholdTransfersIncome(project, filename)
self.exportHouseholdMembers(project, filename)
class GlobalHouseholdCharacteristicsManager:
def __init__(self):
self.database = Database()
def getGlobalHouseholdCharacteristic(self, charid=0, charname=""):
char = GlobalHouseholdCharacteristic(charid, charname)
return char
def addGlobalHouseholdCharacteristic(self, charname, datatype):
char = GlobalHouseholdCharacteristic(0, charname, datatype)
return char
def editGlobalHouseholdCharacteristic(self, charid, charname, datatype):
char = GlobalHouseholdCharacteristic(charid)
char.setData(charname, datatype)
def delGlobalHouseholdCharacteristic(self, charid="", charname=""):
query = "DELETE FROM globalhouseholdcharacteristics WHERE id=%i OR characteristic='%s' " % (charid, charname)
self.database.open()
self.database.execUpdateQuery(query)
self.database.close()
def getGlobalHouseholdCharacteristics(self):
query = "SELECT id FROM globalhouseholdcharacteristics"
self.database.open()
rows = self.database.execSelectQuery(query)
self.database.close()
chars = []
for row in rows:
charid = row[0]
char = GlobalHouseholdCharacteristic(charid)
chars.append(char)
return chars
def test_execUpdateQuery(self):
self.helper.setup_clean_db()
database = Database()
database.open()
database.execUpdateQuery("""
insert into projects
(projectname, startdate, enddate, description, currency)
values
('test', '2012-06-04', '2013-07-03', 'a simple test', 'GBP')""")
query = 'select * from projects'
self.assertEqual([(2, u'test', datetime.date(2012, 6, 4),
datetime.date(2013, 7, 3), u'a simple test', u'GBP')],
database.execSelectQuery(query))
database.close()
def getGlobalPersonCharacteristics(self):
query = "SELECT id FROM globalpersonalcharacteristics"
database = Database()
atabase.open()
rows = database.execSelectQuery( query )
database.close()
chars = []
for row in rows:
charid = row[0]
char = GlobalPersonCharacteristic(charid)
chars.append( char )
return chars
def getProjects(self):
query = "SELECT pid FROM projects"
database = Database()
database.open()
rows = database.execSelectQuery( query )
database.close()
projects = []
for row in rows:
pid = row[0]
project = Project(pid)
projects.append( project )
return projects
def exportProjectIncomeSources(self, project, filename):
database = Database()
database.open()
query = '''SELECT incomesource, incometype FROM projectincomesources
WHERE pid=%s''' % project.pid
incomes = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for inc in incomes:
incomeline = '''INSERT INTO projectincomesources(pid,incomesource,incometype)
VALUES({pid},'%s','%s')<endl>\n''' % (inc[0], inc[1])
ihmFile.write(incomeline)
ihmFile.close()
def delCorrespondingIhmProject(self, projectname, startdate, currency):
''' Delete if the project was transfered before '''
query = '''SELECT projects.pid FROM projects, transferlog WHERE projects.pid=transferlog.pid
AND transferlog.projectname='%s' AND transferlog.datecollected='%s'
AND transferlog.currency='%s' ''' % (projectname, startdate, currency)
db = Database()
db.open()
records = db.execSelectQuery( query )
for record in records:
pid = record[0]
query = '''DELETE FROM projects WHERE pid=%s''' % pid
db.execUpdateQuery( query )
db.close()
def existsCorrespondingIhmProject(self, projectname, startdate, currency):
''' Checks if the project was transfered before '''
query = '''SELECT projects.pid FROM projects, transferlog WHERE projects.pid=transferlog.pid
AND transferlog.projectname='%s' AND transferlog.datecollected='%s'
AND transferlog.currency='%s' ''' % (projectname, startdate, currency)
db = Database()
db.open()
records = db.execSelectQuery( query )
exists = False
if len(records) == 1:
exists = True
db.close()
return exists
def exportProjectAssets(self, project, filename):
database = Database()
database.open()
query = '''SELECT assetname, assettype FROM projectassets
WHERE pid=%s''' % project.pid
assets = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for asset in assets:
assetline = '''INSERT INTO projectassets(pid,assetname,assettype)
VALUES({pid},'%s','%s')<endl>\n''' % (asset[0], asset[1])
ihmFile.write(assetline)
ihmFile.close()
def exportHouseholdCropIncome(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed FROM cropincome
WHERE pid=%s''' % project.pid
crops = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for crop in crops:
cropline = '''INSERT INTO cropincome(pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed)
VALUES({pid},%s,'%s','%s',%s,%s,%s,%s,%s)<endl>\n''' % (crop[0], crop[1], crop[2], crop[3], crop[4], crop[5], crop[6], crop[7])
ihmFile.write(cropline)
ihmFile.close()
def exportProjectStandardOfLiving(self, project, filename):
database = Database()
database.open()
query = '''SELECT summary, scope, gender, agebottom, agetop, item, costperyear FROM standardofliving
WHERE pid=%s''' % project.pid
stdLvs = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for stdLv in stdLvs:
stdLvline = '''INSERT INTO standardofliving (pid, summary, scope, gender, agebottom, agetop, item, costperyear )
VALUES({pid},'%s','%s','%s',%s,%s,'%s',%s)<endl>\n''' % (stdLv[0], stdLv[1], stdLv[2], stdLv[3], stdLv[4], stdLv[5], stdLv[6])
ihmFile.write(stdLvline)
ihmFile.close()
def exportHouseholdEmploymentIncome(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, incomesource, foodtypepaid, unitofmeasure, unitspaid, incomekcal, cashincome FROM employmentincome
WHERE pid=%s''' % project.pid
items = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for item in items:
empline = '''INSERT INTO employmentincome(pid, hhid, incomesource, foodtypepaid, unitofmeasure, unitspaid, incomekcal, cashincome)
VALUES({pid},%s,'%s','%s','%s',%s,%s,%s)<endl>\n''' % (item[0], item[1], item[2], item[3], item[4], item[5], item[6])
ihmFile.write(empline)
ihmFile.close()
def exportHouseholdWildfoodsIncome(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed FROM wildfoods
WHERE pid=%s''' % project.pid
items = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for item in items:
wfline = '''INSERT INTO wildfoods(pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed)
VALUES({pid},%s,'%s','%s',%s,%s,%s,%s,%s)<endl>\n''' % (item[0], item[1], item[2], item[3], item[4], item[5], item[6], item[7])
ihmFile.write(wfline)
ihmFile.close()
def exportProjectDiet(self, project, filename):
database = Database()
database.open()
query = '''SELECT fooditem, unitofmeasure, percentage, priceperunit FROM diet
WHERE pid=%s''' % project.pid
diets = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for diet in diets:
dietline = '''INSERT INTO diet (pid, fooditem,unitofmeasure,percentage, priceperunit )
VALUES({pid},'%s','%s',%s,%s)<endl>\n''' % (diet[0], diet[1], diet[2], diet[3])
ihmFile.write(dietline)
ihmFile.close()
def exportHouseholdAssets(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, assetcategory, assettype, unitofmeasure, unitcost, totalunits FROM assets
WHERE pid=%s''' % project.pid
assets = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for asset in assets:
assetline = '''INSERT INTO assets (pid, hhid, assetcategory, assettype, unitofmeasure, unitcost, totalunits )
VALUES({pid},%s,'%s','%s','%s',%s,%s)<endl>\n''' % (asset[0], asset[1], asset[2], asset[3], asset[4], asset[5])
ihmFile.write(assetline)
ihmFile.close()
def exportHouseholdCharacteristics(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, characteristic, charvalue FROM householdcharacteristics
WHERE pid=%s''' % project.pid
chars = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for char in chars:
charline = '''INSERT INTO householdcharacteristics (pid,hhid, characteristic, charvalue )
VALUES({pid},%s,'%s','%s')<endl>\n''' % (char[0], char[1], char[2])
ihmFile.write(charline)
ihmFile.close()
def exportProjectCharacteristics(self, project, filename):
database = Database()
database.open()
query = '''SELECT characteristic, chartype, datatype FROM projectcharacteristics
WHERE pid=%s''' % project.pid
chars = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for char in chars:
charline = '''INSERT INTO projectcharacteristics (pid, characteristic, chartype, datatype )
VALUES({pid},'%s','%s',%s)<endl>\n''' % (char[0], char[1], char[2])
ihmFile.write(charline)
ihmFile.close()
def exportHouseholdTransfersIncome(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, sourcetype, sourceoftransfer, cashperyear, foodtype, unitofmeasure, unitsgiven,
unitsconsumed, unitssold, priceperunit FROM transfers
WHERE pid=%s''' % project.pid
items = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for item in items:
transline = '''INSERT INTO transfers(pid, hhid, sourcetype, sourceoftransfer, cashperyear, foodtype, unitofmeasure, unitsgiven,
unitsconsumed, unitssold, priceperunit) VALUES({pid},%s,'%s','%s',%s,'%s','%s',%s,
%s,%s,%s)<endl>\n''' % (item[0], item[1], item[2], item[3], item[4], item[5], item[6], item[7], item[8], item[9])
ihmFile.write(transline)
ihmFile.close()
def exportHouseholdMembers(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, personid, yearofbirth, headofhousehold, sex, education, periodaway, reason, whereto
FROM householdmembers
WHERE pid=%s''' % project.pid
members = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for member in members:
memberline = '''INSERT INTO householdmembers(pid, hhid, personid, yearofbirth, headofhousehold, sex, education, periodaway, reason, whereto)
VALUES({pid},%s, '%s', %s,'%s','%s','%s',%s,'%s','%s')<endl>\n''' % (member[0], member[1], member[2], member[3],
member[4], member[5], member[6], member[7], member[8])
ihmFile.write(memberline)
ihmFile.close()
self.exportPersonalCharacteristics(project, filename)
class CurrencyManager( object ):
"""Manages currencies.
Allows adding, editing, deleting and retrieval of currencies.
"""
def __init__( self ) :
self.database = Database()
self.currencies = None
self.currency_names = None
self.getCurrencies()
def existsCurrency(self, name):
"""Check if currency 'name' exists
"""
currency = Currency(currencyname=name)
if not currency.name :
return False
else:
return True
def getCurrencyByID(self, currencyid):
"""return currency currencyid from the database
(returns by currency id)
"""
currency = Currency(currencyid)
if not currency.name :
raise CurrencyError(
"Can't get Currency id %r which does not exist" % currencyid )
return currency
def getCurrencyByName(self, name):
"""return currency currencyname from the database
(returns by currency name)
"""
currency = Currency(currencyname=name)
if not currency.name :
raise CurrencyError(
"Can't get Currency '%r' which does not exist" % name )
return currency
def addCurrency(self, currencyname, abbreviation, symbol ):
"""Adds currency 'currencyname' to the currencies database.
"""
currency = Currency( 0 , currencyname, abbreviation, symbol )
self.getCurrencies() # update currency list
return currency
def editCurrency(self, currencyid, currencyname, abbreviation, symbol ):
"""Edit currency 'currencyid' providing new name, abbreviation, symbol.
"""
currency = Currency(currencyid)
if not currency.name :
raise CurrencyError(
"Can't find Currency id %r which does not exist" % currencyid )
currency.editData( currencyname, abbreviation, symbol )
self.getCurrencies() # update currency list
return
def delCurrency(self, currencyid):
"""Delete currency 'currencyid' from the database.
"""
currency = Currency(currencyid)
if not currency.name :
raise CurrencyError(
"Can't delete Currency id %r which does not exist" %currencyid )
query = "DELETE FROM currencies WHERE id=%i " % ( currencyid )
self.database.open()
self.database.execUpdateQuery( query )
self.database.close()
self.getCurrencies() # update currency list
return
def getCurrencies(self):
"""Return a list of currencies stored in the database
At same time set self.currencies to this list.
"""
query = "SELECT id FROM currencies"
self.database.open()
rows = self.database.execSelectQuery( query )
self.database.close()
self.currencies = list()
for row in rows:
curr_id = row[0]
currency = Currency(curr_id)
self.currencies.append( currency )
self.currency_names = [ x.name for x in self.currencies ]
return self.currencies
