From 375c18a6d2713ff19b22093eec57315992d8333f Mon Sep 17 00:00:00 2001
From: Codex Assistant <codex@example.com>
Date: 星期四, 06 十一月 2025 13:33:52 +0800
Subject: [PATCH] 增加评审下载
---
backend/src/main/java/com/rongyichuang/player/repository/ActivityPlayerRepository.java | 81 +++++++++++++++++++++++++++++++++++++---
1 files changed, 75 insertions(+), 6 deletions(-)
diff --git a/backend/src/main/java/com/rongyichuang/player/repository/ActivityPlayerRepository.java b/backend/src/main/java/com/rongyichuang/player/repository/ActivityPlayerRepository.java
index 588707e..dec8c64 100644
--- a/backend/src/main/java/com/rongyichuang/player/repository/ActivityPlayerRepository.java
+++ b/backend/src/main/java/com/rongyichuang/player/repository/ActivityPlayerRepository.java
@@ -8,6 +8,7 @@
import java.util.List;
import java.util.Optional;
+import java.time.LocalDateTime;
/**
* 娲诲姩閫夋墜Repository鎺ュ彛
@@ -26,9 +27,9 @@
List<ActivityPlayer> findByPlayerId(Long playerId);
/**
- * 鏍规嵁娲诲姩ID鍜岄�夋墜ID鏌ユ壘鍙傝禌璁板綍
+ * 鏍规嵁娲诲姩ID鍜岄�夋墜ID鏌ユ壘鍙傝禌璁板綍鍒楄〃锛屾寜鍒涘缓鏃堕棿闄嶅簭鎺掑簭
*/
- Optional<ActivityPlayer> findByActivityIdAndPlayerId(Long activityId, Long playerId);
+ List<ActivityPlayer> findByActivityIdAndPlayerIdOrderByCreateTimeDesc(Long activityId, Long playerId);
/**
* 鏍规嵁闃舵ID鏌ユ壘鍙傝禌閫夋墜
@@ -51,9 +52,9 @@
List<ActivityPlayer> findByState(Integer state);
/**
- * 鏌ユ壘娲诲姩鐨勫弬璧涢�夋墜鏁伴噺
+ * 鏍规嵁娲诲姩ID缁熻鍙傝禌閫夋墜鏁伴噺锛堝寘鎷湭瀹℃牳鍜屽鏍搁�氳繃鐨勶紝鎺掗櫎瀹℃牳椹冲洖鐨勶級
*/
- @Query("SELECT COUNT(ap) FROM ActivityPlayer ap WHERE ap.activityId = :activityId AND ap.state = 1")
+ @Query("SELECT COUNT(ap) FROM ActivityPlayer ap WHERE ap.activityId = :activityId AND ap.state IN (0, 1)")
Long countByActivityId(@Param("activityId") Long activityId);
/**
@@ -90,7 +91,75 @@
List<ActivityPlayer> findTopRankedPlayers(@Param("activityId") Long activityId);
/**
- * 鏍规嵁鐘舵�佺粺璁℃姤鍚嶆暟閲�
+ * 缁熻鏈�杩戞姤鍚嶈秼鍔匡紙浠呯涓�闃舵锛宻tate鍖呭惈寰呭鏍镐笌閫氳繃锛�
+ */
+ @Query(value = "SELECT DATE(ap.create_time) AS signup_date, COUNT(*) AS total " +
+ "FROM t_activity_player ap " +
+ "JOIN t_activity stage ON stage.id = ap.stage_id " +
+ "WHERE stage.sort_order = 1 AND stage.state = 1 " +
+ " AND ap.state IN (0, 1) " +
+ " AND ap.create_time >= :startDate " +
+ "GROUP BY DATE(ap.create_time) " +
+ "ORDER BY signup_date", nativeQuery = true)
+ List<Object[]> countFirstStageRegistrationsByDate(@Param("startDate") LocalDateTime startDate);
+
+ /**
+ * 缁熻鍚勫尯鍩熸姤鍚嶆暟閲忥紙浠呯涓�闃舵锛宻tate鍖呭惈寰呭鏍镐笌閫氳繃锛�
+ */
+ @Query(value = "SELECT ap.region_id, r.name, r.leaf_flag, COUNT(*) AS total " +
+ "FROM t_activity_player ap " +
+ "JOIN t_activity stage ON stage.id = ap.stage_id " +
+ "LEFT JOIN t_region r ON r.id = ap.region_id " +
+ "WHERE stage.sort_order = 1 AND stage.state = 1 " +
+ " AND ap.state IN (0, 1) " +
+ "GROUP BY ap.region_id, r.name, r.leaf_flag " +
+ "ORDER BY total DESC", nativeQuery = true)
+ List<Object[]> countFirstStageRegistrationsByRegion();
+
+ /**
+ * 缁熻鎸囧畾鐘舵�佺殑鍙傝禌閫夋墜鏁伴噺
*/
long countByState(Integer state);
-}
\ No newline at end of file
+
+ /**
+ * 鏍规嵁闃舵ID鍜岀姸鎬佺粺璁″弬璧涢�夋墜鏁伴噺
+ */
+ Long countByStageIdAndState(Long stageId, Integer state);
+
+ /**
+ * 鏍规嵁闃舵ID缁熻鍙傝禌閫夋墜鏁伴噺锛堝寘鎷湭瀹℃牳鍜屽鏍搁�氳繃鐨勶紝鎺掗櫎瀹℃牳椹冲洖鐨勶級
+ */
+ @Query("SELECT COUNT(ap) FROM ActivityPlayer ap WHERE ap.stageId = :stageId AND ap.state IN (0, 1)")
+ Long countByStageId(@Param("stageId") Long stageId);
+
+ /**
+ * 鏍规嵁娲诲姩ID鍜岀姸鎬佺粺璁″弬璧涢�夋墜鏁伴噺
+ */
+ @Query("SELECT COUNT(ap) FROM ActivityPlayer ap WHERE ap.activityId = :activityId AND ap.state = :state")
+ Long countByActivityIdAndState(@Param("activityId") Long activityId, @Param("state") Integer state);
+
+ /**
+ * 缁熻鍚屼竴娲诲姩+鍚屼竴閫夋墜鐨勬湁鏁堟姤鍚嶆暟閲忥紙state IN (0,1)锛�
+ */
+ @Query("SELECT COUNT(ap) FROM ActivityPlayer ap WHERE ap.activityId = :activityId AND ap.playerId = :playerId AND ap.state IN (0, 1)")
+ Long countActiveOrPendingByActivityAndPlayer(@Param("activityId") Long activityId, @Param("playerId") Long playerId);
+
+ /**
+ * 鏍规嵁闃舵ID鍜岀姸鎬佹煡鎵惧弬璧涢�夋墜锛堝寘鍚�夋墜淇℃伅锛�
+ */
+ @Query("SELECT ap FROM ActivityPlayer ap " +
+ "LEFT JOIN FETCH ap.player p " +
+ "WHERE ap.stageId = :stageId AND ap.state = :state " +
+ "ORDER BY ap.createTime DESC")
+ List<ActivityPlayer> findByStageIdAndStateWithPlayerOrderByCreateTimeDesc(@Param("stageId") Long stageId, @Param("state") Integer state);
+
+ /**
+ * 妫�鏌ラ�夋墜鏄惁宸插湪鎸囧畾闃舵鎶ュ悕
+ */
+ boolean existsByStageIdAndPlayerId(Long stageId, Long playerId);
+
+ /**
+ * 鏍规嵁闃舵鍜岄�夋墜鏌ヨ鎶ュ悕璁板綍
+ */
+ Optional<ActivityPlayer> findByStageIdAndPlayerId(Long stageId, Long playerId);
+}
--
Gitblit v1.8.0