python-fastapi-mm-zhcs-yj-api/routers/api/riskManagement/task.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from fastapi import APIRouter, Request, Depends,Query, HTTPException, status,BackgroundTasks
from common.security import valid_access_token
from fastapi.responses import JSONResponse,StreamingResponse
from common.db import db_czrz
from common.auth_user import *
from sqlalchemy.orm import Session
from sqlalchemy import and_, or_
from pydantic import BaseModel
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
from database import get_db
from typing import List
from models import *
from utils import *
from utils.ry_system_util import *
from utils.riskManagement_uitl import *
import json
import traceback


router = APIRouter()

@router.get('/list')
async def get_inspection_task_list(
    business: str = Query(None, description='巡查业务'),
    cycle :str = Query(None, description='巡查周期'),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(10, gt=0, description='每页条目数量'),
    db: Session = Depends(get_db),
    user_id = Depends(valid_access_token)
):
    try:
        # 构建查询
        query = db.query(RiskManagementInspectionTask)
        query = query.filter(RiskManagementInspectionTask.del_flag != '2')
        # 应用查询条件
        if business:
            query = query.filter(RiskManagementInspectionTask.inspection_business == business)
        if cycle:
            query = query.filter(RiskManagementInspectionTask.inspection_cycle == cycle)

        # 计算总条目数
        total_items = query.count()

        # 排序

        query = query.order_by(RiskManagementInspectionTask.create_time.desc())
        # 执行分页查询
        InspectionTasks = query.offset((page - 1) * pageSize).limit(pageSize).all()

        # 将查询结果转换为列表形式的字典
        InspectionTasks_list = []
        for task in InspectionTasks:
            if task.task_status=='3':
                task_status = '3' #'已完结'
            else:
                if datetime.now()<task.start_time:
                    task_status = '0' #'未开始'
                elif task.start_time<=datetime.now()<=task.end_time:
                    task_status = '1' #'进行中'
                else:
                    task_status = '2' #'未完成'
            create_by = task.create_by
            create_by = db.query(SysUser).filter(SysUser.user_id==create_by).first()
            nick_name = None
            if create_by:
                nick_name=create_by.nick_name
            task_info = {
                "id": task.id,
                "task_number": task.task_number,
                "business": task.inspection_business,
                "task_time": '%s-%s'%(task.start_time.strftime('%Y/%m/%d'),task.end_time.strftime('%Y/%m/%d')),
                "cycle": task.inspection_cycle,
                "corn_query":task.corn_query,
                "inspection_range": task.inspection_range,
                "task_status": task_status,
                "create_by":nick_name,
                "create_time": task.create_time.strftime('%Y-%m-%d')
            }
            InspectionTasks_list.append(task_info)
        # 返回结果
        return {
            "code": 200,
            "msg": "成功",
            "data": InspectionTasks_list,
            "total": total_items,
            "page": page,
            "pageSize": pageSize,
            "totalPages": (total_items + pageSize - 1) // pageSize
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))


@router.get('/{id}')
async def get_inspection_task(
    id: str ,
    db: Session = Depends(get_db),
    user_id = Depends(valid_access_token)
):
    try:
        # 构建查询
        query = db.query(RiskManagementInspectionTask)
        query = query.filter(RiskManagementInspectionTask.del_flag != '2')
        # 应用查询条件
        if id:
            query = query.filter(RiskManagementInspectionTask.id == id)

        # 执行查询
        task = query.first()
        if not task:
            detail = "巡查任务不存在"
            raise HTTPException(status_code=404, detail="巡查任务不存在")
        # 将查询结果转换为列表形式的字典
        if task.task_status == '3':
            task_status = '3'  # '已完结'
        else:
            if datetime.now() < task.start_time:
                task_status = '0'  # '未开始'
            elif task.start_time <= datetime.now() <= task.end_time:
                task_status = '1'  # '进行中'
            else:
                task_status = '2'  # '未完成'
        create_by = task.create_by
        create_by = db.query(SysUser).filter(SysUser.user_id == create_by).first()
        nick_name = None
        if create_by:
            nick_name = create_by.nick_name
        inspection_task_result = {
                "id": task.id,
                "task_number": task.task_number,
                "business": task.inspection_business,
                "task_time": '%s-%s'%(task.start_time.strftime('%Y/%m/%d'),task.end_time.strftime('%Y/%m/%d')),
                "start_time":task.start_time.strftime('%Y-%m-%d'),
                "end_time":task.end_time.strftime('%Y-%m-%d'),
                "cycle": task.inspection_cycle,
                "inspection_range": task.inspection_range,
                "corn_query":task.corn_query,
                "task_status": task_status,
                "create_by":nick_name,
                "create_time": task.create_time.strftime('%Y-%m-%d')
            }

        # 返回结果
        return {
            "code": 200,
            "msg": "成功",
            "data": inspection_task_result
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        if str(e)=='':
            e = detail
        raise HTTPException(status_code=500, detail=str(e))




@router.post('/create')
async def create_inspection_task(
    background_tasks: BackgroundTasks,
    db: Session = Depends(get_db),
    body = Depends(remove_xss_json),
    user_id = Depends(valid_access_token)
):
    try:
        cycle = body['cycle']
        # 0每年、1每月、2每周、3每日、4一次
        corn_query = body['corn_query']
        if cycle=='0':
            corn=f'0 0 {corn_query} *'
        elif cycle=='1':
            corn=f'0 0 {corn_query} * *'
        elif cycle == '2':
            corn = f'0 0 * * {corn_query}'
        elif cycle == '3':
            corn = f'0 0 * * *'
        else:
            corn=''
        # 创建新的预案记录
        start_time = body['start_time']
        if start_time =='':
            start_time = body['end_time']
        end_time = body['end_time']
        if end_time =='':
            end_time= body['start_time']
        if start_time == '' and end_time=='':
            start_time = end_time = datetime.now().strftime("%Y-%m-%d 00:00:00")
        new_task = RiskManagementInspectionTask(
            inspection_business=body['business'],
            start_time = start_time,
            end_time = end_time,
            inspection_cycle = cycle,
            corn_expression = corn,
            corn_query= corn_query,
            inspection_range = body['inspection_range'],
            task_status = '-1',
            create_by = user_id
        )
        # 添加到数据库会话并提交
        db.add(new_task)
        db.commit()
        db.refresh(new_task)  # 可选，如果需要刷新实例状态
        new_task.task_number = f'YJXC{str(new_task.id).zfill(10)}'
        db.commit()
        # 返回创建成功的响应
        background_tasks.add_task(create_children_task,db,new_task,corn_query)
        return {
            "code": 200,
            "msg": "成功",
            "data": None
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))


