63 lines
2.1 KiB
Plaintext
63 lines
2.1 KiB
Plaintext
DELIMITER //
|
|
|
|
CREATE OR REPLACE PROCEDURE bpm_sign_pass (
|
|
IN p_token VARCHAR(100),
|
|
IN p_functiontag VARCHAR(100), -- 單別(* 表示全部)
|
|
IN p_sdate VARCHAR(10), -- 日期區間(* 表示全部)
|
|
IN p_edate VARCHAR(10)
|
|
)
|
|
/*
|
|
Function : 配合 amis 的 bpm, 取得 [經辦事項清單]
|
|
Description: 所有曾經簽核過, 但是尚未結案的 flow
|
|
Build Date : 2022/11/16
|
|
|
|
Modify History
|
|
Item Who Date Modify Docs
|
|
==== ========== ========== ================================================
|
|
1 Michael 2022/11/16
|
|
2 Michael 2022/12/04 新增 @functiontag 的 where filter
|
|
|
|
*/
|
|
BEGIN
|
|
-- token 轉 userid
|
|
DECLARE v_userid VARCHAR(20);
|
|
|
|
-- 根據傳入的 token 取得 userid, 並會判斷 token 是否仍有效(無效傳回空白)
|
|
main_block: BEGIN
|
|
IF v_userid = '' THEN
|
|
LEAVE main_block; -- 結束程序
|
|
END IF;
|
|
|
|
-- 處理參數(TDB)
|
|
IF IFNULL(p_functiontag, '') = '' THEN
|
|
SET p_functiontag = '*';
|
|
END IF;
|
|
|
|
-- 回傳狀態
|
|
SELECT 0 AS code, 'ok' AS msg;
|
|
|
|
-- 判斷原單已結案? 沒有 P 應該就是已結案
|
|
WITH data AS (
|
|
SELECT flow_id, sourceid, COUNT(*) AS not_end
|
|
FROM bpm_flow_sign
|
|
WHERE sign_status = 'P'
|
|
GROUP BY flow_id, sourceid
|
|
)
|
|
SELECT
|
|
b.functiontag, b.flow_name, a.sourceid, a.sourceid AS billno, a.sign_note,
|
|
a.sign_status, KV.keyvalue AS sign_status_name, d.departmentcname,
|
|
a.signerid, p.personcname, a.stepName, cloud_addtime(a.create_date) AS create_date
|
|
FROM bpm_flow_sign A
|
|
LEFT JOIN bpm_list B ON A.flow_id = B.flow_id
|
|
LEFT JOIN basperson P ON A.signerid = P.personid
|
|
LEFT JOIN basdepartment D ON D.departmentid = P.departmentid
|
|
LEFT JOIN zen_keyvalue KV ON KV.fieldname = 'sign_status' AND KV.keyid = A.sign_status
|
|
LEFT JOIN data X ON X.flow_id = A.flow_id AND X.sourceid = A.sourceid
|
|
WHERE FIND_IN_SET(a.sign_status, 'A,R,C') > 0 -- 已簽核過, 但尚未結案
|
|
AND CASE WHEN p_functiontag = '*' THEN p_functiontag ELSE b.functiontag END = p_functiontag
|
|
AND IFNULL(x.not_end, 0) > 0
|
|
AND a.signerid = v_userid;
|
|
END;
|
|
END //
|
|
|
|
DELIMITER ; |