<commit_before>import datetime
import re

from django.db import connection
from django.db.models import Q
from django.shortcuts import get_object_or_404

from rest_framework.decorators import api_view
from rest_framework.response import Response
from rest_framework.exceptions import APIException

from common.utils import namedtuplefetchall
from common.utils import nhs_titlecase
from common.utils import ppu_sql
from frontend.models import GenericCodeMapping
from frontend.models import ImportLog
from frontend.models import Presentation
from frontend.models import Practice, PCT, STP, RegionalTeam, PCN
from matrixstore.db import get_db, get_row_grouper

from . import view_utils as utils


MIN_GHOST_GENERIC_DELTA = 2


class NotValid(APIException):
    status_code = 400
    default_detail = "The code you provided is not valid"


def _valid_or_latest_date(date):
    if date:
        try:
            date = datetime.datetime.strptime(date, "%Y-%m-%d")
        except ValueError:
            raise NotValid("%s is not a valid date" % date)
    else:
        date = ImportLog.objects.latest_in_category("prescribing").current_at
    return date


def _build_conditions_and_patterns(code, focus):
    if not re.match(r"[A-Z0-9]{15}", code):
        raise NotValid("%s is not a valid code" % code)

    # flatten and uniquify the list of codes
    extra_codes = set()
    for mapping in GenericCodeMapping.objects.filter(
        Q(from_code=code) | Q(to_code=code)
    ):
        extra_codes.add(mapping.from_code)
        extra_codes.add(mapping.to_code)

    patterns = ["%s____%s" % (code[:9], code[13:15])]
    for extra_code in extra_codes:
        if extra_code.endswith("%"):
            pattern = extra_code
        else:
            pattern = "%s____%s" % (extra_code[:9], extra_code[13:15])
        patterns.append(pattern)
    conditions = " OR ".join(["presentation_code LIKE %s "] * len(patterns))
    conditions = "AND (%s) " % conditions
    if focus:
        if len(focus) == 3:
            conditions += "AND (pct_id = %s)"
        else:
            conditions += "AND (practice_id = %s)"
        patterns.append(focus)
    return conditions, patterns


def _get_org_or_404(org_code, org_type=None):
    if not org_type and org_code:
        org_type = "ccg" if len(org_code) == 3 else "practice"
    if org_type.lower() == "ccg":
        org = get_object_or_404(PCT, pk=org_code)
    elif org_type == "practice":
        org = get_object_or_404(Practice, pk=org_code)
    else:
        raise ValueError(org_type)
    return org


@api_view(["GET"])
def bubble(request, format=None):
    """Returns data relating to price-per-unit, in a format suitable for
    use in Highcharts bubble chart.

    """
    code = request.query_params.get("bnf_code", "")
    date = _valid_or_latest_date(request.query_params.get("date", None))
    highlight = request.query_params.get("highlight", None)
    focus = request.query_params.get("focus", None) and highlight
    conditions, patterns = _build_conditions_and_patterns(code, focus)
    rounded_ppus_cte_sql = (
        "WITH rounded_ppus AS (SELECT presentation_code, "
        "COALESCE(frontend_presentation.name, 'unknown') "
        "AS presentation_name, "
        "quantity, net_cost, practice_id, pct_id, "
        "ROUND(CAST(net_cost/NULLIF(quantity, 0) AS numeric), 2) AS ppu "
        "FROM frontend_prescription "
        "LEFT JOIN frontend_presentation "
        "ON frontend_prescription.presentation_code = "
        "frontend_presentation.bnf_code "
        "LEFT JOIN frontend_practice ON frontend_practice.code = practice_id "
        "WHERE processing_date = %s "
        "AND setting = 4 " + conditions + ") "
    )
    binned_ppus_sql = rounded_ppus_cte_sql + (
        ", binned_ppus AS (SELECT presentation_code, presentation_name, ppu, "
        "SUM(quantity) AS quantity "
        "FROM rounded_ppus "
        "GROUP BY presentation_code, presentation_name, ppu) "
    )
    ordered_ppus_sql = binned_ppus_sql + (
        "SELECT *, "
        "SUM(ppu * quantity) OVER (PARTITION BY presentation_code) "
        " / SUM(quantity) OVER (PARTITION BY presentation_code)"
        "     AS mean_ppu "
        "FROM binned_ppus "
        "ORDER BY mean_ppu, presentation_name, ppu"
    )
    mean_ppu_for_entity_sql = rounded_ppus_cte_sql + (
        "SELECT SUM(net_cost)/SUM(quantity) FROM rounded_ppus "
    )
    params = [date] + patterns
    with connection.cursor() as cursor:
        cursor.execute(ordered_ppus_sql, params)
        series = []
        categories = []
        pos = 0
        for result in namedtuplefetchall(cursor):
            if result.presentation_name not in [x["name"] for x in categories]:
                pos += 1
                is_generic = False
                if result.presentation_code[9:11] == "AA":
                    is_generic = True
                categories.append(
                    {"name": result.presentation_name, "is_generic": is_generic}
                )

            series.append(
                {
                    "x": pos,
                    "y": result.ppu,
                    "z": result.quantity,
                    "mean_ppu": result.mean_ppu,
                    "name": result.presentation_name,
                }
            )
        if highlight:
            params.append(highlight)
            if len(highlight) == 3:
                mean_ppu_for_entity_sql += "WHERE pct_id = %s "
            else:
                mean_ppu_for_entity_sql += "WHERE practice_id = %s "
        cursor.execute(mean_ppu_for_entity_sql, params)
        plotline = cursor.fetchone()[0]
        return Response(
            {"plotline": plotline, "series": series, "categories": categories}
        )


