SCRUM-68 圖表數據抽取實作分析

JIRA: SCRUM-68 Parent: SCRUM-14 (後台－數值管理頁、操作紀錄) 難度: ⭐⭐⭐ (中等) 預估工作量: 2-3 天角色: 純後端 API + 前端 API 層串接

定位與依賴關係

SCRUM-68 是 SCRUM-67（核心數據抽取）到 SCRUM-43（後台數據監控 UI）的橋樑。

SCRUM-67 提供單一時間點的聚合指標（10 項），SCRUM-68 將這些指標轉化為 按日期分組的時間序列數據，供前端圖表渲染。

SCRUM-67 (核心數據抽取) ─── 10 項平台指標 ✅ 已實作
    │
    ▼
SCRUM-68 (圖表數據抽取) ─── 將指標轉為時間序列 ← 本任務
    │
    ▼
SCRUM-43 (後台數據監控) ─── 前端整合顯示

阻塞關係: SCRUM-68 完成後，SCRUM-43 的圖表區域才能開始整合。

功能需求

4 種圖表類型

圖表	chart_type	Y 軸	資料來源表	SQL 邏輯
活躍人數表	`active_users`	人數	`logs_user_logins`	`COUNT(DISTINCT user_email)` per day
做題總數表	`total_answers`	題數	`answer`	`COUNT(*)` per day (累計)
做題數表	`answer_count`	題數	`answer`	`COUNT(*)` per day (當日)
未批改總數表	`unreviewed`	未批改數	`answer`	`COUNT(*)` WHERE `is_review=0` per day

時間篩選

沿用 SCRUM-67 已實作的 5 種時間範圍：

all - 不限時間
day - 今日
month - 本月
year - 本年
custom - 自訂起迄日期

現有後端分析

✅ 已存在：核心統計 API (SCRUM-67)

端點: POST /admin/statistics/getStatistics 檔案: app/Controllers/Admin/StatisticsController.php 模型: app/Models/Admin/StatisticsModel.php (361 行)

已實作全部 10 項指標 + 5 種時間範圍過濾，但 只回傳單一聚合值，不含時間序列。

❌ 待建立：圖表數據 API

目前完全沒有 time-series 相關邏輯，需從零建立。

API 規格設計

Request

POST /admin/statistics/getChartData
Authority: permission:statistics,view

{
  "chart_type": "active_users",
  "time_range": "month",
  "start_date": "2026-01-06",
  "end_date": "2026-02-06"
}

參數	類型	必填	說明
`chart_type`	string	✅	`active_users` / `total_answers` / `answer_count` / `unreviewed`
`time_range`	string	✅	`day` / `month` / `year` / `all` / `custom`
`start_date`	string	`custom` 時必填	`YYYY-MM-DD`
`end_date`	string	`custom` 時必填	`YYYY-MM-DD`

Response

{
  "code": 200,
  "data": {
    "chart_type": "active_users",
    "labels": ["2026-01-06", "2026-01-07", "2026-01-08"],
    "values": [45, 48, 52],
    "date_range": {
      "start": "2026-01-06",
      "end": "2026-02-06"
    },
    "total_days": 32
  }
}

SQL 查詢模式

活躍人數 (active_users)

SELECT DATE(created_at) as date, COUNT(DISTINCT user_email) as count
FROM logs_user_logins
WHERE created_at BETWEEN ? AND ?
GROUP BY DATE(created_at)
ORDER BY date ASC;

做題數 (answer_count / total_answers)

SELECT DATE(created_at) as date, COUNT(*) as count
FROM answer
WHERE status = 1
  AND created_at BETWEEN ? AND ?
GROUP BY DATE(created_at)
ORDER BY date ASC;

total_answers 與 answer_count 查詢相同，差異在後處理：total_answers 需做累計加總。

未批改數 (unreviewed)

SELECT DATE(created_at) as date, COUNT(*) as count
FROM answer
WHERE status = 1
  AND is_review = 0
  AND created_at BETWEEN ? AND ?
