71 lines
2.4 KiB
Plaintext
71 lines
2.4 KiB
Plaintext
DELIMITER //
|
|
|
|
CREATE OR REPLACE PROCEDURE response_issue_status(
|
|
IN p_token VARCHAR(100),
|
|
IN p_functiontag VARCHAR(100),
|
|
IN p_id INT,
|
|
IN p_assign_user VARCHAR(10),
|
|
IN p_new_status VARCHAR(100),
|
|
IN p_ok_rate SMALLINT,
|
|
IN p_release_readme TEXT
|
|
)
|
|
/*
|
|
Function : 處理問題狀態變更和指派
|
|
Description: 根據不同的功能標籤處理不同類型的問題狀態更新
|
|
Build Date : 2023/07/19 (MariaDB 版本)
|
|
*/
|
|
BEGIN
|
|
DECLARE v_userid VARCHAR(10);
|
|
DECLARE v_org_userid VARCHAR(10);
|
|
DECLARE v_err_code SMALLINT;
|
|
DECLARE v_seq INT;
|
|
DECLARE v_err_msg VARCHAR(100);
|
|
|
|
SET v_err_code = 0;
|
|
SET v_err_msg = '操作成功';
|
|
|
|
-- 根據token獲取userid
|
|
SELECT userid INTO v_userid FROM sys_users WHERE token = p_token LIMIT 1;
|
|
|
|
IF v_userid IS NULL OR v_userid = '' THEN
|
|
SET v_err_code = 4;
|
|
SET v_err_msg = '認證失敗，請重新登錄';
|
|
ELSE
|
|
-- 根據功能標籤處理不同類型的問題
|
|
IF p_functiontag = 'pmsm11' THEN -- Product Backlog Item
|
|
-- 獲取原始負責人和當前最大序號
|
|
SELECT controller INTO v_org_userid FROM pms_pbi WHERE pbiid = p_id;
|
|
SELECT IFNULL(MAX(seqno), 0) INTO v_seq FROM pms_pbi_log WHERE pbiid = p_id;
|
|
|
|
-- 寫入日誌
|
|
INSERT INTO pms_pbi_log (pbiid, response_date, response_by, assign_user, response, pbi_status, pbi_rate, seqno)
|
|
VALUES (p_id, NOW(), v_userid, p_assign_user, p_release_readme, p_new_status, p_ok_rate, v_seq+1);
|
|
|
|
-- 更新主表狀態
|
|
UPDATE pms_pbi
|
|
SET pbi_status = IFNULL(p_new_status, pbi_status),
|
|
controller = IFNULL(v_userid, controller),
|
|
pbi_rate = IFNULL(p_ok_rate, pbi_rate)
|
|
WHERE pbiid = p_id;
|
|
|
|
ELSEIF p_functiontag = 'pmsm12' THEN -- Issue Log 維護作業
|
|
-- 獲取當前最大序號
|
|
SELECT IFNULL(MAX(seqno), 0) INTO v_seq FROM pms_issuelog_d WHERE issueid = p_id;
|
|
|
|
-- 寫入日誌
|
|
INSERT INTO pms_issuelog_d (issueid, raised_date, raised_by, assign_user, response, issue_status, seqno)
|
|
VALUES (p_id, NOW(), v_userid, p_assign_user, p_release_readme, p_new_status, v_seq+1);
|
|
|
|
-- 更新主表狀態
|
|
UPDATE pms_issuelog
|
|
SET issue_status = IFNULL(p_new_status, issue_status),
|
|
responsible = IFNULL(p_assign_user, responsible)
|
|
WHERE issueid = p_id;
|
|
END IF;
|
|
END IF;
|
|
|
|
SELECT v_err_code AS code, v_err_msg AS msg;
|
|
END //
|
|
|
|
DELIMITER ;
|