python-fastapi-mm-zhcs-yj-api/routers/api/resourceProvison/emergency/__init__.py

# from fastapi import APIRouter, HTTPException, Depends, Body,Query
from fastapi import APIRouter, Request, Depends, HTTPException, Query, BackgroundTasks
from fastapi.responses import JSONResponse
import os
# from sqlalchemy.orm import Session
from sqlalchemy.orm import Session, joinedload
import xlrd
from database import get_db
from models import *
from typing import List, Optional
from pydantic import BaseModel,Extra, Field
import uuid
from common.security import valid_access_token
from pydantic import BaseModel
from exceptions import AppException, HmacException
from common.security import valid_access_token
import traceback
from utils import *
from sqlalchemy import create_engine, Column, Integer, String, Boolean, MetaData, Table, \
    inspect, exists,or_,text,insert,asc,desc

from . import rescue_units

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

router = APIRouter()

router.include_router(rescue_units.router, prefix="/rescue_units", tags=["救援队伍"])

# 水利工程管理
# Pydantic 模型

class WaterResourceProjectSchema(BaseModel):
    id: int = None
    jsdwzjlx: str = None
    lxfs: str = None
    sjtgbmtyshxydm: str = None
    jdgljgbm: str = None
    cd_time: str = None
    sjtgbmmc: str = None
    ggmj: str = None
    sjtgbmxzqhdm: str = None
    jsdwzjhm: str = None
    xzqhdm: str = None
    cd_operation: str = None
    zdmj: str = None
    d_bmmc: str = None
    etl_time: str = None
    jssj: str = None
    jsdwmc: str = None
    slsshslgcmc: str = None
    cd_batch: str = None
    slsshslgcdd: str = None
    jdgljg: str = None
    jingdu: str = None
    weidu: str = None
    is_delete: int = 0

class WaterResourceProjectListSchema(BaseModel):
    projects: List[WaterResourceProjectSchema] = []

    class Config:
        orm_mode = True
#插入数据
@router.post("/projects/")
def create_projects(project_list_data: WaterResourceProjectListSchema, db: Session = Depends(get_db)):
    projects = project_list_data.projects
    if not projects:  # 确保列表不为空
        raise HTTPException(status_code=400, detail="项目列表不能为空")
    try:
        new_projects = []  # 创建一个空列表来存储新对象
        for project_data in projects:
            # new_project = WaterResourceProject(**project_data.dict(exclude_none=True))
            # db.add(new_project)
            # new_projects.append(new_project)

            project_data = project_data.dict(exclude_none=True)
            n_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            project_data["cd_time"] = n_time
            project_data["etl_time"] = n_time
            project_data["cd_operation"] = 'I'
            project_data["cd_batch"] = ''

            new_project = WaterResourceProject(**project_data)
            db.add(new_project)
            new_projects.append(new_project)

        # new_units = []
        # for unit_data in units:
        #     unit_data = unit_data.dict(exclude_none=True)
        #     n_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        #     unit_data["add_time"] = n_time
        #
        #     new_unit = Unit(**unit_data)
        #     db.add(new_unit)
        #     new_units.append(new_unit)


        db.commit()
        project_ids = [project.id for project in new_projects]  # 获取所有新对象的ID
        return {"code": 200, "msg": "创建成功", "project_ids": project_ids}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))

#删除
@router.delete("/projects/{project_id}/")
def delete_project(project_id: int, db: Session = Depends(get_db), user_id=Depends(valid_access_token)):
    project = db.query(WaterResourceProject).get(project_id)
    if not project:
        raise HTTPException(status_code=404, detail="项目不存在")
    try:
        # 更新 is_delete 字段为 2，而不是删除记录
        project.is_delete = 2
        db.commit()
        return {"code": 200, "msg": "删除成功"}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))



# class ProjectListQueryParams(BaseModel):
#     page: int = Field(default=1, gt=0)
#     page_size: int = Field(default=10, gt=0)
#查询列表
@router.get("/projects/")
def get_projects(page: int = Query(default=1, gt=0),
                 pageSize: int = Query(default=10, gt=0),
                 db: Session = Depends(get_db), user_id=Depends(valid_access_token)):
    data_query = db.query(WaterResourceProject).filter(WaterResourceProject.is_delete == 0)
    data_query = data_query.order_by(WaterResourceProject.cd_time.desc())
    # 计算总数
    total_count = data_query.count()

    # 分页查询
    offset = (page - 1) * pageSize
    projects = data_query.offset(offset).limit(pageSize).all()

    # 构造结果
    result_items = [project.to_dict() for project in projects]

    result = {
        "code": 200,
        'msg': '查询成功',
        'total': total_count,
        'totalPages': (total_count + pageSize - 1) // pageSize,
        'page': page,
        'pageSize': pageSize,
        'data': result_items
    }

    return result

#查询详情
@router.get("/projects/{project_id}/")
def get_project_by_id(project_id: int, db: Session = Depends(get_db), user_id=Depends(valid_access_token)):
    # 添加条件过滤 is_delete 为 0 的项目
    project = db.query(WaterResourceProject).filter(WaterResourceProject.is_delete == 0, WaterResourceProject.id == project_id).first()
    if not project:
        raise HTTPException(status_code=404, detail="项目不存在或已被标记为删除")
    return {"code": 200, "msg": "查询成功", "project": project.to_dict()}

#修改
@router.put("/projects/{project_id}/")
def update_project(project_id: int, update_data: WaterResourceProjectSchema, db: Session = Depends(get_db), user_id=Depends(valid_access_token)):
    # 创建一个新的查询对象，不包含过滤条件
    project = db.query(WaterResourceProject).get(project_id)
    if project is None or project.is_delete == 1:
        raise HTTPException(status_code=404, detail="项目不存在或已被标记为删除")
    try:
        # 只更新传入的非空字段
        for key, value in update_data.dict(exclude_none=True).items():
            setattr(project, key, value)
        db.commit()
        db.refresh(project)
        return {"code": 200, "msg": "更新成功", "project": project.to_dict()}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))






