def executeQuery(self,query):
'''Execute queries under adult equivalent calculations '''
databaseConnector = Database()
databaseConnector.open()
result = databaseConnector.execSelectQuery( query )
databaseConnector.close()
return result
def executeSelectQuery(self,query):
'''Run Select Query'''
dbconnector = Database()
dbconnector.open()
recset = dbconnector.execSelectQuery(query)
dbconnector.close()
return recset
def executeQuery(self,query):
'''run various select queries'''
dbconnector = Database()
dbconnector.open()
recordset = dbconnector.execSelectQuery(query)
dbconnector.close()
return recordset
def getincomeSources(self,query):
'''run various select queries'''
dbconnector = Database()
dbconnector.open()
print query
recordset = dbconnector.execSelectQuery(query)
dbconnector.close()
return recordset
def getReportHouseholdIDs(self,query):
reporthouseholdIDs=[]
databaseConnector = Database()
if query !='':
databaseConnector.open()
reporthouseholdIDs = databaseConnector.execSelectQuery( query )
databaseConnector.close()
return reporthouseholdIDs
def checkRecordExistence(self,testquery):
'''Test if a record with some given primary key already exists'''
database = Database()
database.open()
testrecset = database.execSelectQuery(testquery)
numrows =0
for row in testrecset:
numrows = numrows + 1
database.open()
return numrows
def test_execSelectQuery(self):
self.helper.setup_clean_db()
self.helper.execute_instruction("""
insert into projects
(projectname, startdate, enddate, description, currency)
values
('test', 2012-06-04, 2013-07-03, 'a simple test', 'GBP')""")
database = Database()
query = 'select * from projects'
database.open()
self.assertEqual([(2, u'test', None, None, u'a simple test', u'GBP')],
database.execSelectQuery(query))
database.close()
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()
class DataEntrySheets:
def __init__(self,projectid):
self.database = Database()
self.pcharstable = 'p' + str(projectid) +'PersonalCharacteristics'
self.hcharstable = 'p' + str(projectid) +'HouseholdCharacteristics'
self.pid = projectid
self.config = Config.dbinfo().copy()
def getPersonalCharacteristics(self):
query = '''SELECT characteristic, datatype FROM projectcharacteristics WHERE pid=%s and chartype='Personal' ''' %(self.pid)
self.database.open()
pchars = self.database.execSelectQuery(query)
self.database.close()
return pchars
def getHouseholdCharacteristics(self):
query = '''SELECT characteristic, datatype FROM projectcharacteristics WHERE pid=%s and chartype='Household' ''' %(self.pid)
self.database.open()
hchars = self.database.execSelectQuery(query)
self.database.close()
return hchars
# Income sources
def buildQueries(self,incometype):
query = '''SELECT incomesource FROM projectincomesources WHERE incometype='%s' AND pid=%s''' % (incometype,self.pid)
return query
def getProjectCropsFoods(self,incometype,projectincomes):
#incomesourcelist = ','.join(projectincomes)
incomes = []
recordset = []
if len(projectincomes)!= 0:
for income in projectincomes:
tempname = "'" + income[0] + "'"
incomes.append(tempname)
incomesourcelist = ','.join(incomes)
query = '''SELECT name, unitofmeasure FROM setup_foods_crops WHERE category='%s' AND name in (%s)''' % (incometype,incomesourcelist)
print query
recordset = self.getincomeSources(query)
return recordset
def getincomeSources(self,query):
'''run various select queries'''
dbconnector = Database()
dbconnector.open()
print query
recordset = dbconnector.execSelectQuery(query)
dbconnector.close()
return recordset
def getCropsFoodsIncomeSourceDetails(self,incometype):
'''Get Income-source Names and Units of Measure for Crops, Livestocks, and Wildfoods, to be displayed in data entry spreadsheet'''
incomesquery = self.buildQueries(incometype)
projectincomes = self.getincomeSources(incomesquery)
incomesourcedetails = self.getProjectCropsFoods(incometype,projectincomes)
return incomesourcedetails
def getprojetAssets(self):
query = '''SELECT assettype, assetname FROM projectassets WHERE pid=%s ORDER BY assettype, assetname''' % self.pid
self.database.open()
assets = self.database.execSelectQuery(query)
self.database.close()
return assets
def getProjectSocialTransfers(self):
query = '''SELECT incomesource FROM projectincomesources WHERE incometype ='Social Transfers' AND pid=%s ORDER BY incomesource''' % self.pid
self.database.open()
transfers = self.database.execSelectQuery(query)
self.database.close()
return transfers
def getProjectOfficialTransfers(self):
query = '''SELECT incomesource FROM projectincomesources WHERE incometype ='Official Transfers' AND pid=%s ORDER BY incomesource''' % self.pid
self.database.open()
transfers = self.database.execSelectQuery(query)
self.database.close()
return transfers
def populateSocialTranfers(self,book,style1,style2,row):
recordset = self.getProjectSocialTransfers()
sheet = book.get_sheet(3)
col = 0
#set section Headings
sheet.write(row, col, "SocialTransfer", style1)
row = row + 1
transferheadings = ["TransferSource","CashPerYear","FoodType","Unit","UnitsConsumed","UnitsSold","PricePerUnit"]
for itemheader in transferheadings:
sheet.write(row, col, itemheader, style2)
col = col + 1
row = row +1
#write transfer sources
col = 0
for rec in recordset:
celvalue = rec[col]
sheet.write(row, col, celvalue)
row = row + 1
row = row + 4 # set space between Income source type sections
return row
def populateOfficialTranfers(self,book,style1,style2,row):
recordset = self.getProjectOfficialTransfers()
sheet = book.get_sheet(3)
col = 0
#set section Headings
sheet.write(row, col, "TransferFromOrganisations", style1)
row = row + 1
transferheadings = ["TransferSource","CashPerYear","FoodType","Unit","UnitsConsumed","UnitsSold","PricePerUnit"]
for itemheader in transferheadings:
sheet.write(row, col, itemheader, style2)
col = col + 1
row = row +1
#write transfer sources
col = 0
for rec in recordset:
celvalue = rec[col]
sheet.write(row, col, celvalue)
row = row + 1
row = row + 4 # set space between Income source type sections
return row
def getAssetUnitOfMeasure(self,unitfld, tblname,assetfld,assettype):
unitofmeasure =""
query = '''SELECT %s FROM %s WHERE %s='%s' ''' % (unitfld, tblname,assetfld,assettype)
self.database.open()
assets = self.database.execSelectQuery(query)
for item in assets:
unitofmeasure = item[0]
self.database.close()
return unitofmeasure
def getAssetList(self):
assets = []
assets = self.getprojetAssets()
finalassetlist = []
for row in assets:
templist = []
for item in row:
templist.append(item)
if templist [0] =='Crops':
tblname, assetfld, unitfld = "setup_crops", "foodtype", "measuringunit"
elif templist [0] =='Land':
tblname, assetfld, unitfld = "setup_landtypes", "landtype", "unitofmeasure"
elif templist [0] =='Livestock':
tblname, assetfld, unitfld = "setup_livestock", "incomesource", "unitofmeasure"
elif templist [0] =='Tradable Goods':
tblname, assetfld, unitfld = "setup_tradablegoods", "tradablegoodtype", "unitofmeasure"
elif templist [0] =='Trees':
tblname, assetfld, unitfld = "setup_treetypes", "treetype", "measuringunit"
unitofmeasure = self.getAssetUnitOfMeasure(unitfld, tblname,assetfld,templist[1])
templist.append(unitofmeasure)
listtuple = tuple(templist)
finalassetlist.append(listtuple)
return finalassetlist
def addProjectAssets(self,book,style1,style2):
''' Populate Sheet 3 with Assets for a selected project'''
sheet3 = book.add_sheet("New Assets")
headings = ["Category","Type","Unit","UnitCost","NumberOfUnits"]
col = 0
row = 0
sheet3.write(row, col, "Assets", style1)
row = row + 1
for itemheader in headings:
sheet3.write(row, col, itemheader, style2)
col = col + 1
row = row +1
#set column width for sheet4 - Asset Columns
for i in range(0,5):
sheet3.col(i).width = 6000
def populateProjectAssetssection(self,book,style1,style2,row):
headings = ["Category","Type","Unit","UnitCost","TotalUnits"]
col = 0
sheet = book.get_sheet(3)
for itemheader in headings:
sheet.write(row, col, itemheader, style2)
col = col + 1
row = row +1
recordset = self.getAssetList()
for rec in recordset:
col = 0
assettype = rec[0]
assetname = rec[1]
unitofmeasure = rec[2]
sheet.write(row, col, assettype)
col = col + 1
sheet.write(row, col, assetname)
col = col + 1
sheet.write(row, col, unitofmeasure)
row = row + 1
return row
def writeFoodsCropsHeaders(self,sectionheading,headerrow,book,style1,style2):
headings = ["Name","Unit","UnitsProduced","UnitsSold","UnitPrice","OtherUses","UnitsConsumed"]
col = 0
sheet = book.get_sheet(3)
#if sectionheading=='Livestock':
sectionheading = sectionheading + '-C'
sheet.write(headerrow, col, sectionheading,style1)
headerrow = headerrow +1
for itemheader in headings:
sheet.write(headerrow, col, itemheader, style2)
col = col + 1
headerrow = headerrow +1
return headerrow
def populateFoodsCropsSections(self,book,style1,style2,row):
''' Populate data Entry Sheet with Crops,Livestock,Wildfoods for a selected project'''
incometypes = ['Crops','Livestock','Wildfoods']
col = 0
for incometype in incometypes:
#set section Headings
row = self.writeFoodsCropsHeaders(incometype,row,book,style1,style2)
#get incomesource details and write in spreadsheet
recordset = self.getCropsFoodsIncomeSourceDetails(incometype.lower())
sheet = book.get_sheet(3)
for rec in recordset:
for col in range (0,2):
celvalue = rec[col]
sheet.write(row, col, celvalue)
row = row + 1
row = row + 4 # set space between Income source type sections
return row
def writeEmploymentSectionHeaders(self,headerrow,book,style1,style2):
headings = ["Type","FoodPaid","Unit","UnitsPaid","Kcals","CashIncome"]
col = 0
sheet = book.get_sheet(3)
sheet.write(headerrow, 0, "Employment", style1)
headerrow = headerrow +1
for itemheader in headings:
sheet.write(headerrow, col, itemheader, style2)
col = col + 1
headerrow = headerrow +1
return headerrow
def getProjectEmploymentTypes(self):
incometype = 'employment'
query = self.buildQueries(incometype)
recordset = self.getincomeSources(query)
return recordset
def populateEmployemntDetails(self,row,book,style1,style2):
row = self.writeEmploymentSectionHeaders(row,book,style1,style2)
recordset = self.getProjectEmploymentTypes()
sheet = book.get_sheet(3)
col = 0
for rec in recordset:
celvalue = rec[col]
sheet.write(row, col, celvalue)
row = row + 1
row = row + 4 # set space between Income source type sections
return row
def populateIncomeSourcesSheet(self,book,style1,style2):
''' Populate Sheet 3 with Headers for ebtry of new income sources users may find during field visits'''
#Income Sources
sheet2 = book.add_sheet("New Income Sources")
incometypes = ['Crops','Livestock','Wildfoods']
col = 0
headerrow = 0
for incometype in incometypes:
#set section Headings
headings = ["Name","Unit","UnitsProduced","UnitsSold","UnitPrice","OtherUses","UnitsConsumed"]
col = 0
sheet2.write(headerrow, 0,incometype , style1)
headerrow = headerrow +1
for itemheader in headings:
sheet2.write(headerrow, col, itemheader, style2)
col = col + 1
headerrow = headerrow +11
#Write Employment Headings
employmentheadings = ["Type","FoodPaid","Unit","UnitsPaid","Kcals","CashIncome"]
col = 0
sheet2.write(headerrow, 0, "Employment", style1)
headerrow = headerrow +1
for itemheader in employmentheadings:
sheet2.write(headerrow, col, itemheader, style2)
col = col + 1
headerrow = headerrow +11
#Write Transfer Headers
incometypes = ['Social Transfers','Official Transfers']
col = 0
for incometype in incometypes:
#set section Headings
transferheadings = ["TransferSource","CashPerYear","FoodType","Unit","UnitsConsumed","UnitsSold","PricePerUnit"]
col = 0
sheet2.write(headerrow, 0,incometype , style1)
headerrow = headerrow +1
for itemheader in transferheadings:
sheet2.write(headerrow, col, itemheader, style2)
col = col + 1
headerrow = headerrow +11
sheet2.write(headerrow, 0, "")
#set column width for sheet1
for i in range(0,7):
sheet2.col(i).width = 6000
def writeDataSheets(self):
book = Workbook(encoding="utf-8")
#set style for headers
style1 = easyxf('font: name Arial;''font: bold True;')
style2 = easyxf('font: name Arial;''font: colour ocean_blue;''font: bold True;''border: left thick, top thick, right thick, bottom thick')
style3 = easyxf('font: name Arial;''font: colour green;''font: bold True;''border: left thick, top thick, right thick, bottom thick')
#create sheet for entering project households
sheettitle = "%s" % self.pid
sheet1 = book.add_sheet(sheettitle)
sheet1.write(0, 0, "Project Households", style1)
sheet1.write(1, 0, "HouseholdNumber", style2)
sheet1.write(1, 1, "HouseholdName", style2)
sheet1.write(1, 2, "DateVisited", style2)
#set column width for sheet1
for i in range(0,3):
sheet1.col(i).width = 6000
self.populateIncomeSourcesSheet(book,style1,style2)
self.addProjectAssets(book,style1,style2)
#Basic Details for Household Members
sheet4 = book.add_sheet("HouseholdID")
sheet4.write(1, 0, "HouseholdMembers", style1)
sheet4.write(2, 0, "Sex", style2)
sheet4.write(2, 1, "Age", style2)
sheet4.write(2, 2, "YearofBirth", style2)
sheet4.write(2, 3, "HouseholdHead", style2)
sheet4.write(2, 4, "PeriodAway", style2)
#get personal and household characteristics, configured for current project
pchars = self.getPersonalCharacteristics()
hchars = self.getHouseholdCharacteristics()
#section for extended personal characteristics
col = 0
sheet4.write(8, 0, "PersonalCharacteristics", style1)
sheet4.write(9, col, 'personid', style2)
sheet4.write(10, col, 'String', style3)
col = col + 1
for char in pchars:
value = char[0]
chartype = char[1]
vartype =''
if value!='pid' and value !='hhid':
if chartype == 1:
vartype ='Yes/No'
elif chartype == 2:
vartype ='Integer'
elif chartype == 3:
vartype ='String'
elif chartype == 4:
vartype ='Double'
sheet4.write(9, col, value, style2)
sheet4.write(10, col, vartype, style3)
col = col + 1
#section for household characteristics
sheet4.write(17, 0, "HouseholdCharacteristics", style1)
col = 0
for char in hchars:
value = char[0]
chartype = char[1]
vartype =''
if value!='pid' and value !='hhid':
if chartype == 1:
vartype ='Yes/No'
elif chartype == 2:
vartype ='Integer'
elif chartype == 3:
vartype ='String'
elif chartype == 4:
vartype ='Double'
sheet4.write(18, col, value, style2)
sheet4.write(19, col, vartype, style3)
col = col + 1
headerrow = 25
itemrow = 26
#Assets
sheet4.write(headerrow, 0, "Assets", style1)
headerrow = headerrow + 1
headerrow = self.populateProjectAssetssection(book,style1,style2,headerrow)
#Expenditure
headerrow = headerrow + 5
sheet4.write(headerrow, 0, "Expenditure", style1)
headerrow = headerrow + 1
sheet4.write(headerrow, 0, "Type", style2)
sheet4.write(headerrow, 1, "Unit", style2)
sheet4.write(headerrow, 2, "KCalPerUnit", style2)
sheet4.write(headerrow, 3, "UnitCost", style2)
sheet4.write(headerrow, 4, "Units", style2)
#Crop, Livestock, and Wildfood Income
headerrow = headerrow + 11
headerrow = self.populateFoodsCropsSections(book,style1,style2,headerrow)
#Employment
headerrow = self.populateEmployemntDetails(headerrow,book,style1,style2)
#Social Transfers
headerrow = self.populateSocialTranfers(book,style1,style2,headerrow)
#Transfers from Organisations
headerrow = self.populateOfficialTranfers(book,style1,style2,headerrow)
#set column width for sheet2
for i in range(0,7):
sheet4.col(i).width = 6000
folder = "inputs/"
filename = folder + "dataEntrySheet-ProjectID-" + str(self.pid) + ".xls"
book.save(filename)
completionmessage = '''Template Saved As open-ihm/''' + str(filename) +'''\n\nClick OK to open the spreadsheet. This may take a few seconds. '''
QtGui.QMessageBox.information(None, 'Data Entry Template', completionmessage)
#
os.system(os.path.curdir + "\\inputs\\dataEntrySheet-ProjectID-" + str(self.pid) + ".xls")
class DataEntrySheets:
def __init__(self,projectid):
self.database = Database()
self.pcharstable = 'p' + str(projectid) +'personalcharacteristics'
self.hcharstable = 'p' + str(projectid) +'householdcharacteristics'
self.pid = projectid
self.config = Config.dbinfo().copy()
def getPersonalCharacteristics(self):
query = '''SHOW columns FROM %s''' %(self.pcharstable)
self.database.open()
pchars = self.database.execSelectQuery(query)
self.database.close()
return pchars
def getHouseholdCharacteristics(self):
query = '''SHOW columns FROM %s''' %(self.hcharstable)
self.database.open()
hchars = self.database.execSelectQuery(query)
self.database.close()
return hchars
def buildQueries(self,incometype):
'''Build queries for getting project income sources'''
query = '''SELECT * FROM projectincomesources WHERE incometype='%s' ''' % incometype
return query
def getincomeSources(self,query):
'''run income source select queries'''
dbconnector = Database()
dbconnector.open()
recordset = dbconnector.execSelectQuery(query)
dbconnector.close()
return recordset
def writeDataSheets(self):
book = Workbook(encoding="utf-8")
#set style for headers
style1 = easyxf('font: name Arial;''font: bold True;')
style2 = easyxf('font: name Arial;''font: colour ocean_blue;''font: bold True;''border: left thick, top thick, right thick, bottom thick')
style3 = easyxf('font: name Arial;''font: colour green;''font: bold True;''border: left thick, top thick, right thick, bottom thick')
#create sheet for entering project households
#projectid = self.pid
sheettitle = "%s" % self.pid
sheet1 = book.add_sheet(sheettitle)
sheet1.write(0, 0, "Project Households", style1)
sheet1.write(1, 0, "HouseholdNumber", style2)
sheet1.write(1, 1, "HouseholdName", style2)
sheet1.write(1, 2, "DateVisited", style2)
#set column width for sheet1
for i in range(0,3):
sheet1.col(i).width = 6000
#Basic Details for Household Members
sheet2 = book.add_sheet("Template")
sheet2.write(1, 0, "HouseholdMembers", style1)
sheet2.write(2, 0, "Sex", style2)
sheet2.write(2, 1, "Age", style2)
sheet2.write(2, 2, "YearofBirth", style2)
sheet2.write(2, 3, "HouseholdHead", style2)
#Basic Details for Household Members
sheet3 = book.add_sheet("Income Sources")
sheet3.write(1, 0, "Crop Types", style1)
sheet3.write(1, 2, "Employment Types", style2)
sheet3.write(1, 4, "Livestock Types", style2)
sheet3.write(1, 6, "Transfer Types", style2)
sheet3.write(1, 8, "Wild Food Types", style2)
#set column width for sheet3
for i in range(0,10):
sheet3.col(i).width = 6000
#get personal and household characteristics, configured for current project
pchars = self.getPersonalCharacteristics()
hchars = self.getHouseholdCharacteristics()
#section for extended personal characteristics
sheet2.write(8, 0, "PersonalCharacteristics", style1)
col = 0
for char in pchars:
value = char[0]
typep = char[1]
if value!='pid' and value !='hhid':
stringvar = 'varchar'
boolvar = 'enum'
intvar = 'bigint'
doublevar ='double'
if typep.startswith(tuple(stringvar)):
vartype ='String'
elif typep.startswith(tuple(boolvar)):
vartype ='Yes/No'
elif typep.startswith(tuple(intvar)):
vartype ='Integer'
elif typep.startswith(tuple(doublevar)):
vartype ='Double'
sheet2.write(9, col, value, style2)
sheet2.write(10, col, vartype, style3)
col = col + 1
#section for household characteristics
sheet2.write(17, 0, "HouseholdCharacteristics", style1)
col = 0
for char in hchars:
value = char[0]
typep = char[1]
if value !='pid' and value !='hhid':
stringvar = 'varchar'
boolvar = 'enum'
intvar = 'bigint'
doublevar ='double'
if typep.startswith(tuple(stringvar)):
vartype ='String'
elif typep.startswith(tuple(boolvar)):
vartype ='Yes/No'
elif typep.startswith(tuple(intvar)):
vartype ='Integer'
elif typep.startswith(tuple(doublevar)):
vartype ='Double'
sheet2.write(18, col, value, style2)
sheet2.write(19, col, vartype, style3)
col = col + 1
headerrow = 25
itemrow = 26
#Assets
sheet2.write(headerrow, 0, "Assets", style1)
sheet2.write(itemrow, 0, "Category", style2)
sheet2.write(itemrow, 1, "Type", style2)
sheet2.write(itemrow, 2, "Unit", style2)
sheet2.write(itemrow, 3, "UnitCost", style2)
sheet2.write(itemrow, 4, "Units", style2)
#Expenditure
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "Expenditure", style1)
sheet2.write(itemrow, 0, "Type", style2)
sheet2.write(itemrow, 1, "Unit", style2)
sheet2.write(itemrow, 2, "KCalPerUnit", style2)
sheet2.write(itemrow, 3, "UnitCost", style2)
sheet2.write(itemrow, 4, "Units", style2)
#Crop Income
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "Crops", style1)
sheet2.write(itemrow, 0, "Name", style2)
sheet2.write(itemrow, 1, "Unit", style2)
sheet2.write(itemrow, 2, "UnitsProduced", style2)
sheet2.write(itemrow, 3, "UnitsSold", style2)
sheet2.write(itemrow, 4, "UnitPrice", style2)
sheet2.write(itemrow, 5, "OtherUses", style2)
sheet2.write(itemrow, 6, "UnitsConsumed", style2)
#Livestock
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "Livestock", style1)
sheet2.write(itemrow, 0, "Name", style2)
sheet2.write(itemrow, 1, "Unit", style2)
sheet2.write(itemrow, 2, "UnitsProduced", style2)
sheet2.write(itemrow, 3, "UnitsSold", style2)
sheet2.write(itemrow, 4, "UnitPrice", style2)
sheet2.write(itemrow, 5, "OtherUses", style2)
sheet2.write(itemrow, 6, "UnitsConsumed", style2)
#WildFoods
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "WildFoods", style1)
sheet2.write(itemrow, 0, "Name", style2)
sheet2.write(itemrow, 1, "Unit", style2)
sheet2.write(itemrow, 2, "UnitsProduced", style2)
sheet2.write(itemrow, 3, "UnitsSold", style2)
sheet2.write(itemrow, 4, "UnitPrice", style2)
sheet2.write(itemrow, 5, "OtherUses", style2)
sheet2.write(itemrow, 6, "UnitsConsumed", style2)
#Employment
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "Employment", style1)
sheet2.write(itemrow, 0, "Type", style2)
sheet2.write(itemrow, 1, "FoodPaid", style2)
sheet2.write(itemrow, 2, "Unit", style2)
sheet2.write(itemrow, 3, "UnitsPaid", style2)
sheet2.write(itemrow, 4, "Kcals", style2)
sheet2.write(itemrow, 5, "CashIncome", style2)
#Social Transfers
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "SocialTransfer", style1)
sheet2.write(itemrow, 0, "TransferSource", style2)
sheet2.write(itemrow, 1, "CashPerYear", style2)
sheet2.write(itemrow, 2, "FoodType", style2)
sheet2.write(itemrow, 3, "Unit", style2)
sheet2.write(itemrow, 4, "UnitsConsumed", style2)
sheet2.write(itemrow, 5, "UnitsSold", style2)
sheet2.write(itemrow, 6, "PricePerUnit", style2)
#Transfers from Organisations
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "TransferFromOrganisations", style1)
sheet2.write(itemrow, 0, "TransferSource", style2)
sheet2.write(itemrow, 1, "CashPerYear", style2)
sheet2.write(itemrow, 2, "FoodType", style2)
sheet2.write(itemrow, 3, "Unit", style2)
sheet2.write(itemrow, 4, "UnitsConsumed", style2)
sheet2.write(itemrow, 5, "UnitsSold", style2)
sheet2.write(itemrow, 6, "PricePerUnit", style2)
#set column width for sheet2
for i in range(0,7):
sheet2.col(i).width = 6000
# Income sources
incometypes = ['crops','employment','livestock','transfers','wildfoods']
col = 0
print 'there'
for i in range (0,5):
incometype = incometypes[i]:
row = 1
query = self.buildQueries(incometype)
print incometype
recordset = self.getincomeSources(query)
for row in recordset:
cellvalue = row[0]
sheet3.write(row, col, cellvalue, style1)
row = row + 1
col = col + 2
folder = "inputs/"
filename = folder + "dataEntrySheet-ProjectID-" + str(self.pid) + ".xls"
book.save(filename)
completionmessage = '''Template Saved As open-ihm/''' + str(filename) +'''\n\nClick OK to open the spreadsheet. This may take a few seconds. '''
QtGui.QMessageBox.information(None, 'Data Entry Template', completionmessage)
#
os.system(os.path.curdir + "\\inputs\\dataEntrySheet-ProjectID-" + str(self.pid) + ".xls")
class HouseholdsByCharacteristics:
def __init__(self):
self.database = Database()
def buildPCharacteristicsQuery(self,pcharacteristics, tablename,projectid):
''' build query for selecting households that meet selected personal characteristics from the report interface'''
houseid = tablename + '.hhid'
basequery = '''SELECT households.pid,households.hhid, households.householdname
FROM households,personalcharacteristics WHERE households.pid=%s AND households.hhid = personalcharacteristics.hhid AND households.pid=personalcharacteristics.pid''' % projectid
for currentcharacteristic in pcharacteristics:
#currentcharacteristic = 'personalcharacteristics' + '.' + '%s' % characteristic
basequery = basequery + " AND personalcharacteristics.characteristic ='%s' AND personalcharacteristics.charvalue='Yes'" % (currentcharacteristic)
basequery = basequery + " GROUP BY households.pid,households.hhid"
print basequery
return basequery
def buildHCharacteristicsQuery(self,hcharacteristics, tablename,projectid):
''' build query for selecting households that meet selected household characteristics from the report interface'''
#settingsmanager = ReportsSettingsManager()
houseid = tablename + '.hhid'
basequery = basequery = '''SELECT households.pid,households.hhid, households.householdname
FROM households,householdcharacteristics WHERE households.pid=%s AND households.hhid = householdcharacteristics.hhid AND households.pid=householdcharacteristics.pid''' % projectid
for currentcharacteristic in hcharacteristics:
#currentcharacteristic = 'householdcharacteristics' + '.' + '%s' % characteristic
basequery = basequery + " AND householdcharacteristics.characteristic ='%s' AND householdcharacteristics.charvalue='Yes'" % (currentcharacteristic)
basequery = basequery + " GROUP BY households.pid,households.hhid"
print basequery
return basequery
def getReportTable(self,projectid,pcharselected,hcharselected,pquery,hquery):
''' generate report tables'''
pcharstable = self.getPcharacteristicsTable(pquery)
hcharstable = self.getHcharacteristicsTable(hquery)
x = len(pcharstable)
y = len(hcharstable)
reporttable = []
if (x ==0 and y== 0)or (x == 0 and pcharselected !=0)or (y == 0 and hcharselected !=0):
return reporttable
elif (x !=0 and y != 0):
query = ''' SELECT * FROM (%s UNION ALL %s) AS tbl GROUP BY tbl.hhid HAVING COUNT(*) = 2''' % (pquery,hquery)
reporttable = self.getFinalReportTableData(query)
print reporttable
return reporttable
elif (x == 0 and pcharselected ==0):
return hcharstable
elif (y == 0 and hcharselected ==0):
return pcharstable
def getPcharacteristicsTable(self,pquery):
''' get households where selected personal characteristics from the interface are met'''
self.database.open()
print pquery
ptable = self.database.execSelectQuery( pquery )
self.database.close()
return ptable
def getHcharacteristicsTable(self,hquery):
''' get households where selected household characteristics from the interface are met'''
self.database.open()
htable = self.database.execSelectQuery( hquery )
self.database.close()
return htable
def getFinalReportTableData(self, query):
'''get reporttable where user has selected both household and personal characteristics as output criteria'''
self.database.open()
reporttable = self.database.execSelectQuery( query )
self.database.close()
return reporttable
def getHouseholdsForReport(self):
pass