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')
else:
return super(ActivityResource, self).get_list(request, **kwargs)
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')
else:
return super(ActivityResource, self).get_list(request, **kwargs)
def update_existing_api_call_caches():
from cache.validator import Validator
v = Validator()
v.update_cache_calls()
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()
organisations = request.GET.get("reporting_organisation__in", 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_activity_recipient_country 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_activity_sector 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_activity_recipient_region rr on r.code = rr.region_id '
'LEFT JOIN IATI_activity a on rr.activity_id = a.id '
'WHERE r.source = "DAC" '
'%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 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), 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), mimetype='application/json')
helper = CustomCallHelper()
country_q = helper.get_and_query(request, 'countries__in', 'c.code')
budget_q_gte = request.GET.get('total_budget__gte', None)
budget_q_lte = request.GET.get('total_budget__lte', 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_activity_recipient_region 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_activity_sector 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_activity_recipient_country 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), mimetype='application/json')
def update_caches(self, request):
validator = Validator()
validator.update_cache_calls()
return HttpResponse('Success')
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):
# 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 update_existing_api_call_caches():
from cache.validator import Validator
v = Validator()
v.update_cache_calls()
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 handle(self, *args, **options):
v = Validator()
v.update_response_times_and_add_to_cache()
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 cache_long_api_calls():
from cache.validator import Validator
v = Validator()
v.update_response_times_and_add_to_cache()
def update_requests(self, request):
validator = Validator()
validator.update_response_times_and_add_to_cache()
return HttpResponse('Success')
def cache_all_requests(self, request):
validator = Validator()
validator.cache_all_requests()
return HttpResponse('Success')
def cache_long_api_calls():
from cache.validator import Validator
v = Validator()
v.update_response_times_and_add_to_cache()
def delete_all_under_x(self, request):
count = request.GET.get('count')
validator = Validator()
validator.delete_all_under_x(count)
return HttpResponse('Success')
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 handle(self, *args, **options):
v = Validator()
v.update_cache_calls()
