python-fastapi-mm-zhcs-yj-api/routers/api/emergencyPlans/doc.py

from fastapi import APIRouter, Request, Depends, HTTPException, Query
from sqlalchemy.exc import IntegrityError
from fastapi.responses import HTMLResponse, FileResponse
from fastapi.responses import JSONResponse
from database import get_db
from sqlalchemy import text, exists, and_, or_, not_
from sqlalchemy.orm import Session
from models import *
import json
import os
import random
from sqlalchemy import create_engine, select
from typing import Optional
from utils.StripTagsHTMLParser import *
from common.db import db_event_management, db_user, db_area, db_emergency_plan
from common.security import valid_access_token
import traceback
from utils import *
from datetime import datetime, timedelta
import xlrd

router = APIRouter()

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

# 获取预案结构化文档
@router.post('/detail')
async def doc(
    request: Request, 
    db: Session = Depends(get_db), 
    body = Depends(remove_xss_json), 
    user_id = Depends(valid_access_token)
):
    plan_id = body['plan_id']
    items = []
    rows = db.query(EmergencyDoc).filter(and_(EmergencyDoc.pid == 0, EmergencyDoc.plan_id == plan_id)).all()
    for row in rows:
        items.append(get_item_data(db, row.id, 1))
    
    return {
        'code': 200,
        'msg': '查询成功',
        'data': items
    }

def get_item_data(db: Session, id: int, level: int):
    data = []
    row = db.query(EmergencyDoc).filter(EmergencyDoc.id == id).first()
    if row is not None:
        pid = row.id
        data = get_model_dict(row)
        data['level'] = level
        if data['value'] is None:
            data['value'] = ''

        data['items'] = []
        rows = db.query(EmergencyDoc).filter(EmergencyDoc.pid == pid).all()
        for row in rows:
            item = get_item_data(db, row.id, level + 1)
            data['items'].append(item)

        if len(data['items']) == 0 and level == 2:
            del data['items']

        data['id'] = data['plan_id']+str(data['id'])
        data['href'] = "#" + data['id']
        data['containerRef'] = "containerRef" + data['id']
        data['value'] = data['value'].replace("\r\n", "<br/>")
    return data


@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:
        plan_id = body['plan_id']
        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

            # 正文
            content = sheet.cell(i, 3).value

            if len(plan_name) < 1 and len(title1) < 1 and len(title2) < 1 and len(content) < 1 :
                break

            data.append({
                'plan_name': plan_name,
                'title1': title1,
                'title2': title2,
                '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(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(db, title1, content, docs, plan_id)

        return {
            'code': 200,
            'msg': '导入成功'
        }
    except Exception:
        traceback.print_exc()
        return {
            'code': 500,
            'msg': '导入发生异常'
        }
    
def add_doc(db: Session, title: str, content: str, docs: list, plan_id: str):
    main_entity = EmergencyDoc(title = title, value= content, pid = 0, plan_id = plan_id)
    db.add(main_entity)
    db.commit()
    db.refresh(main_entity)
    main_id = main_entity.id

    for n in docs:
        sub_entity = EmergencyDoc(title = n['title2'], value= n['content'], pid = main_id, plan_id = plan_id)
        db.add(sub_entity)
        db.commit()