def get_list(self, request, **kwargs):
helper = CustomCallHelper()
country_id = request.GET.get("country_id", None)
cursor = connection.cursor()
cursor.execute('SELECT r.adm1_code, r.name, r.polygon, r.geometry_type '\
'FROM geodata_adm1region r '\
'WHERE r.country_id = "%s"' % (country_id))
activity_result = {'type' : 'FeatureCollection', 'features' : []}
activities = []
results = helper.get_fields(cursor=cursor)
for r in results:
region = {}
region['type'] = 'Feature'
region['geometry'] = {'type' : r['geometry_type'], 'coordinates' : r['polygon']}
region['properties'] = {'id' : r['adm1_code'], 'name' : r['name']}
activities.append(region)
result = {}
activity_result['features'] = activities
return HttpResponse(ujson.dumps(activity_result), content_type='application/json')
def get_list(self, request, **kwargs):
helper = CustomCallHelper()
cursor = connection.cursor()
organisations = request.GET.get("reporting_organisation__in", None)
if organisations:
q_organisations = 'AND a.reporting_organisation_id = "' + organisations + '"'
else:
q_organisations = ""
cursor.execute('SELECT a.id, r.code, r.name, t.title, a.total_budget '
'FROM iati_activity as a '
'JOIN iati_activityrecipientregion as rr on a.id = rr.activity_id '
'JOIN geodata_region as r on r.code = rr.region_id '
'JOIN iati_title as t on a.id = t.activity_id '
'WHERE 1 %s '
'order by a.id LIMIT 5000' % (q_organisations))
results1 = helper.get_fields(cursor=cursor)
activities = []
for r in results1:
activities.append(r)
return HttpResponse(ujson.dumps(activities), content_type='application/json')
def get_list(self, request, **kwargs):
helper = CustomCallHelper()
country_q = helper.get_and_query(request, 'countries__in', 'country.code')
region_q = helper.get_and_query(request, 'regions__in', 'region.code')
indicator_q = helper.get_and_query(request, 'indicators__in', 'i.indicator_id')
filter_string = ' AND (' + country_q + region_q + indicator_q + ')'
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
cursor = connection.cursor()
cursor.execute('SELECT DISTINCT i.indicator_id, ind.friendly_label, 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 '
'LEFT OUTER 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))
results = helper.get_fields(cursor)
regions = {}
countries = {}
indicators = {}
jsondata = {}
for r in 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)
indicator = {}
if r['indicator_id']:
indicator[r['indicator_id']] = r['friendly_label']
indicators.update(indicator)
jsondata['regions'] = regions
jsondata['countries'] = countries
jsondata['indicators'] = indicators
return HttpResponse(ujson.dumps(jsondata), mimetype='application/json')
def get_list(self, request, **kwargs):
helper = CustomCallHelper()
region_q = helper.get_and_query(request, 'regions__in', 'region.code')
indicator_q = helper.get_and_query(request, 'indicators__in', 'i.indicator_id')
filter_string = ' AND (' + region_q + indicator_q + ')'
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
cursor = connection.cursor()
cursor.execute('SELECT DISTINCT i.indicator_id ,ind.friendly_label,region.code as region_id, region.name as region_name '
'FROM indicator_indicatordata i '
'JOIN indicator_indicator ind ON i.indicator_id = ind.id '
'LEFT OUTER JOIN geodata_region region on i.region_id = region.code '
'WHERE 1 %s' % (filter_string))
desc = cursor.description
results = [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
regions = {}
countries = {}
indicators = {}
jsondata = {}
for r in results:
region = {}
if r['region_id']:
region[r['region_id']] = r['region_name']
regions.update(region)
indicator = {}
if r['indicator_id']:
indicator[r['indicator_id']] = r['friendly_label']
indicators.update(indicator)
jsondata['regions'] = regions
jsondata['indicators'] = indicators
return HttpResponse(ujson.dumps(jsondata), mimetype='application/json')
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'
print query
# 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()
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, 'cities__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:
indicator_q = ' indicator_id = "population"'
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, c.dac_region_code, '
'c.dac_region_name, id.value, id.year, AsText(c.center_longlat) as loc, c.code as country_id '
'FROM indicator_indicatordata id '
'LEFT OUTER JOIN geodata_city c ON id.country_id = c.code '
'LEFT OUTER JOIN indicator_indicator da ON da.id = id.indicator_id WHERE %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()
results = helper.get_fields(cursor)
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')
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')
if not indicator_q:
return HttpResponse(ujson.dumps("No indicator given"), content_type='application/json')
filter_string = ' (' + city_q + 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, ci.name as city_name, '
'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.city_id is not 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()
]
city = {}
for r in results:
try:
city[r['city_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
city[r['city_id']] = {'name' : r['city_name'], 'city_id' : r['city_id'], 'country_name' : r['country_name'], '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']
city[r['city_id']]['years'].update(year)
city['max_value'] = result_max[0]
return HttpResponse(ujson.dumps(city), 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):
# 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), mimetype='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)"), mimetype='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), mimetype='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')
if not indicator_q:
return HttpResponse(ujson.dumps("No indicator given"), mimetype='application/json')
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, 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' % (filter_string))
desc = cursor.description
city_results = [
dict(zip([col[0] for col in desc], row))
for row in cursor.fetchall()
]
# country indicator data
filter_string = 'AND (' + 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.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' % (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 (", "")
cursor_max = connection.cursor()
cursor_max.execute('SELECT indicator_id, max(value) as max_value FROM indicator_indicatordata WHERE %s GROUP BY indicator_indicatordata.indicator_id' % indicator_q)
desc = cursor_max.description
max_results = [
dict(zip([col[0] for col in desc], row))
for row in cursor_max.fetchall()
]
# region indicator data
# NOT IMPLEMENTED YET -> WE DO NOT HAVE CENTER LOCATIONS FOR REGIONS
geolocs = {}
for r in city_results:
try:
geolocs[r['indicator_id']]['cities'][r['city_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
if not r['indicator_id'] in geolocs:
for mr in max_results:
if mr['indicator_id']:
max_value = mr['max_value']
geolocs[r['indicator_id']] = {'indicator_friendly' : r['friendly_label'], 'type_data' : r['type_data'], 'indicator' : r['indicator_id'], 'selection_type' : r['selection_type'], 'max_value' : max_value, 'cities' : {}, 'countries' : {}}
geolocs[r['indicator_id']]['cities'][r['city_id']] = {'name' : r['city_name'], 'id' : r['city_id'], 'longitude' : longitude, 'latitude' : latitude, 'years' : {}}
geolocs[r['indicator_id']]['cities'][r['city_id']]['years'][r['year']] = r['value']
for c in country_results:
try:
geolocs[c['indicator_id']]['countries'][c['country_id']]['years']
except:
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
if not c['indicator_id'] in geolocs:
for mr in max_results:
if mr['indicator_id']:
max_value = mr['max_value']
geolocs[c['indicator_id']] = {'indicator_friendly' : c['friendly_label'], 'type_data' : c['type_data'] , 'indicator' : c['indicator_id'], 'selection_type' : c['selection_type'], 'max_value' : max_value, 'cities' : {}, 'countries' : {}}
geolocs[c['indicator_id']]['countries'][c['country_id']] = {'name' : c['country_name'], 'id' : c['country_id'], 'longitude' : longitude, 'latitude' : latitude, 'years' : {}}
geolocs[c['indicator_id']]['countries'][c['country_id']]['years'][c['year']] = c['value']
return HttpResponse(ujson.dumps(geolocs), mimetype='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')
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')
def get_list(self, request, **kwargs):
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.code')
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", "sector_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 + ')'
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
filter_country = ''
if country_q:
filter_country = 'LEFT JOIN iati_activityrecipientcountry rc ON rc.activity_id = a.id LEFT JOIN geodata_country c ON rc.region_id = c.code '
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 '
else:
filter_region = ''
if query:
filter_string += 'AND s.name LIKE "%%' + query + '%%" '
cursor = connection.cursor()
query = 'SELECT s.code as sector_id, s.name as sector_name, count(a.id) as total_projects, sum(a.total_budget) as total_budget '\
'FROM iati_activity a '\
'LEFT JOIN iati_activitysector acts ON acts.activity_id = a.id '\
'LEFT JOIN iati_sector s ON s.code = acts.sector_id '\
'%s %s '\
'WHERE s.code is not null %s'\
'GROUP BY s.code ' \
'ORDER BY %s %s ' \
'LIMIT %s OFFSET %s' % (filter_country, filter_region, filter_string, order_by, order_asc_desc, limit, offset)
cursor.execute(query)
activities = []
results = helper.get_fields(cursor=cursor)
for r in results:
sector = {}
sector['id'] = r['sector_id']
sector['name'] = r['sector_name']
sector['total_projects'] = r['total_projects']
sector['total_budget'] = r['total_budget']
activities.append(sector)
return_json = {}
return_json["objects"] = activities
cursor = connection.cursor()
query = 'SELECT s.code as sector_id '\
'FROM iati_activity a '\
'LEFT JOIN iati_activitysector acts ON acts.activity_id = a.id '\
'LEFT JOIN iati_sector s ON s.code = acts.sector_id '\
'%s %s '\
'WHERE s.code is not null %s'\
'GROUP BY s.code ' % (filter_country, filter_region, 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, "sector")
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", 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
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):
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):
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') 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):
# 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), mimetype='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())}), mimetype='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())}), mimetype='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)"), mimetype='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)"), mimetype='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]
print filter_string
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
print query_select + query_from + query_where + query_group_by
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), mimetype='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()
organisations = request.GET.get("reporting_organisation__in", None)
include_donors = request.GET.get("include_donor", None)
include_start_year_actual = request.GET.get("include_start_year_actual", None)
include_start_year_planned = request.GET.get("include_start_year_planned", None)
if organisations:
q_organisations = 'WHERE a.reporting_organisation_id = "' + organisations + '"'
else:
q_organisations = ""
cursor.execute('SELECT c.code, c.name, count(c.code) as total_amount '
'FROM geodata_country c '
'LEFT JOIN iati_activityrecipientcountry rc on c.code = rc.country_id '
'LEFT JOIN iati_activity a on rc.activity_id = a.id %s '
'GROUP BY c.code' % (q_organisations))
results1 = helper.get_fields(cursor=cursor)
cursor.execute('SELECT s.code, s.name, count(s.code) as total_amount '
'FROM iati_sector s '
'LEFT JOIN iati_activitysector as ias on s.code = ias.sector_id '
'LEFT JOIN iati_activity a on ias.activity_id = a.id '
'%s '
'GROUP BY s.code' % (q_organisations))
results2 = helper.get_fields(cursor=cursor)
if q_organisations:
q_organisations = q_organisations.replace("WHERE", "AND")
cursor.execute('SELECT r.code, r.name, count(r.code) as total_amount '
'FROM geodata_region r '
'LEFT JOIN iati_activityrecipientregion rr on r.code = rr.region_id '
'LEFT JOIN iati_activity a on rr.activity_id = a.id '
'WHERE r.region_vocabulary_id = 1 '
'%s '
'GROUP BY r.code' % (q_organisations))
results3 = helper.get_fields(cursor=cursor)
options = {}
options['countries'] = {}
options['regions'] = {}
options['sectors'] = {}
for r in results1:
country_item = {}
country_item['name'] = r['name']
country_item['total'] = r['total_amount']
options['countries'][r['code']] = country_item
for r in results2:
sector_item = {}
sector_item['name'] = r['name']
sector_item['total'] = r['total_amount']
options['sectors'][r['code']] = sector_item
for r in results3:
region_item = {}
region_item['name'] = r['name']
region_item['total'] = r['total_amount']
options['regions'][r['code']] = region_item
if include_donors:
options['donors'] = {}
cursor.execute('SELECT o.code, o.name, count(o.code) as total_amount '
'FROM iati_activity a '
'JOIN iati_activityparticipatingorganisation as po on a.id = po.activity_id '
'JOIN iati_organisation as o on po.organisation_id = o.code '
'WHERE 1 %s '
'GROUP BY o.code' % (q_organisations))
results4 = helper.get_fields(cursor=cursor)
for r in results4:
donor_item = {}
donor_item['name'] = r['name']
donor_item['total'] = r['total_amount']
options['donors'][r['code']] = donor_item
if include_start_year_actual:
options['start_actual'] = {}
cursor.execute('SELECT YEAR(a.start_actual) as start_year, count(YEAR(a.start_actual)) as total_amount '
'FROM iati_activity a '
'WHERE 1 %s '
'GROUP BY YEAR(a.start_actual)' % (q_organisations))
results5 = helper.get_fields(cursor=cursor)
for r in results5:
start_actual_item = {}
start_actual_item['name'] = r['start_year']
start_actual_item['total'] = r['total_amount']
options['start_actual'][r['start_year']] = start_actual_item
if include_start_year_planned:
options['start_planned_years'] = {}
cursor.execute('SELECT YEAR(a.start_planned) as start_year, count(YEAR(a.start_planned)) as total_amount '
'FROM iati_activity a '
'WHERE 1 %s '
'GROUP BY YEAR(a.start_planned)' % (q_organisations))
results5 = helper.get_fields(cursor=cursor)
for r in results5:
start_planned_item = {}
start_planned_item['name'] = r['start_year']
start_planned_item['total'] = r['total_amount']
options['start_planned_years'][r['start_year']] = start_planned_item
if not q_organisations:
cursor.execute('SELECT a.reporting_organisation_id, o.name, count(a.reporting_organisation_id) as total_amount '
'FROM iati_activity a '
'INNER JOIN iati_organisation o on a.reporting_organisation_id = o.code '
'GROUP BY a.reporting_organisation_id')
results4 = helper.get_fields(cursor=cursor)
options['reporting_organisations'] = {}
for r in results4:
org_item = {}
org_item['name'] = r['name']
org_item['total'] = r['total_amount']
options['reporting_organisations'][r['reporting_organisation_id']] = org_item
return HttpResponse(ujson.dumps(options), 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):
# 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 = ''
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 + ')'
if 'AND ()' in filter_string:
filter_string = filter_string[:-6]
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 '
else:
filter_region = ''
if sector_q:
filter_sector = 'LEFT JOIN iati_activitysector s ON a.id = s.activity_id '
else:
filter_sector = ''
cursor = connection.cursor()
query = 'SELECT c.code as country_id, c.name as country_name, count(a.id) as total_projects '\
'FROM iati_activity a '\
'LEFT JOIN iati_activityrecipientcountry rc ON rc.activity_id = a.id '\
'LEFT JOIN geodata_country c ON rc.country_id = c.code '\
'%s %s'\
'WHERE 1 %s'\
'GROUP BY c.code' % (filter_region, filter_sector, filter_string)
cursor.execute(query)
activity_result = {'type': 'FeatureCollection', 'features': []}
activities = []
results = helper.get_fields(cursor=cursor)
for r in results:
country = {}
country['type'] = 'Feature'
country['id'] = r['country_id']
country['properties'] = {'name' : r['country_name'], 'project_amount' : r['total_projects']}
country['geometry'] = helper.find_polygon(r['country_id'])
activities.append(country)
result = {}
activity_result['features'] = activities
return HttpResponse(ujson.dumps(activity_result), 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):
# 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')