DELIMITER // CREATE OR REPLACE PROCEDURE zen_dynamicMenu( IN p_token VARCHAR(100), IN p_moduleid VARCHAR(10), IN p_ip VARCHAR(100), IN p_db1 VARCHAR(100), IN p_db2 VARCHAR(100) ) /* Function : 根據 menu 產生 amis 所需的 site.json 內容 Description: 每一筆 menu 都預設先有一個 00 role(EveryOne) Build Date : 2021/08/14 CALL zen_dynamicMenu('89b179f7-72a0-11f0-9c5f-d8bbc15c1404','zen','https://api.gex.com.tw:8033','EIS_demo','EIS_demo'); Modify History Item Who Date Modify Docs ==== ========== ========== ================================================ 1 Michael 2022-05-05 微調2個判斷, 避免組出的 site menu 因逗號錯誤無法顯示 menu 2 Michael 2022-05-21 修改 @ip 內容(配合 https 的大改版), 刪除原程式的 http:// 內容 3 Michael 2023-02-01 為了更加彈性, @ip 及 @db 這2個欄位改傳入 ${ls:xxxx}, 測試可行(程式要調整前端呼叫的程式) 4 Michael 2023-02-04 修改 menu 的排序, 改為依 seq_no(原先是 menuid) 5 Michael 2023-04-04 改依參數檔設定的程式版本別 6 Michael 2023-04-22 新增 Wizard 為 zen 模組的相關判斷 */ main_block: BEGIN DECLARE v_rets, v_ret_menu, v_all_menu, v_myfavor LONGTEXT; DECLARE v_userid VARCHAR(20); DECLARE v_imax1, v_imax2, v_y1, v_y2, v_x1, v_x2 INT DEFAULT 0; DECLARE v_pid, v_id1, v_name1, v_id2, v_name2 VARCHAR(100); DECLARE v_isLocalMenu, v_crlf, v_comma CHAR(1); DECLARE v_ver_type, v_wizard_model VARCHAR(100); -- 宣告一級選單游標 DECLARE cur_menu00 CURSOR FOR SELECT menuid, caption FROM tmp_menu00 ORDER BY rowid; -- 宣告二級選單游標 DECLARE cur_menu99 CURSOR FOR SELECT parent_menuid, menuid, caption, isLocalMenu FROM tmp_menu99 ORDER BY rowid; DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = TRUE; -- 初始化變數 SET v_crlf = CHAR(10); SET v_wizard_model = 'zen'; -- 取得版本類型（預設 beta） SELECT COALESCE(pvalue, 'beta') INTO v_ver_type FROM sys_parameter WHERE systemid = 'base' AND pid = 'ver_type' LIMIT 1; -- 根據 token 取得使用者 ID SELECT userid INTO v_userid FROM sys_users WHERE token = p_token LIMIT 1; IF v_userid = '' THEN LEAVE main_block; -- 結束程序 END IF; -- 建立臨時表 (取代原來的表變數) CREATE TEMPORARY TABLE IF NOT EXISTS tmp_menu00 ( menuid VARCHAR(100), caption NVARCHAR(100), rowid INT AUTO_INCREMENT PRIMARY KEY ); CREATE TEMPORARY TABLE IF NOT EXISTS tmp_menu99 ( parent_menuid VARCHAR(100), menuid VARCHAR(100), caption NVARCHAR(100), isLocalMenu CHAR(1), rowid INT AUTO_INCREMENT PRIMARY KEY ); TRUNCATE TABLE tmp_menu00; TRUNCATE TABLE tmp_menu99; -- 插入一級選單 (排序用) INSERT INTO tmp_menu00 (menuid, caption) SELECT DISTINCT m.menuid, m.caption FROM sys_menutable m WHERE m.moduleid = p_moduleid AND ( EXISTS ( SELECT 1 FROM sys_rolemenusecurity rms JOIN sys_userroles ur ON ur.roleid = rms.roleid WHERE ur.userid = v_userid AND rms.controlname = 'read' AND rms.menuid = m.menuid ) OR EXISTS ( SELECT 1 FROM sys_rolemenusecurity rms WHERE rms.roleid = '00' AND rms.menuid = m.menuid ) ) AND COALESCE(m.parent_menuid, '') = '' ORDER BY (SELECT seq_no FROM sys_menutable WHERE menuid = m.menuid); -- 插入二級選單 (排序用) INSERT INTO tmp_menu99 (parent_menuid, menuid, caption, isLocalMenu) SELECT DISTINCT m.parent_menuid, m.menuid, m.caption, COALESCE(m.isLocalMenu, 'N') FROM sys_menutable m WHERE m.moduleid = p_moduleid AND ( EXISTS ( SELECT 1 FROM sys_rolemenusecurity rms JOIN sys_userroles ur ON ur.roleid = rms.roleid WHERE ur.userid = v_userid AND rms.controlname = 'read' AND rms.menuid = m.menuid ) OR EXISTS ( SELECT 1 FROM sys_rolemenusecurity rms WHERE rms.roleid = '00' AND rms.menuid = m.menuid ) ) AND COALESCE(m.parent_menuid, '') <> '' ORDER BY m.parent_menuid, (SELECT seq_no FROM sys_menutable WHERE menuid = m.menuid); -- 取得我的最愛選單 SET v_myfavor = udf_myfavor(p_moduleid, v_userid, v_ver_type, p_ip, p_db2); -- 初始化固定 JSON 結構 SET v_rets = CONCAT( '{', v_crlf, ' "pages": [', v_crlf, ' {', v_crlf, ' "label": "Home",', v_crlf, ' "url": "/",', v_crlf, ' "redirect": "/', CASE WHEN p_moduleid = 'bpm' THEN 'bpm' ELSE 'sys' END, '/dashboard"', v_crlf, ' },', v_crlf, ' {', v_crlf, ' "children": [', v_crlf ); -- 加入 Dashboard 項目 IF p_moduleid <> 'bpm' THEN SET v_rets = CONCAT(v_rets, ' {', v_crlf, ' "label": "Dash Board",', v_crlf, ' "url": "/sys/dashboard",', v_crlf, ' "icon": "fas fa-chart-pie",', v_crlf, ' "schemaApi": "get:/pages/sys-dashboard.json"', v_crlf, ' },', v_crlf, ' {', v_crlf, ' "label": "電子簽核主頁",', v_crlf, ' "visible": false,', v_crlf, ' "url": "/bpm/dashboard",', v_crlf, ' "icon": "fas fa-chart-pie",', v_crlf, ' "schemaApi": "get:/pages/bpm-dashboard.json"', v_crlf, ' }', v_crlf ); ELSE SET v_rets = CONCAT(v_rets, ' {', v_crlf, ' "label": "電子簽核主頁",', v_crlf, ' "url": "/bpm/dashboard",', v_crlf, ' "icon": "fas fa-chart-pie",', v_crlf, ' "schemaApi": "get:/pages/bpm-dashboard.json"', v_crlf, ' },', v_crlf, ' {', v_crlf, ' "label": "Dash Board",', v_crlf, ' "visible": false,', v_crlf, ' "url": "/sys/dashboard",', v_crlf, ' "icon": "fas fa-chart-pie",', v_crlf, ' "schemaApi": "get:/pages/sys-dashboard.json"', v_crlf, ' }', v_crlf ); END IF; -- 加入我的最愛 SET v_rets = CONCAT(v_rets, ' ]', v_crlf, ' },', v_crlf, v_myfavor, v_crlf -- CASE WHEN v_myfavor IS NOT NULL AND v_myfavor <> '' THEN -- CONCAT(v_crlf, ',') -- 只有當有內容時才加逗號和內容 -- ELSE v_crlf -- END ); -- 處理一級選單 SET v_imax1 = (SELECT COALESCE(MAX(rowid), 0) FROM tmp_menu00); SET v_y1 = 1; WHILE v_y1 <= v_imax1 DO -- 取得一級選單資料 SELECT menuid, caption INTO v_id1, v_name1 FROM tmp_menu00 WHERE rowid = v_y1; SET v_ret_menu = CONCAT( ' {', v_crlf, ' "children": [', v_crlf, ' {', v_crlf, ' "label": "', v_name1, '",', v_crlf, ' "url": "/', p_moduleid, '",', v_crlf, ' "icon": "fa fa-cube",', v_crlf, ' "children": [', v_crlf ); -- 處理二級選單 SET v_imax2 = (SELECT COALESCE(MAX(rowid), 0) FROM tmp_menu99 WHERE parent_menuid = v_id1); SET v_y2 = (SELECT COALESCE(MIN(rowid), 0) FROM tmp_menu99 WHERE parent_menuid = v_id1); WHILE v_y2 <= v_imax2 DO -- 取得二級選單資料 SELECT menuid, caption, isLocalMenu INTO v_id2, v_name2, v_isLocalMenu FROM tmp_menu99 WHERE rowid = v_y2 AND parent_menuid = v_id1; -- 判斷是否為最後一項 IF v_y2 = v_imax2 THEN SET v_comma = ''; ELSE SET v_comma = ','; END IF; -- 根據是否為本地選單生成不同結構 IF v_isLocalMenu = 'Y' THEN SET v_ret_menu = CONCAT(v_ret_menu, ' {', v_crlf, ' "label": "', v_name2, '",', v_crlf, ' "url": "/', p_moduleid, '/', v_id2, '",', v_crlf, ' "schemaApi": "get:/pages/', v_id2, '.json"', v_crlf, ' }', v_comma, v_crlf ); ELSE SET v_ret_menu = CONCAT(v_ret_menu, ' {', v_crlf, ' "label": "', v_name2, '",', v_crlf, ' "url": "/', p_moduleid, '/', v_id2, '",', v_crlf, ' "schemaApi": {', v_crlf, ' "method": "post",', v_crlf, ' "url": "', p_ip, '/xapi/v2/', p_db2, '/zen_showsource/0/",', v_crlf, ' "data": {', v_crlf, ' "para01": "', v_id2, '",', v_crlf, ' "para02": "', v_ver_type, '",', v_crlf, ' "para03": "', p_ip, '"', v_crlf, ' },', v_crlf, ' "dataType": "form"', v_crlf, ' }', v_crlf, ' }', v_comma, v_crlf ); END IF; -- 特殊處理 Wizard 模組 IF (v_id2 = 'zen01' OR v_id2 = 'zen11') AND p_moduleid = v_wizard_model THEN -- 加入四項 Wizard 相關功能 SET v_ret_menu = CONCAT(v_ret_menu, ' {', v_crlf, ' "label": "Wizard Function",', v_crlf, ' "url": "/', v_wizard_model, '/function",', v_crlf, ' "visible": false,', v_crlf, ' "schemaApi": {', v_crlf, ' "method": "post",', v_crlf, ' "url": "', p_ip, '/xapi/v2/', p_db2, '/zen_showsource/0/",', v_crlf, ' "data": {', v_crlf, ' "para01": "zen02",', v_crlf, ' "para02": "', v_ver_type, '",', v_crlf, ' "para03": "', p_ip, '"', v_crlf, ' },', v_crlf, ' "dataType": "form"', v_crlf, ' }', v_crlf, ' },', v_crlf, ' {', v_crlf, ' "label": "Wizard Fields",', v_crlf, ' "url": "/', v_wizard_model, '/fields",', v_crlf, ' "visible": false,', v_crlf, ' "schemaApi": {', v_crlf, ' "method": "post",', v_crlf, ' "url": "', p_ip, '/xapi/v2/', p_db2, '/zen_showsource/0/",', v_crlf, ' "data": {', v_crlf, ' "para01": "zen03",', v_crlf, ' "para02": "', v_ver_type, '",', v_crlf, ' "para03": "', p_ip, '"', v_crlf, ' },', v_crlf, ' "dataType": "form"', v_crlf, ' }', v_crlf, ' },', v_crlf, ' {', v_crlf, ' "label": "Query Wizard Function",', v_crlf, ' "url": "/', v_wizard_model, '/query_function",', v_crlf, ' "visible": false,', v_crlf, ' "schemaApi": {', v_crlf, ' "method": "post",', v_crlf, ' "url": "', p_ip, '/xapi/v2/', p_db2, '/zen_showsource/0/",', v_crlf, ' "data": {', v_crlf, ' "para01": "zen12",', v_crlf, ' "para02": "', v_ver_type, '",', v_crlf, ' "para03": "', p_ip, '"', v_crlf, ' },', v_crlf, ' "dataType": "form"', v_crlf, ' }', v_crlf, ' },', v_crlf, ' {', v_crlf, ' "label": "Query Wizard Fields",', v_crlf, ' "url": "/', v_wizard_model, '/query_fields",', v_crlf, ' "visible": false,', v_crlf, ' "schemaApi": {', v_crlf, ' "method": "post",', v_crlf, ' "url": "', p_ip, '/xapi/v2/', p_db2, '/zen_showsource/0/",', v_crlf, ' "data": {', v_crlf, ' "para01": "zen13",', v_crlf, ' "para02": "', v_ver_type, '",', v_crlf, ' "para03": "', p_ip, '"', v_crlf, ' },', v_crlf, ' "dataType": "form"', v_crlf, ' }', v_crlf, ' }', v_comma, v_crlf ); END IF; SET v_y2 = v_y2 + 1; END WHILE; -- 關閉一級選單結構 SET v_ret_menu = CONCAT(v_ret_menu, ' ]', v_crlf, ' }', v_crlf, ' ]', v_crlf, ' }' ); -- 加入開發測試區（僅 Wizard 模組的最後一項） IF v_y1 = v_imax1 AND p_moduleid = v_wizard_model THEN SET v_ret_menu = CONCAT(v_ret_menu, ',', v_crlf, ' {', v_crlf, ' "children": [', v_crlf, ' {', v_crlf, ' "label": "開發公測區",', v_crlf, ' "url": "/dev",', v_crlf, ' "icon": "fab fa-dev",', v_crlf, -- ' "visibleOn": "1=0",', v_crlf, ' "children": [', v_crlf, ' {', v_crlf, ' "label": "測試程式01",', v_crlf, ' "url": "/dev/dev01",', v_crlf, ' "schemaApi": "get:/pages/dev-test01.json"', v_crlf, ' },', v_crlf, ' {', v_crlf, ' "label": "測試程式02",', v_crlf, ' "url": "/dev/dev02",', v_crlf, ' "schemaApi": "get:/pages/dev-test02.json"', v_crlf, ' },', v_crlf, ' {', v_crlf, ' "label": "測試程式03",', v_crlf, ' "url": "/dev/dev03",', v_crlf, ' "schemaApi": "get:/pages/dev-test03.json"', v_crlf, ' }', v_crlf, ' ]', v_crlf, ' }', v_crlf, ' ]', v_crlf, ' }' ); END IF; -- 判斷是否為最後一項 IF v_y1 = v_imax1 THEN SET v_comma = ''; ELSE SET v_comma = ','; END IF; SET v_ret_menu = CONCAT(v_ret_menu, v_comma, v_crlf); SET v_all_menu = CONCAT(COALESCE(v_all_menu, ''), v_ret_menu); SET v_y1 = v_y1 + 1; END WHILE; -- 組合最終 JSON IF v_imax1 <= 0 THEN -- 移除最後的逗號，保留換行 IF RIGHT(v_rets, 11) = '},\n\n ]\n}' THEN SET v_rets = CONCAT(LEFT(v_rets, CHAR_LENGTH(v_rets) - 8), '\n\n ]\n}'); ELSEIF RIGHT(v_rets, 3) = '},\n' THEN SET v_rets = CONCAT(LEFT(v_rets, CHAR_LENGTH(v_rets) - 2), '\n ]', v_crlf, '}'); ELSE SET v_rets = CONCAT(v_rets, ' ]', v_crlf, '}'); END IF; ELSE SET v_rets = CONCAT(v_rets, v_all_menu, v_crlf, ' ]', v_crlf, '}'); END IF; -- 返回結果 SELECT 0 AS code, '選單生成成功!' AS msg; SELECT v_rets AS jsoncode; -- 清理臨時表 DROP TEMPORARY TABLE IF EXISTS tmp_menu00; DROP TEMPORARY TABLE IF EXISTS tmp_menu99; END // DELIMITER ;