[ bible Cash ] 주간 읽은 양 랭킹 기능 추가

[ 요구 사항 ]

주일 ~ 토요일까지 가장 많이 읽은 순위를 보여주는 기능을 추가 해달라고 하였습니다.

 

[ 구현 ]

- 매주 읽은 양을 각자가 읽은 양이 업데이트 되는 weeklyRankingBoard entity를 만들었습니다.

- 스케쥴러로 weeklyRankingBoard의 읽은 양은 매주 주일 00:00분에 0이 되도록 했습니다.

- 기존 읽은 양 저장할 때 로직에 weeklyRankingBoard 에도 읽은 양이 저장되도록 했습니다.

- 조회 당시의 기준 날짜로 해당 주에 주일날짜와 토요일 날짜 안에서 데이터를 조회 되도록 하였습니다.

- 스케쥴러로 읽은 양이 초기화 되어 0이 된 경우, 화면에서 읽은 양이 0인 경우는 보이지 않도록 만들었습니다.

[ 코드 ]

- weeklyRankingBoard  entity 생성

@Entity
@NoArgsConstructor
@Getter
public class WeeklyRankingBoard {
    @Id
    @GeneratedValue
    private Long idx;

    @ManyToOne
    private Member member;

    @Column(nullable = false)
    private int readCount = 0;

    @Column(columnDefinition = "DATE")
    private LocalDate readTime;

    public static WeeklyRankingBoard create(Member member,int readCount,LocalDate readTime){
        WeeklyRankingBoard weeklyRankingBoard = new WeeklyRankingBoard();
        weeklyRankingBoard.member = member;
        weeklyRankingBoard.readCount = readCount;
        weeklyRankingBoard.readTime = readTime;
        return weeklyRankingBoard;
    }
}

 

 

- WeeklyRankingBoardRepository 생성

public interface WeeklyRankingBoardRepository extends JpaRepository<WeeklyRankingBoard,Long> {
    @Modifying
    @Transactional
    @Query("UPDATE WeeklyRankingBoard w SET w.readCount = w.readCount + :readCount, w.readTime = :readTime WHERE w.member.idx = :memberIdx")
    int updateReadCountAndTime(Long memberIdx, int readCount, LocalDate readTime);

    Optional<WeeklyRankingBoard> findByMemberIdx(Long idx);

    @Transactional
    @Modifying
    @Query("UPDATE WeeklyRankingBoard w SET w.readCount = 0")
    void resetWeeklyRankingBoard();

    @Query(value = """
    WITH Ranked AS (
        SELECT m.name, m.member_group, w.read_count,
               DENSE_RANK() OVER (ORDER BY w.read_count DESC) AS ranking
        FROM weekly_ranking_board w
        JOIN member m ON w.member_idx = m.idx
        WHERE w.read_time BETWEEN :startOfWeek AND :endOfWeek
    )
    SELECT name, member_group, ranking,read_count
    FROM Ranked
    WHERE ranking <= 5
    ORDER BY ranking
""", nativeQuery = true)
    List<Object[]> findTop5ByReadCountWithRanking(
            @Param("startOfWeek") LocalDate startOfWeek,
            @Param("endOfWeek") LocalDate endOfWeek
    );

}

 

 

- 스케쥴러 코드 추가

/**
 * 매주 주일 00시 마다 readCount 초기화
 * */
@Scheduled(cron = "0 0 0 * * SUN")// 매주 주일 00:00 실행
public void resetWeeklyRankingBoard(){
    weeklyRankingBoardRepository.resetWeeklyRankingBoard();
}

 

 

- HistoryService의 코드 추가

@Slf4j
@Service
@Transactional
@RequiredArgsConstructor
public class HistoryService {
    private final HistoryRepository historyRepository;
    private final MemberRepository memberRepository;
    private final WeeklyRankingBoardRepository weeklyRankingBoardRepository;

    public final static int STANDARD_READ_COUNT = 5;

