# 📋 AI Coding 需求文件

## 專案概述
Google Apps Script 專案，用於管理員工加班與補休記錄的雙向同步與驗證系統。

---

## 系統架構

### 檔案結構
```
主控台.xlsx (主要資料源)
├── 員工清單
├── 加班記錄總表
└── 補休申請總表

員工A-2025.xlsx, 員工B-2025.xlsx... (20個檔案)
└── 各月份分頁 (08月, 09月...)
```

### 資料表欄位定義

**員工總表**
```
編號	姓名	表格ID	表格連結	狀態
EX:
E001    張OO    asdfjkl-12345    [連結](https://docs.google.com/spreadsheets/..../edit)  啟用|暫停|已離職
```

**加班統計總表 (主控台)**
```
| 加班編號 | 員工編號 | 姓名 | 日期 | 星期 | 加班類型 | 加班時數 | 已使用補休 | 剩餘可補休 | 狀態 | 資料來源月份 | 用掉補休編號 | 錯誤提示 |
```

**補休申請總表 (主控台)**
```
| 補休編號 | 員工編號 | 姓名 | 申請日期 | 補休日期 | 開始時間 | 結束時間 | 使用時數 | 對應加班編號 | 備註 | 行政組查閱打勾 | 錯誤提示 |
```

**員工月份分頁**
```
| 員工 | 日期 | 星期 | 日期·加班屬性 | 上班打卡 | 下班打卡 | 應上下班時間 | 工作時數 | 午晚休時數 | 扣除午晚休後的工時 | 當日加班時數 | 備註 |
```

**日期·加班屬性 可選值：**
- 上班日 / 上班日加班 / 休息日 / 例假日
- 國定假日OO節 / 國定假日OO節的補休日 / 特別休假
- 節日名稱和特休原因請寫在備註欄

**應上下班時間 公式範例：**
```
// 方案A: 合併顯示 (G欄)
=IF(D4="上班日", "應上班" & TEXT(E4,"HH:MM") & CHAR(10) & "應下班" & TEXT(E4+TIME(8,0,0),"HH:MM"), "")

// 方案B: 分離顯示
G4: =IF(D4="上班日", "應上班" & TEXT(E4,"HH:MM"), "")
H4: =IF(D4="上班日", "應下班" & TEXT(E4+TIME(8,0,0),"HH:MM"), "")
```

**個人補休表分頁** (員工個人試算表)
```
| 補休編號 | 員工編號 | 姓名 | 申請日期 | 補休日期 | 開始時間 | 結束時間 | 使用時數 | 對應加班編號 | 備註 | 狀態 |
```
- 員工填寫：員工編號、姓名、申請日期、補休日期、開始時間、結束時間、使用時數、備註
- 系統自動填寫：補休編號、對應加班編號、狀態

**個人加班表分頁** (員工個人試算表)
```
| 加班編號 | 員工編號 | 姓名 | 日期 | 星期 | 加班類型 | 加班時數 | 已使用補休 | 剩餘可補休 | 狀態 | 資料來源月份 | 用掉補休編號 |
```
- 系統自動同步：從主控台「加班記錄總表」同步而來，供員工查閱自己的加班記錄匯總

**請假分頁**
```
| 請假編號 | 員工編號 | 姓名 | 請假類型 | 開始日期 | 結束日期 | 請假時數 | 請假原因 | 行政組查閱打勾 |
```
- 請假類型：事假 / 病假 / 公假 / 婚假 / 喪假 / 產假 等

---

## 核心需求

### 功能 1: 主控台觸發全員檢查
**入口函數：** `checkAllEmployees()`

**流程：**
1. 從「員工清單」讀取所有啟用員工
2. 對每位員工執行完整檢查
3. 記錄執行結果到「執行紀錄」

---

### 功能 2: 員工表格數據同步檢查
**函數：** `checkEmployeeData(employeeId, employeeFileId)`

**步驟：**

#### 2.1 掃描員工所有月份分頁
- 讀取該員工試算表的所有分頁
- 篩選出符合格式的月份分頁（如 "8月", "9月"）
- 依序處理每個月份

#### 2.2 提取加班資料
從每個月份分頁中提取：
- 日期
- 加班時數（非零值）
- 加班類型（上班日/上班日加班/休息日/例假日/國定假日/特別休假）
- 備註

**⚠️ 重要：例假日加班處理規則**
- 例假日加班依法只能發加班費，不可選補休
- 系統須標記例假日加班為「不可補休」狀態
- 配對邏輯需排除例假日加班記錄

