package com.rongyichuang.tools;
|
|
import org.junit.jupiter.api.Test;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.boot.test.context.SpringBootTest;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
|
import java.util.List;
|
import java.util.Map;
|
|
@SpringBootTest
|
public class RatingTemplateFixer {
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
@Test
|
public void fixRatingTemplateData() {
|
System.out.println("=== 修正评分模板数据 ===");
|
|
// 1. 更新活动表的rating_scheme_id为1
|
String updateActivitySql = "UPDATE t_activity SET rating_scheme_id = 1 WHERE id = 1";
|
int activityResult = jdbcTemplate.update(updateActivitySql);
|
System.out.println("更新活动评分模板配置结果: " + activityResult);
|
|
// 2. 更新所有评分项目的scheme_id为1
|
String[] updateItemSqls = {
|
"UPDATE t_rating_item SET scheme_id = 1, name = '代码质量', max_score = 30, order_no = 1 WHERE id = 1",
|
"UPDATE t_rating_item SET scheme_id = 1, name = '功能完整性', max_score = 25, order_no = 2 WHERE id = 2",
|
"UPDATE t_rating_item SET scheme_id = 1, name = '用户体验', max_score = 20, order_no = 3 WHERE id = 3",
|
"UPDATE t_rating_item SET scheme_id = 1, name = '创新性', max_score = 15, order_no = 4 WHERE id = 4",
|
"UPDATE t_rating_item SET scheme_id = 1, name = '项目展示', max_score = 10, order_no = 5 WHERE id = 5"
|
};
|
|
for (int i = 0; i < updateItemSqls.length; i++) {
|
try {
|
int itemResult = jdbcTemplate.update(updateItemSqls[i]);
|
System.out.println("更新评分项目 " + (i + 1) + " 结果: " + itemResult);
|
} catch (Exception e) {
|
System.err.println("更新评分项目 " + (i + 1) + " 失败: " + e.getMessage());
|
}
|
}
|
|
// 3. 删除多余的评分项目（ID 6, 7, 8）
|
String deleteExtraItemsSql = "DELETE FROM t_rating_item WHERE id IN (6, 7, 8)";
|
int deleteResult = jdbcTemplate.update(deleteExtraItemsSql);
|
System.out.println("删除多余评分项目结果: " + deleteResult);
|
|
// 4. 验证修正结果
|
System.out.println("\n=== 验证修正结果 ===");
|
|
// 查询评分模板
|
String schemeSql = "SELECT * FROM t_rating_scheme WHERE id = 1";
|
List<Map<String, Object>> schemes = jdbcTemplate.queryForList(schemeSql);
|
System.out.println("评分模板:");
|
for (Map<String, Object> scheme : schemes) {
|
System.out.println(scheme);
|
}
|
|
// 查询评分项目
|
String itemSql = "SELECT * FROM t_rating_item WHERE scheme_id = 1 ORDER BY order_no";
|
List<Map<String, Object>> items = jdbcTemplate.queryForList(itemSql);
|
System.out.println("\n评分项目:");
|
for (Map<String, Object> item : items) {
|
System.out.println(item);
|
}
|
|
// 查询活动配置
|
String activitySql = "SELECT id, name, rating_scheme_id FROM t_activity WHERE id = 1";
|
List<Map<String, Object>> activities = jdbcTemplate.queryForList(activitySql);
|
System.out.println("\n活动配置:");
|
for (Map<String, Object> activity : activities) {
|
System.out.println(activity);
|
}
|
|
// 计算总分
|
String totalScoreSql = """
|
SELECT
|
rs.name as scheme_name,
|
SUM(ri.max_score) as total_max_score
|
FROM t_rating_scheme rs
|
LEFT JOIN t_rating_item ri ON rs.id = ri.scheme_id
|
WHERE rs.id = 1
|
GROUP BY rs.id, rs.name
|
""";
|
List<Map<String, Object>> totalResults = jdbcTemplate.queryForList(totalScoreSql);
|
System.out.println("\n总分验证:");
|
for (Map<String, Object> row : totalResults) {
|
System.out.println(row);
|
}
|
|
System.out.println("\n✅ 评分模板数据修正完成！");
|
}
|
}
|
