DELIMITER //
CREATE OR REPLACE PROCEDURE sys_m_update_m(
  IN p_para_tablename VARCHAR(100)
)
/*
  function   : 配合 orm_api 的存檔後過帳, 當表頭存檔時, update master 的合計欄位(目前先寫死 totalamount)
  description: 目前先配合 omi_api 的存檔後機制(sys_transaction) 執行, 希望能全部透過設定, 而不是每一個 table 都必須客製
  build date : 2023/10/17
  
  modify history
    item who        date       modify docs
    ==== ========== ========== ================================================
    1    michael    2023-10-17 Init
	2    Michael    2025/07/20 改寫為 MariaDB 並最佳化(ChatGPT+DeepSeek)
*/
BEGIN
  /*
    Function: Update master table totals after detail table changes
    Build Date: 2023/10/17
  */
  DECLARE v_RelationKey VARCHAR(100);
  DECLARE v_MiddleField VARCHAR(100);
  DECLARE v_dest_table VARCHAR(100);
  DECLARE v_dest_field VARCHAR(100);
  DECLARE v_dest_field2 VARCHAR(100);
  DECLARE v_detail_tbl_name VARCHAR(100);
  DECLARE v_sql LONGTEXT;
  DECLARE v_para01 VARCHAR(100);
  DECLARE v_para02 VARCHAR(100);
  DECLARE v_pos INT;
  DECLARE v_source_field VARCHAR(100);
  DECLARE v_source_field2 VARCHAR(100);
  
  -- Read parameters from sys_transaction
  SELECT 
    IFNULL(RelationKey, ''), IFNULL(MiddleField, ''), IFNULL(para01, ''), IFNULL(para02, ''),
    IFNULL(dest_table, ''), IFNULL(dest_field, ''), IFNULL(dest_field2, ''), IFNULL(dest_field2_value, '')
  INTO
    v_RelationKey, v_MiddleField, v_para01, v_para02,
    v_dest_table, v_dest_field, v_dest_field2, v_detail_tbl_name
  FROM sys_transaction WHERE tablename = p_para_tablename;
  
  -- Debug
  IF v_para01 = '' THEN
    SET v_para01 = '231007-001';
  END IF;
  
  -- Check for equals sign to split fields (sum(subamount)=totalamount)
  SET v_pos = LOCATE('=', v_dest_field);
  IF v_pos > 0 THEN
    SET v_source_field = SUBSTRING(v_dest_field, 1, v_pos - 1);
    SET v_dest_field = SUBSTRING(v_dest_field, v_pos + 1);
  END IF;
  
  SET v_pos = LOCATE('=', v_dest_field2);
  IF v_pos > 0 THEN
    SET v_source_field2 = SUBSTRING(v_dest_field2, 1, v_pos - 1);
    SET v_dest_field2 = SUBSTRING(v_dest_field2, v_pos + 1);
  END IF;
  
  IF v_dest_field2 = '' THEN
    SET v_sql = CONCAT(
      'UPDATE ', v_dest_table, ' SET ', v_dest_field, ' = (SELECT ', v_source_field, 
      ' FROM ', v_detail_tbl_name, ' WHERE ', v_RelationKey, ' = ''', v_para01, ''') ',
      'WHERE ', v_MiddleField, ' = ''', v_para01, ''';'
    );
  ELSE
    SET v_sql = CONCAT(
      'UPDATE ', v_dest_table, ' SET ', v_dest_field, ' = (SELECT ', v_source_field, 
      ' FROM ', v_detail_tbl_name, ' WHERE ', v_RelationKey, ' = ''', v_para01, '''), ',
      v_dest_field2, ' = (SELECT ', v_source_field2, 
      ' FROM ', v_detail_tbl_name, ' WHERE ', v_RelationKey, ' = ''', v_para01, ''') ',
      'WHERE ', v_MiddleField, ' = ''', v_para01, ''';'
    );
  END IF;
  
  SET @sql = v_sql;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;