@router.put('/update')
async def update_inspection_task(
    db: Session = Depends(get_db),
    body = Depends(remove_xss_json),
    user_id = Depends(valid_access_token)
):
    try:
        # 提取请求数据
        query = db.query(RiskManagementInspectionTask)
        query = query.filter(RiskManagementInspectionTask.id == body['id'])
        query = query.filter(RiskManagementInspectionTask.del_flag != '2')
        task = query.first()

        if not task:
            detail = "任务不存在"
            raise HTTPException(status_code=404, detail="任务不存在")

        # if 'cycle' in body:
        #     cycle = body['cycle']
        #     # 0每年、1每月、2每周、3每日、4一次
        #     corn_query = body['corn_query']
        #     if cycle == '0':
        #         corn = f'0 0 {corn_query} *'
        #     elif cycle == '1':
        #         corn = f'0 0 {corn_query} * *'
        #     elif cycle == '2':
        #         corn = f'0 0 * * {corn_query}'
        #     elif cycle == '3':
        #         corn = f'0 0 * * *'
        #     else:
        #         corn = ''
        #     task.inspection_cycle = cycle
        #     task.corn_expression = corn
        if 'business' in body:
            task.inspection_business = body['business']
        # if 'start_time' in body:
        #     task.start_time = body['start_time']
        # if 'end_time' in body:
        #     task.end_time = body['end_time']
        if 'inspection_range' in body:
            task.inspection_range = body['inspection_range']
        if 'task_status' in body:
            task.task_status = body['task_status']

        if user_id:
            task.update_by = user_id

        # 更新到数据库会话并提交
        db.commit()
        db.refresh(task)  # 可选，如果需要刷新实例状态

        # 返回创建成功的响应
        return {
            "code": 200,
            "msg": "成功",
            "data": None
        }
    except Exception as e:
        # 处理异常
        if str(e)=='':
            e = detail
        raise HTTPException(status_code=500, detail=str(e))