@api_view(["GET"])
def price_per_unit(request, format=None):
    """Returns price per unit data for presentations and practices or
    CCGs

    """
    entity_code = request.query_params.get("entity_code")
    entity_type = request.query_params.get("entity_type")
    date = request.query_params.get("date")
    bnf_code = request.query_params.get("bnf_code")<commit_msg>Switch API bubble view to use new backend<commit_after>import datetime

from django.db import connection
from django.shortcuts import get_object_or_404

from rest_framework.decorators import api_view
from rest_framework.response import Response
from rest_framework.exceptions import APIException

from common.utils import nhs_titlecase
from common.utils import ppu_sql
from frontend.models import ImportLog
from frontend.models import Presentation
from frontend.models import Practice, PCT, STP, RegionalTeam, PCN
from frontend.price_per_unit.prescribing_breakdown import (
    get_prescribing,
    get_ppu_breakdown,
    get_mean_ppu,
)
from matrixstore.db import get_db, get_row_grouper

from . import view_utils as utils


MIN_GHOST_GENERIC_DELTA = 2


class NotValid(APIException):
    status_code = 400
    default_detail = "The code you provided is not valid"


def _valid_or_latest_date(date):
    if date:
        try:
            date = datetime.datetime.strptime(date, "%Y-%m-%d").date()
        except ValueError:
            raise NotValid("%s is not a valid date" % date)
    else:
        date = ImportLog.objects.latest_in_category("prescribing").current_at
    return date


def _get_org_or_404(org_code, org_type=None):
    if not org_type and org_code:
        org_type = "ccg" if len(org_code) == 3 else "practice"
    if org_type.lower() == "ccg":
        org = get_object_or_404(PCT, pk=org_code)
    elif org_type == "practice":
        org = get_object_or_404(Practice, pk=org_code)
    else:
        raise ValueError(org_type)
    return org


@api_view(["GET"])
def bubble(request, format=None):
    """
    Returns data relating to price-per-unit, in a format suitable for
    use in Highcharts bubble chart.
    """
    code = request.query_params.get("bnf_code", "")
    date = _valid_or_latest_date(request.query_params.get("date", None))
    highlight = request.query_params.get("highlight", None)
    focus = request.query_params.get("focus", None) and highlight

    if highlight:
        highlight_org_id = highlight
        highlight_org_type = (
            "standard_ccg" if len(highlight_org_id) == 3 else "practice"
        )
    else:
        highlight_org_type = "all_standard_practices"
        highlight_org_id = None

    if focus:
        org_type = highlight_org_type
        org_id = highlight_org_id
    else:
        org_type = "all_standard_practices"
        org_id = None

    prescribing = get_prescribing(code, str(date))
    ppu_breakdown = get_ppu_breakdown(prescribing, org_type, org_id)
    mean_ppu = get_mean_ppu(prescribing, highlight_org_type, highlight_org_id)

    categories = []
    series = []

    for n, presentation in enumerate(ppu_breakdown):
        categories.append(
            {"name": presentation["name"], "is_generic": presentation["is_generic"]}
        )
        for ppu_value, quantity_at_ppu in presentation["quantity_at_each_ppu"]:
            series.append(
                {
                    "x": n + 1,
                    "y": ppu_value / 100,
                    "z": quantity_at_ppu,
                    "name": presentation["name"],
                    "mean_ppu": presentation["mean_ppu"] / 100,
                }
            )

    return Response(
        {"plotline": mean_ppu / 100, "series": series, "categories": categories}
    )


@api_view(["GET"])
def price_per_unit(request, format=None):
    """Returns price per unit data for presentations and practices or
    CCGs

    """
    entity_code = request.query_params.get("entity_code")
    entity_type = request.query_params.get("entity_type")
    date = request.query_params.get("date")
    bnf_code = request.query_params.get("bnf_code")
{'fraction_non_alphanumeric': 0.09782940996637114, 'fraction_numerical': 0.007133394476714562, 'mean_word_length': 2.7601532567049807, 'pattern_counts': {'":': 25, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1066109', 'n_tokens_mistral': 3131, 'n_tokens_neox': 2959, 'n_words': 713}