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)