<?php
// 데이터베이스 연결 설정
$server = "100.80.3.6";
$user = "firstgarden";
$pass = "Fg9576861!";
$db = "firstgarden";

// MySQLi 연결 생성
$conn = new mysqli($server, $user, $pass, $db);

// 연결 확인
if ($conn->connect_error) {
    die("연결 실패: " . $conn->connect_error);
}


// 선택한 날짜 가져오기
$date = isset($_GET['date']) ? $_GET['date'] : date('Y-m-d');

// 어제 날짜 계산
$yesterday = date('Y-m-d', strtotime($date . ' -1 day'));

// SQL 쿼리 실행
$sql = "SELECT category_name, product_name, option_name, qty FROM product_quantities WHERE date = '$date'";
$result = $conn->query($sql);

// 어제 데이터 가져오기
$sql_yesterday = "SELECT category_name, product_name, option_name, qty FROM product_quantities WHERE date = '$yesterday'";
$result_yesterday = $conn->query($sql_yesterday);

// 데이터 초기화
$data = [
    '홈페이지' => [
        '1부' => [
            'R석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ],
            'S석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ],
            'A석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ]
        ],
        '2부' => [
            'R석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ],
            'S석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ],
            'A석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ]
        ]
    ], 
    '큐패스' => [
        '1부' => [
            'R석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ],
            'S석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ],
            'A석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ]
        ],
        '2부' => [
            'R석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ],
            'S석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ],
            'A석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ]
        ]
    ]
];
$y_data = [
    '홈페이지' => [
        '1부' => [
            'R석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ],
            'S석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ],
            'A석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ]
        ],
        '2부' => [
            'R석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ],
            'S석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ],
            'A석' => [
                '대인' => 0, '소인' => 0, '36개월미만' => 0
            ]
        ]
    ], 
    '큐패스' => [
        '1부' => [
            'R석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ],
            'S석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ],
            'A석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ]
        ],
        '2부' => [
            'R석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ],
            'S석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ],
            'A석' => [
                '대인' => 0, '소인' => 0, '36개월 미만' => 0
            ]
        ]
    ]
];

// 데이터를 변환하는 로직
if ($result->num_rows > 0) {
    // 각 행 데이터 처리
    while ($row = $result->fetch_assoc()) {
        if (preg_match('/\[(.+?)\](.+?)\s(.+)/', $row['option_name'], $matches)) {
            $part = $matches[1];    // 예: '1부'
            $seat = $matches[2];    // 예: 'R석'
            $option = $matches[3];  // 예: '대인'
        }

        // $data[$row['category_name']][$row['product_name']][$part][$seat][$option] = $row['qty'];
        $data[$row['category_name']][$part][$seat][$option] = $row['qty'];
    }
}

if ($result_yesterday->num_rows > 0) {
    // 어제 각 행 데이터 처리
    while ($row = $result_yesterday->fetch_assoc()) {
        if (preg_match('/\[(.+?)\](.+?)\s(.+)/', $row['option_name'], $matches)) {
            $part = $matches[1];    // 예: '1부'
            $seat = $matches[2];    // 예: 'R석'
            $option = $matches[3];  // 예: '대인'
        }
        // $y_data[$row['category_name']][$row['product_name']][$part][$seat][$option] = $row['qty'];
        $y_data[$row['category_name']][$part][$seat][$option] = $row['qty'];
    }
}

function totalView($data) {
    $result = sprintf("%+d", $data);
    return $result;
}

// 합계 초기화
$web_part1 = $web_part2 = $qpos_part1 = $qpos_part2 = 0;

function catSum($is_web, $option, $data) {
    $sum = 0;
    if ( isset($data[$is_web][$option]) && is_array($data[$is_web][$option]) ) {
        foreach ($data[$is_web][$option] as $seat => $option) {
            foreach ($option as $row) {
                $sum += $row;

            }
        }
        return $sum;
    } else {
        return 0;
    }
    
}

// 합계 데이터 저장
$web_part1 = catSum('홈페이지', '1부', $data);
$web_part2 = catSum('홈페이지', '2부', $data);
$qpos_part1 = catSum('큐패스', '1부', $data);
$qpos_part2 = catSum('큐패스', '2부', $data);

