ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java
@@ -4,10 +4,11 @@ import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.format.DateTimeFormat; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentStyle; import com.ycl.platform.domain.vo.DynamicColumnVO; import lombok.Data; import java.util.Date; import java.util.List; /** * 点位导入导出 @@ -17,10 +18,9 @@ */ @Data public class PointExport { private Integer id; @ColumnWidth(50) @ExcelProperty("点位名称(可修改)") private String pointName; @ColumnWidth(30) @@ -63,11 +63,11 @@ @ExcelIgnore private Boolean importantTag; // @ExcelProperty("是否重点点位") // private String importantTagString; @ExcelProperty("是否重点点位") private String importantTagString; @ExcelIgnore private Boolean importantCommandImageTag; private List<DynamicColumnVO> dynamicData; } ycl-server/src/main/java/com/ycl/platform/mapper/DynamicColumnMapper.java
@@ -27,6 +27,14 @@ DynamicColumnVO getById(Integer id); /** * 获取动态列数据 * @param * @return */ List<DynamicColumnVO> getData(); List<String> getHeader(String tableName); /** * 分页 */ IPage getPage(IPage page, @Param("query") DynamicColumnQuery query); ycl-server/src/main/java/com/ycl/platform/service/impl/DynamicColumnServiceImpl.java
@@ -31,7 +31,7 @@ private final DynamicColumnMapper dynamicColumnMapper; private final static String TABLE_NAME = "t_monitor"; private final static String TABLE_NAME = "t_yw_point"; /** * 添加 ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java
@@ -1,11 +1,14 @@ package com.ycl.platform.service.impl; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper; import com.baomidou.mybatisplus.extension.conditions.update.LambdaUpdateChainWrapper; 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; @@ -15,8 +18,10 @@ import com.ycl.platform.domain.query.YwPointQuery; import com.ycl.platform.domain.result.BaseResult; import com.ycl.platform.domain.vo.CheckTagVO; import com.ycl.platform.domain.vo.DynamicColumnVO; import com.ycl.platform.domain.vo.ReportAuditingRecordVO; import com.ycl.platform.domain.vo.YwPointVO; import com.ycl.platform.mapper.DynamicColumnMapper; import com.ycl.platform.mapper.TMonitorMapper; import com.ycl.platform.mapper.YwPeopleMapper; import com.ycl.platform.mapper.YwPointMapper; @@ -42,6 +47,7 @@ import java.util.*; import java.util.concurrent.*; import java.util.function.Consumer; import java.util.function.Function; import java.util.stream.Collectors; /** @@ -56,10 +62,11 @@ private final SysDeptMapper sysDeptMapper; private final TMonitorMapper monitorMapper; private final DynamicColumnMapper dynamicColumnMapper; /** * 添加 * * @param form * @return */ @@ -69,7 +76,7 @@ Date now = new Date(); entity.setCreateTime(now); entity.setUpdateTime(now); if(baseMapper.insert(entity) > 0) { if (baseMapper.insert(entity) > 0) { return Result.ok("添加成功"); } return Result.error("添加失败"); @@ -113,6 +120,7 @@ /** * 修改 * * @param form * @return */ @@ -129,12 +137,13 @@ /** * 批量删除 * * @param ids * @return */ @Override public Result remove(List<String> ids) { if(baseMapper.deleteBatchIds(ids) > 0) { if (baseMapper.deleteBatchIds(ids) > 0) { return Result.ok("删除成功"); } return Result.error("删除失败"); @@ -142,12 +151,13 @@ /** * id删除 * * @param id * @return */ @Override public Result removeById(String id) { if(baseMapper.deleteById(id) > 0) { if (baseMapper.deleteById(id) > 0) { return Result.ok("删除成功"); } return Result.error("删除失败"); @@ -155,6 +165,7 @@ /** * 分页查询 * * @param query * @return */ @@ -167,6 +178,7 @@ /** * 根据id查找 * * @param id * @return */ @@ -183,6 +195,7 @@ /** * 列表 * * @return */ @Override @@ -228,13 +241,71 @@ public void export(YwPointQuery query, HttpServletResponse response) throws IOException { // 导出数据 List<PointExport> exportData = baseMapper.export(query); EasyExcel.write(response.getOutputStream(), PointExport.class) .sheet("点位更换运维单位") .doWrite(exportData); //获取动态列数据 List<String> dynamicsHeaders = dynamicColumnMapper.getHeader("t_yw_point"); 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)); } //定义表头列表 LinkedHashSet<String> headers = new LinkedHashSet<>(); headers.add("点位名称(可修改)"); headers.add("国标码(不可修改)"); headers.add("点位IP(可修改)"); headers.add("是否省厅标签(可修改)"); headers.add("是否重点指挥图像(可修改)"); headers.add("是否部级标签(可修改)"); List<String> headersList = new LinkedList<>(); if (!CollectionUtils.isEmpty(dynamicsHeaders)) { //使用链表保证后续补充数据时获取数据顺序一致 headersList.addAll(dynamicsHeaders); headers.addAll(headersList); } // 定义数据 List<List<Object>> data = new ArrayList<>(); for (PointExport export : exportData) { List<Object> list = new ArrayList<>(); //需要保证顺序一致 list.add(export.getPointName()); list.add(export.getSerialNumber()); list.add(export.getPointIP()); list.add(export.getProvinceTagString()); list.add(export.getImportantCommandImageTagString()); list.add(export.getDeptTag()); for (String header : headersList) { //创建一个变量记录找没找到这个header boolean flag = false; List<DynamicColumnVO> columnVOS = dynamicMap.get(export.getId()); if (!CollectionUtils.isEmpty(columnVOS)) { for (DynamicColumnVO columnVO : columnVOS) { if (header.equals(columnVO.getLabelValue())) { list.add(columnVO.getColumnValue()); flag = true; } } } //没找到用空串占位 if (!flag) list.add(""); } data.add(list); } List<List<String>> headList = new ArrayList<>(); for (String header : headers) { headList.add(Collections.singletonList(header)); } EasyExcel.write(response.getOutputStream()) .head(headList) // 设置表头 .sheet("点位更换运维单位") // 设置sheet名称 .doWrite(data); // 写入数据 } @Override public Result importData(MultipartFile file, Integer unitId, Date startTime, Date endTime, Boolean needUpdateUnit) throws IOException { public Result importData(MultipartFile file, Integer unitId, Date startTime, Date endTime, Boolean needUpdateUnit) throws IOException { Consumer<List<PointExport>> consumer = (dataList) -> { try { this.updatePoint(dataList, unitId, startTime, endTime, needUpdateUnit); @@ -244,7 +315,7 @@ e.printStackTrace(); } }; EasyExcel.read(file.getInputStream(), PointExport.class , new CurrencyDataListener(consumer)).headRowNumber(1).doReadAll(); EasyExcel.read(file.getInputStream(), PointExport.class, new CurrencyDataListener(consumer)).headRowNumber(1).doReadAll(); return Result.ok(); } @@ -260,7 +331,8 @@ * @param unitId */ @Transactional(rollbackFor = Exception.class) public void updatePoint(List<PointExport> dataList, Integer unitId, Date startTime, Date endTime, Boolean needUpdateUnit) throws ExecutionException, InterruptedException { public void updatePoint(List<PointExport> dataList, Integer unitId, Date startTime, Date endTime, Boolean needUpdateUnit) throws ExecutionException, InterruptedException { if (CollectionUtils.isEmpty(dataList)) { throw new RuntimeException("导入数据不能为空"); } @@ -288,7 +360,8 @@ this.waitAllFinishAndGetResult(pointList); } public void waitAllFinishAndGetResult(List<YwPoint> dataList) throws InterruptedException, ExecutionException { public void waitAllFinishAndGetResult(List<YwPoint> dataList) throws InterruptedException, ExecutionException { int start = 0; Date startTime = new Date(); while (true) { ycl-server/src/main/resources/mapper/zgyw/DynamicColumnMapper.xml
@@ -4,43 +4,56 @@ <!-- 通用查询映射结果 --> <resultMap id="BaseResultMap" type="com.ycl.platform.domain.vo.DynamicColumnVO"> <result column="ref_id" property="refId" /> <result column="prop_name" property="propName" /> <result column="label_value" property="labelValue" /> <result column="field_value" property="fieldValue" /> <result column="ref_id" property="refId"/> <result column="prop_name" property="propName"/> <result column="label_value" property="labelValue"/> <result column="field_value" property="fieldValue"/> </resultMap> <select id="getById" resultMap="BaseResultMap"> SELECT TDC.ref_id, TDC.prop_name, TDC.label_value, TDC.field_value, TDC.id FROM t_dynamic_column TDC WHERE TDC.id = #{id} AND TDC.deleted = 0 SELECT TDC.ref_id, TDC.prop_name, TDC.label_value, TDC.field_value, TDC.id FROM t_dynamic_column TDC WHERE TDC.id = #{id} AND TDC.deleted = 0 </select> <select id="getHeader" resultType="java.lang.String"> SELECT TDC.label_value FROM t_dynamic_column TDC WHERE TDC.table_name =#{tableName} </select> <select id="getData" resultType="com.ycl.platform.domain.vo.DynamicColumnVO"> SELECT TDCV.ref_id as id, TDC.prop_name, TDC.label_value, TDCV.column_value FROM t_dynamic_column TDC LEFT JOIN t_dynamic_column_value TDCV ON TDC.id =TDCV.dynamic_column_id <where> TDC.table_name = 't_yw_point' <!-- <if test="ids !=null and ids.size > 0">--> <!-- AND TDCV.ref_id in--> <!-- <foreach collection="ids" open="(" separator="," close=")" item="id">--> <!-- #{id}--> <!-- </foreach>--> <!-- </if>--> </where> </select> <select id="getPage" resultMap="BaseResultMap"> SELECT TDC.ref_id, TDC.prop_name, TDC.label_value, TDC.field_value, TDC.id FROM t_dynamic_column TDC WHERE TDC.deleted = 0 SELECT TDC.ref_id, TDC.prop_name, TDC.label_value, TDC.field_value, TDC.id FROM t_dynamic_column TDC WHERE TDC.deleted = 0 </select> </mapper> ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml
@@ -22,7 +22,7 @@ <result column="dept_tag" property="deptTag"/> </resultMap> <!-- 通用查询映射结果 --> <!-- 分页映射结果 --> <resultMap id="pageMap" type="com.ycl.platform.domain.vo.YwPointVO"> <result column="id" property="id"/> <result column="point_name" property="pointName"/> @@ -40,7 +40,9 @@ <result column="important_tag" property="importantTag"/> <result column="important_command_image_tag" property="importantCommandImageTag"/> <result column="dept_tag" property="deptTag"/> <collection property="dynamicColumnList" select="getDyColumns" column="{id=id,tableName=tableName,pointName=pointName}" ofType="com.ycl.platform.domain.vo.DynamicColumnVO"/> <collection property="dynamicColumnList" select="getDyColumns" column="{id=id,tableName=tableName,pointName=pointName}" ofType="com.ycl.platform.domain.vo.DynamicColumnVO"/> </resultMap> <resultMap id="dynamicColumnMap" type="com.ycl.platform.domain.vo.DynamicColumnVO"> @@ -93,18 +95,19 @@ WHERE dcv.ref_id = #{id} <if test="pointName != null and pointName != ''"> and dcv.column_value like concat('%', #{keyword}, '%') and dcv.column_value like concat('%', #{pointName}, '%') </if> </select> <select id="dataCenterPage" resultType="com.ycl.platform.domain.vo.PointDetailVO"> SELECT m.* m.* FROM t_yw_point p INNER JOIN t_monitor m ON p.serial_number = m.serial_number t_yw_point p INNER JOIN t_monitor m ON p.serial_number = m.serial_number <where> <if test="query.keyword != null and query.keyword != ''"> AND (m.name like concat('%', #{query.keyword}, '%') OR m.serial_number like concat('%', #{query.keyword}, '%')) AND (m.name like concat('%', #{query.keyword}, '%') OR m.serial_number like concat('%', #{query.keyword}, '%')) </if> <if test="query.dataType == 1"> AND p.province_tag = 1 @@ -145,9 +148,9 @@ <select id="distinctCount" resultType="integer"> SELECT count(DISTINCT m.serial_number) count(DISTINCT m.serial_number) FROM t_yw_point p INNER JOIN t_monitor m ON p.serial_number = m.serial_number t_yw_point p INNER JOIN t_monitor m ON p.serial_number = m.serial_number <where> <if test="query.dataType == 1"> AND p.province_tag = 1 @@ -195,8 +198,7 @@ AND TIMESTAMPDIFF(MONTH, NOW(), end_time) <= (SELECT config_value FROM sys_config WHERE config_key = 'operation.and.maintenance.expiration.warning.time') ORDER BY end_time limit 50 ORDER BY end_time limit 50 ]]> </select> @@ -218,11 +220,12 @@ </where> </select> <select id="export" resultType="com.ycl.platform.domain.excel.PointExport"> <select id="export" resultType ="com.ycl.platform.domain.excel.PointExport"> SELECT yp.id, m.name as pointName, m.serial_number, m.ip as pointIp, m.ip as pointIP, yu.unit_name, yp.start_time, yp.end_time, @@ -243,7 +246,8 @@ </select> <delete id="deleteAll"> delete from t_yw_point delete from t_yw_point </delete> <update id="updatePoint"> @@ -270,54 +274,52 @@ </update> <select id="countNotFinishedWorkOrderByPointId" resultType="com.ycl.platform.domain.vo.YwPointVO"> SELECT yp.id, yp.point_name, wo.serial_number FROM t_yw_point yp SELECT yp.id, yp.point_name, wo.serial_number FROM t_yw_point yp INNER JOIN t_work_order wo ON yp.serial_number = wo.serial_number AND wo.status not in ('AUDITING_SUCCESS','WAIT_DISTRIBUTE') AND wo.deleted = 0 AND yp.deleted = 0 WHERE yp.id = #{pointId} AND wo.status not in ('AUDITING_SUCCESS', 'WAIT_DISTRIBUTE') AND wo.deleted = 0 AND yp.deleted = 0 WHERE yp.id = #{pointId} </select> <select id="countNotFinishedWorkOrderByGb" resultType="com.ycl.platform.domain.vo.YwPointVO"> SELECT yp.id, yp.point_name, wo.serial_number FROM t_yw_point yp INNER JOIN t_work_order wo ON yp.serial_number = wo.serial_number AND wo.status not in ('AUDITING_SUCCESS','WAIT_DISTRIBUTE') AND wo.deleted = 0 AND yp.deleted = 0 AND yp.serial_number = #{serialNumber} LIMIT 1 SELECT yp.id, yp.point_name, wo.serial_number FROM t_yw_point yp INNER JOIN t_work_order wo ON yp.serial_number = wo.serial_number AND wo.status not in ('AUDITING_SUCCESS', 'WAIT_DISTRIBUTE') AND wo.deleted = 0 AND yp.deleted = 0 AND yp.serial_number = #{serialNumber} LIMIT 1 </select> <select id="getDeptPointGB" resultType="string"> SELECT DISTINCT serial_number DISTINCT serial_number FROM t_yw_point t_yw_point WHERE <if test="tagType == 0"> dept_tag = 1 </if> <if test="tagType == 1"> important_tag = 1 </if> <if test="tagType == 2"> important_command_image_tag = 1 </if> AND deleted = 0 <if test="tagType == 0"> dept_tag = 1 </if> <if test="tagType == 1"> important_tag = 1 </if> <if test="tagType == 2"> important_command_image_tag = 1 </if> AND deleted = 0 </select> <select id="select" resultType="com.ycl.platform.domain.entity.YwPoint"> SELECT * * FROM t_yw_point t_yw_point <where> <if test="keyword != null and keyword != ''"> AND (serial_number like concat('%', #{keyword}, '%') or point_name like concat('%', #{keyword}, '%')) @@ -331,30 +333,29 @@ </select> <select id="getReportResult" resultType="com.ycl.platform.domain.vo.ReportAuditingRecordVO"> SELECT ar.result, r.begin_create_time, r.end_create_time FROM t_report r SELECT ar.result, r.begin_create_time, r.end_create_time FROM t_report r INNER JOIN t_report_auditing_record ar ON r.id = ar.report_id AND ar.deleted = 0 AND r.report_type = #{reportType} AND r.deleted = 0 AND r.serial_number = #{serialNumber} AND ar.result = 1 ORDER BY ar.create_time DESC limit 1 AND ar.deleted = 0 AND r.report_type = #{reportType} AND r.deleted = 0 AND r.serial_number = #{serialNumber} AND ar.result = 1 ORDER BY ar.create_time DESC limit 1 </select> <select id="getTagsByGB" resultType="com.ycl.platform.domain.vo.CheckTagVO"> SELECT serial_number as no, province_tag, important_tag, important_command_image_tag, dept_tag serial_number as no, province_tag, important_tag, important_command_image_tag, dept_tag FROM t_yw_point t_yw_point WHERE deleted = 0 AND serial_number in <foreach collection="gbList" open="(" separator="," close=")" item="no">#{no}</foreach> deleted = 0 AND serial_number in <foreach collection="gbList" open="(" separator="," close=")" item="no">#{no}</foreach> GROUP BY serial_number, province_tag, important_tag, important_command_image_tag, dept_tag serial_number, province_tag, important_tag, important_command_image_tag, dept_tag </select>
