這是一份為企業級系統的簽核流程引擎系統規格書。這套架構充分利用了關聯式資料庫處理動態邏輯與交易（Transaction）的優勢，並將非同步任務（如寄發郵件）完美切割給後端服務處理。

# SQL 預存程序驅動之簽核流程引擎系統規格書

## 1. 系統架構概述

本簽核流程引擎（BPM）以關聯式資料庫為核心，透過前端（如 Vue + amis）定義流程 JSON，並由後端資料庫的預存程序（Stored Procedures）負責解析、展開與狀態驅動 。引擎具備高度擴展性，能與各式營運表單（如請假單、資訊處理單、系統變更需求單等）無縫綁定 。信件通知機制則採解耦設計，由資料庫寫入佇列後，交由 Node.js 排程服務發送 。

---

## 2. 核心狀態與代碼定義

系統透過標準化代碼控制節點與表單的生命週期。

### 2.1 節點類型 (Element ID)

* 
**startNode**: 流程起點 。


* 
**nodeNode**: 一般簽核節點 。


* 
**conditionNode**: 條件判斷節點 。


* 
**switchNode**: 分支節點 。


* 
**stopNode**: 流程終點 。



### 2.2 關卡簽核狀態 (`sign_status`)

* 
**N**: 待簽核 。


* 
**P**: 簽核中 。


* 
**R**: 拒絕（駁回） 。


* 
**A**: 同意 。


* 
**C**: 作廢 。



### 2.3 來源表單總體狀態 (`flow_status`)

* 
**N**: 待簽核 。


* 
**P**: 簽核中 。


* 
**Z**: 結案 。


* 
**C**: 作廢 。



---

## 3. 資料庫結構 (Schema Design)

### 3.1 流程定義與設定檔

* 
**`bpm_list`**: 存放流程的原始 JSON 定義與來源表單關聯設定 。


* 包含欄位：`flow_id`、`flow_name`、`functiontag`（如 hrsm11） 。


* 關聯設定：`source_table`（來源表名）、`source_pk`（主鍵）、`source_note`（主旨欄位） 。


* 定義檔：`flow_json`、`flow_json_org` 。




* 
**`bpm_setup`**: 儲存 JSON 解析後展開的節點明細 。


* 包含欄位：`elementId`、`stepId`、`filterSet`（條件公式）、`nextStep`、`nextStep_false` 。


* 簽核人設定：`typeName`（簽核人類型，如部門主管、指定角色）、`roleName` 。





### 3.2 流程運作與紀錄檔

* 
**`bpm_flow_sign`**: 紀錄實際簽核關卡與處理狀態的核心資料表 。


* 包含欄位：`sourceid`、`stepName`、`flow_level`（關卡次序）、`signerid`、`sign_status` 。


* 派工作業：`assign_to`（若關卡有多人，紀錄指派給誰的註記，單人或指定人為 Y，其餘為 N） 。




* 
**`bpm_flow_spread_log`**: 暫存展開流程時 condition 節點判斷的 nextStep 。


* 資料僅保留最近 7 天，以節省空間 。




* 
**`bpm_flow_sign_log`**: 簽核動作的歷史軌跡稽核檔 。


* 紀錄 `sign_status`（同意/拒絕）與 `sign_note`（簽核意見） 。





### 3.3 系統整合檔

* 
**`zen_mail_list`**: 跨庫整合的郵件發送佇列（位於 `zenclouddb` 或 `master`） 。


* 包含收件人、主旨、內容與 `mail_status`（0: 未發送，1: 已發送） 。





---

## 4. 預存程序 (Stored Procedures) 規格清單

### 4.1 流程定義管理 (Setup & Parsing)

* 
**`bpmm01_crud`**: 將前端設計好的流程 JSON 寫入或更新至 `bpm_list` 資料表中 。


* 
**`bpmm01_get`**: 透過 `flow_id` 取出 `bpm_list` 中保存的原始 JSON 內容供前端編輯器渲染 。