#救援人员接口

class RescuePersonnelSchema(BaseModel):
    id: int = None
    name: str = None
    contact_number: str = None
    gender: str = None
    current_address: str = None
    position: str = None
    unit_id: int = None
    unit_name: str = None
    is_delete: int = 0
    class Config:
        orm_mode = True

class RescuePersonnelListSchema(BaseModel):
    personnel_list: List[RescuePersonnelSchema] = []

    class Config:
        orm_mode = True
#创建
@router.post("/rescue_personnel/")
def create_rescue_personnel(personnel_list_data: RescuePersonnelListSchema, db: Session = Depends(get_db)):
    personnel_list = personnel_list_data.personnel_list
    if not personnel_list:  # 确保列表不为空
        raise HTTPException(status_code=400, detail="人员列表不能为空")
    try:
        new_personnel_objects = []  # 创建一个空列表来存储新对象
        for personnel_data in personnel_list:
            personnel_data = personnel_data.dict(exclude_none=True)
            n_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

            personnel_data["created_time"] = n_time

            new_personnel = RescuePersonnel(**personnel_data)
            db.add(new_personnel)
            new_personnel_objects.append(new_personnel)
        db.commit()
        personnel_ids = [personnel.id for personnel in new_personnel_objects]  # 获取所有新对象的ID
        return {"code": 200, "msg": "创建成功", "personnel_ids": personnel_ids}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))

#删除
@router.delete("/rescue_personnel/{personnel_id}/")
def delete_rescue_personnel(personnel_id: int, db: Session = Depends(get_db)):
    personnel = db.query(RescuePersonnel).get(personnel_id)
    if not personnel:
        raise HTTPException(status_code=404, detail="救灾人员不存在")
    personnel.is_delete = 1
    db.commit()
    return {"code": 200, "msg": "删除成功"}


# 查询列表
@router.get("/rescue_personnel/")
def get_rescue_personnel(
                    page: int = Query(default=1, gt=0),
                 pageSize: int = Query(default=10, gt=0), db: Session = Depends(get_db)):
    # 应用过滤条件，仅查询未被删除的人员
    data_query = db.query(RescuePersonnel).filter(RescuePersonnel.is_delete == 0)
    data_query = data_query.order_by(RescuePersonnel.created_time.desc())
    # 计算总数
    total_count = data_query.count()

    # 分页查询
    offset = (page - 1) * pageSize
    personnels = data_query.offset(offset).limit(pageSize).all()

    # 构造结果
    # result_items = []
    # for personnel in personnels:
    #     personnel_dict = personnel.to_dict()
    #     # 通过 unit_id 获取单位名称
    #     if personnel.unit_id:  # 确保 unit_id 不为 None
    #         unit = db.query(Unit).filter(Unit.id == personnel.unit_id).first()
    #         if unit:
    #             personnel_dict['unit_name'] = unit.name
    #         else:
    #             personnel_dict['unit_name'] = None  # 或者其他默认值
    #     else:
    #         personnel_dict['unit_name'] = None  # 或者其他默认值
    #     result_items.append(personnel_dict)
    result_items = [personnel.to_dict() for personnel in personnels]

    result = {
        "code": 200,
        'msg': '查询成功',
        'total': total_count,
        'totalPages': (total_count + pageSize - 1) // pageSize,
        'page': page,
        'pageSize': pageSize,
        'data': result_items
    }
    return result


#查询详情
@router.get("/rescue_personnel/{personnel_id}/")
def get_rescue_personnel_by_id(personnel_id: int, db: Session = Depends(get_db)):
    # unit = db.query(Unit).filter_by(id=unit_id, is_delete=0).first()
    personnel = db.query(RescuePersonnel).filter_by(id = personnel_id, is_delete = 0).first()

    if not personnel:
        raise HTTPException(status_code=404, detail="救灾人员不存在或已被标记为删除")
    return {"code": 200, "msg": "查询成功", "personnel": personnel.to_dict()}

# @router.put("/rescue_units/{unit_id}/")
# def update_unit(unit_id: int, update_data: UnitSchema, db: Session = Depends(get_db),
#                 user_id=Depends(valid_access_token)):
#     # 根据id和is_delete字段获取单位
#     unit = db.query(Unit).filter_by(id=unit_id, is_delete=0).first()
#     if not unit:
#         raise HTTPException(status_code=404, detail="单位不存在或已被标记为删除")
#
#     try:
#         # 更新非空字段，排除id字段
#         for key, value in update_data.dict(exclude_none=True).items():
#             # 确保不更新id字段
#             if key != 'id':
#                 setattr(unit, key, value)
#         db.commit()
#         db.refresh(unit)
#         return {"code": 200, "msg": "更新成功", "unit": unit.to_dict()}
#     except Exception as e:
#         db.rollback()
#         raise HTTPException(status_code=400, detail=str(e))




#修改
@router.put("/rescue_personnel/{personnel_id}/")
def update_rescue_personnel(personnel_id: int, update_data: RescuePersonnelSchema, db: Session = Depends(get_db)):
    personnel = db.query(RescuePersonnel).filter_by(id=personnel_id, is_delete=0).first()
    if not personnel:
        raise HTTPException(status_code=404, detail="救灾人员不存在或已被标记为删除")
    try:
        # 更新非空字段，排除id字段
        for key, value in update_data.dict(exclude_none=True).items():
            if key != 'id':  # 确保不更新id字段
                setattr(personnel, key, value)
        personnel.modified_time = datetime.utcnow()  # 更新修改时间
        db.commit()
        db.refresh(personnel)
        return {"code": 200, "msg": "更新成功", "personnel": personnel.to_dict()}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))







