From 2819d40212abcccd7d404e2126e281a4d3a7f0a6 Mon Sep 17 00:00:00 2001 From: fuliqi <fuliqi@qq.com> Date: 星期日, 29 九月 2024 02:10:49 +0800 Subject: [PATCH] 动态列点位表导出 --- ycl-server/src/main/resources/mapper/zgyw/DynamicColumnMapper.xml | 73 +++++++----- ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml | 125 ++++++++++---------- ycl-server/src/main/java/com/ycl/platform/mapper/DynamicColumnMapper.java | 8 + ycl-server/src/main/java/com/ycl/platform/service/impl/DynamicColumnServiceImpl.java | 2 ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java | 12 +- ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java | 95 ++++++++++++++- 6 files changed, 205 insertions(+), 110 deletions(-) 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 be898c3..1e982be 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 @@ -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; } 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 e7e3a8d..404c43d 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 @@ -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); diff --git a/ycl-server/src/main/java/com/ycl/platform/service/impl/DynamicColumnServiceImpl.java b/ycl-server/src/main/java/com/ycl/platform/service/impl/DynamicColumnServiceImpl.java index 000be1e..b8bca5c 100644 --- a/ycl-server/src/main/java/com/ycl/platform/service/impl/DynamicColumnServiceImpl.java +++ b/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"; /** * 娣诲姞 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 f2476f6..3210871 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 @@ -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) { + //鍒涘缓涓�涓彉閲忚褰曟壘娌℃壘鍒拌繖涓猦eader + 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) { diff --git a/ycl-server/src/main/resources/mapper/zgyw/DynamicColumnMapper.xml b/ycl-server/src/main/resources/mapper/zgyw/DynamicColumnMapper.xml index 7a27cf7..a2ba459 100644 --- a/ycl-server/src/main/resources/mapper/zgyw/DynamicColumnMapper.xml +++ b/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> diff --git a/ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml b/ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml index d31c6a7..2ded25c 100644 --- a/ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml +++ b/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> -- Gitblit v1.8.0