def get_list(self, request, **kwargs):
helper = CustomCallHelper()
cursor = connection.cursor()
# get filters
indicator_id = request.GET.get('indicator_id', None)
# get indicator type
from indicator.models import Indicator
current_indicator = Indicator.objects.get(id=indicator_id)
if (current_indicator.type_data == "p"):
aggregation_type = "AVG"
else:
return HttpResponse(ujson.dumps(["Indicator type not recognized"]),
content_type='application/json')
#create the query
query = 'SELECT year, r.code as region_id, ' + aggregation_type + '(id.value) as aggregation ' \
'FROM indicator_indicatordata as id ' \
'JOIN geodata_country as c on id.country_id = c.code ' \
'JOIN geodata_region as r on c.region_id = r.code ' \
'WHERE id.indicator_id = "'+indicator_id+'" ' \
'GROUP BY year, r.code'
# execute query
cursor.execute(query)
results1 = helper.get_fields(cursor=cursor)
# query result -> json output
options = {}
for r in results1:
options[r['region_id']] = r['aggregation']
return HttpResponse(ujson.dumps(options),
content_type='application/json')
def get_list(self, request, **kwargs):
helper = CustomCallHelper()
country_q = helper.get_and_query(request, 'countries__in', 'c.code')
region_q = helper.get_and_query(request, 'regions__in',
'dac_region_code')
year_q = helper.get_and_query(request, 'years__in', 'id.year')
indicator_q = helper.get_and_query(request, 'indicators__in',
'indicator_id')
if not indicator_q:
return HttpResponse(ujson.dumps("No indicator given"),
content_type='application/json')
filter_string = ' (' + country_q + region_q + year_q + indicator_q + ')'
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
cursor = connection.cursor()
cursor.execute(
'SELECT da.id as indicator_id, da.friendly_label, da.type_data, c.name as country_name, '
'id.value, id.year, AsText(c.center_longlat) as loc, c.code as country_id '
'FROM indicator_indicatordata id '
'LEFT OUTER JOIN geodata_country c ON id.country_id = c.code '
'LEFT OUTER JOIN indicator_indicator da ON da.id = id.indicator_id '
'WHERE '
'id.city_id is NULL '
'AND %s' % (filter_string))
cursor_max = connection.cursor()
indicator_q = indicator_q.replace(" ) AND (", "")
cursor_max.execute(
'SELECT max(value) as max_value FROM indicator_indicatordata WHERE %s'
% indicator_q)
result_max = cursor_max.fetchone()
desc = cursor.description
results = [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
country = {}
for r in results:
try:
country[r['country_id']]['years']
except:
loc = r['loc']
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[r['country_id']] = {
'name': r['country_name'],
'country_id': r['country_id'],
'longitude': longitude,
'latitude': latitude,
'indicator_friendly': r['friendly_label'],
'type_data': r['type_data'],
'indicator': r['indicator_id'],
'years': {}
}
year = {}
year['y' + str(r['year'])] = r['value']
country[r['country_id']]['years'].update(year)
country['max_value'] = result_max[0]
return HttpResponse(ujson.dumps(country),
content_type='application/json')
def get_list(self, request, **kwargs):
helper = CustomCallHelper()
city_q = helper.get_and_query(request, 'cities__in', 'city.id') or ""
country_q = helper.get_and_query(request, 'countries__in',
'country.code') or ""
region_q = helper.get_and_query(request, 'regions__in',
'region.code') or ""
indicator_q = helper.get_and_query(request, 'indicators__in',
'i.indicator_id') or ""
category_q = helper.get_and_query(request, 'categories__in',
'ind.category') or ""
adm_division_q = request.GET.get("adm_division__in",
"city,country,region") or ""
adm_divisions = adm_division_q.split(",")
filter_string = ' AND (' + city_q + country_q + region_q + indicator_q + category_q + ')'
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
regions = {}
countries = {}
cities = {}
indicators = {}
jsondata = {}
if "city" in adm_divisions:
cursor = connection.cursor()
# city filters
cursor.execute(
'SELECT DISTINCT i.indicator_id, i.selection_type ,ind.friendly_label, ind.category as indicator_category, city.id as city_id, city.name as city_name, country.code as country_id, country.name as country_name, region.code as region_id, region.name as region_name '
'FROM indicator_indicatordata i '
'JOIN indicator_indicator ind ON i.indicator_id = ind.id '
'JOIN geodata_city city ON i.city_id=city.id '
'LEFT OUTER JOIN geodata_country country on city.country_id = country.code '
'LEFT OUTER JOIN geodata_region region on country.region_id = region.code '
'WHERE 1 %s ' % (filter_string))
desc = cursor.description
city_results = [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
for r in city_results:
region = {}
if r['region_id']:
region[r['region_id']] = r['region_name']
regions.update(region)
country = {}
if r['country_id']:
country[r['country_id']] = r['country_name']
countries.update(country)
city = {}
if r['city_id']:
city[r['city_id']] = r['city_name']
cities.update(city)
if r['indicator_id']:
if not r['indicator_id'] in indicators:
indicators[r['indicator_id']] = {
"name": r['friendly_label'],
"category": r['indicator_category'],
"selection_types": []
}
if r['selection_type'] and r[
'selection_type'] not in indicators[
r['indicator_id']]["selection_types"]:
indicators[
r['indicator_id']]["selection_types"].append(
r['selection_type'])
if "country" in adm_divisions:
# country filters
filter_string = ' AND (' + country_q + region_q + indicator_q + category_q + ')'
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
cursor = connection.cursor()
cursor.execute(
'SELECT DISTINCT i.indicator_id, i.selection_type, ind.friendly_label, ind.category as indicator_category, country.code as country_id, country.name as country_name, region.code as region_id, region.name as region_name '
'FROM indicator_indicatordata i '
'JOIN indicator_indicator ind ON i.indicator_id = ind.id '
'JOIN geodata_country country on i.country_id = country.code '
'LEFT OUTER JOIN geodata_region region on country.region_id = region.code '
'WHERE 1 %s ' % (filter_string))
desc = cursor.description
country_results = [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
for r in country_results:
region = {}
if r['region_id']:
region[r['region_id']] = r['region_name']
regions.update(region)
country = {}
if r['country_id']:
country[r['country_id']] = r['country_name']
countries.update(country)
if r['indicator_id']:
if not r['indicator_id'] in indicators not in indicators[
r['indicator_id']]["selection_types"]:
indicators[r['indicator_id']] = {
"name": r['friendly_label'],
"category": r['indicator_category'],
"selection_types": []
}
if r['selection_type'] and r[
'selection_type'] not in indicators[
r['indicator_id']]["selection_types"]:
indicators[
r['indicator_id']]["selection_types"].append(
r['selection_type'])
if "region" in adm_divisions:
# region filters
filter_string = ' AND (' + region_q + indicator_q + category_q + ')'
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
cursor = connection.cursor()
cursor.execute(
'SELECT DISTINCT i.indicator_id, i.selection_type ,ind.friendly_label, ind.category as indicator_category, region.code as region_id, region.name as region_name '
'FROM indicator_indicatordata i '
'JOIN indicator_indicator ind ON i.indicator_id = ind.id '
'JOIN geodata_region region on i.region_id = region.code '
'WHERE 1 %s ' % (filter_string))
desc = cursor.description
region_results = [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
for r in region_results:
region = {}
if r['region_id']:
region[r['region_id']] = r['region_name']
regions.update(region)
if r['indicator_id']:
if not r['indicator_id'] in indicators:
indicators[r['indicator_id']] = {
"name": r['friendly_label'],
"category": r['indicator_category'],
"selection_types": []
}
if r['selection_type'] and r[
'selection_type'] not in indicators[
r['indicator_id']]["selection_types"]:
indicators[
r['indicator_id']]["selection_types"].append(
r['selection_type'])
jsondata['regions'] = regions
jsondata['countries'] = countries
jsondata['cities'] = cities
jsondata['indicators'] = indicators
return HttpResponse(ujson.dumps(jsondata),
content_type='application/json')
def get_list(self, request, **kwargs):
helper = CustomCallHelper()
city_q = helper.get_and_query(request, 'cities__in', 'city_id')
country_q = helper.get_and_query(request, 'countries__in', 'c.code')
region_q = helper.get_and_query(request, 'regions__in', 'r.code')
year_q = helper.get_and_query(request, 'years__in', 'id.year')
indicator_q = helper.get_and_query(request, 'indicators__in',
'indicator_id')
selection_type_q = helper.get_and_query(request, 'selection_type__in',
'id.selection_type')
limit_q = request.GET.get("limit", None)
if limit_q:
limit_q = int(limit_q)
if not indicator_q and not country_q and not city_q:
return HttpResponse(ujson.dumps("No indicator given"),
content_type='application/json')
# CITY DATA
filter_string = 'AND (' + city_q + country_q + region_q + year_q + indicator_q + selection_type_q + ')'
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
cursor = connection.cursor()
cursor.execute(
'SELECT da.id as indicator_id, da.friendly_label, da.type_data, id.selection_type, da.category, ci.name as city_name, '
'r.code as region_id, r.name as region_name, c.code as country_id, c.name as country_name, '
'id.value, id.year, AsText(ci.location) as loc, ci.id as city_id '
'FROM indicator_indicatordata id '
'LEFT OUTER JOIN geodata_city ci ON id.city_id = ci.id '
'LEFT OUTER JOIN geodata_country c ON ci.country_id = c.code '
'LEFT OUTER JOIN geodata_region r ON c.region_id = r.code '
'LEFT OUTER JOIN indicator_indicator da ON da.id = id.indicator_id '
'WHERE id.country_id is null %s '
'ORDER BY id.value DESC' % (filter_string))
desc = cursor.description
city_results = [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
# COUNTRY DATA
filter_string = 'AND (' + city_q + country_q + region_q + year_q + indicator_q + selection_type_q + ')'
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
cursor = connection.cursor()
cursor.execute(
'SELECT da.id as indicator_id, da.friendly_label, id.selection_type, da.category, da.type_data, '
'r.code as region_id, r.name as region_name, c.code as country_id, c.name as country_name, '
'id.value, id.year, AsText(c.center_longlat) as loc '
'FROM indicator_indicatordata id '
'LEFT OUTER JOIN geodata_country c ON id.country_id = c.code '
'LEFT OUTER JOIN geodata_region r ON c.region_id = r.code '
'LEFT OUTER JOIN indicator_indicator da ON da.id = id.indicator_id '
'WHERE id.city_id is null %s '
'ORDER BY id.value DESC' % (filter_string))
desc = cursor.description
country_results = [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
indicator_q = indicator_q.replace(" ) AND (", "")
if indicator_q:
indicator_q = "AND " + indicator_q
cursor_max = connection.cursor()
cursor_max.execute(
'SELECT indicator_id, max(value) as max_value FROM indicator_indicatordata WHERE 1 %s GROUP BY indicator_indicatordata.indicator_id order by max_value DESC'
% indicator_q)
desc = cursor_max.description
max_results = [
dict(zip([col[0] for col in desc], row))
for row in cursor_max.fetchall()
]
# REGION DATA
# NOT IMPLEMENTED YET -> WE DO NOT HAVE CENTER LOCATIONS FOR REGIONS
geolocs = {}
for c in country_results:
if c['value']:
try:
geolocs[c['indicator_id']]['locs'][
c['country_id']]['years']
except:
if not c['indicator_id'] in geolocs:
max_value = max_results[0]['max_value']
geolocs[c['indicator_id']] = {
'indicator_friendly': c['friendly_label'],
'type_data': c['type_data'],
'indicator': c['indicator_id'],
'category': c['category'],
'selection_type': c['selection_type'],
'max_value': max_value,
'locs': {}
}
# if the amount of locs to be shown is reached, do not add the new loc
if limit_q:
if geolocs[c['indicator_id']]['locs'].__len__(
) == limit_q:
continue
loc = c['loc']
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
geolocs[c['indicator_id']]['locs'][c['country_id']] = {
'name': c['country_name'],
'id': c['country_id'],
'region_id': c['region_id'],
'longitude': longitude,
'latitude': latitude,
'years': {}
}
geolocs[c['indicator_id']]['locs'][c['country_id']]['years'][
c['year']] = c['value']
for r in city_results:
if r['value']:
try:
geolocs[r['indicator_id']]['locs'][r['city_id']]['years']
except:
if not r['indicator_id'] in geolocs:
max_value = max_results[0]['max_value']
geolocs[r['indicator_id']] = {
'indicator_friendly': r['friendly_label'],
'type_data': r['type_data'],
'indicator': r['indicator_id'],
'category': r['category'],
'selection_type': r['selection_type'],
'max_value': max_value,
'locs': {}
}
# if the amount of locs to be shown is reached, do not add the new loc
if limit_q:
if geolocs[r['indicator_id']]['locs'].__len__(
) == limit_q:
continue
loc = r['loc']
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
geolocs[r['indicator_id']]['locs'][r['city_id']] = {
'name': r['city_name'],
'id': r['city_id'],
'country_id': r['country_id'],
'region_id': r['region_id'],
'longitude': longitude,
'latitude': latitude,
'years': {}
}
geolocs[r['indicator_id']]['locs'][r['city_id']]['years'][
r['year']] = r['value']
return HttpResponse(ujson.dumps(geolocs),
content_type='application/json')
def get_list(self, request, **kwargs):
# get group by and aggregation pars
group_by_key = request.GET.get(
"group_by",
None) # valid : country, region, year, sector, reporting org
aggregation_key = request.GET.get("aggregation_key", "iati-identifier")
group_field = request.GET.get(
"group_field", "start_actual"
) # used for year filtering, valid : start_planned, start_actual, end_planned, end_actual, defaults to start_actual
if group_by_key in {'commitment', 'disbursement', 'incoming-fund'}:
group_field = "t.value_date"
aggregation_element_dict = {
'iati-identifier': {
'select': 'a.id',
'type': 'count',
'from_addition': ''
},
'reporting-org': {
'select': 'a.reporting_organisation_id',
'type': 'count',
'from_addition': ''
},
'title': {
'select': 't.title',
'type': 'count',
'from_addition':
'JOIN iati_title as t on a.id = t.activity_id '
},
'description': {
'select':
'd.description',
'type':
'count',
'from_addition':
'JOIN iati_description as d on a.id = d.activity_id '
},
'commitment': {
'select': 't.value',
'type': 'sum',
'from_addition':
'JOIN iati_transaction as t on a.id = t.activity_id ',
'where_addition': 'AND t.transaction_type_id = "C" '
},
'disbursement': {
'select': 't.value',
'type': 'sum',
'from_addition':
'JOIN iati_transaction as t on a.id = t.activity_id ',
'where_addition': 'AND t.transaction_type_id = "D" '
},
'incoming-fund': {
'select': 't.value',
'type': 'sum',
'from_addition':
'JOIN iati_transaction as t on a.id = t.activity_id ',
'where_addition': 'AND t.transaction_type_id = "IF" '
},
'location': {
'select': 'l.activity_id',
'type': 'count',
'from_addition':
'JOIN iati_location as l on a.id = l.activity_id '
},
'policy-marker': {
'select':
'pm.policy_marker_id',
'type':
'count',
'from_addition':
'JOIN iati_activitypolicymarker as pm on a.id = pm.activity_id '
},
'total-budget': {
'select': 'a.total_budget',
'type': 'sum',
'from_addition': ''
},
# 'recipient-country': {'select': 'a.id', 'type': 'count', 'from_addition': ''},
# 'recipient-region': {'select': 'a.id', 'type': 'count', 'from_addition': ''},
# 'year': {'select': 'a.id', 'type': 'count', 'from_addition': ''},
# 'sector': {'select': 'a.id', 'type': 'count', 'from_addition': ''},
}
group_by_element_dict = {
'recipient-country': {
'select':
'rc.country_id',
'from_addition':
'JOIN iati_activityrecipientcountry as rc on a.id = rc.activity_id '
},
'recipient-region': {
'select':
'rr.region_id',
'from_addition':
'JOIN iati_activityrecipientregion as rr on a.id = rr.activity_id '
},
'year': {
'select': 'YEAR(' + group_field + ')',
'from_addition': ''
},
'sector': {
'select':
'acts.sector_id',
'from_addition':
'JOIN iati_activitysector as acts on a.id = acts.activity_id '
},
'reporting-org': {
'select':
'a.reporting_organisation_id',
'from_addition':
'JOIN iati_organisation as o on a.reporting_organisation_id = o.code '
},
'participating-org': {
'select':
'po.name',
'from_addition':
'JOIN iati_activityparticipatingorganisation as po on a.id = po.activity_id '
},
'policy-marker': {
'select':
'pm.policy_marker_id',
'from_addition':
'JOIN iati_activitypolicymarker as pm on a.id = pm.activity_id '
},
}
# 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()
cursor = connection.cursor()
# get filters
reporting_organisations = helper.get_and_query(
request, 'reporting_organisation__in',
'a.reporting_organisation_id')
recipient_countries = helper.get_and_query(request, 'countries__in',
'rc.country_id')
recipient_regions = helper.get_and_query(request, 'regions__in',
'rr.region_id')
total_budgets = helper.get_and_query(request, 'total_budget__in',
'a.total_budget')
sectors = helper.get_and_query(request, 'sectors__in',
'acts.sector_id')
if aggregation_key in aggregation_element_dict:
aggregation_info = aggregation_element_dict[aggregation_key]
aggregation_key = aggregation_info["select"]
aggregation_type = aggregation_info["type"]
aggregation_from_addition = aggregation_info["from_addition"]
aggregation_where_addition = ""
if "where_addition" in aggregation_info:
aggregation_where_addition = aggregation_info["where_addition"]
else:
return HttpResponse(ujson.dumps({
"error":
"Invalid aggregation key, see included list for viable keys.",
"valid_aggregation_keys":
list(aggregation_element_dict.keys())
}),
content_type='application/json')
if group_by_key in group_by_element_dict:
group_by_info = group_by_element_dict[group_by_key]
group_select = group_by_info["select"]
group_from_addition = group_by_info["from_addition"]
else:
return HttpResponse(ujson.dumps({
"error":
"Invalid group by key, see included list for viable keys.",
"valid_group_by_keys":
list(group_by_element_dict.keys())
}),
content_type='application/json')
# make sure group key and aggregation key are set
if not group_by_key:
return HttpResponse(ujson.dumps(
"No field to group by. add parameter group_by (country/region/etc.. see docs)"
),
content_type='application/json')
if not aggregation_key:
return HttpResponse(ujson.dumps(
"No field to aggregate on. add parameter aggregation_key (iati-identifier/reporting-org/etc.. see docs)"
),
content_type='application/json')
#create the query
query_select = 'SELECT ' + aggregation_type + '(' + aggregation_key + ') as aggregation_field, ' + group_select + ' as group_field, o.name as org_name '
query_from = 'FROM iati_activity as a ' + aggregation_from_addition + group_from_addition
query_where = 'WHERE 1 ' + aggregation_where_addition
query_group_by = 'GROUP BY ' + group_select
# fill where part
filter_string = 'AND (' + reporting_organisations + recipient_countries + recipient_regions + total_budgets + sectors + ')'
if filter_string == 'AND ()':
filter_string = ""
else:
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
query_where += filter_string
# optimalisation for simple (all) queries
if not filter_string and query_from == 'FROM iati_activity as a ':
if (group_by_key == "country"):
query_select = 'SELECT count(activity_id) as aggregation_field, country_id as group_field '
query_from = "FROM iati_activityrecipientcountry "
query_group_by = "GROUP BY country_id"
elif (group_by_key == "region"):
query_select = 'SELECT count(activity_id) as aggregation_field, region_id as group_field '
query_from = "FROM iati_activityrecipientregion "
query_group_by = "GROUP BY region_id"
elif (group_by_key == "sector"):
query_select = 'SELECT count(activity_id) as aggregation_field, sector_id as group_field '
query_from = "FROM iati_activitysector "
query_group_by = "GROUP BY sector_id"
# execute query
cursor.execute(query_select + query_from + query_where +
query_group_by)
results1 = helper.get_fields(cursor=cursor)
# query result -> json output
options = {}
for r in results1:
options[r['group_field']] = [r['aggregation_field'], r['org_name']]
return HttpResponse(ujson.dumps(options),
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
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()
cursor = connection.cursor()
# get filters
reporting_organisations = helper.get_and_query(
request, 'reporting_organisation__in',
'a.reporting_organisation_id')
recipient_countries = helper.get_and_query(request, 'countries__in',
'rc.country_id')
recipient_regions = helper.get_and_query(request, 'regions__in',
'rr.region_id')
total_budgets = helper.get_and_query(request, 'total_budget__in',
'a.total_budget')
sectors = helper.get_and_query(request, 'sectors__in',
'acts.sector_id')
from_countries = False
from_regions = False
from_sectors = False
if recipient_countries:
from_countries = True
if recipient_regions:
from_regions = True
if sectors:
from_sectors = True
# get group by pars
group_by = request.GET.get(
"group_by",
None) # valid : country, region, year, sector, reporting org
field = request.GET.get(
"group_field", "start_actual"
) # used for year filtering, valid : start_planned, start_actual, end_planned, end_actual, defaults to start_actual
if not group_by:
return HttpResponse(ujson.dumps(
"No field to group by. add parameter group_by (country/region/etc.. see docs)"
),
content_type='application/json')
#create the query
query_select = 'SELECT count(a.id) as activity_count, '
query_from = 'FROM iati_activity as a '
query_where = 'WHERE 1 '
query_group_by = 'GROUP BY '
# fill select and group by
if (group_by == "country"):
query_select += 'rc.country_id as group_field '
query_group_by += 'rc.country_id '
from_countries = True
elif (group_by == "region"):
query_select += 'r.region_id as group_field '
query_group_by += 'r.region_id '
from_regions = True
elif (group_by == "year"):
query_select += 'YEAR(a.' + field + ') as group_field '
query_group_by += 'YEAR(a.' + field + ') '
elif (group_by == "sector"):
query_select += 'acts.sector_id as group_field '
query_group_by += 'acts.sector_id '
from_sectors = True
elif (group_by == "reporting_organisation"):
query_select += 'a.reporting_organisation_id as group_field '
query_group_by += 'a.reporting_organisation_id '
# fill from part
if from_countries:
query_from += "JOIN iati_activityrecipientcountry as rc on a.id = rc.activity_id "
if from_regions:
query_from += "JOIN iati_activityrecipientregion as rr on a.id = rr.activity_id "
if from_sectors:
query_from += "JOIN iati_activitysector as acts on a.id = acts.activity_id "
# fill where part
filter_string = 'AND (' + reporting_organisations + recipient_countries + recipient_regions + total_budgets + sectors + ')'
if filter_string == 'AND ()':
filter_string = ""
else:
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
query_where += filter_string
# optimalisation for simple (all) queries
if not filter_string:
# fill select and group by
if (group_by == "country"):
query_select = 'SELECT count(activity_id) as activity_count, country_id as group_field '
query_from = "FROM iati_activityrecipientcountry "
query_group_by = "GROUP BY country_id"
elif (group_by == "region"):
query_select = 'SELECT count(activity_id) as activity_count, region_id as group_field '
query_from = "FROM iati_activityrecipientregion "
query_group_by = "GROUP BY region_id"
elif (group_by == "sector"):
query_select = 'SELECT count(activity_id) as activity_count, sector_id as group_field '
query_from = "FROM iati_activitysector "
query_group_by = "GROUP BY sector_id"
# execute query
cursor.execute(query_select + query_from + query_where +
query_group_by)
results1 = helper.get_fields(cursor=cursor)
# query result -> json output
options = {}
for r in results1:
options[r['group_field']] = r['activity_count']
return HttpResponse(ujson.dumps(options),
content_type='application/json')
def get_list(self, request, **kwargs):
# get group by and aggregation pars
group_by_key = request.GET.get('group_by', None)
aggregation_key = request.GET.get('aggregation_key', 'iati-identifier')
group_field = request.GET.get('group_field', 'start_actual')
query = request.GET.get('query', '')
if group_by_key in {'commitment', 'disbursement', 'incoming-fund'}:
group_field = 't.value_date'
aggregation_element_dict = {
'iati-identifier': {
'select': 'a.id',
'type': 'count',
'from_addition': ''
},
'reporting-org': {
'select': 'a.reporting_organisation_id',
'type': 'count',
'from_addition': ''
},
'title': {
'select': 't.title',
'type': 'count',
'from_addition':
'JOIN iati_title as t on a.id = t.activity_id '
},
'description': {
'select':
'd.description',
'type':
'count',
'from_addition':
'JOIN iati_description as d on a.id = d.activity_id '
},
'commitment': {
'select': 't.value',
'type': 'sum',
'from_addition':
'JOIN iati_transaction as t on a.id = t.activity_id ',
'where_addition': 'AND t.transaction_type_id = "C" '
},
'disbursement': {
'select': 't.value',
'type': 'sum',
'from_addition':
'JOIN iati_transaction as t on a.id = t.activity_id ',
'where_addition': 'AND t.transaction_type_id = "D" '
},
'expenditure': {
'select': 't.value',
'type': 'sum',
'from_addition':
'JOIN iati_transaction as t on a.id = t.activity_id ',
'where_addition': 'AND t.transaction_type_id = "E" '
},
'incoming-fund': {
'select': 't.value',
'type': 'sum',
'from_addition':
'JOIN iati_transaction as t on a.id = t.activity_id ',
'where_addition': 'AND t.transaction_type_id = "IF" '
},
'location': {
'select': 'l.activity_id',
'type': 'count',
'from_addition':
'JOIN iati_location as l on a.id = l.activity_id '
},
'policy-marker': {
'select':
'pm.policy_marker_id',
'type':
'count',
'from_addition':
'JOIN iati_activitypolicymarker as pm on a.id = pm.activity_id '
},
'total-budget': {
'select': 'a.total_budget',
'type': 'sum',
'from_addition': ''
},
}
group_by_element_dict = {
'recipient-country': {
'select':
'rc.country_id',
'from_addition':
'JOIN iati_activityrecipientcountry as rc on a.id = rc.activity_id '
},
'recipient-region': {
'select':
'r.name, rr.region_id',
'from_addition':
'JOIN iati_activityrecipientregion as rr on a.id = rr.activity_id '
'join geodata_region as r on rr.region_id = r.code '
},
'year': {
'select': 'YEAR(' + group_field + ')',
'from_addition': ''
},
'sector': {
'select':
'acts.sector_id',
'from_addition':
'JOIN iati_activitysector as acts on a.id = acts.activity_id '
},
'reporting-org': {
'select': 'a.reporting_organisation_id',
'from_addition': ''
},
'participating-org': {
'select':
'po.name',
'from_addition':
'JOIN iati_activityparticipatingorganisation as po on a.id = po.activity_id '
},
'policy-marker': {
'select':
'pm.policy_marker_id',
'from_addition':
'JOIN iati_activitypolicymarker as pm on a.id = pm.activity_id '
},
'r.title': {
'select': 'r.title',
'from_addition':
'JOIN iati_result as r on a.id = r.activity_id ',
'where_addition': ' AND r.title = %(query)s '
},
}
helper = CustomCallHelper()
cursor = connection.cursor()
# get filters
reporting_organisations = helper.get_and_query(
request, 'reporting_organisation__in',
'a.reporting_organisation_id')
recipient_countries = helper.get_and_query(request, 'countries__in',
'rc.country_id')
recipient_regions = helper.get_and_query(request, 'regions__in',
'rr.region_id')
total_budgets = helper.get_and_query(request, 'total_budget__in',
'a.total_budget')
sectors = helper.get_and_query(request, 'sectors__in',
'acts.sector_id')
if aggregation_key in aggregation_element_dict:
aggregation_info = aggregation_element_dict[aggregation_key]
aggregation_key = aggregation_info["select"]
aggregation_type = aggregation_info["type"]
aggregation_from_addition = aggregation_info["from_addition"]
aggregation_where_addition = ""
if "where_addition" in aggregation_info:
aggregation_where_addition = aggregation_info["where_addition"]
else:
return HttpResponse(ujson.dumps({
"error":
"Invalid aggregation key, see included list for viable keys.",
"valid_aggregation_keys":
list(aggregation_element_dict.keys())
}),
content_type='application/json')
if group_by_key in group_by_element_dict:
group_by_info = group_by_element_dict[group_by_key]
group_select = group_by_info["select"]
group_from_addition = group_by_info["from_addition"]
if "where_addition" in group_by_info and query:
aggregation_where_addition = aggregation_where_addition.join(
group_by_info["where_addition"])
else:
return HttpResponse(ujson.dumps({
"error":
"Invalid group by key, see included list for viable keys.",
"valid_group_by_keys":
list(group_by_element_dict.keys())
}),
content_type='application/json')
# make sure group key and aggregation key are set
if not group_by_key:
return HttpResponse(ujson.dumps(
"No field to group by. add parameter group_by (country/region/etc.. see docs)"
),
content_type='application/json')
if not aggregation_key:
return HttpResponse(ujson.dumps(
"No field to aggregate on. add parameter aggregation_key "),
content_type='application/json')
query_select = ''.join([
'SELECT ', aggregation_type, '(', aggregation_key,
') as aggregation_field, ', group_select, ' as group_field '
])
query_from = ''.join([
'FROM iati_activity as a ', aggregation_from_addition,
group_from_addition
])
query_where = ''.join(['WHERE 1 ', aggregation_where_addition])
query_group_by = ''.join(['GROUP BY ', group_select])
# fill where part
filter_string = ''.join([
'AND (', reporting_organisations, recipient_countries,
recipient_regions, total_budgets, sectors, ')'
])
if filter_string == 'AND ()':
filter_string = ""
elif 'AND ()' in filter_string:
filter_string = filter_string[:-6]
query_where += filter_string
if not filter_string and query_from == 'FROM iati_activity as a ':
if group_by_key == "country":
query_select = 'SELECT count(activity_id) as aggregation_field, country_id as group_field '
query_from = "FROM iati_activityrecipientcountry "
query_group_by = "GROUP BY country_id"
elif group_by_key == "region":
query_select = 'SELECT count(activity_id) as aggregation_field, region_id as group_field '
query_from = "FROM iati_activityrecipientregion "
query_group_by = "GROUP BY region_id"
elif group_by_key == "sector":
query_select = 'SELECT count(activity_id) as aggregation_field, sector_id as group_field '
query_from = "FROM iati_activitysector "
query_group_by = "GROUP BY sector_id"
cursor.execute(
query_select + query_from + query_where + query_group_by, {
"query": query,
})
results1 = helper.get_fields(cursor=cursor)
options = []
for r in results1:
options.append(r)
return HttpResponse(ujson.dumps(options),
content_type='application/json')