@router.delete('/delete')
async def delete_inspection_tasks(
        taskIds: list,
        db: Session = Depends(get_db),
    body = Depends(remove_xss_json),
    user_id = Depends(valid_access_token)
):
    try:
        # 提取请求数据
        query = db.query(RiskManagementInspectionTask)
        query = query.filter(RiskManagementInspectionTask.del_flag != '2')
        query = query.filter(RiskManagementInspectionTask.id.in_(taskIds))
        tasks = query.all()
        if not tasks:
            detail = "任务不存在"
            raise HTTPException(status_code=404, detail="任务不存在")
        for task in tasks:
            task.del_flag = '2'
            task.update_by=user_id
        # 更新到数据库会话并提交
        db.commit()

        # 返回创建成功的响应
        return {
            "code": 200,
            "msg": "删除成功",
            "data": None
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        if str(e) == '':
            e = detail
        raise HTTPException(status_code=500, detail=str(e))

@router.delete('/delete/{userId}')
async def delete_inspection_task(
        userId: str,
        db: Session = Depends(get_db),
    body = Depends(remove_xss_json),
    user_id = Depends(valid_access_token)
):
    try:
        # 提取请求数据
        query = db.query(RiskManagementInspectionTask)
        query = query.filter(RiskManagementInspectionTask.del_flag != '2')
        query = query.filter(RiskManagementInspectionTask.id==userId)
        task = query.first()
        if not task:
            detail = "巡查任务不存在"
            raise HTTPException(status_code=404, detail="巡查任务不存在")

        task.del_flag = '2'
        task.update_by = user_id
        # 更新到数据库会话并提交
        db.commit()
        db.refresh(task)  # 可选，如果需要刷新实例状态

        # 返回创建成功的响应
        return {
            "code": 200,
            "msg": "删除成功",
            "data": None
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        if str(e) == '':
            e = detail
        raise HTTPException(status_code=500, detail=str(e))

####小屏子任务查询

@router.get('/children/task/list')
async def get_inspection_task_list(
    search_keyword: str =Query(None, description='任务名称'),
    type: str =Query(None, description='类型'),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(10, gt=0, description='每页条目数量'),
    db: Session = Depends(get_db),
    user_id = Depends(valid_access_token)
):
    try:
        # 构建查询
        # 查询用户负责层级
        task_range = user_id_get_task_range(db,user_id)
        #查询未办结巡查任务列表
        Task_list = db.query(RiskManagementInspectionTask).filter(RiskManagementInspectionTask.del_flag!='2').filter(RiskManagementInspectionTask.inspection_range.in_(task_range)).filter(RiskManagementInspectionTask.task_status!='3').all()
        #获取所有巡查任务id
        task_ids = [i.id for i in Task_list]
        #用户所有负责的区域
        user_area_code_list = db.query(RiskManagementInspectionUser).filter(RiskManagementInspectionUser.del_flag!='2').filter(RiskManagementInspectionUser.user_id==user_id).all()
        user_area_codes = [i.area_code for i in user_area_code_list]
        #查询子任务
        query = db.query(RiskManagementInspectionTaskChildrenTask)
        query = query.filter(RiskManagementInspectionTaskChildrenTask.del_flag != '2')
        # 应用查询条件
        # 查询小于今天
        query = query.filter(RiskManagementInspectionTaskChildrenTask.tsak_time <=datetime.now())
        # 查询层级、未办结子任务
        query = query.filter(RiskManagementInspectionTaskChildrenTask.task_range.in_(task_range))
        query = query.filter(RiskManagementInspectionTaskChildrenTask.task_id.in_(task_ids))
        # 计算总条目数
        if type:
            query = query.filter(RiskManagementInspectionTaskChildrenTask.type==type)
        total_items = query.count()

        # 排序

        query = query.order_by(RiskManagementInspectionTaskChildrenTask.tsak_time.asc())
        # 执行分页查询
        InspectionTasks = query.offset((page - 1) * pageSize).limit(pageSize).all()

        # 将查询结果转换为列表形式的字典
        InspectionTasks_list = []
        for task in InspectionTasks:
            # 判断这个任务该user_id完成了没
            tasklog = db.query(RiskManagementInspectionTaskChildrenTaskLog).\
                filter(RiskManagementInspectionTaskChildrenTaskLog.del_flag!='2').\
                filter(RiskManagementInspectionTaskChildrenTaskLog.children_task_id==task.id).\
                filter(RiskManagementInspectionTaskChildrenTaskLog.area_code.in_(user_area_codes)).all()
            task_area_code_list = [i.area_code for i in tasklog]
            query = db.query(RiskManagementInspectionUser).filter(RiskManagementInspectionUser.del_flag!='2').filter(RiskManagementInspectionUser.user_id==user_id)
            if task.task_range == '0' and '440900000000' not in task_area_code_list:
                user_range_area_codes= ['440900000000']
            elif task.task_range == '1':
                query = query.filter(
                    and_(RiskManagementInspectionUser.area_code.like('%000000'), RiskManagementInspectionUser.area_code.notlike('%00000000'),~RiskManagementInspectionUser.area_code.in_(task_area_code_list)))
                user_range_area_codes= list({i.area_code for i in query.all()})
            elif task.task_range == '2':
                query = query.filter(and_(RiskManagementInspectionUser.area_code.like('%000'), RiskManagementInspectionUser.area_code.notlike('%000000'),~RiskManagementInspectionUser.area_code.in_(task_area_code_list)))
                user_range_area_codes= list({i.area_code for i in query.all()})
            elif task.task_range == '3':
                query = query.filter(RiskManagementInspectionUser.area_code.notlike('%000'),~RiskManagementInspectionUser.area_code.in_(task_area_code_list))
                user_range_area_codes= list({i.area_code for i in query.all()})
            else:
                user_range_area_codes= []

            for area_code in user_range_area_codes:
                task_info = {
                    "id": task.id,
                    "business": task.type,
                    "task_range": task.task_range,
                    "cycle": task.cycle,
                    "area_code":area_code,
                    "area": area_code_get_ancestors_names(db,area_code_get_area_info(db,area_code)),
                    "task_time": task.tsak_time.strftime('%Y-%m-%d'),
                    "create_time": task.create_time.strftime('%Y-%m-%d')
                }
                InspectionTasks_list.append(task_info)
        # 返回结果
        return {
            "code": 200,
            "msg": "成功",
            "data": InspectionTasks_list,
            "total": total_items,
            "page": page,
            "pageSize": pageSize,
            "totalPages": (total_items + pageSize - 1) // pageSize
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))

##日历
@router.get('/children/task/calendar/list')
async def get_inspection_task_list(
    year_1: int =Query(None, description='年份'),
    month_1: int =Query(None, description='月份'),
    # page: int = Query(1, gt=0, description='页码'),
    # pageSize: int = Query(10, gt=0, description='每页条目数量'),
    db: Session = Depends(get_db),
    user_id = Depends(valid_access_token)
):
    try:
        # 构建查询
        import datetime as datetime_1

        task_range = user_id_get_task_range(db,user_id)
        Task_list = db.query(RiskManagementInspectionTask).filter(RiskManagementInspectionTask.del_flag!='2').filter(RiskManagementInspectionTask.inspection_range.in_(task_range)).all() #.filter(RiskManagementInspectionTask.task_status!='3')
        task_ids = [i.id for i in Task_list]
        user_area_code_list = db.query(RiskManagementInspectionUser).filter(RiskManagementInspectionUser.del_flag!='2').filter(RiskManagementInspectionUser.user_id==user_id).all()
        user_area_codes = [i.area_code for i in user_area_code_list]
        query = db.query(RiskManagementInspectionTaskChildrenTask)
        query = query.filter(RiskManagementInspectionTaskChildrenTask.del_flag != '2')
        # 应用查询条件
        current_date = datetime.today()
        if year_1 is None:
            year_1 =current_date.year
        if month_1 is None:
            month_1 = current_date.month
        if month_1==12:
            query = query.filter(RiskManagementInspectionTaskChildrenTask.tsak_time < datetime_1.date(year_1+1, 1, 1))
        else:
            query = query.filter(RiskManagementInspectionTaskChildrenTask.tsak_time < datetime_1.date(year_1, month_1 + 1, 1))
        query = query.filter(RiskManagementInspectionTaskChildrenTask.tsak_time >= datetime_1.date(year_1, month_1, 1))
        # query = query.filter(RiskManagementInspectionTaskChildrenTask.tsak_time <=datetime.now())
        query = query.filter(RiskManagementInspectionTaskChildrenTask.task_range.in_(task_range))
        query = query.filter(RiskManagementInspectionTaskChildrenTask.task_id.in_(task_ids))
        # 计算总条目数
        total_items = query.count()
        print(total_items)
        result = []
        # 获取指定月份的第一天是周几
        # first_day = datetime.date(yaer_1, month_1, 1)
        # 计算该月的天数
        if month_1 == 12:
            number_of_days = (datetime_1.date(year_1+1, 1, 1) - datetime_1.timedelta(days=1)).day
        else:
            number_of_days = (datetime_1.date(year_1, month_1 + 1, 1) - datetime_1.timedelta(days=1)).day
        # 遍历该月的每一天
        for day in range(1, number_of_days + 1):
            # 创建日期对象
            date_obj = datetime_1.date(year_1, month_1, day)
            # 获取星期（0是周一，6是周日）
            week_day = date_obj.weekday()
            # 将星期转换为中文
            week_day_cn = "一二三四五六日"[week_day]
            data = {"day":day,"date":f"{year_1}-{month_1}-{day}","week":f'周{week_day_cn}',"status":0}
            if date_obj==datetime_1.date.today():
                data['status']=4
            # 排序

            # total_items = query.count()
            # print( '前',total_items)
            # print(datetime_1.date(year_1, month_1, day))
            query_1 = query.filter(
                RiskManagementInspectionTaskChildrenTask.tsak_time == datetime_1.date(year_1, month_1, day))
            # query = query.order_by(RiskManagementInspectionTaskChildrenTask.tsak_time.asc())

            # total_items = query_1.count()
            # print( '后',total_items)
            InspectionTasks = query_1.all()

            # 将查询结果转换为列表形式的字典
            InspectionTasks_list = []
            for task in InspectionTasks:
                # 判断这个任务该user_id完成了没
                # tasklog = db.query(RiskManagementInspectionTaskChildrenTaskLog).\
                #     filter(RiskManagementInspectionTaskChildrenTaskLog.del_flag!='2').\
                #     filter(RiskManagementInspectionTaskChildrenTaskLog.children_task_id==task.id).\
                #     filter(RiskManagementInspectionTaskChildrenTaskLog.area_code.in_(user_area_codes)).all()
                # task_area_code_list = [i.area_code for i in tasklog]
                query_2 = db.query(RiskManagementInspectionUser).filter(RiskManagementInspectionUser.del_flag!='2').filter(RiskManagementInspectionUser.user_id==user_id)
                if task.task_range == '0':
                    user_range_area_codes= ['440900000000']
                elif task.task_range == '1':
                    query_2 = query_2.filter(
                        and_(RiskManagementInspectionUser.area_code.like('%000000'), RiskManagementInspectionUser.area_code.notlike('%00000000')))
                    user_range_area_codes= list({i.area_code for i in query_2.all()})
                elif task.task_range == '2':
                    query_2 = query_2.filter(and_(RiskManagementInspectionUser.area_code.like('%000'), RiskManagementInspectionUser.area_code.notlike('%000000')))
                    user_range_area_codes= list({i.area_code for i in query_2.all()})
                elif task.task_range == '3':
                    query_2 = query_2.filter(RiskManagementInspectionUser.area_code.notlike('%000'))
                    user_range_area_codes= list({i.area_code for i in query_2.all()})
                else:
                    user_range_area_codes= []
                print(task.task_range)
                print(user_range_area_codes)

                for area_code in user_range_area_codes:
                    tasklog = db.query(RiskManagementInspectionTaskChildrenTaskLog). \
                        filter(RiskManagementInspectionTaskChildrenTaskLog.del_flag != '2'). \
                        filter(RiskManagementInspectionTaskChildrenTaskLog.children_task_id == task.id). \
                        filter(RiskManagementInspectionTaskChildrenTaskLog.area_code==area_code).first()
                    task_info = {
                        "id": task.id,
                        "business": task.type,
                        "task_range": task.task_range,
                        "cycle": task.cycle,
                        "area_code":area_code,
                        "area": area_code_get_ancestors_names(db,area_code_get_area_info(db,area_code)),
                        "task_status": tasklog is not None,
                        "task_time": task.tsak_time.strftime('%Y-%m-%d'),
                        "create_time": task.create_time.strftime('%Y-%m-%d %H:%M')
                    }
                    if tasklog :
                        task_info['create_time'] = tasklog.create_time.strftime('%Y-%m-%d %H:%M')
                        if task.tsak_time<datetime.today() and data['status']<1:
                            data['status'] = 1 #有事已完成
                        # elif tasklog.tsak_time > datetime.today():
                        #     data['status'] = 3  # 未来有事
                    else:
                        if task.tsak_time < datetime.today() and data['status'] < 2:
                            data['status'] = 2  # 有事未完成
                        elif task.tsak_time > datetime.today():
                            data['status'] = 3  # 未来有事
                    InspectionTasks_list.append(task_info)

            data['task_list']=InspectionTasks_list
            result.append(data)
        # 返回结果
        return {
            "code": 200,
            "msg": "成功",
            "data": result
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))

