kms-backend/endpoints/web/home.py

# -*- coding:utf-8 -*-
"""
@Created on : 2023/7/5 16:04
@Author: hxl
@Des:
"""
from datetime import datetime

from fastapi import APIRouter, Request, Depends
from tortoise import Tortoise
from tortoise.expressions import Q
from tortoise.functions import Count
from tortoise.queryset import QuerySet

from helper import respond_to, login_required
from helper.drug import drug_near_already_expired
from helper.utils import timezone_now
from models import Drug, DrugStateEnum, DrugUseStateEnum, DrugUseLog, Dictionary, Template, Cabinet, \
    DrawerBoard, User

router = APIRouter(prefix='/home',  dependencies=[Depends(login_required)])


@router.get('/overview', summary='概览数据')
async def index(request: Request):
    """
    获取首页概览数据 \n
    return: \n
    - 'storage_count': 今日入库,
    - 'expire_count': 未归还,
    - 'receive_count': 今日领用,
    - 'return_count': 今日归还,
    - 'total_inventory': 库存总量,
    - 'inventory_warning_count': 库存预警,
    - 'near_warning_count': 试剂临期,
    - 'already_expired_count':试剂过期

    """
    stime = datetime.combine(timezone_now(), datetime.min.time())
    etime = datetime.combine(timezone_now(), datetime.max.time())

    archive_id = request.state.archive_id
    template_obj = await Template.get(archive_id=request.state.archive_id)
    if not template_obj:
        return respond_to()
    # 今日入库
    storage_count = await Drug.filter(storage_at__range=[stime, etime], template_id=template_obj.id).count()
    # 截止目前在库已过期
    expire_count = await Drug.filter(state=DrugStateEnum.IN,
                                     expired_at__lt=timezone_now().strftime('%Y-%m-%d %H:%M:%S'),
                                     template_id=template_obj.id).count()
    # 今日领用
    # receive_count_1 = await (DrugUseLog.filter(state=DrugUseStateEnum.TAKE, created_at__range=[stime, etime])
    #                        .filter(drug__template_id=template_obj.id)
    #                        .filter(Q(" and JSON_EXTRACT(json_data, '$.first_will_empty') = true ")).count())
    receive_count = await (DrugUseLog.filter(state=DrugUseStateEnum.TAKE, created_at__range=[stime, etime])
                           .filter(drug__template_id=template_obj.id).count())
    # 构建自定义SQL语句
    sql = f"""SELECT COUNT(*) count FROM drug_use_logs a 
    left join drugs b on a.drug_id=b.id
    left join templates c on c.id = b.template_id
    left join archives d on d.id = c.archive_id
    WHERE a.state = '{DrugUseStateEnum.EMPTY}'
    AND a.created_at BETWEEN '{stime}' AND '{etime}'
    AND b.template_id = '{template_obj.id}'
    AND JSON_EXTRACT(d.params, '$.first_will_empty') = true"""

    conn = Tortoise.get_connection(connection_name='default')
    receive_count_first_empty = await conn.execute_query(sql)
    receive_count = receive_count + receive_count_first_empty[1][0].get("count")
    # 今日归还
    return_count = await (DrugUseLog.filter(state=DrugUseStateEnum.PUT, created_at__range=[stime, etime])
                          .filter(drug__template_id=template_obj.id).count())
    # 试剂库存总量
    total_inventory = await Drug.filter(state__in=[1, 2], template_id=template_obj.id).count()
    # 库存预警
    inventory_warning_count, _ = await get_lack_stock(archive_id)
    # 临期试剂
    near_warning_count, _ = await get_near_expired(archive_id)
    # 试剂过期
    already_expired_count = await get_already_expired(archive_id)
    data = {
        'storage_count': storage_count,
        'expire_count': expire_count,
        'receive_count': receive_count,
        'return_count': return_count,
        'total_inventory': total_inventory,
        'inventory_warning_count': inventory_warning_count,
        'near_warning_count': near_warning_count,
        'already_expired_count': len(already_expired_count)
    }
    # await conn.close()
    return respond_to(data=data)

