Merge remote-tracking branch 'origin/master'

fuliqi

2024-08-07 95b6c442989442750fc2fb85869523678adcd698

Merge remote-tracking branch 'origin/master'

 ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java

New file
@@ -0,0 +1,32 @@
package com.ycl.platform.domain.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;

/**
 * 点位导入导出
 *
 * @author：xp
 * @date：2024/8/6 11:38
 */
@Data
public class PointExport {

    @ColumnWidth(50)
    @ExcelProperty("点位名称")
    private String pointName;

    @ColumnWidth(30)
    @ExcelProperty("国标码")
    private String serialNumber;

    @ColumnWidth(16)
    @ExcelProperty("点位IP")
    private String pointIP;

    @ColumnWidth(40)
    @ExcelProperty("当前运维单位")
    private String unitName;

}

 ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointSelectHandler.java

New file
@@ -0,0 +1,44 @@
package com.ycl.platform.domain.excel;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;

/**
 * 设置easy-excel下拉处理器
 *
 * @author：xp
 * @date：2024/3/15 17:27
 */
public class PointSelectHandler implements CellWriteHandler {

    private List<String> unitNameList;

    public PointSelectHandler(List unitNameList) {
        this.unitNameList = unitNameList;
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 获取sheet对象
        Sheet sheet = writeSheetHolder.getSheet();


        // 获取数据校验helper，excel的下拉就是通过数据校验设置
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();

        // 设置下拉范围，第一列，20000行内
        CellRangeAddressList questionTypeRange = new CellRangeAddressList(1, 20000, 4, 4);
        // 添加单位下拉
        DataValidationConstraint questionConstraint = dataValidationHelper.createExplicitListConstraint((String[]) unitNameList.toArray());
        DataValidation questionValidation = dataValidationHelper.createValidation(questionConstraint, questionTypeRange);
        sheet.addValidationData(questionValidation);

    }
}

 ycl-pojo/src/main/java/com/ycl/platform/domain/vo/YwPointVO.java

@@ -20,7 +20,6 @@
 * @since 2024-03-05
 */
@Data
@Accessors(chain = true)
public class YwPointVO extends AbsVo {

    /** 点位名称 */

 ycl-server/src/main/java/com/ycl/dataListener/CurrencyDataListener.java

New file
@@ -0,0 +1,102 @@
package com.ycl.dataListener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;

/**
 * 通用easyexcel数据监听器
 *
 * @author：xp
 * @date：2024/8/6 15:04
 */
public class CurrencyDataListener<T> implements ReadListener<T> {

    private Consumer<List<T>> consumer;

    /**
     * 每隔100条存储数据库，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;

    /**
     * 缓存的数据
     */
    private List<T> cachedDataList = new ArrayList<>(BATCH_COUNT);

    private final static Logger log = LoggerFactory.getLogger(CurrencyDataListener.class);

    public CurrencyDataListener(Consumer<List<T>> consumer) {
        this.consumer = consumer;
    }

    /**
     * 读取出现异常处理
     * @param e
     * @param analysisContext
     * @throws Exception
     */
    @Override
    public void onException(Exception e, AnalysisContext analysisContext) throws Exception {

    }

    /**
     * 处理表头
     * @param map
     * @param analysisContext
     */
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> map, AnalysisContext analysisContext) {

    }

    /**
     * 读取数据,每一条数据解析都会来调用
     * @param data
     * @param analysisContext
     */
    @Override
    public void invoke(T data, AnalysisContext analysisContext) {
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            try {
                saveData();
            } catch(Exception e) {
                // 这里需要捕获异常，否则list无法清空，导致每读一条数据就会执行saveData方法
            }
            // 存储完成清理 list
            cachedDataList = new ArrayList<>(BATCH_COUNT);
        }
    }

    /**
     * 读取完成
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        log.info("所有数据解析完成！");
    }

    @Override
    public boolean hasNext(AnalysisContext analysisContext) {
        return true;
    }

    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        consumer.accept(cachedDataList);
        log.info("存储数据库成功！");
    }

}

 ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java

@@ -3,10 +3,13 @@
import com.ycl.platform.domain.form.BatchEditPointForm;
import com.ycl.system.domain.group.Update;
import com.ycl.system.domain.group.Add;
import jakarta.servlet.http.HttpServletResponse;
import jakarta.validation.constraints.NotBlank;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.validation.annotation.Validated;
import lombok.RequiredArgsConstructor;

import java.io.IOException;
import java.util.List;
import org.springframework.validation.annotation.Validated;
import jakarta.validation.constraints.NotEmpty;
@@ -18,6 +21,7 @@
import com.ycl.platform.domain.query.YwPointQuery;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

/**
 * 运维点位 前端控制器
@@ -102,4 +106,17 @@
    public Result select(@NotBlank(message = "请输入点位") String keyword) {
        return ywPointService.select(keyword);
    }

    @GetMapping("/export")
    @ApiOperation(value = "导出数据", notes = "导出数据")
    public void export(YwPointQuery query, HttpServletResponse response) throws IOException {
        ywPointService.export(query, response);
    }

    @PostMapping("/import/{unitId}")
    @ApiOperation(value = "导入数据", notes = "导入数据")
    public Result importData(MultipartFile file, @PathVariable("unitId") Integer unitId) throws IOException {
        return ywPointService.importData(file, unitId);
    }

}

 ycl-server/src/main/java/com/ycl/platform/mapper/YwPointMapper.java

@@ -6,6 +6,7 @@
import com.ycl.platform.domain.entity.YwPoint;
import com.ycl.platform.domain.query.YwPointQuery;
import com.ycl.platform.domain.vo.YwPointVO;
import com.ycl.platform.domain.excel.PointExport;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@@ -40,4 +41,12 @@
     * 通过标签查重点点位或省厅点位
     */
    List<YwPoint> selectByTag(@Param("important")String important, @Param("province")String province);

    /**
     * 导出数据
     *
     * @param query
     * @return
     */
    List<PointExport> export(@Param("query") YwPointQuery query);
}

 ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java