* 
**`bpmm01_spread`**: 將 JSON 格式的流程定義解析成關聯式資料寫入 `bpm_setup` 。


* 
**邏輯**: 透過字串分割迴圈處理，辨識 `elementId` 並萃取 `filterSet`、條件分支（`nextStep`、`nextStep_false`）與多分支（`switchNode` 最高支援 10 個 branch） 。寫入前會先清空舊的定義以防止重複 。





### 4.2 流程引擎驅動 (Engine Execution)

* 
**`bpmm02_spread`**: 表單「上呈」時觸發，負責將流程範本展開為實際的簽核關卡 。


* 
**邏輯**: 寫入申請人節點（flow_level = 0），並一路往下推演直到遇到 `stopNode` 。


* 
**動態條件**: 遇到 `conditionNode` 時，會動態組裝 SQL 去查詢來源表單（如 `hrs_leave`），根據條件公式（`filterSet`）的檢驗結果決定走 `nextStep` 或 `nextStep_false` 。


* 
**初始派工**: 將第一關簽核人的 `assign_to` 設為 'Y'，並更新來源表單狀態為 'P' 。同時將通知信件寫入佇列交由 Node.js 發送 。




* 
**`bpmm02_sign_active`**: 處理核心的簽核動作（同意 A、駁回 R、作廢 C） 。


* 
**交易安全**: 全程包覆於 `BEGIN TRANSACTION` 與 `TRY...CATCH` 中，確保異動的一致性 。


* 
**同意 (A)**: 推進至下一關卡（`flow_level + 1`）；若為最後一關，則將原單狀態轉為結案 ('Z') 。


* 
**駁回 (R)**: 退回上一關（`flow_level - 1`）或依據傳入的 `@next_signer` 指定退回至之前曾經簽核過的特定人員 。


* 
**作廢 (C)**: 直接中止流程，狀態設為 'C' 。


* 
**狀態回寫**: 利用動態 SQL（`sys_sql_debug`）回寫來源表單（如將 `hrs_leave` 的 `flow_status` 更新） 。寫入軌跡紀錄至 `bpm_flow_sign_log` 。





### 4.3 介面查詢與輔助 (UI & Helpers)

* 
**`bpm_sign_todo`**: 查詢當前登入使用者的「待簽核」清單（狀態為 'P' 且 `assign_to` 為 'Y'） 。


* 
**`bpm_sign_pass`**: 查詢使用者曾經參與簽核但「尚未結案」的「已簽核」表單清單 。


* 
**`bpm_sign_signers`**: 列出單一表單完整的「簽核歷程」與所有參與過的簽核人狀態 。


* 
**`bpmm02_sign_assign`**: 提供前端 UI 在同意或駁回時的「指定簽核人」下拉選單資料來源 。


* 同意時，列出下一關（`flow_level + 1`）的人員供主管派工 。


* 駁回時，列出歷史關卡（`flow_level < @flow_level`）中曾實際處理過的人員供指定退回 。





---

## 5. 跨環境時區處理架構

由於資料庫部署於雲端（如 Azure），系統底層紀錄的皆為 UTC (格林威治時間) 。為確保前端報表與列表的顯示一致性，實作了專屬 UDF 解決：

* 
**`dbo.cloud_addtime`**: 查詢端（如 `bpm_sign_todo`, `bpm_sign_pass`, `bpm_sign_signers`）在輸出 `create_date` 等時間欄位時，必須呼叫此函式動態加上 8 小時轉換為台北時間 。此設計確保了底層資料的時區純淨度，僅在展示層進行轉換。



---

這套規格已涵蓋了您提供的所有簽核引擎底層細節。需要我為您進一步深入分析其中「動態 SQL 條件引擎的防禦機制」，或是針對「後端 API 呼叫這些 SP 的架構」進行探討嗎？