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;
|
import org.jeecg.config.firewall.SqlInjection.impl.RobinJdbcConfig;
|
import org.jeecg.modules.demo.contract.entity.Contract;
|
import org.jeecg.modules.demo.contract.entity.SemanticWord;
|
import org.jeecg.modules.demo.contract.service.IContractService;
|
import org.jeecg.modules.demo.contract.service.ISemanticWordService;
|
import org.jeecg.modules.message.handle.impl.EmailSendMsgHandle;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.beans.factory.annotation.Qualifier;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.scheduling.annotation.Scheduled;
|
import org.springframework.stereotype.Component;
|
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;
|
|
/**
|
* JEECG-Boot 定时发送邮件任务
|
*/
|
@Tag(name="邮件")
|
@RestController
|
@RequestMapping("/email")
|
@Slf4j
|
public class EmailScheduledTask {
|
|
@Autowired
|
@Qualifier("robinJdbcTemplate") // 指定特定的JdbcTemplate
|
private JdbcTemplate jdbcTemplate;
|
|
@Autowired
|
private EmailSendMsgHandle emailSendMsgHandle;
|
|
@Autowired
|
private ISemanticWordService semanticWordService;
|
|
@Autowired
|
private IContractService contractService;
|
|
// @Scheduled(initialDelay = 6000, fixedRate = 1800000)
|
public void sendTimedEmail() {
|
|
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());
|
if (byId == null) {
|
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() + "','%'))";
|
Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql);
|
if (stringObjectMap != null) {
|
if (stringObjectMap.get("count(1)") != null && !stringObjectMap.get("count(1)").toString().equals("0")) {
|
// 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[] 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);
|
}
|
|
}//掉词时上词了
|
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.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);
|
}
|
|
}
|
}
|
}
|
}
|
int i = 1;
|
|
// String es_receiver = "2156125618@qq.com";
|
// String es_title = "jeecg测试邮件";
|
// 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);
|
|
}
|
