# @Author : Rocky # @File : VodHlsService.py # @Time : 2023/2/1 15:57 import datetime from django.db import connection from django.db.models import Count from Model.models import VodHlsMon, VodHlsTues, VodHlsWed, VodHlsThur, VodHlsFri, VodHlsSat, VodHlsSun, VodHlsTag, \ AiService, VodHlsTagType from Service.CommonService import CommonService class SplitVodHlsObject: # VodHls分表功能类 @classmethod def creat_vod_hls_data(cls, **kwargs): """ 分表保存云存信息数据 """ start_time = kwargs.get('start_time') week = datetime.datetime.fromtimestamp(int(start_time)).isoweekday() if week == 1: vod_hls_mon = VodHlsMon.objects.create(**kwargs) return vod_hls_mon, week elif week == 2: vod_hls_tues = VodHlsTues.objects.create(**kwargs) return vod_hls_tues, week elif week == 3: vod_hls_wed = VodHlsWed.objects.create(**kwargs) return vod_hls_wed, week elif week == 4: vod_hls_thur = VodHlsThur.objects.create(**kwargs) return vod_hls_thur, week elif week == 5: vod_hls_fri = VodHlsFri.objects.create(**kwargs) return vod_hls_fri, week elif week == 6: vod_hls_sat = VodHlsSat.objects.create(**kwargs) return vod_hls_sat, week elif week == 7: vod_hls_sun = VodHlsSun.objects.create(**kwargs) return vod_hls_sun, week def del_vod_hls_data(self, **kwargs): """ 分表删除云存信息数据 """ VodHlsMon.objects.filter(**kwargs).delete() VodHlsTues.objects.filter(**kwargs).delete() VodHlsWed.objects.filter(**kwargs).delete() VodHlsThur.objects.filter(**kwargs).delete() VodHlsFri.objects.filter(**kwargs).delete() VodHlsSat.objects.filter(**kwargs).delete() VodHlsSun.objects.filter(**kwargs).delete() def get_vod_hls_data(self, **kwargs): """ 分表获取云存信息数据 @return: vod_hls """ vod_hls = VodHlsMon.objects.filter(pk=-1) if 'start_time' in kwargs: start_time = kwargs.get('start_time') week = datetime.datetime.fromtimestamp(int(start_time)).isoweekday() if week == 1: vod_hls = VodHlsMon.objects.filter(**kwargs) elif week == 2: vod_hls = VodHlsTues.objects.filter(**kwargs) elif week == 3: vod_hls = VodHlsWed.objects.filter(**kwargs) elif week == 4: vod_hls = VodHlsThur.objects.filter(**kwargs) elif week == 5: vod_hls = VodHlsFri.objects.filter(**kwargs) elif week == 6: vod_hls = VodHlsSat.objects.filter(**kwargs) elif week == 7: vod_hls = VodHlsSun.objects.filter(**kwargs) return vod_hls if 'start_time__range' in kwargs: start_time, end_time = kwargs.get('start_time__range') vod_hls_mon = VodHlsMon.objects.filter(pk=-1) vod_hls_tus = VodHlsTues.objects.filter(pk=-1) vod_hls_wed = VodHlsWed.objects.filter(pk=-1) vod_hls_thur = VodHlsThur.objects.filter(pk=-1) vod_hls_fri = VodHlsFri.objects.filter(pk=-1) vod_hls_sat = VodHlsSat.objects.filter(pk=-1) vod_hls_sun = VodHlsSun.objects.filter(pk=-1) start_time = datetime.datetime.fromtimestamp(int(start_time)) end_time = datetime.datetime.fromtimestamp(int(end_time)) time_list = CommonService.cutting_time_stamp(start_time, end_time) day_list = [] type_list = kwargs.get('type_list') for time_item in time_list: week = datetime.datetime.fromtimestamp(int(time_item[0])).isoweekday() if week not in day_list: day_list.append(week) for week in day_list: kwargs['type_list'] = type_list if week == 1: kwargs = self.vod_query_param(week, **kwargs) if not kwargs: return vod_hls_mon vod_hls_mon = VodHlsMon.objects.filter(**kwargs) elif week == 2: kwargs = self.vod_query_param(week, **kwargs) if not kwargs: return vod_hls_tus vod_hls_tus = VodHlsTues.objects.filter(**kwargs) elif week == 3: kwargs = self.vod_query_param(week, **kwargs) if not kwargs: return vod_hls_wed vod_hls_wed = VodHlsWed.objects.filter(**kwargs) elif week == 4: kwargs = self.vod_query_param(week, **kwargs) if not kwargs: return vod_hls_thur vod_hls_thur = VodHlsThur.objects.filter(**kwargs) elif week == 5: kwargs = self.vod_query_param(week, **kwargs) if not kwargs: return vod_hls_fri vod_hls_fri = VodHlsFri.objects.filter(**kwargs) elif week == 6: kwargs = self.vod_query_param(week, **kwargs) if not kwargs: return vod_hls_sat vod_hls_sat = VodHlsSat.objects.filter(**kwargs) elif week == 7: kwargs = self.vod_query_param(week, **kwargs) if not kwargs: return vod_hls_sun vod_hls_sun = VodHlsSun.objects.filter(**kwargs) vod_hls = vod_hls.union(vod_hls_mon, vod_hls_tus, vod_hls_wed, vod_hls_thur, vod_hls_fri, vod_hls_sat, vod_hls_sun) return vod_hls vod_hls_mon = VodHlsMon.objects.filter(**kwargs) vod_hls_tus = VodHlsTues.objects.filter(**kwargs) vod_hls_wed = VodHlsWed.objects.filter(**kwargs) vod_hls_thur = VodHlsThur.objects.filter(**kwargs) vod_hls_fri = VodHlsFri.objects.filter(**kwargs) vod_hls_sat = VodHlsSat.objects.filter(**kwargs) vod_hls_sun = VodHlsSun.objects.filter(**kwargs) vod_hls = vod_hls.union(vod_hls_mon, vod_hls_tus, vod_hls_wed, vod_hls_thur, vod_hls_fri, vod_hls_sat, vod_hls_sun) return vod_hls def get_vod_hls_date(self, **kwargs): """ 分表获取云存日期信息数据 @return: vod_hls """ vod_hls_mon = VodHlsMon.objects.extra(select={'date': "FROM_UNIXTIME(start_time,'%%Y-%%m-%%d')"}).values( 'date').filter(**kwargs).annotate(count=Count('start_time')).order_by('-date') vod_hls_tus = VodHlsTues.objects.extra(select={'date': "FROM_UNIXTIME(start_time,'%%Y-%%m-%%d')"}).values( 'date').filter(**kwargs).annotate(count=Count('start_time')).order_by('-date') vod_hls_wed = VodHlsWed.objects.extra(select={'date': "FROM_UNIXTIME(start_time,'%%Y-%%m-%%d')"}).values( 'date').filter(**kwargs).annotate(count=Count('start_time')).order_by('-date') vod_hls_thur = VodHlsThur.objects.extra(select={'date': "FROM_UNIXTIME(start_time,'%%Y-%%m-%%d')"}).values( 'date').filter(**kwargs).annotate(count=Count('start_time')).order_by('-date') vod_hls_fri = VodHlsFri.objects.extra(select={'date': "FROM_UNIXTIME(start_time,'%%Y-%%m-%%d')"}).values( 'date').filter(**kwargs).annotate(count=Count('start_time')).order_by('-date') vod_hls_sat = VodHlsSat.objects.extra(select={'date': "FROM_UNIXTIME(start_time,'%%Y-%%m-%%d')"}).values( 'date').filter(**kwargs).annotate(count=Count('start_time')).order_by('-date') vod_hls_sun = VodHlsSun.objects.extra(select={'date': "FROM_UNIXTIME(start_time,'%%Y-%%m-%%d')"}).values( 'date').filter(**kwargs).annotate(count=Count('start_time')).order_by('-date') vod_hls = vod_hls_mon.union(vod_hls_tus, vod_hls_wed, vod_hls_thur, vod_hls_fri, vod_hls_sat, vod_hls_sun) return vod_hls @classmethod def cloud_vod_hls_tag(cls, vod_id, num, uid, start_time): """ 云存回调信息关联标签 @param vod_id: 云存回放信息id @param num: 所在表编号 @param uid: 设备UID @param start_time: 云存开始时间 @return: True | False """ try: # 查询设备是否有使用中的ai服务 # ai_service_qs = AiService.objects \ # .filter(uid=uid, detect_status=1, use_status=1, endTime__gt=start_time) \ # .values('detect_group') # if not ai_service_qs.exists(): # return False start_time = int(start_time) end_time = start_time + 5 vod_tag_qs = VodHlsTag.objects.filter(ai_event_time__range=(start_time, end_time), uid=uid) if not vod_tag_qs.exists(): return False vod_tag_qs.update(vod_hls_id=vod_id, tab_num=num) return True except Exception as e: print('异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e))) return False @classmethod def vod_query_param(cls, week, **kwargs): types = kwargs.get('type_list') vod_ids = cls.query_tag_by_types(kwargs.get('uid'), kwargs.get('start_time__range'), kwargs.get('type_list'), week) if vod_ids: kwargs['id__in'] = vod_ids if types and not vod_ids: return [] kwargs.pop('type_list') return kwargs @classmethod def query_tag_by_types(cls, uid, time, types, num): """ 根据类型查询云存标签关联ID @return: """ try: if not types or len(types) == 0: return [] cursor = connection.cursor() param_list = [int(time[0]), int(time[1]), uid, num] sql = 'SELECT vod_hls_id FROM ' sql += 'vod_hls_tag vht INNER JOIN vod_hls_tag_type vhtt ON vht.id = vhtt.tag_id ' sql += 'WHERE vht.ai_event_time >= %s AND vht.ai_event_time <= %s ' sql += 'AND vht.uid = %s ' sql += 'AND tab_num = %s ' if types: sql += 'AND vhtt.type IN %s ' param_list.append(types) sql += 'GROUP BY vht.vod_hls_id ' cursor.execute(sql, param_list) data_list = cursor.fetchall() cursor.close() # 执行完，关闭 connection.close() data_list = [val[0] for val in data_list] return data_list except Exception as e: print('异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e))) return False @classmethod def query_tag_type_list(cls, start_time, vod_ids): """ 根据云存ID获取AI类型 @return: """ try: week = datetime.datetime.fromtimestamp(int(start_time)).isoweekday() qs = VodHlsTag.objects.filter(vod_hls_id__in=vod_ids, tab_num=int(week)).values('id', 'vod_hls_id') if not qs: return [] return list(qs) except Exception as e: print('异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e))) return []