dnssash/adm/statistics/ajax.data.php

<?php
include_once('./_common.php');

//if (!isset($_SERVER['HTTP_X_REQUESTED_WITH']) || strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) != 'xmlhttprequest') {
//    die(json_encode(['error' => '잘못된 접근입니다.']));
//}

$type = isset($_GET['type']) ? preg_replace('/[^a-z_]/i', '', $_GET['type']) : 'time';
$start_day = isset($_GET['start_day']) ? $_GET['start_day'] : G5_TIME_YMD;
$end_day = isset($_GET['end_day']) ? $_GET['end_day'] : G5_TIME_YMD;

$result = [
    'type' => 'bar',
    'title' => '',
    'labels' => [],
    'datasets' => [],
    'list' => [],
];

$data = [];
$sql = '';
$sql_common = " from {$g5['visit_table']} ";
$sql_search = " where vi_date between '{$start_day}' and '{$end_day}' ";

// 배너 테이블 존재 여부 확인
$banner_table = 'rb_banner';//isset($g5['banner_table']) ? $g5['banner_table'] : 'g5_shop_banner';

switch($type) {
    // --- 접속 통계 (기존 코드 유지) ---
    case 'time':
        $result['title'] = '시간별 접속 현황';
        $sql = " SELECT SUBSTRING(vi_time, 1, 2) as `key`, count(vi_id) as `cnt` {$sql_common} {$sql_search} GROUP BY `key` ORDER BY `key` ";
        break;
    case 'week':
        $result['title'] = '요일별 접속 현황';
        $sql = " SELECT WEEKDAY(vi_date) as `key`, count(vi_id) as `cnt` {$sql_common} {$sql_search} GROUP BY `key` ORDER BY `key` ";
        break;
    case 'date':
        $result['title'] = '일별 접속 현황';
        $result['type'] = 'line';
        $sql = " SELECT vi_date as `key`, count(vi_id) as `cnt` {$sql_common} {$sql_search} GROUP BY `key` ORDER BY `key` ";
        break;
    case 'month':
        $result['title'] = '월별 접속 현황';
        $result['type'] = 'line';
        $sql = " SELECT SUBSTRING(vi_date, 1, 7) as `key`, count(vi_id) as `cnt` {$sql_common} {$sql_search} GROUP BY `key` ORDER BY `key` ";
        break;
    case 'domain':
        $result['title'] = '접속전 도메인 TOP 50';
        $sql = " SELECT vi_referer as `key`, count(vi_id) as `cnt` {$sql_common} {$sql_search} AND vi_referer != '' GROUP BY `key` ORDER BY `cnt` DESC LIMIT 50";
        break;
    case 'ip':
        $result['title'] = '접속 IP TOP 50';
        $sql = " SELECT vi_ip as `key`, count(vi_id) as `cnt` {$sql_common} {$sql_search} GROUP BY `key` ORDER BY `cnt` DESC LIMIT 50";
        break;
    case 'browser':
        $result['title'] = '브라우저별 접속 현황';
        $result['type'] = 'pie';
        $sql = " SELECT vi_browser as `key`, count(vi_id) as `cnt` {$sql_common} {$sql_search} GROUP BY `key` ORDER BY `cnt` DESC LIMIT 20";
        break;
    case 'os':
        $result['title'] = '운영체제별 접속 현황';
        $result['type'] = 'pie';
        $sql = " SELECT vi_os as `key`, count(vi_id) as `cnt` {$sql_common} {$sql_search} GROUP BY `key` ORDER BY `cnt` DESC LIMIT 20";
        break;

    // --- 게시물 통계 (수정된 부분) ---
    case 'board_view':
        $result['title'] = '게시판별 총 조회수';
        // 💡 [수정] 모든 게시판을 순회하며 실제 wr_hit 합계 계산 (성능 최적화)
        $boards = sql_query("SELECT bo_table, bo_subject FROM {$g5['board_table']} ORDER BY bo_count_write DESC");
        $board_stats = [];
        while($row = sql_fetch_array($boards)) {
            $write_table = $g5['write_prefix'] . $row['bo_table'];
            $chk = sql_fetch("SHOW TABLES LIKE '{$write_table}'");
            if ($chk) {
                $stat = sql_fetch("SELECT SUM(wr_hit) as cnt FROM {$write_table}");
                if ($stat['cnt'] > 0) {
                    $board_stats[] = ['key' => $row['bo_subject'], 'cnt' => $stat['cnt']];
                }
            }
        }
        // 조회수 내림차순 정렬
        usort($board_stats, function($a, $b) { return $b['cnt'] - $a['cnt']; });
        $board_stats = array_slice($board_stats, 0, 20); // 상위 20개만
        
        // SQL 실행 결과를 흉내내어 데이터 처리 로직으로 넘김
        $data_source = $board_stats;
        break;

    case 'board_write':
        $result['title'] = '게시판별 총 게시물 수';
        $sql = " SELECT bo_subject as `key`, bo_count_write as `cnt` FROM {$g5['board_table']} ORDER BY `cnt` DESC LIMIT 20";
        break;
    case 'board_comment':
        $result['title'] = '게시판별 총 댓글 수';
        $sql = " SELECT bo_subject as `key`, bo_count_comment as `cnt` FROM {$g5['board_table']} ORDER BY `cnt` DESC LIMIT 20";
        break;

    case 'post_view':
        $result['title'] = '게시물별 조회수 TOP 20';
        // 💡 [수정] 모든 게시판을 순회하며 상위 게시물 수집 (성능 최적화)
        $boards = sql_query("SELECT bo_table FROM {$g5['board_table']}");
        $all_posts = [];
        while($row = sql_fetch_array($boards)) {
            $write_table = $g5['write_prefix'] . $row['bo_table'];
            $chk = sql_fetch("SHOW TABLES LIKE '{$write_table}'");
            if ($chk) {
                // 각 게시판에서 상위 20개씩 가져옴
                $res = sql_query("SELECT wr_subject as `key`, wr_hit as `cnt` FROM {$write_table} WHERE wr_is_comment = 0 ORDER BY wr_hit DESC LIMIT 20");
                while($post = sql_fetch_array($res)) {
                    $all_posts[] = $post;
                }
            }
        }
        // 전체에서 다시 정렬하여 상위 20개 추출
        usort($all_posts, function($a, $b) { return $b['cnt'] - $a['cnt']; });
        $data_source = array_slice($all_posts, 0, 20);
        break;

    case 'post_comment':
        $result['title'] = '게시물별 댓글 수 TOP 20';
        // 💡 [수정] 모든 게시판을 순회하며 상위 게시물 수집
        $boards = sql_query("SELECT bo_table FROM {$g5['board_table']}");
        $all_posts = [];
        while($row = sql_fetch_array($boards)) {
            $write_table = $g5['write_prefix'] . $row['bo_table'];
            $chk = sql_fetch("SHOW TABLES LIKE '{$write_table}'");
            if ($chk) {
                $res = sql_query("SELECT wr_subject as `key`, wr_comment as `cnt` FROM {$write_table} WHERE wr_is_comment = 0 ORDER BY wr_comment DESC LIMIT 20");
                while($post = sql_fetch_array($res)) {
                    $all_posts[] = $post;
                }
            }
        }
        usort($all_posts, function($a, $b) { return $b['cnt'] - $a['cnt']; });
        $data_source = array_slice($all_posts, 0, 20);
        break;

    // --- 배너 통계 (기존 코드 유지) ---
    case 'position':
        $result['title'] = '배너 위치별 클릭수';
        $result['type'] = 'pie';
        $chk = sql_fetch("SHOW TABLES LIKE '{$banner_table}'");
        if ($chk) {
            $sql = " SELECT bn_position as `key`, SUM(bn_hit) as `cnt` FROM {$banner_table} GROUP BY `key` ORDER BY `cnt` DESC ";
        }
        break;
    case 'each':
        $result['title'] = '개별 배너 클릭수 TOP 20';
        $chk = sql_fetch("SHOW TABLES LIKE '{$banner_table}'");
        if ($chk) {
            $sql = " SELECT bn_alt as `key`, bn_hit as `cnt` FROM {$banner_table} ORDER BY `cnt` DESC LIMIT 20";
        }
        break;
    
    // --- 검색어 통계 (기존 코드 유지) ---
    case 'keyword_today':
    case 'keyword_yesterday':
    case 'keyword_week':
    case 'keyword_month':
        $result['title'] = '검색어 순위';
        $from = $to = '';
        if($type === 'keyword_today') { $from = $to = G5_TIME_YMD; }
        if($type === 'keyword_yesterday') { $from = $to = date('Y-m-d', strtotime('-1 day')); }
        if($type === 'keyword_week') { $from = date('Y-m-d', strtotime('-6 day')); $to = G5_TIME_YMD; }
        if($type === 'keyword_month') { $from = date('Y-m-01'); $to = G5_TIME_YMD; }
        $sql = " SELECT pp_word as `key`, pp_count as `cnt` FROM {$g5['popular_table']} WHERE pp_date BETWEEN '{$from}' AND '{$to}' GROUP BY pp_word ORDER BY `cnt` DESC LIMIT 50";
        break;
}

