# -*- 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 qs_page and len(qs_page) > 0: 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