python-fastapi-mm-zhcs-yj-api/routers/api/resourceProvison/MaterialReserveManagement/material.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from fastapi import APIRouter, Request, Depends, Query, HTTPException, status,WebSocket,WebSocketDisconnect
from common.security import valid_access_token,valid_websocket_token
from fastapi.responses import JSONResponse,StreamingResponse
from common.db import db_czrz
from sqlalchemy.orm import Session
from sqlalchemy.sql import func
from common.auth_user import *
from sqlalchemy import  text
from pydantic import BaseModel
from common.BigDataCenterAPI import *
from database import get_db
from typing import List
from models import *
from utils import *
from utils.spatial import *
from utils.ry_system_util import *
from utils.resource_provision_util import *
from common.barcode import create_bar,create_qr
import json
import traceback
import xlrd
import os

# 目录在文档上传接口写死
UPLOAD_mergefile_PATH = '/data/upload/mergefile'

router = APIRouter()


@router.post("/create")
async def create_pattern(
    user_id=Depends(valid_access_token),
    body = Depends(remove_xss_json),
    db: Session = Depends(get_db)
):
    try:
        new_material = ResourceProvisionMaterialInfo(
            # id = new_guid(),
            material_name=body['material_name'],
            warehouse_id=body['warehouse_id'],
            inventory=body['inventory'],
            specification=body['specification'],
            model=body['model'],
            category_name=body['category_name'],
            material_type=body['material_type_id'],
            unit_name=body['unit_name'],
            brand_name=body['brand_name'],
            length=body['length'],
            width=body['width'],
            height=body['height'],
            volume=body['volume'],
            gross_weight=body['gross_weight'],
            net_weight=body['net_weight'],
            manufacturer=body['manufacturer'],
            origin=body['origin'],
            status=body['status'],
            room_id=body['room_id'],
            package_quantity=body['package_quantity'],
            package_volume=body['package_volume'],
            package_weight=body['package_weight'],
            price=body['price'],
            selling_price=body['selling_price'],
            value=body['value'],
            cost_price=body['cost_price'],
            disaster_types=body['disaster_types'],
            maintenance=body['maintenance'],
            supplier_name=body['supplier_name'],
            special_transportation_requirements=body['special_transportation_requirements'],
            material=body['material'],
            shelf_life=body['shelf_life'],
            inventory_warning_pusher=body['inventory_warning_pusher'],
            inventory_warning_quantity=body['inventory_warning_quantity'],
            shelf_life_warning_days=body['shelf_life_warning_days'],
            shelf_life_warning_pusher=body['shelf_life_warning_pusher'],
            from_sys=body['from_sys'],
            create_by = user_id
        )

        db.add(new_material)
        new_file_list = body['fileList']
        db.commit()
        db.refresh(new_material)
        for file in new_file_list:
            file_name = file['url']
            file_name_desc = file['name']
            status = file['status']
            uid = file['uid']
            new_file = ResourceProvisionFile(
                file_id=uid,
                foreign_key=new_material.material_id,
                from_scenario='ResourceProvisionMaterialInfo',
                file_name=file_name,
                file_name_desc=file_name_desc,
                status=status
            )
            db.add(new_file)
        bar_fileanme = new_guid()+'.png'
        qr_fileanme = new_guid()+'.png'
        if create_bar(str(new_material.material_id),bar_fileanme) and create_qr(str(new_material.material_id),qr_fileanme):
            barcode = ResourceProvisionMaterialBarcode(
                id=new_guid(),
                material_code = new_material.material_id,
                barcode = bar_fileanme,
                qr_code = qr_fileanme
            )
            db.add(barcode)
        db.commit()
        return {"code": 200, "msg": "创建成功", "data": None}
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")