GROUP BY DATE(created_at)
ORDER BY date ASC;

日期填充 (PHP 端處理)

// 產生完整日期序列
$period = new DatePeriod($startDate, new DateInterval('P1D'), $endDate);
$dateMap = array_fill_keys(
    array_map(fn($d) => $d->format('Y-m-d'), iterator_to_array($period)),
    0
);
 
// 合併查詢結果
foreach ($queryResults as $row) {
    $dateMap[$row['date']] = (int) $row['count'];
}
 
// 輸出
$labels = array_keys($dateMap);
$values = array_values($dateMap);

實作 Checklist

Phase 1：後端 API

路由與控制器

新增路由
- 檔案: app/Config/Routes.php
- 加入: $routes->post('statistics/getChartData', 'StatisticsController::getChartData', ['filter' => 'permission:statistics,view'])
新增 Controller 方法
- 檔案: app/Controllers/Admin/StatisticsController.php
- 方法: getChartData()
- 驗證 chart_type 參數 (只允許 4 種)
- 解析時間範圍 (沿用現有 time_range 邏輯)
- 呼叫 Model 方法取得數據
- 回傳統一格式 { code, data: { chart_type, labels, values, date_range, total_days } }

Model 核心邏輯

新增 getTimeSeriesData() 方法
- 檔案: app/Models/Admin/StatisticsModel.php
- 簽名: getTimeSeriesData(string $chartType, ?string $startDate, ?string $endDate): array
- 根據 $chartType 分派到對應的 SQL 查詢
實作 active_users 查詢
- 從 logs_user_logins 表按日 COUNT(DISTINCT user_email)
實作 answer_count 查詢
- 從 answer 表按日 COUNT(*) WHERE status=1
實作 total_answers 查詢
- 同 answer_count 但在 PHP 端做累計加總 (running sum)
實作 unreviewed 查詢
- 從 answer 表按日 COUNT(*) WHERE status=1 AND is_review=0
實作日期填充邏輯
- 產生起迄日期間的完整日期序列
- 無資料日期填 0
- 獨立為 fillDateGaps() 私有方法，4 種圖表共用
資料點上限控制
- 超過 365 天時改為按月聚合 (DATE_FORMAT(created_at, '%Y-%m'))
- 或限制最大 365 個資料點

資料庫索引

確認索引存在（若不存在則新增 migration）
- idx_answer_created_review on answer(created_at, is_review)
- idx_answer_created_status on answer(created_at, status)
- idx_logs_user_logins_created on logs_user_logins(created_at)

錯誤處理

無效 chart_type 回傳 400
custom 缺少日期回傳 400
空結果回傳空陣列（labels: [], values: []，不要 500）
start_date > end_date 回傳 400 或自動交換

Phase 2：前端 API 串接層

Server API Handler

建立 Server API 端點
- 檔案: app/server/api/statistics/chart.post.ts（writeahead-admin-web）
- 轉發至後端 /admin/statistics/getChartData
- 命名慣例轉換: snake_case → camelCase

類型定義

建立統計類型定義

檔案: app/types/statistics.ts（writeahead-admin-web）

type ChartType = 'active_users' | 'total_answers' | 'answer_count' | 'unreviewed'
type TimeRange = 'day' | 'month' | 'year' | 'all' | 'custom'
 
interface ChartRequest {
  chartType: ChartType
  timeRange: TimeRange
  startDate?: string
  endDate?: string
}
 
interface ChartResponse {
  chartType: ChartType
  labels: string[]
  values: number[]
  dateRange: { start: string; end: string }
  totalDays: number
}

Composable

建立 API 呼叫封裝
- 檔案: app/composables/useStatisticsApi.ts（writeahead-admin-web）
- 方法: fetchChartData(request: ChartRequest): Promise<ChartResponse>
- 錯誤處理與 loading 狀態

Phase 3：測試與驗證

後端測試

