python-fastapi-mm-zhcs-yj-api/routers/api/resourceProvison/MaterialReserveManagement/warehouse.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.websocketManager import *
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_type = ResourceProvisionWarehouseInfo(
            warehouse_id = new_guid(),
            warehouse_name=body['warehouse_name'],
            status=body['status'],
            contact_person=body['contact_person'],
            contact_phone=body['contact_phone'],
            address=body['address'],
            type=body['type'],
            level=body['level'],
            storage_dept_id=body['storage_dept_id'],
            storage_dept_name='', #body['storage_dept_name']
            area_name=body['area_name'],
            longitude=body['longitude'],
            latitude=body['latitude'],
            area =body['area'],
            remark=body['remark'],
            create_by = user_id
        )
        db.add(new_type)
        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:
        update_warehouse = warehouse_id_get_warehouse_info(db,id)
        if not update_warehouse:
            return JSONResponse(status_code=404,content={"code":404,"msg":"warehouse not found"})
        update_warehouse.warehouse_name = body['warehouse_name']
        update_warehouse.status = body['status']
        update_warehouse.contact_person = body['contact_person']
        update_warehouse.contact_phone = body['contact_phone']
        update_warehouse.address = body['address']
        update_warehouse.type = body['type']
        update_warehouse.level = body['level']
        update_warehouse.storage_dept_id = body['storage_dept_id']
        update_warehouse.storage_dept_name = body['storage_dept_name']
        update_warehouse.area_name = body['area_name']
        update_warehouse.longitude = body['longitude']
        update_warehouse.latitude = body['latitude']
        update_warehouse.area = body['area']
        update_warehouse.remark = body['remark']
        update_warehouse.update_by = user_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("/info/{id}")
