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 ;