def org_details(request, format=None): ''' Get list size and ASTRO-PU by month, for CCGs or practices. ''' org_type = request.GET.get('org_type', None) keys = utils.param_to_list(request.query_params.get('keys', [])) org_codes = utils.param_to_list(request.query_params.get('org', [])) if org_type is None: org_type = 'all_practices' orgs = _get_orgs(org_type, org_codes) data = _get_practice_stats_entries(keys, org_type, orgs) return Response(list(data))
def spending_by_org(request, format=None, org_type=None): codes = utils.param_to_list(request.query_params.get('code', [])) codes = utils.get_bnf_codes_from_number_str(codes) org_ids = utils.param_to_list(request.query_params.get('org', [])) org_type = request.query_params.get('org_type', org_type) date = request.query_params.get('date', None) # Accept both cases of CCG (better to fix this specific string rather than # make the whole API case-insensitive) if org_type == 'CCG': org_type = 'ccg' # Some special case handling for practices if org_type == 'practice': # Translate any CCG codes into the codes of all practices in that CCG org_ids = utils.get_practice_ids_from_org(org_ids) # Due to the number of practices we only return data for all practices # if a single date is specified if not date and not org_ids: return Response( 'Error: You must supply either a list of practice IDs or a date ' 'parameter, e.g. date=2015-04-01', status=400) # We don't (yet?) have a "proper" code field for STPs so we're using their # ONS code code_field = 'code' if org_type != 'stp' else 'ons_code' if org_type == 'practice': orgs = Practice.objects.all() elif org_type == 'ccg': orgs = PCT.objects.filter(org_type='CCG') elif org_type == 'stp': orgs = STP.objects.all() elif org_type == 'regional_team': orgs = RegionalTeam.objects.all() else: return Response('Error: unrecognised org_type parameter', status=400) # Filter and sort if org_ids: orgs = orgs.filter(**{code_field + '__in': org_ids}) orgs = orgs.order_by(code_field) # For most orgs we just want the code and name but for practices we want # the entire object because, for compatibility with the existing API, we # return extra data for practices if org_type != 'practice': orgs = orgs.only(code_field, 'name') data = _get_prescribing_entries(codes, orgs, org_type, date=date) return Response(list(data))
def org_codes(request, format=None): org_codes = utils.param_to_list(request.query_params.get('q', None)) org_types = utils.param_to_list(request.query_params.get('org_type', None)) is_exact = request.GET.get('exact', '') if not org_types: org_types = [''] if not org_codes: org_codes = [''] data = [] for org_code in org_codes: for org_type in org_types: data += _get_org_from_code(org_code, is_exact, org_type) return Response(data)
def org_location(request, format=None): org_type = request.GET.get('org_type', '') org_codes = utils.param_to_list(request.GET.get('q', '')) if org_type == 'practice': org_codes = utils.get_practice_ids_from_org(org_codes) if org_type == 'ccg': results = PCT.objects.filter(close_date__isnull=True) \ .filter(org_type='CCG') if org_codes: results = results.filter(code__in=org_codes) geo_field = 'boundary' fields = ( 'name', 'code', 'ons_code', 'org_type', 'boundary', ) else: results = Practice.objects.filter(code__in=org_codes) geo_field = 'location' fields = ( 'name', 'code', 'setting', 'is_dispensing', 'location', ) return HttpResponse(serialize('geojson', results, geometry_field=geo_field, fields=fields), content_type='application/json')
def total_spending(request, format=None): codes = utils.param_to_list(request.query_params.get('code', [])) codes = utils.get_bnf_codes_from_number_str(codes) subdivide = request.GET.get('subdivide', None) if subdivide: if codes: if len(codes) > 1: err = 'Error: You can only subdivide a single code' return Response(err, status=400) elif len(codes[0]) > 11: err = 'Error: Code to subdivide must be 11 characters or fewer' return Response(err, status=400) spending_type = utils.get_spending_type(codes) if spending_type is False: err = 'Error: Codes must all be the same length' return Response(err, status=400) if subdivide: query = _get_query_for_total_spending_with_subdivide(codes) else: query = _get_query_for_total_spending(codes) if spending_type != 'presentation': codes = [c + '%' for c in codes] data = utils.execute_query(query, [codes]) return Response(data)
def bnf_codes(request, format=None): codes = utils.param_to_list(request.query_params.get('q', [])) is_exact = request.GET.get('exact', None) is_exact = (is_exact == 'true') querysets = _get_matching_products(codes, is_exact) data = _convert_querysets(querysets) return Response(data)
def bnf_codes(request, format=None): codes = utils.param_to_list(request.query_params.get('q', [])) codes = [c.upper() for c in codes] is_exact = request.GET.get('exact', None) is_exact = (is_exact == 'true') querysets = _get_matching_products(codes, is_exact) data = _convert_querysets(querysets) return Response(data)
def org_details(request, format=None): ''' Get list size and ASTRO-PU by month, for CCGs or practices. ''' org_type = request.GET.get('org_type', None) orgs = utils.param_to_list(request.query_params.get('org', [])) if org_type == 'practice': orgs = utils.get_practice_ids_from_org(orgs) query = "SELECT pr.date as date, pr.practice_id as row_id, " query += "pc.name as row_name, " query += "pr.total_list_size, pr.astro_pu_cost, " query += "pr.astro_pu_items, pr.star_pu_oral_antibac_items " query += "FROM frontend_practicelist pr " query += "JOIN frontend_practice pc ON pr.practice_id=pc.code " if orgs: query += "WHERE " for i, c in enumerate(orgs): query += "pr.practice_id=%s " if (i != len(orgs)-1): query += ' OR ' query += "ORDER BY date, row_id" else: query += "ORDER BY date, row_id" elif org_type == 'ccg': query = "SELECT pr.date, pr.pct_id as row_id, " query += "pc.name as row_name, " query += "SUM(pr.total_list_size) AS total_list_size, " query += "SUM(pr.astro_pu_cost) AS astro_pu_cost, " query += "SUM(pr.astro_pu_items) AS astro_pu_items, " query += "SUM(pr.star_pu_oral_antibac_items) " query += "AS star_pu_oral_antibac_items " query += "FROM frontend_practicelist pr " query += "JOIN frontend_pct pc ON pr.pct_id=pc.code " query += "WHERE pc.org_type='CCG' " if orgs: query += "AND (" for i, c in enumerate(orgs): query += "pct_id=%s " if (i != len(orgs)-1): query += ' OR ' query += ') ' query += "GROUP BY pr.pct_id, pc.name, date ORDER BY date, row_id" else: query += "GROUP BY pr.pct_id, pc.name, date ORDER BY date, row_id" else: # Total across NHS England. query = "SELECT date, SUM(total_list_size) as total_list_size, " query += "SUM(astro_pu_cost) AS astro_pu_cost, " query += "SUM(astro_pu_items) AS astro_pu_items, " query += "SUM(star_pu_oral_antibac_items) " query += "AS star_pu_oral_antibac_items " query += "FROM frontend_practicelist " query += "GROUP BY date ORDER BY date" data = utils.execute_query(query, [orgs]) return Response(data)
def org_details(request, format=None): ''' Get list size and ASTRO-PU by month, for CCGs or practices. ''' org_type = request.GET.get('org_type', None) orgs = utils.param_to_list(request.query_params.get('org', [])) if org_type == 'practice': query = "SELECT pr.date as date, pr.practice_id as row_id, " query += "pc.name as row_name, " query += "pr.total_list_size, pr.astro_pu_cost, " query += "pr.astro_pu_items, pr.star_pu " query += "FROM frontend_practicestatistics pr " query += "JOIN frontend_practice pc ON pr.practice_id=pc.code " if orgs: query += "WHERE " for i, c in enumerate(orgs): if len(c) == 3: query += 'pr.pct_id=%s ' else: query += "pr.practice_id=%s " if (i != len(orgs) - 1): query += ' OR ' query += "ORDER BY date, row_id" else: query += "ORDER BY date, row_id" elif org_type == 'ccg': query = 'SELECT pct_id AS row_id, name as row_name, *' query += ' FROM vw__ccgstatistics ' if orgs: query += "WHERE (" for i, c in enumerate(orgs): query += "pct_id=%s " if (i != len(orgs) - 1): query += ' OR ' query += ') ' query += 'ORDER BY date' else: # Total across NHS England. query = 'SELECT date, ' query += 'AVG(total_list_size) AS total_list_size, ' query += 'AVG(astro_pu_items) AS astro_pu_items, ' query += 'AVG(astro_pu_cost) AS astro_pu_cost, ' query += 'json_object_agg(key, val) AS star_pu ' query += 'FROM (' query += 'SELECT date, ' query += 'SUM(total_list_size) AS total_list_size, ' query += 'SUM(astro_pu_items) AS astro_pu_items, ' query += 'SUM(astro_pu_cost) AS astro_pu_cost, ' query += 'key, SUM(value::numeric) val ' query += 'FROM vw__ccgstatistics p, json_each_text(star_pu) ' query += 'GROUP BY date, key ' query += ') p ' query += 'GROUP BY date ORDER BY date' data = utils.execute_query(query, [orgs]) return Response(data)
def org_details(request, format=None): ''' Get list size and ASTRO-PU by month, for CCGs or practices. ''' org_type = request.GET.get('org_type', None) orgs = utils.param_to_list(request.query_params.get('org', [])) if org_type == 'practice': query = "SELECT pr.date as date, pr.practice_id as row_id, " query += "pc.name as row_name, " query += "pr.total_list_size, pr.astro_pu_cost, " query += "pr.astro_pu_items, pr.star_pu " query += "FROM frontend_practicestatistics pr " query += "JOIN frontend_practice pc ON pr.practice_id=pc.code " if orgs: query += "WHERE " for i, c in enumerate(orgs): if len(c) == 3: query += 'pr.pct_id=%s ' else: query += "pr.practice_id=%s " if (i != len(orgs)-1): query += ' OR ' query += "ORDER BY date, row_id" else: query += "ORDER BY date, row_id" elif org_type == 'ccg': query = 'SELECT pct_id AS row_id, name as row_name, *' query += ' FROM vw__ccgstatistics ' if orgs: query += "WHERE (" for i, c in enumerate(orgs): query += "pct_id=%s " if (i != len(orgs)-1): query += ' OR ' query += ') ' query += 'ORDER BY date' else: # Total across NHS England. query = 'SELECT date, ' query += 'AVG(total_list_size) AS total_list_size, ' query += 'AVG(astro_pu_items) AS astro_pu_items, ' query += 'AVG(astro_pu_cost) AS astro_pu_cost, ' query += 'json_object_agg(key, val) AS star_pu ' query += 'FROM (' query += 'SELECT date, ' query += 'SUM(total_list_size) AS total_list_size, ' query += 'SUM(astro_pu_items) AS astro_pu_items, ' query += 'SUM(astro_pu_cost) AS astro_pu_cost, ' query += 'key, SUM(value::numeric) val ' query += 'FROM vw__ccgstatistics p, json_each_text(star_pu) ' query += 'GROUP BY date, key ' query += ') p ' query += 'GROUP BY date ORDER BY date' data = utils.execute_query(query, [orgs]) return Response(data)
def measure_by_ccg(request, format=None): measure_id = request.query_params.get('measure', None) org_ids = utils.param_to_list(request.query_params.get('org', [])) tags = [x for x in request.query_params.get('tags', '').split(',') if x] rolled = {} measure_values = MeasureValue.objects.by_ccg(org_ids, measure_id, tags) rsp_data = {'measures': _roll_up_measure_values(measure_values, 'ccg')} return Response(rsp_data)
def spending_by_practice(request, format=None): codes = utils.param_to_list(request.query_params.get('code', [])) codes = utils.get_bnf_codes_from_number_str(codes) orgs = utils.param_to_list(request.query_params.get('org', [])) date = request.query_params.get('date', None) spending_type = utils.get_spending_type(codes) if spending_type is False: err = 'Error: Codes must all be the same length' return Response(err, status=400) if spending_type == 'bnf-section' or spending_type == 'product': codes = [c + '%' for c in codes] if not date and not orgs: err = 'Error: You must supply either ' err += 'a list of practice IDs or a date parameter, e.g. ' err += 'date=2015-04-01' return Response(err, status=400) org_for_param = None if not spending_type or spending_type == 'bnf-section' \ or spending_type == 'chemical': # We can do presentation queries indexed by PCT ID, which is faster. # We have yet to update the *_by_practice matviews with PCT ID. # So for these queries, expand the CCG ID to a list of practice IDs. expanded_orgs = utils.get_practice_ids_from_org(orgs) if codes: query = _get_chemicals_or_sections_by_practice(codes, expanded_orgs, spending_type, date) org_for_param = expanded_orgs else: query = _get_total_spending_by_practice(expanded_orgs, date) org_for_param = expanded_orgs else: query = _get_presentations_by_practice(codes, orgs, date) org_for_param = orgs data = utils.execute_query( query, [codes, org_for_param, [date] if date else []]) return Response(data)
def spending_by_ccg(request, format=None): codes = utils.param_to_list(request.query_params.get('code', [])) codes = utils.get_bnf_codes_from_number_str(codes) orgs = utils.param_to_list(request.query_params.get('org', [])) spending_type = utils.get_spending_type(codes) if spending_type is False: err = 'Error: Codes must all be the same length' return Response(err, status=400) if not spending_type or spending_type == 'bnf-section' \ or spending_type == 'chemical': query = _get_query_for_chemicals_or_sections_by_ccg(codes, orgs, spending_type) else: query = _get_query_for_presentations_by_ccg(codes, orgs) if spending_type == 'bnf-section' or spending_type == 'product': codes = [c + '%' for c in codes] data = utils.execute_query(query, [codes, orgs]) return Response(data)
def spending_by_ccg(request, format=None): codes = utils.param_to_list(request.query_params.get('code', [])) codes = utils.get_bnf_codes_from_number_str(codes) orgs = utils.param_to_list(request.query_params.get('org', [])) spending_type = utils.get_spending_type(codes) if spending_type is False: err = CODE_LENGTH_ERROR return Response(err, status=400) if not spending_type or spending_type == 'bnf-section' \ or spending_type == 'chemical': query = _get_query_for_chemicals_or_sections_by_ccg(codes, orgs, spending_type) else: query = _get_query_for_presentations_by_ccg(codes, orgs) if spending_type == 'bnf-section' or spending_type == 'product': codes = [c + '%' for c in codes] data = utils.execute_query(query, [codes, orgs]) return Response(data)
def tariff(request, format=None): # This view uses raw SQL as we cannot produce the LEFT OUTER JOIN using the # ORM. codes = utils.param_to_list(request.query_params.get('codes', [])) # On 2019-05-14 someone set up a job on Zapier which requests the entire # (35MB) drug tariff every 10 minutes. We'd like Cloudflare to cache this # for us but we don't want to cache every reponse from this endpoint as it # contains NCSO concession data which gets updated regularly. As our # internal uses of this endpoint never involve requesting the entire # tariff, a pragmatic -- if hacky -- compromise is to just cache in the # case that the request doesn't specify any BNF codes. response_should_be_cached = not codes query = ''' SELECT tariffprice.date AS date, tariffprice.price_pence AS price_pence, vmpp.nm AS vmpp, vmpp.vppid AS vmpp_id, vmpp.bnf_code AS product, ncso_concession.price_pence AS concession, dtpaymentcategory.descr AS tariff_category, vmpp.qtyval AS pack_size FROM frontend_tariffprice tariffprice INNER JOIN dmd2_dtpaymentcategory dtpaymentcategory ON tariffprice.tariff_category_id = dtpaymentcategory.cd INNER JOIN dmd2_vmpp vmpp ON tariffprice.vmpp_id = vmpp.vppid LEFT OUTER JOIN frontend_ncsoconcession ncso_concession ON (tariffprice.date = ncso_concession.date AND tariffprice.vmpp_id = ncso_concession.vmpp_id) ''' if codes: query += ' WHERE vmpp.bnf_code IN (' query += ','.join('%s' for _ in range(len(codes))) query += ')' params = [codes] else: params = None query += ' ORDER BY date' data = utils.execute_query(query, params) response = Response(data) if request.accepted_renderer.format == 'csv': filename = "tariff.csv" response['content-disposition'] = "attachment; filename=%s" % filename if response_should_be_cached: response['cache-control'] = 'max-age={}, public'.format(60 * 60 * 8) return response
def measure_by_practice(request, format=None): measure_id = request.query_params.get('measure', None) org_ids = utils.param_to_list(request.query_params.get('org', [])) if not org_ids: raise MissingParameter tags = [x for x in request.query_params.get('tags', '').split(',') if x] measure_values = MeasureValue.objects.by_practice(org_ids, measure_id, tags) rsp_data = { 'measures': _roll_up_measure_values(measure_values, 'practice') } return Response(rsp_data)
def total_spending(request, format=None): codes = utils.param_to_list(request.query_params.get('code', [])) codes = utils.get_bnf_codes_from_number_str(codes) spending_type = utils.get_spending_type(codes) if spending_type is False: err = CODE_LENGTH_ERROR return Response(err, status=400) query = _get_query_for_total_spending(codes) if spending_type != 'presentation': codes = [c + '%' for c in codes] data = utils.execute_query(query, [codes]) return Response(data)
def org_location(request, format=None): org_type = request.GET.get('org_type', '') org_codes = utils.param_to_list(request.GET.get('q', '')) if org_type == 'practice': org_codes = utils.get_practice_ids_from_org(org_codes) if org_type == 'ccg': results = PCT.objects.filter(org_type='CCG') if org_codes: results = results.filter(code__in=org_codes) geo_field = 'boundary' fields = ('name', 'code', 'ons_code', 'org_type', 'boundary', ) else: results = Practice.objects.filter(code__in=org_codes) geo_field = 'location' fields = ('name', 'code', 'setting', 'is_dispensing', 'location', ) return HttpResponse(serialize('geojson', results, geometry_field=geo_field, fields=fields), content_type='application/json')
def tariff(request, format=None): # This view uses raw SQL as we cannot produce the LEFT OUTER JOIN using the # ORM. codes = utils.param_to_list(request.query_params.get('codes', [])) query = ''' SELECT dmd_tariffprice.date AS date, dmd_tariffprice.price_pence AS price_pence, dmd_vmpp.nm AS vmpp, dmd_product.bnf_code AS product, dmd_ncsoconcession.price_concession_pence AS concession, dmd_lookup_dt_payment_category.desc AS tariff_category FROM dmd_tariffprice INNER JOIN dmd_lookup_dt_payment_category ON dmd_tariffprice.tariff_category_id = dmd_lookup_dt_payment_category.cd INNER JOIN dmd_product ON dmd_tariffprice.product_id = dmd_product.dmdid INNER JOIN dmd_vmpp ON dmd_tariffprice.vmpp_id = dmd_vmpp.vppid LEFT OUTER JOIN dmd_ncsoconcession ON (dmd_tariffprice.date = dmd_ncsoconcession.date AND dmd_tariffprice.vmpp_id = dmd_ncsoconcession.vmpp_id) ''' if codes: query += ' WHERE dmd_product.bnf_code IN (' query += ','.join('%s' for _ in range(len(codes))) query += ')' params = [codes] else: params = None query += ' ORDER BY date' data = utils.execute_query(query, params) response = Response(data) if request.accepted_renderer.format == 'csv': filename = "tariff.csv" response['content-disposition'] = "attachment; filename=%s" % filename return response
def measure_numerators_by_org(request, format=None): measure = request.query_params.get('measure', None) org = utils.param_to_list(request.query_params.get('org', []))[0] if len(org) == 3: org_selector = 'pct_id' else: org_selector = 'practice_id' this_month = ImportLog.objects.latest_in_category('prescribing').current_at three_months_ago = (this_month - relativedelta(months=2)).strftime('%Y-%m-01') m = Measure.objects.get(pk=measure) if m.numerator_can_be_queried(): # Awkwardly, because the column names in the prescriptions table # are different from those in bigquery (for which the measure # definitions are defined), we have to rename them (e.g. `items` -> # `total_items`) numerator_selector = m.columns_for_select('numerator').replace( 'items', 'total_items') numerator_where = m.numerator_where.replace( 'bnf_code', 'presentation_code' ).replace('bnf_name', 'pn.name').replace( # This is required because the SQL contains %(var)s, which is used # for parameter interpolation '%', '%%') # The redundancy in the following column names is so we can # support various flavours of `WHERE` clause from the measure # definitions that may use a subset of any of these column # names query = ''' WITH nice_names AS ( SELECT bnf_code, MAX(name) AS name FROM dmd_product GROUP BY bnf_code HAVING COUNT(*) = 1) SELECT {org_selector} AS entity, presentation_code AS bnf_code, COALESCE(nice_names.name, pn.name) AS presentation_name, SUM(total_items) AS total_items, SUM(actual_cost) AS cost, SUM(quantity) AS quantity, {numerator_selector} FROM frontend_prescription p LEFT JOIN nice_names ON p.presentation_code = nice_names.bnf_code INNER JOIN frontend_presentation pn ON p.presentation_code = pn.bnf_code WHERE {org_selector} = %(org)s AND processing_date >= %(three_months_ago)s AND ({numerator_where}) GROUP BY {org_selector}, presentation_code, nice_names.name, pn.name ORDER BY numerator DESC LIMIT 50 '''.format( org_selector=org_selector, numerator_selector=numerator_selector, three_months_ago=three_months_ago, numerator_where=numerator_where, ) params = { 'org': org, 'three_months_ago': three_months_ago, } data = utils.execute_query(query, params) else: data = [] response = Response(data) filename = "%s-%s-breakdown.csv" % (measure, org) if request.accepted_renderer.format == 'csv': response['content-disposition'] = "attachment; filename=%s" % filename return response
def measure_by_ccg(request, format=None): measure = request.query_params.get('measure', None) orgs = utils.param_to_list(request.query_params.get('org', [])) query = 'SELECT mv.month AS date, mv.numerator, mv.denominator, ' query += 'mv.calc_value, mv.percentile, mv.cost_savings, ' query += 'mv.pct_id, pc.name as pct_name, measure_id, ' query += 'ms.name, ms.title, ms.description, ms.why_it_matters, ms.numerator_description, ' query += 'ms.denominator_description, ms.denominator_short, ms.numerator_short, ' query += 'ms.url, ms.is_cost_based ' query += "FROM frontend_measurevalue mv " query += "JOIN frontend_pct pc ON mv.pct_id=pc.code " query += "JOIN frontend_measure ms ON mv.measure_id=ms.id " query += "WHERE " if orgs: query += "(" for i, org in enumerate(orgs): query += "mv.pct_id=%s " if (i != len(orgs)-1): query += ' OR ' if orgs: query += ") AND " query += 'mv.practice_id IS NULL ' if measure: query += "AND mv.measure_id=%s " query += "ORDER BY mv.practice_id, measure_id, date" if measure: data = utils.execute_query(query, [orgs, [measure]]) else: data = utils.execute_query(query, [orgs]) rolled = {} for d in data: id = d['measure_id'] d_copy = { 'date': d['date'], 'numerator': d['numerator'], 'denominator': d['denominator'], 'calc_value': d['calc_value'], 'percentile': d['percentile'], 'cost_savings': d['cost_savings'], 'pct_id': d['pct_id'], 'pct_name': d['pct_name'] } if id in rolled: rolled[id]['data'].append(d_copy) else: rolled[id] = { 'id': id, 'name': d['name'], 'title': d['title'], 'description': d['description'], 'why_it_matters': d['why_it_matters'], 'numerator_description': d['numerator_description'], 'denominator_description': d['denominator_description'], 'numerator_short': d['numerator_short'], 'denominator_short': d['denominator_short'], 'url': d['url'], 'is_cost_based': d['is_cost_based'], 'data': [d_copy] } d = { 'measures': [rolled[k] for k in rolled] } return Response(d)
def org_details(request, format=None): ''' Get list size and ASTRO-PU by month, for CCGs or practices. ''' org_type = request.GET.get('org_type', None) keys = utils.param_to_list(request.query_params.get('keys', [])) orgs = utils.param_to_list(request.query_params.get('org', [])) cols = [] if org_type == 'practice': cols, query = _construct_cols(keys, True) query += " FROM frontend_practicestatistics pr " query += "JOIN frontend_practice pc ON pr.practice_id=pc.code " if orgs: query += "WHERE " for i, c in enumerate(orgs): if len(c) == 3: query += 'pr.pct_id=%s ' else: query += "pr.practice_id=%s " if (i != len(orgs) - 1): query += ' OR ' query += "ORDER BY date, row_id" elif org_type == 'ccg': cols, query = _construct_cols(keys, False) query += ' FROM vw__ccgstatistics ' if orgs: query += "WHERE (" for i, c in enumerate(orgs): query += "pct_id=%s " if (i != len(orgs) - 1): query += ' OR ' query += ') ' query += 'ORDER BY date' else: # Total across NHS England. json_query, cols = _query_and_cols_for(keys, json_builder_only=True) query = 'SELECT date, ' query += 'AVG(total_list_size) AS total_list_size, ' query += 'AVG(astro_pu_items) AS astro_pu_items, ' query += 'AVG(astro_pu_cost) AS astro_pu_cost, ' query += 'json_object_agg(key, val) AS star_pu ' query += 'FROM (' query += 'SELECT date, ' query += 'SUM(total_list_size) AS total_list_size, ' query += 'SUM(astro_pu_items) AS astro_pu_items, ' query += 'SUM(astro_pu_cost) AS astro_pu_cost, ' query += 'key, SUM(value::numeric) val ' query += "FROM vw__ccgstatistics p, json_each_text(" if json_query: query += json_query else: query += 'star_pu' query += ") " query += 'GROUP BY date, key ' query += ') p ' query += 'GROUP BY date ORDER BY date' try: if cols: data = utils.execute_query(query, [cols, orgs]) else: data = utils.execute_query(query, [orgs]) except ProgrammingError as e: error = str(e) if keys and 'does not exist' in error: error = error.split('\n')[0].replace('column', 'key') raise KeysNotValid(error) else: raise return Response(data)
def total_spending(request, format=None): codes = utils.param_to_list(request.query_params.get('code', [])) codes = utils.get_bnf_codes_from_number_str(codes) data = _get_total_prescribing_entries(codes) return Response(list(data))