class RescueStationSchema(BaseModel):
    id: int = None
    fwdx: str = None
    data_id: str = None
    zj: str = None
    lng: str = None
    cd_time: str = None
    fwdmc: str = None
    fwnr: str = None
    add_time: str = None
    cd_operation: str = None
    fwdlx: str = None
    lxdh: str = None
    kfsj: str = None
    lat: str = None
    fwdjj: str = None
    lxr: str = None
    fid: str = None
    fwdzt: str = None
    fwdaddr: str = None,
    ssqx:str = None
    is_delete: int = 0

    class Config:
        orm_mode = True

class RescueStationListSchema(BaseModel):
    stations: List[RescueStationSchema] = Field(default_factory=list)

    class Config:
        orm_mode = True


#救助站管理
#创建
@router.post("/rescue_stations/")
def create_rescue_stations(rescue_station_list_data: RescueStationListSchema, db: Session = Depends(get_db)):
    stations = rescue_station_list_data.stations
    if not stations:  # 确保列表不为空
        raise HTTPException(status_code=400, detail="救助站列表不能为空")
    try:
        new_stations = []  # 创建一个空列表来存储新对象
        for station_data in stations:
            station_data_dict = station_data.dict(exclude_none=True)
            n_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            station_data_dict["cd_time"] = n_time
            station_data_dict["add_time"] = n_time
            station_data_dict["data_id"] = uuid.uuid1()
            new_station = RescueStation(**station_data_dict)


            # new_station = RescueStation(**station_data.dict(exclude_none=True))
            db.add(new_station)
            new_stations.append(new_station)
        db.commit()
        station_ids = [station.id for station in new_stations]  # 获取所有新对象的ID
        return {"code": 200, "msg": "创建成功", "station_ids": station_ids}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))




@router.delete("/rescue_stations/{station_id}/")
def delete_rescue_station(station_id: int, db: Session = Depends(get_db)):
    station = db.query(RescueStation).filter_by(id=station_id, is_delete=0).first()
    if not station:
        raise HTTPException(status_code=404, detail="救助站不存在")
    try:
        # 更新 is_delete 字段为 1，而不是删除记录
        station.is_delete = 1
        db.commit()
        return {"code": 200, "msg": "删除成功"}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))

# class RescueStationListQueryParams(BaseModel):
#     page: int = Field(default=1, gt=0)
#     page_size: int = Field(default=10, gt=0)

@router.get("/rescue_stations/")
def get_rescue_stations(page: int = Query(default=1, gt=0),
                 pageSize: int = Query(default=10, gt=0), db: Session = Depends(get_db)):
    # 应用过滤条件，仅查询未被删除的救助站
    data_query = db.query(RescueStation).filter(RescueStation.is_delete == 0)

    data_query = data_query.order_by(RescueStation.add_time.desc())
    # 计算总数
    total_count = data_query.count()

    # 分页查询
    offset = (page - 1) * pageSize
    stations = data_query.offset(offset).limit(pageSize).all()



    # 构造结果
    result_items = [station.to_dict() for station in stations]

    result = {
        "code": 200,
        'msg': '查询成功',
        'total': total_count,
        'totalPages': (total_count + pageSize - 1) // pageSize,
        'page': page,
        'pageSize': pageSize,
        'data': result_items
    }

    return result

@router.get("/rescue_stations/{station_id}/")
def get_rescue_station_by_id(station_id: int, db: Session = Depends(get_db)):
    station = db.query(RescueStation).filter_by(id=station_id, is_delete=0).first()

    if not station:
        raise HTTPException(status_code=404, detail="救助站不存在或已被标记为删除")
    return {"code": 200, "msg": "查询成功", "station": station.to_dict()}

@router.put("/rescue_stations/{station_id}/")
def update_rescue_station(station_id: int, update_data: RescueStationSchema, db: Session = Depends(get_db)):
    station = db.query(RescueStation).filter_by(id=station_id, is_delete=0).first()
    if not station:
        raise HTTPException(status_code=404, detail="救助站不存在或已被标记为删除")

    try:
        # 更新非空字段，排除id字段
        for key, value in update_data.dict(exclude_none=True).items():
            # 确保不更新id字段
            if key != 'id':
                setattr(station, key, value)
        db.commit()
        db.refresh(station)
        return {"code": 200, "msg": "更新成功", "station": station.to_dict()}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))


#人防工程基本信息

class DefenseProjectSchema(BaseModel):
    id: Optional[int] = None
    data_id: Optional[str] = None
    gcmc: Optional[str] = None
    jsdw: Optional[str] = None
    whdw: Optional[str] = None
    rfzyjlgcs: Optional[str] = None
    jsdd: Optional[str] = None
    cd_operation: Optional[str] = None
    yjdxsmj: Optional[float] = None
    sjdxsmj: Optional[float] = None
    cd_time: Optional[datetime] = None
    add_time: Optional[datetime] = None
    jldw: Optional[str] = None
    jsdwdm: Optional[str] = None
    kgsj: Optional[datetime] = None
    stdw: Optional[str] = None
    cd_batch: Optional[str] = None
    rfsjdwdm: Optional[str] = None
    rfsjdw: Optional[str] = None
    ybrs: Optional[float] = None
    stdwdm: Optional[str] = None
    whdwdm: Optional[str] = None
    jldwdm: Optional[str] = None
    rfzjlgcs: Optional[str] = None
    gcid: Optional[str] = None
    extend2: Optional[str] = None
    data_area: Optional[str] = None
    extend1: Optional[str] = None
    jgsj: Optional[datetime] = None
    rffhsbdw: Optional[str] = None
    rffhsbdwdm: Optional[str] = None
    jingdu: Optional[str] = None
    weidu: Optional[str] = None
    is_delete: Optional[bool] = False

    class Config:
        orm_mode = True

