ASJServer/Service/VodHlsService.py

# @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
        """
        vod_hls_union = VodHls01.objects.none().annotate(tab_val=Value('11', output_field=CharField()))
        try:
            if kwargs['type_list']:
                kwargs = self.vod_query_param(**kwargs)
                if 'id__in' in kwargs:
                    for item in kwargs['id__in']:
                        tab_val = str(item['tab_val'] + 10)
                        vod_hls_union = vod_hls_union.union(
                            Vod_Hls_List[item['tab_val'] - 1].objects.filter(id=item['vod_id']).annotate(
                                tab_val=Value(tab_val, output_field=CharField())))
            else:
                kwargs.pop('type_list')
                for index, vod_hls_model in enumerate(Vod_Hls_List):
                    tab_val = str(index + 11)
                    vod_hls_union = vod_hls_union.union(vod_hls_model.objects.filter(**kwargs).annotate(
                        tab_val=Value(tab_val, output_field=CharField())))
        except Exception as e:
            LOGGER.info('get_vod_hls_data异常详情,kwargs:{},errLine:{},errMsg:{}'.format(kwargs, e.__traceback__.tb_lineno, repr(e)))
        LOGGER.info('vod_hls_union结果：{},kwargs:{}'.format(vod_hls_union, 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):
        types = kwargs.get('type_list')
        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_list = [{'vod_id': val[0], 'tab_val': val[1]} 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, 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 []