package com.rongyichuang.player.service;
|
|
import com.rongyichuang.common.dto.PageResponse;
|
import com.rongyichuang.player.dto.response.ActivityPlayerApplicationResponse;
|
import jakarta.persistence.EntityManager;
|
import jakarta.persistence.PersistenceContext;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.stereotype.Service;
|
|
import java.io.ByteArrayOutputStream;
|
import java.io.IOException;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
@Service
|
public class PlayerApplicationService {
|
|
@PersistenceContext
|
private EntityManager em;
|
|
/**
|
* 查询活动报名信息
|
* 报名审核页面只显示海选阶段的数据，不包含复赛等后续阶段
|
* 当传入activityId时，查询该比赛下第一个阶段（sort_order=1）的报名项目
|
*/
|
@SuppressWarnings("unchecked")
|
public PageResponse<ActivityPlayerApplicationResponse> listApplications(String name, Long activityId, Integer state, Integer page, Integer size) {
|
String baseSql =
|
"SELECT ap.id, p.name AS player_name, parent.name AS activity_name, ap.project_name AS project_name, u.phone AS phone, ap.create_time AS apply_time, ap.state AS state, " +
|
"COALESCE(rating_stats.rating_count, 0) AS rating_count, rating_stats.average_score " +
|
"FROM t_activity_player ap " +
|
"JOIN t_player p ON p.id = ap.player_id " +
|
"JOIN t_user u ON u.id = p.user_id " +
|
"JOIN t_activity stage ON stage.id = ap.stage_id " +
|
"JOIN t_activity parent ON parent.id = stage.pid " +
|
"LEFT JOIN (" +
|
" SELECT activity_player_id, COUNT(*) AS rating_count, AVG(total_score) AS average_score " +
|
" FROM t_activity_player_rating " +
|
" WHERE state = 1 " +
|
" GROUP BY activity_player_id" +
|
") rating_stats ON rating_stats.activity_player_id = ap.id ";
|
|
StringBuilder whereClause = new StringBuilder();
|
boolean hasCondition = false;
|
|
// 默认只显示第一阶段的数据（基于sort_order=1），避免硬编码阶段名称
|
whereClause.append("stage.sort_order = 1");
|
hasCondition = true;
|
|
if (name != null && !name.isEmpty()) {
|
if (hasCondition) {
|
whereClause.append(" AND ");
|
}
|
whereClause.append("p.name LIKE CONCAT('%', :name, '%')");
|
hasCondition = true;
|
}
|
|
if (activityId != null) {
|
if (hasCondition) {
|
whereClause.append(" AND ");
|
}
|
// 查询指定主比赛的第一阶段报名项目：activity_id=主比赛ID, stage_id=第一阶段ID
|
whereClause.append("ap.activity_id = :activityId AND ap.stage_id = stage.id AND stage.pid = :activityId AND stage.sort_order = 1");
|
hasCondition = true;
|
}
|
|
if (state != null) {
|
if (hasCondition) {
|
whereClause.append(" AND ");
|
}
|
whereClause.append("ap.state = :state");
|
hasCondition = true;
|
}
|
|
String where = hasCondition ? "WHERE " + whereClause.toString() + " " : "";
|
String order = "ORDER BY ap.create_time DESC ";
|
String limit = "";
|
if (page != null && size != null && page > 0 && size > 0) {
|
int offset = (page - 1) * size;
|
limit = "LIMIT " + size + " OFFSET " + offset + " ";
|
}
|
|
var q = em.createNativeQuery(baseSql + where + order + limit);
|
if (name != null && !name.isEmpty()) {
|
q.setParameter("name", name);
|
}
|
if (activityId != null) {
|
q.setParameter("activityId", activityId);
|
}
|
|
if (state != null) {
|
q.setParameter("state", state);
|
}
|
List<Object[]> rows = q.getResultList();
|
List<ActivityPlayerApplicationResponse> list = new ArrayList<>();
|
for (Object[] r : rows) {
|
ActivityPlayerApplicationResponse dto = new ActivityPlayerApplicationResponse();
|
dto.setId(r[0] != null ? Long.valueOf(r[0].toString()) : null); // activity_player_id
|
dto.setPlayerName(r[1] != null ? r[1].toString() : "");
|
dto.setActivityName(r[2] != null ? r[2].toString() : "");
|
dto.setProjectName(r[3] != null ? r[3].toString() : ""); // project_name
|
dto.setPhone(r[4] != null ? r[4].toString() : "");
|
dto.setApplyTime(r[5] != null ? r[5].toString() : "");
|
// 映射状态：使用 t_activity_player.state（0=未审核，1=审核通过，2=审核驳回）
|
dto.setState(r[6] != null ? Integer.valueOf(r[6].toString()) : 0);
|
// 映射评审统计数据
|
dto.setRatingCount(r[7] != null ? Integer.valueOf(r[7].toString()) : 0);
|
dto.setAverageScore(r[8] != null ? Double.valueOf(r[8].toString()) : null);
|
list.add(dto);
|
}
|
|
// 获取总数
|
String countSql = "SELECT COUNT(*) " + baseSql.substring(baseSql.indexOf("FROM")) + where;
|
var countQuery = em.createNativeQuery(countSql);
|
if (name != null && !name.isEmpty()) {
|
countQuery.setParameter("name", name);
|
}
|
if (activityId != null) {
|
countQuery.setParameter("activityId", activityId);
|
}
|
if (state != null) {
|
countQuery.setParameter("state", state);
|
}
|
long total = ((Number) countQuery.getSingleResult()).longValue();
|
|
return new PageResponse<>(list, total, page != null ? page : 1, size != null ? size : 10);
|
}
|
|
/**
|
* 导出活动报名信息为Excel
|
*/
|
@SuppressWarnings("unchecked")
|
public byte[] exportApplicationsToExcel(String name, Long activityId, Integer state) throws IOException {
|
String baseSql =
|
"SELECT ap.id, p.name AS player_name, parent.name AS activity_name, ap.project_name AS project_name, u.phone AS phone, ap.create_time AS apply_time, ap.state AS state, " +
|
"COALESCE(rating_stats.rating_count, 0) AS rating_count, rating_stats.average_score " +
|
"FROM t_activity_player ap " +
|
"JOIN t_player p ON p.id = ap.player_id " +
|
"JOIN t_user u ON u.id = p.user_id " +
|
"JOIN t_activity stage ON stage.id = ap.stage_id " +
|
"JOIN t_activity parent ON parent.id = stage.pid " +
|
"LEFT JOIN (" +
|
" SELECT activity_player_id, COUNT(*) AS rating_count, AVG(total_score) AS average_score " +
|
" FROM t_activity_player_rating " +
|
" WHERE state = 1 " +
|
" GROUP BY activity_player_id" +
|
") rating_stats ON rating_stats.activity_player_id = ap.id ";
|
|
StringBuilder whereClause = new StringBuilder();
|
boolean hasCondition = false;
|
|
// 默认只显示第一阶段的数据（基于sort_order=1），避免硬编码阶段名称
|
whereClause.append("stage.sort_order = 1");
|
hasCondition = true;
|
|
if (name != null && !name.isEmpty()) {
|
if (hasCondition) {
|
whereClause.append(" AND ");
|
}
|
whereClause.append("p.name LIKE CONCAT('%', :name, '%')");
|
hasCondition = true;
|
}
|
|
if (activityId != null) {
|
if (hasCondition) {
|
whereClause.append(" AND ");
|
}
|
// 查询指定主比赛的第一阶段报名项目：activity_id=主比赛ID, stage_id=第一阶段ID
|
whereClause.append("ap.activity_id = :activityId AND ap.stage_id = stage.id AND stage.pid = :activityId AND stage.sort_order = 1");
|
hasCondition = true;
|
}
|
|
if (state != null) {
|
if (hasCondition) {
|
whereClause.append(" AND ");
|
}
|
whereClause.append("ap.state = :state");
|
hasCondition = true;
|
}
|
|
String where = hasCondition ? "WHERE " + whereClause.toString() + " " : "";
|
String order = "ORDER BY ap.create_time DESC ";
|
|
var q = em.createNativeQuery(baseSql + where + order);
|
if (name != null && !name.isEmpty()) {
|
q.setParameter("name", name);
|
}
|
if (activityId != null) {
|
q.setParameter("activityId", activityId);
|
}
|
|
if (state != null) {
|
q.setParameter("state", state);
|
}
|
List<Object[]> rows = q.getResultList();
|
|
// 创建Excel工作簿
|
Workbook workbook = new XSSFWorkbook();
|
Sheet sheet = workbook.createSheet("报名人员");
|
|
// 创建标题行
|
Row headerRow = sheet.createRow(0);
|
String[] headers = {"ID", "学员名称", "比赛名称", "项目名称", "联系电话", "申请时间", "状态", "评分次数", "平均分"};
|
for (int i = 0; i < headers.length; i++) {
|
Cell cell = headerRow.createCell(i);
|
cell.setCellValue(headers[i]);
|
|
// 设置标题样式
|
CellStyle headerStyle = workbook.createCellStyle();
|
Font font = workbook.createFont();
|
font.setBold(true);
|
headerStyle.setFont(font);
|
cell.setCellStyle(headerStyle);
|
}
|
|
// 填充数据
|
int rowNum = 1;
|
for (Object[] r : rows) {
|
Row row = sheet.createRow(rowNum++);
|
row.createCell(0).setCellValue(r[0] != null ? r[0].toString() : "");
|
row.createCell(1).setCellValue(r[1] != null ? r[1].toString() : "");
|
row.createCell(2).setCellValue(r[2] != null ? r[2].toString() : "");
|
row.createCell(3).setCellValue(r[3] != null ? r[3].toString() : "");
|
row.createCell(4).setCellValue(r[4] != null ? r[4].toString() : "");
|
row.createCell(5).setCellValue(r[5] != null ? r[5].toString() : "");
|
|
// 状态转换
|
String stateText = "未知";
|
if (r[6] != null) {
|
int stateValue = Integer.parseInt(r[6].toString());
|
switch (stateValue) {
|
case 0: stateText = "未审核"; break;
|
case 1: stateText = "审核通过"; break;
|
case 2: stateText = "审核驳回"; break;
|
default: stateText = "未知";
|
}
|
}
|
row.createCell(6).setCellValue(stateText);
|
|
row.createCell(7).setCellValue(r[7] != null ? r[7].toString() : "0");
|
row.createCell(8).setCellValue(r[8] != null ? r[8].toString() : "");
|
}
|
|
// 自动调整列宽
|
for (int i = 0; i < headers.length; i++) {
|
sheet.autoSizeColumn(i);
|
}
|
|
// 将工作簿写入字节数组
|
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
|
workbook.write(outputStream);
|
workbook.close();
|
outputStream.close();
|
|
return outputStream.toByteArray();
|
}
|
|
/**
|
* 项目评审专用查询，包含所有阶段数据（包括复赛、决赛）
|
* 与listApplications的区别：不过滤复赛和决赛阶段
|
*/
|
@SuppressWarnings("unchecked")
|
public PageResponse<ActivityPlayerApplicationResponse> listProjectReviewApplications(String name, Long activityId, Long regionId, Integer state, Integer page, Integer size) {
|
String baseSql =
|
"SELECT ap.id, CONCAT(p.name, '（', ap.project_name, '）') AS player_name, stage.name AS activity_name, ap.project_name AS project_name, u.phone AS phone, ap.create_time AS apply_time, ap.state AS state, " +
|
"COALESCE(rating_stats.rating_count, 0) AS rating_count, rating_stats.average_score " +
|
"FROM t_activity_player ap " +
|
"JOIN t_player p ON p.id = ap.player_id " +
|
"JOIN t_user u ON u.id = p.user_id " +
|
"JOIN t_activity stage ON stage.id = ap.stage_id " +
|
"LEFT JOIN (" +
|
" SELECT activity_player_id, COUNT(*) AS rating_count, AVG(total_score) AS average_score " +
|
" FROM t_activity_player_rating " +
|
" WHERE state = 1 " +
|
" GROUP BY activity_player_id" +
|
") rating_stats ON rating_stats.activity_player_id = ap.id ";
|
|
StringBuilder whereClause = new StringBuilder();
|
boolean hasCondition = false;
|
|
// 项目评审查询：直接根据阶段ID查询，且默认只查询审核通过的数据
|
|
if (name != null && !name.isEmpty()) {
|
if (hasCondition) {
|
whereClause.append(" AND ");
|
}
|
whereClause.append("p.name LIKE CONCAT('%', :name, '%')");
|
hasCondition = true;
|
}
|
|
if (activityId != null) {
|
if (hasCondition) {
|
whereClause.append(" AND ");
|
}
|
// 直接查询指定阶段ID的报名项目
|
whereClause.append("ap.stage_id = :activityId");
|
hasCondition = true;
|
}
|
|
if (regionId != null) {
|
if (hasCondition) {
|
whereClause.append(" AND ");
|
}
|
whereClause.append("ap.region_id = :regionId");
|
hasCondition = true;
|
}
|
|
// 默认只查询审核通过的数据 (state = 1)
|
if (hasCondition) {
|
whereClause.append(" AND ");
|
}
|
whereClause.append("ap.state = 1");
|
hasCondition = true;
|
|
// 如果传入了state参数，则覆盖默认的state=1条件
|
if (state != null) {
|
// 移除最后添加的 "ap.state = 1" 条件
|
String whereStr = whereClause.toString();
|
whereStr = whereStr.replace(" AND ap.state = 1", "");
|
whereClause = new StringBuilder(whereStr);
|
|
if (hasCondition && !whereStr.isEmpty()) {
|
whereClause.append(" AND ");
|
}
|
whereClause.append("ap.state = :state");
|
}
|
|
String where = hasCondition ? "WHERE " + whereClause.toString() + " " : "";
|
String order = "ORDER BY ap.create_time DESC ";
|
String limit = "";
|
if (page != null && size != null && page > 0 && size > 0) {
|
int offset = (page - 1) * size;
|
limit = "LIMIT " + size + " OFFSET " + offset + " ";
|
}
|
|
var q = em.createNativeQuery(baseSql + where + order + limit);
|
if (name != null && !name.isEmpty()) {
|
q.setParameter("name", name);
|
}
|
if (activityId != null) {
|
q.setParameter("activityId", activityId);
|
}
|
if (regionId != null) {
|
q.setParameter("regionId", regionId);
|
}
|
if (state != null) {
|
q.setParameter("state", state);
|
}
|
List<Object[]> rows = q.getResultList();
|
List<ActivityPlayerApplicationResponse> list = new ArrayList<>();
|
for (Object[] r : rows) {
|
ActivityPlayerApplicationResponse dto = new ActivityPlayerApplicationResponse();
|
dto.setId(r[0] != null ? Long.valueOf(r[0].toString()) : null); // activity_player_id
|
dto.setPlayerName(r[1] != null ? r[1].toString() : "");
|
dto.setActivityName(r[2] != null ? r[2].toString() : "");
|
dto.setProjectName(r[3] != null ? r[3].toString() : ""); // project_name
|
dto.setPhone(r[4] != null ? r[4].toString() : "");
|
dto.setApplyTime(r[5] != null ? r[5].toString() : "");
|
// 映射状态：使用 t_activity_player.state（0=未审核，1=审核通过，2=审核驳回）
|
dto.setState(r[6] != null ? Integer.valueOf(r[6].toString()) : 0);
|
// 映射评审统计数据
|
dto.setRatingCount(r[7] != null ? Integer.valueOf(r[7].toString()) : 0);
|
dto.setAverageScore(r[8] != null ? Double.valueOf(r[8].toString()) : null);
|
list.add(dto);
|
}
|
|
// 创建分页响应
|
long totalElements = list.size();
|
return new PageResponse<>(list, totalElements, page, size);
|
}
|
}
|