@router.put("/update/{id}")
async def update_pattern(
    id :str ,
    user_id=Depends(valid_access_token),
    body=Depends(remove_xss_json),
    db: Session = Depends(get_db)
):
    try:
        info = material_id_get_material_info(db,id)
        if not info:
            return JSONResponse(status_code=404,content={"code":404,"msg":"warehouse room not found"})
        # info.room_name = body['room_name']
        info.material_name = body['material_name']
        info.warehouse_id = body['warehouse_id']
        info.inventory = body['inventory']
        info.specification = body['specification']
        info.model = body['model']
        info.category_name = body['category_name']
        info.material_type = body['material_type_id']
        info.unit_name = body['unit_name']
        info.brand_name = body['brand_name']
        info.length = body['length']
        info.width = body['width']
        info.height = body['height']
        info.volume = body['volume']
        info.gross_weight = body['gross_weight']
        info.net_weight = body['net_weight']
        info.manufacturer = body['manufacturer']
        info.origin = body['origin']
        info.status = body['status']
        info.room_id = body['room_id']
        info.package_quantity = body['package_quantity']
        info.package_volume = body['package_volume']
        info.package_weight = body['package_weight']
        info.price = body['price']
        info.selling_price = body['selling_price']
        info.value = body['value']
        info.cost_price = body['cost_price']
        info.disaster_types = body['disaster_types']
        info.maintenance = body['maintenance']
        info.supplier_name = body['supplier_name']
        info.special_transportation_requirements = body['special_transportation_requirements']
        info.material = body['material']
        info.shelf_life = body['shelf_life']
        info.inventory_warning_pusher = body['inventory_warning_pusher']
        info.inventory_warning_quantity = body['inventory_warning_quantity']
        info.shelf_life_warning_days = body['shelf_life_warning_days']
        info.shelf_life_warning_pusher = body['shelf_life_warning_pusher']
        info.from_sys = body['from_sys']
        info.update_by = user_id

        delete_resource_provision_file(db,'ResourceProvisionMaterialInfo',id)


        new_file_list = body['fileList']
        for file in new_file_list:
            file_name = file['url']
            file_name_desc = file['name']
            status = file['status']
            uid = file['uid']
            new_file = ResourceProvisionFile(
                file_id=uid,
                foreign_key=id,
                from_scenario='ResourceProvisionMaterialInfo',
                file_name=file_name,
                file_name_desc=file_name_desc,
                status=status
            )
            db.add(new_file)
        db.commit()
        return {"code": 200, "msg": "更新成功"}
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.get("/info/{id}")
async def get_pattern_info(
    id: str,
    user_id=Depends(valid_access_token),
    db: Session = Depends(get_db)
):
    try:

        info = material_id_get_material_info(db,id)
        if not info:
            return JSONResponse(status_code=404,content={"code":404,"msg":"warehouse room not found"})
        warehouse_info = warehouse_id_get_warehouse_info(db,info.warehouse_id)
        if  warehouse_info:
            warehouse_name = warehouse_info.warehouse_name
        else:
            warehouse_name=None
        material_type_info = type_id_get_material_type_info(db,info.material_type)
        if  material_type_info:
            material_category_name = material_type_info.material_category_name
        else:
            material_category_name=None
        warehouse_room_info = warehouse_room_id_get_warehouse_room_info(db, info.room_id)
        if  warehouse_room_info:
            room_name = warehouse_room_info.room_name
        else:
            room_name = None
        data = {
            "material_id": info.material_id,
            "material_name": info.material_name,
            "warehouse_id": info.warehouse_id,
            "warehouse_name": warehouse_name,
            "inventory": info.inventory,
            "specification": info.specification,
            "model": info.model,
            "category_name": info.category_name,
            "material_type_id": info.material_type,
            "material_type_name": material_category_name,
            "unit_name": info.unit_name,
            "brand_name": info.brand_name,
            "length": info.length,
            "width": info.width,
            "height": info.height,
            "volume": info.volume,
            "gross_weight": info.gross_weight,
            "net_weight": info.net_weight,
            "manufacturer": info.manufacturer,
            "origin": info.origin,
            "status": info.status,
            "room_id": info.room_id,
            "room_name":room_name,
            "package_quantity": info.package_quantity,
            "package_volume": info.package_volume,
            "package_weight": info.package_weight,
            "price": info.price,
            "selling_price": info.selling_price,
            "value": info.value,
            "cost_price": info.cost_price,
            "disaster_types": info.disaster_types,
            "maintenance": info.maintenance,
            "supplier_name": info.supplier_name,
            "special_transportation_requirements": info.special_transportation_requirements,
            "material": info.material,
            "shelf_life": info.shelf_life,
            "inventory_warning_pusher": info.inventory_warning_pusher,
            "inventory_warning_quantity": info.inventory_warning_quantity,
            "shelf_life_warning_days": info.shelf_life_warning_days,
            "shelf_life_warning_pusher": info.shelf_life_warning_pusher,
            "from_sys": info.from_sys,
            "create_time":info.create_time,
            "fileList": get_resource_provision_file_query_fun(db=db,from_scenario='ResourceProvisionMaterialInfo', foreign_key=info.material_id)}
        return {"code": 200, "msg": "获取成功", "data": data}
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.get("/list")
async def get_pattern_list(
    user_id=Depends(valid_access_token),
    material_id: str = Query(None, description='名称'),
    material_name: str = Query(None, description='名称'),
    warehouse_id: str = Query(None, description='名称'),
    from_sys: str = Query(None, description='名称'),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(10, gt=0, description='每页条目数量'),
    db: Session = Depends(get_db)
):
    try:
        query = db.query(ResourceProvisionMaterialInfo)
        query = query.filter_by(del_flag='0')
        if material_id:
            query = query.filter(ResourceProvisionMaterialInfo.material_id.like(f'%{material_id}%'))
        if material_name:
            query = query.filter(ResourceProvisionMaterialInfo.material_name.like(f'%{material_name}%'))
        if warehouse_id:
            query = query.filter(ResourceProvisionMaterialInfo.warehouse_id==warehouse_id)
        if from_sys:
            query = query.filter(ResourceProvisionMaterialInfo.from_sys==from_sys)
        total_items = query.count()
        # 排序
        query = query.order_by(ResourceProvisionMaterialInfo.create_time.desc())
        # 执行分页查询
        lists = query.offset((page - 1) * pageSize).limit(pageSize).all()
        data = []
        for info in lists:
            warehouse_info = warehouse_id_get_warehouse_info(db, info.warehouse_id)
            if  warehouse_info:
                warehouse_name = warehouse_info.warehouse_name
            else:
                warehouse_name = None
            material_type_info = type_id_get_material_type_info(db, info.material_type)
            if  material_type_info:
                material_category_name = material_type_info.material_category_name
            else:
                material_category_name = None
            warehouse_room_info = warehouse_room_id_get_warehouse_room_info(db, info.room_id)
            if  warehouse_room_info:
                room_name = warehouse_room_info.room_name
            else:
                room_name = None
            inventory = material_id_get_material_remaining_stock(db,info.material_id)
            data.append({
            "material_id": info.material_id,
            "material_name": info.material_name,
            "warehouse_id": info.warehouse_id,
            "warehouse_name": warehouse_name,
            "inventory": inventory,
            "specification": info.specification,
            "model": info.model,
            "category_name": info.category_name,
            "material_type_id": info.material_type,
            "material_type_name": material_category_name,
            "unit_name": info.unit_name,
            "brand_name": info.brand_name,
            "length": info.length,
            "width": info.width,
            "height": info.height,
            "volume": info.volume,
            "gross_weight": info.gross_weight,
            "net_weight": info.net_weight,
            "manufacturer": info.manufacturer,
            "origin": info.origin,
            "status": info.status,
            "room_id": info.room_id,
            "room_name":room_name,
            "package_quantity": info.package_quantity,
            "package_volume": info.package_volume,
            "package_weight": info.package_weight,
            "price": info.price,
            "selling_price": info.selling_price,
            "value": info.value,
            "cost_price": info.cost_price,
            "disaster_types": info.disaster_types,
            "maintenance": info.maintenance,
            "supplier_name": info.supplier_name,
            "special_transportation_requirements": info.special_transportation_requirements,
            "material": info.material,
            "shelf_life": info.shelf_life,
            "inventory_warning_pusher": info.inventory_warning_pusher,
            "inventory_warning_quantity": info.inventory_warning_quantity,
            "shelf_life_warning_days": info.shelf_life_warning_days,
            "shelf_life_warning_pusher": info.shelf_life_warning_pusher,
            "from_sys": info.from_sys,
            "create_time":info.create_time,
            "fileList": get_resource_provision_file_query_fun(db=db,from_scenario='ResourceProvisionMaterialInfo', foreign_key=info.material_id)})
        return {"code": 200, "msg": "查询成功", "data": data,
                "total": total_items,
                "page": page,
                "pageSize": pageSize,
                "totalPages": (total_items + pageSize - 1) // pageSize
                }
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")


