python-fastapi-mm-zhcs-yj-api/routers/api/videoResource/videoinfo.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from fastapi import APIRouter, Request, Depends,Query,HTTPException,status
from fastapi.responses import StreamingResponse
from database import get_db
from sqlalchemy.orm import Session
from sqlalchemy import case,or_
from sqlalchemy import inspect
from sqlalchemy import  text
from utils import *
from utils.ry_system_util import *
from utils.video_util import *
from common.security import valid_access_token
from common.db import db_czrz
from common.auth_user import *
from fastapi.responses import JSONResponse
import traceback
from datetime import datetime

router = APIRouter()

@router.get('/video_from')
async def get_dict_data_by_type(
    db: Session = Depends(get_db),
    body = Depends(remove_xss_json),
    user_id = Depends(valid_access_token)
):
    # 视频来源
    try:
        # 根据 dict_type 查询字典数据
        query = db.query(TPVideoRegion)

        # 添加查询条件
        # if dictType:
        query = query.filter(TPVideoRegion.parentIndexCode=='root00000000')
        query = query.filter(TPVideoRegion.status == '0')
        query = query.order_by(TPVideoRegion.sort)
        dict_data = query.all()

        # 转换为字典
        dict_data_list = [
            {
                "dictCode": d.indexCode,
                "dictSort": d.sort,
                "dictLabel": d.name,
                "dictValue": d.indexCode,
                "dictType": 'video',
                "cssClass": '',
                "listClass": 'default',
                "isDefault": 'N',
                "remark": '',
                "createTime": d.createTime.strftime('%Y-%m-%d %H:%M:%S') if d.createTime else ''
            }
            for d in dict_data
        ]

        # 构建返回结果
        result = {
            "rows": dict_data_list,
            "code": 200,
            "msg": "查询成功"
        }
        return result

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

@router.get('/get_video_list_new')
async def get_video_url_by_id(
    area_code: str = Query(None, description='区划编码'),
    video_from: str = Query(None, description='视频来源'),
    video_tag: str = Query(None, description='视频标签'),
    video_name: str = Query(None, description='视频名称'),
    longitude: float = Query(None, description='经度'),
    latitude: float = Query(None, description='纬度'),
    db: Session = Depends(get_db),
    body=Depends(remove_xss_json),
    user_id=Depends(valid_access_token),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(10, gt=0, description='每页条目数量')
):
    # 大屏左下角视频及更多视频
    try:

        area_code_dict = {"440902":"茂南区","440904":"电白区","440981":"高州市","440982":"化州市","440983":"信宜市","440900":"直辖市"}
        query = db.query(TPVideoInfo)

        if area_code:
            query = query.filter(TPVideoInfo.gbIndexCode.like(f'%{area_code}%'))
        if video_from:
            query = query.filter(TPVideoInfo.regionPath.like(f'%{video_from}%'))
        if video_name:
            query = query.filter(TPVideoInfo.name.like(f'%{video_name}%'))
        if video_tag:
            tag_info = get_dict_data_info(db, 'video_type', video_tag)
            if tag_info:
                if tag_info.dict_label != '全量视频':
                    videoli = [info.video_code for info in tag_get_video_tag_list(db, video_tag)]
                    query = query.filter(TPVideoInfo.gbIndexCode.in_(videoli))
        videoIds = user_id_get_user_videoIds(db, user_id)
        video_list = [i.video_code_int for i in videoIds]

        if video_list:
            video_priority_case = case(
                [
                    (TPVideoInfo.gbIndexCode == i, 0)
                    for i in video_list
                ],
                else_=1
            ).label('user_priority')
            query = query.add_columns(video_priority_case).order_by('user_priority')

        if longitude is not None and latitude is not None:
            point = func.ST_GeomFromText(f'POINT({latitude} {longitude})', 4326)
            query = query.add_columns(
                func.ST_Distance_Sphere(TPVideoInfo.location, point).label('distance')
            ).order_by('distance')




        total_items = query.count()
        videos = query.offset(pageSize * (page - 1)).limit(pageSize).all()
        data = []
        for row in videos:
            row = row[0]
            # print(type(row),row[0])
            tag_list =get_video_tag_list(db,row.gbIndexCode)
            tag = []
            tag_lable = []
            for info in tag_list:
                tag_info = get_dict_data_info(db, 'video_type', info.dict_value)
                if tag_info:
                    if tag_info.dict_label not in tag_lable and tag_info.dict_label!='全量视频':
                        tag.append({"id": info.id,
                                    "video_code": row.gbIndexCode,
                                    "dict_type": info.dict_type,
                                    "dict_value": info.dict_value,
                                    "dict_label": tag_info.dict_label,
                                    "dict_code": tag_info.dict_code})
                        tag_lable.append(tag_info.dict_label)
            video_area_code=row.gbIndexCode[:6]
            video_area = ''
            if video_area_code in area_code_dict:
                video_area=area_code_dict[video_area_code]
            data.append({
                "name":row.name,
                "isUserVideos":row.gbIndexCode in video_list,
                "video_code": row.gbIndexCode,
                "isTag" : len(tag_list)>0,
                "tag" : tag,
                "tagLabels" : "、".join(tag_lable),
                "status":row.status,
                "statusName":row.statusName,
                "regionPath":row.regionPath,
                "installPlace":row.installPlace,
                "cameraTypeName":row.cameraTypeName,
                "cameraType":row.cameraType,
                "video_area":video_area
            })
        return {
            "code": 200,
            "msg": "操作成功",
            "data": data,
            "total": total_items,
            "page": page,
            "pageSize": pageSize,
            "totalPages": (total_items + pageSize - 1) // pageSize
        }
    except Exception as e:
        # 处理异常
        traceback.print_exc()
        raise HTTPException(status_code=status.HTTP_500_INTERNAL_SERVER_ERROR, detail=str(e))

