【业务功能篇16】Springboot+mybatisplus+ShedLock框架根据一定的逻辑数据处理规则，定时任务生成告警单

ゝ一世哀愁。 2024-03-16 12:32 28阅读 0赞

> 业务场景：当前业务产品，在生产过程中，不乏会出现一些问题，那么就会有人员记录每个问题单，那么这些问题单，业务需要对这些数据，进一步处理，制定了一些规则逻辑，基于这些问题单，进阶生成一个告警单：
> 
> 比如问题单表会有很多内容：最基本的问题发生时间，产品编码，问题描述等等，另外业务还准备了一个表，记录一些词语，比如：失效，爆炸，严重等。
> 
> 那么业务通过指定规则，在一个周期内发生的问题单中，出现相同编码、指定关词语的问题单有2起及以上的，就生成一个告警单W1, 那么这个告警单又是一个新的表单，会记录对应的生成问题单是哪几个单号 ，并且拼接一块存储在一个字段内，比如 pro001,pro002；等等
> 
> 周期：周一到次周一为一个周期，次周一到次次周一为第二个周期…

>  开发的思路：
> 
>  *  需要生成告警单，那么第一步要设计好这个告警单的表，根据业务的需求，进行设计，默认都要给一个id自增列。
>  *  根据规则，进行sql数据处理逻辑
>  *  重点分析的是生成预警单的sql，比较复杂，通过with table as 建立多张临时表，多表之间进行连表逻辑处理
>  *  核心点：预警单表插入数据

由于这个定时任务是系统内执行的，不需要与前端交互，所以我们直接写服务接口

其中有些自定义的工具类，实体类等这里列举：

### DateUtils 日期转换工具类： ###

package com.xxx.xxx.utils;
    
    import org.apache.commons.lang3.StringUtils;
    import org.springframework.stereotype.Component;
    
    import java.sql.Timestamp;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Calendar;
    import java.util.Date;
    import java.util.List;
    
    
    @Component
    public class DateUtils {
    
        private static final SimpleDateFormat PERIOD_DATE_FORMAT = new SimpleDateFormat("yyyyMM");
    
        private static final SimpleDateFormat YEAR_DATE_FORMAT = new SimpleDateFormat("yyyy");
    
        public static final SimpleDateFormat DATE_TIME_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
        private static final SimpleDateFormat TIME_MILLIS_FORMAT = new SimpleDateFormat("yyyyMMdd");
    
        public static final SimpleDateFormat DATE_TIME_DASH_FORMAT = new SimpleDateFormat("yyyy-MM-dd");
    
        public static final SimpleDateFormat FOUR_DIGITS_MONTH_FORMAT = new SimpleDateFormat("yyMM");
    
        public static final String HW_MIN_DATE = "1987-09-15";
    
        public static final String HW_MAX_DATE = "2100-12-31";
    
        public List<String> getLatestTenMonths() {
            List<String> result = new ArrayList<>();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            calendar.add(Calendar.MONTH, -9);
            result.add(FOUR_DIGITS_MONTH_FORMAT.format(calendar.getTime()));
            for (int i = 0; i < 9; i++) {
                calendar.add(Calendar.MONTH, 1);
                result.add(FOUR_DIGITS_MONTH_FORMAT.format(calendar.getTime()));
            }
            return result;
        }
    
    
        public String getCurYear() {
            return YEAR_DATE_FORMAT.format(new Date());
        }
    
        public String getCurPeriod() {
            return PERIOD_DATE_FORMAT.format(new Date());
        }
    
        /**
         * 根据periodId 获取月份信息 例：202106 -> 6月
         * */
        public String getMonth(String periodId) {
            if (StringUtils.isBlank(periodId) || periodId.length() < 6) {
                return "";
            }
            return Integer.parseInt(periodId.substring(4)) + "月";
        }
    
        public String getLastYearPeriod() {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            calendar.add(Calendar.YEAR, -1);
            return PERIOD_DATE_FORMAT.format(calendar.getTime());
        }
    
        public String getLastMonthPeriod() {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            calendar.add(Calendar.MONTH, -1);
            return PERIOD_DATE_FORMAT.format(calendar.getTime());
        }
    
        public String getLastYear() {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            calendar.add(Calendar.YEAR, -1);
            return YEAR_DATE_FORMAT.format(calendar.getTime());
        }
    
        public static String getYMDHMSDateTime(Date date) {
            return DATE_TIME_FORMAT.format(date);
        }
    
        public static String getYMDDateTime(Date date) {
            return DATE_TIME_DASH_FORMAT.format(date);
        }
    
        public static String getTimeMillisString(Date date) {
            return TIME_MILLIS_FORMAT.format(date);
        }
    
        public static Timestamp getSQLTimeStamp(Date date) {
            return new Timestamp(date.getTime());
        }
    }

###  ###

### 请求表单参数实体类： ###

package com.xxx.xxx.QualityBigData.model;
    
    import java.util.*;
    import java.io.Serializable;
    import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
    import com.fasterxml.jackson.annotation.JsonProperty;
    
    
    
    @Data
    @EqualsAndHashCode(callSuper = false)
    @Accessors(chain = true)
    @NoArgsConstructor
     @JsonIgnoreProperties(ignoreUnknown = true)
    
    public class ProdProblemWarnQueryParam implements Serializable {
        private static final long serialVersionUID = 1L;
        
        @JsonProperty("year")
        private Integer year = null;
        
        @JsonProperty("month")
        private Integer month = null;
        
        @JsonProperty("startDate")
        private String startDate = null;
        
        @JsonProperty("endDate")
        private String endDate = null;
        
        @JsonProperty("startPeriod")
        private String startPeriod = null;
        
        @JsonProperty("endPeriod")
        private String endPeriod = null;
        
        @JsonProperty("warnType")
        private List<String> warnType = new ArrayList<String>();
        
        @JsonProperty("page")
        private Integer page = null;
        
        @JsonProperty("pageSize")
        private Integer pageSize = null;
        
        @JsonProperty("supplier")
        private List<String> supplier = new ArrayList<String>();
        
        @JsonProperty("isEffective")
        private List<String> isEffective = new ArrayList<String>();
        
        @JsonProperty("product")
        private List<String> product = new ArrayList<String>();
        
        @JsonProperty("project")
        private List<String> project = new ArrayList<String>();
        
        @JsonProperty("category")
        private List<String> category = new ArrayList<String>();
        
        @JsonProperty("subCategory")
        private List<String> subCategory = new ArrayList<String>();
        
        @JsonProperty("failItems")
        private List<String> failItems = new ArrayList<String>();
        
        @JsonProperty("itemCode")
        private List<String> itemCode = new ArrayList<String>();
        
        @JsonProperty("warnNo")
        private String warnNo = null;
        
        @JsonProperty("warnStatus")
        private List<String> warnStatus = new ArrayList<String>();
        
        @JsonProperty("warnRea")
        private List<String> warnRea = new ArrayList<String>();
        
        @JsonProperty("warnLev")
        private List<String> warnLev = new ArrayList<String>();
        
        @JsonProperty("warnDesc")
        private String warnDesc = null;
        
        @JsonProperty("mesNo")
        private List<String> mesNo = new ArrayList<String>();
        
        @JsonProperty("toFilled")
        private String toFilled = null;
        
        @JsonProperty("codeType")
        private String codeType = null;
        
        @JsonProperty("filledFlag")
        private String filledFlag = null;
        
        @JsonProperty("nodeName")
        private String nodeName = null;
        
        @JsonProperty("po")
        private String po = null;
        
        @JsonProperty("feedBackName")
        private String feedBackName = null;
        
        @JsonProperty("issureProperty")
        private String issureProperty = null;
        
        @JsonProperty("issureDesc")
        private String issureDesc = null;
        
        @JsonProperty("chartList")
        private Map chartList = null;
        
        @JsonProperty("radioType")
        private String radioType = null;
        
        @JsonProperty("topProblem")
        private List<TopProblem> topProblem = new ArrayList<TopProblem>();
        
        @JsonProperty("analysisBy")
        private List<String> analysisBy = new ArrayList<String>();
        
        @JsonProperty("reviewBy")
        private List<String> reviewBy = new ArrayList<String>();
        
    
        
    }

### 告警表单实体类： ###

package com.xxx.xxx.domain.model;
    
    import java.util.Date;
    
    import com.baomidou.mybatisplus.annotation.*;
    
    import java.io.Serializable;
    import java.util.List;
    import java.util.Map;
    
    import com.xxx.xxx.QualityBigData.model.CommonFile;
    import lombok.NoArgsConstructor;
    import lombok.Data;
    import lombok.EqualsAndHashCode;
    import lombok.experimental.Accessors;
    @Data
    @EqualsAndHashCode(callSuper = false)
    @Accessors(chain = true)
    @NoArgsConstructor
    @TableName("dwr_mt_production_problem_warn_f")
    public class MtProductionProblemWarn implements Serializable {
    
        private static final long serialVersionUID = 1L;
    
        @TableId(value = "ID",type = IdType.AUTO)
        private Integer id;
    
        /**
         * 预警单号
         */
        @TableField("WARN_NO")
        private String warnNo;
    
        /**
         * 预警状态
         */
        @TableField("WARN_STATUS")
        private String warnStatus;
    
        /**
         * 预警时间
         */
        @TableField("WARN_DATE")
        private Date warnDate;
    
        /**
         * 预警原因
         */
        @TableField("WARN_REA")
        private String warnRea;
    
        /**
         * 预警周期开始时间
         */
        @TableField("START_PERIOD")
        private Date startPeriod;
    
        /**
         * 预警周期结束时间
         */
        @TableField("END_PERIOD")
        private Date endPeriod;
    
        /**
         * 预警类型
         */
        @TableField("WARN_TYPE")
        private String warnType;
    
        /**
         * 产品
         */
        @TableField("PRODUCT")
        private String product;
    
        /**
         * 项目
         */
        @TableField("PROJECT")
        private String project;
    
        /**
         * 品类
         */
        @TableField("CATEGORY")
        private String category;
    
        /**
         * 子品类
         */
        @TableField("SUB_CATEGORY")
        private String subCategory;
    
        /**
         * 产品编码
         */
        @TableField("ITEM_CODE")
        private String itemCode;
    
        /**
         * 物料编码
         */
        @TableField("FAIL_ITEM")
        private String failItem;
    
        /**
         * 预警描述
         */
        @TableField("WARN_DESC")
        private String warnDesc;
    
        /**
         * mes+问题单号
         */
        @TableField("MES_NO")
        private String mesNo;
    
        /**
         * 供应商
         */
        @TableField("SUPPLIER")
        private String supplier;
    
        /**
         * 不良数
         */
        @TableField("BAD_QTY")
        private Integer badQty;
    
        /**
         * 分析人
         */
        @TableField("ANALYSIS_BY")
        private String analysisBy;
    
        /**
         * 转处理人
         */
        @TableField("PROCESSED_BY")
        private String processedBy;
    
        /**
         * 评审人
         */
        @TableField("REVIEW_BY")
        private String reviewBy;
    
        /**
         * 编码累计预警单数
         */
        @TableField("CODE_ACC_QTY")
        private Integer codeAccQty;
    
        /**
         * 分析状态
         */
        @TableField("ANALYSIS_STATUS")
        private String analysisStatus;
    
        /**
         * 在制-四在品是否已排查完成
         */
        @TableField("IN_PROCESS")
        private String inProcess;
    
        /**
         * 在库-四在品是否已排查完成
         */
        @TableField("IN_STORE")
        private String inStore;
    
        /**
         * 在途-四在品是否已排查完成
         */
        @TableField("IN_TRANSIT")
        private String inTransit;
    
        /**
         * 在网-四在品是否已排查完成
         */
        @TableField("IN_NET")
        private String inNet;
    
        /**
         * 围堵措施
         */
        @TableField("ENCLOSE_MEASURE")
        private String encloseMeasure;
    
        /**
         * 根因分析-产生原因
         */
        @TableField("OCCUR_CAUSE")
        private String occurCause;
    
        /**
         * 纠正措施-针对产生原因
         */
        @TableField("OCCUR_MEASURE")
        private String occurMeasure;
    
        /**
         * 纠正措施-针对流出原因
         */
        @TableField("OUTFLOW_CAUSE")
        private String outflowCause;
    
        /**
         * 长期措施
         */
        @TableField("SOLUTION")
        private String solution;
    
        /**
         * 横向排查清零
         */
        @TableField("HORIZONTAL")
        private String horizontal;
    
        /**
         * 是否分析完成
         */
        @TableField("IS_ANALYSE_FINISH")
        private String isAnalyseFinish;
    
        /**
         * 是否有效预警
         */
        @TableField("IS_EFFECTIVE")
        private String isEffective;
    
        /**
         * 无效预警原因
         */
        @TableField("INVALID_REASON")
        private String invalidReason;
    
        /**
         * 评审意见
         */
        @TableField("REVIEW_COMMENT")
        private String reviewComment;
    
        /**
         * 是否闭环
         */
        @TableField(value = "IS_CLOSED",updateStrategy = FieldStrategy.IGNORED)
        private String isClosed;
    
        /**
         * 闭环时间
         */
        @TableField("CLOSED_DATE")
        private Date closedDate;
    
        @TableField("CREATION_DATE")
        private Date creationDate;
    
        @TableField("LAST_UPDATE_DATE")
        private Date lastUpdateDate;
    
        /**
         * mes单号数量
         */
        @TableField(exist = false)
        private Integer mesCount;
    
        /**
         * 团队成员
         */
        @TableField(exist = false)
        private String member;
    
        /**
         * 日志列表
         */
        @TableField(exist = false)
        private List<MtProductionProblemLog> logList;
    
        /**
         * businessKey
         */
        @TableField(exist = false)
        private Map<String,String> businessKey;
    
        /**
         * 文件列表
         */
        @TableField(exist = false)
        private List<CommonFile> files;
    
        @TableField("MES_DEFECT_POSITION")
        private String mesDefectPosition;
    }

