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