DELIMITER //

CREATE OR REPLACE PROCEDURE bpmm02_spread (
  IN p_uuid VARCHAR(100),           -- 也可以傳入 functiontag
  IN p_personid VARCHAR(20),        -- 起單人
  IN p_source_pk_value VARCHAR(100) -- 單號
)
/*
  功能      : 配合 vue+jsplumb 的 bpm, 將 bpm_setup 的設定, 展開為實際的簽核步驟
  描述      : 本程式可能會在單據按 [上呈] 時呼叫
               可能要寫在 View Form, 包含 [上呈] [同意] [駁回] 等都是
              CALL bpmm02_spread('d9930b1c-26a9-4000-a7ba-0f0292601000','120912','221030-001')
  建立日期  : 2022/10/31
  
  修改歷史
    項目  誰         日期        修改說明
    ==== ========== ==========  ================================================
    1    michael    2022-10-31 測試
    2    Michael    2022-11-05 初版完成基本功能
    3    Michael    2022-11-29 新增 update assign_to 
    4    Michale    2023-04-14 配合 Node 機制, 加入 bpm 發送 mail 的通用機制
*/
BEGIN
  DECLARE v_elementId VARCHAR(100);
  DECLARE v_stepId VARCHAR(100);
  DECLARE v_filterSet VARCHAR(1000);
  DECLARE v_nextStep VARCHAR(1000);
  DECLARE v_nextStep2 VARCHAR(1000);
  DECLARE v_nextStep_false VARCHAR(100);
  DECLARE v_switchStep VARCHAR(1000);
  DECLARE v_stepName VARCHAR(100);
  DECLARE v_typeName VARCHAR(100);
  DECLARE v_nodeName VARCHAR(100);
  DECLARE v_roleName VARCHAR(100);
  DECLARE v_userid VARCHAR(100);
  DECLARE v_tablename VARCHAR(100);
  DECLARE v_source_pk VARCHAR(100);
  
  DECLARE v_sql_cmd LONGTEXT;
  DECLARE v_now_elementId VARCHAR(100);
  DECLARE v_nowStep VARCHAR(100);
  DECLARE v_step INT;
  DECLARE v_crlf VARCHAR(2);
  
  DECLARE v_all_signers VARCHAR(200);
  DECLARE v_done INT DEFAULT FALSE;
  DECLARE v_cnt INT;

  SET v_crlf = CHAR(10);

  -- 以請假單為例(由 tag 轉 uuid)
  IF LENGTH(p_uuid) < 20 THEN
    SELECT flow_id INTO p_uuid FROM bpm_list WHERE functiontag = p_uuid;
  END IF;

  -- 取得 startNode 的 nextStep
  SELECT nextStep INTO v_nextStep FROM bpm_setup WHERE flow_id = p_uuid AND elementId = 'startNode';

  /*
    寫入申請人的上呈資料(固定第一筆)
  */
  INSERT INTO bpm_flow_sign (flow_id, sourceid, stepName, flow_level, signerid, sign_status, create_date, sign_note)
  VALUES (p_uuid, p_source_pk_value, '上呈', 0, p_personid, 'N', NOW(), '');

  -- 取得第一個 node
  SELECT 
    s.elementId, s.stepId, s.nextStep, s.filterSet,
    s.nextStep_false, s.typeName, s.roleName
  INTO 
    v_elementId, v_stepId, v_nextStep, v_filterSet,
    v_nextStep_false, v_typeName, v_roleName
  FROM bpm_list l 
  LEFT JOIN bpm_setup s ON l.flow_id = s.flow_id
  WHERE l.flow_id = p_uuid AND stepId = v_nextStep;
    
  -- 刪除超過 7 天的 log 資料 
  DELETE FROM bpm_flow_spread_log WHERE create_date < DATE_SUB(NOW(), INTERVAL 7 DAY);

  -- 往下展開, 一直到 stop
  SET v_nowStep = v_nextStep;
  SET v_now_elementId = v_elementId;
  SET v_step = 1;
  
  WHILE v_now_elementId <> 'stopNode' DO
    -- 先判斷 condition true/false
    IF v_elementId = 'conditionNode' THEN
      SELECT source_table, source_pk INTO v_tablename, v_source_pk FROM bpm_list WHERE flow_id = p_uuid;  
      SELECT roleName, stepName INTO v_roleName, v_stepName FROM bpm_setup WHERE flow_id = p_uuid AND stepId = v_stepId;
        
      DELETE FROM bpm_flow_spread_log WHERE flow_id = p_uuid AND sourceid = p_source_pk_value AND stepId = v_stepId;
      
      -- 使用預備語句執行動態SQL
      SET @sql = CONCAT('SELECT COUNT(*) INTO @cnt FROM ', v_tablename, 
                       ' WHERE ', v_source_pk, ' = ''', p_source_pk_value, 
                       ''' AND ', v_filterSet);
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      
      -- 根據條件結果插入不同的nextStep
      IF @cnt > 0 THEN
        INSERT INTO bpm_flow_spread_log (flow_id, sourceid, stepId, nextStep, create_date) 
        VALUES (p_uuid, p_source_pk_value, v_stepId, v_nextStep, NOW());
      ELSE
        INSERT INTO bpm_flow_spread_log (flow_id, sourceid, stepId, nextStep, create_date) 
        VALUES (p_uuid, p_source_pk_value, v_stepId, v_nextStep_false, NOW());
      END IF;

      SET v_step = v_step - 1;
      SELECT nextStep INTO v_nextStep FROM bpm_flow_spread_log 
      WHERE flow_id = p_uuid AND sourceid = p_source_pk_value AND stepId = v_stepId;
    ELSEIF v_elementId = 'switchNode' THEN
      -- 取得所有會簽的名單(我不建議支援此功能, 並不合理, 但程式段暫時保留)
      SELECT 'hello';
    ELSE
      -- 調用用戶定義函數獲取簽核人
      SELECT bpmm02_udf_spread_signer(p_uuid, p_personid, p_source_pk_value, v_stepId, v_typeName, v_elementId, v_filterSet, v_step) INTO v_sql_cmd;
      SET @dynamic_sql = v_sql_cmd;
      PREPARE stmt FROM @dynamic_sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
    END IF;
    
    -- 取得下一個 node
    SELECT 
      elementId, stepId, nextStep, filterSet, nextStep_false, typeName, roleName
    INTO 
      v_elementId, v_stepId, v_nextStep, v_filterSet, v_nextStep_false, v_typeName, v_roleName
    FROM bpm_setup WHERE flow_id = p_uuid AND stepId = v_nextStep;
    
    SET v_step = v_step + 1;
    SET v_nowStep = v_nextStep;
    SET v_now_elementId = v_elementId;
  END WHILE;

  -- 先填入 assign_to 的預設值
  UPDATE bpm_flow_sign SET assign_to = 'N'
  WHERE flow_id = p_uuid AND sourceid = p_source_pk_value;

  -- update bpm_flow_sign
  UPDATE bpm_flow_sign SET sign_status = 'A', assign_to = 'Y'
  WHERE flow_id = p_uuid AND sourceid = p_source_pk_value AND flow_level = 0;

  -- update bpm_flow_sign 的指派, 此處如果第一關就有多人, 都會被設為 Y(暫時無解)
  UPDATE bpm_flow_sign SET assign_to = 'Y' 
  WHERE flow_id = p_uuid AND sourceid = p_source_pk_value AND flow_level = 1;

  /*
    寫入發送 mail 的統一管控檔(use zenclouddb)
    使用 sys_users.user_extend1 = Y 判斷是否要送 mail
  */
  INSERT INTO zenclouddb.zen_mail_list (corp_db, recipient, mail_subject, content, mail_status, create_date)
  SELECT 
    p.pvalue, u.email, CONCAT('簽核通知:【', s.flow_name, '】', bs.sign_note),
    CONCAT('Hi ', u.username, ',', CHAR(10),
    '您有一個待簽核的電子表單，請登入簽核系統後，速審速核，感謝您的配合 !!', CHAR(10),
    '送達時間:', DATE_FORMAT(bs.create_date, '%Y-%m-%d %H:%i:%s'), CHAR(10),
    '主旨:', bs.sign_note, CHAR(10),
    '簽核步驟:', bs.stepname),
    0, NOW()
  FROM bpm_flow_sign bs
  LEFT JOIN sys_users u ON bs.signerid = u.userid
  LEFT JOIN sys_parameter p ON p.pid = 'corpid'
  LEFT JOIN bpm_list s ON s.flow_id = bs.flow_id
  WHERE bs.flow_id = p_uuid AND bs.flow_level = 1 AND bs.sourceid = p_source_pk_value AND assign_to = 'Y';

  -- 下一關的簽核人
  SELECT bpmm02_udf_next_signer(p_uuid, p_source_pk_value, 1) INTO v_all_signers;
  
  -- 更新原單狀態
  /*
      step 3: 寫回原單的 flow_status
      N:待簽核 P:簽核中 Z:結案 C:作廢
  */
  -- update 原單(hrs_leave), flow_level 及 flowstatus
  SELECT source_table, source_pk INTO v_tablename, v_source_pk FROM bpm_list WHERE flow_id = p_uuid;  
    
  -- 動態更新原表
  SET @sql = CONCAT(
    'UPDATE ', v_tablename, ' SET flow_status = ''P''',
    ', flow_signer = ''', IFNULL(v_all_signers, ''), '''',
    ' WHERE ', v_source_pk, ' = ''', p_source_pk_value, ''''
  );
  
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;