class DefenseProjectListSchema(BaseModel):
    projects: List[DefenseProjectSchema] = Field(default_factory=list)

    class Config:
        orm_mode = True

# 创建
@router.post("/defense_projects/")
def create_defense_projects(defense_project_list_data: DefenseProjectListSchema, db: Session = Depends(get_db)):
    projects = defense_project_list_data.projects
    if not projects:  # 确保列表不为空
        raise HTTPException(status_code=400, detail="项目列表不能为空")
    try:
        new_projects = []  # 创建一个空列表来存储新对象
        for project_data in projects:
            # new_project = DefenseProject(**project_data.dict(exclude_none=True))
            # new_projects.append(new_project)

            project_data = project_data.dict(exclude_none=True)
            n_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            project_data["add_time"] = n_time
            project_data["cd_batch"] = ""
            project_data["cd_operation"] = "I"

            new_unit = DefenseProject(**project_data)
            db.add(new_unit)
            new_projects.append(new_unit)

        db.add_all(new_projects)
        db.commit()
        project_ids = [project.id for project in new_projects]  # 获取所有新对象的ID
        return {"code": 200, "msg": "创建成功", "project_ids": project_ids}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))

# 删除
@router.delete("/defense_projects/{project_id}/")
def delete_defense_project(project_id: int, db: Session = Depends(get_db)):
    # project = db.query(DefenseProject).get(project_id)
    project = db.query(DefenseProject).filter_by(id=project_id, is_delete=0).first()

    if not project:
        raise HTTPException(status_code=404, detail="项目不存在")
    try:
        # 更新 is_delete 字段为 True，而不是删除记录
        project.is_delete = True
        db.commit()
        return {"code": 200, "msg": "删除成功"}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))

# 查询列表
class DefenseProjectListQueryParams(BaseModel):
    page: int = Field(default=1, gt=0)
    page_size: int = Field(default=10, gt=0)

    # page: int = Query(1, gt=0, description='页码'),
    # page_size: int = Query(10, gt=0, description='pageSize'),
@router.get("/defense_projects/")
def get_defense_projects(page: int = Query(default=1, gt=0),
                 pageSize: int = Query(default=10, gt=0), db: Session = Depends(get_db)):
    # 应用过滤条件，仅查询未被删除的项目
    data_query = db.query(DefenseProject).filter(DefenseProject.is_delete == False)
    data_query = data_query.order_by(DefenseProject.add_time.desc())
    # 计算总数
    total_count = data_query.count()

    # 分页查询
    offset = (page - 1) * pageSize
    projects = data_query.offset(offset).limit(pageSize).all()

    # 构造结果
    # result_items = [project.to_dict() for project in projects]

    result = {
        "code": 200,
        'msg': '查询成功',
        'total': total_count,
        'totalPages': (total_count + pageSize - 1) // pageSize,
        'page': page,
        'pageSize': pageSize,
        'data': projects
    }

    return result

# 查询详情
@router.get("/defense_projects/{project_id}/")
def get_defense_project_by_id(project_id: int, db: Session = Depends(get_db)):
    # project = db.query(DefenseProject).filter_by(id=project_id, is_delete=0).first()
    project = db.query(DefenseProject).filter_by(id=project_id, is_delete=0).first()

    if not project:
        raise HTTPException(status_code=404, detail="项目不存在或已被标记为删除")
    return {"code": 200, "msg": "查询成功", "project": project.to_dict()}

# 修改
@router.put("/defense_projects/{project_id}/")
def update_defense_project(project_id: int, update_data: DefenseProjectSchema, db: Session = Depends(get_db)):
    project = db.query(DefenseProject).filter_by(id=project_id, is_delete=False).first()
    if not project:
        raise HTTPException(status_code=404, detail="项目不存在或已被标记为删除")

    try:
        # 更新非空字段，排除id字段
        for key, value in update_data.dict(exclude_none=True).items():
            # 确保不更新id字段
            if key != 'id':
                setattr(project, key, value)
        project.cd_time = datetime.utcnow()  # 更新修改时间
        db.commit()
        db.refresh(project)
        return {"code": 200, "msg": "更新成功", "project": project.to_dict()}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))


#避难场所接口
class ShelterSchema(BaseModel):
    id: Optional[int] = None
    data_id: Optional[str] = None
    admin_area: Optional[str] = None
    full_name: Optional[str] = None
    address: Optional[str] = None
    incident_type: Optional[str] = None
    shelter_type: Optional[str] = None
    total_area: Optional[float] = None
    indoor_area: Optional[float] = None
    capacity: Optional[float] = None
    supplies: Optional[str] = None
    facilities: Optional[str] = None
    is_delete: Optional[bool] = False

    class Config:
        orm_mode = True

class ShelterListSchema(BaseModel):
    shelters: List[ShelterSchema] = Field(default_factory=list)

    class Config:
        orm_mode = True


# 创建
@router.post("/shelters/")
def create_shelters(shelter_list_data: ShelterListSchema, db: Session = Depends(get_db)):
    shelters = shelter_list_data.shelters
    if not shelters:
        raise HTTPException(status_code=400, detail="避难场所列表不能为空")
    try:
        new_shelters = []
        for shelter_data in shelters:
            # new_shelter = Shelter(**shelter_data.dict(exclude_none=True))
            # new_shelters.append(new_shelter)
            shelter_data = shelter_data.dict(exclude_none=True)
            n_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            shelter_data["created_time"] = n_time
            shelter_data["data_id"] = uuid.uuid1()

            new_unit = Shelter(**shelter_data)
            db.add(new_unit)
            new_shelters.append(new_unit)
        db.add_all(new_shelters)
        db.commit()
        shelter_ids = [shelter.id for shelter in new_shelters]
        return {"code": 200, "msg": "创建成功", "shelter_ids": shelter_ids}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))

