def _get_prescribed_quantity_matrix(bnf_code_offsets, date_offsets, org_type, org_id):
"""
Given a mapping of BNF codes to row offsets and dates to column offsets,
return a matrix giving the quantity of those presentations prescribed on
those dates by the specified organisation (given by its type and ID).
If the dates extend beyond the latest date for which we have prescribing
data then we just project the last month forwards (e.g. if we only have
prescriptions up to March but have price concessions up to May then
we just assume the same quantities as for March were prescribed in April
and May).
"""
db = get_db()
group_by_org = get_row_grouper(org_type)
shape = (len(bnf_code_offsets), len(date_offsets))
quantities = numpy.zeros(shape, dtype=numpy.int_)
# If this organisation is not in the set of available groups (because it
# has no prescribing data) then return the zero-valued quantity matrix
if org_id not in group_by_org.offsets:
return quantities
# Find the columns corresponding to the dates we're interested in
columns_selector = _get_date_columns_selector(db.date_offsets, date_offsets)
prescribing = _get_quantities_for_bnf_codes(db, list(bnf_code_offsets.keys()))
for bnf_code, quantity in prescribing:
# Remap the date columns to just the dates we want
quantity = quantity[columns_selector]
# Sum the prescribing for the given organisation
quantity = group_by_org.sum_one_group(quantity, org_id)
# Write that sum into the quantities matrix at the correct offset for
# the current BNF code
row_offset = bnf_code_offsets[bnf_code]
quantities[row_offset] = quantity
return quantities
def _get_total_prescribing_entries(bnf_code_prefixes):
"""
Yields a dict for each date in our data giving the total prescribing values
across all practices for all presentations matching the supplied BNF code
prefixes
"""
db = get_db()
items_matrix, quantity_matrix, actual_cost_matrix = _get_prescribing_for_codes(
db, bnf_code_prefixes)
# If no data at all was found, return early which results in an empty
# iterator
if items_matrix is None:
return
# This will sum over every practice (whether setting 4 or not) which might
# not seem like what we want but is what the original API did (it was
# powered by the `vw__presentation_summary` table which summed over all
# practice types)
group_all = get_row_grouper("all_practices")
items_matrix = group_all.sum(items_matrix)
quantity_matrix = group_all.sum(quantity_matrix)
actual_cost_matrix = group_all.sum(actual_cost_matrix)
# Yield entries for each date (unlike _get_prescribing_entries below we
# return a value for each date even if it's zero as this is what the
# original API did)
for date, col_offset in sorted(db.date_offsets.items()):
# The grouped matrices only ever have one row (which represents the
# total over all practices) so we always want row 0 in our index
index = (0, col_offset)
yield {
"items": items_matrix[index],
"quantity": quantity_matrix[index],
"actual_cost": round(actual_cost_matrix[index], 2),
"date": date,
}
def ncso_spending_for_entity(entity,
entity_type,
num_months,
current_month=None):
org_type, org_id = _get_org_type_and_id(entity, entity_type)
end_date = NCSOConcession.objects.aggregate(Max("date"))["date__max"]
# In practice, we always have at least one NCSOConcession object but we
# need to handle the empty case in testing
if not end_date:
return []
start_date = end_date - relativedelta(months=num_months - 1)
last_prescribing_date = parse_date(get_db().dates[-1]).date()
costs = _get_concession_cost_matrices(start_date, end_date, org_type,
org_id)
# Sum together costs over all presentations (i.e. all rows)
tariff_costs = numpy.sum(costs.tariff_costs, axis=0)
extra_costs = numpy.sum(costs.extra_costs, axis=0)
results = []
for date_str, offset in sorted(costs.date_offsets.items()):
date = parse_date(date_str).date()
if extra_costs[offset] == 0:
continue
entry = {
"month": date,
"tariff_cost": float(tariff_costs[offset]),
"additional_cost": float(extra_costs[offset]),
"is_estimate": date > last_prescribing_date,
"last_prescribing_date": last_prescribing_date,
}
if current_month is not None:
entry["is_incomplete_month"] = date >= current_month
results.append(entry)
return results
def _get_prescribing_entries(bnf_code_prefixes, orgs, org_type, date=None):
"""
For each date and organisation, yield a dict giving totals for all
prescribing matching the supplied BNF code prefixes.
If a date is supplied then data for just that date is returned, otherwise
all available dates are returned.
"""
db = get_db()
items_matrix, quantity_matrix, actual_cost_matrix = _get_prescribing_for_codes(
db, bnf_code_prefixes)
# If no data at all was found, return early which results in an empty
# iterator
if items_matrix is None:
return
# Group together practice level data to the appropriate organisation level
group_by_org = get_row_grouper(org_type)
items_matrix = group_by_org.sum(items_matrix)
quantity_matrix = group_by_org.sum(quantity_matrix)
actual_cost_matrix = group_by_org.sum(actual_cost_matrix)
# `group_by_org.offsets` maps each organisation's primary key to its row
# offset within the matrices. We pair each organisation with its row
# offset, ignoring those organisations which aren't in the mapping (which
# implies that they did not prescribe in this period)
org_offsets = [(org, group_by_org.offsets[org.pk]) for org in orgs
if org.pk in group_by_org.offsets]
# Pair each date with its column offset (either all available dates or just
# the specified one)
if date:
try:
date_offsets = [(date, db.date_offsets[date])]
except KeyError:
raise BadDate(date)
else:
date_offsets = sorted(db.date_offsets.items())
# Yield entries for each organisation on each date
for date, col_offset in date_offsets:
for org, row_offset in org_offsets:
index = (row_offset, col_offset)
items = items_matrix[index]
# Mimicking the behaviour of the existing API, we don't return
# entries where there was no prescribing
if items == 0:
continue
entry = {
"items": items,
"quantity": quantity_matrix[index],
"actual_cost": round(actual_cost_matrix[index], 2),
"date": date,
"row_id": org.pk,
"row_name": org.name,
}
# Practices get some extra attributes in the existing API
if org_type == "practice":
entry["ccg"] = org.ccg_id
entry["setting"] = org.setting
yield entry
def get_total_ghost_branded_generic_spending(date, org_type, org_id):
"""
Get the total spend on generics (by this org and in this month) over and
above the price set by the Drug Tariff
"""
db = get_db()
practice_spending = get_total_ghost_branded_generic_spending_per_practice(
db, date, PRESENTATIONS_TO_IGNORE, MIN_GHOST_GENERIC_DELTA
)
group_by_org = get_row_grouper(org_type)
return group_by_org.sum_one_group(practice_spending, org_id)[0] / 100
def get_savings_for_orgs(generic_code, date, org_type, org_ids, min_saving=1):
"""
Get available savings for the given orgs within a particular class of
substitutable presentations
"""
try:
substitution_set = get_substitution_sets()[generic_code]
# Gracefully handle being asked for the savings for a code with no
# substitutions (to which the answer is always: no savings)
except KeyError:
return []
quantities, net_costs = get_quantities_and_net_costs_at_date(
get_db(), substitution_set, date
)
group_by_org = get_row_grouper(org_type)
quantities_for_orgs = group_by_org.sum(quantities, org_ids)
# Bail early if none of the orgs have any relevant prescribing
if not numpy.any(quantities_for_orgs):
return []
net_costs_for_orgs = group_by_org.sum(net_costs, org_ids)
ppu_for_orgs = net_costs_for_orgs / quantities_for_orgs
target_ppu = get_target_ppu(
quantities,
net_costs,
group_by_org=get_row_grouper(CONFIG_TARGET_PEER_GROUP),
target_centile=CONFIG_TARGET_CENTILE,
)
practice_savings = get_savings(quantities, net_costs, target_ppu)
savings_for_orgs = group_by_org.sum(practice_savings, org_ids)
results = [
{
"date": date,
"org_id": org_id,
"price_per_unit": ppu_for_orgs[offset, 0] / 100,
"possible_savings": savings_for_orgs[offset, 0] / 100,
"quantity": quantities_for_orgs[offset, 0],
"lowest_decile": target_ppu[0] / 100,
"presentation": substitution_set.id,
"formulation_swap": substitution_set.formulation_swaps,
"name": substitution_set.name,
}
for offset, org_id in enumerate(org_ids)
if savings_for_orgs[offset, 0] >= min_saving
]
results.sort(key=lambda i: i["possible_savings"], reverse=True)
return results
def _get_prescribing_for_bnf_codes(bnf_codes):
"""
Return the items, quantity and actual cost matrices for the given list of
BNF codes
"""
return get_db().query(
"""
SELECT
bnf_code, items, quantity, actual_cost
FROM
presentation
WHERE
bnf_code IN ({})
""".format(",".join(["?"] * len(bnf_codes))),
bnf_codes,
)
def _get_practice_stats_entries(keys, org_type, orgs):
db = get_db()
practice_stats = db.query(*_get_query_and_params(keys))
group_by_org = get_row_grouper(org_type)
practice_stats = [
(name, group_by_org.sum(matrix)) for (name, matrix) in practice_stats
]
# `group_by_org.offsets` maps each organisation's primary key to its row
# offset within the matrices. We pair each organisation with its row
# offset, ignoring those organisations which aren't in the mapping (which
# implies that we have no statistics for them)
org_offsets = [
(org, group_by_org.offsets[org.pk])
for org in orgs
if org.pk in group_by_org.offsets
]
# For the "all_practices" grouping we have no orgs and just a single row
if org_type == "all_practices":
org_offsets = [(None, 0)]
date_offsets = sorted(db.date_offsets.items())
# Yield entries for each organisation on each date
for date, col_offset in date_offsets:
for org, row_offset in org_offsets:
entry = {"date": date}
if org is not None:
entry["row_id"] = org.pk
entry["row_name"] = org.name
index = (row_offset, col_offset)
star_pu = {}
has_value = False
for name, matrix in practice_stats:
value = matrix[index]
if value != 0:
has_value = True
if name == "nothing":
value = 1
if isinstance(value, float):
value = round(value, 2)
if name.startswith("star_pu."):
star_pu[name[8:]] = value
else:
entry[name] = value
if star_pu:
entry["star_pu"] = star_pu
if has_value:
yield entry
def get_prescribing(generic_code, date):
"""
For a given set of substitutable presentations (identified by
`generic_code`) get all prescribing of those presentations on the given
date
Return value is a dict of the form:
{
...
bnf_code: (quantity_matrix, net_cost_matrix)
...
}
"""
# If the supplied code doesn't represent a substitution set just show
# prescribing for that single BNF code
try:
bnf_codes = get_substitution_sets()[generic_code].presentations
except KeyError:
bnf_codes = [generic_code]
db = get_db()
try:
date_column = db.date_offsets[date]
except KeyError:
return {}
date_slice = slice(date_column, date_column + 1)
results = db.query(
"""
SELECT
bnf_code, quantity, net_cost
FROM
presentation
WHERE
bnf_code IN ({})
""".format(",".join("?" * len(bnf_codes))),
bnf_codes,
)
return {
bnf_code: (
get_submatrix(quantity, cols=date_slice),
get_submatrix(net_cost, cols=date_slice),
)
for bnf_code, quantity, net_cost in results
}
def get_ghost_branded_generic_spending(date, org_type, org_ids):
"""
Return all spending on generics (by these orgs and in this month) which
differs significantly from the tariff price
"""
db = get_db()
prices = get_inferred_tariff_prices(db, date, PRESENTATIONS_TO_IGNORE)
bnf_codes = list(prices.keys())
prescribing = get_prescribing_for_orgs(db, bnf_codes, date, org_type, org_ids)
results = []
bnf_codes_used = set()
for org_id, bnf_code, quantities, net_costs in prescribing:
tariff_price = prices[bnf_code]
tariff_costs = quantities * tariff_price
possible_savings = net_costs - tariff_costs
savings_above_threshold = (
numpy.absolute(possible_savings) >= MIN_GHOST_GENERIC_DELTA
)
total_savings = possible_savings.sum(where=savings_above_threshold)
if total_savings != 0:
bnf_codes_used.add(bnf_code)
total_net_cost = net_costs.sum()
total_quantity = quantities.sum()
results.append(
{
"date": date,
"org_type": org_type,
"org_id": org_id,
"bnf_code": bnf_code,
"median_ppu": tariff_price / 100,
"price_per_unit": total_net_cost / total_quantity / 100,
"quantity": total_quantity,
"possible_savings": total_savings / 100,
}
)
names = Presentation.names_for_bnf_codes(bnf_codes_used)
for result in results:
result["product_name"] = names.get(result["bnf_code"], "unknown")
results.sort(key=lambda i: i["possible_savings"], reverse=True)
return results
def get_total_savings_for_org(date, org_type, org_id):
"""
Get total available savings through presentation switches for the given org
"""
group_by_org = get_row_grouper(org_type)
substitution_sets = get_substitution_sets()
# This only happens during testing where a test case might not have enough
# different presentations to generate any substitutions. If this is the
# case then their are, obviously, zero savings available.
if not substitution_sets:
return 0.0
totals = get_total_savings_for_org_type(
db=get_db(),
substitution_sets=substitution_sets,
date=date,
group_by_org=group_by_org,
min_saving=CONFIG_MIN_SAVINGS_FOR_ORG_TYPE[org_type],
practice_group_by_org=get_row_grouper(CONFIG_TARGET_PEER_GROUP),
target_centile=CONFIG_TARGET_CENTILE,
)
offset = group_by_org.offsets[org_id]
return totals[offset, 0] / 100
def get_subsection_prefixes(prefix):
"""Return BNF codes/prefixes of BNF subsections that begin with `prefix`.
For instance, if `prefix` is "0703021", we find all prefixes corresponding to
chemicals beginning 0703021.
"""
for length in [
2, # Chapter
4, # Section
6, # Paragraph
9, # Chemical
11, # Product
15, # Presentation
]:
if len(prefix) <= length:
break
db = get_db()
sql = (
"SELECT DISTINCT substr(bnf_code, 1, ?) FROM presentation WHERE bnf_code LIKE ?"
)
return {r[0] for r in db.query(sql, [length, prefix + "%"])}
def get_all_bnf_codes():
"""Return list of all BNF codes for which we have prescribing."""
db = get_db()
return {r[0] for r in db.query("SELECT bnf_code FROM presentation")}
def dmd_obj_view(request, obj_type, id):
try:
cls = obj_type_to_cls[obj_type]
except KeyError:
raise Http404
obj = get_object_or_404(cls, id=id)
obj_type_human = obj_type.upper()
fields_by_name = {field.name: field for field in cls._meta.fields}
rels_by_name = {rel.name: rel for rel in cls._meta.related_objects}
rows = []
# Fields for the object
for field_name in view_schema[obj_type]["fields"]:
field = fields_by_name[field_name]
row = _build_row(obj, field)
if row is not None:
rows.append(row)
# Related objects (eg VPIs for a VMP)
for rel_name in view_schema[obj_type]["other_relations"]:
relname = rel_name.replace("_", "")
rel = rels_by_name[relname]
model = rel.related_model
rel_fields_by_name = {
field.name: field
for field in model._meta.fields
}
if rel.multiple:
related_instances = getattr(obj, rel.get_accessor_name()).all()
if not related_instances.exists():
continue
else:
try:
related_instances = [getattr(obj, rel.name)]
except ObjectDoesNotExist:
continue
for related_instance in related_instances:
rows.append({"title": model._meta.verbose_name})
for field_name in view_schema[rel_name]["fields"]:
if field_name == obj_type:
continue
field = rel_fields_by_name[field_name]
row = _build_row(related_instance, field)
if row is not None:
rows.append(row)
# Related child dm+d objects (for a VMP, these will be VMPPs and AMPs)
for rel_name in view_schema[obj_type]["dmd_obj_relations"]:
relname = rel_name.replace("_", "")
rel = rels_by_name[relname]
assert rel.multiple
model = rel.related_model
related_instances = getattr(
obj, rel.get_accessor_name()).valid_and_available()
if not related_instances.exists():
continue
rows.append({"title": model._meta.verbose_name_plural})
for related_instance in related_instances:
link = reverse("dmd_obj", args=[rel_name, related_instance.id])
rows.append({"value": related_instance.title(), "link": link})
if isinstance(obj, (VMP, AMP, VMPP, AMPP)) and obj.bnf_code is not None:
has_prescribing = get_db().query_one(
"""
SELECT EXISTS(
SELECT 1 FROM presentation WHERE bnf_code=?
)
""",
[obj.bnf_code],
)[0]
else:
has_prescribing = False
if isinstance(obj, VMPP):
has_dt = TariffPrice.objects.filter(vmpp_id=id).exists()
else:
has_dt = False
ctx = {
"title": "{} {}".format(obj_type_human, id),
"obj": obj,
"obj_type": obj_type_human,
"rows": rows,
"has_prescribing": has_prescribing,
"has_dt": has_dt,
}
ctx.update(_release_metadata())
return render(request, "dmd/dmd_obj.html", ctx)
def get_substitution_sets():
bnf_codes = [row[0] for row in get_db().query("SELECT bnf_code FROM presentation")]
return get_substitution_sets_from_bnf_codes(bnf_codes, FORMULATION_SWAPS_FILE)