$y_web_part1 = catSum('홈페이지', '1부', $y_data);
$y_web_part2 = catSum('홈페이지', '2부', $y_data);
$y_qpos_part1 = catSum('큐패스', '1부', $y_data);
$y_qpos_part2 = catSum('큐패스', '2부', $y_data);

$web_total = $web_part1 + $web_part2;
$qpos_total = $qpos_part1 + $qpos_part2;

$y_web_total = $y_web_part1 + $y_web_part2;
$y_qpos_total = $y_qpos_part1 + $y_qpos_part2;
$y_grand_total = $y_web_total + $y_qpos_total;

$sub_total1 = $web_part1 + $qpos_part1;
$sub_total2 = $web_part2 + $qpos_part2;

$grand_total = $web_total + $qpos_total;

$y_sub_total1 = $y_web_part1 + $y_qpos_part1;
$y_sub_total2 = $y_web_part2 + $y_qpos_part2;

$y_grand_total = $y_web_total + $y_qpos_total;

// 최종 업데이트 시간 알려주기
// 현재 서버의 시간대를 설정 (+9 시간, 한국 표준시)
date_default_timezone_set('Asia/Seoul');

// 현재 시각 가져오기
$currentDateTime = new DateTime();

// 현재 분을 확인하여 조정
if ((int)$currentDateTime->format('i') < 3) {
    // 현재 분이 3보다 작으면 1시간 전으로 이동
    $currentDateTime->modify('-1 hour');
}

// 03분으로 설정
$currentDateTime->setTime((int)$currentDateTime->format('H'), 3, 0);

$data['w'] = [
    0 => ['today' => $data['홈페이지']['1부']['R석']['대인'] + $data['홈페이지']['2부']['R석']['대인'], 
          'yesterday' => $y_data['홈페이지']['1부']['R석']['대인'] + $y_data['홈페이지']['2부']['R석']['대인']],
    1 => ['today' => $data['홈페이지']['1부']['R석']['소인'] + $data['홈페이지']['2부']['R석']['소인'], 
          'yesterday' => $y_data['홈페이지']['1부']['R석']['소인'] + $y_data['홈페이지']['2부']['R석']['소인']],
    2 => ['today' => $data['홈페이지']['1부']['R석']['36개월미만'] + $data['홈페이지']['2부']['R석']['36개월미만'], 
          'yesterday' => $y_data['홈페이지']['1부']['R석']['36개월미만'] + $y_data['홈페이지']['2부']['R석']['36개월미만']],
    3 => ['today' => $data['홈페이지']['1부']['S석']['대인'] + $data['홈페이지']['2부']['S석']['대인'], 
          'yesterday' => $y_data['홈페이지']['1부']['S석']['대인'] + $y_data['홈페이지']['2부']['S석']['대인']],
    4 => ['today' => $data['홈페이지']['1부']['S석']['소인'] + $data['홈페이지']['2부']['S석']['소인'], 
          'yesterday' => $y_data['홈페이지']['1부']['S석']['소인'] + $y_data['홈페이지']['2부']['S석']['소인']],
    5 => ['today' => $data['홈페이지']['1부']['S석']['36개월미만'] + $data['홈페이지']['2부']['S석']['36개월미만'], 
          'yesterday' => $y_data['홈페이지']['1부']['S석']['36개월미만'] + $y_data['홈페이지']['2부']['S석']['36개월미만']],
    6 => ['today' => $data['홈페이지']['1부']['A석']['대인'] + $data['홈페이지']['2부']['A석']['대인'], 
          'yesterday' => $y_data['홈페이지']['1부']['A석']['대인'] + $y_data['홈페이지']['2부']['A석']['대인']],
    7 => ['today' => $data['홈페이지']['1부']['A석']['소인'] + $data['홈페이지']['2부']['A석']['소인'], 
          'yesterday' => $y_data['홈페이지']['1부']['A석']['소인'] + $y_data['홈페이지']['2부']['A석']['소인']],
    8 => ['today' => $data['홈페이지']['1부']['A석']['36개월미만'] + $data['홈페이지']['2부']['A석']['36개월미만'], 
          'yesterday' => $y_data['홈페이지']['1부']['A석']['36개월미만'] + $y_data['홈페이지']['2부']['A석']['36개월미만']]
];