# 删除
@router.delete("/shelters/{shelter_id}/")
def delete_shelter(shelter_id: int, db: Session = Depends(get_db)):
    shelter = db.query(Shelter).filter_by(id=shelter_id, is_delete=0).first()
    if not shelter:
        raise HTTPException(status_code=404, detail="避难场所不存在")
    try:
        shelter.is_delete = True
        db.commit()
        return {"code": 200, "msg": "删除成功"}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))

# 查询列表
#

@router.get("/shelters/")
def get_shelters(page: int = Query(default=1, gt=0),
                 pageSize: int = Query(default=10, gt=0), db: Session = Depends(get_db)):
    data_query = db.query(Shelter).filter(Shelter.is_delete == False)
    data_query = data_query.order_by(Shelter.created_time.desc())

    total_count = data_query.count()
    offset = (page - 1) * pageSize
    shelters = data_query.offset(offset).limit(pageSize).all()
    result_items = [shelter.to_dict() for shelter in shelters]
    result = {
        "code": 200,
        'msg': '查询成功',
        'total': total_count,
        'totalPages': (total_count + pageSize - 1) // pageSize,
        'page': page,
        'pageSize': pageSize,
        'data': result_items
    }
    return result

# 查询详情
@router.get("/shelters/{shelter_id}/")
def get_shelter_by_id(shelter_id: int, db: Session = Depends(get_db)):
    shelter = db.query(Shelter).filter_by(id=shelter_id, is_delete=0).first()
    if not shelter:
        raise HTTPException(status_code=404, detail="避难场所不存在或已被标记为删除")
    return {"code": 200, "msg": "查询成功", "shelter": shelter.to_dict()}

# 修改
@router.put("/shelters/{shelter_id}/")
def update_shelter(shelter_id: int, update_data: ShelterSchema, db: Session = Depends(get_db)):
    shelter = db.query(Shelter).filter_by(id=shelter_id, is_delete=False).first()
    if not shelter:
        raise HTTPException(status_code=404, detail="避难场所不存在或已被标记为删除")
    try:
        for key, value in update_data.dict(exclude_none=True).items():
            if key != 'id':
                setattr(shelter, key, value)
        shelter.modified_time = datetime.utcnow()
        db.commit()
        db.refresh(shelter)
        return {"code": 200, "msg": "更新成功", "shelter": shelter.to_dict()}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))















def import_data(db, file_path, user_id, file_info):
    import_status = True
    print(file_path)
    try:
        book = xlrd.open_workbook(file_path)
        sheet = book.sheet_by_index(0)
    except:
        file_info.remark = file_info.remark + f'\n文件打开失败，请核实文件格式为xlsx/xlx>'
        file_info.error_num += 1
        import_status = False
    data = []
    for row in range(1, sheet.nrows):

        # 姓名
        jsdwzjlx = sheet.cell(row, 0).value
        lxfs = sheet.cell(row, 1).value
        sjtgbmtyshxydm = sheet.cell(row, 2).value
        jdgljgbm = sheet.cell(row, 3).value
        cd_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        sjtgbmmc = sheet.cell(row, 4).value
        ggmj = sheet.cell(row, 5).value
        sjtgbmxzqhdm = sheet.cell(row, 6).value
        jsdwzjhm = sheet.cell(row, 7).value
        xzqhdm = sheet.cell(row, 8).value
        cd_operation = "I"
        zdmj = sheet.cell(row, 9).value
        d_bmmc = sheet.cell(row, 10).value
        etl_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        jssj = sheet.cell(row, 11).value
        jsdwmc = sheet.cell(row, 12).value
        slsshslgcmc = sheet.cell(row, 13).value
        # cd_batch = Column(String(200), comment='数据批次号')
        slsshslgcdd = sheet.cell(row, 14).value
        jdgljg = sheet.cell(row, 15).value
        jingdu = sheet.cell(row, 16).value
        weidu = sheet.cell(row, 17).value

        new_water = WaterResourceProject(
            jsdwzjlx=jsdwzjlx,
            lxfs=lxfs,
            sjtgbmtyshxydm=sjtgbmtyshxydm,
            jdgljgbm=jdgljgbm,
            cd_time=cd_time,
            sjtgbmmc=sjtgbmmc,
            ggmj=ggmj,
            sjtgbmxzqhdm=sjtgbmxzqhdm,
            jsdwzjhm=jsdwzjhm,
            xzqhdm=xzqhdm,
            cd_operation=cd_operation,
            zdmj=zdmj,
            d_bmmc=d_bmmc,
            etl_time=etl_time,
            jssj=jssj,
            jsdwmc=jsdwmc,
            slsshslgcmc=slsshslgcmc,
            slsshslgcdd=slsshslgcdd,
            jdgljg=jdgljg,
            jingdu=jingdu,
            weidu=weidu
        )
        data.append(new_water)
        db.add(new_water)
        db.commit()
    db.commit()




