From 642e72cfc130d1871d959552d791ecab52493949 Mon Sep 17 00:00:00 2001
From: fuliqi <fuliqi@qq.com>
Date: 星期一, 09 十二月 2024 02:19:33 +0800
Subject: [PATCH] mysql online拆分
---
ycl-pojo/src/main/java/com/ycl/platform/domain/result/HK/SnapshotDataMonitorResult.java | 1
ycl-pojo/src/main/java/com/ycl/platform/domain/entity/YwPoint.java | 4 +
ycl-server/src/main/java/com/ycl/platform/service/UYErrorTypeCheckService.java | 2
ycl-server/src/main/java/com/ycl/task/ContractTask.java | 4
ycl-server/src/main/java/com/ycl/task/HKTask.java | 53 +++++++++++--
ycl-server/src/main/resources/mapper/zgyw/WorkOrderMapper.xml | 13 +++
ycl-pojo/src/main/java/com/ycl/platform/domain/vo/UpdateOnlineVO.java | 1
ycl-server/src/main/java/com/ycl/platform/mapper/TMonitorMapper.java | 2
ycl-server/src/main/java/com/ycl/platform/service/impl/UYErrorTypeCheckServiceImpl.java | 39 +++++++--
ycl-server/src/main/java/com/ycl/task/UYTask.java | 9 +
ycl-server/src/main/java/com/ycl/platform/mapper/WorkOrderMapper.java | 2
ycl-server/src/main/resources/mapper/zgyw/TMonitorMapper.xml | 66 ++++++++++------
12 files changed, 144 insertions(+), 52 deletions(-)
diff --git a/ycl-pojo/src/main/java/com/ycl/platform/domain/entity/YwPoint.java b/ycl-pojo/src/main/java/com/ycl/platform/domain/entity/YwPoint.java
index 9aeda0a..be892b8 100644
--- a/ycl-pojo/src/main/java/com/ycl/platform/domain/entity/YwPoint.java
+++ b/ycl-pojo/src/main/java/com/ycl/platform/domain/entity/YwPoint.java
@@ -87,6 +87,10 @@
@TableField("online")
private Integer online;
+ @ApiModelProperty("1鍦ㄧ嚎 0鏈煡 -1绂荤嚎")
+ @TableField("ping_online")
+ private Integer pingOnline;
+
@TableField("serial_number")
private String serialNumber;
diff --git a/ycl-pojo/src/main/java/com/ycl/platform/domain/result/HK/SnapshotDataMonitorResult.java b/ycl-pojo/src/main/java/com/ycl/platform/domain/result/HK/SnapshotDataMonitorResult.java
index 049c639..53e4f17 100644
--- a/ycl-pojo/src/main/java/com/ycl/platform/domain/result/HK/SnapshotDataMonitorResult.java
+++ b/ycl-pojo/src/main/java/com/ycl/platform/domain/result/HK/SnapshotDataMonitorResult.java
@@ -73,6 +73,7 @@
*/
private String resultTypeText;
+ private String ip;
public String getResultTypeText() {
if (Objects.equals(resultType, 1)) {
resultTypeText = "姝ｅ父";
diff --git a/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/UpdateOnlineVO.java b/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/UpdateOnlineVO.java
index 015c632..8f6345f 100644
--- a/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/UpdateOnlineVO.java
+++ b/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/UpdateOnlineVO.java
@@ -18,4 +18,5 @@
private Integer online;
private Date updateTime;
+
}
diff --git a/ycl-server/src/main/java/com/ycl/platform/mapper/TMonitorMapper.java b/ycl-server/src/main/java/com/ycl/platform/mapper/TMonitorMapper.java
index ecfd274..f1afa0a 100644
--- a/ycl-server/src/main/java/com/ycl/platform/mapper/TMonitorMapper.java
+++ b/ycl-server/src/main/java/com/ycl/platform/mapper/TMonitorMapper.java
@@ -167,7 +167,7 @@
* 鏍规嵁ip淇敼鐐逛綅琛ㄧ殑鏄惁鍦ㄧ嚎瀛楁
* @param onlineList
*/
- void updateOnline(@Param("onlineList") List<UpdateOnlineVO> onlineList);
+ void updateOnlineFromUyOrHk(@Param("onlineList") List<UpdateOnlineVO> onlineList);
/**
diff --git a/ycl-server/src/main/java/com/ycl/platform/mapper/WorkOrderMapper.java b/ycl-server/src/main/java/com/ycl/platform/mapper/WorkOrderMapper.java
index ddcdfab..c5c646f 100644
--- a/ycl-server/src/main/java/com/ycl/platform/mapper/WorkOrderMapper.java
+++ b/ycl-server/src/main/java/com/ycl/platform/mapper/WorkOrderMapper.java
@@ -115,4 +115,6 @@
List<WorkOrder> selectByNos(@Param("workOrderNos") List<String> workOrderNos);
+ List<String> getOfflineWorkOrder();
+
}
diff --git a/ycl-server/src/main/java/com/ycl/platform/service/UYErrorTypeCheckService.java b/ycl-server/src/main/java/com/ycl/platform/service/UYErrorTypeCheckService.java
index ce38103..d9071a2 100644
--- a/ycl-server/src/main/java/com/ycl/platform/service/UYErrorTypeCheckService.java
+++ b/ycl-server/src/main/java/com/ycl/platform/service/UYErrorTypeCheckService.java
@@ -1,5 +1,6 @@
package com.ycl.platform.service;
+import com.ycl.platform.domain.result.HK.SnapshotDataMonitorResult;
import com.ycl.platform.domain.result.UY.*;
import java.util.List;
@@ -44,6 +45,7 @@
// */
// void osdMonitorCheck(List<OsdMonitorResult> dataList);
+ void hkOnlineCheck(List<SnapshotDataMonitorResult> dataList);
/**
* 娴峰悍
*/
diff --git a/ycl-server/src/main/java/com/ycl/platform/service/impl/UYErrorTypeCheckServiceImpl.java b/ycl-server/src/main/java/com/ycl/platform/service/impl/UYErrorTypeCheckServiceImpl.java
index d238b64..11481c5 100644
--- a/ycl-server/src/main/java/com/ycl/platform/service/impl/UYErrorTypeCheckServiceImpl.java
+++ b/ycl-server/src/main/java/com/ycl/platform/service/impl/UYErrorTypeCheckServiceImpl.java
@@ -3,7 +3,9 @@
import com.ycl.platform.controller.YwThresholdController;
import com.ycl.platform.domain.entity.WorkOrder;
import com.ycl.platform.domain.entity.YwThreshold;
+import com.ycl.platform.domain.result.HK.SnapshotDataMonitorResult;
import com.ycl.platform.domain.result.UY.*;
+import com.ycl.platform.mapper.WorkOrderMapper;
import com.ycl.platform.service.IYwThresholdService;
import com.ycl.platform.service.UYErrorTypeCheckService;
import com.ycl.platform.service.WorkOrderService;
@@ -17,6 +19,7 @@
import enumeration.general.WorkOrderStatusEnum;
import lombok.Data;
import lombok.RequiredArgsConstructor;
+import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
@@ -37,6 +40,7 @@
private final WorkOrderService workOrderService;
private final IYwThresholdService ywThresholdService;
+ private final WorkOrderMapper workOrderMapper;
/**
* 鍥惧儚妫�娴嬬敓鎴愬伐鍗�
@@ -117,16 +121,31 @@
@Override
public void videoOnlineCheck(List<VideoOnlineResult> dataList) {
- List<WorkOrder> workOrderList = dataList.stream().map(item -> {
- WorkOrder workOrder = new WorkOrder();
- if (ApiConstants.UY_OnlineSite_Offline.equals(item.getStatus())) {
- this.genWorkOrder(workOrder, ErrorType.DEVICE_OFFLINE, item.getDeviceId());
- }
-// else if (0 == item.getStatus()) {
-// this.genWorkOrder(workOrder, ErrorType.UNKNOWN, item.getDeviceId());
-// }
- return workOrder;
- }).collect(Collectors.toList());
+ //鏌ヨ鏁版嵁搴撳凡瀛樺湪鐨勭绾垮伐鍗曡幏鍙杋p闆嗗悎锛屽墧闄�
+ List<String> ips = workOrderMapper.getOfflineWorkOrder();
+ List<WorkOrder> workOrderList = dataList.stream()
+ .filter(item -> CollectionUtils.isEmpty(ips) || !ips.contains(item.getIpAddr()))
+ .map(item -> {
+ WorkOrder workOrder = new WorkOrder();
+ if (ApiConstants.UY_OnlineSite_Offline.equals(item.getStatus())) {
+ this.genWorkOrder(workOrder, ErrorType.DEVICE_OFFLINE, item.getDeviceId());
+ }
+ return workOrder;
+ }).collect(Collectors.toList());
+ workOrderService.innerAddWorkOrder(workOrderList);
+ }
+
+ @Override
+ public void hkOnlineCheck(List<SnapshotDataMonitorResult> dataList) {
+ //鏌ヨ鏁版嵁搴撳凡瀛樺湪鐨勭绾垮伐鍗曡幏鍙杋p闆嗗悎锛屽墧闄�
+ List<String> ips = workOrderMapper.getOfflineWorkOrder();
+ List<WorkOrder> workOrderList = dataList.stream()
+ .filter(item -> CollectionUtils.isEmpty(ips) || !ips.contains(item.getIp()))
+ .map(item -> {
+ WorkOrder workOrder = new WorkOrder();
+ this.genWorkOrder(workOrder, ErrorType.DEVICE_OFFLINE, item.getExternalIndexCode());
+ return workOrder;
+ }).collect(Collectors.toList());
workOrderService.innerAddWorkOrder(workOrderList);
}
diff --git a/ycl-server/src/main/java/com/ycl/task/ContractTask.java b/ycl-server/src/main/java/com/ycl/task/ContractTask.java
index 60eb9b8..8886cef 100644
--- a/ycl-server/src/main/java/com/ycl/task/ContractTask.java
+++ b/ycl-server/src/main/java/com/ycl/task/ContractTask.java
@@ -80,7 +80,7 @@
.filter(calculateRuleVO -> ContractRule.CONTRACT_RULE_Online.getName().equals(calculateRuleVO.getRuleName()))
.collect(Collectors.toList());
List<Integer> unitIds = ruleVos.stream().map(CalculateRuleVO::getUnitId).collect(Collectors.toList());
- List<YwPoint> ywPoints = ywPointMapper.selectList(new QueryWrapper<YwPoint>().in("unit_id", unitIds));
+ List<YwPoint> ywPoints = ywPointMapper.selectList(new QueryWrapper<YwPoint>().in("unit_id", unitIds).eq("examine_status",1));
//key鏄痷nitId value鏄澶囩紪鐮侀泦鍚�
Map<Long, List<YwPoint>> unitMap = ywPoints.stream().filter(point -> point.getUnitId() != null)
.collect(Collectors.groupingBy(
@@ -99,7 +99,7 @@
if (!CollectionUtils.isEmpty(reportNumbers) && reportNumbers.contains(point.getSerialNumber()))
continue;
totalSite++;
- if (ApiConstants.UY_OnlineSite_Online.equals(point.getOnline())) {
+ if (ApiConstants.UY_OnlineSite_Online.equals(point.getOnline()) && ApiConstants.UY_OnlineSite_Online.equals(point.getPingOnline())) {
onlineSite++;
}
}
diff --git a/ycl-server/src/main/java/com/ycl/task/HKTask.java b/ycl-server/src/main/java/com/ycl/task/HKTask.java
index 10815fd..86ca9cd 100644
--- a/ycl-server/src/main/java/com/ycl/task/HKTask.java
+++ b/ycl-server/src/main/java/com/ycl/task/HKTask.java
@@ -1,10 +1,16 @@
package com.ycl.task;
import com.alibaba.fastjson2.JSONObject;
+import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.mongodb.client.result.DeleteResult;
import com.ycl.feign.HkApiUtil;
+import com.ycl.platform.domain.entity.TMonitor;
import com.ycl.platform.domain.param.HK.*;
import com.ycl.platform.domain.result.HK.*;
+import com.ycl.platform.domain.result.UY.VideoOnlineResult;
+import com.ycl.platform.domain.vo.UpdateOnlineVO;
+import com.ycl.platform.mapper.TMonitorMapper;
+import com.ycl.platform.mapper.WorkOrderMapper;
import com.ycl.platform.service.*;
import com.ycl.utils.DateUtils;
import constant.ApiConstants;
@@ -18,9 +24,9 @@
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
-import java.util.Date;
-import java.util.List;
-import java.util.Objects;
+import java.util.*;
+import java.util.function.Function;
+import java.util.stream.Collectors;
//娴峰悍瀵规帴鏁版嵁浠诲姟
@Slf4j
@@ -39,7 +45,10 @@
private IYwThresholdService ywThresholdService;
@Autowired
private YwPointService pointService;
-
+ @Autowired
+ private TMonitorMapper monitorMapper;
+ @Autowired
+ private UYErrorTypeCheckService uyErrorTypeCheckService;
//杞﹁締璁惧鍏ㄦ鎸囨爣鐩戞祴缁撴灉
public void vehicleDeviceInspectionTask() {
log.info("寮�濮嬫墽琛岃溅杈嗚澶囧叏妫�鎸囨爣鐩戞祴缁撴灉鏁版嵁鍚屾");
@@ -124,8 +133,6 @@
//瀛樻斁鍦╩ongo涓�
carList.forEach(item -> item.setDataType(ApiConstants.HK_DataType_CAR));
mongoTemplate.insertAll(carList);
- //TODO锛氭洿鏂皃oint琛ㄥ湪绾跨姸鎬侊紙鏈夋姄鎷嶉噺瑙嗕綔鍦ㄧ嚎锛�
- //宸ュ崟
}
/** 浜鸿劯鏁版嵁 */
@@ -151,10 +158,38 @@
//瀛樻斁鍦╩ongo涓�
faceList.forEach(item -> item.setDataType(ApiConstants.HK_DataType_FACE));
mongoTemplate.insertAll(faceList);
- //TODO:鏇存柊point琛ㄥ湪绾跨姸鎬侊紙鏈夋姄鎷嶉噺瑙嗕綔鍦ㄧ嚎锛�
- //宸ュ崟
}
-
+ //淇敼online鐘舵�佷互鍙婄敓鎴愬伐鍗�
+ List<SnapshotDataMonitorResult> totalResult =new ArrayList<>();
+ totalResult.addAll(carList);
+ totalResult.addAll(faceList);
+ if(!CollectionUtils.isEmpty(totalResult)) {
+ List<String> numbers = totalResult.stream().map(SnapshotDataMonitorResult::getExternalIndexCode).collect(Collectors.toList());
+ Map<String, TMonitor> map = monitorMapper.selectList(new QueryWrapper<TMonitor>().in("serial_number", numbers))
+ .stream().collect(Collectors.toMap(TMonitor::getSerialNumber, Function.identity()));
+ Date now = new Date();
+ totalResult.forEach(item->{
+ TMonitor monitor = map.get(item.getExternalIndexCode());
+ item.setIp(monitor.getIp());
+ });
+ List<UpdateOnlineVO> willUpdateList = totalResult.stream().map(item -> {
+ UpdateOnlineVO vo = new UpdateOnlineVO();
+ vo.setOnline(item.getDataCount()>0?1:-1);
+ vo.setIp(map.get(item.getExternalIndexCode()).getIp());
+ vo.setUpdateTime(now);
+ return vo;
+ }).collect(Collectors.toList());
+ monitorMapper.updateOnlineFromUyOrHk(willUpdateList);
+ //绂荤嚎鐢熸垚宸ュ崟,涓�涓猧p鍙敓鎴愪竴涓伐鍗�
+ List<SnapshotDataMonitorResult> workOrders = new ArrayList<>(totalResult.stream()
+ .filter(item -> ApiConstants.UY_OnlineSite_Offline.equals(item.getDataCount()>0?1:-1))
+ .collect(Collectors.toMap(
+ SnapshotDataMonitorResult::getIp,
+ Function.identity(),
+ (existing, replacement) -> existing // 濡傛灉閬囧埌鐩稿悓鐨� IP锛屼繚鐣欑涓�涓紙existing锛�
+ )).values());
+ uyErrorTypeCheckService.hkOnlineCheck(workOrders);
+ }
log.info("缁撴潫鎶撴媿鏁版嵁閲忔娴嬬粨鏋滄暟鎹悓姝�");
}
diff --git a/ycl-server/src/main/java/com/ycl/task/UYTask.java b/ycl-server/src/main/java/com/ycl/task/UYTask.java
index a0a9369..227626a 100644
--- a/ycl-server/src/main/java/com/ycl/task/UYTask.java
+++ b/ycl-server/src/main/java/com/ycl/task/UYTask.java
@@ -16,6 +16,7 @@
import com.ycl.platform.domain.result.UY.VideoOnlineResult;
import com.ycl.platform.domain.vo.UpdateOnlineVO;
import com.ycl.platform.mapper.TMonitorMapper;
+import com.ycl.platform.mapper.WorkOrderMapper;
import com.ycl.platform.service.UYErrorTypeCheckService;
import com.ycl.platform.service.WorkOrderService;
import com.ycl.platform.service.YwPointService;
@@ -59,6 +60,7 @@
private final YwPointService pointService;
private final TMonitorMapper monitorMapper;
private final WorkOrderService workOrderService;
+ private final WorkOrderMapper workOrderMapper;
private final SysConfigMapper sysConfigMapper;
private final CheckPointUtil checkPointUtil;
@Value("${youYun.tenantId}")
@@ -193,7 +195,6 @@
log.error("璇烽厤缃绾挎鏁帮紝姝ゆ璁剧疆涓洪粯璁ゅ��2");
}
// 鍏堟煡鍑鸿澶嘔P闆嗗悎,鍓旈櫎鎺夊湪绾挎儏鍐垫槸鏈煡鐨�,骞朵笖鍙娴嬫鍦ㄨ�冩牳鐨勮澶囬伩鍏嶅浣欏伐鍗�
- //TODO锛氶渶瑕佹妸娴峰悍鍜屼紭浜戠殑浠诲姟鐨勬椂闂磋皟鍒皃ing涔嬪墠
List<TMonitorResult> monitorList = monitorMapper.getDistinctIP();
//琛ュ厖閿欒鏃堕棿鐐�
Query onlineQuery = new Query(Criteria.where("mongoCreateTime").gte(DateUtils.getDayStart(new Date())).lt(DateUtils.getDayEnd(new Date())));
@@ -292,8 +293,10 @@
mongoTemplate.insertAll(mongoList);
}
//宸ュ崟锛堝悓涓�IP鍙敓鎴愪竴涓伐鍗曪級
+ //鏌ヨ鏁版嵁搴撳凡瀛樺湪鐨勭绾垮伐鍗曡幏鍙杋p闆嗗悎锛屽墧闄�
+ List<String> ips = workOrderMapper.getOfflineWorkOrder();
List<WorkOrder> workOrderList = dataList.stream()
- .filter(item -> Objects.nonNull(item.getWorkOrder()))
+ .filter(item -> Objects.nonNull(item.getWorkOrder()) && (CollectionUtils.isEmpty(ips) || !ips.contains(item.getIp())))
.collect(Collectors.toMap(
TMonitorResult::getIp,
Function.identity(),
@@ -350,7 +353,7 @@
vo.setUpdateTime(now);
return vo;
}).collect(Collectors.toList());
- monitorMapper.updateOnline(willUpdateList);
+ monitorMapper.updateOnlineFromUyOrHk(willUpdateList);
//绂荤嚎鐢熸垚宸ュ崟,涓�涓猧p鍙敓鎴愪竴涓伐鍗�
List<VideoOnlineResult> workOrders = new ArrayList<>(records.stream()
.filter(item -> ApiConstants.UY_OnlineSite_Offline.equals(item.getStatus()))
diff --git a/ycl-server/src/main/resources/mapper/zgyw/TMonitorMapper.xml b/ycl-server/src/main/resources/mapper/zgyw/TMonitorMapper.xml
index 3fd35dc..d522659 100644
--- a/ycl-server/src/main/resources/mapper/zgyw/TMonitorMapper.xml
+++ b/ycl-server/src/main/resources/mapper/zgyw/TMonitorMapper.xml
@@ -98,7 +98,12 @@
<select id="selectTMonitorList" resultType="com.ycl.platform.domain.vo.TMonitorVO">
select m.id, m.serial_number, name, site_type, mac_addr, ip, camera_fun_type, longitude, latitude,
- camera_capture_area, p.online as onState, civil_code, d.dept_id, d.dept_name, d.area, p.province_tag_video,p.province_tag_car,p.province_tag_face,p.dept_tag,
+ camera_capture_area,
+ CASE
+ WHEN p.online = 1 AND p.ping_online = 1 THEN 1
+ WHEN p.online = -1 OR p.ping_online = -1 THEN -1
+ ELSE 0
+ END AS onState, civil_code, d.dept_id, d.dept_name, d.area, p.province_tag_video,p.province_tag_car,p.province_tag_face,p.dept_tag,
IF(COUNT(w.id) OVER() > 0, '鏄�', '鍚�') AS error, u.unit_name,p.recovery_time,p.reason,p.start_time,p.end_time, w.work_order_no
from t_monitor m
left join t_yw_point p on m.serial_number = p.serial_number and p.deleted = 0
@@ -136,7 +141,9 @@
#{cameraCaptureArea}
</if>
<if test="time !=null and time !=''">and TIMESTAMPDIFF(DAY, p.recovery_time, NOW()) <![CDATA[<=]]> #{time}</if>
- <if test="onState != null ">and p.online = #{onState}</if>
+ <if test="onState != null and onState = 1">and (p.online = #{onState} and p.ping_online = #{onState})</if>
+ <if test="onState != null and onState = -1">and (p.online = #{onState} or p.ping_online = #{onState})</if>
+ <if test="onState != null and onState = 0">and (p.online = #{onState} and p.ping_online = #{onState})</if>
<if test="civilCode != null and civilCode != ''">and civil_code = #{civilCode}</if>
<if test="integratedDevice != null ">and integrated_device = #{integratedDevice}</if>
<if test="cameraBrand != null ">and camera_brand = #{cameraBrand}</if>
@@ -163,7 +170,12 @@
</select>
<select id="exportTMonitorList" resultType="com.ycl.platform.domain.excel.TMonitorExp">
- select m.id, m.serial_number, name, ip, camera_fun_type,p.online as onState,
+ select m.id, m.serial_number, name, ip, camera_fun_type,
+ CASE
+ WHEN p.online = 1 AND p.ping_online = 1 THEN 1
+ WHEN p.online = -1 OR p.ping_online = -1 THEN -1
+ ELSE 0
+ END AS onState,
d.dept_name, p.id as pointId,p.province_tag_video,p.province_tag_car,p.province_tag_face,p.dept_tag,p.important_tag,p.important_command_image_tag,u.unit_name as managementUnit
from t_monitor m
left join t_yw_point p on m.serial_number = p.serial_number and p.deleted = 0
@@ -185,7 +197,9 @@
<if test="cameraFunType != null and cameraFunType != ''">and camera_fun_type like concat('%',
#{cameraFunType}, '%')
</if>
- <if test="onState != null ">and p.online = #{onState}</if>
+ <if test="onState != null and onState = 1">and (p.online = #{onState} and p.ping_online = #{onState})</if>
+ <if test="onState != null and onState = -1">and (p.online = #{onState} or p.ping_online = #{onState})</if>
+ <if test="onState != null and onState = 0">and (p.online = #{onState} and p.ping_online = #{onState})</if>
<if test="civilCode != null and civilCode != ''">and civil_code = #{civilCode}</if>
</where>
</select>
@@ -377,10 +391,10 @@
</delete>
<select id="getVideoCount" resultType="java.util.Map">
SELECT count(*) AS totalPosts,
- IFNULL(SUM(IF(p.online = 1, 1, 0)), 0) AS totalMembers,
- IFNULL(SUM(IF(p.online = -1, 1, 0)), 0) AS postsPercentage,
- IFNULL(SUM(IF(p.online = 0, 1, 0)), 0) AS unknownNumbers,
- IFNULL(ROUND(SUM(IF(p.online = 1 , 1, 0)) / count(*) * 100, 2), 0) as viewsPercentage
+ IFNULL(SUM(IF((p.online = 1 and p.ping_online = 1), 1, 0)), 0) AS totalMembers,
+ IFNULL(SUM(IF((p.online = -1 or p.ping_online = -1), 1, 0)), 0) AS postsPercentage,
+ IFNULL(SUM(IF((p.online = 0 and p.ping_online = 0), 1, 0)), 0) AS unknownNumbers,
+ IFNULL(ROUND(SUM(IF((p.online = 1 and p.online= 1), 1, 0)) / count(*) * 100, 2), 0) as viewsPercentage
FROM t_monitor m
left join t_yw_point p on m.serial_number = p.serial_number
left join sys_dept d on p.dept_id = d.dept_id and d.del_flag = 0
@@ -404,10 +418,10 @@
<select id="recoveryException" resultType="java.util.Map">
SELECT count(*) AS totalPosts,
- IFNULL(SUM(IF(p.online = 1, 1, 0)), 0) AS totalMembers,
- IFNULL(SUM(IF(p.online = -1, 1, 0)), 0) AS postsPercentage,
- IFNULL(SUM(IF(p.online = 0, 1, 0)), 0) AS unknownNumbers,
- IFNULL(ROUND(SUM(IF(p.online = 1 , 1, 0)) / count(*) * 100, 2), 0) as viewsPercentage
+ IFNULL(SUM(IF((p.online = 1 and p.ping_online = 1), 1, 0)), 0) AS totalMembers,
+ IFNULL(SUM(IF((p.online = -1 or p.ping_online = -1), 1, 0)), 0) AS postsPercentage,
+ IFNULL(SUM(IF((p.online = 0 and p.ping_online = 0), 1, 0)), 0) AS unknownNumbers,
+ IFNULL(ROUND(SUM(IF((p.online = 1 and p.ping_online = 1), 1, 0)) / count(*) * 100, 2), 0) as viewsPercentage
FROM t_monitor m
left join t_yw_point p on m.serial_number = p.serial_number
left join sys_dept d on p.dept_id = d.dept_id and d.del_flag = 0
@@ -490,8 +504,8 @@
COUNT(p3.id) AS normalNum
FROM t_monitor
LEFT JOIN t_yw_point p1 ON t_monitor.serial_number = p1.serial_number AND p1.examine_status = 1<if test="dataScope == 1"> AND p1.province_tag_video = 1 </if><if test="dataScope == 3"> AND p1.dept_tag = 1 </if><if test="deptId != null"> AND p1.dept_id = #{deptId} </if>
- LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.examine_status = 1 AND p2.online = -1 AND p1.id = p2.id <if test="dataScope == 1"> AND p2.province_tag_video = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if><if test="deptId != null"> AND p2.dept_id = #{deptId} </if>
- LEFT JOIN t_yw_point p3 ON t_monitor.serial_number = p3.serial_number AND p3.examine_status = 1 AND p3.online = 1 AND p1.id = p3.id<if test="dataScope == 1"> AND p3.province_tag_video = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if><if test="deptId != null"> AND p3.dept_id = #{deptId} </if>
+ LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.examine_status = 1 AND (p2.online = -1 OR p2.ping_online = -1) AND p1.id = p2.id <if test="dataScope == 1"> AND p2.province_tag_video = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if><if test="deptId != null"> AND p2.dept_id = #{deptId} </if>
+ LEFT JOIN t_yw_point p3 ON t_monitor.serial_number = p3.serial_number AND p3.examine_status = 1 AND (p3.online = 1 AND p3.ping_online = 1 ) AND p1.id = p3.id<if test="dataScope == 1"> AND p3.province_tag_video = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if><if test="deptId != null"> AND p3.dept_id = #{deptId} </if>
WHERE INSTR(camera_fun_type, 1)
UNION ALL
SELECT
@@ -501,8 +515,8 @@
COUNT(p3.id) AS normalNum
FROM t_monitor
LEFT JOIN t_yw_point p1 ON t_monitor.serial_number = p1.serial_number AND p1.examine_status = 1 <if test="dataScope == 1"> AND p1.province_tag_car = 1 </if><if test="dataScope == 3"> AND p1.dept_tag = 1 </if><if test="deptId != null"> AND p1.dept_id = #{deptId} </if>
- LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.examine_status = 1 AND p2.online = -1 AND p1.id = p2.id <if test="dataScope == 1"> AND p2.province_tag_car = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if><if test="deptId != null"> AND p2.dept_id = #{deptId} </if>
- LEFT JOIN t_yw_point p3 ON t_monitor.serial_number = p3.serial_number AND p3.examine_status = 1 AND p3.online = 1 AND p1.id = p3.id <if test="dataScope == 1"> AND p3.province_tag_car = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if><if test="deptId != null"> AND p3.dept_id = #{deptId} </if>
+ LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.examine_status = 1 AND (p2.online = -1 OR p2.ping_online = -1) AND p1.id = p2.id <if test="dataScope == 1"> AND p2.province_tag_car = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if><if test="deptId != null"> AND p2.dept_id = #{deptId} </if>
+ LEFT JOIN t_yw_point p3 ON t_monitor.serial_number = p3.serial_number AND p3.examine_status = 1 AND (p3.online = 1 AND p3.ping_online = 1 ) AND p1.id = p3.id <if test="dataScope == 1"> AND p3.province_tag_car = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if><if test="deptId != null"> AND p3.dept_id = #{deptId} </if>
WHERE INSTR(camera_fun_type, 2)
UNION ALL
SELECT
@@ -512,8 +526,8 @@
COUNT(p3.id) AS normalNum
FROM t_monitor
LEFT JOIN t_yw_point p1 ON t_monitor.serial_number = p1.serial_number AND p1.examine_status = 1 <if test="dataScope == 1"> AND p1.province_tag_face = 1 </if><if test="dataScope == 3"> AND p1.dept_tag = 1 </if><if test="deptId != null"> AND p1.dept_id = #{deptId} </if>
- LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.examine_status = 1 AND p2.online = -1 AND p1.id = p2.id<if test="dataScope == 1"> AND p2.province_tag_face = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if><if test="deptId != null"> AND p2.dept_id = #{deptId} </if>
- LEFT JOIN t_yw_point p3 ON t_monitor.serial_number = p3.serial_number AND p3.examine_status = 1 AND p3.online = 1 AND p1.id = p3.id<if test="dataScope == 1"> AND p3.province_tag_face = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if><if test="deptId != null"> AND p3.dept_id = #{deptId} </if>
+ LEFT JOIN t_yw_point p2 ON t_monitor.serial_number = p2.serial_number AND p2.examine_status = 1 AND (p2.online = -1 OR p2.ping_online = -1) AND p1.id = p2.id<if test="dataScope == 1"> AND p2.province_tag_face = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if><if test="deptId != null"> AND p2.dept_id = #{deptId} </if>
+ LEFT JOIN t_yw_point p3 ON t_monitor.serial_number = p3.serial_number AND p3.examine_status = 1 AND (p3.online = 1 AND p3.ping_online = 1 ) AND p1.id = p3.id<if test="dataScope == 1"> AND p3.province_tag_face = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if><if test="deptId != null"> AND p3.dept_id = #{deptId} </if>
WHERE INSTR(camera_fun_type, 3)
</select>
<select id="monitorRate" resultType="com.ycl.platform.domain.vo.screen.MonitorRateVO">
@@ -530,8 +544,8 @@
FROM
sys_dept d
LEFT JOIN t_yw_point p ON p.dept_id = d.dept_id AND p.deleted = 0 AND p.examine_status = 1 <if test="dataScope == 1"> AND p.province_tag_face = 1 </if><if test="dataScope == 3"> AND p.dept_tag = 1 </if>
- LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND p2.online = -1 AND p2.examine_status = 1 AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag_face = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if>
- LEFT JOIN t_yw_point p3 ON p3.dept_id = d.dept_id AND p3.deleted = 0 AND p3.online = 1 AND p3.examine_status = 1 AND p.id = p3.id <if test="dataScope == 1"> AND p3.province_tag_face = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if>
+ LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND (p2.online = -1 OR p2.ping_online = -1) AND p2.examine_status = 1 AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag_face = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if>
+ LEFT JOIN t_yw_point p3 ON p3.dept_id = d.dept_id AND p3.deleted = 0 AND (p3.online = 1 AND p3.ping_online = 1 ) AND p.id = p3.id <if test="dataScope == 1"> AND p3.province_tag_face = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if>
LEFT JOIN t_monitor m ON m.serial_number = p.serial_number AND INSTR(m.camera_fun_type, 3)
LEFT JOIN t_monitor m2 ON m2.serial_number = p2.serial_number AND INSTR(m2.camera_fun_type, 3)
LEFT JOIN t_monitor m3 ON m3.serial_number = p3.serial_number AND INSTR(m3.camera_fun_type, 3)
@@ -550,8 +564,8 @@
FROM
sys_dept d
LEFT JOIN t_yw_point p ON p.dept_id = d.dept_id AND p.deleted = 0 AND p.examine_status = 1 <if test="dataScope == 1"> AND p.province_tag_car = 1 </if><if test="dataScope == 3"> AND p.dept_tag = 1 </if>
- LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND p2.examine_status = 1 AND p2.online = -1 AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag_car = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if>
- LEFT JOIN t_yw_point p3 ON p3.dept_id = d.dept_id AND p3.deleted = 0 AND p3.examine_status = 1 AND p3.online = 1 AND p.id = p3.id <if test="dataScope == 1"> AND p3.province_tag_car = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if>
+ LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND p2.examine_status = 1 AND (p2.online = -1 OR p2.ping_online = -1) AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag_car = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if>
+ LEFT JOIN t_yw_point p3 ON p3.dept_id = d.dept_id AND p3.deleted = 0 AND p3.examine_status = 1 AND (p3.online = 1 AND p3.ping_online = 1 ) AND p.id = p3.id <if test="dataScope == 1"> AND p3.province_tag_car = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if>
LEFT JOIN t_monitor m ON m.serial_number = p.serial_number AND INSTR(m.camera_fun_type, 2)
LEFT JOIN t_monitor m2 ON m2.serial_number = p2.serial_number AND INSTR(m2.camera_fun_type, 2)
LEFT JOIN t_monitor m3 ON m3.serial_number = p3.serial_number AND INSTR(m3.camera_fun_type, 2)
@@ -570,8 +584,8 @@
FROM
sys_dept d
LEFT JOIN t_yw_point p ON p.dept_id = d.dept_id AND p.deleted = 0 AND p.examine_status = 1 <if test="dataScope == 1"> AND p.province_tag_video = 1 </if><if test="dataScope == 3"> AND p.dept_tag = 1 </if>
- LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND p2.examine_status = 1 AND p2.online = -1 AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag_video = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if>
- LEFT JOIN t_yw_point p3 ON p3.dept_id = d.dept_id AND p3.deleted = 0 AND p3.examine_status = 1 AND p3.online = 1 AND p.id = p3.id <if test="dataScope == 1"> AND p3.province_tag_video = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if>
+ LEFT JOIN t_yw_point p2 ON p2.dept_id = d.dept_id AND p2.deleted = 0 AND p2.examine_status = 1 AND (p2.online = -1 OR p2.ping_online = -1) AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag_video = 1 </if><if test="dataScope == 3"> AND p2.dept_tag = 1 </if>
+ LEFT JOIN t_yw_point p3 ON p3.dept_id = d.dept_id AND p3.deleted = 0 AND p3.examine_status = 1 AND (p3.online = 1 AND p3.ping_online = 1 ) AND p.id = p3.id <if test="dataScope == 1"> AND p3.province_tag_video = 1 </if><if test="dataScope == 3"> AND p3.dept_tag = 1 </if>
LEFT JOIN t_monitor m ON m.serial_number = p.serial_number AND INSTR(m.camera_fun_type, 1)
LEFT JOIN t_monitor m2 ON m2.serial_number = p2.serial_number AND INSTR(m2.camera_fun_type, 1)
LEFT JOIN t_monitor m3 ON m3.serial_number = p3.serial_number AND INSTR(m3.camera_fun_type, 1)
@@ -650,7 +664,7 @@
where p.online != 0 and p.examine_status = 1
</select>
- <update id="updateOnline">
+ <update id="updateOnlineFromUyOrHk">
<foreach collection="onlineList" item="online" separator=";">
UPDATE
t_yw_point
@@ -668,7 +682,7 @@
t_yw_point p
LEFT JOIN t_monitor m on p.serial_number = m.serial_number
SET
- p.online = #{online},
+ p.ping_online = #{online},
p.update_time = #{date}
WHERE m.ip in
<foreach collection="ipList" open="(" close=")" separator="," item="ip">
diff --git a/ycl-server/src/main/resources/mapper/zgyw/WorkOrderMapper.xml b/ycl-server/src/main/resources/mapper/zgyw/WorkOrderMapper.xml
index 4eb03d6..3a71794 100644
--- a/ycl-server/src/main/resources/mapper/zgyw/WorkOrderMapper.xml
+++ b/ycl-server/src/main/resources/mapper/zgyw/WorkOrderMapper.xml
@@ -209,7 +209,18 @@
su.nick_name
ORDER BY wo.create_time DESC
</select>
-
+ <select id="getOfflineWorkOrder" resultType="java.lang.String">
+ select tm.ip
+ FROM
+ t_work_order wo
+ INNER JOIN t_monitor tm ON wo.serial_number = tm.serial_number
+ where (EXISTS (
+ SELECT 1
+ FROM t_work_order_error_type twoet
+ WHERE twoet.work_order_no = wo.work_order_no
+ AND twoet.error_name = 'DEVICEOFFLINE'
+ ))
+ </select>
<select id="handlingWorkOrderList" resultType="com.ycl.platform.domain.vo.YwPointJobVO">
SELECT
wo.id,
--
Gitblit v1.8.0