package org.jeecg.modules.demo.contract.controller;
|
|
import cn.hutool.extra.pinyin.PinyinUtil;
|
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
|
import com.baomidou.mybatisplus.core.metadata.IPage;
|
import io.swagger.v3.oas.annotations.Operation;
|
import io.swagger.v3.oas.annotations.tags.Tag;
|
import lombok.extern.slf4j.Slf4j;
|
import org.jeecg.common.api.vo.Result;
|
import org.jeecg.modules.demo.contract.entity.Contract;
|
import org.jeecg.modules.demo.contract.entity.RobinEntity;
|
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.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.beans.factory.annotation.Qualifier;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.web.bind.annotation.*;
|
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.UUID;
|
|
@Tag(name="Robin外部接口")
|
@RestController
|
@RequestMapping("/api/robin")
|
@Slf4j
|
public class RobinController {
|
@Autowired
|
private ISemanticWordService semanticWordService;
|
@Autowired
|
@Qualifier("robinJdbcTemplate") // 指定特定的JdbcTemplate
|
private JdbcTemplate jdbcTemplate;
|
|
@Autowired
|
private IContractService contractService;
|
|
@Operation(summary="a")
|
@GetMapping(value = "/list")
|
public Result queryPageList(RobinEntity robinEntity) {
|
if(robinEntity==null){
|
return Result.error("请上传参数");
|
}else if (robinEntity.getSBookMarkName() == null) {
|
return Result.error("请输入语义词");
|
}else if (robinEntity.getSKeyword() == null) {
|
return Result.error("请输入漏出词");
|
}else if (robinEntity.getSFromLLM() == null) {
|
return Result.error("请选择大模型");
|
}else if (robinEntity.getSemanticWordId() == null) {
|
return Result.error("请输入语义词编号");
|
}
|
String[] arr = new String[]{robinEntity.getSKeyword() + "哪家好",
|
robinEntity.getSKeyword() + "哪个好",
|
robinEntity.getSKeyword() + "哪家靠谱",
|
robinEntity.getSKeyword() + "哪个靠谱",
|
robinEntity.getSKeyword() + "推荐",
|
robinEntity.getSKeyword() + "排行榜",
|
robinEntity.getSKeyword() + "选哪家",
|
robinEntity.getSKeyword() + "选哪个",
|
};
|
SemanticWord semanticWord = semanticWordService.getById(robinEntity.getSemanticWordId());
|
|
List<RobinEntity> result = new ArrayList<>();
|
for (String str : arr) {
|
try {
|
String sql = "SELECT " +
|
"sBookMarkName, sKeyword, " +
|
"DATE_FORMAT(tCreateTime, '%m-%d') as DayTime, " + // 注意这里修改了引号
|
"min(iResultRank) as MinRank, " +
|
"max(iResultRank) as MaxRank, " +
|
"round(avg(iResultRank),2) as AvgRank " +
|
"FROM `d_LLM_Rank` " +
|
"where sFromLLM='"+robinEntity.getSFromLLM()+"'" +
|
"and sKeyword='"+str+"'" +
|
"and sBookMarkName like '%"+robinEntity.getSBookMarkName()+"%'"+
|
"and tCreateTime >= DATE_SUB(NOW(), INTERVAL 7 DAY)"+
|
"GROUP BY sBookMarkName, sKeyword, DATE_FORMAT(tCreateTime, '%m-%d') order by sBookMarkName"; // 注意引号修改
|
|
// 关键：使用不带参数的queryForList方法
|
List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sql);
|
RobinEntity robinEntity1 = new RobinEntity();
|
robinEntity1.setTest(str);
|
robinEntity1.setSBookMarkName(robinEntity.getSBookMarkName());
|
robinEntity1.setResultList(resultList);
|
robinEntity1.setRanking(semanticWord.getRanking());
|
robinEntity1.setLookTime(semanticWord.getLookTime());
|
double avgRank = 0;
|
for (Map <String, Object> map : resultList) {
|
avgRank += Double.parseDouble(map.get("AvgRank").toString());
|
}
|
avgRank /= resultList.size();
|
robinEntity1.setAvgRank(
|
avgRank
|
);
|
|
String reachedTargetSql = "SELECT \n" +
|
" passed_kpi AS 达标率\n" +
|
"FROM \n" +
|
" d_keyword_day_kpi \n" +
|
"WHERE \n" +
|
" semantic_word = '" + semanticWord.getWord() + "' \n" +
|
" AND keyword_name = '" + robinEntity1.getTest() + "'" +
|
" AND day_info = DATE_SUB(CURDATE(), INTERVAL 1 DAY) limit 0,1;";
|
Double aDouble = null;
|
|
try {
|
aDouble = jdbcTemplate.queryForObject(reachedTargetSql, Double.class);
|
}catch (Exception e){
|
|
}
|
|
if (!(aDouble == null)) {
|
double a = aDouble;
|
robinEntity1.setReachedTarget(String.format("%.2f",(a > 100 ? 100 : a)) + "%");
|
}
|
|
robinEntity1.setAvgRank(Double.valueOf(String.format("%.2f", robinEntity1.getAvgRank())));
|
if (Double.isNaN(robinEntity1.getAvgRank())) {
|
robinEntity1.setAvgRank(null);
|
}
|
if (semanticWord.getAcceptindicator() != null){
|
robinEntity1.setAcceptIndicator(String.format("%.2f",semanticWord.getAcceptindicator())+"%");
|
}
|
if (semanticWord.getLookTime() != null){
|
robinEntity1.setSignTermStartDate(java.time.LocalDate.parse(semanticWord.getLookTime()).plusDays(1).format(java.time.format.DateTimeFormatter.ISO_LOCAL_DATE));
|
robinEntity1.setSignTermEndDate(java.util.Optional.ofNullable(semanticWord.getLookTime()).map(s -> { try { return java.time.LocalDate.parse(s, java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd")).plusMonths(semanticWord.getMonth()).format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd")); } catch (java.time.format.DateTimeParseException e) { return null; } }).orElse(null));
|
}
|
result.add(robinEntity1);
|
} catch (Exception e) {
|
log.error("查询失败", e);
|
return Result.error("查询失败：" + e.getMessage());
|
}
|
}
|
if (result.get(0).getResultList().isEmpty()){
|
result.remove(0);
|
}else {
|
result.remove(1);
|
}
|
if (result.get(1).getResultList().isEmpty()){
|
result.remove(1);
|
}else {
|
result.remove(2);
|
}
|
if (result.get(4).getResultList().isEmpty()){
|
result.remove(4);
|
}else {
|
result.remove(5);
|
}
|
return Result.ok(result);
|
}
|
|
@Operation(summary = "最近查询结果")
|
@GetMapping(value = "/recentResults")
|
public Result overview(
|
@RequestParam String sKeywordName, // 对应sKeyword模糊查询关键词
|
@RequestParam String sTargetName, // 对应sBookMarkName模糊查询关键词
|
@RequestParam String tTimeStart, // 开始日期
|
@RequestParam String tTimeEnd) { // 结束日期
|
|
// 非空校验
|
if (org.springframework.util.StringUtils.isEmpty(sKeywordName)) {
|
return Result.error("关键词名称不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(sTargetName)) {
|
return Result.error("目标名称关键词不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(tTimeStart)) {
|
return Result.error("开始日期不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(tTimeEnd)) {
|
return Result.error("结束日期不能为空");
|
}
|
|
try {
|
// 构建SQL语句，使用参数占位符防止SQL注入
|
String sql = "SELECT tCreateTime as 日期, " +
|
"sKeyword, " +
|
"round(avg(iResultRank), 2) as iResultRank " +
|
"FROM d_LLM_Rank r " +
|
"WHERE sKeyword LIKE concat('%', ?, '%') " +
|
"AND sBookMarkName LIKE concat('%', ?, '%') " +
|
"AND sFromLLM = 'DeepSeek' " +
|
"AND EXISTS (SELECT 1 FROM d_keyword_list k WHERE k.sBookMarkName = r.sKeyword AND k.is_active = 1) " +
|
"AND tCreateTime >= concat(?, ' 00:00:00') " +
|
"AND tCreateTime <= concat(?, ' 23:59:59') " +
|
"GROUP BY tCreateTime, sKeyword, sFromLLM " +
|
"ORDER BY tCreateTime DESC " +
|
"LIMIT 30";
|
|
// 执行查询并传入参数（注意参数顺序与占位符对应）
|
List<Map<String, Object>> resultList = jdbcTemplate.queryForList(
|
sql,
|
sKeywordName,
|
sTargetName,
|
tTimeStart,
|
tTimeEnd
|
);
|
|
return Result.ok(resultList);
|
} catch (Exception e) {
|
log.error("概览查询失败", e);
|
return Result.error("查询失败：" + e.getMessage());
|
}
|
}
|
|
|
@Operation(summary = "概览查询")
|
@GetMapping(value = "/overview")
|
public Result recentQueryResults(
|
@RequestParam String sKeywordLike, // 对应'%开锁%'中的"开锁"
|
@RequestParam String sBookMarkNameLike, // 对应'%八个六%'中的"八个六"
|
@RequestParam String sFromLLM, // 对应'DeepSeek'
|
@RequestParam String startTime, // 对应开始时间'2025-11-04 00:00:00'
|
@RequestParam String endTime) { // 对应结束时间'2025-11-10 23:59:59'
|
try {
|
|
// 非空校验
|
if (org.springframework.util.StringUtils.isEmpty(sKeywordLike)) {
|
return Result.error("关键词模糊查询条件不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(sBookMarkNameLike)) {
|
return Result.error("书签名称模糊查询条件不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(sFromLLM)) {
|
return Result.error("大模型名称不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(startTime)) {
|
return Result.error("开始时间不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(endTime)) {
|
return Result.error("结束时间不能为空");
|
}
|
|
String newSql = "select date(tCreateTime) as 日期," +
|
"sKeyword,round(avg(iResultRank),1) as iResultRank " +
|
"from d_LLM_Rank r where sKeyword like concat('%', ?, '%') " +
|
"and (sBookMarkName like concat('%', ?, '%') or category_name like concat('%', ?, '%')) " +
|
"and sFromLLM=? " +
|
"and EXISTS (SELECT 1 FROM d_keyword_list k WHERE k.sBookMarkName = r.sKeyword AND k.is_active = 1) " +
|
"and tCreateTime >= ? " +
|
"and tCreateTime <= ? group by date(tCreateTime),sKeyword,sFromLLM";
|
|
// // 构建SQL语句，使用参数占位符防止SQL注入
|
// String sql = "SELECT date(tCreateTime) as 日期, " +
|
// "sKeyword, " +
|
// "round(avg(iResultRank),2) as iResultRank " +
|
// "FROM d_LLM_Rank r " +
|
// "WHERE sKeyword like concat('%', ?, '%') " +
|
// "AND sBookMarkName like concat('%', ?, '%') " +
|
// "AND sFromLLM = ? " +
|
// "AND EXISTS (SELECT 1 FROM d_keyword_list k WHERE k.sBookMarkName = r.sKeyword AND k.is_active = 1) " +
|
// "AND tCreateTime >= ? " +
|
// "AND tCreateTime <= ? " +
|
// "GROUP BY date(tCreateTime), sKeyword, sFromLLM";
|
|
// 执行查询并传入参数
|
List<Map<String, Object>> resultList = jdbcTemplate.queryForList(
|
newSql,
|
sKeywordLike,
|
sBookMarkNameLike,
|
sBookMarkNameLike,
|
sFromLLM,
|
startTime+" 00:00:00",
|
endTime+" 23:59:59"
|
);
|
|
return Result.ok(resultList);
|
} catch (Exception e) {
|
log.error("最近查询结果失败", e);
|
return Result.error("最近查询结果失败：" + e.getMessage());
|
}
|
}
|
|
@Operation(summary = "全部排名链接引用次数概览")
|
@GetMapping(value = "/rankRefCountOverview")
|
public Result rankRefCountOverview(
|
@RequestParam String sTargetName, // 对应sBookMarkName模糊查询关键词
|
@RequestParam String sKeywordName, // 对应sKeyword模糊查询关键词
|
@RequestParam String tTimeStart, // 开始日期
|
@RequestParam String tTimeEnd) { // 结束日期
|
|
// 非空校验
|
if (org.springframework.util.StringUtils.isEmpty(sTargetName)) {
|
return Result.error("目标名称关键词不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(sKeywordName)) {
|
return Result.error("关键词名称不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(tTimeStart)) {
|
return Result.error("开始日期不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(tTimeEnd)) {
|
return Result.error("结束日期不能为空");
|
}
|
|
try {
|
// 构建SQL语句，使用参数占位符防止SQL注入
|
String sql = "WITH V_DeepSeek_Ref AS (" +
|
"SELECT DISTINCT date(a.tCreateTime) as tDay, " +
|
"a.iResultRank, b.iRefRank, a.sSourceFile " +
|
"FROM d_LLM_Rank a " +
|
"INNER JOIN d_LLM_Ref b ON a.sSourceFile = b.sSourceFile " +
|
"AND a.iResultRank = b.iResultRank " +
|
"AND a.category_name = b.category_name " +
|
"WHERE a.sBookMarkName LIKE concat('%', ?, '%') " +
|
"AND a.sKeyword LIKE concat('%', ?, '%') " +
|
"AND a.sFromLLM = 'DeepSeek' " +
|
"AND date(a.tCreateTime) >= ? " +
|
"AND date(a.tCreateTime) <= ? " +
|
")" +
|
"SELECT b.tDay as 日期, a.sURL, a.sSiteName, a.tPublishTime, COUNT(*) as total_count " +
|
"FROM d_URL AS a " +
|
"INNER JOIN V_DeepSeek_Ref AS b ON a.sSourceFile = b.sSourceFile " +
|
"AND a.iRank = b.iRefRank " +
|
"WHERE date(a.created_at) >= ? " +
|
"AND date(a.created_at) <= ? " +
|
"GROUP BY b.tDay, a.sSiteName, a.sURL, a.tPublishTime";
|
|
// 执行查询并传入参数（注意参数顺序与占位符对应）
|
List<Map<String, Object>> resultList = jdbcTemplate.queryForList(
|
sql,
|
sTargetName,
|
sKeywordName,
|
tTimeStart,
|
tTimeEnd,
|
tTimeStart,
|
tTimeEnd
|
);
|
|
return Result.ok(resultList);
|
} catch (Exception e) {
|
log.error("查询全部排名链接引用次数概览失败", e);
|
return Result.error("查询失败：" + e.getMessage());
|
}
|
}
|
|
@Operation(summary = "刷新合同")
|
@GetMapping(value = "/flushContract")
|
public Result flushContract(@RequestParam String id){
|
QueryWrapper<Contract> objectQueryWrapper = new QueryWrapper<>();
|
if (id != null){
|
objectQueryWrapper.eq("id",id);
|
}
|
List<Contract> list = contractService.list(objectQueryWrapper);
|
for (Contract contract : list) {
|
addContract(contract);
|
}
|
return Result.ok("合同同步完成");
|
}
|
|
public void addContract(Contract contract){
|
String uuid = UUID.randomUUID().toString().replaceAll("-", "");
|
String sBookMarkName;
|
if (contract.getContractCode() == null || contract.getContractCode().equals("")) {
|
sBookMarkName = PinyinUtil.getPinyin(contract.getContractName(), "");
|
}else {
|
sBookMarkName = contract.getContractCode();
|
}
|
String sCreator = contract.getCreateBy();
|
String sType = contract.getAgentsName() == null ? "客户" : "代理商";
|
String sTarget = contract.getCustomerName();
|
String abbr_text = contract.getContractName();
|
String checkSql = "SELECT COUNT(1) FROM d_Contract WHERE sBookMarkName = ?";
|
String contractId = sBookMarkName;
|
Integer count = jdbcTemplate.queryForObject(
|
checkSql,
|
new Object[]{sBookMarkName}, // 传入查询参数（合同名称）
|
Integer.class
|
);
|
if (count == null || count == 0) {
|
String insertSql = "INSERT INTO d_Contract (" +
|
"UUID, sBookMarkName, sCreator, is_active, day_scan_min, status_info, sType, sTarget, abbr_text,brand_regexp" + // 假设表字段名
|
") VALUES (?, ?, ?, 1, 5, 'ongoing', ?, ?, ?, ?)";
|
|
jdbcTemplate.update(
|
insertSql,
|
uuid,
|
sBookMarkName,
|
sCreator,
|
sType,
|
sTarget,
|
abbr_text,
|
abbr_text
|
);
|
}
|
QueryWrapper<SemanticWord> queryWrapper = new QueryWrapper<>();
|
queryWrapper.eq("contract_id", contract.getId());
|
List<SemanticWord> semanticWords = semanticWordService.list(queryWrapper);
|
for (SemanticWord semanticWord : semanticWords){
|
checkSql = "SELECT COUNT(1) FROM d_keyword_list WHERE contractId = '" + contractId + "' AND sBookMarkName LIKE CONCAT(?, '%')";
|
count = jdbcTemplate.queryForObject(
|
checkSql,
|
new Object[]{semanticWord.getWord()}, // 传入查询参数（合同名称）
|
Integer.class
|
);
|
if (count == null || count == 0) {
|
String[] arr = new String[]{"哪家好", "哪家靠谱", "排行榜", "选哪家", "排名"};
|
for (int i = 0; i < 5; i++) {
|
uuid = UUID.randomUUID().toString().replaceAll("-", "");
|
sBookMarkName = semanticWord.getWord() + arr[i];
|
String semantic_word = semanticWord.getOutWord();
|
// 插入SQL，固定值直接写在SQL中，动态值用?占位
|
String insertSql = "INSERT INTO d_keyword_list (" + // 表名修正为 d_keyword_list
|
"UUID, sBookMarkName, ContractId, semantic_word, sCreator, sType, sDomain, " +
|
"apply_name, is_active, priority_num, interval_minute, is_proxy, proxy_mode, scan_min," + // 补充表中存在的 is_proxy 字段
|
"deepseek_active, deepseek_require, deepseek_count," +
|
"doubao_active, doubao_require, doubao_count," +
|
"yuanbao_active, yuanbao_require, yuanbao_count," +
|
"qianwen_active, qianwen_require, qianwen_count," +
|
"yiyan_active, yiyan_require, yiyan_count" +
|
") VALUES (" +
|
"?, ?, ?, ?, 'admin', 'rank', '客户', " +
|
"'deepseek', 1, 8, 240, 0, 'random', 5," +
|
"1,5,0," +
|
"1,5,0," +
|
"1,5,0," +
|
"1,5,0," +
|
"1,5,0" +
|
")";
|
|
// 执行插入，仅传入动态参数
|
jdbcTemplate.update(
|
insertSql,
|
uuid, // 动态UUID
|
sBookMarkName, // 动态sBookMarkName
|
contractId,
|
semanticWord.getWord() // 动态semantic_word
|
);
|
|
}
|
}
|
|
}
|
}
|
|
@Operation(summary = "按站点名称统计引用次数")
|
@GetMapping(value = "/siteRefCount")
|
public Result siteRefCount(
|
@RequestParam String sTargetName, // 对应sBookMarkName模糊查询关键词
|
@RequestParam String sKeywordName, // 对应sKeyword模糊查询关键词
|
@RequestParam String tTimeStart, // 开始日期
|
@RequestParam String tTimeEnd) { // 结束日期
|
|
// 非空校验
|
if (org.springframework.util.StringUtils.isEmpty(sTargetName)) {
|
return Result.error("目标名称关键词不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(sKeywordName)) {
|
return Result.error("关键词名称不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(tTimeStart)) {
|
return Result.error("开始日期不能为空");
|
}
|
if (org.springframework.util.StringUtils.isEmpty(tTimeEnd)) {
|
return Result.error("结束日期不能为空");
|
}
|
|
try {
|
// 构建SQL语句，使用参数占位符防止SQL注入
|
String sql = "WITH V_DeepSeek_Ref AS (" +
|
"SELECT DISTINCT date(a.tCreateTime) as tDay, " +
|
"a.iResultRank, b.iRefRank, a.sSourceFile " +
|
"FROM d_LLM_Rank a " +
|
"INNER JOIN d_LLM_Ref b ON a.sSourceFile = b.sSourceFile " +
|
"AND a.iResultRank = b.iResultRank " +
|
"AND a.category_name = b.category_name " +
|
"WHERE a.sBookMarkName LIKE concat('%', ?, '%') " +
|
"AND a.sKeyword LIKE concat('%', ?, '%') " +
|
"AND a.sFromLLM = 'DeepSeek' " +
|
"AND a.tCreateTime >= concat(?, ' 00:00:00') " +
|
"AND a.tCreateTime <= concat(?, ' 23:59:59') " +
|
"AND a.iResultRank <= 3" +
|
")" +
|
"SELECT a.sSiteName, COUNT(*) as total_count " +
|
"FROM d_URL AS a " +
|
"INNER JOIN V_DeepSeek_Ref AS b ON a.sSourceFile = b.sSourceFile " +
|
"AND a.iRank = b.iRefRank " +
|
"WHERE a.created_at >= concat(?, ' 00:00:00') " +
|
"AND a.created_at <= concat(?, ' 23:59:59') " +
|
"GROUP BY a.sSiteName";
|
|
// 执行查询并传入参数（注意参数顺序与占位符对应）
|
List<Map<String, Object>> resultList = jdbcTemplate.queryForList(
|
sql,
|
sTargetName,
|
sKeywordName,
|
tTimeStart,
|
tTimeEnd,
|
tTimeStart,
|
tTimeEnd
|
);
|
|
return Result.ok(resultList);
|
} catch (Exception e) {
|
log.error("按站点名称统计引用次数失败", e);
|
return Result.error("查询失败：" + e.getMessage());
|
}
|
}
|
}
|
