DELIMITER //

CREATE OR REPLACE PROCEDURE gex_chart_comm(
  IN p_functiontag VARCHAR(20),  -- 程式代號(可以自行控制讀取的資料來源)
  IN p_date1 VARCHAR(10),
  IN p_date2 VARCHAR(10),
  IN p_type VARCHAR(20)    -- bar/line/pie ...
)
/*
  function   : 客戶訂單業績排行前 10 的統計圖, 支援 5種圖形顯示, 這是 amis chart 類的報表的標準寫法
  description: 將一般報表, 轉為 amis queryGroup 類的報表的 sp 範本 (本程式待驗證 ...)
  build date : 2021/12/02
  
  call sample:
  CALL gex_chart_comm('opor11','2020-01-01','2020-12-31','pie')
  
  modify history
    item who        date       modify docs
    ==== ========== ========== ================================================
    1    Michael    2021/12/02 新增
    2    Michael    2025/08/06 改寫為 MariaDB 版本
*/
BEGIN
  DECLARE v_uuid CHAR(100);
  DECLARE v_data TEXT;
  
  -- 生成 UUID
  SET v_uuid = UUID();
  
  -- 先刪除可能存在的舊數據
  DELETE FROM dw_sys_chart_data WHERE uuid = v_uuid;
  
  -- 客戶業績排行前 10
  IF p_functiontag = 'OPOR11' then  
	  INSERT INTO dw_sys_chart_data (uuid, functiontag, xname, xvalue)
	  SELECT v_uuid, p_functiontag, IFNULL(c.corpshortname, m.custid), CAST(SUM(totalamount) AS DECIMAL(18,2)) AS amt 
	  FROM opoorder2_m m
	  LEFT JOIN bascustomer c ON m.custid = c.custid
	  WHERE billdate >= p_date1 AND billdate <= p_date2
	  GROUP BY IFNULL(c.corpshortname, m.custid)
	  ORDER BY SUM(totalamount) DESC
	  LIMIT 10;
  -- else if ...
  end if;
  
  -- 調用通用函數 (假設已存在對應的 MariaDB 函數)
  SELECT sys_genchart(v_uuid, p_type) INTO v_data;
  
  -- 處理模板數據 (假設已存在對應的 MariaDB 存儲過程)
  CALL sys_chart_process(v_uuid);
  
  -- 返回結果
  SELECT 0 AS code, 'ok' AS msg;
  SELECT v_data AS jsoncode;
END //

DELIMITER ;