python-fastapi-mm-zhcs-yj-api/routers/api/resourceProvison/transportCommunicationMaterial/__init__.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()
def resource_material_id_get_material_info(db,id,resource_type):
    query = db.query(TransportCommunicationMaterial)
    query = query.filter_by(id = id,del_flag = '0',resource_type=resource_type)
    return query.first()

@router.post("/{resource_type}/create")
async def create_pattern(
    resource_type:str,
    user_id=Depends(valid_access_token),
    body = Depends(remove_xss_json),
    db: Session = Depends(get_db)
):
    try:
        if resource_type=='comm':
            resource_type = 'COMM'
        elif resource_type=='transport':
            resource_type = 'TRANSPORT'
        else:
            return JSONResponse(status_code=404,content={'code':404,"msg":"Router not found"})

        new_material = TransportCommunicationMaterial(
            # id = new_guid(),
            code=body['code'],
            tool_name=body['tool_name'],
            unit=body['unit'],
            transport_mode=body['transport_mode'],
            material_name=body['material_name'],
            material_type=body['material_type'],
            admin_div=body['admin_div'],
            address=body['address'],
            district=body['district'],
            longitude=body['longitude'],
            latitude=body['latitude'],
            person_in_charge=body['person_in_charge'],
            contact=body['contact'],
            remark=body['remark'],
            resource_type=resource_type,
            create_by = user_id
        )

        db.add(new_material)
        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("/{resource_type}/update/{id}")
async def update_pattern(
    resource_type:str,
    id :str ,
    user_id=Depends(valid_access_token),
    body=Depends(remove_xss_json),
    db: Session = Depends(get_db)
):
    try:
        if resource_type=='comm':
            resource_type = 'COMM'
        elif resource_type=='transport':
            resource_type = 'TRANSPORT'
        else:
            return JSONResponse(status_code=404,content={'code':404,"msg":"Router not found"})
        info = resource_material_id_get_material_info(db,id,resource_type)
        if not info:
            return JSONResponse(status_code=404,content={"code":404,"msg":"resource not found"})
        # info.room_name = body['room_name']
        info.code = body['code']
        info.tool_name = body['tool_name']
        info.unit = body['unit']
        info.transport_mode = body['transport_mode']
        info.material_name = body['material_name']
        info.material_type = body['material_type']
        info.admin_div = body['admin_div']
        info.address = body['address']
        info.district = body['district']
        info.longitude = body['longitude']
        info.latitude = body['latitude']
        info.person_in_charge = body['person_in_charge']
        info.contact = body['contact']
        info.remark = body['remark']
        info.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("/{resource_type}/info/{id}")
async def get_pattern_info(
    resource_type:str,
    id: str,
    user_id=Depends(valid_access_token),
    db: Session = Depends(get_db)
):
    try:
        if resource_type=='comm':
            resource_type = 'COMM'
        elif resource_type=='transport':
            resource_type = 'TRANSPORT'
        else:
            return JSONResponse(status_code=404,content={'code':404,"msg":"Router not found"})
        info = resource_material_id_get_material_info(db,id,resource_type)
        if not info:
            return JSONResponse(status_code=404,content={"code":404,"msg":"warehouse room not found"})
        info = get_model_dict(info)
        return {"code": 200, "msg": "获取成功", "data": info}
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.get("/{resource_type}/list")
async def get_pattern_list(
    resource_type:str,
    user_id=Depends(valid_access_token),
    code: str = Query(None, description='名称'),
    tool_name: str = Query(None, description='名称'),
    unit: str = Query(None, description='名称'),
    transport_mode: 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(TransportCommunicationMaterial)
        query = query.filter_by(del_flag='0',resource_type=resource_type)
        if code:
            query = query.filter(TransportCommunicationMaterial.code.like(f'%{code}%'))
        if tool_name:
            query = query.filter(TransportCommunicationMaterial.tool_name.like(f'%{tool_name}%'))
        if unit:
            query = query.filter(TransportCommunicationMaterial.unit.like(f'%{unit}%'))
        if transport_mode:
            query = query.filter(TransportCommunicationMaterial.transport_mode.like(f'%{transport_mode}%'))

        total_items = query.count()
        # 排序
        query = query.order_by(TransportCommunicationMaterial.create_time.desc())
        # 执行分页查询
        lists = query.offset((page - 1) * pageSize).limit(pageSize).all()
        data = []
        for info in lists:
            data.append(get_model_dict(info))
        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.delete("/{resource_type}/delete/{id}")
async def delete_pattern(
    resource_type:str,
    id :str ,
    user_id=Depends(valid_access_token),
    body=Depends(remove_xss_json),
    db: Session = Depends(get_db)
):
    try:
        if resource_type=='comm':
            resource_type = 'COMM'
        elif resource_type=='transport':
            resource_type = 'TRANSPORT'
        else:
            return JSONResponse(status_code=404,content={'code':404,"msg":"Router not found"})
        info = resource_material_id_get_material_info(db,id,resource_type)
        if not info:
            return JSONResponse(status_code=404,content={"code":404,"msg":"resource not found"})
        # info.room_name = body['room_name']
        info.del_flag = '2'
        info.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)}")