@router.post('/createImport/projects')
async def create_contact(
        request: Request,
        background_tasks: BackgroundTasks,
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        # auth_user: AuthUser = Depends(find_auth_user),
        user_id=Depends(valid_access_token)
):
    try:
        # 提取请求数据
        filename = body['filename']
        file_name_desc = body['file_name_desc']
        if len(filename) == 0:
            raise Exception()

        # file_path = f'/data/upload/mergefile/uploads/{filename}'
        file_path = f'D:\\Desktop\\mm\\{filename}'
        # print(file_path)

        # 检查文件是否存在
        if not os.path.isfile(file_path):
            return JSONResponse(status_code=404, content={
                'errcode': 404,
                'errmsg': f'{filename}不存在'
            })

        new_file = ResourceImportFileStatus(
            file_uuid=filename,
            file_name=file_name_desc,
            status='1',
            remark='',
            user_id=user_id,
            resource_type = '水利'
        )
        db.add(new_file)
        db.commit()
        background_tasks.add_task(import_data, db, file_path, user_id, new_file)

        # db_czrz.log(db, auth_user, "系统管理", f"后台管理导入三防责任人管理人员信息成功", request.client.host)

        # 返回创建成功的响应
        return {
            "code": 200,
            "msg": "成功",
            "data": None
        }

    except AppException as e:
        return {
            "code": 500,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))







def rescue_units_import_data(db, file_path, user_id, file_info):
    import_status = True
    print(file_path)
    try:
        book = xlrd.open_workbook(file_path)
        sheet = book.sheet_by_index(0)
    except:
        file_info.remark = file_info.remark + f'\n文件打开失败，请核实文件格式为xlsx/xlx>'
        file_info.error_num += 1
        import_status = False
    data = []
    for row in range(1, sheet.nrows):

        # 姓名
        name = sheet.cell(row, 0).value
        category = sheet.cell(row, 1).value
        address = sheet.cell(row, 2).value
        equipment = sheet.cell(row, 3).value
        training = sheet.cell(row, 4).value
        responsible_person = sheet.cell(row, 5).value
        contact_number = sheet.cell(row, 6).value
        longitude = sheet.cell(row, 7).value
        latitude = sheet.cell(row, 8).value
        position = sheet.cell(row, 9).value
        team_size = sheet.cell(row, 10).value
        supervisor_unit = sheet.cell(row, 11).value
        add_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        # 创建 Unit 对象
        new_Unit = Unit(
            name=name,
            category=category,
            address=address,
            equipment=equipment,
            training=training,
            responsible_person=responsible_person,
            contact_number=contact_number,
            longitude=longitude,
            latitude=latitude,
            position=position,
            team_size=team_size,
            supervisor_unit=supervisor_unit,
            add_time=add_time
        )
        data.append(new_Unit)
        db.add(new_Unit)
        db.commit()
    db.commit()

@router.post('/createImport/rescue_units')
async def create_contact(
        request: Request,
        background_tasks: BackgroundTasks,
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        # auth_user: AuthUser = Depends(find_auth_user),
        user_id=Depends(valid_access_token)
):
    try:
        # 提取请求数据
        filename = body['filename']
        file_name_desc = body['file_name_desc']
        if len(filename) == 0:
            raise Exception()

        # file_path = f'/data/upload/mergefile/uploads/{filename}'
        file_path = f'D:\\Desktop\\mm\\{filename}'
        # print(file_path)

        # 检查文件是否存在
        if not os.path.isfile(file_path):
            return JSONResponse(status_code=404, content={
                'errcode': 404,
                'errmsg': f'{filename}不存在'
            })

        new_file = ResourceImportFileStatus(
            file_uuid=filename,
            file_name=file_name_desc,
            status='1',
            remark='',
            user_id=user_id,
            resource_type = '救援人员单位管理'
        )
        db.add(new_file)
        db.commit()
        background_tasks.add_task(rescue_units_import_data, db, file_path, user_id, new_file)

        # db_czrz.log(db, auth_user, "系统管理", f"后台管理导入三防责任人管理人员信息成功", request.client.host)

        # 返回创建成功的响应
        return {
            "code": 200,
            "msg": "成功",
            "data": None
        }

    except AppException as e:
        return {
            "code": 500,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))










def rescue_personnel_import_data(db, file_path, user_id, file_info):
    import_status = True
    print(file_path)
    try:
        book = xlrd.open_workbook(file_path)
        sheet = book.sheet_by_index(0)
    except:
        file_info.remark = file_info.remark + f'\n文件打开失败，请核实文件格式为xlsx/xlx>'
        file_info.error_num += 1
        import_status = False
    data = []
    for row in range(1, sheet.nrows):

        # 姓名
        name = sheet.cell(row, 0).value
        contact_number = sheet.cell(row, 1).value
        gender = sheet.cell(row, 2).value
        current_address = sheet.cell(row, 3).value
        position = sheet.cell(row, 4).value
        unit_id = sheet.cell(row, 5).value
        unit_name = sheet.cell(row, 6).value
        created_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        # 创建 Unit 对象
        new_RescuePersonnel = RescuePersonnel(
            name=name,
            contact_number=contact_number,
            gender=gender,
            current_address=current_address,
            position=position,
            unit_id=unit_id,
            unit_name=unit_name,
            created_time=created_time
        )
        data.append(new_RescuePersonnel)
        db.add(new_RescuePersonnel)
        db.commit()
    db.commit()



@router.post('/createImport/rescue_personnel')
async def create_contact(
        request: Request,
        background_tasks: BackgroundTasks,
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        # auth_user: AuthUser = Depends(find_auth_user),
        user_id=Depends(valid_access_token)
):
    try:
        # 提取请求数据
        filename = body['filename']
        file_name_desc = body['file_name_desc']
        if len(filename) == 0:
            raise Exception()

        # file_path = f'/data/upload/mergefile/uploads/{filename}'
        file_path = f'D:\\Desktop\\mm\\{filename}'
        # print(file_path)

        # 检查文件是否存在
        if not os.path.isfile(file_path):
            return JSONResponse(status_code=404, content={
                'errcode': 404,
                'errmsg': f'{filename}不存在'
            })

        new_file = ResourceImportFileStatus(
            file_uuid=filename,
            file_name=file_name_desc,
            status='1',
            remark='',
            user_id=user_id,
            resource_type = '救援人员单位'
        )
        db.add(new_file)
        db.commit()
        background_tasks.add_task(rescue_personnel_import_data, db, file_path, user_id, new_file)

        # db_czrz.log(db, auth_user, "系统管理", f"后台管理导入三防责任人管理人员信息成功", request.client.host)

        # 返回创建成功的响应
        return {
            "code": 200,
            "msg": "成功",
            "data": None
        }

    except AppException as e:
        return {
            "code": 500,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))








