def api_cspy(request, trade_flow, country1, product, year): """ country / show / product / year """ prod_class = request.GET.get( "prod_class", request.session.get('product_classification', 'hs4')) lang = helpers.get_language(request)['code'] name = "name_%s" % lang product = helpers.get_product_by_code(product, prod_class) country1 = Country.objects.get(name_3char=country1) single_year = "single_year" in request.GET query_params = request.GET.copy() query_params["lang"] = lang query_params["product_classification"] = prod_class region = helpers.get_region_list() continents = helpers.get_continent_list() if prod_class == "sitc4": items = Sitc4_ccpy.objects else: items = Hs4_ccpy.objects items = calculate_volume(items, trade_flow) items = items.extra(select={'name': name}) items = items.values_list('year', 'destination__id', 'destination__name_3char', 'name', 'destination__region_id', 'destination__continent', 'val') items = items.filter(origin_id=country1.id, product_id=product.id) if single_year: items = items.filter(year=year) json_response = {} key = "%s:%s:%s:%s:%s" % (country1.name_3char, "show", product.id, prod_class, trade_flow) cached_data = cache.get(key) if cached_data is not None: json_response['data'] = msgpack.loads(cached_data) else: rows = list(items) total_val = sum([r[6] for r in rows]) rows = [{ "year": r[0], "item_id": r[1], "abbrv": r[2], "name": r[3], "value": r[6], "share": (r[6] / total_val) * 100, "region_id": r[4], "continent": r[5], "id": r[1] } for r in rows] json_response["data"] = rows cache.set(key, msgpack.dumps(rows), settings.CACHE_LONG) years_available = helpers.get_years_available(prod_class=prod_class) inflation_adjustment = helpers.get_inflation_adjustment( country1, years_available[0], years_available[-1]) json_response["magic_numbers"] = inflation_adjustment json_response["attr_data"] = Country.objects.get_all(lang) article = "to" if trade_flow == "export" else "from" json_response["title"] = "Where does %s %s %s %s?" % ( country1.name, trade_flow, product.name_en, article) json_response["country1"] = country1.to_json() json_response["product"] = product.to_json() json_response["year"] = year json_response["item_type"] = "country" json_response["continents"] = continents json_response["region"] = region json_response["app_type"] = "cspy" json_response["class"] = prod_class json_response["other"] = query_params if (request.GET.get('data_type', None) is None): return HttpResponse("") elif (request.GET.get('data_type', '') == 'json'): return HttpResponse(json.dumps(json_response))
def api_csay(request, trade_flow, country1, year): """<COUNTRY> / show / all / <YEAR>""" prod_class = request.GET.get( "prod_class", request.session.get('product_classification', 'hs4')) lang = helpers.get_language(request)['code'] name = "name_%s" % lang country1 = Country.objects.get(name_3char=country1) region = helpers.get_region_list() continents = helpers.get_continent_list() if prod_class == "sitc4": items = Sitc4_ccpy.objects else: items = Hs4_ccpy.objects items = calculate_volume(items, trade_flow=trade_flow, sum_val=True) items = items.extra(select={'name': name}) items = items.values_list('year', 'destination__id', 'destination__name_3char', 'name', 'destination__region_id', 'destination__continent', 'val') items = items.filter(origin_id=country1.id) json_response = {} key = "%s:%s:%s:%s:%s" % (country1.name_3char, "show", "all", prod_class, trade_flow) cached_data = cache.get(key) if cached_data is not None: json_response["data"] = msgpack.loads(cached_data) else: # This might possibly be the most disgusting hack ever made, simply # because when doing an aggregate (like SUM()) in extra, django does # not add that stuff correctly into the group by. It's also not # possible to use annotate() here because it's a complex aggregate that # uses addition / subtraction. C'est la vie :( cursor = connection.cursor() if trade_flow == "net_export": val = '`export_value` - `import_value`' elif trade_flow == "net_import": val = '`import_value` - `export_value`' elif trade_flow == "export": val = '`export_value`' else: val = '`import_value`' complete_query = str(items.query) + \ " group by `year`, `destination_id`" + \ " HAVING sum(%s) > 0" % val cursor.execute(complete_query) rows = cursor.fetchall() total_val = sum([r[0] for r in rows]) rows = [{ "year": r[2], "item_id": r[3], "abbrv": r[4], "name": r[1], "value": r[0], "share": (r[0] / total_val) * 100, "id": r[3], "region_id": r[5], "continent": r[6] } for r in rows] cache.set(key, msgpack.dumps(rows), settings.CACHE_LONG) json_response["data"] = rows years_available = helpers.get_years_available() magic_numbers = helpers.get_inflation_adjustment(country1, years_available[0], years_available[-1]) query_params = request.GET.copy() query_params["lang"] = lang query_params["product_classification"] = prod_class json_response["attr_data"] = Country.objects.get_all(lang) json_response["country1"] = country1.to_json() article = "to" if trade_flow == "export" else "from" json_response["title"] = "Where does %s %s %s?" % (country1.name, trade_flow, article) json_response["year"] = year json_response["item_type"] = "country" json_response["app_type"] = "csay" json_response["region"] = region json_response["continents"] = continents json_response["prod_class"] = prod_class json_response["magic_numbers"] = magic_numbers json_response["other"] = query_params # raise Exception(time.time() - start) # Check the request data type if (request.GET.get('data_type', None) is None): #"""Return to browser as JSON for AJAX request""" return HttpResponse("") elif (request.GET.get('data_type', '') == 'json'): """Return to browser as JSON for AJAX request""" return HttpResponse(json.dumps(json_response))
def api_sapy(request, trade_flow, product, year): """show / all / <product> / <year>""" # Get session / request vars prod_class = request.GET.get( "prod_class", request.session.get('product_classification', 'hs4')) lang = helpers.get_language(request)['code'] name = "name_%s" % lang single_year = 'single_year' in request.GET product = helpers.get_product_by_code(product, prod_class) query_params = request.GET.copy() query_params["lang"] = lang query_params["product_classification"] = prod_class region = helpers.get_region_list() continents = helpers.get_continent_list() attr = helpers.get_attrs(prod_class=prod_class, name=name) if prod_class == "sitc4": items = Sitc4_cpy.objects else: items = Hs4_cpy.objects items = calculate_volume(items, trade_flow=trade_flow) items = calculate_rca(items, trade_flow=trade_flow) items = items.extra(select={'name': name}) items = items.values_list('year', 'country__id', 'country__name_3char', 'name', 'country__region_id', 'country__continent', 'val', 'rca') if single_year: items = items.filter(year=year) items = items.filter(product_id=product.id) json_response = {} # Generate cache key key = "%s:%s:%s:%s:%s" % ("show", "all", product.id, prod_class, trade_flow) if single_year: key += ":%d" % int(year) # Check cache cached_data = cache.get(key) if cached_data is not None: json_response["data"] = msgpack.loads(cached_data) else: rows = list(items) total_val = sum([r[6] for r in rows]) rows = [{ "year": r[0], "item_id": r[1], "abbrv": r[2], "name": r[3], "value": r[6], "rca": r[7], "share": (r[6] / total_val) * 100, "id": r[1], "region_id": r[4], "continent": r[5] } for r in rows] # Save in cache cache.set(key, msgpack.dumps(rows), settings.CACHE_LONG) json_response["data"] = rows json_response["attr_data"] = Country.objects.get_all(lang) json_response["product"] = product.to_json() json_response["title"] = "Who %sed %s?" % (trade_flow.replace( "_", " "), product.name_en) json_response["year"] = year json_response["item_type"] = "country" json_response["app_type"] = "sapy" json_response["region"] = region json_response["continents"] = continents json_response["other"] = query_params json_response["prod_class"] = prod_class if (request.GET.get('data_type', None) is None): return HttpResponse("") elif (request.GET.get('data_type', '') == 'json'): """Return to browser as JSON for AJAX request""" return HttpResponse(json.dumps(json_response))
def api_cspy(request, trade_flow, country1, product, year): """ country / show / product / year """ prod_class = request.GET.get("prod_class", request.session.get('product_classification', 'hs4')) lang = helpers.get_language(request)['code'] name = "name_%s" % lang product = helpers.get_product_by_code(product, prod_class) country1 = Country.objects.get(name_3char=country1) single_year = "single_year" in request.GET query_params = request.GET.copy() query_params["lang"] = lang query_params["product_classification"] = prod_class region = helpers.get_region_list() continents = helpers.get_continent_list() if prod_class == "sitc4": items = Sitc4_ccpy.objects else: items = Hs4_ccpy.objects items = calculate_volume(items, trade_flow) items = items.extra(select={'name': name}) items = items.values_list('year', 'destination__id', 'destination__name_3char', 'name', 'destination__region_id', 'destination__continent', 'val') items = items.filter(origin_id=country1.id, product_id=product.id) if single_year: items = items.filter(year=year) json_response = {} key = "%s:%s:%s:%s:%s" % (country1.name_3char, "show", product.id, prod_class, trade_flow) cached_data = cache.get(key) if cached_data is not None: json_response['data'] = msgpack.loads(cached_data) else: rows = list(items) total_val = sum([r[6] for r in rows]) rows = [ {"year": r[0], "item_id": r[1], "abbrv": r[2], "name": r[3], "value": r[6], "share": (r[6] / total_val) * 100, "region_id": r[4], "continent": r[5], "id": r[1]} for r in rows] json_response["data"] = rows cache.set(key, msgpack.dumps(rows), settings.CACHE_LONG) years_available = helpers.get_years_available(prod_class=prod_class) inflation_adjustment = helpers.get_inflation_adjustment( country1, years_available[0], years_available[-1]) json_response["magic_numbers"] = inflation_adjustment json_response["attr_data"] = Country.objects.get_all(lang) article = "to" if trade_flow == "export" else "from" json_response["title"] = "Where does %s %s %s %s?" % ( country1.name, trade_flow, product.name_en, article) json_response["country1"] = country1.to_json() json_response["product"] = product.to_json() json_response["year"] = year json_response["item_type"] = "country" json_response["continents"] = continents json_response["region"] = region json_response["app_type"] = "cspy" json_response["class"] = prod_class json_response["other"] = query_params if (request.GET.get('data_type', None) is None): return HttpResponse("") elif (request.GET.get('data_type', '') == 'json'): return HttpResponse(json.dumps(json_response))
def api_csay(request, trade_flow, country1, year): """<COUNTRY> / show / all / <YEAR>""" prod_class = request.GET.get("prod_class", request.session.get('product_classification', 'hs4')) lang = helpers.get_language(request)['code'] name = "name_%s" % lang country1 = Country.objects.get(name_3char=country1) region = helpers.get_region_list() continents = helpers.get_continent_list() if prod_class == "sitc4": items = Sitc4_ccpy.objects else: items = Hs4_ccpy.objects items = calculate_volume(items, trade_flow=trade_flow, sum_val=True) items = items.extra(select={'name': name}) items = items.values_list('year', 'destination__id', 'destination__name_3char', 'name', 'destination__region_id', 'destination__continent', 'val') items = items.filter(origin_id=country1.id) json_response = {} key = "%s:%s:%s:%s:%s" % (country1.name_3char, "show", "all", prod_class, trade_flow) cached_data = cache.get(key) if cached_data is not None: json_response["data"] = msgpack.loads(cached_data) else: # This might possibly be the most disgusting hack ever made, simply # because when doing an aggregate (like SUM()) in extra, django does # not add that stuff correctly into the group by. It's also not # possible to use annotate() here because it's a complex aggregate that # uses addition / subtraction. C'est la vie :( cursor = connection.cursor() if trade_flow == "net_export": val = '`export_value` - `import_value`' elif trade_flow == "net_import": val = '`import_value` - `export_value`' elif trade_flow == "export": val = '`export_value`' else: val = '`import_value`' complete_query = str(items.query) + \ " group by `year`, `destination_id`" + \ " HAVING sum(%s) > 0" % val cursor.execute(complete_query) rows = cursor.fetchall() total_val = sum([r[0] for r in rows]) rows = [ {"year": r[2], "item_id": r[3], "abbrv": r[4], "name": r[1], "value": r[0], "share": (r[0] / total_val) * 100, "id": r[3], "region_id": r[5], "continent": r[6]} for r in rows] cache.set(key, msgpack.dumps(rows), settings.CACHE_LONG) json_response["data"] = rows years_available = helpers.get_years_available() magic_numbers = helpers.get_inflation_adjustment(country1, years_available[0], years_available[-1]) query_params = request.GET.copy() query_params["lang"] = lang query_params["product_classification"] = prod_class json_response["attr_data"] = Country.objects.get_all(lang) json_response["country1"] = country1.to_json() article = "to" if trade_flow == "export" else "from" json_response["title"] = "Where does %s %s %s?" % ( country1.name, trade_flow, article) json_response["year"] = year json_response["item_type"] = "country" json_response["app_type"] = "csay" json_response["region"] = region json_response["continents"] = continents json_response["prod_class"] = prod_class json_response["magic_numbers"] = magic_numbers json_response["other"] = query_params # raise Exception(time.time() - start) # Check the request data type if (request.GET.get('data_type', None) is None): #"""Return to browser as JSON for AJAX request""" return HttpResponse("") elif (request.GET.get('data_type', '') == 'json'): """Return to browser as JSON for AJAX request""" return HttpResponse(json.dumps(json_response))
def api_sapy(request, trade_flow, product, year): """show / all / <product> / <year>""" # Get session / request vars prod_class = request.GET.get("prod_class", request.session.get('product_classification', 'hs4')) lang = helpers.get_language(request)['code'] name = "name_%s" % lang single_year = 'single_year' in request.GET product = helpers.get_product_by_code(product, prod_class) query_params = request.GET.copy() query_params["lang"] = lang query_params["product_classification"] = prod_class region = helpers.get_region_list() continents = helpers.get_continent_list() attr = helpers.get_attrs(prod_class=prod_class, name=name) if prod_class == "sitc4": items = Sitc4_cpy.objects else: items = Hs4_cpy.objects items = calculate_volume(items, trade_flow=trade_flow) items = calculate_rca(items, trade_flow=trade_flow) items = items.extra(select={'name': name}) items = items.values_list('year', 'country__id', 'country__name_3char', 'name', 'country__region_id', 'country__continent', 'val', 'rca') if single_year: items = items.filter(year=year) items = items.filter(product_id=product.id) json_response = {} # Generate cache key key = "%s:%s:%s:%s:%s" % ("show", "all", product.id, prod_class, trade_flow) if single_year: key += ":%d" % int(year) # Check cache cached_data = cache.get(key) if cached_data is not None: json_response["data"] = msgpack.loads(cached_data) else: rows = list(items) total_val = sum([r[6] for r in rows]) rows = [{"year": r[0], "item_id": r[1], "abbrv": r[2], "name": r[3], "value": r[6], "rca": r[7], "share": (r[6] / total_val) * 100, "id": r[1], "region_id": r[4], "continent": r[5]} for r in rows] # Save in cache cache.set(key, msgpack.dumps(rows), settings.CACHE_LONG) json_response["data"] = rows json_response["attr_data"] = Country.objects.get_all(lang) json_response["product"] = product.to_json() json_response["title"] = "Who %sed %s?" % ( trade_flow.replace("_", " "), product.name_en) json_response["year"] = year json_response["item_type"] = "country" json_response["app_type"] = "sapy" json_response["region"] = region json_response["continents"] = continents json_response["other"] = query_params json_response["prod_class"] = prod_class if (request.GET.get('data_type', None) is None): return HttpResponse("") elif (request.GET.get('data_type', '') == 'json'): """Return to browser as JSON for AJAX request""" return HttpResponse(json.dumps(json_response))