python-fastapi-mm-zhcs-yj-api/routers/api/dutyManagement/duty.py

from fastapi import APIRouter, Request, Depends, HTTPException, Query, BackgroundTasks
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 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
from common import YzyApi
from common.db import db_dict
from urllib.parse import quote
import base64
from config import settings
from extensions import logger
import os
from exceptions import *
import openpyxl

router = APIRouter()

@router.get("/duty_by_day")
async def duty_by_day(
    day: str,
    db: Session = Depends(get_db),
    user_id = Depends(valid_access_token)
):
    try:
        data = {}
        shift_id = 0
        shift_status = 0
        handover_time = None

        current_time  = datetime.now().time()
        date = datetime.strptime(day, "%Y-%m-%d")

        # 人员
        user_row = db_user.get_user_info(db, user_id)
        dept_id = user_row.dept_id

        # 默认值
        data['users'] = {
            "user1": "-", 
            "user2": "-", 
            "user3": "-", 
            "user4": "-"
        }
        
        # 当前用户的同一个部门的值班
        where = and_(DutyShift.shift_date == date, DutyShift.start_time < current_time)
        # where = and_(where, DutyShift.dept_id == dept_id)
        row = db.query(DutyShift).filter(where).first()
        if row is not None:
            shift_id = row.shift_id
            shift_status = row.shift_status
            handover_time = row.handover_time

            user_info = get_model_dict(row)
            user_info['user1'] = db_user.get_nick_name_by_id(db, user_info['leader_id'])
            user_info['user2'] = db_user.get_nick_name_by_id(db, user_info['primary_staff_id'])
            user_info['user3'] = db_user.get_nick_name_by_id(db, user_info['secondary_staff_id'])
            user_info['user4'] = db_user.get_nick_name_by_id(db, user_info['standby_staff_id'])
            
            data['users'] = user_info

        # 提醒事项
        where = and_(DutyNotify.del_flag == '0', DutyNotify.notify_type == '1', DutyNotify.shift_id == shift_id)
        rows = db.query(DutyNotify).filter(where).all()
        items = []
        for row in rows:
            items.append({
                "text": row.notify_content,
                "time": get_datetime_str(row.create_time)
            })
        data['remainds'] = items
        
        # 待办事项
        where = and_(DutyNotify.del_flag == '0', DutyNotify.notify_type == '2', DutyNotify.shift_id == shift_id)
        rows = db.query(DutyNotify).filter(where).all()
        items = []
        for row in rows:
            items.append({
                "text": row.notify_content,
                "time": get_datetime_str(row.create_time)
            })
        data['todos'] = items

        data['shift_id'] = shift_id
        data['shift_status'] = shift_status
        data['handover_time'] = get_datetime_str(handover_time)
        
        # 目前不知道有什么用
        data['days'] = []
        
        return {
            "code": 200,
            "msg": "查询成功",
            "data": data
        }

    except AppException as e:
        return {
            "code": e.code,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        raise HTTPException(status_code=500, detail=str(e))

# 暂时没用
@router.get("/my_calendar_by_month")
async def calendar_by_month(
    date: str = Query(None, description="月份"),
    db: Session = Depends(get_db),
    user_id = Depends(valid_access_token)
):
    the_year = datetime.now().year
    the_month = datetime.now().month
    if date is not None and date != '':
        arr = date.split("-")
        the_year = arr[0]
        the_month = arr[1]

    try:
        user_where = or_(DutyShift.leader_id == user_id, DutyShift.primary_staff_id == user_id, DutyShift.secondary_staff_id == user_id, DutyShift.standby_staff_id == user_id)
        where = and_(func.extract('year', DutyShift.shift_date) == the_year, func.extract('month', DutyShift.shift_date) == the_month)
        where = and_(where, user_where)
        rows = db.query(DutyShift.shift_date).distinct().filter(where).all()
        data = []
        for row in rows:
            data.append({
                "date": row.shift_date,
                "color": "a2d7f1"
            })
        return {
            "code": 200,
            "msg": "查询成功",
            "data": data
        }

    except AppException as e:
        return {
            "code": e.code,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        raise HTTPException(status_code=500, detail=str(e))
    

# 新增提醒或者待办
@router.post("/add_notify")
async def create_by_city_to_area(
    db: Session = Depends(get_db),
    body = Depends(remove_xss_json),
    user_id = Depends(valid_access_token)
):
    try:
        shift_id = body['shift_id']
        notify_type = body['notify_type']
        notify_content = body['notify_content']

        new_notify = DutyNotify(
            shift_id = shift_id,
            notify_type = notify_type,
            notify_content = notify_content,
            create_time = datetime.now(),
            del_flag = '0',
            recorded_by = user_id
        )
        db.add(new_notify)
        db.commit()
        db.refresh(new_notify)
        new_notify_id = new_notify.id

        return {
            "code": 200,
            "msg": "事项创建成功",
            "data": new_notify_id
        }

    except AppException as e:
        return {
            "code": e.code,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        raise HTTPException(status_code=500, detail=str(e))
    

# 交班
@router.post("/handover")
async def handover(
    db: Session = Depends(get_db),
    body = Depends(remove_xss_json),
    user_id = Depends(valid_access_token)
):
    try:
        shift_id = body['shift_id']

        where = and_(DutyShift.shift_id == shift_id)
        row = db.query(DutyShift).filter(where).first()
        if row is None:
            raise AppException("班次不存在")
        
        if row.shift_status == 1:
            raise AppException("班次已交班，不用重复操作")
        
        row.shift_status = 1
        row.handover_time = datetime.now()
        row.handover_user_id = user_id
        db.commit()

        return {
            "code": 200,
            "msg": "交班成功",
            "data": shift_id
        }

    except AppException as e:
        return {
            "code": e.code,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        raise HTTPException(status_code=500, detail=str(e))

# 值班表查询    
@router.get("/duty_book_by_area")
async def duty_book_by_year_and(
    year: str,
    area: str,
    db: Session = Depends(get_db)
):
    try:
        data = []

        where = and_(DutyBook.del_flag == '0', DutyBook.year == year, DutyBook.area_code == area)
        rows = db.query(DutyBook).filter(where).order_by(DutyBook.month.asc()).all()
        for row in rows:

            file_url = ""
            file_row = db.query(DutyFile).filter(and_(DutyFile.del_flag == '0', DutyFile.from_scenario == 'duty_book', DutyFile.foreign_key == row.id)).first()
            if file_row is not None:
                file_url = file_row.storage_file_name

            data.append({
                "id": row.id,
                "name": get_month_text(row.month)+"月份",
                "name2": get_month_text(row.month)+"月份排班表.xlsx",
                "time": row.create_time.strftime("%m-%d %H:%M"),
                "url": file_url
            })
        return {
            "code": 200,
            "msg": "交班成功",
            "data": data
        }

    except AppException as e:
        return {
            "code": e.code,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        raise HTTPException(status_code=500, detail=str(e))


# 上传排班表
@router.post("/upload_duty_book")
async def upload_duty_book(
    db: Session = Depends(get_db),
    body = Depends(remove_xss_json),
    user_id = Depends(valid_access_token)
):
    try:
        dept_id = 0
        user_row = db.query(SysUser).filter(SysUser.user_id == user_id).first()
        dept_id = user_row.dept_id

        area = body['area']
        year = body['year']
        month = body['month']
        
        # 先导入附件
        # 附件
        if 'files' not in body:
            raise AppException(500, "请上传值班表")
        
        # 从1号开始
        date = datetime.strptime(year + "-" + month + "-01", "%Y-%m-%d")

        users = []
        fileName = body['files'][0]
        users = import_duty_book_from_file(int(year), int(month), date, fileName)
        if len(users) == 0:
            raise AppException(500, "无法获取值班人员")
    
        # 标记之前的导入已删除
        old_book_id = 0
        where = and_(DutyBook.del_flag == '0', DutyBook.year == year, DutyBook.area_code == area)
        row = db.query(DutyBook).filter(where).order_by(DutyBook.month.asc()).first()
        if row is not None:
            old_book_id = row.id
            row.del_flag = '2'
            db.commit()
        
        # 新建导入
        new_book = DutyBook(
            year = year, month = month, area_code = area,
            recorded_by = user_id, create_time = datetime.now(), del_flag = '0'
        )
        db.add(new_book)
        db.commit()
        db.refresh(new_book)
        new_book_id = new_book.id

        for (date, item) in users.items():
            onduty_leader = item['onduty_leader']
            onduty_user = item['onduty_user']

            new_shift = DutyShift(
                shift_date = date,

                # 目前只是值班一整天
                start_time = "00:00",
                end_time = "23:59",

                leader_id = 0,
                primary_staff_id = 0,
                onduty_leader = onduty_leader,
                onduty_user = onduty_user,

                # Excel没用到这两个字段
                secondary_staff_id = -1,
                standby_staff_id = -1,

                duty_type = "综合值班",
                shift_status = 0,
                handover_user_id = 0,
                handover_time = None,
                dept_id = -1, # 这个字段没用
                area_code = area,
                del_flag = '0',
                book_id = new_book_id
            )
            db.add(new_shift)
        db.commit()

        # 标记之前的值班信息已删除
        where = and_(DutyShift.del_flag == '0', DutyShift.book_id == old_book_id)
        db.query(DutyShift).filter(where).update({"del_flag": '2'})
        db.commit()

        new_file = DutyFile(
            file_name=fileName["name"],
            storage_file_name=fileName["url"],
            file_path=f'/data/upload/mergefile/uploads/{fileName["url"]}',
            file_size=os.path.getsize(f'/data/upload/mergefile/uploads/{fileName["url"]}'),
            foreign_key=str(new_book_id),
            from_scenario="duty_book",
            update_time=datetime.now(),
            create_time=datetime.now(),
            create_by=user_id,
            create_dept=dept_id,
            del_flag='0',
            status=0,
        )
        db.add(new_file)
        db.commit()
        db.refresh(new_file)

        return {
            "code": 200,
            "msg": "上传成功",
            "data": new_file.id
        }

    except AppException as e:
        return {
            "code": e.code,
            "msg": e.msg
        }

    except Exception as e:
        traceback.print_exc()
        # 处理异常
        raise HTTPException(status_code=500, detail=str(e))
    
def import_duty_book_from_file(year: int, month: int, date: datetime, fileName: dict) -> dict:
    file_path = f'/data/upload/mergefile/uploads/{fileName["url"]}'
    wb = openpyxl.load_workbook(file_path)
    ws = wb.active

    rows = []
    for row in ws.iter_rows(values_only=True):
        rows.append(list(row))
        print(list(row))

    users = {}
    while date.month == month:
        day = datetime.strftime(date, "%m月%d日")
        for x in range(len(rows)):
            row = rows[x]
            for y in range(len(row)):
                cell = row[y]
                if cell == day:
                    try:
                        # 带班领导
                        onduty_leader = rows[x+1][y]
                        # 值班人员
                        onduty_user = rows[x+2][y]

                        if onduty_leader is None:
                            raise AppException(500, "无法获取"+day+"的带班领导")
                        
                        if onduty_user is None:
                            raise AppException(500, "无法获取"+day+"的值班人员")
                        
                        # date1 = date.strftime("%Y-%m-%d")
                        users[date] = {"day": day, "onduty_leader": onduty_leader, "onduty_user": onduty_user}
                    except Exception as e:
                        raise AppException(500, "无法获取"+day+"的值班人员")
                    break

        date = date + timedelta(days=1)

    return users