###  ###

### SpringUtils 工具类：用来可以获取springboot框架当前使用的环境 等方法 ###

package com.xxx.xxx.utils;
    
    import org.springframework.beans.BeansException;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.ApplicationContextAware;
    import org.springframework.stereotype.Component;
    
    import java.util.Objects;
    
    @Component
    public class SpringBeanUtils implements ApplicationContextAware {
        private static ApplicationContext applicationContext;
    
        @Override
        public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
            if (SpringBeanUtils.applicationContext == null) {
                SpringBeanUtils.applicationContext = applicationContext;
            }
        }
    
        public static ApplicationContext getApplicationContext() {
            return applicationContext;
        }
    
        public static Object getBean(String name) {
            return getApplicationContext().getBean(name);
        }
    
        public static <T> T getBean(Class<T> clazz) {
            return getApplicationContext().getBean(clazz);
        }
    
        public static <T> T getBean(String name, Class<T> clazz) {
            return getApplicationContext().getBean(name, clazz);
        }
    
        public static String getActiveProfile() {
            return getApplicationContext().getEnvironment().getActiveProfiles()[0];
        }
    
        public static boolean isTestProfile() {
            return !Objects.equals(getActiveProfile(), "prod");
        }
    }

### service层 接口类： ###

import java.util.function.Function;
    
    public interface ProdMesKeywordWarnRuleService {
    
        //生产预警单方法
        void createPreWarningPeriodically();
    
        void createPreWarningPeriodicallyTest(String[] period, Function<String, Boolean> isDoSync);
    
        void sendMsOnTen();
    }

###  ###

### service层 接口实现类： ###