@router.get('/overview/item/{key}', summary='概览数据明细')
async def index(request: Request, key: str):
    """
    获取首页概览数据明细
    return:
    key: storage expire receive return
    """
    stime = datetime.combine(timezone_now(), datetime.min.time())
    etime = datetime.combine(timezone_now(), datetime.max.time())

    archive_id = request.state.archive_id
    template_obj = await Template.get(archive_id=archive_id)
    if not template_obj:
        return respond_to()

    drugs, logs = [], []
    # 今日入库
    if key == "storage":
        drugs = await Drug.filter(storage_at__range=[stime, etime], template_id=template_obj.id).all()
    # 截止目前在库已过期
    if key == "expire":
        drugs = await Drug.filter(state=DrugStateEnum.IN,
                                         expired_at__lt=timezone_now().strftime('%Y-%m-%d %H:%M:%S'),
                                         template_id=template_obj.id).all()

    # 今日领用
    if key == "receive":
        logs = await (DrugUseLog.filter(state=DrugUseStateEnum.TAKE, created_at__range=[stime, etime])
                           .filter(drug__template_id=template_obj.id).prefetch_related("drug").all())
    # 今日归还
    if key == "return":
        logs = await (DrugUseLog.filter(state=DrugUseStateEnum.PUT, created_at__range=[stime, etime])
                          .filter(drug__template_id=template_obj.id).prefetch_related("drug").all())
    result = []
    if key in ["storage", "expire"]:
        if not drugs:
            return respond_to(data=[])
        for drug_obj in drugs:
            data = {
                "drug_id": drug_obj.id,
                "rfid": drug_obj.rfid,
                "position": drug_obj.position,
                "expired_at": drug_obj.expired_at,
                "drug_info": await drug_obj.parse_fill_json_content()
            }
            result.append(data)
    else:
        if not logs:
            return respond_to(data=[])
        for log in logs:
            if not log.drug:
                continue
            result.append({
                "drug_id": log.drug.id,
                "rfid": log.drug.rfid,
                "position": log.drug.position,
                "expired_at": log.drug.expired_at,
                "drug_info": await log.drug.attribute_drug_info(),
            })
    return respond_to(data=result)


@router.post('/remaining_stock', summary='剩余库存表')
async def get_remaining_stock(request: Request, cabinets: list):
    """
    获取剩余库存表
    :return:
    """
    data = {}
    cabinets = await Cabinet.filter(id__in=cabinets).values("id", "label")
    query = QuerySet(Dictionary).filter(archive_id=request.state.archive_id)

    dictionary_ids = await query.filter().values_list("id", flat=True)
    drug_query = Drug.filter(~Q(state__in=[DrugStateEnum.EMPTY, DrugStateEnum.INITIAL]),
                dictionary_id__in=dictionary_ids, cabinet_id__in=[obj.get("id") for obj in cabinets]).prefetch_related('dictionary').annotate(
        count=Count('id')).group_by("dictionary_id").values("dictionary_id", "count", "template_id")
    drug_counts = await drug_query
    for c in cabinets:
        count = []
        drug_list = []
        for i in drug_counts:
            template_obj = await Template.get(id=i.get("template_id"))
            dictionary_obj = await Dictionary.get(id=i.get("dictionary_id"))
            drug_dict = await dictionary_obj.attribute_dictionary_info(template_obj)
            drug_list.append(",".join(map(str, drug_dict.values())))
            count.append(i.get("count"))
        data['drug_dict'] = drug_list
        data[c["label"]] = count
    return respond_to(data=data)


