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