132 lines
5.7 KiB
Plaintext
132 lines
5.7 KiB
Plaintext
DELIMITER //
|
||
|
||
CREATE OR REPLACE PROCEDURE orm_api_v2(
|
||
IN p_token VARCHAR(100), -- token(直接傳入)
|
||
IN p_tablename VARCHAR(200), -- tablename
|
||
IN p_pk VARCHAR(100), -- primary key(for update delete)
|
||
IN p_action VARCHAR(20), -- CRUDP(如果是 Read, p_paras 參數內容有特殊寫法)
|
||
IN p_paras LONGTEXT -- 前端組好的資料內容(應該是 json 格式的內容)
|
||
)
|
||
/*
|
||
Function : 寫一支通用 orm-api 依傳入的參數及設定, 智慧的組出相應的 sql
|
||
MariaDB 版本
|
||
|
||
Description: 本程式是想模仿 orm 的設定, 然後讓前端程式只要傳入參數, 就能執行 orm 的增刪查改的基本功能
|
||
R(Read) 是讀取單筆資料, p_paras 的參數內容同 CU >> 取消不用
|
||
P(Page) 是以頁為單位的讀取模式, 一般是 cowork with DataGrid
|
||
|
||
呼叫 api 範例如下
|
||
--新增資料
|
||
CALL orm_api_v2('B9F290D4-0F57-45B1-A786-F83A63E8BC4D','basarea','areaid','C','{"areaid":"mm", "areacnmae": "Michael"}');
|
||
CALL orm_api_v2('token_value','basarea','areaid','U','') --修改資料
|
||
CALL orm_api_v2('token_value','basarea','areaid','D','') --刪除資料
|
||
|
||
(pass)CALL orm_api_v2('B9F290D4-0F57-45B1-A786-F83A63E8BC4D','basarea','areaid','P','2^5^areaid^*'); -- 分頁查詢
|
||
-- 新增 Query 後要分頁顯示(擴充 P 的功能, 多傳 wheresql)
|
||
SET @para = CONCAT('areaid LIKE ',QUOTE('%A%'));
|
||
CALL orm_api_v2('token_value','basarea','areaid','P','1^10^fieldid^*^functiontag,tablename,fieldname^zen01');
|
||
|
||
-- 多筆資料 update
|
||
call orm_api_v2('aa54ef9b-7139-11f0-9c5f-d8bbc15c1404','bascustcategory','custcateid','U',
|
||
'{"rows":[
|
||
{"custcateid":"03","custcatecname":"大型企業","custcateename":"555","memochar":"","mergetype":"0"},
|
||
{"custcateid":"04","custcatecname":"政府","custcateename":"666","memochar":"","mergetype":"0"}],
|
||
"rowsDiff":[
|
||
{"custcateename":"555"},{"custcateename":"666"}],"indexes":["2","3"],
|
||
"rowsOrigin":[{"custcateid":"03","custcatecname":"大型企業","custcateename":"","memochar":"","mergetype":"0"},
|
||
{"custcateid":"04","custcatecname":"政府","custcateename":"","memochar":"","mergetype":"0"}],
|
||
"unModifiedItems":[
|
||
{"custcateid":"01","custcatecname":"經銷商","custcateename":"333","memochar":"","mergetype":"0"},
|
||
{"custcateid":"02","custcatecname":"代理商","custcateename":"444","memochar":"","mergetype":"0"},
|
||
{"custcateid":"05","custcatecname":"中小企業","custcateename":"","memochar":"","mergetype":"0"},
|
||
{"custcateid":"06","custcatecname":"學校","custcateename":"","memochar":"","mergetype":"0"},
|
||
{"custcateid":"07","custcatecname":"竹科","custcateename":"","memochar":"","mergetype":"0"}]
|
||
,"token":"aa54ef9b-7139-11f0-9c5f-d8bbc15c1404",
|
||
"_$_tableName":"bascustcategory",
|
||
"_$_pk":"custcateid","_$_action":"U"}');
|
||
|
||
SELECT * FROM orm_json_tmp;
|
||
delete FROM orm_json_tmp;
|
||
|
||
|
||
Build Date : 2023/12/16 (原始版本)
|
||
Modify Date: 2023/12/16 (MariaDB 遷移版本)
|
||
*/
|
||
BEGIN
|
||
DECLARE v_userid VARCHAR(100);
|
||
DECLARE v_uuid VARCHAR(36);
|
||
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
||
BEGIN
|
||
ROLLBACK;
|
||
RESIGNAL;
|
||
END;
|
||
|
||
-- 驗證 token
|
||
SELECT userid INTO v_userid FROM sys_users WHERE token = p_token LIMIT 1;
|
||
|
||
IF v_userid IS NULL THEN
|
||
-- 特殊邏輯：ddmmyy + (yyyy+mm+dd 的最後一碼)
|
||
-- 例如 2026-04-13：
|
||
-- ddmmyy = 130426
|
||
-- CheckSum = (2026 + 4 + 13) = 2043 -> 最後一碼為 3
|
||
-- 最終比對值 = 1304263
|
||
|
||
IF p_token = CONCAT(
|
||
DATE_FORMAT(NOW(), '%d%m%y'),
|
||
(YEAR(NOW()) + MONTH(NOW()) + DAY(NOW())) % 10
|
||
) THEN
|
||
SET v_userid = 'sys_assign';
|
||
ELSE
|
||
-- 若既非正確 token 也非特殊代碼，則報錯
|
||
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'token 錯誤, 請洽系統管理員!!';
|
||
END IF;
|
||
END IF;
|
||
|
||
-- 整理 json 資料
|
||
SET p_paras = orm_retrive_json_v2(p_paras);
|
||
|
||
-- 刪除過期的暫存資料
|
||
DELETE FROM orm_json_tmp WHERE create_date < DATE_SUB(NOW(), INTERVAL 1 DAY);
|
||
|
||
-- 根據操作類型執行不同邏輯
|
||
IF p_action IN ('C', 'Insert', 'U', 'Update', 'D', 'Delete') THEN
|
||
-- 開啟事務
|
||
START TRANSACTION;
|
||
|
||
-- 處理 JSON 資料
|
||
SET p_paras = REPLACE(p_paras, '": "', '":"');
|
||
SET v_uuid = UUID();
|
||
|
||
-- call usp_parsejson(v_uuid,p_paras);
|
||
CALL parse_JSON(v_uuid, p_paras);
|
||
|
||
-- 將傳入的 json 寫入暫存表方便處理
|
||
INSERT INTO orm_json_tmp (uuid, parent_id, object_id, json_key, json_value, valuetype, create_date)
|
||
SELECT v_uuid, parent_id, object_id, name, stringvalue, ValueType, NOW()
|
||
FROM sys_jsondata WHERE UUID = v_uuid and valuetype <> 'OBJECT' and valuetype <> 'ARRAY';
|
||
-- 測試時先 Mark
|
||
DELETE FROM sys_jsondata WHERE UUID = v_uuid;
|
||
|
||
-- 根據操作類型調用不同副程式
|
||
IF p_action IN ('C', 'Insert') THEN
|
||
CALL orm_exec_insert_v2(p_tablename, p_pk, p_action, v_uuid, v_userid);
|
||
ELSEIF p_action IN ('U', 'Update') THEN
|
||
CALL orm_exec_update_v2(p_tablename, p_pk, p_action, v_uuid, v_userid);
|
||
ELSEIF p_action IN ('D', 'Delete') THEN
|
||
CALL orm_api_kernel_v2(p_tablename, p_pk, p_action, v_uuid, 1);
|
||
END IF;
|
||
|
||
-- 存檔後過帳機制
|
||
CALL orm_exec_aftersave_v2(p_tablename, p_pk, p_action, v_userid);
|
||
|
||
-- 提交事務
|
||
COMMIT;
|
||
|
||
ELSEIF p_action IN ('P', 'Page') THEN
|
||
-- 核心的 orm 程式, 取得顯示在 DataTable 的分頁資料
|
||
CALL orm_exec_page_v2(p_tablename, p_pk, v_userid, p_paras);
|
||
END IF;
|
||
END //
|
||
|
||
DELIMITER ;
|