@router.get('/get_video_list_by_user')
async def get_video_url_by_id(
    longitude:float = Query(None,  description='经度'),
    latitude:float = Query(None,  description='纬度'),
    db: Session = Depends(get_db),
    body=Depends(remove_xss_json),
    user_id=Depends(valid_access_token),
    page: int = Query(1, gt=0, description='页码'),
    pageSize: int = Query(10, gt=0, description='每页条目数量')
):
    # 用户视频排序
    if longitude is not None and latitude is not None:
        location = f"""ST_Distance_Sphere(
        ST_GeomFromText(CONCAT('POINT(', longitude, ' ', latitude, ')')), 
        ST_PointFromText('POINT({longitude} {latitude})'))"""
        orddis = 'distance'
    else:
        location = 0
        orddis = ''
    videoIds = user_id_get_user_videoIds(db, user_id)
    video_list = [i.video_code_int for i in videoIds]
    if len(video_list)==0:
        video = ''
    else:
        video = ""
        for i in video_list:
            video += f"WHEN '{i}' THEN 0 \n"

        video = f"""CASE video_code_int
    {video}
    ELSE 1
END """
    if orddis != '' and video !='':
        video += ','
    sql  = f"""SELECT T1.indexcode,T2.`name`,T1.longitude,T1.latitude,
        {location} AS distance,T2.area,T2.ip,T2.`status`,T2.status_lifetime,T2.record_status,T2.inspection_datetime,T2.video_code_int,T2.video_code
    FROM tp_video_base T1 RIGHT JOIN tp_video_log T2 on T1.indexcode=T2.video_code_int -- where  T1.longitude is not NULL
    ORDER BY {video} {orddis} """
    totalsql = f'select count(*) from ({sql})t'
    print(video_list)

    total_items = db.execute(totalsql).first()[0]

    lim = f"limit  {pageSize*(page-1)}, {pageSize};"
    videos = db.execute(sql+lim).all()
    video_list1 = []
    for video in videos:
        videoInfo = {
            "name": video.name,
            "invideoIds": video.video_code_int in video_list,
            "area": video.area,
            "ip": video.ip,
            "status": video.status,
            "status_lifetime": video.status_lifetime,
            "record_status": video.record_status,
            "inspection_datetime": video.inspection_datetime,
            "video_code_int": video.video_code_int,
            "video_code": video.video_code,
            "longitude":video.longitude,
            "latitude":video.latitude
        }
        video_list1.append(videoInfo)
    return {
            "code": 200,
            "msg": "操作成功",
            "rows": video_list1,
            "total": total_items,
            "page": page,
            "pageSize": pageSize,
            "totalPages": (total_items + pageSize - 1) // pageSize
        }

