DELIMITER //

CREATE OR REPLACE PROCEDURE sys_d_update_m(
  IN p_tablename VARCHAR(100)
)
/*
  function   : 配合 orm_api 的存檔後過帳, 當新增一筆 log detail 後, update master 的某1-2 個同名欄位的值
  description: 目前先配合 omi_api 的存檔後機制(sys_transaction) 執行, 希望能全部透過設定, 而不是每一個 table 都必須客製
               copy from sys_transfer_comm
			   目前只能針對 detail 或 log 的 pk 是自增值的情況寫入
  build date : 2022/06/04
  
  modify history
    item who        date       modify docs
    ==== ========== ========== ================================================
    1    michael    2022-6-4   測試
	2    Michael    2023-02-11 測試並修改 pms_pbi_log, 目前已可正確 update, 但仍需繼續觀察
	3    Michael    2025/07/20 改寫為 MariaDB 並最佳化(ChatGPT+DeepSeek)
*/
BEGIN
  DECLARE v_relation_key VARCHAR(100);
  DECLARE v_middle_field VARCHAR(100);
  DECLARE v_dest_table VARCHAR(100);
  DECLARE v_dest_field VARCHAR(100);
  DECLARE v_dest_field2 VARCHAR(100);
  DECLARE v_dest_field2_value VARCHAR(100);
  DECLARE v_sql TEXT;
  DECLARE v_para01 VARCHAR(100);
  DECLARE v_para02 VARCHAR(100);
  
  -- get data
  SELECT 
    IFNULL(RelationKey,''), IFNULL(MiddleField,''), 
    IFNULL(para01,''), IFNULL(para02,''),
    IFNULL(dest_table,''), IFNULL(dest_field,''), 
    IFNULL(dest_field2,''), IFNULL(dest_field2_value,'')
  INTO
    v_relation_key, v_middle_field, 
    v_para01, v_para02,
    v_dest_table, v_dest_field, 
    v_dest_field2, v_dest_field2_value
  FROM sys_transaction 
  WHERE tablename = p_tablename;
  
  -- 构建 SQL
  SET @sql = CONCAT(
    'SELECT ', v_dest_field, ', IFNULL(', v_dest_field2, ', ''''), ', v_middle_field,
    ' INTO @v_field1, @v_field2, @v_field3 ',
    'FROM ', p_tablename, 
    ' WHERE ', v_relation_key, ' = ''', v_para01, ''';'
  );
  
  IF v_dest_field2 IS NOT NULL AND v_dest_field2 <> '' THEN
    SET @sql = CONCAT(@sql,
      'UPDATE ', v_dest_table, 
      ' SET ', v_dest_field, ' = @v_field1, ', v_dest_field2, ' = @v_field2 ',
      'WHERE ', v_middle_field, ' = @v_field3;'
    );
  ELSE
    SET @sql = CONCAT(@sql,
      'UPDATE ', v_dest_table, 
      ' SET ', v_dest_field, ' = @v_field1 ',
      'WHERE ', v_middle_field, ' = @v_field3;'
    );
  END IF;
  
  -- exec SQL
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;