    public void saveHistory(Member member){
        // member readCount 업데이트 회원가입 할 때부터 기본적으로 생성되어있음
        memberRepository.incrementReadCount(member.getIdx(),STANDARD_READ_COUNT);


        // history에 기록 저장
        History history = History.create(member, STANDARD_READ_COUNT, LocalDate.now());
        historyRepository.save(history);


        // WeeklyRankingBoard 기록 저장
        Optional<WeeklyRankingBoard> memberOptional = weeklyRankingBoardRepository.findByMemberIdx(member.getIdx());
        if(memberOptional.isEmpty()){
            WeeklyRankingBoard weeklyRankingBoard = WeeklyRankingBoard.create(member, STANDARD_READ_COUNT, LocalDate.now());
            weeklyRankingBoardRepository.save(weeklyRankingBoard);
        } else {
            weeklyRankingBoardRepository.updateReadCountAndTime(member.getIdx(),STANDARD_READ_COUNT,LocalDate.now());
        }

    }

    public void saveAdd(Member member,int additionalVerse){
        // member readCount 업데이트
        memberRepository.incrementReadCount(member.getIdx(),additionalVerse);

        // history에 기록 저장
        History history = History.create(member, additionalVerse, LocalDate.now());
        historyRepository.save(history);

        // WeeklyRankingBoard 기록 저장
        Optional<WeeklyRankingBoard> memberOptional = weeklyRankingBoardRepository.findByMemberIdx(member.getIdx());
        if(memberOptional.isEmpty()){
            WeeklyRankingBoard weeklyRankingBoard = WeeklyRankingBoard.create(member, additionalVerse, LocalDate.now());
            weeklyRankingBoardRepository.save(weeklyRankingBoard);
        } else {
            weeklyRankingBoardRepository.updateReadCountAndTime(member.getIdx(),additionalVerse,LocalDate.now());

        }
    }


}

 

 

- HistoryController 수정

@Controller
@Slf4j
@RequiredArgsConstructor
public class HistoryController {

    private final HistoryService historyService;

    @PostMapping("/history")
    public String saveHistory(HttpSession session){
        Member member = (Member) session.getAttribute("member");
        log.info("member: {}",member.getUserId());
        historyService.saveHistory(member);
        return "redirect:/home";
    }

    @PostMapping("/add")
    public String addAdditionalVerse(@RequestParam("additionalVerse") int additionalVerse, HttpSession session) {
        log.info("추가 읽은 구절 수: {}", additionalVerse);
        Member member = (Member)session.getAttribute("member");
        log.info("member : {}",member.getName());
        historyService.saveAdd(member,additionalVerse);
        return "redirect:/home"; // 홈 화면으로 리디렉트
    }
}

 

- HomeController

@Slf4j
@Controller
@RequiredArgsConstructor
public class HomeController {

    private final HistoryRepository historyRepository;
    private final MemberRepository memberRepository;
    private final WeeklyRankingBoardRepository weeklyRankingBoardRepository;