4 種 chart_type 各回傳正確格式
labels 與 values 陣列長度一致
無資料日期正確填 0
total_answers 累計加總正確（遞增序列）
time_range=all 不會因資料量過大而超時
無效參數回傳 400
空結果回傳空陣列而非錯誤

前端測試

Server API 正確轉發與轉換命名
Composable 正確處理成功/失敗回應
TypeScript 型別無報錯

效能測試

月範圍查詢 < 500ms
年範圍查詢 < 2s
全部範圍查詢 < 5s（若超過需加快取）

技術決策

日期填充策略

方案	優點	缺點	選擇
PHP 端填充	簡單可靠、不依賴 DB 特性	多一次迭代	✅ 推薦
SQL 日曆表 JOIN	純 SQL 完成	需維護日曆表	❌
前端填充	後端簡單	增加前端複雜度	❌

大範圍聚合策略

範圍	聚合粒度	最大資料點
≤ 31 天	按日	31
32-365 天	按日	365
> 365 天	按月	~36

累計 vs 當日

answer_count: 當日新增數（直接使用 GROUP BY DATE 結果）
total_answers: 累計總數（PHP 端做 running sum: $values[$i] += $values[$i-1]）

關鍵檔案路徑

後端 (writeahead-main-api)

/home/matt/Github/writeahead-main-api/
├── app/Config/Routes.php                              # 新增路由
├── app/Controllers/Admin/StatisticsController.php     # 新增 getChartData()
└── app/Models/Admin/StatisticsModel.php               # 新增 getTimeSeriesData()

前端 (writeahead-admin-web)

/home/matt/Github/writeahead-admin-web/
├── server/api/statistics/chart.post.ts                # 新建 Server API
├── app/composables/useStatisticsApi.ts                # 新建 Composable
└── app/types/statistics.ts                            # 新建型別定義

風險與注意事項

風險	影響	緩解措施
大時間範圍查詢慢	API 超時	加索引、超過 365 天改月聚合、Redis 快取 (TTL 5-15 min)
日期空洞	圖表不連續	PHP 端日期填充
DB 連線開銷	效能瓶頸	單一連線複用 (現有 SCRUM-67 每個指標各連一次)
時區不一致	數據偏移	統一使用 server 時區，文件說明
JSON 回應過大	網路/渲染延遲	限制最大資料點 365
`total_answers` 累計溢位	數值錯誤	PHP int 上限夠用，前端用 `number`

與 SCRUM-43 的整合點

SCRUM-68 完成後，SCRUM-43 前端圖表區域需要：

圖表選擇器 (chart-selector.vue) 呼叫 useStatisticsApi.fetchChartData()
切換圖表類型時，傳入不同 chart_type 重新請求
時間篩選變更時，核心指標 + 圖表數據同時刷新
ECharts 組件接收 { labels, values } 渲染折線圖

更新紀錄

日期	更新內容
2026-02-06	深度分析，建立完整 checklist

WriteAhead Sprint 4

探索

SCRUM-68 圖表數據抽取實作分析

SCRUM-68 圖表數據抽取實作分析

定位與依賴關係

功能需求

4 種圖表類型

時間篩選

現有後端分析

✅ 已存在：核心統計 API (SCRUM-67)

❌ 待建立：圖表數據 API

API 規格設計

Request

Response

SQL 查詢模式

活躍人數 (active_users)

做題數 (answer_count / total_answers)

未批改數 (unreviewed)

日期填充 (PHP 端處理)

實作 Checklist

Phase 1：後端 API

路由與控制器

Model 核心邏輯

資料庫索引

錯誤處理

Phase 2：前端 API 串接層

Server API Handler

類型定義

Composable

Phase 3：測試與驗證

後端測試

前端測試

效能測試

技術決策

日期填充策略

大範圍聚合策略

累計 vs 當日

關鍵檔案路徑

後端 (writeahead-main-api)

前端 (writeahead-admin-web)

風險與注意事項

與 SCRUM-43 的整合點

相關筆記

更新紀錄

關係圖譜

目錄

反向連結