@router.get('/children/task/records')
async def get_children_task_records_list(
    search_keyword: str =Query(None, description='任务名称'),
    type: str =Query(None,  description='类型'),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(10, gt=0, description='每页条目数量'),
    db: Session = Depends(get_db),
    user_id = Depends(valid_access_token)
):
    try:

        # 获取子任务id
        query = db.query(RiskManagementInspectionTaskChildrenTask)
        query = query.filter(RiskManagementInspectionTaskChildrenTask.del_flag != '2')

        if type:
            query = query.filter(RiskManagementInspectionTaskChildrenTask.type==type)
        children_ids = [i.id for i in query.all()]
        print(children_ids)
        # 构建查询
        query = db.query(RiskManagementInspectionTaskChildrenTaskLog)
        query = query.filter(RiskManagementInspectionTaskChildrenTaskLog.del_flag != '2')

        query = query.filter(RiskManagementInspectionTaskChildrenTaskLog.user_id == user_id)
        query = query.filter(RiskManagementInspectionTaskChildrenTaskLog.children_task_id.in_(children_ids))
        total_items = query.count()

        # 排序

        query = query.order_by(RiskManagementInspectionTaskChildrenTaskLog.create_time.desc())
        # 执行分页查询
        InspectionTasks = query.offset((page - 1) * pageSize).limit(pageSize).all()

        # 将查询结果转换为列表形式的字典
        InspectionTasks_list = []
        for task in InspectionTasks:
            children_task = inspection_task_children_task_id_get_inspection_task_children_task_info(db,task.children_task_id)
            task_info = {
                "id": task.children_task_id,
                "business": children_task.type,
                "task_range": children_task.task_range,
                "cycle": children_task.cycle,
                "area_code": task.area_code,
                "area": area_code_get_ancestors_names(db, area_code_get_area_info(db, task.area_code)),
                "task_time": children_task.tsak_time.strftime('%Y-%m-%d'),
                "create_time": task.create_time.strftime('%Y-%m-%d %H:%M')
            }
            InspectionTasks_list.append(task_info)
            # 返回结果
        return {
            "code": 200,
            "msg": "成功",
            "data": InspectionTasks_list,
            "total": total_items,
            "page": page,
            "pageSize": pageSize,
            "totalPages": (total_items + pageSize - 1) // pageSize
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))


