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 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 getProjectsFromAccess(self, filename):
query = 'SELECT ProjectID, ProjectName, DateOfDataCollection FROM TblProject'
db = AccessDB(filename)
db.open()
rows = db.execSelectQuery( query )
db.close()
return rows
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 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()