package xxx.impl.matwarn;
    
    import com.google.common.base.Joiner;
    import com.google.common.collect.Lists;
    import xxx.model.ProdProblemWarnQueryParam;
    import xxx.domain.dto.espDto;
    import xxx.domain.dto.TaskDto;
    import xxx.domain.model.MtProductionProblemMessage;
    import xxx.domain.model.MtProductionProblemWarn;
    import xxx.service.dao.iservice.matwarn.IProdProblemWarnMsgService;
    import xxx.service.impl.dao.iservice.matwarn.IProdProblemWarnLogServiceImpl;
    import xxx.service.impl.dao.iservice.matwarn.IProdProblemWarnServiceImpl;
    import xxx.service.matwarn.ProdMesKeywordWarnRuleService;
    import xxx.service.matwarn.ProdProblemWarnService;
    import xxx.utils.DateUtils;
    import xxx.utils.JsonUtils;
    import xxx.utils.MsgUtils;
    import xxx.utils.SpringBeanUtils;
    import org.apache.commons.lang3.StringUtils;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.transaction.support.TransactionSynchronization;
    import org.springframework.transaction.support.TransactionSynchronizationManager;
    
    import javax.annotation.Resource;
    import java.time.DayOfWeek;
    import java.time.LocalDate;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.Date;
    import java.util.LinkedHashSet;
    import java.util.List;
    import java.util.Set;
    import java.util.function.Function;
    
    import static xxx.service.impl.dao.iservice.matwarn.IProdProblemWarnMsgServiceImpl.MESSAGE_NO_SEND;
    
    @Service
    public class ProdMesKeywordWarnRuleServiceImpl implements ProdMesKeywordWarnRuleService {
        @Resource
        private IProdProblemWarnServiceImpl iProdProblemWarnService;
    
        @Resource
        private IProdProblemWarnLogServiceImpl iProdProblemWarnLogService;
    
        @Resource
        private IProdProblemWarnMsgService iProdProblemWarnMsgService;
    
        @Resource
        private ProdProblemWarnService prodProblemWarnService;
    
        private final String DEVELOPER_ACCOUNT = "用户A";
    
        private final String BUSINESS_ACCOUNT = "用户B";
    
        private final String WARN_TYPE = "关键字预警";
    
        private final String WARN_NO_PREFIX = "PPKW";
    
        /*触发预警的问题单阈值*/
        private final int ISSUE_THRESHOLD_WHEN_WARN = 2;
    
        private String jumpUrl;
        @Value("${Url}")
        public void setJumpUrl(String prefix) {
            jumpUrl = prefix + "/productWarning?warnNo=";
        }
    
        @Transactional
        @Override
        public void createPreWarningPeriodically() {
    
            //获取时间周期数组[上周一，上周日，本周一，本周日]
            String[] period = getPeriodArray();
    
            //获取上周周期，根据编码，关键字描述分组预警的问题单情况
            List<MtProductionProblemWarn> preCycleList = iProdProblemWarnService.getMesKeywordPreWarning(ISSUE_THRESHOLD_WHEN_WARN, WARN_TYPE, period[0], period[1]);
    
            //获取本周周期预警情况
            List<MtProductionProblemWarn> curCycleList = iProdProblemWarnService.getMesKeywordPreWarning(ISSUE_THRESHOLD_WHEN_WARN, WARN_TYPE, period[2], period[3]);
            preCycleList.addAll(curCycleList);
    
    
            //将上周本周的预警情况传参，调用方法执行
            doSyncWarnData(preCycleList, null);
    
            //超期7天未处理预警，将推送信息插入推送表中
            create7DayMsg();
    
            
            TransactionSynchronizationManager.registerSynchronization(new TransactionSynchronization() {
                @Override
                public void afterCommit() {
                    //整个预警生成事务执行结束后，需要推送的数据先已保存好数据库，然后再调用方法
                    //进行数据库对应的需要发送推送的记录 进行推送
                    sendMsg();
                }
            });
        }
    
        @Transactional
        @Override
        public void createPreWarningPeriodicallyTest(String[] period, Function<String, Boolean> isDoSync) {
            List<MtProductionProblemWarn> listToSync = iProdProblemWarnService.getMesKeywordPreWarning(ISSUE_THRESHOLD_WHEN_WARN, WARN_TYPE, period[0], period[1]);
            List<MtProductionProblemWarn> curCycleList = iProdProblemWarnService.getMesKeywordPreWarning(ISSUE_THRESHOLD_WHEN_WARN, WARN_TYPE, period[2], period[3]);
            listToSync.addAll(curCycleList);
            doSyncWarnData(listToSync, isDoSync);
            create7DayMsg();
            TransactionSynchronizationManager.registerSynchronization(new TransactionSynchronization() {
                @Override
                public void afterCommit() {
                    // 查询发送应用号和待办
                    sendMsg();
                }
            });
        }
    
        @Override
        public void sendMsOnTen() {
            prodProblemWarnService.sendMsOnTen();
        }
    
        private void doSyncWarnData(List<MtProductionProblemWarn> listToSync, Function<String, Boolean> isDoSync) {
    
            //获取当前已经生成预警单的最大单号数
            int index = iProdProblemWarnService.getMaxWarnNo(WARN_TYPE);
            for (MtProductionProblemWarn toSyncData : listToSync) {
                if (isDoSync != null && !isDoSync.apply(toSyncData.getItemCode())) {
                    continue;
                }
    
                //给当前的预警单再次确认 设置分析人和评审人 因为可能表中是空值，需要给个默认人
                setProcessor(toSyncData);
    
                // 同预警周期内 同编码同关键字 已生成的预警单是否有 
                // 如果不存在，那么就返回null  mp内置的 selectOne的封装机制
                MtProductionProblemWarn existedOne = getExistOne(toSyncData);
                if (existedOne == null) {
                    如果数据库中没有存在相同预警单，并且当前的预警单 同编码同关键字分组内小于2个问题单的 就不生成单据 跳过当前这一条问题单
                    if (toSyncData.getMesCount() < ISSUE_THRESHOLD_WHEN_WARN) {
                        continue;
                    }
    
                
               //来到这里说明有2个及以上的问题单，符合告警规则 生成下个预警单号，然后插入到告警表单中      iProdProblemWarnService.insertKeywordIssueWhenSync(preProcessWhenInsertNewIssue(toSyncData, ++index));
    
                    //插入日志表单
                    iProdProblemWarnLogService.insertStartLog(toSyncData.getWarnNo());
                    
            //推送应用号信息插入到 推送信息表中
            insertAppNotifyWhenAddNewIssue(toSyncData);
    
                    //分析人生成待办
                    insertAnalystUndoWhenAddNewIssue(toSyncData);
                    continue;
                }
    
                if (existedOne.getWarnStatus() == null) {
                    iProdProblemWarnService.updateExistedKeywordWarnIssueByStatus(preProcessWhenUpdateIssue(existedOne, toSyncData));
                    continue;
                }
    
                // 已生成预警单（已关闭），则重开问题单
                if (existedOne.getWarnStatus().equals("已关闭")) {
                    // 注意：状态需要修改为分析中
                    iProdProblemWarnService.updateExistedKeywordWarnIssueByStatus(preProcessWhenUpdateIssue(existedOne, toSyncData));
                    insertAppNotifyWhenIssueRestart(toSyncData, existedOne);
                    insertUndoWhenProblemRestart(existedOne);
                    iProdProblemWarnLogService.insertReStartWarnIssueLog(existedOne.getWarnNo(), toSyncData.getMesNo());
                } else { // 若新增符同类预警的MES+问题单，则在原有预警单进行累加
                    iProdProblemWarnService.updateExistedKeywordWarnIssueByStatus(preProcessWhenUpdateIssue(existedOne, toSyncData));
                    insertAppNotifyWhenIssueRepeat(toSyncData, existedOne);
                    iProdProblemWarnLogService.insertAddMesPlusIssueLog(existedOne.getWarnNo(), toSyncData.getMesNo());
                }
            }
        }
    
        private String[] getPeriodArray() {
            LocalDate curCycleStartDate = LocalDate.now().with(DayOfWeek.MONDAY);
            LocalDate curCycleEndDate = curCycleStartDate.plusDays(6);
            LocalDate preCycleStartDate = curCycleStartDate.minusDays(7);
            LocalDate preCycleEndDate = curCycleStartDate.minusDays(1);
    
            return new String[]{preCycleStartDate.toString(), preCycleEndDate.toString(),
                    curCycleStartDate.toString(), curCycleEndDate.toString()};
        }
    
        private void setProcessor(MtProductionProblemWarn warn) {
            String default_account = "952";
            String analyst = StringUtils.isBlank(warn.getAnalysisBy()) ? default_account : warn.getAnalysisBy().split(",")[0];
            String reviewer = StringUtils.isBlank(warn.getReviewBy()) ? default_account : warn.getReviewBy().split(",")[0];
            warn.setAnalysisBy(analyst);
            warn.setReviewBy(reviewer);
        }
    
        private MtProductionProblemWarn preProcessWhenInsertNewIssue(MtProductionProblemWarn newData, int warnNoSuffix) {
            newData.setWarnNo(iProdProblemWarnService.getWarnNo(WARN_NO_PREFIX, warnNoSuffix));
            String warnDesc = String.format("预警单号：%s，【编码】%s，【预警关键字】%s，【触发次数】%s次，【MES+问题单号】%s",
                    newData.getWarnNo(), newData.getItemCode(), newData.getWarnRea(), newData.getMesCount(), newData.getMesDefectPosition());
            newData.setWarnDesc(warnDesc);
            return newData;
        }
    
        /**
         * 推送应用号,应用号推送指定提醒内容给该品类团队成员
         */
        private void insertAppNotifyWhenAddNewIssue(MtProductionProblemWarn newData) {
            espDto espDto = new espDto();
            espDto.setTitle("生产问题关键字预警");
            String desc = String.format("当前有一个预警单号：%s，【编码】%s，【预警关键字】%s，【触发次数】%s次，【MES+问题单号】%s，请分析人%s尽快处理分析。",
                    newData.getWarnNo(), newData.getItemCode(), newData.getWarnRea(), newData.getMesCount(), newData.getMesDefectPosition(), newData.getAnalysisBy());
            espDto.setContent(desc);
            espDto.setJumpUrl(jumpUrl + newData.getWarnNo());
            espDto.setToUser(SpringBeanUtils.isTestProfile() ? MsgUtils.getAccount(DEVELOPER_ACCOUNT) : MsgUtils.getAccount(newData.getAnalysisBy()));
            iProdProblemWarnMsgService.insertAppNotify(espDto, newData.getWarnNo(), MESSAGE_NO_SEND);
        }
    
        /**
         * 分析人生成待办
         */
        private void insertAnalystUndoWhenAddNewIssue(MtProductionProblemWarn newData) {
            TaskDto dto = new TaskDto();
            dto.setAppUrl(jumpUrl + newData.getWarnNo());
            dto.setTaskDesc("【" + newData.getWarnNo() + "】【未处理】质量大数据-生产问题关键字预警");
            dto.setTaskURL(jumpUrl + newData.getWarnNo());
            dto.setHandler(SpringBeanUtils.isTestProfile() ? DEVELOPER_ACCOUNT : newData.getAnalysisBy());
            dto.setApplicant("system");
            iProdProblemWarnMsgService.insertAnalystUndo(dto, newData.getWarnNo(), MESSAGE_NO_SEND);
        }
    
        /**
         * 问题重启评审人生成代办（新增同类问题）
         * */
        private void insertUndoWhenProblemRestart(MtProductionProblemWarn vo) {
            String[] review = vo.getReviewBy().split(",");
            for (String s : review) {
                TaskDto dto = new TaskDto();
                dto.setAppUrl(jumpUrl + vo.getWarnNo());
                dto.setTaskDesc("【" + vo.getWarnNo() + "】【新增同类问题】质量大数据-生产问题关键字预警");
                dto.setTaskURL(jumpUrl + vo.getWarnNo());
                dto.setHandler(SpringBeanUtils.isTestProfile() ? DEVELOPER_ACCOUNT : s);
                dto.setApplicant("system");
                iProdProblemWarnMsgService.insertReviewerUndo(dto, vo.getWarnNo(), MESSAGE_NO_SEND);
            }
        }
    
        private MtProductionProblemWarn preProcessWhenUpdateIssue(MtProductionProblemWarn existedOne, MtProductionProblemWarn newOne) {
            existedOne.setMesNo(String.join(",", deduplicate(existedOne.getMesNo(), newOne.getMesNo())));
            existedOne.setMesCount(deduplicate(existedOne.getMesNo(), newOne.getMesNo()).size());
            existedOne.setMesDefectPosition(String.join(",", deduplicate(existedOne.getMesDefectPosition(), newOne.getMesDefectPosition())));
    
            existedOne.setFailItem(String.join(",", deduplicate(existedOne.getFailItem(), newOne.getFailItem())));
            existedOne.setSupplier(String.join(",", deduplicate(existedOne.getSupplier(), newOne.getSupplier())));
            existedOne.setBadQty(existedOne.getBadQty() + newOne.getBadQty());
    
            String warnDesc = String.format("预警单号：%s，【编码】%s，【预警关键字】%s，【触发次数】%s次，【MES+问题单号】%s。",
                    existedOne.getWarnNo(), existedOne.getItemCode(), existedOne.getWarnRea(), existedOne.getMesCount(), existedOne.getMesDefectPosition());
            existedOne.setWarnDesc(warnDesc);
            existedOne.setLastUpdateDate(new Date());
    
            return existedOne;
        }
    
        private Set<String> deduplicate(String str1, String str2) {
            Set<String> result = new LinkedHashSet<>();
            if (!StringUtils.isBlank(str1)) {
                result.addAll(Arrays.asList(str1.split(",")));
            }
            if (!StringUtils.isBlank(str2)) {
                result.addAll(Arrays.asList(str2.split(",")));
            }
            return result;
        }
    
        /**
         * 未关闭，新增同类问题
         *  应用号推送内容提醒分析人尽快处理分析
         */
        private void insertAppNotifyWhenIssueRepeat(MtProductionProblemWarn newData, MtProductionProblemWarn existData) {
            espDto espDto = new espDto();
            espDto.setTitle("生产问题关键字预警");
            String desc = String.format("当前有一个预警单%s，【编码】%s，【预警关键字】%s\n"
                            + "新增同类问题：【触发次数新增】%s，【MES+问题单号新增】%s，请分析人尽快处理分析，评审人重点关注",
                    existData.getWarnNo(), existData.getItemCode(), existData.getWarnRea(), newData.getMesCount(), newData.getMesDefectPosition());
            espDto.setContent(desc);
            espDto.setJumpUrl(jumpUrl + existData.getWarnNo());
            espDto.setToUser(SpringBeanUtils.isTestProfile() ? MsgUtils.getAccount(DEVELOPER_ACCOUNT) : MsgUtils.getAccount(newData.getAnalysisBy()));
            iProdProblemWarnMsgService.insertSentOnTen(espDto, existData.getWarnNo(), MESSAGE_NO_SEND);
        }
    
        /**
         * 重开问题时需要推送给历史相关人员
         *  应用号推送内容
         */
        private void insertAppNotifyWhenIssueRestart(MtProductionProblemWarn newData, MtProductionProblemWarn existData) {
            espDto espDto = new espDto();
            espDto.setTitle("生产问题关键字预警");
            String desc = String.format("当前有一个已关闭的预警单%s，【编码】%s，【预警关键字】%s\n"
                            + "新增同类问题：【触发次数】新增%s，【MES+问题单号新增】%s，请分析人尽快处理分析，评审人重点关注",
                    existData.getWarnNo(), existData.getItemCode(), existData.getWarnRea(), newData.getMesCount(), newData.getMesDefectPosition());
            espDto.setContent(desc);
            espDto.setJumpUrl(jumpUrl + existData.getWarnNo());
            String dealUser;
            if (SpringBeanUtils.isTestProfile()) {
                dealUser = MsgUtils.getAccount(DEVELOPER_ACCOUNT);
            } else {
                List<String> userList = new ArrayList<>();
                userList.add(MsgUtils.getAccount(StringUtils.isNotEmpty(existData.getProcessedBy()) ? existData.getProcessedBy() : existData.getAnalysisBy()));
                userList.add(MsgUtils.getAccount(existData.getReviewBy()));
                userList.add(MsgUtils.getAccount(BUSINESS_ACCOUNT));
                userList.add(MsgUtils.getAccount(DEVELOPER_ACCOUNT));
                dealUser = Joiner.on(",").join(userList);
            }
            espDto.setToUser(dealUser);
            iProdProblemWarnMsgService.insertSentOnTen(espDto, existData.getWarnNo(), MESSAGE_NO_SEND);
        }
    
        /**
         * 超过7天状态还未关闭，第七天开始往后每天进行一次应用号推送指定提醒内容给分析人AB 评审人CD，直到关闭，则无需再推送
         */
        public void create7DayMsg() {
            List<MtProductionProblemWarn> list = iProdProblemWarnService.getOpenMoreThanSevenDaysIssues(WARN_TYPE);
            for (MtProductionProblemWarn item : list) {
                insertAppNotifyWhenOpenIssueMoreSeven(item);
            }
        }
    
        /**
         * 发送应用号和待办
         */
        private void sendMsg() {
            List<MtProductionProblemMessage> list = iProdProblemWarnMsgService.getTodayNotSend();
            for (MtProductionProblemMessage msg : list) {
                if (!msg.getWarnNo().startsWith(WARN_NO_PREFIX)) {
                    continue;
                }
                // 应用号
                if (msg.getType().equals("0")) {
                    espDto espDto = JsonUtils.jsonToObj(msg.getContent(), espDto.class);
                    MsgUtils.sendespMessage(espDto);
                    msg.setIsSend("1");
                    msg.setLastUpdateDate(new Date());
                    iProdProblemWarnMsgService.updateOneMsg(msg);
                }
                // 待办
                if (msg.getType().equals("1") || msg.getType().equals("2")) {
                    TaskDto dto = JsonUtils.jsonToObj(msg.getContent(), TaskDto.class);
                    String taskId = MsgUtils.postTask(dto);
                    msg.setIsSend("1");
                    msg.setTaskId(taskId);
                    msg.setLastUpdateDate(new Date());
                    iProdProblemWarnMsgService.updateOneMsg(msg);
                }
            }
        }
    
        private void insertAppNotifyWhenOpenIssueMoreSeven(MtProductionProblemWarn item) {
            espDto espDto = new espDto();
            espDto.setJumpUrl(jumpUrl);
            espDto.setTitle("生产问题预警");
            // 分析中请分析人处理，评审中请评审人处理
            String desc = String.format("当前有一个预警单%s超过7天还未关闭，【编码】%s，【预警关键字】%s，【预警状态】%s，" + "请%s人%s尽快处理%s。",
                    item.getWarnNo(), item.getItemCode(), item.getWarnRea(), item.getWarnStatus(),
                    item.getWarnStatus().equals("评审中") ? "评审" : "分析",
                    item.getWarnStatus().equals("评审中") ? item.getReviewBy() :
                            (StringUtils.isNotEmpty(item.getProcessedBy()) ? item.getProcessedBy() : item.getAnalysisBy()),
                    item.getWarnStatus().equals("评审中") ? "评审" : "分析");
            espDto.setContent(desc);
    
            String dealUser;
            if (SpringBeanUtils.isTestProfile()) {
                dealUser = MsgUtils.getAccount(DEVELOPER_ACCOUNT);
            } else {
                List<String> userList = new ArrayList<>();
                if (item.getWarnStatus().equals("评审中")) {
                    userList.add(MsgUtils.getAccount(item.getReviewBy()));
                } else {
                    userList.add(MsgUtils.getAccount(
                            StringUtils.isNotEmpty(item.getProcessedBy()) ? item.getProcessedBy() : item.getAnalysisBy()));
                }
                userList.add(MsgUtils.getAccount(BUSINESS_ACCOUNT));
                userList.add(DEVELOPER_ACCOUNT);
                dealUser = Joiner.on(",").join(userList);
            }
            espDto.setToUser(dealUser);
            iProdProblemWarnMsgService.insertAppNotify(espDto, item.getWarnNo(), MESSAGE_NO_SEND);
        }
    
        // 同预警周期内同编码同关键字
        private MtProductionProblemWarn getExistOne(MtProductionProblemWarn toSyncData) {
            ProdProblemWarnQueryParam param = new ProdProblemWarnQueryParam();
            param.setStartPeriod(DateUtils.getYMDDateTime(toSyncData.getStartPeriod()));
            param.setEndPeriod(DateUtils.getYMDDateTime(toSyncData.getEndPeriod()));
            param.setItemCode(Lists.newArrayList(toSyncData.getItemCode()));
            param.setWarnRea(Lists.newArrayList(toSyncData.getWarnRea()));
            param.setWarnType(Lists.newArrayList(WARN_TYPE));
            return iProdProblemWarnService.findOneData(param);
        }
    }

