# -*- 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)