47 lines
1.3 KiB
Plaintext
47 lines
1.3 KiB
Plaintext
DELIMITER //
|
|
|
|
CREATE PROCEDURE qry_pmsq01(
|
|
IN p_projectid1 VARCHAR(50),
|
|
IN p_projectid2 VARCHAR(50)
|
|
)
|
|
/*
|
|
專案進度一覽表: 參考華炎的報表內容
|
|
專案名稱,客戶(無),專案經理,項目狀態,截止日期,待完成任務,待解決問題
|
|
*/
|
|
BEGIN
|
|
-- 返回狀態訊息
|
|
SELECT 0 AS code, 'ok' AS msg;
|
|
|
|
-- 主查詢
|
|
WITH wbs AS (
|
|
SELECT projectid, COUNT(*) AS wbs_no
|
|
FROM pms_pbi
|
|
WHERE pbi_type = 'Task' AND pbi_rate <> 100
|
|
GROUP BY projectid
|
|
),
|
|
issue AS (
|
|
SELECT projectid, COUNT(*) AS issue_no
|
|
FROM pms_pbi
|
|
WHERE pbi_type = 'Issue' AND pbi_rate <> 100
|
|
GROUP BY projectid
|
|
)
|
|
SELECT
|
|
P1.projectid,
|
|
P1.proj_cname,
|
|
P1.proj_boss,
|
|
P2.personcname,
|
|
P1.proj_status,
|
|
K.keyvalue AS proj_status_name,
|
|
P1.proj_enddate,
|
|
IFNULL(W.wbs_no, 0) AS wbs_no,
|
|
IFNULL(I.issue_no, 0) AS issue_no
|
|
FROM pmsproject P1
|
|
LEFT JOIN basperson P2 ON P1.proj_boss = P2.personid
|
|
LEFT JOIN wbs W ON P1.projectid = W.projectid
|
|
LEFT JOIN issue I ON P1.projectid = I.projectid
|
|
LEFT JOIN zen_keyvalue K ON K.fieldname = 'proj_status' AND K.keyid = P1.proj_status
|
|
WHERE CASE WHEN p_projectid1 = '*' THEN 1 ELSE P1.projectid >= p_projectid1 END
|
|
AND CASE WHEN p_projectid2 = '*' THEN 1 ELSE P1.projectid <= p_projectid2 END;
|
|
END //
|
|
|
|
DELIMITER ; |