// 데이터 처리 공통 로직
if (isset($data_source)) {
    // PHP 배열로 데이터가 준비된 경우 (게시판 통계 등)
    $sum_count = 0;
    $max = 0;
    foreach($data_source as $row) {
        $sum_count += $row['cnt'];
        if ($row['cnt'] > $max) $max = $row['cnt'];
    }
    foreach($data_source as $row) {
        $key = $row['key'] ?: '알수없음';
        $result['labels'][] = $key;
        $data[] = $row['cnt'];
        $rate = ($sum_count > 0) ? $row['cnt'] / $sum_count * 100 : 0;
        $bar_width = ($max > 0) ? $row['cnt'] / $max * 100 : 0;
        $result['list'][] = [
            'key' => $key,
            'count' => number_format($row['cnt']),
            'rate' => number_format($rate, 1),
            'bar_width' => $bar_width
        ];
    }
} else if ($sql) {
    // SQL 쿼리로 데이터가 준비된 경우 (접속 통계 등)
    $res = sql_query($sql, false);
    if ($res) {
        $sum_count = 0;
        $max = 0;
        $temp_list = [];
        while($row = sql_fetch_array($res)) {
            $temp_list[] = $row;
            $sum_count += $row['cnt'];
            if ($row['cnt'] > $max) $max = $row['cnt'];
        }
        foreach($temp_list as $row) {
            $key = $row['key'] ?: '알수없음';
            if ($type == 'week') {
                $week_name = ['월', '화', '수', '목', '금', '토', '일'];
                $key = isset($week_name[$row['key']]) ? $week_name[$row['key']] : '알수없음';
            }
            $result['labels'][] = $key;
            $data[] = $row['cnt'];
            $rate = ($sum_count > 0) ? $row['cnt'] / $sum_count * 100 : 0;
            $bar_width = ($max > 0) ? $row['cnt'] / $max * 100 : 0;
            $result['list'][] = [
                'key' => $key,
                'count' => number_format($row['cnt']),
                'rate' => number_format($rate, 1),
                'bar_width' => $bar_width
            ];
        }
    }
}

$dataset_item = ['label' => '수', 'data' => $data];
if (in_array($result['type'], ['pie', 'doughnut', 'radar'])) {
    $colors = [
        'rgba(255, 99, 132, 0.7)', 'rgba(54, 162, 235, 0.7)', 'rgba(255, 206, 86, 0.7)',
        'rgba(75, 192, 192, 0.7)', 'rgba(153, 102, 255, 0.7)', 'rgba(255, 159, 64, 0.7)'
    ];
    $dataset_item['backgroundColor'] = array_slice($colors, 0, count($data));
} else {
    $dataset_item['backgroundColor'] = 'rgba(54, 162, 235, 0.5)';
}

$result['datasets'][] = $dataset_item;

echo json_encode($result);
?>