async def get_pattern_info(
    id: str,
    user_id=Depends(valid_access_token),
    db: Session = Depends(get_db)
):
    try:

        info = warehouse_id_get_warehouse_info(db,id)
        if not info:
            return JSONResponse(status_code=404,content={"code":404,"msg":"warehouse not found"})
        dept_info = dept_id_get_dept_info(db,info.storage_dept_id)
        data = {"warehouse_id": info.warehouse_id,
                "warehouse_name": info.warehouse_name,
                "status": info.status,
                "contact_person": info.contact_person,
                "contact_phone": info.contact_phone,
                "address": info.address,
                "remark": info.remark,
                "type": info.type,
                "level": info.level,
                "storage_dept_id": info.storage_dept_id,
                "storage_dept_name": dept_info.dept_name,
                "area_name": info.area_name,
                "longitude": info.longitude,
                "latitude": info.latitude,
                "create_by": info.create_by,
                "area":info.area,
                "create_time":info.create_time}
        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(
    # name: str = Query(None, description='名称'),
    user_id=Depends(valid_access_token),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(None, gt=0, description='每页条目数量'),
    db: Session = Depends(get_db)
):
    try:
        query = db.query(ResourceProvisionWarehouseInfo)
        query = query.filter_by(del_flag='0')
        # if name:
        #     query = query.filter(ResourceProvisionWarehouseInfo.material_category_name.like(f'%{name}%'))
        total_items = query.count()
        if pageSize is None:
            pageSize=total_items
        # 排序
        query = query.order_by(ResourceProvisionWarehouseInfo.create_time.desc())
        # 执行分页查询
        lists = query.offset((page - 1) * pageSize).limit(pageSize).all()
        data = [ ]
        for info in lists:
            dept_info = dept_id_get_dept_info(db, info.storage_dept_id)
            data.append({"warehouse_id": info.warehouse_id,
                "warehouse_name": info.warehouse_name,
                "status": info.status,
                "contact_person": info.contact_person,
                "contact_phone": info.contact_phone,
                "address": info.address,
                "remark": info.remark,
                "type": info.type,
                "level": info.level,
                "storage_dept_id": info.storage_dept_id,
                "storage_dept_name": dept_info.dept_name,
                "area_name": info.area_name,
                "longitude": info.longitude,
                "latitude": info.latitude,
                "create_by": info.create_by,
                "area":info.area,
                "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("/export")
async def export_data(
        request: Request,
    # name: str = Query(None, description='名称'),
    user_id=Depends(valid_access_token),
        auth_user: AuthUser = Depends(find_auth_user),
    db: Session = Depends(get_db)
):
    try:
        query = db.query(ResourceProvisionWarehouseInfo)
        query = query.filter_by(del_flag='0')
        # if name:
        #     query = query.filter(ResourceProvisionWarehouseInfo.material_category_name.like(f'%{name}%'))

        # 排序
        query = query.order_by(ResourceProvisionWarehouseInfo.create_time.desc())
        # 执行分页查询
        lists = query.all()
        data = []
        for info in lists:
            dept_info = dept_id_get_dept_info(db, info.storage_dept_id)
            data.append({"仓库id": info.warehouse_id,
                "仓库": info.warehouse_name,
                "状态": info.status,
                "联系人": info.contact_person,
                "联系电话": info.contact_phone,
                "地址": info.address,
                "备注": info.remark,
                "类型": info.type,
                "等级": info.level,
                "物资保管部门id": info.storage_dept_id,
                "物资保管部门名称": dept_info.dept_name,
                "地区": info.area_name,
                "经度": info.longitude,
                "纬度": info.latitude,
                "创建者": info.create_by,
                "占地面积(平方米)":info.area,
                "数据创建时间":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()
        return JSONResponse(status_code=500, content={"code": 500, "msg": 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 = warehouse_id_get_warehouse_info(db, id)
        if not info:
            return JSONResponse(status_code=404, content={"code": 404, "msg": "warehouse not found"})
        info.del_flag='2'
        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.post("/import_data")
async def import_data(
    body = Depends(remove_xss_json),
    user_id=Depends(valid_access_token), db: Session = Depends(get_db)):
    # 获取表结构
    import os,openpyxl
    schema_name = "mmyjhd"
    table_name = "resource_provision_warehouse_info"
    columns = [{"column_name":"warehouse_name","column_comment":"仓库名称"},
               {"column_name":"status","column_comment":"状态1启用0关闭"},
               {"column_name":"contact_person","column_comment":"联系人"},
               {"column_name":"contact_phone","column_comment":"联系电话"},
               {"column_name":"address","column_comment":"地址"},
               {"column_name":"type","column_comment":"类型"},
               {"column_name":"level","column_comment":"等级"},
               {"column_name":"storage_dept_name","column_comment":"物资保管部门名称"},
               {"column_name":"area_name","column_comment":"地区(区县)"},
               {"column_name":"longitude","column_comment":"经度"},
               {"column_name":"latitude","column_comment":"纬度"},
               {"column_name":"area","column_comment":"占地面积(平方米)"},
               {"column_name":"remark","column_comment":"备注"}]
    filename = body['filename']
    if '../' in filename or '/' in filename:
        return JSONResponse(status_code=400, content={'code': 400, "msg": '警告：禁止篡改文件路径'})
    file_path = f'/data/upload/mergefile/uploads/{filename}'
    if not os.path.exists(file_path):
        return JSONResponse(status_code=404, content={'code': 404, 'msg': f"文件不存在"})
        # print("文件不存在，请检查路径！")
    # 读取 Excel 文件
    try:
        workbook = openpyxl.load_workbook(file_path)
        sheet = workbook.active
        data = pd.read_excel(file_path, header=1)
        data = data.to_dict(orient='records')
    except Exception as e:
        traceback.print_exc()
        return JSONResponse(status_code=400, content={'code': 400, 'msg': f"接口发生错误:{e}"})
        # raise HTTPException(status_code=400, detail="Invalid Excel file")

        # 获取字段名和字段备注名
    column_names = [col["column_name"] for col in columns if col["column_name"]!='id']
    column_comments = [col["column_comment"] for col in columns if col["column_name"]!='id']

    # 检查第一行是否为字段备注名
    first_row = [cell.value for cell in sheet[1]]
    if first_row != column_comments:
        print("接口发生错误:Excel columns do not match the expected columns")

        return JSONResponse(status_code=400, content={'code': 400, 'msg': f"接口发生错误:Excel columns do not match the expected columns"})
        # raise HTTPException(status_code=400, detail="Excel columns do not match the expected columns")

    # 检查第二行是否为字段名
    second_row = [cell.value for cell in sheet[2]]
    if second_row != column_names:
        print("接口发生错误:Excel columns do not match the expected columns")
        return JSONResponse(status_code=400,
                            content={'code': 400, 'msg': f"接口发生错误:Excel columns do not match the expected columns"})

        # raise HTTPException(status_code=400, detail="Excel columns do not match the expected columns")

    # 将数据插入到数据库
    try:
        # insert_query = text(
        #     f"INSERT INTO `{schema_name}`.`{table_name}` ({', '.join(column_names)}) VALUES ({', '.join([':' + col for col in column_names])})")
        # for row in sheet.iter_rows(min_row=3, values_only=True):
        for row in data:
            infotype = row['type']
            'warehouse_type'
            'warehouse_level'
            new_type = ResourceProvisionWarehouseInfo(
                warehouse_id=new_guid(),
                warehouse_name=row['warehouse_name'],
                status=row['status'],
                contact_person=row['contact_person'],
                contact_phone=row['contact_phone'],
                address=row['address'],
                type=infotype,
                level=row['level'],
                storage_dept_id=row['storage_dept_id'],
                storage_dept_name=row['storage_dept_name'],  # body['storage_dept_name']
                area_name=row['area_name'],
                longitude=row['longitude'],
                latitude=row['latitude'],
                area=row['area'],
                remark=row['remark'],
                create_by=user_id
            )
            db.add(new_type)
        db.commit()
            # db.execute(insert_query, dict(zip(column_names, row)))
        # db.commit()
        return {"code":200,"msg": "Data imported successfully"}
    except Exception as e:
        db.rollback()
        traceback.print_exc()
        return JSONResponse(status_code=500,
                            content={'code': 500, 'msg': f"接口发生错误:{e}"})

# 导入
@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)
):
    # print(body)

    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 = []
        '''
        for i in range(9, sheet.nrows):

            # 预案名称
            plan_name = sheet.cell(i, 0).value

            # 一级目录
            title1 = sheet.cell(i, 1).value

            # 二级目录
            title2 = sheet.cell(i, 2).value

            # 三级目录
            title3 = sheet.cell(i, 3).value

            # 正文
            content = sheet.cell(i, 4).value

            if len(plan_name) < 1 and len(title1) < 1 and len(title2) < 1 and len(title3) < 1 and len(content) < 1 :
                break

            data.append({
                'plan_name': plan_name,
                'title1': title1,
                'title2': title2,
                'title3': title3,
                'content': content,
            })

        if len(data) > 0:
            db.query(EmergencyDoc).filter(EmergencyDoc.plan_id == plan_id).delete()
            db.commit()

        title1 = ''
        content = ''
        docs = []
        for n in data:
            if n['title1'] != '':
                if len(docs) > 0:
                    add_doc_1(db, title1, content, docs, plan_id)

                docs = []
                title1 = n['title1']
                content = n['content']
                if n['title2'] != '':
                    docs.append(n)
                continue

            docs.append(n)

        if len(docs) > 0:
            add_doc_1(db, title1, content, docs, plan_id)
        '''

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