Skip to content

spec.md

Latest commit

 

History

History
409 lines (322 loc) · 12 KB

spec.md

File metadata and controls

409 lines (322 loc) · 12 KB
Raw

📋 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
        * 剩餘可補休 = 加班時數
        * 狀態 = "未使用"
    - 資料來源月份 = 該分頁名稱
    - 其他該月表格同一行之資料

功能 3: 反向驗證加班總表

函數： validateOvertimeRecords()

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

流程：

  1. 讀取「加班記錄總表」所有資料
  2. 對每一筆記錄： 
    根據 員工編號 → 找到員工試算表ID
根據 資料來源月份 → 找到對應分頁
根據 日期 → 在該分頁找到對應列

IF 找不到對應資料
THEN 標記為「資料異常」+ 記錄到錯誤日誌

功能 4: 個人補休表同步至中樞表

函數： syncPersonalLeaveRequests()

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

流程：

4.1 掃描個人補休表

對每位啟用員工：

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

4.2 同步至中樞表

對每筆新的補休申請：

1. 產生補休編號：LV-YYYYMMDD-員工編號-流水號
   例如：LV-20250815-E001-1

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

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

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

4.3 資料驗證與錯誤處理

驗證規則：
- 申請日期不可為空且格式正確
- 補休日期不可為空且格式正確  
- 使用時數必須 > 0
- 員工編號必須在員工清單中存在

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

功能 5: 補休與加班配對檢查

函數： matchLeaveWithOvertime()

步驟：

5.1 掃描未配對的補休記錄

SELECT * FROM 補休申請總表 
WHERE 對應加班編號 IS NULL OR 對應加班編號 = ""

5.2 自動配對邏輯

對每筆未配對的補休記錄：

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

5.3 回寫用掉補休編號

配對完成後，回寫補休編號到加班記錄：
1. 更新「加班記錄總表」的「用掉補休編號」欄位
2. 同步回寫到員工個人試算表的月份分頁
   - 根據員工編號找到對應的員工試算表
   - 根據資料來源月份找到對應分頁  
   - 根據日期找到對應列
   - 在備註欄或新增欄位填入「補休編號:LV-XXXXXX」

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

5.4 更新狀態

更新加班記錄狀態：
- 例假日加班記錄 → 狀態 = "例假日-僅發加班費" (固定不變)
- 其他加班記錄：
  - 剩餘可補休 = 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 筆

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

程式碼結構建議

// 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) { }