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