#### 2.3 比對主控台加班總表並同步到個人加班表
對每筆員工加班記錄：
```
IF 主控台「加班記錄總表」中不存在 (員工編號 + 日期) 的記錄
THEN 新增一行到「加班記錄總表」
    - 自動產生加班編號（如 "OT-20250801-E001-1": 前綴+日期+員工編號+同一天流水號）
    - 填入：員工編號、姓名、日期、加班時數、加班類型
    - 如果加班類型 = "例假日":
        * 已使用補休 = 0
        * 剩餘可補休 = 0
        * 狀態 = "例假日-僅發加班費"
    - 否則:
        * 已使用補休 = 0
        * 剩餘可補休 = 加班時數
        * 狀態 = "未使用"
    - 資料來源月份 = 該分頁名稱
    - 其他該月表格同一行之資料
    
    AND 同步到員工個人試算表的「個人加班表」分頁
    - 如果「個人加班表」分頁不存在，自動建立
    - 如果該加班編號不存在於個人加班表，則新增該記錄
    - 欄位內容與主控台「加班記錄總表」相同（不含錯誤提示欄）
```

---

### 功能 3: 反向驗證加班總表
**函數：** `validateOvertimeRecords()`

**目的：** 確保主控台的每筆加班記錄都能在員工檔案中找到對應數據

**流程：**
1. 讀取「加班記錄總表」所有資料
2. 對每一筆記錄：
   ```
   根據 員工編號 → 找到員工試算表ID
   根據 資料來源月份 → 找到對應分頁
   根據 日期 → 在該分頁找到對應列
   
   IF 找不到對應資料
   THEN 標記為「資料異常」+ 記錄到錯誤日誌
   ```

---

### 功能 4: 個人補休表同步至中樞表
**函數：** `syncPersonalLeaveRequests()`

**目的：** 將員工個人補休表的申請同步到主控台的補休申請總表

**流程：**

#### 4.1 掃描個人補休表
對每位啟用員工：
```javascript
1. 讀取員工試算表的「個人補休表」分頁
2. 找出尚未同步的補休申請 (補休編號為空的列)
3. 驗證必要欄位：員工編號、申請日期、補休日期、使用時數
```

#### 4.2 同步至中樞表
對每筆新的補休申請：
```javascript
1. 產生補休編號：LV-YYYYMMDD-員工編號-流水號
   例如：LV-20250815-E001-1

2. 自動配對加班編號：
   - 取得該員工所有「剩餘可補休 > 0」且「狀態 ≠ 例假日-僅發加班費」的加班記錄
   - 按日期由舊到新排序
   - 計算可配對的加班時數總計
   - 如果使用時數 > 可配對時數總計：錯誤 = "補休時數超過可用加班時數"

3. 新增到「補休申請總表」：
   - 補休編號 = 自動產生
   - 員工編號、姓名、申請日期、補休日期、使用時數 = 來源資料
   - 對應加班編號 = 自動配對結果 (多筆用逗號分隔)
   - 備註 = 錯誤訊息 (如有)
   - 行政組查閱打勾 = FALSE (核選方框)

4. 回寫補休編號到個人補休表
```

#### 4.3 資料驗證與錯誤處理
```javascript
驗證規則：
- 申請日期不可為空且格式正確
- 補休日期不可為空且格式正確  
- 使用時數必須 > 0
- 員工編號必須在員工清單中存在

錯誤處理：
- 驗證失敗：在「補休申請總表」備註欄記錄錯誤原因
- 配對失敗：在「補休申請總表」備註欄記錄「補休時數超過可用加班時數」
- 仍然建立補休記錄，但標記錯誤供行政人員處理
```

---

### 功能 5: 補休與加班配對檢查
**函數：** `matchLeaveWithOvertime()`

**步驟：**

#### 5.1 掃描未配對的補休記錄
```sql
SELECT * FROM 補休申請總表 
WHERE 對應加班編號 IS NULL OR 對應加班編號 = ""
```

#### 5.2 自動配對邏輯
對每筆未配對的補休記錄：

```javascript
1. 取得該員工的所有加班記錄（按日期由舊到新排序）
2. 篩選出「剩餘可補休 > 0」且「狀態 ≠ 例假日-僅發加班費」的加班記錄
3. 依序分配：
   
   補休需求時數 = 使用時數
   
   WHILE 補休需求時數 > 0:
       找到最舊的可用加班記錄
       
       IF 該加班記錄剩餘時數 >= 補休需求時數:
           分配全部需求時數到該加班記錄
           更新加班記錄：已使用補休 += 補休需求時數
           更新加班記錄：剩餘可補休 -= 補休需求時數
           補休需求時數 = 0
           
       ELSE:
           分配該加班記錄的全部剩餘時數
           更新加班記錄：已使用補休 = 加班時數
           更新加班記錄：剩餘可補休 = 0
           更新加班記錄：狀態 = "已全數使用"
           補休需求時數 -= 該加班記錄剩餘時數
           
       更新補休記錄：對應加班編號 += 該加班編號 (用逗號分隔)
       
   IF 補休需求時數 > 0:
       標記錯誤：「補休時數超過可用加班時數」
```