### dao层 接口类 集成mp的IService接口 ###

package xxx.xxx.service.dao.iservice.matwarn;
    
    import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    import com.baomidou.mybatisplus.extension.service.IService;
    import xxx.xxx.QualityBigData.model.CommonFile;
    import xxx.xxx.QualityBigData.model.ProdProblemWarnQueryParam;
    import xxx.xxx.QualityBigData.model.ProdProblemWarnUpdateParam;
    import xxx.xxx.domain.dto.DistributionWarnDto;
    import xxx.xxx.domain.model.MtProductionProblemWarn;
    import xxx.xxx.domain.model.QbdReportTopProblem;
    
    import java.util.List;
    import java.util.Map;
    
    public interface IProdProblemWarnService extends IService<MtProductionProblemWarn> {
    
        List<MtProductionProblemWarn> getToSyncDataList(String startPeriod, String endPeriod);
    
        int updateMatWarnLock();
    
        int resetMatWarnLock();
    
        int getMaxWarnNo(String warnType);
    
        MtProductionProblemWarn findOneDataByFailItemAndPeriod(String failItem, String startPeriod, String endPeriod, String warnType);
    
        MtProductionProblemWarn findOneData(ProdProblemWarnQueryParam param);
    
        int insertNewIssueWhenSync(String prefix, List<MtProductionProblemWarn> nList, MtProductionProblemWarn item, int index);
    
        int insertKeywordIssueWhenSync(MtProductionProblemWarn item);
    
        int updateExistedWarnIssueByStatus(MtProductionProblemWarn vo, MtProductionProblemWarn toSyncData);
    
        int updateExistedKeywordWarnIssueByStatus(MtProductionProblemWarn vo);
    
        List<MtProductionProblemWarn> getOpenMoreThanSevenDaysIssues(String warnType);
    
        Page<MtProductionProblemWarn> getPagedFilterData(ProdProblemWarnQueryParam queryParam);
    
        List<MtProductionProblemWarn> getFilterData(ProdProblemWarnQueryParam queryParam);
    
        List<Map<String, String>> getBusinessKey(List<String> mesPlusNoList);
    
        MtProductionProblemWarn findOneByWarnNo(String warnNo);
    
        int updateIssueWhenTransHandler(ProdProblemWarnUpdateParam updateParam);
    
        int updateIssueWhenSaveAnalysis(ProdProblemWarnUpdateParam updateParam, MtProductionProblemWarn vo);
    
        int updateIssueWhenSaveReview(ProdProblemWarnUpdateParam updateParam);
    
        List<String> getMemberList(String category);
    
        List<Map<String, String>> getCountTag(String w3Account);
    
        List<DistributionWarnDto> getQtyTrends(ProdProblemWarnQueryParam queryParam);
    
        List<DistributionWarnDto> getQtyStatus(ProdProblemWarnQueryParam queryParam);
    
        List<DistributionWarnDto> getTopTenReason(ProdProblemWarnQueryParam queryParam);
    
        List<CommonFile> findFile(String warnNo);
    
        List<MtProductionProblemWarn> getMesKeywordPreWarning(int issueThreshold, String warnType, String startPeriod, String endPeriod);
    
        String getWarnNo(String prefix, Integer no);
    
        List<String> getDropListByType(String type);
    
        List<DistributionWarnDto> getEffectiveRate(ProdProblemWarnQueryParam queryParam);
    
        List<QbdReportTopProblem> getRepTopProblem(ProdProblemWarnQueryParam queryParam);
    
        List<Map<String, String>> getWarnCardData();
    }

### dao层的接口实现类 继承MP的ServiceImpl类 ###

