python-fastapi-mm-zhcs-yj-api/routers/api/resourceProvison/emergency/rescue_units.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, 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})