@router.get('/children/task/result/{children_task_id}')
async def get_children_task_result(
    children_task_id: str ,
    db: Session = Depends(get_db),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(10, gt=0, description='每页条目数量'),
    user_id = Depends(valid_access_token)
):
    try:
        # 构建查询
        query = db.query(RiskManagementInspectionTaskChildrenTaskResult)
        query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.del_flag != '2')
        # 应用查询条件
        query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.children_task_id == children_task_id)
        # 计算总条目数
        total_items = query.count()

        # 排序

        query = query.order_by(RiskManagementInspectionTaskChildrenTaskResult.create_time.desc())
        # 执行分页查询
        InspectionTasks = query.offset((page - 1) * pageSize).limit(pageSize).all()



        InspectionTasks_list = []
        for task in InspectionTasks:
            area_code = task.area_code
            area = area_code_get_area_info(db, area_code)
            area = area_code_get_ancestors_names(db, area)
            task_info = {
                "id": task.id,
                # "children_task_id": task.children_task_id,
                "type":inspection_task_children_task_id_get_inspection_task_children_task_info(db,task.children_task_id).type,
                "inspection_point_name": task.inspection_point_name,
                # "area": area,
                # "create_time": task.create_time.strftime('%Y-%m-%d'),
                # "nick_name": task.nick_name,
                "inspection_result": task.inspection_result,
                "remark": task.remark,
                "fileList": get_file_query_fun(db=db, from_scenario='RiskManagementInspectionTaskChildrenTaskResult',
                                               foreign_key=task.id)
            }
            InspectionTasks_list.append(task_info)
        # 返回结果
        return {
            "code": 200,
            "msg": "成功",
            "data": InspectionTasks_list,
            "total": total_items,
            "page": page,
            "pageSize": pageSize,
            "totalPages": (total_items + pageSize - 1) // pageSize
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))

