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