PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
// ---------- 対象月設定 ----------
$targetMonth = isset($_GET['month'])
? $_GET['month']
: date('Y-m', strtotime('first day of last month'));
// 日付計算
$monthCurrent = $targetMonth;
$monthPrev = date('Y-m', strtotime($targetMonth . ' -1 month'));
$monthNext = date('Y-m', strtotime($targetMonth . ' +1 month'));
$monthPrev2 = date('Y-m', strtotime($monthCurrent.' -2 month'));
// ラベル
$labelThisMonth = date('Y年n月', strtotime($targetMonth . '-01'));
$labelPrevMonth = date('Y年n月', strtotime($targetMonth . ' -1 month'));
$labelNextMonth = date('Y年n月', strtotime($targetMonth . ' +1 month'));
$dateObj = DateTime::createFromFormat('Y-m-d', $targetMonth . '-01');
$targetYear = $dateObj->format('Y');
$targetMonthNum = $dateObj->format('n');
// 企業リスト
$displayCompanies = [
'株式会社フレスコ', '三菱地所ホーム', 'OPEC', 'GENERAL', '太平ハウス・ラボ',
'ジャーブネット', 'あさひハウジングセンター', '住友不動産ハウジング', 'イエタス',
'ワンズ・サポート・システム', 'ITC', '旭化成建材', 'WIT', '福工房',
'株式会社高砂建設', '京阪電鉄不動産', 'よかタウン', '篠原商店'
];
$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 = [];
$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. 追加項目取得
// サーバ空容量
$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']; }
// レスポンス速度
$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期限
$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'];
}
}
// ウイルス・IPS
$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']; }
}
// 作業ログ
$stmt = $pdo->prepare("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->execute([':month' => $monthCurrent . '%']);
$workHistory = $stmt->fetchAll();
$stmt = $pdo->prepare("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->execute([':month' => $monthNext . '%']);
$workPlan = $stmt->fetchAll();
// 2. ディスク使用量
$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']])) {
$loginMap[$companyMap[$r['company_id']]] = [
'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
$stmt = $pdo->prepare("SELECT category,label,active_users FROM ga4_monthly_report WHERE report_month=:month ORDER BY category,active_users DESC");
$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());
}
?>
<?= htmlspecialchars($labelThisMonth) ?> 月報
月報
ディスク使用量
| 企業名 |
|
|
合計 |
| 利用量(GB) | ファイル数 |
利用量(GB) | ファイル数 |
利用量(GB) | ファイル数 |
0, 'cur_files'=>0, 'prev_size'=>0, 'prev_files'=>0, 'total_size'=>0, 'total_files'=>0
];
?>
|
|
|
|
|
|
|
作業報告
① 作業実績
| 作業日 |
対象サーバ |
作業内容 |
0): ?>
|
|
|
| 実績なし |
② 作業予定
| 予定日 |
対象サーバ |
作業内容 |
0): ?>
|
|
|
| 予定なし |