<?php
// --- DB接続設定 ---
$dsn = 'mysql:dbname=databank;host=localhost;charset=utf8mb4';
$user = 'root';
$password = 'Vrock666$$$';

// エラーハンドリング設定
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];

// ---------- 対象月設定 ----------
// デフォルト：先月（例: 実行日が2月なら、対象月は1月）
$targetMonth = isset($_GET['month'])
    ? $_GET['month']
    : date('Y-m', strtotime('first day of last month'));

// 比較・表示用日付計算
// 1. レポート対象月 (例: 2025-12) -> 実績を表示
$monthCurrent = $targetMonth;
// 2. その前月 (例: 2025-11) -> 比較用
$monthPrev = date('Y-m', strtotime($targetMonth . ' -1 month'));
// 3. その翌月 (例: 2026-01) -> 予定を表示
$monthNext = date('Y-m', strtotime($targetMonth . ' +1 month'));

// 表示用ラベル
$labelThisMonth = date('Y年n月', strtotime($targetMonth . '-01'));
$labelPrevMonth = date('Y年n月', strtotime($targetMonth . ' -1 month'));
$labelNextMonth = date('Y年n月', strtotime($targetMonth . ' +1 month'));

// SQL検索用に年と月を分割（追加項目用）
$dateObj = DateTime::createFromFormat('Y-m-d', $targetMonth . '-01');
$targetYear = $dateObj->format('Y'); 
$targetMonthNum = $dateObj->format('n');

// ---------- 表示対象企業（順番固定） ----------
$displayCompanies = [
    '株式会社フレスコ',
    '三菱地所ホーム',
    'OPEC',
    'GENERAL',
    '太平ハウス・ラボ',
    'ジャーブネット',
    'あさひハウジングセンター',
    '住友不動産ハウジング',
    'イエタス',
    'ワンズ・サポート・システム',
    'ITC',
    '旭化成建材',
    'WIT',
    '福工房',
    '株式会社高砂建設',
    '京阪電鉄不動産',
    'よかタウン',
    '篠原商店'
];

// ---------- 企業ID → 企業名（ログイン用） ----------
$companyMap = [
    'Fresco'=>'株式会社フレスコ',
    'MJHOME'=>'三菱地所ホーム',
    'OPEC'=>'OPEC',
    'GENERAL'=>'GENERAL',
    'houselabo'=>'太平ハウス・ラボ',
    'JAHBnet'=>'ジャーブネット',
    'AHC'=>'あさひハウジングセンター',
    'Sumitomo-rd'=>'住友不動産ハウジング',
    'YETUS'=>'イエタス',
    'ONESNET'=>'ワンズ・サポート・システム',
    'ITC'=>'ITC',
    'AKK'=>'旭化成建材',
    'WI-TOKYO'=>'WIT',
    'fukukobo'=>'福工房',
    'takasago'=>'株式会社高砂建設',
    'KEIHAN'=>'京阪電鉄不動産',
    'yoka-town'=>'よかタウン',
    'SHINOHARA'=>'篠原商店'
];

// ---------- 初期化 ----------
// 追加項目のデフォルト値
$serverDisk = '取得なし';
$responseSpeed = '取得なし';
$sslDate = '取得なし';
$virusCount = 0;
$ipsCount = 0;

// 作業ログ用配列
$workHistory = []; // 実績
$workPlan = [];    // 予定

// GAチャート用
$chartData = [
    'Resolution'=>['labels'=>[], 'data'=>[]],
    'Browser'=>['labels'=>[], 'data'=>[]],
    'OS'=>['labels'=>[], 'data'=>[]],
];
$limits = ['Resolution'=>8,'Browser'=>5,'OS'=>5];