$data['q'] = [
    0 => ['today' => $data['큐패스']['1부']['R석']['대인'] + $data['큐패스']['2부']['R석']['대인'], 
          'yesterday' => $y_data['큐패스']['1부']['R석']['대인'] + $y_data['큐패스']['2부']['R석']['대인']],
    1 => ['today' => $data['큐패스']['1부']['R석']['소인'] + $data['큐패스']['2부']['R석']['소인'], 
          'yesterday' => $y_data['큐패스']['1부']['R석']['소인'] + $y_data['큐패스']['2부']['R석']['소인']],
    2 => ['today' => $data['큐패스']['1부']['R석']['36개월 미만'] + $data['큐패스']['2부']['R석']['36개월 미만'], 
          'yesterday' => $y_data['큐패스']['1부']['R석']['36개월 미만'] + $y_data['큐패스']['2부']['R석']['36개월 미만']],
    3 => ['today' => $data['큐패스']['1부']['S석']['대인'] + $data['큐패스']['2부']['S석']['대인'], 
          'yesterday' => $y_data['큐패스']['1부']['S석']['대인'] + $y_data['큐패스']['2부']['S석']['대인']],
    4 => ['today' => $data['큐패스']['1부']['S석']['소인'] + $data['큐패스']['2부']['S석']['소인'], 
          'yesterday' => $y_data['큐패스']['1부']['S석']['소인'] + $y_data['큐패스']['2부']['S석']['소인']],
    5 => ['today' => $data['큐패스']['1부']['S석']['36개월 미만'] + $data['큐패스']['2부']['S석']['36개월 미만'], 
          'yesterday' => $y_data['큐패스']['1부']['S석']['36개월 미만'] + $y_data['큐패스']['2부']['S석']['36개월 미만']],
    6 => ['today' => $data['큐패스']['1부']['A석']['대인'] + $data['큐패스']['2부']['A석']['대인'], 
          'yesterday' => $y_data['큐패스']['1부']['A석']['대인'] + $y_data['큐패스']['2부']['A석']['대인']],
    7 => ['today' => $data['큐패스']['1부']['A석']['소인'] + $data['큐패스']['2부']['A석']['소인'], 
          'yesterday' => $y_data['큐패스']['1부']['A석']['소인'] + $y_data['큐패스']['2부']['A석']['소인']],
    8 => ['today' => $data['큐패스']['1부']['A석']['36개월 미만'] + $data['큐패스']['2부']['A석']['36개월 미만'], 
          'yesterday' => $y_data['큐패스']['1부']['A석']['36개월 미만'] + $y_data['큐패스']['2부']['A석']['36개월 미만']]
];

// 각 항목별 합계를 저장할 배열 초기화
$web_totals = [];
$qpos_totals = [];

// 홈페이지 합계 계산
foreach ($data['w'] as $key => $row) {
    $web_totals[$key]['today'] = $row['today'];
    $web_totals[$key]['yesterday'] = $row['yesterday'];
}

// 큐패스 합계 계산
foreach ($data['q'] as $key => $row) {
    $qpos_totals[$key]['today'] = $row['today'];
    $qpos_totals[$key]['yesterday'] = $row['yesterday'];
}

// 전체 합계
$grand_totals = [];
foreach ($web_totals as $key => $value) {
    $grand_totals[$key]['today'] = $web_totals[$key]['today'] + $qpos_totals[$key]['today'];
    $grand_totals[$key]['yesterday'] = $web_totals[$key]['yesterday'] + $qpos_totals[$key]['yesterday'];
}

$web_total = array_sum(array_column($data['w'], 'today'));
$y_web_total = array_sum(array_column($data['w'], 'yesterday'));
$qpos_total = array_sum(array_column($data['q'], 'today'));
$y_qpos_total = array_sum(array_column($data['q'], 'yesterday'));
$grand_total = $web_total + $qpos_total;
$y_grand_total = $y_web_total + $y_qpos_total;

