88 lines
2.4 KiB
Plaintext
88 lines
2.4 KiB
Plaintext
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 ;
|