def importProject(self, filename, projectid):
''' transfers project data '''
query = "SELECT ProjectID, ProjectName, DateOfDataCollection, Currency FROM TblProject WHERE ProjectID=%s" % projectid
db = AccessDB(filename)
db.open()
row = db.execSelectOneQuery( query )
if self.existsCorrespondingProject(row.ProjectID, row.ProjectName, row.DateOfDataCollection, row.Currency):
self.delCorrespondingProject(row.ProjectID, row.ProjectName, row.DateOfDataCollection, row.Currency)
projectname = row.ProjectName
startdate = row.DateOfDataCollection
currency = row.Currency
# old IHM does not have these
description = ""
enddate = row.DateOfDataCollection
project = self.addProject(projectname, startdate, enddate, description, currency)
self.logTransfer(project.pid, row.ProjectID, projectname, startdate, currency)
#transfer standard of living
self.transferStandardOfLiving(filename, row.ProjectID, project.pid)
#transfer diet
self.transferProjectDiet(filename, row.ProjectID, project.pid)
if not self.existsCurrency(row.Currency):
self.addCurrency(row.Currency, row.Currency, row.Currency)
self.transferHouseholds(filename, row.ProjectID, project.pid, startdate)
def transferStandardOfLiving(self, accessfilename, sourcepid, targetpid):
query = '''SELECT tblLkUpExpenses.ExpenseType, tblLkUpExpenses.Price, tblExpenses.Category, tblExpenses.LowerAgeM,
tblExpenses.UpperAgeM, tblExpenses.LowerAgeF, tblExpenses.UpperAgeF
FROM tblLkUpExpenses, tblExpenses
WHERE tblExpenses.ExpenseID = tblLkUpExpenses.ExpenseID AND tblExpenses.ProjectID=%s''' % sourcepid
db = AccessDB(accessfilename)
db.open()
rows = db.execSelectQuery( query )
project = self.getProject( targetpid )
for row in rows:
if (row.Category == "HH"):
scope = "Household"
gender = "All"
agetop = 0
agebottom = 0
else:
scope = "Person"
gender = "Male" if row.Category == "M" else "Female"
agetop = row.UpperAgeM if gender=="Male" else row.UpperAgeF
agebottom = row.LowerAgeM if gender=="Male" else row.LowerAgeF
item = row.ExpenseType
costperyear = row.Price
summary = "%s - %s - [%s to %s years]" % (item, gender, agebottom, agetop) if scope == "Person" else "%s - %s" % (item, scope)
project.addStandardOfLivingEntry(summary, scope, gender, agebottom, agetop, item, costperyear)
def transferProjectDiet(self, accessfilename, sourcepid, targetpid):
query = '''SELECT tblLkUpFoodVals.FoodName, tblFoodPurchase.PercentInDiet, tblFoodPurchase.PurchasePrice
FROM tblLkUpFoodVals, tblFoodPurchase
WHERE tblFoodPurchase.FoodUnitID = tblLkUpFoodVals.FoodUnitID AND tblFoodPurchase.ProjectID=%s''' % sourcepid
db = AccessDB(accessfilename)
db.open()
rows = db.execSelectQuery( query )
project = self.getProject( targetpid )
for row in rows:
fooditem = row.FoodName
percentage = row.PercentInDiet
priceperunit = row.PurchasePrice
unitofmeasure = "Kg"
project.addProjectDietItem(fooditem, unitofmeasure, percentage, priceperunit)
def transferHouseholdEmploymentIncome(self, accessfilename, sourcepid, sourcehhid, household ):
query = '''SELECT DISTINCT TblLkUpIncomeSources.IncomeSource AS incomesource, TblIncomeSourcesEmp.FoodTypePaid as foodtypepaid,
TblIncomeSourcesEmp.FoodUnit as unitofmeasure,
TblIncomeValsEmp.NFoodUnits AS unitspaid,
TblIncomeValsEmp.IncomeKcals AS incomekcal,
TblIncomeValsEmp.IncomeCash AS cashincome
FROM TblLkUpIncomeSources, TblIncomeSourcesEmp, TblIncomeValsEmp
WHERE TblLkUpIncomeSources.IncomeSourceID=TblIncomeSourcesEmp.IncomeSourceIDEmp
AND TblIncomeSourcesEmp.IncomeSourceIDEmp=TblIncomeValsEmp.IncomeSourceIDEmp
AND TblIncomeValsEmp.ProjectID=%s AND TblIncomeValsEmp.HHID=%s ''' % (sourcepid, sourcehhid)
print query
db = AccessDB(accessfilename)
db.open()
rows = db.execSelectQuery( query )
for row in rows:
incomesource = row.incomesource
foodtypepaid = row.foodtypepaid
unitofmeasure = row.unitofmeasure if row.unitofmeasure != None else ''
unitspaid= row.unitspaid if row.unitspaid != None else 0
incomekcal = row.incomekcal if row.incomekcal != None else 0
cashincome= row.cashincome if row.cashincome != None else 0
household.addEmploymentIncome(incomesource, foodtypepaid, unitofmeasure, unitspaid, incomekcal, cashincome )
db.close()
def transferHouseholdAIDIncome(self, accessfilename, sourcepid, sourcehhid, household ):
query = '''SELECT DISTINCT TblLkUpIncomeSources.IncomeSource AS sourceoftransfer, 'External' AS sourcetype,
TblIncomeValsAID.UnitValueCash * TblIncomeValsAID.TimesItemsReceived AS cashperyear,
'' AS foodtype,
TblIncomeSourcesAID.Unit AS unitofmeasure,
TblIncomeValsAID.UnitValueFood * TblIncomeValsAID.TimesItemsReceived AS unitsgiven,
0 AS unitsconsumed,
0 AS unitssold, 0 AS priceperunit
FROM TblLkUpIncomeSources, TblIncomeSourcesAID, TblIncomeValsAID
WHERE TblLkUpIncomeSources.IncomeSourceID=TblIncomeSourcesAID.IncomeSourceID
AND TblIncomeSourcesAID.IncomeSourceID=TblIncomeValsAID.IncomeSourceID
AND TblIncomeValsAID.ProjectID=%s AND TblIncomeValsAID.HHID=%s ''' % (sourcepid, sourcehhid)
print query
db = AccessDB(accessfilename)
db.open()
rows = db.execSelectQuery( query )
for row in rows:
sourcetype = row.sourcetype
sourceoftransfer = row.sourceoftransfer
cashperyear = row.cashperyear if row.cashperyear != None else 0
foodtype = row.foodtype if row.foodtype != None else ""
unitofmeasure = row.unitofmeasure if row.unitofmeasure != None else ""
unitsgiven = row.unitsgiven if row.unitsgiven != None else 0
unitsconsumed = row.unitsconsumed if row.unitsconsumed != None else 0
unitssold = row.unitssold if row.unitssold != None else 0
priceperunit = row.priceperunit if row.priceperunit != None else 0
household.addTransferIncome(sourcetype, sourceoftransfer, cashperyear, foodtype, unitofmeasure, unitsgiven, unitsconsumed, unitssold, priceperunit )
db.close()
def transferHouseholdWildfoodsIncome(self, accessfilename, sourcepid, sourcehhid, household ):
query = '''SELECT DISTINCT TblLkUpIncomeSources.IncomeSource, TblIncomeSourcesWF.Unit,
TblIncomeValsWF.UnitsSold + TblIncomeValsWF.UnitsConsumed AS UnitsProduced,
TblIncomeValsWF.UnitsSold, 0 AS PriceUnit, 0 AS OtherUses,
TblIncomeValsWF.UnitsConsumed
FROM TblLkUpIncomeSources, TblIncomeSourcesWF, TblIncomeValsWF
WHERE TblLkUpIncomeSources.IncomeSourceID=TblIncomeSourcesWF.IncomeSourceIDWF
AND TblIncomeSourcesWF.IncomeSourceIDWF=TblIncomeValsWF.IncomeSourceIDWF
AND TblIncomeValsWF.ProjectID=%s AND TblIncomeValsWF.HHID=%s ''' % (sourcepid, sourcehhid)
print query
db = AccessDB(accessfilename)
db.open()
rows = db.execSelectQuery( query )
for row in rows:
incomesource = row.IncomeSource
unitofmeasure = row.Unit
unitsproduced = row.UnitsProduced if row.UnitsProduced != None else 0
unitssold = row.UnitsSold if row.UnitsSold != None else 0
unitprice = row.PriceUnit if row.PriceUnit != None else 0
otheruses = row.OtherUses if row.OtherUses != None else 0
unitsconsumed = row.UnitsConsumed if row.UnitsConsumed != None else 0
unitsproduced = unitsconsumed if unitsproduced==0 and unitsconsumed != 0 else unitsproduced
household.addWildfoodsIncome(incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed )
db.close()
def getProjectsFromAccess(self, filename):
query = 'SELECT ProjectID, ProjectName, DateOfDataCollection FROM TblProject'
db = AccessDB(filename)
db.open()
rows = db.execSelectQuery( query )
db.close()
return rows
def transferHouseholdChars(self, accessfilename, sourcepid, sourcehhid, household, project ):
query = '''SELECT tblLkUpHHItem.ItemName, tblLkUpHHItem.Unit, tblHHItemValues.Value, tblHHItemValues.TrueFalse
FROM tblLkUpHHItem, tblHHItemValues
WHERE tblHHItemValues.ItemID = tblLkUpHHItem.ItemID AND tblHHItemValues.ProjectID=%s
AND tblHHItemValues.HHID=%s''' % (sourcepid, sourcehhid)
db = AccessDB(accessfilename)
db.open()
rows = db.execSelectQuery( query )
for row in rows:
chartype = "Household"
charname = row.ItemName
val = row.Value
valyesno = row.TrueFalse
if row.Unit != "Yes/No":
datatype = 3
charvalue = val
else:
datatype = 1
charvalue = "Yes" if str(valyesno) == "True" else "No"
if not self.existsGlobalCharacteristic(charname):
self.addGlobalCharacteristic(charname, chartype, datatype)
if not project.existsProjectCharacteristic(charname):
project.addProjectCharacteristic(charname, chartype, datatype)
household.addCharacteristic(charname, charvalue)
db.close()
def transferHouseholdAssets(self, accessfilename, sourcepid, sourcehhid, household ):
query = '''SELECT tblLkUpAssets.AssetCategory, tblLkUpAssets.AssetName, tblLkUpAssets.Unit, tblLkUpAssets.PriceUnit, TblAssetVals.Value
FROM TblAssetVals, tblLkUpAssets
WHERE TblAssetVals.AssetID = tblLkUpAssets.AssetID AND TblAssetVals.ProjectID=%s
AND TblAssetVals.HHID=%s''' % (sourcepid, sourcehhid)
db = AccessDB(accessfilename)
db.open()
rows = db.execSelectQuery( query )
for row in rows:
category = row.AssetCategory
assettype = row.AssetName
unitofmeasure = row.Unit
costperunit = row.PriceUnit
numunits = row.Value
household.addAsset(category, assettype, unitofmeasure, costperunit, numunits )
db.close()
def transferHouseholdMembers(self, accessfilename, sourcepid, sourcehhid, household, project ):
query = "SELECT PersonID, Sex, Age FROM TblDemog WHERE ProjectID=%s AND HHID=%s " % (sourcepid, sourcehhid)
db = AccessDB(accessfilename)
db.open()
rows = db.execSelectQuery( query )
# old IHM does not have these fields
headhousehold = "No"
education = ""
periodaway = 0
reason = ""
whereto = ""
# this year
thisyear = date.today().year
for row in rows:
yearofbirth = thisyear - int(row.Age)
sex = "Male" if row.Sex == "M" else "Female"
member = household.addMember(row.PersonID, yearofbirth, headhousehold, sex, education, periodaway, reason, whereto)
self.transferHouseholdMemberChars(accessfilename, sourcepid, sourcehhid, row.PersonID, member, project)
db.close()
def transferHouseholds(self, accessfilename, sourcepid, targetpid, startdate):
query = "SELECT HHID, HHName, HHRealName FROM TblHouseholds WHERE ProjectID=%s" % sourcepid
db = AccessDB(accessfilename)
db.open()
rows = db.execSelectQuery( query )
project = self.getProject( targetpid )
for row in rows:
household = project.addHousehold(row.HHName, row.HHRealName, startdate)
self.transferHouseholdChars( accessfilename, sourcepid, row.HHID, household , project)
self.transferHouseholdMembers( accessfilename, sourcepid, row.HHID, household, project )
self.transferHouseholdAssets( accessfilename, sourcepid, row.HHID, household )
self.transferHouseholdCropIncome( accessfilename, sourcepid, row.HHID, household )
self.transferHouseholdLivestockIncome( accessfilename, sourcepid, row.HHID, household )
self.transferHouseholdWildfoodsIncome( accessfilename, sourcepid, row.HHID, household )
self.transferHouseholdGiftsIncome( accessfilename, sourcepid, row.HHID, household )
self.transferHouseholdAIDIncome( accessfilename, sourcepid, row.HHID, household )
self.transferHouseholdEmploymentIncome( accessfilename, sourcepid, row.HHID, household )
self.transferHouseholdExpenditure( accessfilename, sourcepid, row.HHID, household )
db.close()
def transferHouseholdMemberChars(self, accessfilename, sourcepid, sourcehhid, personid, member, project):
query = '''SELECT tblLkUpPersonalChars.CharType, tblPersonValsNew.DataValue, tblPersonValsNew.TrueFalse,
tblPersonValsNew.PercentTimeAway
FROM tblLkUpPersonalChars, tblPersonValsNew
WHERE tblPersonValsNew.CharID = tblLkUpPersonalChars.CharID AND tblPersonValsNew.ProjectID=%s
AND tblPersonValsNew.HHID=%s AND tblPersonValsNew.PersonID=%s ''' % (sourcepid, sourcehhid, personid)
db = AccessDB(accessfilename)
db.open()
rows = db.execSelectQuery( query )
for row in rows:
chartype = "Personal"
charname = row.CharType
valdata = row.DataValue
valpercent = row.PercentTimeAway
valyesno = row.TrueFalse
if valdata != None and str(valyesno) != "True":
datatype = 3
charvalue = valdata
elif valpercent != None and str(valyesno) != "True":
datatype = 3
charvalue = valpercent
else:
datatype = 1
charvalue = "Yes" if str(valyesno) == "True" else "No"
if not self.existsGlobalCharacteristic(charname):
self.addGlobalCharacteristic(charname, chartype, datatype)
if not project.existsProjectCharacteristic(charname):
project.addProjectCharacteristic(charname, chartype, datatype)
member.addCharacteristic(charname, charvalue)
db.close()