import pandas as pd
@router.post("/{resource_type}/import_data")
async def import_data(
    resource_type:str,
    body = Depends(remove_xss_json),
    user_id=Depends(valid_access_token),
    db: Session = Depends(get_db)):
    if resource_type == 'comm':
        resource_type = 'COMM'
    elif resource_type == 'transport':
        resource_type = 'TRANSPORT'
    else:
        return JSONResponse(status_code=404, content={'code': 404, "msg": "Router not found"})
    # 获取表结构
    import os,openpyxl
    schema_name = "mmyjhd"
    table_name = "transport_communication_material"
    columns = [{"column_name":"code","column_comment":"编号"},
               {"column_name":"tool_name","column_comment":"运输工具名称"},
               {"column_name":"unit","column_comment":"所属单位"},
               {"column_name":"transport_mode","column_comment":"运输方式"},
               {"column_name":"material_name","column_comment":"物资名称"},
               {"column_name":"material_type","column_comment":"物资类型"},
               {"column_name":"admin_div","column_comment":"行政区划"},
               {"column_name":"address","column_comment":"地址"},
               {"column_name":"district","column_comment":"区县"},
               {"column_name":"longitude","column_comment":"经度"},
               {"column_name":"latitude","column_comment":"纬度"},
               {"column_name":"person_in_charge","column_comment":"负责人"},
               {"column_name":"contact","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).fillna('')
        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:

        new_material_list = []
        for row in data:
            new_material = TransportCommunicationMaterial(
                code=row['code'],
                tool_name=row['tool_name'],
                unit=row['unit'],
                transport_mode=row['transport_mode'],
                material_name=row['material_name'],
                material_type=row['material_type'],
                admin_div=row['admin_div'],
                address=row['address'],
                district=row['district'],
                longitude=row['longitude'],
                latitude=row['latitude'],
                person_in_charge=row['person_in_charge'],
                contact=row['contact'],
                remark=row['remark'],
                resource_type=resource_type,
                create_by=user_id
            )
            new_material_list.append(new_material)
        db.add_all(new_material_list)
        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.get("/{resource_type}/export")
async def download_file(
resource_type:str,
        code: str = Query(None, description='名称'),
        tool_name: str = Query(None, description='名称'),
        unit: str = Query(None, description='名称'),
        transport_mode: str = Query(None, description='名称'),
    db: Session = Depends(get_db),
    body = Depends(remove_xss_json),
    user_id = Depends(valid_access_token)):
    """
    根据提供的文件名下载文件。
    :param filename: 要下载的文件的名称。
    """
    try:
        if resource_type == 'comm':
            resource_type = 'COMM'
            file_name='通讯资源'
        elif resource_type == 'transport':
            resource_type = 'TRANSPORT'
            file_name='运输物资'
        else:
            return JSONResponse(status_code=404, content={'code': 404, "msg": "Router not found"})
        query = db.query(TransportCommunicationMaterial)
        query = query.filter_by(del_flag='0', resource_type=resource_type)
        if code:
            query = query.filter(TransportCommunicationMaterial.code.like(f'%{code}%'))
        if tool_name:
            query = query.filter(TransportCommunicationMaterial.tool_name.like(f'%{tool_name}%'))
        if unit:
            query = query.filter(TransportCommunicationMaterial.unit.like(f'%{unit}%'))
        if transport_mode:
            query = query.filter(TransportCommunicationMaterial.transport_mode.like(f'%{transport_mode}%'))

        # 排序
        query = query.order_by(TransportCommunicationMaterial.create_time.desc())
        # 执行分页查询
        lists = query.all()
        data_list = []
        for info in lists:
            data_list.append({"编号": info.code,
                              "运输工具名称": info.tool_name,
                              "所属单位": info.unit,
                              "运输方式": info.transport_mode,
                              "物资名称": info.material_name,
                              "物资类型": info.material_type,
                              "行政区划": info.admin_div,
                              "地址": info.address,
                              "区县": info.district,
                              "经度": info.longitude,
                              "纬度": info.latitude,
                              "负责人": info.person_in_charge,
                              "联系方式": info.contact,
                              "备注": info.remark})


        # 构造文件的完整路径

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

        # 将 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'{file_name}{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'
        }


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


    except HTTPException as e:
        raise e
    except Exception as e:
        # 处理其他异常情况
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))