try {
    $pdo = new PDO($dsn, $user, $password, $options);

    // ==========================================
    // 1. 追加項目の取得 (サーバ・セキュリティ等)
    // ==========================================

    // ① サーバ空容量 (server_disk)
    $stmt = $pdo->prepare("SELECT size FROM server_disk WHERE server = 'dbs' AND year = :y AND month = :m LIMIT 1");
    $stmt->execute([':y' => $targetYear, ':m' => $targetMonthNum]);
    $row = $stmt->fetch();
    if ($row) { $serverDisk = $row['size']; }

    // ② レスポンス速度 (response_tbl)
    $stmt = $pdo->prepare("SELECT response FROM response_tbl WHERE service = 'databank' AND year = :y AND month = :m LIMIT 1");
    $stmt->execute([':y' => $targetYear, ':m' => $targetMonthNum]);
    $row = $stmt->fetch();
    if ($row) { $responseSpeed = $row['response']; }

    // ③ SSL証明書期限 (ssl_tbl)
    $stmt = $pdo->prepare("SELECT ssl_date FROM ssl_tbl WHERE service = 'www.databank-solution.net' LIMIT 1");
    $stmt->execute();
    $row = $stmt->fetch();
    if ($row && !empty($row['ssl_date'])) {
        $dateStr = str_replace('/', '-', $row['ssl_date']);
        $timestamp = strtotime($dateStr);
        if ($timestamp) {
            $sslDate = date('Y年m月d日', $timestamp);
        } else {
            $sslDate = $row['ssl_date'];
        }
    }

    // ④ ウイルス・不正侵入検知 (alert_tbl)
    $stmt = $pdo->prepare("SELECT kind, alert_count FROM alert_tbl WHERE year = :y AND month = :m AND kind IN ('virus', 'ips')");
    $stmt->execute([':y' => $targetYear, ':m' => $targetMonthNum]);
    $alertRows = $stmt->fetchAll();
    foreach ($alertRows as $r) {
        if ($r['kind'] === 'virus') { $virusCount = $r['alert_count']; }
        elseif ($r['kind'] === 'ips') { $ipsCount = $r['alert_count']; }
    }

    // ==========================================
    // ★追加: 作業ログの取得
    // ==========================================
    
    // ① 作業実績（対象月 & status=done）
    $sqlWorkHistory = "
        SELECT work_date, server_name, work_content 
        FROM server_work_log 
        WHERE work_date LIKE :month 
          AND status = 'done' 
        ORDER BY work_date ASC
    ";
    $stmt = $pdo->prepare($sqlWorkHistory);
    $stmt->execute([':month' => $monthCurrent . '%']);
    $workHistory = $stmt->fetchAll();

    // ② 作業予定（翌月 & status=plan）
    $sqlWorkPlan = "
        SELECT work_date, server_name, work_content 
        FROM server_work_log 
        WHERE work_date LIKE :month 
          AND status = 'plan' 
        ORDER BY work_date ASC
    ";
    $stmt = $pdo->prepare($sqlWorkPlan);
    $stmt->execute([':month' => $monthNext . '%']);
    $workPlan = $stmt->fetchAll();


    // ==========================================
    // 2. ディスク使用量
    // ==========================================
    $monthPrev2 = date('Y-m', strtotime($monthCurrent.' -2 month'));

    $sqlDisk = "
    SELECT
        cur.company_name,
        (cur.total_size_gb - IFNULL(prev.total_size_gb,0)) AS cur_size,
        (cur.file_count - IFNULL(prev.file_count,0)) AS cur_files,
        (prev.total_size_gb - IFNULL(prev2.total_size_gb,0)) AS prev_size,
        (prev.file_count - IFNULL(prev2.file_count,0)) AS prev_files,
        cur.total_size_gb AS total_size,
        cur.file_count AS total_files
    FROM company_file_usage cur
    LEFT JOIN company_file_usage prev
      ON cur.company_id = prev.company_id AND prev.target_month = :m1
    LEFT JOIN company_file_usage prev2
      ON cur.company_id = prev2.company_id AND prev2.target_month = :m0
    WHERE cur.target_month = :m2
    ";
    $stmt = $pdo->prepare($sqlDisk);
    $stmt->execute([':m0'=>$monthPrev2,':m1'=>$monthPrev,':m2'=>$monthCurrent]);
    $diskRows = $stmt->fetchAll();

    $diskMap = [];
    foreach ($diskRows as $r) {
        $diskMap[$r['company_name']] = $r;
    }


    // ==========================================
    // 3. ログイン回数
    // ==========================================
    $sqlLogin = "
    SELECT
        company_id,
        SUM(CASE WHEN target_month=:m1 THEN login_ok_cnt ELSE 0 END) AS cur_ok,
        SUM(CASE WHEN target_month=:m1 THEN login_ng_cnt ELSE 0 END) AS cur_ng,
        SUM(CASE WHEN target_month=:m2 THEN login_ok_cnt ELSE 0 END) AS prev_ok,
        SUM(CASE WHEN target_month=:m2 THEN login_ng_cnt ELSE 0 END) AS prev_ng
    FROM company_login_stats
    WHERE target_month IN (:m1,:m2)
    GROUP BY company_id
    ";
    $stmt = $pdo->prepare($sqlLogin);
    $stmt->execute([':m1'=>$targetMonth,':m2'=>$monthPrev]);
    $loginRows = $stmt->fetchAll();

    $loginMap = [];
    foreach ($displayCompanies as $companyName) {
        $loginMap[$companyName] = ['cur_ok'=>0, 'cur_ng'=>0, 'prev_ok'=>0, 'prev_ng'=>0];
    }
    foreach ($loginRows as $r) {
        if (isset($companyMap[$r['company_id']])) {
            $cname = $companyMap[$r['company_id']];
            $loginMap[$cname] = [
                'cur_ok'  => (int)$r['cur_ok'],
                'cur_ng'  => (int)$r['cur_ng'],
                'prev_ok' => (int)$r['prev_ok'],
                'prev_ng' => (int)$r['prev_ng'],
            ];
        }
    }


    // ==========================================
    // 4. GA (Google Analytics)
    // ==========================================
    $sqlGA = "
    SELECT category,label,active_users
    FROM ga4_monthly_report
    WHERE report_month=:month
    ORDER BY category,active_users DESC
    ";
    $stmt = $pdo->prepare($sqlGA);
    $stmt->execute([':month'=>$targetMonth]);
    $gaRows = $stmt->fetchAll();

    foreach ($gaRows as $r) {
        $chartData[$r['category']]['labels'][] = $r['label'];
        $chartData[$r['category']]['data'][]   = $r['active_users'];
    }
    foreach ($limits as $k=>$v) {
        if (isset($chartData[$k])) {
            $chartData[$k]['labels'] = array_slice($chartData[$k]['labels'],0,$v);
            $chartData[$k]['data']   = array_slice($chartData[$k]['data'],0,$v);
        }
    }

} catch (PDOException $e) {
    exit('DB Error: '.$e->getMessage());
}
?>
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title><?= htmlspecialchars($labelThisMonth) ?> 月報</title>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chartjs-plugin-datalabels@2"></script>
<style>
body{background:#f3f4f6; margin:0; padding:40px 0; font-family:"Helvetica Neue",Arial,sans-serif; color:#333;}
.page{width:1000px; margin:0 auto; background:#fff; padding:40px 50px; box-shadow:0 4px 15px rgba(0,0,0,0.05);}

/* 見出し */
h1{font-size:26px; color:#1a73e8; border-bottom:2px solid #e5e7eb; padding-bottom:10px; margin-bottom:30px;}
h2{margin-top:50px; margin-bottom:20px; font-size:18px; color:#333; border-left:5px solid #1a73e8; padding-left:12px;}
h3{font-size:15px; margin-top:0; margin-bottom:10px; color:#555;}

/* ステータスカード */
.status-container { display:flex; gap:20px; flex-wrap:wrap; margin-bottom:40px; }
.status-card {
    flex:1; min-width:200px;
    background:#fff; border:1px solid #e5e7eb; border-left:5px solid #1a73e8;
    padding:15px 20px; border-radius:6px;
    box-shadow:0 1px 3px rgba(0,0,0,0.05);
}
.status-card h3 { font-size:13px; color:#666; margin:0 0 8px 0; font-weight:normal; }
.status-card .value { font-size:22px; font-weight:bold; color:#333; }
.status-card .unit { font-size:13px; font-weight:normal; color:#888; margin-left:4px; }
.card-alert { border-left-color:#e53935; }
.card-security { border-left-color:#fbbc04; }

/* テーブル共通 */
table{width:100%; border-collapse:collapse; font-size:14px; margin-bottom:20px;}
th,td{border:1px solid #d1d5db; padding:10px;}
th{background:#f9fafb; text-align:center; font-weight:bold;}
td{text-align:right;}
/* 1列目（企業名やNo）は左寄せ */
td:first-child{text-align:left;}
tr:nth-child(even) td{background:#fafafa;}

/* 作業ログ用テーブルのスタイル上書き */
.work-table th { background:#e8f0fe; color:#1a73e8; } 
.work-table td { text-align: left; } /* 作業内容は左寄せ */
.work-table td.date-col { text-align: center; width: 120px; }
.work-table td.server-col { width: 150px; font-weight:bold; }

/* GAグラフコンテナ */
.report-container{display:flex; gap:20px; height:400px;}
.chart-box{
    flex:1; min-width:0; 
    background:#fff; padding:15px; border-radius:8px; border:1px solid #e5e7eb;
    display:flex; flex-direction:column;
}
.canvas-wrapper{ position:relative; width:100%; height:100%; }

@media print{
 body{background:#fff; padding:0;}
 .page{width:100%; padding:0; box-shadow:none;}
 table{page-break-inside:avoid;}
}
</style>
</head>

<body>
<div class="page">
    <h1><?= htmlspecialchars($labelThisMonth) ?> 月報</h1>

    <h2>ディスク使用量</h2>
    <table>
        <tr>
            <th rowspan="2">企業名</th>
            <th colspan="2"><?= $labelThisMonth ?></th>
            <th colspan="2"><?= $labelPrevMonth ?></th>
            <th colspan="2">合計</th>
        </tr>
        <tr>
            <th>利用量(GB)</th><th>ファイル数</th>
            <th>利用量(GB)</th><th>ファイル数</th>
            <th>利用量(GB)</th><th>ファイル数</th>
        </tr>
        <?php foreach ($displayCompanies as $c):
            $r = isset($diskMap[$c]) ? $diskMap[$c] : [
                'cur_size'=>0, 'cur_files'=>0, 'prev_size'=>0, 'prev_files'=>0, 'total_size'=>0, 'total_files'=>0
            ];
        ?>
        <tr>
            <td><?= htmlspecialchars($c) ?></td>
            <td><?= number_format($r['cur_size'],2) ?></td>
            <td><?= number_format($r['cur_files']) ?></td>
            <td><?= number_format($r['prev_size'],2) ?></td>
            <td><?= number_format($r['prev_files']) ?></td>
            <td><?= number_format($r['total_size'],2) ?></td>
            <td><?= number_format($r['total_files']) ?></td>
        </tr>
        <?php endforeach; ?>
    </table>

    <h2>ログイン回数</h2>
    <table>
        <tr>
            <th rowspan="2">企業名</th>
            <th colspan="2"><?= $labelThisMonth ?></th>
            <th colspan="2"><?= $labelPrevMonth ?></th>
        </tr>
        <tr><th>成功</th><th>失敗</th><th>成功</th><th>失敗</th></tr>
        <?php foreach ($displayCompanies as $c):
            if (!isset($loginMap[$c])) continue;
            $r = $loginMap[$c]; ?>
        <tr>
            <td><?= htmlspecialchars($c) ?></td>
            <td><?= number_format($r['cur_ok']) ?></td>
            <td><?= number_format($r['cur_ng']) ?></td>
            <td><?= number_format($r['prev_ok']) ?></td>
            <td><?= number_format($r['prev_ng']) ?></td>
        </tr>
        <?php endforeach; ?>
    </table>

    <h2>ユーザ情報</h2>
    <div class="report-container">
        <div class="chart-box">
            <h3>解像度 (Top8)</h3>
            <div class="canvas-wrapper"><canvas id="chartResolution"></canvas></div>
        </div>
        <div class="chart-box">
            <h3>ブラウザ (Top5)</h3>
            <div class="canvas-wrapper"><canvas id="chartBrowser"></canvas></div>
        </div>
        <div class="chart-box">
            <h3>OS (Top5)</h3>
            <div class="canvas-wrapper"><canvas id="chartOS"></canvas></div>
        </div>
    </div>

    <h2>サーバ情報</h2>
    <div class="status-container">
        <div class="status-card">
            <h3>サーバ空容量 (dbs)</h3>
            <div class="value"><?= htmlspecialchars($serverDisk) ?></div>
        </div>

        <div class="status-card">
            <h3>レスポンス速度 (databank)</h3>
            <div class="value"><?= htmlspecialchars($responseSpeed) ?></div>
        </div>

        <div class="status-card card-security">
            <h3>SSL証明書期限</h3>
            <div class="value" style="font-size:16px; line-height:1.5;"><?= htmlspecialchars($sslDate) ?></div>
        </div>

        <div class="status-card card-alert">
            <h3>ウイルス検知</h3>
            <div class="value">
                <?= htmlspecialchars($virusCount) ?><span class="unit">件</span> 
            </div>
        </div>

        <div class="status-card card-alert">
            <h3>不正侵入検知</h3>
            <div class="value">
                <?= htmlspecialchars($ipsCount) ?><span class="unit">件</span>
            </div>
        </div>


    </div>

    <h2>作業報告</h2>
    
    <h3>① <?= htmlspecialchars($labelThisMonth) ?> 作業実績</h3>
    <table class="work-table">
        <tr>
            <th>作業日</th>
            <th>対象サーバ</th>
            <th>作業内容</th>
        </tr>
        <?php if (count($workHistory) > 0): ?>
            <?php foreach ($workHistory as $work): ?>
            <tr>
                <td class="date-col"><?= htmlspecialchars($work['work_date']) ?></td>
                <td class="server-col"><?= htmlspecialchars($work['server_name']) ?></td>
                <td><?= nl2br(htmlspecialchars($work['work_content'])) ?></td>
            </tr>
            <?php endforeach; ?>
        <?php else: ?>
            <tr><td colspan="3" style="text-align:center;">実績なし</td></tr>
        <?php endif; ?>
    </table>

    <h3 style="margin-top:30px;">② <?= htmlspecialchars($labelNextMonth) ?> 作業予定</h3>
    <table class="work-table">
        <tr>
            <th>予定日</th>
            <th>対象サーバ</th>
            <th>作業内容</th>
        </tr>
        <?php if (count($workPlan) > 0): ?>
            <?php foreach ($workPlan as $plan): ?>
            <tr>
                <td class="date-col"><?= htmlspecialchars($plan['work_date']) ?></td>
                <td class="server-col"><?= htmlspecialchars($plan['server_name']) ?></td>
                <td><?= nl2br(htmlspecialchars($plan['work_content'])) ?></td>
            </tr>
            <?php endforeach; ?>
        <?php else: ?>
            <tr><td colspan="3" style="text-align:center;">予定なし</td></tr>
        <?php endif; ?>
    </table>

</div>

<script>
Chart.register(ChartDataLabels);
const rawData = <?= json_encode($chartData) ?>;

// グラフ描画共通関数
function draw(id, labels, data){
    if (!labels || labels.length === 0) {
        document.getElementById(id).parentNode.innerHTML = "<p style='text-align:center;margin-top:50px;'>データなし</p>";
        return;
    }

    new Chart(document.getElementById(id), {
        type: 'bar',
        data: {
            labels: labels,
            datasets: [{
                data: data,
                backgroundColor: '#4285f4',
                barThickness: 15,
                borderRadius: 4
            }]
        },
        options: {
            indexAxis: 'y',
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: { display: false },
                datalabels: {
                    anchor: 'end',
                    align: 'end',
                    offset: 4,
                    color: '#333',
                    font: { weight: 'bold', size: 11 },
                    formatter: function(val){ return val + '人'; }
                }
            },
            layout: {
                padding: { right: 50 } 
            },
            scales: {
                x: { beginAtZero: true, grid: { borderDash:[2,2] } },
                y: { grid: { display:false } }
            }
        }
    });
}

draw('chartResolution', rawData.Resolution.labels, rawData.Resolution.data);
draw('chartBrowser', rawData.Browser.labels, rawData.Browser.data);
draw('chartOS', rawData.OS.labels, rawData.OS.data);
</script>
</body>
</html>