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