From e4c69bcd4ba18dd022dd4ed86da96c5e4b8b3150 Mon Sep 17 00:00:00 2001 From: fuliqi <fuliqi@qq.com> Date: 星期一, 14 十月 2024 02:13:44 +0800 Subject: [PATCH] 动态列导出编辑完成 --- ycl-pojo/src/main/java/com/ycl/platform/domain/entity/YwPoint.java | 4 ycl-server/src/main/resources/mapper/zgyw/DynamicColumnMapper.xml | 20 ++ ycl-server/src/main/java/com/ycl/task/HKTask.java | 3 ycl-pojo/src/main/java/com/ycl/platform/domain/vo/UpdateOnlineVO.java | 2 ycl-server/src/main/java/com/ycl/platform/mapper/DynamicColumnMapper.java | 5 ycl-common/src/main/java/constant/PointHeaderConstant.java | 12 + ycl-server/src/main/java/com/ycl/utils/CheckPointUtil.java | 3 ycl-server/src/main/java/com/ycl/thread/OnlineCheckThread.java | 1 ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java | 4 ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml | 8 ycl-pojo/src/main/java/com/ycl/platform/domain/entity/DynamicColumnValue.java | 41 +++++ ycl-server/src/main/java/com/ycl/platform/service/impl/UYErrorTypeCheckServiceImpl.java | 7 ycl-server/src/main/java/com/ycl/task/MonitorTask.java | 26 ++- ycl-server/src/main/java/com/ycl/task/UYTask.java | 44 +++--- ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java | 4 ycl-pojo/src/main/java/com/ycl/platform/domain/vo/DynamicColumnVO.java | 12 + ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java | 143 ++++++++++++++++---- ycl-server/src/main/resources/mapper/zgyw/TMonitorMapper.xml | 47 ++++-- ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java | 8 + 19 files changed, 291 insertions(+), 103 deletions(-) diff --git a/ycl-common/src/main/java/constant/PointHeaderConstant.java b/ycl-common/src/main/java/constant/PointHeaderConstant.java index f8d1ae9..2fa0b5b 100644 --- a/ycl-common/src/main/java/constant/PointHeaderConstant.java +++ b/ycl-common/src/main/java/constant/PointHeaderConstant.java @@ -1,13 +1,23 @@ package constant; + +import java.util.Arrays; +import java.util.HashSet; +import java.util.Set; + /** * 鐐逛綅瀵煎嚭琛ㄥご甯搁噺 */ public class PointHeaderConstant { - + public final static String ID = "id"; public final static String Point_Name = "鐐逛綅鍚嶇О(鍙慨鏀�)"; public final static String Serial_Number = "鍥芥爣鐮�(涓嶅彲淇敼)"; public final static String IP = "鐐逛綅IP(鍙慨鏀�)"; public final static String Province_Tag = "鏄惁鐪佸巺鏍囩(鍙慨鏀�)"; public final static String Impotrant_Command_Image = "鏄惁閲嶇偣鎸囨尌鍥惧儚(鍙慨鏀�)"; public final static String Dept_Tag = "鏄惁閮ㄧ骇鏍囩(鍙慨鏀�)"; + + // 鍙互灏嗚繖浜涘凡鐭ョ殑閿斁鍏ヤ竴涓泦鍚堜腑锛屾柟渚挎鏌� + public static final Set<String> KNOWN_KEYS = new HashSet<>(Arrays.asList( + ID,Point_Name, IP, Serial_Number, Province_Tag, Impotrant_Command_Image, Dept_Tag + )); } diff --git a/ycl-pojo/src/main/java/com/ycl/platform/domain/entity/DynamicColumnValue.java b/ycl-pojo/src/main/java/com/ycl/platform/domain/entity/DynamicColumnValue.java new file mode 100644 index 0000000..3f68e65 --- /dev/null +++ b/ycl-pojo/src/main/java/com/ycl/platform/domain/entity/DynamicColumnValue.java @@ -0,0 +1,41 @@ +package com.ycl.platform.domain.entity; + +import com.baomidou.mybatisplus.annotation.IdType; +import com.baomidou.mybatisplus.annotation.TableField; +import com.baomidou.mybatisplus.annotation.TableId; +import com.baomidou.mybatisplus.annotation.TableName; +import lombok.Data; + +import java.util.Date; + +/** + * 鍔ㄦ�佸垪 + * + * @author xp + * @since 2024-08-16 + */ +@Data +@TableName("t_dynamic_column_value") +public class DynamicColumnValue { + + private static final long serialVersionUID = 1L; + + @TableId(value = "id", type = IdType.AUTO) + private Integer id; + + @TableField("dynamic_column_id") + /** 鍏宠仈鍔ㄦ�佸垪ID */ + private Integer dynamicColumnId; + + @TableField("column_value") + /** elementui table prop */ + private String columnValue; + + @TableField("ref_id") + /** 鍏宠仈鐨勬暟鎹甶d */ + private Integer refId; + + + + +} 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 6305d1a..09fbe11 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 @@ -75,9 +75,9 @@ @TableField("dept_tag") private Boolean deptTag; - @ApiModelProperty("鏄惁鍦ㄧ嚎锛歵rue 鍦ㄧ嚎") + @ApiModelProperty("1鍦ㄧ嚎 0鏈煡 -1绂荤嚎") @TableField("online") - private Boolean online; + private Integer online; @TableField("serial_number") private String serialNumber; diff --git a/ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java b/ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java index 1e982be..6105dfe 100644 --- a/ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java +++ b/ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java @@ -58,9 +58,11 @@ @ColumnWidth(20) @ExcelProperty("鏄惁閮ㄧ骇鏍囩(鍙慨鏀�)") - private String deptTag; + private String deptTagString; @ExcelIgnore + private Boolean deptTag; + @ExcelIgnore private Boolean importantTag; @ExcelProperty("鏄惁閲嶇偣鐐逛綅") diff --git a/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/DynamicColumnVO.java b/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/DynamicColumnVO.java index cb4aee4..b40dcaa 100644 --- a/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/DynamicColumnVO.java +++ b/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/DynamicColumnVO.java @@ -14,19 +14,21 @@ */ @Data public class DynamicColumnVO extends AbsVo { - - /** 鍏宠仈涓�鏈轰竴妗d */ + /** 琛ㄥ悕 */ private String tableName; /** elementui table prop */ private String propName; - /** elementui table label */ + /** elementui 瀛楁鍚� */ private String labelValue; - /** 鍊� */ + /** 瀛楁鍊� */ private String columnValue; - + /** 鍏宠仈鏁版嵁鐨刬d */ + private Integer refId; + /** value琛ㄧ殑id */ + private Integer valueId; public static DynamicColumnVO getVoByEntity(@NonNull DynamicColumn entity, DynamicColumnVO vo) { if(vo == null) { vo = new DynamicColumnVO(); 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 ddbacf8..015c632 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 @@ -15,7 +15,7 @@ private String ip; /** 鏄惁鍦ㄧ嚎 */ - private Boolean online; + private Integer online; private Date updateTime; } diff --git a/ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java b/ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java index 5293a90..5cbd808 100644 --- a/ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java +++ b/ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java @@ -16,6 +16,8 @@ import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; +import java.util.concurrent.ExecutionException; + import org.springframework.validation.annotation.Validated; import jakarta.validation.constraints.NotEmpty; import io.swagger.annotations.Api; @@ -124,7 +126,7 @@ Integer unitId, String startTime, Boolean needUpdateUnit, - String endTime) throws IOException, ParseException { + String endTime) throws IOException, ParseException, ExecutionException, InterruptedException { Date start = null; Date end = null; if (-1 == unitId) { diff --git a/ycl-server/src/main/java/com/ycl/platform/mapper/DynamicColumnMapper.java b/ycl-server/src/main/java/com/ycl/platform/mapper/DynamicColumnMapper.java index 404c43d..e3cd1e5 100644 --- a/ycl-server/src/main/java/com/ycl/platform/mapper/DynamicColumnMapper.java +++ b/ycl-server/src/main/java/com/ycl/platform/mapper/DynamicColumnMapper.java @@ -3,6 +3,7 @@ import com.ycl.platform.domain.entity.DynamicColumn; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.core.mapper.BaseMapper; +import com.ycl.platform.domain.entity.DynamicColumnValue; import com.ycl.platform.domain.query.DynamicColumnQuery; import com.ycl.platform.domain.vo.DynamicColumnVO; import com.ycl.platform.domain.form.DynamicColumnForm; @@ -39,4 +40,8 @@ */ IPage getPage(IPage page, @Param("query") DynamicColumnQuery query); + void saveBatch( @Param("insertList") List<DynamicColumnValue> insertList); + + void updateBatch( @Param("updateList") List<DynamicColumnValue> updateList); + } diff --git a/ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java b/ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java index b3c786f..803e4d6 100644 --- a/ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java +++ b/ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java @@ -3,6 +3,7 @@ import com.baomidou.mybatisplus.extension.service.IService; import com.ycl.platform.domain.entity.ReportAuditingRecord; import com.ycl.platform.domain.entity.YwPoint; +import com.ycl.platform.domain.excel.PointExport; import com.ycl.platform.domain.form.BatchEditPointForm; import com.ycl.platform.domain.form.YwPointForm; import com.ycl.platform.domain.query.YwPointQuery; @@ -16,6 +17,7 @@ import java.io.IOException; import java.util.Date; import java.util.List; +import java.util.concurrent.ExecutionException; /** * 杩愮淮鐐逛綅 鏈嶅姟绫� @@ -125,7 +127,7 @@ * @param unitId 杩愮淮鍗曚綅id * @return */ - Result importData(MultipartFile file, Integer unitId, Date startTime, Date endTime, Boolean needUpdateUnit) throws IOException; + Result importData(MultipartFile file, Integer unitId, Date startTime, Date endTime, Boolean needUpdateUnit) throws IOException, ExecutionException, InterruptedException; /** * 鑾峰彇鎶ュ鏄惁瀹℃牳閫氳繃锛堟渶鏂颁竴鏉★級 @@ -149,4 +151,8 @@ * @return */ <T extends BaseResult> void setNew(List<T> dataList); + + void updatePoint(List<PointExport> dataList, Integer unitId, Date startTime, Date endTime, Boolean needUpdateUnit) throws ExecutionException, InterruptedException; + + void updateDynamic(List<PointExport> 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 332a429..6b09c17 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 @@ -106,11 +106,12 @@ public void videoOnlineCheck(List<VideoOnlineResult> dataList) { List<WorkOrder> workOrderList = dataList.stream().map(item -> { WorkOrder workOrder = new WorkOrder(); - if (-1 == item.getStatus()) { + 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()); } +// else if (0 == item.getStatus()) { +// this.genWorkOrder(workOrder, ErrorType.UNKNOWN, item.getDeviceId()); +// } return workOrder; }).collect(Collectors.toList()); workOrderService.innerAddWorkOrder(workOrderList); diff --git a/ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java b/ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java index 15e8456..646081a 100644 --- a/ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java +++ b/ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java @@ -9,9 +9,7 @@ import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.ycl.dataListener.CurrencyDataListener; import com.ycl.platform.base.AbsVo; -import com.ycl.platform.domain.entity.ReportAuditingRecord; -import com.ycl.platform.domain.entity.YwPeople; -import com.ycl.platform.domain.entity.YwPoint; +import com.ycl.platform.domain.entity.*; import com.ycl.platform.domain.excel.PointExport; import com.ycl.platform.domain.form.BatchEditPointForm; import com.ycl.platform.domain.form.YwPointForm; @@ -25,6 +23,7 @@ import com.ycl.platform.mapper.TMonitorMapper; import com.ycl.platform.mapper.YwPeopleMapper; import com.ycl.platform.mapper.YwPointMapper; +import com.ycl.platform.service.DynamicColumnService; import com.ycl.platform.service.YwPointService; import com.ycl.platform.service.YwUnitService; import com.ycl.system.Result; @@ -34,11 +33,13 @@ import com.ycl.utils.DateUtils; import com.ycl.utils.SecurityUtils; import com.ycl.utils.poi.EasyExcelImportUtils; +import com.ycl.utils.uuid.IdUtils; import constant.PointHeaderConstant; import jakarta.servlet.http.HttpServletResponse; import lombok.RequiredArgsConstructor; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.BeanUtils; +import org.springframework.context.ApplicationContext; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.Assert; @@ -67,6 +68,7 @@ private final SysDeptMapper sysDeptMapper; private final TMonitorMapper monitorMapper; private final DynamicColumnMapper dynamicColumnMapper; + private final ApplicationContext context; /** * 娣诲姞 @@ -250,10 +252,11 @@ List<DynamicColumnVO> dynamics = dynamicColumnMapper.getData(); Map<Integer, List<DynamicColumnVO>> dynamicMap = new HashMap<>(); if (!CollectionUtils.isEmpty(dynamics)) { - dynamicMap = dynamics.stream().filter(vo ->vo.getId()!=null).collect(Collectors.groupingBy(AbsVo::getId)); + dynamicMap = dynamics.stream().filter(vo -> vo.getRefId() != null).collect(Collectors.groupingBy(DynamicColumnVO::getRefId)); } //瀹氫箟琛ㄥご鍒楄〃 LinkedHashSet<String> headers = new LinkedHashSet<>(); + headers.add(PointHeaderConstant.ID); headers.add(PointHeaderConstant.Point_Name); headers.add(PointHeaderConstant.Serial_Number); headers.add(PointHeaderConstant.IP); @@ -271,12 +274,13 @@ for (PointExport export : exportData) { List<Object> list = new ArrayList<>(); //闇�瑕佷繚璇侀『搴忎竴鑷� + list.add(export.getId()); list.add(export.getPointName()); list.add(export.getSerialNumber()); list.add(export.getPointIP()); list.add(export.getProvinceTagString()); list.add(export.getImportantCommandImageTagString()); - list.add(export.getDeptTag()); + list.add(export.getDeptTagString()); for (String header : headersList) { //鍒涘缓涓�涓彉閲忚褰曟壘娌℃壘鍒拌繖涓猦eader boolean flag = false; @@ -308,27 +312,50 @@ @Override + @Transactional(rollbackFor = Exception.class) public Result importData(MultipartFile file, Integer unitId, Date startTime, Date endTime, Boolean - needUpdateUnit) throws IOException { -// List<Map<String,String>> list = EasyExcelImportUtils.makeData(file); - Consumer<List<PointExport>> consumer = (dataList) -> { - try { - this.updatePoint(dataList, unitId, startTime, endTime, needUpdateUnit); - } catch (ExecutionException e) { - e.printStackTrace(); - } catch (InterruptedException e) { - e.printStackTrace(); + needUpdateUnit) throws IOException, ExecutionException, InterruptedException { + List<Map<String, String>> list = EasyExcelImportUtils.makeData(file); + List<PointExport> dataList = new ArrayList<>(); +// Consumer<List<PointExport>> consumer = (dataList) -> { +// try { +// this.updatePoint(dataList, unitId, startTime, endTime, needUpdateUnit); +// } catch (ExecutionException e) { +// e.printStackTrace(); +// } catch (InterruptedException e) { +// e.printStackTrace(); +// } +// }; +// EasyExcel.read(file.getInputStream(), PointExport.class, new CurrencyDataListener(consumer)).headRowNumber(1).doReadAll(); + for (Map<String, String> map : list) { + PointExport pointExport = new PointExport(); + pointExport.setId(Integer.valueOf(map.get(PointHeaderConstant.ID))); + pointExport.setPointName(map.get(PointHeaderConstant.Point_Name)); + pointExport.setPointIP(map.get(PointHeaderConstant.IP)); + pointExport.setSerialNumber(map.get(PointHeaderConstant.Serial_Number)); + pointExport.setProvinceTag("鏄�".equals(map.get(PointHeaderConstant.Province_Tag))); + pointExport.setImportantCommandImageTag("鏄�".equals(map.get(PointHeaderConstant.Impotrant_Command_Image))); + pointExport.setDeptTag("鏄�".equals(map.get(PointHeaderConstant.Dept_Tag))); + List<DynamicColumnVO> dynamics = new ArrayList<>(); + // 澶勭悊鍔ㄦ�佸垪 + for (Map.Entry<String, String> entry : map.entrySet()) { + String key = entry.getKey(); + if (!PointHeaderConstant.KNOWN_KEYS.contains(key)) { + DynamicColumnVO dynamicColumnVO = new DynamicColumnVO(); + dynamicColumnVO.setRefId(pointExport.getId()); + dynamicColumnVO.setLabelValue(entry.getKey()); + dynamicColumnVO.setColumnValue(entry.getValue()); + dynamicColumnVO.setPropName(IdUtils.randomNO(new Date())); + dynamicColumnVO.setTableName("t_yw_point"); + dynamics.add(dynamicColumnVO); + } } - }; - EasyExcel.read(file.getInputStream(), PointExport.class, new CurrencyDataListener(consumer)).headRowNumber(1).doReadAll(); -// for (Map<String, String> map : list) { -// PointExport pointExport = new PointExport(); -// pointExport.setPointName(map.get(PointHeaderConstant.Point_Name)); -// pointExport.setPointIP(map.get(PointHeaderConstant.IP)); -// pointExport.setSerialNumber(map.get(PointHeaderConstant.Serial_Number)); -// } -// this.updatePoint(dataList, unitId, startTime, endTime, needUpdateUnit); -// System.out.println(list); + pointExport.setDynamicData(dynamics); + dataList.add(pointExport); + } + YwPointServiceImpl self = context.getBean(YwPointServiceImpl.class); + self.updatePoint(dataList, unitId, startTime, endTime, needUpdateUnit); + self.updateDynamic(dataList); return Result.ok(); } @@ -343,7 +370,6 @@ * @param dataList * @param unitId */ - @Transactional(rollbackFor = Exception.class) public void updatePoint(List<PointExport> dataList, Integer unitId, Date startTime, Date endTime, Boolean needUpdateUnit) throws ExecutionException, InterruptedException { if (CollectionUtils.isEmpty(dataList)) { @@ -351,9 +377,9 @@ } List<YwPoint> pointList = dataList.stream().map(item -> { YwPoint point = new YwPoint(); - point.setImportantCommandImageTag("鏄�".equals(item.getImportantCommandImageTagString())); - point.setProvinceTag("鏄�".equals(item.getProvinceTagString())); - point.setDeptTag("鏄�".equals(item.getDeptTag())); + point.setImportantCommandImageTag(item.getImportantCommandImageTag()); + point.setProvinceTag(item.getProvinceTag()); + point.setDeptTag(item.getDeptTag()); if (needUpdateUnit) { if (Objects.nonNull(unitId)) { point.setUnitId(Long.valueOf(unitId)); @@ -373,6 +399,67 @@ this.waitAllFinishAndGetResult(pointList); } + @Override + public void updateDynamic(List<PointExport> dataList) { + List<DynamicColumnValue> insertList = new ArrayList<>(); + List<DynamicColumnValue> updateList = new ArrayList<>(); + List<Integer> deleteList = new ArrayList<>(); + //鏌ュ嚭鎵�鏈夌绫诲姩鎬佸垪浣滀负瀛楀吀 + Map<String, Integer> dictionary = new LambdaQueryChainWrapper<>(dynamicColumnMapper) + .list().stream().collect(Collectors.toMap(DynamicColumn::getLabelValue, DynamicColumn::getId)); + //鏌ュ嚭鐐逛綅琛ㄧ殑鎵�鏈夊姩鎬佸垪 + List<DynamicColumnVO> dynamicVOList = dynamicColumnMapper.getData(); + Map<Integer, List<DynamicColumnVO>> dynamicVOMap = new HashMap<>(); + if (!CollectionUtils.isEmpty(dynamicVOList)) { + dynamicVOMap = dynamicVOList.stream().filter(vo -> vo.getRefId() != null).collect(Collectors.groupingBy(DynamicColumnVO::getRefId)); + } + //鏍规嵁label_value鍜宺ef_id鍒ゆ柇鏄惁鏄悓涓�鏁版嵁 + for (PointExport pointExport : dataList) { + //寰楀埌excel涓姩鎬佸垪鍚嶄綔涓簁ey鐨刴ap + Map<String, DynamicColumnVO> exportMap = pointExport.getDynamicData().stream().collect(Collectors.toMap(DynamicColumnVO::getLabelValue, Function.identity())); + //鏌ュ嚭鏁版嵁搴撲腑杩欎釜鐐逛綅鐨勫姩鎬佸垪 + List<DynamicColumnVO> dynamicColumnVOS = dynamicVOMap.get(pointExport.getId()); + if (!CollectionUtils.isEmpty(dynamicColumnVOS)) { + //閬嶅巻鏁版嵁搴撲腑杩欎釜鐐逛綅鐨勫姩鎬佸垪锛屽苟鏍规嵁鍔ㄦ�佸垪鍚嶆壘鍑篹xcel涓殑鍊� + for (DynamicColumnVO vo : dynamicColumnVOS) { + DynamicColumnVO dynamicColumnVO = exportMap.get(vo.getLabelValue()); + //瀛樺湪璇存槑excel鏈夎繖涓暟鎹紝灏嗘暟鎹簱杩欎釜鏁版嵁鐨勫�兼敼涓篹xcel鐨勫�� + if (dynamicColumnVO.getColumnValue() != null) { + DynamicColumnValue entity = new DynamicColumnValue(); + entity.setColumnValue(dynamicColumnVO.getColumnValue()); + entity.setDynamicColumnId(vo.getId()); + entity.setRefId(vo.getRefId()); + entity.setId(vo.getValueId()); + updateList.add(entity); + + } else { + //涓嶅瓨鍦ㄥ垯鍙栨秷鏍囩锛屽垹闄� + deleteList.add(vo.getValueId()); + } + } + } else { + //鏁版嵁搴撻噷鏌ヤ笉鍒癳xcel閲岄潰鐨勮繖涓偣浣嶅姩鎬佸垪锛屽叏涓烘柊澧� + List<DynamicColumnVO> dynamicData = pointExport.getDynamicData(); + for (DynamicColumnVO excelData : dynamicData) { + if(excelData.getColumnValue()!=null) { + Integer dynamicId = dictionary.get(excelData.getLabelValue()); + DynamicColumnValue entity = new DynamicColumnValue(); + entity.setColumnValue(excelData.getColumnValue()); + entity.setDynamicColumnId(dynamicId); + entity.setRefId(pointExport.getId()); + insertList.add(entity); + } + } + } + } + //鎻掑叆 + if(!CollectionUtils.isEmpty(insertList)) dynamicColumnMapper.saveBatch(insertList); + //鏇存柊 + if(!CollectionUtils.isEmpty(updateList)) dynamicColumnMapper.updateBatch(updateList); + //鍒犻櫎 + if(!CollectionUtils.isEmpty(deleteList)) dynamicColumnMapper.deleteBatchIds(deleteList); + } + public void waitAllFinishAndGetResult(List<YwPoint> dataList) { Date startTime = new Date(); this.baseMapper.updatePoint(dataList); 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 85c3811..eecd52e 100644 --- a/ycl-server/src/main/java/com/ycl/task/HKTask.java +++ b/ycl-server/src/main/java/com/ycl/task/HKTask.java @@ -123,6 +123,7 @@ //瀛樻斁鍦╩ongo涓� carList.forEach(item -> item.setDataType(ApiConstants.HK_DataType_CAR)); mongoTemplate.insertAll(carList); + //TODO:鏇存柊point琛ㄥ湪绾跨姸鎬� } /** 浜鸿劯鏁版嵁 */ @@ -148,7 +149,9 @@ //瀛樻斁鍦╩ongo涓� faceList.forEach(item -> item.setDataType(ApiConstants.HK_DataType_FACE)); mongoTemplate.insertAll(faceList); + //TODO:鏇存柊point琛ㄥ湪绾跨姸鎬� } + log.info("缁撴潫鎶撴媿鏁版嵁閲忔娴嬬粨鏋滄暟鎹悓姝�"); } diff --git a/ycl-server/src/main/java/com/ycl/task/MonitorTask.java b/ycl-server/src/main/java/com/ycl/task/MonitorTask.java index 7026520..1b3a5ac 100644 --- a/ycl-server/src/main/java/com/ycl/task/MonitorTask.java +++ b/ycl-server/src/main/java/com/ycl/task/MonitorTask.java @@ -21,6 +21,7 @@ import com.ycl.system.service.ISysDictDataService; import com.ycl.utils.DateUtils; import com.ycl.utils.StringUtils; +import constant.ApiConstants; import constant.CheckConstants; import constant.RedisConstant; import enumeration.general.AreaDeptEnum; @@ -62,18 +63,18 @@ private RedisTemplate redisTemplate; //鍚屾鐐逛綅鍦ㄧ嚎鐜囧埌mongo(寮冪敤) - public void siteOnline() { - log.info("寮�濮嬪悓姝ョ偣浣嶅湪绾跨巼鍒癿ongo"); - List<TMonitorResult> tMonitorResults = monitorMapper.selectMonitorResult(null); - if (!CollectionUtils.isEmpty(tMonitorResults)) { - //濡傛灉瀛樺湪涔嬪墠鐨勬暟鎹厛鍒犻櫎 - Query query = new Query(Criteria.where("mongoCreateTime").gte(DateUtils.getDayStart(new Date())).lt(DateUtils.getDayEnd(new Date()))); - DeleteResult result = mongoTemplate.remove(query, TMonitorResult.class); - //瀛樻斁鍦╩ongo涓� - mongoTemplate.insertAll(tMonitorResults); - log.info("缁撴潫鍚屾鐐逛綅鍦ㄧ嚎鐜囧埌mongo"); - } - } +// public void siteOnline() { +// log.info("寮�濮嬪悓姝ョ偣浣嶅湪绾跨巼鍒癿ongo"); +// List<TMonitorResult> tMonitorResults = monitorMapper.selectMonitorResult(null); +// if (!CollectionUtils.isEmpty(tMonitorResults)) { +// //濡傛灉瀛樺湪涔嬪墠鐨勬暟鎹厛鍒犻櫎 +// Query query = new Query(Criteria.where("mongoCreateTime").gte(DateUtils.getDayStart(new Date())).lt(DateUtils.getDayEnd(new Date()))); +// DeleteResult result = mongoTemplate.remove(query, TMonitorResult.class); +// //瀛樻斁鍦╩ongo涓� +// mongoTemplate.insertAll(tMonitorResults); +// log.info("缁撴潫鍚屾鐐逛綅鍦ㄧ嚎鐜囧埌mongo"); +// } +// } //鍚屾mongodb涓�鏈轰竴妗e埌鏁版嵁搴� @Transactional(rollbackFor = Exception.class) @@ -161,6 +162,7 @@ ywPoint.setImportantCommandImageTag(Boolean.FALSE); ywPoint.setCreateTime(new Date()); ywPoint.setUpdateTime(new Date()); + ywPoint.setOnline(ApiConstants.UY_OnlineSite_Unknown); } //鐘舵�佽缃负鑰冩牳璁惧 ywPoint.setExamineStatus(Boolean.TRUE); 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 a196083..1c489f3 100644 --- a/ycl-server/src/main/java/com/ycl/task/UYTask.java +++ b/ycl-server/src/main/java/com/ycl/task/UYTask.java @@ -172,9 +172,9 @@ } /** - * 鐐逛綅鍦ㄧ嚎妫�娴� + * 鐐逛綅鍦ㄧ嚎PING妫�娴� */ - //TODO锛氳棰戠绾挎鏁般�佺洃娴嬫鏁帮紙淇敼閫昏緫鍙拡瀵瑰伐鍗曪紝妫�娴嬪湪绾跨殑锛� + //TODO锛氱偣浣嶅湪绾匡紙淇敼閫昏緫鍙拡瀵瑰伐鍗曪級 public void pointOnline() throws ExecutionException, InterruptedException { log.info("寮�濮嬫娴嬬偣浣嶅湪绾�"); Integer times = 2; @@ -186,25 +186,16 @@ } else { log.error("璇烽厤缃绾挎鏁帮紝姝ゆ璁剧疆涓洪粯璁ゅ��2"); } - // 鍏堟煡鍑鸿澶嘔P闆嗗悎 + // 鍏堟煡鍑哄湪绾跨殑璁惧IP闆嗗悎,鍓旈櫎鎺夊湪绾挎儏鍐垫槸鏈煡鐨�,骞朵笖鍙娴嬫鍦ㄨ�冩牳鐨勮澶囬伩鍏嶅浣欏伐鍗� List<TMonitorResult> monitorList = monitorMapper.getDistinctIP(); -// List<Future<TMonitorResult>> futureList = new ArrayList<>(48); List<TMonitorResult> dataList = new ArrayList<>(48); -// for (TMonitorResult monitor : monitorList) { -// OnlineCheckThread thread = new OnlineCheckThread(monitor, checkPointUtil, times); -// Future<TMonitorResult> future = executorService.submit(thread); -// futureList.add(future); -// } -// for (Future<TMonitorResult> future : futureList) { -// dataList.add(future.get()); // get鏂规硶浼氶樆濉烇紝鐭ラ亾鎷垮埌缁撴灉鎵嶇户缁墽琛宖or -// } Integer time = times; List<CompletableFuture<TMonitorResult>> futureList = monitorList.stream() .map(monitor -> CompletableFuture.supplyAsync(() -> { OnlineCheckThread thread = new OnlineCheckThread(monitor, checkPointUtil, time); return thread.call(); // 鍋囪 OnlineCheckThread 瀹炵幇浜� Callable 鎺ュ彛 }, executorService) - .orTimeout(40, TimeUnit.SECONDS) + .orTimeout(60, TimeUnit.SECONDS) .exceptionally(ex -> { if (ex instanceof TimeoutException) { log.error("浠诲姟鎵ц瓒呮椂:"+monitor.getIp()); @@ -239,7 +230,7 @@ futureList.toArray(new CompletableFuture[0]) ); try { - allOf.get(35, TimeUnit.SECONDS); // 缁欎簣棰濆鐨�5绉掓潵鏀堕泦缁撴灉 + allOf.get(60, TimeUnit.SECONDS); // 缁欎簣棰濆鐨�5绉掓潵鏀堕泦缁撴灉 } catch (TimeoutException e) { log.warn("閮ㄥ垎浠诲姟鏈湪鎸囧畾鏃堕棿鍐呭畬鎴�"); } catch (Exception e2){ @@ -248,19 +239,19 @@ dataList = futureList.stream() .map(CompletableFuture::join) - .filter(result -> result != null) + .filter(Objects::nonNull) .collect(Collectors.toList()); - // 鏇存柊point琛ㄧ殑鍦ㄧ嚎鏍囪瘑 + //绛涢�夊嚭ping绂荤嚎鐨勮澶囷紝鏇存敼鏁版嵁搴撲负绂荤嚎 Date now = new Date(); - List<UpdateOnlineVO> willUpdateList = dataList.stream().map(item -> { + List<UpdateOnlineVO> willUpdateList = dataList.stream().filter(result->!result.getOnline()).map(item -> { UpdateOnlineVO vo = new UpdateOnlineVO(); - vo.setOnline(item.getOnline()); + vo.setOnline(item.getOnline()?ApiConstants.UY_OnlineSite_Online:ApiConstants.UY_OnlineSite_Offline); vo.setIp(item.getIp()); vo.setUpdateTime(now); return vo; }).collect(Collectors.toList()); monitorMapper.updateOnline(willUpdateList); - // 宸ュ崟 + //宸ュ崟 List<WorkOrder> workOrderList = dataList.stream() .filter(item -> Objects.nonNull(item.getWorkOrder())) .map(TMonitorResult::getWorkOrder) @@ -305,8 +296,19 @@ pointService.setDeviceTagByGB(records); //瀛樻斁鍦╩ongo涓� mongoTemplate.insertAll(records); - // 宸ュ崟鐢熸垚 - uyErrorTypeCheckService.videoOnlineCheck(records); + //鏇存柊point琛ㄥ湪绾跨姸鎬� + Date now = new Date(); + List<UpdateOnlineVO> willUpdateList = records.stream().map(item -> { + UpdateOnlineVO vo = new UpdateOnlineVO(); + vo.setOnline(item.getStatus()); + vo.setIp(item.getIpAddr()); + vo.setUpdateTime(now); + return vo; + }).collect(Collectors.toList()); + monitorMapper.updateOnline(willUpdateList); + //绂荤嚎鐢熸垚宸ュ崟 + List<VideoOnlineResult> workOrders = records.stream().filter(item -> ApiConstants.UY_OnlineSite_Offline.equals(item.getStatus())).collect(Collectors.toList()); + uyErrorTypeCheckService.videoOnlineCheck(workOrders); } else { log.error("鐐逛綅鍦ㄧ嚎缁撴灉鏁版嵁涓虹┖{}", data); } diff --git a/ycl-server/src/main/java/com/ycl/thread/OnlineCheckThread.java b/ycl-server/src/main/java/com/ycl/thread/OnlineCheckThread.java index 520a575..38a7f89 100644 --- a/ycl-server/src/main/java/com/ycl/thread/OnlineCheckThread.java +++ b/ycl-server/src/main/java/com/ycl/thread/OnlineCheckThread.java @@ -73,7 +73,6 @@ workOrder.setStatus(WorkOrderStatusEnum.DISTRIBUTED); result.setWorkOrder(workOrder); } - log.info("鐩戞祴瀵硅薄锛�"+result); return result; } diff --git a/ycl-server/src/main/java/com/ycl/utils/CheckPointUtil.java b/ycl-server/src/main/java/com/ycl/utils/CheckPointUtil.java index 8e97e3a..323cb6c 100644 --- a/ycl-server/src/main/java/com/ycl/utils/CheckPointUtil.java +++ b/ycl-server/src/main/java/com/ycl/utils/CheckPointUtil.java @@ -49,7 +49,6 @@ public TMonitorResult check(TMonitorResult monitor) { // 鍏堟娴嬭兘鍚﹁闂ip鐨勭綉椤� ResponseEntity<String> res = null; - log.info("鐩戞祴IP锛�" + monitor.getIp()); String prefix = "http://"; if ("127.0.0.1".equals(monitor.getIp())) { monitor.setOnline(Boolean.FALSE); @@ -74,7 +73,7 @@ } if (!monitor.getOnline()) { try { - reachable = InetAddress.getByName(monitor.getIp()).isReachable(3000); + reachable = InetAddress.getByName(monitor.getIp()).isReachable(5000); } catch (IOException e) { e.printStackTrace(); } diff --git a/ycl-server/src/main/resources/mapper/zgyw/DynamicColumnMapper.xml b/ycl-server/src/main/resources/mapper/zgyw/DynamicColumnMapper.xml index a2ba459..d1e7e33 100644 --- a/ycl-server/src/main/resources/mapper/zgyw/DynamicColumnMapper.xml +++ b/ycl-server/src/main/resources/mapper/zgyw/DynamicColumnMapper.xml @@ -28,10 +28,12 @@ </select> <select id="getData" resultType="com.ycl.platform.domain.vo.DynamicColumnVO"> SELECT - TDCV.ref_id as id, + TDC.id, + TDCV.ref_id, TDC.prop_name, TDC.label_value, - TDCV.column_value + TDCV.column_value, + TDCV.id as value_id FROM t_dynamic_column TDC LEFT JOIN t_dynamic_column_value TDCV ON TDC.id =TDCV.dynamic_column_id @@ -46,6 +48,7 @@ </where> </select> + <select id="getPage" resultMap="BaseResultMap"> SELECT TDC.ref_id, TDC.prop_name, @@ -56,4 +59,17 @@ WHERE TDC.deleted = 0 </select> + <insert id="saveBatch"> + insert into t_dynamic_column_value (dynamic_column_id,column_value,ref_id) values + <foreach collection="insertList" item="item" separator=","> + (#{item.dynamicColumnId},#{item.columnValue},#{item.refId}) + </foreach> + </insert> + <update id="updateBatch"> + <foreach collection="updateList" separator=";" item="item"> + UPDATE t_dynamic_column_value + SET column_value = #{item.columnValue} + WHERE id = #{item.id} + </foreach> + </update> </mapper> diff --git a/ycl-server/src/main/resources/mapper/zgyw/TMonitorMapper.xml b/ycl-server/src/main/resources/mapper/zgyw/TMonitorMapper.xml index c38edf2..2df6852 100644 --- a/ycl-server/src/main/resources/mapper/zgyw/TMonitorMapper.xml +++ b/ycl-server/src/main/resources/mapper/zgyw/TMonitorMapper.xml @@ -342,8 +342,9 @@ <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 = 0, 1, 0)), 0) AS postsPercentage, - IFNULL(ROUND(SUM(IF(p.online, 1, 0)) / count(*) * 100, 2), 0) as viewsPercentage FROM t_monitor m + 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 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 <where> @@ -439,30 +440,33 @@ 'video' as type, COUNT(p1.id) AS totalNum, COUNT(p2.id) AS errorNum, - COUNT(p1.id) - COUNT(p2.id) AS normalNum + 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 = 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 = 0 AND p1.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = 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 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 = 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 = 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 'car' as type, COUNT(p1.id) AS totalNum, COUNT(p2.id) AS errorNum, - COUNT(p1.id) - COUNT(p2.id) AS normalNum + 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 = 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 = 0 AND p1.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = '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 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 = 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 = 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 'face' as type, COUNT(p1.id) AS totalNum, COUNT(p2.id) AS errorNum, - COUNT(p1.id) - COUNT(p2.id) AS normalNum + 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 = 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 = 0 AND p1.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = 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 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 = 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 = 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"> @@ -473,15 +477,17 @@ ( SELECT d.area, - IFNULL(ROUND((COUNT(m.id) - COUNT(m2.id)) / COUNT(m.id) * 100, 2), 0) AS faceRate, - COUNT(m.id) - COUNT(m2.id) AS faceNormalNum, + IFNULL(ROUND(COUNT(m3.id)/ COUNT(m.id) * 100, 2), 0) AS faceRate, + COUNT(m3.id) AS faceNormalNum, COUNT(m2.id) AS faceErrorNum 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 = 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 = 0 AND p2.examine_status = 1 AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = 1 </if><if test="dataScope == 3"> AND p2.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 = 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 = 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) WHERE d.del_flag = 0 AND d.area IS NOT NULL GROUP BY d.area, d.dept_id ORDER BY d.area_code @@ -491,15 +497,17 @@ ( SELECT d.area, - IFNULL(ROUND((COUNT(m.id) - COUNT(m2.id)) / COUNT(m.id) * 100, 2), 0) AS carRate, - COUNT(m.id) - COUNT(m2.id) AS carNormalNum, + IFNULL(ROUND(COUNT(m3.id) / COUNT(m.id) * 100, 2), 0) AS carRate, + COUNT(m3.id) AS carNormalNum, COUNT(m2.id) AS carErrorNum 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 = 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 = 0 AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = 1 </if><if test="dataScope == 3"> AND p2.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 = 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 = 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) WHERE d.del_flag = 0 AND d.area IS NOT NULL GROUP BY d.area, d.dept_id, d.area_code ORDER BY d.area_code @@ -509,15 +517,17 @@ ( SELECT d.area, - IFNULL(ROUND((COUNT(m.id) - COUNT(m2.id)) / COUNT(m.id) * 100, 2), 0) AS videoRate, - COUNT(m.id) - COUNT(m2.id) AS videoNormalNum, + IFNULL(ROUND(COUNT(m3.id) / COUNT(m.id) * 100, 2), 0) AS videoRate, + COUNT(m3.id) AS videoNormalNum, COUNT(m2.id) AS videoErrorNum 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 = 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 = 0 AND p.id = p2.id <if test="dataScope == 1"> AND p2.province_tag = 1 </if><if test="dataScope == 3"> AND p2.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 = 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 = 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) WHERE d.del_flag = 0 AND d.area IS NOT NULL GROUP BY d.area, d.area_code, d.dept_id ORDER BY d.area_code @@ -645,6 +655,7 @@ 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 + where p.online != 0 and p.examine_status = 1 </select> <update id="updateOnline"> @@ -655,7 +666,7 @@ online = #{online.online}, update_time = #{online.updateTime} WHERE - EXISTS (SELECT 1 FROM t_monitor WHERE ip = #{online.ip} AND t_monitor.serial_number = t_yw_point.serial_number) + EXISTS (SELECT 1 FROM t_monitor WHERE ip = #{online.ip} AND t_monitor.serial_number = t_yw_point.serial_number ) </foreach> </update> </mapper> diff --git a/ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml b/ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml index 66fbde5..bb27935 100644 --- a/ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml +++ b/ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml @@ -95,9 +95,9 @@ LEFT JOIN t_dynamic_column dc ON dc.id = dcv.dynamic_column_id AND dc.table_name = #{tableName} WHERE dcv.ref_id = #{id} - <if test="pointName != null and pointName != ''"> - and dcv.column_value like concat('%', #{pointName}, '%') - </if> +<!-- <if test="pointName != null and pointName != ''">--> +<!-- and dcv.column_value like concat('%', #{pointName}, '%')--> +<!-- </if>--> </select> <select id="dataCenterPage" resultType="com.ycl.platform.domain.vo.PointDetailVO"> @@ -233,7 +233,7 @@ CASE WHEN yp.province_tag = 0 THEN '鍚�' ELSE '鏄�' END AS provinceTagString, CASE WHEN yp.important_tag = 0 THEN '鍚�' ELSE '鏄�' END AS importantTagString, CASE WHEN yp.important_command_image_tag = 0 THEN '鍚�' ELSE '鏄�' END AS importantCommandImageTagString, - CASE WHEN yp.dept_tag = 0 THEN '鍚�' ELSE '鏄�' END AS deptTag + CASE WHEN yp.dept_tag = 0 THEN '鍚�' ELSE '鏄�' END AS deptTagString FROM t_yw_point yp INNER JOIN t_monitor m ON yp.serial_number = m.serial_number -- Gitblit v1.8.0