# 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 router = APIRouter() # 水利工程管理 # 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)) # 救灾人员单位 # Pydantic 模型 class UnitSchema(BaseModel): id: int = None name: str = None category: str = None contact_number: str = None responsible_person: str = None contact_number: str = None team_size: int = None supervisor_unit: str = None unit_prop: str = None unit_level: str = None unit_favor: str = None supervisor_unit_phone: str = None supervisor_unit_contact: str = None responsible_person_phone: str = None area: str = None founding_time: str = None address: str = None longitude: str = None latitude: str = None position: str = None is_delete: int = 0 class UnitListSchema(BaseModel): units: List[UnitSchema] = Field(default_factory=list) class Config: orm_mode = True #创建 @router.post("/rescue_units/") def create_units(unit_list_data: UnitListSchema, db: Session = Depends(get_db)): units = unit_list_data.units if not units: # 确保列表不为空 raise HTTPException(status_code=400, detail="单位列表不能为空") try: new_units = [] # 创建一个空列表来存储新对象 for unit_data in units: unit_data = unit_data.dict(exclude_none=True) unit_data["add_time"] = datetime.now() print(unit_data) new_unit = RescueUnit(**unit_data) db.add(new_unit) db.commit() db.refresh(new_unit) new_units.append(new_unit) unit_ids = [unit.id for unit in new_units] # 获取所有新对象的ID return {"code": 200, "msg": "创建成功", "unit_ids": unit_ids} except Exception as e: traceback.print_exc() db.rollback() raise HTTPException(status_code=400, detail=str(e)) #删除 @router.delete("/delete/rescue_units/{unit_id}") def delete_unit(unit_id: int, db: Session = Depends(get_db), user_id=Depends(valid_access_token)): unit = db.query(RescueUnit).get(unit_id) if not unit: raise HTTPException(status_code=404, detail="单位不存在") try: # 更新 is_delete 字段为 1，而不是删除记录 unit.is_delete = 1 db.commit() return {"code": 200, "msg": "删除成功"} except Exception as e: traceback.print_exc() db.rollback() raise HTTPException(status_code=400, detail=str(e)) # class UnitListQueryParams(BaseModel): # page: int = Field(default=1, gt=0) # page_size: int = Field(default=10, gt=0) #查询列表 @router.get("/rescue_units/") def get_units(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(RescueUnit).filter(RescueUnit.is_delete == 0) data_query = data_query.order_by(RescueUnit.add_time.desc()) # 计算总数 total_count = data_query.count() # 分页查询 offset = (page - 1) * pageSize units = data_query.offset(offset).limit(pageSize).all() # 构造结果 result_items = [unit.to_dict() for unit in units] 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_units/{unit_id}/") def get_unit_by_id(unit_id: int, db: Session = Depends(get_db), user_id=Depends(valid_access_token)): # unit = db.query(Unit).filter(Unit.is_delete == 0).get(unit_id) unit = db.query(RescueUnit).filter_by(id=unit_id, is_delete=0).first() if not unit: raise HTTPException(status_code=404, detail="单位不存在或已被标记为删除") return {"code": 200, "msg": "查询成功", "unit": unit.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(RescueUnit).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: traceback.print_exc() 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))