package xxx.xxx.service.impl.dao.iservice.matwarn;
    
    import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
    import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
    import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import com.google.common.collect.Lists;
    import xxx.xxx.QualityBigData.impl.CommonFileServiceDelegateImpl;
    import xxx.xxx.QualityBigData.model.CommonFile;
    import xxx.xxx.QualityBigData.model.ProdProblemWarnQueryParam;
    import xxx.xxx.QualityBigData.model.ProdProblemWarnUpdateParam;
    import xxx.xxx.domain.dto.DistributionWarnDto;
    import xxx.xxx.domain.model.MtProductionProblemWarn;
    import xxx.xxx.domain.model.QbdReportTopProblem;
    import xxx.xxx.service.UserService;
    import xxx.xxx.service.dao.iservice.matwarn.IProdProblemWarnService;
    import xxx.xxx.service.dao.mapper.MtProductionProblemWarnMapper;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.lang3.StringUtils;
    import org.springframework.stereotype.Component;
    
    import javax.annotation.Resource;
    import java.text.NumberFormat;
    import java.time.LocalDate;
    import java.time.format.DateTimeFormatter;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    import java.util.stream.Collectors;
    
    @Slf4j
    @Component
    public class IProdProblemWarnServiceImpl extends ServiceImpl<MtProductionProblemWarnMapper, MtProductionProblemWarn>
        implements IProdProblemWarnService {
    
        @Resource
        private UserService userService;
        @Resource
        private CommonFileServiceDelegateImpl commonFileService;
    
        @Override
        public List<MtProductionProblemWarn> getToSyncDataList(String startPeriod, String endPeriod) {
            ProdProblemWarnQueryParam queryParam = new ProdProblemWarnQueryParam();
            queryParam.setStartPeriod(startPeriod);
            queryParam.setEndPeriod(endPeriod);
            queryParam.setCategory(Lists.newArrayList("阻容", "二三极管", "MOS管", "磁器件", "辅料"));
            return baseMapper.getNewWarnData(queryParam);
        }
    
        @Override
        public int updateMatWarnLock() {
            int count = baseMapper.updateLock("ProductionProblemWarnSchedule");
            log.info("update数量：" + count);
            return count;
        }
    
        @Override
        public int resetMatWarnLock() {
            return baseMapper.resetLock("ProductionProblemWarnSchedule");
        }
    
        @Override
        public int getMaxWarnNo(String warnType) {
            return baseMapper.getMaxWarnNo(warnType);
        }
    
        @Override
        public MtProductionProblemWarn findOneDataByFailItemAndPeriod(String failItem, String startPeriod, String endPeriod, String warnType) {
            QueryWrapper<MtProductionProblemWarn> wr = new QueryWrapper<>();
            wr.eq("FAIL_ITEM", failItem);
            wr.eq("START_PERIOD", startPeriod);
            wr.eq("END_PERIOD", endPeriod);
            wr.eq("WARN_TYPE", warnType);
            wr.last("limit 1");
            return baseMapper.selectOne(wr);
        }
    
        @Override
        public MtProductionProblemWarn findOneData(ProdProblemWarnQueryParam param) {
            LambdaQueryWrapper<MtProductionProblemWarn> wrapper = getCommonWrapper(param);
            wrapper.last("limit 1");
            return baseMapper.selectOne(wrapper);
        }
    
        @Override
        public int insertNewIssueWhenSync(String prefix, List<MtProductionProblemWarn> nList, MtProductionProblemWarn item, int index) {
            item.setWarnNo(getWarnNo(prefix, index + 1));
            String warnDesc = String.format("预警单号：%s，【MES+问题单号】%s【编码】%s，总共累计出现%sPSC不良",
                    item.getWarnNo(), item.getMesNo(), item.getFailItem(), item.getBadQty());
            item.setWarnDesc(warnDesc);
            item.setCodeAccQty(getCodeAccQty(nList, item));
            return doInsertIssue(item);
        }
    
        @Override
        public int insertKeywordIssueWhenSync(MtProductionProblemWarn item) {
            return doInsertIssue(item);
        }
    
        private int doInsertIssue(MtProductionProblemWarn item) {
            item.setWarnStatus("未处理");
            item.setWarnDate(new Date());
            item.setCreationDate(new Date());
            return baseMapper.insert(item);
        }
    
        /**
         * 计算编码累计预警单数
         *
         * @return 返回
         */
        private Integer getCodeAccQty(List<MtProductionProblemWarn> nList, MtProductionProblemWarn item) {
            int dbCount = baseMapper.getCodeAccQty(item.getFailItem(), item.getWarnType()); // 不良数量预警
            Long lCount = nList.stream().filter(f -> f.getFailItem().equals(item.getFailItem()) && f.getMesCount() >= 2).count();
            return (dbCount + lCount.intValue());
        }
    
        @Override
        public int updateExistedWarnIssueByStatus(MtProductionProblemWarn existedOne, MtProductionProblemWarn toSyncOne) {
            processStatueWhenUpdated(existedOne);
    
            List<String> mesNo = new ArrayList<>(Arrays.asList(existedOne.getMesNo().split(",")));
            List<String> mesNo2 = new ArrayList<>(Arrays.asList(toSyncOne.getMesNo().split(",")));
            mesNo.addAll(mesNo2);
            mesNo = mesNo.stream().distinct().collect(Collectors.toList());
            existedOne.setMesNo(String.join(",", mesNo));
            existedOne.setBadQty(existedOne.getBadQty() + toSyncOne.getBadQty());
            String warnDesc = String.format("预警单号：%s，【MES+问题单号】%s【编码】%s，总共累计出现%sPSC不良",
                    existedOne.getWarnNo(), existedOne.getMesNo(), existedOne.getFailItem(), existedOne.getBadQty());
            existedOne.setWarnDesc(warnDesc);
            existedOne.setLastUpdateDate(new Date());
            return baseMapper.updateById(existedOne);
        }
    
        @Override
        public int updateExistedKeywordWarnIssueByStatus(MtProductionProblemWarn existedOne) {
            processStatueWhenUpdated(existedOne);
            return baseMapper.updateById(existedOne);
        }
    
        private void processStatueWhenUpdated(MtProductionProblemWarn model) {
            String warnStatus = model.getWarnStatus();
            if (warnStatus != null) {
                if (model.getWarnStatus().equals("评审中")) {
                    model.setIsAnalyseFinish("否"); // 是否分析完成 设置为 否
                    model.setIsClosed(null); // 是否闭环 清空状态
                    model.setAnalysisStatus("open");
                }else if(model.getWarnStatus().equals("已关闭")) {
                    // 问题单关闭后 同周期内 同类型新增mes+问题单时，需要再将状态改回 评审中 给评审人判断 是否可以闭环 或者驳回给分析人
                    model.setWarnStatus("评审中");
                    model.setIsClosed(null);
                }
            }
        }
    
        @Override
        public List<MtProductionProblemWarn> getOpenMoreThanSevenDaysIssues(String warnType) {
            QueryWrapper<MtProductionProblemWarn> wr = new QueryWrapper<>();
            wr.ne("WARN_STATUS", "已关闭");
            wr.ge("(TO_DAYS(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) - TO_DAYS(WARN_DATE) + 1)", "7");
            wr.eq("WARN_TYPE", warnType);
            return baseMapper.selectList(wr);
        }
    
        @Override
        public Page<MtProductionProblemWarn> getPagedFilterData(ProdProblemWarnQueryParam queryParam) {
            Page<MtProductionProblemWarn> pageInfo = new Page<>();
            pageInfo.setCurrent(queryParam.getPage() == null ? 1 : queryParam.getPage());
            pageInfo.setSize(queryParam.getPageSize() == null ? 10 : queryParam.getPageSize());
            return baseMapper.selectFilterData(getCurrUserAccount(), queryParam, pageInfo);
        }
    
        @Override
        public List<MtProductionProblemWarn> getFilterData(ProdProblemWarnQueryParam queryParam) {
            return baseMapper.selectFilterData(getCurrUserAccount(), queryParam);
        }
    
        @Override
        public List<Map<String, String>> getBusinessKey(List<String> mesPlusNoList) {
            return baseMapper.getBusinessKey(mesPlusNoList);
        }
    
        public MtProductionProblemWarn findOneByWarnNo(String warnNo) {
            return baseMapper.selectOne(new LambdaQueryWrapper<MtProductionProblemWarn>()
                    .eq(MtProductionProblemWarn::getWarnNo, warnNo).last("limit 1"));
        }
    
        @Override
        public int updateIssueWhenTransHandler(ProdProblemWarnUpdateParam updateParam) {
            return baseMapper.update(null, new LambdaUpdateWrapper<MtProductionProblemWarn>()
                    .eq(MtProductionProblemWarn::getWarnNo, updateParam.getWarnNo())
                    .set(MtProductionProblemWarn::getProcessedBy, updateParam.getProcessedBy()));
    
        }
    
        @Override
        public int updateIssueWhenSaveAnalysis(ProdProblemWarnUpdateParam updateParam, MtProductionProblemWarn vo) {
            return baseMapper.update(null, new LambdaUpdateWrapper<MtProductionProblemWarn>()
                    .eq(MtProductionProblemWarn::getWarnNo, updateParam.getWarnNo())
                    .set(MtProductionProblemWarn::getInProcess, updateParam.getInProcess())
                    .set(MtProductionProblemWarn::getInStore, updateParam.getInStore())
                    .set(MtProductionProblemWarn::getInTransit, updateParam.getInTransit())
                    .set(MtProductionProblemWarn::getInNet, updateParam.getInNet())
                    .set(MtProductionProblemWarn::getEncloseMeasure, updateParam.getEncloseMeasure())
                    .set(MtProductionProblemWarn::getOccurCause, updateParam.getOccurCause())
                    .set(MtProductionProblemWarn::getOccurMeasure, updateParam.getOccurMeasure())
                    .set(MtProductionProblemWarn::getOutflowCause, updateParam.getOutflowCause())
                    .set(MtProductionProblemWarn::getSolution, updateParam.getSolution())
                    .set(MtProductionProblemWarn::getHorizontal, updateParam.getHorizontal())
                    .set(MtProductionProblemWarn::getIsAnalyseFinish, updateParam.getIsAnalyseFinish())
                    .set(MtProductionProblemWarn::getIsEffective, updateParam.getIsEffective())
                    .set(MtProductionProblemWarn::getInvalidReason, updateParam.getInvalidReason())
                    .set(MtProductionProblemWarn::getAnalysisStatus, updateParam.getAnalysisStatus())
                    .set(StringUtils.isNotEmpty(updateParam.getIsAnalyseFinish()) && updateParam.getIsAnalyseFinish()
                            .equals("是"), MtProductionProblemWarn::getWarnStatus, "评审中")
                    .set(StringUtils.isEmpty(updateParam.getIsAnalyseFinish()) || !updateParam.getIsAnalyseFinish()
                            .equals("是"), MtProductionProblemWarn::getWarnStatus, "分析中")
                    .set(MtProductionProblemWarn::getLastUpdateDate, new Date()));
        }
    
        @Override
        public int updateIssueWhenSaveReview(ProdProblemWarnUpdateParam updateParam) {
            return baseMapper.update(null,
                    new LambdaUpdateWrapper<MtProductionProblemWarn>().eq(MtProductionProblemWarn::getWarnNo, updateParam.getWarnNo())
                            .set(MtProductionProblemWarn::getReviewComment, updateParam.getReviewComment())
                            .set(MtProductionProblemWarn::getIsClosed, updateParam.getIsClosed())
                            .set(StringUtils.isNotEmpty(updateParam.getIsClosed()) && updateParam.getIsClosed().equals("同意"),
                                    MtProductionProblemWarn::getClosedDate, new Date())
                            .set(StringUtils.isNotEmpty(updateParam.getIsClosed()) && updateParam.getIsClosed().equals("同意"),
                                    MtProductionProblemWarn::getWarnStatus, "已关闭")
                            .set(StringUtils.isNotEmpty(updateParam.getIsClosed()) && updateParam.getIsClosed().equals("驳回"),
                                    MtProductionProblemWarn::getClosedDate, null)
                            .set(StringUtils.isNotEmpty(updateParam.getIsClosed()) && updateParam.getIsClosed().equals("驳回"),
                                    MtProductionProblemWarn::getWarnStatus, "分析中")
                            .set(MtProductionProblemWarn::getLastUpdateDate, new Date()));
        }
    
        @Override
        public List<String> getMemberList(String category) {
            return Lists.newArrayList("952");
        }
    
        @Override
        public List<Map<String, String>> getCountTag(String Account) {
            return baseMapper.getCountTag(Account);
        }
    
        @Override
        public List<DistributionWarnDto> getQtyTrends(ProdProblemWarnQueryParam queryParam) {
            return baseMapper.getQtyTrends(queryParam, getCurrUserAccount());
        }
    
        @Override
        public List<DistributionWarnDto> getQtyStatus(ProdProblemWarnQueryParam queryParam) {
            return baseMapper.getQtyStatus(queryParam, getCurrUserAccount());
        }
    
        @Override
        public List<DistributionWarnDto> getTopTenReason(ProdProblemWarnQueryParam queryParam) {
            return baseMapper.getTopTenReason(queryParam, getCurrUserAccount());
        }
    
        @Override
        public List<CommonFile> findFile(String warnNo) {
            return commonFileService.getFileList(warnNo, "生产问题单预警");
        }
    
    	//获取指定周期内的预警单
    	//issueThreshold 表示次数  同编码同预警关键字的出现的问题单次数
        @Override
        public List<MtProductionProblemWarn> getMesKeywordPreWarning(int issueThreshold, String warnType, String startPeriod, String endPeriod) {
            ProdProblemWarnQueryParam queryParam = new ProdProblemWarnQueryParam();
            queryParam.setStartPeriod(startPeriod);
            queryParam.setEndPeriod(endPeriod);
            return baseMapper.getMesKeywordPreWarning(issueThreshold, warnType, queryParam);
        }
    
        /**
         * 生成新的预警单号
         *
         * @return 返回
         */
        @Override
        public String getWarnNo(String prefix, Integer no) {
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
            String res = LocalDate.now().format(formatter);
            // 问题单号后缀补全5位
            NumberFormat format = NumberFormat.getNumberInstance();
            format.setMinimumIntegerDigits(5);
            format.setGroupingUsed(false);    //分割逗号不显示 100,000 数字以三位就会有一个逗号
            res += format.format(no);
            return prefix + res;
        }
    
        @Override
        public List<String> getDropListByType(String type) {
            return baseMapper.getDropListByType(type);
        }
    
        @Override
        public List<DistributionWarnDto> getEffectiveRate(ProdProblemWarnQueryParam queryParam) {
            queryParam.setWarnStatus(Lists.newArrayList("已关闭"));
            return baseMapper.getEffectiveRate(queryParam, getCurrUserAccount());
        }
    
        @Override
        public List<QbdReportTopProblem> getRepTopProblem(ProdProblemWarnQueryParam queryParam) {
            return baseMapper.getRepTopProblem(queryParam);
        }
    
        @Override
        public List<Map<String, String>> getWarnCardData() {
            return baseMapper.getWarnCardData();
        }
    
        /**
         * 获取当前用户的账号（去掉首字母）
         * @return
         */
        private String getCurrUserAccount() {
            String Account = "";
            try {
                Account = userService.getUser().getAccount();
            } catch (Exception e) {
              
                log.error("预警单获取账号异常：", e);
            }
            // 账号格式（姓名收字符+账号），去掉姓名首字母
            Account = Account.substring(1);
            return Account;
        }
    
        private LambdaQueryWrapper<MtProductionProblemWarn> getCommonWrapper(ProdProblemWarnQueryParam queryParam) {
            LambdaQueryWrapper<MtProductionProblemWarn> wrapper = new LambdaQueryWrapper<>();
            if (!StringUtils.isBlank(queryParam.getStartPeriod())) {
                wrapper.eq(MtProductionProblemWarn::getStartPeriod, queryParam.getStartPeriod());
            }
            if (!StringUtils.isBlank(queryParam.getEndPeriod())) {
                wrapper.eq(MtProductionProblemWarn::getEndPeriod, queryParam.getEndPeriod());
            }
            if (!queryParam.getWarnType().isEmpty()) {
                wrapper.in(MtProductionProblemWarn::getWarnType, queryParam.getWarnType());
            }
            if (!queryParam.getItemCode().isEmpty()) {
                wrapper.eq(MtProductionProblemWarn::getItemCode, queryParam.getItemCode().get(0));
            }
            if (!queryParam.getFailItems().isEmpty()) {
                wrapper.eq(MtProductionProblemWarn::getFailItem, queryParam.getFailItems().get(0));
            }
            if (!queryParam.getWarnRea().isEmpty()) {
                wrapper.in(MtProductionProblemWarn::getWarnRea, queryParam.getWarnRea());
            }
            return wrapper;
        }
    }

### dao层 Mapper接口 继承MP的BaseMapper接口 ###

