コード例 #1
0
ファイル: sql_resources.py プロジェクト: DanRunfola/OIPA
    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')
コード例 #2
0
ファイル: sql_resources.py プロジェクト: DanRunfola/OIPA
    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')
コード例 #3
0
    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')
コード例 #4
0
    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')
コード例 #5
0
    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')
コード例 #6
0
    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')
コード例 #7
0
ファイル: indicator_resources.py プロジェクト: Jaspermid/OIPA
    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')
コード例 #8
0
ファイル: indicator_resources.py プロジェクト: Jaspermid/OIPA
    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')
コード例 #9
0
    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')
コード例 #10
0
    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')
コード例 #11
0
ファイル: sql_resources.py プロジェクト: DanRunfola/OIPA
    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')
コード例 #12
0
    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')
コード例 #13
0
    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')
コード例 #14
0
    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')
コード例 #15
0
    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')
コード例 #16
0
ファイル: sql_resources.py プロジェクト: DanRunfola/OIPA
    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')
コード例 #17
0
ファイル: sql_resources.py プロジェクト: DanRunfola/OIPA
    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')
コード例 #18
0
ファイル: sql_resources.py プロジェクト: DanRunfola/OIPA
    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')
コード例 #19
0
ファイル: indicator_resources.py プロジェクト: Jaspermid/OIPA
    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')
コード例 #20
0
ファイル: indicator_resources.py プロジェクト: Jaspermid/OIPA
    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')
コード例 #21
0
    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')
コード例 #22
0
    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')
コード例 #23
0
ファイル: sql_resources.py プロジェクト: DanRunfola/OIPA
    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')
コード例 #24
0
    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')
コード例 #25
0
ファイル: sql_resources.py プロジェクト: DanRunfola/OIPA
    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')
コード例 #26
0
    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')
コード例 #27
0
ファイル: sql_resources.py プロジェクト: DanRunfola/OIPA
    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')