?>

<style>
    h2 {margin-top: 30px !important;}
    .table {font-weight: 200;}
    .sum {font-weight: 500;}
    .tsum {font-weight: 600;}
    .chart {
        border: 2px solid #333333;
        border-radius: 3px;
        box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
        margin: 20px auto;
        background-color: #f8f9fa;
    }
</style>


<!DOCTYPE html>
<html>
<head>
    <title>데이터 조회</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
    <section class="container mt-3" style="max-width:1200px">
        <div>
            <form method="get">
                <div class="input-group mb-3" style="max-width:200px;">
                    <input class="form-control" type="date" name="date" value="<?php echo htmlspecialchars($date); ?>">
                    <input class="btn btn-primary" type="submit" value="조회">
                </div>
            </form>
        </div>
        <div class="text-end">
        최종 업데이트 : <?=$currentDateTime->format('Y-m-d H:i')?>
        </div>
        <?php if ($result->num_rows > 0) { ?>


        <h2>구매고객</h2>
        <table class="table table-bordered table-hover text-center align-middle">
            <colgroup>
                <col style="width: 100px";>
                <col>
                <col>
                <col>
                <col>
                <col>
                <col>
                <col>
                <col>
                <col>
                <col style="width:150px;">
            </colgroup>
            <thead class="table-primary">
                <tr>
                    <th class="align-middle" rowspan="2">구분</th>
                    <th class="align-middle" colspan="3">R석</th>
                    <th class="align-middle" colspan="3">S석</th>
                    <th class="align-middle" colspan="3">A석</th>
                    <th class="align-middle" rowspan="2">합계</th>
                </tr>
                <tr>
                    <th class="align-middle">대인</th>
                    <th class="align-middle">소인</th>
                    <th class="align-middle">유아</th>
                    <th class="align-middle">대인</th>
                    <th class="align-middle">소인</th>
                    <th class="align-middle">유아</th>
                    <th class="align-middle">대인</th>
                    <th class="align-middle">소인</th>
                    <th class="align-middle">유아</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <th class="align-middle">홈페이지</th>
                    <?php foreach ($data['w'] as $row) { ?>
                        <td class="align-middle">
                            <?=$row['today']?> 
                            <span class='text-muted'>(<?=totalView($row['today'] - $row['yesterday'])?>)</span>
                        </td>
                    <?php } ?>
                    <td class="align-middle tsum">
                        <?=$web_total?> 
                        <span class='text-muted'>(<?=totalView($web_total - $y_web_total)?>)</span>
                    </td>
                </tr>
                <tr>
                    <th class="align-middle">큐패스</th>
                    <?php foreach ($data['q'] as $row) { ?>
                        <td class="align-middle">
                            <?=$row['today']?> 
                            <span class='text-muted'>(<?=totalView($row['today'] - $row['yesterday'])?>)</span>
                        </td>
                    <?php } ?>
                    <td class="align-middle tsum">
                        <?=$qpos_total?> 
                        <span class='text-muted'>(<?=totalView($qpos_total - $y_qpos_total)?>)</span>
                    </td>
                </tr>
            </tbody>
            <!-- 합계 행 -->
            <tfoot class="table-info">
                <tr class="sum">
                    <th class="align-middle">합계</th>
                    <?php foreach ($grand_totals as $totals) { ?>
                        <td class="align-middle tsum">
                            <?=$totals['today']?>
                            <span class='text-muted'>(<?=totalView($totals['today'] - $totals['yesterday'])?>)</span>
                        </td>
                    <?php } ?>
                    <td class="align-middle tsum">
                        <?=$grand_total?>
                        <span class='text-muted'>(<?=totalView($grand_total - $y_grand_total)?>)</span>
                    </td>
                </tr>
            </tfoot>
        </table>

        <?php } else { ?>
        <p>결과가 없습니다.</p>
        <?php } ?>
    </section>


<?php
// 데이터 가져오기
$query = "
    SELECT 
        DATE(date) as order_date, 
        category_name, 
        product_name, 
        IF(option_name IN ('36개월 미만', '36개월미만'), '유아', REPLACE(option_name, '[1부]', '')) as option_name, 
        qty
    FROM product_quantities
    WHERE product_name = '어린이날'
    ORDER BY order_date, option_name;