#### 5.3 回寫用掉補休編號
```javascript
配對完成後，回寫補休編號到加班記錄：
1. 更新「加班記錄總表」的「用掉補休編號」欄位
2. 同步回寫到員工個人試算表的月份分頁
   - 根據員工編號找到對應的員工試算表
   - 根據資料來源月份找到對應分頁  
   - 根據日期找到對應列
   - 在備註欄或新增欄位填入「補休編號:LV-XXXXXX」
3. 同步更新員工個人試算表的「個人加班表」分頁
   - 更新對應加班記錄的「已使用補休」、「剩餘可補休」、「狀態」、「用掉補休編號」欄位
   - 確保員工可即時查看自己的加班使用狀況

回寫規則：
- 如果一筆加班被多個補休使用，用逗號分隔：LV-001,LV-002
- 如果配對失敗，不回寫補休編號
```

#### 5.4 更新狀態
```javascript
更新加班記錄狀態：
- 例假日加班記錄 → 狀態 = "例假日-僅發加班費" (固定不變)
- 其他加班記錄：
  - 剩餘可補休 = 0 → 狀態 = "已全數使用"
  - 剩餘可補休 > 0 但 已使用補休 > 0 → 狀態 = "部分使用"
  - 已使用補休 = 0 → 狀態 = "未使用"
```

---

## 執行順序

```
主函數 checkAllEmployees()
│
├─ Step 1: 對每位員工執行
│   └─ checkEmployeeData(employeeId, fileId)
│       ├─ 1.1 掃描月份分頁
│       ├─ 1.2 提取加班資料
│       └─ 1.3 比對並新增到主控台
│
├─ Step 2: 反向驗證
│   └─ validateOvertimeRecords()
│       └─ 檢查主控台每筆加班是否對應到員工資料
│
├─ Step 3: 個人補休表同步
│   └─ syncPersonalLeaveRequests()
│       ├─ 3.1 掃描所有員工的「個人補休表」分頁
│       ├─ 3.2 同步新的補休申請到中樞表
│       └─ 3.3 產生補休編號和配對加班編號
│
└─ Step 4: 補休與加班配對
    └─ matchLeaveWithOvertime()
        ├─ 4.1 找出未配對補休
        ├─ 4.2 自動分配到最舊加班
        ├─ 4.3 更新剩餘時數與狀態
        └─ 4.4 回寫用掉補休編號到加班記錄
```

---

## 錯誤處理

### 需要記錄的錯誤情況
1. 員工檔案無法存取（權限問題）
2. 月份分頁格式不正確
3. 加班資料在主控台找不到對應
4. 補休時數超過可用加班時數
5. 資料格式錯誤（日期、數字格式）

### 錯誤記錄格式
```
| 時間戳記 | 錯誤類型 | 員工編號 | 詳細訊息 | 受影響資料 |
```

---

## 技術規格

### 使用工具
- Google Apps Script (clasp)
- SpreadsheetApp API

### 效能考量
- 批次讀寫（避免逐筆操作）
- 使用 `getValues()` 和 `setValues()` 而非 `getValue()` / `setValue()`
- 每位員工處理完記錄一次進度

### 資料安全
- 所有寫入操作要有事前備份機制
- 關鍵操作加入 try-catch
- 失敗時不影響其他員工的處理

---

## 測試案例

### 測試場景 1: 新增加班記錄
```
員工A在 2025-08 分頁有加班 8 小時
主控台「加班記錄總表」中沒有這筆資料
→ 應該新增一筆加班記錄，剩餘可補休 = 8
```

### 測試場景 2: 補休自動配對
```
員工A有兩筆加班：
- 2025-08-01: 10小時 (剩餘10)
- 2025-08-05: 8小時 (剩餘8)

員工A申請補休 12 小時
→ 應該先用掉 8/1 的 10小時，再用掉 8/5 的 2小時
→ 8/1 狀態變「已全數使用」，8/5 剩餘6小時
```

### 測試場景 3: 補休超量錯誤
```
員工A只有 5 小時可補休
員工A申請補休 8 小時
→ 應該標記錯誤，不執行配對
```

---

## 輸出要求

### 執行完成後顯示
```
✅ 檢查完成報告
━━━━━━━━━━━━━━━━━━━━
處理員工數：20
新增加班記錄：35 筆
配對補休記錄：12 筆
發現錯誤：2 筆

詳細記錄請查看「執行紀錄」工作表
```

---

## 程式碼結構建議

```javascript
// Code.js

// ===== 主要流程 =====
function checkAllEmployees() { }

// ===== 員工資料檢查 =====
function checkEmployeeData(employeeId, fileId) { }
function scanMonthlySheets(fileId) { }
function extractOvertimeData(sheet) { }
function addOvertimeRecord(record) { }

// ===== 反向驗證 =====
function validateOvertimeRecords() { }

// ===== 補休配對 =====
function matchLeaveWithOvertime() { }
function allocateLeaveToOvertime(leaveRecord) { }
function updateOvertimeStatus(overtimeId) { }

// ===== 工具函數 =====
function getMasterSheet(sheetName) { }
function getEmployeeSheet(fileId, sheetName) { }
function generateOvertimeId(date) { }
function logError(type, employeeId, message) { }
```