@router.get('/get_waterlogged_all_video_info')
async def get_waterlogged_all_video_info(
        radius:int = Query(None),
        longitude:str = Query(None),
        latitude:str = Query(None),
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        page: int = Query(1, gt=0, description='页码'),
        pageSize: int = Query(10, gt=0, description='每页条目数量'),
        user_id=Depends(valid_access_token)):
    # 雨窝点视频
    try:
        if radius is None:
            radius=500
        # print(1,datetime.now)
        # sql = f"""SELECT * from mid_waterlogged_roads """
        # waterlogged_data =  db.execute(sql).all()
        # resutl = []
        # print(2,datetime.now)
        # tj = []
        # for location_1 in waterlogged_data:
        if longitude is None or latitude is None:
            tj ="1=1"
            px = "1"
        else:
            location = f"POINT({longitude} {latitude})"
            tj = f"ST_Distance_Sphere(ST_GeomFromText(CONCAT('POINT(', longitude, ' ', latitude, ')')),ST_GeomFromText(CONCAT('{location}'))) <= '{radius}'"
            px = f"ST_Distance_Sphere(ST_GeomFromText(CONCAT('POINT(', longitude, ' ', latitude, ')')),ST_GeomFromText(CONCAT('{location}'))) "
        # tj = ' or '.join(tj)
        sql = text(f"""SELECT  indexcode,`name`,longitude,latitude,status FROM (
                SELECT gbIndexCode as indexcode,`name`,longitude,latitude ,status
                FROM tp_video_info where longitude is not null and latitude is not null and ({tj}) and `unitIndexCode`='eab0ed85ff9e4369a0e52680301c3160' 
                order by {px}
                 ) T limit {pageSize*(page-1)}, {pageSize}""")
        resutl=db.execute(sql).all()

        total_items = len(resutl)
        return {
            "code": 200,
            "msg": "成功",
            "data": {"list":[{"indexcode":info.indexcode,"name":info.name,"longitude":info.longitude,"latitude":info.latitude,"status":info.status} for info in resutl[(page - 1) * pageSize:(page - 1) * pageSize+pageSize]]},
            "page": page,
            "pageSize": pageSize,
            "total":total_items,
            "totalPages": (total_items + pageSize - 1) // pageSize
        }

    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.get('/get_video_list')
async def get_video_forest_fire_list(
        video_type:str = Query(None),
        area:str = Query(None),
        name:str = Query(None),
        db: Session = Depends(get_db),
        page: int = Query(1, gt=0, description='页码'),
        pageSize: int = Query(0, gt=0, description='每页条目数量'),
        body=Depends(remove_xss_json),
        user_id=Depends(valid_access_token)):

    try:
        sql = '''SELECT T1.`name`,IF(T1.`status`='在线',1,0) as `status`,T1.`video_code_int`as `video_code`,IFNULL(IFNULL(T3.longitude,T2.longitude),0) as longitude,IFNULL(IFNULL(T3.latitude,T2.latitude),0) as latitude,CASE 
            WHEN IFNULL(T3.area,T1.area) like '%高州%' THEN
                '440981'
            WHEN IFNULL(T3.area,T1.area) like '%信宜%' THEN
                '440983'
            WHEN IFNULL(T3.area,T1.area) like '%化州%' THEN
                '440982'
            WHEN IFNULL(T3.area,T1.area) like '%茂南%' THEN
                '440902'
            WHEN IFNULL(T3.area,T1.area) like '%电白%' THEN
                '440904'
            ELSE
                '440900'
        END 
         as area  FROM tp_video_log T1 LEFT JOIN (SELECT indexcode,longitude,latitude FROM `tp_video_base`where latitude>0) T2 on T1.video_code_int=T2.indexcode  LEFT JOIN tp_video_log_transportation_area_info T3 on T1.video_code_int=T3.video_code where 1=1  and T2.longitude>0 '''

        if video_type:
            sql += f''' and T1.video_code_int in (select video_code from Tp_Video_Tag where del_flag ='0' and dict_value='{video_type}')'''
        if name:
            sql += f''' and T1.name like '%{name}%' '''
        # 计算总条目数
        totalsql = f'select count(*) as ct from ({sql})t where longitude > 0'
        total_items =  db.execute(totalsql).first().ct
        # 排序

        if pageSize!=0:
            sql = f'''select * from ({sql} limit {pageSize*(page-1)}, {pageSize})t where longitude > 0 '''
            video_list = db.execute(sql).all()
            totalPages= (total_items + pageSize - 1) // pageSize
        else:
            pageSize = total_items
            totalPages = 1
            video_list = db.execute(f"""select * from ({sql})t where longitude > 0 """).all()
        result = []
        for video_info in video_list:
            longitude = video_info.longitude
            latitude = video_info.latitude
            area = video_info.area
            result.append({"area":area,
                           "status":video_info.status,
                           "name":video_info.name,
                           "video_code":video_info.video_code,
                           # "video_type_label":video_type_label,
                           "longitude":longitude,
                           "latitude":latitude})


        return {
            "code": 200,
            "msg": "成功",
            "data": result,
            'total':total_items,
            "page": page,
            "pageSize": pageSize,
            "totalPages": totalPages
        }

    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.get('/get_video_transportation_list')