def rescue_stations_import_data(db, file_path, user_id, file_info):
    import_status = True
    print(file_path)
    try:
        book = xlrd.open_workbook(file_path)
        sheet = book.sheet_by_index(0)
    except:
        file_info.remark = file_info.remark + f'\n文件打开失败，请核实文件格式为xlsx/xlx>'
        file_info.error_num += 1
        import_status = False
    data = []
    for row in range(1, sheet.nrows):

        # 姓名
        data_id = uuid.uuid1()
        fwdx = sheet.cell(row, 0).value
        zj = sheet.cell(row, 1).value
        lng = sheet.cell(row, 2).value
        cd_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        fwdmc = sheet.cell(row, 3).value
        fwnr = sheet.cell(row, 4).value
        add_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        cd_operation = "I"
        fwdlx = sheet.cell(row, 5).value
        lxdh = sheet.cell(row, 6).value
        kfsj = sheet.cell(row, 7).value
        lat = sheet.cell(row, 8).value
        fwdjj = sheet.cell(row, 9).value
        lxr = sheet.cell(row, 10).value
        fid = sheet.cell(row, 11).value
        fwdzt = sheet.cell(row, 12).value
        fwdaddr = sheet.cell(row, 13).value
        ssqx = sheet.cell(row, 14).value


        new_RescueStation = RescueStation(
            data_id=data_id,
            fwdx=fwdx,
            zj=zj,
            lng=lng,
            cd_time=cd_time,
            fwdmc=fwdmc,
            fwnr=fwnr,
            add_time=add_time,
            cd_operation=cd_operation,
            fwdlx=fwdlx,
            lxdh=lxdh,
            kfsj=kfsj,
            lat=lat,
            fwdjj=fwdjj,
            lxr=lxr,
            fid=fid,
            fwdzt=fwdzt,
            fwdaddr=fwdaddr,
            ssqx=ssqx
        )
        data.append(new_RescueStation)
        db.add(new_RescueStation)
        db.commit()
    db.commit()

@router.post('/createImport/rescue_stations')
async def create_contact(
        request: Request,
        background_tasks: BackgroundTasks,
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        # auth_user: AuthUser = Depends(find_auth_user),
        user_id=Depends(valid_access_token)
):
    try:
        # 提取请求数据
        filename = body['filename']
        file_name_desc = body['file_name_desc']
        if len(filename) == 0:
            raise Exception()

        # file_path = f'/data/upload/mergefile/uploads/{filename}'
        file_path = f'D:\\Desktop\\mm\\{filename}'
        # print(file_path)

        # 检查文件是否存在
        if not os.path.isfile(file_path):
            return JSONResponse(status_code=404, content={
                'errcode': 404,
                'errmsg': f'{filename}不存在'
            })

        new_file = ResourceImportFileStatus(
            file_uuid=filename,
            file_name=file_name_desc,
            status='1',
            remark='',
            user_id=user_id,
            resource_type = '救助站'
        )
        db.add(new_file)
        db.commit()
        background_tasks.add_task(rescue_stations_import_data, db, file_path, user_id, new_file)

        # db_czrz.log(db, auth_user, "系统管理", f"后台管理导入三防责任人管理人员信息成功", request.client.host)

        # 返回创建成功的响应
        return {
            "code": 200,
            "msg": "成功",
            "data": None
        }

    except AppException as e:
        return {
            "code": 500,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))





#人防工程
def defense_projects_import_data(db, file_path, user_id, file_info):
    import_status = True
    try:
        book = xlrd.open_workbook(file_path)
        sheet = book.sheet_by_index(0)
    except:
        file_info.remark = file_info.remark + f'\n文件打开失败，请核实文件格式为xlsx/xlx>'
        file_info.error_num += 1
        import_status = False
    data = []
    for row in range(1, sheet.nrows):
        # print(row)
        # print(sheet.cell(row, 0).value)

        data_id = uuid.uuid1()
        # id = Column(BigInteger, primary_key=True, autoincrement=True, comment='数字自增ID')
        gcmc = sheet.cell(row, 0).value
        jsdw = sheet.cell(row, 1).value
        whdw = sheet.cell(row, 2).value
        rfzyjlgcs = sheet.cell(row, 3).value
        jsdd = sheet.cell(row, 4).value
        cd_operation = "I"

        yjdxsmj = sheet.cell(row, 5).value
        sjdxsmj = sheet.cell(row, 6).value
        cd_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        add_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        jldw = sheet.cell(row, 7).value
        jsdwdm = sheet.cell(row, 8).value
        kgsj = sheet.cell(row, 9).value
        stdw = sheet.cell(row, 10).value
        # cd_batch = Column(String(100), comment='批次号')  # 新增
        rfsjdwdm = sheet.cell(row, 11).value
        rfsjdw = sheet.cell(row, 12).value
        ybrs = sheet.cell(row, 13).value
        stdwdm = sheet.cell(row, 14).value
        whdwdm = sheet.cell(row, 15).value
        jldwdm = sheet.cell(row, 16).value
        rfzjlgcs = sheet.cell(row, 17).value
        # gcid = Column(String(50), comment='主键')
        extend2 = sheet.cell(row, 18).value
        data_area = sheet.cell(row, 19).value
        extend1 = sheet.cell(row, 20).value
        jgsj = sheet.cell(row, 21).value
        rffhsbdw = sheet.cell(row, 22).value
        rffhsbdwdm = sheet.cell(row, 23).value
        jingdu = sheet.cell(row, 24).value
        weidu = sheet.cell(row, 25).value

        new_DefenseProject = DefenseProject(
            data_id=data_id,
            gcmc=gcmc,
            jsdw=jsdw,
            whdw=whdw,
            rfzyjlgcs=rfzyjlgcs,
            jsdd=jsdd,
            cd_operation=cd_operation,
            yjdxsmj=yjdxsmj,
            sjdxsmj=sjdxsmj,
            cd_time=cd_time,
            add_time=add_time,
            jldw=jldw,
            jsdwdm=jsdwdm,
            kgsj=kgsj,
            stdw=stdw,
            rfsjdwdm=rfsjdwdm,
            rfsjdw=rfsjdw,
            ybrs=ybrs,
            stdwdm=stdwdm,
            whdwdm=whdwdm,
            jldwdm=jldwdm,
            rfzjlgcs=rfzjlgcs,
            extend2=extend2,
            data_area=data_area,
            extend1=extend1,
            jgsj=jgsj,
            rffhsbdw=rffhsbdw,
            rffhsbdwdm=rffhsbdwdm,
            jingdu=jingdu,
            weidu=weidu
        )
        data.append(new_DefenseProject)
        db.add(new_DefenseProject)
        db.commit()
    db.commit()