";

$result = $conn->query($query);

// 데이터 처리
$data = [];
$previousData = []; // 전일 데이터를 저장

while ($row = $result->fetch_assoc()) {
    $date = $row['order_date'];
    $date = date('m/d', strtotime($date));
    $option = trim($row['option_name']);
    $todayQty = (int)$row['qty'];
    $category = $row['category_name'];

    // 데이터 정리 (홈페이지, 큐패스별)
    if (!isset($data[$date])) {
        $data[$date] = [
            'homepage' => 0,
            'qpass' => 0,
            'total' => 0,
            'options' => []
        ];
    }

    if ($category === "홈페이지") {
        $data[$date]['homepage'] += $todayQty;
    } else if ($category === "큐패스") {
        $data[$date]['qpass'] += $todayQty;
    }
    $data[$date]['total'] += $todayQty;

        // 옵션명에서 [2부] 텍스트 제거
    $option = str_replace('[2부]', '', $option);

    // 옵션별 수량 저장
    if (!isset($data[$date]['options'][$option])) {
        $data[$date]['options'][$option] = 0;
    }

    $data[$date]['options'][$option] += $todayQty;}

$dates = array_keys($data); // 날짜 목록 가져오기

$previousData = $data;

foreach ($dates as $index => $date) {
    if ($index === 0) {
        // 첫 번째 날짜는 전날 데이터가 없으므로 그대로 유지
        continue;
    }

    $previousDate = $dates[$index - 1]; // 전날 날짜

    // 현재 날짜 데이터를 처리
    foreach ($data[$date]['options'] as $option => $qty) {
        // 전날 데이터가 있는 경우 값을 차감
        $data[$date]['options'][$option] -= $previousData[$previousDate]['options'][$option] ?? 0;
    }

    // 홈페이지 및 큐패스 데이터 수정
    $data[$date]['homepage'] -= $previousData[$previousDate]['homepage'] ?? 0;
    $data[$date]['qpass'] -= $previousData[$previousDate]['qpass'] ?? 0;

    // 총합 데이터 수정
    $data[$date]['total'] -= $previousData[$previousDate]['total'] ?? 0;
}

// JSON 데이터 출력 (JavaScript에서 사용)
echo "<script>const processedData = " . json_encode($data) . ";</script>";

?>
<section class="container mt-3" style="max-width:1200px">
    <h2>날짜별 주문수량</h2>

<?php
// 데이터 가져오기
$query = "
    SELECT 
        DATE(date) as order_date, 
        category_name, 
        option_name,
        qty
    FROM product_quantities
    WHERE product_name = '어린이날'
    ORDER BY order_date, category_name, option_name;
";

$result = $conn->query($query);
if (!$result) {
    die("Query failed: " . $conn->error);
}

// 데이터 처리
$data = [];

