From 3714621173c606c4c58439ed8941100ce9ddea14 Mon Sep 17 00:00:00 2001
From: Codex Assistant <codex@example.com>
Date: 星期三, 05 十一月 2025 15:10:49 +0800
Subject: [PATCH] bug
---
backend/src/main/java/com/rongyichuang/player/service/PlayerApplicationService.java | 183 +++++++++++++++++++++++++++++++++++++++++++--
1 files changed, 174 insertions(+), 9 deletions(-)
diff --git a/backend/src/main/java/com/rongyichuang/player/service/PlayerApplicationService.java b/backend/src/main/java/com/rongyichuang/player/service/PlayerApplicationService.java
index 962cf57..772be77 100644
--- a/backend/src/main/java/com/rongyichuang/player/service/PlayerApplicationService.java
+++ b/backend/src/main/java/com/rongyichuang/player/service/PlayerApplicationService.java
@@ -1,5 +1,6 @@
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;
@@ -16,19 +17,37 @@
/**
* 鏌ヨ娲诲姩鎶ュ悕淇℃伅
+ * 鎶ュ悕瀹℃牳椤甸潰鍙樉绀烘捣閫夐樁娈电殑鏁版嵁锛屼笉鍖呭惈澶嶈禌绛夊悗缁樁娈�
+ * 褰撲紶鍏ctivityId鏃讹紝鏌ヨ璇ユ瘮璧涗笅绗竴涓樁娈碉紙sort_order=1锛夌殑鎶ュ悕椤圭洰
*/
@SuppressWarnings("unchecked")
- public List<ActivityPlayerApplicationResponse> listApplications(String name, Long activityId, Integer page, Integer size) {
+ public PageResponse<ActivityPlayerApplicationResponse> listApplications(String name, Long activityId, Integer state, Integer page, Integer size) {
String baseSql =
- "SELECT ap.id, p.name AS player_name, a.name AS activity_name, p.phone AS phone, ap.create_time AS apply_time, p.state AS state " +
+ "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_activity a ON a.id = ap.activity_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;
}
@@ -37,7 +56,16 @@
if (hasCondition) {
whereClause.append(" AND ");
}
- whereClause.append("ap.activity_id = :activityId");
+ // 鏌ヨ鎸囧畾涓绘瘮璧涚殑绗竴闃舵鎶ュ悕椤圭洰锛歛ctivity_id=涓绘瘮璧汭D, 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;
}
@@ -56,6 +84,10 @@
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) {
@@ -63,12 +95,145 @@
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.setPhone(r[3] != null ? r[3].toString() : "");
- dto.setApplyTime(r[4] != null ? r[4].toString() : "");
- // 鏄犲皠鐘舵�侊細浣跨敤 t_player.state锛�1=鏈夋晥锛�0=鏃犳晥锛�
- dto.setState(r[5] != null ? Integer.valueOf(r[5].toString()) : 1);
+ 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);
}
- return list;
+
+ // 鑾峰彇鎬绘暟
+ 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);
+ }
+
+ /**
+ * 椤圭洰璇勫涓撶敤鏌ヨ锛屽寘鍚墍鏈夐樁娈垫暟鎹紙鍖呮嫭澶嶈禌銆佸喅璧涳級
+ * 涓巐istApplications鐨勫尯鍒細涓嶈繃婊ゅ璧涘拰鍐宠禌闃舵
+ */
+ @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;
+
+ // 椤圭洰璇勫鏌ヨ锛氱洿鎺ユ牴鎹樁娈礗D鏌ヨ锛屼笖榛樿鍙煡璇㈠鏍搁�氳繃鐨勬暟鎹�
+
+ 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;
+
+ // 濡傛灉浼犲叆浜唖tate鍙傛暟锛屽垯瑕嗙洊榛樿鐨剆tate=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);
}
}
\ No newline at end of file
--
Gitblit v1.8.0