package com.xxx.xxx.service.dao.mapper;
    
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    import com.xxx.xxx.QualityBigData.model.ProdProblemWarnQueryParam;
    import com.xxx.xxx.domain.dto.DistributionWarnDto;
    import com.xxx.xxx.domain.model.MtProductionProblemWarn;
    import com.xxx.xxx.domain.model.QbdReportTopProblem;
    import org.apache.ibatis.annotations.MapKey;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Update;
    
    import java.util.List;
    import java.util.Map;
    
    @Mapper
    public interface MtProductionProblemWarnMapper extends BaseMapper<MtProductionProblemWarn> {
    
        Page<MtProductionProblemWarn> selectFilterData(@Param("Account")String Account,
                                                       @Param("queryParam") ProdProblemWarnQueryParam queryParam,
                                                       @Param("page") Page<MtProductionProblemWarn> pageInfo);
    
        List<MtProductionProblemWarn> selectFilterData(@Param("Account")String Account,
                                                       @Param("queryParam") ProdProblemWarnQueryParam queryParam);
    
        int getCodeAccQty(@Param("failItem") String failItem, @Param("warnType") String warnType);
    
        List<MtProductionProblemWarn> getNewWarnData(@Param("queryParam") ProdProblemWarnQueryParam queryParam);
    
        @MapKey("")
        List<Map<String,String>> getBusinessKey(@Param("list")List<String> mesNo);
    
        @MapKey("")
        List<Map<String, String>> getCountTag(@Param("Account")String Account);
    
        @Update("update dwr_mt_distribution_lock_f set STATUS = 1 where CLASS_NAME = #{className} and STATUS != 1")
        int updateLock(@Param("className") String className);
    
        @Update("update dwr_mt_distribution_lock_f set STATUS = 0 where CLASS_NAME = #{className}")
        int resetLock(@Param("className") String className);
    
        List<DistributionWarnDto> getQtyTrends(@Param("queryParam")ProdProblemWarnQueryParam queryParam, @Param("Account")String Account);
    
        List<DistributionWarnDto> getQtyStatus(@Param("queryParam")ProdProblemWarnQueryParam queryParam,@Param("Account")String Account);
    
        List<DistributionWarnDto> getTopTenReason(@Param("queryParam")ProdProblemWarnQueryParam queryParam,@Param("Account")String Account);
    
        int getMaxWarnNo(@Param("warnType") String warnType);
    
        /**
         * 获取问题单预警
         */
        List<MtProductionProblemWarn> getMesKeywordPreWarning(@Param("issueThreshold") int issueThreshold, @Param("warnType") String warnType, @Param("queryParam") ProdProblemWarnQueryParam queryParam);
    
        List<String> getDropListByType(String type);
    
        List<DistributionWarnDto> getEffectiveRate(@Param("queryParam")ProdProblemWarnQueryParam queryParam,@Param("Account")String Account);
    
        List<QbdReportTopProblem> getRepTopProblem(@Param("queryParam")ProdProblemWarnQueryParam queryParam);
    
        @MapKey("")
        List<Map<String, String>> getWarnCardData();
    }

### dao层 XMl文件映射 ###

