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

from fastapi import APIRouter, Request, Depends, HTTPException, Query,status
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'] = ''

        if level == 2:
            rows = db.query(EmergencyDoc).filter(EmergencyDoc.pid == pid).all()
            if len(rows) > 0:
                value_level3 = "<p>"
                for row in rows:
                    title3 = row.title
                    value3 = row.value

                    if title3 != "":
                        value_level3 = value_level3 + "<strong>" + title3 + "</strong></p><p>"
                    
                    if value3 != "":
                        value_level3 = value_level3 + format_value(value3) + "</p><p>"

                data['value'] = data['value'] + value_level3 + "</p>"

        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'] = format_value(data['value'])
    return data

def format_value(value: str) -> str:
    return value.replace("\r\n", "</p><p>").replace("\n", "</p><p>")


@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

            # 三级目录
            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': '导入发生异常'
        }
    
def add_doc_1(db: Session, title: str, content: str, docs: list, plan_id: str):
    print(title)
    print(docs)
    # 一级记录
    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
    print('main_id: ', main_id, 1)

    title2 = ''
    content = ''
    docs_2 = []
    for n in docs:
        if n['title2'] != '':
            if len(docs_2) > 0:
                add_doc_2(db, title2, content, docs_2, plan_id, main_id)

            docs_2 = []
            title2 = n['title2']
            content = n['content']
            docs_2.append(n)
            continue

        docs_2.append(n)

    if len(docs_2) > 0:
        add_doc_2(db, title2, content, docs_2, plan_id, main_id)

def add_doc_2(db: Session, title: str, content: str, docs: list, plan_id: str, pid: int):
    # 二级记录
    main_entity = EmergencyDoc(title = title, value= content, pid = pid, plan_id = plan_id)
    db.add(main_entity)
    db.commit()
    db.refresh(main_entity)
    main_id = main_entity.id

    print('main_id: ', main_id, 2)

    i = 0
    for n in docs:
        i = i + 1

        # 第一行是二级记录
        if i == 1:
            continue

        sub_entity = EmergencyDoc(title = n['title3'], value= n['content'], pid = main_id, plan_id = plan_id)
        db.add(sub_entity)
        db.commit()
        db.refresh(sub_entity)
        sub_id = sub_entity.id
        print('sub_id: ', sub_id, 3)