@router.post('/children/task/result/create')
async def create_inspection_task(
    db: Session = Depends(get_db),
    body = Depends(remove_xss_json),
    user_id = Depends(valid_access_token)
):
    try:
        children_task_id = body['children_task_id']
        result = body['result']
        area_code = body['area_code']
        area =area_code_get_ancestors_names(db, area_code_get_area_info(db, area_code))
        # task_time = body['task_time']
        # 创建新的
        new_task_log = RiskManagementInspectionTaskChildrenTaskLog(
            id=new_guid(),
            children_task_id=children_task_id,
            area_code = area_code,
            area = area,
            task_status = '1',
            user_id = user_id,
            nick_name = user_id_get_user_info(db,user_id).nick_name,
            create_by = user_id
        )
        for info in result:
            new_file_list = info['fileList']
            inspection_point_name= info['inspection_point_name']
            inspection_result = info['inspection_result']
            remark = info['remark']
            new_task_result = RiskManagementInspectionTaskChildrenTaskResult(
                id=new_guid(),
                children_task_id=children_task_id,
                inspection_point_name=inspection_point_name,
                area_code=area_code,
                inspection_result=inspection_result,
                remark = remark,
                user_id = user_id,
                nick_name = user_id_get_user_info(db,user_id).nick_name,
                create_by = user_id
                )
            db.add(new_task_result)
            for file in new_file_list:
                file_name = file['file_name']
                file_name_desc = file['file_name_desc']
                status = file['status']
                new_file = RiskManagementFile(
                    file_id=new_guid(),
                    foreign_key=new_task_result.id,
                    from_scenario='RiskManagementInspectionTaskChildrenTaskResult',
                    file_name=file_name,
                    file_name_desc=file_name_desc,
                    status=status
                )
                db.add(new_file)
        # 添加到数据库会话并提交
        db.add(new_task_log)
        db.commit()
        
        # 标记消息已读
        child_task_info = db.query(RiskManagementInspectionTaskChildrenTask).filter(RiskManagementInspectionTaskChildrenTask.id == children_task_id).first()
        if child_task_info is not None:
            logger.info("标记[隐患巡查任务]已读 {}", str(child_task_info.task_id))
            db_msg_center.update_msg_read(db, user_id, "隐患巡查", str(child_task_info.task_id))

        # 返回创建成功的响应
        return {
            "code": 200,
            "msg": "成功",
            "data": None
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))


####中屏子任务
@router.get('/children/task/{task_id}/list')
async def get_inspection_task_list(
    task_id: str ,
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(10, gt=0, description='每页条目数量'),
    db: Session = Depends(get_db),
    user_id = Depends(valid_access_token)
):
    try:
        # 构建查询
        query = db.query(RiskManagementInspectionTaskChildrenTask)
        query = query.filter(RiskManagementInspectionTaskChildrenTask.del_flag != '2')
        # 应用查询条件

        query = query.filter(RiskManagementInspectionTaskChildrenTask.task_id == task_id)

        # 计算总条目数
        total_items = query.count()

        # 排序

        query = query.order_by(RiskManagementInspectionTaskChildrenTask.tsak_time.asc())
        # 执行分页查询
        InspectionTasks = query.offset((page - 1) * pageSize).limit(pageSize).all()

        # 将查询结果转换为列表形式的字典
        InspectionTasks_list = []
        for task in InspectionTasks:
            task_num=task.task_num
            completed_num = db.query(RiskManagementInspectionTaskChildrenTaskLog)\
                .filter(RiskManagementInspectionTaskChildrenTaskLog.del_flag!='2')\
                .filter(RiskManagementInspectionTaskChildrenTaskLog.children_task_id==task.id).count()
            incomplete_num = task_num-completed_num
            task_info = {
                "id": task.id,
                "task_id":task.task_id,
                "task_number": task.task_number,
                "business": task.type,
                "task_time": task.tsak_time.strftime('%Y-%m-%d'),
                "cycle": task.cycle,
                "inspection_range": task.task_range,
                "completed_num": completed_num,
                "incomplete_num": incomplete_num,
                "create_time": task.create_time.strftime('%Y-%m-%d')
            }
            InspectionTasks_list.append(task_info)
        # 返回结果
        return {
            "code": 200,
            "msg": "成功",
            "data": InspectionTasks_list,
            "total": total_items,
            "page": page,
            "pageSize": pageSize,
            "totalPages": (total_items + pageSize - 1) // pageSize
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))