while ($row = $result->fetch_assoc()) {
    $date = $row['order_date'];
    $category = $row['category_name'];
    $option = trim($row['option_name']);
    $qty = (int)$row['qty'];

    if (!isset($data[$date])) {
        $data[$date] = [
            'homepage' => ['R' => 0, 'S' => 0, 'A' => 0, '계' => 0],
            'qpass' => ['R' => 0, 'S' => 0, 'A' => 0, '계' => 0],
            '합계' => 0
        ];
    }

    if ($row['option_name'] == "[1부]R석 대인" || 
        $row['option_name'] == "[1부]R석 소인" ||
        $row['option_name'] == "[1부]R석 36개월미만" ||
        $row['option_name'] == "[1부]R석 36개월 미만" ||
        $row['option_name'] == "[2부]R석 대인" || 
        $row['option_name'] == "[2부]R석 소인" ||
        $row['option_name'] == "[2부]R석 36개월미만" ||
        $row['option_name'] == "[2부]R석 36개월 미만"
    ) $option = "R";

    if ($row['option_name'] == "[1부]S석 대인" || 
        $row['option_name'] == "[1부]S석 소인" ||
        $row['option_name'] == "[1부]S석 36개월미만" ||
        $row['option_name'] == "[1부]S석 36개월 미만" ||
        $row['option_name'] == "[2부]S석 대인" || 
        $row['option_name'] == "[2부]S석 소인" ||
        $row['option_name'] == "[2부]S석 36개월미만" ||
        $row['option_name'] == "[2부]S석 36개월 미만"
    ) $option = "S";

    if ($row['option_name'] == "[1부]A석 대인" || 
        $row['option_name'] == "[1부]A석 소인" ||
        $row['option_name'] == "[1부]A석 36개월미만" ||
        $row['option_name'] == "[1부]A석 36개월 미만" ||
        $row['option_name'] == "[2부]A석 대인" || 
        $row['option_name'] == "[2부]A석 소인" ||
        $row['option_name'] == "[2부]A석 36개월미만" ||
        $row['option_name'] == "[2부]A석 36개월 미만"
    ) $option = "A";

    // 옵션별 수량 저장
    if ($category === "홈페이지") {
        if (isset($data[$date]['homepage'][$option])) {
            $data[$date]['homepage'][$option] += $qty;
        }
        $data[$date]['homepage']['계'] += $qty;
    } elseif ($category === "큐패스") {
        if (isset($data[$date]['qpass'][$option])) {
            $data[$date]['qpass'][$option] += $qty;
        }
        $data[$date]['qpass']['계'] += $qty;
    }

    // 전체 합계
    $data[$date]['합계'] += $qty;

}
$prev_data = $data;
?>

<table class='table table-bordered table-hover text-center align-middle'>
    <colgroup>
        <col style="width:100px;">
        <col>
        <col>
        <col>
        <col>
        <col>
        <col>
        <col>
        <col>
        <col style="width:150px;">
    </colgroup>
    <thead class='table-primary'>
        <tr>
            <th class='align-middle' rowspan='2'>구분</th>
            <th class='align-middle' colspan='4'>홈페이지</th>
            <th class='align-middle' colspan='4'>큐패스</th>
            <th class='align-middle' rowspan='2'>합계</th>
        </tr>
        <tr>
            <th class='align-middle'>R</th>
            <th class='align-middle'>S</th>
            <th class='align-middle'>A</th>
            <th class='align-middle'>계</th>
            <th class='align-middle'>R</th>
            <th class='align-middle'>S</th>
            <th class='align-middle'>A</th>
            <th class='align-middle'>계</th>
        </tr>
    </thead>
<?php
$dates = array_keys($data); // 날짜 목록 가져오기

foreach ($dates as $index => $date) {
    $categories = $data[$date];

    // 전일자 가져오기 (첫 번째 날짜는 전일자가 없음)
    if ($index === 0) {
        $web_r = $categories['homepage']['R'];
        $web_s = $categories['homepage']['S'];
        $web_a = $categories['homepage']['A'];
        $web_tot = $categories['homepage']['계'];
        $q_r = $categories['qpass']['R'];
        $q_s = $categories['qpass']['S'];
        $q_a = $categories['qpass']['A'];
        $q_tot = $categories['qpass']['계'];
        $total = $categories['합계'];
    }

    if ($index != 0) {
        $previousDate = $dates[$index - 1]; // 전일 날짜
        $previousCategories = $data[$previousDate]; // 전일 데이터 가져오기

        // 현재 날짜의 값에서 전일자 값 빼기
        $web_r = $categories['homepage']['R'] - ($previousCategories['homepage']['R'] ?? 0);
        $web_s = $categories['homepage']['S'] - ($previousCategories['homepage']['S'] ?? 0);
        $web_a = $categories['homepage']['A'] - ($previousCategories['homepage']['A'] ?? 0);
        $web_tot = $categories['homepage']['계'] - ($previousCategories['homepage']['계'] ?? 0);
        $q_r = $categories['qpass']['R'] - ($previousCategories['qpass']['R'] ?? 0);
        $q_s = $categories['qpass']['S'] - ($previousCategories['qpass']['S'] ?? 0);
        $q_a = $categories['qpass']['A'] - ($previousCategories['qpass']['A'] ?? 0);
        $q_tot = $categories['qpass']['계'] - ($previousCategories['qpass']['계'] ?? 0);
        $total = $categories['합계'] - ($previousCategories['합계'] ?? 0);
    }

?>
    <tr>
        <th class="align-middle"><?=date('m/d', strtotime($date))?></th>
        <td class="align-middle"><?= $web_r === 0 ? '-' : $web_r ?></td>
        <td class="align-middle"><?= $web_s === 0 ? '-' : $web_s ?></td>
        <td class="align-middle"><?= $web_a === 0 ? '-' : $web_a ?></td>
        <td class="align-middle" style="font-weight: 600; "><?= $web_tot === 0 ? '-' : $web_tot ?></td>
        <td class="align-middle"><?= $q_r === 0 ? '-' : $q_r ?></td>
        <td class="align-middle"><?= $q_s === 0 ? '-' : $q_s ?></td>
        <td class="align-middle"><?= $q_a === 0 ? '-' : $q_a ?></td>
        <td class="align-middle" style="font-weight: 600;"><?= $q_tot === 0 ? '-' : $q_tot ?></td>
        <td class="align-middle" style="font-weight: 600;"><?= $total === 0 ? '-' : $total ?></td>
    </tr>
<?php } ?>
    </table>