@@ -6,7 +6,10 @@
import com.ycl.platform.domain.form.YwPointForm;
import com.ycl.platform.domain.query.YwPointQuery;
import com.ycl.system.Result;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;

/**
@@ -101,4 +104,21 @@
     * @return 数据
     */
    List<YwPoint> home();

    /**
     * 导出数据
     *
     * @param query
     * @param response
     */
    void export(YwPointQuery query, HttpServletResponse response) throws IOException;

    /**
     * 导入数据
     *
     * @param file
     * @param unitId 运维单位id
     * @return
     */
    Result importData(MultipartFile file, Integer unitId) throws IOException;
}

 ycl-server/src/main/java/com/ycl/platform/service/impl/WorkOrderServiceImpl.java

@@ -79,6 +79,7 @@
        workOrderList.stream().filter(item -> {
            return StringUtils.hasText(item.getSerialNumber()) && Objects.nonNull(item.getStatus()) && StringUtils.hasText(item.getErrorType());
        });
        // TODO 自动下发工单
        int real = workOrderList.size();
        boolean result = this.saveBatch(workOrderList);
        log.info("传入工单总数: {}，实际添加工单数：{}", total, real);

 ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java

@@ -1,16 +1,21 @@
package com.ycl.platform.service.impl;

import com.alibaba.excel.EasyExcel;
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.BaseSelect;
import com.ycl.platform.domain.entity.YwPeople;
import com.ycl.platform.domain.entity.YwPoint;
import com.ycl.platform.domain.entity.YwUnit;
import com.ycl.platform.domain.form.BatchEditPointForm;
import com.ycl.platform.domain.form.YwPointForm;
import com.ycl.platform.domain.query.YwPointQuery;
import com.ycl.platform.domain.vo.YwPointVO;
import com.ycl.platform.domain.excel.PointExport;
import com.ycl.platform.domain.excel.PointSelectHandler;
import com.ycl.platform.mapper.YwPeopleMapper;
import com.ycl.platform.mapper.YwPointMapper;
import com.ycl.platform.service.YwPointService;
@@ -20,16 +25,20 @@
import com.ycl.system.page.PageUtil;
import com.ycl.utils.DateUtils;
import com.ycl.utils.SecurityUtils;
import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.Consumer;
import java.util.stream.Collectors;

/**
@@ -212,4 +221,41 @@
    public List<YwPoint> home() {
        return baseMapper.home();
    }

    @Override
    public void export(YwPointQuery query, HttpServletResponse response) throws IOException {
        // 导出数据
        List<PointExport> exportData = baseMapper.export(query);

        EasyExcel.write(response.getOutputStream(), PointExport.class)
                .sheet("点位更换运维单位")
                .doWrite(exportData);
    }

    @Override
    public Result importData(MultipartFile file, Integer unitId) throws IOException {
        Consumer<List<PointExport>> consumer = (dataList) -> {
            this.updatePoint(dataList, unitId);
        };
        EasyExcel.read(file.getInputStream(), PointExport.class , new CurrencyDataListener(consumer)).headRowNumber(1).doReadAll();
        return Result.ok();
    }

    /**
     * 修改点位的运维单位
     *
     * @param dataList
     * @param unitId
     */
    private void updatePoint(List<PointExport> dataList, Integer unitId) {
        if (CollectionUtils.isEmpty(dataList)) {
            throw new RuntimeException("导入数据不能为空");
        }
        List<String> pointList = dataList.stream().map(PointExport::getSerialNumber).collect(Collectors.toList());
        new LambdaUpdateChainWrapper<>(baseMapper)
                .in(YwPoint::getSerialNumber, pointList)
                .set(YwPoint::getUnitId, unitId)
                .update();
    }

}

 ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml

@@ -75,4 +75,22 @@
            </if>
        </where>
    </select>

    <select id="export" resultType="com.ycl.platform.domain.excel.PointExport">
        SELECT
               m.name as pointName,
               m.serial_number,
               m.ip,
               yu.unit_name
        FROM
             t_yw_point yp
                 INNER JOIN t_monitor m ON yp.serial_number = m.serial_number
                 INNER JOIN t_yw_unit yu ON yu.id = yp.unit_id
        <where>
            <if test="query.pointName != null and query.pointName != ''">
                AND m.name like concat('%', #{query.pointName} ,'%')
            </if>
        </where>
    </select>

</mapper>