@router.get('/children/task/log/{children_task_id}/{status}/export')
async def get_inspection_task_list(
        request: Request,
    children_task_id: str ,
    status:str,
    area_code: str = Query(None, description='区划编码'),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(10, gt=0, description='每页条目数量'),
    db: Session = Depends(get_db),
    user_id = Depends(valid_access_token),
        auth_user: AuthUser = Depends(find_auth_user),
):
    try:
        # 构建查询
        total_items= 0
        InspectionTasks_list = []
        status_cn = ''
        if status=='completed':
            status_cn='已完成'
            query = db.query(RiskManagementInspectionTaskChildrenTaskLog)
            query = query.filter(RiskManagementInspectionTaskChildrenTaskLog.del_flag != '2')
            # 应用查询条件

            query = query.filter(RiskManagementInspectionTaskChildrenTaskLog.children_task_id == children_task_id)

            if area_code:
                query = query.filter(RiskManagementInspectionTaskChildrenTaskLog.area_code == area_code)
            # 计算总条目数
            total_items = query.count()

            # 排序
            query = query.order_by(RiskManagementInspectionTaskChildrenTaskLog.create_time.desc())
            # 执行分页查询
            InspectionTasks = query.all()

            # 将查询结果转换为列表形式的字典
            for task in InspectionTasks:
                task_info = {
                    "id": task.id,
                    "子任务表id":children_task_id,
                    "区划编码": task.area_code,
                    "区划": task.area,
                    "任务进展": '已完成',
                    "巡查人员id": task.user_id,
                    "巡查人员": task.nick_name,
                    "创建时间": task.create_time.strftime('%Y-%m-%d')
                }
                InspectionTasks_list.append(task_info)
            # 返回结果
        elif status == 'incomplete':
            status_cn='未完成'
            children_task = inspection_task_children_task_id_get_inspection_task_children_task_info(db,children_task_id)
            if children_task:
                task = inspection_task_id_get_inspection_task_info(db,children_task.task_id)
                if task:
                    complete_area_code_list =[i.area_code for i in inspection_task_children_task_id_get_inspection_task_children_task_log_info(db,children_task_id)]
                    # area_code_list1 = get_area_code_exclude_list(db,task.inspection_range,complete_area_code_list)
                    if area_code:# and area_code in area_code_list:
                        area_code_list = area_code_rang_get_area_children_list(db,children_task.task_range,area_code,complete_area_code_list)#[area_code]
                        # print(area_code_list)
                    # elif area_code and  area_code not in area_code_list:
                    #     area_code_list = []
                    else:
                        area_code_list = get_area_code_exclude_list(db, task.inspection_range, complete_area_code_list)
                    for area_code in area_code_list:
                        # if area_code in area_code_list1:
                        task_info = {
                            "id": new_guid(),
                            "子任务表id": children_task_id,
                            "区划编码": area_code,
                            "区划": area_code_get_ancestors_names(db,area_code_get_area_info(db,area_code)),
                            "任务进展": '未完成',
                            "巡查人员id": '',
                            "巡查人员": '',
                            "创建时间": children_task.tsak_time.strftime('%Y-%m-%d')
                        }
                        InspectionTasks_list.append(task_info)
        import pandas as pd
        from io import BytesIO
        # 将查询结果转换为 DataFrame
        df = pd.DataFrame(InspectionTasks_list)

        # 将 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'巡查任务{status_cn}{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(db, auth_user, "巡查工作管理", f"巡查任务{status_cn}导出数据成功", request.client.host)

        # 返回文件流
        return StreamingResponse(output, headers=headers)

    except Exception as e:
        traceback.print_exc()
        return JSONResponse(status_code=500, content={"code": 500, "msg": f"Internal server error: {str(e)}"})

@router.get('/children/task/log/{children_task_id}/{status}/list')
async def get_inspection_task_list(
    children_task_id: str ,
    status:str,
    area_code: str = Query(None, description='区划编码'),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(10, gt=0, description='每页条目数量'),
    db: Session = Depends(get_db),
    user_id = Depends(valid_access_token)
):
    try:
        # 构建查询
        total_items= 0
        InspectionTasks_list = []
        if status=='completed':
            query = db.query(RiskManagementInspectionTaskChildrenTaskLog)
            query = query.filter(RiskManagementInspectionTaskChildrenTaskLog.del_flag != '2')
            # 应用查询条件

            query = query.filter(RiskManagementInspectionTaskChildrenTaskLog.children_task_id == children_task_id)

            if area_code:
                query = query.filter(RiskManagementInspectionTaskChildrenTaskLog.area_code == area_code)
            # 计算总条目数
            total_items = query.count()

            # 排序
            query = query.order_by(RiskManagementInspectionTaskChildrenTaskLog.create_time.desc())
            # 执行分页查询
            InspectionTasks = query.offset((page - 1) * pageSize).limit(pageSize).all()

            # 将查询结果转换为列表形式的字典
            for task in InspectionTasks:
                task_info = {
                    "id": task.id,
                    "children_task_id":children_task_id,
                    "area_code": task.area_code,
                    "area": task.area,
                    "task_status": task.task_status,
                    "user_id": task.user_id,
                    "nick_name": task.nick_name,
                    "create_time": task.create_time.strftime('%Y-%m-%d')
                }
                InspectionTasks_list.append(task_info)
            # 返回结果
        elif status == 'incomplete':
            children_task = inspection_task_children_task_id_get_inspection_task_children_task_info(db,children_task_id)
            if children_task:
                task = inspection_task_id_get_inspection_task_info(db,children_task.task_id)
                if task:
                    complete_area_code_list =[i.area_code for i in inspection_task_children_task_id_get_inspection_task_children_task_log_info(db,children_task_id)]
                    # area_code_list1 = get_area_code_exclude_list(db,task.inspection_range,complete_area_code_list)
                    if area_code:# and area_code in area_code_list:
                        area_code_list = area_code_rang_get_area_children_list(db,children_task.task_range,area_code,complete_area_code_list)#[area_code]
                        # print(area_code_list)
                    # elif area_code and  area_code not in area_code_list:
                    #     area_code_list = []
                    else:
                        area_code_list = get_area_code_exclude_list(db, task.inspection_range, complete_area_code_list)
                    for area_code in area_code_list:
                        # if area_code in area_code_list1:
                        task_info = {
                            "id": new_guid(),
                            "children_task_id": children_task_id,
                            "area_code": area_code,
                            "area": area_code_get_ancestors_names(db,area_code_get_area_info(db,area_code)),
                            "task_status": '0',
                            "user_id": '',
                            "nick_name": '',
                            "create_time": children_task.tsak_time.strftime('%Y-%m-%d')
                        }
                        InspectionTasks_list.append(task_info)
        return {
            "code": 200,
            "msg": "成功",
            "data": InspectionTasks_list,
            "total": total_items,
            "page": page,
            "pageSize": pageSize,
            "totalPages": (total_items + pageSize - 1) // pageSize
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))