</section>
        
    <section class="container mt-3" style="max-width:1200px">
        <h2>Chart</h2>
            <div class="chart-wrap">
                <canvas class="chart" id="chart1" width="1000px"></canvas>
                <canvas class="chart" id="chart2" width="1000px"></canvas>
                <canvas class="chart" id="chart3" width="1000px"></canvas>
            </div>
        </section>

        <script>
    // 1. 홈페이지/큐패스 날짜별 주문수량
    const homepageData = [];
    const qpassData = [];
    const labels = [];
    const totalData = [];
    const optionsData = {}; // 옵션별 데이터

    for (const [date, details] of Object.entries(processedData)) {
        labels.push(date);
        homepageData.push(details.homepage);
        qpassData.push(details.qpass);
        totalData.push(details.total);

        for (const [option, qty] of Object.entries(details.options)) {
            if (!optionsData[option]) {
                optionsData[option] = [];
            }
            optionsData[option].push(qty);
        }
    }

    const ctx1 = document.getElementById('chart1').getContext('2d');
    new Chart(ctx1, {
        type: 'bar',
        data: {
            labels: labels,
            datasets: [
                { label: '홈페이지', data: homepageData, backgroundColor: '#4CAF50' },
                { label: '큐패스', data: qpassData, backgroundColor: '#FF9800' }
            ]
        },
        options: {
            responsive: true,
            plugins: {
                title: {
                    display: true,
                    text: '홈페이지 및 큐패스 날짜별 주문수량'
                }
            }
        }
    });

    // 2. 날짜별 총 주문수량
    const ctx2 = document.getElementById('chart2').getContext('2d');
    new Chart(ctx2, {
        type: 'bar',
        data: {
            labels: labels,
            datasets: [{ label: '총 주문수량', data: totalData, backgroundColor: '#2196F3' }]
        },
        options: {
            responsive: true,
            plugins: {
                title: {
                    display: true,
                    text: '날짜별 총 주문수량'
                }
            }
        }
    });

    // 3. 날짜-옵션별 총 주문수량
    const datasets = Object.keys(optionsData).map(option => ({
        label: option,
        data: optionsData[option],
        backgroundColor: getRandomColor()
    }));

    const ctx3 = document.getElementById('chart3').getContext('2d');
    new Chart(ctx3, {
        type: 'bar',
        data: {
            labels: labels,
            datasets: datasets
        },
        options: {
            responsive: true,
            plugins: {
                title: {
                    display: true,
                    text: '날짜-옵션별 총 주문수량'
                }
            }
        }
    });

    // 유틸리티 함수: 랜덤 색상 생성
    function getRandomColor() {
        return `#${Math.floor(Math.random() * 16777215).toString(16)}`;
    }
</script>

</body>
</html>
<?php $conn->close();