上词掉词逻辑更新-cgf

chengf

2026-01-29 abefef70ee144169527e878513636578742f40bc

 jeecg-module-system/jeecg-system-biz/src/main/java/org/jeecg/modules/demo/contract/controller/EmailScheduledTask.java

@@ -1,5 +1,6 @@
package org.jeecg.modules.demo.contract.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.annotation.PostConstruct;
import lombok.extern.slf4j.Slf4j;
@@ -17,6 +18,7 @@
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@@ -42,23 +44,12 @@
    @Autowired
    private IContractService contractService;

    @PostConstruct
    public void init() {
        new Thread(() -> {
            try {
                Thread.sleep(60000); // 延迟一分钟
                sendTimedEmail(); // 执行首次任务
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }).start();
    }


    @Scheduled(fixedRate = 1800000)
//    @Scheduled(initialDelay = 6000, fixedRate = 1800000)
    public void sendTimedEmail() {

        List<SemanticWord> list = semanticWordService.list();
        QueryWrapper<SemanticWord> semanticWordQueryWrapper = new QueryWrapper<>();
        semanticWordQueryWrapper.in("status", new Object[]{4,5,6,7});
        List<SemanticWord> list = semanticWordService.list(semanticWordQueryWrapper);

        for (SemanticWord semanticWord : list) {
            Contract byId = contractService.getById(semanticWord.getContractId());
@@ -66,29 +57,118 @@
                return;
            } else {
                String contractId = byId.getContractCode();
                String sql = "select count(1) from d_event_list where contract_name = '" + contractId + "' and (keyword_name like concat('%','" + semanticWord.getWord() + "','%') or target_name like concat('%','" + semanticWord.getOutWord() + "','%'))";
                String sql = "select count(1) from d_event_list where contract_name = '" + contractId + "' and (keyword_name like concat('%','" + semanticWord.getWord() + "','%'))";
                Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql);
                if (stringObjectMap != null) {
                    if (stringObjectMap.get("count(1)") != null && !stringObjectMap.get("count(1)").toString().equals("0")) {
                        if (semanticWord.getCount() == null || !semanticWord.getCount().equals(stringObjectMap.get("count(1)").toString())) {
                            semanticWord.setCount(stringObjectMap.get("count(1)").toString());
                        // 1. 定义预编译SQL模板（用?占位符代替变量）
                        String sqlV2 = "SELECT\n" +
                                "    IFNULL(t2.highlight_count / t1.total_count, 0) AS ratio,\n" +
                                "    t1.total_count,\n" +
                                "    t2.highlight_count\n" +
                                "FROM\n" +
                                "    (SELECT COUNT(*) AS total_count\n" +
                                "     FROM d_keyword_list kl\n" +
                                "     WHERE kl.is_active = 1\n" +
                                "       AND kl.sType = 'rank'\n" +
                                "       AND kl.ContractId = ?\n" +  // 占位符1：contractId
                                "       AND kl.sDomain <> 'HC_audit'\n" +
                                "       AND kl.sBookMarkName LIKE CONCAT('%', ?, '%')\n" +  // 占位符2：semanticWord
                                "       AND EXISTS (\n" +
                                "           SELECT 1\n" +
                                "           FROM d_event_list el\n" +
                                "           WHERE el.contract_name = ?\n" +  // 占位符3：contractId
                                "             AND el.keyword_name LIKE CONCAT('%', ?, '%')\n" +  // 占位符4：semanticWord
                                "       )) AS t1,\n" +
                                "    (" +
                                "SELECT COUNT(DISTINCT el.keyword_name) AS highlight_count\n" +
                                "FROM d_event_list el\n" +
                                "INNER JOIN d_keyword_list kl \n" +
                                "    ON el.keyword_name = kl.sBookMarkName\n" +
                                "WHERE el.contract_name = ?\n" +
                                "  AND el.keyword_name LIKE CONCAT('%', ? ,'%')\n" +
                                "  AND kl.is_active = 1\n" +
                                "  AND kl.ContractId = ?\n" +
                                "  AND kl.sDomain <> 'HC_audit'\n" +
                                "  AND (el.keyword_name, el.event_time) IN (\n" +
                                "    SELECT keyword_name, MAX(event_time)\n" +
                                "    FROM d_event_list\n" +
                                "    WHERE contract_name = ?\n" +
                                "      AND keyword_name LIKE CONCAT('%', ? ,'%')\n" +
                                "    GROUP BY keyword_name\n" +
                                "  )\n" +
                                "  AND el.type_info = 'appear') as t2";
                        Map<String, Object> semMap = jdbcTemplate.queryForMap(
                                sqlV2,
                                // 按占位符顺序传入参数（注意顺序和?一一对应）
                                contractId,
                                semanticWord.getWord(),
                                contractId,
                                semanticWord.getWord(),
                                contractId,
                                semanticWord.getWord(),
                                contractId,
                                contractId,
                                semanticWord.getWord()

                            String sqlTest = "select keyword_name,target_name,iResultRank from d_event_list where contract_name = '" + contractId + "' and (keyword_name like concat('%','" + semanticWord.getWord() + "','%') or target_name like concat('%','" + semanticWord.getOutWord() + "','%')) order by create_time desc limit 0,1";
                            Map<String, Object> semMap = jdbcTemplate.queryForMap(sqlTest);
                        );
                        String[] es_receiver = new String[]{"2156125618@qq.com"};
//                        String[] es_receiver = new String[]{"182004222@qq.com","lospace@163.com","2156125618@qq.com","coldspring@163.com"};
                        BigDecimal acceptindicator = new BigDecimal(semMap.get("ratio").toString());
                        if (semanticWord.getCount() == null || semanticWord.getCount().equals("")){
                            if (semanticWord.getAcceptindicator()/100 <= acceptindicator.doubleValue()){//上词
                                semanticWord.setCount(acceptindicator.toString());
                                String es_title = semanticWord.getOutWord() + "首次上词提醒邮件";
                                String es_content = "语义词：" + semanticWord.getWord() + "；漏出词：" + semanticWord.getOutWord() +
                                        "，排名要求：" + (semanticWord.getRanking() < 4 ? "小于大于" + semanticWord.getRanking() + "名。" : "保展现不保排名。") +
                                        "达标率要求：" + semanticWord.getAcceptindicator() +
                                        "%，当前达标率：" + acceptindicator.doubleValue() * 100 + "%";
                                for (String s_receiver : es_receiver) {
                                    emailSendMsgHandle.sendMsg(s_receiver ,es_title ,es_content);
                                }
                                semanticWord.setStatus("5");
                                semanticWordService.updateById(semanticWord);
                            }else {//掉词
                                semanticWord.setCount(acceptindicator.toString());
                                String es_title = semanticWord.getOutWord() + "掉词提醒邮件";
                                String es_content = "语义词：" + semanticWord.getWord() + "；漏出词：" + semanticWord.getOutWord() +
                                        "，排名要求：" + (semanticWord.getRanking() < 4 ? "小于等于" + semanticWord.getRanking() + "名。" : "保展现不保排名。") +
                                        "达标率要求：" + semanticWord.getAcceptindicator() +
                                        "%，当前达标率：" + acceptindicator.doubleValue() * 100 + "%";
                                for (String s_receiver : es_receiver) {
                                    emailSendMsgHandle.sendMsg(s_receiver ,es_title ,es_content);
                                }
                                semanticWord.setStatus("6");
                                semanticWordService.updateById(semanticWord);
                            }

                            String[] es_receiver = new String[]{"182004222@qq.com","lospace@163.com","2156125618@qq.com"};
                            String es_title = semMap.get("target_name") + (Integer.parseInt(semMap.get("iResultRank").toString()) <= semanticWord.getRanking()? (stringObjectMap.get("count(1)").toString().equals("1")?"首次上词提醒邮件" : "再次上词提醒邮件") : "掉词提醒邮件");
                            String es_content = "语义词：" + semMap.get("keyword_name").toString() + "；漏出词：" + semMap.get("target_name") + (Integer.parseInt(semMap.get("iResultRank").toString()) <= semanticWord.getRanking()? (stringObjectMap.get("count(1)").toString().equals("1")?"首次上词" : "再次上词") : "掉词了") +"，当前排名：" + semMap.get("iResultRank") + "。";
                        }//掉词时上词了
                        else if (semanticWord.getStatus().equals("6") && semanticWord.getAcceptindicator() <= acceptindicator.doubleValue() * 100){
                            semanticWord.setCount(acceptindicator.toString());
                            String es_title = semanticWord.getOutWord() + "再次上词提醒邮件";
                            String es_content = "语义词：" + semanticWord.getWord() + "；漏出词：" + semanticWord.getOutWord() +
                                    "，排名要求：" + (semanticWord.getRanking() < 4 ? "小于等于" + semanticWord.getRanking() + "名。" : "保展现不保排名。") +
                                    "达标率要求：" + semanticWord.getAcceptindicator() +
                                    "%，当前达标率：" + acceptindicator.doubleValue() * 100 + "%";
                            for (String s_receiver : es_receiver) {
                                emailSendMsgHandle.sendMsg(s_receiver ,es_title ,es_content);
                            }

                            semanticWord.setNowNo(semMap.get("iResultRank").toString());
                            semanticWord.setStatus(Integer.parseInt(semMap.get("iResultRank").toString()) <= semanticWord.getRanking()?"5" : "6");

                            semanticWord.setStatus("5");
                            semanticWordService.updateById(semanticWord);

                        }//上词后掉词
                        else if (semanticWord.getStatus().equals("5") && semanticWord.getAcceptindicator() > acceptindicator.doubleValue() * 100){
                            semanticWord.setCount(acceptindicator.toString());
                            String es_title = semanticWord.getOutWord() + "掉词提醒邮件";
                            String es_content = "语义词：" + semanticWord.getWord() + "；漏出词：" + semanticWord.getOutWord() +
                                    "，排名要求：" + (semanticWord.getRanking() < 4 ? "小于等于" + semanticWord.getRanking() + "名。" : "保展现不保排名。") +
                                    "达标率要求：" + semanticWord.getAcceptindicator() + "%，当前达标率：" + acceptindicator.doubleValue() * 100 + "%";
                            for (String s_receiver : es_receiver) {
                                emailSendMsgHandle.sendMsg(s_receiver ,es_title ,es_content);
                            }
                            semanticWord.setStatus("6");
                            semanticWordService.updateById(semanticWord);
                        }

                    }
                }
            }
@@ -100,4 +180,17 @@
//        String es_content = "测试内容";
//        emailSendMsgHandle.sendMsg(es_receiver ,es_title ,es_content );
    }
//
//    //                            String[] es_receiver = new String[]{"182004222@qq.com","lospace@163.com","2156125618@qq.com"};
//    String es_title = semMap.get("target_name") + (Integer.parseInt(semMap.get("iResultRank").toString()) <= semanticWord.getRanking()? (stringObjectMap.get("count(1)").toString().equals("1")?"首次上词提醒邮件" : "再次上词提醒邮件") : "掉词提醒邮件");
//    String es_content = "语义词：" + semMap.get("keyword_name").toString() + "；漏出词：" + semMap.get("target_name") + (Integer.parseInt(semMap.get("iResultRank").toString()) <= semanticWord.getRanking()? (stringObjectMap.get("count(1)").toString().equals("1")?"首次上词" : "再次上词") : "掉词了") +"，当前排名：" + semMap.get("iResultRank") + "。";
//                        for (String s_receiver : es_receiver) {
//        emailSendMsgHandle.sendMsg(s_receiver ,es_title ,es_content);
//    }
//
//                        semanticWord.setNowNo(semMap.get("iResultRank").toString());
//                        semanticWord.setStatus(Integer.parseInt(semMap.get("iResultRank").toString()) <= semanticWord.getRanking()?"5" : "6");
//
//                        semanticWordService.updateById(semanticWord);

}