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 = "姝e父"; 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