DELIMITER //

CREATE OR REPLACE PROCEDURE get_gantt_data(
  IN p_projectid VARCHAR(100)
)
/*
  Function   : 配合 amis 的擴充元件(gantt), 取得甘特圖資料
  Description: 獲取專案甘特圖資料，處理雲伺服器時間差問題
  Build Date : 2023/07/19 (MariaDB 版本)
  
  Modify History
    Item Who        Date       Modify Docs
    ==== ========== ========== ================================================
    1    Michael    2022/7/31  初始版本
    2    Michael    2022/12/14 加入 Azure 雲端時間差處理
    3    Michael    2025/07/20 改寫為 MariaDB 並最佳化(ChatGPT+DeepSeek)
*/
BEGIN
  -- 聲明變數
  DECLARE v_year1 INT;
  DECLARE v_start_date VARCHAR(10);
  
  -- 設置默認起始日(去年1月1日)
  SET v_year1 = YEAR(CURDATE()) - 1;
  SET v_start_date = CONCAT(v_year1, '-01-01');
  
  -- 創建臨時表存儲結果
  DROP TEMPORARY TABLE IF EXISTS tmp_gantt_data;
  CREATE TEMPORARY TABLE tmp_gantt_data (
    id VARCHAR(100),
    name VARCHAR(255),
    ower VARCHAR(100),
    per DECIMAL(5,2),
    type TINYINT,
    startTime DATE,
    endTime DATE,
    stoneTime DATE,
    top INT,
    `left` INT,
    widthChild INT,
    widthMe INT,
    isShow TINYINT,
    ROW_ID INT
  );
  
  -- 獲取專案PBI資料
  INSERT INTO tmp_gantt_data
  SELECT
    pbiid AS id,
    pbi_title AS name,      -- 項目名稱
    owner AS ower,          -- 負責人
    pbi_rate AS per,        -- 進度(完成率)
    1 AS type,              -- 項目類型(1.PBI 2.mile stone 3.分組)
    startdate AS startTime,
    DATE_ADD(startdate, INTERVAL CAST(pbi_planworktime AS SIGNED) DAY) AS endTime,
    '2022-01-01' AS stoneTime, -- 里程碑日
    0 AS top,               -- 初始top位置
    DATEDIFF(startdate, v_start_date) * 40 AS `left`,
    CAST(pbi_planworktime AS SIGNED) * 40 AS widthChild,
    CAST(pbi_planworktime AS SIGNED) * 40 AS widthMe,
    1 AS isShow,
    @row_num := @row_num + 1 AS ROW_ID
  FROM pms_pbi, (SELECT @row_num := 0) AS r
  WHERE projectid = p_projectid
  ORDER BY pbiid ASC;
  
  -- 處理雲端時間差並返回結果
  SELECT
    id,
    name,
    ower,
    per,
    type,
    cloud_addtime(startTime) AS startTime,
    cloud_addtime(endTime) AS endTime,
    cloud_addtime(stoneTime) AS stoneTime,
    ((ROW_ID-1) * 40) + 15 AS top,
    `left`,
    widthChild,
    widthMe,
    isShow
  FROM tmp_gantt_data;
  
  -- 臨時表會在會話結束時自動刪除
END //

DELIMITER ;