@router.get("/list")
async def get_pattern_list(
        request: Request,
    user_id=Depends(valid_access_token),
    material_id: str = Query(None, description='名称'),
    material_name: str = Query(None, description='名称'),
    warehouse_id: str = Query(None, description='名称'),
    from_sys: str = Query(None, description='名称'),
        auth_user: AuthUser = Depends(find_auth_user),
    db: Session = Depends(get_db)
):
    try:
        query = db.query(ResourceProvisionMaterialInfo)
        query = query.filter_by(del_flag='0')
        if material_id:
            query = query.filter(ResourceProvisionMaterialInfo.material_id.like(f'%{material_id}%'))
        if material_name:
            query = query.filter(ResourceProvisionMaterialInfo.material_name.like(f'%{material_name}%'))
        if warehouse_id:
            query = query.filter(ResourceProvisionMaterialInfo.warehouse_id==warehouse_id)
        if from_sys:
            query = query.filter(ResourceProvisionMaterialInfo.from_sys==from_sys)
        # 排序
        query = query.order_by(ResourceProvisionMaterialInfo.create_time.desc())
        # 执行分页查询
        lists = query.all()
        data = []
        for info in lists:
            warehouse_info = warehouse_id_get_warehouse_info(db, info.warehouse_id)
            if  warehouse_info:
                warehouse_name = warehouse_info.warehouse_name
            else:
                warehouse_name = None
            material_type_info = type_id_get_material_type_info(db, info.material_type)
            if  material_type_info:
                material_category_name = material_type_info.material_category_name
            else:
                material_category_name = None
            warehouse_room_info = warehouse_room_id_get_warehouse_room_info(db, info.room_id)
            if  warehouse_room_info:
                room_name = warehouse_room_info.room_name
            else:
                room_name = None
            data.append({
            "物资编码": info.material_id,
            "物资名称": info.material_name,
            "仓库id": info.warehouse_id,
            "仓库": warehouse_name,
            "库存": info.inventory,
            "规格": info.specification,
            "型号": info.model,
            "分类名称": info.category_name,
            "物资类型id": info.material_type,
            "物资类型": material_category_name,
            "计量单位名称": info.unit_name,
            "品牌名称": info.brand_name,
            "长(厘米)": info.length,
            "宽(厘米)": info.width,
            "高(厘米)": info.height,
            "体积(立方厘米)": info.volume,
            "毛重(kg)": info.gross_weight,
            "净重(kg)": info.net_weight,
            "生产厂商": info.manufacturer,
            "产地": info.origin,
            "状态": info.status,
            "库房id": info.room_id,
            "库房":room_name,
            "包装数量": info.package_quantity,
            "包装体积(立方厘米)": info.package_volume,
            "包装重量(kg)": info.package_weight,
            "价格": info.price,
            "售卖价格": info.selling_price,
            "价值": info.value,
            "成本价格": info.cost_price,
            "适用灾种": info.disaster_types,
            "使用保养": info.maintenance,
            "供应商名称": info.supplier_name,
            "特殊运输要求": info.special_transportation_requirements,
            "材质": info.material,
            "保质期": info.shelf_life,
            "库存预警推送人": info.inventory_warning_pusher,
            "库存预警数量": info.inventory_warning_quantity,
            "保质期到期预警天数": info.shelf_life_warning_days,
            "保质期预警推送人": info.shelf_life_warning_pusher,
            "来源系统": info.from_sys,
            "数据创建时间":info.create_time})
            # "fileList": get_resource_provision_file_query_fun(db=db,from_scenario='ResourceProvisionMaterialInfo', foreign_key=info.material_id)})

        # 返回结果
        import pandas as pd
        from io import BytesIO
        # 将查询结果转换为 DataFrame
        df = pd.DataFrame(data)

        # 将 DataFrame 导出为 Excel 文件
        output = BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False)

        # 设置响应头
        output.seek(0)
        from urllib.parse import quote
        encoded_filename = f'物资明细{datetime.now().strftime("%Y%m%d%H%mi%s")}.xlsx'
        encoded_filename = quote(encoded_filename, encoding='utf-8')
        headers = {
            'Content-Disposition': f'attachment; filename*=UTF-8\'\'{encoded_filename}',
            'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        }

        db_czrz.log(db, auth_user, "物资储备管理", f"物资储备管理物资明细导出数据成功", request.client.host)

        # 返回文件流
        return StreamingResponse(output, headers=headers)

    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.delete("/delete/{id}")
async def delete_pattern(
    id: str,
    user_id=Depends(valid_access_token),
    db: Session = Depends(get_db)
):
    try:
        # 检查图案是否存在
        info = material_id_get_material_info(db, id)
        if not info:
            return JSONResponse(status_code=404, content={"code": 404, "msg": "warehouse room not found"})
        bar_info = material_id_get_material_barcode_info(db,id)
        info.del_flag='2'
        if  bar_info is not None:
            bar_info.del_flag = '2'
        delete_resource_provision_file(db, 'ResourceProvisionMaterialInfo', id)
        db.commit()
        return {"code": 200, "msg": "删除成功"}
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")



@router.get("/barcode/list")
async def get_pattern_list(
    user_id=Depends(valid_access_token),
    name: str = Query(None, description='名称'),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(None, gt=0, description='每页条目数量'),
    db: Session = Depends(get_db)
):
    try:
        material_code_list=None
        if name:
            query_1 = db.query(ResourceProvisionMaterialInfo.material_id)
            query_1 = query_1.filter(ResourceProvisionMaterialInfo.del_flag=='0')
            query_1 = query_1.filter(ResourceProvisionMaterialInfo.material_name.like(f'%{name}%'))
            material_code_list =[i.material_id  for i in  query_1.all()]
        query = db.query(ResourceProvisionMaterialBarcode)
        query = query.filter_by(del_flag='0')
        if material_code_list is not None:
            query = query.filter(ResourceProvisionMaterialBarcode.material_code.in_(material_code_list))
        total_items = query.count()
        # 排序
        if pageSize is None:
            pageSize=total_items
        query = query.order_by(ResourceProvisionMaterialBarcode.create_time.desc())
        # 执行分页查询
        lists = query.offset((page - 1) * pageSize).limit(pageSize).all()
        data = []
        for info in lists:
            material_info = material_id_get_material_info(db, info.material_code)
            data.append({"id": info.id,
                "material_name": material_info.material_name,
                "material_code": info.material_code,
                "barcode": info.barcode,
                "qr_code": info.qr_code,
                "status": info.status,
                "create_time": info.create_time} )

        return {"code": 200, "msg": "查询成功", "data": data,
                "total": total_items,
                "page": page,
                "pageSize": pageSize,
                "totalPages": (total_items + pageSize - 1) // pageSize
                }
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.get("/barcode/export")
async def get_pattern_list(
        request: Request,
    user_id=Depends(valid_access_token),
    name: str = Query(None, description='名称'),
        auth_user: AuthUser = Depends(find_auth_user),
    db: Session = Depends(get_db)
):
    try:
        material_code_list=None
        if name:
            query_1 = db.query(ResourceProvisionMaterialInfo.material_id)
            query_1 = query_1.filter(ResourceProvisionMaterialInfo.del_flag=='0')
            query_1 = query_1.filter(ResourceProvisionMaterialInfo.material_name.like(f'%{name}%'))
            material_code_list =[i.material_id  for i in  query_1.all()]
        query = db.query(ResourceProvisionMaterialBarcode)
        query = query.filter_by(del_flag='0')
        if material_code_list is not None:
            query = query.filter(ResourceProvisionMaterialBarcode.material_code.in_(material_code_list))
        total_items = query.count()
        # 排序
        query = query.order_by(ResourceProvisionMaterialBarcode.create_time.desc())
        # 执行分页查询
        lists = query.all()
        data = []
        for info in lists:
            material_info = material_id_get_material_info(db, info.material_code)
            status = ""
            if info.status == "1":
                status = '启用'
            else:
                status='禁用'
            data.append({"id": info.id,
                "物资名称": material_info.material_name,
                "物资编号": info.material_code,
                "条形码": 'http://120.241.74.139:8086/api/file/get_img/get_img_by_id/'+info.barcode,
                "二维码": 'http://120.241.74.139:8086/api/file/get_img/get_img_by_id/'+info.qr_code,
                "条码状态":  status,
                "创建时间": info.create_time} )

        # 返回结果
        import pandas as pd
        from io import BytesIO
        # 将查询结果转换为 DataFrame
        df = pd.DataFrame(data)

        # 将 DataFrame 导出为 Excel 文件
        output = BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False)

        # 设置响应头
        output.seek(0)
        from urllib.parse import quote
        encoded_filename = f'条码管理{datetime.now().strftime("%Y%m%d%H%mi%s")}.xlsx'
        encoded_filename = quote(encoded_filename, encoding='utf-8')
        headers = {
            'Content-Disposition': f'attachment; filename*=UTF-8\'\'{encoded_filename}',
            'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        }

        db_czrz.log(db, auth_user, "物资储备管理", f"物资储备管理条码管理导出数据成功", request.client.host)

        # 返回文件流
        return StreamingResponse(output, headers=headers)
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.put('/barcode/changeStatus')
async def change_barcode_status(
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        user_id=Depends(valid_access_token)
):
    try:
        barcode_id = body['id']
        status = body['status']
        info = material_barcode_id_get_material_barcode_info(db, barcode_id)
        if not info:
            return JSONResponse(status_code=404, content={"code": 404, "msg": "barcode not found"})
        info.status= status
        info.update_by=user_id
        db.commit()
        return {
            "code": 200,
            "msg": "操作成功"
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))


