프로젝트

[ bible Cash ] 개인 랭킹, 그룹별 랭킹, 더 읽은 말씀 기능 추가

do_it_zero 2025. 2. 4. 11:04 
GroupReadCountDto
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class GroupReadCountDto {
    private Group memberGroup;
    private int totalReadCount;
}

 

RankDto

@Getter
@NoArgsConstructor
@AllArgsConstructor
public class RankDto {
    private String name;
    private Group memberGroup;
    private int ranking;
}

 

MemberRepository
    @Query(value = """
    WITH Ranked AS (
        SELECT name, member_group, read_count,
               DENSE_RANK() OVER (ORDER BY read_count DESC) AS ranking
        FROM member
    )
    SELECT name, member_group, ranking
    FROM Ranked 
    WHERE ranking <= 5 
    ORDER BY ranking
""", nativeQuery = true)
    List<Object[]> findTop5ByReadCountWithRanking();

    @Query(value = """
    SELECT member_group, SUM(read_count) AS total_read_count
    FROM member
    GROUP BY member_group
    ORDER BY total_read_count DESC
""", nativeQuery = true)
    List<Object[]> getReadCountSumByGroup();

 

 

HomeController

@Slf4j
@Controller
@RequiredArgsConstructor
public class HomeController {

    private final HistoryRepository historyRepository;
    private final MemberRepository memberRepository;

    @GetMapping("/home")
    public String home(HttpSession session, Model model){
        // 읽음 이력 가져오기
        Member member =(Member) session.getAttribute("member");
        List<History> historyList = historyRepository.findByMemberIdx(member.getIdx());

        // 랭킹 데이터 가져오기
        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()
                ))
                .collect(Collectors.toList());
        // 순위(ranking)별로 그룹화
        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>

        <!-- 추가말씀 섹션 -->
        <form id="additionalVerseForm">
            <div class="additional-verse">
                <input type="number" id="additionalVerseInput" class="number-input" required>
                <button type="submit" class="verse-button" id="additionalVerseButton">더 읽은 말씀</button>
            </div>
        </form>

        <h2>말씀 읽은 순위</h2>
        <div th:each="entry : ${groupedRanking}">
            <span th:text="|${entry.key}등 |"></span>

            <!-- 순위 내 멤버들의 이름(그룹) 형식으로 출력 -->
            <span th:each="member, iterStat : ${entry.value}">
        <span th:text="${member.name} + '(' + ${member.memberGroup} + ')'"></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>

    </div>

</div>

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