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 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 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_global(request, format=None): measure = request.query_params.get('measure', None) query = 'SELECT mg.month AS date, mg.numerator, ' query += 'mg.denominator, mg.measure_id, ' query += 'mg.calc_value, mg.percentiles, mg.cost_savings, ' query += 'ms.name, ms.title, ms.description, ' query += 'ms.why_it_matters, ' query += ' ms.denominator_short, ms.numerator_short, ' query += 'ms.url, ms.is_cost_based, ms.is_percentage, ' query += 'ms.low_is_good ' query += "FROM frontend_measureglobal mg " query += "JOIN frontend_measure ms ON mg.measure_id=ms.id " if measure: query += "WHERE mg.measure_id=%s " query += "ORDER BY mg.measure_id, mg.month" data = utils.execute_query(query, [[measure]]) 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'], 'percentiles': d['percentiles'], 'cost_savings': d['cost_savings'] } 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_short': d['numerator_short'], 'denominator_short': d['denominator_short'], 'url': d['url'], 'is_cost_based': d['is_cost_based'], 'is_percentage': d['is_percentage'], 'low_is_good': d['low_is_good'], 'data': [d_copy] } d = { 'measures': [rolled[k] for k in rolled] } return Response(d)
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 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 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 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 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 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 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 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)