@router.post('/createImport/defense_projects')
async def create_contact(
        request: Request,
        background_tasks: BackgroundTasks,
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        # auth_user: AuthUser = Depends(find_auth_user),
        user_id=Depends(valid_access_token)
):
    try:
        # 提取请求数据
        filename = body['filename']
        file_name_desc = body['file_name_desc']
        if len(filename) == 0:
            raise Exception()

        # file_path = f'/data/upload/mergefile/uploads/{filename}'
        file_path = f'D:\\Desktop\\mm\\{filename}'
        # print(file_path)

        # 检查文件是否存在
        if not os.path.isfile(file_path):
            return JSONResponse(status_code=404, content={
                'errcode': 404,
                'errmsg': f'{filename}不存在'
            })

        new_file = ResourceImportFileStatus(
            file_uuid=filename,
            file_name=file_name_desc,
            status='1',
            remark='',
            user_id=user_id,
            resource_type = '人防工程'
        )
        db.add(new_file)
        db.commit()
        background_tasks.add_task(defense_projects_import_data, db, file_path, user_id, new_file)

        # db_czrz.log(db, auth_user, "系统管理", f"后台管理导入三防责任人管理人员信息成功", request.client.host)

        # 返回创建成功的响应
        return {
            "code": 200,
            "msg": "成功",
            "data": None
        }

    except AppException as e:
        return {
            "code": 500,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))







#人防工程
def shelters_import_data(db, file_path, user_id, file_info):
    import_status = True
    try:
        book = xlrd.open_workbook(file_path)
        sheet = book.sheet_by_index(0)
    except:
        file_info.remark = file_info.remark + f'\n文件打开失败，请核实文件格式为xlsx/xlx>'
        file_info.error_num += 1
        import_status = False
    data = []
    for row in range(1, sheet.nrows):




        data_id = uuid.uuid1()
        admin_area = sheet.cell(row, 0).value
        full_name = sheet.cell(row, 0).value
        address = sheet.cell(row, 0).value
        incident_type = sheet.cell(row, 0).value
        shelter_type = sheet.cell(row, 0).value
        total_area = sheet.cell(row, 0).value
        indoor_area = sheet.cell(row, 0).value
        capacity = sheet.cell(row, 0).value
        supplies = sheet.cell(row, 0).value
        facilities = sheet.cell(row, 0).value
        modified_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        created_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        new_Shelter = Shelter(
            data_id=data_id,
            admin_area=admin_area,
            full_name=full_name,
            address=address,
            incident_type=incident_type,
            shelter_type=shelter_type,
            total_area=total_area,
            indoor_area=indoor_area,
            capacity=capacity,
            supplies=supplies,
            facilities=facilities,
            modified_time=modified_time,
            created_time=created_time
        )
        data.append(new_Shelter)
        db.add(new_Shelter)
        db.commit()
    db.commit()



@router.post('/createImport/shelters')
async def create_contact(
        request: Request,
        background_tasks: BackgroundTasks,
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        # auth_user: AuthUser = Depends(find_auth_user),
        user_id=Depends(valid_access_token)
):
    try:
        # 提取请求数据
        filename = body['filename']
        file_name_desc = body['file_name_desc']
        if len(filename) == 0:
            raise Exception()

        # file_path = f'/data/upload/mergefile/uploads/{filename}'
        file_path = f'D:\\Desktop\\mm\\{filename}'
        # print(file_path)

        # 检查文件是否存在
        if not os.path.isfile(file_path):
            return JSONResponse(status_code=404, content={
                'errcode': 404,
                'errmsg': f'{filename}不存在'
            })

        new_file = ResourceImportFileStatus(
            file_uuid=filename,
            file_name=file_name_desc,
            status='1',
            remark='',
            user_id=user_id,
            resource_type = '避难场所'
        )
        db.add(new_file)
        db.commit()
        background_tasks.add_task(shelters_import_data, db, file_path, user_id, new_file)

        # db_czrz.log(db, auth_user, "系统管理", f"后台管理导入三防责任人管理人员信息成功", request.client.host)

        # 返回创建成功的响应
        return {
            "code": 200,
            "msg": "成功",
            "data": None
        }

    except AppException as e:
        return {
            "code": 500,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        db.rollback()
        raise HTTPException(status_code=500, detail=str(e))