EquipmentInfoService.py 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309
  1. # -*- encoding: utf-8 -*-
  2. """
  3. @File : EquipmentInfoService.py
  4. @Time : 2022/4/14 17:28
  5. @Author : stephen
  6. @Email : zhangdongming@asj6.wecom.work
  7. @Software: PyCharm
  8. """
  9. import datetime
  10. import logging
  11. import time
  12. import itertools
  13. from django.db.models import Value, CharField
  14. from Model.models import EquipmentInfoMonday, EquipmentInfoTuesday, EquipmentInfoWednesday, EquipmentInfoThursday, \
  15. EquipmentInfoFriday, EquipmentInfoSaturday, EquipmentInfoSunday
  16. from Object.utils import LocalDateTimeUtil
  17. """
  18. 设备分表查询Service
  19. 因数据量不断增加,单表保留近七天数据进行分表优化设计
  20. 进行拆分为七张表星期一至星期天进行分表存储。分担单表存储读写压力。
  21. """
  22. class EquipmentInfoService:
  23. @staticmethod
  24. def get_equipment_info_model(dt, val=0):
  25. """
  26. 根据日期判断是星期几,返回相应的Model对象
  27. @param val: 1-7代表week
  28. @param dt: 日期 例:2022-03-03
  29. @return: 星期一至星期天equipment_info对象实例
  30. """
  31. week = 1
  32. if dt:
  33. week = LocalDateTimeUtil.date_to_week(dt)
  34. if 0 < val < 8:
  35. week = val
  36. equipment_info = None
  37. if week == 1:
  38. equipment_info = EquipmentInfoMonday.objects.all().annotate(tab_val=Value('1', output_field=CharField()))
  39. elif week == 2:
  40. equipment_info = EquipmentInfoTuesday.objects.all().annotate(tab_val=Value('2', output_field=CharField()))
  41. elif week == 3:
  42. equipment_info = EquipmentInfoWednesday.objects.all().annotate(tab_val=Value('3', output_field=CharField()))
  43. elif week == 4:
  44. equipment_info = EquipmentInfoThursday.objects.all().annotate(tab_val=Value('4', output_field=CharField()))
  45. elif week == 5:
  46. equipment_info = EquipmentInfoFriday.objects.all().annotate(tab_val=Value('5', output_field=CharField()))
  47. elif week == 6:
  48. equipment_info = EquipmentInfoSaturday.objects.all().annotate(tab_val=Value('6', output_field=CharField()))
  49. elif week == 7:
  50. equipment_info = EquipmentInfoSunday.objects.all().annotate(tab_val=Value('7', output_field=CharField()))
  51. return equipment_info
  52. @classmethod
  53. def find_by_start_time_equipment_info(cls, page, size, user_id, start_time, end_time, event_type,
  54. uid_list):
  55. """
  56. 通过start_time查找指定日期当天设备消息推送
  57. @param page: 页数
  58. @param size: 每页条数
  59. @param user_id: 设备用户id
  60. @param start_time: 开始时间
  61. @param end_time: 结束时间
  62. @param event_type: 事件类型
  63. @param uid_list: 设备uid列表
  64. @return: result 查询结果
  65. """
  66. if start_time and end_time:
  67. start_date = datetime.datetime.fromtimestamp(int(start_time))
  68. # 根据开始日期,获取设备信息查询对象
  69. qs = EquipmentInfoService.get_equipment_info_model(str(start_date.date()), 0)
  70. # 调用查询方法
  71. qs = cls.query_equipment_info(qs, user_id, start_time, end_time, event_type,
  72. uid_list)
  73. # 时区问题
  74. week = LocalDateTimeUtil.date_to_week(str(start_date.date()))
  75. if week > 0:
  76. # 根据筛选日期 查找昨天数据
  77. yesterday = 7 if week == 1 else week - 1
  78. yesterday_info = EquipmentInfoService.get_equipment_info_model('', yesterday)
  79. yesterday_info = cls.query_equipment_info(yesterday_info, user_id, start_time, end_time, event_type,
  80. uid_list)
  81. # 根据筛选日期 查找明天数据
  82. tomorrow = 1 if week == 7 else week + 1
  83. tomorrow_info = EquipmentInfoService.get_equipment_info_model('', tomorrow)
  84. tomorrow_info = cls.query_equipment_info(tomorrow_info, user_id, start_time, end_time, event_type,
  85. uid_list)
  86. qs = qs.union(yesterday_info, tomorrow_info, all=True)
  87. if qs.exists():
  88. count = qs.count()
  89. qs_page = cls.get_equipment_info_page(qs, page, size)
  90. return qs_page, count
  91. return None, 0
  92. @classmethod
  93. def get_equipment_info_week_all(cls, page, size, user_id, start_time, end_time, event_type,
  94. uid_list):
  95. """
  96. 分表查询近七天设备消息推送
  97. @param page: 页数
  98. @param size: 分页大小
  99. @param user_id: 设备用户id
  100. @param start_time: 事件开始时间
  101. @param end_time: 事件结束时间
  102. @param event_type: 事件类型
  103. @param uid_list: uid列表
  104. @return: qs_page, count 结果集
  105. """
  106. # 星期一设备信息查询
  107. monday_qs = EquipmentInfoService.get_equipment_info_model('', 1)
  108. monday_qs = cls.query_equipment_info(monday_qs, user_id, start_time, end_time, event_type,
  109. uid_list)
  110. # 星期二设备信息查询
  111. tuesday_qs = EquipmentInfoService.get_equipment_info_model('', 2)
  112. tuesday_qs = cls.query_equipment_info(tuesday_qs, user_id, start_time, end_time, event_type,
  113. uid_list)
  114. # 星期三设备信息查询
  115. wednesday_qs = EquipmentInfoService.get_equipment_info_model('', 3)
  116. wednesday_qs = cls.query_equipment_info(wednesday_qs, user_id, start_time, end_time, event_type,
  117. uid_list)
  118. # 星期四设备信息查询
  119. thursday_qs = EquipmentInfoService.get_equipment_info_model('', 4)
  120. thursday_qs = cls.query_equipment_info(thursday_qs, user_id, start_time, end_time, event_type,
  121. uid_list)
  122. # 星期五设备信息查询
  123. friday_qs = EquipmentInfoService.get_equipment_info_model('', 5)
  124. friday_qs = cls.query_equipment_info(friday_qs, user_id, start_time, end_time, event_type,
  125. uid_list)
  126. # 星期六设备信息查询
  127. saturday_qs = EquipmentInfoService.get_equipment_info_model('', 6)
  128. saturday_qs = cls.query_equipment_info(saturday_qs, user_id, start_time, end_time, event_type,
  129. uid_list)
  130. # 星期天设备信息查询
  131. sunday_qs = EquipmentInfoService.get_equipment_info_model('', 7)
  132. sunday_qs = cls.query_equipment_info(sunday_qs, user_id, start_time, end_time, event_type,
  133. uid_list)
  134. result = monday_qs.union(tuesday_qs, wednesday_qs, thursday_qs, friday_qs, saturday_qs, sunday_qs, all=True)
  135. count = result.count()
  136. qs_page = cls.get_equipment_info_page(result, page, size)
  137. return qs_page, count
  138. @classmethod
  139. def query_equipment_info(cls, qs, user_id, start_time, end_time, event_type,
  140. uid_list):
  141. """
  142. 设备信息条件查询,根据分表设计,默认条件event_time大于七天前时间
  143. @param qs: 设备信息查询对象
  144. @param user_id: 设备用户id
  145. @param start_time: 开始时间
  146. @param end_time: 结束时间
  147. @param event_type: 事件类型
  148. @param uid_list: 设备uid列表
  149. @return: result 设备信息结果集
  150. """
  151. now_time = int(time.time())
  152. # 获取七天前时间戳
  153. seven_days_before_time = LocalDateTimeUtil.get_before_days_timestamp(now_time, 7)
  154. # 默认查询当前表event_time大于七天前时间
  155. if user_id:
  156. qs = qs.filter(device_user_id=user_id)
  157. if event_type:
  158. # 多类型查询
  159. if ',' in event_type:
  160. eventTypeList = cls.get_comb_event_type(event_type)
  161. qs = qs.filter(event_type__in=eventTypeList)
  162. else:
  163. qs = qs.filter(event_type=event_type)
  164. if start_time and end_time:
  165. qs = qs.filter(event_time__range=(int(start_time), int(end_time)))
  166. else:
  167. qs = qs.filter(event_time__range=(seven_days_before_time, now_time))
  168. if uid_list:
  169. uid_list = uid_list.split(',')
  170. qs = qs.filter(device_uid__in=uid_list)
  171. return qs
  172. @classmethod
  173. def get_equipment_info_page(cls, equipment_info_qs, page, size):
  174. """
  175. 获取查询结果集进行排序、分页,遍历重命名字典key(主要针对原函数返回结果集)
  176. @param equipment_info_qs: 设备信息结果集
  177. @param page: 页数
  178. @param size: 分页大小
  179. @return: qs_page 遍历后的设备信息结果集
  180. """
  181. equipment_info_qs = equipment_info_qs.values('id', 'device_uid', 'device_nick_name', 'channel', 'event_type',
  182. 'status', 'alarm',
  183. 'event_time', 'receive_time', 'is_st', 'add_time',
  184. 'storage_location', 'border_coords', 'tab_val')
  185. equipment_info_qs = equipment_info_qs.order_by('-event_time')
  186. qs_page = equipment_info_qs[(page - 1) * size:page * size]
  187. if not qs_page or not qs_page.exists() or qs_page.count == 0:
  188. return qs_page
  189. for item in qs_page:
  190. # 星期表值
  191. tab_val = item['tab_val']
  192. # id = 星期表值+id
  193. item['id'] = int(tab_val + str(item['id']))
  194. item['devUid'] = item['device_uid']
  195. item['devNickName'] = item['device_nick_name']
  196. item['Channel'] = item['channel']
  197. item['eventType'] = item['event_type']
  198. item['eventTime'] = item['event_time']
  199. item['receiveTime'] = item['receive_time']
  200. item['addTime'] = item['add_time']
  201. item['borderCoords'] = item['border_coords']
  202. item.pop('device_uid')
  203. item.pop('device_nick_name')
  204. item.pop('channel')
  205. item.pop('event_type')
  206. item.pop('event_time')
  207. item.pop('receive_time')
  208. item.pop('add_time')
  209. item.pop('border_coords')
  210. item.pop('tab_val')
  211. return qs_page
  212. @classmethod
  213. def get_comb_event_type(cls, event_type):
  214. """
  215. 重新组合ai消息类型查询,使其支持ai多标签查询
  216. @param event_type: 消息类型
  217. @return: event_type_list 消息类型数组
  218. """
  219. event_type_list = event_type.split(',')
  220. event_type_list = [int(i.strip()) for i in event_type_list]
  221. ai_event_type_list = []
  222. for key, val in enumerate(event_type_list):
  223. if val <= 4: # 分离出ai类型,以便后续组合ai标签,目前只存在4个ai类型1,2,3,4
  224. ai_event_type_list.append(val)
  225. del (event_type_list[key])
  226. if len(ai_event_type_list) < 1:
  227. return event_type_list
  228. ai_event_type_list.sort()
  229. type = [1, 2, 3, 4] # AI目前所有的标签,1人,2车,3宠物,4包裹,后续有新类型需要这里加, 后续会优化,存在表里,包裹存对应的aws标签
  230. comb_ai_event_type = []
  231. seen = set()
  232. for i in range(1, len(type) + 1): # 计算所有组合,如[1, 2, 3, 4], 4取1,4取2,4取3,4取4
  233. for s in itertools.combinations(type, i):
  234. if s not in seen: # 去除重复项, 如a=[1,2,3,4,4],会有两个[1,2,3,4,4],[1,2,3,4,4]的组合
  235. seen.add(s)
  236. s_list = list(s)
  237. for ai_event_type in ai_event_type_list:
  238. if ai_event_type in s_list: # 排除没有选择的标签组合
  239. if s_list not in comb_ai_event_type:
  240. s_list = [str(v) for v in s_list]
  241. comb_ai_event_type.append(s_list)
  242. regroup_list = []
  243. for val in comb_ai_event_type: # 组合ai类型组合,如[[2,3],[1,3]] -> [23, 13]
  244. val = ''.join(val)
  245. regroup_list.append(int(val))
  246. event_type_list = regroup_list + event_type_list # 加上普通移动消息类型
  247. return event_type_list
  248. @classmethod
  249. def get_all_comb_event_type(cls):
  250. """
  251. 计算ai消息类型全组合
  252. @return: event_type_list ai所有消息类型数组
  253. """
  254. type = [1, 2, 3, 4] # AI目前所有的标签,1人,2车,3宠物,4包裹,后续有新类型需要这里加, 后续会优化,存在表里,包裹存对应的aws标签
  255. comb_ai_event_type = []
  256. for i in range(1, len(type) + 1): # 计算所有组合,如[1, 2, 3, 4], 4取1,4取2,4取3,4取4
  257. for s in itertools.combinations(type, i):
  258. s_list = list(s)
  259. s_list = [str(v) for v in s_list]
  260. comb_ai_event_type.append(s_list)
  261. regroup_list = []
  262. for val in comb_ai_event_type: # 组合ai类型组合,如[[2,3],[1,3]] -> [23, 13]
  263. val = ''.join(val)
  264. regroup_list.append(int(val))
  265. return regroup_list
  266. @staticmethod
  267. def get_equipment_info_obj(dt, **kwargs):
  268. """
  269. 根据日期判断是星期几,返回相应的对象实例
  270. @param dt: 日期 例:2022-03-03
  271. @param kwargs: 设备信息属性值
  272. @return: 星期一至星期天equipment_info对象实例
  273. """
  274. logger = logging.getLogger('info')
  275. week = LocalDateTimeUtil.date_to_week(dt)
  276. logger.info('本周{}'.format(str(week)))
  277. equipment_info = None
  278. if week == 1:
  279. equipment_info = EquipmentInfoMonday(**kwargs)
  280. elif week == 2:
  281. equipment_info = EquipmentInfoTuesday(**kwargs)
  282. elif week == 3:
  283. equipment_info = EquipmentInfoWednesday(**kwargs)
  284. elif week == 4:
  285. equipment_info = EquipmentInfoThursday(**kwargs)
  286. elif week == 5:
  287. equipment_info = EquipmentInfoFriday(**kwargs)
  288. elif week == 6:
  289. equipment_info = EquipmentInfoSaturday(**kwargs)
  290. elif week == 7:
  291. equipment_info = EquipmentInfoSunday(**kwargs)
  292. logger.info(type(equipment_info))
  293. logger.info(equipment_info)
  294. return equipment_info