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 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