@router.get('/use_number', summary='试剂使用次数')
async def get_drug_count(request: Request, page_on: int = 1, page_size: int = 10):
    """
    获取试剂使用次数
    :param request: 请求
    :param page_on: 当前页码
    :param page_size: 每页显示数量
    :return:
    """
    dictionaries = await Dictionary.filter(archive_id=request.state.archive_id).all().values()
    total_count = 0
    drug_use_counts = []
    start_index = (page_on - 1) * page_size
    end_index = page_on * page_size
    conn = Tortoise.get_connection(connection_name='default')
    for dictionary in dictionaries:
        drug_use_count = await DrugUseLog.filter(drug__dictionary=dictionary['id'], state=2).count()
        sql = f"""SELECT COUNT(*) count FROM drug_use_logs a 
            left join drugs b on a.drug_id=b.id
            left join templates c on c.id = b.template_id
            left join archives d on d.id = c.archive_id
            WHERE a.state = '{DrugUseStateEnum.EMPTY}'
            AND b.dictionary_id = '{dictionary['id']}'
            AND JSON_EXTRACT(d.params, '$.first_will_empty') = true"""
        receive_count_first_empty = await conn.execute_query(sql)
        drug_use_count = drug_use_count + receive_count_first_empty[1][0].get("count")
        if(drug_use_count==0):
            continue
        drug_info = ",".join([value for key, value in dictionary.items() if key.startswith('k') and value is not None])
        drug_use_counts.append({'drug_info': drug_info, 'drug_use_count': drug_use_count})
        total_count += drug_use_count
    paginated_drug_use_counts = drug_use_counts[start_index:end_index]
    # conn.close()
    return respond_to(
        data={'drug_use_counts': paginated_drug_use_counts, 'total_count': len(paginated_drug_use_counts)})


@router.get('/near_warning', summary='近期预警列表')
async def get_warning(request: Request):
    archive_id = request.state.archive_id
    _, near_expired = await get_near_expired(archive_id)
    already_expired = await get_already_expired(archive_id)
    _, lack_stock = await get_lack_stock(archive_id)
    return respond_to(data={'near_expired': near_expired, 'already_expired': already_expired, 'lack_stock': lack_stock})


async def get_near_expired(archive_id: str, page_no: int = 1, page_size: int = 10):
    drug_objs = await Drug.filter(state__in=[DrugStateEnum.IN, DrugStateEnum.OUT]).prefetch_related(
        'dictionary', 'template').filter(Q(dictionary__archive_id=archive_id)).all()
    result = await drug_near_already_expired(drug_objs, "near")

    count = len(result)
    # offset = (page_no - 1) * page_size
    # drug_result = result[offset: offset + page_size]
    return count, result


async def get_already_expired(archive_id: str):
    drug_objs = await (Drug.filter(state__in=[DrugStateEnum.IN, DrugStateEnum.OUT]).prefetch_related('dictionary', 'template')
                       .filter(Q(dictionary__archive_id=archive_id)).all())
    result = await drug_near_already_expired(drug_objs, "already")
    return result


async def get_lack_stock(archive_id: str, page_no: int = 1, page_size: int = 10):
    query = QuerySet(Dictionary).filter(archive_id=archive_id)
    dictionary_ids = await query.filter().all().values_list("id", flat=True)
    drug_counts = await Drug.filter(~Q(state=DrugStateEnum.EMPTY),
                                    dictionary_id__in=dictionary_ids
                                    ).prefetch_related('dictionary').annotate(count=Count('id')).group_by(
        "dictionary_id").order_by("count").values("dictionary_id",
                                                  "count",
                                                  "template_id")

    data = list()
    for i in drug_counts:
        template_obj = await Template.get(id=i.get("template_id"))
        dictionary_obj = await Dictionary.get(id=i.get("dictionary_id"))
        lack_stock_count = dictionary_obj.params.get("lack_stock_count") if dictionary_obj.params.get(
            "lack_stock_count") else 20
        if i.get("count") < lack_stock_count:
            drug_info = await dictionary_obj.attribute_dictionary_info(template_obj)

            data.append({
                "drug_info": ",".join(list(map(lambda x:str(x), drug_info.values()))),
                "count": i.get("count"),
                "dictionary_id": i.get("dictionary_id")
            })

    count = len(data)
    # offset = (page_no - 1) * page_size
    # result = data[offset: offset + page_size]
    return count, data

@router.get('/lock', summary='修改账号锁定状态')
async def destroy(request: Request, id: str,locked:bool):
    """锁定账号"""
    user = await User.get(id=id)
    user.locked = locked
    await user.save()
    return respond_to(200)