# 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, get_db_share 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 common.auth_user import * from common.db import db_czrz from sqlalchemy import create_engine, Column, Integer, String, Boolean, MetaData, Table, \ inspect, exists,or_,text,insert,asc,desc # 目录在文档上传接口写死 UPLOAD_mergefile_PATH = '/data/upload/mergefile' router = APIRouter() # 救灾人员单位 # 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("/") 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("/{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("/list") 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("/detail/{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("/edit/{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)) # 导入 @router.post('/import') async def import_doc( request: Request, db: Session = Depends(get_db), body = Depends(remove_xss_json), user_id = Depends(valid_access_token) ): # print(body) try: filename = body['filename'] if len(filename) == 0: raise Exception() file = filename[0] url = file['url'] file_path = f"{UPLOAD_mergefile_PATH}/uploads/{url}" file_path = os.path.abspath(file_path) print(file_path) book = xlrd.open_workbook(file_path) sheet = book.sheet_by_index(0) data = [] ''' for i in range(9, sheet.nrows): # 预案名称 plan_name = sheet.cell(i, 0).value # 一级目录 title1 = sheet.cell(i, 1).value # 二级目录 title2 = sheet.cell(i, 2).value # 三级目录 title3 = sheet.cell(i, 3).value # 正文 content = sheet.cell(i, 4).value if len(plan_name) < 1 and len(title1) < 1 and len(title2) < 1 and len(title3) < 1 and len(content) < 1 : break data.append({ 'plan_name': plan_name, 'title1': title1, 'title2': title2, 'title3': title3, 'content': content, }) if len(data) > 0: db.query(EmergencyDoc).filter(EmergencyDoc.plan_id == plan_id).delete() db.commit() title1 = '' content = '' docs = [] for n in data: if n['title1'] != '': if len(docs) > 0: add_doc_1(db, title1, content, docs, plan_id) docs = [] title1 = n['title1'] content = n['content'] if n['title2'] != '': docs.append(n) continue docs.append(n) if len(docs) > 0: add_doc_1(db, title1, content, docs, plan_id) ''' return { 'code': 200, 'msg': '导入成功' } except Exception: traceback.print_exc() return { 'code': 500, 'msg': '导入发生异常' } @router.get("/export") async def get_list_info( request: Request, keyword: str = Query(None), auth_user: AuthUser = Depends(find_auth_user), db: Session = Depends(get_db_share), mmdb:Session = Depends(get_db) ): try: query = db.query(EmergencyExpertInfo) if keyword: query = query.filter(or_(EmergencyExpertInfo.name.like(f'%{keyword}%'), EmergencyExpertInfo.unit.like(f'%{keyword}%'), EmergencyExpertInfo.professional_title.like(f'%{keyword}%'), EmergencyExpertInfo.professional_group.like(f'%{keyword}%'), EmergencyExpertInfo.professional_field.like(f'%{keyword}%'))) data = query.all() data = [{'序号':info.id, '姓名':info.name, '所属区县':info.county, '专家类型':info.expert_type, '荣誉称号':info.honorary_title, '单位' :info.unit, '职位' :info.position, '职称' :info.professional_title, '擅长事故类型' :info.specialty, '救援经历' :info.rescue_experience, '出生日期' :info.birth_date, '工作时间' :info.work_start_date, '发证日期' :info.certificate_issue_date, '专业分组' :info.professional_group, '专业领域' :info.professional_field, '工作电话' :info.work_phone, '住宅电话' :info.home_phone, '移动电话' :info.mobile_phone, '电子邮箱' :info.email, '联系地址' :info.contact_address, '经度' :info.longitude, '纬度' :info.latitude} for info in data] # 返回结果 import pandas as pd from io import BytesIO # 将查询结果转换为 DataFrame df = pd.DataFrame(data) # 将 DataFrame 导出为 Excel 文件 output = BytesIO() with pd.ExcelWriter(output, engine='openpyxl') as writer: df.to_excel(writer, index=False) # 设置响应头 output.seek(0) from urllib.parse import quote encoded_filename = f'应急专家管理导出{datetime.now().strftime("%Y%m%d%H%mi%s")}.xlsx' encoded_filename = quote(encoded_filename, encoding='utf-8') headers = { 'Content-Disposition': f'attachment; filename*=UTF-8\'\'{encoded_filename}', 'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' } db_czrz.log(mmdb, auth_user, "应急专家管理", f"应急专家管理导出数据成功", request.client.host) # 返回文件流 return StreamingResponse(output, headers=headers) except Exception as e: traceback.print_exc() return JSONResponse(status_code=500, content={'msg': f"Internal server error: {str(e)}", 'code': 500})