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_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_(DutySchedule.duty_date == date,DutySchedule.del_flag!='2')
        # where = and_(where, DutyShift.dept_id == dept_id)
        subquery = db.query(
            DutySchedule.duty_date,
            DutySchedule.shift_type,
            func.max(DutySchedule.update_time).label("latest_create_time")
        ).filter(DutySchedule.del_flag != '2').group_by(
            DutySchedule.duty_date,
            DutySchedule.shift_type
        ).subquery()

        row = db.query(DutySchedule).filter(where)
        row = row.join(
            subquery,
            and_(
                DutySchedule.duty_date == subquery.c.duty_date,
                DutySchedule.shift_type == subquery.c.shift_type,
                DutySchedule.update_time == subquery.c.latest_create_time
            )
        ).all()

        shift_id = [info.id for info in row]


        if len(shift_id)>0:
            from .schedule import duty_id_get_user_id,user_id_get_info
            user_info = {"user1": [],
            "user2": "-",
            "user3": [],
            "user4": "-"}
            for d in shift_id:
                user_data = []
                user_list = duty_id_get_user_id(db, d)
                for user_info1 in user_list:
                    contact_info = user_id_get_info(db, user_info1.personnel_id)
                    if user_info1.position_id ==1:
                        user_info['user1'].append(contact_info.name)
                    if user_info1.position_id ==8:
                        user_info['user3'].append(contact_info.name)
            # user_info = get_model_dict(row)
            user_info['user1'] = '、'.join(user_info['user1'])
            user_info['user3'] = '、'.join(user_info['user3'])
            
            data['users'] = user_info

        # 提醒事项
        where = and_(DutyNotify.del_flag == '0', DutyNotify.notify_type == '1', DutyNotify.shift_id.in_(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.in_(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'] = ''
        
        # 目前不知道有什么用
        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:
        from utils.ry_system_util import user_id_get_user_info
        from .schedule import mobile_phone_get_dept_id,get_duty_unit_id_list
        from common.enc import mpfun
        now_user_info = user_id_get_user_info(db, user_id)
        if now_user_info:
            user_where =   (DutySchedule.duty_unit == mobile_phone_get_dept_id(db, mpfun.dec_data(now_user_info.phonenumber),
                                                                   get_duty_unit_id_list(db)))
        else:
            return {
            "code": 200,
            "msg": "查询成功",
            "data": []
        }
        # 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', DutySchedule.duty_date) == the_year,
                     func.extract('month', DutySchedule.duty_date) == the_month)
        where = and_(where, user_where,DutySchedule.del_flag != '2')
        rows = db.query(DutySchedule.duty_date).distinct().filter(where).all()
        data = []
        for row in rows:
            data.append({
                "date": row.duty_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.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