package com.rongyichuang.player.repository;
|
|
import com.rongyichuang.player.entity.ActivityPlayer;
|
import org.springframework.data.jpa.repository.JpaRepository;
|
import org.springframework.data.jpa.repository.Query;
|
import org.springframework.data.repository.query.Param;
|
import org.springframework.stereotype.Repository;
|
|
import java.util.List;
|
import java.util.Optional;
|
|
/**
|
* 活动选手Repository接口
|
*/
|
@Repository
|
public interface ActivityPlayerRepository extends JpaRepository<ActivityPlayer, Long> {
|
|
/**
|
* 根据活动ID查找参赛选手
|
*/
|
List<ActivityPlayer> findByActivityId(Long activityId);
|
|
/**
|
* 根据选手ID查找参赛记录
|
*/
|
List<ActivityPlayer> findByPlayerId(Long playerId);
|
|
/**
|
* 根据活动ID和选手ID查找参赛记录列表，按创建时间降序排序
|
*/
|
List<ActivityPlayer> findByActivityIdAndPlayerIdOrderByCreateTimeDesc(Long activityId, Long playerId);
|
|
/**
|
* 根据阶段ID查找参赛选手
|
*/
|
List<ActivityPlayer> findByStageId(Long stageId);
|
|
/**
|
* 根据区域ID查找参赛选手
|
*/
|
List<ActivityPlayer> findByRegionId(Long regionId);
|
|
/**
|
* 根据活动ID和区域ID查找参赛选手
|
*/
|
List<ActivityPlayer> findByActivityIdAndRegionId(Long activityId, Long regionId);
|
|
/**
|
* 根据状态查找参赛选手
|
*/
|
List<ActivityPlayer> findByState(Integer state);
|
|
/**
|
* 根据活动ID统计参赛选手数量（包括未审核和审核通过的，排除审核驳回的）
|
*/
|
@Query("SELECT COUNT(ap) FROM ActivityPlayer ap WHERE ap.activityId = :activityId AND ap.state IN (0, 1)")
|
Long countByActivityId(@Param("activityId") Long activityId);
|
|
/**
|
* 查找区域的参赛选手数量
|
*/
|
@Query("SELECT COUNT(ap) FROM ActivityPlayer ap WHERE ap.regionId = :regionId AND ap.state = 1")
|
Long countByRegionId(@Param("regionId") Long regionId);
|
|
/**
|
* 根据活动ID查找参赛选手，包含选手和区域信息
|
*/
|
@Query("SELECT ap FROM ActivityPlayer ap " +
|
"LEFT JOIN FETCH ap.player p " +
|
"LEFT JOIN FETCH ap.region r " +
|
"WHERE ap.activityId = :activityId AND ap.state = 1 " +
|
"ORDER BY ap.createTime DESC")
|
List<ActivityPlayer> findByActivityIdWithPlayerAndRegion(@Param("activityId") Long activityId);
|
|
/**
|
* 根据区域统计各活动的参赛人数
|
*/
|
@Query("SELECT ap.activityId, ap.regionId, COUNT(ap) " +
|
"FROM ActivityPlayer ap " +
|
"WHERE ap.state = 1 " +
|
"GROUP BY ap.activityId, ap.regionId")
|
List<Object[]> countByActivityAndRegion();
|
|
/**
|
* 查找排名前N的选手
|
*/
|
@Query("SELECT ap FROM ActivityPlayer ap " +
|
"WHERE ap.activityId = :activityId AND ap.state = 1 AND ap.rank IS NOT NULL " +
|
"ORDER BY ap.rank ASC")
|
List<ActivityPlayer> findTopRankedPlayers(@Param("activityId") Long activityId);
|
|
/**
|
* 统计指定状态的参赛选手数量
|
*/
|
long countByState(Integer state);
|
|
/**
|
* 根据阶段ID和状态统计参赛选手数量
|
*/
|
Long countByStageIdAndState(Long stageId, Integer state);
|
|
/**
|
* 根据活动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);
|
}
|
