def getProjectEmploymentIncomeSources(self):
projectid = self.getProjectID()
settingsmgr = ReportsSettingsManager()
recordset = settingsmgr.getSimulationEmploymentIncomeSources(projectid)
model = QStandardItemModel(1,2)
#set headers
model.setHorizontalHeaderItem(0,QStandardItem('Income Source'))
model.setHorizontalHeaderItem(1,QStandardItem('% Ref Income'))
# add data rows
num = 0
for row in recordset:
qtItem = QStandardItem( "%s" % row[0])
qtItem.setTextAlignment( Qt.AlignCenter )
qtRefIncome = QStandardItem( "%d" % row[1] )
qtRefIncome.setTextAlignment( Qt.AlignRight | Qt.AlignVCenter )
model.setItem( num, 0, qtItem )
model.setItem( num, 1, qtRefIncome )
num = num + 1
self.tblIncome.setModel(model)
#self.tableView.verticalHeader().hide()
self.tblIncome.horizontalHeader().setResizeMode(0, QHeaderView.ResizeToContents)
self.tblIncome.horizontalHeader().setResizeMode(1, QHeaderView.ResizeToContents)
self.tblIncome.horizontalHeader().setResizeMode(2, QHeaderView.ResizeToContents)
self.tblIncome.show()
def buildReportHouseholdIDsQuery(self,projectid,selectedhouseholds,pcharselected,hcharselected):
''' generate report household ids'''
selectedpchars =[]
selectedhchars = []
selectedpchars = pcharselected
selectedhchars = hcharselected
hcharssetting = ReportsSettingsManager()
hcharsTable= hcharssetting.setHCharacteristicsTableName(projectid)
pcharssetting = ReportsSettingsManager()
pharsTable = pcharssetting.setPCharacteristicsTableName(projectid)
householdsquery = self.buildHouseholdsQuery(selectedhouseholds,projectid)
query = ''
if len(selectedhouseholds)!=0:
if len(selectedpchars) ==0 and len(selectedhchars) ==0:
query = householdsquery
householdids = self.getReportHouseholdIDs(query)
elif len(selectedpchars) !=0 and len(selectedhchars) !=0:
pcharsQuery =self.buildPCharacteristicsQuery(pcharselected, projectid)
hcharsQuery = self.buildHCharacteristicsQuery(hcharselected, projectid)
query = '''SELECT * FROM (%s UNION ALL %s UNION ALL %s) AS tbl GROUP BY tbl.hhid HAVING COUNT(*) = 3 ''' % (householdsquery,pcharsQuery,hcharsQuery)
elif len(selectedhchars) !=0:
hcharsQuery = self.buildHCharacteristicsQuery(hcharselected, projectid)
query = '''SELECT * FROM (%s UNION ALL %s) AS tbl GROUP BY tbl.hhid HAVING COUNT(*) = 2 ''' % (householdsquery,hcharsQuery)
elif len(selectedpchars) !=0:
pcharsQuery =self.buildPCharacteristicsQuery(pcharselected, projectid)
query = '''SELECT * FROM (%s UNION ALL %s) AS tbl GROUP BY tbl.hhid HAVING COUNT(*) = 2 ''' % (householdsquery,pcharsQuery)
return query
def getProjectStandardOfLiving(self):
projectid = self.getProjectID()
settingsmgr = ReportsSettingsManager()
recordset = settingsmgr.getProjectStandardofLiving(projectid)
model = QStandardItemModel(1,3)
#set headers
model.setHorizontalHeaderItem(0,QStandardItem('Expense Type'))
model.setHorizontalHeaderItem(1,QStandardItem('Price'))
model.setHorizontalHeaderItem(2,QStandardItem('Model Price'))
# add data rows
num = 0
for row in recordset:
qtItem = QStandardItem( "%s" % row[0])
qtItem.setTextAlignment( Qt.AlignCenter )
qtPrice = QStandardItem( "%d" % row[1] )
qtPrice.setTextAlignment( Qt.AlignRight | Qt.AlignVCenter )
qtModelPrice = QStandardItem( "%d" % row[2] )
qtModelPrice.setTextAlignment( Qt.AlignRight | Qt.AlignVCenter )
model.setItem( num, 0, qtItem )
model.setItem( num, 1, qtPrice )
model.setItem( num, 2, qtModelPrice )
num = num + 1
self.tblStandardofLiving.setModel(model)
#self.tableView.verticalHeader().hide()
self.tblStandardofLiving.horizontalHeader().setResizeMode(0, QHeaderView.ResizeToContents)
self.tblStandardofLiving.horizontalHeader().setResizeMode(1, QHeaderView.ResizeToContents)
self.tblStandardofLiving.horizontalHeader().setResizeMode(2, QHeaderView.ResizeToContents)
self.tblStandardofLiving.show()
def getReportHouseholdIDs(self):
householdnumbers = []
projectid = self.getProjectID()
connector = ReportsSettingsManager()
recordset = connector.getHouseholdIDs(projectid)
for row in recordset:
householdnumbers.append(str(row[0]))
return householdnumbers
def getProjectID(self):
''' get ID for the selected project'''
selectedproject = self.cmbProjectNames.currentText()
if selectedproject !="":
settingsmgr = ReportsSettingsManager()
selectedprojectid = settingsmgr.getSelectedProjectID(selectedproject)
return selectedprojectid
else: return 0
def getPorjects(self):
''' populate projects combobox with available projects'''
settingsmgr = ReportsSettingsManager()
rows = settingsmgr.getProjectNames()
for row in rows:
project = row[0]
self.cmbProjects.addItem(project)
self.cmbProjects.setCurrentIndex(-1)
def getHouseholdCharacteristicsQuery(self):
''' build query for selecting household that match the selected household characteristics'''
selectedHChars = []
selectedHChars = self.getSelectedHouseholdCharacteristics()
projectid = self.getProjectID()
hchars = ReportsSettingsManager ()
tablename = hchars.setHCharacteristicsTableName(projectid)
connector = HouseholdsByCharacteristics()
hquery = connector.buildHCharacteristicsQuery(selectedHChars,tablename,projectid)
return hquery
def getPersonalCharacteristicsQuery(self):
''' build query for selecting household that match the selected personal characteristics'''
selectedRows = []
selectedRows = self.getSelectedPersonalCharacteristics()
projectid = self.getProjectID()
pchars = ReportsSettingsManager ()
tablename = pchars.setPCharacteristicsTableName(projectid)
connector = HouseholdsByCharacteristics()
pquery = connector.buildPCharacteristicsQuery(selectedRows,tablename,projectid)
return pquery
def putEmploymentIncomeSources(self):
'''Insert Employment Income Sources into the Household Income Dialog's TreeView'''
projectid = self.getProjectID()
settingsmgr = ReportsSettingsManager()
rows = settingsmgr.getEmploymentIncomeSources(projectid)
numrows = len(rows)
parent = self.getEmploymentIndex()
self.treeView.model().insertColumn(0, parent )
self.treeView.model().insertRows( 0, numrows, parent )
num =0
for row in rows:
child = self.treeView.model().index( num, 0, parent )
self.treeView.model().setData(child, row[0])
num = num + 1
def getPersonalCharacteristics(self):
''' get personal characteristics relevant to the selected project'''
projectid = self.getProjectID()
settingsmgr = ReportsSettingsManager()
rows = settingsmgr.getPersonalCharacteristics(projectid)
model = QStandardItemModel()
num = 0
for row in rows:
qtPCharacteristic = QStandardItem( "%s" % row[0])
qtPCharacteristic.setTextAlignment( Qt.AlignLeft )
if ((qtPCharacteristic.text() != 'hhid')and(qtPCharacteristic.text() != 'personid') and (qtPCharacteristic.text() != 'pid')):
model.setItem( num, 0, qtPCharacteristic )
num = num + 1
self.listViewPersonalCharacteristics.setModel(model)
self.listViewPersonalCharacteristics.show()
def getHouseholdCharacteristics(self):
''' get household characteristics relevant to selected project'''
projectid = self.getProjectID()
settingsmgr = ReportsSettingsManager()
rows = settingsmgr.getHouseholdCharacteristics(projectid)
model = QStandardItemModel()
num = 0
#x=0
#if rows.count(x) != 0:
for row in rows:
qtHCharacteristic = QStandardItem( "%s" % row[0])
qtHCharacteristic.setTextAlignment( Qt.AlignLeft )
if ((qtHCharacteristic.text() != 'hhid')and(qtHCharacteristic.text() != 'pid') ):
model.setItem( num, 0, qtHCharacteristic )
num = num + 1
self.listViewHCharacteristics.setModel(model)
self.listViewHCharacteristics.show()
def getProjectDiet(self):
projectid = self.getProjectID()
settingsmgr = ReportsSettingsManager()
recordset = settingsmgr.getProjectDiet(projectid)
model = QStandardItemModel(1,4)
#set headers
model.setHorizontalHeaderItem(0,QStandardItem('Food Type'))
model.setHorizontalHeaderItem(1,QStandardItem('% in Diet'))
model.setHorizontalHeaderItem(2,QStandardItem('Price'))
model.setHorizontalHeaderItem(3,QStandardItem('Model Price'))
# add data rows
num = 0
for row in recordset:
qtFood = QStandardItem( "%s" % row[0])
qtFood.setTextAlignment( Qt.AlignCenter )
qtPercentage = QStandardItem( "%i" % row[1] )
qtPercentage.setTextAlignment( Qt.AlignRight | Qt.AlignVCenter )
qtPricePerUnit = QStandardItem( "%i" % row[2] )
qtPricePerUnit.setTextAlignment( Qt.AlignRight | Qt.AlignVCenter )
qtModelPrice = QStandardItem( "%i" % row[3] )
qtModelPrice.setTextAlignment( Qt.AlignRight | Qt.AlignVCenter )
model.setItem( num, 0, qtFood )
model.setItem( num, 1, qtPercentage )
model.setItem( num, 2, qtPricePerUnit )
model.setItem( num, 3, qtModelPrice )
num = num + 1
self.tblDiets.setModel(model)
#self.tableView.verticalHeader().hide()
self.tblDiets.horizontalHeader().setResizeMode(0, QHeaderView.ResizeToContents)
self.tblDiets.horizontalHeader().setResizeMode(1, QHeaderView.ResizeToContents)
self.tblDiets.horizontalHeader().setResizeMode(2, QHeaderView.ResizeToContents)
self.tblDiets.horizontalHeader().setResizeMode(3, QHeaderView.ResizeToContents)
self.tblDiets.show()
def getHouseholds(self):
''' get households (numbers and names) for selected project & populate dialog lists'''
projectid = self.getProjectID()
settingsmgr = ReportsSettingsManager()
rows = settingsmgr.getHouseholds(projectid)
model = QStandardItemModel()
model1 = QStandardItemModel()
num = 0
for row in rows:
qtHNumber = QStandardItem( "%s" % row[0])
qtHNumber.setTextAlignment( Qt.AlignLeft )
qtHName = QStandardItem( "%s" % row[1])
qtHName.setTextAlignment( Qt.AlignLeft )
model.setItem( num, 0, qtHNumber )
model1.setItem( num, 0, qtHName )
num = num + 1
self.listViewHNumbers.setModel(model)
self.listViewHNames.setModel(model1)
self.listViewHNumbers.show()
self.listViewHNames.show()
def getProjectIncomeSources(self):
projectid = self.getProjectID()
settingsmgr = ReportsSettingsManager()
currentincomecategory = self.getActiveIcomeSourceCategory()
if currentincomecategory =='Crops':
recordset = settingsmgr.getSimulationCropIncomeSources(projectid)
elif currentincomecategory =='Official Transfers':
recordset = settingsmgr.getSimulationFormalTransferIncomeSources(projectid)
elif currentincomecategory =='Unofficial Tansfers':
recordset = settingsmgr.getSimulationInformalTransferIncomeSources(projectid)
elif currentincomecategory =='Livestock':
recordset = settingsmgr.getSimulationLivestockIncomeSources(projectid)
elif currentincomecategory =='WildFoods':
recordset = settingsmgr.getSimulationWildfoodsIncomeSources(projectid)
model = QStandardItemModel(1,3)
#set headers
model.setHorizontalHeaderItem(0,QStandardItem('Income Source'))
model.setHorizontalHeaderItem(1,QStandardItem('% Ref Price'))
model.setHorizontalHeaderItem(2,QStandardItem('% Ref Production'))
# add data rows
num = 0
for row in recordset:
qtItem = QStandardItem( "%s" % row[0])
qtItem.setTextAlignment( Qt.AlignCenter )
qtRefPrice = QStandardItem( "%d" % row[1] )
qtRefPrice.setTextAlignment( Qt.AlignRight | Qt.AlignVCenter )
qtRefProduction = QStandardItem( "%d" % row[2] )
qtRefProduction.setTextAlignment( Qt.AlignRight | Qt.AlignVCenter )
model.setItem( num, 0, qtItem )
model.setItem( num, 1, qtRefPrice )
model.setItem( num, 2, qtRefProduction )
num = num + 1
self.tblIncome.setModel(model)
#self.tableView.verticalHeader().hide()
self.tblIncome.horizontalHeader().setResizeMode(0, QHeaderView.ResizeToContents)
self.tblIncome.horizontalHeader().setResizeMode(1, QHeaderView.ResizeToContents)
self.tblIncome.horizontalHeader().setResizeMode(2, QHeaderView.ResizeToContents)
self.tblIncome.show()
def getHouseholdNames(self):
'''Get Names of Households selected by the User on the Interface'''
projectid = self.getProjectID()
settingsmgr = ReportsSettingsManager()
rows = settingsmgr.getProjectHouseholds(projectid)
return rows
