# @Author : Rocky # @File : VodHlsService.py # @Time : 2023/2/1 15:57 import random from Ansjer.config import LOGGER from django.db import connection from django.db.models import Value, CharField, Count from Model.models import VodHls01, VodHls02, VodHls03, VodHls04, VodHls05, VodHls06, VodHls07, VodHls08, VodHls09, \ VodHls10, VodHls11, VodHls12, VodHls13, VodHls14, VodHls15, VodHls16, VodHls17, VodHls18, VodHls19, VodHls20, \ VodHls21, VodHls22, VodHls23, VodHls24, VodHls25, VodHls26, VodHls27, VodHls28, VodHls29, VodHls30, VodHls31, \ VodHls32, VodHls33, VodHls34, VodHls35, VodHls36, VodHls37, VodHls38, VodHls39, VodHls40, VodHlsTag Vod_Hls_List = [VodHls01, VodHls02, VodHls03, VodHls04, VodHls05, VodHls06, VodHls07, VodHls08, VodHls09, VodHls10, VodHls11, VodHls12, VodHls13, VodHls14, VodHls15, VodHls16, VodHls17, VodHls18, VodHls19, VodHls20, VodHls21, VodHls22, VodHls23, VodHls24, VodHls25, VodHls26, VodHls27, VodHls28, VodHls29, VodHls30, VodHls31, VodHls32, VodHls33, VodHls34, VodHls35, VodHls36, VodHls37, VodHls38, VodHls39, VodHls40] class SplitVodHlsObject: # VodHls分表功能类 @classmethod def creat_vod_hls_data(cls, **kwargs): """ 分表保存云存信息数据 """ vod_hls_model = random.choice(Vod_Hls_List) tal_num = Vod_Hls_List.index(vod_hls_model) + 1 vod_hls_obj = vod_hls_model.objects.create(**kwargs) return vod_hls_obj, tal_num def del_vod_hls_data(self, **kwargs): """ 分表删除云存信息数据 """ if 'id__in' in kwargs: for vod_id in kwargs['id__in']: tal_index = int(vod_id[:2]) - 11 temp_id = vod_id[2:] Vod_Hls_List[tal_index].objects.filter(id=temp_id).delete() else: for model in Vod_Hls_List: model.objects.filter(**kwargs).delete() def get_vod_hls_data(self, **kwargs): """ 分表获取云存信息数据 @return: vod_hls """ if 'type_list' in kwargs and kwargs['type_list']: kwargs = self.vod_query_param(**kwargs) if 'id__in' in kwargs: if 1 in kwargs['id__in']: vod_hls_01 = VodHls01.objects.filter(id__in=kwargs['id__in'][1]).annotate( tab_val=Value('11', output_field=CharField())) else: vod_hls_01 = VodHls01.objects.none().annotate(tab_val=Value('11', output_field=CharField())) if 2 in kwargs['id__in']: vod_hls_02 = VodHls02.objects.filter(id__in=kwargs['id__in'][2]).annotate( tab_val=Value('12', output_field=CharField())) else: vod_hls_02 = VodHls02.objects.none().annotate(tab_val=Value('12', output_field=CharField())) if 3 in kwargs['id__in']: vod_hls_03 = VodHls03.objects.filter(id__in=kwargs['id__in'][3]).annotate( tab_val=Value('13', output_field=CharField())) else: vod_hls_03 = VodHls03.objects.none().annotate(tab_val=Value('13', output_field=CharField())) if 4 in kwargs['id__in']: vod_hls_04 = VodHls04.objects.filter(id__in=kwargs['id__in'][4]).annotate( tab_val=Value('14', output_field=CharField())) else: vod_hls_04 = VodHls04.objects.none().annotate(tab_val=Value('14', output_field=CharField())) if 5 in kwargs['id__in']: vod_hls_05 = VodHls05.objects.filter(id__in=kwargs['id__in'][5]).annotate( tab_val=Value('15', output_field=CharField())) else: vod_hls_05 = VodHls05.objects.none().annotate(tab_val=Value('15', output_field=CharField())) if 6 in kwargs['id__in']: vod_hls_06 = VodHls06.objects.filter(id__in=kwargs['id__in'][6]).annotate( tab_val=Value('16', output_field=CharField())) else: vod_hls_06 = VodHls06.objects.none().annotate(tab_val=Value('16', output_field=CharField())) if 7 in kwargs['id__in']: vod_hls_07 = VodHls07.objects.filter(id__in=kwargs['id__in'][7]).annotate( tab_val=Value('17', output_field=CharField())) else: vod_hls_07 = VodHls07.objects.none().annotate(tab_val=Value('17', output_field=CharField())) if 8 in kwargs['id__in']: vod_hls_08 = VodHls08.objects.filter(id__in=kwargs['id__in'][8]).annotate( tab_val=Value('18', output_field=CharField())) else: vod_hls_08 = VodHls08.objects.none().annotate(tab_val=Value('18', output_field=CharField())) if 9 in kwargs['id__in']: vod_hls_09 = VodHls09.objects.filter(id__in=kwargs['id__in'][9]).annotate( tab_val=Value('19', output_field=CharField())) else: vod_hls_09 = VodHls09.objects.none().annotate(tab_val=Value('19', output_field=CharField())) if 10 in kwargs['id__in']: vod_hls_10 = VodHls10.objects.filter(id__in=kwargs['id__in'][10]).annotate( tab_val=Value('20', output_field=CharField())) else: vod_hls_10 = VodHls10.objects.none().annotate(tab_val=Value('20', output_field=CharField())) if 11 in kwargs['id__in']: vod_hls_11 = VodHls11.objects.filter(id__in=kwargs['id__in'][11]).annotate( tab_val=Value('21', output_field=CharField())) else: vod_hls_11 = VodHls11.objects.none().annotate(tab_val=Value('21', output_field=CharField())) if 12 in kwargs['id__in']: vod_hls_12 = VodHls12.objects.filter(id__in=kwargs['id__in'][12]).annotate( tab_val=Value('22', output_field=CharField())) else: vod_hls_12 = VodHls12.objects.none().annotate(tab_val=Value('22', output_field=CharField())) if 13 in kwargs['id__in']: vod_hls_13 = VodHls13.objects.filter(id__in=kwargs['id__in'][13]).annotate( tab_val=Value('23', output_field=CharField())) else: vod_hls_13 = VodHls13.objects.none().annotate(tab_val=Value('23', output_field=CharField())) if 14 in kwargs['id__in']: vod_hls_14 = VodHls14.objects.filter(id__in=kwargs['id__in'][14]).annotate( tab_val=Value('24', output_field=CharField())) else: vod_hls_14 = VodHls14.objects.none().annotate(tab_val=Value('24', output_field=CharField())) if 15 in kwargs['id__in']: vod_hls_15 = VodHls15.objects.filter(id__in=kwargs['id__in'][15]).annotate( tab_val=Value('25', output_field=CharField())) else: vod_hls_15 = VodHls15.objects.none().annotate(tab_val=Value('25', output_field=CharField())) if 16 in kwargs['id__in']: vod_hls_16 = VodHls16.objects.filter(id__in=kwargs['id__in'][16]).annotate( tab_val=Value('26', output_field=CharField())) else: vod_hls_16 = VodHls16.objects.none().annotate(tab_val=Value('26', output_field=CharField())) if 17 in kwargs['id__in']: vod_hls_17 = VodHls17.objects.filter(id__in=kwargs['id__in'][17]).annotate( tab_val=Value('27', output_field=CharField())) else: vod_hls_17 = VodHls17.objects.none().annotate(tab_val=Value('27', output_field=CharField())) if 18 in kwargs['id__in']: vod_hls_18 = VodHls18.objects.filter(id__in=kwargs['id__in'][18]).annotate( tab_val=Value('28', output_field=CharField())) else: vod_hls_18 = VodHls18.objects.none().annotate(tab_val=Value('28', output_field=CharField())) if 19 in kwargs['id__in']: vod_hls_19 = VodHls19.objects.filter(id__in=kwargs['id__in'][19]).annotate( tab_val=Value('29', output_field=CharField())) else: vod_hls_19 = VodHls19.objects.none().annotate(tab_val=Value('29', output_field=CharField())) if 20 in kwargs['id__in']: vod_hls_20 = VodHls20.objects.filter(id__in=kwargs['id__in'][20]).annotate( tab_val=Value('30', output_field=CharField())) else: vod_hls_20 = VodHls20.objects.none().annotate(tab_val=Value('30', output_field=CharField())) if 21 in kwargs['id__in']: vod_hls_21 = VodHls21.objects.filter(id__in=kwargs['id__in'][21]).annotate( tab_val=Value('31', output_field=CharField())) else: vod_hls_21 = VodHls21.objects.none().annotate(tab_val=Value('31', output_field=CharField())) if 22 in kwargs['id__in']: vod_hls_22 = VodHls22.objects.filter(id__in=kwargs['id__in'][22]).annotate( tab_val=Value('32', output_field=CharField())) else: vod_hls_22 = VodHls22.objects.none().annotate(tab_val=Value('32', output_field=CharField())) if 23 in kwargs['id__in']: vod_hls_23 = VodHls23.objects.filter(id__in=kwargs['id__in'][23]).annotate( tab_val=Value('33', output_field=CharField())) else: vod_hls_23 = VodHls23.objects.none().annotate(tab_val=Value('33', output_field=CharField())) if 24 in kwargs['id__in']: vod_hls_24 = VodHls24.objects.filter(id__in=kwargs['id__in'][24]).annotate( tab_val=Value('34', output_field=CharField())) else: vod_hls_24 = VodHls24.objects.none().annotate(tab_val=Value('34', output_field=CharField())) if 25 in kwargs['id__in']: vod_hls_25 = VodHls25.objects.filter(id__in=kwargs['id__in'][25]).annotate( tab_val=Value('35', output_field=CharField())) else: vod_hls_25 = VodHls25.objects.none().annotate(tab_val=Value('35', output_field=CharField())) if 26 in kwargs['id__in']: vod_hls_26 = VodHls26.objects.filter(id__in=kwargs['id__in'][26]).annotate( tab_val=Value('36', output_field=CharField())) else: vod_hls_26 = VodHls26.objects.none().annotate(tab_val=Value('36', output_field=CharField())) if 27 in kwargs['id__in']: vod_hls_27 = VodHls27.objects.filter(id__in=kwargs['id__in'][27]).annotate( tab_val=Value('37', output_field=CharField())) else: vod_hls_27 = VodHls27.objects.none().annotate(tab_val=Value('37', output_field=CharField())) if 28 in kwargs['id__in']: vod_hls_28 = VodHls28.objects.filter(id__in=kwargs['id__in'][28]).annotate( tab_val=Value('38', output_field=CharField())) else: vod_hls_28 = VodHls28.objects.none().annotate(tab_val=Value('38', output_field=CharField())) if 29 in kwargs['id__in']: vod_hls_29 = VodHls29.objects.filter(id__in=kwargs['id__in'][29]).annotate( tab_val=Value('39', output_field=CharField())) else: vod_hls_29 = VodHls29.objects.none().annotate(tab_val=Value('39', output_field=CharField())) if 30 in kwargs['id__in']: vod_hls_30 = VodHls30.objects.filter(id__in=kwargs['id__in'][30]).annotate( tab_val=Value('40', output_field=CharField())) else: vod_hls_30 = VodHls30.objects.none().annotate(tab_val=Value('40', output_field=CharField())) if 31 in kwargs['id__in']: vod_hls_31 = VodHls31.objects.filter(id__in=kwargs['id__in'][31]).annotate( tab_val=Value('41', output_field=CharField())) else: vod_hls_31 = VodHls31.objects.none().annotate(tab_val=Value('41', output_field=CharField())) if 32 in kwargs['id__in']: vod_hls_32 = VodHls32.objects.filter(id__in=kwargs['id__in'][32]).annotate( tab_val=Value('42', output_field=CharField())) else: vod_hls_32 = VodHls32.objects.none().annotate(tab_val=Value('42', output_field=CharField())) if 33 in kwargs['id__in']: vod_hls_33 = VodHls33.objects.filter(id__in=kwargs['id__in'][33]).annotate( tab_val=Value('43', output_field=CharField())) else: vod_hls_33 = VodHls33.objects.none().annotate(tab_val=Value('43', output_field=CharField())) if 34 in kwargs['id__in']: vod_hls_34 = VodHls34.objects.filter(id__in=kwargs['id__in'][34]).annotate( tab_val=Value('44', output_field=CharField())) else: vod_hls_34 = VodHls34.objects.none().annotate(tab_val=Value('44', output_field=CharField())) if 35 in kwargs['id__in']: vod_hls_35 = VodHls35.objects.filter(id__in=kwargs['id__in'][35]).annotate( tab_val=Value('45', output_field=CharField())) else: vod_hls_35 = VodHls35.objects.none().annotate(tab_val=Value('45', output_field=CharField())) if 36 in kwargs['id__in']: vod_hls_36 = VodHls36.objects.filter(id__in=kwargs['id__in'][36]).annotate( tab_val=Value('46', output_field=CharField())) else: vod_hls_36 = VodHls36.objects.none().annotate(tab_val=Value('46', output_field=CharField())) if 37 in kwargs['id__in']: vod_hls_37 = VodHls37.objects.filter(id__in=kwargs['id__in'][37]).annotate( tab_val=Value('47', output_field=CharField())) else: vod_hls_37 = VodHls37.objects.none().annotate(tab_val=Value('47', output_field=CharField())) if 38 in kwargs['id__in']: vod_hls_38 = VodHls38.objects.filter(id__in=kwargs['id__in'][38]).annotate( tab_val=Value('48', output_field=CharField())) else: vod_hls_38 = VodHls38.objects.none().annotate(tab_val=Value('48', output_field=CharField())) if 39 in kwargs['id__in']: vod_hls_39 = VodHls39.objects.filter(id__in=kwargs['id__in'][39]).annotate( tab_val=Value('49', output_field=CharField())) else: vod_hls_39 = VodHls39.objects.none().annotate(tab_val=Value('49', output_field=CharField())) if 40 in kwargs['id__in']: vod_hls_40 = VodHls40.objects.filter(id__in=kwargs['id__in'][40]).annotate( tab_val=Value('50', output_field=CharField())) else: vod_hls_40 = VodHls40.objects.none().annotate(tab_val=Value('50', output_field=CharField())) vod_hls_union = vod_hls_01.union(vod_hls_02, vod_hls_03, vod_hls_04, vod_hls_05, vod_hls_06, vod_hls_07, vod_hls_08, vod_hls_09, vod_hls_10, vod_hls_11, vod_hls_12, vod_hls_13, vod_hls_14, vod_hls_15, vod_hls_16, vod_hls_17, vod_hls_18, vod_hls_19, vod_hls_20, vod_hls_21, vod_hls_22, vod_hls_23, vod_hls_24, vod_hls_25, vod_hls_26, vod_hls_27, vod_hls_28, vod_hls_29, vod_hls_30, vod_hls_31, vod_hls_32, vod_hls_33, vod_hls_34, vod_hls_35, vod_hls_36, vod_hls_37, vod_hls_38, vod_hls_39, vod_hls_40, all=True) else: vod_hls_union = VodHls01.objects.none().annotate(tab_val=Value('11', output_field=CharField())) else: if 'type_list' in kwargs: kwargs.pop('type_list') vod_hls_01 = VodHls01.objects.filter(**kwargs).annotate(tab_val=Value('11', output_field=CharField())) vod_hls_02 = VodHls02.objects.filter(**kwargs).annotate(tab_val=Value('12', output_field=CharField())) vod_hls_03 = VodHls03.objects.filter(**kwargs).annotate(tab_val=Value('13', output_field=CharField())) vod_hls_04 = VodHls04.objects.filter(**kwargs).annotate(tab_val=Value('14', output_field=CharField())) vod_hls_05 = VodHls05.objects.filter(**kwargs).annotate(tab_val=Value('15', output_field=CharField())) vod_hls_06 = VodHls06.objects.filter(**kwargs).annotate(tab_val=Value('16', output_field=CharField())) vod_hls_07 = VodHls07.objects.filter(**kwargs).annotate(tab_val=Value('17', output_field=CharField())) vod_hls_08 = VodHls08.objects.filter(**kwargs).annotate(tab_val=Value('18', output_field=CharField())) vod_hls_09 = VodHls09.objects.filter(**kwargs).annotate(tab_val=Value('19', output_field=CharField())) vod_hls_10 = VodHls10.objects.filter(**kwargs).annotate(tab_val=Value('20', output_field=CharField())) vod_hls_11 = VodHls11.objects.filter(**kwargs).annotate(tab_val=Value('21', output_field=CharField())) vod_hls_12 = VodHls12.objects.filter(**kwargs).annotate(tab_val=Value('22', output_field=CharField())) vod_hls_13 = VodHls13.objects.filter(**kwargs).annotate(tab_val=Value('23', output_field=CharField())) vod_hls_14 = VodHls14.objects.filter(**kwargs).annotate(tab_val=Value('24', output_field=CharField())) vod_hls_15 = VodHls15.objects.filter(**kwargs).annotate(tab_val=Value('25', output_field=CharField())) vod_hls_16 = VodHls16.objects.filter(**kwargs).annotate(tab_val=Value('26', output_field=CharField())) vod_hls_17 = VodHls17.objects.filter(**kwargs).annotate(tab_val=Value('27', output_field=CharField())) vod_hls_18 = VodHls18.objects.filter(**kwargs).annotate(tab_val=Value('28', output_field=CharField())) vod_hls_19 = VodHls19.objects.filter(**kwargs).annotate(tab_val=Value('29', output_field=CharField())) vod_hls_20 = VodHls20.objects.filter(**kwargs).annotate(tab_val=Value('30', output_field=CharField())) vod_hls_21 = VodHls21.objects.filter(**kwargs).annotate(tab_val=Value('31', output_field=CharField())) vod_hls_22 = VodHls22.objects.filter(**kwargs).annotate(tab_val=Value('32', output_field=CharField())) vod_hls_23 = VodHls23.objects.filter(**kwargs).annotate(tab_val=Value('33', output_field=CharField())) vod_hls_24 = VodHls24.objects.filter(**kwargs).annotate(tab_val=Value('34', output_field=CharField())) vod_hls_25 = VodHls25.objects.filter(**kwargs).annotate(tab_val=Value('35', output_field=CharField())) vod_hls_26 = VodHls26.objects.filter(**kwargs).annotate(tab_val=Value('36', output_field=CharField())) vod_hls_27 = VodHls27.objects.filter(**kwargs).annotate(tab_val=Value('37', output_field=CharField())) vod_hls_28 = VodHls28.objects.filter(**kwargs).annotate(tab_val=Value('38', output_field=CharField())) vod_hls_29 = VodHls29.objects.filter(**kwargs).annotate(tab_val=Value('39', output_field=CharField())) vod_hls_30 = VodHls30.objects.filter(**kwargs).annotate(tab_val=Value('40', output_field=CharField())) vod_hls_31 = VodHls31.objects.filter(**kwargs).annotate(tab_val=Value('41', output_field=CharField())) vod_hls_32 = VodHls32.objects.filter(**kwargs).annotate(tab_val=Value('42', output_field=CharField())) vod_hls_33 = VodHls33.objects.filter(**kwargs).annotate(tab_val=Value('43', output_field=CharField())) vod_hls_34 = VodHls34.objects.filter(**kwargs).annotate(tab_val=Value('44', output_field=CharField())) vod_hls_35 = VodHls35.objects.filter(**kwargs).annotate(tab_val=Value('45', output_field=CharField())) vod_hls_36 = VodHls36.objects.filter(**kwargs).annotate(tab_val=Value('46', output_field=CharField())) vod_hls_37 = VodHls37.objects.filter(**kwargs).annotate(tab_val=Value('47', output_field=CharField())) vod_hls_38 = VodHls38.objects.filter(**kwargs).annotate(tab_val=Value('48', output_field=CharField())) vod_hls_39 = VodHls39.objects.filter(**kwargs).annotate(tab_val=Value('49', output_field=CharField())) vod_hls_40 = VodHls40.objects.filter(**kwargs).annotate(tab_val=Value('50', output_field=CharField())) vod_hls_union = vod_hls_01.union(vod_hls_02, vod_hls_03, vod_hls_04, vod_hls_05, vod_hls_06, vod_hls_07, vod_hls_08, vod_hls_09, vod_hls_10, vod_hls_11, vod_hls_12, vod_hls_13, vod_hls_14, vod_hls_15, vod_hls_16, vod_hls_17, vod_hls_18, vod_hls_19, vod_hls_20, vod_hls_21, vod_hls_22, vod_hls_23, vod_hls_24, vod_hls_25, vod_hls_26, vod_hls_27, vod_hls_28, vod_hls_29, vod_hls_30, vod_hls_31, vod_hls_32, vod_hls_33, vod_hls_34, vod_hls_35, vod_hls_36, vod_hls_37, vod_hls_38, vod_hls_39, vod_hls_40, all=True) LOGGER.info('vod_hls_union结果：{},kwargs:{}'.format(vod_hls_union.values(), kwargs)) return vod_hls_union def get_vod_hls_date(self, **kwargs): """ 分表获取云存日期信息数据 @return: vod_hls """ vod_hls_union = VodHls01.objects.none() for vod_hls_model in Vod_Hls_List: vod_hls_union = vod_hls_union.union(vod_hls_model.objects.extra( select={'date': "FROM_UNIXTIME(start_time,'%%Y-%%m-%%d')"}).values('date', 'start_time').filter( **kwargs).annotate( count=Count('start_time')).order_by('-date')) result = {} for item in vod_hls_union: if item['date'] not in result: result[item['date']] = item['count'] else: result[item['date']] += item['count'] res = [] for key, value in result.items(): res.append({'date': key, 'count': value}) return res @classmethod def cloud_vod_hls_tag(cls, vod_id, num, uid, start_time, sec): """ 云存回调信息关联标签 @param vod_id: 云存回放信息id @param num: 云存所在表编号 @param uid: 设备UID @param start_time: 云存开始时间 @param sec: 云存片段秒数 @return: True | False """ try: start_time = int(start_time) end_time = start_time + sec 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( 'cloud_vod_hls_tag异常详情,uid:{},errLine:{},errMsg:{}'.format(uid, e.__traceback__.tb_lineno, repr(e))) return False @classmethod def vod_query_param(cls, **kwargs): vod_ids = cls.query_tag_by_types(kwargs.get('uid'), kwargs.get('start_time__range'), kwargs.get('type_list')) if vod_ids: kwargs['id__in'] = vod_ids kwargs.pop('type_list') return kwargs @classmethod def query_tag_by_types(cls, uid, time, types): """ 根据类型查询云存标签关联ID @return: """ try: if not types or len(types) == 0: return {} cursor = connection.cursor() param_list = [int(time[0]), int(time[1]), uid] sql = 'SELECT vod_hls_id, tab_num 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 ' if types: sql += 'AND vhtt.type IN %s ' param_list.append(types) cursor.execute(sql, param_list) data_list = cursor.fetchall() cursor.close() # 执行完，关闭 connection.close() data_dict = {} for item in data_list: if item[1] not in data_dict: data_dict[item[1]] = [item[0]] else: data_dict[item[1]].append(item[0]) return data_dict except Exception as e: print('异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e))) return False @classmethod def query_tag_type_list(cls, vod_ids): """ 根据云存ID获取AI类型 @return: """ try: qs = VodHlsTag.objects.none() for vod_id in vod_ids: tab_val = int(vod_id[:2]) - 10 temp_id = vod_id[2:] qs = qs.union(VodHlsTag.objects.filter(vod_hls_id=temp_id, tab_num=tab_val).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 []