#!/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)}")