def get_list(self, request, **kwargs): # check if call is cached using validator.is_cached # check if call contains flush, if it does the call comes from the cache updater and shouldn't return cached results validator = Validator() cururl = request.META['PATH_INFO'] + "?" + request.META['QUERY_STRING'] if not 'flush' in cururl and validator.is_cached(cururl): return HttpResponse(validator.get_cached_call(cururl), content_type='application/json') helper = CustomCallHelper() # country_q = helper.get_and_query(request, 'countries__in', 'c.code') budget_q_gte = request.GET.get('total_budget__gt', None) budget_q_lte = request.GET.get('total_budget__lt', None) region_q = helper.get_and_query(request, 'regions__in', 'r.code') sector_q = helper.get_and_query(request, 'sectors__in', 's.sector_id') organisation_q = helper.get_and_query(request, 'reporting_organisation__in', 'a.reporting_organisation_id') budget_q = '' limit = request.GET.get("limit", 999) offset = request.GET.get("offset", 0) order_by = request.GET.get("order_by", None) order_asc_desc = request.GET.get("order_asc_desc", "ASC") start_actual_q = helper.get_year_and_query(request, 'start_actual__in', 'a.start_actual') start_planned_q = helper.get_year_and_query(request, 'start_planned__in', 'a.start_planned') vocabulary_q = helper.get_and_query(request, "vocabulary__in", "rv.code") region_query = request.GET.get("region", None) project_query = request.GET.get("query", None) donor_q = helper.get_and_query(request, 'participating_organisations__in', 'apo.organisation_id') if budget_q_gte: budget_q += ' a.total_budget > "' + budget_q_gte + '" ) AND (' if budget_q_lte: budget_q += ' a.total_budget < "' + budget_q_lte + '" ) AND (' filter_string = ' AND (' + organisation_q + region_q + sector_q + budget_q + start_planned_q + start_actual_q + vocabulary_q + donor_q + ')' if 'AND ()' in filter_string: filter_string = filter_string[:-6] filter_sector = '' if sector_q: filter_sector = 'LEFT JOIN iati_activitysector s ON a.id = s.activity_id ' filter_vocabulary = '' if vocabulary_q: filter_vocabulary = "LEFT JOIN iati_regionvocabulary rv ON r.region_vocabulary_id = rv.code " filter_region = '' if region_q: filter_region = 'LEFT JOIN iati_activityrecipientregion rr ON rr.activity_id = a.id LEFT JOIN geodata_region r ON rr.region_id = r.code ' filter_project_query = '' if project_query: filter_project_query = 'LEFT JOIN iati_title as t on a.id = t.activity_id ' filter_string += 'AND t.title LIKE "%%' + project_query + '%%" ' filter_donor = '' if donor_q: filter_donor = 'LEFT JOIN iati_activityparticipatingorganisation as apo on a.id = apo.activity_id ' filter_string += ' AND apo.role_id = "Funding" ' cursor = connection.cursor() query = 'SELECT count(a.id) as total_projects, sum(a.total_budget) as total_budget '\ 'FROM iati_activity a '\ '%s %s %s %s %s '\ 'WHERE a.scope_id = 1 %s'\ 'GROUP BY a.scope_id ' % (filter_sector, filter_vocabulary, filter_project_query, filter_donor, filter_region, filter_string) cursor.execute(query) activities = [] results = helper.get_fields(cursor=cursor) for r in results: region = {} region['total_projects'] = r['total_projects'] region['total_budget'] = r['total_budget'] activities.append(region) return_json = {} return_json["objects"] = activities cursor = connection.cursor() query = 'SELECT count(a.id) as total_projects, sum(a.total_budget) as total_budget '\ 'FROM iati_activity a '\ '%s %s %s %s %s '\ 'WHERE a.scope_id = 1 %s'\ 'GROUP BY a.scope_id ' % (filter_sector, filter_vocabulary, filter_project_query, filter_donor, filter_region, filter_string) cursor.execute(query) results2 = helper.get_fields(cursor=cursor) return_json["meta"] = {"total_count": len(results2)} return HttpResponse(ujson.dumps(return_json), content_type='application/json')
def get_list(self, request, **kwargs): # check if call is cached using validator.is_cached # check if call contains flush, if it does the call comes from the cache updater and shouldn't return cached results helper = CustomCallHelper() country_q = helper.get_and_query(request, 'countries__in', 'c.code') budget_q_gte = request.GET.get('total_budget__gt', None) budget_q_lte = request.GET.get('total_budget__lt', None) region_q = helper.get_and_query(request, 'regions__in', 'r.code') sector_q = helper.get_and_query(request, 'sectors__in', 's.sector_id') donor_q = helper.get_and_query(request, 'participating_organisations__organisation__code__in', 'apo.organisation_id') organisation_q = helper.get_and_query(request, 'reporting_organisation__in', 'a.reporting_organisation_id') start_actual_q = helper.get_year_and_query(request, 'start_actual__in', 'a.start_actual') start_planned_q = helper.get_year_and_query(request, 'start_planned__in', 'a.start_planned') budget_q = '' limit = request.GET.get("limit", 999) offset = request.GET.get("offset", 0) order_by = request.GET.get("order_by", "country_name") order_asc_desc = request.GET.get("order_asc_desc", "ASC") country_query = request.GET.get("country", None) project_query = request.GET.get("query", None) format = request.GET.get("format", "json") include_unesco_empty = request.GET.get("include_unesco_empty", False) if budget_q_gte: budget_q += ' a.total_budget > "' + budget_q_gte + '" ) AND (' if budget_q_lte: budget_q += ' a.total_budget < "' + budget_q_lte + '" ) AND (' filter_string = ' AND (' + country_q + organisation_q + region_q + sector_q + budget_q + start_planned_q + start_actual_q + donor_q + ')' if 'AND ()' in filter_string: filter_string = filter_string[:-6] filter_region = '' if region_q: filter_region = 'LEFT JOIN iati_activityrecipientregion rr ON rr.activity_id = a.id LEFT JOIN geodata_region r ON rr.region_id = r.code ' filter_sector = '' if sector_q: filter_sector = 'LEFT JOIN iati_activitysector s ON a.id = s.activity_id ' filter_donor = '' if donor_q: filter_donor = 'LEFT JOIN iati_activityparticipatingorganisation as apo on a.id = apo.activity_id ' filter_string += ' AND apo.role_id = "Funding" ' if country_query: filter_string += 'AND c.name LIKE "%%' + country_query + '%%" ' filter_project_query = '' if project_query: filter_project_query = 'LEFT JOIN iati_title as t on a.id = t.activity_id ' filter_string += 'AND t.title LIKE "%%' + project_query + '%%" AND c.name LIKE "%%' + project_query + '%%"' cursor = connection.cursor() query = 'SELECT c.code as country_id, c.name as country_name, AsText(c.center_longlat) as location, count(a.id) as total_projects, sum(a.total_budget) as total_budget '\ 'FROM geodata_country c '\ 'LEFT JOIN iati_activityrecipientcountry rc ON rc.country_id = c.code '\ 'LEFT JOIN iati_activity a ON rc.activity_id = a.id '\ '%s %s %s %s'\ 'WHERE c.code is not null %s'\ 'GROUP BY c.code ' \ 'ORDER BY %s %s ' \ 'LIMIT %s OFFSET %s' % (filter_region, filter_sector, filter_donor, filter_project_query, filter_string, order_by, order_asc_desc, limit, offset) if include_unesco_empty and organisation_q: query = query.replace(organisation_q, "") query = query.replace(' AND (' + organisation_q[:-6], "") query = query.replace(organisation_q[:-6], "") query = query.replace("LEFT JOIN iati_activity a ON rc.activity_id = a.id", "LEFT JOIN iati_activity a ON rc.activity_id = a.id AND " + organisation_q[:-8]) query = query.replace("WHERE ", "WHERE unesco_region_id is not null AND ") cursor.execute(query) activities = [] results = helper.get_fields(cursor=cursor) for r in results: country = {} country['id'] = r['country_id'] country['name'] = r['country_name'] country['total_projects'] = r['total_projects'] loc = r['location'] if loc: loc = loc.replace("POINT(", "") loc = loc.replace(")", "") loc_array = loc.split(" ") longitude = loc_array[0] latitude = loc_array[1] else: longitude = None latitude = None country['latitude'] = latitude country['longitude'] = longitude country['total_budget'] = r['total_budget'] activities.append(country) return_json = {} return_json["objects"] = activities cursor = connection.cursor() query = 'SELECT c.code '\ 'FROM geodata_country c '\ 'LEFT JOIN iati_activityrecipientcountry rc ON rc.country_id = c.code '\ 'LEFT JOIN iati_activity a ON rc.activity_id = a.id '\ '%s %s %s %s'\ 'WHERE c.code is not null %s'\ 'GROUP BY c.code ' % (filter_region, filter_sector, filter_donor, filter_project_query, filter_string) if include_unesco_empty and organisation_q: query = query.replace(organisation_q, "") query = query.replace(' AND (' + organisation_q[:-6], "") query = query.replace(organisation_q[:-6], "") query = query.replace("LEFT JOIN iati_activity a ON rc.activity_id = a.id", "LEFT JOIN iati_activity a ON rc.activity_id = a.id AND " + organisation_q[:-8]) query = query.replace("WHERE ", "WHERE unesco_region_id is not null AND ") cursor.execute(query) results2 = helper.get_fields(cursor=cursor) return_json["meta"] = {"total_count": len(results2)} if format == "json": return HttpResponse(ujson.dumps(return_json), content_type='application/json') if format == "xml": for item in return_json["objects"]: item["name"] = item["name"].encode('utf-8', 'ignore') xml = dict2xml(return_json, "objects", True, "country") return HttpResponse(xml, content_type='application/xml') if format == "csv": csvh = CsvHelper() csv_content = csvh.to_csv(return_json) return HttpResponse(csv_content, content_type='text/csv')
def get_list(self, request, **kwargs): # check if call is cached using validator.is_cached # check if call contains flush, if it does the call comes from the cache updater and shouldn't return cached results validator = Validator() cururl = request.META['PATH_INFO'] + "?" + request.META['QUERY_STRING'] if not 'flush' in cururl and validator.is_cached(cururl): return HttpResponse(validator.get_cached_call(cururl), content_type='application/json') helper = CustomCallHelper() # country_q = helper.get_and_query(request, 'countries__in', 'c.code') budget_q_gte = request.GET.get('total_budget__gt', None) budget_q_lte = request.GET.get('total_budget__lt', None) region_q = helper.get_and_query(request, 'regions__in', 'r.code') sector_q = helper.get_and_query(request, 'sectors__in', 's.sector_id') organisation_q = helper.get_and_query(request, 'reporting_organisation__in', 'a.reporting_organisation_id') budget_q = '' limit = request.GET.get("limit", 999) offset = request.GET.get("offset", 0) order_by = request.GET.get("order_by", "region_name") order_asc_desc = request.GET.get("order_asc_desc", "ASC") start_actual_q = helper.get_year_and_query(request, 'start_actual__in', 'a.start_actual') start_planned_q = helper.get_year_and_query(request, 'start_planned__in', 'a.start_planned') vocabulary_q = helper.get_and_query(request, "vocabulary__in", "rv.code") region_query = request.GET.get("region", None) project_query = request.GET.get("query", None) donor_q = helper.get_and_query(request, 'participating_organisations__in', 'apo.organisation_id') format = request.GET.get("format", "json") if budget_q_gte: budget_q += ' a.total_budget > "' + budget_q_gte + '" ) AND (' if budget_q_lte: budget_q += ' a.total_budget < "' + budget_q_lte + '" ) AND (' filter_string = ' AND (' + organisation_q + region_q + sector_q + budget_q + start_planned_q + start_actual_q + vocabulary_q + donor_q + ')' if 'AND ()' in filter_string: filter_string = filter_string[:-6] filter_sector = '' if sector_q: filter_sector = 'LEFT JOIN iati_activitysector s ON a.id = s.activity_id ' filter_vocabulary = '' if vocabulary_q: filter_vocabulary = "LEFT JOIN iati_regionvocabulary rv ON r.region_vocabulary_id = rv.code " if region_query: filter_string += 'AND r.name LIKE "%%' + region_query + '%%" ' filter_project_query = '' if project_query: filter_project_query = 'LEFT JOIN iati_title as t on a.id = t.activity_id ' filter_string += 'AND t.title LIKE "%%' + project_query + '%%" ' filter_donor = '' if donor_q: filter_donor = 'LEFT JOIN iati_activityparticipatingorganisation as apo on a.id = apo.activity_id ' filter_string += ' AND apo.role_id = "Funding" ' cursor = connection.cursor() query = 'SELECT r.code as region_id, r.name as region_name, AsText(r.center_longlat) as location, count(a.id) as total_projects, sum(a.total_budget) as total_budget '\ 'FROM iati_activity a '\ 'LEFT JOIN iati_activityrecipientregion rr ON rr.activity_id = a.id '\ 'LEFT JOIN geodata_region r ON rr.region_id = r.code '\ '%s %s %s %s'\ 'WHERE r.code is not null %s'\ 'GROUP BY r.code ' \ 'ORDER BY %s %s ' \ 'LIMIT %s OFFSET %s' % (filter_sector, filter_vocabulary, filter_project_query, filter_donor, filter_string, order_by, order_asc_desc, limit, offset) cursor.execute(query) activities = [] results = helper.get_fields(cursor=cursor) for r in results: region = {} region['id'] = r['region_id'] region['name'] = r['region_name'] region['total_projects'] = r['total_projects'] loc = r['location'] if loc: loc = loc.replace("POINT(", "") loc = loc.replace(")", "") loc_array = loc.split(" ") longitude = loc_array[0] latitude = loc_array[1] else: longitude = None latitude = None region['latitude'] = latitude region['longitude'] = longitude region['total_budget'] = r['total_budget'] activities.append(region) return_json = {} return_json["objects"] = activities cursor = connection.cursor() query = 'SELECT r.code as region_id, r.name as region_name, AsText(r.center_longlat) as location, count(a.id) as total_projects, sum(a.total_budget) as total_budget '\ 'FROM iati_activity a '\ 'LEFT JOIN iati_activityrecipientregion rr ON rr.activity_id = a.id '\ 'LEFT JOIN geodata_region r ON rr.region_id = r.code '\ '%s %s %s %s'\ 'WHERE r.code is not null %s'\ 'GROUP BY r.code ' % (filter_sector, filter_vocabulary, filter_project_query, filter_donor, filter_string) cursor.execute(query) results2 = helper.get_fields(cursor=cursor) return_json["meta"] = {"total_count": len(results2)} if format == "json": return HttpResponse(ujson.dumps(return_json), content_type='application/json') if format == "xml": for item in return_json["objects"]: item["name"] = item["name"].encode('utf-8', 'ignore') item["name"] = item["name"].replace("&", "and") xml = dict2xml(return_json, "objects", True, "region") return HttpResponse(xml, content_type='application/xml') if format == "csv": csvh = CsvHelper() csv_content = csvh.to_csv(return_json) return HttpResponse(csv_content, content_type='text/csv')
def get_list(self, request, **kwargs): # check if call is cached using validator.is_cached # check if call contains flush, if it does the call comes from the cache updater and shouldn't return cached results helper = CustomCallHelper() country_q = helper.get_and_query(request, 'countries__in', 'c.code') budget_q_gte = request.GET.get('total_budget__gt', None) budget_q_lte = request.GET.get('total_budget__lt', None) region_q = helper.get_and_query(request, 'regions__in', 'r.code') sector_q = helper.get_and_query(request, 'sectors__in', 's.sector_id') donor_q = helper.get_and_query(request, 'donors__in', 'apo.organisation_id') organisation_q = helper.get_and_query(request, 'reporting_organisation__in', 'a.reporting_organisation_id') start_actual_q = helper.get_year_and_query(request, 'start_actual__in', 'a.start_actual') start_planned_q = helper.get_year_and_query(request, 'start_planned__in', 'a.start_planned') budget_q = '' limit = request.GET.get("limit", 999) offset = request.GET.get("offset", 0) order_by = request.GET.get("order_by", "apo.name") order_asc_desc = request.GET.get("order_asc_desc", "ASC") query = request.GET.get("query", None) format = request.GET.get("format", "json") if budget_q_gte: budget_q += ' a.total_budget > "' + budget_q_gte + '" ) AND (' if budget_q_lte: budget_q += ' a.total_budget < "' + budget_q_lte + '" ) AND (' filter_string = ' AND (' + country_q + organisation_q + region_q + sector_q + budget_q + start_planned_q + start_actual_q + donor_q + ')' if 'AND ()' in filter_string: filter_string = filter_string[:-6] filter_string += ' AND apo.role_id = "Funding" ' if query: filter_string += 'AND apo.name LIKE "%%' + query + '%%" ' cursor = connection.cursor() query = 'SELECT apo.organisation_id as organisation_id, apo.name as organisation_name, count(a.id) as total_projects, sum(a.total_budget) as total_budget '\ 'FROM iati_activity a '\ 'LEFT JOIN iati_activityparticipatingorganisation as apo on a.id = apo.activity_id '\ 'WHERE apo.name is not null %s'\ 'GROUP BY apo.name ' \ 'ORDER BY %s %s ' \ 'LIMIT %s OFFSET %s' % (filter_string, order_by, order_asc_desc, limit, offset) cursor.execute(query) activities = [] results = helper.get_fields(cursor=cursor) for r in results: donor = {} donor['id'] = r['organisation_id'] donor['name'] = r['organisation_name'] donor['total_projects'] = r['total_projects'] donor['total_budget'] = r['total_budget'] activities.append(donor) return_json = {} return_json["objects"] = activities query = 'SELECT apo.organisation_id as organisation_id '\ 'FROM iati_activity a '\ 'LEFT JOIN iati_activityparticipatingorganisation as apo on a.id = apo.activity_id '\ 'WHERE apo.name is not null %s'\ 'GROUP BY apo.name ' % (filter_string) cursor.execute(query) results2 = helper.get_fields(cursor=cursor) return_json["meta"] = {"total_count": len(results2)} if format == "json": return HttpResponse(ujson.dumps(return_json), content_type='application/json') if format == "xml": for item in return_json["objects"]: item["name"] = item["name"].encode('utf-8', 'ignore') item["name"] = item["name"].replace("&", "and") xml = dict2xml(return_json, "objects", True, "donor") return HttpResponse(xml, content_type='application/xml') if format == "csv": csvh = CsvHelper() csv_content = csvh.to_csv(return_json) return HttpResponse(csv_content, content_type='text/csv')