@router.get('/children/task/result/{children_task_id}/list')
async def get_inspection_task_list(
    children_task_id: str ,
    area_code: str = Query(None, description='区划编码'),
    inspection_result: str = Query(None, description='巡查结果'),
    nick_name: str = Query(None, description='姓名'),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(10, gt=0, description='每页条目数量'),
    db: Session = Depends(get_db),
    user_id = Depends(valid_access_token)
):
    try:
        # 构建查询
        query = db.query(RiskManagementInspectionTaskChildrenTaskResult)
        query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.del_flag != '2')
        query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.children_task_id == children_task_id)
        # 应用查询条件
        if area_code:
            query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.area_code == area_code)
        if inspection_result:
            query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.inspection_result == inspection_result)
        if nick_name:
            query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.nick_name.like(f'%{nick_name}%') )

        # 计算总条目数
        total_items = query.count()

        # 排序

        query = query.order_by(RiskManagementInspectionTaskChildrenTaskResult.create_time.desc())
        # 执行分页查询
        InspectionTasks = query.offset((page - 1) * pageSize).limit(pageSize).all()

        # 将查询结果转换为列表形式的字典
        InspectionTasks_list = []
        for task in InspectionTasks:
            area_code = task.area_code
            area = area_code_get_area_info(db, area_code)
            area = area_code_get_ancestors_names(db, area)
            task_info = {
                "id": task.id,
                "children_task_id": task.children_task_id,
                "inspection_point_name":task.inspection_point_name,
                "area": area,
                "create_time": task.create_time.strftime('%Y-%m-%d'),
                "nick_name": task.nick_name,
                "inspection_result": task.inspection_result,
                "fileList": get_file_query_fun(db=db,from_scenario='RiskManagementInspectionTaskChildrenTaskResult', foreign_key=task.id),
                "remark":task.remark
            }
            InspectionTasks_list.append(task_info)
        # 返回结果
        return {
            "code": 200,
            "msg": "成功",
            "data": InspectionTasks_list,
            "total": total_items,
            "page": page,
            "pageSize": pageSize,
            "totalPages": (total_items + pageSize - 1) // pageSize
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=str(e))


@router.get('/children/task/result/{children_task_id}/export')
async def get_inspection_task_list(
        request: Request,
        children_task_id: str ,
        area_code: str = Query(None, description='区划编码'),
        inspection_result: str = Query(None, description='巡查结果'),
        nick_name: str = Query(None, description='姓名'),
        db: Session = Depends(get_db),
        auth_user: AuthUser = Depends(find_auth_user),
    user_id = Depends(valid_access_token)
):
    try:
        # 构建查询
        query = db.query(RiskManagementInspectionTaskChildrenTaskResult)
        query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.del_flag != '2')
        query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.children_task_id == children_task_id)
        # 应用查询条件
        if area_code:
            query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.area_code == area_code)
        if inspection_result:
            query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.inspection_result == inspection_result)
        if nick_name:
            query = query.filter(RiskManagementInspectionTaskChildrenTaskResult.nick_name.like(f'%{nick_name}%') )

        # 计算总条目数
        total_items = query.count()

        # 排序

        query = query.order_by(RiskManagementInspectionTaskChildrenTaskResult.create_time.desc())
        # 执行分页查询
        InspectionTasks = query.all()

        # 将查询结果转换为列表形式的字典
        InspectionTasks_list = []
        for task in InspectionTasks:
            area_code = task.area_code
            area = area_code_get_area_info(db, area_code)
            area = area_code_get_ancestors_names(db, area)
            task_info = {
                "id": task.id,
                "子任务表id": task.children_task_id,
                "经度":task.longitude,
                "纬度":task.latitude,
                "巡查点":task.inspection_point_name,
                "区划": area,
                "创建时间": task.create_time.strftime('%Y-%m-%d'),
                "采集人员": task.nick_name,
                "采集结果": task.inspection_result,
                # "fileList": get_file_query_fun(db=db,from_scenario='RiskManagementInspectionTaskChildrenTaskResult', foreign_key=task.id),
                "备注":task.remark
            }
            InspectionTasks_list.append(task_info)
        # 返回结果
        import pandas as pd
        from io import BytesIO
        # 将查询结果转换为 DataFrame
        df = pd.DataFrame(InspectionTasks_list)

        # 将 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(db, auth_user, "巡查任务管理", f"巡查任务巡查结果导出数据成功", request.client.host)

        # 返回文件流
        return StreamingResponse(output, headers=headers)
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        return JSONResponse(status_code=500, content={"code": 500, "msg": f"Internal server error: {str(e)}"})