DELIMITER //

CREATE OR REPLACE PROCEDURE bpm_sign_finish (
  IN p_token VARCHAR(100)
)
/*
  Function   : 配合 amis 的 bpm, 取得 [已結案事項清單]
  Description: 所有曾經簽核過, 且已經結案的 flow   
  Build Date : 2022/11/16
  
  Modify History
    Item Who        Date       Modify Docs
    ==== ========== ========== ================================================
    1    Michael    2022/11/16
	
*/
BEGIN
  -- token 轉 userid
  DECLARE v_userid VARCHAR(20);

  -- 根據傳入的 token 取得 userid, 並會判斷 token 是否仍有效(無效傳回空白)
  main_block: BEGIN
	  IF v_userid = '' THEN 
	    LEAVE main_block;  -- 結束程序
	  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 1=1
	  AND IFNULL(x.not_end, 0) = 0
	  AND a.signerid = v_userid;
  END;
END //

DELIMITER ;