ASJServer/Service/EquipmentInfoService.py

# -*- encoding: utf-8 -*-
"""
@File    : EquipmentInfoService.py
@Time    : 2022/4/14 17:28
@Author  : stephen
@Email   : zhangdongming@asj6.wecom.work
@Software: PyCharm
"""
import datetime
import time

from django.db.models import Value, CharField

from Model.models import EquipmentInfoMonday, EquipmentInfoTuesday, EquipmentInfoWednesday, EquipmentInfoThursday, \
    EquipmentInfoFriday, EquipmentInfoSaturday, EquipmentInfoSunday
from Object.utils import LocalDateTimeUtil

"""
设备分表查询Service    
因数据量不断增加，单表保留近七天数据进行分表优化设计
进行拆分为七张表星期一至星期天进行分表存储。分担单表存储读写压力。
"""


class EquipmentInfoService:

    @staticmethod
    def get_equipment_info_model(dt, val=0):

        """
        根据日期判断是星期几，返回相应的Model对象
        @param val: 1-7代表week
        @param dt: 日期 例：2022-03-03
        @return: 星期一至星期天equipment_info对象实例
        """
        week = 1
        if dt:
            week = LocalDateTimeUtil.date_to_week(dt)
        if 0 < val < 8:
            week = val
        equipment_info = None
        if week == 1:
            equipment_info = EquipmentInfoMonday.objects.all().annotate(tab_val=Value('1', output_field=CharField()))
        elif week == 2:
            equipment_info = EquipmentInfoTuesday.objects.all().annotate(tab_val=Value('2', output_field=CharField()))
        elif week == 3:
            equipment_info = EquipmentInfoWednesday.objects.all().annotate(tab_val=Value('3', output_field=CharField()))
        elif week == 4:
            equipment_info = EquipmentInfoThursday.objects.all().annotate(tab_val=Value('4', output_field=CharField()))
        elif week == 5:
            equipment_info = EquipmentInfoFriday.objects.all().annotate(tab_val=Value('5', output_field=CharField()))
        elif week == 6:
            equipment_info = EquipmentInfoSaturday.objects.all().annotate(tab_val=Value('6', output_field=CharField()))
        elif week == 7:
            equipment_info = EquipmentInfoSunday.objects.all().annotate(tab_val=Value('7', output_field=CharField()))
        return equipment_info

    @classmethod
    def find_by_start_time_equipment_info(cls, page, size, user_id, start_time, end_time, event_type,
                                          uid_list):
        """
        通过start_time查找指定日期当天设备消息推送
        @param page: 页数
        @param size: 每页条数
        @param user_id: 设备用户id
        @param start_time: 开始时间
        @param end_time: 结束时间
        @param event_type: 事件类型
        @param uid_list: 设备uid列表
        @return: result 查询结果
        """
        if start_time and end_time:
            start_date = datetime.datetime.fromtimestamp(int(start_time))
            # 根据开始日期，获取设备信息查询对象
            qs = EquipmentInfoService.get_equipment_info_model(str(start_date.date()), 0)
            # 调用查询方法
            qs = cls.query_equipment_info(qs, user_id, start_time, end_time, event_type,
                                          uid_list)
            # 时区问题
            week = LocalDateTimeUtil.date_to_week(str(start_date.date()))
            if week > 0:
                # 根据筛选日期 查找昨天数据
                yesterday = 7 if week == 1 else week - 1
                yesterday_info = EquipmentInfoService.get_equipment_info_model('', yesterday)
                yesterday_info = cls.query_equipment_info(yesterday_info, user_id, start_time, end_time, event_type,
                                                          uid_list)

                # 根据筛选日期 查找明天数据
                tomorrow = 1 if week == 7 else week + 1
                tomorrow_info = EquipmentInfoService.get_equipment_info_model('', tomorrow)
                tomorrow_info = cls.query_equipment_info(tomorrow_info, user_id, start_time, end_time, event_type,
                                                         uid_list)
                qs = qs.union(yesterday_info, tomorrow_info, all=True)
            if qs.exists():
                count = qs.count()
                qs_page = cls.get_equipment_info_page(qs, page, size)
                return qs_page, count
        return None, 0

    @classmethod
    def get_equipment_info_week_all(cls, page, size, user_id, start_time, end_time, event_type,
                                    uid_list):
        """
        分表查询近七天设备消息推送
        @param page: 页数
        @param size: 分页大小
        @param user_id: 设备用户id
        @param start_time: 事件开始时间
        @param end_time: 事件结束时间
        @param event_type: 事件类型
        @param uid_list: uid列表
        @return: qs_page, count 结果集
        """
        # 星期一设备信息查询
        monday_qs = EquipmentInfoService.get_equipment_info_model('', 1)
        monday_qs = cls.query_equipment_info(monday_qs, user_id, start_time, end_time, event_type,
                                             uid_list)
        # 星期二设备信息查询
        tuesday_qs = EquipmentInfoService.get_equipment_info_model('', 2)
        tuesday_qs = cls.query_equipment_info(tuesday_qs, user_id, start_time, end_time, event_type,
                                              uid_list)
        # 星期三设备信息查询
        wednesday_qs = EquipmentInfoService.get_equipment_info_model('', 3)
        wednesday_qs = cls.query_equipment_info(wednesday_qs, user_id, start_time, end_time, event_type,
                                                uid_list)
        # 星期四设备信息查询
        thursday_qs = EquipmentInfoService.get_equipment_info_model('', 4)
        thursday_qs = cls.query_equipment_info(thursday_qs, user_id, start_time, end_time, event_type,
                                               uid_list)
        # 星期五设备信息查询
        friday_qs = EquipmentInfoService.get_equipment_info_model('', 5)
        friday_qs = cls.query_equipment_info(friday_qs, user_id, start_time, end_time, event_type,
                                             uid_list)
        # 星期六设备信息查询
        saturday_qs = EquipmentInfoService.get_equipment_info_model('', 6)
        saturday_qs = cls.query_equipment_info(saturday_qs, user_id, start_time, end_time, event_type,
                                               uid_list)
        # 星期天设备信息查询
        sunday_qs = EquipmentInfoService.get_equipment_info_model('', 7)
        sunday_qs = cls.query_equipment_info(sunday_qs, user_id, start_time, end_time, event_type,
                                             uid_list)
        result = monday_qs.union(tuesday_qs, wednesday_qs, thursday_qs, friday_qs, saturday_qs, sunday_qs, all=True)
        count = result.count()
        qs_page = cls.get_equipment_info_page(result, page, size)
        return qs_page, count

    @classmethod
    def query_equipment_info(cls, qs, user_id, start_time, end_time, event_type,
                             uid_list):
        """
        设备信息条件查询，根据分表设计，默认条件event_time大于七天前时间
        @param qs: 设备信息查询对象
        @param user_id: 设备用户id
        @param start_time: 开始时间
        @param end_time: 结束时间
        @param event_type: 事件类型
        @param uid_list: 设备uid列表
        @return: result 设备信息结果集
        """
        now_time = int(time.time())
        # 获取七天前时间戳
        seven_days_before_time = LocalDateTimeUtil.get_before_days_timestamp(now_time, 7)
        # 默认查询当前表event_time大于七天前时间
        qs = qs.filter(event_time__gt=seven_days_before_time)
        if user_id:
            qs = qs.filter(device_user_id=user_id)
        if event_type:
            # 兼容AI查询
            if ',' in event_type:
                eventTypeList = event_type.split(',')
                eventTypeList = [int(i.strip()) for i in eventTypeList]
                qs = qs.filter(event_type__in=eventTypeList)
            else:
                qs = qs.filter(event_type=event_type)
        if start_time and end_time:
            qs = qs.filter(event_time__range=(start_time, end_time))
        if uid_list:
            uid_list = uid_list.split(',')
            qs = qs.filter(device_uid__in=uid_list)
        return qs

    @classmethod
    def get_equipment_info_page(cls, equipment_info_qs, page, size):
        """
        获取查询结果集进行排序、分页，遍历重命名字典key（主要针对原函数返回结果集）
        @param equipment_info_qs: 设备信息结果集
        @param page: 页数
        @param size: 分页大小
        @return: qs_page 遍历后的设备信息结果集
        """
        equipment_info_qs = equipment_info_qs.values('id', 'device_uid', 'device_nick_name', 'channel', 'event_type',
                                                     'status', 'alarm',
                                                     'event_time', 'receive_time', 'is_st', 'add_time',
                                                     'storage_location', 'border_coords', 'tab_val')

        equipment_info_qs = equipment_info_qs.order_by('-event_time')
        qs_page = equipment_info_qs[(page - 1) * size:page * size]
        if not qs_page or not qs_page.exists() or qs_page.count == 0:
            return qs_page
        for item in qs_page:
            # 星期表值
            tab_val = item['tab_val']
            # id = 星期表值+id
            item['id'] = int(tab_val + str(item['id']))
            item['devUid'] = item['device_uid']
            item['devNickName'] = item['device_nick_name']
            item['Channel'] = item['channel']
            item['eventType'] = item['event_type']
            item['eventTime'] = item['event_time']
            item['receiveTime'] = item['receive_time']
            item['addTime'] = item['add_time']
            item['borderCoords'] = item['border_coords']
            item.pop('device_uid')
            item.pop('device_nick_name')
            item.pop('channel')
            item.pop('event_type')
            item.pop('event_time')
            item.pop('receive_time')
            item.pop('add_time')
            item.pop('border_coords')
            item.pop('tab_val')
        return qs_page