문의드립니다. 채택완료

<?php

include_once('./_common.php');

 

// 날짜 간격 설정 (오늘과 비교할 과거 날짜, -1은 하루 전)

$days_ago = -1;

 

// 오늘과 비교 날짜 계산

$today = date('Y-m-d');

$compare_date = date('Y-m-d', strtotime($days_ago.' days'));

 

// 출력 인원수

$limit = 10;

 

// 제외할 아이디

$exclude_ids_array = ['webmaster', 'test3'];

$exclude_ids = implode("','", $exclude_ids_array);

 

// 오늘의 총 포인트와 오늘 획득 포인트를 가져오는 쿼리

$sql_today = "

    SELECT

        m.mb_id,

        m.mb_nick,

        m.mb_point as total_points,

        IFNULL(SUM(CASE WHEN DATE(p.po_datetime) = '{$today}' THEN p.po_point ELSE 0 END), 0) as today_points

    FROM

        {$g5['member_table']} m

    LEFT JOIN

        {$g5['point_table']} p ON m.mb_id = p.mb_id

    WHERE

        m.mb_id NOT IN ('{$exclude_ids}')

    GROUP BY

        m.mb_id

    ORDER BY

        m.mb_point DESC

    LIMIT {$limit}

";

$today_result = sql_query($sql_today);

 

// 어제의 총 포인트를 가져오는 쿼리

$sql_compare = "

    SELECT

        m.mb_id,

        m.mb_point - IFNULL(SUM(CASE WHEN DATE(p.po_datetime) = '{$today}' THEN p.po_point ELSE 0 END), 0) as yesterday_points

    FROM

        {$g5['member_table']} m

    LEFT JOIN

        {$g5['point_table']} p ON m.mb_id = p.mb_id

    WHERE

        m.mb_id NOT IN ('{$exclude_ids}')

    GROUP BY

        m.mb_id

    ORDER BY

        yesterday_points DESC

";

$compare_result = sql_query($sql_compare);

 

// 비교 날짜의 랭킹과 포인트를 계산

$compare_ranking = [];

$compare_points = [];

$rank = 1;

while ($row = sql_fetch_array($compare_result)) {

    $compare_ranking[$row['mb_id']] = $rank++;

    $compare_points[$row['mb_id']] = (float)$row['yesterday_points'];

}

 

// 순위 변동 계산 함수

function get_rank_change($mb_id, $current_rank, $compare_ranking) {

    if (isset($compare_ranking[$mb_id])) {

        $compare_rank = $compare_ranking[$mb_id];

        $change = $compare_rank - $current_rank;

        if ($change > 0) {

            return "<span style='color:green'>▲ {$change}</span>";

        } elseif ($change < 0) {

            return "<span style='color:red'>▼ " . abs($change) . "</span>";

        } else {

            return "-";

        }

    } else {

        return "<span style='color:blue'>New</span>";

    }

}

?>

<style>

    .ranking-container {

        max-width: 800px;

        margin: 0 auto;

        padding: 20px 0;

    }

    .ranking-title {

        background: #6f809a;

        color: #fff;

        text-align: center;

        padding: 12px;

        font-size: 1em;

        margin: 0 0 20px;

        border-radius: 4px;

    }

    .ranking-table {

        background: #fff;

        border-radius: 8px;

        box-shadow: 0 2px 8px rgba(0,0,0,0.1);

        overflow: hidden;

    }

    .ranking-table ul {

        display: flex;

        align-items: center;

        padding: 15px 10px;

        border-bottom: 1px solid #eee;

        margin: 0;

    }

    .ranking-table ul:first-child {

        background: #f8f9fa;

        font-weight: 600;

        border-bottom: 2px solid #e9ecef;

    }

    .ranking-table ul:last-child {

        border-bottom: none;

    }

    .ranking-table ul:hover:not(:first-child) {

        background: #f8f9fa;

    }

    .ranking-table li {

        flex: 1;

        text-align: center;

        white-space: nowrap;

        overflow: hidden;

        text-overflow: ellipsis;

        padding: 0 5px;

    }

    .ranking-table li:first-child {

        flex: 0 0 8%;

    }

    .ranking-table li:last-child {

        flex: 0 0 10%;

    }

    .point-change-positive {

        color: #28a745;

    }

    .point-change-negative {

        color: #dc3545;

    }

</style>

 

<div class="ranking-container">

    <h2 class="ranking-title">포인트 랭킹</h2>

    <div class="ranking-table">

        <ul>

            <li>순위</li>

            <li>닉네임</li>

            <li>어제 포인트</li>

            <li>오늘 포인트</li>

            <li>변동 포인트</li>

            <li>총 포인트</li>

            <li>순위변동</li>

        </ul>

        <?php

        $rank = 1;

        while ($row = sql_fetch_array($today_result)) {

            $mb_id = $row['mb_id'];

            $mb_nick = $row['mb_nick'];

            $total_points = (float)$row['total_points'];

            $today_points = (float)$row['today_points'];

            $yesterday_points = isset($compare_points[$mb_id]) ? $compare_points[$mb_id] : 0;

            $point_change = $today_points;

            $rank_change = get_rank_change($mb_id, $rank, $compare_ranking);

           

            $point_change_class = $point_change >= 0 ? 'point-change-positive' : 'point-change-negative';

            $point_change_symbol = $point_change >= 0 ? '+' : '';

 

            echo "<ul>";

            echo "<li>{$rank}</li>";

            echo "<li>{$mb_nick}</li>";

            echo "<li>" . number_format($yesterday_points) . "</li>";

            echo "<li>" . number_format($total_points) . "</li>";

            echo "<li class='{$point_change_class}'>{$point_change_symbol}" . number_format($point_change) . "</li>";

            echo "<li>" . number_format($total_points) . "</li>";

            echo "<li>{$rank_change}</li>";

            echo "</ul>";

           

            $rank++;

        }

        ?>

    </div>

</div>