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