<?xml version="1.0" encoding="UTF-8"?>
    
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="xxx.service.dao.mapper.MtProductionProblemWarnMapper">
    
        
        <resultMap id="BaseResultMap" type="xxx.domain.model.MtProductionProblemWarn">
            <result column="ID" property="id" />
            <result column="WARN_NO" property="warnNo" />
            <result column="WARN_STATUS" property="warnStatus" />
            <result column="WARN_DATE" property="warnDate" />
            <result column="WARN_REA" property="warnRea" />
            <result column="START_PERIOD" property="startPeriod" />
            <result column="END_PERIOD" property="endPeriod" />
            <result column="WARN_TYPE" property="warnType" />
            <result column="PRODUCT" property="product" />
            <result column="PROJECT" property="project" />
            <result column="CATEGORY" property="category" />
            <result column="SUB_CATEGORY" property="subCategory" />
            <result column="ITEM_CODE" property="itemCode" />
            <result column="FAIL_ITEM" property="failItem" />
            <result column="WARN_DESC" property="warnDesc" />
            <result column="MES_NO" property="mesNo" />
            <result column="SUPPLIER" property="supplier" />
            <result column="BAD_QTY" property="badQty" />
            <result column="ANALYSIS_BY" property="analysisBy" />
            <result column="PROCESSED_BY" property="processedBy" />
            <result column="REVIEW_BY" property="reviewBy" />
            <result column="CODE_ACC_QTY" property="codeAccQty" />
            <result column="ANALYSIS_STATUS" property="analysisStatus" />
            <result column="IN_PROCESS" property="inProcess" />
            <result column="IN_STORE" property="inStore" />
            <result column="IN_TRANSIT" property="inTransit" />
            <result column="IN_NET" property="inNet" />
            <result column="ENCLOSE_MEASURE" property="encloseMeasure" />
            <result column="OCCUR_CAUSE" property="occurCause" />
            <result column="OCCUR_MEASURE" property="occurMeasure" />
            <result column="OUTFLOW_CAUSE" property="outflowCause" />
            <result column="SOLUTION" property="solution" />
            <result column="HORIZONTAL" property="horizontal" />
            <result column="IS_ANALYSE_FINISH" property="isAnalyseFinish" />
            <result column="IS_EFFECTIVE" property="isEffective" />
            <result column="INVALID_REASON" property="invalidReason" />
            <result column="REVIEW_COMMENT" property="reviewComment" />
            <result column="IS_CLOSED" property="isClosed" />
            <result column="CLOSED_DATE" property="closedDate" />
            <result column="CREATION_DATE" property="creationDate" />
            <result column="LAST_UPDATE_DATE" property="lastUpdateDate" />
        </resultMap>
    
        
        <sql id="Base_Column_List">
            WARN_NO, WARN_STATUS, WARN_DATE, WARN_REA, START_PERIOD, END_PERIOD, WARN_TYPE, PRODUCT, PROJECT, CATEGORY, SUB_CATEGORY, ITEM_CODE,
                FAIL_ITEM, WARN_DESC, MES_NO, SUPPLIER, BAD_QTY, ANALYSIS_BY, PROCESSED_BY, REVIEW_BY, CODE_ACC_QTY, ANALYSIS_STATUS, IN_PROCESS,
                IN_STORE, IN_TRANSIT, IN_NET, ENCLOSE_MEASURE, OCCUR_CAUSE, OCCUR_MEASURE, OUTFLOW_CAUSE, SOLUTION, HORIZONTAL, IS_ANALYSE_FINISH,
                IS_EFFECTIVE, INVALID_REASON, REVIEW_COMMENT, IS_CLOSED, CLOSED_DATE, CREATION_DATE, LAST_UPDATE_DATE
        </sql>
        <select id="getNewWarnData" resultType="com.xxx.xxx.domain.model.MtProductionProblemWarn">
            with table1 as (
                select item_code itemCode,product,project,category,sub_category subCategory,
                '指定成员用户账号' as member,
                '指定成员用户账号' as analysisBy,
                '指定成员用户账号' as reviewBy
                from dwr_quality_ppm_code_f A
                <where>
                    is_set = '是'
                    <if test="queryParam.category != null and queryParam.category.size > 0">
                        and category in
                        <foreach collection="queryParam.category" separator="," item="item" open="(" close=")">
                            #{item}
                        </foreach>
                    </if>
                </where>
                group by item_code order by item_code
            ),table4 as (
                select SUBSTRING_INDEX(SUBSTRING_INDEX(a.mes_no, ',', b.help_topic_id + 1), ',', - 1) as mesNo from dwr_mt_production_problem_warn_f a
                join mysql.help_topic b on b.help_topic_id  <![CDATA[ < ]]> (length(a.mes_no) - length(replace(a.mes_no, ',', '')) + 1)
                where DATE_FORMAT( warn_date, '%Y-%m-%d') >= DATE_FORMAT( DATE_SUB( CURRENT_DATE, INTERVAL 1 MONTH ), '%Y-%m-%d')
            ),table2 as (
                select issure_no mesNo,item_code itemCode,fail_item failItem,vendor supplier,bad_qty badQty,instance_create_time,defect_name
                from txny.dwr_quality_qm_issure_feed_f A
                where instance_status != '4' and defect_name != '故障返修' and date_format(instance_create_time,'%Y-%m-%d') >= #{queryParam.startPeriod}
                and date_format(instance_create_time,'%Y-%m-%d') <![CDATA[ <= ]]> #{queryParam.endPeriod} and fail_item is not null
                /*where instance_status != '4' and date_format(instance_create_time,'%Y-%m-%d') = date_format(date_sub(curdate(), interval 21 day), '%Y-%m-%d') and fail_item is not null*/
                and not EXISTS(select mesNo from table4 where mesNo = A.issure_no) group by A.issure_no order by failItem
            ),table3 as (
                select group_concat(distinct A.mesNo) mesNo,count(distinct A.mesNo) mesCount,group_concat(distinct A.itemCode) itemCode,A.failItem,defect_name,
                A.supplier,A.instance_create_time,IFNULL(sum(A.badQty),'0') badQty,
                IFNULL(B.product,'') product,IFNULL(B.project,'') project,IFNULL(B.category,'') category,IFNULL(B.subCategory,'') subCategory,B.member,B.analysisBy,B.reviewBy
                from table2 A right join table1 B on A.failItem = B.itemCode where A.failItem is not null group by A.failItem
            ),table5 AS (
                select mesNo,mesCount,itemCode,failItem,#{queryParam.startPeriod} startPeriod,#{queryParam.endPeriod} endPeriod,'不良数量预警' warnType,IFNULL(defect_name,'') warnRea,
                supplier,badQty,product,project,category,subCategory,member,analysisBy,reviewBy from table3
            ),table6 AS (
                select A.PRODUCT_CODE itemCode,A.ANALYST analyst,A.REVIEWER reviewer, '1' codeType from txny.dwr_mt_mpower_report_attr_f A union
                select B.PCBA_CODE itemCode,group_concat(distinct A.ANALYST) ,group_concat(distinct A.ANALYST) reviewer,'2' codeType from txny.dwr_mt_mpower_report_bord_f B
                left join txny.dwr_mt_mpower_report_attr_f A on A.PRODUCT_CODE = B.PRODUCT_CODE
                group by A.PRODUCT_SERIES,B.PCBA_CODE
            )
            select mesNo,mesCount,a.itemCode,failItem,startPeriod,endPeriod,warnType,warnRea,supplier,badQty,product,project,category,subCategory,
            dpDeduplicate(concat(IFNULL(b.analyst,analysisBy),',',IFNULL(reviewBy,b.reviewer))) as member,IFNULL(b.analyst,analysisBy) analysisBy,IFNULL(reviewBy,b.reviewer) reviewBy
            from table5 a left join table6 b on a.itemCode = b.itemCode
        </select>
    
        <select id="getCodeAccQty" resultType="java.lang.Integer">
            SELECT COUNT(1) FROM dwr_mt_production_problem_warn_f
            WHERE FAIL_ITEM = #{failItem}
              AND WARN_TYPE = #{warnType}
              AND date_format(WARN_DATE,'%Y-%m-%d') <![CDATA[ < ]]> date_format(sysdate(),'%Y-%m-%d')
        </select>
    
        <select id="getBusinessKey" resultType="java.util.Map">
            select distinct issure_no,business_key from txny.dwr_quality_qm_issure_feed_f
            <where>
                issure_no in
                <foreach collection="list" separator="," item="mesNo" open="(" close=")">
                    #{mesNo}
                </foreach>
            </where>
        </select>
    
        <select id="getCountTag" resultType="java.util.Map">
            with table1 as (
                select 'total' dataType,count(1) count from dwr_mt_production_problem_warn_f
            ),table2 as (
                select 'open' dataType,count(1) count from dwr_mt_production_problem_warn_f where warn_status != '已关闭'
            ),table3 as (
                select 'closed' dataType,count(1) count from dwr_mt_production_problem_warn_f where warn_status = '已关闭'
            ),table4 as (
                select 'analysis' dataType,count(1) count from dwr_mt_production_problem_warn_f
                where IFNULL(processed_by,analysis_by) like CONCAT('%', #{Account}, '%') and (warn_status = '未处理' or warn_status = '分析中')
            ),table5 as (
                select 'review' dataType,count(1) count from dwr_mt_production_problem_warn_f
                where warn_status = '评审中' and review_by like CONCAT('%', #{Account}, '%')
            )
            select dataType,count from table1 union
            select dataType,count from table2 union
            select dataType,count from table3 union
            select dataType,count from table4 union
            select dataType,count from table5
        </select>
    
        <sql id="common_where">
            <where>
                <if test="queryParam.year != null and queryParam.month != null">
                    and year(WARN_DATE) = #{queryParam.year} and month(WARN_DATE) <![CDATA[ <= ]]> #{queryParam.month}
                </if>
                <if test="queryParam.startDate != null and queryParam.startDate != ''">
                    and date_format(WARN_DATE,'%Y-%m-%d') >= date_format(#{queryParam.startDate},'%Y-%m-%d')
                </if>
                <if test="queryParam.endDate != null and queryParam.endDate != ''">
                    and date_format(WARN_DATE,'%Y-%m-%d') <![CDATA[ <= ]]> date_format(#{queryParam.endDate},'%Y-%m-%d')
                </if>
                <if test="queryParam.warnType != null and queryParam.warnType.size > 0">
                    and WARN_TYPE in
                    <foreach collection="queryParam.warnType" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.supplier != null and queryParam.supplier.size > 0">
                    and SUPPLIER in
                    <foreach collection="queryParam.supplier" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.isEffective != null and queryParam.isEffective.size > 0">
                    and IS_EFFECTIVE in
                    <foreach collection="queryParam.isEffective" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.product != null and queryParam.product.size > 0">
                    and PRODUCT in
                    <foreach collection="queryParam.product" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.project != null and queryParam.project.size > 0">
                    and PROJECT in
                    <foreach collection="queryParam.project" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.category != null and queryParam.category.size > 0">
                    and CATEGORY in
                    <foreach collection="queryParam.category" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.subCategory != null and queryParam.subCategory.size > 0">
                    and SUB_CATEGORY in
                    <foreach collection="queryParam.subCategory" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.warnNo != null and queryParam.warnNo != ''">
                    and find_in_set(WARN_NO, #{queryParam.warnNo})
                </if>
                <if test="queryParam.warnStatus != null and queryParam.warnStatus.size > 0">
                    and WARN_STATUS in
                    <foreach collection="queryParam.warnStatus" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.warnRea != null and queryParam.warnRea.size > 0">
                    and WARN_REA in
                    <foreach collection="queryParam.warnRea" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.warnLev != null and queryParam.warnLev.size > 0">
                    and WARN_LEV in
                    <foreach collection="queryParam.warnLev" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.warnDesc != null and queryParam.warnDesc != ''">
                    and WARN_DESC like concat('%',#{queryParam.warnDesc},'%')
                </if>
                <if test="queryParam.mesNo != null and queryParam.mesNo.size > 0">
                    and
                    <foreach collection="queryParam.mesNo" separator="or" item="item" open="(" close=")">
                        MES_NO like concat('%',#{item},'%')
                    </foreach>
                </if>
                <if test="queryParam.failItems != null and queryParam.failItems.size > 0">
                    and FAIL_ITEM in
                    <foreach collection="queryParam.failItems" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.itemCode != null and queryParam.itemCode.size > 0">
                    and ITEM_CODE in
                    <foreach collection="queryParam.itemCode" separator="," item="item" open="(" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="queryParam.analysisBy != null and queryParam.analysisBy.size > 0">
                    and
                    <foreach collection="queryParam.analysisBy" separator="or" item="item" open="(" close=")">
                        find_in_set(#{item},ANALYSIS_BY)
                    </foreach>
                </if>
                <if test="queryParam.reviewBy != null and queryParam.reviewBy.size > 0">
                    and
                    <foreach collection="queryParam.reviewBy" separator="or" item="item" open="(" close=")">
                        find_in_set(#{item},REVIEW_BY)
                    </foreach>
                </if>
                <choose>
                    <when test='queryParam.radioType != null and queryParam.radioType == "open"'>
                        and WARN_STATUS != '已关闭'
                    </when>
                    <when test='queryParam.radioType != null and queryParam.radioType == "closed"'>
                        and WARN_STATUS = '已关闭'
                    </when>
                    <when test='queryParam.radioType != null and queryParam.radioType == "analysis"'>
                        and IFNULL(PROCESSED_BY,ANALYSIS_BY) like  concat('%',#{Account},'%')
                        and (WARN_STATUS = '未处理' or WARN_STATUS = '分析中')
                    </when>
                    <when test='queryParam.radioType != null and queryParam.radioType == "review"'>
                        and REVIEW_BY like  concat('%',#{Account},'%')
                        and WARN_STATUS = '评审中'
                    </when>
                </choose>
            </where>
        </sql>
    
        <select id="getQtyTrends" resultType="com.xxx.xxx.domain.dto.DistributionWarnDto">
            select date_format(warn_date,'%Y%m') `key`,count(1) count from dwr_mt_production_problem_warn_f
            <include refid="common_where"/>
            group by date_format(warn_date,'%Y%m') order by date_format(warn_date,'%Y%m')
        </select>
    
        <select id="getQtyStatus" resultType="com.xxx.xxx.domain.dto.DistributionWarnDto">
            select warn_status `key`,count(1) count from dwr_mt_production_problem_warn_f
            <include refid="common_where"/>
            group by warn_status order by count(1) desc
        </select>
    
        <select id="getTopTenReason" resultType="com.xxx.xxx.domain.dto.DistributionWarnDto">
            select warn_rea `key`,count(1) count from dwr_mt_production_problem_warn_f
            <include refid="common_where"/>
            group by warn_rea order by count(1) desc limit 10
        </select>
    
        <select id="selectFilterData" resultType="com.xxx.xxx.domain.model.MtProductionProblemWarn">
            select *
            from dwr_mt_production_problem_warn_f
            <include refid="common_where"/>
            order by WARN_DATE desc
        </select>
    
        <select id="getMaxWarnNo" resultType="java.lang.Integer">
            select IFNULL(max(CONVERT(right(warn_no,5),unsigned)),0) maxNo from dwr_mt_production_problem_warn_f where date_format(warn_date,'%Y-%m-%d') = date_format(sysdate(),'%Y-%m-%d')
                and warn_type = #{warnType}
        </select>
    
        <select id="getMesKeywordPreWarning" resultType="com.xxx.xxx.domain.model.MtProductionProblemWarn">
            WITH codeTab AS (
                SELECT
                    product_code as item_code,
                    product_series AS product,
                    analyst AS analysisby,
                    reviewer AS reviewby,
                    '' AS project,
                    '' AS category,
                    '' AS subCategory
                FROM
                    txny.dwr_mt_mpower_report_attr_f
                WHERE
                    is_pre_warn = '是'
                UNION ALL
                SELECT
                    pcba_code AS item_code,
                    product_series AS product,
                    analyst AS analysisby,
                    reviewer AS reviewby,
                    '' AS project,
                    '' AS category,
                    '' AS subCategory
                FROM
                    ( SELECT pcba_code, product_code, row_number() over ( PARTITION BY pcba_code ORDER BY product_code ) AS rankNum FROM txny.dwr_mt_mpower_report_bord_f ) board
                        LEFT JOIN txny.dwr_mt_mpower_report_attr_f product ON board.product_code = product.product_code
                WHERE
                    rankNum = 1
                    AND is_pre_warn = '是'
            ),
            existWarnMesNoTab AS (
                SELECT
                    substring_index( substring_index( a.mes_no, ',', b.help_topic_id + 1 ), ',', - 1 ) AS mesno
                FROM
                    dwr_mt_production_problem_warn_f a
                JOIN mysql.help_topic b ON b.help_topic_id <![CDATA[ < ]]> ( length( a.mes_no) - length( REPLACE ( a.mes_no, ',', '' )) + 1 )
                WHERE DATE_FORMAT( warn_date, '%Y-%m-%d') >= DATE_FORMAT( DATE_SUB( CURRENT_DATE, INTERVAL 1 MONTH ), '%Y-%m-%d')
            )
            ,
            qualifiedMesNoTab AS (
                SELECT
                    a.item_code,
                    issure_no,
                    issure_desc,
                    deal_analysis_causes,
                    deal_correct,
                    deal_measure,
                    GROUP_CONCAT(distinct fail_item ) AS fail_item,
                    GROUP_CONCAT(distinct vendor ) AS vendor,
                    SUM( bad_qty ) AS bad_qty
                FROM
                    txny.dwr_quality_qm_issure_feed_f a
                INNER JOIN codeTab ON codeTab.item_code = a.item_code
                WHERE instance_status != '4'
                    AND date_format( instance_create_time, '%Y-%m-%d' )  <![CDATA[ >= ]]> #{queryParam.startPeriod}
                    AND date_format( instance_create_time, '%Y-%m-%d' )  <![CDATA[ <= ]]> #{queryParam.endPeriod}
                    AND (defect_name IS NULL OR defect_name != '故障返修')
                AND NOT EXISTS ( SELECT mesno FROM existWarnMesNoTab WHERE mesno = a.issure_no )
                GROUP BY
                    item_code,
                    issure_no,
                    issure_desc,
                    deal_analysis_causes,
                    deal_correct,
                    deal_measure
            ), duplicateTab as (
                SELECT
                    item_code,
                    issure_no,
                    fail_item,
                    vendor,
                    IFNULL(bad_qty, 0)  AS bad_qty,
                    keyword,
                    CONCAT(
                        issure_no,
                        '（',
                        CASE
                            WHEN issure_desc REGEXP keyword THEN '问题描述'
                            WHEN deal_analysis_causes REGEXP keyword THEN '原因分析'
                            WHEN deal_correct REGEXP keyword THEN '处理措施'
                            WHEN deal_measure REGEXP keyword THEN '改善措施'
                            ELSE '其他'
                        END,
                        '）'
                    ) AS mes_defect_position
                FROM
                    qualifiedMesNoTab
                    INNER JOIN
                    (select keyword from dwr_mt_production_problem_defect_keyword where is_available = 1) b
                ON issure_desc REGEXP keyword
                    OR deal_analysis_causes REGEXP keyword
                    OR deal_correct REGEXP keyword
                    OR deal_measure REGEXP keyword
                GROUP BY
                    item_code,
                    issure_no
            )
            ,
            mesNoTab AS (
                SELECT
                    item_code,
                    GROUP_CONCAT( issure_no ORDER BY issure_no) issure_no,
                    COUNT( issure_no ) AS issure_cn,
                    GROUP_CONCAT(distinct fail_item ) AS fail_item,
                    GROUP_CONCAT(distinct vendor ) AS vendor,
                    IFNULL(SUM(bad_qty), '0')  AS bad_qty,
                    keyword,
                    GROUP_CONCAT(mes_defect_position order by mes_defect_position) as mes_defect_position
                FROM
                    duplicateTab
                GROUP BY
                    item_code,
                    keyword
            )
            ,
            itemCodeCntTab AS (
                SELECT
                    item_code,
                    COUNT(1) AS codeAccQty
                FROM (
                    /*只计算会新增预警单的编码，如果是同类预警单，则编码累计预警单数不变*/
                    SELECT item_code FROM mesNoTab WHERE issure_cn >= ${issueThreshold}
                    UNION ALL
                    SELECT item_code from dwr_mt_production_problem_warn_f WHERE warn_type = #{warnType}
                ) temp
                GROUP BY item_code
            )
            SELECT
                a.item_code AS itemCode,
                a.issure_no AS mesNo,
                a.issure_cn AS mesCount,
                a.keyword AS warnRea,
                a.fail_item AS failItem,
                a.vendor AS supplier,
                a.bad_qty AS badQty,
                a.mes_defect_position AS mesDefectPosition,
                b.product,
                b.project,
                b.category,
                b.subCategory,
                b.analysisBy,
                b.reviewBy,
                c.codeAccQty,
                #{warnType} AS warnType,
                #{queryParam.startPeriod} startPeriod,
                #{queryParam.endPeriod} endPeriod
            FROM
                mesNoTab a
            INNER JOIN codeTab b ON a.item_code = b.item_code
            INNER JOIN itemCodeCntTab c on a.item_code = c.item_code
        </select>
    
        <select id="getDropListByType" resultType="java.lang.String">
            with table1 as (
                (select distinct
                     case
                         when #{type} = 'warnRea' then WARN_REA
                         when #{type} = 'supplier' then SUPPLIER
                     end value
                 from dwr_mt_production_problem_warn_f)
                union all
                (select distinct
                     case
                         when #{type} = 'product' then PRODUCT
                         when #{type} = 'project' then PROJECT
                         when #{type} = 'category' then CATEGORY
                         when #{type} = 'subCategory' then SUB_CATEGORY
                         when #{type} = 'itemCode2' then left(ITEM_CODE,2)
                     when #{type} = 'itemCode4' then left(ITEM_CODE,4)
                     end value
                 from dwr_quality_ppm_code_f)
            )
            select * from table1 where value is not null
        </select>
    
        <select id="getEffectiveRate" resultType="com.xxx.xxx.domain.dto.DistributionWarnDto">
            select case is_effective when '是' then '有效预警单数' else  '非有效预警单数' end `key`,
            count(1) count from dwr_mt_production_problem_warn_f
            <include refid="common_where"/>
            and warn_status = '已关闭' group by is_effective
        </select>
    
        <select id="getRepTopProblem" resultType="com.xxx.xxx.domain.model.QbdReportTopProblem">
            select ROW_NUMBER() over(order by warn_date) rowNum,
                   warn_no         warnNo,
                   warn_type       warnType,
                   warn_status     warnStatus,
                   date_format(warn_date,'%Y-%m-%d %H:%i:%s')       warnDate,
                   product,
                   project,
                   category,
                   sub_category    subCategory,
                   occur_Cause     occurCause,
                   enclose_Measure encloseMeasure,
                   (select issure_desc from txny.dwr_quality_qm_issure_feed_f where issure_desc is not null and issure_desc != '' and find_in_set(issure_no, mes_no) limit 1) problemDesc
            from dwr_mt_production_problem_warn_f
            where find_in_set(warn_no, #{queryParam.warnNo});
        </select>
    
        <select id="getWarnCardData" resultType="java.util.Map">
            with table1 as (
                select 'total' dataType,count(1) count from dwr_mt_production_problem_warn_f
            ),table2 as (
                select 'today' dataType,count(1) count from dwr_mt_production_problem_warn_f where date_format(warn_date,'%Y%m%d') = date_format(sysdate(),'%Y%m%d')
            ),table3 as (
                select 'effect' dataType,count(1) count from dwr_mt_production_problem_warn_f where warn_status = '已关闭' and is_effective = '是'
            ),table4 as (
                select 'closed' dataType,count(1) count from dwr_mt_production_problem_warn_f where warn_status = '已关闭'
            ),table5 as (
                select 'effectRate' dataType,round((select count from table3)/(select count from table4)*100,2) count
            ),table6 as (
                select 'closedRate' dataType,round((select count from table4)/(select count from table1)*100,2) count
            ),table7 as (
                select 'unprocessed' dataType,count(1) count from dwr_mt_production_problem_warn_f where warn_status = '未处理' union
                select 'analysis' dataType,count(1) count from dwr_mt_production_problem_warn_f where warn_status = '分析中' union
                select 'review' dataType,count(1) count from dwr_mt_production_problem_warn_f where warn_status = '评审中'
            )
            select dataType,CAST(count as CHAR) count from table1 union
            select dataType,CAST(count as CHAR) count from table2 union
            select dataType,CAST(count as CHAR) count from table3 union
            select dataType,CAST(count as CHAR) count from table4 union
            select dataType,CAST(count as CHAR) count from table5 union
            select dataType,CAST(count as CHAR) count from table6 union
            select dataType,CAST(count as CHAR) count from table7
        </select>
    </mapper>

###  ###

### 生成预警单核心SQL解析：    ###

### select id=getMesKeywordPreWarning  ###

利用了with table as 多个临时表进行数据逻辑拆解，再进行嵌套调用。

-- 关键字预警
    
       #编码临时表： 
    	 /**
    	 1.获取需要预警的产品和子产品编码 是两张不同的表进行unoin
    	 2.由于子产品与产品存在多对1关系 利用排名函数row_number()  PARTITION对子产品编码分区 只取排名第一的 这样就能获得去重的子产品编码 
    	 **/
       WITH codeTab AS (
                SELECT
                    product_code as item_code,
                    product_series AS product,
                    analyst AS analysisby,
                    reviewer AS reviewby,
                    '' AS project,
                    '' AS category,
                    '' AS subCategory
                FROM
                    txny.dwr_mt_mpower_report_attr_f
                WHERE
                    is_pre_warn = '是'
                UNION ALL
                SELECT
                    pcba_code AS item_code,
                    product_series AS product,
                    analyst AS analysisby,
                    reviewer AS reviewby,
                    '' AS project,
                    '' AS category,
                    '' AS subCategory
                FROM
                    ( SELECT pcba_code, product_code, row_number() over ( PARTITION BY pcba_code ORDER BY product_code ) AS rankNum FROM txny.dwr_mt_mpower_report_bord_f ) board
                        LEFT JOIN txny.dwr_mt_mpower_report_attr_f product ON board.product_code = product.product_code
                WHERE
                    rankNum = 1
                    AND is_pre_warn = '是'
            ),
    				
    				#已经预警的生产问题单列表： 需要过滤掉已经预警的生产问题单避免重复
            existWarnMesNoTab AS (
                SELECT
                    substring_index( substring_index( a.mes_no, ',', b.help_topic_id + 1 ), ',', - 1 ) AS mesno
                FROM
                    dwr_mt_production_problem_warn_f a
                JOIN mysql.help_topic b ON b.help_topic_id <![CDATA[ < ]]> ( length( a.mes_no) - length( REPLACE ( a.mes_no, ',', '' )) + 1 )
                WHERE DATE_FORMAT( warn_date, '%Y-%m-%d') >= DATE_FORMAT( DATE_SUB( CURRENT_DATE, INTERVAL 1 MONTH ), '%Y-%m-%d')
            )
            ,
    				
    				#预警问题清单： 排除前的临时表已经预警的问题单
            qualifiedMesNoTab AS (
                SELECT
                    a.item_code,
                    issure_no,
                    issure_desc,
                    deal_analysis_causes,
                    deal_correct,
                    deal_measure,
                    GROUP_CONCAT(distinct fail_item ) AS fail_item,
                    GROUP_CONCAT(distinct vendor ) AS vendor,
                    SUM( bad_qty ) AS bad_qty
                FROM
                    txny.dwr_quality_qm_issure_feed_f a
                INNER JOIN codeTab ON codeTab.item_code = a.item_code
                WHERE instance_status != '4'
                    AND date_format( instance_create_time, '%Y-%m-%d' )  <![CDATA[ >= ]]> #{queryParam.startPeriod}
                    AND date_format( instance_create_time, '%Y-%m-%d' )  <![CDATA[ <= ]]> #{queryParam.endPeriod}
                    AND (defect_name IS NULL OR defect_name != '故障返修')
                AND NOT EXISTS ( SELECT mesno FROM existWarnMesNoTab WHERE mesno = a.issure_no )
                GROUP BY
                    item_code,
                    issure_no,
                    issure_desc,
                    deal_analysis_causes,
                    deal_correct,
                    deal_measure
            ), 
    				
    				#针对过滤后的问题单，与关键字表进行关联，判断case when对应的字段中指定字段是否其中一个包含关键字 ，然后通过拼接单号方式找到关键字是在哪个字段出现的
    				duplicateTab as (
                SELECT
                    item_code,
                    issure_no,
                    fail_item,
                    vendor,
                    IFNULL(bad_qty, 0)  AS bad_qty,
                    keyword,
                    CONCAT(
                        issure_no,
                        '（',
                        CASE
                            WHEN issure_desc REGEXP keyword THEN '问题描述'
                            WHEN deal_analysis_causes REGEXP keyword THEN '原因分析'
                            WHEN deal_correct REGEXP keyword THEN '处理措施'
                            WHEN deal_measure REGEXP keyword THEN '改善措施'
                            ELSE '其他'
                        END,
                        '）'
                    ) AS mes_defect_position
                FROM
                    qualifiedMesNoTab
                    INNER JOIN
                    (select keyword from dwr_mt_production_problem_defect_keyword where is_available = 1) b
                ON issure_desc REGEXP keyword
                    OR deal_analysis_causes REGEXP keyword
                    OR deal_correct REGEXP keyword
                    OR deal_measure REGEXP keyword
                GROUP BY
                    item_code,
                    issure_no
            )
            ,
    				#找出问题单中，相同编码，相同预警关键字的进行分组  issure_cn字段表示组内的问题单数 如果有2 ，那么 issure_no就有两个问题单号拼接成
            mesNoTab AS (
                SELECT
                    item_code,
                    GROUP_CONCAT( issure_no ORDER BY issure_no) issure_no,
                    COUNT( issure_no ) AS issure_cn,
                    GROUP_CONCAT(distinct fail_item ) AS fail_item,
                    GROUP_CONCAT(distinct vendor ) AS vendor,
                    IFNULL(SUM(bad_qty), '0')  AS bad_qty,
                    keyword,
                    GROUP_CONCAT(mes_defect_position order by mes_defect_position) as mes_defect_position
                FROM
                    duplicateTab
                GROUP BY
                    item_code,
                    keyword
            )
            ,
    				
    				#编码预警了多少次 编码预警次数  只有2个及以上的单号组成的预警单 才计算。 并且并上当前预警表单中已经预警的编码 一起统计
            itemCodeCntTab AS (
                SELECT
                    item_code,
                    COUNT(1) AS codeAccQty
                FROM (
                    /*只计算会新增预警单的编码，如果是同类预警单，则编码累计预警单数不变*/
                    SELECT item_code FROM mesNoTab WHERE issure_cn >= ${issueThreshold}
                    UNION ALL
                    SELECT item_code from dwr_mt_production_problem_warn_f WHERE warn_type = #{warnType}
                ) temp
                GROUP BY item_code
            )
    -- 				最后 查询mesNoTab预警问题表，连接codeTab编码表编码属性 以及itemCodeCntTab编码预警次数  
            SELECT
                a.item_code AS itemCode,
                a.issure_no AS mesNo,
                a.issure_cn AS mesCount,
                a.keyword AS warnRea,
                a.fail_item AS failItem,
                a.vendor AS supplier,
                a.bad_qty AS badQty,
                a.mes_defect_position AS mesDefectPosition,
                b.product,
                b.project,
                b.category,
                b.subCategory,
                b.analysisBy,
                b.reviewBy,
                c.codeAccQty,
                #{warnType} AS warnType,
                #{queryParam.startPeriod} startPeriod,
                #{queryParam.endPeriod} endPeriod
            FROM
                mesNoTab a
            INNER JOIN codeTab b ON a.item_code = b.item_code
    				INNER JOIN itemCodeCntTab c on a.item_code = c.item_code