ASJServer/Service/VodHlsService.py

# @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(start_time, end_time, 'day')
            day_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:
                if week == 1:
                    kwargs = self.vod_query_param(week, **kwargs)
                    vod_hls_mon = VodHlsMon.objects.filter(**kwargs)
                elif week == 2:
                    kwargs = self.vod_query_param(week, **kwargs)
                    vod_hls_tus = VodHlsTues.objects.filter(**kwargs)
                elif week == 3:
                    kwargs = self.vod_query_param(week, **kwargs)
                    vod_hls_wed = VodHlsWed.objects.filter(**kwargs)
                elif week == 4:
                    kwargs = self.vod_query_param(week, **kwargs)
                    vod_hls_thur = VodHlsThur.objects.filter(**kwargs)
                elif week == 5:
                    kwargs = self.vod_query_param(week, **kwargs)
                    vod_hls_fri = VodHlsFri.objects.filter(**kwargs)
                elif week == 6:
                    kwargs = self.vod_query_param(week, **kwargs)
                    vod_hls_sat = VodHlsSat.objects.filter(**kwargs)
                elif week == 7:
                    kwargs = self.vod_query_param(week, **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
        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):
        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
        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, vod_id):
        """
        根据云存ID获取AI类型
        @return:
        """
        try:
            cursor = connection.cursor()
            sql = 'SELECT vhtt.type FROM '
            sql += 'vod_hls_tag vht INNER JOIN vod_hls_tag_type vhtt ON vht.id = vhtt.tag_id '
            sql += 'WHERE vht.vod_hls_id = %s  '

            cursor.execute(sql, [vod_id])
            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 []