# -*- coding:utf-8 -*- """ @Created on : 2023/7/5 16:04 @Author: hxl @Des: """ import json from datetime import datetime, timedelta from tortoise import Tortoise from helper.logger import logger from fastapi import APIRouter, Request, Depends from tortoise.expressions import Q from tortoise.functions import Count from tortoise.queryset import QuerySet from endpoints.web.warehouse import WarehouseingDrugBinding 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 from tortoise.functions import Function from pypika import CustomFunction router = APIRouter(prefix='/home_index') @router.get('/overview_index', 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()) # 今日入库 storage_count = await Drug.filter(storage_at__range=[stime, etime]).count() # 截止目前在库已过期 expire_count = await Drug.filter(state=DrugStateEnum.IN, expired_at__lt=timezone_now().strftime('%Y-%m-%d %H:%M:%S'), ).count() # 今日领用 receive_count = await (DrugUseLog.filter(state=DrugUseStateEnum.TAKE, created_at__range=[stime, etime]) .filter().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 JSON_EXTRACT(d.params, '$.first_will_empty') = true""" conn = Tortoise.get_connection(connection_name='default') receive_count_first_empty = await conn.execute_query(sql) conn.close() 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().count()) # 试剂库存总量 total_inventory = await Drug.filter(state__in=[1, 2]).count() # 库存预警 inventory_warning_count, _ = await get_lack_stock() # 临期试剂 near_warning_count, _ = await get_near_expired() # 试剂过期 already_expired_count = await get_already_expired() 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) } return respond_to(data=data) @router.get('/put_in', summary='待归还列表') async def index(request: Request, drug_name: str = '', page_no: int = 1, page_size: int = 10): """ 获取当前用户待归还清单 :return: """ # current_user = request.state.current_user # 当前柜体待归还清单 query = Drug.filter( state=2) if drug_name: query = query.filter(Q(dictionary__k1__contains=drug_name) | Q(dictionary__k2__contains=drug_name)) total_count = await query.count() offset = (page_no - 1) * page_size drugs = await query.offset(offset).limit(page_size).all() result = list() for drug_obj in drugs: receive_user_obj = await User.get_or_none(id=drug_obj.last_receive_id) data = { "drug_id": drug_obj.id, "rfid": drug_obj.rfid, "hole": drug_obj.hole, "cabinet_id": drug_obj.cabinet_id if drug_obj.cabinet_id else None, "drawer_id": drug_obj.drawer_id, "board_id": drug_obj.board_id, "position": drug_obj.position, "expired_at": drug_obj.expired_at, "remain_gross_weight": await drug_obj.format_weight(), "display_weight": await drug_obj.format_weight(), "last_receive_at": drug_obj.last_receive_at, "last_receive_user": receive_user_obj.name if receive_user_obj else '', "drug_info": drug_obj.fill_json_content, "drug_name": drug_obj.fill_json_content["k1"] } result.append(data) return respond_to(data=dict(count=total_count, drugs=result)) @router.post('/remaining_stock_index', 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(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_index', 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 for dictionary in dictionaries: drug_use_count = await DrugUseLog.filter(drug__dictionary=dictionary['id'], state=2).count() 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] return respond_to( data={'drug_use_counts': paginated_drug_use_counts, 'total_count': len(paginated_drug_use_counts)}) @router.get('/near_warning_index', summary='近期预警列表') async def get_warning(request: Request): # archive_id = request.state.archive_id _, near_expired = await get_near_expired() already_expired = await get_already_expired() _, lack_stock = await get_lack_stock() return respond_to(data={'near_expired': near_expired, 'already_expired': already_expired, 'lack_stock': lack_stock}) async def get_near_expired( page_no: int = 1, page_size: int = 10): drug_objs = await Drug.filter(state__in=[DrugStateEnum.IN, DrugStateEnum.OUT]).prefetch_related( 'dictionary', 'template').filter().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(): drug_objs = await (Drug.filter(state__in=[DrugStateEnum.IN, DrugStateEnum.OUT]).prefetch_related('dictionary', 'template') .filter().all()) result = await drug_near_already_expired(drug_objs, "already") return result async def get_lack_stock( page_no: int = 1, page_size: int = 10): query = QuerySet(Dictionary).filter() 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) @router.post('/binding', summary="入库绑定上传") async def binding_warehousing_drug(request: Request, body: WarehouseingDrugBinding): """ 绑定入库上传 already_import_count :param request: Request :param drug_list: [{"storage_item_id", str, "rfid": str, "gross_weight": float}] :return: {"abnormal_drugs": List[Dict], "normal_drugs": List[Dict]} """ drug_list = body.drug_list if not drug_list: return respond_to(code=400, desc="绑定药剂为空，上传数据有误") current_user = await User.get_or_none(id="1db35701-6385-47bb-a8b0-d09f3a9f8344") #request.state.current_user normal_drugs, abnormal_drugs = list(), list() drug_data = list() archive_id = "72143c77-cbeb-4a36-bb98-dc9d5993228c" template_obj = await Template.get(archive_id=archive_id).prefetch_related('archive') is_in_weight = template_obj.archive.params.get("storage_require_weigh") print(drug_list) for drug in drug_list: if not drug.get("rfid") or not drug.get("fill_json_content"): abnormal_drugs.append(drug) continue fill_json_content = drug["fill_json_content"] print(fill_json_content) print(is_in_weight) # 如果字典不存在改试剂条目，则新增字典条目信息 # k_args = {f'k{i}': fill_json_content.get(f'k{i}') for i in range(1, 7) if fill_json_content.get(f'k{i}')} # dictionary_obj = await Dictionary.get_or_none(**k_args) k_args = {f'k{i}': fill_json_content.get(f'k{i}') for i in range(1, 7) if fill_json_content.get(f'k{i}')} k_args_with_null = {k: None for k, v in k_args.items() if v is None} query = Q(**k_args) & Q(**k_args_with_null) dictionary_obj = await Dictionary.filter(query).first() if not dictionary_obj: k_args["archive"] = template_obj.archive k_args["params"] = {"lack_stock_count": 0, "expiration_alert": 0} dictionary_obj = await Dictionary.create(**k_args) if is_in_weight and not drug.get("gross_weight"): abnormal_drugs.append(drug) logger.warning("[入库需称重]入库药剂条目未称重：{0}".format(drug)) continue rfid = drug.get("rfid").upper() # 过期日期如果有填写(生产日期保质期)，则添加过期日期字段 if fill_json_content.get("expire_date"): expired_at = fill_json_content.get("expire_date") elif fill_json_content.get("lateDate"): expired_at = fill_json_content.get("lateDate") elif not fill_json_content.get("produce_date") or not fill_json_content.get("shelf_life"): expired_at = "" else: produce_date = fill_json_content.get("produce_date") shelf_life = fill_json_content.get("shelf_life") production_date = datetime.strptime(produce_date, '%Y-%m-%d') shelf_life_date = timedelta(days=int(shelf_life)) expired_at = production_date + shelf_life_date expired_at = expired_at.strftime('%Y-%m-%d') mill_gross_weight = drug.get("gross_weight") # 位置信息 # drawer_obj = await Drawer.get(id=drug.get("drawer_id")).prefetch_related("cabinet") # cabinet = drawer_obj.cabinet # position_str = f"{cabinet.label}-{drawer_obj.label}" now = timezone_now() drug_dict = { "dictionary_id": dictionary_obj.id, "rfid": rfid, "fill_json_content": fill_json_content, "remain_gross_weight": mill_gross_weight, "bind_id": current_user.id, "bind_at": now, "storage_id": current_user.id, "storage_at": now, "template": template_obj, # "cabinet_id": drug.get("cabinet_id"), "drawer_id": drug.get("drawer_id"), # "position": position_str, "state": 0 if len(rfid) >= 16 else 1, "barcode": rfid if len(rfid) < 16 else '' } if expired_at: drug_dict.setdefault("expired_at", expired_at) drug_data.append(drug_dict) # 药剂批量保存 for data in drug_data: try: print(data) await Drug.create(**data) normal_drugs.append(data) except Exception as e: logger.error("药剂导入绑定异常：{0}".format(e)) abnormal_drugs.append(data) desc = "药剂部分保存成功" if abnormal_drugs else "药剂保存成功" return respond_to(code=200, desc=desc, data={"abnormal_drugs": abnormal_drugs, "normal_drugs": normal_drugs}) class JsonExtract(Function): database_func=CustomFunction('JSON_EXTRACT', ['field', 'value']) @router.post('/cabinets', summary='柜体下拉列表') async def index(): """ 获取柜体下拉列表 :return: """ annotate = {} annotate['type'] = JsonExtract('params', '$.type') query = Cabinet.annotate(**annotate).filter(Q(type__lt=3)).filter() cabinets = await query.all().values('id', 'label') restlt = [] for cab in cabinets: drawer_boards = await DrawerBoard.filter(cabinet_id=cab['id']).all() capacity = sum([drawer_board.capacity for drawer_board in drawer_boards]) if drawer_boards else 0 cab_dict = { 'label':cab['label'], 'total_capacity':capacity, } restlt.append(cab_dict) return respond_to(data=restlt)