    @GetMapping("/home")
    public String home(HttpSession session, Model model){
        // 오늘까지 내가 읽은 양
        Member member =(Member) session.getAttribute("member");
        int readCount = member.getReadCount();
        // model.addAttribute("MyReadCount",readCount);

        // 이번주 가장 많이 읽은 순위
        LocalDate now = LocalDate.now();
        LocalDate startOfWeek = now.with(TemporalAdjusters.previousOrSame(DayOfWeek.SUNDAY));
        LocalDate endOfWeek = now.with(TemporalAdjusters.nextOrSame(DayOfWeek.SATURDAY));
        log.info("startOfWeek : {} ,endOfWeek : {} ",startOfWeek,endOfWeek);
        List<Object[]> weeklyRanking = weeklyRankingBoardRepository.findTop5ByReadCountWithRanking(startOfWeek, endOfWeek);
        List<RankDto> weeklyRankinList = weeklyRanking.stream()
                .map(row -> new RankDto(
                        (String) row[0], // name
                        Group.valueOf((String) row[1]), // memberGroup
                        ((Number) row[2]).intValue(), // readCount
                        ((Number) row[3]).intValue() // ranking
                )).toList();
        log.info("weeklyRankinList : {}",weeklyRankinList);
        Map<Integer, List<RankDto>> weeklyRankinMap = weeklyRankinList.stream()
                .collect(Collectors.groupingBy(RankDto::getRanking));
        log.info("weeklyRankinMap : {}",weeklyRankinMap);
        model.addAttribute("weeklyRankinMap",weeklyRankinMap);


        // 현재까지 읽은 양이 제일 많은 순위 데이터 가져오기
        List<Object[]> results = memberRepository.findTop5ByReadCountWithRanking();
        List<RankDto> rankDtoList = results.stream()
                .map(row -> new RankDto(
                        (String) row[0],
                        Group.valueOf((String) row[1]),
                        ((Number) row[2]).intValue(),
                        ((Number) row[3]).intValue()
                ))
                .collect(Collectors.toList());

        Map<Integer, List<RankDto>> groupedRanking = rankDtoList.stream()
                .collect(Collectors.groupingBy(RankDto::getRanking, LinkedHashMap::new, Collectors.toList()));
        model.addAttribute("groupedRanking", groupedRanking);


        //
        List<Object[]> countSumByGroup = memberRepository.getReadCountSumByGroup();
        List<GroupReadCountDto> groupReadCountDtos = countSumByGroup.stream()
                .map(row -> new GroupReadCountDto(
                        Group.valueOf((String) row[0]), // Enum 변환
                        ((Number) row[1]).intValue()   // readCount 합계 변환
                ))
                .collect(Collectors.toList());
        model.addAttribute("groupReadCountDtos", groupReadCountDtos);
        return "home";
    }
}

 

- home.html 

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org" lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>오늘의 말씀</title>
    <link rel="stylesheet" th:href="@{/css/style.css}">
</head>
<body>
<div class="container">
    <div class="calendar-header">
        <div class="current-month" id="yearText">2025년</div>
        <select id="monthSelect" class="dropdown"></select>
        <button class="nav-button" id="todayButton">오늘</button>
    </div>

    <div id="daysOfWeek"></div>
    <div id="calendar"></div>

    <div class="footer">
        <!-- '오늘의 말씀' 버튼을 link로 감싸지 않고, 클릭 이벤트로 처리하도록 수정 -->
        <button class="verse-button" id="verseButton">오늘의 말씀</button>

        <h2>현재까지 가장 많이 읽은 순위</h2>
        <div th:each="entry : ${groupedRanking}">
            <span th:text="|${entry.key}등 |"></span>

            <!-- 순위 내 멤버들의 이름(그룹) 및 읽은 횟수 출력 -->
            <span th:each="member, iterStat : ${entry.value}">
        <span th:text="|${member.memberGroup} ${member.name} ${member.readCount}장|"></span>

                <!-- 마지막 멤버가 아니면 쉼표 추가 -->
        <span th:if="!${iterStat.last}">, </span>
    </span>
            <br/>
        </div>

        <h2>이번주 순위</h2>
        <div th:each="entry : ${weeklyRankinMap}">
            <span th:text="|${entry.key}등 |"></span>

            <span th:each="member, iterStat : ${entry.value}" th:if="${member.readCount > 0}">
        <span th:text="|${member.name} ${member.memberGroup} ${member.readCount}장|"></span>
        <span th:if="!${iterStat.last}">, </span>
    </span>
            <br/>
        </div>

        <h2>그룹별 읽은 장 수</h2>
        <div th:each="groupReadCount : ${groupReadCountDtos}">
            <span th:text="${groupReadCount.memberGroup} + ' 총 ' + ${groupReadCount.totalReadCount} + '장'"></span>
            <br/>
        </div>

        <h3>추가로 읽은 말씀</h3>
        <form id="additionalVerseForm" th:action="@{/add}" method="post">
            <div class="additional-verse">
                <input type="number" id="additionalVerseInput" name="additionalVerse" class="number-input" required>
                <button type="submit" class="verse-button" id="additionalVerseButton">추가</button>
            </div>
        </form>
    </div>

</div>

<!-- JavaScript -->
<script th:src="@{/js/calendar.js}"></script>
</body>
</html>