package com.rongyichuang.review.service;
|
|
import com.rongyichuang.review.dto.response.ReviewProjectPageResponse;
|
import com.rongyichuang.review.dto.response.ReviewProjectResponse;
|
import com.rongyichuang.review.dto.response.ReviewStatisticsResponse;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Service;
|
import org.springframework.util.StringUtils;
|
|
import java.time.LocalDateTime;
|
import java.time.format.DateTimeFormatter;
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* 评审管理服务类
|
*/
|
@Service
|
public class ReviewService {
|
|
private static final Logger log = LoggerFactory.getLogger(ReviewService.class);
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
/**
|
* 查询我未评审的项目列表
|
* 逻辑：存在评委关联但当前评委未评分的项目
|
*/
|
public ReviewProjectPageResponse getUnReviewedProjects(Long judgeId, String searchKeyword, int page, int pageSize) {
|
log.info("查询我未评审的项目列表，judgeId: {}, searchKeyword: {}, page: {}, pageSize: {}", judgeId, searchKeyword, page, pageSize);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append("SELECT ap.id as activity_player_id, ap.activity_id, ap.stage_id, ap.project_name, ")
|
.append("a.name as activity_name, stage.name as stage_name, ")
|
.append("p.name as student_name, ap.create_time as submit_time ")
|
.append("FROM t_activity_player ap ")
|
.append("JOIN t_player p ON ap.player_id = p.id ")
|
.append("JOIN t_activity stage ON ap.stage_id = stage.id ")
|
.append("JOIN t_activity a ON stage.pid = a.id ")
|
.append("WHERE EXISTS (")
|
.append(" SELECT 1 FROM t_activity_judge aj ")
|
.append(" WHERE ap.activity_id = aj.activity_id ")
|
.append(" AND ap.stage_id = aj.stage_id ")
|
.append(" AND aj.judge_id = ? ")
|
.append(") ")
|
.append("AND NOT EXISTS (")
|
.append(" SELECT 1 FROM t_activity_player_rating apr ")
|
.append(" WHERE ap.id = apr.activity_player_id ")
|
.append(" AND apr.judge_id = ? ")
|
.append(" AND apr.state = 1 ")
|
.append(") ")
|
.append("AND ap.state = 1 "); // 只查询审核通过的项目
|
|
List<Object> params = new ArrayList<>();
|
params.add(judgeId);
|
params.add(judgeId);
|
|
// 添加关键词搜索条件
|
if (StringUtils.hasText(searchKeyword)) {
|
sql.append("AND (ap.project_name LIKE ? OR p.name LIKE ?) ");
|
String searchPattern = "%" + searchKeyword + "%";
|
params.add(searchPattern);
|
params.add(searchPattern);
|
}
|
|
// 添加排序
|
sql.append("ORDER BY ap.create_time DESC ");
|
|
// 查询总数
|
String countSql = "SELECT COUNT(*) " + sql.substring(sql.indexOf("FROM"));
|
Integer total = jdbcTemplate.queryForObject(countSql, Integer.class, params.toArray());
|
|
// 添加分页
|
sql.append("LIMIT ? OFFSET ?");
|
params.add(pageSize);
|
params.add((page - 1) * pageSize);
|
|
// 查询数据
|
List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql.toString(), params.toArray());
|
List<ReviewProjectResponse> projects = convertToProjectList(rows);
|
|
return new ReviewProjectPageResponse(projects, total != null ? total : 0, page, pageSize);
|
}
|
|
/**
|
* 查询我已评审的项目列表
|
* 逻辑：将"我未评审"的NOT EXISTS改为EXISTS
|
*/
|
public ReviewProjectPageResponse getReviewedProjects(Long judgeId, String searchKeyword, int page, int pageSize) {
|
log.info("查询我已评审的项目列表，judgeId: {}, searchKeyword: {}, page: {}, pageSize: {}", judgeId, searchKeyword, page, pageSize);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append("SELECT ap.id as activity_player_id, ap.activity_id, ap.stage_id, ap.project_name, ")
|
.append("a.name as activity_name, stage.name as stage_name, ")
|
.append("p.name as student_name, ap.create_time as submit_time ")
|
.append("FROM t_activity_player ap ")
|
.append("JOIN t_player p ON ap.player_id = p.id ")
|
.append("JOIN t_activity stage ON ap.stage_id = stage.id ")
|
.append("JOIN t_activity a ON stage.pid = a.id ")
|
.append("WHERE EXISTS (")
|
.append(" SELECT 1 FROM t_activity_judge aj ")
|
.append(" WHERE ap.activity_id = aj.activity_id ")
|
.append(" AND ap.stage_id = aj.stage_id ")
|
.append(" AND aj.judge_id = ? ")
|
.append(") ")
|
.append("AND EXISTS (") // 改为EXISTS
|
.append(" SELECT 1 FROM t_activity_player_rating apr ")
|
.append(" WHERE ap.id = apr.activity_player_id ")
|
.append(" AND apr.judge_id = ? ")
|
.append(" AND apr.state = 1 ")
|
.append(") ")
|
.append("AND ap.state = 1 "); // 只查询审核通过的项目
|
|
List<Object> params = new ArrayList<>();
|
params.add(judgeId);
|
params.add(judgeId);
|
|
// 添加关键词搜索条件
|
if (StringUtils.hasText(searchKeyword)) {
|
sql.append("AND (ap.project_name LIKE ? OR p.name LIKE ?) ");
|
String searchPattern = "%" + searchKeyword + "%";
|
params.add(searchPattern);
|
params.add(searchPattern);
|
}
|
|
// 添加排序
|
sql.append("ORDER BY ap.create_time DESC ");
|
|
// 查询总数
|
String countSql = "SELECT COUNT(*) " + sql.substring(sql.indexOf("FROM"));
|
Integer total = jdbcTemplate.queryForObject(countSql, Integer.class, params.toArray());
|
|
// 添加分页
|
sql.append("LIMIT ? OFFSET ?");
|
params.add(pageSize);
|
params.add((page - 1) * pageSize);
|
|
// 查询数据
|
List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql.toString(), params.toArray());
|
List<ReviewProjectResponse> projects = convertToProjectList(rows);
|
|
return new ReviewProjectPageResponse(projects, total != null ? total : 0, page, pageSize);
|
}
|
|
/**
|
* 查询学员未评审的项目列表
|
* 逻辑：没有任何评分记录的项目
|
*/
|
public ReviewProjectPageResponse getStudentUnReviewedProjects(Long judgeId, String searchKeyword, int page, int pageSize) {
|
log.info("查询学员未评审的项目列表，judgeId: {}, searchKeyword: {}, page: {}, pageSize: {}", judgeId, searchKeyword, page, pageSize);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append("SELECT ap.id as activity_player_id, ap.activity_id, ap.stage_id, ap.project_name, ")
|
.append("a.name as activity_name, stage.name as stage_name, ")
|
.append("p.name as student_name, ap.create_time as submit_time ")
|
.append("FROM t_activity_player ap ")
|
.append("JOIN t_player p ON ap.player_id = p.id ")
|
.append("JOIN t_activity stage ON ap.stage_id = stage.id ")
|
.append("JOIN t_activity a ON stage.pid = a.id ")
|
.append("WHERE EXISTS (")
|
.append(" SELECT 1 FROM t_activity_judge aj ")
|
.append(" WHERE ap.activity_id = aj.activity_id ")
|
.append(" AND ap.stage_id = aj.stage_id ")
|
.append(" AND aj.judge_id = ? ")
|
.append(") ")
|
.append("AND NOT EXISTS (")
|
.append(" SELECT 1 FROM t_activity_player_rating apr ")
|
.append(" WHERE ap.id = apr.activity_player_id ")
|
.append(" AND apr.state = 1 ")
|
.append(") ")
|
.append("AND ap.state = 1 "); // 只查询审核通过的项目
|
|
List<Object> params = new ArrayList<>();
|
params.add(judgeId);
|
|
// 添加关键词搜索条件
|
if (StringUtils.hasText(searchKeyword)) {
|
sql.append("AND (ap.project_name LIKE ? OR p.name LIKE ?) ");
|
String searchPattern = "%" + searchKeyword + "%";
|
params.add(searchPattern);
|
params.add(searchPattern);
|
}
|
|
// 添加排序
|
sql.append("ORDER BY ap.create_time DESC ");
|
|
// 查询总数
|
String countSql = "SELECT COUNT(*) " + sql.substring(sql.indexOf("FROM"));
|
Integer total = jdbcTemplate.queryForObject(countSql, Integer.class, params.toArray());
|
|
// 添加分页
|
sql.append("LIMIT ? OFFSET ?");
|
params.add(pageSize);
|
params.add((page - 1) * pageSize);
|
|
// 查询数据
|
List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql.toString(), params.toArray());
|
List<ReviewProjectResponse> projects = convertToProjectList(rows);
|
|
return new ReviewProjectPageResponse(projects, total != null ? total : 0, page, pageSize);
|
}
|
|
/**
|
* 查询评审统计信息
|
*/
|
public ReviewStatisticsResponse getReviewStatistics(Long judgeId) {
|
log.info("查询评审统计信息，judgeId: {}", judgeId);
|
|
// 查询我未评审的数量
|
String unReviewedSql = "SELECT COUNT(*) FROM t_activity_player ap " +
|
"WHERE EXISTS (" +
|
" SELECT 1 FROM t_activity_judge aj " +
|
" WHERE ap.activity_id = aj.activity_id " +
|
" AND ap.stage_id = aj.stage_id " +
|
" AND aj.judge_id = ? " +
|
") " +
|
"AND NOT EXISTS (" +
|
" SELECT 1 FROM t_activity_player_rating apr " +
|
" WHERE ap.id = apr.activity_player_id " +
|
" AND apr.judge_id = ? " +
|
" AND apr.state = 1 " +
|
") " +
|
"AND ap.state = 1";
|
Integer unReviewedCount = jdbcTemplate.queryForObject(unReviewedSql, Integer.class, judgeId, judgeId);
|
|
// 查询我已评审的数量
|
String reviewedSql = "SELECT COUNT(*) FROM t_activity_player ap " +
|
"WHERE EXISTS (" +
|
" SELECT 1 FROM t_activity_judge aj " +
|
" WHERE ap.activity_id = aj.activity_id " +
|
" AND ap.stage_id = aj.stage_id " +
|
" AND aj.judge_id = ? " +
|
") " +
|
"AND EXISTS (" +
|
" SELECT 1 FROM t_activity_player_rating apr " +
|
" WHERE ap.id = apr.activity_player_id " +
|
" AND apr.judge_id = ? " +
|
" AND apr.state = 1 " +
|
") " +
|
"AND ap.state = 1";
|
Integer reviewedCount = jdbcTemplate.queryForObject(reviewedSql, Integer.class, judgeId, judgeId);
|
|
// 查询学员未评审的数量
|
String studentUnReviewedSql = "SELECT COUNT(*) FROM t_activity_player ap " +
|
"WHERE EXISTS (" +
|
" SELECT 1 FROM t_activity_judge aj " +
|
" WHERE ap.activity_id = aj.activity_id " +
|
" AND ap.stage_id = aj.stage_id " +
|
" AND aj.judge_id = ? " +
|
") " +
|
"AND NOT EXISTS (" +
|
" SELECT 1 FROM t_activity_player_rating apr " +
|
" WHERE ap.id = apr.activity_player_id " +
|
" AND apr.state = 1 " +
|
") " +
|
"AND ap.state = 1";
|
Integer studentUnReviewedCount = jdbcTemplate.queryForObject(studentUnReviewedSql, Integer.class, judgeId);
|
|
return new ReviewStatisticsResponse(
|
unReviewedCount != null ? unReviewedCount : 0,
|
reviewedCount != null ? reviewedCount : 0,
|
studentUnReviewedCount != null ? studentUnReviewedCount : 0
|
);
|
}
|
|
/**
|
* 转换查询结果为项目列表
|
*/
|
private List<ReviewProjectResponse> convertToProjectList(List<Map<String, Object>> rows) {
|
List<ReviewProjectResponse> projects = new ArrayList<>();
|
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
|
|
for (Map<String, Object> row : rows) {
|
ReviewProjectResponse project = new ReviewProjectResponse();
|
project.setActivityPlayerId(((Number) row.get("activity_player_id")).longValue());
|
project.setActivityId(((Number) row.get("activity_id")).longValue());
|
project.setStageId(((Number) row.get("stage_id")).longValue());
|
project.setProjectName((String) row.get("project_name"));
|
project.setActivityName((String) row.get("activity_name"));
|
project.setStageName((String) row.get("stage_name"));
|
project.setStudentName((String) row.get("student_name"));
|
|
// 处理提交时间
|
Object submitTimeObj = row.get("submit_time");
|
if (submitTimeObj instanceof LocalDateTime) {
|
project.setSubmitTime(((LocalDateTime) submitTimeObj).format(formatter));
|
} else if (submitTimeObj != null) {
|
project.setSubmitTime(submitTimeObj.toString());
|
}
|
|
// 设置默认状态
|
project.setStatus("PENDING");
|
|
projects.add(project);
|
}
|
|
return projects;
|
}
|
}
|