@router.get('/treeselect')
async def gettreeselect(request: Request,material_name: str = Query(None, description='名称'),material_type_name: str = Query(None, description='名称'),db: Session = Depends(get_db), user_id: int = Depends(valid_access_token)):


    query = db.query(ResourceProvisionWarehouseInfo)
    query = query.filter_by(del_flag='0')
    query = query.filter_by(status='1')
    query = query.order_by(ResourceProvisionWarehouseInfo.create_time.desc())
    lists = query.all()
    data = []
    for info in lists:
        data_info = {"id": info.warehouse_id,
                     "label": info.warehouse_name,
                     "isWarehouse": True}
        # material_list = warehouse_id_get_material_list(db,info.warehouse_id)
        query1 = db.query(ResourceProvisionMaterialInfo)
        query1 = query1.filter_by(warehouse_id=info.warehouse_id, del_flag='0')
        if material_name:
            query1 = query1.filter_by(material_name=material_name)
        if material_type_name:
            query2 = db.query(ResourceProvisionMaterialType)
            query2 = query2.filter(ResourceProvisionMaterialType.material_category_name.like(f'%{material_type_name}%'), ResourceProvisionMaterialType.del_flag=='0')
            material_type_list = [str(material_type.id) for material_type in query2.all()]
            query1 = query1.filter(ResourceProvisionMaterialInfo.material_type.in_(material_type_list))
        material_list=  query1.all()
        if material_list:
            data_info['children'] = []
            for material in material_list:
                type_info = type_id_get_material_type_info(db,material.material_type)
                material_category_name = None
                if type_info:
                    material_category_name = type_info.material_category_name
                data_info['children'].append({"id": material.material_id,
                         "label": material.material_name,
                         "type_id": material.material_type,
                         "type":material_category_name,
                         "warehouse_id": info.warehouse_id,
                         "warehouse_name":info.warehouse_name,
                         "isWarehouse": False})
        data.append(data_info)
    return {
        "code": 200,
        "msg": "操作成功",
        "data": data
    }

# 导入
@router.post('/import')
async def import_doc(
    request: Request, 
    db: Session = Depends(get_db), 
    body = Depends(remove_xss_json), 
    user_id = Depends(valid_access_token)
):
    
    try:
        filename = body['filename']
        if len(filename) == 0:
            raise Exception()
        
        file = filename[0]
        url = file['url']
        file_path = f"{UPLOAD_mergefile_PATH}/uploads/{url}"
        file_path = os.path.abspath(file_path)
        print(file_path)
        
        book = xlrd.open_workbook(file_path)
        sheet = book.sheet_by_index(0)

        data = []

        return {
            'code': 200,
            'msg': '导入成功'
        }
    except Exception:
        traceback.print_exc()
        return {
            'code': 500,
            'msg': '导入发生异常'
        }