async def get_video_forest_fire_list(
        video_type:str = Query(None),
        area:str = Query(None),
        name:str = Query(None),
        db: Session = Depends(get_db),
        page: int = Query(1, gt=0, description='页码'),
        pageSize: int = Query(10, gt=0, description='每页条目数量'),
        body=Depends(remove_xss_json),
        user_id=Depends(valid_access_token)):

    try:
        video_type_list = ['sjyld','sgdfd','jtdd','dzzhyhd']
        # video_code_list = [item[0] for item in db.query(TpVideoTag.id).filter(TpVideoTag.dict_value == '4').all()]
        video_list = db.query(TPVideoInfo).filter(TPVideoInfo.regionPath.like('@root00000000@44000000002160847111@%'))
        if video_type:
            if video_type in video_type_list:

                lis = [i.video_code for i in db.query(TpVideoTag.video_code).filter(TpVideoTag.dict_value==video_type , TpVideoTag.del_flag =='0').all()]
                video_list = video_list.filter(TPVideoInfo.gbIndexCode.in_(lis))
        if area:
            if area=='直辖市':
                lis = [i.video_code for i in db.query(TpVideoLogTransportationAreaInfo.video_code).all()]
                video_list = video_list.filter(TPVideoInfo.gbIndexCode.in_(lis))
            else:
                lis = [i.video_code for i in db.query(TpVideoLogTransportationAreaInfo.video_code).filter(TpVideoLogTransportationAreaInfo.area == area).all()]
                video_list = video_list.filter(TPVideoInfo.gbIndexCode.in_(lis))
        if name:
            video_list = video_list.filter(TPVideoInfo.name.like(f'%{name}%'))
        # 计算总条目数
        total_items = video_list.count()
        # 排序

        video_list = video_list.order_by(TPVideoInfo.gbIndexCode)
        # 执行分页查询
        # print(video_list.offset((page - 1) * pageSize).limit(pageSize))
        video_list = video_list.offset((page - 1) * pageSize).limit(pageSize).all()
        result = []
        # print(total_items,len(video_list),(page - 1) * pageSize,pageSize)
        for video_info in video_list:
            video_code = video_info.gbIndexCode
            video_type_label='其他'
            longitude = 0
            latitude = 0
            area = '直辖市'
            video_type_li = db.query(TpVideoTag).filter(TpVideoTag.video_code == video_code,
                                                   TpVideoTag.del_flag == '0',TpVideoTag.dict_value.in_(video_type_list)).first()
            if video_type_li:
                query = db.query(SysDictData)
                query = query.filter(SysDictData.del_flag != '2')
                query = query.filter(SysDictData.dict_type == 'video_type_8')
                query = query.filter(SysDictData.dict_value == video_type_li.dict_value).first()
                if query:
                    video_type_label = query.dict_label

            video_base_info = db.query(TpVideoLogTransportationAreaInfo).filter(TpVideoLogTransportationAreaInfo.video_code==video_code).first()
            if video_base_info:
                longitude = video_base_info.longitude
                latitude = video_base_info.latitude
                area = video_base_info.area
            result.append({"area":area,"status":video_info.status,"name":video_info.name,"video_code":video_code,"video_type_label":video_type_label,"longitude":longitude,"latitude":latitude})


        return {
            "code": 200,
            "msg": "成功",
            "data": result,
            '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=f"Internal server error: {str(e)}")

@router.get('/get_point_info_transportation_video')
@router.post('/get_point_info_transportation_video')
async def get_video_forest_fire_list(
        db: Session = Depends(get_db),
        page: int = Query(1, gt=0, description='页码'),
        pageSize: int = Query(10, gt=0, description='每页条目数量'),
        body=Depends(remove_xss_json),
        user_id=Depends(valid_access_token)):

    try:
        video_type_list = ['sjyld','sgdfd','jtdd','dzzhyhd']
        # video_code_list = [item[0] for item in db.query(TpVideoTag.id).filter(TpVideoTag.dict_value == '4').all()]
        video_list = db.query(TPVideoInfo).filter(TPVideoInfo.regionPath.like('@root00000000@44000000002160847111@%'))

        if 'query' in body:
            if 'id' in body['query']:
                video_list = video_list.filter(TPVideoInfo.gbIndexCode==body['query']['id'])
        # 计算总条目数
        total_items = video_list.count()
        # 排序

        video_list = video_list.order_by(TPVideoInfo.gbIndexCode)
        # 执行分页查询
        # print(video_list.offset((page - 1) * pageSize).limit(pageSize))
        video_list = video_list.offset((page - 1) * pageSize).limit(pageSize).all()
        result = []
        # print(total_items,len(video_list),(page - 1) * pageSize,pageSize)
        for video_info in video_list:
            video_code = video_info.gbIndexCode
            video_type_label='其他'
            longitude = 0
            latitude = 0
            area = '直辖市'
            video_type_li = db.query(TpVideoTag).filter(TpVideoTag.video_code == video_code,
                                                   TpVideoTag.del_flag == '0',TpVideoTag.dict_value.in_(video_type_list)).first()
            if video_type_li:
                query = db.query(SysDictData)
                query = query.filter(SysDictData.del_flag != '2')
                query = query.filter(SysDictData.dict_type == 'video_type')
                query = query.filter(SysDictData.dict_value == video_type_li.dict_value).first()
                if query:
                    video_type_label = query.dict_label

            video_base_info = db.query(TpVideoLogTransportationAreaInfo).filter(TpVideoLogTransportationAreaInfo.video_code==video_code).first()
            if video_base_info:
                longitude = video_base_info.longitude
                latitude = video_base_info.latitude
                area = video_base_info.area
            result.append({"area":area,"status":video_info.status,"name":video_info.name,"video_code":video_info.gbIndexCode,"video_type_label":video_type_label,"longitude":longitude,"latitude":latitude})


        return {
            "code": 0,
            "msg": "success",
            "rows": result,
            'total':total_items,
            "currentPage": page,
            "pageSize": pageSize,
            "pages": (total_items + pageSize - 1) // pageSize
        }

    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.get('/transportation/type')
async def get_dict_data_by_type(
    db: Session = Depends(get_db),
    body = Depends(remove_xss_json),
    user_id = Depends(valid_access_token)
):
    try:
        # 根据 dict_type 查询字典数据
        video_type_list = ['sjyld','sgdfd','jtdd','dzzhyhd']
        query = db.query(SysDictData)
        query = query.filter(SysDictData.dict_type=='video_type_8')
        query = query.filter(SysDictData.dict_value.in_(video_type_list))
        query = query.filter(SysDictData.del_flag != '2')
        query = query.order_by(SysDictData.dict_sort)
        # dict_data = db.query(SysDictData).filter_by(dict_type==dict_type and del_flag != '2').all()
        dict_data = query.all()
        # 将模型转换为字典
        dict_data_list = [
            {
                "dictCode": d.dict_code,
                "dictSort": d.dict_sort,
                "dictLabel": d.dict_label,
                "dictValue": d.dict_value,
                "dictType": d.dict_type,
                "cssClass": d.css_class,
                "listClass": d.list_class,
                "isDefault": d.is_default,
                "remark": d.remark,
                "createTime": d.create_time.strftime('%Y-%m-%d %H:%M:%S') if d.create_time else ''
            }
            for d in dict_data
        ]

        # 构建返回结果
        result = {
            "code": 200,
            "msg": "操作成功",
            "data": dict_data_list
        }
        return result
    except Exception as e:
        # 处理异常

        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")
def id_get_region_info(db, indexCode):
    query = db.query(TPVideoRegion)
    query = query.filter(TPVideoRegion.indexCode == indexCode)
    return query.all()
def parent_id_get_region_info(db, parent_id):
    query = db.query(TPVideoRegion)
    query = query.filter(TPVideoRegion.parentIndexCode == parent_id)
    query = query.order_by(TPVideoRegion.sort.asc())
    return query.all()
def parent_id_get_video_info(db, unitIndexCode,status):
    query = db.query(TPVideoInfo)
    query = query.filter(TPVideoInfo.unitIndexCode == unitIndexCode)
    query = query.order_by(TPVideoInfo.gbIndexCode.asc())
    if status:
        query = query.filter(TPVideoInfo.status == status)
    return query.all()
def region_path_get_video_info(db, regionPath,status):
    query = db.query(TPVideoInfo)
    query = query.filter(TPVideoInfo.regionPath.like(f"%{regionPath}%"))
    query = query.order_by(TPVideoInfo.gbIndexCode.asc())
    if status:
        query = query.filter(TPVideoInfo.status == status)
    return query.all()
@router.get('/get_video_forest_fire_index_code')
async def get_dict_data_by_type(
    db: Session = Depends(get_db)
):
    try:
        # 根据 dict_type 查询字典数据
        # dict_data = db.query(SysDictData).filter_by(dict_type=dictType).all()
        d = parent_id_get_region_info(db, '4409000000216202502')

        dict_data_list = [{
                "dictCode": data.indexCode,
                "dictSort": data.sort,
                "dictLabel": data.name,
                "dictValue": data.indexCode,
                "dictType": '',
                "cssClass": '',
                "listClass": ''
            }for data in d]

        # 构建返回结果
        result = {
            "data": dict_data_list,
            "code": 200,
            "msg": "查询成功"
        }
        return result

    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.get('/get_video_forest_fire_export')
async def get_video_forest_fire_list(
        request: Request,
        indexCode:str = Query('4409000000216202502'),
        status:str = Query(None),
        db: Session = Depends(get_db),
        user_id: AuthUser = Depends(find_auth_user),
        body=Depends(remove_xss_json)):

    try:
        data = region_path_get_video_info(db, indexCode, status)
        data = [{
                "摄像头索引码": info.cameraIndexCode,
                "国标索引码": info.gbIndexCode,
                "摄像头名称": info.name,
                "经度": info.longitude,
                "纬度": info.latitude,
                "海拔": info.altitude,
                "像素": info.pixel,
                "摄像头类型代码": info.cameraType,
                "摄像头类型名称": info.cameraTypeName,
                "安装位置": info.installPlace,
                "所属组织编号": info.unitIndexCode,
                "机构路径": info.regionPath,
                "更新时间": info.updateTime,
                "创建时间": info.createTime,
                "状态码": info.status,
                "状态名称": info.statusName
            } for info in data]
        import pandas as pd
        from io import BytesIO
        # 将查询结果转换为 DataFrame
        df = pd.DataFrame(data)

        # 将 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, user_id, "全域地图", 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)}"})


@router.get('/get_video_forest_fire_list')
async def get_video_forest_fire_list(
        indexCode:str = Query('4409000000216202502'),
        status:str = Query(None),
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        user_id=Depends(valid_access_token)):

    try:

        """
            根据层级路径构建带有 label 和 children 的树形结构。
            :param paths: 包含层级路径的列表
            :return: 树形结构的字典
            """
        "20ba3e565de64df9b29ff70a8056939a"


        def build_video_tree(regions, parent_region,online,total,status):
            video_tree = []
            for region_info in regions:
                region = {
                    "label": region_info.name,
                    "isLeaf": False,
                    "status": region_info.status,
                    "children":[]
                }
                # print(dept_info.dept_id)

                videos = parent_id_get_video_info(db, region_info.indexCode,status)
                if len(videos) > 0:
                    for video_info in videos:
                        region["children"].append({"label":video_info.name,"status":video_info.status,"video_code":video_info.gbIndexCode,"isLeaf":True})
                        if video_info.status==1:
                            online+=1
                        total += 1
                children = parent_id_get_region_info(db, region_info.indexCode)
                if len(children) > 0:
                    children_regions, online, total = build_video_tree(children, region, online, total,status)
                    region["children"] = children_regions+region["children"]
                video_tree.append(region)

            return video_tree,online,total

        result,online,total = build_video_tree(id_get_region_info(db, indexCode), None,0,0,status)


        return {
            "code": 200,
            "msg": "成功",
            "data": result,
            'online':online,
            'total':total
        }

        #
        # video_code_list = [item[0] for item in db.query(TpVideoTag.video_code).filter(TpVideoTag.dict_value == 'slfh').all()]
        # # print(video_code_list)
        # video_list = db.query(TpVideoLog).filter(TpVideoLog.video_code.in_(video_code_list)).all()
        # root = {"label": "Root", "children": [],"online":0,"total":0}  # 创建根节点
        #
        # for video_info in video_list:
        #     levels = video_info.area.split('/')
        #     current_node = root
        #     current_node['total']+=1
        #     if video_info.status == '在线':
        #         current_node['online']+=1
        #     for level in levels:
        #         # 查找当前层级是否已存在
        #         existing_node = next((node for node in current_node["children"] if node["label"] == level), None)
        #         if not existing_node:
        #             # 如果不存在，创建新节点
        #             new_node = {"label": level, "children": [],"online":0,"total":0}
        #             current_node["children"].append(new_node)
        #             existing_node = new_node
        #
        #         # 移动到子节点
        #         current_node = existing_node
        #         current_node['total']+=1
        #         if video_info.status == '在线':
        #             current_node['online']+=1
        #
        #     current_node['children'].append({"label":video_info.name,"status":video_info.status,"video_code":video_info.video_code,"isLeaf":True})
        #
        # return {
        #     "code": 200,
        #     "msg": "成功",
        #     "data": root['children'],
        #     'online':root['online'],
        # 'total':root['total']
        # }

    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")



@router.get('/get_video_tag_info')
async def get_video_tag_info(
        video_code:str = Query(None),
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        user_id=Depends(valid_access_token)
):
    try:
        tag = []
        tag_lable = []
        for info in get_video_tag_list(db,video_code):
            tag_info = get_dict_data_info(db,'video_type',info.dict_value)
            if tag_info:
                if tag_info.dict_label not in tag_lable:
                    tag.append({"id":info.id,
                                "video_code":video_code,
                                "dict_type":info.dict_type,
                                "dict_value":info.dict_value,
                                "dict_label":tag_info.dict_label,
                                "dict_code":tag_info.dict_code})
                    tag_lable.append(tag_info.dict_label)
        return {
            "code": 200,
            "msg": "成功",
            "data": tag
        }
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.post("/add_video_tag")
async def add_video_tag(
    user_id=Depends(valid_access_token),
    body = Depends(remove_xss_json),
    db: Session = Depends(get_db)
):
    try:
        tag_info = get_dict_data_info(db, body['dict_type'], body['dict_value'])
        if tag_info is None:
            return JSONResponse(status_code=404,content={"code":404,"msg":"标签不存在"})
        new_video_tag = TpVideoTag(
            id = new_guid(),
            video_code=body['video_code'],
            dict_value=body['dict_value'],
            dict_type=body['dict_type'],
            create_dept = user_id
        )
        db.add(new_video_tag)
        db.commit()
        return {"code": 200, "msg": "新增成功", "data": None}
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.post("/add_video_tag_label")
async def add_video_tag(
    user_id=Depends(valid_access_token),
    body = Depends(remove_xss_json),
    db: Session = Depends(get_db)
):
    try:
        dict_label = body['dict_label']
        dict_info = dict_label_get_dict_data_info(db,body['dict_type'],dict_label)
        if dict_info is None:
            dict_info = SysDictData(
                dict_label=dict_label,
                dict_value = new_guid(),
                dict_type=body['dict_type'],
                list_class = 'default',
                create_by = user_id
            )
            db.add(dict_info)
        new_video_tag = TpVideoTag(
            id = new_guid(),
            video_code=body['video_code'],
            dict_value=dict_info.dict_value,
            dict_type=body['dict_type'],
            create_dept = user_id
        )
        db.add(new_video_tag)
        db.commit()
        return {"code": 200, "msg": "新增成功", "data": None}
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

@router.delete("/delete_video_tag/{video_tag_id}")
async def delete_video_tag(
    video_tag_id: str,
    db: Session = Depends(get_db)
):
    try:
        # 检查图案是否存在
        query = db.query(TpVideoTag)
        query = query.filter(TpVideoTag.id == video_tag_id)
        query = query.filter(TpVideoTag.del_flag != '2')
        video_tag = query.first()
        if not video_tag:
            return JSONResponse(status_code=404,content={"code":404,"msg":"标签不存在"})

        # 执行删除操作
        video_tag.del_flag='2'
        db.commit()
        return {"code": 200, "msg": "删除成功"}
    except Exception as e:
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")


@router.get('/get_recently_video_tag_info')
async def get_video_tag_info(
        # video_code:str = Query(None),
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        page: int = Query(1, gt=0, description='页码'),
        pageSize: int = Query(3, gt=0, description='每页条目数量'),
        user_id=Depends(valid_access_token)
):
    try:
        query = db.query(SysDictData)
        query = query.filter(SysDictData.del_flag != '2')
        query = query.filter(SysDictData.dict_type == 'video_type')

        total_items = query.count()
        # 排序
        query = query.order_by(SysDictData.create_time.desc())
        # 执行分页查询
        dicts = query.offset((page - 1) * pageSize).limit(pageSize).all()
        tag = []

        for info in dicts:
            tag.append({
                        "dict_type":info.dict_type,
                        "dict_value":info.dict_value,
                        "dict_label":info.dict_label,
                        "dict_code":info.dict_code})
        return {
            "code": 200,
            "msg": "成功",
            "data": tag,
            "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=f"Internal server error: {str(e)}")

@router.get('/get_lx_hy_video_tag_info')
async def get_lx_hy_video_tag_info(
        dict_value:str = Query(None),
        type:str= Query(None),
        db: Session = Depends(get_db),
        body=Depends(remove_xss_json),
        page: int = Query(1, gt=0, description='页码'),
        pageSize: int = Query(3, gt=0, description='每页条目数量'),
        user_id=Depends(valid_access_token)
):
    try:

        if dict_value is None or dict_value =='':
            query = db.query(SysDictData)
            query = query.filter(SysDictData.del_flag != '2')
            query = query.filter(SysDictData.dict_type == 'video_type')



            if type=='lx':
                query_1 = db.query(SysDictData)
                query_1 = query_1.filter(SysDictData.del_flag != '2')
                query_1 = query_1.filter(SysDictData.dict_type == 'video_tag_type')
            elif type == 'hy':
                query_1 = db.query(SysDictData)
                query_1 = query_1.filter(SysDictData.del_flag != '2')
                query_1 = query_1.filter(SysDictData.dict_type == 'video_tag_industry')
            else:
                return {
                    "code": 200,
                    "msg": "成功",
                    "data": []
                }
            # for i in query_1.all():
            query = query.filter(or_(SysDictData.remark.like(f'%{i.dict_value};%') for i in query_1.all()))
            total_items = query.count()
            # 排序
            query = query.order_by(SysDictData.create_time.desc())
            # 执行分页查询
            dicts = query.offset((page - 1) * pageSize).limit(pageSize).all()
            tag = []

            for info in dicts:
                tag.append({
                    "dict_type": info.dict_type,
                    "dict_value": info.dict_value,
                    "dict_label": info.dict_label,
                    "dict_code": info.dict_code})
            return {
                "code": 200,
                "msg": "成功",
                "data": tag,
                "total": total_items,
                "page": page,
                "pageSize": pageSize,
                "totalPages": (total_items + pageSize - 1) // pageSize
            }
        query = db.query(SysDictData)
        query = query.filter(SysDictData.del_flag != '2')
        query = query.filter(SysDictData.dict_type == 'video_type')
        query = query.filter(SysDictData.remark.like( f'%{dict_value};%'))

        total_items = query.count()
        # 排序
        query = query.order_by(SysDictData.create_time.desc())
        # 执行分页查询
        dicts = query.offset((page - 1) * pageSize).limit(pageSize).all()
        tag = []

        for info in dicts:
            tag.append({
                        "dict_type":info.dict_type,
                        "dict_value":info.dict_value,
                        "dict_label":info.dict_label,
                        "dict_code":info.dict_code})
        return {
            "code": 200,
            "msg": "成功",
            "data": tag,
            "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=f"Internal server error: {str(e)}")