DELIMITER //

CREATE OR REPLACE PROCEDURE zen_updatesource (
  IN p_functiontag VARCHAR(100),
  IN p_version VARCHAR(100),
  IN p_jsoncode MEDIUMTEXT
)
/*
  function   : 回寫 json code 的內容(前端有修改傳入)
  description: 
  build date : 2021/10/17
  
  call sample:
  CALL zen_updatesource('abc01','beta','json-code')
  
  modify history
    item who        date       modify docs
    ==== ========== ========== ================================================
    1    michael    2021-10-17 新增
    2    Michael    2023-07-09 新增根據 sys_parameter 的 ver_type 判斷版本
    3    Michael    2024/03/06 擴充 json4 及 json5 2個欄位
*/
BEGIN
  DECLARE v_zcode MEDIUMTEXT;
  DECLARE v_pvalue VARCHAR(100);
  DECLARE v_cnt INT;
  DECLARE v_final_version VARCHAR(100) DEFAULT IFNULL(p_version, 'beta');
  
  -- 依設定的程式版本別設定變數 2023/07/09
  -- 檢查 sys_parameter 表是否存在
  IF (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sys_parameter') > 0 THEN
    SELECT pvalue INTO v_pvalue FROM sys_parameter WHERE systemid = 'base' AND pid = 'ver_type' LIMIT 1;
    IF v_pvalue IS NOT NULL AND v_pvalue <> '' THEN
      SET v_final_version = v_pvalue;
    END IF;
  END IF;
  
  SET v_zcode = p_jsoncode;
  
  -- 去除編輯用的特殊字串 2024-02-13
  IF INSTR(v_zcode, '_W_') > 0 THEN
    SET v_zcode = REPLACE(v_zcode, '_W_', '');
  END IF;
  
  -- 檢查記錄是否存在
  SELECT COUNT(*) INTO v_cnt FROM zen_jsoncode WHERE functiontag = p_functiontag AND version = v_final_version;
  
  -- 如果不存在就新增，否則更新
  -- SET v_zcode = REPLACE(v_zcode, '\n', '');
  IF v_cnt <= 0 THEN
    INSERT INTO zen_jsoncode (functiontag, version, jsoncode)
    VALUES (p_functiontag, v_final_version, v_zcode);
  ELSE
    UPDATE zen_jsoncode 
    SET jsoncode = v_zcode
	WHERE functiontag = p_functiontag AND version = v_final_version;
  END IF;
  
  -- 回傳成功結果
  SELECT 0 AS code, 'OK' AS msg;
END //

DELIMITER ;